if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ChaokersCN_SysMembership_AddModule]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ChaokersCN_SysMembership_AddModule]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ChaokersCN_SysMembership_AddPrivilege]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ChaokersCN_SysMembership_AddPrivilege]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ChaokersCN_SysMembership_AddRole]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ChaokersCN_SysMembership_AddRole]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ChaokersCN_SysMembership_DeletePrivilege]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ChaokersCN_SysMembership_DeletePrivilege]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ChaokersCN_SysMembership_GetPrivilegeAllParentNodes]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ChaokersCN_SysMembership_GetPrivilegeAllParentNodes]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ChaokersCN_SysMembership_PrivilegeSort]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ChaokersCN_SysMembership_PrivilegeSort]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ChaokersCN_SysMembership_UpdatePrivilege]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[ChaokersCN_SysMembership_UpdatePrivilege]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[va_Get_AllShops_SaleAnalysis_Compare]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[va_Get_AllShops_SaleAnalysis_Compare]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[va_Pager201301]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[va_Pager201301]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

-- =============================================
-- 作者:	 金鼎动力
-- 创建时间: 2008-3-10 11:46:11
-- 描述:	 添加Module
-- 调用示例：exec ChaokersCN_SysMembership_AddModule '回访评价',''
-- =============================================
CREATE PROCEDURE ChaokersCN_SysMembership_AddModule
	@ModuleName VARCHAR(50),
	@Icon VARCHAR(50)
AS
	--SET NOCOUNT ON
	
	DECLARE @ModuleID  INT,
			@SortIndex INT
		
	SELECT @ModuleID=MAX(ModuleID),@SortIndex=MAX(SortIndex) FROM ChaokersCN_SysMembership_Modules
	IF @ModuleID IS NULL
		SET @ModuleID = 1
	ELSE
		SET @ModuleID=@ModuleID + 1				
	
	IF @SortIndex IS NULL
		SET @SortIndex = 1
	ELSE
		SET @SortIndex=@SortIndex + 1				
		
	INSERT INTO ChaokersCN_SysMembership_Modules(ModuleID,ModuleName, SortIndex,Icon)VALUES(@ModuleID,@ModuleName,@SortIndex,@Icon)
	
	RETURN
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





-- =============================================
-- 作者:	 金鼎动力
-- 创建时间: 2008-3-9 11:51:11
-- 描述:	 添加Privilege
-- 调用示例：exec ChaokersCN_SysMembership_AddPrivilege 1,0,'人物明星','','','',3,'',1,''
--           exec ChaokersCN_SysMembership_AddPrivilege 1,1,'古天乐','','','',3,'',1,''
--			 exec ChaokersCN_SysMembership_AddPrivilege 1,49,'九寨沟风情','','','',3,'',1,''
-- =============================================
CREATE PROCEDURE ChaokersCN_SysMembership_AddPrivilege
	@ModuleID INT,
	@ParentID INT,	
	@PrivilegeName VARCHAR(50),
	@PrivilegeDesc VARCHAR(200),
	@PageURL VARCHAR(300),
	@BtnID VARCHAR(200),
	@PrivilegeType TINYINT,
	@DefaultPageURL VARCHAR(300),
	@Target VARCHAR(50),
	@StateFlag INT,
	@Icon VARCHAR(50),
	@GroupNum VARCHAR(50),
	@IsDevPriv VARCHAR(1)
AS
	
