Untitled

mail@pastecode.io avatar
unknown
plain_text
a year ago
43 kB
2
Indexable
Never
CREATE   PROCEDURE [dbo].[AcceptShiftSwap]
    @ShiftSwapId INT,
    @WorkerId UNIQUEIDENTIFIER 
AS
BEGIN

    DECLARE @RC INT = 0
    SET @RC = 
        (
            select count(*) 
            FROM ShiftSwap sw 
            where 
            sw.Id=@ShiftSwapId 
            and (sw.ApprovalStatus='a' or sw.ApprovalStatus='p') 
            and sw.IsActive = 1
            and sw.AcceptedByWorkerId is null
        )
    
    IF @RC = 0 
    BEGIN
        THROW 50000, 'GatError.AcceptShiftSwap.Invalid',1
    END

    BEGIN TRY
    BEGIN TRANSACTION;

        DECLARE @ShiftStart datetimeoffset
        DECLARE @ShiftEnd datetimeoffset

        select @ShiftStart=StartDateTime, @ShiftEnd=StartDateTime from shift where Id = (select shiftid from shiftswap where Id=@ShiftSwapId)

        -- step1 : do all checks
        -- check if new worker has overlapping shift
        DECLARE @ShiftOverlapCount INT
        select @ShiftOverlapCount=count(*) from Shift
        where IsActive=1 and Id in 
        (select ShiftId from WorkerShift
            where WorkerId=@workerId and Status='A')		
            and (@ShiftEnd>=StartDateTime and @ShiftStart<=EndDateTime)			

        IF @ShiftOverlapCount > 0 
        BEGIN
            RAISERROR(N'Error.SP.WorkerShiftSignup.WorkerShiftOverlap',16,1)
            RETURN
        END

        -- check if new worker has weekly hrs limmit
        -- check if new worker has continious hrs limit
        DECLARE @WeeklyCheck INT = 1
        SET @WeeklyCheck = dbo.udfCheckCompanyContiniousShiftSignup(@workerId, (select shiftid from ShiftSwap where Id=@ShiftSwapId))

       -- step2 : change workershift
        -- update worker shift to change the workerid
        update 
            WorkerShift
        set 
            workerid=@workerid,
            LastUpdatedDate = GETDATE()
        where
            workerid=(select workerid from ShiftSwap where Id=@ShiftSwapId)
            and shiftid=(select shiftid from ShiftSwap where Id=@ShiftSwapId)

        -- step3 : accept the shift swap
         -- update the shift swap status
        UPDATE
            ShiftSwap
        SET
            AcceptanceDate = GETDATE(),
            AcceptedByWorkerId = @WorkerId
        WHERE
            Id=@ShiftSwapId;


    COMMIT TRAN 
    END TRY
    BEGIN CATCH 
        IF @@TRANCOUNT > 0 ROLLBACK; 

        THROW;
    END CATCH

    
    -- select the requester worker
    SELECT w.Id as WorkerId, sw.ShiftId, w.FirstName,w.MobilePhoneNumber,
                (select STRING_AGG(wdt.devicetoken,',')
                from WorkerDeviceToken wdt
                where wdt.WorkerId=w.Id and wdt.IsActive=1) AS DeviceTokens      
    from ShiftSwap sw
    inner join Worker w on sw.WorkerId=w.Id 
    where sw.Id=@ShiftSwapId

END;

CREATE   PROCEDURE [dbo].[CancelShiftSwap]
    @ShiftSwapId INT,
    @WorkerId UNIQUEIDENTIFIER
AS
BEGIN

    update ShiftSwap
    SET
        IsActive = 0,
        lastupdateddate = GETDATE()
    WHERE
        Id = @ShiftSwapId
        and WorkerId = @WorkerId
        and AcceptedByWorkerId is NULL
        and ApprovalStatus<>'r'
        and IsActive = 1

END;

CREATE PROCEDURE [dbo].[ChangePassword]	
	@UserId INT,	
	@NewPassword NVARCHAR(100),
	@UpdatedBy INT
AS
BEGIN	
	UPDATE [Login] SET [Password] = @NewPassword, LastUpdatedBy = @UpdatedBy, LastUpdatedDate = GETDATE(), ChangePasswordOnLogin = 0  
		WHERE Id = (SELECT LoginId FROM [User] WHERE Id = @UserId)			
END;

create   PROCEDURE [dbo].[ChangeShiftSwapStatus]
    @Id INT,
    @Status varchar(2),
    @UserId INT
AS
BEGIN

    -- update the shift swap status
    UPDATE
        ShiftSwap
    SET
        ApprovalStatus = @Status,
        ApprovalDate = GETDATE(),
        ApprovalBy = @UserId
    WHERE
        Id=@Id
        and ApprovalStatus = 'n'

    if @Status = 'a' or @Status = 'r'
    BEGIN
    
        -- table[0]: select worker details who posted the shift swap req for notification
        SELECT 
            w.Id, w.FirstName,w.MobilePhoneNumber,
                    (select STRING_AGG(wdt.devicetoken,',')
                    from WorkerDeviceToken wdt
                    where wdt.WorkerId=w.Id and wdt.IsActive=1) DeviceTokens,
            (select ShiftId from ShiftSwap where Id=@Id) ShiftId
        from
        worker w
        where Id=(select WorkerId from ShiftSwap where Id=@Id)

    END

    if @Status = 'a'
    BEGIN
        -- table[1]: select all other workers to inform abt a new shift swap request
        SELECT 
            w.Id, w.FirstName,w.MobilePhoneNumber,
                    (select STRING_AGG(wdt.devicetoken,',')
                    from WorkerDeviceToken wdt
                    where wdt.WorkerId=w.Id and wdt.IsActive=1) DeviceTokens,
            (select ShiftId from ShiftSwap where Id=@Id) ShiftId      
        from WorkerJob wj
        INNER join Worker w on wj.WorkerId=w.Id        
        where 
        wj.WorkerId<>(select WorkerId from ShiftSwap where Id=@Id)
        and wj.JobId = (select JobId from Shift where Id=(select ShiftId from ShiftSwap where Id=@Id))
            
    END

END;

CREATE   PROCEDURE [dbo].[CreateBroadcast]
    @Message VARCHAR(255),
    @JobIds VARCHAR(255),
    @UserId INT,
    @WorkerCount INT
AS
BEGIN

    INSERT into BroadCast(ToJobIds,[Message],CreationDate,CreatedBy,WorkerCount)
    VALUES (@JobIds,@Message,GETDATE(),@UserId,@WorkerCount)

END;

