EXCELIA_DBARR/custom/bbus/product_handler.php

<?php
$res = 0;
// Try main.inc.php into web root known defined into CONTEXT_DOCUMENT_ROOT (not always defined)
if (!$res && !empty($_SERVER["CONTEXT_DOCUMENT_ROOT"])) {
    $res = @include $_SERVER["CONTEXT_DOCUMENT_ROOT"] . "/main.inc.php";
}
// Try main.inc.php into web root detected using web root calculated from SCRIPT_FILENAME
$tmp = empty($_SERVER['SCRIPT_FILENAME']) ? '' : $_SERVER['SCRIPT_FILENAME'];
$tmp2 = realpath(__FILE__);
$i = strlen($tmp) - 1;
$j = strlen($tmp2) - 1;
while ($i > 0 && $j > 0 && isset($tmp[$i]) && isset($tmp2[$j]) && $tmp[$i] == $tmp2[$j]) {
    $i--;
    $j--;
}
if (!$res && $i > 0 && file_exists(substr($tmp, 0, ($i + 1)) . "/main.inc.php")) {
    $res = @include substr($tmp, 0, ($i + 1)) . "/main.inc.php";
}
if (!$res && $i > 0 && file_exists(dirname(substr($tmp, 0, ($i + 1))) . "/main.inc.php")) {
    $res = @include dirname(substr($tmp, 0, ($i + 1))) . "/main.inc.php";
}
// Try main.inc.php using relative path
if (!$res && file_exists("../main.inc.php")) {
    $res = @include "../main.inc.php";
}
if (!$res && file_exists("../../main.inc.php")) {
    $res = @include "../../main.inc.php";
}
if (!$res && file_exists("../../../main.inc.php")) {
    $res = @include "../../../main.inc.php";
}
if (!$res) {
    die("Include of main fails");
}

$llx_product_schema = getSchema('llx_product');
$llx_product_extrafields_schema = getSchema('llx_product_extrafields');
$llx_product_price_schema = getSchema('llx_product_price');
$llx_product_association_schema = getSchema('llx_product_association');

function getSchema($table)
{
    global $db;

    $array = [];
    $sql_llx_product_schema = "SELECT column_name, data_type
    FROM information_schema.columns
    WHERE table_name = '{$table}'";

    $result = $db->query($sql_llx_product_schema);
    while ($row = $db->fetch_object($result)) {
        $array[$row->column_name] = $row->data_type;
    }
    return $array;
}

function productsCreator($table, $schema)
{
    $sql = '';
    $header_array = [];
    if (($handle = fopen('csv/' . $table . '_excelia_staging_Excel.csv', 'r')) !== FALSE) {
        $header_InsertInto = '';
        $rowCounter = 0;
        if ($table == 'llx_product_association') {
            $sql .= "DELETE FROM llx_product_association;";
            //$sql .= '<br><br>';
        }
        while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
            if ($rowCounter == 0) {
                $header_array = explode(';', $data[0]);
                $header_InsertInto = implode(',', $header_array);
            } else {
                $fieldCounter = 0;
                $row_array = explode(';', $data[0]);
                $fieldsValues = [];
                if ($table == 'llx_product_extrafields') {
                    $sql .= "DELETE FROM llx_product_extrafields WHERE fk_object = {$row_array[2]};";
                    //$sql .= '<br><br>';
                }
                if ($table == 'llx_product_price') {
                    $sql .= "DELETE FROM llx_product_price WHERE fk_product = {$row_array[3]};";
                    //$sql .= '<br><br>';
                }
                $sql .= "INSERT INTO {$table} ({$header_InsertInto}) VALUES (";
                foreach ($header_array as $field) {

                    if ($schema[$field] == 'character varying' || $schema[$field] == 'timestamp without time zone' || $schema[$field] == 'text') {
                        if ($row_array[$fieldCounter] == "NULL") {
                            $fieldsValues[] = 'NULL';
                        } elseif ($row_array[$fieldCounter] == "") {
                            $fieldsValues[] = '\'\'';
                        } else {
                            if (!mb_check_encoding($row_array[$fieldCounter], 'UTF-8')) {
                                $row_array[$fieldCounter] = iconv('ISO-8859-2', 'UTF-8', $row_array[$fieldCounter]);
                            }
                            //print $row_array[$fieldCounter] . '<br>';
                            $fieldsValues[] = '\'' . $row_array[$fieldCounter] . '\'';
                        }
                    } else {
                        if ($field == 'fk_user_author') {
                            $fieldsValues[] = 1;
                        } else {
                            $fieldsValues[] = $row_array[$fieldCounter];
                        }
                    }
                    $fieldCounter++;
                }

                $sql .= implode(',', $fieldsValues);
                $sql .= ") ON CONFLICT (rowid) DO ";
                $sql .= "UPDATE SET ";
                $updatFieldsValues = [];
                foreach ($header_array as $field) {
                    if ($field != 'rowid') {
                        $updatFieldsValues[] = $field . ' = EXCLUDED.' . $field;
                    }
                }
                $sql .= implode(', ', $updatFieldsValues) . ";";
                //$sql .= "<br><br>";
                //}

            }
            $rowCounter++;
        }
        fclose($handle);
    } else {
        echo "Nem lehet megnyitni a fájlt!";
    }
    return $sql;
}

