GO
PRINT N'Dropping [dbo].[PE_InsideLink]...';


GO
DROP TABLE [dbo].[PE_InsideLink];


GO
PRINT N'Dropping [dbo].[PR_Accessories_InsideLink_Add]...';


GO
DROP PROCEDURE [dbo].[PR_Accessories_InsideLink_Add];


GO
PRINT N'Dropping [dbo].[PR_Accessories_InsideLink_Delete]...';


GO
DROP PROCEDURE [dbo].[PR_Accessories_InsideLink_Delete];


GO
PRINT N'Dropping [dbo].[PR_Accessories_InsideLink_GetById]...';


GO
DROP PROCEDURE [dbo].[PR_Accessories_InsideLink_GetById];


GO
PRINT N'Dropping [dbo].[PR_Accessories_InsideLink_IsEnabled]...';


GO
DROP PROCEDURE [dbo].[PR_Accessories_InsideLink_IsEnabled];


GO
PRINT N'Dropping [dbo].[PR_Accessories_InsideLink_Update]...';


GO
DROP PROCEDURE [dbo].[PR_Accessories_InsideLink_Update];


GO
PRINT N'Altering [dbo].[PE_Client]...';


GO
ALTER TABLE [dbo].[PE_Client]
    ADD [IsBusiness] BIT NULL;


GO
PRINT N'Altering [dbo].[PE_CommonProduct]...';


GO
ALTER TABLE [dbo].[PE_CommonProduct]
    ADD [ExpPrice]       MONEY NULL,
        [ExpMaxQuantity] INT   NULL;


GO
PRINT N'Altering [dbo].[PE_Nodes]...';


GO
ALTER TABLE [dbo].[PE_Nodes]
    ADD [IncrementalUpdatePages] INT CONSTRAINT [DF_PE_Nodes_IncrementalUpdatePages] DEFAULT ((0)) NULL;


GO
PRINT N'Altering [dbo].[PE_OrderItem]...';


GO
ALTER TABLE [dbo].[PE_OrderItem]
    ADD [Status] INT NULL;


GO
PRINT N'Altering [dbo].[PE_Orders]...';


GO
ALTER TABLE [dbo].[PE_Orders]
    ADD [ExpressPrintTime] DATETIME NULL,
        [IsExpressPrinted] BIT      NULL;


GO
PRINT N'Altering [dbo].[PE_Stock]...';


GO
ALTER TABLE [dbo].[PE_Stock]
    ADD [Status] INT CONSTRAINT [DF_PE_Stock_Status] DEFAULT ((0)) NULL;


GO
PRINT N'Altering [dbo].[PE_SubscriptionItems]...';


GO
ALTER TABLE [dbo].[PE_SubscriptionItems]
    ADD [Content]    NVARCHAR (255) NULL,
        [Inputer]    NVARCHAR (50)  NULL,
        [CreateTime] DATETIME       NULL;


GO
PRINT N'Creating [dbo].[PE_Department]...';


GO
CREATE TABLE [dbo].[PE_Department] (
    [DepartmentId]   INT            NOT NULL,
    [DepartmentName] NVARCHAR (30)  NOT NULL,
    [CategoryName]   NVARCHAR (30)  NOT NULL,
    [UploadDir]      NVARCHAR (255) NOT NULL,
    [Number]         NVARCHAR (255) NULL,
    [Priority]       INT            NULL,
    [Template]       NVARCHAR (255) NULL,
    [Abbreviation]   NVARCHAR (50)  NULL,
    [SerialNumber]   NVARCHAR (50)  NULL
);


GO
PRINT N'Creating PK_PE_Department...';