BEGIN TRANSACTION

	DECLARE @IsRootNode INT
	DECLARE @PrivilegePath VARCHAR(100)
	DECLARE @PrivilegeDepth INT
	DECLARE @IsLeaf INT
	DECLARE @SortIndex INT
	DECLARE @PrivilegeID INT
	
	SET @IsRootNode		= 1	
	SET @PrivilegePath	= ''
	SET @PrivilegeDepth = 0
	SET @IsLeaf			= 1  --1表示为叶子节点；
	SET @SortIndex		= 1
	
	IF @ParentID = 0
		SET @IsRootNode = 1		
	ELSE
		SET @IsRootNode = 0		
	
	IF @IsRootNode != 1 -- 如果不是根节点
	BEGIN
		SELECT @PrivilegePath=PrivilegePath,@PrivilegeDepth=PrivilegeDepth FROM ChaokersCN_SysMembership_Privileges WHERE PrivilegeID=@ParentID
		
		IF @PrivilegePath IS NULL
		BEGIN
			RAISERROR('无法得到父权限的PrivilegePath值！',16,1)
			ROLLBACK TRANSACTION
			RETURN
		END
			
		IF @PrivilegeDepth IS NULL
		BEGIN
			RAISERROR('无法得到父权限的PrivilegeDepth值！',16,1)	
			ROLLBACK TRANSACTION
			RETURN
		END
			
		SET @PrivilegeDepth = @PrivilegeDepth + 1			
	END						
	
	SELECT @PrivilegeID=MAX(PrivilegeID),@SortIndex=MAX(SortIndex) FROM ChaokersCN_SysMembership_Privileges		
	
	IF @PrivilegeID IS NULL
		SET @PrivilegeID = 1
	ELSE
		SET @PrivilegeID=@PrivilegeID + 1
	
	IF @SortIndex IS NULL
		SET @SortIndex = 1
	ELSE
		SET @SortIndex=@SortIndex + 1

	INSERT INTO ChaokersCN_SysMembership_Privileges
	(
		PrivilegeID,
    	ModuleID,
		ParentID,
		PrivilegePath,
		PrivilegeDepth,
		IsLeaf,
		PrivilegeName,
		PrivilegeDesc,
		PageURL,
		BtnID,
		PrivilegeType,
		SortIndex,
		DefaultPageURL,
		Target,
		StateFlag,
		Icon,
		GroupNum,
		IsDevPriv
	)
	VALUES
	(
		@PrivilegeID,
    	@ModuleID,
		@ParentID,
		@PrivilegePath,
		@PrivilegeDepth,
		@IsLeaf,
		@PrivilegeName,
		@PrivilegeDesc,
		@PageURL,
		@BtnID,
		@PrivilegeType,
		@SortIndex,
		@DefaultPageURL,
		@Target,
		@StateFlag,
		@Icon,
		@GroupNum,
		@IsDevPriv
	)	
	
	
	IF @IsRootNode = 1 -- 如果是根节点		
		UPDATE ChaokersCN_SysMembership_Privileges SET PrivilegePath=CAST(@PrivilegeID AS VARCHAR(10)) WHERE PrivilegeID=@PrivilegeID
	ELSE
		BEGIN
			UPDATE ChaokersCN_SysMembership_Privileges SET PrivilegePath=PrivilegePath + ',' + CAST(@PrivilegeID AS VARCHAR(10)) WHERE PrivilegeID=@PrivilegeID
			UPDATE ChaokersCN_SysMembership_Privileges SET IsLeaf=0 WHERE PrivilegeID=@ParentID
		END
	

IF @@ERROR<>0
	ROLLBACK TRANSACTION
ELSE
	COMMIT TRANSACTION
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

-- =============================================
-- 作者:	 金鼎动力
-- 创建时间: 2008-3-11 17:55:11
-- 描述:	 添加Role
-- 调用示例：exec ChaokersCN_SysMembership_AddRole '院长','',1
-- =============================================
CREATE PROCEDURE ChaokersCN_SysMembership_AddRole
	@RoleName VARCHAR(50),
	@RoleDesc VARCHAR(200),
	@StateFlag	INT
AS
	--SET NOCOUNT ON
	
	DECLARE @RoleID	INT,
			@SortIndex INT
	
	SELECT @RoleID=MAX(RoleID),@SortIndex=MAX(SortIndex) FROM ChaokersCN_SysMembership_Roles
	IF @RoleID IS NULL
		SET @RoleID = 1
	ELSE
		SET @RoleID=@RoleID + 1
	
	IF @SortIndex IS NULL
		SET @SortIndex = 1
	ELSE
		SET @SortIndex=@SortIndex + 1
		
	INSERT INTO ChaokersCN_SysMembership_Roles(RoleID,RoleName,RoleDesc,SortIndex,StateFlag)VALUES(@RoleID,@RoleName,@RoleDesc,@SortIndex,@StateFlag)
	
	RETURN

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

