--1.0-SP_W_VisitorRequest_Add------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_VisitorRequest_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_VisitorRequest_Add]')
end
begin
EXEC sp_executesql @statement = N'
create PROCEDURE [SP_W_VisitorRequest_Add]
@productId int ,
@userName nvarchar(50),
@phone nvarchar(50),
@fax nvarchar(50),
@companyname nvarchar(50),
@email nvarchar(50),
@content nvarchar(1000)
AS 
BEGIN TRAN  
	BEGIN 
		INSERT INTO W_VisitorRequest([ProductId],[UserName],[Phone],[Fax],[CompanyName],[Email],[RequestContent],[AddDate])
           VALUES(@productId,@userName,@phone,@fax,@companyname,@email,@content,getdate())
	END

 IF @@error<>0
  
   begin
       rollback tran
    end

 commit tran'
end
--2.0-[SP_C_Purchase_UpdateRecommend]-----
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_C_Purchase_UpdateRecommend]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_C_Purchase_UpdateRecommend]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_C_Purchase_UpdateRecommend]
@ID INT,
@Recommend BIT=''''
AS
BEGIN TRAN
SELECT  @Recommend=Recommend FROM C_Purchase WHERE ID=@ID	
    IF(@Recommend=1)
	UPDATE C_Purchase SET Recommend=0 WHERE ID=@ID
   ELSE
	UPDATE C_Purchase SET Recommend=1 WHERE ID=@ID
	
  IF @@error<>0
                 
BEGIN
   ROLLBACK TRAN
   RETURN -2
END
COMMIT TRAN	'
end	
--3.0-[SP_U_SubscriptionProduct_Update]-------------------------------------------
IF EXISTS (SELECT 1 FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_SubscriptionProduct_Update]') AND xtype='P')
BEGIN
	exec('DROP PROC [SP_U_SubscriptionProduct_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_SubscriptionProduct_Update]
@ID INT,
@Type nvarchar(50),
@Keyword nvarchar(50),
@IndType nvarchar(50),
@Area nvarchar(50),
@validity int
as
begin tran
BEGIN 
	UPDATE U_SubscriptionProduct SET [Type]=@Type,[Keyword]=@Keyword,[IndType]=@IndType,[Area]=@Area,[validity]=@validity WHERE ID=@ID
END
IF @@error<>0
    begin
       rollback tran
	   return -2 
    end
commit tran'
end
--4.0--[SP_C_Purchase_UpdateVerify]------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_C_Purchase_UpdateVerify]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_C_Purchase_UpdateVerify]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_C_Purchase_UpdateVerify] 
@ID INT,
@Verify INT=''''
AS
BEGIN TRAN
		SELECT  @Verify=Verify FROM C_Purchase WHERE ID=@ID
		IF(@Verify=''3'')
			UPDATE C_Purchase SET Verify=''1'' WHERE ID=@ID
		ELSE
			UPDATE C_Purchase SET Verify=''3'' WHERE ID=@ID
		
 IF @@error<>0
                 
BEGIN
   ROLLBACK TRAN
   RETURN -2
END
COMMIT TRAN	
' 
END
---5.0--[SP_DeleteByWhere]---------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_DeleteByWhere]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_DeleteByWhere]')
end
begin
EXEC sp_executesql @statement = N'
Create PROCEDURE [SP_DeleteByWhere]
(
@strwhere varchar(500), 
@strTableName varchar(500)
)
as
begin tran trdelete
declare @Sql varchar(400)
declare @ID varchar(20)
set @Sql = ''delete  from ''+ @strTableName +'' ''+@strwhere
execute(@Sql)
commit tran
if @@trancount > 0
begin
	rollback tran
end' 
END
---6.0---[SP_P_UpdateByWhere]--------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_P_UpdateByWhere]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_P_UpdateByWhere]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_P_UpdateByWhere]
(
  @strTableName nvarchar(200), 
  @strDate nvarchar(300)= '''', 
  @strWhere nvarchar(200)=''1=1''
)
AS
begin transaction
declare @sql varchar(4000)
begin
  set @sql= ''update ''+ @strTableName + '' set ''+ @strDate + '' where '' +@strWhere
  execute(@sql)
end
if @@error=0
  commit transaction
else
ROLLBACK TRAN
' 
END
---7.0--[SP_Pagination]----------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_Pagination]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_Pagination]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_Pagination]
@tblName   varchar(255),             -- 表名 .
@strGetFields   varchar(1000) =''* '', -- 需要返回的列   
@fldName   varchar(255)= '' '',        -- 排序的字段名 
@PageSize   int   =   10,            -- 页尺寸 
@PageIndex   int   =   1,            -- 页码 
@doCount   bit   =   0,              -- 返回记录总数,非 0 值则返回 
@OrderType   int   =   0,            -- 设置排序类型,非 0 值则降序 
@strWhere   varchar(1500) ='' '' ,      -- 查询条件(注意: 不要加where) 
@keyDate   varchar(1500) ='' ''
AS 
declare   @strSQL   varchar(5000)    -- 主语句 
declare   @strTmp   varchar(110)     -- 临时变量 
declare   @strOrder   varchar(400)   -- 排序类型 
declare   @fldNames   varchar(100)     -- 排序字段，如果两张表,只查询单张表的数据,去掉前面的表名
declare   @str        varchar(100)
if @doCount != 0 
begin 
	if @strWhere != '' '' 
		set @strSQL = ''select count(*) as Total from '' +@tblName+ '' where ''+@strWhere 
	else 
		set @strSQL = ''select count(*) as Total from '' +@tblName+ '' '' 
end   
--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。以下的所有代码都是@doCount为0的情况： 
else 
begin 
	declare @length int
	declare @str1   varchar(100)
	set @str1=@fldName;
	if charindex('','',@fldName,0)>0
	begin 
        set @str=SUBSTRING(@fldName,CharIndex('','',@fldName,0)+1,len(@fldName))
		if charindex(''.'',@str,0)>0 
		begin
			set @length=CharIndex(''.'',@str,0)
			set @fldNames=SUBSTRING(@str,@length+1,len(@str))
		end
		else
		begin
			set @fldNames=@str
		end
	end
    else
    begin
		if charindex(''.'',@fldName,0)>0 
		begin
			set @length=CharIndex(''.'',@fldName,0)
			set @fldNames=SUBSTRING(@fldName,@length+1,len(@fldName))
		end
		else
		begin
			set @fldNames=@fldName
		end
    end

	if charindex('','',@fldName,0)>0
	begin
		set @str=SUBSTRING(@fldName,0,CharIndex('','',@fldName,0))+'',''
		set @fldName=SUBSTRING(@fldName,CharIndex('','',@fldName,0)+1,len(@fldName))
	end
    else
    begin
		set @str=''''
    end 
 
	if @OrderType = 1 
	begin 
		set @strTmp = '' <(select   min '' 
		set @strOrder ='' order by ''+ @str  +@fldName+ '' desc '' 
	--如果@OrderType不是0，就执行降序，这句很重要！ 
	end 
	else if @OrderType = 0
	begin 
		set @strTmp = ''> (select max '' 
		set @strOrder ='' order by ''+ @str  +@fldName+ '' asc '' 
	end 
	else
	begin 
		set @strTmp = ''NOT IN (SELECT TOP '' 
		set @strOrder ='' order by ''+ @str  +@fldName+ '' asc '' 
	end 
	if   @PageIndex = 1 
	begin 
		if @strWhere != '' '' 
			set @strSQL = ''select top '' + str(@PageSize)+ '' '' +@strGetFields+ '' from '' +@tblName+ '' where '' +@strWhere+ '' '' +@strOrder 
		else 
			set @strSQL = ''select top '' + str(@PageSize)+ '' '' +@strGetFields+ '' from '' +@tblName+ '' '' +@strOrder 
   --如果是第一页就执行以上代码，这样会加快执行速度 
	end 
	else 
	begin 
   --以下代码赋予了@strSQL以真正执行的SQL代码   
		set @strSQL = ''select top '' +str(@PageSize)+ '' '' +@strGetFields+ '' from '' +@tblName+ '' where '' +@fldName+ '' '' +@strTmp+ ''( '' +@fldNames+ '') from (select top '' +str((@PageIndex-1)*@PageSize)   
+ '' '' +@fldName+ '' from '' +@tblName+ '' '' +@strOrder+ '') as tblTmp) '' +@strOrder 

		if @strWhere != '' '' 
			if(charindex('','',@str1,0)>0)
				set @strSQL = ''select top '' +str(@PageSize)+ '' '' +@strGetFields+ '' from '' +@tblName+ '' where '' +@strWhere+ '' and  '' +@keyDate+ '' not in (select top '' +str((@PageIndex-1)*@PageSize)  
+ '' ''+ @keyDate+  '' from '' +@tblName+ '' where '' +@strWhere+ '' '' +@strOrder+ '') ''  +@strOrder 
			else
				set @strSQL = ''select top '' +str(@PageSize)+ '' '' +@strGetFields+ '' from '' +@tblName+ '' where '' +@fldName+ '' '' +@strTmp + ''( '' +@fldNames+ '') from (select top '' +str((@PageIndex-1)*@PageSize)  
+ '' '' +@fldName+ '' from '' +@tblName+ '' where '' +@strWhere+ '' '' +@strOrder+ '') as tblTmp) and '' +@strWhere+ '' '' +@strOrder 
	end   
end
exec   (@strSQL) 





' 
END
--8.0--[SP_SelectByWhere]------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_SelectByWhere]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_SelectByWhere]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_SelectByWhere]
(
  @strTableName nvarchar(200), 
  @strWhere nvarchar(200)='''',
  @strSelect nvarchar(300)= ''*'',
  @strOrder nvarchar(50)=''''
)
AS
begin transaction
   declare @sql varchar(4000)
   begin
      set @sql= ''select ''+ @strSelect+ '' from ''+ @strTableName + '' '' +@strWhere + '' '' +@strOrder
      execute(@sql)
   end
   if @@error=0
   begin
      commit transaction
   end
   else
   begin
      rollback transaction
   end
' 
END
--9.0--[SP_U_U_UserOperate_UpdateTemplate]------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_U_UserOperate_UpdateTemplate]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_U_UserOperate_UpdateTemplate]')
end
begin
EXEC sp_executesql @statement = N'
CREATE  PROCEDURE [SP_U_U_UserOperate_UpdateTemplate]
@ID int,
@Template Nvarchar(50)
AS 
BEGIN TRAN  
  UPDATE U_U_UserOperate SET Template_New=@Template WHERE ID=@ID
IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END
---10.0--[SP_W_Ad_MemberInfo_SelectMeb]-----------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_Ad_MemberInfo_SelectMeb]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_Ad_MemberInfo_SelectMeb]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_Ad_MemberInfo_SelectMeb]
@strWhere NVARCHAR(200)
AS
BEGIN
begin transaction
   declare @sql varchar(4000)
   begin
      set @sql= ''SELECT W_Ad_Table.T_ID, W_Ad_Table.Ad_Type, W_Ad_Table.Ad_Name, W_Ad_Table.Ad_Page, W_Ad_Table.Ad_Height, W_Ad_Table.Ad_Width, W_Ad_Table.DayMoney, W_Ad_Table.Ad_StartDate, W_Ad_MemberInfo.Ad_Adrees,W_Ad_MemberInfo.Ad_Leter,W_Ad_MemberInfo.Ad_ImageAdrees ,W_Ad_MemberInfo.Ad_Day,W_Ad_MemberInfo.M_ID,W_Ad_MemberInfo.[User_ID],W_Ad_MemberInfo.[Ad_RestrictTime] FROM W_Ad_Table INNER JOIN W_Ad_MemberInfo ON W_Ad_Table.T_ID = W_Ad_MemberInfo.T_ID''+'' ''+@strWhere
      execute(@sql)
   end
   if @@error=0
   begin
      commit transaction
   end
   else
   begin
      rollback transaction
   end
END

' 
END
---11.0--[SP_W_Ad_Table_Select]---------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_Ad_Table_Select]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_Ad_Table_Select]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE  [SP_W_Ad_Table_Select]  
(
@strWhere nvarchar(200)
)
AS 
BEGIN
begin transaction
   declare @sql varchar(4000)
   begin
      set @sql= ''SELECT T_ID,Ad_Name,Ad_Type,Ad_Page,Ad_Width,Ad_Height,DayMoney,Ad_ImageAdrees,Ad_Adrees FROM W_Ad_Table''+'' ''+@strWhere
      execute(@sql)
   end
   if @@error=0
   begin
      commit transaction
   end
   else
   begin
      rollback transaction
   end
END
' 
END
---12.0---[SP_W_BusinessInfoSet_Update]----------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_BusinessInfoSet_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_BusinessInfoSet_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_BusinessInfoSet_Update]
(
@VerifyAdd bit,
@VerifyEdit bit,
@Email bit,
@Message bit,	
@MessageTitle nvarchar(50), 	
@MessageInfo nvarchar(1000),	
@EmailTitle nvarchar(50),
@EmailInfo nvarchar(1000),
@MarkAdd smallint,
@MarkEdit smallint
)
AS 
BEGIN TRAN
IF EXISTS(SELECT count(1) FROM W_BusinessInfoSet )  
BEGIN
  INSERT INTO W_BusinessInfoSet([VerifyAdd],[VerifyEdit],[Email],[Message],[MessageTitle],[MessageInfo],[EmailTitle],[EmailInfo],[MarkAdd],[MarkEdit])
  VALUES(0,0,0,0,''商业信息提示'',''短信息提醒'',''商业信息提示'',''邮件提醒'',0,0)
END
ELSE
BEGIN
  UPDATE W_BusinessInfoSet SET 
  [VerifyAdd] = @VerifyAdd, 
  [VerifyEdit] = @VerifyEdit, 
  [Email] = @Email, 
  [Message] = @Message, 
  [MessageTitle] = @MessageTitle, 
  [MessageInfo] = @MessageInfo,
  [EmailTitle] = @EmailTitle,
  [EmailInfo] = @EmailInfo,
  [MarkAdd] = @MarkAdd,
  [MarkEdit] = @MarkEdit
END
IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END
----13.0--[SP_W_Expo_Update]---------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_Expo_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_Expo_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_Expo_Update]
@id int,
@TypeID int ,
@Title nvarchar(100) ,
@ExpoType nvarchar(40) ,
@StartDate datetime ,
@EndDate datetime ,
@Place nvarchar(100) ,
@Authorize nvarchar(100) ,
@FrontFor nvarchar(100) ,
@Undertake nvarchar(100) ,
@Aidance nvarchar(100) ,
@Summary NTEXT ,
@Range nvarchar(1000) ,
@Period nvarchar(40) ,
@Homepage nvarchar(200) ,
@Picture nvarchar(200) ,
@ShowArea int ,
@Businessmen int ,
@Spectator int ,
@Price int ,
@MinNum int 
AS
BEGIN TRAN 
DECLARE @isVerify int 
SELECT @isVerify=isEdit FROM  W_TradeSettings WHERE TradeType=''展会''
--根据商机信息设置表判断修改商机信息是否需要审核
IF @isVerify=1
   SET @isVerify = 3
ELSE
   SET @isVerify = 1
UPDATE W_Expo SET 
[TypeID] = @TypeID,[Title] = @Title,[ExpoType] = @ExpoType,[StartDate] = @StartDate,[EndDate] = @EndDate,[Place] = @Place,[Authorize] = @Authorize,[FrontFor] = @FrontFor,[Undertake] = @Undertake,[Aidance] = @Aidance,[Summary] = @Summary,[Range] = @Range,[Period] = @Period,[Homepage] = @Homepage,[Picture] = @Picture,[ShowArea] = @ShowArea,[Businessmen] = @Businessmen,[Spectator] = @Spectator,[Price] = @Price,[MinNum] = @MinNum,Verify=@isVerify
WHERE [ID] = @ID
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN	
' 
END
----14.0--[SP_W_Expo_UpdateVerify]--------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_Expo_UpdateVerify]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_Expo_UpdateVerify]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_Expo_UpdateVerify]
@ID INT,
@Verify INT
AS
BEGIN TRAN
SELECT  @Verify=Verify FROM W_Expo WHERE ID=@ID
IF(@Verify=''1'')
	UPDATE W_Expo SET Verify=''3'' WHERE ID=@ID
ELSE
	UPDATE W_Expo SET Verify=''1'' WHERE ID=@ID

IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN	
' 
END
-----15.0--[SP_W_ExpoNews_Add]---------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_ExpoNews_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_ExpoNews_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_ExpoNews_Add]
@TypeID int ,
@Title nvarchar(100) ,
@Attribute nvarchar(100)='''' ,
@Origin nvarchar(100)='''',
@Picture nvarchar(100)='''',
@Detail nvarchar(100)='''' 
AS 
BEGIN TRAN
IF EXISTS(SELECT * FROM W_ExpoNews WHERE Title = @Title)
BEGIN
	RAISERROR(''此展会资讯已存在'', 16, 1)
END
ELSE
	INSERT INTO W_ExpoNews(
	[TypeID],[Title],[Attribute],[Origin],[Detail],[Picture]
	)VALUES(
	@TypeID,@Title,@Attribute,@Origin,@Detail,@Picture
	)
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN	

' 
END
--16.0---[SP_U_C_Picture_Add]------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_C_Picture_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_C_Picture_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_C_Picture_Add]
@CircleID INT,
@MemberID INT,
@TypeID INT,
@Picture NVARCHAR(100),
@Explain nvarchar(200),
@AddDate datetime
as
BEGIN TRAN  
BEGIN 
	INSERT INTO U_C_Picture([CircleID],[MemberID],[TypeID],[Picture],[Explain],[AddDate])VALUES(@CircleID,@MemberID,@TypeID,@Picture,@Explain,@AddDate)
	update U_C_PictureType set MemberID=@MemberID,AddDate=@AddDate where CircleID=@CircleID and ID=@TypeID
END
	
IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END
----17.0---[SP_C_Certificate_Update]---------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_C_Certificate_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_C_Certificate_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_C_Certificate_Update]
@ID INT,
@UserID INT,
@Name NVARCHAR(20),
@URL NVARCHAR(100),
@Organization NVARCHAR(50),
@LicenceDate DATETIME,
@ExpiryDate DATETIME
AS 
BEGIN TRAN  
	     
   UPDATE C_Certificate SET 
[UserID] = @UserID, [Name] = @Name, [URL] = @URL, [Organization] = @Organization, 
[LicenceDate] = @LicenceDate, [ExpiryDate] = @ExpiryDate
WHERE [ID] = @ID

IF @@error <> 0
begin
   rollback tran
   return -2
END

COMMIT TRAN 
' 
END
------18.0----[SP_C_Certificate_Add]---------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_C_Certificate_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_C_Certificate_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_C_Certificate_Add](
@UserID INT ,
@Name NVARCHAR(50) ,
@URL NVARCHAR(50) ,
@Organization NVARCHAR(50) ,
@LicenceDate DATETIME ,
@ExpiryDate DATETIME ,
@AddDate DATETIME 
)
AS
BEGIN TRAN  
IF EXISTS(SELECT * FROM C_Certificate WHERE [UserID] = @UserID AND [Name] = @Name)
BEGIN 
	RETURN -1
