Improves:

	+ Add queries script to get report by desktops and list the activities most used.

8 files changed, 170 insertions, 32 deletions

diff --git a/sql/make_report b/sql/make_report
index 329f5c6..63460c4 100755
--- a/sql/make_report
+++ b/sql/make_report
@@ -32,6 +32,8 @@ parser.add_argument('--activity',required=False)
 parser.add_argument('--school',required=False)
 parser.add_argument('--log_level',required=False)
 parser.add_argument('--machine_sn',required=False)
+parser.add_argument('--cant_max',required=False)
+parser.add_argument('--desktop',required=False)
 
 args = parser.parse_args()
 
@@ -57,19 +59,64 @@ logging.getLogger('sugar_stats_consolidation.db').setLevel(log_level)
 logging.getLogger('sugar_stats_consolidation.rrd_files').addHandler(ch)
 logging.getLogger('sugar_stats_consolidation.rrd_files').setLevel(log_level)
 
-def activity_most_used(re, start, end, school=None):
-	activity = re.rep_get_most_activity_used(start, end, school)
+
+def desktops (re, start, end, school=None, desktop='any'):
+	activity = re.rep_get_activities(start, end, school, desktop)
+	
+	log.info("\n\n")
 	log.info("**************************************************************************************************************")
-	log.info("                                     Most Activity used                                                       ")
-	log.info('NAME:     %s', activity[0])
+	log.info("                                     Most Activity/ies used                                                       ")
 	log.info('SCHOOL:   %s', school)
+	log.info('DESKTOP:  %s', desktop)
 	log.info('INTERVAL: %s-> %s', start, end)
-	log.info('TIME:     %.2f hrs / %.2f mins / %.2f secs', (activity[1]/60/60), (activity[1]/60), activity[1])
+	if len(activity) > 0:
+		log.info("")
+		i = 0
+		total = 0
+		while i < len (activity):
+			total = activity[i][1] + total
+			i=i+1
+			
+
+		log.info('TOTAL: %.1f hrs / %.2f mins / %.2f secs', (total/60/60), (total/60), total)
+	else:
+		log.info("No activity found for desktop %s!!", desktop)	
 	log.info("***************************************************************************************************************")
+	log.info("\n\n")
+
+
+
+
+def activity_most_used(re, start, end, school=None, cant_max=None, desktop='any'):
+	activity = re.rep_get_activities(start, end, school, desktop)
+
+	log.info("\n\n")
+	log.info("**************************************************************************************************************")
+	log.info("                                     Most Activity/ies used                                                       ")
+	log.info('SCHOOL:   %s', school)
+	log.info('DESKTOP:  %s', desktop)
+	log.info('INTERVAL: %s-> %s', start, end)
+	if (cant_max == None) and (len(activity) > 0):
+		log.info('NAME:     %s', activity[0][0])
+		log.info('TIME:     %.1f hrs / %.2f mins / %.2f secs', (activity[0][1]/60/60), (activity[0][1]/60), activity[0][1])
+	elif (cant_max != None) and (len(activity) > 0):
+		log.info("")
+		i = 0
+		while (i < len (activity)) and (i < int(cant_max)):
+			log.info('%s) %s: %.1f hrs / %.2f mins / %.2f secs', 
+									i+1,
+									activity[i][0], 
+									(activity[i][1]/60/60), 
+									(activity[i][1]/60), 
+									activity[i][1])
+			i=i+1
+	else:
+		log.info("No activity found !!")	
+	log.info("***************************************************************************************************************")
+	log.info("\n\n")
 
 def frequency_usage(re, start, end, school=None):
-	fres = re.rep_frequency_usage(start, end, school);
-	
+	(fres, xos) = re.rep_frequency_usage(start, end, school);
 
 	ts_end   = long (datetime.strptime(end, "%Y-%m-%d").strftime("%s"))
 	ts_start = long (datetime.strptime(start, "%Y-%m-%d").strftime("%s"))