-- =============================================
-- 作者:	 金鼎动力
-- 创建时间: 2008-3-9 13:31:11
-- 描述:	 删除Privilege
-- 调用示例：exec ChaokersCN_SysMembership_DeletePrivilege 36
-- =============================================
CREATE PROCEDURE ChaokersCN_SysMembership_DeletePrivilege
	@PrivilegeID INT
AS
	
BEGIN TRANSACTION
	
	DECLARE @IsLeaf INT
	DECLARE @ParentID INT		
	
	SELECT @IsLeaf=IsLeaf,@ParentID=ParentID FROM ChaokersCN_SysMembership_Privileges WHERE PrivilegeID=@PrivilegeID
		
	IF @IsLeaf = 1 --如果是叶子节点，则删除当前节点
		DELETE FROM ChaokersCN_SysMembership_Privileges WHERE PrivilegeID=@PrivilegeID		
	ELSE --如果不是叶子节点，则删除当前节点及其下的所有子节点；
		DELETE FROM ChaokersCN_SysMembership_Privileges WHERE PrivilegeID=@PrivilegeID OR PrivilegePath LIKE ''+ CAST(@PrivilegeID AS VARCHAR(10)) +',%' OR PrivilegePath LIKE '%,'+ CAST(@PrivilegeID AS VARCHAR(10)) +',%'
		
	IF @ParentID > 0 --如果当前节点存在父节点
	BEGIN
		DECLARE @ChildNodeCount INT
		SELECT @ChildNodeCount=COUNT(*) FROM ChaokersCN_SysMembership_Privileges WHERE ParentID=@ParentID
		
		IF @ChildNodeCount = 0 --如果当前节点的父节点下已没有子节点，则将其更新为叶子节点；
			UPDATE ChaokersCN_SysMembership_Privileges SET IsLeaf=1 WHERE PrivilegeID=@ParentID
	END
IF @@ERROR<>0
	ROLLBACK TRANSACTION
ELSE
	COMMIT TRANSACTION

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

-- =============================================
-- 作者:	 金鼎动力
-- 创建时间: 2008-3-11 10:32:11
-- 描述:	 得到某个节点的所有父节（直接+间接）点记录；
-- 调用示例：exec ChaokersCN_SysMembership_GetPrivilegeAllParentNodes 150
-- =============================================
CREATE PROCEDURE ChaokersCN_SysMembership_GetPrivilegeAllParentNodes
	@PrivilegeID INT
AS
	
	DECLARE @PrivilegePath VARCHAR(100)
	SELECT @PrivilegePath=PrivilegePath FROM ChaokersCN_SysMembership_Privileges WHERE PrivilegeID=@PrivilegeID
	
	EXEC('SELECT PrivilegeName,PrivilegeID FROM ChaokersCN_SysMembership_Privileges WHERE PrivilegeID IN(' + @PrivilegePath + ') ORDER BY PrivilegeID ASC')
	
	RETURN

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

-- =============================================
-- 作者:	 金鼎动力
-- 创建时间: 2008-3-13 17:27:11
-- 描述:	 权限排序
-- 调用示例：exec ChaokersCN_SysMembership_PrivilegeSort 344,0,'Up'
-- =============================================
CREATE PROCEDURE ChaokersCN_SysMembership_PrivilegeSort	
	@PrivilegeID INT,	
	@ParentID INT,
	@CmdName VARCHAR(10)
