<?php
// cms/api/operations/list

    header("Content-Type:application/json");
    include("../../../settings/dbconn.php");
    include("../../../settings/utils.php");
    
    // parametros obligatorios
    $parmsob = array("sessionid","offset","numofrecords");
    if (!parametrosValidos($_GET, $parmsob))
        badEnd("400", array("msg"=>"Parametros obligatorios " . implode(", ", $parmsob)));
    
    //Declaramos e inicializamos nuestras variables
    $out= new stdClass();
    $sessionid = mysqli_real_escape_string($db, $_GET["sessionid"]);
    $offset = mysqli_real_escape_string($db, $_GET["offset"]);
    $numofrecords = mysqli_real_escape_string($db, $_GET["numofrecords"]);
    $order = determinateOrder(mysqli_real_escape_string($db, $_GET["order"]));
    $time = "";
    $status = mysqli_real_escape_string($db, $_GET["status"]);
    $kindDeposit = "";
    $kindWithdra = "";
    //TIPOS
    //  0 = BOTH; 1 = DEPOSITOS; 2 = RETIROS; 3 = TRANSFERENCIAS; 4 = PAY XFER
    $type = "0";
    $currencyid = "";
    $limit = "LIMIT ".$offset.", ".$numofrecords;
    $filterTx = '';
    $filterDeposit = '';
    $filterWithdrawals = '';
    
    //Validamos que la session del usuario sea valida para continuar
    isSessionValidCMS($db, $sessionid);
    
    //Validamos que el usuario tenga privilegios
    if(!tienePrivilegio($db, $sessionid, 33))
        badEnd("403", array("msg"=>"No tiene Privilegios"));
        
        
    //En caso de que el filter sea enviado, se construye el query
    if(isset($_GET["filter"]) && $_GET["filter"] != ""){
        // Iniciamos la cadena del query
        $filterTx = " AND ( ";
        $filterDeposit = " AND ( ";
        $filterWithdrawals = " AND ( ";
        
        //Separamos el filtro por el simbolo +
        $filterEl = separateFilter(mysqli_real_escape_string($db, $_GET["filter"]));
        
        //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){
                $filterTx .= "users.usr LIKE '".$el."%' OR ".
                "           usercompanies.name LIKE '".$el."%' OR ".
                "           userpersons.fname LIKE '".$el."%'".
                "           OR t.id = '".$el."'".
                "           OR IF( (SELECT deposits.transactionid AS depositid ".
                "                   FROM deposits ".
                "                   WHERE t.id = deposits.transactionid) IS NOT NULL, 'Deposito', ".
                "                   IF( (SELECT withdrawals.transactionid AS depositid ".
                "                       FROM withdrawals ".
                "                       WHERE withdrawals.transactionid = t.id) IS NOT NULL , 'Retiro', 'Transferencia')".
                "               ) LIKE '".$el."%'".
                "           OR (SELECT symbol FROM currencies WHERE id = accounts.currencyid) LIKE '".$el."%'".
                "           OR (CASE ".
                "               WHEN t.amountghost = 0 AND t.paypending = 0 THEN 'Aprobado' ".
                "               ELSE 'Pendiente' ".
                "           END) LIKE '".$el."%' ".
                "           OR DATE(t.datecreated) = DATE('".$dateel."') ".
                "           ".(validNumber($el) ? "OR t.amount LIKE '%".floatval($el)."%'" : (validNumberFormatted($el) ? " OR  t.amount LIKE '%".parseFormattedNumber($el)."%'" : "")).
                "           ".(validNumber($el) ? "OR t.amount LIKE '%-".floatval($el)."%'" : (validNumberFormatted($el) ? " OR  t.amount LIKE '%-".parseFormattedNumber($el)."%'" : ""));
                
                $filterDeposit .= "users.usr LIKE '".$el."%' OR ".
                "           usercompanies.name LIKE '".$el."%' OR ".
                "           userpersons.fname LIKE '".$el."%' ".
                "           OR t.id = '".$el."'".
                "           OR IF( (SELECT deposits.transactionid AS depositid ".
                "                   FROM deposits ".
                "                   WHERE t.id = deposits.transactionid) IS NOT NULL, 'Deposito', ".
                "                   IF( (SELECT withdrawals.transactionid AS depositid ".
                "                       FROM withdrawals ".
                "                       WHERE withdrawals.transactionid = t.id) IS NOT NULL , 'Retiro', 'Transferencia')".
                "               ) LIKE '".$el."%'".
                "           OR (SELECT symbol FROM currencies WHERE id = accounts.currencyid) LIKE '".$el."%'".
                "           OR (CASE ".
                "               WHEN deposits.rejected IS NULL AND deposits.valid IS NULL THEN 'Pendiente' ".
                "               WHEN deposits.rejected IS NULL AND deposits.valid IS NOT NULL THEN 'Aprobado' ".
                "               WHEN deposits.rejected IS NOT NULL AND deposits.valid IS NULL THEN 'Rechazado' ".
                "           END) LIKE '".$el."%' ".
                "           OR DATE(t.datecreated) = DATE('".$dateel."') ".
                "           ".(validNumber($el) ? "OR deposits.amount LIKE '%".floatval($el)."%'" : (validNumberFormatted($el) ? " OR deposits.amount LIKE '%".parseFormattedNumber($el)."%' " : ""));
                
                $filterWithdrawals .= "users.usr LIKE '".$el."%' OR ".
                "           usercompanies.name LIKE '".$el."%' OR ".
                "           userpersons.fname LIKE '".$el."%' ".
                "           OR t.id = '".$el."'".
                "           OR IF( (SELECT deposits.transactionid AS depositid ".
                "                   FROM deposits ".
                "                   WHERE t.id = deposits.transactionid) IS NOT NULL, 'Deposito', ".
                "                   IF( (SELECT withdrawals.transactionid AS depositid ".
                "                       FROM withdrawals ".
                "                       WHERE withdrawals.transactionid = t.id) IS NOT NULL , 'Retiro', 'Transferencia')".
                "               ) LIKE '".$el."%'".
                "           OR (SELECT symbol FROM currencies WHERE id = accounts.currencyid) LIKE '".$el."%'".
                "           OR (CASE ".
                "               WHEN withdrawals.rejected IS NULL AND withdrawals.valid IS NULL THEN 'Pendiente' ".
                "               WHEN withdrawals.rejected IS NULL AND withdrawals.valid IS NOT NULL THEN 'Aprobado' ".
                "               WHEN withdrawals.rejected IS NOT NULL AND withdrawals.valid IS NULL THEN 'Rechazado' ".
                "           END) LIKE '".$el."%' ".
                "           OR DATE(t.datecreated) = DATE('".$dateel."') ".
                "           ".(validNumber($el) ? "OR (ABS(withdrawals.amount) + ".
                "               IF( (SELECT interface FROM currencies WHERE id = accounts.currencyid) != 4 ".
                "               AND (SELECT interface FROM currencies WHERE id = accounts.currencyid) != 5, ABS(withdrawals.fee) ,0 )) LIKE '%".floatval($el)."%'" : (validNumberFormatted($el) ? " OR (ABS(withdrawals.amount) + IF( (SELECT interface FROM currencies WHERE id = accounts.currencyid) != 4 AND (SELECT interface FROM currencies WHERE id = accounts.currencyid) != 5, ABS(withdrawals.fee) ,0 )) LIKE '%".parseFormattedNumber($el)."%'" : ""));
            
                
            }else{
                
                $filterTx .= " OR users.usr LIKE '".$el."%' OR ".
                "           usercompanies.name LIKE '".$el."%' OR ".
                "           userpersons.fname LIKE '".$el."%'".
                "           OR t.id = '".$el."'".
                "           OR IF( (SELECT deposits.transactionid AS depositid ".
                "                   FROM deposits ".
                "                   WHERE t.id = deposits.transactionid) IS NOT NULL, 'Deposito', ".
                "                   IF( (SELECT withdrawals.transactionid AS depositid ".
                "                       FROM withdrawals ".
                "                       WHERE withdrawals.transactionid = t.id) IS NOT NULL , 'Retiro', 'Transferencia')".
                "               ) LIKE '".$el."%'".
                "           OR (SELECT symbol FROM currencies WHERE id = accounts.currencyid) LIKE '".$el."%'".
                "           OR (CASE ".
                "               WHEN t.amountghost = 0 AND t.paypending = 0 THEN 'Aprobado' ".
                "               ELSE 'Pendiente' ".
                "           END) LIKE '".$el."%' ".
                "           OR DATE(t.datecreated) = DATE('".$dateel."') ".
                "           ".(validNumber($el) ? "OR t.amount LIKE '%".floatval($el)."%' " : (validNumberFormatted($el) ? " OR  t.amount LIKE '%".parseFormattedNumber($el)."%' " : "")).
                "           ".(validNumber($el) ? "OR t.amount LIKE '%-".floatval($el)."%' " : (validNumberFormatted($el) ? " OR  t.amount LIKE '%-".parseFormattedNumber($el)."%' " : ""));
                
                $filterDeposit .= " OR users.usr LIKE '".$el."%' OR ".
                "           usercompanies.name LIKE '".$el."%' OR ".
                "           userpersons.fname LIKE '".$el."%' ".
                "           OR t.id = '".$el."'".
                "           OR IF( (SELECT deposits.transactionid AS depositid ".
                "                   FROM deposits ".
                "                   WHERE t.id = deposits.transactionid) IS NOT NULL, 'Deposito', ".
                "                   IF( (SELECT withdrawals.transactionid AS depositid ".
                "                       FROM withdrawals ".
                "                       WHERE withdrawals.transactionid = t.id) IS NOT NULL , 'Retiro', 'Transferencia')".
                "               ) LIKE '".$el."%'".
                "           OR (SELECT symbol FROM currencies WHERE id = accounts.currencyid) LIKE '".$el."%'".
                "           OR (CASE ".
                "               WHEN deposits.rejected IS NULL AND deposits.valid IS NULL THEN 'Pendiente' ".
                "               WHEN deposits.rejected IS NULL AND deposits.valid IS NOT NULL THEN 'Aprobado' ".
                "               WHEN deposits.rejected IS NOT NULL AND deposits.valid IS NULL THEN 'Rechazado' ".
                "           END) LIKE '".$el."%' ".
                "           OR DATE(t.datecreated) = DATE('".$dateel."') ".
                "           ".(validNumber($el) ? "OR deposits.amount LIKE '%".floatval($el)."%' " : (validNumberFormatted($el) ? " OR deposits.amount LIKE '%".parseFormattedNumber($el)."%' " : ""));
                
                $filterWithdrawals .= " OR users.usr LIKE '".$el."%' OR ".
                "           usercompanies.name LIKE '".$el."%' OR ".
                "           userpersons.fname LIKE '".$el."%' ".
                "           OR t.id = '".$el."'".
                "           OR IF( (SELECT deposits.transactionid AS depositid ".
                "                   FROM deposits ".
                "                   WHERE t.id = deposits.transactionid) IS NOT NULL, 'Deposito', ".
                "                   IF( (SELECT withdrawals.transactionid AS depositid ".
                "                       FROM withdrawals ".
                "                       WHERE withdrawals.transactionid = t.id) IS NOT NULL , 'Retiro', 'Transferencia')".
                "               ) LIKE '".$el."%'".
                "           OR (SELECT symbol FROM currencies WHERE id = accounts.currencyid) LIKE '".$el."%'".
                "           OR (CASE ".
                "               WHEN withdrawals.rejected IS NULL AND withdrawals.valid IS NULL THEN 'Pendiente' ".
                "               WHEN withdrawals.rejected IS NULL AND withdrawals.valid IS NOT NULL THEN 'Aprobado' ".
                "               WHEN withdrawals.rejected IS NOT NULL AND withdrawals.valid IS NULL THEN 'Rechazado' ".
                "           END) LIKE '".$el."%' ".
                "           OR DATE(t.datecreated) = DATE('".$dateel."') ".
                "           ".(validNumber($el) ? "OR (ABS(withdrawals.amount) + ABS(withdrawals.fee)) LIKE '%".floatval($el)."%' " : (validNumberFormatted($el) ? " OR (ABS(withdrawals.amount) + ABS(withdrawals.fee)) LIKE '%".parseFormattedNumber($el)."%' " : ""));
            
            }
        }
        
        $filterTx .= " ) ";
        $filterDeposit .= " ) ";
        $filterWithdrawals .= " ) ";
    }
    
    //En caso de que el fecha sea enviado, se construye el query
    if(isset($_GET["fecha"]) && $_GET["fecha"] != ""){
        $time = determinateTime(mysqli_real_escape_string($db, $_GET["fecha"]));
    }
    
    //En caso de que el type sea enviado, se construye el query
    if(isset($_GET["type"]) && $_GET["type"] != ""){
        $type = mysqli_real_escape_string($db, $_GET["type"]);
    }
    
    //En caso de que el currencyid sea enviado, se construye el query
    if(isset($_GET["currencyid"]) && $_GET["currencyid"] != ""){
        $currencyid = "AND accounts.currencyid = ".mysqli_real_escape_string($db, $_GET["currencyid"]);
    }
    
    //Validamos que tipos de operaciones se buscan
    //1=Pendientes, 2=Aprobados, 3=Rechazados, ""=Todos.
    if($status == 1){
        $kindDeposit = " AND deposits.rejected IS NULL ".
        "               AND deposits.valid IS NULL";
        $kindWithdra = " AND withdrawals.rejected IS NULL".
        "               AND withdrawals.valid IS NULL";
        $kindXtr = " AND ((SELECT        CASE ".
	    "                   WHEN t.amountghost = 0 THEN 1 ".
	    "                   ELSE 0 ".
	    "                   END AS status".
	    "                   FROM transactions ".
	    "                   WHERE transactions.id = t.id) = 0".
	    "           OR paypending = 1)";
    }else if($status == 2){
        $kindDeposit = " AND  deposits.rejected IS NULL".
        "               AND deposits.valid IS NOT NULL";
        $kindWithdra = " AND withdrawals.rejected IS NULL ".
        "               AND withdrawals.valid IS NOT NULL";
        $kindXtr = " AND (SELECT       CASE ".
	    "                   WHEN t.amountghost = 0 THEN 1 ".
	    "                   ELSE 0 ".
	    "                   END AS status".
	    "                   FROM transactions ".
	    "                   WHERE transactions.id = t.id) = 1".
	    "           AND paypending = 0";
    }else if($status == 3){
        $kindDeposit = " AND deposits.rejected IS NOT NULL ".
        "               AND deposits.valid IS NULL";
        $kindWithdra = " AND  withdrawals.rejected IS NOT NULL".
        "               AND withdrawals.valid IS NULL";
        $kindXtr = " AND (SELECT        CASE ".
	    "                   WHEN t.amountghost = 0 THEN 1 ".
	    "                   ELSE 0 ".
	    "                   END AS status".
	    "                   FROM transactions ".
	    "                   WHERE transactions.id = t.id) = 2".
	    "           AND paypending = 0";
    }else{
        $kindDeposit = "";
        $kindWithdra = "";
        $kindXtr = "";
    }
    
    if($type == 1){
        
        $sql = "SELECT COUNT(t.id) AS qty ".
        "       FROM deposits, transactions t, accounts, users ".
        "".
        "       LEFT JOIN usercompanies ON usercompanies.usrid = users.id ".
        "       LEFT JOIN userpersons ON userpersons.usrid = users.id ".
        "".
        "       WHERE t.id = deposits.transactionid ".
        "       AND t.validator IS NULL ".
        "       ".($time == "" ? "" : "AND DATE(deposits.createddate) >= ".$time." " ).
        "       AND t.accountid = accounts.id ".
        "       AND accounts.userid = users.id ".
        "       ".$currencyid." ".
        "       ".$kindDeposit." ".
        "       ".$filterDeposit;
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
            
        $row = $rs->fetch_assoc();
        $out->numofrecords = (int)$row["qty"];
        
        $sql = "SELECT t.id AS transactionid, ".
        "       (SELECT sum(amount) FROM transactions WHERE transactions.txid = t.id) AS qty,".
        "       deposits.createddate AS createddate, ".
        "       deposits.amount AS amount, ".
        "       deposits.fee AS fee, ".
        "       deposits.valid AS valid, ".
        "       deposits.rejected AS rejected, ".
        "       t.accountid AS txaccount, ".
        "       t.dsc AS txdsc, ".
        "       t.paypending AS paypending, ".
        "       t.accountorigin AS txaccorigin,".
        "       t.accountdestination AS txaccdestination, ".
        "       accounts.currencyid AS accountcurrencyid, ".
        "       accounts.paymentmethodid AS accountpaymentmethodid, ".
        "       accounts.status AS accountstatus, ".
        "       accounts.rejected AS accountrejected, ".
        "       accounts.approved AS accountapproved, ".
        "       users.usr AS usr, ".
        "       users.id AS usrid, ".
        "       userpersons.fname AS personname, ".
        "       userpersons.flastname AS personlastname, ".
        "       usercompanies.name AS companyname, ".
        "       usercompanies.comercialname AS companycomercialname, ".
        "       CASE ".
	    "           WHEN deposits.rejected IS NULL AND deposits.valid IS NULL THEN 0 ".
	    "           WHEN deposits.rejected IS NULL AND deposits.valid IS NOT NULL THEN 1 ".
	    "           WHEN deposits.rejected IS NOT NULL AND deposits.valid IS NULL THEN 2 ".
	    "       END AS status, ".
	    "       CASE ".
	    "           WHEN deposits.rejected IS NULL AND deposits.valid IS NULL THEN 'Pendiente' ".
	    "           WHEN deposits.rejected IS NULL AND deposits.valid IS NOT NULL THEN 'Aprobado' ".
	    "           WHEN deposits.rejected IS NOT NULL AND deposits.valid IS NULL THEN 'Rechazado' ".
	    "       END AS statusorder ".
        "       FROM deposits, transactions t, accounts, users ".
        "".
        "       LEFT JOIN usercompanies ON usercompanies.usrid = users.id ".
        "       LEFT JOIN userpersons ON userpersons.usrid = users.id ".
        "".
        "       WHERE t.id = deposits.transactionid ".
        "       AND t.validator IS NULL ".
        "       ".($time == "" ? "" : "AND DATE(deposits.createddate) >= ".$time." " ).
        "       AND t.accountid = accounts.id ".
        "       AND accounts.userid = users.id ".
        "       ".$currencyid." ".
        "       ".$kindDeposit." ".
        "       ".$filterDeposit." ".
        "       ".$order.
        "       ".$limit;
        
    }else if($type == 2){
        
        $sql = "SELECT COUNT(t.id) AS qty ".
        "       FROM withdrawals, transactions t, accounts, users ".
        "".
        "       LEFT JOIN usercompanies ON usercompanies.usrid = users.id ".
        "       LEFT JOIN userpersons ON userpersons.usrid = users.id ".
        "".
        "       WHERE t.id = withdrawals.transactionid ".
        "       AND t.validator IS NULL ".
        "       ".($time == "" ? "" : "AND DATE(withdrawals.createddate) >= ".$time." " ).
        "       AND t.accountid = accounts.id ".
        "       AND accounts.userid = users.id ".
        "       ".$currencyid." ".
        "       ".$kindWithdra." ".
        "       ".$filterWithdrawals;
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
            
        $row = $rs->fetch_assoc();
        $out->numofrecords = (int)$row["qty"];
        
        $sql = "SELECT t.id AS transactionid, ".
        "       (SELECT sum(amount) FROM transactions WHERE transactions.txid = t.id) AS qty,".
        "       withdrawals.createddate AS createddate, ".
        "       withdrawals.amount AS amount, ".
        "       withdrawals.fee AS fee, ".
        "       withdrawals.valid AS valid, ".
        "       withdrawals.rejected AS rejected, ".
        "       t.accountid AS txaccount, ".
        "       t.dsc AS txdsc, ".
        "       t.paypending AS paypending, ".
        "       t.accountorigin AS txaccorigin,".
        "       t.accountdestination AS txaccdestination, ".
        "       accounts.currencyid AS accountcurrencyid, ".
        "       accounts.paymentmethodid AS accountpaymentmethodid, ".
        "       accounts.status AS accountstatus, ".
        "       accounts.rejected AS accountrejected, ".
        "       accounts.approved AS accountapproved, ".
        "       users.usr AS usr, ".
        "       users.id AS usrid, ".
        "       userpersons.fname AS personname, ".
        "       userpersons.flastname AS personlastname, ".
        "       usercompanies.name AS companyname, ".
        "       usercompanies.comercialname AS companycomercialname, ".
        "       CASE ".
	    "           WHEN withdrawals.rejected IS NULL AND withdrawals.valid IS NULL THEN 0 ".
	    "           WHEN withdrawals.rejected IS NULL AND withdrawals.valid IS NOT NULL THEN 1 ".
	    "           WHEN withdrawals.rejected IS NOT NULL AND withdrawals.valid IS NULL THEN 2 ".
	    "       END AS status, ".
	    "       CASE ".
	    "           WHEN withdrawals.rejected IS NULL AND withdrawals.valid IS NULL THEN 'Pendiente' ".
	    "           WHEN withdrawals.rejected IS NULL AND withdrawals.valid IS NOT NULL THEN (IF((withdrawals.filetype='9191' OR withdrawals.paymentref='0000'),'Aprobado*', 'Aprobado')) ".
	    "           WHEN withdrawals.rejected IS NOT NULL AND withdrawals.valid IS NULL THEN 'Rechazado' ".
	    "       END AS statusorder ".
        "       FROM withdrawals, transactions t, accounts, users ".
        "".
        "       LEFT JOIN usercompanies ON usercompanies.usrid = users.id ".
        "       LEFT JOIN userpersons ON userpersons.usrid = users.id ".
        "".
        "       WHERE t.id = withdrawals.transactionid ".
        "       AND t.validator IS NULL ".
        "       ".($time == "" ? "" : "AND DATE(withdrawals.createddate) >= ".$time." " ).
        "       AND t.accountid = accounts.id ".
        "       AND accounts.userid = users.id ".
        "       ".$currencyid." ".
        "       ".$kindWithdra." ".
        "       ".$filterWithdrawals." ".
        "       ".$order.
        "       ".$limit;
        
    }else if ($type == 3){
        
        $sql = "SELECT COUNT(t.id) AS qty ".
        "       FROM transactions t, accounts, users ".
        "".
        "       LEFT JOIN usercompanies ON usercompanies.usrid = users.id ".
        "       LEFT JOIN userpersons ON userpersons.usrid = users.id ".
        "".
        "       WHERE (t.dsc LIKE 'Transferencia a%' OR t.dsc LIKE 'Transferencia de%')".
        "       AND t.validator IS NULL ".
        "       AND t.amount != 0".
        "       ".($time == "" ? "" : "AND DATE(t.datecreated) >= ".$time." " ).
        "       AND t.accountid = accounts.id ".
        "       AND accounts.userid = users.id ".
        "       ".$currencyid." ".
        "       ".$kindXtr." ".
        "       ".$filterTx;
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
            
        $row = $rs->fetch_assoc();
        $out->numofrecords = 0;
        $out->numofrecords += (int)$row["qty"];
    
        $sql = "SELECT t.id AS transactionid, ".
        "       (SELECT sum(amount) FROM transactions WHERE transactions.txid = t.id) AS qty,".
        "       t.datecreated AS createddate, ".
        "       t.amount AS amount, ".
        "       t.amountghost AS fee, ".
        "       t.txid AS valid, ".
        "       t.txid AS rejected, ".
        "       t.accountid AS txaccount, ".
        "       t.dsc AS txdsc, ".
        "       t.paypending AS paypending, ".
        "       t.accountorigin AS txaccorigin,".
        "       t.accountdestination AS txaccdestination, ".
        "       accounts.currencyid AS accountcurrencyid, ".
        "       accounts.paymentmethodid AS accountpaymentmethodid, ".
        "       accounts.status AS accountstatus, ".
        "       accounts.rejected AS accountrejected, ".
        "       accounts.approved AS accountapproved, ".
        "       users.usr AS usr, ".
        "       users.id AS usrid, ".
        "       userpersons.fname AS personname, ".
        "       userpersons.flastname AS personlastname, ".
        "       usercompanies.name AS companyname, ".
        "       usercompanies.comercialname AS companycomercialname, ".
        "       CASE ".
	    "           WHEN t.amountghost = 0 AND t.paypending = 0 THEN 1 ".
	    "           ELSE 0 ".
	    "       END AS status, ".
	    "       CASE ".
	    "           WHEN t.amountghost = 0 AND t.paypending = 0 THEN 'Aprobado' ".
	    "           ELSE 'Pendiente' ".
	    "       END AS statusorder ".
        "       FROM transactions t, accounts, users ".
        "".
        "       LEFT JOIN usercompanies ON usercompanies.usrid = users.id ".
        "       LEFT JOIN userpersons ON userpersons.usrid = users.id ".
        "".
        "       WHERE (t.dsc LIKE 'Transferencia a%' OR t.dsc LIKE 'Transferencia de%')".
        "       AND t.validator IS NULL ".
        "       AND t.amount != 0".
        "       ".($time == "" ? "" : "AND DATE(t.datecreated) >= ".$time." " ).
        "       AND t.accountid = accounts.id ".
        "       AND accounts.userid = users.id ".
        "       ".$currencyid." ".
        "       ".$kindXtr." ".
        "       ".$filterTx." ".
        "       ".$order.
        "       ".$limit;
    }else if ($type == 4){
        
        $sql = "SELECT COUNT(t.id) AS qty ".
        "       FROM transactions t, accounts, users ".
        "".
        "       LEFT JOIN usercompanies ON usercompanies.usrid = users.id ".
        "       LEFT JOIN userpersons ON userpersons.usrid = users.id ".
        "".
        "       WHERE (t.dsc LIKE 'Transferencia PAY%' OR (t.dsc LIKE '%Solicitud de Cobro PAY%' AND t.paypending = 0))".
        "       AND t.validator IS NULL ".
        "       AND t.amount != 0".
        "       ".($time == "" ? "" : "AND DATE(t.datecreated) >= ".$time." " ).
        "       AND t.accountid = accounts.id ".
        "       AND accounts.userid = users.id ".
        "       ".$currencyid." ".
        "       ".$kindXtr." ".
        "       ".$filterTx;
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
            
        $row = $rs->fetch_assoc();
        $out->numofrecords = 0;
        $out->numofrecords += (int)$row["qty"];
    
        $sql = "SELECT t.id AS transactionid, ".
        "       (SELECT sum(amount) FROM transactions WHERE transactions.txid = t.id) AS qty,".
        "       t.datecreated AS createddate, ".
        "       t.amount AS amount, ".
        "       t.amountghost AS fee, ".
        "       t.txid AS valid, ".
        "       t.txid AS rejected, ".
        "       t.accountid AS txaccount, ".
        "       t.dsc AS txdsc, ".
        "       t.paypending AS paypending, ".
        "       t.accountorigin AS txaccorigin,".
        "       t.accountdestination AS txaccdestination, ".
        "       accounts.currencyid AS accountcurrencyid, ".
        "       accounts.paymentmethodid AS accountpaymentmethodid, ".
        "       accounts.status AS accountstatus, ".
        "       accounts.rejected AS accountrejected, ".
        "       accounts.approved AS accountapproved, ".
        "       users.usr AS usr, ".
        "       users.id AS usrid, ".
        "       userpersons.fname AS personname, ".
        "       userpersons.flastname AS personlastname, ".
        "       usercompanies.name AS companyname, ".
        "       usercompanies.comercialname AS companycomercialname, ".
        "       CASE ".
        "           WHEN (SELECT tran.dsc AS dsc FROM transactions tran WHERE tran.dsc LIKE CONCAT('%', t.txid, '%') AND tran.id = tran.txid LIMIT 1) IS NOT NULL THEN 2".
	    "           WHEN t.amountghost = 0 AND t.paypending = 0 THEN 1 ".
	    "           ELSE 0 ".
	    "       END AS status, ".
	    "       CASE ".
	    "           WHEN t.amountghost = 0 AND t.paypending = 0 THEN 'Aprobado' ".
	    "           ELSE 'Pendiente' ".
	    "       END AS statusorder ".
        "       FROM transactions t, accounts, users ".
        "".
        "       LEFT JOIN usercompanies ON usercompanies.usrid = users.id ".
        "       LEFT JOIN userpersons ON userpersons.usrid = users.id ".
        "".
        "       WHERE (t.dsc LIKE 'Transferencia PAY%' OR (t.dsc LIKE '%Solicitud de Cobro PAY%' AND t.paypending = 0))".
        "       AND t.validator IS NULL ".
        "       AND t.amount != 0".
        "       ".($time == "" ? "" : "AND DATE(t.datecreated) >= ".$time." " ).
        "       AND t.accountid = accounts.id ".
        "       AND accounts.userid = users.id ".
        "       ".$currencyid." ".
        "       ".$kindXtr." ".
        "       ".$filterTx." ".
        "       ".$order.
        "       ".$limit;
    }else{
        
        $sql = "SELECT COUNT(t.id) AS qty, 'dep' AS ref   ".
        "       FROM deposits, transactions t, accounts, users ".
        "".
        "       LEFT JOIN usercompanies ON usercompanies.usrid = users.id ".
        "       LEFT JOIN userpersons ON userpersons.usrid = users.id ".
        "".
        "       WHERE t.id = deposits.transactionid ".
        "       AND t.validator IS NULL ".
        "       ".($time == "" ? "" : "AND DATE(deposits.createddate) >= ".$time." " ).
        "       AND t.accountid = accounts.id ".
        "       AND accounts.userid = users.id ".
        "       ".$currencyid." ".
        "       ".$kindDeposit." ".
        "       ".$filterDeposit." ". 
        "".
        "       UNION ".
        "".
        "       SELECT COUNT(t.id) AS qty, 'with' AS ref   ".
        "       FROM withdrawals, transactions t, accounts, users ".
        "".
        "       LEFT JOIN usercompanies ON usercompanies.usrid = users.id ".
        "       LEFT JOIN userpersons ON userpersons.usrid = users.id ".
        "".
        "       WHERE t.id = withdrawals.transactionid ".
        "       AND t.validator IS NULL ".
        "       ".($time == "" ? "" : "AND DATE(withdrawals.createddate) >= ".$time." " ).
        "       AND t.accountid = accounts.id ".
        "       AND accounts.userid = users.id ".
        "       ".$currencyid." ".
        "       ".$kindWithdra." ".
        "       ".$filterWithdrawals." ".
        "".
        "       UNION ".
        "".
        "       SELECT COUNT(t.id) AS qty, 'tx' AS ref   ".
        "       FROM transactions t, accounts, users ".
        "".
        "       LEFT JOIN usercompanies ON usercompanies.usrid = users.id ".
        "       LEFT JOIN userpersons ON userpersons.usrid = users.id ".
        "".
        "       WHERE (t.dsc LIKE 'Transferencia a%' OR t.dsc LIKE 'Transferencia de%')".
        "       AND t.validator IS NULL ".
        "       AND t.amount != 0".
        "       ".($time == "" ? "" : "AND DATE(t.datecreated) >= ".$time." " ).
        "       AND t.accountid = accounts.id ".
        "       AND accounts.userid = users.id ".
        "       ".$currencyid." ".
        "       ".$kindXtr." ".
        "       ".$filterTx.
        "".
        "       UNION ".
        "".
        "       SELECT COUNT(t.id) AS qty, 'tx' AS ref   ".
        "       FROM transactions t, accounts, users ".
        "".
        "       LEFT JOIN usercompanies ON usercompanies.usrid = users.id ".
        "       LEFT JOIN userpersons ON userpersons.usrid = users.id ".
        "".
        "       WHERE (t.dsc LIKE 'Transferencia PAY%' OR (t.dsc LIKE '%Solicitud de Cobro PAY%' AND t.paypending = 0))".
        "       AND t.validator IS NULL ".
        "       AND t.amount != 0".
        "       ".($time == "" ? "" : "AND DATE(t.datecreated) >= ".$time." " ).
        "       AND t.accountid = accounts.id ".
        "       AND accounts.userid = users.id ".
        "       ".$currencyid." ".
        "       ".$kindXtr." ".
        "       ".$filterTx;
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
            
        
        $out->numofrecords = 0;
        while($row = $rs->fetch_assoc()){
            $out->numofrecords += (int)$row["qty"];
        }
        
        $sql = "SELECT t.id AS transactionid, ".
        "       (SELECT sum(amount) FROM transactions WHERE transactions.txid = t.id) AS qty,".
        "       deposits.createddate AS createddate, ".
        "       deposits.amount AS amount, ".
        "       deposits.fee AS fee, ".
        "       deposits.valid AS valid, ".
        "       deposits.rejected AS rejected, ".
        "       (ABS(deposits.amount) + ABS(deposits.fee)) AS test, ".
        "       t.accountid AS txaccount, ".
        "       t.dsc AS txdsc, ".
        "       t.paypending AS paypending, ".
        "       t.accountorigin AS txaccorigin,".
        "       t.accountdestination AS txaccdestination, ".
        "       accounts.currencyid AS accountcurrencyid, ".
        "       accounts.paymentmethodid AS accountpaymentmethodid, ".
        "       accounts.status AS accountstatus, ".
        "       accounts.rejected AS accountrejected, ".
        "       accounts.approved AS accountapproved, ".
        "       users.usr AS usr, ".
        "       users.id AS usrid, ".
        "       userpersons.fname AS personname, ".
        "       userpersons.flastname AS personlastname, ".
        "       usercompanies.name AS companyname, ".
        "       usercompanies.comercialname AS companycomercialname, ".
        "       CASE ".
	    "           WHEN deposits.rejected IS NULL AND deposits.valid IS NULL THEN 0 ".
	    "           WHEN deposits.rejected IS NULL AND deposits.valid IS NOT NULL THEN 1 ".
	    "           WHEN deposits.rejected IS NOT NULL AND deposits.valid IS NULL THEN 2 ".
	    "       END AS status, ".
	    "       CASE ".
	    "           WHEN deposits.rejected IS NULL AND deposits.valid IS NULL THEN 'Pendiente' ".
	    "           WHEN deposits.rejected IS NULL AND deposits.valid IS NOT NULL THEN 'Aprobado' ".
	    "           WHEN deposits.rejected IS NOT NULL AND deposits.valid IS NULL THEN 'Rechazado' ".
	    "       END AS statusorder ".
        "       FROM deposits, transactions t, accounts, users ".
        "".
        "       LEFT JOIN usercompanies ON usercompanies.usrid = users.id ".
        "       LEFT JOIN userpersons ON userpersons.usrid = users.id ".
        "".
        "       WHERE t.id = deposits.transactionid ".
        "       AND t.validator IS NULL ".
        "       ".($time == "" ? "" : "AND DATE(deposits.createddate) >= ".$time." " ).
        "       AND t.accountid = accounts.id ".
        "       AND accounts.userid = users.id ".
        "       ".$currencyid." ".
        "       ".$kindDeposit." ".
        "       ".$filterDeposit." ".
        "".
        "       UNION ".
        "".
        "       SELECT t.id AS transactionid, ".
        "       (SELECT sum(amount) FROM transactions WHERE transactions.txid = t.id) AS qty,".
        "       withdrawals.createddate AS createddate, ".
        "       withdrawals.amount AS amount, ".
        "       withdrawals.fee AS fee, ".
        "       withdrawals.valid AS valid, ".
        "       withdrawals.rejected AS rejected, ".
        "       (ABS(withdrawals.amount) + ABS(withdrawals.fee)) AS test, ".
        "       t.accountid AS txaccount, ".
        "       t.dsc AS txdsc, ".
        "       t.paypending AS paypending, ".
        "       t.accountorigin AS txaccorigin,".
        "       t.accountdestination AS txaccdestination, ".
        "       accounts.currencyid AS accountcurrencyid, ".
        "       accounts.paymentmethodid AS accountpaymentmethodid, ".
        "       accounts.status AS accountstatus, ".
        "       accounts.rejected AS accountrejected, ".
        "       accounts.approved AS accountapproved, ".
        "       users.usr AS usr, ".
        "       users.id AS usrid, ".
        "       userpersons.fname AS personname, ".
        "       userpersons.flastname AS personlastname, ".
        "       usercompanies.name AS companyname, ".
        "       usercompanies.comercialname AS companycomercialname, ".
        "       CASE ".
	    "           WHEN withdrawals.rejected IS NULL AND withdrawals.valid IS NULL THEN 0 ".
	    "           WHEN withdrawals.rejected IS NULL AND withdrawals.valid IS NOT NULL THEN 1 ".
	    "           WHEN withdrawals.rejected IS NOT NULL AND withdrawals.valid IS NULL THEN 2 ".
	    "       END AS status, ".
	    "       CASE ".
	    "           WHEN withdrawals.rejected IS NULL AND withdrawals.valid IS NULL THEN 'Pendiente' ".
	    //Agregado al final mantengo a delante por orden
	    "           WHEN withdrawals.rejected IS NULL AND withdrawals.valid IS NOT NULL THEN (IF((withdrawals.filetype='9191' OR withdrawals.paymentref='0000'),'Aprobado*', 'Aprobado')) " .      //'Aprobado' ".
	    "           WHEN withdrawals.rejected IS NOT NULL AND withdrawals.valid IS NULL THEN 'Rechazado' ".
	    "       END AS statusorder ".
        "       FROM withdrawals, transactions t, accounts, users ".
        "".
        "       LEFT JOIN usercompanies ON usercompanies.usrid = users.id ".
        "       LEFT JOIN userpersons ON userpersons.usrid = users.id ".
        "".
        "       WHERE t.id = withdrawals.transactionid ".
        "       AND t.validator IS NULL ".
        "       ".($time == "" ? "" : "AND DATE(withdrawals.createddate) >= ".$time." " ).
        "       AND t.accountid = accounts.id ".
        "       AND accounts.userid = users.id ".
        "       ".$currencyid." ".
        "       ".$kindWithdra." ".
        "       ".$filterWithdrawals." ".
        "".
        "       UNION ".
        "".
        "       SELECT t.id AS transactionid, ".
        "       (SELECT sum(amount) FROM transactions WHERE transactions.txid = t.id) AS qty,".
        "       t.datecreated AS createddate, ".
        "       t.amount AS amount, ".
        "       t.amountghost AS fee, ".
        "       t.txid AS valid, ".
        "       t.txid AS rejected, ".
        "       (t.amount) AS test, ".
        "       t.accountid AS txaccount, ".
        "       t.dsc AS txdsc, ".
        "       t.paypending AS paypending, ".
        "       t.accountorigin AS txaccorigin,".
        "       t.accountdestination AS txaccdestination, ".
        "       accounts.currencyid AS accountcurrencyid, ".
        "       accounts.paymentmethodid AS accountpaymentmethodid, ".
        "       accounts.status AS accountstatus, ".
        "       accounts.rejected AS accountrejected, ".
        "       accounts.approved AS accountapproved, ".
        "       users.usr AS usr, ".
        "       users.id AS usrid, ".
        "       userpersons.fname AS personname, ".
        "       userpersons.flastname AS personlastname, ".
        "       usercompanies.name AS companyname, ".
        "       usercompanies.comercialname AS companycomercialname, ".
        "       CASE ".
	    "           WHEN t.amountghost = 0 AND t.paypending = 0 THEN 1 ".
	    "           ELSE 0 ".
	    "       END AS status, ".
	    "       CASE ".
	    "           WHEN t.amountghost = 0 AND t.paypending = 0 THEN 'Aprobado' ".
	    "           ELSE 'Pendiente' ".
	    "       END AS statusorder ".
        "       FROM transactions t, accounts, users ".
        "".
        "       LEFT JOIN usercompanies ON usercompanies.usrid = users.id ".
        "       LEFT JOIN userpersons ON userpersons.usrid = users.id ".
        "".
        "       WHERE (t.dsc LIKE 'Transferencia a%' OR t.dsc LIKE 'Transferencia de%')".
        "       AND t.validator IS NULL ".
        "       AND t.amount != 0".
        "       ".($time == "" ? "" : "AND DATE(t.datecreated) >= ".$time." " ).
        "       AND t.accountid = accounts.id ".
        "       AND accounts.userid = users.id ".
        "       ".$currencyid." ".
        "       ".$kindXtr." ".
        "       ".$filterTx." ".
        "".
        "       UNION ".
        "".
        "       SELECT t.id AS transactionid, ".
        "       (SELECT sum(amount) FROM transactions WHERE transactions.txid = t.id) AS qty,".
        "       t.datecreated AS createddate, ".
        "       t.amount AS amount, ".
        "       t.amountghost AS fee, ".
        "       t.txid AS valid, ".
        "       t.txid AS rejected, ".
        "       (t.amount) AS test, ".
        "       t.accountid AS txaccount, ".
        "       t.dsc AS txdsc, ".
        "       t.paypending AS paypending, ".
        "       t.accountorigin AS txaccorigin,".
        "       t.accountdestination AS txaccdestination, ".
        "       accounts.currencyid AS accountcurrencyid, ".
        "       accounts.paymentmethodid AS accountpaymentmethodid, ".
        "       accounts.status AS accountstatus, ".
        "       accounts.rejected AS accountrejected, ".
        "       accounts.approved AS accountapproved, ".
        "       users.usr AS usr, ".
        "       users.id AS usrid, ".
        "       userpersons.fname AS personname, ".
        "       userpersons.flastname AS personlastname, ".
        "       usercompanies.name AS companyname, ".
        "       usercompanies.comercialname AS companycomercialname, ".
        "       CASE ".
        "           WHEN (SELECT tran.dsc AS dsc FROM transactions tran WHERE tran.dsc LIKE CONCAT('%', t.txid, '%') AND tran.id = tran.txid LIMIT 1) IS NOT NULL THEN 2".
	    "           WHEN t.amountghost = 0 AND t.paypending = 0 THEN 1 ".
	    "           ELSE 0 ".
	    "       END AS status, ".
	    "       CASE ".
	    "           WHEN t.amountghost = 0 AND t.paypending = 0 THEN 'Aprobado' ".
	    "           ELSE 'Pendiente' ".
	    "       END AS statusorder ".
        "       FROM transactions t, accounts, users ".
        "".
        "       LEFT JOIN usercompanies ON usercompanies.usrid = users.id ".
        "       LEFT JOIN userpersons ON userpersons.usrid = users.id ".
        "".
        "       WHERE (t.dsc LIKE 'Transferencia PAY%' OR (t.dsc LIKE '%Solicitud de Cobro PAY%' AND t.paypending = 0))".
        "       AND t.validator IS NULL ".
        "       AND t.amount != 0".
        "       ".($time == "" ? "" : "AND DATE(t.datecreated) >= ".$time." " ).
        "       AND t.accountid = accounts.id ".
        "       AND accounts.userid = users.id ".
        "       ".$currencyid." ".
        "       ".$kindXtr." ".
        "       ".$filterTx." ".
        "       ".$order.
        "       ".$limit;
    }
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
    
    //Ordenamos los resultados(bof)
    while($row = $rs->fetch_assoc()){
        $record = new stdClass();
        $record->id = (int)$row["transactionid"];
        
        $record->date = new stdClass();
        $record->date->cannonical = date("Y-m-d H:i", strtotime($row["createddate"]));
        $record->date->formatted = date("d/m/Y", strtotime($row["createddate"]))." - ".date("h:i a", strtotime($row["createddate"]));
        
        //Consultamos para saber si es un retiro o un deposito
        $sql = "SELECT transactions.id AS transactionid, ".
        "       deposits.transactionid AS depositid, ".
        "       withdrawals.transactionid AS withdrawalid ".
        "       FROM transactions ".
        "       LEFT JOIN deposits ON transactions.id = deposits.transactionid".
        "       LEFT JOIN withdrawals ON transactions.id = withdrawals.transactionid".
        "       WHERE transactions.id = ".$record->id;
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
    
        $validtype = $res->fetch_assoc();
        $record->type = new stdClass();
    
        // 1 = DEPOSITO; 2 = RETIRO WN
        //Si el campo depositid es distinto a null significa que es un deposito
        if($validtype["depositid"] != null){
            $record->type->id = 1;
            $record->type->dsc = "Depósito";
        }else if($validtype["withdrawalid"] != null){
            $record->type->id = 2;
            $record->type->dsc = "Retiro";
        }else if (strpos($row['txdsc'], 'PAY') !== false) {
            $record->type->id = 4;
            $record->type->dsc = "Transferencia PAY";
        } else {
            $record->type->id = 3;
            $record->type->dsc = "Transferencia";
        }
    
        //Validamos el status
        if($row["valid"] != null && $row["rejected"] == null){
            $record->status = 1;
        }else if($row["valid"] == null && $row["rejected"] != null){
            $record->status = 2;
        }else{
            $record->status = 0;
        }
        
        if($record->type->id == 3 || $record->type->id == 4){
            $record->status = (int)$row["status"];
        }
        $record->statusdsc = $row["statusorder"];
        //Consultamos la cuenta del movimiento
        $record->account = new stdClass();
        
        $record->account->id = (int)$row["txaccount"];
        
        //Ordenamos los datos del usuario
        $record->account->user = new stdClass();
        $record->account->user->id = (int)$row["usrid"];
        $record->account->user->usr = $row["usr"];
    
        if($row["personname"] == null){
            $record->account->user->fullname = $row["companyname"].( $row["companycomercialname"] == "" ? "" : " / ".$row["companycomercialname"] );
        }else{
            $record->account->user->fullname = $row["personname"].( $row["personlastname"] == "" ? "" : " ".$row["personlastname"] );
        }
        
        $sql = "SELECT dsc ".
        "       FROM paymentmethods ".
        "       WHERE id = ".$row["accountpaymentmethodid"];
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
        
        $paymentmethod = $res->fetch_assoc();
        
        $record->account->paymentmethod = new stdClass();
        $record->account->paymentmethod->id = (int)$row["accountpaymentmethodid"];
        $record->account->paymentmethod->dsc = $paymentmethod["dsc"];
        
        //Consultamos la wallet(si existejjeje)
        $sql = "SELECT * FROM wallets".
        "       WHERE accountid = ".$record->account->id;
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
        
        $wallet = $res->fetch_assoc();
    
        $record->account->wallet = $wallet["wallet"];
    
        $sql = "SELECT * FROM currencies WHERE id = ".$row["accountcurrencyid"];
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
        
        $currencydata = $res->fetch_assoc();
        
        $record->account->currency = new stdClass();
        $record->account->currency->id = (int)$currencydata["id"];
        $record->account->currency->name = $currencydata["name"];
        $record->account->currency->symbol = $currencydata["symbol"];
        $record->account->currency->interface = $currencydata['interface'];
        $record->account->currency->decimals = $currencydata['decimals'];
        
        $record->account->status = new stdClass();
        $record->account->status->id = (int)$row["accountstatus"];
        $record->account->status->dsc = $record->account->status->id == 1 ? "Activa" : "Inactiva";
    
        if($row["accountrejected"] != null){
            $record->account->valid = -1;
        }else if($row["accountapproved"] != null && $row["accountrejected"] == null){
            $record->account->valid = 1;
        }else{
            $record->account->valid = 0;
        }
        // 1 = DEPOSITO; 2 = RETIRO WN
        //Datos de la cuenta de AFX DEPOSITOS
        //Si no existe acc destination es por que es un retiro
        if($record->type->id == 2){
            $sql = "SELECT * FROM withdrawals WHERE transactionid = ".$record->id;
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));
                
            $accountdata = $res->fetch_assoc();
            
            if($accountdata["paymentwallet"] != null){
                $record->userpaymentmethod = new stdClass();
                $record->userpaymentmethod->wallet = $accountdata["paymentwallet"];
            }else{
                $record->userpaymentmethod = new stdClass();
            
                $sql = "SELECT ".
                "       users.id AS userid, ".
                "       users.usr AS usr, ".
                "       accounts.id AS accountid, ".
                "       accounts.details1 AS accountdetails1, ".
                "       accounts.details2 AS accountdetails2, ".
                "       accounts.details3 AS accountdetails3, ".
                "       accounts.details4 AS accountdetails4, ".
                "       accounts.currencyid AS accountcurrencyid, ".
                "       paymentmethods.id AS paymentmethodid, ".
                "       paymentmethods.dsc AS paymetmenthoddsc, ".
                "       paymentmethods.details1 AS paymentmethoddetails1, ".
                "       paymentmethods.details2 AS paymentmethoddetails2, ".
                "       paymentmethods.details3 AS paymentmethoddetails3, ".
                "       paymentmethods.details4 AS paymentmethoddetails4 ".
                "       FROM accounts, users, paymentmethods ".
                "       WHERE accounts.id = ".$accountdata["paymentaccountid"].
                "       AND accounts.userid = users.id ".
                "       AND accounts.paymentmethodid = paymentmethods.id";
                if (!$res=$db->query($sql))
                    badEnd("500", array("msg2"=>determinateDBError($db)));
                
                $paymentaccountdata = $res->fetch_assoc();
    
                $record->userpaymentmethod->id = (int)$paymentaccountdata["accountid"];
                
                //Ordenamos los datos del usuario
                $record->userpaymentmethod->user = new stdClass();
                $record->userpaymentmethod->user->id = (int)$paymentaccountdata["userid"];
                $record->userpaymentmethod->user->fullname = $paymentaccountdata["usr"];
                
                $record->userpaymentmethod->paymentmethod = new stdClass();
                $record->userpaymentmethod->paymentmethod->id = (int)$paymentaccountdata["paymentmethodid"];
                $record->userpaymentmethod->paymentmethod->dsc = $paymentaccountdata["paymetmenthoddsc"];
                $record->userpaymentmethod->paymentmethod->details1 = $paymentaccountdata["paymentmethoddetails1"];
                $record->userpaymentmethod->paymentmethod->details2 = $paymentaccountdata["paymentmethoddetails2"];
                $record->userpaymentmethod->paymentmethod->details3 = $paymentaccountdata["paymentmethoddetails3"];
                $record->userpaymentmethod->paymentmethod->details4 = $paymentaccountdata["paymentmethoddetails4"];
                
                //Consultamos la wallet(si existejjeje)
                $sql = "SELECT * FROM wallets".
                "       WHERE accountid = ".$record->userpaymentmethod->id;
                if (!$res=$db->query($sql))
                    badEnd("500", array("msg"=>determinateDBError($db)));
                    
                $wallet = $res->fetch_assoc();
                
                $record->userpaymentmethod->wallet = $wallet["wallet"];
                
                $sql = "SELECT * FROM currencies WHERE id = ".$paymentaccountdata["accountcurrencyid"];
                if (!$res=$db->query($sql))
                     badEnd("500", array("msg"=>determinateDBError($db)));
                    
                $currencydata = $res->fetch_assoc();
                
                $record->userpaymentmethod->currency = new stdClass();
                $record->userpaymentmethod->currency->id = (int)$currencydata["id"];
                $record->userpaymentmethod->currency->name = $currencydata["name"];
                $record->userpaymentmethod->currency->symbol = $currencydata["symbol"];
                $record->userpaymentmethod->currency->interface = $currencydata["interface"];
                
                $record->userpaymentmethod->details1 = $paymentaccountdata["details1"];
                $record->userpaymentmethod->details2 = $paymentaccountdata["details2"];
                $record->userpaymentmethod->details3 = $paymentaccountdata["details3"];
                $record->userpaymentmethod->details4 = $paymentaccountdata["details4"];
            }
        }else if($row["txaccdestination"] == null){
            $record->userpaymentmethod = null;
        } else if ($row['paypending'] == 0) {
            $record->userpaymentmethod = new stdClass();
            
            $sql = "SELECT ".
            "       users.id AS userid, ".
            "       users.usr AS usr, ".
            "       accounts.id AS accountid, ".
            "       accounts.details1 AS accountdetails1, ".
            "       accounts.details2 AS accountdetails2, ".
            "       accounts.details3 AS accountdetails3, ".
            "       accounts.details4 AS accountdetails4, ".
            "       accounts.currencyid AS accountcurrencyid, ".
            "       paymentmethods.id AS paymentmethodid, ".
            "       paymentmethods.dsc AS paymetmenthoddsc, ".
            "       paymentmethods.details1 AS paymentmethoddetails1, ".
            "       paymentmethods.details2 AS paymentmethoddetails2, ".
            "       paymentmethods.details3 AS paymentmethoddetails3, ".
            "       paymentmethods.details4 AS paymentmethoddetails4 ".
            "       FROM accounts, users, paymentmethods ".
            "       WHERE accounts.id = ".($row["txaccdestination"] == $record->account->id && $record->type->id != 1 ? $row["txaccorigin"] : $row["txaccdestination"]).//<-- Devuelve "" en algunos casos y hace que el query explote (depositos especificamente)
            "       AND accounts.userid = users.id ".
            "       AND accounts.paymentmethodid = paymentmethods.id";
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));
                
            $accountdata = $res->fetch_assoc();
    
            $record->userpaymentmethod->id = (int)$accountdata["accountid"];
                
            //Ordenamos los datos del usuario
            $record->userpaymentmethod->user = new stdClass();
            $record->userpaymentmethod->user->id = (int)$accountdata["userid"];
            $record->userpaymentmethod->user->fullname = $accountdata["usr"];
            
            $record->userpaymentmethod->paymentmethod = new stdClass();
            $record->userpaymentmethod->paymentmethod->id = (int)$accountdata["paymentmethodid"];
            $record->userpaymentmethod->paymentmethod->dsc = $accountdata["paymetmenthoddsc"];
            $record->userpaymentmethod->paymentmethod->details1 = $accountdata["paymentmethoddetails1"];
            $record->userpaymentmethod->paymentmethod->details2 = $accountdata["paymentmethoddetails2"];
            $record->userpaymentmethod->paymentmethod->details3 = $accountdata["paymentmethoddetails3"];
            $record->userpaymentmethod->paymentmethod->details4 = $accountdata["paymentmethoddetails4"];
            
            //Consultamos la wallet(si existejjeje)
            $sql = "SELECT * FROM wallets".
            "       WHERE accountid = ".$record->userpaymentmethod->id;
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));
                
            $wallet = $res->fetch_assoc();
                
            $record->userpaymentmethod->wallet = $wallet["wallet"];
            
            $sql = "SELECT * FROM currencies WHERE id = ".$accountdata["accountcurrencyid"];
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));
                
            $currencydata = $res->fetch_assoc();
                
            $record->userpaymentmethod->currency = new stdClass();
            $record->userpaymentmethod->currency->id = (int)$currencydata["id"];
            $record->userpaymentmethod->currency->name = $currencydata["name"];
            $record->userpaymentmethod->currency->symbol = $currencydata["symbol"];
            $record->userpaymentmethod->currency->interface = $currencydata["interface"];
            
            $record->userpaymentmethod->details1 = $accountdata["details1"];
            $record->userpaymentmethod->details2 = $accountdata["details2"];
            $record->userpaymentmethod->details3 = $accountdata["details3"];
            $record->userpaymentmethod->details4 = $accountdata["details4"];
        }
        
        //Si es una transferencia necesitamos consultar el movimiento con su amountghost por si fue rechazada
        if($record->type->id == 3 || $record->type->id == 4){
            $sql = "SELECT * FROM transactions".
            "       WHERE id != ".$record->id.
            "       AND txid = ".$record->id.
            "       AND dsc like '%comision%'";
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));
            
            $txdataFee = $res->fetch_assoc();
            $record->amountquepasa = $txdataFee;
            //Monto del movimiento
            $record->amount = new stdClass();
            $record->feequepasa = $row;
            $record->amount->number = (abs($row["amount"]) + abs($row["fee"])) + (abs($txdataFee["amount"]) + abs($txdataFee["amountghost"]));
            $record->amount->formatted = numberFormatt($record->amount->number,$currencydata["decimals"]);
        }else{
            //Monto del movimiento
            $record->amount = new stdClass();
            $record->amount->number = (float)$row["amount"] ;
            $record->amount->formatted = numberFormatt($record->amount->number,$currencydata["decimals"]);
        }
        
    
        //Si es una transferencia necesitamos consultar el movimiento con su comision
        if($record->type->id == 3 || $record->type->id == 4){
            //Consultamos el movimiento de comision de la cuenta
            $sql = "SELECT * FROM transactions".
            "       WHERE id != ".$record->id.
            "       AND txid = ".$record->id.
            "       AND dsc like '%comision%'";
            if (!$res=$db->query($sql)){
                badEnd("500", array("msg"=>determinateDBError($db)));
            }
                
            $row = $res->fetch_assoc();
            $record->prueba = $row;
            //Comision del movimiento
            $record->fee = new stdClass();
            $record->fee->number = abs((float)$row["amount"] + $row["amountghost"]);
            $record->fee->formatted = numberFormatt($record->fee->number,$currencydata["decimals"]);
        }else{
            //Comision del movimiento
            $record->fee = new stdClass();
            $record->fee->number = (float)$row["fee"];
            $record->fee->formatted = numberFormatt($record->fee->number,$currencydata["decimals"]);
        }
        
        
        $records [] = $record;
    }
    
    $out->records = $records;
    
    header("HTTP/1.1 200");
    echo (json_encode($out));
    die();
    
    /*  Funcion para deteriminar el orden de los resultados
    *   default = mas reciente
    *   1 = Fecha de creacion ASCENDENTE
    *   2 = Nombre del usuario ASCENDENTE
    *   3 = Correo del usuario ASCENDENTE
    *   4 = Moneda ASCENDENTE
    *   5 = Estado del movimiento ASCENDENTE
    *   6 = Monto del movimiento ASCENDENTE
    *   7 = Tipo de movimiento ASCENDENTE
    *   -1 = Fecha de creacion DESCENDENTE
    *   -2 = Nombre del usuario DESCENDENTE
    *   -3 = Correo del usuario DESCENDENTE
    *   -4 = Moneda DESCENDENTE
    *   -5 = Estado del movimiento DESCENDENTE
    *   -6 = Monto del movimiento DESCENDENTE
    *   -7 = Tipo de movimiento DESCENDENTE
    */
    function determinateOrder($order){
        $str = "";
        
        switch($order){
            case 1:
                $str = " ORDER BY createddate ASC ";
                break;
            case 2:
                $str = " ORDER BY personname ASC, companyname ASC ";
                break;
            case 3:
                $str = " ORDER BY usr ASC ";
                break;
            case 4:
                $str = " ORDER BY accountcurrencyid ASC ";
                break;
            case 5:
                $str = " ORDER BY statusorder ASC ";
                break;
            case 6: 
                $str = " ORDER BY amount ASC ";
                break;
            case 7:
                $str = " ORDER BY txdsc ASC ";
                break;
            case -1:
                $str = " ORDER BY createddate DESC ";
                break;
            case -2:
                $str = " ORDER BY personname DESC, companyname DESC ";
                break;
            case -3:
                $str = " ORDER BY usr DESC ";
                break;
            case -4:
                $str = " ORDER BY accountcurrencyid DESC ";
                break;
            case -5:
                $str = " ORDER BY statusorder DESC ";
                break;
            case -6: 
                $str = " ORDER BY amount DESC ";
                break;
            case -7:
                $str = " ORDER BY txdsc DESC ";
                break;
            default:
                $str = " ORDER BY createddate DESC ";
                break;
        }
        
        return $str;
    }
    
    /*  Funcion para determinar el tiempo de las transacciones
    *   Solo recibe el parametro $time que puede ser:
    *   0 = Todos
    *   1 = Hoy
    *   2 = Ultimos 7 días
    *   3 = Ultimos 30 días
    *   4 = Ultimos 3 meses
    *   5 = Ultimos 6 meses
    */
    function determinateTime($time){
        $str = "";
        
        switch($time){
            case 0:
                $str = "";
                break;
            case 1:
                $str = "DATE_SUB(NOW(), INTERVAL 1 DAY)";
                break;
            case 2:
                $str = "DATE_SUB(NOW(), INTERVAL 7 DAY)";
                break;
            case 3:
                $str = "DATE_SUB(NOW(), INTERVAL 30 DAY)";
                break;
            case 4:
                $str = "DATE_SUB(NOW(), INTERVAL 90 DAY)";
                break;
            case 5:
                $str = "DATE_SUB(NOW(), INTERVAL 6 MONTH)";
                break;
            default: 
                badEnd("500", array("msg"=>"El parametro fecha solo puede ser 0, 1, 2, 3, 4 o 5"));
                break;
        }
        
        return $str;
    }
?>
