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