public void listOfMandates() { try { SqlConnection cnSQL = new SqlConnection(MyModules.strConnect); SqlCommand cmSQL = new SqlCommand(); cmSQL.Connection = cnSQL; SqlDataReader drSQL = null; lvList.Items.Clear(); string str = "SELECT MandateNo, COUNT(RefNo) AS NoOfPayments, SUM(Amount) AS TheAmount,MAX([PayValueDate]) AS PayValueDate,Authorised,'Main' AS MandateType FROM Payment WHERE [PayValueDate]>='" + dtpStartDate.Text + "' AND [PayValueDate]<='" + dtpEndDate.Text + "' GROUP BY MandateNo,Authorised"; // ORDER BY [PayValueDate]"; str = str + " UNION SELECT MandateNo, COUNT(RefNo) AS NoOfPayments, SUM(Amount) AS TheAmount,MAX([PayValueDate]) AS PayValueDate,Authorised,MAX(MainAction) AS MandateType FROM PaymentDeductions WHERE [PayValueDate]>='" + dtpStartDate.Text + "' AND [PayValueDate]<='" + dtpEndDate.Text + "' GROUP BY MandateNo,Authorised ORDER BY [PayValueDate]"; cmSQL.CommandText = str; cmSQL.CommandType = CommandType.Text; cnSQL.Open(); string initialText = ""; ListViewItem LvItems = new ListViewItem(initialText); drSQL = cmSQL.ExecuteReader(); while (drSQL.Read()) { initialText = drSQL["MandateNo"].ToString(); LvItems = new ListViewItem(initialText); LvItems.SubItems.Add(MyModules.FormatDate(drSQL["PayValueDate"]).ToString()); // Convert.ToDateTime(drSQL["TheDate"]).ToString("dd-MMM-yyyy")); LvItems.SubItems.Add(drSQL["NoOfPayments"].ToString()); LvItems.SubItems.Add(MyModules.FormatDouble(drSQL["TheAmount"]).ToString()); LvItems.SubItems.Add(Convert.ToBoolean(drSQL["Authorised"]).ToString()); LvItems.SubItems.Add(drSQL["MandateType"].ToString()); lvList.Items.AddRange(new ListViewItem[] { LvItems }); } cmSQL.Connection.Close(); cmSQL.Dispose(); drSQL.Close(); cnSQL.Close(); cnSQL.Dispose(); } catch (Exception ex) { MessageBox.Show(ex.Message, MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information); } }
private void CreateHTML(string ClientCode, string theName) { try { //If Trim(strAccountNo) = "" Then Exit Sub // Create an instance of StreamWriter to write text to a file. string GetHTMFileName = Microsoft.VisualBasic.FileIO.FileSystem.GetTempFileName(); GetHTMFileName = GetHTMFileName.Substring(0, GetHTMFileName.Length - 3) + "apx"; using (StreamWriter sw = new StreamWriter(GetHTMFileName)) { sw.WriteLine("<html>"); sw.WriteLine("<head>"); sw.WriteLine("<meta http-equiv='Content-Type' content='text/html; charset=windows-1252'>"); sw.WriteLine("<title>PrimeScholar</title>"); sw.WriteLine("<style>"); sw.WriteLine("<!--"); sw.WriteLine("BODY { background: url('top-vb.gif') repeat-x; font-family: Verdana; font-size: 67% ; background-color:white } "); sw.WriteLine(".maindiv { background: url('side-vb.gif') repeat-y; padding-left: 30px; padding-top: 5px; position: relative; "); sw.WriteLine("height: 50px }"); sw.WriteLine("P { margin-top: 0; margin-bottom: 6px; line-height:130% }"); sw.WriteLine("H1 { margin-top: 20px; margin-bottom: 12px; font-size:190% }"); sw.WriteLine("H2 { color: #585F56; left: -55px; position: relative; margin-top: 21px; margin-bottom: 9px; font-size:170% "); sw.WriteLine("}"); sw.WriteLine("H3 { margin-top: 21px; margin-bottom: 9px; font-size: 140%; font-weight: bold}"); sw.WriteLine("H4 { margin-top: 18px; margin-bottom: 9px; font-size: 140%; font-weight: bold}"); sw.WriteLine("OL { margin-top: 0; margin-bottom: 9px; line-height:130%}"); sw.WriteLine("UL { margin-top: 0; margin-bottom: 9px; line-height:130%}"); sw.WriteLine("LI { margin-top: 0; margin-bottom: 6px }"); sw.WriteLine("BLOCKQUOTE { margin-left: 10px }"); sw.WriteLine("TABLE { padding: 4px; BACKGROUND: white; BORDER: #DDDCD6 1px solid; BORDER-COLLAPSE: collapse; margin-"); sw.WriteLine("bottom: 9px; }"); sw.WriteLine("TR { vertical-align: top} "); sw.WriteLine("TD { padding: 4px; font-family: Verdana; font-size: 67%; line-height: 130%} "); sw.WriteLine(".contents { line-height: 150% }"); sw.WriteLine("DIV.CodeBlock { font-family: 'Courier New'; font-size: 100%; margin-bottom: 6px; BACKGROUND: #f8f7ef; BORDER: "); sw.WriteLine("#eeede6 1px solid; padding: 5px; }"); sw.WriteLine(".CodeInline { font-family: 'Courier New' }"); sw.WriteLine(".ProcedureLabel {margin-top: 5px; font-style: italic; font-weight: bold; color: #0D4CC3 } "); sw.WriteLine(".FileNameCol { padding: 5px; BACKGROUND: seashell; width=200px; font-size: 60%; font-weight: bold}"); sw.WriteLine(".Col {padding: 5px; width=600px}"); sw.WriteLine("-->"); sw.WriteLine("</style>"); sw.WriteLine("</head>"); sw.WriteLine("<body topmargin='0' leftmargin='0' rightmargin='0'>"); sw.WriteLine("<div class='maindiv'>"); sw.WriteLine("<a name='top'>"); sw.WriteLine("<!-- MAIN CONTENT BEGINS -->"); SqlConnection cnSQL = new SqlConnection(MyModules.strConnect); SqlCommand cmSQL = new SqlCommand(); cmSQL.Connection = cnSQL; SqlDataReader drSQL = null; //if (RadSchool.Checked) //{ cmSQL.CommandText = "SELECT * FROM RegisterSchool WHERE Sn=" + ClientCode; //} //if (RadStudent.Checked) //{ // cmSQL.CommandText = "SELECT * FROM RegisterStudent WHERE Sn=" + ClientCode; //} //if (RadVendor.Checked) //{ // cmSQL.CommandText = "SELECT * FROM RegisterVendor WHERE Sn=" + ClientCode; //} //if (radStaff.Checked) //{ // cmSQL.CommandText = "SELECT * FROM RegisterVendor WHERE Sn=" + ClientCode; //} cmSQL.CommandType = CommandType.Text; cnSQL.Open(); drSQL = cmSQL.ExecuteReader(); int i = 0; if (drSQL.HasRows == false) { goto SkipIt; } if (drSQL.Read()) { sw.WriteLine("<h1>"); sw.WriteLine("<span style='font-size: 11pt'>BENEFICIARY INFORMATION</span><a name='top'>"); sw.WriteLine("</h1>"); sw.WriteLine("</a>"); sw.WriteLine("<table border='1' bordercolor= #DDDCD6 width='100%' style='border-collapse: collapse'>"); sw.WriteLine("<tr>"); for (i = 0; i < drSQL.FieldCount; i++) { sw.WriteLine("<tr>"); sw.WriteLine("<td class='FileNameCol'>" + drSQL.GetName(i) + "</td>"); if (drSQL[i].GetType().ToString() == "System.DateTime") { sw.WriteLine("<td class='Col'>" + MyModules.FormatDate(drSQL[i]) + "</td>"); } else if (drSQL[i].GetType().ToString() == "System.Decimal") { sw.WriteLine("<td class='Col'>" + MyModules.FormatDouble(drSQL[i]) + "</td>"); } else { sw.WriteLine("<td class='Col'>" + MyModules.ChkNull(drSQL[i]) + "</td>"); } sw.WriteLine("</tr>"); } } else { sw.WriteLine("<h1>"); sw.WriteLine("<span style='font-size: 11pt;color: #990000'>" + MyModules.sysOwner + "</span><a name='top'>"); sw.WriteLine("</h1>"); sw.WriteLine("<h1>"); sw.WriteLine("<span style='font-size: 11pt;color: #990000'>Details:</span><a name='top'>"); sw.WriteLine("</h1>"); sw.WriteLine("</a>"); sw.WriteLine("<table border='1' bordercolor= #DDDCD6 width='70%' style='border-collapse: collapse'>"); sw.WriteLine("<tr>"); } SkipIt: cmSQL.Connection.Close(); cmSQL.Dispose(); drSQL.Close(); cnSQL.Close(); cnSQL.Dispose(); sw.WriteLine("</table>"); sw.WriteLine("</p>"); sw.WriteLine("<h3>"); sw.WriteLine("<span style='font-size: 60%'>"); sw.WriteLine("<p> </p>"); sw.WriteLine("</div>"); sw.WriteLine("</body>"); sw.WriteLine("</html>"); sw.Close(); WebBrowser.Navigate(new Uri(GetHTMFileName)); } return; } catch (Exception ex) { MessageBox.Show(ex.Message, MyModules.strApptitle, MessageBoxButtons.OK, MessageBoxIcon.Information); } }