using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace PayRollDAL.Query.DailyAttendance { public static class DailyAttendanceQB { // GB4 parity: EmployeeBLL.RegisterDeletion — deletes future attendance rows for an // employee from EffectiveFrom onward. Parameterized replacement of the GB4 string-concat SQL. public const string DELETE_DAILYATTENDANCE_FROM_DATE = @" DELETE FROM TDAILYATTENDANCE WHERE EMPLOYEEID = @EmployeeId AND ATTENDANCEDATE >= @EffectiveFrom;"; public const string GET_DAILYATTENDANCESINGLE_SQL = @" SELECT dailyatten0_.dailyattendanceid AS DailyAttendanceId, dailyatten0_.attendancedate AS DailyAttendanceAttendanceDate, dailyatten0_.employeeid AS EmployeeId, employee2_.employeecode AS EmployeeCode, employee2_.employeename AS EmployeeName, dailyatten0_.shiftid AS ShiftId, shift4_.shiftcode AS ShiftCode, shift4_.shiftdescription AS ShiftDescription, dailyatten0_.daytype AS DailyAttendanceDayType, dailyatten0_.leaveid AS LeaveId, leave6_.leavecode AS LeaveCode, leave6_.leavename AS LeaveName, dailyatten0_.intime AS DailyAttendanceInTime, dailyatten0_.outtime AS DailyAttendanceOutTime, dailyatten0_.workedhours AS DailyAttendanceWorkedHours, dailyatten0_.earlyin AS DailyAttendanceEarlyIn, dailyatten0_.earlyout AS DailyAttendanceEarlyOut, dailyatten0_.shorthours AS DailyAttendanceShortHours, dailyatten0_.extrahours AS DailyAttendanceExtraHours, dailyatten0_.othours AS DailyAttendanceOtHours, CAST(dailyatten0_.otfactorhours AS FLOAT) AS DailyAttendanceOtFactorHours, dailyatten0_.remarks AS DailyAttendanceRemarks, dailyatten0_.shiftmins AS DailyAttendanceShiftMins, dailyatten0_.bushours AS DailyAttendanceBusHours, dailyatten0_.perhours AS DailyAttendancePerHours, dailyatten0_.departmentid AS DepartmentId, department8_.departmentcode AS DepartmentCode, department8_.departmentname AS DepartmentName, dailyatten0_.worktypeid AS WorkTypeId, gcm1_.gcmcode AS WorkTypeCode, gcm1_.gcmname AS WorkTypeName, location11_.locationname AS DailyAttendanceLocation, dailyatten0_.latein AS DailyAttendanceLateIn, dailyatten0_.lateout AS DailyAttendanceLateOut, dailyatten0_.reasonid AS ReasonId, reason12_.reasoncode AS ReasonCode, reason12_.reasonname AS ReasonName, dailyatten0_.ismanualintime AS DailyAttendanceIsManualInTime, dailyatten0_.ismanualouttime AS DailyAttendanceIsManualOutTime, dailyatten0_.ismanualdaytype AS DailyAttendanceIsManualDayType, dailyatten0_.version AS DailyAttendanceVersion, dailyatten0_.previousdaytype AS DailyAttendancePreviousDayType, dailyatten0_.firsthalfleaveid AS FirstHalfLeaveId, leave14_.leavecode AS FirstHalfLeaveCode, leave14_.leavename AS FirstHalfLeaveName, dailyatten0_.secondhalfleaveid AS SecondHalfLeaveId, leave16_.leavecode AS SecondHalfLeaveCode, leave16_.leavename AS SecondHalfLeaveName, dailyatten0_.TEXTDAYTYPE AS TextDayType, CASE WHEN ( (ISNULL(applev.Number,0)=0 AND ISNULL(rejlev.Number,0)=0 AND ISNULL(othlev.Number,0)=0) AND (ISNULL(apptime.Number,0)=0 AND ISNULL(rejtime.Number,0)=0 AND ISNULL(OthTime.Number,0)=0) AND (ISNULL(appadj.Number,0)=0 AND ISNULL(rejadj.Number,0)=0 AND ISNULL(othadj.Number,0)=0) ) OR (ISNULL(rejlev.Number,0)>0 OR ISNULL(rejtime.Number,0)>0 OR ISNULL(rejadj.Number,0)>0) THEN 0 ELSE CASE WHEN ISNULL(applev.Number,0)>0 OR ISNULL(othlev.Number,0)=0 THEN CASE WHEN ISNULL(apptime.Number,0)>0 OR ISNULL(OthTime.Number,0)=0 THEN CASE WHEN ISNULL(appadj.Number,0)>0 OR ISNULL(othadj.Number,0)=0 THEN 2 ELSE 1 END ELSE 1 END ELSE 1 END END AS ColorId, employee2_.DESIGNATIONID AS DesignationId, gcm2_.gcmcode AS DesignationCode, gcm2_.gcmname AS DesignationName, dailyatten0_.createdbyid AS DailyAttendanceCreatedById, dailyatten0_.createdon AS DailyAttendanceCreatedOn, dailyatten0_.modifiedbyid AS DailyAttendanceModifiedById, dailyatten0_.modifiedon AS DailyAttendanceModifiedOn, dailyatten0_.status AS DailyAttendanceStatus FROM tdailyattendance dailyatten0_ JOIN memployee employee2_ ON dailyatten0_.employeeid = employee2_.employeeid LEFT JOIN mlocation location11_ ON employee2_.locationid = location11_.locationid LEFT JOIN mgcm gcm2_ ON employee2_.DESIGNATIONID = gcm2_.gcmid LEFT JOIN mshift shift4_ ON dailyatten0_.shiftid = shift4_.shiftid LEFT JOIN mleave leave6_ ON dailyatten0_.leaveid = leave6_.leaveid LEFT JOIN mdepartment department8_ ON dailyatten0_.departmentid = department8_.departmentid LEFT JOIN mreason reason12_ ON dailyatten0_.reasonid = reason12_.reasonid LEFT JOIN mleave leave14_ ON dailyatten0_.firsthalfleaveid = leave14_.leaveid LEFT JOIN mleave leave16_ ON dailyatten0_.secondhalfleaveid = leave16_.leaveid LEFT JOIN mgcm gcm1_ ON dailyatten0_.worktypeid = gcm1_.gcmid OUTER APPLY (SELECT COUNT(*) AS Number FROM TLEAVE a JOIN TLEAVEDETAIL b ON a.LEAVEID=b.LEAVEID JOIN MBIZTRANSACTIONTYPE c ON a.BIZTRANSACTIONTYPEID=c.BIZTRANSACTIONTYPEID WHERE c.BiztransactionclassId=-1399999955 AND b.EMPLOYEEID=dailyatten0_.EMPLOYEEID AND b.LEAVEDATE=dailyatten0_.ATTENDANCEDATE AND a.STATUS=1) applev OUTER APPLY (SELECT COUNT(*) AS Number FROM TLEAVE a JOIN TLEAVEDETAIL b ON a.LEAVEID=b.LEAVEID JOIN MBIZTRANSACTIONTYPE c ON a.BIZTRANSACTIONTYPEID=c.BIZTRANSACTIONTYPEID WHERE c.BiztransactionclassId=-1399999955 AND b.EMPLOYEEID=dailyatten0_.EMPLOYEEID AND b.LEAVEDATE=dailyatten0_.ATTENDANCEDATE AND a.STATUS=3) rejlev OUTER APPLY (SELECT COUNT(*) AS Number FROM TLEAVE a JOIN TLEAVEDETAIL b ON a.LEAVEID=b.LEAVEID JOIN MBIZTRANSACTIONTYPE c ON a.BIZTRANSACTIONTYPEID=c.BIZTRANSACTIONTYPEID WHERE c.BiztransactionclassId=-1399999955 AND b.EMPLOYEEID=dailyatten0_.EMPLOYEEID AND b.LEAVEDATE=dailyatten0_.ATTENDANCEDATE AND a.STATUS NOT IN (1,3)) othlev OUTER APPLY (SELECT COUNT(*) AS Number FROM TTIMESLIP a WHERE a.EMPLOYEEID=dailyatten0_.EMPLOYEEID AND a.ATTENDANCEDATE=dailyatten0_.ATTENDANCEDATE AND a.STATUS=1) apptime OUTER APPLY (SELECT COUNT(*) AS Number FROM TTIMESLIP a WHERE a.EMPLOYEEID=dailyatten0_.EMPLOYEEID AND a.ATTENDANCEDATE=dailyatten0_.ATTENDANCEDATE AND a.STATUS=3) rejtime OUTER APPLY (SELECT COUNT(*) AS Number FROM TTIMESLIP a WHERE a.EMPLOYEEID=dailyatten0_.EMPLOYEEID AND a.ATTENDANCEDATE=dailyatten0_.ATTENDANCEDATE AND a.STATUS NOT IN (1,3)) OthTime OUTER APPLY (SELECT COUNT(*) AS Number FROM TADJUSTMENT a JOIN TADJUSTMENTDETAIL b ON a.ADJUSTMENTID=b.ADJUSTMENTID WHERE b.EMPLOYEEID=dailyatten0_.EMPLOYEEID AND b.ATTENDANCEDATE=dailyatten0_.ATTENDANCEDATE AND a.STATUS=1) appadj OUTER APPLY (SELECT COUNT(*) AS Number FROM TADJUSTMENT a JOIN TADJUSTMENTDETAIL b ON a.ADJUSTMENTID=b.ADJUSTMENTID WHERE b.EMPLOYEEID=dailyatten0_.EMPLOYEEID AND b.ATTENDANCEDATE=dailyatten0_.ATTENDANCEDATE AND a.STATUS=3) rejadj OUTER APPLY (SELECT COUNT(*) AS Number FROM TADJUSTMENT a JOIN TADJUSTMENTDETAIL b ON a.ADJUSTMENTID=b.ADJUSTMENTID WHERE b.EMPLOYEEID=dailyatten0_.EMPLOYEEID AND b.ATTENDANCEDATE=dailyatten0_.ATTENDANCEDATE AND a.STATUS NOT IN (1,3)) othadj WHERE dailyatten0_.employeeid = @employeeid"; public const string GET_EMPLOYEESINGLE = @" SELECT e.EmployeeId AS EmployeeId, e.EmployeeCode AS EmployeeCode, e.EmployeeName AS EmployeeName, d.DepartmentId AS DepartmentId, d.DepartmentCode AS DepartmentCode, d.DepartmentName AS DepartmentName, wt.GCMId AS WorkTypeId, wt.GCMCode AS WorkTypeCode, wt.GCMName AS WorkTypeName, l.LocationName AS DailyAttendanceLocation, s.ShiftId AS ShiftId, s.ShiftCode AS ShiftCode, s.ShiftDescription AS ShiftDescription, e.DESIGNATIONID AS DesignationId, desg.GCMCode AS DesignationCode, desg.GCMName AS DesignationName FROM MEmployee e LEFT JOIN MDepartment d ON e.DepartmentId = d.DepartmentId LEFT JOIN MGCM wt ON e.WorkTypeId = wt.GCMId LEFT JOIN MLocation l ON e.LocationId = l.LocationId LEFT JOIN MShift s ON e.ShiftId = s.ShiftId LEFT JOIN MGCM desg ON e.DESIGNATIONID = desg.GCMId WHERE e.EmployeeId = @employeeid"; public const string GET_DAILYATTENDANCEREPORT = @" SELECT datt.DAILYATTENDANCEID AS DailyAttendanceId, datt.ATTENDANCEDATE AS DailyAttendanceAttendanceDate, datt.REMARKS AS Remarks, datt.EMPLOYEEID AS EmployeeId, emp.EMPLOYEECODE AS EmployeeCode, emp.EMPLOYEENAME AS EmployeeName, emp.FIRSTNAME AS Name, emp.SEX AS EmployeeSex, CASE WHEN emp.SEX = 0 THEN 'M' ELSE 'F' END AS SexName, emp.SORTORDER AS SortOrder, emp.DATEOFJOINING AS DOJ, CONVERT(VARCHAR(20), emp.DATEOFJOINING, 1) AS StringDOJ, CONVERT(VARCHAR(40), emp.DATEOFBIRTH, 1) AS StringDOB, emp.FATHERNAME AS FatherName, datt.SHIFTID AS ShiftId, shift.STARTTIME AS ShiftStartTime, shift.ENDTIME AS ShiftEndTime, shift.SHIFTCODE AS ShiftCode, shift.SHIFTDESCRIPTION AS ShiftDescription, pshift.SHIFTCODE AS PreviousShiftCode, pshift.SHIFTDESCRIPTION AS PreviousShiftDescription, datt.PREVIOUSDAYTYPE AS PreviousDayType, datt.DAYTYPE AS DailyAttendanceDayType, datt.TEXTDAYTYPE AS DailyAttendanceTextDayType, CASE WHEN datt.DAYTYPE = 0 THEN 'F' WHEN datt.DAYTYPE = 1 THEN 'HW-HA' WHEN datt.DAYTYPE = 2 THEN 'WOFF' WHEN datt.DAYTYPE = 3 THEN 'O' WHEN datt.DAYTYPE = 4 THEN 'L' WHEN datt.DAYTYPE = 5 THEN 'HW-HL' WHEN datt.DAYTYPE = 6 THEN 'CW' WHEN datt.DAYTYPE = 7 THEN 'COFF' WHEN datt.DAYTYPE = 8 THEN 'A' WHEN datt.DAYTYPE = 9 THEN 'H' WHEN datt.DAYTYPE = 10 THEN 'HL-HA' WHEN datt.DAYTYPE = 11 THEN 'CHW-HL' WHEN datt.DAYTYPE = 12 THEN 'CHW-HA' WHEN datt.DAYTYPE = 13 THEN 'RSND' WHEN datt.DAYTYPE = 14 THEN 'L' ELSE '' END AS DayType, datt.LEAVEID AS LeaveId, leave.LEAVECODE AS LeaveCode, leave.LEAVENAME AS LeaveName, CASE WHEN datt.INTIME - shift.STARTTIME > 0 THEN CONVERT(NUMERIC(18,2), datt.INTIME / 60 + (datt.INTIME % 60) / 60.0) ELSE 0 END AS LateInTime, CAST(DAY(datt.ATTENDANCEDATE) AS VARCHAR) AS Day, ISNULL(late.NoofTime, 0) AS NoofTimes, ISNULL(late.TotalMinutes, 0) AS TotalMinutes, :hourreplace datt.INTIME AS DailyAttendanceInTime, CASE WHEN datt.OUTTIME = datt.INTIME THEN 0 ELSE datt.OUTTIME END AS DailyAttendanceOutTime, CASE WHEN shift.STARTTIME > shift.ENDTIME THEN CASE WHEN datt.INTIME = 0 THEN 1440 WHEN datt.INTIME = datt.OUTTIME THEN CASE WHEN datt.INTIME > 1200 THEN datt.INTIME ELSE datt.INTIME + 1440 END ELSE datt.INTIME END ELSE datt.INTIME END AS DailyAttendanceInTimeForDuration, CASE WHEN shift.STARTTIME > shift.ENDTIME THEN CASE WHEN datt.OUTTIME > 1200 THEN datt.OUTTIME ELSE datt.OUTTIME + 1440 END ELSE CASE WHEN datt.OUTTIME >= datt.INTIME THEN datt.OUTTIME ELSE datt.OUTTIME + 1440 END END AS DailyAttendanceOutTimeForDuration, datt.WORKEDHOURS AS DailyAttendanceWorkedHours, datt.TOTALWORKEDHOURS AS TotalWorkedHrs, datt.DEPARTMENTID AS DepartmentId, dept.DEPARTMENTCODE AS DepartmentCode, dept.DEPARTMENTNAME AS DepartmentName, datt.WORKTYPEID AS WorkTypeId, wktype.GCMCODE AS WorkTypeCode, wktype.GCMNAME AS WorkTypeName, ouunit.ORGANIZATIONUNITCODE AS OUCode, ouunit.ORGANIZATIONUNITNAME AS OUName, emp.PRIMARYMOBILE + ',' + emp.SECONDARYMOBILE AS EmployeeMobile, staff.GCMNAME AS Batch, village.VILLAGECODE AS VillageCode, village.VILLAGENAME AS VillageName, location.LOCATIONNAME AS DailyAttendanceLocation, punch.GEOCODE AS GeoCode, punch.DeviceName AS DeviceName, datt.TEXTDAYTYPE AS TextDayType, CASE WHEN datt.DAYTYPE IN (10,14) THEN datt.TEXTDAYTYPE ELSE leave.LEAVECODE END AS LeaveType FROM TDAILYATTENDANCE datt :innerreplace :outerreplace LEFT JOIN ( SELECT a.EMPLOYEEID, a.ATTENDANCEDATE, a.GEOCODE, md.DEVICENAME FROM TPUNCH a INNER JOIN ( SELECT EMPLOYEEID, ATTENDANCEDATE, MIN(CASE WHEN PUNCHSTATUS = 0 THEN PUNCHTIME END) AS INTIME, MIN(PUNCHID) AS PUNCHID FROM TPUNCH WHERE ATTENDANCEDATE BETWEEN ':periodfrom' AND ':periodto' GROUP BY EMPLOYEEID, ATTENDANCEDATE ) sub ON a.PUNCHID = sub.PUNCHID INNER JOIN MDEVICE md ON md.DEVICEID = a.DEVICEID ) punch ON datt.EMPLOYEEID = punch.EMPLOYEEID AND datt.ATTENDANCEDATE = punch.ATTENDANCEDATE LEFT JOIN TPOSTINGPOSITION post ON datt.POSTINGPOSITIONID = post.POSTINGPOSITIONID LEFT JOIN MPARTYBRANCH pb ON pb.PARTYBRANCHID = post.PARTYBRANCHID, MEMPLOYEE emp, MSHIFT shift, MSHIFT pshift, MGCM wktype, MGCM staff, MGCM grade, MGCM desg, MGCM emt, MDEPARTMENT dept, MLEAVE leave, MVILLAGE village, MCOSTCENTER ct, MEMPLOYEE rpt, MCODEDEFINE define, MLOCATION location, MORGANIZATIONUNIT ouunit WHERE datt.EMPLOYEEID = emp.EMPLOYEEID AND datt.SHIFTID = shift.SHIFTID AND datt.PREVIOUSSHIFTID = pshift.SHIFTID AND datt.WORKTYPEID = wktype.GCMID AND datt.LEAVEID = leave.LEAVEID AND emp.VILLAGEID = village.VILLAGEID AND emp.SALARYGRADEID = grade.GCMID AND emp.DESIGNATIONID = desg.GCMID AND emp.STAFFTYPEID = staff.GCMID AND emp.EMPLOYEETYPEID = emt.GCMID AND emp.CODEDEFINEID = define.CODEDEFINEID AND emp.LOCATIONID = location.LOCATIONID AND emp.COSTCENTERID = ct.COSTCENTERID AND emp.REPORTINGTOEMPLOYEEID = rpt.EMPLOYEEID AND datt.DEPARTMENTID = dept.DEPARTMENTID AND emp.WORKOUID = ouunit.OUID AND emp.PUNCHTYPE <> 0 :employeecount AND ( emp.EMPLOYEESTATUS IN (0) OR ( CONVERT(DATE, emp.DATEOFRESIGNATION) BETWEEN ':periodfrom' AND ':periodto' OR CONVERT(DATE, emp.DATEOFRESIGNATION) >= ':periodto' ) ); "; public const string GET_DAILYATTENDANCEREPORT_PAYPERIOD_TEMP = @" SELECT p.PAYPERIODID AS PayPeriodId, p.PAYPERIODCODE AS PayPeriodCode, p.FROMDATE AS FromDate, p.TODATE AS ToDate INTO #temp FROM MPAYPERIOD p WHERE 1 = 1 "; public const string GET_DAILYATTENDANCEREPORT_PAYPERIOD_BASED = @" SELECT datt.DAILYATTENDANCEID AS DailyAttendanceId, datt.ATTENDANCEDATE AS DailyAttendanceAttendanceDate, datt.REMARKS AS Remarks, datt.EMPLOYEEID AS EmployeeId, emp.EMPLOYEECODE AS EmployeeCode, emp.EMPLOYEENAME AS EmployeeName, emp.SEX AS EmployeeSex, CASE WHEN emp.SEX IN (0) THEN 'M' ELSE 'F' END AS SexName, emp.SORTORDER AS SortOrder, emp.DATEOFJOINING AS DOJ, datt.SHIFTID AS ShiftId, shift.STARTTIME AS ShiftStartTime, shift.ENDTIME AS ShiftEndTime, shift.SHIFTCODE AS ShiftCode, shift.SHIFTDESCRIPTION AS ShiftDescription, pshift.SHIFTCODE AS PreviousShiftCode, pshift.SHIFTDESCRIPTION AS PreviousShiftDescription, datt.PREVIOUSDAYTYPE AS PreviousDayType, datt.DAYTYPE AS DailyAttendanceDayType, datt.TEXTDAYTYPE AS DailyAttendanceTextDayType, CASE WHEN datt.DAYTYPE IN (0) THEN 'F' WHEN datt.DAYTYPE IN (1) THEN 'HW-HA' WHEN datt.DAYTYPE IN (2) THEN 'WOFF' WHEN datt.DAYTYPE IN (3) THEN 'O' WHEN datt.DAYTYPE IN (4) THEN 'L' WHEN datt.DAYTYPE IN (5) THEN 'HW-HL' WHEN datt.DAYTYPE IN (6) THEN 'CW' WHEN datt.DAYTYPE IN (7) THEN 'COFF' WHEN datt.DAYTYPE IN (8) THEN 'A' WHEN datt.DAYTYPE IN (9) THEN 'H' WHEN datt.DAYTYPE IN (10) THEN 'HL-HA' WHEN datt.DAYTYPE IN (11) THEN 'CHW-HL' WHEN datt.DAYTYPE IN (12) THEN 'CHW-HA' WHEN datt.DAYTYPE IN (13) THEN 'RSND' ELSE '' END AS DayType, datt.LEAVEID AS LeaveId, leave.LEAVECODE AS LeaveCode, leave.LEAVENAME AS LeaveName, CASE WHEN datt.INTIME - shift.STARTTIME > 0 THEN CONVERT(NUMERIC(18,2), datt.INTIME / 60 + (datt.INTIME % 60) / 60.0) ELSE 0 END AS LateInTime, CAST(DAY(datt.ATTENDANCEDATE) AS VARCHAR) AS Day, ISNULL(late.NoofTime, 0) AS NoofTimes, ISNULL(late.TotalMinutes, 0) AS TotalMinutes, :hourreplace datt.INTIME AS DailyAttendanceInTime, CASE WHEN datt.OUTTIME = datt.INTIME THEN 0 ELSE datt.OUTTIME END AS DailyAttendanceOutTime, datt.WORKEDHOURS AS DailyAttendanceWorkedHours, datt.DEPARTMENTID AS DepartmentId, dept.DEPARTMENTCODE AS DepartmentCode, dept.DEPARTMENTNAME AS DepartmentName, datt.WORKTYPEID AS WorkTypeId, wktype.GCMCODE AS WorkTypeCode, wktype.GCMNAME AS WorkTypeName, emp.EMPLOYEETYPEID AS EmployeeTypeId, emt.GCMCODE AS EmployeeTypeCode, emt.GCMNAME AS EmployeeTypeName, emp.SALARYGRADEID AS SalaryGradeId, grade.GCMCODE AS SalaryGradeCode, grade.GCMNAME AS SalaryGradeName, emp.DESIGNATIONID AS DesignationId, desg.GCMCODE AS DesignationCode, desg.GCMNAME AS DesignationName, staff.GCMNAME AS Batch, emp.VILLAGEID AS VillageId, village.VILLAGECODE AS VillageCode, village.VILLAGENAME AS VillageName, datt.EARLYIN AS DailyAttendanceEarlyIn, datt.EARLYOUT AS DailyAttendanceEarlyOut, datt.SHORTHOURS AS DailyAttendanceShortHours, datt.EXTRAHOURS AS DailyAttendanceExtraHours, datt.OTHOURS AS DailyAttendanceOtHours, location.LOCATIONNAME AS DailyAttendanceLocation, datt.BREAKTIME AS BreakTime, dept.DEPARTMENTHEADID AS DepartmentHeadId, dpthead.EMPLOYEECODE AS HeadDepartmentCode, dpthead.EMPLOYEENAME AS HeadDepartmentName, dpthead.PRIMARYEMAILID AS HeadMailId FROM TDAILYATTENDANCE datt :innerreplace :outerreplace , MEMPLOYEE emp , MSHIFT shift , MGCM wktype , MDEPARTMENT dept , MLEAVE leave , MVILLAGE village , MGCM grade , MGCM desg , MGCM staff , MGCM emt , MCODEDEFINE define , MLOCATION location , MSHIFT pshift , MEMPLOYEE dpthead , #temp temp WHERE 1 = 1 AND datt.EMPLOYEEID = emp.EMPLOYEEID AND datt.SHIFTID = shift.SHIFTID AND datt.WORKTYPEID = wktype.GCMID AND datt.LEAVEID = leave.LEAVEID AND emp.VILLAGEID = village.VILLAGEID AND emp.SALARYGRADEID = grade.GCMID AND emp.DESIGNATIONID = desg.GCMID AND emp.STAFFTYPEID = staff.GCMID AND emp.EMPLOYEETYPEID = emt.GCMID AND emp.CODEDEFINEID = define.CODEDEFINEID AND emp.LOCATIONID = location.LOCATIONID AND datt.PREVIOUSSHIFTID = pshift.SHIFTID AND datt.DEPARTMENTID = dept.DEPARTMENTID AND dpthead.EMPLOYEEID = dept.DEPARTMENTHEADID AND temp.FROMDATE <= datt.ATTENDANCEDATE AND temp.TODATE >= datt.ATTENDANCEDATE AND emp.PUNCHTYPE <> 0 :employeecount AND ( emp.EMPLOYEESTATUS IN (0) OR ( CONVERT(DATE, emp.DATEOFRESIGNATION) >= temp.FROMDATE AND CONVERT(DATE, emp.DATEOFRESIGNATION) <= temp.TODATE ) OR CONVERT(DATE, emp.DATEOFRESIGNATION) >= temp.TODATE ); "; public const string GET_DAILYATTENDANCEREPORT_COUNT = @" SELECT COUNT(datt.EMPLOYEEID) AS EmployeeId FROM TDAILYATTENDANCE datt :innerreplace :outerreplace , MEMPLOYEE emp , TMMDETAIL detail , TMMHEAD head , MCODEDEFINE define , MSHIFT shift WHERE 1 = 1 AND 1 = 1 AND datt.EMPLOYEEID = emp.EMPLOYEEID AND datt.SHIFTID = shift.SHIFTID AND datt.MMDETAILID = detail.DOCUMENTDETAILID AND head.DOCUMENTID = detail.DOCUMENTID AND emp.CODEDEFINEID = define.CODEDEFINEID AND emp.PUNCHTYPE <> 0 :employeecount AND ( emp.EMPLOYEESTATUS IN (0) OR ( CONVERT(DATE, emp.DATEOFRESIGNATION) >= ':periodfrom' AND CONVERT(DATE, emp.DATEOFRESIGNATION) <= ':periodto' ) OR CONVERT(DATE, emp.DATEOFRESIGNATION) >= ':periodto' ); "; // ===================================================================== // Ported from GB4 DailyAttendanceQueryBuilder for LoadDailyAttendance // (punch-based multi-employee attendance loading), used by // DailyAttendanceDAL.LoadDailyAttendanceFromPunch. // Placeholders (:xxx) are resolved via string.Replace in the DAL, // matching the existing GET_DAILYATTENDANCEREPORT* pattern in this class. // ===================================================================== public const string LOAD_DAILY_ATTENDANCE_FROM_PUNCH_COUNT = "select COUNT(*) from TDAILYATTENDANCE a,MEMPLOYEE b where ATTENDANCEDATE=':attendancedate' and a.EMPLOYEEID=b.EMPLOYEEID and b.WORKOUID=:ouid"; // Loads the site-based punch temp table (#TEMP) for employees whose // attendance is tracked via site/party-branch punches rather than the // standard shift punch flow. Must run before GET_MMDETAIL_AND_POSTINGPOSITIONID. public const string GET_PUNCH_BASED_ON_SITE = @" SELECT PUNCH.ATTENDANCEDATE, PUNCH.EMPLOYEEID, PUNCH.SITEID, CASE WHEN ( CONVERT(DATE, ':attendancedate', 111) = Min(CONVERT(DATE, PUNCH.PUNCHDATE, 111)) AND ( CONVERT(DATE, ':attendancedate', 111) = Max(CONVERT(DATE, PUNCH.PUNCHDATE, 111)) ) ) THEN Min(PUNCH.PUNCHTIME) ELSE CASE WHEN ( CONVERT(DATE, ':attendancedate', 111) = Min(CONVERT(DATE, PUNCH.PUNCHDATE, 111)) AND ( CONVERT(DATE, ':attendancedate', 111) < Max(CONVERT(DATE, PUNCH.PUNCHDATE, 111)) ) ) THEN Min(CASE WHEN CONVERT(DATE, PUNCH.PUNCHDATE, 111) = ':attendancedate' THEN PUNCH.PUNCHTIME END) ELSE Min(PUNCH.PUNCHTIME) END END AS INTIME, CASE WHEN ( CONVERT(DATE, ':attendancedate', 111) = Min(CONVERT(DATE, PUNCH.PUNCHDATE, 111)) AND ( CONVERT(DATE, ':attendancedate', 111) = Max(CONVERT(DATE, PUNCH.PUNCHDATE, 111)) ) ) THEN Max(PUNCH.PUNCHTIME) ELSE CASE WHEN ( CONVERT(DATE, ':attendancedate', 111) = Min(CONVERT(DATE, PUNCH.PUNCHDATE, 111)) AND ( CONVERT(DATE, ':attendancedate', 111) < Max(CONVERT(DATE, PUNCH.PUNCHDATE, 111)) ) ) THEN Max(CASE WHEN CONVERT(DATE, PUNCH.PUNCHDATE, 111) > ':attendancedate' THEN PUNCH.PUNCHTIME END) ELSE Max(PUNCH.PUNCHTIME) END END AS OUTTIME INTO #TEMP FROM TPUNCH PUNCH, MSHIFT SHIFT, MEMPLOYEE EMP WHERE PUNCH.EMPLOYEEID = EMP.EMPLOYEEID AND PUNCH.ATTENDANCEDATE = ':attendancedate' AND EMP.SHIFTID = SHIFT.SHIFTID AND PUNCH.PUNCHTYPE = 2--Site Based AND PUNCH.SITETYPE = 1--PartyBranch AND PUNCH.SITEID <> -1 AND EMP.WORKOUID = case when :ouid=-2100000000 then emp.WORKOUID else :ouid end AND EMP.ISPOSTINGREQUIRED=0--yes GROUP BY PUNCH.ATTENDANCEDATE, PUNCH.EMPLOYEEID, PUNCH.SITEID"; // Joins #TEMP (site punches) against TPOSTINGPOSITION to resolve // MMDETAILID / POSTINGPOSITIONID / WORKTYPEID per employee posting, // producing #TEMP1. Must run after GET_PUNCH_BASED_ON_SITE. public const string GET_MMDETAIL_AND_POSTINGPOSITIONID = @" SELECT A.*, B.MMDETAILID, B.POSTINGPOSITIONID,B.WORKTYPEID INTO #TEMP1 FROM #TEMP A, TPOSTINGPOSITION B WHERE A.EMPLOYEEID = B.EMPLOYEEID AND A.SITEID = B.PARTYBRANCHID AND B.POSTINGSTATUS NOT IN( 3, 5 )---0 PLANNED,1-APPROVED,2-INPROGRESS,3-CLOSED,4-HOLD,5-DROP AND A.ATTENDANCEDATE BETWEEN B.FROMDATE AND B.TODATE ORDER BY A.INTIME"; // Main multi-employee daily attendance load, built for a single // :attendancedate across all employees matching :replacefields / // :innerreplacefields (built dynamically in the DAL from // DailyAttendanceFilterDTO). Writes to #tempdaily via :replacetemptable // (" into #tempdaily") when doing a full load, or is used unmodified // for row-by-row evaluation. Requires #TEMP1 from GET_MMDETAIL_AND_POSTINGPOSITIONID // to already exist in the same session/connection. public const string LOAD_DAILY_ATTENDANCE_FROM_PUNCH = @" select case when dailyatt.DAILYATTENDANCEID Is not null then dailyatt.DAILYATTENDANCEID else 0 end as DailyAttendanceId, CONVERT( date, ':attendancedate' , 111) as DailyAttendanceAttendanceDate, dailyatt.PREVIOUSDAYTYPE as DailyAttendancePreviousDayType, emp.EmployeeId as EmployeeId, emp.EMPLOYEECODE as EmployeeCode, emp.EMPLOYEENAME as EmployeeName, case when shifdailyatt.shiftid is null then shiftemp.SHIFTID else shifdailyatt.shiftid end as ShiftId, case when shifdailyatt.shiftid is null then shiftemp.ShiftCode else shifdailyatt.ShiftCode end as ShiftCode, case when shifdailyatt.shiftid is null then shiftemp.ShiftDescription else shifdailyatt.ShiftDescription end as ShiftDescription, case when (dailyatt.IsManualDayType=0 ) then dailyatt.DAYTYPE else CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN (subquery.EMPLOYEEID IS not null and leavefields.EMPLOYEEID IS null and timeslip.EMPLOYEEID is null) THEN 0 ELSE ( CASE WHEN dailyatt.PREVIOUSDAYTYPE IN( 4, 5 ) THEN dailyatt.PREVIOUSDAYTYPE ELSE CASE WHEN leavefields.EMPLOYEEID IS NOT NULL THEN case when dailyatt.PREVIOUSDAYTYPE in (2,9) then case leavefields.LEAVETYPE when 0 then 0 --WorkingDay when 1 then case when leavefields.NUMBEROFDAYS>=1 then 4 else 11 end --PaidLeave when 2 then case when leavefields.NUMBEROFDAYS>=1 then 8 else 12 end --LOP when 3 then 2 --WeeklyOff when 4 then 4 --Others when 5 then 4--GroupBased when 6 then 9--Holiday when 7 then case when leavefields.NUMBEROFDAYS>=1 then 6 else 11 end--Compensatory Work when 8 then case when leavefields.NUMBEROFDAYS>=1 then 7 else 12 end--Compensatoy Off else 4 end else case leavefields.LEAVETYPE when 0 then 0 when 1 then case when leavefields.NUMBEROFDAYS>=1 then 4 else 5 end when 2 then case when leavefields.NUMBEROFDAYS>=1 then 8 else case when ISNULL(subquery.INTIME,0)=ISNULL(subquery.OUTTIME,0) then 8 else 1 end end when 3 then 2 when 4 then 4 --Others when 5 then 4--GroupBased when 6 then 9--Holiday when 7 then case when leavefields.NUMBEROFDAYS>=1 then 6 else 11 end--Compensatory Work when 8 then case when leavefields.NUMBEROFDAYS>=1 then 7 else 12 end--Compensatoy Off else 4 end end ELSE CASE WHEN timeslip.employeeid IS NOT NULL THEN 0 ELSE case when dailyatt.PREVIOUSDAYTYPE in (2,9) then dailyatt.PREVIOUSDAYTYPE else 8 end END END END ) end ELSE CASE WHEN leavefields.EMPLOYEEID IS NOT NULL THEN ( case when dailyatt.PREVIOUSDAYTYPE in (2,9) then (case leavefields.LEAVETYPE when 0 then 0 when 1 then case when leavefields.NUMBEROFDAYS>=1 then 4 else 11 end when 2 then case when leavefields.NUMBEROFDAYS>=1 then 8 else 12 end when 3 then 2 else 4 end ) else case leavefields.LEAVETYPE when 0 then 0 when 1 then case when leavefields.NUMBEROFDAYS>=1 then 4 else 5 end when 2 then case when leavefields.NUMBEROFDAYS>=1 then 8 else 1 end when 3 then 2 else 4 end end ) ELSE CASE WHEN timeslip.Employeeid IS NOT NULL THEN 0 ELSE case when dailyatt.PREVIOUSDAYTYPE in (2,9) then dailyatt.DayType else 0 end END END END END as DailyAttendanceDayType, case when (case when (dailyatt.IsManualDayType=0 ) then dailyatt.DAYTYPE else CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN (subquery.EMPLOYEEID IS not null and leavefields.EMPLOYEEID IS null and timeslip.EMPLOYEEID is null) THEN 0 ELSE ( CASE WHEN dailyatt.PREVIOUSDAYTYPE IN( 4, 5 ) THEN dailyatt.PREVIOUSDAYTYPE ELSE CASE WHEN leavefields.EMPLOYEEID IS NOT NULL THEN (case when dailyatt.PREVIOUSDAYTYPE in (2,9) then (case leavefields.LEAVETYPE when 0 then 0 when 1 then case when leavefields.NUMBEROFDAYS>=1 then 4 else 11 end when 2 then case when leavefields.NUMBEROFDAYS>=1 then 8 else 12 end when 3 then 2 else 4 end ) else case leavefields.LEAVETYPE when 0 then 0 when 1 then case when leavefields.NUMBEROFDAYS>=1 then 4 else 5 end when 2 then case when leavefields.NUMBEROFDAYS>=1 then 8 else case when subquery.INTIME=subquery.OUTTIME then 8 else 1 end end when 3 then 2 else 4 end end ) ELSE CASE WHEN timeslip.employeeid IS NOT NULL THEN 0 ELSE case when dailyatt.PREVIOUSDAYTYPE in (2,9) then dailyatt.PREVIOUSDAYTYPE else 8 end END END END ) end ELSE CASE WHEN leavefields.EMPLOYEEID IS NOT NULL THEN (case when dailyatt.PREVIOUSDAYTYPE in (2,9) then (case leavefields.LEAVETYPE when 0 then 0 when 1 then case when leavefields.NUMBEROFDAYS>=1 then 4 else 11 end when 2 then case when leavefields.NUMBEROFDAYS>=1 then 8 else 12 end when 3 then 2 else 4 end ) else case leavefields.LEAVETYPE when 0 then 0 when 1 then case when leavefields.NUMBEROFDAYS>=1 then 4 else 5 end when 2 then case when leavefields.NUMBEROFDAYS=1 then 8 else 1 end when 3 then 2 else 4 end end ) ELSE CASE WHEN timeslip.Employeeid IS NOT NULL THEN 0 ELSE 0 END END END END ) in(1,8) then case when dailyatt.ISMANUALDAYTYPE=0 then dailyatt.LEAVEID else ISNULL(leavefields.LEAVETYPEID ,absentLeave.LeaveId ) end else case when (dailyatt.Intime is null or dailyatt.Intime Is not null ) then (case when leavefields.LEAVETYPEID IS not null then leavefields.LEAVETYPEID else case WHEN timeslip.employeeid IS NOT NULL THEN -1 else case when subquery.EMPLOYEEID IS not null then -1 else case when dailyatt.PREVIOUSDAYTYPE in (2,9) then -1 else dailyatt.LEAVEID end end end end) else -1 end end as LeaveId, case when (case when (dailyatt.IsManualDayType=0 ) then dailyatt.DAYTYPE else CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN subquery.EMPLOYEEID IS NOT NULL THEN 0 ELSE ( CASE WHEN dailyatt.PREVIOUSDAYTYPE IN( 4, 5 ) THEN dailyatt.PREVIOUSDAYTYPE ELSE CASE WHEN leavefields.EMPLOYEEID IS NOT NULL THEN ( case when dailyatt.PREVIOUSDAYTYPE in (2,9) then (case leavefields.LEAVETYPE when 0 then 0 when 1 then case when leavefields.NUMBEROFDAYS>=1 then 4 else 11 end when 2 then case when leavefields.NUMBEROFDAYS>=1 then 8 else 12 end when 3 then 2 else 4 end ) else case leavefields.LEAVETYPE when 0 then 0 when 1 then case when leavefields.NUMBEROFDAYS>=1 then 4 else 5 end when 2 then case when leavefields.NUMBEROFDAYS>=1 then 8 else 1 end when 3 then 2 else 4 end end ) ELSE CASE WHEN timeslip.employeeid IS NOT NULL THEN 0 ELSE case when dailyatt.PREVIOUSDAYTYPE in (2,9) then dailyatt.PREVIOUSDAYTYPE else 8 end END END END ) end ELSE CASE WHEN leavefields.EMPLOYEEID IS NOT NULL THEN ( case when dailyatt.PREVIOUSDAYTYPE in (2,9) then (case leavefields.LEAVETYPE when 0 then 0 when 1 then case when leavefields.NUMBEROFDAYS>=1 then 4 else 11 end when 2 then case when leavefields.NUMBEROFDAYS>=1 then 8 else 12 end when 3 then 2 else 4 end ) else case leavefields.LEAVETYPE when 0 then 0 when 1 then case when leavefields.NUMBEROFDAYS>=1 then 4 else 5 end when 2 then case when leavefields.NUMBEROFDAYS>=1 then 8 else 1 end when 3 then 2 else 4 end end ) ELSE CASE WHEN timeslip.Employeeid IS NOT NULL THEN 0 ELSE 0 END END END END ) in(1,8) then case when dailyatt.ISMANUALDAYTYPE=0 then leave.LeaveCode else ISNULL(leavefields.LeaveCode ,absentLeave.LeaveCode ) end else case when (dailyatt.Intime is null or dailyatt.Intime Is not null ) then (case when leavefields.LEAVETYPEID IS not null then FinalLeave.LeaveCode else case WHEN timeslip.employeeid IS NOT NULL THEN 'NONE' else case when subquery.EMPLOYEEID IS not null then 'NONE' else case when dailyatt.PREVIOUSDAYTYPE in (2,9) then 'NONE' else leave.LeaveCode end end end end) else 'NONE' end end as LeaveCode, case when (case when (dailyatt.IsManualDayType=0 ) then dailyatt.DAYTYPE else CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN subquery.EMPLOYEEID IS NOT NULL THEN 0 ELSE ( CASE WHEN dailyatt.PREVIOUSDAYTYPE IN( 4, 5 ) THEN dailyatt.PREVIOUSDAYTYPE ELSE CASE WHEN leavefields.EMPLOYEEID IS NOT NULL THEN ( case when dailyatt.PREVIOUSDAYTYPE in (2,9) then (case leavefields.LEAVETYPE when 0 then 0 when 1 then case when leavefields.NUMBEROFDAYS>=1 then 4 else 11 end when 2 then case when leavefields.NUMBEROFDAYS>=1 then 8 else 12 end when 3 then 2 else 4 end ) else case leavefields.LEAVETYPE when 0 then 0 when 1 then case when leavefields.NUMBEROFDAYS>=1 then 4 else 5 end when 2 then case when leavefields.NUMBEROFDAYS>=1 then 8 else 1 end when 3 then 2 else 4 end end ) ELSE CASE WHEN timeslip.employeeid IS NOT NULL THEN 0 ELSE 8 END END END ) end ELSE CASE WHEN leavefields.EMPLOYEEID IS NOT NULL THEN ( case when dailyatt.PREVIOUSDAYTYPE in (2,9) then (case leavefields.LEAVETYPE when 0 then 0 when 1 then case when leavefields.NUMBEROFDAYS>=1 then 4 else 11 end when 2 then case when leavefields.NUMBEROFDAYS>=1 then 8 else 12 end when 3 then 2 else 4 end ) else case leavefields.LEAVETYPE when 0 then 0 when 1 then case when leavefields.NUMBEROFDAYS>=1 then 4 else 5 end when 2 then case when leavefields.NUMBEROFDAYS>=1 then 8 else 1 end when 3 then 2 else 4 end end ) ELSE CASE WHEN timeslip.Employeeid IS NOT NULL THEN 0 ELSE 0 END END END END ) in(1,8) then case when dailyatt.ISMANUALDAYTYPE=0 then leave.LeaveName else ISNULL(leavefields.LeaveName ,absentLeave.LeaveName) end else case when (dailyatt.Intime is null or dailyatt.Intime Is not null ) then (case when leavefields.LEAVETYPEID IS not null then FinalLeave.LeaveName else case WHEN timeslip.employeeid IS NOT NULL THEN 'NONE' else case when subquery.EMPLOYEEID IS not null then 'NONE' else case when dailyatt.PREVIOUSDAYTYPE in (2,9) then 'NONE' else leave.LeaveName end end end end) else 'NONE' end end as LeaveName, CASE WHEN dailyatt.IsManualInTime = 0 THEN dailyatt.INTIME ELSE CASE WHEN emp.PUNCHTYPE <> -1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.INTIME ELSE dailyatt.INTIME END ELSE dailyatt.INTIME END END as DailyAttendanceInTime, CASE WHEN dailyatt.IsManualOutTime = 0 THEN dailyatt.OUTTIME ELSE CASE WHEN emp.PUNCHTYPE <> -1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.OUTTIME ELSE dailyatt.OUTTIME END ELSE dailyatt.OUTTIME END END as DailyAttendanceOutTime, case when ( CASE WHEN dailyatt.IsManualOutTime = 0 THEN dailyatt.OUTTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.OUTTIME ELSE dailyatt.OUTTIME END ELSE dailyatt.OUTTIME END END - CASE WHEN dailyatt.IsManualInTime = 0 THEN dailyatt.INTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.INTIME ELSE dailyatt.INTIME END ELSE dailyatt.INTIME END END )>=0 then ( CASE WHEN dailyatt.IsManualOutTime = 0 THEN dailyatt.OUTTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.OUTTIME ELSE dailyatt.OUTTIME END ELSE dailyatt.OUTTIME END END - CASE WHEN dailyatt.IsManualInTime = 0 THEN dailyatt.INTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.INTIME ELSE dailyatt.INTIME END ELSE dailyatt.INTIME END END ) else ( CASE WHEN dailyatt.IsManualOutTime = 0 THEN dailyatt.OUTTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.OUTTIME ELSE dailyatt.OUTTIME END ELSE dailyatt.OUTTIME END END - CASE WHEN dailyatt.IsManualInTime = 0 THEN dailyatt.INTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.INTIME ELSE dailyatt.INTIME END ELSE dailyatt.INTIME END END )+1440 end as DailyAttendanceWorkedHours, case when (shifdailyatt.starttime - CASE WHEN dailyatt.IsManualInTime = 0 THEN dailyatt.INTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.INTIME ELSE dailyatt.INTIME END ELSE dailyatt.INTIME END END )>=0 then (shifdailyatt.starttime - CASE WHEN dailyatt.IsManualInTime = 0 THEN dailyatt.INTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.INTIME ELSE dailyatt.INTIME END ELSE dailyatt.INTIME END END ) else 0 end as DailyAttendanceEarlyIn, case when (shifdailyatt.endtime - CASE WHEN dailyatt.IsManualOutTime = 0 THEN dailyatt.OUTTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.OUTTIME ELSE dailyatt.OUTTIME END ELSE dailyatt.OUTTIME END END )>=0 then (shifdailyatt.endtime - CASE WHEN dailyatt.IsManualOutTime = 0 THEN dailyatt.OUTTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.OUTTIME ELSE dailyatt.OUTTIME END ELSE dailyatt.OUTTIME END END ) else 0 end as DailyAttendanceEarlyOut, case when (shifdailyatt.SHIFTMINUTES- case when ( CASE WHEN dailyatt.IsManualOutTime = 0 THEN dailyatt.OUTTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.OUTTIME ELSE dailyatt.OUTTIME END ELSE dailyatt.OUTTIME END END - CASE WHEN dailyatt.IsManualInTime = 0 THEN dailyatt.INTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.INTIME ELSE dailyatt.INTIME END ELSE dailyatt.INTIME END END )>=0 then ( CASE WHEN dailyatt.IsManualOutTime = 0 THEN dailyatt.OUTTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.OUTTIME ELSE dailyatt.OUTTIME END ELSE dailyatt.OUTTIME END END - CASE WHEN dailyatt.IsManualInTime = 0 THEN dailyatt.INTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.INTIME ELSE dailyatt.INTIME END ELSE dailyatt.INTIME END END ) else ( CASE WHEN dailyatt.IsManualOutTime = 0 THEN dailyatt.OUTTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.OUTTIME ELSE dailyatt.OUTTIME END ELSE dailyatt.OUTTIME END END - CASE WHEN dailyatt.IsManualInTime = 0 THEN dailyatt.INTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.INTIME ELSE dailyatt.INTIME END ELSE dailyatt.INTIME END END )+1440 end)>=0 then (shifdailyatt.SHIFTMINUTES- case when ( CASE WHEN dailyatt.IsManualOutTime = 0 THEN dailyatt.OUTTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.OUTTIME ELSE dailyatt.OUTTIME END ELSE dailyatt.OUTTIME END END - CASE WHEN dailyatt.IsManualInTime = 0 THEN dailyatt.INTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.INTIME ELSE dailyatt.INTIME END ELSE dailyatt.INTIME END END )>=0 then ( CASE WHEN dailyatt.IsManualOutTime = 0 THEN dailyatt.OUTTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.OUTTIME ELSE dailyatt.OUTTIME END ELSE dailyatt.OUTTIME END END - CASE WHEN dailyatt.IsManualInTime = 0 THEN dailyatt.INTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.INTIME ELSE dailyatt.INTIME END ELSE dailyatt.INTIME END END ) else ( CASE WHEN dailyatt.IsManualOutTime = 0 THEN dailyatt.OUTTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.OUTTIME ELSE dailyatt.OUTTIME END ELSE dailyatt.OUTTIME END END - CASE WHEN dailyatt.IsManualInTime = 0 THEN dailyatt.INTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.INTIME ELSE dailyatt.INTIME END ELSE dailyatt.INTIME END END )+1440 end) else 0 end as DailyAttendanceShortHours, case when (shifdailyatt.SHIFTMINUTES- case when ( CASE WHEN dailyatt.IsManualOutTime = 0 THEN dailyatt.OUTTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.OUTTIME ELSE dailyatt.OUTTIME END ELSE dailyatt.OUTTIME END END - CASE WHEN dailyatt.IsManualInTime = 0 THEN dailyatt.INTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.INTIME ELSE dailyatt.INTIME END ELSE dailyatt.INTIME END END )>=0 then ( CASE WHEN dailyatt.IsManualOutTime = 0 THEN dailyatt.OUTTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.OUTTIME ELSE dailyatt.OUTTIME END ELSE dailyatt.OUTTIME END END - CASE WHEN dailyatt.IsManualInTime = 0 THEN dailyatt.INTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.INTIME ELSE dailyatt.INTIME END ELSE dailyatt.INTIME END END ) else ( CASE WHEN dailyatt.IsManualOutTime = 0 THEN dailyatt.OUTTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.OUTTIME ELSE dailyatt.OUTTIME END ELSE dailyatt.OUTTIME END END - CASE WHEN dailyatt.IsManualInTime = 0 THEN dailyatt.INTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.INTIME ELSE dailyatt.INTIME END ELSE dailyatt.INTIME END END )+1440 end)<=0 then abs(shifdailyatt.SHIFTMINUTES- case when ( CASE WHEN dailyatt.IsManualOutTime = 0 THEN dailyatt.OUTTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.OUTTIME ELSE dailyatt.OUTTIME END ELSE dailyatt.OUTTIME END END - CASE WHEN dailyatt.IsManualInTime = 0 THEN dailyatt.INTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.INTIME ELSE dailyatt.INTIME END ELSE dailyatt.INTIME END END )>=0 then ( CASE WHEN dailyatt.IsManualOutTime = 0 THEN dailyatt.OUTTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.OUTTIME ELSE dailyatt.OUTTIME END ELSE dailyatt.OUTTIME END END - CASE WHEN dailyatt.IsManualInTime = 0 THEN dailyatt.INTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.INTIME ELSE dailyatt.INTIME END ELSE dailyatt.INTIME END END ) else ( CASE WHEN dailyatt.IsManualOutTime = 0 THEN dailyatt.OUTTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.OUTTIME ELSE dailyatt.OUTTIME END ELSE dailyatt.OUTTIME END END - CASE WHEN dailyatt.IsManualInTime = 0 THEN dailyatt.INTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.INTIME ELSE dailyatt.INTIME END ELSE dailyatt.INTIME END END )+1440 end) else 0 end DailyAttendanceExtraHours, isnull(OverTime.OverTimeDuration,0) as DailyAttendanceOtHours, isnull(OverTime.OTFACTOR,0) as DailyAttendanceOtFactorHours, dailyatt.Remarks as DailyAttendanceRemarks, dailyatt.Version as DailyAttendanceVersion, case when shifdailyatt.shiftid is null then shiftemp.SHIFTMINUTES else shifdailyatt.SHIFTMINUTES end as DailyAttendanceShiftMins, isnull(timeslip.BusHrs,0) as DailyAttendanceBusHours, isnull(timeslip.PerHrs,0) as DailyAttendancePerHours, emp.DEPARTMENTID as DepartmentId, dept.DEPARTMENTCode as DepartmentCode, dept.DEPARTMENTNAME as DepartmentName, gcm.gcmcode as WorkTypeCode, gcm.gcmid as WorkTypeId, gcm.gcmname as WorkTypeName, location.LocationName as DailyAttendanceLocation , case when (shifdailyatt.starttime - CASE WHEN dailyatt.IsManualInTime = 0 THEN dailyatt.INTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.INTIME ELSE dailyatt.INTIME END ELSE dailyatt.INTIME END END )<=0 then abs(shifdailyatt.starttime - CASE WHEN dailyatt.IsManualInTime = 0 THEN dailyatt.INTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.INTIME ELSE dailyatt.INTIME END ELSE dailyatt.INTIME END END ) else 0 end as DailyAttendanceLateIn, case when (shifdailyatt.endtime - CASE WHEN dailyatt.IsManualOutTime = 0 THEN dailyatt.OUTTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.OUTTIME ELSE dailyatt.OUTTIME END ELSE dailyatt.OUTTIME END END )<=0 then abs(shifdailyatt.endtime - CASE WHEN dailyatt.IsManualOutTime = 0 THEN dailyatt.OUTTIME ELSE CASE WHEN emp.PUNCHTYPE = 1 THEN CASE WHEN ( subquery.ATTENDANCEDATE = ':attendancedate' ) THEN subquery.OUTTIME ELSE dailyatt.OUTTIME END ELSE dailyatt.OUTTIME END END ) else 0 end as DailyAttendanceLateOut, case when dailyatt.ReasonId is null then -1 else reason.reasonId end as ReasonId, case when dailyatt.ReasonId is null then 'None' else reason.ReasonCode end as ReasonCode, case when dailyatt.ReasonId is null then 'None' else reason.ReasonName end as ReasonName ,dailyatt.IsManualInTime as DailyAttendanceIsManualInTime, dailyatt.IsManualOutTime as DailyAttendanceIsManualOutTime, dailyatt.IsManualDayType as DailyAttendanceIsManualDayType :replacetemptable from Mleave FinalLeave, MGCM gcm,MDepartment dept, Memployee emp left outer join TDAILYATTENDANCE dailyatt on ( dailyatt.EmployeeId=emp.EmployeeId and CAST (dailyatt.ATTENDANCEDATE as DATE) =':attendancedate' ) left outer join (SELECT punch.attendancedate, punch.EmployeeId, CASE WHEN ( CONVERT(DATE, ':attendancedate', 111) = Min(CONVERT(DATE, punch.PUNCHDATE, 111)) AND ( CONVERT(DATE, ':attendancedate', 111) = Max(CONVERT(DATE, punch.PUNCHDATE, 111)) ) ) THEN Min(punch.PUNCHTIME) ELSE CASE WHEN ( CONVERT(DATE, ':attendancedate', 111) = Min(CONVERT(DATE, punch.PUNCHDATE, 111)) AND ( CONVERT(DATE, ':attendancedate', 111) < Max(CONVERT(DATE, punch.PUNCHDATE, 111)) ) ) THEN Min(CASE WHEN CONVERT(DATE, punch.PUNCHDATE, 111) = ':attendancedate' THEN punch.PUNCHTIME END) ELSE Min(punch.PUNCHTIME) END END AS INTIME, CASE WHEN ( CONVERT(DATE, ':attendancedate', 111) = Min(CONVERT(DATE, punch.PUNCHDATE, 111)) AND ( CONVERT(DATE, ':attendancedate', 111) = Max(CONVERT(DATE, punch.PUNCHDATE, 111)) ) ) THEN Max(punch.PUNCHTIME) ELSE CASE WHEN ( CONVERT(DATE, ':attendancedate', 111) = Min(CONVERT(DATE, punch.PUNCHDATE, 111)) AND ( CONVERT(DATE, ':attendancedate', 111) < Max(CONVERT(DATE, punch.PUNCHDATE, 111)) ) ) THEN Max(CASE WHEN CONVERT(DATE, punch.PUNCHDATE, 111) > ':attendancedate' THEN punch.PUNCHTIME END) ELSE Max(punch.PUNCHTIME) END END AS OUTTIME,case when MAX(emp.ISPOSTINGREQUIRED)=0 then isnull(MAX(ff.MMDETAILID),-1) else -1 end as MMDETAILID FROM tpunch punch, mshift shift, memployee emp outer apply (select top(1) ff.MMDETAILID from Tdailyattendance ff where ff.Employeeid=emp.employeeid and ff.attendancedate = ':attendancedate') ff WHERE punch.EMPLOYEEID = emp.Employeeid AND punch.attendancedate = ':attendancedate' AND emp.shiftid = shift.shiftid AND punch.PUNCHTYPE in(0) :innerreplacefields group by punch.attendancedate,punch.EmployeeId UNION ALL select attendancedate,EMPLOYEEID,INTIME,OUTTIME,MMDETAILID from #TEMP1 ) subquery on ( subquery.EmployeeId=emp.Employeeid and subquery.MMDETAILID=dailyatt.MMDETAILID ) left outer join MSHIFT tdailyattshift on ( dailyatt.SHIFTID=tdailyattshift.shiftid ) left outer join MLeave leave on(dailyatt.LeaveId =leave.leaveid) LEFT OUTER JOIN MOULEVELSETTING OULEVELSETTING ON(OULEVELSETTING.OUID=emp.workouid) LEFT OUTER JOIN MLeave absentleave on(OULEVELSETTING.ABSENTLEAVEID =absentleave.leaveid) left outer join ( SELECT B.EMPLOYEEID,b.LEAVETYPEID ,c.LEAVECODE,c.LEAVENAME,SUM(b.NUMBEROFDAYS) as NUMBEROFDAYS,c.LEAVETYPE,b.MMdetailid FROM tLEAVE A ,tLEAVEdETAIL B,MbiztransactionType biztype,MLEAVE c WHERE A.LEAVEID=B.LEAVEID and A.BIZTRANSACTIONTYPEID =biztype.BIZTRANSACTIONTYPEID and b.LEAVETYPEID=c.LeaveId and biztype.BIZTRANSACTIONCLASSID=- 1399999955 and a.Status=1 and B.LEAVEDATE=':attendancedate' group by EMPLOYEEID,b.LEAVETYPEID,c.LEAVECODE,c.LEAVENAME,c.LEAVETYPE,b.MMdetailid ) leavefields on ( leavefields.EMPLOYEEID=emp.Employeeid ) left outer join (SELECT A.EMPLOYEEID, sum(case when A.TYPE=0 then A.Duration end) as BusHrs,sum(case when A.TYPE=1 then A.Duration end) as PerHrs,A.MMDETAILID FROM TTIMESLIP A WHERE 1=1 and A.ATTENDANCEDATE=':attendancedate' and a.Status=1 group by A.EMPLOYEEID,a.MMDETAILID )timeslip on ( timeslip.EMPLOYEEID=emp.Employeeid ) left outer join (select a.EMPLOYEEID,SUM(a.DURATION)as OverTimeDuration,SUM(a.FACTORHRS)as OTFACTOR from TOVERTIMEDETAIL a, TOVERTIME b where a.OVERTIMEID=b.OVERTIMEID and b.FORTHEDATE>=':attendancedate' and b.FORTHEDATE<=':attendancedate' group by a.EMPLOYEEID)OverTime on (OverTime.EMPLOYEEID=emp.EMPLOYEEID) left outer join Mshift shiftemp on(emp.shiftid=shiftemp.shiftid ) left outer join Mshift shifdailyatt on(dailyatt.shiftid=shifdailyatt.shiftid ) left outer join MREASON reason on(dailyatt.reasonId =reason.reasonId ) ,MLOCATION location where 1=1 and emp.DEPARTMENTID=dept.DepartmentId :replacefields and emp.WORKTYPEID =gcm.gcmid and emp.STATUS=1 and emp.EMPLOYEESTATUS=0 and emp.LocationId =location.LOCATIONID and case when leavefields.LEAVETYPEID IS not null THen leavefields.LEAVETYPEID else dailyatt.LeaveId end =FinalLeave.LeaveId and emp.EmployeeId=dailyatt.EmployeeId "; // Final projection joining #tempdaily (working set produced by // LOAD_DAILY_ATTENDANCE_FROM_PUNCH) back against TDAILYATTENDANCE/MLEAVE // to prefer any persisted (saved) daily attendance row over the // computed-from-punch values. This is the query actually paged with // OFFSET/FETCH by the DAL. public const string GET_FINAL_MULTI_EMPLOYEE_LOAD_ATTENDNANCE = @" select a.DailyAttendanceId, a.DailyAttendanceAttendanceDate, a.DailyAttendancePreviousDayType, a.EmployeeId, a.EmployeeCode, a.EmployeeName, a.ShiftId, a.ShiftCode, a.ShiftDescription, Isnull(b.DayType, a.DailyAttendanceDayType) as DailyAttendanceDayType, isNull(b.LeaveId,a.LeaveId) As LeaveId, isNull(c.LEAVECODE,a.LeaveCode) As LeaveCode, isNull(c.LeaveName,a.LeaveName) As LeaveName, ISNULL(b.INTIME,a.DailyAttendanceInTime) as DailyAttendanceInTime, ISNULL(b.OUTTIME,a.DailyAttendanceOutTime) as DailyAttendanceOutTime, a.DailyAttendanceWorkedHours, a.DailyAttendanceEarlyIn, a.DailyAttendanceEarlyOut, a.DailyAttendanceShortHours, a.DailyAttendanceExtraHours, a.DailyAttendanceOtHours, a.DailyAttendanceOtFactorHours, a.DailyAttendanceRemarks, a.DailyAttendanceVersion, a.DailyAttendanceShiftMins, a.DailyAttendanceBusHours, a.DailyAttendancePerHours, a.DepartmentId, a.DepartmentCode, a.DepartmentName, a.WorkTypeCode, a.WorkTypeId, a.WorkTypeName, a.DailyAttendanceLocation, a.DailyAttendanceLateIn, a.DailyAttendanceLateOut, a.ReasonId, a.ReasonCode, a.ReasonName, isnull(a.DailyAttendanceIsManualInTime,b.ISMANUALINTIME)as DailyAttendanceIsManualInTime, isnull(a.DailyAttendanceIsManualOutTime,b.ismanualOuttime) as DailyAttendanceIsManualOutTime, isnull(a.DailyAttendanceIsManualDayType,b.ismanualdayType) as DailyAttendanceIsManualDayType from #tempdaily a left outer join TDAILYATTENDANCE b on(a.DailyAttendanceId=b.DailyAttendanceId) left outer join MLEAVE c on(b.LEAVEID=c.LEAVEID)"; // Weekoff recalculation across previous/next day boundaries. Stored proc call — // GB4 substitutes all 5 params via string.Replace (not Dapper @binding), since // :fromdate/:todate are pre-quoted with literal single-quotes in the text. // Kept consistent with that pattern here to avoid the same "Conversion failed" // bug we hit earlier when quoted placeholders were bound instead of replaced. public const string UPDATE_DAILYATTENDANCE_PREVIOUS_NEXT_DAY = "EXEC PROCUPDATEDAYTYPEPREVIOUSNEXTCONDITION :payconfigurationid,':fromdate',':todate',:ouid,:employeeid"; // Wraps LOAD_DAILY_ATTENDANCE_FROM_PUNCH's SELECT to turn it into an UPDATE // against TDAILYATTENDANCE. Must be concatenated as: // UPDATE_LOAD_DAILYATTENDNACE + LOAD_DAILY_ATTENDANCE_FROM_PUNCH(select) + UPDATE_LOAD_DAILYATTENDNACE2 // :userid resolved via string.Replace before concatenation. public const string UPDATE_LOAD_DAILYATTENDNACE = @" Update A set A.DAILYATTENDANCEID=sub.DAILYATTENDANCEID, A.ATTENDANCEDATE=sub.DailyAttendanceAttendanceDate, A.EMPLOYEEID=sub.EMPLOYEEID, A.SHIFTID=sub.SHIFTID, A.DAYTYPE=sub.DailyAttendanceDayType, A.LEAVEID=sub.LEAVEID, A.INTIME=sub.DailyAttendanceInTime, A.OUTTIME=sub.DailyAttendanceOutTime, A.WORKEDHOURS=sub.DailyAttendanceWORKEDHOURS, A.EARLYIN=sub.DailyAttendanceEARLYIN, A.EARLYOUT=sub.DailyAttendanceEARLYOUT, A.SHORTHOURS=sub.DailyAttendanceSHORTHOURS, A.EXTRAHOURS=sub.DailyAttendanceEXTRAHOURS, A.OTHOURS=sub.DailyAttendanceOTHOURS, A.OTFACTORHOURS=sub.DailyAttendanceOTFACTORHOURS, A.REMARKS=sub.DailyAttendanceREMARKS, A.SHIFTMINS=sub.DailyAttendanceSHIFTMINS, A.BUSHOURS=sub.DailyAttendanceBUSHOURS, A.PERHOURS=sub.DailyAttendancePERHOURS, A.DEPARTMENTID=sub.DEPARTMENTID, A.LOCATION=sub.DailyAttendanceLOCATION, A.LATEIN=sub.DailyAttendanceLATEIN, A.LATEOUT=sub.DailyAttendanceLATEOUT, --A.WORKTYPEID=sub.WorkTypeId, A.VERSION=sub.DailyAttendanceVersion ,A.IsManualInTime=sub.DailyAttendanceIsManualInTime, A.IsManualOutTime=sub.DailyAttendanceIsManualOutTime, A.IsManualDayType=sub.DailyAttendanceIsManualDayType, A.ModifiedById=:userid, A.ModifiedOn=getdate() FROM TDAILYATTENDANCE A, ( "; // Closes the UPDATE_LOAD_DAILYATTENDNACE...LOAD_DAILY_ATTENDANCE_FROM_PUNCH(select) // sandwich. Joins MSHIFT so shift-dependent logic in the SELECT resolves correctly. public const string UPDATE_LOAD_DAILYATTENDNACE2 = ")sub,MSHIFT shift\r\nwhere sub.dailyattendanceid=A.dailyattendanceid and sub.shiftid=shift.shiftid "; // Corrects OUTTIME for employees with a single punch or where only IN was // recorded — takes the last PUNCHSTATUS=1 (out) punch per employee/date and // applies it, subtracting 1440 if it crossed midnight into the next day. public const string UPDATE_OUT_TIME_DURING_BULK_UPDATE = @" UPDATE TDAILYATTENDANCE SET OUTTIME =CASE WHEN TDAILYATTENDANCE.ISMANUALOUTTIME=0 THEN OUTTIME ELSE CASE WHEN SUB.LASTOUT > 1440 THEN SUB.LASTOUT - 1440 ELSE SUB.LASTOUT END END FROM (SELECT IA.EMPLOYEEID, IA.ATTENDANCEDATE, ISNULL(MAX(CASE WHEN PUNCHSTATUS = 1 THEN CASE WHEN IA.ATTENDANCEDATE < IA.PUNCHDATE THEN PUNCHTIME + 0 ELSE PUNCHTIME END END), 0) LASTOUT FROM(SELECT A.EMPLOYEEID, A.ATTENDANCEDATE, A.PUNCHDATE, A.PUNCHTIME, A.PUNCHSTATUS, ROW_NUMBER() OVER( PARTITION BY A.EMPLOYEEID, A.ATTENDANCEDATE, A.PUNCHSTATUS ORDER BY A.EMPLOYEEID, A.ATTENDANCEDATE, A.PUNCHDATE, PUNCHTIME) SLNO FROM TPUNCH A cross apply(select COUNT(PUN.PUNCHID) as Num from TPUNCH pun where pun.EMPLOYEEID = a.EMPLOYEEID and pun.ATTENDANCEDATE = A.ATTENDANCEDATE)pun, MEMPLOYEE B WHERE 1 = 1 and a.EMPLOYEEID = B.EMPLOYEEID and pun.Num = 1 :replacefields ) IA GROUP BY EMPLOYEEID, ATTENDANCEDATE) SUB WHERE 1 = 1 AND TDAILYATTENDANCE.EMPLOYEEID = SUB.EMPLOYEEID AND TDAILYATTENDANCE.ATTENDANCEDATE = SUB.ATTENDANCEDATE "; // Backfills MMDETAILID/INTIME/OUTTIME/POSTINGPOSITIONID/WORKTYPEID onto existing // TDAILYATTENDANCE rows that were created before an MMDetail/posting was known // (MMDETAILID = -1 placeholder), picking the top-1 matching #TEMP1 row per employee/date. public const string UPDATE_DAILYATTENDANCE_WHERE_MMDETAILID_IS_MINUS_1_TO_TOP_1_RECORD = @" UPDATE TDAILYATTENDANCE SET MMDETAILID = SUB.MMDETAILID, INTIME = SUB.INTIME, OUTTIME = SUB.OUTTIME, POSTINGPOSITIONID = SUB.POSTINGPOSITIONID, WORKTYPEID = SUB.WORKTYPEID FROM (SELECT A.DAILYATTENDANCEID, B.MMDETAILID, B.INTIME, B.OUTTIME, B.POSTINGPOSITIONID,A.WORKTYPEID FROM TDAILYATTENDANCE A CROSS APPLY (SELECT TOP(1)B.MMDETAILID, B.INTIME, B.OUTTIME, B.POSTINGPOSITIONID,EMPLOYEEID,B.WORKTYPEID FROM #TEMP1 B WHERE B.EMPLOYEEID = A.EMPLOYEEID AND B.ATTENDANCEDATE = A.ATTENDANCEDATE) B WHERE 1 = 1 AND A.ATTENDANCEDATE = ':attendancedate' AND A.MMDETAILID = -1)SUB WHERE 1 = 1 AND TDAILYATTENDANCE.DAILYATTENDANCEID = SUB.DAILYATTENDANCEID "; // Same backfill, but for rows already matched exactly on // EMPLOYEEID/MMDETAILID/ATTENDANCEDATE/POSTINGPOSITIONID — a straight sync // from #TEMP1 rather than a top-1 fallback lookup. public const string UPDATE_DAILYATTENDANCE_WHERE_MMDETAILID_IS_FOUND = @" UPDATE TDAILYATTENDANCE SET MMDETAILID = SUB.MMDETAILID, INTIME = SUB.INTIME, OUTTIME = SUB.OUTTIME, POSTINGPOSITIONID = SUB.POSTINGPOSITIONID, WORKTYPEID = SUB.WORKTYPEID FROM (select *from #TEMP1)SUB WHERE 1 = 1 AND TDAILYATTENDANCE.EMPLOYEEID = SUB.EMPLOYEEID AND TDAILYATTENDANCE.MMDETAILID = SUB.MMDETAILID AND TDAILYATTENDANCE.ATTENDANCEDATE = SUB.ATTENDANCEDATE AND TDAILYATTENDANCE.POSTINGPOSITIONID = SUB.POSTINGPOSITIONID "; // Row count of #TEMP1 — used only to seed the AutoNumber range for the // subsequent INSERT (one number reserved per row about to be inserted). public const string TEMP_COUNT_FOR_INSERT = "SELECT COUNT(*) from #TEMP1"; // Inserts any #TEMP1 (employee/date/MMDetail) combination that has no matching // existing TDAILYATTENDANCE row (LEFT JOIN ... WHERE B.DAILYATTENDANCEID IS NULL), // using an auto-numbered ID range and OUTER APPLY to carry forward the most // recent existing attendance values for that employee/date as defaults. public const string INSERT_INTO_TDAILYATTENANCE_IF_ENTRY_FOUND = @" INSERT INTO TDAILYATTENDANCE (DAILYATTENDANCEID, ATTENDANCEDATE, EMPLOYEEID, SHIFTID, DAYTYPE, LEAVEID, INTIME, OUTTIME, WORKEDHOURS, EARLYIN, EARLYOUT, SHORTHOURS, EXTRAHOURS, OTHOURS, OTFACTORHOURS, REMARKS, SHIFTMINS, BUSHOURS, PERHOURS, DEPARTMENTID, LOCATION, LATEIN, LATEOUT, WORKTYPEID, STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, ISMANUALINTIME, ISMANUALOUTTIME, ISMANUALDAYTYPE, REASONID, PREVIOUSDAYTYPE, PIHOURS, PREVIOUSINTIME, PREVIOUSOUTTIME, PREVIOUSLEAVEID, ADJUSTMENTNUMBER, BIZTRANSACTIONTYPEID, ADJUSTMENTID, OUID, TEXTDAYTYPE, MMDETAILID, WORKSUBTYPEID, WORKDONE, SUPERVISOR, POSTINGPOSITIONID, TIMESHEETID, BREAKTIME, TIMESHEETNUMBER, PREVIOUSSHIFTID, FIRSTHALFLEAVEID, SECONDHALFLEAVEID, WAGERULEVERSIONID, TOTALWORKEDHOURS) SELECT :autoid+ Row_number() OVER(ORDER BY A.EMPLOYEEID) DAILYATTENDANCEID, A.ATTENDANCEDATE, A.EMPLOYEEID, Isnull(C.SHIFTID, -1) SHIFTID, Isnull(C.DAYTYPE, 0) DAYTYPE, Isnull(C.LEAVEID, -1) LEAVEID, A.INTIME, A.OUTTIME, Isnull(C.WORKEDHOURS, 0) WORKEDHOURS, Isnull(C.EARLYIN, 0) EARLYIN, Isnull(C.EARLYOUT, 0) EARLYOUT, Isnull(C.SHORTHOURS, 0) SHORTHOURS, Isnull(C.EXTRAHOURS, 0) EXTRAHOURS, Isnull(C.OTHOURS, 0) OTHOURS, Isnull(C.OTFACTORHOURS, 0) OTFACTORHOURS, Isnull(C.REMARKS, '') REMARKS, Isnull(C.SHIFTMINS, 0) SHIFTMINS, Isnull(C.BUSHOURS, 0) BUSHOURS, Isnull(C.PERHOURS, 0) PERHOURS, Isnull(C.DEPARTMENTID, -1) DEPARTMENTID, Isnull(C.LOCATION, -1) LOCATION, Isnull(C.LATEIN, 0) LATEIN, Isnull(C.LATEOUT, 0) LATEOUT, Isnull(A.WORKTYPEID, -1) WORKTYPEID, Isnull(C.STATUS, 1) STATUS, Isnull(C.VERSION, 0) VERSION, :userid CREATEDBYID, Getdate() CREATEDON, :userid MODIFIEDBYID, Getdate() MODIFIEDON, Isnull(C.ISMANUALINTIME, 0) ISMANUALINTIME, Isnull(C.ISMANUALOUTTIME, 0) ISMANUALOUTTIME, Isnull(C.ISMANUALDAYTYPE, 0) ISMANUALDAYTYPE, Isnull(C.REASONID, -1) REASONID, Isnull(C.PREVIOUSDAYTYPE, 0) PREVIOUSDAYTYPE, Isnull(C.PIHOURS, 0) PIHOURS, Isnull(C.PREVIOUSINTIME, 0) PREVIOUSINTIME, Isnull(C.PREVIOUSOUTTIME, 0) PREVIOUSOUTTIME, Isnull(C.PREVIOUSLEAVEID, -1) PREVIOUSLEAVEID, Isnull(C.ADJUSTMENTNUMBER, 0) ADJUSTMENTNUMBER, Isnull(C.BIZTRANSACTIONTYPEID, -1) BIZTRANSACTIONTYPEID, Isnull(C.ADJUSTMENTID, -1) ADJUSTMENTID, Isnull(C.OUID, -1) OUID, Isnull(C.TEXTDAYTYPE, '') TEXTDAYTYPE, a.MMDETAILID MMDETAILID, Isnull(C.WORKSUBTYPEID, -1) WORKSUBTYPEID, Isnull(C.WORKDONE, 0) WORKDONE, Isnull(C.SUPERVISOR, 0) SUPERVISOR, Isnull(a.POSTINGPOSITIONID, -1) POSTINGPOSITIONID, Isnull(C.TIMESHEETID, -1) TIMESHEETID, Isnull(C.BREAKTIME, 0) BREAKTIME, Isnull(C.TIMESHEETNUMBER, '') TIMESHEETNUMBER, Isnull(C.PREVIOUSSHIFTID, -1) PREVIOUSSHIFTID, Isnull(C.FIRSTHALFLEAVEID, -1) FIRSTHALFLEAVEID, Isnull(C.SECONDHALFLEAVEID, -1) SECONDHALFLEAVEID, Isnull(C.WAGERULEVERSIONID, -1) WAGERULEVERSIONID, Isnull(C.TOTALWORKEDHOURS, 0) TOTALWORKEDHOURS FROM #TEMP1 A LEFT OUTER JOIN TDAILYATTENDANCE B ON ( A.EMPLOYEEID = B.EMPLOYEEID AND A.ATTENDANCEDATE = B.ATTENDANCEDATE AND A.MMDETAILID = B.MMDETAILID ) OUTER APPLY (SELECT TOP(1) C.* FROM TDAILYATTENDANCE C WHERE C.EMPLOYEEID = A.EMPLOYEEID AND C.ATTENDANCEDATE = A.ATTENDANCEDATE)C WHERE 1 = 1 AND B.DAILYATTENDANCEID IS NULL "; // Blocks bulk attendance updates if payroll has already processed the pay // period covering :attendancedate for the given OU. :ouid is pre-quoted-free // (case expression, not string-literal), but :attendancedate is embedded in // single quotes in the raw text, so this stays on the .Replace() pattern, // not Dapper @binding — same rule we established for GET_PUNCH_BASED_ON_SITE. public const string CHECK_PAY_PROCESS_ALREADY_DONE = @" select COUNT(a.PAYPERIODID) from TPAYPROCESS a, MPAYPERIOD b,MEMPLOYEE C where a.PAYPERIODID=b.PAYPERIODID and a.EMPLOYEEID=c.EMPLOYEEID and a.OUID=c.WORKOUID and a.ouid=case when :ouid=-2100000000 then a.ouid else :ouid end and a.PAYCONFIGID=c.PAYCONFIGURATIONID and b.FROMDATE<=':attendancedate' and b.TODATE> =':attendancedate' and b.PERIODTYPE=0 "; // ===================================================================== // Ported for UpdateDailyattendanceforSameAndAcrossDate / // UpdateDailyattendanceforSameAndAcrossDateFineTuned. // ===================================================================== // Builds #PUNCH (min/max punch time per employee/day, adjustment-aware). public const string UPDATE_DAILYATTENDANCE_WORKEDHOURS_FROM_PUNCH_1 = @" IF OBJECT_ID('tempdb..#PUNCH') IS NOT NULL DROP TABLE #PUNCH select a.EMPLOYEEID,a.ATTENDANCEDATE, case when b.ADJUSTMENTDETAILID is null then MIn(DATEADD(minute, a.punchtime, a.punchdate))else MIn(DATEADD(minute, b.INTIME, b.attendancedate)) end Inpunch, case when b.ADJUSTMENTDETAILID is null then MAX(DATEADD(minute, a.punchtime, a.punchdate))else max(DATEADD(minute, case when b.INTIME> b.OUTTIME then b.OUTTIME +1440 else b.OUTTIME end , b.attendancedate)) end OutPunch into #PUNCH from TPUNCH a outer apply(select* from Tadjustmentdetail b where a.employeeid= b.employeeid and a.attendancedate= b.attendancedate)b where 1=1 and a.ATTENDANCEDATE >= ':fromattendancedate' and a.ATTENDANCEDATE <= ':toattendancedate' and a.EMPLOYEEID=case when :employeeid=-1 then a.EMPLOYEEID else :employeeid end group by a.EMPLOYEEID, a.ATTENDANCEDATE, b.ADJUSTMENTDETAILID"; // Recalculates worked hours / early-in / short-hours / OT etc. for PUNCHTYPE=4 // (same-date) employees, based on #TPUNCHFORSAMEANDACROSS. public const string UPDATE_DAILYATTENDANCE_IN_CASE_OF_SAME_DATE_ATTENDANCETYPE = @" Update Tdailyattendance set Tdailyattendance.WORKEDHOURS= case when sub.WorkedHrs>= (shift.SHIFTMINUTES/2+shift.BREAKMINUTES) then sub.WorkedHrs else sub.WorkedHrs end , Tdailyattendance.EARLYIN=case when(shift.starttime-Tdailyattendance.INTIME)>0 then (shift.starttime-Tdailyattendance.INTIME) else 0 end , Tdailyattendance.EARLYOUT=case when(shift.ENDTIME-Tdailyattendance.OUTTIME)>0 then (shift.ENDTIME-Tdailyattendance.OUTTIME) else 0 end , Tdailyattendance.SHORTHOURS=case when(shift.SHIFTMINUTES- (case when (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME)<0 then 1440+(Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) else (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) end))>0 then (shift.SHIFTMINUTES-(case when (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME)<0 then 1440+(Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) else (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) end)) else 0 end , Tdailyattendance.EXTRAHOURS=case when(shift.SHIFTMINUTES- (case when (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME)<0 then 1440+(Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) else (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) end))<0 then abs(shift.SHIFTMINUTES-(case when (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME)<0 then 1440+(Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) else (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) end)) else 0 end , Tdailyattendance.SHIFTMINS=shift.SHIFTMINUTES, Tdailyattendance.LATEIN=case when (shift.STARTTIME- Tdailyattendance.INTIME)<0 then ABS(shift.STARTTIME- Tdailyattendance.INTIME)else 0 end, Tdailyattendance.LATEOUT= CASE WHEN TDAILYATTENDANCE.OUTTIME-SHIFT.ENDTIME>=0 THEN TDAILYATTENDANCE.OUTTIME-SHIFT.ENDTIME ELSE 0 END , Tdailyattendance.OTHOURS=case when (sub.WorkedHrs>shift.SHIFTMINUTES) then (sub.WorkedHrs-shift.SHIFTMINUTES ) else 0 end From MSHIFT shift,Memployee emp ,(select inpunch.EMPLOYEEID,inpunch.ATTENDANCEDATE,sum(outpunch.PUNCHTIME-inpunch.PUNCHTIME) WorkedHrs from TPUNCH inpunch,TPUNCH outpunch, ( select a.EMPLOYEEID,a.ATTENDANCEDATE,a.PUNCHID as InPunchId,MIN(b.Punchid) OutPunchId from #TPUNCHFORSAMEANDACROSS a left outer join #TPUNCHFORSAMEANDACROSS b on(a.EMPLOYEEID=b.EMPLOYEEID and a.ATTENDANCEDATE=b.ATTENDANCEDATE and b.PUNCHSTATUS=1 and a.SLNO= (shift.SHIFTMINUTES/2+shift.BREAKMINUTES) then sub.WorkedHrs-shift.BREAKMINUTES else sub.WorkedHrs end , Tdailyattendance.EARLYIN=case when(shift.starttime-Tdailyattendance.INTIME)>0 then (shift.starttime-Tdailyattendance.INTIME) else 0 end , Tdailyattendance.EARLYOUT=case when(shift.ENDTIME-Tdailyattendance.OUTTIME)>0 then (shift.ENDTIME-Tdailyattendance.OUTTIME) else 0 end , Tdailyattendance.SHORTHOURS=case when(shift.SHIFTMINUTES- (case when (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME)<0 then 1440+(Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) else (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) end))>0 then (shift.SHIFTMINUTES-(case when (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME)<0 then 1440+(Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) else (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) end)) else 0 end , Tdailyattendance.EXTRAHOURS=case when(shift.SHIFTMINUTES- (case when (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME)<0 then 1440+(Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) else (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) end))<0 then abs(shift.SHIFTMINUTES-(case when (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME)<0 then 1440+(Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) else (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) end)) else 0 end , Tdailyattendance.SHIFTMINS=shift.SHIFTMINUTES, Tdailyattendance.LATEIN=case when (shift.STARTTIME- Tdailyattendance.INTIME)<0 then ABS(shift.STARTTIME- Tdailyattendance.INTIME)else 0 end, Tdailyattendance.LATEOUT= CASE WHEN TDAILYATTENDANCE.OUTTIME-SHIFT.ENDTIME>=0 THEN TDAILYATTENDANCE.OUTTIME-SHIFT.ENDTIME ELSE 0 END , Tdailyattendance.OTHOURS=case when (sub.WorkedHrs>shift.SHIFTMINUTES) then (sub.WorkedHrs-shift.SHIFTMINUTES ) else 0 end From MSHIFT shift,Memployee emp ,(select inpunch.EMPLOYEEID,min(inpunch.ATTENDANCEDATE) as InDate ,MAX(outpunch.ATTENDANCEDATE) as OutDate , sum(DATEDIFF(minute,(DATEADD(minute,inpunch.PUNCHTIME,inpunch.PUNCHDATE)),(DATEADD(minute,outpunch.PUNCHTIME,outpunch.PUNCHDATE)))) WorkedHrs from TPUNCH inpunch,TPUNCH outpunch, ( select a.EMPLOYEEID,a.PUNCHID as InPunchId,MIN(b.Punchid) OutPunchId from TPUNCH a left outer join TPUNCH b on(a.EMPLOYEEID=b.EMPLOYEEID and b.PUNCHSTATUS=1 and a.PUNCHID=f.STARTTIME then ATTENDANCEDATE else dateadd(day,1,ATTENDANCEDATE) end) as ShiftEndTime, f.SHIFTMINUTES,f.ENDTIME as EndTime INTO #tempob FROM TPUNCH a outer apply (select top(1) EMPLOYEEID,TIMESLIPSTARTTIME from TTIMESLIP where EMPLOYEEID=A.EMPLOYEEID and ATTENDANCEDATE=CASt(A.PUNCHDATE AS DATE) )VV outer apply (select count(PUNCHID) TotalInPunch from TPUNCH WHERE ATTENDANCEDATE=a.ATTENDANCEDATE and PUNCHTYPE=0 and PUNCHSTATUS=0 and EMPLOYEEID=A.EMPLOYEEID )inpunch, MEMPLOYEE b, MSHIFTPATTERNDETAIL c, MSHIFTPATTERN d, MSHIFTPATTERNDAYS e, MSHIFTPATTERNDAYS shiftdays, MSHIFT f WHERE a.EMPLOYEEID = b.EMPLOYEEID AND b.SHIFTPATTERNDETAILID = c.SHIFTPATTERNDETAILID AND d.PATTERNTYPE = 5---Flexi Pattern AND d.SHIFTPATTERNID = e.SHIFTPATTERNID and e.SHIFTPATTERNDAYSID=c.SHIFTPATTERNDAYSID and e.SHIFTPATTERNID=shiftdays.SHIFTPATTERNID AND shiftdays.SHIFTID = f.SHIFTID AND ISNULL(inpunch.TotalInPunch,0)=0 AND a.PUNCHTYPE=0 AND a.PUNCHSTATUS = 1--OUT Punch Only :replacefields GROUP BY a.ATTENDANCEDATE, a.EMPLOYEEID, f.SHIFTID, f.SHIFTId, f.STARTTIME, f.ENDTIME, f.SHIFTMINUTES"; public const string UPDATE_DAILYATTENDANCE_SHIFTID_IN_CASE_OF_FLEXIBLE_SHIFTPATTERN_OUT_BASED_2 = @" SELECT attendancedate,Shiftid,Employeeid,EndTime,Abs(Datediff(minute, ActualPunchTime, ShiftEndTime)) AS FirstDiff INTO #temp2ob FROM #tempob "; public const string UPDATE_DAILYATTENDANCE_SHIFTID_IN_CASE_OF_FLEXIBLE_SHIFTPATTERN_OUT_BASED_3 = @" SELECT ROW_NUMBER() OVER ( partition by a.employeeid,a.attendancedate ORDER BY a.EndTime) AS RowID, a.* into #temp3ob FROM #temp2ob a, (SELECT Min(firstdiff) firstdiff, Employeeid, attendancedate FROM #temp2ob GROUP BY Employeeid, attendancedate) sub WHERE a.firstdiff = sub.firstdiff AND a.Employeeid = sub.Employeeid AND a.attendancedate = sub.attendancedate "; public const string UPDATE_DAILYATTENDANCE_SHIFTID_IN_CASE_OF_FLEXIBLE_SHIFTPATTERN_OUT_BASED_4 = @" update TDAILYATTENDANCE set SHIFTID=sub.shiftid from (Select * from #temp3ob where rowid=1 )sub where TDAILYATTENDANCE.employeeid=sub.employeeid and TDAILYATTENDANCE.ATTENDANCEDATE=sub.ATTENDANCEDATE"; // Recalculates hours/late-in/OT for employees on a flexible shift pattern, // once the shift has been assigned by the four steps above. public const string UPDATE_DAILYATTENDANCE_FIELD_FOR_FLEXISHIFT_EMPLOYEE = @" Update Tdailyattendance set Tdailyattendance.EARLYIN=case when(shift.starttime-Tdailyattendance.INTIME)>0 then (shift.starttime-Tdailyattendance.INTIME) else 0 end , Tdailyattendance.EARLYOUT=case when(shift.ENDTIME-Tdailyattendance.OUTTIME)>0 then (shift.ENDTIME-Tdailyattendance.OUTTIME) else 0 end , Tdailyattendance.SHORTHOURS=case when(shift.SHIFTMINUTES- (case when (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME)<0 then 1440+(Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) else (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) end))>0 then (shift.SHIFTMINUTES-(case when (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME)<0 then 1440+(Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) else (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) end)) else 0 end , Tdailyattendance.EXTRAHOURS=case when(shift.SHIFTMINUTES- (case when (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME)<0 then 1440+(Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) else (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) end))<0 then abs(shift.SHIFTMINUTES-(case when (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME)<0 then 1440+(Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) else (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) end)) else 0 end , Tdailyattendance.SHIFTMINS=shift.SHIFTMINUTES, Tdailyattendance.LATEIN=case when (shift.STARTTIME- Tdailyattendance.INTIME)<0 then ABS(shift.STARTTIME- Tdailyattendance.INTIME)else 0 end, Tdailyattendance.LATEOUT= CASE WHEN TDAILYATTENDANCE.OUTTIME-SHIFT.ENDTIME>=0 THEN TDAILYATTENDANCE.OUTTIME-SHIFT.ENDTIME ELSE 0 END , Tdailyattendance.OTHOURS=case when ((case when (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME)<0 then 1440+(Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) else (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) end)>shift.SHIFTMINUTES) then ((case when (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME)<0 then 1440+(Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) else (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) end)-shift.SHIFTMINUTES ) else 0 end From Memployee emp,MSHIFT shift,MSHIFTPATTERN d,MSHIFTPATTERNDAYS e where Tdailyattendance.EMPLOYEEID=emp.employeeid AND d.SHIFTPATTERNID = e.SHIFTPATTERNID and d.PATTERNTYPE = 5---Flexi Pattern and shift.shiftid=e.shiftid and Tdailyattendance.shiftid=shift.shiftid :replacefields "; // Syncs BUSHOURS/PERHOURS from approved timeslips for a single attendance date. public const string UPDATE_DAILYATTENDANCE_BUS_HOURS_PER_HOURS = @" UPDATE TDAILYATTENDANCE SET BUSHOURS=SUB.BUSHOURS, PERHOURS=SUB.PERHOURS FROM (SELECT TTIMESLIP.EMPLOYEEID, TTIMESLIP.ATTENDANCEDATE,TTIMESLIP.MMDETAILID, SUM(Isnull(CASE WHEN TTIMESLIP.TYPE = 0 THEN TTIMESLIP.DURATION--OFFICAL END, 0)) AS BUSHOURS, SUM(Isnull(CASE WHEN TTIMESLIP.TYPE = 1 THEN TTIMESLIP.DURATION--PERSONAL END, 0)) AS PERHOURS FROM TTIMESLIP,TDAILYATTENDANCE WHERE TTIMESLIP.ATTENDANCEDATE=':attedancedate' AND TTIMESLIP.STATUS=1 AND TTIMESLIP.ATTENDANCEDATE = TDAILYATTENDANCE.ATTENDANCEDATE AND TTIMESLIP.EMPLOYEEID = TDAILYATTENDANCE.EMPLOYEEID AND ( ( TDAILYATTENDANCE.INTIME = 0 AND TDAILYATTENDANCE.OUTTIME = 0 ) OR ( TTIMESLIP.FROMTIME < TDAILYATTENDANCE.INTIME ) ) group by TTIMESLIP.EMPLOYEEID,TTIMESLIP.ATTENDANCEDATE,TTIMESLIP.MMDETAILID )SUB,MEMPLOYEE EMP WHERE TDAILYATTENDANCE.EMPLOYEEID=SUB.EMPLOYEEID AND TDAILYATTENDANCE.ATTENDANCEDATE=SUB.ATTENDANCEDATE :innerreplace AND TDAILYATTENDANCE.EMPLOYEEID=EMP.EMPLOYEEID"; // Adds BUSHOURS from approved leave requests (working-day-type leave only), // per full/first-half/second-half day type (:daytype loop: 0/1/2). public const string UPDATE_DAILYATTENDANCE_BUS_HOURS_PER_HOURS_LEAVE_REQUEST = @" UPDATE TDAILYATTENDANCE SET BUSHOURS =BUSHOURS+CASE WHEN ((FH.LEAVETYPE=0 and FH.LEAVEID<>-1) AND (SH.LEAVETYPE=0 and SH.LEAVEID<>-1)) THEN B.SHIFTMINUTES ELSE CASE SUB.DAYTYPE WHEN 'FULLDAY' THEN B.SHIFTMINUTES WHEN 'FH' THEN CASE WHEN B.FIRSTHALFENDTIME - B.STARTTIME<0 THEN B.FIRSTHALFENDTIME - B.STARTTIME+1440 ELSE B.FIRSTHALFENDTIME - B.STARTTIME END ELSE CASE WHEN B.ENDTIME - B.SECONDHALFSTARTTIME<0 THEN B.ENDTIME - B.SECONDHALFSTARTTIME+1440 ELSE B.ENDTIME - B.SECONDHALFSTARTTIME END END END FROM MEMPLOYEE A, MSHIFT B, (SELECT B.EMPLOYEEID, B.LEAVEDATE, B.MMDETAILID, CASE B.DAYTYPE WHEN 0 THEN 'FULLDAY' WHEN 1 THEN 'FH' ELSE 'SH' END AS DAYTYPE FROM TLEAVE A, TLEAVEDETAIL B, MBIZTRANSACTIONTYPE C, MLEAVE D WHERE A.LEAVEID = B.LEAVEID AND A.BIZTRANSACTIONTYPEID = C.BIZTRANSACTIONTYPEID AND C.BIZTRANSACTIONCLASSID = -1399999955--LEAVEREQUEST AND B.LEAVETYPEID = D.LEAVEID AND D.LEAVETYPE in( 0)--Only Working Day Leave PER HOURS TO UPDATE AND B.DAYTYPE=:daytype AND A.STATUS=1 )SUB,MLEAVE FH, MLEAVE SH WHERE TDAILYATTENDANCE.EMPLOYEEID = SUB.EMPLOYEEID AND TDAILYATTENDANCE.ATTENDANCEDATE = SUB.LEAVEDATE AND TDAILYATTENDANCE.EMPLOYEEID = A.EMPLOYEEID AND TDAILYATTENDANCE.FIRSTHALFLEAVEID=FH.LEAVEID AND TDAILYATTENDANCE.SECONDHALFLEAVEID=SH.LEAVEID :innerreplace AND TDAILYATTENDANCE.SHIFTID = B.SHIFTID AND TDAILYATTENDANCE.ATTENDANCEDATE=':attedancedate'"; // Flips DayType 5/10 (half-leave / half-leave-half-absent) to 5 or 10 based // on whether actual hours cleared the shift's half-day threshold. public const string UPDATE_DAILYATTENDANCE_DAY_TYPE_5_OR_10 = @" UPDATE TDAILYATTENDANCE SET DAYTYPE = sub.DayType FROM (SELECT a.DAILYATTENDANCEID, a.WORKEDHOURS + a.PERHOURS + a.BUSHOURS AS TotalHours, b.HALFDAYMINUTES, CASE WHEN ( a.WORKEDHOURS + a.PERHOURS + a.BUSHOURS ) < b.HALFDAYMINUTES THEN 10 ELSE 5 END AS DayType FROM TDAILYATTENDANCE a, MSHIFT b, MOULEVELSETTING c, MEMPLOYEE d WHERE a.SHIFTID = b.SHIFTID AND b.FULLDAYMINUTES > 0 AND b.HALFDAYMINUTES > 0 AND a.EMPLOYEEID = d.EMPLOYEEID AND d.PUNCHTYPE IN ( 1,4 ) AND c.OUID = d.WORKOUID AND a.DAYTYPe IN( 5, 10 )--5.HALFLEAVE 10 Half Leave Half Absent AND ( a.WORKEDHOURS + a.PERHOURS + a.BUSHOURS ) < b.FULLDAYMINUTES :innerreplace )sub WHERE 1 = 1 AND TDAILYATTENDANCE.DAILYATTENDANCEID = sub.DAILYATTENDANCEID "; // Full/half-day policy determination for non-flexi, punch-based employees. public const string UPDATE_DAILYATTENDANCE_FULL_DAY_HALF_DAY_FOR_EMPLOYEE = @" UPDATE TDAILYATTENDANCE SET DAYTYPE =case when TDAILYATTENDANCE.ISMANUALDAYTYPE=0 then TDAILYATTENDANCE.DAYTYPE else SUB.DAYTYPE end, LeaveId=case when TDAILYATTENDANCE.ISMANUALDAYTYPE=0 then TDAILYATTENDANCE.LEAVEID else case when SUB.DAYTYPE in(1,8) then sub.ABSENTLEAVEID else TDAILYATTENDANCE.LEAVEID end end FROM (SELECT a.DAILYATTENDANCEID, b.FULLDAYMINUTES, b.HALFDAYMINUTES, a.INTIME, a.OUTTIME, b.ENDTIME, C.ABSENTLEAVEID, a.WORKEDHOURS+a.PERHOURS+a.BUSHOURS AS Duration, CASE WHEN ( a.WORKEDHOURS+a.PERHOURS+a.BUSHOURS+b.GRACEMINUTES ) < b.HALFDAYMINUTES THEN CASE WHEN a.PREVIOUSDAYTYPE NOT in(2,9) THEN 8 ELSE a.PREVIOUSDAYTYPE END--Absent ELSE CASE WHEN ( a.WORKEDHOURS+a.PERHOURS+a.BUSHOURS+b.GRACEMINUTES ) >= b.HALFDAYMINUTES AND (a.WORKEDHOURS+a.PERHOURS+a.BUSHOURS+b.GRACEMINUTES ) < b.FULLDAYMINUTES THEN case when a.PREVIOUSDAYTYPE in(6) then case when a.leaveid=-1 then 1 else case LEVTYPE.LEAVETYPE WHEN 1 THEN 10 WHEN 2 THEN 11 ELSE 1 END end else 1 end --Half day ELSE case when a.PREVIOUSDAYTYPE in(6) then 6 else 0 end --full day END END AS DayType FROM TDAILYATTENDANCE a, MSHIFT b, MOULEVELSETTING c, MEMPLOYEE d, MLEAVE LEVTYPE WHERE a.SHIFTID = b.SHIFTID and b.FULLDAYMINUTES>0 and b.HALFDAYMINUTES>0 and a.EMPLOYEEID=d.EMPLOYEEID and a.LEAVEID=LEVTYPE.LEAVEID :innerreplace and (a.DAYTYPE in(0,2,6,9) or LEVTYPE.LEAVETYPE=0) --Full day ,Week off,Holiday Compensatory working and d.PUNCHTYPE in (1) and c.OUID=case when :ouid=-2100000000 then c.ouid else :ouid end AND case when ':attedancedate'=cast(getdate() as date) then case when Datediff(MINUTE, CAST(getdate() as DATE), Getdate())-b.endtime<0 then 0 else case when b.endtime>b.starttime then 1 else 0 end end else case when ':attedancedate'> cast(getdate() as date) then 0 else 1 end end =1)SUB WHERE SUB.DAILYATTENDANCEID = TDAILYATTENDANCE.DAILYATTENDANCEID "; // Half-day/absent correction based on shift cutoff minutes and actual in-time. public const string UPDATE_DAILYATTENDANCE_DAY_TIME_BASED_ON_IN_TIME_AND_CUTOFFMINUTES_IN_SHIFT = @" UPDATE TDAILYATTENDANCE SET DAYTYPE =case when TDAILYATTENDANCE.ISMANUALDAYTYPE=0 then TDAILYATTENDANCE.DAYTYPE else 1 end, LEAVEID =case when TDAILYATTENDANCE.ISMANUALDAYTYPE= 0 then TDAILYATTENDANCE.LEAVEID else SUB.ABSENTLEAVEID end FROM (SELECT A.DAILYATTENDANCEID, C.ABSENTLEAVEID, VV.TIMESLIPENDTIME, CASE WHEN A.LATEIN>B.CUTOFFMINUTES then 'ABSENT' else 'PRESENT' end as DAYTYPE FROM TDAILYATTENDANCE A OUTER APPLY (SELECT MIN(TIMESLIPSTARTTIME) TIMESLIPSTARTTIME, MAX(TIMESLIPENDTIME) TIMESLIPENDTIME FROM TTIMESLIP WHERE EMPLOYEEID = A.EMPLOYEEID AND ATTENDANCEDATE = A.ATTENDANCEDATE AND STATUS = 1) VV, MSHIFT B, MOULEVELSETTING C, MEMPLOYEE D, MLEAVE E,MLEAVE FH, MLEAVE SH WHERE A.SHIFTID = B.SHIFTID AND A.OUID = C.OUID AND A.EMPLOYEEID = D.EMPLOYEEID AND A.DAYTYPE in (0)--FullDay AND a.PREVIOUSDAYTYPE NOT in(2,9)--Week off, Holiday and d.PUNCHTYPE in (1) AND B.CUTOFFMINUTES<> -1 AND A.LEAVEID = E.LEAVEID and E.LEAVETYPE<>0 AND A.FIRSTHALFLEAVEID= FH.LEAVEID AND A.SECONDHALFLEAVEID= SH.LEAVEID AND FH.LEAVETYPE<>0 AND SH.LEAVETYPE<>0 :innerreplace )SUB WHERE TDAILYATTENDANCE.DAILYATTENDANCEID = SUB.DAILYATTENDANCEID AND SUB.DAYTYPE = 'ABSENT'"; // Same correction based on shift cutoff minutes and actual out-time. public const string UPDATE_DAILYATTENDANCE_DAY_TIME_BASED_ON_OUT_TIME_AND_CUTOFFMINUTES_IN_SHIFT = @" UPDATE TDAILYATTENDANCE SET DAYTYPE =case when TDAILYATTENDANCE.ISMANUALDAYTYPE=0 then TDAILYATTENDANCE.DAYTYPE else 1 end, LEAVEID =case when TDAILYATTENDANCE.ISMANUALDAYTYPE= 0 then TDAILYATTENDANCE.LEAVEID else SUB.ABSENTLEAVEID end FROM (SELECT A.DAILYATTENDANCEID, A.OUTTIME, VV.TIMESLIPSTARTTIME, C.ABSENTLEAVEID, CASE WHEN A.EARLYOUT>B.CUTOFFMINUTESOUT then 'ABSENT' else 'PRESENT' end as DAYTYPE FROM TDAILYATTENDANCE A OUTER APPLY (SELECT Min(TIMESLIPSTARTTIME) TIMESLIPSTARTTIME, Max(TIMESLIPENDTIME) TIMESLIPENDTIME FROM TTIMESLIP WHERE EMPLOYEEID = A.EMPLOYEEID AND ATTENDANCEDATE = A.ATTENDANCEDATE AND STATUS = 1) VV, MSHIFT B, MOULEVELSETTING C, MEMPLOYEE D, MLEAVE E,MLEAVE FH, MLEAVE SH WHERE A.SHIFTID = B.SHIFTID AND A.OUID = C.OUID AND A.EMPLOYEEID = D.EMPLOYEEID AND B.CUTOFFMINUTESOUT<> -1 AND A.LEAVEID = E.LEAVEID and E.LEAVETYPE<>0 AND A.FIRSTHALFLEAVEID= FH.LEAVEID AND A.SECONDHALFLEAVEID= SH.LEAVEID AND FH.LEAVETYPE<>0 AND SH.LEAVETYPE<>0 AND A.DAYTYPE in (0)--FullDay AND a.PREVIOUSDAYTYPE NOT in(2,9)--Week off, Holiday and d.PUNCHTYPE in (1) :innerreplace AND case when a.ATTENDANCEDATE=cast(getdate() as date) then case when Datediff(MINUTE, CAST(getdate() as DATE), Getdate())-b.endtime<0 then 0 else case when b.endtime> b.starttime then 1 else 0 end end else case when a.ATTENDANCEDATE> cast(getdate() as date) then 0 else 1 end end = 1 )SUB WHERE TDAILYATTENDANCE.DAILYATTENDANCEID = SUB.DAILYATTENDANCEID AND SUB.DAYTYPE = 'ABSENT'"; // Runs the BATCHOTANDPI stored procedure to calculate OT/PI hours for the // given OU/date range using the OU-level OT settings passed as parameters. public const string UPDATE_DAILYATTENDANCE_FIELD_PI = "exec BATCHOTANDPI :ouid, ':startdateofmonth',':attendancedate', ':enddateofmonth', :oteligableminutes, :maxallowedotformale, :maxallowedotforfemale, :maximumotinmonth , :femaleothoursinevendays, :femaleothoursinodddays, :maleothoursinodddays, :maleothoursinevendays \r\n"; // Multi-statement batch: normalizes #MSHIFT end times across midnight, then // clamps in/out punch times to shift boundaries for shift-work-method // employees, producing #punch2 (consumed by WORKEDHOURS_FROM_PUNCH_2). public const string UPDATE_DAILYATTENDANCE_IN_TIME_OUT_TIME_BASED_ON_SHIFT_WORK_METHOD = @" IF OBJECT_ID('tempdb..#MSHIFT') IS NOT NULL DROP TABLE #MSHIFT IF OBJECT_ID('tempdb..#punch2') IS NOT NULL DROP TABLE #punch2 SELECT * into #MSHIFT FROM MSHIFT where 1=1 UPDATE #MSHIFT SET ENDTIME=CASE WHEN ENDTIMEDATEADD(MINUTE, shift.ENDTIME, b.attendancedate) then DATEADD(MINUTE, shift.ENDTIME, b.attendancedate) else b.OutPunch end end as Outtime into #punch2 from TDAILYATTENDANCE,#PUNCH b,MEMPLOYEE emp,#MSHIFT shift where 1=1 and TDAILYATTENDANCE.ATTENDANCEDATE= b.ATTENDANCEDATE and TDAILYATTENDANCE.EMPLOYEEID= b.EMPLOYEEID and TDAILYATTENDANCE.EMPLOYEEID= emp.EMPLOYEEID and TDAILYATTENDANCE.SHIFTID= shift.SHIFTID :replacefields"; // Final worked-hours write from #punch2 into TDAILYATTENDANCE, subtracting // break minutes once the half-shift+break threshold is met. public const string UPDATE_DAILYATTENDANCE_WORKEDHOURS_FROM_PUNCH_2 = @" Update Tdailyattendance set Tdailyattendance.WORKEDHOURS=case when DATEDIFF(minute, b.INTIME, b.Outtime)>=(shift.SHIFTMINUTES/2+shift.BREAKMINUTES) then DATEDIFF(minute, b.INTIME, b.Outtime) -shift.BREAKMINUTES else case when DATEDIFF(minute, b.INTIME, b.Outtime)<0 then 0 else DATEDIFF(minute, b.INTIME, b.Outtime) end end from #punch2 b ,#MSHIFT shift WHERE Tdailyattendance.DAILYATTENDANCEID=b.DAILYATTENDANCEID and Tdailyattendance.SHIFTID=shift.SHIFTID"; // Builds #TPUNCHFORSAMEANDACROSS — punch rows for the given date range, ranked // by SLNO per employee/date, consumed by UPDATE_DAILYATTENDANCE_IN_CASE_OF_SAME_DATE_ATTENDANCETYPE. public const string GENERATE_TEMP_RECORD_FOR_PUNCH_FOR_ATTENDANCEDATE = "IF OBJECT_ID('tempdb..#TPUNCHFORSAMEANDACROSS') IS NOT NULL DROP TABLE #TPUNCHFORSAMEANDACROSS " + "select ROW_NUMBER() over (partition by a.Employeeid,a.ATTENDANCEDATE order by a.ATTENDANCEDATE, a.punchdate,a.punchtime )SLNo,a.* into #TPUNCHFORSAMEANDACROSS from TPUNCH a where 1=1 :innerreplace"; // ===================================================================== // Late-in / early-out via timeslip + leave reconciliation chain. // ===================================================================== // Ranks timeslips by start time descending per employee, keyed against // #punchlogic's earliest in-punch, to find the timeslip covering the actual in-punch. public const string GET_MIN_MAX_TIMESLIP_BASED_ON_START_END_TIME_IN_TIMESLIP = @" SELECT a.TIMESLIPID, Row_number() OVER(partition BY a.EMPLOYEEID ORDER BY a.employeeid, a.TIMESLIPSTARTTIME desc) StartTimeSlno,b.DailyAttendanceId into #temptimeslipIn FROM TTIMESLIP a, TDAILYATTENDANCE b,#punchlogic punch WHERE 1=1 AND a.STATUS = 1 and a.ATTENDANCEDATE=b.ATTENDANCEDATE and a.EMPLOYEEID=b.EMPLOYEEID and dateadd(minute,a.TIMESLIPSTARTTIME,a.ATTENDANCEDATE)<=punch.Inpunch and a.EMPLOYEEID =punch.employeeid and a.ATTENDANCEDATE=punch.ATTENDANCEDATE :temptimeslipreplace "; // Same for the out-punch side, ranked by end time descending. public const string GET_MIN_MAX_TIMESLIP_BASED_ON_START_END_TIME_OUT_TIMESLIP = @" SELECT a.TIMESLIPID, Row_number() OVER(partition BY a.EMPLOYEEID ORDER BY a.employeeid, a.TIMESLIPENDTIME desc) EndTimeSLno,b.DailyAttendanceId into #temptimeslipOut FROM TTIMESLIP a, TDAILYATTENDANCE b,#punchlogic punch WHERE 1=1 AND a.STATUS = 1 and a.ATTENDANCEDATE=b.ATTENDANCEDATE and a.EMPLOYEEID=b.EMPLOYEEID and dateadd(minute,a.TIMESLIPENDTIME,a.ATTENDANCEDATE)>=punch.OutPunch and a.EMPLOYEEID =punch.employeeid and a.ATTENDANCEDATE=punch.ATTENDANCEDATE :temptimeslipreplace"; // Approved leave requests per employee/date, used to skip late-in/early-out // penalties on days where a leave already accounts for the gap. public const string GET_LEAVE_FOR_FINDING_LATEIN_EARLYOUT = @" select a.EMPLOYEEID,a.LEAVEDATE as Attendancedate,a.DAYTYPE into #tleavedetail from TLEAVEDETAIL a, TLEAVE b ,MBIZTRANSACTIONTYPE c where a.LEAVEID=b.LEAVEID and b.BIZTRANSACTIONTYPEID=c.BIZTRANSACTIONTYPEID and c.BIZTRANSACTIONCLASSID=-1399999955--Leave App :leavereplace and b.STATUS=1 group by a.EMPLOYEEID,a.LEAVEDATE ,a.DAYTYPE "; // Resolves effective shift-start comparison time for late-in, accounting // for full/half-day leave types via #tleavedetail. public const string GET_MIN_MAX_TIMESLIP_BASED_ON_START_END_TIME_LATE_IN = @" select a.EmployeeId,a.AttendanceDate, Min(case when aa.daytype in(0,1) then d.startTime else a.TimeSlipStartTime end)TimeSlipStartTime,Max(a.TimeSlipEndTime) TimeSlipEndTime into #latein from TTIMESLIP a ,#temptimeslipIn b,Tdailyattendance c outer apply (select aa.daytype from #tleavedetail aa where aa.Attendancedate=c.attendancedate and aa.employeeid=c.employeeid) aa ,Mshift d where a.TIMESLIPID=b.timeslipid and b.dailyattendanceid=c.dailyattendanceId and c.shiftid=d.SHIFTID group by a.EmployeeId,a.AttendanceDate,d.startTime"; // Same for early-out, resolving effective shift-end comparison time. public const string GET_MIN_MAX_TIMESLIP_BASED_ON_START_END_TIME_EARLY_OUT = @" select a.EmployeeId,a.AttendanceDate, Min(a.TimeSlipStartTime)TimeSlipStartTime,Max( case when aa.daytype in(0,2) then d.Endtime else a.TimeSlipEndTime end) TimeSlipEndTime into #earlyout from TTIMESLIP a ,#temptimeslipOut b,Tdailyattendance c outer apply (select aa.daytype from #tleavedetail aa where aa.Attendancedate=c.attendancedate and aa.employeeid=c.employeeid) aa ,Mshift d where a.TIMESLIPID=b.timeslipid and b.dailyattendanceid=c.dailyattendanceId and c.shiftid=d.SHIFTID group by a.EmployeeId,a.AttendanceDate,d.Endtime"; // Final write: LATEIN/EARLYOUT/LATEOUT/EARLYIN, reconciling leave, timeslip // permissions, and grace minutes (employee + shift) against #punchlogic's // actual punch times. Depends on #MSHIFT (normalized end times), #latein, // #earlyout, #tleavedetail, and #punchlogic all existing in the same batch. public const string UPDATE_DAILYATTENDANCE_EARLY_IN_LATE_OUT_BASED_ON_TIMESLIP_AND_LEAVE_REQUEST = @" update b set EARLYIN=0, EARLYOUT=0 from Tdailyattendance b, MEMPLOYEE D where 1=1 and b.INTIME=0 and b.OUTTIME=0 and b.EMPLOYEEID=D.EMPLOYEEID :innerreplace IF OBJECT_ID('tempdb..#MSHIFT2') IS NOT NULL DROP TABLE #MSHIFT2 SELECT * into #MSHIFT2 FROM MSHIFT where 1=1 UPDATE #MSHIFT2 SET ENDTIME=CASE WHEN ENDTIME Dateadd(minute,c.STARTTIME,punch.attendancedate) THEN CASE WHEN DateDiff(minute,Dateadd(minute,c.STARTTIME,punch.attendancedate),punch.Inpunch) <= c.GRACEMINUTES+d.GRACEMINUTES THEN 0 ELSE DateDiff(minute,Dateadd(minute,c.STARTTIME,punch.attendancedate),punch.Inpunch) END ELSE 0 END ELSE CASE WHEN timeslip.Employeeid IS NOT NULL THEN CASE WHEN Dateadd(minute,timesliplatein.TIMESLIPSTARTTIME,punch.attendancedate) = Dateadd(minute,c.STARTTIME,punch.attendancedate) THEN CASE WHEN punch.Inpunch > Dateadd(minute,timesliplatein.TIMESLIPENDTIME,punch.attendancedate) THEN CASE WHEN Datediff(minute,Dateadd(minute,timesliplatein.TIMESLIPENDTIME,punch.attendancedate),punch.Inpunch ) <= c.GRACEMINUTES+d.GRACEMINUTES THEN 0 ELSE Datediff(minute, Dateadd(minute,timesliplatein.TIMESLIPENDTIME,punch.attendancedate),punch.Inpunch) END ELSE 0 END ELSE CASE WHEN punch.Inpunch > Dateadd(minute,c.STARTTIME,punch.attendancedate) THEN CASE WHEN DateDiff(minute,Dateadd(minute,c.STARTTIME,punch.attendancedate),punch.Inpunch) <= c.GRACEMINUTES+d.GRACEMINUTES THEN 0 ELSE DateDiff(minute,Dateadd(minute,c.STARTTIME,punch.attendancedate),punch.Inpunch) END ELSE 0 END END ELSE CASE WHEN leave.Employeeid IS NOT NULL THEN CASE WHEN leave.DAYTYPE = 0 THEN 0 ELSE CASE WHEN leave.DAYTYPE = 1 THEN CASE WHEN b.INTIME - c.SECONDHALFSTARTTIME > 0 THEN b.INTIME - c.SECONDHALFSTARTTIME ELSE 0 END ELSE CASE WHEN b.INTIME - c.STARTTIME > 0 THEN b.INTIME - c.STARTTIME ELSE 0 END END END ELSE CASE WHEN punch.Inpunch > Dateadd(minute,c.STARTTIME,punch.attendancedate) THEN CASE WHEN DateDiff(minute,Dateadd(minute,c.STARTTIME,punch.attendancedate),punch.Inpunch) <= c.GRACEMINUTES THEN 0 ELSE DateDiff(minute,Dateadd(minute,c.STARTTIME,punch.attendancedate),punch.Inpunch) END ELSE 0 END END END END AS LATEIN, CASE WHEN leave.Employeeid IS NULL AND timeslip.Employeeid IS NULL THEN case when DATEDIFF(minute,DATEADD(minute,c.ENDTIME,punch.attendancedate),punch.outpunch)<0 then ABS(DATEDIFF(minute,DATEADD(minute,c.ENDTIME,punch.attendancedate),punch.outpunch)) else 0 end ELSE CASE WHEN timeslip.Employeeid IS NOT NULL THEN CASE WHEN timeslipearlyout.TIMESLIPENDTIME >= C.ENDTIME THEN 0 ELSE case when DATEDIFF(minute,DATEADD(minute,c.ENDTIME,punch.attendancedate),punch.outpunch)<0 then ABS(DATEDIFF(minute,DATEADD(minute,c.ENDTIME,punch.attendancedate),punch.outpunch)) else 0 end end else CASE WHEN leave.Employeeid IS NOT NULL THEN CASE WHEN leave.DAYTYPE = 0 THEN 0 ELSE CASE WHEN leave.DAYTYPE = 1 THEN CASE WHEN DATEDIFF(minute,DATEADD(minute,c.ENDTIME,punch.attendancedate),punch.outpunch) < 0 THEN ABS(DATEDIFF(minute,DATEADD(minute,c.ENDTIME,punch.attendancedate),punch.outpunch)) ELSE 0 END ELSE CASE WHEN DATEDIFF(minute,DATEADD(minute,c.FIRSTHALFENDTIME,punch.attendancedate),punch.outpunch) < 0 THEN ABS(DATEDIFF(minute,DATEADD(minute,c.FIRSTHALFENDTIME,punch.attendancedate),punch.outpunch)) ELSE 0 END END END END END end AS EarlyOut, CASE WHEN leave.Employeeid IS NULL AND timeslip.Employeeid IS NULL THEN case when DATEDIFF(minute,DATEADD(minute,c.ENDTIME,punch.attendancedate),punch.outpunch)>0 then ABS(DATEDIFF(minute,DATEADD(minute,c.ENDTIME,punch.attendancedate),punch.outpunch)) else 0 end ELSE CASE WHEN timeslip.Employeeid IS NOT NULL THEN case when DATEDIFF(minute,DATEADD(minute,c.ENDTIME,punch.attendancedate),punch.outpunch)>0 then ABS(DATEDIFF(minute,DATEADD(minute,c.ENDTIME,punch.attendancedate),punch.outpunch)) else 0 end else CASE WHEN leave.Employeeid IS NOT NULL THEN CASE WHEN leave.DAYTYPE = 0 THEN 0 ELSE CASE WHEN leave.DAYTYPE = 1 THEN CASE WHEN DATEDIFF(minute,DATEADD(minute,c.ENDTIME,punch.attendancedate),punch.outpunch) > 0 THEN ABS(DATEDIFF(minute,DATEADD(minute,c.ENDTIME,punch.attendancedate),punch.outpunch)) ELSE 0 END ELSE CASE WHEN DATEDIFF(minute,DATEADD(minute,c.FIRSTHALFENDTIME,punch.attendancedate),punch.outpunch) > 0 THEN ABS(DATEDIFF(minute,DATEADD(minute,c.FIRSTHALFENDTIME,punch.attendancedate),punch.outpunch)) ELSE 0 END END END END END end AS LATEOUT, CASE WHEN leave.Employeeid IS NULL AND timeslip.Employeeid IS NULL THEN case when DATEDIFF(minute,DATEADD(minute,c.STARTTIME,punch.attendancedate),punch.Inpunch)<0 then ABS(DATEDIFF(minute,DATEADD(minute,c.STARTTIME,punch.attendancedate),punch.Inpunch)) else 0 end ELSE CASE WHEN timeslip.Employeeid IS NOT NULL THEN case when DATEDIFF(minute,DATEADD(minute,c.STARTTIME,punch.attendancedate),punch.Inpunch)<0 then ABS(DATEDIFF(minute,DATEADD(minute,c.STARTTIME,punch.attendancedate),punch.Inpunch)) else 0 end else CASE WHEN leave.Employeeid IS NOT NULL THEN CASE WHEN leave.DAYTYPE = 0 THEN 0 ELSE CASE WHEN leave.DAYTYPE = 1 THEN 0 ELSE 0 END END END END end AS EarlyIn FROM TDAILYATTENDANCE B LEFT OUTER JOIN (SELECT B.* FROM TLEAVE A, TLEAVEDETAIL B, MBIZTRANSACTIONTYPE C WHERE A.LEAVEID = B.LEAVEID AND A.BIZTRANSACTIONTYPEID = C.BIZTRANSACTIONTYPEID AND C.BIZTRANSACTIONCLASSID = -1399999955--LeaveRequest AND A.STATUS=1 )LEAVE ON ( B.EMPLOYEEID = LEAVE.EMPLOYEEID AND B.ATTENDANCEDATE = LEAVE.LEAVEDATE ) left outer join TTIMESLIP timeslip on( timeslip.ATTENDANCEDATE = b.ATTENDANCEDATE and timeslip.EMPLOYEEID=b.EMPLOYEEID and timeslip.STATUS=1) left outer join #latein timesliplatein on( timesliplatein.ATTENDANCEDATE = b.ATTENDANCEDATE and timesliplatein.EMPLOYEEID=b.EMPLOYEEID) left outer join #earlyout timeslipearlyout on( timeslipearlyout.ATTENDANCEDATE = b.ATTENDANCEDATE and timeslipearlyout.EMPLOYEEID=b.EMPLOYEEID ), #MSHIFT2 C, MEMPLOYEE d, #punchlogic punch WHERE 1=1 AND B.SHIFTID = C.SHIFTID and b.employeeid=d.employeeid and d.PUNCHTYPE in (1,3) and b.ATTENDANCEDATE=PUNCH.ATTENDANCEDATE and b.EMPLOYEEID=PUNCH.EMPLOYEEID :innerreplace )sub where sub.DAILYATTENDANCEID=Tdailyattendance.DAILYATTENDANCEID"; // Builds #punchlogic — adjustment-aware min/max punch per employee/day, // consumed by the timeslip/late-in/early-out chain above. public const string GET_PUNCH_BEFORE_FINDING_EARLY_AND_LATE_IN = @" IF OBJECT_ID('tempdb..#punchlogic') IS NOT NULL DROP TABLE #punchlogic IF OBJECT_ID('tempdb..#temptimeslipIn') IS NOT NULL DROP TABLE #temptimeslipIn IF OBJECT_ID('tempdb..#temptimeslipOut') IS NOT NULL DROP TABLE #temptimeslipOut IF OBJECT_ID('tempdb..#tleavedetail') IS NOT NULL DROP TABLE #tleavedetail IF OBJECT_ID('tempdb..#latein') IS NOT NULL DROP TABLE #latein IF OBJECT_ID('tempdb..#earlyout') IS NOT NULL DROP TABLE #earlyout IF OBJECT_ID('tempdb..#MSHIFT') IS NOT NULL DROP TABLE #MSHIFT select a.EMPLOYEEID,a.ATTENDANCEDATE, case when b.ADJUSTMENTDETAILID is null then MIn(DATEADD(minute, a.punchtime, a.punchdate))else MIn(DATEADD(minute, b.INTIME, b.attendancedate)) end Inpunch, case when b.ADJUSTMENTDETAILID is null then MAX(DATEADD(minute, a.punchtime, a.punchdate))else max(DATEADD(minute, case when b.INTIME> b.OUTTIME then b.OUTTIME +1440 else b.OUTTIME end , b.attendancedate)) end OutPunch into #punchlogic from TPUNCH a outer apply(select* from Tadjustmentdetail b where a.employeeid= b.employeeid and a.attendancedate= b.attendancedate)b where 1=1 and a.ATTENDANCEDATE >= ':fromattendancedate' and a.ATTENDANCEDATE <= ':toattendancedate' and a.EMPLOYEEID=case when :employeeid=-1 then a.EMPLOYEEID else :employeeid end group by a.EMPLOYEEID, a.ATTENDANCEDATE, b.ADJUSTMENTDETAILID"; // ===================================================================== // Two-half leave handling. // ===================================================================== // Resets FirstHalf/SecondHalf leave to -1 for manually-day-typed rows before // re-deriving them from tLEAVEDETAIL below. public const string FIRST_SECOND_HALF_LEAVE_UPDATE_IN_DAILYATTENDNACE_TO_NONE = @" UPDATE TDAILYATTENDANCE SET FIRSTHALFLEAVEID = -1, SECONDHALFLEAVEID=-1 from MEMPLOYEE emp WHERE 1=1 AND TDAILYATTENDANCE.EMPLOYEEID=emp.EMPLOYEEID AND ISMANUALDAYTYPE=1 :replacefields"; // Sets FirstHalfLeaveId from approved first-half (DAYTYPE=1) leave requests. public const string FIRST_HALF_LEAVE_UPDATE_IN_DAILYATTENDNACE = @" UPDATE TDAILYATTENDANCE SET FIRSTHALFLEAVEID = SUB.LEAVETYPEID FROM (SELECT B.EMPLOYEEID, b.LEAVETYPEID, b.MMDETAILID, B.LEAVEDATE, c.LEAVECODE, c.LEAVENAME, Sum(b.NUMBEROFDAYS) AS NUMBEROFDAYS, c.LEAVETYPE FROM tLEAVE A, tLEAVEdETAIL B, MbiztransactionType biztype, MLEAVE c, MEMPLOYEE EMP WHERE A.LEAVEID = B.LEAVEID AND A.BIZTRANSACTIONTYPEID = biztype.BIZTRANSACTIONTYPEID AND b.LEAVETYPEID = c.LeaveId AND b.EMPLOYEEID = EMP.EMPLOYEEID AND biztype.BIZTRANSACTIONCLASSID = -1399999955 AND a.Status = 1 AND b.DAYTYPE = 1--First half :innerreplace GROUP BY B.EMPLOYEEID, b.LEAVETYPEID, B.LEAVEDATE, c.LEAVECODE, c.LEAVENAME, c.LEAVETYPE,b.MMDETAILID) SUB WHERE 1 = 1 AND TDAILYATTENDANCE.EMPLOYEEID = SUB.EMPLOYEEID AND TDAILYATTENDANCE.ATTENDANCEDATE = SUB.LEAVEDATE AND ISMANUALDAYTYPE=1 :replacefields"; // Same for second-half (DAYTYPE=2) leave requests. public const string SECOND_HALF_LEAVE_UPDATE_IN_DAILYATTENDNACE = @" UPDATE TDAILYATTENDANCE SET SECONDHALFLEAVEID = SUB.LEAVETYPEID FROM (SELECT B.EMPLOYEEID, b.LEAVETYPEID, b.MMDETAILID, B.LEAVEDATE, c.LEAVECODE, c.LEAVENAME, Sum(b.NUMBEROFDAYS) AS NUMBEROFDAYS, c.LEAVETYPE FROM tLEAVE A, tLEAVEdETAIL B, MbiztransactionType biztype, MLEAVE c, MEMPLOYEE EMP WHERE A.LEAVEID = B.LEAVEID AND A.BIZTRANSACTIONTYPEID = biztype.BIZTRANSACTIONTYPEID AND b.LEAVETYPEID = c.LeaveId AND b.EMPLOYEEID = EMP.EMPLOYEEID AND biztype.BIZTRANSACTIONCLASSID = -1399999955 AND a.Status = 1 AND b.DAYTYPE = 2--Second half :innerreplace GROUP BY B.EMPLOYEEID, b.LEAVETYPEID, B.LEAVEDATE, c.LEAVECODE, c.LEAVENAME, c.LEAVETYPE,b.MMDETAILID) SUB WHERE 1 = 1 AND TDAILYATTENDANCE.EMPLOYEEID = SUB.EMPLOYEEID AND TDAILYATTENDANCE.ATTENDANCEDATE = SUB.LEAVEDATE AND ISMANUALDAYTYPE=1 :replacefields"; // Derives overall DAYTYPE from the FirstHalf/SecondHalf leave-type combination // (e.g. paid+paid=14, paid+LOP=10, working+paid=5, etc.), and clears LeaveId // since it's now represented by the two half-day fields instead. public const string LEAVEID_BASEDON_TWO_HALF_LEAVE_UPDATE_IN_DAILYATTENDNACE = @" UPDATE TDAILYATTENDANCE SET DAYTYPE = CASE WHEN (FIRSTHALF.LEAVETYPE=1 AND SECONDHALF.LEAVETYPE=1) THEN 14 else CASE WHEN ( (FIRSTHALF.LEAVETYPE=1 AND SECONDHALF.LEAVETYPE=2) or (FIRSTHALF.LEAVETYPE=2 AND SECONDHALF.LEAVETYPE=1)) then 10 else CASE WHEN ( (FIRSTHALF.LEAVETYPE=0 AND SECONDHALF.LEAVETYPE=1) or (FIRSTHALF.LEAVETYPE=1 AND SECONDHALF.LEAVETYPE=0)) then 5 else CASE WHEN ( (FIRSTHALF.LEAVETYPE=0 AND SECONDHALF.LEAVETYPE=2) or (FIRSTHALF.LEAVETYPE=2 AND SECONDHALF.LEAVETYPE=0)) then 1 else CASE WHEN ( (FIRSTHALF.LEAVETYPE=0 AND SECONDHALF.LEAVETYPE=0)) then 0 else 8 end end end end end , LEAVEID = -1 from MEMPLOYEE emp,MLEAVE FIRSTHALF ,MLEAVE SECONDHALF WHERE 1=1 AND TDAILYATTENDANCE.EMPLOYEEID=emp.EMPLOYEEID AND FIRSTHALFLEAVEID <> -1 AND SECONDHALFLEAVEID <> -1 AND ISMANUALDAYTYPE=1 AND FIRSTHALFLEAVEID=FIRSTHALF.LEAVEID AND SECONDHALFLEAVEID=SECONDHALF.LEAVEID :replacefields"; // ===================================================================== // Short hours, trigger, text-day-type, zero-out-if-shift--1. // ===================================================================== // Computes SHORTHOURS/EXTRAHOURS/TOTALWORKEDHOURS against half-day or // full working minutes depending on current DAYTYPE. public const string UPDATE_DAILYATTENDANCE_FIELD_SHORT_HOURS = @" UPDATE TDAILYATTENDANCE SET SHORTHOURS =CASE WHEN TDAILYATTENDANCE.DAYTYPE IN (1,5,10,11,12) then CASE WHEN SUB.TOTALWORKEDHOURS-SHIFT.HALFDAYMINUTES>=0 THEN 0 ELSE SHIFT.HALFDAYMINUTES-SUB.TOTALWORKEDHOURS END else CASE WHEN TDAILYATTENDANCE.DAYTYPE NOT IN (2,4,8,9,13,1,5,10,11,12) THEN CASE WHEN SUB.TOTALWORKEDHOURS-SHIFT.WORKINGMINUTES>=0 THEN 0 ELSE SHIFT.WORKINGMINUTES-SUB.TOTALWORKEDHOURS END ELSE 0 END END, EXTRAHOURS =CASE WHEN TDAILYATTENDANCE.DAYTYPE IN (1,5,10,11,12) then CASE WHEN SUB.TOTALWORKEDHOURS-SHIFT.HALFDAYMINUTES>=0 THEN SUB.TOTALWORKEDHOURS-SHIFT.HALFDAYMINUTES ELSE 0 END else CASE WHEN TDAILYATTENDANCE.DAYTYPE NOT IN (2,4,8,9,13,1,5,10,11,12) THEN CASE WHEN SUB.TOTALWORKEDHOURS-SHIFT.WORKINGMINUTES>=0 THEN SUB.TOTALWORKEDHOURS-SHIFT.WORKINGMINUTES ELSE 0 END ELSE 0 END END, TOTALWORKEDHOURS= SUB.TOTALWORKEDHOURS FROM (SELECT TDAILYATTENDANCE.DAILYATTENDANCEID, CASE WHEN ( TDAILYATTENDANCE.INTIME = 0 OR TDAILYATTENDANCE.OUTTIME = 0 OR TDAILYATTENDANCE.INTIME = TDAILYATTENDANCE.OUTTIME ) THEN TDAILYATTENDANCE.WORKEDHOURS + TDAILYATTENDANCE.BUSHOURS ELSE CASE WHEN TDAILYATTENDANCE.WORKEDHOURS >= ( TDAILYATTENDANCE.WORKEDHOURS + TDAILYATTENDANCE.BUSHOURS ) THEN TDAILYATTENDANCE.WORKEDHOURS ELSE ( TDAILYATTENDANCE.WORKEDHOURS + TDAILYATTENDANCE.BUSHOURS ) END END TOTALWORKEDHOURS FROM MEMPLOYEE EMP, MSHIFT SHIFT, TDAILYATTENDANCE WHERE TDAILYATTENDANCE.EMPLOYEEID = EMP.EMPLOYEEID AND TDAILYATTENDANCE.SHIFTID = SHIFT.SHIFTID AND EMP.PUNCHTYPE IN ( 1,4 ) :replacefields ) SUB,MSHIFT SHIFT WHERE 1 = 1 AND TDAILYATTENDANCE.DAILYATTENDANCEID = SUB.DAILYATTENDANCEID AND TDAILYATTENDANCE.SHIFTID=SHIFT.SHIFTID"; // Runs the PROCAFTERTDAILYATTENDANCE stored procedure — VV Sir's customized // day-type trigger, invoked once per bulk-update run. public const string UPDATE_DAILYATTENDANCE_DAY_TYPE_FOR_TRGGER_CUSTOMIZED = "exec PROCAFTERTDAILYATTENDANCE :payconfigurationid,':fromdate',':todate' ,:ouid,:employeeid"; // Syncs TEXTDAYTYPE from the FNDAILYATTENDANCEDETAIL table-valued function // for the given date range. public const string UPDATE_DAILYATTENDANCE_TEXT_DATA_TYPE = @" UPDATE TDAILYATTENDANCE SET TEXTDAYTYPE=VDAILYATTENDANCEDETAIL.TEXTDAYTYPE FROM FNDAILYATTENDANCEDETAIL(-1,-1,-1,':fromdate',':todate') VDAILYATTENDANCEDETAIL,MEMPLOYEE emp WHERE TDAILYATTENDANCE.EMPLOYEEID=VDAILYATTENDANCEDETAIL.EMPLOYEEID AND TDAILYATTENDANCE.ATTENDANCEDATE=VDAILYATTENDANCEDETAIL.ATTENDANCEDATE AND TDAILYATTENDANCE.EMPLOYEEID=emp.EMPLOYEEID :replacefields AND TDAILYATTENDANCE.OUID<>-1 "; // Zeroes out all computed time fields for rows where SHIFTID is still -1 // (no shift ever resolved) for punch-based (PUNCHTYPE=1) employees. public const string UPDATE_DAILYATTENDANCE_FIELD_ALL_ZERO_IF_SHIFT_IS_MINUS_1 = @" Update Tdailyattendance set Tdailyattendance.EARLYIN=0, Tdailyattendance.EARLYOUT=0, Tdailyattendance.SHORTHOURS=0, Tdailyattendance.EXTRAHOURS=0, Tdailyattendance.SHIFTMINS=0, Tdailyattendance.LATEIN=0, Tdailyattendance.LATEOUT=0, Tdailyattendance.OTHOURS=0, Tdailyattendance.WORKEDHOURS=0 From Memployee emp,MSHIFT shift where Tdailyattendance.EMPLOYEEID=emp.employeeid and Tdailyattendance.shiftid=shift.shiftid and Tdailyattendance.shiftid=-1 and emp.PUNCHTYPE in (1) :replacefields "; // Bug #9744 base field sync — recalculates WORKEDHOURS/EARLYIN/EARLYOUT/ // SHORTHOURS/EXTRAHOURS/SHIFTMINS/LATEIN/LATEOUT/OTHOURS for punch-based // (PUNCHTYPE=1) employees from raw INTIME/OUTTIME against shift boundaries. // OTHOURS is zeroed entirely for employees with OTTYPE=0 (not OT-eligible). public const string UPDATE_DAILYATTENDANCE_FIELD = @" Update Tdailyattendance set Tdailyattendance.WORKEDHOURS=case when (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME)<0 then case when 1440+(Tdailyattendance.OUTTIME- Tdailyattendance.INTIME)>=(shift.SHIFTMINUTES/2+shift.BREAKMINUTES) then 1440+(Tdailyattendance.OUTTIME- Tdailyattendance.INTIME)-shift.BREAKMINUTES else 1440+(Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) end else case when (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME)>=(shift.SHIFTMINUTES/2+shift.BREAKMINUTES) then (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME)-shift.BREAKMINUTES else (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) end end, Tdailyattendance.EARLYIN=case when(shift.starttime-Tdailyattendance.INTIME)>0 then (shift.starttime-Tdailyattendance.INTIME) else 0 end , Tdailyattendance.EARLYOUT=case when(shift.ENDTIME-Tdailyattendance.OUTTIME)>0 then (shift.ENDTIME-Tdailyattendance.OUTTIME) else 0 end , Tdailyattendance.SHORTHOURS=case when(shift.SHIFTMINUTES- (case when (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME)<0 then 1440+(Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) else (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) end))>0 then (shift.SHIFTMINUTES-(case when (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME)<0 then 1440+(Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) else (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) end)) else 0 end , Tdailyattendance.EXTRAHOURS=case when(shift.SHIFTMINUTES- (case when (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME)<0 then 1440+(Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) else (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) end))<0 then abs(shift.SHIFTMINUTES-(case when (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME)<0 then 1440+(Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) else (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) end)) else 0 end , Tdailyattendance.SHIFTMINS=shift.SHIFTMINUTES, Tdailyattendance.LATEIN=case when (shift.STARTTIME- Tdailyattendance.INTIME)<0 then ABS(shift.STARTTIME- Tdailyattendance.INTIME)else 0 end, Tdailyattendance.LATEOUT= CASE WHEN TDAILYATTENDANCE.OUTTIME-SHIFT.ENDTIME>=0 THEN TDAILYATTENDANCE.OUTTIME-SHIFT.ENDTIME ELSE 0 END , Tdailyattendance.OTHOURS=case when emp.OTTYPE=0 then 0 else case when ((case when (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME)<0 then 1440+(Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) else (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) end)>shift.SHIFTMINUTES) then ((case when (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME)<0 then 1440+(Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) else (Tdailyattendance.OUTTIME- Tdailyattendance.INTIME) end)-shift.SHIFTMINUTES ) else 0 end end From Memployee emp,MSHIFT shift where Tdailyattendance.EMPLOYEEID=emp.employeeid and Tdailyattendance.shiftid=shift.shiftid and emp.PUNCHTYPE in (1) :replacefields "; public const string UPDATE_DAILYATTENDANCE_OT_HOURS_FORMULA_BASED = "update TDAILYATTENDANCE " + "\r\n" + "set " + "\r\n" + "OTHOURS=:expression" + "\r\n" + "from" + "\r\n" + "MSHIFT , MEMPLOYEE " + "\r\n" + "where 1=1" + "\r\n" + "and TDAILYATTENDANCE.SHIFTID=MSHIFT.SHIFTID" + "\r\n" + "and TDAILYATTENDANCE.EMPLOYEEID=MEMPLOYEE.EMPLOYEEID" + "\r\n" + " :replacefields "; public const string GET_EMPLOYEE_WORKED_TOTAL_WORKED_HRS = "IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp; " + "\r\n" + "WITH cte" + "\r\n" + " AS (SELECT Row_number() OVER(partition BY a.attendancedate, a.employeeid ORDER BY a.employeeid, a.attendancedate,a.PUNCHDATE,a.punchtime) SLno," + "\r\n" + " a.EMPLOYEEID," + "\r\n" + " a.ATTENDANCEDATE," + "\r\n" + " DATEADD(MINUTE,PUNCHTIME, PUNCHDATE) as PUNCHTIME" + "\r\n" + " FROM TPUNCH a,MEMPLOYEE b " + "\r\n" + " WHERE 1 = 1" + "\r\n" + " AND a.PUNCHTYPE = 0" + "\r\n" + " AND a.EMPLOYEEID=b.EMPLOYEEID" + "\r\n" + " :breaktimereplace" + "\r\n" + " )" + "\r\n" + "SELECT a.EMPLOYEEID," + "\r\n" + " a.ATTENDANCEDATE," + "\r\n" + " a.SLno," + "\r\n" + " a.PUNCHTIME AS INTIME," + "\r\n" + " Max(b.punchtime) OUTTIME," + "\r\n" + " DATEDIFF(minute, a.PUNCHTIME, Max(b.punchtime)) WorkedHrs" + "\r\n" + "INTO #temp" + "\r\n" + "FROM Cte a" + "\r\n" + " LEFT OUTER JOIN cte b" + "\r\n" + " ON( a.employeeid = b.employeeid" + "\r\n" + " AND a.ATTENDANCEDATE = b.ATTENDANCEDATE" + "\r\n" + " AND a.SLno + 1 = b.SLno )" + "\r\n" + "WHERE a.SLno%2 = 1" + "\r\n" + "GROUP BY a.EMPLOYEEID," + "\r\n" + " a.ATTENDANCEDATE," + "\r\n" + " a.SLno," + "\r\n" + " a.PUNCHTIME"; public const string GET_EMPLOYEE_FINAL_BREAKTIME_UPDATE = "UPDATE TDAILYATTENDANCE SET BREAKTIME=vv.BreakTime" + "\r\n" + "from " + "\r\n" + " (" + "\r\n" + "SELECT a.employeeid," + "\r\n" + " c.EMPLOYEECODE," + "\r\n" + " c.EMPLOYEENAME," + "\r\n" + " a.attendancedate," + "\r\n" + " Max(vv.TotalHours) TotalHours," + "\r\n" + " Sum(Isnull(a.WorkedHrs, 0)) WorkedHrs," + "\r\n" + " Max(Isnull(permission.DURATION, 0)) PermissionHours," + "\r\n" + " CASE" + "\r\n" + " WHEN Max(permission.DURATION) IS NULL THEN" + "\r\n" + " CASE" + "\r\n" + " WHEN Max(vv.TotalHours) - Sum(a.WorkedHrs) >= 0 THEN Max(vv.TotalHours) - Sum(a.WorkedHrs)" + "\r\n" + " ELSE 0" + "\r\n" + " END" + "\r\n" + " ELSE" + "\r\n" + " CASE" + "\r\n" + " WHEN Max(vv.TotalHours) - Sum(a.WorkedHrs) + Max(permission.DURATION) >= 0 THEN Max(vv.TotalHours) - Sum(a.WorkedHrs) + Max(permission.DURATION)" + "\r\n" + " ELSE 0" + "\r\n" + " END" + "\r\n" + " END AS BreakTime" + "\r\n" + "FROM #temp a" + "\r\n" + " OUTER apply (SELECT Sum(DURATION) DURATION" + "\r\n" + " FROM TTIMESLIP" + "\r\n" + " WHERE TIMESLIPDATE = a.ATTENDANCEDATE" + "\r\n" + " AND EMPLOYEEID = a.employeeid)permission" + "\r\n" + " CROSS apply (SELECT MIN(dateadd(minute,punchtime,punchdate)) INTIME," + "\r\n" + " max(dateadd(minute,punchtime,punchdate)) OUTTIME," + "\r\n" + " DATEDIFF(minute,MIN(dateadd(minute,punchtime,punchdate)),max(dateadd(minute,punchtime,punchdate))) as TotalHours" + "\r\n" + " FROM TPUNCH" + "\r\n" + " WHERE ATTENDANCEDATE = a.Attendancedate" + "\r\n" + " AND EMployeeid = a.employeeid)vv," + "\r\n" + " MEMPLOYEE c" + "\r\n" + "WHERE a.employeeid = c.EMPLOYEEID" + "\r\n" + "GROUP BY a.employeeid," + "\r\n" + " c.EMPLOYEECODE," + "\r\n" + " c.EMPLOYEENAME," + "\r\n" + " a.attendancedate" + "\r\n" + ")vv" + "\r\n" + " WHERE 1=1" + "\r\n" + "and TDAILYATTENDANCE.EMPLOYEEID=vv.EMPLOYEEID" + "\r\n" + "and TDAILYATTENDANCE.ATTENDANCEDATE=vv.ATTENDANCEDATE"; public const string UPDATE_DAILYATTENDANCE_BREAKTIME_HOURS_FORMULA_BASED = "update TDAILYATTENDANCE " + "\r\n" + "set " + "\r\n" + "BREAKTIME=:expression" + "\r\n" + "from" + "\r\n" + "MSHIFT , MEMPLOYEE " + "\r\n" + "where 1=1" + "\r\n" + "and TDAILYATTENDANCE.SHIFTID=MSHIFT.SHIFTID" + "\r\n" + "and TDAILYATTENDANCE.EMPLOYEEID=MEMPLOYEE.EMPLOYEEID" + "\r\n" + " :replacefields "; // GB4 parity: ATTENDANCE_DAY_TYPE_FOR_EMPLOYEE — calendar dates for the login OU, // left-joined to the employee's existing daily attendance to surface PREVIOUSDAYTYPE. public const string ATTENDANCE_DAY_TYPE_FOR_EMPLOYEE = @" SELECT ROW_NUMBER() over(order by a.detaildate) AS SlNo, a.DETAILDATE AS LeaveDate, DATENAME(dw, a.DETAILDATE) AS LeaveDayName, 1 AS NoDays, 0 AS LeaveDayType, ISNULL(b.PREVIOUSDAYTYPE, 0) AS DayType FROM MCALENDARDETAIL a LEFT OUTER JOIN TDAILYATTENDANCE b ON (a.DETAILDATE = b.ATTENDANCEDATE AND b.EMPLOYEEID = @employeeid), MORGANIZATIONUNIT c WHERE 1=1 AND a.CALENDARID = c.CALENDARID AND c.OUID = @ouid"; public const string GET_LEAVE_INCLUDEHOLIDAY = "SELECT INCLUDEHOLIDAY FROM MLEAVE WHERE LEAVEID = @leaveid"; // GB4 parity: DayTypeUpdateInAttendance Type=0 (Full Day). DAYTYPE=8 marks the // day as full-day-worked/absent per GB4 convention; LEAVEID resolved from the // employee's OU-level setting ABSENTLEAVEID. public const string UPDATE_HOLIDAY_IN_TDAILYATTENDANCE_FULLDAY = @" UPDATE TDAILYATTENDANCE SET DAYTYPE = 8, PREVIOUSDAYTYPE = 0, LEAVEID = c.ABSENTLEAVEID, REMARKS=':remarks' FROM MEMPLOYEE b,MOULEVELSETTING C WHERE 1 = 1 AND TDAILYATTENDANCE.EMPLOYEEID = b.EMPLOYEEID AND b.WORKOUID = c.OUID :replacefields "; // GB4 parity: DayTypeUpdateInAttendance Type=1 (Week Off). DAYTYPE=2. public const string UPDATE_HOLIDAY_IN_TDAILYATTENDANCE_WEEKOFF = @" UPDATE TDAILYATTENDANCE SET DAYTYPE = 2, PREVIOUSDAYTYPE = 2, LEAVEID = -1, REMARKS=':remarks' FROM MEMPLOYEE b WHERE 1 = 1 AND TDAILYATTENDANCE.EMPLOYEEID = b.EMPLOYEEID :replacefields "; // GB4 parity: DayTypeUpdateInAttendance Type=2 (Holiday). DAYTYPE=9. public const string UPDATE_HOLIDAY_IN_TDAILYATTENDANCE_HOLIDAY = @" UPDATE TDAILYATTENDANCE SET DAYTYPE = 9, PREVIOUSDAYTYPE = 9, LEAVEID = -1, REMARKS=':remarks' FROM MEMPLOYEE b WHERE 1 = 1 AND TDAILYATTENDANCE.EMPLOYEEID = b.EMPLOYEEID :replacefields "; // GB4 parity: EmployeeBLL.RegisterDeletion — PayProcessBLL.IsUpdatePossible(-1,-1,EmployeeId, // EffectiveFrom,EffectiveFrom,LoginDTO) guard branch (PayPeriodId == -1): blocks the deletion // when a regular pay period has already been processed for this employee covering an // attendance date on/after EffectiveFrom. public const string CHECK_PAYPROCESS_EXISTS_FOR_EMPLOYEE_FROM_DATE = @" SELECT COUNT(a.PAYPERIODID) FROM TPAYPROCESS a JOIN MPAYPERIOD b ON a.PAYPERIODID = b.PAYPERIODID JOIN MEMPLOYEE c ON a.EMPLOYEEID = c.EMPLOYEEID JOIN TDAILYATTENDANCE d ON c.EMPLOYEEID = d.EMPLOYEEID WHERE d.ATTENDANCEDATE >= b.FROMDATE AND d.ATTENDANCEDATE <= b.TODATE AND a.OUID = c.WORKOUID AND a.OUID = @OuId AND d.ATTENDANCEDATE >= @EffectiveFrom AND b.PERIODTYPE = 0 AND c.EMPLOYEEID = @EmployeeId; "; // GB4 parity: DailyAttendanceBLL.GetDailyAttendanceMultiEmployeeDetail / // DailyAttendanceQueryBuilder.GET_DAILYATTENDANCEMULTIPLE_WITH_ERROR_LOG. // The GB4 error-log OUTER APPLY against #FINALATTENDANCE is a client-specific // customization (only populated for ClientId -1399999814/LMW; every other tenant // gets an always-empty temp table per TDAILYATTENDANCE_ERROR_LOG_OTHER_THAN_LMW). // GB5 intentionally omits the temp-table error-log correlation and always returns // ErrorLogId=-1/ErrorLogCode=-1/ErrorLogName='' (the DTO's own defaults), matching // the non-LMW behavior that the vast majority of tenants already receive. public const string GET_DAILYATTENDANCE_MULTIPLE_EMPLOYEE = @" SELECT dailyatt.DAILYATTENDANCEID AS DailyAttendanceId, dailyatt.ATTENDANCEDATE AS DailyAttendanceAttendanceDate, dailyatt.EMPLOYEEID AS EmployeeId, employee.EMPLOYEECODE AS EmployeeCode, employee.EMPLOYEENAME AS EmployeeName, dailyatt.SHIFTID AS ShiftId, shift.SHIFTCODE AS ShiftCode, shift.SHIFTDESCRIPTION AS ShiftDescription, dailyatt.DAYTYPE AS DailyAttendanceDayType, dailyatt.LEAVEID AS LeaveId, lv.LEAVECODE AS LeaveCode, lv.LEAVENAME AS LeaveName, dailyatt.INTIME AS DailyAttendanceInTime, dailyatt.OUTTIME AS DailyAttendanceOutTime, dailyatt.WORKEDHOURS AS DailyAttendanceWorkedHours, dailyatt.EARLYIN AS DailyAttendanceEarlyIn, dailyatt.EARLYOUT AS DailyAttendanceEarlyOut, dailyatt.SHORTHOURS AS DailyAttendanceShortHours, dailyatt.EXTRAHOURS AS DailyAttendanceExtraHours, dailyatt.OTHOURS AS DailyAttendanceOtHours, dailyatt.OTFACTORHOURS AS DailyAttendanceOtFactorHours, dailyatt.REMARKS AS DailyAttendanceRemarks, dailyatt.SHIFTMINS AS DailyAttendanceShiftMins, dailyatt.BUSHOURS AS DailyAttendanceBusHours, dailyatt.PERHOURS AS DailyAttendancePerHours, dailyatt.DEPARTMENTID AS DepartmentId, dep.DEPARTMENTCODE AS DepartmentCode, dep.DEPARTMENTNAME AS DepartmentName, dailyatt.WORKTYPEID AS WorkTypeId, worktype.GCMCODE AS WorkTypeCode, worktype.GCMNAME AS WorkTypeName, dailyatt.LATEIN AS DailyAttendanceLateIn, dailyatt.LATEOUT AS DailyAttendanceLateOut, dailyatt.REASONID AS ReasonId, dailyatt.ISMANUALINTIME AS DailyAttendanceIsManualInTime, dailyatt.ISMANUALOUTTIME AS DailyAttendanceIsManualOutTime, dailyatt.ISMANUALDAYTYPE AS DailyAttendanceIsManualDayType, dailyatt.VERSION AS DailyAttendanceVersion, dailyatt.PREVIOUSDAYTYPE AS DailyAttendancePreviousDayType, emptype.GCMNAME AS EmployeeWorkTypeName, ou.ORGANIZATIONUNITNAME AS OuName, -1 AS ErrorLogId, -1 AS ErrorLogCode, '' AS ErrorLogName, CASE dailyatt.DAYTYPE WHEN 0 THEN 'FullWorkingDay' WHEN 1 THEN 'HalfWorking' WHEN 2 THEN 'WeeklyOff' WHEN 3 THEN 'Others' WHEN 4 THEN 'Leave' WHEN 5 THEN 'HalfWorking-Half Leave' WHEN 6 THEN 'CompensateWorking' WHEN 7 THEN 'CompensateOff' WHEN 8 THEN 'Absent' WHEN 9 THEN 'Holiday' WHEN 10 THEN 'Half Leave-Half Absent' WHEN 11 THEN 'CompHalfWorking-Half Leave' WHEN 12 THEN 'CompHalfWorking-Half Absent' WHEN 13 THEN 'Resigned' WHEN 14 THEN 'Two Half Leaves' END AS DayTypeText FROM TDAILYATTENDANCE dailyatt JOIN MGCM worktype ON dailyatt.WORKTYPEID = worktype.GCMID JOIN MEMPLOYEE employee ON employee.EMPLOYEEID = dailyatt.EMPLOYEEID JOIN MLEAVE lv ON lv.LEAVEID = dailyatt.LEAVEID JOIN MGCM emptype ON employee.EMPLOYEETYPEID = emptype.GCMID JOIN MORGANIZATIONUNIT ou ON dailyatt.OUID = ou.OUID JOIN MDEPARTMENT dep ON dailyatt.DEPARTMENTID = dep.DEPARTMENTID JOIN MSHIFT shift ON dailyatt.SHIFTID = shift.SHIFTID WHERE dailyatt.OUID = @OuId :replacefields ORDER BY dailyatt.ATTENDANCEDATE, employee.EMPLOYEECODE OFFSET @FirstNumber ROWS FETCH NEXT @MaxResult ROWS ONLY; "; public const string GET_DAILYATTENDANCE_MULTIPLE_EMPLOYEE_COUNT = @" SELECT COUNT(*) FROM TDAILYATTENDANCE dailyatt JOIN MGCM worktype ON dailyatt.WORKTYPEID = worktype.GCMID JOIN MEMPLOYEE employee ON employee.EMPLOYEEID = dailyatt.EMPLOYEEID JOIN MLEAVE lv ON lv.LEAVEID = dailyatt.LEAVEID JOIN MSHIFT shift ON dailyatt.SHIFTID = shift.SHIFTID WHERE dailyatt.OUID = @OuId :replacefields; "; } }