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