CREATE   PROCEDURE [dbo].[CreateCompany]	
	@Name varchar(150),
	@CreatedBy int,
    @DefaultShiftDuration	tinyint = 4,
    @MinShiftDuration	tinyint = 2,
    @MaxShiftDuration	tinyint = 8,
    @WorkWeekStartDay	varchar(10) = 'Sunday',
    @WorkWeekStartTime	time = '00:00:00',
    @ShiftMinAdvanceInDays	tinyint = 5,
    @ShiftMaxAdvanceInDays	tinyint = 14,
    @MinNumDaysPublished	tinyint = 4,
    @MaxNumDaysPublished	tinyint = 15,
    @SignUpCutOffDays	tinyint = 1,
    @ShiftCancelAllow	bit = 1,
    @ShiftModAllowTillDays	tinyint = 0,
    @WorkerShiftCancelDays	tinyint = 2,
    @ShiftConfirmationLockHours	int = 0,
    @MaxContiniousSignUpHours	float = 12,
    @EnableDailyReportInEmail	bit = 0,
    @EnableDailySmsReminder	bit = 0,
    @LocationName varchar(100),
    @BuildingNo VARCHAR(50),
    @StreetAddress VARCHAR(100),
    @City VARCHAR(50),
    @State VARCHAR(25),
    @County VARCHAR(100),
    @Country VARCHAR(100),
    @ZipCode VARCHAR(10),
    @TimeZone VARCHAR(100),
    @ContactForWorkers VARCHAR(50),
    @DailyReportEmailTime Time(7),
    @DailySmsReminderTime Time(7),
    @WorkerTypeHours ListWorkerTypeHours READONLY
AS
BEGIN

    DECLARE @companyId INT

    -- check if the time zone is valid 
    DECLARE @TZCount INT = 0
    SET @TZCount = (select count(*) from sys.time_zone_info where name=@TimeZone)

    if @TZCount = 0 
    BEGIN
        THROW 50000,'Invalid Time Zone!',1
    END

    BEGIN TRY
        BEGIN TRANSACTION

        INSERT INTO [dbo].[Company] 
            ([Name],[IsActive],[CreatedBy],[CreationDate],[LastUpdatedBy],[LastUpdatedDate])
        VALUES 
            (@Name,1, @CreatedBy, GETDATE(), @CreatedBy, GETDATE())

        SET @companyId = (SELECT SCOPE_IDENTITY())

        INSERT INTO Settings_ShiftCreation
           (CompanyId,
            DefaultShiftDuration,
            MinShiftDuration,
            MaxShiftDuration,
            WorkWeekStartDay,
            WorkWeekStartTime,
            ShiftMinAdvanceInDays,
            ShiftMaxAdvanceInDays,
            MinNumDaysPublished,
            MaxNumDaysPublished,
            SignUpCutOffDays,
            ShiftCancelAllow,
            ShiftModAllowTillDays,
            WorkerShiftCancelDays,
            CreatedBy,
            CreationDate,
            LastUpdatedBy,
            LastUpdatedDate,
            ShiftConfirmationLockHours,
            MaxContiniousSignUpHours,
            EnableDailyReportInEmail,
            EnableDailySMSReminderToWorker) 
        VALUES
            (@companyId,
            @DefaultShiftDuration,
            @MinShiftDuration,
            @MaxShiftDuration,
            @WorkWeekStartDay,
            @WorkWeekStartTime,
            @ShiftMinAdvanceInDays,
            @ShiftMaxAdvanceInDays,
            @MinNumDaysPublished,
            @MaxNumDaysPublished,
            @SignUpCutOffDays,
            @ShiftCancelAllow,
            @ShiftModAllowTillDays,
            @WorkerShiftCancelDays,
            @CreatedBy,
            GETDATE(),
            @CreatedBy,
            GETDATE(),
            @ShiftConfirmationLockHours,
            @MaxContiniousSignUpHours,
            @EnableDailyReportInEmail,
            @EnableDailySmsReminder)

        INSERT INTO WorkerTypeHours(CompanyId,WorkerTypeId,MinHours,MaxHours,CreatedBy,CreationDate,LastUpdatedBy,LastUpdatedDate)
        SELECT @companyId,WorkerTypeId,MinHours,MaxHours,@CreatedBy,GETDATE(),@CreatedBy,GETDATE() from @WorkerTypeHours

        INSERT INTO [Location](CompanyId,[Name],BuildingNumber,Address1,City,[State],County,Country,Zip,IsActive,CreatedBy,CreatedDate,LastUpdatedBy,LastUpdatedDate,TIME_ZONE,CompanyContactForWorker,DailyReportEmailTime,DailySMSReminderTime)
        VALUES(@companyId,@LocationName,@BuildingNo,@StreetAddress,@City,@State,@County,@Country,@ZipCode,1,@CreatedBy,GETDATE(),@CreatedBy,GETDATE(),@TimeZone,@ContactForWorkers,@DailyReportEmailTime,@DailySmsReminderTime)

        COMMIT TRAN
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK;

        THROW;
    END CATCH
           
    SELECT @companyId;
		
END;

CREATE PROCEDURE [dbo].[CreateCompanyAdmin]	
	@UserName nvarchar(150),
	@RoleId int,
	@CompanyId int,
	@FirstName nvarchar(100),
	@MiddleName nvarchar(100),
	@LastName nvarchar(100),
	@Email nvarchar(255),
	@PhoneNumber varchar(20),
	@IsActive bit,
	@CreatedBy int
AS
BEGIN	

 BEGIN TRY
  BEGIN TRANSACTION;
	INSERT INTO [dbo].[Login]
           ([UserName],[IsLocked],[IsActive],[CreationDate],[CreatedBy],[LastUpdatedDate],[LastUpdatedBy])
     VALUES
			(@UserName, 0, 1, GETDATE(), @CreatedBy, GETDATE(), @CreatedBy)
	
	DECLARE @loginId INT
	SET @loginId = (SELECT SCOPE_IDENTITY());		
	INSERT INTO [dbo].[User]
           ([LoginId],[RoleId],[CompanyId],[FirstName],[MiddleName],[LastName],[Email],[PhoneNumber],[IsActive],
		   [CreatedBy],[CreationDate],[LastUpdatedBy],[LastUpdatedDate])
     VALUES (@loginId, @RoleId, @CompanyId, @FirstName, @MiddleName, @LastName, @Email, @PhoneNumber, 1, 
			@CreatedBy, GETDATE(), @CreatedBy, GETDATE())

	DECLARE @userId INT
	SET @userId = (SELECT SCOPE_IDENTITY());
	DECLARE @luEntityId INT
	SET @luEntityId = (SELECT Id FROM [dbo].[LuEntity] WHERE Entity = 'Company')
	INSERT INTO [dbo].[EntityUser]
           ([LuEntityId],[UserId],EntityId,[CreatedBy],[CreationDate],[LastUpdatedBy],[LastUpdatedDate])
     VALUES (@luEntityId, @userId,@CompanyId,@CreatedBy, GETDATE(),@CreatedBy, GETDATE())
	               
  COMMIT TRAN 
 END TRY
 BEGIN CATCH 
      IF @@TRANCOUNT > 0 ROLLBACK 
 END CATCH
