Support A2Billing :

provided by Star2Billing S.L.

Support A2Billing :
It is currently Tue Mar 19, 2024 4:58 am
VoIP Billing solution


All times are UTC




Post new topic Reply to topic  [ 27 posts ]  Go to page 1, 2  Next
Author Message
 Post subject: incorrect LCR behavior
PostPosted: Wed Nov 11, 2009 1:55 pm 
Offline

Joined: Sat Aug 15, 2009 4:25 am
Posts: 35
In my configuration, A2Billing isn't finding the Least Cost Route. It is finding the most specific route - even across two Ratecards.

Across providers with different length prefix's, the least cost route is not being chosen.

EXAMPLE:

provider 1 (in ratecard 01) provides 7 digit rate tables. For Memphis TN

1901201 0.00430

provider 2 (in ratecard 02) provides 8 digit rate tables. For Memphis TN it looks like this

19012010 0.00330
19012011 0.00330
19012012 0.00490
...
19012019 0.00330

A caller calls 1 901 201 1212. Across these two providers, provider 2 has the least cost route at 0.00330. A2Billing does this properly but only by coincidence.

A caller calls 1 901 201 2121. Across these two providers, provider 1 has the least cost route at 0.00430. A2Billing does NOT do this properly. It assumes that provider 2, with a better match for digits is the better route.

I can understand ( and would require! ) A2Billing to use the most specific rate in a provider (or ratecard in this case) as the shorter ones are usually the landline rates, and the more specific ones are for more expensive destinations, but across multiple providers it should certainly not behave this way.

Another, more common example is this.

You have a provider with an excellent rate to a specific rate center - say it's in the US. So you have

Provider A - 1313 - $0.0080
and
Provider B - 1 - $0.0098 for the rest of the country.

Then, you get a new provider for the rest of the country that is cheaper even than Provider A.

Provider C - 1 - $0.075

In A2Billing, provider C will take precedence over Provider B for most of the country, but it someone dials 13135551212, they will get Provider A which is not the least cost route anymore.

CONCLUSION/SUGGESTION:
I'm not a developer, but it seems to be that the LCR engine needs to first fine the cheapest rate in the related callplan for every provider (or ratecard), and then use the provider that is the least expensive.

EXAMPLE1:
Caller X uses Callplan Y. They dial 19012012121

A2Billing looks up that Caller X uses Callplan Y.
A2Billing looks up that Callplan Y uses Ratecards 01 and 02.
A2Billing looks up via LCR that 19012012 is $0.00490 in Ratecard 01
A2Billing looks up via LCR that 1901202 is $0.00430 in Ratecard 02
A2Billing decides using LCRthat it should use the Trunk from Ratecard 02.


