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