END
ELSE
BEGIN
	
 INSERT INTO C_Certificate(
[UserID],[Name],[URL],[Organization],[LicenceDate],[ExpiryDate],[AddDate])
    VALUES(@UserID,@Name,@URL,@Organization,@LicenceDate,@ExpiryDate,@AddDate)
END				
				
IF @@error <> 0
begin
	rollback TRAN
	return -2
end
COMMIT TRAN 
' 
END
---19.0---[SP_U_C_Discussion_UpdateWebTop]--------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_C_Discussion_UpdateWebTop]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_C_Discussion_UpdateWebTop]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_C_Discussion_UpdateWebTop]
@TopicID INT,
@WebTop BIT=''''
AS
BEGIN TRAN
		IF(@WebTop=''True'')
			UPDATE U_C_Discussion SET WebTop=''False'' WHERE TopicID=@TopicID
		ELSE
			UPDATE U_C_Discussion SET WebTop=''True'' WHERE TopicID=@TopicID

IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END
----20.0-----[SP_U_C_Discussion_UpdateWebPith]--------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_C_Discussion_UpdateWebPith]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_C_Discussion_UpdateWebPith]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_C_Discussion_UpdateWebPith]
@TopicID INT,
@WebPith BIT=''''
AS
BEGIN TRAN
		IF(@WebPith=''True'')
			UPDATE U_C_Discussion SET WebPith=''False'' WHERE TopicID=@TopicID
		ELSE
			UPDATE U_C_Discussion SET WebPith=''True'' WHERE TopicID=@TopicID

IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN' 
END
-----21.0---[SP_U_C_Discussion_UpdatePrivateTopic]------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_C_Discussion_UpdatePrivateTopic]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_C_Discussion_UpdatePrivateTopic]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_C_Discussion_UpdatePrivateTopic]
@TopicID INT,
@PrivateTopic BIT=''''
AS
BEGIN TRAN
		IF(@PrivateTopic=''True'')
			UPDATE U_C_Discussion SET PrivateTopic=''False'' WHERE TopicID=@TopicID
		ELSE
			UPDATE U_C_Discussion SET PrivateTopic=''True'' WHERE TopicID=@TopicID

	IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN' 
END
---22.0---[SP_U_C_Discussion_Delete]--------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_C_Discussion_Delete]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_C_Discussion_Delete]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_C_Discussion_Delete]
@TopicID INT
AS
BEGIN TRAN
		DELETE FROM U_C_Discussion WHERE TopicID=@TopicID
		DELETE FROM U_C_Replies WHERE TopicID=@TopicID
IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN' 
END
-----23.0---[SP_U_C_Discussion_UpdateTopTopic]-----------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_C_Discussion_UpdateTopTopic]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_C_Discussion_UpdateTopTopic]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_C_Discussion_UpdateTopTopic]
@TopicID INT,
@TopTopic BIT=''''
AS
BEGIN TRAN
		IF(@TopTopic=''True'')
			UPDATE U_C_Discussion SET TopTopic=''False'' WHERE TopicID=@TopicID
		ELSE
			UPDATE U_C_Discussion SET TopTopic=''True'' WHERE TopicID=@TopicID

IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN' 
END
-----24.0--[SP_U_C_Discussion_UpdatePithTopic]-----------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_C_Discussion_UpdatePithTopic]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_C_Discussion_UpdatePithTopic]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_C_Discussion_UpdatePithTopic]
@TopicID INT,
@PithTopic BIT=''''
AS
BEGIN TRAN
		IF(@PithTopic=''True'')
		     UPDATE U_C_Discussion SET PithTopic=''False'' WHERE TopicID=@TopicID
		ELSE
		     UPDATE U_C_Discussion SET PithTopic=''True'' WHERE TopicID=@TopicID

IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN' 
END
---25.0--[SP_U_C_Discussion_UpdateRecommend]---------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_C_Discussion_UpdateRecommend]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_C_Discussion_UpdateRecommend]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_C_Discussion_UpdateRecommend]
@TopicID INT,
@Recommend BIT=''''
AS
BEGIN TRAN
	      IF(@Recommend=''True'')
		    UPDATE U_C_Discussion SET Recommend=''False'' WHERE TopicID=@TopicID
	       ELSE
		    UPDATE U_C_Discussion SET Recommend=''True'' WHERE TopicID=@TopicID
	
IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END
---26.0---[SP_U_C_Discussion_Add]---------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_C_Discussion_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_C_Discussion_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_C_Discussion_Add]
@CircleID int,
@MemberID int,
@Subject nvarchar(60),
@Message text,
@Picture nvarchar(100)
as
BEGIN TRAN
if  exists (select * from U_C_Discussion where [CircleID]=@CircleID and Subject=@Subject)
	BEGIN
		RAISERROR(''该商圈话题已存在'', 16, 1)
	END
else
	BEGIN
		insert into U_C_Discussion([CircleID],[MemberID],[Subject],[Message],[Picture]) values(@CircleID,@MemberID,@Subject,@Message,@Picture)
	END
IF @@error <> 0
begin
rollback TRAN
return -2
end
COMMIT TRAN
' 
END
---27.0----[SP_U_C_BusinessCircle_Update]--------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_C_BusinessCircle_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_C_BusinessCircle_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_C_BusinessCircle_Update]
@CirCleID INT,
@Introduce nvarchar(500),
@Post nvarchar(500),
@Tag nvarchar(50),
@Picture nvarchar(200)
AS 
BEGIN TRAN  
 BEGIN 
	UPDATE U_C_BusinessCircle SET 
	[Introduce] = @Introduce,[Post]=@Post,[Tag]=@Tag,[Picture]=@Picture
	WHERE [CirCleID] = @CirCleID
END

IF @@error <> 0
begin
rollback TRAN
return -2
end
COMMIT TRAN
' 
END
----28.0------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_C_BusinessCircle_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_C_BusinessCircle_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_C_BusinessCircle_Add]
@UserID INT,
@CircleName nvarchar(50),
@TypeID nvarchar(20),
@Introduce nvarchar(500)='''',
@Post nvarchar(500)='''',
@Tag nvarchar(50)='''',
@Picture varchar(200)=''''
AS
declare @CircleCount int,@GradeID int,@AddCount int,@LoginName nvarchar(50)
BEGIN TRAN
		select @AddCount=CircleCount from U_UserOperate where [UserID]=@UserID
		select @GradeID=GradeID from U_UserInfo where UserID=@UserID
				select @CircleCount=CircleCount from U_VipGrade where ID=@GradeID
		if exists(select * from U_C_BusinessCircle where [CircleName]=@CircleName)
			BEGIN
				RAISERROR(''该商圈已存在'', 16, 1)
			END
		else
			BEGIN
				insert into U_C_BusinessCircle([UserID],[CircleName],[TypeID],[Introduce],[Post],[Tag],[Picture]) values(@UserID,@CircleName,@TypeID,@Introduce,@Post,@Tag,@Picture)
				select @LoginName=LoginName from U_UserInfo where UserID=@UserID
				insert into U_C_Member(CircleID,MemberID,LoginName,Flag,AddDate)values(@@identity,@UserID,@LoginName,''a'',getdate())
				if @CircleCount>@AddCount
					update U_UserOperate set CircleCount=@CircleCount+1
			END
IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN' 
END
----29.0--[SP_U_C_BusinessCircle_UpdateRights]---------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_C_BusinessCircle_UpdateRights]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_C_BusinessCircle_UpdateRights]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_C_BusinessCircle_UpdateRights]
@CircleID INT,
@Rights VARCHAR(10)
AS
BEGIN TRAN  
		UPDATE U_C_BusinessCircle SET Rights=@Rights WHERE CircleID=@CircleID 
	IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN' 
END

-----31.0---[ProductListUpdateSpecialList]---------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[ProductListUpdateSpecialList]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [ProductListUpdateSpecialList]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [ProductListUpdateSpecialList]
@SendUserID int ,
@Title varchar(50) ,
@Info varchar(500) ,
@Date datetime,
@Nature varchar(20) ,
@ReceiveUserID nvarchar(500)
AS
begin transaction
DECLARE @intlen int              --会员ID开始位置
DECLARE @intUserId int             --会员ID
DECLARE @strReceive Nvarchar(500)  --剩余会员ID串
set @strReceive=@ReceiveUserID
while @strReceive !=''''   --判读是否已经读取完，没完则继续插入
begin
   Set @intlen=CharIndex('','',@strReceive,0)
   if @intlen=0    --判断是否是第一个
   begin
       set @intUserId=cast(@strReceive as int)  --截取接收者UserID
       set @strReceive=''''
   end 
   else 
   begin
       set @intUserId=cast(SUBSTRING(@strReceive,0,@intlen) as int)
       set @strReceive=SUBSTRING(@strReceive,@intlen+1,len(@strReceive)-@intlen) 
   end    
   INSERT INTO C_Message([SendUserID],[Title],[Info],[Date],[Nature],ReceiveUserID)
   VALUES(@SendUserID,@Title,@Info,@Date,@Nature,@intUserId)
end   
if @@error=0
begin
    commit transaction
end
else
begin
    rollback transaction
end
' 
END
----32.0---[SP_C_Message_Add]---------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_C_Message_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_C_Message_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_C_Message_Add]
@SendUserID int ,
@Title varchar(50) ,
@Info varchar(500) ,
@Date datetime ,
@Nature varchar(20) ,
@ReceiveUserID nvarchar(500)
AS
begin transaction
DECLARE @intlen int              --会员ID开始位置
DECLARE @intUserId int             --会员ID
DECLARE @strReceive Nvarchar(500)  --剩余会员ID串
set @strReceive=@ReceiveUserID
while @strReceive !=''''   --判读是否已经读取完，没完则继续插入
begin
   Set @intlen=CharIndex('','',@strReceive,0)
   if @intlen=0    --判断是否是第一个
   begin
       set @intUserId=cast(@strReceive as int)  --截取接收者UserID
       set @strReceive=''''
   end 
   else 
   begin
       set @intUserId=cast(SUBSTRING(@strReceive,0,@intlen) as int)
       set @strReceive=SUBSTRING(@strReceive,@intlen+1,len(@strReceive)-@intlen) 
   end    
   INSERT INTO C_Message([SendUserID],[Title],[Info],[Date],[Nature],ReceiveUserID)
   VALUES(@SendUserID,@Title,@Info,@Date,@Nature,@intUserId)
end   
if @@error=0
begin
    commit transaction
end
else
begin
    rollback transaction
end
' 
END
---33.0--[SP_C_Message_Circle_Add]----------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_C_Message_Circle_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_C_Message_Circle_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE  [SP_C_Message_Circle_Add]
@SendUserID int ,
@Title varchar(50) ,
@Info varchar(500) ,
@Date datetime ,
@Nature varchar(20) ,
@ReceiveUserID nvarchar(200)
AS
DECLARE @ReceiveUser nvarchar(200);
DECLARE @t1 nvarchar(200);
BEGIN TRAN
set @ReceiveUser = @ReceiveUserID+ '',''
  while CHARINDEX('','',@ReceiveUser)>0
	   begin
		   SELECT @t1=LEFT(@ReceiveUser,CHARINDEX('','',@ReceiveUser+'','')-1),@ReceiveUser=STUFF(@ReceiveUser,1,CHARINDEX('','',@ReceiveUser+'',''),'''')
		   INSERT INTO C_Message([SendUserID],[Title],[Info],[Date],[Nature],ReceiveUserID)
		   VALUES(@SendUserID,@Title,@Info,@Date,@Nature,@t1)
	   end 
   IF @@ERROR<>0
     BEGIN
        ROLLBACK TRAN 
        return -2
     END

 COMMIT TRAN
' 
END
---34.0----[SP_P_CheckProductCount]-----------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_P_CheckProductCount]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_P_CheckProductCount]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_P_CheckProductCount]
@UnreadMessageCount int output,--未读留言
@PastDueCount int output,				--商机过期数量
@UnauditedCount int output,				--未审核商机数量
@UserID INT								--用户ID
AS
BEGIN TRAN
	SELECT @UnreadMessageCount=count(1) FROM C_Message WHERE [Restore]=0 AND ReceiveUserID=@UserID
	SELECT @UnauditedCount=count(1) FROM C_Supply WHERE Verify=3 AND UserID=@UserID
	SELECT @PastDueCount=count(1) FROM C_Supply WHERE (datediff(hh,EndDate,getdate())>0) AND UserID=@UserID
IF @@error <> 0
begin
	rollback TRAN
	return -2
end
COMMIT TRAN 
' 
END
----35.0-----[SP_W_WillAgent_Add]------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_WillAgent_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_WillAgent_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_WillAgent_Add]
@UserID int ,
@Name nvarchar(100) ,
@AgentType int ,
@Title nvarchar(100) ,
@Validity nvarchar(100) ,
@Count int ,
@Investment nvarchar(100) ,
@Price nvarchar(100) ,
@Standard nvarchar(100) ,
@Detail NTEXT ,
@Picture nvarchar(200) ,
@Obligate nvarchar(100),
@Verify smallint =3 ,
@Return int output
AS 
DECLARE @isVerify bit,@isEmails bit,@isSMS bit,@SMSTitle NVARCHAR(50),@SMSInfo NVARCHAR(500),@Addcount int,@WillAgentCount int
BEGIN TRAN
IF EXISTS(SELECT * FROM W_WillAgent WHERE Title = @Title and UserID=@UserID)
	BEGIN
		RAISERROR(''此商机标题已存在'', 16, 1)
	END
ELSE
BEGIN 
SELECT @isVerify=isADD, @isSMS=isSMS,@SMSTitle=SMSTitle,@SMSInfo=SMSInfo,@isEmails=isEmail FROM  W_TradeSettings WHERE TradeType=''WillAgent''
SELECT @Addcount=AddWillAgent FROM U_UserOperate WHERE UserID=@UserID
SET @Addcount=@Addcount+1
SET @WillAgentCount=@WillAgentCount+1
--根据商机信息设置表判断用户是否需要审核
IF @isVerify=1
  SET @Verify = 3
ELSE
  SET @Verify = 1
--添加愿做代理信息
INSERT INTO W_WillAgent(
[UserID],[Name],[AgentType],[Title],[Validity],[Count],[Investment],[Price],[Standard],[Detail],[Picture],[Obligate],[Verify]
)VALUES(
@UserID,@Name,@AgentType,@Title,@Validity,@Count,@Investment,@Price,@Standard,@Detail,@Picture,@Obligate,@Verify
)
--跟新会员信息统计表发布的愿做代理数量
update U_UserOperate set WillAgentCount=@WillAgentCount,AddWillAgent=@Addcount where UserID=@UserID
--根据商机信息设置表判断发布商机成功后是否给会员发送留言
if @isSms=1
   INSERT INTO C_Message(SendUserID,ReceiveUserID,Title,Info,Nature,Date)VALUES(''0'',@UserID,@SMSTitle,@SMSInfo,''WillAgent'',getdate())
if @isEmails=1
	set @Return=1
else
	set @Return=0
END
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN
' 
END
---36.0---[SP_W_SincereAgent_Add]-------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_SincereAgent_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_SincereAgent_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_SincereAgent_Add]
@UserID int ,
@Name nvarchar(100)='''' ,
@AgentType int='''' ,
@Title nvarchar(100)='''' ,
@Validity nvarchar(40) ='''',
@Brand nvarchar(100) ='''',
@Investment nvarchar(100) ='''',
@Amount float ='''',
@InitialFee float='''' ,
@Require nvarchar(100)='''' ,
@Cradle nvarchar(100) ='''',
@Payback float='''' ,
@Period float ='''',
@Concession float='''' ,
@Contract float ='''',
@Count int ='''',
@BusinessDate int='''' ,
@isMoreBrand bit ='''',
@BusinessMode nvarchar(100)='''' ,
@Developmental nvarchar(100)='''' ,
@Detail NTEXT ,
@Picture nvarchar(200) ='''',
@Verify smallint =3,
@Recommend bit ='''',
@Obligate nvarchar(100)='''',
@Return int output
AS
DECLARE @isVerify bit,@isEmails bit,@isSMS bit,@SMSTitle NVARCHAR(50),@SMSInfo NVARCHAR(500),@Addcount int,@SincereAgentCount int
BEGIN TRAN
IF EXISTS(SELECT * FROM W_SincereAgent WHERE Title = @Title and UserID=@UserID)
	BEGIN
		RAISERROR(''商机已存在'', 16, 1)
	END
ELSE
	BEGIN 
	SELECT @isVerify=isADD, @isSMS=isSMS,@SMSTitle=SMSTitle,@SMSInfo=SMSInfo,@isEmails=isEmail FROM  W_TradeSettings WHERE TradeType=''招商''
	SELECT @SincereAgentCount=SincereAgentCount,@Addcount=AddSincereAgent FROM U_UserOperate WHERE UserID=@UserID
	SET @Addcount=@Addcount+1 
	SET @SincereAgentCount=@SincereAgentCount+1
    --根据商机信息设置表判断用户是否需要审核
	IF @isVerify=1
      SET @Verify = 3
	ELSE
      SET @Verify = 1
	--添加诚招代理信息
    INSERT INTO W_SincereAgent(
	[UserID],[Name],[AgentType],[Title],[Validity],[Brand],[Investment],[Amount],[InitialFee],[Require],[Cradle],[Payback],[Period],[Concession],[Contract],[Count],[BusinessDate],[isMoreBrand],[BusinessMode],[Developmental],[Detail],[Picture],[Verify],[Recommend],[Obligate]
	)VALUES(
	@UserID,@Name,@AgentType,@Title,@Validity,@Brand,@Investment,@Amount,@InitialFee,@Require,@Cradle,@Payback,@Period,@Concession,@Contract,@Count,@BusinessDate,@isMoreBrand,@BusinessMode,@Developmental,@Detail,@Picture,@Verify,@Recommend,@Obligate
	)
	--跟新会员信息统计表招商代理统计和发布的求购数量
	update U_UserOperate set SincereAgentCount=@SincereAgentCount,AddSincereAgent=@Addcount where UserID=@UserID
	--根据商机信息设置表判断发布商机成功后是否给会员发送留言
    if @isSms=1
	   INSERT INTO C_Message(SendUserID,ReceiveUserID,Title,Info,Nature,Date)VALUES(''0'',@UserID,@SMSTitle,@SMSInfo,''SincereAgent'',getdate())
	if @isEmails=1
		set @Return=1
	else
		set @Return=0
	END
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN
' 
END
---37.0---[SP_C_Supply_Add]-------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_C_Supply_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_C_Supply_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_C_Supply_Add] 
@UserID int ,
@IndType varchar(20)='''',
@Name nvarchar(40),
@Key nvarchar(100) ='''',
@Info nvarchar(4000)='''',
@Thumbnail nvarchar(100)='''',
@Picture_1 nvarchar(100)='''',
@Picture_2 nvarchar(100) ='''',
@Picture_3 nvarchar(100)='''',
@ProType int ='''',
@Place nvarchar(100)='''',
@Brand nvarchar(100) ='''',
@Model nvarchar(100) ='''',
@Price int ='''',
@Quantity int='''' ,
@Units nvarchar(10)='''' ,
@Minimum int='''' ,
@Deadline int ='''',
@StartDate datetime ='''',
@EndDate datetime ='''',
@Remark nvarchar(100) ='''',
@Document nvarchar(100)='''',
@AddDate datetime='''',
@Verify int= 3,
@Return int output
AS
set @AddDate=getdate()
DECLARE @isVerify bit,@isEmails bit,@isSMS bit,@SMSTitle NVARCHAR(50),@SMSInfo NVARCHAR(500),@count int,@SupplyCount int,@imgFile nvarchar(100)
BEGIN TRAN
	IF EXISTS(SELECT * FROM C_Supply WHERE Name = @Name and UserID=@UserID)
		BEGIN
			RAISERROR(''商机已存在'', 16, 1)
		END
	ELSE
	BEGIN
		SELECT @isVerify=isADD, @isSMS=isSMS,@SMSTitle=SMSTitle,@SMSInfo=SMSInfo,@isEmails=isEmail FROM  W_TradeSettings WHERE TradeType= ''供应''
		SELECT @SupplyCount=SupplyCount,@count=AddSupply FROM U_UserOperate WHERE UserID=@UserID
		SET @count=@count+1
		SET @SupplyCount=@SupplyCount+1
		--根据商机信息设置表判断用户是否需要审核
        IF @isVerify=''1''
          SET @Verify = 3
		ELSE
          SET @Verify = 1
		set @imgFile=Replace(@Picture_1,''/t_'',''/'')
		--添加供应信息		
		INSERT INTO C_Supply(
		[UserID],[IndType],[Name],[Key],[Info],[Thumbnail],[Picture_1],[Picture_2],[Picture_3],[ProType],[Place],[Brand],[Model],[Price],[Quantity],[Units],[Minimum],[Deadline],[StartDate],[EndDate],[Remark],[Document],[Verify]
		)VALUES(
		@UserID,@IndType,@Name,@Key,@Info,@Thumbnail,@imgFile,@Picture_2,@Picture_3,@ProType,@Place,@Brand,@Model,@Price,@Quantity,@Units,@Minimum,@Deadline,@StartDate,@EndDate,@Remark,@Document,@Verify
		)
		--跟新会员信息统计表供应产品统计和发布的供应数量
		update U_UserOperate set SupplyCount=@SupplyCount,AddSupply=@count where UserID=@UserID
		--根据商机信息设置表判断发布商机成功后是否给会员发送留言
        if @isSms=''1''
		   INSERT INTO C_Message(SendUserID,ReceiveUserID,Title,Info,Nature,Date)VALUES(''0'',@UserID,@SMSTitle,@SMSInfo,''supply'',@AddDate)
		if @isEmails=''1''
			if @isVerify=0
			set @Return=1
		else
			set @Return=0
    END
IF @@error <> 0
begin
rollback tran
return -2
END
COMMIT TRAN 
' 
END
---38.0---[SP_C_Purchase_Add]------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_C_Purchase_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_C_Purchase_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_C_Purchase_Add]
@UserID int ,
@IsUser bit,
@IndType varchar(20) ,
@Name nvarchar(40) ,
@Key nvarchar(100) ,
@Info nvarchar(4000) ,
@Picture_1 nvarchar(50)='''' ,
@Picture_2 nvarchar(50)='''' ,
@Picture_3 nvarchar(50)='''' ,
@StartDate datetime,
@EndDate datetime,
@Brand nvarchar(40) ,
@Model nvarchar(100)='''' ,
@Unit nvarchar(50)='''',
@Count int,
@Price nvarchar(20),
@Verify int=3,
@Return int output
AS 
BEGIN TRAN
DECLARE @isVerify bit,@isEmails bit,@isSMS bit,@SMSTitle NVARCHAR(50),@SMSInfo NVARCHAR(500),@Addcount int,@purchaseCount int,@imgFile nvarchar(50)
IF EXISTS(SELECT * FROM C_Purchase WHERE Name = @Name and UserID=@UserID)
	BEGIN
		RAISERROR(''商机已存在'', 16, 1)
	END
ELSE
	BEGIN
		SELECT @isVerify=isADD, @isSMS=isSMS,@SMSTitle=SMSTitle,@SMSInfo=SMSInfo,@isEmails=isEmail FROM  W_TradeSettings WHERE TradeType=''求购''
		SELECT @purchaseCount=purchaseCount,@Addcount=AddPurchase FROM U_UserOperate WHERE UserID=@UserID
		SET @Addcount=@Addcount+1
		SET @purchaseCount=@purchaseCount+1
		--根据商机信息设置表判断用户是否需要审核
		IF @isVerify=1
		  SET @Verify = 3
		ELSE
		  SET @Verify = 1
		--添加求购信息
		set @imgFile=Replace(@Picture_1,''/t_'',''/'')
		INSERT INTO C_Purchase(
		[UserID],[IsUser],[IndType],[Name],[Key],[Info],[Picture_1],[Picture_2],[Picture_3],[StartDate],[EndDate],[Brand],[Model],[Unit],[Count],[Price],[Verify]
		)VALUES(
		@UserID,@IsUser,@IndType,@Name,@Key,@Info,@imgFile,@Picture_2,@Picture_3,@StartDate,@EndDate,@Brand,@Model,@Unit,@Count,@Price,@Verify
		)
		--跟新会员信息统计表求购产品统计和发布的求购数量
		update U_UserOperate set purchaseCount=@purchaseCount,AddPurchase=@Addcount where UserID=@UserID
		--根据商机信息设置表判断发布商机成功后是否给会员发送留言
		if @isSms=1
		   INSERT INTO C_Message(SendUserID,ReceiveUserID,Title,Info,Nature,Date)VALUES(''0'',@UserID,@SMSTitle,@SMSInfo,''purchase'',getdate())
		if @isEmails=1
			set @Return=1
		else
			set @Return=0
	END
IF @@error<>0
begin
ROLLBACK TRAN 
return -2
end
	
COMMIT TRAN 


' 
END
---39.0----[SP_W_Expo_Add]----------------------------------------------------------------------------  
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_Expo_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_Expo_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_Expo_Add]
@UserID int,
@TypeID int ,
@Title nvarchar(100),
@ExpoType nvarchar(40) ,
@StartDate datetime='''' ,
@EndDate datetime='''' ,
@Place nvarchar(500)='''' ,
@Authorize nvarchar(500)='''' ,
@FrontFor nvarchar(500)='''',
@Undertake nvarchar(100)='''' ,
@Aidance nvarchar(500)='''' ,
@Summary NTEXT ,
@Range nvarchar(3000)='''',
@Period nvarchar(400) ,
@Homepage nvarchar(200)='''' ,
@Picture nvarchar(200)='''' ,
@ShowArea int='''' ,
@Businessmen int='''' ,
@Spectator int='''' ,
@Price int='''' ,
@MinNum int='''' ,
@Verify int=3,
@Return int output
AS 
DECLARE @isVerify bit,@isEmails bit,@isSMS bit,@SMSTitle NVARCHAR(50),@SMSInfo NVARCHAR(500),@Addcount int,@ExpoCount int
BEGIN TRAN  
IF EXISTS(SELECT * FROM W_Expo WHERE  [Title] = @Title )
BEGIN 
	RAISERROR(''此展会已存在'', 16, 1)
