<?php
//  app/api/payments/pagomovil/list.php
    header("Content-Type:application/json");
    include("../../../../settings/dbconn.php");
    include("../../../../settings/utils.php");
    
    // parametros obligatorios
    $parmsob = array("sessionid", "order", "numofrec", "offset",);
    if (!parametrosValidos($_REQUEST, $parmsob)){
        badEnd("400", array("msg"=>"Parametros obligatorios " . implode(",", $parmsob)));
    }
    
    // forzar a que se haga un POST y no un GET
    if ($_SERVER['REQUEST_METHOD']!= "GET")
        badEnd("400", array("msg"=>"Debe llamarse al servicio usando un GET"));
    
    /*
    $sessionid = preg_replace("/[^a-zA-Z0-9]+/", "", $_REQUEST["sessionid"]);
    $filpar = preg_replace("/[^a-zA-Z0-9 -_áéíóúÁÉÍÓÚÑÑ]+/", "", $_REQUEST["search"]);
    $offset = intval($_REQUEST["offset"]);
    $numofrec = intval($_REQUEST["numofrec"]);
    $order = intval($_REQUEST["order"]);
    $fecha = preg_replace("/[^0-9/]+/", "", $_REQUEST["dateto"])
    */
    
    //Definicion de variables y almacenamiento de valores enviados por GET
    $out = new stdClass();
    $sessionid = preg_replace("/[^a-zA-Z0-9]+/", "", $_REQUEST["sessionid"]);
    $order = determinateOrder(intval($_REQUEST["order"]));
    $numofrec = intval($_REQUEST["numofrec"]);  
    $offset = intval($_REQUEST["offset"]);
    $dateto = $_REQUEST["dateto"] ? $_REQUEST["dateto"] : '';
    $datefrom = $_REQUEST["datefrom"] ? $_REQUEST["datefrom"] : '';
    $statusid = $_REQUEST["statusid"] ? $_REQUEST["statusid"] : '';
    $search = $_REQUEST["search"] ? $_REQUEST["search"] : '';
    
    //Validamos la session
    $userid = isSessionValid($db, $sessionid);
    
    // Obtenemos el lenguaje del usuario
    $lang = updateLang($db,$sessionid);
    
    //En caso de que el search sea enviado, se construye el query
    if(isset($search) && $search != ""){
        // Iniciamos la cadena del query
        $search = " AND ( ";
        
        //Separamos el filtro por el simbolo +
        $filterEl = separateFilter(mysqli_real_escape_string($db, $search));
        
        //Recorremos cada elemento
        foreach($filterEl as $index => $el){
            //Si la fecha tiene / lo partimos en mil
            if(strpos($el, '/') !== false){
                $dateelements = explode("/",$el);
                
                foreach($dateelements as $i => $element){
                    if($i == 0){
                        $dateConverted .= $element;
                    }else{
                        $dateConverted .= "-".$element;
                    }
                }
                
                $dateel = strftime("%Y-%m-%d", strtotime($dateConverted));
            }else{
                $dateel = strftime("%Y-%m-%d", strtotime($el));
            }
            
            if($index == 0){
                $search .= " DATE(PM.sendat) = DATE('%".$dateel."') ".
                "           OR LOWER(PM.recipient) LIKE LOWER('%".$el."%')".
                "           OR LOWER(IF(PM.txid IS NULL, PM.note, CONCAT(PM.note, ' - ', PM.txid))) LIKE LOWER('%".$el."%')".
                "           OR LOWER(PM.bankname) LIKE LOWER('%".$el."%')".
                "           ".(validNumber($el) ?  'OR PM.amount = '.floatval($el) : (validNumberFormatted($el) ? 'OR PM.amount = '.parseFormattedNumber($el) : ''));
            }else{
                $search .= "           OR DATE(PM.sendat) = DATE('%".$dateel."') ".
                "           OR LOWER(PM.recipient) LIKE LOWER('%".$el."%')".
                "           OR LOWER(IF(PM.txid IS NULL, PM.note, CONCAT(PM.note, ' - ', PM.txid))) LIKE LOWER('%".$el."%')".
                "           OR LOWER(PM.bankname) LIKE LOWER('%".$el."%')".
                "           ".(validNumber($el) ?  'OR PM.amount = '.floatval($el) : (validNumberFormatted($el) ? 'OR PM.amount = '.parseFormattedNumber($el) : ''));
            }
        }
        
        $search .= " ) ";
    }
    
    //En caso de que el statusid sea enviado, se construye el query
    if(isset($statusid) && $statusid != "" && $statusid != 0){
        $statusid = " AND PM.status=".$statusid;
    }
    
     //En caso de que el dateto sea enviado, se construye el query
    if(isset($dateto) && $dateto != ""){
        $dateto = " AND DATE(PM.sendat) <= DATE('".$dateto."')";
    }
    
    //En caso de que el datefrom sea enviado, se construye el query
    if(isset($datefrom) && $datefrom != ""){
        $datefrom = " AND DATE(PM.sendat) >= DATE('".$datefrom."')";
    }
    
    // Buscamos el registro de VES
    $sql = "SELECT * ".
    "       FROM currencies ".
    "       WHERE id = 1";
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db).$sql));
        
    $currencydata = $rs->fetch_assoc();
    
    //Consultamos los datos del usuario para encontrar su fee
    $sql = "SELECT id, usr, type, level ".
    "       FROM users WHERE id = ".$userid;
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db).$sql));
            
    $userdata = $rs->fetch_assoc();
    
    $level = 1;
    
    if($userdata["level"] == 0){
        $level = 1;
    }else{
        $level = $userdata["level"];
    }
    
    //Consultamos las comisiones con los datos del usuario
    $sql = "SELECT * FROM cmspreffees ".
    "       WHERE levelid = ".$level.
    "       AND currencyid = ".$currencydata['id'].
    "       AND usertype = ".$userdata["type"];
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db).$sql));
            
    $feedata = $rs->fetch_assoc();
    
    // Armamos el item del fee
    $fees = new stdClass();
    
    //Comisiones para retiros
    $fees->withdrawals = new stdClass();
    
    if($feedata["pctfeewithdrawals"] == 0){
        $sql = "SELECT IF(exonerated IS NULL, 0, exonerated) AS exonerated FROM userportfolioprefs ".
        "       WHERE currencyid = ".$currencydata['id'].
        "       AND userid = ".$userid.
        "       AND preffeesfield = 'nmlfeewithdrawals'";
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db).$sql));
        
        $userExg = $res->fetch_assoc();
        
        $fees->withdrawals->nominal = (float)number_format($feedata["nmlfeewithdrawals"] - ($feedata["nmlfeewithdrawals"]/100 * $userExg['exonerated']), $currencydata['decimals'],'.','');
        $fees->withdrawals->percent = 0;
    }else{
        
        $sql = "SELECT IF(exonerated IS NULL, 0, exonerated) AS exonerated FROM userportfolioprefs ".
        "       WHERE currencyid = ".$currencydata['id'].
        "       AND userid = ".$userid.
        "       AND preffeesfield = 'pctfeewithdrawals'";
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db).$sql));
        
        $userExg = $res->fetch_assoc();
        
        $fees->withdrawals->percent = (float)number_format($feedata["pctfeewithdrawals"] - ($feedata["pctfeewithdrawals"]/100 * $userExg['exonerated']), 3,'.','');
        $fees->withdrawals->nominal = 0;
    }
    
    $sql = "SELECT IF(exonerated IS NULL, 0, exonerated) AS exonerated FROM userportfolioprefs ".
    "       WHERE currencyid = ".$currencydata['id'].
    "       AND userid = ".$userid.
    "       AND preffeesfield = 'pagomovilpct'";
    if (!$res=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db).$sql));
    
    $userExg = $res->fetch_assoc();
    
    $fees->pagomovil = new stdClass();
    $fees->pagomovil->percent = (float)number_format($feedata["pagomovilpct"] - ($feedata["pagomovilpct"]/100 * $userExg['exonerated']), 3,'.','');
    
    //Consultamos la cantidad de registros que existen
    $sql = "SELECT COUNT(PM.id) AS qty ".
    "       FROM payments_pagomovil PM ".
    "       JOIN accounts ON accounts.userid = ".$userid.
    "           AND accounts.id = PM.accountid".
    "       WHERE PM.id IS NOT NULL".
    "       AND PM.status != 1 ".
    "       AND PM.status != 5 ".
    "       ".$dateto.
    "       ".$datefrom.
    "       ".$search.
    "       ".$statusid;
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db).$sql));
        
    //Retornamos el total de registros
    $row = $rs->fetch_assoc();
    $out->numofrecords = (int)$row["qty"];
    
    //Consultamos los datos requeridos
    $sql = "SELECT PM.* ".
    "       FROM payments_pagomovil PM".
    "       JOIN accounts ON accounts.userid = ".$userid.
    "           AND accounts.id = PM.accountid".
    "       WHERE PM.id IS NOT NULL".
    "       AND PM.status != 1 ".
    "       AND PM.status != 5 ".
    "       ".$dateto.
    "       ".$datefrom.
    "       ".$statusid.
    "       ".$search.
    "       ORDER BY ".$order.
    "       LIMIT ".$offset.", ".$numofrec;
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db).$sql));
        
    $records = [];
    while($row = $rs->fetch_assoc()){
        
        $record = new stdClass();
        //Guardamos el id de la cuenta
        $record->id = (int)$row["id"];
        
        $record->status = new stdClass();
        $record->status->id = (int)$row['status'];
        $record->status->name = determinateStatusName($row['status']);
        $record->status->ref = $row['ref'] ? $row['ref'] : '';
        
        $record->sendat = new stdClass();
        $record->sendat->date = $row['sendat'];
        $record->sendat->formatted = date("d/m/Y - h:i a", strtotime($row["sendat"]));
        
        $record->sentat = new stdClass();
        $record->sentat->date = $row['sentat'] ? $row['sentat'] : "";
        $record->sentat->formatted = $row['sentat'] ? date("d/m/Y - h:i a", strtotime($row["sentat"])) : "";
        
        $record->destination = new stdClass();
        $record->destination->recipient = new stdClass();
        $record->destination->recipient->name = $row['recipient'];
        $record->destination->recipient->rif = $row['recipientrif'];
        $record->destination->recipient->phone = $row['recipientphone'];
        
        $record->destination->bank = new stdClass();
        $record->destination->bank->code = $row['bankcode'];
        $record->destination->bank->name = $row['bankname'];
        
        
        // Si tiene txid vemos el banco de origen
        if($row['txid'] != null){
            $record->origin = new stdClass();
            
            // Consultamos los datos
            $sql = "SELECT * FROM pagomovildetails ".
            "       WHERE transactionid = ".$row['txid'];
            if (!$result=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db).$sql));
                
            $pagomovildetails = $result->fetch_assoc();
            
            // Buscamos la config de los bancos
            $sql = "SELECT pagomovilbanks ".
            "       FROM cmspreferences";
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db).$sql));
                
            $rowConfig = $res->fetch_assoc();
            
            // Nos traemos los objetos
            $banks = json_decode($rowConfig['pagomovilbanks'], true);
            $banks = $banks['banks'];
            
            // Validamos que las interfaz fue la usada
            $bankInterface = null;
            foreach($banks as $bank){
                if($bank['id'] == $pagomovildetails['pagomovilbankid']){
                    $bankInterface = $bank;
                }
            }
            $record->origin->bank = new stdClass();
            $record->origin->bank->name = $bankInterface['name'];
            
            $record->origin->autor = new stdClass();
            $record->origin->autor->name = "AFX";
            $record->origin->autor->rif = $rifafxAPI;
            $record->origin->autor->phone = $BA_phoneAFX;
        } else {
            $record->origin = null;
        }
        
        
        
        $record->description = $row['note'].($row['txid'] ? " - ".$row['txid'] : "");
        
        $record->main = new stdClass();
        $record->main->currency = new stdClass();
        $record->main->currency->id = (int)$currencydata['id'];
        $record->main->currency->symbol = $currencydata['symbol'];
        $record->main->currency->name = $currencydata['name'];
        $record->main->currency->decimals = (int)$currencydata['decimals'];
        
        $record->main->amount = new stdClass();
        $record->main->amount->number = bcdiv($row['amount'],1,$currencydata['decimals'])*1;
        $record->main->amount->formatted = numberFormatt($record->main->amount->number,$currencydata['decimals']);
        
        // Buscamos la cuenta de origen
        $record->wallet = new stdClass();
        $record->wallet->id = (int)$row['accountid'];
        
        // Buscamos la wallet si la cuenta no es ves
        $sql =  "SELECT     wallets.*, ".
            "currencies.id AS currencyid, ".
            "currencies.name AS currencyname, ".
            "currencies.decimals AS currencydecimals, ".
            "currencies.symbol AS currencysymbol ".
            "FROM       accounts " .
            "LEFT JOIN wallets ON accounts.id = wallets.accountid ".
            "JOIN currencies ON accounts.currencyid = currencies.id ".
            "LEFT JOIN ( " .
            "               SELECT w.wallet,w.accountid, MAX(w.expirationDate) AS expirationDate, MAX(id) AS id ".
            "               FROM wallets w WHERE w.accountid = ".$record->wallet->id. " GROUP BY w.accountid " .
            "           ) lasts ".
            "ON         wallets.id = lasts.id " . 
            "WHERE      accounts.id = ".$record->wallet->id;
        if (!$result=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db).$sql));
            
        $wallet = $result->fetch_assoc();

        $record->wallet->dsc = $wallet["wallet"] ? $wallet["wallet"] : '';
        
        $record->wallet->currency = new stdClass();
        $record->wallet->currency->id = (int)$wallet['currencyid'];
        $record->wallet->currency->name = $wallet['currencyname'];
        $record->wallet->currency->symbol = $wallet['currencysymbol'];
        $record->wallet->currency->decimals = (int)$wallet['currencydecimals'];

        if($row['txid'] == null){
           $record->wallet->amount = null;
           $record->wallet->fee = null;
           $record->wallet->total = null;
        } else {
            // Buscamos el movimiento con el monto
            $sql = "SELECT (ABS(amount) + ABS(amountghost)) AS amount ".
            "       FROM transactions ".
            "       WHERE txid = ".$row['txid'].
            "       AND accountid = ".$record->wallet->id.
            "       AND dsc LIKE 'Venta%'";
            if (!$resTx=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db).$sql));
                
            $amountData = $resTx->fetch_assoc();
            
            $record->wallet->amount = new stdClass();
            $record->wallet->amount->number = bcdiv($amountData['amount'],1,$wallet['currencydecimals'])*1;
            $record->wallet->amount->formatted = numberFormatt($record->wallet->amount->number,$wallet['currencydecimals']);
            
            // Buscamos el movimiento con el fee
            $sql = "SELECT (ABS(amount) + ABS(amountghost)) AS amount ".
            "       FROM transactions ".
            "       WHERE txid = ".$row['txid'].
            "       AND accountid = ".$record->wallet->id.
            "       AND dsc LIKE 'Comisi%'";
            if (!$resTx=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db).$sql));
                
            $feeData = $resTx->fetch_assoc();
            
            $record->wallet->fee = new stdClass();
            $record->wallet->fee->number = bcdiv($feeData['amount'],1,$wallet['currencydecimals'])*1;
            $record->wallet->fee->formatted = numberFormatt($record->wallet->fee->number,$wallet['currencydecimals']);
            
            // Scamos el total
            $record->wallet->total = new stdClass();
            $record->wallet->total->number = bcdiv($record->wallet->fee->number + $record->wallet->amount->number,1,$wallet['currencydecimals'])*1;
            $record->wallet->total->formatted = numberFormatt($record->wallet->total->number,$wallet['currencydecimals']);
        }
    
        $rate = determinateRate($db, $currencydata["id"], $wallet['currencyid'], $currencydata['decimals']);
        
        $record->rate = new stdClass();
        $record->rate->number = (float)$rate->number;
        $record->rate->formatted = $rate->formatted;
        
        $record->fees = $fees;
        
        
        
        $records [] = $record;
    }
        
    $out->records = $records;
    
    header("HTTP/1.1 200");
    echo(json_encode($out));
    die();
    
    /* Funcion para determinar nombre del status
    *  1=por validar
    *  2=por enviar
    *  3=enviado
    *  4=rechazado	
    */
    function determinateStatusName($status){
        $statusname = "";
        switch($status){
            case 1:
                $statusname = "Por validar";
                break;
            case 2:
                $statusname = "Por enviar";
                break;
            case 3:
                $statusname = "Enviado";
                break;
            case 4:
                $statusname = "Rechazado";
                break;
        }
        
        return $statusname;
    }
    
    /*  Funcion para determinar el orden de los resultados, donde:
    *   1 = Fecha de envio ASCENDENTE
    *   2 = Destinatario ASCENDENTE
    *   3 = Banco ASCENDENTE
    *   4 = Monto ASCENDENTE
    *   -1 = Fecha de envio DESCENDENTE
    *   -2 = Destinatario DESCENDENTE
    *   -3 = Banco DESCENDENTE
    *   -4 = Monto DESCENDENTE
    *   Y si el order es enviado si ser algunos de estos valores,
    *   Retornara un error para avisarle al desarrollador/@
    */
    function determinateOrder($order){
        $orderres = "";
        switch($order){
            case 1:
                $orderres = "sendat ASC";
                break;
            case 2:
                $orderres = "recipient ASC";
                break;
            case 3:
                $orderres = "bankcode ASC";
                break;
            case 4:
                $orderres = "amount ASC";
                break;
            case -1:
                $orderres = "sendat DESC";
                break;
            case -2:
                $orderres = "recipient DESC";
                break;
            case -3:
                $orderres = "bankcode DESC";
                break;
            case -4:
                $orderres = "amount DESC";
                break;
            default:
                badEnd("400", array("msg"=>"Order debe ser 1, 2, 3, 4, -1, -2, -3 o -4"));
                break;
        }
        
        return $orderres;
    }
    
?>