

/****** Object:  Stored Procedure dbo.InsertNewsForCnet    Script Date: 2006-5-15 10:39:38 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertNewsForCnet]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertNewsForCnet]
GO


CREATE PROCEDURE dbo.InsertNewsForCnet
(
	@pkId int,
	@specialId int,
	@gradeId int,
	@gate int,
	@departmentId int,
	@listTitle varchar(1000),
	@intactTitle varchar(100),
	@subheading varchar(100),
	@isRecommand int,
	@isTop int,
	@useListTitle int,
	@showReviewLink int,
	@Author varchar(100),
	@AuthorMail varchar(100),
	@CopyFrom varchar(100),
	@copyFromLink varchar(100),
	@about varchar(100),
	@pageType int,
	@autoPageCharNum int,
	@readPoint int,
	@itemType int,
	@content text,
	@descript varchar(1000),
	@usersId int,
	@editor varchar(100),
	@click int,
	@updateTime datetime,
	@passed int,
	@isReview int,
	@isImageNews int,
	@imageName varchar(100),
	@MenuId int,
	@bigClassId int,
	@width int,
	@height int,
	@updateIp varchar(100)
)
AS
INSERT INTO news(pkId, specialId, gradeId, gate, departmentId, listTitle, intactTitle, subheading, isRecommand, isTop, useListTitle, showReviewLink, Author, AuthorMail, CopyFrom, copyFromLink, about, pageType, autoPageCharNum, readPoint, itemType, content, descript, usersId, editor, click, updateTime, passed, isReview, isImageNews, imageName, MenuId, bigClassId, width, height,updateIp) VALUES (@pkId, @specialId, @gradeId, @gate, @departmentId, @listTitle, @intactTitle, @subheading, @isRecommand, @isTop, @useListTitle, @showReviewLink, @Author, @AuthorMail, @CopyFrom, @copyFromLink, @about, @pageType, @autoPageCharNum, @readPoint, @itemType, @content, @descript, @usersId, @editor, @click, @updateTime, @passed, @isReview, @isImageNews, @imageName, @MenuId, @bigClassId, @width, @height,@updateIp)

GO





/****** Object:  Stored Procedure dbo.GetLatestNewsByPassed    Script Date: 2006-5-15 10:39:38 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetLatestNewsByPassed]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetLatestNewsByPassed]
GO


CREATE PROCEDURE GetLatestNewsByPassed
	@pageIndex int,
	@pageSize int=5,
	@gradeid int=1,
	@passed int=1,
	@gateId int,	
	@recordCount int ,
	@pageCount int 
AS	
	--转换成索引
	SELECT @pageIndex=@pageIndex-1	

	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(*) FROM News WHERE gate<>'+Convert(varchar,@gateId)+' and gradeId<='+convert(varchar,@gradeId)+' and passed='+convert(varchar,@passed)+' and isImageNews=0'
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

	--计算页数
	SET @pageCount = (@recordCount +@pageSize-1)/@pageSize

	--构造查询语句
	DECLARE @sqlStr varchar(1000),@size int

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	
	
	--查询第一页
	IF (@pageIndex = 0) OR (@pageIndex > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + '  n.*,u.userName,d.name "department",b.name "BigClassName" from news n,users u,department d,bigClass b where  n.gate<>'+Convert(varchar,@gateId)+' and   n.usersId=u.pkId and n.departmentId=d.pkId and bigClassId=b.pkId and n.gradeId<='+convert(varchar,@gradeId)+' and n.passed='+convert(varchar,@passed)+' and isImageNews=0  ORDER BY isTop DESC, n.pkId DESC'	
	--查询最后一页
	ELSE IF @pageIndex = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' +STR(@size)  + '  n.*,u.userName,d.name "department",b.name "BigClassName" from news n,users u,department d,bigClass b where  n.gate<>'+Convert(varchar,@gateId)+' and   n.usersId=u.pkId and n.departmentId=d.pkId and bigClassId=b.pkId and n.gradeId<='+convert(varchar,@gradeId)+' and n.passed='+convert(varchar,@passed)+' and isImageNews=0  ORDER BY isTop ASC, pkId ASC) TempTable ORDER BY isTop DESC, pkId DESC'	
	--查询中间页后面的页
	ELSE IF @pageIndex > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP '  + STR(@recordCount - @pageSize * @pageIndex ) + '  n.*,u.userName,d.name "department",b.name "BigClassName" from news n,users u,department d,bigClass b where  n.gate<>'+Convert(varchar,@gateId)+' and   n.usersId=u.pkId and n.departmentId=d.pkId and bigClassId=b.pkId and n.gradeId<='+convert(varchar,@gradeId)+' and n.passed='+convert(varchar,@passed)+' and isImageNews=0  ORDER BY isTop ASC, pkId ASC) TempTable ORDER BY isTop DESC, pkId DESC'
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @pageIndex=@pageIndex+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' + STR(@pageSize * @pageIndex ) + '  n.*,u.userName,d.name "department",b.name "BigClassName" from news n,users u,department d,bigClass b where  n.gate<>'+Convert(varchar,@gateId)+' and   n.usersId=u.pkId and n.departmentId=d.pkId and bigClassId=b.pkId and n.gradeId<='+convert(varchar,@gradeId)+' and n.passed='+convert(varchar,@passed)+' and isImageNews=0  ORDER BY isTop DESC, pkId DESC) TempTable1 ORDER BY isTop ASC, pkId ASC) TempTable2 ORDER BY isTop DESC, pkId DESC'
	END

	--执行查询
	EXEC(@sqlStr)

GO





/****** Object:  Stored Procedure dbo.GetAccreditByAdminId    Script Date: 2006-5-15 10:39:38 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetAccreditByAdminId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetAccreditByAdminId]
GO


/* 创建过程/函数 GetAccreditByAdminId;1。                                                            */
CREATE PROCEDURE GetAccreditByAdminId
@adminId int
/*
	(
		@parameter1 datatype = default value,
		@parameter2 datatype OUTPUT
	)
*/
AS
	/* SET NOCOUNT ON */
			select m.pkId,name,parentId
	from menu m,adminAssign a
	where 
	 a.menuId=0
	 and @adminId=a.adminId
	 and m.pkId=0
	union
		select m.pkId,name,parentId
	from menu m,adminAssign a
	where 
	parentId=0
	and a.adminId=@adminId
	and a.menuId=m.pkId
	
	union
	select pkId,name,parentId
	from menu 
	where 
	modeId<>4 
	and parentId<>0
	union
	SELECT m.pkId,m.name,m.parentId
   	FROM menu m,bigClass b,template t,adminAssign a	
	 where t.code<>'newsTop'
	  and t.code<>'fileDownTop'
	  and t.code<>'special'
	  and t.code<>'login'
	  and t.code<>'nav'
	  and t.code<>'search'
	  and t.code<>'SearchHorizontal'
	   and t.code<>'LoginHorizontal'
	    and t.code<>'baidu'
	    and t.code<>'shareNews'
	   and t.code<>'LatestNews'/*李聪杰20060327*/
	 and t.code<>'AdminLogin'/*李聪杰20060328*/
	-- and t.code<>'bigNews'/*文字头条，王敬红20060404*/
	and t.code<>'LatestMessage'/*李聪杰20060405*/
	  and m.bigClassId=b.pkId
	  and b.templateId=t.pkId
	  
	  
	RETURN

GO





