- 01
- 02
- 03
- 04
- 05
- 06
- 07
- 08
- 09
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
SELECT x.org_spec_id, (
SELECT count(c.id) FROM customers c , government_customers_require gcr
WHERE c.org_spec_id=x.org_spec_id AND gcr.customer_id=c.id
) as bal_ins,
sum(x.type_b1) as type_b1, sum(x.type_b2) as type_b2, sum(x.type_b3) as type_b3, sum(x.bus_debit) as bus_debit,
sum(x.gost_ac1) as gost_ac1, sum(x.gost_ac2) as gost_ac2, sum(x.nav_ac1) as nav_ac1, sum(x.nav_ac2) as nav_ac2,
\'-\' as price1, \'-\' as price2, \'-\' as price3, \'-\' as price4, sum(x.scope_b1) as scope_b1,
sum(x.scope_b2) as scope_b2, sum(x.scope_b3) as scope_b3, sum(x.scope_b4) as scope_b4, sum(x.scope_b5) as scope_b5,
sum(x.scope_b6) as scope_b6, sum(x.add11) as add11, sum(x.add12) as add12, sum(x.add21) as add21,
sum(x.add22) as add22, sum(x.add23) as add23, sum(x.add31) as add31, sum(x.add32) as add32, sum(x.add33) as add33
FROM (SELECT c.org_spec_id, b.id , c.id as bal_ins,
case when bsd.id is not null then 1 else 0 end as bus_debit,
case when bsd.id is null then case when bm.type_id = 1 then 1 else 0 end else 0 end as type_b1,
case when bsd.id is null then case when bm.type_id = 2 then 1 else 0 end else 0 end as type_b2,
case when bsd.id is null then case when bm.type_id = 3 then 1 else 0 end else 0 end as type_b3,
case when bsd.id is null AND bm.type_id = 1 then case when gst.accepted then 1 else 0 end else 0 end as gost_ac1,
case when bsd.id is null AND bm.type_id = 1 then case when gst.accepted then 0 else 1 end else 0 end as gost_ac2,
case when bsd.id is null AND bm.type_id = 1 then case when nav.id is not null then 1 else 0 end else 0 end as nav_ac1,
case when bsd.id is null AND bm.type_id = 1 then case when nav.id is not null then 0 else 1 end else 0 end as nav_ac2,
case when bsd.id is null AND b.scope_id = 2 then 1 else 0 end as scope_b1,
case when bsd.id is null AND b.scope_id = 1 then 1 else 0 end as scope_b2,
case when bsd.id is null AND b.scope_id = 3 then 1 else 0 end as scope_b3,
case when bsd.id is null AND b.scope_id = 4 then 1 else 0 end as scope_b4,
case when bsd.id is null AND b.scope_id = 6 then 1 else 0 end as scope_b5,
case when bsd.id is null AND b.scope_id = 5 then 1 else 0 end as scope_b6,
case when bsd.id is null then case when add1.id is not null then case when add1.selected_variant=\'on\' then 1 else 0 end else 1 end else 0 end as add11,
case when bsd.id is null then case when add1.id is not null then case when add1.selected_variant=\'off\' then 1 else 0 end else 0 end else 0 end as add12,
case when bsd.id is null then case when add2.id is not null then case when add2.selected_variant=\'on\' then 1 else 0 end else 0 end else 0 end as add21,
case when bsd.id is null then case when add2.id is not null then case when add2.selected_variant=\'off\' then 1 else 0 end else 0 end else 0 end as add22,
case when bsd.id is null then case when add2.id is not null then case when add2.selected_variant=\'no\' then 1 else 0 end else 1 end else 0 end as add23,
case when bsd.id is null then case when add3.id is not null then case when add3.selected_variant=\'on\' then 1 else 0 end else 0 end else 0 end as add31,
case when bsd.id is null then case when add3.id is not null then case when add3.selected_variant=\'off\' then 1 else 0 end else 0 end else 0 end as add32,
case when bsd.id is null then case when add3.id is not null then case when add3.selected_variant=\'no\' then 1 else 0 end else 1 end else 0 end as add33