1.
2.
3.
4.
5.
6.
7.
8.
9.
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.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.
110.
111.
112.
113.
114.
115.
116.
117.
118.
119.
120.
121.
122.
123.
124.
125.
126.
127.
128.
129.
130.
131.
132.
133.
134.
135.
136.
137.
138.
139.
140.
141.
142.
143.
144.
145.
146.
147.
148.
149.
150.
151.
152.
153.
154.
155.
156.
157.
158.
159.
160.
161.
162.
163.
164.
165.
166.
167.
168.
169.
170.
171.
172.
173.
174.
175.
176.
177.
178.
179.
create or alter procedure LS_STATS_CALCULATE (
ID_TRANS integer)
as
declare variable LS_TRANS_CODE smallint;
begin
select tls.code from transactions_loyal tls where tls.ID_TRANSACTION=:id_trans into :ls_trans_code;
if (:ls_trans_code not in (3,4)) then exit;
-- Обрабатка дебета
if (:ls_trans_code = 3) then
BEGIN
-- Рассчитываем статистику по транзакции
MERGE INTO transactions_loyal tr_lo
using (with tr_goodz as (
select gts.id_transaction as id_trans,
sum(gts.discountsum_out) as sum_discount_bonus,
sum(gts.sum_in) as in_sum,
sum(gts.plat_calc) as plat_calc,
sum(gts.bonus_calc) as bonus_calc
from transactions_goods gts
where gts.id_transaction = :id_trans
group by gts.id_transaction
),
f_earn as
(
SELECT VD.ID_TRANSACTION as id_trans, VD.VALUE_NUM as f_bonus --, CI.KOEF_BONUS
FROM TRANSACTIONS_LOYAL TL
JOIN D_TEMP_VALUE_DELTA VD ON VD.ID_TRANSACTION = TL.ID_TRANSACTION
JOIN D_SCHEME DS ON DS.ID_TREE = TL.ID_SCHEME
JOIN D_COUNTERS_CLIENTS CC ON CC.COUNTERS_CLIENTS_NUM = DS.ID_COUNTER_BONUS AND CC.ID_CLIENTS = TL.ID_CLIENTS
JOIN D_COUNTERS_INFO CI ON CI.NUM_COUNTERS = DS.ID_COUNTER_BONUS
WHERE TL.ID_TRANSACTION = :ID_TRANS
AND VD.ID_COUNTERS_CLIENTS = CC.ID_COUNTERS_CLIENTS
)
select first 1 tr_l.ID_TRANSACTION as id_trans,
IIF(tr_l.discount_amount is null, 0, tr_l.discount_amount) as f_amount,
IIF(tr_l.discount_sum is null, 0, tr_l.discount_sum)as f_sum,
IIF(tr_l.plat_calc is null, 0, tr_l.plat_calc) as f_plat_calc,
IIF(f_earn.f_bonus is null, 0, f_earn.f_bonus) as f_bonus_earn,
--IIF(tr_l.price is null, 0, tr_l.price) as f_price,
IIF(tr_l.discount_amount is null, 0, tr_l.discount_amount)*IIF(tr_l.price is null, 0, tr_l.price) - IIF(tr_l.discount_sum is null, 0, tr_l.discount_sum) as f_discount_sum,
IIF(tr_goodz.sum_discount_bonus is null, 0, tr_goodz.sum_discount_bonus) as g_sum,
IIF(tr_goodz.plat_calc is null, 0, tr_goodz.plat_calc) as g_plat_calc,
IIF(tr_goodz.bonus_calc is null, 0, tr_goodz.bonus_calc) as g_bonus_earn,
(IIF(tr_goodz.in_sum is null, 0, tr_goodz.in_sum) - IIF(tr_goodz.sum_discount_bonus is null, 0, tr_goodz.sum_discount_bonus)) as g_discount_sum --Выгода
from transactions_loyal tr_l
left join tr_goodz on tr_l.ID_TRANSACTION = tr_goodz.id_trans
left join f_earn on tr_l.ID_TRANSACTION = f_earn.id_trans
where tr_l.ID_TRANSACTION = :id_trans
) tr_ls
ON tr_lo.ID_TRANSACTION = tr_ls.id_trans
WHEN MATCHED and tr_lo.account_struc_id in (1, 2) THEN
UPDATE SET tr_lo.real_goods_sum = tr_ls.g_sum,
tr_lo.real_goods_discount = tr_ls.g_discount_sum,
tr_lo.real_goods_bonus_earn = tr_ls.g_bonus_earn,
tr_lo.real_goods_bonus_payed = tr_ls.g_plat_calc
WHEN MATCHED and tr_lo.account_struc_id IN (SELECT ACS.ID FROM ACCOUNTS_STRUC ACS WHERE ACS.IS_FUEL = 1) THEN
UPDATE SET tr_lo.real_fuel_amount = tr_ls.f_amount,
tr_lo.real_fuel_sum = tr_ls.f_sum,
tr_lo.real_fuel_discount = tr_ls.f_discount_sum,
tr_lo.real_fuel_bonus_earn = tr_ls.f_bonus_earn,
tr_lo.real_fuel_bonus_payed = tr_ls.f_plat_calc,
tr_lo.real_goods_sum = tr_ls.g_sum,
tr_lo.real_goods_discount = tr_ls.g_discount_sum,
tr_lo.real_goods_bonus_earn = tr_ls.g_bonus_earn,
tr_lo.real_goods_bonus_payed = tr_ls.g_plat_calc;
-- Обновляем статистику (по карте) крайней и, если необходимо, первой транзакции
MERGE INTO ls_card_stats ls_ca
using (select first 1 tr_l.session_time as tr_time,
tr_l.id_card as tr_card,
tr_l.id_terminal as id_terminal,
t.id_service_station as id_service_station
from transactions_loyal tr_l
left join terminals t on t.id = tr_l.id_terminal
where tr_l.ID_TRANSACTION = :id_trans
) tr_ls
ON ls_ca.id = tr_ls.tr_card
WHEN MATCHED and ls_ca.ls_first_trans is not null THEN
UPDATE SET ls_ca.ls_last_trans = tr_ls.tr_time
WHEN MATCHED and ls_ca.ls_first_trans is null THEN
UPDATE SET ls_ca.ls_first_trans = tr_ls.tr_time,
ls_ca.ls_last_trans = tr_ls.tr_time,
LS_CA.first_terminal_id = tr_ls.id_terminal,
LS_CA.first_sales_point_id = tr_ls.id_service_station;
--
MERGE INTO ls_card_stats ls_ca
using (select first 1 tr_l.id_card as id_card, min(tr_l.session_time) as tr_time
from transactions_loyal tr_l
where tr_l.ID_TRANSACTION = :id_trans
and ((tr_l.real_goods_bonus_payed > 0) or (tr_l.real_fuel_bonus_payed > 0))
group by tr_l.id_card
) tr_ls
ON ls_ca.id = tr_ls.id_card and ls_ca.ls_first_bonus_pay is null
WHEN MATCHED THEN
UPDATE SET ls_ca.ls_first_bonus_pay = tr_ls.tr_time;
END
-- Обрабатка возврата
if (:ls_trans_code =4) then
BEGIN
-- Рассчитываем статистику по транзакции
MERGE INTO transactions_loyal tr_lo
using (with tr_goodz as (
select gts.id_transaction as id_trans,
sum(gts.discountsum_out) as sum_discount_bonus,
sum(gts.sum_in) as in_sum,
sum(gts.plat_calc) as plat_calc,
sum(gts.bonus_calc) as bonus_calc
from transactions_goods gts
where gts.id_transaction = :id_trans
group by gts.id_transaction
),
f_earn as
(
SELECT VD.ID_TRANSACTION as id_trans, VD.VALUE_NUM as f_bonus --, CI.KOEF_BONUS
FROM TRANSACTIONS_LOYAL TL
JOIN D_TEMP_VALUE_DELTA VD ON VD.ID_TRANSACTION = TL.ID_TRANSACTION
JOIN D_SCHEME DS ON DS.ID_TREE = TL.ID_SCHEME
JOIN D_COUNTERS_CLIENTS CC ON CC.COUNTERS_CLIENTS_NUM = DS.ID_COUNTER_BONUS AND CC.ID_CLIENTS = TL.ID_CLIENTS
JOIN D_COUNTERS_INFO CI ON CI.NUM_COUNTERS = DS.ID_COUNTER_BONUS
WHERE TL.ID_TRANSACTION = :ID_TRANS
AND VD.ID_COUNTERS_CLIENTS = CC.ID_COUNTERS_CLIENTS
)
select first 1 tr_l.id_transaction_return as id_trans,
IIF(tr_l.discount_amount is null, 0, tr_l.discount_amount) as f_amount,
IIF(tr_l.discount_sum is null, 0, tr_l.discount_sum)as f_sum,
IIF(tr_l.plat_calc is null, 0, tr_l.plat_calc) as f_plat_calc,
IIF(f_earn.f_bonus is null, 0, f_earn.f_bonus) as f_bonus_earn,
--IIF(tr_l.price is null, 0, tr_l.price) as f_price,
IIF(tr_l.discount_amount is null, 0, tr_l.discount_amount)*IIF(tr_l.price is null, 0, tr_l.price) - IIF(tr_l.discount_sum is null, 0, tr_l.discount_sum) as f_discount_sum,
IIF(tr_goodz.sum_discount_bonus is null, 0, tr_goodz.sum_discount_bonus) as g_sum,
IIF(tr_goodz.plat_calc is null, 0, tr_goodz.plat_calc) as g_plat_calc,
IIF(tr_goodz.bonus_calc is null, 0, tr_goodz.bonus_calc) as g_bonus_earn,
(IIF(tr_goodz.in_sum is null, 0, tr_goodz.in_sum) - IIF(tr_goodz.sum_discount_bonus is null, 0, tr_goodz.sum_discount_bonus)) as g_discount_sum --Выгода
from transactions_loyal tr_l
left join tr_goodz on tr_l.ID_TRANSACTION = tr_goodz.id_trans
left join f_earn on tr_l.ID_TRANSACTION = f_earn.id_trans
where tr_l.ID_TRANSACTION = :id_trans
) tr_ls
ON tr_lo.ID_TRANSACTION = tr_ls.id_trans
WHEN MATCHED and tr_lo.account_struc_id in (1, 2) THEN
UPDATE SET tr_lo.real_goods_sum = tr_lo.real_goods_sum - tr_ls.g_sum,
tr_lo.real_goods_discount = tr_lo.real_goods_discount - tr_ls.g_discount_sum,
tr_lo.real_goods_bonus_earn = tr_lo.real_goods_bonus_earn - tr_ls.g_bonus_earn,
tr_lo.real_goods_bonus_payed = tr_lo.real_goods_bonus_payed - tr_ls.g_plat_calc
WHEN MATCHED and tr_lo.account_struc_id IN (SELECT ACS.ID FROM ACCOUNTS_STRUC ACS WHERE ACS.IS_FUEL = 1) THEN
UPDATE SET tr_lo.real_fuel_amount = tr_lo.real_fuel_amount - tr_ls.f_amount,
tr_lo.real_fuel_sum = tr_lo.real_fuel_sum - tr_ls.f_sum,
tr_lo.real_fuel_discount = tr_lo.real_fuel_discount - tr_ls.f_discount_sum,
tr_lo.real_fuel_bonus_earn = tr_lo.real_fuel_bonus_earn + tr_ls.f_bonus_earn,
tr_lo.real_fuel_bonus_payed = tr_lo.real_fuel_bonus_payed - tr_ls.f_plat_calc,
tr_lo.real_goods_sum = tr_lo.real_goods_sum - tr_ls.g_sum,
tr_lo.real_goods_discount = tr_lo.real_goods_discount - tr_ls.g_discount_sum,
tr_lo.real_goods_bonus_earn = tr_lo.real_goods_bonus_earn - tr_ls.g_bonus_earn,
tr_lo.real_goods_bonus_payed = tr_lo.real_goods_bonus_payed - tr_ls.g_plat_calc;
END
WHEN ANY DO
BEGIN
exit;
END
end