/****** Object:  Stored Procedure dbo.GetAccredit    Script Date: 2006-5-15 10:39:38 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetAccredit]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetAccredit]
GO


/* 创建过程/函数 GetAccredit;1。                                                                     */
CREATE PROCEDURE GetAccredit
/*
	(
		@parameter1 datatype = default value,
		@parameter2 datatype OUTPUT
	)
*/
AS
	/* SET NOCOUNT ON */
	select pkId,name,parentId
	from menu 
	where modeId<>4
	union
	SELECT m.pkId,m.name,m.parentId
   	FROM menu m,bigClass b,template t	
	 where t.code<>'newsTop'
	  and t.code<>'fileDownTop'
	  and t.code<>'special'
	  and t.code<>'login'
	  and t.code<>'nav'
	  and t.code<>'search'
	   and t.code<>'SearchHorizontal'
	   and t.code<>'LoginHorizontal'
	    and t.code<>'baidu'
	    and t.code<>'shareNews'
	  and t.code<>'LatestNews'/*李聪杰20060327*/
	 and t.code<>'AdminLogin'/*李聪杰20060328*/
	-- and t.code<>'bigNews'/*文字头条，王敬红20060404*/
	and t.code<>'LatestMessage'/*李聪杰20060405*/
	  and m.bigClassId=b.pkId
	  and b.templateId=t.pkId
		
	RETURN

GO





/****** Object:  Stored Procedure dbo.InsertAd    Script Date: 2006-5-15 10:39:38 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertAd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertAd]
GO


/* 创建过程/函数 InsertAd;1。                                                                        */
CREATE PROCEDURE dbo.InsertAd
(
    @pkId int,
    @title varchar(100),
	@type int,
	@updatetime datetime,
	@click int,
	@speed int,
	@width int,
	@height int,
	@righter int,
	@toper int,
	@dating int,
	@timing int,
	@insertNews int,
	@url varchar(100),
	@updateIp varchar(100),
	@bigClassId int,
	@menuId int,
	@gradeId int,
	@gate int,
	@userId int,
	@isTop int,
	@ordering int,
	@passed int,
             @showUrl varchar(100),
	@showClose int
)
AS
	SET NOCOUNT ON;
INSERT INTO ad(title, type, updatetime, click,speed, width, height, righter, toper, dating, timing, insertNews, url, updateIp, bigClassId, menuId, gradeId, gate, userId, isTop, ordering, passed,showUrl,showClose) VALUES ( @title, @type, @updatetime, @click,@speed, @width, @height, @righter, @toper, @dating, @timing, @insertNews, @url, @updateIp, @bigClassId, @menuId, @gradeId, @gate, @userId, @isTop, @ordering, @passed,@showUrl,@showClose)


GO





/****** Object:  Stored Procedure dbo.UpdateAd    Script Date: 2006-5-15 10:39:38 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateAd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateAd]
GO


/* 创建过程/函数 UpdateAd;1。                                                                        */
CREATE PROCEDURE dbo.UpdateAd
(
    @pkId int,
	@title varchar(100),
	@type int,
	@updatetime datetime,
	@click int,
	@width int,
	@height int,
	@righter int,
	@toper int,
	@dating int,
	@timing int,
	@insertNews int,
	@speed int,
	@url varchar(100),
	@updateIp varchar(100),
	@bigClassId int,
	@menuId int,
	@gradeId int,
	@gate int,
	@userId int,
	@isTop int,
	@ordering int,
	@passed int,
             @showUrl varchar(100),
	@showClose int
)
AS
	SET NOCOUNT OFF;
UPDATE ad SET showClose=@showClose,showUrl=@showUrl, speed=@speed,title = @title, type = @type, updatetime = @updatetime, click = @click, width = @width, height = @height, righter = @righter, toper = @toper, dating = @dating, timing = @timing, insertNews = @insertNews, url = @url, updateIp = @updateIp, bigClassId = @bigClassId, menuId = @menuId, gradeId = @gradeId, gate = @gate, userId = @userId, isTop = @isTop, ordering = @ordering, passed = @passed WHERE (pkId = @pkId)


GO





/****** Object:  Stored Procedure dbo.InsertBigClass    Script Date: 2006-5-15 10:39:38 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertBigClass]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertBigClass]
GO


CREATE PROCEDURE dbo.InsertBigClass
(
	@pkId int,
	@name varchar(100),
	@remark varchar(100),
	@templateId int,
	@adminId int,
	@updateTime datetime,
	@isSystemItem int,
	@cssName varchar(100) -- added on 20060328 by wjh
)
AS
	SET NOCOUNT OFF;
INSERT INTO bigClass(name, remark, templateId,adminId,updateTime,isSystemItem,cssName) VALUES (@name, @remark, @templateId,@adminId,@updateTime,@isSystemItem, @cssName)

GO





/****** Object:  Stored Procedure dbo.UpDateBigClass    Script Date: 2006-5-15 10:39:38 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpDateBigClass]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpDateBigClass]
GO


CREATE PROCEDURE dbo.UpDateBigClass
(
	@pkId int,
	@name varchar(100),
	@remark varchar(100),
	@templateId int,
	@adminId int,
	@updateTime datetime,
	@isSystemItem int,
	@cssName varchar(100) -- added on 20060328 by wjh
)
AS
	SET NOCOUNT OFF;
UPDATE bigClass SET name = @name, remark = @remark, templateId = @templateId ,adminId=@adminId,updateTime=@updateTime,isSystemItem=@isSystemItem, cssName = @cssName  WHERE (pkId = @pkId)

GO





/****** Object:  Stored Procedure dbo.UpdateSiteBasicProperty    Script Date: 2006-5-15 10:39:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateSiteBasicProperty]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateSiteBasicProperty]
GO


/* 创建过程/函数 UpdateSiteBasicProperty;1。                                                         */
CREATE PROCEDURE dbo.UpdateSiteBasicProperty 
(
	@pkId int,
	@grade int,
	@defaultPassed int,
	@review int,
	@showIp int,
	@reviewCheck int,
	@reviewFiltrate varchar(100),
	@reviewAlternation int,
	@showClick int,
	@navGate int,
	@navPage varchar(100),
	@pagesize int,
	@shareNewsUrl  varchar(100),
	@shareNewsLayoutTemplateID int,
	@outputCacheTime int,
	@newsOpenMode int
)
AS
	SET NOCOUNT OFF;
UPDATE siteBasicProperty SET newsOpenMode=@newsOpenMode,outputCacheTime=@outputCacheTime,shareNewsLayoutTemplateID=@shareNewsLayoutTemplateID,shareNewsUrl=@shareNewsUrl,pagesize=@pagesize,navPage=@navPage,grade = @grade, defaultPassed = @defaultPassed, review = @review, showIp = @showIp, reviewCheck = @reviewCheck, reviewFiltrate = @reviewFiltrate, reviewAlternation = @reviewAlternation, showClick = @showClick, navGate = @navGate


GO





/****** Object:  Stored Procedure dbo.InsertNews    Script Date: 2006-5-15 10:39:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertNews]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertNews]
GO


/* 创建过程/函数 InsertNews;1。                                                                      */
CREATE PROCEDURE dbo.InsertNews
(
	@specialId int,
	@gradeId int,
	@gate int,
	@departmentId int,
	@listTitle varchar(1000),
	@intactTitle varchar(100),
	@subheading varchar(100),
	@isRecommand int,
	@isTop int,
	@useListTitle int,
	@showReviewLink int,
	@Author varchar(100),
	@AuthorMail varchar(100),
	@CopyFrom varchar(100),
	@copyFromLink varchar(100),
	@about varchar(100),
	@pageType int,
	@autoPageCharNum int,
	@readPoint int,
	@itemType int,
	@content text,
	@descript varchar(1000),
	@usersId int,
	@editor varchar(100),
	@click int,
	@updateTime datetime,
	@passed int,
	@isReview int,
	@isImageNews int,
	@imageName varchar(100),
	@MenuId int,
	@bigClassId int,
	@width int,
	@height int,
	@pkId int,
	@updateIp varchar(100),
	@useSubHeading int
)
AS
	SET NOCOUNT OFF;	 BEGIN TRANSACTION
