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