<?php
    header("Content-Type:application/json");
    include("../../../settings/dbconn.php");
    include("../../../settings/utils.php");
    
    echo(json_encode(getPortfolio($db, 631, 0)));    

    function getPortfolio($db, $userid, $totportfolio){
        $portfolio = new stdClass();
        
        $balancepercents = calculateTotal($db, $userid, $totportfolio);
        //$portfolio->testt = $balancepercents;
        
        $portfolio->total = new stdClass();
        $portfolio->total = $balancepercents->total;
        //$portfolio->total->formatted = numberFormatt($portfolio->total->number,2);
        
        $portfolio->total->currencies = $balancepercents->currencies;
        
        $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, ".
        "       curr.icon AS currencyicon, ".
        "       curr.xfer AS currencyxfer, ".
        "       acc.id AS accountid, ".
        "       acc.paymentmethodid AS paymentmethodid, ".
        "       acc.status AS accountstatus ".
        "       FROM currencies curr ".
        "       LEFT JOIN accounts acc ON ".
        "       acc.currencyid = curr.id ".
        "       AND acc.entityusrid = -1 ".
        "       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.entityusrid = -1 ".
        "           AND acc.paymentmethodid = -1)) AND curr.id > 0";
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
        
        $portfolio->accounts = new stdClass();
        
        while($row = $rs->fetch_assoc()){
            $record = new stdClass();

            //Moneda de la cuenta
            $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->status = (int)$row['currencystatus'];
            $record->currency->interface = (int)$row['currencyinterface'];
            $record->currency->icon = $row['currencyicon'];
            $record->currency->xfer = (int)$row['currencyxfer'];
            
            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"=>determinateDBError($db)));
            
                $payment = $res->fetch_assoc();
                //Guardamos el nombre del metodo de pago
                $record->paymentmethod->dsc = $payment["dsc"];
                
                $sql = "SELECT SUM(amount) - ABS(IF(( ".
                "           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) IS NOT NULL,  ".
            	"               ( ".
                "           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), 0)".
                "       + IF(( ".
                "           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) IS NOT NULL, ( ".
                "           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),0) ".
            	"      + IF ((SELECT SUM(transactions.amount + transactions.amountghost) AS amount ".
            	"           FROM transactions  ".
            	"           WHERE transactions.paypending = 1".
            	"           AND transactions.accountid = ".$record->id.
            	"           AND NOT EXISTS(SELECT withdrawals.transactionid FROM withdrawals WHERE withdrawals.transactionid = transactions.id)) IS NOT NULL, ".
            	"           (SELECT SUM(transactions.amount + transactions.amountghost) AS amount ".
            	"           FROM transactions  ".
            	"           WHERE transactions.paypending = 1".
            	"           AND transactions.accountid = ".$record->id.
            	"           AND NOT EXISTS(SELECT withdrawals.transactionid FROM withdrawals WHERE withdrawals.transactionid = transactions.id)), 0)".
            	"      ) as qty FROM transactions WHERE accountid = ".$record->id.
                "       AND paypending = 0";
                if (!$res=$db->query($sql))
                    badEnd("500", array("msg"=>determinateDBError($db)));
                
                $balancetx = $res->fetch_assoc();
            
                // Balance retenido por ordenes de intercambios
                $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 ".
            	"       UNION ALL ".
            	"           SELECT SUM(ABS(transactions.amount) + ABS(transactions.amountghost)) AS amount ".
            	"           FROM transactions  ".
            	"           WHERE transactions.paypending = 1".
            	"           AND transactions.accountid = ".$record->id.
            	"           AND NOT EXISTS(SELECT withdrawals.transactionid FROM withdrawals WHERE withdrawals.transactionid = transactions.id)".
            	"      UNION ALL ".
            	"           SELECT SUM(ABS(withdrawals.amount) + ABS(withdrawals.fee)) AS amount ".
            	"           FROM transactions, withdrawals ".
            	"           WHERE transactions.id = withdrawals.transactionid ".
            	"           AND transactions.accountid = ".$record->id.
            	"           AND withdrawals.valid IS NUll ".
            	"           AND withdrawals.rejected IS NULL ".
                "       ) amount";
                if (!$res=$db->query($sql))
                    badEnd("500", array("msg"=>determinateDBError($db)));
                
                $balanceorder = $res->fetch_assoc();
            
                //Balance a partir de las transacciones
                $record->balance = new stdClass();
                $record->balance->number = bcdiv(number_format( $balancetx["qty"] ,13, '.', ''), '1',$row["currencydecimals"]);
                $record->balance->number = $record->balance->number < 0 ? 0 : $record->balance->number;
                $record->balance->formatted = numberFormatt($record->balance->number,$row["currencydecimals"]);
            
                //Balance a partir de las transacciones
                $record->committed = new stdClass();
                $record->committed->number = (float)number_format($balanceorder["qty"],$row["currencydecimals"],'.','');
                $record->committed->formatted = numberFormatt($record->committed->number,$row["currencydecimals"]);
                
                //Balance pendiente de la cuenta
                $sql = "SELECT SUM(deposits.amount - deposits.fee) as qty ".
                "       FROM accounts, transactions".
                "       LEFT JOIN deposits ON transactions.id = deposits.transactionid ".
                "       AND deposits.rejected IS NULL AND deposits.valid IS NULL".
	            "       WHERE accounts.id = transactions.accountid AND accounts.id = ".$record->id.
	            "       AND accounts.entityusrid = -1 ".
	            "       AND transactions.amountghost >= 0";
                if (!$res=$db->query($sql))
                    badEnd("500", array("msg"=>determinateDBError($db)));
        
                $balance = $res->fetch_assoc();
    
                $record->deferred = new stdClass();
                $record->deferred->number = (float)number_format($balance["qty"],$row["currencydecimals"],'.','');
                $record->deferred->formatted = numberFormatt($record->deferred->number,$row["currencydecimals"]);
            
                $record->total = new stdClass();
                $record->total->number = (float)bcdiv(number_format($record->balance->number,13,'.',''), '1', $row["currencydecimals"]);
                $record->total->formatted = numberFormatt($record->total->number,$row["currencydecimals"]);
                
                 //Porcentaje que representa
                $record->pct = new stdClass();
                $record->pct->number = $record->balance->number < 1 || $portfolio->total->number < 1 ? 0 : $record->balance->number / ($portfolio->total->number / 100);
                $record->pct->formatted = numberFormatt($record->pct->number,2)."%";
                
                //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 < 1 || $balancepercents->total->number < 1 ? 0 : $balanceptcs->balance->number / ($balancepercents->total->number / 100);
                        $record->pct->formatted = numberFormatt($record->pct->number,2)."%";
                    }
                }
            
                //Datos de la cuenta
                $record->status = (int)$row["accountstatus"];
            }else{
                $record->id = 0;
            }
            
            $records [] = $record;
        }
        
        $portfolio->accounts = $records;
        
        return $portfolio;
    }
    
    
    /* 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"=>determinateDBError($db)));
        
            //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->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"=>determinateDBError($db)));
        
            //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->currencies [] = $record;
            }
        }
        
        //Consultamos las cuentas del usuario
        $sql = "SELECT * FROM accounts ".
        "       WHERE userid = ".$userid.
        "       AND accounts.paymentmethodid = -1".
        "       AND accounts.entityusrid = -1";
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
            
        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"=>determinateDBError($db)));
                
                $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"=>determinateDBError($db)));
        
                $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"=>determinateDBError($db)));
        
                $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"=>determinateDBError($db)));
        
                    $pairres = $res->fetch_assoc();
                
                    //Necesitamos el ultimo precio 1 a 1, para eso buscamos el precio del ultimo intercambio
                    //Obtenemos la ultima transaccion realizada
                    $sql = "SELECT (SELECT ord.price FROM orders ord ".
                    "       WHERE ord.type = 2 AND (ord.id = t.orderid OR ord.id = txother.orderid)) AS orderprice ".
                    "       FROM orders, accounts, transactions t  ".
                    "           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 accounts.entityusrid = -1 ".
                    "       AND t.id = t.txid ".
                    "       AND t.dsc NOT LIKE 'Comisi%' ".
                    "       AND accounts.id = orders.accountid ".
                    "       GROUP BY t.txid ".
                    "       ORDER BY t.datecreated DESC, t.id DESC".
                    "       LIMIT 1";
                    if (!$res=$db->query($sql))
                        badEnd("500", array("msg"=>determinateDBError($db)));
                            
                    $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"=>determinateDBError($db)));
                
                    $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"=>determinateDBError($db)));
        
                    $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"=>determinateDBError($db)));
                            
                        $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"=>determinateDBError($db)));
                            
                        $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"=>determinateDBError($db)));
                    
                    $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"=>determinateDBError($db)));
                            
                        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"=>determinateDBError($db)));
        
                            $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"=>determinateDBError($db)));
        
                                $seconpairres = $res->fetch_assoc();
                                
                                //Necesitamos el ultimo precio 1 a 1, para eso buscamos el precio del ultimo intercambio
                                //Obtenemos la ultima transaccion realizada
                                $sql = "SELECT (SELECT ord.price FROM orders ord ".
                                "       WHERE ord.type = 2 AND (ord.id = t.orderid OR ord.id = txother.orderid)) AS orderprice ".
                                "       FROM orders, accounts, transactions t  ".
                                "           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.entityusrid = -1 ".
                                "       AND accounts.id = orders.accountid ".
                                "       GROUP BY t.txid ".
                                "       ORDER BY t.datecreated DESC, t.id DESC".
                                "       LIMIT 1";
                                if (!$res=$db->query($sql))
                                    badEnd("500", array("msg"=>determinateDBError($db)));
                                        
                                $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"=>determinateDBError($db)));
                
                                $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"=>determinateDBError($db)));
        
                                $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
                                //Obtenemos la ultima transaccion realizada
                                $sql = "SELECT (SELECT ord.price FROM orders ord ".
                                "       WHERE ord.type = 2 AND (ord.id = t.orderid OR ord.id = txother.orderid)) AS orderprice ".
                                "       FROM orders, accounts, transactions t  ".
                                "           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.entityusrid = -1 ".
                                "       AND accounts.id = orders.accountid ".
                                "       GROUP BY t.txid ".
                                "       ORDER BY t.datecreated DESC, t.id DESC".
                                "       LIMIT 1";
                                if (!$res=$db->query($sql))
                                    badEnd("500", array("msg"=>determinateDBError($db)));
                                        
                                $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"=>determinateDBError($db)));
                            
                                    $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"=>determinateDBError($db)));
                            
                                    $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->number = ($param->total->number < 0 ? 0 : $param->total->number);
        $param->total->formatted = numberFormatt($param->total->number, $decimals);
        
        return $param;
    }
?>