public ActionResult Report14(Report14Model model)
{
return new ReportsGenController().Report14(model);
}
public ActionResult Report14(Report14Model model)
{
int type = model.type;
string order_id = model.job_id;
ArrayList where = new ArrayList();
string title = String.Empty;
if (!String.IsNullOrWhiteSpace(order_id))
{
where.Add("o.id = '" + order_id + "'");
}
switch (type)
{
default:
case 1:
title = "JN Register";
break;
case 2:
title = "JN Register T&C";
where.Add("isnull( o.tc, 0 ) = 1");
break;
case 3:
title = "JN Register (not including T&C)";
where.Add("isnull( o.tc, 0) = 0");
break;
case 4:
title = "JN Register w/o Service Date";
where.Add("o.repair_date is null");
break;
}
string start_date = model.start_date;
string end_date = model.end_date;
bool hasDateFilter = false;
if (!String.IsNullOrWhiteSpace(start_date))
{
where.Add("o.repair_date >= '" + ConvertDateStringFormat(start_date, DATEPICKER_DATE_FORMAT, DB_DATE_FORMAT) + "'");
hasDateFilter = true;
}
if (!String.IsNullOrWhiteSpace(end_date))
{
where.Add("o.repair_date < DATEADD(day, 1, '" + ConvertDateStringFormat(end_date, DATEPICKER_DATE_FORMAT, DB_DATE_FORMAT) + "')");
hasDateFilter = true;
}
string sql = Common.doSql(
@"SELECT
o.id, o.model, o.fault, o.repair,
o.report, o.repair_date, o.start_time, o.end_time, o.remark,
o.create_date, c.tel1, c.contact, c.title, c.prefix,
c.manual_input_code
FROM {p}_order AS o
LEFT JOIN {p}_relate_customers AS c ON o.customer_id = c.id
WHERE o.status = '1'
and c.customer_code like 'JN%'
" + (where.Count > 0 ? " AND " + String.Join(" AND ", where.ToArray()) : String.Empty)
+ " ORDER BY o.repair_date ASC");
ArrayList order = Common.query(this._db.ExecuteStoreQuery<report14>(sql));
if (order.Count > 0)
{
string[] header = new string[]
{
"ID",
"來電時間",
"Job No.",
"客戶/機型/ Serial No.",
"故障內容",
"Rate",
"Less",
"Amount",
"檢查內容/報告",
"聯絡人",
"電話",
"到場時間",
"完成時間",
"備註",
"說明"
};
ExcelPackage excel = new ExcelPackage();
ExcelWorksheet sheet = excel.Workbook.Worksheets.Add(title);
sheet.Cells[1, 1].Value = "REC ENGINEERING CONTRACTING CO., LTD.";
sheet.Cells[2, 1].Value = "盈電機電工程有限公司";
sheet.Cells[1, 1, 1, 4].Merge = true;
sheet.Cells[2, 1, 2, 4].Merge = true;
sheet.Cells[1, 1, 2, 1].Style.Font.Bold = true;
sheet.Cells[3, 9].Value = title;
sheet.Cells[3, 9, 4, 13].Merge = true;
sheet.Cells[3, 9].Style.Font.Size = 20;
sheet.Cells[3, 9].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
sheet.Cells[3, 14].Value = "Start Date:";
sheet.Cells[3, 15].Value = hasDateFilter ? string.IsNullOrWhiteSpace(start_date) ? string.Empty
: ConvertDateStringFormat(start_date, DATEPICKER_DATE_FORMAT, "d/MM/yyyy")
: Common.to_date("d/MM/yyyy", ((Hashtable)order[0])["repair_date"]);
sheet.Cells[4, 14].Value = "End Date:";
sheet.Cells[4, 15].Value = hasDateFilter ? string.IsNullOrWhiteSpace(end_date) ? string.Empty
: ConvertDateStringFormat(end_date, DATEPICKER_DATE_FORMAT, "d/MM/yyyy")
: Common.to_date("d/MM/yyyy", ((Hashtable)order[order.Count - 1])["repair_date"]);
sheet.Cells[3, 15, 4, 15].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
int row = 5, col = 1;
foreach (string colName in header)
{
sheet.Cells[row, col++].Value = colName;
}
++row;
//sheet.View.FreezePanes(++row, col);
sheet.Column(5).Width = 18;
sheet.Column(6).Width = 18;
foreach (Hashtable item in order)
{
col = 1;
sheet.Cells[row, col++].Value = item["id"];
sheet.Cells[row, col++].Value = Common.to_date("yyyy-MM-dd", item["create_date"]) != "0001-01-01" ? Common.to_date("d/M/yy", item["create_date"]) : String.Empty;
sheet.Cells[row, col++].Value = (item["prefix"] == null ? "" : item["prefix"].ToString()) + (item["manual_input_code"] == null ? string.Empty : item["manual_input_code"].ToString());
sheet.Cells[row, col++].Value = item["model"];
sheet.Cells[row, col++].Value = item["fault"] + "\r\n" + item["repair"];
sheet.Cells[row, col++].Value = String.Empty;
sheet.Cells[row, col++].Value = String.Empty;
sheet.Cells[row, col++].Value = String.Empty;
sheet.Cells[row, col++].Value = item["report"];
sheet.Cells[row, col++].Value = item["contact"] + (Convert.ToInt32(item["title"]) > 0 ? this._title[item["title"].ToString()].ToString() : String.Empty);
sheet.Cells[row, col++].Value = item["tel1"];
sheet.Cells[row, col++].Value = Common.to_date("d/M/yy", item["repair_date"]) + " " + Common.to_date("hh:mm:ss", item["start_time"]);
sheet.Cells[row, col++].Value = Common.to_date("d/M/yy", item["repair_date"]) + " " + Common.to_date("hh:mm:ss", item["end_time"]);
sheet.Cells[row, col++].Value = item["remark"];
sheet.Cells[row, col++].Value = "C";
sheet.Row(row).Style.VerticalAlignment = ExcelVerticalAlignment.Top;
row++;
}
for (int i = 1; i < col; i++)
{
sheet.Column(i).AutoFit();
}
return File(excel.GetAsByteArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", title + ".xlsx");
}
else
{
return Content("找不到任何紀錄。");
}
}