<?php
session_start();
header("Content-type: application/vnd.ms-excel");
header("Content-Disposition: attachment; filename=Informe_Comprobantes_Consolidado.xls");
require_once("../Conexion/conexion.php");
require_once '../Conexion/ConexionPDO.php';
$con = new ConexionPDO();
$conexion = $_SESSION['conexion'];

echo "<!DOCTYPE html PUBLIC \"-//W3C//DTD XHTML 1.0 Transitional//EN\" \"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd\">";
echo "<html xmlns=\"http://www.w3.org/1999/xhtml\">";
echo "<head>";
echo "<meta http-equiv=\"Content-Type\" content=\"text/html; charset=utf-8\" />";
echo "<title>Listado de comprobante por tipo de comprobante</title>";
echo "</head>";
echo "<body>";
echo "<table width=\"100%\" border=\"1\" cellspacing=\"0\" cellpadding=\"0\">";
echo "<tr>";
echo "<td colspan=\"6\" bgcolor=\"skyblue\"><CENTER><strong>LISTADO DE COMPROBANTES CONSOLIDADO</strong></CENTER></td>";
echo "</tr>";

if (!empty($_REQUEST['sltTipoComprobanteInicial']) && !empty($_REQUEST['sltTipoComprobanteFinal'])) {
    list($tipoI, $tipoF, $fechaI, $fechaF) = array($_REQUEST['sltTipoComprobanteInicial'], $_REQUEST['sltTipoComprobanteFinal'], $_REQUEST['txtFechaInicial'], $_REQUEST['txtFechaFinal']);

    $fechaI = explode("/", $fechaI);
    $fechaI = "$fechaI[2]-$fechaI[1]-$fechaI[0]";
    $fechaF = explode("/", $fechaF);
    $fechaF = "$fechaF[2]-$fechaF[1]-$fechaF[0]";

    switch ($conexion) {
        case 1:
            $res = $con->Listar("SELECT DISTINCT tpc.id_unico,tpc.nombre,tpc.sigla
        FROM            gf_tipo_comprobante tpc
        LEFT JOIN       gf_comprobante_cnt     cnt  ON cnt.tipocomprobante = tpc.id_unico
        LEFT JOIN       gf_detalle_comprobante dtc  ON dtc.comprobante = cnt.id_unico
        WHERE           tpc.id_unico BETWEEN $tipoI    AND $tipoF
        AND             cnt.fecha    BETWEEN '$fechaI' AND '$fechaF'
        ORDER BY        cnt.fecha   ASC");

            break;
        case 2:
            $res = $con->Listar("SELECT DISTINCT
        tpc.id_unico,
        tpc.nombre,
        tpc.sigla,
        cnt.fecha
        FROM gf_tipo_comprobante tpc
        LEFT JOIN gf_comprobante_cnt cnt ON cnt.tipocomprobante = tpc.id_unico
        LEFT JOIN gf_detalle_comprobante dtc ON dtc.comprobante = cnt.id_unico
        WHERE tpc.id_unico BETWEEN $tipoI AND $tipoF
        AND cnt.fecha BETWEEN TO_DATE('$fechaI', 'YYYY-MM-DD') AND TO_DATE('$fechaF', 'YYYY-MM-DD')
        ORDER BY cnt.fecha ASC");
            break;
        default:
            # code...
            break;
    }
    // $res = $mysqli->query($sql);

    // while ($row = $res->fetch_row()) {
    for ($row = 0; $row < count($res); $row++) {
        echo "<tr>";
        echo "<td colspan=\"6\" border=\"1\"><strong>" . strtoupper($res[$row][1]) . " " . $res[$row][2] . ": </strong></td>";
        echo "</tr>";
        echo "<tr>";
        echo "<td><strong>No</strong></td>";
        echo "<td><strong>FECHA</strong></td>";
        echo "<td><strong>TERCERO</strong></td>";
        echo "<td><strong>VALOR CONTABILIDAD</strong></td>";
        echo "<td><strong>VALOR PRESUPUESTO</strong></td>";
        echo "<td><strong>DIFERENCIA</strong></td>";
        echo "</tr>";
        switch ($conexion) {
            case 1:
                $res_t = $con->Listar("SELECT DISTINCT     cnt.id_unico,
                cnt.numero,
                date_format(cnt.fecha,'%d/%m/%Y'),
                IF( CONCAT( IF(ter.nombreuno='','',ter.nombreuno),' ',
                IF(ter.nombredos IS NULL,'',ter.nombredos),' ',
                IF(ter.apellidouno IS NULL,'',
                IF(ter.apellidouno IS NULL,'',ter.apellidouno)),' ',
                IF(ter.apellidodos IS NULL,'',ter.apellidodos))=''
                OR  CONCAT( IF(ter.nombreuno='','',ter.nombreuno),' ',
                IF(ter.nombredos IS NULL,'',ter.nombredos),' ',
                IF(ter.apellidouno IS NULL,'',
                IF(ter.apellidouno IS NULL,'',ter.apellidouno)),' ',
                IF(ter.apellidodos IS NULL,'',ter.apellidodos)) IS NULL ,
                (ter.razonsocial),
                CONCAT( IF(ter.nombreuno='','',ter.nombreuno),' ',
                IF(ter.nombredos IS NULL,'',ter.nombredos),' ',
                IF(ter.apellidouno IS NULL,'',
                IF(ter.apellidouno IS NULL,'',ter.apellidouno)),' ',
                IF(ter.apellidodos IS NULL,'',ter.apellidodos))) AS 'NOMBRE',
                cnt.descripcion,
                tpc.comprobante_pptal
                FROM                gf_comprobante_cnt          cnt
                LEFT JOIN           gf_tercero                  ter  ON cnt.tercero                 = ter.id_unico
                LEFT JOIN           gf_detalle_comprobante      dtc  ON dtc.comprobante             = cnt.id_unico
                LEFT JOIN           gf_tipo_comprobante         tpc  ON cnt.tipocomprobante         = tpc.id_unico
                WHERE               cnt.tipocomprobante = " . $res[$row][0] . "
                AND                 cnt.fecha >= '$fechaI' AND cnt.fecha <= '$fechaF'
                AND                 dtc.valor IS NOT NULL
                ORDER BY            cnt.numero ASC");
                break;
            case 2:
                $res_t = $con->Listar("SELECT DISTINCT
                cnt.id_unico,
                cnt.numero,
                TO_CHAR(cnt.fecha, 'DD/MM/YYYY'),
                CASE WHEN TRIM(TRAILING ' ' FROM NVL2(ter.nombreuno, ter.nombreuno || ' ', '') ||
                NVL2(ter.nombredos, ter.nombredos || ' ', '') ||
                NVL2(ter.apellidouno, ter.apellidouno || ' ', '') ||
                NVL2(ter.apellidodos, ter.apellidodos, '')) IS NULL
                THEN ter.razonsocial
                ELSE TRIM(TRAILING ' ' FROM NVL2(ter.nombreuno, ter.nombreuno || ' ', '') ||
                NVL2(ter.nombredos, ter.nombredos || ' ', '') ||
                NVL2(ter.apellidouno, ter.apellidouno || ' ', '') ||
                NVL2(ter.apellidodos, ter.apellidodos, ''))
                END AS NOMBRE,
                cnt.descripcion,
                tpc.comprobante_pptal
                FROM gf_comprobante_cnt cnt
                LEFT JOIN gf_tercero ter ON cnt.tercero = ter.id_unico
                LEFT JOIN gf_detalle_comprobante dtc ON dtc.comprobante = cnt.id_unico
                LEFT JOIN gf_tipo_comprobante tpc ON cnt.tipocomprobante = tpc.id_unico
                WHERE cnt.tipocomprobante = " . $res[$row][0] . "
                AND cnt.fecha >= TO_DATE('$fechaI', 'YYYY-MM-DD') 
                AND cnt.fecha <= TO_DATE('$fechaF', 'YYYY-MM-DD')
                AND dtc.valor IS NOT NULL
                ORDER BY cnt.numero ASC");
                break;
            default:
                # code...
                break;
        }


        // $res_t = $mysqli->query($sql_t);
        // while ($rowD = $res_t->fetch_row()) {
        for ($rowD = 0; $rowD < count($res_t); $rowD++) {
            list($vCnt, $vPtl, $vTot) = array(0, 0, 0);

            $sumar = 0;
            $sumaT = 0;
            $valorD = 0;
            $valorC = 0;
            $resR = $con->Listar("SELECT DISTINCT cnt.naturaleza, dtc.valor , dtc.id_unico
                        FROM            gf_detalle_comprobante dtc
                        LEFT JOIN       gf_cuenta cnt ON dtc.cuenta = cnt.id_unico
                        WHERE           dtc.comprobante = " . $res_t[$rowD][0]);
            // $resR = $mysqli->query($sqlR);
            // while ($row = mysqli_fetch_row($resR)) {
            for ($row = 0; $row < count($resR); $row++) {
                if ($resR[$row][0] == 1) {
                    if ($resR[$row][1] >= 0) {
                        $sumar += $resR[$row][1];
                    }
                } else if ($resR[$row][0] == 2) {
                    if ($resR[$row][1] <= 0) {
                        $x = (float) substr($resR[$row][1], '1');
                        $sumar += $x;
                    }
                }

                if ($resR[$row][0] == 2) {
                    if ($resR[$row][1] >= 0) {
                        $sumaT += $resR[$row][1];
                    }
                } else if ($resR[$row][0] == 1) {
                    if ($resR[$row][1] <= 0) {
                        $x = (float) substr($resR[$row][1], '1');
                        $sumaT += $x;
                    }
                }
            }
            $valorD = $sumar;
            $vCnt  = $valorD;

            $rowPt = $con->Listar("SELECT id_unico FROM gf_comprobante_pptal WHERE numero = " . $res_t[$rowD][1] . " AND tipocomprobante = " . $res_t[$rowD][5] . "");
            // $res_p = $mysqli->query($sql_p);
            // $rowPt = $res_p->fetch_row();

            $res_d = $con->Listar("SELECT valor FROM gf_detalle_comprobante_pptal WHERE comprobantepptal = " . $rowPt[0][0]);
            // $res_d = $mysqli->query($sql_d);
            // while ($row_d = mysqli_fetch_row($res_d)) {
            for ($row_d = 0; $row_d < count($res_d); $row_d++) {
                $vPtl += $res_d[$row_d][0];
            }

            list($numero, $fecha, $tercero) = array(
                $res_t[$rowD][1],
                $res_t[$rowD][2],
                ucwords(mb_strtoupper($res_t[$rowD][3]))
            );

            $vTot = $vCnt - $vPtl;

            echo "<tr>";
            echo "<td>" . $numero . "</td>";
            echo "<td>" . $fecha . "</td>";
            echo "<td>" . $tercero . "</td>";
            echo "<td>" . number_format($vCnt, 2, ',', '.') . "</td>";
            echo "<td>" . number_format($vPtl, 2, ',', '.') . "</td>";
            echo "<td>" . number_format($vTot, 2, ',', '.') . "</td>";
            echo "</tr>";
        }
    }
}
echo "</table>";
echo "</body>";
echo "<html>";
