Support A2Billing :

provided by Star2Billing S.L.

Support A2Billing :
It is currently Wed Oct 23, 2019 7:25 am
VoIP Billing solution


All times are UTC




Post new topic Reply to topic  [ 9 posts ] 
Author Message
 Post subject: RateEngine patch
PostPosted: Sun Dec 28, 2008 4:46 pm 
Offline

Joined: Sun Dec 28, 2008 4:34 pm
Posts: 5
Location: singapore
There is serious bug in RateEngine class that affects the ordering of trunks to use. It chooses incorrect trunk that costs more than the best route.

For example, let's assume there are two trunks, TrunkA and TrunkB.

TrunkB rates are same to all Turkmenistan numbers:
993 0.10

TrunkA's rates are little different, they charge more for mobile phone numbers.
9936 0.13
993 0.10

Now let's say we want to dial 99365xxxxxx. First, RateEngine will find the rate with the longest prefix (9936 - 0.13 TrunkA), and discard
all other rates whose prefix length is less than the longest. In this case, best route is TrunkB, but it is discarded, because len(993) < len(9936).

Below is the new rate_engine_findrates() function, which fixes above problem.

cheers,
Atamurad Hezretkuliyev

Code:
/*

      RATE ENGINE

      CALCUL THE RATE ACCORDING TO THE RATEGROUP, LCR - RATECARD

   */

   function rate_engine_findrates (&$A2B, $phonenumber, $tariffgroupid){

      

      global $agi;

      

      // Check if we want to force the call plan

      if (is_numeric($A2B->agiconfig['force_callplan_id']) && ($A2B->agiconfig['force_callplan_id'] > 0)){

         $A2B -> debug( VERBOSE | WRITELOG, $agi, __FILE__, __LINE__, "force the call plan : ".$A2B->agiconfig['force_callplan_id']);

         $tariffgroupid = $A2B->tariff = $A2B->agiconfig['force_callplan_id'];

      }

      

      if ($this->webui){

         $A2B -> debug( WRITELOG, $agi, __FILE__, __LINE__, "[CC_asterisk_rate-engine: ($tariffgroupid, $phonenumber)]");   

      }

      

      

      /***  0 ) CODE TO RETURN THE DAY OF WEEK + CREATE THE CLAUSE  ***/

      

      $daytag = date("w", time()); // Day of week ( Sunday = 0 .. Saturday = 6 )

      $hours = date("G", time()); // Hours in 24h format ( 0-23 )

      $minutes = date("i", time()); // Minutes (00-59)

      if (!$daytag) $daytag=6;

      else $daytag--;

      if ($this -> debug_st) echo "$daytag $hours $minutes <br>";

      // Race condiction on $minutes ?!

      $minutes_since_monday = ($daytag * 1440) + ($hours * 60) + $minutes;

      if ($this -> debug_st) echo "$minutes_since_monday<br> ";

      //$sql_clause_days = " AND ".$daytable[$daytag]."='1' ";      

      $sql_clause_days = " AND (starttime <= ".$minutes_since_monday." AND endtime >=".$minutes_since_monday.") ";

      

      /*

      

      SELECT tariffgroupname, lcrtype, idtariffgroup, cc_tariffgroup_plan.idtariffplan, tariffname, destination,

      

      cc_ratecard.id,

      dialprefix, destination, buyrate, buyrateinitblock, buyrateincrement, rateinitial, initblock, billingblock,

      connectcharge, disconnectcharge, stepchargea, chargea, timechargea, billingblocka, stepchargeb, chargeb,

      timechargeb, billingblockb, stepchargec, chargec, timechargec, billingblockc,   

      

      cc_tariffplan.id_trunk AS tp_id_trunk, tp_trunk.trunkprefix AS tp_trunk, tp_trunk.providertech AS tp_providertech,

      tp_trunk.providerip AS tp_providerip, tp_trunk.removeprefix AS tp_removeprefix,

      cc_ratecard.id_trunk AS rc_id_trunk, rt_trunk.trunkprefix AS rc_trunkprefix, rt_trunk.providertech AS rc_providertech,

      rt_trunk.providerip AS rc_providerip, rt_trunk.removeprefix AS rc_removeprefix

      

      FROM cc_tariffgroup

      LEFT JOIN cc_tariffgroup_plan ON idtariffgroup=id

      LEFT JOIN cc_tariffplan ON cc_tariffplan.id=cc_tariffgroup_plan.idtariffplan

      LEFT JOIN cc_ratecard ON cc_ratecard.idtariffplan=cc_tariffplan.id

      LEFT JOIN trunk AS rt_trunk ON cc_ratecard.id_trunk=rt_trunk.id_trunk

      LEFT JOIN trunk AS tp_trunk ON cc_tariffplan.id_trunk=tp_trunk.id_trunk

      

      WHERE dialprefix=SUBSTRING('346586699595',1,length(dialprefix))

      AND startingdate<= CURRENT_TIMESTAMP AND (expirationdate > CURRENT_TIMESTAMP OR expirationdate IS NULL OR LENGTH(expirationdate)<5)

      AND startdate<= CURRENT_TIMESTAMP AND (stopdate > CURRENT_TIMESTAMP OR stopdate IS NULL OR LENGTH(stopdate)<5)

      ORDER BY LENGTH(dialprefix) DESC

      

      */

      

      if (strlen($A2B->dnid)>=1) $mydnid = $A2B->dnid;

      if (strlen($A2B->CallerID)>=1) $mycallerid = $A2B->CallerID;

      

      if ($this->webui) $A2B -> debug( WRITELOG, $agi, __FILE__, __LINE__, "[CC_asterisk_rate-engine: CALLERID]\n".$A2B->CallerID."\n",0);

      

      if ($A2B->config["database"]['dbtype'] != "postgres"){

         $DNID_QUERY = "SELECT count(dnidprefix) FROM cc_tariffplan RIGHT JOIN cc_tariffgroup_plan ON cc_tariffgroup_plan.idtariffgroup=$tariffgroupid WHERE dnidprefix=SUBSTRING('$mydnid',1,length(dnidprefix))";

         $result_sub = $A2B->instance_table -> SQLExec ($A2B -> DBHandle, $DNID_QUERY);

         if (!is_array($result_sub) || count($result_sub)==0) $nb_dnid = 0;

         else $nb_dnid = $result_sub[0][0];

         $DNID_SUB_QUERY = "AND 0 = $nb_dnid";

         

         $CALLERID_QUERY = "SELECT count(calleridprefix) FROM cc_tariffplan RIGHT JOIN cc_tariffgroup_plan ON cc_tariffgroup_plan.idtariffgroup=$tariffgroupid WHERE calleridprefix=SUBSTRING('$mycallerid',1,length(calleridprefix))";

         $result_sub = $A2B->instance_table -> SQLExec ($A2B -> DBHandle, $CALLERID_QUERY);

         if (!is_array($result_sub) || count($result_sub)==0) $nb_callerid = 0;

         else $nb_callerid = $result_sub[0][0];

         $CID_SUB_QUERY = "AND 0 = $nb_callerid";

         if ($this->webui) $A2B -> debug( WRITELOG, $agi, __FILE__, __LINE__, "[CALLERID_QUERY]\n".$CALLERID_QUERY."\n[RESULT]".print_r($result_sub, 1)."\n",0);

         

      }else{

         $DNID_SUB_QUERY = "AND 0 = (SELECT count(dnidprefix) FROM cc_tariffplan RIGHT JOIN cc_tariffgroup_plan ON cc_tariffgroup_plan.idtariffgroup=$tariffgroupid WHERE dnidprefix=SUBSTRING('$mydnid',1,length(dnidprefix)) ) ";

         

         $CID_SUB_QUERY = "AND 0 = (SELECT count(calleridprefix) FROM cc_tariffplan RIGHT JOIN cc_tariffgroup_plan ON cc_tariffgroup_plan.idtariffgroup=$tariffgroupid WHERE calleridprefix=SUBSTRING('$mycallerid',1,length(calleridprefix)) ) ";

      }

      

      $QUERY = "SELECT tariffgroupname, lcrtype, idtariffgroup, cc_tariffgroup_plan.idtariffplan, tariffname, destination,

      

      cc_ratecard.id,   dialprefix, destination, buyrate, buyrateinitblock, buyrateincrement, rateinitial, initblock, billingblock,

      connectcharge, disconnectcharge, stepchargea, chargea, timechargea, billingblocka, stepchargeb, chargeb,

      timechargeb, billingblockb, stepchargec, chargec, timechargec, billingblockc,

      cc_tariffplan.id_trunk AS tp_id_trunk, tp_trunk.trunkprefix AS tp_trunk, tp_trunk.providertech AS tp_providertech,

      tp_trunk.providerip AS tp_providerip, tp_trunk.removeprefix AS tp_removeprefix,

      cc_ratecard.id_trunk AS rc_id_trunk, rt_trunk.trunkprefix AS rc_trunkprefix, rt_trunk.providertech AS rc_providertech,

      rt_trunk.providerip AS rc_providerip, rt_trunk.removeprefix AS rc_removeprefix,

      musiconhold,

      tp_trunk.failover_trunk AS tp_failover_trunk,

      rt_trunk.failover_trunk AS rt_failover_trunk,

      tp_trunk.addparameter AS tp_addparameter_trunk,

      rt_trunk.addparameter AS rt_addparameter_trunk,

      id_outbound_cidgroup,

      freetimetocall_package_offer, freetimetocall, packagetype, billingtype, startday, id_cc_package_offer



      

      FROM cc_tariffgroup

      RIGHT JOIN cc_tariffgroup_plan ON cc_tariffgroup.id=$tariffgroupid

      

      INNER JOIN cc_tariffplan ON (cc_tariffplan.id=cc_tariffgroup_plan.idtariffplan )

      

      LEFT JOIN cc_ratecard ON cc_ratecard.idtariffplan=cc_tariffplan.id

      LEFT JOIN cc_trunk AS rt_trunk ON cc_ratecard.id_trunk=rt_trunk.id_trunk

      LEFT JOIN cc_trunk AS tp_trunk ON cc_tariffplan.id_trunk=tp_trunk.id_trunk

      LEFT JOIN cc_package_offer ON cc_package_offer.id=cc_tariffgroup.id_cc_package_offer

      

      WHERE (dialprefix=SUBSTRING('$phonenumber',1,length(dialprefix)) OR dialprefix='defaultprefix')

      AND startingdate<= CURRENT_TIMESTAMP AND (expirationdate > CURRENT_TIMESTAMP OR expirationdate IS NULL OR LENGTH(expirationdate)<5)

      AND startdate<= CURRENT_TIMESTAMP AND (stopdate > CURRENT_TIMESTAMP OR stopdate IS NULL OR LENGTH(stopdate)<5)

      $sql_clause_days

      AND idtariffgroup='$tariffgroupid'

      AND ( dnidprefix=SUBSTRING('$mydnid',1,length(dnidprefix)) OR (dnidprefix='all' $DNID_SUB_QUERY))

      AND ( calleridprefix=SUBSTRING('$mycallerid',1,length(calleridprefix)) OR (calleridprefix='all' $CID_SUB_QUERY))

      ORDER BY LENGTH(dialprefix) DESC";

      

      //-- if ($this -> debug_st) echo $QUERY."\n\n";

      if ($this->webui) $A2B -> debug( WRITELOG, $agi, __FILE__, __LINE__, "[RATE ENGINE QUERY]\n".$QUERY."\n",0);   

      

      

      $A2B->instance_table = new Table();

      $result = $A2B->instance_table -> SQLExec ($A2B -> DBHandle, $QUERY);

      

      

      if (!is_array($result) || count($result)==0) return 0; // NO RATE FOR THIS NUMBER

   

      if ($this -> debug_st) echo "::> Count Total result ".count($result)."\n\n";

      if ($this->webui) $A2B -> debug( WRITELOG, $agi, __FILE__, __LINE__, "[rate-engine: Count Total result ".count($result)."]");   

      

      // CHECK IF THERE IS OTHER RATE THAT 'DEFAULT', IF YES REMOVE THE DEFAULT RATES

      // NOT NOT REMOVE SHIFT THEM TO THE END :P

      $ind_stop_default = -1;

      for ($i=0;$i<count($result);$i++){

         if ( $result[$i][7] != 'defaultprefix'){

            $ind_stop_default = $i;

            break;

         }   

      }

      // IMPORTANT TO CUT THE PART OF THE defaultprefix CAUSE WE WILL APPLY THE SORT ACCORDING TO THE RATE

      // DEFAULPERFIX IS AN ESCAPE IN CASE OF NO RATE IS DEFINED, NOT BE COUNT WITH OTHER DURING THE

      // SORT OF RATE

      if ($ind_stop_default>0) {

         $result_defaultprefix = array_slice ($result, 0, $ind_stop_default);

         $result = array_slice ($result, $ind_stop_default, count($result)-$ind_stop_default);

      }



      

      //2) TAKE THE VALUE OF LCTYPE

      //LCR : According to the buyer price   -0    buyrate [col 6]

      //LCD : According to the seller price   -1  rateinitial   [col 9]

      

      $LCtype = $result[0][1];



      // 3) REMOVE THOSE THAT USE THE SAME TRUNK - MAKE A DISTINCT

      $mylistoftrunk = array();

      for ($i=0;$i<count($result);$i++){

         

         if ($result[$i][34]==-1) $mylistoftrunk_next[]= $mycurrenttrunk = $result[$i][29];

         else $mylistoftrunk_next[]= $mycurrenttrunk = $result[$i][34];

         

         

         // Check if we already have the same trunk in the ratecard

         if ($i==0 || !in_array ($mycurrenttrunk , $mylistoftrunk)) {

            $distinct_result[] = $result[$i];         

         }   

         

         $mylistoftrunk[]= $mycurrenttrunk;            

      }   

      

      

      //if ($ind_stop_default>0)  array_rotate($result, $ind_stop_default);

      

      // Thanks for the fix from the wiki :D next time email me, lol

      if ($LCtype==0){

         //$result = $this -> array_csort($result,'6',SORT_ASC); GOTTYA!

         $distinct_result = $this -> array_csort($distinct_result,'9',SORT_ASC); //1

      }else{

         //$result = $this -> array_csort($result,'9',SORT_ASC); GOTTYA!

         $distinct_result = $this -> array_csort($distinct_result,'12',SORT_ASC); //1

      }

   

      

      // WE ADD THE DEFAULTPREFIX WE REMOVE BEFORE

      if ($ind_stop_default>0) {

         $result = array_merge ($result, $result_defaultprefix);

      }

   

      

   

      

      //4) $result[K][34]==-1 USE THE VALUES OF THE TRUNK OF $result[K][29]

      

      

      $this -> ratecard_obj = $distinct_result;

      $this -> number_trunk = count($distinct_result);

      

      // if an extracharge DID number was called increase rates with the extracharge fee

      if (strlen($A2B->dnid)>2 && is_array($A2B->agiconfig['extracharge_did']) && in_array($A2B->dnid, $A2B->agiconfig['extracharge_did']))

      {

         $fee=$A2B->agiconfig['extracharge_fee'][array_search($A2B->dnid, $A2B->agiconfig['extracharge_did'])];

         $A2B -> debug( WRITELOG, $agi, __FILE__, __LINE__, "[CC_asterisk_rate-engine: Extracharge DID found: ".$A2B->dnid.", extra fee: ".$fee."]");

         for ($i=0; $i<count($this->ratecard_obj); $i++)

         {

            $this->ratecard_obj[$i][9] +=$fee;

            $this->ratecard_obj[$i][12]+=$fee;

         }

      }

      

      if ($this -> debug_st)echo "::> Count Total result ".count($distinct_result)."\n\n";

      if ($this->webui) $A2B -> debug( WRITELOG, $agi, __FILE__, __LINE__, "[CC_asterisk_rate-engine: Count Total result ".count($distinct_result)."]");   

      if ($this->webui) $A2B -> debug( WRITELOG, $agi, __FILE__, __LINE__, "[CC_asterisk_rate-engine: number_trunk ".$this -> number_trunk."]");

      

      return 1;

   }


