CREATE OR REPLACE FUNCTION web_login(web_username character varying, web_pwd character varying, web_ip character varying)
RETURNS integer AS
$BODY$
declare
len Integer:=0; -- -2 fail -3 lockip
u_locktime character varying(10);
u_cleartime character varying(50);
u_count Integer;
user_id Integer;
log_id Integer;
cal interval;
BEGIN
select config_value into u_count from as_all_config where config_item='U_AllowCount' ;
select config_value into u_cleartime from as_all_config where config_item='U_LimitDay' ;
cal:=(u_cleartime||' days') as inverval;
select config_value into u_locktime from as_all_config where config_item='U_Locktime' ;
--判断用户是否已经锁定
select serialno into len from web_user_log where login_id =web_username and last_ip=web_ip and last_lock_time<now() and last_count= u_count limit 1;
if len>0 then
insert into as_log(log_fromtype, log_type,log_text, log_level)values(8,4,'用户 '||web_username||'登录失败 ip锁定',1);
RETURN -3;
end if;
--判断用户失败次数
select last_count,serialno into len,log_id from web_user_log where login_id =web_username and last_ip=web_ip and date_trunc('day',last_fail_time)=date_trunc('day',now()) order by last_fail_time desc limit 1;
if len>3 then
update web_user_log set last_lock_time=now() where serialno=log_id;
insert into as_log(log_fromtype, log_type,log_text, log_level)values(8,4,'用户 '||web_username||'登录失败 ip锁定',1);
RETURN -3;
end if;
--正常操作
select last_count, serialno into len ,log_id from web_user_log where login_id =web_username and last_ip=web_ip and date_trunc('day',last_fail_time)=date_trunc('day',now()) order by last_fail_time desc limit 1 ;
select serialno into user_id from web_user where login_id = web_username and login_pwd=web_pwd limit 1;
delete from web_user_log where login_id =web_username and last_lock_time<=date_trunc('day',((now()-justify_days(cal))));
if user_id>0 then
insert into as_log( log_fromtype, log_type, log_text, log_level)values(8,4,'用户 '||web_username||'登录成功',1);
RETURN user_id;
elsif len is null or len=0 then
insert into web_user_log(last_count, last_ip, login_pwd, login_id)values(1,web_ip,web_pwd,web_username);
else
update web_user_log set last_count=last_count+1 where serialno=log_id;
end if;
insert into as_log(log_fromtype, log_type, log_text, log_level)values(8,4,'用户 '||web_username||'登录失败',1);
RETURN -2;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION web_login(character varying, character varying, character varying) OWNER TO ema_user;