AS
BEGIN TRANSACTION
	
	DECLARE @TargetPrivilegeID INT
	DECLARE @SortIndex INT
	DECLARE @TargetSortIndex INT
	
	SELECT @SortIndex=SortIndex FROM  ChaokersCN_SysMembership_Privileges WHERE PrivilegeID=@PrivilegeID 
	
	IF @CmdName = 'Up'	
	BEGIN					
		SELECT TOP 1 @TargetSortIndex=SortIndex,@TargetPrivilegeID=PrivilegeID FROM  ChaokersCN_SysMembership_Privileges WHERE ParentID=@ParentID AND SortIndex<@SortIndex ORDER BY SortIndex DESC			
	END
	
	IF @CmdName = 'Down'
	BEGIN	
		SELECT TOP 1 @TargetSortIndex=SortIndex,@TargetPrivilegeID=PrivilegeID FROM  ChaokersCN_SysMembership_Privileges WHERE ParentID=@ParentID AND SortIndex>@SortIndex ORDER BY SortIndex ASC
	END
	
	/**/
	IF @TargetSortIndex IS NOT NULL
	BEGIN
		UPDATE ChaokersCN_SysMembership_Privileges SET SortIndex=@TargetSortIndex WHERE PrivilegeID=@PrivilegeID
		UPDATE ChaokersCN_SysMembership_Privileges SET SortIndex=@SortIndex WHERE PrivilegeID=@TargetPrivilegeID
	END
	
		
	--Print cast(@TargetSortIndex as varchar(10))
	--Print cast(@TargetPrivilegeID as varchar(10))
	
IF @@ERROR<>0
	ROLLBACK TRANSACTION
ELSE
	COMMIT TRANSACTION

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO





-- =============================================
-- 作者:	 金鼎动力
-- 创建时间: 2008-3-9 12:18:11
-- 描述:	 更新Privilege
-- 调用示例：exec ChaokersCN_SysMembership_UpdatePrivilege 3,48,46,'成龙','','','',3,'',1
-- =============================================
CREATE PROCEDURE ChaokersCN_SysMembership_UpdatePrivilege	
	@ModuleID INT,
	@PrivilegeID INT,
	@ParentID INT,	
	@PrivilegeName VARCHAR(50),
	@PrivilegeDesc VARCHAR(200),
	@PageURL VARCHAR(300),
	@BtnID VARCHAR(200),
	@PrivilegeType TINYINT,
	@DefaultPageURL VARCHAR(300),
	@Target VARCHAR(50),
	@StateFlag INT,
	@Icon VARCHAR(50),
	@GroupNum VARCHAR(50),
	@IsDevPriv VARCHAR(10)
AS

--实现思路：
--1）更新基本信息；
--2）更新当前节点；
--3）更新当前节点的子节点；
--4）更新目标父节点；
--5）更新当前节点的原父节点；
	
