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

/BaliEnterpriseSystems/BaliEnterpriseSystems/DownloadExport.aspx.cs

https://github.com/sirivedula/BEST
C# | 135 lines | 123 code | 12 blank | 0 comment | 17 complexity | e1ebbb521429599d8b1a2c3a1711eb6a MD5 | raw file
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Linq;
  4. using System.Web;
  5. using System.Web.UI;
  6. using System.Web.UI.WebControls;
  7. using BaliEnterpriseSystems.BestObjects;
  8. using System.IO;
  9. using System.Text;
  10. using System.Data.OleDb;
  11. namespace BaliEnterpriseSystems
  12. {
  13. public partial class DownloadExport : System.Web.UI.Page
  14. {
  15. protected void Page_LoadComplete(object sender, EventArgs e)
  16. {
  17. if (HttpContext.Current.Session["CurrentUser"] == null)
  18. {
  19. Response.Redirect("Logout.aspx");
  20. }
  21. string exportguid = Request.Form["exportguid"];
  22. if (string.IsNullOrEmpty(exportguid))
  23. {
  24. exportguid = Request.QueryString["exportguid"];
  25. }
  26. if (!string.IsNullOrEmpty(exportguid))
  27. {
  28. List<BestField> bparam = new List<BestField>();
  29. BestField fld = new BestField() { fieldName = "guidfield", fieldSize = 40, fieldType = "System.Guid", paramOledbType = System.Data.OleDb.OleDbType.Guid };
  30. fld.fieldValue = exportguid;
  31. bparam.Add(fld);
  32. BestExports bexp = new BestExports();
  33. bexp.LoadRows("guidfield=?", bparam);
  34. BestExportFields bexpFields = new BestExportFields();
  35. bexpFields.LoadRows("exportguid=?", bparam, "ordinal");
  36. string selfields = string.Join(",", bexpFields.TableRows.Rows.Select(x => x.Fields["fieldname"].fieldValue).ToArray());
  37. selfields = ReplaceT1(selfields);
  38. string headers = "\"" + string.Join("\",\"",bexpFields.TableRows.Rows.Select(x=>x.Fields["displayname"].fieldValue).ToArray()) + "\"\n";
  39. string filedata = getExportData(bexp.exportType, selfields);
  40. byte[] bytedata = strToByteArray(filedata);
  41. MemoryStream ms = new MemoryStream();
  42. byte[] bytes = strToByteArray(headers);
  43. ms.Write(bytes, 0, bytes.Length);
  44. ms.Write(bytedata, 0, bytedata.Length);
  45. string dest = bexp.exportType + DateTime.Today.ToString("MMddyyyy") + ".csv";
  46. Response.ClearContent();
  47. Response.ClearHeaders();
  48. Response.ContentType = "application/csv";
  49. Response.AddHeader("Content-Disposition", "attachment; filename=" + dest);
  50. Response.OutputStream.Write(ms.ToArray(), 0, ms.ToArray().Length);
  51. Response.Flush();
  52. Response.Close();
  53. ms.Close();
  54. }
  55. }
  56. private string getExportData(string exptype, string fields)
  57. {
  58. StringBuilder sb = new StringBuilder();
  59. string tsql = "";
  60. if (exptype.Equals("Payments"))
  61. {
  62. tsql = "select " + fields + " From (" + UtilExporter.PaymentSQL() + ") t1";
  63. }
  64. else if (exptype.Equals("Students"))
  65. {
  66. tsql = "select " + fields + " From (" + UtilExporter.StudentSQL() + ") t1";
  67. }
  68. else if (exptype.Equals("Tutors"))
  69. {
  70. tsql = "select " + fields + " From (" + UtilExporter.TutorSQL() + ") t1";
  71. }
  72. else if (exptype.Equals("Change Audit"))
  73. {
  74. tsql = "select " + fields + " From (" + UtilExporter.ChangeAuditSQL() + ") t1";
  75. }
  76. else if (exptype.Equals("Schedules"))
  77. {
  78. tsql = "select " + fields + " From (" + UtilExporter.ScheduleSQL() + ") t1";
  79. }
  80. if (tsql.Length > 0)
  81. {
  82. BestTable tbl = new BestTable();
  83. OleDbCommand myCmd = tbl.dbCmd;
  84. myCmd.CommandText = "insert into BestLogs (message) values (?)";
  85. OleDbParameter p1 = new OleDbParameter("msg", OleDbType.VarChar, 9999999);
  86. p1.Value = tsql;
  87. myCmd.Parameters.Add(p1);
  88. myCmd.ExecuteNonQuery();
  89. myCmd.Parameters.Clear();
  90. myCmd.CommandText = tsql;
  91. OleDbDataReader expReader = myCmd.ExecuteReader();
  92. string aline = "";
  93. while (expReader.Read())
  94. {
  95. aline = "";
  96. for (int i = 0; i < expReader.FieldCount; i++)
  97. {
  98. object val = expReader.GetValue(i);
  99. aline += "\"" + (val.ToString()) + "\",";
  100. }
  101. sb.AppendLine(aline);
  102. }
  103. expReader.Close();
  104. }
  105. return sb.ToString();
  106. }
  107. private string ReplaceT1(string fields)
  108. {
  109. string result = fields;
  110. string[] aryReplaces = { "BestStudents.", "BestStudentGPA.", "BestStudentNotes.", "BestStudentPickup.", "BestStudentPayment.", "BestStudentReference.", "BestStudentPicture.", "BestStudentPrograms.", "BestTutors.", "BestPaymentDetails.", "BestPaymentTypes.", "BestSchedules" };
  111. for (int i = 0; i < aryReplaces.Length; i++)
  112. {
  113. result = result.Replace(aryReplaces[i], "t1.");
  114. }
  115. return result;
  116. }
  117. byte[] strToByteArray(string str)
  118. {
  119. System.Text.ASCIIEncoding enc = new System.Text.ASCIIEncoding();
  120. return enc.GetBytes(str);
  121. }
  122. }
  123. }