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

?