import.php

<?php

error_reporting(E_ALL);
define('LOG_TO_FILE', true);
define('DATA_DIR', 'data');
define('CLEAN', true);
define('MAX_RECORDS', 0);
define('IGNORE_DATA', false);
define('IGNORE_TOO_MUCH_FIELDS', true);
define('ONE_DB_ONLY', false); //false or databasename AS400K_VMDAT
// Init
gc_enable();
ini_set('memory_limit', '1024M');
@unlink('import.log');
$start = time();
writeLog('Start importing');

// Database link
$link = mysqli_connect('localhost', 'root', '', 'mandema');

if (!$link) {
    writeLog('Cannot create connection');
    exit;
}

// Read directories/databases
$databases = getDataBases();

if (CLEAN)
    deleteDatabases($databases, $link);

createDatabases($databases, $link);

foreach ($databases as $dbname => $path) {
    if (ONE_DB_ONLY === false || ONE_DB_ONLY == $dbname) {
        if (mysqli_query($link, 'use ' . $dbname) === true) {
            createTables($dbname, $path, $link);
        } else {
            writeLog('[Failed] cannot use: ' . $dbname);
        }
    }
}

updateVmDat($link);
writeLog('End importing');
writeLog('Duration: ' . date("z H:i:s", (time() - $start - 3600)));

/**
 * Write a log message to import.log and to the console
 * @param type $message 
 */
function writeLog($message) {
    $line = date("Y-m-d h:i:s") . ' - ' . $message;
    file_put_contents('import.log', $line . "\n", FILE_APPEND);
    //echo substr($line, 0, 80) . "\n";
    echo $line . "\n";
}

/**
 * Get databases base on directory structure db_name_p1/module/db_name_p1
 * @return string 
 */
function getDataBases() {
    $array = array();
    //dept 0
    if ($handle0 = opendir(DATA_DIR)) {

        while (false !== ($entry0 = readdir($handle0))) {
            if ($entry0 != '.' && $entry0 != '..') {

                //dept 1
                if ($handle1 = opendir(DATA_DIR . '/' . $entry0)) {
                    while (false != ($entry1 = readdir($handle1))) {
                        if ($entry1 != '.' && $entry1 != '..') {
                            //dept 2

                            if ($handle2 = opendir(DATA_DIR . '/' . $entry0 . '/' . $entry1)) {
                                while (false != ($entry2 = readdir($handle2))) {
                                    if ($entry2 != '.' && $entry2 != '..') {
                                        $array[$entry0 . '_' . $entry2] = $entry0 . '/' . $entry1 . '/' . $entry2;
                                    }
                                }
                                closedir($handle2);
                            }
                        }
                    }
                    closedir($handle1);
                }
            }
        }
        closedir($handle0);
    }

    return $array;
}

/**
 * Create the databases
 * 
 * @param type $databases array with key as database name and value is the path of the csvs. 
 * @param type $link 
 */
function createDatabases($databases, $link) {
    foreach ($databases as $key => $value) {
        if (ONE_DB_ONLY === false || ONE_DB_ONLY == $key) {
            set_time_limit(60);
            if (mysqli_query($link, 'create database ' . $key) === true) {
                writeLog('Database created: ' . $key);
            } else {
                writeLog('[Failed] Database creation: ' . $key);
            }
        }
    }
}

function deleteDatabases($databases, $link) {
    foreach ($databases as $key => $value) {
        if (ONE_DB_ONLY === false || ONE_DB_ONLY == $key) {
            if (mysqli_query($link, 'drop database ' . $key) === true) {
                writeLog('Database dropped: ' . $key);
            } else {
                writeLog('[Failed] Database dropping: ' . $key);
            }
        }
    }
}

function createTables($dbname, $path, $link) {
    if ($handle = opendir(DATA_DIR . '/' . $path)) {

        while (false != ($entry = readdir($handle))) {
            set_time_limit(30);
            sleep(10);
            set_time_limit(30);
            
            $fieldsFile = DATA_DIR . '/' . $path . '/FFD/' . $entry;
            $dataFile = DATA_DIR . '/' . $path . '/' . $entry;

            if ($entry != '.' && $entry != '..' && is_dir($dataFile) == false) {

                //create table
                $tableName = substr($entry, 0, -4);
                $query = 'CREATE TABLE `' . strtolower($tableName) . '` (';
                $firstField = true;
                $numFields = 0;

                if (($fieldsHandler = @fopen($fieldsFile, "r")) === false) {
                    $fieldsHandler = fopen(str_replace(".CSV", ".csv", $fieldsFile), "r");
                }

                if ($fieldsHandler !== false) {
                    while (($data = fgetcsv($fieldsHandler, 0, ";", chr(17))) !== false) {
                        $length = $data[15];
                        if (empty($length) || $length == 0) {
                            $length = 255;
                        }

                        $type = 'VARCHAR(' . $length . ')';
                        if ($data[17] != 0)
                            $type = 'DECIMAL(' . $data[16] . ', ' . $data[17] . ')';
                        else if ($data[16] != 0)
                            $type = 'INT';

                        $fieldName = mysql_escape_string($data[11]);
                        if ($dbname == 'AS400K_VMDAT') {
                            $fieldName = str_replace('#', '3', $fieldName);
                            $fieldName = str_replace('$', '4', $fieldName);
                        }

                        $query.= (($firstField) ? '' : ',') . '`' . $fieldName . '` ' . $type;
                        $firstField = false;

                        $numFields++;
                    }
                }
                $query.= ')';
                if (mysqli_query($link, $query) === true) {
                    writeLog('Table created: ' . $tableName);
                    writeLog('Table created: ' . $query);
                } else {
                    writeLog('[Failed] ' . $query);
                    writeLog('[Failed] Table creation: ' . $tableName);
                    return;
                }

                if (IGNORE_DATA)
                    continue;

                $counter = 0;
                if (($dataHandler = fopen($dataFile, "r")) !== false) {

                    while (($data = fgetcsv($dataHandler, 0, ";", '"')) !== false) {
                        set_time_limit(60);
                        sleep(10);
                        set_time_limit(60);
//                        if (count($data) != $numFields)
//                            writeLog('[Failed] Next query fails, to much fields.');

                        $insert = 'INSERT INTO `' . strtolower($tableName) . '` VALUES (';
                        $firstField = true;
                        $i = 0;
                        foreach ($data as $d) {
                            if (IGNORE_TOO_MUCH_FIELDS && ++$i > $numFields)
                                break;

                            $insert .= (($firstField) ? '' : ',') . "'" . trim(mysql_escape_string($d)) . "'";
                            $firstField = false;
                        }
                        $insert .= ')';
                        //echo $insert . '<br><br><br>';
                        if (false === mysqli_query($link, $insert)) {
                            writeLog('[Failed] (' . count($data) . ')' . $insert);
                        }

                        if (MAX_RECORDS != 0 && $counter++ > MAX_RECORDS)
                            break;
                    }
                }
            }
        }
        closedir($handle);
    }
}

