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,
    (
    select count(1) from security_company_person d,com_task_per e where active =true and company_id = sc.id  and e.perId =d.person_id and e.comTaskId =  t.comTaskId

    ) as perNum,
      if(b.effectStartDate>date_format( now(), '%Y-%m-%d' ),'8',b.contractStatus) as contractStatus,
      sc.id as comId,
      b.comContractId as comContractId,
      b.contractName as contractName
    from com_task t
    INNER JOIN security_company sc on sc.comId=t.comId
    left JOIN com_task_contract a on t.comTaskId = a.comTaskId
    left JOIN com_contract b on a.comContractId = b.comContractId
    WHERE
      t.isDelete='0'