<?php 
date_default_timezone_set('America/La_Paz');
include('config/database.php');
$id_seseion = session_id();
if(isset($_POST['a'])){
    function getGeocodeData($address) { 
        $address = urlencode($address);     
        $googleMapUrl = "https://maps.googleapis.com/maps/api/geocode/json?address={$address}&key=AIzaSyDy0i22lqtG9KvJWgZsEHRewBOz1tl76Ig";
        $geocodeResponseData = file_get_contents($googleMapUrl);
        $responseData = json_decode($geocodeResponseData, true);
        if($responseData['status']=='OK') {
            $latitude = isset($responseData['results'][0]['geometry']['location']['lat']) ? $responseData['results'][0]['geometry']['location']['lat'] : "";
            $longitude = isset($responseData['results'][0]['geometry']['location']['lng']) ? $responseData['results'][0]['geometry']['location']['lng'] : "";
            $formattedAddress = isset($responseData['results'][0]['formatted_address']) ? $responseData['results'][0]['formatted_address'] : "";         
            if($latitude && $longitude && $formattedAddress) {         
                $geocodeData = array();                         
                array_push(
                    $geocodeData, 
                    $latitude, 
                    $longitude, 
                    $formattedAddress
                );             
                return $geocodeData;             
            } else {
                return false;
            }         
        } else {
            echo "ERROR: {$responseData['status']}";
            return false;
        }
    }
    
    $fecha_proceso=date('Y-m-d H:i:s');
    $usuario=$_SESSION['username'];
    $file = $_POST['archivo'];
    $isert_imp = mysqli_query($mysqli,"INSERT INTO importaciones VALUES(NULL,'$file',100,'$fecha_proceso','$usuario','Carga Exitosa')");
    $cod_cliente = $_POST['a'];
    $cliente = $_POST['b'];
    $pedido = $_POST['c'];
    $tel = $_POST['d'];
    $email = $_POST['e'];
    $dir_cliente = $_POST['f'];
    $ref = $_POST['g'];
    $tel_ref = $_POST['h'];
    $detalles = $_POST['i'];
    //Items
    $item = $_POST['j'];
    $cod_art = $_POST['k'];
    $articulo = $_POST['l'];
    $lote = $_POST['m'];
    $cantidad = $_POST['n'];
    $obs = $_POST['o'];
    
     for ($i=0; $i < count($cliente); $i++) {
        $query = mysqli_query($mysqli, "INSERT INTO temp_importaciones VALUES (NULL,'$cod_cliente[$i]','$cliente[$i]','$pedido[$i]','$tel[$i]','$email[$i]','$dir_cliente[$i]','$ref[$i]','$tel_ref[$i]','$detalles[$i]','$item[$i]','$cod_art[$i]','$articulo[$i]','$lote[$i]','$cantidad[$i]','$obs[$i]','$id_seseion')");
    }
    
    $sql_all=mysqli_query($mysqli, "SELECT * FROM temp_importaciones WHERE session_id='$id_seseion' GROUP BY codigo_cliente, pedido") or die('Error : '.mysqli_error($mysqli));
     while($row = mysqli_fetch_array($sql_all)){
         $cod_clien=$row['codigo_cliente'];
         $cli = $row['nombre_cliente'];
         $ped = $row['pedido'];
         $telr = $row['telefono_cliente'];
         $emailr = $row['email_cliente'];
         $dir = $row['direccion'];
         $refr = $row['referencia'];
         $tel_refr = $row['tel_referencia'];
         $det = $row['detalles'];
         //Generar consecutivo segun datos de la BBDD
        $query_id=mysqli_query($mysqli, "SELECT LAST_INSERT_ID(codigo_guia) as codigo FROM guias ORDER BY id_guia DESC LIMIT 1") or die('Error : '.mysqli_error($mysqli));   $count = mysqli_num_rows($query_id);
        if ($count <> 0) { $data_id = mysqli_fetch_assoc($query_id); $nro_guia= $data_id['codigo']+1;} else{ $nro_guia = 1000000000; }
        $geocodeData = getGeocodeData($dir); 
        $latitud = $geocodeData[0]; 
        $longitud = $geocodeData[1];
        $query = mysqli_query($mysqli, "INSERT INTO guias VALUES(NULL,'$nro_guia','$cod_clien','$cli','$ped','$telr','$emair','$dir','$latitud','$longitud','$refr','$tel_refr','$det','$fecha_proceso','$usuario','','','','','','','','','','','',100,0)");
        $isert_log = mysqli_query($mysqli,"INSERT INTO log_guias VALUES(NULL,'$nro_guia','Registro en el sistema','$fecha_proceso','$usuario')");
        //Generar consultar para identificar items y registrarlos a cada guia
        $sql_items=mysqli_query($mysqli, "SELECT * FROM temp_importaciones WHERE session_id='$id_seseion' AND codigo_cliente='$cod_clien' AND pedido='$ped'") or die('Error : '.mysqli_error($mysqli));
        while($items = mysqli_fetch_array($sql_items)){
            $item = $items['item'];
            $cod_art = $items['codigo_articulo'];
            $art = $items['nombre_articulo'];
            $lot = $items['lote'];
            $can = $items['cantidad'];
            $obs = $items['observacion'];
            $insert_item = mysqli_query($mysqli, "INSERT INTO guia_detalles VALUES(NULL,'$nro_guia','$item','$cod_art','$art','$lot','$can','$obs')") or die('Error : '.mysqli_error($mysqli));
        }
        
         
    }
    echo '
        <div class="alert alert-success" role="alert" id="alerta1">
            Los registros se han guardado satisfactoriamente!
            <button type="button" class="close" data-dismiss="alert" aria-label="Close">
                <span aria-hidden="true">&times;</span>
            </button>
        </div>
        <a href="?importacion" class="btn btn-primary btn-user btn-block"> <i class="fas fa-list"></i> Regresar</a>
        ';
    
    
   

}else{
$dir_subida = 'files/';
$fichero_subido = $dir_subida . basename($_FILES['file']['name']);
$nom_file =basename($_FILES['file']['name']);
move_uploaded_file($_FILES['file']['tmp_name'], $fichero_subido);
require_once 'lib/PHPExcel/Classes/PHPExcel.php';
$archivo = "$fichero_subido";
$inputFileType = PHPExcel_IOFactory::identify($archivo);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($archivo);
$sheet = $objPHPExcel->getSheet(0); 
$highestRow = $sheet->getHighestRow(); 
$highestColumn = $sheet->getHighestColumn();
?>
<div class="card mb-4">
    <div class="card-header font-weight-bold"><i class="fas fa-edit"></i> Listado de importacion de guias</div>
    <div class="card-body">
        <form class="form-horizontal" method="POST" action="?impo_guias" enctype="multipart/form-data">
        <table class="table table-bordered" width="100%" style="font-size:9px">
            <tr>
                <th>Cod_Cliente</th>
                <th>Cliente</th>
                <th>Pedido/factura</th>
                <th>Telefono</th>
                <th>Correo</th>
                <th>Direccion</th>
                <th>Referencia</th>
                <th>Tel Referencia</th>
                <th>Detalles</th>
                <th>L</th>
                <th>Cod-Art</th>
                <th>Articulo</th>
                <th>Lote/serie</th>
                <th>Cant</th>
                <th>Obs</th>
            </tr>
            <?php 
                for ($row = 2; $row <= $highestRow; $row++){ 
                        $a=$sheet->getCell("A".$row)->getValue();
                        $b=$sheet->getCell("B".$row)->getValue();
                        $c=$sheet->getCell("C".$row)->getValue();
                        $d=$sheet->getCell("D".$row)->getValue();
                        $e=$sheet->getCell("E".$row)->getValue();
                        $f=$sheet->getCell("F".$row)->getValue();
                        $g=$sheet->getCell("G".$row)->getValue();
                        $h=$sheet->getCell("H".$row)->getValue();
                        $i=$sheet->getCell("I".$row)->getValue();
                        $j=$sheet->getCell("J".$row)->getValue();
                        $k=$sheet->getCell("K".$row)->getValue();
                        $l=$sheet->getCell("L".$row)->getValue();
                        $m=$sheet->getCell("M".$row)->getValue();
                        $n=$sheet->getCell("N".$row)->getValue(); 
                        $o=$sheet->getCell("O".$row)->getValue();
                    ?>
            <tr>
                <td><?php echo $a; ?></td>
                <td><?php echo $b; ?></td>
                <td><?php echo $c; ?></td>
                <td><?php echo $d; ?></td>
                <td><?php echo $e; ?></td>
                <td><?php echo $f; ?></td>
                <td><?php echo $g; ?></td>
                <td><?php echo $h; ?></td>
                <td><?php echo $i; ?></td>
                <td><?php echo $j; ?></td>
                <td><?php echo $k; ?></td>
                <td><?php echo $l; ?></td>
                <td><?php echo $m; ?></td>
                <td><?php echo $n; ?></td>
                <td><?php echo $o; ?></td>
                
                <!--Listado de inputs hidden -->
                <input type='hidden' name="a[]" value="<?php echo $a; ?>" >
                <input type='hidden' name="b[]" value="<?php echo $b; ?>" >
                <input type='hidden' name="c[]" value="<?php echo $c; ?>" >
                <input type='hidden' name="d[]" value="<?php echo $d; ?>" >
                <input type='hidden' name="e[]" value="<?php echo $e; ?>" >
                <input type='hidden' name="f[]" value="<?php echo $f; ?>" >
                <input type='hidden' name="g[]" value="<?php echo $g; ?>" >
                <input type='hidden' name="h[]" value="<?php echo $h; ?>" >
                <input type='hidden' name="i[]" value="<?php echo $i; ?>" >
                <input type='hidden' name="j[]" value="<?php echo $j; ?>" >
                <input type='hidden' name="k[]" value="<?php echo $k; ?>" >
                <input type='hidden' name="l[]" value="<?php echo $l; ?>" >
                <input type='hidden' name="m[]" value="<?php echo $m; ?>" >
                <input type='hidden' name="n[]" value="<?php echo $n; ?>" >
                <input type='hidden' name="o[]" value="<?php echo $o; ?>" >
                <input type='hidden' name="archivo" value="<?php echo $nom_file; ?>" >
            </tr>
            
            <?php } ?>
        </table>
        <input type="submit" class="btn btn-primary btn-user btn-block" name="Guardar" value="Guardar Datos">
        </form>
    </div>
</div>
<?php } ?>