public ActionResult Filter(int id) { object viewModel = null; ViewBag.title = this._report[id]; switch (id) { case 5: ViewBag.type = new SortedList() { { 1, "盈電工程有限公司" }, { 2, "盈電機電工程有限公司" } }; break; case 7: ViewBag.type = new SortedList() { { 1, "All Billed ACQ" }, { 2, "Billed ACQ with Dummy Invoice Only" }, { 3, "Billed ACQ with invoice Number" } }; break; case 8: ViewBag.dummy = new SortedList() { { 1, "All" }, { 2, "With Dummy Invoice" }, { 3, "Without Dummy Invoice" } }; break; case 9: viewModel = new Report9Model() { types = new Report9ModelType[] { Report9ModelType.Maintenance, Report9ModelType.Quotation } }; break; case 11: ViewBag.region = this.getRegion(); viewModel = new Report11Model() { order_date = DateTime.Today.ToString("dd-MM-yyyy"), //order_date_required = true }; break; case 12: ViewBag.engineer = this.getEngineer(); break; case 13: ViewBag.type = new SortedList() { { 1, "Invoice" }, { 2, "Quotation" } }; ViewBag.type2 = new SortedList() { { 1, "for Maintenance Contract (ACM)" }, { 2, "for Quotation (ACQ)" } }; break; case 14: ViewBag.type = new SortedList() { { 1, "All" }, { 2, "T&C Only" }, { 3, "Not T&C Only" }, { 4, "Without Service Date" } }; break; case 15: ViewBag.region = this.getRegion(); break; case 16: //ViewBag.type = _bank; break; case 17: ViewBag.region = this.getRegion(); break; case 19: Report19Model repot19model = new Report19Model(); repot19model.Modes = new Report19ModelMode[] { Report19ModelMode.By_Category, Report19ModelMode.By_Employee, Report19ModelMode.By_Sub__Contractor, Report19ModelMode.By_Supplier, Report19ModelMode.By_Minor_Work }; repot19model.CostCategories = this.getCosts(); repot19model.Suppliers = this.getSuppliers(); viewModel = repot19model; break; case 20: ViewBag.order_status = new SortedList() { { 1, "預約保養" }, { 2, "做保養" }, { 3, "完成保養" } }; break; case 21: //ViewBag.district = this.getDistrict(); var district = (from d in this._db.recsys_district where d.status == 1 && (d.name == "帝琴灣" || d.name == "爵悅庭") orderby d.region ascending select d); ViewBag.district = Common.array_conv(Common.query(district), "id", "name"); break; case 22: Report22Model report22Model = new Report22Model(); report22Model.show_printed = false; viewModel = report22Model; break; } return View("Report" + id, viewModel); }
public ActionResult Report9(Report9Model model) { //return Pdf("/reportsgen/report9/", this._report[9].ToString()); return new ReportsGenController().Report9(model); }
public ActionResult Report9(Report9Model model) { MaintenanceQuotationSummaryReportModel reportModel = new MaintenanceQuotationSummaryReportModel() { Type = model.type }; string start_date = model.start_date; string end_date = model.end_date; string customer_code = model.customer_code; string customer_name = model.customer_name; Boolean by_month = model.by_month; ArrayList where = new ArrayList(); IEnumerable<report9> result; String table = model.type == Report9ModelType.Maintenance ? "maintenance" : "quotation"; string issueDateDefinition = model.type == Report9ModelType.Maintenance ? string.Format(" isnull( isnull({0}, {1}), {2} ) ", table + ".issue_date", table + ".confirm_date", table + ".start_date") : string.Format(" isnull({0}, {1}) ", table + ".issue_date", table + ".confirm_date"); string amountSelectionSql = ""; if (!String.IsNullOrWhiteSpace(start_date)) { where.Add(issueDateDefinition + " >= '" + ConvertDateStringFormat(start_date, DATEPICKER_DATE_FORMAT, DB_DATE_FORMAT) + "'"); } if (!String.IsNullOrWhiteSpace(end_date)) { where.Add(issueDateDefinition + " < DATEADD(day, 1, '" + ConvertDateStringFormat(end_date, DATEPICKER_DATE_FORMAT, DB_DATE_FORMAT) + "')"); } if (model.MasterCustomerID.HasValue) where.Add("c.customer_id = " + model.MasterCustomerID.Value); if (model.type == Report9ModelType.Maintenance) //# maintenance { amountSelectionSql = @" maintenance.amount, "; } else if (model.type == Report9ModelType.Quotation) { amountSelectionSql = @" ( SELECT COALESCE(SUM(price), 0) FROM recsys_quotation_items AS qi LEFT JOIN recsys_relate AS r ON r.id2 = qi.id WHERE r.table1 = 'quotation' AND r.table2 = 'quotation_items' AND r.id1 = quotation.id) AS amount, "; } //string sql = Common.doSql("SELECT " + table + ".id, " + table + ".invoice, " + table + ".create_date, (SELECT COALESCE(SUM(price1), 0) FROM {p}_costs AS cs LEFT JOIN {p}_relate AS r ON r.id1 = cs.id WHERE r.table1 = 'costs' AND r.table2 = '" + table + "' AND r.id2 = " + table + ".id AND cs.status = '1') AS amount, CONVERT(NVARCHAR(7), " + table + ".create_date, 120) AS date, c.customer_code, c.name AS customer_name FROM {p}_" + table + " AS " + table + " LEFT JOIN {p}_relate_customers AS c ON " + table + ".customer_id = c.id WHERE " + table + ".status = '1'" + (where.Count > 0 ? " AND " + String.Join(" AND ", where.ToArray()) : String.Empty) + " ORDER BY " + (by_month ? table + ".create_date ASC, c.customer_id ASC" : "c.customer_id ASC, " + table + ".invoice_date ASC")); string sql = ""; if (table == "maintenance") { sql = @" SELECT {table}.id, {table}.invoice, " + issueDateDefinition + @" as issue_date, maintenance.start_date as contract_begin_date, maintenance.end_date as contract_end_date, {amountSelectionInjection} CONVERT(NVARCHAR(7), " + issueDateDefinition + @", 120) AS date, c.type, c.code, c.prefix, c.manual_input_code, c.name AS customer_name, c.customer_id as master_customer_id, c.customer_code as CustomerNumber, c.address2 as working_location FROM {p}_{table} AS {table} LEFT JOIN {p}_relate_customers AS c ON {table}.customer_id = c.id WHERE {table}.status = '1'" + (where.Count > 0 ? " AND " + String.Join(" AND ", where.ToArray()) : String.Empty) + @" ORDER BY c.customer_code, " + (by_month ? "CONVERT(NVARCHAR(7), " + issueDateDefinition + @", 120) , " : string.Empty) + @" maintenance.start_date ASC "; } else { sql = @" SELECT {table}.id, {table}.invoice, " + issueDateDefinition + @" as issue_date, {amountSelectionInjection} CONVERT(NVARCHAR(7), " + issueDateDefinition + @", 120) AS date, c.type, c.code, c.prefix, c.manual_input_code, c.name AS customer_name, c.customer_id as master_customer_id, c.customer_code as CustomerNumber, c.address2 as working_location FROM {p}_{table} AS {table} LEFT JOIN {p}_relate_customers AS c ON {table}.customer_id = c.id WHERE {table}.status = '1'" + (where.Count > 0 ? " AND " + String.Join(" AND ", where.ToArray()) : String.Empty) + @" ORDER BY " + (by_month ? "CONVERT(NVARCHAR(7), " + issueDateDefinition + @", 120) , " : string.Empty) + @" c.customer_code, "+ issueDateDefinition + @" ASC "; } sql = sql.Replace("{amountSelectionInjection}", amountSelectionSql); sql = sql.Replace("{table}", table); sql = Common.doSql(sql); result = Common.queryToObject<report9>(this._db.ExecuteStoreQuery<report9>(sql)); if (result.Count() > 0) { //ViewBag.result = result; reportModel.Records = result; //ViewBag.start_date = minDate.HasValue ? minDate.Value.ToString("d/M/yyyy") : string.Empty; //reportModel.StartDate = result.Select(theRecord => theRecord.issue_date).Min(); if (!String.IsNullOrWhiteSpace(start_date)) reportModel.StartDate = DateTime.ParseExact(start_date, DATEPICKER_DATE_FORMAT, null); else reportModel.StartDate = null; //ViewBag.end_date = maxDate.HasValue ? maxDate.Value.ToString("d/M/yyyy") : string.Empty; //reportModel.EndDate = result.Select(theRecord => theRecord.issue_date).Max(); if (!String.IsNullOrWhiteSpace(end_date)) reportModel.EndDate = DateTime.ParseExact(end_date, DATEPICKER_DATE_FORMAT, null); else reportModel.EndDate = null; //ViewBag.title = CultureInfo.CurrentUICulture.TextInfo.ToTitleCase(table) + " Summary by " + (by_month ? "Month" : "Customer"); reportModel.Title = CultureInfo.CurrentUICulture.TextInfo.ToTitleCase(table) + " Summary by " + (by_month ? "Month" : "Customer"); //ViewBag.subtitle = CultureInfo.CurrentUICulture.TextInfo.ToTitleCase(table); if (by_month) { reportModel.ByMonthRecords = new List<KeyValuePair<DateTime, List<report9>>>(); foreach (report9 item in result) { if (item.date == null) continue; string dateString = item.date; DateTime date; if (DateTime.TryParse(dateString, out date)) { KeyValuePair<DateTime, List<report9>> listOfMonth; if (!reportModel.ByMonthRecords.Any(kvp => kvp.Key == date)) { listOfMonth = new KeyValuePair<DateTime, List<report9>>(date, new List<report9>()); reportModel.ByMonthRecords.Add(listOfMonth); } else { listOfMonth = reportModel.ByMonthRecords.Where(kvp => kvp.Key == date).FirstOrDefault(); } listOfMonth.Value.Add(item); } } return View("~/Views/ReportsGen/Report9_by_month.cshtml", reportModel); } else { return View("~/Views/ReportsGen/Report9.cshtml", reportModel); } } else { return Content("找不到任何紀錄。"); } }