CREATE Procedure [dbo].[GetContacts]
@pageIndex INT,
@pageSize INT,
@searchText NVARCHAR(500),
@orderField NVARCHAR(100),
@UserId UniqueIdentifier,
@AccountId NVARCHAR(500)
AS
DECLARE @sqlScript NVARCHAR(1000), @sqlScriptMax NVARCHAR(1000)
DECLARE @Start INT, @End INT, @MaxRecord INT
SET @Start = (@pageIndex-1)*@pageSize
SET @End = @Start+@pageSize
IF LEN(@orderField)$=0
BEGIN
SET @orderField=NULL
END
DECLARE @permission SMALLINT
DECLARE @UserTypeId int
SELECT @permission=PermissionId FROM ClientUsers WHERE UserId=@UserId
SELECT @UserTypeId = ISNULL(UserTypeId, 0 ) FROM ClientUsers WHERE UserId=@UserId
if @AccountId $$ ''
begin
IF @UserTypeId = 2
BEGIN
SET @sqlScript='SELECT ROW_NUMBER() OVER (ORDER BY '+ISNULL(@orderField,'UserId')+') As Row, ContactId, AccountId, AccountName, Salutation, FirstName, LastName, DesignationId,
DepartmentId,Department,Designation, Email, ReportTo, MobileNumber, Fax,
AssistantName, AssistantPhone, IsSendEmailEnabled,Status,CreatedOn, CreatedBy, LastModifiedOn, LastModifiedBy ,AccountOwner ,AccountOwnerMobile
FROM vw_Contacts Where Status = 1 AND AccountId IN(SELECT AccountId FROM Account WHERE Status=1 and AccountId='+@AccountId+' AND ServiceAccountOwner IN(SELECT UserId FROM GetUserHierarchy('''
+ CAST(@userID AS VARCHAR(255)) +''')))'
END
ELSE IF (@UserTypeId = 1 OR @UserTypeId = 0)
BEGIN
IF @permission=3 OR @permission=4 OR @permission=5
BEGIN
SET @sqlScript='SELECT ROW_NUMBER() OVER (ORDER BY '+ISNULL(@orderField,'UserId')+') As Row, ContactId, AccountId, AccountName, Salutation, FirstName, LastName, DesignationId,
DepartmentId,Department,Designation, Email, ReportTo, MobileNumber, Fax,
AssistantName, AssistantPhone, IsSendEmailEnabled,Status,CreatedOn, CreatedBy, LastModifiedOn, LastModifiedBy ,AccountOwner ,AccountOwnerMobile
FROM vw_Contacts Where Status = 1 AND AccountId IN(SELECT AccountId FROM Account WHERE Status=1 and AccountId='+@AccountId+' )'
END
ELSE
BEGIN
SET @sqlScript='SELECT ROW_NUMBER() OVER (ORDER BY '+ISNULL(@orderField,'UserId')+') As Row, ContactId, AccountId, AccountName, Salutation, FirstName, LastName, DesignationId,
DepartmentId,Department,Designation, Email, ReportTo, MobileNumber, Fax,
AssistantName, AssistantPhone, IsSendEmailEnabled,Status,CreatedOn, CreatedBy, LastModifiedOn, LastModifiedBy ,AccountOwner ,AccountOwnerMobile
FROM vw_Contacts Where Status = 1 AND AccountId IN(SELECT AccountId FROM Account WHERE Status=1 and AccountId='+ @AccountId +' AND AccountOwner IN(SELECT UserId FROM GetUserHierarchy('''
+ CAST(@userID AS VARCHAR(255)) +''')))'
END
END
end
else
begin
IF @UserTypeId = 2
BEGIN
SET @sqlScript='SELECT ROW_NUMBER() OVER (ORDER BY '+ISNULL(@orderField,'UserId')+') As Row, ContactId, AccountId, AccountName, Salutation, FirstName, LastName, DesignationId,
DepartmentId,Department,Designation, Email, ReportTo, MobileNumber, Fax,
AssistantName, AssistantPhone, IsSendEmailEnabled,Status,CreatedOn, CreatedBy, LastModifiedOn, LastModifiedBy ,AccountOwner ,AccountOwnerMobile
FROM vw_Contacts Where Status = 1 AND AccountId IN(SELECT AccountId FROM Account WHERE Status=1 AND ServiceAccountOwner IN(SELECT UserId FROM GetUserHierarchy('''
+ CAST(@userID AS VARCHAR(255)) +''')))'
END
ELSE IF (@UserTypeId = 1 OR @UserTypeId = 0)
BEGIN
SET @sqlScript='SELECT ROW_NUMBER() OVER (ORDER BY '+ISNULL(@orderField,'UserId')+') As Row, ContactId, AccountId, AccountName, Salutation, FirstName, LastName, DesignationId,
DepartmentId,Department,Designation, Email, ReportTo, MobileNumber, Fax,
AssistantName, AssistantPhone, IsSendEmailEnabled,Status,CreatedOn, CreatedBy, LastModifiedOn, LastModifiedBy ,AccountOwner ,AccountOwnerMobile
FROM vw_Contacts Where Status = 1 AND AccountId IN(SELECT AccountId FROM Account WHERE Status=1 AND AccountOwner IN(SELECT UserId FROM GetUserHierarchy('''
+ CAST(@userID AS VARCHAR(255)) +''')))'
END

END
end
-- Set the max record.
SET @sqlScriptMax = 'SELECT COUNT(1) FROM ('+@sqlScript+')AS NewTable'

IF(@searchText IS NOT NULL AND LEN(@searchText)$0)
BEGIN
SET @sqlScript = @sqlScript +' AND '+@searchText
SET @sqlScriptMax = @sqlScriptMax +' WHERE '+@searchText
END

-- Set the paging.
SET @sqlScript = 'SELECT * FROM ('+@sqlScript+') AS NewTable WHERE Row $ '+CAST(@Start AS VARCHAR(10))+' AND Row $= '+CAST(@End AS VARCHAR(10))

--SELECT @sqlScript
--SELECT @sqlScriptMax

EXEC (@sqlScript)-- List users
EXEC(@sqlScriptMax)-- Maximum Record

свернуть