// Required indexes:
//   TRESOURCEPUNCH: IX_TRESOURCEPUNCH_EMPLOYEE_DATETIME (EMPLOYEEID, PUNCHDATETIME, TENANTID)
namespace MMDAL.Query.Production
{
    public static class ResourcePunchQB
    {
        public const string SAVE_RESOURCE_PUNCH_SQL = @"INSERT INTO TRESOURCEPUNCH
                                                        (
                                                            RESOURCEID,
                                                            EMPLOYEEID,
                                                            MACHINEID,
                                                            WORKORDERID,
                                                            ALLOCATIONID,
                                                            TASKID,
                                                            PUNCHDATETIME,
                                                            STATUS,
                                                            QUANTITY,
                                                            REMARKS,
                                                            DEVICEID,
                                                            GPSLAT,
                                                            GPSLON,
                                                            CAPTUREMODE
                                                        )
                                                        VALUES
                                                        (
                                                            @ResourceId,
                                                            @EmployeeId,
                                                            @MachineId,
                                                            @WorkOrderId,
                                                            @AllocationId,
                                                            @TaskId,
                                                            @PunchDateTime,
                                                            @Status,
                                                            @Quantity,
                                                            @Remarks,
                                                            @DeviceId,
                                                            @GpsLat,
                                                            @GpsLon,
                                                            @CaptureMode
                                                        );";

        public const string SAVE_RESOURCE_PUNCH_PG = @"INSERT INTO TRESOURCEPUNCH
                                                        (
                                                            RESOURCEID,
                                                            EMPLOYEEID,
                                                            MACHINEID,
                                                            WORKORDERID,
                                                            ALLOCATIONID,
                                                            TASKID,
                                                            PUNCHDATETIME,
                                                            STATUS,
                                                            QUANTITY,
                                                            REMARKS,
                                                            DEVICEID,
                                                            GPSLAT,
                                                            GPSLON,
                                                            CAPTUREMODE
                                                        )
                                                    VALUES
                                                        (
                                                            :ResourceId,
                                                            :EmployeeId,
                                                            :MachineId,
                                                            :WorkOrderId,
                                                            :AllocationId,
                                                            :TaskId,
                                                            :PunchDateTime,
                                                            :Status,
                                                            :Quantity,
                                                            :Remarks,
                                                            :DeviceId,
                                                            :GpsLat,
                                                            :GpsLon,
                                                            :CaptureMode
                                                        );";

        public const string GET_ACTIVE_PUNCH_FOR_EMPLOYEE_SQL = @"
            SELECT RESOURCEPUNCHID, MACHINEID, PUNCHDATETIME
            FROM   TRESOURCEPUNCH
            WHERE  EMPLOYEEID    = @EmployeeId
            AND    STATUS        = 0
            AND    PUNCHDATETIME > DATEADD(HOUR, -24, GETUTCDATE());";

        public const string GET_ACTIVE_PUNCH_FOR_EMPLOYEE_PG = @"
            SELECT RESOURCEPUNCHID, MACHINEID, PUNCHDATETIME
            FROM   TRESOURCEPUNCH
            WHERE  EMPLOYEEID    = :EmployeeId
            AND    STATUS        = 0
            AND    PUNCHDATETIME > NOW() AT TIME ZONE 'UTC' - INTERVAL '24 hours';";

        public const string GET_RESOURCE_PUNCH_BY_WO_SQL = @"
            SELECT rp.RESOURCEPUNCHID, rp.EMPLOYEEID,
                   ISNULL(e.EMPLOYEENAME, '') AS EmployeeName,
                   rp.MACHINEID, rp.WORKCENTERID, rp.PUNCHDATETIME,
                   rp.STATUS AS PunchStatus, rp.QUANTITY, rp.CAPTUREMODE
            FROM   TRESOURCEPUNCH rp
            LEFT   JOIN MEMPLOYEE e ON e.EMPLOYEEID = rp.EMPLOYEEID AND e.TENANTID = rp.TENANTID
            WHERE  rp.INDENTDETAILID = @IndentDetailId
            ORDER  BY rp.PUNCHDATETIME;";

        public const string GET_RESOURCE_PUNCH_BY_WO_PG = @"
            SELECT rp.RESOURCEPUNCHID, rp.EMPLOYEEID,
                   COALESCE(e.EMPLOYEENAME, '') AS ""EmployeeName"",
                   rp.MACHINEID, rp.WORKCENTERID, rp.PUNCHDATETIME,
                   rp.STATUS AS ""PunchStatus"", rp.QUANTITY, rp.CAPTUREMODE
            FROM   TRESOURCEPUNCH rp
            LEFT   JOIN MEMPLOYEE e ON e.EMPLOYEEID = rp.EMPLOYEEID AND e.TENANTID = rp.TENANTID
            WHERE  rp.INDENTDETAILID = :IndentDetailId
            ORDER  BY rp.PUNCHDATETIME;";
    }
}