コード例 #1
0
 public ActionResult Report21(Report21Model model)
 {
     return new ReportsGenController().Report21(model);
 }
コード例 #2
0
        public ActionResult Report21(Report21Model model)
        {
            if (model.district_id == null)
            {
                return Content("找不到任何紀錄。");
            }
            else
            {
                string districtSql = "SELECT * FROM recsys_district as d WHERE d.status = '1' AND d.id =" + model.district_id.ToString();
                IEnumerable<recsys_district> districts = Common.queryToObject<recsys_district>(this._db.ExecuteStoreQuery<recsys_district>(districtSql));
                if (districts.Count() > 0)
                {
                    ViewBag.district = districts.ElementAt(0).name;
                }
                else
                {
                    return Content("找不到任何紀錄。");
                }
            }

            string sql = @"
                SELECT
                    m.id, c.customer_code, c.address2, c.name as customer_name, c.tel1, r.repair_date_frequency_in_month as routine, m.start_date
                FROM recsys_maintenance as m
                LEFT JOIN recsys_relate_customers as c on m.customer_id = c.id
                LEFT JOIN recsys_routine as r on m.master_routine_id = r.id
                WHERE m.status = '1' AND c.district2 = " + model.district_id.ToString() + " AND m.confirm_date is not null";

            //Filtering
            if (model.show_printed.HasValue && model.show_printed.Value == false) sql = sql + @" AND m.printed = '0'";
            if (!string.IsNullOrEmpty(model.first_print_date) && model.show_printed.Value == true)
            {
                sql = sql + string.Format(" AND m.first_print_Date >= '{0}' AND m.first_print_Date < DATEADD(day, 1, '{0}')",
                                ConvertDateStringFormat(model.first_print_date, DATEPICKER_DATE_FORMAT, DB_DATE_FORMAT)
                             );
            }
            sql = sql + " ORDER BY m.start_date ASC";

            IEnumerable<report21> maintenances = Common.queryToObject<report21>(this._db.ExecuteStoreQuery<report21>(sql));
            if (maintenances.Count() > 0)
            {

                foreach (report21 maintenance in maintenances)
                {
                    sql = Common.doSql("UPDATE recsys_maintenance SET printed = '1', first_print_Date = getDate() WHERE id = '" + maintenance.id + "' AND printed = '0'");
                    this._db.ExecuteStoreCommand(sql);
                }

                this._db.SaveChanges();

                return View("~/Views/ReportsGen/Report21.cshtml", maintenances);
            }
            else
            {
                return Content("找不到任何紀錄。");
            }
        }