~singpolyma/jmp-schemas

ref: 240b3444466fb458b517d84c83bd6a782a21934e jmp-schemas/deploy/cdr_charge.sql -rw-r--r-- 1.4 KiB
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
40
41
42
43
44
45
46
47
48
49
50
51
52
-- Deploy jmp:cdr_charge to pg
-- requires: customers
-- requires: customer_plans
-- requires: plans
-- requires: cdr_with_charge

BEGIN;

CREATE FUNCTION cdr_charge(
	cdr_start TIMESTAMP,
	cdr_customer_id TEXT,
	cdr_rate NUMERIC,
	cdr_charge NUMERIC -- Basic rate*ceil(billsec/60)
) RETURNS NUMERIC AS $$
WITH
	customer_limit AS (
		SELECT COALESCE(MAX((data->>'monthly_overage_limit')::numeric), 0) AS customer_limit
		FROM customers
		WHERE customer_id=cdr_customer_id
	),
	included_charge AS (
		SELECT ((plan->'minutes'->'included')::numeric / 10000.0) AS included_charge
		FROM plans INNER JOIN customer_plans ON plan_name=plan->>'name'
		WHERE customer_id=cdr_customer_id
	),
	used AS (
		SELECT COALESCE(SUM(charge), 0) AS used
		FROM cdr_with_charge
		WHERE
			customer_id=cdr_customer_id AND
			start >= DATE_TRUNC('month', cdr_start) AND
			start < cdr_start
	)

SELECT
	CASE
		WHEN cdr_charge IS NULL OR included_charge IS NULL THEN
			0
		WHEN used + cdr_charge < included_charge + customer_limit THEN
			GREATEST(cdr_charge - GREATEST(included_charge - used, 0), 0)
		WHEN included_charge + customer_limit - (cdr_rate * 10) < used THEN
			GREATEST(cdr_charge - GREATEST(included_charge - used, 0), 0)
		ELSE
			LEAST(customer_limit + included_charge - used, customer_limit)
	END
FROM
	customer_limit
	FULL JOIN included_charge ON true
	FULL JOIN used ON true;
$$ LANGUAGE sql;

COMMIT;