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...