public ActionResult Report2(Report2Model model) { //return Pdf("/reportsgen/report2/", this._report[2].ToString(), false); return new ReportsGenController().Report2(model); }
public ActionResult Report2(Report2Model model) { //string quotation_id = model.quotation_id; //string customer_code = model.customer_code; //string customer_name = model.customer_name; string start_date = model.start_date; string end_date = model.end_date; ArrayList where = new ArrayList(); //if (! String.IsNullOrWhiteSpace(quotation_id)) //{ // where.Add("q.id = '" + quotation_id + "'"); //} //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.reference, (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.subcon_estimation, (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 = 'quotation' AND r.id2 = q.id AND cs.supplier_id != '0' AND cs.status = '1') AS subcon_actual, q.supervision, (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 = 'quotation' AND r.id2 = q.id AND cs.user_id != '0' AND cs.status = '1') AS material_estimation, (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 = 'quotation' AND r.id2 = q.id AND cs.user_id != '0' AND cs.status = '1') AS material_actual, c.name AS customer_name FROM {p}_quotation AS q LEFT JOIN {p}_relate_customers AS c ON q.customer_id = c.id WHERE q.status = '1'" + (where.Count > 0 ? " AND " + String.Join(" AND ", where.ToArray()) : String.Empty) + " ORDER BY q.id DESC"); //string sql = Common.doSql("SELECT q.reference, (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.subcon_estimation, (SELECT COALESCE(SUM(price1), 0) FROM {p}_costs AS cs LEFT JOIN {p}_relate AS r ON r.id1 = cs.id LEFT JOIN {p}_users AS u ON cs.user_id = u.id WHERE r.table1 = 'costs' AND r.table2 = 'quotation' AND r.id2 = q.id AND cs.user_id != '0' AND u.[group] = '0' AND cs.status = '1') AS subcon_actual, q.supervision, q.supplier_id AS material_estimation, (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 = 'quotation' AND r.id2 = q.id AND cs.supplier_id != '0' AND cs.status = '1') AS material_actual, c.name AS customer_name FROM {p}_quotation AS q LEFT JOIN {p}_relate_customers AS c ON q.customer_id = c.id WHERE q.status = '1'" + (where.Count > 0 ? " AND " + String.Join(" AND ", where.ToArray()) : String.Empty) + " ORDER BY q.id DESC"); if (String.IsNullOrWhiteSpace(start_date)) start_date = "01-01-2013" ; if (!String.IsNullOrWhiteSpace(start_date)) where.Add("q.confirm_date >= '" + ConvertDateStringFormat(start_date, DATEPICKER_DATE_FORMAT, DB_DATE_FORMAT) + "'"); if (!String.IsNullOrWhiteSpace(end_date)) where.Add("q.confirm_date < DATEADD(day, 1, '" + ConvertDateStringFormat(end_date, DATEPICKER_DATE_FORMAT, DB_DATE_FORMAT) + "')"); string sql = @" SELECT q.number, ( SELECT COALESCE(SUM(price), 0) FROM recsys_quotation_items AS i LEFT JOIN recsys_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, COALESCE(q.subcon_estimation,0) + COALESCE(q.subcon_estimation2,0) + COALESCE(q.subcon_estimation3,0) as subcon_estimation, ( SELECT COALESCE(SUM(price1), 0) FROM recsys_costs AS cs LEFT JOIN recsys_relate AS r ON r.id1 = cs.id left join recsys_cost as cc on cs.cost_id = cc.id WHERE r.table1 = 'costs' AND r.table2 = 'quotation' AND cc.IsSubCon = 1 AND r.id2 = q.id AND cs.status = '1' AND cc.status = '1' ) AS subcon_actual, q.supervision, COALESCE(q.supplier_id, 0) + COALESCE(q.material_estimation2, 0) + COALESCE(q.material_estimation3, 0) AS material_estimation, ( SELECT COALESCE(SUM(price1), 0) FROM recsys_costs AS cs LEFT JOIN recsys_relate AS r ON r.id1 = cs.id left join recsys_cost as cc on cs.cost_id = cc.id WHERE r.table1 = 'costs' AND r.table2 = 'quotation' AND r.id2 = q.id AND isnull(cc.IsSubCon, 0) <> 1 AND isnull(cc.IsMinorWork, 0) <> 1 AND cs.status = '1' AND cc.status = '1' ) AS material_actual, q.minor_work_currency AS minor_work_estimation, ( SELECT COALESCE(SUM(price1), 0) FROM recsys_costs AS cs LEFT JOIN recsys_relate AS r ON r.id1 = cs.id left join recsys_cost as cc on cs.cost_id = cc.id WHERE r.table1 = 'costs' AND r.table2 = 'quotation' AND r.id2 = q.id AND cc.IsMinorWork = 1 AND cs.status = '1' AND cc.status = '1' ) AS minor_work_actual, c.name AS customer_name FROM recsys_quotation AS q LEFT JOIN recsys_relate_customers AS c ON q.customer_id = c.id WHERE q.status = '1' and ( COALESCE(subcon_estimation, 0) + COALESCE(subcon_estimation2, 0) + COALESCE(subcon_estimation3, 0) < ( SELECT COALESCE(SUM(price1), 0) FROM recsys_costs AS cs LEFT JOIN recsys_relate AS r ON r.id1 = cs.id left join recsys_cost as cc on cs.cost_id = cc.id WHERE r.table1 = 'costs' AND r.table2 = 'quotation' AND cc.IsSubCon = 1 AND r.id2 = q.id AND cs.status = '1' AND cc.status = '1' ) or COALESCE(supplier_id, 0) + COALESCE(material_estimation2, 0) + COALESCE(material_estimation3, 0) < ( SELECT COALESCE(SUM(price1), 0) FROM recsys_costs AS cs LEFT JOIN recsys_relate AS r ON r.id1 = cs.id left join recsys_cost as cc on cs.cost_id = cc.id WHERE r.table1 = 'costs' AND r.table2 = 'quotation' AND r.id2 = q.id AND isnull(cc.IsSubCon, 0) <> 1 AND isnull(cc.IsMinorWork, 0) <> 1 AND cs.status = '1' AND cc.status = '1' ) or q.minor_work_currency < ( SELECT COALESCE(SUM(price1), 0) FROM recsys_costs AS cs LEFT JOIN recsys_relate AS r ON r.id1 = cs.id left join recsys_cost as cc on cs.cost_id = cc.id WHERE r.table1 = 'costs' AND r.table2 = 'quotation' AND r.id2 = q.id AND cc.IsMinorWork = 1 AND cs.status = '1' AND cc.status = '1' ) ) " + (where.Count > 0 ? " AND " + String.Join(" AND ", where.ToArray()) : String.Empty) + " ORDER BY q.id DESC"; sql = Common.doSql(sql); ArrayList quotation = Common.query(this._db.ExecuteStoreQuery<report2>(sql)); if (quotation.Count > 0) { ViewBag.quotation = quotation; return View("~/Views/ReportsGen/Report2.cshtml"); } else { return Content("找不到任何紀錄。"); } }