public static OfficesTable GetOfficesByClass(string stateCode, string countyCode,
string localKey, OfficeClass officeClass, int commandTimeout = -1)
{
if (IsNullOrWhiteSpace(localKey))
{
const string cmdText =
"SELECT OfficeKey,StateCode,CountyCode,LocalKey,DistrictCode,OfficeLine1," +
"OfficeLine2,OfficeLevel,IsRunningMateOffice,IsPrimaryRunningMateOffice,Incumbents,IsVacant FROM Offices" +
" WHERE StateCode=@StateCode AND CountyCode=@CountyCode AND LocalKey='' AND OfficeLevel=@OfficeLevel AND IsVirtual=0" +
" ORDER BY OfficeLine1,OfficeLine2";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
VoteDb.AddCommandParameter(cmd, "CountyCode", countyCode);
VoteDb.AddCommandParameter(cmd, "OfficeLevel", officeClass.ToInt());
return(FillTable(cmd, OfficesTable.ColumnSet.Cache));
}
else
{
const string cmdText =
"SELECT OfficeKey,StateCode,CountyCode,LocalKey,DistrictCode,OfficeLine1," +
"OfficeLine2,OfficeLevel,IsRunningMateOffice,IsPrimaryRunningMateOffice,Incumbents,IsVacant FROM Offices" +
" WHERE StateCode=@StateCode AND LocalKey=@LocalKey AND OfficeLevel=@OfficeLevel AND IsVirtual=0" +
" ORDER BY OfficeLine1,OfficeLine2";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
VoteDb.AddCommandParameter(cmd, "CountyCode", countyCode);
VoteDb.AddCommandParameter(cmd, "LocalKey", localKey);
VoteDb.AddCommandParameter(cmd, "OfficeLevel", officeClass.ToInt());
return(FillTable(cmd, OfficesTable.ColumnSet.Cache));
}
}
public static string GetOfficePageUrl(string stateCode, OfficeClass officeClass,
string countyCode = "", string localKey = "")
{
return(GetAdminFolderPageUrl("office", "state", stateCode,
"county", countyCode, "local", localKey,
"class", officeClass.ToInt().ToString(CultureInfo.InvariantCulture)));
}
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 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 string BuildWhereClause()
{
var sbWhere = new StringBuilder();
if ((OfficeClass != OfficeClass.All) ||
(Option != OfficesAdminReportViewOption.None))
{
var terms = new List <string>();
if (OfficeClass != OfficeClass.All)
{
terms.Add($"OfficeLevel={OfficeClass.ToInt()}");
}
terms.Add($"StateCode='{StateCode}'");
if (Option >= OfficesAdminReportViewOption.ByCounty)
{
terms.Add($"CountyCode='{CountyCode}'");
terms.Add(Option == OfficesAdminReportViewOption.ByLocal
? $"LocalCode='{LocalCode}'"
: "LocalCode=''");
}
else
{
terms.Add("CountyCode=''");
terms.Add("LocalCode=''");
}
sbWhere.Append(string.Join(" AND ", terms));
}
if (sbWhere.Length == 0)
{
throw new ArgumentException("no selections were made");
}
return(" WHERE " + sbWhere);
}
public string BuildWhereClause()
{
var sbWhere = new StringBuilder();
if (OfficeClass != OfficeClass.All ||
Option != PoliticiansAdminReportViewOption.None)
{
var terms = new List <string>();
if (OfficeClass != OfficeClass.All)
{
terms.Add($"OfficeLevel={OfficeClass.ToInt()}");
}
terms.Add($"StateCode='{StateCode}'");
switch (Option)
{
case PoliticiansAdminReportViewOption.ByCounty:
terms.Add($"CountyCode='{CountyCode}'");
break;
case PoliticiansAdminReportViewOption.ByLocal:
terms.Add($"LocalKey='{LocalKey}'");
break;
}
sbWhere.Append(Join(" AND ", terms));
}
if (sbWhere.Length == 0)
{
throw new ArgumentException("no selections were made");
}
return(" WHERE " + sbWhere);
}
private static void InsertOffice(ElectionsOfficesTable electionsOfficesTable,
string electionKey, string officeKey, OfficeClass officeClass, string districtCode)
{
var stateCode = Elections.GetStateCodeFromKey(electionKey);
electionsOfficesTable.AddRow(electionKey, officeKey, electionKey,
Elections.GetFederalElectionKeyFromKey(electionKey, officeClass.StateCodeProxy()),
stateCode, Empty, Empty, districtCode, officeClass.ToInt(), false);
}
public static string GetUpdateOfficesPageUrl(string stateCode,
string countyCode, string localKey, OfficeClass officeClass = OfficeClass.Undefined)
{
var oClass = officeClass == OfficeClass.Undefined
? Empty
: officeClass.ToInt().ToString(CultureInfo.InvariantCulture);
return(GetAdminFolderPageUrl("updateoffices", "state", stateCode, "county",
countyCode, "local", localKey, "class", oClass));
}
public static OfficesTable GetOfficeTemplatesByClass(string stateCode, OfficeClass officeClass,
int commandTimeout = -1)
{
const string cmdText =
"SELECT OfficeKey,StateCode,CountyCode,LocalCode,DistrictCode,OfficeLine1," +
"OfficeLine2,OfficeLevel,IsRunningMateOffice,Incumbents,IsVacant FROM Offices" +
" WHERE StateCode=@StateCode AND OfficeLevel=@OfficeLevel AND IsVirtual=1" +
" ORDER BY OfficeLine1,OfficeLine2";
var cmd = VoteDb.GetCommand(cmdText, commandTimeout);
VoteDb.AddCommandParameter(cmd, "StateCode", stateCode);
VoteDb.AddCommandParameter(cmd, "OfficeLevel", officeClass.ToInt());
return(FillTable(cmd, OfficesTable.ColumnSet.Cache));
}
public override bool Filter(OfficesAdminReportViewRow row)
{
return(row.OfficeLevel == _OfficeClass.ToInt());
}