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

    header("Content-Type:application/json");
    include("../../../settings/dbconn.php");
    include("../../../settings/utils.php");
    
    // parametros obligatorios
    $parmsob = array("sessionid","offset","numofrecords");
    if (!parametrosValidos($_GET, $parmsob))
        badEnd("400", array("msg"=>"Parametros obligatorios " . implode(", ", $parmsob)));
    
    //Declaramos e inicializamos nuestras variables
    $out= new stdClass();
    $sessionid = mysqli_real_escape_string($db, $_GET["sessionid"]);
    $offset = mysqli_real_escape_string($db, $_GET["offset"]);
    $numofrecords = mysqli_real_escape_string($db, $_GET["numofrecords"]);
    $order = determinateOrder(mysqli_real_escape_string($db, $_GET["order"]));
    $time = "";
    $status = mysqli_real_escape_string($db, $_GET["status"]);
    $kindDeposit = "";
    $kindWithdra = "";
    //TIPOS
    //  0 = BOTH; 1 = DEPOSITOS; 2 = RETIROS; 3 = TRANSFERENCIAS; 4 = PAY PAGOS
    $type = "";
    $currencyid = "";
    $limit = "LIMIT ".$offset.", ".$numofrecords;
    $filter = '';
    
    //Validamos que la session del usuario sea valida para continuar
    isSessionValidCMS($db, $sessionid);
    
    //Validamos que el usuario tenga privilegios
    if(!tienePrivilegio($db, $sessionid, 33))
        badEnd("403", array("msg"=>"No tiene Privilegios"));
        
        
    //En caso de que el filter sea enviado, se construye el query
    if(isset($_GET["filter"]) && $_GET["filter"] != ""){
        // Iniciamos la cadena del query
        //$filterTx = " AND ( ";
        //$filterDeposit = " AND ( ";
        //$filterWithdrawals = " AND ( ";
        
        //Separamos el filtro por el simbolo +
        $filterEl = separateFilter(mysqli_real_escape_string($db, $_GET["filter"]));
        //Recorremos cada elemento
        foreach($filterEl as $index => $el){
             
            //Si la fecha tiene / lo partimos en mil
            if(strpos($el, '/') !== false){
                $dateelements = explode("/",$el);
                
                foreach($dateelements as $i => $element){
                    if($i == 0){
                        $dateConverted .= $element;
                    }else{
                        $dateConverted .= "-".$element;
                    }
                }
                
                $dateel = strftime("%Y-%m-%d", strtotime($dateConverted));
            }else{
                $dateel = strftime("%Y-%m-%d", strtotime($el));
            }
            //FALTA ARMAR ACÁ UN QUERY QUE FILTRE PARA LA TABLA PRINCIPAL 'SQLMAIN' DIRECTAMENTE
            if($index !== 0)$filter .= " OR ";
            else $filter .= " AND ";

            $filter .= "(   u.usr LIKE '".$el."%' OR ".
            "               usercompanies.name LIKE '".$el."%' OR " .
            "               CONCAT(userpersons.fname,' ',userpersons.flastname) LIKE '".$el."%'" .
            "               OR A.transactionid = '".$el."'".
            "               OR A.typedsc LIKE '".$el."%'".
            "               OR currencies.symbol LIKE '".$el."%'".
            "               OR A.statusorder LIKE '".$el."%' ".
            "               OR DATE(A.datecreated) = DATE('".$dateel."') ".
            "               ".(validNumber($el) ? "OR A.amount LIKE '%".floatval($el)."%'" : (validNumberFormatted($el) ? " OR  A.amount LIKE '%".parseFormattedNumber($el)."%'" : "")).
            "               ".(validNumber($el) ? "OR A.amount LIKE '%-".floatval($el)."%'" : (validNumberFormatted($el) ? " OR  A.amount LIKE '%-".parseFormattedNumber($el)."%'" : "")).
            "           )";
            
            /*
            if($index == 0){
                $filterTx .= "users.usr LIKE '".$el."%' OR ".
                "           usercompanies.name LIKE '".$el."%' OR ".
                "           userpersons.fname LIKE '".$el."%'".
                "           OR t.id = '".$el."'".
                "           OR IF( (SELECT deposits.transactionid AS depositid ".
                "                   FROM deposits ".
                "                   WHERE t.id = deposits.transactionid) IS NOT NULL, 'Deposito', ".
                "                   IF( (SELECT withdrawals.transactionid AS depositid ".
                "                       FROM withdrawals ".
                "                       WHERE withdrawals.transactionid = t.id) IS NOT NULL , 'Retiro', 'Transferencia')".
                "               ) LIKE '".$el."%'".
                "           OR (SELECT symbol FROM currencies WHERE id = accounts.currencyid) LIKE '".$el."%'".
                "           OR (CASE ".
                "               WHEN t.amountghost = 0 AND t.paypending = 0 THEN 'Aprobado' ".
                "               ELSE 'Pendiente' ".
                "           END) LIKE '".$el."%' ".
                "           OR DATE(t.datecreated) = DATE('".$dateel."') ".
                "           ".(validNumber($el) ? "OR t.amount LIKE '%".floatval($el)."%'" : (validNumberFormatted($el) ? " OR  t.amount LIKE '%".parseFormattedNumber($el)."%'" : "")).
                "           ".(validNumber($el) ? "OR t.amount LIKE '%-".floatval($el)."%'" : (validNumberFormatted($el) ? " OR  t.amount LIKE '%-".parseFormattedNumber($el)."%'" : ""));
                
                $filterDeposit .= "users.usr LIKE '".$el."%' OR ".
                "           usercompanies.name LIKE '".$el."%' OR ".
                "           userpersons.fname LIKE '".$el."%' ".
                "           OR t.id = '".$el."'".
                "           OR IF( (SELECT deposits.transactionid AS depositid ".
                "                   FROM deposits ".
                "                   WHERE t.id = deposits.transactionid) IS NOT NULL, 'Deposito', ".
                "                   IF( (SELECT withdrawals.transactionid AS depositid ".
                "                       FROM withdrawals ".
                "                       WHERE withdrawals.transactionid = t.id) IS NOT NULL , 'Retiro', 'Transferencia')".
                "               ) LIKE '".$el."%'".
                "           OR (SELECT symbol FROM currencies WHERE id = accounts.currencyid) LIKE '".$el."%'".
                "           OR (CASE ".
                "               WHEN deposits.rejected IS NULL AND deposits.valid IS NULL THEN 'Pendiente' ".
                "               WHEN deposits.rejected IS NULL AND deposits.valid IS NOT NULL THEN 'Aprobado' ".
                "               WHEN deposits.rejected IS NOT NULL AND deposits.valid IS NULL THEN 'Rechazado' ".
                "           END) LIKE '".$el."%' ".
                "           OR DATE(t.datecreated) = DATE('".$dateel."') ".
                "           ".(validNumber($el) ? "OR deposits.amount LIKE '%".floatval($el)."%'" : (validNumberFormatted($el) ? " OR deposits.amount LIKE '%".parseFormattedNumber($el)."%' " : ""));
                
                $filterWithdrawals .= "users.usr LIKE '".$el."%' OR ".
                "           usercompanies.name LIKE '".$el."%' OR ".
                "           userpersons.fname LIKE '".$el."%' ".
                "           OR t.id = '".$el."'".
                "           OR IF( (SELECT deposits.transactionid AS depositid ".
                "                   FROM deposits ".
                "                   WHERE t.id = deposits.transactionid) IS NOT NULL, 'Deposito', ".
                "                   IF( (SELECT withdrawals.transactionid AS depositid ".
                "                       FROM withdrawals ".
                "                       WHERE withdrawals.transactionid = t.id) IS NOT NULL , 'Retiro', 'Transferencia')".
                "               ) LIKE '".$el."%'".
                "           OR (SELECT symbol FROM currencies WHERE id = accounts.currencyid) LIKE '".$el."%'".
                "           OR (CASE ".
                "               WHEN withdrawals.rejected IS NULL AND withdrawals.valid IS NULL THEN 'Pendiente' ".
                "               WHEN withdrawals.rejected IS NULL AND withdrawals.valid IS NOT NULL THEN 'Aprobado' ".
                "               WHEN withdrawals.rejected IS NOT NULL AND withdrawals.valid IS NULL THEN 'Rechazado' ".
                "           END) LIKE '".$el."%' ".
                "           OR DATE(t.datecreated) = DATE('".$dateel."') ".
                "           ".(validNumber($el) ? "OR (ABS(withdrawals.amount) + ".
                "               IF( (SELECT interface FROM currencies WHERE id = accounts.currencyid) != 4 ".
                "               AND (SELECT interface FROM currencies WHERE id = accounts.currencyid) != 5, ABS(withdrawals.fee) ,0 )) LIKE '%".floatval($el)."%'" : (validNumberFormatted($el) ? " OR (ABS(withdrawals.amount) + IF( (SELECT interface FROM currencies WHERE id = accounts.currencyid) != 4 AND (SELECT interface FROM currencies WHERE id = accounts.currencyid) != 5, ABS(withdrawals.fee) ,0 )) LIKE '%".parseFormattedNumber($el)."%'" : ""));
            
                
            }else{
                
                $filterTx .= " OR users.usr LIKE '".$el."%' OR ".
                "           usercompanies.name LIKE '".$el."%' OR ".
                "           userpersons.fname LIKE '".$el."%'".
                "           OR t.id = '".$el."'".
                "           OR IF( (SELECT deposits.transactionid AS depositid ".
                "                   FROM deposits ".
                "                   WHERE t.id = deposits.transactionid) IS NOT NULL, 'Deposito', ".
                "                   IF( (SELECT withdrawals.transactionid AS depositid ".
                "                       FROM withdrawals ".
                "                       WHERE withdrawals.transactionid = t.id) IS NOT NULL , 'Retiro', 'Transferencia')".
                "               ) LIKE '".$el."%'".
                "           OR (SELECT symbol FROM currencies WHERE id = accounts.currencyid) LIKE '".$el."%'".
                "           OR (CASE ".
                "               WHEN t.amountghost = 0 AND t.paypending = 0 THEN 'Aprobado' ".
                "               ELSE 'Pendiente' ".
                "           END) LIKE '".$el."%' ".
                "           OR DATE(t.datecreated) = DATE('".$dateel."') ".
                "           ".(validNumber($el) ? "OR t.amount LIKE '%".floatval($el)."%' " : (validNumberFormatted($el) ? " OR  t.amount LIKE '%".parseFormattedNumber($el)."%' " : "")).
                "           ".(validNumber($el) ? "OR t.amount LIKE '%-".floatval($el)."%' " : (validNumberFormatted($el) ? " OR  t.amount LIKE '%-".parseFormattedNumber($el)."%' " : ""));
                
                $filterDeposit .= " OR users.usr LIKE '".$el."%' OR ".
                "           usercompanies.name LIKE '".$el."%' OR ".
                "           userpersons.fname LIKE '".$el."%' ".
                "           OR t.id = '".$el."'".
                "           OR IF( (SELECT deposits.transactionid AS depositid ".
                "                   FROM deposits ".
                "                   WHERE t.id = deposits.transactionid) IS NOT NULL, 'Deposito', ".
                "                   IF( (SELECT withdrawals.transactionid AS depositid ".
                "                       FROM withdrawals ".
                "                       WHERE withdrawals.transactionid = t.id) IS NOT NULL , 'Retiro', 'Transferencia')".
                "               ) LIKE '".$el."%'".
                "           OR (SELECT symbol FROM currencies WHERE id = accounts.currencyid) LIKE '".$el."%'".
                "           OR (CASE ".
                "               WHEN deposits.rejected IS NULL AND deposits.valid IS NULL THEN 'Pendiente' ".
                "               WHEN deposits.rejected IS NULL AND deposits.valid IS NOT NULL THEN 'Aprobado' ".
                "               WHEN deposits.rejected IS NOT NULL AND deposits.valid IS NULL THEN 'Rechazado' ".
                "           END) LIKE '".$el."%' ".
                "           OR DATE(t.datecreated) = DATE('".$dateel."') ".
                "           ".(validNumber($el) ? "OR deposits.amount LIKE '%".floatval($el)."%' " : (validNumberFormatted($el) ? " OR deposits.amount LIKE '%".parseFormattedNumber($el)."%' " : ""));
                
                $filterWithdrawals .= " OR users.usr LIKE '".$el."%' OR ".
                "           usercompanies.name LIKE '".$el."%' OR ".
                "           userpersons.fname LIKE '".$el."%' ".
                "           OR t.id = '".$el."'".
                "           OR IF( (SELECT deposits.transactionid AS depositid ".
                "                   FROM deposits ".
                "                   WHERE t.id = deposits.transactionid) IS NOT NULL, 'Deposito', ".
                "                   IF( (SELECT withdrawals.transactionid AS depositid ".
                "                       FROM withdrawals ".
                "                       WHERE withdrawals.transactionid = t.id) IS NOT NULL , 'Retiro', 'Transferencia')".
                "               ) LIKE '".$el."%'".
                "           OR (SELECT symbol FROM currencies WHERE id = accounts.currencyid) LIKE '".$el."%'".
                "           OR (CASE ".
                "               WHEN withdrawals.rejected IS NULL AND withdrawals.valid IS NULL THEN 'Pendiente' ".
                "               WHEN withdrawals.rejected IS NULL AND withdrawals.valid IS NOT NULL THEN 'Aprobado' ".
                "               WHEN withdrawals.rejected IS NOT NULL AND withdrawals.valid IS NULL THEN 'Rechazado' ".
                "           END) LIKE '".$el."%' ".
                "           OR DATE(t.datecreated) = DATE('".$dateel."') ".
                "           ".(validNumber($el) ? "OR (ABS(withdrawals.amount) + ABS(withdrawals.fee)) LIKE '%".floatval($el)."%' " : (validNumberFormatted($el) ? " OR (ABS(withdrawals.amount) + ABS(withdrawals.fee)) LIKE '%".parseFormattedNumber($el)."%' " : ""));
            
            }
        
        */
        }
        //$filterTx .= " ) ";
        //$filterDeposit .= " ) ";
        //$filterWithdrawals .= " ) ";
    }
    
    //En caso de que el fecha sea enviado, se construye el query
    if(isset($_GET["fecha"]) && $_GET["fecha"] != ""){
        $time = " AND DATE(t.createddate) >= ".determinateTime(mysqli_real_escape_string($db, $_GET["fecha"]));
    }
    
    //En caso de que el type sea enviado, se construye el query
    if(isset($_GET["type"]) && $_GET["type"] != ""){
        $type = " AND A.group = ". mysqli_real_escape_string($db, $_GET["type"]);
    }
    
    //En caso de que el currencyid sea enviado, se construye el query
    if(isset($_GET["currencyid"]) && $_GET["currencyid"] != ""){
        $currencyid = "AND accounts.currencyid = ".mysqli_real_escape_string($db, $_GET["currencyid"]);
    }
    
    
    if($status == 1){
        $kindDeposit = " AND d.rejected IS NULL ".
        "                AND d.valid IS NULL";
        $kindWithdra = " AND w.rejected IS NULL".
        "                AND w.valid IS NULL";

        $kindXtr = " AND t.paypending = 1";
    }else if($status == 2){
        $kindDeposit = " AND  d.rejected IS NULL".
        "                AND d.valid IS NOT NULL";
        $kindWithdra = " AND w.rejected IS NULL ".
        "                AND w.valid IS NOT NULL";

        $kindXtr = " AND t.paypending = 0";
    }else if($status == 3){
        $kindDeposit = " AND d.rejected IS NOT NULL ".
        "                AND d.valid IS NULL";
        $kindWithdra = " AND  w.rejected IS NOT NULL".
        "                AND w.valid IS NULL";

        $kindXtr = " AND t.paypending = 0";
    }else{
        $kindDeposit = "";
        $kindWithdra = "";
        $kindXtr = "";
    }
    /*
    if($type == 1){
        
        $sql =  "SELECT         COUNT(t.id) AS qty " .
        "FROM           transactions t ".
        "INNER JOIN     deposits d ON d.transactionid = t.txid ".
        "INNER JOIN     accounts a " .
        "ON             t.accountid = a.id " .
        "               ".$time.
        "               AND a.paymentmethodid = -1 " .
        "               AND a.entityusrid = -1 ".
        "               ".$kindDeposit." ".
        "       WHERE t.id = t.txid";
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db).$sql));
            
        $row = $rs->fetch_assoc();
        $out->numofrecords = (int)$row["qty"];
        
        $sqlDeposits =  "SELECT         t.id AS transactionid, ".
        "               t.dsc, ".
        "               t.accountorigin, t.accountdestination, " .
        "               IF(t.amount = 0, t.amountghost, t.amount) AS amount, ".
        "               d.fee AS fee, ".
        "               DATE_FORMAT(t.datecreated, '%Y-%m-%d %H:%i') AS datecreated, ".
        "               t.amountghost AS amountghost,  ".
        "               CASE ".
	    "                   WHEN d.rejected IS NULL AND d.valid IS NULL THEN 0 ".
	    "                   WHEN d.rejected IS NULL AND d.valid IS NOT NULL THEN 1 ".
	    "                   WHEN d.rejected IS NOT NULL AND d.valid IS NULL THEN 2 ".
	    "               END AS status, ".
	    "               CASE ".
	    "                   WHEN d.rejected IS NULL AND d.valid IS NULL THEN 'Pendiente' ".
	    "                   WHEN d.rejected IS NULL AND d.valid IS NOT NULL THEN 'Aprobado' ".
	    "                   WHEN d.rejected IS NOT NULL AND d.valid IS NULL THEN 'Rechazado' ".
	    "               END AS statusorder, ".
    	"		        t.accountid, ".
    	"               IF(t.id <> t.txid, 'Comisi&oacuten', 'Dep&oacutesito') AS typedsc, ".
        "               IF(t.id <> t.txid, 'FEE', 'DEP') AS type, ".
        "               'DEP' AS 'group' " .
        "FROM           transactions t ".
        "INNER JOIN     deposits d ON d.transactionid = t.txid ".
        "INNER JOIN     accounts a " .
        "ON             t.accountid = a.id " .
        "               ".$time.
        "               AND a.paymentmethodid = -1 " .
        "               AND a.entityusrid = -1 ".
        "               ".$kindDeposit." ".
        "       WHERE t.id = t.txid";
        
        // Moneda y cuenta
        $sql =  "SELECT     A.*, " .
                "           currencies.id AS currid, " .
                "           currencies.name AS currname, " .
                "           currencies.symbol AS currsymbol, " .
                "           currencies.decimals AS decimals, " .
                "           accounts.paymentmethodid AS accountpaymentmethodid, ".
        	    "           accounts.currencyid AS accountcurrencyid, ".
        	    "           accounts.status AS accountstatus, ".
        	    "           u.id AS usrid, ".
                "           u.usr AS usr, ".
                "           userpersons.fname AS personname, ".
                "           userpersons.flastname AS personlastname, ".
                "           usercompanies.name AS companyname, ".
                "           usercompanies.comercialname AS companycomercialname, ".
                "           CASE ".
        	    "               WHEN accounts.rejected IS NULL AND accounts.approved IS NULL THEN 0 ".
        	    "               WHEN accounts.rejected IS NULL AND accounts.approved IS NOT NULL THEN 1 ".
        	    "               WHEN accounts.rejected IS NOT NULL THEN -1 ".
        	    "           END AS accountvalid, ".
                "           currencies.icon AS icon " .
                "FROM ( " . $sqlDeposits . "  ) A " .
                "INNER JOIN accounts ON A.accountid = accounts.id " .
                "INNER JOIN currencies ON accounts.currencyid = currencies.id " .
                "INNER JOIN users u ON u.id = accounts.userid ".
                "LEFT JOIN usercompanies ON usercompanies.usrid = u.id ".
                "LEFT JOIN userpersons ON userpersons.usrid = u.id ".
                "WHERE      A.transactionid IS NOT NULL ".
                "           ".$currencyid.
                "           ".$order.
                "           ".$limit;
        
    }else if($type == 2){
        
        $sql =  "SELECT         COUNT(t.id) AS qty " .
        "FROM           transactions t ".
        "INNER JOIN     withdrawals w ON w.transactionid = t.txid ".
        "INNER JOIN     accounts a " .
        "ON             t.accountid = a.id " .
        "               ".$time.
        "               AND a.paymentmethodid = -1 " .
        "               AND a.entityusrid = -1 ".
        "               ".$kindWithdra." ".
        "       WHERE t.id = t.txid";
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db).$sql));
            
        $row = $rs->fetch_assoc();
        $out->numofrecords = (int)$row["qty"];
        
        $sqlWithdrawals =  "SELECT         t.id AS transactionid, ".
        "               t.dsc, ".
        "               t.accountorigin, t.accountdestination, " .
        "               IF(t.amount = 0, t.amountghost, t.amount) AS amount, ".
        "               w.fee AS fee, ".
        "               DATE_FORMAT(t.datecreated, '%Y-%m-%d %H:%i') AS datecreated, ".
        "               t.amountghost AS amountghost,  ".
        "               CASE ".
	    "                   WHEN w.rejected IS NULL AND w.valid IS NULL THEN 0 ".
	    "                   WHEN w.rejected IS NULL AND w.valid IS NOT NULL THEN 1 ".
	    "                   WHEN w.rejected IS NOT NULL AND w.valid IS NULL THEN 2 ".
	    "               END AS status, ".
	    "               CASE ".
	    "                   WHEN w.rejected IS NULL AND w.valid IS NULL THEN 'Pendiente' ".
	    "                   WHEN w.rejected IS NULL AND w.valid IS NOT NULL THEN 'Aprobado' ".
	    "                   WHEN w.rejected IS NOT NULL AND w.valid IS NULL THEN 'Rechazado' ".
	    "               END AS statusorder, ".
    	"		        t.accountid, ".
    	"               IF(t.id <> t.txid, 'Comisi&oacuten', 'Dep&oacutesito') AS typedsc, ".
    	"               IF(t.id <> t.txid, 'FEE', 'WITH') AS type, ".
        "               'DEP' AS 'group' " .
        "FROM           transactions t ".
        "INNER JOIN     withdrawals w ON w.transactionid = t.txid ".
        "INNER JOIN     accounts a " .
        "ON             t.accountid = a.id " .
        "               ".$time.
        "               AND a.paymentmethodid = -1 " .
        "               AND a.entityusrid = -1 ".
        "               ".$kindWithdra." ".
        "       WHERE t.id = t.txid";
        
        // Moneda y cuenta
        $sql =  "SELECT     A.*, " .
                "           currencies.id AS currid, " .
                "           currencies.name AS currname, " .
                "           currencies.symbol AS currsymbol, " .
                "           currencies.decimals AS decimals, " .
                "           accounts.paymentmethodid AS accountpaymentmethodid, ".
        	    "           accounts.currencyid AS accountcurrencyid, ".
        	    "           accounts.status AS accountstatus, ".
        	    "           u.id AS usrid, ".
                "           u.usr AS usr, ".
                "           userpersons.fname AS personname, ".
                "           userpersons.flastname AS personlastname, ".
                "           usercompanies.name AS companyname, ".
                "           usercompanies.comercialname AS companycomercialname, ".
                "           CASE ".
        	    "               WHEN accounts.rejected IS NULL AND accounts.approved IS NULL THEN 0 ".
        	    "               WHEN accounts.rejected IS NULL AND accounts.approved IS NOT NULL THEN 1 ".
        	    "               WHEN accounts.rejected IS NOT NULL THEN -1 ".
        	    "           END AS accountvalid, ".
                "           currencies.icon AS icon " .
                "FROM ( " . $sqlWithdrawals . "  ) A " .
                "INNER JOIN accounts ON A.accountid = accounts.id " .
                "INNER JOIN currencies ON accounts.currencyid = currencies.id " .
                "INNER JOIN users u ON u.id = accounts.userid ".
                "LEFT JOIN usercompanies ON usercompanies.usrid = u.id ".
                "LEFT JOIN userpersons ON userpersons.usrid = u.id ".
                "WHERE      A.transactionid IS NOT NULL ".
                "           ".$currencyid.
                "           ".$order.
                "           ".$limit;
        
    }else if ($type == 3){
        
        $sql =  "SELECT         COUNT(t.id) AS qty " .
                    "FROM           transactions t " . 
                	"LEFT JOIN 	    deposits d ON d.transactionid = t.txid " .
                    "LEFT JOIN 	    withdrawals w ON w.transactionid = t.txid " .
                    "LEFT JOIN 	    pagomovildetails pm ON pm.transactionid = t.txid ".
                    "LEFT JOIN 	    paytxdetails ptx ON ptx.transactionid = t.txid ".
                	"INNER JOIN 	accounts a  ".
                	"ON    	   	    a.id = t.accountid  ".
                	"               AND a.entityusrid = -1 ".
                	"               AND a.paymentmethodid = -1 ".
                	"LEFT JOIN      accounts adest ON adest.id = t.accountdestination " .
                	"WHERE 		    d.transactionid IS NULL ". 
                	"               AND w.transactionid IS NULL ".
                    "               AND ptx.transactionid IS NULL ".
                    "               AND pm.transactionid IS NULL ".
                	"               AND t.orderid IS NULL ".
                	"               AND t.accountorigin IS NOT NULL ".
                	"               AND (t.dsc LIKE 'Transferencia a%' OR t.dsc LIKE 'Transferencia de%')".
                	"               ".$kindXtr.
                	"               ".$time;
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db).$sql));
            
        $row = $rs->fetch_assoc();
        $out->numofrecords = (int)$row["qty"];
        
        
        
        $sqlXfer     =  "SELECT         t.id AS transactionid, t.dsc, ".
                    "               t.accountorigin, t.accountdestination, " .
                    "               IF(t.amount = 0, t.amountghost, t.amount) AS amount, ".
                    "               DATE_FORMAT(t.datecreated, '%Y-%m-%d %H:%i') AS datecreated, ".
                    "               t.amountghost AS amountghost,  ".
                	"		        t.accountid, ".
                	"               CASE ".
            	    "                   WHEN t.amountghost = 0 AND t.paypending = 0 THEN 1 ".
            	    "                   ELSE 0 ".
            	    "               END AS status, ".
            	    "               CASE ".
            	    "                   WHEN t.amountghost = 0 AND t.paypending = 0 THEN 'Aprobado' ".
            	    "                   ELSE 'Pendiente' ".
            	    "               END AS statusorder, ".
                	"               IF(adest.paymentmethodid<>-1, 'Comisi&oacuten', 'Transferencia') AS typedsc, ".
                    "               IF(adest.paymentmethodid<>-1, 'FEE', 'XFER') AS type, ".
		            "               'XFER' AS 'group' " .
                    "FROM           transactions t " . 
                	"LEFT JOIN 	    deposits d ON d.transactionid = t.txid " .
                    "LEFT JOIN 	    withdrawals w ON w.transactionid = t.txid " .
                    "LEFT JOIN 	    pagomovildetails pm ON pm.transactionid = t.txid ".
                    "LEFT JOIN 	    paytxdetails ptx ON ptx.transactionid = t.txid ".
                	"INNER JOIN 	accounts a  ".
                	"ON    	   	    a.id = t.accountid  ".
                	"               AND a.entityusrid = -1 ".
                	"               AND a.paymentmethodid = -1 " .
                	"LEFT JOIN      accounts adest ON adest.id = t.accountdestination " .
                	"WHERE 		    d.transactionid IS NULL ". 
                	"               AND w.transactionid IS NULL ".
                    "               AND ptx.transactionid IS NULL ".
                    "               AND pm.transactionid IS NULL ".
                	"               AND t.orderid IS NULL ".
                	"               AND t.accountorigin IS NOT NULL ".
                	"               AND (t.dsc LIKE 'Transferencia a%' OR t.dsc LIKE 'Transferencia de%')".
                	"               ".$kindXtr.
                	"               ".$time;
        
        // Moneda y cuenta
        $sql =  "SELECT     A.*, " .
                "           currencies.id AS currid, " .
                "           currencies.name AS currname, " .
                "           currencies.symbol AS currsymbol, " .
                "           currencies.decimals AS decimals, " .
                "           accounts.paymentmethodid AS accountpaymentmethodid, ".
        	    "           accounts.currencyid AS accountcurrencyid, ".
        	    "           accounts.status AS accountstatus, ".
        	    "           u.id AS usrid, ".
                "           u.usr AS usr, ".
                "           userpersons.fname AS personname, ".
                "           userpersons.flastname AS personlastname, ".
                "           usercompanies.name AS companyname, ".
                "           usercompanies.comercialname AS companycomercialname, ".
                "           CASE ".
        	    "               WHEN accounts.rejected IS NULL AND accounts.approved IS NULL THEN 0 ".
        	    "               WHEN accounts.rejected IS NULL AND accounts.approved IS NOT NULL THEN 1 ".
        	    "               WHEN accounts.rejected IS NOT NULL THEN -1 ".
        	    "           END AS accountvalid, ".
                "           currencies.icon AS icon " .
                "FROM ( " . $sqlXfer . "  ) A " .
                "INNER JOIN accounts ON A.accountid = accounts.id " .
                "INNER JOIN currencies ON accounts.currencyid = currencies.id " .
                "INNER JOIN users u ON u.id = accounts.userid ".
                "LEFT JOIN usercompanies ON usercompanies.usrid = u.id ".
                "LEFT JOIN userpersons ON userpersons.usrid = u.id ".
                "WHERE      A.transactionid IS NOT NULL ".
                "           ".$currencyid.
                "           ".$order.
                "           ".$limit;
        
    }else if ($type == 4){
        
        $sql =  "SELECT         COUNT(t.id) AS qty " .
                    "FROM           transactions t " . 
                	"LEFT JOIN 	    deposits d ON d.transactionid = t.txid " .
                    "LEFT JOIN 	    withdrawals w ON w.transactionid = t.txid " .
                    "LEFT JOIN 	    pagomovildetails pm ON pm.transactionid = t.txid ".
                	"INNER JOIN 	accounts a  ".
                	"ON    	   	    a.id = t.accountid  ".
                	"               AND a.entityusrid = -1 ".
                	"               AND a.paymentmethodid = -1 ".
                	"LEFT JOIN      accounts adest ON adest.id = t.accountdestination " .
                	"WHERE 		    d.transactionid IS NULL ". 
                	"               AND w.transactionid IS NULL ".
                    "               AND pm.transactionid IS NULL ".
                	"               AND t.orderid IS NULL ".
                	"               AND t.accountorigin IS NOT NULL ".
                	"               AND (t.dsc LIKE 'Transferencia PAY%' OR (t.dsc LIKE 'Solicitud de Cobro PAY%' AND t.paypending = 0))".
                	"               ".$kindXtr.
                	"               ".$time;
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db).$sql));
            
        $row = $rs->fetch_assoc();
        $out->numofrecords = (int)$row["qty"];
        
        $sqlXferPay     =  "SELECT         t.id AS transactionid, t.dsc, ".
                    "               t.accountorigin, t.accountdestination, " .
                    "               IF(t.amount = 0, t.amountghost, t.amount) AS amount, ".
                    "               DATE_FORMAT(t.datecreated, '%Y-%m-%d %H:%i') AS datecreated, ".
                    "               t.amountghost AS amountghost,  ".
                	"		        t.accountid, ".
                	"               CASE ".
            	    "                   WHEN t.amountghost = 0 AND t.paypending = 0 THEN 1 ".
            	    "                   ELSE 0 ".
            	    "               END AS status, ".
            	    "               CASE ".
            	    "                   WHEN t.amountghost = 0 AND t.paypending = 0 THEN 'Aprobado' ".
            	    "                   ELSE 'Pendiente' ".
            	    "               END AS statusorder, ".
                	"               IF(adest.paymentmethodid<>-1, 'Comisi&oacuten', 'Transferencia PAY') AS typedsc, ".
                    "               IF(adest.paymentmethodid<>-1, 'FEE', 'XFER') AS type, ".
		            "               'XFER' AS 'group' " .
                    "FROM           transactions t " . 
                	"LEFT JOIN 	    deposits d ON d.transactionid = t.txid " .
                    "LEFT JOIN 	    withdrawals w ON w.transactionid = t.txid " .
                    "LEFT JOIN 	    pagomovildetails pm ON pm.transactionid = t.txid ".
                	"INNER JOIN 	accounts a  ".
                	"ON    	   	    a.id = t.accountid  ".
                	"               AND a.entityusrid = -1 ".
                	"               AND a.paymentmethodid = -1 " .
                	"LEFT JOIN      accounts adest ON adest.id = t.accountdestination " .
                	"WHERE 		    d.transactionid IS NULL ". 
                	"               AND w.transactionid IS NULL ".
                    "               AND pm.transactionid IS NULL ".
                	"               AND t.orderid IS NULL ".
                	"               AND t.accountorigin IS NOT NULL ".
                	"               AND (t.dsc LIKE 'Transferencia PAY%' OR (t.dsc LIKE 'Solicitud de Cobro PAY%' AND t.paypending = 0))".
                	"               ".$kindXtr.
                	"               ".$time;
                
        // Moneda y cuenta
        $sql =  "SELECT     A.*, " .
                "           currencies.id AS currid, " .
                "           currencies.name AS currname, " .
                "           currencies.symbol AS currsymbol, " .
                "           currencies.decimals AS decimals, " .
                "           accounts.paymentmethodid AS accountpaymentmethodid, ".
        	    "           accounts.currencyid AS accountcurrencyid, ".
        	    "           accounts.status AS accountstatus, ".
        	    "           u.id AS usrid, ".
                "           u.usr AS usr, ".
                "           userpersons.fname AS personname, ".
                "           userpersons.flastname AS personlastname, ".
                "           usercompanies.name AS companyname, ".
                "           usercompanies.comercialname AS companycomercialname, ".
                "           CASE ".
        	    "               WHEN accounts.rejected IS NULL AND accounts.approved IS NULL THEN 0 ".
        	    "               WHEN accounts.rejected IS NULL AND accounts.approved IS NOT NULL THEN 1 ".
        	    "               WHEN accounts.rejected IS NOT NULL THEN -1 ".
        	    "           END AS accountvalid, ".
                "           currencies.icon AS icon " .
                "FROM ( " . $sqlXferPay . "  ) A " .
                "INNER JOIN accounts ON A.accountid = accounts.id " .
                "INNER JOIN currencies ON accounts.currencyid = currencies.id " .
                "INNER JOIN users u ON u.id = accounts.userid ".
                "LEFT JOIN usercompanies ON usercompanies.usrid = u.id ".
                "LEFT JOIN userpersons ON userpersons.usrid = u.id ".
                "WHERE      A.transactionid IS NOT NULL ".
                "           ".$currencyid.
                "           ".$order.
                "           ".$limit;
                
    }else{
        
        $sqlXferPay =  "SELECT      t.id AS transactionid, t.dsc, ".
                    "               t.accountorigin, t.accountdestination, " .
                    "               IF(t.amount = 0, t.amountghost, t.amount) AS amount, ".
                    "               0 AS fee, ".
                    "               DATE_FORMAT(t.datecreated, '%Y-%m-%d %H:%i') AS datecreated, ".
                    "               t.amountghost AS amountghost,  ".
                	"		        t.accountid, ".
                	"               CASE ".
            	    "                   WHEN t.amountghost = 0 AND t.paypending = 0 THEN 1 ".
            	    "                   ELSE 0 ".
            	    "               END AS status, ".
            	    "               CASE ".
            	    "                   WHEN t.amountghost = 0 AND t.paypending = 0 THEN 'Aprobado' ".
            	    "                   ELSE 'Pendiente' ".
            	    "               END AS statusorder, ".
                	"               IF(adest.paymentmethodid<>-1, 'Comisi&oacuten', 'Transferencia') AS typedsc, ".
                    "               IF(adest.paymentmethodid<>-1, 'FEE', 'XFER') AS type, ".
		            "               'XFER' AS 'group' " .
                    "FROM           transactions t " . 
                	"LEFT JOIN 	    deposits d ON d.transactionid = t.txid " .
                    "LEFT JOIN 	    withdrawals w ON w.transactionid = t.txid " .
                    "LEFT JOIN 	    pagomovildetails pm ON pm.transactionid = t.txid ".
                	"INNER JOIN 	accounts a  ".
                	"ON    	   	    a.id = t.accountid  ".
                	"               AND a.entityusrid = -1 ".
                	"               AND a.paymentmethodid = -1 " .
                	"LEFT JOIN      accounts adest ON adest.id = t.accountdestination " .
                	"WHERE 		    d.transactionid IS NULL ". 
                	"               AND w.transactionid IS NULL ".
                    "               AND pm.transactionid IS NULL ".
                	"               AND t.orderid IS NULL ".
                	"               AND t.accountorigin IS NOT NULL ".
                	"               ".$kindXtr.
                	"               ".$time;
        */

        //Agregamos los pagos pay a las operaciones el pago siempre es monto completo + comision se agrupa y se suman por cuenta del usuario. 
        //Muestra 2 operaciones similar a transferencias       
        $sqlPaytx =     "SELECT         t.txid AS transactionid, t.dsc, ".
                        "               t.accountorigin, t.accountdestination, " .
                        "               IF(SUM(t.amount) = 0, SUM(t.amountghost), SUM(t.amount)) AS amount, ".
                        "               DATE_FORMAT(t.datecreated, '%Y-%m-%d %H:%i') AS datecreated, ".
                        "               t.amountghost AS amountghost,  ".                    
                        "               IF(t.paypending = 1, 0, 1) AS status, " .
                        "               IF(t.paypending = 1, 'Rechazado', 'Aprobado') AS statusorder, " .
                        "		        t.accountid, ".
                        "               IF(adest.paymentmethodid <> -1, 'Comisi&oacuten', 'Pagos AFX Pay') AS typedsc, ".
                        "               IF(adest.paymentmethodid <> -1, 'FEE', 'PAYTX') AS type, " .
                        "               '4' AS 'group' " .
                        "FROM           transactions t ".
                        "INNER JOIN     paytxdetails pd ON pd.transactionid = t.txid ".
                        "INNER JOIN     accounts a " .
                        "ON             t.accountid = a.id " .
                        "               ".$time.
                        "               AND a.paymentmethodid = -1 " .
                        "               AND a.entityusrid = -1 " .
                        $kindXtr." ".//Esto usa el t.paypending
                        "LEFT JOIN      accounts adest ".
                        "               ON adest.id = t.accountdestination " .
                        "GROUP BY       t.txid, a.id";
                    
        //Movimientos de cripto pago sin sumar las comisiones
        $sqlPMAll =     "       SELECT  	    t.id, t.txid, t.dsc, pd.ref AS ref, " .
                        "                       t.accountorigin, t.accountdestination, " .
                        "                       IF(t.amount = 0, t.amountghost, t.amount) AS amount, " .
                        "                       DATE_FORMAT(t.datecreated, '%Y-%m-%d %H:%i') AS datecreated,  ". 
                        "                       t.amountghost AS amountghost, " .
    			        "                       t.accountid, t.paypending AS paypending, t.validator AS validator, " .
    			        "                       IF(adest.paymentmethodid <> -7, 'Comisi&oacuten', 'Cripto Pago') AS typedsc, ".
    			        "                       IF(adest.paymentmethodid <> -7, 'FEE', 'PMPAY') AS type, " .                                      
                        "                       IF(t.paypending = 0, 1, 0) AS status, " .                                             
                        "                       IF(t.paypending = 0, 'Aprobado', 'Pendiente') AS statusorder " .
    			        "       FROM            transactions t " .
        		        "       INNER JOIN      accounts a ON t.accountid = a.id ".                        
        		        "                       ".$time.
                        "                       AND a.paymentmethodid = -1 " .
                        "                       AND a.entityusrid = -1 ".
        		        "       INNER JOIN      pagomovildetails pd ON pd.transactionid = t.txid ".
    			        "       LEFT JOIN       accounts adest ON adest.id = t.accountdestination" .
                        "       WHERE           t.paypending = 0 AND t.validator IS NULL ". //Filtro los que si se confirmaron con código                        
    			        "       ORDER BY        t.id ";
                        
        //Se suma todo por txid valor de la transaccion más los 2 fees cobrados
        $sqlPMGroup =   "SELECT         PM.txid AS transactionid, PM.dsc, ".        
                        "               MAX(PM.accountorigin) AS accountorigin, MAX(PM.accountdestination)  AS accountdestination, " .
                        "               IF(SUM(PM.amount) = 0, SUM(PM.amountghost), SUM(PM.amount)) AS amount, ".
                        "               DATE_FORMAT(PM.datecreated, '%Y-%m-%d %H:%i') AS datecreated, ".
                        "               PM.amountghost AS amountghost,  ".            
                        "               PM.status, PM.statusorder, " .
                        "		        PM.accountid, ".
                        "               PM.typedsc AS typedsc, " .
                        "               PM.type AS type, " .
                        "               '5' AS 'group' " .
                        "FROM           (" . $sqlPMAll . ") PM " .
                        "INNER JOIN     accounts a ON PM.accountid = a.id ".
                        "GROUP BY       PM.txid";
    
        //El deposito siempre ingresa el monto total y luego se rebaja una comisión así que debemos tomar el primero movimiento
        $sqlDeposits =  "SELECT         t.id AS transactionid, ".
                        "               t.dsc, ".
                        "               t.accountorigin, t.accountdestination, " .
                        "               IF(t.amount = 0, t.amountghost, t.amount) AS amount, ".
                        "               DATE_FORMAT(t.datecreated, '%Y-%m-%d %H:%i') AS datecreated, ".
                        "               t.amountghost AS amountghost,  ".
                        "               CASE ".
                        "                   WHEN d.rejected IS NULL AND d.valid IS NULL THEN 0 ".
                        "                   WHEN d.rejected IS NULL AND d.valid IS NOT NULL THEN 1 ".
                        "                   WHEN d.rejected IS NOT NULL AND d.valid IS NULL THEN 2 ".
                        "               END AS status, ".
                        "               CASE ".
                        "                   WHEN d.rejected IS NULL AND d.valid IS NULL THEN 'Pendiente' ".
                        "                   WHEN d.rejected IS NULL AND d.valid IS NOT NULL THEN 'Aprobado' ".
                        "                   WHEN d.rejected IS NOT NULL AND d.valid IS NULL THEN 'Rechazado' ".
                        "               END AS statusorder, ".
                        "		        t.accountid, ".
                        "               IF(t.id <> t.txid, 'Comisi&oacuten', 'Dep&oacutesito') AS typedsc, ".
                        "               IF(t.id <> t.txid, 'FEE', 'DEP') AS type, ".
                        "               '1' AS 'group' " .
                        "FROM           transactions t ".
                        "INNER JOIN     deposits d ON d.transactionid = t.txid ".
                        "INNER JOIN     accounts a " .
                        "ON             t.accountid = a.id " .
                        "               ".$time.
                        "               AND a.paymentmethodid = -1 " .
                        "               AND a.entityusrid = -1 ".
                        "               ".$kindDeposit." ".
                        "WHERE          t.id = t.txid";
                        
        //Separamos el select del retiro en 2 querys para poder sumar el movimiento de comisión cuando corresponda
        //y poder devolver la estructura del unión (la tabla a mostrar no debería tener calculos o querys dentro del while)

        //En este query principal debemos ignorar las comisiones en todos los casos menos el retiro ves
        $sqlWithdAll =  "SELECT         t.id AS transactionid, ".
                        "               t.dsc, ".
                        "               t.accountorigin, t.accountdestination, " .
                        "               IF(t.amount = 0, t.amountghost, t.amount) AS amount, ".
                        "               DATE_FORMAT(t.datecreated, '%Y-%m-%d %H:%i') AS datecreated, ".
                        "               t.amountghost AS amountghost,  ".
                        "               CASE ".
                        "                   WHEN w.rejected IS NULL AND w.valid IS NULL THEN 0 ".
                        "                   WHEN w.rejected IS NULL AND w.valid IS NOT NULL THEN 1 ".
                        "                   WHEN w.rejected IS NOT NULL AND w.valid IS NULL THEN 2 ".
                        "               END AS status, ".
                        "               CASE ".
                        "                   WHEN w.rejected IS NULL AND w.valid IS NULL THEN 'Pendiente' ".
                        "                   WHEN w.rejected IS NULL AND w.valid IS NOT NULL THEN 'Aprobado' ".
                        "                   WHEN w.rejected IS NOT NULL AND w.valid IS NULL THEN 'Rechazado' ".
                        "               END AS statusorder, ".
                        "		        t.accountid, ".
                        "               IF(t.id <> t.txid, 'Comisi&oacuten', 'Retiro') AS typedsc, ".
                        "               IF(t.id <> t.txid, 'FEE', 'WITH') AS type, ".
                        "               '2' AS 'group', " .
                        "               c.interface AS interface, c.decimals, t.txid " .
                        "FROM           transactions t ".
                        "INNER JOIN     withdrawals w ON w.transactionid = t.txid ".
                        "INNER JOIN     accounts a " .
                        "ON             t.accountid = a.id " .
                        "               ".$time.
                        "               AND a.paymentmethodid = -1 " .
                        "               AND a.entityusrid = -1 ".
                        "               ".$kindWithdra." " .
                        "LEFT JOIN      currencies c ON c.id = a.currencyid " .
                        "WHERE          (t.id = t.txid AND c.interface <> 1) OR (c.interface = 1)";
                            
        //Agrupamos los retiros esto sumará las comisiones dónde correspondan prefiltrado anterior
        $sqlWithdGroup ="SELECT         W.txid AS transactionid, W.dsc, ".        
                        "               MAX(W.accountorigin) AS accountorigin, MAX(W.accountdestination)  AS accountdestination, " .
                        "               IF(SUM(W.amount) = 0, SUM(W.amountghost), SUM(W.amount)) AS amount, ".
                        "               DATE_FORMAT(W.datecreated, '%Y-%m-%d %H:%i') AS datecreated, ".
                        "               W.amountghost AS amountghost,  ".                        
                        "               W.status AS status, " .
                        "               W.statusorder AS statusorder, " .
                        "		        W.accountid, ".
                        "               W.typedsc AS typedsc, " .
                        "               W.type AS type, " .
                        "               W.group AS 'group' " .
                        "FROM           (" . $sqlWithdAll . ") W " .
                        "INNER JOIN      accounts a ON W.accountid = a.id ".
                        "GROUP BY       W.txid";

        //Las transferencias deben reflejar 2 movimientos C/U la salida por el monto completo y el ingreso el movimiento menos la comisión
        //Lo que se debe hacer es sumar las salidas para tener el monto total y mantener el ingreso lo hacemos por accountid
        $sqlXfer     =  "SELECT         t.txid AS transactionid, t.dsc, ".
                        "               t.accountorigin, t.accountdestination, " .
                        "               IF(SUM(t.amount) = 0, SUM(t.amountghost), SUM(t.amount)) AS amount, ".
                        "               DATE_FORMAT(t.datecreated, '%Y-%m-%d %H:%i') AS datecreated, ".
                        "               SUM(t.amountghost) AS amountghost,  ".
                        "               CASE ".
                        "                   WHEN t.amountghost = 0 AND t.paypending = 0 THEN 1 ".
                        "                   ELSE 0 ".
                        "               END AS status, ".
                        "               CASE ".
                        "                   WHEN t.amountghost = 0 AND t.paypending = 0 THEN 'Aprobado' ".
                        "                   ELSE 'Pendiente' ".
                        "               END AS statusorder, ".                        
                        "		        t.accountid, ".
                        "               IF(adest.paymentmethodid<>-1, 'Comisi&oacuten', 'Transferencia') AS typedsc, ".
                        "               IF(adest.paymentmethodid<>-1, 'FEE', 'XFER') AS type, ".
                        "               '3' AS 'group' " .
                        "FROM           transactions t " . 
                        "LEFT JOIN 	    deposits d ON d.transactionid = t.txid " .
                        "LEFT JOIN 	    withdrawals w ON w.transactionid = t.txid " .
                        "LEFT JOIN 	    pagomovildetails pm ON pm.transactionid = t.txid ".
                        "LEFT JOIN 	    paytxdetails ptx ON ptx.transactionid = t.txid ".
                        "INNER JOIN 	accounts a  ".
                        "ON    	   	    a.id = t.accountid  ".
                        "               AND a.entityusrid = -1 ".
                        "               AND a.paymentmethodid = -1 " .
                        "LEFT JOIN      accounts adest ON adest.id = t.accountdestination " .
                        "WHERE 		    d.transactionid IS NULL ". 
                        "               AND w.transactionid IS NULL ".
                        "               AND ptx.transactionid IS NULL ".
                        "               AND pm.transactionid IS NULL ".
                        "               AND t.orderid IS NULL ".
                        "               AND t.accountorigin IS NOT NULL ".
                        "               ".$kindXtr.//Esto usa el t.paypending
                        "               ".$time.
                        "GROUP BY       t.accountid,t.txid";


            $lastWallet="SELECT wallets.*       FROM  wallets ".
                        "INNER JOIN     (".
                        "               SELECT MAX(tb2.id) AS id, accountid AS accid FROM wallets tb2 GROUP BY tb2.accountid".
                        "               ) maxwall ".//Wallets mas recientes agrupada por cuenta con las cuentas acc.id
                        "               ON wallets.id = maxwall.id ";//Información de las wallets recientes
                        
            $sqlMain =  "SELECT         A.*, " .
                        "               currencies.id AS currid, " .
                        "               currencies.name AS currname, " .
                        "               currencies.symbol AS currsymbol, " .
                        "               currencies.decimals AS currdecimals, " .                         
                        "               currencies.interface AS currinterface, " .
                        "               accounts.paymentmethodid AS accountpaymentmethodid, ".
                        "               accounts.currencyid AS accountcurrencyid, ".
                        "               accounts.status AS accountstatus, ".
                        "               IF(accounts.status = 1,'Activa','Inactiva') AS accountstatusdsc, ".                        
                        "               pm.dsc AS accountpaymentmethoddsc, " .
                        "               IFNULL(wallets.wallet,'') as accountwallet, " .
                        "               u.id AS usrid, ".
                        "               u.usr AS usr, ".
                        "               userpersons.fname AS personname, ".
                        "               userpersons.flastname AS personlastname, ".
                        "               usercompanies.name AS companyname, ".
                        "               usercompanies.comercialname AS companycomercialname, ".
                        "               CASE ".
                        "                   WHEN accounts.rejected IS NULL AND accounts.approved IS NULL THEN 0 ".
                        "                   WHEN accounts.rejected IS NULL AND accounts.approved IS NOT NULL THEN 1 ".
                        "                   WHEN accounts.rejected IS NOT NULL THEN -1 ".
                        "               END AS accountvalid, ".
                        "               currencies.icon AS icon " .
                        "FROM           ( " . $sqlDeposits .    " UNION ".
                        "               "   . $sqlPMGroup .     " UNION ".
                        "               "   . $sqlXfer .        " UNION ".
                        "               "   . $sqlPaytx .       " UNION ".
                        "               "   . $sqlWithdGroup . " ) A " .
                        "INNER JOIN     accounts ON A.accountid = accounts.id " .
                        "LEFT JOIN      paymentmethods pm ON pm.id = accounts.paymentmethodid " .
                        "INNER JOIN     currencies ON accounts.currencyid = currencies.id " .
                        "INNER JOIN     users u ON u.id = accounts.userid ".
                        "LEFT JOIN      usercompanies ON usercompanies.usrid = u.id ".
                        "LEFT JOIN      userpersons ON userpersons.usrid = u.id ".
                        "LEFT JOIN      (".$lastWallet.") wallets ON wallets.accountid = accounts.id " .
                        "WHERE          A.transactionid IS NOT NULL ".
                        "               ".$filter.
                        "               ".$type.
                        "               ".$currencyid.
                        "               ".$order;       

                
                
           
        
        
        //Query principal para el numofrecords
        $sql =  "SELECT     COUNT(A.transactionid) AS qty " .
                "FROM       ( " . $sqlMain . ") A";
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));        
        
        $row = $rs->fetch_assoc();
        $out->numofrecords = (int)$row["qty"];
        
        //Query principal filtrado por el limit
        $sql =  "SELECT     A.* " .
                "FROM       ( " . $sqlMain . ") A " .
                "           ".$limit;    
    
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db).$sql));
    
    //Ordenamos los resultados(bof)
    while($row = $rs->fetch_assoc()){
        $record = new stdClass();
        $record->id = (int)$row["transactionid"];
        
        $record->date = new stdClass();
        $record->date->cannonical = date("Y-m-d H:i", strtotime($row["datecreated"]));
        $record->date->formatted = date("d/m/Y", strtotime($row["datecreated"]))." - ".date("h:i a", strtotime($row["createddate"]));
        
        $record->type = new stdClass();
    
        // 1 = DEPOSITO; 2 = RETIRO WN
        //Si el campo depositid es distinto a null significa que es un deposito
        /*
        if($row['type'] == 'DEP'){
            $record->type->id = 1;
            $record->type->dsc = "Depósito";
        }else if($row['type'] == 'WITH'){
            $record->type->id = 2;
            $record->type->dsc = "Retiro";
        }else if ($row['type'] == 'PAYTX') {
            $record->type->id = 4;
            $record->type->dsc = "Transferencia PAY";
        } else {
            $record->type->id = 3;
            $record->type->dsc = "Transferencia";
        }*/
        $record->type->id = $row['group'];
        $record->type->dsc = $row['typedsc'];
    
        $record->status = (int)$row["status"];
        
        $record->statusdsc = $row["statusorder"];
        //Consultamos la cuenta del movimiento
        $record->account = new stdClass();
        
        $record->account->id = (int)$row["accountid"];
        
        //Ordenamos los datos del usuario
        $record->account->user = new stdClass();
        $record->account->user->id = (int)$row["usrid"];
        $record->account->user->usr = $row["usr"];
    
        if($row["personname"] == null){
            $record->account->user->fullname = $row["companyname"].( $row["companycomercialname"] == "" ? "" : " / ".$row["companycomercialname"] );
        }else{
            $record->account->user->fullname = $row["personname"].( $row["personlastname"] == "" ? "" : " ".$row["personlastname"] );
        }
        /*
        $sql = "SELECT dsc ".
        "       FROM paymentmethods ".
        "       WHERE id = ".$row["accountpaymentmethodid"];
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db).$sql));
        
        $paymentmethod = $res->fetch_assoc();*/
        
        $record->account->paymentmethod = new stdClass();
        $record->account->paymentmethod->id = (int)$row["accountpaymentmethodid"];
        $record->account->paymentmethod->dsc = $row["accountpaymentmethoddsc"];
        
        //Consultamos la wallet(si existejjeje)
        /*
        $sql = "SELECT * FROM wallets".
        "       WHERE accountid = ".$record->account->id;
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db).$sql));
        
        $wallet = $res->fetch_assoc();*/
    
        $record->account->wallet = $row["accountwallet"];
        
        /*
        $sql = "SELECT * FROM currencies WHERE id = ".$row["accountcurrencyid"];
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db).$sql));
        
        $currencydata = $res->fetch_assoc();*/
        
        $record->account->currency = new stdClass();
        $record->account->currency->id = (int)$row["currid"];
        $record->account->currency->name = $row["currname"];
        $record->account->currency->symbol = $row["currsymbol"];
        $record->account->currency->interface = $row['currinterface'];
        $record->account->currency->decimals = $row['currdecimals'];
        
        $record->account->status = new stdClass();
        $record->account->status->id = (int)$row["accountstatus"];
        $record->account->status->dsc = $row["accountstatusdsc"];//$record->account->status->id == 1 ? "Activa" : "Inactiva";

        $record->account->valid = $row['accountvalid'];
        
        $record->amount = new stdClass();
        $record->amount->number = $row["amount"];//(abs($row["amount"]) + abs($row["fee"])) + (abs($txdataFee["amount"]) + abs($txdataFee["amountghost"]));
        $record->amount->formatted = numberFormatt($record->amount->number,$row["currdecimals"]);

        // 1 = DEPOSITO; 2 = RETIRO WN
        //Datos de la cuenta de AFX DEPOSITOS
        //Si no existe acc destination es por que es un retiro

        //LOS QUERYS INTERNOS DE ESTA SECCIÓN DEBEN SER REMPLAZADOS AGREGANDO EN EL QUERY PRINCIPAL LAS COLUMNAS REQUERIDAS OBTENIDAS DE DICHOS QUERYS

        if($record->type->id == 2){
            $sql = "SELECT * FROM withdrawals WHERE transactionid = ".$record->id;
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db).$sql));
                
            $accountdata = $res->fetch_assoc();
            
            if($accountdata["paymentwallet"] != null){
                $record->userpaymentmethod = new stdClass();
                $record->userpaymentmethod->wallet = $accountdata["paymentwallet"];
            }else{
                $record->userpaymentmethod = new stdClass();
            
                $sql = "SELECT ".
                "       users.id AS userid, ".
                "       users.usr AS usr, ".
                "       accounts.id AS accountid, ".
                "       accounts.details1 AS accountdetails1, ".
                "       accounts.details2 AS accountdetails2, ".
                "       accounts.details3 AS accountdetails3, ".
                "       accounts.details4 AS accountdetails4, ".
                "       accounts.currencyid AS accountcurrencyid, ".
                "       paymentmethods.id AS paymentmethodid, ".
                "       paymentmethods.dsc AS paymetmenthoddsc, ".
                "       paymentmethods.details1 AS paymentmethoddetails1, ".
                "       paymentmethods.details2 AS paymentmethoddetails2, ".
                "       paymentmethods.details3 AS paymentmethoddetails3, ".
                "       paymentmethods.details4 AS paymentmethoddetails4 ".
                "       FROM accounts, users, paymentmethods ".
                "       WHERE accounts.id = ".$accountdata["paymentaccountid"].
                "       AND accounts.userid = users.id ".
                "       AND accounts.paymentmethodid = paymentmethods.id";
                if (!$res=$db->query($sql))
                    badEnd("500", array("msg2"=>determinateDBError($db).$sql));
                
                $paymentaccountdata = $res->fetch_assoc();
    
                $record->userpaymentmethod->id = (int)$paymentaccountdata["accountid"];
                
                //Ordenamos los datos del usuario
                $record->userpaymentmethod->user = new stdClass();
                $record->userpaymentmethod->user->id = (int)$paymentaccountdata["userid"];
                $record->userpaymentmethod->user->fullname = $paymentaccountdata["usr"];
                
                $record->userpaymentmethod->paymentmethod = new stdClass();
                $record->userpaymentmethod->paymentmethod->id = (int)$paymentaccountdata["paymentmethodid"];
                $record->userpaymentmethod->paymentmethod->dsc = $paymentaccountdata["paymetmenthoddsc"];
                $record->userpaymentmethod->paymentmethod->details1 = $paymentaccountdata["paymentmethoddetails1"];
                $record->userpaymentmethod->paymentmethod->details2 = $paymentaccountdata["paymentmethoddetails2"];
                $record->userpaymentmethod->paymentmethod->details3 = $paymentaccountdata["paymentmethoddetails3"];
                $record->userpaymentmethod->paymentmethod->details4 = $paymentaccountdata["paymentmethoddetails4"];
                
                //Consultamos la wallet(si existejjeje)
                $sql = "SELECT * FROM wallets".
                "       WHERE accountid = ".$record->userpaymentmethod->id;
                if (!$res=$db->query($sql))
                    badEnd("500", array("msg"=>determinateDBError($db).$sql));
                    
                $wallet = $res->fetch_assoc();
                
                $record->userpaymentmethod->wallet = $wallet["wallet"];
                
                $sql = "SELECT * FROM currencies WHERE id = ".$paymentaccountdata["accountcurrencyid"];
                if (!$res=$db->query($sql))
                     badEnd("500", array("msg"=>determinateDBError($db).$sql));
                    
                $currencydata = $res->fetch_assoc();
                
                $record->userpaymentmethod->currency = new stdClass();
                $record->userpaymentmethod->currency->id = (int)$currencydata["id"];
                $record->userpaymentmethod->currency->name = $currencydata["name"];
                $record->userpaymentmethod->currency->symbol = $currencydata["symbol"];
                $record->userpaymentmethod->currency->interface = $currencydata["interface"];
                
                $record->userpaymentmethod->details1 = $paymentaccountdata["details1"];
                $record->userpaymentmethod->details2 = $paymentaccountdata["details2"];
                $record->userpaymentmethod->details3 = $paymentaccountdata["details3"];
                $record->userpaymentmethod->details4 = $paymentaccountdata["details4"];
            }
        }else if($row["txaccdestination"] == null){
            $record->userpaymentmethod = null;
        } else if ($row['paypending'] == 0) {
            $record->userpaymentmethod = new stdClass();
            
            $sql = "SELECT ".
            "       users.id AS userid, ".
            "       users.usr AS usr, ".
            "       accounts.id AS accountid, ".
            "       accounts.details1 AS accountdetails1, ".
            "       accounts.details2 AS accountdetails2, ".
            "       accounts.details3 AS accountdetails3, ".
            "       accounts.details4 AS accountdetails4, ".
            "       accounts.currencyid AS accountcurrencyid, ".
            "       paymentmethods.id AS paymentmethodid, ".
            "       paymentmethods.dsc AS paymetmenthoddsc, ".
            "       paymentmethods.details1 AS paymentmethoddetails1, ".
            "       paymentmethods.details2 AS paymentmethoddetails2, ".
            "       paymentmethods.details3 AS paymentmethoddetails3, ".
            "       paymentmethods.details4 AS paymentmethoddetails4 ".
            "       FROM accounts, users, paymentmethods ".
            "       WHERE accounts.id = ".($row["txaccdestination"] == $record->account->id && $record->type->id != 1 ? $row["txaccorigin"] : $row["txaccdestination"]).//<-- Devuelve "" en algunos casos y hace que el query explote (depositos especificamente)
            "       AND accounts.userid = users.id ".
            "       AND accounts.paymentmethodid = paymentmethods.id";
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db).$sql));
                
            $accountdata = $res->fetch_assoc();
    
            $record->userpaymentmethod->id = (int)$accountdata["accountid"];
                
            //Ordenamos los datos del usuario
            $record->userpaymentmethod->user = new stdClass();
            $record->userpaymentmethod->user->id = (int)$accountdata["userid"];
            $record->userpaymentmethod->user->fullname = $accountdata["usr"];
            
            $record->userpaymentmethod->paymentmethod = new stdClass();
            $record->userpaymentmethod->paymentmethod->id = (int)$accountdata["paymentmethodid"];
            $record->userpaymentmethod->paymentmethod->dsc = $accountdata["paymetmenthoddsc"];
            $record->userpaymentmethod->paymentmethod->details1 = $accountdata["paymentmethoddetails1"];
            $record->userpaymentmethod->paymentmethod->details2 = $accountdata["paymentmethoddetails2"];
            $record->userpaymentmethod->paymentmethod->details3 = $accountdata["paymentmethoddetails3"];
            $record->userpaymentmethod->paymentmethod->details4 = $accountdata["paymentmethoddetails4"];
            
            //Consultamos la wallet(si existejjeje)
            $sql = "SELECT * FROM wallets".
            "       WHERE accountid = ".$record->userpaymentmethod->id;
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db).$sql));
                
            $wallet = $res->fetch_assoc();
                
            $record->userpaymentmethod->wallet = $wallet["wallet"];
            
            $sql = "SELECT * FROM currencies WHERE id = ".$accountdata["accountcurrencyid"];
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db).$sql));
                
            $currencydata = $res->fetch_assoc();
                
            $record->userpaymentmethod->currency = new stdClass();
            $record->userpaymentmethod->currency->id = (int)$currencydata["id"];
            $record->userpaymentmethod->currency->name = $currencydata["name"];
            $record->userpaymentmethod->currency->symbol = $currencydata["symbol"];
            $record->userpaymentmethod->currency->interface = $currencydata["interface"];
            
            $record->userpaymentmethod->details1 = $accountdata["details1"];
            $record->userpaymentmethod->details2 = $accountdata["details2"];
            $record->userpaymentmethod->details3 = $accountdata["details3"];
            $record->userpaymentmethod->details4 = $accountdata["details4"];
        }
        
        //Si es una transferencia necesitamos consultar el movimiento con su amountghost por si fue rechazada
        /*
        if($record->type->id == 3 || $record->type->id == 4){
            $sql = "SELECT * FROM transactions".
            "       WHERE id != ".$record->id.
            "       AND txid = ".$record->id.
            "       AND dsc like '%comision%'";
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db).$sql));
            
            $txdataFee = $res->fetch_assoc();
            //Monto del movimiento
            $record->amount = new stdClass();
            $record->amount->number = (abs($row["amount"]) + abs($row["fee"])) + (abs($txdataFee["amount"]) + abs($txdataFee["amountghost"]));
            $record->amount->formatted = numberFormatt($record->amount->number,$currencydata["decimals"]);
        }else{
            //Monto del movimiento
            $record->amount = new stdClass();
            $record->amount->number = (float)$row["amount"] ;
            $record->amount->formatted = numberFormatt($record->amount->number,$currencydata["decimals"]);
        }
        */
        /*
        //Si es una transferencia necesitamos consultar el movimiento con su comision
        if($record->type->id == 3 || $record->type->id == 4){
            //Consultamos el movimiento de comision de la cuenta
            $sql = "SELECT * FROM transactions".
            "       WHERE id != ".$record->id.
            "       AND txid = ".$record->id.
            "       AND dsc like '%comision%'";
            if (!$res=$db->query($sql)){
                badEnd("500", array("msg"=>determinateDBError($db).$sql));
            }
                
            $row = $res->fetch_assoc();
            
            //Comision del movimiento
            $record->fee = new stdClass();
            $record->fee->number = abs((float)$row["amount"] + $row["amountghost"]);
            $record->fee->formatted = numberFormatt($record->fee->number,$currencydata["decimals"]);
        }else{
            //Comision del movimiento
            $record->fee = new stdClass();
            $record->fee->number = (float)$row["fee"];
            $record->fee->formatted = numberFormatt($record->fee->number,$currencydata["decimals"]);
        }
        */
        //Comision del movimiento
        $record->fee = new stdClass();
        $record->fee->number = (float)0;
        $record->fee->formatted = numberFormatt($record->fee->number,$row["accdecimals"]);
        
        $records [] = $record;
    }
    
    $out->records = $records;
    
    header("HTTP/1.1 200");
    echo (json_encode($out));
    die();

    
    /*  Funcion para deteriminar el orden de los resultados
    *   default = mas reciente
    *   1 = Fecha de creacion ASCENDENTE
    *   2 = Nombre del usuario ASCENDENTE
    *   3 = Correo del usuario ASCENDENTE
    *   4 = Moneda ASCENDENTE
    *   5 = Estado del movimiento ASCENDENTE
    *   6 = Monto del movimiento ASCENDENTE
    *   7 = Tipo de movimiento ASCENDENTE
    *   -1 = Fecha de creacion DESCENDENTE
    *   -2 = Nombre del usuario DESCENDENTE
    *   -3 = Correo del usuario DESCENDENTE
    *   -4 = Moneda DESCENDENTE
    *   -5 = Estado del movimiento DESCENDENTE
    *   -6 = Monto del movimiento DESCENDENTE
    *   -7 = Tipo de movimiento DESCENDENTE
    */
    function determinateOrder($order){
        $str = "";
        
        switch($order){
            case 1:
                $str = " ORDER BY datecreated ASC ";
                break;
            case 2:
                $str = " ORDER BY personname ASC, companyname ASC ";
                break;
            case 3:
                $str = " ORDER BY usr ASC ";
                break;
            case 4:
                $str = " ORDER BY accountcurrencyid ASC ";
                break;
            case 5:
                $str = " ORDER BY statusorder ASC ";
                break;
            case 6: 
                $str = " ORDER BY amount ASC ";
                break;
            case 7:
                $str = " ORDER BY A.dsc ASC ";
                break;
            case -1:
                $str = " ORDER BY datecreated DESC ";
                break;
            case -2:
                $str = " ORDER BY personname DESC, companyname DESC ";
                break;
            case -3:
                $str = " ORDER BY usr DESC ";
                break;
            case -4:
                $str = " ORDER BY accountcurrencyid DESC ";
                break;
            case -5:
                $str = " ORDER BY statusorder DESC ";
                break;
            case -6: 
                $str = " ORDER BY amount DESC ";
                break;
            case -7:
                $str = " ORDER BY A.dsc DESC ";
                break;
            default:
                $str = " ORDER BY datecreated DESC ";
                break;
        }
        
        return $str;
    }
    
    /*  Funcion para determinar el tiempo de las transacciones
    *   Solo recibe el parametro $time que puede ser:
    *   0 = Todos
    *   1 = Hoy
    *   2 = Ultimos 7 días
    *   3 = Ultimos 30 días
    *   4 = Ultimos 3 meses
    *   5 = Ultimos 6 meses
    */
    function determinateTime($time){
        $str = "";
        
        switch($time){
            case 0:
                $str = "";
                break;
            case 1:
                $str = "DATE_SUB(NOW(), INTERVAL 1 DAY)";
                break;
            case 2:
                $str = "DATE_SUB(NOW(), INTERVAL 7 DAY)";
                break;
            case 3:
                $str = "DATE_SUB(NOW(), INTERVAL 30 DAY)";
                break;
            case 4:
                $str = "DATE_SUB(NOW(), INTERVAL 90 DAY)";
                break;
            case 5:
                $str = "DATE_SUB(NOW(), INTERVAL 6 MONTH)";
                break;
            default: 
                badEnd("500", array("msg"=>"El parametro fecha solo puede ser 0, 1, 2, 3, 4 o 5"));
                break;
        }
        
        return $str;
    }
?>
