USE [DBEnvigoHr]
GO
/****** Object:  StoredProcedure [dbo].[ProcAddUpdateLink]    Script Date: 03-09-2019 17:32:09 ******/
DROP PROCEDURE [dbo].[ProcAddUpdateLink]
GO
/****** Object:  StoredProcedure [dbo].[proc_tblUserLogin_Approve]    Script Date: 03-09-2019 17:32:09 ******/
DROP PROCEDURE [dbo].[proc_tblUserLogin_Approve]
GO
/****** Object:  StoredProcedure [dbo].[proc_tblUserLogin]    Script Date: 03-09-2019 17:32:09 ******/
DROP PROCEDURE [dbo].[proc_tblUserLogin]
GO
/****** Object:  StoredProcedure [dbo].[Proc_tblSuggestedApplicants_Select]    Script Date: 03-09-2019 17:32:09 ******/
DROP PROCEDURE [dbo].[Proc_tblSuggestedApplicants_Select]
GO
/****** Object:  StoredProcedure [dbo].[proc_tblSuggestedApplicants_ReOpen]    Script Date: 03-09-2019 17:32:09 ******/
DROP PROCEDURE [dbo].[proc_tblSuggestedApplicants_ReOpen]
GO
/****** Object:  StoredProcedure [dbo].[Proc_tblSuggestedApplicants_reject]    Script Date: 03-09-2019 17:32:09 ******/
DROP PROCEDURE [dbo].[Proc_tblSuggestedApplicants_reject]
GO
/****** Object:  StoredProcedure [dbo].[Proc_tblSuggestedApplicants_Approve]    Script Date: 03-09-2019 17:32:09 ******/
DROP PROCEDURE [dbo].[Proc_tblSuggestedApplicants_Approve]
GO
/****** Object:  StoredProcedure [dbo].[proc_tblSuggestedApplicants]    Script Date: 03-09-2019 17:32:09 ******/
DROP PROCEDURE [dbo].[proc_tblSuggestedApplicants]
GO
/****** Object:  StoredProcedure [dbo].[proc_tblPositions]    Script Date: 03-09-2019 17:32:09 ******/
DROP PROCEDURE [dbo].[proc_tblPositions]
GO
/****** Object:  StoredProcedure [dbo].[proc_tblMasterCandidatePool]    Script Date: 03-09-2019 17:32:09 ******/
DROP PROCEDURE [dbo].[proc_tblMasterCandidatePool]
GO
/****** Object:  StoredProcedure [dbo].[proc_tblJobDescription_Approve]    Script Date: 03-09-2019 17:32:09 ******/
DROP PROCEDURE [dbo].[proc_tblJobDescription_Approve]
GO
/****** Object:  StoredProcedure [dbo].[proc_tblJobDescription]    Script Date: 03-09-2019 17:32:09 ******/
DROP PROCEDURE [dbo].[proc_tblJobDescription]
GO
/****** Object:  StoredProcedure [dbo].[proc_tblApplicants]    Script Date: 03-09-2019 17:32:09 ******/
DROP PROCEDURE [dbo].[proc_tblApplicants]
GO
/****** Object:  StoredProcedure [dbo].[proc_tblApplicantRating]    Script Date: 03-09-2019 17:32:09 ******/
DROP PROCEDURE [dbo].[proc_tblApplicantRating]
GO
/****** Object:  StoredProcedure [dbo].[proc_tblApplicantComments]    Script Date: 03-09-2019 17:32:09 ******/
DROP PROCEDURE [dbo].[proc_tblApplicantComments]
GO
/****** Object:  StoredProcedure [dbo].[proc_tblApplicantComments]    Script Date: 03-09-2019 17:32:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[proc_tblApplicantComments]
(
@CommentID int,
@ApplicantdID int,
@PositionID int,
@CommentedBy int,
@Comments nvarchar(2000)
)
As begin
if(@CommentID=0)
Begin
insert into tblApplicantComments (ApplicantdID,PositionID,CommentedBy,Comments,CreateDate)
values (@ApplicantdID,@PositionID,@CommentedBy,@Comments, CURRENT_TIMESTAMP)
End
if(@CommentID>0)
Begin
update  tblApplicantComments set
ApplicantdID=@ApplicantdID,
PositionID=@PositionID,
CommentedBy=@CommentedBy,
Comments=@Comments,
UpdatedDate=CURRENT_TIMESTAMP
where CommentID=@CommentID
End
end

GO
/****** Object:  StoredProcedure [dbo].[proc_tblApplicantRating]    Script Date: 03-09-2019 17:32:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[proc_tblApplicantRating]
(
@RoundID int,
@ApplicantID int,
@InterViewStatus int,
@InterviewResult int,
@rating float,
@CommunicationID int,
@Communication int,
@JobKnowledgeID int,
@JobKnowledge int,
@PastExperienceID int,
@PastExperience int,
@CompanyFitnessID int,
@CompanyFitness int,
@AttitudeID int,
@Attitude int,
@UserID int
)
as 
Begin
Declare @positionID int,@CandidateID int
Select @positionID=PositionID,@CandidateID=CandidateID from tblApplicants where ApplicantID=@ApplicantID
if(@InterViewStatus=6)
Begin
update tblInterviewRounds set InterviewStatus=@InterviewStatus,
InterviewResult=@InterviewResult,
Rating=@Rating,
updatedate=current_TimeStamp,
updatedby=@UserID
where RoundID=@RoundID
if(@InterviewResult=1)
Begin
update tblApplicants set stage=5 where ApplicantID=@ApplicantID
end
if(@InterviewResult=5)
Begin
update tblApplicants set stage=3 where ApplicantID=@ApplicantID
end
if(@InterviewResult=2 or @InterviewResult=3 or @InterviewResult=4)
Begin
update tblApplicants set stage=2 where ApplicantID=@ApplicantID
end
insert into tblApplicantRating(RoundID,ApplicantID,RatingID,Rating,AddedBy,CreateDate)
Values(@RoundID,@ApplicantID,@CommunicationID,@Communication,@USerID,CURRENT_TIMESTAMP)
insert into tblApplicantRating(RoundID,ApplicantID,RatingID,Rating,AddedBy,CreateDate)
Values(@RoundID,@ApplicantID,@JobKnowledgeID,@JobKnowledge,@USerID,CURRENT_TIMESTAMP)
insert into tblApplicantRating(RoundID,ApplicantID,RatingID,Rating,AddedBy,CreateDate)
Values(@RoundID,@ApplicantID,@PastExperienceID,@PastExperience,@USerID,CURRENT_TIMESTAMP)
insert into tblApplicantRating(RoundID,ApplicantID,RatingID,Rating,AddedBy,CreateDate)
Values(@RoundID,@ApplicantID,@CompanyFitnessID,@CompanyFitness,@USerID,CURRENT_TIMESTAMP)
insert into tblApplicantRating(RoundID,ApplicantID,RatingID,Rating,AddedBy,CreateDate)
Values(@RoundID,@ApplicantID,@AttitudeID,@Attitude,@USerID,CURRENT_TIMESTAMP)
end
else
if(@InterViewStatus=5)
Begin
update tblPositions set Status='Closed',updatedby=@UserID,Updateddate=current_timeStamp where PositionID=@PositionID

--Update Candidate Interview Status to be Position Filled
update r set InterviewStatus=5 from tblInterviewRounds r inner join (select rounds.ApplicantID,RoundID,PositionID,Rank() over (Partition BY PositionID,rounds.CandidateID ORDER BY rounds.Createdate desc ) AS Rank from tblInterviewRounds rounds inner join tblapplicants apl on (rounds.ApplicantID=apl.ApplicantID) ) as t on(r.RoundID=t.roundid)   where positionID=@PositionID and rank=1

--Update other Candidate when position filled in Candidate Pool as Active=0
update tblMasterCandidatePool set Active=0 where CandidateID in (select CandidateID from tblApplicants where positionID=@positionID and Stage<3)

--release or recycle those candidate who are approved for post and in the process of interview but didn't completed till filling positions
delete tblSuggestedApplicants where positionID=@positionID and Candidate in (select CandidateID from tblApplicants where positionID=@positionID and Stage<3) 
End
if(@InterViewStatus=4)
Begin
update tblPositions set Status='OnHold',updatedby=@UserID,Updateddate=current_timeStamp where PositionID=@PositionID

--Update Candidate Interview Status to be Position OnHold
update r set InterviewStatus=4 from tblInterviewRounds r inner join (select rounds.ApplicantID,RoundID,PositionID,Rank() over (Partition BY PositionID,rounds.CandidateID ORDER BY rounds.Createdate desc ) AS Rank from tblInterviewRounds rounds inner join tblapplicants apl on (rounds.ApplicantID=apl.ApplicantID) ) as t on(r.RoundID=t.roundid)   where positionID=@PositionID and rank=1

--Update other Candidate when position filled in Candidate Pool as Active=0
update tblMasterCandidatePool set Active=0 where CandidateID in (select CandidateID from tblApplicants where positionID=@positionID and Stage<3)

--release or recycle those candidate who are approved for post and in the process of interview but didn't completed till filling positions
delete tblSuggestedApplicants where positionID=@positionID and Candidate in (select CandidateID from tblApplicants where positionID=@positionID and Stage<3) 
End
if(@InterViewStatus<4)
Begin
update tblInterviewRounds set 
InterviewStatus=@InterviewStatus,
updatedate=current_TimeStamp,
updatedby=@UserID
where RoundID=@RoundID
end
end


GO
/****** Object:  StoredProcedure [dbo].[proc_tblApplicants]    Script Date: 03-09-2019 17:32:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[proc_tblApplicants]
(
@PositionID int,
@CandidateID int,
@NoticePeriodDays int,
 @CurrentSalary float,
 @ExpectedSalary float,
 @RaisePercentage float,
 @Last_Appraised_On nvarchar(50),
 @IsBuyout nvarchar(50),
 @BuyoutCost float,
 @interviewType nvarchar(50),
 @interviewer int,
 @interviewdate datetime,
 @Stage int,
  @UserID int
)
As
Begin
declare @count int,@applicantID int,@countRound int
Select @count=count(1) from tblApplicants where PositionID=@PositionID and CandidateID=@CandidateID
if(@count=0)
begin
insert into tblApplicants(PositionID,CandidateID,NoticePeriodDays,CurrentSalary,ExpectedSalary,RaisePercentage,Last_Appraised_On,IsBuyout,BuyoutCost,Stage,createdate,addedby)
values(@PositionID,@CandidateID,@NoticePeriodDays,@CurrentSalary,@ExpectedSalary,@RaisePercentage,@Last_Appraised_On,@IsBuyout,@BuyoutCost,@Stage,CURRENT_TIMESTAMP,@UserID)
set @applicantID=IDENT_CURRENT('tblApplicants')
end
if(@count>0)
Begin
Select @applicantID=ApplicantID from tblApplicants where PositionID=@PositionID and CandidateID=@CandidateID
update tblApplicants set
NoticePeriodDays=@NoticePeriodDays,
CurrentSalary=@CurrentSalary,
ExpectedSalary=@ExpectedSalary,
RaisePercentage=@RaisePercentage,
Last_Appraised_On=@Last_Appraised_On,
IsBuyout=@IsBuyout,
BuyoutCost=@BuyoutCost,
Stage=@Stage,
updatedate=CURRENT_TIMESTAMP,
addedby=@UserID
where PositionID=@PositionID and CandidateID=@CandidateID
set @applicantID=IDENT_CURRENT('tblApplicants')
end
select @countRound=count(1) from tblInterviewRounds where ApplicantID=@applicantID
if(@countRound=0)
Begin
insert into tblInterviewRounds (ApplicantID,CandidateID,RoundType,Interviewer,SheduledOn,InterviewStatus
,createdate,addedby)
values(@applicantID,@CandidateID,@interviewType,@interviewer,@interviewdate,1,CURRENT_TIMESTAMP,@UserID)
end
--if(@countRound>1)
--begin
--update tblInterviewRounds set
--RoundType=@interviewType,
--Interviewer=@interviewer,
--SheduledOn=@interviewdate,
--updatedate=CURRENT_TIMESTAMP,
--updatedby=@UserID
--where ApplicantID=@applicantID
--end
update tblMasterCandidatePool set ApplicantDate=CURRENT_TIMESTAMP,LastApplicantID=@applicantID,Active=1 
where CandidateID=@CandidateID
if(@Stage=4)
Begin
--Update Candidate after selection in Candidate Pool as Active =2
update tblMasterCandidatePool set Active=2 where CandidateID=@CandidateID
--Update other Candidate after selection in Candidate Pool as Active=0
update tblMasterCandidatePool set Active=0 where CandidateID in (select CandidateID from tblApplicants where positionID=@positionID and Stage<3)
--Update Candidate Interview Status to be Position Filled
update r set InterviewStatus=5 from tblInterviewRounds r inner join (select rounds.ApplicantID,RoundID,PositionID,Rank() over (Partition BY PositionID,rounds.CandidateID ORDER BY rounds.Createdate desc ) AS Rank from tblInterviewRounds rounds inner join tblapplicants apl on (rounds.ApplicantID=apl.ApplicantID) ) as t on(r.RoundID=t.roundid)   where positionID=@PositionID and rank=1
--update tblposition against selected candidate
update tblPositions set Closure_Date=CURRENT_TIMESTAMP,SelectedCandidateID=@CandidateID,Status='Closed',updatedby=@UserID,Updateddate=current_timeStamp where PositionID=@PositionID
--release or recycle those candidate who are approved for post and in the process of interview but didn't completed till filling positions
delete tblSuggestedApplicants where positionID=@positionID and Candidate in (select CandidateID from tblApplicants where positionID=@positionID and Stage<3) 
End
if(@Stage=5)
Begin
update tblMasterCandidatePool set Active=3 where CandidateID=@CandidateID

End
end


GO
/****** Object:  StoredProcedure [dbo].[proc_tblJobDescription]    Script Date: 03-09-2019 17:32:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[proc_tblJobDescription]
(
@PositionID int,
@JobDescription nvarchar(max),
@UserID int,
@nstatus int out,
@ApproveStatus int
)
as Begin
declare @count int
select @count=count(1) from tblJobDescription where PositionID=@PositionID
if(@count=0)
Begin
insert into tblJobDescription (PositionID,JobDescription,AddedBy,ApproveStatus,CreateDate)
values(@PositionID,@JobDescription,@UserID,@ApproveStatus,CURRENT_TIMESTAMP)
set @nstatus=1
end
if(@count>0)
Begin
update tblJobDescription set 
JobDescription=@JobDescription,
UpdatedBy=@UserID,
UpdatedDate=CURRENT_TIMESTAMP,
ApproveStatus=@ApproveStatus
where PositionID=@PositionID
set @nstatus=2
end
update tblPositions set JDStatus=@ApproveStatus where PositionID=@PositionID
return @nstatus
end


GO
/****** Object:  StoredProcedure [dbo].[proc_tblJobDescription_Approve]    Script Date: 03-09-2019 17:32:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[proc_tblJobDescription_Approve]
(
@PositionID int,
@ApproveStatus int,
@UserID int
)
as begin
update tblJobDescription set
ApproveStatus=@ApproveStatus,
ApprovedBy=@UserID,
ApprovedDate=CURRENT_TIMESTAMP
where PositionID=@PositionID
update tblPositions set JDStatus=@ApproveStatus where PositionID=@PositionID
end


GO
/****** Object:  StoredProcedure [dbo].[proc_tblMasterCandidatePool]    Script Date: 03-09-2019 17:32:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[proc_tblMasterCandidatePool]
(
@CandidateID int,
@Name nvarchar(50),
@gender nvarchar(10),
@Experience numeric(4,2),
@EmailID nvarchar(50),
@Phone nvarchar(20),
@Education nvarchar(50),
@Year_of_Passing nvarchar(5),
@InterestedDepartmentID int,
@ProfessionalSummary nvarchar(500),
@LastCompany nvarchar(50),
@PrimaryIndustry nvarchar(50),
@PreviousNotableOrganisations nvarchar(500),
@CandidateStatus nvarchar(50),
@Source nvarchar(50),
@SourcingDate datetime,
@LastContactedDate datetime,
@ResumeGoogleLink nvarchar(1000),
@ResumeName nvarchar(500),
@addedby int,
@nid int out
)
as
Begin
declare @count int
select @count=count(1) from tblMasterCandidatePool where CandidateID=@CandidateID
if(@count=0)
begin
insert into tblMasterCandidatePool (Name,Gender,Experience,EmailID,Phone,Education,Year_of_Passing,InterestedDepartmentID,ProfessionalSummary,
LastCompany,PrimaryIndustry,PreviousNotableOrganisations,CandidateStatus,Source,SourcingDate,LastContactedDate,ResumeGoogleLink,ResumeName,addedby,createdate)
values(@Name,@gender,@Experience,@EmailID,@Phone,@Education,@Year_of_Passing,@InterestedDepartmentID,@ProfessionalSummary,@LastCompany,@PrimaryIndustry,
@PreviousNotableOrganisations,@CandidateStatus,@Source,@SourcingDate,@LastContactedDate,@ResumeGoogleLink,@ResumeName,@addedby,CURRENT_TIMESTAMP)
set @nid=IDENT_CURRENT('tblMasterCandidatePool')
end
else if(@count>0)
begin
update tblMasterCandidatePool set
Name=@Name,
Gender=@gender,
Experience=@Experience,
EmailID=@EmailID,
Phone=@Phone,
Education=@Education,
Year_of_Passing=@Year_of_Passing,
InterestedDepartmentID=@InterestedDepartmentID,
ProfessionalSummary=@ProfessionalSummary,
LastCompany=@LastCompany,
PrimaryIndustry=@PrimaryIndustry,
PreviousNotableOrganisations=@PreviousNotableOrganisations,
CandidateStatus=@CandidateStatus,
Source=@Source,
SourcingDate=@SourcingDate,
LastContactedDate=@LastContactedDate,
ResumeGoogleLink=@ResumeGoogleLink,
ResumeName=@ResumeName,
addedby=@addedby,
updatedate=CURRENT_TIMESTAMP
where CandidateID=@CandidateID
set @nid=@CandidateID
end
end


GO
/****** Object:  StoredProcedure [dbo].[proc_tblPositions]    Script Date: 03-09-2019 17:32:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[proc_tblPositions]
(
@PositionID int,
@DesignationID int,
@RequesterID int,
@Department int,
@Unit int,
@Priority nvarchar(15),
@TAT datetime,
@ClosedByDate datetime,
@Experience int,
@YearlySalaryBudget numeric(10,2),
@Status nvarchar(20)
)
AS
Begin
if(@PositionID=0)
Begin
insert into tblPositions (DesignationID,RequesterID,Department,Unit,Priority,TAT,RequestDate,ClosedByDate,Experience, YearlySalaryBudget, Status)
values(@DesignationID,@RequesterID,@Department,@Unit,@Priority,@TAT,CURRENT_TIMESTAMP,@ClosedByDate,@Experience,@YearlySalaryBudget,@Status)
end
else if(@PositionID>0)
Begin
update tblPositions set
DesignationID=@DesignationID,
RequesterID=@RequesterID,
Department=@Department,
Unit=@Unit,
Priority=@Priority,
TAT=@TAT,
ClosedByDate=@ClosedByDate,
Experience=@Experience,
YearlySalaryBudget=@YearlySalaryBudget,
Status=@Status
where PositionID=@PositionID
if(@Status='OnHold')
Begin
update tblPositions set Status='OnHold',updatedby=@RequesterID,Updateddate=current_timeStamp where PositionID=@PositionID

--Update Candidate Interview Status to be Position OnHold
update r set InterviewStatus=4 from tblInterviewRounds r inner join (select rounds.ApplicantID,RoundID,PositionID,Rank() over (Partition BY PositionID,rounds.CandidateID ORDER BY rounds.Createdate desc ) AS Rank from tblInterviewRounds rounds inner join tblapplicants apl on (rounds.ApplicantID=apl.ApplicantID) ) as t on(r.RoundID=t.roundid)   where positionID=@PositionID and rank=1

--Update other Candidate when position filled in Candidate Pool as Active=0
update tblMasterCandidatePool set Active=0 where CandidateID in (select CandidateID from tblApplicants where positionID=@positionID and Stage<3)

--release or recycle those candidate who are approved for post and in the process of interview but didn't completed till filling positions
delete tblSuggestedApplicants where positionID=@positionID and Candidate in (select CandidateID from tblApplicants where positionID=@positionID and Stage<3) 
end
end
end


GO
/****** Object:  StoredProcedure [dbo].[proc_tblSuggestedApplicants]    Script Date: 03-09-2019 17:32:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[proc_tblSuggestedApplicants] --proc_tblSuggestedApplicants 1, '1,','Selected'
(
@PositionID int,
@Candidate nvarchar(500),
@CurrentStatus nvarchar(15)

)
as
begin
DECLARE @IntLocation INT,@count int,@candidateID int,@candidatelist nvarchar(200)
delete tblSuggestedApplicants where currentstatus='Selected' and PositionID=@PositionID and ','+@candidate+',' not like '%,'+candidate+',%' 
        WHILE (CHARINDEX(',',    @Candidate, 0) > 0)
        BEGIN
              SET @IntLocation =   CHARINDEX(',',    @Candidate, 0)   
			  SELECT @candidateID=RTRIM(LTRIM(SUBSTRING(@Candidate,   0, @IntLocation)))
			  Select @count=count(1) from  tblSuggestedApplicants where candidate=@candidateID and PositionID=@PositionID
			  
			  
			  if(@count=0)
			  Begin
              INSERT INTO   tblSuggestedApplicants (PositionID,Candidate,CurrentStatus,ShareDate)
			  values(@PositionID,@candidateID,@CurrentStatus,CURRENT_TIMESTAMP)
			  end
			  if(@count>0)
			  Begin
              update tblSuggestedApplicants set 
			  CurrentStatus=@CurrentStatus,ShareDate=CURRENT_TIMESTAMP
			  where PositionID=@PositionID and Candidate=@candidateID
			  end
              --LTRIM and RTRIM to ensure blank spaces are   removed
                 
              SET @Candidate = STUFF(@Candidate,   1, @IntLocation,   '') 
        END
		end


GO
/****** Object:  StoredProcedure [dbo].[Proc_tblSuggestedApplicants_Approve]    Script Date: 03-09-2019 17:32:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[Proc_tblSuggestedApplicants_Approve]
(
@PositionID int,
@candidateID int,
@CurrentStatus nvarchar(15)
)
as
Begin

              update tblSuggestedApplicants set 
			  CurrentStatus=@CurrentStatus,ApproveDate=CURRENT_TIMESTAMP
			  where PositionID=@PositionID and Candidate=@candidateID
			 
update tblMasterCandidatePool set Iscandidate=1 where CandidateID=@candidateID
		end


GO
/****** Object:  StoredProcedure [dbo].[Proc_tblSuggestedApplicants_reject]    Script Date: 03-09-2019 17:32:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[Proc_tblSuggestedApplicants_reject]
(
@PositionID int,
@candidateID int,
@CurrentStatus nvarchar(15)
)
as
Begin
Declare @count int
select @count=count(1) from tblApplicants where PositionID=@PositionID and CandidateID=@candidateID
if(@count=0) begin
              update tblSuggestedApplicants set 
			  CurrentStatus=@CurrentStatus,RejectDate=CURRENT_TIMESTAMP
			  where PositionID=@PositionID and Candidate=@candidateID
			  end

			  end


GO
/****** Object:  StoredProcedure [dbo].[proc_tblSuggestedApplicants_ReOpen]    Script Date: 03-09-2019 17:32:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[proc_tblSuggestedApplicants_ReOpen]
(
@PositionID int,
@ApplicantID int
)
As 
Begin
Declare @count int,@countapprove int,@CandidateID int
select @CandidateID=t.CandidateID, @count= (select count(1) from tblInterviewRounds where ApplicantID!=t.ApplicantID and candidateid=t.candidateID and SheduledOn>t.SheduledOn) from (select applicantid,CandidateID,SheduledOn,Rank() over (Partition BY Applicantid ORDER BY Createdate desc ) AS Rank from tblInterviewRounds) as t where ApplicantID=@ApplicantID and rank=1
select @countapprove=count(1) from tblSuggestedApplicants where positionid=@PositionID and candidate=@CandidateID
if(@count=0 and @countapprove=0)
Begin
INSERT INTO   tblSuggestedApplicants (PositionID,Candidate,CurrentStatus,ShareDate)
			  values(@PositionID,@CandidateID,'Approve',CURRENT_TIMESTAMP)
update tblMasterCandidatePool set active=1
end
end



GO
/****** Object:  StoredProcedure [dbo].[Proc_tblSuggestedApplicants_Select]    Script Date: 03-09-2019 17:32:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[Proc_tblSuggestedApplicants_Select]
(
@PositionID int,
@candidateID int
)
as
Begin
Declare @count int
select @count=count(1) from tblApplicants where PositionID=@PositionID and CandidateID=@candidateID
if(@count=0) begin
              delete tblSuggestedApplicants 
			  where PositionID=@PositionID and Candidate=@candidateID
			 end

		end


GO
/****** Object:  StoredProcedure [dbo].[proc_tblUserLogin]    Script Date: 03-09-2019 17:32:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[proc_tblUserLogin]
(
@UserEmail nvarchar(500),
@UserName nvarchar(500)
)
as
Begin
Declare @count int
select @count=count(1) from tblUserLogin where userEmail=@UserEmail
if(@count=0)
begin
insert into tblUserLogin(UserName,UserEmail,Status,CreateDate)
Values(@UserName,@UserEmail,0,current_Timestamp)
end
end


GO
/****** Object:  StoredProcedure [dbo].[proc_tblUserLogin_Approve]    Script Date: 03-09-2019 17:32:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[proc_tblUserLogin_Approve]
(
@UserId int,
@Role int,
@Status int
)
As
Begin
update tblUserLogin set 
Role=@Role,
Status=@Status,
ActivatedDate=CURRENT_TIMESTAMP
where UserID=@UserId
end


GO
/****** Object:  StoredProcedure [dbo].[ProcAddUpdateLink]    Script Date: 03-09-2019 17:32:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ProcAddUpdateLink] 
--sp_help tblMasterLink
@LinkID	int,
@LinkTitle	nvarchar (200),
@Template	nvarchar(200),
@LinkActive	tinyint,
@DisplayOrder	int,
@Action int,
@Perfix nvarchar (50)
AS
BEGIN
	SET NOCOUNT ON;
if(@Action= '1')
begin
insert into tblMasterLink (
LinkTitle,
Template,
LinkActive,
DisplayOrder,
CreateDate, Perfix) values (
@LinkTitle,
@Template,
@LinkActive,
@DisplayOrder,
CURRENT_TIMESTAMP,
@Perfix
)
end 
if(@Action = '2')
begin 
update tblMasterLink set 
LinkTitle = @LinkTitle,
Template=@Template,
LinkActive=@LinkActive,
DisplayOrder=@DisplayOrder,
Perfix = @Perfix,
UpdateDate=CURRENT_TIMESTAMP where LinkID=@LinkID
end
END
GO
