f_pricing.sql

gnugk软交换产品

\echo Reading destinations from file destinations.csv

CREATE TEMPORARY TABLE voiptariffdst_temp (
  active TEXT NOT NULL,
  destination TEXT NOT NULL,
  carrierpfx TEXT NOT NULL,
  dialpfx TEXT NOT NULL,
  finalpfx TEXT NOT NULL,
  exactmatch TEXT NOT NULL
);

\copy voiptariffdst_temp from 'destinations.csv' with delimiter '\t'

\echo Preprocessing destinations

UPDATE voiptariffdst_temp SET finalpfx = trim(finalpfx, '\'\r\n\t '),
	active = trim(active, '\'\r\n\t '), destination = trim(destination, '\'\r\n\t '),
	exactmatch = trim(exactmatch, '\'\r\n\t ');

DELETE FROM voiptariffdst_temp WHERE active NOT IN ('T', 'F');

\echo Reading pricing information from file pricing.csv

CREATE TEMPORARY TABLE voiptariff_temp (
  destination TEXT NOT NULL,
  grp TEXT,
  netprice TEXT NOT NULL,
  vat TEXT NOT NULL,
  price TEXT NOT NULL,
  currency TEXT NOT NULL,
  initialincrement TEXT NOT NULL,
  regularincrement TEXT NOT NULL,
  graceperiod TEXT NOT NULL,
  terminating TEXT NOT NULL
);

\copy voiptariff_temp from 'pricing.csv' with delimiter '\t'

\echo Preprocessing pricing information

UPDATE voiptariff_temp SET destination = trim(destination, '\'\r\n\t '),
	grp = trim(grp, '\'\r\n\t '), price = trim(price, '\'\r\n\t '),
	currency = trim(currency, '\'\r\n\t '), initialincrement = trim(initialincrement, '\'\r\n\t '),
	regularincrement = trim(regularincrement, '\'\r\n\t '),
	graceperiod = trim(graceperiod, '\'\r\n\t '), terminating = trim(terminating, '\'\r\n\t ');

UPDATE voiptariff_temp SET price = replace(price, ',', '.');

DELETE FROM voiptariff_temp WHERE NOT initialincrement::TEXT ~* '[0-9]+';

CREATE TEMPORARY TABLE voiptarifffull_temp
(
  active TEXT NOT NULL,
  destination TEXT NOT NULL,
  prefix TEXT NOT NULL,
  exactmatch TEXT NOT NULL,
  grp TEXT,
  price TEXT NOT NULL,
  currency TEXT NOT NULL,
  initialincrement TEXT NOT NULL,
  regularincrement TEXT NOT NULL,
  graceperiod TEXT NOT NULL,
  terminating TEXT NOT NULL
);

INSERT INTO voiptarifffull_temp
	SELECT D.active, D.destination, D.finalpfx, D.exactmatch, T.grp, T.price, 
		T.currency,	T.initialincrement, T.regularincrement, T.graceperiod, T.terminating
	FROM voiptariffdst_temp D JOIN voiptariff_temp T ON D.destination = T.destination;

CREATE OR REPLACE FUNCTION update_tariffs(voiptarifffull_temp)
	RETURNS INT AS
'
DECLARE
	update_query TEXT;
	modified BOOLEAN := FALSE;
	execute_query BOOLEAN;
	dst RECORD;
	trf voiptariff%ROWTYPE;
	grp voiptariffgrp%ROWTYPE;
BEGIN
	-- find a destination associated with this tariff
	SELECT INTO dst * FROM voiptariffdst WHERE prefix = $1.prefix;
	IF NOT FOUND OR dst.id IS NULL THEN
		-- destination not found, create a new one
		INSERT INTO voiptariffdst (active, prefix, description, exactmatch)
			VALUES (get_bool($1.active), $1.prefix, $1.destination,
				get_bool($1.exactmatch));
		SELECT INTO dst * FROM voiptariffdst WHERE prefix = $1.prefix;
		IF NOT FOUND OR dst.id IS NULL THEN
			RAISE WARNING ''update_tariff: Could not insert a new destination: % %'', $1.prefix, $1.destination;
			RETURN -1;
		END IF;
		modified := TRUE;
	ELSE
		-- destination exists, update information if neccessary
		update_query := ''UPDATE voiptariffdst SET'';
		execute_query := FALSE;
		IF dst.description <> $1.destination THEN
			IF execute_query THEN
				update_query := update_query || '','';
			END IF;
			update_query := update_query || '' description = '' || quote_literal($1.destination);
			execute_query := TRUE;
		END IF;
		IF dst.active <> get_bool($1.active) THEN
			IF execute_query THEN
				update_query := update_query || '','';
			END IF;
			update_query := update_query || '' active = '' || $1.active;
			execute_query := TRUE;
		END IF;
		IF dst.exactmatch <> get_bool($1.exactmatch) THEN
			IF execute_query THEN
				update_query := update_query || '','';
			END IF;
			update_query := update_query || '' exactmatch = '' || $1.exactmatch;
			execute_query := TRUE;
		END IF;
		update_query := update_query || '' WHERE id = '' || CAST(dst.id AS TEXT);
		
		-- execute the query if at least one field needs to be updated
		IF execute_query THEN
			EXECUTE update_query;
			IF NOT FOUND THEN
				RAISE WARNING ''error:update_tariff: Failed to update destination info for prefix %'', $1.prefix;
				RETURN -1;
			END IF;
			modified := TRUE;
		END IF;
	END IF;

	-- try to find a tariff for the given destination
	IF length($1.grp) > 0 THEN
		-- this tariff belongs to some group
		SELECT INTO trf * FROM voiptariff T JOIN voiptariffgrp G
			ON T.grpid = G.id
			WHERE T.terminating = get_bool($1.terminating) 
				AND T.dstid = dst.id AND T.currencysym = $1.currency 
				AND G.description = $1.grp;
	ELSE
		-- this is a default tariff
		SELECT INTO trf * FROM voiptariff 
			WHERE terminating = get_bool($1.terminating) 
				AND dstid = dst.id AND currencysym = $1.currency AND grpid IS NULL;
	END IF;
	IF NOT FOUND OR trf.id IS NULL THEN
		-- create a new entry for this tariff
		IF length($1.grp) > 0 THEN
			-- create an entry for the tariff group, if neccessary
			SELECT INTO grp * FROM voiptariffgrp WHERE description = $1.grp;
			IF NOT FOUND OR grp.id IS NULL THEN
				INSERT INTO voiptariffgrp (description) VALUES ($1.grp);
				SELECT INTO grp * FROM voiptariffgrp WHERE id = currval(''voiptariffgrp_id_seq'');
			END IF;
			INSERT INTO voiptariff (dstid, grpid, price, currencysym, 
					initialincrement, regularincrement, graceperiod, terminating)
				VALUES (dst.id, grp.id, CAST($1.price AS NUMERIC), $1.currency, 
					$1.initialincrement::INT, $1.regularincrement::INT, 
					$1.graceperiod::INT, get_bool($1.terminating));
		ELSE
			-- create a default tariff
			INSERT INTO voiptariff (dstid, price, currencysym, initialincrement, 
					regularincrement, graceperiod, terminating)
				VALUES (dst.id, CAST($1.price AS NUMERIC), $1.currency, 
					$1.initialincrement::INT, $1.regularincrement::INT, 
					$1.graceperiod::INT, get_bool($1.terminating));
		END IF;
		IF NOT FOUND THEN
			RAISE WARNING ''update_tariff: Failed to insert a new tariff for the prefix %'',
				dst.prefix;
			RETURN -1;
		END IF;
		modified := TRUE;
	ELSE
		-- the tariff already exists, check if it needs to be updated
		update_query := ''UPDATE voiptariff SET'';
		execute_query := FALSE;
		IF trf.price <> $1.price::NUMERIC(12,4) THEN
			IF execute_query THEN
				update_query := update_query || '','';
			END IF;
			update_query := update_query || '' price = '' || $1.price;
			execute_query := TRUE;
		END IF;
		IF trf.currencysym <> $1.currency THEN
			IF execute_query THEN
				update_query := update_query || '','';
			END IF;
			update_query := update_query || '' currencysym = '' || quote_literal($1.currency);
			execute_query := TRUE;
		END IF;
		IF trf.initialincrement <> $1.initialincrement THEN
			IF execute_query THEN
				update_query := update_query || '','';
			END IF;
			update_query := update_query || '' initialincrement = '' || CAST($1.initialincrement AS TEXT);
			execute_query := TRUE;
		END IF;
		IF trf.regularincrement <> $1.regularincrement THEN
			IF execute_query THEN
				update_query := update_query || '','';
			END IF;
			update_query := update_query || '' regularincrement = '' || CAST($1.regularincrement AS TEXT);
			execute_query := TRUE;
		END IF;
		IF trf.graceperiod <> $1.graceperiod THEN
			IF execute_query THEN
				update_query := update_query || '','';
			END IF;
			update_query := update_query || '' graceperiod = '' || CAST($1.graceperiod AS TEXT);
			execute_query := TRUE;
		END IF;
		IF trf.terminating <> get_bool($1.terminating) THEN
			IF execute_query THEN
				update_query := update_query || '','';
			END IF;
			update_query := update_query || '' terminating = '' || $1.terminating;
			execute_query := TRUE;
		END IF;
		update_query := update_query || '' WHERE id = '' || CAST(trf.id AS TEXT);
		IF execute_query THEN
			EXECUTE update_query;
			IF NOT FOUND THEN
				RAISE WARNING ''update_tariff: Failed to update a tariff with id %'',trf.id;
				RETURN -1;
			END IF;
			modified := TRUE;
		END IF;
	END IF;
	
	IF modified THEN
		RETURN 1;
	ELSE
		RETURN 0;
	END IF;