END;

CREATE PROCEDURE [dbo].[CreateJob]	
	@UserId INT,
	@Name VARCHAR(150),
	@HexColor VARCHAR(6),
	@CreatedBy INT,
    @WorkCenterIdsCsv varchar(50),
    @SkillIdsCsv varchar(100)
AS
BEGIN	
	Declare @LocationId INT
	Declare @JobCount INT
    DECLARE @UserCompanyId INT
    DECLARE @jId INT
    DECLARE @JobExpiryMinWorkHrs INT
    DECLARE @JobExpiryMinWorkHrsDays INT
    Declare @UserWorkCenterIds Table(Id int)

    -- get user related work center Ids
    Insert into @UserWorkCenterIds
    select items from dbo.Split(dbo.udfUserWorkCenters(@userid),',')

    -- check if job name already exists with the user workcenters
    Set @JobCount = 
        (select count(*) from Job
            WHERE
            Id in (select JobId from JobWorkcenter where WorkcenterId in (Select Id from @UserWorkCenterIds))
            and lower(trim(Name))=lower(trim(@Name))            
            and IsActive = 1
        )

    if @JobCount > 0
    BEGIN
        THROW 50000, 'Error.SP.CreateJob.JobAlreadyExist', 1
    END


    -- get user company
    Set @UserCompanyId = (select CompanyId from [User] where Id=@UserId)

    -- get job expiry min working hours and minimum working hours days
    Set @JobExpiryMinWorkHrs = COALESCE((select JobExpiryMinWorkHrs from [Settings_CompanyJob] where CompanyId=@UserCompanyId), 0)
    Set @JobExpiryMinWorkHrsDays = COALESCE((select JobExpiryMinWorkHrsDays from [Settings_CompanyJob] where CompanyId=@UserCompanyId), 0)


    -- check if job name already exists for the user company
    Set @JobCount = 
        (select count(*) from Job 
            where 
            CompanyId=@UserCompanyId 
            and lower(trim(Name))=lower(trim(@Name)))

    
    -- start transaction
    BEGIN TRY
	  BEGIN TRANSACTION;

        if @JobCount > 0
        BEGIN
            -- if job name already exists for the company then get job id
            set @jId = 
                (select Id from Job 
                    where 
                    CompanyId=@UserCompanyId 
                    and lower(trim(Name))=lower(trim(@Name)))


            -- update company job to make it active
            update job 
            set IsActive = 1
            where Id = @jId
        END
        ELSE
        BEGIN
            -- if job name does not exists for the company then add it with user company
            INSERT INTO [dbo].[Job]	
                ([Name],[HexColor],IsActive,CreatedBy,CreationDate,LastUpdatedBy,LastUpdatedDate,CompanyId,MinWorkHrs,MinWorkHrsDays)
            VALUES
                    ( @Name, @HexColor, 1, @CreatedBy, GETDATE(), @CreatedBy, GETDATE(),@UserCompanyId,@JobExpiryMinWorkHrs,@JobExpiryMinWorkHrsDays)		

            SET @jId = (SELECT SCOPE_IDENTITY())

        END       

        -- insert into JobWorkCenter only the WorkCenterId from CSV which is associated with user
        INSERT into JobWorkCenter(JobId,WorkCenterId,CreatedBy,CreationDate,LastUpdatedBy,LastUpdatedDate)
        select @jId,items,@CreatedBy,GETDATE(),@CreatedBy,GETDATE() from dbo.Split(@WorkCenterIdsCsv,',')
            where 
            items in (select Id from @UserWorkCenterIds)
            and items not in (select WorkCenterId from JobWorkcenter where JobId=@jId)

        -- add skills to the job
        DECLARE @SkillIds TABLE(SkillId BIGINT)
        INSERT INTO @SkillIds
        SELECT items from dbo.split(@SkillIdsCsv,',')


        -- update existing jobskill to make them inactive which are not present in @skillids
        UPDATE JobSkill
        SET 
            IsActive = 0, 
            LastUpdatedBy = @UserId, 
            LastUpdatedDate = GETDATE()
        WHERE 
        JobId = @jId 
        and SkillId not in (select 
                        SkillId 
                        from @SkillIds)

        -- update exisint jobskill to make them active
        UPDATE JobSkill
        SET 
            IsActive = 1, 
            LastUpdatedBy = @UserId, 
            LastUpdatedDate = GETDATE()
        WHERE 
        JobId = @jId 
        and SkillId in (select 
                        SkillId 
                        from @SkillIds)

        -- insert missing jobskill
        INSERT INTO JobSkill
        SELECT 
            @jId,SkillId,1,
            @UserId,GETDATE(),@UserId,GETDATE()
        FROM @SkillIds
        where SkillId not in (select 
                            SkillId 
                            from JobSkill 
                            where JobId=@jId)


        -- insert job->skills to workers with missing skills
        DECLARE @Workers TABLE(WorkerId UNIQUEIDENTIFIER)
        DECLARE @Skills TABLE(SkillId INT)

        -- get workersIds of all workers associated with the job
        insert into @Workers
        SELECT wj.WorkerId from WorkerJob wj where wj.JobId=@jId

        -- get skillsIds of all skills associated with the job
        insert into @Skills
        SELECT js.SkillId from JobSkill js where js.JobId=@jId and js.IsActive=1

        -- insert missing skills to workers
        ;WITH cte as (
            SELECT wi.WorkerId,si.SkillId from @Workers wi,@Skills si
            EXCEPT
            SELECT ws.WorkerId,ws.SkillId from WorkerSkill ws where ws.WorkerId in (SELECT WorkerId from @Workers)
        )
        INSERT into WorkerSkill
        SELECT cte.workerid,cte.skillid,1,@UserId,GETDATE(),@UserId,GETDATE() from cte

        
        -- select job to return
        SELECT Id, [Name], HexColor, IsActive,(select STRING_AGG(jw.WorkCenterId,',') 
                                                from JobWorkCenter jw 
                                                where jw.JobId=@jId
                                                and jw.WorkcenterId in (select Id from @UserWorkCenterIds)) WorkCenterIds
            FROM Job
                WHERE Id = @jId

      COMMIT TRAN 
	END TRY
	BEGIN CATCH 
		  IF @@TRANCOUNT > 0 ROLLBACK 
	END CATCH    

END;

CREATE   PROCEDURE [dbo].[CreateJobSkill]
    @JobIds VARCHAR(100),
    @SkillName NVARCHAR(100),
    @UserId INT
