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

namespace MMDAL.Query.Shift
{
    public static class ShiftQB
    {

        public const string GET_SHIFT = @"SELECT
                                            S.SHIFTID AS ShiftId,
                                            S.SHIFTCODE AS ShiftCode,
                                            S.SHIFTDESCRIPTION AS ShiftDescription,
                                            S.ISPRODUCTIONSHIFT AS ShiftIsProductionShift,
                                            S.ISBREAKAPPLICABLE AS ShiftIsBreakApplicable,
                                            S.STARTTIME AS ShiftStartTime,
                                            S.ENDTIME AS ShiftEndTime,
                                            S.SHIFTMINUTES AS ShiftShiftMinutes,
                                            S.WORKINGMINUTES AS ShiftWorkingMinutes,
                                            S.FIRSTHALFENDTIME AS ShiftFirstHalfEndTime,
                                            S.BREAKMINUTES AS ShiftBreakMinutes,
                                            S.SECONDHALFSTARTTIME AS ShiftSecondHalfStartTime,
                                            S.GRACEMINUTES AS ShiftGraceMinutes,
                                            S.REMARKS AS ShiftRemarks,
                                            S.SORTORDER AS ShiftSortOrder,
                                            S.STATUS AS ShiftStatus,
                                            S.VERSION AS ShiftVersion,
                                            S.SOURCETYPE AS ShiftSourceType,
                                            S.FULLDAYMINUTES AS ShiftFullDayMinutes,
                                            S.HALFDAYMINUTES AS ShiftHalfDayMinutes,
                                            S.CUTOFFMINUTES AS ShiftCutOffMinutes,
                                            S.CUTOFFMINUTESOUT AS ShiftCutOffMinutesOut,
                                            S.WORKTIMEMETHOD AS ShiftWorkTimeMethod,
                                            S.PUNCHTOATTENDANCETYPE AS ShiftPunchToAttendanceType,
                                            S.CREATEDBYID AS ShiftCreatedById,
                                            UC.USERNAME AS ShiftCreatedByName,
                                            S.CREATEDON AS ShiftCreatedOn,
                                            S.MODIFIEDBYID AS ShiftModifiedById,
                                            UM.USERNAME AS ShiftModifiedByName,
                                            S.MODIFIEDON AS ShiftModifiedOn,
                                            S.TENANTID AS TenantId
                                        FROM MSHIFT S
                                        LEFT JOIN MUSER UC ON S.CREATEDBYID = UC.USERID
                                        LEFT JOIN MUSER UM ON S.MODIFIEDBYID = UM.USERID
                                        WHERE S.SHIFTID = @ShiftId;";


        public const string SAVE_SHIFT = @"INSERT INTO MSHIFT (
                                                    SHIFTID,
                                                    SHIFTCODE,
                                                    SHIFTDESCRIPTION,
                                                    ISPRODUCTIONSHIFT,
                                                    ISBREAKAPPLICABLE,
                                                    STARTTIME,
                                                    ENDTIME,
                                                    SHIFTMINUTES,
                                                    WORKINGMINUTES,
                                                    FIRSTHALFENDTIME,
                                                    BREAKMINUTES,
                                                    SECONDHALFSTARTTIME,
                                                    GRACEMINUTES,
                                                    REMARKS,
                                                    SORTORDER,
                                                    STATUS,
                                                    VERSION,
                                                    CREATEDBYID,
                                                    CREATEDON,
                                                    MODIFIEDBYID,
                                                    MODIFIEDON,
                                                    SOURCETYPE,
                                                    FULLDAYMINUTES,
                                                    HALFDAYMINUTES,
                                                    TENANTID,
                                                    CUTOFFMINUTES,
                                                    CUTOFFMINUTESOUT,
                                                    WORKTIMEMETHOD,
                                                    PUNCHTOATTENDANCETYPE
                                                )
                                                VALUES (
                                                    @ShiftId,
                                                    @ShiftCode,
                                                    @ShiftDescription,
                                                    @ShiftIsProductionShift,
                                                    @ShiftIsBreakApplicable,
                                                    @ShiftStartTime,
                                                    @ShiftEndTime,
                                                    @ShiftShiftMinutes,
                                                    @ShiftWorkingMinutes,
                                                    @ShiftFirstHalfEndTime,
                                                    @ShiftBreakMinutes,
                                                    @ShiftSecondHalfStartTime,
                                                    @ShiftGraceMinutes,
                                                    @ShiftRemarks,
                                                    @ShiftSortOrder,
                                                    @ShiftStatus,
                                                    @ShiftVersion,
                                                    @ShiftCreatedById,
                                                    @ShiftCreatedOn,
                                                    @ShiftModifiedById,
                                                    @ShiftModifiedOn,
                                                    @ShiftSourceType,
                                                    @ShiftFullDayMinutes,
                                                    @ShiftHalfDayMinutes,
                                                    @TenantId,
                                                    @ShiftCutOffMinutes,
                                                    @ShiftCutOffMinutesOut,
                                                    @ShiftWorkTimeMethod,
                                                    @ShiftPunchToAttendanceType
                                                );";


