Support A2Billing :

provided by Star2Billing S.L.

Support A2Billing :
It is currently Thu Mar 28, 2024 6:40 pm
Hosted Voice Broadcast


All times are UTC




Post new topic Reply to topic  [ 1 post ] 
Author Message
 Post subject: Scripts to help with A2billing Least Cost routing
PostPosted: Sun Jan 10, 2010 1:17 am 
Offline
User avatar

Joined: Sun Mar 19, 2006 3:13 pm
Posts: 123
Location: English Indiana, USA
I am posting some code rather than making a request. But it would be nice if something was done in a2billing to help adding rates easily to help with least cost routing. I am not sure anything I am posting can ever be used but it can be used by itself for someone who is a little knowledgeable in MYSQL.

I am posting some scripts to help modify rate decks to assist with a2billings least cost routing.
As many may not be aware of, a2billing and many (if not all other least cost routing telecom application) routes calls based on the longest prefix. If you try and add 10 providers you will discover that some providers might be using a longer prefix for the same areas. In such cases least cost routing will use the longest prefix even if that provider’s rates is 4 times higher for the same area than what the rest of the providers are charging.

Adding another provider to get better rates can actually cause you to pay a lot more if you don’t take in consideration if they are using longer prefixes than your other providers.

There are some dirty tricks some providers can use to steal your traffic from the other providers you might have in your least cost routing table. This is very simple to do. A provider can use all the longest available prefixes for any given area with high prices and give really cheap rates to fake prefixes or prefixes seldom used to make it appear they have good rates to some areas to entice you to add their route to your least cost routing table. However adding them could turn out to be a big mistake.

Even if they are not doing this on purpose you still need normalize your prefix lengths based on price in order for you to have true least cost routing. You can delete prefixes from providers to area you can get cheaper with another provider but this can cause your failover not to work right and this can be very time consuming.

I wrote some code in mysql triggers that will standardize prefixes lengths as data is entered. This will enable a2billing to always find the least cost provider regardless of what type of rate-deck your provider might have sent you and regardless of how many providers you might be using. This code well even analyze and shorten prefixes when it’s safe to do so. Example if provider has 1812 in their rate deck and priced at $0.01 and also have 1812739 priced at $0.01 and 18127392 priced at $0.002 the prefix 1812739 can be safely removed.

I have a provider with really good rates to north America but they use 7 digit prefixes (8 including the 1) and many have the same rate. They sent me a rate-deck with more than 500,000 records for just USA and Canada. In this case it can be reduced safely to around 250,000 without effecting exact prefix pricing. If I try and add 9 other north America providers and offer 4 types of call plans to my clients, my rate table would have 20 million records in it. And this doesn’t account for the fact many providers I buy from have intrastate and interstate pricing which can double records thus bringing it to almost 40 million records

This of course is assuming I am attempting to match prefix length for least cost routing to work. In this case by reducing and normalizing prefix length, least cost routing works like a charm and I saved myself from having 10 million extra records in my rate table. At the same time I don’t have to reduce all the prefixes down to just area codes and base the pricing off an average for the area code. Doing so well open doors for clients to send me only calls to the highest cost areas in those area codes.

Incase others have the same needs as I do I thought I would post some of my work here. I don’t mind if it’s edited and added to a2biling. I am not sure how it can be done but it would be nice for a2billing users that are less data savvy.


Code:
/**
* This file contains code from a rate program by (http://www.comcardLLC.com/)
*
* Commercial Source Code for least cost routing Billing platforms,   
* powered by COMCARDLLC.COM
*
* @copyright   Copyright (C) 2009-2010 - COMCARDLLC.COM
* @author      Samuel Atkins <[email protected]>
* @license     Free for personal use only. Contact author for resale
*       or adding to other applications
* @package     One file with SQL CODE
*
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
*
* order to properly read the notes and the code you will   
* need to use a program such as SQLYyog or other SQL editor
* If code is modified and improved please email me a copy
**/


-- 1. lets create the table for putting your new rates you just got from a new provider

