Untitled

INSERT INTO reporting.bts_client_report (system_date, client_date, provider_date, org_sub, dst_sub, cli_area_code, cli_destination, pro_area_code, pro_destination, sale_rate, purchase_rate, internal_area_code, country_code, country_name, num_calls, num_fas_calls, num_fb_calls, org_seconds, dst_seconds, num_seconds, income, cost)

SELECT d.system_date,

       d.client_date,

       d.provider_date,

       d.org_subscription,

       d.dst_subscription,

       d.cli_area_code,

       d.cli_destination,

       d.pro_area_code,

       d.pro_destination,

       d.sale_rate,

       d.purchase_rate,

       d.area_code,

       COALESCE(ac.country_code, cc.country_code, npa.npa),

       COALESCE(acc.country_name, cc.country_name, npa.TYPE),

       d.num_calls,

       d.num_fas_calls,

       d.num_fb_calls,

       d.org_seconds,

       d.dst_seconds,

       d.num_seconds,

       d.income,

       d.cost

FROM (

SELECT start_time::DATE AS system_date,

       COALESCE((start_time AT TIME ZONE a.account_timezone)::DATE, start_time::DATE) AS client_date,

       COALESCE((start_time AT TIME ZONE b.account_timezone)::DATE, start_time::DATE) AS provider_date,

       org_subscription,

       dst_subscription,

       TRIM(SUBSTRING(org_params

                      FROM $1

                      FOR $2)) AS cli_area_code,

            COALESCE(org_destination, -1) AS cli_destination,

            TRIM(SUBSTRING(dst_params

                           FROM $3

                           FOR $4)) AS pro_area_code, COALESCE(dst_destination, -1) AS pro_destination, sale_rate, purchase_rate, area_code, COUNT(NULLIF(duration, 0)) AS num_calls, SUM(CASE WHEN duration > 0

                                                                                                                                                                                          AND duration < 15 THEN 1 ELSE 0 END) AS num_fas_calls, SUM(CASE WHEN duration = 0

                                                                                                                                                                                                                                                     AND release_cause = 34 THEN 1 ELSE 0 END) AS num_fb_calls, SUM(org_duration) AS org_seconds, SUM(dst_duration) AS dst_seconds, SUM(duration) AS num_seconds, SUM(sale_price) AS income, SUM(purchase_price) AS cost

                 FROM bts_cdr c, bts_subscription s, bts_account a, bts_subscription t, bts_account b

                 WHERE start_time >= $5::DATE

                   AND start_time < $6::DATE + INTERVAL '1 day'

                   AND billing_code_id % 2 = 1

                   AND billing_code_id & 2048 = 0

                   AND (call_direction = 1

                        OR org_subscription IN (2178, 2161, 130, 1105, 35))

                   AND (duration > 0

                        OR org_duration > 0

                        OR dst_duration > 0)

                   AND s.subscription_id = c.org_subscription

                   AND t.subscription_id = c.dst_subscription

                   AND a.account_id = s.account_id

                   AND b.account_id = t.account_id

                 GROUP BY 1, 2, 3, org_subscription, dst_subscription, 6, 7, 8, 9, sale_rate, purchase_rate, area_code) AS d

LEFT OUTER JOIN bts_area_code ac ON ((ac.cancelled IS NULL

                                      OR ac.cancelled = '10000-01-01')

                                     AND ac.effective <= d.system_date

                                     AND (ac.expired IS NULL

                                          OR ac.expired > d.system_date)

                                     AND ac.area_code = d.area_code)

LEFT OUTER JOIN bts_country_code cc ON (cc.country_code = d.area_code)

LEFT OUTER JOIN bts_country_code acc ON (acc.country_code = ac.country_code)

LEFT OUTER JOIN

  (SELECT '1' || npa AS npa, TYPE

   FROM bts_npa) npa ON (npa = d.area_code), bts_subscription c,

                                             bts_account a,

                                             bts_subscription p,

                                             bts_account b

WHERE c.subscription_id = d.org_subscription

  AND a.account_id = c.account_id

  AND p.subscription_id = d.dst_subscription

  AND b.account_id = p.account_id