<?php
    /************************************************************************************************* 
    GETDOCUMENTS 
    Servicio para recibir los documentos (Facturas) de una session valida y activa en el sistema 
    Parametro: {sessionid}
    Retorna: jSon con el formato establecido en el servicio.
                HTTP/1.1 200 con el formato y si data
                [
                {"fecha":"23 ene 2018","dsc":"Esta es una prueba"},
                {"fecha":"23 ene 2018","dsc":"Otra prueba"},
                {"fecha":"23 ene 2018","dsc":"Una mas}
                ]
             o en caso de errores 
                HTTP/1.1 400 o superior: Con su respectivo mensaje de error
    ************************************************************************************************/
     
    header("Content-Type:application/json");
    include("../connection.php");
    
    // validar que hay paginacion del easyui
    $page = 1;
    $rows = 99;
    if (isset($_REQUEST['page']))
        $page = intval($db->real_escape_string($_REQUEST['page']));
    if (isset($_REQUEST['rows']))
        $rows = intval($db->real_escape_string($_REQUEST['rows']));
    //Limite maximo para evitar explotación de muchos datos
    $rows = ($rows > 50) ? 50 : $rows;
        
    $salida = new stdClass();

    $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"]));

//    mysql_query("SET lc_time_names = 'es_ES'");

    /* Se valida el usuario y la session */
    $sql =  "SELECT id, name, rif " .
            "FROM   customers " .
            "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()){
            case 0:
                http_response_code(401);
                $salida->rsp = true ;
                $salida->msg = "Sesión inválida o caduca";
                $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;
        }
    }

    $cust_name  = $rs["name"];
    $cust_rif   = $rs["rif"];
    $cust_id    = $rs["id"]*1;

    $where = "customerid = " . $cust_id ." ";
    $order = "documents.docdate DESC " ; 
    
    // contar númer de documentos para la paginacion
    $sql = "SELECT * FROM documents WHERE " . $where;
    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);
        
        // realizar query limitando paguinación actual
        $sql   =  "SELECT A.* FROM (" .
                      "SELECT  documents.id, ". 
                              "documents.docdate as docdate, DATE_FORMAT(documents.docdate, '%d/%m/%Y') as fdocdate, " .
                              "documents.doctype, documents.docnumber, documents.doccontrol, ".
                              "documents.base as base, " .
                              "documents.total as total, " .
                              "documents.pagado as pagado, " .
                              "documents.errlvl, documents.errdsc, ".
                              "IF(isnull(retislr.id), false, true) as swichtislr, " .
                              "IF(isnull(retiva.id), false, true) as swichtiva, " .
                              "retislr.id as islr_id, ".
                              "DATE_FORMAT(retislr.islrdate, '%d/%m/%Y') as islr_date, ". 
                              "retislr.islrnumber as islr_number, " . 
                              "retislr.amount as islr_amount, " .
                              "retislr.repdate as islr_repdate, ".
                              "retislr.pdf as islr_pdf, ".
                              "IFNULL(retislr.status,0) as islr_status, ".
                              "retislr.obs as islr_obs, ".
                              "retiva.id as iva_id, ".
                              "DATE_FORMAT(retiva.ivadate, '%d/%m/%Y') as iva_date, ". 
                              "retiva.ivanumber as iva_number, " . 
                              "retiva.amount as iva_amount, " .
                              "retiva.repdate as iva_repdate, ".
                              "retiva.pdf as iva_pdf, ".
                              "IFNULL(retiva.status, 0) as iva_status, ".
                              "retiva.obs as iva_obs ".
                      "FROM   documents " .
                      "  LEFT JOIN retislr ON retislr.documentId = documents.id " .
                      "  LEFT JOIN retiva  ON  retiva.documentId = documents.id " .
                      "WHERE ". $where . " " .
                  ") A " .
                  "ORDER BY A.docdate DESC, A.id DESC " .
                  "LIMIT  " . (($page-1)*$rows) . "," . $rows;
            
        //"ORDER BY " . $order;
        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)){
                // calcular estatus de editabilidad
                // 0 = completamente editable y eliminable
                // 1 = islr validado por ML no se pueden cambiar ni datos de la factura ni de islr
                // 2 = iva validado por ML no se pueden cambiar ni datos de la factura ni de iva
                // 3 = completamente validado ni edici��n ni eliminaci��n posibles
                $status = 0;
                if (($row["islr_status"]==1 && $row["iva_status"]==0) || ($row["iva_repdate"]!==NULL && $row["iva_repdate"]!=='0000-00-00 00:00:00')){
                    $status = 1;
                }
                if ($row["islr_status"]==0 && $row["iva_status"]==1 || ($row["islr_repdate"]!==NULL && $row["islr_repdate"]!=='0000-00-00 00:00:00')){
                    $status = 2;
                }
                if ($row["islr_status"]==1 && $row["iva_status"]==1 || (($row["islr_repdate"]!==NULL && $row["islr_repdate"]!=='0000-00-00 00:00:00') && ($row["iva_repdate"]!==NULL && $row["iva_repdate"]!=='0000-00-00 00:00:00'))){
                    $status = 3;
                }
                if ($row["islr_status"]==3 && $row["iva_status"]==3){
                    $status = 0;
                }
                array_push($records, 
                                array( 
                                "status"       => $status,     
                                    
                                "documentid"   => $row["id"]*1, 
                                "docdate"      => $row["docdate"],
                                "fdocdate"     => $row["fdocdate"],
                                "docnumber"    => $row["docnumber"],
                                "doccontrol"   => $row["doccontrol"],
                                "base"         => number_format($row["base"], 2, ",", "."),
                                "total"        => number_format($row["total"], 2, ",", "."),
                                "pagado"       => number_format($row["pagado"], 2, ",", "."),
                                "errlvl"       => $row["errlvl"]*1,
                                "errdsc"       => $row["errdsc"],

                                "swichtislr"    => $row["swichtislr"],
                                "islr_id"       => $row["islr_id"]*1,
                                "islr_status"   => $row["islr_status"],
                                "islr_date"     => $row["islr_date"],
                                "islr_number"   => $row["islr_number"],
                                "islr_amount"   => number_format(($row["islr_amount"] != null ? $row["islr_amount"] : 0), 2, ",", "."),
                                "islr_repdate"  => $row["islr_repdate"],
                                "islr_pdf"      => $row["islr_pdf"],
                                
                                "swichtiva"    => $row["swichtiva"],
                                "iva_id"        => $row["iva_id"]*1,
                                "iva_status"    => $row["iva_status"],
                                "iva_date"      => $row["iva_date"],
                                "iva_number"    => $row["iva_number"],
                                "iva_amount"    => number_format(($row["islr_amount"] != null ? $row["islr_amount"] : 0), 2, ",", "."),
                                "iva_repdate"   => $row["iva_repdate"],
                                "iva_pdf"   => $row["iva_pdf"]
                                )
                );
            }
            mysqli_free_result($qry);
            http_response_code(200);
            $salida->total        = $numofrecords;
            $salida->rows         = $records;
        }
    }
    
    echo (json_encode($salida));
?>