c_voipcall_trig.sql

gnugk软交换产品

-- Trigger procedures for voipcall pre- and postprocessing
--
-- VoIP Billing Platform for GnuGk
-- Copyright (c) 2004, Michal Zygmuntowicz
--
-- This work is published under the GNU Public License (GPL)
-- see file COPYING for details

-- Normalize strings (convert special FreeRADIUS characters)
-- and find the account associated with the call
CREATE OR REPLACE FUNCTION voipcall_preprocess_fields()
	RETURNS TRIGGER AS
'
DECLARE
	userid INT;
BEGIN
	NEW.h323id := radius_xlat(NEW.h323id);
	NEW.gkid := radius_xlat(NEW.gkid);
	NEW.callingstationid := radius_xlat(NEW.callingstationid);
	NEW.calledstationid := radius_xlat(NEW.calledstationid);

	userid := match_user(NEW.h323id, NEW.callingstationip);
	IF userid IS NOT NULL THEN
		IF NEW.accountid IS NULL THEN
			SELECT INTO NEW.accountid, NEW.currencysym, NEW.h323id 
					A.id, A.currencysym, U.h323id
				FROM voipaccount A JOIN voipuser U ON A.id = U.accountid 
				WHERE U.id = userid;
		END IF;
	END IF;

	RETURN NEW;
END;
' LANGUAGE 'plpgsql';

-- Find tariff information for the call
CREATE OR REPLACE FUNCTION voipcall_match_tariff()
	RETURNS TRIGGER AS
'
DECLARE
	userid INT;
	trf voiptariff%ROWTYPE;
	trfdst voiptariffdst%ROWTYPE;
	termaccount voipaccount%ROWTYPE;
	termuser voipuser%ROWTYPE;
BEGIN
	IF NEW.price IS NOT NULL AND NEW.cost IS NOT NULL THEN
		RETURN NEW;
	END IF;

	IF NEW.accountid IS NULL THEN
		userid := match_user(NEW.h323id, NEW.callingstationip);
		IF userid IS NOT NULL THEN
			SELECT INTO NEW.accountid, NEW.currencysym, NEW.h323id
					A.id, A.currencysym, U.h323id
				FROM voipuser U JOIN voipaccount A ON U.accountid = A.id
				WHERE U.id = userid;
		END IF;
	END IF;

	trf.id := NULL;
	SELECT INTO trf * FROM match_tariff(NEW.calledstationid, NEW.accountid, NEW.currencysym);
	IF trf.id IS NOT NULL THEN
		SELECT INTO trfdst * FROM voiptariffdst WHERE id = trf.dstid;
	END IF;
	IF NEW.price IS NULL AND NEW.accountid IS NOT NULL AND trf.id IS NOT NULL THEN
		SELECT INTO NEW.price, NEW.tariffdesc, NEW.initialincrement,
				NEW.regularincrement, NEW.graceperiod, NEW.prefix
				trf.price, trfdst.description, trf.initialincrement, trf.regularincrement,
				trf.graceperiod, trfdst.prefix;
	END IF;

	IF trf.id IS NULL OR trfdst.id IS NULL THEN
		RETURN NEW;
	END IF;

	-- check if there exists a terminating tariff
	
	termuser.id := NULL;	
	SELECT INTO termuser * FROM match_terminating_user(NEW.calledstationid, trfdst.exactmatch, NEW.calledstationip);
	IF termuser.id IS NULL THEN
		RETURN NEW;
	END IF;

	SELECT INTO termaccount * FROM voipaccount A JOIN voipuser U ON A.id = U.accountid
		WHERE U.id = termuser.id;

	trf.id := NULL;	
	SELECT INTO trf * FROM match_terminating_tariff(trfdst.id, termaccount.id, termaccount.currencysym);
	IF trf.id IS NOT NULL THEN
		INSERT INTO voipcalltermtariff (callid, accountid, h323id, terminatingip,
				cost, price, currencysym, initialincrement, regularincrement, graceperiod, tariffdesc)
			VALUES (NEW.id, termaccount.id, termuser.h323id, NEW.calledstationip,
				0, trf.price, trf.currencysym, trf.initialincrement, trf.regularincrement,
				trf.graceperiod, trf.description);
	END IF;
	RETURN NEW;