AS
BEGIN

    DECLARE @SkillId BIGINT = 0
    DECLARE @JobSkillId BIGINT = 0
    DECLARE @CompanyId INT = 0

    SET @CompanyId = (Select CompanyId FROM [User] where Id=@UserId)
       
    BEGIN TRY
        BEGIN TRANSACTION

        -- check 
        select 
            @SkillId = Id
        from
            Skill
        where 
            lower([Name])=lower(@SkillName)
            AND CompanyId=@CompanyId

        if @@ROWCOUNT = 0
        BEGIN

            INSERT INTO SKILL VALUES(@SkillName,1,@CompanyId,@UserId,GETDATE(),@UserId,GETDATE())

            SET @SkillId = (SELECT SCOPE_IDENTITY())

        END

        -- update jobskill records to make active
        UPDATE JobSkill
        SET IsActive = 1, LastUpdatedBy = @UserId, LastUpdatedDate = GETDATE()
        WHERE
        SkillId = @SkillId and JobId in (select items from dbo.split(@JobIds,','))

        -- insert jobskill records whicha are not available
        INSERT INTO JOBSKILL    
        SELECT 
            items,@SkillId,1,@UserId,GETDATE(),@UserId,GETDATE() 
        from 
            dbo.split(@JobIds,',')
        WHERE
            items not in (select JobId from JobSkill where SkillId=@SkillId)

        SET @JobSkillId = (SELECT SCOPE_IDENTITY())

        SELECT 
            js.Id,js.JobId,js.SkillId,
            s.Name as SkillName,j.Name as JobName
        from JobSkill js
        inner join Job j on js.JobId = j.Id
        inner join Skill s on js.SkillId = s.Id
        where js.SkillId = @SkillId and js.JobId in (select items from dbo.split(@JobIds,','))

        COMMIT TRAN
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK;

        THROW;
    END CATCH

END;

CREATE PROCEDURE [dbo].[CreateLocationAdmin]	
	@UserName nvarchar(150),
	@RoleId int,
	@CompanyId int,
	@LocationId int,
	@FirstName nvarchar(100),
	@MiddleName nvarchar(100),
	@LastName nvarchar(100),
	@Email nvarchar(255),
	@PhoneNumber varchar(20),
	@IsActive bit,
	@CreatedBy int
AS
BEGIN	

 BEGIN TRY
  BEGIN TRANSACTION;
	INSERT INTO [dbo].[Login]
           ([UserName],[IsLocked],[IsActive],[CreationDate],[CreatedBy],[LastUpdatedDate],[LastUpdatedBy])
     VALUES
			(@UserName, 0, 1, GETDATE(), @CreatedBy, GETDATE(), @CreatedBy)
	
	DECLARE @loginId INT
	SET @loginId = (SELECT SCOPE_IDENTITY());		
	INSERT INTO [dbo].[User]
           ([LoginId],[RoleId],[CompanyId],[FirstName],[MiddleName],[LastName],[Email],[PhoneNumber],[IsActive],
		   [CreatedBy],[CreationDate],[LastUpdatedBy],[LastUpdatedDate])
     VALUES (@loginId, @RoleId, @CompanyId, @FirstName, @MiddleName, @LastName, @Email, @PhoneNumber, 1, 
			@CreatedBy, GETDATE(), @CreatedBy, GETDATE())

	DECLARE @userId INT
	SET @userId = (SELECT SCOPE_IDENTITY());
	DECLARE @entityId INT
	SET @entityId = (SELECT Id FROM [dbo].[LuEntity] WHERE Entity = 'Location')
	INSERT INTO [dbo].[EntityUser]
           ([EntityId],[UserId],[CreatedBy],[CreationDate],[LastUpdatedBy],[LastUpdatedDate])
     VALUES (@entityId, @userId, @CreatedBy, GETDATE(),@CreatedBy, GETDATE())
	               
  COMMIT TRAN 
 END TRY
 BEGIN CATCH 
      IF @@TRANCOUNT > 0 ROLLBACK 
 END CATCH
END;

Create PROCEDURE [dbo].[CreateShifts]
    @tvpNewShifts ListOfShifts READONLY
AS
BEGIN
	INSERT INTO [dbo].[Shift] 
			(StartDateTime
			, EndDateTime
			, JobId
			, WorkCenterId
			, NumWorkersNeeded
			, ShiftPremium
			, IsPublished
			,[IsActive]
			,[CreatedBy]
			,[CreationDate]
			,[LastUpdatedBy]
			,[LastUpdatedDate])
	SELECT 
			ns.StartDateTime,
			ns.EndDateTime, 
			ns.JobId, 
			ns.WorkCenterId, 
			ns.NumWorkersNeeded, 
			ns.ShiftPremium,
			ns.IsPublished, 
			1, --IsActive
			ns.CreatedBy, 
			GETDATE(), 
			ns.CreatedBy,
			GETDATE()
		FROM @tvpNewShifts AS ns;  
END;

CREATE PROCEDURE [dbo].[CreateShiftsAndReturn]
    @tvpNewShifts ListOfShifts READONLY
AS
BEGIN
    
    -- check for shift min and max duration
    DECLARE @MinShiftDuration TINYINT
    DECLARE @MaxShiftDuration TINYINT
    DECLARE @ErrorMsg VARCHAR(100)

    SELECT 
        @MinShiftDuration = MinShiftDuration,
        @MaxShiftDuration = MaxShiftDuration
    FROM
        Settings_ShiftCreation
    WHERE
        CompanyId = (Select CompanyId from Job where Id=(select top 1 JobId from @tvpNewShifts))

    SELECT s.* FROM @tvpNewShifts s
    INNER JOIN Job j on s.JobId = j.Id
    INNER JOIN Settings_ShiftCreation ss on j.CompanyId = ss.CompanyId
    WHERE
    DATEDIFF(MI,s.StartDateTime,s.EndDateTime)/60.0 < ss.MinShiftDuration
    or DATEDIFF(MI,s.StartDateTime,s.EndDateTime)/60.0 > ss.MaxShiftDuration

    IF @@ROWCOUNT > 0
    BEGIN

        SET @ErrorMsg = CONCAT('GATError.SP.CreateShifts.InvalidDuration','##',@MinShiftDuration,',',@MaxShiftDuration);

        THROW 50001, @ErrorMsg , 1
    END

    DECLARE @MissingJobWorkCenterCount INT 

    -- check for any shift with no job-workcenter relation
    set @MissingJobWorkCenterCount = 
        (select count(*) from @tvpNewShifts AS ns
        where
        CONCAT(ns.JobId,'-',ns.WorkCenterId) not in (select CONCAT(JobId,'-',WorkCenterId) from JobWorkcenter))

    if @MissingJobWorkCenterCount > 0
    BEGIN
        THROW 50000,'GATError.SP.CreateShifts.NoJobWorkCenterExists',1
    END