END
ELSE
BEGIN
	SELECT @isVerify=isADD, @isSMS=isSMS,@SMSTitle=SMSTitle,@SMSInfo=SMSInfo,@isEmails=isEmail FROM  W_TradeSettings WHERE TradeType=''展会''
	SELECT @Addcount=AddExpo  FROM U_UserOperate WHERE UserID=@UserID
	SET @Addcount=@Addcount+1
	SET @ExpoCount=@ExpoCount+1
	--根据商机信息设置表判断用户是否需要审核
	IF @isVerify=1
	  SET @Verify = 3
	ELSE
	  SET @Verify = 1
	--添加展会信息
	INSERT INTO W_Expo(
	[UserID],[TypeID],[Title],[ExpoType],[StartDate],[EndDate],[Place],[Authorize],[FrontFor],[Undertake],[Aidance],[Summary],[Range],[Period],[Homepage],[Picture],[ShowArea],[Businessmen],[Spectator],[Price],[MinNum],[Verify]
	)VALUES(
	@UserID,@TypeID,@Title,@ExpoType,@StartDate,@EndDate,@Place,@Authorize,@FrontFor,@Undertake,@Aidance,@Summary,@Range,@Period,@Homepage,@Picture,@ShowArea,@Businessmen,@Spectator,@Price,@MinNum,@Verify
	)
	--跟新会员信息统计表发布的求购数量
	update U_UserOperate set ExpoCount=@ExpoCount,AddExpo=@Addcount where UserID=@UserID
	--根据商机信息设置表判断发布商机成功后是否给会员发送留言
	if @isSms=1
	   INSERT INTO C_Message(SendUserID,ReceiveUserID,Title,Info,Nature,Date)VALUES(''0'',@UserID,@SMSTitle,@SMSInfo,''SincereAgent'',getdate())
	if @isEmails=1
		set @Return=1
	else
		set @Return=0
END
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN
' 
END
----40.0---[SP_U_C_Member_Add]---------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_C_Member_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_C_Member_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_C_Member_Add]
@CircleID int,
@MemberID int,
@LoginName nvarchar(20),
@Flag nvarchar(50)
AS
BEGIN TRAN  
IF EXISTS(SELECT * FROM U_C_Member WHERE  [CircleID] = @CircleID AND MemberID=@MemberID )
	BEGIN 
		RAISERROR(''你已经是本圈成员'', 16, 1)
	END
 ELSE
	BEGIN 
		INSERT INTO U_C_Member([CircleID],[MemberID],[LoginName],[Flag])VALUES(@CircleID,@MemberID,@LoginName,@Flag)
	END
IF @@error <> 0
begin
rollback TRAN
return -2
end
COMMIT TRAN
' 
END
--41.0--[SP_U_C_Member_Update]----------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_C_Member_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_C_Member_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_C_Member_Update]
@CircleID INT,
@MemberID INT,
@Flag VARCHAR(10)
AS
BEGIN TRAN  
	UPDATE U_C_Member SET Flag=@Flag WHERE CircleID=@CircleID AND MemberID=@MemberID
IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN' 
END
---42.0-----[SP_U_C_MessageBoard_Add]--------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_C_MessageBoard_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_C_MessageBoard_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_C_MessageBoard_Add]
@CircleID int ,
@MemberID INT,
@Message nvarchar(500),
@AddDate datetime
AS 
BEGIN TRAN  
		    BEGIN 
				INSERT INTO U_C_MessageBoard([CircleID],[MemberID],[Message],[AddDate])VALUES(@CircleID,@MemberID,@Message,@AddDate)
			END
IF @@error <> 0
begin
rollback TRAN
return -2
end
COMMIT TRAN
' 
end
---43.0-----[SP_C_News_UpdateVerify]-------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_C_News_UpdateVerify]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_C_News_UpdateVerify]')
end
begin
EXEC sp_executesql @statement = N'
CREATE  PROCEDURE [SP_C_News_UpdateVerify]
@ID int,
@Verify bit
AS 

BEGIN TRAN  
SELECT  @Verify=Verify FROM C_News WHERE ID=@ID
IF(@Verify=1)
UPDATE C_News SET Verify=0 WHERE ID=@ID
ELSE
UPDATE C_News SET Verify=1 WHERE ID=@ID
IF @@error <> 0
begin
	rollback tran
	return -2
END
COMMIT TRAN 
' 
END
---44.0---[SP_C_news_Add]--------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_C_news_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_C_news_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_C_news_Add]
@UserID int ,
@Title nvarchar(100) ,
@Info nvarchar(4000) 
AS 
BEGIN TRAN  
IF EXISTS(SELECT * FROM C_news WHERE  [Title] = @Title )
	BEGIN 
		RAISERROR(''公司新闻已存在'', 16, 1)
	END
 ELSE
	BEGIN 
		INSERT INTO C_news([UserID],[Title],[Info])VALUES(@UserID,@Title,@Info)
	END

 IF @@error<>0
  
   begin
       rollback tran
    end

 commit tran

' 
END
---45.0--[SP_C_news_Update]-----------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_C_news_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_C_news_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_C_news_Update]
@ID int,
@Title nvarchar(100) ,
@Info nvarchar(4000) 
AS 
BEGIN TRAN  
BEGIN 
UPDATE C_news SET 
[Title] = @Title,[Info] = @Info
WHERE [ID] = @ID
END
IF @@error <> 0
begin
	rollback tran
	return -2
END
COMMIT TRAN 
' 
END
--46.0----[SP_U_C_PictureType_Add]--------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_C_PictureType_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_C_PictureType_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_C_PictureType_Add]
@CircleID INT,
@Name nvarchar(50)
AS
BEGIN TRAN  
IF EXISTS(SELECT * FROM U_C_PictureType WHERE  [CircleID] = @CircleID and [Name]=@Name)
	BEGIN 
		RAISERROR(''该圈中已存在此专辑'', 16, 1)
	END
 ELSE
   BEGIN 
	 INSERT INTO U_C_PictureType([CircleID],[Name])VALUES(@CircleID,@Name)
   END

IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END
----47.0---[SP_U_C_CircleType_Add]-----------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_C_CircleType_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_C_CircleType_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_C_CircleType_Add]
@Name nvarchar(20)
AS
BEGIN TRAN
		IF EXISTS(SELECT * FROM U_C_CircleType WHERE [Name]=@Name)
		   RAISERROR(''该圈已存在'',16,1)
		ELSE
		   INSERT INTO U_C_CircleType([Name])VALUES(@Name)
	
IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END
--48.0----[SP_U_C_CircleType_Update]-------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_C_CircleType_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_C_CircleType_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_C_CircleType_Update]
@ID INT,
@Name NVARCHAR(20)
AS
BEGIN TRAN
    Update U_C_CircleType set [Name]=@Name WHERE ID=@ID
	
IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END
---49.0---[SP_U_RegisterIP_Check]------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_RegisterIP_Check]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_RegisterIP_Check]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_RegisterIP_Check]
@IP nvarchar(50)
AS 
DECLARE @oldtime datetime;
DECLARE @BreakTime int;
SELECT  @BreakTime=MinTimeRegister FROM U_Register 
BEGIN TRAN tr
IF EXISTS(SELECT * FROM U_RegisterIP WHERE [IP] = @IP )
BEGIN
   SELECT  @oldTIme=LastTime FROM U_RegisterIP WHERE [IP] = @IP
   IF datediff(n,getdate(),@oldTIme) <@BreakTime
   BEGIN
      UPDATE U_RegisterIP SET [LastTime] = getdate() WHERE [IP] = @IP    
       COMMIT TRAN  tr
       RETURN -1
   END
   ELSE
   BEGIN
     COMMIT TRAN  tr
     RETURN -2
   END
END
ELSE
BEGIN
   INSERT INTO U_RegisterIP ([IP],[lastTime])VALUES(@IP,getdate())
   COMMIT TRAN tr
   RETURN -1
END  	
IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END
---50.0--[SP_C_ProType_Add]----------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_C_ProType_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_C_ProType_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE  PROCEDURE [SP_C_ProType_Add]
@UserID int ,
@Name nvarchar(100),
@AddDate datetime
AS 
declare @aa int
set @AddDate=getdate()
BEGIN TRAN  
	SELECT @aa=max(Sequence) FROM C_ProType WHERE  UserID=@UserID
	IF @aa is NULL
		SET @aa=1
	ELSE IF @aa=0
		set @aa=1
	ELSE
		set @aa=@aa+1 
	IF EXISTS(SELECT * FROM C_ProType WHERE  [Name] = @Name and UserID=@UserID)
		BEGIN 
			RAISERROR(''该分类已存在'', 16, 1)
		END
	 ELSE
		BEGIN 
			INSERT INTO C_ProType([UserID],[Name],[Sequence],[AddDate])VALUES(@UserID,@Name,@aa,@AddDate)
		END
IF @@error <> 0
begin
	rollback tran
	return -2
END
COMMIT TRAN 
' 
END
---51.0---[SP_C_ProType_Update]-------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_C_ProType_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_C_ProType_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE  PROCEDURE [SP_C_ProType_Update]
@ID int,
@Sequence int,
@Name nvarchar(50)
AS 
BEGIN TRAN  
BEGIN 
	UPDATE C_ProType SET [Sequence]=@Sequence,[Name]=@Name WHERE ID=@ID;
END

IF @@error <> 0
begin
	rollback tran
	return -2
END
COMMIT TRAN
' 
END
---52.0--[SP_W_Ad_Table_Update]----------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_Ad_Table_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_Ad_Table_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_Ad_Table_Update]
(
@Ad_Name VARCHAR(50),
@Ad_Type VARCHAR(20),
@Ad_Page VARCHAR(30),
@Ad_Width INT,
@Ad_Height INT,
@DayMoney MONEY,
@Ad_ImageAdrees NVARCHAR(200),
@Ad_Adrees NVARCHAR(200),
@id INT
)
AS
BEGIN TRAN
     BEGIN
     UPDATE [W_Ad_Table] SET [Ad_Name]=@Ad_Name,[Ad_Type]=@Ad_Type,[Ad_Page]=@Ad_Page,[Ad_Width]=@Ad_Width,[Ad_Height]=@Ad_Height,[DayMoney]=@DayMoney,[Ad_ImageAdrees]=@Ad_ImageAdrees,[Ad_Adrees]=@Ad_Adrees WHERE [T_ID]=@id	
  
     END

IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END
---53.0---[SP_W_Ad_Table_Insert]------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_Ad_Table_Insert]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_Ad_Table_Insert]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE  [SP_W_Ad_Table_Insert]
(
    @Ad_Name varchar(50),
    @Ad_Type varchar(20),
    @Ad_Page  varchar(30),
    @Ad_Width int,
    @Ad_Height int,
    @DayMoney money,
    @Ad_ImageAdrees nvarchar(200),
    @Ad_Adrees nvarchar(200)
)
AS
BEGIN TRAN
	insert into W_Ad_Table (Ad_Name,Ad_Type,Ad_Page,Ad_Width,Ad_Height,DayMoney,Ad_ImageAdrees,Ad_Adrees)values(@Ad_Name,@Ad_Type,@Ad_Page,@Ad_Width,@Ad_Height,@DayMoney,@Ad_ImageAdrees,@Ad_Adrees)

IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END
---54.0---[SP_W_Ad_Table_Delte]---------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_Ad_Table_Delte]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_Ad_Table_Delte]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_Ad_Table_Delte]
@id int
AS
BEGIN
  begin tran                           
		delete from W_Ad_Table where [T_ID]=@id
		delete from W_Ad_MemberInfo where [T_Id]=@id
  IF @@error=0 
	 begin
	      commit tran
	  end
  else
	 begin
		 rollback tran
	 end
