Changeset 747 for lliurex-analytics-server/trunk/fuentes
- Timestamp:
- Mar 1, 2016, 9:45:21 AM (5 years ago)
- Location:
- lliurex-analytics-server/trunk/fuentes
- Files:
-
- 20 added
- 7 edited
Legend:
- Unmodified
- Added
- Removed
-
lliurex-analytics-server/trunk/fuentes/debian/changelog
r699 r747 1 lliurex-analytics-server (0.0.6) xenial; urgency=medium 2 3 * Add stats processing by release and flavour, improved stats page view 4 5 -- M.Angel Juan <m.angel.juan@gmail.com> Tue, 01 Mar 2016 09:44:50 +0100 6 1 7 lliurex-analytics-server (0.0.5) xenial; urgency=medium 2 8 -
lliurex-analytics-server/trunk/fuentes/debian/control
r352 r747 9 9 Depends: ${shlibs:Depends}, ${misc:Depends}, 10 10 debconf (>= 0.5) | debconf-2.0, po-debconf, 11 ucf, python3, python-daemon, apache2, libapache2-mod-php5, php5-mysql, mysql-server, python-requests 11 ucf, python3, python-daemon, apache2, libapache2-mod-php5, php5-mysql, mysql-server, python-requests, dialog 12 12 Recommends: 13 13 Suggests: -
lliurex-analytics-server/trunk/fuentes/lliurex-analytics-server/usr/lib/analytics-server/analytics.sql
r350 r747 1 -- phpMyAdmin SQL Dump2 -- version 4.4.13.1deb13 -- http://www.phpmyadmin.net4 --5 -- Servidor: localhost6 -- Tiempo de generación: 24-09-2015 a las 12:45:167 -- Versión del servidor: 5.6.19-1~exp1ubuntu28 -- Versión de PHP: 5.6.11-1ubuntu29 10 1 SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; 2 SET AUTOCOMMIT = 0; 3 START TRANSACTION; 11 4 SET time_zone = "+00:00"; 12 13 5 14 6 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; 15 7 /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; 16 8 /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; 17 /*!40101 SET NAMES utf8 mb4*/;9 /*!40101 SET NAMES utf8 */; 18 10 19 --20 -- Base de datos: `analytics`21 --22 11 CREATE DATABASE IF NOT EXISTS `analytics` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 23 12 USE `analytics`; 24 25 -- --------------------------------------------------------26 27 --28 -- Estructura de tabla para la tabla `alias`29 --30 13 31 14 DROP TABLE IF EXISTS `alias`; 32 15 CREATE TABLE IF NOT EXISTS `alias` ( 33 16 `name` varchar(50) NOT NULL, 34 `alias` varchar(50) DEFAULT NULL 17 `alias` varchar(50) DEFAULT NULL, 18 PRIMARY KEY (`name`) 35 19 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 36 37 -- --------------------------------------------------------38 39 --40 -- Estructura de tabla para la tabla `clients`41 --42 20 43 21 DROP TABLE IF EXISTS `clients`; 44 22 CREATE TABLE IF NOT EXISTS `clients` ( 45 `id` int(10) NOT NULL ,23 `id` int(10) NOT NULL AUTO_INCREMENT, 46 24 `user` varchar(18) NOT NULL, 47 `lastlogin` datetime NOT NULL 48 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 49 50 -- -------------------------------------------------------- 51 52 -- 53 -- Estructura de tabla para la tabla `historico` 54 -- 25 `lastlogin` datetime NOT NULL, 26 `version` char(20) NOT NULL, 27 `sabor` char(50) NOT NULL, 28 PRIMARY KEY (`id`), 29 UNIQUE KEY `user_2` (`user`,`version`,`sabor`) 30 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 55 31 56 32 DROP TABLE IF EXISTS `historico`; 57 33 CREATE TABLE IF NOT EXISTS `historico` ( 58 `id` int(11) NOT NULL ,34 `id` int(11) NOT NULL AUTO_INCREMENT, 59 35 `app` varchar(150) NOT NULL, 60 36 `count` int(11) NOT NULL, 61 `fecha` date NOT NULL 62 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 63 64 -- -------------------------------------------------------- 65 66 -- 67 -- Estructura de tabla para la tabla `packages` 68 -- 37 `fecha` date NOT NULL, 38 `version` char(20) NOT NULL, 39 `sabor` char(50) NOT NULL, 40 PRIMARY KEY (`id`) 41 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 69 42 70 43 DROP TABLE IF EXISTS `packages`; 71 44 CREATE TABLE IF NOT EXISTS `packages` ( 72 `id` int(11) NOT NULL ,45 `id` int(11) NOT NULL AUTO_INCREMENT, 73 46 `app` varchar(150) NOT NULL, 74 `count` int(10) NOT NULL 75 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 47 `count` int(10) NOT NULL, 48 `version` char(20) NOT NULL, 49 `sabor` char(50) NOT NULL, 50 PRIMARY KEY (`id`), 51 UNIQUE KEY `app` (`app`,`version`,`sabor`) 52 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 53 COMMIT; 76 54 77 --78 -- Índices para tablas volcadas79 --80 81 --82 -- Indices de la tabla `alias`83 --84 ALTER TABLE `alias`85 ADD PRIMARY KEY (`name`);86 87 --88 -- Indices de la tabla `clients`89 --90 ALTER TABLE `clients`91 ADD PRIMARY KEY (`id`),92 ADD UNIQUE KEY `user` (`user`);93 94 --95 -- Indices de la tabla `historico`96 --97 ALTER TABLE `historico`98 ADD PRIMARY KEY (`id`);99 100 --101 -- Indices de la tabla `packages`102 --103 ALTER TABLE `packages`104 ADD PRIMARY KEY (`id`),105 ADD UNIQUE KEY `a` (`app`);106 107 --108 -- AUTO_INCREMENT de las tablas volcadas109 --110 111 --112 -- AUTO_INCREMENT de la tabla `clients`113 --114 ALTER TABLE `clients`115 MODIFY `id` int(10) NOT NULL AUTO_INCREMENT;116 --117 -- AUTO_INCREMENT de la tabla `historico`118 --119 ALTER TABLE `historico`120 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;121 --122 -- AUTO_INCREMENT de la tabla `packages`123 --124 ALTER TABLE `packages`125 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;126 55 /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; 127 56 /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; -
lliurex-analytics-server/trunk/fuentes/lliurex-analytics-server/usr/lib/analytics-server/analytics/config.php
r350 r747 8 8 $app_dir='/analytics'; 9 9 10 $distros= 11 ' 12 { 13 "distros": [ 14 { 15 "name": "15", 16 "like": "LIKE \'%15.05%\'", 17 "sabor": [ 18 { 19 "name": "desktop", 20 "like": "NOT LIKE \'%server%\' and sabor LIKE \'%desktop%\'" 21 }, 22 { 23 "name": "server", 24 "like": "LIKE \'%server%\'" 25 }, 26 { 27 "name": "client", 28 "like": "LIKE \'%client%\'" 29 } 30 ] 31 }, 32 { 33 "name": "16", 34 "like": "LIKE \'%16%\'", 35 "sabor": [ 36 { 37 "name": "desktop", 38 "like": "NOT LIKE \'%server%\' and sabor LIKE \'%desktop%\'" 39 }, 40 { 41 "name": "server", 42 "like": "LIKE \'%server%\'" 43 }, 44 { 45 "name": "client", 46 "like": "LIKE \'%client%\'" 47 } 48 ] 49 } 50 ] 51 } 52 '; 53 function debug_json(){ 54 global $distros; 55 $a=json_decode($distros,true); 56 $num_distros=count($a['distros']); 57 echo '<pre>'.var_export($a,true).'</pre>'; 58 echo '<pre>'.$num_distros." distros\n</pre>"; 59 $i=1; 60 foreach ($a['distros'] as $distro){ 61 $nombre=$distro['name']; 62 $like=$distro['like']; 63 $num_sabores=count($distro['sabor']); 64 echo "<pre>Distro $i: nombre=$nombre like=$like con $num_sabores sabores.\n</pre>"; 65 $k=1; 66 foreach ($distro['sabor'] as $sabor){ 67 $sname=$sabor['name']; 68 $slike=$sabor['like']; 69 echo "<pre> Sabor $k: nombre=$sname like=$slike.\n</pre>"; 70 $k++; 71 } 72 $i++; 73 } 74 } 10 75 ?> -
lliurex-analytics-server/trunk/fuentes/lliurex-analytics-server/usr/lib/analytics-server/analytics/db.php
r699 r747 11 11 function DB(){ 12 12 require_once('config.php'); 13 global $dbhost,$dbname,$dbpass,$dbuser ;13 global $dbhost,$dbname,$dbpass,$dbuser,$distros; 14 14 15 15 $this->dbhost=$dbhost; … … 18 18 $this->dbuser=$dbuser; 19 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['first_this_month']=date("Y-m-").'01'; 27 $this->dates['last_one_month_ago']=date("Y-m-d",strtotime($this->dates['first_this_month']." -1 days")); 28 $this->dates['first_one_month_ago']=date("Y-m-d",strtotime($this->dates['first_this_month']." -1 months")); 29 $this->dates['last_two_month_ago']=date("Y-m-d",strtotime($this->dates['first_one_month_ago']." -1 days")); 30 $this->dates['first_two_month_ago']=date("Y-m-d",strtotime($this->dates['first_one_month_ago']." -1 months")); 31 20 32 } 21 33 function connect(){ … … 32 44 $this->dbconn->begin_transaction(MYSQLI_TRANS_START_READ_WRITE); 33 45 } 34 function insert_data($user,$date ){35 $sql = "INSERT INTO clients (user,lastlogin ) VALUES (?,?) ON DUPLICATE KEY UPDATE lastlogin = VALUES(lastlogin)";46 function insert_data($user,$date,$version,$sabor){ 47 $sql = "INSERT INTO clients (user,lastlogin,version,sabor) VALUES (?,?,?,?) ON DUPLICATE KEY UPDATE lastlogin = VALUES(lastlogin)"; 36 48 $query=$this->dbconn->prepare($sql); 37 49 if (! $query) 38 50 throw new Exception($this->dbconn->error); 39 $query->bind_param("ss ",$user,$date);51 $query->bind_param("ssss",$user,$date,$version,$sabor); 40 52 $query->execute(); 41 if ( $query->affected_rows < 0 ) 53 if ( $query->affected_rows < 0 ){ 42 54 throw new Exception($query->mysqli_error()); 43 $query->close(); 44 } 45 function update_data($data=''){ 46 $sql="INSERT INTO packages (app,count) VALUES (?,?) ON DUPLICATE KEY UPDATE count = count + ?"; 55 }else{ 56 $id=$this->dbconn->insert_id; 57 $query->close(); 58 return $id; 59 } 60 return false; 61 } 62 function update_data($data='',$version='',$sabor=''){ 63 $sql="INSERT INTO packages (app,count,version,sabor) VALUES (?,?,?,?) ON DUPLICATE KEY UPDATE count = count + ?"; 47 64 $query=$this->dbconn->prepare($sql); 48 $query->bind_param("sss ",$k,$v,$v);65 $query->bind_param("sssss",$k,$v,$version,$sabor,$v); 49 66 if (! $query) 50 67 throw new Exception($this->dbconn->error); … … 71 88 while($row=$result->fetch_row()){ 72 89 if(empty($row[0])){ 73 $sql="insert into historico(app,count,fecha ) values (?,?,?)";90 $sql="insert into historico(app,count,fecha,version,sabor) values (?,?,?,?,?)"; 74 91 $query=$this->dbconn->prepare($sql); 75 92 $name='dummy'; $value=0; $today=date("Y-m-d"); 76 $query->bind_param('sds ',$name,$value,$today);93 $query->bind_param('sdsss',$name,$value,$today,$name,$name); 77 94 $query->execute(); 78 95 $query->close(); … … 83 100 } 84 101 if ($thismonth > $lastmonth){ 85 $sql="insert into historico(app,count,fecha ) (select app,count,DATE(NOW()) as fechafrom packages);";102 $sql="insert into historico(app,count,fecha,version,sabor) (select app,count,DATE(NOW()) as fecha,version,sabor from packages);"; 86 103 $this->dbconn->query($sql); 87 104 $sql="truncate packages;"; … … 91 108 function get_historic_data(){ 92 109 $this->load_alias(); 93 $today=date("Y-m-d");94 $first_this_month=date("Y-m-").'01';95 $last_one_month_ago=date("Y-m-d",strtotime("$first_this_month -1 days"));96 $first_one_month_ago=date("Y-m-d",strtotime("$first_this_month -1 months"));97 $last_two_month_ago=date("Y-m-d",strtotime("$first_one_month_ago -1 days"));98 $first_two_month_ago=date("Y-m-d",strtotime("$first_one_month_ago -1 months"));99 110 //echo $today.' '.$first_this_month.' '.$first_one_month_ago.' '.$last_one_month_ago.' '.$first_two_month_ago.' '.$last_two_month_ago.' EOL'; 100 111 $obj=[]; 101 $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"; 112 113 foreach ($this->info_distro['distros'] as $distro){ 114 $dname=$distro['name']; 115 $dlike=$distro['like']; 116 $obj[$dname]=array(); 117 foreach ($distro['sabor'] as $sabor){ 118 $sname=$sabor['name']; 119 $slike=$sabor['like']; 120 $obj[$dname][$sname][]=$this->get_current_chart($dlike,$slike); 121 $obj[$dname][$sname][]=$this->get_old_chart($dlike,$slike); 122 $obj[$dname][$sname][]=$this->get_very_old_chart($dlike,$slike); 123 } 124 } 125 return json_encode($obj); 126 } 127 function get_current_chart($version='',$sabor=''){ 128 $date1=$this->dates['first_this_month']; 129 $date2=$this->dates['today']; 130 if ($version != '' and $sabor != ''){ 131 $where_a="where version $version and sabor $sabor"; 132 $where_b="and version $version and sabor $sabor"; 133 }else{ 134 $where_a=''; 135 $where_b=''; 136 } 137 $sql = "select app,sum(count) as count from (SELECT app,count from packages $where_a UNION ALL SELECT app,count from historico where fecha BETWEEN '$date1' and '$date2' $where_b) tabla group by app order by count DESC LIMIT 30"; 102 138 if ($result=$this->dbconn->query($sql)){ 103 139 $obj2=[]; … … 116 152 } 117 153 } 118 $obj[]=$obj2; 119 } 120 $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"; 154 return $obj2; 155 } 156 return false; 157 } 158 function get_old_chart($version='',$sabor=''){ 159 $date1=$this->dates['first_one_month_ago']; 160 $date2=$this->dates['last_one_month_ago']; 161 if ($version != '' and $sabor != ''){ 162 $where_a="and version $version and sabor $sabor"; 163 }else{ 164 $where_a=''; 165 } 166 $sql="SELECT app,count,fecha from historico where fecha BETWEEN '$date1' and '$date2' $where_a order by count DESC LIMIT 30"; 121 167 if ($result=$this->dbconn->query($sql)){ 122 168 $obj2=[]; … … 135 181 } 136 182 } 137 $obj[]=$obj2; 138 } 139 $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"; 183 return $obj2; 184 } 185 return false; 186 } 187 function get_very_old_chart($version='',$sabor=''){ 188 $date1=$this->dates['first_two_month_ago']; 189 $date2=$this->dates['last_two_month_ago']; 190 if ($version != '' and $sabor != ''){ 191 $where_a="and version $version and sabor $sabor"; 192 }else{ 193 $where_a=''; 194 } 195 $sql="SELECT app,count,fecha from historico where fecha BETWEEN '$date1' and '$date2' $where_a order by count DESC LIMIT 30"; 140 196 if ($result=$this->dbconn->query($sql)){ 141 197 $obj2=[]; … … 154 210 } 155 211 } 156 $obj[]=$obj2; 157 } 158 159 return json_encode($obj); 212 return $obj2; 213 } 214 return false; 160 215 161 216 } -
lliurex-analytics-server/trunk/fuentes/lliurex-analytics-server/usr/lib/analytics-server/analytics/functions.php
r350 r747 8 8 $db->init_trans(); 9 9 try{ 10 $db->insert_data($data['uid'],date("Y-m-d H:i:s")); 11 $db->update_data(json_decode($data['stats'],true)); 10 if (isset($data['vers'])) 11 $version=$data['vers']; 12 else 13 $version='Nada'; 14 if (isset($data['sab'])) 15 $sabor=$data['sab']; 16 else 17 $sabor='Nada'; 18 $id=$db->insert_data($data['uid'],date("Y-m-d H:i:s"),$version,$sabor); 19 $db->update_data(json_decode($data['stats'],true),$version,$sabor); 12 20 }catch (Exception $e){ 13 21 $db->dbconn->rollback(); … … 29 37 }; 30 38 } 39 40 31 41 function call_show_stats(){ 32 return function($request,$reponse,$service){33 42 34 echo "<html> 43 return function($request,$reponse,$service){ 44 45 echo "<html> 35 46 <head> 36 47 <!--Load the AJAX API--> … … 38 49 <script type='text/javascript' src='jquery.min.js'></script> 39 50 <script type='text/javascript' src='graph.js'></script> 51 <link href='ui/jquery-ui.css' rel='stylesheet'> 52 <script type='text/javascript' src='ui/jquery-ui.js'></script> 40 53 </head> 54 <body> 55 <div style='text-align:center; font-size:2em;'>Lliurex-Analytics</div><hr/> 56 <div id='accordion'></div> 57 </body> 58 </html> 59 "; 41 60 42 <body> 43 <!--Div that will hold the pie chart--> 44 <div id='chart_div1'></div> 45 <div id='chart_div2'></div> 46 <div id='chart_div3'></div> 47 </body> 48 </html>"; 61 }; 49 62 50 };51 63 } 52 64 -
lliurex-analytics-server/trunk/fuentes/lliurex-analytics-server/usr/lib/analytics-server/analytics/graph.js
r350 r747 5 5 // Set a callback to run when the Google Visualization API is loaded. 6 6 google.setOnLoadCallback(doChart); 7 7 8 8 } 9 9 10 10 function drawChart(datos,title,id) { 11 var custom_width=$('#'+id).parent().width()*0.9; 12 var custom_height=$(window).height()/3*0.75; 13 11 14 // Set chart options 12 15 var options = {'title':title, 13 'width':1024, 14 'height':300}; 16 'width': custom_width, 17 'height':custom_height, 18 'fontSize': 10, 19 'hAxis': { 20 viewWindow:{ 21 min:0 22 } 23 } 24 }; 25 15 26 16 27 // Instantiate and draw our chart, passing in some options. 28 var view = new google.visualization.DataView(datos); 29 view.setColumns([0, 1, 30 { calc: "stringify", 31 sourceColumn: 1, 32 type: "string", 33 role: "annotation" }]); 17 34 var chart = new google.visualization.BarChart(document.getElementById(id)); 18 chart.draw(datos, options); 35 google.visualization.events.addListener(chart, 'ready', ready_charts); 36 chart.draw(view, options); 19 37 } 20 38 … … 37 55 //Gets Data from DB 38 56 $.getJSON( "./GetStats", function( json ) { 39 // Log each key in the response data 40 $.each( json[0], function( key, value ) { 41 //console.log( '0 ->'+key + " : " + value ); 42 datos1.addRow([key,parseInt(value)]); 43 }); 44 while (datos1.Gf.length < 10) 45 datos1.addRow(['',0]) 46 $.each( json[1], function( key, value ) { 47 //console.log( '1 ->'+key + " : " + value ); 48 datos2.addRow([key,parseInt(value)]); 49 }); 50 while (datos2.Gf.length < 10) 51 datos2.addRow(['',0]) 52 $.each( json[2], function( key, value ) { 53 //console.log( '2 ->'+key + " : " + value ); 54 datos3.addRow([key,parseInt(value)]); 55 }); 56 while (datos3.Gf.length < 10) 57 datos3.addRow(['',0]); 58 drawChart(datos1,'Top apps este mes','chart_div1'); 59 drawChart(datos2,'Top apps ultimo mes','chart_div2'); 60 drawChart(datos3,'Top apps penultimo mes','chart_div3'); 57 // j=json; 58 count=0; 59 for (key in json) 60 for (key2 in json[key]) 61 count++; 62 max_graph=count*3; 63 obj=[]; 64 for (distro in json){ 65 titles=['Top apps este mes','Top apps ultimo mes','Top apps penultimo mes']; 66 for (sabor in json[distro]){ 67 divname='chart_'+distro+'_'+sabor; 68 $('#accordion').append('<h3>Distro '+distro+'('+sabor+')</h3>'); 69 $('#accordion').append('<div class="'+divname+'"></h3>'); 70 for (i=0; i<3; i++){ 71 $('div.'+divname).append('<div id="'+divname+i+'"></div>'); 72 //k=0; 73 var datos=new google.visualization.DataTable(); 74 datos.addColumn('string','App'); 75 datos.addColumn('number','Count'); 76 $.each(json[distro][sabor][i],function(key,value){ 77 //console.log( i+':'+k+'->'+key + " : " + value ); 78 datos.addRow([key,parseInt(value)]); 79 //k++; 80 }) 81 while (datos.Gf.length < 10) 82 datos.addRow(['',0]) 83 obj.push(datos); 84 //console.log('drawing:'+titles[i]+' into '+divname+i); 85 drawChart(obj[obj.length-1],titles[i],divname+i); 86 } 87 } 88 } 89 return; 61 90 }); 62 91 92 } 93 num_ready=0; 94 function ready_charts(){ 95 num_ready++; 96 if (num_ready != max_graph){ 97 //console.log('not now') 98 //console.log('ready '+num_ready); 99 }else{ 100 //console.log('now ready'); 101 $('#accordion').accordion({active:false,collapsible:true}); 102 } 63 103 } 64 104
Note: See TracChangeset
for help on using the changeset viewer.