-- ==============================
-- 长登科技 www.ppfor.com
-- 唐镇波   2010-04-19
-- ------------------------------
-- 管理员表
-- ==============================

--创建管理员表
if not exists(select name from sysobjects where name='PPFOR_TZB_Administrator' and type='U')
	create table PPFOR_TZB_Administrator
	(
		[id] int identity(1000,1) not null primary key,	--自动编号，从1000开始递增
		adminName nvarchar(20) COLLATE Chinese_PRC_CS_AS not null unique ,		--管理员账号
		userName nvarchar(20) COLLATE Chinese_PRC_CS_AS not null,				--会员账号
		adminPassword char(32) COLLATE Chinese_PRC_CS_AS not null,				--管理员密码
		role nvarchar(20) COLLATE Chinese_PRC_CS_AS not null,					--管理员所属角色
		languages text null,							--允许管理的语言
		menus text null,								--允许管理的栏目		
		isAllowModifyPwd bit not null default(1),		--是否允许管理员修改密码
		isLocked bit not null default(0),				--否锁定账号
		lastLoginedIP varchar(15),						--最后登录IP
		lastLoginedTime datetime,						--最后登录时间
		loginedTimes int not null default(0),			--登录次数
		expiresTime int not null default(30)			--登录过期时间
	)
PPFOR_GO;  

if exists(select name from sysobjects where name='PPFOR_TZB_Administrator_Add' and type='P')
	drop procedure PPFOR_TZB_Administrator_Add
PPFOR_GO;

--添加管理员，返回添加的管理员id
create procedure PPFOR_TZB_Administrator_Add (@adminName nvarchar(20),@userName nvarchar(20),
	@adminPassword char(32),@role nvarchar(20),@languages text,@menus text,
	@isAllowModifyPwd bit,@isLocked bit,@id int output)
as
	begin
		if not exists(select [id] from PPFOR_TZB_Administrator where adminName=@adminName
		 or (userName<>'' and userName=@userName))
			begin
				insert into PPFOR_TZB_Administrator values(@adminName,@userName,@adminPassword,@role,
					@languages,@menus,@isAllowModifyPwd,@isLocked,null,null,0,30)
				set @id=@@identity
			end
		else
			set @id=0
	end
PPFOR_GO;

if exists(select name from sysobjects where name='PPFOR_TZB_Administrator_Update' and type='P')
	drop procedure PPFOR_TZB_Administrator_Update
PPFOR_GO;


--更新管理员设置,不包括更新账号和密码
create procedure PPFOR_TZB_Administrator_Update(@adminName nvarchar(20),@role nvarchar(20),@languages text,
	@menus text,@isAllowModifyPwd bit,@isLocked bit,@expiresTime int)
as
	update PPFOR_TZB_Administrator set role=@role,languages=@languages,menus=@menus,isAllowModifyPwd=
	 	@isAllowModifyPwd,isLocked=@isLocked,expiresTime=@expiresTime where adminName=@adminName
PPFOR_GO;


if exists(select name from sysobjects where name='PPFOR_TZB_Administrator_UpdatePassword' and type='P')
	drop procedure PPFOR_TZB_Administrator_UpdatePassword
PPFOR_GO;
--更新管理员密码
create procedure PPFOR_TZB_Administrator_UpdatePassword(@adminName nvarchar(20),@adminPassword char(32))
as
	update PPFOR_TZB_Administrator set adminPassword=@adminPassword where adminName=@adminName
PPFOR_GO;

if exists(select name from sysobjects where name='PPFOR_TZB_Administrator_Delete' and type='P')
	drop procedure PPFOR_TZB_Administrator_Delete
PPFOR_GO;

--根据id删除管理员
create procedure PPFOR_TZB_Administrator_Delete(@id int)
as
	delete from PPFOR_TZB_Administrator where [id]=@id
PPFOR_GO;

if exists(select name from sysobjects where name='PPFOR_TZB_Administrator_GetModelById' and type='P')
	drop procedure PPFOR_TZB_Administrator_GetModelById
PPFOR_GO;

--根据管理员id返回管理员资料,不包括密码
create procedure PPFOR_TZB_Administrator_GetModelById (@id int)
as
	select [id],adminName,userName,role,languages,menus,isAllowModifyPwd,isLocked,
		lastLoginedIP,lastLoginedTime,loginedTimes,expiresTime 
		from PPFOR_TZB_Administrator where [id]=@id
PPFOR_GO;

if exists(select name from sysobjects where name='PPFOR_TZB_Administrator_GetModel' and type='P')
	drop procedure PPFOR_TZB_Administrator_GetModel
PPFOR_GO;

--根据管理员账号返回管理员资料,不包括密码
create procedure PPFOR_TZB_Administrator_GetModel (@adminName nvarchar(20))
as
	select [id],userName,role,languages,menus,isAllowModifyPwd,isLocked,
		lastLoginedIP,lastLoginedTime,loginedTimes,expiresTime 
		from PPFOR_TZB_Administrator where adminName=@adminName
