From f03f28d6e4422d303a61b1cf68facbbd9ffea5d9 Mon Sep 17 00:00:00 2001 From: ceibal Date: Mon, 18 Feb 2013 15:02:56 +0000 Subject: + Improves: + Add query script to get most activity used by schoool wq --- 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: -- cgit v0.9.1