private void InitStats(StationViewModel model)
{
if (model.VarId <= 0) return;
var cname = Helper.Vars[model.VarId].ColumnName;
var sql = DBHelper.SQLGetValByVarAndStat(model.VarId, model.StatId);
sql = sql.Where(" time_utc>=@0 and time_utc<=@1", model.StartDate, model.EndDate);
if (model.VarId != 16)
sql = sql.Where(string.Format("{0} >0", cname));
var list = db.Query<TableValue>(sql);
if (list.Any())
{
var min = list.Min(x => x.Val);
var max = list.Max(x => x.Val);
ViewBag.minv = TableValue.ModifyValue(model.VarId, min);
ViewBag.mindate = list.First(x => x.Val == min).DateTime.Date;
ViewBag.maxv = TableValue.ModifyValue(model.VarId, max); ;
ViewBag.maxdate = list.First(x => x.Val == max).DateTime.Date;
ViewBag.MeasureUnit = Helper.Vars[model.VarId].MeasureUnit;
}
}
//
// GET: /Map/
public ActionResult Index(string varname, string address)
{
var model = new StationViewModel();
//if (string.IsNullOrEmpty(varname)) varname = Helper.GetRouteVar(4);
InitModel(varname, model);
//chached db query
var sw = Stopwatch.StartNew();
IEnumerable<station> stats = Repo.GetStationsWithObservDates(model.VarId, true);
stats = stats.Where(x => x.lat.HasValue);
ViewBag.elapsed = string.Format("[GetStations] = {0} ms", sw.ElapsedMilliseconds);
string obsInfo = "";
if (!string.IsNullOrEmpty(Request.Params["show"]))
{
//return View(stats.ToList());
}
if (!string.IsNullOrEmpty(address))
{
var loc = FindbyAddress(address);
if (loc != null)
{
ViewBag.Place = loc;
}
}
model.Stations = stats.ToList();
return View(model);
}
//
// GET: /Public/Stations/
public ActionResult Index(string varname)
{
var model = new StationViewModel();
InitModel(varname, model);
model.Stations = Repo.GetStationsWithObservDates(model.VarId, false);
return View(model);
}
private void CalcAverage(StationViewModel model)
{
var varid = model.VarId;
if (varid <= 0) return;
var tname = Helper.Vars[varid].TableName;
var cname = Helper.Vars[varid].ColumnName;
string expr1 = "";
string calcTitle = "";
if (varid == 8)
{
expr1 = "count(*)";
calcTitle = "number of snow days";
}
else if (varid == 1 || varid == 2)
{
expr1 = string.Format("sum({0})", cname);
calcTitle = "Sum";
}
else
{
expr1 = string.Format("avg({0})", cname);
calcTitle = "Average";
}
string sql = "";
//for temperature not need >0
if (varid == 16)
sql = string.Format(@"SELECT COALESCE({0}, 0) FROM {1} where station_id=@0 and time_utc > @1 and time_utc < @2", expr1, tname);
else
sql = string.Format(@"SELECT COALESCE({0}, 0) FROM {1} where station_id=@0 and time_utc > @1 and time_utc < @2 and {2} > 0", expr1, tname, cname);
var sqlb = PetaPoco.Sql.Builder.Append(sql, model.StatId, model.StartDate, model.EndDate);
var res = db.ExecuteScalar<float>(sqlb);
ViewBag.calc = TableValue.ModifyValue(varid, res);
ViewBag.calcTitle = calcTitle;
}
protected StationViewModel CreateAndInitModel(string varname, string sturi)
{
var model = new StationViewModel();
//read station cookies
if (string.IsNullOrWhiteSpace(sturi)) sturi = ReadCookie("sel_station");
else { SetCookie("sel_station", sturi); }
InitModel(varname, model);
var stats = Repo.GetStationsWithObservDates(model.VarId, true);
//sturi = sturi.Replace('-', '/');
model.Station = stats.FirstOrDefault(x => x.UrlParm == sturi);
if (model.Station != null)
{
model.sturi = model.Station.st_uri;
model.StatId = model.Station.st_id;
}
model.SelectListStations = new SelectList(stats, "UrlParm", "st_name", sturi);
return model;
}
protected void InitModel(string varname, StationViewModel model)
{
//read var cookie
if (string.IsNullOrWhiteSpace(varname)) varname = ReadCookie("sel_var");
else { SetCookie("sel_var", varname); }
var varid = GetVarIdByName(varname);
if (varid != 0)
{
model.VarId = varid;
model.VarName = Helper.Vars[model.VarId].TableName;
model.VarPageName = Helper.Vars[model.VarId].PageName;
}
model.StartDate = Helper.CurrentDate.AddYears(-1);
model.EndDate = Helper.CurrentDate;
model.Vars = new SelectList(Helper.VarsTable.Select(x =>
new { TableName = x.TableName, PageName = T.Text(x.PageName) }), "TableName", "PageName", model.VarName);
}
protected StationViewModel InitModel(int? VarId, int? StatId)
{
var model = new StationViewModel();
model.VarId = VarId.GetValueOrDefault(0);
model.Vars = new SelectList(Helper.VarsTable.Select(x =>
new { Id = x.Id, PageName = T.Text(x.PageName) }), "Id", "PageName", model.VarId);
if (model.VarId != 0) model.VarName = Helper.Vars[model.VarId].PageName;
model.StatId = StatId.GetValueOrDefault(0);
//if (VarId.HasValue)
{
var stats = Repo.GetStationsWithObservDates(model.VarId, true);
model.Stations = stats;
model.Station = stats.FirstOrDefault(x => x.st_id == StatId);
model.SelectListStations = new SelectList(stats, "st_id", "st_name", model.StatId);
}
return model;
}