<?php
//This script will convert the DispatchTrack XML files into a format that can be inserted into the database
//http://10.1.1.41/DT_Fetch/convert2DB.php
ini_set('max_execution_time', 1200);  // set max_execution_time to 20 minutes

require_once("dt_sql_config.php"); // database connection config.
$fileArr = preg_grep('/^([^.])/',scandir('DTHistory'));

foreach ($fileArr as $file) {
    $dt = substr($file,0,10); // output like 2020-01-03
    $x = date('m-d-Y', strtotime($dt)); //rearrange the date format
    //if(strtotime($dt) > strtotime('October 27 2019')){ //pickup where we left off if the script times out
        $xml = simplexml_load_file("DTHistory/".$file); //load file into simplexml object so we can parse it
        foreach ($xml->service_order as $soInfo) {
            if($soInfo['order_number']){
                $order_number = $soInfo['order_number'];
                $id = $soInfo['id'];
                $display_order_number = $soInfo->display_order_number;
                $customer_id = $soInfo->customer->customer_id;
                $first_name = $soInfo->customer->first_name;
                $last_name = $soInfo->customer->last_name;
                $email = $soInfo->customer->email;
                $phone1 = $soInfo->customer->phone1;
                $phone2 = $soInfo->customer->phone2;
                $phone3 = $soInfo->customer->phone3;
                $address1 = $soInfo->customer->address1;
                $address2 = $soInfo->customer->address2;
                $city = $soInfo->customer->city;
                $state = $soInfo->customer->state;
                $zip = $soInfo->customer->zip;
                $latitude = $soInfo->customer->latitude;
                $longitude = $soInfo->customer->longitude;
                $status = $soInfo->status;
                $service_type = $soInfo->service_type;
                $description = htmlspecialchars($soInfo->description, ENT_QUOTES, 'UTF-8'); //CDATA
                $account = $soInfo->account;
                $survey_status = $soInfo->survey_status;
                $stop_number = $soInfo->stop_number;
                $route_locked = $soInfo->route_locked;
                $scheduled_at = $soInfo->scheduled_at;
                $time_window_start = $soInfo->time_window_start;
                $time_window_end = $soInfo->time_window_end;
                $truck_id = $soInfo->truck->id;
                $truck_name = $soInfo->truck->name;
                $gps_arrive = $soInfo->gps->arrive;
                $gps_depart = $soInfo->gps->depart;
                $service_time = $soInfo->service_time;
                $request_date = $soInfo->request_date;
                $service_unit = $soInfo->service_unit;
                $started_at = $soInfo->started_at;
                $finished_at = $soInfo->finished_at;
                $amount = $soInfo->amount;
                $cod_amount = $soInfo->cod_amount;
                $cod = $soInfo->cod;
                $pieces = $soInfo->pieces;
                $volume = $soInfo->volume;
                $delivery_charges = $soInfo->delivery_charges;
                $sales_tax = $soInfo->sales_tax;
                $notes_count = $soInfo->notes['count'];
                $items_delivered_quantity = $soInfo->items['delivered_quantity'];
                $items_count = $soInfo->items['count'];
                $items_total_quantity = $soInfo->items['total_quantity'];
                $images_count = $soInfo->images['count'];
                $timeframe = $soInfo->extra->timeframe;
                $route_label = $soInfo->extra->route_label;
                $store_code = $soInfo->extra->store_code;
                $sales_person = $soInfo->extra->sales_person;
                $subtotal = $soInfo->extra->subtotal;
                $level_of_satisfaction = $soInfo->custom_fields->level_of_satisfaction;
                $signature_callback_code = $soInfo->signature['callback_code'];
                $signature_created_at = $soInfo->signature['created_at'];

                $sql = "INSERT INTO dispatch.orders (`order_number`, 
                                                        `id`, 
                                                        `customer_id`,
                                                        `first_name`, 
                                                        `last_name`, 
                                                        `email`, 
                                                        `phone1`, 
                                                        `phone2`, 
                                                        `phone3`, 
                                                        `address1`, 
                                                        `address2`,
                                                        `city`,
                                                        `state`,
                                                        `zip`,
                                                        `latitude`,
                                                        `longitude`,
                                                        `status`,
                                                        `service_type`,
                                                        `description`,
                                                        `account`,
                                                        `survey_status`,
                                                        `stop_number`,
                                                        `route_locked`,
                                                        `scheduled_at`,
                                                        `time_window_start`,
                                                        `time_window_end`,
                                                        `truck_id`,
                                                        `truck_name`,
                                                        `gps_arrive`,
                                                        `gps_depart`,
                                                        `service_time`,
                                                        `request_date`,
                                                        `service_unit`,
                                                        `started_at`,
                                                        `finished_at`,
                                                        `amount`,
                                                        `cod_amount`,
                                                        `cod`,
                                                        `pieces`,
                                                        `volume`,
                                                        `delivery_charges`,
                                                        `sales_tax`,
                                                        `notes_count`,
                                                        `items_delivered_quantity`,
                                                        `items_count`,
                                                        `items_total_quantity`,
                                                        `images_count`,
                                                        `timeframe`,
                                                        `route_label`,
                                                        `store_code`,
                                                        `sales_person`,
                                                        `subtotal`,
                                                        `custom_fields_level_of_satisfaction`,
                                                        `signature_callback_code`,
                                                        `signature_created_at`
                                                        ) 
                            VALUES ('".$soInfo['order_number']."',
                                   '".$id."', 
                                   '".$customer_id."',
                                   '".$first_name."', 
                                   '".$last_name."', 
                                   '".$email."', 
                                   '".$phone1."', 
                                   '".$phone2."', 
                                   '".$phone3."', 
                                   '".$address1."', 
                                   '".$address2."',
                                   '".$city."',
                                   '".$state."',
                                   '".$zip."',
                                   '".$latitude."',
                                   '".$longitude."',
                                   '".$status."',
                                   '".$service_type."',
                                   '".$description."',
                                   '".$account."',
                                   '".$survey_status."',
                                   '".$stop_number."',
                                   '".$route_locked."',
                                   '".$scheduled_at."',
                                   '".$time_window_start."',
                                   '".$time_window_end."',
                                   '".$truck_id."',
                                   '".$truck_name."',
                                   '".$gps_arrive."',
                                   '".$gps_depart."',
                                   '".$service_time."',
                                   '".$request_date."',
                                   '".$service_unit."',
                                   '".$started_at."',
                                   '".$finished_at."',
                                   '".$amount."',
                                   '".$cod_amount."',
                                   '".$cod."',
                                   '".$pieces."',
                                   '".$volume."',
                                   '".$delivery_charges."',
                                   '".$sales_tax."',
                                   '".$notes_count."',
                                   '".$items_delivered_quantity."',
                                   '".$items_count."',
                                   '".$items_total_quantity."',
                                   '".$images_count."',
                                   '".$timeframe."',
                                   '".$route_label."',
                                   '".$store_code."',
                                   '".$sales_person."',
                                   '".$subtotal."',
                                   '".$level_of_satisfaction."',
                                   '".$signature_callback_code."',
                                   '".$signature_created_at."'
                                    )";
                mysqli_query($db1, $sql);
                //echo $sql."<br>";

                foreach($soInfo->drivers->driver as $driver){
                    $driver_id = $driver->id;
                    $driver_name = $driver->name;
                    $sql = "INSERT INTO dispatch.drivers (`order_number`, `driver_id`, `driver_name`) 
                            VALUES ('".$soInfo['order_number']."','".$driver_id."','".$driver_name."')";
                    mysqli_query($db1, $sql);
                }

                if($soInfo->notes['count'] > 0){
                    foreach($soInfo->notes->note as $note){
                        $author = $note['author'];
                        $created_at = $note['created_at'];
                        $order_note = htmlspecialchars($note, ENT_QUOTES, 'UTF-8');
                        $sql = "INSERT INTO dispatch.notes (`order_number`, `note`, `note_author`, `note_created_at`) 
                                VALUES ('".$soInfo['order_number']."','".$order_note."','".$author."','".$created_at."')";
                        mysqli_query($db1, $sql);
                    }
                }

                if($soInfo->items['count'] > 0){
                    foreach ($soInfo->items->item as $item) {
                        $serial_number = $item->serial_number;
                        $item_id = $item->item_id;
                        $description = htmlspecialchars($item->description, ENT_QUOTES, 'UTF-8');
                        $sale_sequence = $item->sale_sequence;
                        $quantity = $item->quantity;
                        $delivered = $item->delivered;
                        $delivered_quantity = $item->delivered_quantity;
                        $amount = $item->amount;
                        $checked_quantity = $item->checked_quantity;
                        $location = $item->location;
                        $return_code = $item->return_codes->return_code;
                        $sql = "INSERT INTO dispatch.items (`order_number`, 
                                                            `serial_number`, 
                                                            `description`,
                                                            `sale_sequence`, 
                                                            `quantity`, 
                                                            `delivered`, 
                                                            `delivered_quantity`, 
                                                            `amount`, 
                                                            `checked_quantity`, 
                                                            `location`, 
                                                            `return_code`) 
                                VALUES ('".$soInfo['order_number']."',
                                        '".$serial_number."',
                                        '".$description."',
                                        '".$sale_sequence."',
                                        '".$quantity."',
                                        '".$delivered."',
                                        '".$delivered_quantity."',
                                        '".$amount."',
                                        '".$checked_quantity."',
                                        '".$location."',
                                        '".$return_code."')";
                        mysqli_query($db1, $sql);
                    }
                }

                if($soInfo->images['count'] > 0){
                    foreach ($soInfo->images->image as $image) {
                        $image_src = $image['src'];
                        $image_name = $image['name'];
                        $image_created_at = $image['created_at'];
                        $image_id = $image['id'];
                        $sql = "INSERT INTO dispatch.images (`order_number`, 
                                                            `image_src`, 
                                                            `image_name`,
                                                            `image_created_at`, 
                                                            `image_id`) 
                                VALUES ('".$soInfo['order_number']."',
                                        '".$image_src."',
                                        '".$image_name."',
                                        '".$image_created_at."',
                                        '".$image_id."')";
                        mysqli_query($db1, $sql);
                        
                    }
                }
            }
        }
    //}
}

?>
