public ActionResult Report18(Report18Model model) { //return Pdf("/reportsgen/report18/", this._report[18].ToString(), false); return new ReportsGenController().Report18(model); }
public ActionResult Report18(Report18Model model) { string maintenance_id = model.maintenance_id; string customer_code = model.customer_code; string customer_name = model.customer_name; string start_date = model.start_date; string end_date = model.end_date; ArrayList where = new ArrayList(); if (!String.IsNullOrWhiteSpace(maintenance_id)) { where.Add("m.maintenance_id = '" + maintenance_id + "'"); } if (model.MasterCustomerID.HasValue) where.Add("c.customer_id = " + model.MasterCustomerID.Value); if (String.IsNullOrWhiteSpace(start_date) && String.IsNullOrWhiteSpace(end_date)) { where.Add("m.end_date >= '" + DateTime.Today.AddMonths(-1).ToString(DB_DATE_FORMAT) + "' "); } else { try { DateTime.ParseExact(model.start_date, "dd-MM-yyyy", null); } catch (Exception ex) { return Content("開始日期不符合日期格式"); } try { DateTime.ParseExact(model.end_date, "dd-MM-yyyy", null); } catch (Exception ex) { return Content("完結日期不符合日期格式"); } where.Add("m.start_date < DATEADD(day, 1, '" + ConvertDateStringFormat(end_date, DATEPICKER_DATE_FORMAT, DB_DATE_FORMAT) + "')"); where.Add("m.end_date >= '" + ConvertDateStringFormat(start_date, DATEPICKER_DATE_FORMAT, DB_DATE_FORMAT) + "'"); } string sql = Common.doSql(@" SELECT m.id, m.start_date, m.end_date, m.period, c.customer_code, c.name AS customer_name, c.address2 FROM {p}_maintenance AS m LEFT JOIN {p}_relate_customers AS c ON m.customer_id = c.id LEFT JOIN {p}_district AS d ON c.district2 = d.id WHERE m.status = '1'" + (where.Count > 0 ? " AND " + String.Join(" AND ", where.ToArray()) : String.Empty) + " ORDER BY c.customer_code ASC, d.region ASC, d.name, c.address2, m.end_date desc, m.id ASC"); IEnumerable<report18> maintenance = Common.queryToObject<report18>(this._db.ExecuteStoreQuery<report18>(sql)); IEnumerable<report18_order> orders = null; if (maintenance.Count() > 0) { IEnumerable<int> contractIDs = maintenance.Select(theContract => theContract.id); if (contractIDs.Count() > 0) { string contractOrderSQL = Common.doSql(@" SELECT r.id1 as maintenance_id, o.repair_date, o.completion_date, CASE WHEN o.completion_date is not null THEN o.completion_date ELSE o.repair_date END as order_date FROM {p}_relate AS r LEFT JOIN {p}_order AS o ON r.id2 = o.id WHERE o.by_system = 1 AND o.status = 1 AND r.id1 IN ('" + String.Join("','", contractIDs) + "') AND table1 = 'maintenance' AND r.table2 = 'order' ORDER BY r.id1 ASC, order_date ASC, o.id ASC"); orders = Common.queryToObject<report18_order>(this._db.ExecuteStoreQuery<report18_order>(contractOrderSQL)); foreach (report18 item in maintenance) { item.orders = orders.Where(theOrder => theOrder.maintenance_id == item.id).OrderBy(theOrder => theOrder.order_date).ToList(); item.customer_code = trimZero(item.customer_code); } } /*-----------Excel----------*/ string title = "Customer Service Date Matching List"; string[] header = new string[] { "客戶編號", "客戶名稱", "客戶地址", "開始日期", "完成日期", "期數" }; int maxOrderTime = maintenance.Max(x => x.period).Value; 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, 10].Merge = true; sheet.Cells[1, 1].Style.Font.Bold = true; sheet.Cells[1, 1].Style.Font.Size = 20; sheet.Cells[3, 1].Value = "選擇日期 : "; sheet.Cells[3, 2].Value = start_date + " 至 " + end_date; sheet.Cells[3, 1, 3, 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; sheet.Cells[3, 2].Style.Border.Bottom.Style = ExcelBorderStyle.Thin; sheet.Cells[3, 4].Style.Fill.PatternType = ExcelFillStyle.Solid; sheet.Cells[3, 4].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(146,208,80)); sheet.Cells[3, 4].Style.Border.BorderAround(ExcelBorderStyle.Thin); sheet.Cells[3, 5].Value = "Completed"; sheet.Cells[3, 5, 3, 6].Merge = true; sheet.Cells[5, 4].Value = "*"; sheet.Cells[5, 4].Style.Border.BorderAround(ExcelBorderStyle.Thin); sheet.Cells[5, 4].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; sheet.Cells[5, 5].Value = "Behind Schedule"; sheet.Cells[5, 5, 5, 6].Merge = true; #endregion int row = 7, col = 1, i = 1; string area = String.Empty; #region header foreach (string colName in header) { sheet.Cells[row, col++].Value = colName; } int time; for ( ; col-6 <= maxOrderTime ; col++) { time = col - 6; sheet.Cells[row, col].Value = "第" + time + "次"; } sheet.Cells[row, col].Value = "尚欠"; sheet.Cells[7, 1, 7, col].Style.Font.Bold = true; #endregion ++row; string lastCustomerCode = ""; string lastAddress = ""; int repeatedIDnum = 0; int repeatedAddressNum = 0; foreach (report18 item in maintenance) { col = 1; #region merge previous records if it has multiple contracts if (lastCustomerCode == item.customer_code) { repeatedIDnum++; if (lastAddress == item.address2) repeatedAddressNum++; else { if (repeatedAddressNum > 0) sheet.Cells[row - 1 - repeatedAddressNum, 3, row - 1, 3].Merge = true; repeatedAddressNum = 0; } } else { if (repeatedIDnum > 0) { sheet.Cells[row - 1 - repeatedIDnum, 1, row - 1, 1].Merge = true; sheet.Cells[row - 1 - repeatedIDnum, 2, row - 1, 2].Merge = true; if (repeatedAddressNum > 0) sheet.Cells[row - 1 - repeatedAddressNum, 3, row - 1, 3].Merge = true; repeatedAddressNum = 0; } else if (row - 1 != 7) sheet.Row(row - 1).Height = 30; repeatedIDnum = 0; } #endregion #region data sheet.Cells[row, col++].Value = trimZero(item.customer_code); ; sheet.Cells[row, col++].Value = item.customer_name; sheet.Cells[row, col++].Value = item.address2; sheet.Cells[row, col].Style.Numberformat.Format = "dd/MM/yyyy"; sheet.Cells[row, col++].Value = String.Format("{0:dd/MM/yyyy}", item.start_date); sheet.Cells[row, col].Style.Numberformat.Format = "dd/MM/yyyy"; sheet.Cells[row, col++].Value = String.Format("{0:dd/MM/yyyy}", item.end_date); sheet.Cells[row, col++].Value = item.TotalNumberOfOrder; #endregion foreach (report18_order orderItem in item.orders) { string repairDate = String.Format("{0:dd/MM/yyyy}", orderItem.repair_date); sheet.Cells[row, col].Style.Numberformat.Format = "dd/MM/yyyy"; if (orderItem.Completed) { sheet.Cells[row, col].Style.Fill.PatternType = ExcelFillStyle.Solid; sheet.Cells[row, col].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(146, 208, 80)); sheet.Cells[row, col++].Value = String.Format("{0:dd/MM/yyyy}", orderItem.completion_date); } else sheet.Cells[row, col++].Value = orderItem.IsBehindSchedule ? repairDate + " *" : repairDate; } sheet.Cells[row, 7 + maxOrderTime].Value = item.CountFutureOrders; lastCustomerCode = item.customer_code; lastAddress = item.address2; row++; } #region merge records for the latest record if (repeatedIDnum > 0) { sheet.Cells[row - 1 - repeatedIDnum, 1, row - 1, 1].Merge = true; sheet.Cells[row - 1 - repeatedIDnum, 2, row - 1, 2].Merge = true; if (repeatedAddressNum > 0) { sheet.Cells[row - 1 - repeatedAddressNum, 3, row - 1, 3].Merge = true; } repeatedAddressNum = 0; } else if (row - 1 != 7) sheet.Row(row - 1).Height = 30; #endregion #region set alignments sheet.Cells[1, 1, row - 1, maxOrderTime + 7].Style.VerticalAlignment = ExcelVerticalAlignment.Center; sheet.Cells[7, 1, row - 1, maxOrderTime + 7].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; #endregion for (i = 1 ; i <= maxOrderTime+7 ; i++) for (int j = 7; j < row ; j++) sheet.Cells[j, i].Style.Border.BorderAround(ExcelBorderStyle.Thin); #region autofit columns sheet.Column(1).AutoFit(0, 13.5); sheet.Column(2).AutoFit(0, 27.5); sheet.Column(3).AutoFit(0, 27.5); sheet.Column(4).AutoFit(0, 13.5); sheet.Column(5).AutoFit(0, 13.5); sheet.Column(6).AutoFit(0, 9); sheet.Column(maxOrderTime + 7).AutoFit(0, 8.5); for (i = 7; i < maxOrderTime + 7; i++) { sheet.Column(i).AutoFit(0, 12.5); } #endregion sheet.Cells[8, 2, row - 1, 3].Style.WrapText = 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"); //ViewBag.maintenance = maintenance; } else { return Content("找不到任何紀錄。"); } }