-- Function: sp_processcdr()

-- DROP FUNCTION sp_processcdr();

CREATE OR REPLACE FUNCTION sp_processcdr()
  RETURNS int8 AS
$BODY$
DECLARE
	result bigint;
	billCostDivider float8;
BEGIN
	-- cost divider to convert whole integer costs to actual values.
	billCostDivider := 100000;

	-- Create temporary table
	CREATE TEMP TABLE tmp_Cust_CDR (
		cdrid 		int8,
		account 	varchar(75),
		src 		varchar(75),
		dst 		varchar(75),
		startdate 	timestamp,
		isocode 	varchar(5),
		region 		varchar(20),
		minimum 	int8,
		"increment" 	int8,
		minimumcost 	int8,
		incrementcost 	int8,
		billsec 	int8,
		BilledSeconds 	int8,
		BilledCost 	float8,
		a 		int2);

	-- Get all the new cdrs
	INSERT INTO tmp_Cust_CDR (cdrid, account, src, dst, startdate, billsec, a)
		SELECT cdrid, account, src, dst, startdate, billsec, 0
		FROM CDR_info 
		WHERE len(dst) > 5
			AND cdrid NOT IN (SELECT cdrid FROM customer_cdr);
			
	-- Local numbers
	UPDATE tmp_Cust_CDR SET isocode = '1', dst = '705' || dst, region = 'LOCAL', a = 1 WHERE len(dst) < 10;

	-- For the unlikely scenario where there's still 11 digit numbers ie: 1800whatever
	UPDATE tmp_Cust_CDR SET isocode = '1', dst = right(dst, len(dst) - 1), region = 'NORTHAMERICA', a = 11 WHERE a = 0 AND len(dst) = 11 AND left(dst,1) = 1;

	-- Any remaining 10 digit dsts would be North American
	UPDATE tmp_Cust_CDR SET isocode = 1, region = 'NORTHAMERICA', a = 11 WHERE a = 0 AND len(dst) = 10;

	-- International only - Get the iso code from remaining CDRs (a = 0) that match an International ISO Code from rate_info
	UPDATE tmp_Cust_CDR SET
		dst = inat.i_dst,
		isocode = inat.i_isocode,
		a = 2
	FROM (	SELECT 	DISTINCT cdrid AS i_cdrid, 
			right(cdr.dst, len(cdr.dst) - len(ri.isocode)) AS i_dst,
			ri.isocode AS i_isocode
		FROM tmp_Cust_CDR AS cdr
			LEFT OUTER JOIN rate_info AS rA ON rA.isocode <> 1
			LEFT OUTER JOIN rate_info AS ri ON left(cdr.dst, len(rA.isocode)) = ri.isocode
		WHERE cdr.a=0
		AND len(cdr.dst) > 10
		AND isnull(cdr.isocode,'') = '' ) AS inat
	WHERE cdrid = inat.i_cdrid;

	-- Get the regions
	UPDATE tmp_Cust_CDR SET
		region = isnull(rgn.region, 'NOREGION')
	FROM (	SELECT 	DISTINCT cdrid AS l_cdrid,
				ri.region,
				len(ri.region),
				cdr.dst
		FROM tmp_Cust_CDR AS cdr
		LEFT OUTER JOIN rate_info AS ri ON (ri.isocode = cdr.isocode AND left(cdr.dst, len(ri.region)) = ri.region)
		ORDER BY cdrid, len(ri.region) DESC
	) AS rgn
	WHERE cdrid = rgn.l_cdrid;

	-- If the region isn't in rate_info then it must default to the empty region for the isocode
	UPDATE tmp_Cust_CDR SET
		region = 'NOREGION'
	WHERE region NOT IN (SELECT region FROM rate_info);

	-- Get the rate info where the parsed out isocode and regions match exactly in rate_info
	UPDATE tmp_Cust_CDR SET
		minimum = T.i_min,
		"increment" = T.i_inc,
		minimumcost = T.i_mincost,
		incrementcost = T.i_inccost
	FROM (	SELECT t.cdrid AS i_cdrid, ri.minimum AS i_min, ri.increment AS i_inc, ri.minimumcost AS i_mincost, ri.incrementcost AS i_inccost
		FROM tmp_Cust_CDR AS t
			LEFT OUTER JOIN ( SELECT t.cdrid, min(ri.incrementcost) AS mincost
				FROM tmp_Cust_CDR AS t
					LEFT OUTER JOIN rate_info AS ri ON (ri.isocode = t.isocode AND isnull(ri.region, 'NOREGION') = t.region)
				group by t.cdrid
				) AS dbl ON dbl.cdrid = t.cdrid
			LEFT OUTER JOIN rate_info AS ri ON (ri.isocode = t.isocode AND isnull(ri.region, 'NOREGION') = t.region)
		WHERE isnull(ri.incrementcost, -99::bigint) = CASE 	WHEN isnull(dbl.mincost, -99::bigint) = -99 THEN ri.incrementcost
									WHEN isnull(ri.incrementcost, -99::bigint) = -99 THEN -99
									ELSE dbl.mincost END 
		) AS T
	WHERE cdrid = T.i_cdrid;
	
	-- calculate the actual billable seconds and the billed cost
	UPDATE tmp_Cust_CDR SET
		BilledSeconds = CASE	WHEN billsec - minimum <= 0 THEN
					minimum
				ELSE
					CASE WHEN (billsec - minimum) % "increment" = 0 THEN
						billsec
					ELSE
						minimum + ( ( ( (billsec - minimum) / "increment" ) + 1 ) * "increment" )
					END
				END,
		BilledCost = 	(CASE WHEN len(dst) >= 10 THEN
					CASE WHEN billsec - minimum <= 0 THEN
						minimumCost
					ELSE
						CASE WHEN (billsec - minimum) % "increment" = 0 THEN
							minimumCost + ( ( (billsec - minimum) / "increment" ) * incrementcost )
						ELSE
							minimumCost + ( ( ( (billsec - minimum) / "increment" ) + 1 ) * incrementcost)
						END
					END
				ELSE
					0
				END)::float8 / billCostDivider
	WHERE "increment" > 0;

	UPDATE tmp_Cust_CDR SET BilledSeconds = BillSec, BilledCost = 0 WHERE "increment" = 0;

	-- Add to customer_cdr table
	INSERT INTO customer_cdr (cdrid, account, startdate, src, dst, billsec, callcost)
		SELECT cdrid, account, startdate, src, dst, BilledSeconds, BilledCost
		FROM tmp_Cust_CDR
		ORDER BY cdrid;

	-- Get the number of records inserted
	result := count(cdrid) FROM tmp_Cust_CDR;

	return result;

END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION sp_processcdr() OWNER TO voctel;