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');

//print_r($llx_product_schema);
//var_dump($llx_product_schema['ref_ext']);

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($csvFile, $table, $schema)
{
    $sql = '';
    if (($handle = fopen($csvFile, 'r')) !== FALSE) {
        $header_InsertInto = '';
        $rowCounter = 0;
        while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
            if ($rowCounter == 0) {
                $header_array = explode(';', $data[0]);
                $header_InsertInto = implode(',', $header_array);
            } else {
                $sql .= "INSERT INTO {$table} ({$header_InsertInto}) VALUES (";
                $fieldCounter = 0;
                $row_array = explode(';', $data[0]);
                $fieldsValues = [];
                foreach ($header_array as $field) {
                    //print $field . ' = ';
                    //var_dump($row_array[$fieldCounter]);
                    //print '<br>';
                    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 {
                            $fieldsValues[] = '\'' . $row_array[$fieldCounter] . '\'';
                        }
                    } 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) . ";" . '<br><br>';
            }
            $rowCounter++;
        }
        fclose($handle);
    } else {
        echo "Nem lehet megnyitni a fájlt!";
    }
    return $sql;
}

$csvFile = 'csv/llx_product_excelia_staging_Excel.csv';

$sql = productsCreator($csvFile, 'llx_product_temp', $llx_product_schema);

print $sql;