Untitled
unknown
plain_text
2 years ago
182 kB
6
Indexable
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;
Editor is loading...