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

Last change on this file since 4854 was 4854, checked in by mabarracus, 3 years ago

Add number of clients into statistics report

File size: 13.2 KB
Line 
1<?php
2class DB{
3
4        private $dbhost;
5        private $dbname;
6        private $dbuser;
7        private $dbpass;
8        private $alias;
9        public $dbconn;
10
11        function DB(){
12                require_once('config.php');
13                global $dbhost,$dbname,$dbpass,$dbuser,$distros;
14               
15                $this->dbhost=$dbhost;
16                $this->dbname=$dbname;
17                $this->dbpass=$dbpass;
18                $this->dbuser=$dbuser;
19                $this->alias=array();
20                $this->info_distro=json_decode($distros,true);
21                $this->init_dates();
22        }
23        function init_dates(){
24                $this->dates=array();
25                $this->dates['today']=date("Y-m-d");
26                $this->dates['tomorrow']=date("Y-m-d",strtotime('tomorrow'));
27                $this->dates['first_this_month']=date("Y-m-").'01';
28                $this->dates['last_one_month_ago']=date("Y-m-d",strtotime($this->dates['first_this_month']." -1 days"));
29                $this->dates['first_one_month_ago']=date("Y-m-d",strtotime($this->dates['first_this_month']." -1 months"));
30                $this->dates['last_two_month_ago']=date("Y-m-d",strtotime($this->dates['first_one_month_ago']." -1 days"));
31                $this->dates['first_two_month_ago']=date("Y-m-d",strtotime($this->dates['first_one_month_ago']." -1 months"));
32
33        }
34        function connect(){
35                $this->dbconn=new mysqli($this->dbhost, $this->dbuser , $this->dbpass, $this->dbname);
36                if ($this->dbconn->connect_error) {
37                die('Connect Error:'. $this->dbconn->connect_error);
38                }
39        }
40        function disconnect(){
41                $this->dbconn->close();
42        }
43        function init_trans(){
44                $this->dbconn->autocommit(FALSE);
45                $this->dbconn->begin_transaction(MYSQLI_TRANS_START_READ_WRITE);
46        }
47        function insert_data($user,$date,$version,$sabor){
48                $sql = "INSERT INTO clients (user,lastlogin,version,sabor) VALUES (?,?,?,?) ON DUPLICATE KEY UPDATE lastlogin = VALUES(lastlogin)";
49                $query=$this->dbconn->prepare($sql);
50                if (! $query)
51                        throw new Exception($this->dbconn->error);
52                $query->bind_param("ssss",$user,$date,$version,$sabor);
53                $query->execute();
54                if ( $query->affected_rows < 0 ){
55                        throw new Exception($query->mysqli_error());
56                }else{
57                        $id=$this->dbconn->insert_id;
58                        $query->close();
59                        return $id;
60                }
61                return false;
62        }
63        function update_data($data='',$version='',$sabor=''){
64                $sql="INSERT INTO packages (app,count,version,sabor) VALUES (?,?,?,?) ON DUPLICATE KEY UPDATE count = count + ?";
65                $query=$this->dbconn->prepare($sql);
66                $query->bind_param("sssss",$k,$v,$version,$sabor,$v);
67                if (! $query)
68                        throw new Exception($this->dbconn->error);
69                foreach ($data as $k => $v){
70                        $noerr=$query->execute();
71                        if (! $noerr){
72                                throw new Exception($query->mysqli_error());
73                        }
74                }
75                $query->close();
76        }
77        private function load_alias(){
78                $sql="SELECT name,alias from alias";
79                $result=$this->dbconn->query($sql);
80                while($row=$result->fetch_array(MYSQLI_ASSOC)){
81                        $this->alias[$row['name']]=$row['alias'];
82                }
83        }
84        function rotate(){
85                $sql="select max(fecha) from historico;";
86                $thismonth=intval(date("Ym"));
87                $lastmonth=$thismonth;
88                if ($result=$this->dbconn->query($sql)){
89                        while($row=$result->fetch_row()){
90                                if(empty($row[0])){
91                                        $sql="insert into historico(app,count,fecha,version,sabor) values (?,?,?,?,?)";
92                                        $query=$this->dbconn->prepare($sql);
93                                        $name='dummy'; $value=0; $today=date("Y-m-d");
94                                        $query->bind_param('sdsss',$name,$value,$today,$name,$name);
95                                        $query->execute();
96                                        $query->close();
97                                }else{
98                                        $lastmonth=intval(explode('-',$row[0])[0].explode('-',$row[0])[1]);
99                                }
100                        }
101                }
102                if ($thismonth > $lastmonth){
103                        $date_to_history=$this->dates['last_one_month_ago'];
104                        $sql="insert into historico(app,count,fecha,version,sabor) (select app,count,'$date_to_history' as fecha,version,sabor from packages);";
105                        $this->dbconn->query($sql);
106                        $sql="truncate packages;";
107                        $this->dbconn->query($sql);
108
109                        $sql=$this->prepare_rotate_clients();
110                        foreach ($this->info_distro['distros'] as $distro) {
111                                $dname=$distro['name'];
112                                foreach ($distro['sabor'] as $sabor) {
113                                        $sname=$sabor['name'];
114                                        foreach (array('insert','delete') as $op) {
115                                                $this->dbconn->query($sql[$dname][$sname][$op]);
116                                        }
117                                }
118                                foreach (array('insert','delete') as $op) {
119                                        $this->dbconn->query($sql[$dname]['others'][$op]);
120                                }
121                        }
122                }
123        }
124        function prepare_rotate_clients(){
125                $date_clients='BETWEEN \''.$this->dates['first_one_month_ago'].'\' and \''.$this->dates['first_this_month'].'\'';
126                foreach ($this->info_distro['distros'] as $distro) {
127                        $dname=$distro['name'];
128                        $dlike=$distro['like'];
129                        $sql_clients[$dname]=array();
130                        $sql_where_other_clients=array();
131                        $version_in_history='';
132                        if ($dname == '15')
133                                $version_in_history='15.05';
134                        else
135                                $version_in_history='16';
136                        $date_to_history=$this->dates['last_one_month_ago'];
137
138                        foreach ($distro['sabor'] as $sabor) {
139                                $sname=$sabor['name'];
140                                $slike=$sabor['like'];
141                                $sql_where_other_clients[]='( sabor '.$slike.')';
142                                $where_select="lastlogin $date_clients and version $dlike and (sabor $slike)";
143                                $sql_clients[$dname][$sname]['delete']="delete from clients where $where_select ;";
144                                $sql_clients[$dname][$sname]['insert']="insert into historico_clients(version,sabor,fecha,nclients) (select '$version_in_history','$sname','$date_to_history',count(id) from clients where $where_select );";
145                        }
146                        //others
147                        $sql_sabor_where_other_clients=implode(' or ',$sql_where_other_clients);
148                        $where_other_clients="lastlogin $date_clients and version $dlike and not ($sql_sabor_where_other_clients)";
149                        $sql_clients[$dname]['others']['insert']="insert into historico_clients(version,sabor,fecha,nclients) (select '$version_in_history','others','$date_to_history',count(id) from clients where  $where_other_clients);";
150                        $sql_clients[$dname]['others']['delete']="delete from clients where $where_other_clients;";
151                }
152                return $sql_clients;
153        }
154        function get_historic_data(){
155                $this->rotate();
156                $this->load_alias();
157                //echo $today.' '.$first_this_month.' '.$first_one_month_ago.' '.$last_one_month_ago.' '.$first_two_month_ago.' '.$last_two_month_ago.' EOL';
158                $obj=[];
159                //4 Debugging only
160                $dbg_obj=[];
161                $other_sql=array();
162                foreach ($this->info_distro['distros'] as $distro){
163                        $dname=$distro['name'];
164                        $dlike=$distro['like'];
165                        $obj[$dname]=array();
166                        //4 Debugging only
167                        $dbg_obj[$dname]=array();
168                        foreach ($distro['sabor'] as $sabor){
169                                $sname=$sabor['name'];
170                                $slike=$sabor['like'];
171                                $obj[$dname][$sname][]=$this->get_current_chart($dlike,$slike);
172                                $obj[$dname][$sname][]=$this->get_old_chart($dlike,$slike);
173                                $obj[$dname][$sname][]=$this->get_very_old_chart($dlike,$slike);
174                                $other_sql[]=$this->get_current_chart($dlike,$slike,'yes');
175                                $other_sql[]=$this->get_old_chart($dlike,$slike,'yes');
176                                $other_sql[]=$this->get_very_old_chart($dlike,$slike,'yes');
177                               
178                                //4 Debugging only
179                                $dbg_obj[$dname][$sname][]=$this->get_current_chart($dlike,$slike,'yes');
180                                $dbg_obj[$dname][$sname][]=$this->get_old_chart($dlike,$slike,'yes');
181                                $dbg_obj[$dname][$sname][]=$this->get_very_old_chart($dlike,$slike,'yes');
182                        }
183                }
184                //4 Debugging only
185                file_put_contents('/tmp/graph_sql',str_replace('\\','',var_export($dbg_obj,true)));
186                file_put_contents('/tmp/other_sql',str_replace('\\','',var_export($this->gen_other($other_sql),true)));
187                $sql_other_sentences=$this->gen_other($other_sql);
188                //4 Debugging only
189                file_put_contents('/tmp/rotate_sql',str_replace('\\','',var_export($this->prepare_rotate_clients(),true)));
190               
191                $obj['other']['other'][]=array($this->get_result_from_sql($sql_other_sentences['current']),$this->get_clients_from_sql($sql_other_sentences['current_clients']));
192                $obj['other']['other'][]=array($this->get_result_from_sql($sql_other_sentences['old']),$this->get_clients_from_sql($sql_other_sentences['old_clients']));
193                $obj['other']['other'][]=array($this->get_result_from_sql($sql_other_sentences['very_old']),$this->get_clients_from_sql($sql_other_sentences['very_old_clients']));
194                return json_encode($obj);
195        }
196        function gen_other($other_data){
197            $sql=array('current' =>'','old'=>'', 'very_old'=>'', 'current_clients' => '', 'old_clients' => '', 'very_old_clients' => '');
198
199            $sql['current']="select app,sum(count) as count from (select app,count from packages where app not in ( select app from packages where (";
200            $sql['old']="select app,count,fecha from historico where";
201            $sql['very_old']="select app,count,fecha from historico where";
202            $where_current=array();
203            $where_old=array();
204            $where_very_old=array();
205            foreach($other_data as $odata){
206                switch($odata['type']){
207                    case 'current':
208                        $date_current=$odata['date'];
209                        $where_current[]=$odata['where'];
210                    break;
211                    case 'old':
212                        $date_old=$odata['date'];
213                        $where_old[]=$odata['where'];
214                    break;
215                    case 'very_old':
216                        $date_very_old=$odata['date'];
217                        $where_very_old[]=$odata['where'];
218                    break;
219                }
220            }
221            $sql['current'].=implode(' or ',$where_current);
222            $sql['current'].=")) UNION ALL select app,count from historico where app in ( select app from historico where ";
223            $sql['current'].="fecha ".$date_current.  " and not (";
224            $sql['current'].=implode(' or ',$where_current);
225            $sql['current'].=")) and app not like 'dummy' ) tabla group by app order by count DESC LIMIT 30";
226            $sql['old'].=' fecha '.$date_old." and not (";
227            $sql['old'].=implode(' or ',$where_old);
228            $sql['old'].=") group by app order by count DESC LIMIT 30";
229            $sql['very_old'].=' fecha '.$date_very_old." and not(";
230            $sql['very_old'].=implode(' or ',$where_very_old);
231            $sql['very_old'].=") group by app order by count DESC LIMIT 30";
232           
233            $sql['current_clients'].="select coalesce(sum(suma_parcial),0) as count from (";
234            $sql['current_clients'].="select count(id) as suma_parcial from clients where lastlogin $date_current and not ( ".implode(' or ',$where_current);
235            $sql['current_clients'].=") UNION ALL select nclients as suma_parcial from historico_clients where fecha $date_current and not ( ".implode(' or ',$where_current);
236            $sql['current_clients'].=") ) tabla";
237
238            $sql['old_clients'].="select coalesce(sum(nclients),0) as count from historico_clients where fecha $date_old and not(";
239            $sql['old_clients'].=implode(' or ',$where_old). ')';
240
241            $sql['very_old_clients'].="select coalesce(sum(nclients),0) as count from historico_clients where fecha $date_very_old and not(";
242            $sql['very_old_clients'].=implode(' or ',$where_very_old). ')';
243            return $sql;
244        }
245        function get_current_chart($version='',$sabor='',$dumpsql='no'){
246                $date1=$this->dates['first_this_month'];
247                $date2=$this->dates['tomorrow'];
248                if ($version != '' and $sabor != ''){
249                        $where="version $version and sabor $sabor";
250                }else{
251                        $where='';
252                }
253                $range_dates="BETWEEN '$date1' and '$date2' ";
254                $sql="select app,sum(count) as count from (SELECT app,count from packages where $where UNION ALL SELECT app,count from historico where fecha $range_dates and $where) tabla group by app order by count DESC LIMIT 30";
255                $sql_clients = "select count(user) as count from clients where (lastlogin $range_dates) and $where";
256                if($dumpsql == "yes")
257                        return array('type'=>'current','date' => "$range_dates",'where' => '('.$where.')','clients'=>"$sql_clients");
258               
259                return array($this->get_result_from_sql($sql),$this->get_clients_from_sql($sql_clients));
260        }
261        function get_clients_from_sql($sql){
262                if ($result=$this->dbconn->query($sql)){
263                        while($row=$result->fetch_array(MYSQLI_ASSOC)){
264                                if (isset($row['count'])){
265                                        return array('nclients'=>$row['count']);
266                                }
267                        }
268                        return array('nclients'=>'not available');
269                }else{
270                        return array('nclients'=>'Query error');
271                }
272        }
273        function get_result_from_sql($sql){
274                if ($result=$this->dbconn->query($sql)){
275                        $obj2=[];
276                        $nobj=0;
277                        while($row=$result->fetch_array(MYSQLI_ASSOC)){
278                                if (array_key_exists($row['app'],$this->alias)){
279                                        if (! empty($this->alias[$row['app']])){
280                                                if ($nobj < 10)
281                                                        $obj2[$this->alias[$row['app']]]=$row['count'];
282                                                $nobj++;
283                                        }
284                                }else{
285                                        if ($nobj < 10)
286                                                $obj2[$row['app']]=$row['count'];
287                                        $nobj++;
288                                }
289                        }
290                        return $obj2;
291                }
292                return false;
293        }
294        function get_old_chart($version='',$sabor='',$dumpsql='no'){
295                $date1=$this->dates['first_one_month_ago'];
296                $date2=$this->dates['last_one_month_ago'];
297                if ($version != '' and $sabor != ''){
298                        $where_a="version $version and sabor $sabor";
299                }else{
300                        $where_a='';
301                }
302                $range_dates="BETWEEN '$date1' and '$date2'";
303                $sql="SELECT app,count,fecha from historico where fecha $range_dates and $where_a order by count DESC LIMIT 30";
304                $sql_clients = "select nclients as count from historico_clients where (fecha $range_dates) and $where_a";
305                if($dumpsql == "yes")
306                        return array('type'=>'old','date'=> "$range_dates",'where'=>'('.$where_a.')','clients'=>"$sql_clients");
307                return array($this->get_result_from_sql($sql),$this->get_clients_from_sql($sql_clients));
308        }
309        function get_very_old_chart($version='',$sabor='',$dumpsql='no'){
310                $date1=$this->dates['first_two_month_ago'];
311                $date2=$this->dates['last_two_month_ago'];
312                if ($version != '' and $sabor != ''){
313                        $where_a="version $version and sabor $sabor";
314                }else{
315                        $where_a='';
316                }
317                $range_dates="BETWEEN '$date1' and '$date2'";
318                $sql="SELECT app,count,fecha from historico where fecha $range_dates and $where_a order by count DESC LIMIT 30";
319                $sql_clients = "select nclients as count from historico_clients where (fecha $range_dates) and $where_a";
320                if($dumpsql == "yes")
321                        return array('type'=>'very_old','date' => "$range_dates",'where' => '('.$where_a.')','clients'=>"$sql_clients");
322               
323                return array($this->get_result_from_sql($sql),$this->get_clients_from_sql($sql_clients));
324        }
325} 
326
327?>
Note: See TracBrowser for help on using the repository browser.