BEGIN TRANSACTION
		
	DECLARE @HasChildNodes INT  
	DECLARE @HasParentNodes INT  
	DECLARE @OldParentID INT
	DECLARE @OldModuleID INT
	DECLARE @PrivilegePath VARCHAR(100)
	DECLARE @PrivilegeDepth INT
	DECLARE @IsLeaf INT
	
	DECLARE @TmpPrivilegePath VARCHAR(100)
	DECLARE @TmpPrivilegeDepth INT
	
	--先读取出原来的部分信息
	SELECT @OldParentID=ParentID,@OldModuleID=ModuleID,@PrivilegePath=PrivilegePath,@PrivilegeDepth=@PrivilegeDepth,@IsLeaf=IsLeaf FROM ChaokersCN_SysMembership_Privileges WHERE PrivilegeID=@PrivilegeID
	
	--更新基本信息；
	UPDATE ChaokersCN_SysMembership_Privileges SET ModuleID=@ModuleID,PrivilegeName=@PrivilegeName,PrivilegeDesc=@PrivilegeDesc,PageURL=@PageURL,BtnID=@BtnID,PrivilegeType=@PrivilegeType,DefaultPageURL=@DefaultPageURL,Target=@Target,StateFlag=@StateFlag,Icon=@Icon,GroupNum=@GroupNum,IsDevPriv=@IsDevPriv WHERE PrivilegeID=@PrivilegeID
	
	print cast(@ModuleID as varchar(30)) + '|'
	print cast(@OldModuleID as varchar(30))
			
	--如果父节点及所属模块不变；
	IF (@ParentID = @OldParentID AND @ModuleID = @OldModuleID)
	BEGIN
		IF @@ERROR<>0
			ROLLBACK TRANSACTION
		ELSE
		BEGIN
			COMMIT TRANSACTION
			RETURN
		END
	END	
	
	--判断当前节点是否拥有子节点和父节点；
	IF @IsLeaf = 1
		SET @HasChildNodes = 0  -- 没有子节点；
	ELSE
		SET @HasChildNodes = 1  -- 拥有子节点；
	
	IF @OldParentID = 0
		SET @HasParentNodes = 0 -- 没有父节点；
	ELSE
		SET @HasParentNodes = 1 -- 拥有父节点；
	
	
	/*****处理当前节点和目标父节点.BEGIN*******************************/			
	
	--读取目标父节点的信息；
	SELECT @TmpPrivilegePath=PrivilegePath,@TmpPrivilegeDepth=PrivilegeDepth FROM ChaokersCN_SysMembership_Privileges WHERE PrivilegeID=@ParentID
	
		
	--如果当前节点有子节点，并且目标父节点是当前节点的子节点，则提示出错，操作终止；
	--也就是说，不能把当前节点转移到其子节点下；
	IF @HasChildNodes = 1
	BEGIN	
		DECLARE @TargetNodePrivilegePath VARCHAR(100) --目标节点路径；
		IF @TmpPrivilegePath IS NOT NULL
			SET @TargetNodePrivilegePath = @TmpPrivilegePath
		
		IF @TargetNodePrivilegePath IS NOT NULL AND @TargetNodePrivilegePath<>''
		BEGIN
		
			DECLARE @HasError INT
			SET @HasError = 0
		
			--如果目标父节点的路径中含有当前节点（则说明将要把当前节点移动到其子节点下，这是不允许的）
			IF CHARINDEX(','+ Cast(@PrivilegeID AS VARCHAR(10)) +',',@TargetNodePrivilegePath) > 0
			BEGIN				
				SET @HasError = 1					
			END
			
			IF CHARINDEX(Cast(@PrivilegeID AS VARCHAR(10)) +',',@TargetNodePrivilegePath) > 0
			BEGIN				
				SET @HasError = 1					
			END
			
			IF @HasError = 1
			BEGIN
				RAISERROR('不能将当前节点转移到其子节点下！',16,1)
				ROLLBACK TRANSACTION	
				RETURN	
			END	
		END
	END
	
		
	SET @PrivilegeDepth = @TmpPrivilegeDepth + 1
	SET @PrivilegePath	= @TmpPrivilegePath + ',' + CAST(@PrivilegeID AS VARCHAR(10))	
		
	IF @ParentID = 0  --如果新的父节点为根节点；
	BEGIN
		SET @PrivilegeDepth = 0
		SET @PrivilegePath	= CAST(@PrivilegeID AS VARCHAR(10))
	END
	
	--更新当前节点的信息；				
	UPDATE ChaokersCN_SysMembership_Privileges SET PrivilegePath=@PrivilegePath,PrivilegeDepth=@PrivilegeDepth,IsLeaf=@IsLeaf,ParentID=@ParentID WHERE PrivilegeID=@PrivilegeID
	
	--更新目标节点的信息；
	UPDATE ChaokersCN_SysMembership_Privileges SET IsLeaf=0 WHERE PrivilegeID=@ParentID
	/*****处理当前节点和目标父节点.BEGIN*******************************/


	--如果当前节点拥有子节点，则更新其所有子节点的信息
	IF @HasChildNodes = 1		
	BEGIN				
		--通过游标来处理该节点下的所有子节点；
		
		DECLARE @PrivilegeID1 INT
		DECLARE @ParentID1 INT
		DECLARE @PrivilegePath1 VARCHAR(100)
		DECLARE @PrivilegeDepth1 INT		
		
		SET @PrivilegeID1		= -1
		SET @ParentID1			= -1
		SET @PrivilegePath1		= ''
		SET @PrivilegeDepth1	= -1	
		
		DECLARE tbCursor CURSOR FOR 
		SELECT PrivilegeID,ParentID,PrivilegePath,PrivilegeDepth FROM ChaokersCN_SysMembership_Privileges WHERE PrivilegePath LIKE ''+ CAST(@PrivilegeID AS VARCHAR(10)) +',%' OR PrivilegePath LIKE '%,'+ CAST(@PrivilegeID AS VARCHAR(10)) +',%' 

		OPEN tbCursor
		FETCH NEXT FROM tbCursor INTO @PrivilegeID1,@ParentID1,@PrivilegePath1,@PrivilegeDepth1

		WHILE @@fetch_status = 0
		BEGIN		
			--***************************************************************************************
			
			SELECT @TmpPrivilegeDepth=PrivilegeDepth,@TmpPrivilegePath=PrivilegePath FROM ChaokersCN_SysMembership_Privileges WHERE PrivilegeID=@ParentID1
			
			SET @PrivilegeDepth1 = @TmpPrivilegeDepth + 1
			SET @PrivilegePath1	= @TmpPrivilegePath + ',' + CAST(@PrivilegeID1 AS VARCHAR(10))			
		
			UPDATE ChaokersCN_SysMembership_Privileges SET ModuleID=@ModuleID,PrivilegePath=@PrivilegePath1,PrivilegeDepth=@PrivilegeDepth1 WHERE PrivilegeID=@PrivilegeID1
			
			--***************************************************************************************			
			SET @PrivilegeID1		= -1
			SET @ParentID			= -1
			SET @PrivilegePath1		= ''
			SET @PrivilegeDepth1	= -1

		FETCH NEXT FROM tbCursor INTO @PrivilegeID1,@ParentID1,@PrivilegePath1,@PrivilegeDepth1
		END
		DEALLOCATE tbCursor
		
	END
	
	--当前节点没有父节点的处理逻辑；
	IF @HasParentNodes = 0
	BEGIN
		--不做任何处理；
		PRINT '不做任何处理'			
	END
	
	--当前节点有父节点的处理逻辑；
	IF @HasParentNodes = 1
	BEGIN
		DECLARE @ChildNodeCount INT
		SELECT @ChildNodeCount=COUNT(*) FROM ChaokersCN_SysMembership_Privileges WHERE ParentID=@OldParentID
		
		IF @ChildNodeCount = 0 --如果当前节点的原父节点下已没有子节点，则将其更新为叶子节点；
			UPDATE ChaokersCN_SysMembership_Privileges SET IsLeaf=1 WHERE PrivilegeID=@OldParentID
	END

