~singpolyma/jmp-schemas

jmp-schemas/verify/insert_charge_for_cdr.sql -rw-r--r-- 1.9 KiB
395bb797Stephen Paul Weber Tag for notify on top up an expired account a month 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
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
-- Verify jmp:insert_charge_for_cdr on pg

BEGIN;

INSERT INTO cdr (cdr_id, customer_id, start, billsec, disposition, tel)
	VALUES(
		'test1',
		'test',
		LOCALTIMESTAMP - MAKE_INTERVAL(months := 2),
		10000,
		'ANSWERED',
		'+15551234567'
);

INSERT INTO plans VALUES ('{
	"name": "test",
	"minutes": { "included": 10440 }
}');

INSERT INTO plan_log (customer_id, plan_name, date_range)
	VALUES('test', 'test', TSRANGE(LOCALTIMESTAMP, LOCALTIMESTAMP + '5 minutes'));

INSERT INTO cdr (cdr_id, customer_id, start, billsec, disposition, tel)
	VALUES(
		'test2',
		'test',
		LOCALTIMESTAMP - MAKE_INTERVAL(months := 1),
		10000,
		'ANSWERED',
		'+15551234567'
);

INSERT INTO call_rates (prefix, direction, plan_name, rate)
	VALUES('+1', 'outbound', 'test', 0.0087);

INSERT INTO cdr (cdr_id, customer_id, start, billsec, disposition, tel) VALUES(
	'test3',
	'test',
	LOCALTIMESTAMP - MAKE_INTERVAL(secs := 20),
	3600,
	'ANSWERED',
	'+15551234567'
);

INSERT INTO cdr (cdr_id, customer_id, start, billsec, disposition, tel) VALUES(
	'test4',
	'test',
	LOCALTIMESTAMP - MAKE_INTERVAL(secs := 10),
	3660,
	'ANSWERED',
	'+15551234567'
);

INSERT INTO cdr (cdr_id, customer_id, start, billsec, disposition, tel)
	VALUES('test5', 'test', LOCALTIMESTAMP, 60000, 'ANSWERED', '+15551234567');

DO $$
DECLARE
	c integer;
	a numeric;
BEGIN
	SELECT COUNT(1) FROM transactions WHERE
	customer_id='test' AND transaction_id IN (
		'cdr_charge_for_test1',
		'cdr_charge_for_test2',
		'cdr_charge_for_test3',
		'cdr_charge_for_test4'
	) INTO c;

	IF c > 0 THEN
		RAISE EXCEPTION 'One of test[1234] present';
	END IF;

	SELECT COUNT(1) FROM transactions WHERE customer_id='test' INTO c;
	IF c <> 1 THEN
		RAISE EXCEPTION 'Expected 2 transactions, got %', c;
	END IF;

	SELECT amount FROM transactions WHERE
	customer_id='test' AND transaction_id='cdr_charge_for_test5' INTO a;
	IF a <> -8.7 THEN
		RAISE EXCEPTION 'Expected -8.7, got %', a;
	END IF;
END $$;

ROLLBACK;