select
	u_asm.id as asm_id,
	u_asm.calc_full_name as asm_name,
	u_sv.id as sv_id,
	u_sv.calc_full_name as sv_name,
	u.id as user_id,
	coalesce(u.route,u.calc_full_name) as user_route,
	coalesce(ru.calc_full_name,u.calc_full_name) as user_name,
	count(distinct t.shop_id) as shops_cnt,
	count(*) as visits_plan,
	sum(donev) as visits_fact,
	sum(distribution_plan) as distribution_plan,
	sum(distribution_fact) as distribution_fact,
	sum(case when distribution_plan>0 then 100.0*distribution_fact/distribution_plan else 0 end) as distribution_pcnt2_1,
	sum(case when distribution_plan>0 then 1 else 0 end) as distribution_pcnt2_2,
	sum(pa) as has_pa,
	jsonb_sum(data1) as data1,
	jsonb_sum(data2) as data2,
	sum(case when to_bigint(data2->>'shelf_share')>=snfrpd.col1 then 1 else 0 end) as shelf_share_ok,
	sum(case when snfrpd.col1>0 then 100.0*to_bigint(data2->>'shelf_share')/snfrpd.col1 else 0 end) as shelf_share_ok_pcnt2_1,
	sum(case when snfrpd.col1>0 then 1 else 0 end) as shelf_share_ok_pcnt2_2
from
	(
		select
			t.*,
			case when r_ex.id is not null then 1 else 0 end as donev,
--			(select count(*) from Tshop_skus ss,Tskus s where ss.shop_id=t.shop_id and ss.sku_id=s.id and (:category_id::bigint is null or s.category_id=:category_id)) as distribution_plan,
			(select count(*) from Treport_skus rs,Tskus s where rs.report_id=r_ex.id and rs.sku_id=s.id and (:category_id::bigint is null or s.category_id=:category_id)) as distribution_plan,
			(select count(*) from Treport_skus rs,Tskus s where rs.report_id=r_ex.id and rs.sku_id=s.id and rs.amount>0 and (:category_id::bigint is null or s.category_id=:category_id)) as distribution_fact,
			(
				select jsonb_object_agg(rtq.ident,1)
				from Treport_type_questions rtq,Treport_answers ra left join Treport_type_question_answers rtqa on rtqa.id=ra.q_answer_id
				where
					ra.report_id=r_q.id and ra.question_id=rtq.id and rtq.ident is not null and
					(
						(rtq.datatype_id=4 and rtqa.value<>'0') or -- select
						(rtq.datatype_id=11 and ra.image_id is not null) -- image
					)
			) as data1,
			(
				select jsonb_object_agg(rtq.ident,coalesce(rtqa.value,ra.answer)::numeric)
				from Treport_type_questions rtq,Treport_answers ra left join Treport_type_question_answers rtqa on rtqa.id=ra.q_answer_id
				where
					ra.report_id=r_q.id and ra.question_id=rtq.id and rtq.ident is not null and
					(
						rtq.datatype_id in (1,2) or -- boolean, number
						(rtq.datatype_id=4 and rtqa.value<>'0') -- select
					)
			) as data2,
			case when exists (
					select 1
					from Treport_type_questions rtq,Treport_answers ra left join Treport_type_question_answers rtqa on rtqa.id=ra.q_answer_id
					where
						ra.report_id=r_q.id and ra.question_id=rtq.id and rtq.ident like 'pa_%' and
						(
							(rtq.datatype_id=4 and rtqa.value<>'0') or -- select
							(rtq.datatype_id=11 and ra.image_id is not null) -- image
						)
					) then 1 else 0 end as pa
		from
			Ttmp_trips1 t
			left join Ttmp_reports1 r_ex on t.date_use=r_ex.date_use and t.shop_id=r_ex.shop_id and t.user_id=r_ex.user_id and r_ex.type_id=5
			left join Ttmp_reports1 r_q  on t.date_use=r_q .date_use and t.shop_id=r_q .shop_id and t.user_id=r_q .user_id and r_q .type_id=:questionary_id
	) t
	left join Tusers u on u.id = t.user_id
	left join Tperson_dates ru on ru.user_id = u.id and ru.date_hire<=t.date_use and ru.date_fire>=t.date_use
	left join Tusers u_sv on u_sv.id = t.sv_id
	left join Tusers u_asm on u_asm.id = t.asm_id
	left join Tusers u_um on u_um.id = t.um_id
	inner join Tshops s on t.shop_id=s.id
	inner join Tcities ci on s.city_id=ci.id
	inner join Tshop_networks sn on s.network_id=sn.id
	left join Tshop_network_format_city_period_datas snfrpd on snfrpd.network_format_id=s.network_format_id and snfrpd.city_id=ci.id and date_start<=t.date_use and date_end>=t.date_use
group by
	/* merchs 1 */			u_asm.id,u_asm.calc_full_name,u_sv.id,u_sv.calc_full_name,u.id,coalesce(u.route,u.calc_full_name),coalesce(ru.calc_full_name,u.calc_full_name) /* /merchs */
order by
	/* merchs 1 */			u_asm.id,u_asm.calc_full_name,u_sv.id,u_sv.calc_full_name,u.id,coalesce(u.route,u.calc_full_name),coalesce(ru.calc_full_name,u.calc_full_name) /* /merchs */