CREATE TABLE `prefix_reduction_original` (
  `npanxx` VARCHAR(11) NOT NULL DEFAULT '0',
  `price` DOUBLE DEFAULT NULL,
  `buyrateinitblock` INT(11) DEFAULT NULL,
  `buyrateincrement` INT(11) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=latin1


-- 2. lets create table for the trigger code to remove duplicates,
-- prefixes not needed for routing and reduse other prefix lengths

CREATE TABLE `prefix_reduction` (
  `npanxx` VARCHAR(11) NOT NULL DEFAULT '0',
  `price` DOUBLE DEFAULT NULL,
  `buyrateinitblock` INT(11) DEFAULT NULL,
  `buyrateincrement` INT(11) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=latin1


-- 3. lets create table to hold the newly modified rates
CREATE TABLE `prefix_reduction_final` (
  `npanxx` VARCHAR(11) NOT NULL DEFAULT '0',
  `price` DOUBLE DEFAULT NULL,
  `buyrateinitblock` INT(11) DEFAULT NULL,
  `buyrateincrement` INT(11) DEFAULT NULL,
  PRIMARY KEY  (`npanxx`)
) ENGINE=INNODB DEFAULT CHARSET=latin1


-- 4. now create trigger for table 'prefix_reduction'


DELIMITER $$

USE `mya2billing`$$

DROP TRIGGER /*!50032 IF EXISTS */ `prefix_reduction_insert`$$

CREATE
    /*!50017 DEFINER = 'root'@'%' */
    TRIGGER `prefix_reduction_insert` BEFORE INSERT ON `prefix_reduction`
    FOR EACH ROW BEGIN
    DECLARE Strnpanxxnull TEXT DEFAULT '';
    DECLARE Strnpanxx TEXT DEFAULT '';
    DECLARE Strprice DOUBLE;
    DECLARE Strprice2 DOUBLE;
    DECLARE Strprefixlenght INTEGER;

  SET Strnpanxx = New.npanxx;
  SET Strprice = New.price;
 
-- finds a shorter prefix with same provider that has the same
-- price and INSERTS it instead of the longer prefix

SET Strnpanxxnull = (SELECT npanxx FROM mya2billing.prefix_reduction_original WHERE (prefix_reduction_original.npanxx
LIKE LEFT(Strnpanxx,13)
OR npanxx LIKE LEFT(Strnpanxx,12)
OR npanxx LIKE LEFT(Strnpanxx,11)
OR npanxx LIKE LEFT(Strnpanxx,10)
OR npanxx LIKE LEFT(Strnpanxx,9)
OR npanxx LIKE LEFT(Strnpanxx,8)
OR npanxx LIKE LEFT(Strnpanxx,7)
OR npanxx LIKE LEFT(Strnpanxx,6)
OR npanxx LIKE LEFT(Strnpanxx,5)
OR npanxx LIKE LEFT(Strnpanxx,4)
OR npanxx LIKE LEFT(Strnpanxx,3)
OR npanxx LIKE LEFT(Strnpanxx,2)
OR npanxx LIKE LEFT(Strnpanxx,1))
AND prefix_reduction_original.price = NEW.price ORDER BY LENGTH(npanxx) ASC LIMIT 0,1);

   SET Strprefixlenght = LENGTH(Strnpanxxnull);
-- this line of code is for reducing prefix length by one digit when there are no shorter prefixes found.
-- care should be taken when using this. setting  Strprefixlenght "< 8  THEN"  to "< 50  THEN"  well disable it
-- if there are lot of prefixes all being 8 digits in length and you wish to reduce them to 7
-- and there are no shorter prefixes beging with the same digits, this code will find
-- find the greatest number of prefixes with the exact same price and reduce them to
-- one diget less and leave the rest of the prefixes 8 digets or whatever digits its set for.

      IF Strnpanxxnull <> Strnpanxx OR Strprefixlenght < 8  THEN
      
INSERT INTO mya2billing.prefix_reduction_final
   (npanxx,
   price,
   buyrateinitblock,
   buyrateincrement
   )
   SELECT Strnpanxxnull, NEW.Price, buyrateinitblock, buyrateincrement FROM mya2billing.prefix_reduction_final WHERE Strnpanxxnull NOT IN (SELECT npanxx FROM mya2billing.prefix_reduction_final) LIMIT 0,1;
   
   
ELSE
   SET Strprice2 = (SELECT prefix_reduction_original.price FROM  mya2billing.prefix_reduction_original WHERE npanxx LIKE CONCAT(LEFT(Strnpanxxnull,7),'%') AND LEFT(Strnpanxxnull,7) NOT IN (SELECT npanxx FROM prefix_reduction_original) GROUP BY prefix_reduction_original.price ORDER BY COUNT(npanxx) DESC LIMIT 0, 1);
         
IF Strprice = Strprice2 THEN
INSERT INTO mya2billing.prefix_reduction_final
   (npanxx,
   price,
   buyrateinitblock,
   buyrateincrement
   )
   SELECT LEFT(Strnpanxxnull,7), NEW.Price, buyrateinitblock, buyrateincrement FROM mya2billing.prefix_reduction_final WHERE LEFT(Strnpanxxnull,7) NOT IN (SELECT npanxx FROM mya2billing.prefix_reduction_final) LIMIT 0,1;
ELSE
INSERT INTO mya2billing.prefix_reduction_final
   (npanxx,
   price,
   buyrateinitblock,
   buyrateincrement
   )
   SELECT Strnpanxxnull, NEW.Price, buyrateinitblock, buyrateincrement  FROM mya2billing.prefix_reduction_final WHERE Strnpanxxnull NOT IN (SELECT npanxx FROM mya2billing.prefix_reduction_final) LIMIT 0,1;
END IF;
END IF;
END;
$$

DELIMITER ;


-- 5. in order for the trigger to work there must be one record in table 'prefix_reduction_final'  so lets add it.

INSERT INTO mya2billing.prefix_reduction_final
   (npanxx,
   price,
   buyrateinitblock,
   buyrateincrement
   )
   VALUES
   ('1111',
   '1111',
   '1111',
   '1111'
   );


-- 6.  put your rates deck from your provider in table 'prefix_reduction_original' and run the below SQL statement




INSERT INTO mya2billing.prefix_reduction
   (npanxx,
   price,
   buyrateinitblock,
   buyrateincrement
   )
SELECT    npanxx,
   price,
   buyrateinitblock,
   buyrateincrement
   FROM
   mya2billing.prefix_reduction_original
   ;

-- 7. now you have just removed all duplicate prefixes and redundant long
-- prefixes where shorter prefix could be used.
-- This prevents providers from playing a dirty little trick to steal your
-- traffic when using thier rate deck in your least cost routing table

-- the data in table 'prefix_reduction' and table 'prefix_reduction_original' are no longer needed
-- so lets delete it so its it doesnt grow too large.

DELETE prefix_reduction.* FROM prefix_reduction;
DELETE prefix_reduction_original.* FROM prefix_reduction_original;

-- 8. now lets delete the record added to the 'prefix_reduction_final' table
--  so it doesnt screw up your routing.



DELETE FROM mya2billing.prefix_reduction_final
   WHERE
   npanxx = '1111' ;




-- Now you ready to add the new rates to your 'cc_ratecard' table. So lets make
--  2 temp tables. 'cc_ratecard1' well be the one with the triger code and cc_ratecard2
-- well be your final table you will use for a2billing. The Trigger contains the code
-- to make all prefixes with the best buyrate ratesthe longest or atleast the same length as
-- your other prefixes from other providers that might have a higher buyrate. NOW A2billing
-- least cost routing feature will do exactly what you want it to do. The 'cc_ratecard2'
-- will be your final table which we will rename to 'cc_ratecard'. after all the rates has been added.


-- now create table cc_ratecard1
-- 9.
CREATE TABLE `cc_ratecard1` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `idtariffplan` INT(11) NOT NULL DEFAULT '0',
  `dialprefix` VARCHAR(30) COLLATE utf8_bin NOT NULL,
  `buyrate` DECIMAL(15,6) NOT NULL DEFAULT '0.000000',
  `buyrateinitblock` INT(11) NOT NULL DEFAULT '0',
  `buyrateincrement` INT(11) NOT NULL DEFAULT '0',
  `rateinitial` DECIMAL(15,6) NOT NULL DEFAULT '0.000000',
  `initblock` INT(11) NOT NULL DEFAULT '0',
  `billingblock` INT(11) NOT NULL DEFAULT '0',
  `connectcharge` DECIMAL(15,6) NOT NULL DEFAULT '0.000000',
  `disconnectcharge` DECIMAL(15,6) NOT NULL DEFAULT '0.000000',
  `stepchargea` DECIMAL(15,6) NOT NULL DEFAULT '0.000000',
  `chargea` DECIMAL(15,6) NOT NULL DEFAULT '0.000000',
  `timechargea` INT(11) NOT NULL DEFAULT '0',
  `billingblocka` INT(11) NOT NULL DEFAULT '0',
  `stepchargeb` DECIMAL(15,6) NOT NULL DEFAULT '0.000000',
  `chargeb` DECIMAL(15,6) NOT NULL DEFAULT '0.000000',
  `timechargeb` INT(11) NOT NULL DEFAULT '0',
  `billingblockb` INT(11) NOT NULL DEFAULT '0',
  `stepchargec` DOUBLE NOT NULL DEFAULT '0',
  `chargec` DOUBLE NOT NULL DEFAULT '0',
  `timechargec` INT(11) NOT NULL DEFAULT '0',
  `billingblockc` INT(11) NOT NULL DEFAULT '0',
  `startdate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `stopdate` TIMESTAMP NOT NULL DEFAULT '2019-05-15 13:35:11',
  `starttime` INT(5) UNSIGNED DEFAULT '0',
  `endtime` INT(5) UNSIGNED DEFAULT '10079',
  `id_trunk` INT(11) DEFAULT '-1',
  `musiconhold` VARCHAR(100) COLLATE utf8_bin NOT NULL DEFAULT '',
  `id_outbound_cidgroup` INT(11) DEFAULT '-1',
  `rounding_calltime` INT(11) NOT NULL DEFAULT '0',
  `rounding_threshold` INT(11) NOT NULL DEFAULT '0',
  `additional_block_charge` DECIMAL(15,6) NOT NULL DEFAULT '0.000000',
  `additional_block_charge_time` INT(11) NOT NULL DEFAULT '0',
  `tag` VARCHAR(50) COLLATE utf8_bin DEFAULT NULL,
  `disconnectcharge_after` INT(11) NOT NULL DEFAULT '0',
  `is_merged` INT(11) DEFAULT '0',
  `additional_grace` INT(11) NOT NULL DEFAULT '0',
  `minimal_cost` DECIMAL(15,6) NOT NULL DEFAULT '0.000000',
  `announce_time_correction` DECIMAL(5,3) NOT NULL DEFAULT '1.000',
  `destination` INT(11) DEFAULT '0',
  PRIMARY KEY  (`id`),
  KEY `ind_cc_ratecard_dialprefix` (`dialprefix`),
  KEY `idtariffplan_index` (`idtariffplan`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin

-- now create table cc_ratecard2
CREATE TABLE `cc_ratecard2` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `idtariffplan` INT(11) NOT NULL DEFAULT '0',
  `dialprefix` VARCHAR(30) COLLATE utf8_bin NOT NULL,
  `buyrate` DECIMAL(15,6) NOT NULL DEFAULT '0.000000',
  `buyrateinitblock` INT(11) NOT NULL DEFAULT '0',
  `buyrateincrement` INT(11) NOT NULL DEFAULT '0',
  `rateinitial` DECIMAL(15,6) NOT NULL DEFAULT '0.000000',
  `initblock` INT(11) NOT NULL DEFAULT '0',
  `billingblock` INT(11) NOT NULL DEFAULT '0',
  `connectcharge` DECIMAL(15,6) NOT NULL DEFAULT '0.000000',
  `disconnectcharge` DECIMAL(15,6) NOT NULL DEFAULT '0.000000',
  `stepchargea` DECIMAL(15,6) NOT NULL DEFAULT '0.000000',
  `chargea` DECIMAL(15,6) NOT NULL DEFAULT '0.000000',
  `timechargea` INT(11) NOT NULL DEFAULT '0',
  `billingblocka` INT(11) NOT NULL DEFAULT '0',
  `stepchargeb` DECIMAL(15,6) NOT NULL DEFAULT '0.000000',
  `chargeb` DECIMAL(15,6) NOT NULL DEFAULT '0.000000',
  `timechargeb` INT(11) NOT NULL DEFAULT '0',
  `billingblockb` INT(11) NOT NULL DEFAULT '0',
  `stepchargec` DOUBLE NOT NULL DEFAULT '0',
  `chargec` DOUBLE NOT NULL DEFAULT '0',
  `timechargec` INT(11) NOT NULL DEFAULT '0',
  `billingblockc` INT(11) NOT NULL DEFAULT '0',
  `startdate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `stopdate` TIMESTAMP NOT NULL DEFAULT '2019-05-15 13:35:11',
  `starttime` INT(5) UNSIGNED DEFAULT '0',
  `endtime` INT(5) UNSIGNED DEFAULT '10079',
  `id_trunk` INT(11) DEFAULT '-1',
  `musiconhold` VARCHAR(100) COLLATE utf8_bin NOT NULL DEFAULT '',
  `id_outbound_cidgroup` INT(11) DEFAULT '-1',
  `rounding_calltime` INT(11) NOT NULL DEFAULT '0',
  `rounding_threshold` INT(11) NOT NULL DEFAULT '0',
  `additional_block_charge` DECIMAL(15,6) NOT NULL DEFAULT '0.000000',
  `additional_block_charge_time` INT(11) NOT NULL DEFAULT '0',
  `tag` VARCHAR(50) COLLATE utf8_bin DEFAULT '',
  `disconnectcharge_after` INT(11) NOT NULL DEFAULT '0',
  `is_merged` INT(11) DEFAULT '0',
  `additional_grace` INT(11) NOT NULL DEFAULT '0',
  `minimal_cost` DECIMAL(15,6) NOT NULL DEFAULT '0.000000',
  `announce_time_correction` DECIMAL(5,3) NOT NULL DEFAULT '1.000',
  `destination` INT(11) DEFAULT '0',
  PRIMARY KEY  (`id`),
  KEY `ind_cc_ratecard_dialprefix` (`dialprefix`),
  KEY `idtariffplan_index` (`idtariffplan`)
) ENGINE=MYISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin



-- 10. lets make a trigger for table 'cc_ratecard1'
DELIMITER $$

USE `mya2billing`$$

DROP TRIGGER /*!50032 IF EXISTS */ `cc_ratecard_validate_matchrates`$$

CREATE
    /*!50017 DEFINER = 'root'@'%' */
    TRIGGER `cc_ratecard_validate_matchrates` BEFORE INSERT ON `cc_ratecard1`
    FOR EACH ROW BEGIN
    DECLARE strcount TEXT DEFAULT '';
    DECLARE valid INTEGER;
    DECLARE Strleft TEXT DEFAULT '';
    DECLARE Strleft1 TEXT DEFAULT '';
    DECLARE Strleft2 TEXT DEFAULT '';
  SET Strleft = LENGTH(NEW.dialprefix);
  SET strcount = NEW.dialprefix;

SELECT '0' REGEXP REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONCAT('^', NEW.dialprefix, '$'), 'X', '[0-9]'), 'Z', '[1-9]'), 'N', '[2-9]'), '.', '.+'), '_', '')INTO valid; 

