private static void Main() { try { var fileToRead = new FileInfo( @"D:\Users\Curt\Dropbox\Documents\Vote\Mantis\772\PACs Master List revised.v2.xlsx"); using (var stream = fileToRead.OpenRead()) { var reader = ExcelReaderFactory.CreateReader(stream); using (reader) { var config = new ExcelDataSetConfiguration { ConfigureDataTable = tableReader => new ExcelDataTableConfiguration { UseHeaderRow = true } }; var result = reader.AsDataSet(config); var dataTable = result.Tables[0]; MessageBox.Show(Join("\n", dataTable.Columns.OfType <DataColumn>().Select(c => c.ColumnName))); MessageBox.Show(dataTable.Rows.Count.ToString()); MessageBox.Show(Join("\n", dataTable.Rows.OfType <DataRow>().Select(r => r["Type"] as string) .Where(t => !IsNullOrWhiteSpace(t)).Distinct()), "Type"); MessageBox.Show(Join("\n", dataTable.Rows.OfType <DataRow>().Select(r => r["Ideology"] as string) .Where(t => !IsNullOrWhiteSpace(t)).Distinct()), "Ideology"); MessageBox.Show(Join("\n", dataTable.Rows.OfType <DataRow>().Select(r => r["Status"] as string) .Where(t => !IsNullOrWhiteSpace(t)).Distinct()), "Status"); MessageBox.Show(Join("\n", dataTable.Rows.OfType <DataRow>().Select(r => r["State"] as string) .Where(s => !IsNullOrWhiteSpace(s) && !StateCache.IsValidStateCode(s)).Distinct()), "Invalid States"); MessageBox.Show(Join("\n", dataTable.Rows.OfType <DataRow>().Select(r => r["Notes"] as string) .Where(n => !IsNullOrWhiteSpace(n) && n.Contains("\n"))), "Multi-line Notes"); MessageBox.Show(Join("\n", dataTable.Rows.OfType <DataRow>().Select(r => r["Mission URL"] as string) .Where(n => !IsNullOrWhiteSpace(n) && n.Contains("\n"))), "Multi-line Mission URL"); MessageBox.Show(Join("\n", dataTable.Rows.OfType <DataRow>().Select(r => r["Email1"] as string) .Where(n => !IsNullOrWhiteSpace(n) && n.Contains("\n"))), "Multi-line Email1"); MessageBox.Show(Join("\n", dataTable.Rows.OfType <DataRow>().Select(r => r["Email2"] as string) .Where(n => !IsNullOrWhiteSpace(n) && n.Contains("\n"))), "Multi-line Email2"); MessageBox.Show(Join("\n", dataTable.Rows.OfType <DataRow>().Select(r => r["Email1"] as string) .Where(m => !IsNullOrWhiteSpace(m) && m != "#N/A" && !Validation.IsValidEmailAddress(m)).Distinct()), "Invalid Email1s"); MessageBox.Show(Join("\n", dataTable.Rows.OfType <DataRow>().Select(r => r["Email2"] as string) .Where(m => !IsNullOrWhiteSpace(m) && m != "#N/A" && !Validation.IsValidEmailAddress(m)).Distinct()), "Invalid Email2s"); MessageBox.Show(Join("\n", dataTable.Rows.OfType <DataRow>().Select(r => r["PAC URL"] as string) .Where(m => !IsNullOrWhiteSpace(m) && !VotePage.IsValidUrl(m)).Distinct()), "Invalid URLs"); MessageBox.Show(Join("\n", dataTable.Rows.OfType <DataRow>().Select(r => r["Mission URL"] as string) .SelectMany(s => s.SafeString().Split('\n')) .Where(m => !IsNullOrWhiteSpace(m) && !VotePage.IsValidUrl(m)).Distinct()), "Invalid Mission URLs"); var orgTypeDictionary = OrganizationTypes.GetAllData() .ToDictionary(r => r.OrgType, r => r.OrgTypeId); var orgSubTypeDictionary = OrganizationSubTypes.GetAllData() .ToDictionary(r => new { r.OrgTypeId, r.OrgSubType }, r => r.OrgSubTypeId); var ideologyDictionary = OrganizationIdeologies.GetAllData() .ToDictionary(r => r.Ideology, r => r.IdeologyId); ideologyDictionary.Add(Empty, 0); string Field(DataRow row, string fieldname) { return((row[fieldname] as string).SafeString().Trim()); } foreach (var row in dataTable.Rows.OfType <DataRow>()) { var orgTypeId = orgTypeDictionary["PAC"]; string orgSubType; switch (Field(row, "Type")) { case "Independent Expenditures PAC": orgSubType = "Independent Expenditures"; break; default: orgSubType = "Standard"; break; } var orgSubTypeId = orgSubTypeDictionary[new { OrgTypeId = orgTypeId, OrgSubType = orgSubType }]; var orgId = Organizations.Insert(orgTypeId, orgSubTypeId, ideologyDictionary[Field(row, "Ideology")], DateTime.UtcNow, Field(row, "Name"), Field(row, "Abbreviated Name"), Empty, Empty, Field(row, "City"), Field(row, "State"), Empty, Field(row, "PAC URL"), Empty, Empty, Empty, null, Empty, Empty, null, Empty); if (Field(row, "Contact 1") != Empty || Field(row, "Title 1") != Empty || Field(row, "Email1") != Empty || Field(row, "Phone1") != Empty) { OrganizationContacts.Insert(orgId, Field(row, "Contact 1"), Field(row, "Email1"), Field(row, "Phone1"), Field(row, "Title 1"), 10); } if (Field(row, "Contact 2") != Empty || Field(row, "Title 2") != Empty || Field(row, "Email2") != Empty || Field(row, "Phone2") != Empty) { OrganizationContacts.Insert(orgId, Field(row, "Contact 2"), Field(row, "Email2"), Field(row, "Phone2"), Field(row, "Title 2"), 10); } var mOrder = 0; foreach (var missionUrl in Field(row, "Mission URL").Split('\n') .Select(s => s.Trim()).Where(s => s != Empty)) { mOrder += 10; OrganizationMissionUrls.Insert(orgId, missionUrl, mOrder); } var notes = Field(row, "Notes"); if (notes != Empty) { OrganizationNotes.Insert(orgId, DateTime.UtcNow, notes); } } } } } catch (Exception ex) { MessageBox.Show(ex.Message); } }
public static void SaveOrganizationsData(OneOrgType[] data) { var organizationsTable = GetAllData(); var contactsTable = OrganizationContacts.GetAllData(); var missionUrlsTable = OrganizationMissionUrls.GetAllData(); var emailTagsTable = OrganizationAssignedEmailTags.GetAllData(); bool UpdateContacts(int orgId, OneOrganizationContact[] items, OrganizationContactsTable table) { var changed = false; // delete any missing contacts foreach (var row in table.Where(r => r.RowState != DataRowState.Deleted && r.OrgId == orgId)) { if (items.All(i => row.ContactId != i.ContactId)) { row.Delete(); changed = true; } } // update or add remaining entries var order = 0; foreach (var i in items) { order += 10; var row = table.Where(r => r.RowState != DataRowState.Deleted) .FirstOrDefault(r => r.OrgId == orgId && r.ContactId == i.ContactId); if (row == null) { OrganizationContacts.Insert(i.ContactId, orgId, i.Contact, i.Email, i.Phone, i.Title, order); changed = true; } else { if (row.Contact != i.Contact) { row.Contact = i.Contact; changed = true; } if (row.Email != i.Email) { row.Email = i.Email; changed = true; } if (row.Phone != i.Phone) { row.Phone = i.Phone; changed = true; } if (row.Title != i.Title) { row.Title = i.Title; changed = true; } if (row.ContactOrder != order) { row.ContactOrder = order; changed = true; } } } return(changed); } bool UpdateMissionUrls(int orgId, OneOrganizationMissionUrl[] items, OrganizationMissionUrlsTable table) { var changed = false; // delete any missing urls foreach (var row in table.Where(r => r.RowState != DataRowState.Deleted && r.OrgId == orgId)) { if (items.All(i => row.OrgMissionUrlId != i.OrgMissionUrlId)) { row.Delete(); changed = true; } } // update or add remaining entries var order = 0; foreach (var i in items) { order += 10; var row = table.Where(r => r.RowState != DataRowState.Deleted).FirstOrDefault(r => r.OrgId == orgId && r.OrgMissionUrlId == i.OrgMissionUrlId); if (row == null) { OrganizationMissionUrls.Insert(i.OrgMissionUrlId, orgId, i.Url, order); changed = true; } else { if (row.Url != i.Url) { row.Url = i.Url; changed = true; } if (row.UrlOrder != order) { row.UrlOrder = order; changed = true; } } } return(changed); } bool UpdateEmailTags(int orgId, int[] items, OrganizationAssignedEmailTagsTable table) { var changed = false; // delete any missing tags foreach (var row in table.Where(r => r.RowState != DataRowState.Deleted && r.OrgId == orgId)) { if (items.All(i => row.EmailTagId != i)) { row.Delete(); changed = true; } } // add remaining entries foreach (var i in items) { var row = table.Where(r => r.RowState != DataRowState.Deleted) .FirstOrDefault(r => r.OrgId == orgId && r.EmailTagId == i); if (row == null) { OrganizationAssignedEmailTags.Insert(orgId, i); changed = true; } } return(changed); } // delete any missing Organizations rows and related contacts, mission urls // and assigned email tags foreach (var orgRow in organizationsTable) { if (data.All(ot => ot.Organizations.All(o => orgRow.OrgId != o.OrgId))) { foreach (var row in contactsTable) { if (row.RowState != DataRowState.Deleted && row.OrgId == orgRow.OrgId) { row.Delete(); } } foreach (var row in missionUrlsTable) { if (row.RowState != DataRowState.Deleted && row.OrgId == orgRow.OrgId) { row.Delete(); } } foreach (var row in emailTagsTable) { if (row.RowState != DataRowState.Deleted && row.OrgId == orgRow.OrgId) { row.Delete(); } } orgRow.Delete(); } } // update or add remaining entries foreach (var ot in data) { foreach (var o in ot.Organizations) { var orgRow = organizationsTable.Where(r => r.RowState != DataRowState.Deleted) .FirstOrDefault(r => r.OrgId == o.OrgId); if (orgRow == null) { Insert(o.OrgId, ot.OrgTypeId, o.OrgSubTypeId, o.IdeologyId, DateTime.UtcNow, o.Name, o.OrgAbbreviation, o.Address1, o.Address2, o.City, o.StateCode, o.Zip, o.Url, o.LongMission, o.ShortMission, o.EmailMission, null, Empty, Empty, null, Empty); } else { var changed = false; if (orgRow.OrgSubTypeId != o.OrgSubTypeId) { orgRow.OrgSubTypeId = o.OrgSubTypeId; changed = true; } if (orgRow.IdeologyId != o.IdeologyId) { orgRow.IdeologyId = o.IdeologyId; changed = true; } if (orgRow.Name != o.Name) { orgRow.Name = o.Name; changed = true; } if (orgRow.OrgAbbreviation != o.OrgAbbreviation) { orgRow.OrgAbbreviation = o.OrgAbbreviation; changed = true; } if (orgRow.Address1 != o.Address1) { orgRow.Address1 = o.Address1; changed = true; } if (orgRow.Address2 != o.Address2) { orgRow.Address2 = o.Address2; changed = true; } if (orgRow.City != o.City) { orgRow.City = o.City; changed = true; } if (orgRow.StateCode != o.StateCode) { orgRow.StateCode = o.StateCode; changed = true; } if (orgRow.Zip != o.Zip) { orgRow.Zip = o.Zip; changed = true; } if (orgRow.Url != o.Url) { orgRow.Url = o.Url; changed = true; } if (orgRow.LongMission != o.LongMission) { orgRow.LongMission = o.LongMission; changed = true; } if (orgRow.ShortMission != o.ShortMission) { orgRow.ShortMission = o.ShortMission; changed = true; } if (orgRow.EmailMission != o.EmailMission) { orgRow.EmailMission = o.EmailMission; changed = true; } changed |= UpdateContacts(o.OrgId, o.Contacts, contactsTable); changed |= UpdateMissionUrls(o.OrgId, o.MissionUrls, missionUrlsTable); changed |= UpdateEmailTags(o.OrgId, o.EmailTagIds, emailTagsTable); if (changed) { orgRow.DateStamp = DateTime.UtcNow; } } } } UpdateTable(organizationsTable); OrganizationContacts.UpdateTable(contactsTable); OrganizationMissionUrls.UpdateTable(missionUrlsTable); OrganizationAssignedEmailTags.UpdateTable(emailTagsTable); }