SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE [dbo].[TasksInsert]	
	@PKID int output,
	@Title nvarchar(500),
	@File nvarchar(1000),
	@Status int,
	@Comment ntext,
	@JoinTime datetime,
	@PassTime datetime,
	@OverTime datetime,
	@UserID int,
	@PassUserID int,
	@OverUserID int,
	@BigClassID int
AS
BEGIN
	
	SET NOCOUNT ON;

    INSERT INTO [Tasks]
(
	[Title],
	[DocFile],
	[Status],
	[Comment],
	[JoinTime],
	[PassTime],
	[OverTime],
	[UserID],
	[PassUserID],
	[OverUserID],
	[BigClassID]
)
 VALUES (
	@Title,
	@File,
	@Status,
	@Comment,
	@JoinTime,
	@PassTime,
	@OverTime,
	@UserID,
	@PassUserID,
	@OverUserID,
	@BigClassID
)

SELECT @PKID = scope_identity()
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE [dbo].[TasksUpdate]	
	@PKID int,
	@Title nvarchar(500),
	@File nvarchar(1000),
	@Status int,
	@Comment ntext,
	@JoinTime datetime,
	@PassTime datetime,
	@OverTime datetime,
	@UserID int,
	@PassUserID int,
	@OverUserID int,
	@BigClassID int
AS
BEGIN
	
	SET NOCOUNT ON;

    UPDATE [Tasks] SET 

	[Title] = @Title,
	[DocFile] = @File,
	[Status] = @Status,
	[Comment] = @Comment,
	[JoinTime] = @JoinTime,
	[PassTime] = @PassTime,
	[OverTime] = @OverTime,
	[UserID] = @UserID,
	[PassUserID] = @PassUserID,
	[OverUserID] = @OverUserID,
	[BigClassID] = @BigClassID

WHERE [PKID] = @PKID

END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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



CREATE PROCEDURE [dbo].[TasksList]	
	@PageIndex int,
	@PageSize int,
	@Condition char(200),
	@Sequence char(100),
	@SequenceHelp char(100),	
	@RecordCount int output,
	@PageCount int output
AS
	DECLARE @vSQL nvarchar(2000)
	DECLARE @_where nvarchar(200)
	DECLARE @_order nvarchar(50)
	DECLARE @_orderHelp nvarchar(50)
	DECLARE @tempNum int	
	
	IF @Condition = ''
		SET @_where = ''
	ELSE
		SET @_where = ' WHERE ' + @Condition

	IF @Sequence = ''
		BEGIN
			SET @_order = ' ORDER BY [PKID] DESC'
			SET @_orderHelp = ' ORDER BY [PKID] ASC'
		END
	ELSE
		BEGIN
			SET	@_order = ' ORDER BY ' + @Sequence
			SET @_orderHelp = ' ORDER BY ' + @SequenceHelp
		END
	
	SET @vSQL = N'SELECT @count = Count(*) FROM [Tasks]' + @_where
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output

	SELECT @RecordCount = @tempNum	
	SELECT @PageIndex = @PageIndex - 1
	SET @PageCount = (@RecordCount + @PageSize-1)/@PageSize

	DECLARE @sqlStr varchar(2000),@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) + ' * FROM [Tasks] ' + @_where + @_order
	ELSE IF @pageIndex = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' * FROM [Tasks] ' + @_where + @_orderHelp + ') TempTable ' + @_order	
	ELSE IF @pageIndex > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @pageIndex ) + ' * FROM [Tasks] ' + @_where + @_orderHelp + ') TempTable ' + @_order
	ELSE 
		BEGIN							
			SET @pageIndex=@pageIndex+1
			SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @pageIndex ) + ' * FROM [Tasks]' + @_where + @_order + ') TempTable1 ' + @_orderHelp + ' ) TempTable2 ' + @_order
		END
	EXEC(@sqlStr)
	RETURN
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
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 ,
	@menuId int
AS	
	DECLARE @num int
	DECLARE @condition varchar(100)
	select @num=0
	select @num = count(pkId) from BigClassAssociation where menuId = @menuId
	if(@num = 0)
		SET @condition = '1=1'
	else
		SET @condition = 'n.bigClassId in (select bigClassId from BigClassAssociation where menuId='+Convert(varchar,@menuId)+')'
	--转换成索引
	SELECT @pageIndex=@pageIndex-1	

	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int
	DECLARE @vSQL nvarchar(2000)	
	SET @vSQL = N'SELECT @count = Count(*) FROM News n,bigClass b,template t WHERE n.gate<>'+Convert(varchar,@gateId)+' and n.gradeId<='+convert(varchar,@gradeId)+' and n.validDays <> 0  and n.passed='+convert(varchar,@passed)+' and (n.isDuplicated=0 or n.isDuplicated is null) and  n.isImageNews=0 and n.bigClassId = b.pkId and b.templateId = t.pkId and t.code!=''discuss'''
	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,template t where '+@condition+' and  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.bigClassId=b.pkId and n.gradeId<='+convert(varchar,@gradeId)+' and n.validDays <> 0 and n.passed='+convert(varchar,@passed)+' and n.isImageNews=0 and t.pkId = b.templateId and t.code!=''discuss'' 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,template t where '+@condition+' and  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.bigClassId=b.pkId and n.gradeId<='+convert(varchar,@gradeId)+' and n.validDays <> 0 and n.passed='+convert(varchar,@passed)+' and n.isImageNews=0 and t.pkId = b.templateId and t.code!=''discuss'' 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,template t where '+@condition+' and  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.bigClassId=b.pkId and n.gradeId<='+convert(varchar,@gradeId)+' and n.validDays <> 0 and n.passed='+convert(varchar,@passed)+' and n.isImageNews=0 and t.pkId = b.templateId and t.code!=''discuss'' 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,template t where '+@condition+' and  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.bigClassId=b.pkId and n.gradeId<='+convert(varchar,@gradeId)+' and n.validDays <> 0 and n.passed='+convert(varchar,@passed)+' and n.isImageNews=0 and t.pkId = b.templateId and t.code!=''discuss'' ORDER BY n.updateTime DESC, n.pkId DESC) TempTable1 ORDER BY updateTime ASC, pkId ASC) TempTable2 ORDER BY updateTime DESC, pkId DESC'
	END

	--执行查询
	EXEC(@sqlStr)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
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,
@menuId int
AS
	 SET NOCOUNT ON 
