public JsonResult Add(string id, string name, string search_string)
{
LeapDataClassesDataContext context = new LeapDataClassesDataContext();
data_search dsearch = null;
IQueryable<data_search> query = context.data_searches.Where(d => d.user_name == User.Identity.Name);
if (id != null && id != "" && id != "null")
{
query = query.Where(d => d.id == int.Parse(id));
if (query.Count() <= 0)
{
return Json(new { error = "search with the name [" + name + "] does not exist!" }, JsonRequestBehavior.AllowGet);
}
dsearch = Update(context, int.Parse(id), search_string);
Log("Update Search", "Search[" + id + ":" + name + "] was modified with search string [" + search_string + "]");
}
else
{
query = query.Where(d => d.name == name);
if (query.Count() > 0)
{
return Json(new { error = "search with the name [" + name + "] already exists!" }, JsonRequestBehavior.AllowGet);
}
dsearch = Create(context, name, search_string);
Log("Create Search", "Search[" + dsearch.id + ":" + name + "] was created with search string [" + search_string + "]");
}
return Find(dsearch.id);
}
public JsonResult Find(int id)
{
LeapDataClassesDataContext context = new LeapDataClassesDataContext();
IQueryable<data_search> query = context.data_searches.Where(d => d.user_name == User.Identity.Name && d.id == id);
if (query.Count() == 0)
{
return Json(new { error = "No search found" }, JsonRequestBehavior.AllowGet);
}
var dsearch = query.ToList()
.Select(d => new
{
d.id,
d.user_name,
d.name,
d.search_string,
created = d.created_datetime.ToString("yyyy-MM-dd hh:mm:ss"),
modified = d.last_modified_datetime.Value.ToString("yyyy-MM-dd hh:mm:ss")
});
var search = dsearch.First();
Log("Find Search", "Search[" + search.id + ":" + search.name + "] was retrieved.");
return Json(search, JsonRequestBehavior.AllowGet);
}
private web_site_support_text Modify(LeapDataClassesDataContext context, web_site_support_text support_text, string text)
{
support_text.text_block = text;
context.SubmitChanges();
return support_text;
}
//
// GET: /Poll/
public ActionResult Index(string num_to_fetch, string view)
{
if (!User.IsInRole("Admin")) view = null;
ViewBag.view = view;
LeapDataClassesDataContext context = new LeapDataClassesDataContext();
List<leap_event> list = new List<leap_event>();
var query = context.leap_events.OrderByDescending(e => e.date);
if (num_to_fetch != null)
{
list = query.Take(int.Parse(num_to_fetch)).ToList();
}
else
{
list = query.ToList();
}
List<Models.EventYearArchive> year_archive_list = find_archives_in_years(context);
foreach (Models.EventYearArchive year_archive in year_archive_list)
{
List<Models.EventMonthArchive> month_archive_list = find_archives_in_months(context, year_archive.Year);
year_archive.MonthArchiveList = month_archive_list;
}
ViewBag.archives = year_archive_list;
return View(list);
}
public ActionResult Matrix(string fips)
{
int state_fips = int.Parse(fips);
LeapDataClassesDataContext context = new LeapDataClassesDataContext();
Leap.Models.StateMatrix matrix = FindStateMatrix(context, state_fips);
return View(matrix);
}
private web_site_support_text Create(LeapDataClassesDataContext context, string name, string text)
{
web_site_support_text support_text = new web_site_support_text();
support_text.text_block_name = name;
support_text.text_block = text;
context.web_site_support_texts.InsertOnSubmit(support_text);
context.SubmitChanges();
return support_text;
}
protected void LogPageEdit(string text_block_name, string old_text, string new_text)
{
LeapDataClassesDataContext context = new LeapDataClassesDataContext();
if (!User.IsInRole("Admin")) throw new Exception("You must login as an Administrator first to edit page");
String user_name = User.Identity.Name;
leap_logger.LogPageEdit(context, text_block_name, user_name, this.Request.UserHostAddress, old_text, new_text);
}
public static string DisplayEntityName(this HtmlHelper helper, int? stat_fips, int? county_fips, int? entity_code)
{
LeapDataClassesDataContext context = new LeapDataClassesDataContext();
List<list_fips_meta_data> meta_data_list = context.list_fips_meta_datas
.Where(m => m.state_fips == stat_fips)
.Where(m => m.county_fips == county_fips)
.Where(m => m.entity_code == entity_code).ToList();
if (meta_data_list.Count == 0) return "";
return meta_data_list[0].entity_name;
}
protected void Log(string method, string message)
{
try
{
LeapDataClassesDataContext context = new LeapDataClassesDataContext();
leap_logger.Log(context, User.Identity.Name, this.Request.UserHostAddress, method, message);
}
catch (Exception)
{
}
}
public ActionResult FindAll(int num_to_fetch, string view, string page)
{
if (!User.IsInRole("Admin")) view = null;
ViewBag.view = view;
ViewBag.page = page;
LeapDataClassesDataContext context = new LeapDataClassesDataContext();
List<leap_event> list = context.leap_events.OrderByDescending(e => e.date).Take(num_to_fetch).ToList();
return View(list);
}
public web_site_support_text Search(LeapDataClassesDataContext context, string name)
{
IQueryable<web_site_support_text> query = context.web_site_support_texts.Where(w => w.text_block_name == name);
if (query.Count() == 0)
{
return null;
}
return query.First();
}
public void Log(LeapDataClassesDataContext context, string user_name, string user_ip, string method, string message)
{
leap_log log = new leap_log();
log.method = method;
log.user_name = user_name;
log.client_ip = user_ip;
log.message = message;
log.log_date = DateTime.Now;
context.leap_logs.InsertOnSubmit(log);
context.SubmitChanges();
}
public void LogPageEdit(LeapDataClassesDataContext context, string text_block_name, string user_name, string user_ip, string old_text, string new_text)
{
leap_page_edit_log log = new leap_page_edit_log();
log.modified_by = user_name;
log.client_ip = user_ip;
log.text_block_name = text_block_name;
log.old_text = old_text;
log.new_text = new_text;
log.modified_time = DateTime.Now;
context.leap_page_edit_logs.InsertOnSubmit(log);
context.SubmitChanges();
}
public ActionResult AllStateMatrix()
{
LeapDataClassesDataContext context = new LeapDataClassesDataContext();
List<Leap.Models.StateMatrix> matrix_list = new List<Models.StateMatrix>();
foreach (int state_fips in FindStatesWithData())
{
matrix_list.Add(FindStateMatrix(context, state_fips));
}
return View(matrix_list);
}
public JsonResult Find(string name)
{
LeapDataClassesDataContext context = new LeapDataClassesDataContext();
web_site_support_text support_text = Search(context, name);
if (support_text == null)
{
return Json(new { error = "No text found" }, JsonRequestBehavior.AllowGet);
}
else
{
return Json(support_text, JsonRequestBehavior.AllowGet);
}
}
public leap_event Search(LeapDataClassesDataContext context, int id)
{
if (id < 0) return null;
IQueryable<leap_event> query = context.leap_events.Where(e => e.id == id);
if (query.Count() == 0)
{
return null;
}
return query.First();
}
public JsonResult Delete(int id)
{
LeapDataClassesDataContext context = new LeapDataClassesDataContext();
IQueryable<data_search> query = context.data_searches.Where(d => d.user_name == User.Identity.Name && d.id == id);
if (query.Count() == 0)
{
return Json(new { error = "No search found" }, JsonRequestBehavior.AllowGet);
}
data_search dsearch = query.First();
string search_name = dsearch.name;
context.data_searches.DeleteOnSubmit(dsearch);
context.SubmitChanges();
Log("Delete Search", "Search[" + id + ":" + search_name + "] was deleted.");
return Json(new {}, JsonRequestBehavior.AllowGet);
}
public JsonResult Update(string name, string text)
{
string old_text = "";
string new_text = text;
LeapDataClassesDataContext context = new LeapDataClassesDataContext();
web_site_support_text support_text = Search(context, name);
if (support_text == null)
{
support_text = Create(context, name, text);
}
else
{
old_text = support_text.text_block;
support_text = Modify(context, support_text, text);
}
LogPageEdit(support_text.text_block_name, old_text, new_text);
return Json(support_text, JsonRequestBehavior.AllowGet);
}
//
// GET: /SchoolDistrict/
private List<int> FindDistrictsWithData(int state_fips)
{
LeapDataClassesDataContext context = new LeapDataClassesDataContext();
List<int?> list = context.ldc_results_elections
.Where(e => e.archive_flag == 0)
.Where(e => e.state_fips == state_fips)
.Where(e => e.leap_office_name_id > 0)
//.Where(e => e.government_level_id > 2)
.Where(e => e.meta_data_log.data_type == 1)
.Select(e => e.entity_code)
.Distinct().ToList();
List<int> entity_list = new List<int>();
foreach (int entity in list)
{
if (entity == 0) continue;
entity_list.Add(entity);
}
return entity_list;
}
public ActionResult CountyMatrix(string state, string county)
{
int state_fips = int.Parse(state);
Leap.Models.CountyMatrix matrix = new Models.CountyMatrix();
LeapDataClassesDataContext context = new LeapDataClassesDataContext();
matrix.StateFips = state_fips;
matrix.StateAbbr = context.list_loc_keys.Where(s => s.state_fips == state_fips).First().state_abbrev;
if (county != "")
{
int county_fips = int.Parse(county);
matrix.CountyFips = county_fips;
matrix.CountyName = context.list_loc_keys.Where(c => c.state_fips == state_fips && c.county_fips == county_fips).First().county_name;
matrix.ElectionCount = context.ldc_results_elections.Where(e => e.archive_flag == 0).Where(e => e.state_fips == state_fips && e.county_fips == county_fips).Where(e => e.data_log_id < 0).Distinct().Count().ToString();
matrix.PlaceCount = context.ldc_results_elections.Where(e => e.archive_flag == 0).Where(e => e.state_fips == state_fips && e.county_fips == county_fips).Where(e => e.data_log_id < 0).Select(e => new { e.fips_place_location }).Distinct().Count().ToString();
matrix.CandidateCount = context.ldc_results_election_candidates.Where(c => c.ldc_results_election.state_fips == state_fips && c.ldc_results_election.county_fips == county_fips).Where(c => c.ldc_results_election.data_log_id < 0).Distinct().Count().ToString();
matrix.YearRange = GenerateYearRangeStr(context.ldc_results_elections.Where(e => e.archive_flag == 0).Where(e => e.state_fips == state_fips && e.county_fips == county_fips).Where(e => e.data_log_id < 0).Select(e => e.election_date.Value.Year).Distinct().ToList());
}
return View(matrix);
}
public ActionResult Find(int id)
{
LeapDataClassesDataContext context = new LeapDataClassesDataContext();
leap_event l_event = Search(context, id);
List<Models.EventYearArchive> year_archive_list = find_archives_in_years(context);
foreach (Models.EventYearArchive year_archive in year_archive_list)
{
List<Models.EventMonthArchive> month_archive_list = find_archives_in_months(context, year_archive.Year);
year_archive.MonthArchiveList = month_archive_list;
}
ViewBag.archives = year_archive_list;
if (l_event == null)
{
return View();
}
else
{
return View(l_event);
}
}
public JsonResult Find(int state_fips)
{
LeapDataClassesDataContext context = new LeapDataClassesDataContext();
var entity_list = context.ldc_results_elections
.Where(e => e.archive_flag == 0)
.Where(e => e.state_fips == state_fips)
.Where(e => e.leap_office_name_id > 0)
//.Where(e => e.government_level_id > 2)
.Where(e => e.meta_data_log.data_type == 1)
.Select(e => e.entity_code)
.Distinct();
var list = context.list_fips_meta_datas
.Where(m => m.state_fips == state_fips)
.Where(m => entity_list.Contains(m.entity_code))
.Where(m => m.primary_latitude != 0 && m.primary_longitude != 0)
.Select(m => new { m.state_fips, m.entity_code, m.primary_latitude, m.primary_longitude })
.Distinct()
.OrderBy(m => m.entity_code)
.ToList();
return Json(list, JsonRequestBehavior.AllowGet);
}
public ActionResult MatrixElectionDetail(string fips, string gid)
{
int state_fips = int.Parse(fips);
int government_level_id = int.Parse(gid);
LeapDataClassesDataContext context = new LeapDataClassesDataContext();
List<ldc_results_election> election_list = GetElectionList(context, state_fips, government_level_id);
return View(election_list);
}
private leap_event Modify(LeapDataClassesDataContext context, leap_event l_event, DateTime date, string author, string summary, string detail)
{
l_event.date = date;
l_event.author = author;
l_event.summary = summary;
l_event.detail = detail;
context.SubmitChanges();
return Search(context, l_event.id);
}
private List<Models.EventYearArchive> find_archives_in_years(LeapDataClassesDataContext context)
{
//context.leap_events.GroupBy(e => new {e.date.Year, e.date.Month}).Select(g => new { g.Key, count = g.Count(e => e.summary)});
return context.leap_events.GroupBy(e => e.date.Year)
.Select(g => new Models.EventYearArchive
{ Year = g.Key, Count = g.Count() }
).OrderByDescending(a => a.Year).ToList();
}
private List<Models.EventMonthArchive> find_archives_in_months(LeapDataClassesDataContext context, int year)
{
List<Models.EventMonthArchive> list = context.leap_events.Where(e => e.date.Year == year).GroupBy(e => e.date.Month)
.Select(g => new Models.EventMonthArchive
{ Year = year, Month = g.Key, Count = g.Count() }
).OrderByDescending(a => a.Month).ToList();
foreach (Models.EventMonthArchive archive in list)
{
archive.MonthInStr = new DateTime(archive.Year, archive.Month, 1).ToString("MMMM");
}
return list;
}
private leap_event Create(LeapDataClassesDataContext context, DateTime date, string author, string summary, string detail)
{
leap_event l_event = new leap_event();
l_event.date = date;
l_event.author = author;
l_event.summary = summary;
l_event.detail = detail;
context.leap_events.InsertOnSubmit(l_event);
context.SubmitChanges();
return l_event;
}
private string GetYearRange(LeapDataClassesDataContext context, int state_fips, int government_level_id)
{
IQueryable<ldc_results_election> query = context.ldc_results_elections
.Where(e => e.archive_flag == 0)
.Where(e => e.state_fips == state_fips)
.Where(e => e.government_level_id == government_level_id)
.Where(e => e.election_date != null);
var count_query = query
.Select(e => e.election_date.Value.Year)
.Distinct()
.OrderBy(e => e);
string sql = context.GetCommand(count_query).CommandText;
List<int> year_list = count_query.ToList();
return GenerateYearRangeStr(year_list);
}
public JsonResult Update(string id, DateTime date, string author, string summary, string detail)
{
string event_id = "news_new";
string old_text = "";
string new_text = ToStr(date, author, summary, detail);
LeapDataClassesDataContext context = new LeapDataClassesDataContext();
leap_event l_event = Search(context, int.Parse(id));
if (l_event == null)
{
l_event = Create(context, date, author, summary, detail);
}
else
{
event_id = "news_" + id;
old_text = ToStr(l_event.date, l_event.author, l_event.summary, l_event.detail);
l_event = Modify(context, l_event, date, author, summary, detail);
}
LogPageEdit(event_id, old_text, new_text);
return Json(l_event, JsonRequestBehavior.AllowGet);
}
public JsonResult Index(int country, string offices, string ranges)
{
// build offices clauses
var predicate_offices = PredicateBuilder.True<ldc_results_election>();
if (offices != "")
{
string[] office_array = offices.Split(',');
predicate_offices = PredicateBuilder.False<ldc_results_election>();
foreach (string office_str in office_array)
{
if (office_str == "-1")
{
predicate_offices = PredicateBuilder.True<ldc_results_election>();
break;
}
if (office_str == "" ) continue;
predicate_offices = predicate_offices.Or(e => e.leap_office_name_id == int.Parse(office_str));
}
}
// build office clauses
var predicate_time = PredicateBuilder.True<ldc_results_election>();
if (ranges != "")
{
string[] range_array = ranges.Split(',');
predicate_time = PredicateBuilder.False<ldc_results_election>();
foreach (string range_str in range_array)
{
if (range_str == "") continue;
string[] time_strs = range_str.Split(':');
if (time_strs[0] != "" && time_strs[1] != "")
{
//DateTime from_date = new DateTime(1970, 1, 1).AddTicks(long.Parse(time_strs[0]) * 10000);
//DateTime to_date = new DateTime(1970, 1, 1).AddTicks(long.Parse(time_strs[1]) * 10000);
DateTime from_date = DateTime.Parse(time_strs[0]);
DateTime to_date = DateTime.Parse(time_strs[1]);
predicate_time = predicate_time.Or(e => e.election_date.Value >= from_date
&& e.election_date.Value <= to_date);
}
else if (time_strs[0] != "")
{
//DateTime from_date = new DateTime(1970, 1, 1).AddTicks(long.Parse(time_strs[0]) * 10000);
//DateTime to_date = new DateTime(1970, 1, 1).AddTicks(long.Parse(time_strs[1]) * 10000);
DateTime from_date = DateTime.Parse(time_strs[0]);
predicate_time = predicate_time.Or(e => e.election_date.Value >= from_date);
}
else if (time_strs[1] != "")
{
//DateTime from_date = new DateTime(1970, 1, 1).AddTicks(long.Parse(time_strs[0]) * 10000);
//DateTime to_date = new DateTime(1970, 1, 1).AddTicks(long.Parse(time_strs[1]) * 10000);
DateTime to_date = DateTime.Parse(time_strs[1]);
predicate_time = predicate_time.Or(e => e.election_date.Value <= to_date);
}
}
}
LeapDataClassesDataContext context = new LeapDataClassesDataContext();
List<Leap.Models.State> list = context.ldc_results_elections
.Where(e => e.archive_flag == 0)
.Where(e => e.country_id == country)
.Where(predicate_offices)
.Where(predicate_time)
.Where(e => e.leap_office_name_id > 0)
//.Where(e => e.government_level_id > 2)
.Select(e => new Leap.Models.State
{
CountryId = e.country_id,
StateFips = e.state_fips,
StateName = e.list_loc_key.state_name,
StateAbbrev = e.list_loc_key.state_abbrev
})
.Distinct().OrderBy(s => s.StateName).ToList();
return Json(list, JsonRequestBehavior.AllowGet);
}