IF @@ERROR<>0
	ROLLBACK TRANSACTION
ELSE
	COMMIT TRANSACTION
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

-- =============================================
-- 作者:	 金鼎动力
-- 创建时间: 2007-3-10 11:46:11
-- 描述:	 各门店经营状况对比分析表
-- 调用示例：exec va_Get_AllShops_SaleAnalysis_Compare '2006-7-1','2016-7-1','Shop_Code ASC'
-- =============================================
CREATE PROCEDURE va_Get_AllShops_SaleAnalysis_Compare
	@BeginDate DateTime,
	@EndDate DateTime,
	@OrderBy VARCHAR(100)
AS
	

DECLARE @Shop_Code VARCHAR(10)
DECLARE @Shop_Name VARCHAR(50)

--创构建主表
CREATE TABLE #maintb(
Shop_Code VARCHAR(10),
Shop_Name VARCHAR(50),
Consume_Costs NUMERIC(18,2),  --消费总金额
Consume_Charges NUMERIC(18,2), --消费实收金额
Consume_Discounts NUMERIC(18,2), --消费优惠金额
Wallet_Charges NUMERIC(18,2), --会员充值金额
Wallet_Donate NUMERIC(18,2), --充值赠送金额
Withdraw_Amount NUMERIC(18,2), --会员提现金额
PointCard_Charges NUMERIC(18,2), --计次卡销售金额
PointCard_Balance NUMERIC(18,2), --计次卡余次金额
Member_Count INT, --办理会员数量
Member_Account_Balance NUMERIC(18,2)) --会员帐户余额

