Hello, For those interested, I added a piece to mod lcr wiki. It makes sorting / routing logic better than default logic: http://wiki.freeswitch.org/wiki/Mod_lcr#Custom_SQL_with_sub-query_-_for_real... Why it helps: Rates rates can often be given both on per-NPA or per-NPANXX level depending on the carrier and on the NPA. Moreover, some carriers may have NPANXX rate lower than the corresponding NPA rate, while others will have it inverse. Neither simple ORDER BY rate, prefix; nor ORDER BY prefix, rate; give the truly cheapest route. The LCR logic should be two-step process to accommodate this. Cheers, -Victor
On 12/12/2013 02:42 PM, Victor Chukalovskiy wrote:
Hello,
For those interested, I added a piece to mod lcr wiki. It makes sorting / routing logic better than default logic:
http://wiki.freeswitch.org/wiki/Mod_lcr#Custom_SQL_with_sub-query_-_for_real...
Why it helps:
Rates rates can often be given both on per-NPA or per-NPANXX level depending on the carrier and on the NPA. Moreover, some carriers may have NPANXX rate lower than the corresponding NPA rate, while others will have it inverse. Neither simple ORDER BY rate, prefix; nor ORDER BY prefix, rate; give the truly cheapest route. The LCR logic should be two-step process to accommodate this.
Cheers, -Victor Unless I misunderstand what you're saying, this is what the reorder_by_rate param does.
You'll always want to pick the longest digit match per carrier. Then you probably want to grab the cheapest overall rate of the matches you got back. -Ray
I agree. This definitely sounds like he simply needed the "reorder_by_rate" param. It will reorder the initial sql results strictly by rate: reorder_by_rate - Forces the LCR module to re-order the query strictly on rate basis. By default this is turned off, but enabling this will always prefer rate over anything else. Beware this may have an adverse effect! I initially had this turned on then quickly realized it would sometimes try to route ALL calls by the lowest rate found. Ex: flowroute lists all calls for the US with a default NPA of "1" at .0098. Now a user trying to call Jamaica with "1876" at a rate of 0.19 (or whatever) will get both flowroute rates returned. The reorder by rate will assume 0.0098 is a valid rate since it will now be the "cheapest" in the list and send the call along its way to flowroute, whom will now bill you at 0.19. Now if you have another carrier in your list with 1876 at 0.15 you can see why this would be a problem. In this case you will always want the longest matched NPANXX rate. Date: Fri, 13 Dec 2013 15:58:54 -0500 From: intralanman@freeswitch.org To: freeswitch-users@lists.freeswitch.org Subject: Re: [Freeswitch-users] Mod LCR SQL sub-query for better routing On 12/12/2013 02:42 PM, Victor Chukalovskiy wrote: Hello, For those interested, I added a piece to mod lcr wiki. It makes sorting / routing logic better than default logic: http://wiki.freeswitch.org/wiki/Mod_lcr#Custom_SQL_with_sub-query_-_for_real... Why it helps: Rates rates can often be given both on per-NPA or per-NPANXX level depending on the carrier and on the NPA. Moreover, some carriers may have NPANXX rate lower than the corresponding NPA rate, while others will have it inverse. Neither simple ORDER BY rate, prefix; nor ORDER BY prefix, rate; give the truly cheapest route. The LCR logic should be two-step process to accommodate this. Cheers, -Victor Unless I misunderstand what you're saying, this is what the reorder_by_rate param does. You'll always want to pick the longest digit match per carrier. Then you probably want to grab the cheapest overall rate of the matches you got back. -Ray _________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org
You should never simply re-order just by rate... When you select the rate for the route you _must_ select the longest prefix... You can just fall back to 1 since it matches 1876 also... This is not valid just for non-US areas in NANPA but for globally... Its not uncommon to see something like +44 @ 0.0095 (UK London Landline) then see something like +447938 @ 0.0150 (O2 Mobile) if you tried to route this off the cheapest prefix you would always be doing it wrong... And don¹t even get started with ignoring things like LRN in the US with a ratedeck that has per NPA-NXX(-X) pricing while respecting LRN On 12/16/13 5:36 PM, "DP ." <hi-tecc@hotmail.com> wrote:
I agree. This definitely sounds like he simply needed the "reorder_by_rate" param. It will reorder the initial sql results strictly by rate:
* reorder_by_rate - Forces the LCR module to re-order the query strictly on rate basis. By default this is turned off, but enabling this will always prefer rate over anything else.
Beware this may have an adverse effect! I initially had this turned on then quickly realized it would sometimes try to route ALL calls by the lowest rate found.
Ex: flowroute lists all calls for the US with a default NPA of "1" at .0098. Now a user trying to call Jamaica with "1876" at a rate of 0.19 (or whatever) will get both flowroute rates returned. The reorder by rate will assume 0.0098 is a valid rate since it will now be the "cheapest" in the list and send the call along its way to flowroute, whom will now bill you at 0.19. Now if you have another carrier in your list with 1876 at 0.15 you can see why this would be a problem.
In this case you will always want the longest matched NPANXX rate.
Date: Fri, 13 Dec 2013 15:58:54 -0500 From: intralanman@freeswitch.org To: freeswitch-users@lists.freeswitch.org Subject: Re: [Freeswitch-users] Mod LCR SQL sub-query for better routing
On 12/12/2013 02:42 PM, Victor Chukalovskiy wrote:
Hello,
For those interested, I added a piece to mod lcr wiki. It makes sorting / routing logic better than default logic:
http://wiki.freeswitch.org/wiki/Mod_lcr#Custom_SQL_with_sub-query_-_for_real... life_ratesheet_complexities
Why it helps:
Rates rates can often be given both on per-NPA or per-NPANXX level depending on the carrier and on the NPA. Moreover, some carriers may have NPANXX rate lower than the corresponding NPA rate, while others will have it inverse. Neither simple ORDER BY rate, prefix; nor ORDER BY prefix, rate; give the truly cheapest route. The LCR logic should be two-step process to accommodate this.
Cheers, -Victor
Unless I misunderstand what you're saying, this is what the reorder_by_rate param does.
You'll always want to pick the longest digit match per carrier. Then you probably want to grab the cheapest overall rate of the matches you got back.
-Ray
_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org
_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com
FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com
Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com
FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org
-- Ken http://www.FreeSWITCH.org http://www.ClueCon.com http://www.OSTAG.org irc.freenode.net #freeswitch Twitter: @FreeSWITCH
Greetings! I was away for a couple days, let me clarify now: -I did not use default mod_lcr params for a while, so I don't know what exactly reorder_by_rate does. -I use custom_sql, and so I ran into the issues that single SELECT...FROM...WHERE...JOIN...ORDER BY query shown in the WiKi is not working well. Seems to be the same problem that Ken and DP mentioned: one needs to select only longest prefix for each carrier, and then order the results by rate. Below is my example: Prefix Rate Carrier 1718 0.003 Carrier 1 1 0.001 Carrier 1 1718 0.002 Carrier 2 1416 0.004 Carrier 2 ORDER BY prefix, rate will give Carrier 2 for dest 1416XXXXXXX, while the best route is Carrier 1 - Bad ORDER BY rate, prefix will give Carrier 1 for dest 1718XXXXXXX, while the best route is Carrier 2 - Also bad That's why I did the sub-query I showed in the WiKi. Is this problem already solved with mod_lcr and custom SQL? Did not see any mention of the solution. On 13-12-16 07:02 PM, Ken Rice wrote:
Re: [Freeswitch-users] Mod LCR SQL sub-query for better routing You should never simply re-order just by rate... When you select the rate for the route you _must_select the longest prefix... You can just fall back to 1 since it matches 1876 also... This is not valid just for non-US areas in NANPA but for globally... Its not uncommon to see something like +44 @ 0.0095 (UK London Landline) then see something like +447938 @ 0.0150 (O2 Mobile) if you tried to route this off the cheapest prefix you would always be doing it wrong...
And don't even get started with ignoring things like LRN in the US with a ratedeck that has per NPA-NXX(-X) pricing while respecting LRN
On 12/16/13 5:36 PM, "DP ." <hi-tecc@hotmail.com> wrote:
I agree. This definitely sounds like he simply needed the "reorder_by_rate" param. It will reorder the initial sql results strictly by rate:
* reorder_by_rate - Forces the LCR module to re-order the query strictly on rate basis. By default this is turned off, but enabling this will always prefer rate over anything else.
Beware this may have an adverse effect! I initially had this turned on then quickly realized it would sometimes try to route ALL calls by the lowest rate found.
Ex: flowroute lists all calls for the US with a default NPA of "1" at .0098. Now a user trying to call Jamaica with "1876" at a rate of 0.19 (or whatever) will get both flowroute rates returned. The reorder by rate will assume 0.0098 is a valid rate since it will now be the "cheapest" in the list and send the call along its way to flowroute, whom will now bill you at 0.19. Now if you have another carrier in your list with 1876 at 0.15 you can see why this would be a problem.
In this case you will always want the longest matched NPANXX rate.
------------------------------------------------------------------------ Date: Fri, 13 Dec 2013 15:58:54 -0500 From: intralanman@freeswitch.org To: freeswitch-users@lists.freeswitch.org Subject: Re: [Freeswitch-users] Mod LCR SQL sub-query for better routing
On 12/12/2013 02:42 PM, Victor Chukalovskiy wrote:
Hello,
For those interested, I added a piece to mod lcr wiki. It makes sorting / routing logic better than default logic:
http://wiki.freeswitch.org/wiki/Mod_lcr#Custom_SQL_with_sub-query_-_for_real...
Why it helps:
Rates rates can often be given both on per-NPA or per-NPANXX level depending on the carrier and on the NPA. Moreover, some carriers may have NPANXX rate lower than the corresponding NPA rate, while others will have it inverse. Neither simple ORDER BY rate, prefix; nor ORDER BY prefix, rate; give the truly cheapest route. The LCR logic should be two-step process to accommodate this.
Cheers, -Victor
Unless I misunderstand what you're saying, this is what the reorder_by_rate param does.
You'll always want to pick the longest digit match per carrier. Then you probably want to grab the cheapest overall rate of the matches you got back.
-Ray
_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org
------------------------------------------------------------------------ _________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com
FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com
Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com
FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org
-- Ken _http://www.FreeSWITCH.org http://www.ClueCon.com http://www.OSTAG.org _irc.freenode.net #freeswitch Twitter: @FreeSWITCH
_________________________________________________________________________ Professional FreeSWITCH Consulting Services: consulting@freeswitch.org http://www.freeswitchsolutions.com
FreeSWITCH-powered IP PBX: The CudaTel Communication Server http://www.cudatel.com
Official FreeSWITCH Sites http://www.freeswitch.org http://wiki.freeswitch.org http://www.cluecon.com
FreeSWITCH-users mailing list FreeSWITCH-users@lists.freeswitch.org http://lists.freeswitch.org/mailman/listinfo/freeswitch-users UNSUBSCRIBE:http://lists.freeswitch.org/mailman/options/freeswitch-users http://www.freeswitch.org
participants (4)
-
DP .
-
Ken Rice
-
Raymond Chandler
-
Victor Chukalovskiy