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