INSERT INTO news(specialId, gradeId, gate, departmentId, listTitle, intactTitle, subheading, isRecommand, isTop, useListTitle, showReviewLink, Author, AuthorMail, CopyFrom, copyFromLink, about, pageType, autoPageCharNum, readPoint, itemType, content, descript, usersId, editor, click, updateTime, passed, isReview, isImageNews, imageName, MenuId, bigClassId, width, height,updateIp,useSubHeading) VALUES (@specialId, @gradeId, @gate, @departmentId, @listTitle, @intactTitle, @subheading, @isRecommand, @isTop, @useListTitle, @showReviewLink, @Author, @AuthorMail, @CopyFrom, @copyFromLink, @about, @pageType, @autoPageCharNum, @readPoint, @itemType, @content, @descript, @usersId, @editor, @click, @updateTime, @passed, @isReview, @isImageNews, @imageName, @MenuId, @bigClassId, @width, @height,@updateIp,@useSubHeading)
  COMMIT TRANSACTION


GO





/****** Object:  Stored Procedure dbo.UpdateNews    Script Date: 2006-5-15 10:39:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateNews]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateNews]
GO


/* 创建过程/函数 UpdateNews;1。                                                                      */
CREATE PROCEDURE dbo.UpdateNews
(
	@specialId int,
	@gradeId int,
	@gate int,
	@departmentId int,
	@listTitle varchar(1000),
	@intactTitle varchar(100),
	@subheading varchar(100),
	@isRecommand int,
	@isTop int,
	@useListTitle int,
	@showReviewLink int,
	@Author varchar(100),
	@AuthorMail varchar(100),
	@CopyFrom varchar(100),
	@copyFromLink varchar(100),
	@about varchar(100),
	@pageType int,
	@autoPageCharNum int,
	@readPoint int,
	@itemType int,
	@content text,
	@descript varchar(1000),
	@usersId int,
	@editor varchar(100),
	@click int,
	@updateTime datetime,
	@passed int,
	@isReview int,
	@isImageNews int,
	@imageName varchar(100),
	@MenuId int,
	@bigClassId int,
	@width int,
	@height int,
	@pkId int,
	@updateIp  varchar(100),
	@useSubHeading int
)
AS
	SET NOCOUNT OFF;
UPDATE news SET useSubHeading=@useSubHeading,updateIp=@updateIp,specialId = @specialId, gradeId = @gradeId, gate = @gate, departmentId = @departmentId, listTitle = @listTitle, intactTitle = @intactTitle, subheading = @subheading, isRecommand = @isRecommand, isTop = @isTop, useListTitle = @useListTitle, showReviewLink = @showReviewLink, Author = @Author, AuthorMail = @AuthorMail, CopyFrom = @CopyFrom, copyFromLink = @copyFromLink, about = @about, pageType = @pageType, autoPageCharNum = @autoPageCharNum, readPoint = @readPoint, itemType = @itemType, content = @content, descript = @descript, usersId = @usersId, editor = @editor, click = @click, updateTime = @updateTime, passed = @passed, isReview = @isReview, isImageNews = @isImageNews, imageName = @imageName, MenuId = @MenuId, bigClassId = @bigClassId, width = @width, height = @height WHERE (pkId = @pkId)


GO





/****** Object:  Stored Procedure dbo.InsertBackupInfo    Script Date: 2006-5-15 10:39:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertBackupInfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertBackupInfo]
GO


/* 创建过程/函数 InsertBackupInfo;1。                                                                */
CREATE PROCEDURE dbo.InsertBackupInfo
(
	@name varchar(100),
	@zipName varchar(100),
	@updateTime datetime,
	@isSystem int,
	@pkId int,
	@remark varchar(1000)
)
AS
	SET NOCOUNT OFF;
INSERT INTO BackupInfo(name, zipName, updateTime, isSystem,remark) VALUES (@name, @zipName, @updateTime, @isSystem,@remark)


GO





/****** Object:  Stored Procedure dbo.UpdateBackupInfo    Script Date: 2006-5-15 10:39:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpdateBackupInfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpdateBackupInfo]
GO


/* 创建过程/函数 UpdateBackupInfo;1。                                                                */
CREATE PROCEDURE dbo.UpdateBackupInfo
(
	@name varchar(100),
	@zipName varchar(100),
	@updateTime datetime,
	@isSystem int,
	@pkId int,
	@remark varchar(1000)
)
AS
	SET NOCOUNT OFF;
UPDATE BackupInfo SET name = @name, zipName = @zipName, updateTime = @updateTime, isSystem = @isSystem, remark=@remark WHERE (pkId = @pkId)


GO





/****** Object:  Stored Procedure dbo.GetNewsByBeforePkId    Script Date: 2006-5-15 10:39:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetNewsByBeforePkId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByBeforePkId]
GO


-- 根据pkId获取上一条新闻	20060407 添加约束条件passed=1
CREATE PROCEDURE GetNewsByBeforePkId
	@pkId int =null,
	@gradeId int=1,
	@gateId int 
AS
	SET NOCOUNT ON 		
	SELECT TOP 1 * FROM news WHERE gradeId <= @gradeId AND gate <> @gateId AND pkId > @pkId  AND passed=1
	AND bigClassId = (SELECT bigClassId FROM news where pkId = @pkId) ORDER BY pkId ASC

GO





/****** Object:  Stored Procedure dbo.GetNewsByAfterPkId    Script Date: 2006-5-15 10:39:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetNewsByAfterPkId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByAfterPkId]
GO


-- 根据pkId获取下一条新闻	20060407 添加约束条件passed=1
CREATE PROCEDURE GetNewsByAfterPkId
	@pkId int =null,
	@gradeId int=1,
	@gateId int 
AS
	SET NOCOUNT ON 		
	SELECT TOP 1 * FROM news WHERE gradeId <= @gradeId AND gate <> @gateId AND pkId < @pkId  AND passed=1
	AND bigClassId = (SELECT bigClassId FROM news where pkId = @pkId)ORDER BY pkId DESC

GO





/****** Object:  Stored Procedure dbo.InsertSiteBasicProperty    Script Date: 2006-5-15 10:39:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertSiteBasicProperty]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertSiteBasicProperty]
GO


/* 创建过程/函数 InsertSiteBasicProperty;1。                                                         */
CREATE PROCEDURE dbo.InsertSiteBasicProperty
(
	@pkId int,
	@grade int,
	@defaultPassed int,
	@review int,
	@showIp int,
	@reviewCheck int,
	@reviewFiltrate varchar(100),
	@reviewAlternation int,
	@showClick int,
	@navGate int,
	@shareNewsUrl  varchar(100),
	@shareNewsLayoutTemplateID int,
	@newsOpenMode int
)
AS
	SET NOCOUNT OFF;
INSERT INTO siteBasicProperty(pkId, grade, defaultPassed, review, showIp, reviewCheck, reviewFiltrate, reviewAlternation, showClick, navGate,shareNewsUrl,shareNewsLayoutTemplateID,newsOpenMode) VALUES (@pkId, @grade, @defaultPassed, @review, @showIp, @reviewCheck, @reviewFiltrate, @reviewAlternation, @showClick, @navGate,@shareNewsUrl,@shareNewsLayoutTemplateID,@newsOpenMode)

GO





