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