land owners

select l_l.num, l_o.name, l_o.phone, l_o.email, l_o.township_member
    from lands.owners l_o
        inner join lands.owner_lands l_ol on (l_ol.owner_id=l_o.id)
	left join lands.lands l_l on (l_l.id=l_ol.land_id)
    order by l_ol.land_id


ALTER TABLE balances.payment_orders ADD FOREIGN KEY (land_id) REFERENCES lands.lands (id);
ALTER TABLE balances.payment_orders ADD FOREIGN KEY (charge_type_id) REFERENCES charges.types (id);
ALTER TABLE balances.payment_orders ADD FOREIGN KEY (invoice_id) REFERENCES balances.invoices (id);

http://township.test/user/payment/fail?orderId=f75ff397-e58f-7c74-9d2c-cd6b2891a039&lang=ru
http://township.test/user/payment/success?orderId=aaa9a5c1-e678-704b-bc6f-fcff2891a039&lang=ru
http://township.test/user/payment/success?orderId=d0f74500-651c-730c-b8f7-82df2891a039&lang=ru

[
{"id":0,"line":189,"error":"extData.errors._invalid_period","data":{"dt":"18.05.2022","amount":5370,"month":null,"year":null,"land":null,"charge_code":"192","budget_item":"ц"}},
{"id":1,"line":212,"error":"extData.errors._invalid_period","data":{"dt":"20.05.2022","amount":3580,"month":null,"year":null,"land":null,"charge_code":"022","budget_item":"ц"}}
]

select b_i.land_id, b_i.amount, b_i.month, b_i.year, l_l.square
  from balances.invoices b_i
  inner join lands.lands l_l on (l_l.id=b_i.land_id)
  where (b_i.month=11)and(b_i.charge_type_id=3)
  order by 1

----------------------

CREATE TABLE IF NOT EXISTS public.temp
(
    land_id integer NOT NULL,
    owner character varying COLLATE pg_catalog."default",
    tp_3 numeric(20,2),
    tp_2 numeric(20,2),
    tp_1 numeric(20,2)
)


select b_l.land_id, b_l.amount, t.tp_1, 
  case b_l.amount-t.tp_1 > 0 when true then  b_l.amount-t.tp_1 else null end create_pay,
  case b_l.amount-t.tp_1 < 0 when true then  t.tp_1 - b_l.amount else null end min_pay
  from public.temp t
  inner join balances.lands b_l on (b_l.land_id=t.land_id)and(b_l.charge_type_id=1)
  order by b_l.land_id

insert into balances.pays (land_id, amount, dt, charge_type_id)
select b_l.land_id, b_l.amount-t.tp_1, '2022-10-23', 1
  from public.temp t
  inner join balances.lands b_l on (b_l.land_id=t.land_id)and(b_l.charge_type_id=1)
  where (b_l.amount-t.tp_1)>0

UPDATE balances.pays SET
amount = '142628.45'::numeric WHERE
id = 476;
UPDATE balances.pays SET
amount = '60874.53'::numeric WHERE
id = 484;
UPDATE balances.pays SET
amount = '19747.83'::numeric WHERE
id = 502;
update public.temp set tp_1=0 where land_id=74;
UPDATE balances.pays SET
amount = '1719.67'::numeric WHERE
id = 691;
UPDATE balances.pays SET
amount = '54.23'::numeric WHERE
id = 515;
UPDATE balances.pays SET
distributed = '123.95'::numeric WHERE
id = 517;
UPDATE balances.pays SET
amount = '197.10'::numeric WHERE
id = 529;
DELETE FROM balances.pays
    WHERE id IN
        (530);
UPDATE balances.pays SET
amount = '1031.96'::numeric WHERE
id = 1003;
UPDATE balances.pays SET
amount = '32766.49'::numeric WHERE
id = 539;
UPDATE balances.pays SET
amount = '96999.57'::numeric WHERE
id = 540;
UPDATE balances.pays SET
amount = '6.05'::numeric WHERE
id = 548;
UPDATE balances.pays SET
amount = '72764.13'::numeric WHERE
id = 552;
UPDATE balances.pays SET
amount = '90.08'::numeric WHERE
id = 535;
UPDATE balances.pays SET
amount = '2820.23'::numeric WHERE
id = 901;
UPDATE balances.pays SET
amount = '8553.25'::numeric WHERE
id = 652;

членские

select b_l.land_id, b_l.amount-sum(b_i.amount) as base_to, b_l.amount base_current, t.tp_2,
  case (b_l.amount-sum(b_i.amount)-t.tp_2) > 0 when true then b_l.amount-sum(b_i.amount)-t.tp_2 else null end create_pay,
  case (b_l.amount-sum(b_i.amount)-t.tp_2) < 0 when true then  t.tp_2 -(b_l.amount-sum(b_i.amount)) else null end min_pay
  from public.temp t
  inner join balances.lands b_l on (b_l.land_id=t.land_id)and(b_l.charge_type_id=2)
   inner join balances.invoices b_i on (b_i.land_id=t.land_id)and(b_i.charge_type_id=2)and(b_i.year=2022)and(b_i.month>9)
  group by b_l.land_id, b_l.amount, t.tp_2
  order by b_l.land_id

целевые

