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