ALTER TABLE `company_certificate`
ADD COLUMN `areaCode`  varchar(10) NULL COMMENT '区域代码',
ADD COLUMN `orgId`  bigint(20) NULL COMMENT '监管机构id';

ALTER TABLE `grade_Certificate`
ADD COLUMN `cerDate`  datetime NULL COMMENT '发证日期';

ALTER TABLE `bank_account`
ADD COLUMN `initial`  varchar(2) NULL COMMENT '是否初始 0非初始（已激活） 1是初始（未激活）';

DROP VIEW if exists v_security_situation;
create view v_security_situation as
select `sc`.`name`                                                     AS `NAME`,
       `sc`.`orgName`                                                  AS `orgName`,
       (select count(1)
        from (`security_company_person` `scp`
                 join `security_person` `sp`)
        where ((`scp`.`company_id` = `sc`.`id`) and (`sp`.`personType` <> '1') and (`scp`.`person_id` = `sp`.`id`) and
               (`scp`.`active` = TRUE)))                               AS `countPerson`,
       (select count(1)
        from `com_task` `c`
        where ((`c`.`comId` = `sc`.`comId`) and (`c`.`isDelete` = 0))) AS `stationingNum`,
       (select count(1)
        from `com_contract` `c`
        where ((`c`.`comId` = `sc`.`comId`) and (`c`.`isDelete` = 0) and
               (`c`.`contractType` = '01')))                           AS `contractNum`,
       (select count(1)
        from `com_contract` `c`
        where ((`c`.`comId` = `sc`.`comId`) and (`c`.`isDelete` = 0) and
               (`c`.`contractType` = '02')))                           AS `personContractNum`,
       (select count(1)
        from ((`security_company_person` `scp` join `security_person` `sp`)
                 join `user` `u`)
        where ((`sp`.`incumbencyStatus` = '1') and (`u`.`id` = `sp`.`user_id`) and (`sp`.`id` = `scp`.`person_id`) and
               (`scp`.`active` = TRUE) and (`scp`.`company_id` = `sc`.`id`) and
               (isnull(`sp`.`zgcredential`) or (`sp`.`zgcredential` = '')) and
               (`sp`.`personType` <> '1')))                            AS `unCertificate`,
       `sc`.`areacode`                                                 AS `areacode`,
       `sc`.`orgId`                                                    AS `orgId`,
       1                                                               AS `active`,
       (
           SELECT count(1)
           FROM security_guard guard
           WHERE guard.active = true
             and guard.security_company = sc.name
       )                                                               as securityGuardCount,
       (
           SELECT count(1)
           FROM sb_investor investor
           where investor.userid = sc.user_id
             and investor.active = true
       )                                                               as managerCount,
       ifnull(sc.isuredNumber, 0)                                      as isuredNumber,
       if(sc.security_amount - (
           SELECT count(DISTINCT p.id)
           FROM security_person p
                    INNER JOIN security_company_person scp ON scp.person_id = p.id AND scp.active = TRUE
                    INNER JOIN com_task_schedul cts ON cts.perId = p.id AND cts.active = TRUE
                    INNER JOIN com_task ct ON ct.comTaskId = cts.comTaskId AND ct.isdelete = '0'
           WHERE p.active = TRUE
             and curdate() BETWEEN ct.startDate AND ct.endDate
             AND scp.company_id = sc.id
       ) < 0, 0, sc.security_amount - (
           SELECT count(DISTINCT p.id)
           FROM security_person p
                    INNER JOIN security_company_person scp ON scp.person_id = p.id AND scp.active = TRUE
                    INNER JOIN com_task_schedul cts ON cts.perId = p.id AND cts.active = TRUE
                    INNER JOIN com_task ct ON ct.comTaskId = cts.comTaskId AND ct.isdelete = '0'
           WHERE p.active = TRUE
             and curdate() BETWEEN ct.startDate AND ct.endDate
             AND scp.company_id = sc.id
       ))                                                              AS noSchedulePersons
from (`security_company` `sc`
         join `user` `u`
              on (((`sc`.`user_id` = `u`.`id`) and (`u`.`active` = TRUE) and (`sc`.`active` = TRUE) and
                   (`sc`.`institutionType` in ('0102', '0105')))));