<?php
// app/api/orders/insert
    header("Content-Type:application/json");
    include_once("../../../settings/dbconn.php");
    include_once("../../../settings/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);
    
    //Obtenemos el lenguaje del usuario
    $lang = updateLang($db,$sessionid);
    
    if($qty == 0){
        badEnd("403", array("msg"=>determinateMsgFail(array(
            "esp"=>"La cantidad debe ser mayor a 0",
            "eng"=>"The amount must be greater than 0"), $lang, "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"=>determinateDBError($db)));
        
    $pairdata = $rs->fetch_assoc();
    
    $refcurrencydecimals = $pairdata['decimals'];
    
    $qty = number_format($qty, $refcurrencydecimals, '.', '');
    
    if(number_format($qty, $refcurrencydecimals, '.', '') == 0){
        badEnd("403", array("msg"=>determinateMsgFail(array(
            "esp"=>"La cantidad debe ser mayor a 0 - ".number_format($qty, $refcurrencydecimals, '.', ''),
            "eng"=>"The amount must be greater than 0 - ".number_format($qty, $refcurrencydecimals, '.', '')), $lang, "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"=>determinateDBError($db)));
        
    $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"=>determinateMsgFail(array(
            "esp"=>"El precio debe ser mayor a 0",
            "eng"=>"The price must be greater than 0"), $lang, "El precio debe ser mayor a 0")));
            }
            $price = $_POST["price"];
        }else{
            badEnd("400", array("msg"=>determinateMsgFail(array(
            "esp"=>"Si la operacion es limit el precio es requerido",
            "eng"=>"If the operation is limit the price is required"), $lang, "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"=>determinateDBError($db)));
        
    $pairdata = $rs->fetch_assoc();
    
    // Consultamos los datos de el usuario
    $sql = "SELECT * FROM users WHERE id = ".$userid;
    if (!$res=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
    
    $userdata = $res->fetch_assoc();
    
    // Validamos el monto minimo
    $sql = "SELECT minexchange ".
    "       FROM cmspreffees ".
    "       WHERE currencyid = ".$pairdata['refcurrencyid'].
    "       AND usertype = ".$userdata["type"].
    "       AND levelid = ".$userdata["level"].
    "       LIMIT 1";
    if (!$res=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
    
    $min = $res->fetch_assoc();
    
    if(validateMin($pairdata['refcurrencyid'], $userdata["type"], 4, $db, number_format($qty, $refcurrencydecimals, '.', ''))){
        badEnd("403", array("msg"=>determinateMsgFail(array(
            "esp"=>"Monto mínimo autorizado: ".number_format($qty, $refcurrencydecimals, '.', '')." A ".numberFormatt($min['minexchange'], $refcurrencydecimals),
            "eng"=>"Minimum authorized amount: A ".numberFormatt($min['minexchange'], $refcurrencydecimals)), $lang, "Monto mínimo autorizado: ".numberFormatt($min['minexchange'], $refcurrencydecimals))));
    }
    
    //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.entityusrid = -1 ".
    "       AND (accounts.currencyid = ".$pairdata['refcurrencyid'].
    "       OR accounts.currencyid = ".$pairdata['currencyid'].")";
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
    
    $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('a'=>$sql,'msg'=>determinateMsgFail(array(
            "esp"=>'Usuario no posee una cuenta para realizar la operación',
            "eng"=>"User does not have an account to perform the operation"), $lang, '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.entityusrid = -1 ".
    "       AND accounts.currencyid = ".$pairdata["refcurrencyid"];
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
        
    $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.entityusrid = -1 ".
    "       AND accounts.currencyid = ".$pairdata["currencyid"];
    if (!$rs=$db->query($sql))
        badEnd("500", array("msg"=>determinateDBError($db)));
        
    $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"=>determinateDBError($db)));
            
        $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"=>determinateDBError($db)));
                
                $row = $rs->fetch_assoc();
                
                if($row['qty'] == 0){
                    badEnd('406', array('msg'=>determinateMsgFail(array(
                        "esp"=>'No hay liquidez, espere o coloque una orden tipo límite',
                        "eng"=>"No liquidity, wait or place a limit order"), $lang, 'No hay liquidez, espere o coloque una orden tipo límite')));
                }else{
                    badEnd('402',array('msg'=>determinateMsgFail(array(
                        "esp"=>'Liquidez insuficiente',
                        "eng"=>"Insufficient liquidity"), $lang, '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"=>determinateDBError($db)));
            
        $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"=>determinateDBError($db)));
            
        $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"=>determinateDBError($db)));
                
                $row = $rs->fetch_assoc();
                
                if($row['qty'] == 0){
                    badEnd('406', array('msg'=>determinateMsgFail(array(
                        "esp"=>'No hay liquidez, espere o coloque una orden tipo límite',
                        "eng"=>"No liquidity, wait or place a limit order"), $lang, 'No hay liquidez, espere o coloque una orden tipo límite')));
                }else{
                    badEnd('402',array('msg'=>determinateMsgFail(array(
                        "esp"=>'Liquidez insuficiente',
                        "eng"=>"Insufficient liquidity"), $lang, '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"=>determinateDBError($db)));
            
        $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"=>determinateDBError($db)));
            
        $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"=>determinateDBError($db)));
                
            $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"=>determinateDBError($db)));
                
                $row = $rs->fetch_assoc();
                
                if($row['qty'] == 0){
                    badEnd('406', array('msg'=>determinateMsgFail(array(
                        "esp"=>'No hay liquidez, espere o coloque una orden tipo límite',
                        "eng"=>"No liquidity, wait or place a limit order"), $lang, 'No hay liquidez, espere o coloque una orden tipo límite')));
                }else{
                    badEnd('402',array('msg'=>determinateMsgFail(array(
                        "esp"=>'Liquidez insuficiente',
                        "eng"=>"Insufficient liquidity"), $lang, '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"=>determinateDBError($db)));
                
            $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"=>determinateDBError($db)));
            
        $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"=>determinateDBError($db)));
            
            $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"=>determinateDBError($db)));
                
                $row = $rs->fetch_assoc();
                
                if($row['qty'] == 0){
                    badEnd('406', array('msg'=>determinateMsgFail(array(
                        "esp"=>'No hay liquidez, espere o coloque una orden tipo límite',
                        "eng"=>"No liquidity, wait or place a limit order"), $lang, 'No hay liquidez, espere o coloque una orden tipo límite')));
                }else{
                    badEnd('402',array('msg'=>determinateMsgFail(array(
                        "esp"=>'Liquidez insuficiente',
                        "eng"=>"Insufficient liquidity"), $lang, '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"=>determinateDBError($db)));
            
            $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 = determinateDBError($db);
        $db->rollback();
        $db->close();
        badEnd("500", array("msg"=>$error));
    }
    
    //Guardamos el id de la transaccion de deposito
    $out->id = (int)$db->insert_id;
    
    //Si la orden es de tipo market tenemos que calcular el precio referencial
    if($type == 1){
        
        $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 accounts.entityusrid = -1 ".
        "       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)){
            $error = determinateDBError($db);
            $db->rollback();
            $db->close();
            badEnd("500", array("msg"=>$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)){
            $error = determinateDBError($db);
            $db->rollback();
            $db->close();
            badEnd("500", array("msg"=>$error));
        }
            
            $currencydecimals = $res->fetch_assoc();
            
            //Decimales moneda de ref
            $sql = "SELECT decimals FROM currencies WHERE id = ".$row['refcurrencyid'];
            if (!$res=$db->query($sql)){
            $error = determinateDBError($db);
            $db->rollback();
            $db->close();
            badEnd("500", array("msg"=>$error));
        }
            
            $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;
        
        validateBalance2($userid, $db, $side, $pairdata, $qty, $out->finalprice);
    }
    
    //auditoria
    setAudit($db, "ORDENES", $sessionid, "APP", "Se creó la orden (".$out->id.")");
    
    $db->commit();
    $db->close();
    
    header("HTTP/1.1 200");
    echo (json_encode($out));
    die();
    
    function validateBalance($userid, $db, $side, $pairdata, $qty, $price){
        // Consultamos el lang del usuario
        $sql = "SELECT lang FROM users WHERE id - ".$userid;
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
        
        $row = $rs->fetch_assoc();
        $lang = $row['lang'];
        
        //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
            //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, accounts ".
            "           WHERE accounts.currencyid = ".$pairdata["refcurrencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND orders.accountid = accounts.id".
        	"           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, accounts ".
            "           WHERE accounts.currencyid = ".$pairdata["refcurrencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND orders.accountid = accounts.id".
        	"           AND orders.canceled IS NULL ".
        	"           AND orders.type != 1), 0)".
            "       + IF(( ".
            "           SELECT SUM(transactions.amount) AS amount ".
            "           FROM transactions, orders, accounts ".
            "           WHERE accounts.currencyid = ".$pairdata["refcurrencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND orders.id = transactions.orderid ".
            "           AND transactions.accountid = accounts.id".
            "           AND orders.accountid = accounts.id".
        	"           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, accounts ".
            "           WHERE accounts.currencyid = ".$pairdata["refcurrencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND orders.id = transactions.orderid ".
            "           AND transactions.accountid = accounts.id".
            "           AND orders.accountid = accounts.id".
        	"           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, accounts ".
        	"           WHERE accounts.currencyid = ".$pairdata["refcurrencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND transactions.paypending = 1".
        	"           AND transactions.accountid = accounts.id".
        	"           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, accounts ".
        	"           WHERE accounts.currencyid = ".$pairdata["refcurrencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND transactions.paypending = 1".
        	"           AND transactions.accountid = accounts.id".
        	"           AND NOT EXISTS(SELECT withdrawals.transactionid FROM withdrawals WHERE withdrawals.transactionid = transactions.id)), 0)".
        	"      ) as qty FROM transactions, accounts ".
        	"           WHERE transactions.accountid = accounts.id".
        	"           AND accounts.currencyid = ".$pairdata["refcurrencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND transactions.paypending = 0";
            if (!$rs=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));
            
            /*$sql = "SELECT SUM(transactions.amount) - ABS(IF(( ".
            "           SELECT SUM(CASE WHEN orders.side = 1 THEN orders.quantity ELSE orders.total END) AS amount ".
            "           FROM orders ".
            "           WHERE orders.canceled IS NULL ".
            "           AND accounts.currencyid = ".$pairdata["refcurrencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND accounts.entityusrid = -1 AND accounts.paymentmethodid = -1  ".
        	"           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.canceled IS NULL ".
            "           AND accounts.currencyid = ".$pairdata["refcurrencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND accounts.entityusrid = -1 AND accounts.paymentmethodid = -1  ".
        	"           AND orders.type != 1), 0)".
            "       + IF(( ".
            "           SELECT SUM(transactions.amount) AS amount ".
            "           FROM transactions, orders ".
            "           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 
                    AND accounts.entityusrid = -1
                    AND orders.type != 1) IS NOT NULL, ( ".
            "           SELECT SUM(transactions.amount) AS amount ".
            "           FROM transactions, orders ".
            "           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 
                    AND accounts.entityusrid = -1
                    AND orders.type != 1),0) ".
        	"      + IF ((SELECT SUM(transactions.amount + transactions.amountghost) AS amount ".
        	"           FROM transactions  ".
        	"           WHERE transactions.paypending = 1".
        "           AND accounts.id = transactions.accountid".
        "           AND accounts.currencyid = ".$pairdata["refcurrencyid"].
        "           AND accounts.userid = ".$userid.
        "           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 accounts.id = transactions.accountid".
        "           AND accounts.currencyid = ".$pairdata["refcurrencyid"].
        "           AND accounts.userid = ".$userid.
        "           AND NOT EXISTS(SELECT withdrawals.transactionid FROM withdrawals WHERE withdrawals.transactionid = transactions.id)), 0)".
        	"      ) as qty FROM transactions, accounts WHERE ".
        	"       transactions.accountid = accounts.id ".
        	"       AND accounts.currencyid = ".$pairdata["refcurrencyid"]."
                    AND accounts.userid = ".$userid.
            "       AND transactions.paypending = 0";
	        if (!$rs=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));*/
        
            $roworders = $rs->fetch_assoc();
    
            //Si el saldo es menor a cantidad*precio retornamos error 403-saldo insuficiente
            if(($roworders["qty"]) < $qty){
                badEnd('403', array('a'=>$roworders,'sql'=>$sql,'msg'=>determinateMsgFail(array(
                    "esp"=>'Cantidad insuficiente',
                    "eng"=>"Insufficient quantity"), $lang, '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 por sus ordenes
            /*$sql = "SELECT SUM(transactions.amount) - ABS(IF(( ".
            "           SELECT SUM(CASE WHEN orders.side = 1 THEN orders.quantity ELSE orders.total END) AS amount ".
            "           FROM orders ".
            "           WHERE orders.canceled IS NULL ".
            "           AND accounts.currencyid = ".$pairdata["refcurrencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND accounts.entityusrid = -1 AND accounts.paymentmethodid = -1  ".
        	"           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.canceled IS NULL ".
            "           AND accounts.currencyid = ".$pairdata["refcurrencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND accounts.entityusrid = -1 AND accounts.paymentmethodid = -1  ".
        	"           AND orders.type != 1), 0)".
            "       + IF(( ".
            "           SELECT SUM(transactions.amount) AS amount ".
            "           FROM transactions, orders ".
            "           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 
                    AND accounts.entityusrid = -1
                    AND orders.type != 1) IS NOT NULL, ( ".
            "           SELECT SUM(transactions.amount) AS amount ".
            "           FROM transactions, orders ".
            "           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 
                    AND accounts.entityusrid = -1
                    AND orders.type != 1),0) ".
        	"      + IF ((SELECT SUM(transactions.amount + transactions.amountghost) AS amount ".
        	"           FROM transactions  ".
        	"           WHERE transactions.paypending = 1".
        "           AND accounts.id = transactions.accountid".
        "           AND accounts.currencyid = ".$pairdata["refcurrencyid"].
        "           AND accounts.userid = ".$userid.
        "           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 accounts.id = transactions.accountid".
        "           AND accounts.currencyid = ".$pairdata["refcurrencyid"].
        "           AND accounts.userid = ".$userid.
        "           AND NOT EXISTS(SELECT withdrawals.transactionid FROM withdrawals WHERE withdrawals.transactionid = transactions.id)), 0)".
        	"      ) as qty FROM transactions, accounts WHERE ".
        	"       transactions.accountid = accounts.id ".
        	"       AND accounts.currencyid = ".$pairdata["refcurrencyid"]."
                    AND accounts.userid = ".$userid.
            "       AND transactions.paypending = 0";*/
            $sql = "SELECT SUM(amount) - ABS(IF(( ".
            "           SELECT SUM(CASE WHEN orders.side = 1 THEN orders.quantity ELSE orders.total END) AS amount ".
            "           FROM orders, accounts ".
            "           WHERE accounts.currencyid = ".$pairdata["currencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND orders.accountid = accounts.id".
        	"           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, accounts ".
            "           WHERE accounts.currencyid = ".$pairdata["currencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND orders.accountid = accounts.id".
        	"           AND orders.canceled IS NULL ".
        	"           AND orders.type != 1), 0)".
            "       + IF(( ".
            "           SELECT SUM(transactions.amount) AS amount ".
            "           FROM transactions, orders, accounts ".
            "           WHERE accounts.currencyid = ".$pairdata["currencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND orders.id = transactions.orderid ".
            "           AND transactions.accountid = accounts.id".
            "           AND orders.accountid = accounts.id".
        	"           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, accounts ".
            "           WHERE accounts.currencyid = ".$pairdata["currencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND orders.id = transactions.orderid ".
            "           AND transactions.accountid = accounts.id".
            "           AND orders.accountid = accounts.id".
        	"           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, accounts ".
        	"           WHERE accounts.currencyid = ".$pairdata["currencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND transactions.paypending = 1".
        	"           AND transactions.accountid = accounts.id".
        	"           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, accounts ".
        	"           WHERE accounts.currencyid = ".$pairdata["currencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND transactions.paypending = 1".
        	"           AND transactions.accountid = accounts.id".
        	"           AND NOT EXISTS(SELECT withdrawals.transactionid FROM withdrawals WHERE withdrawals.transactionid = transactions.id)), 0)".
        	"      ) as qty FROM transactions, accounts ".
        	"           WHERE transactions.accountid = accounts.id".
        	"           AND accounts.currencyid = ".$pairdata["currencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND transactions.paypending = 0";
	        if (!$rs=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));
        
            $roworders = $rs->fetch_assoc();
    
            //Si el saldo es menor a cantidad*precio retornamos error 403-saldo insuficiente
            if($roworders["qty"] < ($price * $qty)){
                badEnd('403', array('msg'=>determinateMsgFail(array(
                    "esp"=>'Saldo insuficiente 22',
                    "eng"=>"Insufficient balance 22"), $lang, 'Saldo insuficiente 22')));
            }
        
        }else{
            
            badEnd('500',array('msg'=>'El parametro side debe ser 1(venta) o 2(compra)'));
        }
    }
    
    function validateBalance2($userid, $db, $side, $pairdata, $qty, $price){
        // Consultamos el lang del usuario
        $sql = "SELECT lang FROM users WHERE id - ".$userid;
        if (!$rs=$db->query($sql)){
            $error = determinateDBError($db);
            $db->rollback();
            $db->close();
            badEnd("500", array("msg"=>$error));
        }
        
        $row = $rs->fetch_assoc();
        $lang = $row['lang'];
        
        //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 por sus ordenes
            $sql = "SELECT SUM(amount) - ABS(IF(( ".
            "           SELECT SUM(CASE WHEN orders.side = 1 THEN orders.quantity ELSE orders.total END) AS amount ".
            "           FROM orders, accounts ".
            "           WHERE accounts.currencyid = ".$pairdata["refcurrencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND orders.accountid = accounts.id".
        	"           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, accounts ".
            "           WHERE accounts.currencyid = ".$pairdata["refcurrencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND orders.accountid = accounts.id".
        	"           AND orders.canceled IS NULL ".
        	"           AND orders.type != 1), 0)".
            "       + IF(( ".
            "           SELECT SUM(transactions.amount) AS amount ".
            "           FROM transactions, orders, accounts ".
            "           WHERE accounts.currencyid = ".$pairdata["refcurrencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND orders.id = transactions.orderid ".
            "           AND transactions.accountid = accounts.id".
            "           AND orders.accountid = accounts.id".
        	"           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, accounts ".
            "           WHERE accounts.currencyid = ".$pairdata["refcurrencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND orders.id = transactions.orderid ".
            "           AND transactions.accountid = accounts.id".
            "           AND orders.accountid = accounts.id".
        	"           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, accounts ".
        	"           WHERE accounts.currencyid = ".$pairdata["refcurrencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND transactions.paypending = 1".
        	"           AND transactions.accountid = accounts.id".
        	"           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, accounts ".
        	"           WHERE accounts.currencyid = ".$pairdata["refcurrencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND transactions.paypending = 1".
        	"           AND transactions.accountid = accounts.id".
        	"           AND NOT EXISTS(SELECT withdrawals.transactionid FROM withdrawals WHERE withdrawals.transactionid = transactions.id)), 0)".
        	"      ) as qty FROM transactions, accounts ".
        	"           WHERE transactions.accountid = accounts.id".
        	"           AND accounts.currencyid = ".$pairdata["refcurrencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND transactions.paypending = 0";
            
            /*$sql = "SELECT SUM(transactions.amount) - ABS(IF(( ".
            "           SELECT SUM(CASE WHEN orders.side = 1 THEN orders.quantity ELSE orders.total END) AS amount ".
            "           FROM orders ".
            "           WHERE orders.canceled IS NULL ".
            "           AND accounts.currencyid = ".$pairdata["refcurrencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND accounts.entityusrid = -1 AND accounts.paymentmethodid = -1  ".
        	"           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.canceled IS NULL ".
            "           AND accounts.currencyid = ".$pairdata["refcurrencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND accounts.entityusrid = -1 AND accounts.paymentmethodid = -1  ".
        	"           AND orders.type != 1), 0)".
            "       + IF(( ".
            "           SELECT SUM(transactions.amount) AS amount ".
            "           FROM transactions, orders ".
            "           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 
                    AND accounts.entityusrid = -1
                    AND orders.type != 1) IS NOT NULL, ( ".
            "           SELECT SUM(transactions.amount) AS amount ".
            "           FROM transactions, orders ".
            "           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 
                    AND accounts.entityusrid = -1
                    AND orders.type != 1),0) ".
        	"      + IF ((SELECT SUM(transactions.amount + transactions.amountghost) AS amount ".
        	"           FROM transactions  ".
        	"           WHERE transactions.paypending = 1".
        "           AND accounts.id = transactions.accountid".
        "           AND accounts.currencyid = ".$pairdata["refcurrencyid"].
        "           AND accounts.userid = ".$userid.
        "           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 accounts.id = transactions.accountid".
        "           AND accounts.currencyid = ".$pairdata["refcurrencyid"].
        "           AND accounts.userid = ".$userid.
        "           AND NOT EXISTS(SELECT withdrawals.transactionid FROM withdrawals WHERE withdrawals.transactionid = transactions.id)), 0)".
        	"      ) as qty FROM transactions, accounts WHERE ".
        	"       transactions.accountid = accounts.id ".
        	"       AND accounts.currencyid = ".$pairdata["refcurrencyid"]."
                    AND accounts.userid = ".$userid.
            "       AND transactions.paypending = 0";*/
    	        if (!$rs=$db->query($sql)){
                    $error = determinateDBError($db);
                    $db->rollback();
                    $db->close();
                    badEnd("500", array("msg"=>$error));
                }
        
            $roworders = $rs->fetch_assoc();
    
            //Si el saldo es menor a cantidad*precio retornamos error 403-saldo insuficiente
            if(($roworders["qty"]) < $qty){
                $db->rollback();
                $db->close();
                badEnd('403', array('testing'=>$roworders, 'a'=>'aaaa','msg'=>determinateMsgFail(array(
                    "esp"=>'Cantidad insuficiente2',
                    "eng"=>"Insufficient quantity"), $lang, 'Cantidad insuficiente2')));
            }
        }else if($side == 2){
            //Al ser una compra, validamos que el usuario posea la cantidad*precio de la moneda base
        
            //Consultamos el balance por sus ordenes
            $sql = "SELECT SUM(amount) - ABS(IF(( ".
            "           SELECT SUM(CASE WHEN orders.side = 1 THEN orders.quantity ELSE orders.total END) AS amount ".
            "           FROM orders, accounts ".
            "           WHERE accounts.currencyid = ".$pairdata["currencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND orders.accountid = accounts.id".
        	"           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, accounts ".
            "           WHERE accounts.currencyid = ".$pairdata["currencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND orders.accountid = accounts.id".
        	"           AND orders.canceled IS NULL ".
        	"           AND orders.type != 1), 0)".
            "       + IF(( ".
            "           SELECT SUM(transactions.amount) AS amount ".
            "           FROM transactions, orders, accounts ".
            "           WHERE accounts.currencyid = ".$pairdata["currencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND orders.id = transactions.orderid ".
            "           AND transactions.accountid = accounts.id".
            "           AND orders.accountid = accounts.id".
        	"           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, accounts ".
            "           WHERE accounts.currencyid = ".$pairdata["currencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND orders.id = transactions.orderid ".
            "           AND transactions.accountid = accounts.id".
            "           AND orders.accountid = accounts.id".
        	"           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, accounts ".
        	"           WHERE accounts.currencyid = ".$pairdata["currencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND transactions.paypending = 1".
        	"           AND transactions.accountid = accounts.id".
        	"           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, accounts ".
        	"           WHERE accounts.currencyid = ".$pairdata["currencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND transactions.paypending = 1".
        	"           AND transactions.accountid = accounts.id".
        	"           AND NOT EXISTS(SELECT withdrawals.transactionid FROM withdrawals WHERE withdrawals.transactionid = transactions.id)), 0)".
        	"      ) as qty FROM transactions, accounts ".
        	"           WHERE transactions.accountid = accounts.id".
        	"           AND accounts.currencyid = ".$pairdata["currencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND transactions.paypending = 0";
            /*$sql = "SELECT SUM(transactions.amount) - ABS(IF(( ".
            "           SELECT SUM(CASE WHEN orders.side = 1 THEN orders.quantity ELSE orders.total END) AS amount ".
            "           FROM orders ".
            "           WHERE orders.canceled IS NULL ".
            "           AND accounts.currencyid = ".$pairdata["refcurrencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND accounts.entityusrid = -1 AND accounts.paymentmethodid = -1  ".
        	"           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.canceled IS NULL ".
            "           AND accounts.currencyid = ".$pairdata["refcurrencyid"].
            "           AND accounts.userid = ".$userid.
            "           AND accounts.entityusrid = -1 AND accounts.paymentmethodid = -1  ".
        	"           AND orders.type != 1), 0)".
            "       + IF(( ".
            "           SELECT SUM(transactions.amount) AS amount ".
            "           FROM transactions, orders ".
            "           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 
                    AND accounts.entityusrid = -1
                    AND orders.type != 1) IS NOT NULL, ( ".
            "           SELECT SUM(transactions.amount) AS amount ".
            "           FROM transactions, orders ".
            "           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 
                    AND accounts.entityusrid = -1
                    AND orders.type != 1),0) ".
        	"      + IF ((SELECT SUM(transactions.amount + transactions.amountghost) AS amount ".
        	"           FROM transactions  ".
        	"           WHERE transactions.paypending = 1".
        "           AND accounts.id = transactions.accountid".
        "           AND accounts.currencyid = ".$pairdata["refcurrencyid"].
        "           AND accounts.userid = ".$userid.
        "           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 accounts.id = transactions.accountid".
        "           AND accounts.currencyid = ".$pairdata["refcurrencyid"].
        "           AND accounts.userid = ".$userid.
        "           AND NOT EXISTS(SELECT withdrawals.transactionid FROM withdrawals WHERE withdrawals.transactionid = transactions.id)), 0)".
        	"      ) as qty FROM transactions, accounts WHERE ".
        	"       transactions.accountid = accounts.id ".
        	"       AND accounts.currencyid = ".$pairdata["refcurrencyid"]."
                    AND accounts.userid = ".$userid.
            "       AND transactions.paypending = 0";*/
	        if (!$rs=$db->query($sql)){
                $error = determinateDBError($db);
                $db->rollback();
                $db->close();
                badEnd("500", array("msg"=>$error));
            }
        
            $roworders = $rs->fetch_assoc();
    
            //Si el saldo es menor a cantidad*precio retornamos error 403-saldo insuficiente
            if($roworders["qty"] < ($price * $qty)){
                $db->rollback();
            $db->close();
                badEnd('403', array('testing'=>'hola','msg'=>determinateMsgFail(array(
                    "esp"=>'Saldo insuficiente',
                    "eng"=>"Insufficient balance"), $lang, 'Saldo insuficiente')));
            }
        
        }else{
            $db->rollback();
            $db->close();
            badEnd('500',array('msg'=>'El parametro side debe ser 1(venta) o 2(compra)'));
        }
    }
?>