

drop view if EXISTS v_person_bank;
CREATE
VIEW `v_person_bank` AS
select  t1.id,t1.areacode, t1.xm as `name`,
t1.idnum,
IFNULL(tt.phone,t1.phone) as phone,
tt.`name` as 'companyName',
t1.zsbh as 'cerNo',
t1.fzrq as 'cerDate',
t1.havephoto as 'havePhoto',
t1.isDraw as 'isDraw',
IFNULL(tt.incumbencyStatus,0) as 'incumbencyStatus',
if(t3.create_time is null,0,1) as 'bankState',
t3.activeDate,
t3.create_time as 'bankDate'
from security_certificate t1
LEFT JOIN (
select t5.`name`,t2.id_number,t2.phone,t2.incumbencyStatus from security_person t2
INNER JOIN security_company_person t4 on t2.id= t4.person_id and t4.active=true
INNER JOIN security_company t5 on t4.company_id = t5.id and t5.active=true
where t2.active=true and t2.incumbencyStatus=1
)tt on t1.idnum = tt.id_number
LEFT JOIN bank_account t3 on t1.idnum = t3.idNum and t3.active=TRUE
where t1.active=true and ifnull(t1.state,1)=1;

