404

D7net
:/ var / www / html / rosario / adm / app / gre / Views / Cotacao /
Filename : VieCtImportaAgenda.php
back
<?php
ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL); 
ob_start();
$path = getcwd();
$Dados = $_POST;
include_once("conecta.php");
$anomes = $Dados['anomes'];
$data = $anomes.'-01';
$sql="select count(*) itens, max(st.nome) st_nome from ct_agenda_dia a
           left join ct_status st
            on st.id = a.status_id
        where a.anomes = '$anomes'
        and a.status_id <> 1";
$res = mysqli_query($link,$sql);
$row = mysqli_fetch_assoc($res);
    
//$_UP['pasta'] = '../../rosario/adm/';
$_UP['pasta'] = $_SERVER['DOCUMENT_ROOT']."/rosario/adm/";
// $_UP['pasta'] = $_SERVER['DOCUMENT_ROOT']."/rosario/upload/";
//var_dump($_UP['pasta']); 
// die;
// Tamanho máximo do arquivo (em Bytes)
$_UP['tamanho'] = 1024 * 1024 * 2; // 2Mb
// var_dump($Dados);

$arquivo = $Dados['id_arquivo'];
//echo '<pre>';
//$dir = $_SERVER['DOCUMENT_ROOT']."/rosario/adm/";
//$dir = $_SERVER['DOCUMENT_ROOT']."/rosario/";
$nome_final = $_UP['pasta'] .$_FILES['arquivo']['name'];
$path = $_FILES['arquivo']['name'];
$ext = pathinfo($path, PATHINFO_EXTENSION);
require $_SERVER['DOCUMENT_ROOT']."/rosario/adm/vendor/autoload.php";
use PhpOffice\PhpSpreadsheet\Spreadsheet;

