CREATE TABLE `sb_trainChange` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `active` bit(1) DEFAULT NULL COMMENT '是否可用',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  `update_by` varchar(50) DEFAULT NULL COMMENT '修改者',
  `companyname` varchar(200) DEFAULT NULL COMMENT '单位名称',
  `address` varchar(200) DEFAULT NULL COMMENT '地址',
  `licencenum` varchar(50) DEFAULT NULL COMMENT '许可证',
  `legalname` varchar(50) DEFAULT NULL COMMENT '法人姓名',
  `legalsex` varchar(50) DEFAULT NULL COMMENT '法人性别',
  `officetime` datetime DEFAULT NULL COMMENT '任职时间',
  `trainCardtype` varchar(3) DEFAULT NULL COMMENT '法人证件类型',
  `trainCardnumber` varchar(20) DEFAULT NULL COMMENT '法人证件号码',
  `ndlegalname` varchar(50) DEFAULT NULL COMMENT '拟定法人名称',
  `ndlegalsex` varchar(3) DEFAULT NULL COMMENT '拟定法人性别',
  `ndlegalbrith` VARCHAR(20) DEFAULT NULL COMMENT '拟定法人出生年月',
  `ndtrainCulture` varchar(3) DEFAULT NULL COMMENT '拟定法人文化程度',
  `ndtrainCardtype` varchar(3) DEFAULT NULL COMMENT '拟定法人证件类型',
  `ndtrainCardnum` varchar(20) DEFAULT NULL COMMENT '拟定法人证件号码',
  `ndlegalnationality` varchar(50) DEFAULT NULL COMMENT '拟定法人国籍',
  `ndlegalphone` varchar(20) DEFAULT NULL COMMENT '拟定法人手机号码',
  `ndlegaladdress` varchar(200) DEFAULT NULL COMMENT '拟定法人户籍所在地址',
  `ndleaglsjaddress` varchar(200) DEFAULT NULL COMMENT '拟定法人实际地址',
  `USERID` bigint(32) DEFAULT NULL COMMENT '申请公司账号ID',
  `approvalstate` varchar(3) DEFAULT NULL COMMENT '审批状态',
  `approvalperson` varchar(20) DEFAULT NULL COMMENT '审批人',
  `approvalreason` varchar(200) DEFAULT NULL COMMENT '审批原因',
  `approvaldate` datetime DEFAULT NULL COMMENT '审批时间',
  `template_rar` varchar(500) DEFAULT NULL COMMENT '上传模板压缩包',
  `province` varchar(50) DEFAULT NULL COMMENT '所在地省代码',
  `city` varchar(50) DEFAULT NULL COMMENT '所在地市代码',
  `district` varchar(50) DEFAULT NULL COMMENT '所在地县（区）代码',
  `orgid` int(11) DEFAULT NULL COMMENT '派出所ID',
  `cityOrgId` int(11) DEFAULT NULL COMMENT '市机构id',
  `districtOrgId` int(11) DEFAULT NULL COMMENT '区县机构id',
  `trainCensorstatus` varchar(3) DEFAULT NULL COMMENT '原法定代表人审查状态',
  `ndtrainCensorstatus` varchar(3) DEFAULT NULL COMMENT '拟定法定代表人审查状态',
  `chargecensorstatus` varchar(3) DEFAULT NULL COMMENT '单位负责人审查状态',
  `szss` varchar(50) DEFAULT NULL COMMENT '所在地省市县（区）',
  `bgregisteredMoney` decimal(14,2) DEFAULT NULL COMMENT '变更注册资本',
  `securityScopes` varchar(255) DEFAULT NULL COMMENT '变更后服务范围',
  `otherSecScopeType` varchar(20) DEFAULT NULL COMMENT '其他保安服务范围名称(保安范围为其他则此列必填)',
  `policeid` int(11) DEFAULT NULL COMMENT '受理民警ID',
  `oldsecurityScopes` varchar(50) DEFAULT NULL COMMENT '原服务范围',
  `oldcompanyname` varchar(200) DEFAULT NULL COMMENT '原公司名称',
  `oldaddress` varchar(200) DEFAULT NULL COMMENT '原办公地址',
  `oldregisteredMoney` varchar(50) DEFAULT NULL COMMENT '原注册资本',
  `companyId` bigint(20) NOT NULL,
  `cardState` varchar(255) DEFAULT NULL COMMENT '证书状态1已制证0未制证',
  `giveLicenseDate` varchar(20) DEFAULT NULL COMMENT '原发证日期',
  `expireRemarks` varchar(200) DEFAULT NULL COMMENT '到期换证备注',
  `socialCode` varchar(50) DEFAULT NULL COMMENT '社会统一信用代码',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='培训单位项目变更';

