| 1 | <?php |
|---|
| 2 | class 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 | ?> |
|---|
