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