# 20201118    资格证报名
ALTER TABLE `sb_personcertificate`
ADD COLUMN `trainMaterials`  VARCHAR(500)  COMMENT '培训材料';


-- v_person_certificate增加理论学习进度字段
drop view v_person_certificate;

create view v_person_certificate as
select `t`.`id`                                   AS `id`,
       `t`.`Sex`                                  AS `sex`,
       `t`.`Brith`                                AS `brith`,
       `t`.`Nation`                               AS `nation`,
       `t`.`Politics`                             AS `politics`,
       `t`.`Culture`                              AS `culture`,
       `t`.`Zipcode`                              AS `zipcode`,
       `t`.`approvaldate`                         AS `approvaldate`,
       `t`.`address`                              AS `address`,
       `t`.`beforeName`                           AS `beforeName`,
       `t`.`guaranteecar`                         AS `guaranteecar`,
       `t`.`familyPhone`                          AS `familyPhone`,
       `t`.`militaryService`                      AS `militaryService`,
       `t`.`fingerprint`                          AS `fingerprint`,
       `t`.`religion`                             AS `religion`,
       `t`.`nationality`                          AS `nationality`,
       `t`.`birthPlace`                           AS `birthPlace`,
       `t`.`hight`                                AS `hight`,
       `t`.`booldType`                            AS `booldType`,
       `t`.`healthStatus`                         AS `healthStatus`,
       `t`.`nowAddress`                           AS `nowAddress`,
       `t`.`baseRemack`                           AS `baseRemack`,
       `t`.`operate_score`                        AS `operate_score`,
       `t`.`sum_score`                            AS `sum_score`,
       ifnull(`t`.`fingercard_state`, 0)          AS `fingercard_state`,
       `t`.`szss`                                 AS `szss`,
       `t`.`nocrime_photo`                        AS `nocrime_photo`,
       `t`.`exam_address`                         AS `exam_address`,
       `t`.`exam_start`                           AS `exam_start`,
       `t`.`exam_start`                           AS `examTime`,
       `t`.`exam_end`                             AS `exam_end`,
       `t`.`stamina_address`                      AS `stamina_address`,
       `t`.`stamina_startDate`                    AS `stamina_startDate`,
       `t`.`stamina_endDate`                      AS `stamina_endDate`,
       `t`.`signConfirm`                          AS `signConfirm`,
       `t`.`payConfirm`                           AS `payConfirm`,
       `t`.`isMakeUp`                             AS `isMakeUp`,
       `t`.`proState`                             AS `proState`,
       `t`.`makeUpstate`                          AS `makeUpstate`,
       `t`.`Approvalperson`                       AS `approvalperson`,
       `t`.`approvalreason`                       AS `approvalreason`,
       `t`.`applyforstate`                        AS `applyforstate`,
       `t`.`approvalstate`                        AS `approvalState`,
       `t`.`companyName`                          AS `scName`,
       `t`.`Name`                                 AS `name`,
       `t`.`naturalPer`                           AS `naturalPer`,
       `t`.`orgid`                                AS `orgid`,
       ifnull(`t`.`censor_status`, '001')         AS `censorStatus`,
       ifnull(`t`.`confirmOrder`, '0')            AS `confirmOrder`,
       `t`.`trainOrgName`                         AS `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`                                AS `phone`,
       ifnull(`t`.`checkState`, '0')              AS `checkState`,
       `t`.`exam_state`                           AS `examState`,
       `t`.`written_score`                        AS `writtenScore`,
       `t`.`active`                               AS `active`,
       `t`.`payComName`                           AS `payComName`,
       `sa`.`areaCode`                            AS `areaCode`,
       `sa`.`id`                                  AS `approvalId`,
       `sa`.`processInstanceId`                   AS `processInstanceId`,
       -- 增加理论学习进度字段
       `t`.`learnRate`                            AS `learnRate`
from (`sb_personcertificate` `t`
         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
                                              from `security_certificate`
                                              where ((`security_certificate`.`active` = 1) and
                                                     (`security_certificate`.`securitypersonid` = `t`.`id`) and
                                                     (`security_certificate`.`isOldData` = '1'))))));