Untitled
unknown
plain_text
2 years ago
43 kB
5
Indexable
CREATE PROCEDURE [dbo].[AcceptShiftSwap]
@ShiftSwapId INT,
@WorkerId UNIQUEIDENTIFIER
AS
BEGIN
DECLARE @RC INT = 0
SET @RC =
(
select count(*)
FROM ShiftSwap sw
where
sw.Id=@ShiftSwapId
and (sw.ApprovalStatus='a' or sw.ApprovalStatus='p')
and sw.IsActive = 1
and sw.AcceptedByWorkerId is null
)
IF @RC = 0
BEGIN
THROW 50000, 'GatError.AcceptShiftSwap.Invalid',1
END
BEGIN TRY
BEGIN TRANSACTION;
DECLARE @ShiftStart datetimeoffset
DECLARE @ShiftEnd datetimeoffset
select @ShiftStart=StartDateTime, @ShiftEnd=StartDateTime from shift where Id = (select shiftid from shiftswap where Id=@ShiftSwapId)
-- step1 : do all checks
-- check if new worker has overlapping shift
DECLARE @ShiftOverlapCount INT
select @ShiftOverlapCount=count(*) from Shift
where IsActive=1 and Id in
(select ShiftId from WorkerShift
where WorkerId=@workerId and Status='A')
and (@ShiftEnd>=StartDateTime and @ShiftStart<=EndDateTime)
IF @ShiftOverlapCount > 0
BEGIN
RAISERROR(N'Error.SP.WorkerShiftSignup.WorkerShiftOverlap',16,1)
RETURN
END
-- check if new worker has weekly hrs limmit
-- check if new worker has continious hrs limit
DECLARE @WeeklyCheck INT = 1
SET @WeeklyCheck = dbo.udfCheckCompanyContiniousShiftSignup(@workerId, (select shiftid from ShiftSwap where Id=@ShiftSwapId))
-- step2 : change workershift
-- update worker shift to change the workerid
update
WorkerShift
set
workerid=@workerid,
LastUpdatedDate = GETDATE()
where
workerid=(select workerid from ShiftSwap where Id=@ShiftSwapId)
and shiftid=(select shiftid from ShiftSwap where Id=@ShiftSwapId)
-- step3 : accept the shift swap
-- update the shift swap status
UPDATE
ShiftSwap
SET
AcceptanceDate = GETDATE(),
AcceptedByWorkerId = @WorkerId
WHERE
Id=@ShiftSwapId;
COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK;
THROW;
END CATCH
-- select the requester worker
SELECT w.Id as WorkerId, sw.ShiftId, w.FirstName,w.MobilePhoneNumber,
(select STRING_AGG(wdt.devicetoken,',')
from WorkerDeviceToken wdt
where wdt.WorkerId=w.Id and wdt.IsActive=1) AS DeviceTokens
from ShiftSwap sw
inner join Worker w on sw.WorkerId=w.Id
where sw.Id=@ShiftSwapId
END;
CREATE PROCEDURE [dbo].[CancelShiftSwap]
@ShiftSwapId INT,
@WorkerId UNIQUEIDENTIFIER
AS
BEGIN
update ShiftSwap
SET
IsActive = 0,
lastupdateddate = GETDATE()
WHERE
Id = @ShiftSwapId
and WorkerId = @WorkerId
and AcceptedByWorkerId is NULL
and ApprovalStatus<>'r'
and IsActive = 1
END;
CREATE PROCEDURE [dbo].[ChangePassword]
@UserId INT,
@NewPassword NVARCHAR(100),
@UpdatedBy INT
AS
BEGIN
UPDATE [Login] SET [Password] = @NewPassword, LastUpdatedBy = @UpdatedBy, LastUpdatedDate = GETDATE(), ChangePasswordOnLogin = 0
WHERE Id = (SELECT LoginId FROM [User] WHERE Id = @UserId)
END;
create PROCEDURE [dbo].[ChangeShiftSwapStatus]
@Id INT,
@Status varchar(2),
@UserId INT
AS
BEGIN
-- update the shift swap status
UPDATE
ShiftSwap
SET
ApprovalStatus = @Status,
ApprovalDate = GETDATE(),
ApprovalBy = @UserId
WHERE
Id=@Id
and ApprovalStatus = 'n'
if @Status = 'a' or @Status = 'r'
BEGIN
-- table[0]: select worker details who posted the shift swap req for notification
SELECT
w.Id, w.FirstName,w.MobilePhoneNumber,
(select STRING_AGG(wdt.devicetoken,',')
from WorkerDeviceToken wdt
where wdt.WorkerId=w.Id and wdt.IsActive=1) DeviceTokens,
(select ShiftId from ShiftSwap where Id=@Id) ShiftId
from
worker w
where Id=(select WorkerId from ShiftSwap where Id=@Id)
END
if @Status = 'a'
BEGIN
-- table[1]: select all other workers to inform abt a new shift swap request
SELECT
w.Id, w.FirstName,w.MobilePhoneNumber,
(select STRING_AGG(wdt.devicetoken,',')
from WorkerDeviceToken wdt
where wdt.WorkerId=w.Id and wdt.IsActive=1) DeviceTokens,
(select ShiftId from ShiftSwap where Id=@Id) ShiftId
from WorkerJob wj
INNER join Worker w on wj.WorkerId=w.Id
where
wj.WorkerId<>(select WorkerId from ShiftSwap where Id=@Id)
and wj.JobId = (select JobId from Shift where Id=(select ShiftId from ShiftSwap where Id=@Id))
END
END;
CREATE PROCEDURE [dbo].[CreateBroadcast]
@Message VARCHAR(255),
@JobIds VARCHAR(255),
@UserId INT,
@WorkerCount INT
AS
BEGIN
INSERT into BroadCast(ToJobIds,[Message],CreationDate,CreatedBy,WorkerCount)
VALUES (@JobIds,@Message,GETDATE(),@UserId,@WorkerCount)
END;
CREATE PROCEDURE [dbo].[CreateCompany]
@Name varchar(150),
@CreatedBy int,
@DefaultShiftDuration tinyint = 4,
@MinShiftDuration tinyint = 2,
@MaxShiftDuration tinyint = 8,
@WorkWeekStartDay varchar(10) = 'Sunday',
@WorkWeekStartTime time = '00:00:00',
@ShiftMinAdvanceInDays tinyint = 5,
@ShiftMaxAdvanceInDays tinyint = 14,
@MinNumDaysPublished tinyint = 4,
@MaxNumDaysPublished tinyint = 15,
@SignUpCutOffDays tinyint = 1,
@ShiftCancelAllow bit = 1,
@ShiftModAllowTillDays tinyint = 0,
@WorkerShiftCancelDays tinyint = 2,
@ShiftConfirmationLockHours int = 0,
@MaxContiniousSignUpHours float = 12,
@EnableDailyReportInEmail bit = 0,
@EnableDailySmsReminder bit = 0,
@LocationName varchar(100),
@BuildingNo VARCHAR(50),
@StreetAddress VARCHAR(100),
@City VARCHAR(50),
@State VARCHAR(25),
@County VARCHAR(100),
@Country VARCHAR(100),
@ZipCode VARCHAR(10),
@TimeZone VARCHAR(100),
@ContactForWorkers VARCHAR(50),
@DailyReportEmailTime Time(7),
@DailySmsReminderTime Time(7),
@WorkerTypeHours ListWorkerTypeHours READONLY
AS
BEGIN
DECLARE @companyId INT
-- check if the time zone is valid
DECLARE @TZCount INT = 0
SET @TZCount = (select count(*) from sys.time_zone_info where name=@TimeZone)
if @TZCount = 0
BEGIN
THROW 50000,'Invalid Time Zone!',1
END
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO [dbo].[Company]
([Name],[IsActive],[CreatedBy],[CreationDate],[LastUpdatedBy],[LastUpdatedDate])
VALUES
(@Name,1, @CreatedBy, GETDATE(), @CreatedBy, GETDATE())
SET @companyId = (SELECT SCOPE_IDENTITY())
INSERT INTO Settings_ShiftCreation
(CompanyId,
DefaultShiftDuration,
MinShiftDuration,
MaxShiftDuration,
WorkWeekStartDay,
WorkWeekStartTime,
ShiftMinAdvanceInDays,
ShiftMaxAdvanceInDays,
MinNumDaysPublished,
MaxNumDaysPublished,
SignUpCutOffDays,
ShiftCancelAllow,
ShiftModAllowTillDays,
WorkerShiftCancelDays,
CreatedBy,
CreationDate,
LastUpdatedBy,
LastUpdatedDate,
ShiftConfirmationLockHours,
MaxContiniousSignUpHours,
EnableDailyReportInEmail,
EnableDailySMSReminderToWorker)
VALUES
(@companyId,
@DefaultShiftDuration,
@MinShiftDuration,
@MaxShiftDuration,
@WorkWeekStartDay,
@WorkWeekStartTime,
@ShiftMinAdvanceInDays,
@ShiftMaxAdvanceInDays,
@MinNumDaysPublished,
@MaxNumDaysPublished,
@SignUpCutOffDays,
@ShiftCancelAllow,
@ShiftModAllowTillDays,
@WorkerShiftCancelDays,
@CreatedBy,
GETDATE(),
@CreatedBy,
GETDATE(),
@ShiftConfirmationLockHours,
@MaxContiniousSignUpHours,
@EnableDailyReportInEmail,
@EnableDailySmsReminder)
INSERT INTO WorkerTypeHours(CompanyId,WorkerTypeId,MinHours,MaxHours,CreatedBy,CreationDate,LastUpdatedBy,LastUpdatedDate)
SELECT @companyId,WorkerTypeId,MinHours,MaxHours,@CreatedBy,GETDATE(),@CreatedBy,GETDATE() from @WorkerTypeHours
INSERT INTO [Location](CompanyId,[Name],BuildingNumber,Address1,City,[State],County,Country,Zip,IsActive,CreatedBy,CreatedDate,LastUpdatedBy,LastUpdatedDate,TIME_ZONE,CompanyContactForWorker,DailyReportEmailTime,DailySMSReminderTime)
VALUES(@companyId,@LocationName,@BuildingNo,@StreetAddress,@City,@State,@County,@Country,@ZipCode,1,@CreatedBy,GETDATE(),@CreatedBy,GETDATE(),@TimeZone,@ContactForWorkers,@DailyReportEmailTime,@DailySmsReminderTime)
COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK;
THROW;
END CATCH
SELECT @companyId;
END;
CREATE PROCEDURE [dbo].[CreateCompanyAdmin]
@UserName nvarchar(150),
@RoleId int,
@CompanyId int,
@FirstName nvarchar(100),
@MiddleName nvarchar(100),
@LastName nvarchar(100),
@Email nvarchar(255),
@PhoneNumber varchar(20),
@IsActive bit,
@CreatedBy int
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
INSERT INTO [dbo].[Login]
([UserName],[IsLocked],[IsActive],[CreationDate],[CreatedBy],[LastUpdatedDate],[LastUpdatedBy])
VALUES
(@UserName, 0, 1, GETDATE(), @CreatedBy, GETDATE(), @CreatedBy)
DECLARE @loginId INT
SET @loginId = (SELECT SCOPE_IDENTITY());
INSERT INTO [dbo].[User]
([LoginId],[RoleId],[CompanyId],[FirstName],[MiddleName],[LastName],[Email],[PhoneNumber],[IsActive],
[CreatedBy],[CreationDate],[LastUpdatedBy],[LastUpdatedDate])
VALUES (@loginId, @RoleId, @CompanyId, @FirstName, @MiddleName, @LastName, @Email, @PhoneNumber, 1,
@CreatedBy, GETDATE(), @CreatedBy, GETDATE())
DECLARE @userId INT
SET @userId = (SELECT SCOPE_IDENTITY());
DECLARE @luEntityId INT
SET @luEntityId = (SELECT Id FROM [dbo].[LuEntity] WHERE Entity = 'Company')
INSERT INTO [dbo].[EntityUser]
([LuEntityId],[UserId],EntityId,[CreatedBy],[CreationDate],[LastUpdatedBy],[LastUpdatedDate])
VALUES (@luEntityId, @userId,@CompanyId,@CreatedBy, GETDATE(),@CreatedBy, GETDATE())
COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK
END CATCH
END;
CREATE PROCEDURE [dbo].[CreateJob]
@UserId INT,
@Name VARCHAR(150),
@HexColor VARCHAR(6),
@CreatedBy INT,
@WorkCenterIdsCsv varchar(50),
@SkillIdsCsv varchar(100)
AS
BEGIN
Declare @LocationId INT
Declare @JobCount INT
DECLARE @UserCompanyId INT
DECLARE @jId INT
DECLARE @JobExpiryMinWorkHrs INT
DECLARE @JobExpiryMinWorkHrsDays INT
Declare @UserWorkCenterIds Table(Id int)
-- get user related work center Ids
Insert into @UserWorkCenterIds
select items from dbo.Split(dbo.udfUserWorkCenters(@userid),',')
-- check if job name already exists with the user workcenters
Set @JobCount =
(select count(*) from Job
WHERE
Id in (select JobId from JobWorkcenter where WorkcenterId in (Select Id from @UserWorkCenterIds))
and lower(trim(Name))=lower(trim(@Name))
and IsActive = 1
)
if @JobCount > 0
BEGIN
THROW 50000, 'Error.SP.CreateJob.JobAlreadyExist', 1
END
-- get user company
Set @UserCompanyId = (select CompanyId from [User] where Id=@UserId)
-- get job expiry min working hours and minimum working hours days
Set @JobExpiryMinWorkHrs = COALESCE((select JobExpiryMinWorkHrs from [Settings_CompanyJob] where CompanyId=@UserCompanyId), 0)
Set @JobExpiryMinWorkHrsDays = COALESCE((select JobExpiryMinWorkHrsDays from [Settings_CompanyJob] where CompanyId=@UserCompanyId), 0)
-- check if job name already exists for the user company
Set @JobCount =
(select count(*) from Job
where
CompanyId=@UserCompanyId
and lower(trim(Name))=lower(trim(@Name)))
-- start transaction
BEGIN TRY
BEGIN TRANSACTION;
if @JobCount > 0
BEGIN
-- if job name already exists for the company then get job id
set @jId =
(select Id from Job
where
CompanyId=@UserCompanyId
and lower(trim(Name))=lower(trim(@Name)))
-- update company job to make it active
update job
set IsActive = 1
where Id = @jId
END
ELSE
BEGIN
-- if job name does not exists for the company then add it with user company
INSERT INTO [dbo].[Job]
([Name],[HexColor],IsActive,CreatedBy,CreationDate,LastUpdatedBy,LastUpdatedDate,CompanyId,MinWorkHrs,MinWorkHrsDays)
VALUES
( @Name, @HexColor, 1, @CreatedBy, GETDATE(), @CreatedBy, GETDATE(),@UserCompanyId,@JobExpiryMinWorkHrs,@JobExpiryMinWorkHrsDays)
SET @jId = (SELECT SCOPE_IDENTITY())
END
-- insert into JobWorkCenter only the WorkCenterId from CSV which is associated with user
INSERT into JobWorkCenter(JobId,WorkCenterId,CreatedBy,CreationDate,LastUpdatedBy,LastUpdatedDate)
select @jId,items,@CreatedBy,GETDATE(),@CreatedBy,GETDATE() from dbo.Split(@WorkCenterIdsCsv,',')
where
items in (select Id from @UserWorkCenterIds)
and items not in (select WorkCenterId from JobWorkcenter where JobId=@jId)
-- add skills to the job
DECLARE @SkillIds TABLE(SkillId BIGINT)
INSERT INTO @SkillIds
SELECT items from dbo.split(@SkillIdsCsv,',')
-- update existing jobskill to make them inactive which are not present in @skillids
UPDATE JobSkill
SET
IsActive = 0,
LastUpdatedBy = @UserId,
LastUpdatedDate = GETDATE()
WHERE
JobId = @jId
and SkillId not in (select
SkillId
from @SkillIds)
-- update exisint jobskill to make them active
UPDATE JobSkill
SET
IsActive = 1,
LastUpdatedBy = @UserId,
LastUpdatedDate = GETDATE()
WHERE
JobId = @jId
and SkillId in (select
SkillId
from @SkillIds)
-- insert missing jobskill
INSERT INTO JobSkill
SELECT
@jId,SkillId,1,
@UserId,GETDATE(),@UserId,GETDATE()
FROM @SkillIds
where SkillId not in (select
SkillId
from JobSkill
where JobId=@jId)
-- insert job->skills to workers with missing skills
DECLARE @Workers TABLE(WorkerId UNIQUEIDENTIFIER)
DECLARE @Skills TABLE(SkillId INT)
-- get workersIds of all workers associated with the job
insert into @Workers
SELECT wj.WorkerId from WorkerJob wj where wj.JobId=@jId
-- get skillsIds of all skills associated with the job
insert into @Skills
SELECT js.SkillId from JobSkill js where js.JobId=@jId and js.IsActive=1
-- insert missing skills to workers
;WITH cte as (
SELECT wi.WorkerId,si.SkillId from @Workers wi,@Skills si
EXCEPT
SELECT ws.WorkerId,ws.SkillId from WorkerSkill ws where ws.WorkerId in (SELECT WorkerId from @Workers)
)
INSERT into WorkerSkill
SELECT cte.workerid,cte.skillid,1,@UserId,GETDATE(),@UserId,GETDATE() from cte
-- select job to return
SELECT Id, [Name], HexColor, IsActive,(select STRING_AGG(jw.WorkCenterId,',')
from JobWorkCenter jw
where jw.JobId=@jId
and jw.WorkcenterId in (select Id from @UserWorkCenterIds)) WorkCenterIds
FROM Job
WHERE Id = @jId
COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK
END CATCH
END;
CREATE PROCEDURE [dbo].[CreateJobSkill]
@JobIds VARCHAR(100),
@SkillName NVARCHAR(100),
@UserId INT
AS
BEGIN
DECLARE @SkillId BIGINT = 0
DECLARE @JobSkillId BIGINT = 0
DECLARE @CompanyId INT = 0
SET @CompanyId = (Select CompanyId FROM [User] where Id=@UserId)
BEGIN TRY
BEGIN TRANSACTION
-- check
select
@SkillId = Id
from
Skill
where
lower([Name])=lower(@SkillName)
AND CompanyId=@CompanyId
if @@ROWCOUNT = 0
BEGIN
INSERT INTO SKILL VALUES(@SkillName,1,@CompanyId,@UserId,GETDATE(),@UserId,GETDATE())
SET @SkillId = (SELECT SCOPE_IDENTITY())
END
-- update jobskill records to make active
UPDATE JobSkill
SET IsActive = 1, LastUpdatedBy = @UserId, LastUpdatedDate = GETDATE()
WHERE
SkillId = @SkillId and JobId in (select items from dbo.split(@JobIds,','))
-- insert jobskill records whicha are not available
INSERT INTO JOBSKILL
SELECT
items,@SkillId,1,@UserId,GETDATE(),@UserId,GETDATE()
from
dbo.split(@JobIds,',')
WHERE
items not in (select JobId from JobSkill where SkillId=@SkillId)
SET @JobSkillId = (SELECT SCOPE_IDENTITY())
SELECT
js.Id,js.JobId,js.SkillId,
s.Name as SkillName,j.Name as JobName
from JobSkill js
inner join Job j on js.JobId = j.Id
inner join Skill s on js.SkillId = s.Id
where js.SkillId = @SkillId and js.JobId in (select items from dbo.split(@JobIds,','))
COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK;
THROW;
END CATCH
END;
CREATE PROCEDURE [dbo].[CreateLocationAdmin]
@UserName nvarchar(150),
@RoleId int,
@CompanyId int,
@LocationId int,
@FirstName nvarchar(100),
@MiddleName nvarchar(100),
@LastName nvarchar(100),
@Email nvarchar(255),
@PhoneNumber varchar(20),
@IsActive bit,
@CreatedBy int
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
INSERT INTO [dbo].[Login]
([UserName],[IsLocked],[IsActive],[CreationDate],[CreatedBy],[LastUpdatedDate],[LastUpdatedBy])
VALUES
(@UserName, 0, 1, GETDATE(), @CreatedBy, GETDATE(), @CreatedBy)
DECLARE @loginId INT
SET @loginId = (SELECT SCOPE_IDENTITY());
INSERT INTO [dbo].[User]
([LoginId],[RoleId],[CompanyId],[FirstName],[MiddleName],[LastName],[Email],[PhoneNumber],[IsActive],
[CreatedBy],[CreationDate],[LastUpdatedBy],[LastUpdatedDate])
VALUES (@loginId, @RoleId, @CompanyId, @FirstName, @MiddleName, @LastName, @Email, @PhoneNumber, 1,
@CreatedBy, GETDATE(), @CreatedBy, GETDATE())
DECLARE @userId INT
SET @userId = (SELECT SCOPE_IDENTITY());
DECLARE @entityId INT
SET @entityId = (SELECT Id FROM [dbo].[LuEntity] WHERE Entity = 'Location')
INSERT INTO [dbo].[EntityUser]
([EntityId],[UserId],[CreatedBy],[CreationDate],[LastUpdatedBy],[LastUpdatedDate])
VALUES (@entityId, @userId, @CreatedBy, GETDATE(),@CreatedBy, GETDATE())
COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK
END CATCH
END;
Create PROCEDURE [dbo].[CreateShifts]
@tvpNewShifts ListOfShifts READONLY
AS
BEGIN
INSERT INTO [dbo].[Shift]
(StartDateTime
, EndDateTime
, JobId
, WorkCenterId
, NumWorkersNeeded
, ShiftPremium
, IsPublished
,[IsActive]
,[CreatedBy]
,[CreationDate]
,[LastUpdatedBy]
,[LastUpdatedDate])
SELECT
ns.StartDateTime,
ns.EndDateTime,
ns.JobId,
ns.WorkCenterId,
ns.NumWorkersNeeded,
ns.ShiftPremium,
ns.IsPublished,
1, --IsActive
ns.CreatedBy,
GETDATE(),
ns.CreatedBy,
GETDATE()
FROM @tvpNewShifts AS ns;
END;
CREATE PROCEDURE [dbo].[CreateShiftsAndReturn]
@tvpNewShifts ListOfShifts READONLY
AS
BEGIN
-- check for shift min and max duration
DECLARE @MinShiftDuration TINYINT
DECLARE @MaxShiftDuration TINYINT
DECLARE @ErrorMsg VARCHAR(100)
SELECT
@MinShiftDuration = MinShiftDuration,
@MaxShiftDuration = MaxShiftDuration
FROM
Settings_ShiftCreation
WHERE
CompanyId = (Select CompanyId from Job where Id=(select top 1 JobId from @tvpNewShifts))
SELECT s.* FROM @tvpNewShifts s
INNER JOIN Job j on s.JobId = j.Id
INNER JOIN Settings_ShiftCreation ss on j.CompanyId = ss.CompanyId
WHERE
DATEDIFF(MI,s.StartDateTime,s.EndDateTime)/60.0 < ss.MinShiftDuration
or DATEDIFF(MI,s.StartDateTime,s.EndDateTime)/60.0 > ss.MaxShiftDuration
IF @@ROWCOUNT > 0
BEGIN
SET @ErrorMsg = CONCAT('GATError.SP.CreateShifts.InvalidDuration','##',@MinShiftDuration,',',@MaxShiftDuration);
THROW 50001, @ErrorMsg , 1
END
DECLARE @MissingJobWorkCenterCount INT
-- check for any shift with no job-workcenter relation
set @MissingJobWorkCenterCount =
(select count(*) from @tvpNewShifts AS ns
where
CONCAT(ns.JobId,'-',ns.WorkCenterId) not in (select CONCAT(JobId,'-',WorkCenterId) from JobWorkcenter))
if @MissingJobWorkCenterCount > 0
BEGIN
THROW 50000,'GATError.SP.CreateShifts.NoJobWorkCenterExists',1
END
DECLARE @tblShiftsForID TABLE (
Id bigint NOT NULL,
StartDateTime datetime NOT NULL,
EndDateTime datetime NOT NULL,
JobId int NOT NULL,
WorkCenterId int NOT NULL,
NumWorkersNeeded smallint NOT NULL,
ShiftPremium varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
IsPublished bit NOT NULL,
IsActive bit NOT NULL,
CreatedBy int NOT NULL,
CreationDate datetime2(2) NOT NULL,
LastUpdatedBy int NOT NULL,
LastUpdatedDate datetime2(2) NOT NULL
)
--SET IDENTITY_INSERT dbo.Shift ON
INSERT INTO
[dbo].[Shift]
(
StartDateTime,
EndDateTime,
JobId,
WorkCenterId,
NumWorkersNeeded,
ShiftPremium,
IsPublished,
[IsActive],
[CreatedBy],
[CreationDate],
[LastUpdatedBy],
[LastUpdatedDate]
)
OUTPUT
INSERTED.ID,
INSERTED.StartDateTime,
INSERTED.EndDateTime,
INSERTED.JobId,
INSERTED.WorkCenterId,
INSERTED.NumWorkersNeeded,
INSERTED.ShiftPremium,
INSERTED.IsPublished,
INSERTED.IsActive,
INSERTED.CreatedBy,
INSERTED.CreationDate,
INSERTED.LastUpdatedBy,
INSERTED.LastUpdatedDate
INTO @tblShiftsForID
SELECT ns.StartDateTime, ns.EndDateTime, ns.JobId, ns.WorkCenterId, ns.NumWorkersNeeded, ns.ShiftPremium,
ns.IsPublished, 1, ns.CreatedBy, GETDATE(), ns.CreatedBy, GETDATE()
FROM @tvpNewShifts AS ns
SELECT Id, StartDateTime, EndDateTime, JobId, WorkCenterId, NumWorkersNeeded, ShiftPremium, IsPublished, IsActive
FROM @tblShiftsForID
END;
CREATE PROCEDURE [dbo].[CreateShiftSwapReq]
@ShiftId INT,
@WorkerId UNIQUEIDENTIFIER,
@Comment VARCHAR(255)
AS
BEGIN
-- get companyid from shiftid
DECLARE @CompanyId INT
set @CompanyId = (select j.CompanyId from shift s
inner join job j on s.JobId=j.Id
where
s.Id=@shiftId)
--Check if shift swap is enabled for the worker's company
DECLARE @IsShiftSwapAllowed BIT = 0
SET @IsShiftSwapAllowed =
(
select ShiftSwapEnabled from Settings_ShiftCreation where CompanyId = @CompanyId
)
IF @IsShiftSwapAllowed = 0
BEGIN
THROW 50000, 'GATError.NotAuthorized', 1;
END
--Check if a valid shift swap request exists for the specified shift
DECLARE @ShiftSwapCount INT = 0
SET @ShiftSwapCount =
(
SELECT count(*)
from ShiftSwap
WHERE
WorkerId=@WorkerId
and ShiftId=@ShiftId
and IsActive = 1
and ApprovalStatus<>'r'
)
IF @ShiftSwapCount > 0
BEGIN
THROW 50000, 'GatError.AcceptShiftSwap.Invalid', 1;
END;
--Check if shift swap approval is required for the worker's company
DECLARE @ShiftSwapApprovalEnabled BIT = 0
SET @ShiftSwapApprovalEnabled =
(
select ShiftSwapApprovalEnabled from Settings_ShiftCreation where CompanyId = @CompanyId
)
DECLARE @Status CHAR(2) = 'n'
IF @ShiftSwapApprovalEnabled = 0
BEGIN
SET @Status = 'p'
END
DECLARE @RCount INT = 0
-- create shift swap request if a valid worker<->shift exists
INSERT INTO ShiftSwap(ShiftId,WorkerId,RequestDate,Comment,ApprovalStatus,IsActive)
SELECT ws.ShiftId,ws.WorkerId,getdate(),@Comment,@Status,1
from WorkerShift ws
inner join shift s on ws.ShiftId=s.Id
where
ShiftId=@ShiftId
and WorkerId=@WorkerId
and [Status]='A'
and s.StartDateTime>GETDATE()
SET @RCount = @@ROWCOUNT
-- select all workers with the shift job to send notification
IF @RCount > 0
BEGIN
SELECT wj.WorkerId, w.FirstName,w.MobilePhoneNumber,
(select STRING_AGG(wdt.devicetoken,',')
from WorkerDeviceToken wdt
where wdt.WorkerId=w.Id and wdt.IsActive=1) DeviceTokens
from WorkerJob wj
INNER join Worker w on wj.WorkerId=w.Id
where
wj.JobId = (select JobId from Shift where Id=@ShiftId)
END
END;
CREATE PROCEDURE [dbo].[CreateSkill]
@Name NVARCHAR(100),
@UserId INT
AS
BEGIN
DECLARE @SkillId BIGINT = 0
DECLARE @IsActive BIT
DECLARE @CompanyId INT = 0
SET @CompanyId = (Select CompanyId FROM [User] where Id=@UserId)
-- check if already exists
select
@SkillId = Id,
@IsActive = IsActive
from
Skill
where
lower(trim([Name]))=lower(trim(@Name))
AND CompanyId=@CompanyId
-- if skill name already exist in company
if @@ROWCOUNT > 0 and @IsActive = 1
BEGIN
THROW 50000,'GATError.Skill.AlreadyExists',1
END
-- if skill exist and inactive make it active
if @@ROWCOUNT > 0 and @IsActive = 0
BEGIN
UPDATE Skill
SET IsActive = 1, LastUpdatedBy = @UserId, LastUpdatedDate = GETDATE()
where Id = @SkillId
END
-- add new skill if it does not exists
if @@ROWCOUNT = 0
BEGIN
INSERT INTO SKILL
VALUES(@Name, 1, @CompanyId, @UserId, GETDATE(), @UserId, GETDATE())
SET @SkillId = (SELECT SCOPE_IDENTITY())
END
SELECT * from Skill where Id=@SkillId
END;
--=============================================
-- Author: Gnan
-- Create date: 4th July 2021
-- Description: Creat new user
-- =============================================
CREATE PROCEDURE [dbo].[CreateUser]
-- Add the parameters for the stored procedure here
@Username varchar(50),
@SaltValue varchar(100),
@RoleId int,
@Company int,
@FirstName varchar(50),
@MiddleName varchar(50),
@LastName varchar(100),
@Email varchar(50),
@PhoneNumber varchar(20),
@IsActive bit,
@Password varchar(100),
@CreatedBy int,
@WorkCenterIds VARCHAR(100)
AS
BEGIN
DECLARE @NewLoginId INT;
DECLARE @NewUserId INT;
-- check if username already exists
DECLARE @UCount INT = 0
SET @UCount = (
SELECT count(*) from [Login] l where UserName = @Username
)
if @UCount > 0
BEGIN
THROW 50000, 'GATError.SP.CreateUser.AlreadyExist', 1
END
-- Create a new login id for new user
BEGIN TRY
BEGIN TRANSACTION;
INSERT INTO
[dbo].[Login]
(
[UserName] ,
[Password] ,
[SaltValue],
[PasswordUpdateDate],
[ChangePasswordOnLogin] ,
[FailedAttemptedLogins] ,
[IsLocked],
[IsActive],
[CreationDate] ,
[CreatedBy],
[LastUpdatedDate] ,
[LastUpdatedBy]
)
VALUES
(
@Username,
@Password,
@SaltValue,
GETDATE(),
1,
0,
0,
1,
GETDATE(),
@CreatedBy,
GETDATE(),
@CreatedBy
)
SET @NewLoginId = (SELECT Id FROM [Login] WHERE [UserName] = @username)
--create user with above created login id
INSERT INTO
[dbo].[User]
(
[LoginId],
[RoleId],
[CompanyId],
[FirstName],
[MiddleName],
[LastName],
[Email],
[PhoneNumber] ,
[IsActive] ,
[CreatedBy],
[CreationDate],
[LastUpdatedBy],
[LastUpdatedDate]
)
VALUES
(
@NewLoginId,
@RoleId,
@Company,
@FirstName,
@MiddleName,
@LastName,
@Email,
@PhoneNumber,
@IsActive,
@CreatedBy,
GETDATE(),
@CreatedBy,
GETDATE()
)
SET @NewUserId = (SELECT SCOPE_IDENTITY());
-- add user work center associations
INSERT INTO EntityUser
SELECT
@NewUserId,
items,
(select id from LuEntity where Entity='WorkCenter'),
@CreatedBy,
GETDATE(),
@CreatedBy,
GETDATE()
from
dbo.Split(@WorkCenterIds,',')
COMMIT TRAN
select @NewUserId
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK;
THROW
END CATCH
END;
CREATE PROCEDURE [dbo].[CreateWorker]
@FirstName VARCHAR(50),
@MiddleName VARCHAR(50),
@LastName VARCHAR(50),
@Email NVARCHAR(100),
@MobilePhoneNumber VARCHAR(20),
@CompanyId INT,
@WorkerTypeId TINYINT,
@CompanyEmployeeId VARCHAR(100),
@JobId VARCHAR(255),
@CreatedBy INT,
@Skills ListOfWorkerSkills readonly,
@WorkerJobs ListOfWorkerJobs READONLY
AS
BEGIN
DECLARE @MobileExist BIT = 0
DECLARE @NameMatch BIT = 0
DECLARE @WorkerActive BIT = 0
DECLARE @CompanyWorkerExist BIT = 0
DECLARE @CompanyWorkerActive BIT = 0
DECLARE @WorkerId uniqueidentifier
DECLARE @CompanyWorkerId INT
Select @MobileExist = (case when count(Id) > 0 then 1 else 0 end) from Worker where MobilePhoneNumber=@MobilePhoneNumber;
if @MobileExist = 'true'
begin
Select @NameMatch = (case when count(Id) > 0 then 1 else 0 end) from Worker
where
MobilePhoneNumber=@MobilePhoneNumber
and lower(FirstName)=lower(@FirstName)
and lower(LastName)=lower(@LastName);
end
if @NameMatch = 'true'
Begin
select top 1 @WorkerActive = IsActive, @WorkerId = lower(Id) from Worker
where
MobilePhoneNumber=@MobilePhoneNumber
and lower(FirstName)=lower(@FirstName)
and lower(LastName)=lower(@LastName);
select @CompanyWorkerExist = (case when count(Id) > 0 then 1 else 0 end) from CompanyWorker where CompanyId=@CompanyId and WorkerId=@WorkerId;
if @CompanyWorkerExist = 'true'
Begin
select top 1 @CompanyWorkerActive = IsActive, @CompanyWorkerId = Id from CompanyWorker where CompanyId=@CompanyId and WorkerId=@WorkerId;
end
end
-- get user jobs
DECLARE @UserJobIds TABLE(JobId INT)
INSERT @UserJobIds
SELECT JobId
from JobWorkcenter jwc
INNER JOIN Job j on jwc.JobId=j.Id and j.IsActive=1
where jwc.WorkcenterId in
(
SELECT items
from dbo.Split(dbo.udfUserWorkCenters(@CreatedBy),',')
)
-- get worker jobs
DECLARE @WorkerJobIds TABLE(JobId INT)
INSERT INTO @WorkerJobIds
SELECT items
from dbo.Split(dbo.udfWorkerJobs(@WorkerId,@CompanyId),',')
BEGIN TRY
BEGIN TRANSACTION;
-- add new worker
if @MobileExist = 'false'
begin
SET @WorkerId = (SELECT NEWID());
INSERT INTO [dbo].[Worker]
([Id],[FirstName],[MiddleName],[LastName],[Email],[MobilePhoneNumber],[IsActive],[CreatedBy],[CreationDate],[LastUpdatedBy],[LastUpdatedDate])
VALUES
(@WorkerId, @FirstName, @MiddleName, @LastName, @Email, @MobilePhoneNumber, 1, @CreatedBy, GETDATE(), @CreatedBy, GETDATE())
end
-- check if worker exist and names does not match
if @MobileExist = 'true' and @NameMatch = 'false'
begin
RAISERROR(N'Error.SP.CreateWorker.MobileExistWithDiffWorker',16,1)
end
-- enable existing worker
if @MobileExist = 'true' and @NameMatch = 'true' and @WorkerActive = 'false'
begin
update Worker set IsActive = 'true' where Id = lower(@WorkerId);
end
-- add new company worker
if @MobileExist = 'false' or
(@MobileExist = 'true' and @NameMatch = 'true' and @CompanyWorkerExist = 'false')
begin
INSERT INTO [dbo].[CompanyWorker]
([CompanyId],[WorkerId],[WorkerTypeId],[CompanyEmployeeId],IsActive,[CreatedBy],[CreationDate],[LastUpdatedBy],[LastUpdatedDate])
VALUES
(@CompanyId,@WorkerId,@WorkerTypeId,@CompanyEmployeeId,1,@CreatedBy, GETDATE(), @CreatedBy, GETDATE())
end
-- enable existing company worker
if @MobileExist = 'true' and @NameMatch = 'true' and
@CompanyWorkerExist = 'true' and @CompanyWorkerActive = 'false'
begin
update CompanyWorker set IsActive = 'true' where Id = @CompanyWorkerId;
end
-- if worker as well as company worker exist
if @MobileExist = 'true' and @NameMatch = 'true' and @WorkerActive = 'true' and
@CompanyWorkerExist = 'true' and @CompanyWorkerActive = 'true'
begin
-- check if the worker has at least one job from @UserJobIds
DECLARE @JCount INT
SELECT @JCount = COUNT(uj.JobId) from @UserJobIds uj
INNER JOIN @WorkerJobIds wj on uj.JobId=wj.JobId
-- if worker does not have any job from @UserJobIds,
-- then user is trying to add an active worker from same company
if @JCount > 0
BEGIN
RAISERROR(N'Error.SP.CreateWorker.WorkerExistWithCompany',16,1)
END
end
/*
-- add new worker jobs
if @MobileExist = 'false'
begin
DECLARE @JobIds TABLE (Id INT IDENTITY(1,1), JobId INT)
INSERT INTO @JobIds SELECT * FROM dbo.Split(@JobId,',')
DECLARE @MaxCount INT, @i INT = 1, @jId INT
SET @MaxCount = (SELECT COUNT(*) FROM @JobIds)
WHILE (@i <= @MaxCount)
BEGIN
SET @jId = (SELECT JobId FROM @JobIds WHERE Id = @i)
INSERT INTO [dbo].[WorkerJob]
([WorkerId],[JobId],[CreatedBy],[CreationDate],[LastUpdatedBy],[LastUpdatedDate])
VALUES
(@WorkerId, @jId, @CreatedBy, GETDATE(), @CreatedBy, GETDATE())
SET @i = (@i + 1)
END
end
*/
-- add new worker job which doesnt exist already
insert into WorkerJob
select @WorkerId,*,@CreatedBy,GETDATE(),@CreatedBy,GETDATE(),1,1,0,0,GETDATE() from dbo.Split(@JobId,',')
where items not in (select jobid from WorkerJob where WorkerId=@WorkerId);
UPDATE
dbo.WorkerJob
SET
dbo.WorkerJob.IsExpired = wjs.IsExpired,
dbo.WorkerJob.IsExpiredBy = @CreatedBy,
dbo.WorkerJob.IsExpiredDate = GETDATE()
FROM
dbo.WorkerJob wj
INNER JOIN
@WorkerJobs wjs
ON
wj.JobId = wjs.JobId
WHERE
wj.WorkerId = @WorkerId;
-- insert skill that doesn't exist
INSERT into workerskill
SELECT @WorkerId,skillid,levelid,@CreatedBy,GETDATE(),@CreatedBy,GETDATE()
from @Skills
WHERE skillid not in (select skillid from workerskill where workerid=@WorkerId)
-- update levels for existing skills if they are different
Update ws
set ws.skilllevelid=s.levelid,ws.LastUpdatedBy=@CreatedBy,ws.LastUpdatedDate=getdate()
from workerskill ws
inner join @Skills s on ws.skillid=s.skillid and ws.skilllevelid<>s.levelid
where
workerid=@WorkerId
COMMIT TRAN
SELECT w.Id, w.FirstName, w.MiddleName, w.LastName, w.Email, w.MobilePhoneNumber, lt.[Type], c.[Name] AS CompanyName,
(SELECT STRING_AGG(wj1.JobId,',') FROM WorkerJob wj1
WHERE
wj1.WorkerId = @WorkerId
and wj1.JobId in (select JobId from @UserJobIds)) AS Jobs,
(select STRING_AGG(wdt.devicetoken,',')
from WorkerDeviceToken wdt
where wdt.WorkerId=w.Id and wdt.IsActive=1) DeviceTokens
FROM Worker w INNER JOIN CompanyWorker cw ON w.Id = cw.WorkerId INNER JOIN Company c ON cw.CompanyId = c.Id
INNER JOIN LuWorkerType lt ON cw.WorkerTypeId = lt.Id
WHERE w.Id = lower(@WorkerId) and cw.CompanyId=@CompanyId
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK;
Throw;
END CATCH
END;
CREATE PROCEDURE [dbo].[CreateWorkerNotification]
@Notifications ListOfWorkerNotification READONLY
AS
BEGIN
DECLARE @NewIds TABLE(ID INT)
INSERT INTO WorkerNotification (WorkerId,[Type],Title,[Message],ShiftIds,NotificationDate)
OUTPUT Inserted.ID INTO @NewIds
SELECT WorkerId,[Type],Title,[Message],ShiftIds,GETDATE() FROM @Notifications
-- return IDs of inserted rows
Select ID from @NewIds
END;
CREATE PROCEDURE [dbo].[CreateWorkerSkillInterest]
@WorkerId UNIQUEIDENTIFIER,
@SkillId INT,
@JobId INT,
@Level INT
AS
BEGIN
DECLARE @WSID INT
-- check if the skill and with >= level already exists with worker
DECLARE @WorkerSkillCount INT = 0
SELECT
@WorkerSkillCount = count(ws.Id)
from WorkerSkill ws
INNER JOIN SkillLevel sl on ws.SkillLevelId=sl.Id
where
WorkerId=@WorkerId
and SkillId=@SkillId
and sl.Seq >= @Level
if @WorkerSkillCount > 0
BEGIN
THROW 50000,'GATError.Skill.AlreadyExists',1
END
SELECT @WSID=wi.Id
from WorkerSkillInterest wi
where
wi.WorkerId=@WorkerId
AND wi.SkillId=@SkillId
--AND wi.JobId=@JobId
--AND wi.SkillLevelId=(select Id from SkillLevel where Seq=@Level)
AND wi.Status = 'N'
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO WorkerSkillInterest
VALUES(
@WorkerId,
@SkillId,
(select Id from SkillLevel where Seq=@Level),
@JobId,
'N',
0,GETDATE(),0,GETDATE()
)
SET @WSID = SCOPE_IDENTITY()
END
SELECT
wi.Id, wi.SkillId, wi.JobId, wi.SkillLevelId,
j.Name JobName,
s.Name SkillName,
sl.Name LevelName,
sl.Seq LevelSeq
from WorkerSkillInterest wi
INNER JOIN Job j on wi.JobId=j.Id
INNER JOIN Skill s on wi.SkillId=s.Id
INNER JOIN SkillLevel sl on wi.SkillLevelId=sl.Id
WHERE
wi.Id=@WSID
END;
CREATE PROCEDURE [dbo].[DeleteJobSkill]
@Id BIGINT,
@UserId INT
AS
BEGIN
UPDATE JobSkill
SET IsActive = 0, LastUpdatedBy = @UserId, LastUpdatedDate = GETDATE()
where Id=@Id
END;
CREATE PROCEDURE [dbo].[DeleteShiftById]
@shiftId int
AS
BEGIN
DECLARE @ShiftStartDateTime datetimeoffset
Set @ShiftStartDateTime =
(select StartDateTime from shift where Id=@shiftId)
if @ShiftStartDateTime < GETDATE()
Begin
throw 50000, 'Error.SP.DeleteShiftById.CannotDeletePastShift' , 1
End
UPDATE [Shift] SET IsActive = 0 WHERE Id = @shiftId and IsActive = 1
END;
CREATE PROCEDURE [dbo].[DeleteWorkerAvailability]
@WorkerId UNIQUEIDENTIFIER,
@Ids NVARCHAR(255)
AS
BEGIN
DELETE FROM
WorkerAvailability
WHERE
WorkerId = @WorkerId
and Id in (select items from dbo.Split(@Ids,','))
END;
CREATE PROCEDURE [dbo].[DeleteWorkersById]
@workerIds VARCHAR(MAX),
@UserId INT
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION;
DECLARE @workerId TABLE (Id UNIQUEIDENTIFIER)
INSERT INTO @workerId SELECT * FROM dbo.Split(@workerids,',')
-- get user company
DECLARE @CompanyId INT
SET @CompanyId = (select CompanyId from [User] where Id=@UserId)
-- get user jobs
DECLARE @UserJobs TABLE(Id INT)
INSERT into @UserJobs
select
jwc.JobId
from
JobWorkcenter jwc
INNER JOIN Job j on jwc.JobId=j.Id
where
j.IsActive=1
and jwc.WorkcenterId in (select items
from dbo.Split(dbo.udfUserWorkCenters(@UserId),','))
-- throw error if the worker has active signed up shifts in future with user jobs
DECLARE @RC INT = 0
SELECT @RC = count(*)
from WorkerShift ws
INNER JOIN Shift s on ws.ShiftId=s.Id and s.IsActive=1
where
ws.WorkerId IN (SELECT Id FROM @workerId)
and s.JobId in (select Id from @UserJobs)
and s.StartDateTime>GETDATE()
IF @RC > 0
BEGIN
THROW 50000,'GATError.WorkerDelete.ShiftExists',1
END
-- deactivate the company worker for worker and user company
UPDATE CompanyWorker
SET IsActive = 0
WHERE
WorkerId IN (SELECT Id FROM @workerId)
and CompanyId=@CompanyId
-- disabled it as per JIRA #GM-29
--delete workers future signed up shifts
/*DELETE ws FROM WorkerShift ws
INNER JOIN [Shift] s ON ws.ShiftId = s.Id
WHERE WorkerId IN (SELECT Id FROM @workerId) AND s.StartDateTime > GETDATE()*/
COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK;
THROW;
END CATCH
END;
CREATE PROCEDURE [dbo].[DeleteWorkerShift]
@WorkerId UNIQUEIDENTIFIER,
@ShiftIds VARCHAR(255),
@UserId INT
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
-- DELETE FROM
-- WorkerShift
-- WHERE
-- WorkerId=@WorkerId
-- and ShiftId in (SELECT items from dbo.Split(@ShiftIds,','))
UPDATE
WorkerShift
SET
[Status]='R',
LastUpdatedDate=GETDATE(),
LastUpdatedBy=@UserId
WHERE
WorkerId=@WorkerId
and ShiftId in (SELECT items from dbo.Split(@ShiftIds,','))
COMMIT TRAN
END TRY
BEGIN CATCH
if @@TRANCOUNT > 0 ROLLBACK;
THROW;
END CATCH
END;
CREATE PROCEDURE [dbo].[DisableWorkerDeviceToken]
@WorkerId UNIQUEIDENTIFIER,
@DeviceId NVARCHAR(255)
AS
BEGIN
UPDATE
WorkerDeviceToken
SET
IsActive=0,
LastUpdatedDate=GETDATE()
WHERE
WorkerId=@WorkerId
AND DeviceId=@DeviceId
END;
Editor is loading...