END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION remove_unused_tariffs()
	RETURNS INT AS
'
DECLARE
	trfcursor CURSOR FOR SELECT T.id AS tid, T.currencysym AS tcurrsym, 
		D.prefix AS tprefix, T.grpid AS grpid, T.terminating AS terminating
		FROM voiptariff T JOIN voiptariffdst D ON T.dstid = D.id;
	trf RECORD;
	tfound BOOLEAN;
	numremoved INT := 0;
	grpname TEXT;
BEGIN
	OPEN trfcursor;
	LOOP
		FETCH trfcursor INTO trf;
		EXIT WHEN NOT FOUND;
		IF trf.grpid IS NOT NULL THEN
			SELECT INTO grpname description FROM voiptariffgrp WHERE id = trf.grpid;
		ELSE
			grpname := '''';
		END IF;
		tfound := NULL;
		SELECT INTO tfound TRUE FROM voiptarifffull_temp 
			WHERE prefix = trf.tprefix AND currency = trf.tcurrsym
				AND grp = grpname AND trf.terminating = get_bool(terminating);
		IF NOT FOUND OR tfound IS NULL THEN
			RAISE INFO ''Removing tariff for %, %, %'', trf.tprefix, trf.tcurrsym, grpname;
			DELETE FROM voiptariff WHERE id = trf.tid;
			IF FOUND THEN
				numremoved := numremoved + 1;
			END IF;
		END IF;
	END LOOP;
	CLOSE trfcursor;
	RETURN numremoved;
END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION remove_unused_destinations() RETURNS INT AS
'
DECLARE
	dstcursor CURSOR FOR SELECT id, prefix, description FROM voiptariffdst;
	dst RECORD;
	dstfound BOOLEAN;
	numremoved INT := 0;
BEGIN
	OPEN dstcursor;
	LOOP
		FETCH dstcursor INTO dst;
		EXIT WHEN NOT FOUND;
		dstfound := NULL;
		SELECT INTO dstfound TRUE FROM voiptarifffull_temp WHERE prefix = dst.prefix;
		IF NOT FOUND OR dstfound IS NULL THEN
			RAISE INFO ''Removing destination % - %'', dst.description, dst.prefix;
			DELETE FROM voiptariffdst WHERE id = dst.id;
			IF FOUND THEN
				numremoved := numremoved + 1;
			END IF;
		END IF;
	END LOOP;
	CLOSE dstcursor;
	RETURN numremoved;
END;
' LANGUAGE 'plpgsql';

\echo Updating tariffs

SELECT SUM(update_tariffs(voiptarifffull_temp)) AS tariffs_updated FROM voiptarifffull_temp;
SELECT remove_unused_tariffs() AS tariffs_removed;
SELECT remove_unused_destinations() AS destinations_removed;

\echo Cleanup

DROP FUNCTION remove_unused_tariffs();
DROP FUNCTION remove_unused_destinations();
DROP FUNCTION update_tariffs(voiptarifffull_temp);
DROP TABLE voiptariffdst_temp;
DROP TABLE voiptariff_temp;
DROP TABLE voiptarifffull_temp;