function runQuery($sql, $table)
{
    global $db;
    echo 'START query runnning: <b>' . $table . '</b><br>';
    $sql_commands = explode(';', $sql);
    foreach ($sql_commands as $sql_command) {
        $sql_command = trim($sql_command); // Üres parancsok eltávolítása
        if (!empty($sql_command)) {
            $resql = $db->query($sql_command);
            if (!$resql) {
                //echo $sql . '<br>';
                echo '<br>';
                echo "Hiba történt: " . $db->lasterror() . '<br>';
                echo $sql_command . '<br><br>';
            }
        }
    }
    echo 'KÉSZ!<br><br>';
}

$files = ['llx_product_excelia_staging_Excel', 'llx_product_extrafields_excelia_staging_Excel', 'llx_product_price_excelia_staging_Excel', 'llx_product_association_excelia_staging_Excel'];
print '<form method="post" action="' . $_SERVER["PHP_SELF"] . '">';
print '<input type="hidden" name="token" value="' . newToken() . '">';
if (isset($_REQUEST['submit_button'])) {
    $debug = false;

    $sql_llx_product = productsCreator('llx_product', $llx_product_schema);
    $sql_llx_product_extrafields = productsCreator('llx_product_extrafields', $llx_product_extrafields_schema);
    $sql_llx_product_price = productsCreator('llx_product_price', $llx_product_price_schema);
    $sql_llx_product_association = productsCreator('llx_product_association', $llx_product_association_schema);

    if ($debug) {
        print $sql_llx_product;
        print $sql_llx_product_extrafields;
        print $sql_llx_product_price;
        print $sql_llx_product_association;
    } else {
        runQuery($sql_llx_product, 'llx_product');
        runQuery($sql_llx_product_extrafields, 'llx_product_extrafields');
        runQuery($sql_llx_product_price, 'llx_product_price');
        runQuery($sql_llx_product_association, 'llx_product_association');

        echo 'KÉSZ!';
    }
    print '<input type="submit" name="done" value="OK">';
} elseif ($_REQUEST['done'] || !isset($_REQUEST['submit_button'])) {
    foreach ($files as $file) {
        if (file_exists('csv/' . $file . '.csv')) {
            print $file . '.csv - <font style="color: green;"><b>OK</b></font><br>';
        } else {
            print $file . '.csv - <font style="color: RED;"><b>PROBLEM</b></font><br>';
        }
    }
    print '<br><br>';
    print '<input type="submit" name="submit_button" value="Termékek és szolgáltatások generálása!">';
}
print '</form>';