Thoughts? Questions/comments? Am I doing something wrong in A2Billing? Must I create 8 digit ratecards for providers that only give me 7 digits (i hope not - such overhead - That's 1.4 million records per US carrier!)

Thanks!
Andy


Last edited by andyml on Thu Nov 12, 2009 3:35 pm, edited 1 time in total.

Top
 Profile  
 
 Post subject: Re: incorrect LCR behavior
PostPosted: Wed Nov 11, 2009 6:55 pm 
Offline

Joined: Thu May 29, 2008 9:07 pm
Posts: 72
I wrote callblast2.0, and the way mine finds rates is apparently how A2B does it.
I knew of this bug when I wrote mine.

Code:
'SELECT rates.rate FROM trunks,rates WHERE trunks.active="Yes" AND rates.destination = left('.$a["$i"]["number"].', length(rates.destination)) ORDER BY destination DESC LIMIT 1'


Thats my code. If areski or someone else comes up with a solution to his problem please post the mysql query here, I could use it too.

Thanks
-Matthew

ps...it is choosing the longest destination match by 'ORDER BY destination DESC LIMIT 1'
I thought a more exact match would be better, but i guess if i did 'ORDER BY rate ASC LIMIT 1', that would fix this problem for mine...maybe that will help areski...or you if you want to modify your own code.


Top
 Profile  
 
 Post subject: Re: incorrect LCR behavior
PostPosted: Wed Nov 11, 2009 7:02 pm 
Offline

Joined: Sat Aug 15, 2009 4:25 am
Posts: 35
I think I'm hot on the trail of the SQL that would solve the problem but I haven't a clue how to implement it. If I put the query up here, will someone help me figure out where to put it in the code?

Also, and again I'm no developer... would someone else, or perhaps that same person help me run a comparative performance test? I'd hate to fix this problem and cause the query to take 3 times as long to run...


Top
 Profile  
 
 Post subject: Re: incorrect LCR behavior
PostPosted: Wed Nov 11, 2009 7:31 pm 
Offline

Joined: Thu May 29, 2008 9:07 pm
Posts: 72
andyml wrote:
I think I'm hot on the trail of the SQL that would solve the problem but I haven't a clue how to implement it. If I put the query up here, will someone help me figure out where to put it in the code?

Also, and again I'm no developer... would someone else, or perhaps that same person help me run a comparative performance test? I'd hate to fix this problem and cause the query to take 3 times as long to run...


Post the current query...ill take a look at it at least, ive never looked at any A2B code so...


Top
 Profile  
 
 Post subject: Re: incorrect LCR behavior
PostPosted: Wed Nov 11, 2009 7:42 pm 
Offline

Joined: Sat Aug 15, 2009 4:25 am
Posts: 35
Well that's just it - neither have I. I just dug around trying to find it to no avail.

What I'm writing is a straight SQL query against my db directly, hoping someone can take that and implement it in the LCR engine in A2Billing.


Top
 Profile  
 
 Post subject: Re: incorrect LCR behavior
PostPosted: Wed Nov 11, 2009 9:32 pm 
Offline

Joined: Sat Aug 15, 2009 4:25 am
Posts: 35
Ok - this works properly, and returns the cheapest route from each rate sorted by cost. If you did DESC LIMIT 1 on the whole thing, you'd get the least cost of all ratecards in the callplan, regardless of the number of digits in the match. Now 'we' just need someone totally awesome (areski?? :) ) to implement it properly in Class.RateEngine.php, which I can't seem to understand despite my best efforts.

Code:
mysql> SELECT * FROM (
    ->
    -> (SELECT id, id_trunk, destination, dialprefix, buyrate, rateinitial
    -> FROM cc_ratecard
    -> WHERE id_trunk = '4'
    -> AND (substr('19012012121',1,12) = dialprefix
    -> OR substr('19012012121',1,11) = dialprefix
    -> OR substr('19012012121',1,10) = dialprefix
    -> OR substr('19012012121',1,9) = dialprefix
    -> OR substr('19012012121',1,8) = dialprefix
    -> OR substr('19012012121',1,7) = dialprefix
    -> OR substr('19012012121',1,6) = dialprefix
    -> OR substr('19012012121',1,5) = dialprefix
    -> OR substr('19012012121',1,4) = dialprefix
    -> OR substr('19012012121',1,3) = dialprefix
    -> OR substr('19012012121',1,2) = dialprefix
    -> OR substr('19012012121',1,1) = dialprefix)
    -> ORDER BY length(dialprefix) DESC LIMIT 1)
    -> UNION
    -> (SELECT id, id_trunk, destination, dialprefix, buyrate, rateinitial
    -> FROM cc_ratecard
    -> WHERE id_trunk = '5'
    -> AND (substr('19012012121',1,12) = dialprefix
    -> OR substr('19012012121',1,11) = dialprefix
    -> OR substr('19012012121',1,10) = dialprefix
    -> OR substr('19012012121',1,9) = dialprefix
    -> OR substr('19012012121',1,8) = dialprefix
    -> OR substr('19012012121',1,7) = dialprefix
    -> OR substr('19012012121',1,6) = dialprefix
    -> OR substr('19012012121',1,5) = dialprefix
    -> OR substr('19012012121',1,4) = dialprefix
    -> OR substr('19012012121',1,3) = dialprefix
    -> OR substr('19012012121',1,2) = dialprefix
    -> OR substr('19012012121',1,1) = dialprefix)
    -> ORDER BY length(dialprefix) DESC LIMIT 1)
    ->
    -> ) AS `cc_ratecard` ORDER BY buyrate;
+---------+----------+-------------+------------+---------+-------------+
| id      | id_trunk | destination | dialprefix | buyrate | rateinitial |
+---------+----------+-------------+------------+---------+-------------+
| 1634812 |        5 |     1901201 | 1901201    | 0.00430 |     0.00761 |
| 1130411 |        4 |    19012012 | 19012012   | 0.00490 |     0.00760 |
+---------+----------+-------------+------------+---------+-------------+
2 rows in set (0.13 sec)


Top
 Profile  
 
 Post subject: Re: incorrect LCR behavior
PostPosted: Wed Nov 11, 2009 9:39 pm 
Offline

Joined: Sat Aug 15, 2009 4:25 am
Posts: 35
I believe this is the current query in Class.RateEngine.php

Code:
// $prefixclause to allow good DB servers to use an index rather than sequential scan
        // justification at http://forum.asterisk2billing.org/viewtopic.php?p=9620#9620
        $max_len_prefix = min(strlen($phonenumber), 15);    // don't match more than 15 digits (the most I have on my side is 8 digit prefixes)
        $prefixclause = '(';
        while ($max_len_prefix > 0 ) {
            $prefixclause .= "dialprefix='".substr($phonenumber,0,$max_len_prefix)."' OR ";
            $max_len_prefix--;
        }
        $prefixclause .= "dialprefix='defaultprefix')";

        // match Asterisk/POSIX regex prefixes,  rewrite the Asterisk '_XZN.' characters to
        // POSIX equivalents, and test each of them against the dialed number
        $prefixclause .= " OR (dialprefix LIKE '&_%' ESCAPE '&' AND '$phonenumber' ";
        $prefixclause .= "REGEXP REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CONCAT('^', dialprefix, '$'), ";
        $prefixclause .= "'X', '[0-9]'), 'Z', '[1-9]'), 'N', '[2-9]'), '.', '.+'), '_', ''))";

        // select group by 5 ... more easy to count
        $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,
        id_cc_package_offer,tp_trunk.status, rt_trunk.status, tp_trunk.inuse, rt_trunk.inuse,
        tp_trunk.maxuse,  rt_trunk.maxuse,tp_trunk.if_max_use, rt_trunk.if_max_use,cc_ratecard.rounding_calltime AS rounding_calltime,
        cc_ratecard.rounding_threshold AS rounding_threshold,cc_ratecard.additional_block_charge AS additional_block_charge,cc_ratecard.additional_block_charge_time AS additional_block_charge_time, cc_ratecard.additional_grace AS additional_grace, cc_ratecard.minimal_cost AS minimal_cost,disconnectcharge_after,announce_time_correction

        FROM cc_tariffgroup
        RIGHT JOIN cc_tariffgroup_plan ON cc_tariffgroup_plan.idtariffgroup=cc_tariffgroup.id
        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

        WHERE cc_tariffgroup.id=$tariffgroupid AND ($prefixclause)
        AND startingdate<= CURRENT_TIMESTAMP AND (expirationdate > CURRENT_TIMESTAMP OR expirationdate IS NULL)
        AND startdate<= CURRENT_TIMESTAMP AND (stopdate > CURRENT_TIMESTAMP OR stopdate IS NULL)
        $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";


Top
 Profile  
 
 Post subject: Re: incorrect LCR behavior
PostPosted: Thu Nov 12, 2009 7:46 am 
Offline

Joined: Fri Jun 23, 2006 3:56 pm
Posts: 4065
Hi

I'm not wholly comfortable with this - lets take a worked example.

In the UK, looking at say mobile numbers.

A mobile is 4474 to 4479
An expensive personal number is 447

Many carriers, particularly ones foreign to the UK regard 447 as UK mobile - when it is not. Also a mistake common in rate cards is to just put 44 - or the country code.

So if I have, in various rate tables:-

447[4-9] = 10p
447 = 9p (because the foriegn carrier has got it wrong and I should be charging 50p
44 = 1p

and the customer dials 4470XXXXXXXX

Your solution would automatically select 44 as the rate to use, and charges them 1p, when I should have charged them 50p and made more markup.




However, in most cases - but not all, the customer gets charged the same rate, whichever carrier we decide to put the calls through.

So I can see a case being made for separating the carriers rates from the customer's rates.


From the customer perspective, you know about the difference between 4474-79 and 4470, so you would charge the customer 50p irrespective of the cost and the carrier used, and you would endeavour to send the call via the cheapest carrier.

So the idea is that you upload the rates with the carrier's dial codes - because if they have told you that 447 costs 10p and you don't have a rate from them for 4470, and you go and send a few thousand calls in the direction of a 4470 number which should cost 50p but actually cost 10p, you can argue that with the carrier if they try and charge you later.

Then you upload the customer's rate table, as a separate entity.

The system would then work out which rate to apply to the customer, on the basis of the longest match, and then chooses which carriers provide that destination, and chose the carrier to use on the longest match per carrier table, with the cheapest rate across all the rate tables.

This of course does not actually check to see if the carrier delivers to the number, which would need some thought. E.G. just because a carrier publishes 447 as the rate to UK mobile, it does not necessarily deliver the call to 4470 - so you would have to send the call anyway, and then fail it over to the other possibilities if it failed, and better - after a certain number of concurrent failures, update the system so that it knows that this particular carrier fails on calls to 4470, and do all this, without increasing PDD.


In order to achieve this in A2Billing 1.4, now, it would take weeks of work and money to throw the entire rating system up in the air and start again, and test it thoroughly and make sure it is accurate, as well as all the unexpected issues you come across when you do something like this. So it is unlikely to happen in the 1.4 version without either some sponsorship, or community effort from some talented programmers who know telecoms.

It would be nice to take your carrier rates, with dial codes, and just upload them every month, create your customer rate table, and upload that separately.

For the moment, doing the work on the rates before hand in a tool such as MS Access is the way to do this - even after 10 years of creating rate tables, I find it's still a long and painful job, but has to be done properly.

Joe


Top
 Profile  
 
 Post subject: Re: incorrect LCR behavior
PostPosted: Thu Nov 12, 2009 1:21 pm 
Offline

Joined: Sat Aug 15, 2009 4:25 am
Posts: 35
Joe,

I really appreciate your thoughts, and your keying in on this issue. I apologize for the length of this post but before I get into it, I just want to point out to the community how immensely helpful Joe is and what a vast font of knowledge we have in him. I certainly would not be where I am today with A2Billing or Asterisk without Joe Roper. Truly.

Joe - My opinion is that you might be over-thinking it just a bit, and in doing so I think you've made a mistake. Please forgive me as I try to make my point below if I get in any way sharp. I present this argument with much respect.

In the states, when you request a rate-table from a carrier and get an 8 digit rate table from a carrier, (1 NPA NXX X) they are giving you a solemn vow. An 8 digit table (refered to as a 7 digit table here, since no one would include the 1*) has ~1.4 million rows - it is incredibly specific. These rows aren't committed to willy-nilly. If 1 901 201 1XXX costs less than 1 901 201 2XXX, there's a reason for that - some CLEC somewhere has that 2XXX block and charges a little more for access etc, and that carrier knows about it and wants their margin intact.

Likewise, bigger carriers (read really big - Verizon, Level3 etc) might only give you a 7 digit rate table, (or an 1 NPA NXX, refereed to as a 6 digit table here*). Their margins are better because they might run the infrastructure in the area or have better deals because they're a huge monster, for example. They don't need to mark up the 2XXX anymore than the 1XXX so they don't. Their rates to the area are likely better than the other carrier that needed to mark that one area up, so processing the call through them is an important thing.

I would argue that saying A2Billing needs to compensate for Carriers that have bad rate tables is very dangerous ground. For one, how can you know it is compensating properly? What is the point of LCR if you can't actually use it to route calls to their destinations based on cost?

I understand your example - I really do. That carrier of yours that wants 10p for a 50p route (if he were in the US anyway) needs to get burned, and as a result update their rate table. Meanwhile, you need to save 40p/min. That is Telecom - always has been. A2Billing can't be responsible for that. When they update their rate table and you load it up, your LCR engine will know about it too. Alternatively, if they pass the burn on to you, you can simply update that specific rate in your deck (either accurately, or just to disable it) so as not to go through that carrier - but in the case of a larger carrier, that less specific rate may legitimately be cheaper. How is A2Billing supposed to know which it is? One is a mistake, and the other is by design. My argument is that A2Billing should respect the one that is by design.

You're right though - this is not a minor change. I don't really expect someone to come along and say "Oh yeah, swap out line 223 of XYZ file with this line and it'll work" (though I'd welcome anyone to try!!) But the way rate-tables are written here, the correct way to determine the cost of a call from that carrier is to find the most specific rate from that carrier in their rate table. If it is less specific than another carrier's rate table, they're still saying that is the rate. Hell, it probably came out of the customer side of THEIR LCR engine.

The query I submitted above came from a commercial LCR engine the company I work from in my day job wrote. It is "how you do LCR in Telecom". I changed the table and column names for it to work against A2B's db. If your upstream reseller isn't being specific about a premium rate in their rate-deck, that is considered VERY bad form (and most certainly their fault) - bad enough to take legal action here in the states (if the table was up to date.) A2Billing can't be responsible for that.

Now - your other point - what if the carrier doesn't/won't route calls to those high-dollar areas? I've run into that here in the states. I have a very low cost International/A-Z provider that has an excellent international rates, and a good flat rate to the US - $0.0101. They are not in the US so they don't understand all the ins and outs, but they also don't care. What they've done is stopped routing to very rural areas where it costs them more than x. They only guarantee 50% delivery anyway, so if I want to take advantage their really good rates to anywhere, I have to be able to choose them based only on the 1 digit (country code of 1 for the US) AND I need to be prepared to fail over to a backup provider. The query above actually returns to you the two cheapest providers from the customer's callplan for that destination regardless of the number of digits that match so theoretically it could route the call to one, then the next on failure - BUT A2Billing already has a failover trunk, which is usually "good-enough". Lets leave failover aside though - I really just want to establish LCR.

You also referred to LCD - or LCR based on the customer's cost - which is certainly a different issue entirely. A lot of small-time provider (like me I'm sure) gain their market share by offering a flat rate to US destinations, knowing full well that there are 1.6 million destinations and that the cost is most certainly not flat. Actually - I'm sure you know this since FonicaTec offers their Penny service (not to say that I consider FonicaTec small-time). Offering a service like that is a wager that the traffic will be balanced with an emphasis on the 80% of destinations who's cost is far below $0.01, and much less emphasis on the 20% that are $0.02-$0.04/min. (If FonicaTec has a flat-rate provider that they resell for the Penny route, than it is that upstream provider making the wager but someone's doing it.+) In this case, if the small-time provider wants to increase his margins in those rural areas, he needs to fine an upstream provider that is local to those rural destinations and get their rate tables in his LCR engine.

+Any large provider offering a flat-rate will provide a guideline for 'normal' traffic that qualifies for their flat-rate. Take www.gafachi.com - these guys are a large scale us provider. They might even qualify as a proper carrier here, yet they offer a tiered flat-rate. http://www.gafachi.com/d/2341974/xUdpvYLsRuelydoa/1/0/prod/product/wholesale_voip_termination/ shows 100,000-1,999,999 min/month at $0.080, but below that is a http://www.gafachi.com/d/2341974/xUdpvYLsRuelydoa/2/0/prod/product/standard_usage_guidelines/ inside which you'll see that things are not so simple. It says that no more than 0.04% of calls can be to a "Tier 8" destination as defined by http://www.gafachi.com/sd/gafachi_tier_2009_10_28.zip, and that guy has 140,000 rows - a mere 6-digit table.

In this table 1 901 201 2121 (0.0049 and 0.0043 in my example above) is only 0.0057 above 100,000 min/month and so is 1 901 201 1212 - their margin (as you can see) is enough to cover the difference. I'll admit, we learned about this - not through the small print, but because our traffic didn't fit their guidelines and our projected costs didn't match our actual costs. We called gafachi support and they explained it to us. Welcome to Telecom they said.

I digress. Joe - bottom line. I understand your fears. I'm just more scared of the alternative. Building A2Billing to compensate for the potential mistakes in rate tables at the cost of properly supporting LCR seems to me a mistake - a mistake I am perfectly willing to help try to fix if my case is well stated enough to warrant a repair... I'm happy to provide whatever knowledge I have to get this 'right'. I'm no php dev - I'd be embarassed for areski to see my php scripts that I use instead of Access to build my 1.4 million row ratecards (64M!), then my bash scripts that use split to break them them up into the 65 999k csv's so that I can load them into A2Billing within their 1000k upload limit. That is per-provider. (You can see how being able to use a 140,000 row 6-digit ratetable could be something I'd want to pursue. Imagine if your cc_ratecard table had 4.2 million rows!)

Now I reiterate my apologies for length above and hope that people are willing to read my diatribe.


Top
 Profile  
 
 Post subject: Re: incorrect LCR behavior
PostPosted: Thu Nov 12, 2009 1:26 pm 
Offline

Joined: Sat Aug 15, 2009 4:25 am
Posts: 35
Also, per your example

I did a quick look in cc_ratecard for 447%. I have 852 rows as specific as 7 digits and one of my providers wants $3/min to your 4470* destinations. Boy do I want to avoid them!!


Top
 Profile  
 
 Post subject: Re: incorrect LCR behavior
PostPosted: Thu Nov 12, 2009 2:44 pm 
Offline

Joined: Fri Jun 23, 2006 3:56 pm
Posts: 4065
Hi

A well put together argument, and I think we are coming from the same angle. I did not necessarily agree that the way that A2Billing rates is perfect, but you can manipulate the core data before you import it to do what you want it to do, but it is less than elegant.

However a large number of our users and customers are new to telecoms, and some say "WHAT I need to find the dialcodes for all everywhere - but there are over 7000 of them ;-) and "What is a trunk, and why would I need one"

7,000 to 12,000 rows seem to be abut the usual for the smaller carriers. now I would hate anyone to be burned, because they downloaded A2Billing, and found themselves losing money hand over fist because they made what Telecoms people would regard to be an elementary mistake in their rate tables.

I think that separating the rate tables into the process of uploading the carrier rate tables, exactly as they have been delivered to you, followed by uploading your customer rate tables is simple for people to understand, should not leave them wide open to fraudulent activity, as well as making maintenance easier.

It maybe useful to agree on that architecture first, if we are to plan an overhaul.

Secondly, the methodology I outlined to you fits with your design methodology, unless I have misunderstood.

e.g:-

1. Discover the rate for the customer from the customer rate table.
2. Discover what trunks are available for that route by selecting the most exact match from each carrier rate table.
3. Chose the cheapest route from all the carriers.
4. Call ends, write CDR with Customer cost and exact carrier cost.

Yours

Joe


Top
 Profile  
 
 Post subject: Re: incorrect LCR behavior
PostPosted: Thu Nov 12, 2009 3:16 pm 
Offline

Joined: Sat Aug 15, 2009 4:25 am
Posts: 35
jroper wrote:

A well put together argument, and I think we are coming from the same angle. I did not necessarily agree that the way that A2Billing rates is perfect, but you can manipulate the core data before you import it to do what you want it to do, but it is less than elegant.

Thanks Joe. I'm glad we agree, at least fundementally.

Pre-processing the deck is less elegant to be sure, but with the size of the ratetables required for proper LCR in the US, it makes mysql almost insufficient for more than 1 or 2 carriers. I don't know the limits of mysql, but I presume a couple million rows makes the LCR query a difficult one.

jroper wrote:
However a large number of our users and customers are new to telecoms, and some say "WHAT I need to find the dialcodes for all everywhere - but there are over 7000 of them ;-) and "What is a trunk, and why would I need one"

7,000 to 12,000 rows seem to be abut the usual for the smaller carriers. now I would hate anyone to be burned, because they downloaded A2Billing, and found themselves losing money hand over fist because they made what Telecoms people would regard to be an elementary mistake in their rate tables.

I do agree here too. I hate to see people burned. That said, Asterisk+A2Billing doesn't necessarily mean "Now you're an ITSP!" without the knowledge necessary to implement it all, and I don't just mean install it (your tutorials make it so simply my mother can install it!) I wonder if the community might not benefit from a blog post or two about the ins and outs of telecom? It isn't the cake-walk for free money that so many people believe it is. But of course, you know this...

jroper wrote:
I think that separating the rate tables into the process of uploading the carrier rate tables, exactly as they have been delivered to you, followed by uploading your customer rate tables is simple for people to understand, should not leave them wide open to fraudulent activity, as well as making maintenance easier.

This is a novel idea. My concern would be that people might make the mistake you refer to in your example. 447* does not always mean UK mobile phones, and they should not assume that it does - 4470* will bite you in the arse if you do. Separating the buy and sell rates MIGHT make making that mistake a little easier. Of course, it could just as easily be optional! Even now, once your ratecards are loaded up, you can run an LCR on it, and get a sell ratetable. All we need to do is make that editable (I know, I know - not simple. still...) and you have almost what you're talking about. or at least exportable, and re-importable (I know, even harder.)
jroper wrote:
It maybe useful to agree on that architecture first, if we are to plan an overhaul.
I'm not completely convinced there needs to be a major overhaul, but lets talk about it below.
jroper wrote:
Secondly, the methodology I outlined to you fits with your design methodology, unless I have misunderstood.

e.g:-

1. Discover the rate for the customer from the customer rate table.
2. Discover what trunks are available for that route by selecting the most exact match from each carrier rate table.
3. Chose the cheapest route from all the carriers.
4. Call ends, write CDR with Customer cost and exact carrier cost.

Right on, though the customer's rate could just as easily be dependent on the buy rate (like in the current architecture) and this process could work in the same way.

The linch-pin of my proposal (finding the best match from each "provider/carrier" in the customer's callplan) is assuming that each ratecard corresponds to a provider/carrier. Nothing in A2Billing forces this, but if you set yourself up that way, the changes to the LCR query are almost negligible (for someone that can read the existing one!)

Really I'm suggesting (corresponding with your steps 2 and 3)

1. find the rate for the best digit match (with the most corresponding digits) PER provider.
2. determine which provider's BEST rate is the least of all.

Which I'm quite sure is exactly what you're suggesting, in which case - awesome. The piece that I find most important to note, is that changing the actual query itself, leaving all database schema and other pieces aside, makes this possible in the current version. I'm not saying we can't retool everything, but I think I'm saying I don't think we necessarily need to. I'm ALL FOR building a better way to get rate-tables into A2Billing but the way they're stored in the database is sufficient as far as I'm concerned.

In fact, the config database could have an extra entry - AdvancedLCR=true/false if that made anyone feel better. I have no interest in breaking everyone's a2billing installations! This theoretical "new" query can be turned on or off, but, it is the _right_ way to do LCR...


Top
 Profile  
 
 Post subject: Re: incorrect LCR behavior
PostPosted: Thu Nov 12, 2009 3:46 pm 
Offline

Joined: Fri Jun 23, 2006 3:56 pm
Posts: 4065
Quote:
(your tutorials make it so simply my mother can install it!)


Say that again - LOUDER

I've been accused of obfuscating the process!!!


Meanwhile, now I understand, your last post has clarified this, and I am now wholly comfortable with this.

The key is to find the best digit match in each rate table, then compare all the rates (one from each rate table in the call plan) and go with the cheapest. However, I would like to get Areski's view on this, as I am no programmer, and may have missed something.

Joe


Top
 Profile  
 
 Post subject: Re: incorrect LCR behavior
PostPosted: Thu Nov 12, 2009 4:02 pm 
Offline

Joined: Sat Aug 15, 2009 4:25 am
Posts: 35
JOE ROPER'S A2BILLING TUTORIALS MAKETHE PROCESS SO EASY MY MOM CAN DO IT! Just don't ask her about importing rate tables. Ugh!

Glad you get me. These concepts are far too complicated for normal conversation. Repetition is often required, so thanks for staying tuned in!

I'm not a developer either, though I have had to learn a fair bit of php just to get my rate-tables in order for import/export (Access/Excel won't handle enough rows!) I'm happy to be involved on any level necessary. Areski's LCR query is so flexible in function, I'm afraid to try to edit it even in my own environment - not understanding all the pieces.

Also - if a conversation or collaboration can be arranged, I'm happy to try to clarify any of the comments in the code. Areski's code is so clean - it'd be great to get the meaning of his comments cleared up. I believe I'd have a chance at contributing if I could understand some of them.


Top
 Profile  
 
 Post subject: Re: incorrect LCR behavior
PostPosted: Thu Nov 12, 2009 4:29 pm 
Offline

Joined: Fri Jun 23, 2006 3:56 pm
Posts: 4065
Hi

Changing the subject slightly, I'm surprised that MS Access will not handle the number of rows successfully. Have you looked at the method described here: http://trac.asterisk2billing.org/cgi-bi ... rtRatecard and using the concatenation formula to create the export file.

interestingly, I also note that my latest version of MS access will now connect directly to the A2Billing database, and I can edit directly - dangerous to do if you do not know what you are doing, but can help in certain circumstances.

Joe


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 27 posts ]  Go to page 1, 2  Next
Hosted Voice Broadcast


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