Design idea for Call rating
Posted: Sun Apr 19, 2009 3:12 pm
Hi,
Im re-designing a part of my invoice generation tool for my voip billing system (inhouse written) and i need new ideas to rate the individual calls that are on my database.
there is a CDR database (Call Detail records) with millions of records, each line represents a call that a user has made to and from a destination. Each Call is made by different customer to a A-Z destination (For example bilgaria) and for that specific the rate of calls to Bulgaria is defined in the rating table. The rates could change and EffectiveDate is applied to each date.
Right now i do the rating on the fly and is not done on scheduled implementation where the call rates are stored in the database. Here is how its done now:
1) I load the current rates applied to that customer from the prices table. I then put them into an associated array. for example:
$PRICES[$row[AreaCode]][Destination]=$row[Description];
$PRICES[$row[AreaCode]][Effective][$row[EffectiveDate]]=$row[Cost];
2) I load every individual call from my CDR's table
3) For every line record thats fetched, i loop through the PRICES array and i look to see if the Called Destination begins with the AreaCode, if it does the i'll select that areacode and use its Rate and Description for rating.
Here is my simplified code:
The problem with this code that does the rating on the fly is pretty simple, almot 1000-2000 different rating entries are looked for every call record which could be over 100,000 for a selected period.
This consumes a lot of processing and takes about 30seconds+ to complete depending on the number of call records returned for that period.
I need a new design, can you guys think of a different way this could be done?
(besides daily schedules of ratings...)
Thanks for your time in advance
Im re-designing a part of my invoice generation tool for my voip billing system (inhouse written) and i need new ideas to rate the individual calls that are on my database.
there is a CDR database (Call Detail records) with millions of records, each line represents a call that a user has made to and from a destination. Each Call is made by different customer to a A-Z destination (For example bilgaria) and for that specific the rate of calls to Bulgaria is defined in the rating table. The rates could change and EffectiveDate is applied to each date.
Right now i do the rating on the fly and is not done on scheduled implementation where the call rates are stored in the database. Here is how its done now:
1) I load the current rates applied to that customer from the prices table. I then put them into an associated array. for example:
$PRICES[$row[AreaCode]][Destination]=$row[Description];
$PRICES[$row[AreaCode]][Effective][$row[EffectiveDate]]=$row[Cost];
2) I load every individual call from my CDR's table
3) For every line record thats fetched, i loop through the PRICES array and i look to see if the Called Destination begins with the AreaCode, if it does the i'll select that areacode and use its Rate and Description for rating.
Here is my simplified code:
Code: Select all
while($row1 = mysql_fetch_row ($result1)){ // Go over every Call Record
reset($PRICES);
$numaaaa = $row1[1]; // Destination Dialled Number
while(list($Prefix,$InfoElements) = each($PRICES)){ // GO over every Rate applied to Callee
if(strncmp($numaaaa,$Prefix,strlen($Prefix))==0){ // See if AreCode is within the Called Number
foreach($PRICES[$Prefix][Effective] as $EDATE=>$ERATE){ // Loop through the different applied dates and identify the matching effective Date based on date of the call.
$CALLTIME = strtotime($row1[2]);
$EFFECTIVETIME = strtotime($EDATE); // Effective Date
$ENDDATE = strtotime($enddate); //Date To of query
if($CALLTIME>=$EFFECTIVETIME && $EFFECTIVETIME <= $ENDDATE){ // Identify the Rate that applies to the Date Range
$DETAILS[$Prefix][Effective][$EDATE][Rate]=$ERATE;
$DETAILS[$Prefix][Effective][$EDATE][TotalCalls]++;
$DETAILS[$Prefix][Effective][$EDATE][Cost]+=$ERATE/60*$row1[0];
$DETAILS[$Prefix][Effective][$EDATE][TotalDur]+=$row1[0];
$DETAILS[$Prefix][Effective][$EDATE][TotalSuccess]++;
break; //End loop if effective date is found and rate selected!
}
}
$DETAILS[$Prefix][Destination]=$PRICES[$Prefix][Destination];
break;
}
}
}This consumes a lot of processing and takes about 30seconds+ to complete depending on the number of call records returned for that period.
I need a new design, can you guys think of a different way this could be done?
(besides daily schedules of ratings...)
Thanks for your time in advance