Top
 Profile  
 
 Post subject:
PostPosted: Sun Dec 28, 2008 5:00 pm 
Offline
Moderator
User avatar

Joined: Thu Jun 22, 2006 2:19 pm
Posts: 2890
Location: Devon, UK
I'm very pleased to see you contributing your changes, but could you please attach diffs against an enhancement ticket on trac; they are a lot more readable there, and much more difficult to forget about.

Additionally you can search for similar tickets before commencing work on your own patch. ;-)


Top
 Profile  
 
 Post subject:
PostPosted: Mon Dec 29, 2008 4:52 am 
Offline
User avatar

Joined: Mon Apr 30, 2007 6:43 am
Posts: 1060
Location: Canada
Hello Atamurad,

Are you sure this is what you want to do? IMHO, when a provider gives you prefixes and rates like this ...

9936 ---> $0.13
993 ---> $0.10

... this mean that although the shortest prefix is in theory valid for the same destination, the longest one should take precedence. This is for the trunk provider a way to tell you that this specific number should be treated with special care because it will cost you more to call 9936 no matter what.

For the sake of taking this example to the extreme, let's just imagine that there are some special prefixes for social date line or pay per use Tarot reading service that advertise high rates like 5$ per minute (it's the only example I could come up with). And let's assume that the prefix is 222, then will will have the following:

