<?php 
//=====================================================================================
//INICIO LA SESION---------------------------------------------------------------------
//=====================================================================================
session_start();
//=====================================================================================
//CONEXION-----------------------------------------------------------------------------
//=====================================================================================
require_once '../conn/conn.php';
$bd_conn = new connexion();
$bd_conn->query("SET NAMES 'utf8'");
setlocale(LC_TIME,"es_ES");
//=====================================================================================
//VARIABLES----------------------------------------------------------------------------
//=====================================================================================
require_once '../core/config.php';
$clave_exp = $_POST["clave_exp"];
//=====================================================================================
//CODE---------------------------------------------------------------------------------
//=====================================================================================
//APORTADOS
$base = consultarDocumentosExpediente($bd_conn, $clave_exp, DOCS_APORTADOS);
$docs_aportados = array();
$i = 0;
while ($row = $base->fetch_object()){
    $id_doc = substr($row->CLAVE, -16);
    $docs_aportados[$i] = array ( 
        "clave_doc" => $id_doc,
        "nombre_doc" => mb_strtoupper($row->NOMBRE, 'UTF-8'),
        "estado_doc" => $row->ESTADO_PC,
        "accion_doc" => $row->ACCION,
        "fecha_doc" => $row->FECHA_DOC
    );
    $i++;
}
//PENDIENTES
$base = consultarDocumentosExpediente($bd_conn, $clave_exp, DOCS_PENDIENTES);
$docs_pendientes = array();
$i = 0;
while ($row = $base->fetch_object()){
    $id_doc = substr($row->CLAVE, -16);
    $docs_pendientes[$i] = array ( 
        "clave_doc" => $id_doc,
        "nombre_doc" => mb_strtoupper($row->NOMBRE, 'UTF-8'),
        "estado_doc" => $row->ESTADO_PC,
        "accion_doc" => $row->ACCION,
        "fecha_doc" => $row->FECHA_DOC
    );
    $i++;
}
//POR_FIRMAR
$base = consultarDocumentosExpediente($bd_conn, $clave_exp, DOCS_PORFIRMAR);
$docs_firmar = array();
$i = 0;
while ($row = $base->fetch_object()){
    $id_doc = substr($row->CLAVE, -16);
    $docs_firmar[$i] = array ( 
        "clave_doc" => $id_doc,
        "nombre_doc" => mb_strtoupper($row->NOMBRE, 'UTF-8'),
        "estado_doc" => $row->ESTADO_PC,
        "accion_doc" => $row->ACCION,
        "fecha_doc" => $row->FECHA_DOC
    );
    $i++;
}
//Datos del Expediente
$base = consultarDatosExpediente($bd_conn, $clave_exp);
while ($row = $base->fetch_object()){
    $exp_codigo = $row->CODIGO;
    $exp_tipo = $row->TIPO;
}
//DATA
$data = array();
$data["data"] = array ( 
    "docs_aportados" => $docs_aportados,
    "docs_pendientes" => $docs_pendientes,
    "docs_firmar" => $docs_firmar,
    "exp_codigo" => $exp_codigo,
    "exp_tipo" => $exp_tipo
);
header("Content-type: application/json; charset=utf-8");
mysqli_close($bd_conn);
echo json_encode($data);
//=====================================================================================
//FUNCIONES----------------------------------------------------------------------------
//=====================================================================================
function consultarDocumentosExpediente($bd, $clave_exp, $doc_estado)
{
    $sql =  "   SELECT E.CLAVE, E.NOMBRE, E.ESTADO_PC, E.ACCION, date_format(E.FECHA, '%d/%m/%Y') AS FECHA_DOC
                FROM BIBLIOTECA_ELEMENTOS E
                WHERE E.PAPELERA = 0 AND E.TIPO = 'D' AND E.UBICACION LIKE CONCAT('%',?,'%') AND E.ESTADO_PC = ?";
    $base = $bd->prepare($sql);
    $clave_exp = mysqli_real_escape_string($bd, $clave_exp);
    $doc_estado = mysqli_real_escape_string($bd, $doc_estado);
    $base->bind_param('si', $clave_exp, $doc_estado);
    $base->execute();
    $result = $base->get_result();
    $base->close();
    return $result;
}
function consultarDatosExpediente($bd, $clave_exp)
{
    $sql =  "   SELECT DISTINCT E.NOMBRE AS CODIGO, ET.DESCRIPCION AS TIPO
                FROM BIBLIOTECA_ELEMENTOS E
                LEFT JOIN BIBLIOTECA_EXPEDIENTES EP ON EP.CLAVE = E.EXPTE_CODIGO
                LEFT JOIN EXPEDIENTES_TIPOS ET ON ET.CODIGO = EP.TIPOEXP
                WHERE E.CLAVE = ? LIMIT 1";
    $base = $bd->prepare($sql);
    $clave_exp = mysqli_real_escape_string($bd, $clave_exp);
    $base->bind_param('s', $clave_exp);
    $base->execute();
    $result = $base->get_result();
    $base->close();
    return $result;
}
?>