API tools faq
paste
Guest User

Untitled

a guest
Dec 5th, 2017
365
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 14.44 KB | None | 0 0
raw download clone embed print report
  1. Dataset is something like this:
  2. DocumentNo |SiteID| CltInvNumber | InvDate | ... | GrossAmount | TotalAmt
  3. BKRA24112015|3060 | BKLF152 |24-11-2015| ... | 128.448 | 128.448
  4. FTRA24112015|3062 | FTCB274 |24-11-2015| ... | 200 |
  5. FTRA24112015|3062 | FTCB275 |24-11-2015| ... | 4 |
  6. FTRA24112015|3062 | FTCB276 |24-11-2015| ... | 12 |
  7. FTRA24112015|3062 | FTLF163 |24-11-2015| ... | 2372.892 | 2588.89
  8. HYRA24112015|3061 | HYCB66 |24-11-2015| ... | 12 |
  9. HYRA24112015|3061 | HYLF142 |24-11-2015| ... | 0.012 | 12.012
  10.  
  11. private void GenerateDirectDebitStatement(string SiteId, string DocumentNo, List<string> ClientInvoiceNumber, string ShortInvoiceDate, string ShortDueDate, List<decimal> GrossAmount, string CustomerNo, string CustVATNo, string SiteName, string ClientName, string Address1, string Email, string AlternetEmail, string PaymentMethod, string SortCode, string Telephone, string AccountNo, string Currency, string TotalAmount)
  12. {
  13. //DataSet dt = this.GetSummaryData();
  14. using (StringWriter sw = new StringWriter())
  15. {
  16. using (HtmlTextWriter hw = new HtmlTextWriter(sw))
  17. {
  18. StringBuilder sb = new StringBuilder();
  19. sb.Append("<table width='100%' cellpadding='2'>");
  20. sb.Append("<tr><td align='center'><h2><u><b>DIRECT DEBIT STATEMENT</b></u></h2></td></tr>");
  21. sb.Append("<tr><td colspan = '2'></td></tr>");
  22. sb.Append("</table>");
  23. sb.Append("<br/>");
  24. sb.Append("<img src='logo.png' width='50' height='75'/>");
  25. sb.Append("<br/>");
  26. sb.Append("<p><b>PARK GARAGE GROUP PLC</b></p>");
  27. //sb.Append("<br/>");
  28. sb.Append("<table cellpadding ='0'>");
  29. //sb.Append("<tr><td></td><td></td><td></td></tr>");
  30. sb.Append("<tr><td><font size ='1'>Registered in England No. 34970229</font></td>");
  31. sb.Append("<td bgcolor='#C6C3C3' align ='right'><font size ='1'>Document No:</td><td bgcolor='#C6C3C3'> " + DocumentNo + "</font></td></tr>");
  32. sb.Append("<tr><td><font size ='1'>1-3 Station Approach</font></td>");
  33. sb.Append("<td align ='right'><font size ='1'></td><td></font></td></tr>");
  34. sb.Append("<tr><td><font size ='1'>Hayes, Kent</font></td>");
  35. sb.Append("<td align ='right'><font size ='1'>Customer No:</td><td> " + CustomerNo + "</font></td></tr>");
  36. sb.Append("<tr><td><font size ='1'>BR2 7EQ</font></td>");
  37. sb.Append("<td align ='right'><font size ='1'>Document Date:</td><td> " + ShortInvoiceDate + "</font></td></tr>");
  38. sb.Append("<tr><td><font size ='1'>VAT Registration No. 726 799969</font></td>");
  39. sb.Append("<td align ='right'><font size ='1'>Payment Amount:</td><td> " + TotalAmount + "</font></td></tr>");
  40. sb.Append("<tr><td><font size ='1'>Tel: 02083158340</font></td>");
  41. sb.Append("<td align ='right'><font size ='1'></td><td></font></td></tr>");
  42. sb.Append("<tr><td><font size ='1'>Fax: 02083158341</font></td>");
  43. sb.Append("<td align ='right'><font size ='1'></td><td></font></td></tr>");
  44. //sb.Append("<td align ='right'></td><td></td></tr>");
  45. sb.Append("</table>");
  46. sb.Append("<br />");
  47. sb.Append("<table width='100%' cellpadding='0'>");
  48. sb.Append("<tr><td width = '50%'><font size ='1'>To,</font></td>");
  49. sb.Append("<td align ='right'></td><td></td></tr>");
  50. sb.Append("<tr><td width='50%'><font size ='1'><b>" + SiteName + " [" + SiteId + "]</b></td>");
  51. sb.Append("<td bgcolor='#C6C3C3' align ='right' width='20%'>Value Date:</td><td bgcolor='#C6C3C3'> " + ShortDueDate + "</td></tr>");
  52. sb.Append("<tr><td><b>" + ClientName + "</b></td>");
  53. sb.Append("<td align ='right'>Payment Method:</td><td> " + PaymentMethod + "</td></tr>");
  54. sb.Append("<tr><td><b>" + Address1 + "</b></td>");
  55. sb.Append("<td align ='right'>Sort Code:</td><td> " + SortCode + "</td></tr>");
  56. sb.Append("<tr><td><b>" + AlternetEmail + "</b></td>");
  57. sb.Append("<td align ='right'>Account No:</td><td> " + AccountNo + "</td></tr>");
  58. sb.Append("<tr><td><b>Tel: " + Telephone + "</b></td>");
  59. sb.Append("<td align ='right'>Currency:</td><td> " + Currency + "</td></tr>");
  60. sb.Append("<tr><td></td>");
  61. sb.Append("<td align ='right'>Customer VAT No:</td><td> " + CustVATNo + "</td></tr>");
  62. sb.Append("</table>");
  63. sb.Append("<br />");
  64.  
  65. sb.Append("<p>Payment for the following item(s) will be credited to your bank account:</p>");
  66. sb.Append("<table width='100%' cellpadding='0'>");
  67. sb.Append("<tr><font><b><th>Document No</th><th>Document Date</th><th>Due Date</th><th>Gross Amount</th></b></font></tr>");
  68. //sb.Append("<tr align='center'><td height='400'>" + Itype + "</td><td> " + DescriptionItem1 + "</td><td> " + UnitPrice + " <td> " + VATPercent + "</td><td> 1 </td><td> " + VATAmount + " </td><td>" + NetAmount + "</td><td>" + GrossAmount + " </td></tr>");
  69. for(int i=0; i < ClientInvoiceNumber.Count; i++)
  70. {
  71. sb.Append("<tr>");
  72. sb.Append("<td>");
  73. sb.Append(ClientInvoiceNumber[i]);
  74. sb.Append("</td>");
  75. sb.Append("<td>");
  76. sb.Append(ShortInvoiceDate);
  77. sb.Append("</td>");
  78. sb.Append("<td>");
  79. sb.Append(ShortDueDate);
  80. sb.Append("</td>");
  81. sb.Append("<td>");
  82. sb.Append(GrossAmount[i]);
  83. sb.Append("</td>");
  84. sb.Append("</tr>");
  85. }
  86.  
  87. sb.Append("</table>");
  88. sb.Append("</div>");
  89. sb.Append("<br />");
  90.  
  91. sb.Append("<table width='100%' bgcolor='#C6C3C3' cellpadding='0'>");
  92. sb.Append("<tr><td align='right' width='90%'><b>Total Amount: </td><td width='10%' align='right'> " + TotalAmount + " </td></b></font></tr>"); //
  93. sb.Append("</table>");
  94. sb.Append("<br />");
  95.  
  96. sb.Append("<div>");
  97. sb.Append("<table>");
  98. sb.Append("<tr><td><p><font size= '1'><b><u>Terms and Conditions:</u></b> Park Garage Group Plc. If any of your details are <br />incorrect, please inform us at your earliest convenience. If no tax point date <br />exists then use the document date as tax point date. </p></td></tr>");
  99. sb.Append("</table>");
  100. sb.Append("</div>");
  101.  
  102. StringReader sr = new StringReader(sb.ToString());
  103. Document pdfDoc = new Document(PageSize.A4, 20f, 20f, 20f, 0f);
  104. HTMLWorker htmlparser = new HTMLWorker(pdfDoc);
  105. using (MemoryStream memoryStream = new MemoryStream())
  106. {
  107. PdfWriter writer = PdfWriter.GetInstance(pdfDoc, memoryStream);
  108. pdfDoc.Open();
  109. htmlparser.Parse(sr);
  110. pdfDoc.Close();
  111. byte[] bytes = memoryStream.ToArray();
  112. memoryStream.Close();
  113.  
  114. MailMessage mm = new MailMessage("site@pggplc.com", "naimeshjani@pggplc.com");
  115. mm.Subject = "Direct Debit Statement";
  116. mm.Body = "Direct Debit Statement Attached";
  117. mm.Attachments.Add(new Attachment(new MemoryStream(bytes), "DirectDebitStatement.pdf"));
  118. mm.IsBodyHtml = true;
  119. SmtpClient smtp = new SmtpClient("nexgen.simplyms.com");
  120. smtp.EnableSsl = true;
  121. NetworkCredential NetworkCred = new NetworkCredential();
  122. NetworkCred.UserName = "site@pggplc.com";
  123. NetworkCred.Password = "pggplc";
  124. smtp.UseDefaultCredentials = true;
  125. smtp.Credentials = NetworkCred;
  126. smtp.Send(mm);
  127. }
  128. }
  129. }
  130. }
  131.  
  132. public void GetSummaryData()
  133. {
  134. string conString = "Data Source=PGGPLC;Initial Catalog=Park;Integrated Security=True";
  135.  
  136. string query = "Select (a.ShortName+'RA'+REPLACE(CONVERT(CHAR(10),[InvoiceDate],103),'/','')) AS DocumentNo,a.SiteId, i.ClientInvoiceNumber, i.InvoiceDate, i.taxpointdate,"
  137. + " i.DueDate, i.PGGFileNo, "
  138. + " a.CustomerNo, a.CustVATNo, a.SiteName, a.ClientName, a.Address1, "
  139. + " a.Email, a.AlternetEmail, a.PaymentMethod, a.SortCode, a.AccountNo,"
  140. + " a.Currency, a.Telephone, "
  141. + " I.SiteId, I.DueDate, I.GrossAmount,"
  142. + " CASE WHEN I.ClientInvoiceNumber = (SELECT TOP 1 ClientInvoiceNumber "
  143. + " FROM [Park].[dbo].[IMS_ProcessedInvoicesAndSBTIs_test]"
  144. + " WHERE SiteId = I.SiteId and GrossAmount > 0"
  145. + " ORDER BY ClientInvoiceNumber desc) "
  146. + " THEN (SELECT CAST(SUM(GrossAmount) AS CHAR(10))"
  147. + " FROM [Park].[dbo].[IMS_ProcessedInvoicesAndSBTIs_test]"
  148. + " WHERE ClientInvoiceNumber <= I.ClientInvoiceNumber "
  149. + " AND SiteId = I.SiteId and GrossAmount > 0)"
  150. + " ELSE ' ' END AS 'TotalAmount'"
  151. + " FROM [Park].[dbo].[IMS_ProcessedInvoicesAndSBTIs_test] I, [Park].[dbo].[IMS_CommSiteMaster] a"
  152. + " WHERE I.SiteId = a.SiteId AND InvoiceGenerated = 'N' and GrossAmount > 0"
  153. + " ORDER BY ClientInvoiceNumber";
  154.  
  155. SqlConnection sqlConn = new SqlConnection(conString);
  156. try
  157. {
  158. sqlConn.Open();
  159. SqlCommand cmd = new SqlCommand(query, sqlConn);
  160. SqlDataAdapter sqlDa = new SqlDataAdapter();
  161. sqlDa.SelectCommand = cmd;
  162. DataSet ds = new DataSet();
  163. sqlDa.Fill(ds, "Summary");
  164. var count = ds.Tables[0].Rows.Count;
  165.  
  166. string documentNo= ""; string siteId= ""; string customerNo= ""; string custVATNo= ""; string siteName= ""; string clientName= ""; string address1= ""; string email= ""; string alternameEmail= ""; string paymentMethod= ""; string sortCode= ""; string telephone= ""; string accountNo= ""; string currency= ""; string totalAmount= ""; string ShortInvoiceDate= ""; string ShortDueDate= ""; string ShortTaxPointDate = "";
  167.  
  168. DateTime? InvoiceDate;
  169. DateTime? DueDate;
  170. DateTime? TaxPointDate;
  171. List<string> ClientInvoiceNumber = new List<string>();
  172. List<decimal> GrossAmount = new List<decimal>();
  173.  
  174. foreach (DataRow row in ds.Tables[0].Rows)
  175. {
  176. var currentDocumentNo = row["DocumentNo"].ToString();
  177.  
  178. if (documentNo == string.Empty)
  179. documentNo = currentDocumentNo;
  180.  
  181. if (documentNo == currentDocumentNo)
  182. {
  183. InvoiceDate = DateTime.Parse(row["InvoiceDate"].ToString());
  184. ShortInvoiceDate = InvoiceDate.Value.ToShortDateString();
  185. DueDate = DateTime.Parse(row["DueDate"].ToString());
  186. ShortDueDate = DueDate.Value.ToShortDateString();
  187. TaxPointDate = DateTime.Parse(row["TaxPointDate"].ToString());
  188. ShortTaxPointDate = TaxPointDate.Value.ToShortDateString();
  189. documentNo = row["DocumentNo"].ToString();
  190. siteId = row["SiteId"].ToString();
  191. customerNo = row["CustomerNo"].ToString();
  192. custVATNo = row["CustVATNo"].ToString();
  193. siteName = row["SiteName"].ToString();
  194. clientName = row["ClientName"].ToString();
  195. address1 = row["Address1"].ToString();
  196. email = row["Email"].ToString();
  197. alternameEmail = row["alternetEmail"].ToString();
  198. paymentMethod = row["PaymentMethod"].ToString();
  199. sortCode = row["SortCode"].ToString();
  200. telephone = row["Telephone"].ToString();
  201. accountNo = row["AccountNo"].ToString();
  202. currency = row["Currency"].ToString();
  203. totalAmount = row["TotalAmount"].ToString();
  204.  
  205. ClientInvoiceNumber.Add(row["ClientInvoiceNumber"].ToString());
  206. GrossAmount.Add(decimal.Parse(row["GrossAmount"].ToString()));
  207. }
  208. else
  209. {
  210. if (decimal.Parse(row["GrossAmount"].ToString()) > 0)
  211. {
  212. GenerateDirectDebitStatement(siteId, documentNo, ClientInvoiceNumber, ShortInvoiceDate, ShortDueDate, GrossAmount, customerNo, custVATNo, siteName, clientName, address1, email, alternameEmail, paymentMethod, sortCode, telephone, accountNo, currency, totalAmount);//(DateTime date, string firstRef, string secRef));
  213. documentNo = "";
  214. ClientInvoiceNumber.Clear();
  215. GrossAmount.Clear();
  216. }
  217. }
  218. }
  219. }
  220. catch (Exception ex)
  221. {
  222. lbErrMess.Text = "Error: " + ex.Message;
  223. }
  224. finally
  225. {
  226. sqlConn.Close();
  227. }
  228. }
  229.  
  230. if (documentNo == string.Empty)
  231. documentNo = currentDocumentNo;
  232.  
  233. if (documentNo == currentDocumentNo)
  234. {
  235. ....
Add Comment
Please, Sign In to add comment
Public Pastes
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!