drop view if EXISTS v_sb_legalchange;
CREATE
VIEW `v_sb_legalchange`AS
SELECT
          a.id, --  主建
          IFNULL(a.oldcompanyname, sc.name) AS companyname,   --  公司名称
          IFNULL(a.oldaddress, sc.location) AS address,   --  公司地址
          IFNULL(a.legalname, sc.legal_person) AS legalname, --  法人
          DATE_FORMAT(s.create_time,'%Y-%m-%d') as createTime,   --  提交时间
          DATE_FORMAT(s.update_time,'%Y-%m-%d %H:%i:%S') as updateTime,   --  更新时间
          DATE_FORMAT(a.officetime,'%Y-%m-%d') as officetime,  -- 任职时间
          a.legalcardtype,   -- 证件类型
          a.legalcardnumber, -- 法人身份证号
          u.phone as legalphone , --  法人手机
          a.companyid,   --  公司ID
          a.approvalstate, --  审批状态
          a.cardState, -- 制证状态
          s.processInstanceId,  --  审批流程ID
          s.id as approvalId,   -- 审批记录ID
          s.nextPerson,   --  下一个审批人
          a.district,
          a.licencenum,
          a.legalsex,
          a.active,
          a.legalcensorstatus,
          a.ndlegalcensorstatus,
          a.chargecensorstatus,
          a.oldregisteredMoney,
          a.bgregisteredMoney,
          a.otherSecScopeType,
          a.ndlegalname,
          a.ndlegalsex,
          a.ndlegalbrith,
          a.ndlegalculture,
          a.ndlegalcardtype,
          a.ndlegalcardnum,
          a.ndlegalnationality,
          a.ndlegalphone,
          a.ndlegaladdress,
          a.ndleaglsjaddress,
          a.approvalperson,
          a.approvalreason,
          a.approvaldate,
          a.oldsecurityScopes,
          a.template_rar,
          a.giveLicenseDate,
          a.expireRemarks,
          a.szss,
          sc.securityScopeType as securityScopes ,
          sc.institutionType,  --  企业类型
          CONCAT(IF(ISNULL(a.securityScopes),'','服务范围变更,'),IF(ISNULL(a.giveLicenseDate),'','到期换证,'),
          IF(ISNULL(a.companyname),'','公司名称变更,'),
          IF(ISNULL(a.address),'','地址变更,'),
          IF(ISNULL(a.legalname),'','法人变更,'),
          IF(ISNULL(a.bgregisteredMoney),'','注册资金变更,'))
          as changeName -- 变更项目
        FROM
          sb_legalchange a
        INNER JOIN security_company sc ON sc.user_id = a.userid
        LEFT JOIN `user` u on u.id= sc.user_id
        AND sc.active = 1
        LEFT JOIN sys_approval s ON a.id = s.businessid
        AND s.active = TRUE
        WHERE
          a.active = TRUE
          AND s.businesstype = '007'