5 Oct
2005
5 Oct
'05
10:55 a.m.
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@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]