ALTER TABLE `sb_personcertificate`
  ADD COLUMN `needApprove`  bit NULL DEFAULT true COMMENT '是否需要审批' AFTER `contractEnd`;


ALTER TABLE `sb_legalchange`
  ADD COLUMN `socialCode`  varchar(50) NULL COMMENT '社会统一信用代码';


# 2021-11-22
ALTER TABLE `problemperson` ADD COLUMN `comparisonDate`  datetime NULL COMMENT '比对时间' AFTER `update_time`;

drop view if EXISTS v_problemperson;
create view v_problemperson as
SELECT
  ifnull(`pb`.`dispose`, '0') AS `dispose`,
  `pb`.`problemtype` AS `problemtype`,
  `pb`.`idnumber` AS `idnumber`,
  `pb`.`name` AS `name`,
  `pb`.`create_time` AS `create_time`,
  `pb`.`comparisonDate` AS `comparisonDate`,
  `pb`.`handleDate` AS `handleDate`,
  `pb`.`sex` AS `sex`,
  `pb`.`companyname` AS `companyname`,
  `pb`.`companyId` AS `companyid`,
  `pb`.`phone` AS `phone`,
  `pb`.`suggestion` AS `suggestion`,
  `pb`.`orgName` AS `orgName`,
  `pb`.`inform` AS `inform`,
  `pb`.`orgId` AS `orgId`,
  `pb`.`reson` AS `reson`,
  `pb`.`comHandleDate` AS `comHandleDate`,
  `pb`.`personType` AS `personType`,
  `pb`.`active` AS `active`,
  `pb`.`areaCode` AS `areaCode`,
  (
    SELECT

      IF (`scp`.`active`, '1', '0')
    FROM
      `security_company_person` `scp`
    WHERE
      (
          (
              `scp`.`person_id` = `pb`.`personid`
            )
          AND (
              `scp`.`company_id` = `pb`.`companyId`
            )
        )
    ORDER BY
      `scp`.`create_time` DESC
    LIMIT 1
  ) AS `incumbencyStatus`,
  ifnull(`pb`.`disposeCompany`, '0') AS `disposeCompany`,
  `pb`.`companyReson` AS `companyReson`,
  `pb`.`personid` AS `personid`,
  `pb`.`id` AS `id`,
  ifnull(`pb`.`catchState`, '0') AS `catchState`
FROM
  `problemperson` `pb`
WHERE
  (`pb`.`active` = TRUE);