9936 ---> $0.13 ---> Mobiles
993 ---> $0.10 ---> Landline Phone or other
993222 ---> $5.00 ---> High rate special numbers (e.g. Tarot reading service)

I hope that that make it more obvious that the longer prefix (if defined) should alway have a higher priority. If some longer prefixes have not special meaning, then people should can save the hard disk storage space and avoid the headache by not using a longer prefix unless absolutely necessary.

In the example above, if you choose to send the call to "993222X." through the trunk that cost only $0.10, then you will need to come up with a way to compensate the revenue lost at a rate of $4.90 per minute.

Cheers


Top
 Profile  
 
 Post subject:
PostPosted: Mon Dec 29, 2008 5:20 am 
Offline

Joined: Sun Dec 28, 2008 4:34 pm
Posts: 5
Location: singapore
#248 is not same as what I solved.

Let me try to explain.
Imagine there are two different providers, ProviderA and ProviderB.

Provider A gives me the following rate table:
993 0.10

They charge 0.10 for every call that begins with 993. It's actually their problem if I call 99366xxx number and they paid 5$ but charged me 0.10$.

Provider B gives me different rates.
993 0.13
99366 10

Now i want to call 99366112233.

For this call, ProviderA will charge me 0.10 but ProviderB will charge 10$

A2B will route this call from ProviderB, and i pay 10$ per minute. My patch fixes this problem.

