public static DataTable GetBallotReportData(string electionKey, string countyCode,
string district, string place, string elementary, string secondary, string unified,
string cityCouncil, string countySupervisors, string schoolDistrictDistrict, int commandTimeout = -1)
{
var stateCode = Elections.GetStateCodeFromKey(electionKey);
electionKey = Elections.GetStateElectionKeyFromKey(electionKey) + "%";
var districtsClause = LocalIdsCodes.GetLocals(stateCode, countyCode, district, place,
elementary, secondary, unified, cityCouncil, countySupervisors, schoolDistrictDistrict)
.SqlIn("r.LocalKey");
var cmdText = "SELECT r.ReferendumKey,r.ReferendumTitle,r.ReferendumDesc," +
" r.OrderOnBallot,r.CountyCode,r.LocalKey,r.ElectionKey,l.LocalDistrict" +
" FROM Referendums r" +
" LEFT JOIN LocalDistricts l ON l.StateCode=r.StateCode" +
" AND l.LocalKey=r.LocalKey" +
" WHERE r.ElectionKey LIKE @ElectionKey" +
$" AND (r.CountyCode='' AND r.LocalKey='' OR r.CountyCode=@CountyCode OR {districtsClause})" +
" AND r.IsReferendumTagForDeletion=0 ORDER BY r.OrderOnBallot";
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, "CountyCode", countyCode);
DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
adapter.Fill(table);
return(table);
}
}
public static DataTable GetElectedReportData(string stateCode,
string countyCode, string congressionalDistrict, string stateSenateDistrict,
string stateHouseDistrict, string district, string place,
string elementary, string secondary, string unified, string cityCouncil,
string countySupervisors, string schoolDistrictDistrict, bool includeLocals,
int commandTimeout = -1)
{
var localKeys = includeLocals
? LocalIdsCodes.GetLocals(stateCode, countyCode, district, place, elementary,
secondary, unified, cityCouncil, countySupervisors, schoolDistrictDistrict)
: null;
var cmdText = Format(OfficialsReportCmdTextTemplate,
OfficialsReportColumnList,
BuildElectedReportWhereClause(stateCode, countyCode, congressionalDistrict,
stateSenateDistrict, stateHouseDistrict, false, localKeys),
BuildElectedReportWhereClause(stateCode, countyCode, congressionalDistrict,
stateSenateDistrict, stateHouseDistrict, true, localKeys));
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
using (var cn = VoteDb.GetOpenConnection())
{
cmd.Connection = cn;
var table = new DataTable();
DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
adapter.Fill(table);
return(table);
}
}
public static string[] GetCountyElectionKeysFromKey(string electionKey)
{
// there may be multiple counties for a local
var stateCode = GetStateCodeFromKey(electionKey);
if (electionKey.Length < ElectionKeyLengthCounty)
{
return(new string[0]);
}
if (electionKey.Length != ElectionKeyLengthLocal)
{
return new[] { electionKey.Substring(0, ElectionKeyLengthCounty) }
}
;
// return all possible keys
var stateElectionKey = GetStateElectionKeyFromKey(electionKey);
return
(LocalIdsCodes.FindCounties(stateCode, GetLocalKeyFromKey(electionKey))
.Select(c => stateElectionKey + c)
.ToArray());
}