select b_l.land_id, b_l.amount, t.tp_3, 
  case b_l.amount-t.tp_3 > 0 when true then  b_l.amount-t.tp_3 else null end create_pay,
  case b_l.amount-t.tp_3 < 0 when true then  t.tp_3 - b_l.amount else null end min_pay
  from public.temp t
  inner join balances.lands b_l on (b_l.land_id=t.land_id)and(b_l.charge_type_id=3)
  order by b_l.land_id

select * from balances.pays
where land_id=15 and charge_type_id=3


update charges.budget set discount=0
from
(select c_b.id from
balances.invoices b_i
inner join charges.budget c_b on (c_b.invoice_id=b_i.id)
where (b_i.year=2022)and(c_b.discount<>0)) t
where charges.budget.id=t.id


select b_l.land_id, b_l.amount-sum(b_i.amount) as base_to, b_l.amount base_current
  from balances.lands b_l
   inner join balances.invoices b_i on (b_i.land_id=b_l.land_id)and(b_i.charge_type_id=2)and(b_i.year=2022)and(b_i.month>9)
   where (b_l.charge_type_id=2)
  group by b_l.land_id, b_l.amount
  order by b_l.land_id


select * from balances.pays
where land_id=109 and charge_type_id=2

stroka_tur@mail.ru

select l_l.id, b_l1.amount el, b_l2.amount-sum(b_i.amount) ch, b_l3.amount z
  from lands.lands l_l
   left join balances.lands b_l1 on (b_l1.land_id=l_l.id)and(b_l1.charge_type_id=1)
   left join balances.lands b_l2 on (b_l2.land_id=l_l.id)and(b_l2.charge_type_id=2)
   left join balances.lands b_l3 on (b_l3.land_id=l_l.id)and(b_l3.charge_type_id=3)
   left join balances.invoices b_i on (b_i.land_id=l_l.id)and(b_i.charge_type_id=2)and(b_i.year=2022)and(b_i.month>10)
   group by l_l.id, b_l1.amount, b_l2.amount, b_l3.amount
  order by l_l.id  


select l_l.num, b_l.amount, l_o.name
from balances.lands b_l
left join lands.lands l_l on (l_l.id=b_l.land_id)
left join lands.owner_lands l_ol on(l_ol.land_id=l_l.id)
left join lands.owners l_o on(l_o.id=l_ol.owner_id)
where b_l.charge_type_id=1
order by b_l.amount desc

all

select l_l.num, l_o.name,
 b_e.amount electricity,
 b_m.amount members,
 b_t.amount target,
 b_e.amount+b_m.amount+b_t.amount as all
from lands.lands l_l
left join balances.lands b_e on (l_l.id=b_e.land_id)and(b_e.charge_type_id=1)
left join balances.lands b_m on (l_l.id=b_m.land_id)and(b_m.charge_type_id=2)
left join balances.lands b_t on (l_l.id=b_t.land_id)and(b_t.charge_type_id=3)
left join lands.owner_lands l_ol on(l_ol.land_id=l_l.id)
left join lands.owners l_o on(l_o.id=l_ol.owner_id)
order by 1 


electricity

select l_l.num, b_i.amount, l_o.name
from balances.invoices b_i
left join lands.lands l_l on(l_l.id=b_i.land_id)
left join lands.owner_lands l_ol on(l_ol.land_id=l_l.id)
left join lands.owners l_o on(l_o.id=l_ol.owner_id)
where b_i.year=2021 and b_i.month=1 and b_i.charge_type_id=1
order by l_l.id

electricity 2

select l_l.num, b_i.amount, string_agg(l_o.name, ','), l_l.id
from balances.invoices b_i
left join lands.lands l_l on(l_l.id=b_i.land_id)
left join lands.owner_lands l_ol on(l_ol.land_id=l_l.id)
left join lands.owners l_o on(l_o.id=l_ol.owner_id)
where b_i.year=2023 and b_i.month=10 and b_i.charge_type_id=1
group by l_l.num, b_i.amount, l_l.id
order by l_l.id

select l_ll.num, l_ll.owners, l_ll.id land_id,
  b_i_prev.id prev_id,
  b_i_last.id last_id,
  b_i_prev.dt prev_dt, b_i_last.dt last_dt,
  c_e_prev.day_meter_end, c_e_last.day_meter_start,
  c_e_prev.night_meter_end, c_e_last.night_meter_start
from
 (select l_l.num, string_agg(l_o.name, ',') owners, l_l.id,
   charges.electricity_last(l_l.id, 1, 2024) b_i_last_id,
   charges.electricity_last(l_l.id, 12, 2023) b_i_prev_id
   from lands.lands l_l
     left join lands.owner_lands l_ol on(l_ol.land_id=l_l.id)
     left join lands.owners l_o on(l_o.id=l_ol.owner_id)
	 group by l_l.num, l_l.id) l_ll
 left join balances.invoices b_i_last on (b_i_last.id=l_ll.b_i_last_id)
 left join charges.electricity c_e_last on (c_e_last.invoice_id=b_i_last.id)
 left join balances.invoices b_i_prev on (b_i_prev.id=l_ll.b_i_prev_id)
 left join charges.electricity c_e_prev on (c_e_prev.invoice_id=b_i_prev.id)
 
 order by 3