/****** Object:  Stored Procedure dbo.GetNewsByUpdateTime    Script Date: 2006-5-15 10:39:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetNewsByUpdateTime]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByUpdateTime]
GO



CREATE PROCEDURE GetNewsByUpdateTime
	@pageSize int=5,
	@gradeid int=1,
	@gateId int
AS
	 SET NOCOUNT ON 
	
	DECLARE @vSQL varchar(1000)
	SET @vSQL = 'SELECT TOP ' + CONVERT(varchar, @pageSize) + ' n.*,u.userName,d.name "department" FROM news n,users u,department d WHERE  n.gate<>' 
		        + CONVERT(varchar,@gateId) + ' AND  n.usersId=u.pkId AND n.departmentId=d.pkId AND n.gradeId<=' 
		        + convert(varchar,@gradeId) + ' AND n.passed=1  ORDER BY n.updateTime  DESC'
	EXECUTE (@vSQL)

GO





/****** Object:  Stored Procedure dbo.GetLinkGangsByGroupId    Script Date: 2006-5-15 10:39:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetLinkGangsByGroupId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetLinkGangsByGroupId]
GO


CREATE PROCEDURE dbo.GetLinkGangsByGroupId
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeid int=1,
	@gateId int,
	@groupId int,
	@recordCount int output	-- 修改方法，返回记录数 王敬红20060404
AS	
	--转换成索引
	SELECT @currentPage = @currentPage-1	

	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(l.pkId) FROM linkGang l, users u, grade g WHERE l.gradeId<=' + CONVERT(nvarchar,@gradeId) 
				+ N' AND l.groupId=' + CONVERT(nvarchar,@groupId) + N' AND l.gate<>' + CONVERT(nvarchar,@gateId) 
				+ ' AND l.gradeId=g.pkId and l.userId=u.pkId'
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

	--计算页数
	SET @pageCount = (@recordCount +@pageSize-1)/@pageSize

	--构造查询语句
	DECLARE @sqlStr varchar(1000),@size int

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	
	
	--查询第一页
	IF (@currentPage = 0) OR (@currentPage > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' l.*,u.userName "userName",g.name "grade"  FROM linkGang l, users u, grade g '
					+ 'WHERE l.gradeId<=' + STR(@gradeId) + ' AND l.groupId=' + STR(@groupId) + ' AND l.gate<>' + STR(@gateId) 
					+ 'AND l.gradeId=g.pkId and l.userId=u.pkId ORDER BY l.isTop DESC, l.pkId DESC'	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' l.*,u.userName "userName",g.name "grade"  FROM linkGang l, users u, grade g '
					+ 'WHERE l.gradeId<=' + STR(@gradeId) + ' AND l.groupId=' + STR(@groupId) + ' AND l.gate<>' + STR(@gateId) 
					+ 'AND l.gradeId=g.pkId and l.userId=u.pkId ORDER BY l.isTop ASC, l.pkId ASC) TempTable ORDER BY isTop DESC, pkId DESC'	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' l.*,u.userName "userName",g.name "grade"  FROM linkGang l, users u, grade g '
					+ 'WHERE l.gradeId<=' + STR(@gradeId) + ' AND l.groupId=' + STR(@groupId) + ' AND l.gate<>' + STR(@gateId) 
					+ 'AND l.gradeId=g.pkId and l.userId=u.pkId ORDER BY l.isTop ASC, l.pkId ASC) TempTable ORDER BY isTop DESC, pkId DESC'
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' l.*,u.userName "userName",g.name "grade"  FROM linkGang l, users u, grade g '
					+ 'WHERE l.gradeId<=' + STR(@gradeId) + ' AND l.groupId=' + STR(@groupId) + ' AND l.gate<>' + STR(@gateId) 
					+ 'AND l.gradeId=g.pkId and l.userId=u.pkId ORDER BY l.isTop DESC, l.pkId DESC) TempTable1 '
					+ ' ORDER BY isTop ASC, pkId ASC) TempTable2 ORDER BY isTop DESC, pkId DESC'
	END

	--执行查询
	EXEC(@sqlStr)

RETURN

GO





/****** Object:  Stored Procedure dbo.GetLinkGangsByGroupIdAndUserId    Script Date: 2006-5-15 10:39:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetLinkGangsByGroupIdAndUserId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetLinkGangsByGroupIdAndUserId]
GO


CREATE PROCEDURE dbo.GetLinkGangsByGroupIdAndUserId
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeid int=1,
	@gateId int,
	@groupId int,
	@userId int,
	@recordCount int output	-- 修改方法，返回记录数 王敬红20060404
AS	
	--转换成索引
	SELECT @currentPage = @currentPage-1	

	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(l.pkId) FROM linkGang l, users u, grade g WHERE l.gradeId<=' + CONVERT(nvarchar,@gradeId) 
				+ N' AND l.groupId=' + CONVERT(nvarchar,@groupId) + N' AND l.userId=' + CONVERT(nvarchar,@userId) 
				+ N' AND l.gate<>' + CONVERT(nvarchar,@gateId) + ' AND l.gradeId=g.pkId and l.userId=u.pkId'
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

	--计算页数
	SET @pageCount = (@recordCount +@pageSize-1)/@pageSize

	--构造查询语句
	DECLARE @sqlStr varchar(1000),@size int

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	
		
	--查询第一页
	IF (@currentPage = 0) OR (@currentPage > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' l.*,u.userName "userName",g.name "grade"  FROM linkGang l, users u, grade g '
					+ 'WHERE l.gradeId<=' + STR(@gradeId) + ' AND l.groupId=' + STR(@groupId) + ' AND l.userId=' + STR(@userId) 
					+ ' AND l.gate<>' + STR(@gateId) + 'AND l.gradeId=g.pkId and l.userId=u.pkId ORDER BY l.isTop DESC, l.pkId DESC'	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1	
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' l.*,u.userName "userName",g.name "grade"  FROM linkGang l, users u, grade g '
					+ 'WHERE l.gradeId<=' + STR(@gradeId) + ' AND l.groupId=' + STR(@groupId) + ' AND l.userId=' + STR(@userId) 
					+ ' AND l.gate<>' + STR(@gateId) + 'AND l.gradeId=g.pkId and l.userId=u.pkId ORDER BY l.isTop ASC, l.pkId ASC) TempTable ORDER BY isTop DESC, pkId DESC'	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' l.*,u.userName "userName",g.name "grade"  FROM linkGang l, users u, grade g '
					+ 'WHERE l.gradeId<=' + STR(@gradeId) + ' AND l.groupId=' + STR(@groupId) + ' AND l.userId=' + STR(@userId) 
					+ ' AND l.gate<>' + STR(@gateId) + 'AND l.gradeId=g.pkId and l.userId=u.pkId ORDER BY l.isTop ASC, l.pkId ASC) TempTable ORDER BY isTop DESC, pkId DESC'
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' l.*,u.userName "userName",g.name "grade"  FROM linkGang l, users u, grade g '
					+ 'WHERE l.gradeId<=' + STR(@gradeId) + ' AND l.groupId=' + STR(@groupId) + ' AND l.userId=' + STR(@userId) 
					+ ' AND l.gate<>' + STR(@gateId) + 'AND l.gradeId=g.pkId and l.userId=u.pkId ORDER BY l.isTop DESC, l.pkId DESC) TempTable1 '
					+ ' ORDER BY isTop ASC, pkId ASC) TempTable2 ORDER BY isTop DESC, pkId DESC'
	END

	--执行查询
	EXEC(@sqlStr)
RETURN

GO





/****** Object:  Stored Procedure dbo.GetMessageBoardProperty    Script Date: 2006-5-15 10:39:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetMessageBoardProperty]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetMessageBoardProperty]
GO



CREATE PROCEDURE dbo.GetMessageBoardProperty
AS
	SET NOCOUNT ON;
SELECT * FROM messageBoardProperty

GO





/****** Object:  Stored Procedure dbo.UpDateMessageBoardProperty    Script Date: 2006-5-15 10:39:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpDateMessageBoardProperty]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpDateMessageBoardProperty]
GO



CREATE PROCEDURE dbo.UpDateMessageBoardProperty 
(
	@pkId int,
	@allowAnonymityAppear int,
	@isAllowAnonymityAppearSecrecy int,
	@isAnonymityAuditing int,
	@isRegisterAuditing int,
	@isShowIp int,
	@suspendUseMessageBoard int,
	@isShowRespondFlag int,
	@showParticularListWidth int
)
AS
	SET NOCOUNT OFF;
UPDATE MessageBoardProperty SET 
	allowAnonymityAppear=@allowAnonymityAppear,
	isAllowAnonymityAppearSecrecy=@isAllowAnonymityAppearSecrecy,
	isAnonymityAuditing=@isAnonymityAuditing,
	isRegisterAuditing=@isRegisterAuditing,
	isShowIp=@isShowIp,
	suspendUseMessageBoard=@suspendUseMessageBoard,
	isShowRespondFlag=@isShowRespondFlag,
	showParticularListWidth=@showParticularListWidth

GO





/****** Object:  Stored Procedure dbo.GetMenuById    Script Date: 2006-5-15 10:39:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetMenuById]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetMenuById]
GO



CREATE PROCEDURE GetMenuById
@pkId int
/*
	(
		@parameter1 datatype = default value,
		@parameter2 datatype OUTPUT
	)
*/
AS
	/* SET NOCOUNT ON */
	SELECT * FROM menu where pkId=@pkId
	
	RETURN

