<?php if (!defined('BASEPATH')) exit('No direct script access allowed');


class UpExcel {
    
    private static $EXCEL;
    
    private static $modelo;
    private static $rowCabecalho;
    private static $rowIniDados;
    
    private static $pathTemp;
    private static $inputName;
    private static $file;
    
    private static $arrayEncode = [];
    private static $arrayDecimal = [];
    
    static private $CIs;
    
    function __construct() 
    {
        self::$CIs =& get_instance();
    }
    
    public static function ini($modelo = [], $rowCabecalho = 1, $rowIniDados = 2, $path = 'path/temporario')
    {
        self::$modelo = $modelo;
        self::$pathTemp = $path;
        self::$rowCabecalho = $rowCabecalho;
        self::$rowIniDados = $rowIniDados;
        
        self::setFile();
        self::setExcel();
        
        if(!empty($modelo))
            self::validaPlanilha();
    }
    
    public static function getCabecalho()
    {
        return UpExcel::getCamposRow(self::$rowCabecalho);
    }
    
    public static function remove()
    {
        $upDoc = self::$CIs->upload->data();
	    $file_path = './'.self::$pathTemp.'/'.$upDoc['file_name'];
        if(file_exists($file_path))
            unlink($file_path);
    }
    
    public static function camposEncode($newKey, $campos){
        self::$arrayEncode[$newKey] = $campos;
    }
    public static function camposDecimal($campos){
        self::$arrayDecimal = $campos;
    }
    
    public static function getDados()
    {
        $dados = [];
        
        $row = self::$rowIniDados;
        
        for($row; $row < self::countRows(); $row ++){
            $linhasPreenchidas = 0;
        	foreach (self::getCamposRow(self::$rowCabecalho) as $col=>$rowName){
                $keyDB = self::getKeyDB($rowName);
                $val = self::getValColRow($col,$row);
                
                $dados[$row][$keyDB] = $val;
                if($val != '')
                    $linhasPreenchidas++;
            }
            if($linhasPreenchidas == 0)
                unset($dados[$row]);
        }
        
        foreach ($dados as $k=>$row){
            
            $encode = [];
            
            foreach ($row as $key=>$value){
                
                if(in_array($key, self::$arrayDecimal))
                    $dados[$k][$key] = number_format(str_replace(',', '.', str_replace('.', '', $value)), 2, '.', '');
                
                foreach (self::$arrayEncode  as $newKey=>$campos){
                    if(in_array($key, $campos)){
                        $encode[$newKey][$key] = $value;
                        unset($dados[$k][$key]);
                    }
                    $dados[$k][$newKey] = json_encode($encode[$newKey]??[]);
                }
            }
        }
        return $dados;
    }
    
    
    public static function validaPlanilha()
    {
        $msgErro = self::getErroModelo();
    	if($msgErro != '')
    	{
    	    $upDoc = self::$CIs->upload->data();
    	    $file_path = './'.self::$pathTemp.'/'.$upDoc['file_name'];
            if(file_exists($file_path))
                unlink($file_path);
            throw new Exception($msgErro);
    	}
    }
    
    public static function file_tmp_name()
    {
        return self::$file['tmp_name'];
    }
    public static function file_name()
    {
        return self::$file['name'];
    }
    
    public static function file_name_convert()
    {
        $path_parts = pathinfo(self::$file['name']);
        $extension = $path_parts['extension'];
        return str_replace($extension, "", convertAccentsAndSpecialToNormal(self::$file['name'])).'.'.$extension;
    }
    
    
    public static function countRows()
    {
        return self::$EXCEL->getActiveSheet()->getHighestRow();
    }
    
    public static function getCampoExcel($nameDB, $row){
        foreach (self::getCamposRow(self::$rowCabecalho) as $col=>$rowName)
            if(self::getKeyDB($rowName) == $nameDB)
                return self::get_cell_value($col,$row);
        return null;
    }
    
    private static function getValColRow($col,$row)
    {
        return self::get_cell_value($col,$row);
    }
    
    private static function getKeyDB($rowName)
    {
        return self::$modelo[$rowName]??'';
    }
    
    private static function getErroModelo()
    {
        
        $arrayNamesEX = self::getCamposRow(self::$rowCabecalho);
        $return = '';
        $contErro = 0;
        $erros = '';
        
        foreach (self::$modelo as $nameEX=>$nameDB)
        {
            if(!in_array($nameEX, $arrayNamesEX)){
                $return .= "Campo '$nameEX' não foi encontrado na Planilha, não é compativel com modelo. \n";
                
                $erros .= " '$nameEX' \n";
                
                if($contErro > 0)
                    $return = "Estes campos devem conter na planilha: \n $erros";
                
               $contErro++; 
            }
        }
        return $return;
    }
    /*private static function getErroModelo()
    {
        foreach (self::getCamposRow(self::$rowCabecalho) as $k=>$val)
        {
            $valid = self::$modelo[trim($val)]??false;
            if($valid == false)
                return "Campo '$val' não foi encontrado no modelo. Planilha não é compativel.";
        }
        return '';
    }*/
    
    private static function getCamposRow($row)
    {
        $col = 1;
        $campos = [];
        while (true) {
            $campos[$col] = self::get_cell_value($col,$row);
            if($campos[$col] == '' || $campos[$col] == null)
            {
                unset($campos[$col]);
                break;
            }
               
            $col++;
        }
        return $campos;
    }
    
    private static function get_cell_value($column,$row)
	{
	    return self::$EXCEL->getActiveSheet()->getCellByColumnAndRow($column,$row)->getValue();
	}
    
    private static function setFile()
    {
        foreach ($_FILES as $inputName=>$file){
                $inputName = $inputName; $file = $file;
        }
        
        
        self::$inputName = $inputName;
        self::$file = $file;
    }
    
    private static function setExcel()
    {
        $path = self::$pathTemp;
        
        if (!isset(self::$file))
            throw new Exception("File not uploaded");
        if(!is_dir('./'.$path.'/'))
            mkdir('./'.$path.'/', 0774, true);
        $config['upload_path']          = './'.$path.'/';
        $config['allowed_types']        = 'xlsx';
        
        self::$CIs =& get_instance();
        self::$CIs->load->library('upload', $config);
        
        if (!self::$CIs->upload->do_upload(self::$inputName))
            throw new Exception("Erro ao importar arquivo");
        $upDoc = self::$CIs->upload->data();
        $inputFileName = '../html/'.$path.'/'.$upDoc['file_name'];
         
        self::$EXCEL =  \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName);
    }
    
    
    
    
    
    
    
}