DECLARE @tblShiftsForID TABLE (
    Id bigint NOT NULL,
	StartDateTime datetime NOT NULL,
	EndDateTime datetime NOT NULL,
	JobId int NOT NULL,
	WorkCenterId int NOT NULL,
	NumWorkersNeeded smallint NOT NULL,
	ShiftPremium varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	IsPublished bit NOT NULL,
	IsActive bit NOT NULL,
	CreatedBy int NOT NULL,
	CreationDate datetime2(2) NOT NULL,
	LastUpdatedBy int NOT NULL,
	LastUpdatedDate datetime2(2) NOT NULL
	)

	--SET IDENTITY_INSERT dbo.Shift ON
	
	INSERT INTO 
		[dbo].[Shift] 
		(
		StartDateTime, 
		EndDateTime, 
		JobId, 
		WorkCenterId, 
		NumWorkersNeeded,
		ShiftPremium,
		IsPublished,
		[IsActive],
		[CreatedBy],
		[CreationDate],
		[LastUpdatedBy],
		[LastUpdatedDate]
		)
	OUTPUT 
		 INSERTED.ID,
         INSERTED.StartDateTime,   
         INSERTED.EndDateTime,   
         INSERTED.JobId,    
         INSERTED.WorkCenterId,   
         INSERTED.NumWorkersNeeded,   
         INSERTED.ShiftPremium,          
         INSERTED.IsPublished,
         INSERTED.IsActive,
         INSERTED.CreatedBy,    
         INSERTED.CreationDate,   
         INSERTED.LastUpdatedBy,   
         INSERTED.LastUpdatedDate          
  	INTO @tblShiftsForID  
  
	SELECT ns.StartDateTime, ns.EndDateTime, ns.JobId, ns.WorkCenterId, ns.NumWorkersNeeded, ns.ShiftPremium,
			ns.IsPublished, 1, ns.CreatedBy, GETDATE(), ns.CreatedBy, GETDATE()
		FROM @tvpNewShifts AS ns
	
	SELECT Id, 	StartDateTime, EndDateTime, JobId, WorkCenterId, NumWorkersNeeded, ShiftPremium, IsPublished, IsActive
		FROM @tblShiftsForID
END;

CREATE   PROCEDURE [dbo].[CreateShiftSwapReq]
    @ShiftId INT,
    @WorkerId UNIQUEIDENTIFIER,
    @Comment VARCHAR(255)
AS
BEGIN

    -- get companyid from shiftid
    DECLARE @CompanyId INT
    set @CompanyId = (select j.CompanyId from shift s
            inner join job j on s.JobId=j.Id				
            where
            s.Id=@shiftId)

    --Check if shift swap is enabled for the worker's company
    DECLARE @IsShiftSwapAllowed BIT = 0
    SET @IsShiftSwapAllowed = 
        (
            select ShiftSwapEnabled from Settings_ShiftCreation where CompanyId = @CompanyId
        )

    IF @IsShiftSwapAllowed = 0
    BEGIN
        THROW 50000, 'GATError.NotAuthorized', 1;
    END

    --Check if a valid shift swap request exists for the specified shift
    DECLARE @ShiftSwapCount INT = 0
    SET @ShiftSwapCount = 
        (
            SELECT count(*) 
            from ShiftSwap
            WHERE 
            WorkerId=@WorkerId
            and ShiftId=@ShiftId
            and IsActive = 1
            and ApprovalStatus<>'r'
        )
    
    IF @ShiftSwapCount > 0
    BEGIN
        THROW 50000, 'GatError.AcceptShiftSwap.Invalid', 1;
    END;

    --Check if shift swap approval is required for the worker's company
    DECLARE @ShiftSwapApprovalEnabled BIT = 0
    SET @ShiftSwapApprovalEnabled = 
        (
            select ShiftSwapApprovalEnabled from Settings_ShiftCreation where CompanyId = @CompanyId
        )

    DECLARE @Status CHAR(2) = 'n'

    IF @ShiftSwapApprovalEnabled = 0
    BEGIN
        SET @Status = 'p'
    END

    DECLARE @RCount INT = 0
    -- create shift swap request if a valid worker<->shift exists
    INSERT INTO ShiftSwap(ShiftId,WorkerId,RequestDate,Comment,ApprovalStatus,IsActive)
    SELECT ws.ShiftId,ws.WorkerId,getdate(),@Comment,@Status,1 
    from WorkerShift ws
    inner join shift s on ws.ShiftId=s.Id
    where 
    ShiftId=@ShiftId
    and WorkerId=@WorkerId
    and [Status]='A'
    and s.StartDateTime>GETDATE()

    SET @RCount = @@ROWCOUNT

    -- select all workers with the shift job to send notification
    IF @RCount > 0 
    BEGIN

        SELECT wj.WorkerId, w.FirstName,w.MobilePhoneNumber,
                    (select STRING_AGG(wdt.devicetoken,',')
                    from WorkerDeviceToken wdt
                    where wdt.WorkerId=w.Id and wdt.IsActive=1) DeviceTokens
        from WorkerJob wj
        INNER join Worker w on wj.WorkerId=w.Id
        where 
        wj.JobId = (select JobId from Shift where Id=@ShiftId)
        

    END

END;

CREATE   PROCEDURE [dbo].[CreateSkill]
    @Name NVARCHAR(100),
    @UserId INT
AS
BEGIN

    DECLARE @SkillId BIGINT = 0
    DECLARE @IsActive BIT 
    DECLARE @CompanyId INT = 0

    SET @CompanyId = (Select CompanyId FROM [User] where Id=@UserId)

    -- check if already exists
    select
        @SkillId = Id,
        @IsActive = IsActive
    from
        Skill
    where 
        lower(trim([Name]))=lower(trim(@Name))
        AND CompanyId=@CompanyId        

    -- if skill name already exist in company 
    if @@ROWCOUNT > 0 and @IsActive = 1
    BEGIN

        THROW 50000,'GATError.Skill.AlreadyExists',1

    END

    -- if skill exist and inactive make it active
    if @@ROWCOUNT > 0 and @IsActive = 0
    BEGIN

        UPDATE Skill
        SET IsActive = 1, LastUpdatedBy = @UserId, LastUpdatedDate = GETDATE()
        where Id = @SkillId

    END

    -- add new skill if it does not exists
    if @@ROWCOUNT = 0
    BEGIN
    
        INSERT INTO SKILL
        VALUES(@Name, 1, @CompanyId, @UserId, GETDATE(), @UserId, GETDATE())

        SET @SkillId = (SELECT SCOPE_IDENTITY())

    END

    SELECT * from Skill where Id=@SkillId

