ALTER TABLE `sb_personcertificate`
ADD COLUMN `payComId`  bigint(20) NULL COMMENT '缴费公司id',
ADD COLUMN `payComName`  varchar(200) NULL COMMENT '缴费公司名称';

ALTER TABLE `security_person`
ADD COLUMN `active`  bit NULL COMMENT '是否有效' AFTER `id`,
ADD COLUMN `create_time`  datetime NULL COMMENT '创建时间' AFTER `user_id`,
ADD COLUMN `init`  varchar(5) NULL COMMENT '是否初始化' AFTER `comTaskFlag`,
ADD COLUMN `photo`  varchar(500) NULL COMMENT '头像' AFTER `init`,
ADD COLUMN `realName`  varchar(200) NULL COMMENT '真实姓名' AFTER `photo`,
ADD COLUMN `phone`  varchar(50) NULL COMMENT '手机号码' AFTER `realName`;

drop view if EXISTS v_security_person;
CREATE
VIEW `v_security_person` AS
SELECT
	`t2`.`id` AS `id`,
	`t4`.`id` AS `companyId`,
	`t3`.`id` AS `relationId`,
	`t2`.`id_number` AS `id_number`,
	`t2`.`realname` AS `realname`,
	`t2`.`phone` AS `phone`,
	`t2`.`photo` AS `photo`,
	`t2`.`identityAuthTime` AS `identityAuthTime`,
	`t2`.`personType` AS `personType`,
	(
		to_days(now()) - to_days(`t2`.`entrydate`)
	) AS `outTimeDay`,
	ifnull(`t2`.`insured`, '0') AS `insured`,

IF (
	(
		substr(`t2`.`id_number`, 17, 1) % 2
	),
	'1',
	'2'
) AS `gender`,

IF (
	`t3`.`active`,
	`t2`.`entrydate`,
	`t3`.`create_time`
) AS `entrydate`,

IF (
	`t3`.`active`,
	NULL,
	`t3`.`update_time`
) AS `leaveDate`,

IF (`t3`.`active`, '1', '0') AS `incumbencyStatus`,
 ifnull(
	`t2`.`identityAuthState`,
	'0'
) AS `identityAuthState`,

IF (
	(
		ifnull(`t2`.`zgcredential`, '') = ''
	),
	0,
	1
) AS `certificateState`,
 `t4`.`name` AS `companyName`,
 `t4`.`orgName` AS `orgname`,
 `t2`.`zgcredential` AS `zgcredential`,
 `t2`.`credential` AS `credential`,
 `t4`.`institutionType` AS `institutionType`,
 `t4`.`orgId` AS `orgid`,
 `t2`.`id_card_address` AS `id_card_address`,
 `t2`.`current_address` AS `current_address`,
 `t2`.`nation` AS `nation`,
 `t2`.`stature` AS `stature`,
 `t2`.`blood` AS `blood`,
 (
	(
		substr(now(), 1, 4) - substr(`t2`.`id_number`, 7, 4)
	) - (
		(
			substr(`t2`.`id_number`, 11, 4) - date_format(now(), '%m%d')
		) > 0
	)
) AS `age`,
 `t2`.`edu` AS `edu`,
 `t2`.`active` AS `active`,
 `t2`.`profession_level` AS `profession_level`,
 ifnull(`t2`.`comTaskFlag`, '0') AS `comTaskFlag`,
 `t4`.`areacode` AS `areacode`
FROM
	(
		(
			(
				`security_person` `t2`
			)
			JOIN `security_company_person` `t3` ON (
				(`t2`.`id` = `t3`.`person_id`)
			)
		)
		JOIN `security_company` `t4` ON (
			(
				`t4`.`id` = `t3`.`company_id`
			)
		)
	)
WHERE
	(`t2`.`active` = TRUE);

DROP VIEW IF EXISTS v_security_person_task;

CREATE VIEW `v_security_person_task` AS SELECT
	`t2`.`id` AS `id`,
	`t4`.`id` AS `companyId`,
	`t3`.`id` AS `relationId`,
	`t2`.`id_number` AS `id_number`,
	`t2`.`realname` AS `realname`,
	`t2`.`phone` AS `phone`,
	`t2`.`photo` AS `photo`,
	`t2`.`personType` AS `personType`,
	`t5`.`comTaskId` AS `comTaskId`,
	(
		to_days(now()) - to_days(`t2`.`entrydate`)
	) AS `outTimeDay`,
	ifnull(`t2`.`insured`, '0') AS `insured`,

IF (
	(
		substr(`t2`.`id_number`, 17, 1) % 2
	),
	'1',
	'2'
) AS `gender`,

IF (
	`t3`.`active`,
	`t2`.`entrydate`,
	`t3`.`create_time`
) AS `entrydate`,

IF (
	`t3`.`active`,
	NULL,
	`t3`.`update_time`
) AS `leaveDate`,

IF (`t3`.`active`, '1', '0') AS `incumbencyStatus`,
 ifnull(
	`t2`.`identityAuthState`,
	'0'
) AS `identityAuthState`,

IF (
	(
		ifnull(`t2`.`zgcredential`, '') = ''
	),
	0,
	1
) AS `certificateState`,
 `t4`.`name` AS `companyName`,
 `t4`.`orgName` AS `orgname`,
 `t2`.`zgcredential` AS `zgcredential`,
 `t2`.`credential` AS `credential`,
 `t4`.`institutionType` AS `institutionType`,
 `t4`.`orgId` AS `orgid`,
 `t2`.`id_card_address` AS `id_card_address`,
 `t2`.`current_address` AS `current_address`,
 `t2`.`nation` AS `nation`,
 `t2`.`stature` AS `stature`,
 `t2`.`blood` AS `blood`,
 `t2`.`age` AS `age`,
 `t2`.`edu` AS `edu`,
 `t2`.`active` AS `active`,
 `t2`.`profession_level` AS `profession_level`,
 ifnull(`t2`.`comTaskFlag`, 0) AS `comTaskFlag`,
 `t4`.`areacode` AS `areacode`
FROM
	(
		(
			(
				(
					(
						`security_person` `t2`
					)
					JOIN `security_company_person` `t3` ON (
						(`t2`.`id` = `t3`.`person_id`)
					)
				)
				JOIN `security_company` `t4` ON (
					(
						`t4`.`id` = `t3`.`company_id`
					)
				)
			)
			LEFT JOIN `com_task_per` `t5` ON ((`t2`.`id` = `t5`.`perId`))
		)
		LEFT JOIN `com_task` `t6` ON (
			(
				(
					`t5`.`comTaskId` = `t6`.`comTaskId`
				)
				AND (`t6`.`isDelete` = '0')
				AND (
					`t6`.`taskStatus` IN ('0', '1', '6')
				)
			)
		)
	)
WHERE
	(`t2`.`active` = TRUE);

###广西内网执行
drop view if EXISTS v_security_person_450200;
CREATE
VIEW `v_security_person_450200` AS
select t1.*
from security_person t1
where INSTR(t1.areacode,'4502')
and t1.active=true;