Hi ,
Hii ,
ALTER PROCEDURE [dbo].[usp_Get121ReportForMasterAdmin]
(
@DataForYear Int,
@CompanyId Int,
@ManagerId Int,
@BusinessUnitId Int,
@DepartmentId Int,
@TeamId Int
)
As
Begin
if (@BusinessUnitId <= 0)
begin
set @DepartmentId = 0
set @TeamId = 0
end
if (@DepartmentId <= 0)
begin
set @TeamId = 0
end
-- get company business year period
DECLARE @businessYearStart as date
DECLARE @businessYearEnd as date
exec usp_GetCompanyBusinessYearPeriod
@compID = @CompanyId,
@DataForYear = @DataForYear,
@businessYear_end = @businessYearEnd OUTPUT,
@businessYear_start = @businessYearStart OUTPUT
Declare @IncludeTestEmployeeInReport bit
Declare @IncludeHRAdminInReport bit
set @IncludeTestEmployeeInReport=isnull((Select IncludeTestEmployeeInReport from Company where id=@CompanyId),0)
set @IncludeHRAdminInReport=isnull((Select IncludeHRAdminInReport from Company where id=@CompanyId),0)
declare @finalTable as table
(
rowID int identity(1, 1),
EmpId int,
Companyname nvarchar(max),
PlanSubject nvarchar(max),
PlanType nvarchar(50),
PlanDate datetime,
[Status] nvarchar(20),
EmployeeName nvarchar(50),
ManagerName nvarchar(50)
)
insert into @finalTable
Select
EmpId,
(Select Name from Company where Id = CompanyId) Companyname,PlanSubject,PlanType,PlanDate,[Status],EmployeeName,
ManagerName From
(
Select e.Id as EmpId ,p.Comp_Id CompanyId,
(Select p.Plan_Subject) as PlanSubject,
(Select Case p.[121Type] when 1 then 'Virtual' when 2 then 'Face to Face' Else '' end) as PlanType,
(Select p.Paln_Date) as PlanDate,
(Select Case p.IsClosed when 0 then 'Planned' when 1 then 'Published' Else '' end) as [Status],
(Select e.LastName + ' ' + e.FirstName) EmployeeName,
(Select
case when ((Select count(*) from CurrentManagerLogDetails where EmployeeId=e.Id and DataForYear=@DataForYear)=0)
then
(Select LastName + ' ' + FirstName from Employee where Id = e.ManagerId)
else
(Select LastName + ' ' + FirstName from Employee where Id = (Select CurrentManagerId from CurrentManagerLogDetails
where EmployeeId=e.Id and DataForYear=@DataForYear))
end) ManagerName
from [Plan] p
inner join Employee e
on p.PlanAddedFor_Id = e.Id
where p.Comp_Id = Company_Id
and p.IsDeleted=0
and p.Comp_Id = @CompanyId
and e.Status_Id=0
and ((@IncludeTestEmployeeInReport <> 1) and (e.Id in (Select Id from Employee with (nolock)
where isnull(IsTest,0) <> 1 and Company_Id=@CompanyId)) or @IncludeTestEmployeeInReport=1)
and e.IsDeleted = 0
and ((@IncludeHRAdminInReport <> 1) and (e.Id in (Select Id from Employee with (nolock) where Role_Id <> 4
and Company_Id=@CompanyId )) or @IncludeHRAdminInReport=1)
and isnull(p.Paln_Date, @businessYearStart) between @businessYearStart and @businessYearEnd
and ( isnull(@ManagerId,0)=0 or e.ManagerId = @ManagerId)
and (isnull(@BusinessUnitId,0)=0 or e.BusinessUnitId = @BusinessUnitId)
and (isnull(@DepartmentId,0)=0 or e.DepartmentId = @DepartmentId)
and (isnull(@TeamId,0)=0 or e.TeamId = @TeamId )
)f
order by ManagerName,EmployeeName
declare @totalRecords as int
set @totalRecords = (select COUNT(*) from @finalTable)
if(@totalRecords > 0)
begin
declare @counter as int
declare @tmpID as int
declare @tmpMgrName as nvarchar(100)
set @counter = 1
while (@counter <= @totalRecords)
begin
set @tmpID = (select EmpId from @finalTable where rowID = @counter)
EXEC usp_GetManagerNameByYear @empId = @tmpID, @compId = @CompanyId, @dataForYear= @DataForYear, @returnManagerName = @tmpMgrName output
update @finalTable set ManagerName = ISNULL(@tmpMgrName, ManagerName) where rowID = @counter and EmpId = @tmpID
set @counter = @counter + 1
end
end
select
Companyname ,
PlanSubject ,
PlanType ,
PlanDate ,
[Status] ,
EmployeeName,
ManagerName
from @finalTable
order by ManagerName, EmployeeName
End
GO
this is my procedure
when i try to drag on drop on my linq orm it gives me an error that return type of the procedure could not be detected ..
Please suggest .. i tried to set autotdetect for this by going to properties of this procedure .. but not able to ..
Please suggest