﻿select 	sle."shiftDate" as shift_date, 
	cast 	(
			sum 	(
					sle."productCount" * template1."lengthBlankSize" * template1."widthBlankSize" / 1000000 * case when template2."id" = product2."id" then bspg."numbersProduct" else 1 end
				) as int8
		) as square_meters 
from 	SensorLogEntry sle 				inner join Job job on sle."job" = job."id" 
							inner join OrderStep os on job."orderStep" = os."id" 
							inner join cb_order order1 on os."order_id" = order1."id" 
							inner join Template template2 on order1."template" = template2."id" 
							left outer join Product product1 on template2."id" = product1."id", 
	Template template1 				cross join Product product2 inner join Template template3 on product2."id" = template3."id", 
	TechnologyCard tc,
	BlankSizeParameterGroup bspg 
where 	order1."template" = template1."id" 
	and product2."id" = tc."product" 
	and tc."bl_size_fk" = bspg."id" 
	and 	(
			template2."id"=product2."id" 
			or case 	when product1."id" is not null then 1 
					when template2."id" is not null then 0 
			end <>1 
			and product2."id"=(select max(product3."id") from Product product3 inner join Template template4 on product3."id"=template4."id")
		) 
group by sle."shiftDate" 
order by sle."shiftDate";