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

[Adrian Georgescu]

Can you pl telnet to the port where rating engine runs and issue manually the commands for MaxSessionTime and DebitBalance Type help in the console for samples. Then see which sql queries run for each command and paste them here in the sequence they happen.

Adrian
 
On Jul 23, 2011, at 5:31 PM, Jan Baumann wrote:

> 
> 
> 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
> 
> 
> 
> _______________________________________________
> Users mailing list
> Users at lists.opensips.org
> http://lists.opensips.org/cgi-bin/mailman/listinfo/users
>