/*
-- checks other provider's prefixes. If exist than new prefixe needs to match length.
-- If none exist than only the new prefix for the new provider well be INSERTED
-- if some exist it will not match other prefixes with same or higher buy cost
*/
SET Strleft = (SELECT cc_ratecard2.dialprefix FROM mya2billing.cc_ratecard2 WHERE cc_ratecard2.dialprefix
LIKE CONCAT(strcount,'%') AND cc_ratecard2.idtariffplan <> NEW.idtariffplan AND cc_ratecard2.buyrate > NEW.buyrate LIMIT 0,1);


/*-- check new prefix is not already INSERTED.
-- If match is found prefix is lengthened to match exiting prefix length
-- later instead of skipping prefixes already existing, existing prefix records can be updated.
*/
SET Strleft1 = (SELECT cc_ratecard2.dialprefix FROM mya2billing.cc_ratecard2 WHERE cc_ratecard2.dialprefix
LIKE strcount AND cc_ratecard2.idtariffplan = NEW.idtariffplan LIMIT 0,1);

--  Checks if prefix doesnt match the beginning of any existing providers prefixes
--   and hasnt already been INSERTED for new provider. 
IF Strleft IS NULL AND Strleft1 IS NULL THEN

-- INSERT new prefix. Only the new prefix record will be INSERTED
INSERT INTO mya2billing.cc_ratecard2 (idtariffplan, dialprefix, buyrate, buyrateinitblock, buyrateincrement, rateinitial, initblock, billingblock, connectcharge, disconnectcharge, stepchargea, chargea, timechargea, billingblocka, stepchargeb, chargeb, timechargeb, billingblockb, stepchargec, chargec, timechargec, billingblockc, starttime, endtime, id_trunk, musiconhold, id_outbound_cidgroup, rounding_calltime, rounding_threshold, additional_block_charge, additional_block_charge_time, tag, disconnectcharge_after, is_merged, additional_grace, minimal_cost, announce_time_correction, destination)
VALUES (NEW.idtariffplan, strcount, NEW.buyrate, NEW.buyrateinitblock, NEW.buyrateincrement, NEW.rateinitial, NEW.initblock, NEW.billingblock, NEW.connectcharge, NEW.disconnectcharge, NEW.stepchargea, NEW.chargea, NEW.timechargea, NEW.billingblocka, NEW.stepchargeb, NEW.chargeb, NEW.timechargeb, NEW.billingblockb, NEW.stepchargec, NEW.chargec, NEW.timechargec, NEW.billingblockc, NEW.starttime, NEW.endtime, NEW.id_trunk, '', NEW.id_outbound_cidgroup, NEW.rounding_calltime, NEW.rounding_threshold, NEW.additional_block_charge, NEW.additional_block_charge_time, '', NEW.disconnectcharge_after, NEW.is_merged, NEW.additional_grace, NEW.minimal_cost, NEW.announce_time_correction, NEW.destination);

