drop view if EXISTS v_security_person_task;
CREATE
VIEW `v_security_person_task` 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,
  (
    select GROUP_CONCAT(native_name Separator '') from native where `code` = left(id_number,2) or `code` = left(id_number,4)
  ) as nativePlace,
  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
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 t1.active=true
and t2.personType !=1;



drop view if EXISTS v_security_person_task;
CREATE
VIEW `v_security_person_task` 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,
  (
    select GROUP_CONCAT(native_name Separator '') from native where `code` = left(id_number,2) or `code` = left(id_number,4)
  ) as nativePlace,
  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
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 t1.active=true
and t2.personType !=1