<?php 
    function getPairDetails($db, $pairid, $userid){
        //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 decimals ".
        "       FROM pairs, currencies ".
        "       WHERE pairs.status = 1 AND ".
        "       pairs.id = ".$pairid." AND ".
        "       currencies.id = pairs.currencyid ".
        "       GROUP BY pairs.id";
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
    
        $pairdata = $rs->fetch_assoc();
        
        //Consultamos decimales de moneda ref
        $sql = "SELECT * FROM currencies WHERE id = ".$pairdata['pairsrefcurrency'];
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
    
        $pairrefdata = $rs->fetch_assoc();
    
        //Ordenamos los valores
        $pair = new stdClass();
        $pair->id = (int)$pairid;
        
        //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 = ".$pair->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"=>determinateDBError($db)));
                
        $row = $res->fetch_assoc();
            
        $pair->last = new stdClass();
        $pair->last->number = (float)number_format($row["lastprice"],$pairdata["decimals"],".","");
        $pair->last->formatted = number_format($pair->last->number,$pairdata["decimals"],",",".");
    
        //Obtenemos la cantidad total que se ha hecho
        $sql = "SELECT SUM(transactions.amount) AS transactionamount FROM orders, transactions, accounts".
        "       WHERE orders.pairid = ".$pair->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"=>determinateDBError($db)));
            
        $row = $res->fetch_assoc();
    
        $pair->volume = new stdClass();
        $pair->volume->number = (float)number_format($row['transactionamount'],$pairrefdata["decimals"],'.','');
        $pair->volume->formatted = number_format($pair->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 = ".$pair->id.
        "       AND t.id = t.txid ".
        "       AND t.dsc NOT LIKE 'Comisi%' ".
        "       AND accounts.id = orders.accountid ".
        "       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"=>determinateDBError($db)));
            
        $row = $res->fetch_assoc();
        $pair->change = new stdClass();
        $pair->nmalchange = new stdClass();
        
        if($pair->last->number == 0){
            $pair->change->number = 0;
            $pair->change->formatted = number_format($pair->change->number,2,",",".")."%";
            $pair->nmalchange->number =  0;
            $pair->nmalchange->formatted = number_format($pair->nmalchange->number, $pairdata["decimals"],',','.');;
        }else{
            if($row['transactionamount'] > 0){
                $pair->change->number = (($pair->last->number - $row['transactionamount'])*100)/$row['transactionamount'] ;
                $pair->change->formatted = number_format($pair->change->number,2,",",".")."%";
                $pair->nmalchange->number =  abs($row['transactionamount'] - $pair->last->number);
                $pair->nmalchange->formatted = number_format($pair->nmalchange->number, $pairdata["decimals"],',','.');
            }else{
                $pair->change->number = 0;
                $pair->change->formatted = number_format($pair->change->number,2,",",".")."%";
                $pair->nmalchange->number =  0;
                $pair->nmalchange->formatted = number_format($pair->nmalchange->number, $pairdata["decimals"],',','.');
            }
        }
        
        //Consultamos la ultima venta
        $sql = "SELECT o.quantity AS qty, o.id AS grouptx, ".
        "       o.price, o.side, o.id, o.total".
        "       FROM orders o".
        "       WHERE o.side = 1 AND o.canceled IS NULL AND o.pairid = ".$pair->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 ".
        "       ORDER BY o.price ASC".
        "       LIMIT 1";
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
        
        $row = $res->fetch_assoc();
    
        $pair->ask = new stdClass();
        $pair->ask->number = (float)number_format($row['price'], $pairdata["decimals"], '.','');
        $pair->ask->formatted = number_format($pair->ask->number,$pairdata["decimals"],",",".");
    
        //Consultamos los datos de la ultima compra
        $sql = "SELECT o.quantity AS qty, o.id AS grouptx, ".
        "       o.price, o.side, o.id, o.total".
        "       FROM orders o".
        "       WHERE o.side = 2 AND o.canceled IS NULL AND o.pairid = ".$pair->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 ".
        "       ORDER BY o.price DESC".
        "       LIMIT 1";
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
        
        $row = $res->fetch_assoc();
    
        $pair->bid = new stdClass();
        $pair->bid->number = (float)number_format($row['price'],$pairdata["decimals"],'.','');
        $pair->bid->formatted = number_format($pair->bid->number,$pairdata["decimals"],",",".");
        
        //Consultamos la orden mas barata durante todo 1 dia
        $sql = "SELECT MIN((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 = ".$pair->id."
               AND t.id = t.txid 
               AND t.dsc NOT LIKE 'Comisi%' 
               AND accounts.id = orders.accountid 
               AND t.datecreated >= DATE_SUB(NOW(), INTERVAL 1 DAY)
               ORDER BY t.datecreated DESC, t.id DESC";
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
            
        $row = $res->fetch_assoc();
        
        $pair->low = new stdClass();
        $pair->low->number = (float)number_format($row['lastprice'],$pairdata["decimals"],'.','');
        $pair->low->formatted = number_format($pair->low->number,$pairdata["decimals"],",",".");
        
        //Consultamos la orden mas barata durante todo 1 dia
        $sql = "SELECT MAX((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 = ".$pair->id."
               AND t.id = t.txid 
               AND t.dsc NOT LIKE 'Comisi%' 
               AND accounts.id = orders.accountid 
               AND t.datecreated >= DATE_SUB(NOW(), INTERVAL 1 DAY)
               ORDER BY t.datecreated DESC, t.id DESC";
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
            
        $row = $res->fetch_assoc();
        
        $pair->high = new stdClass();
        $pair->high->number = (float)number_format($row['lastprice'], $pairdata["decimals"],'.','');
        $pair->high->formatted = number_format($pair->high->number,$pairdata["decimals"],",",".");
        
        //Datos de moneda refcurrency
        $pair->currency = new stdClass();
        $pair->currency->id = (int)$pairdata["currencyid"];
        $pair->currency->dsc = $pairdata["currencyname"];
        $pair->currency->icon = $pairdata["currencyicon"];
        $pair->currency->symbol = $pairdata["currencysymbol"];
        $pair->currency->decimals = $pairdata['decimals'];
            
        //Datos de currency
        $pair->refcurrency = new stdClass();
            
        $sql = "SELECT * FROM currencies WHERE id = ".$pairdata["pairsrefcurrency"];
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
        
        $row = $res->fetch_assoc();
            
        $pair->refcurrency->id = (int)$row["id"];
        $pair->refcurrency->dsc = $row["name"];
        $pair->refcurrency->symbol = $row["symbol"];
        $pair->refcurrency->icon = $row["icon"];
        $pair->refcurrency->decimals = $row['decimals'];
        
        //Consultamos los datos del usuario para encontrar su fee
        $sql = "SELECT id, usr, type, level ".
        "       FROM users WHERE id = ".$userid;
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
            
        $userdata = $res->fetch_assoc();
    
        $level = 1;
    
        if($userdata["level"] == 0){
            $level = 1;
        }else{
            $level = $userdata["level"];
        }
    
        // Consultamos las comisiones del par
        $sql = "SELECT * FROM cmspreffees ".
        "       WHERE levelid = ".$level.
        "       AND pairid = ".$pairid.
        "       AND usertype = ".$userdata["type"];
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
            
        $feedata = $res->fetch_assoc();
        $pair->fees = new stdClass();
        $pair->fees->orders = new stdClass();
    
        //Comisiones para market
        $pair->fees->orders->market = new stdClass();
    
        if($feedata["pctmktfee"] == 0){
            $pair->fees->orders->market->pctfee = null;
        }else{
            
            // Obtenemos el exonerado para depositos si podee
            $sql = "SELECT IF(exonerated IS NULL, 0, exonerated) AS exonerated FROM userxchgprefs ".
            "       WHERE pairid = ".$pairid.
            "       AND userid = ".$userid.
            "       AND preffeesfield = 'pctmktfee'";
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));
            
            $userExg = $res->fetch_assoc();
            
            $pair->fees->orders->market->pctfee = new stdClass();
            $pair->fees->orders->market->pctfee ->number = $feedata["pctmktfee"] - ($feedata["pctmktfee"]/100 * $userExg['exonerated']);
            $pair->fees->orders->market->pctfee->formatted = number_format($pair->fees->orders->market->pctfee ->number,2,",",".");
        }
        
        if($feedata["nmlmktfee"] == 0){
            $pair->fees->orders->market->nmalfee = null;
        }else{
            
            // Obtenemos el exonerado para depositos si podee
            $sql = "SELECT IF(exonerated IS NULL, 0, exonerated) AS exonerated FROM userxchgprefs ".
            "       WHERE pairid = ".$pairid.
            "       AND userid = ".$userid.
            "       AND preffeesfield = 'nmlmktfee'";
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));
            
            $userExg = $res->fetch_assoc();
            
            $pair->fees->orders->market->nmalfee = new stdClass();
            $pair->fees->orders->market->nmalfee ->number = $feedata["nmlmktfee"] - ($feedata["nmlmktfee"]/100 * $userExg['exonerated']);
            $pair->refcurrency->nmalfee = number_format($pair->fees->orders->market->nmalfee ->number,$pair->refcurrency->decimals,",",".");
        }
        
        //Comisiones para limit
        $pair->fees->orders->limit = new stdClass();
        
        if($feedata["pctlimitfee"] == 0){
            $pair->fees->orders->limit->pctfee = null;
        }else{
            
            // Obtenemos el exonerado para depositos si podee
            $sql = "SELECT IF(exonerated IS NULL, 0, exonerated) AS exonerated FROM userxchgprefs ".
            "       WHERE pairid = ".$pairid.
            "       AND userid = ".$userid.
            "       AND preffeesfield = 'pctlimitfee'";
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));
            
            $userExg = $res->fetch_assoc();
            
            $pair->fees->orders->limit->pctfee = new stdClass();
            $pair->fees->orders->limit->pctfee ->number = $feedata["pctlimitfee"] - ($feedata["pctlimitfee"]/100 * $userExg['exonerated']);
            $pair->fees->orders->limit->pctfee->formatted = number_format($pair->fees->orders->limit->pctfee ->number,2,",",".");
        }
        
        if($feedata["nmllimitfee"] == 0){
            $pair->fees->orders->limit->nmalfee = null;
        }else{
            
            // Obtenemos el exonerado para depositos si podee
            $sql = "SELECT IF(exonerated IS NULL, 0, exonerated) AS exonerated FROM userxchgprefs ".
            "       WHERE pairid = ".$pairid.
            "       AND userid = ".$userid.
            "       AND preffeesfield = 'nmllimitfee'";
            if (!$res=$db->query($sql))
                badEnd("500", array("msg"=>determinateDBError($db)));
            
            $userExg = $res->fetch_assoc();
            
            $pair->fees->orders->limit->nmalfee = new stdClass();
            $pair->fees->orders->limit->nmalfee->number = $feedata["nmllimitfee"] - ($feedata["nmllimitfee"]/100 * $userExg['exonerated']);
            $pair->currency->nmalfee = number_format($pair->fees->orders->limit->nmalfee->number,$pair->currency->decimals,",",".");
        };
        
        // Consultamos el minimo en la moneda base
        $sql = "SELECT minexchange ".
        "       FROM cmspreffees ".
        "       WHERE currencyid = ".$pair->refcurrency->id.
        "       AND usertype = ".$userdata["type"].
        "       LIMIT 1";
        if (!$res=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
    
        $min = $res->fetch_assoc();
    
        // Retornamos el valor
        $pair->fees->min = new stdClass();
        $pair->fees->min->number = (float)$min['minexchange'];
        $pair->fees->min->formatted = numberFormatt($pair->fees->min->number, $pair->refcurrency->decimals);
        
        return $pair;
    }
?>