One more example:
Provider A:
993 0.01
99312 0.5
993125 0.25
Provider B:
993 0.02
99312 0.15

In this case, A2B will route the call to 993125001122 through ProvA - 0.25$. But ProvB would charge 0.15, much cheaper than ProvA.

Current a2b algorithm:
- Find the longest matching prefix
- Discard all rates where len < max_len
- Choose the best one

Corrected algorithm:
- For each provider X, find the longest prefix, let it be max_len[X]
- Discard all rates from provider X, where len < max_len[X]
- Choose the best one

So simple said, ProviderA's "993" rate should not be discarded just because of ProviderB charges differently for "99366" number.

I hope it is clear now.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Dec 29, 2008 5:23 am 
Offline
User avatar

Joined: Mon Apr 30, 2007 6:43 am
Posts: 1060
Location: Canada
You are completely right. I failed to keep in mind the idea of 2 separate providers.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Dec 29, 2008 11:48 am 
Offline
Moderator
User avatar

Joined: Thu Jun 22, 2006 2:19 pm
Posts: 2890
Location: Devon, UK
Without a diff it's very difficult to see what you changed. I just wasted 10 minutes manually diffing the code your posted. It seems the only change you made was to delete the section that removes shorter prefixes?
Code:
//1) REMOVE THOSE THAT HAVE A SMALLER DIALPREFIX
      $max_len_prefix = strlen($result[0][7]);
      for ($i=1;$i<count($result);$i++){
         if ( strlen($result[$i][7]) < $max_len_prefix) break;
      }
      $result = array_slice ($result, 0, $i);
