<?php
// require_once("xlsphp/Classes/PHPExcel.php");
include 'xlsphp/Classes/PHPExcel/IOFactory.php';
// ini_set('memory_limit', '1024M');
Class Motor{

  var $obj;
  var $sheet;
  var $objReader;
  var $highestRow;
  var $inputFileType;
  var $highestColumn;
  var $numberedRecords;
  var $headers = [['KodPel','Eponumia_pel','Dieuthunsi','Poli','TK','AFM','Til_Ergasias','Til_Epikoin','Kod_paragogou','Asfalizomenos','AFM_asfalizomenou','Titlos_sumbolaiou','No_Sumb','No_Anan','No_PrPr','Im_ekdosis','Im_apo','Im_mexri','Tropos_Pliromis',
    'PrPr_Apo','PrPr_Eos','Zoni','Pinakida','Marka','Xrisi','Ippoi','Theseis','Etos_katask','BM','Kathara_asf','Mikta_asfalistra','Promitheies_sunergati','Akuro','key0'],['Klados','Kod_Kal','Kalupsi','Kefalaio','Kathara','key0'],
    ['ELOA','ELIM','ELKA','ELXOA','ELA','ELLS','EE','ELPA','Epibnsi_LILI','Epibnsi_LDIP','Epibnsi_LPIN','Epibnsi_LTOYR','Olika','key0']];

  function __construct($file){
    try{
      	$this->inputFileType = PHPExcel_IOFactory::identify($file);
      	$this->objReader = PHPExcel_IOFactory::createReader($this->inputFileType);
      	$this->obj = $this->objReader->load($file);
      	$this->sheet = $this->obj->getSheet(0); 
      	$this->highestRow = $this->sheet->getHighestRow();
      	$this->highestColumn = PHPExcel_Cell::columnIndexFromString($this->sheet->getHighestColumn());
    }
    catch(Exception $e){
      	die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
    }    
    $x=1;
    for($i=1;$i<=$this->highestRow && $i!='EOF';$i=$this->get_info($i)['next']){
      	$next = $this->get_info($i)['start'];
      	$this->numberedRecords[$x] = $next;
    	$x++;
    }
    // echo $this->numberedRecords[0];
  }

   public function get_cell($row=1, $col=0){
    $sheet = $this->obj->getSheet(0); 
    $cell = $sheet->getCellByColumnAndRow($col, $row)->getFormattedValue();
    $split = explode('-',$cell);
    if(count($split)>2){
      $cell = $split[1].'-'.$split[0].'-20'.$split[2];
    }
    return $cell; 
   }

  public function get_line($gr=false,$row=1,$start=0,$end=0){
    if($end==0){
      $end=$this->highestColumn;
    }
    $line = [];
    $x = 0;
    if($gr==false){
      for($i=$start;$i<=$end;$i++){
        $line[$x] = $this->get_cell($row,$i);
        $x++;
      }
    } else {
      for($i=$start;$i<=$end;$i++){
        $line[$x] = $this->greeklish($this->get_cell($row,$i));
        $x++;
      }
    }
    
    return $line;
  }

  public function get_dict($gr=false,$line=1,$start=0,$end=0,$head=false){
  	if($head===false){
  		$arr1 = $this->get_line($gr,$line,$start,$end);
  	} else {
  		$arr1 = $head;
  	}
  	$arr2 = $this->get_line(false,$line+1,$start,$end);
    $size = count($arr1);
    $dict = [];
    for($i=0;$i<$size;$i++){
      $dict[$arr1[$i]]=$arr2[$i];
    }
    return $dict;
  }

  function get_inner($start=1,$headers=false){
    $inner = [];
    $col = [];
    $info = $this->get_info($start);
     for($x=1;$x<=6;$x++){
        $y=0;
        for($i=$info['inner-start']+1;$i<=$info['inner-end'];$i++){
          $col[$y] = $this->get_cell($i, $x);
          $y++;
        }
        if($head==false){
        	$inner[$this->greeklish($this->get_cell($info['inner-start'],$x))] = $col;
        } else {
        	$inner[$this->headers[1]] = $col;
        }        
      }
    return $inner;
  }

  public function greeklish($Name){  
    $greek = array('α','ά','Ά','Α','β','Β','γ', 'Γ', 'δ','Δ','ε','έ','Ε','Έ','ζ','Ζ','η','ή','Η','θ','Θ','ι','ί','ϊ','ΐ','Ι','Ί', 
      'κ','Κ','λ','Λ','μ','Μ','ν','Ν','ξ','Ξ','ο','ό','Ο','Ό','π','Π','ρ','Ρ','σ','ς', 'Σ','τ','Τ','υ','ύ','Υ','Ύ','φ','Φ','χ','Χ','ψ','Ψ','ω','ώ','Ω','Ώ',' ',"'","'",',','.','/'); 
    $english = array('a', 'a','A','A','b','B','g','G','d','D','e','e','E','E','z','Z','i','i','I','th','Th', 'i','i','i','i','I','I',
      'k','K','l','L','m','M','n','N','x','X','o','o','O','O','p','P' ,'r','R','s','s','S','t','T','u','u','Y','Y','f','F','x','X','ps','Ps','o','o','O','O','_','_','_','_','',''); 
    $string  = str_replace($greek, $english, $Name); 
    return $string; 
  } 

  public function get_info($start=1){
    $highestRow = $this->highestRow; 
    $head = $this->get_cell(1,1);
    for($i=$start;$i<=$highestRow+1;$i++){
      if(($this->get_cell($i, 1)==$head && $i!=$start) || $i>$highestRow){
        if($i>$highestRow){
          $i++;
        }
        $lines = $start*2>$i ? $i-$start : $i-1;
        return ['start'=>$start,'end'=>$i-1,'lines'=>$lines,'inner-lines'=>$lines-7,'inner-start'=>$start+3,'inner-end'=>$start+($lines-5),'inner2-start'=>$start+($lines-3),'inner2-end'=>$start+($lines-2),'next'=>($i>=$highestRow ? 'EOF' : $i)];
      }
    }
    // return "EOF";
  }

  public function to_db($dbhost,$dbuser,$dbpass,$dbname,$start=1,$limit=0){
    ini_set('max_execution_time', 0);
    $conn = mysql_connect($dbhost, $dbuser, $dbpass);
    if(! $conn ){
     die('Could not connect: ' . mysql_error());
    }
    mysql_select_db($dbname);
    mysql_set_charset('utf8',$conn);
   // echo 'Connected successfully';
    $qinsert = ['insert into asf_pelates','insert into asf_details','insert into asf_details2'];
    $records = $this->get_records($start,$limit);
    $count = 1;
    foreach($records as $record){
      $error = '';
      $success = 0;
      $fail = 0;
      // PELATES
      $headers = '(';
      $cells = ' VALUES (';
      foreach ($record[0] as $head => $cell) {
        $headers.=$head;
        $cells.="'".$cell."'";
        if(($head=='key0' && !isset($record[0]['Diatrapezikos_kodikos_pliromis'])) || $head=='Diatrapezikos_kodikos_pliromis'){
          $headers.=")";
          $cells.=")";
          break;
        } else {
          $headers.=",";
          $cells.=",";
        }  
        
      }

      $query = $qinsert[0].$headers.$cells;
      if(mysql_query($query)){
        $success++;
      } else{
        $error .= mysql_error().'&nbsp&nbsp';
        $fail++;
      }

// DETAILS
    for($i=0;$i<sizeof($record[1]['Klados']);$i++){
      $headers = "(KodPel,Pinakida,Im_mexri,";
      $cells = " VALUES (".$record[0]['KodPel'].",'".$record[0]['Pinakida']."','".$record[0]['Im_mexri']."',";
      foreach ($record[1] as $head => $cell) {

        if($cell===end($record[1])){
          $headers.=$head.")";
          $cells.="'".$cell[$i]."')";
        } else {
          $headers.=$head.",";
          $cells.="'".$cell[$i]."',";
        }  
      }
      // print_r($record[1]);
      $query2 = $qinsert[1].$headers.$cells;
      // echo $query2.'<br>';
      if(mysql_query($query2)){
        $success++;
      } else{
        $error .= mysql_error().'&nbsp&nbsp';
        $fail++;
      }
    }

// ASF_DETAILS2
    $headers = "(KodPel,Pinakida,Im_mexri,";
    $cells = " VALUES (".$record[0]['KodPel'].",'".$record[0]['Pinakida']."','".$record[0]['Im_mexri']."',";
    foreach ($record[2] as $head => $cell) {
      if($cell===end($record[2])){
        $headers.=$head.")";
        $cells.="'".$cell."')";
      } else {
        $headers.=$head.",";
        $cells.="'".$cell."',";
      }  
    }
    $query3 = $qinsert[2].$headers.$cells;

    if(mysql_query($query3)){
      $success++;
    } else{
      $error .= mysql_error().'&nbsp&nbsp';
      $fail++;
    }
    // echo $query.'<br>'.$query2.'<br>'.$query3;
    if($fail==0){
      echo $count.'&nbsp<b style="color:green;">Success</b> ('.$record[0]['KodPel'].' <b>||</b> '.$record[0]['Eponumia_pel'].')<br>';
      header('Location: index0.php?status=done');
    } 
    elseif($fail==3){
      echo $count.'&nbsp<b style="color:red;">Failed</b> ('.$record[0]['KodPel'].' <b>||</b> '.$record[0]['Eponumia_pel'].')&nbsp'.$error.'<br>';
    }
    else{
      echo $count.'&nbsp<b style="color:orange;">Partial-Fail</b> ('.$record[0]['KodPel'].' <b>||</b> '.$record[0]['Eponumia_pel'].')&nbsp'.$error.'<br>';
    }
    $count++;

  }
}

  public function get_records($start=1, $limit=0){
    $rec = [];
    $end = $this->highestRow;
    $l = 1; 
    for($i=$start;$i<=$end && $i!='EOF';$i=$this->get_info($i)['next']){
      array_push($rec, $this->get_record_array($i));
      if(($l >= $limit && $limit!=0)){break;} else {$l++;}
    }
    return $rec;    
  }

  public function get_record_array($start){
    $one = $this->get_dict(false,$start,0,0,$this->headers[0]);
    $two = $this->get_inner($start);
    $three = $this->get_dict(false,$this->get_info($start)['inner2-start'],1,15,$this->headers[2]);
    return [$one,$two,$three];
  }

  public function debug($start=1, $limit=0){
    // print_r($this->get_info($start));
    $records = $this->get_records($start, $limit);
    $x=1;
    foreach ($records as $record) {
      echo "<div style='border:solid 1px black;width:50px;text-align:center;font-size:20px;background-color:darkgray;color:darkred;'>".$x."</div>";
      echo "<br>--------------<br>";
      echo "<b>asf_pelates</b><br>";
      echo "--------------<br><br>";
      foreach ($record[0] as $head => $cell) {
        echo $head.' => '.$cell.'<br>';
      }
      echo "<br>--------------<br>";
      echo "<b>asf_details</b><br>";
      echo "--------------<br><br>";
      foreach ($record[1] as $key => $value) {
        echo $key.' => ';
        foreach ($value as $val) {
          echo $val.' <b>||</b> ';
        }
        echo "<br>";
      }
      echo "<br>---------------<br>";
      echo "<b>asf_details2</b><br>";
      echo "---------------<br><br>";
      foreach ($record[2] as $head => $cell) {
        echo $head.' => '.$cell.'<br>';
      }
      echo "<br>";
      $x++;
    }
  }
}

