public ActionResult Report15(Report15Model model) { return new ReportsGenController().Report15(model); }
public ActionResult Report15(Report15Model model) { string customer_code = model.customer_code; string customer_name = model.customer_name; Boolean show_confirmed_only = model.show_confirmed_only; ArrayList where = new ArrayList(); if (model.choose_region.Trim().Length > 0) { string[] chosenRegionList = model.choose_region.Split(','); string regionIDFilter = string.Empty; string regionNullFilter = " d.region is null "; bool isRegionNullFilterChoose = false; foreach (var regionId in chosenRegionList) { if (regionId.Equals("-1")) isRegionNullFilterChoose = true; else { regionIDFilter += regionId + ","; } } if (isRegionNullFilterChoose) { if (!string.IsNullOrEmpty(regionIDFilter)) where.Add("(d.region in ( " + regionIDFilter.TrimEnd(',') + " ) or " + regionNullFilter + " )"); else where.Add(regionNullFilter); } else { where.Add("d.region in ( " + regionIDFilter.TrimEnd(',') + " )"); } } else { return Content("找不到任何紀錄。"); } if (!String.IsNullOrWhiteSpace(customer_code)) { where.Add("c.customer_code LIKE '%" + customer_code + "%'"); } if (!String.IsNullOrWhiteSpace(customer_name)) { where.Add("c.name LIKE '%" + customer_name + "%'"); } if (show_confirmed_only) { where.Add("m.billing_date IS NOT NULL"); } string title = "Active Customer Maintenance Records List"; string sql = Common.doSql(@" SELECT m.id, m.start_date, m.end_date, m.confirm_date, m.model, COALESCE(m.period, 0) as period, m.remark, c.name AS customer_name, COALESCE(d.region, 0) as region, d.name AS district, c.address2, m.amount, m.checking_date, c.customer_code 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' AND m.end_date >= '" + Common.to_date(DB_DATE_FORMAT, DateTime.Now.AddMonths(-1)) + "'" + (where.Count > 0 ? " AND " + String.Join(" AND ", where.ToArray()) : String.Empty) + " ORDER BY d.region ASC, d.name ASC, c.address2 ASC, m.start_date ASC"); IEnumerable<report15> maintenance = Common.queryToObject<report15>(this._db.ExecuteStoreQuery<report15>(sql)); if (maintenance.Count() > 0) { //# 20131126: Eddie: as cleaning service has become dynamics (maintenance -> additional_service : one to many), get service dates from another sql string maintenanceID_SQL_String = ""; foreach (report15 item in maintenance) maintenanceID_SQL_String += (string.IsNullOrEmpty(maintenanceID_SQL_String) ? string.Empty : ", ") + item.id; string clearningServiceSQL = string.Format(@" SELECT [maintenance_id], [service_date] FROM [recsys_additional_service] where [maintenance_id] in ({0}) order by [maintenance_id] ", maintenanceID_SQL_String); IEnumerable<report15AdditionalService> cleaningServices = Common.queryToObject<report15AdditionalService>(this._db.ExecuteStoreQuery<report15AdditionalService>(clearningServiceSQL)); var region = (from r in this._db.recsys_region orderby r.id ascending select r); Hashtable _region = Common.array_conv(Common.query(region), "id", "name"); _region.Add("0", "無地區"); string[] header = new string[] { String.Empty, "ACM", "客戶名稱", "地區", "地址", "詳細地址", "start date", "complete date", "cfm_date", "Amount", "機型及數量", "routine", "remark", "cleaning date", "fire damper checking date" }; ExcelPackage excel = new ExcelPackage(); ExcelWorksheet sheet = excel.Workbook.Worksheets.Add(title); sheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; 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, 2].Value = "complete date < today within 1 month"; sheet.Cells[3, 2, 3, 5].Merge = true; int row = 5, col = 1, i = 1; string area = String.Empty; foreach (string colName in header) { sheet.Cells[row, col++].Value = colName; } ++row; //sheet.View.FreezePanes(++row, col); foreach (report15 item in maintenance) { //# insert cleaning services to contract item.cleaningServices.AddRange(cleaningServices.Where(theService => theService.maintenance_id == item.id).OrderByDescending(theService => theService.service_date)); if (area != item.region.ToString()) { area = item.region.ToString(); sheet.Cells[row, 2].Value = _region[item.region.ToString()]; sheet.Cells[row, 2].Style.Font.Bold = true; sheet.Cells[row, 2].Style.Font.Size = 20; row++; } col = 1; sheet.Cells[row, col++].Value = i++; sheet.Cells[row, col++].Value = trimZero(item.customer_code); ; sheet.Cells[row, col++].Value = item.customer_name; sheet.Cells[row, col++].Value = _region[item.region.ToString()]; sheet.Cells[row, col++].Value = item.district; sheet.Cells[row, col++].Value = item.address2; sheet.Cells[row, col++].Value = item.start_date; sheet.Cells[row, col++].Value = item.end_date; sheet.Cells[row, col++].Value = Common.to_date("yyyy-MM-dd", item.confirm_date) != "1900-01-01" ? item.confirm_date : null; sheet.Cells[row, col++].Value = item.amount.HasValue?item.amount:0; sheet.Cells[row, col++].Value = item.model; sheet.Cells[row, col++].Value = item.period; sheet.Cells[row, col++].Value = item.remark; sheet.Cells[row, col].Style.WrapText = true; sheet.Cells[row, col++].Value = item.GetCleaningServiceAsCellValue("dd-MMM-yy", ", \n"); sheet.Cells[row, col++].Value = item.checking_date; sheet.Cells[row, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; sheet.Cells[row, 10].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; sheet.Cells[row, 12].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; //sheet.Cells[row, 7, row, 10].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right; row++; } #region set date column format sheet.Column(7).Style.Numberformat.Format = @"dd-MMM-yy"; sheet.Column(8).Style.Numberformat.Format = @"dd-MMM-yy"; sheet.Column(9).Style.Numberformat.Format = @"dd-MMM-yy"; sheet.Column(15).Style.Numberformat.Format = @"dd-MMM-yy"; #endregion #region set number format sheet.Column(10).Style.Numberformat.Format = @"#,##0.00"; #endregion for (i = 1; i < col; i++) { //sheet.Column(i).AutoFit(); if (i == 6 || i == 11 || i == 13) sheet.Column(i).AutoFit(0, 30); //address, model, remark else sheet.Column(i).AutoFit(0, 20); sheet.Column(i).Style.WrapText = true; sheet.Column(i).Style.VerticalAlignment = ExcelVerticalAlignment.Top; } sheet.Cells[6, 1, row, col].Style.Border.Bottom.Style = ExcelBorderStyle.Thin; sheet.Row(1).Style.WrapText = false; sheet.PrinterSettings.LeftMargin = sheet.PrinterSettings.RightMargin = sheet.PrinterSettings.TopMargin = sheet.PrinterSettings.BottomMargin = 0.3M / 2.54M; sheet.PrinterSettings.RepeatRows = sheet.Cells["5:5"]; sheet.PrinterSettings.Orientation = eOrientation.Landscape; sheet.HeaderFooter.OddHeader.RightAlignedText = string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages); sheet.HeaderFooter.EvenHeader.RightAlignedText = string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages); sheet.PrinterSettings.HeaderMargin = 0.3M / 2.54M; sheet.PrinterSettings.FitToPage = true; sheet.PrinterSettings.FitToWidth = 1; sheet.PrinterSettings.FitToHeight = 0; return File(excel.GetAsByteArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", title + ".xlsx"); } else { return Content("找不到任何紀錄。"); } }