SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

ALTER             PROCEDURE spITM_CheckPaperList(
@intCount  INT Output  ,
@chvKeywords            VARCHAR(100),            --关键字
@chvinterunittypeid             varchar(100),
 @dtmdatelowerlimit               DATETIME ,
@dtmdateupperlimit              DATETIME ,
@isspecialbuy             int,
@checkresultid            int,
  @intAudit                         INT,                                --审批状态
@chvEmployeeTypeID VARCHAR(100) ,  --职员id
@bitViewPersonalLimit  BIT,  --浏览个人
@bitViewDepartmentLimit BIT,  --浏览部门
@bitEmployeeIsManager  BIT,  --浏览全部
   @chvOrgTypeID            VARCHAR(100), 
@intPageSize  INT  ,
@intPageNo  INT  
)
AS
--权限控制条件开始
DECLARE @chvEmployeeDepartment  VARCHAR(36)
DECLARE @bitEmployeeIsAdmin  BIT
SELECT @chvEmployeeDepartment = '000'

IF EXISTS(SELECT TypeID FROM CRM_Employee WHERE TypeID = @chvEmployeeTypeID)
 AND
 EXISTS(SELECT Count(*) FROM CRM_LoginUser WHERE InnerObject = 'Employee' AND InnerObjectTypeID = @chvEmployeeTypeID)
BEGIN
 --SELECT @chvEmployeeDepartment = [Department] FROM [CRM_Employee] WHERE [TypeID] = @chvEmployeeTypeID
 SELECT @bitEmployeeIsAdmin = IsAdmin FROM CRM_LoginUser WHERE InnerObject = 'Employee' AND InnerObjectTypeID = @chvEmployeeTypeID
 --deal  empty 2007/1/22
 SELECT @chvEmployeeDepartment = case @chvEmployeeDepartment when ' ' then  '000' else  @chvEmployeeDepartment end
END
ELSE
BEGIN
 SELECT * FROM ITM_CheckPaper  WHERE TypeID = '0' --返回一个空的记录集
 RETURN
END
--权限控制条件结束--
select a.*  ,b.Name VendorName,  ww.Name CheckPaperName,
w.Name CreatorName
  INTO #ITM_CheckPaperListPageTable
   from ITM_CheckPaper as  a 
             left JOIN CRM_Employee w      ON a.CreatorTypeID = w.TypeID
       left join CRM_Employee ww on a.CHECKERTYPEID=ww.Typeid
  LEFT JOIN scm_Vendor b ON a.VendorTypeID = b.TypeID
WHERE
 a.OrgTypeID = @chvOrgTypeID and
(
 a.SERIALNUMBER     like '%'+@chvKeywords+'%' or
 a.ContractNo                 like '%'+@chvKeywords+'%' or
b.Name                 like '%'+@chvKeywords+'%' or
 a.CheckSite     like '%'+@chvKeywords+'%'
)
--and
--a.AuditFlag = case @intAudit
  ---                        when 0 then a.AuditFlag
  --                       else @intAudit
  --                    end
--权限控制条件开始--
AND
(
 @bitViewDepartmentLimit = 1
 AND
 (
 ISNULL(w.[Department],'001') LIKE ISNULL(@chvEmployeeDepartment,'not the same') + '%'
 OR
 ISNULL(w.[Department2],'001') LIKE ISNULL(@chvEmployeeDepartment,'not the same') + '%'
 OR
 ISNULL(w.[Department3],'001') LIKE  ISNULL(@chvEmployeeDepartment,'not the same') + '%'
 )
 OR
 a.[CreatorTypeID] = @chvEmployeeTypeID  AND  @bitViewPersonalLimit = 1
 OR
 @bitEmployeeIsAdmin = 1
 OR
 @bitEmployeeIsManager = 1
 OR
 a.[EmployeeRange] LIKE '%' +@chvEmployeeTypeID +'%'
)
--权限控制条件结束--
--Order by a.CreateDate desc
---------------判断是否取记录数
if @intCount = 1
Begin
 SELECT @intCount=Count(0)
 FROM #ITM_CheckPaperListPageTable;
End
--------------------------取记录数完成
DECLARE @chvSql  VARCHAR(1000)
--处理大于总页数时的请求页数
DECLARE @intPageCount INT
SELECT @intPageCount = (@intCount + @intPageSize-1) / @intPageSize;
IF @intPageNo > 1 AND @intPageNo > @intPageCount
 SELECT @intPageNo = @intPageCount
ELSE IF @intPageNo > @intPageCount
 SELECT @intPageNo = 1
--处理大于总页数时的请求页数结束
Select @chvSql = 'SELECT TOP ' + Str(@intPageSize) + '  *  ' +
' FROM #ITM_CheckPaperListPageTable a ' +
' WHERE a.TypeID not in ' +
' (select top ' + Str((@intPageNo - 1) * @intPageSize) + ' TypeID from #ITM_CheckPaperListPageTable )'
Exec(@chvSql)
Drop Table #ITM_CheckPaperListPageTable

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO