-- 测试手机号

call P_Base_CheckLogin('');

-- 测试登录名

call P_Base_CheckLogin('sch000001')

-- 测试身份证号

call P_Base_CheckLogin('')

-- 测试学生手机号

call P_Base_CheckLogin('')

drop PROCEDURE IF EXISTS P_Base_CheckLogin;

create procedure  P_Base_CheckLogin(v_loginName      VARCHAR())

label:

BEGIN

        -- 手机号匹配

        SELECT v_loginName REGEXP "^[1][35678][0-9]{9}$" into @checkResult;

        if @checkResult= then

                select p.person_id,p.identity_id,p.person_name  into @person_id,@identity_id,@person_name from  t_base_person p  where p.tel=v_loginName limit ;

                if @person_id is not null THEN

                    select l.login_name,l.login_password  into @login_name,@login_password from t_sys_loginperson l where l.person_id=@person_id and l.IDENTITY_ID=@identity_id;

                    select @login_name as USER_NAME,@person_id as PERSON_ID,@identity_id as IDENTITY_ID ,@person_name as REAL_NAME,@login_password as PASSWORD;

                    LEAVE label;

                end if;

                -- 学生的手机号匹配

                select p.student_id, as identity_id into @person_id,@identity_id  from t_base_student as p where p.STU_TEL=v_loginName limit ;

                if @person_id is not null THEN

                    select l.login_name,l.login_password  into @login_name,@login_password from t_sys_loginperson l where l.person_id=@person_id and l.IDENTITY_ID=@identity_id;

                    select @login_name as USER_NAME,@person_id as PERSON_ID,@identity_id as IDENTITY_ID ,@person_name as REAL_NAME,@login_password as PASSWORD;

                    LEAVE label;

                end if;

        end if;

        -- 身份证号匹配

        select f_base_check_id_number(v_loginName) into @checkResult;

        if @checkResult= then

                select person_id,identity_id,person_name into @person_id,@identity_id,@person_name from  t_base_person p where p.IDENTITY_NUM=v_loginName limit ;

                if @person_id is not null THEN

                    select l.login_name,l.login_password  into @login_name,@login_password from t_sys_loginperson l where l.person_id=@person_id and l.IDENTITY_ID=@identity_id;

                    select @login_name as USER_NAME,@person_id as PERSON_ID,@identity_id as IDENTITY_ID ,@person_name as REAL_NAME,@login_password as PASSWORD;

                    LEAVE label;

                end if;

        end if;

    -- 正常登录名查询

        select l.login_name,person_id,identity_id,l.person_name,l.login_password into @login_name,@person_id,@identity_id,@person_name,@login_password from t_sys_loginperson l where l.login_name=v_loginName limit ;

        if @person_id is not null THEN

            select @login_name as USER_NAME,@person_id as PERSON_ID,@identity_id as IDENTITY_ID ,@person_name as REAL_NAME,@login_password as PASSWORD;

            LEAVE label;

        end if;

END;

drop function if EXISTS f_base_check_id_number;

CREATE  FUNCTION `f_base_check_id_number`(`idnumber` CHAR())

    RETURNS enum('','')

    LANGUAGE SQL

    NOT DETERMINISTIC

    NO SQL

    SQL SECURITY DEFINER

    COMMENT ''

BEGIN

DECLARE status ENUM('','') default '';

DECLARE verify CHAR();

DECLARE sigma INT;

DECLARE remainder INT;

IF length(idnumber) =  THEN

    set sigma = cast(substring(idnumber,,) as UNSIGNED) *

        +cast(substring(idnumber,,) as UNSIGNED) *

        +cast(substring(idnumber,,) as UNSIGNED) *

        +cast(substring(idnumber,,) as UNSIGNED) *

        +cast(substring(idnumber,,) as UNSIGNED) *

        +cast(substring(idnumber,,) as UNSIGNED) *

        +cast(substring(idnumber,,) as UNSIGNED) *

        +cast(substring(idnumber,,) as UNSIGNED) *

        +cast(substring(idnumber,,) as UNSIGNED) *

        +cast(substring(idnumber,,) as UNSIGNED) *

        +cast(substring(idnumber,,) as UNSIGNED) *

        +cast(substring(idnumber,,) as UNSIGNED) *

        +cast(substring(idnumber,,) as UNSIGNED) *

        +cast(substring(idnumber,,) as UNSIGNED) *

        +cast(substring(idnumber,,) as UNSIGNED) *

        +cast(substring(idnumber,,) as UNSIGNED) *

        +cast(substring(idnumber,,) as UNSIGNED) * ;

    set remainder = MOD(sigma,);

    set verify = (case remainder

        when  then '' when  then '' when  then 'X' when  then ''

        when  then '' when  then '' when  then '' when  then ''

        when  then '' when  then '' when  then '' else '/' end

    );

END IF;

IF right(idnumber,) = verify THEN

    set status = '';

END IF;

RETURN status;

END    

SELECT PERSON_ID,IDENTITY_ID,PERSON_NAME as REAL_NAME,LOGIN_NAME as USER_NAME FROM

(

select p.person_id,p.identity_id,p.person_name,p.tel as inputname,l.login_name  from  t_base_person p join t_sys_loginperson l on p.person_id= l.person_id and p.identity_id= l.identity_id

union

select p.person_id,p.identity_id,p.person_name,p.IDENTITY_NUM as inputname,l.login_name  from  t_base_person p join t_sys_loginperson l on p.person_id= l.person_id and p.identity_id= l.identity_id

union

select l.person_id,l.identity_id,l.person_name,s.STU_TEL as inputname,l.login_name  from t_base_student s join t_sys_loginperson l on s.student_id = l.person_id and l.identity_id =

union

 select person_id,identity_id,l.person_name,l.login_name as inputname,l.login_name from t_sys_loginperson l

)  t  WHERE t.inputname = ''