--07/05/2015
WITH RECURSIVE search_ou(id, title, parent_id) AS
(SELECT ou1.id, ou1.title, ou1.parent_id
 FROM tbl_ou ou1
 WHERE coalesce(${paramOU_id}, NULL) IS NOT NULL AND
    			    ou1.id IN (${paramOU_id})
 UNION ALL
 SELECT ou2.id, ou2.title, ou2.parent_id
 FROM tbl_ou ou2,
       search_ou
 WHERE  ${subOU} AND
        (search_ou.id=ou2.parent_id)
)

SELECT
	'0000'  as grp_1
 	, sc.id as sc_id
	, sc.number_ as  sc_title
	, sc.case_id as sc_case
	, sc.state

	, sc.priority_id
	, pt.code
	, pt.title  as priority_title
	, pt.priority_level

	, COUNT(sc.id) OVER (PARTITION BY '0000' ) as cnt_sc_id
	, Count (Case When (sc.registration_date is not null AND 	date_trunc('minute', sc.registration_date) BETWEEN
							CAST( COALESCE(${DateFrom} , DATE_TRUNC('day', current_timestamp)) as timestamp)
						and CAST( COALESCE(${DateTo}, DATE_TRUNC('minute', current_timestamp))  as timestamp) )
				Then 1 Else Null End)  OVER (PARTITION BY '0000' ) as count_reg
	, Count (Case When (sc.state in ('closed') AND 	date_trunc('minute', sc.dateDecision::timestamp) 	 BETWEEN
							CAST( COALESCE(${DateFrom} , DATE_TRUNC('day', current_timestamp)) as timestamp)
						and CAST( COALESCE(${DateTo}, DATE_TRUNC('minute', current_timestamp))  as timestamp) )
	  Then 1 Else Null End) OVER (PARTITION BY '0000' ) as count_closed
	, srv.id
	, srv.title as srv_title
	, sc.service_id
	, sc.clientOU_id

, tsmt.title as case_title
--, sc.metaClass_id
, tsms.title as state_title


FROM
	tbl_servicecall sc
	LEFT JOIN tbl_slmService as srv ON srv.id = sc.service_id
--		LEFT JOIN search_ou AS ou ON  sc.clientou_id = ou.id
		LEFT JOIN tbl_priority pt ON sc.priority_id = pt.id

		LEFT JOIN TBL_SYS_METAINFO_STATES tsms ON  (tsms.clazz='serviceCall'  AND  sc.case_id = tsms.kase  AND sc.state = tsms.code)
		LEFT JOIN TBL_SYS_METAINFO_TITLES tsmt ON  (tsmt.clazz='serviceCall'  AND  sc.case_id = tsmt.kase)

--WHERE  sc.case_id in ('request', 'serviceCall', 'call')