myLKW_import.class.php

Go to the documentation of this file.

<?php
namespace dbx\myLKW;
 
class myLKW_import {
 
 
/* =========================================================
   PUBLIC IMPORT
   ========================================================= */
 
public function import(){
 
    $oTPL = dbx()->get_system_obj('dbxTPL');
    $oDB  = dbx()->get_system_obj('dbxDB');
 
    $path_file = dbx_get_file_dir().'sys/csv/lkw.csv';
 
    if (!file_exists($path_file)) {
        return $oTPL->get_tpl('dbx|alert-danger',[
            'msg'=>"CSV Datei nicht gefunden: $path_file"
        ]);
    }
 
    $rows = $this->read_csv($path_file);
 
    if (!$rows) {
        return $oTPL->get_tpl('dbx|alert-warning',[
            'msg'=>"CSV Datei leer oder nicht lesbar"
        ]);
    }
 
    $ddFields = $oDB->get_dd_fields('lkw');
 
    $ddNames=[];
    foreach ($ddFields as $f) {
        if (!empty($f['name'])) {
            $ddNames[$f['name']] = 1;
        }
    }
 
    $oDB->empty('lkw');
 
    $count_insert=0;
    $count_skip=0;
 
    foreach ($rows as $row) {
 
        $record = $this->map_csv_row_to_lkw($row);
 
        /* LEERZEILEN ERKENNEN */
        if ($this->is_empty_lkw_row($record)) {
            $count_skip++;
            continue;
        }
 
        /* nur gültige DD Felder */
        $record = array_intersect_key($record,$ddNames);
 
        if (!$record) {
            $count_skip++;
            continue;
        }
 
        $ok = $oDB->insert('lkw',$record);
 
        if ($ok) $count_insert++;
        else     $count_skip++;
    }
 
    $msg['msg'] =
        "Import abgeschlossen.<br>".
        "Neu angelegt: $count_insert<br>".
        "Übersprungen: $count_skip";
 
    return $oTPL->get_tpl('dbx|alert-info',$msg);
}
 
 
/* =========================================================
   CSV LESEN
   ========================================================= */
 
protected function read_csv(string $file): array {
 
    $data=[];
    $sep=',';
 
    $fh=fopen($file,'rb');
    if(!$fh) return [];
 
    $bom=fread($fh,3);
    if($bom!=="\xEF\xBB\xBF"){
        rewind($fh);
    }
 
    $header=fgetcsv($fh,0,$sep);
 
    if(!$header){
        fclose($fh);
        return [];
    }
 
    foreach ($header as &$h) {
        $h = $this->normalize_str($h);
    }
 
    $fixed=[
        'DOMICILIO'=>'DOMICILIO',
        'TRACTOR'=>'TRACTOR',
        'I.T.V.TRACT'=>'ITV_TRACT',
        'TIPO'=>'TIPO',
        'REMOLQUE'=>'REMOLQUE',
        'I.T.V.REMOL.'=>'ITV_REMOL',
        'CONDUCTOR'=>'CONDUCTOR',
        'TELF.'=>'TELF',
        'EMPRESA'=>'EMPRESA',
        'EXT.'=>'EXT',
        'MANT.'=>'MANT',
        'EVENTOS'=>'EVENTOS',
        'BUJES'=>'BUJES',
        'VENCIMIENTO'=>'VENCIMIENTO',
        'ANOTACIONES'=>'ANOTACIONES',
        'ODOMETRO'=>'ODOMETRO',
    ];
 
    $fixedCols=[];
 
    foreach($header as $i=>$h){
        if(isset($fixed[$h])){
            $fixedCols[$fixed[$h]]=$i;
        }
    }
 
    if(!isset($fixedCols['TRACTOR'])){
        fclose($fh);
        return [];
    }
 
    $map=[
       'd0'=>['X','Y','Z','AA','AB'],
       'd1'=>['AD','AE','AF','AG','AH'],
       'd2'=>['AJ','AK','AL','AM','AN'],
       'd3'=>['AP','AQ','AR','AS','AT'],
       'd4'=>['AV','AW','AX','AY','AZ']
    ];
 
    $colIndex=[];
 
    foreach($map as $d=>$cols){
        foreach($cols as $c){
            $colIndex[$d][]=$this->excel_col_to_index($c);
        }
    }
 
    while(($row=fgetcsv($fh,0,$sep))!==false){
 
        if(!is_array($row)) continue;
 
        /* komplett leere CSV Zeilen überspringen */
        if(!array_filter($row)){
            continue;
        }
 
        $row=array_pad($row,140,'');
 
        foreach($row as &$v){
            $v=$this->normalize_str($v);
        }
 
        $rec=[];
 
        foreach($fixedCols as $f=>$idx){
            $rec[$f]=$row[$idx] ?? '';
        }
 
        foreach($colIndex as $d=>$idxs){
 
            $rec["__{$d}__"]=[
                'origen_region'=>$row[$idxs[0]] ?? '',
                'origen_lugar'=>$row[$idxs[1]] ?? '',
                'carga_region'=>$row[$idxs[2]] ?? '',
                'carga_lugar'=>$row[$idxs[3]] ?? '',
                'obs'=>$row[$idxs[4]] ?? '',
            ];
        }
 
        $data[]=$rec;
    }
 
    fclose($fh);
 
    return $data;
}
 
 
/* =========================================================
   UTF8 CLEAN
   ========================================================= */
 
protected function dbx_clean_utf8(string $s): string {
 
    if ($s === '') return '';
 
    $s = preg_replace('/^\xEF\xBB\xBF/', '', $s);
 
    for ($i = 0; $i < 5; $i++) {
 
        $prev = $s;
 
        $s = @iconv('UTF-8', 'Windows-1252//IGNORE', $s);
        $s = @iconv('Windows-1252', 'UTF-8//IGNORE', $s);
 
        if ($s === $prev) break;
    }
 
    $s = mb_convert_encoding($s, 'UTF-8', 'UTF-8');
 
    $s = preg_replace('/[\x00-\x1F\x7F]/u', ' ', $s);
 
    $s = str_replace("\xC2\xA0", ' ', $s);
 
    $s = preg_replace('/\s+/u', ' ', $s);
 
    return trim($s);
}
 
 
/* =========================================================
   NORMALIZER
   ========================================================= */
 
protected function normalize_str(?string $v,bool $upper=false): string {
 
    if($v===null) return '';
 
    $v=$this->dbx_clean_utf8($v);
 
    if($upper){
        $v=mb_strtoupper($v,'UTF-8');
    }
 
    return $v;
}
 
 
/* =========================================================
   LEERZEILE ERKENNEN
   ========================================================= */
 
protected function is_empty_lkw_row(array $record): bool {
 
    $fields=['TRACTOR','REMOLQUE','TIPO'];
 
    foreach ($fields as $f) {
 
        $v = (string)($record[$f] ?? '');
 
        /* alle whitespace entfernen */
        $v = preg_replace('/\s+/u','',$v);
 
        if ($v === '' || $v === '-' || $v === '.' || strtolower($v)==='null') {
            continue;
        }
 
        return false;
    }
 
    return true;
}
 
/* =========================================================
   EXCEL COL → INDEX
   ========================================================= */
 
protected function excel_col_to_index(string $col): int {
 
    $col=strtoupper(trim($col));
 
    $len=strlen($col);
    $num=0;
 
    for($i=0;$i<$len;$i++){
        $num=$num*26+(ord($col[$i])-64);
    }
 
    return $num-1;
}
 
 
/* =========================================================
   CSV → DB
   ========================================================= */
 
protected function map_csv_row_to_lkw(array $r): array {
 
    $out=[
        'DOMICILIO'=>$this->normalize_str($r['DOMICILIO'] ?? ''),
        'TRACTOR'=>$this->normalize_str($r['TRACTOR'] ?? '',true),
        'ITV_TRACT'=>$this->normalize_str($r['ITV_TRACT'] ?? ''),
        'TIPO'=>$this->normalize_str($r['TIPO'] ?? '',true),
        'REMOLQUE'=>$this->normalize_str($r['REMOLQUE'] ?? '',true),
        'ITV_REMOL'=>$this->normalize_str($r['ITV_REMOL'] ?? ''),
        'CONDUCTOR'=>$this->normalize_str($r['CONDUCTOR'] ?? ''),
        'TELF'=>$this->normalize_str($r['TELF'] ?? ''),
        'EMPRESA'=>$this->normalize_str($r['EMPRESA'] ?? ''),
        'EXT'=>$this->normalize_str($r['EXT'] ?? ''),
        'MANT'=>$this->normalize_str($r['MANT'] ?? ''),
        'EVENTOS'=>$this->normalize_str($r['EVENTOS'] ?? ''),
        'BUJES'=>$this->normalize_str($r['BUJES'] ?? ''),
        'VENCIMIENTO'=>$this->normalize_str($r['VENCIMIENTO'] ?? ''),
        'ANOTACIONES'=>$this->normalize_str($r['ANOTACIONES'] ?? ''),
        'ODOMETRO'=>$this->normalize_str($r['ODOMETRO'] ?? ''),
    ];
 
    foreach(['d0','d1','d2','d3','d4'] as $d){
 
        if(empty($r["__{$d}__"])) continue;
 
        $out["{$d}_origen_region"]=$this->normalize_str($r["__{$d}__"]['origen_region'] ?? '',true);
        $out["{$d}_origen_lugar"]=$this->normalize_str($r["__{$d}__"]['origen_lugar'] ?? '');
        $out["{$d}_carga_region"]=$this->normalize_str($r["__{$d}__"]['carga_region'] ?? '',true);
        $out["{$d}_carga_lugar"]=$this->normalize_str($r["__{$d}__"]['carga_lugar'] ?? '');
        $out["{$d}_observaciones"]=$this->normalize_str($r["__{$d}__"]['obs'] ?? '');
    }
 
    return $out;
}
 
 
/* =========================================================
   RUN
   ========================================================= */
 
public function run(){
 
    $modul=dbx()->get_system_var('dbx_activ_modul');
    $work=dbx()->get_modul_var('dbx_run2','import');
    $content="";
 
    switch($work){
 
        case 'import':
            $content=$this->import();
        break;
 
        default:
 
            $oTPL=dbx()->get_system_obj('dbxTPL');
 
            $content=$oTPL->get_tpl('dbx|alert-warning',[
                'msg'=>"Modul=($modul) Work=($work) is undef."
            ]);
    }
 
    return $content;
}
 
}