USE [MJ2009]
GO
/****** Object: StoredProcedure [dbo].[p_OrderInfo_Select] Script Date: 2014/4/3 16:17:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: cheng
-- Create date: 2012-06-12
-- Description: 订单表分页查询
-- =============================================
ALTER PROCEDURE [dbo].[p_OrderInfo_Select] (
@where NVARCHAR(4000),
@order NVARCHAR(200),
@pagesize INT,
@page INT,
@totalcount INT OUTPUT,
@userID INT,
@departID VARCHAR(50),
@Export INT = 0
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @rights VARCHAR(8000)
DECLARE @rightResult VARCHAR(8000)
SET @rightResult = dbo.fn_GetRightStr(@userID, @departID)
IF ( @userID = -1 OR @rightResult = 'all' )--管理员，不做任何操作
BEGIN
SET @rights = ''
END
ELSE
begin
IF ( @rightResult = '' )--本人权限
BEGIN
SET @rights = ' AND (t.DutyUser=' + CONVERT(VARCHAR(100), @userID)
+ ' OR t.CreateUserID=' + CONVERT(VARCHAR(100), @userID) + ')'
END
ELSE --设置权限
BEGIN
SET @rights = ' AND (t.DepartID IN (' + @rightResult
+ ') OR t.DutyUser=' + CONVERT(VARCHAR(100), @userID)
+ ' OR t.CreateUserID=' + CONVERT(VARCHAR(100), @userID) + ')'
END
end
DECLARE @s VARCHAR(8000);
SET @s = '
SELECT t.[AdDateCode]
,t.[OrderType]
,t.[CustID]
,t.[CustName]
,t.[AgentCustID]
,t.[AgentCustName]
,t.[DepartId]
,t.[Status]
,t.[ExpiresTime]
,t.[ProvinceID]
,t.[CityID]
,t.[CountyID]
,t.[CreateUserID]
,t.[ContractCode]
,t.[DutyUser]
,t.[UseStyle]
,t.[CreateTime]
,t.[ProjectType]
,t.[ProjectID]
,t.[ProjectName]
,t.[BeginTime]
,t.[EndTime]
,t.[Amount]
,t.[LastUpdateTime]
,t.[ConfirmTime]
,t.[Price]
,t.[MemberCode]
,t.[Discount]
,t.[Quantity]
,t.[TotalPrice]
,t.[RelationID]
,t.[Remark]
,t.[BatchNumber]
,t.[ProductID]
,t.[interfacestatus]
,t.PriceUnit
,t.FreeQuantity
,t.CustomerFullName
,t.CustomerMobilePhone
,t.CustUsersEmail
,t.[ConfirmTime] AS OrderConfirmTime
,server.ServiceStatus as ServiceStatus
,ui.truename AS dutyUsertrueName
,uic.truename AS createUsertrueName
,dp.departName AS departname
,et.description AS description
,et2.description AS OrderTypeName'
IF (@Export = 1)
BEGIN
SET @s = @s + ',(CASE WHEN (OrderType=6002 AND ProjectType=2)
THEN ISNULL((SELECT TOP 1 TrueName FROM v_UserInfo JOIN JiCaiProjectMember ON JiCaiProjectMember.ImportUserID=v_UserInfo.UserID
JOIN JICaiProjectBatch ON JiCaiProjectBatch.BatchID=JiCaiProjectMember.BatchID
WHERE JiCaiProjectMember.Status>=0 AND JiCaiProjectMember.MemberCode=t.MemberCode AND CONVERT(VARCHAR(100),JiCaiProjectBatch.ProjectID)=t.ProjectID),'''')
ELSE '''' END) AS ImportUser'
END
SET @s = @s + 'YanFaFROM [orderinfo] t
LEFT JOIN department dp ON dp.departid=t.DepartID
LEFT JOIN EnumType et on et.EnumTypeID=t.Status
LEFT JOIN userinfo ui on ui.userid=t.DutyUser
LEFT JOIN userinfo uic on uic.userid=t.createUserid
LEFT JOIN EnumType et2 on et2.EnumTypeID=t.ordertype
left join OrderService server on server.AdDateCode=t.AdDateCode
WHERE t.Status>=0 AND t.IsUse=0 ' + @where + @rights
--PRINT @s
EXEC p_page @s,@order,@page,@pagesize,@totalcount OUTPUT
END
--------------------------------------------------------------------------函数
USE [MJ2009]
GO
/****** Object: UserDefinedFunction [dbo].[fn_GetRightStr] Script Date: 2014/4/3 16:21:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fn_GetRightStr]
(
@userid int ,@departid varchar(50)
)
RETURNS varchar(8000)
AS
BEGIN
-- Declare the return variable here
DECLARE @RightStr varchar(8000)
declare @RightType int
select @RightType=isnull(RightType,0) from dbo.UserOrderDataRigth where userid=@userid
if(isnull(@RightType,0)=0)--本人
begin
set @RightStr=''
end
else if(@RightType=4)--管理员
begin
set @RightStr='all'
end
else if(@RightType=1)--本部门
begin
set @RightStr='select '''+@departid + ''''
end
else if(@RightType=2)--本部门及子部门
begin
set @RightStr='select id from [dbo].[f_GetChildDepartid]('''+@departid+''')'
end
else if(@RightType=3)--指定部门
begin
set @RightStr='SELECT DepartID FROM UserDepMentMapping WHERE UserID='+Convert(varchar(10),@UserID)
end
RETURN @RightStr
END