END;

--=============================================
-- Author:		Gnan
-- Create date: 4th July 2021
-- Description:	Creat new user
-- =============================================
CREATE   PROCEDURE [dbo].[CreateUser]
	-- Add the parameters for the stored procedure here
	@Username varchar(50),	
	@SaltValue varchar(100),
    @RoleId int, 
    @Company int,
    @FirstName varchar(50),
    @MiddleName varchar(50),
    @LastName varchar(100),	
    @Email varchar(50),	
    @PhoneNumber varchar(20),	
    @IsActive bit,
    @Password varchar(100),
    @CreatedBy int,
    @WorkCenterIds VARCHAR(100)
    
AS
BEGIN
	
	DECLARE @NewLoginId INT;
    DECLARE @NewUserId INT;
   
   -- check if username already exists
   DECLARE @UCount INT = 0
   SET @UCount = (
   	SELECT count(*) from [Login] l where UserName = @Username
   )
   
  if @UCount > 0
    BEGIN
        THROW 50000, 'GATError.SP.CreateUser.AlreadyExist', 1
    END 
   

-- Create a new login id for new user
BEGIN TRY
  BEGIN TRANSACTION;

	INSERT INTO
		[dbo].[Login]
			(
			[UserName] ,
			[Password] ,
			[SaltValue],
			[PasswordUpdateDate],
			[ChangePasswordOnLogin] ,
			[FailedAttemptedLogins] ,
			[IsLocked],
			[IsActive],
			[CreationDate] ,
			[CreatedBy],
			[LastUpdatedDate] ,
			[LastUpdatedBy]
			)
			VALUES
			(
			@Username,
			@Password,
			@SaltValue,
			GETDATE(), 
			1,
			0,	
			0,	
			1,	
			GETDATE(),
			@CreatedBy,
			GETDATE(),
			@CreatedBy
			)

	SET @NewLoginId = (SELECT Id FROM [Login] WHERE [UserName] = @username)


--create user with above created login id

	INSERT INTO
		[dbo].[User]
			(
			[LoginId],
			[RoleId],
			[CompanyId],
			[FirstName],
			[MiddleName],
			[LastName],
			[Email],
			[PhoneNumber] ,
			[IsActive] ,
			[CreatedBy],
			[CreationDate],
			[LastUpdatedBy],
			[LastUpdatedDate] 
				)
			VALUES
			(
			@NewLoginId,
			@RoleId, 
			@Company,
			@FirstName,	
			@MiddleName,	
			@LastName,	
			@Email,	
			@PhoneNumber,	
			@IsActive,
			@CreatedBy,
			GETDATE(),
			@CreatedBy,
			GETDATE()
			)

        SET @NewUserId = (SELECT SCOPE_IDENTITY());

        -- add user work center associations
        INSERT INTO EntityUser
        SELECT 
            @NewUserId, 
            items,
            (select id from LuEntity where Entity='WorkCenter'),
            @CreatedBy,
            GETDATE(),
            @CreatedBy,
            GETDATE() 
        from 
            dbo.Split(@WorkCenterIds,',')
    COMMIT TRAN 

    select @NewUserId

 END TRY
 BEGIN CATCH 
      IF @@TRANCOUNT > 0 ROLLBACK;
      THROW

 END CATCH
			
END;

CREATE PROCEDURE [dbo].[CreateWorker]	
	@FirstName VARCHAR(50),
	@MiddleName VARCHAR(50),
	@LastName VARCHAR(50),
	@Email NVARCHAR(100),
	@MobilePhoneNumber VARCHAR(20),	
	@CompanyId INT,
	@WorkerTypeId TINYINT,
	@CompanyEmployeeId VARCHAR(100),
	@JobId VARCHAR(255),
	@CreatedBy INT,
    @Skills ListOfWorkerSkills readonly,
	@WorkerJobs ListOfWorkerJobs READONLY
