using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PayRollDAL.Query.OverTime
{
    public static class OverTimeQB
    {
        public const string GET_SELECTLIST_OVERTIME = @"
                            WITH PagedOverTime AS (
                            SELECT
                            OT.OVERTIMEID AS Id,
                            OT.OVERTIMENUMBER AS OverTimeNumber,
                            OT.OVERTIMEDATE AS OverTimeDate,
                            OT.FORTHEDATE AS FortheDate,
                            E.EMPLOYEECODE AS EmployeeCode,
                            E.EMPLOYEENAME AS EmployeeName,
                            ROW_NUMBER() OVER (ORDER BY OT.OVERTIMENUMBER DESC) AS RowNum
                            FROM TOVERTIME OT
                            INNER JOIN TOVERTIMEDETAIL OTD ON OTD.OVERTIMEID = OT.OVERTIMEID
                            INNER JOIN MEMPLOYEE E ON E.EMPLOYEEID = OTD.EMPLOYEEID
                            WHERE 1 = 1
                            AND (@biztransactiontypeid = -1 OR OT.BIZTRANSACTIONTYPEID = @biztransactiontypeid)
                            AND (@overtimenumber = '' OR OT.OVERTIMENUMBER LIKE '%' + @overtimenumber + '%')
                            AND (@employeecode = '' OR E.EMPLOYEECODE LIKE '%' + @employeecode + '%')
                            AND (@employeename = '' OR E.EMPLOYEENAME LIKE '%' + @employeename + '%')
                            AND (@employeeid = -1 OR OTD.EMPLOYEEID = @employeeid)
                            AND (@overtimefromdate IS NULL OR OT.FORTHEDATE >= @overtimefromdate)
                            AND (@overtimetodate IS NULL OR OT.FORTHEDATE <= @overtimetodate)
                            )
                            SELECT
                            Id,
                            OverTimeNumber,
                            OverTimeDate,
                            FortheDate,
                            EmployeeCode,
                            EmployeeName
                            FROM PagedOverTime
                            WHERE
                            (@firstnumber = -1 AND @maxresult = -1)
                            OR
                            (RowNum BETWEEN @firstnumber AND @maxresult);
                            ";

        // ─────────────────────────────────────────────────────────────────
        // SaveOverTime — header + detail
        // ─────────────────────────────────────────────────────────────────

        public const string SAVE_OVERTIME = @"
INSERT INTO TOVERTIME
(
    OVERTIMEID, BIZTRANSACTIONTYPEID, OUID, PERIODID, OVERTIMENUMBER, OVERTIMEDATE, FORTHEDATE,
    REMARKS, STATUS, VERSION, SOURCETYPE, FUNCTIONALREPORTINGTO, ADMINREPORTINGTO,
    PAYCONFIGURATIONID, OUWISEDEPARTMENTWISEHEADID, TENANTID,
    CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
)
VALUES
(
    @OverTimeId, @BIZTransactionTypeId, @OUId, @PeriodId, @OverTimeOverTimeNumber, @OverTimeOverTimeDate, @OverTimeForTheDate,
    @OverTimeRemarks, @OverTimeStatus, @OverTimeVersion, @OverTimeSourcetype, @FunctionalReportingTo, @AdminReportingTo,
    @PayConfigurationId, @OuWiseDepartmentWiseHeadId, @TenantId,
    @UserId, @CreatedOn, @UserId, @ModifiedOn
)";

        public const string UPDATE_OVERTIME = @"
UPDATE TOVERTIME
SET
    BIZTRANSACTIONTYPEID       = @BIZTransactionTypeId,
    OUID                       = @OUId,
    PERIODID                   = @PeriodId,
    OVERTIMEDATE               = @OverTimeOverTimeDate,
    FORTHEDATE                 = @OverTimeForTheDate,
    REMARKS                    = @OverTimeRemarks,
    STATUS                     = @OverTimeStatus,
    VERSION                    = VERSION + 1,
    SOURCETYPE                 = @OverTimeSourcetype,
    FUNCTIONALREPORTINGTO      = @FunctionalReportingTo,
    ADMINREPORTINGTO           = @AdminReportingTo,
    PAYCONFIGURATIONID         = @PayConfigurationId,
    OUWISEDEPARTMENTWISEHEADID = @OuWiseDepartmentWiseHeadId,
    MODIFIEDBYID               = @UserId,
    MODIFIEDON                 = @ModifiedOn
WHERE
    OVERTIMEID = @OverTimeId
AND TENANTID   = @TenantId";

        public const string SAVE_OVERTIME_DETAIL = @"
INSERT INTO TOVERTIMEDETAIL
(
    OVERTIMEDETAILID, OVERTIMEID, SLNO, EMPLOYEEID,
    FROMTIME, TOTIME, DURATION, FACTOR, FACTORHRS, ACTUALHOURS, COMPOFFDAYS, TENANTID
)
VALUES
(
    @OverTimeDetailId, @OverTimeId, @OverTimeDetailSlNo, @EmployeeId,
    @OverTimeDetailFromTime, @OverTimeDetailToTime, @OverTimeDetailDuration, @OverTimeDetailFactor,
    @OverTimeDetailFactorHrs, @OverTimeDetailActualHours, @OverTimeDetailCompOffDays, @TenantId
)";

        // Mirrors GB4 OverTimeDAL.DeleteOverTimeDetails(int) — parameterized, no string concatenation.
        public const string DELETE_OVERTIME_DETAIL_ONLY = @"
DELETE FROM TOVERTIMEDETAIL WHERE OVERTIMEID = @OverTimeId AND TENANTID = @TenantId";

        // Mirrors GB4 OverTimeDAL.DeleteOverTimeDetail(int) — also removes the header (used by Delete()).
        public const string DELETE_OVERTIME_HEADER_ONLY = @"
DELETE FROM TOVERTIME WHERE OVERTIMEID = @OverTimeId AND TENANTID = @TenantId";

        // Duplicate-entry check: CHECK_ENTRIES_IN_OVERTIMEDETAIL from GB4 OverTimeQueryBuilder.
        public const string CHECK_ENTRIES_IN_OVERTIMEDETAIL = @"
SELECT TOP 1
    OT.OVERTIMENUMBER   AS OverTimeOverTimeNumber,
    OT.FORTHEDATE       AS OverTimeForTheDate,
    E.EMPLOYEECODE      AS EmployeeCode,
    E.EMPLOYEENAME      AS EmployeeName
FROM   TOVERTIMEDETAIL OTD
INNER JOIN TOVERTIME  OT ON OT.OVERTIMEID = OTD.OVERTIMEID
INNER JOIN MEMPLOYEE  E  ON E.EMPLOYEEID  = OTD.EMPLOYEEID
WHERE  OTD.EMPLOYEEID  = @EmployeeId
AND    OT.FORTHEDATE   = @ForTheDate
AND    OT.TENANTID     = @TenantId";

        // Single-record header GET, including period/OU/biztype descriptive columns.
        public const string GET_OVERTIME = @"
SELECT
    OT.OVERTIMEID                    AS OverTimeId,
    OT.BIZTRANSACTIONTYPEID          AS BIZTransactionTypeId,
    BTT.BIZTRANSACTIONTYPECODE       AS BIZTransactionTypeCode,
    BTT.BIZTRANSACTIONTYPENAME       AS BIZTransactionTypeName,
    OT.OUID                          AS OUId,
    OU.ORGANIZATIONUNITCODE          AS OUCode,
    OU.ORGANIZATIONUNITNAME          AS OUName,
    OT.PERIODID                      AS PeriodId,
    P.PERIODCODE                     AS PeriodCode,
    P.PERIODNAME                     AS PeriodName,
    OT.OVERTIMENUMBER                AS OverTimeOverTimeNumber,
    OT.OVERTIMEDATE                  AS OverTimeOverTimeDate,
    OT.FORTHEDATE                    AS OverTimeForTheDate,
    OT.REMARKS                       AS OverTimeRemarks,
    OT.STATUS                        AS OverTimeStatus,
    OT.VERSION                       AS OverTimeVersion,
    OT.SOURCETYPE                    AS OverTimeSourcetype,
    OT.CREATEDBYID                   AS OverTimeCreatedById,
    OT.CREATEDON                     AS OverTimeCreatedOn,
    OT.MODIFIEDBYID                  AS OverTimeModifiedById,
    OT.MODIFIEDON                    AS OverTimeModifiedOn
FROM   TOVERTIME OT
LEFT JOIN MBIZTRANSACTIONTYPE BTT ON BTT.BIZTRANSACTIONTYPEID = OT.BIZTRANSACTIONTYPEID
LEFT JOIN MORGANIZATIONUNIT   OU  ON OU.OUID                  = OT.OUID
LEFT JOIN MPERIOD             P   ON P.PERIODID               = OT.PERIODID
WHERE  OT.OVERTIMEID = @OverTimeId
AND    OT.TENANTID   = @TenantId";

        public const string GET_OVERTIME_DETAILS_BY_HEADER = @"
SELECT
    D.OVERTIMEDETAILID      AS OverTimeDetailId,
    D.OVERTIMEID            AS OverTimeId,
    D.SLNO                  AS OverTimeDetailSlNo,
    D.EMPLOYEEID            AS EmployeeId,
    E.EMPLOYEECODE          AS EmployeeCode,
    E.EMPLOYEENAME          AS EmployeeName,
    D.FROMTIME              AS OverTimeDetailFromTime,
    D.TOTIME                AS OverTimeDetailToTime,
    D.DURATION              AS OverTimeDetailDuration,
    D.FACTOR                AS OverTimeDetailFactor,
    D.FACTORHRS             AS OverTimeDetailFactorHrs,
    D.ACTUALHOURS           AS OverTimeDetailActualHours,
    D.COMPOFFDAYS           AS OverTimeDetailCompOffDays
FROM   TOVERTIMEDETAIL D
LEFT JOIN MEMPLOYEE E ON E.EMPLOYEEID = D.EMPLOYEEID
WHERE  D.OVERTIMEID = @OverTimeId
AND    D.TENANTID   = @TenantId
ORDER  BY D.SLNO";

        // Narrow reporting-to / pay-config lookup for a single employee (SaveOverTime enrichment).
        // Justification (MigrationClaude.md §15 decision table): only 6 scalar columns are
        // needed here — a JOIN-style narrow projection, not a re-implementation of
        // IEmployeeDAL's full employee query (which returns a pre-serialized JSON blob anyway).
        public const string GET_EMPLOYEE_REPORTING_INFO = @"
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,
    E.WORKOUID                   AS WorkOUId,
    E.DEPARTMENTID               AS DepartmentId,
    DEPT.DEPARTMENTHEADID        AS DepartmentHeadId
FROM   MEMPLOYEE E
LEFT JOIN MEMPLOYEE RPT   ON RPT.EMPLOYEEID = E.REPORTINGTOEMPLOYEEID
LEFT JOIN MEMPLOYEE ADM   ON ADM.EMPLOYEEID = E.ADMINREPORTINGTOID
LEFT JOIN MDEPARTMENT DEPT ON DEPT.DEPARTMENTID = E.DEPARTMENTID
WHERE  E.EMPLOYEEID = @EmployeeId
AND    E.TENANTID   = @TenantId";

        // OuWiseDepartmentWiseHead lookup — GB4 wraps this in try/catch because the
        // Z_DEPTWISEOUWISEHEAD addon table may not exist in every deployment. GB5 DAL
        // preserves the same try/catch guard (parameterized, no string concatenation).
        public const string GET_OUWISE_DEPARTMENTWISE_HEAD = @"
SELECT INCHARGEID FROM Z_DEPTWISEOUWISEHEAD WHERE OUID = @OUId AND DEPID = @DepartmentId";

        // Period date-range validation for SaveOverTime ("Overtime Date is Not in Financial Range").
        public const string GET_PERIOD_RANGE = @"
SELECT PERIODID AS PeriodId, FROMDATE AS FromDate, TODATE AS ToDate
FROM   MPERIOD
WHERE  PERIODID = @PeriodId
AND    TENANTID = @TenantId";

        // BizTransactionType lookup for VNO generation type check.
        public const string GET_BIZTRANSACTIONTYPE_NOGEN = @"
SELECT BIZTRANSACTIONTYPEID AS BizTransactionTypeId, NOGENERATIONTYPE AS NOGenerationType
FROM   MBIZTRANSACTIONTYPE
WHERE  BIZTRANSACTIONTYPEID = @BizTransactionTypeId
AND    TENANTID              = @TenantId";

        // ─────────────────────────────────────────────────────────────────
        // LoadOvertimeRecords — GB4 GET_OVERTIME_RECORDS, parameterized.
        // Base query joins MEMPLOYEE + TDAILYATTENDANCE + MSHIFT + MWORKTYPE for ForTheDate.
        // Dynamic filters appended by the DAL (Department/WorkType/EmployeeType/StaffType/
        // OtType/EmployerStatus/Status/WorkOU/ReportingTo) mirror GB4's ApplyOvertimeRecords.
        // ─────────────────────────────────────────────────────────────────
        public const string GET_OVERTIME_RECORDS = @"
SELECT
    emp.EMPLOYEEID           AS EmployeeId,
    emp.EMPLOYEECODE         AS EmployeeCode,
    emp.EMPLOYEENAME         AS EmployeeName,
    emp.WORKOUID             AS OUId,
    a.SHIFTID                AS ShiftId,
    sh.SHIFTDESCRIPTION      AS ShiftDescription,
    emp.WORKTYPEID           AS WorkTypeId,
    wt.WORKTYPENAME          AS WorkTypeName,
    a.ATTENDANCEDATE         AS OverTimeForTheDate,
    emp.TENANTID             AS TenantId
FROM   MEMPLOYEE emp
INNER JOIN TDAILYATTENDANCE a ON a.EMPLOYEEID = emp.EMPLOYEEID
LEFT JOIN MSHIFT     sh ON sh.SHIFTID    = a.SHIFTID
LEFT JOIN MWORKTYPE  wt ON wt.WORKTYPEID = emp.WORKTYPEID
WHERE  a.ATTENDANCEDATE = @forthedate
AND    emp.TENANTID     = @TenantId
{DYNAMIC_FILTERS}
ORDER BY emp.SORTORDER ASC";

        // ─────────────────────────────────────────────────────────────────
        // LoadDailyAttendanceDataToOverTime — GB4 LOAD_OVER_TINE_FROM_DAILTYATTENDANCE.
        // Same-day-only load (PeriodFromDate == PeriodToDate enforced in BLL/DAL).
        // Dynamic filters (EmployeeId/ShiftId/PayConfigurationId) appended by the DAL,
        // plus the mandatory a.ouid = @ouid filter carried over from GB4 (LoginDTO.WorkOUId).
        // ─────────────────────────────────────────────────────────────────
        public const string LOAD_OVER_TIME_FROM_DAILY_ATTENDANCE = @"
SELECT
    a.EMPLOYEEID             AS EmployeeId,
    emp.EMPLOYEECODE         AS EmployeeCode,
    emp.EMPLOYEENAME         AS EmployeeName,
    a.OTHOURS                AS OTHours,
    a.INTIME                 AS INTIME,
    a.OUTTIME                AS OUTTIME,
    (CASE WHEN a.OUTTIME - a.INTIME >= 0
          THEN a.OUTTIME - a.INTIME
          ELSE a.OUTTIME - a.INTIME + 1440 END)   AS TotalDuration,
    a.SHIFTID                AS ShiftId,
    sh.SHIFTCODE             AS ShiftCode,
    sh.SHIFTDESCRIPTION      AS ShiftDescription,
    emp.WORKTYPEID           AS WorkTypeId,
    wt.WORKTYPECODE          AS WorkTypeCode,
    wt.WORKTYPENAME          AS WorkTypeName,
    b.PAYCONFIGURATIONID     AS PayConfigurationId,
    emp.TENANTID             AS TenantId
FROM   TDAILYATTENDANCE a
INNER JOIN MEMPLOYEE emp        ON emp.EMPLOYEEID = a.EMPLOYEEID
LEFT JOIN  MPAYCONFIGURATION b  ON b.PAYCONFIGURATIONID = emp.PAYCONFIGURATIONID
LEFT JOIN  MSHIFT sh            ON sh.SHIFTID = a.SHIFTID
LEFT JOIN  MWORKTYPE wt         ON wt.WORKTYPEID = emp.WORKTYPEID
WHERE  emp.TENANTID = @TenantId
{DYNAMIC_FILTERS}
AND    a.OUID = @ouid
ORDER BY emp.EMPLOYEECODE ASC";

        // ─────────────────────────────────────────────────────────────────
        // PostAutoOverTime support queries
        // ─────────────────────────────────────────────────────────────────

        // Finds the OT BizTransactionType configured for the given OU + Period.
        public const string GET_BIZTYPE_FOR_OVERTIME = @"
SELECT BIZTRANSACTIONTYPEID AS BizTransactionTypeId
FROM   MBIZTRANSACTIONTYPE
WHERE  BIZTRANSACTIONTYPECLASSID = -1399999973
AND    OUID      = @OUId
AND    PERIODID  = @PeriodId
AND    TENANTID  = @TenantId";

        // Employees with attendance-based OT hours for a given OU + attendance date.
        // GB4 parity fix: GB4's GET_EMPLOYEE_WITH_OVERTIME also filters
        // A.PREVIOUSDAYTYPE NOT IN (2, 9) — excluding employees whose previous day
        // was a WeeklyOff(2) or Holiday(9), which should not roll into auto-posted OT.
        // This was dropped in the initial migration; restored here.
        public const string GET_EMPLOYEE_WITH_OVERTIME = @"
SELECT
    a.EMPLOYEEID       AS EmployeeId,
    emp.EMPLOYEECODE   AS EmployeeCode,
    emp.EMPLOYEENAME   AS EmployeeName,
    a.OTHOURS          AS DailyAttOtHrs,
    a.INTIME           AS DailyInTime,
    a.OUTTIME          AS DailyOutTime
FROM   TDAILYATTENDANCE a
INNER JOIN MEMPLOYEE emp ON emp.EMPLOYEEID = a.EMPLOYEEID
WHERE  a.OUID              = @OUId
AND    a.ATTENDANCEDATE    = @AttendanceDate
AND    a.OTHOURS           > 0
AND    a.PREVIOUSDAYTYPE NOT IN (2, 9)
AND    emp.TENANTID        = @TenantId";

        // Clears prior auto-posted OT entries for the given OU/date, matched by the
        // GB4 sentinel remark 'Posted from Auto program..'. Detail-addon table is not
        // ported (see MigrationClaude.md task notes — OverTimeDetailAddon dropped), so
        // only detail + header are deleted here.
        public const string DELETE_AUTO_POSTED_OVERTIME_DETAIL = @"
DELETE D
FROM   TOVERTIMEDETAIL D
INNER JOIN TOVERTIME H ON H.OVERTIMEID = D.OVERTIMEID
WHERE  H.OUID       = @OUId
AND    H.PERIODID   = @PeriodId
AND    H.FORTHEDATE = @AttendanceDate
AND    H.REMARKS    = 'Posted from Auto program..'
AND    H.TENANTID   = @TenantId";

        public const string DELETE_AUTO_POSTED_OVERTIME_HEADER = @"
DELETE FROM TOVERTIME
WHERE  OUID       = @OUId
AND    PERIODID   = @PeriodId
AND    FORTHEDATE = @AttendanceDate
AND    REMARKS    = 'Posted from Auto program..'
AND    TENANTID   = @TenantId";

        // ─────────────────────────────────────────────────────────────────
        // GetOverTimeDetailReport — GB4 IOverTime.GetOverTimeDetailReport
        // (route "/OverTimeDetail"), backed by GB4's GET_OVERTIMEDETAILS HQL.
        // Ported here as raw parameterized SQL against GB5's TOVERTIME /
        // TOVERTIMEDETAIL / MEMPLOYEE tables (same tables/columns already
        // used above by GET_SELECTLIST_OVERTIME / GET_OVERTIME / GET_OVERTIME_DETAILS_BY_HEADER).
        // This module is SQL-Server-only (no PostgreSQL dialect variant exists
        // anywhere else in this file), so no _PG counterpart is added here.
        // ─────────────────────────────────────────────────────────────────
        public const string GET_OVERTIME_DETAIL_REPORT = @"
WITH PagedOverTimeDetail AS (
    SELECT
        OT.OVERTIMEID                AS OverTimeId,
        OT.OVERTIMENUMBER            AS OverTimeOverTimeNumber,
        OT.FORTHEDATE                AS OverTimeForTheDate,
        OT.REMARKS                   AS OverTimeRemarks,
        OTD.EMPLOYEEID               AS EmployeeId,
        E.EMPLOYEECODE               AS EmployeeCode,
        E.EMPLOYEENAME               AS EmployeeName,
        E.DEPARTMENTID               AS DepartmentId,
        D.DEPARTMENTNAME             AS DepartmentName,
        E.DESIGNATIONID              AS DesignationId,
        DES.DESIGNATIONCODE          AS DesignationCode,
        DES.DESIGNATIONNAME          AS DesignationName,
        E.WORKTYPEID                 AS WorkTypeId,
        WT.WORKTYPECODE              AS WorkTypeCode,
        WT.WORKTYPENAME              AS WorkTypeName,
        OT.PERIODID                  AS PayPeriodId,
        P.PERIODCODE                 AS PayPeriodCode,
        P.PERIODNAME                 AS PayPeriodName,
        E.SHIFTID                    AS ShiftId,
        SH.SHIFTCODE                 AS ShiftCode,
        SH.SHIFTDESCRIPTION          AS ShiftDescription,
        OTD.DURATION                 AS OverTimeDetailDuration,
        OTD.FACTOR                   AS OverTimeDetailFactor,
        OTD.FACTORHRS                AS OverTimeDetailFactorHrs,
        OTD.ACTUALHOURS              AS OverTimeDetailActualOTHours,
        OTD.FROMTIME                 AS FromTime,
        OTD.TOTIME                   AS ToTime,
        ROW_NUMBER() OVER (ORDER BY OT.FORTHEDATE DESC, OT.OVERTIMENUMBER DESC) AS RowNum
    FROM   TOVERTIME OT
    INNER JOIN TOVERTIMEDETAIL OTD ON OTD.OVERTIMEID   = OT.OVERTIMEID
    INNER JOIN MEMPLOYEE E         ON E.EMPLOYEEID     = OTD.EMPLOYEEID
    LEFT JOIN  MDEPARTMENT D       ON D.DEPARTMENTID   = E.DEPARTMENTID
    LEFT JOIN  MDESIGNATION DES    ON DES.DESIGNATIONID = E.DESIGNATIONID
    LEFT JOIN  MWORKTYPE WT        ON WT.WORKTYPEID    = E.WORKTYPEID
    LEFT JOIN  MSHIFT SH           ON SH.SHIFTID       = E.SHIFTID
    LEFT JOIN  MPERIOD P           ON P.PERIODID       = OT.PERIODID
    WHERE  OT.TENANTID = @tenantid
    AND    (@ouid = -1 OR OT.OUID = @ouid)
    AND    (@biztransactiontypeid = -1 OR OT.BIZTRANSACTIONTYPEID = @biztransactiontypeid)
    AND    (@employeeid = -1 OR OTD.EMPLOYEEID = @employeeid)
    AND    (@departmentid = -1 OR E.DEPARTMENTID = @departmentid)
    AND    (@designationid = -1 OR E.DESIGNATIONID = @designationid)
    AND    (@worktypeid = -1 OR E.WORKTYPEID = @worktypeid)
    AND    (@shiftid = -1 OR E.SHIFTID = @shiftid)
    AND    (@periodfromdate IS NULL OR OT.FORTHEDATE >= @periodfromdate)
    AND    (@periodtodate IS NULL OR OT.FORTHEDATE <= @periodtodate)
)
SELECT
    OverTimeId, OverTimeOverTimeNumber, OverTimeForTheDate, OverTimeRemarks,
    EmployeeId, EmployeeCode, EmployeeName,
    DepartmentId, DepartmentName,
    DesignationId, DesignationCode, DesignationName,
    WorkTypeId, WorkTypeCode, WorkTypeName,
    PayPeriodId, PayPeriodCode, PayPeriodName,
    ShiftId, ShiftCode, ShiftDescription,
    OverTimeDetailDuration, OverTimeDetailFactor, OverTimeDetailFactorHrs, OverTimeDetailActualOTHours,
    FromTime, ToTime
FROM   PagedOverTimeDetail
WHERE  (@firstnumber = -1 AND @maxresult = -1)
   OR  (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string GET_OVERTIME_DETAIL_REPORT_COUNT = @"
SELECT COUNT(*)
FROM   TOVERTIME OT
INNER JOIN TOVERTIMEDETAIL OTD ON OTD.OVERTIMEID = OT.OVERTIMEID
INNER JOIN MEMPLOYEE E         ON E.EMPLOYEEID   = OTD.EMPLOYEEID
WHERE  OT.TENANTID = @tenantid
AND    (@ouid = -1 OR OT.OUID = @ouid)
AND    (@biztransactiontypeid = -1 OR OT.BIZTRANSACTIONTYPEID = @biztransactiontypeid)
AND    (@employeeid = -1 OR OTD.EMPLOYEEID = @employeeid)
AND    (@departmentid = -1 OR E.DEPARTMENTID = @departmentid)
AND    (@designationid = -1 OR E.DESIGNATIONID = @designationid)
AND    (@worktypeid = -1 OR E.WORKTYPEID = @worktypeid)
AND    (@shiftid = -1 OR E.SHIFTID = @shiftid)
AND    (@periodfromdate IS NULL OR OT.FORTHEDATE >= @periodfromdate)
AND    (@periodtodate IS NULL OR OT.FORTHEDATE <= @periodtodate);";
    }
}