PPFOR_GO;

if exists(select name from sysobjects where name='PPFOR_TZB_Administrator_GetModelListByPage' and type='P')
	drop procedure PPFOR_TZB_Administrator_GetModelListByPage
PPFOR_GO;

--返回所有管理员资料，不包括密码
create procedure PPFOR_TZB_Administrator_GetModelListByPage(@pageSize int,@pageIndex int)
as
	begin
		declare @rc int
		declare @pc int
		declare @sqlStr nvarchar(1000)
		declare @sqlStr2 nvarchar(1000)
		
		if @pageSize<1
			set @pageSize=20

		select @rc=count([id]) from PPFOR_TZB_Administrator
		if @rc=0
			set @pc=0
		else
			set @pc = ceiling(@rc * 1.0 / @pageSize)

		if @pageIndex>@pc
			set @pageIndex=@pc
		else if @pageIndex<1
			set @pageIndex=1
		
		set @sqlStr2=' [id],adminName,userName,role,languages,menus,
			isAllowModifyPwd,isLocked,lastLoginedIP,lastLoginedTime,
			loginedTimes,expiresTime from PPFOR_TZB_Administrator '
	
		if @pageIndex <= 1 or @pc <= 1
			set @sqlStr='select top ' + str(@pageSize) + @sqlStr2 + ' order by [id] desc'
		else if @pageIndex = @pc
			set @sqlStr ='select * from (select top ' + str( @rc - @pageSize * (@pageIndex-1) ) 
				+ @sqlStr2 + ' order by [id] asc) Temptable order by [id] desc'
		else  
			set @sqlStr ='select top ' + str(@pageSize) + ' * from (select top '
				+ str( @rc - @pageSize * (@pageIndex-1) ) + @sqlStr2 + ' order by 
				[id] asc) Temptable order by [id] desc'
		
		exec (@sqlStr)
		select @rc as recordCount,@pc as pageCount
	end
PPFOR_GO;

if exists(select name from sysobjects where name='PPFOR_TZB_Administrator_IsExists' and type='P')
	drop procedure PPFOR_TZB_Administrator_IsExists
PPFOR_GO;

--返回是否存在管理员，是为1，否为0
create procedure PPFOR_TZB_Administrator_IsExists(@adminName nvarchar(20),@userName nvarchar(20),
	@retValue bit output)
as
	begin
		if exists(select [id] from PPFOR_TZB_Administrator where adminName=@adminName 
			or (userName<>'' and userName=@userName))
			set @retValue=1
		else
			set @retValue=0
	end
PPFOR_GO;

if exists(select name from sysobjects where name='PPFOR_TZB_Administrator_CheckLogin' and type='P')
	drop procedure PPFOR_TZB_Administrator_CheckLogin
PPFOR_GO;

--验证管理员是否可以登录，成功@retValue为1，失败@retValue为0
create procedure PPFOR_TZB_Administrator_CheckLogin(@adminName nvarchar(20),@adminPassword char(32),
	 @retValue bit output)
as
	begin
		if exists (select [id] from PPFOR_TZB_Administrator where adminName=@adminName
			 and adminPassword=@adminPassword and isLocked=0)	 

			set @retValue=1
		else
			set @retValue=0
	end
PPFOR_GO;

if exists(select name from sysobjects where name='PPFOR_TZB_Administrator_Login' and type='P')
	drop procedure PPFOR_TZB_Administrator_Login
PPFOR_GO;

--管理员登录，成功@retValue为1，失败@retValue为0
create procedure PPFOR_TZB_Administrator_Login(@adminName nvarchar(20),@adminPassword char(32),
	@lastIP varchar(15),@lastTime datetime,@retValue bit output)
as
	begin
		set @retValue=0
		update PPFOR_TZB_Administrator set lastLoginedIP=@lastIP,lastLoginedTime=@lastTime,
			loginedTimes=loginedTimes+1,@retValue=1 
			where adminName=@adminName and adminPassword=@adminPassword and isLocked=0 			
	end
PPFOR_GO;

if exists(select name from sysobjects where name='PPFOR_TZB_Administrator_Lock' and type='P')
	drop procedure PPFOR_TZB_Administrator_Lock
PPFOR_GO;

--根据ID锁定管理员
create procedure PPFOR_TZB_Administrator_Lock(@id int)
as
	update PPFOR_TZB_Administrator set isLocked=1 where [id]=@id
PPFOR_GO;

if exists(select name from sysobjects where name='PPFOR_TZB_Administrator_UnLock' and type='P')
	drop procedure PPFOR_TZB_Administrator_UnLock
PPFOR_GO;

--根据ID解锁管理员
create procedure PPFOR_TZB_Administrator_UnLock(@id int)
as
	update PPFOR_TZB_Administrator set isLocked=0 where [id]=@id
PPFOR_GO;

