| 1 | DROP VIEW IF EXISTS public.v_customer_roaming_activity;
|
|---|
| 2 |
|
|---|
| 3 | CREATE VIEW public.v_customer_roaming_activity AS
|
|---|
| 4 | SELECT c.customer_id,
|
|---|
| 5 | CASE WHEN c.customer_type = 'business' THEN c.company_name ELSE c.first_name || ' ' || c.last_name END AS customer_name,
|
|---|
| 6 | a.account_number, s.subscription_number, rp.country AS roaming_country,
|
|---|
| 7 | 'call'::text AS usage_type, cdr.call_cdr_id AS usage_id, cdr.event_start_time AS event_time,
|
|---|
| 8 | ROUND(cdr.duration_seconds::numeric / 60.0, 2) AS usage_quantity, 'minutes'::text AS usage_unit,
|
|---|
| 9 | cdr.direction, cdr.charge_amount
|
|---|
| 10 | FROM public.customers c
|
|---|
| 11 | JOIN public.accounts a ON a.customer_id = c.customer_id
|
|---|
| 12 | JOIN public.subscriptions s ON s.account_id = a.account_id
|
|---|
| 13 | JOIN public.usage_cdr_calls cdr ON cdr.subscription_id = s.subscription_id
|
|---|
| 14 | JOIN public.roaming_partners rp ON rp.roaming_partner_id = cdr.roaming_partner_id
|
|---|
| 15 | UNION ALL
|
|---|
| 16 | SELECT c.customer_id,
|
|---|
| 17 | CASE WHEN c.customer_type = 'business' THEN c.company_name ELSE c.first_name || ' ' || c.last_name END,
|
|---|
| 18 | a.account_number, s.subscription_number, rp.country,
|
|---|
| 19 | 'sms', sms.sms_cdr_id, sms.event_time, 1::numeric, 'message', sms.direction, sms.charge_amount
|
|---|
| 20 | FROM public.customers c
|
|---|
| 21 | JOIN public.accounts a ON a.customer_id = c.customer_id
|
|---|
| 22 | JOIN public.subscriptions s ON s.account_id = a.account_id
|
|---|
| 23 | JOIN public.usage_cdr_sms sms ON sms.subscription_id = s.subscription_id
|
|---|
| 24 | JOIN public.roaming_partners rp ON rp.roaming_partner_id = sms.roaming_partner_id
|
|---|
| 25 | UNION ALL
|
|---|
| 26 | SELECT c.customer_id,
|
|---|
| 27 | CASE WHEN c.customer_type = 'business' THEN c.company_name ELSE c.first_name || ' ' || c.last_name END,
|
|---|
| 28 | a.account_number, s.subscription_number, rp.country,
|
|---|
| 29 | 'data', d.data_cdr_id, d.session_start, d.data_used_mb, 'MB', NULL::text, d.charge_amount
|
|---|
| 30 | FROM public.customers c
|
|---|
| 31 | JOIN public.accounts a ON a.customer_id = c.customer_id
|
|---|
| 32 | JOIN public.subscriptions s ON s.account_id = a.account_id
|
|---|
| 33 | JOIN public.usage_cdr_data d ON d.subscription_id = s.subscription_id
|
|---|
| 34 | JOIN public.roaming_partners rp ON rp.roaming_partner_id = d.roaming_partner_id;
|
|---|
| 35 |
|
|---|
