PageRenderTime 41ms CodeModel.GetById 14ms RepoModel.GetById 1ms app.codeStats 0ms

/CmsWeb/Areas/Main/Models/Export/UpdatePeopleModel.cs

https://bitbucket.org/mahalowe/bvcms
C# | 207 lines | 201 code | 6 blank | 0 comment | 13 complexity | ec3fc60c798bb20df32cb19f8d449a83 MD5 | raw file
Possible License(s): CC-BY-SA-3.0, Apache-2.0, BSD-3-Clause, LGPL-2.1, MPL-2.0-no-copyleft-exception, AGPL-3.0
  1. using System;
  2. using System.Collections.Generic;
  3. using System.Xml;
  4. using System.Web.Mvc;
  5. using System.Xml.Linq;
  6. using UtilityExtensions;
  7. using System.Linq;
  8. using CmsData;
  9. using System.Web.UI.WebControls;
  10. using System.Web.UI;
  11. using System.Collections;
  12. using System.Data.Common;
  13. using System.IO;
  14. using NPOI.HSSF.UserModel;
  15. using System.Text;
  16. namespace CmsWeb.Models
  17. {
  18. public class UpdatePeopleModel : ActionResult
  19. {
  20. int queryid;
  21. public UpdatePeopleModel(int QueryId)
  22. {
  23. this.queryid = QueryId;
  24. }
  25. public override void ExecuteResult(ControllerContext context)
  26. {
  27. var fs = new FileStream(context.HttpContext.Server.MapPath(
  28. @"\Content\UpdatePeople.xls"), FileMode.Open, FileAccess.Read);
  29. var wb = new HSSFWorkbook(fs, true);
  30. var sheet = wb.GetSheet("Sheet1");
  31. var r = 1;
  32. foreach (var p in UpdatePeopleRows())
  33. {
  34. var row = sheet.CreateRow(r++);
  35. var c = 0;
  36. row.CreateCell(c++).SetCellValue(p.PeopleId);
  37. row.CreateCell(c++).SetCellValue(p.Title);
  38. row.CreateCell(c++).SetCellValue(p.First);
  39. row.CreateCell(c++).SetCellValue(p.GoesBy);
  40. row.CreateCell(c++).SetCellValue(p.Last);
  41. row.CreateCell(c++).SetCellValue(p.Suffix);
  42. row.CreateCell(c++).SetCellValue(p.Email1);
  43. row.CreateCell(c++).SetCellValue(p.Email2);
  44. row.CreateCell(c++).SetCellValue(p.Gender);
  45. if (p.BirthDate.HasValue)
  46. row.CreateCell(c++).SetCellValue(p.BirthDate.Value);
  47. else
  48. row.CreateCell(c++, NPOI.SS.UserModel.CellType.BLANK);
  49. if (p.Anniversary.HasValue)
  50. row.CreateCell(c++).SetCellValue(p.Anniversary.Value);
  51. else
  52. row.CreateCell(c++, NPOI.SS.UserModel.CellType.BLANK);
  53. if (p.Joined.HasValue)
  54. row.CreateCell(c++).SetCellValue(p.Joined.Value);
  55. else
  56. row.CreateCell(c++, NPOI.SS.UserModel.CellType.BLANK);
  57. row.CreateCell(c++).SetCellValue(p.Cell);
  58. row.CreateCell(c++).SetCellValue(p.Work);
  59. row.CreateCell(c++).SetCellValue(p.Member);
  60. if (p.Grade.HasValue)
  61. row.CreateCell(c++).SetCellValue(p.Grade.Value);
  62. else
  63. row.CreateCell(c++, NPOI.SS.UserModel.CellType.BLANK);
  64. row.CreateCell(c++).SetCellValue(p.Marital);
  65. row.CreateCell(c++).SetCellValue(p.FamilyPos);
  66. row.CreateCell(c++).SetCellValue(p.AltName);
  67. row.CreateCell(c++).SetCellValue(p.Campus);
  68. row.CreateCell(c++).SetCellValue(p.School);
  69. row.CreateCell(c++).SetCellValue(p.Occupation);
  70. row.CreateCell(c++).SetCellValue(p.Employer);
  71. if (p.Deceased.HasValue)
  72. row.CreateCell(c++).SetCellValue(p.Deceased.Value);
  73. else
  74. row.CreateCell(c++, NPOI.SS.UserModel.CellType.BLANK);
  75. }
  76. var Response = context.HttpContext.Response;
  77. Response.Buffer = true;
  78. Response.ContentType = "application/vnd.ms-excel";
  79. Response.AddHeader("Content-Disposition", "attachment;filename=UpdatePeople.xls");
  80. Response.Charset = "";
  81. wb.Write(Response.OutputStream);
  82. }
  83. public IEnumerable<UpdatePeopleItem> UpdatePeopleRows()
  84. {
  85. var Db = DbUtil.Db;
  86. var query = Db.PeopleQuery(queryid);
  87. var q = from p in query
  88. select new UpdatePeopleItem
  89. {
  90. PeopleId = p.PeopleId,
  91. Title = p.TitleCode,
  92. First = p.FirstName,
  93. GoesBy = p.NickName,
  94. Last = p.LastName,
  95. Suffix = p.SuffixCode,
  96. Email1 = p.EmailAddress,
  97. Email2 = p.EmailAddress2,
  98. Gender = p.Gender.Description,
  99. BirthDate = Util.FormatBirthday(p.BirthYear, p.BirthMonth, p.BirthDay).ToDate(),
  100. Anniversary = p.WeddingDate,
  101. Joined = p.JoinDate,
  102. Cell = p.CellPhone.FmtFone(),
  103. Work = p.WorkPhone.FmtFone(),
  104. Member = p.MemberStatus.Description,
  105. Grade = p.Grade,
  106. Marital = p.MaritalStatus.Description,
  107. FamilyPos = p.FamilyPosition.Description,
  108. AltName = p.AltName,
  109. Campus = p.Campu.Description,
  110. School = p.SchoolOther,
  111. Occupation = p.OccupationOther,
  112. Employer = p.EmployerOther,
  113. Deceased = p.DeceasedDate
  114. };
  115. return q;
  116. }
  117. public static void UpdatePeople(string path, string host, int UserPeopleId)
  118. {
  119. var factory = DbProviderFactories.GetFactory("System.Data.OleDb");
  120. using (var cn = factory.CreateConnection())
  121. {
  122. cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
  123. + path + ";Extended Properties=\"Excel 8.0;HDR=YES;\";";
  124. cn.Open();
  125. var Db = new CMSDataContext(Util.GetConnectionString(host));
  126. UpdatePeople(cn, Db, UserPeopleId);
  127. }
  128. }
  129. public class UpdatePeopleItem
  130. {
  131. public int PeopleId { get; set; }
  132. public string Title { get; set; }
  133. public string First { get; set; }
  134. public string GoesBy { get; set; }
  135. public string Last { get; set; }
  136. public string Suffix { get; set; }
  137. public string Email1 { get; set; }
  138. public string Email2 { get; set; }
  139. public string Gender { get; set; }
  140. public DateTime? BirthDate { get; set; }
  141. public DateTime? Anniversary { get; set; }
  142. public DateTime? Joined { get; set; }
  143. public string Cell { get; set; }
  144. public string Work { get; set; }
  145. public string Member { get; set; }
  146. public int? Grade { get; set; }
  147. public string Marital { get; set; }
  148. public string FamilyPos { get; set; }
  149. public string AltName { get; set; }
  150. public string Campus { get; set; }
  151. public string School { get; set; }
  152. public string Occupation { get; set; }
  153. public string Employer { get; set; }
  154. public DateTime? Deceased { get; set; }
  155. };
  156. private static void UpdatePeople(DbConnection cn, CMSDataContext Db, int UserPeopleId)
  157. {
  158. var cv = new CodeValueModel();
  159. var pcmd = cn.CreateCommand();
  160. pcmd.CommandText = "select * from [Sheet1$]";
  161. var rd = pcmd.ExecuteReader();
  162. while (rd.Read())
  163. {
  164. var i = Util.GetAs(rd, typeof(UpdatePeopleItem)) as UpdatePeopleItem;
  165. var p = Db.LoadPersonById(i.PeopleId);
  166. var psb = new StringBuilder();
  167. p.UpdateValue(psb, "TitleCode", i.Title);
  168. p.UpdateValue(psb, "FirstName", i.First);
  169. p.UpdateValue(psb, "NickName", i.GoesBy);
  170. p.UpdateValue(psb, "LastName", i.Last);
  171. p.UpdateValue(psb, "SuffixCode", i.Suffix);
  172. p.UpdateValue(psb, "EmailAddress", i.Email1);
  173. p.UpdateValue(psb, "EmailAddress2", i.Email2);
  174. p.UpdateValue(psb, "DOB", i.BirthDate.FormatDate());
  175. p.UpdateValue(psb, "WeddingDate", i.Anniversary);
  176. p.UpdateValue(psb, "JoinDate", i.Joined);
  177. p.UpdateValue(psb, "CellPhone", i.Cell.GetDigits());
  178. p.UpdateValue(psb, "WorkPhone", i.Work.GetDigits());
  179. p.UpdateValue(psb, "AltName", i.AltName);
  180. p.UpdateValue(psb, "SchoolOther", i.School);
  181. p.UpdateValue(psb, "OccupationOther", i.Occupation);
  182. p.UpdateValue(psb, "EmployerOther", i.Employer);
  183. p.UpdateValue(psb, "Grade", i.Grade);
  184. p.UpdateValue(psb, "Deceased", i.Deceased);
  185. p.UpdateValue(psb, "MemberStatusId", CviOrNull(cv.MemberStatusCodes().SingleOrDefault(c => c.Value == i.Member)) ?? 20);
  186. p.UpdateValue(psb, "GenderId", CviOrNull(cv.GenderCodes().SingleOrDefault(c => c.Value == i.Gender)) ?? 0);
  187. p.UpdateValue(psb, "MaritalStatusId", CviOrNull(cv.MaritalStatusCodes().SingleOrDefault(c => c.Value == i.Marital)) ?? 0);
  188. p.UpdateValue(psb, "PositionInFamilyId", CviOrNull(cv.FamilyPositionCodes().SingleOrDefault(c => c.Value == i.FamilyPos)) ?? 0);
  189. p.UpdateValue(psb, "CampusId", CviOrNull(cv.AllCampuses().SingleOrDefault(c => c.Value == i.Campus)));
  190. p.LogChanges(Db, psb, UserPeopleId);
  191. Db.SubmitChanges();
  192. }
  193. }
  194. private static int? CviOrNull(CodeValueItem cvi)
  195. {
  196. if (cvi == null)
  197. return null;
  198. return cvi.Id;
  199. }
  200. }
  201. }