public ActionResult Report19_ByUser(DateTime? startDate, DateTime? endDate, DateTime? lastUpdateDate, ref ExpenseReportModel model) { bool hasRecord = false; SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["reportConnection"].ConnectionString); string subContractor_userGroup_name = UserGroup.SubCon; int subContractor_id = int.Parse((from o in this._db.recsys_option where o.type_code == "UG" && o.active == true && o.name == subContractor_userGroup_name select o.value).FirstOrDefault()); using (connection) { SqlCommand command = new SqlCommand( string.Format( @" SELECT cs.user_id, cs.id, u.name, u.employee_number, cc.code, cs.price1 FROM [recsys_costs] cs join [recsys_users] u on cs.user_id = u.id join [recsys_type] t on u.type = t.id left outer join [recsys_cost] cc on cs.cost_id = cc.id where {0} {1} {2} {3} {4} order by u.employee_number, cs.account_date ", "u.user_group != " + subContractor_id, startDate.HasValue ? string.Format("and account_date >= '{0}'", startDate.Value.ToString(DB_DATE_FORMAT)) : string.Empty, endDate.HasValue ? string.Format("and account_date < DATEADD(day, 1,'{0}')", endDate.Value.ToString(DB_DATE_FORMAT)) : string.Empty, "and cs.status = 0", lastUpdateDate.HasValue ? string.Format("and cs.last_update >= '{0}' and cs.last_update < DATEADD(day,1,'{0}')", lastUpdateDate.Value.ToString(DB_DATE_FORMAT), ")") : string.Empty ), connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); hasRecord = reader.HasRows; if (!hasRecord) { //# log down } else { model.ByUserRows = new List<ExpenseReportByUserRow>(); while (reader.Read()) { ExpenseReportByUserRow newRow = model.ByUserRows.FirstOrDefault(theRow => theRow.EmployeeID == reader.GetInt32(0)); if (newRow == null) //# if the row does not exists, add the new row and map details to it { newRow = new ExpenseReportByUserRow(); if (!reader.IsDBNull(0)) { newRow.EmployeeID = reader.GetInt32(0); } if (!reader.IsDBNull(2)) { newRow.EmployeeName = reader.GetString(2); } if (!reader.IsDBNull(3)) newRow.EmployeeNumber = reader.GetString(3); model.ByUserRows.Add(newRow); } //# add sub rows newRow.SubRows.Add(new ExpenseReportByUserSubRow() { CategoryName = reader.IsDBNull(4) ? string.Empty : reader.GetString(4), Price = reader.IsDBNull(5) ? 0 : reader.GetDouble(5) }); } reader.Close(); } } if (hasRecord) return View("~/Views/ReportsGen/Report19_ByUser.cshtml", model); else return Content("找不到任何紀錄。"); }
public ActionResult Report19_BySupplier(DateTime? startDate, DateTime? endDate, IEnumerable<int> supplierIDs, DateTime? lastUpdateDate, ref ExpenseReportModel model) { //# validation if (supplierIDs.Count() <= 0) throw new SystemException("invalid Suppliers parameter values"); bool hasRecord = false; SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["reportConnection"].ConnectionString); string idsSqlClause = string.Empty; foreach (int id in supplierIDs) idsSqlClause += (string.IsNullOrEmpty(idsSqlClause) ? string.Empty : ",") + id; using (connection) { SqlCommand command = new SqlCommand( string.Format( @" select id, name from recsys_supplier where id in ({0}) order by name ", idsSqlClause ), connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); hasRecord = reader.HasRows; if (!hasRecord) { //# log down } else { model.BySupplierRows = new List<ExpenseReportBySupplierRow>(); while (reader.Read()) { ExpenseReportBySupplierRow newRow = new ExpenseReportBySupplierRow(); if (!reader.IsDBNull(0)) newRow.SupplierID = reader.GetInt32(0); if (!reader.IsDBNull(1)) newRow.SupplierName = reader.GetString(1); model.BySupplierRows.Add(newRow); } reader.Close(); //# get sub-rows command = new SqlCommand( string.Format( @" select s.id, s.name, c.code, cc.price1 from recsys_costs cc join recsys_supplier s on cc.supplier_id = s.id left outer join recsys_cost c on cc.cost_id = c.id where s.id in ({0}) {1} {2} {3} order by cc.account_date ", idsSqlClause, startDate.HasValue ? string.Format("and account_date >= '{0}'", startDate.Value.ToString(DB_DATE_FORMAT)) : string.Empty, endDate.HasValue ? string.Format("and account_date < DATEADD(day, 1, '{0}')", endDate.Value.ToString(DB_DATE_FORMAT)) : string.Empty, lastUpdateDate.HasValue ? string.Format("and cc.last_update >= '{0}' and cc.last_update < DATEADD(day,1,'{0}')", lastUpdateDate.Value.ToString(DB_DATE_FORMAT), ")") : string.Empty ), connection); reader = command.ExecuteReader(); while (reader.Read()) { int supplierID = reader.GetInt32(0); ExpenseReportBySupplierRow newRow = model.BySupplierRows.FirstOrDefault(theRow => theRow.SupplierID == supplierID); newRow.SubRows.Add(new ExpenseReportBySupplierSubRow() { CategoryName = reader.IsDBNull(2) ? string.Empty : reader.GetString(2), Price = reader.IsDBNull(3) ? 0 : reader.GetDouble(3) }); } reader.Close(); } } if (hasRecord) return View("~/Views/ReportsGen/Report19_BySupplier.cshtml", model); else return Content("找不到任何紀錄。"); }
public ActionResult Report19_ByCategory(DateTime? startDate, DateTime? endDate, IEnumerable<int> costCategoryIDs, DateTime? lastUpdateDate, ref ExpenseReportModel model) { //# validation if (costCategoryIDs.Count() <= 0) throw new SystemException("invalid Cost Categories parameter values"); bool hasRecord = false; SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["reportConnection"].ConnectionString); string idsSqlClause = string.Empty; foreach (int id in costCategoryIDs) idsSqlClause += (string.IsNullOrEmpty(idsSqlClause) ? string.Empty : ",") + id; //string dateClause = string.Empty; //if (startDate.HasValue && endDate.HasValue) //{ // dateClause = string.Format(" and account_date between {0} and {1} ", startDate.Value.ToString(DB_DATE_FORMAT), endDate.Value.ToString(DB_DATE_FORMAT)); //} //else //{ // if (startDate.HasValue) // { // dateClause += string.Format("and account_date >= '{0}'", startDate.Value.ToString(DB_DATE_FORMAT)); // } // if (endDate.HasValue) // { // dateClause += string.Format("and account_date <= '{0}'", endDate.Value.ToString(DB_DATE_FORMAT)); // } //} using (connection) { SqlCommand command = new SqlCommand( string.Format( @" select id, code, name from recsys_cost where id in ({0}) order by code ", idsSqlClause ), connection); connection.Open(); SqlDataReader reader = command.ExecuteReader(); hasRecord = reader.HasRows; if (!hasRecord) { //# log down } else { model.ByCategoriesRows = new List<ExpenseReportByCategoryRow>(); while (reader.Read()) { ExpenseReportByCategoryRow newRow = new ExpenseReportByCategoryRow(); if (!reader.IsDBNull(0)) newRow.CategoryID = reader.GetInt32(0); if (!reader.IsDBNull(1)) newRow.CategoryCode = reader.GetString(1); if (!reader.IsDBNull(2)) newRow.CategoryName = reader.GetString(2); model.ByCategoriesRows.Add(newRow); } reader.Close(); //# get sub-rows command = new SqlCommand( string.Format( @" SELECT co.cost_id as costCategoryID, co.center, c.customer_code, co.price1, q.number, m.start_date, m.end_date, r.table2 FROM [recsys_costs] co left join [recsys_relate] r on co.id = r.id1 left join [recsys_maintenance] m on m.id = r.id2 and r.table2 = 'maintenance' left join [recsys_quotation] q on q.id = r.id2 and r.table2 = 'quotation' left join [recsys_relate_customers] c on c.id = isnull(m.customer_id, q.customer_id) where r.table1 = 'costs' and co.cost_id is not null and co.cost_id in ({0}) {1} {2} {3} order by account_date ", idsSqlClause, startDate.HasValue ? string.Format("and account_date >= '{0}'", startDate.Value.ToString(DB_DATE_FORMAT)) : string.Empty, endDate.HasValue ? string.Format("and account_date < DATEADD(day, 1, '{0}')", endDate.Value.ToString(DB_DATE_FORMAT)) : string.Empty, lastUpdateDate.HasValue ? string.Format("and co.last_update >= '{0}' and co.last_update < DATEADD(day,1,'{0}')", lastUpdateDate.Value.ToString(DB_DATE_FORMAT), ")") : string.Empty ), connection); reader = command.ExecuteReader(); while (reader.Read()) { int costCategoryID = reader.GetInt32(0); ExpenseReportByCategoryRow newRow = model.ByCategoriesRows.FirstOrDefault(theRow => theRow.CategoryID == costCategoryID); newRow.SubRows.Add(new ExpenseReportByCategorySubRow() { Center = reader.IsDBNull(1) ? null : new Center() { name = reader.GetString(1) }, CustomerCode = reader.GetString(7)=="maintenance" ? reader.IsDBNull(2) ? string.Empty : reader.IsDBNull(5) ? reader.GetString(2) : reader.IsDBNull(6) ? reader.GetString(2) + " ( " + reader.GetDateTime(5).ToString("dd/MM/yyyy") + " - )" : reader.GetString(2) + " ( " + reader.GetDateTime(5).ToString("dd/MM/yyyy") + " - " + reader.GetDateTime(6).ToString("dd/MM/yyyy") + " )" : reader.GetString(7)=="quotation" ? reader.IsDBNull(4) ? "ACQ No.: " : "ACQ No.: " + reader.GetString(4) : string.Empty, Price = reader.IsDBNull(3) ? 0 : reader.GetDouble(3) }); } reader.Close(); } } if (hasRecord) return View("~/Views/ReportsGen/Report19_ByCategory.cshtml", model); else return Content("找不到任何紀錄。"); }
public ActionResult Report19_BySubCon(DateTime? startDate, DateTime? endDate, DateTime? lastUpdateDate, ref ExpenseReportModel model) { string sql = string.Format( @" SELECT s.name as subConName, u.name as userName, cc.code + ' ' + cc.name as category, cs.invoice, cs.price1 as price, cs.account_date, cs.remark, c.customer_code as customerCode, q.number as number, m.start_date as startDate, m.end_date as endDate, r.table2 as tableName FROM [recsys_costs] cs left join [recsys_users] u on cs.user_id = u.id left join [recsys_supplier] s on cs.supplier_id = s.id left outer join [recsys_cost] cc on cs.cost_id = cc.id left join [recsys_relate] r on cs.id = r.id1 left join [recsys_maintenance] m on m.id = r.id2 and r.table2 = 'maintenance' left join [recsys_quotation] q on q.id = r.id2 and r.table2 = 'quotation' left join [recsys_relate_customers] c on c.id = isnull(m.customer_id, q.customer_id) where cc.status = 1 and cc.IsSubCon = 1 and r.table1 = 'costs' {0} {1} {2} order by cc.code, cs.account_date, u.name, s.name", startDate.HasValue ? string.Format("and account_date >= '{0}'", startDate.Value.ToString(DB_DATE_FORMAT)) : string.Empty, endDate.HasValue ? string.Format("and account_date < DATEADD(day, 1,'{0}')", endDate.Value.ToString(DB_DATE_FORMAT)) : string.Empty, lastUpdateDate.HasValue ? string.Format("and cs.last_update >= '{0}' and cs.last_update < DATEADD(day,1,'{0}')", lastUpdateDate.Value.ToString(DB_DATE_FORMAT), ")") : string.Empty ); IEnumerable<report19_bySubCon> expenses = Common.queryToObject<report19_bySubCon>(this._db.ExecuteStoreQuery<report19_bySubCon>(sql)); //Maintenance/ACQ No. Column foreach (report19_bySubCon expense in expenses) { expense.maintenanceACQ = expense.tableName == "maintenance" ? string.IsNullOrEmpty(expense.customerCode) ? string.Empty : expense.startDate.HasValue ? expense.endDate.HasValue ? expense.customerCode + " ( " + expense.startDate.Value.ToString("dd/MM/yyyy") + " - " + expense.endDate.Value.ToString("dd/MM/yyyy") + " )" : expense.customerCode + " ( " + expense.startDate.Value.ToString("dd/MM/yyyy") + " - )" : expense.customerCode : expense.tableName == "quotation" ? string.IsNullOrEmpty(expense.number) ? "ACQ No.: " : "ACQ No.: " + expense.number : string.Empty; } if (expenses.Count()>0) { /*-----------Excel-----------*/ string title = "Expenses by Sub-Contractor"; string[] header = new string[] { "Category", "User名稱", "供應商", "Maintenance/ACQ No.", "Invoice", "價錢", "入帳日期", "備註" }; ExcelPackage excel = new ExcelPackage(); ExcelWorksheet sheet = excel.Workbook.Worksheets.Add(title); sheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left; int row = 1, col = 1; #region header sheet.Cells[1, 1].Value = title; sheet.Cells[2, 8].Value = "Date: " + DateTime.Now.Date.ToString("dd/MM/yyyy"); row += 3; foreach (string colName in header) { sheet.Cells[row, col++].Value = colName; } sheet.Cells[4, 1, row, col].Style.Font.Bold = true; #endregion ++row; string lastCategory = ""; int repeatedCategoryNum = 0; foreach (report19_bySubCon expense in expenses) { col = 1; #region merge record for each subcontractor if (expense.category == lastCategory) repeatedCategoryNum++; else { if(repeatedCategoryNum>0) sheet.Cells[row - 1 - repeatedCategoryNum, 1, row - 1, 1].Merge = true; repeatedCategoryNum = 0; } #endregion #region data sheet.Cells[row, col++].Value = expense.category; sheet.Cells[row, col++].Value = expense.userName; sheet.Cells[row, col++].Value = expense.subConName; sheet.Cells[row, col++].Value = expense.maintenanceACQ; sheet.Cells[row, col++].Value = expense.invoice; sheet.Cells[row, col++].Value = expense.price; sheet.Cells[row, col++].Value = string.Format("{0:dd/MM/yyyy}", expense.account_date); sheet.Cells[row, col++].Value = expense.remark; #endregion lastCategory = expense.category; row++; } #region merge expenses for the lastest sub-contractor if (repeatedCategoryNum > 0) { sheet.Cells[row - 1 - repeatedCategoryNum, 1, row - 1, 1].Merge = true; } #endregion #region set date and number column format sheet.Column(7).Style.Numberformat.Format = @"dd/MM/yyyy"; sheet.Column(6).Style.Numberformat.Format = @"0.00"; #endregion #region set autofit, alignment and wraptext for (int i = 1; i < col; i++) { sheet.Column(i).AutoFit(0, 30); sheet.Column(i).Style.VerticalAlignment = ExcelVerticalAlignment.Top; } sheet.Column(4).AutoFit(0, 30); sheet.Cells[5, 1, row - 1, 5].Style.WrapText = true; sheet.Cells[5, 8, row - 1, 8].Style.WrapText = true; #endregion sheet.Cells[1, 1, 1, 8].Merge = true; sheet.Cells[1, 1].Style.Font.Bold = true; sheet.Cells[1, 1].Style.Font.Size = 20; for (int i = 1; i < col ; i++) for (int j = 4; j < row; j++) sheet.Cells[j, i].Style.Border.BorderAround(ExcelBorderStyle.Thin); 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("找不到任何紀錄。"); }
public ActionResult Report19(Report19Model model) { ExpenseReportModel reportModel = new ExpenseReportModel() { CompanyName1 = "REC ENGINEERING COMPANY LIMITED", CompanyName2 = "REC ENGINEERING CONTRACTING COMPANY LIMITED", Mode = model.Mode, PrintingDate = DateTime.Now }; DateTime? start; DateTime? end; if (!string.IsNullOrEmpty(model.start_date)) { start = DateTime.ParseExact(model.start_date, DATEPICKER_DATE_FORMAT, null); } else { start = null; } if (!string.IsNullOrEmpty(model.end_date)) { end = DateTime.ParseExact(model.end_date, DATEPICKER_DATE_FORMAT, null); } else { end = null; } DateTime? lastUpdate; if (!string.IsNullOrEmpty(model.lastUpdateDate)) { lastUpdate = DateTime.ParseExact(model.lastUpdateDate, DATEPICKER_DATE_FORMAT, null); } else { lastUpdate = null; } switch (model.Mode) { case Report19ModelMode.By_Category: if (model.SelectedCostCategories == null || model.SelectedCostCategories.Count() <= 0) return Content("請選擇Cost Category"); else return this.Report19_ByCategory(start, end, model.SelectedCostCategories, lastUpdate, ref reportModel); case Report19ModelMode.By_Employee: return this.Report19_ByUser(start, end, lastUpdate, ref reportModel); case Report19ModelMode.By_Sub__Contractor: return this.Report19_BySubCon(start, end, lastUpdate, ref reportModel); case Report19ModelMode.By_Supplier: if (model.SelectedSuppliers == null || model.SelectedSuppliers.Count() <= 0) return Content("請選擇Supplier"); else return this.Report19_BySupplier(start, end, model.SelectedSuppliers, lastUpdate, ref reportModel); case Report19ModelMode.By_Minor_Work: var minor_work_cats = (from c in this._db.recsys_cost where c.status == 1 && c.IsMinorWork == 1 select c.id); int[] minor_work_cats_array = minor_work_cats.ToArray(); //minor-work-category-id if (minor_work_cats_array.Length == 0) return Content("沒有設定Minor Work Category"); return this.Report19_ByCategory(start, end, minor_work_cats_array, lastUpdate, ref reportModel); default: return Content("請選擇報告模式"); } }