+ Improves:

	+ Add query script to get most activity used by schoool
wq

4 files changed, 110 insertions, 25 deletions

diff --git a/sql/make_report b/sql/make_report
index 81db04b..904ec4b 100755
--- a/sql/make_report
+++ b/sql/make_report
@@ -29,6 +29,8 @@ parser.add_argument('--db_name',required=True)
 parser.add_argument('--db_user',required=True)
 parser.add_argument('--db_pass',required=True)
 parser.add_argument('--activity',required=False)
+parser.add_argument('--school',required=False)
+parser.add_argument('--log_level',required=False)
 
 args = parser.parse_args()
 
@@ -37,23 +39,35 @@ _LOG_FORMAT='[%(asctime)s]-%(levelname)s-\'%(name)s\': %(message)s'
 """
 _LOG_FORMAT='%(message)s'
 
+if args.log_level == 'debug':
+	log_level = logging.DEBUG
+else:
+	log_level = logging.INFO
+
 
 log = logging.getLogger(__name__)
-log.setLevel(logging.DEBUG)
+log.setLevel(log_level)
 ch = logging.StreamHandler()
 ch.setFormatter(logging.Formatter(_LOG_FORMAT))
 log.addHandler(ch)
 
 logging.getLogger('sugar_stats_consolidation.db').addHandler(ch)
+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):
-	activity = re.rep_most_activity_used(start, end);
-	log.debug("**************************************************************************************************************")
-	log.debug("                                     Most Activity used                                                       ")
-	log.debug('NAME:     %s', activity[0])
-	log.debug('INTERVAL: %s-> %s', start, end)
-	log.debug('TIME:     %.2f hrs / %.2f mins / %.2f secs', (activity[1]/60/60), (activity[1]/60), activity[1])
-	log.debug("***************************************************************************************************************")
+def activity_most_used(re, start, end, school=None):
+	if school==None:
+		activity = re.rep_most_activity_used(start, end);
+	else:
+		activity = re.rep_get_most_activity_used_by_school(school, start,end)
+	log.info("**************************************************************************************************************")
+	log.info("                                     Most Activity used                                                       ")
+	log.info('NAME:     %s', activity[0])
+	log.info('SCHOOL:   %s', school)
+	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])
+	log.info("***************************************************************************************************************")
 
 def frequency_usage(re, start, end):
 	ts_end   = long (datetime.strptime(end, "%Y-%m-%d").strftime("%s"))
@@ -62,25 +76,25 @@ def frequency_usage(re, start, end):
 	fres = re.rep_frequency_usage(start, end);
 	f_x_day = (fres / ((ts_end - ts_start)/(60*60*24)))/60
 	f_total = (fres / 60)
-	log.debug("**************************************************************************************************************")
-	log.debug("                                     Fequency Usage                                                           ")
-	log.debug('INTERVAL: %s-> %s', start, end)
-	log.debug('PER DAY:  %.2f min / day', (f_x_day))
-	log.debug('TOTAL:    %.2f min', (f_total))
-	log.debug("**************************************************************************************************************")
+	log.info("**************************************************************************************************************")
+	log.info("                                     Fequency Usage                                                           ")
+	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("**************************************************************************************************************")
 
 def activity_time (re, start, end, activity):
 	ts_end   = long (datetime.strptime(end, "%Y-%m-%d").strftime("%s"))
 	ts_start = long (datetime.strptime(start, "%Y-%m-%d").strftime("%s"))
 
 	time = re.rep_activity_time (start, end, activity);
-	log.debug("**************************************************************************************************************")
-	log.debug("                                     Activity Time                                                            ")  
-	log.debug('NAME:     %s', activity)
-	log.debug('INTERVAL: %s-> %s', start, end)
-	log.debug('UPTIME:   %s secs', time[0])
-	log.debug('FOCUS     %s secs', time[1])
-	log.debug("**************************************************************************************************************")
+	log.info("**************************************************************************************************************")
+	log.info("                                     Activity Time                                                            ")  
+	log.info('NAME:     %s', activity)
+	log.info('INTERVAL: %s-> %s', start, end)
+	log.info('UPTIME:   %s secs', time[0])
+	log.info('FOCUS     %s secs', time[1])
+	log.info("**************************************************************************************************************")
 
 
 
@@ -91,7 +105,7 @@ def main():
 	if args.query == 'activity_time':
 		activity_time(re, args.start_date, args.end_date, args.activity)
 	elif args.query == 'activity_most_used':
-		activity_most_used(re, args.start_date, args.end_date);
+		activity_most_used(re, args.start_date, args.end_date, args.school);
 	elif args.query == 'frequency_usage':
 		frequency_usage(re, args.start_date, args.end_date)
 	elif args.query == 'all':
diff --git a/sql/rep_activity_most_used.sh b/sql/rep_activity_most_used.sh
index aa0a2aa..34cefc9 100755
--- a/sql/rep_activity_most_used.sh
+++ b/sql/rep_activity_most_used.sh
@@ -8,7 +8,8 @@ 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
+./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_school.sh b/sql/rep_activity_most_used_by_school.sh
new file mode 100755
index 0000000..1e6a66b
--- /dev/null
+++ b/sql/rep_activity_most_used_by_school.sh
@@ -0,0 +1,14 @@
+#!/bin/bash
+DB_NAME=statistics
+DB_USER=root
+DB_PASS=gustavo
+
+QUERY=activity_most_used
+SCHOOL=Ceibal
+START_DATE=2013-02-10
+END_DATE=2013-02-14
+
+
+
+./make_report --query $QUERY --school $SCHOOL --start_date $START_DATE --end_date $END_DATE --db_name $DB_NAME --db_user $DB_USER --db_pass $DB_PASS
+
diff --git a/sugar_stats_consolidation/db.py b/sugar_stats_consolidation/db.py
index e1f4cd6..26b8e50 100644
--- a/sugar_stats_consolidation/db.py
+++ b/sugar_stats_consolidation/db.py
@@ -268,8 +268,61 @@ class DB_Stats:
 			log.error('MySQL on rep_activity_time %s', err)
 		except Exception as e:
                         log.error('MySQL on rep_activity_time : %s', e)
-		return (None, None)	
+		return (None, None)
+
 	
+	def is_an_activity(self, name):
+		if (name != 'system') and (name != 'journal') and (name != 'network') and (name != 'shell'):
+			return True
+		else:	
+			return False
+
+	def rep_get_most_activity_used_by_school (self, school, start, end):
+		activity_name=''
+		focus_last = 0
+		try:
+			log.debug('Most activiy used by school: %s', school)
+			cursor1 = self.cnx.cursor()
+			cursor2 = self.cnx.cursor()
+			cursor3 = self.cnx.cursor()
+			""" Get user hash from a School"""
+			cursor1.execute ("SELECT hash FROM Users WHERE school = %s", (school,))
+			user_hashes = cursor1.fetchall()
+			
+			""" Get valid activities """
+			cursor2.execute("SELECT name FROM Resources")
+			resources = cursor2.fetchall()
+			
+			""" 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') AND (user_hash = %s)"
+
+			ts_start = datetime.strptime(start, "%Y-%m-%d")
+			ts_end   = datetime.strptime(end, "%Y-%m-%d")
+
+
+			for resource in resources:
+				log.debug('Resource: %s', resource[0])
+				if self.is_an_activity (resource[0]):
+					for user_hash in user_hashes:
+						log.debug('user Hash: %s', user_hash[0])
+						cursor3.execute(select_usage, (resource[0], ts_start, ts_end, user_hash[0]))
+						focus = cursor3.fetchone()
+						log.debug('Focus time: %s', )
+						if focus[0] > focus_last:
+							focus_last = focus[0]
+							activity_name = resource[0]
+		except  mysql.connector.Error as err:
+                        log.error('MySQL on most_activity_used_by_school %s', err)
+                except Exception as e:
+                        log.error('most_activity_used_by_school Fail: %s', e)
+                cursor1.close()
+                cursor2.close()
+                cursor3.close()
+		return (activity_name, focus_last)
+
+					
+		
+
 	def rep_most_activity_used (self, start, end):
 		uptime_last=0
 		activity_name=''
@@ -302,6 +355,9 @@ class DB_Stats:
                 cursor2.close()
 		return (activity_name, uptime_last)
 
+
+
+
 	def rep_frequency_usage(self, start, end):
 		cursor = self.cnx.cursor()
                 try: