source: lliurex-analytics-server/trunk/fuentes/lliurex-analytics-server/usr/lib/analytics-server/analytics/db.php @ 699

<?php
class DB{

        private $dbhost;
        private $dbname;
        private $dbuser;
        private $dbpass;
        private $alias;
        public $dbconn;

        function DB(){
                require_once('config.php');
                global $dbhost,$dbname,$dbpass,$dbuser;
                
                $this->dbhost=$dbhost;
                $this->dbname=$dbname;
                $this->dbpass=$dbpass;
                $this->dbuser=$dbuser;
                $this->alias=array();
        }
        function connect(){
                $this->dbconn=new mysqli($this->dbhost, $this->dbuser , $this->dbpass, $this->dbname);
                if ($this->dbconn->connect_error) {
                die('Connect Error:'. $this->dbconn->connect_error);
                }
        }
        function disconnect(){
                $this->dbconn->close();
        }
        function init_trans(){
                $this->dbconn->autocommit(FALSE);
                $this->dbconn->begin_transaction(MYSQLI_TRANS_START_READ_WRITE);
        }
        function insert_data($user,$date){
                $sql = "INSERT INTO clients (user,lastlogin) VALUES (?,?) ON DUPLICATE KEY UPDATE lastlogin = VALUES(lastlogin)";
                $query=$this->dbconn->prepare($sql);
                if (! $query)
                        throw new Exception($this->dbconn->error);
                $query->bind_param("ss",$user,$date);
                $query->execute();
                if ( $query->affected_rows < 0 )
                        throw new Exception($query->mysqli_error());
                $query->close();
        }
        function update_data($data=''){
                $sql="INSERT INTO packages (app,count) VALUES (?,?) ON DUPLICATE KEY UPDATE count = count + ?";
                $query=$this->dbconn->prepare($sql);
                $query->bind_param("sss",$k,$v,$v);
                if (! $query)
                        throw new Exception($this->dbconn->error);
                foreach ($data as $k => $v){
                        $noerr=$query->execute();
                        if (! $noerr){
                                throw new Exception($query->mysqli_error());
                        }
                }
                $query->close();
        }
        private function load_alias(){
                $sql="SELECT name,alias from alias";
                $result=$this->dbconn->query($sql);
                while($row=$result->fetch_array(MYSQLI_ASSOC)){
                        $this->alias[$row['name']]=$row['alias'];
                }
        }
        function rotate(){
            $sql="select max(fecha) from historico;";
            $thismonth=intval(date("Ym"));
            $lastmonth=$thismonth;
            if ($result=$this->dbconn->query($sql)){
                while($row=$result->fetch_row()){
                    if(empty($row[0])){
                        $sql="insert into historico(app,count,fecha) values (?,?,?)";
                        $query=$this->dbconn->prepare($sql);
                        $name='dummy'; $value=0; $today=date("Y-m-d");
                        $query->bind_param('sds',$name,$value,$today);
                        $query->execute();
                        $query->close();
                    }else{
                        $lastmonth=intval(explode('-',$row[0])[0].explode('-',$row[0])[1]);
                    }
                }
            }
            if ($thismonth > $lastmonth){
                $sql="insert into historico(app,count,fecha) (select app,count,DATE(NOW()) as fecha from packages);";
                $this->dbconn->query($sql);
                $sql="truncate packages;";
                $this->dbconn->query($sql);
            }
        }
        function get_historic_data(){
                $this->load_alias();
                $today=date("Y-m-d");
                $first_this_month=date("Y-m-").'01';
                $last_one_month_ago=date("Y-m-d",strtotime("$first_this_month -1 days"));
                $first_one_month_ago=date("Y-m-d",strtotime("$first_this_month -1 months"));
                $last_two_month_ago=date("Y-m-d",strtotime("$first_one_month_ago -1 days"));
                $first_two_month_ago=date("Y-m-d",strtotime("$first_one_month_ago -1 months"));
                //echo $today.' '.$first_this_month.' '.$first_one_month_ago.' '.$last_one_month_ago.' '.$first_two_month_ago.' '.$last_two_month_ago.' EOL';
                $obj=[];
                $sql="SELECT app,count from packages UNION SELECT app,count from historico where fecha BETWEEN '$first_this_month' and '$today' order by count DESC LIMIT 30";
                if ($result=$this->dbconn->query($sql)){
                        $obj2=[];
                        $nobj=0;
                        while($row=$result->fetch_array(MYSQLI_ASSOC)){
                                if (array_key_exists($row['app'],$this->alias)){
                                        if (! empty($this->alias[$row['app']])){
                                                if ($nobj < 10)
                                                        $obj2[$this->alias[$row['app']]]=$row['count'];
                                                $nobj++;
                                        }
                                }else{
                                        if ($nobj < 10)
                                                $obj2[$row['app']]=$row['count'];
                                        $nobj++;
                                }
                        }
                        $obj[]=$obj2;
                }
                $sql="SELECT app,count,fecha from historico where fecha BETWEEN '$first_one_month_ago' and '$last_one_month_ago' order by count DESC LIMIT 30";
                if ($result=$this->dbconn->query($sql)){
                        $obj2=[];
                        $nobj=0;
                        while($row=$result->fetch_array(MYSQLI_ASSOC)){
                                if (array_key_exists($row['app'],$this->alias)){
                                        if (! empty($this->alias[$row['app']])){
                                                if ($nobj < 10)
                                                        $obj2[$this->alias[$row['app']]]=$row['count'];
                                                $nobj++;
                                        }
                                }else{
                                        if ($nobj < 10)
                                                $obj2[$row['app']]=$row['count'];
                                        $nobj++;
                                }
                        }
                        $obj[]=$obj2;
                }
                $sql="SELECT app,count,fecha from historico where fecha BETWEEN '$first_two_month_ago' and '$last_two_month_ago' order by count DESC LIMIT 30";
                if ($result=$this->dbconn->query($sql)){
                        $obj2=[];
                        $nobj=0;
                        while($row=$result->fetch_array(MYSQLI_ASSOC)){
                                if (array_key_exists($row['app'],$this->alias)){
                                        if (! empty($this->alias[$row['app']])){
                                                if ($nobj < 10)
                                                        $obj2[$this->alias[$row['app']]]=$row['count'];
                                                $nobj++;
                                        }
                                }else{
                                        if ($nobj < 10)
                                                $obj2[$row['app']]=$row['count'];
                                        $nobj++;
                                }
                        }
                        $obj[]=$obj2;
                }

                return json_encode($obj);

        }
} 

?>