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

--创建管理员登录日志表
if not exists(select name from sysobjects where name='PPFOR_TZB_AdministratorLog' and type='U')
	create table PPFOR_TZB_AdministratorLog
	(
		[id] int identity(1,1) not null primary key,	--自动编号，从1开始递增
		adminName nvarchar(20) COLLATE Chinese_PRC_CS_AS not null REFERENCES [PPFOR_TZB_Administrator]([adminName]) ON DELETE CASCADE,				--管理员账号
		adminLoginedIP varchar(15) not null,			--登录IP
		adminLoginedSystem nvarchar(255) not null,		--操作系统
		adminIsSuccessed bit not null default(0),		--是否登录成功
		adminLoginedTime datetime not null,				--登录时间
	)
PPFOR_GO;  

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

--添加管理员日志
create procedure PPFOR_TZB_AdministratorLog_Add (@adminName nvarchar(20),@adminLoginedIP nvarchar(15),
	@adminLoginedSystem varchar(255),@adminIsSuccessed bit,	@adminLoginedTime datetime,
	@id int output)
as
	begin
		insert into PPFOR_TZB_AdministratorLog values(@adminName,@adminLoginedIP,@adminLoginedSystem,
			@adminIsSuccessed,@adminLoginedTime)
		if(@@rowcount>0)
			set @id=@@identity
		else
			set @id=0
	end
PPFOR_GO;

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

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

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

--分页返回所有管理员日志
create procedure PPFOR_TZB_AdministratorLog_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_AdministratorLog
		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,adminLoginedIP,adminLoginedSystem,
			adminIsSuccessed,adminLoginedTime from PPFOR_TZB_AdministratorLog '
	
		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;
