
ALTER TABLE `com_task`
ADD COLUMN `comContractId`  bigint(20) NULL COMMENT '关联合同id';


update com_task t1,com_task_contract t2
set t1.comContractId = t2.comContractId
where t1.comTaskId = t2.comTaskId and t2.active=true ;

drop view if EXISTS v_com_task;
CREATE
VIEW `v_com_task`AS
select
      t.comTaskId,
      t.taskName,
      t.orgName,
      t.taskStationType,
      sc.`name` as comName,
      t.serviceObject,
      t.serviceObjectSySituation,
      t.taskAddress,
      b.effectStartDate as startDate,
      b.effectEndDate as endDate,
      t.startDate as taskStartDate,
      t.endDate as taskEndDate,
      t.area,
      sc.areaCode as comAreaCode,
      t.areaCode as taskAreaCode,
      sc.orgId as comOrgId,
      t.orgId as taskOrgId,
      (
      select count(1) from security_company_person d,com_task_per e where d.active =true and e.active =true and company_id = sc.id  and e.perId =d.person_id and e.comTaskId =  t.comTaskId

      ) as perNum,
      (case contractStatus
    when effectStartDate>date_format( now(), '%Y-%m-%d' ) then '8'
    when '1' then '1'
    when '2' then '2'
    when '3' then '1'
    else '2'
    end) as contractStatus,
      sc.id as comId,
      b.comContractId as comContractId,
      b.contractName as contractName
    from com_task t
    INNER JOIN security_company sc on t.comId = sc.comId
    left JOIN com_contract b on t.comContractId = b.comContractId
    WHERE
      t.isDelete='0'