<?php
// app/api/balance/accounts

    header("Content-Type:application/json");
    include("../dbconn.php");
    include("../utils.php");
    
    // parametros obligatorios
    $parmsob = array("sessionid", "currencyid");
    if (!parametrosValidos($_GET, $parmsob))
        badEnd("400", array("msg"=>"Parametros obligatorios " . implode(", ", $parmsob)));
        
    //Declaramos e inicializamos nuestras variables
    $out = new stdClass();
    $out->total = new stdClass();
    $sessionid = $_GET["sessionid"];
    $offset = "";
    $limit = "";
    $filter = "";
    $dateAndHour = false;
    $currencyid = $_GET["currencyid"];
    
    //Validamos que la session del usuario sea valida para continuar
    $userid = isSessionValid($db, $sessionid);
    
    //En caso de que el offset sea enviado, se construye el query
    if(isset($_GET["filter"]) && $_GET["filter"] != ""){
        
        //Si la fecha tiene / lo partimos en mil
        // Partimos la hora de la fecha
        $datehour = explode(' - ',$_GET["filter"]);
            
        //SI este pana trae la hora, guardamos que la trajo
        if(sizeof($datehour) > 1){
            $out->probando [] = $datehour;
            $dateAndHour = true;
        }
            
        // Si la fecha esta con dd/mm/yyyy la acomodamos a yyyy-mm-dd
        if(strpos($datehour[0], '/') !== false){
            $dateelements = explode("/",$datehour[0]);
            
            foreach($dateelements as $i => $element){
                if($i == 0){
                    $dateConverted .= $element;
                }else{
                    $dateConverted .= "-".$element;
                }
            }
        
            $dateelhour = strftime("%Y-%m-%d".( $dateAndHour ? ' %H:%M %p' : ''), strtotime($dateConverted." ".$datehour[1]));
        }else{
            $dateelhour = strftime("%Y-%m-%d".( $dateAndHour ? ' %H:%M %p' : ''), strtotime($_GET["filter"]));
        }
        
        $out->testeando [] = $dateelhour;
            
        $filter = " AND (curr.name LIKE '".$_GET["filter"]."%' ".
        "           OR curr.symbol LIKE '".$_GET["filter"]."%' ".
        "           OR (SELECT DATE(transactions.datecreated) FROM transactions ".
        "               WHERE acc.id = transactions.accountid ".
        "               AND transactions.datecreated = (SELECT MAX(transactions.datecreated) FROM transactions WHERE acc.id = transactions.accountid) ".
        "               ORDER BY transactions.datecreated DESC, transactions.id DESC ".
        "               LIMIT 1 ".
        "           ) = DATE('".$dateelhour."') ".
        ( $dateAndHour ? " OR (SELECT DATE_FORMAT(transactions.datecreated,'%Y-%m-%d %h:%i %p') FROM transactions ".
        "               WHERE acc.id = transactions.accountid ".
        "               AND transactions.datecreated = (SELECT MAX(transactions.datecreated) FROM transactions WHERE acc.id = transactions.accountid) ".
        "               ORDER BY transactions.datecreated DESC, transactions.id DESC ".
        "               LIMIT 1 ".
        "           ) = '".$dateelhour."' " : '').
        "           ".(!is_numeric($_GET['filter']) ? '' : 
        "          OR IF(acc.id IS NULL, 0, ( ".
        "           (SELECT SUM(amount) as qty FROM transactions WHERE accountid = acc.id) - ".
        "               ( ".
        "                   IF( ".
        "                   (SELECT SUM(CASE WHEN orders.side = 1 THEN orders.quantity ELSE orders.total END) AS amount ".
        "                   FROM orders ".
        "                   WHERE orders.accountid = acc.id ".
        "                   AND orders.canceled IS NULL ".
        "                   AND orders.type != 1) IS NULL, 0, ".
        "                   (SELECT SUM(CASE WHEN orders.side = 1 THEN orders.quantity ELSE orders.total END) AS amount ".
        "                   FROM orders ".
        "                   WHERE orders.accountid = acc.id ".
        "                   AND orders.canceled IS NULL ".
        "                   AND orders.type != 1) ".
        "                   ) + IF( ".
        "                   (SELECT SUM(transactions.amount) AS amount ".
        "                   FROM transactions, orders ".
        "                   WHERE orders.id = transactions.orderid ".
        "                   AND transactions.accountid = acc.id ".
        "                   AND orders.accountid = acc.id  ".
        "                   AND orders.canceled IS NULL ".
        "                   AND transactions.amount < 0 ".
        "                   AND orders.type != 1) IS NULL, 0, ".
        "                   (SELECT SUM(transactions.amount) AS amount ".
        "                   FROM transactions, orders ".
        "                   WHERE orders.id = transactions.orderid ".
        "                   AND transactions.accountid = acc.id ".
        "                   AND orders.accountid = acc.id ".
        "                   AND orders.canceled IS NULL ".
        "                   AND transactions.amount < 0 ".
        "                   AND orders.type != 1) ".
        "                   ) ".
        "               ) ".
        "           )) = ".$_GET["filter"] ).
        "            )";
    }
    
    //En caso de que el offset sea enviado, se construye el query
    if(isset($_GET["offset"]) && $_GET["offset"] != ""){
        $offset = $_GET["offset"];
    }else{
        $offset = "0";
    }
    
    //En caso de que el numofrec sea enviado, se construye el query
    if(isset($_GET["numofrec"]) && $_GET["numofrec"] != ""){
        $numofrec = $_GET["numofrec"];
    }
    
    if($offset != 0 && $numofrec == ""){
        $limit = " LIMIT ".$offset.", 18446744073709551615";
    }else if($offset != 0 && $numofrec != ""){
        $limit = " LIMIT ".$offset.", ".$numofrec;
    }else if($offset == 0 && $numofrec != ""){
        $limit = " LIMIT ".$numofrec;
    }else{
        $limit = " LIMIT ".$offset.", 18446744073709551615";
    }
    
    $balancepercents = calculateTotal($db, $userid, $currencyid);
    
    $out->total = $balancepercents->total;
    
    //Contamos la cantidad de monedas activas
    $sql = "SELECT COUNT(curr.id) AS qty ".
    "       FROM currencies curr ".
    "       LEFT JOIN accounts acc ON ".
    "       acc.currencyid = curr.id ".
    "       AND acc.userid = ".$userid.
    "       AND acc.paymentmethodid = -1 ".
    "       WHERE (curr.status = 1 ".
    "       OR curr.id = (SELECT acc.currencyid ".
    "           FROM accounts acc ".
    "           WHERE acc.userid = ".$userid.
    "           AND acc.currencyid = curr.id ".
    "           AND acc.paymentmethodid = -1)) AND curr.id > 0".
    "       ".$filter;
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>$db->error.$sql));
    
    $row = $rs->fetch_assoc();
    $out->numofrecords = (int)$row['qty'];
    
    //Consultamos las monedas
    $sql = "SELECT curr.id AS currencyid,".
    "       curr.name AS currencyname, ".
    "       curr.symbol AS currencysymbol, ".
    "       curr.decimals AS currencydecimals, ".
    "       curr.status AS currencystatus, ".
    "       curr.interface AS currencyinterface, ".
    "       acc.id AS accountid, ".
    "       acc.paymentmethodid AS paymentmethodid, ".
    "       acc.status AS accountstatus, ".
    "       IF(acc.id IS NULL, 0, ( ".
    "           (SELECT SUM(amount) as qty FROM transactions WHERE accountid = acc.id) - ".
    "               ( ".
    "                   IF( ".
    "                   (SELECT SUM(CASE WHEN orders.side = 1 THEN orders.quantity ELSE orders.total END) AS amount ".
    "                   FROM orders ".
    "                   WHERE orders.accountid = acc.id ".
    "                   AND orders.canceled IS NULL ".
    "                   AND orders.type != 1) IS NULL, 0, ".
    "                   (SELECT SUM(CASE WHEN orders.side = 1 THEN orders.quantity ELSE orders.total END) AS amount ".
    "                   FROM orders ".
    "                   WHERE orders.accountid = acc.id ".
    "                   AND orders.canceled IS NULL ".
    "                   AND orders.type != 1) ".
    "                   ) + IF( ".
    "                   (SELECT SUM(transactions.amount) AS amount ".
    "                   FROM transactions, orders ".
	"                   WHERE orders.id = transactions.orderid ".
    "                   AND transactions.accountid = acc.id ".
    "                   AND orders.accountid = acc.id  ".
    "                   AND orders.canceled IS NULL ".
    "                   AND transactions.amount < 0 ".
    "                   AND orders.type != 1) IS NULL, 0, ".
    "                   (SELECT SUM(transactions.amount) AS amount ".
    "                   FROM transactions, orders ".
	"                   WHERE orders.id = transactions.orderid ".
    "                   AND transactions.accountid = acc.id ".
    "                   AND orders.accountid = acc.id ".
    "                   AND orders.canceled IS NULL ".
    "                   AND transactions.amount < 0 ".
    "                   AND orders.type != 1) ".
    "                   ) ".
    "               ) ".
    "       )) AS avaliable, ".
    "       (SELECT transactions.datecreated FROM transactions ".
    "           WHERE acc.id = transactions.accountid ".
    "           AND transactions.datecreated = (SELECT MAX(transactions.datecreated) FROM transactions WHERE acc.id = transactions.accountid) ".
    "           ORDER BY transactions.datecreated DESC, transactions.id DESC ".
    "           LIMIT 1 ".
    "       ) AS lasttx ".
    "       FROM currencies curr ".
    "       LEFT JOIN accounts acc ON ".
    "           acc.currencyid = curr.id ".
    "           AND acc.userid = ".$userid.
    "           AND acc.paymentmethodid = -1 ".
    "       WHERE (curr.status = 1 ".
    "       OR curr.id = (SELECT accs.currencyid ".
    "           FROM accounts accs ".
    "           WHERE accs.userid = ".$userid.
    "           AND accs.currencyid = curr.id ".
    "           AND accs.paymentmethodid = -1)) AND curr.id > 0 ".
    "       ".$filter.
    "       ".$limit;
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>$db->error.$sql));
        
    while($row = $rs->fetch_assoc()){
        $record = new stdClass();
        
        $record->currency = new stdClass();
        $record->currency->id = (int)$row['currencyid'];
        $record->currency->dsc = $row['currencyname'];
        $record->currency->symbol = $row['currencysymbol'];
        $record->currency->decimals = (int)$row['currencydecimals'];
        $record->currency->interface = (int)$row['currencyinterface'];
        $record->currency->status = (int)$row['currencystatus'];
        
        $records [] = $record;
        
        if($row['accountid'] != NULL){
            $record->id = (int)$row["accountid"];
            
            //Metodo de pago
            $record->paymentmethod = new stdClass();
            $record->paymentmethod->id = (int)$row["paymentmethodid"];
            
            //Consultamos el nombre del metodo de pago
            $sql = "SELECT dsc ".
            "       FROM paymentmethods ".
            "       WHERE id = ".$row["paymentmethodid"];
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>$db->error.$sql));
            
            $payment = $res->fetch_assoc();
            //Guardamos el nombre del metodo de pago
            $record->paymentmethod->dsc = $payment["dsc"];
            
            //Balance a partir de las transacciones
            $record->balance = new stdClass();
            $record->balance->number = (float)$row['avaliable'];
            $record->balance->formatted = numberFormatt($record->balance->number,$row["currencydecimals"]);
            
            //Porcentaje que representa
            $record->pct = new stdClass();
            $record->pct->number = $record->balance->number < 1 || $out->total->number < 1 ? 0 : $record->balance->number / ($out->total->number / 100);
            $record->pct->formatted = numberFormatt($record->pct->number,2)."%";
                
            $record->total = new stdClass();
            $record->total->number = 0;
            $record->total->formatted = "0";
            
            $record->last = $row['lasttx'] == null ? '' : date("d/m/Y", strtotime($row["lasttx"]))." - ".date("h:i a", strtotime($row["lasttx"]));
            
            //Recorremos el arreglo para verificar que encontro pares de la cuenta y pudo realizar la conversion
            foreach($balancepercents->records as $balanceptcs){
                if($record->id == $balanceptcs->id){
                    //Porcentaje que representa
                    $record->pct = new stdClass();
                    $record->pct->number = $balanceptcs->balance->number <= 0 ? 0 : $balanceptcs->balance->number / ($balancepercents->total->number / 100);
                    $record->pct->formatted = numberFormatt($record->pct->number,2)."%";
                    
                    $record->total = new stdClass();
                    $record->total->number = $balanceptcs->balance->number <= 0 ? 0 : $balanceptcs->balance->number;
                    $record->total->formatted = numberFormatt($record->total->number, $balancepercents->decimals);
                }
            }
            
            //Datos de la cuenta
            $record->status = (int)$row["accountstatus"];
        }else{
            $record->id = 0;
            $record->paymentmethod = NULL;
            
            $record->balance = new stdClass();
            $record->balance->number = 0;
            $record->balance->formatted = numberFormatt($record->balance->number,$row["currencydecimals"]);
        }
    }
    
    $out->record = $records;
    
    header("HTTP/1.1 200");
    echo (json_encode($out));
    die();
    
    /* Funcion para obtener los totales de las cuentas en UNA MONEDA*/
    function calculateTotal($db, $userid, $totportfolio){
        $param = new stdClass();
        $param->total = new stdClass();
        $param->total->number = 0;
        $currencyid = '';
        $decimals = 2;
        
        //Si recibimos la moneda distinta a 0 la usamos
        if($totportfolio == 0){
            //Consultamos las monedas que existen
            $sql = "SELECT currencies.id AS currencyid, ".
            "       currencies.symbol AS currenciessymbol, ".
            "       currencies.decimals AS decimals ".
            "       FROM currencies ".
            "       WHERE EXISTS( ".
            "           SELECT 1 FROM pairs ".
            "           WHERE pairs.currencyid = currencies.id ".
            "           OR pairs.refcurrencyid = currencies.id) ".
            "       ORDER BY currencies.id ASC ".
            "       LIMIT 1";
            if (!$rs=$db->query($sql))
                badEnd("500", array("msg"=>$db->error.$sql));
        
            //Recorremos el valor
            while($row = $rs->fetch_assoc()){
                $record = new stdClass();
                
                //Guardamos el id de la moneda
                $currencyid = (int)$row['currencyid'];
                $decimals = (int)$row['decimals'];
                
                //Retornaremos los datos de la moneda inicial
                $record->id = (int)$row['currencyid'];
                $param->total->id = (int)$row['currencyid'];
                $record->dsc = $row['currenciessymbol'];
                $param->total->dsc = $row['currenciessymbol'];
                $param->decimals = $row['decimals'];
                $param->currencies [] = $record;
            }
        }else{
            //Consultamos las monedas que existen
            $sql = "SELECT currencies.id AS currencyid, ".
            "       currencies.symbol AS currenciessymbol, ".
            "       currencies.decimals AS decimals ".
            "       FROM currencies ".
            "       WHERE EXISTS( ".
            "           SELECT 1 FROM pairs ".
            "           WHERE pairs.currencyid = currencies.id ".
            "           OR pairs.refcurrencyid = currencies.id) ".
            "       AND currencies.id = ".$totportfolio.
            "       ORDER BY currencies.id ASC ".
            "       LIMIT 1";
            if (!$rs=$db->query($sql))
                badEnd("500", array("msg"=>$db->error.$sql));
        
            //Recorremos el valor
            while($row = $rs->fetch_assoc()){
                $record = new stdClass();
                
                //Guardamos el id de la moneda
                $currencyid = (int)$row['currencyid'];
                $decimals = (int)$row['decimals'];
                
                //Retornaremos los datos de la moneda inicial
                $record->id = (int)$row['currencyid'];
                $param->total->id = (int)$row['currencyid'];
                $record->dsc = $row['currenciessymbol'];
                $param->total->dsc = $row['currenciessymbol'];
                $param->decimals = $row['decimals'];
                $param->currencies [] = $record;
            }
        }
        
        //Consultamos las cuentas del usuario
        $sql = "SELECT * FROM accounts ".
        "       WHERE userid = ".$userid.
        "       AND accounts.paymentmethodid = -1";
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>$db->error.$sql));
            
        while($row = $rs->fetch_assoc()){
            $record = new stdClass();
            $record->id = (int)$row['id'];
             
            //SI la cuenta es igual a la inicial se deja todo igual
            if($row['currencyid'] == $currencyid){
                
                $sql = "SELECT SUM(amount) as qty FROM transactions WHERE accountid = ".$record->id;
                if (!$res=$db->query($sql))
                    badEnd("500", array("msg"=>$db->error));
                
                $balancetx = $res->fetch_assoc();
            
                $sql = "SELECT SUM(amount) AS qty ".
                "       FROM ( ".
                "           SELECT SUM(CASE WHEN orders.side = 1 THEN orders.quantity ELSE orders.total END) AS amount ".
                "           FROM orders ".
                "           WHERE orders.accountid = ".$record->id.
	            "           AND orders.canceled IS NULL ".
	            "           AND orders.type != 1 ".
                "       UNION ALL ".
                "           SELECT SUM(transactions.amount) AS amount ".
                "           FROM transactions, orders ".
                "           WHERE orders.id = transactions.orderid ".
                "           AND transactions.accountid = ".$record->id.
                "           AND orders.accountid = ".$record->id.
	            "           AND orders.canceled IS NULL ".
	            "           AND transactions.amount < 0 ".
	            "           AND orders.type != 1 ".
                "       ) amount";
                if (!$res=$db->query($sql))
                    badEnd("500", array("msg"=>$db->error.$sql));
        
                $balanceorder = $res->fetch_assoc();
            
                //Balance a partir de las transacciones
                $record->balance = new stdClass();
                $record->balance->number = (float)($balancetx["qty"] - $balanceorder["qty"]);
                $param->total->number += $record->balance->number;
            }else{
                //Si la moneda no es igual a la inicial debemos buscar si estan relacionadas en un par
                $sql = "SELECT COUNT(pairs.id) AS qty ".
                "       FROM pairs ".
                "       WHERE ".
                "       (currencyid = ".$currencyid.
                "           AND refcurrencyid = ".$row['currencyid'].
                "       ) OR ".
                "       (currencyid = ".$row['currencyid'].
                "           AND refcurrencyid = ".$currencyid.
                "       )";
                if (!$res=$db->query($sql))
                    badEnd("500", array("msg"=>$db->error.$sql));
        
                $pairres = $res->fetch_assoc();
                
                //Si existe el par directo hacemos la conversion de balance
                if($pairres['qty'] > 0){
                    
                    $sql = "SELECT * FROM pairs WHERE ".
                    "       (currencyid = ".$currencyid.
                    "           AND refcurrencyid = ".$row['currencyid'].
                    "       ) OR ".
                    "       (currencyid = ".$row['currencyid'].
                    "           AND refcurrencyid = ".$currencyid.
                    "       )";
                    if (!$res=$db->query($sql))
                        badEnd("500", array("msg"=>$db->error.$sql));
        
                    $pairres = $res->fetch_assoc();
                
                    //Necesitamos el ultimo precio 1 a 1, para eso buscamos el precio del ultimo intercambio
                    $sql = "SELECT orders.id AS orderid, ".
                    "       orders.quantity AS orderqty, ".
                    "       IF(orders.type = 1, orders.side, IF(orders.side = 1, 2, 1)) AS orderside, ".
                    "       orders.price AS orderprice, ".
                    "       t.id AS txid, ".
                    "       t.datecreated AS datecreated,  ".
                    "       IF(orders.side = 2, t.amount, tx.amount) AS qty ".
                    "       FROM orders, accounts, transactions t  ".
                    "           LEFT JOIN transactions tx ".
                    "           ON t.orderid = tx.orderid  ".
                    "           AND t.txid = tx.txid ".
                    "           AND tx.amount < 0 ".
                    "           AND tx.dsc NOT LIKE 'Comisi%' ".
                    "           LEFT JOIN transactions txother ".
                    "           ON t.orderid != txother.orderid ".
                    "           AND t.txid = txother.txid  ".
                    "           AND txother.amount > 0 ".
                    "           AND txother.dsc NOT LIKE 'Comisi%' ".
                    "       WHERE t.orderid IS NOT NULL ".
                    "       AND orders.id = txother.orderid ".
                    "       AND orders.pairid = ".$pairres['id'].
                    "       AND t.id = t.txid ".
                    "       AND t.dsc NOT LIKE 'Comisi%' ".
                    "       AND accounts.id = orders.accountid ".
                    "       GROUP BY t.txid ".
                    "       ORDER BY datecreated DESC, txid DESC".
                    "       LIMIT 1";
                        if (!$res=$db->query($sql))
                            badEnd("500", array("msg"=>$db->error.$sql));
                        
                    $lastexchange = $res->fetch_assoc();
                    
                    //Consultamos el balance de la cuenta
                    $sql = "SELECT SUM(amount) as qty FROM transactions WHERE accountid = ".$record->id;
                    if (!$res=$db->query($sql))
                        badEnd("500", array("msg"=>$db->error));
                
                    $balancetx = $res->fetch_assoc();
            
                    $sql = "SELECT SUM(amount) AS qty ".
                    "       FROM ( ".
                    "           SELECT SUM(CASE WHEN orders.side = 1 THEN orders.quantity ELSE orders.total END) AS amount ".
                    "           FROM orders ".
                    "           WHERE orders.accountid = ".$record->id.
	                "           AND orders.canceled IS NULL ".
	                "           AND orders.type != 1 ".
                    "       UNION ALL ".
                    "           SELECT SUM(transactions.amount) AS amount ".
                    "           FROM transactions, orders ".
                    "           WHERE orders.id = transactions.orderid ".
                    "           AND transactions.accountid = ".$record->id.
                    "           AND orders.accountid = ".$record->id.
	                "           AND orders.canceled IS NULL ".
	                "           AND transactions.amount < 0 ".
	                "           AND orders.type != 1 ".
                    "       ) amount";
                    if (!$res=$db->query($sql))
                        badEnd("500", array("msg"=>$db->error.$sql));
        
                    $balanceorder = $res->fetch_assoc();
                    
                    $balancetmp = (float)($balancetx["qty"] - $balanceorder["qty"]);
                    
                    //Luego de que tenemos la moneda, necesitamos saber en que moneda esta el precio
                    if($pairres['currencyid'] == $currencyid){
                        $record->balance = new stdClass();
                        $record->balance->number = $balancetmp * $lastexchange['orderprice'];
                        $param->total->number += $record->balance->number;
                        
                        $sql = "SELECT  id, symbol FROM currencies WHERE id = ".$pairres['refcurrencyid'];
                        if (!$res=$db->query($sql))
                            badEnd("500", array("msg"=>$db->error.$sql));
                            
                        $rscur = $res->fetch_assoc();
                        
                        $currentcurrency = new stdClass();
                        $currentcurrency->id = (int)$rscur['id'];
                        $currentcurrency->dsc = $rscur['symbol'];
                        
                        $param->currencies [] = $currentcurrency;
                    }else if($pairres['refcurrencyid'] == $currencyid){
                        $record->balance = new stdClass();
                        $record->balance->number = ($lastexchange['orderprice'] > 0 ? $balancetmp / $lastexchange['orderprice'] : 0);
                        $param->total->number += $record->balance->number;
                        
                        $sql = "SELECT  id, symbol FROM currencies WHERE id = ".$pairres['currencyid'];
                        if (!$res=$db->query($sql))
                            badEnd("500", array("msg"=>$db->error.$sql));
                            
                        $rscur = $res->fetch_assoc();
                        
                        $currentcurrency = new stdClass();
                        $currentcurrency->id = (int)$rscur['id'];
                        $currentcurrency->dsc = $rscur['symbol'];
                        
                        $param->currencies [] = $currentcurrency;
                    }
                }else{
                    //Si no se encuentra directamente a un par debemos hacer una doble conversion(NO HOY PLS)
                    $record->balance = new stdClass();
                    $record->balance->number = 0;
                    
                    // Primero, buscaremos todos los pares donde se encuentra nuestra moneda inicial(1)
                    $sql = "SELECT COUNT(pairs.id) AS qty ".
                    "       FROM pairs ".
                    "       WHERE ".
                    "       (currencyid = ".$currencyid.
                    "           OR refcurrencyid = ".$currencyid.
                    "       )";
                    if (!$res=$db->query($sql))
                        badEnd("500", array("msg"=>$db->error.$sql));
                    
                    $pairres = $res->fetch_assoc();
                    
                    if($pairres['qty'] > 0){
                        // Ahora debemos recorrer los pares buscando por su otra moneda(2)
                        $sql = "SELECT * ".
                        "       FROM pairs ".
                        "       WHERE ".
                        "       (currencyid = ".$currencyid.
                        "           OR refcurrencyid = ".$currencyid.
                        "       )";
                        if (!$res=$db->query($sql))
                            badEnd("500", array("msg"=>$db->error.$sql));
                            
                        while($pairres = $res->fetch_assoc()){
                            //Guardamos la otra moneda(2) del par
                            $secondcurrencyid = $pairred['currencyid'] == $currencyid ? $pairres['refcurrencyid'] : $pairres['currencyid'];
                            
                            // algun par que este con la moneda(3) que buscamos
                            $sql = "SELECT COUNT(pairs.id) AS qty ".
                            "       FROM pairs ".
                            "       WHERE ".
                            "       (currencyid = ".$secondcurrencyid.
                            "           AND refcurrencyid = ".$pairres['currencyid'].
                            "       ) OR ".
                            "       (currencyid = ".$pairres['currencyid'].
                            "           AND refcurrencyid = ".$secondcurrencyid.
                            "       )";
                            if (!$res=$db->query($sql))
                                badEnd("500", array("msg"=>$db->error.$sql));
        
                            $secondpairres = $res->fetch_assoc();
                            
                            if($secondpairres['qty'] > 0){
                                
                                $sql = "SELECT * ".
                                "       FROM pairs ".
                                "       WHERE ".
                                "       (currencyid = ".$secondcurrencyid.
                                "           AND refcurrencyid = ".$pairres['currencyid'].
                                "       ) OR ".
                                "       (currencyid = ".$pairres['currencyid'].
                                "           AND refcurrencyid = ".$secondcurrencyid.
                                "       )";
                                if (!$res=$db->query($sql))
                                    badEnd("500", array("msg"=>$db->error.$sql));
        
                                $seconpairres = $res->fetch_assoc();
                                
                                //Necesitamos el ultimo precio 1 a 1, para eso buscamos el precio del ultimo intercambio
                                $sql = "SELECT orders.id AS orderid, ".
                                "       orders.quantity AS orderqty, ".
                                "       IF(orders.type = 1, orders.side, IF(orders.side = 1, 2, 1)) AS orderside, ".
                                "       orders.price AS orderprice, ".
                                "       t.id AS txid, ".
                                "       t.datecreated AS datecreated,  ".
                                "       IF(orders.side = 2, t.amount, tx.amount) AS qty ".
                                "       FROM orders, accounts, transactions t  ".
                                "           LEFT JOIN transactions tx ".
                                "           ON t.orderid = tx.orderid  ".
                                "           AND t.txid = tx.txid ".
                                "           AND tx.amount < 0 ".
                                "           AND tx.dsc NOT LIKE 'Comisi%' ".
                                "           LEFT JOIN transactions txother ".
                                "           ON t.orderid != txother.orderid ".
                                "           AND t.txid = txother.txid  ".
                                "           AND txother.amount > 0 ".
                                "           AND txother.dsc NOT LIKE 'Comisi%' ".
                                "       WHERE t.orderid IS NOT NULL ".
                                "       AND orders.id = txother.orderid ".
                                "       AND orders.pairid = ".$seconpairres['id'].
                                "       AND t.id = t.txid ".
                                "       AND t.dsc NOT LIKE 'Comisi%' ".
                                "       AND accounts.id = orders.accountid ".
                                "       GROUP BY t.txid ".
                                "       ORDER BY datecreated DESC, txid DESC".
                                "       LIMIT 1";
                                if (!$res=$db->query($sql))
                                    badEnd("500", array("msg"=>$db->error.$sql));
                            
                                $lastexchange = $res->fetch_assoc();
                    
                                //Consultamos el balance de la cuenta
                                $sql = "SELECT SUM(amount) as qty FROM transactions WHERE accountid = ".$record->id;
                                if (!$res=$db->query($sql))
                                    badEnd("500", array("msg"=>$db->error));
                
                                $balancetx = $res->fetch_assoc();
            
                                $sql = "SELECT SUM(amount) AS qty ".
                                "       FROM ( ".
                                "           SELECT SUM(CASE WHEN orders.side = 1 THEN orders.quantity ELSE orders.total END) AS amount ".
                                "           FROM orders ".
                                "           WHERE orders.accountid = ".$record->id.
                                "           AND orders.canceled IS NULL ".
                                "           AND orders.type != 1 ".
                                "       UNION ALL ".
                                "           SELECT SUM(transactions.amount) AS amount ".
                                "           FROM transactions, orders ".
                                "           WHERE orders.id = transactions.orderid ".
                                "           AND transactions.accountid = ".$record->id.
                                "           AND orders.accountid = ".$record->id.
                                "           AND orders.canceled IS NULL ".
                                "           AND transactions.amount < 0 ".
                                "           AND orders.type != 1 ".
                                "       ) amount";
                                if (!$res=$db->query($sql))
                                    badEnd("500", array("msg"=>$db->error.$sql));
        
                                $balanceorder = $res->fetch_assoc();
                    
                                $balancetmp = (float)($balancetx["qty"] - $balanceorder["qty"]);
                    
                                // Ahora hacemos una conversion de la moneda(3) que buscamos a la otra moneda(2)
                                if($pairres['currencyid'] == $currencyid){
                                    $balancetmp = $balancetmp * $lastexchange['orderprice'];
                                    break;
                                }else if($pairres['refcurrencyid'] == $currencyid){
                                    $balancetmp = ($lastexchange['orderprice'] > 0 ? $balancetmp / $lastexchange['orderprice'] : 0);
                                    break;
                                }
                                
                                // Y entonces desde la otra moneda(2) convertimos a la inicial(1)
                                //Necesitamos el ultimo precio 1 a 1, para eso buscamos el precio del ultimo intercambio
                                $sql = "SELECT orders.id AS orderid, ".
                                "       orders.quantity AS orderqty, ".
                                "       IF(orders.type = 1, orders.side, IF(orders.side = 1, 2, 1)) AS orderside, ".
                                "       orders.price AS orderprice, ".
                                "       t.id AS txid, ".
                                "       t.datecreated AS datecreated,  ".
                                "       IF(orders.side = 2, t.amount, tx.amount) AS qty ".
                                "       FROM orders, accounts, transactions t  ".
                                "           LEFT JOIN transactions tx ".
                                "           ON t.orderid = tx.orderid  ".
                                "           AND t.txid = tx.txid ".
                                "           AND tx.amount < 0 ".
                                "           AND tx.dsc NOT LIKE 'Comisi%' ".
                                "           LEFT JOIN transactions txother ".
                                "           ON t.orderid != txother.orderid ".
                                "           AND t.txid = txother.txid  ".
                                "           AND txother.amount > 0 ".
                                "           AND txother.dsc NOT LIKE 'Comisi%' ".
                                "       WHERE t.orderid IS NOT NULL ".
                                "       AND orders.id = txother.orderid ".
                                "       AND orders.pairid = ".$pairres['id'].
                                "       AND t.id = t.txid ".
                                "       AND t.dsc NOT LIKE 'Comisi%' ".
                                "       AND accounts.id = orders.accountid ".
                                "       GROUP BY t.txid ".
                                "       ORDER BY datecreated DESC, txid DESC".
                                "       LIMIT 1";
                                if (!$res=$db->query($sql))
                                    badEnd("500", array("msg"=>$db->error.$sql.'-------'.$pairres['id'].'---'.$secondcurrencyid.'----'.$pairres['currencyid']));
                        
                                $lastexchange = $res->fetch_assoc();
                        
                                //Luego de que tenemos la moneda, necesitamos saber en que moneda esta el precio
                                if($pairres['currencyid'] == $currencyid){
                                    $record->balance = new stdClass();
                                    $record->balance->number = $balancetmp * $lastexchange['orderprice'];
                                    $param->total->number += $record->balance->number;
                                    
                                    $sql = "SELECT  id, symbol FROM currencies WHERE id = ".$pairres['refcurrencyid'];
                                    if (!$res=$db->query($sql))
                                        badEnd("500", array("msg"=>$db->error.$sql));
                            
                                    $rscur = $res->fetch_assoc();
                            
                                    $currentcurrency = new stdClass();
                                    $currentcurrency->id = (int)$rscur['id'];
                                    $currentcurrency->dsc = $rscur['symbol'];
                            
                                    $param->currencies [] = $currentcurrency;
                                }else if($pairres['refcurrencyid'] == $currencyid){
                                    $record->balance = new stdClass();
                                    $record->balance->number = ($lastexchange['orderprice'] > 0 ? $balancetmp / $lastexchange['orderprice'] : 0);
                                    $param->total->number += $record->balance->number;
                                    
                                    $sql = "SELECT  id, symbol FROM currencies WHERE id = ".$pairres['currencyid'];
                                    if (!$res=$db->query($sql))
                                        badEnd("500", array("msg"=>$db->error.$sql));
                            
                                    $rscur = $res->fetch_assoc();
                            
                                    $currentcurrency = new stdClass();
                                    $currentcurrency->id = (int)$rscur['id'];
                                    $currentcurrency->dsc = $rscur['symbol'];
                            
                                    $param->currencies [] = $currentcurrency;
                                }
                            }
                        }
                    }
                }
            }
            
            $records [] = $record; 
        }
        
        $param->records = $records;
        
        $param->total->formatted = numberFormatt($param->total->number, $decimals);
        
        return $param;
    }
?>
