public static DataTable GetOfficesForCsv(string stateCode)
{
const string cmdText =
"SELECT l.StateCode,l.LocalKey,l.LocalDistrict,o.OfficeKey,o.OfficeLine1,o.OfficeLine2," +
"p.PoliticianKey,p.FName AS FirstName,p.MName AS MiddleName,p.Nickname,p.LName AS LastName," +
"p.Suffix," +
"(SELECT ElectionKey FROM ElectionsPoliticians ep" +
" WHERE ep.OfficeKey = o.OfficeKey" +
" AND NOT SUBSTR(ep.ElectionKey, 11, 1) IN ('A', 'B', 'P', 'Q')" +
" ORDER BY ep.ElectionKey DESC Limit 1) AS ElectionKey" +
" FROM LocalDistricts l" +
" LEFT OUTER JOIN Offices o ON o.StateCode = l.StateCode AND o.LocalKey = l.LocalKey AND" +
" o.IsVirtual = 0 AND o.IsInactive = 0" +
" LEFT OUTER JOIN OfficesOfficials oo ON oo.OfficeKey = o.OfficeKey" +
" LEFT OUTER JOIN Politicians p ON p.PoliticianKey = oo.PoliticianKey" +
" WHERE l.IsLocalDistrictTagForDeletion = 0 AND l.StateCode = @StateCode";
var cmd = VoteDb.GetCommand(cmdText, 0);
VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
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 DataTable GetCountyWideLocalsForStateInCounties(string stateCode)
{
var cmdText =
"SELECT d.StateCode,d.LocalKey,d.LocalDistrict,tpc.CountyCode,c.County," +
"tpc.TigerType,tpc.TigerCode" +
" FROM LocalDistricts d" +
" INNER JOIN LocalIdsCodes lic ON lic.StateCode=d.StateCode AND lic.LocalKey=d.LocalKey" +
$" AND lic.LocalType='{LocalIdsCodes.LocalTypeVote}'" +
" INNER JOIN TigerPlacesCounties tpc ON tpc.StateCode=d.StateCode" +
" AND tpc.TigerCode=lic.LocalId AND tpc.TigerType=lic.LocalType" +
" INNER JOIN Counties c ON c.StateCode=tpc.StateCode AND c.CountyCode=tpc.CountyCode" +
" WHERE d.StateCode=@StateCode";
var cmd = VoteDb.GetCommand(cmdText, 0);
VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
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 List <SimpleListItem> GetSearchDistrictsInState(string stateCode,
string searchString)
{
searchString = searchString.Trim();
const string cmdText = "SELECT LocalKey,LocalDistrict FROM LocalDistricts" +
" WHERE StateCode=@StateCode AND LocalDistrict LIKE @LocalDistrictMatchAny" +
" AND IsLocalDistrictTagForDeletion=0" +
" ORDER BY LocalDistrict LIKE @LocalDistrictMatchStart DESC,LocalDistrict";
var cmd = VoteDb.GetCommand(cmdText, 0);
VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
VoteDb.AddCommandParameter(cmd, "LocalDistrictMatchAny", $"%{searchString}%");
VoteDb.AddCommandParameter(cmd, "LocalDistrictMatchStart", $"{searchString}%");
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>().Select(r => new SimpleListItem
{
Text = r.LocalDistrict(),
Value = r.LocalKey()
}).ToList());
}
}
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 DataTable GetIssuesReportData(string issueLevel, string stateCode,
int commandTimeout = -1)
{
const string cmdText =
"SELECT i.IssueKey,i.IssueOrder,i.Issue,i.IsIssueOmit," +
"q.QuestionKey,q.Question,q.IsQuestionOmit,COUNT(*) AS Count" +
" FROM Issues i" +
" LEFT JOIN Questions q ON q.IssueKey = i.IssueKey" +
" LEFT JOIN Answers a ON a.QuestionKey=q.QuestionKey" +
" WHERE i.StateCode=@StateCode AND i.IssueLevel=@IssueLevel" +
" GROUP BY i.IssueKey,q.QuestionKey" +
" ORDER BY i.IssueOrder,q.QuestionOrder";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
using (var cn = VoteDb.GetOpenConnection())
{
cmd.Connection = cn;
VoteDb.AddCommandParameter(cmd, "IssueLevel", issueLevel);
VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
var table = new DataTable();
DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
adapter.Fill(table);
return(table);
}
}
public static DistrictAddressesViewReader GetDataReaderForAddressExtraction(
ICollection <string> stateCodes, bool nameRequired, bool addressRequired,
bool emailRequired, bool noEmailRequired, bool phoneRequired, DateTime fromDate,
DateTime toDate, string districtColumnName, ICollection <string> districts,
int commandTimeout = -1)
{
var whereClause = Addresses.FormatAddressExtractionWhereClause(
stateCodes, nameRequired, addressRequired, emailRequired, noEmailRequired,
phoneRequired, fromDate, toDate, districtColumnName, districts);
var cmdText = GetSelectCommandText(DistrictAddressesViewTable.ColumnSet.All) +
whereClause + " ORDER BY Zip5, Zip4";
var cn = VoteDb.GetOpenConnection();
var cmd = VoteDb.GetCommand(cmdText, cn, commandTimeout);
if (fromDate != DateTime.MinValue.Date)
{
VoteDb.AddCommandParameter(cmd, "fromDate", fromDate);
}
if (toDate != DateTime.MaxValue.Date)
{
VoteDb.AddCommandParameter(cmd, "toDate", toDate);
}
return(new DistrictAddressesViewReader(cmd.ExecuteReader(), cn));
}
public static List <IGrouping <string, DataRow> > GetOfficesWithCandidatesToReinstate(
string electionKey, int commandTimeout = -1)
{
const string cmdText = "SELECT eir.OfficeKey,eir.PoliticianKey,o.OfficeLine1,o.OfficeLine2," +
"p.FName as FirstName,p.MName as MiddleName,p.Nickname," +
"p.LName as LastName,p.Suffix" +
" FROM ElectionsIncumbentsRemoved eir" +
" INNER JOIN Offices o ON o.OfficeKey=eir.OfficeKey" +
" INNER JOIN Politicians p ON p.PoliticianKey=eir.PoliticianKey" +
" WHERE eir.ElectionKey=@ElectionKey";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
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.AsEnumerable()
.GroupBy(r => r.OfficeKey())
.ToList());
}
public static DataTable GetSearchCandidates(string lastname,
IList <string> keysToSkip, string stateCode = null,
bool fullAlphaNameOnly = false, int commandTimeout = -1)
{
// if stateCode is not supplied, do not search on VowelStrippedName --
// these are presidential candidates (or if it is a single character)
var haveStateCode = StateCache.IsValidStateCode(stateCode);
var alphaName = lastname.StripAccents();
if (!fullAlphaNameOnly)
{
alphaName += "%";
}
var vowelStrippedName = lastname.StripVowels() + "%";
var stateCodeClause = haveStateCode
? "p.StateCode=@StateCode AND"
: string.Empty;
var excludeClause = (keysToSkip == null) || (keysToSkip.Count == 0)
? string.Empty
: "p.PoliticianKey NOT IN ('" + string.Join("','", keysToSkip) + "') AND";
var vowelStrippedClause = haveStateCode && (vowelStrippedName.Length > 2) &&
!fullAlphaNameOnly
? "OR p.VowelStrippedName LIKE @VowelStrippedName"
: string.Empty;
var cmdText =
"SELECT p.Address,p.CityStateZip,p.FName AS FirstName," +
"p.MName as MiddleName,p.LiveOfficeKey," +
"p.LiveOfficeStatus,p.LName AS LName,p.Nickname,p.PoliticianKey," +
"p.StateAddress,p.StateCityStateZip,p.Suffix,p.AlphaName," +
"p.VowelStrippedName,pt.PartyCode,o.OfficeLine1," +
"o.OfficeLine2,o.OfficeLevel,l.LocalDistrict FROM Politicians p" +
" LEFT JOIN Parties pt ON pt.PartyKey=p.PartyKey" +
" LEFT JOIN Offices o ON o.OfficeKey=p.LiveOfficeKey" +
" LEFT JOIN LocalDistricts l ON l.StateCode=o.StateCode" +
" AND l.CountyCode=o.CountyCode AND l.LocalCode=o.LocalCode" +
$" WHERE {stateCodeClause} {excludeClause} (p.AlphaName LIKE @AlphaName {vowelStrippedClause})";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
VoteDb.AddCommandParameter(cmd, "AlphaName", alphaName);
if (haveStateCode)
{
VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
if (vowelStrippedName.Length > 1)
{
VoteDb.AddCommandParameter(cmd, "VowelStrippedName", vowelStrippedName);
}
}
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 OfficesAdminReportViewTable GetData(
OfficesAdminReportViewOptions options, int commandTimeout = -1)
{
var cmdText = SelectAllCommandText + options.BuildWhereClause();
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
return(FillTable(cmd, OfficesAdminReportViewTable.ColumnSet.All));
}
public static AddressesTable GetAllGeocodedData(int commandTimeout = -1)
{
var cmdText = SelectDistrictCodingCommandText +
" WHERE NOT Latitude IS NULL AND Latitude!=0 AND NOT Longitude IS NULL AND Longitude!=0";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
return(FillTable(cmd, AddressesTable.ColumnSet.DistrictCoding));
}
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 IEnumerable <OfficesRow> GetGeneralElectionOffices(int commandTimeout = -1)
{
const string cmdText = "SELECT OfficeKey,OfficeLevel,StateCode,DistrictCode FROM Offices" +
" WHERE CountyCode='' AND LocalCode='' AND OfficeLevel<4 AND IsSpecialOffice=0";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
return(FillTable(cmd, OfficesTable.ColumnSet.GeneralElection));
}
public static OrganizationNotesData[] GetOrganizationNotesData()
{
const string cmdText =
"SELECT ot.OrgTypeId,ot.OrgType,(o.OrgId IS NULL) AS IsNull,o.OrgId,o.OrgTypeId," +
"o.Name FROM OrganizationTypes ot" +
" LEFT OUTER JOIN Organizations o ON o.OrgTypeId = ot.OrgTypeId" +
" ORDER BY ot.OrgTypeOrder,o.Name";
OrganizationNotesData[] result;
var cmd = VoteDb.GetCommand(cmdText);
using (var cn = VoteDb.GetOpenConnection())
{
cmd.Connection = cn;
var table = new DataTable();
DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
adapter.Fill(table);
result = table.Rows.OfType <DataRow>().GroupBy(r => r.OrgTypeId()).Select(g =>
new OrganizationNotesData
{
OrgTypeId = g.First().OrgTypeId(),
OrgType = g.First().OrgType(),
Organizations = g.Where(r => r.OrgId() != 0).Select(r => new OneOrganizationNotes
{
OrgId = r.OrgId(),
Name = r.Name()
}).ToArray()
}).ToArray();
}
// attach notes
var notesDictionary = GetAllData().GroupBy(r => r.OrgId)
.ToDictionary(g => g.Key, g => g);
foreach (var orgType in result)
{
foreach (var org in orgType.Organizations)
{
if (notesDictionary.ContainsKey(org.OrgId))
{
org.Notes = notesDictionary[org.OrgId].OrderByDescending(n => n.DateStamp)
.Select(n => new OneNote
{
Id = n.Id,
DateStamp = n.DateStamp.AsUtc(),
Notes = HttpUtility.HtmlEncode(n.Notes).ReplaceNewLinesWithBreakTags()
})
.ToArray();
}
else
{
org.Notes = new OneNote[0];
}
}
}
return(result);
}
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);
}
public static Dictionary <string, string> GetFocusedNamesDictionary(
DataTable tableIn)
{
if (tableIn.Rows.Count == 0)
{
return(new Dictionary <string, string>());
}
// group the jurisdictions and eliminate dups
var grouped = tableIn.Rows.Cast <DataRow>()
.Select(
row =>
new
{
StateCode = row.StateCode(),
CountyCode = row.CountyCode(),
LocalCode = row.LocalCode()
})
.Distinct()
.GroupBy(i => new { i.StateCode, i.CountyCode })
.GroupBy(g => g.Key.StateCode);
// build the where clause
var statesConditions = new List <string>();
foreach (var stateGroup in grouped)
{
var countiesConditions = new List <string>();
foreach (var countyGroup in stateGroup)
{
var locals = countyGroup.ToList();
var localsCondition = locals.Count == 1
? "='" + locals[0].LocalCode + "'"
: "IN ('" + string.Join("','", locals.Select(l => l.LocalCode)) + "')";
countiesConditions.Add("CountyCode='" + countyGroup.Key.CountyCode +
"' AND LocalCode " + localsCondition);
}
statesConditions.Add("StateCode='" + stateGroup.Key + "' AND (" +
string.Join(" OR ", countiesConditions) + ")");
}
var cmdText = "SELECT StateCode,CountyCode,LocalCode,LocalDistrict" +
" FROM LocalDistricts WHERE " + string.Join(" OR ", statesConditions);
var cmd = VoteDb.GetCommand(cmdText, 0);
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.Cast <DataRow>()
.ToDictionary(
row => row.StateCode() + "|" + row.CountyCode() + "|" + row.LocalCode(),
row => row.LocalDistrict()));
}
}
public static DataTable GetVotersEdgeData(string electionKey,
int commandTimeout = -1)
{
const string columnList =
"ep.ElectionKey, ep.OfficeKey,ep.PoliticianKey,ep.RunningMateKey,o.DistrictCode," +
"o.OfficeLevel,o.OfficeLine1,o.OfficeLine2," +
//"p.Accomplishments," +
"p.Address,p.BallotPediaWebAddress,p.BloggerWebAddress," +
"p.CityStateZip," +
//"p.Civic," +
"p.DateOfBirth," +
//"p.Education," +
"p.EmailAddr," +
"p.FacebookWebAddress,p.FlickrWebAddress,p.FName AS FirstName," +
"p.GeneralStatement," +
//"p.GooglePlusWebAddress," +
//"p.LDSAddress,p.LDSCityStateZip,p.LDSEmailAddr,p.LDSPhone,p.LDSWebAddr," +
"p.LinkedInWebAddress,p.LName AS LastName," +
//"p.Military," +
"p.MName AS MiddleName,p.Nickname," +
//"p.Personal," +
"p.Phone," +
"p.PinterestWebAddress," +
//"p.Political,p.Profession,p.Religion," +
"p.RSSFeedWebAddress,p.StateAddress,p.StateCityStateZip," +
"p.StateEmailAddr,p.StatePhone,p.StateWebAddr,p.Suffix," +
"p.TwitterWebAddress,p.VimeoWebAddress,p.WebAddr," +
"p.WebstagramWebAddress,p.WikipediaWebAddress,p.YouTubeWebAddress," +
"pt.PartyName,pt.PartyCode";
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" +
" WHERE ElectionKey=@ElectionKey" +
" 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" +
" WHERE ElectionKey=@ElectionKey" +
" ORDER BY OfficeLevel,OfficeLine1,OfficeLine2,LastName,FirstName", columnList);
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
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 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 int DeleteByEmailDate(string email, DateTime date)
{
const string cmdText = "DELETE FROM Donations WHERE Email=@email AND Date=@date";
var cmd = VoteDb.GetCommand(cmdText);
VoteDb.AddCommandParameter(cmd, "Email", email);
VoteDb.AddCommandParameter(cmd, "Date", date);
return(VoteDb.ExecuteNonQuery(cmd));
}
public static PoliticiansTable GetYouTubeRefreshData(int commandTimeout = -1)
{
const string cmdText = "SELECT PoliticianKey,YouTubeWebAddress,YouTubeDescription," +
"YouTubeRunningTime,YouTubeDate,YouTubeAutoDisable FROM Politicians" +
" WHERE YouTubeWebAddress!=''";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
return(FillTable(cmd, PoliticiansTable.ColumnSet.YouTubeRefresh));
}
public static PoliticiansImagesDataTable GetDataForOutOfDateHeadshots(
int commandTimeout = -1)
{
const string cmdText =
"SELECT PoliticianKey,ProfileOriginalDate,HeadshotDate,HeadshotResizeDate" +
" FROM PoliticiansImagesData" + " WHERE ProfileOriginalDate > HeadshotDate AND PoliticianKey!='NoPhoto'";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
return(FillTable(cmd, PoliticiansImagesDataTable.ColumnSet.All));
}
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 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));
}
// 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));
}
public static QuestionsTable GetNonOmittedDataByIssueKey(string issueKey,
int commandTimeout = -1)
{
var cmdText = SelectAllCommandText + " WHERE IssueKey=@IssueKey" +
" AND IsQuestionOmit = 0" + " ORDER BY QuestionOrder";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
VoteDb.AddCommandParameter(cmd, "IssueKey", issueKey);
return(FillTable(cmd, QuestionsTable.ColumnSet.All));
}
// 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 ReferendumsTable GetElectionReportSummaryData(
string electionKey, int commandTimeout = -1)
{
var cmdText = SelectSummaryCommandText + " WHERE ElectionKey=@ElectionKey" +
" AND IsReferendumTagForDeletion = 0" + " ORDER BY OrderOnBallot";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
VoteDb.AddCommandParameter(cmd, "ElectionKey", electionKey);
return(FillTable(cmd, ReferendumsTable.ColumnSet.Summary));
}
public static DataTable GetCompareCandidateIssues(string electionKey, string officeKey,
string questionKey = null, int commandTimeout = -1)
{
//const string columnList =
// "a.Answer,a.IssueKey,a.QuestionKey,a.Sequence,a.Source,a.DateStamp,a.YouTubeUrl,a.YouTubeSource," +
// "a.YouTubeDescription,a.YouTubeRunningTime,a.YouTubeSourceUrl,a.YouTubeDate,a.YouTubeRefreshTime," +
// "a.YouTubeAutoDisable,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 {1}" +
" AND (TRIM(a.Answer)<>''" +
" OR TRIM(a.YouTubeUrl)<>'' AND NOT a.YouTubeUrl IS NULL" +
" AND (a.YouTubeAutoDisable IS NULL OR a.YouTubeAutoDisable=''))" +
" 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=@ElectionKey AND ep.OfficeKey=@OfficeKey" +
" AND i.IssueLevel IN ('A', GetIssueLevel(@OfficeKey))" +
" 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 {1}" +
" AND (TRIM(a.Answer) <> ''" +
" OR TRIM(a.YouTubeUrl)<>'' AND NOT a.YouTubeUrl IS NULL" +
" AND (a.YouTubeAutoDisable IS NULL OR a.YouTubeAutoDisable=''))" +
" 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=@ElectionKey AND ep.OfficeKey=@OfficeKey" +
" AND i.IssueLevel IN ('A', GetIssueLevel(@OfficeKey))" +
" ORDER BY IssueLevel,IssueOrder,Issue,QuestionOrder,Question,DateStamp DESC",
IssueColumnList,
questionKey == null ? string.Empty : "AND a.QuestionKey=@QuestionKey");
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, "OfficeKey", officeKey);
if (questionKey != null)
{
VoteDb.AddCommandParameter(cmd, "QuestionKey", questionKey);
}
DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
adapter.Fill(table);
return(table);
}
}
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);
}
public static Dictionary <string, string> GetCountyAndLocalElections(
string stateElectionKey, int commandTimeout = -1)
{
using (var cn = VoteDb.GetOpenConnection())
{
var altElectionKey = Elections.GetElectionKeyToInclude(stateElectionKey);
const string cmdText =
"SELECT r.CountyCode,r.ElectionKey FROM Referendums r" +
" WHERE r.ElectionKeyState IN (@ElectionKeyState,@AltElectionKey) AND" +
" r.CountyCode<>''" +
" GROUP BY r.CountyCode";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
VoteDb.AddCommandParameter(cmd, "ElectionKeyState", stateElectionKey);
VoteDb.AddCommandParameter(cmd, "AltElectionKey", altElectionKey);
cmd.Connection = cn;
var table = new DataTable();
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
const string cmdText2 = "SELECT r.CountyCode,r.ElectionKey FROM Referendums r" +
" WHERE r.ElectionKeyState IN (@ElectionKeyState,@AltElectionKey) AND" +
" r.LocalKey<>''" +
" GROUP BY r.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 Dictionary <string, SimpleListItem> GetFocusedNamesDictionary(
DataTable tableIn)
{
var rows = tableIn.Rows.OfType <DataRow>()
.Where(r => !IsNullOrWhiteSpace(r.LocalKey())).ToList();
if (rows.Count == 0)
{
return(new Dictionary <string, SimpleListItem>());
}
// group the jurisdictions and eliminate dups
var grouped = rows
.Select(row => new { StateCode = row.StateCode(), LocalKey = row.LocalKey() })
.Distinct().GroupBy(i => i.StateCode);
// build the where clause
var statesConditions = new List <string>();
foreach (var stateGroup in grouped)
{
var locals = stateGroup.ToList();
var localsCondition = "IN ('" + Join("','", locals.Select(l => l.LocalKey)) + "')";
statesConditions.Add("StateCode='" + stateGroup.Key + "' AND LocalKey " +
localsCondition);
}
var cmdText = "SELECT StateCode,LocalKey,LocalDistrict" +
" FROM LocalDistricts WHERE " + Join(" OR ", statesConditions);
var cmd = VoteDb.GetCommand(cmdText, 0);
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.Cast <DataRow>().ToDictionary(
row => row.StateCode() + "|" + row.LocalKey(), row =>
{
string countyCode = null;
var countyName = CountyCache.GetCountyDescription(row.StateCode(),
ref countyCode, row.LocalKey());
return new SimpleListItem
{
Value = countyCode,
Text = row.LocalDistrict() + ", " + countyName
};
}));
}
}