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)); }
//列表 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)); }
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)); }