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

CREATE PROCEDURE [dbo].[GetBigClassForDuplicatedBoard]
(
	@pkId int
)	
AS
	SET NOCOUNT ON
	select b.pkId, b.name from bigClass b, board n
	where b.pkId = n.bigClassId and n.duplicatedPkId = @pkId and n.duplicateFrom='board'
	RETURN
GO

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

CREATE PROCEDURE [dbo].[GetBigClassForDuplicatedNews]
(
	@pkId int
)
AS
	SET NOCOUNT ON
	select b.pkId, b.name from bigClass b, news n
	where b.pkId = n.bigClassId and n.duplicatedPkId = @pkId and n.duplicateFrom='news' 
	union all 
	select b.pkId, b.name from bigClass b, board n
	where b.pkId = n.bigClassId and n.duplicatedPkId = @pkId and n.duplicateFrom='news'
	RETURN
GO

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

CREATE PROCEDURE [dbo].[DeleteDuplicatedInfoByBoardIdAndBigClassId]
(
	@pkId int,
	@bigClassId int
)
AS
	SET NOCOUNT OFF	
	BEGIN TRANSACTION	
	DELETE FROM board WHERE (duplicatedPkId=@pkId) and duplicateFrom='board' and bigClassId = @bigClassId	
	COMMIT TRANSACTION
GO


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

CREATE PROCEDURE [dbo].[DeleteDuplicatedInfoByNewsIdAndBigClassId]
(
	@pkId int,
	@bigClassId int
)
AS
	SET NOCOUNT OFF
	
	BEGIN TRANSACTION
	
	DECLARE @duplicatedId int

	-- 查询被抄送新闻并删除其对应评论
	SELECT @duplicatedId=pkId FROM news WHERE duplicatedPkId=@pkId and bigClassId = @bigClassId	
	DELETE FROM review WHERE (newsId=@duplicatedId)		

	-- 从新闻、公告中删除抄送新闻
	DELETE FROM news WHERE (duplicatedPkId=@pkId) and bigClassId = @bigClassId	
	DELETE FROM board WHERE (duplicatedPkId=@pkId) and duplicateFrom='news' and bigClassId = @bigClassId	
	
	COMMIT TRANSACTION
GO

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

CREATE PROCEDURE [dbo].[GetNewsByBigClassIdAndDuplicatedPkId]
(
	@bigClassId int,
	@duplicatedPkId int,
	@duplicatedFrom varchar(100)
)
AS
	SET NOCOUNT ON
	SELECT * FROM news WHERE bigClassId = @bigClassId AND duplicatedPkId = @duplicatedPkId AND duplicateFrom = @duplicatedFrom
	RETURN 
GO

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

CREATE PROCEDURE [dbo].[GetBoardByBigClassIdAndDuplicatedPkId]
(
	@bigClassId int,
	@duplicatedPkId int,	
	@duplicatedFrom varchar(100)
)
AS
	SET NOCOUNT ON
	SELECT * FROM board WHERE bigClassId = @bigClassId AND duplicatedPkId = @duplicatedPkId AND duplicateFrom = @duplicatedFrom
	RETURN 
GO

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

CREATE  PROCEDURE [dbo].[GetAuditingNewsByUserId]
	@currentPage int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@gradeId int,
	@userId int,
	@gateId int,
	@manageAll int,
	@recordCount int output
	
