//When page is loaded public ActionResult Main() { TableModel model = new TableModel(this.MobiContext); DateTimeRef dateTime = GetDate("hour"); string query = QueryHelp(); var lista = new List <dynamic>(); using (conn) { conn.Open(); IEnumerable <dynamic> all = conn.Query(query, new { @first = dateTime.From, @second = dateTime.To }); foreach (var ii in all) { lista.Add(ii); } conn.Close(); } ViewBag.Query = "From: " + dateTime.From + " To : " + dateTime.To; return(PartialView("_Table", lista)); }
//public string SplitHelper(string time) //{ // var select = String.Empty; // var groupBy = String.Empty; // if (time == "hour") // //select = " DATEADD(WK,DATEDIFF(wk, 0, m.Created),0) as 'Created' "; // //groupBy = " DATEADD(WK,DATEDIFF(wk, 0, m.Created ),0) "; // select = " DATEPART(wk, m.Created) as 'Created' "; // groupBy = " DATEPART(wk, m.Created) "; // if (time == "day") // select = " DATEPART(wk, m.Created) as 'Created' "; // groupBy = " DATEPART(wk, m.Created) "; // if (time == "week") // select = " DATEPART(wk, m.Created) as 'Created' "; // groupBy = " DATEPART(wk, m.Created) "; // return select + "+" + groupBy; //} public DateTimeRef GetDate(string time) { DateTimeRef result = new DateTimeRef(); DateTime refFrom; DateTime refTo; switch (time) { case "hour": refFrom = DateTime.Now.AddHours(-1); refTo = DateTime.Now; break; case "day": refFrom = DateTime.Today; refTo = DateTime.Now; break; case "yesterday": refFrom = DateTime.Today.AddDays(-1); refTo = DateTime.Today; break; case "week": refFrom = DateTime.Now.StartOfWeek(DayOfWeek.Monday); refTo = DateTime.Now; break; case "month": DateTime now = DateTime.Now; refFrom = new DateTime(now.Year, now.Month, 1); refTo = DateTime.Now; break; case "year": refFrom = DateTime.Now.AddYears(-1); refTo = DateTime.Now; break; default: refFrom = DateTime.Now; refTo = DateTime.Now; break; } result.From = new DateTime(refFrom.Year, refFrom.Month, refFrom.Day, refFrom.Hour, refFrom.Minute, 0); result.To = new DateTime(refTo.Year, refTo.Month, refTo.Day, refTo.Hour, refTo.Minute, 0); return(result); }