public ActionResult Report12(Report12Model model)
{
//return Pdf("/reportsgen/report12/", this._report[12].ToString(), false);
return new ReportsGenController().Report12(model);
}
public ActionResult Report12(Report12Model model)
{
int user_id = model.user_id;
string order_id = model.order_id;
string start_date = model.start_date;
string end_date = model.end_date;
string customer_code = model.customer_code;
string customer_name = model.customer_name;
ReportECallByTechnician.ReportModel reportModel = new ReportECallByTechnician.ReportModel();
ArrayList where = new ArrayList();
if (!String.IsNullOrWhiteSpace(start_date))
where.Add("o.repair_date >= '" + ConvertDateStringFormat(start_date, DATEPICKER_DATE_FORMAT, DB_DATE_FORMAT) + "'");
if (!String.IsNullOrWhiteSpace(end_date))
where.Add("o.repair_date < DATEADD(day, 1, '" + ConvertDateStringFormat(end_date, DATEPICKER_DATE_FORMAT, DB_DATE_FORMAT) + "')");
if (!String.IsNullOrWhiteSpace(order_id))
where.Add("o.id = '" + order_id + "'");
if (user_id > 0)
{
where.Add("(isnull(o.engineer_id_1, 0) = '" + user_id + "' or isnull(o.engineer_id_2, 0) = '" + user_id + "' or isnull(o.engineer_id_3, 0) = '" + user_id + "' or isnull(o.engineer_id_4, 0) = '" + user_id + "' )");
reportModel.TechnicianID = user_id;
}
if (model.MasterCustomerID.HasValue)
where.Add("c.customer_id = " + model.MasterCustomerID.Value);
string sql = @"
select
o.id, o.repair_date, o.start_time, o.end_time, o.remark, o.fault,
e1.name as technician1, e2.name as technician2, e3.name as technician3, e4.name as technician4, o.reviewer,
c.name AS customer_name, c.address2, c.tel1, c.contact,
c.type, c.code, c.manual_input_code, c.prefix,
c.title, pu.name as recipient, e1.id as technician1_id, e2.id as technician2_id, e3.id as technician3_id, e4.id as technician4_id,
c.customer_code as customer_code
from recsys_order as o
left outer join recsys_relate_customers as c on o.customer_id = c.id
left outer join recsys_users as e1 on o.engineer_id_1 = e1.id
left outer join recsys_users as e2 on o.engineer_id_2 = e2.id
left outer join recsys_users as e3 on o.engineer_id_3 = e3.id
left outer join recsys_users as e4 on o.engineer_id_4 = e4.id
left outer join recsys_users as pu on o.call_pickup_user_id = pu.id
where o.status = '1'
and o.by_system = 0
" + (where.Count > 0 ? " AND " + String.Join(" AND ", where.ToArray()) : String.Empty) + @"
order by o.repair_date desc";
reportModel.Records = Common.queryToObject<ReportECallByTechnician.RecordModel>(this._db.ExecuteStoreQuery<ReportECallByTechnician.RecordModel>(sql)).ToList();
if (reportModel.Records.Count > 0)
{
ViewBag.date = Common.to_date("d/M/yyyy", DateTime.Now);
return View("~/Views/ReportsGen/Report12.cshtml", reportModel);
}
else
{
return Content("找不到任何紀錄。");
}
}