AS
BEGIN	

	DECLARE @MobileExist BIT = 0
	DECLARE @NameMatch BIT = 0
	DECLARE @WorkerActive BIT = 0
	DECLARE @CompanyWorkerExist BIT = 0	
	DECLARE @CompanyWorkerActive BIT = 0
	DECLARE @WorkerId uniqueidentifier
	DECLARE @CompanyWorkerId INT

	Select @MobileExist = (case when count(Id) > 0 then 1 else 0 end) from Worker where MobilePhoneNumber=@MobilePhoneNumber;	
		
	if @MobileExist = 'true'
	begin
		Select @NameMatch = (case when count(Id) > 0 then 1 else 0 end) from Worker 
			where 
			MobilePhoneNumber=@MobilePhoneNumber 
			and lower(FirstName)=lower(@FirstName)
			and lower(LastName)=lower(@LastName);
	end
	if @NameMatch = 'true'
	Begin
		select top 1  @WorkerActive = IsActive, @WorkerId = lower(Id) from Worker
			where 
			MobilePhoneNumber=@MobilePhoneNumber 
			and lower(FirstName)=lower(@FirstName)
			and lower(LastName)=lower(@LastName);

		select @CompanyWorkerExist = (case when count(Id) > 0 then 1 else 0 end) from CompanyWorker where CompanyId=@CompanyId and WorkerId=@WorkerId;

		if @CompanyWorkerExist = 'true'
		Begin
			select top 1 @CompanyWorkerActive = IsActive, @CompanyWorkerId = Id from CompanyWorker where CompanyId=@CompanyId and WorkerId=@WorkerId;
		end
	end


    -- get user jobs
    DECLARE @UserJobIds TABLE(JobId INT)
    INSERT @UserJobIds
    SELECT JobId
    from JobWorkcenter jwc
        INNER JOIN Job j on jwc.JobId=j.Id and j.IsActive=1
    where jwc.WorkcenterId in
                        (
                            SELECT items
    from dbo.Split(dbo.udfUserWorkCenters(@CreatedBy),',')
                        )

    -- get worker jobs
    DECLARE @WorkerJobIds TABLE(JobId INT)
    INSERT INTO @WorkerJobIds
    SELECT items
    from dbo.Split(dbo.udfWorkerJobs(@WorkerId,@CompanyId),',')


	
		BEGIN TRY
		  BEGIN TRANSACTION;			

			-- add new worker
			if @MobileExist = 'false'
			begin
				SET @WorkerId = (SELECT NEWID());				
				INSERT INTO [dbo].[Worker]	
					   ([Id],[FirstName],[MiddleName],[LastName],[Email],[MobilePhoneNumber],[IsActive],[CreatedBy],[CreationDate],[LastUpdatedBy],[LastUpdatedDate])
				 VALUES
						(@WorkerId, @FirstName, @MiddleName, @LastName, @Email, @MobilePhoneNumber, 1, @CreatedBy, GETDATE(), @CreatedBy, GETDATE())
			end

			-- check if worker exist and names does not match
			if @MobileExist = 'true' and @NameMatch = 'false'
			begin
				RAISERROR(N'Error.SP.CreateWorker.MobileExistWithDiffWorker',16,1)
			end

			-- enable existing worker
			if @MobileExist = 'true' and @NameMatch = 'true' and @WorkerActive = 'false'
			begin				
				update Worker set IsActive = 'true' where Id = lower(@WorkerId);				
			end

			-- add new company worker
			if @MobileExist = 'false' or 
				(@MobileExist = 'true' and @NameMatch = 'true' and @CompanyWorkerExist = 'false')
			begin

				INSERT INTO [dbo].[CompanyWorker]
					   ([CompanyId],[WorkerId],[WorkerTypeId],[CompanyEmployeeId],IsActive,[CreatedBy],[CreationDate],[LastUpdatedBy],[LastUpdatedDate])
				 VALUES
					   (@CompanyId,@WorkerId,@WorkerTypeId,@CompanyEmployeeId,1,@CreatedBy, GETDATE(), @CreatedBy, GETDATE())
			end

			-- enable existing company worker
			if @MobileExist = 'true' and @NameMatch = 'true' and 
				@CompanyWorkerExist = 'true' and @CompanyWorkerActive = 'false'
			begin
				update CompanyWorker set IsActive = 'true' where Id = @CompanyWorkerId;
			end

			-- if worker as well as company worker exist
			if @MobileExist = 'true' and @NameMatch = 'true' and @WorkerActive = 'true' and
				@CompanyWorkerExist = 'true' and @CompanyWorkerActive = 'true'
			begin
                
                -- check if the worker has at least one job from @UserJobIds
                DECLARE @JCount INT 
                SELECT @JCount = COUNT(uj.JobId) from @UserJobIds uj
                INNER JOIN @WorkerJobIds wj on uj.JobId=wj.JobId

                -- if worker does not have any job from @UserJobIds, 
                -- then user is trying to add an active worker from same company
                if @JCount > 0
                BEGIN
                    RAISERROR(N'Error.SP.CreateWorker.WorkerExistWithCompany',16,1)                        
                END
                                				
			end

			/*
			-- add new worker jobs
			if @MobileExist = 'false'
			begin
				DECLARE @JobIds TABLE (Id INT IDENTITY(1,1), JobId INT)
				INSERT INTO @JobIds  SELECT * FROM dbo.Split(@JobId,',')

				DECLARE @MaxCount INT, @i INT = 1, @jId INT
				SET @MaxCount = (SELECT COUNT(*) FROM @JobIds)

				WHILE (@i <= @MaxCount)
				BEGIN
				
					SET @jId = (SELECT JobId FROM @JobIds WHERE Id = @i)
					INSERT INTO [dbo].[WorkerJob]
						   ([WorkerId],[JobId],[CreatedBy],[CreationDate],[LastUpdatedBy],[LastUpdatedDate])
					 VALUES
						   (@WorkerId, @jId, @CreatedBy, GETDATE(), @CreatedBy, GETDATE())
					SET @i = (@i + 1)
				END
			end
			*/ 

			-- add new worker job which doesnt exist already
			insert into WorkerJob
				select @WorkerId,*,@CreatedBy,GETDATE(),@CreatedBy,GETDATE(),1,1,0,0,GETDATE() from dbo.Split(@JobId,',') 
					where items not in (select jobid from WorkerJob where WorkerId=@WorkerId);

			UPDATE
				dbo.WorkerJob
			SET
				dbo.WorkerJob.IsExpired = wjs.IsExpired,
				dbo.WorkerJob.IsExpiredBy = @CreatedBy,
				dbo.WorkerJob.IsExpiredDate = GETDATE()
			FROM
				dbo.WorkerJob wj
			INNER JOIN
				@WorkerJobs wjs
			ON 
				wj.JobId = wjs.JobId
			WHERE
				wj.WorkerId = @WorkerId;

            -- insert skill that doesn't exist
            INSERT into workerskill
            SELECT @WorkerId,skillid,levelid,@CreatedBy,GETDATE(),@CreatedBy,GETDATE()
            from @Skills
            WHERE skillid not in (select skillid from workerskill where workerid=@WorkerId)

            -- update levels for existing skills if they are different
            Update ws
            set ws.skilllevelid=s.levelid,ws.LastUpdatedBy=@CreatedBy,ws.LastUpdatedDate=getdate()
            from workerskill ws
            inner join @Skills s on ws.skillid=s.skillid and ws.skilllevelid<>s.levelid
            where 
            workerid=@WorkerId
					

			COMMIT TRAN

			SELECT w.Id, w.FirstName, w.MiddleName, w.LastName, w.Email, w.MobilePhoneNumber, lt.[Type], c.[Name] AS CompanyName, 
                (SELECT STRING_AGG(wj1.JobId,',') FROM WorkerJob wj1					
					WHERE 
						wj1.WorkerId = @WorkerId
						and wj1.JobId in (select JobId from @UserJobIds)) AS Jobs,
                    (select STRING_AGG(wdt.devicetoken,',')
                    from WorkerDeviceToken wdt
                    where wdt.WorkerId=w.Id and wdt.IsActive=1) DeviceTokens          
				FROM Worker w INNER JOIN CompanyWorker cw ON w.Id = cw.WorkerId INNER JOIN Company c ON cw.CompanyId = c.Id
					INNER JOIN LuWorkerType lt ON cw.WorkerTypeId = lt.Id
					WHERE w.Id = lower(@WorkerId) and cw.CompanyId=@CompanyId
		
		 END TRY
		 BEGIN CATCH 
			  IF @@TRANCOUNT > 0 ROLLBACK; 
			  Throw;
		 END CATCH
END;

CREATE   PROCEDURE [dbo].[CreateWorkerNotification]
    @Notifications ListOfWorkerNotification READONLY
AS
BEGIN

    DECLARE @NewIds TABLE(ID INT)

    INSERT INTO WorkerNotification (WorkerId,[Type],Title,[Message],ShiftIds,NotificationDate)
    OUTPUT Inserted.ID INTO @NewIds
    SELECT WorkerId,[Type],Title,[Message],ShiftIds,GETDATE() FROM @Notifications

    -- return IDs of inserted rows
    Select ID from @NewIds

END;

CREATE   PROCEDURE [dbo].[CreateWorkerSkillInterest]
    @WorkerId UNIQUEIDENTIFIER,
    @SkillId INT,
    @JobId  INT,
    @Level INT
