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