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