AS	
	--转换成索引
	SELECT @currentPage = @currentPage - 1
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(1000)
	SET @str = ' 1=1 '
	SET @str = @str + ' and (n.isDuplicated=0 or n.isDuplicated is null) and b.pkId = n.bigClassId and b.templateId = t.pkId and n.bigClassId in (select b.pkId from bigClass b, menu m, moduleAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and usersId='+STR(@userId)+')'
	IF (@manageAll<>1)
		SET @str = @str + ' and n.usersId='+STR(@userId)
	SET @str = @str + ' and n.passed=0 and n.gradeId<='+STR(@gradeId)
	SET @str = @str + ' and n.gate<>'+STR(@gateId)
	SET @str = @str + ' and n.MenuId=m.pkId and n.usersId=u.pkId and n.gradeId=g.pkId and n.departmentId=d.pkId '

	--设置查询表名称
	DECLARE @tableName nvarchar(100)
	SET @tableName = ' news n,users u,department d,grade g, menu m, template t, bigClass b '

	--设置查询排序（不带order by）
	DECLARE @orderTypeA nvarchar(100)
	DECLARE @orderTypeD nvarchar(100)
	SET @orderTypeA = 'menuName ASC, newsId ASC'
	SET @orderTypeD = 'menuName DESC, newsId DESC'
	
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(n.pkId) from '+Convert(nvarchar(100),@tableName)+' where ' + Convert(nvarchar(1000),@str)
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum

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

	--构造查询语句
	DECLARE @sqlStr varchar(2000),@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) + ' n.*,u.userName,d.name "department" ,g.name "grade",m.name "menuName", n.pkId as newsId , t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(1000),@str)+' and (n.isDuplicated is null or n.isDuplicated =0 ) and b.pkId = n.bigClassId and b.templateId = t.pkId ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询最后一页
	ELSE IF @currentPage = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' n.*,u.userName,d.name "department" ,g.name "grade",m.name "menuName", n.pkId as newsId , t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)	
	--查询中间页后面的页
	ELSE IF @currentPage > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @currentPage ) + ' n.*,u.userName,d.name "department" ,g.name "grade",m.name "menuName", n.pkId as newsId, t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @currentPage=@currentPage+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @currentPage ) + ' n.*,u.userName,d.name "department" ,g.name "grade",m.name "menuName", n.pkId as newsId, t.code FROM '+Convert(nvarchar(100),@tableName)+' where '+Convert(nvarchar(1000),@str)+' ORDER BY '+Convert(nvarchar(100),@orderTypeD)+') TempTable1 '
					+ ' ORDER BY '+Convert(nvarchar(100),@orderTypeA)+') TempTable2 ORDER BY '+Convert(nvarchar(100),@orderTypeD)
	END
	--执行查询
	EXEC(@sqlStr)
RETURN

GO

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

CREATE  PROCEDURE [dbo].[GetAuditingCount]
	@gradeId int,
	@userId int,
	@gateId int,
	@manageAll int,
	@newsCount int output,
	@reviewsCount int output,
	@messageBoardCount int output
	
AS
	
	--初始化查询条件(不带where)
	DECLARE @str nvarchar(1000)
	DECLARE @str1 nvarchar(1000)
	DECLARE @str3 nvarchar(1000)
	--新闻
	SET @str = ' 1=1 '
	SET @str = @str + '  and (n.isDuplicated=0 or n.isDuplicated is null) and n.bigClassId in (select b.pkId from bigClass b, menu m, moduleAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and a.usersId='+STR(@userId)+')'
	IF (@manageAll<>1)
		SET @str = @str + ' and n.usersId='+STR(@userId)
	SET @str = @str + ' and n.passed=0 and n.gradeId<='+STR(@gradeId)
	SET @str = @str + ' and n.gate<>'+STR(@gateId)
	SET @str = @str + ' and n.usersId=u.pkId'
	SET @str = @str + ' and n.MenuId=m.pkId and n.bigClassId=b.pkId'
	SET @str = @str + ' and n.gradeId=g.pkId and n.departmentId=d.pkId'
	--评论
	SET @str1 = ' 1=1 '
	SET @str1 = @str1 + ' and n.bigClassId in (select b.pkId from bigClass b, menu m, moduleAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and a.usersId='+STR(@userId)+')'
	IF (@manageAll<>1)
		SET @str1 = @str1 + ' and n.usersId='+STR(@userId)
	SET @str1 = @str1 + ' and r.passed=0 and n.gradeId<='+STR(@gradeId)
	SET @str1 = @str1 + ' and n.gate<>'+STR(@gateId)
	SET @str1 = @str1 + ' and r.newsId=n.pkId and n.usersId=u.pkId'
	--留言本
	SET @str3 = ' 1=1 '
	SET @str3 = @str3 + ' and bigClassId in (select b.pkId from bigClass b, menu m, moduleAssign a where m.bigClassId=b.pkId and m.pkId=a.menuId and a.usersId='+STR(@userId)+')'
	--IF (@manageAll<>1)
		--SET @str3 = @str3+ ' and n.usersId='+STR(@userId)
	SET @str3 = @str3 + ' and checked=0 and gradeId<='+STR(@gradeId)
	SET @str3 = @str3 + ' and gate<>'+STR(@gateId)

	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count1 = Count(n.pkId) from news n , users u, menu m, department d,grade g, bigClass b where ' + Convert(nvarchar(1000),@str)
	EXEC sp_ExecuteSQL @vSQL, N'@count1 int output', @tempNum output
	SELECT @newsCount = @tempNum

	SET @vSQL = N'SELECT @count2 = Count(*) from (select Count(n.pkId) as recordCount from review r, news n, users u where ' + Convert(nvarchar(1000),@str1) + ' group by r.newsid) TempTable'
	EXEC sp_ExecuteSQL @vSQL, N'@count2 int output', @tempNum output
	SELECT @reviewsCount = @tempNum

	SET @vSQL = N'SELECT @count3 = Count(pkId) from messageBoard where ' + Convert(nvarchar(1000),@str3)
	EXEC sp_ExecuteSQL @vSQL, N'@count3 int output', @tempNum output
	SELECT @messageBoardCount = @tempNum	

