public ActionResult Report21(Report21Model model)
{
return new ReportsGenController().Report21(model);
}
public ActionResult Report21(Report21Model model)
{
if (model.district_id == null)
{
return Content("找不到任何紀錄。");
}
else
{
string districtSql = "SELECT * FROM recsys_district as d WHERE d.status = '1' AND d.id =" + model.district_id.ToString();
IEnumerable<recsys_district> districts = Common.queryToObject<recsys_district>(this._db.ExecuteStoreQuery<recsys_district>(districtSql));
if (districts.Count() > 0)
{
ViewBag.district = districts.ElementAt(0).name;
}
else
{
return Content("找不到任何紀錄。");
}
}
string sql = @"
SELECT
m.id, c.customer_code, c.address2, c.name as customer_name, c.tel1, r.repair_date_frequency_in_month as routine, m.start_date
FROM recsys_maintenance as m
LEFT JOIN recsys_relate_customers as c on m.customer_id = c.id
LEFT JOIN recsys_routine as r on m.master_routine_id = r.id
WHERE m.status = '1' AND c.district2 = " + model.district_id.ToString() + " AND m.confirm_date is not null";
//Filtering
if (model.show_printed.HasValue && model.show_printed.Value == false) sql = sql + @" AND m.printed = '0'";
if (!string.IsNullOrEmpty(model.first_print_date) && model.show_printed.Value == true)
{
sql = sql + string.Format(" AND m.first_print_Date >= '{0}' AND m.first_print_Date < DATEADD(day, 1, '{0}')",
ConvertDateStringFormat(model.first_print_date, DATEPICKER_DATE_FORMAT, DB_DATE_FORMAT)
);
}
sql = sql + " ORDER BY m.start_date ASC";
IEnumerable<report21> maintenances = Common.queryToObject<report21>(this._db.ExecuteStoreQuery<report21>(sql));
if (maintenances.Count() > 0)
{
foreach (report21 maintenance in maintenances)
{
sql = Common.doSql("UPDATE recsys_maintenance SET printed = '1', first_print_Date = getDate() WHERE id = '" + maintenance.id + "' AND printed = '0'");
this._db.ExecuteStoreCommand(sql);
}
this._db.SaveChanges();
return View("~/Views/ReportsGen/Report21.cshtml", maintenances);
}
else
{
return Content("找不到任何紀錄。");
}
}