public static void Upsert(string name, string ownerType, string owner, bool isPublic, DateTime createTime, DateTime modTime, string subject, string body, bool isNew = false, int commandTimeout = -1) { const string cmdTemplate = "INSERT INTO EmailTemplates" + " (Name,OwnerType,Owner,IsPublic,CreateTime,ModTime,Requirements,Subject,Body)" + " VALUES (@Name,@OwnerType,@Owner,@IsPublic,@CreateTime,@ModTime,@Requirements,@Subject,@Body)" + " ON DUPLICATE KEY UPDATE IsPublic=VALUES(IsPublic)," + " ModTime=VALUES(ModTime),Requirements=VALUES(Requirements),Subject=VALUES(Subject),Body=VALUES(Body)"; var cmdText = cmdTemplate; if (isNew) { cmdText += ",SelectRecipientOptions=NULL,EmailOptions=NULL"; } var requirements = GetTemplateRequirementsString(subject, body); var cmd = VoteDb.GetCommand(cmdText, commandTimeout); VoteDb.AddCommandParameter(cmd, "Name", name); VoteDb.AddCommandParameter(cmd, "OwnerType", ownerType); VoteDb.AddCommandParameter(cmd, "Owner", owner); VoteDb.AddCommandParameter(cmd, "IsPublic", isPublic); VoteDb.AddCommandParameter(cmd, "CreateTime", createTime); VoteDb.AddCommandParameter(cmd, "ModTime", modTime); VoteDb.AddCommandParameter(cmd, "Requirements", requirements); VoteDb.AddCommandParameter(cmd, "Subject", subject); VoteDb.AddCommandParameter(cmd, "Body", body); VoteDb.ExecuteScalar(cmd); }
public static DataTable GetIssueTopicsByPoliticianKey(string politicianKey, string officeKey = null) { if (IsNullOrWhiteSpace(officeKey)) { officeKey = VotePage.GetPageCache().Politicians.GetLiveOfficeKey(politicianKey); } var(stateCode, countyCode, localKey, level) = Offices.GetIssuesCoding(officeKey); var cmdText = "SELECT i.IssueId,i.Issue,q.QuestionId,q.Question FROM Issues2 i" + " INNER JOIN IssuesQuestions iq ON iq.IssueId = i.IssueId" + " INNER JOIN Questions2 q ON q.QuestionId = iq.QuestionId" + " INNER JOIN QuestionsJurisdictions qj ON qj.QuestionId = q.QuestionId" + " WHERE i.IssueId > 1000 AND i.IsIssueOmit = 0 AND q.IsQuestionOmit = 0" + $" AND {Questions.QuestionsJurisdictionsWhereClause}" + " GROUP BY i.issueId,q.QuestionId" + " ORDER BY IssueOrder,QuestionOrder"; var cmd = VoteDb.GetCommand(cmdText); var table = new DataTable(); using (var cn = VoteDb.GetOpenConnection()) { cmd.Connection = cn; VoteDb.AddCommandParameter(cmd, "Level", level); VoteDb.AddCommandParameter(cmd, "StateCode", stateCode); VoteDb.AddCommandParameter(cmd, "CountyCode", countyCode); VoteDb.AddCommandParameter(cmd, "LocalKey", localKey); VoteDb.AddCommandParameter(cmd, "PoliticianKey", politicianKey); DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand); adapter.Fill(table); return(table); } }
public static DataTable GetPlacesByCountyCode(string stateCode, string countyCode, int commandTimeout = -1) { var cmdText = "SELECT tpc.StateCode,tpc.CountyCode,tpc.TigerCode,tpc.TigerType," + "tp.Name,tp.LongName,lic.localKey" + " FROM vote.TigerPlacesCounties tpc" + " INNER JOIN TigerPlaces tp ON tp.StateCode=tpc.StateCode AND tp.TigerCode=tpc.TigerCode" + $" LEFT OUTER JOIN LocalIdsCodes lic ON lic.LocalType='{LocalIdsCodes.LocalTypeTiger}'" + " AND lic.StateCode=tpc.StateCode" + //" AND lic.localId=tpc.TigerCode AND lic.CountyCode=tpc.CountyCode" + " AND lic.localId=tpc.TigerCode" + $" WHERE tpc.StateCode=@StateCode AND tpc.CountyCode=@CountyCode AND tpc.TigerType='{TigerTypePlace}'" + " AND NOT tp.Lsad IN ('22','57')" + " ORDER BY tp.LongName"; var cmd = VoteDb.GetCommand(cmdText, commandTimeout); var table = new DataTable(); using (var cn = VoteDb.GetOpenConnection()) { cmd.Connection = cn; VoteDb.AddCommandParameter(cmd, "StateCode", stateCode); VoteDb.AddCommandParameter(cmd, "CountyCode", countyCode); DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand); adapter.Fill(table); return(table); } }
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, 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 GetElectionCsvReferendumData(string electionKey) { const string cmdText = "SELECT r.ElectionKey,r.CountyCode,r.LocalKey,r.ReferendumTitle,r.ReferendumDesc," + "r.ReferendumDetail,r.ReferendumDetailUrl,r.ReferendumFullText,r.ReferendumFullTextUrl," + "r.IsPassed,e.ElectionDesc,e.ElectionDate,c.County,l.LocalDistrict" + " FROM Referendums r" + " INNER JOIN Elections e ON e.ElectionKey=r.ElectionKey" + " LEFT OUTER JOIN Counties c ON c.StateCode = r.StateCode AND c.CountyCode=r.CountyCode" + " LEFT OUTER JOIN LocalDistricts l ON l.StateCode = r.StateCode AND l.LocalKey=r.LocalKey" + " WHERE r.ElectionKeyState=@ElectionKey" + " ORDER BY r.OrderOnBallot"; var cmd = VoteDb.GetCommand(cmdText); var table = new DataTable(); using (var cn = VoteDb.GetOpenConnection()) { cmd.Connection = cn; VoteDb.AddCommandParameter(cmd, "ElectionKey", electionKey); 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 DataTable GetElectionOffices(string electionKey, int commandTimeout = -1) { const string cmdText = "SELECT o.OfficeKey,o.DistrictCode," + //"o.DistrictCodeAlpha," + "o.OfficeLevel," + "o.OfficeLine1,o.OfficeLine2,o.OfficeOrderWithinLevel" + " FROM ElectionsOffices eo" + " INNER JOIN Offices o ON o.OfficeKey=eo.OfficeKey" + " WHERE eo.ElectionKey=@ElectionKey ORDER BY o.OfficeLevel," + "o.DistrictCode,o.OfficeOrderWithinLevel," + //"o.DistrictCodeAlpha," + "o.OfficeLine1,o.OfficeLine2"; var cmd = VoteDb.GetCommand(cmdText, commandTimeout); VoteDb.AddCommandParameter(cmd, "ElectionKey", electionKey); 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 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 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 OfficeKeyInfo GetPreviousOfficeKeyInfoByRunningMateKey( string runningMateKey, int commandTimeout = -1) { var cmdText = "SELECT ElectionsPoliticians.ElectionKey,ElectionsPoliticians.OfficeKey" + " FROM ElectionsPoliticians,Elections" + " WHERE ElectionsPoliticians.RunningMateKey=@RunningMateKey" + " AND ElectionsPoliticians.ElectionKey = Elections.ElectionKey" + " AND Elections.ElectionDate<@Today" + " ORDER BY Elections.ElectionDate DESC"; cmdText = VoteDb.InjectSqlLimit(cmdText, 1); var cmd = VoteDb.GetCommand(cmdText, commandTimeout); VoteDb.AddCommandParameter(cmd, "RunningMateKey", runningMateKey); VoteDb.AddCommandParameter(cmd, "Today", DateTime.Today); var table = FillTable(cmd, ElectionsPoliticiansTable.ColumnSet.OfficeKey); if (table.Count == 0) { return(OfficeKeyInfo.Empty); } return(new OfficeKeyInfo { ElectionKey = table[0].ElectionKey, OfficeKey = table[0].OfficeKey }); }
public static OfficeKeyInfo GetFutureOfficeKeyInfoByRunningMateKey( string runningMateKey, bool isViewable, int commandTimeout = -1) { var cmdText = "SELECT ElectionsPoliticians.ElectionKey,ElectionsPoliticians.OfficeKey" + " FROM ElectionsPoliticians,Elections" + " WHERE ElectionsPoliticians.RunningMateKey=@RunningMateKey" + " AND ElectionsPoliticians.ElectionKey = Elections.ElectionKey" + " AND Elections.ElectionDate>=@Today" + " AND Elections.IsViewable=@IsViewable" + " ORDER BY Elections.ElectionDate"; cmdText = VoteDb.InjectSqlLimit(cmdText, 1); var cmd = VoteDb.GetCommand(cmdText, commandTimeout); VoteDb.AddCommandParameter(cmd, "RunningMateKey", runningMateKey); VoteDb.AddCommandParameter(cmd, "Today", DateTime.Today.AddDays(-DaysAfterElectionToShowAsUpcoming)); VoteDb.AddCommandParameter(cmd, "IsViewable", isViewable); var table = FillTable(cmd, ElectionsPoliticiansTable.ColumnSet.OfficeKey); if (table.Count == 0) { return(OfficeKeyInfo.Empty); } return(new OfficeKeyInfo { ElectionKey = table[0].ElectionKey, OfficeKey = table[0].OfficeKey }); }
public static DataTable GetOtherCountyReferences(string stateCode, string countyCode, string localKey, int commandTimeout = -1) { var cmdText = "SELECT c.StateCode,c.CountyCode,c.County" + " FROM LocalIdsCodes lic" + " INNER JOIN TigerPlacesCounties tpc ON tpc.StateCode = lic.StateCode" + " AND tpc.TigerCode = lic.LocalId" + " INNER JOIN Counties c ON c.StateCode = tpc.StateCode AND c.CountyCode = tpc.CountyCode" + " AND (tpc.TigerType = lic.LocalType OR" + $" tpc.TigerType IN ('{TigerPlacesCounties.TigerTypePlace}', '{TigerPlacesCounties.TigerTypeCousub}')" + $" AND lic.LocalType = '{LocalTypeTiger}')" + " WHERE lic.StateCode = @StateCode AND lic.LocalKey = @LocalKey" + " AND tpc.CountyCode != @CountyCode"; var cmd = VoteDb.GetCommand(cmdText, commandTimeout); var table = new DataTable(); using (var cn = VoteDb.GetOpenConnection()) { cmd.Connection = cn; VoteDb.AddCommandParameter(cmd, "StateCode", stateCode); VoteDb.AddCommandParameter(cmd, "CountyCode", countyCode); VoteDb.AddCommandParameter(cmd, "LocalKey", localKey); DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand); adapter.Fill(table); return(table); } }