0
\$\begingroup\$

My program is called "Cleaner".

What it does is that we have a "Dirty" excel file with a good number of lines in it around 20,000 - 400,000 lines.

The process of cleaning the records is basically improving the formats(Phone Number) and adding additional details(Country, State, Address, etc) based on the records.

This includes a whole lot of loops and conditional statement. I'll try my best to explain every part of my code here.

Currently, this works great but I would like to optimize it so that it can handle processing files that have > 20,000 lines in it since it cannot do so currently.

Includes: Name Parser

Sample content of timezones.csv

Area Code|  Timezone|   State|    Abbr|    Country|   Continent
204|        CST|       Manitoba|   MB|      Canada|   North America
226|        EST|       Ontario|    ON|      Canada|   North America

Sample of content for upload csv file:

Full Name   Email               Phone 1
John Doe    tester@gmail.com    (123) 456-1638 
Jeremy Doe  awesome@gmail.com   1234561268
Joseph Doe  coolkid@gmail.com   (123) 456-4958

Code:

<?php
error_reporting(0);
require_once('parser/parser.php'); ##includes the name parser plugin
if(!empty($_FILES["uploadedfile"]["name"])){ ## gets the uploaded excel file
    $target_dir = "uploads/";
    $target_file = $target_dir.substr(md5(rand()), 0, 7).basename($_FILES["uploadedfile"]["name"]);

    if (move_uploaded_file($_FILES["uploadedfile"]["tmp_name"], $target_file)) {
        if (file_exists($target_file)) {
            $file = fopen($target_file,"r");
            ##fetches a list of timezone per state or country (if outside USA)
            $timezone = fopen("timezones.csv","r");

            ##defined country array with country codes
            ##had to do this since long code is not allowed here :)
            ## I'm using $countryArray
            https://gist.github.com/josephilipraja/8341837

            ##time zone csv to array
            $timezone_array   = array();
            $tiemzone_counter = 0;
            while (($data_time = fgetcsv($timezone)) !== FALSE) {
                $timezone_array[$tiemzone_counter]['area_code'] = $data_time[0];
                $timezone_array[$tiemzone_counter]['timezone']  = $data_time[1];
                $timezone_array[$tiemzone_counter]['state']     = $data_time[2];
                $timezone_array[$tiemzone_counter]['abbr']      = $data_time[3];
                $timezone_array[$tiemzone_counter]['country']   = $data_time[4];
                $timezone_array[$tiemzone_counter]['continent'] = $data_time[5];
                $tiemzone_counter++;
            }
            array_shift($timezone_array);

            ##cleaner part 1; removes unnecessary characters on the phone number
            $clean_numbers = array();
            $counter = 0;
            while (($data = fgetcsv($file)) !== FALSE) {

                if(!empty($data[2])){
                    ##removes non number characters
                    $data[2] = preg_replace('~\D~', '', $data[2]);
                    if(strlen($data[2]) >= 10){
                        $clean_numbers[$counter]['name']    = $data[0];
                        $clean_numbers[$counter]['email']   = $data[1];
                        $clean_part_1 = preg_replace('/^(011)/', '', $data[2]); ##removes leading 011
                        $clean_part_2 = preg_replace('/^(0+)/', '', $clean_part_1); ##removes leading 0's
                        if(strlen($clean_part_2) === 11){
                            $clean_part_3 = preg_replace('/^(1)/', '', $clean_part_2); ##removes leading 1's
                        }else{
                            $clean_part_3 = $clean_part_2;
                        }
                        $clean_numbers[$counter]['numbers'] = $clean_part_3;
                        $counter++;
                    }
                }
            }
            ##removes duplicates
            $cleaned_data      = unique_multidim_array($clean_numbers,'numbers');
            $so_cleaned_data   = array_values($cleaned_data);
            $cleaned_data_size = sizeof($so_cleaned_data);
            $almost_cleaned    = array();
            $header_array      = array("Salutation", "First Name", "Middle Name", "Last Name", "Email Address", "Office Phone", "Area Code", "Time Zone", "Primary Address State", "Primary Address Country", "Continent", "Lead Source", "Tags", "Assigned User Name");
            $parser = new FullNameParser();
            ##creates the array to be converted to CSV and downloaded
            for($cleaned_count = 0; $cleaned_count < $cleaned_data_size; $cleaned_count++){
                if(strlen($so_cleaned_data[$cleaned_count]['numbers']) > 10){
                    $key_country = searchInternational($countryArray, $so_cleaned_data[$cleaned_count]['numbers']);
                    if(!empty($key_country)){
                        $temp_areacode = $countryArray[$key_country]['code'];
                        $prime_country = $countryArray[$key_country]['name'];
                        $prime_state   = $key_country;
                        $continent     = $countryArray[$key_country]['continent'];
                    }

                    $limited_num        = $so_cleaned_data[$cleaned_count]['numbers'];
                    $found_key          = $key_country;
                    $timezone           = "";


                }else{
                    $temp_areacode = substr($so_cleaned_data[$cleaned_count]['numbers'], 0, -7);
                    $found_key     = searchthearray($timezone_array, 'area_code', $temp_areacode);
                    $prime_state   = $timezone_array[$found_key]['abbr'];
                    $prime_country = $timezone_array[$found_key]['country'];
                    $limited_num   = $so_cleaned_data[$cleaned_count]['numbers'];
                    $timezone      = $timezone_array[$found_key]['timezone'];
                    $continent     = $timezone_array[$found_key]['continent'];
                }
                $parsed_name = $parser->parse_name($so_cleaned_data[$cleaned_count]['name']);

                $almost_cleaned[$cleaned_count]['salutation']  = $parsed_name['salutation'];
                $almost_cleaned[$cleaned_count]['first_name']  = $parsed_name['fname'];
                $almost_cleaned[$cleaned_count]['middle_name'] = $parsed_name['initials'];
                $almost_cleaned[$cleaned_count]['last_name']   = $parsed_name['lname']." ".$parsed_name['suffix'];
                $almost_cleaned[$cleaned_count]['email_address'] = $so_cleaned_data[$cleaned_count]['email'];
                $almost_cleaned[$cleaned_count]['office_phone']  = $limited_num;
                $almost_cleaned[$cleaned_count]['area_code']     = (!empty($found_key))?$temp_areacode:"";
                $almost_cleaned[$cleaned_count]['timezone']      = (!empty($found_key))?$timezone:"";
                $almost_cleaned[$cleaned_count]['prime_state']   = (!empty($found_key))?$prime_state:"";
                $almost_cleaned[$cleaned_count]['prime_country'] = (!empty($found_key))?$prime_country:"";
                $almost_cleaned[$cleaned_count]['continent']     = (!empty($found_key))?$continent:"";
                $almost_cleaned[$cleaned_count]['lead_source']   = "Vendor";
                $almost_cleaned[$cleaned_count]['tags']          = "Predictive Dialing";
                $almost_cleaned[$cleaned_count]['assigned_user'] = "Predictive Dialing";
            }


            header("Content-Type: text/csv");
            header('Content-disposition: attachment;filename=leadsfortoday.csv');
            $fp = fopen("php://output", "w");

            fputcsv ($fp, $header_array);
            foreach($almost_cleaned as $row){
                fputcsv($fp, $row);
            }
            die;
            fclose($fp);

            header("Location: index.php");
            exit;
        }
    }
}

function searchInternational($country, $number){
    foreach($country as $key => $value){
        preg_match('/^('.$value['code'].')/',$number,$matches);
        if(!empty($matches[1])){
            return $key;
            break;
        }
    }

}

function searchthearray($products, $field, $value){
    foreach($products as $key => $product){
        if ( $product[$field] === $value )
            return $key;
    }
    return false;
}

function unique_multidim_array($array, $key) {
    $temp_array = array();
    $i = 0;
    $key_array = array();

    foreach($array as $val) {
        if (!in_array($val[$key], $key_array)) {
            $key_array[$i] = $val[$key];
            $temp_array[$i] = $val;
        }
        $i++;
    }
    return $temp_array;
}
?>
\$\endgroup\$
  • \$\begingroup\$ You should improve searchInternational and searchthearray to not have to iterate with a for \$\endgroup\$ – juvian Mar 21 '18 at 14:15
  • \$\begingroup\$ @juvian What would you suggest as an alternative to for? \$\endgroup\$ – hungrykoala Mar 22 '18 at 2:18
  • \$\begingroup\$ in searchthearray case, for each field you will use it for, make a map of values to keys \$\endgroup\$ – juvian Mar 22 '18 at 5:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.