SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
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 InsertUsers
	-- Add the parameters for the stored procedure here
  (@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)
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 OFF 
GO
SET ANSI_NULLS OFF 
GO

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

CREATE PROCEDURE UpdateUsers
	(@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,
	@pkId int,
	@isSbwUser int,
	@isDel int)
AS
	SET NOCOUNT OFF;
UPDATE Users SET isDel=@isDel, userName = @userName, fullName = @fullName, password = @password, question = @question, answer = @answer, sex = @sex, born = @born, QQ = @QQ, email = @email, intro = @intro, loginIp = @loginIp, loginNum = @loginNum, loginTime = @loginTime, logIp = @logIp, logTime = @logTime, tel = @tel, photo = @photo, examine = @examine, gradeId = @gradeId, departmentId = @departmentId, company = @company, appear = @appear, networkTest = @networkTest, Auditing = @Auditing, manageAll = @manageAll, usersGroupId = @usersGroupId, readInfoType = @readInfoType, topRight = @topRight, recommandRight = @recommandRight, isSbwUser=@isSbwUser WHERE (pkId = @pkId)
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].[GetUsersByPage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetUsersByPage]
GO

CREATE PROCEDURE GetUsersByPage
	(@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(u.pkId) FROM users u,grade g,department d  WHERE ' + @searchStr + ' and u.gradeId=g.pkId and u.departmentId=d.pkId'
	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) + ' u.*,g.name "grade",d.name "department" FROM users u,grade g,department d WHERE ' + @searchStr + ' and u.gradeId=g.pkId and u.departmentId=d.pkId ORDER BY u.pkId DESC'	
	ELSE IF @pageIndex = @pageCount -1			
		SET @sqlStr = 'SELECT * FROM (SELECT TOP ' + STR(@size) + ' u.*,g.name "grade",d.name "department" FROM users u,grade g,department d WHERE ' + @searchStr + ' and u.gradeId=g.pkId and u.departmentId=d.pkId ORDER BY u.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 ) + ' u.*,g.name "grade",d.name "department" FROM users u,grade g,department d WHERE ' + @searchStr + ' and u.gradeId=g.pkId and u.departmentId=d.pkId ORDER BY u.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 ) + ' u.*,g.name "grade",d.name "department" FROM users u,grade g,department d WHERE ' + @searchStr + ' and u.gradeId=g.pkId and u.departmentId=d.pkId ORDER BY u.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 OFF 
GO
SET ANSI_NULLS OFF 
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
)
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) VALUES (@parentId, @name, @remark, @ordering, @address,@bigClassId, @modeId, @gradeId,@gate,@recorderNum,@menuPic,@itemId,@layoutTemplateId,@adminId,@displayRoot,@userIntercept,@interceptNums,@columnNum)
declare @menuId int
select @menuId=SCOPE_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