GO





/****** Object:  Stored Procedure dbo.GetMessagesByPkId    Script Date: 2006-5-15 10:39:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetMessagesByPkId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetMessagesByPkId]
GO



CREATE PROCEDURE GetMessagesByPkId
@pkId int =null,
@gradeId int=1,
@gateId int
/*
	(
		@parameter1 datatype = default value,
		@parameter2 datatype OUTPUT
	)
*/
AS
	 SET NOCOUNT ON 
	select *
	from messageBoard
	where @pkId=pkId
	  and gradeId<=@gradeId
	  and gate<>@gateId
	RETURN

GO





/****** Object:  Stored Procedure dbo.InsertMessageBoard    Script Date: 2006-5-15 10:39:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertMessageBoard]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertMessageBoard]
GO


/* 创建过程/函数 InsertNews;1。                                                                      */
CREATE PROCEDURE dbo.InsertMessageBoard
(
	@pkId int,
	@title varchar(100),
	@checked int,
	@author varchar(100),
	@tel varchar(100),
	@isSecrecy int,
	@email varchar(100),
	@sex varchar(10),
	@homePage varchar(100),
	@Content ntext,
	@reContent ntext,
	@userId int,
	@updateTime datetime,
	@updateIp varchar(100),
	@click int,
	@isTop int,
	@MenuId int,
	@gradeId int,
	@bigClassId int,
	@gate int,
	@username varchar(100),
	@password varchar(50),
	@retime datetime
)
AS
	SET NOCOUNT OFF;	 BEGIN TRANSACTION
INSERT INTO messageBoard(
	title, 
	checked, 
	author,
	tel, 
	isSecrecy, 
	email, 
	sex, 
	homePage, 
	Content, 
	reContent, 
	userId, 
	updateTime, 
	updateIp, 
	click, 
	isTop, 
	MenuId, 
	gradeId, 
	bigClassId, 
	gate, 
	username, 
	password, 
	retime
) VALUES (
	@title, 
	@checked, 
	@author,
	@tel, 
	@isSecrecy, 
	@email, 
	@sex, 
	@homePage, 
	@Content, 
	@reContent, 
	@userId, 
	@updateTime, 
	@updateIp, 
	@click, 
	@isTop, 
	@MenuId, 
	@gradeId, 
	@bigClassId, 
	@gate, 
	@username, 
	@password, 
	@retime
)
  COMMIT TRANSACTION

GO





/****** Object:  Stored Procedure dbo.UpDateMessageBoard    Script Date: 2006-5-15 10:39:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[UpDateMessageBoard]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[UpDateMessageBoard]
GO


/* 创建过程/函数 UpdateNews;1。                                                                      */
CREATE PROCEDURE dbo.UpDateMessageBoard
(
	@pkId int,
	@title varchar(100),
	@checked int,
	@author varchar(100),
	@tel varchar(100),
	@isSecrecy int,
	@email varchar(100),
	@sex varchar(10),
	@homePage varchar(100),
	@Content ntext,
	@reContent ntext,
	@userId int,
	@updateTime datetime,
	@updateIp varchar(100),
	@click int,
	@isTop int,
	@MenuId int,
	@gradeId int,
	@bigClassId int,
	@gate int,
	@username varchar(100),
	@password varchar(50),
	@retime datetime
)
AS
	SET NOCOUNT OFF;
UPDATE messageBoard SET 
	title=@title,
	checked=@checked,
	author = @author, 
	tel = @tel, 
	isSecrecy = @isSecrecy, 
	email = @email, 
	sex = @sex, 
	homePage = @homePage, 
	Content = @Content, 
	reContent = @reContent, 
	userId = @userId, 
	updateTime = @updateTime, 
	updateIp = @updateIp, 
	click = @click, 
	isTop = @isTop, 
	MenuId = @MenuId, 
	gradeId = @gradeId, 
	bigClassId = @bigClassId, 
	gate = @gate, 
	username = @username, 
	password = @password, 
	retime = @retime

WHERE (pkId = @pkId)

GO





/****** Object:  Stored Procedure dbo.GetMessageBoardByBigClassId    Script Date: 2006-5-15 10:39:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetMessageBoardByBigClassId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetMessageBoardByBigClassId]
GO


/* 创建过程/函数 GetNewsByBigClassId;1。                                                             */
CREATE PROCEDURE GetMessageBoardByBigClassId
@bigClassId int=null,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@gateId int
/*
	(
		@parameter1 datatype = default value,
		@parameter2 datatype OUTPUT
	)
*/
AS
	 SET NOCOUNT ON
	declare @vSQL varchar(1000)
select @vSQL = 'select top ' + convert(varchar, @pageSize) + ' m.*,g.name "grade" from messageBoard m, grade g where  m.gate<>'+Convert(varchar,@gateId)+' and   m.bigClassId='+Convert(varchar,@bigClassId)+' and m.gradeId<='+convert(varchar,@gradeId)+' and m.gradeId=g.pkId and m.pkId <> all (select top '+Convert(varchar,((@currentPage-1)*@pageSize))+' pkId from messageBoard where   gate<>'+Convert(varchar,@gateId)+' and   bigClassId='+Convert(varchar,@bigClassId)+' and gradeId<='+convert(varchar,@gradeId)+' order by istop desc,pkId desc)  order by istop desc,m.pkId desc'
Execute (@vSQL)
	
	RETURN

GO





/****** Object:  Stored Procedure dbo.DeleteMessageBoard    Script Date: 2006-5-15 10:39:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DeleteMessageBoard]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteMessageBoard]
GO



CREATE PROCEDURE dbo.DeleteMessageBoard
(
	@pkId int
)
AS
	SET NOCOUNT OFF;
DELETE FROM messageBoard WHERE (pkId = @pkId)

GO





/****** Object:  Stored Procedure dbo.GetModuleAssignByusersIdAndBigClassId    Script Date: 2006-5-15 10:39:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetModuleAssignByusersIdAndBigClassId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetModuleAssignByusersIdAndBigClassId]
GO



CREATE PROCEDURE GetModuleAssignByusersIdAndBigClassId
@usersId int=null,
@bigClassId int
/*
	(
		@parameter1 datatype = default value,
		@parameter2 datatype OUTPUT
	)
*/
AS
	SET NOCOUNT ON
	select a.* 
	from moduleAssign a, menu m
	where m.pkId=a.menuId
	and a.usersId=@usersId
	and m.bigClassId=@bigClassId
	RETURN

GO