I think you just broke the rate-engine. ;-)
The replacement code on ticket #248 looks to do the right thing. Why do you say #248 is solving a different problem? Surely you want to take the best match from each rate-card, rather than the simply the shortest prefix returned from all rate-cards?


Top
 Profile  
 
 Post subject:
PostPosted: Mon Dec 29, 2008 2:15 pm 
Offline

Joined: Sun Dec 28, 2008 4:34 pm
Posts: 5
Location: singapore
I'm using that code for a year in production.

I tried to get diff output with the latest version but it is much more uglier sorry. I modified older version long time ago, have no original file with me right now.

Steps in original a2b:
1) REMOVE THOSE THAT HAVE A SMALLER DIALPREFIX
2) TAKE THE VALUE OF LCTYPE + Sorting
3) REMOVE THOSE THAT USE THE SAME TRUNK - MAKE A DISTINCT

In my code, there is no step 1, and step 2 and step 3 changes order.
So it becomes:
1) REMOVE THOSE THAT USE THE SAME TRUNK - MAKE A DISTINCT
2) TAKE THE VALUE OF LCTYPE + Sorting

#248 uses one extra SQL Query and nested for loop with depth of 3!
Mine is even much more simpler/faster than original a2b.

If you doubt that it really works, i can go on.

At first, returned data set is sorted by LENGTH(dialprefix) DESC;
So it looks something like this:
Code:
prefix  | rate | trunkid
993125  | 0.25 | 1
99312   | 0.5  | 1
99312   | 0.15 | 2
993     | 0.01 | 1
993     | 0.02 | 2


