<?php 
    function getChart($db, $pairid, $timeframe){
        $chart = new stdClass();

        $timeobj = new stdClass();
        $timeobj = convertTimeFrame($timeframe);
        $interval = $timeobj->interval;
        $days = $timeobj->days;
        //$lblfullformat = $timeobj->fulllbl;
        //$lblformat = $timeobj->lbl;
        $lasthours = 60;

        $volume =   "   SELECT X.pairid, MAX(X.amount) high, GROUP_CONCAT(IFNULL(X.amount, '--') ORDER BY X.h DESC) qty " ./*
                    "   GROUP_CONCAT(DATE_FORMAT(X.h,$lblfullformat) ORDER BY X.h DESC) fulldate, ". 
                    "   GROUP_CONCAT(DATE_FORMAT(X.h,$lblformat) ORDER BY X.h DESC) lbldate ". */
                    "FROM (".
                    "   SELECT      HREF.pairid, HREF.h, SUM(V.transactionamount) AS amount ".
                    "   FROM        ( ".
                    "                   SELECT P.id pairid, l24.ref h FROM (SELECT * FROM pairs WHERE status=1) P, ".
                    "                   (SELECT DATE_FORMAT((@now := DATE_SUB(@now, INTERVAL ".$interval." )), '%Y-%m-%d %H:00') ref ".
                    "                   FROM cities, (SELECT @now := DATE_ADD(NOW(), INTERVAL ".$interval." )) AS tt limit ".$lasthours.") l24 ".
                    "               ) HREF ".
                    "   LEFT JOIN   (  ".
                    "			    SELECT 		O.pairid, ABS(SUM(T.amount)) transactionamount, DATE_FORMAT(T.datecreated, '%Y-%m-%d %H:00') h ".
                    "			    FROM 		transactions T   ".
                    "			    INNER JOIN	orders O  ".
                    "			    ON			T.orderid=O.id  ".
                    "               AND         T.accountid = O.accountid " .
                    "               INNER JOIN  pairs P " .
                    "               ON          P.id = O.pairid ".
                    "               INNER JOIN  accounts A ".
                    "               ON          T.accountid=A.id ".
                    "               AND         A.currencyid = P.refcurrencyid ".
                    "               WHERE		T.datecreated > DATE_SUB(NOW(), INTERVAL ".$days." DAY) ".
                    "               GROUP BY    O.pairid, DATE_FORMAT(T.datecreated, '%Y%m%d%H')  " .
                    "			    ) V ".
                    "   ON          V.h BETWEEN DATE_SUB(HREF.h, INTERVAL ".$interval." ) AND HREF.h " .
                   /* "   ON          V.h BETWEEN  HREF.h AND DATE_ADD(HREF.h, INTERVAL ".$interval." HOUR) " .*/
                    "   AND         V.pairid = HREF.pairid" .                                   
                    "   GROUP BY	HREF.pairid, DATE_FORMAT(HREF.h, '%Y%m%d%H')" .
                    "   ) X GROUP BY X.pairid";

        $price ="   SELECT     Z.pairid, MAX(Z.price) high, GROUP_CONCAT(IFNULL(Z.price, '--') ORDER BY Z.h DESC) price, ".
                "   GROUP_CONCAT(IFNULL(Z.h, '--') ORDER BY Z.h DESC) `date` ".
                "   FROM (".                   
                "   SELECT      HREF.pairid, IFNULL(O.price, '--') price, MAX(O.tid), HREF.h " .
                "   FROM     ( ".
                "               SELECT P.id pairid, l24.ref h FROM (SELECT * FROM pairs WHERE status=1) P, " .
                "               (SELECT DATE_FORMAT((@now2 := DATE_SUB(@now2, INTERVAL ".$interval." )), '%Y-%m-%d %H:00') ref ".
                "               FROM cities, (SELECT @now2 := DATE_ADD(NOW(), INTERVAL ".$interval." )) AS tt limit ".$lasthours.") l24 ".
                "            ) HREF ".
                "   LEFT JOIN   ( ".
                "   SELECT		O.pairid, DATE_FORMAT(T.datecreated, '%Y-%m-%d %H:00') h, O.price , T.id tid ".
                "   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 ".$days." DAY) ".
                "              GROUP BY    O.id  ".
                "   			)  ".
                "   GROUP BY   	O.pairid, DATE_FORMAT(T.datecreated, '%Y%m%d%H') ".
                "   ) ".
                "   ) O ".
                "   ON			HREF.pairid = O.pairid ".
                "   AND         O.h BETWEEN DATE_SUB(HREF.h, INTERVAL ".$interval." ) AND HREF.h " .
                /*"   AND         O.h BETWEEN  HREF.h  AND DATE_ADD(HREF.h, INTERVAL ".$interval." HOUR) " .*/
                "   GROUP BY	HREF.pairid, DATE_FORMAT(HREF.h, '%Y%m%d%H') " .                 
                "   ) Z GROUP BY Z.pairid ORDER BY Z.h";

    $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 ".
                "            )";

        $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(G.price, 0) price, ".
                    "           IFNULL(G.high, 0) maxprice, ".
                    "           IFNULL(G.date, 0) `date`, ".      /*              
                    "           IFNULL(V.fulldate,0) fulldatelbl, ".
                    "           IFNULL(V.lbldate,0) datelbl, ".*/
                    "           IFNULL(V.high, 0) maxvol, ".
                    "           IFNULL(V.qty, 0) volume, ".                    
                    "           IFNULL(L.price, 0) lastprice ".
                    "FROM 		pairs P ".
                    "INNER JOIN currencies C ".
                    "ON			C.id = P.currencyid ".
                    "INNER JOIN currencies CR ".
                    "ON			P.refcurrencyid= CR.id ".
                    "LEFT JOIN  (".$price.") G ".
                    "ON         G.pairid=P.id " .
                    "LEFT JOIN  (".$volume.") V ".
                    "ON         V.pairid=P.id " .
                    "LEFT JOIN  (".$lstp.") L ".
                    "ON         L.pairid=P.id " .
                    "WHERE 		P.status = 1 AND P.id = " .$pairid. " ".
                    "ORDER BY   P.id";

        if (!$rs=$db->query($sql))
            badEnd("500", array("msg"=>determinateDBError($db)));
        
        $row = $rs->fetch_assoc();
        $prices = [];
        
        $dates = explode(",",$row['date']); //Fechas completas
        $prices = explode(",",$row['price']); //Todos los precios


        $vols = explode(",",$row['volume']); //Todos los Volumen label        
        $last = $row['lastprice'];
        
        for($i=0;$i<count($dates);$i++){
            $date = $dates[$i];

            $record = new stdClass();
            if($prices[$i]!="--"){
                $last = $prices[$i];
            }
            //$fulldatelbl = $fulldatelbls[$i];
            //$datelbl = $datelbls[$i];

            $record->label = assembleSimpleLabel($timeframe,$date);
            $record->labeldetail = assembleStrongLabel($timeframe,$date);  
            $record->cannonical = $date;
            $record->price = new stdClass();
            $record->price->number = (float)number_format($last,$row["currencydecimals"], '.', '');
            $record->price->formatted = numberFormatt($last, $row["currencydecimals"]);
           
            $lines [] = $record;

            $record = new stdClass();
            //$fulldatelbl = $fulldatelbls[$i];
            //$datelbl = $datelbls[$i];

            $record->label = assembleSimpleLabel($timeframe,$date);
            $record->labeldetail = assembleStrongLabel($timeframe,$date);             
            $record->cannonical = $date;
            $record->price = new stdClass();
            
            $volume = $vols[$i];
            if($volume=="--")$volume = 0;
            $record->price->number = (float)$volume;
            $record->price->formatted = numberFormatt($volume, $row["refcurrencydecimals"]);
            $bars [] = $record;
        }
        //Lines es el precio del ultimo CADA TIMEFRAME
        $chart->pair = $row["refcurrencysymbol"]."/".$row["currencysymbol"];
        $chart->lines = new stdClass();
        $chart->lines->records = $lines;
        $chart->lines->max =($row['maxprice']=="--") ?  (float)number_format($last,$row["currencydecimals"], '.', '') :  (float)number_format($row['maxprice'],$row["currencydecimals"], '.', '');
        //bars es el volumen de intercambio CADA TIMEFRAME
        $chart->bars = new stdClass();
        $chart->bars->records = $bars;
        $chart->bars->max = ($row['maxvol']=="--") ? 0 : (float)$row['maxvol'];
        return $chart;
     
    
    }
    /* Funcion para determinar el timeframe de la grafica:
        1 = 1hora
        2 = 4 horas
        3 = 24 horas
        otro = 1 hora
    */
    function convertTimeFrame($timeframe){
        $obj = new stdClass();
        switch($timeframe){
            case 1:
                $obj->interval = '1 HOUR';
                $obj->days = 4;
                $obj->lbl = "IF((EXTRACT(HOUR FROM X.h) = 00), '%d', '%H:00')";
                $obj->fulllbl = "'%d %b %Y %H:00'";
            break;
            case 2:
                $obj->interval = '4 HOUR';
                $obj->days = 11;
                $obj->lbl = "IF((EXTRACT(HOUR FROM X.h) = 00), '%d', '%H:00')";
                $obj->fulllbl = "'%d %b %Y %H:00'";
            break;
            case 3:
                $obj->interval = '1 DAY';
                $obj->days = 61;
                $obj->lbl = "IF((EXTRACT(DAY FROM X.h) = 01), '%d %b', '%d')";                
                $obj->fulllbl = "'%d %b %Y'";
            break;
            default:
                $obj->interval = 1;
                $obj->days = 1;
                $obj->lbl = "IF((EXTRACT(HOUR FROM X.h) = 00), '%d', '%H:00')";
                $obj->fulllbl = "'%d %b %Y %H:00'";
            break;
        }
        
        return $obj;
    }
    /*  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));
            }
        
        }
    }
    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;
    }

    
?>