Beispiel #1
0
        public DataTable GetTaxiSummary(ManageListParms parms)
        {
            string strSQL =
                @"SELECT DISTINCT overtime.enddatetime    AS EndDatetime,
				Count(emp.nationaltype) AS Total
FROM   overtimeform overtime
	   LEFT OUTER JOIN employee emp
					ON overtime.employeeid_fk = emp.employeeid
	   LEFT OUTER JOIN department dept
					ON overtime.supportdeptid_fk = dept.departmentid
       LEFT OUTER JOIN SignForm_Main Sign_M
					ON overtime.SignDocID_FK=Sign_M.SignDocID
{0}
GROUP  BY overtime.enddatetime ";

            string strConditions = String.Format(
                @"WHERE  overtime.startdatetime >= @StartDatetime
	   AND overtime.enddatetime < @EndDatetime
	   AND (emp.nationaltype NOT IN ( 'Taiwan', 'Japan' )  and emp.SexType <>'F') and Sign_M.FinalStatus <>'5' and emp.CostDepartmentID not like'39% '
");

            var conditionsDic = new Conditions()
            {
                { "@StartDatetime", parms.StartDateTime.HasValue ? parms.StartDateTime.Value : UTC_START_DATETIME },
                { "@EndDatetime", parms.EndDateTime.HasValue ? parms.EndDateTime.Value : DateTime.Now }
            };

            //組成查詢條件 SQL
            strSQL = String.Format(strSQL, strConditions);

            return(_dc.QueryForDataTable(strSQL, conditionsDic));
        }
Beispiel #2
0
        //列表
        public Pagination GetPagination(ManageListParms mlParms, PaggerParms pParms)
        {
            //組出SQL查詢語句
            string strSQL =
                @"select distinct * from (select TOP 18000 *  from ( SELECT  overtime.employeeid_fk,
                                emp.employeename,
                                right(overtime.supportdeptid_fk,4) supportdeptid_fk,
                                dept.departmentname,
                                overtime.startdatetime,
                                overtime.enddatetime,
                                overtime.paytypekey,
                                overtime.mealorderkey,
                                emp.nationaltype
                            FROM   overtimeform overtime
                                LEFT OUTER JOIN employee emp
                                ON overtime.employeeid_fk = emp.employeeid
                                LEFT OUTER JOIN department dept
                                ON overtime.supportdeptid_fk = dept.departmentid
                                LEFT OUTER JOIN SignForm_Main Sign_M
                                ON overtime.SignDocID_FK=Sign_M.SignDocID
                                {0} ) as aa) as overtime
                                       ";
            //設定Where條件
            string strConditions = String.Format(
                @"WHERE  overtime.startdatetime >= @StartDatetime
                    AND overtime.enddatetime < @EndDatetime and Sign_M.FinalStatus <>'5' and emp.CostDepartmentID not like'39%' ");
            //設定參數話查詢欄位
            var conditionsDic = new Conditions()
            {
                { "@StartDatetime", mlParms.StartDateTime.HasValue ? mlParms.StartDateTime.Value : UTC_START_DATETIME },
                { "@EndDatetime", mlParms.EndDateTime.HasValue ? mlParms.EndDateTime.Value : DateTime.Now }
            };
            //設定資料所需參數(PaginationParms為資料查詢模型)
            var paginationParms = new PaginationParms()
            {
                QueryString     = String.Format(strSQL, strConditions),
                QueryConditions = conditionsDic,
                PageIndex       = pParms.PageIndex,
                PageSize        = pParms.PageSize
            };

            pParms.OrderField = "CreateDate".Equals(pParms.OrderField) ? "NationalType" : pParms.OrderField;
            string orderExpression = String.Format("{0}{1}", pParms.Descending ? "-" : "", pParms.OrderField);

            var allowColumns = new List <string>
            {
                "EmployeeID_FK", "EmployeeName", "SupportDeptID_FK", "DepartmentName", "StartDatetime", "EndDatetime", "PayTypeKey", "MealOrderKey", "NationalType"
            };

            //根據 SQL取得 Pagination
            return(_dc.QueryForPagination(paginationParms, orderExpression, allowColumns));
        }
        //列表
        public Pagination GetPagination(ManageListParms mlParms, PaggerParms pParms)
        {
            string tableName = RepositoryFactory.SmartManConn["DataSource"] + "." + RepositoryFactory.SmartManConn["Catelog"] + ".dbo.DailyOnOff";

            //20170223 修改convert (nvarchar, startDateTime, 112) = onoff.RealOnDate 為 convert (nvarchar, startDateTime, 112) = onoff.DUTYDATE
            //怕志元沒寫值進RealOnDate欄位
            string strSQL = String.Format(
                @"SELECT overtime.*, onoff.*, emp.CostDepartmentID  FROM   overtimeform overtime 
left outer join {0} onoff
on overtime.employeeID_FK = onoff.employecd 
left outer join employee emp
on overtime.employeeID_FK = emp.employeeID 
where autoinsert = 'False' and signDocID_FK = @SignDocID_FK and convert (nvarchar, startDateTime, 112) = onoff.DUTYDATE
and convert (nvarchar, startDateTime, 112) = onoff.DUTYDATE ", tableName);

            var conditionsDic = new Conditions()
            {
                { "@SignDocID_FK", mlParms.SignDocID },
            };

            var overtimeList = _rootRepo.QueryForOvertimeFormDataBySignDocID(mlParms.SignDocID);

            var paginationParms = new PaginationParms()
            {
                QueryString     = strSQL,
                QueryConditions = conditionsDic,
                PageIndex       = pParms.PageIndex,
                PageSize        = overtimeList.Count
            };

            pParms.OrderField = "CreateDate".Equals(pParms.OrderField) ? "SignDocID_FK" : pParms.OrderField;
            string orderExpression = String.Format("{0}{1}", pParms.Descending ? "-" : "", pParms.OrderField);

            var allowColumns = new List <string>
            {
                "SignDocID_FK", "EmployeeID_FK", "ApplyID_FK", "ApplyDateTime", "EmployeeID_FK", "DepartmentID_FK", "StartDateTime", "EndDateTime", "SupportDeptID_FK", "PayTypeKey", "MealOrderKey", "AutoInsert", "IsHoliday", "TotalHours", "Note"
            };

            //根據 SQL取得 Pagination
            return(_dc.QueryForPagination(paginationParms, orderExpression, allowColumns));
        }
        //列表
        public Pagination GetPagination(ManageListParms mlParms, PaggerParms pParms)
        {
            string strSQL =
                @"SELECT main.signdocid SignDocID, 
       overtime.ApplyID_FK ApplyID,
	   emp2.employeename Applyname,
	   overtime.employeeid_fk EmployeeID, 
	   emp.employeename EmployeeName,  
	   main.senddate SendDate, 
	   main.finalstatus FinalStatus, 
	   main.remainder Remainder, 
	   detail.chiefid_fk ChiefID, 
	   chief.employeename ChiefName, 
	   detail.status Status, 
	   overtime.paytypekey PayType, 
	   sup.departmentname SupportDeptName, 
	   overtime.startdatetime StartDateTime,  
	   overtime.enddatetime EndDateTime, 
	   overtime.totalhours TotalHours,
	   overtime.isholiday IsHoliday, 
	   overtime.autoinsert AutoInsert
FROM   signform_main main 
	   LEFT OUTER JOIN signform_detail detail 
					ON main.signdocid = detail.signdocid_fk 
	   LEFT OUTER JOIN overtimeform overtime 
					ON main.signdocid = overtime.signdocid_fk 
	   LEFT OUTER JOIN employee emp 
					ON overtime.employeeid_fk = emp.employeeid 
       LEFT OUTER JOIN employee emp2 
					ON overtime.ApplyID_FK = emp2.employeeid
	   LEFT OUTER JOIN employee chief 
					ON detail.chiefid_fk = chief.employeeid 
	   LEFT OUTER JOIN department sup 
					ON overtime.supportdeptid_fk = sup.departmentid 
 {0}";

            string strConditions = String.Format(
                @"WHERE overtime.startdatetime >= @StartDatetime 
	   AND overtime.enddatetime < @EndDatetime and finalStatus<>'5'"    );

            var conditionsDic = new Conditions()
            {
                { "@StartDatetime", mlParms.StartDateTime.HasValue ? mlParms.StartDateTime.Value : UTC_START_DATETIME },
                { "@EndDatetime", mlParms.EndDateTime.HasValue ? mlParms.EndDateTime.Value : DateTime.Now },
                { "@FinalStatus", mlParms.FinalStatus }
            };

            if (!String.IsNullOrWhiteSpace(mlParms.FinalStatus))
            {
                strConditions += " And finalStatus = @FinalStatus";
            }
            else
            {
                strConditions += " And finalStatus <> '6'";
            }


            var paginationParms = new PaginationParms()
            {
                QueryString     = String.Format(strSQL, strConditions),
                QueryConditions = conditionsDic,
                PageIndex       = pParms.PageIndex,
                PageSize        = pParms.PageSize
            };

            pParms.OrderField = "CreateDate".Equals(pParms.OrderField) ? "SignDocID" : pParms.OrderField;
            string orderExpression = String.Format("{0}{1}", pParms.Descending ? "-" : "", pParms.OrderField);

            var allowColumns = new List <string>
            {
                "SignDocID", "EmployeeID", "EmployeeName", "SendDate", "FinalStatus", "Remainder", "ChiefID", "ChiefName", "Status", "PayType", "StartDateTime", "EndDateTime", "SupportDeptName", "AutoInsert", "IsHoliday", "TotalHours"
            };

            //根據 SQL取得 Pagination
            return(_dc.QueryForPagination(paginationParms, orderExpression, allowColumns));
        }
Beispiel #5
0
        public DataTable GetMealSummary(ManageListParms parms)
        {
            #region #0002 增加避免重複判斷,避免加班餐車資料統計數量與列表不符情況 假日則不判斷,申請次就請幾次伙食

            string holidayCondition = string.Empty;
            {
                string sqlholidayday        = @"select * from HOLIDAY where holdate = @StartDatetime ";
                DB     smartDb              = ConnectionFactory.GetSmartManDC();
                var    conditionsHolidayday = new Conditions()
                {
                    { "@StartDatetime", parms.StartDateTime.HasValue ? parms.StartDateTime.Value.ToString("yyyyMMdd") : UTC_START_DATETIME.ToString("yyyyMMdd") }
                };
                var isHoliday = smartDb.QueryForDataTable(sqlholidayday, conditionsHolidayday);
                //不等於null就是假日,則申請幾筆顯示幾筆 2017-08-18 先將isHoliday設定為null 假日與平日一樣條件 有時間要將假日新增一個判斷時間若重複就篩掉 不同時段就累積加筆數
                isHoliday = null;
                if (isHoliday != null)
                {
                    holidayCondition = " FROM   overtimeform overtime ";
                }
                else
                {
                    holidayCondition = @"
                    from
                    (
                        select distinct o.supportdeptid_fk,o.startdatetime, o.enddatetime,o.mealorderkey,o.EmployeeID_FK
                        from OvertimeForm o
                        LEFT OUTER JOIN SignForm_Main Sign_M
                        ON o.SignDocID_FK = Sign_M.SignDocID
                        where startdatetime >= @StartDatetime and EndDateTime < @EndDatetime
                        and Sign_M.FinalStatus <> '5'
                    ) as overtime
                    ";
                }
            }

            #endregion #0002 增加避免重複判斷,避免加班餐車資料統計數量與列表不符情況 假日則不判斷,申請次就請幾次伙食

            string strSQL =
                @"
               --Select--
                SELECT right(SupportDeptId,4) as SupportDeptId,supportdeptname SupportDeptName,
                Sum(carnivore) Carnivore,
                Sum(vegan)     Vegan,
                Sum(none)      None,
                Sum(total)-Sum(none)     Total

               --select case 將table符合條件的給1--
                FROM   (
                SELECT dept.departmentname SupportDeptName, dept.DepartmentID SupportDeptId,
                    CASE
                    WHEN overtime.mealorderkey = 'None' THEN 1
                    ELSE 0
                    END                 AS None,
                    CASE
                    WHEN overtime.mealorderkey = 'Carnivore' THEN 1
                    ELSE 0
                    END                 AS Carnivore,
                    CASE
                    WHEN overtime.mealorderkey = 'Vegan' THEN 1
                    ELSE 0
                    END                 AS Vegan,
                    1                   AS Total "
                +
                //2017-07-24 #0002 增加子查詢 過濾掉重複的員工-
                holidayCondition
                +
                @"
                --Join Table--
                LEFT OUTER JOIN department dept
                ON overtime.supportdeptid_fk = dept.departmentid
                Left OUTER JOIN {1}.dbo.DUTYWORK dtw
                ON overtime.employeeid_fk = dtw.EMPLOYECD and CONVERT(varchar(12) , overtime.startdatetime, 112 )=dtw.WORKDATE
                {0}
                ) AS MealSummary
                GROUP  BY SupportDeptId,supportdeptname order by right(SupportDeptId,4) ";

            string table         = RepositoryFactory.SmartManConn["DataSource"] + "." + RepositoryFactory.SmartManConn["Catelog"];
            string strConditions = String.Format(
                @"WHERE  overtime.startdatetime >= @StartDatetime
                AND overtime.enddatetime < @EndDatetime
                AND (
                (dtw.HOLIDAY='N' AND REPLACE(SUBSTRING(CONVERT(CHAR(16),startdatetime,120),12,5),':','')>=1700)
                OR (dtw.HOLIDAY='Y' and REPLACE(SUBSTRING(CONVERT(CHAR(16),enddatetime,120),12,5),':','')>=0800)
                       )
                AND dept.departmentid NOT LIKE'39%'
                AND dept.departmentid NOT like'9239%'
                AND dept.departmentid NOT LIKE'9339%' ");

            var conditionsDic = new Conditions()
            {
                { "@StartDatetime", parms.StartDateTime.HasValue ? parms.StartDateTime.Value : UTC_START_DATETIME },
                { "@EndDatetime", parms.EndDateTime.HasValue ? parms.EndDateTime.Value : DateTime.Now }
            };

            //組成查詢條件 SQL
            strSQL = String.Format(strSQL, strConditions, table);
            return(_dc.QueryForDataTable(strSQL, conditionsDic));
        }