· 8 years ago · Jan 03, 2018, 05:10 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 vTrxSuccess character varying(1);
15 vTrxError character varying(1);
16
17 vParamCashbackDsAmount character varying;
18 vParamCashbackDirectSellingAmount character varying;
19 vParamCashbackSponsorAmount character varying;
20
21 vCashbackDsAmount numeric;
22 vCashbackDirectSellingAmount numeric;
23 vCashbackSponsorAmount numeric;
24
25 vCashbackTypeDs character varying;
26 vCashbackTypeDirectSelling character varying;
27 vCashbackTypeSponsor character varying;
28
29 vCurrCode character varying;
30 vProcessName character varying;
31 vDailySummaryId bigint;
32
33 vDocDescCalculateCashback character varying;
34BEGIN
35
36 vYes := 'Y';
37 vNo := 'N';
38 vTrxSuccess := 'S';
39 vTrxError := 'E';
40 vDailySummaryId := nextval('mlm_member_daily_summary_trx_pulsa_seq');
41 vDocDescCalculateCashback := 'CALCULATE.CASHBACK';
42
43 vParamCashbackDsAmount := 'CASHBACK.PPOB.DS.AMOUNT';
44 vParamCashbackDirectSellingAmount := 'CASHBACK.PPOB.DIRECT.SELLING.AMOUNT';
45 vParamCashbackSponsorAmount := 'CASHBACK.PPOB.SPONSOR.AMOUNT';
46
47 vCashbackTypeDs := 'CASHBACK.DS';
48 vCashbackTypeDirectSelling := 'CASHBACK.DIRECT.SELLING';
49 vCashbackTypeSponsor := 'CASHBACK.SPONSOR';
50
51 vProcessName := 'fs_calculate_cashback_ppob_member';
52
53 /*
54 * 1. Pastikan data mlm_trx_penjualan_pulsa ada data dengan flg_process = 'N'. Jika belum ada, maka function akan mengembalikan informasi Belum ada data transaksi untuk saat ini.
55 * 2. INSERT ke table tt_trx_penjualan_pulsa_calculate untuk data-data transaksi penjualan pulsa yang memiliki flg_process = 'N' (tidak perduli status)
56 * 3. Catat COUNT banyaknya transaksi per member dari table tt_trx_penjualan_pulsa_calculate ke table tt_member_total_transaction untuk yang status = 'S'
57 * 4. Catat tanggal dijalankan function ke table mlm_member_daily_summary_trx_pulsa
58 * 5. INSERT ke table mlm_member_daily_summary_trx_pulsa_detail untuk cashback yang dihitung pada hari tanggal proses (COUNT transaksi)
59 * 6. Mendapatkan semua informasi hubungan member dan DS yang berada dalam 1 pohon jaringan serta sponsor dan tampung dalam table tt_member_cashback_ppob
60 * 7. Hitung cashback DS (untuk DS yang aktif) berdasarkan data yang didapat dari table tt_member_cashback_ppob, hitung SUM cashback yang didapatkan untuk DS tersebut dan tampung dalam table tt_cashback_ppob_member
61 * 8. Hitung Cashback direct selling untuk masing-masing member yang melakukan transaksi pulsa dan tampung dalam table tt_cashback_ppob_member
62 * 9. Hitung Cashback sponsor untuk masing-masing member yang melakukan transaksi pulsa dan tampung dalam table tt_cashback_ppob_member
63 * 10. Data yang sudah dihitung UPDATE datanya ke table mlm_member_cashback_ppob_balance (Saldo balance cashback instan yang exists untuk ditambahkan penambahan amount cashbacknya dengan SUM cashback amount di table tt_cashback_ppob_member)
64 * 11. INSERT data ke table mlm_member_cashback_ppob_balance yang not exists sesuai dengan SUM cashback amount di table tt_cashback_ppob_member
65 * 12. INSERT datanya ke table mlm_log_member_cashback_ppob_balance berdasarkan data di table tt_cashback_ppob_member di group by cashack_type dan member_id. doc_desc diisi dengan CALCULATE.CASHBACK
66 * 13. UPDATE flg_process dan process_datetime yag ada di table mlm_trx_penjualan_pulsa yang ada di table tt_trx_penjualan_pulsa_calculate
67 * 14. Recheck semua SELECT yang pakai table temp, harus menggunakan session_id
68 */
69
70 SELECT f_get_value_system_config_by_param_code(pTenantId, vParamCashbackDsAmount)::numeric INTO vCashbackDsAmount;
71 SELECT f_get_value_system_config_by_param_code(pTenantId, vParamCashbackDirectSellingAmount)::numeric INTO vCashbackDirectSellingAmount;
72 SELECT f_get_value_system_config_by_param_code(pTenantId, vParamCashbackSponsorAmount)::numeric INTO vCashbackSponsorAmount;
73 SELECT f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku')::character varying INTO vCurrCode;
74
75 RAISE NOTICE '>> vCashbackDsAmount : %', vCashbackDsAmount;
76 RAISE NOTICE '>> vCashbackDirectSellingAmount : %', vCashbackDirectSellingAmount;
77 RAISE NOTICE '>> vCashbackSponsorAmount : %', vCashbackSponsorAmount;
78 RAISE NOTICE '>> vCurrCode : %', vCurrCode;
79
80 DELETE FROM tt_trx_penjualan_pulsa_calculate WHERE session_id = pSessionId;
81 DELETE FROM tt_member_total_transaction WHERE session_id = pSessionId;
82 DELETE FROM tt_member_cashback_ppob WHERE session_id = pSessionId;
83 DELETE FROM tt_cashback_ppob_member WHERE session_id = pSessionId;
84
85 -- 1. Pastikan data mlm_trx_penjualan_pulsa ada data dengan flg_process = 'N'.
86 -- Jika belum ada, maka function akan mengembalikan informasi Belum ada data transaksi untuk saat ini.
87 IF EXISTS (
88 SELECT 1 FROM mlm_trx_penjualan_pulsa WHERE flg_process = vNo
89 ) THEN
90 -- 2. INSERT ke table tt_trx_penjualan_pulsa_calculate untuk data-data transaksi penjualan pulsa yang memiliki flg_process = 'N' (tidak perduli status)
91 INSERT INTO tt_trx_penjualan_pulsa_calculate (
92 session_id, trx_pulsa_id, member_code,
93 item_name, item_amount, pv_percent,
94 msisdn, purchase_datetime, status)
95 SELECT pSessionId, trx_pulsa_id, member_code,
96 item_name, item_amount, pv_percent,
97 msisdn, purchase_datetime, status
98 FROM mlm_trx_penjualan_pulsa
99 WHERE flg_process = vNo;
100
101 -- 3. Catat COUNT banyaknya transaksi per member dari table tt_trx_penjualan_pulsa_calculate ke table tt_member_total_transaction untuk yang status = 'S'
102 INSERT INTO tt_member_total_transaction (session_id, member_id, total_transaction)
103 SELECT pSessionId, B.member_id, COUNT(1)
104 FROM tt_trx_penjualan_pulsa_calculate A
105 INNER JOIN mlm_member B ON A.member_code = B.member_code
106 WHERE session_id = pSessionId
107 AND status = vTrxSuccess
108 GROUP BY B.member_id;
109
110 -- 4. Catat tanggal dijalankan function ke table mlm_member_daily_summary_trx_pulsa
111 INSERT INTO mlm_member_daily_summary_trx_pulsa (
112 daily_summary_pulsa_id, process_name, process_date,
113 create_datetime, create_user_id, update_datetime,
114 update_user_id, version)
115 SELECT vDailySummaryId, vProcessName, pProcessDate,
116 pDatetime, pUserId, pDatetime,
117 pUserId, 0;
118
119 -- 5. INSERT ke table mlm_member_daily_summary_trx_pulsa_detail untuk cashback yang dihitung pada hari tanggal proses (COUNT transaksi)
120 INSERT INTO mlm_member_daily_summary_trx_pulsa_detail (
121 daily_summary_pulsa_id, member_id, total_trx,
122 create_datetime, create_user_id, update_datetime,
123 update_user_id, version)
124 SELECT vDailySummaryId, member_id, total_transaction,
125 pDatetime, pUserId, pDatetime,
126 pUserId, 0
127 FROM tt_member_total_transaction
128 WHERE session_id = pSessionId;
129
130 -- 6. Mendapatkan semua informasi hubungan member dan DS yang berada dalam 1 pohon jaringan serta sponsor dan tampung dalam table tt_member_cashback_ppob
131 WITH upline_depth AS (
132 SELECT A.member_id, MIN(depth) AS depth
133 FROM tt_member_total_transaction A
134 INNER JOIN mlm_member_tree B ON A.member_id = B.member_id
135 INNER JOIN mlm_ds C ON B.upline_id = C.member_id
136 WHERE session_id = pSessionId
137 GROUP BY A.member_id
138 )
139 INSERT INTO tt_member_cashback_ppob (session_id, member_id, ds_id, sponsor_id)
140 SELECT pSessionId, A.member_id, B.upline_id, C.sponsor_id
141 FROM upline_depth A
142 INNER JOIN mlm_member_tree B ON A.member_id = B.member_id AND A.depth = B.depth
143 INNER JOIN mlm_member C ON A.member_id = C.member_id;
144
145 -- 7. Hitung cashback DS (untuk DS yang aktif) berdasarkan data yang didapat dari table tt_member_cashback_ppob, hitung SUM cashback yang didapatkan untuk DS tersebut dan tampung dalam table tt_cashback_ppob_member
146 INSERT INTO tt_cashback_ppob_member (session_id, member_id, cashback_amount, cashback_type)
147 SELECT pSessionId, B.ds_id, (SUM(A.total_transaction) * vCashbackDsAmount) AS cashback_amount, vCashbackTypeDs
148 FROM tt_member_total_transaction A
149 INNER JOIN tt_member_cashback_ppob B ON A.member_id = B.member_id
150 INNER JOIN mlm_ds C ON B.ds_id = C.member_id
151 WHERE A.session_id = pSessionId
152 AND C.active = vYes
153 GROUP BY B.ds_id;
154
155 -- 8. Hitung Cashback direct selling untuk masing-masing member yang melakukan transaksi pulsa dan tampung dalam table tt_cashback_ppob_member
156 INSERT INTO tt_cashback_ppob_member (session_id, member_id, cashback_amount, cashback_type)
157 SELECT pSessionId, A.member_id, (total_transaction * vCashbackDirectSellingAmount) AS cashback_amount, vCashbackTypeDirectSelling
158 FROM tt_member_total_transaction A
159 WHERE session_id = pSessionId;
160
161 -- 9. Hitung Cashback sponsor untuk masing-masing member yang melakukan transaksi pulsa dan
162 -- tampung dalam table tt_cashback_ppob_member. Member yang mendapatkan Cashback Sponsor tidak boleh DS
163 INSERT INTO tt_cashback_ppob_member (session_id, member_id, cashback_amount, cashback_type)
164 SELECT pSessionId, B.sponsor_id, (SUM(A.total_transaction) * vCashbackSponsorAmount) AS cashback_amount, vCashbackTypeSponsor
165 FROM tt_member_total_transaction A
166 INNER JOIN tt_member_cashback_ppob B ON A.member_id = B.member_id
167 WHERE A.session_id = pSessionId
168 AND NOT EXISTS (SELECT 1 FROM mlm_ds Z WHERE B.sponsor_id = Z.member_id AND Z.active = vYes)
169 GROUP BY B.sponsor_id;
170
171 -- 10. Data yang sudah dihitung UPDATE datanya ke table mlm_member_cashback_ppob_balance (Saldo balance cashback instan yang exists untuk ditambahkan penambahan amount cashbacknya dengan SUM cashback amount di table tt_cashback_ppob_member)
172 WITH member_cashback AS (
173 SELECT member_id, SUM(cashback_amount) AS total_cashback
174 FROM tt_cashback_ppob_member
175 WHERE session_id = pSessionId
176 GROUP BY member_id
177 )
178 UPDATE mlm_member_cashback_ppob_balance A
179 SET
180 cashback_amount = A.cashback_amount + Z.total_cashback,
181 version = A.version + 1,
182 update_datetime = pDatetime,
183 update_user_id = pUserId
184 FROM member_cashback Z
185 WHERE A.member_id = Z.member_id;
186
187 -- 11. INSERT data ke table mlm_member_cashback_ppob_balance yang not exists sesuai dengan SUM cashback amount di table tt_cashback_ppob_member
188 INSERT INTO mlm_member_cashback_ppob_balance (
189 member_id, cashback_amount, curr_code,
190 create_datetime, create_user_id,
191 update_datetime, update_user_id, version)
192 SELECT A.member_id, SUM(A.cashback_amount), vCurrCode,
193 pDatetime, pUserId,
194 pDatetime, pUserId, 0
195 FROM tt_cashback_ppob_member A
196 WHERE session_id = pSessionId
197 AND NOT EXISTS (SELECT 1 FROM mlm_member_cashback_ppob_balance Z WHERE A.member_id = Z.member_id)
198 GROUP BY A.member_id;
199
200 -- 12. INSERT datanya ke table mlm_log_member_cashback_ppob_balance berdasarkan data di table tt_cashback_ppob_member di group by cashack_type dan member_id. doc_desc diisi dengan CALCULATE.CASHBACK
201 INSERT INTO mlm_log_member_cashback_ppob_balance (
202 member_id, transaction_date, cashback_amount, curr_code,
203 remark, create_datetime, create_user_id, update_datetime,
204 update_user_id, version, doc_desc, cashback_type)
205 SELECT member_id, pProcessDate, SUM(cashback_amount), vCurrCode,
206 'CALCULATE CASHBACK FOR PPOB TRANSACTION '||pProcessDate, pDatetime, pUserId, pDatetime,
207 pUserId, 0, vDocDescCalculateCashback, cashback_type
208 FROM tt_cashback_ppob_member
209 WHERE session_id = pSessionId
210 GROUP BY member_id, cashback_type;
211
212 -- 13. UPDATE flg_process dan process_datetime yag ada di table mlm_trx_penjualan_pulsa yang ada di table tt_trx_penjualan_pulsa_calculate
213 UPDATE mlm_trx_penjualan_pulsa Z
214 SET
215 flg_process = vYes,
216 process_datetime = pDatetime
217 FROM tt_trx_penjualan_pulsa_calculate A
218 WHERE A.session_id = pSessionId
219 AND A.trx_pulsa_id = Z.trx_pulsa_id;
220 ELSE
221 RAISE NOTICE 'TIDAK ADA DATA VOUCHER YANG BISA DIPROSES';
222 END IF;
223
224 DELETE FROM tt_trx_penjualan_pulsa_calculate WHERE session_id = pSessionId;
225 DELETE FROM tt_member_total_transaction WHERE session_id = pSessionId;
226 DELETE FROM tt_member_cashback_ppob WHERE session_id = pSessionId;
227 DELETE FROM tt_cashback_ppob_member WHERE session_id = pSessionId;
228
229END;
230$BODY$
231 LANGUAGE plpgsql VOLATILE
232 COST 100;