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);
}
}
