· 8 years ago · Feb 18, 2018, 10:00 PM
1## PROBLEM
2
3I need to insert new "member_transactions" when days_to_return have expired since the transaction was posted. Based on the total commission_amount of a member_transaction parent-child lineage, the new type will be 'void' or 'closure' if the total is 0, it will be void, more than 0, it is a closure.
4
5- The new transaction amounts and commission amounts will be $0.00
6- The parent_member_transaction_id of the new record will be the member_transaction_id of the current parent-most record.
7- The real key for a set of member_transactions is: affiliate_id, site_id, member_id, order_id and posted_date.
8
9## SQL
10
11TRUNCATE TABLE sites CASCADE;
12TRUNCATE TABLE affiliates CASCADE;
13TRUNCATE TABLE aggregators CASCADE;
14TRUNCATE TABLE users CASCADE;
15TRUNCATE TABLE members CASCADE;
16
17INSERT INTO aggregators (aggregator_id, name) VALUES (0, 'Linkshare');
18
19INSERT INTO affiliates (affiliate_id, aggregator_id, aggregator_affiliate_id, name, days_to_return) VALUES (0, 0, 0, 'Test Affiliate', 20);
20
21INSERT INTO sites (site_id, name) VALUES (0, 'Test Site');
22
23INSERT INTO users (user_id, user_name, password) VALUES (0, 'testuser', 'password');
24
25INSERT INTO members (member_id, user_id) VALUES (0, 0);
26
27INSERT INTO member_transactions (member_transaction_id, parent_member_transaction_id, member_id, site_id, affiliate_id, transaction_amount, commission_amount, transaction_date, posted_date, transaction_type, is_closed, created_date, order_id)
28 VALUES (0, NULL, 0, 0, 0, 123.45, 12.35, '1-1-2011', '1-2-2011', 'original', FALSE, '3-10-2011 11:50:03.345', 'testorder-update');
29
30INSERT INTO member_transaction_line_items (member_transaction_id, product_sku, quantity, sale_price, commission, transaction_type, transaction_date, posted_date, order_id, created_date)
31 VALUES (0, 'testsku1', 2, 100.00, 10.00, 'original', '1-1-2011', '1-2-2011', 'testorder-update', '3-10-2011 11:50:03.345');
32
33INSERT INTO member_transaction_line_items (member_transaction_id, product_sku, quantity, sale_price, commission, transaction_type, transaction_date, posted_date, order_id, created_date)
34 VALUES (0, 'testsku2', 1, 23.45, 2.35, 'original', '1-1-2011', '1-2-2011', 'testorder-update', '3-10-2011 11:50:03.345');
35
36INSERT INTO member_transactions (member_transaction_id, parent_member_transaction_id, member_id, site_id, affiliate_id, transaction_amount, commission_amount, transaction_date, posted_date, transaction_type, is_closed, created_date, order_id)
37 VALUES (1, 0, 0, 0, 0, -23.45, -2.35, '1-1-2011', '1-2-2011', 'update', FALSE, '3-10-2011 11:56:03.345', 'testorder-update');
38
39INSERT INTO member_transaction_line_items (member_transaction_id, product_sku, quantity, sale_price, commission, transaction_type, transaction_date, posted_date, order_id, created_date)
40 VALUES (1, 'testsku1', 2, 100.00, 10.00, 'original', '1-1-2011', '1-2-2011', 'testorder-update', '3-10-2011 11:56:03.345');
41
42INSERT INTO member_transactions (member_transaction_id, parent_member_transaction_id, member_id, site_id, affiliate_id, transaction_amount, commission_amount, transaction_date, posted_date, transaction_type, is_closed, created_date, order_id)
43 VALUES (2, NULL, 0, 0, 0, 34.45, 5.35, '2-7-2011', '2-8-2011', 'closure', TRUE, '3-10-2011 11:57:03.345', 'testorder-closure');
44
45INSERT INTO member_transaction_line_items (member_transaction_id, product_sku, quantity, sale_price, commission, transaction_type, transaction_date, posted_date, order_id, created_date)
46 VALUES (2, 'testsku3', 2, 34.45, 5.35, 'closure', '2-7-2011', '2-8-2011', 'testorder-update', '3-10-2011 11:57:03.345');
47
48INSERT INTO member_transactions (member_transaction_id, parent_member_transaction_id, member_id, site_id, affiliate_id, transaction_amount, commission_amount, transaction_date, posted_date, transaction_type, is_closed, created_date, order_id)
49 VALUES (3, NULL, 0, 0, 0, 334.45, 15.35, '2-12-2011', '2-13-2011', 'original', FALSE, '3-10-2011 11:58:03.345', 'testorder-void');
50
51INSERT INTO member_transaction_line_items (member_transaction_id, product_sku, quantity, sale_price, commission, transaction_type, transaction_date, posted_date, order_id, created_date)
52 VALUES (3, 'testsku4', 2, 334.45, 15.35, 'original', '2-12-2011', '2-13-2011', 'testorder-update', '3-10-2011 11:58:03.345');
53
54INSERT INTO member_transactions (member_transaction_id, parent_member_transaction_id, member_id, site_id, affiliate_id, transaction_amount, commission_amount, transaction_date, posted_date, transaction_type, is_closed, created_date, order_id)
55 VALUES (4, 3, 0, 0, 0, -334.45, -15.35, '2-12-2011', '2-13-2011', 'update', FALSE, '3-10-2011 11:59:03.345', 'testorder-void');
56
57
58CREATE OR REPLACE FUNCTION closeExpiredLinkshareCommissions() RETURNS INT AS
59$closeExpiredLinkshareCommissions$
60 DECLARE current_transaction RECORD;
61 BEGIN
62 FOR current_transaction IN
63 WITH RECURSIVE expired_transactions AS (
64 SELECT mt.member_transaction_id, mt.site_id, mt.member_id, mt.affiliate_id, mt.parent_member_transaction_id, mt.transaction_amount, mt.commission_amount, mt.is_closed, mt.aggregator_order_id, mt.transaction_date, mt.posted_date, mt.order_id, mt.transaction_type, mt.created_date, mt.commission_amount
65 FROM member_transactions AS mt
66 JOIN affiliates AS a ON a.affiliate_id = mt.affiliate_id
67 JOIN aggregators AS agg ON agg.aggregator_id = a.aggregator_id
68 WHERE agg.name = 'Linkshare'
69 AND mt.is_closed = FALSE
70 AND (mt.posted_date + (a.days_to_return * INTERVAL '1 DAY')) < NOW()
71 AND NOT EXISTS (SELECT * FROM member_transactions AS mt_inner WHERE mt_inner.parent_member_transaction_id = mt.member_transaction_id)
72 UNION ALL
73 SELECT mt2.member_transaction_id, mt2.site_id, mt2.member_id, mt2.affiliate_id, mt2.parent_member_transaction_id, mt2.transaction_amount, mt2.commission_amount, mt2.is_closed, mt2.aggregator_order_id, mt2.transaction_date, mt2.posted_date, mt2.order_id, mt2.transaction_type, mt2.created_date, mt2.commission_amount
74 FROM expired_transactions AS ex
75 JOIN member_transactions AS mt2 ON mt2.parent_member_transaction_id = ex.member_transaction_id
76 )
77 SELECT ex.member_id, ex.site_id, ex.affiliate_id, ex.member_transaction_id, 0.0 AS "transaction_amount", 0.0 AS "commission_amount", TRUE AS "is_closed", ex.aggregator_order_id, ex.transaction_date, ex.posted_date, ex.order_id, CASE WHEN SUM(ex.commission_amount) = 0 THEN 'void'::transaction_type ELSE 'closure'::transaction_type END, NOW() AS "created_date"
78 FROM expired_transactions AS ex
79 GROUP BY member_id, site_id, affiliate_id, member_transaction_id, transaction_amount, commission_amount, is_closed, aggregator_order_id, transaction_date, posted_date, order_id, created_date
80 LOOP
81 RAISE NOTICE 'Transaction: %', current_transaction;
82 -- Insert a new record into member_transactions,
83 -- Copy the latest member_transaction's member_transaction_line_items into member_transaction_line_items
84 END LOOP;
85 RETURN 0;
86 END;
87$closeExpiredLinkshareCommissions$
88LANGUAGE plpgsql;
89
90SELECT closeExpiredLinkshareCommissions();
91
92SELECT * FROM member_transactions;