//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 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 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, 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 DataTable GetBallotReportData(string electionKey,
string countyCode, int commandTimeout = -1)
{
electionKey = Elections.GetStateElectionKeyFromKey(electionKey) + "%";
const string cmdText = "SELECT r.ReferendumKey,r.ReferendumTitle,r.ReferendumDesc," +
" r.OrderOnBallot,r.CountyCode,r.LocalCode,r.ElectionKey,l.LocalDistrict" +
" FROM Referendums r" +
" LEFT JOIN LocalDistricts l ON l.StateCode=r.StateCode" +
" AND l.CountyCode=r.CountyCode AND l.LocalCode=r.LocalCode" +
" WHERE r.ElectionKey LIKE @ElectionKey" +
" AND (r.CountyCode='' OR r.CountyCode=@CountyCode)" +
" 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 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 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 decimal GetAdRate(string electionKey, string officeKey)
{
var adRates = GetAdRates(officeKey);
return(Elections.IsPrimaryElection(electionKey)
? adRates.PrimaryAdRate
: adRates.GeneralAdRate);
}
public static DataTable GetSampleBallotIssues(string electionKey, string congress,
string stateSenate, string stateHouse, string countyCode, int commandTimeout = -1)
{
electionKey = Elections.GetStateElectionKeyFromKey(electionKey) + "%";
const string columnList =
"a.Answer,a.IssueKey,a.QuestionKey,i.Issue,i.IssueLevel,i.IssueOrder," +
"q.Question,q.QuestionOrder";
var cmdText =
string.Format(
"SELECT {0}, ep.PoliticianKey, 0 AS IsRunningMate FROM ElectionsPoliticians ep" +
" INNER JOIN Offices o ON o.OfficeKey=ep.OfficeKey" +
" INNER JOIN Answers a ON a.PoliticianKey=ep.PoliticianKey" +
" AND TRIM(a.Answer) <> ''" +
" INNER JOIN Issues i ON i.IssueKey=a.IssueKey AND i.IsIssueOmit=0" +
" INNER JOIN Questions q ON q.QuestionKey=a.QuestionKey" +
" AND q.IsQuestionOmit=0" +
" 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)" +
" UNION SELECT {0},ep.RunningMateKey AS PoliticianKey, 1 AS IsRunningMate FROM ElectionsPoliticians ep" +
" INNER JOIN Offices o ON o.OfficeKey=ep.OfficeKey" +
" INNER JOIN Answers a ON a.PoliticianKey=ep.RunningMateKey" +
" AND TRIM(a.Answer) <> ''" +
" INNER JOIN Issues i ON i.IssueKey=a.IssueKey AND i.IsIssueOmit=0" +
" INNER JOIN Questions q ON q.QuestionKey=a.QuestionKey" +
" AND q.IsQuestionOmit=0" +
" 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)" +
" ORDER BY IssueLevel,IssueOrder,Issue,QuestionOrder,Question", columnList);
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, "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);
}
}
public static void RemoveOrphanedRow(string electionKey, int commandTimeout = -1)
{
var defaultElectionKey = Elections.GetDefaultElectionKeyFromKey(electionKey);
const string cmdText = "SELECT COUNT(*) FROM Elections WHERE ElectionKey LIKE @ElectionKey";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
VoteDb.AddCommandParameter(cmd, "ElectionKey", defaultElectionKey + "%");
var count = Convert.ToInt32(VoteDb.ExecuteScalar(cmd));
if (count == 0)
{
DeleteByDefaultElectionKey(defaultElectionKey);
}
}
// ReSharper disable MemberCanBePrivate.Global
// ReSharper disable MemberCanBeProtected.Global
// ReSharper disable UnusedMember.Global
// ReSharper disable UnusedMethodReturnValue.Global
// ReSharper disable UnusedAutoPropertyAccessor.Global
public static void CreateEmptyRow(string electionKey, int commandTimeout = -1)
{
var defaultElectionKey = Elections.GetDefaultElectionKeyFromKey(electionKey);
if (!DefaultElectionKeyExists(defaultElectionKey))
{
var stateRow = States.GetData(Elections.GetStateCodeFromKey(electionKey))[0];
Insert(defaultElectionKey, stateRow.ElectionAdditionalInfo.SafeString(),
stateRow.BallotInstructions.SafeString(), VotePage.DefaultDbDate, VotePage.DefaultDbDate,
VotePage.DefaultDbDate, VotePage.DefaultDbDate, VotePage.DefaultDbDate,
VotePage.DefaultDbDate, VotePage.DefaultDbDate, VotePage.DefaultDbDate,
VotePage.DefaultDbDate, commandTimeout);
}
}
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));
}
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);
}
}