END;
' LANGUAGE 'plpgsql';

-- Calculate total call cost (duration*price) and substract
-- difference from the associated account balance
CREATE OR REPLACE FUNCTION voipcall_update_cost()
	RETURNS TRIGGER AS
'
DECLARE
	costdiff NUMERIC(12,4);
	oldcost NUMERIC(12,4);
	termtariff voipcalltermtariff%ROWTYPE;
BEGIN
	IF NEW.price IS NOT NULL THEN
		IF NEW.duration <= NEW.graceperiod OR NEW.duration = 0 THEN
			NEW.cost := 0;
		ELSE
			NEW.cost := NEW.price::NUMERIC(12,4) * NEW.initialincrement::NUMERIC(12,4)
				/ 60::NUMERIC(12,4);
			IF NEW.duration > NEW.initialincrement THEN
				NEW.cost := NEW.cost + NEW.price::NUMERIC(12,4) 
					* ((NEW.duration - NEW.initialincrement + NEW.regularincrement - 1)::INT 
						/ NEW.regularincrement::INT)::NUMERIC(12,4) 
					* NEW.regularincrement::NUMERIC(12,4) / 60::NUMERIC(12,4);
			END IF;
		END IF;
		IF NEW.accountid IS NOT NULL THEN
			IF TG_OP = ''UPDATE'' THEN
				IF OLD.cost IS NULL THEN
					costdiff := NEW.cost;
				ELSE
					costdiff := NEW.cost - OLD.cost;
				END IF;
			ELSE
				costdiff := NEW.cost;
			END IF;
			UPDATE voipaccount SET balance = balance - costdiff
				WHERE id = NEW.accountid;
		END IF;
	END IF;
	SELECT INTO termtariff * FROM voipcalltermtariff WHERE callid = NEW.id;
	IF termtariff.id IS NOT NULL THEN
		IF termtariff.price IS NOT NULL THEN
			oldcost := termtariff.cost;
			IF NEW.duration <= termtariff.graceperiod OR NEW.duration = 0 THEN
				termtariff.cost := 0;
			ELSE
				termtariff.cost := termtariff.price::NUMERIC(12,4) 
					* termtariff.initialincrement::NUMERIC(12,4) / 60::NUMERIC(12,4);
				IF NEW.duration > termtariff.initialincrement THEN
					termtariff.cost := termtariff.cost + termtariff.price::NUMERIC(12,4) 
						* ((NEW.duration - termtariff.initialincrement + termtariff.regularincrement - 1)::INT 
							/ termtariff.regularincrement::INT)::NUMERIC(12,4) 
						* termtariff.regularincrement::NUMERIC(12,4) / 60::NUMERIC(12,4);
				END IF;
			END IF;
			UPDATE voipcalltermtariff SET cost = termtariff.cost WHERE id = termtariff.id;
			IF termtariff.accountid IS NOT NULL THEN
				IF oldcost IS NULL THEN
					costdiff := termtariff.cost;
				ELSE
					costdiff := termtariff.cost - oldcost;
				END IF;
				UPDATE voipaccount SET balance = balance + costdiff
					WHERE id = termtariff.accountid;
			END IF;
		END IF;
	END IF;
	RETURN NEW;
END;
' LANGUAGE 'plpgsql';


CREATE TRIGGER voipcall_biu_trig1 BEFORE INSERT OR UPDATE ON voipcall
	FOR EACH ROW EXECUTE PROCEDURE voipcall_preprocess_fields();
CREATE TRIGGER voipcall_biu_trig2 BEFORE INSERT OR UPDATE ON voipcall
	FOR EACH ROW EXECUTE PROCEDURE voipcall_match_tariff();
CREATE TRIGGER voipcall_biu_trig3 BEFORE INSERT OR UPDATE ON voipcall
	FOR EACH ROW EXECUTE PROCEDURE voipcall_update_cost();