DECLARE @num int
DECLARE @condition varchar(100)
	select @num=0
	select @num = count(pkId) from BigClassAssociation where menuId = @menuId
	if(@num = 0)
		SET @condition = '1=1'
	else
		SET @condition = 'n.bigClassId in (select bigClassId from BigClassAssociation where menuId='+Convert(varchar,@menuId)+')'
		

	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 '+@condition+' and  n.gate<>'+Convert(varchar,@gateId)+' and 
						(n.isDuplicated=0 or n.isDuplicated is null) and 
						n.bigClassId not in (select pkId 
											 from bigClass 
											 where templateId in (49,50,53,54,65)
											 ) and 
						n.usersId=u.pkId and 
						n.departmentId=d.pkId and 
						n.gradeId<='+convert(varchar,@gradeId)+' and 
						n.passed=1 and 
						validDays <> 0
				order by n.click desc'
Execute (@vSQL)	
	RETURN 



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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




CREATE PROCEDURE [dbo].[DeleteBigClass]
(
	@pkId int
)
AS
	SET NOCOUNT OFF;
 BEGIN TRANSACTION
delete from BigClassAssociation where bigClassId = @pkId 
delete  from contributeClass where bigClassId = @pkId
delete  from contributeClass
  where contributeClassId in (select m.pkId from menu m where @pkId=bigClassId)

delete  from siteContributeClass where bigClassId = @pkId

delete  from moduleAssign
  where menuId in (select m.pkId from menu m where @pkId=bigClassId)
  delete from menu
   where @pkId=bigClassId
  delete from bigclass
   where @pkId=pkId
  
  COMMIT TRANSACTION   



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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




CREATE PROCEDURE [dbo].[DeleteMenu]
(
@pkId int
)
as
 BEGIN TRANSACTION
	declare @mpkId int
	declare @count int
	declare @npkId int
	set @npkId=@pkId
	set @mpkId=-1
	select  @count=Count(*) from menu where  @pkId=parentId
	while(@count<>0 or @mpkId<>@pkId)
	begin
		SELECT top 1 @mpkId=pkId
		FROM menu
		WHERE @npkId=parentId
		select  @count=Count(*) from menu where  @pkId=parentId
		if(@count=0)
		begin
			break;
		end
		select @count=Count(*) from menu where  @mpkId=parentId
		set @npkId=@mpkId
		if(@count>0)
		begin
			continue
		end
		else
		begin
			delete from contributeClass where contributeClassId= @mpkId	-- edit contributeClass
			delete from moduleAssign where @mpkId=menuId
			delete from artDesign where @mpkId=menuId
			delete from menu  where @mpkId=pkId  
			set @npkId=@pkId  
		end
	 END	 
	delete from BigClassAssociation where menuId = @pkId 
	 delete from contributeClass where contributeClassId= @pkId	-- edit contributeClass
	 delete from moduleAssign where @pkId=menuId
	 delete from artDesign where @pkId=menuId
	 delete from menu  where @pkId=pkId   
 COMMIT TRANSACTION 
 return




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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




CREATE PROCEDURE [dbo].[GetLayoutTemplateByName]
	@name varchar(100),
	@type int
AS
	SET NOCOUNT ON;
SELECT * FROM layoutTemplate where [name] = @name and [layoutTemplateType]=@type



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

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

CREATE PROCEDURE [dbo].[GetTopicReviewByNewsId]
	@searchStr varchar(500),
	@pageIndex int,
	@pageSize int,
	@pageCount int output,
	@recordCount int output
