· 8 years ago · May 14, 2018, 08:46 AM
1<?php
2
3use Phinx\Migration\AbstractMigration;
4
5class CreateFunctionsForChargeCoverage extends AbstractMigration
6{
7 /**
8 * Функции раÑчета таблицы Ð¿Ð¾ÐºÑ€Ñ‹Ñ‚Ð¸Ñ Ð½Ð°Ñ‡Ð¸Ñлений
9 */
10 public function change()
11 {
12 if (\Constant::VAR_REGION_CODE == 'lipetsk') {
13 // Ð¤ÑƒÐ½ÐºÑ†Ð¸Ñ Ð¿Ñ€ÐµÐ´Ð²Ð°Ñ€Ð¸Ñ‚ÐµÐ»ÑŒÐ½Ð¾Ð³Ð¾ раÑчета Ð¿Ð¾ÐºÑ€Ñ‹Ñ‚Ð¸Ñ (платежи Ñ Ð·Ð°Ð´Ð°Ð½Ð½Ñ‹Ð¼Ð¸ периодами)
14 $sql = "CREATE OR REPLACE FUNCTION fn_charge_coverage_prepayment(IN _billing_bill_ro_account_id int4, IN _billing_bill_spec_account_id int4)
15RETURNS TABLE
16(
17 billing_bill_account_operation_id int,
18 billing_bill_ticket_id int,
19 dt TIMESTAMP,
20 dt_payment TIMESTAMP,
21 dt_period date,
22 billing_bill_service_id int,
23 is_peni smallint,
24 paid NUMERIC(10,2), -- Сумма оплаты (Ñ ÑƒÑ‡ÐµÑ‚Ð¾Ð¼ возвратов)
25 paid_itog NUMERIC(10,2), -- Сумма оплаты Ñ Ð½Ð°Ñ€Ð°Ñтающим итогом по периоду
26 credit NUMERIC(10,2), -- Сумма, которую нужно покрыть (начиÑлено по мин.взноÑу + перераÑчеты)
27 overpayment NUMERIC(10,2), -- переплата/задолженноÑÑ‚ÑŒ (Ñ Ð½Ð°Ñ€Ð°Ñтающим итогом)
28 ostatok_dolg_last_period NUMERIC(10,2), -- непокрытый оÑтаток Ñ Ð½Ð°Ñ€Ð°Ñтающим итогом
29 coverage NUMERIC(10,2) -- Покрыто из таблицы покрытий
30)
31 AS $$
32DECLARE res NUMERIC;
33DECLARE ro_id INT[];
34DECLARE sql text;
35DECLARE acc_exists BOOLEAN;
36DECLARE ticket_exists BOOLEAN;
37DECLARE check_dolg BOOLEAN;
38BEGIN
39
40SELECT EXISTS(select * from information_schema.columns where table_schema like 'pg_temp%' and table_name like 'acc_t') into acc_exists;
41if (acc_exists = false) THEN
42 sql = 'CREATE temporary TABLE acc_t as select * from fn_billing_bill_account_move($1,$2);create index on acc_t(billing_bill_ro_account_id);create index on acc_t(billing_bill_spec_account_id);';
43 execute sql USING _billing_bill_ro_account_id, _billing_bill_spec_account_id;
44end IF;
45SELECT EXISTS(select * from information_schema.columns where table_schema like 'pg_temp%' and table_name like 'ticket_temp') into ticket_exists;
46if (ticket_exists = false) THEN
47 sql = 'CREATE temporary TABLE ticket_temp as select t.id,t.date,t.credit_min,
48(select sum(recalc) from billing_bill_account_operation o where (billing_bill_ro_account_id=t.billing_bill_ro_account_id or billing_bill_spec_account_id=t.billing_bill_spec_account_id) and recalc_dt between t.date and t.date + ''1 month'' and is_peni=0) recalc_t
49from billing_bill_ticket t
50join acc_t on (acc_t.billing_bill_ro_account_id=t.billing_bill_ro_account_id or acc_t.billing_bill_spec_account_id=t.billing_bill_spec_account_id);';
51 sql = concat(sql,'create index on ticket_temp(id);create index on ticket_temp(date);');
52 execute sql;
53end IF;
54
55RETURN QUERY
56select
57pt.payment_id
58,pt.ticket_id
59,pt.dt
60,pt.dt_payment
61,pt.dt_period
62,pt.billing_bill_service_id
63,pt.is_peni
64,pt.paid_itog paid
65,sum(pt.paid_itog) over (partition by pt.dt_period order by pt.payment_id) paid_itog
66,pt.credit_itog credit
67,sum(pt.paid_itog) over (partition by pt.dt_period order by pt.payment_id) - pt.credit_itog overpayment
68,lag(pt.credit_itog - pt.paid_itog,1,pt.credit_itog) over (partition by pt.dt_period order by pt.payment_id) ostatok_dolg_last_period
69,COALESCE(pt.coverage,0) coverage
70from
71(
72select
73o.id payment_id
74,o.paid
75,o.dt_payment
76,o.dt
77,o.dt_period
78,o.billing_bill_service_id
79,o.is_peni
80,o.paid - COALESCE((select sum(COALESCE(recalc,0)) recalc from billing_bill_account_operation r where r.billing_bill_account_operation_type_id=3 and r.parent_id=o.id and r.is_peni=o.is_peni),0) paid_itog
81,t.id ticket_id
82,t.credit_min credit
83,COALESCE(t.recalc_t,0) recalc_t
84,t.credit_min + COALESCE(t.recalc_t,0) credit_itog
85,0::NUMERIC(10,2) coverage
86from acc_t
87join billing_bill_account_operation o on (acc_t.billing_bill_ro_account_id = o.billing_bill_ro_account_id or acc_t.billing_bill_spec_account_id = o.billing_bill_spec_account_id)
88join ticket_temp t on o.dt_period=t.date
89where
90 o.is_peni=0
91and (o.billing_bank_packet_operation_id is not null or o.billing_bill_account_operation_type_id=1)
92) pt;
93
94if (acc_exists = false) THEN
95 sql = 'DROP TABLE acc_t';
96 execute sql;
97END IF;
98if (ticket_exists = false) THEN
99 sql = 'DROP TABLE ticket_temp';
100 execute sql;
101END IF;
102
103END;
104$$ LANGUAGE 'plpgsql';";
105 $this->execute($sql);
106
107 // Ð¤ÑƒÐ½ÐºÑ†Ð¸Ñ Ñ€Ð°Ñчета покрытиÑ
108 $sql="CREATE OR REPLACE FUNCTION fn_charge_coverage_table(IN _billing_bill_ro_account_id int4, IN _billing_bill_spec_account_id int4)
109RETURNS TABLE(billing_bill_account_operation_id int,billing_bill_ticket_id int ,amount NUMERIC(10,2),billing_bill_service_id int,is_peni int) AS $$
110DECLARE res1 NUMERIC;
111DECLARE res2 NUMERIC;
112DECLARE res NUMERIC;
113DECLARE sql TEXT;
114BEGIN
115res=0;
116
117-- Цепочка лицевы Ñчетов
118sql = 'CREATE temporary TABLE acc_t as select * from fn_billing_bill_account_move($1,$2);create index on acc_t(billing_bill_ro_account_id);create index on acc_t(billing_bill_spec_account_id);';
119execute sql USING _billing_bill_ro_account_id, _billing_bill_spec_account_id;
120-- СпиÑок квитанций
121sql = 'CREATE temporary TABLE ticket_temp as select t.id,t.date,t.credit_min,
122(select sum(recalc) from billing_bill_account_operation o where (billing_bill_ro_account_id=t.billing_bill_ro_account_id or billing_bill_spec_account_id=t.billing_bill_spec_account_id) and recalc_dt between t.date and t.date + ''1 month'' and is_peni=0) recalc_t
123from billing_bill_ticket t
124join acc_t on (acc_t.billing_bill_ro_account_id=t.billing_bill_ro_account_id or acc_t.billing_bill_spec_account_id=t.billing_bill_spec_account_id);';
125sql = concat(sql,'create index on ticket_temp(id);create index on ticket_temp(date);');
126execute sql;
127-- Покрытие по платежам, имеющих период
128sql = 'CREATE temporary TABLE prepayment_t as select * from fn_charge_coverage_prepayment($1,$2);create index on prepayment_t (billing_bill_account_operation_id);create index on prepayment_t (billing_bill_ticket_id);';
129execute sql USING _billing_bill_ro_account_id, _billing_bill_spec_account_id;
130
131sql = 'CREATE temporary TABLE billing_bill_payment_ticket_temp (billing_bill_account_operation_id int,billing_bill_ticket_id int ,amount NUMERIC(10,2),billing_bill_service_id int,is_peni int);';
132sql = concat(sql,'create index on billing_bill_payment_ticket_temp(billing_bill_account_operation_id);create index on billing_bill_payment_ticket_temp(billing_bill_ticket_id);');
133execute sql;
134-- Ð’Ñтавка платежей в таблицу покрытий по заÑвленным периодам
135-- Stock control with FIFO costing (https://wiki.postgresql.org/images/a/a2/PostgreSQL_Window_Functions.pdf)
136insert into billing_bill_payment_ticket_temp (billing_bill_account_operation_id,billing_bill_ticket_id,amount,billing_bill_service_id,is_peni)
137select c.billing_bill_account_operation_id
138,c.billing_bill_ticket_id
139,LEAST(c.paid,c.credit,c.ostatok_dolg_last_period) amount
140,c.billing_bill_service_id,c.is_peni
141 from prepayment_t c
142where ostatok_dolg_last_period>0
143and c.paid<>0 and c.credit>c.coverage;
144
145-- Получение оÑтавшихÑÑ Ð¿Ð»Ð°Ñ‚ÐµÐ¶ÐµÐ¹
146-- Получение непокрытых начиÑлений
147-- Формирование покрытий из оÑтатков
148RETURN QUERY with ts as
149(
150select -- ОÑтатки по покрытиÑм
151 case when overpayment>0 then c.billing_bill_account_operation_id else 0 end billing_bill_account_operation_id
152 ,case when overpayment<0 then c.billing_bill_ticket_id else 0 end billing_bill_ticket_id
153 ,LEAST(abs(overpayment),c.paid) amount
154 ,c.billing_bill_service_id
155 ,c.is_peni
156from prepayment_t c
157where ostatok_dolg_last_period<>0 and overpayment<>0 and c.credit>c.coverage
158UNION ALL -- Платежи без покрытиÑ
159select c.billing_bill_account_operation_id
160 ,0 billing_bill_ticket_id
161 ,case when c.paid < c.credit then c.paid else c.credit end amount
162 ,c.billing_bill_service_id,c.is_peni
163 from prepayment_t c
164where ostatok_dolg_last_period=0
165union ALL -- квитанции без покрытиÑ
166 select 0,t.id,t.credit_min+COALESCE(t.recalc_t,0),1,0
167from ticket_temp t
168left join billing_bill_payment_ticket_temp pt on pt.billing_bill_ticket_id=t.id
169where pt.billing_bill_account_operation_id is null --t.billing_bill_ro_account_id=_billing_bill_ro_account_id and pt.id is null
170union ALL -- платежи без периодов
171 select o.id,0,o.paid-sum(COALESCE(r.recalc,0)),o.billing_bill_service_id,o.is_peni
172from acc_t
173 join billing_bill_account_operation o on (acc_t.billing_bill_ro_account_id=o.billing_bill_ro_account_id or acc_t.billing_bill_spec_account_id=o.billing_bill_spec_account_id)
174left join billing_bill_account_operation r on r.parent_id=o.id and r.recalc<>0
175where
176o.dt_period is null
177group by o.id
178having o.paid-sum(COALESCE(r.recalc,0))<>0
179union ALL
180select o.id,0,o.paid-sum(COALESCE(r.recalc,0)),o.billing_bill_service_id,o.is_peni
181from acc_t
182 join billing_bill_account_operation o on (acc_t.billing_bill_ro_account_id=o.billing_bill_ro_account_id or acc_t.billing_bill_spec_account_id=o.billing_bill_spec_account_id)
183left join billing_bill_account_operation r on r.parent_id=o.id and r.recalc<>0
184left join ticket_temp t on o.dt_period=t.date
185where --o.billing_bill_ro_account_id=_billing_bill_ro_account_id and
186t.id is null
187and o.dt_period is not null
188group by o.id
189having o.paid-sum(COALESCE(r.recalc,0))<>0
190)
191select
192tt.*
193from
194(
195select max(t.billing_bill_account_operation_id) over (order by t.total) billing_bill_account_operation_id
196,max(t.billing_bill_ticket_id) over (order by t.total) billing_bill_ticket_id
197,lead(t.Total) over (order by t.Total)-t.Total amount
198,t.billing_bill_service_id
199,t.is_peni
200from (
201select ts.billing_bill_account_operation_id,ts.billing_bill_ticket_id,sum(ts.amount) over(order by ts.billing_bill_ticket_id) - ts.amount total,ts.billing_bill_service_id,ts.is_peni from ts where ts.billing_bill_account_operation_id=0
202union ALL
203select 0,0,sum(ts.amount),min(ts.billing_bill_service_id),min(ts.is_peni) from ts where ts.billing_bill_account_operation_id=0
204UNION ALL
205select ts.billing_bill_account_operation_id,ts.billing_bill_ticket_id,sum(ts.amount) over (order by ts.billing_bill_account_operation_id)-ts.amount,ts.billing_bill_service_id,ts.is_peni from ts where ts.billing_bill_ticket_id=0
206union ALL
207select 0,0,sum(ts.amount),min(ts.billing_bill_service_id),min(ts.is_peni) from ts where ts.billing_bill_ticket_id=0
208) t
209where t.total <=
210(select LEAST(sum(case when ts.billing_bill_account_operation_id=0 then ts.amount else 0 end),sum(case when ts.billing_bill_account_operation_id=0 then 0 else ts.amount end)) from ts)
211
212) tt
213LEFT JOIN billing_bill_payment_ticket_temp pt on pt.billing_bill_account_operation_id=tt.billing_bill_account_operation_id and pt.billing_bill_ticket_id=tt.billing_bill_ticket_id
214and pt.amount=tt.amount and pt.billing_bill_service_id=tt.billing_bill_service_id and pt.is_peni=tt.is_peni
215where COALESCE(tt.amount,0)<>0
216and pt.billing_bill_account_operation_id is NULL
217UNION ALL -- Платежи, покрывающие заданный период
218SELECT * from billing_bill_payment_ticket_temp pt2 where pt2.amount>0;
219
220sql = 'DROP TABLE IF EXISTS acc_t;DROP TABLE IF EXISTS prepayment_t;DROP TABLE IF EXISTS ticket_temp;DROP TABLE IF EXISTS billing_bill_payment_ticket_temp;';
221execute sql;
222
223END;
224$$ LANGUAGE 'plpgsql';";
225 $this->execute($sql);
226
227 //Ð¤ÑƒÐ½ÐºÑ†Ð¸Ñ Ñ€Ð°Ñчета Ð¿Ð¾ÐºÑ€Ñ‹Ñ‚Ð¸Ñ c учетом неÑущеÑтвующих квитанций
228 $sql="CREATE OR REPLACE FUNCTION fn_charge_coverage_prepayment(IN _billing_bill_ro_account_id int4, IN _billing_bill_spec_account_id int4, IN _fantom_ticket BOOLEAN)
229RETURNS TABLE
230(
231 billing_bill_account_operation_id int,
232 billing_bill_ticket_id int,
233 dt TIMESTAMP,
234 dt_payment TIMESTAMP,
235 dt_period date,
236 billing_bill_service_id int,
237 is_peni smallint,
238 paid NUMERIC(10,2), -- Сумма оплаты (Ñ ÑƒÑ‡ÐµÑ‚Ð¾Ð¼ возвратов)
239 paid_itog NUMERIC(10,2), -- Сумма оплаты Ñ Ð½Ð°Ñ€Ð°Ñтающим итогом по периоду
240 credit NUMERIC(10,2), -- Сумма, которую нужно покрыть (начиÑлено по мин.взноÑу + перераÑчеты)
241 overpayment NUMERIC(10,2), -- переплата/задолженноÑÑ‚ÑŒ (Ñ Ð½Ð°Ñ€Ð°Ñтающим итогом)
242 ostatok_dolg_last_period NUMERIC(10,2), -- непокрытый оÑтаток Ñ Ð½Ð°Ñ€Ð°Ñтающим итогом
243 coverage NUMERIC(10,2) -- Покрыто из таблицы покрытий
244)
245 AS $$
246DECLARE res NUMERIC;
247DECLARE ro_id INT[];
248DECLARE sql text;
249DECLARE acc_exists BOOLEAN;
250DECLARE ticket_exists BOOLEAN;
251DECLARE check_dolg BOOLEAN;
252BEGIN
253
254SELECT EXISTS(select * from information_schema.columns where table_schema like 'pg_temp%' and table_name like 'acc_t') into acc_exists;
255if (acc_exists = false) THEN
256 sql = 'CREATE temporary TABLE acc_t as select * from fn_billing_bill_account_move($1,$2);create index on acc_t(billing_bill_ro_account_id);create index on acc_t(billing_bill_spec_account_id);';
257 execute sql USING _billing_bill_ro_account_id, _billing_bill_spec_account_id;
258end IF;
259SELECT EXISTS(select * from information_schema.columns where table_schema like 'pg_temp%' and table_name like 'ticket_temp') into ticket_exists;
260if (ticket_exists = false) THEN
261 sql = 'CREATE temporary TABLE ticket_temp as select t.id,t.date,t.credit_min,
262(select sum(recalc) from billing_bill_account_operation o where (billing_bill_ro_account_id=t.billing_bill_ro_account_id or billing_bill_spec_account_id=t.billing_bill_spec_account_id) and recalc_dt between t.date and t.date + ''1 month'' and is_peni=0) recalc_t
263from billing_bill_ticket t
264join acc_t on (acc_t.billing_bill_ro_account_id=t.billing_bill_ro_account_id or acc_t.billing_bill_spec_account_id=t.billing_bill_spec_account_id)';
265if (_fantom_ticket = true) THEN --Добавить еще не Ñформированные квитанции
266 sql = concat(sql,'union all select
267t2.id + (o.dt_period::date-t2.date::date),o.dt_period,t2.credit_min, 0
268from billing_bill_account_operation o
269join acc_t on (acc_t.billing_bill_ro_account_id=o.billing_bill_ro_account_id or acc_t.billing_bill_spec_account_id=o.billing_bill_spec_account_id)
270left join billing_bill_ticket t on (t.billing_bill_ro_account_id=o.billing_bill_ro_account_id or t.billing_bill_spec_account_id=o.billing_bill_spec_account_id) and t.date=o.dt_period
271join billing_bill_ticket t2 on t2.billing_bill_ro_account_id=o.billing_bill_ro_account_id and t2.id=(select max(id) from billing_bill_ticket where billing_bill_ro_account_id=o.billing_bill_ro_account_id and date<o.dt_period)
272where o.dt_period is not null
273and t.id is null
274group by t2.id,o.dt_period');
275end IF;
276 sql = concat(sql,';create index on ticket_temp(id);create index on ticket_temp(date);');
277 execute sql;
278end IF;
279
280RETURN QUERY
281select
282pt.payment_id
283,pt.ticket_id
284,pt.dt
285,pt.dt_payment
286,pt.dt_period
287,pt.billing_bill_service_id
288,pt.is_peni
289,pt.paid_itog paid
290,sum(pt.paid_itog) over (partition by pt.dt_period order by pt.payment_id) paid_itog
291,pt.credit_itog credit
292,sum(pt.paid_itog) over (partition by pt.dt_period order by pt.payment_id) - pt.credit_itog overpayment
293,lag(pt.credit_itog - pt.paid_itog,1,pt.credit_itog) over (partition by pt.dt_period order by pt.payment_id) ostatok_dolg_last_period
294,COALESCE(pt.coverage,0) coverage
295from
296(
297select
298o.id payment_id
299,o.paid
300,o.dt_payment
301,o.dt
302,o.dt_period
303,o.billing_bill_service_id
304,o.is_peni
305,o.paid - COALESCE((select sum(COALESCE(recalc,0)) recalc from billing_bill_account_operation r where r.billing_bill_account_operation_type_id=3 and r.parent_id=o.id and r.is_peni=o.is_peni),0) paid_itog
306,t.id ticket_id
307,t.credit_min credit
308,COALESCE(t.recalc_t,0) recalc_t
309,t.credit_min + COALESCE(t.recalc_t,0) credit_itog
310,0::NUMERIC(10,2) coverage
311from acc_t
312join billing_bill_account_operation o on (acc_t.billing_bill_ro_account_id = o.billing_bill_ro_account_id or acc_t.billing_bill_spec_account_id = o.billing_bill_spec_account_id)
313join ticket_temp t on o.dt_period=t.date
314where
315 o.is_peni=0
316and (o.billing_bank_packet_operation_id is not null or o.billing_bill_account_operation_type_id=1)
317) pt;
318
319if (acc_exists = false) THEN
320 sql = 'DROP TABLE acc_t';
321 execute sql;
322END IF;
323if (ticket_exists = false) THEN
324 sql = 'DROP TABLE ticket_temp';
325 execute sql;
326END IF;
327
328END;
329$$ LANGUAGE 'plpgsql';";
330 $this->execute($sql);
331 }
332 }
333}