· 8 years ago · Dec 08, 2017, 06:44 AM
1CREATE OR REPLACE FUNCTION fs_calculate_cashback_ppob_member(character varying, bigint, character varying, bigint, character varying)
2 RETURNS void AS
3$BODY$
4DECLARE
5
6 pSessionId ALIAS FOR $1;
7 pTenantId ALIAS FOR $2;
8 pDatetime ALIAS FOR $3;
9 pUserId ALIAS FOR $4;
10 pProcessDate ALIAS FOR $5;
11
12 vYes character varying(1);
13 vNo character varying(1);
14
15 vParamCashbackDsAmount character varying;
16 vParamCashbackDirectSellingAmount character varying;
17 vParamCashbackSponsorAmount character varying;
18
19 vCashbackDsAmount numeric;
20 vCashbackDirectSellingAmount numeric;
21 vCashbackSponsorAmount numeric;
22
23 vCashbackTypeDs character varying;
24 vCashbackTypeDirectSelling character varying;
25 vCashbackTypeSponsor character varying;
26
27 vCurrCode character varying;
28 vProcessName character varying;
29BEGIN
30
31 vYes := 'Y';
32 vNo := 'N';
33
34 vParamCashbackDsAmount := 'CASHBACK.PPOB.DS.AMOUNT';
35 vParamCashbackDirectSellingAmount := 'CASHBACK.PPOB.DIRECT.SELLING.AMOUNT';
36 vParamCashbackSponsorAmount := 'CASHBACK.PPOB.SPONSOR.AMOUNT';
37
38 vCashbackTypeDs := 'CASHBACK.DS';
39 vCashbackTypeDirectSelling := 'CASHBACK.DIRECT.SELLING';
40 vCashbackTypeSponsor := 'CASHBACK.SPONSOR';
41
42 vProcessName := 'fs_calculate_cashback_ppob_member';
43
44 /*
45 * 1. Pastikan data mlm_trx_penjualan_pulsa di TaskHub sudah terisi untuk tanggal transaksi yang mau diproses. Jika belum ada, maka function akan mengembalikan informasi Belum ada data transaksi untuk saat ini.
46 * 2. Catat tanggal dijalankan function untuk menghindari function dijalankan lebih dari 1x
47 * 3. Catat COUNT banyaknya transaksi per member ke table tt_member_total_transaction
48 * 4. Mendapatkan informasi hubungan member dan DS yang berada dalam 1 pohon jaringan dan tampung dalam table tt_member_cashback_ppob
49 * 5. Hitung cashback DS (untuk DS yang aktif) berdasarkan data yang didapat dari no. 3, hitung SUM cashback yang didapatkan untuk DS tersebut dan tampung dalam table tt_cashback_ppob_member
50 * 6. Hitung Cashback direct selling untuk masing-masing member yang melakukan transaksi pulsa dan tampung dalam table tt_cashback_ppob_member
51 * 7. Member yang melakukan transaksi, hitung cashback Sponsor
52 * 8. Data yang sudah dihitung di no 4, 5 dan 6, INSERT / UPDATE datanya ke table mlm_log_member_cashback_ppob_balance dan UPDATE cashback amount ke table mlm_member_cashback_ppob_balance
53 * 9. INSERT ke table mlm_member_daily_summary_trx_pulsa untukcashback yang dihitung pada hari tanggal proses
54 */
55
56 SELECT f_get_value_system_config_by_param_code(pTenantId, vParamCashbackDsAmount)::numeric INTO vCashbackDsAmount;
57 SELECT f_get_value_system_config_by_param_code(pTenantId, vParamCashbackDirectSellingAmount)::numeric INTO vCashbackDirectSellingAmount;
58 SELECT f_get_value_system_config_by_param_code(pTenantId, vParamCashbackSponsorAmount)::numeric INTO vCashbackSponsorAmount;
59 SELECT f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku')::character varying INTO vCurrCode;
60
61 RAISE NOTICE '>> vCashbackDsAmount : %', vCashbackDsAmount;
62 RAISE NOTICE '>> vCashbackDirectSellingAmount : %', vCashbackDirectSellingAmount;
63 RAISE NOTICE '>> vCashbackSponsorAmount : %', vCashbackSponsorAmount;
64 RAISE NOTICE '>> vCurrCode : %', vCurrCode;
65
66 DELETE FROM tt_member_total_transaction WHERE session_id = pSessionId;
67 DELETE FROM tt_member_cashback_ppob WHERE session_id = pSessionId;
68 DELETE FROM tt_cashback_ppob_member WHERE session_id = pSessionId;
69
70 -- Jika Sudah pernah dijalankan, Function Mengembalikan Exception
71 IF EXISTS (
72 SELECT 1 FROM m_function_system_process
73 WHERE SUBSTRING(process_datetime, 1, 8) = pProcessDate
74 ) THEN
75 RAISE EXCEPTION 'Perhitungan Cashback untuk tanggal % Sudah pernah dilakukan', pProcessDate;
76 END IF;
77
78 -- 1.pastikan data mlm_trx_penjualan_pulsa di TaskHub sudah terisi untuk tanggal transaksi yang mau diproses. Jika belum ada, maka function akan mengembalikan informasi Belum ada data transaksi untuk saat ini.
79 IF EXISTS (
80 SELECT 1 FROM mlm_trx_penjualan_pulsa WHERE SUBSTRING(purchase_datetime, 1, 6) = pProcessDate
81 ) THEN
82 -- 2.catat tanggal dijalankan function untuk menghindari function dijalankan lebih dari 1x
83 INSERT INTO m_function_system_process (
84 process_name, process_datetime,
85 create_datetime, create_user_id,
86 update_datetime, update_user_id, version
87 )
88 SELECT vProcessName, pDatetime,
89 pDatetime, pUserId,
90 pDatetime, pUserId, 0;
91
92 -- 3.Catat COUNT banyaknya transaksi per member ke table tt_member_total_transaction
93 INSERT INTO tt_member_total_transaction (session_id, member_id, total_transaction)
94 SELECT pSessionId, member_id, COUNT(1)
95 FROM mlm_trx_penjualan_pulsa A
96 INNER JOIN mlm_member B ON A.member_code = B.member_code AND B.active = 'Y'
97 WHERE SUBSTRING(A.purchase_datetime,1,8) = pProcessDate
98 GROUP BY member_id;
99
100 -- 4.dapatkan informasi hubungan member dan DS yang berada dalam 1 pohon jaringan dan tampung dalam table tt_member_cashback_ppob
101 WITH upline_depth AS (
102 SELECT A.member_id, MIN(depth) AS depth
103 FROM tt_member_total_transaction A
104 INNER JOIN mlm_member_tree B ON A.member_id = B.member_id
105 INNER JOIN mlm_ds C ON B.upline_id = C.member_id
106 WHERE session_id = pSessionId
107 GROUP BY A.member_id
108 )
109 INSERT INTO tt_member_cashback_ppob (session_id, member_id, ds_id, sponsor_id)
110 SELECT pSessionId, A.member_id, B.upline_id, C.sponsor_id
111 FROM upline_depth A
112 INNER JOIN mlm_member_tree B ON A.member_id = B.member_id AND A.depth = B.depth
113 INNER JOIN mlm_member C ON A.member_id = C.member_id;
114
115 -- 5.Hitung cashback DS (untuk DS yang aktif) berdasarkan data yang didapat dari no. 3, hitung SUM cashback yang didapatkan untuk DS tersebut dan tampung dalam table tt_cashback_ppob_member
116 INSERT INTO tt_cashback_ppob_member (session_id, member_id, cashback_amount, cashback_type)
117 SELECT pSessionId, B.ds_id, (SUM(A.total_transaction) * 50), vCashbackTypeDs
118 FROM tt_member_total_transaction A
119 INNER JOIN tt_member_cashback_ppob B ON A.member_id = B.member_id
120 WHERE A.session_id = pSessionId
121 GROUP BY B.ds_id;
122
123 -- 6.Hitung Cashback direct selling untuk masing-masing member yang melakukan transaksi pulsa dan tampung dalam table tt_cashback_ppob_member
124 INSERT INTO tt_cashback_ppob_member (session_id, member_id, cashback_amount, cashback_type)
125 SELECT pSessionId, A.member_id, (total_transaction * 100) AS cashback_type, vParamCashbackDirectSellingAmount
126 FROM tt_member_total_transaction A
127 WHERE session_id = pSessionId;
128
129 -- 7.untuk member yang melakukan transaksi, hitung cashback Sponsor
130 -- Sponsor yang mendapatkan hanya Sponsor yang bukan DS
131 INSERT INTO tt_cashback_ppob_member (session_id, member_id, cashback_amount, cashback_type)
132 SELECT pSessionId, B.sponsor_id, (SUM(A.total_transaction) * 50), vParamCashbackSponsor
133 FROM tt_member_total_transaction A
134 INNER JOIN tt_member_cashback_ppob B ON A.member_id = B.member_id
135 WHERE A.session_id = pSessionId
136 AND NOT EXISTS (SELECT 1 FROM mlm_ds Z WHERE B.sponsor_id = Z.member_id)
137 GROUP BY B.sponsor_id;
138
139 -- 8.untuk data yang sudah dihitung di no 4, 5 dan 6, INSERT / UPDATE datanya ke table mlm_log_member_cashback_ppob_balance dan UPDATE cashback amount ke table mlm_member_cashback_ppob_balance
140 -- INSERT Cashback_cmount untuk member yang belum ada di table mlm_member_cashback_ppob_balance
141 INSERT INTO mlm_member_cashback_ppob_balance (
142 member_id, cashback_amount, curr_code,
143 create_datetime, create_user_id,
144 update_datetime, update_user_id, version)
145 SELECT A.member_id, SUM(A.cashback_amount), vCurrCode,
146 pDatetime, pUserId,
147 pDatetime, pUserId, 0
148 FROM tt_cashback_ppob_member A
149 WHERE session_id = pSessionId
150 AND NOT EXISTS (SELECT 1 FROM mlm_member_cashback_ppob_balance Z WHERE A.member_id = Z.member_id)
151 GROUP BY A.member_id;
152
153 -- UPDATE cashback_amount untuk member yang sudah ada di table mlm_member_cashback_ppob_balance
154 WITH member_cashback AS (
155 SELECT member_id, SUM(cashback_amount) AS total_cashback
156 FROM tt_cashback_ppob_member
157 WHERE session_id = pSessionId
158 )
159 UPDATE mlm_member_cashback_ppob_balance A
160 SET
161 cashback_amount = A.cashback_amount + Z.total_cashback,
162 version = A.version + 1,
163 update_datetime = pDatetime,
164 update_user_id = pUserId
165 FROM member_cashback Z
166 WHERE session_id = pSessionId
167 AND A.member_id = Z.member_id;
168
169 -- 9. INSERT ke table LOG mlm_log_member_cashback_ppob_balance
170 INSERT INTO mlm_log_member_cashback_ppob_balance (
171 member_id, transaction_date, cashback_amount,
172 curr_code, remark, create_datetime, create_user_id,
173 update_datetime, update_user_id, version)
174 SELECT A.member_id, pProcessDate, SUM(A.cashback_amount),
175 vCurrCode, 'SYSTEM GENERATED ADD CASHBACK FOR PPOB TRANSACTION '||pProcessDate, pDatetime, pUserId,
176 pDatetime, pUserId, 0
177 FROM tt_cashback_ppob_member A
178 WHERE A.session_id = pSessionId
179 GROUP BY A.member_id;
180
181 ELSE
182 RAISE NOTICE 'TIDAK ADA DATA VOUCHER YANG BISA DIPROSES';
183 END IF;
184
185 DELETE FROM tt_member_total_transaction WHERE session_id = pSessionId;
186 DELETE FROM tt_member_cashback_ppob WHERE session_id = pSessionId;
187 DELETE FROM tt_cashback_ppob_member WHERE session_id = pSessionId;
188
189END;
190$BODY$
191 LANGUAGE plpgsql VOLATILE
192 COST 100;