f_merged.sql

gnugk软交换产品

\echo Reading destinations/pricing from file merged.csv

CREATE TEMPORARY TABLE voiptarifffull_temp
(
  active TEXT NOT NULL,
  destination TEXT NOT NULL,
  carrierprefix TEXT NOT NULL,
  countryprefix TEXT NOT NULL,
  prefix TEXT NOT NULL,
  exactmatch 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 voiptarifffull_temp from 'merged.csv' with delimiter '\t'

\echo Preprocessing tariffs

UPDATE voiptarifffull_temp SET prefix = trim(prefix, '\'\r\n\t '),
	active = trim(active, '\'\r\n\t '), destination = trim(destination, '\'\r\n\t '),
	exactmatch = trim(exactmatch, '\'\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 ');

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

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

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
	SELECT INTO dst * FROM voiptariffdst WHERE prefix = $1.prefix;
	IF NOT FOUND OR dst.id IS NULL THEN
		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
		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 = '' || CAST($1.exactmatch AS TEXT);
			execute_query := TRUE;
		END IF;
		update_query := update_query || '' WHERE id = '' || CAST(dst.id AS TEXT);
		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;

	IF length($1.grp) > 0 THEN
		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
		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
		IF length($1.grp) > 0 THEN
			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
			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
		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 = '' || CAST($1.terminating AS TEXT);
			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 voiptarifffull_temp;