public ActionResult Report7(Report7Model model) { //return Pdf("/reportsgen/report7/", this._report[7].ToString(), false); return new ReportsGenController().Report7(model); }
public ActionResult Report7(Report7Model model) { int type = model.type; string start_date = model.start_date; string end_date = model.end_date; string start_date_2 = model.start_date_2; string end_date_2 = model.end_date_2; string customer_code = model.customer_code; string customer_name = model.customer_name; ArrayList where = new ArrayList(); /* if (! String.IsNullOrWhiteSpace(start_date)) { where.Add("q.create_date >= '" + start_date + "'"); } if (! String.IsNullOrWhiteSpace(end_date)) { where.Add("q.create_date <= '" + end_date + "'"); } */ if (!String.IsNullOrWhiteSpace(start_date_2)) where.Add("q.confirm_date >= '" + ConvertDateStringFormat(start_date_2, DATEPICKER_DATE_FORMAT, DB_DATE_FORMAT) + "'"); if (!String.IsNullOrWhiteSpace(end_date_2)) where.Add("q.confirm_date < DATEADD(day, 1, '" + ConvertDateStringFormat(end_date_2, DATEPICKER_DATE_FORMAT, DB_DATE_FORMAT) + "')"); switch (type) { case 2: where.Add("q.dummy = '1'"); break; case 3: where.Add("q.invoice != ''"); break; } //record will not show if direct labour = 1 where.Add("q.direct_labour <> 'true'"); if (!String.IsNullOrWhiteSpace(customer_code)) { where.Add("c.customer_code LIKE '%" + customer_code + "%'"); } if (!String.IsNullOrWhiteSpace(customer_name)) { where.Add("c.name LIKE '%" + customer_name + "%'"); } string sql = Common.doSql(@" SELECT q.id, q.number, q.lang, q.dummy, q.invoice , ( SELECT COALESCE(SUM(price), 0) FROM {p}_quotation_items AS i LEFT JOIN {p}_relate AS r ON r.id2 = i.id WHERE r.table1 = 'quotation' AND r.table2 = 'quotation_items' AND r.id1 = q.id ) AS quotation_amount, q.confirm_date, q.initial, q.order_number, q.billing_date, q.invoice_date, COALESCE(q.subcon_estimation, 0)+COALESCE(q.subcon_estimation2, 0)+COALESCE(q.subcon_estimation3, 0) as subcon_estimation, COALESCE(q.supplier_id, 0)+COALESCE(q.material_estimation2, 0)+COALESCE(q.material_estimation3, 0) AS material_estimation, c.name AS customer_name, c.address1, c.address2 , c.customer_code FROM {p}_quotation AS q LEFT JOIN {p}_relate_customers AS c ON q.customer_id = c.id --S-Add-150430-LL LEFT JOIN ( SELECT r.id2 FROM recsys_relate r INNER JOIN recsys_costs c ON c.id = r.id1 AND r.table1 = 'costs' AND r.table2 = 'quotation' INNER JOIN recsys_cost c_type ON c_type.id = c.cost_id AND c_type.IsSubCon = 1 GROUP BY r.id2) r_c ON r_c.id2 = q.id --E-Add-150430-LL WHERE q.status = '1' --S-Add-150430-LL AND r_c.id2 is null --E-Add-150430-LL AND q.billing_date is not null " + (where.Count > 0 ? " AND " + String.Join(" AND ", where.ToArray()) : String.Empty) + @" ORDER BY q.billing_date ASC"); ArrayList quotation = Common.query(this._db.ExecuteStoreQuery<report7>(sql)); if (quotation.Count > 0) { ArrayList qid = new ArrayList(); Hashtable items = new Hashtable(); foreach (Hashtable item in quotation) { qid.Add(item["id"]); items.Add(item["id"], item); } if (qid.Count > 0) { sql = Common.doSql(@" SELECT r.id1, i.* FROM {p}_quotation_items AS i LEFT JOIN {p}_relate AS r ON r.id2 = i.id WHERE r.table1 = 'quotation' AND r.table2 = 'quotation_items' AND r.id1 IN ('" + String.Join("','", qid.ToArray()) + @"') ORDER BY r.id1 ASC, i.nSequence, i.id ASC"); ArrayList quotation_items = Common.query(this._db.ExecuteStoreQuery<report7_items>(sql)); if (quotation_items.Count > 0) { Hashtable tmp = new Hashtable(); foreach (Hashtable item in quotation_items) { if (!tmp.ContainsKey(item["id1"])) { tmp.Add(item["id1"], new ArrayList()); } ((ArrayList)tmp[item["id1"]]).Add(item); } foreach (Hashtable item in quotation) { item["items"] = tmp.ContainsKey(item["id"]) ? tmp[item["id"]] : new ArrayList(); } } } ViewBag.quotation = quotation; ViewBag.start_date = Common.to_date("dd/MM/yyyy", ((Hashtable)quotation[0])["billing_date"]); ViewBag.end_date = Common.to_date("dd/MM/yyyy", ((Hashtable)quotation[quotation.Count - 1])["billing_date"]); ViewBag.type = type; return View("~/Views/ReportsGen/Report7.cshtml"); } else { return Content("找不到任何紀錄。"); } }