コード例 #1
0
 public ActionResult Report7(Report7Model model)
 {
     //return Pdf("/reportsgen/report7/", this._report[7].ToString(), false);
     return new ReportsGenController().Report7(model);
 }
コード例 #2
0
        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("找不到任何紀錄。");
            }
        }