<?php 
    //Funcion para obtener los datos de los pares
    
    function getPairs($db){
        //Consultamos los datos requeridos
        $sql = "SELECT pairs.refcurrencyid AS pairsrefcurrency, ".
        "       currencies.name AS currencyname, currencies.symbol AS currencysymbol, ".
        "       currencies.icon AS currencyicon, ".
        "       currencies.id AS currencyid, pairs.id AS pairsid,".
        "       currencies.decimals AS currencydecimals ".
        "       FROM pairs, currencies ".
        "       WHERE pairs.status = 1 AND ".
        "       currencies.id = pairs.currencyid ".
        "       GROUP BY pairs.id".
        "       ORDER BY pairs.id ASC";
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>$db->error.$sql));
    
        while($pairdata = $rs->fetch_assoc()){
            $record = new stdClass();
            $record->id = (int) $pairdata["pairsid"];
            
            //Obtenemos la ultima transaccion realizada
            $sql = "SELECT (SELECT ord.price FROM orders ord ".
            "       WHERE ord.type = 2 AND (ord.id = t.orderid OR ord.id = txother.orderid)) AS lastprice ".
            "       FROM orders, accounts, transactions t  ".
            "           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 orders.pairid = ".$record->id.
            "       AND t.id = t.txid ".
            "       AND t.dsc NOT LIKE 'Comisi%' ".
            "       AND accounts.id = orders.accountid ".
            "       GROUP BY t.txid ".
            "       ORDER BY t.datecreated DESC, t.id DESC".
            "       LIMIT 1";
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>$db->error.$sql));
                
            $row = $res->fetch_assoc();
            
            $record->last = new stdClass();
            $record->last->number = (float)$row["lastprice"];
            $record->last->formatted = number_format($record->last->number,$pairdata["currencydecimals"],",",".");
            
            //Consultamos decimales de moneda ref
            $sql = "SELECT * FROM currencies WHERE id = ".$pairdata['pairsrefcurrency'];
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>$db->error.$sql));
    
            $pairrefdata = $res->fetch_assoc();
        
            //Obtenemos la cantidad total que se ha hecho
            $sql = "SELECT SUM(transactions.amount) AS transactionamount FROM orders, transactions, accounts".
            "       WHERE orders.pairid = ".$record->id.
            "       AND accounts.id = IF(orders.side = 2, orders.paymentid, orders.accountid) ".
            "       AND accounts.currencyid = ".$pairdata["pairsrefcurrency"].
            "       AND accounts.id = transactions.accountid ".
            "       AND transactions.amount > 0".
            "       AND transactions.orderid = orders.id".
            "       AND transactions.datecreated >= DATE_SUB(NOW(), INTERVAL 1 DAY)";
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>$db->error.$sql));
                
            $row = $res->fetch_assoc();
            
            $record->volume = new stdClass();
            $record->volume->number = (float)$row["transactionamount"];
            $record->volume->formatted = number_format($record->volume->number,$pairrefdata["decimals"],",",".");
            
            //Consultamos la ultima transaccion hace 24horas
            $sql = "SELECT (SELECT ord.price FROM orders ord ".
            "       WHERE ord.type = 2 AND (ord.id = t.orderid OR ord.id = txother.orderid)) AS transactionamount ".
            "       FROM orders, accounts, transactions t  ".
            "           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 orders.pairid = ".$record->id.
            "       AND t.id = t.txid ".
            "       AND t.dsc NOT LIKE 'Comisi%' ".
            "       AND accounts.id = orders.accountid ".
            //"       AND DATE(t.datecreated) = DATE(NOW() - INTERVAL 1 DAY) ".
            "       AND t.datecreated <= DATE_SUB(NOW(), INTERVAL 1 DAY) ".
            "       GROUP BY t.txid ".
            "       ORDER BY t.datecreated DESC, t.id DESC".
            "       LIMIT 1";
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>$db->error.$sql));
                
            $row = $res->fetch_assoc();
        
            $record->change = new stdClass();
            
            if($record->last->number == 0){
                $record->change->number = 0;
                $record->change->formatted = number_format($record->change->number,2,",",".")."%";
            }else{
                if($row['transactionamount'] > 0){
                    $record->change->number = (($record->last->number - $row['transactionamount'])*100)/$row['transactionamount'] ;
                    $record->change->formatted = number_format($record->change->number,2,",",".")."%";
                    $record->change->par = $row['transactionamount'];
                }else{
                    $record->change->number = 0;
                    $record->change->formatted = number_format($record->change->number,2,",",".")."%";
                    $record->change->par = $row['transactionamount'];
                }
            }
        
            //Datos de moneda currency
            $record->currency = new stdClass();
            $record->currency->id = (int)$pairdata["currencyid"];
            $record->currency->dsc = $pairdata["currencyname"];
            $record->currency->symbol = $pairdata["currencysymbol"];
            $record->currency->icon = $pairdata["currencyicon"];
            $record->currency->decimals = (int)$pairdata["currencydecimals"];
            
            //Datos de refcurrency
            $record->refcurrency = new stdClass();
            
            $sql = "SELECT * FROM currencies WHERE id = ".$pairdata["pairsrefcurrency"];
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>$db->error.$sql));
        
            $row = $res->fetch_assoc();
            
            $record->refcurrency->id = (int)$row["id"];
            $record->refcurrency->dsc = $row["name"];
            $record->refcurrency->symbol = $row["symbol"];
            $record->refcurrency->icon = $row["icon"];
            $record->refcurrency->decimals = (int)$row["decimals"];
            
            $records [] = $record;
        }
        
        return $records;
    }
?>