using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace PayRollDAL.Query.Adjustment { public static class AdjustmentQB { public const string GET_ADJUSTMENT_REPORT = @"SELECT b.ADJUSTMENTNUMBER AS AdjustmentNumber, b.REFERENCEDATE AS AdjustmentDate, c.ATTENDANCEDATE AS AttendanceDate, e.REASONID AS ReasonId, e.REASONCODE AS ReasonCode, e.REASONNAME AS ReasonName, created.USERID AS CreatedUserId, created.USERCode AS CreatedUserCode, created.USERName AS CreatedUserName, Modified.USERID AS ModifiedUserId, Modified.USERCode AS ModifiedUserCode, Modified.USERName AS ModifiedUserName, a.EMPLOYEEID AS EmployeeId, a.EMPLOYEECODE AS EmployeeCode, a.EMPLOYEENAME AS EmployeeName, d.DEPARTMENTID AS DepartmetId, d.DEPARTMENTCODE AS DepartmetCode, d.DEPARTMENTNAME AS DepartmetName, cast(floor(C.PreviousIntime/60) as varchar)+':'+ case when (C.PreviousIntime- 60*floor(C.PreviousIntime/60))<10 then CAST(0 AS varchar)+cast((C.PreviousIntime- 60*floor(C.PreviousIntime/60)) as varchar) else cast( (C.PreviousIntime- 60*floor(C.PreviousIntime/60)) as varchar) end as PreviousInTime, cast(floor(C.InTime/60) as varchar)+':'+ case when (C.InTime- 60*floor(C.InTime/60))<10 then CAST(0 AS varchar)+cast((C.InTime- 60*floor(C.InTime/60)) as varchar) else cast( (C.InTime- 60*floor(C.InTime/60)) as varchar) end as InTime, cast(floor(C.PreviousOutTime/60) as varchar)+':'+ case when (C.PreviousOutTime- 60*floor(C.PreviousOutTime/60))<10 then CAST(0 AS varchar)+cast((C.PreviousOutTime- 60*floor(C.PreviousOutTime/60)) as varchar) else cast( (C.PreviousOutTime- 60*floor(C.PreviousOutTime/60)) as varchar) end as PreviousOutTime, cast(floor(C. OutTime/60) as varchar)+':'+ case when (C. OutTime- 60*floor(C. OutTime/60))<10 then CAST(0 AS varchar)+cast((C. OutTime- 60*floor(C. OutTime/60)) as varchar) else cast( (C. OutTime- 60*floor(C. OutTime/60)) as varchar) end as OutTime, case c.PREVIOUSDAYTYPE when 0 then 'FULLWORKINGDAY' when 1 then 'HALFWORKINGDAY' when 2 then 'WEEKLYOFF' when 3 then 'OTHERS' when 4 then 'LEAVE' when 5 then 'HALFLEAVE' when 6 then 'COMPENSATEWORKING' when 7 then 'COMPENSATEOFF' when 8 then 'ABSENT' when 9 then 'HOLIDAY' when 10 then 'HALFLEAVE-ABSENT' end as PreviousDayType, case c.DAYTYPE when 0 then 'FULLWORKINGDAY' when 1 then 'HALFWORKINGDAY' when 2 then 'WEEKLYOFF' when 3 then 'OTHERS' when 4 then 'LEAVE' when 5 then 'HALFLEAVE' when 6 then 'COMPENSATEWORKING' when 7 then 'COMPENSATEOFF' when 8 then 'ABSENT' when 9 then 'HOLIDAY' when 10 then 'HALFLEAVE-ABSENT' end as DayType, prleave.LEAVEID AS PreviousLeaveId, prleave.LEAVECODE AS PreviousLeaveCode, prleave.LEAVENAME AS PreviousLeaveName, currleave.LEAVEID AS LeaveId, currleave.LEAVECODE AS LeaveCode, currleave.LEAVENAME AS LeaveName, prshift.ShiftId AS PreviousShiftId, prshift.ShiftCode AS PreviousShiftCode, prshift.SHIFTDESCRIPTION AS PreviousShiftDescription, currshift.ShiftId AS ShiftId, currshift.ShiftCode AS ShiftCode, currshift.SHIFTDESCRIPTION AS ShiftDescription ,b.REMARKS AS Remarks ,CAST (b.CREATEDON AS DATE) AS CreatedOn ,b.ADJUSTMENTID AS AdjustmentId, art.USERCODE as AdminReportingToCode, art.USERNAME as AdminReportingToName, fn.EMPLOYEECODE as FnReportingtoCode, fn.EMPLOYEENAME as FnReportingtoName, currleave.STATUS as Status, b.STATUS as AdjustmentStatus FROM MEMPLOYEE a, TADJUSTMENT b, TADJUSTMENTDETAIL c, MDEPARTMENT d, MLEAVE prleave, MLEAVE currleave, MSHIFT prshift, MSHIFT currshift, MREASON e, MUSER created, MUSER Modified, MUSER art, MEMPLOYEE fn WHERE 1 = 1 AND b.ADJUSTMENTID = c.ADJUSTMENTID AND a.EMPLOYEEID = c.EMPLOYEEID AND a.DEPARTMENTID = d.DEPARTMENTID AND c.PREVIOUSLEAVEID = prleave.LEAVEID AND c.LEAVEID = currleave.LEAVEID AND c.PREVIOUSSHIFTID = prshift.SHIFTID AND c.SHIFTID = currshift.SHIFTID AND b.REASONID = e.REASONID AND b.CREATEDBYID = created.USERID AND b.MODIFIEDBYID = Modified.USERID AND a.ADMINREPORTINGTOID = art.USERID AND a.REPORTINGTOEMPLOYEEID = fn.EMPLOYEEID" + "\r\n"; public const string GET_ADJUSTMENT_REPORT_COUNT = @"SELECT count( b.ADJUSTMENTNUMBER) FROM MEMPLOYEE a, TADJUSTMENT b, TADJUSTMENTDETAIL c, MDEPARTMENT d, MLEAVE prleave, MLEAVE currleave, MSHIFT prshift, MSHIFT currshift, MREASON e, MUSER created, MUSER Modified, MUSER art, MEMPLOYEE fn WHERE 1 = 1 AND b.ADJUSTMENTID = c.ADJUSTMENTID AND a.EMPLOYEEID = c.EMPLOYEEID AND a.DEPARTMENTID = d.DEPARTMENTID AND c.PREVIOUSLEAVEID = prleave.LEAVEID AND c.LEAVEID = currleave.LEAVEID AND c.PREVIOUSSHIFTID = prshift.SHIFTID AND c.SHIFTID = currshift.SHIFTID AND b.REASONID = e.REASONID AND b.CREATEDBYID = created.USERID AND b.MODIFIEDBYID = Modified.USERID AND a.ADMINREPORTINGTOID = art.USERID AND a.REPORTINGTOEMPLOYEEID = fn.EMPLOYEEID " + "\r\n"; // GB4 parity: Adjustment.svc/AdjustmentNumberSelectList — AdjustmentDAL.AdjustmentNumberSelectlist // against AdjustmentQueryBuilder.GET_ADJUSTMENTNUMBER_SELECTLIST (TADJUSTMENT/TADJUSTMENTDETAIL/MEMPLOYEE/MUSER). // FirstNumber/MaxResult are a zero-based row offset/take (GB4 GenericLazy.ExceuteSelectQuery semantics). public const string GET_ADJUSTMENTNUMBER_SELECTLIST = @" WITH AdjustmentNumberSelectList AS ( SELECT a.ADJUSTMENTID AS AdjustmentId, a.ADJUSTMENTNUMBER AS AdjustmentNumber, c.EMPLOYEECODE AS EmployeeCode, c.EMPLOYEENAME AS EmployeeName, b.ATTENDANCEDATE AS AdjustmentAttendanceDate, d.USERCODE AS AdjustmentUserCode, d.USERNAME AS AdjustmentUserName, ROW_NUMBER() OVER (ORDER BY a.ADJUSTMENTNUMBER DESC, c.EMPLOYEENAME, b.ATTENDANCEDATE) AS RowNum FROM TADJUSTMENT a JOIN TADJUSTMENTDETAIL b ON a.ADJUSTMENTID = b.ADJUSTMENTID JOIN MEMPLOYEE c ON b.EMPLOYEEID = c.EMPLOYEEID JOIN MUSER d ON a.CREATEDBYID = d.USERID WHERE 1 = 1 /*CRITERIA*/ ) SELECT AdjustmentId, AdjustmentNumber, EmployeeCode, EmployeeName, AdjustmentAttendanceDate, AdjustmentUserCode, AdjustmentUserName FROM AdjustmentNumberSelectList WHERE (@FirstNumber = -1 AND @MaxResult = -1) OR (RowNum > @FirstNumber AND RowNum <= @FirstNumber + @MaxResult) ORDER BY RowNum;"; public const string GET_ADJUSTMENTNUMBER_SELECTLIST_COUNT = @" SELECT COUNT(*) AS TotalCount FROM TADJUSTMENT a JOIN TADJUSTMENTDETAIL b ON a.ADJUSTMENTID = b.ADJUSTMENTID JOIN MEMPLOYEE c ON b.EMPLOYEEID = c.EMPLOYEEID JOIN MUSER d ON a.CREATEDBYID = d.USERID WHERE 1 = 1 /*CRITERIA*/;"; // ── SaveAdjustment / GetAdjustment / DeleteAdjustment ────────────────────────── // Columns confirmed from GET_ADJUSTMENT_REPORT / GET_ADJUSTMENTNUMBER_SELECTLIST // above (sibling queries against the same tables in this file) — TADJUSTMENT: // ADJUSTMENTID, ADJUSTMENTNUMBER, REFERENCENUMBER, REFERENCEDATE, REMARKS, // BIZTRANSACTIONTYPEID, OUID, PERIODID, REASONID, STATUS, VERSION, CREATEDBYID, // CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID (§23 standard tenant column). // TADJUSTMENTDETAIL: ADJUSTMENTDETAILID, ADJUSTMENTID, SLNO, EMPLOYEEID, // INTIME, OUTTIME, PREVIOUSINTIME, PREVIOUSOUTTIME, DAYTYPE, PREVIOUSDAYTYPE, // LEAVEID, PREVIOUSLEAVEID, SHIFTID, PREVIOUSSHIFTID, ATTENDANCEDATE. public const string GET_EMPLOYEE_REPORTING_INFO_FOR_ADJUSTMENT = @" SELECT E.EMPLOYEEID AS EmployeeId, E.EMPLOYEECODE AS EmployeeCode, E.EMPLOYEENAME AS EmployeeName, E.REPORTINGTOEMPLOYEEID AS ReportInttoEmployeeId, RPT.PRIMARYEMAILID AS ReportInttoEmployeeMailId, RPT.PRIMARYMOBILE AS ReportInttoEmployeeMobile, E.ADMINREPORTINGTOID AS AdminReportingToId, ADM.PRIMARYEMAILID AS AdminReportingToMailId, ADM.PRIMARYMOBILE AS AdminReportingToMobile, E.PAYCONFIGURATIONID AS PayConfigurationId FROM MEMPLOYEE E LEFT JOIN MEMPLOYEE RPT ON RPT.EMPLOYEEID = E.REPORTINGTOEMPLOYEEID LEFT JOIN MEMPLOYEE ADM ON ADM.EMPLOYEEID = E.ADMINREPORTINGTOID WHERE E.EMPLOYEEID = @EmployeeId AND E.TENANTID = @TenantId"; public const string GET_PERIOD_RANGE_FOR_ADJUSTMENT = @" SELECT PERIODID AS PeriodId, FROMDATE AS FromDate, TODATE AS ToDate FROM MPERIOD WHERE PERIODID = @PeriodId AND TENANTID = @TenantId"; public const string SAVE_ADJUSTMENT = @" INSERT INTO TADJUSTMENT ( ADJUSTMENTID, ADJUSTMENTNUMBER, REFERENCENUMBER, REFERENCEDATE, REMARKS, BIZTRANSACTIONTYPEID, OUID, PERIODID, REASONID, STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, TENANTID ) VALUES ( @AdjustmentId, @AdjustmentAdjustmentNumber, @AdjustmentReferenceNumber, @AdjustmentReferenceDate, @AdjustmentRemarks, @AdjustmentBiztransactiontypeId, @OUId, @PeriodId, @ReasonId, @AdjustmentStatus, @AdjustmentVersion, @AdjustmentCreatedById, @AdjustmentCreatedOn, @AdjustmentModifiedById, @AdjustmentModifiedOn, @TenantId );"; public const string UPDATE_ADJUSTMENT = @" UPDATE TADJUSTMENT SET REFERENCENUMBER = @AdjustmentReferenceNumber, REFERENCEDATE = @AdjustmentReferenceDate, REMARKS = @AdjustmentRemarks, BIZTRANSACTIONTYPEID = @AdjustmentBiztransactiontypeId, OUID = @OUId, PERIODID = @PeriodId, REASONID = @ReasonId, STATUS = @AdjustmentStatus, VERSION = VERSION + 1, MODIFIEDBYID = @AdjustmentModifiedById, MODIFIEDON = @AdjustmentModifiedOn WHERE ADJUSTMENTID = @AdjustmentId AND TENANTID = @TenantId;"; public const string SAVE_ADJUSTMENT_DETAIL = @" INSERT INTO TADJUSTMENTDETAIL ( ADJUSTMENTDETAILID, ADJUSTMENTID, SLNO, EMPLOYEEID, INTIME, OUTTIME, PREVIOUSINTIME, PREVIOUSOUTTIME, DAYTYPE, PREVIOUSDAYTYPE, LEAVEID, PREVIOUSLEAVEID, SHIFTID, PREVIOUSSHIFTID, ATTENDANCEDATE, TENANTID ) VALUES ( @AdjustmentDetailId, @AdjustmentId, @AdjustmentDetailSlNo, @EmployeeId, @AdjustmentDetailInTime, @AdjustmentDetailOutTime, @AdjustmentDetailPreviousInTime, @AdjustmentDetailPreviousOutTime, @AdjustmentDetailDayType, @AdjustmentDetailPreviousDayType, @LeaveId, @PreviousLeaveId, @ShiftId, @PreviousShiftId, @AdjustmentDetailAttendanceDate, @TenantId );"; public const string DELETE_ADJUSTMENT_DETAIL_ONLY = @" DELETE FROM TADJUSTMENTDETAIL WHERE ADJUSTMENTID = @AdjustmentId AND TENANTID = @TenantId;"; public const string GET_ADJUSTMENT = @" SELECT a.ADJUSTMENTID AS AdjustmentId, a.ADJUSTMENTNUMBER AS AdjustmentAdjustmentNumber, a.REFERENCENUMBER AS AdjustmentReferenceNumber, a.REFERENCEDATE AS AdjustmentReferenceDate, a.REMARKS AS AdjustmentRemarks, a.BIZTRANSACTIONTYPEID AS AdjustmentBiztransactiontypeId, a.OUID AS OUId, a.PERIODID AS PeriodId, a.REASONID AS ReasonId, a.STATUS AS AdjustmentStatus, a.VERSION AS AdjustmentVersion, a.CREATEDBYID AS AdjustmentCreatedById, a.CREATEDON AS AdjustmentCreatedOn, a.MODIFIEDBYID AS AdjustmentModifiedById, a.MODIFIEDON AS AdjustmentModifiedOn FROM TADJUSTMENT a WHERE a.ADJUSTMENTID = @AdjustmentId AND a.TENANTID = @TenantId;"; public const string GET_ADJUSTMENT_DETAILS_BY_HEADER = @" SELECT b.ADJUSTMENTDETAILID AS AdjustmentDetailId, b.ADJUSTMENTID AS AdjustmentId, b.SLNO AS AdjustmentDetailSlNo, b.EMPLOYEEID AS EmployeeId, c.EMPLOYEECODE AS EmployeeCode, c.EMPLOYEENAME AS EmployeeName, b.INTIME AS AdjustmentDetailInTime, b.OUTTIME AS AdjustmentDetailOutTime, b.PREVIOUSINTIME AS AdjustmentDetailPreviousInTime, b.PREVIOUSOUTTIME AS AdjustmentDetailPreviousOutTime, b.DAYTYPE AS AdjustmentDetailDayType, b.PREVIOUSDAYTYPE AS AdjustmentDetailPreviousDayType, b.LEAVEID AS LeaveId, b.PREVIOUSLEAVEID AS PreviousLeaveId, b.SHIFTID AS ShiftId, b.PREVIOUSSHIFTID AS PreviousShiftId, b.ATTENDANCEDATE AS AdjustmentDetailAttendanceDate FROM TADJUSTMENTDETAIL b JOIN MEMPLOYEE c ON b.EMPLOYEEID = c.EMPLOYEEID WHERE b.ADJUSTMENTID = @AdjustmentId AND b.TENANTID = @TenantId;"; public const string DELETE_ADJUSTMENT_HEADER_ONLY = @" DELETE FROM TADJUSTMENT WHERE ADJUSTMENTID = @AdjustmentId AND TENANTID = @TenantId;"; // ── GetAdjustmentForImport (GB4 Adjustment.svc "Data/For/Import", // AdjustmentBLL.GetAdjustmentForImport, AdjustmentQueryBuilder.ADJUSTMENT_LOAD_FOR_IMPORT) // Returns adjustment rows scoped to the logged-in OU/Period for CSV/grid import, // ported as a typed list query (GB4 returned CSV text from a DataTable — GB5 returns // the same shape the caller can serialize/export as needed). // GB4 parity: AdjustmentQueryBuilder.ADJUSTMENT_LOAD_FOR_IMPORT. An import TEMPLATE seeded // from existing TDAILYATTENDANCE rows for the OU/period — row 1 carries the literal // 'ADJ001' placeholder AdjustmentNumber/ReferenceNumber, all other rows blank; Remarks/ // Reason are always blank; AdjustmentId is always the literal 1. Meant to be filled in // client-side and re-imported as new adjustments — NOT a listing of existing adjustments. // InTime/OutTime are returned as raw minutes (formatted to HH:mm in the DAL, matching // GB5's existing MinutesIntoTime convention rather than a SQL-side conversion function). public const string GET_ADJUSTMENT_FOR_IMPORT = @" SELECT CASE WHEN ROW_NUMBER() OVER (ORDER BY dailyatt.DAILYATTENDANCEID) = 1 THEN 'ADJ001' ELSE '' END AS AdjustmentNumber, biztype.BIZTRANSACTIONTYPECODE AS BizType, ou.ORGANIZATIONUNITCODE AS OU, period.PERIODCODE AS Period, 'ADJ001' AS ReferenceNumber, CONVERT(VARCHAR, dailyatt.ATTENDANCEDATE, 106) AS ReferenceDate, '' AS Remarks, '' AS Reason, CAST(ROW_NUMBER() OVER (ORDER BY dailyatt.DAILYATTENDANCEID) AS INT) AS Slno, employee.EMPLOYEECODE AS EmployeeCode, CONVERT(VARCHAR, dailyatt.ATTENDANCEDATE, 106) AS DetailAttendanceDate, dailyatt.INTIME AS InTimeMinutes, dailyatt.OUTTIME AS OutTimeMinutes, dailyatt.DAYTYPE AS DayType, lv.LEAVECODE AS Leave, shift.SHIFTCODE AS Shift, dailyatt.INTIME AS PreviousInTimeMinutes, dailyatt.OUTTIME AS PreviousOutTimeMinutes, dailyatt.DAYTYPE AS PreviousDayType, lv.LEAVECODE AS PreviousLeave, shift.SHIFTCODE AS PreviousShift, 1 AS AdjustmentId 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 JOIN MBIZTRANSACTIONTYPE biztype ON biztype.OUID = dailyatt.OUID AND biztype.PERIODID = @PeriodId AND biztype.BIZTRANSACTIONCLASSID = -1399999810 JOIN MORGANIZATIONUNIT ou ON ou.OUID = biztype.OUID JOIN MPERIOD period ON period.PERIODID = biztype.PERIODID WHERE dailyatt.OUID = @OUId :replacefields ORDER BY dailyatt.ATTENDANCEDATE;"; // GB4 parity: AdjustmentBLL.SaveAdjustment's Reason lookup (GenericDal.Get) // used to build the EventLog audit-trail text. public const string GET_REASON_NAME = @" SELECT REASONNAME FROM MREASON WHERE REASONID = @ReasonId; "; } }