#1
  1. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,111
    Rep Power
    487

    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
    "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
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,969
    Rep Power
    374
    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.
  4. #3
  5. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,111
    Rep Power
    487
    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($argv1)), $_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($destination02) == "00") {
                            
    $destination substr($destination2strlen($destination));
                        } else {
                            if (
    substr($destination01) == "0") {
                                
    $destination "44" substr($destination1strlen($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($destination0$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)?
    "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
  6. #4
  7. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    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.
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  8. #5
  9. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,111
    Rep Power
    487
    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!
    "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
  10. #6
  11. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,317
    Rep Power
    7170
    Do the rates change frequently?
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  12. #7
  13. Confused badger
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Mar 2009
    Location
    West Yorkshire
    Posts
    1,111
    Rep Power
    487
    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?
    "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

IMN logo majestic logo threadwatch logo seochat tools logo