### 20200825
ALTER TABLE `security_certificate`
ADD COLUMN `headImg`  varchar(500) NULL COMMENT '头像' AFTER `seq`;
# end

ALTER TABLE `bank_account`
ADD COLUMN `bankAccountType` VARCHAR(5) DEFAULT NULL COMMENT '账户类型 编码值 ，1为I类卡，2为II类卡',
ADD COLUMN `push` bit(1) DEFAULT b'0' COMMENT '是否已推送';


ALTER TABLE `security_certificate`
ADD COLUMN `push` bit(1) DEFAULT b'0' COMMENT '是否已推送';



drop view if EXISTS v_person_bank;
CREATE
VIEW `v_person_bank` AS
select t1.xm as `name`,
t1.idnum,
IFNULL(tt.phone,t1.phone) as phone,
tt.`name` as 'companyName',
t1.zsbh as 'cerNo',
t1.fzrq as 'cerDate',
t1.areaCode as'areaCode',
IFNULL(tt.incumbencyStatus,0) as 'incumbencyStatus',
if(t3.create_time is null,0,1) as 'bankState',
t3.activeDate,
t3.create_time as 'bankDate'
from security_certificate t1
LEFT JOIN (
select t5.`name`,t2.id_number,t2.phone,t2.incumbencyStatus from security_person t2
INNER JOIN security_company_person t4 on t2.id= t4.person_id and t4.active=true
INNER JOIN security_company t5 on t4.company_id = t5.id and t5.active=true
where t2.active=true and t2.incumbencyStatus=1
)tt on t1.idnum = tt.id_number
LEFT JOIN bank_account t3 on t1.idnum = t3.idNum and t3.active=TRUE
where t1.active=true and ifnull(t1.state,1)=1;


----20200825 v_person_bank 是否有证书照片 是否换发联名卡
drop view if EXISTS v_person_bank;
CREATE
VIEW `v_person_bank` AS
select  t1.id, t1.xm as `name`,
t1.idnum,
IFNULL(tt.phone,t1.phone) as phone,
tt.`name` as 'companyName',
t1.zsbh as 'cerNo',
t1.fzrq as 'cerDate',
t1.havephoto as 'havePhoto',
t1.isDraw as 'isDraw',
IFNULL(tt.incumbencyStatus,0) as 'incumbencyStatus',
if(t3.create_time is null,0,1) as 'bankState',
t3.activeDate,
t3.create_time as 'bankDate'
from security_certificate t1
LEFT JOIN (
select t5.`name`,t2.id_number,t2.phone,t2.incumbencyStatus from security_person t2
INNER JOIN security_company_person t4 on t2.id= t4.person_id and t4.active=true
INNER JOIN security_company t5 on t4.company_id = t5.id and t5.active=true
where t2.active=true and t2.incumbencyStatus=1
)tt on t1.idnum = tt.id_number
LEFT JOIN bank_account t3 on t1.idnum = t3.idNum and t3.active=TRUE
where t1.active=true and ifnull(t1.state,1)=1;







ALTER TABLE `security_company_person`
ADD COLUMN `entryDate`  datetime NULL COMMENT '入职日期' AFTER `active`,
ADD COLUMN `leaveDate`  datetime NULL COMMENT '离职时间' AFTER `entryDate`;


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

IF (
	(
		substr(`t2`.`id_number`, 17, 1) % 2
	),
	'1',
	'2'
) AS `gender`,
t3.`entrydate`,
t3.`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`,
 `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`;
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(`t3`.`entryDate`)
	) AS `outTimeDay`,
	ifnull(`t2`.`insured`, '0') AS `insured`,
IF (
	(
		substr(`t2`.`id_number`, 17, 1) % 2
	),
	'1',
	'2'
) AS `gender`,
t3.`entrydate`,
t3.`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`,
 `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) ;