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

    header("Content-Type:application/json");
    include("../../../settings/dbconn-performance.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 = '';
    $currencyid = '';
    $accountType = ' AND us.id > 0 ';
    
    $caseType = "(CASE ".
    "           WHEN t.dsc LIKE '%Comisión %' THEN 'Comisión' ".
    "           WHEN deposits.transactionid IS NOT NULL THEN 'Depósito' ".
    "           WHEN withdrawals.transactionid IS NOT NULL THEN 'Retiro' ".
    "           WHEN paytxdetails.transactionid IS NOT NULL THEN 'Pagos ".$shortname." Pay'".
    "           WHEN (t.paypending = 0 AND t.accountorigin IS NULL) AND t.txid = t.id THEN 'Ajuste' ".
    "           WHEN t.dsc LIKE '%Transferencia%' AND t.accountorigin IS NOT NULL THEN 'Transferencia' ".
    "           WHEN t.dsc LIKE '%Intercambio%' AND t.accountorigin IS NOT NULL THEN 'Intercambio' ".
    "           ELSE 'No definido'".
    "           END) AS type,";
    
    //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 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"]));
    }
    
    //EN caso que el currencyid sea enviado, se construye el query
    if(isset($_GET["currencyid"]) && $_GET["currencyid"] != ""){
        $currencyid = " AND currencies.id = ".mysqli_real_escape_string($db, $_GET["currencyid"]);
    }
    
    //En caso que el accounttype sea enviado, se construye el query
    if(isset($_GET['accounttype']) && $_GET['accounttype'] != ""){
        if($_GET['accounttype'] == -1){
            $accountType = ' AND us.id = -1 ';
            $caseType = " CASE ".
            "           WHEN t.dsc LIKE '%Comisión %' THEN 'Comisión' ".
            "           WHEN deposits.transactionid IS NOT NULL THEN 'Depósito' ".
            "           WHEN withdrawals.transactionid IS NOT NULL THEN 'Retiro' ".
            "           WHEN paytxdetails.transactionid IS NOT NULL THEN 'Pagos ".$shortname." Pay'".
            "           WHEN (t.paypending = 0 AND t.accountorigin IS NULL) AND t.txid = t.id THEN 'Ajuste' ".
            "           WHEN t.dsc LIKE '%Transferencia%' AND t.accountorigin IS NOT NULL THEN 'Transferencia' ".
            "           WHEN t.dsc LIKE '%Intercambio%' AND t.accountorigin IS NOT NULL THEN 'Intercambio' ".
            "           ELSE 'No definido'".
            "           END AS type,";
            
            if(isset($_GET["type"]) && $_GET["type"] != ""){
                $type = determinateType($_GET["type"]);
            }
        }else{
            $accountType= ' AND us.id > 0 ';
        }
    }
    
    //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 t.dsc LIKE '".$el."%' ".
                "           OR DATE(t.datecreated) = DATE('".$dateel."') ".
                "           OR t.id = '".$el."'".
                "           OR (CASE ".
                "               WHEN t.dsc LIKE '%Comisión %' THEN 'Comisión' ".
                "               WHEN deposits.transactionid IS NOT NULL THEN 'Depósito' ".
                "               WHEN withdrawals.transactionid IS NOT NULL THEN 'Retiro' ".
                "               WHEN paytxdetails.transactionid IS NOT NULL THEN 'Pagos ".$shortname." Pay'".
                "               WHEN (t.paypending = 0 AND t.accountorigin IS NULL) AND t.txid = t.id THEN 'Ajuste' ".
                "               WHEN t.dsc LIKE '%Transferencia%' AND t.accountorigin IS NOT NULL THEN 'Transferencia' ".
                "               WHEN t.dsc LIKE '%Intercambio%' AND t.accountorigin IS NOT NULL THEN 'Intercambio' ".
                "               ELSE 'No definido' ".
                "               END) LIKE '".$el."%'".
                "           ".(validNumber($el) ?  'OR t.amount = '.floatval($el) : (validNumberFormatted($el) ? 'OR t.amount = '.parseFormattedNumber($el) : '')).
                "           ".(validNumber($el) ?  'OR t.amount = -'.floatval($el) : (validNumberFormatted($el) ? 'OR t.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 t.dsc LIKE '".$el."%' ".
                "           OR DATE(t.datecreated) = DATE('".$dateel."') ".
                "           OR t.id = '".$el."'".
                "           OR (CASE ".
                "               WHEN t.dsc LIKE '%Comisión %' THEN 'Comisión' ".
                "               WHEN deposits.transactionid IS NOT NULL THEN 'Depósito' ".
                "               WHEN withdrawals.transactionid IS NOT NULL THEN 'Retiro' ".
                "               WHEN paytxdetails.transactionid IS NOT NULL THEN 'Pagos ".$shortname." Pay'".
                "               WHEN (t.paypending = 0 AND t.accountorigin IS NULL) AND t.txid = t.id THEN 'Ajuste' ".
                "               WHEN t.dsc LIKE '%Transferencia%' AND t.accountorigin IS NOT NULL THEN 'Transferencia' ".
                "               WHEN t.dsc LIKE '%Intercambio%' AND t.accountorigin IS NOT NULL THEN 'Intercambio' ".
                "               ELSE 'No definido' ".
                "               END) LIKE '".$el."%'".
                "           ".(validNumber($el) ?  'OR t.amount = '.floatval($el) : (validNumberFormatted($el) ? 'OR t.amount = '.parseFormattedNumber($el) : '')).
                "           ".(validNumber($el) ?  'OR t.amount = -'.floatval($el) : (validNumberFormatted($el) ? 'OR t.amount = -'.parseFormattedNumber($el) : '')).
                "           OR us.usr LIKE '%".$el."%' ".
                "           OR currencies.symbol LIKE '".$el."%'";
            }
        }
        
        $filter .= " ) ";
    }
    
    $sql = "SELECT COUNT(t.id) AS qty ".
    "       FROM currencies, transactions t ".
    "           JOIN accounts ON t.accountid = accounts.id".
    "           JOIN users us ON accounts.userid = us.id ".
    "           LEFT JOIN usercompanies ON usercompanies.usrid = us.id ".
    "          	LEFT JOIN userpersons ON userpersons.usrid = us.id ".
    "           LEFT JOIN deposits ON deposits.transactionid = t.id ".
    "           LEFT JOIN withdrawals ON withdrawals.transactionid = t.id ".
    "           LEFT JOIN paytxdetails ON paytxdetails.transactionid = t.id ".
    "       WHERE accounts.currencyid = currencies.id ".
    "       ".$accountType.
    //"       AND t.amount != 0 ". // 27/01/2021: se traen los movimientos EN 0
    "       ".$currencyid.
    "       ".$type.
    "       ".$filter.
    "       ".$daterange;
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
        
    $row = $rs->fetch_assoc();
    $out->numofrecords = (int)$row['qty'];
    
    $sql = "SELECT t.id AS txid, ".
    "       t.datecreated AS date, ".
    "       t.amount AS amount, ".
    "       t.dsc AS dsc, ".
    "       currencies.symbol AS currencysymbol, ".
    "       currencies.id AS currencyid, ".
    "       currencies.decimals AS decimals, ".
    "       t.txid AS txindexid, ".
    "       ".$caseType.
    "       us.usr AS usr, ".
    "       us.id AS userid, ".
    "       userpersons.fname AS personname, ".
    "       userpersons.flastname AS personlastname, ".
    "       usercompanies.name AS companyname, ".
    "       usercompanies.comercialname AS companycomercialname ".
    "       FROM currencies, transactions t ".
    "           JOIN accounts ON t.accountid = accounts.id".
    "           JOIN users us ON accounts.userid = us.id ".
    "           LEFT JOIN usercompanies ON usercompanies.usrid = us.id ".
    "          	LEFT JOIN userpersons ON userpersons.usrid = us.id ".
    "           LEFT JOIN deposits ON deposits.transactionid = t.id ".
    "           LEFT JOIN withdrawals ON withdrawals.transactionid = t.id ".
    "           LEFT JOIN paytxdetails ON paytxdetails.transactionid = t.id ".
    "       WHERE accounts.currencyid = currencies.id ".
    "       ".$accountType.
    "       ".$currencyid.
    "       ".$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->test = $row;
        $record->id = (int)$row['txid'];
        
        $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['currencyid'];
        $record->currency->symbol = $row['currencysymbol'];
        
        $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["date"]));
        $record->date->formatted = date("d/m/Y", strtotime($row["date"]))." - ".date("h:i a", strtotime($row["date"]));
        
        $record->description = $row['dsc'];
        
        $record->type = $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(t.datecreated) >= DATE_SUB(NOW(), INTERVAL 1 DAY)";
                break;
            case 2:
                $str = " AND DATE(t.datecreated) >= DATE_SUB(NOW(), INTERVAL 7 DAY)";
                break;
            case 3:
                $str = " AND DATE(t.datecreated) >= DATE_SUB(NOW(), INTERVAL 30 DAY)";
                break;
            case 4:
                $str = " AND DATE(t.datecreated) >= DATE_SUB(NOW(), INTERVAL 90 DAY)";
                break;
            case 5:
                $str = " AND DATE(t.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){
        switch($type){
            case 1:
                $query = " AND deposits.transactionid IS NOT NULL";
                break;
            case 2:
                $query = " AND withdrawals.transactionid IS NOT NULL";
                break;
            case 3:
                $query = " AND t.dsc LIKE '%Transferencia%' AND t.accountorigin IS NOT NULL";
                break;
            case 4:
                $query = " AND t.dsc LIKE '%Intercambio%' AND t.accountorigin IS NOT NULL";
                break;
            case 5:
                $query = " AND t.dsc LIKE '%Comisión %' ";
                break;
            case 6:
                $query = " AND (t.paypending = 0 AND t.accountorigin IS NULL) AND t.txid = t.id";
                break;
            case 7:
                $query = " AND paytxdetails.transactionid IS NOT NULL";
                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 t.datecreated DESC, t.id DESC';
                break;
            case 2:
                $query = ' ORDER BY t.amount DESC';
                break;
            case 3:
                $query = ' ORDER BY us.usr ASC';
                break;
            case -1:
                $query = ' ORDER BY t.datecreated ASC, t.id DESC';
                break;
            case -2:
                $query = ' ORDER BY t.amount ASC';
                break;
            case -3:
                $query = ' ORDER BY us.usr DESC';
                break;
            default:
                $query = ' ORDER BY t.datecreated DESC, t.id DESC';
                break;
        }
        
        return $query;
        
    }
?>
