ALTER TABLE `yearreportdata`
ADD COLUMN `legalPhone`  varchar(20) NULL COMMENT '法人手机' AFTER `areaCode`,
ADD COLUMN `legalPerson`  varchar(50) NULL COMMENT '法定代表人' AFTER `legalPhone`,
ADD COLUMN `legalPersonNo`  varchar(20) NULL COMMENT '法人身份证号' AFTER `legalPerson`,
ADD COLUMN `publicSecurityLicenseNumber`  varchar(50) NULL COMMENT '保安服务（培训）许可证编号' AFTER `legalPersonNo`,
ADD COLUMN `securityScope`  varchar(500) NULL COMMENT '企业类型（人防、技防、押运、培训）' AFTER `publicSecurityLicenseNumber`,
ADD COLUMN `socialCode`  varchar(50) NULL COMMENT '注册号/统一社会信用代码' AFTER `securityScope`,
ADD COLUMN `tel`  varchar(20) NULL COMMENT '企业联系电话' AFTER `socialCode`,
ADD COLUMN `location`  varchar(500) NULL COMMENT '企业通信地址' AFTER `tel`,
ADD COLUMN `registeredMoney`  varchar(20) NULL COMMENT '注册资本（万元）' AFTER `location`,
ADD COLUMN `postalcode`  varchar(20) NULL COMMENT '邮政编码' AFTER `registeredMoney`;


