USE [DBEnvigoHr]
GO
EXEC sys.sp_dropextendedproperty @name=N'MS_DiagramPaneCount' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vwCandidatepool'

GO
EXEC sys.sp_dropextendedproperty @name=N'MS_DiagramPane1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vwCandidatepool'

GO
/****** Object:  StoredProcedure [dbo].[proc_tblUserLogin_Approve]    Script Date: 8/22/2019 5:58:19 PM ******/
DROP PROCEDURE [dbo].[proc_tblUserLogin_Approve]
GO
/****** Object:  StoredProcedure [dbo].[proc_tblUserLogin]    Script Date: 8/22/2019 5:58:19 PM ******/
DROP PROCEDURE [dbo].[proc_tblUserLogin]
GO
/****** Object:  StoredProcedure [dbo].[Proc_tblSuggestedApplicants_Select]    Script Date: 8/22/2019 5:58:19 PM ******/
DROP PROCEDURE [dbo].[Proc_tblSuggestedApplicants_Select]
GO
/****** Object:  StoredProcedure [dbo].[Proc_tblSuggestedApplicants_reject]    Script Date: 8/22/2019 5:58:19 PM ******/
DROP PROCEDURE [dbo].[Proc_tblSuggestedApplicants_reject]
GO
/****** Object:  StoredProcedure [dbo].[Proc_tblSuggestedApplicants_Approve]    Script Date: 8/22/2019 5:58:19 PM ******/
DROP PROCEDURE [dbo].[Proc_tblSuggestedApplicants_Approve]
GO
/****** Object:  StoredProcedure [dbo].[proc_tblSuggestedApplicants]    Script Date: 8/22/2019 5:58:19 PM ******/
DROP PROCEDURE [dbo].[proc_tblSuggestedApplicants]
GO
/****** Object:  StoredProcedure [dbo].[proc_tblPositions]    Script Date: 8/22/2019 5:58:19 PM ******/
DROP PROCEDURE [dbo].[proc_tblPositions]
GO
/****** Object:  StoredProcedure [dbo].[proc_tblMasterCandidatePool]    Script Date: 8/22/2019 5:58:19 PM ******/
DROP PROCEDURE [dbo].[proc_tblMasterCandidatePool]
GO
/****** Object:  StoredProcedure [dbo].[proc_tblJobDescription_Approve]    Script Date: 8/22/2019 5:58:19 PM ******/
DROP PROCEDURE [dbo].[proc_tblJobDescription_Approve]
GO
/****** Object:  StoredProcedure [dbo].[proc_tblJobDescription]    Script Date: 8/22/2019 5:58:19 PM ******/
DROP PROCEDURE [dbo].[proc_tblJobDescription]
GO
/****** Object:  StoredProcedure [dbo].[proc_tblApplicants]    Script Date: 8/22/2019 5:58:19 PM ******/
DROP PROCEDURE [dbo].[proc_tblApplicants]
GO
/****** Object:  StoredProcedure [dbo].[proc_tblApplicantRating]    Script Date: 8/22/2019 5:58:19 PM ******/
DROP PROCEDURE [dbo].[proc_tblApplicantRating]
GO
/****** Object:  View [dbo].[vwtblSuggestedApplicants]    Script Date: 8/22/2019 5:58:19 PM ******/
DROP VIEW [dbo].[vwtblSuggestedApplicants]
GO
/****** Object:  View [dbo].[vwtblApplicants]    Script Date: 8/22/2019 5:58:19 PM ******/
DROP VIEW [dbo].[vwtblApplicants]
GO
/****** Object:  View [dbo].[vwPosition]    Script Date: 8/22/2019 5:58:19 PM ******/
DROP VIEW [dbo].[vwPosition]
GO
/****** Object:  View [dbo].[vwCandidatepool]    Script Date: 8/22/2019 5:58:19 PM ******/
DROP VIEW [dbo].[vwCandidatepool]
GO
/****** Object:  Table [dbo].[tblUserLogin]    Script Date: 8/22/2019 5:58:19 PM ******/
DROP TABLE [dbo].[tblUserLogin]
GO
/****** Object:  Table [dbo].[tblSuggestedApplicants]    Script Date: 8/22/2019 5:58:19 PM ******/
DROP TABLE [dbo].[tblSuggestedApplicants]
GO
/****** Object:  Table [dbo].[tblPositions]    Script Date: 8/22/2019 5:58:19 PM ******/
DROP TABLE [dbo].[tblPositions]
GO
/****** Object:  Table [dbo].[tblMasterUnit]    Script Date: 8/22/2019 5:58:19 PM ******/
DROP TABLE [dbo].[tblMasterUnit]
GO
/****** Object:  Table [dbo].[tblMasterRole]    Script Date: 8/22/2019 5:58:19 PM ******/
DROP TABLE [dbo].[tblMasterRole]
GO
/****** Object:  Table [dbo].[tblMasterRating]    Script Date: 8/22/2019 5:58:19 PM ******/
DROP TABLE [dbo].[tblMasterRating]
GO
/****** Object:  Table [dbo].[tblMasterInterviewStatus]    Script Date: 8/22/2019 5:58:19 PM ******/
DROP TABLE [dbo].[tblMasterInterviewStatus]
GO
/****** Object:  Table [dbo].[tblMasterInterviewResult]    Script Date: 8/22/2019 5:58:19 PM ******/
DROP TABLE [dbo].[tblMasterInterviewResult]
GO
/****** Object:  Table [dbo].[tblMasterHiringStatus]    Script Date: 8/22/2019 5:58:19 PM ******/
DROP TABLE [dbo].[tblMasterHiringStatus]
GO
/****** Object:  Table [dbo].[tblMasterDesignation]    Script Date: 8/22/2019 5:58:19 PM ******/
DROP TABLE [dbo].[tblMasterDesignation]
GO
/****** Object:  Table [dbo].[tblMasterDepartment]    Script Date: 8/22/2019 5:58:19 PM ******/
DROP TABLE [dbo].[tblMasterDepartment]
GO
/****** Object:  Table [dbo].[tblMasterCandidatePool]    Script Date: 8/22/2019 5:58:19 PM ******/
DROP TABLE [dbo].[tblMasterCandidatePool]
GO
/****** Object:  Table [dbo].[tblJobDescription]    Script Date: 8/22/2019 5:58:19 PM ******/
DROP TABLE [dbo].[tblJobDescription]
GO
/****** Object:  Table [dbo].[tblInterviewRounds]    Script Date: 8/22/2019 5:58:19 PM ******/
DROP TABLE [dbo].[tblInterviewRounds]
GO
/****** Object:  Table [dbo].[tblApplicants]    Script Date: 8/22/2019 5:58:19 PM ******/
DROP TABLE [dbo].[tblApplicants]
GO
/****** Object:  Table [dbo].[tblApplicantRating]    Script Date: 8/22/2019 5:58:19 PM ******/
DROP TABLE [dbo].[tblApplicantRating]
GO
/****** Object:  Table [dbo].[tblApplicantRating]    Script Date: 8/22/2019 5:58:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblApplicantRating](
	[ReviewID] [int] IDENTITY(1,1) NOT NULL,
	[RoundID] [int] NULL,
	[ApplicantID] [int] NULL,
	[RatingID] [int] NULL,
	[Rating] [int] NULL,
	[createdate] [datetime] NULL,
	[addedby] [int] NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tblApplicants]    Script Date: 8/22/2019 5:58:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblApplicants](
	[ApplicantID] [int] IDENTITY(1,1) NOT NULL,
	[PositionID] [int] NULL,
	[CandidateID] [int] NULL,
	[RecruiterComments] [nvarchar](500) NULL,
	[NoticePeriodDays] [int] NULL,
	[CurrentSalary] [float] NULL,
	[ExpectedSalary] [float] NULL,
	[RaisePercentage] [float] NULL,
	[Last_Appraised_On] [nvarchar](50) NULL,
	[IsBuyout] [nvarchar](50) NULL,
	[BuyoutCost] [float] NULL,
	[Stage] [nvarchar](50) NULL,
	[createdate] [datetime] NULL,
	[updatedate] [datetime] NULL,
	[addedby] [int] NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tblInterviewRounds]    Script Date: 8/22/2019 5:58:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblInterviewRounds](
	[RoundID] [int] IDENTITY(1,1) NOT NULL,
	[ApplicantID] [int] NULL,
	[CandidateID] [int] NULL,
	[RoundType] [nvarchar](50) NULL,
	[Interviewer] [int] NULL,
	[SheduledOn] [datetime] NULL,
	[InterviewStatus] [int] NULL,
	[InterviewResult] [int] NULL,
	[Rating] [float] NULL,
	[createdate] [datetime] NULL,
	[updatedate] [datetime] NULL,
	[addedby] [int] NULL,
	[updatedby] [int] NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tblJobDescription]    Script Date: 8/22/2019 5:58:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblJobDescription](
	[JobID] [int] IDENTITY(1,1) NOT NULL,
	[PositionID] [int] NULL,
	[JobDescription] [nvarchar](max) NULL,
	[AddedBy] [int] NULL,
	[CreateDate] [datetime] NULL,
	[UpdatedBy] [int] NULL,
	[UpdatedDate] [datetime] NULL,
	[ApproveStatus] [int] NULL,
	[ApprovedBy] [int] NULL,
	[ApprovedDate] [datetime] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tblMasterCandidatePool]    Script Date: 8/22/2019 5:58:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblMasterCandidatePool](
	[CandidateID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NULL,
	[Gender] [nvarchar](10) NULL,
	[Experience] [float] NULL,
	[EmailID] [nvarchar](50) NULL,
	[Phone] [nvarchar](20) NULL,
	[Education] [nvarchar](50) NULL,
	[Year_of_Passing] [nvarchar](5) NULL,
	[InterestedDepartmentID] [int] NULL,
	[ProfessionalSummary] [nvarchar](500) NULL,
	[LastCompany] [nvarchar](50) NULL,
	[PrimaryIndustry] [nvarchar](50) NULL,
	[PreviousNotableOrganisations] [nvarchar](500) NULL,
	[CandidateStatus] [nvarchar](50) NULL,
	[Source] [nvarchar](50) NULL,
	[SourcingDate] [datetime] NULL,
	[LastContactedDate] [datetime] NULL,
	[ResumeGoogleLink] [nvarchar](1000) NULL,
	[ResumeName] [nvarchar](200) NULL,
	[createdate] [datetime] NULL,
	[updatedate] [datetime] NULL,
	[addedby] [int] NULL,
	[Iscandidate] [int] NULL CONSTRAINT [DF_tblMasterCandidatePool_Iscandidate]  DEFAULT ((0)),
	[ApplicantDate] [datetime] NULL,
	[LastApplicantID] [int] NULL,
	[Active] [tinyint] NULL CONSTRAINT [DF_tblMasterCandidatePool_Active]  DEFAULT ((0))
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tblMasterDepartment]    Script Date: 8/22/2019 5:58:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblMasterDepartment](
	[DepartmentID] [int] IDENTITY(1,1) NOT NULL,
	[DepartmentName] [nvarchar](50) NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tblMasterDesignation]    Script Date: 8/22/2019 5:58:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblMasterDesignation](
	[DesignationID] [int] IDENTITY(1,1) NOT NULL,
	[DesignationName] [nvarchar](50) NULL,
	[DepartmentID] [int] NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tblMasterHiringStatus]    Script Date: 8/22/2019 5:58:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblMasterHiringStatus](
	[HiringID] [int] IDENTITY(1,1) NOT NULL,
	[StatusName] [nvarchar](200) NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tblMasterInterviewResult]    Script Date: 8/22/2019 5:58:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblMasterInterviewResult](
	[StatusID] [int] IDENTITY(1,1) NOT NULL,
	[Result] [nvarchar](50) NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tblMasterInterviewStatus]    Script Date: 8/22/2019 5:58:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblMasterInterviewStatus](
	[InterviewStatusID] [int] IDENTITY(1,1) NOT NULL,
	[StatusName] [nvarchar](50) NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tblMasterRating]    Script Date: 8/22/2019 5:58:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblMasterRating](
	[RatingID] [int] IDENTITY(1,1) NOT NULL,
	[RatingName] [nvarchar](50) NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tblMasterRole]    Script Date: 8/22/2019 5:58:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblMasterRole](
	[RoleID] [int] IDENTITY(1,1) NOT NULL,
	[RoleName] [nvarchar](50) NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tblMasterUnit]    Script Date: 8/22/2019 5:58:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblMasterUnit](
	[unitID] [int] IDENTITY(1,1) NOT NULL,
	[UnitName] [nvarchar](50) NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tblPositions]    Script Date: 8/22/2019 5:58:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblPositions](
	[PositionID] [int] IDENTITY(1,1) NOT NULL,
	[DesignationID] [int] NULL,
	[RequesterID] [int] NULL,
	[Department] [int] NULL,
	[Unit] [int] NULL,
	[Priority] [nvarchar](15) NULL,
	[TAT] [datetime] NULL,
	[RequestDate] [datetime] NULL,
	[ClosedByDate] [datetime] NULL,
	[Experience] [int] NULL,
	[YearlySalaryBudget] [float] NULL,
	[Status] [nvarchar](20) NULL,
	[JDStatus] [int] NULL CONSTRAINT [DF_tblPositions_JDStatus]  DEFAULT ((0)),
	[Closure_Date] [datetime] NULL,
	[SelectedCandidateID] [int] NULL,
	[Remark] [nvarchar](300) NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tblSuggestedApplicants]    Script Date: 8/22/2019 5:58:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblSuggestedApplicants](
	[SelectionID] [int] IDENTITY(1,1) NOT NULL,
	[PositionID] [int] NULL,
	[Candidate] [nvarchar](5) NULL,
	[CurrentStatus] [nvarchar](15) NULL,
	[ShareDate] [datetime] NULL,
	[ApproveDate] [datetime] NULL,
	[RejectDate] [datetime] NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[tblUserLogin]    Script Date: 8/22/2019 5:58:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblUserLogin](
	[UserID] [int] IDENTITY(1,1) NOT NULL,
	[UserEmail] [nvarchar](50) NULL,
	[UserName] [nvarchar](50) NULL,
	[LastLoginDate] [datetime] NULL,
	[Createdate] [datetime] NULL,
	[Status] [tinyint] NULL,
	[Role] [int] NULL,
	[ActivatedDate] [datetime] NULL
) ON [PRIMARY]

GO
/****** Object:  View [dbo].[vwCandidatepool]    Script Date: 8/22/2019 5:58:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vwCandidatepool]
AS
SELECT DISTINCT 
                         t.CandidateID, t.Name, t.Experience, t.EmailID, t.CandidateStatus, t.Source, t.Gender, t.ResumeName, t.ResumeGoogleLink, t.LastContactedDate, t.Education, t.LastApplicantID, t1.DepartmentName, t.Phone, t.Year_of_Passing, 
                         t.ProfessionalSummary, t.InterestedDepartmentID, t.LastCompany, t.PrimaryIndustry, t.PreviousNotableOrganisations, t.SourcingDate, t.createdate, t.updatedate, t.addedby, t.Iscandidate, t.ApplicantDate, t.Active
FROM            dbo.tblMasterCandidatePool AS t INNER JOIN
                         dbo.tblMasterDepartment AS t1 ON t.InterestedDepartmentID = t1.DepartmentID
WHERE        (t.Active <> 1) OR
                         (t.Active <> 2) OR
                         (t.Active IS NULL)


GO
/****** Object:  View [dbo].[vwPosition]    Script Date: 8/22/2019 5:58:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE View [dbo].[vwPosition]
as

select pos.*,dpt.DepartmentName,desg.DesignationName,unit.UnitName from tblPositions pos inner join tblMasterDepartment dpt on (pos.Department=dpt.DepartmentID) 
inner join tblMasterDesignation desg on (pos.DesignationID=desg.DesignationID) inner join tblMasterUnit unit on (pos.Unit=unit.unitID)




GO
/****** Object:  View [dbo].[vwtblApplicants]    Script Date: 8/22/2019 5:58:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO



CREATE View [dbo].[vwtblApplicants]
as

select sa.ApplicantID,sa.Stage,Sa.PositionID, pos.RequesterID,pos.Unit,unit.UnitName,pos.priority,pos.TAT,pos.experience as ReuiredExperience,pos.JDstatus,dpt.DepartmentName,desg.DesignationName,cand.* 
,(select username from tblUserLogin where UserID=pos.RequesterID and Status=1 and role is not null) as RequesterName

 from tblApplicants sa
inner join tblMasterCandidatePool cand on (sa.CandidateID=cand.CandidateID)
inner join tblPositions pos on (sa.PositionID=pos.PositionID)
inner join tblMasterDepartment dpt on (pos.Department=dpt.DepartmentID) 
inner join tblMasterDesignation desg on (pos.DesignationID=desg.DesignationID)
inner join tblMasterUnit unit on (pos.unit=unit.unitID)








GO
/****** Object:  View [dbo].[vwtblSuggestedApplicants]    Script Date: 8/22/2019 5:58:19 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE View [dbo].[vwtblSuggestedApplicants]
as

select pos.RequesterID,(select username from tblUserLogin where UserID=pos.RequesterID and Status=1 and role is not null) as RequesterName,pos.Unit,pos.priority,pos.TAT,pos.experience as ReuiredExperience,pos.JDstatus,dpt.DepartmentName,desg.DesignationName,cand.* from tblSuggestedApplicants sa
inner join tblMasterCandidatePool cand on (sa.Candidate=cand.CandidateID)
inner join tblPositions pos on (sa.PositionID=pos.PositionID)
inner join tblMasterDepartment dpt on (pos.Department=dpt.DepartmentID) 
inner join tblMasterDesignation desg on (pos.DesignationID=desg.DesignationID)







GO
SET IDENTITY_INSERT [dbo].[tblApplicantRating] ON 

INSERT [dbo].[tblApplicantRating] ([ReviewID], [RoundID], [ApplicantID], [RatingID], [Rating], [createdate], [addedby]) VALUES (1, 1, 1, 1, 3, CAST(N'2019-08-22 17:51:33.730' AS DateTime), 5)
INSERT [dbo].[tblApplicantRating] ([ReviewID], [RoundID], [ApplicantID], [RatingID], [Rating], [createdate], [addedby]) VALUES (2, 1, 1, 2, 5, CAST(N'2019-08-22 17:51:33.733' AS DateTime), 5)
INSERT [dbo].[tblApplicantRating] ([ReviewID], [RoundID], [ApplicantID], [RatingID], [Rating], [createdate], [addedby]) VALUES (3, 1, 1, 3, 1, CAST(N'2019-08-22 17:51:33.733' AS DateTime), 5)
INSERT [dbo].[tblApplicantRating] ([ReviewID], [RoundID], [ApplicantID], [RatingID], [Rating], [createdate], [addedby]) VALUES (4, 1, 1, 4, 1, CAST(N'2019-08-22 17:51:33.733' AS DateTime), 5)
INSERT [dbo].[tblApplicantRating] ([ReviewID], [RoundID], [ApplicantID], [RatingID], [Rating], [createdate], [addedby]) VALUES (5, 1, 1, 5, 1, CAST(N'2019-08-22 17:51:33.733' AS DateTime), 5)
SET IDENTITY_INSERT [dbo].[tblApplicantRating] OFF
SET IDENTITY_INSERT [dbo].[tblApplicants] ON 

INSERT [dbo].[tblApplicants] ([ApplicantID], [PositionID], [CandidateID], [RecruiterComments], [NoticePeriodDays], [CurrentSalary], [ExpectedSalary], [RaisePercentage], [Last_Appraised_On], [IsBuyout], [BuyoutCost], [Stage], [createdate], [updatedate], [addedby]) VALUES (1, 1, 2, NULL, 30, 1.5, 2, 33.333333333333343, N'Jan-2019', N'Not Sure', 0, N'2', CAST(N'2019-08-22 17:44:24.387' AS DateTime), NULL, 4)
INSERT [dbo].[tblApplicants] ([ApplicantID], [PositionID], [CandidateID], [RecruiterComments], [NoticePeriodDays], [CurrentSalary], [ExpectedSalary], [RaisePercentage], [Last_Appraised_On], [IsBuyout], [BuyoutCost], [Stage], [createdate], [updatedate], [addedby]) VALUES (2, 1, 3, NULL, 60, 2, 2.5, 25, N'Aug-2019', N'Not Sure', 0, N'1', CAST(N'2019-08-22 17:46:35.050' AS DateTime), NULL, 4)
SET IDENTITY_INSERT [dbo].[tblApplicants] OFF
SET IDENTITY_INSERT [dbo].[tblInterviewRounds] ON 

INSERT [dbo].[tblInterviewRounds] ([RoundID], [ApplicantID], [CandidateID], [RoundType], [Interviewer], [SheduledOn], [InterviewStatus], [InterviewResult], [Rating], [createdate], [updatedate], [addedby], [updatedby]) VALUES (1, 1, 2, N'Telephonic', 5, CAST(N'2019-08-24 05:42:00.000' AS DateTime), 6, 4, 2.6, CAST(N'2019-08-22 17:44:24.387' AS DateTime), CAST(N'2019-08-22 17:51:33.730' AS DateTime), 4, 5)
INSERT [dbo].[tblInterviewRounds] ([RoundID], [ApplicantID], [CandidateID], [RoundType], [Interviewer], [SheduledOn], [InterviewStatus], [InterviewResult], [Rating], [createdate], [updatedate], [addedby], [updatedby]) VALUES (2, 2, 3, N'Telephonic', 5, CAST(N'2019-09-07 05:46:00.000' AS DateTime), 1, NULL, NULL, CAST(N'2019-08-22 17:46:35.053' AS DateTime), NULL, 4, NULL)
SET IDENTITY_INSERT [dbo].[tblInterviewRounds] OFF
SET IDENTITY_INSERT [dbo].[tblJobDescription] ON 

INSERT [dbo].[tblJobDescription] ([JobID], [PositionID], [JobDescription], [AddedBy], [CreateDate], [UpdatedBy], [UpdatedDate], [ApproveStatus], [ApprovedBy], [ApprovedDate]) VALUES (1, 1, N'&lt;p&gt;&amp;nbsp;df dafdasf asd f&lt;/p&gt;', 4, CAST(N'2019-08-22 17:25:52.400' AS DateTime), 5, CAST(N'2019-08-22 17:29:04.997' AS DateTime), 2, 5, CAST(N'2019-08-22 17:29:07.600' AS DateTime))
INSERT [dbo].[tblJobDescription] ([JobID], [PositionID], [JobDescription], [AddedBy], [CreateDate], [UpdatedBy], [UpdatedDate], [ApproveStatus], [ApprovedBy], [ApprovedDate]) VALUES (2, 2, N'&lt;p&gt;sd fasdf asdf&lt;/p&gt;', 4, CAST(N'2019-08-22 17:26:03.430' AS DateTime), NULL, NULL, 2, 5, CAST(N'2019-08-22 17:29:12.970' AS DateTime))
SET IDENTITY_INSERT [dbo].[tblJobDescription] OFF
SET IDENTITY_INSERT [dbo].[tblMasterCandidatePool] ON 

INSERT [dbo].[tblMasterCandidatePool] ([CandidateID], [Name], [Gender], [Experience], [EmailID], [Phone], [Education], [Year_of_Passing], [InterestedDepartmentID], [ProfessionalSummary], [LastCompany], [PrimaryIndustry], [PreviousNotableOrganisations], [CandidateStatus], [Source], [SourcingDate], [LastContactedDate], [ResumeGoogleLink], [ResumeName], [createdate], [updatedate], [addedby], [Iscandidate], [ApplicantDate], [LastApplicantID], [Active]) VALUES (1, N'Rahul Gupta', N'Male', 2, N'rahul@gmal.com', N'9874563210', N'm-tech', N'2018', 1, N'&lt;p&gt;SF CSD FSA&lt;/p&gt;', NULL, NULL, NULL, N'Working', N'Linked in', CAST(N'2019-08-22 00:00:00.000' AS DateTime), CAST(N'2019-02-02 00:00:00.000' AS DateTime), N'http://google.com', N'Vivek Gupta637020911082707028.pdf', CAST(N'2019-08-22 17:18:28.330' AS DateTime), NULL, 4, 0, NULL, NULL, 0)
INSERT [dbo].[tblMasterCandidatePool] ([CandidateID], [Name], [Gender], [Experience], [EmailID], [Phone], [Education], [Year_of_Passing], [InterestedDepartmentID], [ProfessionalSummary], [LastCompany], [PrimaryIndustry], [PreviousNotableOrganisations], [CandidateStatus], [Source], [SourcingDate], [LastContactedDate], [ResumeGoogleLink], [ResumeName], [createdate], [updatedate], [addedby], [Iscandidate], [ApplicantDate], [LastApplicantID], [Active]) VALUES (2, N'Dinesh Singh', N'Male', 2, N'Dinesh@gmal.com', N'9874563210', N'm-tech', N'2018', 1, N'&lt;p&gt;SF CSD FSA&lt;/p&gt;', NULL, NULL, NULL, N'Working', N'Linked in', CAST(N'2019-08-22 00:00:00.000' AS DateTime), CAST(N'2019-02-02 00:00:00.000' AS DateTime), N'http://google.com', N'Vivek Gupta637020911082707028.pdf', CAST(N'2019-08-22 17:18:28.330' AS DateTime), NULL, 4, 1, CAST(N'2019-08-22 17:44:24.423' AS DateTime), 1, 0)
INSERT [dbo].[tblMasterCandidatePool] ([CandidateID], [Name], [Gender], [Experience], [EmailID], [Phone], [Education], [Year_of_Passing], [InterestedDepartmentID], [ProfessionalSummary], [LastCompany], [PrimaryIndustry], [PreviousNotableOrganisations], [CandidateStatus], [Source], [SourcingDate], [LastContactedDate], [ResumeGoogleLink], [ResumeName], [createdate], [updatedate], [addedby], [Iscandidate], [ApplicantDate], [LastApplicantID], [Active]) VALUES (3, N'Pushkar', N'Male', 2, N'Pushkar@gmal.com', N'9874563210', N'm-tech', N'2018', 1, N'&lt;p&gt;SF CSD FSA&lt;/p&gt;', NULL, NULL, NULL, N'Working', N'Linked in', CAST(N'2019-08-22 00:00:00.000' AS DateTime), CAST(N'2019-02-02 00:00:00.000' AS DateTime), N'http://google.com', N'Vivek Gupta637020911082707028.pdf', CAST(N'2019-08-22 17:18:28.330' AS DateTime), NULL, 4, 1, CAST(N'2019-08-22 17:46:35.063' AS DateTime), 2, 0)
INSERT [dbo].[tblMasterCandidatePool] ([CandidateID], [Name], [Gender], [Experience], [EmailID], [Phone], [Education], [Year_of_Passing], [InterestedDepartmentID], [ProfessionalSummary], [LastCompany], [PrimaryIndustry], [PreviousNotableOrganisations], [CandidateStatus], [Source], [SourcingDate], [LastContactedDate], [ResumeGoogleLink], [ResumeName], [createdate], [updatedate], [addedby], [Iscandidate], [ApplicantDate], [LastApplicantID], [Active]) VALUES (4, N'Vikas', N'Male', 2, N'vikas@gmal.com', N'9874563210', N'm-tech', N'2018', 1, N'&lt;p&gt;SF CSD FSA&lt;/p&gt;', NULL, NULL, NULL, N'Working', N'Linked in', CAST(N'2019-08-22 00:00:00.000' AS DateTime), CAST(N'2019-02-02 00:00:00.000' AS DateTime), N'http://google.com', N'Vivek Gupta637020911082707028.pdf', CAST(N'2019-08-22 17:18:28.330' AS DateTime), NULL, 4, 0, NULL, NULL, 0)
INSERT [dbo].[tblMasterCandidatePool] ([CandidateID], [Name], [Gender], [Experience], [EmailID], [Phone], [Education], [Year_of_Passing], [InterestedDepartmentID], [ProfessionalSummary], [LastCompany], [PrimaryIndustry], [PreviousNotableOrganisations], [CandidateStatus], [Source], [SourcingDate], [LastContactedDate], [ResumeGoogleLink], [ResumeName], [createdate], [updatedate], [addedby], [Iscandidate], [ApplicantDate], [LastApplicantID], [Active]) VALUES (5, N'Rakesh', N'Male', 2, N'Rakesh@gmal.com', N'9874563210', N'm-tech', N'2018', 1, N'&lt;p&gt;SF CSD FSA&lt;/p&gt;', NULL, NULL, NULL, N'Working', N'Linked in', CAST(N'2019-08-22 00:00:00.000' AS DateTime), CAST(N'2019-02-02 00:00:00.000' AS DateTime), N'http://google.com', N'Vivek Gupta637020911082707028.pdf', CAST(N'2019-08-22 17:18:28.330' AS DateTime), NULL, 4, 0, NULL, NULL, 0)
INSERT [dbo].[tblMasterCandidatePool] ([CandidateID], [Name], [Gender], [Experience], [EmailID], [Phone], [Education], [Year_of_Passing], [InterestedDepartmentID], [ProfessionalSummary], [LastCompany], [PrimaryIndustry], [PreviousNotableOrganisations], [CandidateStatus], [Source], [SourcingDate], [LastContactedDate], [ResumeGoogleLink], [ResumeName], [createdate], [updatedate], [addedby], [Iscandidate], [ApplicantDate], [LastApplicantID], [Active]) VALUES (6, N'Nicky', N'Male', 2, N'Nicky@gmal.com', N'9874563210', N'm-tech', N'2018', 1, N'&lt;p&gt;SF CSD FSA&lt;/p&gt;', NULL, NULL, NULL, N'Working', N'Linked in', CAST(N'2019-08-22 00:00:00.000' AS DateTime), CAST(N'2019-02-02 00:00:00.000' AS DateTime), N'http://google.com', N'Vivek Gupta637020911082707028.pdf', CAST(N'2019-08-22 17:18:28.330' AS DateTime), NULL, 4, 0, NULL, NULL, 0)
INSERT [dbo].[tblMasterCandidatePool] ([CandidateID], [Name], [Gender], [Experience], [EmailID], [Phone], [Education], [Year_of_Passing], [InterestedDepartmentID], [ProfessionalSummary], [LastCompany], [PrimaryIndustry], [PreviousNotableOrganisations], [CandidateStatus], [Source], [SourcingDate], [LastContactedDate], [ResumeGoogleLink], [ResumeName], [createdate], [updatedate], [addedby], [Iscandidate], [ApplicantDate], [LastApplicantID], [Active]) VALUES (7, N'Ashish', N'Male', 2, N'ashish@gmal.com', N'9874563210', N'm-tech', N'2018', 1, N'&lt;p&gt;SF CSD FSA&lt;/p&gt;', NULL, NULL, NULL, N'Working', N'Linked in', CAST(N'2019-08-22 00:00:00.000' AS DateTime), CAST(N'2019-02-02 00:00:00.000' AS DateTime), N'http://google.com', N'Vivek Gupta637020911082707028.pdf', CAST(N'2019-08-22 17:18:28.330' AS DateTime), NULL, 4, 0, NULL, NULL, 0)
SET IDENTITY_INSERT [dbo].[tblMasterCandidatePool] OFF
SET IDENTITY_INSERT [dbo].[tblMasterDepartment] ON 

INSERT [dbo].[tblMasterDepartment] ([DepartmentID], [DepartmentName]) VALUES (1, N'Business Development')
INSERT [dbo].[tblMasterDepartment] ([DepartmentID], [DepartmentName]) VALUES (2, N'Content')
INSERT [dbo].[tblMasterDepartment] ([DepartmentID], [DepartmentName]) VALUES (3, N'Client Services')
INSERT [dbo].[tblMasterDepartment] ([DepartmentID], [DepartmentName]) VALUES (4, N'Pay Per Click Marketing')
INSERT [dbo].[tblMasterDepartment] ([DepartmentID], [DepartmentName]) VALUES (5, N'Design')
INSERT [dbo].[tblMasterDepartment] ([DepartmentID], [DepartmentName]) VALUES (6, N'Development')
INSERT [dbo].[tblMasterDepartment] ([DepartmentID], [DepartmentName]) VALUES (7, N'SEO')
SET IDENTITY_INSERT [dbo].[tblMasterDepartment] OFF
SET IDENTITY_INSERT [dbo].[tblMasterDesignation] ON 

INSERT [dbo].[tblMasterDesignation] ([DesignationID], [DesignationName], [DepartmentID]) VALUES (1, N'BD Head', 1)
INSERT [dbo].[tblMasterDesignation] ([DesignationID], [DesignationName], [DepartmentID]) VALUES (2, N'BD Manager', 1)
INSERT [dbo].[tblMasterDesignation] ([DesignationID], [DesignationName], [DepartmentID]) VALUES (3, N'PPC Head', 4)
INSERT [dbo].[tblMasterDesignation] ([DesignationID], [DesignationName], [DepartmentID]) VALUES (4, N'Content Writer', 2)
SET IDENTITY_INSERT [dbo].[tblMasterDesignation] OFF
SET IDENTITY_INSERT [dbo].[tblMasterHiringStatus] ON 

INSERT [dbo].[tblMasterHiringStatus] ([HiringID], [StatusName]) VALUES (5, N'No hire')
INSERT [dbo].[tblMasterHiringStatus] ([HiringID], [StatusName]) VALUES (2, N'Interviewing')
INSERT [dbo].[tblMasterHiringStatus] ([HiringID], [StatusName]) VALUES (3, N'Decision needed')
INSERT [dbo].[tblMasterHiringStatus] ([HiringID], [StatusName]) VALUES (1, N'Not started')
INSERT [dbo].[tblMasterHiringStatus] ([HiringID], [StatusName]) VALUES (4, N'Hire')
SET IDENTITY_INSERT [dbo].[tblMasterHiringStatus] OFF
SET IDENTITY_INSERT [dbo].[tblMasterInterviewResult] ON 

INSERT [dbo].[tblMasterInterviewResult] ([StatusID], [Result]) VALUES (1, N'No Hire')
INSERT [dbo].[tblMasterInterviewResult] ([StatusID], [Result]) VALUES (2, N'Probable')
INSERT [dbo].[tblMasterInterviewResult] ([StatusID], [Result]) VALUES (3, N'Worth Consideration')
INSERT [dbo].[tblMasterInterviewResult] ([StatusID], [Result]) VALUES (4, N'Good Candidate')
INSERT [dbo].[tblMasterInterviewResult] ([StatusID], [Result]) VALUES (5, N'Hire')
SET IDENTITY_INSERT [dbo].[tblMasterInterviewResult] OFF
SET IDENTITY_INSERT [dbo].[tblMasterInterviewStatus] ON 

INSERT [dbo].[tblMasterInterviewStatus] ([InterviewStatusID], [StatusName]) VALUES (1, N'Pending')
INSERT [dbo].[tblMasterInterviewStatus] ([InterviewStatusID], [StatusName]) VALUES (2, N'Pending with interviewer')
INSERT [dbo].[tblMasterInterviewStatus] ([InterviewStatusID], [StatusName]) VALUES (3, N'Pending with interviewee')
INSERT [dbo].[tblMasterInterviewStatus] ([InterviewStatusID], [StatusName]) VALUES (4, N'Position on Hold')
INSERT [dbo].[tblMasterInterviewStatus] ([InterviewStatusID], [StatusName]) VALUES (5, N'Position Filled')
INSERT [dbo].[tblMasterInterviewStatus] ([InterviewStatusID], [StatusName]) VALUES (6, N'Done')
SET IDENTITY_INSERT [dbo].[tblMasterInterviewStatus] OFF
SET IDENTITY_INSERT [dbo].[tblMasterRating] ON 

INSERT [dbo].[tblMasterRating] ([RatingID], [RatingName]) VALUES (1, N'Communication')
INSERT [dbo].[tblMasterRating] ([RatingID], [RatingName]) VALUES (2, N'Job Knowledge')
INSERT [dbo].[tblMasterRating] ([RatingID], [RatingName]) VALUES (3, N'Past Experience')
INSERT [dbo].[tblMasterRating] ([RatingID], [RatingName]) VALUES (4, N'Company Fitness')
INSERT [dbo].[tblMasterRating] ([RatingID], [RatingName]) VALUES (5, N'Attitude')
SET IDENTITY_INSERT [dbo].[tblMasterRating] OFF
SET IDENTITY_INSERT [dbo].[tblMasterRole] ON 

INSERT [dbo].[tblMasterRole] ([RoleID], [RoleName]) VALUES (1, N'Admin')
INSERT [dbo].[tblMasterRole] ([RoleID], [RoleName]) VALUES (2, N'HR')
INSERT [dbo].[tblMasterRole] ([RoleID], [RoleName]) VALUES (3, N'User')
INSERT [dbo].[tblMasterRole] ([RoleID], [RoleName]) VALUES (4, N'Recruiter')
SET IDENTITY_INSERT [dbo].[tblMasterRole] OFF
SET IDENTITY_INSERT [dbo].[tblMasterUnit] ON 

INSERT [dbo].[tblMasterUnit] ([unitID], [UnitName]) VALUES (1, N'Envigo')
INSERT [dbo].[tblMasterUnit] ([unitID], [UnitName]) VALUES (2, N'IBISM')
SET IDENTITY_INSERT [dbo].[tblMasterUnit] OFF
SET IDENTITY_INSERT [dbo].[tblPositions] ON 

INSERT [dbo].[tblPositions] ([PositionID], [DesignationID], [RequesterID], [Department], [Unit], [Priority], [TAT], [RequestDate], [ClosedByDate], [Experience], [YearlySalaryBudget], [Status], [JDStatus], [Closure_Date], [SelectedCandidateID], [Remark]) VALUES (1, 1, 5, 1, 1, N'High', CAST(N'2019-08-29 00:00:00.000' AS DateTime), CAST(N'2019-08-22 17:23:39.580' AS DateTime), CAST(N'2019-08-31 00:00:00.000' AS DateTime), 2, 0, N'Open', 2, NULL, NULL, NULL)
INSERT [dbo].[tblPositions] ([PositionID], [DesignationID], [RequesterID], [Department], [Unit], [Priority], [TAT], [RequestDate], [ClosedByDate], [Experience], [YearlySalaryBudget], [Status], [JDStatus], [Closure_Date], [SelectedCandidateID], [Remark]) VALUES (2, 2, 5, 1, 1, N'Medium', CAST(N'2019-09-06 00:00:00.000' AS DateTime), CAST(N'2019-08-22 17:24:36.143' AS DateTime), CAST(N'2019-08-31 00:00:00.000' AS DateTime), 3, 0, N'Open', 2, NULL, NULL, NULL)
SET IDENTITY_INSERT [dbo].[tblPositions] OFF
SET IDENTITY_INSERT [dbo].[tblSuggestedApplicants] ON 

INSERT [dbo].[tblSuggestedApplicants] ([SelectionID], [PositionID], [Candidate], [CurrentStatus], [ShareDate], [ApproveDate], [RejectDate]) VALUES (6, 1, N'1', N'Reject', CAST(N'2019-08-22 17:42:26.757' AS DateTime), NULL, CAST(N'2019-08-22 17:42:42.053' AS DateTime))
INSERT [dbo].[tblSuggestedApplicants] ([SelectionID], [PositionID], [Candidate], [CurrentStatus], [ShareDate], [ApproveDate], [RejectDate]) VALUES (7, 1, N'2', N'Approve', CAST(N'2019-08-22 17:42:26.760' AS DateTime), CAST(N'2019-08-22 17:42:38.960' AS DateTime), NULL)
INSERT [dbo].[tblSuggestedApplicants] ([SelectionID], [PositionID], [Candidate], [CurrentStatus], [ShareDate], [ApproveDate], [RejectDate]) VALUES (8, 1, N'3', N'Approve', CAST(N'2019-08-22 17:42:26.760' AS DateTime), CAST(N'2019-08-22 17:42:40.457' AS DateTime), NULL)
SET IDENTITY_INSERT [dbo].[tblSuggestedApplicants] OFF
SET IDENTITY_INSERT [dbo].[tblUserLogin] ON 

INSERT [dbo].[tblUserLogin] ([UserID], [UserEmail], [UserName], [LastLoginDate], [Createdate], [Status], [Role], [ActivatedDate]) VALUES (4, N'vivekgupta0505@gmail.com', N'Vivek Gupta', NULL, CAST(N'2019-08-22 16:47:03.023' AS DateTime), 1, 4, CAST(N'2019-08-22 16:49:24.367' AS DateTime))
INSERT [dbo].[tblUserLogin] ([UserID], [UserEmail], [UserName], [LastLoginDate], [Createdate], [Status], [Role], [ActivatedDate]) VALUES (5, N'ajeet.kumar@envigo.co.uk', N'Ajeet Kumar', NULL, CAST(N'2019-08-22 16:48:23.100' AS DateTime), 1, 3, CAST(N'2019-08-22 16:48:55.803' AS DateTime))
INSERT [dbo].[tblUserLogin] ([UserID], [UserEmail], [UserName], [LastLoginDate], [Createdate], [Status], [Role], [ActivatedDate]) VALUES (3, N'vivek@envigo.co.uk', N'Vivek Gupta', NULL, CAST(N'2019-08-22 16:14:12.570' AS DateTime), 1, 1, CAST(N'2019-02-02 00:00:00.000' AS DateTime))
SET IDENTITY_INSERT [dbo].[tblUserLogin] OFF
/****** Object:  StoredProcedure [dbo].[proc_tblApplicantRating]    Script Date: 8/22/2019 5:58:19 PM ******/
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
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<6)
Begin
update tblInterviewRounds set 
InterviewStatus=@InterviewStatus,

