Пример #1
0
 public ActionResult Report15(Report15Model model)
 {
     return new ReportsGenController().Report15(model);
 }
Пример #2
0
        public ActionResult Report15(Report15Model model)
        {
            string customer_code = model.customer_code;
            string customer_name = model.customer_name;
            Boolean show_confirmed_only = model.show_confirmed_only;
            ArrayList where = new ArrayList();
            if (model.choose_region.Trim().Length > 0)
            {

                string[] chosenRegionList = model.choose_region.Split(',');

                string regionIDFilter = string.Empty;
                string regionNullFilter = " d.region is null ";
                bool isRegionNullFilterChoose = false;
                foreach (var regionId in chosenRegionList)
                {
                    if (regionId.Equals("-1"))
                        isRegionNullFilterChoose = true;
                    else
                    {
                        regionIDFilter += regionId + ",";
                    }
                }

                if (isRegionNullFilterChoose)
                {
                    if (!string.IsNullOrEmpty(regionIDFilter))
                        where.Add("(d.region in ( " + regionIDFilter.TrimEnd(',') + " ) or " + regionNullFilter + " )");
                    else
                        where.Add(regionNullFilter);
                }

                else
                {
                    where.Add("d.region in ( " + regionIDFilter.TrimEnd(',') + " )");
                }
            }
            else
            {
                return Content("找不到任何紀錄。");
            }
            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 + "%'");
            }
            if (show_confirmed_only)
            {
                where.Add("m.billing_date IS NOT NULL");
            }
            string title = "Active Customer Maintenance Records List";

            string sql = Common.doSql(@"
                SELECT
                        m.id, m.start_date, m.end_date, m.confirm_date, m.model,
                       COALESCE(m.period, 0) as period, m.remark, c.name AS customer_name, COALESCE(d.region, 0) as region, d.name AS district,
                        c.address2, m.amount, m.checking_date, c.customer_code
                FROM {p}_maintenance AS m
                LEFT JOIN {p}_relate_customers AS c ON m.customer_id = c.id
                LEFT JOIN {p}_district AS d ON c.district2 = d.id
                WHERE m.status = '1'
                AND m.end_date >= '" + Common.to_date(DB_DATE_FORMAT, DateTime.Now.AddMonths(-1)) + "'" + (where.Count > 0 ? " AND " + String.Join(" AND ", where.ToArray()) : String.Empty) + " ORDER BY d.region ASC, d.name ASC, c.address2 ASC, m.start_date ASC");

            IEnumerable<report15> maintenance = Common.queryToObject<report15>(this._db.ExecuteStoreQuery<report15>(sql));

            if (maintenance.Count() > 0)
            {
                //# 20131126: Eddie: as cleaning service has become dynamics (maintenance -> additional_service : one to many), get service dates from another sql
                string maintenanceID_SQL_String = "";
                foreach (report15 item in maintenance)
                    maintenanceID_SQL_String += (string.IsNullOrEmpty(maintenanceID_SQL_String) ? string.Empty : ", ") + item.id;
                string clearningServiceSQL = string.Format(@"
                                    SELECT
                                        [maintenance_id], [service_date]
                                    FROM [recsys_additional_service]
                                    where [maintenance_id] in ({0})
                                    order by [maintenance_id]
                                ", maintenanceID_SQL_String);
                IEnumerable<report15AdditionalService> cleaningServices = Common.queryToObject<report15AdditionalService>(this._db.ExecuteStoreQuery<report15AdditionalService>(clearningServiceSQL));

                var region = (from r in this._db.recsys_region
                              orderby r.id ascending
                              select r);
                Hashtable _region = Common.array_conv(Common.query(region), "id", "name");
                _region.Add("0", "無地區");

                string[] header = new string[]
                {
                    String.Empty,
                    "ACM",
                    "客戶名稱",
                    "地區",
                    "地址",
                    "詳細地址",
                    "start date",
                    "complete date",
                    "cfm_date",
                    "Amount",
                    "機型及數量",
                    "routine",
                    "remark",
                    "cleaning date",
                    "fire damper checking date"
                };
                ExcelPackage excel = new ExcelPackage();
                ExcelWorksheet sheet = excel.Workbook.Worksheets.Add(title);
                sheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;
                sheet.Cells[1, 1].Value = title;
                sheet.Cells[1, 1, 1, 10].Merge = true;
                sheet.Cells[1, 1].Style.Font.Bold = true;
                sheet.Cells[1, 1].Style.Font.Size = 20;
                sheet.Cells[3, 2].Value = "complete date < today within 1 month";
                sheet.Cells[3, 2, 3, 5].Merge = true;
                int row = 5, col = 1, i = 1;
                string area = String.Empty;
                foreach (string colName in header)
                {
                    sheet.Cells[row, col++].Value = colName;
                }
                ++row;
                //sheet.View.FreezePanes(++row, col);
                foreach (report15 item in maintenance)
                {
                    //# insert cleaning services to contract
                    item.cleaningServices.AddRange(cleaningServices.Where(theService => theService.maintenance_id == item.id).OrderByDescending(theService => theService.service_date));

                    if (area != item.region.ToString())
                    {
                        area = item.region.ToString();
                        sheet.Cells[row, 2].Value = _region[item.region.ToString()];
                        sheet.Cells[row, 2].Style.Font.Bold = true;
                        sheet.Cells[row, 2].Style.Font.Size = 20;
                        row++;
                    }
                    col = 1;
                    sheet.Cells[row, col++].Value = i++;
                    sheet.Cells[row, col++].Value = trimZero(item.customer_code); ;
                    sheet.Cells[row, col++].Value = item.customer_name;
                    sheet.Cells[row, col++].Value = _region[item.region.ToString()];
                    sheet.Cells[row, col++].Value = item.district;
                    sheet.Cells[row, col++].Value = item.address2;
                    sheet.Cells[row, col++].Value = item.start_date;
                    sheet.Cells[row, col++].Value = item.end_date;
                    sheet.Cells[row, col++].Value = Common.to_date("yyyy-MM-dd", item.confirm_date) != "1900-01-01" ? item.confirm_date : null;
                    sheet.Cells[row, col++].Value = item.amount.HasValue?item.amount:0;
                    sheet.Cells[row, col++].Value = item.model;
                    sheet.Cells[row, col++].Value = item.period;
                    sheet.Cells[row, col++].Value = item.remark;
                    sheet.Cells[row, col].Style.WrapText = true;
                    sheet.Cells[row, col++].Value = item.GetCleaningServiceAsCellValue("dd-MMM-yy", ", \n");
                    sheet.Cells[row, col++].Value = item.checking_date;
                    sheet.Cells[row, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                    sheet.Cells[row, 10].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    sheet.Cells[row, 12].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    //sheet.Cells[row, 7, row, 10].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                    row++;
                }
                #region set date column format
                sheet.Column(7).Style.Numberformat.Format = @"dd-MMM-yy";
                sheet.Column(8).Style.Numberformat.Format = @"dd-MMM-yy";
                sheet.Column(9).Style.Numberformat.Format = @"dd-MMM-yy";
                sheet.Column(15).Style.Numberformat.Format = @"dd-MMM-yy";
                #endregion

                #region set number format
                sheet.Column(10).Style.Numberformat.Format = @"#,##0.00";
                #endregion

                for (i = 1; i < col; i++)
                {
                    //sheet.Column(i).AutoFit();
                    if (i == 6 || i == 11 || i == 13) sheet.Column(i).AutoFit(0, 30);   //address, model, remark
                    else sheet.Column(i).AutoFit(0, 20);
                    sheet.Column(i).Style.WrapText = true;
                    sheet.Column(i).Style.VerticalAlignment = ExcelVerticalAlignment.Top;
                }

                sheet.Cells[6, 1, row, col].Style.Border.Bottom.Style = ExcelBorderStyle.Thin;
                sheet.Row(1).Style.WrapText = false;
                sheet.PrinterSettings.LeftMargin = sheet.PrinterSettings.RightMargin = sheet.PrinterSettings.TopMargin = sheet.PrinterSettings.BottomMargin = 0.3M / 2.54M;
                sheet.PrinterSettings.RepeatRows = sheet.Cells["5:5"];
                sheet.PrinterSettings.Orientation = eOrientation.Landscape;
                sheet.HeaderFooter.OddHeader.RightAlignedText = string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
                sheet.HeaderFooter.EvenHeader.RightAlignedText = string.Format("Page {0} of {1}", ExcelHeaderFooter.PageNumber, ExcelHeaderFooter.NumberOfPages);
                sheet.PrinterSettings.HeaderMargin = 0.3M / 2.54M;
                sheet.PrinterSettings.FitToPage = true;
                sheet.PrinterSettings.FitToWidth = 1;
                sheet.PrinterSettings.FitToHeight = 0;
                return File(excel.GetAsByteArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", title + ".xlsx");
            }
            else
            {
                return Content("找不到任何紀錄。");
            }
        }