Untitled

mail@pastecode.io avatar
unknown
plain_text
a year ago
182 kB
3
Indexable
Never
CREATE PROCEDURE [dbo].[GetAllCompanies]	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT Id, [Name], IsActive
		FROM Company			
END;

CREATE   PROCEDURE [dbo].[GetAllCompanyBroadcasts]
    @companyid INT
AS
BEGIN

    SELECT 
        b.*,u.FirstName,u.LastName 
    from 
        Broadcast b 
    inner JOIN [User] u on b.CreatedBy=u.Id
    where 
        u.CompanyId= @companyid 
        order by b.CreationDate desc
    

END;

CREATE PROCEDURE [dbo].[GetAllCompanyLocations]	
	@Id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT l.[Id],c.[Name] as [CompanyName],l.[Name],[BuildingNumber],[Address1],[Address2],
	  [City],[State],[Zip],[ZipExt],[County],[Country],l.[IsActive]
		FROM [Location] l INNER JOIN Company c ON l.CompanyId = c.Id
		WHERE c.Id = @Id
END;

CREATE PROCEDURE [dbo].[GetAllCompanyWorkers]	
	@Id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;	

    SELECT w.[Id], w.FirstName, w.MiddleName, w.LastName, w.Email, MobilePhoneNumber as PhoneNumber, 
	wt.[Type], c.[Name] as [CompanyName], dbo.udfWorkerJobs(w.Id,c.Id) AS Jobs, cw.[IsActive]
		FROM Worker w INNER JOIN CompanyWorker cw ON w.id = cw.WorkerId 
		INNER JOIN Company c ON cw.CompanyId = c.Id
		INNER JOIN LuWorkerType wt ON cw.WorkerTypeId = wt.Id
		WHERE c.Id = @Id AND cw.IsActive = 1
END;

CREATE   PROCEDURE [dbo].[GetAllFTShiftsByUser]	
	@userId INT,
	@companyId INT,
	@startDate datetimeoffset,
	@endDate datetimeoffset
AS
BEGIN	
	
	DECLARE @Role VARCHAR(50)
	SET @Role = (SELECT [Role] FROM [User] u INNER JOIN LuRole lr ON u.RoleId = lr.Id WHERE u.Id = @userId)
	
	DECLARE @tmpShift TABLE 
	( 
		Id int NOT NULL, 
		[StartDateTime] datetimeoffset NOT NULL,
		[EndDateTime] datetimeoffset NOT NULL,
		[JobId] int NOT NULL,
		[WorkCenterId] int NOT NULL,
		[NumWorkersNeeded] smallint NOT NULL,
		[ShiftPremium] varchar(50) NULL,
		[IsPublished] bit NOT NULL,
		[IsActive] bit NOT NULL,
		SlotsFilled smallint NOT NULL,
		NumWorkersNotified int NOT NULL,
		ShiftViews int NOT NULL,
		Createdby int NOT NULL,
		CreatedDate datetime NOT NULL,
        CreatedByName VARCHAR(200),
        FlexShiftCount int not null DEFAULT 0
	) 

	IF(@Role = 'ShiftSupervisor' or @Role = 'ReadOnly')
	BEGIN		
		INSERT INTO @tmpShift ([Id],[StartDateTime],[EndDateTime],[JobId],[WorkCenterId],[NumWorkersNeeded],
				[ShiftPremium],[IsPublished],[IsActive],
				SlotsFilled,
				NumWorkersNotified,
				ShiftViews,Createdby,CreatedDate,CreatedByName,FlexShiftCount)
		SELECT [Id],[StartDateTime],[EndDateTime],[JobId],[WorkCenterId],[NumWorkersNeeded],
				[ShiftPremium],[IsPublished],[IsActive],
				(SELECT COUNT(*) FROM [FTWorkerShift] ws 							
								WHERE ws.ShiftId = s.Id AND ws.[Status] = 'A') AS SlotsFilled, 
				0 AS NumWorkersNotified,
				0 AS ShiftViews,
				s.[CreatedBy] AS CreatedBy,
				s.CreationDate as CreatedDate,
                (select CONCAT(FirstName,' ',LastName) from [User] where Id=s.CreatedBy) as CreatedByName,
				(select count(*) from FTShiftFlexShift where FtShiftId=s.Id) as FlexShiftCount
				 FROM [FTShift] s                 
					WHERE 
                        JobId in (select JobId from JobWorkcenter INNER join Job on JobWorkcenter.JobId=Job.Id where Job.IsActive = 1 AND WorkCenterId IN (select items from dbo.Split(dbo.udfUserWorkCenters(@userId),',')))
						--WorkCenterId IN (SELECT EntityId FROM EntityUser WHERE UserId = @userId)
						--AND CAST(StartDateTime as date) BETWEEN @StartDate AND @EndDate
						AND EndDateTime  >  @StartDate  
						AND StartDateTime < DATEADD(d, 1, @EndDate) 
						AND [IsActive]=1
		
		SELECT [Id],[StartDateTime],[EndDateTime],[JobId],
				[WorkCenterId],[NumWorkersNeeded],[ShiftPremium],[IsPublished],[IsActive],SlotsFilled,NumWorkersNotified,ShiftViews,CreatedBy,CreatedDate,CreatedByName,FlexShiftCount
			FROM @tmpShift
		SELECT Id, [Name], HexColor,IsActive FROM Job WHERE Id in (SELECT JobId FROM @tmpShift);
		SELECT Id,[Name] FROM WorkCenter WHERE Id in (SELECT WorkCenterId FROM @tmpShift);

	END
	ELSE IF (@Role = 'LocationAdmin')
	BEGIN
		INSERT INTO @tmpShift ([Id],[StartDateTime],[EndDateTime],[JobId],[WorkCenterId],[NumWorkersNeeded],
				[ShiftPremium],[IsPublished],[IsActive],
				SlotsFilled,
				NumWorkersNotified,
				ShiftViews,Createdby,CreatedDAte,CreatedByName,FlexShiftCount)
			SELECT [Id],[StartDateTime],[EndDateTime],[JobId],[WorkCenterId],[NumWorkersNeeded],
				[ShiftPremium],[IsPublished],[IsActive],
				(SELECT COUNT(*) FROM [FTWorkerShift] ws 							
								WHERE ws.ShiftId = s.Id AND ws.[Status] = 'A') AS SlotsFilled, 
				0 AS NumWorkersNotified,	
				0 AS ShiftViews,
				s.[CreatedBy] AS CreatedBy,
				s.[CreationDate] As CreatedDate,
                (select CONCAT(FirstName,' ',LastName) from [User] where Id=s.CreatedBy) as CreatedByName,
                (select count(*) from FTShiftFlexShift where FtShiftId=s.Id) as FlexShiftCount
				FROM [FTShift] s
					WHERE 
                        JobId in (select JobId from JobWorkcenter INNER join Job on JobWorkcenter.JobId=Job.Id where Job.IsActive = 1 AND WorkCenterId IN (select items from dbo.Split(dbo.udfUserWorkCenters(@userId),',')))
                        --WorkCenterId IN (SELECT Id FROM WorkCenter WHERE LocationId IN (SELECT EntityId FROM EntityUser WHERE UserId = @userId))
						--AND  CAST(StartDateTime as date) BETWEEN @StartDate AND @EndDate
						AND EndDateTime  >  @StartDate  
						AND StartDateTime < DATEADD(d, 1, @EndDate) 
						AND [IsActive]=1
					  --AND [ShiftDate] BETWEEN DATEADD(wk, DATEDIFF(wk,0,GETDATE()) - 1, 0) AND DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) + 20
				
		SELECT [Id],[StartDateTime],[EndDateTime],[JobId],
				[WorkCenterId],[NumWorkersNeeded],[ShiftPremium],[IsPublished],[IsActive],SlotsFilled,NumWorkersNotified,ShiftViews,CreatedBy,CreatedDate,CreatedByName,FlexShiftCount
			FROM @tmpShift
		SELECT Id, [Name], HexColor,IsActive FROM Job WHERE Id in (SELECT JobId FROM @tmpShift);
		SELECT Id,[Name] FROM WorkCenter WHERE Id in (SELECT WorkCenterId FROM @tmpShift);
	END
END;

CREATE PROCEDURE [dbo].[GetAllJobsAtLocation]
	-- Add the parameters for the stored procedure here
	@Id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

   SELECT j.Id, l.[Name] as [Location], j.Code, j.[Name], j.Category, j.SubCategory, j.HexColor, j.IsActive 
		FROM Job j 
		inner join jobworkcenter jw on j.id=jw.jobid
		inner join WorkCenter wc on jw.workcenterid=wc.id
		INNER JOIN [Location] l ON wc.LocationId = l.Id	
			WHERE l.Id = @Id

END;

CREATE PROCEDURE [dbo].[GetAllLocationsAndCompanyByUser]
	@userid int
AS
BEGIN

SELECT l.Id AS LocationId, c.Id AS CompanyId, l.[Name] AS LocationName, c.[Name] AS CompanyName  
	FROM [Location] l INNER JOIN Company c ON l.CompanyId = c.Id WHERE 
				l.Id IN (SELECT EntityId FROM EntityUser WHERE UserId = @userid 
					AND LuEntityId IN (SELECT Id FROM [dbo].[LuEntity] WHERE Entity IN ('Location', 'WorkCenter')))
				AND l.IsActive = 1 AND c.IsActive = 1
END;

CREATE PROCEDURE [dbo].[GetAllShiftsByUser]	
	@userId INT,
	@companyId INT,
	@startDate datetimeoffset,
	@endDate datetimeoffset
AS
BEGIN	
	
	DECLARE @Role VARCHAR(50)
	SET @Role = (SELECT [Role] FROM [User] u INNER JOIN LuRole lr ON u.RoleId = lr.Id WHERE u.Id = @userId)
	
	DECLARE @tmpShift TABLE 
	( 
		Id int NOT NULL, 
		[StartDateTime] datetimeoffset NOT NULL,
		[EndDateTime] datetimeoffset NOT NULL,
		[JobId] int NOT NULL,
		[WorkCenterId] int NOT NULL,
		[NumWorkersNeeded] smallint NOT NULL,
		[ShiftPremium] varchar(50) NULL,
		[IsPublished] bit NOT NULL,
		[IsActive] bit NOT NULL,
		SlotsFilled smallint NOT NULL,
		NumWorkersNotified int NOT NULL,
		ShiftViews int NOT NULL,
		Createdby int NOT NULL,
		CreatedDate datetime NOT NULL,
        CreatedByName VARCHAR(200)
	) 

	IF(@Role = 'ShiftSupervisor' or @Role = 'ReadOnly')
	BEGIN		
		INSERT INTO @tmpShift ([Id],[StartDateTime],[EndDateTime],[JobId],[WorkCenterId],[NumWorkersNeeded],
				[ShiftPremium],[IsPublished],[IsActive],
				SlotsFilled,
				NumWorkersNotified,
				ShiftViews,Createdby,CreatedDate,CreatedByName)
		SELECT [Id],[StartDateTime],[EndDateTime],[JobId],[WorkCenterId],[NumWorkersNeeded],
				[ShiftPremium],[IsPublished],[IsActive],
				[dbo].[udfSlotsFilled](Id) AS SlotsFilled, 
				[dbo].[udfMaxWorkersForJob](JobId) AS NumWorkersNotified,
				[dbo].[udfShiftViews](Id,@companyId) AS ShiftViews,
				s.[CreatedBy] AS CreatedBy,
				s.CreationDate as CreatedDate,
                (select CONCAT(FirstName,' ',LastName) from [User] where Id=s.CreatedBy) as CreatedByName
				 FROM [Shift] s                 
					WHERE 
                        JobId in (select JobId from JobWorkcenter INNER join Job on JobWorkcenter.JobId=Job.Id where Job.IsActive = 1 AND WorkCenterId IN (select items from dbo.Split(dbo.udfUserWorkCenters(@userId),',')))
						--WorkCenterId IN (SELECT EntityId FROM EntityUser WHERE UserId = @userId)
						--AND CAST(StartDateTime as date) BETWEEN @StartDate AND @EndDate
						AND EndDateTime  >  @StartDate  
						AND StartDateTime < DATEADD(d, 1, @EndDate) 
						AND [IsActive]=1
		
		SELECT [Id],[StartDateTime],[EndDateTime],[JobId],
				[WorkCenterId],[NumWorkersNeeded],[ShiftPremium],[IsPublished],[IsActive],SlotsFilled,NumWorkersNotified,ShiftViews,CreatedBy,CreatedDate,CreatedByName
			FROM @tmpShift
		SELECT Id, [Name], HexColor,IsActive FROM Job WHERE Id in (SELECT JobId FROM @tmpShift);
		SELECT Id,[Name] FROM WorkCenter WHERE Id in (SELECT WorkCenterId FROM @tmpShift);

	END
	ELSE IF (@Role = 'LocationAdmin')
	BEGIN
		INSERT INTO @tmpShift ([Id],[StartDateTime],[EndDateTime],[JobId],[WorkCenterId],[NumWorkersNeeded],
				[ShiftPremium],[IsPublished],[IsActive],
				SlotsFilled,
				NumWorkersNotified,
				ShiftViews,Createdby,CreatedDAte,CreatedByName)
			SELECT [Id],[StartDateTime],[EndDateTime],[JobId],[WorkCenterId],[NumWorkersNeeded],
				[ShiftPremium],[IsPublished],[IsActive],
				[dbo].[udfSlotsFilled](Id) AS SlotsFilled, 
				[dbo].[udfMaxWorkersForJob](JobId) AS NumWorkersNotified,	
				[dbo].[udfShiftViews](Id,@companyId) AS ShiftViews,
				s.[CreatedBy] AS CreatedBy,
				s.[CreationDate] As CreatedDate,
                (select CONCAT(FirstName,' ',LastName) from [User] where Id=s.CreatedBy) as CreatedByName
				FROM [Shift] s
					WHERE 
                        JobId in (select JobId from JobWorkcenter INNER join Job on JobWorkcenter.JobId=Job.Id where Job.IsActive = 1 AND WorkCenterId IN (select items from dbo.Split(dbo.udfUserWorkCenters(@userId),',')))
                        --WorkCenterId IN (SELECT Id FROM WorkCenter WHERE LocationId IN (SELECT EntityId FROM EntityUser WHERE UserId = @userId))
						--AND  CAST(StartDateTime as date) BETWEEN @StartDate AND @EndDate
						AND EndDateTime  >  @StartDate  
						AND StartDateTime < DATEADD(d, 1, @EndDate) 
						AND [IsActive]=1
					  --AND [ShiftDate] BETWEEN DATEADD(wk, DATEDIFF(wk,0,GETDATE()) - 1, 0) AND DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) + 20
				
		SELECT [Id],[StartDateTime],[EndDateTime],[JobId],
				[WorkCenterId],[NumWorkersNeeded],[ShiftPremium],[IsPublished],[IsActive],SlotsFilled,NumWorkersNotified,ShiftViews,CreatedBy,CreatedDate,CreatedByName
			FROM @tmpShift
		SELECT Id, [Name], HexColor,IsActive FROM Job WHERE Id in (SELECT JobId FROM @tmpShift);
		SELECT Id,[Name] FROM WorkCenter WHERE Id in (SELECT WorkCenterId FROM @tmpShift);
	END
END;

CREATE PROCEDURE [dbo].[GetAllUsersAtCompany]
	-- Add the parameters for the stored procedure here
	@Id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT u.Id, r.[Role], FirstName, LastName, Email, PhoneNumber, u.isActive 
		FROM [User] u INNER JOIN LuRole r ON u.RoleId = r.Id
				INNER JOIN Company c ON u.CompanyId = c.Id 
			WHERE c.Id = @Id

END;

CREATE     PROCEDURE [dbo].[GetAllWorkersForUser]
    @UserId INT
AS
BEGIN

    -- get user company id (to check active workers)
    DECLARE @CompanyId INT    
    SET @CompanyId = (select CompanyId from [User] where Id=@UserId)
    
    -- select all user jobids
    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),','))

    -- table[0] select all worker skils
    SELECT 
        wss.SkillId, wss.SkillLevelId, sl.Seq LevelSeq, 
        sl.Name LevelName, s.Name, wss.WorkerId
    from WorkerSkill wss
    INNER JOIN Skill s on wss.SkillId=s.Id and s.CompanyId=@CompanyId
    INNER JOIN SkillLevel sl on wss.SkillLevelId=sl.Id
    where 
    wss.WorkerId in (

            -- select all company workers
            select cw.WorkerId from CompanyWorker cw where cw.CompanyId=@CompanyId and cw.IsActive=1

            -- select workers only associated with user jobs (disabled due to GAT-551)
            -- SELECT 
            --     distinct wj.WorkerId
            -- from WorkerJob wj
            -- WHERE
            -- wj.JobId in (select Id from @UserJobs)
        )
    AND s.IsActive = 1

    -- table[1] select all worker skil interests
    SELECT 
        wssi.id,
        wssi.SkillId, wssi.SkillLevelId, sl.Seq LevelSeq, 
        sl.Name LevelName, s.Name, wssi.WorkerId
    from WorkerSkillInterest wssi
    INNER JOIN Skill s on wssi.SkillId=s.Id and s.CompanyId=@CompanyId
    INNER JOIN SkillLevel sl on wssi.SkillLevelId=sl.Id
    where 
    wssi.Status = 'N'
    and wssi.WorkerId in (

            -- select all company workers
            select cw.WorkerId from CompanyWorker cw where cw.CompanyId=@CompanyId and cw.IsActive=1

            -- select workers only associated with user jobs (disabled due to GAT-551)
            -- SELECT 
            --     distinct wj.WorkerId
            -- from WorkerJob wj
            -- WHERE
            -- wj.JobId in (select Id from @UserJobs)
        )
    AND s.IsActive = 1

    -- table[2] get all workerjob records with expiry status for all workers from user company
    select wj.Id, wj.WorkerId, wj.JobId, wj.IsExpired
    from WorkerJob wj
    INNER JOIN Job j on wj.JobId=j.Id and j.IsActive=1
    where 
    wj.JobId in (select Id from @UserJobs)
    and wj.WorkerId in (select WorkerId from CompanyWorker where CompanyId=@CompanyId and IsActive=1)

    -- table[3] select workers with jobs associated with user
    ;with wte as (

        -- select all company workers
        select cw.WorkerId from CompanyWorker cw where cw.CompanyId=@CompanyId and cw.IsActive=1

        -- select workers only associated with user jobs (disabled due to GAT-551)
        -- SELECT 
        --     distinct wj.WorkerId
        -- from WorkerJob wj
        -- WHERE
        -- wj.JobId in (select Id from @UserJobs)     
    )
    SELECT 
        w.[Id], 
        w.FirstName, 
        w.MiddleName, 
        w.LastName, w.Email, 
        w.MobilePhoneNumber as PhoneNumber,
        wt.[Type], 
        (Select Name from Company where Id = @CompanyId) as [CompanyName],
        --dbo.udfWorkerJobs(w.Id,@CompanyId) AS Jobs, 
        (SELECT STRING_AGG(wj1.JobId,',') FROM WorkerJob wj1					
					WHERE 
						wj1.WorkerId = wte.WorkerId
						and wj1.JobId in (select Id from @UserJobs)) as Jobs,
        cw.[IsActive],
        w.RScore,
        w.TotalCompletedHours,
        (Select ISNULL(Title,'NA')  from ExpLevel where Id=w.ExpLevelId) ExpLevelName,
        (
            SELECT string_agg(sub.SkillId,',') FROM
            (
                SELECT ws.SkillId 
                from WorkerSkill ws 
                INNER JOIN skill s on ws.SkillId=s.Id and s.IsActive=1 and s.CompanyId=@CompanyId
                where 
                ws.WorkerId=wte.WorkerId

                EXCEPT

                SELECT 
                --wj.JobId,
                js.SkillId 
                from WorkerJob wj
                INNER JOIN Job j on wj.JobId=j.Id and j.IsActive=1 and j.CompanyId=@CompanyId
                INNER join JobSkill js on wj.JobId=js.JobId and js.IsActive=1
                where
                wj.WorkerId=wte.WorkerId
                and wj.JobId in (select Id from @UserJobs)
            ) sub
        ) OtherSkillIds
    from wte
    INNER join Worker w on wte.WorkerId=w.Id
    INNER JOIN CompanyWorker cw ON w.id = cw.WorkerId and cw.CompanyId=@CompanyId        
    INNER JOIN LuWorkerType wt ON cw.WorkerTypeId = wt.Id
    WHERE
    cw.IsActive = 1   
    ORDER  by w.CreationDate DESC

END;

CREATE PROCEDURE [dbo].[GetCancelledShiftDetailsById]	
	@shiftId BIGINT,
	@workerId UNIQUEIDENTIFIER
AS
BEGIN	

    DECLARE @LocationTimeZone VARCHAR(100)
    DECLARE @CompanyContactForWorker VARCHAR(100)
    DECLARE @CompanyId INt 

	-- get location time zone, company contact for workers, companyid    
    select 
        @LocationTimeZone = l.time_zone,
        @CompanyContactForWorker = l.CompanyContactForWorker,
        @CompanyId = l.CompanyId
    from 
        [Location] l
    INNER JOIN WorkCenter wc on l.Id=wc.LocationId
    INNER JOIN Shift s on wc.Id=s.WorkCenterId
    where 
        s.id=@shiftId
    
    -- [0] get job name, shift start and end date time    
	SELECT 
        j.[Name], 
        CONVERT(datetime, SWITCHOFFSET(StartDateTime, DATEPART(TZOFFSET, StartDateTime AT TIME ZONE @LocationTimeZone))) AS StartDateTime, 
        EndDateTime 
    FROM [Shift] s 
    INNER JOIN Job j ON s.JobId = j.Id 
	WHERE 
        s.Id = @shiftId

    -- [1] get company shift cancellation settings
	SELECT 
        [WorkerShiftCancelDays],
        [ShiftCancelAllow],
        [ShiftConfirmationLockHours],
        [AllowShiftForceCancel]  
    FROM 
        [Settings_ShiftCreation] 
	WHERE 
        CompanyId = @CompanyId

    -- [2] get worker name and company contact from location
	SELECT 
        Id, FirstName+' '+LastName AS [Name], 
        @CompanyContactForWorker as CompanyContactForWorker 
    FROM 
        Worker 
    WHERE 
        Id = @workerId

    -- [3] get user details
	SELECT 
        Id,FirstName,LastName,Email 
    FROM 
        [User] 
    WHERE 
    IsActive = 1
    AND
    Id IN (
		SELECT UserId FROM EntityUser 
			WHERE EntityId IN (SELECT WorkCenterId FROM [Shift] WHERE id = @shiftId))
				AND RoleId = (SELECT Id FROM LuRole WHERE [Role] = 'ShiftSupervisor')

    -- [4] get worker confirmation date for the shift
    SELECT LastUpdatedDate from WorkerShift ws where ShiftId = @shiftId and WorkerId = @workerid and Status = 'A'
END;

CREATE PROCEDURE [dbo].[GetCompanyById]
	-- Add the parameters for the stored procedure here
	@Id int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    SELECT Id, [Name], IsActive
		FROM Company
			WHERE Id = @Id
			AND IsActive = 1

    select * from Settings_ShiftCreation where CompanyId=@Id

    SELECT * from WorkerTypeHours where CompanyId=@Id
END;

CREATE   PROCEDURE [dbo].[GetDailyReportEmailInfo]
AS
BEGIN

 DECLARE @LT TABLE(Id INT)

-- get all active location with curr time as time for daily report email
INSERT INTO @LT
SELECT l.Id
 FROM [Location] l
 INNER JOIN Settings_ShiftCreation ss on ss.CompanyId = l.CompanyId
 --WHERE DailyReportEmailTime = FORMAT(GETDATE() at time zone 'UTC' at time zone l.TIME_ZONE,'HH:mm') 
 WHERE 
    FORMAT(cast(GETDATE() as datetimeoffset) at time zone l.TIME_ZONE,'HH:mm')
        BETWEEN DATEADD(MINUTE,-1,DailyReportEmailTime) and DATEADD(MINUTE,1,DailyReportEmailTime) -- keep a margin of one minute
 AND IsActive = 1
 and ss.EnableDailyReportInEmail = 1


-- [START] get all userids, locationid pairs with above locations
DECLARE @UL TABLE(UserId INT, LocationId INT);

with user_wc
as
(
select eu.UserId,eu.EntityId as WorkCenterId from EntityUser eu
WHERE
eu.LuEntityId = (select Id from LuEntity where Entity='WorkCenter')
AND eu.EntityId in (select wc.Id from WorkCenter wc where wc.LocationId in (select Id from @LT))
)

INSERT INTO @UL
select distinct user_wc.UserId, wc.LocationId from user_wc
INNER JOIN WorkCenter wc on user_wc.WorkCenterId=wc.Id

UNION ALL

select distinct eu.UserId,eu.EntityId as LocationId from EntityUser eu
WHERE
eu.LuEntityId = (select Id from LuEntity where Entity='Location')
AND eu.EntityId in (select Id from @LT)
-- [END] get all userids, locationid pairs with above locations

-- get user info with report date in UTC to be used to generate report
select u.*,c.Name as CompanyName,
    DATEADD(DAY,1,cast(cast(
        GETDATE() at time zone 'UTC' at time zone l.TIME_ZONE
    as date) as datetime)) at time ZONE l.TIME_ZONE at time ZONE 'UTC' as ReportDateUTC,
    cast(DATEADD(DAY,1,GETDATE()) as datetimeoffset) at time ZONE l.TIME_ZONE as ReportDate
 from @UL ul
INNER JOIN [User] u on ul.UserId=u.Id
INNER JOIN [Location] l on ul.LocationId=l.Id
INNER JOIN Company c on u.CompanyId=c.Id
where u.IsActive = 1

SELECT CAST(DATEADD(HOUR,-5,DATEADD(DAY,1,GETDATE())) as datetime) AS ReportDate

END;

CREATE PROCEDURE [dbo].[GetFutureShiftForJobWorkCenter]	
    @jobId INT,
    @workCenterIdsCsv VARCHAR(50),
    @userId INT
as
BEGIN
    DECLARE @oldWorkCenters TABLE(Id INT)
    DECLARE @newWorkCenters TABLE(Id INT)
    DECLARE @missingWorkCenters TABLE(Id INT)


    -- get all work center ids for @jId (oldWorkCenterIds) related to @userId
    Insert into @oldWorkCenters
    select WorkcenterId 
        from JobWorkcenter 
    WHERE 
        JobId=@jobId 
        and WorkcenterId in 
            (select items 
                from dbo.Split(dbo.udfUserWorkCenters(@userId),','))

    -- split @workCenterIdsCsv into newWorkCenterIds
    INSERT into @newWorkCenters
    SELECT items from dbo.Split(@workCenterIdsCsv,',')

    -- find oldWorkCenterIds minus newWorkCenterIds (present in first but not in later) missingWorkCenterIds'
    INSERT INTO @missingWorkCenters 
    select Id from @oldWorkCenters 
    EXCEPT 
    select Id from @newWorkCenters

    -- find all shifts in future for @jId and missingWorkCenterIds
    select * from Shift
    WHERE
    StartDateTime > GETDATE()
    and IsActive = 1
    and JobId = @jobId
    and WorkCenterId in (select Id from @missingWorkCenters)
