Design idea for Call rating

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
diaphragm
Forum Newbie
Posts: 1
Joined: Sun Apr 19, 2009 2:54 pm

Design idea for Call rating

Post by diaphragm »

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:

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;
    }
}
}
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 :)
Post Reply