public ActionResult Report20(Report20Model model) { //return Pdf("/reportsgen/report20/", this._report[20].ToString(), false); return new ReportsGenController().Report20(model); }
public ActionResult Report20(Report20Model model) { int choose_month; int choose_year; if (model.choose_month == 0) // no selection choose_month = DateTime.Now.Month; else //value selected choose_month = model.choose_month; if (model.choose_year == 0) // no selection choose_year = DateTime.Now.Year; else //value selected choose_year = System.DateTime.Now.AddYears(model.choose_year-5).Year; ArrayList where = new ArrayList(); if (choose_month >= 0 ) { string start_date = new DateTime(choose_year, choose_month, 1).ToString("yyyy-MM-dd"); string end_date = new DateTime(choose_year, choose_month, 1).AddMonths(1).ToString("yyyy-MM-dd"); where.Add("repair_date >= '" + start_date + "'"); where.Add("repair_date < '" + end_date + "'"); } else { return Content("找不到任何紀錄。"); } string sql = @" select o.id, o.repair_date, c.customer_code, c.name as customer_name, c.address2, o.create_date, o.fault, c.contact, c.tel1, pu.name as recipient, e1.name as technician1, e2.name as technician2, e3.name as technician3, e4.name as technician4, ahe.name as adhocTechnician, o.start_time, o.end_time, o.actual_start_time, o.actual_end_time, o.completion_date, o.remark 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 pu on o.call_pickup_user_id = pu.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 ahe on o.ad_hoc_engineer_id = ahe.id where o.status = '1' AND o.by_system = '1' AND o.order_status = '" + model.order_status + "'" + (where.Count > 0 ? " AND " + String.Join(" AND ", where.ToArray()) : String.Empty) + @" order by c.address2"; IEnumerable<report20> records = Common.queryToObject<report20>(this._db.ExecuteStoreQuery<report20>(sql)).ToList(); if (records.Count() > 0) { string title = "Maintenance Status Report"; string[] header = new string[] { "項目", "Order ID", "ACM", "客戶名稱", "地址", "落單日期", "內容", "聯絡人", "電話", "接聽者", "維修員1", "維修員2", "維修員3", "維修員4", "臨時維修員", "接單時間", "完成時間", "實際完成日期", "備註" }; ExcelPackage excel = new ExcelPackage(); ExcelWorksheet sheet = excel.Workbook.Worksheets.Add(title); sheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; #region top sheet.Cells[1, 1].Value = title; sheet.Cells[1, 1, 1, 4].Merge = true; sheet.Cells[1, 1].Style.Font.Bold = true; sheet.Cells[1, 1].Style.Font.Size = 16; sheet.Cells[2, 1, 2, 4].Merge = true; sheet.Cells[2, 1].Value = "Status : " + this._order_status[model.order_status-1].ToString(); sheet.Cells[3, 1, 3, 4].Merge = true; sheet.Cells[3, 1].Value = "Selection month : " + choose_year.ToString() + "/" + choose_month.ToString("00"); #endregion top int row = 5, col = 1, i =1; #region header foreach (string colName in header) { sheet.Cells[row, col++].Value = colName; } sheet.Row(5).Height = 30.0; #endregion header ++row; #region data foreach (report20 record in records) { col = 1; sheet.Cells[row, col++].Value = i; sheet.Cells[row, col++].Value = record.id; sheet.Cells[row, col++].Value = record.customer_code; sheet.Cells[row, col++].Value = record.customer_name; sheet.Cells[row, col++].Value = record.address2; sheet.Cells[row, col++].Value = string.Format("{0:dd/MM/yyyy}", record.create_date); sheet.Cells[row, col++].Value = record.fault; sheet.Cells[row, col++].Value = record.contact; sheet.Cells[row, col++].Value = record.tel1; sheet.Cells[row, col++].Value = record.recipient; sheet.Cells[row, col++].Value = record.technician1; sheet.Cells[row, col++].Value = record.technician2; sheet.Cells[row, col++].Value = record.technician3; sheet.Cells[row, col++].Value = record.technician4; sheet.Cells[row, col++].Value = record.ad_hoc_technician; sheet.Cells[row, col++].Value = (record.actual_start_time != null) ? string.Format("{0:hh\\:mm}", record.actual_start_time) : string.Format("{0:hh\\:mm}", record.start_time); sheet.Cells[row, col++].Value = (record.actual_end_time != null) ? string.Format("{0:hh\\:mm}", record.actual_end_time) : string.Format("{0:hh\\:mm}", record.end_time); sheet.Cells[row, col++].Value = string.Format("{0:dd/MM/yyyy}", record.completion_date); sheet.Cells[row++, col++].Value = record.remark; i++; } #endregion data #region set alignments sheet.Cells[1, 1, row - 1, col - 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center; sheet.Cells[5, 1, row - 1, col - 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; #endregion set alignments for (i = 1; i < col; i++) for (int j = 5; j < row; j++) sheet.Cells[j, i].Style.Border.BorderAround(ExcelBorderStyle.Thin); #region autofit columns sheet.Column(1).AutoFit(10.0, 11.0); sheet.Column(2).AutoFit(14.0, 15.0); sheet.Column(3).AutoFit(11.0, 23.0); sheet.Column(4).AutoFit(0, 30.0); sheet.Column(5).AutoFit(0, 30.0); sheet.Column(6).AutoFit(14.5, 15.0); sheet.Column(7).AutoFit(10.5, 15.0); sheet.Column(8).AutoFit(0, 30.0); sheet.Column(9).AutoFit(12.5, 20.0); for (i = 10 ; i <= 14; i++) { sheet.Column(i).AutoFit(14.0, 15.0); } sheet.Column(15).AutoFit(16.5, 17.0); sheet.Column(16).AutoFit(14.0, 15.0); sheet.Column(17).AutoFit(14.0, 15.0); sheet.Column(18).AutoFit(19.0, 20.0); sheet.Column(19).AutoFit(14.0, 50.0); #endregion autofit columns #region set time and date format sheet.Cells[6, 6, row - 1, 6].Style.Numberformat.Format = "dd-MM-yyyy"; sheet.Cells[6, 18, row - 1, 18].Style.Numberformat.Format = "dd-MM-yyyy"; sheet.Cells[6, 16, row - 1, 16].Style.Numberformat.Format = "hh\\:mm"; sheet.Cells[6, 17, row - 1, 17].Style.Numberformat.Format = "hh\\:mm"; #endregion set time and date format #region set wrap text sheet.Cells[6, 3, row - 1, 5].Style.WrapText = true; sheet.Cells[6, 7, row - 1, 9].Style.WrapText = true; sheet.Cells[6, 19, row - 1, 19].Style.WrapText = true; #endregion sheet.Cells[5, 1, row - 1, col - 1].AutoFilter = true; sheet.PrinterSettings.FitToPage = true; sheet.PrinterSettings.FitToWidth = 1; sheet.PrinterSettings.FitToHeight = 0; return File(excel.GetAsByteArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", title + ".xlsx"); //return View("~/Views/ReportsGen/Report20.cshtml", records); } else { return Content("找不到任何紀錄。"); } }