Ejemplo n.º 1
0
 public ActionResult Report20(Report20Model model)
 {
     //return Pdf("/reportsgen/report20/", this._report[20].ToString(), false);
     return new ReportsGenController().Report20(model);
 }
Ejemplo n.º 2
0
        public ActionResult Report20(Report20Model model)
        {
            int choose_month;
            int choose_year;

            if (model.choose_month == 0) // no selection
                choose_month = DateTime.Now.Month;
            else //value selected
                choose_month = model.choose_month;

            if (model.choose_year == 0) // no selection
                choose_year = DateTime.Now.Year;
            else //value selected
                choose_year = System.DateTime.Now.AddYears(model.choose_year-5).Year;

            ArrayList where = new ArrayList();

            if (choose_month >= 0 )
            {
                string start_date = new DateTime(choose_year, choose_month, 1).ToString("yyyy-MM-dd");
                string end_date = new DateTime(choose_year, choose_month, 1).AddMonths(1).ToString("yyyy-MM-dd");

                where.Add("repair_date >= '" + start_date + "'");
                where.Add("repair_date < '" + end_date + "'");

            }
            else
            {
                return Content("找不到任何紀錄。");
            }

            string sql = @"
                select
                    o.id, o.repair_date, c.customer_code, c.name as customer_name, c.address2, o.create_date, o.fault, c.contact, c.tel1, pu.name as recipient, e1.name as technician1, e2.name as technician2, e3.name as technician3, e4.name as technician4, ahe.name as adhocTechnician, o.start_time, o.end_time, o.actual_start_time, o.actual_end_time, o.completion_date, o.remark
                from recsys_order as o
                left outer join recsys_relate_customers as c on o.customer_id = c.id
                left outer join recsys_users as pu on o.call_pickup_user_id = pu.id
                left outer join recsys_users as e1 on o.engineer_id_1 = e1.id
                left outer join recsys_users as e2 on o.engineer_id_2 = e2.id
                left outer join recsys_users as e3 on o.engineer_id_3 = e3.id
                left outer join recsys_users as e4 on o.engineer_id_4 = e4.id
                left outer join recsys_users as ahe on o.ad_hoc_engineer_id = ahe.id
                where o.status = '1' AND o.by_system = '1' AND o.order_status = '" + model.order_status + "'"
                + (where.Count > 0 ? " AND " + String.Join(" AND ", where.ToArray()) : String.Empty) + @"
                order by c.address2";

            IEnumerable<report20> records = Common.queryToObject<report20>(this._db.ExecuteStoreQuery<report20>(sql)).ToList();
            if (records.Count() > 0)
            {
                string title = "Maintenance Status Report";

                string[] header = new string[]
                {
                    "項目",
                    "Order ID",
                    "ACM",
                    "客戶名稱",
                    "地址",
                    "落單日期",
                    "內容",
                    "聯絡人",
                    "電話",
                    "接聽者",
                    "維修員1",
                    "維修員2",
                    "維修員3",
                    "維修員4",
                    "臨時維修員",
                    "接單時間",
                    "完成時間",
                    "實際完成日期",
                    "備註"
                };

                ExcelPackage excel = new ExcelPackage();
                ExcelWorksheet sheet = excel.Workbook.Worksheets.Add(title);
                sheet.Cells.Style.HorizontalAlignment = ExcelHorizontalAlignment.Left;

                #region top
                sheet.Cells[1, 1].Value = title;
                sheet.Cells[1, 1, 1, 4].Merge = true;
                sheet.Cells[1, 1].Style.Font.Bold = true;
                sheet.Cells[1, 1].Style.Font.Size = 16;

                sheet.Cells[2, 1, 2, 4].Merge = true;
                sheet.Cells[2, 1].Value = "Status : " + this._order_status[model.order_status-1].ToString();
                sheet.Cells[3, 1, 3, 4].Merge = true;
                sheet.Cells[3, 1].Value = "Selection month : " + choose_year.ToString() + "/" + choose_month.ToString("00");
                #endregion top

                int row = 5, col = 1, i =1;

                #region header
                foreach (string colName in header)
                {
                    sheet.Cells[row, col++].Value = colName;
                }
                sheet.Row(5).Height = 30.0;
                #endregion header

                ++row;

                #region data
                foreach (report20 record in records)
                {
                    col = 1;
                    sheet.Cells[row, col++].Value = i;
                    sheet.Cells[row, col++].Value = record.id;
                    sheet.Cells[row, col++].Value = record.customer_code;
                    sheet.Cells[row, col++].Value = record.customer_name;
                    sheet.Cells[row, col++].Value = record.address2;
                    sheet.Cells[row, col++].Value = string.Format("{0:dd/MM/yyyy}", record.create_date);
                    sheet.Cells[row, col++].Value = record.fault;
                    sheet.Cells[row, col++].Value = record.contact;
                    sheet.Cells[row, col++].Value = record.tel1;
                    sheet.Cells[row, col++].Value = record.recipient;
                    sheet.Cells[row, col++].Value = record.technician1;
                    sheet.Cells[row, col++].Value = record.technician2;
                    sheet.Cells[row, col++].Value = record.technician3;
                    sheet.Cells[row, col++].Value = record.technician4;
                    sheet.Cells[row, col++].Value = record.ad_hoc_technician;
                    sheet.Cells[row, col++].Value = (record.actual_start_time != null) ? string.Format("{0:hh\\:mm}", record.actual_start_time) : string.Format("{0:hh\\:mm}", record.start_time);
                    sheet.Cells[row, col++].Value = (record.actual_end_time != null) ? string.Format("{0:hh\\:mm}", record.actual_end_time) : string.Format("{0:hh\\:mm}", record.end_time);
                    sheet.Cells[row, col++].Value = string.Format("{0:dd/MM/yyyy}", record.completion_date);
                    sheet.Cells[row++, col++].Value = record.remark;
                    i++;
                }
                #endregion data

                #region set alignments
                sheet.Cells[1, 1, row - 1, col - 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                sheet.Cells[5, 1, row - 1, col - 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                #endregion set alignments

                for (i = 1; i < col; i++)
                    for (int j = 5; j < row; j++)
                        sheet.Cells[j, i].Style.Border.BorderAround(ExcelBorderStyle.Thin);

                #region autofit columns
                sheet.Column(1).AutoFit(10.0, 11.0);
                sheet.Column(2).AutoFit(14.0, 15.0);
                sheet.Column(3).AutoFit(11.0, 23.0);
                sheet.Column(4).AutoFit(0, 30.0);
                sheet.Column(5).AutoFit(0, 30.0);
                sheet.Column(6).AutoFit(14.5, 15.0);
                sheet.Column(7).AutoFit(10.5, 15.0);
                sheet.Column(8).AutoFit(0, 30.0);
                sheet.Column(9).AutoFit(12.5, 20.0);
                for (i = 10 ; i <= 14; i++)
                {
                    sheet.Column(i).AutoFit(14.0, 15.0);
                }
                sheet.Column(15).AutoFit(16.5, 17.0);
                sheet.Column(16).AutoFit(14.0, 15.0);
                sheet.Column(17).AutoFit(14.0, 15.0);
                sheet.Column(18).AutoFit(19.0, 20.0);
                sheet.Column(19).AutoFit(14.0, 50.0);
                #endregion autofit columns

                #region set time and date format
                sheet.Cells[6, 6, row - 1, 6].Style.Numberformat.Format = "dd-MM-yyyy";
                sheet.Cells[6, 18, row - 1, 18].Style.Numberformat.Format = "dd-MM-yyyy";
                sheet.Cells[6, 16, row - 1, 16].Style.Numberformat.Format = "hh\\:mm";
                sheet.Cells[6, 17, row - 1, 17].Style.Numberformat.Format = "hh\\:mm";
                #endregion set time and date format

                #region set wrap text
                sheet.Cells[6, 3, row - 1, 5].Style.WrapText = true;
                sheet.Cells[6, 7, row - 1, 9].Style.WrapText = true;
                sheet.Cells[6, 19, row - 1, 19].Style.WrapText = true;
                #endregion

                sheet.Cells[5, 1, row - 1, col - 1].AutoFilter = true;

                sheet.PrinterSettings.FitToPage = true;
                sheet.PrinterSettings.FitToWidth = 1;
                sheet.PrinterSettings.FitToHeight = 0;
                return File(excel.GetAsByteArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", title + ".xlsx");
                //return View("~/Views/ReportsGen/Report20.cshtml", records);
            }
            else
            {
                return Content("找不到任何紀錄。");
            }
        }