<?php
    session_start();
    $usuarioSession = $_SESSION['USUARIO'];
    $grupo = $_SESSION['GRUPO'];
    $contacto = $_SESSION['CONTACTO'];
    $awa_session_id = $_SESSION['AWA_SESION_ID'];
    include('../lg/config_vo.php');
    include('../includes/conex.php');
    include('../includes/eat.php');
    include('../includes/historial.php');
    include('../includes/functions.php');
    include('../includes/esquemas.php');
    include('../class/Schema.php');
    include('../class/MotherCar.php');
    include('../class/Static.php');
    include('../class/Agenda.php');
    include('../class/Rgpd.php');
    include('../class/Bibliotecas.php');
    include_once("../assets/excel/PHPExcel/IOFactory.php");

    if(!isset($usuarioSession) OR $awa_session_id != $awa_id) {
        eatCookies();
        header('Location:../index.php');
    }
    if(isset($_COOKIE['usuario']) AND $_COOKIE['usuario'] != $staticUsu ) {
        eatCookies();
        header('Loction:../index.php');
    }
    if (isset($_COOKIE['idioma'])) {
        include('../lg/LG_' . $_COOKIE['idioma'] . '.php');
    } else {
        include('../lg/LG_SP.php');
    }
    $Schema = new Schema($con);
    $MotherCar = new MotherCar($con);

    function cellColor($cells,$color){
        global $objPHPExcel;
    
        $objPHPExcel->getActiveSheet()->getStyle($cells)->getFill()->applyFromArray(
            array(
            'type' => PHPExcel_Style_Fill::FILL_SOLID,
            'startcolor' => array(
                 'rgb' => $color
            )
        ));
    }

    $objPHPExcel = new PHPExcel();
    $objPHPExcel->setActiveSheetIndex(0);
    $letters = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N'];
    foreach($letters as $l) {
        $objPHPExcel->getActiveSheet()->getColumnDimension($l)->setAutoSize(false);
        $objPHPExcel->getActiveSheet()->getColumnDimension($l)->setWidth("30");
        cellColor($l . 1, 'e5e5e5');
    }


    $objPHPExcel->getActiveSheet()->SetCellValue('A1', "MARCA");
    $objPHPExcel->getActiveSheet()->SetCellValue('B1', "MODELO");
    $objPHPExcel->getActiveSheet()->SetCellValue('C1', "VERSION");
    $objPHPExcel->getActiveSheet()->SetCellValue('D1', "MATRICULA");
    $objPHPExcel->getActiveSheet()->SetCellValue('E1', "BASTIDOR");
    $objPHPExcel->getActiveSheet()->SetCellValue('F1', "NOTAS");
    $objPHPExcel->getActiveSheet()->SetCellValue('G1', "COMBUSTIBLE");
    $objPHPExcel->getActiveSheet()->SetCellValue('H1', "CARROCERIA");
    $objPHPExcel->getActiveSheet()->SetCellValue('I1', "KM");
    $objPHPExcel->getActiveSheet()->SetCellValue('J1', "CAJACAMBIOS");
    $objPHPExcel->getActiveSheet()->SetCellValue('K1', "MATRICULACION");
    $objPHPExcel->getActiveSheet()->SetCellValue('L1', "COLOR_PRINCIPAL");
    $objPHPExcel->getActiveSheet()->SetCellValue('M1', "VENTA_TOTALSINDTO");
    $objPHPExcel->getActiveSheet()->SetCellValue('N1', "COMPRA_TOTAL");


    $INT = "#*INT$";
    $GRUPO_CONDICION = "#$grupo$";
    $USUARIO_SESSION = "#$usuarioSession$";
    $conditions = '';
    $type = 'isis';
    $values = [1, 'VO', 0, 'STK'];
    $sql = "SELECT DISTINCT V.CODIGO AS CODIGO, V.ALTA, V.IDCAR, V.MOSTRAR, V.KM, M.DESCRIPCION AS MARCAS, MO.DESCRIPCION AS MODELOS, V.RESERVA, V.MOSTRAR, V.MATRICULA, V.ANO_FAB, V.VENTA_TOTAL, V.VENTA_TOTALSINDTO, V.COMPRA_TOTAL, V.VERSION, V.MATRICULACION, V.NOTAS, V.BASTIDOR, V.COLORPRI_LIT,
    L1.DESCRIPCION_$lg AS DESCRIPCION_LG1, 
    L2.DESCRIPCION_$lg AS DESCRIPCION_LG2,
    L3.DESCRIPCION_$lg AS DESCRIPCION_LG3,
    L3.DESCRIPCION_$lg AS DESCRIPCION_LG4,
    L3.EXTRA1 AS EXTRA_COLOR_PRINCIPAL
    FROM VEHICULOS V
    INNER JOIN VEHICULOS_STOCKS S ON V.STOCK = S.CODIGO
    INNER JOIN VEHICULOS_MARCAS M ON M.CODIGO = V.MARCA
    INNER JOIN VEHICULOS_MODELOS MO ON MO.CODIGO = V.MODELO
    LEFT JOIN VEHICULOS_LITERALES L1 ON L1.CODIGO = V.COMBUSTIBLE AND L1.TIPO = 'TIP_COM'
    LEFT JOIN VEHICULOS_LITERALES L2 ON L2.CODIGO = V.CAJACAMBIOS AND L2.TIPO = 'TIP_CAJ'
    LEFT JOIN VEHICULOS_LITERALES L3 ON L3.CODIGO = V.COLOR_PRINCIPAL AND L3.TIPO = 'COL_PRI'
    LEFT JOIN VEHICULOS_LITERALES L4 ON L4.CODIGO = V.CARROCERIA AND L4.TIPO = 'TIP_CAR'
    WHERE V.ACTIVO = ?
    AND V.TIPO = ?
    AND V.PAPELERA = ?
    AND V.ESTADO = ?
    AND S.ACCESO = '' OR S.ACCESO IS NULL OR S.ACCESO LIKE '%$INT%' OR S.ACCESO LIKE '%$GRUPO_CONDICION%' OR S.ACCESO LIKE '%$USUARIO_SESSION%' ORDER BY MARCAS ASC";
    $res = $Schema->executeQuery($sql, $type, $values);
    if($res->num_rows) {
        $i = 2;
        while($row = $res->fetch_array()) {
            $objPHPExcel->getActiveSheet()->SetCellValue('A' . $i, $row['MARCAS']);
            $objPHPExcel->getActiveSheet()->SetCellValue('B' . $i, $row['MODELOS']);
            $objPHPExcel->getActiveSheet()->SetCellValue('C' . $i, $row['VERSION']);
            $objPHPExcel->getActiveSheet()->SetCellValue('D' . $i, $row['MATRICULA']);
            $objPHPExcel->getActiveSheet()->SetCellValue('E' . $i, $row['BASTIDOR']);
            $objPHPExcel->getActiveSheet()->SetCellValue('F' . $i, $row['NOTAS']);
            $objPHPExcel->getActiveSheet()->SetCellValue('G' . $i, $row['DESCRIPCION_LG1']);
            $objPHPExcel->getActiveSheet()->SetCellValue('H' . $i, $row['DESCRIPCION_LG4']);
            $objPHPExcel->getActiveSheet()->SetCellValue('I' . $i, $row['KM']);
            $objPHPExcel->getActiveSheet()->SetCellValue('J' . $i, $row['DESCRIPCION_LG2']);
            $objPHPExcel->getActiveSheet()->SetCellValue('K' . $i, $row['MATRICULACION']);
            $objPHPExcel->getActiveSheet()->SetCellValue('L' . $i, $row['COLORPRI_LIT']);
            $objPHPExcel->getActiveSheet()->SetCellValue('M' . $i, $row['VENTA_TOTALSINDTO']);
            $objPHPExcel->getActiveSheet()->SetCellValue('N' . $i, $row['COMPRA_TOTAL']);
            $i++;
        }
    }
 
    $writer = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="stock' . date('d_m_Y') . '.xls"');
    header('Cache-Control: max-age=0');
    $writer->save('php://output');

?>