ELSE


-- Checks new prefix if not already been INSERTED. Only new prefix record will be INSERTED
IF Strleft1 IS NULL THEN

INSERT INTO mya2billing.cc_ratecard2 (idtariffplan, dialprefix, buyrate, buyrateinitblock, buyrateincrement, rateinitial, initblock, billingblock, connectcharge, disconnectcharge, stepchargea, chargea, timechargea, billingblocka, stepchargeb, chargeb, timechargeb, billingblockb, stepchargec, chargec, timechargec, billingblockc, starttime, endtime, id_trunk, musiconhold, id_outbound_cidgroup, rounding_calltime, rounding_threshold, additional_block_charge, additional_block_charge_time, tag, disconnectcharge_after, is_merged, additional_grace, minimal_cost, announce_time_correction, destination)
VALUES (NEW.idtariffplan,strcount, NEW.buyrate, NEW.buyrateinitblock, NEW.buyrateincrement, NEW.rateinitial, NEW.initblock, NEW.billingblock, NEW.connectcharge, NEW.disconnectcharge, NEW.stepchargea, NEW.chargea, NEW.timechargea, NEW.billingblocka, NEW.stepchargeb, NEW.chargeb, NEW.timechargeb, NEW.billingblockb, NEW.stepchargec, NEW.chargec, NEW.timechargec, NEW.billingblockc, NEW.starttime, NEW.endtime, NEW.id_trunk, '', NEW.id_outbound_cidgroup, NEW.rounding_calltime, NEW.rounding_threshold, NEW.additional_block_charge, NEW.additional_block_charge_time, '', NEW.disconnectcharge_after, NEW.is_merged, NEW.additional_grace, NEW.minimal_cost, NEW.announce_time_correction, NEW.destination);


