drop view if EXISTS v_security_person;
CREATE
VIEW `v_security_person`AS
SELECT
        p.id,
        p.personType,
        p.insured,
        p.credential,
        p.profession_level,
        p.edu,
        p.age,
        p.blood,
        p.stature,
        p.native,
        p.nation,
        u.phone,
        p.id_card_address,
        p.current_address,
        p.zgcredential,
        u.realname,
        p.id_number,
        sc.`name` as companyName,
        sc.id as companyId,
        sc.orgName,
        sc.orgId,
        sc.areacode,
        cp.create_time,
        cp.update_time,
        u.active,
        ifnull(p.identityAuthState,'0') as identityAuthState,
        if(ifnull(p.zgcredential,'')='',0,1) as certificateState,
        if(cp.active,p.entrydate,cp.create_time) as entrydate,
        if(cp.active,null,cp.update_time) as leaveDate,
        if(cp.active,'1','0') as incumbencyStatus,
        datediff(now(),p.entrydate) as outTimeDay,
        IF(mod(SUBSTR(id_number,17,1),2),'1','2')  as gender,
        ( select CONCAT(b.`name`,a.name) from sysarea a LEFT JOIN sysarea b on (a.parentid = b.id)  where a.`code` = CONCAT(left(id_number,4),'00')) as nativePlace
        from `user` u
        INNER JOIN security_person p on p.user_id = u.id
        INNER JOIN security_company_person cp on cp.person_id = p.id
        LEFT JOIN security_company sc on sc.id=cp.company_id
        WHERE
        u.active=TRUE