END
' 
END
---55.0---[SP_W_Ad_MemberInfo_Select]---------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_Ad_MemberInfo_Select]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_Ad_MemberInfo_Select]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_Ad_MemberInfo_Select]
@userid int
AS
BEGIN
  begin tran                           
		SELECT W_Ad_MemberInfo.Ad_Day,W_Ad_MemberInfo.M_ID,W_Ad_MemberInfo.Ad_ImageAdrees, W_Ad_MemberInfo.Ad_Adrees,W_Ad_MemberInfo.Ad_Auditing,W_Ad_Table.Ad_Type, W_Ad_Table.Ad_Name, W_Ad_Table.Ad_Height, W_Ad_Table.DayMoney, W_Ad_Table.Ad_Width 
        FROM W_Ad_MemberInfo INNER JOIN W_Ad_Table ON W_Ad_MemberInfo.T_ID= W_Ad_Table.T_ID
        WHERE W_Ad_MemberInfo.[User_ID]=@userid
  IF @@error=0 
	 begin
	      commit tran
	  end
  else
	 begin
		 rollback tran
	 end
END
' 
END
---56.0----[SP_W_Ad_MemberInfo_RestrictTime]------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_Ad_MemberInfo_RestrictTime]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_Ad_MemberInfo_RestrictTime]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_Ad_MemberInfo_RestrictTime]
@Ad_StartDate DATETIME,
@T_ID int, 
@M_ID int
AS
BEGIN
    begin tran 
    UPDATE [W_Ad_MemberInfo] SET  Ad_RestrictTime=''发布完毕'' WHERE T_ID=@T_ID AND Ad_RestrictTime=''正在发布''
    UPDATE [W_Ad_MemberInfo] SET [Ad_RestrictTime]=''正在发布'' WHERE [M_ID]=@M_ID
    UPDATE [W_Ad_Table] SET [Ad_StartDate]=@Ad_StartDate WHERE [T_ID]=@T_ID
   if @@error=0
    begin
      commit tran
    end
   else
   begin
     rollback tran
   end
END
' 
END
----57.0---[SP_W_Ad_MemberInfo_Update]-----------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_Ad_MemberInfo_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_Ad_MemberInfo_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_Ad_MemberInfo_Update]
@Ad_Adrees NVARCHAR(200),
@Ad_Leter NVARCHAR(50),
@Ad_ImageAdrees NVARCHAR(200), 
@Ad_Day INT,
@upid INT,
@MemberInfoName NVARCHAR(50),
@Ad_StartDate DATETIME
AS
BEGIN TRAN
   BEGIN
     UPDATE W_Ad_MemberInfo SET [Ad_Adrees]=@Ad_Adrees,[Ad_Leter]=@Ad_Leter,[Ad_ImageAdrees]=@Ad_ImageAdrees,[Ad_Day]=@Ad_Day,[Ad_MemberInfoName]=@MemberInfoName,[Ad_StartDate]=@Ad_StartDate WHERE [M_ID]=@upid
    END
IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END
---58.0---[SP_W_Ad_MemberInfo_Insert]-------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_Ad_MemberInfo_Insert]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_Ad_MemberInfo_Insert]')
end
begin
EXEC sp_executesql @statement = N'
--用户添加广告信息
CREATE PROCEDURE [SP_W_Ad_MemberInfo_Insert]
@User_ID INT,
@T_ID INT,
@Ad_Adrees NVARCHAR(200),
@Ad_Leter NVARCHAR(50),
@Ad_ImageAdrees NVARCHAR(200),
@Ad_Day INT,
@Ad_StartDate DATETIME,
@UM_Name nchar(20),
@UM_Sign nvarchar(5),
@MemberInfoName  NVARCHAR(50)
AS
BEGIN TRAN
 BEGIN
 insert into [W_Ad_MemberInfo] ([User_ID],[T_ID],Ad_Adrees,Ad_Leter,Ad_ImageAdrees,Ad_Day,[Ad_StartDate],[UM_Name],[UM_Sign],[Ad_MemberInfoName])
                         values(@User_ID,@T_ID,@Ad_Adrees,@Ad_Leter,@Ad_ImageAdrees,@Ad_Day,@Ad_StartDate,@UM_Name,@UM_Sign,@MemberInfoName)

 END
IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END
---59.0---[SP_W_AgentType_Update]-----------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_AgentType_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_AgentType_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_AgentType_Update]
@ID int,
@Name nvarchar(40),
@Compositor int
AS
BEGIN TRAN
		UPDATE W_AgentType SET [Name] = @Name,[Compositor]=@Compositor WHERE [ID] = @ID

IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN' 
END
---60.0----[SP_W_AgentType_Add]------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_AgentType_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_AgentType_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_AgentType_Add]
@Name nvarchar(40), 
@ParentID int
AS
declare @aa int
DECLARE @FullID nvarchar(20)
IF @ParentID=0
	BEGIN	
		SET @FullID=@ParentID
	END
ELSE
	BEGIN
	   SELECT @FullID=FullID FROM W_AgentType WHERE ID=@ParentID 
	   IF  @FullID=''0''
		   SET @FullID=@ParentID
	   ELSE
		   SET @FullID=@FullID+'',''+cast(@ParentID  as varchar)
	END
	BEGIN TRAN
		SELECT @aa=max(Compositor) FROM W_AgentType WHERE  ParentID=@ParentID
		IF @aa is null
			SET @aa=0
		ElSE IF @aa=0
			set @aa=1
		ELSE
			set @aa=@aa+1 
		IF EXISTS(SELECT * FROM W_AgentType WHERE [Name] = @Name)
			BEGIN 
				RAISERROR(''该类别已存在'', 16, 1)
			END
		 ELSE
			BEGIN 
                update W_AgentType set UnderlingFlag=1 WHERE ID = @ParentID; 
			    INSERT INTO W_AgentType([Name],[ParentID],[FullID],[Compositor])VALUES(
				@Name,@ParentID,@FullID,@aa)
	
		    END 
IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN' 
END
----61.0----[SP_C_Supply_UpdateVerify]----------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_C_Supply_UpdateVerify]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_C_Supply_UpdateVerify]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_C_Supply_UpdateVerify]
@ID INT,
@Verify INT
AS
BEGIN TRAN
SELECT  @Verify=Verify FROM C_Supply WHERE ID=@ID
IF(@Verify=''1'')
	UPDATE C_Supply SET Verify=''3'' WHERE ID=@ID
ELSE
	UPDATE C_Supply SET Verify=''1'' WHERE ID=@ID

IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN
        RETURN -2
END 
COMMIT TRAN
' 
END 
----62.0--[SP_C_Supply_Update]-------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_C_Supply_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_C_Supply_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_C_Supply_Update]
@ID INT ,
@IndType varchar(50),
@Name NVARCHAR(20) ,
@Key NVARCHAR(50) ,
@Info NVARCHAR(4000) ,
@Picture_1 NVARCHAR(50)='''' ,
@Picture_2 NVARCHAR(50)='''' ,
@Picture_3 NVARCHAR(50)='''' ,
@ProType INT ,
@Place NVARCHAR(50) ,
@Brand NVARCHAR(50) ,
@Model NVARCHAR(50) ,
@Price INT ,
@Quantity INT ,
@Units NVARCHAR(5) ,
@Minimum INT ,
@Deadline INT ,
@StartDate datetime ='''',
    @EndDate datetime ='''',
@Remark NVARCHAR(50) ,
@Document NVARCHAR(50)
AS
BEGIN TRAN 
DECLARE @imgFile nvarchar(50),@isVerify int
set @imgFile=Replace(@Picture_1,''/t_'',''/'')
SELECT @isVerify=isEdit FROM  W_TradeSettings WHERE TradeType=''供应''
--根据商机信息设置表判断修改商机信息是否需要审核
IF @isVerify=1
   SET @isVerify = 3
ELSE
   SET @isVerify = 1
UPDATE C_Supply SET [IndType] = @IndType, [Name] = @Name, [Key] = @Key, Info = @Info,Picture_1 = @imgFile, Picture_2 = @Picture_2
  , Picture_3 = @Picture_3, ProType = @ProType, Place = @Place, Brand = @Brand, Model = @Model, Price = @Price, Quantity = @Quantity, Units = @Units
  , Minimum = @Minimum, Deadline = @Deadline, StartDate= @StartDate,EndDate=@EndDate, Remark = @Remark, [Document] = @Document,Verify=@isVerify
WHERE ID = @ID
	
 IF @@ERROR<>0	
 BEGIN 
   ROLLBACK TRAN 
   RETURN -2
 END 

COMMIT TRAN	
' 
END
--63.0---[SP_C_Supply_UpdateRecommend]----------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_C_Supply_UpdateRecommend]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_C_Supply_UpdateRecommend]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_C_Supply_UpdateRecommend] 
@ID INT,
@Recommend BIT=''''
AS
BEGIN TRAN
SELECT  @Recommend=Recommend FROM C_Supply WHERE ID=@ID
IF(@Recommend=1)
	UPDATE C_Supply SET Recommend=0 WHERE ID=@ID
ELSE
	UPDATE C_Supply SET Recommend=1 WHERE ID=@ID

IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN
        RETURN -2
END 
COMMIT TRAN
' 
END
---64.0---[SP_P_SelectDatetoIndex]--------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_P_SelectDatetoIndex]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_P_SelectDatetoIndex]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_P_SelectDatetoIndex] 
AS
BEGIN
declare @m1 int,@m2 int,@m3 int,@m4 int 
CREATE TABLE #t(t1 INT,t2 int,t3 int ,t4 int )
select @m1=count(1) from c_Supply
select @m2=count(1) from c_Supply where  Verify=1
select @m3=count(1) from c_Supply where  Verify=3
select @m4=count(1) from c_Supply where  Recommend=1
insert into #t values(@m1,@m2,@m3,@m4)

select @m1=count(1) from C_Purchase
select @m2=count(1) from C_Purchase where  Verify=1
select @m3=count(1) from C_Purchase where  Verify=3
select @m4=count(1) from C_Purchase where  Recommend=1
insert into #t values(@m1,@m2,@m3,@m4)

