[Serusers] query for lcr table
Simone Cittadini
mymailforlists at gmail.com
Wed Oct 5 16:55:41 CEST 2005
Iqbal ha scritto:
> Hi
>
> I have a query which select the correct prefix from the lcr table, but
> have realised that it my be incorrect, is anyone else using something
> similiar for rating
> SELECT prefix from lcr WHERE 'sip:1234 at sip.domain.com' LIKE
> lcr.from_uri AND '35387318220' LIKE CONCAT(lcr.prefix, '%') ORDER BY
> CHAR_LENGTH(lcr.prefix), lcr.priority DESC, RAND() ;
>
> Now this returns
>
> +--------+
> | prefix |
> +--------+
> | 353 |
> | 3538 |
> | 35387 |
> +--------+
>
> Now I assume you should pick the one with the best match, i.e at the
> bottom, if so should the query not have ASC no desc in the query,
> rather than having to add another line which takes all result and sees
> length of each.
I do this way (this is a python prototype of a personalized cdr_mysql.so
for asterisk, anyway you should catch the algorithm ...
for i in range(len(calledNum)-6):
prefix = calledNum[5:i+7]
queryRate = 'SELECT '+accountCode+' FROM rates WHERE prefix LIKE
\''+prefix+'%\' OR prefix = \''+prefix+'\''
dbCursor.execute(queryRate)
numeroRighe = int(dbCursor.rowcount)
if numeroRighe == 1:
break
elif numeroRighe == 0:
queryRate = 'SELECT '+accountCode+' FROM rates WHERE prefix =
\''+prefix[0:len(prefix)-1]+'\''
dbCursor.execute(queryRate)
break
rate = dbCursor.fetchone()
rate = rate[0]
More information about the sr-users
mailing list