GO
ALTER TABLE [dbo].[PE_Department]
    ADD CONSTRAINT [PK_PE_Department] PRIMARY KEY CLUSTERED ([DepartmentId] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating [dbo].[PE_Department_Members]...';


GO
CREATE TABLE [dbo].[PE_Department_Members] (
    [AdminId]      INT NOT NULL,
    [DepartmentId] INT NOT NULL
);


GO
PRINT N'Creating [dbo].[PE_Indent]...';


GO
CREATE TABLE [dbo].[PE_Indent] (
    [IndentID]  INT            NOT NULL,
    [IndentNum] NVARCHAR (40)  NULL,
    [InputTime] DATETIME       NOT NULL,
    [Inputer]   NVARCHAR (100) NULL,
    [Status]    INT            NOT NULL,
    [Remark]    NVARCHAR (510) NULL
);


GO
PRINT N'Creating PK_PE_Indent...';


GO
ALTER TABLE [dbo].[PE_Indent]
    ADD CONSTRAINT [PK_PE_Indent] PRIMARY KEY CLUSTERED ([IndentID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating [dbo].[PE_IndentItem]...';


GO
CREATE TABLE [dbo].[PE_IndentItem] (
    [ItemID]    INT            NOT NULL,
    [IndentID]  INT            NOT NULL,
    [ProductID] INT            NOT NULL,
    [Property]  NVARCHAR (510) NULL,
    [Amount]    INT            NOT NULL,
    [Price]     MONEY          NULL,
    [TableName] NVARCHAR (510) NULL
);


GO
PRINT N'Creating PK_PE_IndentItem...';


GO
ALTER TABLE [dbo].[PE_IndentItem]
    ADD CONSTRAINT [PK_PE_IndentItem] PRIMARY KEY CLUSTERED ([ItemID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating [dbo].[PE_Provider]...';


GO
CREATE TABLE [dbo].[PE_Provider] (
    [ProviderID]   INT            NOT NULL,
    [ProviderName] NVARCHAR (100) NOT NULL,
    [InputTime]    DATETIME       NOT NULL,
    [Inputer]      NVARCHAR (100) NULL,
    [Remark]       NVARCHAR (510) NULL
);


GO
PRINT N'Creating PK_PE_Provider...';


GO
ALTER TABLE [dbo].[PE_Provider]
    ADD CONSTRAINT [PK_PE_Provider] PRIMARY KEY CLUSTERED ([ProviderID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating [dbo].[PE_RedirectCategory]...';


GO
CREATE TABLE [dbo].[PE_RedirectCategory] (
    [CategoryID]          INT           IDENTITY (1, 1) NOT NULL,
    [CategoryName]        NVARCHAR (50) NULL,
    [CategoryDescription] NTEXT         NULL,
    [OrderID]             INT           NULL
);


GO
PRINT N'Creating PK_PE_RedirectCategory_CategoryID...';


GO
ALTER TABLE [dbo].[PE_RedirectCategory]
    ADD CONSTRAINT [PK_PE_RedirectCategory_CategoryID] PRIMARY KEY NONCLUSTERED ([CategoryID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF) ON [PRIMARY];


GO
PRINT N'Creating [dbo].[PE_RedirectUrl]...';


GO
CREATE TABLE [dbo].[PE_RedirectUrl] (
    [ID]             INT            IDENTITY (1, 1) NOT NULL,
    [UrlID]          INT            NOT NULL,
    [UrlName]        NVARCHAR (50)  NULL,
    [UrlValue]       NVARCHAR (255) NULL,
    [UrlDescription] NTEXT          NULL,
    [CategoryID]     INT            NOT NULL
);


GO
PRINT N'Creating PK_PE_RedirectUrl_ID...';


GO
ALTER TABLE [dbo].[PE_RedirectUrl]
    ADD CONSTRAINT [PK_PE_RedirectUrl_ID] PRIMARY KEY NONCLUSTERED ([ID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF) ON [PRIMARY];


GO
PRINT N'Creating [dbo].[PE_SendBack]...';


GO
CREATE TABLE [dbo].[PE_SendBack] (
    [SendBackID]  INT            NOT NULL,
    [SendBackNum] NVARCHAR (40)  NULL,
    [InputTime]   DATETIME       NOT NULL,
    [Inputer]     NVARCHAR (100) NULL,
    [Status]      INT            NOT NULL,
    [Remark]      NVARCHAR (510) NULL
);


GO
PRINT N'Creating PK_PE_SendBack...';


GO
ALTER TABLE [dbo].[PE_SendBack]
    ADD CONSTRAINT [PK_PE_SendBack] PRIMARY KEY CLUSTERED ([SendBackID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating [dbo].[PE_SendBackItem]...';


GO
CREATE TABLE [dbo].[PE_SendBackItem] (
    [ItemID]     INT            NOT NULL,
    [SendBackID] INT            NOT NULL,
    [ProductID]  INT            NOT NULL,
    [Property]   NVARCHAR (510) NULL,
    [Amount]     INT            NOT NULL,
    [Price]      MONEY          NULL,
    [TableName]  NVARCHAR (510) NULL
);


GO
PRINT N'Creating PK_PE_SendBackItem...';


GO
ALTER TABLE [dbo].[PE_SendBackItem]
    ADD CONSTRAINT [PK_PE_SendBackItem] PRIMARY KEY CLUSTERED ([ItemID] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating [dbo].[PE_VT_UserVoteTotal]...';


GO
CREATE TABLE [dbo].[PE_VT_UserVoteTotal] (
    [Id]         INT            NOT NULL,
    [VoteId]     INT            NOT NULL,
    [VoteItemId] NVARCHAR (255) NOT NULL,
    [IP]         NVARCHAR (50)  NULL,
    [UserName]   NVARCHAR (50)  NULL
);


GO
PRINT N'Creating PK_PE_VT_UserVoteTotal...';


GO
ALTER TABLE [dbo].[PE_VT_UserVoteTotal]
    ADD CONSTRAINT [PK_PE_VT_UserVoteTotal] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating [dbo].[PE_VT_Vote]...';


GO
CREATE TABLE [dbo].[PE_VT_Vote] (
    [Id]              INT            NOT NULL,
    [Title]           NVARCHAR (255) NOT NULL,
    [AllowVoteNumber] INT            NULL,
    [IsCheck]         BIT            NULL,
    [EndTime]         DATETIME       NULL,
    [State]           INT            NULL,
    [VoteLimit]       INT            NULL
);


GO
PRINT N'Creating PK_PE_VT_Vote...';


GO
ALTER TABLE [dbo].[PE_VT_Vote]
    ADD CONSTRAINT [PK_PE_VT_Vote] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating [dbo].[PE_VT_VoteItem]...';


GO
CREATE TABLE [dbo].[PE_VT_VoteItem] (
    [Id]          INT            NOT NULL,
    [GeneralId]   INT            NULL,
    [Title]       NVARCHAR (255) NOT NULL,
    [VoteId]      INT            NOT NULL,
    [TotalNumber] INT            NULL
);


GO
PRINT N'Creating PK_PE_VT_VoteItem...';


GO
ALTER TABLE [dbo].[PE_VT_VoteItem]
    ADD CONSTRAINT [PK_PE_VT_VoteItem] PRIMARY KEY CLUSTERED ([Id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating DF_PE_Indent_Status...';


GO
ALTER TABLE [dbo].[PE_Indent]
    ADD CONSTRAINT [DF_PE_Indent_Status] DEFAULT ((0)) FOR [Status];


GO
PRINT N'Creating DF_PE_Provider_ProviderID...';


GO
ALTER TABLE [dbo].[PE_Provider]
    ADD CONSTRAINT [DF_PE_Provider_ProviderID] DEFAULT ((0)) FOR [ProviderID];


GO
PRINT N'Creating DF_PE_Orders_Status...';


GO
ALTER TABLE [dbo].[PE_Orders]
    ADD CONSTRAINT [DF_PE_Orders_Status] DEFAULT ((0)) FOR [Status];


GO
PRINT N'Altering [dbo].[PR_Common_GetListBySortColumn]...';


GO
ALTER PROCEDURE [dbo].[PR_Common_GetListBySortColumn]
@StartRows INT, @PageSize INT, @PrimaryColumn VARCHAR (1000), @SortColumnDbType VARCHAR (100), @SortColumn VARCHAR (1000), @StrColumn VARCHAR (1000), @Sorts VARCHAR (100), @Filter VARCHAR (4000), @TableName VARCHAR (1000), @Total INT OUTPUT
AS
SET NOCOUNT ON

Begin
DECLARE @equalOperator char(2)
IF @StartRows<=0 
    begin
     SET @StartRows = 1
     SET @equalOperator='='
    end
Else 
    begin
    SET @equalOperator=''
    end

/*Set sorting variables.*/
DECLARE @operator char(2)
DECLARE @Order int
set @Order=	CHARINDEX('DESC',@Sorts)
IF @Order>0
	BEGIN
		SET @operator = '<'+@equalOperator
	END
ELSE
	BEGIN
		SET @operator = '>'+@equalOperator
	END
DECLARE @strFilter varchar(4000)
DECLARE @strSimpleFilter varchar(4000)

IF @Filter IS NOT NULL AND @Filter!=''
	BEGIN
		SET @strFilter = ' WHERE ' + @Filter + ' '
		SET @strSimpleFilter = ' AND ' + @Filter + ' '
	END
ELSE
	BEGIN	
		SET @strFilter = ''
		SET @strSimpleFilter = ''
	END

DECLARE @Sql nVarchar(4000)
SET @Sql=N'SELECT @Total=Count(*) FROM ' + @TableName + @strFilter
Exec sp_executesql @Sql, N'@Total Int Out',@Total Out 

IF @PageSize<=0
SET @PageSize=@Total

IF @PrimaryColumn!=@SortColumn
BEGIN
EXEC(
'DECLARE @SortId int ' +
'DECLARE @SortCol '+ @SortColumnDbType + ' '+
'SET ROWCOUNT '+ @StartRows + '
 SELECT @SortId = '+ @PrimaryColumn + ',@SortCol='+ @SortColumn +' FROM '+ @TableName + @strFilter +' ORDER BY '+ @SortColumn +' ' +@Sorts + ',' +  @PrimaryColumn + ' ' +@Sorts +'
 IF(@SortCol IS NOT NULL)
  begin
   IF('+@Order+'>0)
    begin
     SET ROWCOUNT '+@PageSize+' 
     SELECT '+ @StrColumn +' FROM '+ @TableName + '
     WHERE ('+@SortColumn+' IS NULL OR '+ @SortColumn  + @operator+'@SortCol OR ('+@SortColumn+'=@SortCol AND '+ @PrimaryColumn  + @operator +'@SortId ))' + @strSimpleFilter + ' ORDER BY '+ @SortColumn + ' ' +@Sorts + ',' + @PrimaryColumn + ' '+ @Sorts + '
    end
   ELSE
    BEGIN
    SET ROWCOUNT '+@PageSize+' 
    SELECT '+ @StrColumn +' FROM '+ @TableName + '
    WHERE ('+@SortColumn+' IS NOT NULL AND ('+ @SortColumn  + @operator+' @SortCol OR ('+ @SortColumn  + '= @SortCol AND '+ @PrimaryColumn  + @operator +'@SortId)))' + @strSimpleFilter + ' ORDER BY '+ @SortColumn + ' ' +@Sorts + ',' + @PrimaryColumn + ' '+ @Sorts + '
   END
  END
 ELSE
 begin
 IF('+@Order+'>0)
 begin
 SET ROWCOUNT '+@PageSize+' 
 SELECT '+ @StrColumn +' FROM '+ @TableName + '
 WHERE ('+@SortColumn+' IS NULL AND '+ @PrimaryColumn  + @operator +' @SortId )' + @strSimpleFilter + ' ORDER BY '+ @SortColumn + ' ' +@Sorts + ',' + @PrimaryColumn + ' '+ @Sorts + '
 END
 else
 begin
 SET ROWCOUNT '+@PageSize+' 
 SELECT '+ @StrColumn +' FROM '+ @TableName + '
 WHERE (('+@SortColumn+' IS NULL AND '+@PrimaryColumn+@operator+'@SortId ) OR '+@SortColumn+' IS NOT NULL )' + @strSimpleFilter + ' ORDER BY '+ @SortColumn + ' ' +@Sorts + ',' + @PrimaryColumn + ' '+ @Sorts + '
 end
 end'
)
END
ELSE
BEGIN
EXEC(
'DECLARE @SortId int ' +
'SET ROWCOUNT '+ @StartRows + '
 SELECT @SortId = '+ @SortColumn + ' FROM '+ @TableName + @strFilter +' ORDER BY '+ @SortColumn + ' ' +@Sorts +'
 SET ROWCOUNT '+ @PageSize  + '
 SELECT '+ @StrColumn +' FROM '+ @TableName + ' 
 WHERE '+ @SortColumn  + @operator +' @SortId '+ @strSimpleFilter + ' ORDER BY '+ @SortColumn +' '+ @Sorts + ''
)
END

Return @Total
END

SET ROWCOUNT 0

SET NOCOUNT OFF
GO
PRINT N'Altering [dbo].[PR_Contents_Nodes_Add]...';


GO
ALTER PROCEDURE [dbo].[PR_Contents_Nodes_Add]
@NodeId INT, @NodeIdentifier NVARCHAR (50), @NodeType INT, @ParentId INT, @ParentPath NTEXT, @Depth INT, @RootId INT, @Child INT, @ArrChildId NTEXT, @PrevId INT, @NextId INT, @OrderId INT, @NodeDir NVARCHAR (50), @ParentDir NTEXT, @NodeName NVARCHAR (50), @Tips NVARCHAR (255), @Description NTEXT, @NodePicUrl NVARCHAR (255), @MetaKeywords NVARCHAR (255), @MetaDescription NVARCHAR (255), @ShowOnMenu BIT, @ShowOnPath BIT, @ShowOnMap BIT, @ShowOnListIndex BIT, @ShowOnListParent BIT, @PurviewType INT, @Creater NVARCHAR (50), @InheritPurviewFromParent INT, @WorkFlowId INT, @HitsOfHot INT, @LeastOfEliteLevel INT, @OpenType INT, @ItemCount INT, @ItemChecked INT, @CommentCount INT, @CustomContent NTEXT, @EnableSubDomain BIT, @IsCreateContentPage BIT, @IsCreateListPage BIT, @AutoCreateHtmlType INT, @ContentPageHtmlRule NVARCHAR (255), @ListPageHtmlRule NVARCHAR (255), @ListPageSavePathType INT, @ListPagePostfix NVARCHAR (50), @RelateNode NTEXT, @RelateSpecial NTEXT, @ItemAspxFileName NVARCHAR (50), @DefaultTemplateFile NVARCHAR (255), @ContainChildTemplateFile NVARCHAR (255), @ItemOpenType INT, @ItemListOrderType INT, @ItemPageSize INT, @LinkUrl NVARCHAR (255), @Settings NTEXT, @IPLock NTEXT, @NeedCreateHtml BIT, @CultureName NVARCHAR (10), @SubDomain NVARCHAR (255), @ConfigType INT, @IncrementalUpdatePages INT
AS
INSERT	INTO PE_Nodes
		(
			NodeID,
			NodeIdentifier,
			NodeType,
			ParentID,
			ParentPath,
			Depth,
			RootID,
			Child,
			arrChildID,
			PrevID,
			NextID,
			OrderID,
			NodeDir,
			ParentDir,
			NodeName,
			Tips,
			Description,
			NodePicUrl,
			Meta_Keywords,
			Meta_Description,
			ShowOnMenu,
			ShowOnPath,
			ShowOnMap,
			ShowOnList_Index,
			ShowOnList_Parent,
			PurviewType,
			Creater,
			InheritPurviewFromParent,
			WorkFlowID,
			HitsOfHot,
			LeastOfEliteLevel,
			OpenType,
			ItemCount,
			ItemChecked,
			CommentCount,
			Custom_Content,
			
			IsCreateContentPage,
			IsCreateListPage,
			AutoCreateHtmlType,
			ContentPageHtmlRule,
			ListPageHtmlRule,
			ListPageSavePathType,
			ListPagePostfix,
			
			RelateNode,
			RelateSpecial,
			
			ItemAspxFileName,
			DefaultTemplateFile,
			ContainChildTemplateFile,
			ItemOpenType,
			ItemListOrderType,
			ItemPageSize,
			LinkUrl,
			Settings,
			IPLock,
			NeedCreateHtml,
			CultureName,
			EnableSubDomain,
			SubDomain,
			ConfigType,
			IncrementalUpdatePages
		)
		VALUES
		(
			@NodeId,
			@NodeIdentifier,
			@NodeType,
			@ParentId,
			@ParentPath,
			@Depth,
			@RootId,
			@Child,
			@ArrChildId,
			@PrevId,
			@NextId,
			@OrderId,
			@NodeDir,
			@ParentDir,
			@NodeName,
			@Tips,
			@Description,
			@NodePicUrl,
			@MetaKeywords,
			@MetaDescription,
			@ShowOnMenu,
			@ShowOnPath,
			@ShowOnMap,
			@ShowOnListIndex,
			@ShowOnListParent,
			@PurviewType,
			@Creater,
			@InheritPurviewFromParent,
			@WorkFlowId,
			@HitsOfHot,
			@LeastOfEliteLevel,
			@OpenType,
			@ItemCount,
			@ItemChecked,
			@CommentCount,
			@CustomContent,
			
			@IsCreateContentPage,
			@IsCreateListPage,
			@AutoCreateHtmlType,
			@ContentPageHtmlRule,
			@ListPageHtmlRule,
			
			@ListPageSavePathType,
			@ListPagePostfix,
			
			@RelateNode,
			@RelateSpecial,

			@ItemAspxFileName,
			@DefaultTemplateFile,
			@ContainChildTemplateFile,
			@ItemOpenType,
			@ItemListOrderType,
			@ItemPageSize,
			@LinkUrl,
			@Settings,
			@IPLock,
			@NeedCreateHtml,
			@CultureName,
			@EnableSubDomain,
			@SubDomain,
			@ConfigType,
			@IncrementalUpdatePages
		)
	RETURN
GO
PRINT N'Altering [dbo].[PR_Contents_Nodes_UpDate]...';


GO
ALTER PROCEDURE [dbo].[PR_Contents_Nodes_UpDate]
@NodeId INT, @NodeIdentifier NVARCHAR (50), @ParentId INT, @ParentPath NTEXT, @Depth INT, @RootId INT, @Child INT, @ArrChildId NTEXT, @PrevId INT, @NextId INT, @OrderId INT, @NodeDir NVARCHAR (50), @ParentDir NTEXT, @EnableSubDomain BIT, @NodeName NVARCHAR (50), @Tips NVARCHAR (255), @Description NTEXT, @NodePicUrl NVARCHAR (255), @MetaKeywords NVARCHAR (255), @MetaDescription NVARCHAR (255), @ShowOnMenu BIT, @ShowOnPath BIT, @ShowOnMap BIT, @ShowOnListIndex BIT, @ShowOnListParent BIT, @PurviewType INT, @Creater NVARCHAR (50), @InheritPurviewFromParent INT, @WorkFlowId INT, @HitsOfHot INT, @LeastOfEliteLevel INT, @OpenType INT, @ItemCount INT, @ItemChecked INT, @CommentCount INT, @CustomContent NTEXT, @IsCreateContentPage BIT, @IsCreateListPage BIT, @AutoCreateHtmlType INT, @ContentPageHtmlRule NVARCHAR (255), @ListPageHtmlRule NVARCHAR (255), @ListPageSavePathType INT, @ListPagePostfix NVARCHAR (50), @RelateNode NTEXT, @RelateSpecial NTEXT, @ItemAspxFileName NVARCHAR (50), @DefaultTemplateFile NVARCHAR (255), @ContainChildTemplateFile NVARCHAR (255), @ItemOpenType INT, @ItemListOrderType INT, @ItemPageSize INT, @LinkUrl NVARCHAR (255), @Settings NTEXT, @IPLock NTEXT, @NeedCreateHtml BIT, @CultureName NVARCHAR (10), @SubDomain NVARCHAR (255), @ConfigType INT, @IncrementalUpdatePages INT
AS
UPDATE
			PE_Nodes
	SET
			NodeIdentifier =@NodeIdentifier,
			
			ParentId=@ParentId,
			ParentPath=@ParentPath,
			Depth=@Depth,
			RootId=@RootId,
			Child=@Child,
			ArrChildId=@ArrChildId,
			PrevId=@PrevId,
			NextId=@NextId,
			OrderId=@OrderId,
			NodeDir=@NodeDir,
			ParentDir=@ParentDir,

			EnableSubDomain=@EnableSubDomain,
			NodeName = @NodeName,
			Tips = @Tips,
			Description = @Description,
			NodePicUrl = @NodePicUrl,
			Meta_Keywords = @MetaKeywords,
			Meta_Description = @MetaDescription,
			ShowOnMenu = @ShowOnMenu,
			ShowOnPath = @ShowOnPath,
			ShowOnMap = @ShowOnMap,
			ShowOnList_Index = @ShowOnListIndex,
			ShowOnList_Parent = @ShowOnListParent,
			PurviewType = @PurviewType,
			Creater = @Creater,
			InheritPurviewFromParent = @InheritPurviewFromParent,
			WorkFlowID = @WorkFlowId,
			HitsOfHot = @HitsOfHot,
			LeastOfEliteLevel = @LeastOfEliteLevel,
			OpenType = @OpenType,
			ItemCount = @ItemCount,
			ItemChecked = @ItemChecked,
			CommentCount = @CommentCount,
			Custom_Content = @CustomContent,
			IsCreateContentPage = @IsCreateContentPage,
			IsCreateListPage  = @IsCreateListPage ,
			AutoCreateHtmlType = @AutoCreateHtmlType,
			ContentPageHtmlRule = @ContentPageHtmlRule,
			ListPageHtmlRule = @ListPageHtmlRule,
			ListPageSavePathType=@ListPageSavePathType,
			ListPagePostfix=@ListPagePostfix,
			RelateNode = @RelateNode,
			RelateSpecial = @RelateSpecial,
			
			ItemAspxFileName = @ItemAspxFileName,
			DefaultTemplateFile = @DefaultTemplateFile,
			ContainChildTemplateFile = @ContainChildTemplateFile,
			ItemOpenType = @ItemOpenType,
			ItemListOrderType = @ItemListOrderType,
			ItemPageSize = @ItemPageSize,
			LinkUrl = @LinkUrl,
			Settings = @Settings,
			IPLock = @IPLock,
			NeedCreateHtml=@NeedCreateHtml,
			CultureName=@CultureName,
			SubDomain=@SubDomain,
			ConfigType=@ConfigType,
			IncrementalUpdatePages=@IncrementalUpdatePages
	WHERE
			NodeID = @NodeID
GO
PRINT N'Altering [dbo].[PR_Crm_Client_Add]...';


GO
ALTER PROCEDURE [dbo].[PR_Crm_Client_Add]
@ClientID INT, @ParentID INT, @ClientNum NVARCHAR (20), @ClientType INT, @ClientName NVARCHAR (100), @ShortedForm NVARCHAR (20), @Area INT, @ClientField INT, @ValueLevel INT, @CreditLevel INT, @Importance INT, @ConnectionLevel INT, @GroupID INT, @SourceType INT, @PhaseType INT, @Remark NTEXT, @CreateTime DATETIME, @UpdateTime DATETIME, @Owner NVARCHAR (50), @IsBusiness BIT
AS
INSERT INTO [PE_Client]
	      (	[ClientID], [ParentID], [ClientNum], [ClientType], [ClientName], [ShortedForm], [Area], [ClientField], [ValueLevel], [CreditLevel], [Importance], [ConnectionLevel], [GroupID], [SourceType], [PhaseType], [Remark], [CreateTime], [UpdateTime], [Owner],[LastContactedTime],[IsBusiness] )
	VALUES (@ClientID, @ParentID, @ClientNum, @ClientType, @ClientName, @ShortedForm, @Area, @ClientField, @ValueLevel, @CreditLevel, @Importance, @ConnectionLevel, @GroupID, @SourceType, @PhaseType, @Remark, @CreateTime, @UpdateTime, @Owner,getdate(),@IsBusiness)
GO
PRINT N'Altering [dbo].[PR_Crm_Client_Merge]...';


GO
ALTER PROCEDURE [dbo].[PR_Crm_Client_Merge]
@PrincipalClientID INT, @SubordinateClientID INT
AS
declare @clienttype1 int
declare @clienttype2 int
declare @LastContactedTime1 datetime
declare @LastContactedTime2 datetime
select @clienttype1=clienttype,@LastContactedTime1=LastContactedTime from pe_client where clientid=@PrincipalClientID
select @clienttype2=clienttype,@LastContactedTime2=LastContactedTime from pe_client where clientid=@SubordinateClientID
if @clienttype1=0--主客户为企业客户
begin
declare ContacterCursor cursor 
for
select contacterid,truename from Pe_contacter where clientid=@SubordinateClientID
declare @contacterid int
declare @truename varchar(20)
open ContacterCursor
fetch next from ContacterCursor into @contacterid,@truename 
while(@@fetch_status=0)
begin--处理联系信息
	declare @PrincipalClientcontacterid int
	select @PrincipalClientcontacterid=contacterid from pe_contacter where truename=@truename and clientid=@PrincipalClientID
	if(@PrincipalClientcontacterid>0) --判断是否有同名的
		begin
			--和主客户的联系人的资料同名的联系人id 删除 并更改相应的联系记录内容
			update pe_clientannal set clientid=@PrincipalClientID,contacter=@PrincipalClientcontacterid where clientid=@SubordinateClientID and contacter=@contacterid
			update pe_reminditem set CorrelativeClient=@PrincipalClientID,CorrelativeContacter =@PrincipalClientcontacterid where CorrelativeClient=@SubordinateClientID and CorrelativeContacter=@contacterid
			update pe_ComplainItem set clientid=@PrincipalClientID,contacterid=@PrincipalClientcontacterid where clientid=@SubordinateClientID and contacterid=@contacterid
			update pe_serviceItem set clientid=@PrincipalClientID,contacterid=@PrincipalClientcontacterid where clientid=@SubordinateClientID and contacterid=@contacterid
			delete pe_contacter where contacterid=@contacterid 
		end
	else--没有和主客户同名的联系人则只需更改记录的客户ID
		begin
			update pe_clientannal set clientid=@PrincipalClientID where clientid=@SubordinateClientID
			update pe_reminditem set CorrelativeClient=@PrincipalClientID where CorrelativeClient=@SubordinateClientID 
			update pe_serviceItem set clientid=@PrincipalClientID where clientid=@SubordinateClientID 
			update pe_ComplainItem set clientid=@PrincipalClientID where clientid=@SubordinateClientID
			update pe_contacter set clientid=@PrincipalClientID where contacterid=@contacterid
		end
fetch next from ContacterCursor into @contacterid ,@truename
end
close ContacterCursor
DEALLOCATE ContacterCursor
end
if @clienttype2=1--从客户为个人客户
begin
update pe_contacter set usertype=2 where clientid=@PrincipalClientID and usertype=0
end
if(@clienttype2=1 and @clienttype1=1)--主从客户都是个人客户
begin
set nocount on
declare @contacterid_z int 
declare @contacterid_c int
select @contacterid_z = contacterid from pe_contacter where clientid=@PrincipalClientID
select @contacterid_c = contacterid from pe_contacter where clientid=@SubordinateClientID
update pe_clientannal set clientid=@PrincipalClientID,contacter=@contacterid_z where clientid=@SubordinateClientID and contacter=@contacterid_c
update pe_reminditem set CorrelativeClient=@PrincipalClientID,CorrelativeContacter =@contacterid_z where CorrelativeClient=@SubordinateClientID and CorrelativeContacter=@contacterid_c
update pe_ComplainItem set clientid=@PrincipalClientID,contacterid=@contacterid_z where clientid=@SubordinateClientID and contacterid=@contacterid_c
update pe_serviceItem set clientid=@PrincipalClientID,contacterid=@contacterid_z where clientid=@SubordinateClientID and contacterid=@contacterid_c
delete pe_contacter where contacterid=@contacterid_c 
end

UPDATE PE_Orders SET ClientID=@PrincipalClientID WHERE ClientID=@SubordinateClientID--从客户的订单改为主客户的
UPDATE PE_BankrollItem SET ClientID=@PrincipalClientID WHERE ClientID=@SubordinateClientID--资金明细记录
UPDATE PE_Client SET Balance= (select sum(Balance)from pe_client where clientid IN(@PrincipalClientID ,@SubordinateClientID)) WHERE ClientID =@PrincipalClientID--资金余额相加
DELETE PE_Client WHERE ClientID=@SubordinateClientID--删除从客户

IF @LastContactedTime2 > @LastContactedTime1
BEGIN
	UPDATE PE_Client SET LastContactedTime=@LastContactedTime2 WHERE ClientID=@PrincipalClientID
END

DECLARE @SubUserID int
SELECT @SubUserID=UserID FROM PE_Users WHERE ClientID=@SubordinateClientID

IF (@SubUserID IS NOT NULL)
BEGIN
	UPDATE PE_Users SET ClientID=@PrincipalClientID WHERE UserID=@SubUserID
END
GO
PRINT N'Altering [dbo].[PR_Crm_Client_Update]...';


GO
ALTER PROCEDURE [dbo].[PR_Crm_Client_Update]
@ClientID INT, @ParentID INT, @ClientNum NVARCHAR (20), @ClientType INT, @ClientName NVARCHAR (100), @ShortedForm NVARCHAR (20), @Area INT, @ClientField INT, @ValueLevel INT, @CreditLevel INT, @Importance INT, @ConnectionLevel INT, @GroupID INT, @SourceType INT, @PhaseType INT, @Remark NTEXT, @UpdateTime DATETIME, @Owner NVARCHAR (50), @IsBusiness BIT
AS
UPDATE [PE_Client]
   SET 
		ParentID=@ParentID, 
		ClientNum=@ClientNum, 
		ClientType=@ClientType, 
		ClientName=@ClientName, 
		ShortedForm=@ShortedForm, 
		Area=@Area, 
		ClientField=@ClientField, 
		ValueLevel=@ValueLevel, 
		CreditLevel=@CreditLevel, 
		Importance=@Importance, 
		ConnectionLevel=@ConnectionLevel, 
		GroupID=@GroupID, 
		SourceType=@SourceType, 
		PhaseType=@PhaseType, 
		Remark=@Remark, 
		UpdateTime=@UpdateTime, 
		Owner=@Owner,
		IsBusiness=@IsBusiness
 WHERE  ClientID = @ClientID
GO
PRINT N'Creating [dbo].[PR_Department_Add]...';


GO
CREATE PROCEDURE [dbo].[PR_Department_Add]
@DepartmentId INT, @DepartmentName NVARCHAR (30), @CategoryName NVARCHAR (30), @UploadDir NVARCHAR (255), @Number NVARCHAR (255), @Priority INT, @Template NVARCHAR (255), @SerialNumber NVARCHAR (50), @Abbreviation NVARCHAR (50)
AS
INSERT INTO 
		[PE_Department] 
		(
			[DepartmentId],    --部门ID
			[DepartmentName],    --部门名称
			[CategoryName],    --所属分类
			[UploadDir],    --上传文件目录
			[Number],       --编号
            [Priority],        --权重
            [Template],      --部门模板路径
            [SerialNumber],
            [Abbreviation]
		)
	VALUES
	(
		@DepartmentId,
		@DepartmentName,
		@CategoryName,
		@UploadDir,
		@Number,
        @Priority,
        @Template,
        @SerialNumber,
        @Abbreviation
	)
GO
PRINT N'Creating [dbo].[PR_Department_Members_Add]...';


GO
CREATE PROCEDURE [dbo].[PR_Department_Members_Add]
@AdminId INT, @DepartmentId INT
AS
if not exists(select [AdminId] from [PE_Department_Members] where [AdminID]=@AdminId and [DepartmentId]=@DepartmentId)
begin
INSERT INTO 
[PE_Department_Members] 
(
	[AdminId],    --管理员ID
	[DepartmentId]    --部门Id
)
VALUES
(
	@AdminId, 
    @DepartmentId 
)
end
GO
PRINT N'Creating [dbo].[PR_Department_Update]...';


GO
CREATE PROCEDURE [dbo].[PR_Department_Update]
@DepartmentId INT, @DepartmentName NVARCHAR (30), @CategoryName NVARCHAR (30), @UploadDir NVARCHAR (255), @Number NVARCHAR (255), @Priority INT, @Template NVARCHAR (255), @SerialNumber NVARCHAR (50), @Abbreviation NVARCHAR (50)
AS
UPDATE 
		[PE_Department] 
	SET
			[DepartmentName] = @DepartmentName,    --部门名称
			[CategoryName] = @CategoryName,    --所属分类
			[UploadDir] = @UploadDir,    --上传文件目录
			[Number] = @Number,          --编号
            [Priority]=@Priority,        --权重
            [Template]=@Template,      -- 部门模板路径
            [SerialNumber]=@SerialNumber,
            [Abbreviation]=@Abbreviation
	WHERE
		[DepartmentId] = @DepartmentId
GO
PRINT N'Creating [dbo].[PR_Shop_Indent_Add]...';


GO
CREATE PROCEDURE [dbo].[PR_Shop_Indent_Add]
@IndentID INT, @IndentNum NVARCHAR (40), @InputTime DATETIME, @Inputer NVARCHAR (100), @Status INT, @Remark NVARCHAR (510)
AS
INSERT INTO PE_Indent
	      (IndentID,IndentNum,InputTime,Inputer,Status,Remark)
	VALUES (@IndentID,@IndentNum,@InputTime,@Inputer,@Status,@Remark)
GO
PRINT N'Creating [dbo].[PR_Shop_Indent_Update]...';


GO
CREATE PROCEDURE [dbo].[PR_Shop_Indent_Update]
@IndentID INT, @IndentNum NVARCHAR (40), @InputTime DATETIME, @Inputer NVARCHAR (100), @Status INT, @Remark NVARCHAR (510)
AS
UPDATE PE_Indent
SET IndentNum=@IndentNum,InputTime=@InputTime, Inputer=@Inputer, Status=@Status,Remark=@Remark
WHERE IndentID=@IndentID
GO
PRINT N'Creating [dbo].[PR_Shop_IndentItem_Add]...';


GO
CREATE PROCEDURE [dbo].[PR_Shop_IndentItem_Add]
@ItemID INT, @IndentID INT, @ProductID INT, @Property NVARCHAR (510), @Price MONEY, @Amount INT, @TableName NVARCHAR (510)
AS
INSERT INTO PE_IndentItem(ItemID,IndentID,ProductID,[Property],Price,Amount,[TableName])
VALUES(@ItemID,@IndentID,@ProductID,@Property,@Price,@Amount,@TableName)
GO
PRINT N'Creating [dbo].[PR_Shop_Provider_Add]...';


GO
CREATE PROCEDURE [dbo].[PR_Shop_Provider_Add]
@ProviderID INT, @ProviderName NVARCHAR (100), @InputTime DATETIME, @Inputer NVARCHAR (100), @Remark NVARCHAR (510)
AS
INSERT INTO PE_Provider
	      (ProviderID,ProviderName, InputTime, Inputer, Remark)
	VALUES (@ProviderID,@ProviderName,@InputTime,@Inputer,@Remark)
GO
PRINT N'Creating [dbo].[PR_Shop_Provider_Update]...';


GO
CREATE PROCEDURE [dbo].[PR_Shop_Provider_Update]
@ProviderID INT, @ProviderName NVARCHAR (100), @InputTime DATETIME, @Inputer NVARCHAR (100), @Remark NVARCHAR (510)
AS
UPDATE PE_Provider
SET ProviderName=@ProviderName,InputTime=@InputTime, Inputer=@Inputer, Remark=@Remark
WHERE ProviderID=@ProviderID
GO
PRINT N'Creating [dbo].[PR_Shop_SendBack_Add]...';


GO
CREATE PROCEDURE [dbo].[PR_Shop_SendBack_Add]
@SendBackID INT, @SendBackNum NVARCHAR (40), @InputTime DATETIME, @Inputer NVARCHAR (100), @Status INT, @Remark NVARCHAR (510)
AS
INSERT INTO PE_SendBack(SendBackID,SendBackNum,InputTime,Inputer,Status,Remark)
VALUES(@SendBackID,@SendBackNum,@InputTime,@Inputer,@Status,@Remark)
GO
PRINT N'Creating [dbo].[PR_Shop_SendBack_Update]...';


GO
CREATE PROCEDURE [dbo].[PR_Shop_SendBack_Update]
@SendBackID INT, @SendBackNum NVARCHAR (40), @InputTime DATETIME, @Inputer NVARCHAR (100), @Status INT, @Remark NVARCHAR (510)
AS
UPDATE PE_SendBack
SET SendBackNum=@SendBackNum,InputTime=@InputTime,Inputer=@Inputer,Status=@Status,Remark=@Remark
WHERE SendBackID=@SendBackID
GO
PRINT N'Creating [dbo].[PR_Shop_SendBackItem_Add]...';


GO
CREATE PROCEDURE [dbo].[PR_Shop_SendBackItem_Add]
@ItemID INT, @SendBackID INT, @ProductID INT, @Property NVARCHAR (510), @Amount INT, @Price MONEY, @TableName NVARCHAR (510)
AS
INSERT INTO PE_SendBackItem(ItemID,SendBackID,ProductID,[Property],Amount,Price,[TableName])
VALUES(@ItemID,@SendBackID,@ProductID,@Property,@Amount,@Price,@TableName)
GO
