-- 资格证发证机关配置 20200903
ALTER TABLE `orgno`
ADD COLUMN `orgName`  varchar(200) NULL COMMENT '发证机关' AFTER `areacode`;

-- 资格证发证机关配置 20200903 end

ALTER TABLE `security_certificate`
ADD COLUMN `seq`  int NULL DEFAULT 1000 ;


ALTER TABLE `security_certificate`
ADD COLUMN `isDraw`  varchar(5) NULL COMMENT '推送渤海银行';

### 20200825
ALTER TABLE `security_certificate`
ADD COLUMN `headImg`  varchar(500) NULL COMMENT '头像';
# end

-- ALTER TABLE `security_person`
-- ADD COLUMN `comIdPhoto`  varchar(500) NULL COMMENT '企业上传的证件照' AFTER `idPhoto`;


-- 20200825 v_person_bank 是否有证书照片 是否换发联名卡
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',
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;




-- #dt20200827

drop view if EXISTS v_person_certificate;
CREATE
VIEW `v_person_certificate` AS
SELECT
t.id,t.sex,t.brith,t.nation,t.politics,t.culture,t.zipcode,t.approvaldate,t.address,t.beforeName,
t.guaranteecar,t.familyPhone,t.militaryService,t.fingerprint,t.religion,t.nationality,t.birthPlace,
t.hight,t.booldType,t.healthStatus,t.nowAddress,t.baseRemack,t.operate_score,t.sum_score,t.fingercard_state,
t.szss,t.nocrime_photo,t.exam_address,
t.exam_start,
t.exam_start as examTime,
t.exam_end,
t.stamina_address,t.stamina_startDate,
t.stamina_endDate,t.signConfirm,t.payConfirm,t.isMakeUp,t.proState,t.makeUpstate,t.approvalperson,
t.approvalreason,t.applyforstate,
t.approvalstate as approvalState,   -- 报名审批状态
t.companyname as scName, -- 报名公司名称
t.name,  -- 姓名
t.orgid, -- 机构id
IFNULL(t.censor_status,'001') as censorStatus, -- 背景筛查
t.trainOrgName,   -- 所属培训机构
date_format(t.create_time,'%Y-%m-%d')as createTime,    -- 报名日期
IFNULL(t.fee_state,'0') as feeState,      -- 缴费情况
IFNULL(t.test_state,'2') as testState,    -- 考试结果
t.Cardnumber as cardNumber,         -- 身份证号
t.phone ,        -- 手机号
IFNULL(t.checkState,'0') as checkState,    -- 材料核对状态
t.exam_state as examState,    -- 考试安排
t.written_score as writtenScore,   -- 笔试成绩
t.active,
t.payComName,
sa.areaCode,
sa.id as approvalId,
sa.processInstanceId
FROM
sb_personcertificate t
INNER JOIN sys_approval sa ON t.id = sa.businessid and sa.businesstype='004' and sa.active =true
WHERE t.active = true
AND NOT EXISTS (SELECT 1   /*<!-- 排除历史导入的保安员 by llc 2018-08-21 -->*/
FROM security_certificate
WHERE active = 1
AND securitypersonid = t.id
AND isOldData = '1');