/****** Object:  Stored Procedure dbo.GetMessageBoardByBigClassIdAndPassed    Script Date: 2006-5-15 10:39:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetMessageBoardByBigClassIdAndPassed]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetMessageBoardByBigClassIdAndPassed]
GO


CREATE PROCEDURE dbo.GetMessageBoardByBigClassIdAndPassed
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@bigClassId int=null,
	@gradeid int=1,
	@gateId int,
	@passed int=1,
	@recordCount int output	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage-1	

	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(pkId) from messageBoard where  gate<>'+Convert(varchar,@gateId)+' and  bigClassId='+Convert(varchar,@bigClassId)+' and gradeId<='+convert(varchar,@gradeId)+' and checked='+convert(varchar,@passed)+' and isSecrecy =0'
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

	--计算页数
	SET @pageCount = (@recordCount +@pageSize-1)/@pageSize

	--构造查询语句
	DECLARE @sqlStr varchar(1000),@size int

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	
	
	--查询第一页
	IF (@currentPage = 0) OR (@currentPage > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' *  FROM messageBoard where gate<>'+Convert(varchar,@gateId)+' and  bigClassId='+Convert(varchar,@bigClassId)+' and gradeId<='+convert(varchar,@gradeId)+' and checked='+convert(varchar,@passed)+' and isSecrecy =0 ORDER BY isTop DESC, pkId DESC'	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' *  FROM messageBoard  where gate<>'+Convert(varchar,@gateId)+' and  bigClassId='+Convert(varchar,@bigClassId)+' and gradeId<='+convert(varchar,@gradeId)+' and checked='+convert(varchar,@passed)+' and isSecrecy =0 ORDER BY isTop ASC, pkId ASC) TempTable ORDER BY isTop DESC, pkId DESC'	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' * FROM messageBoard where gate<>'+Convert(varchar,@gateId)+' and  bigClassId='+Convert(varchar,@bigClassId)+' and gradeId<='+convert(varchar,@gradeId)+' and checked='+convert(varchar,@passed)+' and isSecrecy =0 ORDER BY isTop ASC, pkId ASC) TempTable ORDER BY isTop DESC, pkId DESC'
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' * FROM messageBoard where gate<>'+Convert(varchar,@gateId)+' and  bigClassId='+Convert(varchar,@bigClassId)+' and gradeId<='+convert(varchar,@gradeId)+' and checked='+convert(varchar,@passed)+' and isSecrecy =0 ORDER BY isTop DESC, pkId DESC) TempTable1 '
					+ ' ORDER BY isTop ASC, pkId ASC) TempTable2 ORDER BY isTop DESC, pkId DESC'
	END

	--执行查询
	EXEC(@sqlStr)

RETURN

GO





/****** Object:  Stored Procedure dbo.GetMessageBoardByBeforePkId    Script Date: 2006-5-15 10:39:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetMessageBoardByBeforePkId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetMessageBoardByBeforePkId]
GO


-- 根据pkId获取上一条留言
CREATE PROCEDURE GetMessageBoardByBeforePkId
	@pkId int =null,
	@gradeId int=1,
	@gateId int 
AS
	SET NOCOUNT ON 		
	SELECT TOP 1 * FROM messageBoard WHERE gradeId <= @gradeId AND gate <> @gateId AND pkId > @pkId 
	AND bigClassId = (SELECT bigClassId FROM messageBoard where pkId = @pkId) ORDER BY pkId ASC

GO





/****** Object:  Stored Procedure dbo.GetMessageBoardByAfterPkId    Script Date: 2006-5-15 10:39:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetMessageBoardByAfterPkId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetMessageBoardByAfterPkId]
GO


-- 根据pkId获取下一条留言
CREATE PROCEDURE GetMessageBoardByAfterPkId
	@pkId int =null,
	@gradeId int=1,
	@gateId int 
AS
	SET NOCOUNT ON 		
	SELECT TOP 1 * FROM messageBoard WHERE gradeId <= @gradeId AND gate <> @gateId AND pkId < @pkId 
	AND bigClassId = (SELECT bigClassId FROM messageBoard where pkId = @pkId) ORDER BY pkId desc

GO





/****** Object:  Stored Procedure dbo.GetMessageBoardByBeforePkIdAndPassed    Script Date: 2006-5-15 10:39:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetMessageBoardByBeforePkIdAndPassed]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetMessageBoardByBeforePkIdAndPassed]
GO


-- 根据pkId获取上一条留言
CREATE PROCEDURE GetMessageBoardByBeforePkIdAndPassed
	@pkId int =null,
	@gradeId int=1,
	@gateId int 
AS
	SET NOCOUNT ON 		
	SELECT TOP 1 * FROM messageBoard WHERE gradeId <= @gradeId AND gate <> @gateId AND pkId > @pkId 
	and checked=1 and isSecrecy=0 AND bigClassId = (SELECT bigClassId FROM messageBoard where pkId = @pkId) ORDER BY pkId ASC

GO





/****** Object:  Stored Procedure dbo.GetMessageBoardByAfterPkIdAndPassed    Script Date: 2006-5-15 10:39:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetMessageBoardByAfterPkIdAndPassed]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetMessageBoardByAfterPkIdAndPassed]
GO


-- 根据pkId获取下一条留言
CREATE PROCEDURE GetMessageBoardByAfterPkIdAndPassed
	@pkId int =null,
	@gradeId int=1,
	@gateId int 
AS
	SET NOCOUNT ON 		
	SELECT TOP 1 * FROM messageBoard WHERE gradeId <= @gradeId AND gate <> @gateId AND pkId < @pkId 
	and checked=1 and isSecrecy=0  AND bigClassId = (SELECT bigClassId FROM messageBoard where pkId = @pkId) ORDER BY pkId desc

GO





/****** Object:  Stored Procedure dbo.GetMessageBoardByNameAndPwd    Script Date: 2006-5-15 10:39:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetMessageBoardByNameAndPwd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetMessageBoardByNameAndPwd]
GO



CREATE PROCEDURE GetMessageBoardByNameAndPwd
	@username varchar(100),
	@password varchar(50)
AS
	SET NOCOUNT ON 		
	SELECT * FROM messageBoard WHERE username=@username and password=@password

GO





/****** Object:  Stored Procedure dbo.GetLatestMessageBoardByPassed    Script Date: 2006-5-15 10:39:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetLatestMessageBoardByPassed]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetLatestMessageBoardByPassed]
GO


/* 创建过程/函数 GetNewsByBigClassIdAndPassed;1。                                                    */
CREATE PROCEDURE GetLatestMessageBoardByPassed

@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@passed int=1,
@gateId int
/*
	(
		@parameter1 datatype = default value,
		@parameter2 datatype OUTPUT
	)
*/
AS
	 SET NOCOUNT ON 
	
	declare @vSQL varchar(1000)
select @vSQL = 'select top ' + convert(varchar, @pageSize) + ' m.*, b.name "BigClassName"  from messageBoard m, bigClass b where m.bigClassId=b.pkId and m.gate<>'+Convert(varchar,@gateId)+' and  m.gradeId<='+convert(varchar,@gradeId)+' and m.checked='+convert(varchar,@passed)+' and m.isSecrecy =0  and m.pkId < all (select top '+Convert(varchar,((@currentPage-1)*@pageSize))+' pkId from messageBoard where  gate<>'+Convert(varchar,@gateId)+' and   gradeId<='+convert(varchar,@gradeId)+' and checked='+convert(varchar,@passed)+' and isSecrecy=0 order by istop desc,pkId desc)  order by m.istop desc,m.pkId desc'
Execute (@vSQL)
	
	RETURN

GO





