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