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> 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, bool> HasCountyOrLocalOfficesByCounty(
string stateCode, int commandTimeout = -1)
{
using (var cn = VoteDb.GetOpenConnection())
{
// first create a dictionary of all counties in the state
var dictionary = CountyCache.GetCountiesByState(stateCode)
.ToDictionary(c => c, c => false);
// the first query just gets the county offices
const string cmdText =
"SELECT CountyCode,COUNT(*) AS Count FROM Offices" +
" WHERE StateCode=@StateCode AND CountyCode<>''" +
" GROUP BY CountyCode";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
cmd.Connection = cn;
var table = new DataTable();
DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
adapter.Fill(table);
// counties tagged for deletion won't be in the table
foreach (var c in table.Rows.OfType <DataRow>())
{
if (dictionary.ContainsKey(c.CountyCode()) && c.Count() > 0)
{
dictionary[c.CountyCode()] = true;
}
}
// we only have to query locals for counties that are still false
foreach (var c in dictionary.Where(kvp => !kvp.Value).Select(kvp => kvp.Key).ToList())
{
// we need to do a pre-query to get all the locals in the selected county
var localKeysClause = LocalDistricts.GetLocalKeysForCounty(stateCode, c)
.SqlIn("LocalKey");
var cmdText2 = "SELECT COUNT(*) AS Count FROM Offices" +
$" WHERE StateCode=@StateCode AND {localKeysClause}";
var cmd2 = VoteDb.GetCommand(cmdText2, commandTimeout);
VoteDb.AddCommandParameter(cmd2, "StateCode", stateCode);
cmd2.Connection = cn;
if (Convert.ToInt32(cmd2.ExecuteScalar()) > 0)
{
dictionary[c] = true;
}
}
// only return the trues
return(dictionary.Where(kvp => kvp.Value)
.ToDictionary(kvp => kvp.Key, kvp => kvp.Value));
}
}
public static Dictionary <string, string> GetLocalNamesWithOffices(
string stateCode, string countyCode, int commandTimeout = -1)
{
// we need to do a pre-query to get all the locals in the selected county
var localKeysClause =
LocalDistricts.GetLocalKeysForCounty(stateCode, countyCode).SqlIn("o.LocalKey");
var cmdText = "SELECT o.LocalKey,l.LocalDistrict FROM Offices o" +
" INNER JOIN LocalDistricts l ON l.StateCode = o.StateCode" +
" AND l.localKey = o.LocalKey" +
$" WHERE o.StateCode=@StateCode AND {localKeysClause}" + " GROUP BY o.LocalKey" +
" ORDER BY l.LocalDistrict";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
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.LocalDistrict()));
}
}
