public ActionResult Report19_ByUser(DateTime? startDate, DateTime? endDate, DateTime? lastUpdateDate, ref ExpenseReportModel model)
        {
            bool hasRecord = false;

            SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["reportConnection"].ConnectionString);

            string subContractor_userGroup_name = UserGroup.SubCon;
            int subContractor_id = int.Parse((from o in this._db.recsys_option
                                              where o.type_code == "UG" && o.active == true && o.name == subContractor_userGroup_name
                                              select o.value).FirstOrDefault());

            using (connection)
            {
                SqlCommand command = new SqlCommand(
                    string.Format(
                  @"
                    SELECT
                        cs.user_id, cs.id, u.name, u.employee_number, cc.code,
                        cs.price1
                    FROM [recsys_costs] cs
                    join [recsys_users] u on cs.user_id = u.id
                    join [recsys_type] t on u.type = t.id
                    left outer join [recsys_cost] cc on cs.cost_id = cc.id
                    where {0}
                    {1}
                    {2}
                    {3}
                    {4}
                    order by u.employee_number, cs.account_date
                    ",
                    "u.user_group != " + subContractor_id,
                    startDate.HasValue ? string.Format("and account_date >= '{0}'", startDate.Value.ToString(DB_DATE_FORMAT)) : string.Empty,
                    endDate.HasValue ? string.Format("and account_date < DATEADD(day, 1,'{0}')", endDate.Value.ToString(DB_DATE_FORMAT)) : string.Empty,
                    "and cs.status = 0",
                    lastUpdateDate.HasValue ? string.Format("and cs.last_update >= '{0}' and cs.last_update < DATEADD(day,1,'{0}')", lastUpdateDate.Value.ToString(DB_DATE_FORMAT),
                                                                 ")") : string.Empty
                    ),
                  connection);
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();

                hasRecord = reader.HasRows;
                if (!hasRecord)
                {
                    //# log down
                }
                else
                {

                    model.ByUserRows = new List<ExpenseReportByUserRow>();
                    while (reader.Read())
                    {
                        ExpenseReportByUserRow newRow = model.ByUserRows.FirstOrDefault(theRow => theRow.EmployeeID == reader.GetInt32(0));

                        if (newRow == null)     //# if the row does not exists, add the new row and map details to it
                        {
                            newRow = new ExpenseReportByUserRow();
                            if (!reader.IsDBNull(0))
                            {
                                newRow.EmployeeID = reader.GetInt32(0);
                            }
                            if (!reader.IsDBNull(2))
                            {
                                newRow.EmployeeName = reader.GetString(2);
                            }
                            if (!reader.IsDBNull(3))
                                newRow.EmployeeNumber = reader.GetString(3);
                            model.ByUserRows.Add(newRow);
                        }

                        //# add sub rows
                        newRow.SubRows.Add(new ExpenseReportByUserSubRow()
                        {
                            CategoryName = reader.IsDBNull(4) ? string.Empty : reader.GetString(4),
                            Price = reader.IsDBNull(5) ? 0 : reader.GetDouble(5)
                        });

                    }
                    reader.Close();
                }

            }

            if (hasRecord)
                return View("~/Views/ReportsGen/Report19_ByUser.cshtml", model);
            else
                return Content("找不到任何紀錄。");
        }
        public ActionResult Report19_BySupplier(DateTime? startDate, DateTime? endDate, IEnumerable<int> supplierIDs, DateTime? lastUpdateDate, ref ExpenseReportModel model)
        {
            //# validation
            if (supplierIDs.Count() <= 0)
                throw new SystemException("invalid Suppliers parameter values");

            bool hasRecord = false;

            SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["reportConnection"].ConnectionString);
            string idsSqlClause = string.Empty;
            foreach (int id in supplierIDs)
                idsSqlClause += (string.IsNullOrEmpty(idsSqlClause) ? string.Empty : ",") + id;

            using (connection)
            {
                SqlCommand command = new SqlCommand(
                    string.Format(
                  @"
                    select id, name
                    from recsys_supplier
                    where id in ({0})
                    order by name
                    ",
                     idsSqlClause
                     ),
                  connection);
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();

                hasRecord = reader.HasRows;
                if (!hasRecord)
                {
                    //# log down
                }
                else
                {

                    model.BySupplierRows = new List<ExpenseReportBySupplierRow>();
                    while (reader.Read())
                    {
                        ExpenseReportBySupplierRow newRow = new ExpenseReportBySupplierRow();
                        if (!reader.IsDBNull(0))
                            newRow.SupplierID = reader.GetInt32(0);
                        if (!reader.IsDBNull(1))
                            newRow.SupplierName = reader.GetString(1);
                        model.BySupplierRows.Add(newRow);
                    }
                    reader.Close();

                    //# get sub-rows
                    command = new SqlCommand(
                        string.Format(
                      @"
                    select
                        s.id,
                        s.name,
                        c.code,
                        cc.price1
                    from recsys_costs cc
                    join recsys_supplier s on cc.supplier_id = s.id
                    left outer join recsys_cost c on cc.cost_id = c.id
                    where s.id in ({0})
                    {1}
                    {2}
                    {3}
                    order by cc.account_date
                    ",
                         idsSqlClause,
                         startDate.HasValue ? string.Format("and account_date >= '{0}'", startDate.Value.ToString(DB_DATE_FORMAT)) : string.Empty,
                         endDate.HasValue ? string.Format("and account_date < DATEADD(day, 1, '{0}')", endDate.Value.ToString(DB_DATE_FORMAT)) : string.Empty,
                         lastUpdateDate.HasValue ? string.Format("and cc.last_update >= '{0}' and cc.last_update < DATEADD(day,1,'{0}')", lastUpdateDate.Value.ToString(DB_DATE_FORMAT),
                                                                 ")") : string.Empty
                         ),
                      connection);
                    reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        int supplierID = reader.GetInt32(0);
                        ExpenseReportBySupplierRow newRow = model.BySupplierRows.FirstOrDefault(theRow => theRow.SupplierID == supplierID);
                        newRow.SubRows.Add(new ExpenseReportBySupplierSubRow()
                        {
                            CategoryName = reader.IsDBNull(2) ? string.Empty : reader.GetString(2),
                            Price = reader.IsDBNull(3) ? 0 : reader.GetDouble(3)
                        });
                    }
                    reader.Close();
                }

            }

            if (hasRecord)
                return View("~/Views/ReportsGen/Report19_BySupplier.cshtml", model);
            else
                return Content("找不到任何紀錄。");
        }
        public ActionResult Report19_ByCategory(DateTime? startDate, DateTime? endDate, IEnumerable<int> costCategoryIDs, DateTime? lastUpdateDate, ref ExpenseReportModel model)
        {
            //# validation
            if (costCategoryIDs.Count() <= 0)
                throw new SystemException("invalid Cost Categories parameter values");

            bool hasRecord = false;

            SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["reportConnection"].ConnectionString);
            string idsSqlClause = string.Empty;
            foreach (int id in costCategoryIDs)
                idsSqlClause += (string.IsNullOrEmpty(idsSqlClause) ? string.Empty : ",") + id;

            //string dateClause = string.Empty;
            //if (startDate.HasValue && endDate.HasValue)
            //{
            //    dateClause = string.Format(" and account_date between {0} and {1} ",  startDate.Value.ToString(DB_DATE_FORMAT), endDate.Value.ToString(DB_DATE_FORMAT));
            //}
            //else
            //{
            //    if (startDate.HasValue)
            //    {
            //        dateClause += string.Format("and account_date >= '{0}'", startDate.Value.ToString(DB_DATE_FORMAT));
            //    }

            //    if (endDate.HasValue)
            //    {
            //        dateClause += string.Format("and account_date <= '{0}'", endDate.Value.ToString(DB_DATE_FORMAT));
            //    }
            //}

            using (connection)
            {
                SqlCommand command = new SqlCommand(
                    string.Format(
                  @"
                    select id, code, name
                    from recsys_cost
                    where id in ({0})
                    order by code
                    ",
                     idsSqlClause
                     ),
                  connection);
                connection.Open();

                SqlDataReader reader = command.ExecuteReader();

                hasRecord = reader.HasRows;
                if (!hasRecord)
                {
                    //# log down
                }
                else
                {

                    model.ByCategoriesRows = new List<ExpenseReportByCategoryRow>();
                    while (reader.Read())
                    {
                        ExpenseReportByCategoryRow newRow = new ExpenseReportByCategoryRow();
                        if (!reader.IsDBNull(0))
                            newRow.CategoryID = reader.GetInt32(0);
                        if (!reader.IsDBNull(1))
                            newRow.CategoryCode = reader.GetString(1);
                        if (!reader.IsDBNull(2))
                            newRow.CategoryName = reader.GetString(2);
                        model.ByCategoriesRows.Add(newRow);
                    }
                    reader.Close();

                    //# get sub-rows
                    command = new SqlCommand(
                        string.Format(
                      @"
                    SELECT
                        co.cost_id as costCategoryID,
                        co.center,
                        c.customer_code,
                        co.price1,
                        q.number,
                        m.start_date,
                        m.end_date,
                        r.table2
                    FROM [recsys_costs] co
                    left join [recsys_relate] r on co.id = r.id1
                    left join [recsys_maintenance] m on m.id = r.id2 and r.table2 = 'maintenance'
                    left join [recsys_quotation] q on q.id = r.id2 and r.table2 = 'quotation'
                    left join [recsys_relate_customers] c on c.id = isnull(m.customer_id, q.customer_id)
                    where r.table1 = 'costs'
                    and co.cost_id is not null
                    and co.cost_id in ({0})
                    {1}
                    {2}
                    {3}
                    order by account_date
                    ",
                         idsSqlClause,
                         startDate.HasValue ? string.Format("and account_date >= '{0}'", startDate.Value.ToString(DB_DATE_FORMAT)) : string.Empty,
                         endDate.HasValue ? string.Format("and account_date < DATEADD(day, 1, '{0}')", endDate.Value.ToString(DB_DATE_FORMAT)) : string.Empty,
                         lastUpdateDate.HasValue ? string.Format("and co.last_update >= '{0}' and co.last_update < DATEADD(day,1,'{0}')", lastUpdateDate.Value.ToString(DB_DATE_FORMAT),
                                                                 ")") : string.Empty

                        ),
                      connection);
                    reader = command.ExecuteReader();
                    while (reader.Read())
                    {
                        int costCategoryID = reader.GetInt32(0);
                        ExpenseReportByCategoryRow newRow = model.ByCategoriesRows.FirstOrDefault(theRow => theRow.CategoryID == costCategoryID);
                        newRow.SubRows.Add(new ExpenseReportByCategorySubRow()
                        {
                            Center = reader.IsDBNull(1)
                                            ? null
                                            : new Center() { name = reader.GetString(1) },
                            CustomerCode = reader.GetString(7)=="maintenance"
                                           ? reader.IsDBNull(2)
                                                        ? string.Empty
                                                        : reader.IsDBNull(5)
                                                                ? reader.GetString(2)
                                                                : reader.IsDBNull(6)
                                                                            ? reader.GetString(2) + " ( " + reader.GetDateTime(5).ToString("dd/MM/yyyy") + " - )"
                                                                            : reader.GetString(2) + " ( " + reader.GetDateTime(5).ToString("dd/MM/yyyy") + " - " + reader.GetDateTime(6).ToString("dd/MM/yyyy") + " )"
                                           : reader.GetString(7)=="quotation"
                                                        ? reader.IsDBNull(4)
                                                                    ? "ACQ No.: "
                                                                    : "ACQ No.: " + reader.GetString(4)
                                                        : string.Empty,
                            Price = reader.IsDBNull(3) ? 0 : reader.GetDouble(3)
                        });
                    }
                    reader.Close();
                }

            }

            if (hasRecord)
                return View("~/Views/ReportsGen/Report19_ByCategory.cshtml", model);
            else
                return Content("找不到任何紀錄。");
        }
        public ActionResult Report19_BySubCon(DateTime? startDate, DateTime? endDate, DateTime? lastUpdateDate, ref ExpenseReportModel model)
        {
            string sql = string.Format(
                @"
                SELECT
                    s.name as subConName, u.name as userName, cc.code + ' ' + cc.name as category, cs.invoice, cs.price1 as price, cs.account_date, cs.remark,
                    c.customer_code as customerCode, q.number as number, m.start_date as startDate, m.end_date as endDate, r.table2 as tableName
                FROM [recsys_costs] cs
                left join [recsys_users] u on cs.user_id = u.id
                left join [recsys_supplier] s on cs.supplier_id = s.id
                left outer join [recsys_cost] cc on cs.cost_id = cc.id
                left join [recsys_relate] r on cs.id = r.id1
                left join [recsys_maintenance] m on m.id = r.id2 and r.table2 = 'maintenance'
                left join [recsys_quotation] q on q.id = r.id2 and r.table2 = 'quotation'
                left join [recsys_relate_customers] c on c.id = isnull(m.customer_id, q.customer_id)
                where cc.status = 1 and cc.IsSubCon = 1 and r.table1 = 'costs'
                {0}
                {1}
                {2}
                order by cc.code, cs.account_date, u.name, s.name",
                startDate.HasValue ? string.Format("and account_date >= '{0}'", startDate.Value.ToString(DB_DATE_FORMAT)) : string.Empty,
                endDate.HasValue ? string.Format("and account_date < DATEADD(day, 1,'{0}')", endDate.Value.ToString(DB_DATE_FORMAT)) : string.Empty,
                lastUpdateDate.HasValue ? string.Format("and cs.last_update >= '{0}' and cs.last_update < DATEADD(day,1,'{0}')", lastUpdateDate.Value.ToString(DB_DATE_FORMAT),
                                                                ")") : string.Empty
                );

            IEnumerable<report19_bySubCon> expenses = Common.queryToObject<report19_bySubCon>(this._db.ExecuteStoreQuery<report19_bySubCon>(sql));

            //Maintenance/ACQ No. Column
            foreach (report19_bySubCon expense in expenses)
            {
                expense.maintenanceACQ = expense.tableName == "maintenance"
                                           ? string.IsNullOrEmpty(expense.customerCode)
                                                        ? string.Empty
                                                        : expense.startDate.HasValue
                                                                ? expense.endDate.HasValue
                                                                            ? expense.customerCode + " ( " + expense.startDate.Value.ToString("dd/MM/yyyy") + " - " + expense.endDate.Value.ToString("dd/MM/yyyy") + " )"
                                                                            : expense.customerCode + " ( " + expense.startDate.Value.ToString("dd/MM/yyyy") + " - )"
                                                                : expense.customerCode
                                           : expense.tableName == "quotation"
                                                        ? string.IsNullOrEmpty(expense.number)
                                                                    ? "ACQ No.: "
                                                                    : "ACQ No.: " + expense.number
                                                        : string.Empty;
            }

            if (expenses.Count()>0)
            {

                /*-----------Excel-----------*/
                string title = "Expenses by Sub-Contractor";

                string[] header = new string[]
                {
                    "Category",
                    "User名稱",
                    "供應商",
                    "Maintenance/ACQ No.",
                    "Invoice",
                    "價錢",
                    "入帳日期",
                    "備註"
                };

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

                int row = 1, col = 1;
                #region header
                sheet.Cells[1, 1].Value = title;
                sheet.Cells[2, 8].Value = "Date: " + DateTime.Now.Date.ToString("dd/MM/yyyy");
                row += 3;

                foreach (string colName in header)
                {
                    sheet.Cells[row, col++].Value = colName;
                }
                sheet.Cells[4, 1, row, col].Style.Font.Bold = true;
                #endregion

                ++row;

                string lastCategory = "";
                int repeatedCategoryNum = 0;

                foreach (report19_bySubCon expense in expenses)
                {
                    col = 1;
                    #region merge record for each subcontractor
                    if (expense.category == lastCategory) repeatedCategoryNum++;
                    else
                    {
                        if(repeatedCategoryNum>0)
                            sheet.Cells[row - 1 - repeatedCategoryNum, 1, row - 1, 1].Merge = true;
                        repeatedCategoryNum = 0;
                    }
                    #endregion

                    #region data
                    sheet.Cells[row, col++].Value = expense.category;
                    sheet.Cells[row, col++].Value = expense.userName;
                    sheet.Cells[row, col++].Value = expense.subConName;
                    sheet.Cells[row, col++].Value = expense.maintenanceACQ;
                    sheet.Cells[row, col++].Value = expense.invoice;
                    sheet.Cells[row, col++].Value = expense.price;
                    sheet.Cells[row, col++].Value = string.Format("{0:dd/MM/yyyy}", expense.account_date);
                    sheet.Cells[row, col++].Value = expense.remark;
                    #endregion

                    lastCategory = expense.category;
                    row++;
                }

                #region merge expenses for the lastest sub-contractor
                if (repeatedCategoryNum > 0)
                {
                    sheet.Cells[row - 1 - repeatedCategoryNum, 1, row - 1, 1].Merge = true;
                }
                #endregion

                #region set date and number column format
                sheet.Column(7).Style.Numberformat.Format = @"dd/MM/yyyy";
                sheet.Column(6).Style.Numberformat.Format = @"0.00";
                #endregion

                #region set autofit, alignment and wraptext
                for (int i = 1; i < col; i++)
                {
                    sheet.Column(i).AutoFit(0, 30);
                    sheet.Column(i).Style.VerticalAlignment = ExcelVerticalAlignment.Top;
                }
                sheet.Column(4).AutoFit(0, 30);
                sheet.Cells[5, 1, row - 1, 5].Style.WrapText = true;
                sheet.Cells[5, 8, row - 1, 8].Style.WrapText = true;
                #endregion

                sheet.Cells[1, 1, 1, 8].Merge = true;
                sheet.Cells[1, 1].Style.Font.Bold = true;
                sheet.Cells[1, 1].Style.Font.Size = 20;

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

                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("找不到任何紀錄。");
        }
        public ActionResult Report19(Report19Model model)
        {
            ExpenseReportModel reportModel = new ExpenseReportModel()
            {
                CompanyName1 = "REC ENGINEERING COMPANY LIMITED",
                CompanyName2 = "REC ENGINEERING CONTRACTING COMPANY LIMITED",
                Mode = model.Mode,
                PrintingDate = DateTime.Now
            };

            DateTime? start;
            DateTime? end;
            if (!string.IsNullOrEmpty(model.start_date))
            {
                start = DateTime.ParseExact(model.start_date, DATEPICKER_DATE_FORMAT, null);
            }
            else
            {
                start = null;
            }
            if (!string.IsNullOrEmpty(model.end_date))
            {
                end = DateTime.ParseExact(model.end_date, DATEPICKER_DATE_FORMAT, null);
            }
            else
            {
                end = null;
            }

            DateTime? lastUpdate;
            if (!string.IsNullOrEmpty(model.lastUpdateDate))
            {
                lastUpdate = DateTime.ParseExact(model.lastUpdateDate, DATEPICKER_DATE_FORMAT, null);
            }
            else
            {
                lastUpdate = null;
            }

            switch (model.Mode)
            {
                case Report19ModelMode.By_Category:
                    if (model.SelectedCostCategories == null || model.SelectedCostCategories.Count() <= 0)
                        return Content("請選擇Cost Category");
                    else
                        return this.Report19_ByCategory(start, end, model.SelectedCostCategories, lastUpdate, ref reportModel);
                case Report19ModelMode.By_Employee:
                    return this.Report19_ByUser(start, end, lastUpdate, ref reportModel);
                case Report19ModelMode.By_Sub__Contractor:
                    return this.Report19_BySubCon(start, end, lastUpdate, ref reportModel);
                case Report19ModelMode.By_Supplier:
                    if (model.SelectedSuppliers == null || model.SelectedSuppliers.Count() <= 0)
                        return Content("請選擇Supplier");
                    else
                        return this.Report19_BySupplier(start, end, model.SelectedSuppliers, lastUpdate, ref reportModel);
                case Report19ModelMode.By_Minor_Work:
                    var minor_work_cats = (from c in this._db.recsys_cost
                                    where c.status == 1 && c.IsMinorWork == 1
                                    select c.id);
                    int[] minor_work_cats_array = minor_work_cats.ToArray();    //minor-work-category-id
                    if (minor_work_cats_array.Length == 0) return Content("沒有設定Minor Work Category");
                    return this.Report19_ByCategory(start, end, minor_work_cats_array, lastUpdate, ref reportModel);
                default:
                    return Content("請選擇報告模式");
            }
        }