-- Match the new prefix being INSERTED to all other provider's prefix and
-- if new prefix price is lower then existing prefixes the prefix will be
-- lengthen to match any prefixes with longer length. New prefixes being
-- INSERTED well not be lengthen to match a existing prefixes of other provider
-- with a cheaper buyrate. If new prefix from new provider is 1812 $0.01 and
-- finds other prefixes from other providers that are 1812739 $0.001 and 18127392 $0.20
-- 1812 $0.01 will be INSERTED as 1812 $0.1 and 18127392 $0.01 and will skip 1812739 $0.001
-- prefixes well never be shorten however deplicates well be removed.
IF Strleft IS NOT NULL THEN


INSERT INTO mya2billing.cc_ratecard2 (idtariffplan, dialprefix, buyrate, buyrateinitblock, buyrateincrement, rateinitial, initblock, billingblock, connectcharge, disconnectcharge, stepchargea, chargea, timechargea, billingblocka, stepchargeb, chargeb, timechargeb, billingblockb, stepchargec, chargec, timechargec, billingblockc, starttime, endtime, id_trunk, musiconhold, id_outbound_cidgroup, rounding_calltime, rounding_threshold, additional_block_charge, additional_block_charge_time, tag, disconnectcharge_after, is_merged, additional_grace, minimal_cost, announce_time_correction, destination)
SELECT NEW.idtariffplan, cc_ratecard2.dialprefix, NEW.buyrate, NEW.buyrateinitblock, NEW.buyrateincrement, NEW.rateinitial, NEW.initblock, NEW.billingblock, NEW.connectcharge, NEW.disconnectcharge, NEW.stepchargea, NEW.chargea, NEW.timechargea, NEW.billingblocka, NEW.stepchargeb, NEW.chargeb, NEW.timechargeb, NEW.billingblockb, NEW.stepchargec, NEW.chargec, NEW.timechargec, NEW.billingblockc, NEW.starttime, NEW.endtime, NEW.id_trunk, '', NEW.id_outbound_cidgroup, NEW.rounding_calltime, NEW.rounding_threshold, NEW.additional_block_charge, NEW.additional_block_charge_time, '', NEW.disconnectcharge_after, NEW.is_merged, NEW.additional_grace, NEW.minimal_cost, NEW.announce_time_correction, NEW.destination
FROM cc_ratecard2
WHERE cc_ratecard2.dialprefix LIKE CONCAT(strcount,'%') AND  cc_ratecard2.idtariffplan <> NEW.idtariffplan AND cc_ratecard2.buyrate > NEW.buyrate AND (cc_ratecard2.dialprefix NOT IN (SELECT cc_ratecard2.dialprefix FROM cc_ratecard2 WHERE cc_ratecard2.idtariffplan = NEW.idtariffplan) OR cc_ratecard2.dialprefix NOT IN (SELECT cc_ratecard2.dialprefix FROM cc_ratecard2 WHERE cc_ratecard2.idtariffplan = NEW.idtariffplan)) GROUP BY cc_ratecard2.dialprefix;

