CREATE OR REPLACE FUNCTION updatebalance()
RETURNS void AS
$BODY$
DECLARE
rec1 RECORD;
rec2 RECORD;
rec3 RECORD;
rec4 RECORD;
rec5 RECORD;
rec6 RECORD;
rec7 RECORD;
rec8 RECORD;
rec9 RECORD;
BEGIN
FOR rec1 IN (SELECT n.vnumber, t.vname, noutfavoritecost, noutsystemcost, noutothercost, noutcityphonecost, noutinternationalcost, nsmscost, nmmscost, nwapcost FROM t_numbers n   -- t_numbers + t_tariff 
FULL JOIN t_contract c 
FULL JOIN t_tariff t ON c.ntariffid=t.id
ON c.id=n.ncontractid
GROUP BY n.vnumber, t.noutfavoritecost, t.vname, noutsystemcost, noutothercost, noutcityphonecost, noutinternationalcost, nsmscost, nmmscost, nwapcost)
LOOP
FOR rec2 IN (SELECT c.nnumberid, n.vnumber, t.vname, sum(c.vvalue::numeric) AS summary FROM t_numbers n   -- t_numbers + t_units
FULL JOIN t_units c 
FULL JOIN t_unittype t ON c.ntypeid=t.id
ON c.nnumberid=n.id
WHERE vinout='OUTGOING' AND t.vname = 'WAP'
GROUP BY n.vnumber, t.vname, c.nnumberid)
LOOP
FOR rec3 IN (SELECT c.nnumberid, n.vnumber, t.vname, sum(c.nvalue) AS summary FROM t_numbers n   -- t_numbers + t_cashactions 
FULL JOIN t_cashactions c 
FULL JOIN t_cashactiontype t ON c.ncashactiontypeid=t.id
ON c.nnumberid=n.id
GROUP BY n.vnumber, t.vname, c.nnumberid)
LOOP
FOR rec4 IN (SELECT c.nnumberid, n.vnumber, t.vname, sum(c.vvalue::numeric) AS summary FROM t_numbers n   -- t_numbers + t_units
FULL JOIN t_units c 
FULL JOIN t_unittype t ON c.ntypeid=t.id
ON c.nnumberid=n.id
WHERE vinout='OUTGOING' AND t.vname = 'SMS'
GROUP BY n.vnumber, t.vname, c.nnumberid)
LOOP
FOR rec5 IN (SELECT c.nnumberid, n.vnumber, t.vname, sum(c.vvalue::numeric) AS summary FROM t_numbers n   -- t_numbers + t_units
FULL JOIN t_units c 
FULL JOIN t_unittype t ON c.ntypeid=t.id
ON c.nnumberid=n.id
WHERE vinout='OUTGOING' AND t.vname = 'MMS'
GROUP BY n.vnumber, t.vname, c.nnumberid)
LOOP
FOR rec6 IN (SELECT c.nnumberid, n.vnumber, t.vname, sum(c.vvalue::numeric) AS summary FROM t_numbers n   -- t_numbers + t_units
FULL JOIN t_units c 
FULL JOIN t_unittype t ON c.ntypeid=t.id
ON c.nnumberid=n.id
WHERE vinout='OUTGOING' AND t.vname = 'вызов' AND visinternalnet='Y'
GROUP BY n.vnumber, t.vname, c.nnumberid)
LOOP
FOR rec7 IN (SELECT c.nnumberid, n.vnumber, t.vname, sum(c.vvalue::numeric) AS summary FROM t_numbers n   -- t_numbers + t_units
FULL JOIN t_units c 
FULL JOIN t_unittype t ON c.ntypeid=t.id
ON c.nnumberid=n.id
WHERE vinout='OUTGOING' AND t.vname = 'вызов' AND visinternational='Y'
GROUP BY n.vnumber, t.vname, c.nnumberid)
LOOP
FOR rec8 IN (SELECT c.nnumberid, n.vnumber, t.vname, sum(c.vvalue::numeric) AS summary FROM t_numbers n   -- t_numbers + t_units
FULL JOIN t_units c 
FULL JOIN t_unittype t ON c.ntypeid=t.id
ON c.nnumberid=n.id
WHERE vinout='OUTGOING' AND t.vname = 'вызов' AND viscityphone='Y'
GROUP BY n.vnumber, t.vname, c.nnumberid)
LOOP
FOR rec9 IN (SELECT c.nnumberid, n.vnumber, t.vname, sum(c.vvalue::numeric) AS summary FROM t_numbers n   -- t_numbers + t_units
FULL JOIN t_units c 
FULL JOIN t_unittype t ON c.ntypeid=t.id
ON c.nnumberid=n.id
WHERE vinout='OUTGOING' AND t.vname = 'вызов' AND visother='Y'
GROUP BY n.vnumber, t.vname, c.nnumberid)
LOOP
UPDATE t_numbers SET nbalance = (rec1.nwapcost*rec2.summary + rec1.noutsystemcost*rec6.summary + rec1.noutinternationalcost*rec7.summary + rec1.noutcityphonecost*rec8.summary + rec1.noutothercost*rec9.summary + rec1.nsmscost*rec4.summary + rec1.nmmscost*rec5.summary) + rec3.summary WHERE id=rec2.nnumberid OR id=rec3.nnumberid;
END LOOP;
END LOOP;
END LOOP;
END LOOP;
END LOOP;
END LOOP;
END LOOP;
END LOOP;
END LOOP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
COST 100;