use PHPOffice\phpspreadsheet\Writer\Xlsx;
use PHPOffice\phpspreadsheet\Shared\Date;
$row['itens'] = 0;
if ($row['itens'] < 1) {

if($ext <> 'xlsx') {
     $_SESSION['msg'] = "<div class='alert alert-danger' role='alert'>
                            ERRO: Planilha Precisa ser extensão xlxs!
                            <button type='button' class='close' data-dismiss='alert' aria-label='Close'>
                            <span aria-hidden='true'>&times;</span></button>
                       </div>";
    
}else{

//var_dump($_FILES['arquivo']);
//die;
// Depois verifica se é possível mover o arquivo para a pasta escolhida
if (!move_uploaded_file($_FILES['arquivo']['tmp_name'],  $nome_final)) {
$_SESSION['msg'] = "<div class='alert alert-danger' role='alert'>
                            ERRO: Erro no Upload!
                            <button type='button' class='close' data-dismiss='alert' aria-label='Close'>
                            <span aria-hidden='true'>&times;</span></button>
                        </div>";
}else{


$sqlw = "truncate table w_agenda;";
mysqli_query($link,$sqlw);

if(mysqli_query($link,$sqlw)){
    
//$reader = \PHPOffice\phpspreadsheet\IOFactory::createReader('Xlsx');
$reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader("Xlsx");    
//$reader = \PHPOffice/phpspreadsheet\src\IOFactory::createReader('Xlsx');
$reader->setReadDataOnly(TRUE);
$spreadsheet = $reader->load($arquivo );

$worksheet = $spreadsheet->getActiveSheet();
// Get the highest row and column numbers referenced in the worksheet
$highestRow = $worksheet->getHighestRow(); // e.g. 10
$highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
$highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn); // e.g. 5

$reg = "insert into w_agenda ( anomes, 
                                dia_semana,
                                marca_id,
                                comp_id,
                                tipo_compra,
                                frequencia,
                                dia_sem1,
                                dia_sem2,
                                dia_sem3,
                                dia_sem4,
                                dia_sem5
				  ) VALUES (";


$colunas =  $highestColumnIndex - 1;
If($highestColumnIndex > 11) {
// var_dump( '$highestRow ='.$highestRow ) ;      
for ($row = 2; $row <= $highestRow ; ++$row) {
 // var_dump('Row: '.$row);


    $reg.="'".$anomes."','";
    for ($col = 1; $col <= $highestColumnIndex; ++$col) {
        $value = $worksheet->getCellByColumnAndRow($col, $row)->getValue();
        if ($value == null) { $value = '0';};
        if ($value == '#N/A'){ $value = '0';};
        if($col == 1) {$reg.= $value."',";}
	if($col == 2) {$reg.= $value.",'";}
        if($col == 4) {$reg.= $value."','";}
        if($col == 6) {$reg.= $value."','";}
	if($col == 7) {$reg.= $value."',";
        }
        if ($col == 8 ) {
       //   echo ('$col 8= '.$value);
     
           if ($value == 0  ) {
               $reg.= "null,";   
            } else {  
	       $reg.= "'".Date::excelToDateTimeObject($value)->format('Y-m-d')."',";
            } 
        }   
       
        if ($col == 9) {
 //         echo ('$col 9= '.$value);
 //          die;
           if ($value == 0 ) {
               $reg.= "null,";   
              
           } else {
           $reg.= "'".Date::excelToDateTimeObject($value)->format('Y-m-d')."',";       
           }    
       }   
       if ($col == 10) {
 //         echo ('$col 9= '.$value);
 //          die;
           if ($value == 0) {
               $reg.= "null,";   
              
           } else {
           $reg.= "'".Date::excelToDateTimeObject($value)->format('Y-m-d')."',";       
           }    
       }
        if ($col == 11) {
 //         echo ('$col 9= '.$value);
 //          die;
           if ($value == 0 ) {
               $reg.= "null,";   
              
           } else {
           $reg.= "'".Date::excelToDateTimeObject($value)->format('Y-m-d')."',";       
           }    
       }
       if ($col == 12) {
 //         echo ('$col 9= '.$value);
 //          die;
           if ($value == 0 ) {
               $reg.= "null)";   
              
           } else {
           $reg.= "'".Date::excelToDateTimeObject($value)->format('Y-m-d')."')";       
           }    
       }
 
	}
		
	if($row < $highestRow ){
		$reg.= ",(";
		
	}else{
	   $reg.= ";";	
	}	
}
    }else{
        for ($row = 4; $row <= $highestRow ; ++$row) {


 
    $reg.="'".$anomes."','";
    for ($col = 1; $col <= $highestColumnIndex; ++$col) {
        $value = $worksheet->getCellByColumnAndRow($col, $row)->getValue();
      if ($value == '') { $value = '0';};
        if ($value == '#N/A'){ $value = '0';};
        if($col == 1) {$reg.= $value.",'";}
	if($col == 2) {$reg.= $value."','";}
        if($col == 4) {$reg.= $value."','";}
        if($col == 6) {$reg.= $value."','";}
	if($col == 7) {$reg.= $value."',";
      
        }
         if ($col == 8) {
 
           if ($value == 0 ) {
               $reg.= "null,";   
              
           } else {
            $reg.=  "'".Date::excelToDateTimeObject($value)->format('Y-m-d')."','";
           }
         }   
        if ($col == 9) {
 
           if ($value == 0) {
               $reg.= "null,";   
              
           } else {
           $reg.= "'".Date::excelToDateTimeObject($value)->format('Y-m-d')."',";       
           }    
       }   
       if ($col == 10) {
            if ($value == 0 ) {
               $reg.= "null,";   
              
           } else {
           $reg.= "'".Date::excelToDateTimeObject($value)->format('Y-m-d')."',";       
           }    
       }
        if ($col == 11) {
            if ($value == 0) {
               $reg.= "null,";   
              
           } else {
           $reg.= "'".Date::excelToDateTimeObject($value)->format('Y-m-d')."',";       
           }    
       }
       if ($col == 12) {
            if ($value == 0 ) {
               $reg.= "null)";   
              
           } else {
           $reg.= "'".Date::excelToDateTimeObject($value)->format('Y-m-d')."')";       
           }    
       }

	}
		
	if($row < $highestRow ){
		$reg.= ",(";
		
	}else{
	   $reg.= ";";	
	}	
//	echo '</tr>' . PHP_EOL;
		
}
    } 
//var_dump($reg);
//die;
if(mysqli_query($link,$reg)){
   $sqlv = "call ct_carga_agenda();"; 
      if(mysqli_query($link,$sqlv)){
         $_SESSION['msg'] = "<div class='alert alert-success' role='alert'>
                            Agenda Carregada com Sucesso!
                            <button type='button' class='close' data-dismiss='alert' aria-label='Close'>
                            <span aria-hidden='true'>&times;</span></button>
                        </div>";
        }else{
            $_SESSION['msg'] = "<div class='alert alert-danger' role='alert'>
                            ERRO: Procedure ct_carga_agenda !
                            <button type='button' class='close' data-dismiss='alert' aria-label='Close'>
                            <span aria-hidden='true'>&times;</span></button>
                        </div>";
        } 
	echo 'Registros gravados: '.$row.'<br>'. PHP_EOL;
}else{

	$_SESSION['msg'] = "<div class='alert alert-danger' role='alert'>
                            ERRO: Agenda não Carregada!
                            <button type='button' class='close' data-dismiss='alert' aria-label='Close'>
                            <span aria-hidden='true'>&times;</span></button>
                       </div>";
}	
//echo '</table>' . PHP_EOL;
mysqli_close($link);

}
}
}
$path      =  $_SERVER['DOCUMENT_ROOT'].'/rosario/adm/'.$_FILES['arquivo']['name'];
unlink($path);
}else{
    $_SESSION['msg'] = "<div class='alert alert-danger' role='alert'>
                            ERRO: Agenda já tem Movimento <b>{$row['st_nome']}</b> - Não permite Carregar novamente !
                            <button type='button' class='close' data-dismiss='alert' aria-label='Close'>
                            <span aria-hidden='true'>&times;</span></button>
                       </div>";
}
$UrlDestino = URLADM . 'con-ct-importa-agenda/listar';
header("Location: $UrlDestino");

?>