function updateVmDat($link) {

    writeLog('Start updating vmdat');

    mysqli_select_db($link, 'as400k_vmdat');

    mysqli_query($link, 'ALTER TABLE  `$divppf` CHANGE  `4KY`  `4KY` VARCHAR( 10 ) CHARACTER SET latin1 COLLATE latin1_bin NULL DEFAULT NULL');
    mysqli_query($link, 'ALTER TABLE  `$divppf` ADD PRIMARY KEY (  `4TBNR` ,  `4SPRCH` ,  `4KY` )');

    mysqli_query($link, "ALTER TABLE  `stadrp` ADD PRIMARY KEY (  `ADR3A` )");
    mysqli_query($link, "ALTER TABLE  `stadrxp` ADD PRIMARY KEY (  `VNNR` )");
    mysqli_query($link, "ALTER TABLE  `stansp` ADD PRIMARY KEY (  `AD3KT` ,  `ADLF3` )");
    mysqli_query($link, "ALTER TABLE  `stavip` ADD PRIMARY KEY (  `AD3VN` )");
    mysqli_query($link, "ALTER TABLE  `stavnp` ADD PRIMARY KEY (  `AD3VN` )");
    mysqli_query($link, "ALTER TABLE  `aknzbp` ADD PRIMARY KEY (  `IDVT` ,  `NLFD` )");
    mysqli_query($link, "ALTER TABLE  `gdvzop` ADD PRIMARY KEY (  `VVN3` ,  `VT3` ,  `VT3R` )");
    mysqli_query($link, "ALTER TABLE  `hprsp` ADD PRIMARY KEY (  `SCHLPR` )");
    mysqli_query($link, "ALTER TABLE  `hps1p` ADD PRIMARY KEY (  `SCHLPR` ,  `FA` )");
    mysqli_query($link, "ALTER TABLE  `hps2p` ADD PRIMARY KEY (  `SCHLPR` ,  `SCHLSK` )");
    mysqli_query($link, "ALTER TABLE  `opdatp` ADD PRIMARY KEY (  `OKTO` ,  `OBELD` ,  `OPLFD` ,  `OPLFU` )");
    mysqli_query($link, "ALTER TABLE  `schadp` ADD PRIMARY KEY (  `VVR` ,  `SCHNR` )");
    mysqli_query($link, "ALTER TABLE  `schbsp` ADD PRIMARY KEY (  `SCHNR` ,  `SBLFD` )");
    mysqli_query($link, "ALTER TABLE  `s2000p` ADD PRIMARY KEY (  `VTA` ,  `IDVT` ,  `AEND` ,  `@LFD` )");
    mysqli_query($link, "ALTER TABLE  `staccp` ADD PRIMARY KEY (  `CDBHI` ,  `CDBHD` )");
    mysqli_query($link, "ALTER TABLE  `stadkp` ADD PRIMARY KEY (  `ADR3K` ,  `ANRD` )");
    mysqli_query($link, "ALTER TABLE  `stnzbp` ADD PRIMARY KEY (  `NADR3` ,  `NLFD` )");
    mysqli_query($link, "ALTER TABLE  `gdvzop` ADD  `STATIC` VARCHAR( 1 ) NOT NULL");
    mysqli_query($link, "UPDATE `gdvzop` SET `STATIC`='2' WHERE 1");
    mysqli_query($link, "ALTER TABLE  `opdmap` ADD PRIMARY KEY (  `MFA` ,  `MKTO` ,  `MBELD` ,  `MPLFD` ,  `MMALF` )");
    mysqli_query($link, "ALTER TABLE  `stbkp` ADD PRIMARY KEY (  `BBLZ` )");
    mysqli_query($link, "ALTER TABLE  `stsbp` ADD PRIMARY KEY (  `SB3` )");
    mysqli_query($link, "ALTER TABLE  `stspap` ADD PRIMARY KEY (  `SSPA3` )");

    mysqli_select_db($link, 'as400k_datawhouse ');

    mysqli_query($link, "ALTER TABLE  `polissen` ADD PRIMARY KEY (  `IDENTNR` )");
    mysqli_query($link, "ALTER TABLE  `produkten` ADD  `id` INT NOT NULL AUTO_INCREMENT FIRST , ADD PRIMARY KEY (  `id` )");

    writeLog('End updating vmdat');
}