Class BlueByte{
	var $obj;
  	var $sheet;
  	var $sheet_count;
  	var $objReader;
  	var $highestRow;
  	var $inputFileType;
  	var $highestColumn;
  	var $numberedRecords;

  	function __construct($file){
    	try{
      		$this->inputFileType = PHPExcel_IOFactory::identify($file);
      		$this->objReader = PHPExcel_IOFactory::createReader($this->inputFileType);
      		$this->obj = $this->objReader->load($file);
      		$this->sheet_count = $this->obj->getSheetCount();
      		for($i=0;$i<$this->sheet_count;$i++){
      			$this->sheet[$i] = $this->obj->getSheet($i);
      			$this->highestRow[$i] = $this->sheet[$i]->getHighestRow();
      			$this->highestColumn[$i] = PHPExcel_Cell::columnIndexFromString($this->sheet[$i]->getHighestColumn());
      		}
    	}
    	catch(Exception $e){
      		die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
    	}
  	}

  	public function get_dict($sheet=0){
  		$Fields = [['','','No_Sumb','No_Anan','Katigoria', 'Im_ekdosis','Im_apo','Im_mexri','Pinakida','Mikta_asfalistra','Kathara_asf','KodPel','Eponumia_pel','Dieuthunsi','TK','Poli','Til_Epikoin','Fax','Im_gennisis','Titlos','AFM','AT','Titlos_DOY','Patronumo','Praktoras','Klados','Ar_Protokolou','Marka','Ippoi','Xrisi','Bonus_Malus','Apalagi','Omadiki_Ekptosi','Ekptosi_idiotitas','Epasfalistra','Ypopraktoras','Kod_Paketou','Ekptosi_me_meiosi_Promitheias','ArProtasis'],['','HEADER','No_sumb','No_Anan','Kod_kal','Kefalaio','Mikta_asfalistra','Kathara','Klados','Akuro']];

  	$HR = $this->highestRow[$sheet];
		$dict = [];
		for($i=2;$i<=$HR;$i++){
			if($this->get_cell($i, 2)!=''){
				$dict[$Fields[$sheet][$i]] = $this->get_cell($i, 2, $sheet);
			}
		}
		return $dict;
  	}

  	public function get_whole_dict(){
  		$dict[0] = $this->get_dict();
  		$dict[1] = $this->get_dict(1);
  		// print_r($dict[1]);
  		return $dict;
  	}

  	public function get_cell($row=1, $col=0, $sheet=0){
    	$sheet = $this->obj->getSheet($sheet); 
    	$cell = $sheet->getCellByColumnAndRow($col, $row)->getFormattedValue();
    	// $split = explode('-',$cell);
    	// if(count($split)>1){
     //  		$cell = $split[1].'-'.$split[0].'-20'.$split[2];
    	// }
   		return $cell; 
   }

   public function to_db($dbhost,$dbuser,$dbpass,$dbname){
   		$conn = mysql_connect($dbhost, $dbuser, $dbpass);
    	if(! $conn ){
     		die('Could not connect: ' . mysql_error());
    	}
    	mysql_select_db($dbname);
    	mysql_set_charset('utf8',$conn);
   		$err=0;
   		$err_desc = '';
   		$qry = ['INSERT INTO asf_pelates (','INSERT INTO asf_details (Im_mexri,Pinakida,KodPel,'];
   		$cells=['',''];
   		$xtra_info = '';
   		$dict = $this->get_whole_dict();
   		$y=0;
   		foreach ($dict as $sheet) {
   			foreach ($sheet as $head => $cell) {

   				if($head=='ArProtasis' || $head=='Akuro'){
   					$qry[$y].=$head.') VALUES ( ';
   					$cells[$y].='"'.str_replace('"', '', $cell).'")';
   				} else {
   					if($y==0 && ($head=='Im_mexri' || $head=='Pinakida' || $head=='KodPel')){
   						$xtra_info.='"'.str_replace('"', '', $cell).'",';
   					}
            if($head!=''){
               $qry[$y].=$head.',';
            }
            if($cell!=''){
               $cells[$y].='"'.str_replace('"', '', $cell).'",';
            }    
   				}
          // echo $head.' => '.$cell.'<br>';
   			}

   			$y++;
   		}
   		// echo $qry[0].$cells[0].'<br>--------------<br>';
   		// echo $qry[1].$xtra_info.$cells[1];
   		if(!mysql_query($qry[0].$cells[0])){
   			$err++;
   			$err_desc.=mysql_error().' || ';
   		}
   		if(!mysql_query($qry[1].$xtra_info.$cells[1])){
   			$err++;
   			$err_desc.=mysql_error().' || ';
   		}
   		if($err==0){
   			echo "<font style='color:green;'>Success!</font>";
        header('Location: index0.php?status=done');
   		} elseif($err>0 && $err<2) {
   			echo "<font style='color:orange;'>Partial-Success</font> ".$err_desc."";
   		} else {
   			echo "<font style='color:red;'>Failed</font> ".$err_desc."";
   		}
   }

}


// $dbhost = 'localhost';
// $dbuser = 'root';
// $dbpass = '';
// $dbname = 'asfaleies';
// $XL = new Bluebyte('files/new/BlueByte_protaseis.xls');
// $XL->debug();
// print_r($XL->headers);
// foreach ($dictionary as $dict) {
// 	foreach ($dict as $key => $value) {
// 		echo $key.' => '.$value.'<br>';
// 	}
// 	echo "<br>----------<br>";
// }
// $XL->to_db($dbhost,$dbuser,$dbpass,$dbname);

?>