/****** Object:  Stored Procedure dbo.GetMessageBoardBySearch    Script Date: 2006-5-15 10:39:39 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetMessageBoardBySearch]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetMessageBoardBySearch]
GO



CREATE PROCEDURE GetMessageBoardBySearch
@bigClassId int,
@isManage int=0,
@gradeId int=1,
@gateId int

AS
	DECLARE @sqlStr varchar(1000)
	IF (@isManage = 0)
	 	SET @sqlStr = 'select *	 from messageBoard where checked=1 and IsSecrecy=0 and gradeId<='+Convert(varchar,@gradeId)+' and gate<>'+Convert(varchar,@gateId)+' and bigClassId='+Convert(varchar,@bigClassId)+' order by isTop desc,  pkId desc'

	 ELSE 
		SET @sqlStr = 'select *	 from messageBoard where gradeId<='+Convert(varchar,@gradeId)+' and gate<>'+Convert(varchar,@gateId)+' and bigClassId='+Convert(varchar,@bigClassId)+' order by isTop desc,  pkId desc'
	
	 
	EXEC(@sqlStr)

GO





/****** Object:  Stored Procedure dbo.GetNewsByBigClassIdCount    Script Date: 2006-5-15 10:39:40 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetNewsByBigClassIdCount]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetNewsByBigClassIdCount]
GO


/* 创建过程/函数 GetNewsByBigClassIdCount;1。                                                        */
CREATE PROCEDURE GetNewsByBigClassIdCount
@bigClassId int=null,
@gradeId int=1,
@gateId int=-1,
@table varchar(100)=null,
@count int output
/*
	(
		@parameter1 datatype = default value,
		@parameter2 datatype OUTPUT
	)
*/
AS
	 SET NOCOUNT ON
   declare @num int
   declare @vSQL varchar(1000)
  select @vSQL=
   case @table
      when 'users' then 'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from users return'
      when 'popwindow' then  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from '+convert(varchar,@table)+' where gate<> '+convert(varchar,@gateId)+' and gradeId<='+convert(varchar,@gradeId)+' return'
      when 'newss' then  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and (specialId1='+convert(varchar,@bigclassId)+' or specialId2='+convert(varchar,@bigclassId)+' )   and  gradeId<='+convert(varchar,@gradeId)+' return'
      when 'picNews'then  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and  bigclassId='+convert(varchar,@bigclassId)+'  and isImageNews=1 and gradeId<='+convert(varchar,@gradeId)+' return'
       when 'picNews1'then  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and  bigclassId='+convert(varchar,@bigclassId)+'  and isImageNews=1 and gradeId<='+convert(varchar,@gradeId)+' return'--李聪杰20060406
      when 'picNews2'then  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from news where gate<> '+convert(varchar,@gateId)+' and  bigclassId='+convert(varchar,@bigclassId)+'  and isImageNews=1 and gradeId<='+convert(varchar,@gradeId)+' return'--李聪杰20060406	
      ELSE  'ALTER  procedure GetCount @count int output as set nocount on select @count=Count(*) from '+convert(varchar,@table)+' where gate<> '+convert(varchar,@gateId)+' and bigclassId='+convert(varchar,@bigclassId)+' and  gradeId<='+convert(varchar,@gradeId)+' return'
   end
Execute (@vSQL)
execute GetCount @count=@num output
select @count=@num
	RETURN

GO





/****** Object:  Stored Procedure dbo.DeleteUsers    Script Date: 2006-5-15 10:39:40 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[DeleteUsers]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[DeleteUsers]
GO


/* 创建过程/函数 DeleteUsers;1。                                                                     */
CREATE PROCEDURE dbo.DeleteUsers
(
	@pkId int
)
AS
	SET NOCOUNT OFF;
 BEGIN TRANSACTION
DELETE FROM moduleAssign where(usersId=@pkId)
DELETE FROM riches where (usersId=@pkId)
DELETE FROM news WHERE (usersId = @pkId)
DELETE FROM popwindow WHERE (userId = @pkId)
delete from  board where (userId=@pkId)
DELETE FROM link WHERE (userId = @pkId)
DELETE FROM people WHERE (userId = @pkId)
DELETE FROM ad WHERE (userId = @pkId)
DELETE FROM linkGang WHERE (userId=@pkId)
DELETE FROM linkGroup WHERE (userId=@pkId)
DELETE FROM messageBoard WHERE (userId=@pkId)
DELETE FROM Users WHERE (pkId = @pkId)
         COMMIT TRANSACTION

GO





/****** Object:  Stored Procedure dbo.InsertMessageBoardProperty    Script Date: 2006-5-15 10:39:40 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertMessageBoardProperty]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertMessageBoardProperty]
GO


/* 创建过程/函数 InsertAd;1。                                                                        */
CREATE PROCEDURE dbo.InsertMessageBoardProperty
(
    	@pkId int,
	@allowAnonymityAppear int,
	@isAllowAnonymityAppearSecrecy int,
	@isAnonymityAuditing int,
	@isRegisterAuditing int,
	@isShowIp int,
	@suspendUseMessageBoard int,
	@isShowRespondFlag int,
	@showParticularListWidth int
)
AS
	SET NOCOUNT ON;
INSERT INTO messageBoardProperty
(
	allowAnonymityAppear,
	isAllowAnonymityAppearSecrecy,
	isAnonymityAuditing, 
	isRegisterAuditing,
	isShowIp,
	suspendUseMessageBoard, 
	isShowRespondFlag,
	showParticularListWidth
) VALUES ( 
	@allowAnonymityAppear,
	 @isAllowAnonymityAppearSecrecy,
	 @isAnonymityAuditing, 
	@isRegisterAuditing,
	@isShowIp,
	 @suspendUseMessageBoard, 
	@isShowRespondFlag,
	 @showParticularListWidth
)

GO





/****** Object:  Stored Procedure dbo.GetLayoutTemplateByBigClassId    Script Date: 2006-5-15 10:39:40 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetLayoutTemplateByBigClassId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetLayoutTemplateByBigClassId]
GO



CREATE PROCEDURE GetLayoutTemplateByBigClassId
@bigClassId int
/*
	(
		@parameter1 datatype = default value,
		@parameter2 datatype OUTPUT
	)
*/
AS
	/* SET NOCOUNT ON */
	select l.*
	      from layoutTemplate l,menu m
	where m.bigClassId=@bigClassId
	  and m.layoutTemplateId=l.pkId
	RETURN

GO





