using Org.BouncyCastle.Asn1.X509; using System; using System.Collections.Generic; using System.Linq; using System.Reflection; using System.Text; using System.Text.RegularExpressions; using System.Threading.Tasks; namespace PayRollDAL.Query.TimeSlip { public class TimeSlipQB { public const string GET_TIMESLIP = @"SELECT ts.TIMESLIPID AS TimeSlipId, ts.TIMESLIPNUMBER AS TimeSlipNumber, ts.TIMESLIPDATE AS TimeSlipDate, ts.ATTENDANCEDATE AS TimeSlipAttendanceDate, ts.TYPE AS TimeSlipType, CASE WHEN ts.TYPE = 0 THEN 'Official' WHEN ts.TYPE = 1 THEN 'Personal' ELSE 'Unknown' END AS TimeSlipTypeName, ts.TIMESLIPSTARTTIME AS TimeSlipTimeSlipStartTime, ts.TIMESLIPENDTIME AS TimeSlipTimeSlipEndTime, ts.FROMTIME AS TimeSlipTimeSlipCalStartTime, ts.TOTIME AS TimeSlipTimeSlipCalEndTime, ts.DURATION AS TimeSlipDuration, ts.REASON AS TimeSlipReason, ts.ONDUTYLOCATION AS TimeSlipOnDutyLocation, ts.VERSION AS TimeSlipVersion, ts.STATUS AS TimeSlipStatus, ts.CREATEDBYID AS TimeSlipCreatedById, ts.CREATEDON AS TimeSlipCreatedOn, ts.MODIFIEDBYID AS TimeSlipModifiedById, ts.MODIFIEDON AS TimeSlipModifiedOn, ts.MMDETAILID AS MMDetailId, btt.BIZTRANSACTIONTYPEID AS BizTransactionTypeId, btt.BIZTRANSACTIONTYPECODE AS BizTransactionTypeCode, btt.BIZTRANSACTIONTYPENAME AS BizTransactionTypeName, ou.OUID AS OrganizationUnitId, ou.ORGANIZATIONUNITCODE AS OrganizationUnitCode, ou.ORGANIZATIONUNITNAME AS OrganizationUnitName, p.PERIODID AS PeriodId, p.PERIODCODE AS PeriodCode, p.PERIODNAME AS PeriodName, p.FROMDATE AS PeriodFromDate, p.TODATE AS PeriodToDate, ts.EMPLOYEEID AS EmployeeId, e.EMPLOYEECODE AS EmployeeCode, e.EMPLOYEENAME AS EmployeeName, e.PRIMARYMOBILE AS EmployeeMobileNumber, e.PRIMARYEMAILID AS UserPrimaryMailId, s.SHIFTID AS ShiftId, s.SHIFTCODE AS ShiftCode, s.SHIFTDESCRIPTION AS ShiftDescription, s.STARTTIME AS ShiftStartTime, s.ENDTIME AS ShiftEndTime, ap.EMPLOYEEID AS ApprovedById, ap.EMPLOYEECODE AS ApprovedByCode, ap.EMPLOYEENAME AS ApprovedByName, r.REASONID AS PermissionReasonId, r.REASONCODE AS PermissionReasonCode, r.REASONNAME AS PermissionReasonName FROM TTIMESLIP ts LEFT JOIN MEMPLOYEE e ON ts.EMPLOYEEID = e.EMPLOYEEID LEFT JOIN MSHIFT s ON ts.SHIFTID = s.SHIFTID LEFT JOIN MORGANIZATIONUNIT ou ON ts.OUID = ou.OUID LEFT JOIN MBIZTRANSACTIONTYPE btt ON ts.BIZTRANSACTIONTYPEID = btt.BIZTRANSACTIONTYPEID LEFT JOIN MPERIOD p ON ts.PERIODID = p.PERIODID LEFT JOIN MEMPLOYEE ap ON ts.APPROVEDBYID = ap.EMPLOYEEID LEFT JOIN MREASON r ON ts.REASONID = r.REASONID WHERE ts.TIMESLIPID = @timeslipid;"; public const string SAVE_TIMESLIP = @"INSERT INTO TTIMESLIP ( TIMESLIPID, BIZTRANSACTIONTYPEID, OUID, PERIODID, TIMESLIPNUMBER, TIMESLIPDATE, EMPLOYEEID, ATTENDANCEDATE, SHIFTID, TYPE, FROMTIME, TOTIME, DURATION, REASON, APPROVEDBYID, VERSION, STATUS, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TIMESLIPSTARTTIME, TIMESLIPENDTIME, ONDUTYLOCATION, MMDETAILID, REASONID ) VALUES ( @TimeSlipId, @BizTransactionTypeId, @OrganizationUnitId, @PeriodId, @TimeSlipNumber, @TimeSlipDate, @EmployeeId, @TimeSlipAttendanceDate, @ShiftId, @TimeSlipType, @TimeSlipTimeSlipStartTime, @TimeSlipTimeSlipEndTime, @TimeSlipDuration, @TimeSlipReason, @ApprovedById, @TimeSlipVersion, @TimeSlipStatus, @TimeSlipCreatedById, @TimeSlipCreatedOn, @TimeSlipModifiedById, @TimeSlipModifiedOn, @TimeSlipTimeSlipStartTime, @TimeSlipTimeSlipEndTime, @TimeSlipOnDutyLocation, @MMDetailId, @PermissionReasonId );"; public const string UPDATE_TIMESLIP = @"UPDATE TTIMESLIP SET BIZTRANSACTIONTYPEID = @BizTransactionTypeId, OUID = @OrganizationUnitId, PERIODID = @PeriodId, TIMESLIPNUMBER = @TimeSlipNumber, TIMESLIPDATE = @TimeSlipDate, EMPLOYEEID = @EmployeeId, ATTENDANCEDATE = @TimeSlipAttendanceDate, SHIFTID = @ShiftId, TYPE = @TimeSlipType, FROMTIME = @TimeSlipTimeSlipStartTime, TOTIME = @TimeSlipTimeSlipEndTime, DURATION = @TimeSlipDuration, REASON = @TimeSlipReason, APPROVEDBYID = @ApprovedById, VERSION = @TimeSlipVersion, STATUS = @TimeSlipStatus, MODIFIEDBYID = @TimeSlipModifiedById, MODIFIEDON = @TimeSlipModifiedOn, TIMESLIPSTARTTIME = @TimeSlipTimeSlipStartTime, TIMESLIPENDTIME = @TimeSlipTimeSlipEndTime, ONDUTYLOCATION = @TimeSlipOnDutyLocation, MMDETAILID = @MMDetailId, REASONID = @PermissionReasonId WHERE TIMESLIPID = @TimeSlipId;"; public const string DELETE_TIMESLIP = @"DELETE FROM TTIMESLIP WHERE TIMESLIPID = @timeslipid;"; public const string GET_TIMESLIP_SUMMARY = @"SELECT E.EMPLOYEEID AS EmployeeId, E.EMPLOYEECODE AS EmployeeCode, E.EMPLOYEENAME AS EmployeeName, YEAR(@FromDate) AS YearPart, MONTH(@FromDate) AS MonthPart, COUNT(TS.TIMESLIPID) AS PermissionTakenTimes, CASE WHEN COUNT(TS.TIMESLIPID) >= 2 THEN 0 ELSE 2 - COUNT(TS.TIMESLIPID) END AS BalanceTimes, CAST(ISNULL(SUM(TS.DURATION),0) / 60.0 AS DECIMAL(18,6)) AS TimeSlipDuration, CAST( CASE WHEN (ISNULL(SUM(TS.DURATION),0) / 60.0) >= 4 THEN 0 ELSE 4 - (ISNULL(SUM(TS.DURATION),0) / 60.0) END AS DECIMAL(18,6)) AS BalanceHours FROM dbo.MEMPLOYEE E LEFT JOIN dbo.TTIMESLIP TS ON TS.EMPLOYEEID = E.EMPLOYEEID AND TS.TYPE = 1 AND (@FromDate IS NULL OR TS.ATTENDANCEDATE >= @FromDate) AND (@ToDate IS NULL OR TS.ATTENDANCEDATE <= @ToDate) WHERE (@EmployeeId IS NULL OR E.EMPLOYEEID = @EmployeeId) GROUP BY E.EMPLOYEEID, E.EMPLOYEECODE, E.EMPLOYEENAME, YEAR(TS.ATTENDANCEDATE), MONTH(TS.ATTENDANCEDATE) ORDER BY E.EMPLOYEEID, YearPart, MonthPart;"; public const string GET_SELECTLIST_TIMESLIP = @"WITH PagedTimeSlip AS ( SELECT ts.TIMESLIPNUMBER AS Number, ts.TIMESLIPID AS Id, ts.ATTENDANCEDATE AS AttendanceDate, e.EMPLOYEECODE AS EmployeeCode, e.EMPLOYEENAME AS EmployeeName, CASE WHEN ts.TYPE = 0 THEN 'On Duty' WHEN ts.TYPE = 1 THEN 'Permission' ELSE 'Unknown' END AS PermissionType, ROW_NUMBER() OVER (ORDER BY ts.TIMESLIPNUMBER) AS RowNum FROM TTIMESLIP ts LEFT JOIN MEMPLOYEE e ON ts.EMPLOYEEID = e.EMPLOYEEID LEFT JOIN MREASON r ON ts.REASONID = r.REASONID WHERE ts.STATUS = 1 ) SELECT Number, Id, AttendanceDate, EmployeeCode, EmployeeName, PermissionType FROM PagedTimeSlip WHERE (@firstnumber = -1 AND @maxresult = -1) OR (RowNum BETWEEN @firstnumber AND @maxresult);"; public const string GET_LIST_TIMESLIP = @"WITH PagedTimeSliplist AS ( SELECT ts.TIMESLIPID AS TimeSlipId, ts.TIMESLIPNUMBER AS TimeSlipNumber, ts.TIMESLIPDATE AS TimeSlipDate, e.EMPLOYEEID AS EmployeeId, e.EMPLOYEECODE AS EmployeeCode, e.EMPLOYEENAME AS EmployeeName, d.DEPARTMENTID AS DepartmentId, d.DEPARTMENTCODE AS DepartmentCode, d.DEPARTMENTNAME AS DepartmentName, s.SHIFTID AS ShiftId, s.SHIFTCODE AS ShiftCode, s.SHIFTDESCRIPTION AS ShiftDescription, btt.BIZTRANSACTIONTYPEID AS BIZTransactionTypeId, btt.BIZTRANSACTIONTYPECODE AS BIZTransactionTypeCode, btt.BIZTRANSACTIONTYPENAME AS BIZTransactionTypeName, ISNULL(ap.EMPLOYEEID, -1) AS ApprovedById, ISNULL(ap.EMPLOYEECODE, 'NONE') AS ApprovedByCode, ISNULL(ap.EMPLOYEENAME, 'NONE') AS ApprovedByName, ts.TYPE AS TimeSlipType, ts.ATTENDANCEDATE AS TimeSlipAttendanceDate, ts.TIMESLIPSTARTTIME AS TimeSlipTimeSlipStartTime, ts.TIMESLIPENDTIME AS TimeSlipTimeSlipEndTime, ts.DURATION AS TimeSlipDuration, ts.ONDUTYLOCATION AS TimeSlipOnDutyLocation, r.REASONNAME AS TimeSlipReason, adminEmp.EMPLOYEECODE AS AdminReportingtoCode, adminEmp.EMPLOYEENAME AS AdminReportingtoName, fnEmp.EMPLOYEECODE AS FnReportingtoCode, fnEmp.EMPLOYEENAME AS FnReportingtoName, ts.STATUS AS Status, ou.ORGANIZATIONUNITCODE AS OUCode, ou.ORGANIZATIONUNITNAME AS OUName, e.EMPLOYEETYPEID AS EmplyeeTypeId, et.GCMCODE AS EmployeeTypeCode, et.GCMNAME AS EmployeeTypeName, -- Total Permission Count for Employee in Period ISNULL(tsSummary.TotalNumberofPermission,0) AS TotalNumberofPermission, ISNULL(tsSummary.TotalDurationMinutes,0) AS TotalDurationMinutes, ISNULL(tsSummary.PersonalDurationMinutes,0) AS PersonalDurationMinutes, ISNULL(tsSummary.OfficialDurationMinutes,0) AS OfficialDurationMinutes, CASE WHEN ts.STATUS = 0 THEN 'Pending' WHEN ts.STATUS = 1 THEN 'Approved' WHEN ts.STATUS = 2 THEN 'Cancelled' WHEN ts.STATUS = 3 THEN 'Rejected' ELSE 'Unknown' END AS StatusText, ROW_NUMBER() OVER (ORDER BY ts.TIMESLIPDATE DESC) AS RowNum FROM TTIMESLIP ts LEFT JOIN MEMPLOYEE e ON ts.EMPLOYEEID = e.EMPLOYEEID LEFT JOIN MDEPARTMENT d ON e.DEPARTMENTID = d.DEPARTMENTID LEFT JOIN MSHIFT s ON ts.SHIFTID = s.SHIFTID LEFT JOIN MORGANIZATIONUNIT ou ON ts.OUID = ou.OUID LEFT JOIN MBIZTRANSACTIONTYPE btt ON ts.BIZTRANSACTIONTYPEID = btt.BIZTRANSACTIONTYPEID LEFT JOIN MEMPLOYEE ap ON ts.APPROVEDBYID = ap.EMPLOYEEID LEFT JOIN MREASON r ON ts.REASONID = r.REASONID LEFT JOIN MGCM et ON et.GCMID = e.EMPLOYEETYPEID LEFT JOIN MEMPLOYEE adminEmp ON e.ADMINREPORTINGTOID = adminEmp.EMPLOYEEID LEFT JOIN MEMPLOYEE fnEmp ON e.EMPLOYEEID = fnEmp.EMPLOYEEID LEFT JOIN ( SELECT EMPLOYEEID, COUNT(*) AS TotalNumberofPermission, SUM(DURATION) AS TotalDurationMinutes, SUM(CASE WHEN TYPE = 1 THEN DURATION ELSE 0 END) AS PersonalDurationMinutes, SUM(CASE WHEN TYPE = 0 THEN DURATION ELSE 0 END) AS OfficialDurationMinutes FROM TTIMESLIP GROUP BY EMPLOYEEID ) tsSummary ON ts.EMPLOYEEID = tsSummary.EMPLOYEEID WHERE (@employeeid = -1 OR ts.EMPLOYEEID = @employeeid) AND (@ouid = -1 OR ts.OUID = @ouid) AND (@periodfromdate = -1 OR ts.ATTENDANCEDATE >= DATEADD(SECOND,@periodfromdate,'1970-01-01')) AND (@periodtodate = -1 OR ts.ATTENDANCEDATE < DATEADD(DAY,1,DATEADD(SECOND,@periodtodate,'1970-01-01'))) AND (@entityid = -1 OR btt.BIZTRANSACTIONTYPEID = @entityid) AND (@biztransactionclassid = -1 OR btt.BIZTRANSACTIONCLASSID = @biztransactionclassid) ) SELECT TimeSlipId, TimeSlipNumber, TimeSlipDate, EmployeeId, EmployeeCode, EmployeeName, DepartmentId, DepartmentCode, DepartmentName, ShiftId, ShiftCode, ShiftDescription, BIZTransactionTypeId, BIZTransactionTypeCode, BIZTransactionTypeName, ApprovedById, ApprovedByCode, ApprovedByName, TimeSlipType, TimeSlipAttendanceDate, TimeSlipTimeSlipStartTime, TimeSlipTimeSlipEndTime, TimeSlipDuration, TimeSlipOnDutyLocation, TimeSlipReason, AdminReportingtoCode, AdminReportingtoName, FnReportingtoCode, FnReportingtoName, Status, OUCode, OUName, EmployeeTypeCode, EmployeeTypeName, TotalNumberofPermission, TotalDurationMinutes, PersonalDurationMinutes, OfficialDurationMinutes, StatusText FROM PagedTimeSliplist WHERE (@firstnumber = -1 AND @maxresult = -1) OR (RowNum BETWEEN @firstnumber AND @maxresult);"; public const string SELECT_LIST = @"WITH PagedTimeSlip AS ( SELECT ts.TIMESLIPNUMBER AS Number, ts.TIMESLIPID AS Id, ts.ATTENDANCEDATE AS AttendanceDate, e.EMPLOYEECODE AS EmployeeCode, e.EMPLOYEENAME AS EmployeeName, CASE WHEN ts.TYPE = 0 THEN 'On Duty' WHEN ts.TYPE = 1 THEN 'Permission' ELSE 'Unknown' END AS PermissionType, ROW_NUMBER() OVER (ORDER BY ts.TIMESLIPNUMBER DESC) AS RowNum FROM TTIMESLIP ts LEFT JOIN MEMPLOYEE e ON ts.EMPLOYEEID = e.EMPLOYEEID LEFT JOIN MBIZTRANSACTIONTYPE btt ON ts.BIZTRANSACTIONTYPEID = btt.BIZTRANSACTIONTYPEID WHERE (@biztransactiontypeid = -1 OR ts.BIZTRANSACTIONTYPEID = @biztransactiontypeid) AND (@employeeid = -1 OR ts.EMPLOYEEID = @employeeid) AND (@timesliptype = -1 OR ts.TYPE = @timesliptype) AND (@number = '' OR ts.TIMESLIPNUMBER LIKE '%' + @number + '%') AND (@employeecode = '' OR e.EMPLOYEECODE LIKE '%' + @employeecode + '%') AND (@employeename = '' OR e.EMPLOYEENAME LIKE '%' + @employeename + '%') AND ts.STATUS != 2 ) SELECT Number, Id, AttendanceDate, EmployeeCode, EmployeeName, PermissionType FROM PagedTimeSlip WHERE (@firstnumber = -1 AND @maxresult = -1) OR (RowNum BETWEEN @firstnumber AND @maxresult);"; public const string CHECK_ATTENDANCE_FOR_SAME_DATE = @"SELECT TOP 1 TIMESLIPID, TIMESLIPNUMBER, FROMTIME AS TempStartTime, TOTIME AS TempEndTime FROM TTIMESLIP WHERE EMPLOYEEID = @EmployeeId AND ATTENDANCEDATE = @AttendanceDate AND ( FROMTIME < @EndTime AND TOTIME > @StartTime )"; public const string GET_PERIOD = @"SELECT TOP 1 PERIODID AS PeriodId, FROMDATE AS PeriodFromDate, TODATE AS PeriodToDate FROM MPERIOD WHERE PERIODID = @PeriodId;"; public const string CHECK_PERMISSION = @"SELECT CASE --RULE 1: Max 2 hours per day WHEN ( SELECT ISNULL(SUM(DURATION),0) FROM TTIMESLIP WHERE EMPLOYEEID = @EmployeeId AND ATTENDANCEDATE = @AttendanceDate AND TYPE = @TimeSlipType AND (@TimeSlipId = 0 OR TIMESLIPID <> @TimeSlipId) ) + @Duration > 120 THEN 'MAXIMUM ALLOWED PERMISSION FOR A DAY IS EXCEEDED..ONLY 2 HOURS ARE ALLOWED' --RULE 2: Max 2 times per month WHEN ( SELECT COUNT(*) FROM TTIMESLIP WHERE EMPLOYEEID = @EmployeeId AND TYPE = @TimeSlipType AND MONTH(ATTENDANCEDATE) = MONTH(@AttendanceDate) AND YEAR(ATTENDANCEDATE) = YEAR(@AttendanceDate) AND (@TimeSlipId = 0 OR TIMESLIPID <> @TimeSlipId) ) >= 2 THEN 'MAXIMUM ALLOWED PERMISSION FOR A MONTH IS EXCEEDED..ONLY 2 ARE ALLOWED' --RULE 3: Max 4 hours per month WHEN ( SELECT ISNULL(SUM(DURATION),0) FROM TTIMESLIP WHERE EMPLOYEEID = @EmployeeId AND TYPE = @TimeSlipType AND MONTH(ATTENDANCEDATE) = MONTH(@AttendanceDate) AND YEAR(ATTENDANCEDATE) = YEAR(@AttendanceDate) AND (@TimeSlipId = 0 OR TIMESLIPID <> @TimeSlipId) ) + @Duration > 240 THEN 'You have exceeded monthly permission limit (4 hours)' ELSE '' END;"; public const string CHECK_CROSS_TYPE_OVERLAP = @"SELECT TOP 1 TIMESLIPID, TIMESLIPNUMBER, FROMTIME AS TempStartTime, TOTIME AS TempEndTime, TYPE AS TimeSlipType FROM TTIMESLIP WHERE EMPLOYEEID = @EmployeeId AND ATTENDANCEDATE = @AttendanceDate AND TYPE <> @TimeSlipType -- opposite type AND FROMTIME < @EndTime AND TOTIME > @StartTime AND (@TimeSlipId = 0 OR TIMESLIPID <> @TimeSlipId);"; public const string GET_NEXT_TIMESLIP_NUMBER = @"SELECT ISNULL(MAX(CAST(SUBSTRING(TIMESLIPNUMBER,4,10) AS INT)),0) + 1 FROM TTIMESLIP WHERE TIMESLIPNUMBER LIKE 'TS-%';"; /// /// Fetches the employee's core fields and two-level reporting chain in one round-trip. /// Used by BLL to enrich the TimeSlipDTO before save (shift default, mail IDs, mobile numbers). /// public const string GET_EMPLOYEE_FOR_TIMESLIP = @" SELECT e.EMPLOYEEID AS EmployeeId, e.EMPLOYEECODE AS EmployeeCode, e.EMPLOYEENAME AS EmployeeName, e.PRIMARYMOBILE AS EmployeeMobileNumber, e.PRIMARYEMAILID AS UserPrimaryMailId, e.SHIFTID AS ShiftId, fre.PRIMARYEMAILID AS FunctionReportingToMailId, fre.PRIMARYMOBILE AS FunctionReportingToMobileNumber, fre2.PRIMARYEMAILID AS FunctionReportingToOfFunctionReportingToMailId, fre2.PRIMARYMOBILE AS FunctionReportingToOfFunctionReportingToMobileNumber, are.PRIMARYEMAILID AS AdminReportingToMailId, are.PRIMARYMOBILE AS AdminReportingToMobileNumber FROM MEMPLOYEE e LEFT JOIN MEMPLOYEE fre ON e.REPORTINGTOEMPLOYEEID = fre.EMPLOYEEID LEFT JOIN MEMPLOYEE fre2 ON fre.REPORTINGTOEMPLOYEEID = fre2.EMPLOYEEID LEFT JOIN MEMPLOYEE are ON e.ADMINREPORTINGTOID = are.EMPLOYEEID WHERE e.EMPLOYEEID = @EmployeeId;"; } }