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);
}
}
public static DataRow GetListItemData(string politicianKey, int commandTimeout = -1)
{
const string cmdText =
"SELECT p.Address,p.CityStateZip,p.FName AS FirstName," +
//"p.LDSAddress,p.LDSCityStateZip," +
"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 p.PoliticianKey=@PoliticianKey";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
VoteDb.AddCommandParameter(cmd, "PoliticianKey", politicianKey);
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.Count == 1 ? table.Rows[0] : null);
}
}
public static DataTable GetCacheData(int contactId)
{
const string cmdText =
"SELECT oc.ContactId,oc.OrgId,oc.Contact,oc.Email,oc.Phone,oc.Title,o.Name,o.OrgAbbreviation," +
"o.Address1,o.Address2,o.City,o.StateCode,o.Zip,ot.OrgType," +
"IF(ost.OrgSubType IS NULL, 'None', ost.OrgSubType) AS OrgSubType," +
"IF(oi.Ideology IS NULL, 'None', oi.Ideology) AS Ideology FROM OrganizationContacts oc" +
" INNER JOIN Organizations o ON o.OrgId = oc.OrgId" +
" INNER JOIN OrganizationTypes ot ON ot.OrgTypeId = o.OrgTypeId" +
" LEFT OUTER JOIN OrganizationSubTypes ost ON ost.OrgTypeId = o.OrgTypeId" +
" AND ost.OrgSubTypeId = o.OrgSubTypeId" +
" LEFT OUTER JOIN OrganizationIdeologies oi ON oi.IdeologyId = o.IdeologyId" +
" WHERE oc.ContactId = @ContactId";
var cmd = VoteDb.GetCommand(cmdText);
VoteDb.AddCommandParameter(cmd, "ContactId", contactId);
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 string GetDesignStringWithSubstitutions(Column dataColumn,
Column isTextColumn, Substitutions substitutions = null,
int commandTimeout = -1)
{
var dataColumnName = GetColumnName(dataColumn);
var isTextColumnName = GetColumnName(isTextColumn);
var cmdText =
$"SELECT {dataColumnName},{isTextColumnName} FROM MasterDesign";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
var table = new DataTable();
using (var cn = VoteDb.GetOpenConnection())
{
cmd.Connection = cn;
DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
adapter.Fill(table);
}
if (table.Rows.Count != 1)
{
return(string.Empty);
}
var text = (table.Rows[0][dataColumnName] as string).SafeString();
if (Convert.ToBoolean(table.Rows[0][isTextColumnName]))
{
text = text.ReplaceNewLinesWithBreakTags();
}
if (substitutions == null)
{
substitutions = new Substitutions();
}
return(substitutions.Substitute(text));
}
public static DataRow GetPoliticianIssueReportData(string politiciankey,
int commandTimeout = -1)
{
const string cmdText =
"SELECT p.AddOn,p.Address,p.BallotPediaWebAddress,p.BloggerWebAddress,p.CityStateZip,p.DateOfBirth," +
" p.EmailAddr AS Email,p.FacebookWebAddress,p.FlickrWebAddress," +
" p.FName AS FirstName,p.GooglePlusWebAddress," +
//"p.LDSAddress,p.LDSCityStateZip,p.LDSEmailAddr AS LDSEmail,p.LDSPhone,p.LDSWebAddr AS LDSWebAddress," +
"p.LinkedInWebAddress," +
" p.LiveElectionKey,p.LiveOfficeKey,p.LiveOfficeStatus,p.LName AS LastName," +
" p.MName AS MiddleName,p.Nickname,p.PartyKey,p.Phone," +
" p.PinterestWebAddress,p.PoliticianKey,p.RSSFeedWebAddress," +
" p.StateAddress,p.StateCityStateZip,p.StateEmailAddr AS StateEmail," +
" p.StatePhone,p.StateWebAddr AS StateWebAddress,p.Suffix," +
" p.TwitterWebAddress,p.VimeoWebAddress,p.WebAddr AS WebAddress," +
" p.WebstagramWebAddress,p.WikipediaWebAddress,p.YouTubeWebAddress," +
" pt.PartyCode,pt.PartyName,pt.PartyUrl,o.OfficeLevel,o.OfficeLine1," +
" o.OfficeLine2,e.ElectionDesc FROM Politicians p" +
" LEFT JOIN Parties pt ON pt.PartyKey=p.PartyKey" +
" LEFT JOIN Offices o ON o.OfficeKey=p.LiveOfficeKey" +
" LEFT JOIN Elections e ON e.ElectionKey=p.LiveElectionKey" +
" WHERE p.PoliticianKey=@PoliticianKey";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
using (var cn = VoteDb.GetOpenConnection())
{
cmd.Connection = cn;
VoteDb.AddCommandParameter(cmd, "PoliticianKey", politiciankey);
var table = new DataTable();
DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
adapter.Fill(table);
return(table.Rows.Count == 1 ? table.Rows[0] : null);
}
}
public static DataTable GetAnswerQuestionsNew([NotNull] string politicianKey,
[NotNull] string officeKey, int issueId, int commandTimeout = -1)
{
var(stateCode, countyCode, localKey, level) = Offices.GetIssuesCoding(officeKey);
var cmdText =
"SELECT q.QuestionId,q.Question FROM QuestionsJurisdictions qj" +
" INNER JOIN Questions2 q ON q.QuestionId = qj.QuestionId AND q.IsQuestionOmit = 0" +
" INNER JOIN IssuesQuestions iq ON iq.QuestionId = q.QuestionId AND iq.IssueId=@IssueId" +
" INNER JOIN Issues2 i ON i.IssueId = iq.IssueId AND i.IsIssueOmit = 0" +
$" WHERE {Questions.QuestionsJurisdictionsWhereClause}" +
" ORDER BY iq.QuestionOrder,q.Question";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
using (var cn = VoteDb.GetOpenConnection())
{
cmd.Connection = cn;
VoteDb.AddCommandParameter(cmd, "PoliticianKey", politicianKey);
VoteDb.AddCommandParameter(cmd, "Level", level);
VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
VoteDb.AddCommandParameter(cmd, "CountyCode", countyCode);
VoteDb.AddCommandParameter(cmd, "LocalKey", localKey);
VoteDb.AddCommandParameter(cmd, "IssueId", issueId);
var table = new DataTable();
DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
adapter.Fill(table);
return(table);
}
}
public static DataTable GetCityCouncilDistrictsByCountyCode([NotNull] string stateCode,
[NotNull] string countyCode, int commandTimeout = -1)
{
var cmdText = "SELECT cc.StateCode,tpc.CountyCode,cc.CityCouncilCode,cc.Name," +
"lic.LocalKey FROM vote.CityCouncil cc" +
" INNER JOIN TigerPlacesCounties tpc ON tpc.StateCode = cc.StateCode AND" +
" tpc.TigerCode = cc.TigerCode AND tpc.TigerType IN" +
$" ('{TigerPlacesCounties.TigerTypePlace}','{TigerPlacesCounties.TigerTypeCousub}')" +
$" LEFT OUTER JOIN LocalIdsCodes lic ON lic.LocalType = '{LocalIdsCodes.LocalTypeCityCouncil}'" +
" AND lic.StateCode = tpc.StateCode" +
" AND lic.localId = cc.CityCouncilCode" +
" WHERE cc.StateCode = @StateCode AND tpc.CountyCode = @CountyCode" +
" GROUP BY cc.CityCouncilCode" +
" ORDER BY cc.Name";
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 OrganizationImageData[] GetOrganizationImageData()
{
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";
OrganizationImageData[] 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 OrganizationImageData
{
OrgTypeId = g.First().OrgTypeId(),
OrgType = g.First().OrgType(),
Organizations = g.Where(r => r.OrgId() != 0).Select(r =>
new OneLogoOrganization {
OrgId = r.OrgId(), Name = r.Name()
}).ToArray()
}).ToArray();
}
return(result);
}
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 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 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 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 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 OrganizationTypesData[] GetOrganizationTypesData()
{
const string cmdText =
"SELECT ot.OrgTypeId,ot.OrgType,(o.OrgTypeId IS NULL) AS IsNull,COUNT(*) AS Count" +
" FROM OrganizationTypes ot" +
" LEFT OUTER JOIN Organizations o ON o.OrgTypeId=ot.OrgTypeId" +
" GROUP BY OrgTypeId" +
" ORDER BY ot.OrgTypeOrder";
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);
return(table.Rows.OfType <DataRow>().Select(r =>
new OrganizationTypesData
{
OrgTypeId = r.OrgTypeId(),
OrgType = r.OrgType(),
Count = r.IsNull() ? 0 : r.Count()
}).ToArray());
}
}
public static List <IGrouping <string, DataRow> > GetOfficesWithCandidatesToEliminate(
string electionKey, int commandTimeout = -1)
{
const string cmdText = "SELECT eo.OfficeKey,oo.PoliticianKey,o.OfficeLine1," +
"o.OfficeLine2,o.Incumbents,o.ElectionPositions,p.FName as FirstName," +
"p.MName as MiddleName,p.Nickname,p.LName as LastName,p.Suffix" +
" FROM ElectionsOffices eo" +
" INNER JOIN Offices o ON o.OfficeKey=eo.OfficeKey AND" +
" o.Incumbents>o.ElectionPositions" +
" INNER JOIN OfficesOfficials oo ON oo.OfficeKey=eo.OfficeKey" +
" INNER JOIN Politicians p ON p.PoliticianKey=oo.PoliticianKey" +
" LEFT OUTER JOIN ElectionsPoliticians ep ON ep.ElectionKey=eo.ElectionKey" +
" AND ep.OfficeKey= eo.OfficeKey AND ep.PoliticianKey=oo.PoliticianKey" +
" WHERE eo.ElectionKey=@ElectionKey AND (ep.IsWinner IS NULL" +
" OR 0 <> (SELECT COUNT(*) FROM ElectionsPoliticians" +
" WHERE ElectionKey=@ElectionKey AND OfficeKey=eo.OfficeKey" +
" AND PoliticianKey<>oo.PoliticianKey AND IsWinner=1))";
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())
//.Where(g => g.First().Incumbents() - g.Count() < g.First().ElectionPositions())
.Where(g => g.Count() > g.First().Incumbents() - g.First().ElectionPositions())
.ToList());
}
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 Dictionary<string, string> GetCountyElections(
// string stateElectionKey, int commandTimeout = -1)
//{
// const string cmdText =
// "SELECT CountyCode,ElectionKey FROM ElectionsOffices" +
// " WHERE ElectionKeyState=@ElectionKeyState AND CountyCode<>'' AND LocalCode=''" +
// " GROUP BY CountyCode";
// var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
// VoteDb.AddCommandParameter(cmd, "ElectionKeyState", stateElectionKey);
// 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["CountyCode"] as string,
// row => row["ElectionKey"] as string);
// }
//}
public static Dictionary <string, string> GetCountyAndLocalElections(
string stateElectionKey, bool allowEmptyOffices = false, int commandTimeout = -1)
{
var altElectionKey = Elections.GetElectionKeyToInclude(stateElectionKey);
var cmdText =
"SELECT eo.CountyCode,eo.ElectionKeyCounty AS ElectionKey FROM ElectionsOffices eo" +
(allowEmptyOffices
? string.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);
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["CountyCode"] as string,
row => row["ElectionKey"] as string));
}
}
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 GetSchoolDistrictDistrictsByTigerTypeTigerCode([NotNull] string stateCode,
[NotNull] string tigerType, [NotNull] string tigerCode, int commandTimeout = -1)
{
var cmdText = "SELECT sdd.StateCode,sdd.SchoolDistrictDistrictCode,sdd.Name," +
"sdd.IsInShapefile,lic.LocalKey" +
" FROM SchoolDistrictDistricts sdd" +
$" LEFT OUTER JOIN LocalIdsCodes lic ON lic.LocalType='{LocalIdsCodes.LocalTypeSchoolDistrictDistrict}'" +
" AND lic.StateCode = sdd.StateCode" +
" AND lic.localId = sdd.SchoolDistrictDistrictCode" +
" WHERE sdd.StateCode = @StateCode AND sdd.TigerType=@TigerType AND sdd.TigerCode = @TigerCode" +
" ORDER BY sdd.SchoolDistrictDistrictCode";
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, "TigerType", tigerType);
VoteDb.AddCommandParameter(cmd, "TigerCode", tigerCode);
DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
adapter.Fill(table);
return(table);
}
}
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 GetCityCouncilDistrictsByTigerCode([NotNull] string stateCode,
[NotNull] string tigerCode, int commandTimeout = -1)
{
var cmdText = "SELECT cc.StateCode,cc.CityCouncilCode,cc.District,cc.Name," +
"cc.IsInShapefile,lic.LocalKey" +
" FROM vote.CityCouncil cc" +
$" LEFT OUTER JOIN LocalIdsCodes lic ON lic.LocalType = '{LocalIdsCodes.LocalTypeCityCouncil}'" +
" AND lic.StateCode = cc.StateCode" +
" AND lic.localId = cc.CityCouncilCode" +
" WHERE cc.StateCode = @StateCode AND cc.TigerCode = @TigerCode" +
" ORDER BY cc.CityCouncilCode";
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, "TigerCode", tigerCode);
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 PoliticianOfficeStatus GetOfficeStatus(string politicianKey)
{
const string cmdText = "CALL OfficeStatus(@PoliticianKey)";
var cmd = VoteDb.GetCommand(cmdText, -1);
VoteDb.AddCommandParameter(cmd, "PoliticianKey", politicianKey);
var table = new DataTable();
using (var cn = VoteDb.GetOpenConnection())
{
cmd.Connection = cn;
var adapter = VoteDb.GetDataAdapter(cmd);
adapter.Fill(table);
}
var status = table.Rows[0]["Status"].ToString();
PoliticianStatus policicianStatus;
if (!Enum.TryParse(status, out policicianStatus))
{
policicianStatus = PoliticianStatus.Unknown;
}
var result = new PoliticianOfficeStatus
{
OfficeKey = table.Rows[0]["OfficeKey"].ToString(),
PoliticianStatus = policicianStatus
};
return(result);
}
public static string GetDistrictItem(string stateCode,
OfficeClass officeClass, string districtCode, int commandTimeout = -1)
{
const string cmdText =
"SELECT DistrictCode,OfficeLine1,OfficeLine2 FROM Offices" +
" WHERE StateCode=@StateCode AND OfficeLevel=@OfficeLevel" +
" AND DistrictCode=@DistrictCode";
if (districtCode.Length < 3)
{
districtCode = districtCode.ZeroPad(3);
}
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
VoteDb.AddCommandParameter(cmd, "OfficeLevel", officeClass.ToInt());
VoteDb.AddCommandParameter(cmd, "DistrictCode", districtCode);
using (var cn = VoteDb.GetOpenConnection())
{
cmd.Connection = cn;
var table = new DataTable();
DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
adapter.Fill(table);
if (table.Rows.Count == 0)
{
return(Empty);
}
var row = table.Rows[0];
return(IsNullOrWhiteSpace(row.OfficeLine2())
? row.OfficeLine1()
: row.OfficeLine2());
}
}
public static DataRow GetCandidateData(string politicianKey, bool isRunningMate,
int commandTimeout = -1)
{
var cmdText =
"SELECT p.AddOn,p.Address,p.CityStateZip,p.EmailAddr AS Email,p.FName AS FirstName," +
"p.MName as MiddleName,p.LName AS LName,p.Nickname,p.Phone," +
"p.PoliticianKey,p.StateAddress,p.StateCityStateZip," +
"p.StateEmailAddr AS StateEmail,p.StatePhone," +
"p.StateWebAddr AS StateWebAddress,p.Suffix,p.WebAddr AS WebAddress," +
"pt.PartyCode,pt.PartyName,pt.PartyUrl," +
$"{(isRunningMate ? 1 : 0)} AS IsRunningMate FROM Politicians p" +
" LEFT JOIN Parties pt ON pt.PartyKey=p.PartyKey" + " WHERE p.PoliticianKey=@PoliticianKey";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
VoteDb.AddCommandParameter(cmd, "PoliticianKey", politicianKey);
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.Count == 1 ? table.Rows[0] : null);
}
}
public static IEnumerable <SimpleListItem> GetDistrictItems(string stateCode,
OfficeClass officeClass, int commandTimeout = -1)
{
const string cmdText =
"SELECT DistrictCode,OfficeLine1,OfficeLine2 FROM Offices" +
" WHERE StateCode=@StateCode AND OfficeLevel=@OfficeLevel" +
" ORDER BY DistrictCode";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
VoteDb.AddCommandParameter(cmd, "OfficeLevel", officeClass.ToInt());
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(row => new SimpleListItem
{
Value = row.DistrictCode(),
Text = IsNullOrWhiteSpace(row.OfficeLine2())
? row.OfficeLine1()
: row.OfficeLine2()
}));
}
}
public static OrganizationEmailTagsData[] GetOrganizationEmailTagsData()
{
const string cmdText =
"SELECT ot.OrgTypeId,et.EmailTagId,et.EmailTag,ot.OrgType,COUNT(*) AS Count,(aet.OrgId IS NULL) AS IsNull" +
" FROM OrganizationTypes ot" +
" LEFT OUTER JOIN OrganizationEmailTags et ON et.OrgTypeId = ot.OrgTypeId" +
" LEFT OUTER JOIN OrganizationAssignedEmailTags aet ON aet.EmailTagId=et.EmailTagId" +
" GROUP BY ot.OrgTypeId,et.EmailTagId" +
" ORDER BY ot.OrgTypeOrder,et.EmailTagOrder";
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);
return(table.Rows.OfType <DataRow>().GroupBy(r => r.OrgTypeId()).Select(g =>
new OrganizationEmailTagsData
{
OrgTypeId = g.First().OrgTypeId(),
OrgType = g.First().OrgType(),
EmailTags = g.Where(r => r.EmailTagId() != 0).Select(r => new OneEmailTag
{
EmailTagId = r.EmailTagId(),
EmailTag = r.EmailTag(),
Count = r.IsNull() ? 0 : r.Count()
}).ToArray()
}).ToArray());
}
}
public static DataTable GetElectedReportData(string stateCode,
string countyCode, string congressionalDistrict, string stateSenateDistrict,
string stateHouseDistrict, string district, string place,
string elementary, string secondary, string unified, string cityCouncil,
string countySupervisors, string schoolDistrictDistrict, bool includeLocals,
int commandTimeout = -1)
{
var localKeys = includeLocals
? LocalIdsCodes.GetLocals(stateCode, countyCode, district, place, elementary,
secondary, unified, cityCouncil, countySupervisors, schoolDistrictDistrict)
: null;
var cmdText = Format(OfficialsReportCmdTextTemplate,
OfficialsReportColumnList,
BuildElectedReportWhereClause(stateCode, countyCode, congressionalDistrict,
stateSenateDistrict, stateHouseDistrict, false, localKeys),
BuildElectedReportWhereClause(stateCode, countyCode, congressionalDistrict,
stateSenateDistrict, stateHouseDistrict, true, localKeys));
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
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 GetVideoAnswers(string politicianKey, string officeKey,
int commandTimeout = -1)
{
const string cmdText =
"SELECT a.Answer,a.IssueKey,a.QuestionKey,a.Sequence,a.Source,a.DateStamp,a.YouTubeUrl," +
"a.YouTubeDescription,a.YouTubeSource,a.YouTubeRunningTime,a.YouTubeSourceUrl,a.YouTubeDate," +
"a.YouTubeRefreshTime,a.YouTubeAutoDisable,a.YouTubeRefreshTime,a.YouTubeAutoDisable," +
"a.PoliticianKey,i.Issue,i.IssueLevel,i.IssueOrder,q.Question,q.QuestionOrder FROM Answers a" +
" 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 a.PoliticianKey=@PoliticianKey" +
" AND NOT a.YouTubeUrl IS NULL AND TRIM(a.YouTubeUrl)<>''" +
" AND (a.YouTubeAutoDisable IS NULL OR a.YouTubeAutoDisable='')" +
" AND i.IssueLevel IN ('A', GetIssueLevel(@OfficeKey))" +
" ORDER BY IssueLevel,IssueOrder,Issue,QuestionOrder,Question,DateStamp DESC,Sequence DESC";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
using (var cn = VoteDb.GetOpenConnection())
{
cmd.Connection = cn;
VoteDb.AddCommandParameter(cmd, "PoliticianKey", politicianKey);
VoteDb.AddCommandParameter(cmd, "OfficeKey", officeKey);
var table = new DataTable();
DbDataAdapter adapter = new MySqlDataAdapter(cmd as MySqlCommand);
adapter.Fill(table);
return(table);
}
}
public static List <SimpleListItem> GetSearchCountiesInState(string stateCode,
string searchString)
{
searchString = searchString.Trim();
const string cmdText = "SELECT CountyCode,County FROM Counties" +
" WHERE StateCode=@StateCode AND County LIKE @CountyMatchAny" +
" AND IsCountyTagForDeletion=0" +
" ORDER BY County LIKE @CountyMatchStart DESC,County";
var cmd = VoteDb.GetCommand(cmdText, 0);
VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
VoteDb.AddCommandParameter(cmd, "CountyMatchAny", $"%{searchString}%");
VoteDb.AddCommandParameter(cmd, "CountyMatchStart", $"{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.County(),
Value = r.CountyCode()
}).ToList());
}
}
