~singpolyma/jmp-schemas

ref: 240b3444466fb458b517d84c83bd6a782a21934e jmp-schemas/deploy/insert_charge_for_cdr.sql -rw-r--r-- 1015 bytes
240b3444Stephen Paul Weber NOTIFY on low balance 7 months ago
                                                                                
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
-- Deploy jmp:insert_charge_for_cdr to pg
-- requires: cdr_charge
-- requires: cdr_with_charge
-- requires: transactions

BEGIN;

CREATE OR REPLACE FUNCTION insert_charge_for_cdr() RETURNS TRIGGER AS
$insert_charge_for_cdr$
	DECLARE
		raw_rate NUMERIC;
		raw_charge NUMERIC;
		final_charge NUMERIC;
	BEGIN
		SELECT
			rate, charge INTO raw_rate, raw_charge
		FROM cdr_with_charge WHERE cdr_id=NEW.cdr_id;
		final_charge := cdr_charge(NEW.start, NEW.customer_id, raw_rate, raw_charge);
		IF final_charge > 0.0 THEN
			INSERT INTO transactions (
				customer_id, transaction_id, created_at, amount, note
			) VALUES (
				NEW.customer_id,
				'cdr_charge_for_' || NEW.cdr_id,
				NEW.start + MAKE_INTERVAL(secs := NEW.billsec),
				-final_charge,
				'Charge for ' || (CEIL(NEW.billsec / 60.0)) || ' minute call'
			);
		END IF;
		RETURN NEW;
	END;
$insert_charge_for_cdr$ LANGUAGE plpgsql;

CREATE TRIGGER insert_charge_for_cdr
	AFTER INSERT ON cdr
	FOR EACH ROW
	EXECUTE PROCEDURE insert_charge_for_cdr();

COMMIT;