Login Test for MVC Web Project

----------------------22----------------

/****** Object: StoredProcedure [Leave].[SP_Leave_AnnualLeaveStatus] Script Date: 22-Sep-2015 10:59:58 AM ******/
DROP PROCEDURE [Leave].[SP_Leave_AnnualLeaveStatus]
GO

/****** Object: StoredProcedure [Leave].[SP_Leave_AnnualLeaveStatus] Script Date: 22-Sep-2015 10:59:58 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO




-- EXEC [Leave].[SP_Leave_AnnualLeaveStatus] '0001','22-Sep-2015','00010001','%','%','%','%','%','%','%','%','%','00000002'
CREATE PROCEDURE [Leave].[SP_Leave_AnnualLeaveStatus]
@ClientCode VARCHAR(04),
@UpToDate date,
@IdentityCode VARCHAR(08)='%',
@JobStatusID varchar(1) = '%',
@JobBaseID varchar(1) = '%',
@DesignationID varchar(5) = '%',
@JobLevelId varchar(3) = '%',
@FunctionalDesignationID varchar(5) = '%',
@ProjectID varchar(3)='%',
@AreaID varchar(4)='%',
@UnitID varchar(4)='%',
@DivisionID varchar (4)='%',
@EmployeeID VARCHAR(08) = '%'


--With Encryption

AS

Set nocount ON

Set nocount ON
Declare @NextDate date


DECLARE @PIN AS NCHAR(8)
DECLARE @DayOffName AS NCHAR(25)

SELECT * INTO #EmployeeInfo
FROM [Employee].[GetEmployeeStatusSummaryInformationAsOnDate] (@UpToDate,@ClientCode,@IdentityCode,@JobStatusID,@JobBaseID,@DesignationID,@JobLevelId,@FunctionalDesignationID,@ProjectID,@AreaID,@UnitID,@DivisionID,@EmployeeID) e



Select E.*,isnull(tk.AvailedThisYear,0)AvailedThisYear,isnull(cl.LeaveBalance,0)LeaveBalance,isnull(cl.ExcessLeaveBalance,0)ExcessLeaveBalance,isnull(cl.LeaveBalance,0)+isnull(cl.ExcessLeaveBalance,0)ToalBalance
From #EmployeeInfo E
Inner join [Leave].[EmployeeWiseLeaveGroupMapping] lg on lg.IdentityCode=e.IdentityCode And lg.EmployeeId=e.EmployeeID
inner join
(
Select IdentityCode,EmployeeId,Max(EffectiveDate)EffectiveDate from [Leave].[EmployeeWiseLeaveGroupMapping] where EffectiveDate <=@UpToDate Group By IdentityCode,EmployeeId
) mxlg on mxlg.IdentityCode=lg.IdentityCode And mxlg.EmployeeId=lg.EmployeeId And mxlg.EffectiveDate=lg.EffectiveDate
Inner join [Leave].[LeaveGroupSetup] g on g.IdentityCode=lg.IdentityCode And g.LeaveGroupId=lg.LeaveGroupId
inner join [Leave].[LeaveTypeSetup] l on l.IdentityCode=g.IdentityCode And l.LeaveTypeId=g.LeaveTypeId
Left join [Leave].[EmployeeCurrentLeaveStatus] cl on l.IdentityCode=e.IdentityCode And cl.EmployeeId=e.EmployeeID And cl.LeaveTypeId=l.LeaveTypeId
Left Join
(
select IdentityCode,EmployeeId,LeaveTypeId,Sum(TotalLeaveDays) AvailedThisYear from [Leave].[EmployeeLeaveTaken] where [ApplicationApprovedYN]=1 And Year([LeaveFrom])=Year(@UpToDate) Group By IdentityCode,EmployeeId,LeaveTypeId
) tk on tk.IdentityCode=e.IdentityCode And tk.EmployeeId=e.EmployeeID And tk.LeaveTypeId=l.LeaveTypeId

Order By E.IdentityCode,E.EmployeeID





GO