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