<?php
// app/api/transactions/movements

    header("Content-Type:application/json");
    include("../../../settings/dbconn.php");
    include("../../../settings/utils.php");
    
    // parametros obligatorios
    $parmsob = array("sessionid","accountid","time");
    if (!parametrosValidos($_GET, $parmsob)){
        badEnd("400", array("msg"=>"Parametros obligatorios " . implode(", ", $parmsob)));
    }
    
    //Declaracion de variables
    $out = new stdClass();
    $sessionid = $_GET["sessionid"];
    $time = determinateTime($_GET["time"]);
    $accountid = $_GET["accountid"];
    $filter = "";
    
    //Validamos la session
    isSessionValid($db, $sessionid);
    
    //Obtenemos el lenguaje del usuario
    $lang = updateLang($db,$sessionid);
    
    //Consultamos la cuenta para validar que exista
    $sql = "SELECT count(id) AS qty FROM accounts WHERE accounts.entityusrid = -1 AND id = ".$accountid;
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
        
    $row = $rs->fetch_assoc();
    //Retornamos 404 si no existe
    if($row["qty"] == 0){
        badEnd("404",array("msg"=>determinateMsgFail(array(
            "esp"=>"Registro no encontrado",
            "eng"=>"Record not found"), $lang, "Registro no encontrado")));
    }
    
    //En caso de que el search sea enviado, se construye el query
    if(isset($_GET["search"]) && $_GET["search"] != ""){
        $filter = "AND t.dsc LIKE '".$_GET["search"]."%'";
    }
    
    //En caso de que el offset sea enviado, se construye el query
    if(isset($_GET["offset"]) && $_GET["offset"] != ""){
        $offset = $_GET["offset"];
    }else{
        $offset = "0";
    }
    
    //En caso de que el numofrec sea enviado, se construye el query
    if(isset($_GET["numofrec"]) && $_GET["numofrec"] != ""){
        $numofrec = $_GET["numofrec"];
    }
    
    if($offset != 0 && $numofrec == ""){
        $limit = " LIMIT ".$offset.", 18446744073709551615";
    }else if($offset != 0 && $numofrec != ""){
        $limit = " LIMIT ".$offset.", ".$numofrec;
    }else if($offset == 0 && $numofrec != ""){
        $limit = " LIMIT ".$numofrec;
    }else{
        $limit = " LIMIT ".$offset.", 18446744073709551615";
    }
    
    //Consultamos los detalles de la cuenta 
    $sql = "SELECT * ".
    "       FROM accounts ".
    "       WHERE id = ".$accountid.
    "       AND accounts.entityusrid = -1 ";
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
        
    $row = $rs->fetch_assoc();
    
    //Ordenamos los datos de la cuenta
    $out->account = new stdClass();
    $out->account->id = (int)$row["id"];
    $out->account->ref = $row["details1"];
    
    //Moneda de la cuenta
    $sql = "SELECT * ".
    "       FROM currencies ".
    "       WHERE id = ".$row["currencyid"];
    if (!$res=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
        
    $currency = $res->fetch_assoc();
    
    $out->account->currency = new stdClass();
    $out->account->currency->id = (int)$currency["id"];
    $out->account->currency->dsc = $currency["name"];
    $out->account->currency->symbol = $currency["symbol"];
    $out->account->currency->interface = (int)$currency["interface"];
    
    //Balance de la cuenta
    $sql = "SELECT SUM(amount) - ABS(IF(( ".
    "           SELECT SUM(CASE WHEN orders.side = 1 THEN orders.quantity ELSE orders.total END) AS amount ".
    "           FROM orders ".
    "           WHERE orders.accountid = ".$accountid.
	"           AND orders.canceled IS NULL ".
	"           AND orders.type != 1) IS NOT NULL,  ".
	"               ( ".
    "           SELECT SUM(CASE WHEN orders.side = 1 THEN orders.quantity ELSE orders.total END) AS amount ".
    "           FROM orders ".
    "           WHERE orders.accountid = ".$accountid.
	"           AND orders.canceled IS NULL ".
	"           AND orders.type != 1), 0)".
    "       + IF(( ".
    "           SELECT SUM(transactions.amount) AS amount ".
    "           FROM transactions, orders ".
    "           WHERE orders.id = transactions.orderid ".
    "           AND transactions.accountid = ".$accountid.
    "           AND orders.accountid = ".$accountid.
	"           AND orders.canceled IS NULL ".
	"           AND transactions.amount < 0 ".
	"           AND orders.type != 1) IS NOT NULL, ( ".
    "           SELECT SUM(transactions.amount) AS amount ".
    "           FROM transactions, orders ".
    "           WHERE orders.id = transactions.orderid ".
    "           AND transactions.accountid = ".$accountid.
    "           AND orders.accountid = ".$accountid.
	"           AND orders.canceled IS NULL ".
	"           AND transactions.amount < 0 ".
	"           AND orders.type != 1),0) ".
	"      + IF ((SELECT SUM(transactions.amount + transactions.amountghost) AS amount ".
	"           FROM transactions  ".
	"           WHERE transactions.paypending = 1".
	"           AND transactions.accountid = ".$accountid.
	"           AND NOT EXISTS(SELECT withdrawals.transactionid FROM withdrawals WHERE withdrawals.transactionid = transactions.id)) IS NOT NULL, ".
	"           (SELECT SUM(transactions.amount + transactions.amountghost) AS amount ".
	"           FROM transactions  ".
	"           WHERE transactions.paypending = 1".
	"           AND transactions.accountid = ".$accountid.
	"           AND NOT EXISTS(SELECT withdrawals.transactionid FROM withdrawals WHERE withdrawals.transactionid = transactions.id)), 0)".
	"      ) as qty FROM transactions WHERE accountid = ".$accountid.
    "       AND paypending = 0";
    if (!$res=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
    
    $balancetx = $res->fetch_assoc();
        
    $sql = "SELECT SUM(amount) AS qty ".
    "       FROM ( ".
    "           SELECT SUM(CASE WHEN orders.side = 1 THEN orders.quantity ELSE orders.total END) AS amount ".
    "           FROM orders ".
    "           WHERE orders.accountid = ".$accountid.
	"           AND orders.canceled IS NULL ".
	"           AND orders.type != 1 ".
    "       UNION ALL ".
    "           SELECT SUM(transactions.amount) AS amount ".
    "           FROM transactions, orders ".
    "           WHERE orders.id = transactions.orderid ".
    "           AND transactions.accountid = ".$accountid.
    "           AND orders.accountid = ".$accountid.
	"           AND orders.canceled IS NULL ".
	"           AND transactions.amount < 0 ".
	"           AND orders.type != 1 ".
	"       UNION ALL ".
	"           SELECT SUM(ABS(transactions.amount) + ABS(transactions.amountghost)) AS amount ".
	"           FROM transactions  ".
	"           WHERE transactions.paypending = 1".
	"           AND transactions.accountid = ".$accountid.
	"           AND NOT EXISTS(SELECT withdrawals.transactionid FROM withdrawals WHERE withdrawals.transactionid = transactions.id)".
	"      UNION ALL ".
	"           SELECT SUM(ABS(withdrawals.amount) + ABS(withdrawals.fee)) AS amount ".
	"           FROM transactions, withdrawals ".
	"           WHERE transactions.id = withdrawals.transactionid ".
	"           AND transactions.accountid = ".$accountid.
	"           AND withdrawals.valid IS NUll ".
	"           AND withdrawals.rejected IS NULL ".
    "       ) amount";
    if (!$res=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
    
    $balanceorder = $res->fetch_assoc();
        
    //Balance a partir de las transacciones
    $out->account->balance = new stdClass();
    
    $out->account->balance->number = (float)number_format((number_format($balancetx["qty"],$currency['decimals'],'.','')),$currency['decimals'],'.','');
    $out->account->balance->number = $out->account->balance->number < 0 ? 0 : $out->account->balance->number;
    
    $out->account->balance->formatted = numberFormatt($out->account->balance->number,$currency["decimals"]);
    
    //Balance a partir de las transacciones
    $out->account->committed = new stdClass();
    $out->account->committed->number = (float)number_format($balanceorder["qty"],$currency['decimals'],'.','');
    $out->account->committed->formatted = numberFormatt($out->account->committed->number,$currency["decimals"]);
    
    //Balance pendiente de la cuenta
	$sql = "SELECT SUM(deposits.amount - deposits.fee) as qty ".
    "       FROM accounts, transactions, deposits ".
    "       WHERE transactions.id = deposits.transactionid ".
    "       AND deposits.rejected IS NULL ".
    "       AND deposits.valid IS NULL ".
    "       AND accounts.id = transactions.accountid ".
    "       AND accounts.entityusrid = -1 ".
    "       AND accounts.id = ".$accountid.
    "       AND transactions.amountghost >= 0 ";
    if (!$res=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
        
    $balance = $res->fetch_assoc();
    
    $out->account->deferred = new stdClass();
    $out->account->deferred->number = (float)number_format($balance["qty"],$currency['decimals'],'.','');
    $out->account->deferred->formatted = numberFormatt($out->account->deferred->number,$currency["decimals"]);
    
    //Consultamos las transacciones
    $sql = "SELECT t.id AS id, ".
    "       t.dsc AS dsc, ".
    "       t.amount AS amount, ".
    "       DATE_FORMAT(t.datecreated, '%Y-%M-%d %H:%i') AS datecreated, ".
    "       t.amountghost AS amountghost, ".
    "       t.txid AS txid, ".
    "       (SELECT COUNT(tx.id) ".
    "           FROM transactions tx ".
    "           WHERE tx.txid = t.txid) AS txcount ".
    "       FROM transactions t ".
    "       WHERE t.accountid = ".$accountid.
    "       ".$time.
    "       ".$filter.
    "       ORDER BY t.datecreated DESC, id ASC";
    
    //Consultamos la cantidad de registros
    $sql = "SELECT COUNT(*) AS qty FROM (SELECT t.id AS id, 
           t.dsc AS  dsc, 
           t.amount AS amount, 
           DATE_FORMAT(t.datecreated, '%Y-%m-%d %H:%i') AS datecreated, 
           t.amountghost AS amountghost, 
           t.txid AS txid,
           (SELECT COUNT(tx.id) 
               FROM transactions tx 
               WHERE tx.txid = t.txid) AS txcount 
           FROM transactions t 
           WHERE t.accountid = $accountid
           AND t.validthru IS NULL
           
           $time
           $filter
           
           UNION
 
            SELECT o.id AS id,
 		    CONCAT('Orden de ',(CASE WHEN o.side = 1 THEN 'Venta' ELSE 'Compra' END)) AS dsc,
            (CASE WHEN o.side = 1 THEN o.quantity ELSE o.total END) AS amount,
            DATE_FORMAT(o.date, '%Y-%m-%d %H:%i') AS datecreated,
       	    IF( 
                (SELECT SUM(ABS(transactions.amount)) 
                    FROM transactions, orders 
                    WHERE orders.id = transactions.orderid 
                    AND orders.id = o.id 
                    AND IF(o.side = 2, transactions.amount > 0, transactions.amount < 0) 
                    AND transactions.accountid = (CASE WHEN o.side = 1 THEN o.accountid ELSE o.paymentid END)) IS NOT NULL, 
                (SELECT SUM(ABS(transactions.amount))
                    FROM transactions, orders 
                    WHERE orders.id = transactions.orderid 
                    AND orders.id = o.id 
                    AND IF(o.side = 2, transactions.amount > 0, transactions.amount < 0) 
                    AND transactions.accountid = (CASE WHEN o.side = 1 THEN o.accountid ELSE o.paymentid END)), 
                0 ) AS amountghost,
            o.id AS txid,
            0 AS txcount
 	        FROM orders o WHERE o.accountid = $accountid. AND o.canceled IS NULL AND o.type != 1
    	       AND (CASE 
                       WHEN o.canceled IS NOT NULL THEN -1 
                       WHEN (SELECT SUM(ABS(transactions.amount)) 
                           FROM transactions, orders 
                           WHERE orders.id = transactions.orderid 
                           AND orders.id = o.id 
                           AND IF(o.side = 2, transactions.amount > 0, transactions.amount < 0) 
                           AND transactions.accountid = (CASE WHEN o.side = 1 THEN o.accountid ELSE o.paymentid END)) >= o.quantity 
                           AND o.canceled IS NULL THEN 1 
                       ELSE 0 
                   END) = 0 
                   
            ORDER BY datecreated DESC,id ASC) A";
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
        
    $row = $rs->fetch_assoc();
    //Retornamos el total
    $out->numofrecords = (int)$row['qty'];
    
    //Se removieron los segundos del valor que captura la fecha debído a que podía variar en casos my particulares por 1s 
    //AND t.paypending = 0
    $sql = "SELECT A.* FROM (SELECT t.id AS id, 
           t.dsc AS  dsc, 
           t.amount AS amount, 
           DATE_FORMAT(t.datecreated, '%Y-%m-%d %H:%i') AS datecreated, 
           t.amountghost AS amountghost, 
           t.txid AS txid,
           (SELECT COUNT(tx.id) 
               FROM transactions tx 
               WHERE tx.txid = t.txid) AS txcount 
           FROM transactions t 
           WHERE t.accountid = $accountid
           AND t.validthru IS NULL
           
           $time
           $filter
           
           UNION
 
            SELECT o.id AS id,
 		    CONCAT('Orden de ',(CASE WHEN o.side = 1 THEN 'Venta' ELSE 'Compra' END)) AS dsc,
            (CASE WHEN o.side = 1 THEN o.quantity ELSE o.total END) AS amount,
            DATE_FORMAT(o.date, '%Y-%m-%d %H:%i') AS datecreated,
       	    IF( 
                (SELECT SUM(ABS(transactions.amount)) 
                    FROM transactions, orders 
                    WHERE orders.id = transactions.orderid 
                    AND orders.id = o.id 
                    AND IF(o.side = 2, transactions.amount > 0, transactions.amount < 0) 
                    AND transactions.accountid = (CASE WHEN o.side = 1 THEN o.accountid ELSE o.paymentid END)) IS NOT NULL, 
                (SELECT SUM(ABS(transactions.amount))
                    FROM transactions, orders 
                    WHERE orders.id = transactions.orderid 
                    AND orders.id = o.id 
                    AND IF(o.side = 2, transactions.amount > 0, transactions.amount < 0) 
                    AND transactions.accountid = (CASE WHEN o.side = 1 THEN o.accountid ELSE o.paymentid END)), 
                0 ) AS amountghost,
            o.id AS txid,
            0 AS txcount
 	        FROM orders o WHERE o.accountid = $accountid. AND o.canceled IS NULL AND o.type != 1
    	       AND (CASE 
                       WHEN o.canceled IS NOT NULL THEN -1 
                       WHEN (SELECT SUM(ABS(transactions.amount)) 
                           FROM transactions, orders 
                           WHERE orders.id = transactions.orderid 
                           AND orders.id = o.id 
                           AND IF(o.side = 2, transactions.amount > 0, transactions.amount < 0) 
                           AND transactions.accountid = (CASE WHEN o.side = 1 THEN o.accountid ELSE o.paymentid END)) >= o.quantity 
                           AND o.canceled IS NULL THEN 1 
                       ELSE 0 
                   END) = 0 
                   
            ORDER BY datecreated DESC,id ASC) A";
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
        
    $records = [];
    while($row = $rs->fetch_assoc()){
        $record = new stdClass();
        $record->id = (int)$row["id"];
        $record->dsc = $row["dsc"];
        
        //  Obtenemos el paypending
        $sql = "SELECT paypending FROM transactions WHERE txid = ".$row["txid"];
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
        
        $paypending = $res->fetch_assoc();
        
        //Por default se marca cómo transferencia
        $record->type = 3;
        //Por default viene como aprobado
        $record->status = 1;
        //Consultamos para saber si es un retiro
        $sql = "SELECT * FROM withdrawals WHERE transactionid = ".$record->id;
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
            
        $withdrawals = $res->fetch_assoc();
        
        if($withdrawals["createddate"] != null){
            //Tipo retiro
            $record->type = 2;
            
            if($withdrawals["valid"] != null && $withdrawals["rejected"] == null){
                $record->status = 1;
            }else if($withdrawals["valid"] == null && $withdrawals["rejected"] != null){
                $record->status = -1;
            }else{
                $record->status = 0;
            }
            
            if(isCripto($out->account->currency->interface)){
                $record->txid = $withdrawals['ref'];
            }else{
                $record->txid = $withdrawals['paymentref'];
            }
            
            
        }else{
            
            //Consultamos datos del deposito
            $sql = "SELECT * FROM deposits WHERE transactionid = ".$record->id;
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));
            
            $deposit = $res->fetch_assoc();
            
            if($deposit["createddate"] != null){
                //Tipo deposito
                $record->type = 2;
                if($deposit["valid"] != null && $deposit["rejected"] == null){
                    $record->status = 1;
                }else if($deposit["valid"] == null && $deposit["rejected"] != null){
                    $record->status = -1;
                }else{
                    $record->status = 0;
                }
                
                if($out->account->currency->interface == 4){
                    $record->txid = $deposit['ref'];
                }else{
                    $record->txid = $deposit['paymentref'];
                }
            }else{
                
                if($paypending['paypending'] == 1){
                    $record->status = 0;
                } else if($row['txcount'] == 4 || $row['txcount'] == 8 || $row['txcount'] == 1){
                    
                    $record->status = 1;
                    
                }else if($row['txcount'] == 0){
                    $record->status = 0;
                } else if ($row['txcount'] == 3) {
                    // Validamos si el movimiento tiene alguna devolucion
                    $sql = "SELECT tran.dsc AS dsc ".
                    "       FROM transactions tran ".
                    "       WHERE tran.dsc LIKE '%".$row['txid']."%'".
                    "       AND '".$record->dsc."' LIKE 'Transferencia PAY%'".
                    "       AND tran.id = tran.txid ".
                    "       LIMIT 1";
                    if (!$res=$db->query($sql))
                        badEnd("500", array("msg"=>determinateDBError($db).$sql));
                    
                    $payTransfer = $res->fetch_assoc();
                    
                    if ($payTransfer['dsc'] != null) {
                        $record->status = -1;
                    } else {
                        $record->status = 1;
                    }
                }
            }
            
        }

        //Recorres el arreglo por que si es comision se valida por su tx
        foreach($records as $transaction){
            //Si se encuentra validamos que status tiene el movimiento
            if($row["txid"] == $transaction->id){
                
                $record->status = (int)$transaction->status;
                
            }
        }
        
        // SI la cantidad es 0, significa que es una orden
        if($row['txcount'] == 0){
            
            $record->amount = new stdClass();
            
            $amount = 0;
            
            // Como es una orden, obtenemos la cantidad para sacar el porcentaje completado
            $sql = "SELECT * FROM orders WHERE id = ".$row['id'];
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));
            
            $orderdata = $res->fetch_assoc();
            
            // Guardamos el total
            // Amountghost == cantidad completada
            if($orderdata['side'] == 2){
                $amount = $orderdata['price'] * ($orderdata['quantity'] - $row['amountghost']);
            }else{
                $amount = $orderdata['quantity'] - $row['amountghost'];
            }
            
            //$record->amount->testing = $orderdata;
            
            $record->amount->number = $amount;
            $record->amount->formatted = numberFormatt($record->amount->number,$currency["decimals"]);
            
        }else{
            $record->amount = new stdClass();
            $record->amount->number = $row["amount"] == 0 ? (float)number_format($row["amountghost"],$currency["decimals"],'.','') : (float)number_format($row["amount"],$currency["decimals"],'.','');
            $record->amount->formatted = numberFormatt($record->amount->number,$currency["decimals"]);
        }
        
        $record->date = new stdClass();
        $record->date->canonical = $row["datecreated"];
        $record->date->formatted = getRangeDateString($row["datecreated"])." - ".date("h:i a", strtotime($row["datecreated"]));
        
        $records [] = $record;
    }
    
    $out->records = $records;
    
    header("HTTP/1.1 200");
    echo (json_encode($out));
    die();
    
    /*  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
    */
    function determinateTime($time){
        $str = "";
        
        switch($time){
            case 0:
                $str = "";
                break;
            case 1:
                $str = " AND t.datecreated >= DATE_ADD(NOW(), INTERVAL -1 DAY)";
                break;
            case 2:
                $str = " AND t.datecreated >= DATE_ADD(NOW(), INTERVAL -7 DAY)";
                break;
            case 3:
                $str = " AND t.datecreated >= DATE_ADD(NOW(), INTERVAL -30 DAY)";
                break;
            case 4:
                $str = " AND t.datecreated >= DATE_ADD(NOW(), INTERVAL -90 DAY)";
                break;
        }
        
        return $str;
    }
    
    /*  Formateo de fecha para obtener hace cuando se hizo
    *   Recibe $timestamp como fecha de el movimiento
    *
    */
    function getRangeDateString($timestamp) {
        $current = strtotime(date("Y-m-d"));
        $date    = strtotime($timestamp);

        $datediff = $date - $current;
        $difference = floor($datediff/(60*60*24));

        switch( $difference ) {
            case 0:
                return "Hoy";
                break;
            default:
                return date("d/m/Y", $date);
        }    
    }
?>