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();
}