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

    header("Content-Type:application/json");
    include("../dbconn.php");
    include("../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 = $_GET["sessionid"];
    $numofrec = $_GET['numofrec'];
    $offset = $_GET['offset'];
    $order = determinateOrder($_GET['order']);
    $daterange = '';
    $type = '';
    $currencyid = '';
    
    //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($_GET["daterange"]);
    }
    
    //En caso de que el type sea enviado, se construye el query
    if(isset($_GET["type"]) && $_GET["type"] != ""){
        $type = determinateType($_GET["type"]);
    }
    
    //EN caso que el currencyid sea enviado, se construye el query
    if(isset($_GET["currencyid"]) && $_GET["currencyid"] != ""){
        $currencyid = " AND currencies.id = ".$_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($_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 '%comis%' THEN 'Comisión' ".
                "               WHEN (SELECT COUNT(tx.id) FROM transactions tx WHERE tx.txid = t.txid) = 1 THEN 'Ajuste' ".
                "               WHEN (SELECT COUNT(tx.id) FROM transactions tx WHERE tx.txid = t.txid) = 8 THEN 'Intercambio' ".
                "               WHEN (SELECT COUNT(de.transactionid) FROM deposits de WHERE de.transactionid = t.txid) = 1 THEN 'Depósito' ".
                "               WHEN (SELECT COUNT(withdraw.transactionid) FROM withdrawals withdraw WHERE withdraw.transactionid = t.txid) = 1 THEN 'Retiro' ".
                "               WHEN (SELECT COUNT(tx.id) FROM transactions tx WHERE tx.txid = t.txid) = 4 THEN 'Transferencia' 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 users.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 '%comis%' THEN 'Comisión' ".
                "               WHEN (SELECT COUNT(tx.id) FROM transactions tx WHERE tx.txid = t.txid) = 1 THEN 'Ajuste' ".
                "               WHEN (SELECT COUNT(tx.id) FROM transactions tx WHERE tx.txid = t.txid) = 8 THEN 'Intercambio' ".
                "               WHEN (SELECT COUNT(de.transactionid) FROM deposits de WHERE de.transactionid = t.txid) = 1 THEN 'Depósito' ".
                "               WHEN (SELECT COUNT(withdraw.transactionid) FROM withdrawals withdraw WHERE withdraw.transactionid = t.txid) = 1 THEN 'Retiro' ".
                "               WHEN (SELECT COUNT(tx.id) FROM transactions tx WHERE tx.txid = t.txid) = 4 THEN 'Transferencia' 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 users.usr LIKE '%".$el."%' ".
                "           OR currencies.symbol LIKE '".$el."%'";
            }
        }
        
        $filter .= " ) ";
    }
    
    $sql = "SELECT COUNT(t.id) AS qty ".
    "       FROM transactions t, accounts, currencies, users ".
    "           LEFT JOIN usercompanies ON usercompanies.usrid = users.id ".
    "          	LEFT JOIN userpersons ON userpersons.usrid = users.id ".
    "       WHERE t.accountid = accounts.id ".
    "       AND accounts.userid = users.id ".
    "       AND accounts.currencyid = currencies.id ".
    "       AND users.id > 0 ".
    "       AND t.amount != 0 ".
    "       ".$currencyid.
    "       ".$type.
    "       ".$filter.
    "       ".$daterange;
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>$db->error));
        
    $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, ".
    "       CASE ".
    "       WHEN t.dsc LIKE '%comis%' THEN 'Comisión' ".
    "       WHEN (SELECT COUNT(tx.id) FROM transactions tx WHERE tx.txid = t.txid) = 1 THEN 'Ajuste' ".
    "       WHEN (SELECT COUNT(tx.id) FROM transactions tx WHERE tx.txid = t.txid) = 8 THEN 'Intercambio' ".
    "       WHEN (SELECT COUNT(de.transactionid) FROM deposits de WHERE de.transactionid = t.txid) = 1 THEN 'Depósito' ".
    "       WHEN (SELECT COUNT(withdraw.transactionid) FROM withdrawals withdraw WHERE withdraw.transactionid = t.txid) = 1 THEN 'Retiro' ".
    "       WHEN (SELECT COUNT(tx.id) FROM transactions tx WHERE tx.txid = t.txid) = 4 THEN 'Transferencia' END AS type,".
    "       users.usr AS usr, ".
    "       users.id AS userid, ".
    "       userpersons.fname AS personname, ".
    "       userpersons.flastname AS personlastname, ".
    "       usercompanies.name AS companyname, ".
    "       usercompanies.comercialname AS companycomercialname ".
    "       FROM transactions t, accounts, currencies, users ".
    "           LEFT JOIN usercompanies ON usercompanies.usrid = users.id ".
    "          	LEFT JOIN userpersons ON userpersons.usrid = users.id ".
    "       WHERE t.accountid = accounts.id ".
    "       AND accounts.userid = users.id ".
    "       AND accounts.currencyid = currencies.id ".
    "       AND users.id > 0 ".
    "       AND t.amount != 0 ".
    "       ".$currencyid.
    "       ".$type.
    "       ".$daterange.
    "       ".$filter.
    "       ".$order.
    "       LIMIT ".$offset.", ".$numofrec;
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>$db->error));
        
    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){
        $range = '';
        
        switch($daterange){
            case 1:
                $range = "AND t.datecreated >= CURDATE()";
                break;
            case 2:
                $range = "AND t.datecreated >= DATE_ADD(NOW(), INTERVAL -7 DAY)";
                break;
            case 3:
                $range = "AND t.datecreated >= DATE_ADD(NOW(), INTERVAL -30 DAY)";
                break;
            case 4:
                $range = "AND t.datecreated >= DATE_ADD(NOW(), INTERVAL -90 DAY)";
                break;
            case 5:
                $range = "AND t.datecreated >= DATE_ADD(NOW(), INTERVAL -5 MONTH)";
                break;
            default: 
                badEnd("500", array("msg"=>"El parametro fecha solo puede ser 1, 2, 3, 4 o 5"));
                break;
        }
        
        return $range;
    }
    
    /* Funcion para determinar el tipo de operacion a obtener, donde:
    *   1 = Depositos
    *   2 = Retiros
    *   3 = Transferencias
    *   4 = Intercambios
    *   5 = Comisiones
    */
    function determinateType($type){
        $query = '';
        
        switch($type){
            case 1:
                $query = " AND (CASE ".
                "       WHEN t.dsc LIKE '%comis%' THEN 5 ".
                "       WHEN (SELECT  COUNT(tx.id) FROM transactions tx WHERE tx.txid = t.txid) = 1 THEN 6 ".
                "       WHEN (SELECT  COUNT(tx.id) FROM transactions tx WHERE tx.txid = t.txid) = 8 THEN 4 ".
                "       WHEN (SELECT COUNT(de.transactionid) FROM deposits de WHERE de.transactionid = t.txid) = 1 THEN 1 ".
                "       WHEN (SELECT COUNT(withdraw.transactionid) FROM withdrawals withdraw WHERE withdraw.transactionid = t.txid) = 1 THEN 2 ".
                "       WHEN (SELECT COUNT(tx.id) FROM transactions tx WHERE tx.txid = t.txid) = 4 THEN 3 END) = 1";
                break;
            case 2:
                $query = " AND (CASE ".
                "       WHEN t.dsc LIKE '%comis%' THEN 5 ".
                "       WHEN (SELECT  COUNT(tx.id) FROM transactions tx WHERE tx.txid = t.txid) = 1 THEN 6 ".
                "       WHEN (SELECT  COUNT(tx.id) FROM transactions tx WHERE tx.txid = t.txid) = 8 THEN 4 ".
                "       WHEN (SELECT COUNT(de.transactionid) FROM deposits de WHERE de.transactionid = t.txid) = 1 THEN 1 ".
                "       WHEN (SELECT COUNT(withdraw.transactionid) FROM withdrawals withdraw WHERE withdraw.transactionid = t.txid) = 1 THEN 2 ".
                "       WHEN (SELECT COUNT(tx.id) FROM transactions tx WHERE tx.txid = t.txid) = 4 THEN 3 END) = 2";
                break;
            case 3:
                $query = " AND (CASE ".
                "       WHEN t.dsc LIKE '%comis%' THEN 5 ".
                "       WHEN (SELECT  COUNT(tx.id) FROM transactions tx WHERE tx.txid = t.txid) = 1 THEN 6 ".
                "       WHEN (SELECT  COUNT(tx.id) FROM transactions tx WHERE tx.txid = t.txid) = 8 THEN 4 ".
                "       WHEN (SELECT COUNT(de.transactionid) FROM deposits de WHERE de.transactionid = t.txid) = 1 THEN 1 ".
                "       WHEN (SELECT COUNT(withdraw.transactionid) FROM withdrawals withdraw WHERE withdraw.transactionid = t.txid) = 1 THEN 2 ".
                "       WHEN (SELECT COUNT(tx.id) FROM transactions tx WHERE tx.txid = t.txid) = 4 THEN 3 END) = 3";
                break;
            case 4:
                $query = " AND (CASE ".
                "       WHEN t.dsc LIKE '%comis%' THEN 5 ".
                "       WHEN (SELECT  COUNT(tx.id) FROM transactions tx WHERE tx.txid = t.txid) = 1 THEN 6 ".
                "       WHEN (SELECT  COUNT(tx.id) FROM transactions tx WHERE tx.txid = t.txid) = 8 THEN 4 ".
                "       WHEN (SELECT COUNT(de.transactionid) FROM deposits de WHERE de.transactionid = t.txid) = 1 THEN 1 ".
                "       WHEN (SELECT COUNT(withdraw.transactionid) FROM withdrawals withdraw WHERE withdraw.transactionid = t.txid) = 1 THEN 2 ".
                "       WHEN (SELECT COUNT(tx.id) FROM transactions tx WHERE tx.txid = t.txid) = 4 THEN 3 END) = 4";
                break;
            case 5:
                $query = " AND (CASE ".
                "       WHEN t.dsc LIKE '%comis%' THEN 5 ".
                "       WHEN (SELECT  COUNT(tx.id) FROM transactions tx WHERE tx.txid = t.txid) = 1 THEN 6 ".
                "       WHEN (SELECT  COUNT(tx.id) FROM transactions tx WHERE tx.txid = t.txid) = 8 THEN 4 ".
                "       WHEN (SELECT COUNT(de.transactionid) FROM deposits de WHERE de.transactionid = t.txid) = 1 THEN 1 ".
                "       WHEN (SELECT COUNT(withdraw.transactionid) FROM withdrawals withdraw WHERE withdraw.transactionid = t.txid) = 1 THEN 2 ".
                "       WHEN (SELECT COUNT(tx.id) FROM transactions tx WHERE tx.txid = t.txid) = 4 THEN 3 END) = 5";
                break;
            case 6:
                $query = " AND (CASE ".
                "       WHEN t.dsc LIKE '%comis%' THEN 5 ".
                "       WHEN (SELECT  COUNT(tx.id) FROM transactions tx WHERE tx.txid = t.txid) = 1 THEN 6 ".
                "       WHEN (SELECT  COUNT(tx.id) FROM transactions tx WHERE tx.txid = t.txid) = 8 THEN 4 ".
                "       WHEN (SELECT COUNT(de.transactionid) FROM deposits de WHERE de.transactionid = t.txid) = 1 THEN 1 ".
                "       WHEN (SELECT COUNT(withdraw.transactionid) FROM withdrawals withdraw WHERE withdraw.transactionid = t.txid) = 1 THEN 2 ".
                "       WHEN (SELECT COUNT(tx.id) FROM transactions tx WHERE tx.txid = t.txid) = 4 THEN 3 END) = 6";
                break;
        }
        
        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 users.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 users.usr DESC';
                break;
            default:
                $query = ' ORDER BY t.datecreated DESC, t.id DESC';
                break;
        }
        
        return $query;
        
    }
?>
