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("找不到任何紀錄。"); } }