select @m1=count(1) from U_UserInfo
select @m2=count(1) from U_UserInfo where  Verify=1
select @m3=count(1) from U_UserInfo where  Verify=3
insert into #t values(@m1,@m2,@m3,@m4)
select * from #t
END
' 
END
---65.0---[SP_W_Area_Add]----------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_Area_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_Area_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_Area_Add]
	@Name nvarchar(50),
	@ParentID int,
    @Sequence int=''''

AS 
DECLARE @FullID nvarchar(20),@Grade int
IF @ParentID=0
	BEGIN	
		SET @Grade=1
		SET @FullID=@ParentID
	END
ELSE
BEGIN
   SELECT @Grade=Grade,@FullID=FullID FROM W_Area WHERE ID=@ParentID 
   SET @Grade=@Grade+1
   IF  @FullID=''0''
	   SET @FullID=@ParentID
   ELSE
	   SET @FullID=@FullID+'',''+cast(@ParentID  as varchar)
END
BEGIN TRAN
	IF EXISTS(SELECT * FROM W_Area WHERE [Name] = @Name)
		BEGIN 
			RAISERROR(''该地区已存在'', 16, 1)
		END
	 ELSE
		BEGIN 
            update W_Area set UnderlingFlag=1 WHERE ID = @ParentID; 
		    insert into W_Area([Name],[ParentID],[FullID],[Grade],[Sequence])
		    values(@Name,@ParentID,@FullID,@Grade,@Sequence);
		
	    END 

IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END
---66.0----[SP_W_Area_Update]-----------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_Area_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_Area_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_Area_Update]
@ID int,
@Name nvarchar(50)
AS 
BEGIN TRAN
	BEGIN 
        update W_Area set [Name]=@Name WHERE ID = @ID
	END 
IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END
---67.0---[SP_U_SubscriptionEmail_Update]------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_SubscriptionEmail_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_SubscriptionEmail_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_SubscriptionEmail_Update]
@UserID INT,
@Email nvarchar(100)
as
begin tran
BEGIN
	update  U_SubscriptionEmail set Email=@Email where UserID=@UserID
END
IF @@error<>0
    begin
       rollback tran
	   return -2 
    end
commit tran
' 
END
---68.0-----[SP_U_SubscriptionNews_Add]-----------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_SubscriptionNews_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_SubscriptionNews_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_SubscriptionNews_Add]
@UserID INT,
@Type nvarchar(50)
AS
BEGIN TRAN
if exists(select * from U_SubscriptionNews where Type=@Type and UserID=@UserID)
	BEGIN
	   raiserror(''对不起，您已经订阅了此商业资讯信息'',16,1)
	END
else
	begin
		declare @Email nvarchar(100)
		select @Email=Email from U_UserInfo where UserID=@UserID
		insert into U_SubscriptionNews(UserID,Type)values(@UserID,@Type)
		if not exists(select * from U_SubscriptionEmail where UserID=@UserID)
		BEGIN
			insert into U_SubscriptionEmail(UserID,Email)values(@UserID,@Email)
		END
	end
if @@error<>0
	begin
		rollback tran
		return -2
	end
COMMIT TRAN 
' 
END
---69.0---[SP_U_SubscriptionProduct_Add]---------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_SubscriptionProduct_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_SubscriptionProduct_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_SubscriptionProduct_Add]
@UserID INT,
@Type nvarchar(50),
@Keyword nvarchar(50),
@IndType nvarchar(50),
@Area nvarchar(50),
@validity int
as
begin tran
IF EXISTS(SELECT * FROM U_SubscriptionProduct WHERE  [Keyword] = @Keyword and UserID=@UserID and Type=@Type)
	BEGIN 
		RAISERROR(''订阅商机关键字已存在'', 16, 1)
	END	
else
    BEGIN 
		declare @Email nvarchar(100)
		select @Email=Email from U_UserInfo where UserID=@UserID
		INSERT INTO U_SubscriptionProduct([UserID],[Type],[Keyword],[IndType],[Area],[validity])VALUES(@UserID,@Type,@Keyword,@IndType,@Area,@validity)
		if not exists(select * from U_SubscriptionEmail where UserID=@UserID)
		BEGIN
			insert into U_SubscriptionEmail(UserID,Email)values(@UserID,@Email)
		END
	END
IF @@error<>0
    begin
       rollback tran
	   return -2 
    end
commit tran
' 
END
---70.0----[SP_U_EmailListNews]---------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_EmailListNews]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_EmailListNews]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_EmailListNews]
@UserID int
AS
begin transaction
	CREATE TABLE #t(UserID int,strTypeID nvarchar(200))	
	DECLARE @tempID nvarchar(100),@aa nvarchar(100),@bb nvarchar(100)
	DECLARE rs CURSOR LOCAL SCROLL FOR select Type from U_SubscriptionNews  where UserID = @UserID 
	OPEN rs 
	FETCH NEXT FROM rs INTO @tempID
	WHILE @@FETCH_STATUS = 0 
	BEGIN
		SET @aa=@tempID+'',''
		set @bb=@bb+@aa
        FETCH NEXT FROM rs INTO @tempID
	END
	insert into #t values(@UserID,@bb)    
	CLOSE  rs
    
	select * from #t
IF @@error <> 0
begin
rollback tran
return -2
END
COMMIT TRAN 
' 
END
---71.0---[SP_U_SubscriptionNews_Update]---------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_SubscriptionNews_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_SubscriptionNews_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_SubscriptionNews_Update]
@ID INT,
@Type nvarchar(50)
AS
BEGIN TRAN
begin
    UPDATE  U_SubscriptionNews SET Type=@Type where ID=@ID
end
if @@error<>0
	begin
		rollback tran
		return -2
	end
COMMIT TRAN 
' 
END
---72.0--[SP_W_MetaInfo_Update]-------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_MetaInfo_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_MetaInfo_Update]')
end

begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_MetaInfo_Update]
@ID INT,
@Keywords NVARCHAR(500),
@Descriptions NVARCHAR(500)
AS 
BEGIN TRAN
BEGIN
             UPDATE W_MetaInfo SET [Keywords]=@Keywords,[Descriptions]=@Descriptions
	WHERE ID=@ID

END
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN	
' 
END
---73.0----[SP_W_MetaType_Add]------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_MetaType_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_MetaType_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE  [SP_W_MetaType_Add]
@Type NVARCHAR(20)
AS 
BEGIN TRAN
IF EXISTS(SELECT * FROM W_MetaType WHERE [Type] = @Type)
	BEGIN
		RAISERROR(''此类别已存在'', 16, 1)
	END
ELSE
	BEGIN
        INSERT INTO W_MetaType([Type]) VALUES (@Type)            
        INSERT INTO W_MetaInfo([TypeID],[Keywords],[Descriptions]) VALUES (@@identity,'''','''')

	END
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN	
' 
END
---74.0----[SP_W_MetaInfo_Add]----------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_MetaInfo_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_MetaInfo_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_MetaInfo_Add]
@TypeID INT,
@Keywords NVARCHAR(500),
@Descriptions NVARCHAR(500)
AS 
BEGIN TRAN
	IF EXISTS(SELECT * FROM W_MetaInfo WHERE [TypeID] = @TypeID)
		BEGIN
			RAISERROR(''此Meta信息已存在'', 16, 1)
		END
	ELSE
		BEGIN
            INSERT INTO W_MetaInfo([TypeID],[Keywords],[Descriptions]) VALUES (@TypeID,@Keywords,@Descriptions)
			
		END

IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN	
' 
END
---75.0---[SP_W_ExpoNews_Update]----------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_ExpoNews_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_ExpoNews_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_ExpoNews_Update]
@ID int,
@TypeID int,
@Title nvarchar(100),
@Attribute nvarchar(100),
@Origin nvarchar(100),
@Picture nvarchar(100),
@Detail nvarchar(100),
@Verify nvarchar(3)
AS 
BEGIN TRAN
UPDATE W_ExpoNews SET 
[TypeID] = @TypeID,[Title] = @Title,[Attribute] = @Attribute,[Origin] = @Origin,[Picture]=@Picture,[Detail] = @Detail,[Verify]=@Verify
WHERE [ID] = @ID

IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN	
' 
END
--76.0----[SP_W_Role_Add]--------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_Role_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_Role_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_Role_Add]
@RoleName varchar(20) ,
@Basic varchar(100) ,
@Column varchar(100) ,
@User varchar(100) ,
@Template varchar(100) ,
@System varchar(100),
@Describe nvarchar(500)
AS 
BEGIN TRAN  
IF EXISTS(SELECT * FROM W_Role WHERE  RoleName = @RoleName )
	BEGIN 
		RAISERROR(''网站角色已存在'', 16, 1)
	END
 ELSE
	BEGIN 
		INSERT INTO W_Role(
		[RoleName],[Basic],[Column],[User],[Template],[System],[Describe],[AddDate]
		)VALUES(
		@RoleName,@Basic,@Column,@User,@Template,@System,@Describe,getdate()
		)
	END
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN' 
END
---77.0----[SP_W_Role_Update]--------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_Role_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_Role_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_Role_Update]
@ID int, 
@RoleName varchar(20) ,
@Basic varchar(100) ,
@Column varchar(100) ,
@User varchar(100) ,
@Template varchar(100) ,
@System varchar(100),
@Describe nvarchar(500)
AS 
BEGIN TRAN 
BEGIN 
UPDATE W_Role SET 
[RoleName] = @RoleName,
[Basic] = @Basic,
[Column] = @Column,
[User] = @User,
[Template] = @Template,
[Describe] = @Describe,
[System] = @System
WHERE [ID] = @ID
END
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN' 
END
---78.0---[SP_W_EmailStatistics_Update]--------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_EmailStatistics_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_EmailStatistics_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_EmailStatistics_Update]
@Id INT,
@UserId INT ,
@State NVARCHAR(50) ,
@SubscriptionType NVARCHAR(50) ,
@AddDate DATETIME ,
@Extend NVARCHAR(50)
AS 
BEGIN TRAN  
	     
   UPDATE W_EmailStatistics SET 
[UserID] = @UserID, [State] = @State, [SubscriptionType] = @SubscriptionType, [AddDate] = @AddDate, 
[Extend] = @Extend
WHERE [ID] = @Id

IF @@error <> 0
begin
   rollback tran
   return -2
END

COMMIT TRAN ' 
END
---79.0---[SP_W_EmailStatistics_Add]-----------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_EmailStatistics_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_EmailStatistics_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_EmailStatistics_Add]
@UserId INT ,
@State NVARCHAR(50) ,
@SubscriptionType NVARCHAR(50) ,
@AddDate DATETIME ,
@Extend NVARCHAR(50)
AS
BEGIN TRAN  
BEGIN
    INSERT INTO W_EmailStatistics([UserID],[State],[SubscriptionType],[AddDate],[Extend])
    VALUES(@UserID,@State,@SubscriptionType,@AddDate,@Extend)
END				
IF @@error <> 0
begin
	rollback TRAN
	return -2
end
COMMIT TRAN 
' 
END
---80.0----[SP_W_Consultative_Add]----------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_Consultative_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_Consultative_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_Consultative_Add]
	@Title nvarchar(100) ,
	@Color varchar(50)='''',
	@Font varchar(50)='''',
	@TypeID int ,
	@Picture nvarchar(100)='''' ,
	@KeyWord nvarchar(40)='''' ,
	@Author nvarchar(40)='''' ,
	@Origin nvarchar(100)='''' ,
	@Reading nvarchar(2000)='''' ,
	@ChangeDirectionLink nvarchar(100)='''' ,
	@AddDate datetime='''' ,
	@ClickCount varchar(10)='''' ,
	@Detail ntext ,
    @CountUp int,
    @CountDown int,
    @IsTop bit,
    @IsRecommend bit,
    @IsComment bit,
    @IsHotspot bit
AS
BEGIN TRAN  
        IF EXISTS(SELECT * FROM W_Consultative WHERE Title = @Title)
		   BEGIN
			 RAISERROR(''该资讯已存在'', 16, 1)
		   END
		ELSE
           BEGIN
		     INSERT INTO W_Consultative([Title],[Color],[Font],[TypeID],[Picture],[KeyWord],[Author],[Origin],[Reading],[ChangeDirectionLink],[AddDate],[ClickCount],[Detail],[CountUp],[CountDown],[IsComment],[IsTop],[IsHotspot],[IsRecommend])
			 VALUES(@Title,@Color,@Font,@TypeID,@Picture,@KeyWord,@Author,@Origin,@Reading,@ChangeDirectionLink,@AddDate,@ClickCount,@Detail,@CountUp,@CountDown,@IsComment,@IsTop,@IsHotspot,@IsRecommend)
            END
        
IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END
--81.0---[SP_W_Consultative_Update]----------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_Consultative_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_Consultative_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_Consultative_Update]
@ID INT ,
@Title nvarchar(100) ,
@Color varchar(50)='''',
@Font varchar(50)='''',
@TypeID int ,
@Picture nvarchar(100)='''',
@KeyWord nvarchar(40)='''' ,
@Author nvarchar(40)='''' ,
@Origin nvarchar(100)='''' ,
@Reading nvarchar(2000)='''' ,
@ChangeDirectionLink nvarchar(100)='''',
@AddDate datetime='''',
@ClickCount varchar(10)='''',
@Detail ntext,
@IsTop bit,
@IsRecommend bit,
@IsComment bit,
@IsHotspot bit
AS
BEGIN TRAN 
UPDATE W_Consultative SET 
    [Title] = @Title,[Color]=@Color,[Font]=@Font,[TypeID] = @TypeID,[Picture] = @Picture,[KeyWord] = @KeyWord,[Author] = @Author,[Origin] = @Origin,[Reading] = @Reading,[ChangeDirectionLink] = @ChangeDirectionLink,[AddDate] = @AddDate,[ClickCount] = @ClickCount,[Detail] = @Detail,[IsTop] = @IsTop,[IsRecommend]= @IsRecommend,[IsComment]=@IsComment,[IsHotspot]=@IsHotspot
 WHERE [ID] = @ID
	
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN
' 
END
---82.0-----[SP_W_ConsultativeType_Delete]--------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_ConsultativeType_Delete]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_ConsultativeType_Delete]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_ConsultativeType_Delete]
@SelfId int,     --自身ID
@Toid int='''', --转向的哪个类别ID
@Type int,   --判断是否删除,1、转移，0、删除
@ParentId int=''''  --判断是否有子类别,0、父级，其它值为子类
AS
if @Type=1 and @ParentId=0
BEGIN
    BEGIN TRAN 
    UPDATE [W_ConsultativeType] set [ParentId]=@Toid where ID = @SelfId
    UPDATE [W_Consultative] SET [TypeID]=@Toid WHERE ID IN (SELECT [ID] FROM [W_Consultative] WHERE [TypeID]=@SelfId)
    UPDATE W_ConsultativeType set parentID=@Toid where id in  (select ID from W_ConsultativeType where parentID=@SelfId)
    COMMIT TRAN
END

ELSE if @Type=1 and @ParentId<>0
BEGIN
  BEGIN TRAN
    UPDATE [W_ConsultativeType] SET [ParentID]=@Toid where ID=@SelfId
	--UPDATE [W_ConsultativeType] SET [ParentID]=@ParentId WHERE ID in (SELECT [ID] FROM [W_ConsultativeType] WHERE [ParentID]=@SelfId)
    --UPDATE [W_Consultative] SET [TypeID]=@Toid WHERE ID IN (SELECT [ID] FROM [W_Consultative] WHERE [TypeID] in (SELECT [ID] FROM [W_ConsultativeType] WHERE [ParentID]=@SelfId))
    --UPDATE [W_ConsultativeType] SET [ParentID]=@ParentId where ID=@SelfId
    --UPDATE [W_Consultative] SET [TypeID]=@Toid WHERE ID IN (SELECT [ID] FROM [W_Consultative] WHERE [TypeID]=@SelfId)
    COMMIT TRAN
END
ELSE IF @Type=0
BEGIN
    BEGIN TRAN 
    DELETE FROM [W_Consultative]  WHERE ID IN (SELECT [ID] FROM [W_Consultative] WHERE [TypeID]=@SelfId)
	DELETE FROM [W_ConsultativeType] WHERE ID=@SelfID
	COMMIT TRAN
END
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
' 
END
----83.0----[SP_W_WebNews_Update]----------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_WebNews_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_WebNews_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_WebNews_Update]
@ID int,
@Title nvarchar(40),
@Type varchar(20),
@Attribute nvarchar(100)='''',
@Picture varchar(50)='''',
@Origin nvarchar(100),
@Detail ntext=''''
AS 
BEGIN TRAN
	UPDATE W_WebNews SET 
	[Title] = @Title,[Type] = @Type,[Attribute] = @Attribute,[Picture] = @Picture,[Origin] = @Origin,[Detail] = @Detail
	WHERE [ID] = @ID
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN
' 
END
---84.0---[SP_W_WebNews_Add]--------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_WebNews_Add]') AND type in (N'P', N'PC'))
BEGIN
   exec('DROP PROC [SP_W_WebNews_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_WebNews_Add] 
@Title nvarchar(40),
@Type varchar(20),
@Attribute nvarchar(100)='''' ,
@Picture varchar(50)='''' ,
@Origin nvarchar(100) ,
@Detail ntext='''' 
AS 
BEGIN TRAN
IF EXISTS(SELECT * FROM W_WebNews WHERE [Title] = @Title )
	BEGIN
		RAISERROR(''此标题已存在'', 16, 1)
	END
ELSE
	BEGIN
		INSERT INTO W_WebNews(
		[Title],[Type],[Attribute],[Picture],[Origin],[Detail]
		)VALUES(
		@Title,@Type,@Attribute,@Picture,@Origin,@Detail
		)
	END

IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN
' 
END
----85.0-----[SP_U_Favorites_Add]---------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_Favorites_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_Favorites_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_Favorites_Add]
@ProductID int,
@UserID int,
@Type nvarchar(20)
AS 
BEGIN TRAN  
IF EXISTS(SELECT * FROM U_Favorites WHERE ProductID=@ProductID AND UserID=@USerID AND [Type] = @Type )
	BEGIN 
		RAISERROR(''您想收藏的信息已存在!'', 16, 1)
	END
 ELSE
	BEGIN 
		INSERT INTO U_Favorites([ProductID],[UserID],[Type])VALUES(@ProductID,@UserID,@Type)
	END
IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END
---86.0-----[SP_W_Manager_UpdateIP]----------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_Manager_UpdateIP]') AND type in (N'P', N'PC'))
BEGIN
   exec('DROP PROC [SP_W_Manager_UpdateIP]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_Manager_UpdateIP]
@ID INT=1,
@LastIP VARCHAR(50),
@LastDate datetime,
@Flag INT,
@ManagerName NVARCHAR(20)
AS
DECLARE @SuccessCount INT
DECLARE @DefeatCount INT
DECLARE @MID INT
BEGIN TRAN 
BEGIN
IF EXISTS(select * from W_ManagerCount where IP=@LastIP and ManagerName=@ManagerName)
 BEGIN  
	select @MID=ID from W_ManagerCount where IP=@LastIP and ManagerName=@ManagerName
	select @SuccessCount=SuccessCount from W_ManagerCount where IP=@LastIP and ManagerName=@ManagerName
	select @DefeatCount=DefeatCount from W_ManagerCount where IP=@LastIP and ManagerName=@ManagerName
	IF @Flag=1
	   BEGIN  	
		 SET @SuccessCount=@SuccessCount+1
		 UPDATE W_Manager SET [LastIP] = @LastIP,[LastDate] = @LastDate WHERE [ID] = @ID
	   END
	ELSE
	   BEGIN 
		  SET @DefeatCount=@DefeatCount+1
	   END
	UPDATE W_ManagerCount SET [LastDate] = @LastDate,[SuccessCount]=@SuccessCount,[DefeatCount]=@DefeatCount WHERE [ID] = @MID
 END		
ELSE
 BEGIN	    
 	 IF @Flag=1
	   BEGIN  	
		  INSERT INTO W_ManagerCount([IP],[StartDate],[LastDate],[SuccessCount],[DefeatCount],[ManagerName]) values(@LastIP,getdate(),@LastDate,1,0,@ManagerName)
	   END
	 ELSE
	   BEGIN 
		  INSERT INTO W_ManagerCount([IP],[StartDate],[LastDate],[SuccessCount],[DefeatCount],[ManagerName]) values(@LastIP,getdate(),@LastDate,0,1,@ManagerName)
	   END 
  END
END
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN	
' 
END
---87.0-----[SP_U_FastAddUserInfo]-------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_FastAddUserInfo]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_FastAddUserInfo]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_FastAddUserInfo] 
@LoginName VARCHAR(20),
@Password VARCHAR(50),
@CompanyName VARCHAR(50),
@Email VARCHAR(30),
@UserID int OUTPUT

AS
IF EXISTS(SELECT * FROM U_UserInfo WHERE LoginName = @LoginName)
BEGIN
RAISERROR(''会员名已存在'', 16, 1)
END
ELSE
BEGIN
DECLARE @VipGrade int
DECLARE @ShopTemplate nvarchar(20)
SELECT @VipGrade = VipGrade FROM  U_Register
INSERT INTO [U_UserInfo] (LoginName,Sex,[Password],Question,Answer,GradeID,Lockedup,LinkName,Email,CompanyName,Verify)
 VALUES (@LoginName,''男'',@Password,'' '','' '',@VipGrade,0,'' '',@Email,@CompanyName,''1'')
SELECT @UserID=@@IDENTITY
SELECT @ShopTemplate=ShopTemplate  FROM U_VipGrade WHERE ID=@VipGrade
INSERT INTO U_UserOperate ([UserID],[Template_New]) VALUES (@UserID,@ShopTemplate)
END
' 
END
---88.0------[SP_U_Register_Update]---------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_Register_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_Register_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_Register_Update]
(  
@isRegister  bit=1 ,      --是否允许注册会员
@ManageRegister bit=1,    --是否需要管理员审核
@VipGrade int,                 --会员注册默认的等级
@isCode bit=1,            --会员注册是否需要验证码
@isQuestion bit=1,        --会员注册是否需要验证问题
@Question nvarchar(500),            --验证问题
@Answer nvarchar(500),              --答案
@BannedIP nvarchar(500),       --不允许注册的IP
@BannedUser nvarchar(500),     --不允许注册的用户名
@Code_Login bit=1,        --会员登陆是否需要验证码
@Code_GetPass bit=1,      --取回密码是否需要验证码
@GetPass_Count int,            --取回密码的次数(0:无限制)
@Minimum int=6 ,               --用户名的最小长度
@Longest int = 20,             --用户名的最长长度
@MinTimeRegister int=0,        --同一IP注册的最小间隔
@Gocipher_Method int =0,       --取回密码的方法[0: 问题取回,1:邮箱取回,2:二者选一]
@OnlyUser_Email bit=1,    --一个邮箱是否只允许注册一个用户
@ToMessage bit=1,         --注册成功是否发送短信
@MessageInfo nvarchar(1000),    --发送的短信信息
@ToEmail bit =1,          --注册成功是否发送Email
@EmailInfo nvarchar(1000)       --发送的Eamil信息
) 	
AS
BEGIN TRAN
IF not EXISTS(SELECT * FROM U_Register)
   BEGIN        
    INSERT INTO U_Register ( [isRegister] ,[ManageRegister] ,[VipGrade] ,[isCode] ,[isQuestion] ,[Question] ,[Answer] ,[BannedIP] ,[BannedUser] ,[Code_Login] ,[Code_GetPass] ,[GetPass_Count] ,[Minimum] ,[Longest] ,[MinTimeRegister] ,[Gocipher_Method] ,[OnlyUser_Email] ,[ToMessage] ,[MessageInfo] ,[ToEmail] ,[EmailInfo] ) 
    VALUES (@isRegister,@ManageRegister,@VipGrade,@isCode,@isQuestion,@Question,@Answer,@BannedIP,@BannedUser,@Code_Login,@Code_GetPass,@GetPass_Count,@Minimum,@Longest,@MinTimeRegister,@Gocipher_Method,@OnlyUser_Email,@ToMessage,@MessageInfo,@ToEmail,@EmailInfo)
   END
ELSE
	update U_Register set 
	[isRegister] = @isRegister,
	[ManageRegister] = @ManageRegister,
	[VipGrade] = @VipGrade,
	[isCode] = @isCode,
	[isQuestion] = @isQuestion,
	[Question] = @Question,
	[Answer] = @Answer,
	[BannedIP] = @BannedIP,
	[BannedUser] = @BannedUser,
	[Code_Login] = @Code_Login,
	[Code_GetPass] = @Code_GetPass,
	[GetPass_Count] = @GetPass_Count,
	[Minimum] = @Minimum,
	[Longest] = @Longest,
	[MinTimeRegister] = @MinTimeRegister,
	[Gocipher_Method ] = @Gocipher_Method ,
	[OnlyUser_Email] = @OnlyUser_Email,
	[ToMessage] = @ToMessage,
	[MessageInfo] = @MessageInfo,
	[ToEmail] = @ToEmail,
	[EmailInfo] = @EmailInfo

IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END
----89.0-----[SP_U_UserInfo_Add]-------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_UserInfo_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_UserInfo_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_UserInfo_Add]
(
	@LoginName NVARCHAR(20),
	@Sex NVARCHAR(20),
	@Password VARCHAR(50),
	@Question NVARCHAR(50),
	@Answer NVARCHAR(50),
	@GradeID INT,
	@Lockuedup BIT,
	@LinkName NVARCHAR(10),
	@MobilePhone VARCHAR(15),
	@Email VARCHAR(30),
	@QQ VARCHAR(15),
	@MSN VARCHAR(50),
	@Tel_International VARCHAR(5),
	@Tel_DistrictNumber VARCHAR(5),
	@Tel_Telephone VARCHAR(10),
	@Tel_Ext VARCHAR(10),
	@Fax_International VARCHAR(5),
	@Fax_DistrictNumber VARCHAR(5),
	@Fax_Telephone VARCHAR(10),
	@Fax_Ext VARCHAR(10),
	@Department NVARCHAR(20),
	@Position NVARCHAR(10),
	@CompanyName NVARCHAR(50),
	@CompanyNature NVARCHAR(20)='''',
	@BusinessModel NVARCHAR(20)='''',
	@DealinAdd NVARCHAR(50),
	@Product NVARCHAR(50) = '''',
	@Industry NVARCHAR(50),
	@Summary NVARCHAR(4000)='''',
	@Verify BIT =0,
	@Country NVARCHAR(20),
	@Province NVARCHAR(10),
	@City NVARCHAR(10),
	@Area NVARCHAR(20),
	@Street NVARCHAR(50)='''',
	@Postalcode VARCHAR(10)='''',
	@URL NVARCHAR(50)='''',
	@Capital INT =0,
	@RegisterAddress NVARCHAR(50)='''',
	@Corporate NVARCHAR(10)='''',
	--@StartDate DATETIME = '''',
	@EndDate DATETIME = '''',
	@Logo NVARCHAR(100) =''''
) 	
AS
 if (@EndDate='''')
  select @EndDate=dateadd(mm,1,getdate())

BEGIN TRAN
Declare @Mark int,@ShopTemplate nvarchar(50),@ManageRegister bit,@VipGrade int,@isVerify bit
SELECT @VipGrade = VipGrade,@isVerify =ManageRegister FROM  U_Register
SELECT @Mark=Mark,@ShopTemplate=ShopTemplate  FROM U_VipGrade WHERE ID=@GradeID
IF EXISTS(SELECT * FROM U_UserInfo WHERE LoginName = @LoginName or CompanyName=@CompanyName)
BEGIN
	RAISERROR(''会员公司信息已存在'', 16, 1)
END
ELSE
BEGIN
	--根据注册信息设置表判断会员注册是否需要审核
    IF @isVerify=''1''
      SET @Verify = 0
	ELSE
      SET @Verify = 1
	INSERT INTO U_UserInfo(LoginName, Sex, Password, Question, Answer, GradeID, Lockedup, LinkName, MobilePhone, Email, QQ, MSN, Tel_International,
		Tel_DistrictNumber, Tel_Telephone, Tel_Ext, Fax_International,Fax_DistrictNumber, Fax_Telephone, Fax_Ext, Department, Position, CompanyName, CompanyNature, BusinessModel, DealinAdd, Product,
		Industry, Summary, Verify, Country, Province, City, Area, Street, Postalcode, URL, Capital, RegisterAddress, Corporate,EndDate,Logo)
	VALUES (@LoginName, @Sex, @Password, @Question, @Answer, @VipGrade, @Lockuedup, @LinkName, @MobilePhone, @Email, @QQ, @MSN, @Tel_International, @Tel_DistrictNumber, 
		@Tel_Telephone, @Tel_Ext, @Fax_International,@Fax_DistrictNumber,@Fax_Telephone,@Fax_Ext, @Department, @Position, @CompanyName, @CompanyNature, @BusinessModel, @DealinAdd, @Product, @Industry, 
		@Summary, @Verify, @Country, @Province, @City, @Area, @Street, @Postalcode, @URL, @Capital,  @RegisterAddress, @Corporate,@EndDate, @Logo)
	--修改会员统计信息表
       declare @UserID int
       select @UserID=UserID from U_UserInfo where LoginName=@LoginName
       insert into U_UserOperate(UserID,Mark,Template_Old,Template_New)values(@UserID,@Mark,@ShopTemplate,@ShopTemplate )
       insert into U_SubscriptionEmail(UserID,Email,AddDate)Values(@UserID,@Email,@EndDate)
END
IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END

---91.0-----[SP_U_UserInfo_UpdateVerify]---------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_UserInfo_UpdateVerify]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_UserInfo_UpdateVerify]')
end
begin
EXEC sp_executesql @statement = N'