END;

CREATE   PROCEDURE [dbo].[GetJobsByJobIds]
    @jobids NVARCHAR(1000)
AS
BEGIN

    select * from job where Id in (select items from dbo.split(@jobids,','))

END;

CREATE   PROCEDURE [dbo].[GetJobsByUserId]
	@userid int
AS
BEGIN

    Declare @UserWorkCenterIds Table(Id int)

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

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

	SELECT 	
		j.Id
		, Code
		, [Name]
		, Category
		, SubCategory
		, HexColor
		, [dbo].[udfMaxWorkersForJob](j.Id) AS MaxWorkers
		, IsActive
        ,(select STRING_AGG(jw.WorkCenterId,',') 
            from JobWorkCenter jw 
            where jw.JobId=j.Id 
            and jw.WorkcenterId in (select Id from @UserWorkCenterIds)) WorkCenterIds
	FROM 
		Job j
			--inner join JobWorkcenter jw on j.Id = jw.JobId		
	WHERE 
        j.IsActive = 1
		and j.id in (select jw.jobid from JobWorkCenter jw where 
		jw.WorkcenterId in (select Id from @UserWorkCenterIds)
        )
	ORDER BY
 	Name

    -- fetch all skills for above jobs (filter in application)
    SELECT 
        js.Id,js.JobId,s.Id as SkillId,s.Name as SkillName
    from 
        JobSkill js
    INNER join Skill s on js.SkillId = s.Id and s.CompanyId=@UserCompanyId
    where 
        js.IsActive = 1 
        and js.JobId in (select jw.jobid
                            from JobWorkCenter jw
                            where 
                                    jw.WorkcenterId in (select Id
                            from @UserWorkCenterIds)
                        )
    
END;

CREATE   PROCEDURE [dbo].[GetJobScheduleData]
    @CompanyId INT,
    @StartDate DATE,
    @EndDate DATE
AS
BEGIN

    -- table[0] job skills
    select 
        j.Id JobId,[Name] as JobName,ISNULL(jst.JobHrs,8) as hours,ISNULL(jst.JobPriority,1) as priority,
        ISNULL((
            select STRING_AGG(s.Name,',') from JobSkill js
            INNER JOIN Skill s on js.SkillId=s.Id
            where js.JobId=j.Id and js.IsActive=1
        ),'') skills
    from job j
    LEFT JOIN JobSetting jst on j.Id=jst.JobId
    where j.IsActive=1 and j.companyid=@CompanyId
    -- table[1]  worker skills
    SELECT 
        w.Id as WorkerId, w.FirstName + ' ' + w.LastName as Worker,
        (
            select STRING_AGG(s.Name,',') from WorkerSkill ws
            INNER join Skill s on ws.SkillId=s.Id
            where ws.WorkerId=w.Id
        )  skills
    from companyworker cw
    inner join Worker w  on cw.WorkerId=w.Id
    where cw.CompanyId=@CompanyId and cw.IsActive=1
    and cw.WorkerTypeId=(select Id from LuWorkerType where Type='FullTime')

    -- table[2]  weekly job requirements
    -- Generate the dates between the start and end date using a recursive CTE
    ;WITH DateRange (DateValue) AS (
        SELECT @StartDate
        UNION ALL
        SELECT DATEADD(DAY, 1, DateValue)
        FROM DateRange
        WHERE DateValue < @EndDate
    )
    select * from (
    SELECT Job.Id as JobId, Job.Name as JobName, DateValue, DATENAME(WEEKDAY, DateValue) AS DayName,
    ISNULL(
        (select WorkerCount from FullTimeWorkerRequirement wr where wr.JobId=Job.Id and wr.RDate=DateValue)
    ,0) JobWorkerRequirement
    FROM DateRange,Job
    where Job.IsActive = 1 and Job.CompanyId=@CompanyId
    )sub
    where sub.JobWorkerRequirement>0
    order by sub.JobId, sub.DateValue
    OPTION (MAXRECURSION 0);

    -- table[3]  weekly worker availability
    -- Generate the dates between the start and end date using a recursive CTE
    ;WITH DateRange (DateValue) AS (
        SELECT @StartDate
        UNION ALL
        SELECT DATEADD(DAY, 1, DateValue)
        FROM DateRange
        WHERE DateValue < @EndDate
    )
    select * from (
    SELECT cw.WorkerId,w.FirstName + ' ' + w.LastName as Name, DateValue, DATENAME(WEEKDAY, DateValue) AS DayName,
    ISNULL(
        (
            select 
                case 
                    when fwat.Name='Absent' then 'absent' 
                    when fwat.Name='In Training' then 'unavailable'
                    when fwat.Name='Available + OT' then 'available+ot'
                    else 'available' 
                end
            from FulltimeWorkerAvailability fwa 
            inner join fulltimeworkeravailabilitytype fwat on fwa.typeid=fwat.Id
            where fwa.WorkerId=cw.WorkerId and fwa.AvailabilityDate=DateValue
        )
    ,'available') WorkerAvailability
    FROM DateRange,CompanyWorker cw
    INNER JOIN worker w on cw.WorkerId=w.Id
    where cw.CompanyId=@CompanyId and cw.WorkerTypeId=(select Id from LuWorkerType where Type='FullTime')
    )sub
    OPTION (MAXRECURSION 0);

END;

CREATE   PROCEDURE [dbo].[GetJobSkillsForWorker]
    @WorkerId UNIQUEIDENTIFIER
as
BEGIN

    DECLARE @JobIds TABLE(JobId BIGINT)

    -- get all job ids associated with worker companies
    INSERT INTO @JobIds
    SELECT Id
    from Job
    where 
    CompanyId in (select companyid
                from CompanyWorker
                where WorkerId=@WorkerId) 
    and IsActive=1

    -- table[0] get all jobs for the worker companies
    SELECT Id,[Name],HexColor from Job where Id in (select JobId from @JobIds)

    -- table[1] get all skills for the above jobs
    SELECT 
        js.Id JobSKillId, js.JobId,
        js.SkillId,s.Name SkillName
    from JobSkill js
    INNER JOIN Skill s on js.SkillId=s.Id
    where 
    js.JobId in (select JobId
        from @JobIds) 
    and js.IsActive = 1
    and s.IsActive = 1

        -- table[2] get all skills associated with worker companies 
        -- and not associated with worker jobs
    SELECT Id,[Name] 
    from Skill s 
    where s.IsActive = 1
    and s.CompanyId in (select companyid 
            from CompanyWorker
            where WorkerId=@WorkerId) 
    and s.Id not in (select js.SkillId 
            from JobSkill js 
            where js.JobId in (select JobId
                from @JobIds) 
                and js.IsActive = 1)


END;

CREATE PROCEDURE [dbo].[GetLoginCredentials]
	-- Add the parameters for the stored procedure here
	@username nvarchar(150)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @Id INT;
	SET @Id = (SELECT Id FROM [Login] WHERE [UserName] = @username);

	IF (@Id > 0)
	BEGIN 
		SELECT u.Id, l.[Password], l.SaltValue, l.RefreshToken, l.ChangePasswordOnLogin, l.FailedAttemptedLogins 
			FROM [User] u INNER JOIN [Login] l ON u.LoginId = l.Id
			WHERE l.Id = @Id	
	END
    
END;

CREATE PROCEDURE [dbo].[GetPassword]
	@UserId int
AS
BEGIN		
	
	SELECT [Password], [SaltValue] FROM [Login] WHERE  Id = (SELECT LoginId FROM [User] WHERE Id = @UserId)			
	
END;

CREATE   PROCEDURE [dbo].[GetReportAvailableWorkerCount]
    @StartDate datetimeoffset,
    @EndDate datetimeoffset,
    @UserId INT
AS
BEGIN

    -- collect all workers with jobs associated with the user
    DECLARE @WorkerJobs TABLE(wid UNIQUEIDENTIFIER, jid INT)
    INSERT INTO @WorkerJobs
    select workerid,JobId from WorkerJob where JobId in 
    (SELECT JobId
    from JobWorkCenter
    where WorkcenterId in (select items
    from dbo.split(dbo.udfUserWorkcenters(@userId),',')))

    -- generate hours between the given dates
    DECLARE @HourTable TABLE(StartHour datetimeoffset,EndHour datetimeoffset)

    SET @endDate = DATEADD(DAY,1,@endDate)
    WHILE @startDate < @endDate
    BEGIN
        
        INSERT INTO @HourTable
        select @startDate,DATEADD(HOUR,1,@startDate)

        set @startDate = DATEADD(HOUR,1,@startDate)
    END

    -- count workers by availability per hour for each jobid
    ;with cte as (
        SELECT ht.*,wa.WorkerId,wj.jid,wa.StartDateTime,wa.EndDateTime from @HourTable ht
        INNER JOIN WorkerAvailability wa on (wa.StartDateTime<=ht.startHour and ht.endHour<=wa.EndDateTime)
        INNER JOIN @WorkerJobs wj on wa.WorkerId=wj.wid     
        INNER JOIN CompanyWorker cw on wj.wid=cw.WorkerId and 
                        cw.CompanyId=(select companyid from [User] where Id=@UserId)
                        and cw.IsActive=1
    )    
    select cte.starthour,cte.endhour,cte.jid,count(cte.workerid) WorkerCount from cte
    GROUP BY cte.StartHour,cte.EndHour,cte.jid

END;

CREATE PROCEDURE [dbo].[GetReportDayWorkers]	
	@UserId INT,
	@DateOfReport datetimeoffset
AS
BEGIN	
	Declare @LocationId INT
	
    /*
	-- get user location from workcenter and use in the following query
	Set @LocationId = (select  top 1 wc.LocationId  from EntityUser eu
						inner join WorkCenter wc on eu.EntityId=wc.Id
					where 
						eu.LuEntityId=3 and eu.UserId=@UserId)

	if @LocationId = null or @LocationId <= 0 
	Begin			
			THROW 50000, 'Error.SP.CreateJob.NoLocationFoundForUser', 1
	end
	*/	
	select 
	  	wc.Name "Workcenter"
		,s.StartDateTime 
		,s.EndDateTime 
		,j.Name  "Job"
		,j.HexColor "JobColor" 
		,w.Id 
		,w.FirstName 
		,w.LastName 
		,s.Id "ShiftId"
		,ws.attend_status
		,ws.Comment
		,ROW_NUMBER() over(order by s.StartDateTime ASC) 'RowId'
		, convert(datetime,StartDateTime at time zone (select time_zone from [Location] where Id=wc.LocationId)) 'LocalStartDateTime'
        , convert(datetime,EndDateTime at time zone (select time_zone from [Location] where Id=wc.LocationId)) 'LocalEndDateTime'
	from 
		Shift s 
		inner join WorkCenter wc on s.WorkCenterId = wc.Id 
	    inner join Job j  on s.JobId = j.Id 
	    inner join WorkerShift ws  on ws.ShiftId = s.Id
		right join Worker w on ws.WorkerId  = w.Id 
	WHERE 
	  	s.StartDateTime >= @DateOfReport
		and s.StartDateTime < DATEADD(day, 1, @DateOfReport)
		and ws.Status  = 'A'
		and s.IsActive = 1 
		-- and wc.LocationId=@LocationId -- removed to show records from all locations
		and wc.Id in (select  eu.EntityID  from EntityUser eu
					where 
						eu.LuEntityId=3 and eu.UserId=@UserId)

END;

CREATE   PROCEDURE [dbo].[GetReportJobOccupancy]
	@UserId INT,
	@DateOfReportStart datetimeoffset,
	@DateOfReportEnd datetimeoffset
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    Declare @LocationId INT
	
	-- get user location from workcenter
	Set @LocationId = (select  top 1 wc.LocationId  from EntityUser eu
						inner join WorkCenter wc on eu.EntityId=wc.Id						
					where 
						eu.LuEntityId=3 and eu.UserId=@UserId)

	if @LocationId = null or @LocationId <= 0 
	Begin			
			THROW 50000, 'Error.SP.CreateJob.NoLocationFoundForUser', 1
	end

	select sub.JobId,j.Name,sum(sub.accepted) Accepted,sum(sub.NumWorkersNeeded) Needed,sum(sub.NumWorkersNeeded)-sum(sub.accepted) Vacant from 
	(select
		(select count(ws.WorkerId) from WorkerShift ws
			where 
			ws.Status='A' and ws.ShiftId=s.Id) accepted,
			s.* 
		from Shift s 
		--inner join Job j on s.JobId=j.Id
		inner join jobworkcenter jw on s.jobid=jw.jobid
		inner join WorkCenter wc on jw.workcenterid=wc.id
		where 
		s.IsPublished=1 and s.IsActive=1
		and s.StartDateTime>=@DateOfReportStart
		 and s.StartDateTime  < DATEADD(DD,1,@DateOfReportEnd)
		and wc.LocationId=@LocationId
		) sub
		inner join job j on sub.JobId=j.Id
		group by sub.JobId,j.Name

END;

CREATE PROCEDURE [dbo].[GetReportWorkerHours]	
	@UserId INT,
	@DateOfReportStart datetimeoffset,
	@DateOfReportEnd datetimeoffset
AS
BEGIN	
	Declare @LocationId INT
	
    /*
	-- get user location from workcenter
	Set @LocationId = (select  top 1 wc.LocationId  from EntityUser eu
						inner join WorkCenter wc on eu.EntityId=wc.Id						
					where 
						eu.LuEntityId=3 and eu.UserId=@UserId)

	if @LocationId = null or @LocationId <= 0 
	Begin			
			THROW 50000, 'Error.SP.CreateJob.NoLocationFoundForUser', 1
	end
    */
	select 
	    wc.Name "Workcenter"
		,s.StartDateTime 
		,s.EndDateTime 
		,j.Name  "Job"
		,j.HexColor "JobColor" 
		,w.Id 
		,w.FirstName 
		,w.LastName
		,DATEDIFF(hour, s.StartDateTime, s.EndDateTime) "Hours" --, * --from WorkerShift ws where status = 'A'
		,wc.CostCenter
		,s.Id "ShiftId"
		,ws.attend_status
	from 
		Shift s 
	    inner join WorkerShift_V ws  on ws.ShiftId = s.Id
	    inner join WorkCenter wc on s.WorkCenterId = wc.Id 
	    inner join Job j  on s.JobId = j.Id 
		inner join Worker w on ws.WorkerId  = w.Id 
	where 
	    ws.status = 'A'
	    and s.StartDateTime >= @DateOfReportStart
	    and s.StartDateTime  < DATEADD(DD,1,@DateOfReportEnd)
		and s.IsActive = 1
		--and wc.LocationId = @LocationId -- removed to show records from all locations
		--and ws.IsAbsent <> 1
		and wc.Id in (select  eu.EntityID  from EntityUser eu
					where 
						eu.LuEntityId=3 and eu.UserId=@UserId)
	order by s.StartDateTime
END;

-- =============================================
-- Author:		Rayees Afroz
-- Create date: 2022-06-29
-- Description:	Get shift workers' attendance
-- =============================================
CREATE   PROCEDURE [dbo].[GetShiftAttendance] 
	@shiftid int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	select ShiftId,WorkerId,ATTEND_STATUS,Comment from WorkerShift where ShiftId=@shiftid and Status='A'
    
END;

-- this SP returns set of workers eligible for a shift auto accept based on the rules (see inline comments)
CREATE   PROCEDURE [dbo].[GetShiftAutoAcceptWorkers]
    @ShiftId INT
AS
BEGIN

    declare @LocalTimeZone VARCHAR(25) = 'Eastern Standard Time'

    -- fetch local time zone based on the shift->workcenter->location
    SET @LocalTimeZone =
        (
            SELECT l.Time_Zone from shift s
            inner join workcenter wc on s.WorkCenterId=wc.id
            inner join location l on l.id=wc.locationid
            where  s.id=@shiftId
        )

    -- get company settings for shift auto accept
    DECLARE @IsAutoAcceptEnabled BIT = 0
    SET @IsAutoAcceptEnabled = 
        (
            SELECT ss.EnableShiftAutoAccept from Settings_ShiftCreation ss
            INNER JOIN Job j on ss.companyid=j.companyid
            INNER join shift s on j.id=s.JobId
            where s.id=@shiftId 
        )

    -- if company shift auto accept is enabled
    IF @IsAutoAcceptEnabled = 1
    BEGIN

        ;with cte AS (
            select 
                wj.WorkerId,   
                wj.JobId,
                wj.IsExpired,                 
                --(0.2).composite-score
                dbo.udfWorkerCompositeScore(wj.WorkerId) cscore,
                --	2.With Auto-accept is on						
                dbo.udfGetWorkerSetting
                (
                    wj.WorkerId,
                    (
                        select Id 
                        from WorkerSettingType 
                        where Code='ShiftAutoAccept'
                    )
                ) WorkerShiftAutoAccept,
                dbo.udfGetWorkerSetting
                (
                    wj.WorkerId,
                    (
                        select Id 
                        from WorkerSettingType 
                        where Code='DailyMaxWorkHours'
                    )
                ) DailyMaxWorkHours,
                -- 3.With availability during the new shift period 			
                (   
                    SELECT count(*) from WorkerAvailability wa
                    where 
                    wa.WorkerId=wj.WorkerId
                    and wa.StartDateTime<=(select StartDateTime from shift where Id=@ShiftId)
                    and wa.EndDateTime>=(select EndDateTime from shift where Id=@ShiftId)
                )  Availability,
                -- 4.With no active accepted overlapping shift during the new shift period
                dbo.udfGetWorkerShiftByTime(
                    wj.WorkerId,
                    (select StartDateTime from shift where Id=@ShiftId),
                    (select EndDateTime from shift where Id=@ShiftId)
                ) OverlappingShiftId ,
                -- 5.With that day consumed hrs + new shift hrs < worker daily hrs limit
                dbo.udfGetWorkerSignedUpHrs(
                    wj.WorkerId,
                    (select cast(startdatetime at time zone @LocalTimeZone as datetime) from shift where Id=@shiftId),
                    @LocalTimeZone
                ) ConsumedHrs ,
                -- 6.check for company continious hrs rule
                dbo.udfCheckCompanyContiniousShiftSignup(
                    wj.WorkerId,
                    @shiftId
                ) ContiniousCheckPass
            from 
                WorkerJob wj
        )
        SELECT 
            cte.*,
            w.FirstName, w.MobilePhoneNumber,
            (select STRING_AGG(wdt.devicetoken,',')
                    from WorkerDeviceToken wdt
                    where wdt.WorkerId=w.Id and wdt.IsActive=1) DeviceTokens          
        from cte        
        INNER JOIN worker w on cte.workerid = w.id
        -- 1.With active (not expired) shift job
        where 
        cte.JobId=(Select JobId from Shift where Id=@ShiftId)
        and cte.IsExpired=0
        and cte.WorkerShiftAutoAccept = 'True'
        and cte.Availability>0
        and (
                cte.DailyMaxWorkHours = 0
    OR cte.ConsumedHrs + (select datediff(hour,startdatetime,EndDateTime) from shift where Id=@shiftId) < cte.DailyMaxWorkHours
            )
        order by cscore desc

    END

    

END;

CREATE PROCEDURE [dbo].[GetShiftById]	
	@Id int
AS
BEGIN	

    --DECLARE @LocationTimeZone VARCHAR(100)

	-- get location time zone
    -- set @LocationTimeZone = 
    --     (select l.time_zone from [Location] l
    --         INNER JOIN WorkCenter wc on l.Id=wc.LocationId
    --         INNER JOIN Shift s on wc.Id=s.WorkCenterId
    --         where s.id=@Id
    --     )

    SELECT 
    	s.Id
    	, s.StartDateTime
    	, s.EndDateTime
    	, s.JobId
    	, s.WorkCenterId
    	, s.NumWorkersNeeded
    	, s.ShiftPremium
    	, s.IsPublished
    	, s.IsActive
		, convert(datetime,s.StartDateTime at time zone l.time_zone) 'LocalStartDateTime',
		s.NumWorkersNeeded-dbo.udfSlotsFilled(s.Id) 'SlotsVacant',
        j.Name as JobName,
        l.Name as LocationName
		FROM [Shift] s
		inner join Job j on s.JobId = j.Id
        INNER join workcenter wc on s.workcenterid = wc.id
        inner join location l on wc.locationid = l.id
			WHERE s.Id = @Id
END;

CREATE PROCEDURE [dbo].[GetShiftByWorkCenter]	
	@WorkCenterId int,
	@StartDate date,
	@EndDate date	
AS
BEGIN			
	SELECT 
		[Id] 
		,[StartDateTime] 
		,[EndDateTime]
		,[JobId] 
		,[WorkCenterId] 
		,[NumWorkersNeeded]
		,[ShiftPremium]
		, IsPublished
		, [IsActive]
		,[CreatedBy]
		,[CreationDate]
		,[LastUpdatedBy]
		,[LastUpdatedDate]
	FROM [dbo].[Shift]  
			WHERE WorkCenterId = @WorkCenterId 
				AND	CAST(StartDateTime as date) BETWEEN @StartDate AND @EndDate		
					ORDER BY StartDateTime
END;

CREATE     PROCEDURE [dbo].[GetShiftProspectWorkers]
	@shiftid int,
    @excludeStatusCsv VARCHAR(20)
AS
BEGIN
	DECLARE @companyId int
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- get companyid from shiftid
	set @companyId = (select l.CompanyId from Shift s
						--inner join Job j on s.JobId=j.Id
				inner join WorkCenter wc on s.WorkCenterId=wc.Id
				inner join location l on wc.LocationId=l.id
						where s.Id=@shiftid)

	-- get prospective workers for a shift i.e. who can still signup for the shift
    SELECT w.Id, c.[Name] as Company, FirstName, MiddleName, LastName, Email, [MobilePhoneNumber], w.IsActive,
            (select STRING_AGG(wdt.devicetoken,',')
            from WorkerDeviceToken wdt
            where wdt.WorkerId=w.Id and wdt.IsActive=1) DeviceTokens        
		FROM [dbo].[Worker] w 
		INNER JOIN [dbo].[WorkerJob] wj ON wj.WorkerId = w.Id
		INNER JOIN [dbo].[CompanyWorker] cw ON cw.WorkerId = w.Id
		INNER JOIN dbo.Company c ON cw.CompanyId = c.Id and cw.CompanyId=@companyId
			WHERE wj.JobId=(select JobId from Shift where Id=@shiftid)
			AND cw.IsActive = 1
			AND w.id not in (select WorkerId 
                                from WorkerShift 
                                where ShiftId=@shiftid 
                                and Status in (select items from dbo.Split(@excludeStatusCsv,',')) -- exclude workers with workershift status
                            )
END;

-- this procedure will get all approved and not accepted shift swap requests for prospective workers
CREATE   PROCEDURE [dbo].[GetShiftSwaps]
    @ShiftIds VARCHAR(MAX)
AS
BEGIN

    SELECT * from ShiftSwap sp
    WHERE
    sp.IsActive=1
    and sp.ShiftId in (select items from dbo.Split(@ShiftIds,','))
    and (sp.ApprovalStatus = 'a' or sp.ApprovalStatus = 'p') and sp.IsActive = 1 and sp.AcceptedByWorkerId is NULL
    order by sp.RequestDate DESC
    

END;

create   PROCEDURE [dbo].[GetShiftSwapsForUser]
    @UserId INT
AS
BEGIN

    Declare @UserWorkCenterIds Table(Id int)

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

    DECLARE @UserJobs TABLE(Id INT)

    INSERT INTO @UserJobs
    SELECT Id
    FROM 
		Job j					
	WHERE 
        j.IsActive = 1
		and j.id in (select jw.jobid from JobWorkCenter jw where 
		jw.WorkcenterId in (select Id from @UserWorkCenterIds)
        )


    select sw.*, s.StartDateTime, s.EndDateTime, s.JobId, j.[Name] as JobName, NumWorkersNeeded, ShiftPremium,
    w.FirstName + ' ' + w.LastName as WorkerName,
    w2.FirstName + ' ' + w2.LastName as AcceptedByWorkerName
    from ShiftSwap sw
    INNER join Shift s on sw.ShiftId=s.Id
    INNER JOIN job j on s.JobId = j.Id
    LEFT JOIN worker w on w.id = sw.workerId
    LEFT JOIN worker w2 on w2.id = sw.AcceptedByWorkerId
    where 
    sw.ApprovalStatus='n' and sw.IsActive=1
    and s.JobId in (select Id from @UserJobs) 
    and s.startdatetime > GETDATE()    

END;

CREATE PROCEDURE [dbo].[GetSignedupShiftsByWorker]
(
	@workerid VARCHAR(50)
)
AS
BEGIN
	
	SELECT 
		s.Id
		, ws.[Status]
		, [StartDateTime]
		, [EndDateTime]		 
		, [JobId]
		, j.[Name] AS JobName
		, j.HexColor
		, [WorkCenterId]
		, wc.[Name] AS WorkcenterName
		, s.[ShiftPremium],
		dbo.udfIsNewShift(s.Id) IsNew,
        l.time_zone as LocationTimeZoneName,
        (select dbo.udfUtcOffsetToMin(tz.current_utc_offset) from sys.time_zone_info tz 
            where tz.name=l.time_zone) CurrUtcOffsetInMin,
            l.Name as  LocationName,
            l.BuildingNumber,
            l.Address1,
            l.City,
            l.[State],
            l.Zip,
            l.County,
            l.Country,
            (select Name from Company where Id=l.companyId) as  CompanyName,
            l.IsActive as LocationIsActive,
            (select top 1 AllowShiftForceCancel from Settings_ShiftCreation where CompanyId=l.CompanyId) AllowShiftForceCancel,
			(select IsExpired from WorkerJob wj where wj.WorkerId=@WorkerId and wj.JobId=s.JobId)  as IsWorkerJobExpired,
			(select case when IsExpiredBy>0 then cast(1 as bit) else cast(0 as bit) end from WorkerJob wj where wj.WorkerId=ws.WorkerId and wj.JobId=s.JobId)  as IsWorkerJobExpiredBySupervisor,
            j.MinWorkHrsDays JobMinWorkHrsDays ,
            cast(1 as bit) IsShiftSwap,
            (select top 1 ShiftSwapEnabled from Settings_ShiftCreation where CompanyId=l.CompanyId) AllowShiftSwap  ,
			0 as ShiftSwapId
		FROM [Shift] s 
			INNER JOIN Job j ON s.JobId = j.Id 
			INNER JOIN WorkCenter wc ON s.WorkCenterId = wc.Id
			INNER JOIN WorkerShift ws ON s.Id = ws.ShiftId			
            INNER JOIN [Location] l ON wc.LocationId=l.Id	
		WHERE 
			s.Id IN (SELECT ShiftId FROM dbo.WorkerShift WHERE WorkerId =  @workerid)					
				AND  CAST(StartDateTime AS DATE) >=  GETDATE()  - 1 
				AND ws.[Status] = 'A' 
				AND s.IsPublished = 1 AND s.IsActive = 1 
				AND j.IsActive = 1
				AND  ws.WorkerId = @workerid 
				--and (@companyid=0 or s.JobId in (select Id from Job where LocationId in (select Id from Location where CompanyId=@companyid)))