        public const string UPDATE_SHIFT = @"UPDATE MSHIFT
                                            SET
                                                SHIFTCODE = @ShiftCode,
                                                SHIFTDESCRIPTION = @ShiftDescription,
                                                ISPRODUCTIONSHIFT = @ShiftIsProductionShift,
                                                ISBREAKAPPLICABLE = @ShiftIsBreakApplicable,
                                                STARTTIME = @ShiftStartTime,
                                                ENDTIME = @ShiftEndTime,
                                                SHIFTMINUTES = @ShiftShiftMinutes,
                                                WORKINGMINUTES = @ShiftWorkingMinutes,
                                                FIRSTHALFENDTIME = @ShiftFirstHalfEndTime,
                                                BREAKMINUTES = @ShiftBreakMinutes,
                                                SECONDHALFSTARTTIME = @ShiftSecondHalfStartTime,
                                                GRACEMINUTES = @ShiftGraceMinutes,
                                                REMARKS = @ShiftRemarks,
                                                SORTORDER = @ShiftSortOrder,
                                                STATUS = @ShiftStatus,
                                                VERSION = @ShiftVersion,
                                                MODIFIEDBYID = @ShiftModifiedById,
                                                MODIFIEDON = @ShiftModifiedOn,
                                                SOURCETYPE = @ShiftSourceType,
                                                FULLDAYMINUTES = @ShiftFullDayMinutes,
                                                HALFDAYMINUTES = @ShiftHalfDayMinutes,
                                                TENANTID = @TenantId,
                                                CUTOFFMINUTES = @ShiftCutOffMinutes,
                                                CUTOFFMINUTESOUT = @ShiftCutOffMinutesOut,
                                                WORKTIMEMETHOD = @ShiftWorkTimeMethod,
                                                PUNCHTOATTENDANCETYPE = @ShiftPunchToAttendanceType
                                            WHERE SHIFTID = @ShiftId;";


        public const string DELETE_SHIFT = @"DELETE FROM MSHIFT 
                                            WHERE SHIFTID = @ShiftId;";

        public const string GET_SELECTLIST_SHIFT = @"SELECT
                                                    S.SHIFTID           AS Id,
                                                    S.SHIFTCODE         AS Code,
                                                    S.SHIFTDESCRIPTION  AS Description
                                                FROM MSHIFT S
                                                WHERE S.STATUS = 1
                                                    AND (@code        = '' OR S.SHIFTCODE        LIKE '%' + @code        + '%')
                                                    AND (@name        = '' OR S.SHIFTDESCRIPTION LIKE '%' + @name        + '%')
                                                    AND (@description = '' OR S.SHIFTDESCRIPTION LIKE '%' + @description + '%');";
        //public const string GET_SELECTLIST_SHIFT = @"SELECT
        //                                                S.SHIFTID            AS Id,
        //                                                S.SHIFTCODE          AS Code,
        //                                                S.SHIFTDESCRIPTION   AS Description
        //                                            FROM MSHIFT S
        //                                            WHERE S.STATUS = 1;";
    }
}