<?php 
    function getChart($db, $pairid, $timeframe){
        $chart = new stdClass();
        
        //Consultamos los datos requeridos
        $sql = "SELECT pairs.refcurrencyid AS pairsrefcurrency, ".
        "       currencies.name AS currencyname, currencies.symbol AS currencysymbol, ".
        "       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();
        
        // Obtenemos el nombre del par
        $sql = "SELECT CONCAT(currencies.symbol,'/',cursec.symbol) AS pairdsc, ".
        "       currencies.decimals AS currendecimals, cursec.decimals AS cursecdecimals".
        "       FROM currencies, pairs par ".
        "       LEFT JOIN currencies cursec ON cursec.id = par.currencyid ".
        "       WHERE par.refcurrencyid = currencies.id ".
        "       AND par.id = ".$pairid;
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
            
        $row = $rs->fetch_assoc();
        
        $currencyrefdecimals = (int)$row['currendecimals'];
        $currencydecimals = (int)$row['cursecdecimals'];
        
        $chart->pair = $row['pairdsc'];
        
        //Obtenemos el tipo de intervarlo y su equivalente en horas
        $chart->timeframe = determinateTimeframe($timeframe);
        $hour = convertTimeFrame($chart->timeframe);
        
        //Convertimos el timeframe a segundos
        $secondsHour = $hour * 3600;
        
        //Lines es el precio del ultimo CADA TIMEFRAME
        $chart->lines = new stdClass();
        /*
        $sql = "SELECT orders.id AS orderid, 
                orders.price AS orderprice, 
                t.txid AS txid,
                FROM_UNIXTIME(CEILING(UNIX_TIMESTAMP(t.datecreated)/".$secondsHour.")*".$secondsHour.") AS label,
                CONCAT(HOUR(FROM_UNIXTIME(CEILING(UNIX_TIMESTAMP(t.datecreated)/".$secondsHour.")*".$secondsHour.")),':00') AS reallabel,
                t.datecreated AS datecreated
                FROM orders, transactions t  
                    LEFT JOIN transactions txother 
                    ON t.orderid != txother.orderid 
                    AND t.txid = txother.txid
                WHERE orders.id = t.orderid
                AND orders.pairid = ".$pairid."
                AND orders.type = 2
                AND t.orderid IS NOT NULL
                AND t.txid = (SELECT MAX(txmax.txid)
                   	FROM transactions txmax, orders
                    WHERE 
                    FROM_UNIXTIME(
                        CEILING(UNIX_TIMESTAMP(txmax.datecreated)/".$secondsHour.")*".$secondsHour."
                    ) = FROM_UNIXTIME(
                        CEILING(UNIX_TIMESTAMP(t.datecreated)/".$secondsHour.")*".$secondsHour."
                    ) 
                AND txmax.orderid = orders.id AND orders.pairid = ".$pairid.")
                GROUP BY label
            ORDER BY t.datecreated  DESC 
            LIMIT 60";*/
        $sql = "SELECT orders.id AS orderid, 
                orders.price AS orderprice, 
                t.txid AS txid,
                FROM_UNIXTIME(CEILING(UNIX_TIMESTAMP(t.datecreated)/".$secondsHour.")*".$secondsHour.") AS label,
                CONCAT(HOUR(FROM_UNIXTIME(CEILING(UNIX_TIMESTAMP(t.datecreated)/".$secondsHour.")*".$secondsHour.")),':00') AS reallabel,
                t.datecreated AS datecreated
                FROM orders, transactions t  
                WHERE orders.id = t.orderid
                AND orders.pairid = ".$pairid."
                AND orders.type = 2
                AND t.orderid IS NOT NULL
                AND t.txid = (SELECT MAX(txmax.txid)
                   	FROM transactions txmax, orders
                    WHERE 
                    FROM_UNIXTIME(
                        CEILING(UNIX_TIMESTAMP(txmax.datecreated)/".$secondsHour.")*".$secondsHour."
                    ) = FROM_UNIXTIME(
                        CEILING(UNIX_TIMESTAMP(t.datecreated)/".$secondsHour.")*".$secondsHour."
                    ) 
                AND txmax.orderid = orders.id AND orders.pairid = ".$pairid.")
                GROUP BY label
            ORDER BY t.datecreated  DESC 
            LIMIT 60";
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
        
        while($row = $rs->fetch_assoc()){
            $record = new stdClass();
            
            $record->label = $row['reallabel'];
            $record->labeldetail = date("Y-m-d H:i:s", strtotime($row["label"]));
            
            $record->price = new stdClass();
            $record->price->number = (float)number_format($row['orderprice'],$currencydecimals, '.', '');
            $record->price->formatted = numberFormatt($row['orderprice'], $currencydecimals);
            
            $lines [] = $record;
        }
        
        $count = 0;
        $highestPrice = 0;
        
        // Obtenemos la hora y fecha de caracas
        $date = new DateTime('now', new DateTimeZone('America/Caracas'));
        $currentDate = $date->format('Y-m-d H:00:00');
        
        if($lines != NULL){
            foreach($lines as $i => $line){
                
                if($i == 0){
                    
                    $record = new stdClass();
                    
                    //Si la fecha actual es mayor a la del ultimo intercambio, colocamos ese encima
                    if(strtotime($currentDate) > strtotime($line->labeldetail)){
                        $record->label = assembleSimpleLabel($chart->timeframe, $currentDate);
                        $record->labeldetail = assembleStrongLabel($chart->timeframe, $currentDate);
                
                        $record->canonnical = $currentDate;
                        
                        $record->price = new stdClass();
                        $record->price->number = $line->price->number;
                        $record->price->formatted = $line->price->formatted;
                    
                        $linestest [] = $record;
                        $count++;
                        
                        //Validamos que el numero actual sea mayor
                        if($line->price->number > $highestPrice){
                            $highestPrice = $line->price->number;
                        }
                        
                        $lastHour = $currentDate;
                        
                        //Aqui recorremos este primer valor ya que no fue guardado
                        do{
                            $record = new stdClass();
                        
                            //Restamos la hora segun el timeframe
                            $lastHour = date('Y-m-d H:i:s', strtotime($lastHour) - $secondsHour);
                        
                            $record->label = assembleSimpleLabel($chart->timeframe, $lastHour);
                            $record->labeldetail = assembleStrongLabel($chart->timeframe, $lastHour);
                            
                            $record->canonnical = $lastHour;
                            
                            $record->price = new stdClass();
                            $record->price->number = $line->price->number;
                            $record->price->formatted = $line->price->formatted;
                            
                            //Validamos que el numero actual sea mayor
                            if($line->price->number > $highestPrice){
                                $highestPrice = $line->price->number;
                            }
                        
                            $linestest [] = $record;
                            $count++;
                
                        }while(strtotime($lastHour) > strtotime($line->labeldetail) && $count <= 60);
                        
                    }else{
                        $record->label = assembleSimpleLabel($chart->timeframe, $line->labeldetail);
                        $record->labeldetail = assembleStrongLabel($chart->timeframe, $line->labeldetail);
                
                        $record->canonnical = $line->labeldetail;
                        
                        $record->price = new stdClass();
                        $record->price->number = $line->price->number;
                        $record->price->formatted = $line->price->formatted;
                    
                        $linestest [] = $record;
                        $count++;
                        
                        //Validamos que el numero actual sea mayor
                        if($line->price->number > $highestPrice){
                            $highestPrice = $line->price->number;
                        }
                    }
                    
                    
                    
                }else if($i != 0 && $count <= 60){
                    //Guardamos la ultima hora
                    $lastHour = $linestest[$count-1]->canonnical;
                    do{
                        $record = new stdClass();
                        
                        //Restamos la hora segun el timeframe
                        $lastHour = date('Y-m-d H:i:s', strtotime($lastHour) - $secondsHour);
                        
                        $record->label = assembleSimpleLabel($chart->timeframe, $lastHour);
                        $record->labeldetail = assembleStrongLabel($chart->timeframe, $lastHour);
                        
                        $record->canonnical = $lastHour;
                        
                        $record->price = new stdClass();
                        $record->price->number = $line->price->number;
                        $record->price->formatted = $line->price->formatted;
                        
                        //Validamos que el numero actual sea mayor
                        if($line->price->number > $highestPrice){
                            $highestPrice = $line->price->number;
                        }
                        
                        $linestest [] = $record;
                        $count++;
                
                    }while(strtotime($lastHour) > strtotime($line->labeldetail) && $count <= 60);
                }
            }
        }
        
        $chart->lines->max = (float)$highestPrice;
        $chart->lines->records = $linestest;

        //bars es el volumen de intercambio CADA TIMEFRAME
        $chart->bars = new stdClass();
        
        $sql = "SELECT SUM(transactions.amount) AS volume, 
	            FROM_UNIXTIME(CEILING(UNIX_TIMESTAMP(transactions.datecreated)/".$secondsHour.")*".$secondsHour.") AS label,
	            CONCAT(HOUR(FROM_UNIXTIME(CEILING(UNIX_TIMESTAMP(transactions.datecreated)/".$secondsHour.")*".$secondsHour.")),':00') AS reallabel,
                transactions.datecreated 
            FROM orders, transactions, accounts
            WHERE orders.pairid = ".$pairid."
            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
            GROUP BY label
            ORDER BY transactions.datecreated DESC
            LIMIT 60";
        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
        
        // Recogemos los valores de la BD
        while($row = $rs->fetch_assoc()){
            $record = new stdClass();

            $record->label = $row['reallabel'];
            $record->labeldetail = date("Y-m-d H:i:s", strtotime($row["label"]));
            $record->price = new stdClass();
            $record->price->number = $row['volume'];
            $record->price->formatted = numberFormatt($row['volume'], $currencyrefdecimals);
            
            $bars [] = $record;
        }
        
        
        //Armamos los valores de la grafica
        //Deben ser 60 valores
        $count = 0;
        $maxvalues = 60;
        $highestVol = 0;
        if($bars != NULL){
            foreach($bars as $i => $bar){
            
                //SI es cero guardamos nuestro punto de partida
                if($i == 0){
                    $record = new stdClass();
                    
                    //Si la fecha actual es mayor a la del ultimo intercambio, colocamos ese encima
                    if(strtotime($currentDate) > strtotime($bar->labeldetail)){
                        $record->label = assembleSimpleLabel($chart->timeframe, $currentDate);
                        $record->labeldetail = assembleStrongLabel($chart->timeframe, $currentDate);
                
                        $record->canonnical = $currentDate;
                        $record->price = new stdClass();
                        $record->price->number = 0;
                        $record->price->formatted = numberFormatt(0, $currencyrefdecimals);
                        
                        $barstest [] = $record;
                        $count++;
                        
                        //Validamos que el numero actual sea mayor
                        if($record->price->number > $highestVol){
                            $highestVol = $record->price->number;
                        }
                        
                        //Aqui recorremos este primer valor ya que no fue guardado
                        $lastHour = $currentDate;
                        do{
                            $record = new stdClass();
                        
                            //Restamos la hora segun el timeframe
                            $lastHour = date('Y-m-d H:i:s', strtotime($lastHour) - $secondsHour);
                        
                            $record->label = assembleSimpleLabel($chart->timeframe, $lastHour);
                            $record->labeldetail = assembleStrongLabel($chart->timeframe, $lastHour);
                        
                            $record->canonnical = $lastHour;
                        
                            // SI no son iguales no hubo volumen ese dia/hora
                            if(strtotime($lastHour) > strtotime($bar->labeldetail)){
                                $record->price = new stdClass();
                                $record->price->number = 0;
                                $record->price->formatted = numberFormatt(0, $currencyrefdecimals);
                            }else{
                                $record->price = new stdClass();
                                $record->price->number = $bar->price->number;
                                $record->price->formatted = $bar->price->formatted;
                                
                                //Validamos que el numero actual sea mayor
                                if($bar->price->number > $highestVol){
                                    $highestVol = $bar->price->number;
                                }
                            }
                        
                            $barstest [] = $record;
                            $count++;
                        
                        }while(strtotime($lastHour) > strtotime($bar->labeldetail) && $count <= $maxvalues);
                        
                    }else{
                        //Guardamos el valor retornado
                        $record->label = assembleSimpleLabel($chart->timeframe, $bar->labeldetail);
                        $record->labeldetail = assembleStrongLabel($chart->timeframe, $bar->labeldetail);
                
                        $record->canonnical = $bar->labeldetail;
                        $record->price = new stdClass();
                        $record->price->number = $bar->price->number;
                        $record->price->formatted = $bar->price->formatted;
                        
                        $barstest [] = $record;
                        $count++;
                        
                        //Validamos que el numero actual sea mayor
                        if($record->price->number > $highestVol){
                            $highestVol = $record->price->number;
                        }
                    
                    }
                    
            
                }else if($i != 0 && $count <= $maxvalues){
                    //Guardamos la ultima hora
                    $lastHour = $barstest[$count-1]->canonnical;
                    do{
                        $record = new stdClass();
                        
                        //Restamos la hora segun el timeframe
                        $lastHour = date('Y-m-d H:i:s', strtotime($lastHour) - $secondsHour);
                        
                        $record->label = assembleSimpleLabel($chart->timeframe, $lastHour);
                        $record->labeldetail = assembleStrongLabel($chart->timeframe, $lastHour);
                        
                        $record->canonnical = $lastHour;
                        
                        // SI no son iguales no hubo volumen ese dia/hora
                        if(strtotime($lastHour) > strtotime($bar->labeldetail)){
                            $record->price = new stdClass();
                            $record->price->number = 0;
                            $record->price->formatted = numberFormatt(0, $currencyrefdecimals);
                        }else{
                            $record->price = new stdClass();
                            $record->price->number = $bar->price->number;
                            $record->price->formatted = $bar->price->formatted;
                            
                            //Validamos que el numero actual sea mayor
                            if($bar->price->number > $highestVol){
                                $highestVol = $bar->price->number;
                            }
                        }
                        
                        $barstest [] = $record;
                        $count++;
                        
                    }while(strtotime($lastHour) >  strtotime($bar->labeldetail) && $count <= $maxvalues);
                }
            }
        }
        
        $chart->bars->max = (float)$highestVol;
        //$chart->bars->records = $bars;
        $chart->bars->records = $barstest;
        
        return $chart;
    }
    
    /*  Funcion para formateo de label sencilla
    *   Recibe: 
    *   $timeframe = tipo de formateo
    *   $time = Fecha a formatear y evaluar
    */
    function assembleSimpleLabel($timeframe, $time){
        /* timeframe:  
        1 = 1hora
        2 = 4 horas
        3 = 24 horas
        otro = 1 hora
        */
        if($timeframe == 1){
                            
            if(date('H', strtotime($time)) == '00'){
                return date('d', strtotime($time));
            }else{
                return date('H', strtotime($time)).':00';
            }
            
        }else if($timeframe == 2){
                            
            if(date('H', strtotime($time)) == '00'){
                return date('d', strtotime($time));
            }else{
                return date('H', strtotime($time)).':00';
            }
                
        }else if($timeframe == 3){
            
            if(date('d',strtotime($time)) == '01'){
                return determinateMonth(date('n',strtotime($time)))." ".date('d',strtotime($time));
            }else{
                return date('d',strtotime($time));
            }
        }
    }
    
    /*  Funcion para retornar la label con full datos
    *
    */
    function assembleStrongLabel($timeframe, $time){
        /*  1 = 1hora
        2 = 4 horas
        3 = 24 horas
        otro = 1 hora
        */
        if($timeframe == 1){
            
            if(date('H', strtotime($time)) == '00'){
                return date('d',strtotime($time))." ".determinateMonth(date('n',strtotime($time)))." ".date('Y',strtotime($time))." ".date('H:i',strtotime($time));
            }else{
                return date('d',strtotime($time))." ".determinateMonth(date('n',strtotime($time)))." ".date('Y',strtotime($time))." ".date('H:i',strtotime($time));
            }
        
        }else if($timeframe == 2){
        
            if(date('H', strtotime($time)) == '00'){
                return date('d',strtotime($time))." ".determinateMonth(date('n',strtotime($time)))." ".date('Y',strtotime($time))." ".date('H:i',strtotime($time));
            }else{
                return date('d',strtotime($time))." ".determinateMonth(date('n',strtotime($time)))." ".date('Y',strtotime($time))." ".date('H:i',strtotime($time));
            }
                            
        }else if($timeframe == 3){
        
            if(date('d',strtotime($time)) == '01'){
                return date('d',strtotime($time))." ".determinateMonth(date('n',strtotime($time)))." ".date('Y',strtotime($time));
            }else{
                return date('d',strtotime($time))." ".determinateMonth(date('n',strtotime($time)))." ".date('Y',strtotime($time));
            }
        
        }
    }
    
    /* Funcion para determinar el timeframe de la grafica:
        1 = 1
        2 = 2
        3 = 3
        otro = 1 hora
    */
    function  determinateTimeframe($timeframe){
        $time = 0;
        
        switch($timeframe){
            case 1:
                $time = 1;
                break;
            case 2:
                $time = 2;
                break;
            case 3:
                $time = 3;
                break;
            default:
                $time = 1;
                break;
        }
        
        return $time;
    }
    
    /* Funcion para determinar el timeframe de la grafica:
        1 = 1hora
        2 = 4 horas
        3 = 24 horas
        otro = 1 hora
    */
    function convertTimeFrame($timeframe){
        $time = 0;
        
        switch($timeframe){
            case 1:
                $time = 1;
                break;
            case 2:
                $time = 4;
                break;
            case 3:
                $time = 24;
                break;
            default:
                $time = 1;
                break;
        }
        
        return $time;
    }
    
    function determinateMonth($mon){
        $str = '';
        
        switch($mon){
            case 1:
                $str = 'Ene';
                break;
            case 2:
                $str = 'Feb';
                break;
            case 3:
                $str = 'Mar';
                break;
            case 4:
                $str = 'Abr';
                break;
            case 5:
                $str = 'May';
                break;
            case 6:
                $str = 'Jun';
                break;
            case 7:
                $str = 'Jul';
                break;
            case 8:
                $str = 'Ago';
                break;
            case 9:
                $str = 'Sep';
                break;
            case 10:
                $str = 'Oct';
                break;
            case 11:
                $str = 'Nov';
                break;
            case 12:
                $str = 'Dic';
                break;
        }
        
        return $str;
    }
?>