1. Let's apply "MAKE A DISTINCT" code
Code:
prefix  | rate | trunkid
993125  | 0.25 | 1
99312   | 0.15 | 2


2. Now let's sort by rate
Code:
prefix  | rate | trunkid
99312   | 0.15 | 2
993125  | 0.25 | 1


That's all! :wink:

atamurad

Added after 2 minutes:


forgot to write, yeah #248 actually solved this problem, i got it wrong last time but as i said above, this one is much more clear/faster, i think.


Top
 Profile  
 
 Post subject:
PostPosted: Sat Jan 03, 2009 2:24 pm 
Offline

Joined: Sun Mar 12, 2006 2:49 pm
Posts: 954
Location: Barcelona
I am not very agree with this change.
I know that for a lot of users in our community for which it's important to have
a provider for landline and an other provider for Cellphone

Example :
Code:
prefix  | rate | trunkid
99312   | 0.15 | 2
993     | 0.01 | 1


so if someone call 99312XXXXXXXX we dont want to use trunk ID 1


if we want this option, I think this might be a parameter on the CallPlan to define the rule.
Thoughts ?

Please provide also a diff when you come up with code change, it will help everybody to understand what need to be changed.

Yours,
/Areski


Top
 Profile  
 
 Post subject:
PostPosted: Mon Feb 16, 2009 9:04 am 
Offline

Joined: Fri Jul 11, 2008 6:54 pm
Posts: 39
Location: Armenia, Yerevan
I think so one must have the A2Billing, but its come to some problems also.
For example
We have 2 VoIP SP A and B.
A and B provide a mobile and landline.
but we dont want make connection via B to mobile, and we dont import the mobile rate and code for B.
And when we use this patch or some one like this, we will connect to B landline route with landline price.
I have say before also, in development forum about this.
Curently I use so patch, but I am make high price for B mobile routes.
In this case I say, we need one filed, named route_priority, he can have the next value:
EXL, OFF, 0..9, whe EXL - is mean only this route, OFF - this route is disable, dont use this, and dont find any routes with low price as this.
and the 0 up to 9 - is priority, 0 - low, 0 - high

Thanks


areski wrote:
I am not very agree with this change.
I know that for a lot of users in our community for which it's important to have
a provider for landline and an other provider for Cellphone

Example :
Code:
prefix  | rate | trunkid
99312   | 0.15 | 2
993     | 0.01 | 1


so if someone call 99312XXXXXXXX we dont want to use trunk ID 1


if we want this option, I think this might be a parameter on the CallPlan to define the rule.
Thoughts ?

Please provide also a diff when you come up with code change, it will help everybody to understand what need to be changed.

Yours,
/Areski


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 9 posts ] 
Voice Broadcast System


All times are UTC


Who is online

Users browsing this forum: No registered users and 2 guests


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