drop view if EXISTS v_com_task;

CREATE VIEW `v_com_task` AS SELECT
	`t`.`comTaskId` AS `comTaskId`,
	`t`.`taskName` AS `taskName`,
	`t`.`orgName` AS `orgName`,
	`t`.`area` AS `area`,
	`sc`.`areacode` AS `comAreaCode`,
	`t`.`areaCode` AS `taskAreaCode`,
	`sc`.`orgId` AS `comOrgId`,
	`t`.`orgId` AS `taskOrgId`,
	`t`.`taskStationType` AS `taskStationType`,
	`sc`.`name` AS `comName`,
	`t`.`serviceObject` AS `serviceObject`,
	`t`.`serviceObjectSySituation` AS `serviceObjectSySituation`,
	`t`.`taskAddress` AS `taskAddress`,
	`b`.`effectStartDate` AS `startDate`,
	`b`.`effectEndDate` AS `endDate`,
	(
		SELECT
			count(1)
		FROM
			(
				`security_company_person` `d`
				JOIN `com_task_per` `e`
			)
		WHERE
			(
				(`d`.`active` = TRUE)
				AND (`d`.`company_id` = `sc`.`id`)
				AND (
					`e`.`perId` = `d`.`person_id`
				)
				AND (
					`e`.`comTaskId` = `t`.`comTaskId`
				)
			)
	) AS `perNum`,

(
		CASE `b`.`contractStatus`
		WHEN (
			`b`.`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`
				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')