Importcsv

<?php

class Importcsv extends Routing
{
    var $csvPath;
    var $csvPathFrom;
    var $csvPathTo;
    
    var $tablesDone=array();
    var $linesDone=array();
    var $LOFcount=array();
    
    var $tables_to_import=array();
    var $csv_only_this_line_from_end=array();
    
    var $codepageFrom;
    var $codepageTo;
    
    public function __construct() 
    {
        parent::__construct();
        
        $this->csvPathFrom = ROOT_DOC."csv_sym/csv/";
        $this->csvPathTo = ROOT_DOC."csv/";
        
        $this->tables_to_import=array(
            //'CARICO0F',
            //'RIORFO0F',
            //'ANAGR00F',
            'TABEL00F'
        );
        
        $this->csv_only_this_line_from_end=array(
            'CARICO0F'=>'25001',
            'RIORFO0F'=>'47001'
        );
        
        $this->files_to_merge=array(
            array('source'=>array('file_1_articoli.csv','file_2_articoli.csv'),'dest'=>'file_articoli.csv'),
            array('source'=>array('file_1_assegnazione.csv','file_2_assegnazione.csv'),'dest'=>'file_assegnazione.csv')
        );
        
        //print_r("Starting time: ".date($this->startTime.'d m Y H:i:s'));
        
        //$this->codepageFrom="IBM437";
        //$this->codepageFrom="IBM850";
        $this->codepageFrom="ISO-8859-1";
        
        $this->codepageTo="UTF8";
        
        $this->mergeFiles();exit;
        
        $this->changeCodepage();
        
        
        
        foreach ($this->tables_to_import AS $T)
        {
            $importResult = $this->importCsv($this->csvPathTo.$T.".csv", $T);
            $tablesDone[] = $T;
            $linesDone[] = $importResult['INScount'];
            $LOFcount[] = $importResult['LOFcount'];
        }
        
        $this->tablesDone = $tablesDone;
        $this->linesDone = $linesDone;
        $this->LOFcount = $LOFcount;
        
        $this->endTime=time();
        $difTime = $this->endTime-$this->startTime;
        $this->execTime = secondToTime($difTime);
        
        $this->printReport();
    }
    
    
    function mergeFiles()
    {
        //Merge of files
        foreach ($this->files_to_merge AS $T)
        {
            exec("> ".$this->csvPathTo.$T['dest']);
            $count=0;
            foreach ($T['source'] AS $Source)
            {
                if($count==0)
                    exec("cat ".$this->csvPathTo.$Source." > ".$this->csvPathTo.$T['dest']);
                else
                    exec("tail -n +2 ".$this->csvPathTo.$Source." >> ".$this->csvPathTo.$T['dest']);
                $count++;
            }
        }
    }

    function changeCodepage(){
        //Copy and change Codepage
        foreach ($this->tables_to_import AS $T)
        {
            
            @unlink($this->csvPathTo.$T.'.csv');
            exec('cp '.$this->csvPathFrom.$T.'.txt '.$this->csvPathTo.$T.'.csv');
            exec('iconv -f '.$this->codepageFrom.' -t '.$this->codepageTo.' '.$this->csvPathTo.$T.'.csv');
            //print_r($output2);
        }
    }
    
    function importCsv($file_path,$table_name,$max_execution_time=3000)
    {
        
        ini_set('max_execution_time', $max_execution_time);
        $c=0;
        $data=fopen($file_path,'r');
        
        $Q="TRUNCATE TABLE ".$table_name;
        mysql_query($Q,connect());
        
        
        //while($row=fgets($data)){
        $ERRcount=0;
        $INScount=0;
        $LOFcount=getLOF($file_path);
        
        $startByLine=0;
        $csv_OTLFE = $this->csv_only_this_line_from_end;
        if($csv_OTLFE[$table_name])
        {
            $startByLine = ($LOFcount-$csv_OTLFE[$table_name]);
        }
        
        while(!feof($data)){ 
        $row=fgetcsv($data,4096,',','"'); 
            $NOMI_CAMPI=array();
            if($c==0)
            {
                //$NOMI_CAMPI=explode(",",$row);
                $NOMI_CAMPI = $row;
                //print_r($row);
                $FIELD=array();
                $FIELD[]="id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ";
                $ONLYFIELDS="";$virgola="";
                foreach ($NOMI_CAMPI AS $NC)
                {
                    $FIELD[]=", ".$NC." VARCHAR(250) ";
                    $ONLYFIELDS.=$virgola." ".$NC."";$virgola=",";
                }
                    $Scrivi_FIELD="";
                    foreach ($FIELD AS $FF)
                    {
                        $Scrivi_FIELD.=$FF;
                    }
                    
            }
            else if ($c>$startByLine)
            {
                //print_r($row);exit;
                //$DATI=  explode(",", $row);
                $DATI = $row; 
                
                $VALUES = "NULL,";$virgola=""; 
                if(!empty($DATI))
                {
                    foreach ($DATI AS $D)
                    {
                        $D=$this->prepareValueForDB($D);
                        $VALUES.=$virgola." '".$D."' ";$virgola=",";
                    }
                }
                
                if(mysql_query("DESCRIBE ".$table_name." ",connect()) && !empty($DATI))
                {
                    //$Q='INSERT '.$table_name.' ('.$ONLYFIELDS.') VALUES ('.$VALUES.') ';
                    $Q='INSERT '.$table_name.' VALUES ('.$VALUES.') ';
                    //echo '<span style="color:GREEN;">'.$c.") --> ";print_r($VALUES);echo "</span><br><hr>";
                    
                    mysql_query("SET character_set_client=utf8", connect());
                    mysql_query("SET character_set_connection=utf8", connect());
                    mysql_query('set names utf8', connect());
                    
                    $INS=mysql_query($Q,connect());
                    if(!$INS)
                    {
                        print_r("SQL: ".$Q.PHP_EOL);
                        echo "\n".mysql_error().""."\n";
                        $ERRcount++;
                        //exit;
                    }else{
                        
                        $INScount++;
                    }
                    //Console Output 
                    print_r(''
                            .' - TAB: '.$table_name
                            .' - LOF: '.$LOFcount
                            .' - STR: '.$startByLine
                            .' - ERR: '.$ERRcount
                            .' - INS: '.$INScount
                            .PHP_EOL);
                }
            }
           $c++;
        }
        return array(
            'table_name'=>$table_name,
            'csv_file_path'=>$file_path,
            'LOFcount'=>$LOFcount,
            'INScount'=>$INScount,
            'ERRcount'=>$ERRcount);
    }
    
    
    private function printReport()
    {
        $CsvImportReport=New CsvImportReport($this);
        print_r(PHP_EOL);
        print_r($CsvImportReport);
                //."Execution Time: ".$this->execTime.PHP_EOL);
        //print_r("Ending: ".date($this->endTime.'d m Y H:i:s').PHP_EOL);
    }   
}

class CsvImportReport{
    var $tablesDone;
    var $LOFcount;
    var $linesDone;
    var $execTime;
    
    
    public function __construct($obj) {
        $this->tablesDone = $obj->tablesDone;
        $this->LOFcount = $obj->LOFcount;
        $this->linesDone = $obj->linesDone;
        $this->execTime = $obj->execTime;
    
        return $this;
        
    }
}