public static void SaveOrganizationTypesData(OrganizationTypesData[] data)
{
var organizationTypesTable = GetAllData();
// delete any missing OrganizationTypes rows
foreach (var otRow in organizationTypesTable)
{
if (data.All(ot => otRow.OrgTypeId != ot.OrgTypeId))
{
var typeId = otRow.OrgTypeId;
otRow.Delete();
// we also delete any organization sub types or email tags with this org types
// note: the application prevents deletion of an org type with any organizations assigned
OrganizationSubTypes.DeleteByOrgTypeId(typeId);
OrganizationEmailTags.DeleteByOrgTypeId(typeId);
}
}
// update or add remaining entries
var otOrder = 0;
foreach (var ot in data)
{
otOrder += 10;
var otRow = organizationTypesTable.Where(r => r.RowState != DataRowState.Deleted)
.FirstOrDefault(r => r.OrgTypeId == ot.OrgTypeId);
if (otRow == null)
{
Insert(ot.OrgTypeId, ot.OrgType, otOrder);
}
else
{
if (otRow.OrgTypeOrder != otOrder)
{
otRow.OrgTypeOrder = otOrder;
}
if (otRow.OrgType != ot.OrgType)
{
otRow.OrgType = ot.OrgType;
}
}
}
UpdateTable(organizationTypesTable);
}
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);
}
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);
}