-- 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;