API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Nov 27th, 2015
82
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 33.18 KB | None | 0 0
raw download clone embed print report
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Text;
  5. using System.Threading.Tasks;
  6. using System.Data;
  7. using Decisor.Core;
  8. using Decisor.Core.DB;
  9. using Decisor.Core.Users;
  10. using System.Net;
  11. using System.Net.Mail;
  12. using System.Net.Security;
  13. using System.Net.Sockets;
  14. using System.Security;
  15. using System.Security.Authentication.ExtendedProtection.Configuration;
  16.  
  17.  
  18. namespace dcsAlertasBragalux
  19. {
  20.  
  21.  
  22. public class SmtpEmailService
  23. {
  24. /// <summary>
  25. /// Método de envio de email com um client smtp.
  26. /// </summary>
  27. /// <param name="tarefa">Nome da tarefa: 'Novas Obras' ou 'Novas Facturas'</param>
  28. /// <param name="html">Uma string que contem a estrutra html da tabela com os dados obras/facturas</param>
  29. /// <param name="user">Utilizador Decisor.Core.DB.dcsDB</param>
  30. ///
  31.  
  32.  
  33.  
  34. private static string CLIENT_EMAIL = "mlry000244@bragalux.pt";
  35. private static string CLIENT_PASSWORD = "2YTguMDEhZT";
  36. private static string CLIENT_HOST = "outbound-relay-in.ptprime.pt";
  37. private static int CLIENT_PORT = 25;
  38.  
  39. MailAddress EMAIL_FROM = new MailAddress(CLIENT_EMAIL, "Alertas Bragalux", Encoding.UTF8);
  40. MailAddress EMAIL_GERMANO = new MailAddress("germano.duarte@bragalux.pt", "Germano Duarte", Encoding.UTF8);
  41. MailAddress EMAIL_JOAO = new MailAddress("joao.carvalho@elo-si.com", "João Carvalho", Encoding.UTF8);
  42. MailAddress EMAIL_MIGUEL = new MailAddress("miguel.dias@elo-si.com", "Miguel Dias", Encoding.UTF8);
  43.  
  44.  
  45.  
  46. private void EnviarEmail(string tarefa, string html, Decisor.Core.DB.dcsDB user)
  47. {
  48. if (html.Equals(""))
  49. {
  50. try
  51. {
  52. dcsFieldInfoLst dcsField = new dcsFieldInfoLst();
  53. dcsField.SetField("DtRegisto", DateTime.Now);
  54. dcsField.SetField("Report", "Não existem obras ou facturas novas.");
  55. long lIdRet = 0;
  56. user.ExecInsert(dcsField, "SmtpEmailReport", false, ref lIdRet);
  57.  
  58. SmtpClient sc = new SmtpClient(CLIENT_HOST, CLIENT_PORT);
  59. sc.Credentials = new System.Net.NetworkCredential(CLIENT_EMAIL, CLIENT_PASSWORD);
  60. sc.DeliveryMethod = SmtpDeliveryMethod.Network;
  61. sc.EnableSsl = false;
  62.  
  63. MailMessage msg = new MailMessage(EMAIL_FROM, EMAIL_JOAO);
  64.  
  65. string EMAIL_BODY = "<html>";
  66. EMAIL_BODY += "<head>";
  67. EMAIL_BODY += "<meta http-equiv='Content-Type' content='text/html; charset=utf-8' />";
  68. EMAIL_BODY += "<meta name='viewport' content='width=device-width'/>";
  69. EMAIL_BODY += "<style type='text/css'>";
  70. EMAIL_BODY += "* {margin: 0;padding: 0;font-size: 100%;font-family: 'Avenir Next', 'Helvetica Neue', 'Helvetica', Helvetica, Arial, sans-serif;line-height: 1.65; }img {max-width: 100%;margin: 0 auto;display: block; }body,.body-wrap {width: 100% !important;height: 100%;background: #FFFFFF;-webkit-font-smoothing: antialiased;-webkit-text-size-adjust: none; }a {color: #71bc37;text-decoration: none; }.text-center {text-align: center; }.text-right {text-align: right; }.text-left {text-align: left; }.button {display: inline-block;color: white;background: #71bc37;border: solid #71bc37;border-width: 10px 20px 8px;font-weight: bold;border-radius: 4px; }h1, h2, h3, h4, h5, h6 {margin-bottom: 20px;line-height: 1.25; } h1 { font-size: 32px; }h2 { font-size: 28px; }h3 { font-size: 24px; }h4 { font-size: 20px; }h5 { font-size: 16px; }p, ul, ol {font-size: 16px;font-weight: normal;margin-bottom: 20px; }.container {display: block !important;clear: both !important; margin: 0 auto !important;max-width: 580px !important;}.container table {width: 100% !important;border-collapse: collapse; }.container .masthead {background: #DBFFBF;color: white;}.container .masthead h1 {margin: 0 auto !important;max-width: 90%;text-transform: uppercase;}.container .content {background: white;padding: 30px 35px;}.container .content.footer {background: none;}.container .content.footer p {margin-bottom: 0; color: #888;text-align: center;font-size: 14px;}.container .content.footer a {color: #888;text-decoration: none;font-weight: bold;}.titulo{font-weight: 700;}.subtitulo{font-weight: 250;}.tg{border-collapse:collapse;border-spacing:0;border-color:#bbb;}.tg td{font-family:Arial, sans-serif;font-size:14px;padding:10px 5px;border-style:solid;border-width:0px;overflow:hidden;word-break:normal;border-color:#bbb;color:#594F4F;background-color:#E0FFEB;border-top-width:1px;border-bottom-width:1px;}.tg th{font-family:Arial, sans-serif;font-size:14px;font-weight:normal;padding:10px 5px;border-style:solid;border-width:0px;overflow:hidden;word-break:normal;border-color:#bbb;color:#493F3F;background-color:#9DE0AD;border-top-width:1px;border-bottom-width:1px;} p.MsoNormal, li.MsoNormal, div.MsoNormal {margin: 0cm;margin-bottom: .0001pt;font-size: 11.0pt;font-family: 'Calibri',sans-serif;}";
  71. EMAIL_BODY += "</style>";
  72. EMAIL_BODY += "</head>";
  73. EMAIL_BODY += "<body>";
  74.  
  75. //colocar mensagem no body se for obra ou factura
  76. if (tarefa.Equals("Novas Obras"))
  77. EMAIL_BODY += "Não foi registada qualquer obra no dia de hoje.<br/>";
  78. else
  79. EMAIL_BODY += "Não foi registada qualquer factura no dia de hoje.<br/>";
  80.  
  81. EMAIL_BODY += "<img src='http://goo.gl/08w2jp' />";
  82. EMAIL_BODY += "<p class='MsoNormal' style='text-autospace:none'>";
  83. EMAIL_BODY += "<span style='font-size:8.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#595959;'>";
  84. EMAIL_BODY += "Rua dos Pitancinhos, 67 – Palmeira - 706 Braga PORTUGAL</span><span style='color:black;'><o:p></o:p></span></p>";
  85. EMAIL_BODY += "<p class='MsoNormal' style='text-autospace:none'>";
  86. EMAIL_BODY += "<span style='font-size:8.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#595959;'>";
  87. EMAIL_BODY += "T. (+351) 253 607 360 | M. (+351) 964 643 164</span><span style='color:black;'><o:p></o:p></span></p>";
  88. EMAIL_BODY+= "<p class='MsoNormal' style='text-autospace:none'><span style='font-size:8.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#595959;'>";
  89. EMAIL_BODY += "F. (+351) 253 607 362</span><span style='color:black;'><o:p></o:p></span></p>";
  90. EMAIL_BODY += "<p class='MsoNormal'><a href='mailto:bragalux@bragalux.pt'><span style='font-size:8.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#595959;'>";
  91. EMAIL_BODY += "bragalux@bragalux.pt</span></a><span style='font-size:8.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#595959;'>";
  92. EMAIL_BODY += " | </span><a href='http://www.bragalux.pt/'><b><span style='font-size:8.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#595959;'>";
  93. EMAIL_BODY += "www.bragalux.pt</span></b></a><span style='color:#595959;'><o:p></o:p></span></p>";
  94. EMAIL_BODY += "</body>";
  95. EMAIL_BODY += "</html>";
  96.  
  97. msg.IsBodyHtml = true;
  98. msg.Body = EMAIL_BODY;
  99. msg.BodyEncoding = Encoding.UTF8;
  100.  
  101.  
  102.  
  103. if (tarefa.Equals("Novas Obras"))
  104. msg.Subject = "Alertas Bragalux - Não existem obras novas registadas";
  105. else
  106. msg.Subject = "Alertas Bragalux - Não existem facturas novas registadas";
  107.  
  108. msg.SubjectEncoding = Encoding.UTF8;
  109. msg.Priority = MailPriority.High;
  110. msg.Bcc.Add(EMAIL_MIGUEL);
  111.  
  112.  
  113. sc.Send(msg);
  114.  
  115. sc.Dispose();
  116.  
  117. }
  118. catch (Exception ex)
  119. {
  120. Console.WriteLine(ex.Message);
  121. Decisor.Core.Tools.dcsUtils.LogError(ex.Message);
  122. }
  123. }
  124. else
  125. {
  126. try
  127. {
  128. string EMAIL_SUBJECT = "Alertas Bragalux - Registo de " + tarefa;
  129.  
  130. SmtpClient sc = new SmtpClient(CLIENT_HOST, CLIENT_PORT);
  131. sc.Credentials = new System.Net.NetworkCredential(CLIENT_EMAIL, CLIENT_PASSWORD);
  132. sc.DeliveryMethod = SmtpDeliveryMethod.Network;
  133. sc.EnableSsl = false;
  134.  
  135.  
  136. MailMessage msg;
  137. string EMAIL_BODY_HEADER = "<html>";
  138. EMAIL_BODY_HEADER += "<head>";
  139. EMAIL_BODY_HEADER += "<meta http-equiv='Content-Type' content='text/html; charset=utf-8' />";
  140. EMAIL_BODY_HEADER += "<meta name='viewport' content='width=device-width'/>";
  141. EMAIL_BODY_HEADER += "<style type='text/css'>";
  142. EMAIL_BODY_HEADER += " .table { font-size:10px; width: auto; border-collapse: collapse ; }";
  143. EMAIL_BODY_HEADER += " .td_header { background: #4983B9; width:auto; border: solid #9BC2E6 1.0pt; padding: 0cm 3.5pt cm 3.5pt; height: 15.0pt; color:#FFFFFF; text-align:left; vertical-align:top; }";
  144. EMAIL_BODY_HEADER += " .td_row1_facturas { background-color:smokewhite; border-top: none; border-left: solid #9BC2E6 1.0pt; border-bottom: solid #9BC2E6 1.0pt; border-right: solid #9BC2E6 1.0pt; padding: 0cm 3.5pt 0cm 3.5pt; height: 45.0pt; color:#333333; vertical-align:top; } ";
  145. EMAIL_BODY_HEADER += " .td_row2_facturas { background-color:#FFFFFF; border-top: none; border-left: solid #9BC2E6 1.0pt; border-bottom: solid #9BC2E6 1.0pt; border-right: solid #9BC2E6 1.0pt; padding: 0cm 3.5pt 0cm 3.5pt; height: 45.0pt; color:#333333; vertical-align:top; } ";
  146. EMAIL_BODY_HEADER += " .td_row1_obras { background-color:#FFFFFF; border-top: none; border-left: solid #9BC2E6 1.0pt; border-bottom: solid #9BC2E6 1.0pt; border-right: solid #9BC2E6 1.0pt; padding: 0cm 3.5pt 0cm 3.5pt; height: 45.0pt; color:#333333; vertical-align:top; } ";
  147. EMAIL_BODY_HEADER += " .td_row2_obras { background-color:#D4F7E2; border-top: none; border-left: solid #9BC2E6 1.0pt; border-bottom: solid #9BC2E6 1.0pt; border-right: solid #9BC2E6 1.0pt; padding: 0cm 3.5pt 0cm 3.5pt; height: 45.0pt; color:#333333; vertical-align:top; } ";
  148. EMAIL_BODY_HEADER += " .td_plus { width:100px;}";
  149. EMAIL_BODY_HEADER += " .plus { height:5px;font-size:10px; }";
  150. EMAIL_BODY_HEADER += "</style>";
  151. EMAIL_BODY_HEADER += "</head>";
  152. EMAIL_BODY_HEADER += "<body>";
  153. EMAIL_BODY_HEADER += "<h2 style='color:#F5D44C;'>Registo de " + tarefa + "</h2>";
  154.  
  155.  
  156. string EMAIL_BODY_TABELA_HEADER = "<table border='0' cellspacing='0' cellpadding='0' width='1049' class='table'>";
  157. EMAIL_BODY_TABELA_HEADER += "<tbody>";
  158.  
  159.  
  160. string EMAIL_BODY_TABELA_CONTENT = html;//local do conteudo principal do email
  161.  
  162.  
  163. string EMAIL_BODY_TABELA_FOOTER = "</tbody>";
  164. EMAIL_BODY_TABELA_FOOTER += "</table>";
  165.  
  166.  
  167.  
  168.  
  169.  
  170. string EMAIL_BODY_FOOTER = "<p style='text-autospace:none'>";
  171. EMAIL_BODY_FOOTER += "<img src='http://goo.gl/08w2jp' />";
  172. EMAIL_BODY_FOOTER += "<span style='font-size:8.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#595959;'>";
  173. EMAIL_BODY_FOOTER += "Rua dos Pitancinhos, 67 – Palmeira - 706 Braga PORTUGAL</span><span style='color:black;'><o:p></o:p></span></p>";
  174. EMAIL_BODY_FOOTER += "<p class='MsoNormal' style='text-autospace:none'>";
  175. EMAIL_BODY_FOOTER += "<span style='font-size:8.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#595959;'>";
  176. EMAIL_BODY_FOOTER += "T. (+351) 253 607 360 | M. (+351) 964 643 164</span><span style='color:black;'><o:p></o:p></span></p>";
  177. EMAIL_BODY_FOOTER += "<p class='MsoNormal' style='text-autospace:none'><span style='font-size:8.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#595959;'>";
  178. EMAIL_BODY_FOOTER += "F. (+351) 253 607 362</span><span style='color:black;'><o:p></o:p></span></p>";
  179. EMAIL_BODY_FOOTER += "<p class='MsoNormal'><a href='mailto:bragalux@bragalux.pt'><span style='font-size:8.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#595959;'>";
  180. EMAIL_BODY_FOOTER += "bragalux@bragalux.pt</span></a><span style='font-size:8.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#595959;'>";
  181. EMAIL_BODY_FOOTER += " | </span><a href='http://www.bragalux.pt/'><b><span style='font-size:8.0pt;font-family:&quot;Arial&quot;,sans-serif;color:#595959;'>";
  182. EMAIL_BODY_FOOTER += "www.bragalux.pt</span></b></a><span style='color:#595959;'><o:p></o:p></span></p>";
  183. EMAIL_BODY_FOOTER += "</body>";
  184. EMAIL_BODY_FOOTER += "</html>";
  185.  
  186.  
  187. if (tarefa.Equals("Novas Obras"))
  188. {
  189. EMAIL_BODY_TABELA_HEADER += "<tr style='height: 45pt'>";
  190. EMAIL_BODY_TABELA_HEADER += "<td nowrap='nowrap' class='td_header'><p><b><span>Data Registo</span></b></p></td>";
  191. EMAIL_BODY_TABELA_HEADER += "<td nowrap='nowrap' class='td_header'><p><b><span>Código Obra</span></b></p></td>";
  192. EMAIL_BODY_TABELA_HEADER += "<td nowrap='nowrap' class='td_header'><p><b><span>Código Obra Pai</span></b></p></td>";
  193. EMAIL_BODY_TABELA_HEADER += "<td nowrap='nowrap' class='td_header'><p><b><span>Nome Pessoa</span></b></p></td>";
  194. EMAIL_BODY_TABELA_HEADER += "<td nowrap='nowrap' class='td_header'><p><b><span>Valor Encomendado</span></b></p></td>";
  195. EMAIL_BODY_TABELA_HEADER += "</tr>";
  196.  
  197.  
  198.  
  199.  
  200. string BODY = EMAIL_BODY_HEADER +
  201. EMAIL_BODY_TABELA_HEADER +
  202. EMAIL_BODY_TABELA_CONTENT +
  203. EMAIL_BODY_TABELA_FOOTER +
  204. EMAIL_BODY_FOOTER;
  205.  
  206. msg = new MailMessage(EMAIL_FROM, EMAIL_GERMANO);
  207. msg.IsBodyHtml = true;
  208. msg.Body = BODY;
  209. msg.BodyEncoding = Encoding.UTF8;
  210. msg.SubjectEncoding = Encoding.UTF8;
  211. msg.Subject = EMAIL_SUBJECT;
  212. msg.Priority = MailPriority.High;
  213. msg.Bcc.Add(EMAIL_JOAO);
  214. msg.Bcc.Add(EMAIL_MIGUEL);
  215.  
  216. AlterarEstadoObra(user);
  217.  
  218. sc.Send(msg);
  219.  
  220.  
  221. }
  222. else
  223. {
  224. if (tarefa.Equals("Novas Facturas"))
  225. {
  226. EMAIL_BODY_TABELA_HEADER += "<tr style='height: 45pt'>";
  227. EMAIL_BODY_TABELA_HEADER += "<td nowrap='nowrap' class='td_header'><p><b><span>Ano Factura</span></b></p></td>";
  228. EMAIL_BODY_TABELA_HEADER += "<td nowrap='nowrap' class='td_header'><p><b><span>Data Factura</span></b></p></td>";
  229. EMAIL_BODY_TABELA_HEADER += "<td nowrap='nowrap' class='td_header'><p><b><span>Código Factura</span></b></p></td>";
  230. EMAIL_BODY_TABELA_HEADER += "<td nowrap='nowrap' class='td_header'><p><b><span>Código Afetação</span></b></p></td>";
  231. EMAIL_BODY_TABELA_HEADER += "<td nowrap='nowrap' class='td_header'><p><b><span>Nome Pessoa</span></b></p></td>";
  232. EMAIL_BODY_TABELA_HEADER += "<td nowrap='nowrap' class='td_header'><p><b><span>Total Líquido</span></b></p></td>";
  233. EMAIL_BODY_TABELA_HEADER += "<td nowrap='nowrap' class='td_header'><p><b><span>Descrição Produto</span></b></p></td>";
  234. EMAIL_BODY_TABELA_HEADER += "<td nowrap='nowrap' class='td_header'><p><b><span>Valor Líquido</span></b></p></td>";
  235. EMAIL_BODY_TABELA_HEADER += "</tr>";
  236.  
  237.  
  238. string BODY = EMAIL_BODY_HEADER +
  239. EMAIL_BODY_TABELA_HEADER +
  240. EMAIL_BODY_TABELA_CONTENT +
  241. EMAIL_BODY_TABELA_FOOTER +
  242. EMAIL_BODY_FOOTER;
  243.  
  244. msg = new MailMessage(EMAIL_FROM, EMAIL_GERMANO);
  245. msg.IsBodyHtml = true;
  246. msg.Body = BODY;
  247. msg.BodyEncoding = Encoding.UTF8;
  248. msg.SubjectEncoding = Encoding.UTF8;
  249. msg.Subject = EMAIL_SUBJECT;
  250. msg.Priority = MailPriority.High;
  251. msg.Bcc.Add(EMAIL_JOAO);
  252. msg.Bcc.Add(EMAIL_MIGUEL);
  253.  
  254. AlterarEstadoFactura(user);
  255. sc.Send(msg);
  256. }
  257. }
  258. dcsFieldInfoLst dcsField = new dcsFieldInfoLst();
  259. dcsField.SetField("DtRegisto", DateTime.Now);
  260. dcsField.SetField("Report", "Foi enviado um novo email.");
  261. long lIdRet = 0;
  262. user.ExecInsert(dcsField, "SmtpEmailReport", false, ref lIdRet);
  263.  
  264.  
  265. sc.Dispose();
  266. }
  267. catch (Exception ex)
  268. {
  269. Console.WriteLine(ex.Message);
  270. Decisor.Core.Tools.dcsUtils.LogError(ex.Message);
  271. }
  272. }
  273. }
  274.  
  275.  
  276.  
  277. private bool AlterarEstadoObra(Decisor.Core.DB.dcsDB user)
  278. {
  279. dcsFieldInfoLst dcsField = new dcsFieldInfoLst();
  280. dcsField.SetField("EstadoEmail", dcsDB.GetString("Enviado"));
  281. dcsField.SetField("EstadoEmail2", dcsDB.GetString("Criado"));
  282. if (user.ExecSQL("UPDATE Act1ObrasEmail SET EstadoEmail = ? WHERE EstadoEmail = ?", dcsField))
  283. return true;
  284.  
  285. return false;
  286. }
  287.  
  288.  
  289.  
  290. private bool AlterarEstadoFactura(Decisor.Core.DB.dcsDB user)
  291. {
  292. dcsFieldInfoLst dcsField = new dcsFieldInfoLst();
  293. dcsField.SetField("EstadoEmail", dcsDB.GetString("Enviado"));
  294. dcsField.SetField("EstadoEmail2", dcsDB.GetString("Criado"));
  295. if (user.ExecSQL("UPDATE Fnc1FCEmail SET EstadoEmail=? WHERE EstadoEmail=? ", dcsField))
  296. return true;
  297.  
  298. return false;
  299. }
  300.  
  301.  
  302.  
  303. public void VerificaNovasObras(Decisor.Core.DB.dcsDB user)
  304. {
  305. dcsFieldInfoLst dcsField = new dcsFieldInfoLst();
  306. dcsField.SetField("YEAR(DtRegisto)", DateTime.Now.Year);
  307. dcsField.SetField("MONTH(DtRegisto)", DateTime.Now.Month);
  308. dcsField.SetField("IdPessoa", 4);
  309.  
  310. DataTable dtObras = null;
  311.  
  312. StringBuilder sql = new StringBuilder();
  313. sql.Append(" SELECT IdObra, DtRegisto, CdObra, CdObraPai, NmPessoa, DsObra,VlrEncomendado");
  314. sql.Append(" FROM Act1Obras");
  315. sql.Append(" WHERE YEAR(DtRegisto) = ?");
  316. sql.Append(" AND MONTH(DtRegisto) = ?");
  317. sql.Append(" AND IdPessoa <> ?");
  318.  
  319.  
  320. //Pesquisa todas as obras na tabela 'Act1Obras'
  321. if (user.OpenDataTable(ref dtObras, dcsDB.GetString(sql), "T", dcsField))
  322. {
  323. for (int i = 0; i < dtObras.Rows.Count; i++)
  324. {
  325. dcsField.Clear();
  326.  
  327. long ret = 0;
  328. DataTable dtAux = null;
  329. //verifica se a obra já existe na tabela de emails
  330. dcsField.SetField("IdObra", dcsDB.GetString(dtObras.Rows[i]["IdObra"]));
  331.  
  332. user.OpenDataTable(ref dtAux, "SELECT * FROM Act1ObrasEmail WHERE IdObra = ?", "T", dcsField);
  333. if (dtAux.Rows.Count == 0)
  334. {
  335. dcsField.Clear();
  336. dcsField.SetField("IdObra", dcsDB.GetString(dtObras.Rows[i]["IdObra"]));
  337. dcsField.SetField("EstadoEmail", "Criado");
  338. dcsField.SetField("DtRegisto", dcsDB.GetDateStamp(dtObras.Rows[i]["DtRegisto"]));
  339. dcsField.SetField("CdObra", dcsDB.GetString(dtObras.Rows[i]["CdObra"]));
  340. dcsField.SetField("CdObraPai", dcsDB.GetString(dtObras.Rows[i]["CdObraPai"]));
  341. dcsField.SetField("NmPessoa", dcsDB.GetString(dtObras.Rows[i]["NmPessoa"]));
  342. dcsField.SetField("VlrEncomendado", dcsDB.GetSingle(dtObras.Rows[i]["VlrEncomendado"]));
  343.  
  344. user.ExecInsert(dcsField, "Act1ObrasEmail", false, ref ret);
  345. }
  346. }
  347. }
  348.  
  349. //Pesquisar todos as linhas da tabela 'Act1ObrasEmail' com o EstadoEmail = 'Criado' e enviar os dados de cada linha para o email
  350. DataTable dtmail = null;
  351. string ObrasHTML = "";
  352. dcsField.Clear();
  353. dcsField.SetField("EstadoEmail", "Criado");
  354. user.OpenDataTable(ref dtmail, "SELECT * FROM Act1ObrasEmail WHERE EstadoEmail = ? ORDER BY DtRegisto DESC", "T", dcsField);
  355. for (int i = 0; i < dtmail.Rows.Count; i++)
  356. {
  357. ObrasHTML += "<tr style='height: 45pt'>";
  358. ObrasHTML += " <td width='71' nowrap='nowrap' class='td_row1_obras'><p><span>" + dcsDB.GetString(dtmail.Rows[i]["DtRegisto"]) + "</span></p></td>";
  359. ObrasHTML += " <td width='71' nowrap='nowrap' class='td_row1_obras'><p><span>" + dcsDB.GetString(dtmail.Rows[i]["CdObra"]) + "</span></p></td>";
  360. ObrasHTML += " <td width='71' nowrap='nowrap' class='td_row1_obras'><p><span>" + dcsDB.GetString(dtmail.Rows[i]["CdObraPai"]) + "</span></p></td>";
  361. ObrasHTML += " <td width='71' nowrap='nowrap' class='td_row1_obras'><p><span>" + dcsDB.GetString(dtmail.Rows[i]["NmPessoa"]) + "</span></p></td>";
  362. ObrasHTML += " <td width='71' nowrap='nowrap' class='td_row1_obras'><p><span>" + dcsDB.GetString(dtmail.Rows[i]["VlrEncomendado"]) + "</span></p></td>";
  363. ObrasHTML += "</tr>";
  364.  
  365. i++;
  366.  
  367.  
  368. if (i != dtmail.Rows.Count)
  369. {
  370. ObrasHTML += "<tr style='height: 45pt'>";
  371. ObrasHTML += " <td width='71' nowrap='nowrap' class='td_row2_obras'><p><span>" + dcsDB.GetString(dtmail.Rows[i]["DtRegisto"]) + "</span></p></td>";
  372. ObrasHTML += " <td width='71' nowrap='nowrap' class='td_row2_obras'><p><span>" + dcsDB.GetString(dtmail.Rows[i]["CdObra"]) + "</span></p></td>";
  373. ObrasHTML += " <td width='71' nowrap='nowrap' class='td_row2_obras'><p><span>" + dcsDB.GetString(dtmail.Rows[i]["CdObraPai"]) + "</span></p></td>";
  374. ObrasHTML += " <td width='71' nowrap='nowrap' class='td_row2_obras'><p><span>" + dcsDB.GetString(dtmail.Rows[i]["NmPessoa"]) + "</span></p></td>";
  375. ObrasHTML += " <td width='71' nowrap='nowrap' class='td_row2_obras'><p><span>" + dcsDB.GetString(dtmail.Rows[i]["VlrEncomendado"]) + "</span></p></td>";
  376. ObrasHTML += "</tr>";
  377. }
  378. }
  379.  
  380. //Enviar os dados para o função do email smtp
  381. if (dtmail.Rows.Count > 0)
  382. EnviarEmail("Novas Obras", ObrasHTML, user);
  383. else
  384. EnviarEmail("Novas Obras", "", user);
  385. }
  386.  
  387.  
  388.  
  389.  
  390. public void VerificaNovasFacturas(Decisor.Core.DB.dcsDB user)
  391. {
  392. dcsFieldInfoLst dcsField = new dcsFieldInfoLst();
  393. dcsField.SetField("IdPessoa", 4);
  394. dcsField.SetField("CdFactura", dcsDB.GetString("NÃO EMITIDA"));
  395. dcsField.SetField("YEAR(DtFactura)", DateTime.Now.Year);
  396. dcsField.SetField("MONTH(DtFactura)", DateTime.Now.Month);
  397.  
  398. DataTable dtFacturas = null;
  399.  
  400. StringBuilder sqlFC = new StringBuilder();
  401. sqlFC.Append(" SELECT Fnc1FC.IdFactura,Fnc1FC.AnoFactura,Fnc1FC.DtFactura,Fnc1FC.CdFactura,Fnc1FC.CdCntCustos,Fnc1FC.NmPessoa,Fnc1FC.TtIliquido,");
  402. sqlFC.Append(" Fnc1FCLn.DsProduto,Fnc1FCLn.VlrIliquidoCrr");
  403. sqlFC.Append(" FROM Fnc1FC,Fnc1FCLn");
  404. sqlFC.Append(" WHERE Fnc1FC.IdFactura = Fnc1FCLn.IdFactura");
  405. sqlFC.Append(" AND Fnc1FC.IdPessoa <> ?");
  406. sqlFC.Append(" AND Fnc1FC.CdFactura <> ?");
  407. sqlFC.Append(" AND YEAR(DtFactura) = ?");
  408. sqlFC.Append(" AND MONTH(DtFactura) = ?");
  409.  
  410.  
  411.  
  412. //Pesquisa todoas as facturas com o join de 'Fnc1FC' com 'Fnc1FCLn' com filtragem por CdPessoa=0001 e CdFactura<>'Não Emitida'
  413. if (user.OpenDataTable(ref dtFacturas, dcsDB.GetString(sqlFC), "T", dcsField))
  414. {
  415. for (int i = 0; i < dtFacturas.Rows.Count; i++)
  416. {
  417. dcsField.Clear();
  418. dcsField.SetField("IdFactura", dcsDB.GetLong(dtFacturas.Rows[i]["IdFactura"]));
  419. dcsField.SetField("CdProduto", dcsDB.GetString(dtFacturas.Rows[i]["DsProduto"]));
  420.  
  421. //verifica se a factura já existe na tabela de emails
  422. long ret = 0;
  423. string IdFactura = dcsDB.GetString(dtFacturas.Rows[i]["IdFactura"]);
  424.  
  425. DataTable dtAux = null;
  426.  
  427. user.OpenDataTable(ref dtAux, "SELECT * FROM Fnc1FCEmail WHERE IdFactura = ? AND DsProduto = ?", "T", dcsField);
  428. if (dtAux.Rows.Count == 0)
  429. {
  430. dcsField.Clear();
  431. dcsField.SetField("IdFactura", dcsDB.GetLong(dtFacturas.Rows[i]["IdFactura"]));
  432. dcsField.SetField("EstadoEmail", "Criado");
  433. dcsField.SetField("AnoFactura", dcsDB.GetLong(dtFacturas.Rows[i]["AnoFactura"]));
  434. dcsField.SetField("DtFactura", dcsDB.GetDateStamp(dtFacturas.Rows[i]["DtFactura"]));
  435. dcsField.SetField("CdFactura", dcsDB.GetString(dtFacturas.Rows[i]["CdFactura"]));
  436. dcsField.SetField("CdCntCustos", dcsDB.GetString(dtFacturas.Rows[i]["CdCntCustos"]));
  437. dcsField.SetField("NmPessoa", dcsDB.GetString(dtFacturas.Rows[i]["NmPessoa"]));
  438. dcsField.SetField("TtIliquido", dcsDB.GetDouble(dtFacturas.Rows[i]["TtIliquido"]));
  439. dcsField.SetField("DsProduto", dcsDB.GetString(dtFacturas.Rows[i]["DsProduto"]));
  440. dcsField.SetField("VlrIliquidoCrr", dcsDB.GetDouble(dtFacturas.Rows[i]["VlrIliquidoCrr"]));
  441.  
  442. user.ExecInsert(dcsField, "Fnc1FCEmail", false, ref ret);
  443. }
  444. }
  445. }
  446.  
  447. //Pesquisar todos as linhas da tabela 'Fnc1FCEmail' com o EstadoEmail = 'Criado' e enviar os dados de cada linha para o email
  448. DataTable dtmail = null;
  449. StringBuilder FacturasHTML = new StringBuilder();
  450. dcsField.Clear();
  451. dcsField.SetField("EstadoEmail", "Criado");
  452. user.OpenDataTable(ref dtmail, "SELECT * FROM Fnc1FCEmail WHERE EstadoEmail = ? ORDER BY IdFactura DESC", "T", dcsField);
  453.  
  454. for (int i = 0; i < dtmail.Rows.Count; i++)
  455. {
  456. dcsFieldInfoLst dcsField2 = new dcsFieldInfoLst();
  457. dcsField2.SetField("IdFactura", dcsDB.GetLong(dtmail.Rows[i]["IdFactura"]));
  458. dcsField2.SetField("EstadoEmail", "Criado");
  459.  
  460. long total = dcsDB.GetLong(user.Dlookup("COUNT(*)", "Fnc1FCEmail", "IdFactura = ? AND EstadoEmail = ?", dcsField2));
  461.  
  462. if (total > 1)
  463. {
  464. DataTable dt2 = null;
  465. dcsFieldInfoLst dcsField3 = new dcsFieldInfoLst();
  466. dcsField3.SetField("EstadoEmail", "Criado");
  467. dcsField3.SetField("IdFactura", dcsDB.GetLong(dtmail.Rows[i]["IdFactura"]));
  468.  
  469. user.OpenDataTable(ref dt2, "SELECT * FROM Fnc1FCEmail WHERE EstadoEmail = ? AND IdFactura = ?", "TD2", dcsField3);
  470. FacturasHTML.Append("<tr style='height: 45pt'>");
  471. FacturasHTML.Append(" <td width='71' nowrap='nowrap' class='td_row1_facturas'><p><span>" + dcsDB.GetString(dtmail.Rows[i]["AnoFactura"]) + "</span></p></td>");
  472. FacturasHTML.Append(" <td width='71' nowrap='nowrap' class='td_row1_facturas'><p><span>" + dcsDB.GetString(dtmail.Rows[i]["DtFactura"]) + "</span></p></td>");
  473. FacturasHTML.Append(" <td width='71' nowrap='nowrap' class='td_row1_facturas'><p><span>" + dcsDB.GetString(dtmail.Rows[i]["CdFactura"]) + "</span></p></td>");
  474. FacturasHTML.Append(" <td width='71' nowrap='nowrap' class='td_row1_facturas'><p><span>" + dcsDB.GetString(dtmail.Rows[i]["CdCntCustos"]) + "</span></p></td>");
  475. FacturasHTML.Append(" <td width='71' nowrap='nowrap' class='td_row1_facturas'><p><span>" + dcsDB.GetString(dtmail.Rows[i]["NmPessoa"]) + "</span></p></td>");
  476. FacturasHTML.Append(" <td width='71' nowrap='nowrap' class='td_row1_facturas'><p><span>" + dcsDB.GetString(dtmail.Rows[i]["TtIliquido"]) + "</span></p></td>");
  477.  
  478.  
  479.  
  480. //O DsProduto pode ser repetido para a mesma factura
  481. FacturasHTML.Append(" <td nowrap='nowrap' class='td_row1_facturas td_plus'>");
  482. for (int j = 0; j < dt2.Rows.Count; j++)
  483. FacturasHTML.Append("<p class='plus'><span>" + dcsDB.GetString(dtmail.Rows[j]["DsProduto"]) + "</span></p>");
  484. FacturasHTML.Append(" </td>");
  485.  
  486.  
  487.  
  488. //O VlrIliquidoCrr pode ser repetido para a mesma factura
  489. FacturasHTML.Append(" <td nowrap='nowrap' class='td_row1_facturas td_plus'>");
  490. for (int j = 0; j < dt2.Rows.Count; j++)
  491. FacturasHTML.Append("<p class='plus'><span>" + dcsDB.GetString(dtmail.Rows[j]["VlrIliquidoCrr"]) + "</span></p>");
  492. FacturasHTML.Append(" </td>");
  493.  
  494.  
  495. i += (int)total;
  496. FacturasHTML.Append("</tr>");
  497. }
  498. else
  499. {
  500. FacturasHTML.Append("<tr style='height: 45pt'>");
  501. FacturasHTML.Append(" <td width='71' nowrap='nowrap' class='td_row1_facturas'><p><span>" + dcsDB.GetString(dtmail.Rows[i]["AnoFactura"]) + "</span></p></td>");
  502. FacturasHTML.Append(" <td width='71' nowrap='nowrap' class='td_row1_facturas'><p><span>" + dcsDB.GetString(dtmail.Rows[i]["DtFactura"]) + "</span></p></td>");
  503. FacturasHTML.Append(" <td width='71' nowrap='nowrap' class='td_row1_facturas'><p><span>" + dcsDB.GetString(dtmail.Rows[i]["CdFactura"]) + "</span></p></td>");
  504. FacturasHTML.Append(" <td width='71' nowrap='nowrap' class='td_row1_facturas'><p><span>" + dcsDB.GetString(dtmail.Rows[i]["CdCntCustos"]) + "</span></p></td>");
  505. FacturasHTML.Append(" <td width='71' nowrap='nowrap' class='td_row1_facturas'><p><span>" + dcsDB.GetString(dtmail.Rows[i]["NmPessoa"]) + "</span></p></td>");
  506. FacturasHTML.Append(" <td width='71' nowrap='nowrap' class='td_row1_facturas'><p><span>" + dcsDB.GetString(dtmail.Rows[i]["TtIliquido"]) + "</span></p></td>");
  507. FacturasHTML.Append(" <td width='71' nowrap='nowrap' class='td_row1_facturas'><p><span>" + dcsDB.GetString(dtmail.Rows[i]["DsProduto"]) + "</span></p></td>");
  508. FacturasHTML.Append(" <td width='71' nowrap='nowrap' class='td_row1_facturas'><p><span>" + dcsDB.GetString(dtmail.Rows[i]["VlrIliquidoCrr"]) + "</span></p></td>");
  509. FacturasHTML.Append("</tr>");
  510.  
  511.  
  512. }
  513. }
  514.  
  515. //Enviar os dados para o função do email smtp
  516. if (dtmail.Rows.Count > 0)
  517. EnviarEmail("Novas Facturas", dcsDB.GetString(FacturasHTML), user);
  518. else
  519. EnviarEmail("Novas Facturas", "", user);
  520.  
  521. }
  522.  
  523.  
  524.  
  525. }
  526. }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!