The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.
|
 |
|
Dev Shed Forums
> Programming Languages
> PHP Development
|
Is there a better way?
Discuss Is there a better way? in the PHP Development forum on Dev Shed.
Is there a better way? PHP Development forum discussing coding practices, tips on PHP, and other PHP-related topics. PHP is an open source scripting language that has taken the web development industry by storm.
|
|
 |
|
|
|
|
Dev Shed Forums Sponsor:
|
|
|
October 3rd, 2012, 05:48 AM
|
 |
Confused badger
|
|
Join Date: Mar 2009
Location: West Yorkshire
|
|
|
Is there a better way?
Hello all
I'm using the following code in my script which works but I'm wondering if there's a more efficient way to do this ...
Scenario:-
A CSV file holds some data: call_destination, call_duration
In this CSV file, there are 700+ records.
For each of the lines in there, my code loops through the CSV file and inspects the call_destination.
Ok with me so far?! Good ... I also have an array of call charge rates, an example value is:
PHP Code:
[0] => Array
(
[prefix] => 26377
[destination] => Zimbabwe Mobile Econet
[peak] => 0.299
[offpeak] => 0.299
[connection] => 0
[cust_peakpm] => 0.4485
[cust_offpeakpm] => 0.4485
)
At the moment, I am using the following code which goes through each record in the CSV file and checks against each record in the $rates array for a match; if a match is found, then it drags out the cost of the call. Pretty straight forward and, like I said above, I have code that works already BUT, the $rates array has about 37,000 entries! So it's working out that if there are 700 lines in my CSV file, it performs 700 * 37000 checks (that's 25,900,000 checks!)
I have tried to create another array to hold the key from $rates along with the $value['prefix'], this will be used when we're checking the destination prefix later on to get the cost per minute
PHP Code:
foreach ($rates as $key => $value) {
$short_rates[$key] = $value['prefix'];
}
But as each prefix varies in length, I'll still have to loop through 37000 rates 700 times!
So, does anyone have any ideas on what I can do to reduce the frankly stupid number of checks?!
Thanks to all for reading, I hope that someone can help me to find a solution (although I am worried that there might not be one and what I am doing is the only way to do it!)
Regards
__________________
The number for UK Emergencies is changing, the new number is 0118 999 881 999 119 7253
"For if leisure and security were enjoyed by all alike, the great mass of human beings who are normally stupefied by poverty would become literate and would learn to think for themselves; and when once they had done this, they would sooner or later realise that the privileged minority had no function and they would sweep it away"
- George Orwell, 1984
|
October 3rd, 2012, 06:19 AM
|
|
|
|
why not have another "column" saying charges in your csv file? and then simply add all these up for each destination?
you could also do this:
change the way you record data:
call_destination, call_duration, call_charge
egypt, 0.39, £2.00
then next time instead of creating another entry
egypt, 0.20.
append that to the previous entry of egypt,0.39? i.e add 0.39+0.20? so you now have:
egypt, 0.59, £2
so you do not have duplicate entries?
Because surely you will charge for each call by what the rates were at THAT time? so even if rates change it shouldnt matter?
I am not sure if this file holds the details for each user, or all user and whether you need to differentiate between each user when working out charges.
|
October 3rd, 2012, 07:03 AM
|
|
Confused badger
|
|
Join Date: Mar 2009
Location: West Yorkshire
|
|
Hi Paul
Thank you for the reply, unfortunately we can't add the cost at the time of the call/into the CSV ... Allow me to fill in the whole picture ...
We have three SIP servers all running Asterisk. They're named SIP1, SIP2 and SIP3 (cunning eh).
At 4am on a daily basis a CRON job runs a shell-script on each SIP server which renames the "Master.csv" file to "servername-yyyy-mm-dd.csv" and then uses SCP to transfer the file securely over SSH to our webserver.
Asterisk then creates a new "Master.csv" file when it deals with the next call (the script has the side-effect of creating a daily rotation of the Master.csv file, which is good as the Master.csv can grow quite substantially!).
So, over on the webserver, at 4am (give or take a few mins) every day a new file appears in the "import" directory, e.g. "SIP1-2012-10-03.csv".
At 4:10am every day a CRON job runs on the Webserver to parse and import this data into an existing database.
The shell-script is:
Code:
## IMPORT.SH #################################################################################################### ########
## Created 03.10.2012 R PEACOCK dpwss@hotmail.com
## This script runs via CRON at a set time every day to grab and import call data which is sent from the SIP Servers
## to /srv/www/htdocs/viveuk.net_v2/support/vbilling_v2/import.
cd /import
if [ $# -ne 0 ]
then
FILENAME="$1.csv"
else
STAMP=$(date +"%Y%m%d")
FILENAME="$STAMP.csv"
fi
php /import/import.php "filename=$FILENAME&importpath=/import/"
rm /$FILENAME.*
The PHP script for the import is:-
PHP Code:
<?php
date_default_timezone_set('Europe/London');
parse_str(implode('&', array_slice($argv, 1)), $_GET);
$importfile = $_GET['filename'];
$importpath = $_GET['importpath'];
include_once "../database.php"; // Credits here to Northie, cheers mate, this is the handiest class/code ever!
include_once "../config.php"; // Has my shared functions etc in
$servers = array("sip", "sip2", "sip3");
// We need to get the rates table from the DB now and stuff into an array ...
$rates_sql = "SELECT * FROM `callrates` ORDER BY `destination` ASC";
$rates = DB::Load()->Execute($rates_sql)->returnArray();
foreach ($servers as $server) {
$sourcefile = "{$importpath}{$server}-{$importfile}";
echo "\nPROCESSING : {$sourcefile}\n";
if (($handle = fopen($sourcefile, "r")) !== FALSE) {
$max_line_length=10000;
$columns = fgetcsv($handle, $max_line_length, ",");
foreach ($columns as &$column) { $column = str_replace(".","",$column); }
$record_counter=0;
$processed_record_counter=0;
while (($data = fgetcsv($handle, $max_line_length, ",")) !== FALSE) {
if ($data[8]!="") {
$iax_data = explode("/",$data[8]);
if ($iax_data[0] == "IAX2") {
$dest_data = explode(",",$iax_data[2]);
$destination = $dest_data[0];
// If the first two chars of $destination are "00" then we drop this entirely
// Otherwise, if the first char of $destination is "0" then we replace with "44"
if (substr($destination, 0, 2) == "00") {
$destination = substr($destination, 2, strlen($destination));
} else {
if (substr($destination, 0, 1) == "0") {
$destination = "44" . substr($destination, 1, strlen($destination));
} else {
$destination = "44" . $destination;
}
}
$dest_data[] = $destination;
// Now we work out the values to store in the database; we're going to use the existing `calldata` table for this which only needs the fields noted below.
// As we're storing call data and call time in different fields, we need to explode it here
$dt = explode(" ", $data[9]);
$guid = create_guid(); // A unique ID for this call
$calldate = $dt[0]; // The date the call was made
$calltime = $dt[1]; // The time the call was made
$anumber = $data[1]; // The PSTN / DID the call was made from
$dialled = $destination; // The number the call was made to
$duration = $data[13]; // Duration of the call
// Here we need to calculate the raw cost.
// This is a simple formula: duration * rate
// Peak hours are:
$peak_days = array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday");
$peak_start = "08:00:00";
$peak_finish = "18:00:00";
// By default, we assign the call to "Off-peak" but perform checks to see if it needs to be switched to "Peak".
$rate = "cust_offpeakpm";
// Work out what DAY the call took place and determine if it falls in the $peak_days array ...
$call_day = date("l", strtotime($data[9]));
if (in_array($call_day, $peak_days)) {
// ... if it does, we check then to see if it falls into the peak times
if ($calltime > $peak_start && $calltime < $peak_finish) { $rate = "cust_peakpm"; }
}
// Ok so at this point, we know if the call is Peak or Offpeak, now we check the $rates array (which
// holds all the prefixes and associated costs) to determine the actual cost (in pounds per minute)
if (strlen($destination) > 6) {
foreach ($rates as $value) {
$call_cost_per_min=0;
$call_cost_rate_desc="";
$prefix_length = strlen($value['prefix']);
$call_destination_prefix = substr($destination, 0, $prefix_length);
if ($call_destination_prefix == $value['prefix']) {
$match[] = array($value[$rate], $value['destination']);
}
}
asort($match);
$cost = $match[count($match)-1];
$call_cost_per_min = $cost[0];
$call_cost_rate_desc = $cost[1];
echo "The cost to call {$destination} is {$call_cost_per_min} POUNDS PER MINUTE ({$call_cost_rate_desc})\n";
}
$debit = $call_cost_per_min; // The raw cost (i.e, no markup or VAT) of the call
}
$sql = ""; // My INSERT sql goes here
DB::Load() -> Execute($sql);
$processed_record_counter++;
} else {
$not_processed[] = $data;
}
$record_counter++;
}
echo "Processed {$processed_record_counter} out of {$record_counter}\n";
print_r($not_processed);
}
}
?>
Now, for those who didn't read the script (and I don't blame you!) it DOES work, I get a nice screen of info telling me how much each call will cost, this is also stuffed into the database.
So, now you see the whole picture, any thoughts (at all, even to pick fault with my coding skills lol)?
|
October 4th, 2012, 09:08 PM
|
 |
