public static string GetAvailableLocalKey(string stateCode)
{
var cmdText = "SELECT MAX(LocalKey) FROM LocalDistricts WHERE StateCode=@StateCode";
var cmd = VoteDb.GetCommand(cmdText, 0);
VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
var result = VoteDb.ExecuteScalar(cmd);
if (result == null)
{
cmdText =
"SELECT MAX(LocalKey) FROM LocalDistricts" +
" WHERE StateCode=@StateCode AND LocalKey<'91001'";
cmd = VoteDb.GetCommand(cmdText, 0);
VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
result = VoteDb.ExecuteScalar(cmd);
if (result == null)
{
result = "10000";
}
else if ((string)result == "91000")
{
return(null);
}
}
return((int.Parse((string)result) + 1).ToString());
}
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 int CountOutOfDateHeadshots()
{
const string cmdText = "SELECT COUNT(*) FROM PoliticiansImagesData" +
" WHERE ProfileOriginalDate > HeadshotDate AND PoliticianKey!='NoPhoto'";
var cmd = VoteDb.GetCommand(cmdText);
return(Convert.ToInt32(VoteDb.ExecuteScalar(cmd)));
}
public static void Upsert(string adType, string stateCode, string electionKey,
string officeKey, string adImageName, string adUrl, bool adEnabled, string adMediaType,
string adYouTubeUrl, string adDescription1, string adDescription2,
string adDescriptionUrl, bool adIsPaid, byte[] file)
{
// with file
if (IsNullOrWhiteSpace(adYouTubeUrl))
{
adYouTubeUrl = null;
}
if (IsNullOrWhiteSpace(adDescription1))
{
adDescription1 = null;
}
if (IsNullOrWhiteSpace(adDescription2))
{
adDescription2 = null;
}
if (IsNullOrWhiteSpace(adDescriptionUrl))
{
adDescriptionUrl = null;
}
//if (file == null)
// Upsert(adType, stateCode, electionKey, officeKey, adImageName,adUrl, adEnabled,
// adMediaType, adYouTubeUrl, adDescription1, adDescription2, adDescriptionUrl,
// adIsPaid);
const string cmdText =
"INSERT INTO BannerAds (AdType,StateCode,ElectionKey,OfficeKey,AdImage,AdImageName," +
"AdUrl,AdEnabled,AdMediaType,AdYouTubeUrl,AdDescription1,AdDescription2," +
"AdDescriptionUrl,AdIsPaid)" +
" VALUES(@AdType,@StateCode,@ElectionKey,@OfficeKey,@AdImage,@AdImageName,@AdUrl," +
"@AdEnabled,@AdMediaType,@AdYouTubeUrl,@AdDescription1,@AdDescription2," +
"@AdDescriptionUrl,@AdIsPaid)" +
" ON DUPLICATE KEY UPDATE AdImage=VALUES(AdImage),AdImageName=VALUES(AdImageName)," +
"AdUrl=VALUES(AdUrl),AdEnabled=VALUES(AdEnabled),AdMediaType=VALUES(AdMediaType)," +
"AdYouTubeUrl=VALUES(AdYouTubeUrl),AdDescription1=VALUES(AdDescription1)," +
"AdDescription2=VALUES(AdDescription2),AdDescriptionUrl=VALUES(AdDescriptionUrl)," +
"AdIsPaid=VALUES(AdIsPaid)";
var cmd = VoteDb.GetCommand(cmdText);
VoteDb.AddCommandParameter(cmd, "AdType", adType);
VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
VoteDb.AddCommandParameter(cmd, "ElectionKey", electionKey);
VoteDb.AddCommandParameter(cmd, "OfficeKey", officeKey);
VoteDb.AddCommandParameter(cmd, "AdImage", file);
VoteDb.AddCommandParameter(cmd, "AdImageName", adImageName);
VoteDb.AddCommandParameter(cmd, "AdUrl", adUrl);
VoteDb.AddCommandParameter(cmd, "AdEnabled", adEnabled);
VoteDb.AddCommandParameter(cmd, "AdMediaType", adMediaType);
VoteDb.AddCommandParameter(cmd, "AdYouTubeUrl", adYouTubeUrl);
VoteDb.AddCommandParameter(cmd, "AdDescription1", adDescription1);
VoteDb.AddCommandParameter(cmd, "AdDescription2", adDescription2);
VoteDb.AddCommandParameter(cmd, "AdDescriptionUrl", adDescriptionUrl);
VoteDb.AddCommandParameter(cmd, "AdIsPaid", adIsPaid);
VoteDb.ExecuteScalar(cmd);
}
// ReSharper disable MemberCanBePrivate.Global
// ReSharper disable MemberCanBeProtected.Global
// ReSharper disable UnusedMember.Global
public static int CountAllActualOffices(int commandTimeout = -1)
{
const string cmdText =
"SELECT COUNT(*) FROM Offices" +
" WHERE IsVirtual=0";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
var result = VoteDb.ExecuteScalar(cmd);
return(Convert.ToInt32(result));
}
// ReSharper disable MemberCanBePrivate.Global
// ReSharper disable MemberCanBeProtected.Global
// ReSharper disable UnusedMember.Global
// ReSharper disable UnusedMethodReturnValue.Global
// ReSharper disable UnusedAutoPropertyAccessor.Global
public static int CountData(OfficesAdminReportViewOptions options,
int commandTimeout = -1)
{
var cmdText = "SELECT COUNT(*) FROM OfficesAdminReportView" +
options.BuildWhereClause();
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
var result = VoteDb.ExecuteScalar(cmd);
return(Convert.ToInt32(result));
}
public static int CountPersonalAnswers(int commandTimeout = -1)
{
const string cmdText =
"SELECT COUNT(*) FROM Answers" +
" WHERE IssueKey = 'ALLPersonal'";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
var result = VoteDb.ExecuteScalar(cmd);
return(Convert.ToInt32(result));
}
public static bool OfficeKeyExists(string officeKey)
{
const string cmdText = "SELECT COUNT(*) FROM ElectionsOffices WHERE OfficeKey=@OfficeKey";
var cmd = VoteDb.GetCommand(cmdText, -1);
VoteDb.AddCommandParameter(cmd, "OfficeKey", officeKey);
var result = VoteDb.ExecuteScalar(cmd);
return(Convert.ToInt32(result) != 0);
}
public static bool QuestionExists(string question, string issueLevel)
{
const string cmdText = "SELECT COUNT(*) FROM Questions WHERE Question=@Question AND" +
"SUBSTRING(QuestionKey,1,1)=@IssueLevel";
var cmd = VoteDb.GetCommand(cmdText, -1);
VoteDb.AddCommandParameter(cmd, "Question", question);
VoteDb.AddCommandParameter(cmd, "IssueLevel", issueLevel);
var result = VoteDb.ExecuteScalar(cmd);
return(Convert.ToInt32(result) != 0);
}
// ReSharper disable MemberCanBePrivate.Global
// ReSharper disable MemberCanBeProtected.Global
// ReSharper disable UnusedMember.Global
public static int CountAllCountyOffices(string stateCode,
int commandTimeout = -1)
{
const string cmdText =
"SELECT COUNT(*) FROM Offices" +
" WHERE StateCode=@StateCode AND CountyCode<>'' AND LocalCode=''";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
var result = VoteDb.ExecuteScalar(cmd);
return(Convert.ToInt32(result));
}
public static string GetPrefix([NotNull] string stateCode, [NotNull] string tigerCode)
{
const string cmdText = "SELECT SUBSTR(CityCouncilCode,1,2) AS Prefix" +
" FROM vote.CityCouncil" +
" WHERE StateCode=@StateCode AND TigerCode=@TigerCode" +
" GROUP BY SUBSTR(CityCouncilCode, 1, 2)";
var cmd = VoteDb.GetCommand(cmdText, 0);
VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
VoteDb.AddCommandParameter(cmd, "TigerCode", tigerCode);
return(VoteDb.ExecuteScalar(cmd) as string);
}
public static int CountActiveIssueAnswers(int commandTimeout = -1)
{
const string cmdText =
"SELECT COUNT(*) FROM Answers a" +
" INNER JOIN Issues i ON i.IssueKey=a.IssueKey" +
" INNER JOIN Questions q ON q.QuestionKey=a.QuestionKey" +
" WHERE NOT a.IssueKey IN ('ALLBio','ALLPersonal')" +
" AND i.IsIssueOmit=0 AND q.IsQuestionOmit=0";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
var result = VoteDb.ExecuteScalar(cmd);
return(Convert.ToInt32(result));
}
public static decimal GetTotalAmountByEmail(string email)
{
const string cmdText = "SELECT SUM(Amount) FROM Donations WHERE Email=@Email";
var cmd = VoteDb.GetCommand(cmdText, -1);
VoteDb.AddCommandParameter(cmd, "Email", email);
var result = VoteDb.ExecuteScalar(cmd);
if (result == null || result == DBNull.Value)
{
return(0);
}
return((decimal)result);
}
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);
}
}
public static int GetNextOrderOnBallot(string electionKey)
{
var cmdText =
"SELECT OrderOnBallot FROM Referendums WHERE ElectionKey=@ElectionKey" +
" ORDER BY OrderOnBallot DESC";
cmdText = VoteDb.InjectSqlLimit(cmdText, 1);
var cmd = VoteDb.GetCommand(cmdText);
VoteDb.AddCommandParameter(cmd, "ElectionKey", electionKey);
var result = VoteDb.ExecuteScalar(cmd);
return(result == null ? 10 : Convert.ToInt32(result) + 10);
}
public static string GetPrefix([NotNull] string stateCode, [NotNull] string tigerType,
[NotNull] string tigerCode)
{
const string cmdText = "SELECT SUBSTR(SchoolDistrictDistrictCode,1,3) AS Prefix" +
" FROM SchoolDistrictDistricts" +
" WHERE StateCode=@StateCode AND TigerType=@TigerType AND TigerCode=@TigerCode" +
" GROUP BY SUBSTR(SchoolDistrictDistrictCode, 1, 3)";
var cmd = VoteDb.GetCommand(cmdText, 0);
VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
VoteDb.AddCommandParameter(cmd, "TigerType", tigerType);
VoteDb.AddCommandParameter(cmd, "TigerCode", tigerCode);
return(VoteDb.ExecuteScalar(cmd) as string);
}
public static int CountPersonalAnswersNew(int commandTimeout = -1)
{
// the double select is to eliminate double counting
// when a question is assigned to more than one issue
var cmdText =
"SELECT COUNT(*) FROM" +
" (SELECT COUNT(*) FROM Answers2 a" +
" INNER JOIN Questions2 q ON q.QuestionId = a.QuestionId" +
" INNER JOIN IssuesQuestions iq ON iq.QuestionId = a.QuestionId" +
" INNER JOIN Issues2 i ON i.IssueId = iq.IssueId" +
$" WHERE i.IssueId={Issues.IssueId.Reasons.ToInt()}" +
" AND i.IsIssueOmit = 0 AND q.IsQuestionOmit = 0" +
" GROUP BY a.PoliticianKey, a.QuestionId, a.Sequence) as q";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
var result = VoteDb.ExecuteScalar(cmd);
return(Convert.ToInt32(result));
}
public static int GetNextSequence(string politicianKey, string questionKey)
{
const string cmdText =
"SELECT MAX(Sequence) FROM Answers WHERE PoliticianKey=@PoliticianKey AND QuestionKey=@QuestionKey";
var cmd = VoteDb.GetCommand(cmdText);
using (var cn = VoteDb.GetOpenConnection())
{
cmd.Connection = cn;
VoteDb.AddCommandParameter(cmd, "PoliticianKey", politicianKey);
VoteDb.AddCommandParameter(cmd, "QuestionKey", questionKey);
var result = VoteDb.ExecuteScalar(cmd);
if ((result == null) || (result == DBNull.Value))
{
return(0);
}
return(Convert.ToInt32(result) + 1);
}
}
public static string GetLatestViewableElectionKeyStateByOfficeKey(
string officeKey, string defaultValue = null)
{
var cmdText = "SELECT ElectionsOffices.ElectionKeyState" +
" FROM Elections,ElectionsOffices" +
" WHERE ElectionsOffices.OfficeKey=@OfficeKey" +
" AND Elections.IsViewable='1'" +
" ORDER BY ElectionsOffices.ElectionKeyState DESC";
cmdText = VoteDb.InjectSqlLimit(cmdText, 1);
var cmd = VoteDb.GetCommand(cmdText, -1);
VoteDb.AddCommandParameter(cmd, "OfficeKey", officeKey);
var result = VoteDb.ExecuteScalar(cmd);
if ((result == null) || (result == DBNull.Value))
{
return(defaultValue);
}
return(result as string);
}
public static string GetNextVoteIdForState(string stateCode)
{
const string cmdText = "SELECT MAX(LocalId) FROM LocalIdsCodes" +
" WHERE StateCode=@StateCode AND LocalType='V'";
var cmd = VoteDb.GetCommand(cmdText);
VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
var max = VoteDb.ExecuteScalar(cmd) as string;
switch (max)
{
case null:
return("10001"); // the first
case "99999":
throw new Exception($"Out of Vote id numbers for state {stateCode}");
default:
// ReSharper disable once AssignNullToNotNullAttribute
return((int.Parse(max) + 1).ToString());
}
}
public static int GetPoliticianCountForOfficeInElection(string electionKey,
string officeKey, int commandTimeout = -1)
{
const string cmdText =
"SELECT COUNT(*) FROM ElectionsPoliticians" +
" WHERE ElectionKey=@ElectionKey AND OfficeKey=@OfficeKey";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
using (var cn = VoteDb.GetOpenConnection())
{
cmd.Connection = cn;
VoteDb.AddCommandParameter(cmd, "ElectionKey", electionKey);
VoteDb.AddCommandParameter(cmd, "OfficeKey", officeKey);
var result = VoteDb.ExecuteScalar(cmd);
if ((result == null) || (result == DBNull.Value))
{
return(0);
}
return(Convert.ToInt32(result));
}
}
public static DataTable GetPresidentialCandidatesFromTemplate(
DateTime electionDate, string nationalPartyCode, int commandTimeout = -1)
{
const string cmdText1 = "SELECT ElectionKey FROM Elections" +
" WHERE ElectionType='A' AND StateCode='PP'" +
" AND NationalPartyCode=@NationalPartyCode" +
" AND ElectionDate>=@ElectionDate ORDER BY ElectionDate LIMIT 1";
var cmd1 = VoteDb.GetCommand(cmdText1, commandTimeout);
string electionKey;
using (var cn = VoteDb.GetOpenConnection())
{
cmd1.Connection = cn;
VoteDb.AddCommandParameter(cmd1, "ElectionDate", electionDate);
VoteDb.AddCommandParameter(cmd1, "NationalPartyCode", nationalPartyCode);
electionKey = VoteDb.ExecuteScalar(cmd1) as string;
}
if (string.IsNullOrWhiteSpace(electionKey))
{
return(null);
}
const string cmdText2 = "SELECT OfficeKey,PoliticianKey,OrderOnBallot" +
" FROM ElectionsPoliticians" +
" WHERE ElectionKey=@ElectionKey";
var cmd2 = VoteDb.GetCommand(cmdText2, commandTimeout);
var table = new DataTable();
using (var cn = VoteDb.GetOpenConnection())
{
cmd2.Connection = cn;
VoteDb.AddCommandParameter(cmd2, "ElectionKey", electionKey);
DbDataAdapter adapter = new MySqlDataAdapter(cmd2 as MySqlCommand);
adapter.Fill(table);
return(table);
}
}
public static void Upsert(string adType, string stateCode, string electionKey,
string officeKey, string adImageName, string adUrl, bool adEnabled, string adMediaType,
string adYouTubeUrl, string adDescription1, string adDescription2,
string adDescriptionUrl, bool adIsPaid)
{
// no file
const string cmdText =
"INSERT INTO BannerAds (AdType,StateCode,ElectionKey,OfficeKey,AdImageName," +
"AdUrl,AdEnabled,AdMediaType,AdYouTubeUrl,AdDescription1,AdDescription2," +
"AdDescriptionUrl,AdIsPaid)" +
" VALUES(@AdType,@StateCode,@ElectionKey,@OfficeKey,@AdImageName,@AdUrl," +
"@AdEnabled,@AdMediaType,@AdYouTubeUrl,@AdDescription1,@AdDescription2," +
"@AdDescriptionUrl,@AdIsPaid)" +
" ON DUPLICATE KEY UPDATE AdImageName=VALUES(AdImageName)," +
"AdUrl=VALUES(AdUrl),AdEnabled=VALUES(AdEnabled),AdMediaType=VALUES(AdMediaType)," +
"AdYouTubeUrl=VALUES(AdYouTubeUrl),AdDescription1=VALUES(AdDescription1)," +
"AdDescription2=VALUES(AdDescription2),AdDescriptionUrl=VALUES(AdDescriptionUrl)," +
"AdIsPaid=VALUES(AdIsPaid)";
var cmd = VoteDb.GetCommand(cmdText);
VoteDb.AddCommandParameter(cmd, "AdType", adType);
VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
VoteDb.AddCommandParameter(cmd, "ElectionKey", electionKey);
VoteDb.AddCommandParameter(cmd, "OfficeKey", officeKey);
VoteDb.AddCommandParameter(cmd, "AdImageName", adImageName);
VoteDb.AddCommandParameter(cmd, "AdUrl", adUrl);
VoteDb.AddCommandParameter(cmd, "AdEnabled", adEnabled);
VoteDb.AddCommandParameter(cmd, "AdMediaType", adMediaType);
VoteDb.AddCommandParameter(cmd, "AdYouTubeUrl", adYouTubeUrl);
VoteDb.AddCommandParameter(cmd, "AdDescription1", adDescription1);
VoteDb.AddCommandParameter(cmd, "AdDescription2", adDescription2);
VoteDb.AddCommandParameter(cmd, "AdDescriptionUrl", adDescriptionUrl);
VoteDb.AddCommandParameter(cmd, "AdIsPaid", adIsPaid);
VoteDb.ExecuteScalar(cmd);
}
