ALTER TABLE `security_company_person`
DROP INDEX `fk_com_per_role`;

ALTER TABLE `security_person`
ADD COLUMN `comTaskFlag`  varchar(20) NULL DEFAULT '0' COMMENT '是否关联驻勤点';

ALTER TABLE `com_task_per`
ADD COLUMN `active`  bit NULL COMMENT '是否可用' AFTER `perId`,
ADD COLUMN `updateTime`  datetime NULL COMMENT '更新时间' AFTER `createBy`,
ADD COLUMN `updateBy`  varchar(20) NULL COMMENT '修改者' AFTER `updateTime`;


drop view if EXISTS v_security_person;
CREATE
VIEW `v_security_person` AS
SELECT
	t2.id,
  t4.id as companyId,
  t3.id as relationId,
  id_number,
	t1.realname,
	t1.phone,
  t1.photo,
  t2.personType,
  t5.comTaskId,
	datediff(now(),entrydate) as outTimeDay,
  ifnull(insured,'0') as insured,
  IF(mod(SUBSTR(id_number,17,1),2),'1','2')  as gender,

  if(t3.active,entrydate,t3.create_time) as entrydate,
  if(t3.active,null,t3.update_time) as leaveDate,
  if(t3.active,'1','0') as incumbencyStatus,
  ifnull(identityAuthState,'0') as identityAuthState,
  if(ifnull(zgcredential,'')='',0,1) as certificateState,
	t4.`name` as companyName,
  t4.orgname,
  zgcredential,
  t2.credential,
  t4.institutionType,
  t4.orgid,
  t2.id_card_address,
	t2.current_address,
	t2.nation,
	t2.stature,
	t2.blood,
	t2.age,
	t2.edu,
	t1.active,
	t2.profession_level,
	ifnull(t2.comTaskFlag,'0') as comTaskFlag,
  t4.areacode
from security_person t2
INNER JOIN `user` t1 on t2.user_id = t1.id
INNER JOIN security_company_person t3 on t2.id = t3.person_id
INNER JOIN security_company t4 on t4.id = t3.company_id
where t1.active=true