Untitled

ALTER 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