<?php 
    function getOrders($db, $ownfilterorders, $pairid, $ordernumofrec, $userid, $ownorders){
        if($ownorders){ //Se crea el filtro en caso de que se pida el reques de solo las nuestras
            $filtacc =  "       INNER JOIN     accounts A ".
                        "       ON             A.id = O.accountid " .
                        "       AND            A.userid = ".$userid." ";

            
            $filtsideA =  "AND O.side = 1 GROUP BY O.id LIMIT ".$ordernumofrec;            
            $filtsideB =  "AND O.side = 2 GROUP BY O.id LIMIT ".$ordernumofrec;
        }else{
            $filtacc =  "";
            
            $filtsideA =  "AND O.side = 1 GROUP BY O.price, O.side LIMIT ".$ordernumofrec;
            $filtsideB =  "AND O.side = 2 GROUP BY O.price, O.side LIMIT ".$ordernumofrec;
        }
         
        
        $fill = "   SELECT O.id orderid, " .
                "   SUM(ABS(T.amount)) filled ".
                "   FROM        transactions T ".
                "   INNER JOIN  accounts A ON T.accountid = A.id ".
                "   INNER JOIN  orders O " .
                "   ON          O.id = T.orderid ".
                "   AND         (".
                "                   T.accountid = O.accountid ".
                "                   OR  (T.accountid = O.paymentid AND T.amount > 0) " .
                "               ) " .
                "   INNER JOIN  pairs P ON P.id = O.pairid AND A.currencyid = P.refcurrencyid ".
                "   WHERE    T.id ".
                "   IN ( ".
                "       SELECT      T.id ".
                "       FROM        transactions T ".
                "       INNER JOIN  orders O ON ".
                "                   T.orderid = O.id ".  
                "       GROUP BY    O.id ".
                "   ) GROUP BY O.id";

        $own =  "   SELECT O.id ".
                "   FROM orders O".
                "   INNER JOIN accounts A".
                "   ON A.userid = ".$userid. 
                "   AND (A.id = O.accountid OR A.id = O.paymentid) ".
                "   GROUP BY O.id";
    
        $sql =      "SELECT 	P.id AS pairsid, " .
                    "           P.refcurrencyid AS pairsrefcurrency,  ".
                    "           C.name AS currencyname, ".
                    "           C.symbol AS currencysymbol, ".
                    "           C.icon AS currencyicon, ".
                    "           C.decimals AS currencydecimals, ".
                    "           C.id AS currencyid, ".
                    "           CR.name AS refcurrencyname, ".
                    "           CR.symbol AS refcurrencysymbol, ".
                    "           CR.icon AS refcurrencyicon, ".
                    "           CR.decimals AS refcurrencydecimals, ".
                    "           GROUP_CONCAT(O.id SEPARATOR '-') ids, O.price, O.side, ".
                    "           IFNULL(F.filled, '0') filled, ".
                    "           SUM(O.quantity-IFNULL(F.filled, '0')) tot, ".
                    "           COUNT(O.id) grouptx, O.accountid, " .
                    "           IFNULL(GROUP_CONCAT(OW.id SEPARATOR '-'),'') own ".
                    "FROM 		pairs P ".
                    "INNER JOIN currencies C ".
                    "ON			C.id = P.currencyid ".
                    "INNER JOIN currencies CR ".
                    "ON			P.refcurrencyid= CR.id ".
                    "INNER JOIN orders O " .                   
                    "ON         O.pairid = P.id ".
                    "AND        O.canceled IS NULL ".   
                    "LEFT JOIN  (".$fill .") F ".
                    "ON         F.orderid = O.id " . 
                    "LEFT JOIN  (".$own .") OW ".
                    "ON         OW.id = O.id " . 
                    $filtacc .
                    "WHERE 		P.status = 1 AND P.id = " .$pairid. " AND (O.quantity-IFNULL(F.filled,0))>0 ";//Filtro por par, status del par activo y ordenes no completadas

            $orders = new StdClass();
            if (!$rs=$db->query($sql.$filtsideA))
                badEnd("500", array("msg"=>determinateDBError($db)));
        
            $total = 0;
        
            $orders->myown = "";
            //Ordenamos las ventas
            while($row = $rs->fetch_assoc()){                
                $orders->myown .= $row["own"]."-";
                $record = new stdClass();                
                $record->ids = $row["ids"];
                $record->orders = (int)$row["grouptx"];
                
                $temptotal = (float)number_format(($row["tot"]),$row['refcurrencydecimals'],'.','');
                
                $total += $temptotal;
                
                $record->price = new stdClass();
                $record->price->number = (float)number_format($row["price"],$row['currencydecimals'],'.','');
                $record->price->formatted = number_format($record->price->number,$row['currencydecimals'],",",".");
                
                $record->quantity = new stdClass();
                $record->quantity->number = (float)number_format(($row["tot"]),$row['refcurrencydecimals'],'.','');
                $record->quantity->formatted = number_format($record->quantity->number,$row['refcurrencydecimals'],",",".");
                
                $record->total = new stdClass();
                $record->total->number =  (float)number_format($total,$row['refcurrencydecimals'],'.','');
                $record->total->formatted = number_format($record->total->number,$row['refcurrencydecimals'],",",".");
                
                $bids [] = $record;
            }

            $orders->bids = $bids;

            
            if (!$rs=$db->query($sql.$filtsideB))
                badEnd("500", array("msg"=>determinateDBError($db)));
            
            $total = 0;
                //Ordenamos las compras
            while($row = $rs->fetch_assoc()){
                $orders->myown .= $row["own"]."-";
                $record = new stdClass();
                $record->ids = $row["ids"];
                $record->orders = (int)$row["grouptx"];
                    
                $temptotal = (float)number_format(($row["tot"]),$row['refcurrencydecimals'],'.','');
                
                $total += $temptotal;

                $record->price = new stdClass();
                $record->price->number = (float)number_format($row["price"],$row['currencydecimals'],'.','');
                $record->price->formatted = number_format($record->price->number,$row['currencydecimals'],",",".");
                
                $record->quantity = new stdClass();
                $record->quantity->number = (float)number_format(($row["tot"]),$row['refcurrencydecimals'],'.','');
                $record->quantity->formatted = number_format($record->quantity->number,$row['refcurrencydecimals'],",",".");
                
                $record->total = new stdClass();
                $record->total->number =  (float)number_format($total,$row['refcurrencydecimals'],'.','');
                $record->total->formatted = number_format($record->total->number,$row['refcurrencydecimals'],",",".");
                
            
                $asks [] = $record;
            }

            $orders->asks = $asks;

            $orders->myown = trim($orders->myown, '-');
            return $orders;
       
        /*
        
                    "LEFT JOIN  (".$trans .") T ".
                    "ON         T.pairid = P.id " 
        $orders = new stdClass();
        
        //Obtenemos los datos del par para obtener los decimales base
        $sql = "SELECT pairs.refcurrencyid AS pairscurrency, ".
        "       currencies.decimals AS currencydecimals ".
        "       FROM pairs, currencies ".
        "       WHERE pairs.id AND".
        "       pairs.status = 1 AND ".
        "       pairs.id = ".$pairid." AND ".
        "       currencies.id = pairs.currencyid ".
        "       GROUP BY pairs.id".
        "       ORDER BY pairs.id ASC";
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
            
        $row = $rs->fetch_assoc();
        $currencydecimals = $row['currencydecimals'];
        
        //Obtenemos los datos del par para obtener los decimales ref
        $sql = "SELECT pairs.refcurrencyid AS pairscurrency, ".
        "       currencies.decimals AS currencydecimals ".
        "       FROM pairs, currencies ".
        "       WHERE pairs.id AND".
        "       pairs.status = 1 AND ".
        "       pairs.id = ".$pairid." AND ".
        "       currencies.id = pairs.refcurrencyid ".
        "       GROUP BY pairs.id".
        "       ORDER BY pairs.id ASC";
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
            
        $row = $rs->fetch_assoc();
        $refcurrencydecimals = $row['currencydecimals'];
            
        if(($ownorders == 1 && $ownfilterorders != '') || $ownorders == 0){
            //Obtenemos la cantidad de ventas que hay
            $sql = "SELECT SUM(o.quantity) AS qty, COUNT(o.id) AS grouptx, ".
            "       GROUP_CONCAT(o.id SEPARATOR '-') AS ids, ".
            "       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 totallyfilled, ".
            "       o.price, o.side, o.id, o.total".
            "       FROM orders o".
            "       WHERE o.side = 1 AND o.canceled IS NULL AND o.pairid = ".$pairid.
            "       ".$ownfilterorders.
            "       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 ".
            "       GROUP BY o.price".
            "       ORDER BY o.price ASC, o.date DESC".
            "       LIMIT ".$ordernumofrec;
            if (!$rs=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));
        
            $total = 0;
            //Ordenamos las ventas
            while($row = $rs->fetch_assoc()){
                $record = new stdClass();
                $record->ids = $row["ids"];
                $record->orders = (int)$row["grouptx"];
                
                $temptotal = (float)($row["qty"] - $row['totallyfilled']);
                
                $total += $temptotal;
                
                $record->price = new stdClass();
                $record->price->number = (float)number_format($row["price"],$currencydecimals,'.','');
                $record->price->formatted = number_format($record->price->number,$currencydecimals,",",".");
                
                $record->quantity = new stdClass();
                $record->quantity->number = (float)number_format(($row["qty"] - $row['totallyfilled']),$refcurrencydecimals,'.','');
                $record->quantity->formatted = number_format($record->quantity->number,$refcurrencydecimals,",",".");
                
                $record->total = new stdClass();
                $record->total->number =  (float)number_format($total,$refcurrencydecimals,'.','');
                $record->total->formatted = number_format($record->total->number,$refcurrencydecimals,",",".");
                
                $bids [] = $record;
            }
            
        }
        */
            
        $orders->bids = $bids;
            
        if(($ownorders == 1 && $ownfilterorders != '') || $ownorders == 0){
            //Obtenemos la cantidad de compras que hay
            $sql = "SELECT SUM(o.quantity) AS qty, COUNT(o.id) AS grouptx, ".
            "       GROUP_CONCAT(o.id SEPARATOR '-') AS ids, ".
            "       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 totallyfilled, ".
            "       o.price, o.side, o.id, o.total".
            "       FROM orders o".
            "       WHERE o.side = 2 AND o.canceled IS NULL AND o.pairid = ".$pairid.
            "       ".$ownfilterorders.
            "       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 ".
            "       GROUP BY o.price".
            "       ORDER BY o.price DESC, o.date DESC".
            "       LIMIT ".$ordernumofrec;
            if (!$rs=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));
                
            $total = 0;
            //Ordenamos las compras
            while($row = $rs->fetch_assoc()){
                $record = new stdClass();
                $record->ids = $row["ids"];
                $record->orders = (int)$row["grouptx"];
                
                $temptotal = (float)($row["qty"] - $row['totallyfilled']);
                
                $total += $temptotal;
            
                $record->price = new stdClass();
                $record->price->number = (float)number_format($row["price"], $currencydecimals, '.', '');
                $record->price->formatted = number_format($record->price->number,$currencydecimals,",",".");
            
                $record->quantity = new stdClass();
                $record->quantity->number = (float)number_format(($row["qty"] - $row['totallyfilled']), $refcurrencydecimals, '.', '');
                $record->quantity->formatted = number_format($record->quantity->number,$refcurrencydecimals,",",".");
            
                $record->total = new stdClass();
                $record->total->number = (float)number_format($total, $refcurrencydecimals, '.', '');
                $record->total->formatted = number_format($record->total->number,$refcurrencydecimals,",",".");
            
                $asks [] = $record;
            }
        }
    
        $orders->asks = $asks;
        
        //ID ordenes del usuario
        $ownorders = '';
        //Consultamos los datos del para para obtener las monedas
        $sql = "SELECT * FROM pairs WHERE id = ".$pairid;
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
        
        $row = $rs->fetch_assoc();
        
        //Consultamos las cuentas del usuario por su moneda
        $sql = "SELECT accounts.id AS accountid ".
        "       FROM accounts, users".
        "       WHERE users.id = ".$userid.
        "       AND accounts.userid = users.id".
        "       AND accounts.paymentmethodid = -1".
        "       AND (accounts.currencyid = ".$row['currencyid'].
        "       OR  accounts.currencyid = ".$row['refcurrencyid'].")";
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
            
        $ownaccounts = [];
        while($row = $rs->fetch_assoc()){
            $ownaccounts [] = $row['accountid'];
        }
        
        $orders->myown = '';
        
        if(sizeof($ownaccounts) > 0){
            $qtyaccounts = sizeof($ownaccounts) - 1;
            foreach($ownaccounts as $index => $accountid){
                if($index == 0){
                    $ownorders = " (";
                }
            
                if($index != 0){
                    $ownorders .= " OR ";
                }
            
                $ownorders .= " accountid = ".$accountid. ' OR paymentid = '.$accountid;
                
                if($index == $qtyaccounts){
                    $ownorders .= ')';
                }
            }
        
            $sql = "SELECT id FROM orders ".
            "       WHERE ".$ownorders;
            if (!$rs=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));
            
            while($row = $rs->fetch_assoc()){
                $orders->myown .= "".$row['id']."-";
            }
        
            $orders->myown = $orders->myown == '' ? '' : substr($orders->myown, 0, -1);
        }
        
        return $orders;
    }
?>