<?php
// cms/api/balances/general

    header("Content-Type:application/json");
    include("../../../settings/dbconn.php");
    include("../../../settings/utils.php");
    
    // parametros obligatorios
    $parmsob = array("sessionid", "order", "offset", "numofrec");
    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']);
    $numofrec = mysqli_real_escape_string($db, $_GET['numofrec']);
    $filter = "";
    $currencyid = "";
    $whereclause = ""; 
    $otheraccounts = " AND cur.id > 0 ";
    $order = determinateOrder(mysqli_real_escape_string($db, $_GET["order"]));
    // VAriable para controlar busqueda por fecha o por fecha y hora
    $dateAndHour = false;
    
    //Validamos que la session del usuario sea valida para continuar
    isSessionValidCMS($db, $sessionid);
    
    //Validamos que el usuario tenga privilegios
    if(!tienePrivilegio($db, $sessionid, 36))
        badEnd("403", array("msg"=>"No tiene Privilegios"));
    
    //  En caso de que el otheraccounts sea enviado, armamos el query
    if(isset($_GET["otheraccounts"]) && $_GET["otheraccounts"] != ""){
        if($_GET["otheraccounts"] == 1){
            $otheraccounts = " AND cur.id < -1 ";
        }else{
            $otheraccounts = " AND cur.id > 0 ";
        }
    }
    
    //En caso de que el filter sea enviado, se construye el query
    // Filtros por montos
    if(isset($_GET["filter"]) && $_GET["filter"] != ""){
        // Iniciamos la cadena del query
        $filter = " 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
            // Partimos la hora de la fecha
            $datehour = explode(' - ',$el);
            
            //SI este pana trae la hora, guardamos que la trajo
            if(sizeof($datehour) > 1){
                $dateAndHour = true;
            }
            
            if(strpos($datehour[0], '/') !== false){
                $dateelements = explode("/",$datehour[0]);
                
                foreach($dateelements as $i => $element){
                    if($i == 0){
                        $dateConverted .= $element;
                    }else{
                        $dateConverted .= "-".$element;
                    }
                }
                
                $dateel = strftime("%Y-%m-%d", strtotime($dateConverted));
                $dateelhour = strftime("%Y-%m-%d %H:%M", strtotime($dateConverted." ".$datehour[1]));
            }else{
                $dateel = strftime("%Y-%m-%d", strtotime($el));
                $dateelhour = strftime("%Y-%m-%d %H:%M", strtotime($el));
            }
            
                $filter .= ($index != 0 ? " OR " : '').( !$dateAndHour ? " (SELECT DATE(transactions.datecreated) FROM  transactions, accounts ".
                " 	            WHERE accounts.currencyid = cur.id ".
                "          	    AND accounts.id = transactions.accountid ".
                "         	    AND transactions.datecreated = (SELECT MAX(transactions.datecreated) FROM transactions WHERE accounts.id = transactions.accountid) ".
                "         	    ORDER BY transactions.datecreated DESC, transactions.id DESC ".
                "    		    LIMIT 1 ".
                "           ) = DATE('".$dateel."') " : '').
                ( $dateAndHour ? " (SELECT DATE_FORMAT(transactions.datecreated,'%Y-%m-%d %l:%i') FROM  transactions, accounts ".
                " 	            WHERE accounts.currencyid = cur.id ".
                "          	    AND accounts.id = transactions.accountid ".
                "         	    AND transactions.datecreated = (SELECT MAX(transactions.datecreated) FROM transactions WHERE accounts.id = transactions.accountid) ".
                "         	    ORDER BY transactions.datecreated DESC, transactions.id DESC ".
                "    		    LIMIT 1 ".
                "           ) = DATE_FORMAT('".$dateelhour."','%Y-%m-%d %l:%i') " : '').
                "           OR cur.symbol LIKE '".$el."%' ".
                "           ".(validNumber($el) ? " OR (SELECT COUNT(accounts.id) ".
                "        	    FROM accounts ".
                "   	    	WHERE accounts.currencyid = cur.id ".
                "               AND accounts.paymentmethodid = -1 ".
                "           ) LIKE '%".floatval($el)."%'" : (validNumberFormatted($el) ? " OR (SELECT COUNT(accounts.id) ".
                "        	    FROM accounts ".
                "   	    	WHERE accounts.currencyid = cur.id ".
                "               AND accounts.paymentmethodid = -1 ".
                "           ) LIKE '%".parseFormattedNumber($el)."%'" : '') ).
                "           ".(validNumber($el) ? " OR IF((SELECT SUM(transactions.amount) - (IF(( ".
                "           SELECT SUM(CASE WHEN orders.side = 1 THEN orders.quantity ELSE orders.total END) AS amount ".
                "           FROM orders, accounts ".
                "           WHERE orders.accountid = accounts.id".
                "           AND accounts.currencyid = cur.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, accounts ".
                "           WHERE orders.accountid = accounts.id".
                "           AND accounts.currencyid = cur.id ".
            	"           AND orders.canceled IS NULL ".
            	"           AND orders.type != 1), 0)".
                "       + IF(( ".
                "           SELECT SUM(transactions.amount) AS amount ".
                "           FROM transactions, orders, accounts ".
                "           WHERE orders.id = transactions.orderid ".
                "           AND transactions.accountid = accounts.id".
                "           AND accounts.currencyid = cur.id ".
                "           AND orders.accountid = accounts.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, accounts ".
                "           WHERE orders.id = transactions.orderid ".
                "           AND transactions.accountid = accounts.id".
                "           AND accounts.currencyid = cur.id ".
                "           AND orders.accountid = accounts.id".
            	"           AND orders.canceled IS NULL ".
            	"           AND transactions.amount < 0 ".
            	"           AND orders.type != 1),0)) ".
                "        	    FROM transactions, accounts ".
                "        	    WHERE transactions.accountid = accounts.id ".
                "       	    AND accounts.currencyid = cur.id ".
                "       	    AND accounts.paymentmethodid = -1) IS NOT NULL, ".
                "               ROUND((SELECT SUM(transactions.amount) - (IF(( ".
                "           SELECT SUM(CASE WHEN orders.side = 1 THEN orders.quantity ELSE orders.total END) AS amount ".
                "           FROM orders, accounts ".
                "           WHERE orders.accountid = accounts.id".
                "           AND accounts.currencyid = cur.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, accounts ".
                "           WHERE orders.accountid = accounts.id".
                "           AND accounts.currencyid = cur.id ".
            	"           AND orders.canceled IS NULL ".
            	"           AND orders.type != 1), 0)".
                "       + IF(( ".
                "           SELECT SUM(transactions.amount) AS amount ".
                "           FROM transactions, orders, accounts ".
                "           WHERE orders.id = transactions.orderid ".
                "           AND transactions.accountid = accounts.id".
                "           AND accounts.currencyid = cur.id ".
                "           AND orders.accountid = accounts.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, accounts ".
                "           WHERE orders.id = transactions.orderid ".
                "           AND transactions.accountid = accounts.id".
                "           AND accounts.currencyid = cur.id ".
                "           AND orders.accountid = accounts.id".
            	"           AND orders.canceled IS NULL ".
            	"           AND transactions.amount < 0 ".
            	"           AND orders.type != 1),0)) ".
                "        	        FROM transactions, accounts ".
                "        	        WHERE transactions.accountid = accounts.id ".
                "       	        AND accounts.currencyid = cur.id ".
                "       	        AND accounts.paymentmethodid = -1), cur.decimals), ".
                "                0) LIKE '%".floatval($el)."%'" : (validNumberFormatted($el) ? " OR IF((SELECT SUM(transactions.amount) - (IF(( ".
                "           SELECT SUM(CASE WHEN orders.side = 1 THEN orders.quantity ELSE orders.total END) AS amount ".
                "           FROM orders, accounts ".
                "           WHERE orders.accountid = accounts.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, accounts ".
                "           WHERE orders.accountid = accounts.id".
            	"           AND orders.canceled IS NULL ".
            	"           AND orders.type != 1), 0)".
                "       + IF(( ".
                "           SELECT SUM(transactions.amount) AS amount ".
                "           FROM transactions, orders, accounts ".
                "           WHERE orders.id = transactions.orderid ".
                "           AND transactions.accountid = accounts.id".
                "           AND accounts.currencyid = cur.id ".
                "           AND orders.accountid = accounts.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, accounts ".
                "           WHERE orders.id = transactions.orderid ".
                "           AND transactions.accountid = accounts.id".
                "           AND accounts.currencyid = cur.id ".
                "           AND orders.accountid = accounts.id".
            	"           AND orders.canceled IS NULL ".
            	"           AND transactions.amount < 0 ".
            	"           AND orders.type != 1),0)) ".
                "        	    FROM transactions, accounts ".
                "        	    WHERE transactions.accountid = accounts.id ".
                "       	    AND accounts.currencyid = cur.id ".
                "       	    AND accounts.paymentmethodid = -1) IS NOT NULL, ".
                "               ROUND((SELECT SUM(transactions.amount) - (IF(( ".
                "           SELECT SUM(CASE WHEN orders.side = 1 THEN orders.quantity ELSE orders.total END) AS amount ".
                "           FROM orders, accounts ".
                "           WHERE orders.accountid = accounts.id".
                "           AND accounts.currencyid = cur.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, accounts ".
                "           WHERE orders.accountid = accounts.id".
                "           AND accounts.currencyid = cur.id ".
            	"           AND orders.canceled IS NULL ".
            	"           AND orders.type != 1), 0)".
                "       + IF(( ".
                "           SELECT SUM(transactions.amount) AS amount ".
                "           FROM transactions, orders, accounts ".
                "           WHERE orders.id = transactions.orderid ".
                "           AND transactions.accountid = accounts.id".
                "           AND accounts.currencyid = cur.id ".
                "           AND orders.accountid = accounts.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, accounts ".
                "           WHERE orders.id = transactions.orderid ".
                "           AND transactions.accountid = accounts.id".
                "           AND accounts.currencyid = cur.id ".
                "           AND orders.accountid = accounts.id".
            	"           AND orders.canceled IS NULL ".
            	"           AND transactions.amount < 0 ".
            	"           AND orders.type != 1),0)) ".
                "        	        FROM transactions, accounts ".
                "        	        WHERE transactions.accountid = accounts.id ".
                "       	        AND accounts.currencyid = cur.id ".
                "       	        AND accounts.paymentmethodid = -1), cur.decimals), ".
                "                0) LIKE '%".parseFormattedNumber($el)."%'" : '')).
                "           ".(validNumber($el) ? " OR IF((SELECT SUM(deposits.amount - deposits.fee) ".
                "               FROM accounts, transactions, deposits ".
                "               WHERE transactions.id = deposits.transactionid ".
                "               AND deposits.rejected IS NULL ".
                "               AND deposits.valid IS NULL ".
                "               AND accounts.id = transactions.accountid ".
                "               AND accounts.currencyid = cur.id ".
                "               AND transactions.amountghost >= 0) IS NOT NULL,".
                "               ROUND((SELECT SUM(deposits.amount - deposits.fee) ".
                "                   FROM accounts, transactions, deposits ".
                "                   WHERE transactions.id = deposits.transactionid ".
                "                   AND deposits.rejected IS NULL ".
                "                   AND deposits.valid IS NULL ".
                "                   AND accounts.id = transactions.accountid ".
                "                   AND accounts.currencyid = cur.id ".
                "                   AND transactions.amountghost >= 0), cur.decimals), ".
                "            0) LIKE '%".floatval($el)."%'" : ( validNumberFormatted($el) ? " OR IF((SELECT SUM(deposits.amount - deposits.fee) ".
                "               FROM accounts, transactions, deposits ".
                "               WHERE transactions.id = deposits.transactionid ".
                "               AND deposits.rejected IS NULL ".
                "               AND deposits.valid IS NULL ".
                "               AND accounts.id = transactions.accountid ".
                "               AND accounts.currencyid = cur.id ".
                "               AND transactions.amountghost >= 0) IS NOT NULL,".
                "               ROUND((SELECT SUM(deposits.amount - deposits.fee) ".
                "                   FROM accounts, transactions, deposits ".
                "                   WHERE transactions.id = deposits.transactionid ".
                "                   AND deposits.rejected IS NULL ".
                "                   AND deposits.valid IS NULL ".
                "                   AND accounts.id = transactions.accountid ".
                "                   AND accounts.currencyid = cur.id ".
                "                   AND transactions.amountghost >= 0), cur.decimals), ".
                "            0) LIKE '%".parseFormattedNumber($el)."%'" : '')).
                "           ".(validNumber($el) ? " OR ( IF((SELECT SUM(transactions.amount) - (IF(( ".
                "           SELECT SUM(CASE WHEN orders.side = 1 THEN orders.quantity ELSE orders.total END) AS amount ".
                "           FROM orders, accounts ".
                "           WHERE orders.accountid = accounts.id".
                "           AND accounts.currencyid = cur.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, accounts ".
                "           WHERE orders.accountid = accounts.id".
                "           AND accounts.currencyid = cur.id ".
            	"           AND orders.canceled IS NULL ".
            	"           AND orders.type != 1), 0)".
                "       + IF(( ".
                "           SELECT SUM(transactions.amount) AS amount ".
                "           FROM transactions, orders, accounts ".
                "           WHERE orders.id = transactions.orderid ".
                "           AND transactions.accountid = accounts.id".
                "           AND accounts.currencyid = cur.id ".
                "           AND orders.accountid = accounts.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, accounts ".
                "           WHERE orders.id = transactions.orderid ".
                "           AND transactions.accountid = accounts.id".
                "           AND accounts.currencyid = cur.id ".
                "           AND orders.accountid = accounts.id".
            	"           AND orders.canceled IS NULL ".
            	"           AND transactions.amount < 0 ".
            	"           AND orders.type != 1),0)) ".
                "        	FROM transactions, accounts ".
                "        	WHERE transactions.accountid = accounts.id ".
                "       	AND accounts.currencyid = cur.id ".
                "       	AND accounts.paymentmethodid = -1 ".
                "       ) IS NOT NULL, (SELECT SUM(transactions.amount) - (IF(( ".
                "           SELECT SUM(CASE WHEN orders.side = 1 THEN orders.quantity ELSE orders.total END) AS amount ".
                "           FROM orders, accounts ".
                "           WHERE orders.accountid = accounts.id".
                "           AND accounts.currencyid = cur.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, accounts ".
                "           WHERE orders.accountid = accounts.id".
                "           AND accounts.currencyid = cur.id ".
            	"           AND orders.canceled IS NULL ".
            	"           AND orders.type != 1), 0)".
                "       + IF(( ".
                "           SELECT SUM(transactions.amount) AS amount ".
                "           FROM transactions, orders, accounts ".
                "           WHERE orders.id = transactions.orderid ".
                "           AND transactions.accountid = accounts.id".
                "           AND accounts.currencyid = cur.id ".
                "           AND orders.accountid = accounts.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, accounts ".
                "           WHERE orders.id = transactions.orderid ".
                "           AND transactions.accountid = accounts.id".
                "           AND accounts.currencyid = cur.id ".
                "           AND orders.accountid = accounts.id".
            	"           AND orders.canceled IS NULL ".
            	"           AND transactions.amount < 0 ".
            	"           AND orders.type != 1),0)) ".
                "        	FROM transactions, accounts ".
                "        	WHERE transactions.accountid = accounts.id ".
                "       	AND accounts.currencyid = cur.id ".
                "       	AND accounts.paymentmethodid = -1 ".
                "       ), 0)) ".
                "       + IF((SELECT SUM(deposits.amount - deposits.fee) ".
                "               FROM accounts, transactions, deposits ".
                "               WHERE transactions.id = deposits.transactionid ".
                "               AND deposits.rejected IS NULL ".
                "               AND deposits.valid IS NULL ".
                "               AND accounts.id = transactions.accountid ".
                "               AND accounts.currencyid = cur.id ".
                "               AND transactions.amountghost >= 0) IS NOT NULL,".
                "               (SELECT SUM(deposits.amount - deposits.fee) ".
                "                   FROM accounts, transactions, deposits ".
                "                   WHERE transactions.id = deposits.transactionid ".
                "                   AND deposits.rejected IS NULL ".
                "                   AND deposits.valid IS NULL ".
                "                   AND accounts.id = transactions.accountid ".
                "                   AND accounts.currencyid = cur.id ".
                "                   AND transactions.amountghost >= 0), ".
                "            0) ".
                "       + (IF(( ".
                "           SELECT SUM(CASE WHEN orders.side = 1 THEN orders.quantity ELSE orders.total END) AS amount ".
                "           FROM orders, accounts ".
                "           WHERE orders.accountid = accounts.id".
                "           AND accounts.currencyid = cur.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, accounts ".
                "           WHERE orders.accountid = accounts.id".
                "           AND accounts.currencyid = cur.id" .
            	"           AND orders.canceled IS NULL ".
            	"           AND orders.type != 1), 0)".
                "       + IF(( ".
                "           SELECT SUM(transactions.amount) AS amount ".
                "           FROM transactions, orders, accounts ".
                "           WHERE orders.id = transactions.orderid ".
                "           AND transactions.accountid = accounts.id".
                "           AND accounts.currencyid = cur.id".
                "           AND orders.accountid = accounts.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, accounts ".
                "           WHERE orders.id = transactions.orderid ".
                "           AND transactions.accountid = accounts.id".
                "           AND accounts.currencyid = cur.id".
                "           AND orders.accountid = accounts.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, accounts ".
            	"           WHERE transactions.paypending = 1".
            	"           AND NOT EXISTS(SELECT withdrawals.transactionid FROM withdrawals WHERE withdrawals.transactionid = transactions.id)".
            	"           AND transactions.accountid = accounts.id".
            	"           AND accounts.currencyid = cur.id) IS NOT NULL, ( ".
            	"           SELECT SUM(transactions.amount + transactions.amountghost) AS amount ".
            	"           FROM transactions, accounts ".
            	"           WHERE transactions.paypending = 1".
            	"           AND NOT EXISTS(SELECT withdrawals.transactionid FROM withdrawals WHERE withdrawals.transactionid = transactions.id)".
            	"           AND transactions.accountid = accounts.id".
            	"           AND accounts.currencyid = cur.id),0)".
            	"       + IF(( ".
            	"           SELECT SUM(ABS(withdrawals.amount) + ABS(withdrawals.fee)) AS amount ".
            	"           FROM transactions, withdrawals, accounts ".
            	"           WHERE transactions.id = withdrawals.transactionid ".
            	"           AND transactions.accountid = accounts.id".
            	"           AND accounts.currencyid = cur.id ".
            	"           AND withdrawals.valid IS NUll ".
            	"           AND withdrawals.rejected IS NULL) IS NOT NULL, ( ".
            	"           SELECT SUM(ABS(withdrawals.amount + withdrawals.fee)) AS amount ".
            	"           FROM transactions, withdrawals, accounts ".
            	"           WHERE transactions.id = withdrawals.transactionid ".
            	"           AND transactions.accountid = accounts.id".
            	"           AND accounts.currencyid = cur.id ".
            	"           AND withdrawals.valid IS NUll ".
            	"           AND withdrawals.rejected IS NULL),0)".
                "       ) LIKE '%".floatval($el)."%'" : (validNumberFormatted($el) ? " OR ( IF((SELECT SUM(transactions.amount) - (IF(( ".
                "           SELECT SUM(CASE WHEN orders.side = 1 THEN orders.quantity ELSE orders.total END) AS amount ".
                "           FROM orders, accounts ".
                "           WHERE orders.accountid = accounts.id".
                "           AND accounts.currencyid = cur.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, accounts ".
                "           WHERE orders.accountid = accounts.id".
                "           AND accounts.currencyid = cur.id ".
            	"           AND orders.canceled IS NULL ".
            	"           AND orders.type != 1), 0)".
                "       + IF(( ".
                "           SELECT SUM(transactions.amount) AS amount ".
                "           FROM transactions, orders, accounts ".
                "           WHERE orders.id = transactions.orderid ".
                "           AND transactions.accountid = accounts.id".
                "           AND orders.accountid = accounts.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, accounts ".
                "           WHERE orders.id = transactions.orderid ".
                "           AND transactions.accountid = accounts.id".
                "           AND orders.accountid = accounts.id".
            	"           AND orders.canceled IS NULL ".
            	"           AND transactions.amount < 0 ".
            	"           AND orders.type != 1),0)) ".
                "        	FROM transactions, accounts ".
                "        	WHERE transactions.accountid = accounts.id ".
                "       	AND accounts.currencyid = cur.id ".
                "       	AND accounts.paymentmethodid = -1 ".
                "       ) IS NOT NULL, (SELECT SUM(transactions.amount) - (IF(( ".
                "           SELECT SUM(CASE WHEN orders.side = 1 THEN orders.quantity ELSE orders.total END) AS amount ".
                "           FROM orders, accounts ".
                "           WHERE orders.accountid = accounts.id".
                "           AND accounts.currencyid = cur.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, accounts ".
                "           WHERE orders.accountid = accounts.id".
                "           AND accounts.currencyid = cur.id ".
            	"           AND orders.canceled IS NULL ".
            	"           AND orders.type != 1), 0)".
                "       + IF(( ".
                "           SELECT SUM(transactions.amount) AS amount ".
                "           FROM transactions, orders, accounts ".
                "           WHERE orders.id = transactions.orderid ".
                "           AND transactions.accountid = accounts.id".
                "           AND orders.accountid = accounts.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, accounts ".
                "           WHERE orders.id = transactions.orderid ".
                "           AND transactions.accountid = accounts.id".
                "           AND orders.accountid = accounts.id".
            	"           AND orders.canceled IS NULL ".
            	"           AND transactions.amount < 0 ".
            	"           AND orders.type != 1),0)) ".
                "        	FROM transactions, accounts ".
                "        	WHERE transactions.accountid = accounts.id ".
                "       	AND accounts.currencyid = cur.id ".
                "       	AND accounts.paymentmethodid = -1 ".
                "       ), 0)) ".
                "       + IF((SELECT SUM(deposits.amount - deposits.fee) ".
                "               FROM accounts, transactions, deposits ".
                "               WHERE transactions.id = deposits.transactionid ".
                "               AND deposits.rejected IS NULL ".
                "               AND deposits.valid IS NULL ".
                "               AND accounts.id = transactions.accountid ".
                "               AND accounts.currencyid = cur.id ".
                "               AND transactions.amountghost >= 0) IS NOT NULL,".
                "               (SELECT SUM(deposits.amount - deposits.fee) ".
                "                   FROM accounts, transactions, deposits ".
                "                   WHERE transactions.id = deposits.transactionid ".
                "                   AND deposits.rejected IS NULL ".
                "                   AND deposits.valid IS NULL ".
                "                   AND accounts.id = transactions.accountid ".
                "                   AND accounts.currencyid = cur.id ".
                "                   AND transactions.amountghost >= 0), ".
                "            0) ".
                "       + (IF(( ".
                "           SELECT SUM(CASE WHEN orders.side = 1 THEN orders.quantity ELSE orders.total END) AS amount ".
                "           FROM orders, accounts ".
                "           WHERE orders.accountid = accounts.id".
                "           AND accounts.currencyid = cur.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, accounts ".
                "           WHERE orders.accountid = accounts.id".
                "           AND accounts.currencyid = cur.id" .
            	"           AND orders.canceled IS NULL ".
            	"           AND orders.type != 1), 0)".
                "       + IF(( ".
                "           SELECT SUM(transactions.amount) AS amount ".
                "           FROM transactions, orders, accounts ".
                "           WHERE orders.id = transactions.orderid ".
                "           AND transactions.accountid = accounts.id".
                "           AND accounts.currencyid = cur.id".
                "           AND orders.accountid = accounts.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, accounts ".
                "           WHERE orders.id = transactions.orderid ".
                "           AND transactions.accountid = accounts.id".
                "           AND accounts.currencyid = cur.id".
                "           AND orders.accountid = accounts.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, accounts ".
            	"           WHERE transactions.paypending = 1".
            	"           AND NOT EXISTS(SELECT withdrawals.transactionid FROM withdrawals WHERE withdrawals.transactionid = transactions.id)".
            	"           AND transactions.accountid = accounts.id".
            	"           AND accounts.currencyid = cur.id) IS NOT NULL, ( ".
            	"           SELECT SUM(transactions.amount + transactions.amountghost) AS amount ".
            	"           FROM transactions, accounts ".
            	"           WHERE transactions.paypending = 1".
            	"           AND NOT EXISTS(SELECT withdrawals.transactionid FROM withdrawals WHERE withdrawals.transactionid = transactions.id)".
            	"           AND transactions.accountid = accounts.id".
            	"           AND accounts.currencyid = cur.id),0)".
            	"       + IF(( ".
            	"           SELECT SUM(ABS(withdrawals.amount) + ABS(withdrawals.fee)) AS amount ".
            	"           FROM transactions, withdrawals, accounts ".
            	"           WHERE transactions.id = withdrawals.transactionid ".
            	"           AND transactions.accountid = accounts.id".
            	"           AND accounts.currencyid = cur.id ".
            	"           AND withdrawals.valid IS NUll ".
            	"           AND withdrawals.rejected IS NULL) IS NOT NULL, ( ".
            	"           SELECT SUM(ABS(withdrawals.amount + withdrawals.fee)) AS amount ".
            	"           FROM transactions, withdrawals, accounts ".
            	"           WHERE transactions.id = withdrawals.transactionid ".
            	"           AND transactions.accountid = accounts.id".
            	"           AND accounts.currencyid = cur.id ".
            	"           AND withdrawals.valid IS NUll ".
            	"           AND withdrawals.rejected IS NULL),0)".
                "       ) LIKE '%".parseFormattedNumber($el)."%'" : ''));
        }
        
        $filter .= " ) ";
    }
    
    //En caso de que el currencyid sea enviado, se construye el query
    if(isset($_GET["currencyid"]) && $_GET["currencyid"] != ""){
        $currencyid = " AND cur.id = ".mysqli_real_escape_string($db, $_GET['currencyid']);
    }
    
    $sql = "SELECT COUNT(cur.id) AS qty".
    "       FROM currencies cur ".
    "       WHERE cur.name IS NOT NULL".
    "       ".$otheraccounts.
    "       ".$filter.
    "       ".$currencyid;
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
        
    $row = $rs->fetch_assoc();
    
    $out->numofrecords = (int)$row['qty'];
    
    $sql = "SELECT cur.id AS currencyid, cur.symbol AS currencysymbol, ".
    "       cur.name AS currencyname, cur.decimals AS currencydecimals, ".
    "       cur.interface AS currencyinterface, ".
    "       (SELECT COUNT(accounts.id) ".
    "        	FROM accounts ".
    "   		WHERE accounts.currencyid = cur.id ".
    "           AND accounts.paymentmethodid = -1 ".
    "       ) AS qtywallets, ".
    "       IF((SELECT SUM(transactions.amount) - ABS(IF(( ".
    "           SELECT SUM(CASE WHEN orders.side = 1 THEN orders.quantity ELSE orders.total END) AS amount ".
    "           FROM orders, accounts ".
    "           WHERE orders.accountid = accounts.id".
    "           AND accounts.currencyid = cur.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, accounts ".
    "           WHERE orders.accountid = accounts.id".
    "           AND accounts.currencyid = cur.id ".
	"           AND orders.canceled IS NULL ".
	"           AND orders.type != 1), 0)".
    "       + IF(( ".
    "           SELECT SUM(transactions.amount) AS amount ".
    "           FROM transactions, orders, accounts ".
    "           WHERE orders.id = transactions.orderid ".
    "           AND transactions.accountid = accounts.id".
    "           AND accounts.currencyid = cur.id ".
    "           AND orders.accountid = accounts.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, accounts ".
    "           WHERE orders.id = transactions.orderid ".
    "           AND transactions.accountid = accounts.id".
    "           AND accounts.currencyid = cur.id ".
    "           AND orders.accountid = accounts.id".
	"           AND orders.canceled IS NULL ".
	"           AND transactions.amount < 0 ".
	"           AND orders.type != 1),0)) ".
    "        	FROM transactions, accounts ".
    "        	WHERE transactions.accountid = accounts.id ".
    "       	AND accounts.currencyid = cur.id ".
    "       	AND accounts.paymentmethodid = -1 ".
    "       ) IS NOT NULL, (SELECT SUM(transactions.amount) - ABS(IF(( ".
    "           SELECT SUM(CASE WHEN orders.side = 1 THEN orders.quantity ELSE orders.total END) AS amount ".
    "           FROM orders, accounts ".
    "           WHERE orders.accountid = accounts.id".
    "           AND accounts.currencyid = cur.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, accounts ".
    "           WHERE orders.accountid = accounts.id".
    "           AND accounts.currencyid = cur.id ".
	"           AND orders.canceled IS NULL ".
	"           AND orders.type != 1), 0)".
    "       + IF(( ".
    "           SELECT SUM(transactions.amount) AS amount ".
    "           FROM transactions, orders, accounts ".
    "           WHERE orders.id = transactions.orderid ".
    "           AND transactions.accountid = accounts.id".
    "           AND accounts.currencyid = cur.id ".
    "           AND orders.accountid = accounts.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, accounts ".
    "           WHERE orders.id = transactions.orderid ".
    "           AND transactions.accountid = accounts.id".
    "           AND accounts.currencyid = cur.id ".
    "           AND orders.accountid = accounts.id".
	"           AND orders.canceled IS NULL ".
	"           AND transactions.amount < 0 ".
	"           AND orders.type != 1),0)) ".
    "        	FROM transactions, accounts ".
    "        	WHERE transactions.accountid = accounts.id ".
    "       	AND accounts.currencyid = cur.id ".
    "       	AND accounts.paymentmethodid = -1 ".
    "       ), 0) AS avaliable, ".
    "       IF((SELECT SUM(deposits.amount - deposits.fee) ".
    "           FROM accounts, transactions, deposits ".
    "           WHERE transactions.id = deposits.transactionid ".
    "           AND deposits.rejected IS NULL ".
    "           AND deposits.valid IS NULL ".
    "           AND accounts.id = transactions.accountid ".
    "           AND accounts.currencyid = cur.id ".
    "           AND transactions.amountghost >= 0 ".
    "       ) IS NOT NULL, (SELECT SUM(deposits.amount - deposits.fee) ".
    "           FROM accounts, transactions, deposits ".
    "           WHERE transactions.id = deposits.transactionid ".
    "           AND deposits.rejected IS NULL ".
    "           AND deposits.valid IS NULL ".
    "           AND accounts.id = transactions.accountid ".
    "           AND accounts.currencyid = cur.id ".
    "           AND transactions.amountghost >= 0 ".
    "       ), 0) AS deferred, ".
    "       (IF(( ".
    "           SELECT SUM(CASE WHEN orders.side = 1 THEN orders.quantity ELSE orders.total END) AS amount ".
    "           FROM orders, accounts ".
    "           WHERE orders.accountid = accounts.id".
    "           AND accounts.currencyid = cur.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, accounts ".
    "           WHERE orders.accountid = accounts.id".
    "           AND accounts.currencyid = cur.id" .
	"           AND orders.canceled IS NULL ".
	"           AND orders.type != 1), 0)".
    "       + IF(( ".
    "           SELECT SUM(transactions.amount) AS amount ".
    "           FROM transactions, orders, accounts ".
    "           WHERE orders.id = transactions.orderid ".
    "           AND transactions.accountid = accounts.id".
    "           AND accounts.currencyid = cur.id".
    "           AND orders.accountid = accounts.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, accounts ".
    "           WHERE orders.id = transactions.orderid ".
    "           AND transactions.accountid = accounts.id".
    "           AND accounts.currencyid = cur.id".
    "           AND orders.accountid = accounts.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, accounts ".
	"           WHERE transactions.paypending = 1".
	"           AND NOT EXISTS(SELECT withdrawals.transactionid FROM withdrawals WHERE withdrawals.transactionid = transactions.id)".
	"           AND transactions.accountid = accounts.id".
	"           AND accounts.currencyid = cur.id) IS NOT NULL, ( ".
	"           SELECT SUM(transactions.amount + transactions.amountghost) AS amount ".
	"           FROM transactions, accounts ".
	"           WHERE transactions.paypending = 1".
	"           AND NOT EXISTS(SELECT withdrawals.transactionid FROM withdrawals WHERE withdrawals.transactionid = transactions.id)".
	"           AND transactions.accountid = accounts.id".
	"           AND accounts.currencyid = cur.id),0)".
	"       + IF(( ".
	"           SELECT SUM(ABS(withdrawals.amount) + ABS(withdrawals.fee)) AS amount ".
	"           FROM transactions, withdrawals, accounts ".
	"           WHERE transactions.id = withdrawals.transactionid ".
	"           AND transactions.accountid = accounts.id".
	"           AND accounts.currencyid = cur.id ".
	"           AND withdrawals.valid IS NUll ".
	"           AND withdrawals.rejected IS NULL) IS NOT NULL, ( ".
	"           SELECT SUM(ABS(withdrawals.amount + withdrawals.fee)) AS amount ".
	"           FROM transactions, withdrawals, accounts ".
	"           WHERE transactions.id = withdrawals.transactionid ".
	"           AND transactions.accountid = accounts.id".
	"           AND accounts.currencyid = cur.id ".
	"           AND withdrawals.valid IS NUll ".
	"           AND withdrawals.rejected IS NULL),0)".
    "       ) AS commited, ".
    "       ( IF((SELECT SUM(transactions.amount) - ABS(IF(( ".
    "           SELECT SUM(CASE WHEN orders.side = 1 THEN orders.quantity ELSE orders.total END) AS amount ".
    "           FROM orders, accounts ".
    "           WHERE orders.accountid = accounts.id".
    "           AND accounts.currencyid = cur.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, accounts ".
    "           WHERE orders.accountid = accounts.id".
    "           AND accounts.currencyid = cur.id ".
	"           AND orders.canceled IS NULL ".
	"           AND orders.type != 1), 0)".
    "       + IF(( ".
    "           SELECT SUM(transactions.amount) AS amount ".
    "           FROM transactions, orders, accounts ".
    "           WHERE orders.id = transactions.orderid ".
    "           AND transactions.accountid = accounts.id".
    "           AND accounts.currencyid = cur.id ".
    "           AND orders.accountid = accounts.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, accounts ".
    "           WHERE orders.id = transactions.orderid ".
    "           AND transactions.accountid = accounts.id".
    "           AND accounts.currencyid = cur.id ".
    "           AND orders.accountid = accounts.id".
	"           AND orders.canceled IS NULL ".
	"           AND transactions.amount < 0 ".
	"           AND orders.type != 1),0)) ".
    "        	FROM transactions, accounts ".
    "        	WHERE transactions.accountid = accounts.id ".
    "       	AND accounts.currencyid = cur.id ".
    "       	AND accounts.paymentmethodid = -1 ".
    "       ) IS NOT NULL, (SELECT SUM(transactions.amount) - ABS(IF(( ".
    "           SELECT SUM(CASE WHEN orders.side = 1 THEN orders.quantity ELSE orders.total END) AS amount ".
    "           FROM orders, accounts ".
    "           WHERE orders.accountid = accounts.id".
    "           AND accounts.currencyid = cur.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, accounts ".
    "           WHERE orders.accountid = accounts.id".
    "           AND accounts.currencyid = cur.id ".
	"           AND orders.canceled IS NULL ".
	"           AND orders.type != 1), 0)".
    "       + IF(( ".
    "           SELECT SUM(transactions.amount) AS amount ".
    "           FROM transactions, orders, accounts ".
    "           WHERE orders.id = transactions.orderid ".
    "           AND transactions.accountid = accounts.id".
    "           AND accounts.currencyid = cur.id ".
    "           AND orders.accountid = accounts.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, accounts ".
    "           WHERE orders.id = transactions.orderid ".
    "           AND transactions.accountid = accounts.id".
    "           AND accounts.currencyid = cur.id ".
    "           AND orders.accountid = accounts.id".
	"           AND orders.canceled IS NULL ".
	"           AND transactions.amount < 0 ".
	"           AND orders.type != 1),0)) ".
    "        	FROM transactions, accounts ".
    "        	WHERE transactions.accountid = accounts.id ".
    "       	AND accounts.currencyid = cur.id ".
    "       	AND accounts.paymentmethodid = -1 ".
    "       ), 0)) ".
    "       + IF((SELECT SUM(deposits.amount - deposits.fee) ".
    "               FROM accounts, transactions, deposits ".
    "               WHERE transactions.id = deposits.transactionid ".
    "               AND deposits.rejected IS NULL ".
    "               AND deposits.valid IS NULL ".
    "               AND accounts.id = transactions.accountid ".
    "               AND accounts.currencyid = cur.id ".
    "               AND transactions.amountghost >= 0) IS NOT NULL,".
    "               (SELECT SUM(deposits.amount - deposits.fee) ".
    "                   FROM accounts, transactions, deposits ".
    "                   WHERE transactions.id = deposits.transactionid ".
    "                   AND deposits.rejected IS NULL ".
    "                   AND deposits.valid IS NULL ".
    "                   AND accounts.id = transactions.accountid ".
    "                   AND accounts.currencyid = cur.id ".
    "                   AND transactions.amountghost >= 0), ".
    "            0) ".
    "       + (IF(( ".
    "           SELECT SUM(CASE WHEN orders.side = 1 THEN orders.quantity ELSE orders.total END) AS amount ".
    "           FROM orders, accounts ".
    "           WHERE orders.accountid = accounts.id".
    "           AND accounts.currencyid = cur.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, accounts ".
    "           WHERE orders.accountid = accounts.id".
    "           AND accounts.currencyid = cur.id" .
	"           AND orders.canceled IS NULL ".
	"           AND orders.type != 1), 0)".
    "       + IF(( ".
    "           SELECT SUM(transactions.amount) AS amount ".
    "           FROM transactions, orders, accounts ".
    "           WHERE orders.id = transactions.orderid ".
    "           AND transactions.accountid = accounts.id".
    "           AND accounts.currencyid = cur.id".
    "           AND orders.accountid = accounts.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, accounts ".
    "           WHERE orders.id = transactions.orderid ".
    "           AND transactions.accountid = accounts.id".
    "           AND accounts.currencyid = cur.id".
    "           AND orders.accountid = accounts.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, accounts ".
	"           WHERE transactions.paypending = 1".
	"           AND NOT EXISTS(SELECT withdrawals.transactionid FROM withdrawals WHERE withdrawals.transactionid = transactions.id)".
	"           AND transactions.accountid = accounts.id".
	"           AND accounts.currencyid = cur.id) IS NOT NULL, ( ".
	"           SELECT SUM(transactions.amount + transactions.amountghost) AS amount ".
	"           FROM transactions, accounts ".
	"           WHERE transactions.paypending = 1".
	"           AND NOT EXISTS(SELECT withdrawals.transactionid FROM withdrawals WHERE withdrawals.transactionid = transactions.id)".
	"           AND transactions.accountid = accounts.id".
	"           AND accounts.currencyid = cur.id),0)".
	"       + IF(( ".
	"           SELECT SUM(ABS(withdrawals.amount) + ABS(withdrawals.fee)) AS amount ".
	"           FROM transactions, withdrawals, accounts ".
	"           WHERE transactions.id = withdrawals.transactionid ".
	"           AND transactions.accountid = accounts.id".
	"           AND accounts.currencyid = cur.id ".
	"           AND withdrawals.valid IS NUll ".
	"           AND withdrawals.rejected IS NULL) IS NOT NULL, ( ".
	"           SELECT SUM(ABS(withdrawals.amount + withdrawals.fee)) AS amount ".
	"           FROM transactions, withdrawals, accounts ".
	"           WHERE transactions.id = withdrawals.transactionid ".
	"           AND transactions.accountid = accounts.id".
	"           AND accounts.currencyid = cur.id ".
	"           AND withdrawals.valid IS NUll ".
	"           AND withdrawals.rejected IS NULL),0)".
    "       ) AS total, ".
    "       (SELECT transactions.datecreated FROM  transactions, accounts ".
    " 	        WHERE accounts.currencyid = cur.id ".
    "          	AND accounts.id = transactions.accountid ".
    "         	AND transactions.datecreated = (SELECT MAX(transactions.datecreated) FROM transactions WHERE accounts.id = transactions.accountid) ".
    "         	ORDER BY transactions.datecreated DESC, transactions.id DESC ".
    "    		LIMIT 1 ".
    "       ) AS lasttx ".
    "       FROM currencies cur".
    "       WHERE cur.name IS NOT NULL".
    "       ".$otheraccounts.
    "       ".$filter.
    "       ".$currencyid.
    "       ".$order.
    "       LIMIT ".$offset.", ".$numofrec;
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
    
    //Ordenamos los datos
    while($row = $rs->fetch_assoc()){
        $getBalanceFail = false;
        $record = new stdClass();
        $record->currency = new stdClass();
        $record->currency->name = $row['currencyname'];
        $record->currency->symbol = $row['currencysymbol'];
        
        $record->accounts = numberFormatt($row['qtywallets'],0);
        $record->avaliable = $row['avaliable'] == null ? numberFormatt(0, $row['currencydecimals']) : numberFormatt($row['avaliable'], $row['currencydecimals']);
        $record->pending = $row['deferred'] == null ? numberFormatt(0, $row['currencydecimals']) : numberFormatt($row['deferred'], $row['currencydecimals']);
        $record->commited = $row['commited'] == null ? numberFormatt(0, $row['currencydecimals']) : numberFormatt($row['commited'], $row['currencydecimals']);
        $record->total = $row['total'] == null ? numberFormatt(0, $row['currencydecimals']) : numberFormatt($row['total'], $row['currencydecimals']);
        $record->last = $row['lasttx'] == null ? '' : date("d/m/Y", strtotime($row["lasttx"]))." - ".date("h:i a", strtotime($row["lasttx"]));
        
        $record->interface = new stdClass();
        $record->interface->id = (int)$row['currencyinterface'];
        $record->interface->balance = new stdClass();
        
        // Validamos la interfaz para determinar la api
        switch($record->interface->id){
            case 4:
                // BTC
                // Dejamos los parametros vacios
                $parms = [];
                $request = array('method'=>'getbalance','parms' => $parms);
                $rest = json_decode(queryServicesBTC($BTC_URL, $request), true);
                
                // Validamos que el error no sea nulo para mandar el mensaje
                if($rest['error'] != null){
                    $getBalanceFail = true;
                    //badEnd('500',array("msg"=>$rest['error']['message']));
                }
                
                if ($getBalanceFail) {
                    $record->interface->balance->amount = 0;
                } else {
                    $record->interface->balance->amount = $rest['result'];
                }
                
                break;
            case 5:
                // Petro
                $balance = 0;
                
                $rest = queryPrivate("accounts", $TCV_JWT, $TCV_URL, $VERSION_API, true);
                
                if($rest['httpres'] != 200){
                    $getBalanceFail = true;
                     // Validamos que el error no sea nulo para mandar el mensaje
                    //badEnd('500',array("msg2"=>$rest['error']['message']));
                }
                
                if ($getBalanceFail) {
                    $record->interface->balance->amount = 0;
                } else {
                    foreach($rest as $item){
                        if(strtolower($item['currency']) == 'ptr'){
                            $balance = $item['balance'];
                        }
                    }
        
                    $record->interface->balance->amount = $balance;
                }
                
                break;
            case 6:
                // USDT
                $request = array('method'=>'getBalances', "address" => $USDT_CENTRAL_WALLET);
                $rest = json_decode(queryServicesUSDT($USDT_URL, $request), true);
                
                if($rest == null){
                    $getBalanceFail = true;
                     // Validamos que el error no sea nulo para mandar el mensaje
                    //badEnd('500',array("msg2"=>$rest['error']['message']));
                }
                
                if ($getBalanceFail) {
                    $record->interface->balance->amount = 0;
                } else {
                    if($record->currency->symbol == 'TRX'){
                        $record->interface->balance->amount = $rest['TRX'] == 0 ? 0 : (float)formattedTrxAmount($rest['TRX']);
                    }else{
                        $record->interface->balance->amount = $rest['USDt'] == 0 ? 0 : (float)formattUsdtAmount($rest['USDt'], 6);
                    }
                    
                    $record->interface->wallet = $USDT_CENTRAL_WALLET;
                }
                
                break;
            case 7:
                // DASH
                
                $parms = array();
                $request = array('method'=>'getbalance','parms' => $parms);
                $rest = json_decode(queryServicesDash($DASH_URL, $request),true);
                
                if($rest == null){
                    $getBalanceFail = true;
                     // Validamos que el error no sea nulo para mandar el mensaje
                    //badEnd('500',array("msg2"=>$rest['error']['message']));
                }
                
                if ($getBalanceFail) {
                    $record->interface->balance->amount = 0;
                } else {
                    $record->interface->balance->amount = $rest['result'];
                }
                
                break;
            default:
                $record->interface->balance->amount = 0;
        }
        
        $record->interface->balance->formatted = $getBalanceFail ? 'Sin conexión' : numberFormatt($record->interface->balance->amount, $row['currencydecimals']);
        
        
        $record->row = $row;
        
        $records [] = $record;
    }
    
    $out->records = $records;
    //Retornamos los privilegios del usuario en este modulo
    $access = new stdClass();
    $access->read = tienePrivilegio($db, $sessionid, 36);
    $out->privileges = $access;
    
    header("HTTP/1.1 200");
    echo (json_encode($out));
    die();
    
    /*  Funcion para determinar el orden de los resultados, donde:
    *   0 = Ultima transferencia
    *   1 = Cantidad cuentas ASCENDENTE
    *   2 = Saldo disponible ASCENDENTE
    *   3 = Saldo pendiente ASCENDENTE
    *   4 = Saldo total ASCENDENTE
    *   5 = Ultima operacion ASCENDENTE
    *   -1 = Cantidad cuentas DESCENTE
    *   -2 = Saldo disponible DESCENTE
    *   -3 = Saldo pendiente DESCENTE
    *   -4 = Saldo total DESCENTE
    *   -5 = Ultima operacion DESCENDENTE
    *   Y si el order es enviado si ser algunos de estos valores,
    */
    function determinateOrder($order){
        $orderres = "";
        switch($order){
            case 0:
                $orderres = "ORDER BY lasttx DESC, cur.id DESC";
                break;
            case 1:
                $orderres = "ORDER BY qtywallets ASC";
                break;
            case 2:
                $orderres = "ORDER BY avaliable ASC";
                break;
            case 3:
                $orderres = "ORDER BY deferred ASC";
                break;
            case 4:
                $orderres = "ORDER BY total ASC";
                break;
            case 5:
                $orderres = "ORDER BY lasttx ASC, cur.id DESC";
                break;
            case -1:
                $orderres = "ORDER BY qtywallets DESC";
                break;
            case -2:
                $orderres = "ORDER BY avaliable DESC";
                break;
            case -3:
                $orderres = "ORDER BY deferred DESC";
                break;
            case -4:
                $orderres = "ORDER BY total DESC";
                break;
            case -5:
                $orderres = "ORDER BY lasttx DESC, cur.id DESC";
                break;
            default:
                $orderres = "ORDER BY lasttx DESC, cur.id DESC";
                break;
        }
        
        return $orderres;
    }
    
    function formattedTrxAmount($value){
        $amount = 0;
        
        if($value > 0){
            $amount = $value / 1000000;
        }
        
        return $amount;
    }
    
    function formattUsdtAmount($value, $decimals){
        $amount = '';
        
        $begin = substr($value, 0, strlen($value)-$decimals);
        $end = substr($value, strlen($value)-$decimals);
        $amount = $begin.".".$end;
        
        return $amount;
    }
?>
