--explain analyze
SELECT * FROM (SELECT COALESCE(levelData.pci, collected.pci) AS pci,
COALESCE(collected.unit,
(SELECT(SELECT (SELECT textcat_all(DISTINCT CASE WHEN iu.unit = 1 THEN 'kg' ELSE 'Kus' END || ';') FROM t_item iu WHERE pe.ean = iu.ean AND iu.company_fk = (SELECT s.company_fk FROM t_store_info s WHERE s.id = '87')  LIMIT 1) FROM t_pn pe WHERE pe.product_custom_identificator = COALESCE(levelData.pci, collected.pci) AND pe.company_fk = (SELECT s.company_fk FROM t_store_info s WHERE s.id = '87') LIMIT 1)),
'#NOUNIT') AS units,
COALESCE(collected.ean, (SELECT textcat_all(pe.ean||'; ') FROM t_pn pe WHERE pe.product_custom_identificator = COALESCE(levelData.pci, collected.pci) AND pe.company_fk = (SELECT s.company_fk FROM t_store_info s WHERE s.id = '87'))) AS eans,
COALESCE(collected.description, (SELECT (SELECT i.description FROM t_item i WHERE i.ean = pd.ean AND i.company_fk = (SELECT s.company_fk FROM t_store_info s WHERE s.id = '87') LIMIT 1)
FROM t_pn pd
WHERE pd.product_custom_identificator = COALESCE(levelData.pci, collected.pci) AND pd.company_fk = (SELECT s.company_fk FROM t_store_info s WHERE s.id = '87') LIMIT 1)) AS description,
COALESCE(levelData.level_data_count, 0) AS level_data_count,
COALESCE(collected.total, 0) AS collected_count,
ROUND(CAST ((COALESCE(collected.total, 0) - COALESCE(levelData.level_data_count, 0)) AS numeric), 1) AS count_difference,
ROUND(CAST (((COALESCE(collected.total, 0) - COALESCE(levelData.level_data_count, 0)) * (COALESCE(levelData.unit_price, 0.01))) AS numeric), 1) AS value_difference,
COALESCE(levelData.unit_price, 0.01) AS unit_price,
COALESCE(collected.hierarchy,
(SELECT (SELECT hi.hierarchy_fk FROM t_item hi WHERE pe.ean = hi.ean AND hi.company_fk = (SELECT s.company_fk FROM t_store_info s WHERE s.id = '87')LIMIT 1) FROM t_pn pe WHERE pe.product_custom_identificator = COALESCE(levelData.pci, collected.pci) AND pe.company_fk = (SELECT s.company_fk FROM t_store_info s WHERE s.id = '87') LIMIT 1)) AS hierarchy,
collected.data_source AS data_source,
COALESCE(collected.location, '#neinventovane') AS location
--, *
FROM
(SELECT(SELECT pn.product_custom_identificator FROM t_pn pn, t_weighting w4
WHERE MAX(w.item_ean) = pn.ean AND pn.company_fk = (SELECT s.company_fk FROM t_store_info s WHERE s.id = '87') LIMIT 1) as pci,
textcat_all(DISTINCT w.item_ean || '; ') AS ean,
textcat_all(DISTINCT i.description || '; ') AS description,
(SELECT CASE WHEN (TRUNC(SUM(w.item_count)) = ROUND(SUM(w.item_count), 4))
                THEN TRUNC(SUM(w.item_count))
                ELSE ROUND(SUM(w.item_count), 4)
                END) AS total,
textcat_all(DISTINCT CASE WHEN i.unit = 1 THEN 'kg'
ELSE 'Kus'
END || ';') AS unit,
textcat_all(DISTINCT (SELECT
CASE WHEN (w.stat_count_entered IS NOT NULL AND w.stat_weight_start IS NULL)
THEN CASE WHEN (w.box_ean IS NULL)
THEN 'm'
ELSE 'M'
END
ELSE ''
END ||
CASE WHEN (w.stat_item_count > 1 OR (w.stat_count_entered IS NULL AND w.stat_weight_start IS NULL))
THEN CASE WHEN (w.box_ean IS NULL)
THEN 's'
ELSE 'S'
END
ELSE ''
END ||
CASE WHEN (w.stat_weight_start IS NOT NULL)
THEN CASE WHEN (w.box_ean IS NULL)
THEN 'w'
ELSE 'W'
END
ELSE ''
END
)) AS data_source,
max(i.hierarchy_fk) AS hierarchy,
(SELECT textcat_all ('['||coord_x||','||coord_y||','||coord_z||']:' || pocet || ' (' || shelf || '/' || shelf_total || '); ')
FROM
(SELECT CASE WHEN (TRUNC(SUM(w3.item_count)) = ROUND(SUM(w3.item_count), 4))
                THEN TRUNC(SUM(w3.item_count))
                ELSE ROUND(SUM(w3.item_count), 4)
                END AS pocet,   coord_x, coord_y, coord_z, shelf, shelf_total
FROM t_weighting w3
WHERE w3.item_ean = ANY (array_agg(w.item_ean)) AND w3.stocktaking_id = w.stocktaking_id
GROUP BY coord_x, coord_y, coord_z, shelf, shelf_total
)f ) AS location
FROM t_weighting w, t_item i
WHERE w.item_ean = i.ean AND w.stocktaking_id = 'TeKarvinaKosmo07092014;87' AND
i.company_fk = (SELECT s.company_fk FROM t_store_info s WHERE s.id = '87') AND
w.user_id = (SELECT user_id
FROM t_weighting wi
WHERE wi.coord_x = w.coord_x AND wi.coord_y = w.coord_y AND wi.coord_z = w.coord_z AND wi.stocktaking_id = w.stocktaking_id
ORDER BY stat_item_start DESC
LIMIT 1)
GROUP BY w.stocktaking_id,
(SELECT pn.product_custom_identificator FROM t_pn pn
WHERE i.ean = pn.ean AND pn.company_fk = (SELECT s.company_fk FROM t_store_info s WHERE s.id = '87') LIMIT 1)
ORDER BY pci) collected
FULL OUTER JOIN
(SELECT cld.product_custom_identificator AS pci,
SUM(count) AS level_data_count,
MAX(unit_price) AS unit_price
FROM t_currentlevel_data cld
WHERE cld.batch_id = (SELECT cl.batch_id FROM t_currentlevel cl WHERE cl.stocktaking_id = 'TeKarvinaKosmo07092014;87')
GROUP BY cld.product_custom_identificator) levelData
ON (levelData.pci = collected.pci)) as diff
WHERE NOT (level_data_count = 0 AND collected_count = 0);