END;

CREATE PROCEDURE [dbo].[GetUpcomingShiftsForWorker]
(
	@workerid VARCHAR(50)
)
AS
BEGIN
	
	;WITH
		cteShiftsNotFilled (ShiftId, NumWorkersNeeded, SlotsFilled)
		AS
		(
			SELECT Id AS ShiftId, NumWorkersNeeded, dbo.udfSlotsFilled(Id) AS SlotsFilled FROM [Shift] 
				WHERE 
					 CAST(StartDateTime AS DATETIME)  >=  GETDATE() 
						AND IsPublished = 1 AND IsActive = 1 
		)
    -- get all shift except shifts from shift table but not from workershift table for the worker
	SELECT 
			s.Id, '' AS [Status],
			[StartDateTime],
			[EndDateTime],			
			j.[Id] AS JobId,
			j.[Name] AS JobName, 
			j.HexColor, [WorkCenterId], 
			wc.[Name] AS WorkcenterName, 
			s.[ShiftPremium],
			dbo.udfIsNewShift(s.Id) IsNew,
            l.time_zone as LocationTimeZoneName,
            (select dbo.udfUtcOffsetToMin(tz.current_utc_offset) from sys.time_zone_info tz 
                where tz.name=l.time_zone) CurrUtcOffsetInMin,
            l.Name as  LocationName,
            l.BuildingNumber,
            l.Address1,
            l.City,
            l.[State],
            l.Zip,
            l.County,
            l.Country,
            (select Name from Company where Id=l.companyId) as  CompanyName,
            l.IsActive as LocationIsActive,
            (select top 1 AllowShiftForceCancel from Settings_ShiftCreation where CompanyId=l.CompanyId) AllowShiftForceCancel,
            wj.IsExpired as IsWorkerJobExpired,
			(case when wj.IsExpiredBy>0 then cast(1 as bit) else cast(0 as bit) end) IsWorkerJobExpiredBySupervisor,
            j.MinWorkHrsDays JobMinWorkHrsDays,
            cast(0 as bit) IsShiftSwap,
            cast(0 as bit) AllowShiftSwap,
            0 as ShiftSwapId
	FROM [Shift] s 
		INNER JOIN [dbo].[WorkerJob] wj ON wj.JobId = s.JobId
		INNER JOIN Worker w ON wj.WorkerId = w.Id
		INNER JOIN CompanyWorker cw ON cw.WorkerId = w.Id
		INNER JOIN Job j ON s.JobId = j.Id 
		INNER JOIN WorkCenter wc ON s.WorkCenterId = wc.Id		
        INNER JOIN [Location] l ON wc.LocationId = l.Id
	WHERE wj.WorkerId =  @workerid
		AND s.Id NOT IN (SELECT ShiftId FROM WorkerShift WHERE WorkerId =  @workerid) --ignore reviewed shifts(A,D,C)
		AND s.Id IN (SELECT ShiftId FROM cteShiftsNotFilled WHERE SlotsFilled < NumWorkersNeeded) -- use shifts which are not filled		
		AND cw.IsActive = 1
UNION 		

    -- get all shifts from workershift table except the accepted one
	SELECT 
			s.Id, 			
			ws.[Status], 
			[StartDateTime],
			[EndDateTime],			
			j.[Id] AS JobId,
			j.[Name] AS JobName, 
			j.HexColor, [WorkCenterId], 
			wc.[Name] AS WorkcenterName, 
			s.[ShiftPremium],
			dbo.udfIsNewShift(s.Id) IsNew,
            l.time_zone as LocationTimeZoneName,
            (select dbo.udfUtcOffsetToMin(tz.current_utc_offset) from sys.time_zone_info tz 
                where tz.name=l.time_zone) CurrUtcOffsetInMin,
            l.Name as  LocationName,
            l.BuildingNumber,
            l.Address1,
            l.City,
            l.[State],
            l.Zip,
            l.County,
            l.Country,
            (select Name from Company where Id=l.companyId) as  CompanyName,
            l.IsActive as LocationIsActive,
            (select top 1 AllowShiftForceCancel from Settings_ShiftCreation where CompanyId=l.CompanyId) AllowShiftForceCancel,
            (select IsExpired from WorkerJob wj where wj.WorkerId=ws.WorkerId and wj.JobId=s.JobId)  as IsWorkerJobExpired,
            (select case when IsExpiredBy>0 then cast(1 as bit) else cast(0 as bit) end from WorkerJob wj where wj.WorkerId=ws.WorkerId and wj.JobId=s.JobId)  as IsWorkerJobExpiredBySupervisor,
            j.MinWorkHrsDays JobMinWorkHrsDays,
            cast(0 as bit) IsShiftSwap,
            cast(0 as bit) AllowShiftSwap,
            0 as ShiftSwapId
	FROM [WorkerShift] ws
		INNER JOIN Worker w ON ws.WorkerId = w.Id
		INNER JOIN CompanyWorker cw ON cw.WorkerId = w.Id
		INNER JOIN [Shift] s ON (s.Id = ws.ShiftId and s.JobId in (select JobId from WorkerJob where WorkerId=@workerid)) -- added this to include only those shifts which are associated with worker job
		INNER JOIN Job j ON s.JobId = j.Id INNER JOIN WorkCenter wc ON s.WorkCenterId = wc.Id
        INNER JOIN [Location] l ON wc.LocationId = l.Id
	WHERE ws.WorkerId = @workerid
		AND s.Id NOT IN (SELECT ShiftId FROM WorkerShift WHERE WorkerId = @workerid AND [Status] = 'A') --ignore accepted shifts	
		AND CAST(StartDateTime AS DATETIME) >=  GETDATE() 
		AND cw.IsActive = 1
		AND s.Id  IN (SELECT ShiftId FROM cteShiftsNotFilled WHERE SlotsFilled < NumWorkersNeeded) -- use shifts which are not filled
		AND cw.IsActive = 1
		

UNION

    -- get shift swap request 
    select 
    s.Id, '' AS [Status],
			[StartDateTime],
			[EndDateTime],			
			j.[Id] AS JobId,
			j.[Name] AS JobName, 
			j.HexColor, [WorkCenterId], 
			wc.[Name] AS WorkcenterName, 
			s.[ShiftPremium], 
            dbo.udfIsNewShift(s.Id) IsNew,
            l.time_zone as LocationTimeZoneName,
            (select dbo.udfUtcOffsetToMin(tz.current_utc_offset) from sys.time_zone_info tz 
                where tz.name=l.time_zone) CurrUtcOffsetInMin,
            l.Name as  LocationName,
            l.BuildingNumber,
            l.Address1,
            l.City,
            l.[State],
            l.Zip,
            l.County,
            l.Country,
            (select Name from Company where Id=l.companyId) as  CompanyName,
            l.IsActive as LocationIsActive,
            (select top 1 AllowShiftForceCancel from Settings_ShiftCreation where CompanyId=l.CompanyId) AllowShiftForceCancel,
            wj.IsExpired as IsWorkerJobExpired,
			(case when wj.IsExpiredBy>0 then cast(1 as bit) else cast(0 as bit) end) IsWorkerJobExpiredBySupervisor,
            j.MinWorkHrsDays JobMinWorkHrsDays,
            cast(1 as bit) IsShiftSwap,
            cast(0 as bit) AllowShiftSwap,
            sw.id as ShiftSwapId
    from ShiftSwap sw
    INNER join Shift s on sw.ShiftId=s.Id
    INNER join job j on s.JobId = j.Id
    INNER join WorkCenter wc on s.WorkCenterId=wc.Id
    inner join [Location] l on wc.LocationId=l.Id
    inner join WorkerJob wj on wj.WorkerId=@workerid and wj.JobId=s.JobId
    where
    (sw.ApprovalStatus='a' or lower(sw.ApprovalStatus)='p')
    and sw.IsActive=1 and sw.AcceptedByWorkerId is NULL
    and s.JobId in (select JobId from WorkerJob where WorkerId=@workerid and IsExpired=0)
    and s.Id not in (select shiftid from workershift where WorkerId=@WorkerId and STATUS<>'C')
    AND CAST(StartDateTime AS DATETIME) >=  GETDATE() 

END;

CREATE PROCEDURE [dbo].[GetUserById]
	-- Add the parameters for the stored procedure here
	@userId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- table[0]: fetch user details
    SELECT u.Id,lr.[Role] as [Role],c.[Name] as [Company],c.[Id] as [CompanyId],FirstName,LastName,Email,PhoneNumber,u.IsActive 
		FROM [User] u INNER JOIN LuRole lr ON u.RoleId = lr.Id INNER JOIN Company c ON u.CompanyId = c.Id
			WHERE u.Id = @userId

	-- table[1]: fetch user properties from Settings table
    DECLARE @Entity NVARCHAR(50) = 'company'
    DECLARE @EntityId INT = (select u.CompanyId from [User] u where Id=@userId)

    ;WITH cte as
    (
        SELECT 
            Id, DN,[Name], DataType, [Value] DefaultValue
        from 
            Settings
        where 
            IsActive = 1        
            and dn like @Entity+'.'+'%'
            and EntityId=0
    )
    SELECT 
        cte.*, 
        ISNULL((select top 1
                        [Value]
                    from settings s
                    where s.dn=cte.dn and s.entityid=@EntityId
                    order by LastUpdatedDate desc),cte.DefaultValue) Value
    from cte

    -- table[2]: fetch company shift settings
    SELECT * from Settings_ShiftCreation where companyId = (select u.CompanyId from [User] u where Id=@userId)

    -- table[3]: fetch user roles
    ;with cte as 
    (
        SELECT RoleId from [User] where Id=@userId
        union ALL
        SELECT RoleId from UserRole where UserId=@userId and IsActive=1
    )
    SELECT cte.RoleId,lr.Role RoleName from cte
    inner JOIN LuRole lr on cte.RoleId=lr.Id
    

END;

CREATE PROCEDURE [dbo].[GetUserByPassword]
	-- Add the parameters for the stored procedure here
	@username nvarchar(150),
	@password nvarchar(100)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @Id INT;
	SET @Id = (SELECT Id FROM [Login] WHERE [UserName] = @username);
   
	SELECT u.Id,l.[Password], l.ChangePasswordOnLogin, l.FailedAttemptedLogins, lr.[Role] as [Role],
	c.[Name] as [Company],FirstName,LastName,Email,PhoneNumber,u.IsActive 
		FROM [User] u INNER JOIN [Login] l ON u.LoginId = l.Id
		INNER JOIN LuRole lr ON u.RoleId = lr.Id 
		INNER JOIN Company c ON u.CompanyId = c.Id
			WHERE u.Id = @Id

END;

CREATE PROCEDURE [dbo].[GetUserByUserName]	
	@email VARCHAR(255),
	@refreshToken NVARCHAR(100)
AS
BEGIN
	
	UPDATE [Login] SET RefreshToken = @refreshToken, [PasswordResetExpiration] = GETDATE() + 2
			WHERE UserName = @email	

	IF @@ROWCOUNT <> 0 
		SELECT l.Id AS LoginId, u.FirstName, u.LastName, RefreshToken AS [Key] FROM [Login] l INNER JOIN [User] u on l.Id = u.LoginId WHERE UserName = @email
END;

CREATE PROCEDURE [dbo].[GetWorkcenterByUserId]
	-- Add the parameters for the stored procedure here
	@userid int
AS
BEGIN

	SELECT 
        wc.Id,wc.[Name], wc.CostCenter, wc.LocationId, wc.CreatedBy, wc.CreationDate, wc.LastUpdatedBy, wc.LastUpdatedDate,
        (select string_agg(jwc.JobId,',') from JobWorkcenter jwc where jwc.WorkcenterId=wc.Id) as JobIds
	FROM 
        WorkCenter wc
    WHERE
        Id in (select items from dbo.Split(dbo.udfUserWorkCenters(@userid),','))
			
END;

CREATE     PROCEDURE [dbo].[GetWorkerAvailability]
    @WorkerId VARCHAR(255),
    @FromDate DATETIMEOFFSET,
    @ToDate DATETIMEOFFSET
AS
BEGIN

    SELECT * from workerAvailability WHERE
    WorkerId=@WorkerId
    and StartDateTime >= @FromDate
    and StartDateTime <= DATEADD(DAY,1,@ToDate)
    order by StartDateTime