DROP TABLE IF EXISTS `sb_yearreport_investor`;
CREATE TABLE `sb_yearreport_investor` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `yearReportId` int(11) DEFAULT NULL COMMENT '企业年报ID',
  `userid` int(11) DEFAULT NULL COMMENT '用户ID',
  `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 '修改者',
  `investors_type` varchar(3) DEFAULT NULL COMMENT '出资人类型',
  `name` varchar(50) DEFAULT NULL COMMENT '出资人姓名',
  `id_number` varchar(50) DEFAULT NULL COMMENT '出资人身份证号',
  `nation` varchar(50) DEFAULT NULL COMMENT '民族',
  `address` varchar(200) DEFAULT NULL COMMENT '地址',
  `national` varchar(50) DEFAULT NULL COMMENT '国籍',
  `sex` varchar(3) DEFAULT NULL COMMENT '性别',
  `phone` varchar(50) DEFAULT NULL COMMENT '手机号',
  `nowaddress` varchar(200) DEFAULT NULL COMMENT '地址',
  `funded_number` decimal(14,2) DEFAULT NULL COMMENT '出资金额',
  `stock_proportion` decimal(12,2) DEFAULT NULL COMMENT '股份占比',
  `ratio_transfer` decimal(12,2) DEFAULT NULL COMMENT '股权转让比例',
  `shareholder_before` varchar(255) DEFAULT NULL COMMENT '转让前股东',
  `ratio_befor` decimal(12,2) DEFAULT NULL COMMENT '转让前股东比例',
  `transfer_time` varchar(20) DEFAULT NULL COMMENT '转让时间',
  `change_record` varchar(10) DEFAULT NULL COMMENT '股权变更记录,0：没有变更，1：有',
  KEY `ak_investor_key` (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=121 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;


DROP TABLE IF EXISTS `sb_yearreport_insurance`;
CREATE TABLE `sb_yearreport_insurance` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `active` bit(1) DEFAULT NULL COMMENT '是否可用',
  `update_by` varchar(50) DEFAULT NULL COMMENT '修改者',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `yearReportId` int(11) DEFAULT NULL COMMENT '企业年报ID',
  `company_id` int(11) DEFAULT NULL COMMENT '公司ID',
  `insured_num` int(11) DEFAULT NULL COMMENT '在保人数',
  `insurance_company` varchar(50) DEFAULT NULL COMMENT '保险公司名称',
  `protection_date` datetime DEFAULT NULL COMMENT '起保日期',
  `insurance_product` varchar(50) DEFAULT NULL COMMENT '保险产品名称',
  `premium_person` decimal(8,2) DEFAULT NULL COMMENT '每人保费',
  `ywsg` varchar(100) DEFAULT NULL COMMENT '意外身故/伤残',
  `jtyw` varchar(100) DEFAULT NULL COMMENT '交通意外',
  `ywyl` varchar(100) DEFAULT NULL COMMENT '意外医疗',
  `zdcj` varchar(100) DEFAULT NULL COMMENT '重大疾病',
  `jyjt` varchar(100) DEFAULT NULL COMMENT '住院津贴',
  `flfy` varchar(100) DEFAULT NULL COMMENT '法院费用',
  `end_date` varchar(100) DEFAULT NULL COMMENT '结束日期',
  `insurance_number` varchar(100) DEFAULT NULL COMMENT '保单号',
  `insurance_pic` varchar(100) DEFAULT NULL COMMENT '保单图片',
  UNIQUE KEY `insurnace_pk` (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=567 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

###旧数据处理

INSERT INTO sb_yearreport_investor (
	yearReportId,
	userid,
	active,
	create_time,
	update_time,
	update_by,
	investors_type,
	`name`,
	id_number,
	nation,
	address,
	national,
	sex,
	phone,
	nowaddress,
	funded_number,
	stock_proportion,
	ratio_transfer,
	shareholder_before,
	ratio_befor,
	transfer_time,
	change_record
) SELECT
	yd.id,
	sbi.userid,
	sbi.active,
	sbi.create_time,
	sbi.update_time,
	sbi.update_by,
	sbi.investors_type,
	sbi.`name`,
	sbi.id_number,
	sbi.nation,
	sbi.address,
	sbi.national,
	sbi.sex,
	sbi.phone,
	sbi.nowaddress,
	sbi.funded_number,
	sbi.stock_proportion,
	sbi.ratio_transfer,
	sbi.shareholder_before,
	sbi.ratio_befor,
	sbi.transfer_time,
	sbi.change_record
FROM
	yearreportdata yd,
	security_company sc,
	`user` u,
	sb_investor sbi
WHERE
	yd.active = 1
AND sc.active = 1
AND yd.userid = sc.user_id
AND u.active = 1
AND u.id = sc.user_id
AND sbi.active = 1
AND sbi.userid = u.id
AND yd.id NOT IN (
	SELECT
		syi.yearReportId
	FROM
		sb_yearreport_investor syi
);


INSERT INTO sb_yearreport_insurance(
	yearReportId,
	active,
	create_time,
	update_time,
	update_by,
	company_id,
	insurance_company,
	insured_num,
	protection_date,
	insurance_product,
	premium_person,
	ywsg,
	jtyw,
	ywyl,
	zdcj,
	jyjt,
	flfy,
	end_date,
	insurance_number,
	insurance_pic
) SELECT
	yd.id,
	sbi.active,
	sbi.create_time,
	sbi.update_time,
	sbi.update_by,
	sbi.company_id,
	sbi.insurance_company,
	sbi.insured_num,
	sbi.protection_date,
	sbi.insurance_product,
	sbi.premium_person,
	sbi.ywsg,
	sbi.jtyw,
	sbi.ywyl,
	sbi.zdcj,
	sbi.jyjt,
	sbi.flfy,
	sbi.end_date,
	sbi.insurance_number,
	sbi.insurance_pic
FROM
	yearreportdata yd,
	security_company sc,
	`user` u,
	security_company_insurance sbi
WHERE
	yd.active = 1
AND sc.active = 1
AND yd.userid = sc.user_id
AND u.active = 1
AND u.id = sc.user_id
AND sbi.active = 1
AND sbi.company_id = u.id
AND yd.id NOT IN (
	SELECT
		syi.yearReportId
	FROM
		sb_yearreport_insurance syi
);

UPDATE yearreportdata yd,
 security_company sc,
 `user` u
SET yd.companyName = sc.`name`,
 yd.areaCode = sc.areacode,
 yd.legalPhone = u.phone,
 yd.legalPerson = sc.legal_person,
 yd.legalPersonNo = sc.legalPersonNo,
 yd.publicSecurityLicenseNumber = sc.public_security_license_number,
 yd.socialCode = sc.social_code,
 yd.tel = sc.tel,
 yd.location = sc.location,
 yd.registeredMoney = sc.registered_money,
 yd.postalcode = sc.postalcode,
 yd.securityScope = (
	SELECT
		GROUP_CONCAT(ss.`name` SEPARATOR ',')
	FROM
		security_company_scope scs,
		security_scope ss
	WHERE
		ss.id = scs.scope_id
	AND scs.company_id = sc.id
)
WHERE
	yd.active = 1
AND sc.active = 1
AND yd.userid = sc.user_id
AND u.active = 1
AND u.id = sc.user_id;