<?php
// cms/api/transactions/list

    header("Content-Type:application/json");
    include("../../../settings/dbconn.php");
    include("../../../settings/utils.php");
    
    // parametros obligatorios
    $parmsob = array("sessionid","numofrec",'offset');
    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"]);
    $numofrec = mysqli_real_escape_string($db, $_GET['numofrec']);
    $offset = mysqli_real_escape_string($db, $_GET['offset']);
    $order = determinateOrder(mysqli_real_escape_string($db, $_GET['order']));
    $daterange = '';
    $type = '';
    $currencyperfid = '';
    $accountType = ' AND us.id > 0 ';
    
    //Validamos que la session del usuario sea valida para continuar
    isSessionValidCMS($db, $sessionid);
    
    //Validamos que el usuario tenga privilegios
    if(!tienePrivilegio($db, $sessionid, 40))
        badEnd("403", array("msg"=>"No tiene Privilegios"));
        
    //En caso de que el daterange sea enviado, se construye el query
    if(isset($_GET["daterange"]) && $_GET["daterange"] != ""){
        $daterange = determinateRange(mysqli_real_escape_string($db, $_GET["daterange"]));
    }
    
    //En caso que el accounttype sea enviado, se construye el query
    $accfilt = false;
    if(isset($_GET['accounttype']) && $_GET['accounttype'] != ""){
        if($_GET['accounttype'] == -1){
            $accountType = ' AND us.id = -1 ';
        }else{
            $accountType= ' AND us.id > 0 ';
            $accfilt = true;
        }
    }

    //En caso de que el type sea enviado, se construye el query
    if(isset($_GET["type"]) && $_GET["type"] != ""){
        $type = determinateType(mysqli_real_escape_string($db, $_GET["type"]),$accfilt)." ";
    }
    
    //EN caso que el currencyid sea enviado, se construye el query
    if(isset($_GET["currencyid"]) && $_GET["currencyid"] != ""){
        $currencyperfid = " AND a.currencyid = ".mysqli_real_escape_string($db, $_GET["currencyid"])." ";
    }
    
    //En caso de que el filter sea enviado, se construye el query
    if(isset($_GET["filter"]) && $_GET["filter"] != ""){
        // Iniciamos la cadena del query
        $filter = " AND ( ";
        
        //Separamos el filtro por el simbolo +
        $filterEl = separateFilter(mysqli_real_escape_string($db, $_GET["filter"]));
        
        //Recorremos cada elemento
        foreach($filterEl as $index => $el){
            //Si la fecha tiene / lo partimos en mil
            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));
            }
            
            if($index == 0){
                $filter .= " usercompanies.name LIKE '%".$el."%' ".
                "           OR userpersons.fname LIKE '%".$el."%' ".
                "           OR IF(A.amountghost = 0, A.dsc, CONCAT(A.dsc, ' - Rechazado')) LIKE '".$el."%' ".
                "           OR DATE(A.datecreated) = DATE('".$dateel."') ".
                "           OR A.id = '".$el."'".
                "           OR A.typedsc LIKE '".$el."%'".
                "           ".(validNumber($el) ?  'OR A.amount = '.floatval($el) : (validNumberFormatted($el) ? 'OR A.amount = '.parseFormattedNumber($el) : '')).
                "           ".(validNumber($el) ?  'OR A.amount = -'.floatval($el) : (validNumberFormatted($el) ? 'OR A.amount = -'.parseFormattedNumber($el) : '')).
                "           OR us.usr LIKE '%".$el."%' ".
                "           OR currencies.symbol LIKE '".$el."%'";
            }else{
                $filter .= " OR usercompanies.name LIKE '%".$el."%' ".
                "           OR userpersons.fname LIKE '%".$el."%' ".
                "           OR IF(A.amountghost = 0, A.dsc, CONCAT(A.dsc, ' - Rechazado')) '".$el."%' ".
                "           OR DATE(A.datecreated) = DATE('".$dateel."') ".
                "           OR A.id = '".$el."'".
                "           OR A.typedsc LIKE '".$el."%'".
                "           ".(validNumber($el) ?  'OR A.amount = '.floatval($el) : (validNumberFormatted($el) ? 'OR A.amount = '.parseFormattedNumber($el) : '')).
                "           ".(validNumber($el) ?  'OR A.amount = -'.floatval($el) : (validNumberFormatted($el) ? 'OR A.amount = -'.parseFormattedNumber($el) : '')).
                "           OR us.usr LIKE '%".$el."%' ".
                "           OR currencies.symbol LIKE '".$el."%'";
            }
        }
        
        $filter .= " ) ";
    }
    
    $sqlDeposits =  "SELECT         t.id, t.dsc, ".
                    "               IF(d.valid IS NOT NULL, 1, " . //Si es valido, si no validamos el rechazado
                    "               IF(d.rejected IS NOT NULL, -1, 0)) AS status, " . //Si tampoco est�� rechazado pendiente
                    "               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, ".
                	"               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 " .
                    "               ".$currencyperfid.
                    "               AND a.entityusrid = -1 ";
    
    $sqlWithdraw =  "SELECT         t.id, t.dsc, ".
                    "               IF(d.valid IS NOT NULL, 1, " . //Si es valido, si no validamos el rechazado
                    "               IF(d.rejected IS NOT NULL, -1, 0)) AS status, " . //Si tampoco est�� rechazado pendiente
                    "               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, ".
                	"               IF(t.id <> t.txid, 'Comisi&oacuten', 'Retiro') AS typedsc, ".
                    "               IF(t.id <> t.txid, 'FEE', 'WITH') AS type, ".
                    "               'WITH' AS 'group' " .
                    "FROM           transactions t ".
                    "INNER JOIN     withdrawals d ON d.transactionid = t.txid ".
                    "INNER JOIN     accounts a " .
                    "ON             t.accountid = a.id " .
                    "               ".$currencyperfid.
                    "               AND a.entityusrid = -1 ";
                    
    $sqlXch     =   "SELECT         t.id, t.dsc, ".
                    "               '1' AS status, " .
                    "               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, ".
                	"               IF(adest.paymentmethodid<>-1, 'Comisi&oacuten', 'Intercambio') AS typedsc, ".
                    "               IF(adest.paymentmethodid<>-1, 'FEE', 'XCH') AS type, ".
		            "               'XCH' AS 'group' " .
                    "FROM           transactions t " . 
                    "INNER JOIN     orders o ON o.id = t.orderid " .
                    "INNER JOIN     accounts a " .
                    "ON             t.accountid = a.id " .
                    "               ".$currencyperfid.
                    "               AND a.entityusrid = -1 " .
                	"LEFT JOIN      accounts adest ON adest.id = t.accountdestination ";
                	
                	
    $sqlXfer     =  "SELECT         t.id, t.dsc, ".
                    "               IF(t.paypending = 1, 0, 1) AS status, " .
                    "               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, ".
                	"               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 ".
                	"               ".$currencyperfid.
                	"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 ";
                	
    $sqlPaytx =     "SELECT         t.id, t.dsc, ".
                    "               IF(t.paypending = 1, 0, 1) AS status, " .
                    "               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, ".
                	"               IF(adest.paymentmethodid <> -1, 'Comisi&oacuten', 'Pagos AFX Pay') AS typedsc, ".
                    "               IF(adest.paymentmethodid <> -1, 'FEE', 'PAYTX') AS type, " .
                    "               'PAYTX' AS 'group' " .
                    "FROM           transactions t ".
                    "INNER JOIN     paytxdetails pd ON pd.transactionid = t.txid ".
                    "INNER JOIN     accounts a " .
                    "ON             t.accountid = a.id " .
                    "               ".$currencyperfid.
                    "               AND a.entityusrid = -1 " .
                    "LEFT JOIN      accounts adest ON adest.id = t.accountdestination";
                    
        //Movimientos de movil sin sumar las comisiones
        $sqlPM =        "       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 " .
    			        "       FROM            transactions t " .
        		        "       INNER JOIN      accounts a ON t.accountid = a.id ".
        		        "                       ".$currencyperfid.
        		        "       INNER JOIN      pagomovildetails pd ON pd.transactionid = t.txid ".
    			        "       LEFT JOIN       accounts adest ON adest.id = t.accountdestination" .
    			        "       ORDER BY        t.id ";
    			      
    	
    	$sqlPMThird =        "       SELECT  	    t.id, t.txid, t.dsc, pp.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 " .
    			        "       FROM            transactions t " .
        		        "       INNER JOIN      accounts a ON t.accountid = a.id ".
        		        "                       ".$currencyperfid.
        		        "       INNER JOIN      payments_pagomovil pp ON pp.txid = t.txid AND pp.status != 3".
    			        "       LEFT JOIN       accounts adest ON adest.id = t.accountdestination" .
    			        "       ORDER BY        t.id ";
    
    $sqlPMGroup =   "SELECT         PM.id, PM.dsc, ".
                    "               IF(PM.ref IS NOT NULL, 1, 0) AS status, " .
                    "               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.accountid, ".
                	"               PM.typedsc AS typedsc, " .
                    "               PM.type AS type, " .
                    "               'PMPAY' AS 'group' " .
                    "FROM           ( (" . $sqlPM . ") UNION ".
                    "               (".$sqlPMThird.")) PM " .
                    "INNER JOIN      accounts a ON PM.accountid = a.id ".
        		    "                 ".$currencyperfid.
                    "WHERE          PM.id IS NOT NULL " .
                    "AND            PM.paypending = 0 AND PM.validator IS NULL " . //Filtro los que si se confirmaron con código
			        "GROUP BY       PM.id";
    
                
    $sqlAjustes =   "SELECT         t.id, t.dsc, ".
                    "               '1' AS status, " .
                    "               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, ".
                	"               'Ajuste' AS typedsc, " .
                    "               'AJT' AS type, " .
                    "               'AJT' AS 'group' " .
                    "FROM           transactions t " .
                    "INNER JOIN     accounts a " .
                    "               ON  t.accountid = a.id " .
                    "               ".$currencyperfid.
                    "               AND a.entityusrid = -1 " .
                    "LEFT JOIN (" . $sqlDeposits . " UNION " .
                    "           " . $sqlWithdraw . " UNION " .
                    "           " . $sqlXch .      " UNION " .
                    "           " . $sqlXfer .     " UNION " .
                    "           " . $sqlPaytx .    ") AL ON AL.id = t.id " .
                    "LEFT JOIN (" . $sqlPM . ") PM ON PM.id = t.id " .
                    "LEFT JOIN (" . $sqlPMThird . ") PMThird ON PMThird.id = t.id " .
                    "WHERE          AL.id IS NULL " . //Que no sea un movimiento de pago movil o ningún anterior          	
                	"               AND PM.id IS NULL".
                	"               AND PMThird.id IS NULL";
    
     $sql = "SELECT     COUNT(A.id) AS qty " .
            "FROM ( " . $sqlDeposits . " UNION " .
            "       " . $sqlWithdraw . " UNION " .
            "       " . $sqlXch .      " UNION " .
            "       " . $sqlXfer .     " UNION " .
            "       " . $sqlPaytx .    " UNION " .
            "       " . $sqlPMGroup .  " UNION " .
            "       " . $sqlAjustes . "  ) A " .
            "INNER JOIN accounts ON A.accountid = accounts.id " .
            "INNER JOIN currencies ON accounts.currencyid = currencies.id " .
            "INNER JOIN users us ON accounts.userid = us.id ".
            "LEFT JOIN usercompanies ON usercompanies.usrid = us.id ".
            "LEFT JOIN userpersons ON userpersons.usrid = us.id ".
            "       WHERE A.id IS NOT NULL ". // Esto lo hago para montar un where
            "       ".$accountType.
            "       ".$daterange.
            "       ".$filter.
            "       ".$type;
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
        
    $row = $rs->fetch_assoc();
    $out->numofrecords = (int)$row['qty'];
    
    $sql =  "SELECT     A.*, " .
        "       IF(A.amountghost = 0, A.dsc, CONCAT(A.dsc, ' - Rechazado')) AS dsc, ".
        "       currencies.id AS currid, " .
        "       currencies.name AS currname, " .
        "       currencies.symbol AS currsymbol, " .
        "       currencies.icon AS icon, " .
        "       us.usr AS usr, ".
        "       us.id AS userid, ".
        "       userpersons.fname AS personname, ".
        "       userpersons.flastname AS personlastname, ".
        "       usercompanies.name AS companyname, ".
        "       usercompanies.comercialname AS companycomercialname, ".
        "       currencies.decimals AS decimals " .
        "FROM ( " . $sqlDeposits . " UNION " .
        "       " . $sqlWithdraw . " UNION " .
        "       " . $sqlXch .      " UNION " .
        "       " . $sqlXfer .     " UNION " .
        "       " . $sqlPaytx .    " UNION " .
        "       " . $sqlPMGroup .  " UNION " .
        "       " . $sqlAjustes . "  ) A " .
        "INNER JOIN accounts ON A.accountid = accounts.id " .
        "INNER JOIN currencies ON accounts.currencyid = currencies.id " .
        "INNER JOIN users us ON accounts.userid = us.id ".
        "LEFT JOIN usercompanies ON usercompanies.usrid = us.id ".
        "LEFT JOIN userpersons ON userpersons.usrid = us.id ".
        "       WHERE A.id IS NOT NULL ". // Esto lo hago para montar un where
        "       ".$accountType.
        "       ".$type.
        "       ".$daterange.
        "       ".$filter.
        "       ".$order.
        "       LIMIT ".$offset.", ".$numofrec;
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
        
    while($row = $rs->fetch_assoc()){
        $record = new stdClass();
        $record->id = (int)$row['id'];
        
        $record->amount = new stdClass();
        $record->amount->number = $row['amount'];
        $record->amount->formatted = numberFormatt($row['amount'],$row['decimals']);
        
        $record->currency = new stdClass();
        $record->currency->id = (int)$row['currid'];
        $record->currency->symbol = $row['currsymbol'];
        
        $record->user = new stdClass();
        $record->user->id = (int)$row['userid'];
        $record->user->usr = $row['usr'];
        
        if($row["personname"] == null){
            $record->user->fullname = $row['companyname'].( $row['companycomercialname'] == "" ? '' : ' / '.$row['companycomercialname'] );
        }else{
            $record->user->fullname = $row['personname'].( $row['personlastname'] == "" ? '' : ' '.$row['personlastname'] );
        }
        
        $record->date = new stdClass();
        $record->date->cannonical = date("Y-m-d h:i a", strtotime($row["datecreated"]));
        $record->date->formatted = date("d/m/Y", strtotime($record->date->cannonical))." - ".date("h:i a", strtotime($record->date->cannonical));
        
        $record->description = $row['dsc'];
        
        $record->type = $row['typedsc'];
        $record->typedsc = $row['type'];
        
        $records [] = $record;
    }
    
    //Retornamos los privilegios del usuario en este modulo
    $access = new stdClass();
    $access->create = tienePrivilegio($db, $sessionid, 37);
    $access->read =   tienePrivilegio($db, $sessionid, 40);
    $access->update = tienePrivilegio($db, $sessionid, 38);
    $access->delete = tienePrivilegio($db, $sessionid, 39);
    $out->privileges = $access;
    
    $out->records = $records;
    header("HTTP/1.1 200");
    echo (json_encode($out));
    die();
    
    /* Funcion para determinar el rango de operaciones, donde:
    *   1 = Hoy
    *   2 = Ultimos 7 dias
    *   3 = Ultimos 30 dias
    *   4 = Ultimos 3 meses
    *   5 = Ultimos 5 meses
    */
    function determinateRange($daterange){
        $str = '';
        
        switch($daterange){
            case 1:
                $str = " AND DATE(A.datecreated) >= DATE_SUB(NOW(), INTERVAL 1 DAY)";
                break;
            case 2:
                $str = " AND DATE(A.datecreated) >= DATE_SUB(NOW(), INTERVAL 7 DAY)";
                break;
            case 3:
                $str = " AND DATE(A.datecreated) >= DATE_SUB(NOW(), INTERVAL 30 DAY)";
                break;
            case 4:
                $str = " AND DATE(A.datecreated) >= DATE_SUB(NOW(), INTERVAL 90 DAY)";
                break;
            case 5:
                $str = " AND DATE(A.datecreated) >= DATE_SUB(NOW(), INTERVAL 6 MONTH)";
                break;
            default: 
                badEnd("500", array("msg"=>"El parametro fecha solo puede ser 1, 2, 3, 4 o 5"));
                break;
        }
        
        return $str;
    }
    
    /* Funcion para determinar el tipo de operacion a obtener, donde:
    *   1 = Depositos
    *   2 = Retiros
    *   3 = Transferencias
    *   4 = Intercambios
    *   5 = Comisiones
    */
    function determinateType($type, $accountType){
        if($accountType){            
            $aditional = " AND A.type <> 'FEE' ";
        }
        switch($type){
            case 1:
                $query = " AND A.group = 'DEP'".$aditional;
                break;
            case 2:
                $query = " AND A.group = 'WITH'".$aditional;
                break;
            case 3:
                $query = " AND A.group = 'XFER'".$aditional;
                break;
            case 4:
                $query = " AND A.group = 'XCH'".$aditional;
                break;
            case 5:
                $query = " AND A.type = 'FEE'";
                break;
            case 6:
                $query = " AND A.group = 'AJT'".$aditional;
                break;
            case 7:
                $query = " AND A.group = 'PAYTX'".$aditional;
                break;
            case 8:
                $query = " AND A.group = 'PMPAY'".$aditional;
                break;
            default:
                $query = "";
        }
        
        return $query;
    }
    
    /* Funcion para determinar el orden de los resultados, donde:
    *   1 = Datetime(DEFAULT)
    *   2 = Monto
    *   3 = Usario
    */
    function determinateOrder($order){
        $query = '';
        
        switch($order){
            case 1:
                $query = ' ORDER BY A.datecreated DESC, A.id DESC';
                break;
            case 2:
                $query = ' ORDER BY A.amount DESC';
                break;
            case 3:
                $query = ' ORDER BY us.usr ASC';
                break;
            case -1:
                $query = ' ORDER BY A.datecreated ASC, A.id DESC';
                break;
            case -2:
                $query = ' ORDER BY A.amount ASC';
                break;
            case -3:
                $query = ' ORDER BY us.usr DESC';
                break;
            default:
                $query = ' ORDER BY A.datecreated DESC, A.id DESC';
                break;
        }
        
        return $query;
        
    }
?>