<?php
// app/api/orders/insert
    header("Content-Type:application/json");
    include_once("../dbconn.php");
    include_once("../utils.php");
    
    // parametros obligatorios
    $parmsob = array("sessionid","pairid","qty","type","side");
    if (!parametrosValidos($_POST, $parmsob)){
        badEnd("400", array("msg"=>"Parametros obligatorios " . implode(", ", $parmsob)));
    }
    
    //Declaracion de variables
    $out = new stdClass();
    $sessionid = $_POST["sessionid"];
    $pairid = $_POST["pairid"];
    $qty = $_POST["qty"];
    $type = $_POST["type"];
    $side = $_POST["side"];
    $price = 0;
    
    //Validamos la session
    $userid = isSessionValid($db, $sessionid);
    
    //Validamos el si el usuario puede hacer operaciones
    isLevelValid($db, $userid);
    
    if($qty == 0){
        badEnd("403", array("msg"=>"La cantidad debe ser mayor a 0"));
    }
    
    // Consultamos la cantidad ingresada para formatearla y validarla
    $sql = "SELECT currencies.decimals AS decimals FROM currencies, pairs ".
    "       WHERE currencies.id = pairs.refcurrencyid ".
    "       AND pairs.id = ".$pairid;
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>$db->error));
        
    $pairdata = $rs->fetch_assoc();
    
    $refcurrencydecimals = $pairdata['decimals'];
    
    if(number_format($qty, $refcurrencydecimals, '.', '') == 0){
        badEnd("403", array("msg"=>"La cantidad debe ser mayor a 0 - ".number_format($qty, $refcurrencydecimals, '.', '')));
    }
    
    //Obtenemos los decimales para el precio
    $sql = "SELECT currencies.decimals AS decimals FROM currencies, pairs ".
    "       WHERE currencies.id = pairs.currencyid ".
    "       AND pairs.id = ".$pairid;
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>$db->error));
        
    $pairdata = $rs->fetch_assoc();
    
    $currencydecimals = $pairdata['decimals'];
    
    //SI el precio es enviado, guardamos el valor
    if($type == 2){
        if(isset($_POST["price"]) && $_POST["price"] != ""){
            if($_POST['price'] == 0){
                badEnd("403", array("msg"=>"El precio debe ser mayor a 0"));
            }
            $price = $_POST["price"];
        }else{
            badEnd("400", array("msg"=>"Si la operacion es limit el precio es requerido"));
        }
    }
    
    
    //Consultamos los datos del par
    $sql = "SELECT * FROM pairs WHERE id = ".$pairid;
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>$db->error));
        
    $pairdata = $rs->fetch_assoc();
    
    //Validamos que el usuario posea cuentas con las monedas del par
    $sql = "SELECT COUNT(accounts.id) AS qty FROM accounts ".
    "       WHERE accounts.userid = ". $userid.
    "       AND accounts.paymentmethodid = -1 ".
    "       AND (accounts.currencyid = ".$pairdata['refcurrencyid'].
    "       OR accounts.currencyid = ".$pairdata['currencyid'].")";
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>$db->error));
    
    $row = $rs->fetch_assoc();
    
    //Si la cantidad es menor a 2, retornamos error 403-Usuario no posee una cuenta
    if($row['qty'] < 2){
        badEnd('403', array('msg'=>'Usuario no posee una cuenta para realizar la operación'));
    }
    
    validateBalance($userid, $db, $side, $pairdata, $qty, $price);
    
    //Obtenemos los datos de la cuenta del usuario para account
    $sql = "SELECT * FROM accounts".
    "       WHERE userid = ".$userid.
    "       AND accounts.paymentmethodid = -1".
    "       AND accounts.currencyid = ".$pairdata["refcurrencyid"];
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>$db->error));
        
    $accountdata = $rs->fetch_assoc();
    
    //Obtenemos los datos de la cuenta del usuario para paymentaccount
    $sql = "SELECT * FROM accounts".
    "       WHERE userid = ".$userid.
    "       AND accounts.paymentmethodid = -1".
    "       AND accounts.currencyid = ".$pairdata["currencyid"];
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>$db->error));
        
    $paymentaccountdata = $rs->fetch_assoc();
    
    //SI la operacion es del tipo market, consultamos la mejor oferta dependiendo de si es compra o venta
    if($side == 1 && $type == 1){
        
        //Consultamos que exista una venta
        $sql = "SELECT COUNT(o.id) as qty FROM orders o 
               WHERE o.side = 2 AND o.pairid = ".$pairid."
               AND (SELECT SUM(ord.quantity - (
                       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 
                   ))) FROM orders ord 
                   WHERE (CASE 
                           WHEN ord.canceled IS NOT NULL THEN -1 
                           WHEN (SELECT SUM(ABS(transactions.amount)) 
                               FROM transactions, orders 
                               WHERE orders.id = transactions.orderid 
                               AND orders.id = ord.id 
                               AND IF(ord.side = 2, transactions.amount > 0, transactions.amount < 0) 
                               AND transactions.accountid = (CASE WHEN ord.side = 1 THEN ord.accountid ELSE ord.paymentid END)) >= ord.quantity 
                               AND ord.canceled IS NULL THEN 1 
                           ELSE 0 
                           END) = 0 
                   AND ord.pairid = ".$pairid."
                   AND ord.side = 2 ) >= ".$qty."
               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";
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>$db->error));
            
        $row = $rs->fetch_assoc();
        
        if($row['qty'] == 0){
            $sql = "SELECT (SELECT SUM(ord.quantity - (
                       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 
                   ))) FROM orders ord 
                   WHERE (CASE 
                           WHEN ord.canceled IS NOT NULL THEN -1 
                           WHEN (SELECT SUM(ABS(transactions.amount)) 
                               FROM transactions, orders 
                               WHERE orders.id = transactions.orderid 
                               AND orders.id = ord.id 
                               AND IF(ord.side = 2, transactions.amount > 0, transactions.amount < 0) 
                               AND transactions.accountid = (CASE WHEN ord.side = 1 THEN ord.accountid ELSE ord.paymentid END)) >= ord.quantity 
                               AND ord.canceled IS NULL THEN 1 
                           ELSE 0 
                           END) = 0 
                   AND ord.pairid = ".$pairid."
                  AND ord.side = 2 ) as qty FROM orders o 
               WHERE o.side = 2 AND o.pairid = ".$pairid."
              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";
                if (!$rs=$db->query($sql))
                    badEnd("500", array("msg"=>$db->error));
                
                $row = $rs->fetch_assoc();
                
                if($row['qty'] == 0){
                    badEnd('406', array('msg'=>'No hay liquidez, espere o coloque una orden tipo límite'));
                }else{
                    badEnd('402',array('msg'=>'Liquidez insuficiente','avaliable'=>(float)$row['qty']));
                }
        }
        
        //Consultamos el mejor precio de venta
        $sql = "SELECT o.id, o.price FROM orders o ".
        "       WHERE o.side = 2 AND o.pairid = ".$pairid.
        "       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 o.price DESC";
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>$db->error));
            
        $row = $rs->fetch_assoc();
        
        $price = $row["price"];
        
        validateBalance($userid, $db, $side, $pairdata, $qty, $price);
    }else if($side == 2 && $type == 1){
        // Consultamos que exista una venta
        $sql = "SELECT COUNT(o.id) as qty FROM orders o 
               WHERE o.side = 1 AND o.pairid = ".$pairid."
               AND (SELECT SUM(ord.quantity - (
                       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 
                   ))) FROM orders ord 
                   WHERE (CASE 
                           WHEN ord.canceled IS NOT NULL THEN -1 
                           WHEN (SELECT SUM(ABS(transactions.amount)) 
                               FROM transactions, orders 
                               WHERE orders.id = transactions.orderid 
                               AND orders.id = ord.id 
                               AND IF(ord.side = 2, transactions.amount > 0, transactions.amount < 0) 
                               AND transactions.accountid = (CASE WHEN ord.side = 1 THEN ord.accountid ELSE ord.paymentid END)) >= ord.quantity 
                               AND ord.canceled IS NULL THEN 1 
                           ELSE 0 
                           END) = 0 
                   AND ord.pairid = ".$pairid."
                   AND ord.side = 1 ) >= ".$qty."
               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";
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>$db->error));
            
        $row = $rs->fetch_assoc();
        
        if($row['qty'] == 0){
            $sql = "SELECT (SELECT SUM(ord.quantity - (
                       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 
                   ))) FROM orders ord 
                   WHERE (CASE 
                           WHEN ord.canceled IS NOT NULL THEN -1 
                           WHEN (SELECT SUM(ABS(transactions.amount)) 
                               FROM transactions, orders 
                               WHERE orders.id = transactions.orderid 
                               AND orders.id = ord.id 
                               AND IF(ord.side = 2, transactions.amount > 0, transactions.amount < 0) 
                               AND transactions.accountid = (CASE WHEN ord.side = 1 THEN ord.accountid ELSE ord.paymentid END)) >= ord.quantity 
                               AND ord.canceled IS NULL THEN 1 
                           ELSE 0 
                           END) = 0 
                   AND ord.pairid = ".$pairid."
                  AND ord.side = 1 ) as qty FROM orders o 
               WHERE o.side = 1 AND o.pairid = ".$pairid."
              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";
                if (!$rs=$db->query($sql))
                    badEnd("500", array("msg"=>$db->error));
                
                $row = $rs->fetch_assoc();
                
                if($row['qty'] == 0){
                    badEnd('406', array('msg'=>'No hay liquidez, espere o coloque una orden tipo límite'));
                }else{
                    badEnd('402',array('msg'=>'Liquidez insuficiente','avaliable'=>(float)$row['qty']));
                }
        }
        
        //Consultamos el mejor precio de compra
        $sql = "SELECT o.id, o.price FROM orders o".
        "       WHERE o.side = 1 AND o.pairid = ".$pairid.
        "       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 o.price ASC";
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>$db->error));
            
        $row = $rs->fetch_assoc();
        
        $price = $row["price"];
        validateBalance($userid, $db, $side, $pairdata, $qty, $price);
    }
    
    
    //SI la operacion es del tipo limit, verificamos el precio para cambiarlo o dejarlo
    if($side == 1 && $type == 2){
        //Consultamos el mejor precio de venta
        $sql = "SELECT o.id, o.price FROM orders o ".
        "       WHERE o.side = 2 AND o.pairid = ".$pairid.
        "       AND o.price >= ".$price.
        "       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 o.price DESC".
        "       LIMIT 1";
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>$db->error));
            
        $row = $rs->fetch_assoc();
        
        if($price >= $row['price'] && $row['price'] != null){
            
            //Consultamos que exista una venta
            $sql = "SELECT COUNT(o.id) AS qty FROM orders o ".
            "       WHERE o.side = 2 AND o.pairid = ".$pairid.
            "       AND (SELECT SUM(ord.quantity) FROM orders ord ".
            "           WHERE (CASE ".
            "                   WHEN ord.canceled IS NOT NULL THEN -1 ".
            "                   WHEN (SELECT SUM(ABS(transactions.amount)) ".
            "                       FROM transactions, orders ".
            "                       WHERE orders.id = transactions.orderid ".
            "                       AND orders.id = ord.id ".
            "                       AND IF(ord.side = 2, transactions.amount > 0, transactions.amount < 0) ".
            "                       AND transactions.accountid = (CASE WHEN ord.side = 1 THEN ord.accountid ELSE ord.paymentid END)) >= ord.quantity ".
            "                       AND ord.canceled IS NULL THEN 1 ".
            "                   ELSE 0 ".
            "                   END) = 0 ".
            "           AND ord.pairid = ".$pairid.
            "           AND ord.side = 2 ) >= ".$qty.
            "       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 ";
            if (!$rs=$db->query($sql))
                badEnd("500", array("msg"=>$db->error));
                
            $row = $rs->fetch_assoc();
            
            if($row['qty'] == 0){
                $sql = "SELECT (SELECT SUM(ord.quantity - (
                       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 
                   ))) FROM orders ord 
                   WHERE (CASE 
                           WHEN ord.canceled IS NOT NULL THEN -1 
                           WHEN (SELECT SUM(ABS(transactions.amount)) 
                               FROM transactions, orders 
                               WHERE orders.id = transactions.orderid 
                               AND orders.id = ord.id 
                               AND IF(ord.side = 2, transactions.amount > 0, transactions.amount < 0) 
                               AND transactions.accountid = (CASE WHEN ord.side = 1 THEN ord.accountid ELSE ord.paymentid END)) >= ord.quantity 
                               AND ord.canceled IS NULL THEN 1 
                           ELSE 0 
                           END) = 0 
                   AND ord.pairid = ".$pairid."
                  AND ord.side = 2 ) as qty FROM orders o 
               WHERE o.side = 2 AND o.pairid = ".$pairid."
              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";
                if (!$rs=$db->query($sql))
                    badEnd("500", array("msg"=>$db->error));
                
                $row = $rs->fetch_assoc();
                
                if($row['qty'] == 0){
                    badEnd('406', array('msg'=>'No hay liquidez, espere o coloque una orden tipo límite'));
                }else{
                    badEnd('402',array('msg'=>'Liquidez insuficiente','avaliable'=>(float)$row['qty']));
                }
            }
            
            //Consultamos el mejor precio de venta
            $sql = "SELECT o.id, o.price FROM orders o ".
            "       WHERE o.side = 2 AND o.pairid = ".$pairid.
            "       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 o.price DESC";
            if (!$rs=$db->query($sql))
                badEnd("500", array("msg"=>$db->error));
                
            $row = $rs->fetch_assoc();
            
            $price = $row["price"];
            $type = 1;
            validateBalance($userid, $db, $side, $pairdata, $qty, $price);
        }
    }else if($side == 2 && $type == 2){
        //Consultamos el mejor precio de compra
        $sql = "SELECT o.id, o.price FROM orders o ".
        "       WHERE o.side = 1 AND o.pairid = ".$pairid.
        "       AND o.price <= ".$price.
        "       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 o.price ASC".
        "       LIMIT 1";
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>$db->error));
            
        $row = $rs->fetch_assoc();
        
        if($price <= $row['price'] && $row['price'] != null){
             // Consultamos que exista una venta
            $sql = "SELECT  COUNT(o.id) AS qty FROM orders o".
            "       WHERE o.side = 1 AND o.pairid = ".$pairid.
            "       AND (SELECT SUM(ord.quantity) FROM orders ord ".
            "           WHERE (CASE ".
            "                   WHEN ord.canceled IS NOT NULL THEN -1 ".
            "                   WHEN (SELECT SUM(ABS(transactions.amount)) ".
            "                       FROM transactions, orders ".
            "                       WHERE orders.id = transactions.orderid ".
            "                       AND orders.id = ord.id ".
            "                       AND IF(ord.side = 2, transactions.amount > 0, transactions.amount < 0) ".
            "                       AND transactions.accountid = (CASE WHEN ord.side = 1 THEN ord.accountid ELSE ord.paymentid END)) >= ord.quantity ".
            "                       AND ord.canceled IS NULL THEN 1 ".
            "                   ELSE 0 ".
            "                   END) = 0 ".
            "           AND ord.pairid = ".$pairid.
            "           AND ord.side = 1 ) >= ".$qty.
            "       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 ";
            if (!$rs=$db->query($sql))
                badEnd("500", array("msg"=>$db->error));
            
            $row = $rs->fetch_assoc();
        
            if($row['qty'] == 0){
                $sql = "SELECT (SELECT SUM(ord.quantity - (
                       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 
                   ))) FROM orders ord 
                   WHERE (CASE 
                           WHEN ord.canceled IS NOT NULL THEN -1 
                           WHEN (SELECT SUM(ABS(transactions.amount)) 
                               FROM transactions, orders 
                               WHERE orders.id = transactions.orderid 
                               AND orders.id = ord.id 
                               AND IF(ord.side = 2, transactions.amount > 0, transactions.amount < 0) 
                               AND transactions.accountid = (CASE WHEN ord.side = 1 THEN ord.accountid ELSE ord.paymentid END)) >= ord.quantity 
                               AND ord.canceled IS NULL THEN 1 
                           ELSE 0 
                           END) = 0 
                   AND ord.pairid = ".$pairid."
                  AND ord.side = 1 ) as qty FROM orders o 
               WHERE o.side = 1 AND o.pairid = ".$pairid."
              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";
                if (!$rs=$db->query($sql))
                    badEnd("500", array("msg"=>$db->error));
                
                $row = $rs->fetch_assoc();
                
                if($row['qty'] == 0){
                    badEnd('406', array('msg'=>'No hay liquidez, espere o coloque una orden tipo límite'));
                }else{
                    badEnd('402',array('msg'=>'Liquidez insuficiente','avaliable'=>(float)$row['qty']));
                }
            }
        
            //Consultamos el mejor precio de compra
            $sql = "SELECT o.id, o.price FROM orders o".
            "       WHERE o.side = 1 AND o.pairid = ".$pairid.
            "       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 o.price ASC";
            if (!$rs=$db->query($sql))
                badEnd("500", array("msg"=>$db->error));
            
            $row = $rs->fetch_assoc();
        
            $price = $row["price"];
            $type = 1;
            validateBalance($userid, $db, $side, $pairdata, $qty, $price);
        }
    }
    
    //Insertamos la orden
    $db->autocommit(FALSE);
    
    $sql = "INSERT INTO orders ".
    "       ( pairid, ".
    "       quantity, side, ".
    "       type, price, ".
    "       total, ".
    "       date, accountid, ".
    "       paymentid, canceled )".
    "       VALUES (".
    "       ".$pairid.", ".
    "       ".number_format($qty, $refcurrencydecimals, '.', '').", ".$side.", ".
    "       ".$type.", ".number_format($price, $currencydecimals, '.', '').", ".
    "       ".number_format($qty * $price, $currencydecimals, '.', '').", ".
    "       NOW(), ".($side == 1 ? $accountdata['id'] : $paymentaccountdata['id']).", ".
    "       ".($side == 2 ? $accountdata['id'] : $paymentaccountdata['id']).", NULL".
    "       )";
    if (!$db->query($sql)){
        $error = $db->error;
        $db->rollback();
        $db->close();
        badEnd("500", array("msg"=>$error));
    }
    
    //Guardamos el id de la transaccion de deposito
    $out->id = (int)$db->insert_id;
    
    //auditoria
    setAudit($db, "ORDENES", $sessionid, "APP", "Se creó la orden (".$out->id.")");
    
    $db->commit();
    
    //Si la orden es de tipo market tenemos que calcular el precio referencial
    if($type == 1){
        $db->autocommit(TRUE);
        
        $sql = "SELECT o.id AS orderprinid, o.side AS orderprinside, ".
    "       o.type AS orderprintype, o.quantity AS orderprinqty, ".
    "       o.price AS orderprinprice, o.total AS orderprintotal, ".
    "       o.accountid AS orderprinaccount, o.paymentid AS orderprinpayment, ".
    "       o.pairid AS orderpair, ".
    "       pairs.refcurrencyid AS refcurrencyid, pairs.currencyid AS currencyid, ".
    "       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 totallyprinfilled, ".
    "       users.id AS usridprin, users.level AS userlevelprin,  users.type AS usrtypeprin, ".
    "       ord.id AS ordersecid, ord.price AS ordersecprice, ord.quantity AS ordersecqty, ord.type AS ordersectype, ".
    "       ord.side AS ordersecside, ord.accountid AS ordersecaccount, ord.paymentid AS ordersecpayment, ".
    "       ord.total AS ordersectotal, ".
    "       IF( ".
    "          (SELECT SUM(ABS(transactions.amount)) ".
    "               FROM transactions, orders ".
    "               WHERE orders.id = transactions.orderid ".
    "               AND orders.id = ord.id ".
    "               AND IF(ord.side = 2, transactions.amount > 0, transactions.amount < 0) ".
    "               AND transactions.accountid = (CASE WHEN ord.side = 1 THEN ord.accountid ELSE ord.paymentid END)) IS NOT NULL, ".
    "           (SELECT SUM(ABS(transactions.amount)) ".
    "               FROM transactions, orders ".
    "               WHERE orders.id = transactions.orderid ".
    "               AND orders.id = ord.id ".
    "               AND IF(ord.side = 2, transactions.amount > 0, transactions.amount < 0) ".
    "               AND transactions.accountid = (CASE WHEN ord.side = 1 THEN ord.accountid ELSE ord.paymentid END)), ".    
    "           0 ) AS totallysecfilled ".
    "       FROM users, accounts, pairs,orders o ".
    "           INNER JOIN orders ord ON ord.type = 1 AND o.side != ord.side AND o.pairid = ord.pairid ".
    "           AND (CASE ".
    "               WHEN ord.canceled IS NOT NULL THEN -1 ".
    "               WHEN (SELECT SUM(ABS(transactions.amount)) ".
    "                   FROM transactions, orders ".
    "                   WHERE orders.id = transactions.orderid ".
    "                   AND orders.id = ord.id ".
    "                   AND IF(ord.side = 2, transactions.amount > 0, transactions.amount < 0) ".
    "                   AND transactions.accountid = (CASE WHEN ord.side = 1 THEN ord.accountid ELSE ord.paymentid END)) >= ord.quantity ".
    "                   AND ord.canceled IS NULL THEN 1 ".
    "               ELSE 0 ".
    "           END) = 0 ".
    "       WHERE o.accountid = accounts.id ".
    "       AND accounts.userid = users.id ".
    "       AND pairs.id = o.pairid ".
    "       AND o.type = 2 ".
    "       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 ordersecid ASC, (CASE WHEN ord.side = 1 THEN orderprinprice END) DESC,".
    "      (CASE WHEN ord.side = 2 THEN orderprinprice END) ASC";
        if (!$rs=$db->query($sql))
                badEnd("500", array("msg"=>$db->error));
        
        $orders = array();
        $currentOrder = [];
        while($row = $rs->fetch_assoc()){
            $records [] = $row;
            $qtytmp = 0;
            
            //Decimales de moneda base
            $sql = "SELECT decimals FROM currencies WHERE id = ".$row['currencyid'];
            if (!$res=$db->query($sql)){
                badEnd("500", array("msg"=>$db->error.$sql));
            }
            
            $currencydecimals = $res->fetch_assoc();
            
            //Decimales moneda de ref
            $sql = "SELECT decimals FROM currencies WHERE id = ".$row['refcurrencyid'];
            if (!$res=$db->query($sql)){
                badEnd("500", array("msg"=>$db->error.$sql));
            }
            
            $currencyrefdecimals = $res->fetch_assoc();
            
            //SI la orden PRIN no esta ingresada, la ingresamos
            if(!array_key_exists($row['orderprinid'], $orders)){
                $orders[$row['orderprinid']] = new stdClass();
                $orders[$row['orderprinid']]->id = (int)$row['orderprinid'];
                $prinfilled = ($row['totallyprinfilled']*100)/$row['orderprinqty'];
                
                $orders[$row['orderprinid']]->avaliable = number_format((($row['orderprinqty'] * (100 - $prinfilled))/100), $currencyrefdecimals['decimals'], '.', '');
                
                $orders[$row['orderprinid']]->price = number_format($row['orderprinprice'], $currencydecimals['decimals'], '.', '');
            }
            
            //SI la orden SEC no esta ingresada, la ingresamos
            if(!array_key_exists($row['ordersecid'], $orders)){
                $orders[$row['ordersecid']] = new stdClass();
                $orders[$row['ordersecid']]->id = (int)$row['ordersecid'];
                $secfilled = ($row['totallysecfilled']*100)/$row['ordersecqty'];
                
                $orders[$row['ordersecid']]->avaliable = number_format((($row['ordersecqty'] * (100 - $secfilled))/100), $currencyrefdecimals['decimals'], '.', '');
                
                $orders[$row['ordersecid']]->price = number_format($row['ordersecprice'], $currencydecimals['decimals'], '.', '');
            }
            
            //En este punto ya la orden esta ingresada, hacemos el intercambio (SIMULACION)
            
            //Validamos que las ordenes no se hayan completado
            
            if($orders[$row['ordersecid']]->avaliable <= 0 || $orders[$row['orderprinid']]->avaliable <= 0){
                continue;
            }
            
            //Con eso validamos que una sea completada en su totalidad y la otra de manera parcial
            if($orders[$row['orderprinid']]->avaliable < $orders[$row['ordersecid']]->avaliable){
                $qtytmp = $orders[$row['orderprinid']]->avaliable;
            }elseif($orders[$row['orderprinid']]->avaliable > $orders[$row['ordersecid']]->avaliable){
                $qtytmp = $orders[$row['ordersecid']]->avaliable;
            }else{
                $qtytmp = $orders[$row['orderprinid']]->avaliable;
            }
            
            //Si coincide hacemos la simulacion y la guardamos
            if($row['ordersecid'] == $out->id){
                $exchange = new stdClass();
                $exchange->quantity = $qtytmp;
                $exchange->price = $orders[$row['orderprinid']]->price;
                $exchange->decimals = $currencydecimals['decimals'];
                
                $currentOrder [] = $exchange;
                
                //resta de cantidad disponible de la orden market
                if($orders[$row['ordersecid']]->avaliable < $qtytmp){
                    $orders[$row['ordersecid']]->avaliable = number_format($qtytmp - $orders[$row['ordersecid']]->avaliable , $currencyrefdecimals['decimals'], '.', '');
                }else{
                    $orders[$row['ordersecid']]->avaliable = number_format($orders[$row['ordersecid']]->avaliable - $qtytmp, $currencyrefdecimals['decimals'], '.', '');
                }
                
                //Resta de la cantidad disponible en la orden limit
                if($orders[$row['orderprinid']]->avaliable < $qtytmp){
                    $orders[$row['orderprinid']]->avaliable = number_format($qtytmp - $orders[$row['orderprinid']]->avaliable , $currencyrefdecimals['decimals'], '.', '');
                }else{
                    $orders[$row['orderprinid']]->avaliable = number_format($orders[$row['orderprinid']]->avaliable - $qtytmp , $currencyrefdecimals['decimals'], '.', '');
                }
                
                //Luego de la simulacion, verificamos si la orden es 0 para romper el ciclo
                if($orders[$row['ordersecid']]->avaliable <= 0){
                    break;
                }
            }else{
                
                //resta de cantidad disponible de la orden market
                if($orders[$row['ordersecid']]->avaliable < $qtytmp){
                    $orders[$row['ordersecid']]->avaliable = number_format($qtytmp - $orders[$row['ordersecid']]->avaliable, $currencyrefdecimals['decimals'], '.', '');
                }else{
                    $orders[$row['ordersecid']]->avaliable = number_format($orders[$row['ordersecid']]->avaliable - $qtytmp, $currencyrefdecimals['decimals'], '.', '');
                }
                
                //Resta de la cantidad disponible en la orden limit
                if($orders[$row['orderprinid']]->avaliable < $qtytmp){
                    $orders[$row['orderprinid']]->avaliable = number_format($qtytmp - $orders[$row['orderprinid']]->avaliable, $currencyrefdecimals['decimals'], '.', '');
                }else{
                    $orders[$row['orderprinid']]->avaliable = number_format($orders[$row['orderprinid']]->avaliable - $qtytmp, $currencyrefdecimals['decimals'], '.', '');
                }
            }
        }
        
        $out->testing2 = $currentOrder;
        
        $amountprices = 0;
        //Luego de que el ciclo se cumpla seguimos a mostrar el precio final
        foreach($currentOrder as $exchange){
            $pctparcial = 0;
            $pctparcial = ($exchange->quantity * 100) / $qty;
            
            $promprice = 0;
            $promprice = number_format((($pctparcial * $exchange->price) / 100), $exchange->decimals, '.', '');
                    
            //Sumamos los precios
            $amountprices += $promprice;
        }
        
        $out->finalprice = (float)$amountprices;
    }
    
    
    $db->close();
    
    header("HTTP/1.1 200");
    echo (json_encode($out));
    die();
    
    function validateBalance($userid, $db, $side, $pairdata, $qty, $price){
        //Validar cantidad o monto dependiendo del side
        if($side == 1){
            //Al ser una venta, validamos que el usuario posea la cantidad indicada de la moneda ref del par
            //Consultamos el balance total del usuario actual
            $sql = "SELECT SUM(transactions.amount) as qty ".
            "       FROM accounts, transactions".
	        "       WHERE accounts.id = transactions.accountid AND accounts.userid = ".$userid.
	        "       AND accounts.paymentmethodid = -1".
	        "       AND accounts.currencyid = ".$pairdata["refcurrencyid"];
	        if (!$rs=$db->query($sql))
                badEnd("500", array("msg"=>$db->error));
        
            $row = $rs->fetch_assoc();
        
            //Consultamos el balance por sus ordenes
            $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, accounts ".
            "           WHERE accounts.id = orders.accountid ".
	        "           AND orders.canceled IS NULL ".
	        "           AND accounts.currencyid = ".$pairdata["refcurrencyid"].
	        "           AND accounts.userid = ".$userid.
	        "           AND accounts.paymentmethodid = -1 ".
            "       UNION ALL ".
            "           SELECT SUM(transactions.amount) AS amount ".
            "           FROM transactions, orders, accounts ".
            "           WHERE orders.id = transactions.orderid ".
            "           AND accounts.id = transactions.accountid ".
	        "           AND orders.canceled IS NULL ".
	        "           AND accounts.currencyid = ".$pairdata["refcurrencyid"].
	        "           AND accounts.userid = ".$userid.
	        "           AND accounts.paymentmethodid = -1".
	        "           AND transactions.amount < 0".
            "       ) amount";
	        if (!$rs=$db->query($sql))
                badEnd("500", array("msg"=>$db->error));
        
            $roworders = $rs->fetch_assoc();
    
            //Si el saldo es menor a cantidad*precio retornamos error 403-saldo insuficiente
            if(($row["qty"] - $roworders["qty"]) < $qty){
                badEnd('403', array('msg'=>'Cantidad insuficiente'));
            }
        }else if($side == 2){
            //Al ser una compra, validamos que el usuario posea la cantidad*precio de la moneda base
            //Consultamos el balance total del usuario actual
            $sql = "SELECT SUM(transactions.amount) as qty ".
            "       FROM accounts, transactions".
	        "       WHERE accounts.id = transactions.accountid AND accounts.userid = ".$userid.
	        "       AND accounts.paymentmethodid = -1".
	        "       AND accounts.currencyid = ".$pairdata["currencyid"];
	        if (!$rs=$db->query($sql))
                badEnd("500", array("msg"=>$db->error));
        
            $row = $rs->fetch_assoc();
        
            //Consultamos el balance por sus ordenes
            $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, accounts ".
            "           WHERE accounts.id = orders.accountid ".
	        "           AND orders.canceled IS NULL ".
	        "           AND accounts.currencyid = ".$pairdata["currencyid"].
	        "           AND accounts.userid = ".$userid.
	        "           AND accounts.paymentmethodid = -1 ".
            "       UNION ALL ".
            "           SELECT SUM(transactions.amount) AS amount ".
            "           FROM transactions, orders, accounts ".
            "           WHERE orders.id = transactions.orderid ".
            "           AND accounts.id = transactions.accountid ".
	        "           AND orders.canceled IS NULL ".
	        "           AND accounts.currencyid = ".$pairdata["currencyid"].
	        "           AND accounts.userid = ".$userid.
	        "           AND accounts.paymentmethodid = -1".
	        "           AND transactions.amount < 0".
            "       ) amount";
	        if (!$rs=$db->query($sql))
                badEnd("500", array("msg"=>$db->error));
        
            $roworders = $rs->fetch_assoc();
    
            //Si el saldo es menor a cantidad*precio retornamos error 403-saldo insuficiente
            if(($row["qty"] - $roworders["qty"]) < ($price * $qty)){
                badEnd('403', array('msg'=>'Saldo insuficiente'));
            }
        
        }else{
            badEnd('500',array('msg'=>'El parametro side debe ser 1(venta) o 2(compra)'));
        }
    }
?>