select u.lastName || ' ' || u.firstName || ' ' || u.middleName,  
                               
(select count(p1) from Kontroller k1 join k1.gossrvcDocPackage p1 where k1.user = k.user   
 and (p1.externalSystem = 1 or p1.externalSystem = 4) and p1.completedDate = NULL),  
                               
(select count(p4) from Kontroller k1 join k1.gossrvcDocPackage p4 where k1.user = k.user  
and p4.externalSystem = NULL and p4.completedDate = NULL),  
                               
(select count(p2)  from Kontroller k1, GossrvcDocPackageItem item, CardElementFieldValue cv, CardField cf  join k1.gossrvcDocPackage p2  
where  k1.user = k.user and (p2.externalSystem = 1 or p2.externalSystem = 4) and p2.completedDate <> NULL and item.isResult = true   
 and item.gossrvcDocPackage.id = p2.id  
 and cv.cardElement = item.docTypeId  
 and cf.fieldId = cv.cardField.id   
 and cf.fieldName='typeResult'  
 and item.docValue = true   
 and cv.fieldIntegerValue = 0   and p2.testPackage <> true),  
                               
 (select count(p5)   
 from Kontroller k1, GossrvcDocPackageItem item, CardElementFieldValue cv, CardField cf  join k1.gossrvcDocPackage p5  
 where  k1.user = k.user and p5.externalSystem = NULL and p5.completedDate <> NULL and item.isResult = true   
 and item.gossrvcDocPackage.id = p5.id   
 and cv.cardElement = item.docTypeId   
 and cf.fieldId = cv.cardField.id   
 and cf.fieldName='typeResult'   
 and item.docValue = true   
 and cv.fieldIntegerValue = 0   
 and p5.testPackage <> true),   
                               
 (select count(p3)   
 from Kontroller k1, GossrvcDocPackageItem item, CardElementFieldValue cv, CardField cf  join k1.gossrvcDocPackage p3  
 where  k1.user = k.user and (p3.externalSystem = 1 or p3.externalSystem = 4) and p3.completedDate <> NULL and item.isResult = true   
 and item.gossrvcDocPackage.id = p3.id   
 and cv.cardElement = item.docTypeId   
 and cf.fieldId = cv.cardField.id   
 and cf.fieldName='typeResult'   
 and item.docValue = true   
 and cv.fieldIntegerValue = 1   
 and p3.testPackage <> true),   
                               
 (select count(p6)   
 from Kontroller k1, GossrvcDocPackageItem item, CardElementFieldValue cv, CardField cf  join k1.gossrvcDocPackage p6  
 where  k1.user = k.user and p6.externalSystem = NULL and p6.completedDate <> NULL and item.isResult = true   
 and item.gossrvcDocPackage.id = p6.id   
 and cv.cardElement = item.docTypeId   
 and cf.fieldId = cv.cardField.id   
 and cf.fieldName='typeResult'   
 and item.docValue = true   
 and cv.fieldIntegerValue = 1  
 and p6.testPackage <> true)   
                               
 from Kontroller k   
 left join k.user u where u.owner = 'RSOC'   
 group by u.lastName || ' ' || u.firstName || ' ' || u.middleName, k.user, k.gossrvcDocPackage;  

select u.last_Name, 
(select count(p1.id) from Kontroller k1 join gossrvc_doc_package p1 on p1.id = k1.doc_package_id
where k.user_id = k1.user_id and (p1.externalsystem=1 or p1.externalsystem=4) and p1.completed_date IS NULL),

(select count(p4.id) from Kontroller k1 join gossrvc_doc_package p4 on p4.id = k1.doc_package_id
where k.user_id = k1.user_id and p4.externalsystem is null and p4.completed_date is null),

(select count(p2.id) from Kontroller k1 join gossrvc_doc_package p2 on p2.id = k1.doc_package_id, gossrvc_doc_package_item item, card_element_field_value cv, card_field cf
where k.user_id = k1.user_id and (p2.externalsystem=1 or p2.externalsystem=4) and p2.completed_date IS NULL
and item.is_result = true and p2.id = item.doc_package_id and cv.card_element_id = item.doc_type_id and cf.field_id = cv.field_id
and cf.field_name = 'typeResult' and item.doc_value = true and cv.field_integer_value = 0 and p2.test != true),

(select count(p5.id) from Kontroller k1 join gossrvc_doc_package p5 on p5.id = k1.doc_package_id, gossrvc_doc_package_item item, card_element_field_value cv, card_field cf
where k.user_id = k1.user_id and p5.externalsystem is null and p5.completed_date IS NULL
and item.is_result = true and p5.id = item.doc_package_id and cv.card_element_id = item.doc_type_id and cf.field_id = cv.field_id
and cf.field_name = 'typeResult' and item.doc_value = true and cv.field_integer_value = 0 and p5.test != true),

(select count(p3.id) from Kontroller k1 join gossrvc_doc_package p3 on p3.id = k1.doc_package_id, gossrvc_doc_package_item item, card_element_field_value cv, card_field cf
where k.user_id = k1.user_id and (p3.externalsystem=1 or p3.externalsystem=4) and p3.completed_date IS NULL
and item.is_result = true and p3.id = item.doc_package_id and cv.card_element_id = item.doc_type_id and cf.field_id = cv.field_id
and cf.field_name = 'typeResult' and item.doc_value = true and cv.field_integer_value = 1 and p3.test != true),

(select count(p6.id) from Kontroller k1 join gossrvc_doc_package p6 on p6.id = k1.doc_package_id, gossrvc_doc_package_item item, card_element_field_value cv, card_field cf
where k.user_id = k1.user_id and p6.externalsystem is null and p6.completed_date IS NULL
and item.is_result = true and p6.id = item.doc_package_id and cv.card_element_id = item.doc_type_id and cf.field_id = cv.field_id
and cf.field_name = 'typeResult' and item.doc_value = true and cv.field_integer_value = 1 and p6.test != true)

from Kontroller k inner join  user_ref u on k.user_id = u.id where u.owner = 'RSOC'
group by u.last_name, k.user_id, k.doc_package_id