/****** Object:  Stored Procedure dbo.GetLinkGangByPkId    Script Date: 2006-5-15 10:39:40 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetLinkGangByPkId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetLinkGangByPkId]
GO



CREATE PROCEDURE GetLinkGangByPkId
@pkId int=null,
@gradeId int=1,
@gateId int

AS
	SET NOCOUNT ON
	select l.*,g.name 'grade'
	from linkGang l,grade g
	where l.pkId = @pkId
	   and l.gradeId <= @gradeId
	   and l.gradeId = g.pkId
	   and l.gate <> @gateId
	
	RETURN



GO





/****** Object:  Stored Procedure dbo.GetLinkGroupByBigClassId    Script Date: 2006-5-15 10:39:40 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetLinkGroupByBigClassId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetLinkGroupByBigClassId]
GO



CREATE PROCEDURE dbo.GetLinkGroupByBigClassId
@bigClassId int=null,
@currentPage int=1,
@pageSize int=7,
@gradeid int=1,
@gateId int

AS
	SET NOCOUNT ON
	 
	
	declare @vSQL varchar(1000)
	select @vSQL = 'select top ' + convert(varchar, @pageSize) + 
   		' l.*,u.userName "userName",g.name "grade" from linkGroup l,users u,grade g where  l.gate<>'+Convert(varchar,@gateId) + 
		' and l.isShow = 1' + 
		' and l.bigClassId=' + Convert(varchar,@bigClassId) +
		' and l.gradeId<=' + convert(varchar,@gradeId) +
		' and l.gradeId=g.pkId and l.userId=u.pkId  and l.pkId <> all (select top ' + Convert(varchar,((@currentPage-1)*@pageSize)) + ' pkId from linkGroup  where bigClassId=' + Convert(varchar,@bigClassId) + 
		' and gradeId<=' + convert(varchar,@gradeId) + ' order by ordering desc,pkId desc)  order by ordering desc,l.pkId desc'
	
	Execute (@vSQL)
	
	RETURN 




GO





/****** Object:  Stored Procedure dbo.GetLinkGroupByBigClassIdAndUserId    Script Date: 2006-5-15 10:39:40 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetLinkGroupByBigClassIdAndUserId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetLinkGroupByBigClassIdAndUserId]
GO



CREATE PROCEDURE dbo.GetLinkGroupByBigClassIdAndUserId
@bigClassId int=null,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@userId int,
@gateId int

AS
	SET NOCOUNT ON
	 
	declare @vSQL varchar(1000)
	select @vSQL = 'select top ' + convert(varchar, @pageSize) + 
	' l.*,u.username "userName",g.name "grade" from linkGroup l,users u,grade g where   l.gate<>' + Convert(varchar,@gateId) + 
	' and   l.bigClassId=' + Convert(varchar,@bigClassId) + 
	' and l.gradeId<=' + convert(varchar,@gradeId) + 
	' and l.userId=' + convert(varchar,@userId) + 
	' and l.gradeId=g.pkId and l.userId=u.pkId  and l.pkId <> all (select top ' + Convert(varchar,((@currentPage-1)*@pageSize))+' pkId from linkGroup where bigClassId='+Convert(varchar,@bigClassId)+
	' and userId=' + convert(varchar,@userId) + 
	' and  gradeId<=' + convert(varchar,@gradeId) + 
	' order by ordering desc,pkId desc)  order by ordering desc,l.pkId desc'
	
	Execute (@vSQL)
	
	RETURN


GO





/****** Object:  Stored Procedure dbo.GetLinkGroupByPkId    Script Date: 2006-5-15 10:39:40 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetLinkGroupByPkId]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetLinkGroupByPkId]
GO



CREATE PROCEDURE GetLinkGroupByPkId
@pkId int=null,
@gradeId int=1,
@gateId int

AS
	SET NOCOUNT ON
	select l.*,g.name  'grade'
	  from linkGroup l,grade g
	where l.pkId=@pkId
	   and l.gradeId<=@gradeId
	   and l.gradeId=g.pkId
	    and l.gate<>@gateId
	
	RETURN


GO




/****** 对象: 存储过程 dbo.InsertLinkGang    脚本日期: 2006-7-28 10:50:35 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertLinkGang]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertLinkGang]
GO



CREATE PROCEDURE dbo.InsertLinkGang
(
	@name varchar(100),
	@pic varchar(100),
	@url varchar(100),
	@ordering int,
	@isTop int,
	@userId int,
	@updateTime datetime,
	@updateIp varchar(100),
	@passed int,
	@gradeId int,
	@gate int,
	@groupId int,
	@menuId int,
	@IsPicLink int,
	@pkId int ,
	@height int,
	@width int
)
AS
	SET NOCOUNT OFF;
BEGIN TRANSACTION
INSERT INTO linkGang(height,width,name, pic, url, ordering, isTop, userId, updateTime, updateIp, passed, gradeId, gate, groupId, menuId, IsPicLink) VALUES (@height,@width,@name, @pic, @url, @ordering, @isTop, @userId, @updateTime, @updateIp, @passed, @gradeId, @gate, @groupId, @menuId, @IsPicLink)

  COMMIT TRANSACTION




GO





/****** 对象: 存储过程 dbo.InsertLinkGroup    脚本日期: 2006-7-28 10:50:35 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[InsertLinkGroup]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[InsertLinkGroup]
GO



CREATE PROCEDURE dbo.InsertLinkGroup
(
	@groupName varchar(100),
	@remark varchar(100),
	@ordering int,
	@userId int,
	@updateTime datetime,
	@updateIp varchar(100),
	@isShow int,
	@gradeId int,
	@gate int,
	@bigClassId int,
	@menuId int,
	@pkId int

)
AS
	SET NOCOUNT OFF;
BEGIN TRANSACTION
INSERT INTO linkGroup(groupName, remark, ordering, userId, updateTime, updateIp, isShow, gradeId, gate, bigClassId, menuId) 
VALUES (@groupName, @remark, @ordering, @userId, @updateTime, @updateIp, @isShow, @gradeId, @gate, @bigClassId, @menuId)

  COMMIT TRANSACTION




GO


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

CREATE PROCEDURE [dbo].[DeleteLinkGang]
(
	@pkId int
)
AS
	SET NOCOUNT OFF;
	begin transaction
	DELETE FROM linkGang where (pkId = @pkId)
	commit transaction

GO



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

CREATE PROCEDURE [dbo].[DeleteLinkGroup]
(
	@pkId int
)
AS
	SET NOCOUNT OFF;
	begin transaction
	DELETE FROM linkGang where (groupId = @pkId)
	DELETE FROM linkGroup WHERE (pkId = @pkId)
	commit transaction

GO


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

CREATE PROCEDURE [dbo].[InsertLinkGang]
(
	@name varchar(100),
	@pic varchar(100),
	@url varchar(100),
	@ordering int,
	@isTop int,
	@userId int,
	@updateTime datetime,
	@updateIp varchar(100),
	@passed int,
	@gradeId int,
	@gate int,
	@groupId int,
	@menuId int,
	@IsPicLink int,
	@pkId int ,
	@height int,
	@width int
)
AS
	SET NOCOUNT OFF;
BEGIN TRANSACTION
INSERT INTO linkGang(height,width,name, pic, url, ordering, isTop, userId, updateTime, updateIp, passed, gradeId, gate, groupId, menuId, IsPicLink) VALUES (@height,@width,@name, @pic, @url, @ordering, @isTop, @userId, @updateTime, @updateIp, @passed, @gradeId, @gate, @groupId, @menuId, @IsPicLink)

  COMMIT TRANSACTION

GO

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

CREATE PROCEDURE [dbo].[InsertLinkGroup]
(
	@groupName varchar(100),
	@remark varchar(100),
	@ordering int,
	@userId int,
	@updateTime datetime,
	@updateIp varchar(100),
	@isShow int,
	@gradeId int,
	@gate int,
	@bigClassId int,
	@menuId int,
	@pkId int

)
AS
	SET NOCOUNT OFF;
BEGIN TRANSACTION
INSERT INTO linkGroup(groupName, remark, ordering, userId, updateTime, updateIp, isShow, gradeId, gate, bigClassId, menuId) 
VALUES (@groupName, @remark, @ordering, @userId, @updateTime, @updateIp, @isShow, @gradeId, @gate, @bigClassId, @menuId)

  COMMIT TRANSACTION

GO

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

CREATE PROCEDURE [dbo].[UpdateLinkGang]
(
	@name varchar(100),
	@pic varchar(100),
	@url varchar(100),
	@ordering int,
	@isTop int,
	@userId int,
	@updateTime datetime,
	@updateIp varchar(100),
	@passed int,
	@gradeId int,
	@gate int,
	@groupId int,
	@menuId int,
	@IsPicLink int,
	@pkId int,
	@height int,
	@width int
)
AS
	SET NOCOUNT OFF;
UPDATE linkGang SET height=@height,width=@width, name = @name, pic = @pic, url = @url, ordering = @ordering, isTop = @isTop, userId = @userId, updateTime = @updateTime, updateIp = @updateIp, passed = @passed, gradeId = @gradeId, gate = @gate, groupId = @groupId, menuId = @menuId, IsPicLink = @IsPicLink WHERE (pkId = @pkId)


GO

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

CREATE PROCEDURE [dbo].[UpdateLinkGroup]
(
	@groupName varchar(100),
	@remark varchar(100),
	@ordering int,
	@userId int,
	@updateTime datetime,
	@updateIp varchar(100),
	@isShow int,
	@gradeId int,
	@gate int,
	@bigClassId int,
	@menuId int,
	@pkId int

)
AS
	SET NOCOUNT OFF;
BEGIN TRANSACTION
update linkGroup set 
	groupName = @groupName, 
	remark = @remark, 
	ordering = @ordering, 
	userId = @userId, 
	updateTime = @updateTime, 
	updateIp = @updateIp, 
	isShow = @isShow, 
	gradeId = @gradeId, 
	gate = @gate, 
	bigClassId = @bigClassId, 
	menuId = @menuId
where pkId = @pkId

COMMIT TRANSACTION

GO