@@ -77,13 +124,21 @@ def frequency_usage(re, start, end, school=None):
 
 	f_x_day = (fres / ((ts_end - ts_start)/(60*60*24)))/60
 	f_total = (fres / 60)
+	if xos != 0:
+		average = f_total / xos
+	else:
+		average = 0
+	log.info("\n\n")
 	log.info("**************************************************************************************************************")
 	log.info("                                     Fequency Usage                                                           ")
-	log.info('SCHOOL:   %s', school)
-	log.info('INTERVAL: %s-> %s', start, end)
-	log.info('PER DAY:  %.2f min / day', (f_x_day))
-	log.info('TOTAL:    %.2f min', (f_total))
+	log.info('SCHOOL:        %s', school)
+	log.info('INTERVAL:      %s-> %s', start, end)
+	log.info('NUMBER OF XOs: %s', xos)
+	log.info('PER DAY:       %.2f min/day', (f_x_day))
+	log.info('TOTAL:         %.2f min', (f_total))
+	log.info('AVERAGE:       %.2f min/XOs', average)
 	log.info("**************************************************************************************************************")
+	log.info("\n\n")
 
 def activity_time (re, start, end, activity, school=None):
 	time = re.rep_activity_time (start, end, activity, school);
@@ -92,6 +147,7 @@ def activity_time (re, start, end, activity, school=None):
 	ts_end   = long (datetime.strptime(end, "%Y-%m-%d").strftime("%s"))
 	ts_start = long (datetime.strptime(start, "%Y-%m-%d").strftime("%s"))
 
+	log.info("\n\n")
 	log.info("**************************************************************************************************************")
 	log.info("                                     Activity Time                                                            ")  
 	log.info('NAME:     %s', activity)
@@ -100,15 +156,18 @@ def activity_time (re, start, end, activity, school=None):
 	log.info('UPTIME:   %s secs', time[0])
 	log.info('FOCUS     %s secs', time[1])
 	log.info("**************************************************************************************************************")
+	log.info("\n\n")
 
 def update_school  (re, machine_sn, school):
 	re.rep_update_school(machine_sn, school);
 	
+	log.info("\n\n")
 	log.info("**************************************************************************************************************")
 	log.info("                                     Set School name to User                                                  ")  
 	log.info('SCHOOL:     %s', school)
 	log.info('MACHINE_SN: %s', machine_sn)
 	log.info("**************************************************************************************************************")
+	log.info("\n\n")
 
 
 def main():
@@ -117,8 +176,10 @@ def main():
 	
 	if args.query == 'activity_time':
 		activity_time(re, args.start_date, args.end_date, args.activity, args.school)
+	elif args.query == 'desktops':
+		desktops (re, args.start_date, args.end_date, args.school,args.desktop);
 	elif args.query == 'activity_most_used':
-		activity_most_used(re, args.start_date, args.end_date, args.school);
+		activity_most_used(re, args.start_date, args.end_date, args.school, args.cant_max, args.desktop);
 	elif args.query == 'frequency_usage':
 		frequency_usage(re, args.start_date, args.end_date, args.school)
 	elif args.query == 'update_school':
