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