public ActionResult Report6(Report6Model model) { return Pdf("/reportsgen/report6/", this._report[6].ToString()); }
public ActionResult Report6(Report6Model model) { string start_date = model.start_date; ArrayList where = new ArrayList(); int max = 7; if (String.IsNullOrWhiteSpace(start_date)) { start_date = Common.to_date("yyyy-MM-01", DateTime.Now); } else { start_date = ConvertDateStringFormat(start_date, DATEPICKER_DATE_FORMAT, DB_DATE_FORMAT); } DateTime start; DateTime.TryParse(start_date, null, DateTimeStyles.None, out start); start_date = start.ToString("yyyy-MM-01"); string sql = Common.doSql(@" DECLARE @startDate Datetime SET @startDate = '" + start_date + @"' SELECT CONVERT(NVARCHAR(7), @startDate, 120) AS [DATE], SUM(A.amount_per_month) AS [total] FROM (SELECT id, start_date, end_date, (DATEDIFF(mm, start_date, end_date)+1) AS [no_of_month], amount / (DATEDIFF(mm, start_date, end_date)+1) AS [amount_per_month] FROM recsys_maintenance WHERE start_date is not null AND end_date is not null AND end_date>start_date AND amount is not null AND start_date<=@startDate AND end_date >=@startDate AND status = 1 ) A UNION SELECT CONVERT(NVARCHAR(7), DATEADD(MM, 1, @startDate), 120) AS [DATE], SUM(B.amount_per_month) AS [total] FROM (SELECT id, start_date, end_date, (DATEDIFF(mm, start_date, end_date)+1) AS [no_of_month], amount / (DATEDIFF(mm, start_date, end_date)+1) AS [amount_per_month] FROM recsys_maintenance WHERE start_date is not null AND end_date is not null AND end_date>start_date AND amount is not null AND start_date<=DATEADD(MM, 1, @startDate) AND end_date >=DATEADD(MM, 1, @startDate) AND status = 1 ) B UNION SELECT CONVERT(NVARCHAR(7), DATEADD(MM, 2, @startDate), 120) AS [DATE], SUM(C.amount_per_month) AS [total] FROM (SELECT id, start_date, end_date, (DATEDIFF(mm, start_date, end_date)+1) AS [no_of_month], amount / (DATEDIFF(mm, start_date, end_date)+1) AS [amount_per_month] FROM recsys_maintenance WHERE start_date is not null AND end_date is not null AND end_date>start_date AND amount is not null AND start_date<=DATEADD(MM, 2, @startDate) AND end_date >=DATEADD(MM, 2, @startDate) AND status = 1 ) C UNION SELECT CONVERT(NVARCHAR(7), DATEADD(MM, 3, @startDate), 120) AS [DATE], SUM(D.amount_per_month) AS [total] FROM (SELECT id, start_date, end_date, (DATEDIFF(mm, start_date, end_date)+1) AS [no_of_month], amount / (DATEDIFF(mm, start_date, end_date)+1) AS [amount_per_month] FROM recsys_maintenance WHERE start_date is not null AND end_date is not null AND end_date>start_date AND amount is not null AND start_date<=DATEADD(MM, 3, @startDate) AND end_date >=DATEADD(MM, 3, @startDate) AND status = 1 ) D UNION SELECT CONVERT(NVARCHAR(7), DATEADD(MM, 4, @startDate), 120) AS [DATE], SUM(E.amount_per_month) AS [total] FROM (SELECT id, start_date, end_date, (DATEDIFF(mm, start_date, end_date)+1) AS [no_of_month], amount / (DATEDIFF(mm, start_date, end_date)+1) AS [amount_per_month] FROM recsys_maintenance WHERE start_date is not null AND end_date is not null AND end_date>start_date AND amount is not null AND start_date<=DATEADD(MM, 4, @startDate) AND end_date >=DATEADD(MM, 4, @startDate) AND status = 1 ) E UNION SELECT CONVERT(NVARCHAR(7), DATEADD(MM, 5, @startDate), 120) AS [DATE], SUM(F.amount_per_month) AS [total] FROM (SELECT id, start_date, end_date, (DATEDIFF(mm, start_date, end_date)+1) AS [no_of_month], amount / (DATEDIFF(mm, start_date, end_date)+1) AS [amount_per_month] FROM recsys_maintenance WHERE start_date is not null AND end_date is not null AND end_date>start_date AND amount is not null AND start_date<=DATEADD(MM, 5, @startDate) AND end_date >=DATEADD(MM, 5, @startDate) AND status = 1 ) F UNION SELECT CONVERT(NVARCHAR(7), DATEADD(MM, 6, @startDate), 120) AS [DATE], SUM(G.amount_per_month) AS [total] FROM (SELECT id, start_date, end_date, (DATEDIFF(mm, start_date, end_date)+1) AS [no_of_month], amount / (DATEDIFF(mm, start_date, end_date)+1) AS [amount_per_month] FROM recsys_maintenance WHERE start_date is not null AND end_date is not null AND end_date>start_date AND amount is not null AND start_date<=DATEADD(MM, 6, @startDate) AND end_date >=DATEADD(MM, 6, @startDate) AND status = 1 ) G "); ArrayList order = Common.query(this._db.ExecuteStoreQuery<report6>(sql)); ArrayList date = new ArrayList(); ArrayList total = new ArrayList(); foreach (Hashtable item in order) { date.Add(item["date"]); total.Add(item["total"]); } ViewBag.date = date; ViewBag.total = total; ViewBag.start_date = Common.to_date("dddd, d MMMM, yyyy", start_date); ViewBag.start = start; ViewBag.today = Common.to_date("dddd, d MMMM, yyyy", DateTime.Now); ViewBag.max = max; return View(); }