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 IEnumerable <ElectionsPoliticiansRow> GetPrimaryWinnersForGeneralElection(
string generalElectionKey, DateTime primaryDate, bool isRunoff = false)
{
var electionTypes = new List <string>();
if (isRunoff)
{
electionTypes.Add(Elections.ElectionTypeStatePrimaryRunoff);
}
else
{
electionTypes.Add(Elections.ElectionTypeStatePresidentialPrimary);
electionTypes.Add(Elections.ElectionTypeStatePrimary);
}
var searchElectionKey = Elections.GetStateCodeFromKey(generalElectionKey) +
primaryDate.ToString("yyyyMMdd") +
//(isRunoff ? Elections.ElectionTypeStatePrimaryRunoff : Elections.ElectionTypeStatePrimary) +
"__" /*any type, any party*/ +
Elections.GetCountyCodeFromKey(generalElectionKey) +
Elections.GetLocalCodeFromKey(generalElectionKey);
var cmdText = SelectAllCommandText + " WHERE ElectionKey LIKE @ElectionKey AND IsWinner=1" +
" AND SUBSTR(ElectionKey,11,1) IN ('" + string.Join("','", electionTypes) + "')";
var cmd = VoteDb.GetCommand(cmdText);
VoteDb.AddCommandParameter(cmd, "ElectionKey", searchElectionKey);
return(FillTable(cmd, ElectionsPoliticiansTable.ColumnSet.All));
}
public static IEnumerable <ElectionsPoliticiansRow> GetRunoffAdvancersForElection(
string runoffElectionKey, DateTime electionDate)
{
var searchElectionKey = Elections.GetStateCodeFromKey(runoffElectionKey) +
electionDate.ToString("yyyyMMdd") +
Elections.GetElectionTypeForRunoff(Elections.GetElectionTypeFromKey(runoffElectionKey)) +
Elections.GetNationalPartyCodeFromKey(runoffElectionKey) +
Elections.GetCountyCodeFromKey(runoffElectionKey) +
Elections.GetLocalCodeFromKey(runoffElectionKey);
var cmdText = SelectAllCommandText + " WHERE ElectionKey=@ElectionKey AND AdvanceToRunoff=1";
var cmd = VoteDb.GetCommand(cmdText);
VoteDb.AddCommandParameter(cmd, "ElectionKey", searchElectionKey);
return(FillTable(cmd, ElectionsPoliticiansTable.ColumnSet.All));
}