CREATE PROCEDURE [SP_U_UserInfo_UpdateVerify]
@UserID INT,
@Verify BIT
AS
BEGIN TRAN
IF(@Verify=1)
	UPDATE U_UserInfo SET Verify=0 WHERE UserID=@UserID
ELSE
	UPDATE U_UserInfo SET Verify=1 WHERE UserID=@UserID
IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END
----92.0---[SP_U_UserInfo_UpdatePassword]--------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_UserInfo_UpdatePassword]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_UserInfo_UpdatePassword]')
end
begin
EXEC sp_executesql @statement = N'
CREATE  PROCEDURE [SP_U_UserInfo_UpdatePassword]
(
   @UserID  INT ,
   @OldPassword VARCHAR(20),
   @Password VARCHAR(50)
) 	
AS
BEGIN TRAN sp  
DECLARE @Pass VARCHAR(20)
SELECT @Pass = Password FROM U_UserInfo WHERE UserID = @UserID
IF @Pass = @OldPassword
	BEGIN 
		UPDATE U_UserInfo SET Password = @Password WHERE UserID = @UserID
	END
ELSE
	BEGIN
		RAISERROR(''原密码错误'', 16, 1)
	END
IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END
---93.0----[SP_U_UserInfo_LinkMan_Update]--------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_UserInfo_LinkMan_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_UserInfo_LinkMan_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_UserInfo_LinkMan_Update]
(
   @UserID  int ,
   @linkName Varchar(20),
   @Sex Nchar(10),
   @Email varchar(30),
   @Tel_International varchar(10),
   @Tel_DistrictNumber varchar(10),
   @Tel_Telephone varchar(10),
   @Tel_Ext varchar(10),
   @Fax_International varchar(10),   
   @Fax_DistrictNumber varchar(10),
   @Fax_Telephone varchar(10),
   @Fax_Ext varchar(10),
   @MobilePhone varchar(15),
   @QQ varchar(10),
   @MSN varchar(10),
   @Department varchar(30),
   @Position varchar(20)
) 	
AS
BEGIN TRAN
 UPDATE U_UserInfo SET 
		linkName = @linkName,
		Sex = @Sex,
		Email = @Email,
		Tel_International = @Tel_International,
		Tel_DistrictNumber = @Tel_DistrictNumber,
		Tel_Telephone = @Tel_Telephone,
		Tel_Ext = @Tel_Ext,
		Fax_Telephone = @Fax_Telephone,
		Fax_International = @Fax_International,
		Fax_DistrictNumber = @Fax_DistrictNumber,
		Fax_Ext = @Fax_Ext,
		MobilePhone = @MobilePhone,
		QQ = @QQ,
		MSN = @MSN,
		Department = @Department,
		Position = @Position 
WHERE UserID = @UserID
IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END
---94.0---[SP_U_UserInfo_UpdateLocked]-----------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_UserInfo_UpdateLocked]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_UserInfo_UpdateLocked]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_UserInfo_UpdateLocked]
@UserID INT,
@Lockedup BIT
AS
BEGIN TRAN
	SELECT  @Lockedup=Lockedup FROM U_UserInfo WHERE UserID=@UserID
	IF(@Lockedup=1)
		UPDATE U_UserInfo SET Lockedup=0 WHERE UserID=@UserID
	ELSE
		UPDATE U_UserInfo SET Lockedup=1 WHERE UserID=@UserID
IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END
---95.0----[SP_P_GetEmailInfo]-----------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_P_GetEmailInfo]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_P_GetEmailInfo]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_P_GetEmailInfo]
@strType nvarchar(50)= '''',
@intUserID int
AS
BEGIN TRAN
    DECLARE
	@strTo NVARCHAR(50),            --收件人 
	@strTitle nvarchar(100),        --标题
	@strInfo  nvarchar(4000),       --内容
	@strIsHTML BIT,
	@strFrom NVARCHAR(50),          --发件人
	@strLoginName NVARCHAR(50),     --邮箱登录名
	@strPassWord NVARCHAR(50),      --密码
	@strsmtp nvarchar(50)           --SMTP
BEGIN 
    CREATE TABLE #t(strTo nvarchar(50),strTitle nvarchar(100),strInfo nvarchar(4000),strIsHTML bit,strFrom nvarchar(50),strLoginName nvarchar(50),strPassWord nvarchar(50),strsmtp nvarchar(50))
    SELECT @strFrom=Email,@strLoginName=EmailName,@strPassword=EmailPass,@strsmtp=SMTP FROM W_WebBasic
    SELECT @strTo=Email FROM U_UserInfo  WHERE UserID=@intUserID
    SELECT @strTitle=Title,@strInfo=cast(Content as nvarchar(4000)),@strIsHTML=isHtml FROM W_EmailTemplate WHERE Flag=@strType
   
    --返回发送信息
    insert into #t values(@strTo,@strTitle,@strInfo,@strIsHTML,@strFrom,@strLoginName,@strPassWord,@strsmtp) 
	select * from #t
END
IF @@error <> 0
begin
rollback tran
return -2
END
COMMIT TRAN 
' 
END
----96.0---[SP_U_UserInfo_UpdateLogin]-----------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_UserInfo_UpdateLogin]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_UserInfo_UpdateLogin]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE  [SP_U_UserInfo_UpdateLogin]
@UserID INT,
@GradeID INT,
@Password NVARCHAR(50),
@Question NVARCHAR(50),
@Answer NVARCHAR(50)
AS
BEGIN TRAN
	UPDATE U_UserInfo SET GradeID =@GradeID,Password=@Password,Question=@Question,Answer=@Answer where UserID=@UserID
IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END
---97.0---[SP_W_ExpoNewsType_Add]------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_ExpoNewsType_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_ExpoNewsType_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_ExpoNewsType_Add]
@Type nvarchar(50)
AS 
BEGIN TRAN  
IF EXISTS(SELECT * FROM W_ExpoNewsType WHERE  [Type] = @Type )
	BEGIN 
		RAISERROR(''此展会类别已存在'', 16, 1)
	END
 ELSE
	BEGIN
	INSERT INTO W_ExpoNewsType([Type])VALUES(@Type)
    END
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN	
' 
END
---98.0--[SP_W_ExpoNewsType_Update]-----------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_ExpoNewsType_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_ExpoNewsType_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_ExpoNewsType_Update]
@ID INT,
@Type nvarchar(50)
AS 
BEGIN TRAN  
	  UPDATE W_ExpoNewsType SET[Type]=@Type WHERE ID=@ID
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN	
' 
END
---99.0---[SP_W_ExpoType_Add]-----------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_ExpoType_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_ExpoType_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_ExpoType_Add]
@Type nvarchar(50)
AS 
BEGIN TRAN  
IF EXISTS(SELECT * FROM W_ExpoType WHERE  [Type] = @Type )
	BEGIN 
		RAISERROR(''此展会类别已存在'', 16, 1)
	END
 ELSE
	BEGIN
	    INSERT INTO W_ExpoType([Type])VALUES(@Type)
    END
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN	
' 
END
---100.0----[SP_W_ExpoType_Update]-------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_ExpoType_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_ExpoType_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_ExpoType_Update]
@ID INT,
@Type nvarchar(50)
AS 
BEGIN TRAN  
	UPDATE W_ExpoType SET[Type]=@Type WHERE ID=@ID
	
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN	
' 
END
---101.0---[SP_W_WebBasic_EmailInfo_Update]-------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_WebBasic_EmailInfo_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_WebBasic_EmailInfo_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_WebBasic_EmailInfo_Update]
@Email varchar(50)='''',
@EmailName nvarchar(20)='''',
@EmailPass varchar(20)='''',
@SMTP nvarchar(50)=''''
 AS
BEGIN TRAN
BEGIN  
	UPDATE W_WebBasic SET 
       [Email] = @Email,[EmailName] = @EmailName,[EmailPass] = @EmailPass,[SMTP] = @SMTP
END
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN
' 
END
---102.0----[SP_W_WebBasic_Filter_Update]------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_WebBasic_Filter_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_WebBasic_Filter_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_WebBasic_Filter_Update]
@Filter nvarchar(1000)
AS
BEGIN TRAN
BEGIN  
	UPDATE W_WebBasic SET 
	[Filter] = @Filter
END

IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN
' 
END
----103.0----[SP_W_WebBasic_SystemInfo_Update]--------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_WebBasic_SystemInfo_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_WebBasic_SystemInfo_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_WebBasic_SystemInfo_Update] 
@State int='''',
@StopReadme nvarchar(500)='''',
@WebName nvarchar(100)='''',
@WebURL varchar(50)='''',
@Catalog varchar(50)='''',
@isBlog bit='''',
@isSecondUrl bit='''',
@isForum bit='''',
@Copyright nvarchar(500)='''',
@Record nvarchar(50)='''',
@WebLOGO VARCHAR(100)='''',
@SystemTel VARCHAR(20)='''',
@HotTel VARCHAR(20)='''',
@FaxTel VARCHAR(20)='''',
@CorporationName NVARCHAR(20)='''',
@Receiver NVARCHAR(20)='''',
@Address NVARCHAR(50)='''',
@PostalCode NVARCHAR(20)='''',
@BankAccount VARCHAR(20)='''',
@BankName NVARCHAR(20)='''',
@Rewrite NVARCHAR(10)=''''
as
BEGIN TRAN
BEGIN 
	UPDATE W_WebBasic SET [State] = @State,[StopReadme]=@StopReadme,[WebName] = @WebName,[WebURL] = @WebURL,
		[Catalog] = @Catalog,[isBlog] = @isBlog,[isSecondURL] = @isSecondURL,[isForum] = @isForum,
		[Copyright]=@Copyright,[Record]=@Record,[WebLOGO]=@WebLOGO,[SystemTel] = @SystemTel,[HotTel] = @HotTel,
		[FaxTel] = @FaxTel,[CorporationName] = @CorporationName,[Receiver] = @Receiver,[Address] = @Address,
		[PostalCode] = @PostalCode,[BankAccount] = @BankAccount,[BankName] = @BankName,[Rewrite]=@Rewrite
END
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN
' 
END
---104.0----[SP_W_TradeSettings_Update]--------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_TradeSettings_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_TradeSettings_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_TradeSettings_Update] 
@TradeType nvarchar(100),
@isADD bit='''',
@isEdit bit='''',
@Mark int='''',
@isEmail bit='''',
@EmailTitle nvarchar(100)='''',
@EmailInfo NTEXT,
@isSMS nvarchar(100)='''',
@SMSTitle nvarchar(100)='''',
@SMSInfo nvarchar(1000)=''''
 AS 
BEGIN TRAN 
if exists (select * from W_TradeSettings where TradeType=@TradeType)
BEGIN 
	UPDATE W_TradeSettings SET 
	[isADD] = @isADD,[isEdit] = @isEdit,[Mark] = @Mark,[isEmail] = @isEmail,[EmailTitle] = @EmailTitle,[EmailInfo] = @EmailInfo,[isSMS] = @isSMS,[SMSTitle] = @SMSTitle,[SMSInfo] = @SMSInfo
	WHERE [TradeType] = @TradeType
	if @TradeType=''供应''
		update W_EmailTemplate set Title=@EmailTitle,Content=@EmailInfo where Flag=''us_supply''
	if @TradeType=''求购''
		update W_EmailTemplate set Title=@EmailTitle,Content=@EmailInfo where Flag=''us_purchase''
END 
else
BEGIN
	INSERT INTO W_TradeSettings([TradeType],[isADD],[isEdit],[Mark],[isEmail],[EmailTitle],[EmailInfo],[isSMS],[SMSTitle],[SMSInfo])
	VALUES(@TradeType,@isADD,@isEdit,@Mark,@isEmail,@EmailTitle,@EmailInfo,@isSMS,@SMSTitle,@SMSInfo)
	if @TradeType=''供应''
		update W_EmailTemplate set Title=@EmailTitle,Content=@EmailInfo where Flag=''us_supply''
	if @TradeType=''求购''
		update W_EmailTemplate set Title=@EmailTitle,Content=@EmailInfo where Flag=''us_purchase''
END
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN
' 
END
---105.0-----[SP_W_EmailTemplate_Add]-----------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_EmailTemplate_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_EmailTemplate_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_EmailTemplate_Add]
@Type nvarchar(50),
@Flag nvarchar(50),
@Name nvarchar(50),
@Title nvarchar(100),
@Content text,
@IsHtml bit,
@AddDate datetime
as
begin tran
	if exists (select * from W_EmailTemplate where name=@Name)
		raiserror(''该邮件名称已存在'',16,1)
	else
		insert into W_EmailTemplate(Type,Flag,Name,Title,Content,IsHtml,AddDate)values(@Type,@Flag,@Name,@Title,@Content,@IsHtml,@AddDate)
	if @Flag=''us_supply''
		update W_TradeSettings set EmailTitle=@Title,EmailInfo=@Content where TradeType=''供应''
	if @Flag=''us_purchase''
		update W_TradeSettings set EmailTitle=@Title,EmailInfo=@Content where TradeType=''求购''
	if @@error<>0
	begin
       rollback tran
       return -2
    end
commit tran
' 
END
---106.0----[SP_W_EmailTemplate_Update]-----------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_EmailTemplate_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_EmailTemplate_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_EmailTemplate_Update]
@ID int,
@Flag nvarchar(50),
@Name nvarchar(50),
@Title nvarchar(100),
@Content text,
@IsHtml bit,
@AddDate datetime
as
begin tran
    update  W_EmailTemplate set Flag=@Flag,Name=@Name,Title=@Title,Content=@Content,IsHtml=@IsHtml,AddDate=@AddDate where ID=@ID
	if @Flag=''us_supply''
		update W_TradeSettings set EmailTitle=@Title,EmailInfo=@Content where TradeType=''供应''
	if @Flag=''us_purchase''
		update W_TradeSettings set EmailTitle=@Title,EmailInfo=@Content where TradeType=''求购''
	if @@error<>0
	begin
       rollback tran
       return -2
    end
commit tran
' 
END
----107.0----[Pu_AddPurchase]------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[Pu_AddPurchase]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [Pu_AddPurchase]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [Pu_AddPurchase] 
@LinkName NVARCHAR(50),
@CompanyName NVARCHAR(100),
@Email NVARCHAR(100),
@TelePhone NVARCHAR(30),
@Mobile NVARCHAR(15),
@UserIP NVARCHAR(20),
@ID int OUTPUT
AS
INSERT INTO [U_Visitor] ([LinkMan],[CompanyName],[Email],[Telephone],[Mobile],[UserIP],[AddDate]) VALUES (@LinkName,@CompanyName,@Email,@TelePhone,@Mobile,@UserIP,GETDATE());
SELECT @ID=@@IDENTITY;' 
END
---108.0---[SP_C_Purchase_Update]----------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_C_Purchase_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_C_Purchase_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_C_Purchase_Update] 
@ID int,
@IndType varchar(20) ,
@Name nvarchar(40) ,
@Key nvarchar(100) ,
@Info nvarchar(4000) ,
@Picture_1 nvarchar(50) ,
@Picture_2 nvarchar(50) ,
@Picture_3 nvarchar(50) ,
@StartDate datetime,
@EndDate datetime,
@Brand nvarchar(40),
@Model nvarchar(100),
@Unit nvarchar(50),
@Count int,
@Price nvarchar(20) 
AS
BEGIN TRAN
DECLARE @imgFile nvarchar(50),@isVerify int
BEGIN
	set @imgFile=Replace(@Picture_1,''/t_'',''/'')
	SELECT @isVerify=isEdit FROM  W_TradeSettings WHERE TradeType=''求购''
	--根据商机信息设置表判断修改商机信息是否需要审核
		IF @isVerify=1
		  SET @isVerify = 3
		ELSE
		  SET @isVerify = 1
	UPDATE C_Purchase SET 
	[IndType] = @IndType,[Name] = @Name,[Key] = @Key,[Info] = @Info,[Picture_1] = @imgFile,[Picture_2] = @Picture_2,[Picture_3] = @Picture_3,[StartDate] = @StartDate,[EndDate]=@EndDate,[Brand] = @Brand,[Model] = @Model,[Unit]=@Unit,[Count]=@Count,[Price]=@Price,[Verify]=@isVerify
	WHERE [ID] = @ID

END
IF @@error<>0
BEGIN
ROLLBACK TRAN
    return -2
END	
COMMIT TRAN
' 
END
---109.0---[SP_W_WillAgent_Update]-----------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_WillAgent_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_WillAgent_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_WillAgent_Update]
@ID int,
@Name nvarchar(100) ,
@AgentType int ,
@Title nvarchar(100) ,
@Validity nvarchar(100) ,
@Count int ,
@Investment nvarchar(100) ,
@Price nvarchar(100) ,
@Standard nvarchar(100) ,
@Detail NTEXT ,
@Picture nvarchar(200) ,
@Obligate nvarchar(100) 
AS 
BEGIN TRAN
UPDATE W_WillAgent SET 
[Name] = @Name,[AgentType] = @AgentType,[Title] = @Title,[Validity] = @Validity,[Count] = @Count,[Investment] = @Investment,[Price] = @Price,[Standard] = @Standard,[Detail] = @Detail,[Picture] = @Picture,[Obligate] = @Obligate
WHERE [ID] = @ID
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN
' 
END
---110.0-----[SP_W_WillAgent_UpdateVerify]---------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_WillAgent_UpdateVerify]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_WillAgent_UpdateVerify]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_WillAgent_UpdateVerify] 
@ID INT,
@Verify INT
AS
BEGIN TRAN
SELECT  @Verify=Verify FROM W_WillAgent WHERE ID=@ID
IF(@Verify=''1'')
	UPDATE W_WillAgent SET Verify=''3'' WHERE ID=@ID
