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);
}
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);
}
