create VIEW `v_security_person_temp_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`.`comTempTaskId` AS `comTempTaskId`,
	(
		to_days(now()) - to_days(`t3`.`entryDate`)
	) AS `outTimeDay`,
	ifnull(`t2`.`insured`, '0') AS `insured`,
IF (
	(
		substr(`t2`.`id_number`, 17, 1) % 2
	),
	'1',
	'2'
) AS `gender`,
 `t3`.`entryDate` AS `entrydate`,
 `t3`.`leaveDate` AS `leaveDate`,
 `t3`.`create_time` AS `inputDate`,

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

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_temp_task_per` `t5` ON ((`t2`.`id` = `t5`.`perId`))
		)
		LEFT JOIN `com_temp_task` `t6` ON (
			(
				(
					`t5`.`comTempTaskId` = `t6`.`comTempTaskId`
				)
				AND (`t6`.`active` = true)
				AND (
					`t6`.`taskStatus` IN ('0', '1', '6')
				)
			)
		)
	)
WHERE
	(`t2`.`active` = TRUE) ;


ALTER 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(`t3`.`entryDate`)
	) AS `outTimeDay`,
	ifnull(`t2`.`insured`, '0') AS `insured`,

IF (
	(
		substr(`t2`.`id_number`, 17, 1) % 2
	),
	'1',
	'2'
) AS `gender`,
 `t3`.`entryDate` AS `entrydate`,
 `t3`.`leaveDate` AS `leaveDate`,
 `t3`.`create_time` AS `inputDate`,

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`,
 `t4`.`districtOrgName` AS `districtOrgName`,
 `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) ;

ALTER TABLE `enrolment_leave_beijing`
ADD COLUMN `area`  varchar(50) NULL COMMENT '目的地详区县code' AFTER `arrival_time`,
ADD COLUMN `areaName`  varchar(200) NULL COMMENT '目的地详区县' AFTER `area`;