public ActionResult Report23(Report23Model model)
{
return new ReportsGenController().Report23(model);
}
public ActionResult Report23(Report23Model model)
{
string number = model.number;
string customer_code = model.customer_code;
string customer_name = model.customer_chi_name;
string start_confirm_date = model.start_confirm_date;
string end_confirm_date = model.end_confirm_date;
string rec_invoice_no = model.REC_invoice;
string cost_invoice_no = model.cost_invoice;
ArrayList where = new ArrayList();
if (!String.IsNullOrWhiteSpace(number))
{
where.Add("q.number like '%" + number + "%'");
}
if (!String.IsNullOrWhiteSpace(customer_code))
{
where.Add("rc.customer_code LIKE '%" + customer_code + "%'");
}
if (!String.IsNullOrWhiteSpace(customer_name))
{
where.Add("rc.name LIKE '%" + customer_name + "%'");
}
if (!String.IsNullOrWhiteSpace(start_confirm_date))
{
try
{
DateTime.ParseExact(model.start_confirm_date, "dd-MM-yyyy", null);
}
catch (Exception ex)
{
return Content("開始日期不符合日期格式");
}
where.Add("q.confirm_date >= '" + ConvertDateStringFormat(start_confirm_date, DATEPICKER_DATE_FORMAT, DB_DATE_FORMAT) + "'");
}
if(!String.IsNullOrWhiteSpace(end_confirm_date))
{
try
{
DateTime.ParseExact(model.end_confirm_date, "dd-MM-yyyy", null);
}
catch (Exception ex)
{
return Content("完結日期不符合日期格式");
}
where.Add("q.confirm_date <= '" + ConvertDateStringFormat(end_confirm_date, DATEPICKER_DATE_FORMAT, DB_DATE_FORMAT) + "'");
}
if (!String.IsNullOrWhiteSpace(rec_invoice_no))
{
where.Add("q.invoice LIKE '%" + rec_invoice_no + "%'");
}
if (!String.IsNullOrWhiteSpace(cost_invoice_no))
{
where.Add("cs.invoice LIKE '%" + cost_invoice_no + "%'");
}
string sql = Common.doSql(@"
SELECT
q.id as quotation_id, rc.center, q.number, rc.customer_code, rc.chi_name as customer_name, q.confirm_date, q.billing_date, q.invoice as rec_invoice, q.subcon_name, q.subcon_estimation, q.subcon_name2, q.subcon_estimation2, q.subcon_name3, q.subcon_estimation3, q.supplier_name, q.supplier_estimation, q.supplier_name2, q.supplier_estimation2, q.supplier_name3, q.supplier_estimation3, q.supervision, q.supplier_id as material_estimation, q.material_estimation2, q.material_estimation3, q.direct_labour_cost, q.minor_work_currency as minor_work_currency, q.gp,
(cc.code + ' - ' + cc.name) as type, s.name as subCon_supplier, cs.price1 as cost, cs.invoice as subCon_invoice, cs.account_date as subCon_invoice_date
FROM [recsys_quotation] q
left join [recsys_relate] r on q.id = r.id2 and r.table1 = 'costs' and r.table2 = 'quotation'
left join [recsys_costs] cs on r.id1 = cs.id and cs.status='1'
left join [recsys_relate_customers] rc on q.customer_id = rc.id
left join [recsys_cost] cc on cs.cost_id = cc.id
left join [recsys_supplier] s on cs.supplier_id = s.id
where q.status='1'" + (where.Count > 0 ? " AND " + String.Join(" AND ", where.ToArray()) : String.Empty)
+ "ORDER BY q.id asc, cs.account_date desc, cs.invoice asc");
IEnumerable<report23> record = Common.queryToObject<report23>(this._db.ExecuteStoreQuery<report23>(sql));
if (record.Count() > 0)
{
/*-------------Excel-------------*/
string title = "Quotation Cost Breakdown Report";
string[] firstHeader = new string[]
{
"Centre",
"ACQ No.",
"客戶編號",
"客戶名稱(中文)",
"Confirm Date",
"Billing Date",
"REC Invoice No."
};
string[] secondHeader = new string[]
{
"Sub. Con. (1)",
"Sub. Con. (1) Cost",
"Sub. Con. (2)",
"Sub. Con. (2) Cost",
"Sub. Con. (3)",
"Sub. Con. (3) Cost",
"Supplier (1)",
"Supplier Cost (1)",
"Supplier (2)",
"Supplier Cost (2)",
"Supplier (3)",
"Supplier Cost (3)",
"Supervision",
"Material Cost (1)",
"Material Cost (2)",
"Material Cost (3)",
"Direct Labour",
"Minor Works",
"Total Estimation Cost",
"GP %",
"G.P.",
"Quotation Total Price:",
"Type",
"Sub. Con. / Supplier",
"Cost",
"Total Cost",
"Sub. Con. Invoice No.",
"Sub. Con. Invoice Date"
};
ExcelPackage excel = new ExcelPackage();
ExcelWorksheet sheet = excel.Workbook.Worksheets.Add(title);
sheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
#region top
sheet.Cells[2, 1].Value = title;
sheet.Cells[2, 1].Style.Font.Bold = true;
sheet.Cells[2, 1].Style.Font.Size = 22;
sheet.Cells[3, 1].Value = "Print Date: " + System.DateTime.Now.Date.ToString("dd-MM-yyyy");
sheet.Cells[2, 1, 2, 6].Merge = true;
sheet.Cells[3, 1, 3, 3].Merge = true;
#endregion top
int row = 6, col = 1, i = 1;
#region header
foreach (string colName in firstHeader)
{
sheet.Cells[row, col++].Value = colName;
}
sheet.Cells[row, col].Value = "Cost Estimation";
sheet.Cells[row, col, row, 29].Merge = true;
sheet.Cells[row, 30].Value = "Actual Cost";
sheet.Cells[row, 30, row++, 35].Merge = true;
foreach (string colName in secondHeader)
{
sheet.Cells[row, col++].Value = colName;
}
sheet.Cells[6, 30, 7, 35].Style.Fill.PatternType = ExcelFillStyle.Solid;
sheet.Cells[6, 30, 7, 35].Style.Fill.BackgroundColor.SetColor(Color.Yellow);
#endregion header
++row;
int lastQuotationID = 0;
int repeatedRecordNum = 0;
foreach (report23 data in record)
{
col = 1;
#region merge previous records for the same quotation
if (lastQuotationID == data.quotation_id)
{
repeatedRecordNum++;
}
else
{
if (repeatedRecordNum > 0)
{
for (int j = 1; j <= 29; j++)
{
sheet.Cells[row - 1 - repeatedRecordNum, j, row - 1, j].Merge = true;
}
sheet.Cells[row - 1 - repeatedRecordNum, 33, row - 1, 33].Merge = true;
sheet.Cells[row - repeatedRecordNum - 1, 33].Formula = string.Format("sum(AF{0}:AF{1})", row - 1 - repeatedRecordNum, row - 1);
}
else if (row > 8)
sheet.Cells[row - 1, 33].Formula = string.Format("sum(AF{0}:AF{1})", row - 1 - repeatedRecordNum, row - 1);
repeatedRecordNum = 0;
}
#endregion
#region data
sheet.Cells[row, col++].Value = data.center;
sheet.Cells[row, col++].Value = data.number;
sheet.Cells[row, col++].Value = data.customer_code;
sheet.Cells[row, col++].Value = data.customer_name;
sheet.Cells[row, col++].Value = data.confirm_date;
sheet.Cells[row, col++].Value = data.billing_date;
sheet.Cells[row, col++].Value = data.rec_invoice;
sheet.Cells[row, col++].Value = data.subcon_name;
sheet.Cells[row, col++].Value = data.subcon_estimation;
sheet.Cells[row, col++].Value = data.subcon_name2;
sheet.Cells[row, col++].Value = data.subcon_estimation2;
sheet.Cells[row, col++].Value = data.subcon_name3;
sheet.Cells[row, col++].Value = data.subcon_estimation3;
sheet.Cells[row, col++].Value = data.supplier_name;
sheet.Cells[row, col++].Value = data.supplier_estimation;
sheet.Cells[row, col++].Value = data.supplier_name2;
sheet.Cells[row, col++].Value = data.supplier_estimation2;
sheet.Cells[row, col++].Value = data.supplier_name3;
sheet.Cells[row, col++].Value = data.supplier_estimation3;
sheet.Cells[row, col++].Value = data.supervision;
sheet.Cells[row, col++].Value = data.material_estimation;
sheet.Cells[row, col++].Value = data.material_estimation2;
sheet.Cells[row, col++].Value = data.material_estimation3;
sheet.Cells[row, col++].Value = data.direct_labour_cost;
sheet.Cells[row, col++].Value = data.minor_work_currency;
sheet.Cells[row, col++].Formula = string.Format("I{0}+K{0}+M{0}+O{0}+Q{0}+S{0}+sum(T{0}:Y{0})", row);
sheet.Cells[row, col++].Formula = string.Format("if(or(AC{0}=\"\",AC{0}=0), \"\", AB{0}/AC{0})", row);
sheet.Cells[row, col++].Value = data.gp;
sheet.Cells[row, col++].Formula = string.Format("Z{0}+AB{0}", row);
sheet.Cells[row, col++].Value = data.type;
sheet.Cells[row, col++].Value = data.subCon_supplier;
sheet.Cells[row, col++].Value = data.cost;
col++;
sheet.Cells[row, col++].Value = data.subCon_invoice;
sheet.Cells[row, col++].Value = data.subCon_invoice_date;
#endregion data
lastQuotationID = data.quotation_id;
row++;
}
#region handle merging for the last quotation
if (repeatedRecordNum > 0)
{
for (int j = 1; j <= 29; j++)
{
sheet.Cells[row - 1 - repeatedRecordNum, j, row - 1, j].Merge = true;
}
sheet.Cells[row - 1 - repeatedRecordNum, 33, row - 1, 33].Merge = true;
sheet.Cells[row - 1 - repeatedRecordNum, 33].Formula = string.Format("sum(AF{0}:AF{1})", row - 1 - repeatedRecordNum, row - 1);
}
else
sheet.Cells[row - 1, 33].Formula = string.Format("sum(AF{0}:AF{1})", row - 1 - repeatedRecordNum, row - 1);
repeatedRecordNum = 0;
#endregion
#region set alignments
sheet.Cells[1, 1, row - 1, 35].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
sheet.Cells[6, 1, row - 1, 35].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
#endregion
for (i = 1; i <= 35; i++)
for (int j = 6; j < row; j++)
sheet.Cells[j, i].Style.Border.BorderAround(ExcelBorderStyle.Thin);
#region set number formats
sheet.Column(9).Style.Numberformat.Format = @"#,##0.00";
sheet.Column(11).Style.Numberformat.Format = @"#,##0.00";
sheet.Column(13).Style.Numberformat.Format = @"#,##0.00";
sheet.Column(15).Style.Numberformat.Format = @"#,##0.00";
sheet.Column(17).Style.Numberformat.Format = @"#,##0.00";
for(i=19;i<=26;i++)
sheet.Column(i).Style.Numberformat.Format = @"#,##0.00";
sheet.Column(28).Style.Numberformat.Format = @"#,##0.00";
sheet.Column(29).Style.Numberformat.Format = @"#,##0.00";
sheet.Column(32).Style.Numberformat.Format = @"#,##0.00";
sheet.Column(33).Style.Numberformat.Format = @"#,##0.00";
sheet.Column(27).Style.Numberformat.Format = @"0.00%";
#endregion
#region set date format
sheet.Column(5).Style.Numberformat.Format = "dd/MM/yyyy";
sheet.Column(6).Style.Numberformat.Format = "dd/MM/yyyy";
sheet.Column(35).Style.Numberformat.Format = "dd/MM/yyyy";
#endregion set date format
#region autofit columns
for (i = 1; i <= 3; i++)
{
sheet.Column(i).AutoFit(9, 40);
}
for (i = 5; i <= 35; i++)
{
sheet.Column(i).AutoFit(9, 40);
}
sheet.Column(4).AutoFit(9, 55);
#endregion autofit columns
for (i = 6; i < row; ++i)
sheet.Row(i).Height = 30;
sheet.Cells[7, 1, row - 1, 35].AutoFilter = true;
for (i = 1; i <= 7; i++)
sheet.Cells[6, i, 7, i].Merge = true;
sheet.Cells[8, 4, row - 1, 4].Style.WrapText = true;
sheet.Cells[8, 6, row - 1, 6].Style.WrapText = true;
sheet.Cells[8, 30, row - 1, 30].Style.WrapText = true;
sheet.Cells[8, 31, row - 1, 31].Style.WrapText = true;
sheet.Cells[8, 34, row - 1, 34].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");
}
else
return Content("找不到任何紀錄。");
}