END IF;
END IF;
END IF;
END;
$$

DELIMITER ;   

-- now lets insert or first provider's rate deck  from table 'prefix_reduction_final'
-- into table 'cc_ratecard1' you should save the data in 'prefix_reduction_final'
-- by renameing it as you will need to insert it again after other providers have been
--  added. This is because there might be some new longer prefixes added which we
-- need to have this provider's prefix match it's length.

-- 11. So lets rename it to the idtariffplan we want to use

ALTER TABLE prefix_reduction_final RENAME TO prefix_reduction_final_1

-- 12. lets remake the old table it with the original name so
-- it can be used for  next rate-deck from next provider.


CREATE TABLE `prefix_reduction_final` (
  `npanxx` VARCHAR(11) NOT NULL DEFAULT '0',
  `price` DOUBLE DEFAULT NULL,
  `buyrateinitblock` INT(11) DEFAULT NULL,
  `buyrateincrement` INT(11) DEFAULT NULL,
  PRIMARY KEY  (`npanxx`)
) ENGINE=INNODB DEFAULT CHARSET=latin1

-- now its time insert our first set of rates into table 'cc_ratecard1'
-- since this is the first set of record to insert it should be safe
-- just to insert it directly into table 'cc_ratecard2' since the trigger
-- is probably not going to make any changes to the data and it would be faster
-- however for the sake of writing less instructions lets just  insert records
-- into table 'cc_ratecard1' and let the trigger INSERT the records into 'cc_ratecard2'
-- and remove duplicates if any exist.



-- 13 READ all the NOTES behind the "--" before running the below SQL statement

INSERT INTO cc_ratecard1
(
idtariffplan,
dialprefix,
buyrate,
buyrateinitblock,
buyrateincrement,
rateinitial,
initblock,
billingblock,
connectcharge,
disconnectcharge,
id_trunk,
musiconhold,
id_outbound_cidgroup,
destination
)

