full-import.php

<?php
# Se rad 54 för tabellerna som du vill importera
# rad 9,10 och 12 måste ändras enligt dina miljö
# Denna filen kan man köra hur ofta man vill, den gör en hel updatering första gången den körs på en dag och sedan tar det bara
# ändringar under dagen
# OBS!!!! ÄNDRINGAR ÄR ENDAST NYA POSTER, DEN TAR EJ ÄNDRAR SOM GJORT I EXISTERANDE POSTER
#

shell_exec('sudo mount -t cifs //<ip till servern med företaget>/SPCSFiler/Foretag -o username="<användarnamn till windows share>",password="<lösen till windows share>" /mnt/SPCS');
$Folder = "/mnt/SPCS/<företagets id>/";

require '<fil där mysql.php med mysql configurationen>';
date_default_timezone_set("Europe/Berlin");

$state =array();
$currentRec = 0;
$currentTable = "";

$handler = opendir($Folder);


//handel CTRL + C or other kill
declare(ticks = 1);
pcntl_signal(SIGTERM, "signal_handler");
pcntl_signal(SIGINT, "signal_handler");
function signal_handler($signal) {
    global $currentRec;
    global $currentTable;
    print "\r\n Caught SIG Saving state ...";
    sleep(1);
    updateIndex($currentTable, $currentRec);
    exit(" done \r\n ");
}

//handeling the current state of the import
$getState = $mysqli->query("SELECT * FROM current_state");

while ($tableState = $getState->fetch_array(MYSQLI_ASSOC))
{
    if($tableState['latest_update'] === date("Y-m-d"))
    {
        $state[$tableState['_table']] = $tableState['rec'];
    }
}

while(false !== ($file = readdir($handler)))
{
  $dbf = substr($file, -4);
  if($dbf == ".DBF")
  {
    $tbl = str_replace(".DBF","",$file);

    //if(in_array($tbl,array("BST"))){ dowork($Folder . $file, $tbl); print "Done \r\n"; }
    if(in_array($tbl,array("BST","ARTRAD","OOF","ART"))){ dowork($Folder . $file, $tbl); print "Done \r\n"; }
  }
}
print "-----------DONE WITH IMPORT, NOW DOING TRANSFER TO UPPFOLJ----------- \r\n";

// Open dbase file
function dowork($file,$tbl)
{
  global $mysqli;
  global $db;
  global $state;

  echo "File: " . $file . "   " . "|    Tbl: " . $tbl;

  if($state[$tbl]>0)
  {
       import_dbf($db, $tbl, $file);
  }else {
    $createString = "CREATE TABLE " . $db . "." . $tbl . " (";
    $dbh = dbase_open($file, 0)
    or die("Error! Could not open dbase database file '$db_path'.");
    // Get column information
    $column_info = dbase_get_header_info($dbh);
    // Display information
    //print_r($column_info);

    $line = array();

    foreach($column_info as $col)
    {
    switch($col['type'])
    {
      case 'character':
        $line[]= "`$col[name]` VARCHAR( $col[length] )";
        break;
      case 'number':
        $line[]= "`$col[name]` FLOAT";
        break;
      case 'boolean':
        $line[]= "`$col[name]` BOOL";
        break;
      case 'date':
        $line[]= "`$col[name]` DATE null default null";
        break;
      case 'memo':
        $line[]= "`$col[name]` TEXT";
        break;
    }
    }
    $line[] = "`DELETED` TEXT";
    $str = implode(",",$line);
    $sql = "CREATE TABLE `$db`.`$tbl` ( $str );";



    $mysqli->query("DROP TABLE IF EXISTS `$db`.`$tbl`") or die(mysqli_error($mysqli));

    $mysqli->query($sql) or die(mysqli_error($mysqli));
    /*
     * Thease if/else statments are for table optimacation. If you want to do indexes or stuff like that.
     */
    print "Altering tebles with indexes, this my take some time. \r\n";
    if($tbl =="BST")
    {
        $mysqli->query("ALTER TABLE `BST` ADD PRIMARY KEY(`BSTNR`);");

    }
    elseif($tbl =="ARTRAD")
    {

        $mysqli->query("ALTER TABLE  `ARTRAD` ADD INDEX (  `TYP` ,  `DOKNR` ) ;");
    }
    set_time_limit(0); // I added unlimited time limit here, because the records I imported were in the hundreds of thousands.

    import_dbf($db, $tbl, $file);
  }
}

function import_dbf($db, $table, $dbf_file)
{
    global $mysqli;
    global $state;
    global $currentRec;
    global $currentTable;

    $currentTable = $table;

    //getting current state of tables
    if(isset($state[$table])){
        $iState = $state[$table];
    }else $iState = 1;

    if (!$dbf = dbase_open ($dbf_file, 0)){
        die("Could not open $dbf_file for import.");
    }
    $num_rec = dbase_numrecords($dbf); // Get total number of rows so we can calcualte the progress
    $num_fields = dbase_numfields($dbf); //get total number of fields (not used)
    $fields = array();

    echo "\r\n";

    $stateI = 0;

    for ($i=$iState; $i<=$num_rec; $i++){
      echo "           " . number_format(($i/$num_rec)*100,2,'.','') . "%           \r";
        $row = @dbase_get_record_with_names($dbf,$i);
        $q = "insert into $db.$table values (";
        foreach ($row as $key => $val){
            //if ($key == 'deleted'){ continue; }

            $q .= "'" . addslashes(trim($val)) . "',"; // Code modified to trim out whitespaces
        }
        if (isset($extra_col_val)){
            $q .= "'$extra_col_val',";
        }
        $q = substr($q, 0, -1);
        $q .= ')';
        //if the query failed - go ahead and print a bunch of debug info
        if (!$result = $mysqli->query($q)){
            print (mysqli_error($mysqli) . " SQL: $q
            \n");
            print (substr_count($q, ',') + 1) . " Fields total.

            ";
            $problem_q = explode(',', $q);
            $q1 = "desc $db.$table";
            $result1 = $mysqli->query($q1);
            $columns = array();
            $i = 1;
            while ($row1 = $result1->fetch_assoc()){
                $columns[$i] = $row1['Field'];
                $i++;
            }
            $i = 1;
            foreach ($problem_q as $pq){
                print "$i column: {$columns[$i]} data: $pq
                \n";
                $i++;

            }
            die();
        }

        //state index keepeer
        if($stateI >= 1000)
        {
            updateIndex($table, $i);
            $stateI = 0;
        }
        $stateI++;
        $currentRec = $i;

    }
    updateIndex($table, $i);
}



function updateIndex($table,$num)
{
    global $mysqli;
    global $state;

    if($state[$table] > 0)
    {
        $mysqli->query("UPDATE `current_state` SET `rec`=" . $num . ", `latest_update`=now() WHERE `_table`='" . $table . "'");

    }else{
        $sql = "INSERT INTO `current_state` (`_table`,`REC`,`latest_update`) VALUES ('" . $table . "'," . $num . ",now())";
        $mysqli->query($sql);
        $state[$table] = 1;
    }

}
?>