<?php 
    //Funcion para obtener los datos de los pares
    
    function getPairs($db){

        $lstp =     "SELECT		O.pairid, O.price ".
                    "FROM		orders O  ".
                    "INNER JOIN	transactions T ".
                    "ON			O.id=T.orderid ".
                    "WHERE		T.id IN ".
                    "            ( ".
                    "            SELECT		MAX(T.id) tid ".
                    "            FROM		transactions T  ".
                    "            INNER JOIN	orders O  ".
                    "            ON			T.orderid=O.id ".
                    "            WHERE		T.id IN ".
                    "            			( ".
                    "            			SELECT 		MIN(T.id) tid ".
                    "            			FROM 		transactions T  ".
                    "            			INNER JOIN	orders O ".
                    "            			ON			T.orderid=O.id ".
                    "                       GROUP BY    O.id ".
                    "            			) ".
                    "            GROUP BY	O.pairid ".
                    "            )";
            
        $lst24  =   "SELECT		O.pairid, O.price ".
                    "FROM		orders O  ".
                    "INNER JOIN	transactions T ".
                    "ON			O.id=T.orderid ".
                    "WHERE		T.id IN ".
                    "            ( ".
                    "            SELECT		MAX(T.id) tid ".
                    "            FROM		transactions T  ".
                    "            INNER JOIN	orders O  ".
                    "            ON			T.orderid=O.id ".
                    "            WHERE		T.id IN ".
                    "            			( ".
                    "            			SELECT 		MIN(T.id) tid ".
                    "            			FROM 		transactions T  ".
                    "            			INNER JOIN	orders O ".
                    "            			ON			T.orderid=O.id ".
                    "                       WHERE		T.datecreated <= DATE_SUB(NOW(), INTERVAL 1 DAY) " .
                    "                       GROUP BY    O.id ".
                    "            			) ".
                    "            GROUP BY	O.pairid ".
                    "            )";
            
                    
        $volume =   "SELECT     O.pairid, A.currencyid acccurid, ".
                    "           ABS(SUM(T.amount)) AS transactionamount ".
                    "FROM       transactions T ".
                    "INNER JOIN orders O ".
                    "ON         T.orderid=O.id ".                    
                    "AND        T.accountid = O.accountid " .
                    "INNER JOIN accounts A ".
                    "ON         T.accountid=A.id ".
                    "WHERE      T.datecreated >= DATE_SUB(NOW(), INTERVAL 1 DAY) ".
                    "GROUP BY   O.pairid, A.currencyid";

        $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, ".
                    "           IFNULL(V.transactionamount, 0) transactionamount, ".
                    "           IFNULL(L.price, 0) lastprice, ".
                    "           IFNULL(D.price, 0) lastprice24 ".
                    "FROM 		pairs P ".
                    "INNER JOIN currencies C ".
                    "ON			C.id = P.currencyid ".
                    "INNER JOIN currencies CR ".
                    "ON			P.refcurrencyid= CR.id ".
                    "LEFT JOIN  (".$volume.") V ".
                    "ON         V.pairid=P.id ".
                    "AND        V.acccurid=P.refcurrencyid ".
                    "LEFT JOIN  (".$lstp.") L ".
                    "ON         L.pairid=P.id " .
                    "LEFT JOIN  (".$lst24.") D ".
                    "ON         D.pairid=P.id " .
                    "WHERE 		P.status = 1 " .
                    "ORDER BY   P.id";

        //Consultamos los datos requeridos
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
    
        while($row = $rs->fetch_assoc()){
            $record = new stdClass();
            $record->id = (int) $row["pairsid"];
            $record->last = new stdClass();
            $record->last->number = (float)number_format($row["lastprice"], $row["currencydecimals"], '.','');
            $record->last->formatted = number_format($record->last->number,$row["currencydecimals"],",",".");

            //Volumen
            $record->volume = new stdClass();
            $record->volume->number = (float)number_format($row["transactionamount"], $row["refcurrencydecimals"], '.','');
            $record->volume->formatted = number_format($record->volume->number,$row["refcurrencydecimals"],",",".");
            
            //Porcentaje de cambio del precio en relación al de 24hs
            $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['lastprice24'] > 0){
                    $record->change->number = (($record->last->number - $row['lastprice24'])*100)/$row['lastprice24'] ;
                    $record->change->formatted = number_format($record->change->number,2,",",".")."%";
                    $record->change->par = $row['lastprice24'];
                }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)$row["currencyid"];
            $record->currency->dsc = $row["currencyname"];
            $record->currency->symbol = $row["currencysymbol"];
            $record->currency->icon = $row["currencyicon"];
            $record->currency->decimals = (int)$row["currencydecimals"];
            
            //Datos de refcurrency
            $record->refcurrency = new stdClass();
            $record->refcurrency->id = (int)$row["pairsrefcurrency"];
            $record->refcurrency->dsc = $row["refcurrencyname"];
            $record->refcurrency->symbol = $row["refcurrencysymbol"];
            $record->refcurrency->icon = $row["refcurrencyicon"];
            $record->refcurrency->decimals = (int)$row["refcurrencydecimals"];
            
            $records [] = $record;
        }
        
        return $records;
    }
?>