<?php
// app/api/exchange/match
    error_reporting(E_ERROR | E_PARSE);
    header("Content-Type:application/json");
    include_once("../dbconn.php");
    include_once("../utils.php");
    
    $out = new stdClass();
    
    
    //Primero debemos consultar las orden market que tiene una pareja limit para hacer el intercambio
    //Lo hacemos en una sola consulta para asi tener los datos del Intercambio
    
    //NOTAS:
    
    //Una venta o compra limit puede ser completada parcialmente por una orden market,\
    //Igual que una orden market puede ser compleata parcialmente por ordenes limit
    
    //Las comisiones se aplican al momento del intercambio, siempre sera porcentual
    
    //Consultamos una compra con su venta para hacer el intercambio
    $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));
        
    //Recorremos los posibles intercambios
    while($row = $rs->fetch_assoc()){
        $db->autocommit(TRUE);
        //En cada vuelta validamos que una orden no haya sido completada
        //Validamos la orden principal
        $sql = 'SELECT 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 AS isComplete ".
        "       FROM orders o ".
        "       WHERE o.id = ".$row['orderprinid'];
        if (!$res=$db->query($sql)){
            badEnd("500", array("msg"=>$db->error.$sql));
        }
        
        $ordervalidprin = $res->fetch_assoc();
        
        //Validamos la orden secundaria
        $sql = 'SELECT 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 AS isComplete ".
        "       FROM orders o ".
        "       WHERE o.id = ".$row['ordersecid'];
        if (!$res=$db->query($sql)){
            badEnd("500", array("msg"=>$db->error.$sql));
        }
        $ordervalidsec = $res->fetch_assoc();
        
        if($ordervalidsec['isComplete'] != 0 || $ordervalidprin['isComplete'] != 0 || $row['userlevelprin'] == 0){
            //nose
        }else{
            //Necesitamos los decimales de cada moneda
            
            //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();
            
            //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();
        
            //Si ambas ordenes estan sin completar consultamos su filled
            
            // Consultamos el filled de la orden principal(LIMIT)
            $sql = "SELECT 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 ".
            "       FROM orders o ".
            "       WHERE (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 ".
            "       AND o.id = ".$row['orderprinid'];
            if (!$res=$db->query($sql)){
                badEnd("500", array("msg"=>$db->error.$sql));
            }
            
            $orderfilled = $res->fetch_assoc();
            
            //Calculamos el porcentaje de completado de le orden principal(LIMIT)
            $prinfilled = 0;
            if($row['orderprinqty'] != 0){
                $prinfilled = ($orderfilled['totallyprinfilled']*100)/$row['orderprinqty'];
            }
                
            // Consultamos el filled de la orden secundaria(market)
            $sql = "SELECT 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 totallysecfilled ".
            "       FROM orders o ".
            "       WHERE (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 ".
            "       AND o.id = ".$row['ordersecid'];
            if (!$res=$db->query($sql)){
                badEnd("500", array("msg"=>$db->error.$sql));
            }
            
            $orderfilled = $res->fetch_assoc();
        
            //Calculamos el porcentaje de completado de le orden secundaria(MARKET)
            $secfilled = 0;
            if($row['ordersecqty'] != 0){
                $secfilled = ($orderfilled['totallysecfilled']*100)/$row['ordersecqty'];                
            }
        
            //Primero obtenemos la orden con menor cantidad
            $qty = 0;
            $orderprinqty = 0;
            $ordersecqty = 0;
            
            //Obtenemos la cantidad de la orden principal
            if($row['orderprinqty'] != 0){
                $orderprinqty = number_format((($row['orderprinqty'] * (100 - $prinfilled))/100), $currencyrefdecimals['decimals'], '.', '');
            }
            
            //Obtenemos la cantidad de la orden secundaria
            if($row['ordersecqty'] != 0){
                $ordersecqty = number_format((($row['ordersecqty'] * (100 - $secfilled))/100), $currencyrefdecimals['decimals'], '.', '');
            }
            
            //Con eso validamos que una sea completada en su totalidad y la otra de manera parcial
            if($orderprinqty < $ordersecqty){
                $qty = $orderprinqty;
                $out->pctsec [] = $secfilled;
                $out->pctprin [] = $prinfilled;
            }elseif($orderprinqty > $ordersecqty){
                $qty = $ordersecqty;
                $out->pctsec [] = $secfilled;
                $out->pctprin [] = $prinfilled;
            }else{
                $out->pctsec [] = $secfilled;
                $out->pctprin [] = $prinfilled;
                $qty = $orderprinqty;
            }
            
            
            $out->qty [] = $qty;
        
            //luego de que tenemos la cantidad por la que se hara el intercambio podemos realizarlo
            //Guardamos las cuentas para ubicarlas mejor
            
            //Consultamos el fee de la cuenta principal
            $sql = "SELECT * FROM cmspreffees".
            "       WHERE pairid = ".$row['orderpair'].
            "       AND usertype = ".$row['usrtypeprin'].
            "       AND levelid = ".$row['userlevelprin'];
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>$db->error.$sql));
            
            $feedata = $res->fetch_assoc();
            
            //Cuentas de orden principal
            $orderprin = [
                'account' => $row['orderprinaccount'],
                'payment' => $row['orderprinpayment'],
                'orderid' => $row['orderprinid'],
                'userid' => $row['usridprin'],
                'userlevel' => $row['userlevelprin'],
                'usertype' => $row['usrtypeprin'],
                'orderside' => $row['orderprinside'],
                'pctfee' => $row['orderprintype'] == 1 ? floatval($feedata['pctmktfee']) : floatval($feedata['pctlimitfee']),
                'nmlfee' => $row['orderprintype'] == 1 ? floatval($feedata['nmlmktfee']) : floatval($feedata['nmllimitfee']),
            ];
            
            //Consultamos el tipo y nivel del segundo usuario
            $sql = "SELECT users.id AS usrid, users.type AS usrtype, users.level AS usrlevel ".
            "       FROM users, accounts ".
            "       WHERE accounts.id = ".$row['ordersecaccount'].
            "       AND users.id = accounts.userid";
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>$db->error.$sql));
            
            $userdata = $res->fetch_assoc();
            
            if($userdata['usrlevel'] != 0){
            //Consultamos el fee de la cuenta secundaria
                $sql = "SELECT * FROM cmspreffees".
                "       WHERE pairid = ".$row['orderpair'].
                "       AND usertype = ".$userdata['usrtype'].
                "       AND levelid = ".$userdata['usrlevel'];
                if (!$res=$db->query($sql))
                    badEnd("500", array("msg"=>$db->error.$sql));
            
                $feedata = $res->fetch_assoc();
        
                //Cuentas de la orden secundaria
                $ordersec = [
                    'account' => $row['ordersecaccount'],
                    'payment' => $row['ordersecpayment'],
                    'orderid' => $row['ordersecid'],
                    'userid' => $userdata['usrid'],
                    'userlevel' => $userdata['usrlevel'],
                    'usertype' => $userdata['usrtype'],
                    'orderside' => $row['ordersecside'],
                    'pctfee' => $row['ordersectype'] == 1 ? floatval($feedata['pctmktfee']) : floatval($feedata['pctlimitfee']),
                    'nmlfee' => $row['ordersectype'] == 1 ? floatval($feedata['nmlmktfee']) : floatval($feedata['nmllimitfee']),
                ];
        
                $db->autocommit(FALSE);
        
                //insertamos la salida de orden de VENTA
                $sql = "INSERT INTO transactions".
                "       (".
                "       datecreated, ".
                "       dsc, ".
                "       amountghost, ".
                "       amount, ".
                "       accountid, ".
                "       orderid, ".
                "       txid, ".
                "       accountorigin, ".
                "       accountdestination".
                "       )".
                "       VALUES(".
                "       NOW(), ".
                "       'Intercambio', ".
                "       0, ".
                "       -".$qty.", ".
                "       ".($orderprin['orderside'] == 1 ? $orderprin['account'] : $ordersec['account']).", ".
                "       ".($orderprin['orderside'] == 1 ? $orderprin['orderid'] : $ordersec['orderid']).", ".
                "       NULL, ".
                "       ".($orderprin['orderside'] == 1 ? $orderprin['account'] : $ordersec['account']).", ".
                "       ".($orderprin['orderside'] == 1 ? $ordersec['payment'] : $orderprin['payment']).
                "       )";
                if (!$db->query($sql)){
                    $error = $db->error;
                    $db->rollback();
                    $db->close();
                    badEnd("500", array("msg"=>$error.$sql.'------1'.$currencyrefdecimals));
                }
        
                //Guardamos el id de la transaccion de deposito
                $txid = (int)$db->insert_id;
                $out->id = $txid;
                
                //Le colocamos a la primera transferencia el txid
                $sql = "UPDATE transactions SET".
                "       txid = ".$txid.
                "       WHERE id = ".$txid;
                if (!$db->query($sql)){
                    $error = $db->error;
                    $db->rollback();
                    $db->close();   
                    badEnd("500", array("msg"=>$error));
                }  
        
            $realfee = number_format(((($orderprin['orderside'] == 1 ? $orderprin['pctfee'] : $ordersec['pctfee'])*($qty*$row['orderprinprice']))/100), $currencydecimals['decimals'], '.', '');
            $amountwfee = number_format(($qty*$row['orderprinprice']), $currencydecimals['decimals'], '.', '');
            
            //insertamos la entrada de order de VENTA
            $sql = "INSERT INTO transactions".
            "       (".
            "       datecreated, ".
            "       dsc, ".
            "       amountghost, ".
            "       amount, ".
            "       accountid, ".
            "       orderid, ".
            "       txid, ".
            "       accountorigin, ".
            "       accountdestination".
            "       )".
            "       VALUES(".
            "       NOW(), ".
            "       'Intercambio', ".
            "       0, ".
            "       ".$amountwfee.", ".
            "       ".($orderprin['orderside'] == 1 ? $orderprin['payment'] : $ordersec['payment']).", ".
            "       ".($orderprin['orderside'] == 1 ? $orderprin['orderid'] : $ordersec['orderid']).", ".
            "       ".$out->id.", ".
            "       ".($orderprin['orderside'] == 1 ? $ordersec['account'] : $orderprin['account']).", ".
            "       ".($orderprin['orderside'] == 1 ? $orderprin['payment'] : $ordersec['payment']).
            "       )";
            if (!$db->query($sql)){
                $error = $db->error;
                $db->rollback();
                $db->close();
                badEnd("500", array("msg"=>$error.$sql.'------2'));
            }
            
            //Consultamos los datos de la cuenta POTE para VENTA
            //Metodo de pago comisiones -5
            $sql = "SELECT * FROM accounts ".
            "       WHERE paymentmethodid = -5".
            "       AND currencyid = ".($orderprin['orderside'] == 2 ? $row['refcurrencyid'] : $row['currencyid']);
            if (!$res=$db->query($sql)){
                $error = $db->error;
                $db->rollback();
                $db->close();
                badEnd("500", array("msg"=>$error.$sql));
            }
            
            $accountfee = $res->fetch_assoc();
            
            //insertamos la comision de order de VENTA
            $sql = "INSERT INTO transactions".
            "       (".
            "       datecreated, ".
            "       dsc, ".
            "       amountghost, ".
            "       amount, ".
            "       accountid, ".
            "       orderid, ".
            "       txid, ".
            "       accountorigin, ".
            "       accountdestination".
            "       )".
            "       VALUES(".
            "       NOW(), ".
            "       'Comisión de intercambio', ".
            "       0, ".
            "       -".$realfee.", ".
            "       ".($orderprin['orderside'] == 1 ? $orderprin['payment'] : $ordersec['payment']).", ".
            "       ".($orderprin['orderside'] == 1 ? $orderprin['orderid'] : $ordersec['orderid']).", ".
            "       ".$out->id.", ".
            "       ".($orderprin['orderside'] == 1 ? $orderprin['payment'] : $ordersec['payment']).", ".
            "       ".$accountfee['id'].
            "       )";
            if (!$db->query($sql)){
                $error = $db->error;
                $db->rollback();
                $db->close();
                badEnd("500", array("msg"=>$error.$sql.'------3'));
            }
            
            //insertamos la comision de order de VENTA en la cuenta AFX
            $sql = "INSERT INTO transactions".
            "       (".
            "       datecreated, ".
            "       dsc, ".
            "       amountghost, ".
            "       amount, ".
            "       accountid, ".
            "       orderid, ".
            "       txid, ".
            "       accountorigin, ".
            "       accountdestination".
            "       )".
            "       VALUES(".
            "       NOW(), ".
            "       'Comisión de intercambio', ".
            "       0, ".
            "       ".$realfee.", ".
            "       ".$accountfee['id'].", ".
            "       ".($orderprin['orderside'] == 1 ? $orderprin['orderid'] : $ordersec['orderid']).", ".
            "       ".$out->id.", ".
            "       ".($orderprin['orderside'] == 1 ? $orderprin['payment'] : $ordersec['payment']).", ".
            "       ".$accountfee['id'].
            "       )";
            if (!$db->query($sql)){
                $error = $db->error;
                $db->rollback();
                $db->close();
                badEnd("500", array("msg"=>$error.$sql));
            }
            
            $realfee = (($orderprin['orderside'] == 2 ? $orderprin['pctfee'] : $ordersec['pctfee'])*$qty)/100;
            $qtywfee = $qty;
            
            //insertamos la entrada de orden de COMPRA
            $sql = "INSERT INTO transactions".
            "       (".
            "       datecreated, ".
            "       dsc, ".
            "       amountghost, ".
            "       amount, ".
            "       accountid, ".
            "       orderid, ".
            "       txid, ".
            "       accountorigin, ".
            "       accountdestination".
            "       )".
            "       VALUES(".
            "       NOW(), ".
            "       'Intercambio', ".
            "       0, ".
            "       ".$qtywfee.", ".
            "       ".($orderprin['orderside'] == 2 ? $orderprin['payment'] : $ordersec['payment']).", ".
            "       ".($orderprin['orderside'] == 2 ? $orderprin['orderid'] : $ordersec['orderid']).", ".
            "       ".$out->id.", ".
            "       ".($orderprin['orderside'] == 2 ? $ordersec['account'] : $orderprin['account']).", ".
            "       ".($orderprin['orderside'] == 2 ? $orderprin['payment'] : $ordersec['payment']).
            "       )";
            if (!$db->query($sql)){
                $error = $db->error;
                $db->rollback();
                $db->close();
                badEnd("500", array("msg"=>$error.$sql));
            }
            
            //Consultamos los datos de la cuenta POTE para COMPRA
            //Metodo de pago comisiones -5
            $sql = "SELECT * FROM accounts ".
            "       WHERE paymentmethodid = -5".
            "       AND currencyid = ".($orderprin['orderside'] == 1 ? $row['refcurrencyid'] : $row['currencyid']);
            if (!$res=$db->query($sql)){
                $error = $db->error;
                $db->rollback();
                $db->close();
                badEnd("500", array("msg"=>$error.$sql));
            }
            
            $accountfee = $res->fetch_assoc();
            
            //insertamos la resta de la comisiom
            $sql = "INSERT INTO transactions".
            "       (".
            "       datecreated, ".
            "       dsc, ".
            "       amountghost, ".
            "       amount, ".
            "       accountid, ".
            "       orderid, ".
            "       txid, ".
            "       accountorigin, ".
            "       accountdestination".
            "       )".
            "       VALUES(".
            "       NOW(), ".
            "       'Comisión de intercambio', ".
            "       0, ".
            "       -".$realfee.", ".
            "       ".($orderprin['orderside'] == 2 ? $orderprin['payment'] : $ordersec['payment']).", ".
            "       ".($orderprin['orderside'] == 2 ? $orderprin['orderid'] : $ordersec['orderid']).", ".
            "       ".$out->id.", ".
            "       ".($orderprin['orderside'] == 2 ? $orderprin['payment'] : $ordersec['payment']).", ".
            "       ".$accountfee['id'].
            "       )";
            if (!$db->query($sql)){
                $error = $db->error;
                $db->rollback();
                $db->close();
                badEnd("500", array("msg"=>$error.$sql));
            }
            
            //insertamos la resta de la comisiom en AFX positivo
            $sql = "INSERT INTO transactions".
            "       (".
            "       datecreated, ".
            "       dsc, ".
            "       amountghost, ".
            "       amount, ".
            "       accountid, ".
            "       orderid, ".
            "       txid, ".
            "       accountorigin, ".
            "       accountdestination".
            "       )".
            "       VALUES(".
            "       NOW(), ".
            "       'Comisión de intercambio', ".
            "       0, ".
            "       ".$realfee.", ".
            "       ".$accountfee['id'].", ".
            "       ".($orderprin['orderside'] == 2 ? $orderprin['orderid'] : $ordersec['orderid']).", ".
            "       ".$out->id.", ".
            "       ".($orderprin['orderside'] == 2 ? $orderprin['payment'] : $ordersec['payment']).", ".
            "       ".$accountfee['id'].
            "       )";
            if (!$db->query($sql)){
                $error = $db->error;
                $db->rollback();
                $db->close();
                badEnd("500", array("msg"=>$error.$sql));
            }
            
                //insertamos la SALIDA de order de COMPRA
                $sql = "INSERT INTO transactions".
                "       (".
                "       datecreated, ".
                "       dsc, ".
                "       amountghost, ".
                "       amount, ".
                "       accountid, ".
                "       orderid, ".
                "       txid, ".
                "       accountorigin, ".
                "       accountdestination".
                "       )".
                "       VALUES(".
                "       NOW(), ".
                "       'Intercambio', ".
                "       0, ".
                "       -".($qty*$row['orderprinprice']).", ".
                "       ".($orderprin['orderside'] == 2 ? $orderprin['account'] : $ordersec['account']).", ".
                "       ".($orderprin['orderside'] == 2 ? $orderprin['orderid'] : $ordersec['orderid']).", ".
                "       ".$out->id.", ".
                "       ".($orderprin['orderside'] == 2 ? $orderprin['account'] : $ordersec['account']).", ".
                "       ".($orderprin['orderside'] == 2 ? $ordersec['payment'] : $orderprin['payment']).
                "       )";
                if (!$db->query($sql)){
                    $error = $db->error;
                    $db->rollback();
                    $db->close();
                    badEnd("500", array("msg"=>$error.$sql));
                }
        
                $db->commit();
                
                /******************AQUI VALIDAMOS QUE LA ORDEN FUE EJECUTADA COMPLETAMENTE, Y SI FUE ASI TOCA COLOCARLE SU PRECIO REAL********/
                $db->autocommit(TRUE);
                
                //Validamos la orden secundaria (MARKET)
                $sql = 'SELECT 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 AS isComplete ".
                "       FROM orders o ".
                "       WHERE o.id = ".$row['ordersecid'];
                if (!$res=$db->query($sql)){
                    badEnd("500", array("msg"=>$db->error.$sql));
                }
                
                $ordervalidsec = $res->fetch_assoc();
                
                //SI la segunda orden es market y encima ya fue completada(cantidad)
                //Nos toca ver cual es su promedio de VERDAD
                if($row['ordersectype'] == 1 && $ordervalidsec['isComplete'] == 1){
                    $sql = "SELECT orders.id AS orderid, 
                            orders.quantity AS orderqty, 
                            IF(orders.type = 1, orders.side, IF(orders.side = 1, 2, 1)) AS orderside, 
                            (SELECT ord.price FROM orders ord 
                            WHERE ord.type = 2 AND (ord.id = t.orderid OR ord.id = txother.orderid)) AS orderprice, 
                            orders.type AS ordertype, 
                            orders.pairid AS orderpair, 
                            orders.total AS ordertotal, 
                            t.id AS txid, 
                            t.txid AS exchangeid, 
                            t.orderid AS txotherorderid, 
                            t.datecreated AS datecreated, 
                            ABS(IF(orders.side = 2, t.amount, tx.amount)) AS qty, 
                            (IF(orders.side = 2, t.amount, tx.amount) * orders.price) AS totally, 
                            users.id AS usrid, 
                            users.usr AS usr, 
                            users.type AS usertype
                            FROM orders, accounts, users, transactions t 
                                LEFT JOIN transactions tx 
                                ON t.orderid = tx.orderid 
                                AND t.txid = tx.txid 
                                AND tx.amount < 0 
                                AND tx.dsc NOT LIKE 'Comisi%'
                                LEFT JOIN transactions txother 
                                ON t.orderid != txother.orderid 
                                AND t.txid = txother.txid 
                                AND txother.amount > 0 
                                AND txother.dsc NOT LIKE 'Comisi%'
                            WHERE t.orderid IS NOT NULL 
                            AND orders.id = txother.orderid 
                            AND IF(orders.type = 2, t.orderid, txother.orderid) = ".$row['ordersecid']."
                            AND t.id = t.txid 
                            AND t.dsc NOT LIKE 'Comisi%'
                            AND accounts.id = orders.accountid 
                            AND accounts.userid = users.id
                            GROUP BY t.txid ";
                    if (!$resprom=$db->query($sql)){
                        badEnd("500", array("msg"=>$db->error.$sql));
                    }
                    
                    echo "id: ".$row['ordersecid']."<br>";
                    
                    //Consultamos la cantidad TOTAL
                    $sql = "SELECT quantity AS qty ".
                    "       FROM orders ".
                    "       WHERE id = ".$row['ordersecid'];
                    if (!$restotal=$db->query($sql)){
                        badEnd("500", array("msg"=>$db->error.$sql));
                    }
                    
                    $marketTotal = $restotal->fetch_assoc();
                    
                    //Inicializamos los valores a calcular
                    $amountprices = 0;
                    while($orderprom = $resprom->fetch_assoc()){
                        $pctparcial = 0;
                        $pctparcial = ($orderprom['qty'] * 100) / $marketTotal['qty'];
                        
                        $promprice = 0;
                        $promprice = number_format((($pctparcial * $orderprom['orderprice']) / 100), $currencydecimals['decimals'], '.', '');
                        
                        //Sumamos los precios
                        $amountprices += $promprice;
                    }
                    
                    $sql = "UPDATE orders SET".
                    "       price = ".$amountprices.", ".
                    "       total = (quantity * price)".
                    "       WHERE id = ".$row['ordersecid'];
                    if (!$db->query($sql)){
                        badEnd("500", array("msg"=>$db->error.$sql));
                    }
                    
                    echo "Precio: ".$amountprices."<br>";
                }
            }
        }
    }
    
    $db->autocommit(TRUE);
    
    //Consultamos los pares que existen para ver su mercado
    $sql = "SELECT * FROM pairs";
    if (!$rs=$db->query($sql)){
        badEnd("500", array("msg"=>$db->error.$sql));
    }
    
    while($row = $rs->fetch_assoc()){
        // Validamos que exista liquidez para ventas
        $sql = "SELECT SUM(o.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))) AS market
                FROM orders o 
            WHERE o.side = 1 
            AND o.pairid = ".$row['id']."
            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";
        if (!$res=$db->query($sql)){
            badEnd("500", array("msg"=>$db->error.$sql));
        }
        
        $avaliable = $res->fetch_assoc();
        
        //Si la cantidad disponible de VENTA es 0 o null 
        if($avaliable['market'] == 0 || $avaliable['market'] == NULL){
            //Cancelamos todas las ordenes market de COMPRA
            $sql = "SELECT * FROM orders o
                    WHERE o.type = 1 
                    AND o.side = 2
                    AND o.canceled IS NULL 
                    AND o.pairid = ".$row['id']."
                    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 (!$res=$db->query($sql)){
                badEnd("500", array("msg"=>$db->error.$sql));
            }
            
            while($order = $res->fetch_assoc()){
                // Antes de cancelar debemos calcular el precio ponderado
                $sql = "SELECT orders.id AS orderid, 
                        orders.quantity AS orderqty, 
                        IF(orders.type = 1, orders.side, IF(orders.side = 1, 2, 1)) AS orderside, 
                        (SELECT ord.price FROM orders ord 
                        WHERE ord.type = 2 AND (ord.id = t.orderid OR ord.id = txother.orderid)) AS orderprice, 
                        orders.type AS ordertype, 
                        orders.pairid AS orderpair, 
                        orders.total AS ordertotal, 
                        t.id AS txid, 
                        t.txid AS exchangeid, 
                        t.orderid AS txotherorderid, 
                        t.datecreated AS datecreated, 
                        ABS(IF(orders.side = 2, t.amount, tx.amount)) AS qty
                        FROM orders, accounts, users, transactions t 
                            LEFT JOIN transactions tx 
                            ON t.orderid = tx.orderid 
                            AND t.txid = tx.txid 
                            AND tx.amount < 0 
                            AND tx.dsc NOT LIKE 'Comisi%'
                            LEFT JOIN transactions txother 
                            ON t.orderid != txother.orderid 
                            AND t.txid = txother.txid 
                            AND txother.amount > 0 
                            AND txother.dsc NOT LIKE 'Comisi%'
                        WHERE t.orderid IS NOT NULL 
                        AND orders.id = txother.orderid 
                        AND IF(orders.type = 2, t.orderid, txother.orderid) = ".$order['id']."
                        AND t.id = t.txid 
                        AND t.dsc NOT LIKE 'Comisi%'
                        AND accounts.id = orders.accountid 
                        AND accounts.userid = users.id
                        GROUP BY t.txid ";
                if (!$resprom=$db->query($sql)){
                    badEnd("500", array("msg"=>$db->error.$sql));
                }
                    
                echo "id: ".$order['id']."<br>";
                    
                //Inicializamos los valores a calcular
                $amountprices = 0;
                while($orderprom = $resprom->fetch_assoc()){
                    $pctparcial = 0;
                    $pctparcial = ($orderprom['qty'] * 100) / $order['quantity'];
                    
                    $promprice = 0;
                    $promprice = number_format((($pctparcial * $orderprom['orderprice']) / 100), $currencydecimals['decimals'], '.', '');
                    
                    //Sumamos los precios
                    $amountprices += $promprice;
                }
                    
                $sql = "UPDATE orders SET".
                "       price = ".number_format($amountprices, $currencydecimals['decimals'], '.', '').", ".
                "       total = (quantity * price)".
                "       WHERE id = ".$order['id'];
                if (!$db->query($sql)){
                    badEnd("500", array("msg"=>$db->error.$sql));
                }
                    
                $sql = "UPDATE orders SET ".
                "       canceled = NOW() ".
                "       WHERE id = ".$order['id'].
                "       AND type = 1 ".
                "       AND side = 2";
                if (!$db->query($sql)){
                    badEnd("500", array("msg"=>$db->error.$sql));
                }
            }
        }
        
        // Validamos que exista liquidez para compras
        $sql = "SELECT SUM(o.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))) AS market
                FROM orders o 
            WHERE o.side = 2 
            AND o.pairid = ".$row['id']."
            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";
        if (!$res=$db->query($sql)){
            badEnd("500", array("msg"=>$db->error.$sql));
        }
        
        $avaliable = $res->fetch_assoc();
        
        //Si la cantidad disponible de COMPRA es 0 o null 
        if($avaliable['market'] == 0 || $avaliable['market'] == NULL){
            //Cancelamos todas las ordenes market de VENTA
            $sql = "SELECT * FROM orders o
                    WHERE o.type = 1 
                    AND o.side = 1
                    AND o.canceled IS NULL 
                    AND o.pairid = ".$row['id']."
                    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 (!$res=$db->query($sql)){
                badEnd("500", array("msg"=>$db->error.$sql));
            }
            
            while($order = $res->fetch_assoc()){
                
                // Antes de cancelar debemos calcular el precio ponderado
                $sql = "SELECT orders.id AS orderid, 
                        orders.quantity AS orderqty, 
                        IF(orders.type = 1, orders.side, IF(orders.side = 1, 2, 1)) AS orderside, 
                        (SELECT ord.price FROM orders ord 
                        WHERE ord.type = 2 AND (ord.id = t.orderid OR ord.id = txother.orderid)) AS orderprice, 
                        orders.type AS ordertype, 
                        orders.pairid AS orderpair, 
                        orders.total AS ordertotal, 
                        t.id AS txid, 
                        t.txid AS exchangeid, 
                        t.orderid AS txotherorderid, 
                        t.datecreated AS datecreated, 
                        ABS(IF(orders.side = 2, t.amount, tx.amount)) AS qty
                        FROM orders, accounts, users, transactions t 
                            LEFT JOIN transactions tx 
                            ON t.orderid = tx.orderid 
                            AND t.txid = tx.txid 
                            AND tx.amount < 0 
                            AND tx.dsc NOT LIKE 'Comisi%'
                            LEFT JOIN transactions txother 
                            ON t.orderid != txother.orderid 
                            AND t.txid = txother.txid 
                            AND txother.amount > 0 
                            AND txother.dsc NOT LIKE 'Comisi%'
                        WHERE t.orderid IS NOT NULL 
                        AND orders.id = txother.orderid 
                        AND IF(orders.type = 2, t.orderid, txother.orderid) = ".$order['id']."
                        AND t.id = t.txid 
                        AND t.dsc NOT LIKE 'Comisi%'
                        AND accounts.id = orders.accountid 
                        AND accounts.userid = users.id
                        GROUP BY t.txid ";
                if (!$resprom=$db->query($sql)){
                    badEnd("500", array("msg"=>$db->error.$sql));
                }
                    
                echo "id: ".$order['id']."<br>";
                    
                //Inicializamos los valores a calcular
                $amountprices = 0;
                while($orderprom = $resprom->fetch_assoc()){
                    $pctparcial = 0;
                    $pctparcial = ($orderprom['qty'] * 100) / $order['quantity'];
                    
                    $promprice = 0;
                    $promprice = number_format((($pctparcial * $orderprom['orderprice']) / 100), $currencydecimals['decimals'], '.', '');
                    
                    //Sumamos los precios
                    $amountprices += $promprice;
                }
                    
                $sql = "UPDATE orders SET".
                "       price = ".number_format($amountprices, $currencydecimals['decimals'], '.', '').", ".
                "       total = (quantity * price)".
                "       WHERE id = ".$order['id'];
                if (!$db->query($sql)){
                    badEnd("500", array("msg"=>$db->error.$sql));
                }
                
                $sql = "UPDATE orders SET ".
                "       canceled = NOW() ".
                "       WHERE id = ".$order['id'].
                "       AND type = 1 ".
                "       AND side = 1";
                if (!$db->query($sql)){
                    badEnd("500", array("msg"=>$db->error.$sql));
                }
            }
        }
    }
    
    $db->close();  
    
?>