public static OrganizationsSelectReportData GetOrganizationsSelectReportData() { const string cmdText = "SELECT ot.OrgTypeId,ot.OrgType FROM OrganizationTypes ot" + " ORDER BY ot.OrgTypeOrder"; var result = new OrganizationsSelectReportData(); var cmd = VoteDb.GetCommand(cmdText); using (var cn = VoteDb.GetOpenConnection()) { cmd.Connection = cn; var table = new DataTable(); DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand); adapter.Fill(table); result.OrgTypes = table.Rows.OfType <DataRow>().GroupBy(r => r.OrgTypeId()) .Select(g => new OneReportOrgType { OrgTypeId = g.First().OrgTypeId(), OrgType = g.First().OrgType() }).ToArray(); } // attach sub types var subTypeDictionary = OrganizationSubTypes.GetAllData().GroupBy(r => r.OrgTypeId) .ToDictionary(g => g.Key, g => g); foreach (var orgType in result.OrgTypes) { if (subTypeDictionary.ContainsKey(orgType.OrgTypeId)) { orgType.SubTypes = subTypeDictionary[orgType.OrgTypeId] .OrderBy(t => t.OrgSubTypeOrder).Select(t => new OneOrgTypeSubType { OrgSubTypeId = t.OrgSubTypeId, OrgSubType = t.OrgSubType }).ToArray(); } else { orgType.SubTypes = new OneOrgTypeSubType[0]; } } // attach ideologies result.Ideologies = OrganizationIdeologies.GetAllData().OrderBy(r => r.IdeologyOrder) .Select(r => new OneIdeology { IdeologyId = r.IdeologyId, Ideology = r.Ideology }) .ToArray(); // attach email tags var tagDictionary = OrganizationEmailTags.GetAllData().GroupBy(r => r.OrgTypeId) .ToDictionary(g => g.Key, g => g); foreach (var orgType in result.OrgTypes) { if (tagDictionary.ContainsKey(orgType.OrgTypeId)) { orgType.EmailTags = tagDictionary[orgType.OrgTypeId].OrderBy(t => t.EmailTagOrder) .Select(t => new OneOrgTypeEmailTag { EmailTagId = t.EmailTagId, EmailTag = t.EmailTag }) .ToArray(); } else { orgType.EmailTags = new OneOrgTypeEmailTag[0]; } } return(result); }
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 OrganizationsData GetOrganizationsData() { const string cmdText = "SELECT ot.OrgTypeId,ot.OrgType,(o.OrgId IS NULL) AS IsNull,o.OrgId,o.OrgTypeId," + "o.OrgSubTypeId,o.IdeologyId,o.DateStamp,o.Name,o.OrgAbbreviation,o.Address1,o.Address2,o.City," + "o.StateCode,o.Zip,o.Url,o.LongMission,o.ShortMission,o.EmailMission" + " FROM OrganizationTypes ot" + " LEFT OUTER JOIN Organizations o ON o.OrgTypeId = ot.OrgTypeId" + " ORDER BY ot.OrgTypeOrder,o.Name"; var result = new OrganizationsData(); var cmd = VoteDb.GetCommand(cmdText); using (var cn = VoteDb.GetOpenConnection()) { cmd.Connection = cn; var table = new DataTable(); DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand); adapter.Fill(table); result.OrgTypes = table.Rows.OfType <DataRow>().GroupBy(r => r.OrgTypeId()).Select( g => new OneOrgType { OrgTypeId = g.First().OrgTypeId(), OrgType = g.First().OrgType(), Organizations = g.Where(r => r.OrgId() != 0).Select(r => new OneOrganization { OrgId = r.OrgId(), OrgTypeId = g.First().OrgTypeId(), OrgSubTypeId = r.OrgSubTypeId(), IdeologyId = r.IdeologyId(), DateStamp = r.DateStamp().AsUtc(), Name = r.Name(), OrgAbbreviation = r.OrgAbbreviation(), Address1 = r.Address1(), Address2 = r.Address2(), City = r.City(), StateCode = r.StateCode(), Zip = r.Zip(), Url = r.Url(), LongMission = r.LongMission(), ShortMission = r.ShortMission(), EmailMission = r.EmailMission() }).ToArray() }).ToArray(); } // attach sub types var subTypeDictionary = OrganizationSubTypes.GetAllData().GroupBy(r => r.OrgTypeId) .ToDictionary(g => g.Key, g => g); foreach (var orgType in result.OrgTypes) { if (subTypeDictionary.ContainsKey(orgType.OrgTypeId)) { orgType.SubTypes = subTypeDictionary[orgType.OrgTypeId] .OrderBy(t => t.OrgSubTypeOrder).Select(t => new OneOrgTypeSubType { OrgSubTypeId = t.OrgSubTypeId, OrgSubType = t.OrgSubType }).ToArray(); } else { orgType.SubTypes = new OneOrgTypeSubType[0]; } } // attach ideologies result.Ideologies = OrganizationIdeologies.GetAllData().OrderBy(r => r.IdeologyOrder) .Select(r => new OneIdeology { IdeologyId = r.IdeologyId, Ideology = r.Ideology }) .ToArray(); // attach email tags var tagDictionary = OrganizationEmailTags.GetAllData().GroupBy(r => r.OrgTypeId) .ToDictionary(g => g.Key, g => g); foreach (var orgType in result.OrgTypes) { if (tagDictionary.ContainsKey(orgType.OrgTypeId)) { orgType.EmailTags = tagDictionary[orgType.OrgTypeId].OrderBy(t => t.EmailTagOrder) .Select(t => new OneOrgTypeEmailTag { EmailTagId = t.EmailTagId, EmailTag = t.EmailTag }) .ToArray(); } else { orgType.EmailTags = new OneOrgTypeEmailTag[0]; } } // attach assigned email tags var assignedTagDictionary = OrganizationAssignedEmailTags.GetAllData() .GroupBy(r => r.OrgId).ToDictionary(g => g.Key, g => g); foreach (var orgType in result.OrgTypes) { foreach (var org in orgType.Organizations) { if (assignedTagDictionary.ContainsKey(org.OrgId)) { org.EmailTagIds = assignedTagDictionary[org.OrgId].Select(t => t.EmailTagId) .OrderBy(id => id).ToArray(); } else { org.EmailTagIds = new int[0]; } } } // attach contacts var contactsDictionary = OrganizationContacts.GetAllData().GroupBy(r => r.OrgId) .ToDictionary(g => g.Key, g => g); foreach (var orgType in result.OrgTypes) { foreach (var org in orgType.Organizations) { if (contactsDictionary.ContainsKey(org.OrgId)) { org.Contacts = contactsDictionary[org.OrgId].OrderBy(c => c.ContactOrder).Select( c => new OneOrganizationContact { ContactId = c.ContactId, Contact = c.Contact, Email = c.Email, Phone = c.Phone, Title = c.Title }).ToArray(); } else { org.Contacts = new OneOrganizationContact[0]; } } } // attach mission urls var missionUrlsDictionary = OrganizationMissionUrls.GetAllData().GroupBy(r => r.OrgId) .ToDictionary(g => g.Key, g => g); foreach (var orgType in result.OrgTypes) { foreach (var org in orgType.Organizations) { if (missionUrlsDictionary.ContainsKey(org.OrgId)) { org.MissionUrls = missionUrlsDictionary[org.OrgId].OrderBy(c => c.UrlOrder) .Select(c => new OneOrganizationMissionUrl { OrgMissionUrlId = c.OrgMissionUrlId, Url = c.Url }).ToArray(); } else { org.MissionUrls = new OneOrganizationMissionUrl[0]; } } } return(result); }