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

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

Fix errors counting clients
Better postinstallation script

File size: 13.8 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($this->dbconn->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($this->dbconn->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                        $dummy_new_date=$this->dates['first_this_month'];
104                        $sql="update historico set fecha ='$dummy_new_date' where app = 'dummy'";
105                        $this->dbconn->query($sql);
106                        $date_to_history=$this->dates['last_one_month_ago'];
107                        $sql="insert into historico(app,count,fecha,version,sabor) select app,count,'$date_to_history',version,sabor from packages";
108                        $this->dbconn->query($sql);
109                        $sql="truncate packages;";
110                        $this->dbconn->query($sql);
111                        $delete_before=$this->dates['first_two_month_ago'];
112                        $sql="delete from historico where app != 'dummy' and fecha < '$delete_before'";
113                        $this->dbconn->query($sql);
114
115                        $sql=$this->prepare_rotate_clients();
116                        foreach ($this->info_distro['distros'] as $distro) {
117                                $dname=$distro['name'];
118                                foreach ($distro['sabor'] as $sabor) {
119                                        $sname=$sabor['name'];
120                                        foreach (array('insert','delete') as $op) {
121                                                $this->dbconn->query($sql[$dname][$sname][$op]);
122                                        }
123                                }
124                                foreach (array('insert','delete') as $op) {
125                                        $this->dbconn->query($sql[$dname]['others'][$op]);
126                                }
127                        }
128                }
129        }
130        function prepare_rotate_clients(){
131                $date_clients='BETWEEN \''.$this->dates['first_one_month_ago'].'\' and \''.$this->dates['first_this_month'].'\'';
132                foreach ($this->info_distro['distros'] as $distro) {
133                        $dname=$distro['name'];
134                        $dlike=$distro['like'];
135                        $sql_clients[$dname]=array();
136                        $sql_where_other_clients=array();
137                        $version_in_history='';
138                        if ($dname == '15')
139                                $version_in_history='15.05';
140                        else
141                                $version_in_history='16';
142                        $date_to_history=$this->dates['last_one_month_ago'];
143
144                        foreach ($distro['sabor'] as $sabor) {
145                                $sname=$sabor['name'];
146                                $slike=$sabor['like'];
147                                $sql_where_other_clients[]='( sabor '.$slike.')';
148                                $where_select="lastlogin $date_clients and version $dlike and (sabor $slike)";
149                                $sql_clients[$dname][$sname]['delete']="delete from clients where $where_select ;";
150                                $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 );";
151                        }
152                        //others
153                        $sql_sabor_where_other_clients=implode(' or ',$sql_where_other_clients);
154                        $where_other_clients="lastlogin $date_clients and version $dlike and not ($sql_sabor_where_other_clients)";
155                        $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);";
156                        $sql_clients[$dname]['others']['delete']="delete from clients where $where_other_clients;";
157                }
158                return $sql_clients;
159        }
160        function get_historic_data(){
161                $this->rotate();
162                $this->load_alias();
163                //echo $today.' '.$first_this_month.' '.$first_one_month_ago.' '.$last_one_month_ago.' '.$first_two_month_ago.' '.$last_two_month_ago.' EOL';
164                $obj=[];
165                //4 Debugging only
166                //$dbg_obj=[];
167                $other_sql=array();
168                foreach ($this->info_distro['distros'] as $distro){
169                        $dname=$distro['name'];
170                        $dlike=$distro['like'];
171                        $obj[$dname]=array();
172                        //4 Debugging only
173                        //$dbg_obj[$dname]=array();
174                        foreach ($distro['sabor'] as $sabor){
175                                $sname=$sabor['name'];
176                                $slike=$sabor['like'];
177                                $obj[$dname][$sname][]=$this->get_current_chart($dlike,$slike);
178                                $obj[$dname][$sname][]=$this->get_old_chart($dlike,$slike);
179                                $obj[$dname][$sname][]=$this->get_very_old_chart($dlike,$slike);
180                                $other_sql[]=$this->get_current_chart($dlike,$slike,'yes');
181                                $other_sql[]=$this->get_old_chart($dlike,$slike,'yes');
182                                $other_sql[]=$this->get_very_old_chart($dlike,$slike,'yes');
183                               
184                                //4 Debugging only
185                                //$dbg_obj[$dname][$sname][]=$this->get_current_chart($dlike,$slike,'yes');
186                                //$dbg_obj[$dname][$sname][]=$this->get_old_chart($dlike,$slike,'yes');
187                                //$dbg_obj[$dname][$sname][]=$this->get_very_old_chart($dlike,$slike,'yes');
188                        }
189                }
190                //4 Debugging only
191                //file_put_contents('/tmp/graph_sql',str_replace('\\','',var_export($dbg_obj,true)));
192                //file_put_contents('/tmp/other_sql',str_replace('\\','',var_export($this->gen_other($other_sql),true)));
193                $sql_other_sentences=$this->gen_other($other_sql);
194                //4 Debugging only
195                //file_put_contents('/tmp/rotate_sql',str_replace('\\','',var_export($this->prepare_rotate_clients(),true)));
196               
197                $obj['other']['other'][]=array($this->get_result_from_sql($sql_other_sentences['current']),$this->get_clients_from_sql($sql_other_sentences['current_clients']));
198                $obj['other']['other'][]=array($this->get_result_from_sql($sql_other_sentences['old']),$this->get_clients_from_sql($sql_other_sentences['old_clients']));
199                $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']));
200                return json_encode($obj);
201        }
202        function gen_other($other_data){
203            $sql=array('current' =>'','old'=>'', 'very_old'=>'', 'current_clients' => '', 'old_clients' => '', 'very_old_clients' => '');
204
205            $sql['current']="select app,sum(count) as count from (select app,count from packages where (app,version,sabor) not in ( select app,version,sabor from packages where (";
206            $sql['old']="select app,sum(count) as count,fecha from historico where";
207            $sql['very_old']="select app,sum(count) as count,fecha from historico where";
208            $where_current=array();
209            $where_old=array();
210            $where_very_old=array();
211            foreach($other_data as $odata){
212                switch($odata['type']){
213                    case 'current':
214                        $date_current=$odata['date'];
215                        $where_current[]=$odata['where'];
216                    break;
217                    case 'old':
218                        $date_old=$odata['date'];
219                        $where_old[]=$odata['where'];
220                    break;
221                    case 'very_old':
222                        $date_very_old=$odata['date'];
223                        $where_very_old[]=$odata['where'];
224                    break;
225                }
226            }
227            $sql['current'].=implode(' or ',$where_current);
228            $sql['current'].=")) UNION ALL select app,count from historico where app in ( select app from historico where ";
229            $sql['current'].="fecha ".$date_current.  " and not (";
230            $sql['current'].=implode(' or ',$where_current);
231            $sql['current'].=")) and app not like 'dummy' ) tabla group by app order by count DESC LIMIT 30";
232            $sql['old'].=' fecha '.$date_old." and not (";
233            $sql['old'].=implode(' or ',$where_old);
234            $sql['old'].=") group by app order by sum(count) DESC LIMIT 30";
235            $sql['very_old'].=' fecha '.$date_very_old." and not(";
236            $sql['very_old'].=implode(' or ',$where_very_old);
237            $sql['very_old'].=") group by app order by sum(count) DESC LIMIT 30";
238           
239            $sql['current_clients'].="select coalesce(sum(suma_parcial),0) as count from (";
240            $sql['current_clients'].="select count(id) as suma_parcial from clients where lastlogin $date_current and not ( ".implode(' or ',$where_current);
241            $sql['current_clients'].=") UNION ALL select nclients as suma_parcial from historico_clients where fecha $date_current and not ( ".implode(' or ',$where_current);
242            $sql['current_clients'].=") ) tabla";
243
244            $sql['old_clients'].="select coalesce(sum(nclients),0) as count from historico_clients where fecha $date_old and not(";
245            $sql['old_clients'].=implode(' or ',$where_old). ')';
246
247            $sql['very_old_clients'].="select coalesce(sum(nclients),0) as count from historico_clients where fecha $date_very_old and not(";
248            $sql['very_old_clients'].=implode(' or ',$where_very_old). ')';
249            return $sql;
250        }
251        function get_current_chart($version='',$sabor='',$dumpsql='no'){
252                $date1=$this->dates['first_this_month'];
253                $date2=$this->dates['tomorrow'];
254                if ($version != '' and $sabor != ''){
255                        $where="version $version and sabor $sabor";
256                }else{
257                        $where='';
258                }
259                $range_dates="BETWEEN '$date1' and '$date2' ";
260                $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";
261                $sql_clients = "select count(user) as count from clients where (lastlogin $range_dates) and $where";
262                if($dumpsql == "yes")
263                        return array('type'=>'current','date' => "$range_dates",'where' => '('.$where.')','clients'=>"$sql_clients",'sql'=>$sql);
264               
265                return array($this->get_result_from_sql($sql),$this->get_clients_from_sql($sql_clients));
266        }
267        function get_clients_from_sql($sql){
268                if ($result=$this->dbconn->query($sql)){
269                        while($row=$result->fetch_array(MYSQLI_ASSOC)){
270                                if (isset($row['count'])){
271                                        return array('nclients'=>$row['count']);
272                                }
273                        }
274                        return array('nclients'=>'not available');
275                }else{
276                        return array('nclients'=>'Query error');
277                }
278        }
279        function get_result_from_sql($sql){
280                if ($result=$this->dbconn->query($sql)){
281                        $obj2=[];
282                        $nobj=0;
283                        while($row=$result->fetch_array(MYSQLI_ASSOC)){
284                                if (array_key_exists($row['app'],$this->alias)){
285                                        if (! empty($this->alias[$row['app']])){
286                                                if ($nobj < 10)
287                                                        $obj2[$this->alias[$row['app']]]=$row['count'];
288                                                $nobj++;
289                                        }
290                                }else{
291                                        if ($nobj < 10)
292                                                $obj2[$row['app']]=$row['count'];
293                                        $nobj++;
294                                }
295                        }
296                        return $obj2;
297                }
298                return false;
299        }
300        function get_old_chart($version='',$sabor='',$dumpsql='no'){
301                $date1=$this->dates['first_one_month_ago'];
302                $date2=$this->dates['last_one_month_ago'];
303                if ($version != '' and $sabor != ''){
304                        $where_a="version $version and sabor $sabor";
305                }else{
306                        $where_a='';
307                }
308                $range_dates="BETWEEN '$date1' and '$date2'";
309                $sql="SELECT app,sum(count) as count,fecha from historico where fecha $range_dates and $where_a group by app order by sum(count) DESC LIMIT 30";
310                $sql_clients = "select nclients as count from historico_clients where (fecha $range_dates) and $where_a";
311                if($dumpsql == "yes")
312                        return array('type'=>'old','date'=> "$range_dates",'where'=>'('.$where_a.')','clients'=>"$sql_clients",'sql'=>$sql);
313                return array($this->get_result_from_sql($sql),$this->get_clients_from_sql($sql_clients));
314        }
315        function get_very_old_chart($version='',$sabor='',$dumpsql='no'){
316                $date1=$this->dates['first_two_month_ago'];
317                $date2=$this->dates['last_two_month_ago'];
318                if ($version != '' and $sabor != ''){
319                        $where_a="version $version and sabor $sabor";
320                }else{
321                        $where_a='';
322                }
323                $range_dates="BETWEEN '$date1' and '$date2'";
324                $sql="SELECT app,sum(count) as count,fecha from historico where fecha $range_dates and $where_a group by app order by sum(count) DESC LIMIT 30";
325                $sql_clients = "select nclients as count from historico_clients where (fecha $range_dates) and $where_a";
326                if($dumpsql == "yes")
327                        return array('type'=>'very_old','date' => "$range_dates",'where' => '('.$where_a.')','clients'=>"$sql_clients",'sql'=>$sql);
328               
329                return array($this->get_result_from_sql($sql),$this->get_clients_from_sql($sql_clients));
330        }
331} 
332
333?>
Note: See TracBrowser for help on using the repository browser.