AS 
   SELECT @pageIndex = @pageIndex - 1
	
	DECLARE @tempNum int
	DECLARE @vSQL nvarchar(2000)
	SET @vSQL = N'SELECT @count = Count(review.newsId) FROM review WHERE ' + @searchStr
	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 (@pageIndex = 0) OR (@pageIndex > @pageCount-1)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM review WHERE ' + @searchStr + ' ORDER BY review.pkId DESC'	
	ELSE IF @pageIndex = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' * FROM review WHERE ' + @searchStr + ' ORDER BY review.pkId ASC) TempTable ORDER BY pkId DESC'	
	ELSE IF @pageIndex > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @pageIndex ) + ' * FROM review WHERE ' + @searchStr + ' ORDER BY review.pkId ASC) TempTable ORDER BY pkId DESC'
	ELSE BEGIN							
		SET @pageIndex=@pageIndex+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @pageIndex ) + ' * FROM review WHERE ' + @searchStr + ' ORDER BY review.pkId DESC) TempTable1 '
					+ ' ORDER BY pkId ASC) TempTable2 ORDER BY pkId DESC'
	END
	EXEC(@sqlStr)
	RETURN
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetdiscussPropertyByPkId] 
	@pkId int =null
AS 
	select	*
	from	discussProperty dP
	where	dP.discussId=@pkId

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
create PROCEDURE [dbo].[UpDatediscussProperty] 
	@discussId int =null,
    @mustLogin int,
    @mustCheckPass int
AS 
BEGIN
   SET NOCOUNT ON;
	if  not exists(select   1   from   discussProperty   where  discussId=@discussId)   
  begin       
  insert into discussProperty(discussId,mustLogin,mustCheckPass) values(@discussId,@mustLogin,@mustCheckPass) 
    end   
  else   
  update discussProperty set  mustLogin=@mustLogin , mustCheckPass = @mustCheckPass where discussId = @discussId
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[DeletediscussProperty]
	-- Add the parameters for the stored procedure here
	@discussId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	delete from discussProperty where discussId = @discussId
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE GetTemplateBypkId 
	-- Add the parameters for the stored procedure here
	@newsId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT * from template,bigClass,news where template.pkId = bigClass.templateId and news.bigClassId = bigClass.pkId and news.pkId = @newsId
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE [dbo].[TasksCount] 
	@Condition char(500)
AS
	DECLARE @_sql nvarchar(2000)
	DECLARE @_where nvarchar(500)
BEGIN
	SET NOCOUNT ON;
	IF @Condition = ''
		BEGIN
			SET @_where = ''
		END
	ELSE
		BEGIN
			SET @_where = ' WHERE ' + @Condition
		END
	SET @_sql = 'SELECT COUNT(*) FROM [Tasks] ' + @_where
	
	EXEC(@_sql)
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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





CREATE PROCEDURE [dbo].[InsertUsers]
(
	@userName varchar(100),
	@fullName varchar(100),
	@password varchar(100),
	@question varchar(100),
	@answer varchar(100),
	@sex varchar(10),
	@born datetime,
	@QQ varchar(100),
	@email varchar(100),
	@intro varchar(100),
	@loginIp varchar(100),
	@loginNum int,
	@loginTime datetime,
	@logIp varchar(100),
	@logTime datetime,
	@tel varchar(100),
	@photo varchar(100),
	@examine int,
	@gradeId int,
	@departmentId int,
	@company varchar(100),
	@appear int,
	@networkTest int,
	@Auditing int,
	@manageAll int,
	@usersGroupId int,
	@readInfoType int,
	@topRight int,
	@recommandRight int,
	@isSbwUser int,
	@isDel int,
	@pkId int output
)
AS
SET NOCOUNT OFF;
	
	 BEGIN TRANSACTION
	declare @count int
    declare @point int
    declare @days int
	
	select @usersGroupId=1
	select @gradeId=1
	select @topRight=0
	select @recommandRight=0
	select @count=Count(*) from users
	where userName=@userName
if @count!=0
 return 
	select @readInfoType= readInfoType,@examine=examine,@point=point,@days=days from RegUserInfo
		
INSERT INTO Users(userName, fullName, password, question, answer, sex, born, QQ, email, intro, 
loginIp, loginNum, loginTime, logIp, logTime, tel, photo, examine, gradeId, departmentId,
company, appear, networkTest, Auditing, manageAll, usersGroupId, readInfoType, topRight,
recommandRight, isSbwUser,isDel) VALUES (@userName, @fullName, @password, @question, @answer, @sex, @born, @QQ,
@email, @intro, @loginIp, @loginNum, @loginTime, @logIp, @logTime, @tel, @photo, @examine, 
@gradeId, @departmentId, @company, @appear, @networkTest, @Auditing, @manageAll,
@usersGroupId, @readInfoType, @topRight, @recommandRight, @isSbwUser,@isDel)
select @pkId=@@IDENTITY
insert into Riches(usersId,addPoint,subPoint,updateTime,addDays,subDays,remark) values
(@pkId,@point,0,GETDATE(),@days,0,'')
INSERT INTO moduleAssign 
   (usersId,menuId,privilegeSet)
SELECT usersId=@pkId,menuId,privilegeSet
FROM moduleAssign 
WHERE usersId=-1
 COMMIT TRANSACTION





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

CREATE PROCEDURE [dbo].[UsersInRolesInsert]
(
	@RoleID int,
	@UserID int,
	@pkId int output
)
AS
SET NOCOUNT OFF;
	
	BEGIN TRANSACTION			
		INSERT INTO UsersInRoles([RoleID], [UserID]) VALUES (@RoleID, @UserID)
		SELECT @pkId=@@IDENTITY
	COMMIT TRANSACTION





GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================

create PROCEDURE [dbo].[GetBigResearAllInfo] 
	-- Add the parameters for the stored procedure here
	
	@pkId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	select * from BigResear where pkId = @pkId
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE GetBigResearVerify
	-- Add the parameters for the stored procedure here
	
	@Phone nvarchar(15)
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	select pkId from BigResear where Phone = @Phone
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE GetBigResearDate
	-- Add the parameters for the stored procedure here
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT * from BigResearDate
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE InsertBigResear
	-- Add the parameters for the stored procedure here
	@Area nvarchar(50),
	@Address nvarchar(50),
	@Zipcode nvarchar(6),
	@Name nvarchar(50),
	@Department nvarchar(50),
	@Duty nvarchar(50),
	@Phone nvarchar(15),
	@DateY nvarchar(5),
	@DateM nvarchar(5),
	@DateD nvarchar(5),
	@Answer ntext
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	insert into BigResear(Area,Address,ZipCode,Name,Department,Duty,Phone,DateY,DateM,DateD,Answer)
                    values(@Area,@Address,@Zipcode,@Name,@Department,@Duty,@Phone,@DateY,@DateM,@DateD,
                    @Answer)
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE UpdateBigResear
	-- Add the parameters for the stored procedure here
	@pkId int,
	@Area nvarchar(50),
	@Address nvarchar(50),
	@Zipcode nvarchar(6),
	@Name nvarchar(50),
	@Department nvarchar(50),
	@Duty nvarchar(50),
	@Phone nvarchar(15),
	@DateY nvarchar(5),
	@DateM nvarchar(5),
	@DateD nvarchar(5),
	@Answer ntext
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	update BigResear set
                        Area=@Area,
                        Address=@Address,
                        ZipCode=@Zipcode,
                        Name=@Name,
                        Department=@Department,
                        Duty=@Duty,
                        Phone=@Phone,
                        DateY=@Datey,
                        DateM=@Datem,
                        DateD=@Dated,
                        Answer=@Answer 
                        where pkId = @pkId
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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



CREATE PROCEDURE [dbo].[InsertMenu]
(
	@pkId int,
	@parentId int,
	@name varchar(100),
	@remark varchar(100)=null,
	@ordering int=0,
	@address varchar(100)=null,
	@bigClassId int=0,
	@modeId int,
	@gradeId int,
	@gate int=0,
	@recorderNum int=7,
	@menuPic varchar(100),
	@itemId varchar(100),
	@layoutTemplateId int,
	@adminId int,
	@displayRoot int,
	@userIntercept int,
	@interceptNums int,
    @columnNum     int,
    @artDesignTemplateId int,
	@contributeSortType int
)
AS
	SET NOCOUNT OFF;
BEGIN TRANSACTION
INSERT INTO menu(parentId, [name], remark, ordering, address, bigClassId, modeId, gradeId,gate,recorderNum,menuPic,itemId,layoutTemplateId,adminId,displayRoot, userIntercept,interceptNums,columnNum,artDesignTemplateId, contributeSortType) VALUES (@parentId, @name, @remark, @ordering, @address,@bigClassId, @modeId, @gradeId,@gate,@recorderNum,@menuPic,@itemId,@layoutTemplateId,@adminId,@displayRoot,@userIntercept,@interceptNums,@columnNum,@artDesignTemplateId, @contributeSortType)
declare @menuId int
select @menuId=@@IDENTITY
declare @n varchar(100)
select @n=[name] from mode where pkId=@modeId
if(@n='频道')
begin
select @address='index.aspx?pkId='+convert(varchar,@menuId)
update menu set address=@address where pkId=@menuId
end
if(@n='栏目')
begin
     insert into artDesign([name],menuId,leftVert,rightVert,fontPosition,color,[size],[space],font,bold,italic,topColor,middleColor,bottomColor,border,cellPad,cellSpace,brdrColor,leftMiddleFrontWidth,leftMiddleFrontHeight,centerTopFrontWidth,centerTopFrontHeight,rightMiddleFrontWidth,rightMiddleFrontHeight,centerTopBackHeight,middleTableBorderColor,bottomMoreInfoImageVar) values(@name,@menuId,'middle','middle','left','#000000',3,2,'宋体',0,0,'','','',0,0,0,'',20,20,20,20,20,20,25,'','more.gif')
     select @layoutTemplateId=pkId 
        from layoutTemplate
        where layoutTemplateType=1 
          and isSystem=1
     update menu set layoutTemplateId=@layoutTemplateId where pkId=@menuId
  end
 COMMIT TRANSACTION
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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




CREATE PROCEDURE [dbo].[UpdateMenu]
(
	@parentId int,
	@name varchar(100),
	@remark varchar(100),
	@ordering int,
	@address varchar(100),
	@bigClassId int,
	@modeId int,
	@gradeId int,
	@pkId int,
	@gate int,
	@recorderNum int,
	@menuPic varchar(100),
	@itemId  varchar(100),
	@layoutTemplateId int,
	@adminId int,
	@displayRoot int,
	@userIntercept int,
	@interceptNums int,
    @columnNum     int,
    @artDesignTemplateId int,
	@contributeSortType int
)
AS
	SET NOCOUNT OFF;
 BEGIN TRANSACTION