--填充门店基本数据;
DECLARE cur_Shops CURSOR FOR SELECT Shop_Code,Shop_Name FROM va_Shop_Dict;
OPEN cur_Shops;
FETCH NEXT FROM cur_Shops INTO @Shop_Code,@Shop_Name
WHILE @@FETCH_STATUS = 0
BEGIN
	INSERT INTO #maintb(Shop_Code,Shop_Name)VALUES(@Shop_Code,@Shop_Name);
	FETCH NEXT FROM cur_Shops INTO @Shop_Code,@Shop_Name
END
CLOSE cur_Shops;
DEALLOCATE cur_Shops;

--完善门店各项统计数据
DECLARE cur_main CURSOR FOR SELECT Shop_Code,Shop_Name FROM #maintb;
OPEN cur_main;

DECLARE @Consume_Costs NUMERIC(18,2)
DECLARE @Consume_Charges NUMERIC(18,2)
DECLARE @Consume_Discounts NUMERIC(18,2)
DECLARE @Wallet_Charges NUMERIC(18,2)
DECLARE @Wallet_Donate NUMERIC(18,2)
DECLARE @Withdraw_Amount NUMERIC(18,2)
DECLARE @PointCard_Charges NUMERIC(18,2)
DECLARE @PointCard_Balance NUMERIC(18,2)
DECLARE @Member_Count NUMERIC(18,2)
DECLARE @Member_Account_Balance NUMERIC(18,2)

FETCH NEXT FROM cur_main INTO @Shop_Code,@Shop_Name
WHILE @@FETCH_STATUS = 0
BEGIN
	--更新消费金额统计
	SELECT 	@Consume_Costs=SUM(Costs),@Consume_Discounts=SUM(Discounts),@Consume_Charges=SUM(Charges) FROM va_Charge_Master WHERE Shop_Code=@Shop_Code AND Operate_Date>=@BeginDate AND Operate_Date<@EndDate
	SET @Consume_Costs=ISNULL(@Consume_Costs,0.00);
	SET @Consume_Discounts=ISNULL(@Consume_Discounts,0.00);
	SET @Consume_Charges=ISNULL(@Consume_Charges,0.00);
	UPDATE #maintb SET Consume_Costs=@Consume_Costs,Consume_Discounts=@Consume_Discounts,Consume_Charges=@Consume_Charges WHERE Shop_Code=@Shop_Code;

	--更新充值金额统计
	SELECT 	@Wallet_Charges=SUM(Pay_Amount),@Wallet_Donate=SUM(Donate_Amount) FROM va_Member_WalletCharges WHERE Shop_Code=@Shop_Code AND Charge_Date>=@BeginDate AND Charge_Date<@EndDate
	SET @Wallet_Charges=ISNULL(@Wallet_Charges,0.00);
	SET @Wallet_Donate=ISNULL(@Wallet_Donate,0.00);
	UPDATE #maintb SET Wallet_Charges=@Wallet_Charges,Wallet_Donate=@Wallet_Donate WHERE Shop_Code=@Shop_Code;

	--更新提现金额统计
	SELECT 	@Withdraw_Amount=SUM(Withdraw_Amount) FROM va_Member_WalletWithdraw WHERE Shop_Code=@Shop_Code AND Withdraw_Date>=@BeginDate AND Withdraw_Date<@EndDate
	SET @Withdraw_Amount=ISNULL(@Withdraw_Amount,0.00);
	UPDATE #maintb SET Withdraw_Amount=@Withdraw_Amount WHERE Shop_Code=@Shop_Code;

	--更新计次卡金额统计
	SELECT @PointCard_Charges=SUM(mp.PointCard_Price),@PointCard_Balance=SUM((mp.PointCard_Price/mp.PointCard_SrvCount)*mp.PointCard_RemainingCount) FROM va_Member_PointCards mp INNER JOIN va_Member_PointCardCharges mpc ON mp.PointCardCharge_ID=mpc.RID WHERE Shop_Code=@Shop_Code AND mp.Status='1' AND Charge_Date>=@BeginDate AND Charge_Date<@EndDate 
	SET @PointCard_Charges=ISNULL(@PointCard_Charges,0.00);
	SET @PointCard_Balance=ISNULL(@PointCard_Balance,0.00);
	UPDATE #maintb SET PointCard_Charges=@PointCard_Charges,PointCard_Balance=@PointCard_Balance WHERE Shop_Code=@Shop_Code;

	--更新会员信息统计
	SELECT 	@Member_Count=COUNT(*),@Member_Account_Balance=SUM(Account_Balance) FROM va_Member_BasicInfo WHERE Shop_Code=@Shop_Code AND Create_Date>=@BeginDate AND Create_Date<@EndDate 
	SET @Member_Count=ISNULL(@Member_Count,0.00);
	SET @Member_Account_Balance=ISNULL(@Member_Account_Balance,0.00);
	UPDATE #maintb SET Member_Count=@Member_Count,Member_Account_Balance=@Member_Account_Balance WHERE Shop_Code=@Shop_Code;
	
	FETCH NEXT FROM cur_main INTO @Shop_Code,@Shop_Name