AS
BEGIN

    DECLARE @WSID INT

    -- check if the skill and with >= level already exists with worker
    DECLARE @WorkerSkillCount INT = 0
    SELECT 
        @WorkerSkillCount = count(ws.Id)
    from WorkerSkill  ws
    INNER JOIN SkillLevel sl on ws.SkillLevelId=sl.Id
    where 
    WorkerId=@WorkerId 
    and SkillId=@SkillId 
    and sl.Seq >= @Level

    if @WorkerSkillCount > 0
    BEGIN

        THROW 50000,'GATError.Skill.AlreadyExists',1

    END


    SELECT @WSID=wi.Id
    from WorkerSkillInterest wi
    where 
    wi.WorkerId=@WorkerId
    AND wi.SkillId=@SkillId    
    --AND wi.JobId=@JobId
    --AND wi.SkillLevelId=(select Id from SkillLevel where Seq=@Level)
    AND wi.Status = 'N'

    IF @@ROWCOUNT = 0
    BEGIN

        INSERT INTO WorkerSkillInterest
        VALUES(
                @WorkerId,
                @SkillId,
                (select Id from SkillLevel where Seq=@Level),
                @JobId,
                'N',
                0,GETDATE(),0,GETDATE()
            )

        SET @WSID = SCOPE_IDENTITY()
    END

    SELECT 
        wi.Id, wi.SkillId, wi.JobId, wi.SkillLevelId,
        j.Name JobName, 
        s.Name SkillName,
        sl.Name LevelName,
        sl.Seq LevelSeq
    from WorkerSkillInterest wi    
    INNER JOIN Job j on wi.JobId=j.Id
    INNER JOIN Skill s on wi.SkillId=s.Id
    INNER JOIN SkillLevel sl on wi.SkillLevelId=sl.Id
    WHERE
    wi.Id=@WSID

END;

CREATE   PROCEDURE [dbo].[DeleteJobSkill]
    @Id BIGINT,
    @UserId INT
AS
BEGIN

    UPDATE JobSkill 
    SET IsActive = 0, LastUpdatedBy = @UserId, LastUpdatedDate = GETDATE()
    where Id=@Id

END;

CREATE PROCEDURE [dbo].[DeleteShiftById]
	@shiftId int
AS
BEGIN
	
	DECLARE @ShiftStartDateTime datetimeoffset

	Set @ShiftStartDateTime =
		(select StartDateTime from shift where Id=@shiftId)

	if @ShiftStartDateTime < GETDATE()
	Begin
		throw 50000, 'Error.SP.DeleteShiftById.CannotDeletePastShift' , 1
	End
		
	UPDATE [Shift] SET IsActive = 0 WHERE Id = @shiftId and IsActive = 1
	
END;

CREATE   PROCEDURE [dbo].[DeleteWorkerAvailability]
    @WorkerId UNIQUEIDENTIFIER,
    @Ids NVARCHAR(255)
AS
BEGIN

    DELETE FROM 
        WorkerAvailability 
    WHERE 
        WorkerId = @WorkerId 
        and Id in (select items from dbo.Split(@Ids,','))

END;

CREATE   PROCEDURE [dbo].[DeleteWorkersById]
	@workerIds VARCHAR(MAX),
    @UserId INT
AS
BEGIN			 

BEGIN TRY
  BEGIN TRANSACTION;
	
	DECLARE @workerId TABLE (Id UNIQUEIDENTIFIER)

	INSERT INTO @workerId  SELECT * FROM dbo.Split(@workerids,',')	

    -- get user company
    DECLARE @CompanyId INT    
    SET @CompanyId = (select CompanyId from [User] where Id=@UserId)

    -- get user jobs
    DECLARE @UserJobs TABLE(Id INT)
    INSERT into @UserJobs
    select
        jwc.JobId
    from
        JobWorkcenter jwc
    INNER JOIN Job j on jwc.JobId=j.Id
    where 
        j.IsActive=1
        and jwc.WorkcenterId in (select items
                                from dbo.Split(dbo.udfUserWorkCenters(@UserId),','))

    -- throw error if the worker has active signed up shifts in future with user jobs
    DECLARE @RC INT = 0
    SELECT @RC = count(*)
    from WorkerShift ws
    INNER JOIN Shift s on ws.ShiftId=s.Id and s.IsActive=1
    where 
    ws.WorkerId IN (SELECT Id FROM @workerId)
    and  s.JobId in (select Id from @UserJobs)
    and s.StartDateTime>GETDATE()

    IF @RC > 0 
    BEGIN
        THROW 50000,'GATError.WorkerDelete.ShiftExists',1
    END

    -- deactivate the company worker for worker and user company
	UPDATE CompanyWorker 
    SET IsActive = 0 
    WHERE 
    WorkerId IN (SELECT Id FROM @workerId)
    and CompanyId=@CompanyId
	
	-- disabled it as per JIRA #GM-29
	--delete workers future signed up shifts
	/*DELETE ws FROM WorkerShift ws 
		INNER JOIN [Shift] s ON ws.ShiftId = s.Id 
		WHERE WorkerId IN (SELECT Id FROM @workerId) AND s.StartDateTime  > GETDATE()*/

 COMMIT TRAN 
 END TRY
 BEGIN CATCH 
      IF @@TRANCOUNT > 0 ROLLBACK; 

      THROW;
 END CATCH

END;

CREATE       PROCEDURE [dbo].[DeleteWorkerShift]
    @WorkerId UNIQUEIDENTIFIER,
    @ShiftIds VARCHAR(255),
    @UserId INT
AS
BEGIN

    BEGIN TRY
        BEGIN TRANSACTION

            -- DELETE FROM 
            --     WorkerShift
            -- WHERE
            --     WorkerId=@WorkerId
            --     and ShiftId in (SELECT items from dbo.Split(@ShiftIds,','))

            UPDATE
                WorkerShift
            SET
                [Status]='R',
                LastUpdatedDate=GETDATE(),
                LastUpdatedBy=@UserId
            WHERE
                WorkerId=@WorkerId
                and ShiftId in (SELECT items from dbo.Split(@ShiftIds,','))

        COMMIT TRAN
    END TRY
    BEGIN CATCH
        if @@TRANCOUNT > 0 ROLLBACK;
        THROW;
    END CATCH

END;

CREATE   PROCEDURE [dbo].[DisableWorkerDeviceToken]
    @WorkerId UNIQUEIDENTIFIER,
    @DeviceId NVARCHAR(255)
AS
BEGIN

    UPDATE
        WorkerDeviceToken
    SET
        IsActive=0,
        LastUpdatedDate=GETDATE()
    WHERE
        WorkerId=@WorkerId
        AND DeviceId=@DeviceId

END;