END;

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

    DECLARE @CompanyId INT 

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

    -- get active workers with @JobIds
    SELECT 
        w.id,w.FirstName,w.LastName, w.MobilePhoneNumber,
            (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 and cw.CompanyId = @CompanyId
    WHERE
        w.IsActive = 1
        AND cw.IsActive = 1
        AND w.Id in (select wj.WorkerId from WorkerJob wj  where wj.JobId in (select items from dbo.Split(@JobIds,',')))

END;

CREATE   procedure [dbo].[GetWorkerByMobile]
	@MobileNumber varchar(20)
AS
Begin

	SELECT w.Id, w.FirstName, w.MiddleName, w.LastName, w.Email, w.MobilePhoneNumber, lt.[Type], c.[Name] AS CompanyName, dbo.udfWorkerJobs(w.Id,cw.CompanyId) AS Jobs 
		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.MobilePhoneNumber = @MobileNumber

End;

CREATE PROCEDURE [dbo].[GetWorkerGUIDbyPhone]
	-- Add the parameters for the stored procedure here
	@workerPhoneNumber varchar(50)
AS
BEGIN

	SELECT w.Id   
		FROM Worker w 
			WHERE 
				w.MobilePhoneNumber = @workerPhoneNumber
END;

CREATE     PROCEDURE [dbo].[GetWorkerNotifications]
    @WorkerId UNIQUEIDENTIFIER,
    @psize INT,
    @type varchar(10),
    @nid BIGINT
AS
BEGIN

    IF lower(@type) = 'new'
    BEGIN

        SELECT 
            wn.* 
        FROM 
            WorkerNotification wn
        where 
        WorkerId=@WorkerId
        and (@nid = 0 or Id > @nid)
        ORDER by wn.Id DESC

    END
    ELSE
    BEGIN

        SELECT 
        top (@psize) wn.* 
        FROM 
            WorkerNotification wn
        where 
        WorkerId=@WorkerId
        and (@nid = 0  or Id < @nid)
        ORDER by wn.Id DESC

    END

END;

CREATE   PROCEDURE [dbo].[GetWorkerPassCode]
 @WorkerId UNIQUEIDENTIFIER,
 @PassCode VARCHAR(10)
AS
BEGIN

    select 
        Id, PassCode
    from 
        Worker
    where 
        (Id=@WorkerId and PassCode=@PassCode)
        OR
        (@WorkerId='fe9565e3-4ac6-4f69-af06-786a449a8244' 
            and Id=@WorkerId 
            and @Passcode='9735')


END;

CREATE     PROCEDURE [dbo].[GetWorkerProfile]
    @workerid VARCHAR(MAX)
AS
BEGIN

    -- table[0] fetch worker details with reliability score and shift acceptance rate
    SELECT  w.Id, FirstName, MiddleName, LastName, Email, [MobilePhoneNumber], 
        w.IsActive, w.rscore,cast(w.rscorelastupdated as datetimeoffset) rscorelastupdated, w.SAR, (select top 1
                                            w.RScore/MaxScore*100
                                        from Settings_WorkerScore
                                        order by LastUpdatedDate desc) RScorePercent
		FROM [dbo].[Worker] w
    WHERE
        w.Id = @workerid
        AND w.IsActive = 1

    -- table[1] fetch worker roles with color codes
    select 
        j.Id, j.Name, j.HexColor, wj.IsExpired,
		(case when wj.IsExpiredBy>0 then cast(1 as bit) else cast(0 as bit) end) IsWorkerJobExpiredBySupervisor,
        j.MinWorkHrsDays JobMinWorkHrsDays
    from 
        WorkerJob wj
    inner join job j on wj.JobId=j.Id
    where 
        WorkerId = @workerid
        and j.IsActive=1

    DECLARE @ScorePeriodInDays INT = 30
    DECLARE @CurrDate DateTimeOffset = GETDATE()    

    -- table[2] fetch all cancelled and no show shifts for last @scoreperioddays
    set @CurrDate=(select w.RScoreLastUpdated from worker w where Id=@workerid)
    SELECT 
        wsi.WorkerShiftId as id,
        wsi.WorkerShiftStatus as [Status],    
        wsi.WorkerShiftIsAbsent as IsAbsent,
        wsi.WorkerShiftIsTardy as IsTardy,
        wsi.InvalidShiftCancelPenalty as p,
        wsi.ShiftStart as StartDateTime,
        wsi.ShiftEnd as EndDateTime,
        wsi.ShiftJobId as JobId,
        wsi.ShiftJobName as JobName,
        wsi.WorkerShiftLastUpdated as LastUpdatedDate,
        ws.comment as Comments
    from 
        WorkerScoreItem wsi 
    inner JOIN workershift ws on wsi.workershiftid = ws.id
    where wsi.WorkerId=@WorkerId

    -- table[3] fetch worker experience level details
    SELECT 
        el.Id as ExpLevelId,
        el.Title as ExpLevelTitle,
        (SELECT TotalCompletedHours from Worker where Id=@workerid) as CompletedHours
    from 
        ExpLevel el
    where el.Id = (SELECT ExpLevelId from Worker where Id=@workerid)    

    -- table[4] fetch worker shifts from past with status and attendance
    SELECT 
    s.Id,s.StartDateTime,s.EndDateTime,ws.[Status],ws.ATTEND_STATUS,ws.comment
    from 
        WorkerShift  ws
    INNER JOIN shift s on ws.shiftid=s.id
    where 
    s.IsActive = 1
    and ws.workerid=@workerid
    and s.EndDateTime < GETDATE()
    order by s.StartDateTime DESC

    -- table[5] fetch sar related week records
    SELECT 
        WkStartDateTime sdt,
        WkEndDateTime edt, 
        WkSeq wk,
        CompletedHrs hrs
    from WorkerSARWkHrs where WorkerId=@workerid

    -- table[6] fetch worker tier
    SELECT * from WorkerTier where Seq=(Select TierSeq from Worker where Id=@workerid)

    -- table[7] fetch all worker jobskills
   SELECT 
        wsk.id, wsk.workerid,
        js.JobId, j.Name JobName, wsk.skillid, s.Name SkillName,
        wsk.SkillLevelId, sl.Seq Level, sl.Name LevelName
    from workerskill wsk
        INNER JOIN JobSkill js on wsk.skillid=js.SkillId and js.IsActive=1
        INNER JOIN Job j on js.JobId=j.Id and j.IsActive=1
        inner JOIN Skill s on wsk.skillid=s.Id and s.IsActive=1
        INNER JOIN SkillLevel sl on wsk.SkillLevelId=sl.Id
    where 
        wsk.workerid=@workerid

    -- table[8] fetch all worker interested skills
    SELECT
        wski.id, wski.workerid,
        wski.skillid, s.Name SkillName,
        wski.SkillLevelId, sl.Seq Level, sl.Name LevelName,
        (select STRING_AGG(js.JobId,',')
            from JobSkill js inner join job j on js.JobId=j.id and j.IsActive=1
            where js.SkillId=wski.SkillId and js.IsActive=1) JobIds
    from WorkerSkillInterest wski
        inner JOIN Skill s on wski.skillid=s.Id and s.IsActive=1
        INNER JOIN SkillLevel sl on wski.SkillLevelId=sl.Id
    where 
        wski.workerid=@workerid
        and wski.[Status]='N'

    -- table[9] fetch worker other skills
     ;WITH cte as (
        SELECT ws.SkillId 
        from WorkerSkill ws 
        INNER JOIN skill s on ws.SkillId=s.Id and s.IsActive=1 --and s.CompanyId=@UserCompanyId
        where 
        ws.WorkerId=@workerid

        EXCEPT

        SELECT 
        --wj.JobId,
        js.SkillId 
        from WorkerJob wj
        INNER JOIN Job j on wj.JobId=j.Id and j.IsActive=1 --and j.CompanyId=@UserCompanyId
        INNER join JobSkill js on wj.JobId=js.JobId and js.IsActive=1
        where
        wj.WorkerId=@workerid
        --and wj.JobId in (select Id from @UserJobs)    
    )
    SELECT cte.SkillId,s.Name SkillName, ws.SkillLevelId LevelId,sl.Seq [Level],sl.Name LevelName from cte 
    inner join Skill s on cte.SkillId=s.Id
    inner join WorkerSkill ws on cte.SkillId=ws.SkillId and ws.WorkerId=@workerid
    INNER join SkillLevel sl on ws.SkillLevelId=sl.Id

END;

CREATE   PROCEDURE [dbo].[GetWorkerProfileForUser]
    @workerid   VARCHAR(MAX),
    @userid     INT
AS
BEGIN

    DECLARE @CurrDate DateTimeOffset = GETDATE()
    DECLARE @UserCompanyId INT = (select CompanyId from [User] where Id=@userid)

    -- check if user is allowed to view worker profile
    -- select all user jobids
    DECLARE @UserJobs TABLE(Id INT)
    INSERT into @UserJobs
    select
        jwc.JobId
    from
        JobWorkcenter jwc
    where 
        jwc.WorkcenterId in (select items
                                from dbo.Split(dbo.udfUserWorkCenters(@userid),',')) 
    
    -- table[0] fetch worker details with reliability score
    SELECT  w.Id, FirstName, MiddleName, LastName, Email, [MobilePhoneNumber], 
        w.IsActive, w.rscore, cast(w.rscorelastupdated as datetimeoffset) rscorelastupdated, w.SAR , (select top 1
                                            w.RScore/MaxScore*100
                                        from Settings_WorkerScore
                                        order by LastUpdatedDate desc) RScorePercent
		FROM [dbo].[Worker] w
    WHERE
        w.Id = @workerid
        AND w.IsActive = 1

    -- table[1] fetch worker roles with color codes
    select 
        j.Id, j.Name, j.HexColor, wj.IsExpired,
		(case when wj.IsExpiredBy>0 then cast(1 as bit) else cast(0 as bit) end) IsWorkerJobExpiredBySupervisor,
        j.MinWorkHrsDays JobMinWorkHrsDays
    from 
        WorkerJob wj
    inner join job j on wj.JobId=j.Id and j.CompanyId=@UserCompanyId
    where 
        WorkerId = @workerid
        and j.IsActive=1

    -- table[2] fetch all cancelled and no show shifts for last @scoreperioddays        
    set @CurrDate=(select w.RScoreLastUpdated from worker w where Id=@workerid)
    SELECT 
        WorkerShiftId as id,
        WorkerShiftStatus as [Status],    
        WorkerShiftIsAbsent as IsAbsent,
        WorkerShiftIsTardy as IsTardy,
        InvalidShiftCancelPenalty as p,
        ShiftStart as StartDateTime,
        ShiftEnd as EndDateTime,
        ShiftJobId as JobId,
        ShiftJobName as JobName,
        WorkerShiftLastUpdated as LastUpdatedDate,
        ws.comment as Comments
    from 
        WorkerScoreItem wsi 
    inner JOIN workershift ws on wsi.workershiftid = ws.id
    where wsi.WorkerId=@WorkerId    

    -- table[3] fetch worker experience level details            
    set @CurrDate=(select w.ExpLevelIdLastUpdated from worker w where Id=@workerid)
    SELECT 
        ExpLevelId,ExpLevelTitle,CompletedHours  
    from 
        dbo.udfGetWorkerExpLevel(@workerid,@CurrDate)

    -- table[4] fetch worker shifts from past with status and attendance
    SELECT 
        s.Id,s.StartDateTime,s.EndDateTime,ws.[Status],ws.ATTEND_STATUS,ws.comment
    from 
        WorkerShift  ws
    INNER JOIN shift s on ws.shiftid=s.id
    where 
        s.IsActive = 1
        and ws.workerid=@workerid
        and s.EndDateTime < GETDATE()
    order by s.StartDateTime DESC

    -- table[5] fetch sar related week records
    SELECT 
        WkStartDateTime sdt,
        WkEndDateTime edt, 
        WkSeq wk,
        CompletedHrs hrs
    from WorkerSARWkHrs where WorkerId=@workerid

    -- table[6] fetch worker tier
    SELECT * from WorkerTier where Seq=(Select TierSeq from Worker where Id=@workerid)

    -- table[7] fetch all worker jobskills
   SELECT 
        wsk.id, wsk.workerid,
        js.JobId, j.Name JobName, wsk.skillid, s.Name SkillName,
        wsk.SkillLevelId, sl.Seq Level, sl.Name LevelName
    from workerskill wsk
        INNER JOIN JobSkill js on wsk.skillid=js.SkillId and js.IsActive=1
        INNER JOIN Job j on js.JobId=j.Id and j.IsActive=1
        inner JOIN Skill s on wsk.skillid=s.Id and s.IsActive=1
        INNER JOIN SkillLevel sl on wsk.SkillLevelId=sl.Id
    where 
        wsk.workerid=@workerid
        and j.CompanyId=@UserCompanyId
        and s.CompanyId=@UserCompanyId

    -- table[8] fetch all worker interested skills
    SELECT
        wski.id, wski.workerid,
        wski.skillid, s.Name SkillName,
        wski.SkillLevelId, sl.Seq Level, sl.Name LevelName,
        (select STRING_AGG(js.JobId,',')
            from JobSkill js inner join job j on js.JobId=j.id and j.IsActive=1 and j.CompanyId=@UserCompanyId
            where js.SkillId=wski.SkillId and js.IsActive=1) JobIds
    from WorkerSkillInterest wski
        inner JOIN Skill s on wski.skillid=s.Id and s.IsActive=1 and s.CompanyId=@UserCompanyId
        INNER JOIN SkillLevel sl on wski.SkillLevelId=sl.Id
    where 
        wski.workerid=@workerid
        and wski.[Status]='N'

    -- table[9] fetch worker other skills
     ;WITH cte as (
        SELECT ws.SkillId 
        from WorkerSkill ws 
        INNER JOIN skill s on ws.SkillId=s.Id and s.IsActive=1 and s.CompanyId=@UserCompanyId
        where 
        ws.WorkerId=@workerid

        EXCEPT

        SELECT 
        --wj.JobId,
        js.SkillId 
        from WorkerJob wj
        INNER JOIN Job j on wj.JobId=j.Id and j.IsActive=1 and j.CompanyId=@UserCompanyId
        INNER join JobSkill js on wj.JobId=js.JobId and js.IsActive=1
        where
        wj.WorkerId=@workerid
        and wj.JobId in (select Id from @UserJobs)    
    )
    SELECT cte.SkillId,s.Name SkillName, ws.SkillLevelId LevelId,sl.Seq [Level],sl.Name LevelName from cte 
    inner join Skill s on cte.SkillId=s.Id
    inner join WorkerSkill ws on cte.SkillId=ws.SkillId and ws.WorkerId=@workerid
    INNER join SkillLevel sl on ws.SkillLevelId=sl.Id

END;

CREATE PROCEDURE [dbo].[GetWorkersById]
	@workerids VARCHAR(MAX),
	@shiftid int,
	@onlyActive bit
AS
BEGIN	
	
	DECLARE @workerId TABLE (Id UNIQUEIDENTIFIER)
	DECLARE @companyId INT

	-- spllit comma separated workerids
	INSERT INTO @workerId  SELECT * FROM dbo.Split(@workerids,',')	

	-- get companyid from shiftid to use in following query
	set @companyId = (select l.CompanyId from Shift s
						--inner join Job j on s.JobId=j.Id
				inner join WorkCenter wc on s.WorkCenterId=wc.Id
				inner join location l on wc.LocationId=l.id
						where s.Id=@shiftid)

	/*SELECT  w.Id, c.[Name] as Company, FirstName, MiddleName, LastName, Email, [MobilePhoneNumber], w.IsActive    
		FROM [dbo].[Worker] w INNER JOIN [dbo].[CompanyWorker] cw ON cw.WorkerId = w.Id
		INNER JOIN dbo.Company c ON cw.CompanyId = c.Id
			WHERE w.Id IN (SELECT Id FROM @workerId)
				AND (@activeFlag=0 or cw.IsActive = 1)*/

	-- get workers for company based on activeflag
	SELECT  w.Id, c.[Name] as Company, FirstName, MiddleName, LastName, Email, [MobilePhoneNumber], w.IsActive,
            (select STRING_AGG(wdt.devicetoken,',')
            from WorkerDeviceToken wdt
            where wdt.WorkerId=w.Id and wdt.IsActive=1) DeviceTokens        
		FROM [dbo].[Worker] w
		INNER JOIN [dbo].[CompanyWorker] cw ON cw.WorkerId = w.Id
		INNER JOIN dbo.Company c ON cw.CompanyId = c.Id  and c.Id=@companyId
		--inner join WorkerShift ws on w.Id=ws.WorkerId and ws.ShiftId=@shiftid
		WHERE w.Id IN (SELECT Id FROM @workerId)
		AND (@onlyActive=0 or cw.IsActive = 1)

END;

CREATE   PROCEDURE [dbo].[GetWorkerSettings]
    @WorkerId UNIQUEIDENTIFIER,
    @TypeCode NVARCHAR(100) = NULL
AS
BEGIN

    SELECT
        wst.Id, wst.Name, wst.ValueType,wst.Code,
        dbo.udfGetWorkerSetting(w.id,wst.id) Value
    from
        Worker w, workersettingtype wst
    WHERE
        wst.IsActive=1
        AND w.Id=@WorkerId
        AND (@TypeCode is null or lower(wst.Code)=lower(@TypeCode))

END;

CREATE   PROCEDURE [dbo].[GetWorkersForNextDayShift]	
AS
BEGIN

    DECLARE @LT TABLE(Id INT)

    -- get all active location with curr time as time for daily sms reminder
    INSERT INTO @LT
    SELECT l.Id
    FROM [Location] l
        INNER JOIN Settings_ShiftCreation ss on ss.CompanyId = l.CompanyId
    WHERE 
        FORMAT(GETDATE() at time zone 'UTC' at time zone l.TIME_ZONE,'HH:mm') 
            BETWEEN DATEADD(MINUTE,-1,DailySMSReminderTime) and DATEADD(MINUTE,1,DailySMSReminderTime) -- keep a margin of one minute
        --DailySMSReminderTime = FORMAT(GETDATE() at time zone 'UTC' at time zone l.TIME_ZONE,'HH:mm')
        AND l.IsActive = 1
        and ss.EnableDailySMSReminderToWorker = 1

    -- get all active shifts with a day margin on both sides to limit records
    DECLARE @DateOfReport DATETIME = DATEADD(DAY,1, cast(getdate() as Date))
    ;with s as (
    SELECT s.* from Shift s    
    where 
    s.StartDateTime >= DATEADD(day, -2, @DateOfReport)
    and s.StartDateTime < DATEADD(day, 2, @DateOfReport)    
    and s.IsActive = 1
    )
    -- query above filtered records for the accepted worker shifts
    select 
        --s.* 
        w.FirstName, 
        w.LastName, 
        w.MobilePhoneNumber, 
        s.StartDateTime at time zone l.TIME_ZONE LocalDateTime,
        (Select name from Company where Id=j.CompanyId) CompanyName,
        j.Name JobName,
        l.TIME_ZONE,
        (@DateOfReport at time ZONE l.TIME_ZONE at time zone 'UTC') UTCDateTime3
    from s
        INNER JOIN WorkerShift ws on s.Id=ws.ShiftId
        INNER JOIN WorkerSettings wss on wss.WorkerId=ws.WorkerId
        INNER JOIN WorkCenter wc on s.WorkCenterId = wc.Id
        INNER JOIN Worker w on ws.WorkerId = w.Id
        INNER JOIN Job j on s.JobId = j.Id
        INNER JOIN CompanyWorker cw on ws.WorkerId = cw.WorkerId and cw.CompanyId = j.CompanyId and cw.IsActive = 1         
        INNER JOIN [Location] l on wc.LocationId = l.Id
    WHERE
        s.StartDateTime >= (@DateOfReport at time ZONE l.TIME_ZONE at time zone 'UTC')
        and s.StartDateTime < DATEADD(day, 1, (@DateOfReport at time ZONE l.TIME_ZONE at time zone 'UTC'))
        AND ws.Status  = 'A'
        and s.IsActive = 1        
        and lower(dbo.udfGetWorkerSetting(w.Id,(select Id from WorkerSettingType where Code='DailyReminderSMS')))='true'
        and l.id in (select Id from @LT)    

END;

-- return if there are worker accepted shifts for the worker jobs missing from @NewJobIdsCsv
CREATE   PROCEDURE [dbo].[GetWorkerShiftForRemovedJobs]
    -- params
    @NewJobIdsCsv VARCHAR(MAX),
    @WorkerId VARCHAR(100),
    @UserId INT
AS
BEGIN
    -- Get new jobs from update request
    DECLARE @NewJobIds TABLE (JobId INT)
    INSERT INTO @NewJobIds  
    SELECT * FROM dbo.Split(@NewJobIdsCsv,',')

    -- Get worker current jobs which are in jobs related to the logged in user
    DECLARE @OldJobIds TABLE (JobId INT)
    INSERT into @OldJobIds
            SELECT JobId 
            FROM 
                WorkerJob 
            WHERE WorkerId = @WorkerId 
                and JobId in (select 
                                    jw.jobid 
                                from 
                                    JobWorkCenter jw where 
                                    jw.WorkcenterId in
                                    (	
                                    SELECT 
                                        items 
                                    FROM 
                                        dbo.Split(dbo.udfUserWorkCenters(@UserId),',')
                                    )
                                )

    -- get jobids removed from worker 
    DECLARE @RemovedJobIds TABLE(JobId INT)
    INSERT INTO @RemovedJobIds
    SELECT JobId from @OldJobIds
    EXCEPT
    SELECT JobId from @NewJobIds

    -- get future worker shift for above jobids
    SELECT 
        s.Id
        , ws.[Status]
        , [StartDateTime]
        , [EndDateTime]		 
        , [JobId]
        , j.[Name] AS JobName
        , j.HexColor
        , [WorkCenterId]
        , wc.[Name] AS WorkcenterName
        , s.[ShiftPremium],
        dbo.udfIsNewShift(s.Id) IsNew  
    FROM [Shift] s 
    INNER JOIN Job j ON s.JobId = j.Id 
    INNER JOIN WorkCenter wc ON s.WorkCenterId = wc.Id
    INNER JOIN WorkerShift ws ON s.Id = ws.ShiftId				
    WHERE 
        s.Id IN (SELECT ShiftId FROM dbo.WorkerShift WHERE WorkerId =  @workerid)	
        AND s.JobId in (SELECT JobId from @RemovedJobIds)
        AND ws.[Status] = 'A' 
        AND s.IsPublished = 1 AND s.IsActive = 1 
        AND s.StartDateTime > GETDATE()
        AND j.IsActive = 1
        AND  ws.WorkerId = @workerid 
END;

CREATE   PROCEDURE [dbo].[GetWorkerShiftSwaps]
    @WorkerId UNIQUEIDENTIFIER,
    @ShiftIds VARCHAR(MAX)
AS
BEGIN

    SELECT * from ShiftSwap sp
    WHERE
    sp.WorkerId=@WorkerId
    and sp.IsActive=1
    and sp.ShiftId in (select items from dbo.Split(@ShiftIds,','))
    order by sp.RequestDate DESC
    

END;

CREATE PROCEDURE [dbo].[GetWorkerToken]
    @workerid VARCHAR(100)
AS
BEGIN
    SELECT Id,PassCode,RefreshToken from Worker where Id=@workerid
END;

CREATE   PROCEDURE [dbo].[LogPushNotification]
    @Provider VARCHAR(50),
    @Type VARCHAR(50),
    @Request VARCHAR(MAX),
    @Response VARCHAR(MAX),
    @WorkerId VARCHAR(255)
AS
BEGIN

    INSERT INTO PushNotificationLog
    VALUES(@Provider,@Type,@Request,@Response,GETDATE(),@WorkerId)

END;

CREATE PROCEDURE [dbo].[NotifyWorkers]
    @tvpWorkerShiftNotification ListWorkerShiftNotification READONLY
AS
BEGIN
	INSERT INTO [dbo].[ShiftNotification]
           ([WorkerId]
           ,[ShiftId]
           ,[Status]           
		   ,[ShiftPremium]
           ,[CreatedBy]
           ,[CreationDate]
           ,[LastUpdatedBy]
           ,[LastUpdatedDate])
	SELECT 
			wsn.WorkerId,
			wsn.ShiftId,
			wsn.[Status],
			(SELECT ShiftPremium FROM [Shift] WHERE Id = wsn.ShiftId),
			wsn.CreatedBy,
			GETDATE(),
			wsn.CreatedBy,
			GETDATE()			
		FROM @tvpWorkerShiftNotification AS wsn;  
END;

CREATE PROCEDURE [dbo].[PublishShiftsById]
	@shiftIds VARCHAR(MAX),
	@userCompanyId INT
AS
BEGIN	
	
	DECLARE @ShiftId TABLE (Id INT)

	INSERT INTO @ShiftId  SELECT * FROM dbo.Split(@shiftIds,',')

	UPDATE [Shift] SET IsPublished = 1, LastUpdatedDate = GETDATE() WHERE Id IN (SELECT Id FROM @ShiftId)

    -- get worker details who are to be notified
	SELECT w.Id, c.[Name] as Company, FirstName, MiddleName, LastName, Email, [MobilePhoneNumber], w.IsActive,
        (select STRING_AGG(wdt.devicetoken,',')
        from WorkerDeviceToken wdt
        where wdt.WorkerId=w.Id and wdt.IsActive=1) DeviceTokens    
		FROM [dbo].[Worker] w INNER JOIN [dbo].[WorkerJob] wj ON wj.WorkerId = w.Id
		INNER JOIN [dbo].[CompanyWorker] cw ON cw.WorkerId = w.Id
		INNER JOIN dbo.Company c ON cw.CompanyId = c.Id and cw.CompanyId=@userCompanyId
			WHERE wj.JobId IN (
				SELECT JobId FROM [Shift] WHERE Id IN (SELECT Id FROM @ShiftId))
				AND cw.IsActive = 1


    -- get worker shifts to log into workershifts
    SELECT 
    	wj.WorkerId,s.id as ShiftId
    FROM 
        WorkerJob wj 
    inner join CompanyWorker cw on wj.WorkerId=cw.WorkerId
    inner join shift s on wj.JobId = s.JobId and s.id in (SELECT Id FROM @ShiftId)
    WHERE 
        wj.JobId in (SELECT JobId FROM [Shift] WHERE Id IN (SELECT Id FROM @ShiftId)) 
        and cw.CompanyId=@userCompanyId and cw.IsActive=1    

END;

CREATE   PROCEDURE [dbo].[RefreshDataForDemo]
    @UserId INT
AS
BEGIN
	

	DECLARE @RoleCount INT = 0
	Set @RoleCount = (
		SELECT COUNT(*) FROM dbo.[UserRole] ur		
		Inner Join LuRole r on ur.RoleId = r.Id
		WHERE ur.UserId = @UserId AND r.Role = 'DemoResetRole'
	)
	
	-- check if user has authorized role i.e. DemoResetRole
	IF @RoleCount = 0
	BEGIN
		THROW 50000, 'GATError.NotAuthorized', 1;		
		RETURN;
	END


	DECLARE @CompanyId INT
	SET @CompanyId = (SELECT CompanyId FROM dbo.[User] where Id = @UserId)
	DECLARE @CompanyName VARCHAR(100) = (select Name from company where id = @CompanyId) --do not allow these companies - 4-5 
	-- check if procedure is allowed for company and environment
	IF (@CompanyName <> 'Acme Manufacturing') OR @@SERVERNAME NOT IN ('gatdbstag', 'gatdb','MSI\SQLEXPRESS') OR @@SERVERNAME IN ('gigandtakeprod')
	BEGIN		
		THROW 50000, 'GATError.SP.DemoReset.NotAllowed', 1;
		RETURN;
	END

	IF (@CompanyName = 'Acme Manufacturing') AND @@SERVERNAME IN ('gatdbstag', 'gatdb','MSI\SQLEXPRESS') AND @@SERVERNAME NOT IN ('gigandtakeprod')
	BEGIN

		BEGIN TRANSACTION;
		BEGIN TRY
			-- Delete rows from JobSkill table
			DELETE FROM dbo.JobSkill WHERE JobId IN (SELECT Id FROM dbo.Job WHERE CompanyId = @CompanyId);
			DELETE FROM dbo.JobSkill WHERE SkillId IN (SELECT Id FROM dbo.Skill WHERE CompanyId = @CompanyId);

			-- Delete rows from WorkerSkill table
			DELETE FROM dbo.WorkerSkill WHERE SkillId IN (SELECT Id FROM dbo.Skill WHERE CompanyId = @CompanyId);

			-- Delete rows from WorkerSkillInterest table
			DELETE FROM dbo.WorkerSkillInterest WHERE SkillId IN (SELECT Id FROM dbo.Skill WHERE CompanyId = @CompanyId);
			DELETE FROM dbo.WorkerSkillInterest WHERE WorkerId IN (SELECT WorkerId FROM dbo.CompanyWorker WHERE CompanyId = @CompanyId);

			-- Delete rows from JobExpLevel table
			DELETE FROM dbo.JobExpLevel WHERE JobId IN (SELECT Id FROM dbo.Job WHERE CompanyId = @CompanyId);

			-- Delete rows from JobWorkcenter table
			DELETE FROM dbo.JobWorkcenter WHERE JobId IN (SELECT Id FROM dbo.Job WHERE CompanyId = @CompanyId);

			-- Delete rows from WorkerShiftStatusLog table
			DELETE FROM dbo.WorkerShiftStatusLog WHERE WorkerShiftId IN (SELECT Id FROM dbo.WorkerShift WHERE WorkerId IN (SELECT WorkerId FROM dbo.CompanyWorker WHERE CompanyId = @CompanyId));

			-- Delete rows from ShiftNotification table
			DELETE FROM dbo.ShiftNotification WHERE WorkerId IN (SELECT WorkerId FROM dbo.CompanyWorker WHERE CompanyId = @CompanyId);

			-- Delete rows from WorkerScoreHistory table
			DELETE FROM dbo.WorkerScoreHistory WHERE WorkerId IN (SELECT WorkerId FROM dbo.CompanyWorker WHERE CompanyId = @CompanyId);

			-- Delete rows from WorkerDeviceToken table
			DELETE FROM dbo.WorkerDeviceToken WHERE WorkerId IN (SELECT WorkerId FROM dbo.CompanyWorker WHERE CompanyId = @CompanyId);

			-- Delete rows from WorkerSARWkHrs table
			DELETE FROM dbo.WorkerSARWkHrs WHERE WorkerId IN (SELECT WorkerId FROM dbo.CompanyWorker WHERE CompanyId = @CompanyId);

			-- Delete rows from WorkerNotification table
			DELETE FROM dbo.WorkerNotification WHERE WorkerId IN (SELECT WorkerId FROM dbo.CompanyWorker WHERE CompanyId = @CompanyId);

			-- Delete rows from PushNotificationLog table
			DELETE FROM dbo.PushNotificationLog WHERE WorkerId IN (SELECT WorkerId FROM dbo.CompanyWorker WHERE CompanyId = @CompanyId);

			-- Delete rows from WorkerJob table
			DELETE FROM dbo.WorkerJob WHERE WorkerId IN (SELECT WorkerId FROM dbo.CompanyWorker WHERE CompanyId = @CompanyId);

			-- Delete rows from WorkerShift table
			DELETE FROM dbo.WorkerShift WHERE WorkerId IN (SELECT WorkerId FROM dbo.CompanyWorker WHERE CompanyId = @CompanyId);

			-- Delete rows from WorkerAvailability table
			DELETE FROM dbo.WorkerAvailability WHERE WorkerId IN (SELECT WorkerId FROM dbo.CompanyWorker WHERE CompanyId = @CompanyId);

			-- Delete rows from CompanyWorker table
			DELETE FROM dbo.CompanyWorker WHERE CompanyId = @CompanyId;

			-- Delete rows from WorkerSettings table
			DELETE FROM dbo.WorkerSettings WHERE WorkerId IN (SELECT WorkerId FROM dbo.CompanyWorker WHERE CompanyId = @CompanyId);

			-- Delete rows from Worker table
			DELETE FROM dbo.Worker WHERE Id IN (SELECT WorkerId FROM dbo.CompanyWorker WHERE CompanyId = @CompanyId);

			-- Delete rows from Skill table
			DELETE FROM dbo.Skill WHERE CompanyId = @CompanyId;

			--remaining shifts
			DELETE FROM WorkerShiftStatusLog WHERE WorkerShiftId in (select id from WorkerShift where shiftid in (select id from dbo.[Shift] where JobId in (Select Id from Job where CompanyId = @CompanyId)))
			DELETE FROM WorkerShift WHERE ShiftId in (select id from dbo.[Shift] where JobId in (Select Id from Job where CompanyId = @CompanyId))
			DELETE FROM ShiftNotification WHERE ShiftId in (select id from dbo.[Shift] where JobId in (Select Id from Job where CompanyId = @CompanyId))
			DELETE FROM ShiftSwap WHERE ShiftId in (select id from dbo.[Shift] where JobId in (Select Id from Job where CompanyId = @CompanyId))
			DELETE FROM dbo.[Shift] where JobId in (Select Id from Job where CompanyId = @CompanyId)

			--jobs
			DELETE DBO.JOB WHERE CompanyId = @CompanyId
	
			COMMIT TRANSACTION;
		END TRY
		BEGIN CATCH
		  SELECT ERROR_MESSAGE() AS ErrorMessage;
		  ROLLBACK TRAN
		END CATCH

		--INSERT
		BEGIN TRAN

		BEGIN TRY
			DECLARE @WorkerId UNIQUEIDENTIFIER
			DECLARE @JobName varchar(150)
			DECLARE @HexColor varchar(6)
			DECLARE @WorkCenterIdsCsv varchar(50)
			DECLARE @SkillIdsCsv varchar(100)
			DECLARE @JobIds varchar(100)
			CREATE TABLE #tmpSkills
			(
				[Id] [bigint],
				[Name] [nvarchar](100),
				[IsActive] [bit],
				[CompanyId] [int],
				[CreatedBy] [int],
				[CreatedDate] [datetime2](7),
				[LastUpdatedBy] [int],
				[LastUpdatedDate] [datetime2](7),
			)
			CREATE TABLE #tmpJobs
			(
				[Id] [bigint],
				[Name] [nvarchar](150),
				[HexColor] varchar(6),
				[IsActive] [bit],
				[WorkCenterIds] varchar(50)
			)
			CREATE TABLE #tmpWorkers
			(
				[Id] [uniqueidentifier],
				[FirstName] [varchar](50),
				[MiddleName] [varchar](50),
				[LastName] [varchar](50),
				[Email] [nvarchar](100),
				[MobilePhoneNumber] [varchar](20),
				[Type] [varchar](50),
				[CompanyName] [varchar](100),
				[Jobs] [varchar](150),
				[DeviceTokens] varchar(100)
			)
			SET NOCOUNT ON;
			--INSERT INTO dbo.SKILL and #tmpSkills
			INSERT INTO #tmpSkills EXEC [dbo].[CreateSkill] 'Skill 1',@UserId
			INSERT INTO #tmpSkills EXEC [dbo].[CreateSkill] 'Skill 2',@UserId
			INSERT INTO #tmpSkills EXEC [dbo].[CreateSkill] 'Skill 3',@UserId
			INSERT INTO #tmpSkills EXEC [dbo].[CreateSkill] 'Skill 4',@UserId
			INSERT INTO #tmpSkills EXEC [dbo].[CreateSkill] 'Skill 5',@UserId		
			SET @SkillIdsCsv = (select string_agg(id, ',') from #tmpSkills)

			--Production :  Assembler, Machine Operator, CNC Machinist, Test Operator, Welder
			--Quality Control :  Incoming Inspector, QC Inspector 
			--Stockroom : Shipping Clerk, Picker/Packer


			SET @WorkCenterIdsCsv = (select string_agg(id, ',') from WorkCenter where [Name] in ('Production'))	
			SET @JobName = 'Assembler'
			SET @HexColor = 'bd96ee'
			INSERT INTO #tmpJobs EXEC [dbo].[CreateJob] @UserId,@JobName,@HexColor,@UserId,@WorkCenterIdsCsv,@SkillIdsCsv --INSERT INTO Job,JobWorkCenter,JobSkill,WorkerSkill
		
			SET @JobName = 'Machine Operator'
			SET @HexColor = '7fd09d'
			INSERT INTO #tmpJobs EXEC [dbo].[CreateJob] @UserId,@JobName,@HexColor,@UserId,@WorkCenterIdsCsv,@SkillIdsCsv --INSERT INTO Job,JobWorkCenter,JobSkill,WorkerSkill
		
			SET @JobName = 'CNC Machinist'
			SET @HexColor = 'e8f9fd'
			INSERT INTO #tmpJobs EXEC [dbo].[CreateJob] @UserId,@JobName,@HexColor,@UserId,@WorkCenterIdsCsv,@SkillIdsCsv --INSERT INTO Job,JobWorkCenter,JobSkill,WorkerSkill
	
			SET @JobName = 'Test Operator'
			SET @HexColor = '59ce8f'
			INSERT INTO #tmpJobs EXEC [dbo].[CreateJob] @UserId,@JobName,@HexColor,@UserId,@WorkCenterIdsCsv,@SkillIdsCsv --INSERT INTO Job,JobWorkCenter,JobSkill,WorkerSkill

			SET @JobName = 'Welder'
			SET @HexColor = 'FD8F3F'
			INSERT INTO #tmpJobs EXEC [dbo].[CreateJob] @UserId,@JobName,@HexColor,@UserId,@WorkCenterIdsCsv,@SkillIdsCsv --INSERT INTO Job,JobWorkCenter,JobSkill,WorkerSkill


			SET @WorkCenterIdsCsv = (select string_agg(id, ',') from WorkCenter where [Name] in ('Quality Control'))	
			SET @JobName = 'Incoming Inspector'
			SET @HexColor = 'fee440'
			INSERT INTO #tmpJobs EXEC [dbo].[CreateJob] @UserId,@JobName,@HexColor,@UserId,@WorkCenterIdsCsv,@SkillIdsCsv --INSERT INTO Job,JobWorkCenter,JobSkill,WorkerSkill
		
			SET @JobName = 'QC Inspector'
			SET @HexColor = 'FFA800'
			INSERT INTO #tmpJobs EXEC [dbo].[CreateJob] @UserId,@JobName,@HexColor,@UserId,@WorkCenterIdsCsv,@SkillIdsCsv --INSERT INTO Job,JobWorkCenter,JobSkill,WorkerSkill
		
			SET @WorkCenterIdsCsv = (select string_agg(id, ',') from WorkCenter where [Name] in ('Stockroom'))	
			SET @JobName = 'Shipping Clerk'
			SET @HexColor = 'fee440'
			INSERT INTO #tmpJobs EXEC [dbo].[CreateJob] @UserId,@JobName,@HexColor,@UserId,@WorkCenterIdsCsv,@SkillIdsCsv --INSERT INTO Job,JobWorkCenter,JobSkill,WorkerSkill
		
			SET @JobName = 'Picker/Packer'
			SET @HexColor = '00f5d4'
			INSERT INTO #tmpJobs EXEC [dbo].[CreateJob] @UserId,@JobName,@HexColor,@UserId,@WorkCenterIdsCsv,@SkillIdsCsv --INSERT INTO Job,JobWorkCenter,JobSkill,WorkerSkill
		
	
			DECLARE @CreatedBy int = @UserId
			DECLARE @FirstName varchar(50)
			DECLARE @MiddleName varchar(50) = ''
			DECLARE @LastName varchar(50)
			DECLARE @Email nvarchar(100) = ''
			DECLARE @MobilePhoneNumber varchar(20)
			DECLARE @WorkerTypeId tinyint = 1 --1 = FULL TIME, 2 = PART TIME, 3 = GIG
			DECLARE @CompanyEmployeeId varchar(100) = NULL
			DECLARE @Skills [ListOfWorkerSkills]
			DECLARE @WorkerJobs [ListOfWorkerJobs]

			--insert into Worker,CompanyWorker,WorkerJob,workerskill
			SET @FirstName = 'Rohit'
			SET @LastName = 'Joshi'
			SET @MobilePhoneNumber = '+15854550338'
			SET @JobIds = (select string_agg(id, ',') from #tmpJobs);
			INSERT INTO @Skills SELECT ID, 2 FROM #tmpSkills;
			INSERT INTO @WorkerJobs SELECT ID, 0 FROM #tmpJobs; --job Expiry (optional)
	
			INSERT INTO #tmpWorkers
			EXEC [dbo].[CreateWorker] @FirstName,@MiddleName,@LastName,@Email,@MobilePhoneNumber,@CompanyId,@WorkerTypeId,@CompanyEmployeeId,@JobIds,@CreatedBy,@Skills,@WorkerJobs

			SET @FirstName = 'Mandy'
			SET @LastName = 'R.'
			SET @MobilePhoneNumber = '+12404393377';

			INSERT INTO #tmpWorkers
			EXEC [dbo].[CreateWorker] @FirstName,@MiddleName,@LastName,@Email,@MobilePhoneNumber,@CompanyId,@WorkerTypeId,@CompanyEmployeeId,@JobIds,@CreatedBy,@Skills,@WorkerJobs

			SET @FirstName = 'John'
			SET @LastName = 'Actisdano II'
			SET @MobilePhoneNumber = '+15706181515';

			INSERT INTO #tmpWorkers
			EXEC [dbo].[CreateWorker] @FirstName,@MiddleName,@LastName,@Email,@MobilePhoneNumber,@CompanyId,@WorkerTypeId,@CompanyEmployeeId,@JobIds,@CreatedBy,@Skills,@WorkerJobs

			-- [WorkerAvailability]
			-- 13.insert worker availability for @Today+10 days
			DECLARE @WorkerAvailability [dbo].[ListTimePeriod]

		--DECLARE @WorkerStartDate datetimeoffset(7) = DATEADD(day, -10, GETUTCDATE())
        DECLARE @WorkerStartDate datetimeoffset(7) = DATEADD(day, 1, GETDATE())
		DECLARE @WorkerEndDate datetimeoffset(7) = DATEADD(day, 10, GETDATE())

		WHILE @WorkerStartDate <= @WorkerEndDate
		BEGIN
			DECLARE @WorkerStartTime datetimeoffset(7) = CONVERT(datetimeoffset(7), CONVERT(varchar(10), @WorkerStartDate, 23) + 'T08:00:00+00:00')
			DECLARE @WorkerEndTime datetimeoffset(7) = CONVERT(datetimeoffset(7), CONVERT(varchar(10), @WorkerStartDate, 23) + 'T22:00:00+00:00')

				INSERT INTO @WorkerAvailability ([StartTime], [EndTime])
				VALUES (@WorkerStartTime, @WorkerEndTime)

				SET @WorkerStartDate = DATEADD(day, 1, @WorkerStartDate)
			END

			DECLARE workerCursor CURSOR FOR 
			SELECT Id FROM #tmpWorkers

			OPEN workerCursor

			FETCH NEXT FROM workerCursor INTO @WorkerId

			WHILE @@FETCH_STATUS = 0
			BEGIN
				-- SET worker availability
				EXEC [dbo].[SaveWorkerAvailability] @WorkerId,@WorkerAvailability

				FETCH NEXT FROM workerCursor INTO @WorkerId
			END

			CLOSE workerCursor
			DEALLOCATE workerCursor

			--[optional] --EXEC [dbo].[CreateJobSkill] @JobIds,@SkillName,@UserId --[OPTIONAL] INSERT INTO SKILL,JobSkill

			-- [Shift]
			-- 10.insert 3 shifts per day for @Today-10 and @Today+10 days, each covering all the jobs 
			DECLARE @tvpNewShifts [dbo].[ListOfShifts]

			DECLARE @StartDate datetimeoffset(7) = DATEADD(day, -10, SYSUTCDATETIME())
			DECLARE @EndDate datetimeoffset(7) = DATEADD(day, 10, SYSUTCDATETIME())
			DECLARE @StartTime time = '09:00:00'
			DECLARE @SecondShiftStartTime time = '13:00:00'
			DECLARE @ThirdShiftStartTime time = '16:00:00'
			DECLARE @IsPublished BIT = 1
			DECLARE @ShiftPremium1 [varchar](50) = '1'
			DECLARE @ShiftPremium2 [varchar](50) = '2'
			DECLARE @ShiftPremium3 [varchar](50) = '3'
			DECLARE @NumWorkersNeeded1 INT = 4
			DECLARE @NumWorkersNeeded2 INT = 5
			DECLARE @NumWorkersNeeded3 INT = 6
			DECLARE @JobId1 INT = (SELECT ID FROM #tmpJobs where [Name] = 'Assembler')
			DECLARE @JobId2 INT = (SELECT ID FROM #tmpJobs where [Name] = 'Incoming Inspector')
			DECLARE @JobId3 INT = (SELECT ID FROM #tmpJobs where [Name] = 'Shipping Clerk')
			DECLARE @WorkCenterId1 INT = (SELECT top 1 WorkCenterId FROM JobWorkcenter where JobId = @JobId1)
			DECLARE @WorkCenterId2 INT = (SELECT top 1 WorkCenterId FROM JobWorkcenter where JobId = @JobId2)
			DECLARE @WorkCenterId3 INT = (SELECT top 1 WorkCenterId FROM JobWorkcenter where JobId = @JobId3)
			WHILE (@StartDate <= @EndDate)
			BEGIN
				-- First shift (4 hours)
				DECLARE @firstShiftStart datetimeoffset(7) = DATEADD(minute, DATEDIFF(minute, GETUTCDATE(), @StartDate), CAST(@StartTime AS datetime))
				DECLARE @firstShiftEnd datetimeoffset(7) = DATEADD(minute, DATEDIFF(minute, GETUTCDATE(), @StartDate), CAST(DATEADD(hour, 4, @StartTime) AS datetime))
				SET @firstShiftStart = DATEADD(day, DATEDIFF(day, 0, SYSUTCDATETIME()), @firstShiftStart)
				SET @firstShiftEnd = DATEADD(day, DATEDIFF(day, 0, SYSUTCDATETIME()), @firstShiftEnd)

				INSERT INTO @tvpNewShifts ([StartDateTime],[EndDateTime],[JobId],[WorkCenterId],[NumWorkersNeeded],[ShiftPremium],[IsPublished],[CreatedBy])
				VALUES (@firstShiftStart,@firstShiftEnd,@JobId1,@WorkCenterId1,@NumWorkersNeeded1,@ShiftPremium1,@IsPublished,@CreatedBy)
	
				-- Second shift (5 hours)
				DECLARE @secondShiftStart datetimeoffset(7) = DATEADD(minute, DATEDIFF(minute, GETUTCDATE(), @StartDate), CAST(@SecondShiftStartTime AS datetime))
				DECLARE @secondShiftEnd datetimeoffset(7) = DATEADD(minute, DATEDIFF(minute, GETUTCDATE(), @StartDate), CAST(DATEADD(hour, 5, @SecondShiftStartTime) AS datetime))
				SET @secondShiftStart = DATEADD(day, DATEDIFF(day, 0, SYSDATETIMEOFFSET()), @secondShiftStart)
				SET @secondShiftEnd = DATEADD(day, DATEDIFF(day, 0, SYSDATETIMEOFFSET()), @secondShiftEnd)

				INSERT INTO @tvpNewShifts ([StartDateTime],[EndDateTime],[JobId],[WorkCenterId],[NumWorkersNeeded],[ShiftPremium],[IsPublished],[CreatedBy])
				VALUES (@secondShiftStart,@secondShiftEnd,@JobId2,@WorkCenterId2,@NumWorkersNeeded2,@ShiftPremium2,@IsPublished,@CreatedBy)
	
				-- Third shift (3 hours)
				DECLARE @thirdShiftStart datetimeoffset(7) = DATEADD(minute, DATEDIFF(minute, GETUTCDATE(), @StartDate), CAST(@thirdShiftStartTime AS datetime))
				DECLARE @thirdShiftEnd datetimeoffset(7) = DATEADD(minute, DATEDIFF(minute, GETUTCDATE(), @StartDate), CAST(DATEADD(hour, 3, @thirdShiftStartTime) AS datetime))
				SET @thirdShiftStart = DATEADD(day, DATEDIFF(day, 0, SYSDATETIMEOFFSET()), @thirdShiftStart)
				SET @thirdShiftEnd = DATEADD(day, DATEDIFF(day, 0, SYSDATETIMEOFFSET()), @thirdShiftEnd)

				--SELECT @firstShiftStart AS FirstShiftStart, @firstShiftEnd AS FirstShiftEnd, @secondShiftStart AS SecondShiftStart, @secondShiftEnd AS SecondShiftEnd, @thirdShiftStart AS ThirdShiftStart, @thirdShiftEnd AS ThirdShiftEnd

				INSERT INTO @tvpNewShifts ([StartDateTime],[EndDateTime],[JobId],[WorkCenterId],[NumWorkersNeeded],[ShiftPremium],[IsPublished],[CreatedBy])
				VALUES (@thirdShiftStart,@thirdShiftEnd,@JobId2,@WorkCenterId3,@NumWorkersNeeded3,@ShiftPremium3,@IsPublished,@CreatedBy)
	
				-- Move to the next day
				SET @StartDate = DATEADD(day, 1, @StartDate)
			END

			CREATE TABLE #tmpShifts
			(
				Id bigint,
				StartDateTime datetime,
				EndDateTime datetime,
				JobId int,
				WorkCenterId int,
				NumWorkersNeeded smallint,
				ShiftPremium varchar(50),
				IsPublished bit,
				IsActive bit
			)

			EXEC [dbo].[CreateShifts] @tvpNewShifts
			INSERT INTO #tmpShifts
			SELECT s.Id, s.StartDateTime, s.EndDateTime, s.JobId, s.WorkCenterId, s.NumWorkersNeeded, s.ShiftPremium, s.IsPublished, s.IsActive
			FROM [Shift] s inner join @tvpNewShifts ts on
				ts.JobId = s.JobId and ts.StartDateTime = s.StartDateTime and ts.EndDateTime = s.EndDateTime
				and ts.CreatedBy = s.CreatedBy and ts.WorkCenterId = s.WorkCenterId

			-- 12. insert worker shift acceptance based on shift->job and worker->job [WorkerShift]
			DECLARE @shiftId int, @shiftDate date, @status char(1) = 'A'
			DECLARE @workerShiftDates TABLE (WorkerId uniqueidentifier, ShiftDate date)

			DECLARE worker_cursor CURSOR FOR
			SELECT Id FROM #tmpWorkers

			OPEN worker_cursor

			FETCH NEXT FROM worker_cursor INTO @WorkerId

			WHILE @@FETCH_STATUS = 0
			BEGIN
				DECLARE shift_cursor CURSOR FOR
				SELECT id, CONVERT(date, StartDateTime) as ShiftDate
				FROM #tmpShifts

				OPEN shift_cursor

				FETCH NEXT FROM shift_cursor INTO @shiftId, @shiftDate

				WHILE @@FETCH_STATUS = 0
				BEGIN
					IF (SELECT COUNT(*) FROM @workerShiftDates WHERE WorkerId = @WorkerId AND ShiftDate = @shiftDate) = 0
					BEGIN
						INSERT INTO @workerShiftDates (WorkerId, ShiftDate) VALUES (@WorkerId, @shiftDate)
						EXEC [dbo].[WorkerShiftSignup] @WorkerId, @shiftId, @status

						-- [ShiftNotification]
						-- 11. insert shift notifications based on shift->job and worker->job

						INSERT INTO [dbo].[ShiftNotification]
							   ([WorkerId]
							   ,[ShiftId]
							   ,[Status]           
							   ,[ShiftPremium]
							   ,[CreatedBy]
							   ,[CreationDate]
							   ,[LastUpdatedBy]
							   ,[LastUpdatedDate])
						SELECT 
								@WorkerId,
								wsn.Id,
								'N',
								wsn.ShiftPremium,
								@CreatedBy,
								GETUTCDATE(),
								@CreatedBy,
								GETUTCDATE()            
							FROM #tmpShifts AS wsn
							WHERE wsn.Id = @shiftId;
					END

					FETCH NEXT FROM shift_cursor INTO @shiftId, @shiftDate
				END

				CLOSE shift_cursor
				DEALLOCATE shift_cursor

				FETCH NEXT FROM worker_cursor INTO @WorkerId
			END

			CLOSE worker_cursor
			DEALLOCATE worker_cursor
			COMMIT TRAN

		END TRY
		BEGIN CATCH
		  SELECT ERROR_MESSAGE() AS ErrorMessage;
		  ROLLBACK TRAN

		END CATCH
	END
END;

CREATE      PROCEDURE [dbo].[SaveWorkerAvailability]
    @WorkerId  VARCHAR(150),
    @WorkerAvailability ListTimePeriod READONLY
AS
BEGIN

    --DECLARE @TZ VARCHAR(100) = 'Eastern Standard Time'

    -- check for valid time period
    DECLARE @InvalidCount INT = 0
    set @InvalidCount = 
        (
            SELECT count(*) 
            from @WorkerAvailability wa
            where 
            DATEDIFF(HOUR,wa.StartTime,wa.EndTime)<=0
            OR wa.StartTime<GETDATE()
        )

    if @InvalidCount > 0 
    BEGIN
        THROW 500000, 'GATError.AddAvailability.InvalidTimePeriod', 1
    END

    

    -- check if there are any overlapping time periods in the input
    ;with ste as(
        SELECT 
            wa.StartTime, wa.EndTime,
            LAG(wa.EndTime,1,null) over (order by wa.StartTime) lag,
            DATEDIFF(SECOND,LAG(wa.EndTime,1,null) over (order by wa.StartTime),wa.StartTime) diff
        from 
            @WorkerAvailability wa
    )
    select 
        @WorkerId as WorkerId, ste.* 
    from
        ste
    where 
        diff < 0
    
    -- if there are overlapping time period, throw exception
    if @@ROWCOUNT > 0
    BEGIN
        THROW 500000, 'GATError.AddAvailability.OverLapping', 1
    END

    ----- check if there is an overlap with existing availability  within the input period -------
    
    -- get min and max datetime from input
    DECLARE @MinDateTime DateTimeOffset
    DECLARE @MaxDateTime DateTimeOffset    
    DECLARE @AllAvailability TABLE(StartTime DateTimeOffset, EndTime DateTimeOffset)

    SET @MinDateTime = (select min(StartTime) from @WorkerAvailability)
    SET @MaxDateTime = (select max(EndTime) from @WorkerAvailability)

    -- get existing availabilities set in the range
    -- insert int temp table
    INSERT INTO @AllAvailability
    SELECT 
        wa.StartDateTime,wa.EndDateTime 
    from 
        WorkerAvailability wa 
    where 
        wa.WorkerId=@WorkerId 
        and (
            (wa.EndDateTime BETWEEN @MinDateTime AND @MaxDateTime)
            OR (wa.StartDateTime BETWEEN @MinDateTime AND @MaxDateTime)
            )

    -- insert the new availability into temp table
    INSERT INTO @AllAvailability
    SELECT wp.StartTime, wp.EndTime from @WorkerAvailability wp

    -- check for overlapping in all availabilities
    ;with ste as(
        SELECT 
            wa.StartTime, wa.EndTime,
            LAG(wa.EndTime,1,null) over (order by wa.StartTime) lag,
            DATEDIFF(SECOND,LAG(wa.EndTime,1,null) over (order by wa.StartTime),wa.StartTime) diff
        from 
            @AllAvailability wa
    )
    select 
        @WorkerId as WorkerId, ste.* 
    from
        ste
    where 
        diff < 0

    -- if there are overlapping time period, throw exception
    if @@ROWCOUNT > 0
    BEGIN
        THROW 500000, 'GATError.AddAvailability.OverLapping', 1
    END


    BEGIN TRY
		BEGIN TRANSACTION;

        INSERT INTO WorkerAvailability(
            WorkerId,
            StartDateTime,
            EndDateTime,
            CreationDate,
            CreatedBy,
            LastUpdatedDate,
            LastUpdatedBy)
        select         
            @WorkerId as WorkerId,
            wp.StartTime,
            wp.EndTime,
            GETDATE(),0,GETDATE(),0
        from @WorkerAvailability wp

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

CREATE PROCEDURE [dbo].[ShiftsViewed]
    @tvpShiftViewed ListShiftViewedNotification READONLY
AS
BEGIN

MERGE dbo.ShiftNotification AS tgt
USING @tvpShiftViewed AS src ON tgt.ShiftId = src.ShiftId
	AND tgt.WorkerId = src.WorkerId
	AND (tgt.ShiftPremium = src.ShiftPremium or (src.ShiftPremium is NULL and tgt.ShiftPremium is NULL)) -- added this null check to handle null is not null comparison
	AND tgt.[Status] = src.[Status]
WHEN NOT MATCHED THEN
INSERT (WorkerId,ShiftId,[Status],ShiftPremium,CreatedBy,CreationDate,LastUpdatedBy,LastUpdatedDate)
    VALUES (src.WorkerId,src.ShiftId,src.[Status],src.ShiftPremium,0,GETDATE(),0,GETDATE());    

END;

CREATE FUNCTION [dbo].[Split](@String VARCHAR(8000), @Delimiter CHAR(1))    
RETURNS @temptable TABLE (items VARCHAR(8000))    
AS   
BEGIN 
	DECLARE @idx INT    
	DECLARE @slice VARCHAR(8000)    
   
	SELECT @idx = 1    
	IF LEN(@String)<1 OR @String IS NULL RETURN
   
	WHILE @idx!= 0    
	BEGIN    
		SET @idx = CHARINDEX(@Delimiter,@String)    
		IF @idx!=0    
			SET @slice = left(@String,@idx - 1)    
		ELSE    
			SET @slice = @String    

		IF(LEN(@slice)>0)
			INSERT INTO @temptable(Items) VALUES(@slice)    

			SET @String = right(@String,LEN(@String) - @idx)    
			IF LEN(@String) = 0 BREAK
	END
RETURN
END;

-- this function checks if company continious shift signup policy is violated if the @worker sign up for the @shiftid
-- it return BIT 1 or 0 for true and false
CREATE   FUNCTION [dbo].[udfCheckCompanyContiniousShiftSignup](
        @WorkerId UNIQUEIDENTIFIER,
        @ShiftId INT
)
RETURNS BIT
BEGIN

        DECLARE @MinutesLeftForWorker INT
        DECLARE @MinutesOfShift INT
        DECLARE @ShiftStart datetime
        DECLARE @ShiftEnd datetime
        DECLARE @ShiftOverlapCount INT	
		DECLARE @CompanyId INT


        DECLARE @ShiftWeekStartDateTime DateTimeOffSet
		DECLARE @ShiftWeekEndDateTime DateTimeOffSet
        DECLARE @ShiftsDuration FLOAT 
        DECLARE @MaxContiniousSignUpHours FLOAT = 0

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

        -- check company continious shift signup policy
        SET @MaxContiniousSignUpHours = 
            (SELECT MaxContiniousSignUpHours from Settings_ShiftCreation where CompanyId = @CompanyId)

        SET @ShiftsDuration = dbo.udfContiniousSignUpTime(@shiftId,@workerId)

        IF @MaxContiniousSignUpHours > 0 AND @ShiftsDuration > @MaxContiniousSignUpHours
        BEGIN
			--RAISERROR(N'Error.SP.WorkerShiftSignup.ExceedingContiniousSignUpLimit',16,1)
			RETURN cast(0 as BIT)
		END 

		-- get shift week start and end datetime
		set @ShiftWeekStartDateTime = dbo.udfShiftWeekStart(@shiftId,@CompanyId)
		set @ShiftWeekEndDateTime = DATEADD(dd,7,@ShiftWeekStartDateTime)
		
		SET @MinutesLeftForWorker = (Select [dbo].[udfMinutesLeftForWorker] (@ShiftWeekStartDateTime, @CompanyId, @workerId) as MinutesLeftForWorker)
		SET @MinutesOfShift = (SELECT DATEDIFF(minute, StartDateTime, EndDateTime) from Shift where Id = @shiftId)
		
		-- TWO WEEKS OVERLAP SHIFT LOGIC START
		-- if shift overlaps next week
		if @ShiftEnd>@ShiftWeekEndDateTime
		Begin	
		
			DECLARE @MinsLeftWeek1 INT 
			DECLARE @MINsLeftWeek2 INT
			DECLARE @DummyShiftId INT

			set @MinsLeftWeek1 = dbo.udfMinutesLeftForWorker(@ShiftWeekStartDateTime,@CompanyId,@workerId)
			set @MinsLeftWeek2 = dbo.udfMinutesLeftForWorker(@ShiftWeekEndDateTime,@CompanyId,@workerId)

			if DATEDIFF(mi,@ShiftStart,@ShiftWeekEndDateTime)>@MinsLeftWeek1
				or DATEDIFF(mi,@ShiftWeekEndDateTime,@ShiftEnd)>@MinsLeftWeek2
			BEGIN
				--RAISERROR(N'Error.SP.WorkerShiftSignup.HourExceeded',16,1)
				RETURN cast(0 as BIT)
			END			

		End
		-- TWO WEEKS OVERLAP SHIFT LOGIC END

		-- EXCEEDING WEEKLY LIMIT (NOT OVERLAPPING WEEKS)
		IF @ShiftEnd<=@ShiftWeekEndDateTime and @MinutesOfShift > @MinutesLeftForWorker 
		BEGIN
			--RAISERROR(N'Error.SP.WorkerShiftSignup.HourExceeded',16,1)		
			RETURN cast(0 as BIT)
		END

        RETURN cast(1 as BIT)

    END;

CREATE   FUNCTION [dbo].[udfContiniousSignUpTime](
    @shiftid INT,
    @workerId UNIQUEIDENTIFIER
)
RETURNS FLOAT
BEGIN

    DECLARE @ShiftDuration FLOAT = 0
    DECLARE @StartDateTime datetimeoffset
    DECLARE @EndDateTime datetimeoffset
    DECLARE @NextShiftDuration FLOAT
    DECLARE @PreShiftDuration FLOAT

    -- get signing up shift details
    SELECT
        @ShiftDuration = DATEDIFF(HH,StartDateTime,EndDateTime),
        @StartDateTime  = StartDateTime,
        @EndDateTime = EndDateTime
    FROM 
        Shift 
    WHERE 
        Id=@shiftid
      
    -- loop till exists next consecutive signed up shift
    WHILE 1=1
    BEGIN        

        select 
            @NextShiftDuration = DATEDIFF(HH,StartDateTime,EndDateTime),
            @EndDateTime = EndDateTime            
        from 
            Shift
        where 
            Id in (SELECT ShiftId FROM WorkerShift WHERE WorkerId=@workerId and [Status]='A')
            AND IsActive = 1
            AND IsPublished = 1
            AND StartDateTime = @EndDateTime

        IF @@ROWCOUNT > 0
            SET @ShiftDuration = @ShiftDuration + @NextShiftDuration
        ELSE
            BREAK
        
    END
    
    -- loop till exists previous consecutive signed up shift
    WHILE 1=1
    BEGIN        

        select 
            @PreShiftDuration = DATEDIFF(HH,StartDateTime,EndDateTime),
            @StartDateTime = StartDateTime            
        from 
            Shift
        where 
            Id in (SELECT ShiftId FROM WorkerShift WHERE WorkerId=@workerId and [Status]='A')
            AND IsActive = 1
            AND IsPublished = 1
            AND EndDateTime = @StartDateTime

        IF @@ROWCOUNT > 0
            SET @ShiftDuration = @ShiftDuration + @PreShiftDuration
        ELSE
            BREAK
        
    END


    RETURN @ShiftDuration

END;

CREATE   FUNCTION [dbo].[udfGetWeekStartDate](@WeekStartDay INT,@CurrDate DATETIME)
RETURNS DATETIME
AS
BEGIN

    DECLARE @WeekStartDate DATETIME 
    DECLARE @CurrWeekDay INT = DATEPART(DW,@CurrDate)

    IF @WeekStartDay = @CurrWeekDay
    BEGIN
        SET @WeekStartDate = @CurrDate
    END

    IF @WeekStartDay < @CurrWeekDay
    BEGIN
        select @WeekStartDate = DATEADD(day, -1 * (IIF(@CurrWeekDay - @WeekStartDay = 0,7,@CurrWeekDay - @WeekStartDay)), @CurrDate)
    END

    IF @WeekStartDay > @CurrWeekDay
    BEGIN
        select @WeekStartDate = DATEADD(day, -1 * (@CurrWeekDay + (7 - @WeekStartDay)), @CurrDate)
    END

    RETURN @WeekStartDate

END;

CREATE     FUNCTION [dbo].[udfGetWorkerExpLevel](
    @WorkerId UNIQUEIDENTIFIER,
    @CalDate DateTimeOffset
)
RETURNS @WorkerExpLevel TABLE
(
    ExpLevelId INT,
    ExpLevelTitle NVARCHAR(50),
    CompletedHours FLOAT
)
AS
BEGIN
    
    DECLARE @CurrMins INT = 0
    DECLARE @CurrHours FLOAT = 0

    -- calculate current worker job munites completed shifts
    SET @CurrMins = (
        SELECT             
            ISNULL(sum(DATEDIFF(MINUTE,s.StartDateTime,s.EndDateTime)),0) ShiftDurationMins
        from 
            WorkerShift_V ws
        inner join Shift s on ws.ShiftId=s.Id
        WHERE
            ws.WorkerId=@WorkerId        
            and ws.[Status]='A'
            and ws.isAbsent<>1
            and s.IsActive=1            
            and s.EndDateTime<@CalDate    
    )

    -- conver min to hours
    SET @CurrHours = @CurrMins / 60

    INSERT INTO @WorkerExpLevel
    select top 1 el.id, el.title,@CurrHours
        from ExpLevel el 
        where             
            @CurrHours BETWEEN el.MinHours and el.MaxHours
            OR (el.MaxHours=0 and @CurrHours>=el.MinHours)

    RETURN            

END;

CREATE    FUNCTION [dbo].[udfGetWorkerJobExpiry](
    @WorkerId UNIQUEIDENTIFIER,
    @JobId INT,    
    @CreationDate DateTime2,
    @IsExpired BIT,
    @IsExpiredBy INT,
    @IsExpiredDate DATETIME2
)
RETURNS @WorkerJobExpiry TABLE(
    IsExpired BIT,
    IsExpiredBy INT,
    IsExpiredDate DATETIME2
)
BEGIN

    DECLARE @MinWorkHrs INT = 0
    DECLARE @MinWorkHrsDays INT = 0

    -- fetch job expiration settings
    SELECT 
        @MinWorkHrs = MinWorkHrs,
        @MinWorkHrsDays = MinWorkHrsDays
    FROM
        Job
    WHERE
        Id=@JobId

    DECLARE @EndDate DATETIME2(7) = GETDATE()
    DECLARE @StartDate DATETIME2(7) = DATEADD(DAY, -1 * @MinWorkHrsDays, @EndDate)

    -- if setting is zero then job cannot be expired
    IF (@MinWorkHrs = 0 OR @MinWorkHrsDays = 0)
    BEGIN
        INSERT INTO @WorkerJobExpiry
        --SELECT 0, 0, GETDATE()
        SELECT @IsExpired, @IsExpiredBy, @IsExpiredDate
        RETURN 
    END

    -- if job is assigned before @minworkhrsdays 
    -- if job made active (not expired) before @minworkhrsdays then job cannot be expired
    IF (@StartDate < @CreationDate)
        OR (@IsExpiredBy<>0 and @IsExpired = 0 and @StartDate < @IsExpiredDate)
    BEGIN    
        INSERT INTO @WorkerJobExpiry
        SELECT @IsExpired, @IsExpiredBy, @IsExpiredDate
        RETURN 
    END

    -- calculate worker work hours for the job
    DECLARE @WorkHours INT = 0
    SET @WorkHours = dbo.udfWorkHours(@WorkerId,@JobId,@StartDate,@EndDate)
        
    -- if worker work hours is less the @MinWorkHrs then make job expired
    IF @WorkHours < @MinWorkHrs
    BEGIN
        INSERT INTO @WorkerJobExpiry
        SELECT cast(1 as BIt), 0, GETDATE()
        RETURN 
    END
    ELSE
    BEGIN
        INSERT INTO @WorkerJobExpiry
        SELECT cast(0 as BIt), 0, GETDATE()
        RETURN 
    END

    -- default return (job not expired)
    INSERT INTO @WorkerJobExpiry
    SELECT @IsExpired, @IsExpiredBy, @IsExpiredDate
    RETURN 

END;

CREATE   FUNCTION [dbo].[udfGetWorkerSARWkHrs](@WorkerId UNIQUEIDENTIFIER)
RETURNS @WeeksTable Table(sdt DATETIME, edt DATETIME, wk INT,hrs FLOAT)
AS
BEGIN

    DECLARE @WeekStartDay INT = 2 -- week start is Monday
    DECLARE @CurrDate DATETIME = GETDATE()    
    DECLARE @WeekCount INT = 4

    -- get week start date
    DECLARE @WeekStartDate DATETIME = dbo.udfGetWeekStartDate(@WeekStartDay,CAST(GETDATE() as Date))

    -- generate start and end dates for last @WeekCount weeks starting from now based on week start date    
    ;WITH cte as (
         --SELECT @CurrDate sdt, @WeekStartDate edt,1 as i    
        SELECT @WeekStartDate sdt, DATEADD(WEEK, -1, @WeekStartDate)  edt,1 as i  
        UNION ALL
        SELECT edt, DATEADD(WEEK, -1, edt) edt, (i + 1) from cte
        where i < @WeekCount
    )
    INSERT INTO @WeeksTable
    select sdt,edt,i as wk,0
    from cte

    -- calculate work hrs for last @WeekCount weeks
    DECLARE @i INT = 1
    WHILE @i <= @WeekCount
    BEGIN

        DECLARE @sdt DATETIME = (select edt from @WeeksTable where wk=@i)
        DECLARE @edt DATETIME = (select sdt from @WeeksTable where wk=@i)

        --SET @edt = dateadd(day,1,@edt)

        UPDATE @WeeksTable
        SET
        hrs = (        
            select             
                ISNULL(
                    --sum(DATEDIFF(mi,s.StartDateTime, s.EndDateTime))/60,
                    -- below stmt consider border line cases and only include hours within the week
                    sum(DATEDIFF(mi,IIF(cast(s.StartDateTime at time zone 'Eastern Standard Time' as DATETIME)<@sdt,@sdt,cast(s.StartDateTime at time zone 'Eastern Standard Time' as DATETIME)), IIF(cast(s.EndDateTime at time zone 'Eastern Standard Time' as DATETIME)>@edt,@edt,cast(s.EndDateTime at time zone 'Eastern Standard Time' as DATETIME))))/60,
                    0
                )
            from 
                WorkerShift_V ws
            INNER JOIN Shift s on ws.ShiftId = s.Id
            where 
                ws.WorkerId=@WorkerId
                and ws.[Status] = 'A'
                and ws.isAbsent <> 1
                and s.IsActive = 1
                and (
                        (cast(s.StartDateTime at time zone 'Eastern Standard Time' as datetime)>=@sdt and cast(s.StartDateTime at time zone 'Eastern Standard Time' as datetime)<@edt)
                        or 
                        (cast(s.EndDateTime at time zone 'Eastern Standard Time' as datetime)>=@sdt and cast(s.EndDateTime at time zone 'Eastern Standard Time' as datetime)<@edt)
                    )
        )
        where wk=@i

        SET @i = @i + 1

    END

    RETURN 

END;

CREATE   FUNCTION [dbo].[udfGetWorkerScoreItems](@WorkerId UNIQUEIDENTIFIER, @CalDate DateTimeOffset)
RETURNS @WorkerScoreItems TABLE(
    WorkerShiftId INT,
    WorkerShiftStatus CHAR(1),
    WorkerShiftIsAbsent Bit,
    WorkerShiftIsTardy Bit,
    InvalidShiftCancelPenalty FLOAT,
    ShiftStart DateTimeOffset,
    ShiftEnd DateTimeOffset,
    ShiftJobId INT,
    ShiftJobName NVARCHAR(100),
    WorkerShiftLastUpdated DATETIME2
)
AS
BEGIN

    DECLARE @ScorePeriodInDays INT = 30

    SELECT 
        TOP 1 
        @ScorePeriodInDays=ScorePeriodInDays
    FROM
        Settings_WorkerScore
    ORDER BY LastUpdatedDate desc


    INSERT INTO @WorkerScoreItems
    SELECT 
        ws.id,
        ws.[Status],    
        ws.IsAbsent,
        ws.IsTardy,
        dbo.udfInvalidShiftCancelPenalty(ws.id) p,
        s.StartDateTime,
        s.EndDateTime,
        s.JobId,
        j.Name as JobName,
        ws.LastUpdatedDate
    from 
        WorkerShift_V ws
    INNER JOIN Shift s on ws.ShiftId=s.Id
    INNER JOIN Job j on s.JobId=j.Id
    where 
        ws.WorkerId=@WorkerId
        AND s.EndDateTime BETWEEN DATEADD(DAY, -1 * @ScorePeriodInDays, @CalDate) and @CalDate    
        AND (ws.[Status]='C' 
                OR (
                        ws.[Status]='A' 
                        and (ws.isAbsent=1 OR ws.IsTardy=1)
                    )
            )

    RETURN

END;

CREATE   FUNCTION [dbo].[udfGetWorkerSetting](
    @WorkerId UNIQUEIDENTIFIER,
    @TypeId BIGINT
)
RETURNS NVARCHAR(255)
AS
BEGIN

    DECLARE @Val NVARCHAR(255) = NULL
    DECLARE @SettingDN NVARCHAR(255) = NULL

    SELECT @Val = [Value] 
    from WorkerSettings
    WHERE
    WorkerId=@WorkerId
    and WorkerSettingTypeId=@TypeId

    -- if worker does not have a value for this setting
    IF @Val is NULL
    BEGIN

        -- check if there is company level setting
        SET @SettingDN = (SELECT SettingDN FROM WorkerSettingType WHERE Id=@TypeId)

        -- if there no company level setting
        IF @SettingDN is NULL
        BEGIN

            -- get the default value for the worker setting type
            SELECT @Val = DefaultValue
            FROM WorkerSettingType
            where Id=@TypeId

        END
        ELSE
        BEGIN

            -- get the company level setting value
            set @Val = 
            (
                SELECT TOP 1 Value
                FROM Settings
                WHERE DN=@SettingDN and EntityId in (select CompanyId from CompanyWorker where WorkerId=@WorkerId and IsActive=1)
            )
            
            -- if there is no company level setting value
            IF @Val is NULL
            BEGIN

                -- get the default value for the company setting 
                set @Val = 
                (
                    SELECT TOP 1 Value
                    FROM Settings
                    WHERE DN=@SettingDN and EntityId=0
                )

            END

        END        

    END

    RETURN @Val
END;

CREATE   FUNCTION [dbo].[udfGetWorkerShiftByTime](
    @WorkerId UNIQUEIDENTIFIER,
    @StartDateTime DateTimeOffSet,
    @EndDateTime DateTimeOffSet
)
RETURNS VARCHAR
AS
BEGIN

    DECLARE @ShiftIds VARCHAR

    
    select
        @ShiftIds = STRING_AGG(ws.ShiftId, ',')
    from WorkerShift ws
        INNER JOIN Shift s on ws.ShiftId=s.Id
    where 
            ws.Status='A' and ws.ATTEND_STATUS<>'a'
        and (@StartDateTime BETWEEN s.StartDateTime and s.EndDateTime
            or @EndDateTime BETWEEN s.StartDateTime and s.EndDateTime
            or s.StartDateTime BETWEEN @StartDateTime and @EndDateTime
            or s.EndDateTime BETWEEN @StartDateTime and @EndDateTime)	
    

    RETURN @ShiftIds

END;

CREATE     FUNCTION [dbo].[udfGetWorkerSignedUpHrs](
    @WorkerId UNIQUEIDENTIFIER,
    @WorkDate DATETIME,
    @LocalTimeZone VARCHAR(255)
)
RETURNS FLOAT
BEGIN
    -- this function returns total hours signed up by the worker on @workdate based on @localtimezone

    DECLARE @WorkHrs FLOAT = 0.0
    DECLARE @WorkEndDate DATETIME = DATEADD(day,1,@WorkDate)

    SET @WorkHrs =
    (
        SELECT 
            ISNULL(
                sum
                (
                    case 
                        WHEN cast(s.StartDateTime at time zone @LocalTimeZone as datetime)<=@WorkDate then DATEDIFF(MINUTE,@WorkDate,cast(s.EndDateTime at time zone @LocalTimeZone as datetime))
                        WHEN cast(s.EndDateTime at time zone @LocalTimeZone as datetime)>=@WorkEndDate then DATEDIFF(MINUTE,@WorkEndDate,cast(s.EndDateTime at time zone @LocalTimeZone as datetime))
                        ELSE DATEDIFF(MINUTE,s.StartDateTime,s.EndDateTime)
                    END
                ),
            0) / 60.0 AS WorkHrs
        from WorkerShift ws
        inner join shift s on ws.ShiftId=s.Id
        WHERE
        ws.WorkerId=@WorkerId
        and ws.[Status]='A' and ws.ATTEND_STATUS<>'a' 
        AND (
                cast((s.StartDateTime at time zone @LocalTimeZone) as Date)=@WorkDate
                or cast((s.EndDateTime at time zone @LocalTimeZone) as Date)=@WorkDate
            )
    )   

    return @WorkHrs
END;

CREATE   FUNCTION [dbo].[udfInvalidShiftCancelPenalty](
    @WorkerShiftId BIGINT
)
RETURNS FLOAT
BEGIN

    -- settings to be stored in a table    
    DECLARE @InvalidShiftCancelPenalty FLOAT = 0.5

    -- fetch settings 
    SELECT 
        TOP 1         
        @InvalidShiftCancelPenalty=CancelPenaltyRate
    FROM
        Settings_WorkerScore
    ORDER BY LastUpdatedDate desc

    DECLARE @TotalPenalty FLOAT = 0.0

    DECLARE @ShiftId BIGINT 
    DECLARE @WorkerId UNIQUEIDENTIFIER
    DECLARE @AcceptedDate datetimeoffset
    DECLARE @CancelledDate datetimeoffset
    DECLARE @ShiftWorkerStatus VARCHAR(1)

    -- fetch worker shift details
    SELECT
        @ShiftId=ShiftId,
        @WorkerId=WorkerId,
        @ShiftWorkerStatus=[Status],
        @CancelledDate=LastUpdatedDate,
        @AcceptedDate=CreationDate
    FROM
        WorkerShift
    WHERE
        Id=@WorkerShiftId      

    if @ShiftWorkerStatus <> 'C'
    BEGIN
        SET @TotalPenalty = 0.0
        RETURN @TotalPenalty
    END

    DECLARE @ShiftStartDateTime DateTimeOffset
    DECLARE @ShiftJobId INT

    -- fetch shift details
    SELECT
        @ShiftStartDateTime=StartDateTime,
        @ShiftJobId=JobId
    FROM
        Shift
    WHERE
        Id=@ShiftId
    
    DECLARE @WorkerShiftCancelDays TINYINT = 0
    DECLARE @ShiftConfirmationLockHours INT = 0

    -- fetch company settings
    SELECT 
        @WorkerShiftCancelDays=WorkerShiftCancelDays,
        @ShiftConfirmationLockHours=ShiftConfirmationLockHours
    from Settings_ShiftCreation
    where CompanyId=(select companyid from job where Id=@ShiftJobId)

    if (
        -- check for restriction upon cancelling within a period before the shift start
        (@WorkerShiftCancelDays > 0
        AND DATEADD(DAY, @WorkerShiftCancelDays ,@CancelledDate) > @ShiftStartDateTime)

        OR
        
        -- check for restriction upon cancelling after a period of acceptance
        (@ShiftConfirmationLockHours>0
        AND @CancelledDate > DATEADD(HOUR,@ShiftConfirmationLockHours,@AcceptedDate))
        )
        
    BEGIN
        SET @TotalPenalty = @InvalidShiftCancelPenalty
    END

    return @TotalPenalty
END;

-- =============================================
-- Author:		Rayees Afroz
-- Create date: 2022-07-05
-- Description:	returns is new flag for a shift
-- =============================================
CREATE   FUNCTION [dbo].[udfIsNewShift]
(
	@shiftId int 
)
RETURNS bit
AS
BEGIN
	declare @isNewShift bit = 0

	-- check if the shift is last updated within 24 hrs
	set @isNewShift =
		(select 
			IIF(DATEDIFF(hh,LastUpdatedDate,getdate())<24,1,0) 
		from 
			shift 
		where 
			id=@shiftId)

	if @isNewShift = 1
		return @isNewShift

	DECLARE @needed		int
	DECLARE @vacant		int
	DECLARE @responses	int

	-- check if there was any cancellation within 24 hrs
	select 	
		@needed=s.NumWorkersNeeded,	
		@vacant=(s.NumWorkersNeeded-dbo.udfSlotsFilled(s.Id)),
		@responses=(select count(*) from WorkerShift ws where s.Id=ws.ShiftId)	
	from Shift s
	where 
	s.Id=@shiftId
	and s.Id in (select 
					ws.ShiftId 
					from 
					WorkerShift ws 
					where 
					ws.ShiftId=@shiftId and ws.Status='C' 
					and DATEDIFF(hh,ws.LastUpdatedDate,getdate())<24)

	-- after cancellation, responses must be >= neeeded
	-- and slot(s) vacant
	if @@ROWCOUNT > 0 and @responses >= @needed and @vacant > 0
	Begin
		set @isNewShift = @isNewShift | 1
	End

	return @isNewShift
END;

CREATE   FUNCTION [dbo].[udfLocationDateTime](
    @DateTimeUtc DateTimeOffset,
    @LocationTimeZone VARCHAR(255)
)
RETURNS DATETIME2
AS 
BEGIN
	DECLARE @LocationDateTime DATETIME2
	
    SET @LocationDateTime = (select @DateTimeUtc at time zone @LocationTimeZone)

    RETURN @LocationDateTime
END;

CREATE FUNCTION [dbo].[udfMaxWorkersForJob](
    @jobId INT
)
RETURNS INT
AS 
BEGIN
	DECLARE @MaxWorkersQualifiedForJob INT
	DECLARE @CompanyId int
	/*SET @MaxWorkersQualifiedForJob = (select count(wj.WorkerId) from WorkerJob wj
										inner join JobWorkcenter jwc on wj.JobId = jwc.JobId 
										inner join WorkCenter wc on jwc.WorkcenterId = wc.Id 
										inner join Location l  on  l.Id = wc.LocationId 
										INNER JOIN CompanyWorker cw ON wj.WorkerId = cw.WorkerId and l.CompanyId = cw.CompanyId 
										where wj.JobId=@jobId AND cw.IsActive = 1)*/

	-- get companyid from jobid; considering that all jobs goes to one location and one company through multiple workcenters
	set @companyid = 
		(select distinct l.CompanyId from Location l
			where l.id in (select wc.locationid from WorkCenter wc
							inner join JobWorkcenter jw on wc.id=jw.workcenterid
							where jw.jobid=@jobid
							)
		)

	-- get worker count for the @jobid who is active in the @companyid
	set @MaxWorkersQualifiedForJob = 
		(SELECT count(wj.WorkerId)
		FROM WorkerJob wj 
		inner join CompanyWorker cw 
			on wj.WorkerId=cw.WorkerId
		WHERE wj.JobId = @jobId 
			and cw.CompanyId=@companyid 
					and cw.IsActive=1)

    RETURN @MaxWorkersQualifiedForJob
END;

CREATE FUNCTION [dbo].[udfMinutesLeftForWorker](
    @shiftWeekStartDateTime datetimeoffset,
	@companyId Int,
    @workerId VARCHAR(50)
)
RETURNS INT
AS 
BEGIN
	DECLARE @MinutesDoneForWorker INT
	DECLARE @MinutesElgibleForWorker INT
	DECLARE @MinutesLeftForWorker INT
	DECLARE @WeekNumberOfShift INT	
	DECLARE @ShiftWeekEndDateTime Datetime	
		
	-- get shift week start datetime from shiftid and companyid
	--set @ShiftWeekStartDateTime = (select dbo.udfShiftWeekStart(@shiftId,@CompanyId))
	set @shiftWeekEndDateTime = DATEADD(day,7,@ShiftWeekStartDateTime)

	-- get eligible minutes for company and worker type
	select @MinutesElgibleForWorker=wth.MaxHours * 60
		from  WorkerTypeHours wth
		where 
		wth.CompanyId = @companyId
		and wth.WorkerTypeId=(select WorkerTypeId from CompanyWorker 
								where CompanyId=@companyId and WorkerId=@workerId)
		
	
	-- calculate worker's booking time (minutes) for the week involving the shift
	SELECT 			
		
		@MinutesDoneForWorker = sum(DATEDIFF(minute, 
						IIF(s.StartDateTime>=@shiftWeekStartDateTime,s.StartDateTime,@shiftWeekStartDateTime),
						IIF(s.EndDateTime<@shiftWeekEndDateTime,s.EndDateTime,@shiftWeekEndDateTime)))
			 
			from 
				WorkerShift ws 				
				inner join shift s on ws.ShiftId = s.id				
			where 
				s.IsActive = 1
				and ws.WorkerId = @workerId
				--and s.Id=@shiftId
				and ws.Status = 'A'
				and (
				(s.StartDateTime >= @ShiftWeekStartDateTime
					and s.StartDateTime < @shiftWeekEndDateTime) 
						or 
				(s.EndDateTime > @ShiftWeekStartDateTime
					and s.EndDateTime < @shiftWeekEndDateTime))
			
	-- calcuate the worker remaining time for the week involving the shift
	SET @MinutesLeftForWorker =  @MinutesElgibleForWorker - @MinutesDoneForWorker
	
    RETURN @MinutesLeftForWorker
   
END;

CREATE FUNCTION [dbo].[udfNumWorkersNotified](
    @jobId INT
)
RETURNS INT
AS 
BEGIN
	DECLARE @WorkersNotified INT
	DECLARE @CompanyId int
	/*SET @WorkersNotified = (SELECT COUNT(*) FROM WorkerJob wj INNER JOIN Worker w ON wj.WorkerId = w.Id 
								INNER JOIN CompanyWorker cw ON w.Id = cw.WorkerId
								WHERE JobId = @jobId AND cw.IsActive = 1)*/
	/*SET @WorkersNotified = (SELECT count(distinct wj.WorkerId) FROM WorkerJob wj 	
                            INNER JOIN CompanyWorker cw ON wj.WorkerId = cw.WorkerId
                            --inner join job j on wj.JobId=j.Id
                            --inner join Location l on j.LocationId=l.Id
							inner join jobworkcenter jw on wj.jobid=jw.JobId
							inner join WorkCenter wc on jw.workcenterid=wc.id
							inner join location l on wc.LocationId=l.id
                            WHERE wj.JobId = @jobId and l.CompanyId=cw.CompanyId AND cw.IsActive = 1)*/

	-- get companyid from jobid; considering that all jobs goes to one location and one company through multiple workcenters
set @companyid = 
	(select l.CompanyId from Location l
		where l.id in (select wc.locationid from WorkCenter wc
						inner join JobWorkcenter jw on wc.id=jw.workcenterid
						where jw.jobid=@jobid
						)
	)

-- get worker count for the @jobid who is active in the @companyid
set @WorkersNotified = 
	(SELECT count(wj.WorkerId)
	FROM WorkerJob wj 
	inner join CompanyWorker cw 
		on wj.WorkerId=cw.WorkerId
	WHERE wj.JobId = @jobId 
		and cw.CompanyId=@companyid 
				and cw.IsActive=1)

    RETURN @WorkersNotified
END;

CREATE     FUNCTION [dbo].[udfReliabilityScore](
    @WorkerId UNIQUEIDENTIFIER,
    @CalDate DateTimeOffset
)
RETURNS FLOAT
BEGIN

    -- settings
    --DECLARE @ShiftBatchSize INT = 50 -- last 30 days instead of shifts are considered now
    DECLARE @ScorePeriodInDays INT = 30
    DECLARE @CancelLimitWithinPeriod INT = 5
    DECLARE @CancelPenaltyRate FLOAT = 0.5
    DECLARE @AbsentPenaltyRate FLOAT = 1.0
    DECLARE @TardyPenaltyRate FLOAT = 0.5
    DECLARE @MinScore FLOAT = 1.0
    DECLARE @MaxScore FLOAT = 5.0

    -- fetch settings 
    SELECT 
        TOP 1 
        @ScorePeriodInDays=ScorePeriodInDays,
        @CancelLimitWithinPeriod=CancelLimitWithinPeriod,
        @CancelPenaltyRate=CancelPenaltyRate,
        @AbsentPenaltyRate=AbsentPenaltyRate,
        @MinScore=MinScore,
        @MaxScore=MaxScore
    FROM
        Settings_WorkerScore
    ORDER BY LastUpdatedDate desc

    -- local var
    DECLARE @ValidCancelPenalty FLOAT = 0.0
    DECLARE @InValidCancelPenalty FLOAT = 0.0
    DECLARE @AbsentPenalty FLOAT = 0.0
    DECLARE @TardyPenalty FLOAT = 0.0
    DECLARE @TotalPenalty FLOAT = 0.0
    DECLARE @Score FLOAT = @MaxScore


    -- fetch all cancelled and no show shifts for last @scoreperioddays
    DECLARE @WS AS TABLE(wsid BIGINT,wsstatus char(1),wsisabsent bit,wsistardy bit, p float)
    INSERT INTO @WS
    SELECT 
        WorkerShiftId AS Id,
        WorkerShiftStatus as [Status],    
        WorkerShiftIsAbsent as [IsAbsent],
        WorkerShiftIsTardy as [IsTardy],
        InvalidShiftCancelPenalty AS p
    from 
        WorkerScoreItem ws where WorkerId=@WorkerId
    

    -- calculate penalty for invalid cancellations in last @ScorePeriodInDays days
    SET @InValidCancelPenalty = (
        SELECT ISNULL(sum(p),0) from @ws where wsstatus='C'
    )

    -- calculate penalty for valid cancellations
    -- @CancelPenaltyRate for every @CancelLimitWithinPeriod within last @ScorePeriodInDays
    SET @ValidCancelPenalty = (
        SELECT IIF(
                    count(*) >= @CancelLimitWithinPeriod,
                    count(*) * @CancelPenaltyRate,
                    0
                ) 
        from @ws where wsstatus='C' and p=0
    )

    -- calculate absent penalty
    SET @AbsentPenalty = (    
        SELECT count(*) * @AbsentPenaltyRate from @WS where wsstatus='A' and wsisabsent=1
    )

    -- calculate tardy penalty
    SET @TardyPenalty = (    
        SELECT count(*) * @TardyPenaltyRate from @WS where wsstatus='A' and wsistardy=1
    )

    -- calculate total penalty
    set @TotalPenalty = @ValidCancelPenalty + @InvalidCancelPenalty + @AbsentPenalty + @TardyPenalty

    -- calculate final score
    set @Score = IIF( (@MaxScore - @TotalPenalty) < @MinScore , @MinScore , (@MaxScore - @TotalPenalty))

    RETURN @Score

END;

CREATE   Function [dbo].[udfShiftAcceptanceRate](
    @WorkerId UNIQUEIDENTIFIER
)
RETURNS INT
AS
BEGIN

        DECLARE @SAR INT = 0
    DECLARE @MinWeekHrs INT = 12
    DECLARE @WeekCount INT = 4

    -- get min weekly hours and week count from settings
    select 
    top 1 
        @MinWeekHrs = SarMinWeeklyHrs,
        @WeekCount = SarWeekCount
    from 
        Settings_WorkerScore 
    order by 
        CreationDate desc

    SET @SAR = 
        (
            SELECT sum(IIF(
                CompletedHrs>=@MinWeekHrs,
                100/@WeekCount,
                0
            )) sar from WorkerSARWkHrs where WorkerId=@WorkerId
        )

    RETURN @SAR

END;

CREATE FUNCTION [dbo].[udfShiftViews](
    @shiftId INT,
	@companyId INT
)
RETURNS INT
AS 
BEGIN

	DECLARE @ShiftsViewed INT
	SET @ShiftsViewed = (	
	
		SELECT COUNT(*) FROM (
			SELECT sn.WorkerId FROM 
				ShiftNotification sn
				inner join CompanyWorker cw on cw.WorkerId=sn.WorkerId and cw.CompanyId=@companyId
                inner join Shift s on sn.ShiftId=s.Id
				WHERE 
					sn.ShiftId = @shiftId 
                    AND [Status] = 'V' 
                    AND cw.IsActive = 1              
                    AND s.JobId in (select wj.JobId from WorkerJob wj where wj.WorkerId=sn.WorkerId) -- only workers who have the job      
				GROUP BY sn.WorkerId) tmp
		)

RETURN @ShiftsViewed
END;

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
CREATE   FUNCTION [dbo].[udfShiftWeekStart] 
(
	-- Add the parameters for the function here
	@shiftId INT,
	@companyId INT
)
RETURNS datetime
AS
BEGIN
	declare @startDayOfWeek INT 
	declare @startTimeOfWeek Varchar(12)
	declare @weekStartDate Date
	declare @shiftStartDateTime Datetime
	declare @fromDate datetime
	
	-- fetch company settings (in UTC) i.e. week start day and time
	select @startDayOfWeek = dbo.udfWeekDayNumFromName(WorkWeekStartDay) , 
			@startTimeOfWeek = WorkWeekStartTime 
		from Settings_ShiftCreation where CompanyId=@companyId

	-- fetch shift startDateTime and calculate shift WeekStartDate
	select 
		@weekStartDate=DATEADD(day,-1*(DATEPART(WEEKDAY,StartDateTime)-@startDayOfWeek),StartDateTime),
		@shiftStartDateTime=StartDateTime
		from Shift where Id=@shiftId

	-- generate shift WeekStartDateTime
	set @fromDate = CONCAT(@weekStartDate,' ',@startTimeOfWeek)

	-- if date in future then get previous week start date time
	set @fromDate = case when @fromDate>@shiftStartDateTime then @fromDate-7 else @fromDate end

	RETURN @fromDate

END;

CREATE FUNCTION [dbo].[udfSlotsFilled](
    @shiftId INT
)
RETURNS INT
AS 
BEGIN
	DECLARE @SlotsFilled INT
	SET @SlotsFilled = (SELECT COUNT(*) AS SlotsFilled FROM [WorkerShift] ws 
							--INNER JOIN [Shift] s ON ws.ShiftId = s.id
							--INNER JOIN Job j ON s.JobId = j.Id
								WHERE ws.ShiftId = @shiftId AND ws.[Status] = 'A')
    RETURN @SlotsFilled
END;

CREATE   FUNCTION [dbo].[udfUserWorkCenters](    
	@userid INT
)
RETURNS VARCHAR(255)
AS 
BEGIN
	
	DECLARE @userWorkCenterIds VARCHAR(255)
	SET @userWorkCenterIds = 
        (select STRING_AGG(sub.Id,',') from
        (SELECT Id  
            FROM WorkCenter 
            WHERE 
            LocationId IN (SELECT EntityId FROM EntityUser WHERE UserId = @UserId 
            AND LuEntityId = (SELECT Id FROM [dbo].[LuEntity] WHERE Entity = 'Location'))
        UNION 
        SELECT Id 
            FROM WorkCenter 
            WHERE Id IN (
            SELECT EntityId FROM EntityUser 
            WHERE UserId = @userid 
            AND LuEntityId = (SELECT Id FROM [dbo].[LuEntity] WHERE Entity = 'WorkCenter'))
        ) sub)

    RETURN @userWorkCenterIds 

END;

CREATE   FUNCTION [dbo].[udfUtcOffsetToMin](
    @utcOffset VARCHAR(6)
)
RETURNS INT
AS
BEGIN
    return DATEDIFF(MINUTE, 0, SUBSTRING(@utcOffset,2,5)) * IIF(SUBSTRING(@utcOffset,1,1) = '-',-1,1)     
END;

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
create     FUNCTION [dbo].[udfWeekDayNumFromName]
(
	-- Add the parameters for the function here
	@WeekName varchar(12)
)
RETURNS int
AS
BEGIN
	Declare @WeekNum INT = 1
	
	set @WeekNum = case lower(@WeekName) 
					when 'sunday' then 1
					when 'monday' then 2
					when 'tuesday' then 3
					when 'wednesday' then 4
					when 'thursday' then 5
					when 'friday' then 6
					when 'saturday' then 7
					else 1
					end

	return @WeekNum
END;

-- this function returns worker composite score used in shift auto accepts
CREATE   FUNCTION [dbo].[udfWorkerCompositeScore](
    @WorkerId UNIQUEIDENTIFIER
)
RETURNS FLOAT
BEGIN

    DECLARE @cscore FLOAT = 0.0

    SET @cscore = 
        (
            SELECT 
                round(TotalCompletedHours/100.0 + sar/25.0 + 5-RScore,3) cscore 
            from 
                Worker
            where
                Id=@WorkerId
        )

    RETURN @cscore

END;

CREATE   FUNCTION [dbo].[udfWorkerJobExpLevelId](
    @WorkerId UNIQUEIDENTIFIER,
    @JobId INT,
    @InitExpertLevelId INT    
)
RETURNS INT
BEGIN

    DECLARE @WorkerJobLevelId INT
        -- local vars
    DECLARE @CalDate datetimeoffset = GETDATE()
    DECLARE @InitExpertLevelHours INT = 0
    DECLARE @CurrJobMins INT = 0
    DECLARE @CurrJobHours INT = 0
    DECLARE @TotalJobHours INT = 0

    -- get  initial worker job expert level hours
    SET @InitExpertLevelHours = (
        SELECT MinHours from JobExpLevel where Id=@InitExpertLevelId
    )
    
    -- calculate current worker job munites completed shifts
    SET @CurrJobMins = (
        SELECT             
            ISNULL(sum(DATEDIFF(MINUTE,s.StartDateTime,s.EndDateTime)),0) ShiftDurationMins
        from 
            WorkerShift_V ws
        inner join Shift s on ws.ShiftId=s.Id
        WHERE
            ws.WorkerId=@WorkerId        
            and ws.[Status]='A'
            and ws.isAbsent<>1
            and s.IsActive=1
            and s.JobId=@JobId
            and s.EndDateTime<@CalDate    
    )

    -- conver min to hours
    SET @CurrJobHours = @CurrJobMins / 60

    -- calculate total hours
    SET @TotalJobHours = @InitExpertLevelHours + @CurrJobHours

    -- get worker job current expert level id
    SET @WorkerJobLevelId = ( 
        select top 1 wel.id
        from JobExpLevel wel 
        where 
            wel.JobId=@JobId
            AND (
                @TotalJobHours BETWEEN wel.MinHours and wel.MaxHours
                OR (wel.MaxHours=0 and @TotalJobHours>=wel.MinHours)
            )
    )

    RETURN @WorkerJobLevelId
END;

CREATE FUNCTION [dbo].[udfWorkerJobs](
    @workerId UNIQUEIDENTIFIER,
	@companyId INT
)
RETURNS VARCHAR(255)
AS 
BEGIN
	
	DECLARE @jobIds VARCHAR(255)
	SET @jobIds = (SELECT STRING_AGG(wj.JobId,',') FROM WorkerJob wj
					--INNER JOIN Job j on wj.JobId = j.Id
					--inner join jobworkcenter jw on wj.JobId=jw.jobid					
					WHERE 
						wj.WorkerId = @workerId 
						and wj.JobId in (select jw.jobid from jobworkcenter jw
								inner join WorkCenter wc on jw.workcenterid = wc.Id
								INNER JOIN Location l on wc.LocationId = l.Id
								where l.CompanyId=@companyId
										))

    RETURN @jobIds 

END;

CREATE   FUNCTION [dbo].[udfWorkerTier](@WorkerId UNIQUEIDENTIFIER)
RETURNS INT
BEGIN

    DECLARE @TierSeq INT = 1

    SET @TierSeq = (

        SELECT 
            case 
                when 
                    RScore >= 4.5 
                    and SAR > 50 
                    and ExpLevelId=(select Id from ExpLevel where lower(Title)='advanced') 
                then 3
                when 
                    RScore < 4 
                    or SAR < 50 
                    or ExpLevelId=(select Id from ExpLevel where lower(Title)='beginner') 
                then 1
                else 2
            END
        FROM 
            Worker 
        WHERE
            Id=@WorkerId

    )    

    RETURN @TierSeq

END;

CREATE    FUNCTION [dbo].[udfWorkHours](
    @WorkerId UNIQUEIDENTIFIER,
    @JobId INT,
    @StartDate DateTime2,
    @EndDate DATETIME2
)
RETURNS INT
BEGIN

    DECLARE @WorkHours INT = 0

    SET @WorkHours = (

        -- fetch all accepted & completed worker active shifts for the job 
        -- between given start and end dates
        -- where worker is not absent
        SELECT
            ISNULL(SUM(DATEDIFF(HH,s.StartDateTime,s.EndDateTime)),0) Workhrs
        from WorkerShift ws
        inner join Shift s on ws.ShiftId=s.Id
        where
            ws.WorkerId=@WorkerId and ws.[Status]='A' and ws.ATTEND_STATUS<>'a'
            and s.IsActive=1 and s.JobId=@JobId
            and s.EndDateTime BETWEEN @StartDate and @EndDate

    )
        
    RETURN @WorkHours

END;

CREATE     PROCEDURE [dbo].[UpdateCompany]	
    @Id int,
	@Name varchar(150),
    @IsActive bit = 1,
	@UpdatedBy 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,
    @WorkerTypeHours ListWorkerTypeHours READONLY
AS
BEGIN

    BEGIN TRY
        BEGIN TRANSACTION

        -- update company table
        UPDATE 
            [dbo].[Company] 
        SET
            [Name]=@Name,
            [IsActive]=@IsActive,
            [LastUpdatedBy]=@UpdatedBy,
            [LastUpdatedDate]=GETDATE()
        WHERE
            Id=@Id

        -- update settings table
        UPDATE 
            Settings_ShiftCreation
        SET           
            DefaultShiftDuration=@DefaultShiftDuration,
            MinShiftDuration=@MinShiftDuration,
            MaxShiftDuration=@MaxShiftDuration,
            WorkWeekStartDay=@WorkWeekStartDay,
            WorkWeekStartTime=@WorkWeekStartTime,
            ShiftMinAdvanceInDays=@ShiftMinAdvanceInDays,
            ShiftMaxAdvanceInDays=@ShiftMaxAdvanceInDays,
            MinNumDaysPublished=@MinNumDaysPublished,
            MaxNumDaysPublished=@MaxNumDaysPublished,
            SignUpCutOffDays=@SignUpCutOffDays,
            ShiftCancelAllow=@ShiftCancelAllow,
            ShiftModAllowTillDays=@ShiftModAllowTillDays,
            WorkerShiftCancelDays=@WorkerShiftCancelDays,            
            LastUpdatedBy=@UpdatedBy,
            LastUpdatedDate=GETDATE(),
            ShiftConfirmationLockHours=@ShiftConfirmationLockHours,
            MaxContiniousSignUpHours=@MaxContiniousSignUpHours,
            EnableDailyReportInEmail=@EnableDailyReportInEmail,
            EnableDailySMSReminderToWorker=@EnableDailySmsReminder
        WHERE
            CompanyId=@Id

        DECLARE @table1 TABLE(
            Id INT IDENTITY(1,1),
            WTID INT,
            MinH INT,
            MaxH INT
        )

        -- update worker type hours
        UPDATE
            t1
        SET
            t1.MinHours = t2.MinHours,
            t1.MaxHours = t2.MaxHours,
            LastUpdatedBy = @UpdatedBy,
            LastUpdatedDate = GETDATE()
        FROM
            WorkerTypeHours t1
        INNER JOIN 
            @WorkerTypeHours t2 on 
                t1.WorkerTypeId=t2.WorkerTypeId 
                and t1.CompanyId=@Id

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

        THROW;
    END CATCH
		
END;

CREATE PROCEDURE [dbo].[UpdateCompanyById]	
	@id int,
	@name varchar(150),
	@isActive bit,
	@lastUpdatedBy int
AS
BEGIN		

	UPDATE [dbo].[Company] SET [Name] = @name, IsActive = @isActive, LastUpdatedBy = @lastUpdatedBy, LastUpdatedDate = GETDATE()
		WHERE id = @Id	
		
END;

CREATE PROCEDURE [dbo].[UpdateJob]    
	@Id INT,
	@LocationId INT,
	@Name VARCHAR(150),
	@HexColor VARCHAR(6),
	@LastUpdatedBy INT,
    @UserId INT,
    @WorkCenterIdsCsv varchar(50),
    @SkillIdsCsv varchar(100)
AS
BEGIN	
    
    Declare @JobCount INT
    DECLARE @UserCompanyId INT

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

    -- check if another job with same name exists
    Set @JobCount = 
        (select count(*) from Job 
            where 
            CompanyId=(select CompanyId from Job where Id=@Id)
            and lower(trim(Name))=lower(trim(@Name))
            and Id<>@Id)

    if @JobCount > 0
    BEGIN
        throw 5000,'SP.ERROR.UpdateJob.JobNameAlreadyExists',1
    END

    -- start transaction
    BEGIN TRY
	  BEGIN TRANSACTION;

        -- update job table record
        UPDATE [dbo].[Job]	
        SET 
            [Name] = @Name, 
            HexColor = @HexColor, 
		    LastUpdatedBy = @LastUpdatedBy, 
            LastUpdatedDate = GETDATE()
	    WHERE 
            Id = @Id				
            and CompanyId=@UserCompanyId

        -- if job row is updated
        if @@ROWCOUNT > 0
        BEGIN
            -- get user related work center Ids
            Declare @UserWorkCenterIds Table(Id int)
            Insert into @UserWorkCenterIds
            select items from dbo.Split(dbo.udfUserWorkCenters(@UserId),',')
            
            -- delete job-workcenter records from jobworkcenter
            delete from JobWorkcenter
            WHERE
                JobId=@Id
                and WorkcenterId in (select Id from @UserWorkCenterIds)

            -- insert into JobWorkCenter new workcenterids CSV but only those which is associated with user
            INSERT into JobWorkCenter(JobId,WorkCenterId,CreatedBy,CreationDate,LastUpdatedBy,LastUpdatedDate)
            select @Id,items,@LastUpdatedBy,GETDATE(),@LastUpdatedBy,GETDATE() from dbo.Split(@WorkCenterIdsCsv,',')
                where 
                items in (select Id from @UserWorkCenterIds)

            -- 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 = @Id 
            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 = @Id 
            and SkillId in (select 
                            SkillId 
                            from @SkillIds)

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

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

        insert into @Workers
        SELECT wj.WorkerId from WorkerJob wj where wj.JobId=@Id

        insert into @Skills
        SELECT js.SkillId from JobSkill js where js.JobId=@Id and js.IsActive=1

        ;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
        

      COMMIT TRAN 
	END TRY
	BEGIN CATCH 
		  IF @@TRANCOUNT > 0 ROLLBACK 
	END CATCH
	
	
    -- select job to return
	SELECT Id, [Name], HexColor, IsActive, (select STRING_AGG(jw.WorkCenterId,',') 
                                            from JobWorkCenter jw 
                                            where jw.JobId=@Id
                                            and jw.WorkcenterId in (select Id from @UserWorkCenterIds)) WorkCenterIds 
    FROM Job WHERE Id = @Id			
		
END;

CREATE PROCEDURE [dbo].[UpdateRefreshToken]	
	@id int,
	@refreshToken [varchar](50)
AS
BEGIN	
	DECLARE @loginId INT;
	SET @loginId = (SELECT LoginId FROM [User] WHERE [id] = @id);
	
			UPDATE [dbo].[Login] SET 
				[RefreshToken] = @refreshtoken,
				[LastUpdatedBy] = @id,
				[LastUpdatedDate] = GETDATE()
			WHERE id = @loginId		
END;

CREATE PROCEDURE [dbo].[UpdateShiftById]	
	@id int,
	@startDateTime datetimeoffset,
	@endDateTime datetimeoffset,
	@jobId [int],
	@workCenterId [int],
	@numWorkersNeeded [smallint],
	@shiftPremium [varchar](50) ,
	@lastUpdatedBy [int]
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=@jobId)

    IF DATEDIFF(MI,@startDateTime,@endDateTime)/60.0 < @MinShiftDuration
        or DATEDIFF(MI,@startDateTime,@endDateTime)/60.0 > @MaxShiftDuration
    BEGIN

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

        THROW 50001, @ErrorMsg , 1

    END

    DECLARE @JobWorkCenterCount INT 

    -- check if job-workcenter relation exists
    set @JobWorkCenterCount = 
        (select count(*) from JobWorkcenter
            where
            CONCAT(JobId,'-',WorkCenterId) = CONCAT(@jobId,'-',@workCenterId)
        )

    if @JobWorkCenterCount = 0
    BEGIN
        THROW 50000,'GATError.SP.CreateShifts.NoJobWorkCenterExists',1
    END

	-- get shift published status 
	Declare @IsPublished BIT
	SET @IsPublished = (select IsPublished from Shift where Id=@Id)

	if @IsPublished = 1 -- if shift is already published
	Begin

		-- get shift accepted worker count
		DECLARE @AcceptedWorkerCount INT = 0
		Set @AcceptedWorkerCount = (
			Select count(*) 
			from 
				WorkerShift 
			where 
				ShiftId=@id
				and Status='A')

		-- throw error if workers needed are less than accepted workers
		if @numWorkersNeeded < @AcceptedWorkerCount
		Begin
			Throw 50000,'Error.SP.UpdateShiftById.WorkersNeededCannotBeLessThanAccepted',1
			Return
		End

		-- update only number of required workers
		UPDATE [dbo].[Shift] SET 			
			[StartDateTime] = [StartDateTime],
			[EndDateTime] = [EndDateTime],
			[JobId] = [JobId],
			[WorkCenterId] = [WorkCenterId],
			[NumWorkersNeeded] = @numWorkersNeeded,
			[ShiftPremium] = [ShiftPremium],
			[LastUpdatedBy] = @lastUpdatedBy,
			[LastUpdatedDate] = GETDATE()
		WHERE id = @Id				
	End
	else	-- if shift is not published yet
	Begin

		-- update all incomming columns
		UPDATE [dbo].[Shift] SET 
			[StartDateTime] = @startDateTime,
			[EndDateTime] = @endDateTime,
			[JobId] = @jobId,
			[WorkCenterId] = @workCenterId,
			[NumWorkersNeeded] = @numWorkersNeeded,
			[ShiftPremium] = @shiftPremium,
			[LastUpdatedBy] = @lastUpdatedBy,
			[LastUpdatedDate] = GETDATE()
		WHERE id = @Id		

	End
	
	-- return the updated shift
	select * from [Shift] where Id=@id

END;

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

    Declare @SkillCount INT
    DECLARE @UserCompanyId INT

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

    -- check if the skill name already exists    
    Set @SkillCount = 
        (select count(*)
            from Skill
            where 
            lower(Name) = LOWER(@Name) 
            and CompanyId=@UserCompanyId
            and Id<>@Id)

    if @SkillCount > 0
    BEGIN
        throw 50000,'GATError.UpdateSkill.SkillNameAlreadyExists',1
    END

    -- update the skill
    UPDATE Skill
    SET Name = @Name, LastUpdatedBy=@UserId, LastUpdatedDate=GETDATE()
    where Id=@Id
   
    -- return updated skill
    SELECT * from Skill where Id=@Id
    

END;

CREATE   PROCEDURE [dbo].[UpdateSkillInterest]
    @Status CHAR(1),
    @UserId INT,
    @Id INT,
    @WorkerId UNIQUEIDENTIFIER
AS
BEGIN

    DECLARE @SkillId INT
    DECLARE @LevelId INT    
    
    SELECT
        @SkillId=SkillId,
        @LevelId=SkillLevelId
    FROM WorkerSkillInterest
    where Id=@Id

    -- if it is worker updating
    if @UserId = 0
    BEGIN

        -- update skill interest status by worker
        Update WorkerSkillInterest 
        set 
            Status=@Status, 
            LastUpdatedBy=@UserId,
            LastUpdatedDate=GETDATE() 
        where 
            Id=@Id and 
            WorkerId=@WorkerId

    END
    ELSE
    BEGIN

        SET @WorkerId = (select WorkerId from WorkerSkillInterest where Id=@Id)

        -- check if userid and skill interest worker belong to same company
        DECLARE @WorkerCount INT = 0
        SELECT 
            @WorkerCount = COUNT(*)
        from CompanyWorker cw
        WHERE
            WorkerId = @WorkerId
            and CompanyId = (select CompanyId from [User] where Id = @UserId)

        -- if above is false throw error
        IF @WorkerCount = 0
        BEGIN
            THROW 50000, 'GATError.NotAuthorized', 1
        END
       
            
        -- update skill interest with new status
        Update WorkerSkillInterest 
        set 
            Status=@Status, 
            LastUpdatedBy=@UserId,
            LastUpdatedDate=GETDATE() 
        where 
            Id=@Id

        -- if status is 'A' (accepted)
        IF @Status = 'A'
        BEGIN

            -- update worker skill level if it exists
            UPDATE WorkerSkill
            SET
                SkillLevelId = @LevelId,
                LastUpdatedBy = @UserId,
                LastUpdatedDate = GETDATE()
            WHERE
                WorkerId=@WorkerId
                and SkillId = @SkillId

            IF @@ROWCOUNT = 0
            BEGIN
                -- create new worker skill with given level
                INSERT INTO WorkerSkill
                    select 
                        @WorkerId,
                        SkillId,SkillLevelId,
                        @UserId ,GETDATE(), @UserId, GETDATE()
                    from WorkerSkillInterest 
                    where Id=@Id

            END
        END
    END

    SELECT 
        wsi.Id,wsi.WorkerId,wsi.SkillId,wsi.SkillLevelId,wsi.[Status],
        w.FirstName, w.MobilePhoneNumber, s.Name SkillName,
        (select STRING_AGG(wdt.devicetoken,',')
            from WorkerDeviceToken wdt
            where wdt.WorkerId=wsi.WorkerId and wdt.IsActive=1) DeviceTokens
    from WorkerSkillInterest wsi
    INNER JOIN Worker w on wsi.WorkerId=w.Id    
    INNER JOIN Skill s on wsi.SkillId=s.Id
    WHERE
    wsi.Id=@Id

END;

CREATE PROCEDURE [dbo].[UpdateWorkerById]
	@Id VARCHAR(50),
	@FirstName VARCHAR(50),
	@MiddleName VARCHAR(50),
	@LastName VARCHAR(50),
	@Email NVARCHAR(100),
	@MobilePhoneNumber VARCHAR(20),	
	@WorkerTypeId TINYINT,
	@CompanyEmployeeId VARCHAR(100),
	@JobId VARCHAR(MAX),
	@LastUpdatedBy INT,
    @Skills ListOfWorkerSkills READONLY,
    @WorkerJobs ListOfWorkerJobs READONLY
AS
BEGIN	

	DECLARE @MobileExist BIT = 0
    DECLARE @OldJobIds TABLE(JobId INT)

	-- check if new mobile exists with another worker
	Select @MobileExist = (case when count(Id) > 0 then 1 else 0 end) 
		from Worker 
			where MobilePhoneNumber=@MobilePhoneNumber and
			Id <> @Id;	

	-- if new mobile already exists then raise error
	if @MobileExist = 'true'
	begin
		RAISERROR(N'Error.SP.UpdateWorkerById.MobileAlreadyInUse',16,1)
	end

    --saving information about worker expiry, it will be needed for sending notifications for updated records
    DECLARE @WorkerJobExpiryStatus TABLE (
    JobId INT NOT NULL,
    IsExpired BIT NOT NULL);

    INSERT INTO @WorkerJobExpiryStatus
    SELECT wj.JobId, wj.IsExpired
    FROM dbo.WorkerJob wj
    WHERE wj.WorkerId = @Id

	if @MobileExist = 'false'
	begin
	 BEGIN TRY
	  BEGIN TRANSACTION;
		UPDATE Worker SET 
			FirstName = @FirstName, 
			MiddleName = @MiddleName,
			LastName = @LastName,
			Email = @Email,
			MobilePhoneNumber = @MobilePhoneNumber,
			LastUpdatedBy = @LastUpdatedBy,
			LastUpdatedDate = GETDATE()
				WHERE Id = @Id

		UPDATE CompanyWorker SET
			WorkerTypeId = @WorkerTypeId,
			CompanyEmployeeId = @CompanyEmployeeId,
			LastUpdatedBy = @LastUpdatedBy,
			LastUpdatedDate = GETDATE()
				WHERE WorkerId = @Id

        -- save worker old jobids for later comparison
        INSERT into @OldJobIds
		SELECT JobId 
        FROM 
            WorkerJob 
        WHERE WorkerId = @Id 
            and JobId in (select 
								jw.jobid 
							from 
								JobWorkCenter jw where 
								jw.WorkcenterId in
								(	
								SELECT 
									items 
								FROM 
									dbo.Split(dbo.udfUserWorkCenters(@LastUpdatedBy),',')
								)
							)
				
		--delete worker jobs and insert them again
        DELETE from WorkerJob
        where WorkerId = @Id 
            and JobId in (select JobId from @OldJobIds)


		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
				   (@Id, @jId, @LastUpdatedBy, GETDATE(), @LastUpdatedBy, GETDATE())
			SET @i = (@i + 1)
		END

        --update worker Job for job expiry based on the job id, worker id and isExpired values received from the UI
        UPDATE
            wj
        SET
            wj.IsExpired = wjs.IsExpired,
            wj.IsExpiredBy = @LastUpdatedBy,
            wj.IsExpiredDate = GETDATE()
        FROM
            dbo.WorkerJob wj
        INNER JOIN
            @WorkerJobs wjs
        ON 
            wj.JobId = wjs.JobId
        WHERE
	        wj.WorkerId = @Id;


		----- START : DELETE removed skills    
        -- fetch skills to be removed for notification purpose
        DECLARE @RemovedSkills TABLE(Id INT, SkillId INT, LevelId INT)
        INSERT INTO @RemovedSkills
        SELECT Id, SkillId, SkillLevelId
        from workerskill 
        where 
        workerid=@Id
        and skillid in (
                (select Id from Skill where companyId = (select CompanyId from [User] where Id=@LastUpdatedBy))
                EXCEPT
                (select SkillId from @Skills)
            );

        delete from workerskill 
        where 
        Id in (select Id from @RemovedSkills)

        ----- END : DELETE removed skills    

        ---- START : update levels for existing skills if they are different
        -- fetch skills where level is updated
        DECLARE @UpdatedSkills TABLE(Id INT, SkillId INT,LevelId INT)
        INSERT INTO @UpdatedSkills
        Select 
            ws.Id, ws.SkillId, s.LevelId
        from workerskill ws
        inner join @Skills s on ws.skillid=s.skillid and ws.skilllevelid<>s.levelid
        where 
        workerid=@Id

        Update ws
        set ws.skilllevelid=s.levelid,ws.LastUpdatedBy=@LastUpdatedBy,ws.LastUpdatedDate=getdate()
        from workerskill ws
        inner join @UpdatedSkills s on ws.Id = s.Id        
        ---- END : update levels for existing skills if they are different

        ---- START : insert skill that doesn't exist
        -- fetch new skills added
        DECLARE @AddedSkills TABLE(SkillId INT,LevelId INT)
        INSERT INTO @AddedSkills
        SELECT skillid,levelid
        from @skills
        WHERE 
        skillid not in (select skillid from workerskill where workerid=@Id)
        --and skillid not in (select skillid from jobskill where jobid in (select items from dbo.split(@JobId,',')))

        INSERT into workerskill
        SELECT @Id,skillid,levelid,@LastUpdatedBy,GETDATE(),@LastUpdatedBy,GETDATE()
        from @AddedSkills
        ---- END : insert skill that doesn't exist
	
	  COMMIT TRAN 
        
        -- Table[0]: return worker details
        SELECT w.[Id], w.FirstName, w.MiddleName, w.LastName, w.Email, w.MobilePhoneNumber as MobilePhoneNumber, 
	        '' as [Type], '' as [CompanyName], '' AS JobIds,
            (select STRING_AGG(wdt.devicetoken,',')
            from WorkerDeviceToken wdt
            where wdt.WorkerId=w.Id and wdt.IsActive=1) DeviceTokens
		FROM Worker w 
        WHERE w.Id = @Id

        -- get new added jobids (if any)
        DECLARE @NewlyAddedJobIds TABLE(JobId INT)

        INSERT into @NewlyAddedJobIds
        SELECT JobId from @JobIds 
        EXCEPT
        SELECT JobId from @OldJobIds

        -- get removed jobids (if any)
        DECLARE @RemovedJobIds TABLE(JobId INT)

        INSERT into @RemovedJobIds
        SELECT JobId from @OldJobIds
        EXCEPT
        SELECT JobId from @JobIds

        -- Table[1]: return newly added jobids count
        SELECT COUNT(JobId) as JobIdsCount from @NewlyAddedJobIds

        -- Table[2]: return newly added job name csv
        SELECT STRING_AGG([Name],',') as JobNames from Job WHERE Id in (select JobId from @NewlyAddedJobIds)

        -- Table[3]: return removed job name csv
        SELECT STRING_AGG([Name],',') as RemovedJobNames from Job WHERE Id in (select JobId from @RemovedJobIds)

        -- Table[4]: return future shift Ids for the new JobIds added to the worker
        SELECT s.Id from Shift s 
        WHERE s.JobId in (Select JobId from @NewlyAddedJobIds)
        AND s.StartDateTime > GETDATE()
        AND s.IsActive = 1
        AND s.IsPublished = 1

		-- Table[5]: return skill updates
        ;with cte AS (
            SELECT SkillId,LevelId,'u' [Action] from @UpdatedSkills
            UNION ALL
            Select SkillId,LevelId,'a' [Action] from @AddedSkills where LevelId not in (select Id from SkillLevel where Seq=0)
            UNION ALL
            Select SkillId,LevelId,'r' [Action] from @RemovedSkills
        )
        SELECT 
            SkillId,LevelId,[Action],
            s.NAME SkillName, sl.Name LevelName
        from cte
        INNER JOIN Skill s on cte.SkillId=s.Id
        INNER JOIN SkillLevel sl on cte.LevelId=sl.Id

        -- Table[6]: return Worker Jobs that were updated for expiry status (filled only for the updated records, not-updated records are skipped for notification)
		SELECT j.[Name] as JobName, c.[Name] as CompanyName, wj.IsExpired
        FROM dbo.workerjob wj
		INNER JOIN @WorkerJobExpiryStatus wjs on wj.jobId = wjs.JobId
        INNER JOIN dbo.Job j on j.Id = wjs.JobId
        INNER JOIN dbo.Company c on j.CompanyId = c.Id
		WHERE wj.WorkerId = @Id and wj.IsExpired <> wjs.IsExpired

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

CREATE   PROCEDURE [dbo].[UpdateWorkerDeviceToken]
    @WorkerId UNIQUEIDENTIFIER,
    @DeviceToken VARCHAR(255),
    @Platform VARCHAR(255),
    @DeviceId VARCHAR(255)
AS
BEGIN

    -- if the token exists with another workerid(s) then make them all inactive
    UPDATE 
        WorkerDeviceToken
    SET 
        IsActive = 0
    WHERE        
        (DeviceId = @DeviceId
        and WorkerId <> @WorkerId)
        OR
        (WorkerId=@WorkerId)

    -- update last updated date if the token already exists
    UPDATE 
        WorkerDeviceToken
    SET
        DeviceToken = @DeviceToken,
        Platform = @Platform,
        LastUpdatedDate = GETDATE(),
        IsActive = 1
    WHERE
        WorkerId = @WorkerId
        and DeviceId = @DeviceId

    -- insert the new token if it doesnt exists
    if @@ROWCOUNT = 0 
    BEGIN

        INSERT INTO
            WorkerDeviceToken(WorkerId,DeviceId,DeviceToken,Platform,IsActive,creationdate,LastUpdatedDate)
        VALUES(@WorkerId,@DeviceId,@DeviceToken,@Platform,1,GETDATE(),GETDATE())

    END


END;

CREATE   PROCEDURE [dbo].[UpdateWorkerJobExpiry]
AS
BEGIN

    DECLARE @UpdatedWorkerJobs INT = 0
    DECLARE @ActiveWorkerJobIds TABLE (WorkerJobId INT)

    -- step1 : get all workerjobs ids which were not expired in @ActiveWorkerJobs
    Insert into @ActiveWorkerJobIds
    SELECT Id from WorkerJob WHERE IsExpired = 0

    -- step2 : update worker job expiry 
    UPDATE wj
    SET 
        wj.IsExpired = e.IsExpired, 
        wj.IsExpiredBy = e.IsExpiredBy,
        wj.IsExpiredDate = e.IsExpiredDate
    FROM WorkerJob wj 
    CROSS APPLY dbo.udfGetWorkerJobExpiry(wj.WorkerId, wj.JobId, wj.CreationDate, wj.IsExpired, wj.IsExpiredBy, wj.IsExpiredDate) e

    SET @UpdatedWorkerJobs = @@ROWCOUNT

    -- step3 : get all worker jobs in @ActiveWorkerJobs which are now expired with worker details
    -- table[0] get worker job which are expred (with worker, job and company details)
    SELECT 
    wj.Id, wj.WorkerId, wj.JobId, wj.IsExpired,
    (
        select 
            DATEDIFF(DAY,max(s.StartDateTime),GETDATE())            
            --max(ws.ShiftId)
        from WorkerShift ws 
        INNER join shift s on ws.ShiftId=s.Id
        where ws.WorkerId=wj.WorkerId 
        and ws.[Status]='A' and ws.ATTEND_STATUS<>'a'
        and s.JobId=wj.JobId and s.IsActive=1 and s.EndDateTime<GETDATE()
        --order by s.StartDateTime desc
    ) DaysLastShift,
    w.FirstName, w.LastName, w.MobilePhoneNumber,
    j.Name as JobName, j.MinWorkHrsDays JobMinWorkHrsDays,
    (select [Name] from Company where Id=j.CompanyId) CompanyName,
    (select STRING_AGG(wdt.devicetoken,',')
        from WorkerDeviceToken wdt
        where wdt.WorkerId=w.Id and wdt.IsActive=1) DeviceTokens,
    (
	    SELECT 
			CASE 
	           WHEN dbo.udfGetWorkerSetting(wj.WorkerId,(SELECT Id from WorkerSettingType where Code='SmsNotifications')) = 'true' 
	           THEN cast(1 as BIT) 
	           ELSE cast(0 as BIT)
	    END
    ) IsWorkerSmsEnabled        
    from WorkerJob wj
    INNER JOIN Worker w on wj.WorkerId=w.Id
    INNER JOIN Job j on wj.JobId=j.Id
    WHERE 
    wj.Id in (select WorkerJobId from @ActiveWorkerJobIds)
    and wj.IsExpired = 1

END;

CREATE   PROCEDURE [dbo].[UpdateWorkerJobExpiryByUser]
    @WorkerId UNIQUEIDENTIFIER,
    @JobId INT,
    @IsExpired BIT,
    @UserId INT
AS
BEGIN

    DECLARE @WorkerJobCount INT = 0
    DECLARE @UserCompanyId INT = 0

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

    -- 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(@UserId),',')
                        )

    -- check if worker and job is associated to the user
    Set @WorkerJobCount = 
        (
            select count(*) 
            from WorkerJob wj
            INNER join CompanyWorker cw on wj.workerid=cw.WorkerId and cw.CompanyId=@UserCompanyId and cw.IsActive=1
            WHERE
            wj.workerid=@WorkerId
            and wj.JobId = @JobId
            and wj.JobId in (select JobId from @UserJobIds)
        )

    -- if worker and job not associated with user
    if @WorkerJobCount = 0
    BEGIN
        THROW 50000, 'GATError.NotAuthorized', 1
    END

    -- check if worker job expiry status is same as @IsExpired
    DECLARE @RowCount INT = 0
    SET @RowCount = 
        (
            select count(*) 
            from WorkerJob
            where WorkerId=@WorkerId
            and JobId = @JobId
            and IsExpired = @IsExpired
        )

    IF @RowCount > 0
    BEGIN
        THROW 50000, 'GATError.WorkerJobExpiry.AlreadyUpdated', 1
    END
    

    -- update worker jjob
    update WorkerJob
    SET 
    IsExpired = @IsExpired,
    IsExpiredBy = @UserId,
    IsExpiredDate = GETDATE()
    WHERE
    WorkerId=@WorkerId
    and JobId=@JobId

    -- return worker job with other details for notification
    SELECT 
        wj.WorkerId,wj.JobId,j.Name JobName, w.FirstName, w.MobilePhoneNumber, wj.IsExpired,
        (select [Name] from Company where Id=@UserCompanyId) CompanyName,
        (select STRING_AGG(wdt.devicetoken,',')
            from WorkerDeviceToken wdt
            where wdt.WorkerId=w.Id and wdt.IsActive=1) DeviceTokens        
    from WorkerJob wj
    INNER JOIN Job j on wj.JobId=j.Id
    INNER JOIN Worker w on wj.WorkerId=w.Id
    WHERE
    wj.WorkerId=@WorkerId
    AND wj.JobId=@JobId


END;

CREATE       PROCEDURE [dbo].[UpdateWorkerPassCode]
    @WorkerId UNIQUEIDENTIFIER,
    @PassCode VARCHAR(10)
AS
BEGIN

    Update Worker
    SET
        PassCode = @PassCode,
        PassCodeLastUpdated = GETDATE()
    WHERE
        Id = @WorkerId

    
    SELECT 
        Id, PassCode, RefreshToken 
    from 
        Worker 
    where 
        Id=@WorkerId

END;

create   PROCEDURE [dbo].[UpdateWorkerRefreshToken]
    @WorkerId VARCHAR(50),
    @RefreshToken VARCHAR(100)
AS
BEGIN

    Update Worker 
    SET
    RefreshToken = @RefreshToken,
    RefreshTokenLastUpdated = GETDATE()
    WHERE
    Id = @WorkerId

END;

CREATE   PROCEDURE [dbo].[UpdateWorkerScores]
WITH RECOMPILE
AS
BEGIN

BEGIN TRAN
BEGIN TRY
    DECLARE @UpdatedWorkers INT = 0
    DECLARE @UpdatedWorkerJobs INT = 0

    DECLARE @WorkerScoreItem TABLE(
        WorkerId UNIQUEIDENTIFIER,
        WorkerShiftId INT,
        WorkerShiftStatus CHAR(1),
        WorkerShiftIsAbsent Bit,
        WorkerShiftIsTardy Bit,
        InvalidShiftCancelPenalty FLOAT,
        ShiftStart DateTimeOffset,
        ShiftEnd DateTimeOffset,
        ShiftJobId INT,
        ShiftJobName NVARCHAR(100),
        WorkerShiftLastUpdated DATETIME2,
        LastUpdatedDate DATETIME2
    )


    DECLARE @WeeksTable Table(WorkerId UNIQUEIDENTIFIER, sdt DATETIME, edt DATETIME, wk INT,hrs FLOAT,LastUpdatedDate DATETIME2)    
   

    -- calculate and insert worker latest score items records
    ;with cte as (
    select w.Id, wsc.*, GETDATE() ldt
    from worker w CROSS APPLY dbo.udfGetWorkerScoreItems(w.id,GETDATE()) wsc
    )
    INSERT INTO @WorkerScoreItem
    select * from cte

    --delete all reliability score items records
    delete from WorkerScoreItem

    INSERT into WorkerScoreItem
        (workerid,WorkerShiftId,WorkerShiftStatus,WorkerShiftIsAbsent,WorkerShiftIsTardy,InvalidShiftCancelPenalty,ShiftStart,ShiftEnd,ShiftJobId,ShiftJobName,WorkerShiftLastUpdated,LastUpdatedDate)
    SELECT * from @WorkerScoreItem

    -- calculate and insert worker latest SAR weekly hrs records    
    INSERT INTO @WeeksTable
    select w.Id, wwh.*, GETDATE()
    from Worker w CROSS APPLY dbo.udfGetWorkerSARWkHrs(w.Id) wwh

    -- delete all SAR weekly records
    delete from WorkerSARWkHrs

    INSERT into WorkerSARWkHrs
        (WorkerId,WkStartDateTime,WkEndDateTime,WkSeq,CompletedHrs,LastUpdatedDate)
    SELECT * from @WeeksTable

    -- update worker table with rscore, exp level, SAR values
    UPDATE
        Worker
    set 
        rscore=dbo.udfReliabilityScore(Id,getdate()),
        rscorelastupdated=GETDATE(),
        TotalCompletedHours=(select CompletedHours from dbo.udfGetWorkerExpLevel(Id,GETDATE())),
        ExpLevelId=(select ExpLevelId from dbo.udfGetWorkerExpLevel(Id,GETDATE())),
        ExpLevelIdLastUpdated=GETDATE(),
        SAR = ISNULL(dbo.udfShiftAcceptanceRate(Id),0),
        SARLastUpdated = GETDATE()

    -- -- update worker tier info based on rscore, exp level, SAR values
    UPDATE
        Worker
    set         
        TierSeq = dbo.udfWorkerTier(Id)

    SET @UpdatedWorkers = @@ROWCOUNT
    
    COMMIT TRANSACTION

    SELECT @UpdatedWorkers as WorkerCount, @UpdatedWorkerJobs as WorkerJobCount

END TRY
BEGIN CATCH
        -- if error, roll back any chanegs done by any of the sql statements
        ROLLBACK TRANSACTION                
        THROW;
END CATCH
    
END;

CREATE   PROCEDURE [dbo].[UpdateWorkerSetting]
    @WorkerId UNIQUEIDENTIFIER,
    @TypeId BIGINT,
    @Val  NVARCHAR(255)
AS
BEGIN

    -- update the worker setting
    UPDATE
        WorkerSettings
    SET
        [Value]=@Val,
        LastUpdatedDate=GETDATE(),
        LastUpdatedBy=1

    WHERE
        WorkerId=@WorkerId
        and WorkerSettingTypeId=@TypeId

    -- if worker setting doesnt exist then 
    IF @@ROWCOUNT = 0
    BEGIN

        INSERT INTO 
            WorkerSettings(WorkerId,WorkerSettingTypeId,[Value],CreationDate,CreatedBy,LastUpdatedDate,LastUpdatedBy)
        VALUES
            (@WorkerId,@TypeId,@Val,GETDATE(),1,GETDATE(),1)

    END

END;

-- =============================================
-- Author:		Rayees Afroz
-- Create date: 2022-06-27
-- Description:	Mark a workershift for a worker as absent or present
-- =============================================
CREATE   PROCEDURE [dbo].[UpdateWorkerShiftAttend] 
	-- Add the parameters for the stored procedure here
	@WorkerId uniqueidentifier,
	@ShiftId int,
	@AttendStatus char(1),
    @UserId INT,
	@Comment NVARCHAR(max)

AS
BEGIN
	
    -- check if the user is authorized
    DECLARE @SId BIGINT = 0
    SET @SId =
        (SELECT s.Id from Shift s 
            where s.Id=@ShiftId
            and s.JobId in (select JobId 
                            from JobWorkcenter 
                            INNER join Job on JobWorkcenter.JobId=Job.Id 
                            where Job.IsActive = 1 
                            AND WorkCenterId IN (select items from dbo.Split(dbo.udfUserWorkCenters(@UserId),','))
                            )
        )

    IF @SId = 0
    Begin
		Throw 50000,'GATError.NotAuthorized',1
	End

    -- check if the shift is in future
	DECLARE @ShiftStartDateTime DateTimeOffset
	set @ShiftStartDateTime = (Select StartDateTime from Shift where Id=@ShiftId)	
	if @ShiftStartDateTime > GETDATE()
	Begin
		Throw 50000,'GATError.SP.UpdateWorkerShiftAbsent.FutureShift',1
	End

    -- fetch current worker shift attend status
    DECLARE @CurrAttendStatus char(1)
    SET @CurrAttendStatus = 
        (select attend_status from workershift where WorkerId=@WorkerId	and ShiftId=@ShiftId and Status = 'A')

    -- update new status
	update WorkerShift 
	set
		attend_status=@AttendStatus,
        LastUpdatedBy=@UserId,
        LastUpdatedDate=GETDATE(),
		Comment = @Comment
	where
		WorkerId=@WorkerId
		and ShiftId=@ShiftId
		and Status = 'A'

    SELECT ws.*,@CurrAttendStatus as CurrAttendStatus
    from WorkerShift ws
    where
		ws.WorkerId=@WorkerId
		and ws.ShiftId=@ShiftId
		and ws.Status = 'A'

END;

CREATE PROCEDURE [dbo].[VerifyTokenAndUpdatePassword]
	@loginId INT,
	@token NVARCHAR(100),
	@password NVARCHAR(100),
	@salt NVARCHAR(100)
AS
BEGIN

	DECLARE @passwordResetExpiration DATETIME2(2)
	SET @passwordResetExpiration = (SELECT PasswordResetExpiration FROM [Login] WHERE Id = @loginId)

	IF (@passwordResetExpiration >= GETDATE())
	BEGIN
		UPDATE [Login] SET [Password] = @password, SaltValue = @salt, PasswordResetExpiration = GETDATE() WHERE Id = @loginId		
	END

END;

CREATE PROCEDURE [dbo].[WorkerShiftSignup]	
	@workerId VARCHAR(150),
	@shiftId INT,
	@status CHAR(1)
AS
BEGIN
	
	DECLARE @MinutesLeftForWorker INT
	DECLARE @MinutesOfShift INT
	DECLARE @ShiftStart datetime
	DECLARE @ShiftEnd datetime
	DECLARE @ShiftOverlapCount INT	
	
	
	
	IF @status = 'A'
	BEGIN

		DECLARE @ShiftWeekStartDateTime DateTimeOffSet;
		DECLARE @ShiftWeekEndDateTime DateTimeOffSet;		

        -- check if the worker job is expired
        DECLARE @IsWorkerJobExpired BIT = 0
        SET @IsWorkerJobExpired = 
            (
                SELECT IsExpired from WorkerJob 
                where 
                WorkerId = @workerId 
                and JobId = (Select JobId from Shift where Id=@shiftId)
            )

        IF @IsWorkerJobExpired = 1
        BEGIN
            THROW 50000, 'Error.SP.WorkerShiftSignup.WorkerJobExpired',1
        END


		-- get start and end time of the signup shift
		Select @ShiftStart=StartDateTime, @ShiftEnd=EndDateTime  from Shift where Id=@shiftId;

		-- find if there exist a worker shift that overlaps
		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 company continious shift signup policy         
        DECLARE @ContiniousShiftSignupCheck BIT = 
            dbo.udfCheckCompanyContiniousShiftSignup(@workerId,@shiftId)

        IF @ContiniousShiftSignupCheck = 0
        BEGIN
            RAISERROR(N'Error.SP.WorkerShiftSignup.ExceedingContiniousSignUpLimit',16,1)
            RETURN
        END


		-- EVERYTHING OK : PERFORM THE TRANSACTION
		SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
		BEGIN TRANSACTION;
			DECLARE @slotsFilled INT
			DECLARE @numOfWorkersNeeded INT
			SET @slotsFilled = (SELECT COUNT(*) FROM [dbo].[WorkerShift] WHERE [Status] = 'A' AND [ShiftId] = @shiftId)
			SET @numOfWorkersNeeded = (SELECT [NumWorkersNeeded] FROM [dbo].[Shift] WHERE [Id] = @shiftId)
			IF(@slotsFilled < @numOfWorkersNeeded)
			BEGIN							
				DECLARE @ShiftPremium VARCHAR(50)
				SET @ShiftPremium = (SELECT ShiftPremium FROM [Shift] WHERE Id = @shiftId)
				UPDATE [dbo].[WorkerShift] SET [Status] = @status, LastUpdatedDate = GETDATE(), ShiftPremium = @ShiftPremium
						WHERE WorkerId = @workerId AND ShiftId = @shiftId;
				IF @@ROWCOUNT = 0
					INSERT INTO [dbo].[WorkerShift] 
						([WorkerId],[ShiftId],[Status],[ShiftPremium],[CreatedBy],[CreationDate],[LastUpdatedBy],[LastUpdatedDate]) 
						VALUES (@workerId, @shiftId, @status, @ShiftPremium, 1, GETDATE(), 1, GETDATE())																				
			END
			ELSE
			BEGIN
				RAISERROR(N'Error.SP.WorkerShiftSignup.ShiftFull',16,1)		
				RETURN
			END
		COMMIT TRANSACTION;
	END	
	ELSE -- status = 'D' or 'C'
	BEGIN
		SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
		BEGIN TRANSACTION;
			DECLARE @premium VARCHAR(50)
			SET @premium = (SELECT ShiftPremium FROM [Shift] WHERE Id = @shiftId)							
			UPDATE [dbo].[WorkerShift] SET [Status] = @status, ShiftPremium = @premium, LastUpdatedDate = GETDATE() WHERE WorkerId = @workerId AND ShiftId = @shiftId;
			IF @@ROWCOUNT = 0
			BEGIN
				INSERT INTO [dbo].[WorkerShift]
						([WorkerId],[ShiftId],[Status],[ShiftPremium],[CreatedBy],[CreationDate],[LastUpdatedBy],[LastUpdatedDate])
						VALUES (@workerId, @shiftId, @status, @premium, 1, GETDATE(), 1, GETDATE())
			END
		COMMIT TRANSACTION;
	END
END;

CREATE PROCEDURE [dbo].[WorkersInShift]	
	@ShiftId int
AS
BEGIN		

DECLARE @tblWorkerIdResponse TABLE (Id UNIQUEIDENTIFIER NOT NULL)
DECLARE @tblWorkerIdNotification TABLE (Id UNIQUEIDENTIFIER NOT NULL)
DECLARE @companyId INT

-- get companyid from shiftid to use in following query
	set @companyId = (select l.CompanyId from Shift s						
				inner join WorkCenter wc on s.WorkCenterId=wc.Id
				inner join location l on wc.LocationId=l.id
				where s.Id=@shiftid)

--Accepted								
SELECT * FROM (
SELECT w.Id, FirstName+' '+LastName AS WorkerName , ws.LastUpdatedDate
      ,ROW_NUMBER() OVER (
                     PARTITION BY w.Id 
                     ORDER BY ws.LastUpdatedDate DESC
         	   ) AS [ROW NUMBER]
  FROM [WorkerShift] ws INNER JOIN Worker w ON ws.WorkerId = w.Id
  WHERE ws.ShiftId = @shiftId
  and ws.Status = 'A'
  ) groups
WHERE groups.[ROW NUMBER] = 1
ORDER BY groups.LastUpdatedDate DESC								
								
--Declined/Cancelled
SELECT * FROM (
SELECT w.Id, FirstName+' '+LastName AS WorkerName , ws.LastUpdatedDate
      ,ROW_NUMBER() OVER (
                     PARTITION BY w.Id 
                     ORDER BY ws.LastUpdatedDate DESC
         	   ) AS [ROW NUMBER]
  FROM [WorkerShift] ws INNER JOIN Worker w ON ws.WorkerId = w.Id
  WHERE ws.ShiftId = @shiftId
  and ws.Status in ('C','D')
  ) groups
WHERE groups.[ROW NUMBER] = 1
ORDER BY groups.LastUpdatedDate DESC								


--above worker ids with responses to be removed from below categories
INSERT INTO 
		@tblWorkerIdResponse(Id) 
	(SELECT 
			w.Id
		FROM 
			[WorkerShift] ws 
				INNER JOIN Worker w 
					ON ws.WorkerId = w.Id
						WHERE ws.ShiftId = @shiftId 
							AND [Status] IN  ('C','D','A'))	


--Viewed
SELECT w.Id, FirstName+' '+LastName AS WorkerName FROM Worker w INNER JOIN CompanyWorker cw 
	ON w.Id = cw.WorkerId and cw.companyid=@companyid INNER JOIN ShiftNotification sn ON sn.workerId = w.Id
		WHERE sn.[Status] = 'V' AND sn.shiftId = @ShiftId
			AND cw.IsActive = 1
			AND w.Id NOT IN (SELECT Id FROM @tblWorkerIdResponse)

			
--above worker ids with responses to be removed from below categories
INSERT INTO @tblWorkerIdNotification(Id) (SELECT w.Id FROM Worker w INNER JOIN CompanyWorker cw 
	ON w.Id = cw.WorkerId and cw.companyid=@companyid INNER JOIN ShiftNotification sn ON sn.workerId = w.Id
		WHERE sn.[Status] = 'V' AND sn.shiftId = @ShiftId
			AND cw.IsActive = 1
			AND w.Id NOT IN (SELECT Id FROM @tblWorkerIdResponse))			
			
--Notified and Renotified 
			
SELECT * FROM (
SELECT w.Id, FirstName+' '+LastName AS WorkerName , sn.LastUpdatedDate
      ,ROW_NUMBER() OVER (
                     PARTITION BY w.Id 
                     ORDER BY sn.LastUpdatedDate DESC
         	   ) AS [ROW NUMBER]
  FROM Worker w INNER JOIN CompanyWorker cw 
						ON w.Id = cw.WorkerId and cw.CompanyId=@companyId
				INNER JOIN ShiftNotification sn 
						ON sn.workerId = w.Id
		WHERE 
			sn.[Status] IN ('N','R') 
			AND sn.[Status] NOT IN ('V') 
			AND sn.shiftId = @ShiftId
			AND cw.IsActive = 1
			AND w.Id NOT IN (SELECT Id FROM @tblWorkerIdResponse)
			and w.Id NOT IN (select Id FROM @tblWorkerIdNotification)
			) groups
WHERE groups.[ROW NUMBER] = 1
ORDER BY groups.LastUpdatedDate DESC								


--above worker ids with responses to be removed from below categories
INSERT INTO @tblWorkerIdNotification(Id) (SELECT w.Id FROM Worker w INNER JOIN CompanyWorker cw 
	ON w.Id = cw.WorkerId and cw.CompanyId=@companyId INNER JOIN ShiftNotification sn ON sn.workerId = w.Id
		WHERE sn.[Status] IN ('N','R') AND sn.shiftId = @ShiftId
			AND cw.IsActive = 1
			AND w.Id NOT IN (SELECT Id FROM @tblWorkerIdResponse))


--None (new workers and failed SMS notifications)
SELECT w.Id, FirstName+' '+LastName AS WorkerName FROM Worker w 
	INNER JOIN CompanyWorker cw ON w.Id = cw.WorkerId 
	INNER JOIN WorkerJob wj on w.Id = wj.WorkerId
		WHERE w.CreationDate > (SELECT CreationDate FROM [Shift] WHERE Id = @ShiftId)
			AND wj.JobId = (select JobId from [Shift] where Id=@ShiftId)
			AND cw.IsActive = 1 
			AND w.Id NOT IN (SELECT Id FROM @tblWorkerIdResponse)
			AND w.Id NOT IN (SELECT Id FROM @tblWorkerIdNotification)
UNION
(
SELECT w.Id, FirstName+' '+LastName AS WorkerName FROM Worker w INNER JOIN CompanyWorker cw 
	ON w.Id = cw.WorkerId and cw.CompanyId=@companyId INNER JOIN WorkerJob wj ON wj.WorkerId = w.Id 
	INNER JOIN [Shift] s ON s.JobId = wj.JobId
		WHERE cw.IsActive = 1 AND s.Id = @ShiftId
EXCEPT
SELECT 	w.Id, FirstName+' '+LastName AS WorkerName FROM Worker w INNER JOIN [dbo].[ShiftNotification] sn 
	ON w.Id = sn.WorkerId WHERE sn.ShiftId = @ShiftId
)
END;