# 20201014 补贴资金发放
ALTER TABLE `grade_subsidy_roster`
ADD COLUMN `platSerFee`  varchar(50) NULL COMMENT '平台服务费';

ALTER TABLE `sb_grade_subsidy_batch`
ADD COLUMN `platSerFee`  varchar(50) NULL COMMENT '平台服务费',
ADD COLUMN `orderNo`  varchar(50) NULL COMMENT '交易流水号',
ADD COLUMN `grantState`  char(5) NULL COMMENT '补贴发放状态',
ADD COLUMN `reason`  varchar(500) NULL COMMENT '公示不通过原因';


#联名卡 新增areacode

drop view if EXISTS v_person_bank;
CREATE
VIEW `v_person_bank` AS
select  t1.id, 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',
t1.areaCode as'areaCode',
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;