updatedate=current_TimeStamp,
updatedby=@UserID
where RoundID=@RoundID
end
end

GO
/****** Object:  StoredProcedure [dbo].[proc_tblApplicants]    Script Date: 8/22/2019 5:58:19 PM ******/
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=@interviewType,
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 
where CandidateID=@CandidateID
end

GO
/****** Object:  StoredProcedure [dbo].[proc_tblJobDescription]    Script Date: 8/22/2019 5:58:19 PM ******/
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: 8/22/2019 5:58:19 PM ******/
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: 8/22/2019 5:58:19 PM ******/
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: 8/22/2019 5:58:19 PM ******/
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
end
end

GO
/****** Object:  StoredProcedure [dbo].[proc_tblSuggestedApplicants]    Script Date: 8/22/2019 5:58:19 PM ******/
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: 8/22/2019 5:58:19 PM ******/
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: 8/22/2019 5:58:19 PM ******/
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_Select]    Script Date: 8/22/2019 5:58:19 PM ******/
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: 8/22/2019 5:58:19 PM ******/
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: 8/22/2019 5:58:19 PM ******/
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
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[40] 4[20] 2[20] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "t"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 203
               Right = 287
            End
            DisplayFlags = 280
            TopColumn = 18
         End
         Begin Table = "t1"
            Begin Extent = 
               Top = 6
               Left = 325
               Bottom = 211
               Right = 509
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vwCandidatepool'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=1 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'vwCandidatepool'
GO
