<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="moz-cite-prefix">Greetings! I was away for a couple
days, let me clarify now:<br>
<br>
-I did not use default mod_lcr params for a while, so I don't know
what exactly reorder_by_rate does.<br>
<br>
-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.<br>
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:<br>
Prefix Rate Carrier<br>
1718 0.003 Carrier 1<br>
1 0.001 Carrier 1<br>
1718 0.002 Carrier 2<br>
1416 0.004 Carrier 2<br>
<br>
ORDER BY prefix, rate will give Carrier 2 for dest 1416XXXXXXX,
while the best route is Carrier 1 - Bad<br>
ORDER BY rate, prefix will give Carrier 1 for dest 1718XXXXXXX,
while the best route is Carrier 2 - Also bad<br>
<br>
<br>
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.<br>
<br>
<br>
On 13-12-16 07:02 PM, Ken Rice wrote:<br>
</div>
<blockquote cite="mid:CED4F09C.72795%25krice@freeswitch.org"
type="cite">
<title>Re: [Freeswitch-users] Mod LCR SQL sub-query for better
routing</title>
<font face="Monaco, Courier New"><span style="font-size:11pt">You
should never simply re-order just by rate... When you select
the rate for the route you _must_<span style="font-size:14px">
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...<br>
<br>
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<br>
<span style="font-size:11pt"><br>
<br>
On 12/16/13 5:36 PM, "DP ." <<a moz-do-not-send="true"
href="hi-tecc@hotmail.com">hi-tecc@hotmail.com</a>>
wrote:<br>
<br>
</span></span></span></font>
<blockquote><span style="font-size:11pt"><span
style="font-size:14px"><font face="Calibri, Verdana,
Helvetica, Arial"><span style="font-size:12pt">I agree.
This definitely sounds like he simply needed the
"reorder_by_rate" param. It will reorder the initial sql
results strictly by rate: <br>
<br>
</span></font></span></span>
<ul>
<li><span style="font-size:11pt"><span style="font-size:14px"><font
face="Calibri, Verdana, Helvetica, Arial"><font
size="2"><span style="font-size:10pt">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.<br>
</span></font></font></span></span></li>
</ul>
<span style="font-size:11pt"><span style="font-size:14px"><font
face="Calibri, Verdana, Helvetica, Arial"><span
style="font-size:12pt"><br>
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. <br>
<br>
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.<br>
<br>
In this case you will always want the longest matched
NPANXX rate. <br>
<br>
<hr align="CENTER" size="3" width="100%">Date: Fri, 13
Dec 2013 15:58:54 -0500<br>
From: <a moz-do-not-send="true"
href="intralanman@freeswitch.org">intralanman@freeswitch.org</a><br>
To: <a moz-do-not-send="true"
href="freeswitch-users@lists.freeswitch.org">freeswitch-users@lists.freeswitch.org</a><br>
Subject: Re: [Freeswitch-users] Mod LCR SQL sub-query
for better routing<br>
<br>
<br>
On 12/12/2013 02:42 PM, Victor Chukalovskiy wrote:<br>
<br>
<br>
</span></font></span></span>
<blockquote><span style="font-size:11pt"><span
style="font-size:14px"><font face="Calibri, Verdana,
Helvetica, Arial"><span style="font-size:12pt"> <br>
Hello,<br>
<br>
For those interested, I added a piece to mod lcr wiki.
It makes sorting <br>
/ routing logic better than default logic:<br>
<br>
<a moz-do-not-send="true"
href="http://wiki.freeswitch.org/wiki/Mod_lcr#Custom_SQL_with_sub-query_-_for_real-life_ratesheet_complexities">http://wiki.freeswitch.org/wiki/Mod_lcr#Custom_SQL_with_sub-query_-_for_real-life_ratesheet_complexities</a><br>
<br>
Why it helps:<br>
<br>
Rates rates can often be given both on per-NPA or
per-NPANXX level <br>
depending on the carrier and on the NPA. Moreover,
some carriers may <br>
have NPANXX rate lower than the corresponding NPA
rate, while others <br>
will have it inverse. Neither simple ORDER BY rate,
prefix; nor ORDER BY <br>
prefix, rate; give the truly cheapest route. The LCR
logic should be <br>
two-step process to accommodate this.<br>
<br>
Cheers,<br>
-Victor<br>
<br>
</span></font></span></span></blockquote>
<span style="font-size:11pt"><span style="font-size:14px"><font
face="Calibri, Verdana, Helvetica, Arial"><span
style="font-size:12pt"> Unless I misunderstand what
you're saying, this is what the reorder_by_rate param
does.<br>
<br>
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.<br>
<br>
-Ray<br>
<br>
<br>
_________________________________________________________________________
Professional FreeSWITCH Consulting Services: <a
moz-do-not-send="true"
href="consulting@freeswitch.org">consulting@freeswitch.org</a>
<a moz-do-not-send="true"
href="http://www.freeswitchsolutions.com">http://www.freeswitchsolutions.com</a>
FreeSWITCH-powered IP PBX: The CudaTel Communication
Server <a moz-do-not-send="true"
href="http://www.cudatel.com">http://www.cudatel.com</a>
Official FreeSWITCH Sites <a moz-do-not-send="true"
href="http://www.freeswitch.org">http://www.freeswitch.org</a>
<a moz-do-not-send="true"
href="http://wiki.freeswitch.org">http://wiki.freeswitch.org</a>
<a moz-do-not-send="true" href="http://www.cluecon.com">http://www.cluecon.com</a>
FreeSWITCH-users mailing list <a moz-do-not-send="true"
href="FreeSWITCH-users@lists.freeswitch.org">FreeSWITCH-users@lists.freeswitch.org</a>
<a moz-do-not-send="true"
href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users">http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</a>
UNSUBSCRIBE:<a moz-do-not-send="true"
href="http://lists.freeswitch.org/mailman/options/freeswitch-users">http://lists.freeswitch.org/mailman/options/freeswitch-users</a>
<a moz-do-not-send="true"
href="http://www.freeswitch.org">http://www.freeswitch.org</a><br>
<br>
<br>
<hr align="CENTER" size="3" width="95%"></span></font><font
size="2"><font face="Consolas, Courier New, Courier"><span
style="font-size:10pt">_________________________________________________________________________<br>
Professional FreeSWITCH Consulting Services:<br>
<a moz-do-not-send="true"
href="consulting@freeswitch.org">consulting@freeswitch.org</a><br>
<a moz-do-not-send="true"
href="http://www.freeswitchsolutions.com">http://www.freeswitchsolutions.com</a><br>
<br>
FreeSWITCH-powered IP PBX: The CudaTel Communication
Server<br>
<a moz-do-not-send="true"
href="http://www.cudatel.com">http://www.cudatel.com</a><br>
<br>
Official FreeSWITCH Sites<br>
<a moz-do-not-send="true"
href="http://www.freeswitch.org">http://www.freeswitch.org</a><br>
<a moz-do-not-send="true"
href="http://wiki.freeswitch.org">http://wiki.freeswitch.org</a><br>
<a moz-do-not-send="true"
href="http://www.cluecon.com">http://www.cluecon.com</a><br>
<br>
FreeSWITCH-users mailing list<br>
<a moz-do-not-send="true"
href="FreeSWITCH-users@lists.freeswitch.org">FreeSWITCH-users@lists.freeswitch.org</a><br>
<a moz-do-not-send="true"
href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users">http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</a><br>
UNSUBSCRIBE:<a moz-do-not-send="true"
href="http://lists.freeswitch.org/mailman/options/freeswitch-users">http://lists.freeswitch.org/mailman/options/freeswitch-users</a><br>
<a moz-do-not-send="true"
href="http://www.freeswitch.org">http://www.freeswitch.org</a><br>
</span></font></font></span></span></blockquote>
<span style="font-size:11pt"><span style="font-size:14px"><font
size="2"><font face="Consolas, Courier New, Courier"><span
style="font-size:10pt"><br>
</span></font></font><font face="Monaco, Courier New"><span
style="font-size:11pt">-- <br>
Ken<br>
<font color="#0000FF"><u><a moz-do-not-send="true"
href="http://www.FreeSWITCH.org">http://www.FreeSWITCH.org</a><br>
<a moz-do-not-send="true"
href="http://www.ClueCon.com">http://www.ClueCon.com</a><br>
<a moz-do-not-send="true" href="http://www.OSTAG.org">http://www.OSTAG.org</a><br>
</u></font>irc.freenode.net #freeswitch<br>
Twitter: @FreeSWITCH<br>
<br>
</span></font>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_________________________________________________________________________
Professional FreeSWITCH Consulting Services:
<a class="moz-txt-link-abbreviated" href="mailto:consulting@freeswitch.org">consulting@freeswitch.org</a>
<a class="moz-txt-link-freetext" href="http://www.freeswitchsolutions.com">http://www.freeswitchsolutions.com</a>
FreeSWITCH-powered IP PBX: The CudaTel Communication Server
<a class="moz-txt-link-freetext" href="http://www.cudatel.com">http://www.cudatel.com</a>
Official FreeSWITCH Sites
<a class="moz-txt-link-freetext" href="http://www.freeswitch.org">http://www.freeswitch.org</a>
<a class="moz-txt-link-freetext" href="http://wiki.freeswitch.org">http://wiki.freeswitch.org</a>
<a class="moz-txt-link-freetext" href="http://www.cluecon.com">http://www.cluecon.com</a>
FreeSWITCH-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:FreeSWITCH-users@lists.freeswitch.org">FreeSWITCH-users@lists.freeswitch.org</a>
<a class="moz-txt-link-freetext" href="http://lists.freeswitch.org/mailman/listinfo/freeswitch-users">http://lists.freeswitch.org/mailman/listinfo/freeswitch-users</a>
UNSUBSCRIBE:<a class="moz-txt-link-freetext" href="http://lists.freeswitch.org/mailman/options/freeswitch-users">http://lists.freeswitch.org/mailman/options/freeswitch-users</a>
<a class="moz-txt-link-freetext" href="http://www.freeswitch.org">http://www.freeswitch.org</a>
</pre>
</span></span></blockquote>
<br>
</body>
</html>