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