<?php
// cms/api/balances/users

    header("Content-Type:application/json");
    include("../dbconn.php");
    include("../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 = $_GET["sessionid"];
    $offset = $_GET['offset'];
    $numofrec = $_GET['numofrec'];
    $filter = "";
    $filterPerson = "";
    $filterCompany = "";
    $currencyid = "";
    $order = determinateOrder($_GET["order"]);
    
    //Validamos que la session del usuario sea valida para continuar
    isSessionValidCMS($db, $sessionid);
    
    //Validamos que el usuario tenga privilegios
    if(!tienePrivilegio($db, $sessionid, 43))
        badEnd("403", array("msg"=>"No tiene Privilegios"));
        
    //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 ( ";
        $filterPerson = " AND ( ";
        $filterCompany = " AND ( ";
        
        //Separamos el filtro por el simbolo +
        $filterEl = separateFilter($_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));
            }
            
            if($index == 0){
                $filterPerson .= " users.usr LIKE '%".$el."%' ".
                "               OR cur.symbol LIKE '".$el."%' ".
                ( !$dateAndHour ? " OR (SELECT DATE(transactions.datecreated) FROM  transactions ".
                " 	            WHERE acc.id = transactions.accountid ".
                "         	    AND transactions.datecreated = (SELECT MAX(transactions.datecreated) FROM transactions WHERE acc.id = transactions.accountid) ".
                "         	    ORDER BY transactions.datecreated DESC, transactions.id DESC ".
                "    		    LIMIT 1 ".
                "           ) = DATE('".$dateel."') " : '').
                ( $dateAndHour ? " OR  (SELECT DATE_FORMAT(transactions.datecreated,'%Y-%m-%d %l:%i') FROM  transactions".
                " 	            WHERE acc.id = transactions.accountid ".
                "         	    AND transactions.datecreated = (SELECT MAX(transactions.datecreated) FROM transactions WHERE acc.id = transactions.accountid) ".
                "         	    ORDER BY transactions.datecreated DESC, transactions.id DESC ".
                "    		    LIMIT 1 ".
                "           ) = DATE_FORMAT('".$dateelhour."','%Y-%m-%d %l:%i') " : '').
                "           ".( validNumber($el) ? " OR IF((SELECT SUM(transactions.amount) ".
                "            	FROM transactions ".
                "            	WHERE transactions.accountid = acc.id) IS NOT NULL, ".
                "               ROUND((SELECT SUM(transactions.amount) ".
                "                	FROM transactions ".
                "                	WHERE transactions.accountid = acc.id), cur.decimals),".
                "               0) = ".floatval($el) : (validNumberFormatted($el) ? " OR IF((SELECT SUM(transactions.amount) ".
                "            	FROM transactions ".
                "            	WHERE transactions.accountid = acc.id) IS NOT NULL, ".
                "               ROUND((SELECT SUM(transactions.amount) ".
                "                	FROM transactions ".
                "                	WHERE transactions.accountid = acc.id), cur.decimals),".
                "               0) LIKE '%".parseFormattedNumber($el)."%'" : '')).
                "           ".( validNumber($el) ? " OR IF((SELECT SUM(deposits.amount - deposits.fee) ".
                "               FROM transactions, deposits ".
                "               WHERE transactions.id = deposits.transactionid ".
                "               AND deposits.rejected IS NULL ".
                "               AND deposits.valid IS NULL ".
                "               AND acc.id = transactions.accountid ".
                "               AND transactions.amountghost >= 0) IS NOT NULL,".
                "               ROUND((SELECT SUM(deposits.amount - deposits.fee) ".
                "                   FROM transactions, deposits ".
                "                   WHERE transactions.id = deposits.transactionid ".
                "                   AND deposits.rejected IS NULL ".
                "                   AND deposits.valid IS NULL ".
                "                   AND acc.id = transactions.accountid ".
                "                   AND transactions.amountghost >= 0), cur.decimals), ".
                "               0) LIKE '%".floatval($el)."%'" : (validNumberFormatted($el) ? " OR IF((SELECT SUM(deposits.amount - deposits.fee) ".
                "               FROM transactions, deposits ".
                "               WHERE transactions.id = deposits.transactionid ".
                "               AND deposits.rejected IS NULL ".
                "               AND deposits.valid IS NULL ".
                "               AND acc.id = transactions.accountid ".
                "               AND transactions.amountghost >= 0) IS NOT NULL,".
                "               ROUND((SELECT SUM(deposits.amount - deposits.fee) ".
                "                   FROM transactions, deposits ".
                "                   WHERE transactions.id = deposits.transactionid ".
                "                   AND deposits.rejected IS NULL ".
                "                   AND deposits.valid IS NULL ".
                "                   AND acc.id = transactions.accountid ".
                "                   AND transactions.amountghost >= 0), cur.decimals), ".
                "               0) LIKE '%".parseFormattedNumber($el)."%'" : '')).
                "               ".( validNumber($el) ? " OR ( IF((SELECT SUM(transactions.amount) ".
                "          	        FROM transactions ".
                "          	        WHERE transactions.accountid = acc.id) IS NOT NULL, ".
                "                   ROUND((SELECT SUM(transactions.amount) ".
                "                     	FROM transactions ".
                "                      	WHERE transactions.accountid = acc.id), cur.decimals), ".
                "                   0) ".
                "                   + IF((SELECT SUM(deposits.amount - deposits.fee) ".
                "                       FROM transactions, deposits ".
                "                       WHERE transactions.id = deposits.transactionid ".
                "                       AND deposits.rejected IS NULL ".
                "                       AND deposits.valid IS NULL ".
                "                       AND acc.id = transactions.accountid ".
                "                       AND transactions.amountghost >= 0) IS NOT NULL, ".
                "                       ROUND((SELECT SUM(deposits.amount - deposits.fee) ".
                "                           FROM transactions, deposits ".
                "                           WHERE transactions.id = deposits.transactionid ".
                "                           AND deposits.rejected IS NULL ".
                "                           AND deposits.valid IS NULL ".
                "                           AND acc.id = transactions.accountid ".
                "                           AND transactions.amountghost >= 0), cur.decimals), ".
                "                       0)) LIKE '%".floatval($el)."%'" : (validNumberFormatted($el) ? " OR ( IF((SELECT SUM(transactions.amount) ".
                "          	        FROM transactions ".
                "          	        WHERE transactions.accountid = acc.id) IS NOT NULL, ".
                "                   ROUND((SELECT SUM(transactions.amount) ".
                "                     	FROM transactions ".
                "                      	WHERE transactions.accountid = acc.id), cur.decimals), ".
                "                   0) ".
                "                   + IF((SELECT SUM(deposits.amount - deposits.fee) ".
                "                       FROM transactions, deposits ".
                "                       WHERE transactions.id = deposits.transactionid ".
                "                       AND deposits.rejected IS NULL ".
                "                       AND deposits.valid IS NULL ".
                "                       AND acc.id = transactions.accountid ".
                "                       AND transactions.amountghost >= 0) IS NOT NULL, ".
                "                       ROUND((SELECT SUM(deposits.amount - deposits.fee) ".
                "                           FROM transactions, deposits ".
                "                           WHERE transactions.id = deposits.transactionid ".
                "                           AND deposits.rejected IS NULL ".
                "                           AND deposits.valid IS NULL ".
                "                           AND acc.id = transactions.accountid ".
                "                           AND transactions.amountghost >= 0), cur.decimals), ".
                "                       0)) LIKE '%".parseFormattedNumber($el)."%'" : '') ).
                "               OR userpersons.sname LIKE '%".$el."%' ";
                
                $filterCompany .= " users.usr LIKE '%".$el."%' ".
                "               OR cur.symbol LIKE '".$el."%' ".
                ( !$dateAndHour ? " OR (SELECT DATE(transactions.datecreated) FROM  transactions ".
                " 	            WHERE acc.id = transactions.accountid ".
                "         	    AND transactions.datecreated = (SELECT MAX(transactions.datecreated) FROM transactions WHERE acc.id = transactions.accountid) ".
                "         	    ORDER BY transactions.datecreated DESC, transactions.id DESC ".
                "    		    LIMIT 1 ".
                "           ) = DATE('".$dateel."') " : '').
                ( $dateAndHour ? " OR  (SELECT DATE_FORMAT(transactions.datecreated,'%Y-%m-%d %l:%i') FROM  transactions".
                " 	            WHERE acc.id = transactions.accountid ".
                "         	    AND transactions.datecreated = (SELECT MAX(transactions.datecreated) FROM transactions WHERE acc.id = transactions.accountid) ".
                "         	    ORDER BY transactions.datecreated DESC, transactions.id DESC ".
                "    		    LIMIT 1 ".
                "           ) = DATE_FORMAT('".$dateelhour."','%Y-%m-%d %l:%i') " : '').
                "           ".( validNumber($el) ? " OR IF((SELECT SUM(transactions.amount) ".
                "            	FROM transactions ".
                "            	WHERE transactions.accountid = acc.id) IS NOT NULL, ".
                "               ROUND((SELECT SUM(transactions.amount) ".
                "                	FROM transactions ".
                "                	WHERE transactions.accountid = acc.id), cur.decimals),".
                "               0) LIKE '%".floatval($el)."%'" : (validNumberFormatted($el) ? " OR IF((SELECT SUM(transactions.amount) ".
                "            	FROM transactions ".
                "            	WHERE transactions.accountid = acc.id) IS NOT NULL, ".
                "               ROUND((SELECT SUM(transactions.amount) ".
                "                	FROM transactions ".
                "                	WHERE transactions.accountid = acc.id), cur.decimals),".
                "               0) LIKE '%".parseFormattedNumber($el)."%'" : '')).
                "           ".( validNumber($el) ? " OR IF((SELECT SUM(deposits.amount - deposits.fee) ".
                "               FROM transactions, deposits ".
                "               WHERE transactions.id = deposits.transactionid ".
                "               AND deposits.rejected IS NULL ".
                "               AND deposits.valid IS NULL ".
                "               AND acc.id = transactions.accountid ".
                "               AND transactions.amountghost >= 0) IS NOT NULL,".
                "               ROUND((SELECT SUM(deposits.amount - deposits.fee) ".
                "                   FROM transactions, deposits ".
                "                   WHERE transactions.id = deposits.transactionid ".
                "                   AND deposits.rejected IS NULL ".
                "                   AND deposits.valid IS NULL ".
                "                   AND acc.id = transactions.accountid ".
                "                   AND transactions.amountghost >= 0), cur.decimals), ".
                "               0) LIKE '%".floatval($el)."%'" : (validNumberFormatted($el) ? " OR IF((SELECT SUM(deposits.amount - deposits.fee) ".
                "               FROM transactions, deposits ".
                "               WHERE transactions.id = deposits.transactionid ".
                "               AND deposits.rejected IS NULL ".
                "               AND deposits.valid IS NULL ".
                "               AND acc.id = transactions.accountid ".
                "               AND transactions.amountghost >= 0) IS NOT NULL,".
                "               ROUND((SELECT SUM(deposits.amount - deposits.fee) ".
                "                   FROM transactions, deposits ".
                "                   WHERE transactions.id = deposits.transactionid ".
                "                   AND deposits.rejected IS NULL ".
                "                   AND deposits.valid IS NULL ".
                "                   AND acc.id = transactions.accountid ".
                "                   AND transactions.amountghost >= 0), cur.decimals), ".
                "               0) LIKE '%".parseFormattedNumber($el)."%'" : '')).
                "               ".( validNumber($el) ? " OR ( IF((SELECT SUM(transactions.amount) ".
                "          	        FROM transactions ".
                "          	        WHERE transactions.accountid = acc.id) IS NOT NULL, ".
                "                   ROUND((SELECT SUM(transactions.amount) ".
                "                     	FROM transactions ".
                "                      	WHERE transactions.accountid = acc.id), cur.decimals), ".
                "                   0) ".
                "                   + IF((SELECT SUM(deposits.amount - deposits.fee) ".
                "                       FROM transactions, deposits ".
                "                       WHERE transactions.id = deposits.transactionid ".
                "                       AND deposits.rejected IS NULL ".
                "                       AND deposits.valid IS NULL ".
                "                       AND acc.id = transactions.accountid ".
                "                       AND transactions.amountghost >= 0) IS NOT NULL, ".
                "                       ROUND((SELECT SUM(deposits.amount - deposits.fee) ".
                "                           FROM transactions, deposits ".
                "                           WHERE transactions.id = deposits.transactionid ".
                "                           AND deposits.rejected IS NULL ".
                "                           AND deposits.valid IS NULL ".
                "                           AND acc.id = transactions.accountid ".
                "                           AND transactions.amountghost >= 0), cur.decimals), ".
                "                       0)) LIKE '%".floatval($el)."%'" : (validNumberFormatted($el) ? " OR ( IF((SELECT SUM(transactions.amount) ".
                "          	        FROM transactions ".
                "          	        WHERE transactions.accountid = acc.id) IS NOT NULL, ".
                "                   ROUND((SELECT SUM(transactions.amount) ".
                "                     	FROM transactions ".
                "                      	WHERE transactions.accountid = acc.id), cur.decimals), ".
                "                   0) ".
                "                   + IF((SELECT SUM(deposits.amount - deposits.fee) ".
                "                       FROM transactions, deposits ".
                "                       WHERE transactions.id = deposits.transactionid ".
                "                       AND deposits.rejected IS NULL ".
                "                       AND deposits.valid IS NULL ".
                "                       AND acc.id = transactions.accountid ".
                "                       AND transactions.amountghost >= 0) IS NOT NULL, ".
                "                       ROUND((SELECT SUM(deposits.amount - deposits.fee) ".
                "                           FROM transactions, deposits ".
                "                           WHERE transactions.id = deposits.transactionid ".
                "                           AND deposits.rejected IS NULL ".
                "                           AND deposits.valid IS NULL ".
                "                           AND acc.id = transactions.accountid ".
                "                           AND transactions.amountghost >= 0), cur.decimals), ".
                "                       0)) LIKE '%".parseFormattedNumber($el)."%'" : '') ).
                "               OR usercompanies.name LIKE '%".$el."%' ";
                
                $filter .= " users.usr LIKE '%".$el."%' ".
                "           OR cur.symbol LIKE '".$el."%' ".
                ( !$dateAndHour ? " OR (SELECT DATE(transactions.datecreated) FROM  transactions ".
                " 	            WHERE acc.id = transactions.accountid ".
                "         	    AND transactions.datecreated = (SELECT MAX(transactions.datecreated) FROM transactions WHERE acc.id = transactions.accountid) ".
                "         	    ORDER BY transactions.datecreated DESC, transactions.id DESC ".
                "    		    LIMIT 1 ".
                "           ) = DATE('".$dateel."') " : '').
                ( $dateAndHour ? " OR  (SELECT DATE_FORMAT(transactions.datecreated,'%Y-%m-%d %l:%i') FROM  transactions".
                " 	            WHERE acc.id = transactions.accountid ".
                "         	    AND transactions.datecreated = (SELECT MAX(transactions.datecreated) FROM transactions WHERE acc.id = transactions.accountid) ".
                "         	    ORDER BY transactions.datecreated DESC, transactions.id DESC ".
                "    		    LIMIT 1 ".
                "           ) = DATE_FORMAT('".$dateelhour."','%Y-%m-%d %l:%i') " : '').
                "           ".( validNumber($el) ? " OR IF((SELECT SUM(transactions.amount) ".
                "            	FROM transactions ".
                "            	WHERE transactions.accountid = acc.id) IS NOT NULL, ".
                "               ROUND((SELECT SUM(transactions.amount) ".
                "                	FROM transactions ".
                "                	WHERE transactions.accountid = acc.id), cur.decimals),".
                "               0) LIKE '%".floatval($el)."%'" : (validNumberFormatted($el) ? " OR IF((SELECT SUM(transactions.amount) ".
                "            	FROM transactions ".
                "            	WHERE transactions.accountid = acc.id) IS NOT NULL, ".
                "               ROUND((SELECT SUM(transactions.amount) ".
                "                	FROM transactions ".
                "                	WHERE transactions.accountid = acc.id), cur.decimals),".
                "               0) LIKE '%".parseFormattedNumber($el)."%'" : '')).
                "           ".( validNumber($el) ? " OR IF((SELECT SUM(deposits.amount - deposits.fee) ".
                "               FROM transactions, deposits ".
                "               WHERE transactions.id = deposits.transactionid ".
                "               AND deposits.rejected IS NULL ".
                "               AND deposits.valid IS NULL ".
                "               AND acc.id = transactions.accountid ".
                "               AND transactions.amountghost >= 0) IS NOT NULL,".
                "               ROUND((SELECT SUM(deposits.amount - deposits.fee) ".
                "                   FROM transactions, deposits ".
                "                   WHERE transactions.id = deposits.transactionid ".
                "                   AND deposits.rejected IS NULL ".
                "                   AND deposits.valid IS NULL ".
                "                   AND acc.id = transactions.accountid ".
                "                   AND transactions.amountghost >= 0), cur.decimals), ".
                "               0) LIKE '%".floatval($el)."%'" : (validNumberFormatted($el) ? " OR IF((SELECT SUM(deposits.amount - deposits.fee) ".
                "               FROM transactions, deposits ".
                "               WHERE transactions.id = deposits.transactionid ".
                "               AND deposits.rejected IS NULL ".
                "               AND deposits.valid IS NULL ".
                "               AND acc.id = transactions.accountid ".
                "               AND transactions.amountghost >= 0) IS NOT NULL,".
                "               ROUND((SELECT SUM(deposits.amount - deposits.fee) ".
                "                   FROM transactions, deposits ".
                "                   WHERE transactions.id = deposits.transactionid ".
                "                   AND deposits.rejected IS NULL ".
                "                   AND deposits.valid IS NULL ".
                "                   AND acc.id = transactions.accountid ".
                "                   AND transactions.amountghost >= 0), cur.decimals), ".
                "               0) LIKE '%".parseFormattedNumber($el)."%'" : '')).
                "               ".( validNumber($el) ? " OR ( IF((SELECT SUM(transactions.amount) ".
                "          	        FROM transactions ".
                "          	        WHERE transactions.accountid = acc.id) IS NOT NULL, ".
                "                   ROUND((SELECT SUM(transactions.amount) ".
                "                     	FROM transactions ".
                "                      	WHERE transactions.accountid = acc.id), cur.decimals), ".
                "                   0) ".
                "                   + IF((SELECT SUM(deposits.amount - deposits.fee) ".
                "                       FROM transactions, deposits ".
                "                       WHERE transactions.id = deposits.transactionid ".
                "                       AND deposits.rejected IS NULL ".
                "                       AND deposits.valid IS NULL ".
                "                       AND acc.id = transactions.accountid ".
                "                       AND transactions.amountghost >= 0) IS NOT NULL, ".
                "                       ROUND((SELECT SUM(deposits.amount - deposits.fee) ".
                "                           FROM transactions, deposits ".
                "                           WHERE transactions.id = deposits.transactionid ".
                "                           AND deposits.rejected IS NULL ".
                "                           AND deposits.valid IS NULL ".
                "                           AND acc.id = transactions.accountid ".
                "                           AND transactions.amountghost >= 0), cur.decimals), ".
                "                       0)) LIKE '%".floatval($el)."%'" : (validNumberFormatted($el) ? " OR ( IF((SELECT SUM(transactions.amount) ".
                "          	        FROM transactions ".
                "          	        WHERE transactions.accountid = acc.id) IS NOT NULL, ".
                "                   ROUND((SELECT SUM(transactions.amount) ".
                "                     	FROM transactions ".
                "                      	WHERE transactions.accountid = acc.id), cur.decimals), ".
                "                   0) ".
                "                   + IF((SELECT SUM(deposits.amount - deposits.fee) ".
                "                       FROM transactions, deposits ".
                "                       WHERE transactions.id = deposits.transactionid ".
                "                       AND deposits.rejected IS NULL ".
                "                       AND deposits.valid IS NULL ".
                "                       AND acc.id = transactions.accountid ".
                "                       AND transactions.amountghost >= 0) IS NOT NULL, ".
                "                       ROUND((SELECT SUM(deposits.amount - deposits.fee) ".
                "                           FROM transactions, deposits ".
                "                           WHERE transactions.id = deposits.transactionid ".
                "                           AND deposits.rejected IS NULL ".
                "                           AND deposits.valid IS NULL ".
                "                           AND acc.id = transactions.accountid ".
                "                           AND transactions.amountghost >= 0), cur.decimals), ".
                "                       0)) LIKE '%".parseFormattedNumber($el)."%'" : '') ).
                "               OR userpersons.sname LIKE '%".$el."%' ".
                "               OR usercompanies.name LIKE '%".$el."%' ";
            }else{
                
                $filterPerson .= " OR users.usr LIKE '%".$el."%' ".
                "               OR cur.symbol LIKE '".$el."%' ".
                ( !$dateAndHour ? " OR (SELECT DATE(transactions.datecreated) FROM  transactions ".
                " 	            WHERE acc.id = transactions.accountid ".
                "         	    AND transactions.datecreated = (SELECT MAX(transactions.datecreated) FROM transactions WHERE acc.id = transactions.accountid) ".
                "         	    ORDER BY transactions.datecreated DESC, transactions.id DESC ".
                "    		    LIMIT 1 ".
                "           ) = DATE('".$dateel."') " : '').
                ( $dateAndHour ? " OR  (SELECT DATE_FORMAT(transactions.datecreated,'%Y-%m-%d %l:%i') FROM  transactions".
                " 	            WHERE acc.id = transactions.accountid ".
                "         	    AND transactions.datecreated = (SELECT MAX(transactions.datecreated) FROM transactions WHERE acc.id = transactions.accountid) ".
                "         	    ORDER BY transactions.datecreated DESC, transactions.id DESC ".
                "    		    LIMIT 1 ".
                "           ) = DATE_FORMAT('".$dateelhour."','%Y-%m-%d %l:%i') " : '').
                "           ".( validNumber($el) ? " OR IF((SELECT SUM(transactions.amount) ".
                "            	FROM transactions ".
                "            	WHERE transactions.accountid = acc.id) IS NOT NULL, ".
                "               ROUND((SELECT SUM(transactions.amount) ".
                "                	FROM transactions ".
                "                	WHERE transactions.accountid = acc.id), cur.decimals),".
                "               0) LIKE '%".floatval($el)."%'" : (validNumberFormatted($el) ? " OR IF((SELECT SUM(transactions.amount) ".
                "            	FROM transactions ".
                "            	WHERE transactions.accountid = acc.id) IS NOT NULL, ".
                "               ROUND((SELECT SUM(transactions.amount) ".
                "                	FROM transactions ".
                "                	WHERE transactions.accountid = acc.id), cur.decimals),".
                "               0) LIKE '%".parseFormattedNumber($el)."%'" : '')).
                "           ".( validNumber($el) ? " OR IF((SELECT SUM(deposits.amount - deposits.fee) ".
                "               FROM transactions, deposits ".
                "               WHERE transactions.id = deposits.transactionid ".
                "               AND deposits.rejected IS NULL ".
                "               AND deposits.valid IS NULL ".
                "               AND acc.id = transactions.accountid ".
                "               AND transactions.amountghost >= 0) IS NOT NULL,".
                "               ROUND((SELECT SUM(deposits.amount - deposits.fee) ".
                "                   FROM transactions, deposits ".
                "                   WHERE transactions.id = deposits.transactionid ".
                "                   AND deposits.rejected IS NULL ".
                "                   AND deposits.valid IS NULL ".
                "                   AND acc.id = transactions.accountid ".
                "                   AND transactions.amountghost >= 0), cur.decimals), ".
                "               0) LIKE '%".floatval($el)."%'" : (validNumberFormatted($el) ? " OR IF((SELECT SUM(deposits.amount - deposits.fee) ".
                "               FROM transactions, deposits ".
                "               WHERE transactions.id = deposits.transactionid ".
                "               AND deposits.rejected IS NULL ".
                "               AND deposits.valid IS NULL ".
                "               AND acc.id = transactions.accountid ".
                "               AND transactions.amountghost >= 0) IS NOT NULL,".
                "               ROUND((SELECT SUM(deposits.amount - deposits.fee) ".
                "                   FROM transactions, deposits ".
                "                   WHERE transactions.id = deposits.transactionid ".
                "                   AND deposits.rejected IS NULL ".
                "                   AND deposits.valid IS NULL ".
                "                   AND acc.id = transactions.accountid ".
                "                   AND transactions.amountghost >= 0), cur.decimals), ".
                "               0) LIKE '%".parseFormattedNumber($el)."%'" : '')).
                "               ".( validNumber($el) ? " OR ( IF((SELECT SUM(transactions.amount) ".
                "          	        FROM transactions ".
                "          	        WHERE transactions.accountid = acc.id) IS NOT NULL, ".
                "                   ROUND((SELECT SUM(transactions.amount) ".
                "                     	FROM transactions ".
                "                      	WHERE transactions.accountid = acc.id), cur.decimals), ".
                "                   0) ".
                "                   + IF((SELECT SUM(deposits.amount - deposits.fee) ".
                "                       FROM transactions, deposits ".
                "                       WHERE transactions.id = deposits.transactionid ".
                "                       AND deposits.rejected IS NULL ".
                "                       AND deposits.valid IS NULL ".
                "                       AND acc.id = transactions.accountid ".
                "                       AND transactions.amountghost >= 0) IS NOT NULL, ".
                "                       ROUND((SELECT SUM(deposits.amount - deposits.fee) ".
                "                           FROM transactions, deposits ".
                "                           WHERE transactions.id = deposits.transactionid ".
                "                           AND deposits.rejected IS NULL ".
                "                           AND deposits.valid IS NULL ".
                "                           AND acc.id = transactions.accountid ".
                "                           AND transactions.amountghost >= 0), cur.decimals), ".
                "                       0)) LIKE '%".floatval($el)."%'" : (validNumberFormatted($el) ? " OR ( IF((SELECT SUM(transactions.amount) ".
                "          	        FROM transactions ".
                "          	        WHERE transactions.accountid = acc.id) IS NOT NULL, ".
                "                   ROUND((SELECT SUM(transactions.amount) ".
                "                     	FROM transactions ".
                "                      	WHERE transactions.accountid = acc.id), cur.decimals), ".
                "                   0) ".
                "                   + IF((SELECT SUM(deposits.amount - deposits.fee) ".
                "                       FROM transactions, deposits ".
                "                       WHERE transactions.id = deposits.transactionid ".
                "                       AND deposits.rejected IS NULL ".
                "                       AND deposits.valid IS NULL ".
                "                       AND acc.id = transactions.accountid ".
                "                       AND transactions.amountghost >= 0) IS NOT NULL, ".
                "                       ROUND((SELECT SUM(deposits.amount - deposits.fee) ".
                "                           FROM transactions, deposits ".
                "                           WHERE transactions.id = deposits.transactionid ".
                "                           AND deposits.rejected IS NULL ".
                "                           AND deposits.valid IS NULL ".
                "                           AND acc.id = transactions.accountid ".
                "                           AND transactions.amountghost >= 0), cur.decimals), ".
                "                       0)) LIKE '%".parseFormattedNumber($el)."%'" : '') ).
                "               OR userpersons.sname LIKE '%".$el."%' ";
                
                $filterCompany .= " OR users.usr LIKE '%".$el."%' ".
                "               OR cur.symbol LIKE '".$el."%' ".
                ( !$dateAndHour ? " OR (SELECT DATE(transactions.datecreated) FROM  transactions ".
                " 	            WHERE acc.id = transactions.accountid ".
                "         	    AND transactions.datecreated = (SELECT MAX(transactions.datecreated) FROM transactions WHERE acc.id = transactions.accountid) ".
                "         	    ORDER BY transactions.datecreated DESC, transactions.id DESC ".
                "    		    LIMIT 1 ".
                "           ) = DATE('".$dateel."') " : '').
                ( $dateAndHour ? " OR  (SELECT DATE_FORMAT(transactions.datecreated,'%Y-%m-%d %l:%i') FROM  transactions".
                " 	            WHERE acc.id = transactions.accountid ".
                "         	    AND transactions.datecreated = (SELECT MAX(transactions.datecreated) FROM transactions WHERE acc.id = transactions.accountid) ".
                "         	    ORDER BY transactions.datecreated DESC, transactions.id DESC ".
                "    		    LIMIT 1 ".
                "           ) = DATE_FORMAT('".$dateelhour."','%Y-%m-%d %l:%i') " : '').
                "           ".( validNumber($el) ? " OR IF((SELECT SUM(transactions.amount) ".
                "            	FROM transactions ".
                "            	WHERE transactions.accountid = acc.id) IS NOT NULL, ".
                "               ROUND((SELECT SUM(transactions.amount) ".
                "                	FROM transactions ".
                "                	WHERE transactions.accountid = acc.id), cur.decimals),".
                "               0) LIKE '%".floatval($el)."%'" : (validNumberFormatted($el) ? " OR IF((SELECT SUM(transactions.amount) ".
                "            	FROM transactions ".
                "            	WHERE transactions.accountid = acc.id) IS NOT NULL, ".
                "               ROUND((SELECT SUM(transactions.amount) ".
                "                	FROM transactions ".
                "                	WHERE transactions.accountid = acc.id), cur.decimals),".
                "               0) LIKE '%".parseFormattedNumber($el)."%'" : '')).
                "           ".( validNumber($el) ? " OR IF((SELECT SUM(deposits.amount - deposits.fee) ".
                "               FROM transactions, deposits ".
                "               WHERE transactions.id = deposits.transactionid ".
                "               AND deposits.rejected IS NULL ".
                "               AND deposits.valid IS NULL ".
                "               AND acc.id = transactions.accountid ".
                "               AND transactions.amountghost >= 0) IS NOT NULL,".
                "               ROUND((SELECT SUM(deposits.amount - deposits.fee) ".
                "                   FROM transactions, deposits ".
                "                   WHERE transactions.id = deposits.transactionid ".
                "                   AND deposits.rejected IS NULL ".
                "                   AND deposits.valid IS NULL ".
                "                   AND acc.id = transactions.accountid ".
                "                   AND transactions.amountghost >= 0), cur.decimals), ".
                "               0) LIKE '%".floatval($el)."%'" : (validNumberFormatted($el) ? " OR IF((SELECT SUM(deposits.amount - deposits.fee) ".
                "               FROM transactions, deposits ".
                "               WHERE transactions.id = deposits.transactionid ".
                "               AND deposits.rejected IS NULL ".
                "               AND deposits.valid IS NULL ".
                "               AND acc.id = transactions.accountid ".
                "               AND transactions.amountghost >= 0) IS NOT NULL,".
                "               ROUND((SELECT SUM(deposits.amount - deposits.fee) ".
                "                   FROM transactions, deposits ".
                "                   WHERE transactions.id = deposits.transactionid ".
                "                   AND deposits.rejected IS NULL ".
                "                   AND deposits.valid IS NULL ".
                "                   AND acc.id = transactions.accountid ".
                "                   AND transactions.amountghost >= 0), cur.decimals), ".
                "               0) LIKE '%".parseFormattedNumber($el)."%'" : '')).
                "               ".( validNumber($el) ? " OR ( IF((SELECT SUM(transactions.amount) ".
                "          	        FROM transactions ".
                "          	        WHERE transactions.accountid = acc.id) IS NOT NULL, ".
                "                   ROUND((SELECT SUM(transactions.amount) ".
                "                     	FROM transactions ".
                "                      	WHERE transactions.accountid = acc.id), cur.decimals), ".
                "                   0) ".
                "                   + IF((SELECT SUM(deposits.amount - deposits.fee) ".
                "                       FROM transactions, deposits ".
                "                       WHERE transactions.id = deposits.transactionid ".
                "                       AND deposits.rejected IS NULL ".
                "                       AND deposits.valid IS NULL ".
                "                       AND acc.id = transactions.accountid ".
                "                       AND transactions.amountghost >= 0) IS NOT NULL, ".
                "                       ROUND((SELECT SUM(deposits.amount - deposits.fee) ".
                "                           FROM transactions, deposits ".
                "                           WHERE transactions.id = deposits.transactionid ".
                "                           AND deposits.rejected IS NULL ".
                "                           AND deposits.valid IS NULL ".
                "                           AND acc.id = transactions.accountid ".
                "                           AND transactions.amountghost >= 0), cur.decimals), ".
                "                       0)) LIKE '%".floatval($el)."%'" : (validNumberFormatted($el) ? " OR ( IF((SELECT SUM(transactions.amount) ".
                "          	        FROM transactions ".
                "          	        WHERE transactions.accountid = acc.id) IS NOT NULL, ".
                "                   ROUND((SELECT SUM(transactions.amount) ".
                "                     	FROM transactions ".
                "                      	WHERE transactions.accountid = acc.id), cur.decimals), ".
                "                   0) ".
                "                   + IF((SELECT SUM(deposits.amount - deposits.fee) ".
                "                       FROM transactions, deposits ".
                "                       WHERE transactions.id = deposits.transactionid ".
                "                       AND deposits.rejected IS NULL ".
                "                       AND deposits.valid IS NULL ".
                "                       AND acc.id = transactions.accountid ".
                "                       AND transactions.amountghost >= 0) IS NOT NULL, ".
                "                       ROUND((SELECT SUM(deposits.amount - deposits.fee) ".
                "                           FROM transactions, deposits ".
                "                           WHERE transactions.id = deposits.transactionid ".
                "                           AND deposits.rejected IS NULL ".
                "                           AND deposits.valid IS NULL ".
                "                           AND acc.id = transactions.accountid ".
                "                           AND transactions.amountghost >= 0), cur.decimals), ".
                "                       0)) LIKE '%".parseFormattedNumber($el)."%'" : '') ).
                "               OR usercompanies.name LIKE '%".$el."%' ";
                
                $filter .= " OR users.usr LIKE '%".$el."%' ".
                "           OR cur.symbol LIKE '".$el."%' ".
                ( !$dateAndHour ? " OR (SELECT DATE(transactions.datecreated) FROM  transactions ".
                " 	            WHERE acc.id = transactions.accountid ".
                "         	    AND transactions.datecreated = (SELECT MAX(transactions.datecreated) FROM transactions WHERE acc.id = transactions.accountid) ".
                "         	    ORDER BY transactions.datecreated DESC, transactions.id DESC ".
                "    		    LIMIT 1 ".
                "           ) = DATE('".$dateel."') " : '').
                ( $dateAndHour ? " OR  (SELECT DATE_FORMAT(transactions.datecreated,'%Y-%m-%d %l:%i') FROM  transactions".
                " 	            WHERE acc.id = transactions.accountid ".
                "         	    AND transactions.datecreated = (SELECT MAX(transactions.datecreated) FROM transactions WHERE acc.id = transactions.accountid) ".
                "         	    ORDER BY transactions.datecreated DESC, transactions.id DESC ".
                "    		    LIMIT 1 ".
                "           ) = DATE_FORMAT('".$dateelhour."','%Y-%m-%d %l:%i') " : '').
                "           ".( validNumber($el) ? " OR IF((SELECT SUM(transactions.amount) ".
                "            	FROM transactions ".
                "            	WHERE transactions.accountid = acc.id) IS NOT NULL, ".
                "               ROUND((SELECT SUM(transactions.amount) ".
                "                	FROM transactions ".
                "                	WHERE transactions.accountid = acc.id), cur.decimals),".
                "               0) LIKE '%".floatval($el)."%'" : (validNumberFormatted($el) ? " OR IF((SELECT SUM(transactions.amount) ".
                "            	FROM transactions ".
                "            	WHERE transactions.accountid = acc.id) IS NOT NULL, ".
                "               ROUND((SELECT SUM(transactions.amount) ".
                "                	FROM transactions ".
                "                	WHERE transactions.accountid = acc.id), cur.decimals),".
                "               0) LIKE '%".parseFormattedNumber($el)."%'" : '')).
                "           ".( validNumber($el) ? " OR IF((SELECT SUM(deposits.amount - deposits.fee) ".
                "               FROM transactions, deposits ".
                "               WHERE transactions.id = deposits.transactionid ".
                "               AND deposits.rejected IS NULL ".
                "               AND deposits.valid IS NULL ".
                "               AND acc.id = transactions.accountid ".
                "               AND transactions.amountghost >= 0) IS NOT NULL,".
                "               ROUND((SELECT SUM(deposits.amount - deposits.fee) ".
                "                   FROM transactions, deposits ".
                "                   WHERE transactions.id = deposits.transactionid ".
                "                   AND deposits.rejected IS NULL ".
                "                   AND deposits.valid IS NULL ".
                "                   AND acc.id = transactions.accountid ".
                "                   AND transactions.amountghost >= 0), cur.decimals), ".
                "               0) LIKE '%".floatval($el)."%'" : (validNumberFormatted($el) ? " OR IF((SELECT SUM(deposits.amount - deposits.fee) ".
                "               FROM transactions, deposits ".
                "               WHERE transactions.id = deposits.transactionid ".
                "               AND deposits.rejected IS NULL ".
                "               AND deposits.valid IS NULL ".
                "               AND acc.id = transactions.accountid ".
                "               AND transactions.amountghost >= 0) IS NOT NULL,".
                "               ROUND((SELECT SUM(deposits.amount - deposits.fee) ".
                "                   FROM transactions, deposits ".
                "                   WHERE transactions.id = deposits.transactionid ".
                "                   AND deposits.rejected IS NULL ".
                "                   AND deposits.valid IS NULL ".
                "                   AND acc.id = transactions.accountid ".
                "                   AND transactions.amountghost >= 0), cur.decimals), ".
                "               0) LIKE '%".parseFormattedNumber($el)."%'" : '')).
                "               ".( validNumber($el) ? " OR ( IF((SELECT SUM(transactions.amount) ".
                "          	        FROM transactions ".
                "          	        WHERE transactions.accountid = acc.id) IS NOT NULL, ".
                "                   ROUND((SELECT SUM(transactions.amount) ".
                "                     	FROM transactions ".
                "                      	WHERE transactions.accountid = acc.id), cur.decimals), ".
                "                   0) ".
                "                   + IF((SELECT SUM(deposits.amount - deposits.fee) ".
                "                       FROM transactions, deposits ".
                "                       WHERE transactions.id = deposits.transactionid ".
                "                       AND deposits.rejected IS NULL ".
                "                       AND deposits.valid IS NULL ".
                "                       AND acc.id = transactions.accountid ".
                "                       AND transactions.amountghost >= 0) IS NOT NULL, ".
                "                       ROUND((SELECT SUM(deposits.amount - deposits.fee) ".
                "                           FROM transactions, deposits ".
                "                           WHERE transactions.id = deposits.transactionid ".
                "                           AND deposits.rejected IS NULL ".
                "                           AND deposits.valid IS NULL ".
                "                           AND acc.id = transactions.accountid ".
                "                           AND transactions.amountghost >= 0), cur.decimals), ".
                "                       0)) LIKE '%".floatval($el)."%'" : (validNumberFormatted($el) ? " OR ( IF((SELECT SUM(transactions.amount) ".
                "          	        FROM transactions ".
                "          	        WHERE transactions.accountid = acc.id) IS NOT NULL, ".
                "                   ROUND((SELECT SUM(transactions.amount) ".
                "                     	FROM transactions ".
                "                      	WHERE transactions.accountid = acc.id), cur.decimals), ".
                "                   0) ".
                "                   + IF((SELECT SUM(deposits.amount - deposits.fee) ".
                "                       FROM transactions, deposits ".
                "                       WHERE transactions.id = deposits.transactionid ".
                "                       AND deposits.rejected IS NULL ".
                "                       AND deposits.valid IS NULL ".
                "                       AND acc.id = transactions.accountid ".
                "                       AND transactions.amountghost >= 0) IS NOT NULL, ".
                "                       ROUND((SELECT SUM(deposits.amount - deposits.fee) ".
                "                           FROM transactions, deposits ".
                "                           WHERE transactions.id = deposits.transactionid ".
                "                           AND deposits.rejected IS NULL ".
                "                           AND deposits.valid IS NULL ".
                "                           AND acc.id = transactions.accountid ".
                "                           AND transactions.amountghost >= 0), cur.decimals), ".
                "                       0)) LIKE '%".parseFormattedNumber($el)."%'" : '') ).
                "               OR userpersons.sname LIKE '%".$el."%' ".
                "               OR usercompanies.name LIKE '%".$el."%' ";
            }
        }
        
        $filter .= " ) ";
        $filterPerson .= " ) ";
        $filterCompany .= " ) ";
    }
    
    //En caso de que el currencyid sea enviado, se construye el query
    if(isset($_GET["currencyid"]) && $_GET["currencyid"] != ""){
        $currencyid = " AND cur.id = ".$_GET['currencyid'];
    }
    
    $sql = "SELECT COUNT(acc.id) AS qty ".
    "       FROM accounts acc, currencies cur, users ".
    "           LEFT JOIN userpersons ON userpersons.usrid = users.id ".
    "           LEFT JOIN usercompanies ON usercompanies.usrid = users.id ".
    "       WHERE users.id = acc.userid ".
    "       AND acc.paymentmethodid = -1 ".
    "       AND acc.currencyid = cur.id ".
    "       ".$currencyid.
    "       ".$filter;
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>$db->error.$sql));
        
    $row = $rs->fetch_assoc();
    
    $out->numofrecords = (int)$row['qty'];
    
    /*
    SELECT acc.id, users.id, users.usr, users.type,
	CONCAT(userpersons.fname, ' ', userpersons.sname) AS firstname,
    CONCAT(userpersons.flastname, ' ' ,userpersons.slastname) AS secondname,
    currencies.symbol, currencies.name, currencies.id, currencies.decimals,
           IF((SELECT SUM(transactions.amount) 
            	FROM transactions
            	WHERE transactions.accountid = acc.id
           ) IS NOT NULL, (SELECT SUM(transactions.amount)
            	FROM transactions
            	WHERE transactions.accountid = acc.id
           ), 0) AS avaliable, 
           IF((SELECT SUM(deposits.amount - deposits.fee) 
               FROM transactions, deposits 
               WHERE transactions.id = deposits.transactionid 
               AND deposits.rejected IS NULL 
               AND deposits.valid IS NULL 
               AND acc.id = transactions.accountid
               AND transactions.amountghost >= 0 
           ) IS NOT NULL, (SELECT SUM(deposits.amount - deposits.fee) 
               FROM transactions, deposits 
               WHERE transactions.id = deposits.transactionid 
               AND deposits.rejected IS NULL 
               AND deposits.valid IS NULL 
               AND acc.id = transactions.accountid 
               AND transactions.amountghost >= 0 
           ), 0) AS deferred, 
           ( IF((SELECT SUM(transactions.amount) 
                	FROM transactions
                	WHERE transactions.accountid = acc.id) IS NOT NULL, 
               (SELECT SUM(transactions.amount) 
                    	FROM transactions
                    	WHERE transactions.accountid = acc.id),
               0) 
           + IF((SELECT SUM(deposits.amount - deposits.fee) 
                   FROM transactions, deposits 
                   WHERE transactions.id = deposits.transactionid 
                   AND deposits.rejected IS NULL 
                   AND deposits.valid IS NULL 
                   AND acc.id = transactions.accountid
                   AND transactions.amountghost >= 0) IS NOT NULL,
                   (SELECT SUM(deposits.amount - deposits.fee) 
                       FROM transactions, deposits 
                       WHERE transactions.id = deposits.transactionid 
                       AND deposits.rejected IS NULL 
                       AND deposits.valid IS NULL 
                       AND acc.id = transactions.accountid
                       AND transactions.amountghost >= 0), 
                0)) AS total, 
           (SELECT transactions.datecreated FROM  transactions
     	        WHERE acc.id = transactions.accountid 
             	AND transactions.datecreated = (SELECT MAX(transactions.datecreated) FROM transactions WHERE acc.id = transactions.accountid)
             	ORDER BY transactions.datecreated DESC, transactions.id DESC 
        		LIMIT 1 
           ) AS lasttx 
    FROM accounts acc, users, userpersons, currencies
    WHERE users.id = userpersons.usrid
    AND users.id = acc.userid
    AND acc.paymentmethodid = -1
    AND acc.currencyid = currencies.id
    
    UNION ALL
    
    SELECT acc.id, users.id, users.usr, users.type,
    CONCAT(usercompanies.name) AS firstname,
    CONCAT(usercompanies.comercialname) AS secondname,
    currencies.symbol, currencies.name, currencies.id, currencies.decimals,
           IF((SELECT SUM(transactions.amount) 
            	FROM transactions
            	WHERE transactions.accountid = acc.id
           ) IS NOT NULL, (SELECT SUM(transactions.amount)
            	FROM transactions
            	WHERE transactions.accountid = acc.id
           ), 0) AS avaliable, 
           IF((SELECT SUM(deposits.amount - deposits.fee) 
               FROM transactions, deposits 
               WHERE transactions.id = deposits.transactionid 
               AND deposits.rejected IS NULL 
               AND deposits.valid IS NULL 
               AND acc.id = transactions.accountid 
               AND transactions.amountghost >= 0 
           ) IS NOT NULL, (SELECT SUM(deposits.amount - deposits.fee) 
               FROM transactions, deposits 
               WHERE transactions.id = deposits.transactionid 
               AND deposits.rejected IS NULL 
               AND deposits.valid IS NULL 
               AND acc.id = transactions.accountid 
               AND transactions.amountghost >= 0 
           ), 0) AS deferred, 
           ( IF((SELECT SUM(transactions.amount) 
                	FROM transactions
                	WHERE transactions.accountid = acc.id) IS NOT NULL, 
               (SELECT SUM(transactions.amount) 
                    	FROM transactions
                    	WHERE transactions.accountid = acc.id),
               0) 
           + IF((SELECT SUM(deposits.amount - deposits.fee) 
                   FROM transactions, deposits 
                   WHERE transactions.id = deposits.transactionid 
                   AND deposits.rejected IS NULL 
                   AND deposits.valid IS NULL 
                   AND acc.id = transactions.accountid
                   AND transactions.amountghost >= 0) IS NOT NULL,
                   (SELECT SUM(deposits.amount - deposits.fee) 
                       FROM transactions, deposits 
                       WHERE transactions.id = deposits.transactionid 
                       AND deposits.rejected IS NULL 
                       AND deposits.valid IS NULL 
                       AND acc.id = transactions.accountid
                       AND transactions.amountghost >= 0), 
                0)) AS total, 
           (SELECT transactions.datecreated FROM  transactions
     	        WHERE acc.id = transactions.accountid 
             	AND transactions.datecreated = (SELECT MAX(transactions.datecreated) FROM transactions WHERE acc.id = transactions.accountid) 
             	ORDER BY transactions.datecreated DESC, transactions.id DESC 
        		LIMIT 1 
           ) AS lasttx 
	FROM accounts acc, users, usercompanies, currencies
    WHERE users.id = usercompanies.usrid
    AND users.id = acc.userid
    AND acc.paymentmethodid = -1
    AND acc.currencyid = currencies.id
    */
    
    $sql = "SELECT acc.id AS accountid, ".
    "       users.id AS userid, ".
    "       users.usr AS usr, ".
    "       users.type AS usrtype, ".
    "       CONCAT(userpersons.fname, ' ', userpersons.sname) AS firstname, ".
    "       CONCAT(userpersons.flastname, ' ' ,userpersons.slastname) AS secondname, ".
    "       cur.symbol AS currencysymbol, ".
    "       cur.name AS currencyname, ".
    "       cur.id AS currencyid, ".
    "       cur.decimals AS currencydecimals, ".
    "       IF((SELECT SUM(transactions.amount) ".
    "         	FROM transactions ".
    "          	WHERE transactions.accountid = acc.id ".
    "       ) IS NOT NULL, (SELECT SUM(transactions.amount) ".
    "       	FROM transactions ".
    "          	WHERE transactions.accountid = acc.id ".
    "       ), 0) AS avaliable, ".
    "       IF((SELECT SUM(deposits.amount - deposits.fee) ".
    "           FROM transactions, deposits ".
    "           WHERE transactions.id = deposits.transactionid ".
    "           AND deposits.rejected IS NULL ".
    "           AND deposits.valid IS NULL ".
    "           AND acc.id = transactions.accountid ".
    "           AND transactions.amountghost >= 0 ".
    "       ) IS NOT NULL, (SELECT SUM(deposits.amount - deposits.fee) ".
    "           FROM transactions, deposits ".
    "           WHERE transactions.id = deposits.transactionid ".
    "           AND deposits.rejected IS NULL ".
    "           AND deposits.valid IS NULL ".
    "           AND acc.id = transactions.accountid ".
    "           AND transactions.amountghost >= 0 ".
    "       ), 0) AS deferred, ".
    "       ( IF((SELECT SUM(transactions.amount) ".
    "          	FROM transactions ".
    "          	WHERE transactions.accountid = acc.id) IS NOT NULL, ".
    "           (SELECT SUM(transactions.amount) ".
    "             	FROM transactions ".
    "              	WHERE transactions.accountid = acc.id), ".
    "       0) ".
    "       + IF((SELECT SUM(deposits.amount - deposits.fee) ".
    "           FROM transactions, deposits ".
    "           WHERE transactions.id = deposits.transactionid ".
    "           AND deposits.rejected IS NULL ".
    "           AND deposits.valid IS NULL ".
    "           AND acc.id = transactions.accountid ".
    "           AND transactions.amountghost >= 0) IS NOT NULL, ".
    "           (SELECT SUM(deposits.amount - deposits.fee) ".
    "               FROM transactions, deposits ".
    "               WHERE transactions.id = deposits.transactionid ".
    "               AND deposits.rejected IS NULL ".
    "               AND deposits.valid IS NULL ".
    "               AND acc.id = transactions.accountid ".
    "               AND transactions.amountghost >= 0), ".
    "       0)) AS total, ".
    "       (SELECT transactions.datecreated FROM  transactions ".
    "           WHERE acc.id = transactions.accountid ".
    "           AND transactions.datecreated = (SELECT MAX(transactions.datecreated) FROM transactions WHERE acc.id = transactions.accountid) ".
    "           ORDER BY transactions.datecreated DESC, transactions.id DESC ".
    "           LIMIT 1 ".
    "       ) AS lasttx ".
    "       FROM accounts acc, users, userpersons, currencies cur".
    "       WHERE users.id = userpersons.usrid ".
    "       AND users.id = acc.userid ".
    "       AND acc.paymentmethodid = -1 ".
    "       AND acc.currencyid = cur.id ".
    "       ".$currencyid.
    "       ".$filterPerson.
    "       ".
    "   UNION ALL ".
    "       ".
    "       SELECT acc.id AS accountid, ".
    "       users.id AS userid, ".
    "       users.usr AS usr, ".
    "       users.type AS usrtype, ".
    "       CONCAT(usercompanies.name) AS firstname, ".
    "       CONCAT(usercompanies.comercialname) AS secondname, ".
    "       cur.symbol AS currencysymbol, ".
    "       cur.name AS currencyname, ".
    "       cur.id AS currencyid, ".
    "       cur.decimals AS currencydecimals, ".
    "       IF((SELECT SUM(transactions.amount) ".
    "         	FROM transactions ".
    "          	WHERE transactions.accountid = acc.id ".
    "       ) IS NOT NULL, (SELECT SUM(transactions.amount) ".
    "       	FROM transactions ".
    "          	WHERE transactions.accountid = acc.id ".
    "       ), 0) AS avaliable, ".
    "       IF((SELECT SUM(deposits.amount - deposits.fee) ".
    "           FROM transactions, deposits ".
    "           WHERE transactions.id = deposits.transactionid ".
    "           AND deposits.rejected IS NULL ".
    "           AND deposits.valid IS NULL ".
    "           AND acc.id = transactions.accountid ".
    "           AND transactions.amountghost >= 0 ".
    "       ) IS NOT NULL, (SELECT SUM(deposits.amount - deposits.fee) ".
    "           FROM transactions, deposits ".
    "           WHERE transactions.id = deposits.transactionid ".
    "           AND deposits.rejected IS NULL ".
    "           AND deposits.valid IS NULL ".
    "           AND acc.id = transactions.accountid ".
    "           AND transactions.amountghost >= 0 ".
    "       ), 0) AS deferred, ".
    "       ( IF((SELECT SUM(transactions.amount) ".
    "          	FROM transactions ".
    "          	WHERE transactions.accountid = acc.id) IS NOT NULL, ".
    "           (SELECT SUM(transactions.amount) ".
    "             	FROM transactions ".
    "              	WHERE transactions.accountid = acc.id), ".
    "       0) ".
    "       + IF((SELECT SUM(deposits.amount - deposits.fee) ".
    "           FROM transactions, deposits ".
    "           WHERE transactions.id = deposits.transactionid ".
    "           AND deposits.rejected IS NULL ".
    "           AND deposits.valid IS NULL ".
    "           AND acc.id = transactions.accountid ".
    "           AND transactions.amountghost >= 0) IS NOT NULL, ".
    "           (SELECT SUM(deposits.amount - deposits.fee) ".
    "               FROM transactions, deposits ".
    "               WHERE transactions.id = deposits.transactionid ".
    "               AND deposits.rejected IS NULL ".
    "               AND deposits.valid IS NULL ".
    "               AND acc.id = transactions.accountid ".
    "               AND transactions.amountghost >= 0), ".
    "       0)) AS total, ".
    "       (SELECT transactions.datecreated FROM  transactions ".
    "           WHERE acc.id = transactions.accountid ".
    "           AND transactions.datecreated = (SELECT MAX(transactions.datecreated) FROM transactions WHERE acc.id = transactions.accountid) ".
    "           ORDER BY transactions.datecreated DESC, transactions.id DESC ".
    "           LIMIT 1 ".
    "       ) AS lasttx ".
    "       FROM accounts acc, users, usercompanies, currencies cur ".
	"       WHERE users.id = usercompanies.usrid ".
    "       AND users.id = acc.userid ".
    "       AND acc.paymentmethodid = -1 ".
    "       AND acc.currencyid = cur.id".
    "       ".$currencyid.
    "       ".$filterCompany.
    "       ".$order.
    "       LIMIT ".$offset.", ".$numofrec;
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>$db->error.$sql));
        
    //Ordenamos los datos
    while($row = $rs->fetch_assoc()){
        $record = new stdClass();
        $record->testing = $row;
        
        $record->currency = new stdClass();
        $record->currency->id = (int)$row['currencyid'];
        $record->currency->name = $row['currencyname'];
        $record->currency->symbol = $row['currencysymbol'];
        
        $record->user = new stdClass();
        $record->user->id = (int)$row['userid'];
        $record->user->usr = $row['usr'];
        if($row["usrtype"] == 1){
 
            $record->user->fullname = $row["firstname"].($row["secondname"] == "" ? "" : " ".$row["secondname"]);
        }else{
            $record->user->fullname = $row["firstname"].($row["secondname"] == "" ? "" : " / ".$row["secondname"]);
        }
        
        
        $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->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"]));
        
        
        $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
    *   2 = Saldo disponible ASCENDENTE
    *   3 = Saldo pendiente ASCENDENTE
    *   4 = Saldo total ASCENDENTE
    *   -2 = Saldo disponible DESCENTE
    *   -3 = Saldo pendiente DESCENTE
    *   -4 = Saldo total DESCENTE
    *   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";
                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";
                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";
                break;
            default:
                $orderres = "ORDER BY lasttx DESC";
                break;
        }
        
        return $orderres;
    }
?>
