[OpenSIPS-Users] UPDATE: CDRTool prepaid bebits correct customer with wrong pricing of another

[Jan Baumann]

Hi Tijmen,

and thanks for your quick answer. I have successfully investigated this and the difference in rating prepaid and postpaid calls.

A wireshark trace of the sql communication during rating shows interesting details - and I think a bug:

You know, our problem is, that prepaid calls are billed with the billing profile of a wrong and "randomly" choosen customer while postpaid calls rated from the cronjob work perfecly.

I see the following mysql query that messes things up during the rating process:

prepaid:
select * from billing_customers where subscriber = '0821723xxx at mysipserver.de'  or domain = '' or gateway = '212.125.x.y' or (subscriber = '' and domain = '' and gateway = '') order by subscriber desc, domain desc, gateway desc limit 1 

postpaid:
select * from billing_customers where subscriber = '0821889xxx at mysipserver.de' or domain = 'mysipserver.de' or gateway = '212.125.x.y'  or (subscriber = '' and domain = '' and gateway = '') order by subscriber desc, domain desc, gateway desc limit 1


As you can see the domain field is empty in the prepaid and populated in the postpaid query.
When I have the domain fields in the billing_customers table empty this "OR domain = ''" selects all my customers and picks the one with the lowest numerical phone number. If I have the correct domain of ALL the users set in billing_customers, this empty OR clause does not match anything and does not bother. 

BUT...

As the postpaid query selects WITH the "or domain = 'mysipserver.de'" this breaks the postpaid customers in exactly the same way - finds all of them and orders them numerically descending ending up with the lowest.


How is this domain column in billing_customers meant to be used? What is the sense behind this query?
And do you agree that this is a bug that needs to be fixed? 

Thank you and
Best Regards,

Jan




>> Am 30.06.11 15:06, schrieb Tijmen de Mes:
>>> Hi,
>>> 
>>> We have never seen this before. Have you done al the migration steps
>>> required to go to 8.1.2?
>>> 
>>> Also which php version are you using?
>>> 
>>> Best regards,
>>> 
>>> Tijmen de Mes
>>> AG Projects.
>>> 
>>> Op 6/30/11 1:40 PM, Jan Baumann schreef:
>>>> Good afternoon,
>>>> 
>>>> I have updated to CDRTool 8.1.2, callcontrol 2.0.12 and mediaproxy
>>>> 2.5.0 running together with an older opensips 1.5.3.
>>>> Everything works perfectly for postpaid calls.
>>>> 
>>>> I now have come across very strange behaviour when testing prepaid calls.
>>>> After terminating a prepaid call I get the following syslog - all with
>>>> the same callid and surely belonging together.
>>>> "Default" is the name of the fallback billing profile for all customer
>>>> accounts.
>>>> The calling user has in fact assigned a dedicated rating plan
>>>> "euroflat" with free calls to Austria.
>>>> 
>>>> When user 0821723xxx now calls to Austria the prepaid system uses the
>>>> rating profile of subscriber 08219987xyz(!) to calculate the pricing
>>>> and debits 0821723xxx's prepaid account with the inappropriate default
>>>> rate of the wrong other customer.
>>>> The wrongly chosen 08219987xyz seems a random, but always the same
>>>> choice, even for different prepaid callers. If I delete the
>>>> 08219987xyz user the system chooses another wrong one and sticks with
>>>> that.
>>>> 
>>>> Immediately after prepaid call ends:
>>>> First line wrong customer choice, second line correct caller's balance
>>>> debited with wrong rate:
>>>> 
>>>> Jun 30 13:12:24 voip cdrtool[4901]: Increment=60 MinDuration=60
>>>> ConnectFee=0.0000 CallId=NPI2JDqYKnQCsLz.x3ELQVMsLvQ5wh5p Span=1
>>>> Duration=60 DestId=43 subscriber=08219987xxx at mysipserver.de
>>>> Profile=default Period=weekday Rate=default Interval=0-24
>>>> Cost=0.0250/60 Price=0.0250 PriceIn=0.0181
>>>> Jun 30 13:12:24 voip cdrtool[4901]: DebitBalance=0.0250 Duration=4
>>>> CallId=NPI2JDqYKnQCsLz.x3ELQVMsLvQ5wh5pBillingParty=0821723xxx at mysipserver.de 
>>>> DestId=43 MaxSessionTime=0 Counter=1->0
>>>> 
>>>> 
>>>> When cron normalizes the radius CDRs 2 min. later (or I renormalize
>>>> from the web GUI) it grabs the correct profile for the correct customer:
>>>> 
>>>> Jun 30 13:15:02 voip cdrtool[5305]: Increment=60 MinDuration=60
>>>> ConnectFee=0.0000 CallId=NPI2JDqYKnQCsLz.x3ELQVMsLvQ5wh5p Span=1
>>>> Duration=60 DestId=43 subscriber=0821723xxx at mysipserver.de
>>>> Profile=euroflat Period=weekday Rate=euroflat Interval=0-24
>>>> Cost=0.0000/60 Price=0.0000 PriceIn=0.0181
>>>> 
>>>> 
>>>> Any ideas what can be going on there???
>>>> 
>>>> Thank you and
>>>> Kind Regards,
>>>> 
>>>> Jan Baumann