END

CLOSE cur_main;
DEALLOCATE cur_main;

exec('SELECT * FROM #maintb ORDER BY ' + @OrderBy);

--删除临时表
drop Table #maintb;


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


/*                      
功能说明：该存储过程使用临时表进行分页。                    
--exec va_Pager201301 'vs_CM_Articles','articleID>0','articleID','adddate desc,istop desc',1,10                    
*/                      
CREATE    procedure va_Pager201301
(                      
@TableName varchar(3000),  --表名或子查询，如果是子查询应用括号括起来（如：(select * from vs_CM_Articles)）
@SqlFilter varchar(1000), --筛选条件，不包含and和where。如：“articleID>0”。
@KeyFieldName varchar(100), --关键字段名称。
@OrderBy varchar(100), --排序依据，如：adddate desc,isTop desc
@PageIndex int, --当前页码
@PageSize int --每页记录数量                                    
)                    
as                    
                  
--保存最终构造出来的select语句；
declare @sql_select varchar(2000)
--构造数据筛选条件
if @SqlFilter is not null and @SqlFilter<>''
begin
	set @SqlFilter = 'and ' + @SqlFilter
end
if @PageIndex = 1  --如果查询的为第一页
begin
   	set @sql_select = 'SELECT TOP '+ cast(@PageSize as varchar(10)) +' t.* from '+ @TableName +' t where 1=1 '+ @SqlFilter +' ORDER BY ' + @OrderBy
	print @sql_select;
end 
else if @PageIndex > 1  --如果查询的为第N页（N>1）
begin	
	--构建临时表.BEGIN（在临时表中添加一个行号字段ROWNUM）
	create table #temp
	(
	ROWNUM int identity,
	KeyField varchar(30)
	);
	declare @TopRecord int      
	set @TopRecord = @PageIndex * @PageSize  	
	declare @tempsql varchar(2000)
	--@tempsql原型：insert into #temp(KeyField) select articleID from vs_CM_Articles as t where 1=1 and articleID>0 order by addDate desc,articleID desc
	set @tempsql='insert into  #temp(KeyField) select TOP '+ cast(@TopRecord as varchar(10)) +' '+ @KeyFieldName +' from  '+ @TableName +' as t where 1=1 '+ @SqlFilter +' order by ' + @OrderBy
	--调试用 set @tempsql='insert into  temp1(KeyField) select TOP '+ cast(@TopRecord as varchar(10)) +' '+ @KeyFieldName +' from  '+ @TableName +' as t where 1=1 '+ @SqlFilter +' order by ' + @OrderBy	
        print @tempsql
	exec (@tempsql)
	
	--构造select语句	
	declare @StartRecord int      
	set @StartRecord = (@PageIndex-1) * @PageSize  
	set @sql_select = 'SELECT TOP '+ cast(@PageSize as varchar(10)) +' t.* from '+ @TableName +' t INNER JOIN #temp temp ON t.'+ @KeyFieldName +'=temp.KeyField WHERE temp.ROWNUM>'+ cast(@StartRecord as varchar(10)) + ' ORDER BY temp.ROWNUM ASC '
  print @sql_select 
end
exec (@sql_select)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