Lost in code
|
|
|
|
Quote: | At the moment, I am using the following code which goes through each record in the CSV file and checks against each record in the $rates array for a match; if a match is found, then it drags out the cost of the call. Pretty straight forward and, like I said above, I have code that works already BUT, the $rates array has about 37,000 entries! So it's working out that if there are 700 lines in my CSV file, it performs 700 * 37000 checks (that's 25,900,000 checks!) |
Put your rates into a database table, add an index on the column that links it with the calls. Put your calls into a database table. Select your calls back out with a join to the rates table and let the database engine do the hard work.
|
October 5th, 2012, 02:14 AM
|
|
Confused badger
|
|
Join Date: Mar 2009
Location: West Yorkshire
|
|
Quote: | Originally Posted by E-Oreo Put your rates into a database table, add an index on the column that links it with the calls. Put your calls into a database table. Select your calls back out with a join to the rates table and let the database engine do the hard work. |
Although that sounds like a good idea, the call data comes in the format
nnnnnnnnnn (although the length can vary depending on the country/area code etc) whereas the rates have a 'prefix' column which again, varies in length. As the system records the whole number and not the prefix and the called number in different columns, I don't think this would be possible using the method you've described.
I did find a sort-of work around in that I used 2 SQL queries to build 2 arrays, one for "commonly used" prefixes, in our case numbers beginning with '44', and another which exclude those.
The code then checks against the smaller table (6000 records) first and then, if not found, checks against the larger (28000 records) array.
It seems to have sped it up considerably so 'wooo hoooo'
But appreciate your suggestion!
|
October 7th, 2012, 09:12 AM
|
|
Lost in code
|
|
|
|
|
Do the rates change frequently?
|
October 7th, 2012, 10:19 AM
|
|
Confused badger
|
|
Join Date: Mar 2009
Location: West Yorkshire
|
|
Quote: | Originally Posted by E-Oreo Do the rates change frequently? |
Not *frequently* although they are subject to change ... we do get a little notice (usually at the start of a month) ... why?
|
Developer Shed Advertisers and Affiliates
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
Rate This Thread |
 Linear Mode
|
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