RETURN
GO

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 [dbo].[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 (isDuplicated=0 or isDuplicated is null) 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.isDuplicated=0 or n.isDuplicated is null) 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 n.updateTime 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.isDuplicated=0 or n.isDuplicated is null) 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 n.updateTime ASC, n.pkId ASC) TempTable ORDER BY updateTime 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.isDuplicated=0 or n.isDuplicated is null) 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 n.updateTime ASC, n.pkId ASC) TempTable ORDER BY updateTime 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.isDuplicated=0 or n.isDuplicated is null) 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 updateTime DESC, pkId DESC) TempTable1 ORDER BY n.updateTime ASC, n.pkId ASC) TempTable2 ORDER BY updateTime DESC, pkId DESC'
	END
	--执行查询
	EXEC(@sqlStr)
GO

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

CREATE PROCEDURE [dbo].[GetNewsByClick]
@pageSize int=5,
@gradeid int=1,
@gateId int
AS
	 SET NOCOUNT ON 
	
	declare @vSQL varchar(1000)
select @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.isDuplicated=0 or n.isDuplicated is null) and n.usersId=u.pkId and n.departmentId=d.pkId and n.gradeId<='+convert(varchar,@gradeId)+' and n.passed=1  order by n.click desc'
Execute (@vSQL)	
	RETURN 

GO

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

CREATE PROCEDURE [dbo].[GetNewsBySpecialAndpassed]
@special int=null,
@currentPage int=1,
@pageSize int=5,
@gradeid int=1,
@passed int=1,
@gateId int
AS
	 SET NOCOUNT ON 
	
	declare @vSQL varchar(1000)
select @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  (specialId='+Convert(varchar,@special)
	+') and n.usersId=u.pkId and (n.isDuplicated=0 or n.isDuplicated is null) and n.departmentId=d.pkId and n.gradeId<='
	+convert(varchar,@gradeId)+' and n.passed='+convert(varchar,@passed)
	+'  and n.pkId < all (select top '+Convert(varchar,((@currentPage-1)*@pageSize))
	+' pkId from news where     gate<>'+Convert(varchar,@gateId)+' and (specialId='
	+Convert(varchar,@special)+')  and gradeId<='+convert(varchar,@gradeId)+' and isDuplicated<>1 and passed='
	+convert(varchar,@passed)+' order by istop desc,pkId desc)  order by istop desc,n.pkId desc'
Execute (@vSQL)
	
	RETURN 

GO

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

CREATE PROCEDURE [dbo].[InsertAspProperty]
	@url varchar(100),
	@space int,
	@usedImagesSpace float,
	@usedBackupSpace float,
	@usedSpace float,
	@buildByAsp varchar(200),
	@pkId int
AS

SET NOCOUNT OFF;

INSERT INTO [dbo].[AspProperty] (
	url,
	space,
	usedImagesSpace,
	usedBackupSpace,
	usedSpace,
	buildByAsp
) VALUES (
	@url,
	@space,
	@usedImagesSpace,
	@usedBackupSpace,
	@usedSpace,
	@buildByAsp
)
GO

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

CREATE PROCEDURE [dbo].[UpdateAspProperty]
	@pkId int,
	@url varchar(100),
	@space int,
	@usedImagesSpace float,
	@usedBackupSpace float,
	@usedSpace float,
	@buildByAsp varchar(200)
AS

SET NOCOUNT OFF;

UPDATE [dbo].[AspProperty] SET
	url = @url,
	space = @space,
	usedImagesSpace = @usedImagesSpace,
	usedBackupSpace = @usedBackupSpace,
	usedSpace = @usedSpace,
	buildByAsp = @buildByAsp
WHERE
	pkId = @pkId
GO