diff --git a/sql/rep_activity_list.sh b/sql/rep_activity_list.sh
new file mode 100755
index 0000000..34cefc9
--- /dev/null
+++ b/sql/rep_activity_list.sh
@@ -0,0 +1,15 @@
+#!/bin/bash
+DB_NAME=statistics
+DB_USER=root
+DB_PASS=gustavo
+
+QUERY=activity_most_used
+
+START_DATE=2013-02-10
+END_DATE=2013-02-14
+
+LOG_LEVEL=info
+
+
+./make_report --query $QUERY --start_date $START_DATE --end_date $END_DATE --db_name $DB_NAME --db_user $DB_USER --db_pass $DB_PASS --log_level $LOG_LEVEL
+
diff --git a/sql/rep_activity_list_by_desktop.sh b/sql/rep_activity_list_by_desktop.sh
new file mode 100755
index 0000000..7d143ee
--- /dev/null
+++ b/sql/rep_activity_list_by_desktop.sh
@@ -0,0 +1,18 @@
+#!/bin/bash
+DB_NAME=statistics
+DB_USER=root
+DB_PASS=gustavo
+
+QUERY=activity_most_used
+CANT_MAX=10
+DESKTOP=any
+START_DATE=2013-02-10
+END_DATE=2013-02-14
+
+
+
+LOG_LEVEL=info
+
+
+./make_report --query $QUERY --desktop $DESKTOP --cant_max $CANT_MAX --start_date $START_DATE --end_date $END_DATE --db_name $DB_NAME --db_user $DB_USER --db_pass $DB_PASS --log_level $LOG_LEVEL
+
diff --git a/sql/rep_activity_most_used.sh b/sql/rep_activity_most_used.sh
index 5014802..34cefc9 100755
--- a/sql/rep_activity_most_used.sh
+++ b/sql/rep_activity_most_used.sh
@@ -8,7 +8,7 @@ QUERY=activity_most_used
 START_DATE=2013-02-10
 END_DATE=2013-02-14
 
-LOG_LEVEL=debug
+LOG_LEVEL=info
 
 
 ./make_report --query $QUERY --start_date $START_DATE --end_date $END_DATE --db_name $DB_NAME --db_user $DB_USER --db_pass $DB_PASS --log_level $LOG_LEVEL
diff --git a/sql/rep_activity_most_used_by_desktop.sh b/sql/rep_activity_most_used_by_desktop.sh
new file mode 100755
index 0000000..bf337c6
--- /dev/null
+++ b/sql/rep_activity_most_used_by_desktop.sh
@@ -0,0 +1,16 @@
+#!/bin/bash
+DB_NAME=statistics
+DB_USER=root
+DB_PASS=gustavo
+
+QUERY=activity_most_used
+DESKTOP=sugar
+
+START_DATE=2013-02-10
+END_DATE=2013-02-14
+
+LOG_LEVEL=info
+
+
+./make_report --query $QUERY --desktop $DESKTOP --start_date $START_DATE --end_date $END_DATE --db_name $DB_NAME --db_user $DB_USER --db_pass $DB_PASS --log_level $LOG_LEVEL
+
diff --git a/sql/rep_activity_most_used_by_school.sh b/sql/rep_activity_most_used_by_school.sh
index 6bfc471..150b139 100755
--- a/sql/rep_activity_most_used_by_school.sh
+++ b/sql/rep_activity_most_used_by_school.sh
@@ -4,11 +4,11 @@ DB_USER=root
 DB_PASS=gustavo
 
 QUERY=activity_most_used
-SCHOOL=Ceibal
+SCHOOL=activitycentral
 START_DATE=2013-02-10
 END_DATE=2013-02-14
 
-LOG_LEVEL=debug
+LOG_LEVEL=info
 
 
 
diff --git a/sql/rep_desktops.sh b/sql/rep_desktops.sh
new file mode 100755
index 0000000..0640c4e
--- /dev/null
+++ b/sql/rep_desktops.sh
@@ -0,0 +1,17 @@
+#!/bin/bash
+DB_NAME=statistics
+DB_USER=root
+DB_PASS=gustavo
+
+QUERY=desktops
+DESKTOP=gnome
+START_DATE=2013-02-10
+END_DATE=2013-02-14
+
+
+s
+LOG_LEVEL=info
+
+
+./make_report --query $QUERY --desktop $DESKTOP --start_date $START_DATE --end_date $END_DATE --db_name $DB_NAME --db_user $DB_USER --db_pass $DB_PASS --log_level $LOG_LEVEL
+
diff --git a/sugar_stats_consolidation/db.py b/sugar_stats_consolidation/db.py
index b8cd8b2..647f69c 100644
--- a/sugar_stats_consolidation/db.py
+++ b/sugar_stats_consolidation/db.py
@@ -305,14 +305,23 @@ class DB_Stats:
                         log.error('MySQL on rep_activity_time : %s', e)
 		return (None, None)
 
