//public static Dictionary<string, string> GetCountyElections(
// string stateElectionKey, int commandTimeout = -1)
//{
// const string cmdText =
// "SELECT CountyCode,ElectionKey FROM ElectionsOffices" +
// " WHERE ElectionKeyState=@ElectionKeyState AND CountyCode<>'' AND LocalCode=''" +
// " GROUP BY CountyCode";
// var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
// VoteDb.AddCommandParameter(cmd, "ElectionKeyState", stateElectionKey);
// using (var cn = VoteDb.GetOpenConnection())
// {
// cmd.Connection = cn;
// var table = new DataTable();
// DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
// adapter.Fill(table);
// return table.Rows.OfType<DataRow>()
// .ToDictionary(row => row["CountyCode"] as string,
// row => row["ElectionKey"] as string);
// }
//}
public static Dictionary <string, string> GetCountyAndLocalElections(
string stateElectionKey, bool allowEmptyOffices = false, int commandTimeout = -1)
{
var altElectionKey = Elections.GetElectionKeyToInclude(stateElectionKey);
var cmdText =
"SELECT eo.CountyCode,eo.ElectionKeyCounty AS ElectionKey FROM ElectionsOffices eo" +
(allowEmptyOffices
? string.Empty
: " INNER JOIN ElectionsPoliticians ep ON ep.ElectionKey=eo.ElectionKey") +
" WHERE eo.ElectionKeyState IN (@ElectionKeyState,@AltElectionKey) AND eo.CountyCode<>''" +
" GROUP BY eo.CountyCode";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
VoteDb.AddCommandParameter(cmd, "ElectionKeyState", stateElectionKey);
VoteDb.AddCommandParameter(cmd, "AltElectionKey", altElectionKey);
using (var cn = VoteDb.GetOpenConnection())
{
cmd.Connection = cn;
var table = new DataTable();
DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
adapter.Fill(table);
return(table.Rows.OfType <DataRow>()
.ToDictionary(row => row["CountyCode"] as string,
row => row["ElectionKey"] as string));
}
}
public static Dictionary <string, string> GetLocalElections(string stateElectionKey,
string countyCode, IEnumerable <string> localKeys = null, int commandTimeout = -1)
{
if (localKeys == null)
{
localKeys =
LocalDistricts.GetLocalKeysForCounty(Elections.GetStateCodeFromKey(stateElectionKey),
countyCode);
}
var localKeysClause = localKeys.SqlIn("r.LocalKey");
var altElectionKey = Elections.GetElectionKeyToInclude(stateElectionKey);
var cmdText = "SELECT r.LocalKey,r.ElectionKey AS ElectionKey FROM Referendums r" +
$" WHERE r.ElectionKeyState IN (@ElectionKeyState,@AltElectionKey) AND {localKeysClause}" +
" GROUP BY r.LocalKey";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
VoteDb.AddCommandParameter(cmd, "ElectionKeyState", stateElectionKey);
VoteDb.AddCommandParameter(cmd, "AltElectionKey", altElectionKey);
VoteDb.AddCommandParameter(cmd, "CountyCode", countyCode);
using (var cn = VoteDb.GetOpenConnection())
{
cmd.Connection = cn;
var table = new DataTable();
DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
adapter.Fill(table);
return(table.Rows.OfType <DataRow>()
.ToDictionary(row => row.LocalKey(), row => row.ElectionKey()));
}
}
public static Dictionary <string, string> GetCountyAndLocalElections(
string stateElectionKey, bool allowEmptyOffices = false, int commandTimeout = -1)
{
using (var cn = VoteDb.GetOpenConnection())
{
// the first select just gets counties with elections
var altElectionKey = Elections.GetElectionKeyToInclude(stateElectionKey);
var cmdText =
"SELECT eo.CountyCode,eo.ElectionKey FROM ElectionsOffices eo" +
(allowEmptyOffices
? Empty
: " INNER JOIN ElectionsPoliticians ep ON ep.ElectionKey=eo.ElectionKey") +
" WHERE eo.ElectionKeyState IN (@ElectionKeyState,@AltElectionKey) AND" +
" eo.CountyCode<>''" +
" GROUP BY eo.CountyCode";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
VoteDb.AddCommandParameter(cmd, "ElectionKeyState", stateElectionKey);
VoteDb.AddCommandParameter(cmd, "AltElectionKey", altElectionKey);
var table = new DataTable();
cmd.Connection = cn;
DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
adapter.Fill(table);
var list1 =
table.Rows.OfType <DataRow>()
.Select(
row => new { CountyCode = row.CountyCode(), ElectionKey = row.ElectionKey() }).ToList();
// the second select gets locals with elections
var cmdText2 =
"SELECT eo.CountyCode,eo.ElectionKey FROM ElectionsOffices eo" +
(allowEmptyOffices
? Empty
: " INNER JOIN ElectionsPoliticians ep ON ep.ElectionKey=eo.ElectionKey") +
" WHERE eo.ElectionKeyState IN (@ElectionKeyState,@AltElectionKey) AND" +
" eo.LocalKey<>''" +
" GROUP BY eo.ElectionKey";
var cmd2 = VoteDb.GetCommand(cmdText2, commandTimeout);
VoteDb.AddCommandParameter(cmd2, "ElectionKeyState", stateElectionKey);
VoteDb.AddCommandParameter(cmd2, "AltElectionKey", altElectionKey);
var table2 = new DataTable();
cmd2.Connection = cn;
DbDataAdapter adapter2 = new MySqlDataAdapter(cmd2 as MySqlCommand);
adapter2.Fill(table2);
// generate county election keys from the local keys and create list
var list2 =
table2.Rows.OfType <DataRow>()
.SelectMany(row => Elections.GetCountyElectionKeysFromKey(row.ElectionKey()))
.Select(e => new
{
CountyCode = Elections.GetCountyCodeFromKey(e),
ElectionKey = e
}).ToList();
// concatenate, eliminate dups and return as dictionary
return(list1.Concat(list2)
.GroupBy(i => i.CountyCode)
.ToDictionary(g => g.Key, g => g.First().ElectionKey));
}
}
public static Dictionary <string, string> GetLocalElections(string stateElectionKey,
string countyCode, IEnumerable <string> localKeys = null, bool allowEmptyOffices = false,
int commandTimeout = -1)
{
if (localKeys == null)
{
localKeys =
LocalDistricts.GetLocalKeysForCounty(Elections.GetStateCodeFromKey(stateElectionKey),
countyCode);
}
var localKeysClause = localKeys.SqlIn("eo.LocalKey");
var altElectionKey = Elections.GetElectionKeyToInclude(stateElectionKey);
var cmdText =
"SELECT eo.LocalKey,eo.ElectionKey FROM ElectionsOffices eo" +
(allowEmptyOffices
? Empty
: " INNER JOIN ElectionsPoliticians ep ON ep.ElectionKey=eo.ElectionKey") +
$" WHERE eo.ElectionKeyState IN (@ElectionKeyState,@AltElectionKey) AND {localKeysClause}" +
" GROUP BY eo.LocalKey";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
VoteDb.AddCommandParameter(cmd, "ElectionKeyState", stateElectionKey);
VoteDb.AddCommandParameter(cmd, "AltElectionKey", altElectionKey);
VoteDb.AddCommandParameter(cmd, "CountyCode", countyCode);
using (var cn = VoteDb.GetOpenConnection())
{
cmd.Connection = cn;
var table = new DataTable();
DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
adapter.Fill(table);
return(table.Rows.OfType <DataRow>()
.ToDictionary(row => row.LocalKey(), row => row.ElectionKey()));
}
}
public static Dictionary <string, string> GetLocalElections(
string stateElectionKey, string countyCode, int commandTimeout = -1)
{
var altElectionKey = Elections.GetElectionKeyToInclude(stateElectionKey);
const string cmdText = "SELECT r.LocalCode,r.ElectionKey AS ElectionKey FROM Referendums r" +
" WHERE r.ElectionKeyState IN (@ElectionKeyState,@AltElectionKey) AND r.CountyCode=@CountyCode AND r.LocalCode<>''" +
" GROUP BY r.LocalCode";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
VoteDb.AddCommandParameter(cmd, "ElectionKeyState", stateElectionKey);
VoteDb.AddCommandParameter(cmd, "AltElectionKey", altElectionKey);
VoteDb.AddCommandParameter(cmd, "CountyCode", countyCode);
using (var cn = VoteDb.GetOpenConnection())
{
cmd.Connection = cn;
var table = new DataTable();
DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
adapter.Fill(table);
return(table.Rows.OfType <DataRow>()
.ToDictionary(row => row["LocalCode"] as string,
row => row["ElectionKey"] as string));
}
}
public static DataTable GetSampleBallotData(string electionKey,
string congress, string stateSenate, string stateHouse,
string countyCode, bool includeOptionalData = false, int commandTimeout = -1)
{
var electionKeyToInclude = Elections.GetElectionKeyToInclude(electionKey);
if (!string.IsNullOrWhiteSpace(electionKeyToInclude))
{
electionKeyToInclude += "%";
}
electionKey = Elections.GetStateElectionKeyFromKey(electionKey) + "%";
const string columnList =
"ep.ElectionKey, ep.OfficeKey,ep.OrderOnBallot," +
"ep.RunningMateKey,ep.CountyCode,ep.LocalCode,o.DistrictCode," +
"o.IsRunningMateOffice,o.OfficeLevel,o.OfficeLine1,o.OfficeLine2," +
"o.OfficeOrderWithinLevel,o.VoteForWording,o.WriteInLines," +
"o.ElectionPositions,o.PrimaryPositions,o.PrimaryRunoffPositions,o.GeneralRunoffPositions," +
"o.WriteInWording,p.AddOn,p.BallotPediaWebAddress,p.BloggerWebAddress,p.EmailAddr AS Email," +
"p.FacebookWebAddress,p.FlickrWebAddress,p.FName AS FirstName," +
"p.GooglePlusWebAddress," +
"p.LinkedInWebAddress,p.LName AS LastName,p.MName AS MiddleName,p.Nickname,p.PartyKey," +
"p.PinterestWebAddress,p.PoliticianKey,p.RSSFeedWebAddress," +
"p.StateEmailAddr AS StateEmail,p.StateWebAddr AS StateWebAddress," +
"p.Suffix,p.DateOfBirth,p.TwitterWebAddress,p.VimeoWebAddress," +
"p.WebAddr AS WebAddress,p.WebstagramWebAddress,p.WikipediaWebAddress," +
"p.YouTubeWebAddress,pt.PartyCode,pt.PartyName,pt.PartyUrl,pt.IsPartyMajor," +
"bo.OfficeOrder,bo.DemographicClass,l.LocalDistrict," +
"oo.PoliticianKey=ep.PoliticianKey AS IsIncumbent";
const string columns = columnList;
var cmdText =
string.Format(
"SELECT {0}, 0 AS IsRunningMate FROM ElectionsPoliticians ep" +
" INNER JOIN Politicians p ON p.PoliticianKey=ep.PoliticianKey" +
" INNER JOIN Offices o ON o.OfficeKey=ep.OfficeKey" +
" LEFT JOIN Parties pt ON pt.PartyKey = p.PartyKey" +
" INNER JOIN ElectionsBallotOrder bo ON bo.StateCode=ep.StateCode" +
" AND bo.OfficeClass=o.OfficeLevel" +
" LEFT JOIN LocalDistricts l ON l.StateCode=ep.StateCode" +
" AND l.CountyCode=ep.CountyCode AND l.LocalCode=ep.LocalCode" +
" LEFT JOIN OfficesOfficials oo ON oo.OfficeKey=ep.OfficeKey" +
" AND oo.PoliticianKey=ep.PoliticianKey" +
" WHERE (ep.ElectionKey LIKE @ElectionKey OR ep.ElectionKey LIKE @ElectionKeyToInclude)" +
" AND (o.OfficeLevel IN (1,2,4)" +
" OR o.OfficeLevel=3 AND o.DistrictCode=@Congress" +
" OR o.OfficeLevel=5 AND o.DistrictCode=@StateSenate" +
" OR o.OfficeLevel=6 AND o.DistrictCode=@StateHouse" +
" OR o.OfficeLevel>=7)" +
" AND (ep.CountyCode='' OR ep.CountyCode=@CountyCode)" +
" UNION SELECT {0}, 1 AS IsRunningMate FROM ElectionsPoliticians ep" +
" INNER JOIN Politicians p ON p.PoliticianKey=ep.RunningMateKey" +
" INNER JOIN Offices o ON o.OfficeKey=ep.OfficeKey" +
" LEFT JOIN Parties pt ON pt.PartyKey = p.PartyKey" +
" INNER JOIN ElectionsBallotOrder bo ON bo.StateCode=ep.StateCode" +
" AND bo.OfficeClass=o.OfficeLevel" +
" LEFT JOIN LocalDistricts l ON l.StateCode=ep.StateCode" +
" AND l.CountyCode=ep.CountyCode AND l.LocalCode=ep.LocalCode" +
" LEFT JOIN OfficesOfficials oo ON oo.OfficeKey=ep.OfficeKey" +
" AND oo.PoliticianKey=ep.PoliticianKey" +
" WHERE ep.ElectionKey LIKE @ElectionKey" +
" AND (o.OfficeLevel IN (1,2,4)" +
" OR o.OfficeLevel=3 AND o.DistrictCode=@Congress" +
" OR o.OfficeLevel=5 AND o.DistrictCode=@StateSenate" +
" OR o.OfficeLevel=6 AND o.DistrictCode=@StateHouse" +
" OR o.OfficeLevel>=7)" +
" AND (ep.CountyCode='' OR ep.CountyCode=@CountyCode)", columns);
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
var table = new DataTable();
using (var cn = VoteDb.GetOpenConnection())
{
cmd.Connection = cn;
VoteDb.AddCommandParameter(cmd, "ElectionKey", electionKey);
VoteDb.AddCommandParameter(cmd, "ElectionKeyToInclude", electionKeyToInclude);
VoteDb.AddCommandParameter(cmd, "Congress", congress);
VoteDb.AddCommandParameter(cmd, "StateSenate", stateSenate);
VoteDb.AddCommandParameter(cmd, "StateHouse", stateHouse);
VoteDb.AddCommandParameter(cmd, "CountyCode", countyCode);
DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
adapter.Fill(table);
return(table);
}
}