SELECT  --
1 AS idtariffplan, -- Same as RateCARD ID in A2billing for now lets use 1 and the next provider can be 2 than 3 and so on.
npanxx AS dialprefix,        -- prefix 
price AS buyrate, --  provider's RATE.
buyrateinitblock,
buyrateincrement,
0.019 AS rateinitial, -- this field is what you charge clients perminute. If you want to bill your client 25% more then your cost than put "price*0.25+price AS rateinitial"
60 AS initblock, -- <== billingblock 60 = one minute. You can also use "buyrateinitblock"  to pass to your client thebuyrateinitblock your provider bills you at
60 AS billingblock,  -- <== billingblock 60 = one minute You can also use "buyrateincrement"  to pass to your client buyrateincrement your provider bills you at
0.00 AS connectcharge, -- <== sets to 0 incase table defaults are not 0
0 AS disconnectcharge, -- <== sets to 0 incase table defaults are not 0
'-1' AS id_trunk,     -- <== doesnt assign trunk trunk to use. 
'' AS musiconhold,   -- <== sets music field to blank instead of null. If set to null your calls will fail
'-1' AS id_outbound_cidgroup, -- <== doesnt assign caller ID group
LEFT(npanxx,4) AS destination -- <== puts the first 4 digits of the prefix in this field. This might not be a good idea as a2billing might use an ID
FROM prefix_reduction_final_1 ORDER BY LENGTH(npanxx) DESC; -- <--its very important LENGTH(npanxx) DESC is used otherwise the trigger well not update prefixes right

--  "from prefix_reduction_final_1"  is table name to change
-- if your records are in a differant table.



-- ok now that finish adding the first provider.  You can just delete
-- the records in the 'cc_ratecard1' table or keep them if you want to do a record count.

-- to delete the records use

DELETE cc_ratecard1.* FROM cc_ratecard1;


-- now when you are ready to add your next provider from so lets start back at step 5.


-- 5.

INSERT INTO mya2billing.prefix_reduction_final
   (npanxx,
   price,
   buyrateinitblock,
   buyrateincrement
   )
   VALUES
   ('1111',
   '1111',
   '1111',
   '1111'
   );


-- 6. 


INSERT INTO mya2billing.prefix_reduction
   (npanxx,
   price,
   buyrateinitblock,
   buyrateincrement
   )
SELECT    npanxx,
   price,
   buyrateinitblock,
   buyrateincrement
   FROM
   mya2billing.prefix_reduction_original
   ;

-- 7.

DELETE prefix_reduction.* FROM prefix_reduction;
DELETE prefix_reduction_original.* FROM prefix_reduction_original;

-- 8.


DELETE FROM mya2billing.prefix_reduction_final
   WHERE
   npanxx = '1111' ;

-- 9. -- Notice this time around we change the name to prefix_reduction_final_2
-- to match your second RATECARD ID.
ALTER TABLE prefix_reduction_final RENAME TO prefix_reduction_final_2



-- 10.


CREATE TABLE `prefix_reduction_final` (
  `npanxx` VARCHAR(11) NOT NULL DEFAULT '0',
  `price` DOUBLE DEFAULT NULL,
  `buyrateinitblock` INT(11) DEFAULT NULL,
  `buyrateincrement` INT(11) DEFAULT NULL,
  PRIMARY KEY  (`npanxx`)
) ENGINE=INNODB DEFAULT CHARSET=latin1



-- now lets add the second provider and change "1 AS idtariffplan," to  "2 AS idtariffplan,"
-- if isnt anything else you want to change than just change
-- "FROM prefix_reduction_final_1 ORDER BY LENGTH(npanxx) DESC;" to
-- "FROM prefix_reduction_final_2 ORDER BY LENGTH(npanxx) DESC;"
-- now run the code.


INSERT INTO cc_ratecard1
(
idtariffplan,
dialprefix,
buyrate,
buyrateinitblock,
buyrateincrement,
rateinitial,
initblock,
billingblock,
connectcharge,
disconnectcharge,
id_trunk,
musiconhold,
id_outbound_cidgroup,
destination
)

SELECT
2 AS idtariffplan, -- <== besure to change this with each provider
npanxx AS dialprefix,
price AS buyrate,
buyrateinitblock,
buyrateincrement,
0.019 AS rateinitial,
60 AS initblock,
60 AS billingblock,
0.00 AS connectcharge,
0 AS disconnectcharge,
'-1' AS id_trunk,   
'' AS musiconhold,   
'-1' AS id_outbound_cidgroup,
LEFT(npanxx,4) AS destination
FROM prefix_reduction_final_2 ORDER BY LENGTH(npanxx) DESC;

-- < font forget to change "prefix_reduction_final_2" each time you add provider
-- this is the name of the table for each provider

-- If finished adding providers you need to reINSERT all the all previous providers.
-- If you added 5 providers you will need to insert the first 4 again but not the last rate-deck
-- This does not create duplicates and by reinserting the previous rate-decks again
-- it insures all prefixes will be normalized with any prefixes INSERT afterwards

-- so lets reINSERT the first ratedeck and any rate-deck after it but not the last ratedeck
-- If Following these instructions to the letter and you only inserted 2 rate-decks with the
-- same names I used than run the below Sql STATEMENT REINSERT the first Rate-DECK