declare @n varchar(100)
select @n=[name] from mode where pkId=@modeId
if(@n='频道')
begin
select @address='index.aspx?pkId='+convert(varchar,@pkId)
end
UPDATE menu SET artDesignTemplateId=@artDesignTemplateId,columnNum=@columnNum,interceptNums=@interceptNums,userIntercept=@userIntercept, displayRoot=@displayRoot,parentId = @parentId, name = @name, remark = @remark, ordering = @ordering, address = @address, bigClassId = @bigClassId, modeId = @modeId, gradeId = @gradeId,gate=@gate ,recorderNum=@recorderNum,menuPic=@menuPic,itemId=@itemId,layoutTemplateId=@layoutTemplateId ,adminId=@adminId, contributeSortType=@contributeSortType WHERE (pkId = @pkId)
 COMMIT TRANSACTION
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE UpdateMenuArtDesign 
	-- Add the parameters for the stored procedure here
	@menuId int,
	@templateId int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	update menu set artDesignTemplateId = @templateId where pkId = @menuId
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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



CREATE PROCEDURE [dbo].[GetShowItem]
@gradeId int=1,
@parentId int,
@gateId int
AS
	select m.pkId,m.name,m.itemId,m.bigClassId 'bigClassId',m.gate,m.userIntercept,m.interceptNums,m.columnNum,ordering,m.recorderNum,m.menuPic,m.gradeId,t.code,m.layoutTemplateId, m.displayRoot, m.contributeSortType
	  from menu m,bigClass b,template t
	  where m.parentId=@parentId
	    and m.gate<>@gateId
	    and m.gradeId<=@gradeId
	    and m.bigClassId=b.pkId
	    and b.templateId=t.pkId
	order by m.pkId desc,ordering desc
	RETURN
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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




CREATE PROCEDURE [dbo].[GetContributeByStatTime]
	@beginTime varchar(20),
	@endTime varchar(20),
	@pageIndex int,
	@pageSize int,
	@recordCount int output,
	@contributeSortType int
