PageRenderTime 50ms CodeModel.GetById 19ms RepoModel.GetById 1ms app.codeStats 0ms

/BaliEnterpriseSystems/BaliEnterpriseSystems/StudentReports.aspx.cs

https://github.com/sirivedula/BEST
C# | 385 lines | 363 code | 21 blank | 1 comment | 39 complexity | 36bb0d3fa53d0d19e00054e4ef331cea MD5 | raw file
  1. using System;
  2. using System.Collections;
  3. using System.Configuration;
  4. using System.Data;
  5. using System.Linq;
  6. using System.Web;
  7. using System.Web.Security;
  8. using System.Web.UI;
  9. using System.Web.UI.WebControls;
  10. using System.Web.UI.WebControls.WebParts;
  11. using System.Web.UI.HtmlControls;
  12. using System.Xml.Linq;
  13. using BaliEnterpriseSystems.BestObjects;
  14. using System.Text;
  15. using System.Data.OleDb;
  16. namespace BaliEnterpriseSystems
  17. {
  18. public partial class StudentReports : System.Web.UI.Page
  19. {
  20. protected void Page_LoadComplete(object sender, EventArgs e)
  21. {
  22. if (HttpContext.Current.Session["CurrentUser"] == null)
  23. {
  24. Response.Redirect("Logout.aspx");
  25. }
  26. ltrSubMenu.Text = UtilMenu.StudentMenu("studentreports");
  27. if (!Utils.User.UserRoleByName("Student - Reports").allowView)
  28. {
  29. ltrGrid.Text = "You do not have rights to view.";
  30. return;
  31. }
  32. string ms = Request.QueryString["ms"];
  33. ltrMScript.Text = Utils.MenuSelectScript(ms);
  34. StringBuilder rowshtml = new StringBuilder();
  35. string fromdate = Request.Form["fromdate"];
  36. if (string.IsNullOrEmpty(fromdate)) fromdate = DateTime.Today.ToString("MM/dd/yyyy");
  37. string todate = Request.Form["todate"];
  38. if (string.IsNullOrEmpty(todate)) todate = DateTime.Today.AddMonths(1).AddDays(-1).ToString("MM/dd/yyyy");
  39. string summaryby = Request.Form["summaryby"];
  40. if (string.IsNullOrEmpty(summaryby)) summaryby = "";
  41. bool chkStudentName = (Request.Form["chkStudentName"] ?? "").Equals("on");
  42. bool chkParentName = (Request.Form["chkParentName"] ?? "").Equals("on");
  43. bool chkAddress = (Request.Form["chkAddress"]??"").Equals("on");
  44. bool chkTutorName = (Request.Form["chkTutorName"]??"").Equals("on");
  45. bool chkTotalHours = (Request.Form["chkTotalHours"]??"").Equals("on");
  46. bool chkTotalAmountPaid = (Request.Form["chkTotalAmountPaid"]??"").Equals("on");
  47. bool chkTotalAmountCharged = (Request.Form["chkTotalAmountCharged"]??"").Equals("on");
  48. bool chkAmountDue = (Request.Form["chkAmountDue"]??"").Equals("on");
  49. bool chkPayDate = (Request.Form["chkPayDate"]??"").Equals("on");
  50. bool chkProgramDesc = (Request.Form["chkProgramDesc"]??"").Equals("on");
  51. bool chkNotes = (Request.Form["chkNotes"] ?? "").Equals("on");
  52. if (!IsPostBack)
  53. {
  54. chkStudentName = true;
  55. chkTotalHours = true;
  56. chkTotalAmountPaid = true;
  57. chkTotalAmountCharged = true;
  58. chkAmountDue = true;
  59. chkPayDate = true;
  60. }
  61. if (IsPostBack)
  62. {
  63. bool canReport = true;
  64. if (!string.IsNullOrEmpty(fromdate))
  65. {
  66. try
  67. {
  68. DateTime fromdt = Convert.ToDateTime(fromdate);
  69. }
  70. catch (Exception ex)
  71. {
  72. ltrValidateMsg.Text = Utils.WarningMessage(ex.Message);
  73. canReport = false;
  74. }
  75. }
  76. else
  77. {
  78. ltrValidateMsg.Text = Utils.WarningMessage("From Date is Required.");
  79. canReport = false;
  80. }
  81. if (!string.IsNullOrEmpty(todate))
  82. {
  83. try
  84. {
  85. DateTime todt = Convert.ToDateTime(todate);
  86. }
  87. catch (Exception ex)
  88. {
  89. ltrValidateMsg.Text = Utils.WarningMessage(ex.Message);
  90. canReport = false;
  91. }
  92. }
  93. else
  94. {
  95. ltrValidateMsg.Text = Utils.WarningMessage("To Date is Required.");
  96. canReport = false;
  97. }
  98. if (canReport)
  99. {
  100. BestField bffromdate = new BestField() { fieldName = "fromdate", fieldSize = 40, fieldType = "System.String", paramOledbType = System.Data.OleDb.OleDbType.VarChar, displayField = false };
  101. bffromdate.fieldValue = fromdate.Substring(6, 4) + fromdate.Substring(0, 2) + fromdate.Substring(3, 2);
  102. BestField bftodate = new BestField() { fieldName = "todate", fieldSize = 40, fieldType = "System.String", paramOledbType = System.Data.OleDb.OleDbType.VarChar, displayField = false };
  103. bftodate.fieldValue = todate.Substring(6, 4) + todate.Substring(0, 2) + todate.Substring(3, 2);
  104. BestField GuidField = new BestField() { fieldName = "guidfield", fieldSize = 40, fieldType = "System.Guid", paramOledbType = OleDbType.Guid, displayField = false };
  105. string StudentGuid = Request.Form["StudentGuid"] ?? "";
  106. GuidField.fieldValue = StudentGuid;
  107. string tsql = "select schdate, studentid, BestStudents.firstName + ' ' + BestStudents.lastName as StudentName, BestStudents.ParentName, BestStudents.address1 + ' ' + BestStudents.address2 as address, BestStudents.city + ' ' + BestStudents.state + ' ' + BestStudents.zip as csz, BestStudents.homephone as homephone, BestStudents.workphone as workphone, BestStudents.emailid, case when trantype='Scheduled' then isnull(vbest.amount,0) else 0 end as tobecharged, case when trantype!='Scheduled' then vbest.amount else 0 end as paid, vbest.hours, case trantype when 'Scheduled' then case vbest.attendance when 0 then 'Attended' when 1 then 'Informed' when 2 then 'Absent' end else 'Payment' end transType, bprog.programName + ':' + bprog.programType programName, BestTutors.firstName + ' ' + BestTutors.lastName as TutorName, vbest.note from VBestSchedulesAndPayments vbest inner join BestStudents on beststudents.guidfield = vbest.studentguid left join BestPrograms bprog on bprog.guidfield = vbest.programguid left join BestTutors on BestTutors.guidfield = vbest.tutGuid where schdate >= ? and schdate <= ? and BestStudents.CenterId=? " ;
  108. if (!string.IsNullOrEmpty(StudentGuid))
  109. {
  110. tsql = tsql + " and BestStudents.guidfield=? ";
  111. }
  112. tsql += " order by StudentName, schDate";
  113. BestDatabase db = new BestDatabase();
  114. OleDbCommand myCmd = db.dbCmd;
  115. myCmd.CommandText = tsql;
  116. myCmd.Parameters.Add(bffromdate.Param);
  117. myCmd.Parameters.Add(bftodate.Param);
  118. myCmd.Parameters.Add(Utils.User.CenterIdField.Param);
  119. if (!string.IsNullOrEmpty(StudentGuid))
  120. {
  121. myCmd.Parameters.Add(GuidField.Param);
  122. }
  123. OleDbDataReader tblReader = myCmd.ExecuteReader();
  124. double total = 0;
  125. double chargedTotal = 0;
  126. double totalHours = 0;
  127. int colSpan = 0;
  128. rowshtml.Append("<br /><div id=\"studentReport\"><table class=\"tblreports\" cellspacing=\"0\" cellpadding=\"0\">");
  129. rowshtml.Append("<thead><tr><td colspan=\"9\" style=\"font-weight:bold;font-size:12px;\">" + Utils.User.CenterId + " Students Report</td></thead>");
  130. rowshtml.Append("<tr>");
  131. if (chkPayDate)
  132. {
  133. rowshtml.Append("<th>Date</th>");
  134. colSpan++;
  135. }
  136. rowshtml.Append("<th>Student Id</th>");
  137. colSpan++;
  138. if (chkStudentName)
  139. {
  140. rowshtml.Append("<th>Student Name</th>");
  141. colSpan++;
  142. }
  143. if (chkParentName)
  144. {
  145. rowshtml.Append("<th>Parent Name</th>");
  146. colSpan++;
  147. }
  148. if (chkAddress)
  149. {
  150. rowshtml.Append("<th>Contact Info</th>");
  151. rowshtml.Append("<th>Home Phone</th>");
  152. rowshtml.Append("<th>Work Phone</th>");
  153. rowshtml.Append("<th>E-Mail Id</th>");
  154. colSpan += 4;
  155. }
  156. if (chkProgramDesc)
  157. {
  158. rowshtml.Append("<th>Program Name</th>");
  159. colSpan++;
  160. }
  161. if (chkTutorName)
  162. {
  163. rowshtml.Append("<th>Tutor Name</th>");
  164. colSpan++;
  165. }
  166. if (chkNotes)
  167. {
  168. rowshtml.Append("<th>Notes</th>");
  169. colSpan++;
  170. }
  171. rowshtml.Append("<th>Trans Type</th>");
  172. colSpan++;
  173. rowshtml.Append("<th>Charged Amount</th>");
  174. rowshtml.Append("<th>Amount Paid</th>");
  175. rowshtml.Append("<th>Hours</th>");
  176. rowshtml.Append("<th>Balance</th>");
  177. rowshtml.Append("</tr>");
  178. string studentid = "";
  179. int Cnt = 0;
  180. string studName = "";
  181. double grandTotal = 0;
  182. while (tblReader.Read())
  183. {
  184. string stuid = tblReader[1].ToString();
  185. if (!studentid.Equals(stuid) && (Cnt > 0))
  186. {
  187. /* Totals for the Student */
  188. rowshtml.Append("<tr><td style=\"text-align:right;background-color:#fff;font-weight:bold;\" colspan=\"" + colSpan.ToString() + "\">" + studName + " Total&nbsp;</td>");
  189. if (chkTotalAmountCharged)
  190. {
  191. rowshtml.Append("<td class=\"ra\" style=\"background-color:#fff;padding-right:3px;\">" + chargedTotal.ToString("$0.00") + "</td>");
  192. }
  193. else
  194. {
  195. rowshtml.Append("<td>&nbsp;</td>");
  196. }
  197. if (chkTotalAmountPaid)
  198. {
  199. rowshtml.Append("<td class=\"ra\" style=\"background-color:#fff;padding-right:3px;\">" + total.ToString("$0.00") + "</td>");
  200. }
  201. else
  202. {
  203. rowshtml.Append("<td>&nbsp;</td>");
  204. }
  205. if (chkTotalHours)
  206. {
  207. rowshtml.Append("<td class=\"ra\" style=\"background-color:#fff;padding-right:3px;\">" + totalHours.ToString("0") + "</td>");
  208. }
  209. else
  210. {
  211. rowshtml.Append("<td>&nbsp;</td>");
  212. }
  213. if (chkAmountDue)
  214. {
  215. rowshtml.Append("<td class=\"ra\" style=\"white-space:nowrap;background-color:#fff;padding-right:3px;\">" + (total - chargedTotal).ToString("$0.00") + "</td>");
  216. }
  217. else
  218. {
  219. rowshtml.Append("<td>&nbsp;</td>");
  220. }
  221. rowshtml.Append("</tr>");
  222. chargedTotal = 0;
  223. total = 0;
  224. totalHours = 0;
  225. }
  226. studentid = stuid;
  227. Cnt++;
  228. string paydate = tblReader[0].ToString();
  229. paydate = paydate.Substring(4, 2) + "/" + paydate.Substring(6, 2) + "/" + paydate.Substring(0, 4);
  230. studName = tblReader[2].ToString();
  231. string parentName = tblReader[3].ToString();
  232. string address = tblReader[4].ToString() + " " + tblReader[5].ToString();
  233. string homephone = tblReader[6].ToString();
  234. string workphone = tblReader[7].ToString();
  235. string emailid = tblReader[8].ToString();
  236. string tranType = tblReader[12].ToString();
  237. Double chargedAmt = 0;
  238. Double.TryParse(tblReader[9].ToString(), out chargedAmt);
  239. if (tranType.Equals("Informed"))
  240. {
  241. chargedAmt = 0;
  242. }
  243. Double payAmt = 0;
  244. Double.TryParse(tblReader[10].ToString(), out payAmt);
  245. Double hours = 0;
  246. Double.TryParse(tblReader[11].ToString(), out hours);
  247. totalHours += hours;
  248. string progName = tblReader[13].ToString();
  249. string tutorName = tblReader[14].ToString();
  250. string notes = tblReader[15].ToString();
  251. total += payAmt;
  252. chargedTotal += chargedAmt;
  253. grandTotal += chargedAmt;
  254. string strPayAmt = (payAmt > 0) ? payAmt.ToString("$0.00") : "&nbsp;";
  255. string strChargedAmt = (chargedAmt > 0) ? chargedAmt.ToString("$0.00") : "&nbsp;";
  256. rowshtml.Append("<tr>");
  257. if (chkPayDate)
  258. {
  259. rowshtml.Append("<td>" + paydate + "</td>");
  260. }
  261. rowshtml.Append("<td>" + HttpUtility.HtmlEncode(stuid) + "</td>");
  262. if (chkStudentName)
  263. {
  264. rowshtml.Append("<td>" + HttpUtility.HtmlEncode(studName) + "</td>");
  265. }
  266. if (chkParentName)
  267. {
  268. rowshtml.Append("<td>" + HttpUtility.HtmlEncode(parentName) + "</td>");
  269. }
  270. if (chkAddress)
  271. {
  272. rowshtml.Append("<td>" + HttpUtility.HtmlEncode(address) + "</td>");
  273. rowshtml.Append("<td>" + HttpUtility.HtmlEncode(homephone) + "</td>");
  274. rowshtml.Append("<td>" + HttpUtility.HtmlEncode(workphone) + "</td>");
  275. rowshtml.Append("<td>" + HttpUtility.HtmlEncode(emailid) + "</td>");
  276. }
  277. if (chkProgramDesc)
  278. {
  279. rowshtml.Append("<td>" + HttpUtility.HtmlEncode(progName) + "</td>");
  280. }
  281. if (chkTutorName)
  282. {
  283. rowshtml.Append("<td>" + HttpUtility.HtmlEncode(tutorName) + "</td>");
  284. }
  285. if (chkNotes)
  286. {
  287. rowshtml.Append("<td>" + HttpUtility.HtmlEncode(notes) + "</td>");
  288. }
  289. rowshtml.Append("<td>" + HttpUtility.HtmlEncode(tranType) + "</td>");
  290. rowshtml.Append("<td class=\"ra\" style=\"padding-right:3px;\">" + strChargedAmt + "</td>");
  291. rowshtml.Append("<td class=\"ra\" style=\"padding-right:3px;\">" + strPayAmt + "</td>");
  292. rowshtml.Append("<td class=\"ra\">" + HttpUtility.HtmlEncode(hours.ToString("0")) + "</td></tr>");
  293. }
  294. rowshtml.Append("<tr><td style=\"text-align:right;background-color:#fff;font-weight:bold;\" colspan=\"" + colSpan.ToString() + "\">" + studName + " Total&nbsp;</td>");
  295. if (chkTotalAmountCharged)
  296. {
  297. rowshtml.Append("<td class=\"ra\" style=\"background-color:#fff;padding-right:3px;\">" + chargedTotal.ToString("$0.00") + "</td>");
  298. }
  299. else
  300. {
  301. rowshtml.Append("<td>&nbsp;</td>");
  302. }
  303. if (chkTotalAmountPaid)
  304. {
  305. rowshtml.Append("<td class=\"ra\" style=\"background-color:#fff;padding-right:3px;\">" + total.ToString("$0.00") + "</td>");
  306. }
  307. else
  308. {
  309. rowshtml.Append("<td>&nbsp;</td>");
  310. }
  311. if (chkTotalHours)
  312. {
  313. rowshtml.Append("<td class=\"ra\" style=\"background-color:#fff;padding-right:3px;\">" + totalHours.ToString("0") + "</td>");
  314. }
  315. else
  316. {
  317. rowshtml.Append("<td>&nbsp;</td>");
  318. }
  319. if (chkAmountDue)
  320. {
  321. rowshtml.Append("<td class=\"ra\" style=\"white-space:nowrap;background-color:#fff;padding-right:3px;\">" + (total - chargedTotal).ToString("0.00") + "</td>");
  322. }
  323. else
  324. {
  325. rowshtml.Append("<td>&nbsp;</td>");
  326. }
  327. rowshtml.Append("</tr>");
  328. rowshtml.Append("<tr><td style=\"font-weight:bold;\">Grand Total</td><td>" + Server.HtmlEncode(fromdate + " to " + todate) + "</td><td colspan=\"3\" style=\"text-align:right;font-weight:bold;\">" + grandTotal.ToString("$0.00") + "</td></tr>");
  329. rowshtml.Append("</table></div><br />");
  330. rowshtml.Append("<div><input type=\"button\" value=\"Print\" onclick=\"PrintTable('studentReport');\" /></div>");
  331. }
  332. }
  333. StringBuilder sb = new StringBuilder();
  334. sb.Append("<div class=\"centered\">");
  335. sb.Append("<table class=\"bestgrid\" cellpadding=\"0\" cellspacing=\"0\" border=\"0\">");
  336. sb.Append("<tr><td style=\"text-align:center;\" colspan=\"4\">Student Reports</td></tr>");
  337. sb.Append("<tr><td>From Date <input type=\"text\" id=\"fromdate\" name=\"fromdate\" size=\"9\" maxlength=\"10\" value=\"" + Server.HtmlEncode(fromdate) + "\" /></td><td>To Date <input type=\"text\" id=\"todate\" name=\"todate\" size=\"9\" maxlength=\"10\" value=\"" + Server.HtmlEncode(todate) + "\" /></td>");
  338. sb.Append("<td>Summary By <select id=\"summaryby\" name=\"summaryby\"><option value=\"\"></option>");
  339. sb.Append("<option value=\"paydate\" " + (summaryby.Equals("paydate") ? " selected " : "") + ">Payment Date</option>");
  340. sb.Append("<option value=\"payweek\" " + (summaryby.Equals("payweek") ? " selected " : "") + ">Week</option>");
  341. sb.Append("<option value=\"paymonth\" " + (summaryby.Equals("paymonth") ? " selected " : "") + ">Month</option>");
  342. sb.Append("<option value=\"payyear\" " + (summaryby.Equals("payyear") ? " selected " : "") + ">Year</option>");
  343. sb.Append("</select></td></tr>");
  344. sb.Append("<tr><td><input type=\"checkbox\" id=\"chkStudentName\" name=\"chkStudentName\" " + (chkStudentName ? "checked=\"checked\"" : "") + " /><label for=\"chkStudentName\">Student Name</lable></td><td><input type=\"checkbox\" id=\"chkParentName\" name=\"chkParentName\" " + (chkParentName ? "checked=\"checked\"" : "") + " /><label for=\"chkParentName\">Parent Name</label></td><td><input type=\"checkbox\" id=\"chkAddress\" name=\"chkAddress\" " + (chkAddress ? "checked=\"checked\"" : "") + " /><label for=\"chkAddress\">Contact Info</label></td></tr>");
  345. sb.Append("<tr><td><input type=\"checkbox\" name=\"chkTutorName\" id=\"chkTutorName\" " + (chkTutorName ? "checked=\"checked\"" : "") + " /><label for=\"chkTutorName\">Tutor name</label></td><td><input type=\"checkbox\" id=\"chkTotalHours\" name=\"chkTotalHours\" " + (chkTotalHours ? "checked=\"checked\"" : "") + " /><lable for=\"chkTotalHours\">Total Hours</label></td><td><input type=\"checkbox\" id=\"chkTotalAmountPaid\" name=\"chkTotalAmountPaid\" " + (chkTotalAmountPaid ? "checked=\"checked\"" : "") + " /><label for=\"chkTotalAmountPaid\">Total Amount Paid</lable></td></tr>");
  346. sb.Append("<tr><td><input type=\"checkbox\" id=\"chkTotalAmountCharged\" name=\"chkTotalAmountCharged\" " + (chkTotalAmountCharged ? "checked=\"checked\"" : "") + " /><label for=\"chkTotalAmountCharged\">Total Amount Charged</label></td><td><input type=\"checkbox\" id=\"chkAmountDue\" name=\"chkAmountDue\" " + (chkAmountDue ? "checked=\"checked\"" : "") + " /><label for=\"chkAmountDue\">Amount Due</label></td><td><input type=\"checkbox\" id=\"chkPayDate\" name=\"chkPayDate\" " + (chkPayDate ? "checked=\"checked\"" : "") + " /><label for=\"chkPayDate\">Payment Date</label></td></tr>");
  347. sb.Append("<tr><td><input type=\"checkbox\" id=\"chkProgramDesc\" name=\"chkProgramDesc\" " + (chkProgramDesc ? "checked=\"checked\"" : "") + " /><label for=\"chkProgramDesc\">Program Description</label></td><td><input type=\"checkbox\" id=\"chkNotes\" name=\"chkNotes\" " + (chkNotes ? "checked=\"checked\"" : "") + " /><label for=\"chkNotes\">Additional notes</label></td>");
  348. sb.Append("<td>Student <select id=\"StudentGuid\" name=\"StudentGuid\">" + StudentOptions() + "</select></td></tr>");
  349. sb.Append("<tr><td colspan=\"3\" style=\"text-align:center;\" ><input type=\"button\" value=\"Submit\" onclick=\"submit();\" /></td></tr>");
  350. sb.Append("</table>");
  351. ltrGrid.Text = sb.ToString() + rowshtml.ToString() + "</div>";
  352. }
  353. private string StudentOptions()
  354. {
  355. StringBuilder sb = new StringBuilder();
  356. string stuguid = Request.Form["StudentGuid"] ?? "";
  357. sb.Append("<option value=\"\"></option>");
  358. BestStudents bs = new BestStudents();
  359. bs.LoadRows("CenterId=?", "centerid", Utils.User.CenterId, "firstname");
  360. sb.Append(string.Join("", bs.TableRows.Rows.Select(x => "<option value=\"" + Server.HtmlEncode(x.Fields["guidfield"].fieldValue) + "\" " + (stuguid.Equals(x.Fields["guidfield"].fieldValue)?"selected":"") + " >" + Server.HtmlEncode(x.Fields["firstname"].fieldValue + " " + x.Fields["lastname"].fieldValue) + "</option>").ToArray()));
  361. return sb.ToString();
  362. }
  363. }
  364. }