<?php
    header("Content-Type:application/json");
    include("../connection.php");

    // validar que hay paginacion del easyui
    $page = 1;
    $rows = 99;
    $order = "";
    $value = "";
    $name = "";
    $filtro = "";
    $st = " IF(((IFNULL(R.repdate, '00/00/0000')='00/00/0000' AND IFNULL(I.repdate, '00/00/0000')='00/00/0000') " .
          "    OR (CAST(R.repdate AS CHAR) = '0000-00-00 00:00:00' AND CAST(I.repdate AS CHAR) = '0000-00-00 00:00:00')), ".
          "    IF(R.status='1' OR I.status='1', 'APR', ".
          "       IF(D.errlvl=0, 'OK', ".
          "          IF(D.errlvl=1, 'WAR', 'ERR'))), ".
          "    'REP')";
    if (isset($_REQUEST['page']))   $page = intval($db->real_escape_string($_REQUEST['page']));
    if (isset($_REQUEST['rows']))   $rows = intval($db->real_escape_string($_REQUEST['rows']));
    if (isset($_REQUEST['sort']))   $sort = strval($db->real_escape_string($_REQUEST['sort']));
    if (isset($_REQUEST['order']))  $order = strval($db->real_escape_string($_REQUEST['order']));
    if (isset($_REQUEST['value']))  $value = strval($db->real_escape_string($_REQUEST['value']));
    if (isset($_REQUEST['name']))   $name = strval($db->real_escape_string($_REQUEST['name']));
    //Limite maximo para evitar explotaci��n de muchos datos
    $rows = ($rows > 50) ? 50 : $rows;
    if ($name == "all")             $name = "";
    
    // se filtra siempre por documentos sin errores críticos
    $filtro = " (D.errlvl<2) ";
    
    // filtro de las fechas de las facturas
    if (isset($_REQUEST['fDesde'])){
        $filtro .= " AND (D.docdate >= STR_TO_DATE('" . $db->real_escape_string($_REQUEST['fDesde']) . "', '%d/%m/%Y') ) ";
    }
    if (isset($_REQUEST['fHasta'])){
        $filtro .= " AND (D.docdate <= STR_TO_DATE('" . $db->real_escape_string($_REQUEST['fHasta']) . "', '%d/%m/%Y') )";
    }

    // filtro de las fechas de reporte
    if (isset($_REQUEST['rDesde'])){
        $filtro .= " AND (DATE(R.repdate) >= STR_TO_DATE('" . $db->real_escape_string($_REQUEST['rDesde']) . "', '%d/%m/%Y') )";
    }
    if (isset($_REQUEST['rHasta'])){
        $filtro .= " AND (DATE(R.repdate) <= STR_TO_DATE('" . $db->real_escape_string($_REQUEST['rHasta']) . "', '%d/%m/%Y') ) ";
    }

    // filtro de los estatus, pending, aproved, warning y ready solo pasan cuando vienen en false
    // QUITAR PENDIENTES
    if (isset($_REQUEST['pending']) && $_REQUEST['pending'] === 'false' ){
        $filtro .= " AND (".$st." <> 'OK') " ;
    }
    // QUITAR APROBADOS
    if (isset($_REQUEST['aproved']) && $_REQUEST['aproved'] === 'false' ){
        $filtro .= " AND (".$st." <> 'APR') " ;
    }
    // QUITAR WARNINGS
    if (isset($_REQUEST['warning']) && $_REQUEST['warning'] === 'false' ){
        $filtro .= " AND (".$st." <> 'WAR') " ;
    }
    // QUITAR REPORTED
    if (isset($_REQUEST['ready']) && $_REQUEST['ready'] === 'false' ){
        $filtro .= " AND (".$st." <> 'REP') " ;
    }

    $errMsg = "";
    if (!isset($_REQUEST["sessid"]) || empty($_REQUEST["sessid"]) ) { $errMsg .= "No se recibio el Parametro sessid. "; }

    
    if (!empty($errMsg) ){
        http_response_code(400);
        $salida->msg = $errMsg;
        echo (json_encode($salida));
        return;
    }
    $sessid = preg_replace("/[^a-zA-Z0-9]/", "", $db->real_escape_string($_REQUEST["sessid"]));
    $salida = new stdClass();


    /* Se valida el usuario y la session y acceso */
    $sql =  "SELECT id, name, rif, accs_invoices " .
            "FROM   users " .
            "WHERE  sessionid = ? " .
            "  AND  IF(CURDATE()<sessionvalidthru, 1, 0)" ;

            
    //Preparamos el statement
    $stmt =  $db->prepare($sql);
    //Remplazamos los parametros en la consulta almacenada
    $stmt->bind_param("s", $sessid);

    //Validamos la ejecución
    if (!($stmt->execute())){
        http_response_code(500);
        $salida->rsp = false ;
        $salida->msg = mysqli_error($db);
        echo (json_encode($salida));
        return;
    }

    //Obtenemos los resultados y recorremos
    $result = $stmt->get_result();
    if (!($rs = mysqli_fetch_array($result))){
        switch (mysqli_errno($db)){
            case 0:
                http_response_code(401);
                $salida->rsp = true ;
                $salida->msg = "Sesión invalida o terminada";
                //$salida->sql = $sql;
                $salida->errno = 0;
                echo (json_encode($salida));
                return;
                break;
            default:
                http_response_code(500);
                $salida->rsp = false ;
                $salida->msg = mysqli_error($db);
                echo (json_encode($salida));
                return;
                break;
        }
    }


   // Se verifica si tiene permiso
   if ($rs["accs_invoices"]*1 < 1){
       http_response_code(401);
       $salida->rsp = false ;
       $salida->msg = "No tiene los privilegios para visualizar las facturas";
       $salida->name = $rs["name"];
       $salida->accs = $rs["accs_invoices"]*1;
       $salida->sessid = $sessid;
       echo (json_encode($salida));
       return;
   }
    
    //$filtro = " (R.status = 0 AND I.status = 0)  "; // || 1
    
    if ($filtro == "") 
        $filtro = " (true) ";
    
    $where = "";
    if ($name != ""){
        if ($value != ""){
            $where = " AND " . $name . " LIKE '%" . $value . "%' ";
        }
    }else{
        if ($value != ""){
            $where = " AND  (D.customerid   LIKE '%" . $value . "%' OR " .
                     "       DATE_FORMAT(D.docdate, '%d/%m/%Y')     LIKE '%" . $value . "%' OR " .
                     "       D.docnumber    LIKE '%" . $value . "%' OR " .
                     "       D.total        LIKE '%" . $value . "%' OR " .
                     "       C.nickname     LIKE '%" . $value . "%' OR " .
                     "       C.name         LIKE '%" . $value . "%' OR " .
                     "       C.rif          LIKE '%" . $value . "%' OR " .
                     "       R.islrnumber     LIKE '%" . $value . "%' OR " .
                     "       I.ivanumber       LIKE '%" . $value . "%' )" ;
        }
    }
    
    // contar numero de documentos para la paginacion
    $sql =  "SELECT * ";
    $sql .= "FROM   ((customers C ";
    $sql .= "         INNER JOIN documents D  ON C.id = D.customerid) ";
    $sql .= "           LEFT JOIN retislr R   ON R.documentid = D.id) ";
    $sql .= "             LEFT JOIN retiva  I   ON I.documentid = D.id ";
    $sql .= "WHERE " . $filtro . $where;
    
    $sqlorder = "";
    if (!($qry = mysqli_query($db, $sql))){
        http_response_code(500);
        $salida->msg = mysqli_error($db);
        //$salida->sql = $sql;
    }else{
        $numofrecords = mysqli_num_rows($qry);
        mysqli_free_result($qry);
        
        if ($sort != "" && !empty($sort)){
            switch ($sort){
                case "docdate": 
                    $sortfld = "D.docdate";
                    break;
                case "custid": 
                    $sortfld = "C.custid";
                    break;
                case "docnumber": 
                    $sortfld = "D.docnumber";
                    break;
                case "total": 
                    $sortfld = "D.total";
                    break;
            }
        
            $sqlorder = "ORDER BY " . $sortfld ." " . $order . " ";
        }
        // realizar query limitando paginacion actual
        $sql =  "SELECT   D.id         as documentid, ". 
                        " D.errlvl     as errlvl, ".
                        " D.errdsc     as errdsc, ".
                        " D.customerid as customerid,  ".
                        " DATE_FORMAT(D.docdate, '%d/%m/%Y')   as docdate,  ".
                        " D.docnumber  as docnumber, " .
                        " D.doccontrol as doccontrol, " .
                        " D.base       as base, " .
                        " D.total      as total, " .
                        " D.pagado     as pagado, " .
                        " IF(isnull(I.amount), 0,I.amount) + ".
                        " IF(isnull(R.amount),0,R.amount) as retenido, " .
                        " C.custid     as custid, ".
                        " C.name       as name, ".
                        " C.nickname   as nickname, ".
                        " C.rif        as rif, ".
                        " C.status     as custstatus, ".
                        " R.id           as islrid, ".
                        " DATE_FORMAT(R.islrdate, '%d/%m/%Y')    as islrdate, ".
                        " R.islrnumber   as islrnumber, ".
                        " DATE_FORMAT(R.islrdate, '%d/%m/%Y')    as islrdate, ".
                        " R.amount       as islramount, ".
                        " R.pdf          as islrpdf, ".
                        " DATE_FORMAT(R.repdate, '%d/%m/%Y')     as islrrepdate, ".
                        " IF(isnull(R.islrnumber),0,R.status)  as islrstatus, ".
                        " I.id            as ivaid, ".
                        " DATE_FORMAT(I.ivadate, '%d/%m/%Y')      as ivadate, ".
                        " I.ivanumber     as ivanumber, ".
                        " DATE_FORMAT(I.ivadate, '%d/%m/%Y')      as ivadate, ".
                        " I.amount        as ivaamount, ".
                        " I.pdf           as ivapdf, ".
                        " IF(isnull(I.status),0,I.status)    as ivastatus, ".
                        " DATE_FORMAT(I.repdate, '%d/%m/%Y')      as ivarepdate, ".
                        " IF( ".
                        "    ((IFNULL(R.repdate, '00/00/0000')='00/00/0000' AND IFNULL(I.repdate, '00/00/0000')='00/00/0000') " .
                        "    OR (CAST(R.repdate AS CHAR) = '0000-00-00 00:00:00' AND CAST(I.repdate AS CHAR) = '0000-00-00 00:00:00')), ".
                        "    IF(R.status='1' OR I.status='1', 'APR', ".
                        "       IF(D.errlvl=0, 'OK', ".
                        "          IF(D.errlvl=1, 'WAR', 'ERR'))), ".
                        "    'REP') ST " .
                "FROM   ((customers C " .
                "         INNER JOIN documents D  ON C.id = D.customerid) " .
                "           LEFT JOIN retislr R   ON R.documentid = D.id) " .
                "             LEFT JOIN retiva  I   ON I.documentid = D.id " .
                "WHERE " . $filtro . $where .
                $sqlorder . " " .
                " LIMIT  " . (($page-1)*$rows) . "," . $rows;
                //die($sql);
        if (!($qry = mysqli_query($db, $sql))){
            http_response_code(500);
            $salida->msg = mysqli_error($db);
            //$salida->sql = $sql;
        }else{
            $records = array();
            while ($row = mysqli_fetch_array($qry)){
                $ivarepnull = ($row["ivarepdate"]==null || $row["ivarepdate"]=="00/00/0000");
                $islrrepnull = ($row["islrrepdate"]==null || $row["islrrepdate"]=="00/00/0000");
                $reportado =  !($ivarepnull && $islrrepnull); 
                
                //var_dump($reportado);
                $reportadoel = '';
                if (!$ivarepnull)
                    $reportadoel = $row["ivarepdate"];
                if (!$islrrepnull)
                    $reportadoel = $row["islrrepdate"];
                $aprobado = ($row["islrstatus"]=="1" || $row["ivastatus"]=="1");

                array_push($records, 
                                array( 
                                "documentid"    => $row["documentid"]*1,
                                "status"        => $row["errlvl"]*1,
                                "st"            => $row["ST"],
                                "customerid"    => $row["customerid"]*1, 
                                "docdate"       => $row["docdate"], 
                                "docnumber"     => $row["docnumber"], 
                                "doccontrol"    => $row["doccontrol"], 
                                "base"          => number_format($row["base"], 2, ",", "."),
                                "total"         => number_format($row["total"], 2, ",", "."),
                                "pagado"        => number_format($row["pagado"], 2, ",", "."),
                                "retenido"      => number_format($row["retenido"], 2, ",", "."), 
                                "custid"        => $row["custid"]*1, 
                                "name"          => $row["name"],
                                "nickname"      => $row["nickname"], 
                                "rif"           => $row["rif"], 
                                "custstatus"    => $row["custstatus"]*1, 
                                
                                "islrnumber"    => $row["islrnumber"],
                                "islrdate"      => $row["islrdate"], 
                                "islramount"    => number_format(($row["islramount"] != null ? $row["islramount"] : 0), 2, ",", "."), 
                                "islrpdf"       => $row["islrpdf"],
                                "islrrepdate"   => $row["islrrepdate"],
                                "islrstatus"    => $row["islrstatus"]*1,
                                
                                "ivanumber"     => $row["ivanumber"],
                                "ivadate"       => $row["ivadate"], 
                                "ivaamount"     => number_format(($row["ivaamount"] != null ? $row["ivaamount"] : 0), 2, ",", "."), 
                                "ivapdf"        => $row["ivapdf"],
                                "ivarepdate"    => $row["ivarepdate"],
                                "ivastatus"     => $row["ivastatus"]*1,
                                
                                "errdsc"        => $row["errdsc"],
                                
                                "reported"      => $reportado,
                                "reportedat"    => $reportadoel,
                                "aproved"       => $aprobado,
                                
                                "mark"          => false
                                )
                );
            }
            mysqli_free_result($qry);
            http_response_code(200);
            $salida->total  = $numofrecords;
            $salida->rows   = $records;
            //$salida->sql    = $sql;
            $salida->name   = $name;
            $salida->order  = $order;
            $salida->sort   = $sort;
            $salida->filtro   = $filtro;
            $salida->var_pending  = (isset($_REQUEST['pending']) ? $_REQUEST['pending'] : "");
            $salida->var_aproved  = (isset($_REQUEST['aproved']) ? $_REQUEST['aproved'] : "");
            $salida->var_warning  = (isset($_REQUEST['warning']) ? $_REQUEST['warning'] : "");
            $salida->var_ready    = (isset($_REQUEST['ready']) ? $_REQUEST['ready'] : "");
            $salida->var_fDesde = (isset($_REQUEST['fDesde']) ? $_REQUEST['fDesde'] : "");
            $salida->var_fHasta = (isset($_REQUEST['fHasta']) ? $_REQUEST['fHasta'] : "");
            $salida->var_rDesde = (isset($_REQUEST['rDesde']) ? $_REQUEST['rDesde'] : "");
            $salida->var_rHasta = (isset($_REQUEST['rHasta']) ? $_REQUEST['rHasta'] : "");
            $salida->var_REQUEST  = $_REQUEST;
        }
    }
    
    echo (json_encode($salida));
?>