INSERT INTO cc_ratecard1
(
idtariffplan,
dialprefix,
buyrate,
buyrateinitblock,
buyrateincrement,
rateinitial,
initblock,
billingblock,
connectcharge,
disconnectcharge,
id_trunk,
musiconhold,
id_outbound_cidgroup,
destination
)

SELECT
1 AS idtariffplan, -- <== Notice this is back to 1 same as first rate-deck
npanxx AS dialprefix,
price AS buyrate,
buyrateinitblock,
buyrateincrement,
0.019 AS rateinitial,
60 AS initblock,
60 AS billingblock,
0.00 AS connectcharge,
0 AS disconnectcharge,
'-1' AS id_trunk,   
'' AS musiconhold,   
'-1' AS id_outbound_cidgroup,
LEFT(npanxx,4) AS destination
FROM prefix_reduction_final_1 ORDER BY LENGTH(npanxx) DESC; -- <= using same records for first rate-deck

-- Everything else should be same as it was the first time this rate-deck was was inserted





-- the final step is to update your a2billing  to use the new tariffplans.
-- If you are sure the IDTARIFFPLAN IDs  were the same as what you had before than
-- the only step you need to do now is to rename tables so a2billing can read from
-- your new 'cc_ratecard' table.

-- rename you current cc_ratecard to cc_ratecard_old
ALTER TABLE cc_ratecard RENAME TO cc_ratecard_old

-- rename cc_ratecard2  to cc_ratecard
ALTER TABLE cc_ratecard_2 RENAME TO cc_ratecard

-- there have been some slight modifications to table ‘cc_ratecard2’ which were not
-- originally in ‘cc_ratecard’ a2billing 1.4.4.1 to change this back run the sql statement below.
-- depending on how many records you have this sql statement might 5 to 15 minutes to run


ALTER TABLE `cc_ratecard`
   CHANGE `dialprefix` `dialprefix` CHAR(30)  COLLATE utf8_bin NOT NULL AFTER `idtariffplan`,
   CHANGE `buyrate` `buyrate` DECIMAL(15,5)   NOT NULL DEFAULT '0.00000' AFTER `dialprefix`,
   CHANGE `rateinitial` `rateinitial` DECIMAL(15,5)   NOT NULL DEFAULT '0.00000' AFTER `buyrateincrement`,
   CHANGE `connectcharge` `connectcharge` DECIMAL(15,5)   NOT NULL DEFAULT '0.00000' AFTER `billingblock`,
   CHANGE `disconnectcharge` `disconnectcharge` DECIMAL(15,5)   NOT NULL DEFAULT '0.00000' AFTER `connectcharge`,
   CHANGE `stepchargea` `stepchargea` DECIMAL(15,5)   NOT NULL DEFAULT '0.00000' AFTER `disconnectcharge`,
   CHANGE `chargea` `chargea` DECIMAL(15,5)   NOT NULL DEFAULT '0.00000' AFTER `stepchargea`,
   CHANGE `stepchargeb` `stepchargeb` DECIMAL(15,5)   NOT NULL DEFAULT '0.00000' AFTER `billingblocka`,
   CHANGE `chargeb` `chargeb` DECIMAL(15,5)   NOT NULL DEFAULT '0.00000' AFTER `stepchargeb`,
   CHANGE `stepchargec` `stepchargec` FLOAT   NOT NULL DEFAULT '0' AFTER `billingblockb`,
   CHANGE `chargec` `chargec` FLOAT   NOT NULL DEFAULT '0' AFTER `stepchargec`,
   CHANGE `stopdate` `stopdate` TIMESTAMP   NOT NULL DEFAULT '0000-00-00 00:00:00' AFTER `startdate`,
   CHANGE `starttime` `starttime` SMALLINT(5) UNSIGNED   NULL DEFAULT '0' AFTER `stopdate`,
   CHANGE `endtime` `endtime` SMALLINT(5) UNSIGNED   NULL DEFAULT '10079' AFTER `starttime`,
   CHANGE `musiconhold` `musiconhold` CHAR(100)  COLLATE utf8_bin NOT NULL AFTER `id_trunk`,
   CHANGE `additional_block_charge` `additional_block_charge` DECIMAL(15,5)   NOT NULL DEFAULT '0.00000' AFTER `rounding_threshold`,
   CHANGE `tag` `tag` CHAR(50)  COLLATE utf8_bin NULL AFTER `additional_block_charge_time`,
   CHANGE `minimal_cost` `minimal_cost` DECIMAL(15,5)   NOT NULL DEFAULT '0.00000' AFTER `additional_grace`, COMMENT='';




There are a few better ways this code could have been written. Example I believe it can all be written in a trigger inside of a2billing table cc_ratecard. I had other needs for it and that is the reason I done this way.


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 1 post ] 
Auto Dialer Software


All times are UTC


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
cron
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group