# -*- coding: utf-8
# $Id$
#
# Copyright 2001, 2002 by IVA Team and contributors
#
# This file is part of IVA.
#
# IVA is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# IVA is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with IVA; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA

""" Module collects statistics from all parts of IVA. """

__version__ = "$Revision$"[11:-2]

import time, datetime
import os, Globals
from OFS.SimpleItem import SimpleItem
from OFS.PropertyManager import PropertyManager
from Globals import Persistent, PersistentMapping
from Products.PageTemplates.PageTemplateFile import PageTemplateFile
import AccessControl
from common import perm_view,perm_manage, perm_edit
import Acquisition

CONN_ID = 'iva_mysql_connection'
from Products.ZSQLMethods.SQL import SQL

from zope.interface import implements
from interfaces import IStatistics
from zope.component import adapter
from zope.app.container.interfaces import IObjectAddedEvent

class StatisticsManager(PropertyManager, SimpleItem):
    """ store and serve statistics. Storing should be through session expiring """

    security = AccessControl.ClassSecurityInfo()
    security.declareObjectPublic()
    
    implements(IStatistics)
    meta_type = 'Statistics'

    _version = 1

    security.declareProtected(perm_manage, 'stats_tab')
    stats_tab = PageTemplateFile('ui/stats.zpt',globals())
    stats_tab._owner = None

    manage_options = (
        {'label': 'Stats', 'action': 'stats_tab'},
        ) + PropertyManager.manage_options+ SimpleItem.manage_options

    def __init__(self):
        """
        ug_stat: username -> pagesOpenTotal - n
                             sessions : [{'pagesOpened', 'start', 'end', 'urls'},]
                             all_urls : [[url, time.time()],]
                             lastAccessGlobal - time.time()
        c_stat: course_id -> (modBookshelf, modKnowledge, .. )
        o_stat: url_to_object -> {'uname': {'count', 'times': time.time()}
        uc_stat: username -> {course_id: {postedArtefactAnnos, openPages, numTimesEntered}}
        """
        self.id = 'Statistics'
        self.ug_stat = {} # not course spec. user stats
        self.uc_stat = {} # course spec. user stats
        self.c_stat = {} # course stats
        self.o_stat = {} # object views

    def _makeSQLMethods(self, prefix='iva'):
        if not hasattr(self, CONN_ID):
            sm = DummySQLMethods()
        else:
            sm = IVASQLMethods(prefix=prefix)
        try:
            self.aq_parent._delObject('iva_sql_methods')
        except AttributeError:
            pass
        if not hasattr(self, 'iva_sql_methods'):
            self.aq_parent._setObject('iva_sql_methods', sm)
        try:
            self.iva_sql_methods._after_init()
            self.iva_sql_methods._sql_create_tables()
            cl = self.courses.objectIds('Course')
            cls = ''
            for x in cl: cls = cls+'('+str(x)+'),'
            cls = cls[:-1]
            if cls:
                self.iva_sql_methods._sql_populate_course_table(course_list=str(cls))
        except AttributeError:
            pass

    security.declareProtected(perm_view, 'active_users')
    def active_users(self):
        """ list of user who has session. """
        if isinstance(self.Statistics.iva_sql_methods, DummySQLMethods):
            return []
        res = self.iva_sql_methods._sql_get_active_users()
        result = []
        for x in res:
            if x is None: 
                break
            result.append(x.UNAME)
        return result
        
    security.declarePublic('index_html')
    def index_html(self):
        """ index """
        return "no index here"

    security.declareProtected(perm_view, 'get_showAllViews')
    def get_showAllViews(self):
        """ return showAllViews """
        return self.showAllViews

    security.declareProtected(perm_manage, 'get_trackSessions')
    def get_trackSessions(self):
        """ return trackSessions """
        return self.trackSessions

    security.declareProtected(perm_manage, 'get_storeAllURLs')
    def get_storeAllURLs(self):
        """ return storeAllURLs """
        return self.storeAllURLs

    security.declareProtected(perm_manage, 'getAllURLs')
    def getAllURLs(self, uname):
        """ return all URLs """
        if isinstance(self.Statistics.iva_sql_methods, DummySQLMethods):
            return []
        return [ x.URL for x in self.iva_sql_methods._sql_get_users_urls(uname=uname) ]

    security.declarePrivate('delObjectHistory')
    def delObjectHistory(self, url):
        """ delete object history. This is triggered when object is deleted """
        if isinstance(self.Statistics.iva_sql_methods, DummySQLMethods):
            return 
        self.iva_sql_methods._sql_del_object_history(url=url)

    security.declareProtected(perm_manage, 'getLastVisitTime')
    def getLastVisitTime(self, REQUEST, url, uname):
        """ url - relative url 
        """
        if not hasattr(self.Statistics, 'iva_sql_methods'):
            return 0
        if isinstance(self.Statistics.iva_sql_methods, DummySQLMethods):
            return 0
        tim = 0
        res = self.iva_sql_methods._sql_get_last_obj_access(url=url, uname=uname)[0]
        if res is not None and res.TIME is not None:
            tim = time.mktime(res.TIME.utctimetuple())
        return tim

    security.declareProtected(perm_view, 'getObjVisitors')
    def getObjVisitors(self, url):
        """ return visitors for object """
        if isinstance(self.Statistics.iva_sql_methods, DummySQLMethods):
            return {}
        sqlres = self.iva_sql_methods._sql_get_object_history(url=url)
        res = {}
        for x in sqlres:
            if not res.has_key(x.UNAME):
                res[x.UNAME] = {'times': [x.TIME,]}
            else:
                res[x.UNAME]['times'].append(x.TIME)
        return res

    security.declareProtected(perm_manage, 'getUserCourseStat')
    def getUserCourseStat(self, course, stat, uname):
        """ return stat for user on given course """
        if isinstance(self.Statistics.iva_sql_methods, DummySQLMethods):
            return None
        try:
            res = self.iva_sql_methods._sql_get_user_course_stat(uname=uname, stat=stat, course_id=course)[0]
        except IndexError:
            return None
        if res is None: return None
        if isinstance(res[stat], type(datetime.datetime(1,2,3))):
            return time.mktime(res[stat].utctimetuple())
        return res[stat]

    security.declareProtected(perm_manage, 'getCourseStat')
    def getCourseStat(self, course, stat):
        """ return stat for course """
        if isinstance(self.Statistics.iva_sql_methods, DummySQLMethods):
            return None
        res = self.iva_sql_methods._sql_get_course_updates(course=str(course), stat=stat)
        if res[0] is None: return None
        return res[0][stat]

    def _delCourseHistory(self, course):
        """ delete course stats. triggered from manage_beforeDelete """
        if isinstance(self.Statistics.iva_sql_methods, DummySQLMethods):
            return None
        self.iva_sql_methods._sql_del_course(course=str(course))

    security.declareProtected(perm_manage, 'getUserGlobalStat')
    def getUserGlobalStat(self, uname, stat):
        """ return stat for user """
        if isinstance(self.Statistics.iva_sql_methods, DummySQLMethods):
            return 0
        if stat == 'lastAccessGlobal':
            stat = 'lastAccess'
        res = self.iva_sql_methods._sql_get_user_global_stat(uname=uname, stat=stat)
        if len(res)==0:
            return 0
        return res[0][stat]

    # registering methods

    def _registerVisit(self, REQUEST, course_id, userLocation):
        """ registers visit, page open, note reading etc. """

        #print "STATS: registering visit:", course_id, userLocation, type(REQUEST)
        sess_id = str(REQUEST.SESSION.getId())
        uname = str(REQUEST.AUTHENTICATED_USER)

        if not hasattr(self, 'iva_sql_methods'):
            return 0

        if isinstance(self.iva_sql_methods, DummySQLMethods):
            return 0
        self.iva_sql_methods._sql_update_user_stat(uname=uname)

        store_all = self.get_storeAllURLs()
        if store_all:
                s_url = REQUEST.get('URL0', 'URL POLE')
                q_string = REQUEST.get('QUERY_STRING', '')
                if q_string: s_url += '?'+q_string
                self.iva_sql_methods._sql_add_visited_url(uname=uname, url=s_url, sess_id=sess_id)
        course_id = int(course_id)
        if course_id and userLocation!='Organizer':
                res = self.iva_sql_methods._sql_update_course_user_access(uname=uname, course_id=str(course_id));
                if res[0] is None: return None
                if res[0]['rows'] == 0:
                    self.iva_sql_methods._sql_insert_course_user_access(uname=uname, course_id=str(course_id))
        # print "Statistics.py", "all done"
        return 0

    def _updateStat(self, REQUEST, field, course_id=None):
        """ updates one statistics field. course specific only. """
        if not hasattr(self, 'iva_sql_methods'):
            return 0
        if isinstance(self.iva_sql_methods, DummySQLMethods):
            return 0
        uname = str(REQUEST.AUTHENTICATED_USER)
        if course_id is None:
            user = getattr(self.fle_root().fle_users, uname, None)
            #XXX: maybe we should check user here too ?
            course_id = int(user.get_jooksev_kursus())
        if field in ('modified_jamming', 'modified_knowledgebuilding', 'modified_bookshelf'):
            # update table: _course_updates
            #update time
            res = self.iva_sql_methods._sql_update_course_modified(field=field, course_id=course_id)
            if res[0] is None: return None
            if res[0]['rows']>0:
                pass
        elif field in ('visit_jamming', 'visit_knowledgebuilding'):
            # update table: _course_user
            self.iva_sql_methods._sql_update_course_user_visit(field=field, course_id=course_id, uname=uname)
        else:
            # update table: _course_user
            self.iva_sql_methods._sql_update_course_user_stat(field=field, course_id=course_id, uname=uname)
        return 1

    def _viewedObject(self, REQUEST, url):
        """ user is viewing object. url is basically an identifier, bare in mind that url might change
        """
        if isinstance(self.iva_sql_methods, DummySQLMethods):
            return 0
        res = self.iva_sql_methods._sql_add_obj_history(url=url, uname=str(REQUEST.AUTHENTICATED_USER))
        return 1