CREATE TABLE `sb_trainChangeworkexperience` (
    `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
    `active` varchar(255) DEFAULT NULL COMMENT '是否可用',
    `create_time` datetime DEFAULT NULL COMMENT '创建时间',
    `update_time` datetime DEFAULT NULL COMMENT '修改时间',
    `update_by` varchar(200) NOT NULL COMMENT '修改者',
    `companyname` varchar(200) DEFAULT NULL COMMENT '工作单位名称',
    `post` varchar(100) DEFAULT NULL COMMENT '岗位/职务',
    `startdate` datetime DEFAULT NULL COMMENT '开始时间',
    `enddate` datetime DEFAULT NULL COMMENT '结束时间',
    `remarks` varchar(255) DEFAULT NULL COMMENT '备注',
    `trainChangeid` int(11) DEFAULT NULL COMMENT '法人变更申请ID',
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='培训单位项目变更 法人工作经历';

drop view if exists v_sb_trainChange;
CREATE VIEW `v_sb_trainChange` AS SELECT
  `a`.`id` AS `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`.`trainCardtype` AS `trainCardtype`,
  `a`.`trainCardnumber` AS `trainCardnumber`,
  `u`.`phone` AS `legalphone`,
  `a`.`companyId` AS `companyid`,
  `a`.`approvalstate` AS `approvalstate`,
  `a`.`cardState` AS `cardState`,
  `s`.`processInstanceId` AS `processInstanceId`,
  `s`.`id` AS `approvalId`,
  `s`.`nextPerson` AS `nextPerson`,
  `a`.`district` AS `district`,
  `a`.`licencenum` AS `licencenum`,
  `a`.`legalsex` AS `legalsex`,
  `a`.`active` AS `active`,
  `a`.`trainCensorstatus` AS `trainCensorstatus`,
  `a`.`ndtrainCensorstatus` AS `ndtrainCensorstatus`,
  `a`.`chargecensorstatus` AS `chargecensorstatus`,
  `a`.`ndlegalname` AS `ndlegalname`,
  `a`.`ndlegalsex` AS `ndlegalsex`,
  `a`.`ndlegalbrith` AS `ndlegalbrith`,
  `a`.`ndtrainCulture` AS `ndtrainCulture`,
  `a`.`ndtrainCardtype` AS `ndtrainCardtype`,
  `a`.`ndtrainCardnum` AS `ndtrainCardnum`,
  `a`.`ndlegalnationality` AS `ndlegalnationality`,
  `a`.`ndlegalphone` AS `ndlegalphone`,
  `a`.`ndlegaladdress` AS `ndlegaladdress`,
  `a`.`ndleaglsjaddress` AS `ndleaglsjaddress`,
  `a`.`approvalperson` AS `approvalperson`,
  `a`.`approvalreason` AS `approvalreason`,
  `a`.`approvaldate` AS `approvaldate`,
  `a`.`template_rar` AS `template_rar`,
  `a`.`szss` AS `szss`,
  `sc`.`institutionType` AS `institutionType`,
  concat(
      IF (
          isnull(`a`.`companyname`),
          '',
          '公司名称变更,'
        ),

      IF (
          isnull(`a`.`address`),
          '',
          '地址变更,'
        ),

      IF (
          isnull(`a`.`legalname`),
          '',
          '法人变更,'
        )
    )AS `changeName`
FROM
  (
   (
     (
       `sb_trainChange` `a`
         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` = '013')
    )