drop view if EXISTS v_security_situation;
CREATE
VIEW `v_security_situation` AS
SELECT
	sc.`name`,
	sc.orgName,
	sc.areaCode,
	sc.orgId as orgId,
	ifnull(sc.isuredNumber,0) as isuredNumber, -- 已投保保安数
	sc.security_amount -(
	SELECT
	count(DISTINCT p.id)
	FROM
	security_person p
	INNER JOIN security_company_person scp ON scp.person_id = p.id AND scp.active = TRUE
	INNER JOIN com_task_schedul cts ON cts.perId = p.id AND cts.active = TRUE
	INNER JOIN com_task ct ON ct.comTaskId = cts.comTaskId AND ct.isdelete = '0'
	WHERE p.active = TRUE and curdate() BETWEEN ct.startDate AND ct.endDate
	AND scp.company_id = sc.id
	) AS noSchedulePersons, -- 未排班人数
	(
	SELECT
	count(1)
	FROM
	security_company_person scp  ,  security_person sp
	WHERE
	scp.company_id = sc.id
	AND sp.personType != '1'
	and scp.person_id = sp.id
	AND scp.active = TRUE
	) countPerson,
	--  总人数 保安员总人数：不包括内勤人员，包括武装押运人员。
	(
	SELECT
	count(1)
	FROM
	com_task c
	WHERE
	c.comId = sc.comId
	AND c.isDelete = 0
	and c.taskStatusNew ='1'
	) stationingNum,
	-- 驻勤点 驻勤点个数：统计正在进行中的（生效）的驻勤点。
	(
	SELECT
	count(1)
	FROM
	com_contract c
	WHERE
	c.comId = sc.comId
	AND c.isDelete = 0
	AND contractType = '01'
	and effectStartDate <= date_format(now(), '%Y-%m-%d')
	and contractStatus in ('1','3')
	) contractNum,
	--  保安服务合同数，统计合同状态为生效的合同数。
	(
	SELECT
	count(1)
	FROM
	com_contract c
	WHERE
	c.comId = sc.comId
	AND c.isDelete = 0
	AND contractType = '02'
	and effectStartDate <= date_format(now(), '%Y-%m-%d')
	and contractStatus in ('1','3')
	) personContractNum,
	--  签订劳工合同数 统计合同状态为生效的劳动合同数。
	(
	SELECT
	count(1)
	FROM
	security_company_person scp,
	security_person sp
	WHERE
	sp.incumbencyStatus = '1'
	AND sp.id = scp.person_id
	AND scp.active = TRUE
	AND scp.company_id = sc.id
	AND (
	sp.zgcredential IS NULL
	OR sp.zgcredential = ''
	)
	AND sp.personType != '1'
	) unCertificate -- 未持证保安数，统计保安员未持证数量，不包括内勤人员。
FROM
	security_company sc
INNER JOIN `user` u ON sc.user_id =u.id AND u.active=true
WHERE sc.active =TRUE
AND institutionType in('0102','0105');