AS
BEGIN	
	SET NOCOUNT ON;
	
	DECLARE @isSql2005 int, @isSql2000 int, @tempTable varchar(20)		
	
	-- 判断数据库版本
	IF PATINDEX('%2005 - 9%', @@VERSION)<>0
	BEGIN
		SET @isSql2005 = 1
		SET @isSql2000 = 0
	END
	ELSE IF PATINDEX('%2000 - 8%', @@VERSION)<>0
	BEGIN
		SET @isSql2000 = 1
		SET @isSql2005 = 0
		SET @tempTable = 'TempA' + LTRIM(STR(YEAR(GetDate()))) + LTRIM(STR(MONTH(GetDate()))) + LTRIM(STR(DAY(GetDate()))) 
					+ LTRIM(STR(DATEPART(hh,GetDate()))) + LTRIM(STR(DATEPART(n,GetDate()))) + LTRIM(STR(DATEPART(ss,GetDate())))
	END
		
	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(2000), @sortFiled varchar(50)
	if(@contributeSortType=0)
		SET @sortFiled = 'articleNums'
	Else
		SET @sortFiled = 'totalNums'
	
	SET @vSQL = N'SELECT @count = Count(*) FROM (SELECT u.pkId as userId, Count(n.pkId) as articleNums FROM Users u, news n, contribute c '
			+'WHERE u.pkId = c.userId AND c.newsId = n.pkId AND c.state = 1 AND c.updateTime >= ''' + @beginTime + ''' AND c.updateTime <''' 
			+ @endTime + ''' GROUP BY u.pkId) TempTable'
	EXEC sp_ExecuteSQL @vSQL, N'@count int output', @tempNum output
	SELECT @recordCount = @tempNum
	
	--转换成索引
	DECLARE @pageIndex_1 int
	SELECT @pageIndex_1 = @pageIndex-1	
	
	--计算页数
	SET @pageCount = (@recordCount +@pageSize-1)/@pageSize

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

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	

	DECLARE @insert varchar(1000)
		
	IF (@isSql2005 = 1) AND (@isSql2000 = 0)	--sql2005执行
	BEGIN
		if(@contributeSortType = 0)
		BEGIN
		SET	@sqlStr = 'SELECT * FROM (SELECT  u.pkId AS userId, u.userName AS userName, u.fullName AS fullName, u.tel AS tel, u.email AS email, '
				+ 'Count(n.pkId) AS articleNums,(select Count(*) from contribute where userId=u.pkId and updateTime>=''' + @beginTime+ ''' and updateTime<''' + @endTime + ''' and state<>5) AS totalNums, rank() over(order by Count(n.pkId) DESC, u.fullName ASC) AS ID , row_number() over(order by Count(n.pkId) DESC, u.fullName ASC) AS pkId '
				+ 'FROM Users u, news n, contribute c WHERE u.pkId = c.userId AND c.newsId = n.pkId AND c.state = 1 AND c.updateTime >= '''
				+ @beginTime + ''' AND c.updateTime <''' + @endTime + ''' GROUP BY u.pkId, userName, fullName, tel, email) '
				+ 'TempTable WHERE pkId > ' + STR(@pageIndex_1*@pageSize) + ' AND pkId<=' + STR(@pageIndex*@pageSize) + 'ORDER BY ID asc'
		END
		ELSE
		BEGIN
			SET	@sqlStr = 'SELECT * FROM (SELECT  u.pkId AS userId, u.userName AS userName, u.fullName AS fullName, u.tel AS tel, u.email AS email, '
				+ 'Count(n.pkId) AS articleNums,(select Count(*) from contribute where userId=u.pkId and updateTime>=''' + @beginTime+ ''' and updateTime<''' + @endTime + ''' and state<>5) AS totalNums, rank() over(order by (select Count(*) from contribute where userId=u.pkId and updateTime>=''' + @beginTime+ ''' and updateTime<''' + @endTime + ''' and state<>5) DESC, u.fullName ASC) AS ID , row_number() over(order by (select Count(*) from contribute where userId=u.pkId and updateTime>=''' + @beginTime+ ''' and updateTime<''' + @endTime + ''' and state<>5) DESC, u.fullName ASC) AS pkId '
				+ 'FROM Users u, news n, contribute c WHERE u.pkId = c.userId AND c.newsId = n.pkId AND c.state = 1 AND c.updateTime >= '''
				+ @beginTime + ''' AND c.updateTime <''' + @endTime + ''' GROUP BY u.pkId, userName, fullName, tel, email) '
				+ 'TempTable WHERE pkId > ' + STR(@pageIndex_1*@pageSize) + ' AND pkId<=' + STR(@pageIndex*@pageSize) + 'ORDER BY ID asc'
		END
	END
	ELSE IF (@isSql2005 = 0) AND (@isSql2000 =1) --sql2000执行
	BEGIN	
		SET @insert = 'SELECT Count(n.pkId) AS articleNums,(select Count(*) from contribute where userId=u.pkId and updateTime>=''' + @beginTime+ ''' and updateTime<''' + @endTime + ''' and state<>5) AS totalNums, u.pkId AS userId, u.userName AS userName, u.fullName AS fullName, u.tel AS tel, u.email AS email'
				+ ' into ' + @tempTable + ' FROM Users u, news n, contribute c WHERE u.pkId = c.userId AND c.newsId = n.pkId AND '
				+ 'c.state = 1 AND c.updateTime >= ''' + @beginTime + ''' AND c.updateTime <''' + @endTime + ''' GROUP BY u.pkId, userName, fullName, tel, email '
				+ 'ORDER BY '+@sortFiled+' DESC,  u.fullName ASC'	
		EXEC(@insert)
		 
		DECLARE @tempTable1 varchar(1000)
		SET @tempTable1 = 'TempC' + LTRIM(STR(YEAR(GetDate()))) + LTRIM(STR(MONTH(GetDate()))) + LTRIM(STR(DAY(GetDate()))) 
					+ LTRIM(STR(DATEPART(hh,GetDate()))) + LTRIM(STR(DATEPART(n,GetDate()))) + LTRIM(STR(DATEPART(ss,GetDate())))

		SET @insert = 'SELECT identity(int, 1, 1) as pkId , 1 as ID, * into ' + @tempTable1 + ' FROM  '+@tempTable
		EXEC(@insert)
		SET @insert = 'DROP TABLE ' + @tempTable	
		EXEC(@insert)		
		SET @insert = 'declare @id int, @nums int declare myCursor CURSOR FOR SELECT MIN(pkId) as Id, '+@sortFiled+' as nums FROM ' + @tempTable1 + ' GROUP BY '+@sortFiled+' ORDER BY Id asc '
				+ 'OPEN myCursor FETCH NEXT FROM myCursor INTO @id, @nums WHILE @@FETCH_STATUS = 0 BEGIN UPDATE ' + @tempTable1 + ' SET ID=@id WHERE '+@sortFiled+'=@nums '
				+ 'FETCH NEXT FROM myCursor INTO @id, @nums END CLOSE myCursor DEALLOCATE myCursor'
		EXEC(@insert)
		IF @pageIndex_1 <= 0
		BEGIN
			SET	@sqlStr = 'SELECT TOP ' + STR(@pageSize) + '* FROM ' + @tempTable1 + ' ORDER BY ID ASC, fullName ASC'
		END
		ELSE IF @pageIndex_1 >= @pageCount -1
		BEGIN
			SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' * FROM ' + @tempTable1 + ' ORDER BY ID DESC, fullName DESC) TempTable ORDER BY ID ASC, fullName ASC'
		END
		ELSE IF @pageIndex_1 > @pageCount/2
		BEGIN
			SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + '* FROM (SELECT TOP ' + STR(@recordCount - @pageIndex * @pageSize) + ' * FROM ' + @tempTable1
					+ ' ORDER BY ID DESC, fullName DESC) TempTable ORDER BY ID ASC, fullName ASC'
		END
		ELSE BEGIN
			SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + '* FROM (SELECT TOP ' + STR(@pageIndex * @pageSize) + ' * FROM ' + @tempTable1
					+ ' ORDER BY ID ASC, fullName ASC) TempTable ORDER BY ID DESC, fullName DESC) TempTable1 ORDER BY ID ASC, fullName ASC'
		END		
		SET @insert = 'DROP TABLE ' + @tempTable1
	END
	--执行查询
	EXEC(@sqlStr)
	
	EXEC(@insert)
END




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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


CREATE PROCEDURE [dbo].[GetContributesByPage]
	@pageIndex int=1,		--@pageIndex
	@pageSize int=7,		--@pageSize
	@recordCount int output,	-- 
	@contributeSortType int
AS
--转换成索引
	SELECT @pageIndex = @pageIndex-1	

	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int, @pageCount int
	DECLARE @vSQL nvarchar(2000),@sortFiled varchar(50)

	if(@contributeSortType=0)
		SET @sortFiled = 'articleNums'
	Else
		SET @sortFiled = 'totalNums'

	SET @vSQL = N'SELECT @count = Count(*) FROM( SELECT Count(n.pkId) as nums, u.pkId as userId FROM news n,users u,department d,'
			+ 'contribute c where c.userId=u.pkId and u.departmentId=d.pkId and c.state=1 and c.newsId = n.pkId '
			+ 'group by u.pkId) TempTable'
	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, @tempTable varchar(1000)

	SET @tempTable = 'TempB' + LTRIM(STR(YEAR(GetDate()))) + LTRIM(STR(MONTH(GetDate()))) + LTRIM(STR(DAY(GetDate()))) 
					+ LTRIM(STR(DATEPART(hh,GetDate()))) + LTRIM(STR(DATEPART(n,GetDate()))) + LTRIM(STR(DATEPART(ss,GetDate())))

	--判断分页大小，查询最后一页用
	SET @size = @recordCount%@pageSize	
	IF(@size=0)
		SET @size = @pageSize	

	DECLARE @insert varchar(1000)
	
	SET @insert = 'SELECT u.pkId as userId, u.userName as userName, '
					+ 'u.fullName as fullName, u.email as email, u.tel as tel, d.name as department, '
					+ 'Count(n.pkId) as articleNums,(select Count(*) from contribute where userId=u.pkId and state<>5) AS totalNums into ' + @tempTable + ' from news n,users u,department d, '
					+ 'contribute c where c.userId=u.pkId and u.departmentId=d.pkId and c.state=1 and '
					+ 'c.newsId = n.pkId Group by u.pkId, u.userName, u.fullName, u.tel, u.email, d.name '
					+ ' ORDER BY '+@sortFiled+' desc,  u.fullName ASC '
	EXEC(@insert)					
	--查询第一页
	IF (@pageIndex = 0) OR (@pageIndex <0)	
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM ' + @tempTable + ' ORDER by '+@sortFiled+' desc, fullName ASC'	
	--查询最后一页
	ELSE IF ((@pageIndex = @pageCount -1) OR (@pageIndex > @pageCount-1))			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' * from ' + @tempTable + ' ORDER BY '+@sortFiled+' asc, fullName DESC) TempTable ORDER BY '+@sortFiled+' desc, fullName ASC'	
	--查询中间页后面的页
	ELSE IF @pageIndex > @pageCount/2			
		SET @sqlStr = 'SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@recordCount - @pageSize * @pageIndex ) + ' * from ' + @tempTable + ' ORDER BY '+@sortFiled+' asc, fullName DESC) TempTable ORDER BY '+@sortFiled+' desc, fullName ASC'
	--查询中间页前面的页
 	ELSE BEGIN							
		SET @pageIndex=@pageIndex+1
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@pageSize) + ' * FROM (SELECT TOP ' 
			        + STR(@pageSize * @pageIndex ) + ' * from ' + @tempTable + ' ORDER BY '+@sortFiled+' desc, fullName ASC) TempTable1 '
					+ ' ORDER BY '+@sortFiled+' asc, fullName DESC) TempTable2 ORDER BY '+@sortFiled+' desc, fullName ASC'
	END

	--执行查询
	EXEC(@sqlStr)
	SET @insert = 'DROP TABLE ' + @tempTable
	EXEC(@insert)
RETURN

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

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




create PROCEDURE [dbo].[GetBigClassAssociateByMenuId]
@menuId int,
@gradeId int=1
AS
	/* SET NOCOUNT ON */
	SELECT b.*,
	       b.pkId "bigClassId"
	FROM bigClass b,BigClassAssociation c
	where b.pkId=c.bigClassId
	  and c.menuId=@menuId
	 
	order by c.pkId  desc
	RETURN 



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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



CREATE PROCEDURE [dbo].[GetBigClassAssocateByPkId]
@pkId int =null
AS
	 SET NOCOUNT ON 
	select *
	from BigClassAssociation
	where pkId=@pkId
	  
	RETURN 



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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



CREATE PROCEDURE [dbo].[InsertBigClassAssociation]
@pkId int=null,
@menuId	int,
@bigClassId int	
AS
	SET NOCOUNT ON 
	insert BigClassAssociation(menuId, bigClassId)
	       values(@menuId, @bigClassId)
	RETURN


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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



CREATE PROCEDURE [dbo].[DeleteBigClassAssociateByMenuIdAndBigClassId]
(
	@menuId int,	-- menuId
	@bigClassId int
)
AS
	SET NOCOUNT OFF;
             
       DELETE FROM BigClassAssociation WHERE menuId = @menuId and bigClassId = @bigClassId  



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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



CREATE PROCEDURE [dbo].[Insertreview]
@pkId int=null,
@newsId    int,
@name	varchar(100),
@content	ntext,
@title	varchar(100),
@updateTime	datetime,
@ipAddress	varchar(100),
@gradeId  int,
@passed	int ,
@userPhoto nvarchar(100)
AS
		SET NOCOUNT ON
		insert review(newsId,
name,
content,
title,
updateTime,
ipAddress,
gradeId,
passed,	 
userPhoto	               
	               )
	         values(@newsId,
@name,
@content,
@title,
@updateTime,
@ipAddress,
@gradeId,
@passed,	 
@userPhoto	               
	               )
	RETURN 




GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

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



CREATE PROCEDURE [dbo].[UpdateReview]
(
	@newsId int,
	@name varchar(100),
	@content ntext,
	@title varchar(100),
	@updateTime datetime,
	@ipAddress varchar(100),
	@gradeId int,
	@passed int,
	@userPhoto nvarchar(100),
	@pkId int
)
AS
	SET NOCOUNT OFF;
UPDATE review SET newsId = @newsId, name = @name, content = @content, title = @title, updateTime = @updateTime, ipAddress = @ipAddress, gradeId = @gradeId, passed = @passed, userPhoto=@userPhoto WHERE (pkId = @pkId)



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
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 ,
	@menuId int
AS	
	DECLARE @num int
	DECLARE @condition varchar(100)
	select @num=0
	select @num = count(pkId) from BigClassAssociation where menuId = @menuId
	if(@num = 0)
		SET @condition = '1=1'
	else
		SET @condition = 'n.bigClassId in (select bigClassId from BigClassAssociation where menuId='+Convert(varchar,@menuId)+')'
	--转换成索引
	SELECT @pageIndex=@pageIndex-1	

	--先获取记录总数，保存在@recordCount
	DECLARE @tempNum int
	DECLARE @vSQL nvarchar(2000)	
	SET @vSQL = N'SELECT @count = Count(*) FROM News n,bigClass b,template t WHERE n.gate<>'+Convert(varchar,@gateId)+' and n.gradeId<='+convert(varchar,@gradeId)+' and n.validDays <> 0  and n.passed='+convert(varchar,@passed)+' and (n.isDuplicated=0 or n.isDuplicated is null) and  n.isImageNews=0 and n.bigClassId = b.pkId and b.templateId = t.pkId and t.code!=''discuss'''
	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,template t where '+@condition+' and  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.bigClassId=b.pkId and n.gradeId<='+convert(varchar,@gradeId)+' and n.validDays <> 0 and n.passed='+convert(varchar,@passed)+' and n.isImageNews=0 and t.pkId = b.templateId and t.code!=''discuss'' 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,template t where '+@condition+' and  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.bigClassId=b.pkId and n.gradeId<='+convert(varchar,@gradeId)+' and n.validDays <> 0 and n.passed='+convert(varchar,@passed)+' and n.isImageNews=0 and t.pkId = b.templateId and t.code!=''discuss'' 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,template t where '+@condition+' and  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.bigClassId=b.pkId and n.gradeId<='+convert(varchar,@gradeId)+' and n.validDays <> 0 and n.passed='+convert(varchar,@passed)+' and n.isImageNews=0 and t.pkId = b.templateId and t.code!=''discuss'' 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,template t where '+@condition+' and  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.bigClassId=b.pkId and n.gradeId<='+convert(varchar,@gradeId)+' and n.validDays <> 0 and n.passed='+convert(varchar,@passed)+' and n.isImageNews=0 and t.pkId = b.templateId and t.code!=''discuss'' ORDER BY n.updateTime DESC, n.pkId DESC) TempTable1 ORDER BY updateTime ASC, pkId ASC) TempTable2 ORDER BY updateTime DESC, pkId DESC'
	END

	--执行查询
	EXEC(@sqlStr)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
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,
@menuId int
AS
	 SET NOCOUNT ON 
DECLARE @num int
DECLARE @condition varchar(100)
	select @num=0
	select @num = count(pkId) from BigClassAssociation where menuId = @menuId
	if(@num = 0)
		SET @condition = '1=1'
	else
		SET @condition = 'n.bigClassId in (select bigClassId from BigClassAssociation where menuId='+Convert(varchar,@menuId)+')'
		

	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 '+@condition+' and  n.gate<>'+Convert(varchar,@gateId)+' and 
						(n.isDuplicated=0 or n.isDuplicated is null) and 
						n.bigClassId not in (select pkId 
											 from bigClass 
											 where templateId in (49,50,53,54,65)
											 ) and 
						n.usersId=u.pkId and 
						n.departmentId=d.pkId and 
						n.gradeId<='+convert(varchar,@gradeId)+' and 
						n.passed=1 and 
						validDays <> 0
				order by n.click desc'
Execute (@vSQL)	
	RETURN 



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