ELSE
	UPDATE W_WillAgent SET Verify=''1'' WHERE ID=@ID
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN
' 
END
---111.0-----[SP_W_WillAgent_UpdateRecommend]-----------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_WillAgent_UpdateRecommend]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_WillAgent_UpdateRecommend]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_WillAgent_UpdateRecommend] 
@ID INT,
@Recommend  bit
AS
BEGIN TRAN
SELECT  @Recommend =Recommend  FROM W_WillAgent WHERE ID=@ID
IF(@Recommend =1)
	UPDATE W_WillAgent SET Recommend =0 WHERE ID=@ID
ELSE
	UPDATE W_WillAgent SET Recommend =1 WHERE ID=@ID
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN
' 
END
---112.0----[SP_W_HelpCenter_Add]---------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_HelpCenter_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_HelpCenter_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_HelpCenter_Add]
@TypeID int ,
@Title nvarchar(400) ,
@Info nvarchar(3000)='''' ,
@AddDate datetime
AS 
SET @AddDate=GETDATE()
BEGIN TRAN
IF EXISTS (SELECT * FROM W_HelpCenter WHERE Title=@Title)
	RAISERROR(''改标题已存在'',16,1)
ELSE
	BEGIN
		INSERT INTO W_HelpCenter([TypeID],[Title],[Info],[AddDate]
        )VALUES(@TypeID,@Title,@Info,@AddDate)
	END
	
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN	
' 
END
---113.0----[SP_W_HelpCenter_Update]----------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_HelpCenter_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_HelpCenter_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_HelpCenter_Update]
@ID int,
@TypeID int ,
@Title nvarchar(400) ,
@Info nvarchar(3000)='''' ,
@AddDate datetime 
AS 
BEGIN TRAN
BEGIN
	UPDATE W_HelpCenter SET 
    [TypeID] = @TypeID,[Title] = @Title,[Info] = @Info,[AddDate] = @AddDate
    WHERE [ID] = @ID
END
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN	
' 
END
---114.0-----[SP_W_HelpType_Delete]-------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_HelpType_Delete]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_HelpType_Delete]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_HelpType_Delete]
@ID INT
AS
BEGIN TRAN 
DELETE FROM W_HelpType WHERE ID=@ID
DELETE FROM W_HelpCenter WHERE TypeID=@ID

IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN	
' 
END
---115.0----[SP_W_HelpType_Add]----------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_HelpType_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_HelpType_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_HelpType_Add]
@ParentID INT,
@Name nvarchar(50)
AS
BEGIN TRAN
IF EXISTS ( SELECT * from [W_HelpType] WHERE [Name] = @Name )
  BEGIN
   RAISERROR(''此帮助类型已存在'', 16, 1)
  END
ELSE
 BEGIN
  INSERT INTO W_HelpType([Name],[ParentID]) VALUES(@Name,@ParentID)
 END

IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN	
' 
END
---116.0---[SP_W_HelpType_Update]-------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_HelpType_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_HelpType_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_HelpType_Update]
@ID INT,
@Name nvarchar(50)
AS
BEGIN TRAN
 UPDATE W_HelpType SET [Name]=@Name WHERE ID=@ID
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN	
' 
END
---117.0---[SP_W_SincereAgent_UpdateVerify]---------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_SincereAgent_UpdateVerify]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_SincereAgent_UpdateVerify]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_SincereAgent_UpdateVerify] 
@ID INT,
@Verify INT
AS
BEGIN TRAN
SELECT  @Verify=Verify FROM W_SincereAgent WHERE ID=@ID
IF(@Verify=''1'')
	UPDATE W_SincereAgent SET Verify=''3'' WHERE ID=@ID
ELSE
	UPDATE W_SincereAgent SET Verify=''1'' WHERE ID=@ID
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN 
' 
END
---118.0----[SP_W_SincereAgent_UpdateRecommend]------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_SincereAgent_UpdateRecommend]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_SincereAgent_UpdateRecommend]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_SincereAgent_UpdateRecommend]
@ID INT,
@Recommend bit
AS
BEGIN TRAN
SELECT  @Recommend =Recommend  FROM W_SincereAgent WHERE ID=@ID
IF(@Recommend =1)
	UPDATE W_SincereAgent SET Recommend =0 WHERE ID=@ID
ELSE
	UPDATE W_SincereAgent SET Recommend =1 WHERE ID=@ID
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN  
' 
END
---119.0----[SP_W_SincereAgent_Update]-------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_SincereAgent_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_SincereAgent_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_SincereAgent_Update]
@ID int,
@Name nvarchar(100)='''' ,
@AgentType int='''' ,
@Title nvarchar(100)='''' ,
@Validity nvarchar(40) ='''',
@Brand nvarchar(100) ='''',
@Investment nvarchar(100) ='''',
@Amount float ='''',
@InitialFee float='''' ,
@Require nvarchar(100)='''' ,
@Cradle nvarchar(100) ='''',
@Payback float='''' ,
@Period float ='''',
@Concession float='''' ,
@Contract float ='''',
@Count int ='''',
@BusinessDate int='''' ,
@isMoreBrand bit ='''',
@BusinessMode nvarchar(100)='''' ,
@Developmental nvarchar(100)='''' ,
@Detail NTEXT ,
@Picture nvarchar(200) ='''',
@Obligate nvarchar(100)=''''
AS
BEGIN TRAN
		UPDATE W_SincereAgent SET 
		[Name] = @Name,[AgentType] = @AgentType,[Title] = @Title,[Validity] = @Validity,[Brand] = @Brand,[Investment] = @Investment,[Amount] = @Amount,[InitialFee] = @InitialFee,[Require] = @Require,[Cradle] = @Cradle,[Payback] = @Payback,[Period] = @Period,[Concession] = @Concession,[Contract] = @Contract,[Count] = @Count,[BusinessDate] = @BusinessDate,[isMoreBrand] = @isMoreBrand,[BusinessMode] = @BusinessMode,[Developmental] = @Developmental,[Detail] = @Detail,[Picture] = @Picture,[Obligate] = @Obligate
		WHERE [ID] = @ID

IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN 
' 
END
-----120.0--[SP_W_ConsultativeType_Update]---------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_ConsultativeType_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_ConsultativeType_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_ConsultativeType_Update]
@ID INT ,
@Name NVARCHAR(50)
AS
BEGIN TRAN 
  UPDATE W_ConsultativeType SET [Name] = @Name WHERE ID = @ID
	
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN
' 
END
----121.0---[SP_W_ConsultativeType_Add]---------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_ConsultativeType_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_ConsultativeType_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_ConsultativeType_Add]
@Name NVARCHAR(50),
@ParentID INT='''',
@FullID VARCHAR(50)=''''
AS
BEGIN TRAN 
IF EXISTS(SELECT * FROM W_ConsultativeType WHERE [Name] = @Name)
	BEGIN 
		RAISERROR(''此咨询类型已存在'', 16, 1)
	END
 ELSE
	BEGIN 
		INSERT INTO W_ConsultativeType([Name],[ParentID],[FullID]) VALUES (@Name,@ParentID,@FullID)
		
	END

IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN
' 
END
----122.0---[SP_W_UserLable_Add]------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_UserLable_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_UserLable_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_UserLable_Add]
(
@LableName NVARCHAR(20)='''',
@TypeID INT,
@Info NTEXT='''',
@Remarks NVARCHAR(100)=''''

) 	
AS 
BEGIN TRAN
IF EXISTS(SELECT * FROM W_UserLable WHERE [LableName] = @LableName )
BEGIN
	RAISERROR(''此标签已存在'', 16, 1)
END
ELSE
BEGIN
    INSERT INTO W_UserLable([LableName],[TypeID],[Info],[Remarks])
	VALUES (@LableName,@TypeID,@Info,@Remarks)

END

IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN
' 
END
---123.0----[SP_W_UserLable_Update]-------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_UserLable_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_UserLable_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_UserLable_Update]
@ID INT,
@TypeID INT,
@LableName nVarchar(20),
@Info NTEXT,
@Remarks NVARCHAR(100)
AS
BEGIN TRAN  
UPDATE W_UserLable SET 
	[LableName]=@LableName,
	[TypeID]=@TypeID,
	[Info]=@Info,
	[Remarks]=@Remarks
	WHERE [ID] = @ID
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN
' 
END
---124.0----[SP_W_TemplatesType_Add]----------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_TemplatesType_Add]') AND type in (N'P', N'PC'))
BEGIN
DROP PROC [SP_W_TemplatesType_Add]
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_TemplatesType_Add]
(
	@Name NVARCHAR(20)='''',
    @ParentID INT =0
) 	
AS 
DECLARE @FullID varchar(50)   
BEGIN TRAN
	IF EXISTS(SELECT * FROM W_TemplatesType WHERE [Name] = @Name ANd ParentID=@ParentID)
		BEGIN
			RAISERROR(''此模板类型已存在'', 16, 1)
		END
	ELSE
		BEGIN
            IF @ParentID=0
               SET @FullID=0
            ELSE
               BEGIN
                  SELECT @FullID=FullID FROM W_TemplatesType WHERE  ParentID=@ParentID
                  SET @FullID=@FullID+'',''+cast(@ParentID as varchar) 
			   END
            INSERT INTO W_TemplatesType([Name],ParentID,FullID)
			VALUES (@Name,@ParentID,@FullID)

		END

IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN' 
END
-----125.0---[SP_W_TemplatesType_Update]-------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_TemplatesType_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_TemplatesType_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_TemplatesType_Update]
(
@Name NVARCHAR(20)='''',
@ID INT
) 	
AS    
	
BEGIN TRAN
IF EXISTS(SELECT * FROM W_TemplatesType WHERE ID = @ID)

BEGIN
   UPDATE W_TemplatesType SET 
   [Name] = @Name
   WHERE ID=@ID 
   COMMIT TRAN
END
ELSE
	BEGIN
		RAISERROR(''此模板类型不存在'', 16, 1)
	END	
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN
' 
END
---126.0-----[SP_W_Links_UpdateVerify]----------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_Links_UpdateVerify]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_Links_UpdateVerify]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_Links_UpdateVerify]
@ID INT,
@Verify BIT=''''
AS
BEGIN TRAN
SELECT  @Verify=Verify FROM W_Links WHERE ID=@ID
IF(@Verify=1)
	UPDATE W_Links SET Verify=0 WHERE ID=@ID
ELSE
	UPDATE W_Links SET Verify=1 WHERE ID=@ID

IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN	
' 
END
---127.0---[SP_W_Links_Add]---------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_Links_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_Links_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_Links_Add] 
@LinkType int,
@WebName nvarchar(100)='''' ,
@WebURL nvarchar(100)='''' ,
@WebLOGO varchar(50)='''' ,
@WebIntro nvarchar(300)='''' ,
@WebAdmin nvarchar(100)='''' ,
@Email varchar(50)='''' ,
@Verify bit,
@Sequence varchar(50)='''' 
AS 
BEGIN TRAN  
IF EXISTS(SELECT * FROM W_Links WHERE  [WebName] = @WebName )
	BEGIN 
		RAISERROR(''此链接已存在'', 16, 1)
	END
 ELSE
	BEGIN
		INSERT INTO W_Links(
		[LinkType],[WebName],[WebURL],[WebLOGO],[WebIntro],[WebAdmin],[Email],[Verify],[Sequence]
		)VALUES(
		@LinkType,@WebName,@WebURL,@WebLOGO,@WebIntro,@WebAdmin,@Email,@Verify,@Sequence
		)
		
	END

IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN	
' 
END
---128.0--[SP_W_Links_Update]-------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_Links_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_Links_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_Links_Update]
@ID int,
@LinkType int,
@WebName nvarchar(100),
@WebURL nvarchar(100),
@WebLOGO varchar(50),
@WebIntro nvarchar(300),
@WebAdmin nvarchar(100),
@Email varchar(50),
@Sequence varchar(50)
AS 
BEGIN TRAN  
BEGIN
	UPDATE W_Links SET 
	[LinkType] = @LinkType,[WebName] = @WebName,[WebURL] = @WebURL,[WebLOGO] = @WebLOGO,[WebIntro] = @WebIntro,[WebAdmin] = @WebAdmin,[Email] = @Email,[Sequence] = @Sequence
	WHERE [ID] = @ID

END
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN	
' 
END
---129.0---[SP_W_Keyword_UpdateRecommend]----------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_Keyword_UpdateRecommend]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_Keyword_UpdateRecommend]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_Keyword_UpdateRecommend]
@ID INT,
@IsCommend BIT
AS
BEGIN TRAN
SELECT  @IsCommend=IsCommend FROM W_Keyword WHERE ID=@ID
IF(@IsCommend=1)
	UPDATE W_Keyword SET IsCommend=0 WHERE ID=@ID
ELSE
	UPDATE W_Keyword SET IsCommend=1 WHERE ID=@ID

IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN		
' 
END
----130.0----[SP_W_Keyword_Add]---------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_Keyword_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_Keyword_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_Keyword_Add]
@Name nvarchar(500)='''',
@Type varchar(500)='''',
@Count int=0,
@IsCommend bit=0,
@IsStatic bit=1,
@EditDate datetime
AS
DECLARE @Names nvarchar(500);
DECLARE @Types nvarchar(500);
DECLARE @t1 nvarchar(50);
DECLARE @n1 nvarchar(50);
BEGIN TRAN
set @Names = @Name+ '',''
set @Types = @Type+'',''
while CHARINDEX('','',@Types)>0
   begin
   SELECT @t1=LEFT(@Types,CHARINDEX('','',@Types+'','')-1),@Types=STUFF(@Types,1,CHARINDEX('','',@Types+'',''),'''')
   while CHARINDEX('','',@Names)>0
      begin
        SELECT @n1=LEFT(@Names,CHARINDEX('','',@Names+'','')-1),@Names=STUFF(@Names,1,CHARINDEX('','',@Names+'',''),'''')
        INSERT INTO W_Keyword ([Name], [Type], [Count], IsCommend, IsStatic,EditDate) 
        VALUES (@n1, @t1, @Count, @IsCommend, @IsStatic,@EditDate)
      end
   set @Names = @Name+ '',''
   end 
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN
' 
END
-----131.0------[SP_W_Keyword_Update]---------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_Keyword_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_Keyword_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_Keyword_Update]
@ID int,
@Name nvarchar(20),
@Count int=0,
@IsCommend bit,
@EditDate datetime
AS 
BEGIN TRAN  
BEGIN 
	UPDATE W_Keyword SET [Name]=@Name,[Count]=@Count,IsCommend=@IsCommend,EditDate=@EditDate
    WHERE ID=@ID
  END

IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN	
' 
END
----132.0----[SP_W_UsersProducts_Update]------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_UsersProducts_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_UsersProducts_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_UsersProducts_Update]
@ID int,
@sort int,
@money float='''',
@date DateTime=''''
AS 
BEGIN TRAN  
  if @date=''''
   update [W_UsersProducts] SET [Sort]=@sort,[Money]=@money WHERE ID = @Id
  else
	BEGIN 
		update [W_UsersProducts] SET [Sort]=@sort,[Money]=@money,[LastDate]=@date WHERE ID = @Id
		update [W_UsersProducts] set IsTop=0 where sort=0
		update [W_UsersProducts] set IsTop=1 where sort<>0
	END
IF @@error <> 0
begin
	rollback tran
	return -2
END
COMMIT TRAN 
' 
END
----133.0---[SP_U_UserLink_UpdateVerify]-----------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_UserLink_UpdateVerify]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_UserLink_UpdateVerify]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_UserLink_UpdateVerify]
@ID int,
@Verify bit
AS 
BEGIN TRAN  
 SELECT  @Verify=Verify FROM U_UserLink WHERE ID=@ID
IF(@Verify=1)
	UPDATE U_UserLink SET Verify=0 WHERE ID=@ID
ELSE
	UPDATE U_UserLink SET Verify=1 WHERE ID=@ID

IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END
---134.0---[SP_U_UserLink_Add]-------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_UserLink_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_UserLink_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_UserLink_Add]
@UserID int,
@Name nvarchar(100) ,
@Url nvarchar(100)='''' ,
@Logo nvarchar(100)='''' 
AS 
BEGIN TRAN  
IF EXISTS(SELECT * FROM U_UserLink WHERE  [Name] = @Name )
	BEGIN 
		RAISERROR(''此链接已存在'', 16, 1)
	END
 ELSE
	INSERT INTO U_UserLink([UserID],[Name],[Url],[Logo])VALUES(@UserID,@Name,@Url,@Logo)
IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END
----135.0----[SP_U_UserLink_Update]------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_UserLink_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_UserLink_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_UserLink_Update]
@ID int,
@Name nvarchar(100) ,
@Url nvarchar(100)='''' ,
@Logo nvarchar(100)='''' 
AS 
BEGIN TRAN  
   UPDATE U_UserLink SET [Name] = @Name,[Url] = @Url,[Logo] = @Logo WHERE [ID] = @ID	
IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END
-----136.0----[SP_W_MetaType_Update]-------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_MetaType_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_MetaType_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_MetaType_Update]
@ID INT,
@Type NVARCHAR(20)
AS 
BEGIN TRAN
BEGIN
    UPDATE W_MetaType SET[Type]=@Type WHERE ID=@ID
	
END
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN	
' 
END
---137.0----[SP_W_UserLableType_Add]-------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_UserLableType_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_UserLableType_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_UserLableType_Add] 
@Name Nvarchar(20)
AS
begin tran
if exists(select * from W_UserLableType where [Name]=@Name)
	begin
		RAISERROR (''对不起！添加失败，该类别已经存在!'',16,1)
	end
else 
	begin
		insert into W_UserLableType([Name])values(@Name)
		
	end
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN' 
END
----138.0----[SP_W_UserLableType_Update]---------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_UserLableType_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_UserLableType_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_UserLableType_Update]
@ID int,
@Name NVARCHAR(20)
AS
BEGIN TRAN 
BEGIN
	UPDATE 	W_UserLableType SET [Name]=@Name WHERE ID=@ID
	
END
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN' 
END
-----139.0----[SP_U_UserOperate_UpdateTemplate]--------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_UserOperate_UpdateTemplate]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_UserOperate_UpdateTemplate]')
end
begin
EXEC sp_executesql @statement = N'
CREATE  PROCEDURE [SP_U_UserOperate_UpdateTemplate]
@ID int,
@Template Nvarchar(50)
AS
BEGIN TRAN  
   UPDATE U_UserOperate SET Template_New=@Template WHERE UserID=@ID
IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END
----140.0-------[SP_U_VipGrade_UserOperate_Count]---------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_VipGrade_UserOperate_Count]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_VipGrade_UserOperate_Count]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_VipGrade_UserOperate_Count]
@GradeID INT,
@Product nvarchar(50),
@UserID INT
AS
begin 
	declare @SupplyCount int,@PurchaseCount int,@ExpoCount int,@SincereCount int,@WillCount int
    declare @SupplyCount1 int,@PurchaseCount1 int,@ExpoCount1 int,@SincereCount1 int,@WillCount1 int
	select @SupplyCount=SupplyCount,@PurchaseCount=PurchaseCount,@ExpoCount=ExpoCount,@SincereCount=SincereCount,@WillCount=WillCount from U_VipGrade where ID=@GradeID
	select @SupplyCount1=AddSupply,@PurchaseCount1=AddPurchase from U_UserOperate WHERE UserID=@UserID
	if @Product=''supply''
		begin
			if @SupplyCount-@SupplyCount1<=0
			return 1
		end
    if  @Product=''purchase''
		begin
			if @PurchaseCount-@PurchaseCount1<=0
			return 1
		end
		
end
' 
END
----141.0-------[SP_P_CheckBusinessCount]-----------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_P_CheckBusinessCount]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_P_CheckBusinessCount]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_P_CheckBusinessCount]
@ManageRights int output,
@AddProduct int output,
@ProductCount int output,
@GradeID INT,
@Product nvarchar(50),
@UserID INT
AS
begin
	if exists (select * from U_UserOperate WHERE UserID=@UserID)
	begin
    declare @newdate datetime,@olddate datetime
    set @newdate =CONVERT(VARCHAR(30),GETDATE(),112)
    select @olddate=CONVERT(VARCHAR(30),Nowdate,112) from U_UserOperate WHERE UserID=@UserID
    --发布管理的数量
	select @ManageRights=ManageRights from U_VipGrade where ID=@GradeID
	--如果为一天
	if @ManageRights=''1''
	begin 
		if @newdate <> @olddate
			update   U_UserOperate set Nowdate=getdate(),AddSupply=0,AddPurchase=0,AddExpo=0,AddSincereAgent=0,AddWillAgent=0
		    WHERE UserID=@UserID 
	end    
    IF @Product=''supply''
		begin
     		select  @ProductCount=SupplyCount from U_VipGrade where ID=@GradeID
			select  @AddProduct=AddSupply from U_UserOperate WHERE UserID=@UserID
		end
    ELSE IF @Product= ''purchase''
		begin
     		select @ProductCount=PurchaseCount from U_VipGrade where ID=@GradeID
			select @AddProduct=AddPurchase from U_UserOperate WHERE UserID=@UserID
		end
	ELSE IF @Product=''SincereAgent''
		begin
			select @ProductCount=SincereCount from U_VipGrade where ID=@GradeID
			select @AddProduct=AddSincereAgent  from U_UserOperate WHERE UserID=@UserID
		end
    ELSE IF @Product=''WillAgent''
		begin
			select @ProductCount = WillCount from U_VipGrade where ID=@GradeID
			select @AddProduct = AddWillAgent  from U_UserOperate WHERE UserID=@UserID
		end
	ELSE
		begin
			select @ProductCount=ExpoCount from U_VipGrade where ID=@GradeID
			select @AddProduct =AddExpo  from U_UserOperate WHERE UserID=@UserID		
		end
	end
end
' 
END
----142.0----[SP_W_IndustryType_UpdateToRelease]------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_IndustryType_UpdateToRelease]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_IndustryType_UpdateToRelease]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_IndustryType_UpdateToRelease]
@ID int,
@ToRelease int
AS 
BEGIN TRAN
	 SELECT  @ToRelease=ToRelease FROM W_IndustryType WHERE ID=@ID
	 IF(@ToRelease=1)
	     UPDATE W_IndustryType SET ToRelease=0 WHERE ID=@ID
	 ELSE
	     UPDATE W_IndustryType SET ToRelease=1 WHERE ID=@ID
IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END
---143.0-----[SP_W_IndustryType_Add]------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_IndustryType_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_IndustryType_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_IndustryType_Add]
@Type varchar(20),
@ID int,
@ToRelease bit,
@Sequence int
AS 
DECLARE @SuperiorsFlag bigint,@Superiors varchar(50)
IF @ID=0
	BEGIN	
		SET @SuperiorsFlag=0
		SET @Superiors=@ID
	END
ELSE
BEGIN
   SELECT @SuperiorsFlag=SuperiorsFlag,@Superiors=Superiors FROM W_IndustryType WHERE ID=@ID 
   SET @SuperiorsFlag=@SuperiorsFlag+1
   IF  @Superiors=''0''
	   SET @Superiors=@ID
   ELSE
	   SET @Superiors=@Superiors+'',''+cast(@ID as varchar)
END
BEGIN TRAN
	IF EXISTS(SELECT * FROM W_IndustryType WHERE [Type] = @Type and SuperiorsID=@ID)
		BEGIN 
			RAISERROR(''该行业类别已存在'', 16, 1)
		END
	 ELSE
		BEGIN 
            update W_IndustryType set UnderlingFlag=1 WHERE ID = @ID; 
			--SELECT ID FROM W_IndustryType WHERE [Type] = @Type
			insert into W_IndustryType([Type],[ToRelease],[SuperiorsFlag],UnderlingFlag,[SuperiorsID],[Superiors],[Sequence])
		    values(@Type,@ToRelease,@SuperiorsFlag,0,@ID,@Superiors,@Sequence);
	
	    END 
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN
' 
END
---144.0----[SP_W_IndustryType_Update]-----------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_IndustryType_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_IndustryType_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_IndustryType_Update]	
@ID int,
@Type varchar(20),
@Sequence int=''''
AS
declare @Count int
 select  @Count = count(ID) from W_IndustryType where ID = @ID
     if @Count <0
         begin 
         return -1
         end
     else
        begin
          begin tran sp   
            begin 
	         UPDATE W_IndustryType SET 
	         [Type] = @Type,[Sequence] = @Sequence
	         WHERE ID=@ID 
    end	      
    if @@error <> 0
            begin  
               rollback tran sp 
               return -2
            end
          commit tran
        end

' 
END
---145.0---[SP_W_TypeLink]----------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_TypeLink]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_TypeLink]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_TypeLink]
(
    @ID INT
) 	
AS    
BEGIN TRAN
IF EXISTS(SELECT * FROM W_IndustryType WHERE ID = @ID)
	BEGIN
     SELECT [Type] ,ID FROM W_IndustryType where patindex(''%,''+rtrim( ID)+'',%'','',''+(select Superiors from W_IndustryType where ID=@ID) +'','')>0 or ID=@ID

	END
ELSE
	BEGIN
		RAISERROR(''不存在此行业类别'', 16, 1)
	END
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN
' 
END
---146.0-----[SP_W_SystemLableType_Delete]-------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_SystemLableType_Delete]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_SystemLableType_Delete]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_SystemLableType_Delete]
@ID INT
AS
BEGIN TRAN
	DELETE FROM W_SystemLableType WHERE ID=@ID
	DELETE FROM W_SystemLable WHERE TypeID=@ID
	
IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN' 
END
----147.0--[SP_W_SystemLableType_Add]-------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_SystemLableType_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_SystemLableType_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE  PROCEDURE [SP_W_SystemLableType_Add]
@Name VARCHAR(20),
@ParentID VARCHAR(20)='''' 
AS
BEGIN TRAN  
IF EXISTS(SELECT * FROM W_SystemLableType WHERE [Name] = @Name)
	BEGIN 
		RAISERROR(''此标签类型已存在'', 16, 1)
	END
 ELSE
	BEGIN
		INSERT INTO W_SystemLableType([Name],[ParentID])VALUES(@Name,@ParentID)
		
	END
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN 
' 
END
----148.0-------[SP_W_SystemLableType_Update]--------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_SystemLableType_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_SystemLableType_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_SystemLableType_Update]
@ID INT,
@Name VARCHAR(20)
AS
BEGIN TRAN  
UPDATE W_SystemLableType SET 
	[Name] = @Name WHERE [ID] = @ID

IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN ' 
END
---149.0-----[SP_W_Product_Add]-------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_Product_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_Product_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_Product_Add]
	@name nvarchar(100) ,
	@TypeID nvarchar(50) ,
	@KeyWord nvarchar(40)='''' ,
	@Picture nvarchar(100)='''' ,
	@Detail nvarchar(4000) ='''' ,
	@AddDate datetime='''' ,
    @Price Float=''''
AS
BEGIN TRAN  
        IF EXISTS(SELECT * FROM W_Products WHERE [Name] = @name)
		   BEGIN
			 RAISERROR(''该产品已存在'', 16, 1)
		   END
		ELSE
           BEGIN
		     INSERT INTO W_Products([Name],[TypeID],[KeyWord],[Picture],[Detail],[AddDate],[Price])
			 VALUES(@name,@TypeID,@KeyWord,@Picture,@Detail,@AddDate,@Price)
            END
        
IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END
----150.0-----[SP_W_Product_Update]----------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_Product_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_Product_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_Product_Update]
	@name nvarchar(100) ,
	@TypeID nvarchar(50) ,
	@KeyWord nvarchar(40)='''' ,
	@Picture nvarchar(100)='''' ,
	@Detail nvarchar(4000) ='''' ,
	@AddDate datetime='''' ,
    @Price Float='''',
    @ID int
AS
BEGIN TRAN  
     UPDATE W_Products SET [Name]=@name,[TypeID]=@TypeID,[KeyWord]=@KeyWord,[Picture]=@Picture,[Detail]=@Detail,[AddDate]=@AddDate,[Price]=@Price
			 WHERE ID=@ID
           
IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END
----151.0-----[SP_W_Manager_Update]-----------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_Manager_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_Manager_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_Manager_Update]
@ID int,
@ManagerName nvarchar(100),
@PassWord varchar(50),
@RoleID int,
@Lockedup bit
AS 
BEGIN TRAN 
BEGIN 
    UPDATE W_Manager SET 
	[ManagerName] = @ManagerName,[PassWord] = @PassWord,[RoleID] = @RoleID,[Lockedup] = @Lockedup
	WHERE [ID] = @ID
END
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN	
' 
END
-----152.0------[SP_W_Manager_Add]-----------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_Manager_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_Manager_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_Manager_Add]
@ManagerName nvarchar(100) ,
@PassWord varchar(50) ,
@RoleID int ,
@Lockedup bit
AS 
BEGIN TRAN  
IF EXISTS(SELECT * FROM W_Manager WHERE  ManagerName = @ManagerName )
	BEGIN 
		RAISERROR(''该管理员已存在'', 16, 1)
	END
 ELSE
	BEGIN 
		INSERT INTO W_Manager(
		[ManagerName],[PassWord],[RoleID],[Lockedup]
		)VALUES(
		@ManagerName,@PassWord,@RoleID,@Lockedup
		)
	END
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN	
' 
END
---153.0----[SP_W_Manager_UpdateLocked]-------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_Manager_UpdateLocked]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_Manager_UpdateLocked]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_Manager_UpdateLocked]
@ID INT,
@Lockedup bit
AS
BEGIN TRAN
SELECT  @Lockedup=Lockedup FROM W_Manager WHERE ID=@ID
IF(@Lockedup=1)
	UPDATE W_Manager SET Lockedup=0 WHERE ID=@ID
ELSE
	UPDATE W_Manager SET Lockedup=1 WHERE ID=@ID
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN	
' 
END
----154.0---[SP_U_VipGrade_UpdateRights]------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_VipGrade_UpdateRights]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_VipGrade_UpdateRights]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_VipGrade_UpdateRights](
   @ID INT ,
   @ManageRights varchar(2) = ''0'',
   @SupplyCount INT = 0, 
   @PurchaseCount INT = 0 ,
   @ExpoCount INT = 0,
   @SincereCount INT = 0,
   @WillCount INT = 0
)
AS
BEGIN TRAN
IF EXISTS(SELECT Count(1) FROM U_VIPGrade WHERE ID = @ID)
   RAISERROR(''不存在该会员等级'', 16, 1)
ELSE
BEGIN
UPDATE U_VipGrade SET ManageRights = @ManageRights, SupplyCount = @SupplyCount, PurchaseCount = @PurchaseCount, 
ExpoCount = @ExpoCount, SincereCount = @SincereCount, WillCount = @WillCount
WHERE ID = @ID
END
IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END
---155.0--[SP_U_VipGrade_Update]---------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_VipGrade_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_VipGrade_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_VipGrade_Update]
(  
@ID INT ,
@Name VARCHAR(20),
@Flag VARCHAR(50), 
@ServiceTime INT,
@Price INT,
@Register BIT,
@ShopTemplate nvarchar (50)
) 	
AS
BEGIN TRAN
UPDATE U_VipGrade SET [Name] = @Name, Flag = @Flag, ServiceTime = @ServiceTime, Price = @Price, Register = @Register,ShopTemplate=@ShopTemplate
WHERE ID = @ID

IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END
---156.0---[SP_U_VipGrade_Add]---------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_VipGrade_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_VipGrade_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_VipGrade_Add]
(
   @Name VARCHAR(20),
   @Flag NVARCHAR(50),
   @ServiceTime int,   
   @Price INT,
   @Register BIT,
   @ShopTemplate nvarchar(50)
) 	
AS
BEGIN TRAN
IF EXISTS(SELECT * FROM U_VIPGrade WHERE [Name] = @Name)
BEGIN
	RAISERROR(''此会员等级已存在'', 16, 1)
END
ELSE
BEGIN
	INSERT INTO U_VIPGrade([Name], Flag, ServiceTime, Price, Register,ShopTemplate) 
	VALUES (@Name,@Flag, @ServiceTime, @Price,  @Register,@ShopTemplate)
	
END
IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END
---157.0----[SP_W_ConsultativeRestore_Add]------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_ConsultativeRestore_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_ConsultativeRestore_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_ConsultativeRestore_Add]
@ConsultativeID INT ,
@UserName NVARCHAR(50) ,
@Detail NVARCHAR(4000) 
AS
BEGIN TRAN  
INSERT INTO W_ConsultativeRestore (ConsultativeID, UserName, Detail)
	VALUES (@ConsultativeID, @UserName, @Detail)

IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN
' 
END
---158.0-----[SP_W_ConsultativeRestore_UpdateShield]----------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_ConsultativeRestore_UpdateShield]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_ConsultativeRestore_UpdateShield]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_ConsultativeRestore_UpdateShield]
@ID INT,
@Shield BIT=''''
AS
BEGIN TRAN
      SELECT  @Shield=Shield FROM W_ConsultativeRestore WHERE ID=@ID
	IF(@Shield=1)
	    UPDATE W_ConsultativeRestore SET Shield=0 WHERE ID=@ID
	ELSE
	   UPDATE W_ConsultativeRestore SET Shield=1 WHERE ID=@ID
	
IF @@ERROR<>0
BEGIN 
	ROLLBACK TRAN 
	RETURN -2
END 
COMMIT TRAN
' 
END
----159.0-------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_ConsultativeRestore_Update]') AND type in (N'P', N'PC'))
BEGIN
   exec('DROP PROC [SP_W_ConsultativeRestore_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_ConsultativeRestore_Update]
	@ID INT ,
	@Shield BIT
AS
BEGIN TRAN 
		UPDATE W_ConsultativeRestore SET Shield = @Shield WHERE ID = @ID
	
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN
' 
END
----160.0-----[SP_W_SystemLable_Update]----------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_SystemLable_Update]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_SystemLable_Update]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_SystemLable_Update]
@ID INT,
@Name NVARCHAR(50),
@TypeID INT,
@Title NVARCHAR(50),
@Info NTEXT,
@Remarks NVARCHAR(100),
@Head VARCHAR(1000)='''',
@Content NTEXT,
@Footer VARCHAR(1000)=''''
AS
BEGIN TRAN  
UPDATE W_SystemLable SET 
	[Name] = @Name, 
	[TypeID]=@TypeID,
	[Title]=@Title,
	[Info]=@Info,
	[Remarks]=@Remarks,
	[Head]=@Head,
	[Content]=@Content,
	[Footer]=@Footer
	WHERE [ID] = @ID
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN 
' 
END
---161.0----[SP_W_SystemLable_Add]--------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_W_SystemLable_Add]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_W_SystemLable_Add]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_W_SystemLable_Add]
(
@Name NVARCHAR(20)='''',
@Remarks NVARCHAR(100)='''',
@TypeID INT ='''',
@Title VARCHAR(50)='''',
@Info NTEXT='''',
@Head VARCHAR(1000)='''',
@Content NTEXT,
@Footer VARCHAR(1000)=''''
) 	
AS 
BEGIN TRAN
IF EXISTS(SELECT * FROM W_SystemLable WHERE [Name] = @Name)
	BEGIN
		RAISERROR(''此标签已存在'', 16, 1)
	END
ELSE
	BEGIN
        INSERT INTO W_SystemLable([Name],TypeID,Remarks,[Title],Info,Head,[Content],Footer)
		VALUES (@Name,@TypeID,@Remarks,@Title,@Info,@Head,@Content,@Footer)
		
	END

IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END 
COMMIT TRAN 
' 
END
-----162.0-----[SP_U_UserInfo_Delte]------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_UserInfo_Delte]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_UserInfo_Delte]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_UserInfo_Delte]
@id int
AS
BEGIN
  begin tran                           
		delete from U_UserInfo where [UserID]=@id
		delete from C_Certificate where [UserID]=@id
		delete from C_ProType where [UserID]=@id
		delete from C_News where [UserID]=@id
		delete from U_UserLink where [UserID]=@id
		delete from C_Supply where [UserID]=@id
		delete from C_Purchase where [UserID]=@id
		delete from W_SincereAgent where [UserID]=@id
		delete from W_WillAgent where [UserID]=@id
		delete from W_Expo where [UserID]=@id
		delete from W_UsersProducts where [UserID]=@id
		delete from U_Favorites where [UserID]=@id
		delete from U_UserOperate where [UserID]=@id
		delete from U_SubscriptionProduct where [UserID]=@id
		delete from U_SubscriptionNews where [UserID]=@id
		delete from U_SubscriptionEmail where [UserID]=@id
  IF @@error=0 
	 begin
	      commit tran
	  end
  else
	 begin
		 rollback tran
	 end
END'
end

--[SP_U_VipGrade_Delete]
IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N'[SP_U_VipGrade_Delete]') AND type in (N'P', N'PC'))
BEGIN
	exec('DROP PROC [SP_U_VipGrade_Delete]')
end
begin
EXEC sp_executesql @statement = N'
CREATE PROCEDURE [SP_U_VipGrade_Delete]
@SelfId int,     --自身ID
@Toid int='''', --转向的哪个类别ID
@Type int   --判断是否删除,1、转移，0、删除
AS
if @Type=1 
BEGIN
    BEGIN TRAN 
       UPDATE U_UserInfo set GradeID=@Toid where GradeID= @SelfId
	   DELETE FROM U_VipGrade WHERE ID=@SelfId
    COMMIT TRAN
END
IF @Type=0
BEGIN
    BEGIN TRAN 
       DELETE FROM U_VipGrade WHERE ID=@SelfId
	   DELETE FROM 	U_UserInfo WHERE GradeID=@SelfId
	COMMIT TRAN
END
IF @@ERROR<>0
BEGIN 
 ROLLBACK TRAN 
RETURN -2
END '
end