-	
 
-	def rep_get_most_activity_used (self, start, end, school=None):
-		activity_name=''
-		focus_last = 0
+
+	def rep_get_activities (self, start, end, school=None, desktop='any'):
+		res_list = list();
 		cursor1 = self.cnx.cursor()
 		cursor2 = self.cnx.cursor()
 		cursor3 = self.cnx.cursor()
+		
+		if desktop == 'gnome':
+			cursor2.execute("SELECT name FROM Resources WHERE name REGEXP 'application'")
+		elif desktop == 'sugar':
+			cursor2.execute("SELECT name FROM Resources WHERE name REGEXP 'activity'")
+		else:	
+			cursor2.execute("SELECT name FROM Resources")
+		
+		resources = cursor2.fetchall()
+			 
 		try:
 			if school != None:
 				log.debug('Most activiy used by school: %s', school)
@@ -324,11 +333,8 @@ class DB_Stats:
 			else:
 				log.debug('Most activiy used')
 				""" Cursor for select resources from Uages table"""
-				select_usage = "SELECT SUM(data) FROM Usages WHERE (resource_name = %s) AND (start_date > %s) AND (start_date < %s) AND (data_type = 'active')"
+				select_usage = "SELECT SUM(data) FROM Usages WHERE (resource_name = %s) AND (start_date > %s) AND (start_date < %s) AND (data_type = 'active')" 
 				
-			""" Get valid activities """
-			cursor2.execute("SELECT name FROM Resources")
-			resources = cursor2.fetchall()
 			
 
 			ts_start = self.date_to_ts(start)
@@ -343,17 +349,16 @@ class DB_Stats:
 							log.debug('user Hash: %s', user_hash[0])
 							cursor3.execute(select_usage, (resource[0], ts_start, ts_end, user_hash[0]))
 							focus = cursor3.fetchone()[0]
+							if focus == None: focus = 0
+
 							log.debug('Focus time: %s', focus)
-							if focus > focus_last:
-								focus_last = focus
-								activity_name = resource[0]
+							res_list.append((resource[0], focus))
 					else:
 						cursor3.execute(select_usage, (resource[0], ts_start, ts_end))
 						focus = cursor3.fetchone()[0]
+						if focus == None: focus = 0
 						log.debug('Focus time: %s', focus )
-						if focus > focus_last:
-							focus_last = focus
-							activity_name = resource[0]
+						res_list.append((resource[0], focus))
 						
 		except  mysql.connector.Error as err:
                         log.error('MySQL on most_activity_used %s', err)
@@ -362,13 +367,15 @@ class DB_Stats:
                 cursor1.close()
                 cursor2.close()
                 cursor3.close()
-		return (activity_name, focus_last)
+		log.debug ('Activities: %s', sorted(res_list, key=lambda x: x[1], reverse=True))
+		return sorted(res_list, key=lambda x: x[1], reverse=True)		
 
 					
 
 	def rep_frequency_usage (self, start, end, school=None):
 		cursor1 = self.cnx.cursor()
 		cursor2 = self.cnx.cursor()
+		user_hashes=()
                 time = 0
 		try:
 			ts_start = self.date_to_ts(start)
@@ -387,10 +394,12 @@ class DB_Stats:
 						time = float (res[0]) + time 
 			else:
 				log.debug('Frequency usage')
+				cursor1.execute ("SELECT hash FROM Users")
+				user_hashes = cursor1.fetchall()
 				cursor2.execute("SELECT SUM(data) FROM Usages WHERE (resource_name = 'system') AND (start_date > %s) AND (start_date < %s) AND (data_type = 'uptime')", (ts_start, ts_end))
 				time = cursor2.fetchone()[0]
 		
-			return time
+			return (time, len(user_hashes))
 				
 
 		except mysql.connector.Error as err:
@@ -420,4 +429,6 @@ class DB_Stats:
 
 	def date_to_ts(self, date):
 		return datetime.strptime(date, "%Y-%m-%d")
-	
+
+
+