Globals.InitializeClass(StatisticsManager)

class IVASQLMethods(SimpleItem, Persistent, Acquisition.Implicit):
    """ class containing all sql methods """

    security = AccessControl.ClassSecurityInfo()

    security.declarePublic('prefix')
    def prefix(self):
        return self._prefix

    security.declareProtected(perm_manage, 'setPrefix')
    def setPrefix(self, val):
        self._prefix = val

    def __init__(self, prefix='iva'):
        """ make SQL methods """
        self.id = 'iva_sql_methods'
        self._prefix = prefix

    def _after_init(self):

        self._sql_populate_course_table = SQL('_sql_populate_course_table', 'Populate course table with proper data',
            CONN_ID, 'course_list',
            """
            INSERT INTO <dtml-var prefix>_course_updates (course_id) VALUES 
            <dtml-var course_list>
            ON DUPLICATE KEY 
            UPDATE course_id=course_id;
            """)

        # create index abc on digitiigeriva_obj_history (url DESC);

        self._sql_create_tables = SQL('_sql_create_tables', 'Create tables',
            CONN_ID, '',
            """
            CREATE TABLE IF NOT EXISTS <dtml-var prefix>_users (
                uname CHAR(255) PRIMARY KEY UNIQUE,
                pagesOpenTotal INT(255),
                lastAccess TIMESTAMP
            ) CHARACTER SET utf8 COLLATE utf8_bin
            <dtml-var sql_delimiter>
            CREATE TABLE IF NOT EXISTS <dtml-var prefix>_obj_history(
                id INT(50) PRIMARY KEY AUTO_INCREMENT, 
                url CHAR(255), 
                uname CHAR(255), 
                count INT(5), 
                time TIMESTAMP,
                INDEX abc(url DESC)
            ) CHARACTER SET utf8 COLLATE utf8_bin
            <dtml-var sql_delimiter>
            CREATE TABLE IF NOT EXISTS <dtml-var prefix>_browse_history(
                uname CHAR(255),
                url CHAR(255),
                sess_id CHAR(255),
                time TIMESTAMP
            ) CHARACTER SET utf8 COLLATE utf8_bin
            <dtml-var sql_delimiter>
            CREATE TABLE IF NOT EXISTS <dtml-var prefix>_course_user(
                uname CHAR(255),
                course_id CHAR(255),
                openPages INT(5),
                lastAccess TIMESTAMP,
                notesRead INT(5) DEFAULT 0,
                postedNotes INT(5) DEFAULT 0,
                postedArtefacts INT(5) DEFAULT 0,
                postedArtefactAnnos INT(5) DEFAULT 0,
                uploadedFiles INT(5) DEFAULT 0,
                createdWikis INT(5) DEFAULT 0,
                memosAdded INT(5) DEFAULT 0,
                foldersOpened INT(5) DEFAULT 0,
                testsSolved INT(5) DEFAULT 0,
                numTimesEntered INT(5) DEFAULT 0,
                linksAdded INT(5) DEFAULT 0,
                visit_knowledgebuilding DATETIME DEFAULT 0,
                visit_jamming DATETIME DEFAULT 0,
                INDEX USING BTREE(uname)
            ) CHARACTER SET utf8 COLLATE utf8_bin
            <dtml-var sql_delimiter>
            CREATE TABLE IF NOT EXISTS <dtml-var prefix>_course_updates(
                course_id char(255) PRIMARY KEY UNIQUE,
                modified_jamming DATETIME DEFAULT 0,
                modified_knowledgebuilding DATETIME DEFAULT 0,
                modified_bookshelf DATETIME DEFAULT 0,
                modified_assignments DATETIME DEFAULT 0,
                modified_quizzes DATETIME DEFAULT 0
            ) CHARACTER SET utf8 COLLATE utf8_bin;
            """)

        self._sql_add_visited_url = SQL('_sql_add_visited_url', 'store visited url',
            CONN_ID, 'uname url sess_id',
            """
            INSERT INTO <dtml-var prefix>_browse_history (uname, url, sess_id) VALUES (
            <dtml-sqlvar uname type="string">,
            <dtml-sqlvar url type="string">,
            <dtml-sqlvar sess_id type="string">
            );
            """)
        self._sql_update_user_stat = SQL('_sql_update_user_stat', 'updates user statistics',
            CONN_ID, 'uname',
            """
            INSERT INTO <dtml-var prefix>_users (uname, pagesOpenTotal) VALUES
            (<dtml-sqlvar uname type="string">, 1)
            ON DUPLICATE KEY
            UPDATE pagesOpenTotal=pagesOpenTotal + 1;
            """)
        self._sql_add_obj_history = SQL('_sql_add_obj_history', 'store who has seen object', 
            CONN_ID, 'url uname',
            """
            INSERT INTO <dtml-var prefix>_obj_history (url, uname, count, time) VALUES
                (<dtml-sqlvar url type="string">,
                 <dtml-sqlvar uname type="string">,
                 1,
                 NOW()
                );
            """)
        self._sql_update_course_modified = SQL('_sql_update_course_modified', 'update course modified block',
            CONN_ID, 'field course_id',
            """
            UPDATE <dtml-var prefix>_course_updates
            SET <dtml-var field>=NOW()
            WHERE course_id=<dtml-sqlvar course_id type="string">
            <dtml-var sql_delimiter>
            SELECT row_count() as rows
            """)


        self._sql_update_course_user_access = SQL('_sql_update_course_user_access', 'updates course stat on user action',
            CONN_ID, 'uname course_id',
            """
            UPDATE <dtml-var prefix>_course_user
            SET openPages = openPages + 1
            WHERE course_id=<dtml-sqlvar course_id type="string"> AND uname=<dtml-sqlvar uname type="string">
            <dtml-var sql_delimiter>
            SELECT row_count() as rows
            """)
        self._sql_insert_course_user_access = SQL('_sql_insert_course_user_access', 'inserts course stat on user action',
            CONN_ID, 'uname course_id',
            """
            INSERT INTO <dtml-var prefix>_course_user (uname, course_id, openPages) VALUES
            (
              <dtml-sqlvar uname type="string">,
              <dtml-sqlvar course_id type="string">,
              1
            )
            """)


        self._sql_update_course_user_visit = SQL('_sql_update_course_user_visit', 'register users visit on course',
            CONN_ID, 'field course_id uname',
            """
            UPDATE <dtml-var prefix>_course_user
            SET <dtml-var field>=NOW()
            WHERE course_id=<dtml-sqlvar course_id type="string">
            AND uname=<dtml-sqlvar uname type="string">
            <dtml-var sql_delimiter>
            SELECT row_count() as rows
            """)
        self._sql_update_course_user_stat = SQL('_sql_update_course_user_stat', 'update course user stat',
            CONN_ID, 'field course_id uname',
            """
            UPDATE <dtml-var prefix>_course_user
            SET <dtml-var field>=<dtml-var field>+1
            WHERE course_id=<dtml-sqlvar course_id type="string">
            AND uname=<dtml-sqlvar uname type="string">
            <dtml-var sql_delimiter>
            SELECT row_count() as rows
            """)

        self._sql_get_object_history = SQL('_sql_get_object_history', 'get object history - who has seen it',
            CONN_ID, 'url',
            """
            SELECT uname, time FROM <dtml-var prefix>_obj_history 
            WHERE url = <dtml-sqlvar url type="string">
            ORDER BY time DESC
            """)

        self._sql_get_active_users = SQL('_sql_get_active_users', 'get active users - accessed last 10 minutes',
            CONN_ID, '',
            """
            SELECT uname FROM <dtml-var prefix>_users WHERE lastAccess>NOW()-1000
            """)
        self._sql_get_users_urls = SQL('_sql_get_users_urls', 'get urls user has gone throught',
            CONN_ID, 'uname',
            """
            SELECT url FROM <dtml-var prefix>_browse_history WHERE uname=<dtml-sqlvar uname type="string">
            """)
        self._sql_del_object_history = SQL('_sql_del_object_history', 'delete object history',
            CONN_ID, 'url',
            """
            DELETE FROM <dtml-var prefix>_obj_history WHERE url=<dtml-sqlvar url type="string">;
            """)
        self._sql_get_last_obj_access = SQL('_sql_get_last_obj_access', 'get last access time to object by user',
            CONN_ID, 'url uname',
            """
            SELECT max(time) AS time 
            FROM <dtml-var prefix>_obj_history 
            WHERE url=<dtml-sqlvar url type="string"> 
            AND uname=<dtml-sqlvar uname type="string">
            """)
        self._sql_get_user_course_stat = SQL('_sql_get_user_course_stat', 'get users stats on course',
            CONN_ID, 'uname stat course_id',
            """
            SELECT <dtml-var stat>
            FROM <dtml-var prefix>_course_user
            WHERE uname=<dtml-sqlvar uname type="string">
            AND course_id=<dtml-sqlvar course_id type="string">
            """)
        self._sql_get_user_global_stat = SQL('_sql_get_user_global_stat', 'get users global last access time or pagesOpenTotal',
            CONN_ID, 'uname stat',
            """
            SELECT <dtml-var stat> 
            FROM <dtml-var prefix>_users
            WHERE uname=<dtml-sqlvar uname type="string">
            """)
        self._sql_del_course = SQL('_sql_del_course', 'course is being deleted', 
            CONN_ID, 'course',
            """
            DELETE FROM <dtml-var prefix>_course_updates
            WHERE course_id = <dtml-sqlvar course type="string">
            <dtml-var sql_delimiter>
            DELETE FROM <dtml-var prefix>_course_user
            WHERE course_id = <dtml-sqlvar course type="string">;
            """)
        self._sql_get_course_updates = SQL('_sql_get_course_updates', 'get modification time',
            CONN_ID, 'course stat',
            """
            SELECT <dtml-var stat>
            FROM <dtml-var prefix>_course_updates
            WHERE course_id=<dtml-sqlvar course type="string">
            """)

        self._sql_get_range_users = SQL('_sql_get_range_users', 'get a list of users who have been visited IVA on a range',
            CONN_ID, 'range_start range_end',
            """
            SELECT * from <dtml-var prefix>_users
            WHERE lastAccess><dtml-sqlvar range_start type="string">
            AND lastAccess<<dtml-sqlvar range_end type="string">
            """)
        
class DummySQLMethods(SimpleItem, Persistent, Acquisition.Implicit):
    """ dummy class """

    pass

@adapter(IStatistics, IObjectAddedEvent)
def added(obj, event):
    """ manage after add 
        storeAllURLs -- store ALL urls user goes, that can be very resource consuming.
        trackSessions -- most of the statistics machinery is switched off. it will also disable showAllViews.
        showAllViews -- if object is accessed then we write it down. if all access times are stored or only last
    """
    try:
        obj.manage_addProperty('storeAllURLs', 1, 'boolean')
        obj.manage_addProperty('trackSessions', 1, 'boolean')
        obj.manage_addProperty('showAllViews', 1, 'boolean')
    except:
        pass
    obj._makeSQLMethods()