#encoding=utf-8
import re
import calendar, time
import md5
from datetime import datetime, date, timedelta
from time import strptime ,mktime
from trac.util.datefmt import utc, utcmax, to_timestamp

from genshi.builder import tag

from trac.core import *
from trac.env import IEnvironmentSetupParticipant
from trac.web import IRequestHandler
from trac.web.chrome import INavigationContributor, ITemplateProvider, add_stylesheet, add_script
from trac.perm import IPermissionRequestor

from enquete_answer import *
from enquete_edit import *
from enquete_chart import *
from enquete_title import *

class EnquetePlugin(Component):
    implements(IEnvironmentSetupParticipant,
                    INavigationContributor, IRequestHandler, ITemplateProvider,IPermissionRequestor)

    # INavigationContributor methods
    def get_active_navigation_item(self, req):
        return 'Enquete'

    def get_navigation_items(self, req):
        if req.perm.has_permission('ENQUETE_VIEW'):
            yield ('mainnav', 'enquete',
                   tag.a('アンケート', href=req.href.enquete()))

    # IRequestHandler methods
    def match_request(self, req):
        #return re.match(r'/enquete(?:_trac)?(?:/.*)?$', req.path_info)
        match = re.match('/enquete(?:/([^/]+))?(?:/(.*)$)?', req.path_info)
        if match:
            req.args['module'] = match.group(1)
            req.args['arguments'] = match.group(2)
            return True
        else:
            return False

    #IPermissionRequestor methods
    def get_permission_actions(self):
        return ['ENQUETE_VIEW','ENQUETE_EDIT','ENQUETE_ANSWER']

    def process_request(self, req):
        req.perm.assert_permission('ENQUETE_VIEW')

        db = self.env.get_db_cnx()
        actionName = req.args.get('actionName')
        enquete_id = req.args.get('enquete_id')
        id = req.args.get('id')
        error =''

        add_stylesheet(req, 'enquete/css/enquete.css')
        add_stylesheet(req, 'enquete/css/calendar.css')
        add_script(req,'enquete/js/yahoo-dom-event.js')
        add_script(req,'enquete/js/calendar.js')
        add_script(req,'enquete/js/util.js')
        add_script(req,'enquete/js/swfobject.js')

        #モジュールの判定
        module = req.args.get('module', None)
        if module:
            if module =='edittitle':
                check = True
                if actionName == 'update':
                    check,status = self.checkOwner(db,req.authname,enquete_id)

                if check:
                    return edittitle(self,req)
                else:
                    error = "アンケートのオーナではありません。"
            elif module == 'edit':
                check,status = self.checkOwner(db,req.authname,enquete_id)
                if check:
                    return edit(self,req)
                else:
                    error = "アンケートのオーナではありません。"
            elif module == 'editdetail':
                return edit_detail(self,req)
            elif module == 'answer':
                if actionName == 'count' or actionName == 'group':
                    if id != None and id != '':
                        check,status = self.checkOwner(db,req.authname,id)
                        if int(status) == 2 or check:
                            return answer(self,req)
                        else:
                            error = "アンケートのオーナではありません。"
                    else:
                        return answer(self,req)
                else:
                    return answer(self,req)
            elif module == 'chart':
                return chart(self,req)

        data = {}

        when = datetime.now(utc)
        when_ts = to_timestamp(when)

        cursor = db.cursor()
        if actionName:
            #アンケート削除
            if actionName=='delete':
                req.perm.assert_permission('ENQUETE_EDIT')

                #アンケートのオーナのみ削除可能
                check,status = self.checkOwner(db,req.authname,enquete_id)
                if check:
                    try:
                        #cursor.execute("""delete from enquete where enquete_id = %d"""  % (int(enquete_id)))
                        #cursor.execute("""delete from question where enquete_id = %d"""  % (int(enquete_id)))
                        #cursor.execute("""delete from answer where enquete_id = %d"""  % (int(enquete_id)))
                        #cursor.execute("""delete from answer_detail where enquete_id = %d"""  % (int(enquete_id)))
                        sql = [
                             ("""delete from enquete where enquete_id = %d""")  % (int(enquete_id)),
                             ("""delete from question where enquete_id = %d""") % (int(enquete_id)),
                             ("""delete from answer where enquete_id = %d""")  % (int(enquete_id)),
                             ("""delete from answer_detail where enquete_id = %d""")  % (int(enquete_id))
                        ]
                        cursor = db.cursor()
                        for s in sql:
                            try:
                                self.log.debug('%s' % s)
                                cursor.execute(s)
                            except Exception,e:
                                self.log.debug('%s' % e)
                                None

                    except Exception,e:
                        self.log.debug('%s' % e)
                        raise e

                    db.commit()
                else:
                    error = "アンケートのオーナではありません。"

            #アンケート公開・非公開・終了
            elif actionName=='open' or actionName=='close' or actionName=='expire':
                req.perm.assert_permission('ENQUETE_EDIT')

                #アンケートのオーナのみ公開・非公開・終了可能
                check,status = self.checkOwner(db,req.authname,enquete_id)
                if check:
                    try:
                        if actionName == 'open':
                            change_status = 1
                            sql = ("""update enquete set status=%d,open_date = '%s' where enquete_id = %d""" ) % (change_status,when,int(enquete_id))
                            self.log.debug('%s' % sql)
                            cursor.execute(sql)
                        elif actionName == 'close':
                            change_status = 0
                            sql = ("""update enquete set status=%d,open_date = '' where enquete_id = %d""" ) % (change_status,int(enquete_id))
                            self.log.debug('%s' % sql)
                            cursor.execute(sql)
                        elif actionName == 'expire':
                            change_status = 2
                            sql = ("""update enquete set status=%d where enquete_id = %d"""  ) % (change_status,int(enquete_id))
                            self.log.debug('%s' % sql)
                            cursor.execute(sql)

                    except Exception,e:
                        self.log.debug('%s' % e)
                        raise e
                    db.commit()
                else:
                    error = "アンケートのオーナではありません。"
        #アンケート一覧検索
        try:
            sql = ("""SELECT e0.*,strftime('%Y/%m/%d %H:%M:%S',a2.update_date) as answer_date from (""")
            sql = sql +("""SELECT e.enquete_id as enquete_id, e.title as title, strftime('%Y/%m/%d %H:%M:%S',e.update_date) as upd_date, e.create_user,""")
            sql = sql + (""" strftime('%Y/%m/%d %H:%M:%S',e.open_date) as open_date, e.status as status,e.expire_date as expire_date, e.description as description, count(a.user_id) cnt FROM enquete e""")
            sql = sql + (""" left outer join answer a on a.enquete_id=e.enquete_id""")
            if req.authname != 'admin':
                #admin以外はオーナが自分のアンケートか、公開受付中、受付終了のみ
                sql = sql + (""" where e.create_user='%s' or e.status in (1,2)""" ) % (req.authname)
            sql = sql + (""" group by e.enquete_id""")
            digest_user = md5.new(req.authname).hexdigest()
            sql = sql + (""" ) e0 left outer join answer a2 on a2.enquete_id=e0.enquete_id and a2.user_id = '%s'""") % (digest_user)
            self.log.debug('%s' % sql)
            cursor.execute(sql)

        except Exception,e:
            self.log.debug('%s' % e)
            raise e

        enquete_list=[]
        for enquete_id, title, upd_date, create_user, open_date, status, expire_date, description, cnt, answer_date in cursor:
             enquete = {'enquete_id':enquete_id ,'title':title, 'update_date':upd_date, 'create_user':create_user, 'open_date':open_date, 'status':status, 'expire_date':expire_date, 'description':description, 'answer_date':answer_date, 'count':cnt}
             enquete_list.append(enquete)

        data = {'enquete_list': enquete_list,'error':error}

        return 'enquete.html', data, None

    # ITemplateProvider methods
    def get_templates_dirs(self):
        from pkg_resources import resource_filename
        return [resource_filename(__name__, 'templates')]

    def get_htdocs_dirs(self):
        from pkg_resources import resource_filename
        return [('enquete',resource_filename(__name__, 'htdocs'))]

    def checkOwner(self,db,user,enquete_id):
        cursor = db.cursor()
        sql = ("""SELECT create_user,status FROM enquete where enquete_id = %d""" % (int(enquete_id)))
        cursor.execute(sql)
        row = cursor.fetchone()
        if row[0] == user:
            return True,row[1]
        else:
            return False,row[1]

    # IEnvironmentSetupParticipant methods
    def environment_created(self):
        self.log.debug('EnquetePlugin environment_created')
        pass

    def environment_needs_upgrade(self, db):
        self.log.debug('EnquetePlugin environment_needs_upgrade')
        cursor = db.cursor()
        try:
            sql = 'SELECT enquete_id FROM enquete'
            self.log.debug('%s' % sql)
            cursor.execute(sql)
        except Exception,e:
            self.log.debug('%s' % e)
            return True

        return False

    def upgrade_environment(self, db):
        self.log.debug('EnquetePlugin upgrade_environment')

        sql = [
 """CREATE TABLE enquete (enquete_id integer PRIMARY KEY,title text, status integer, open_date date, update_date date, create_user text, expire_date text, description text)""",
 """CREATE TABLE question (enquete_id integer ,question_id integer ,question_title text, type integer, detail text, unique(enquete_id,question_id))""",
 """CREATE TABLE answer (enquete_id integer ,user_id text ,status integer,update_date date, unique(enquete_id,user_id))""",
 """CREATE TABLE answer_detail (enquete_id integer ,user_id text ,question_id integer, answer text)"""
]
        cursor = db.cursor()
        for s in sql:
            try:
                self.log.debug('%s' % s)
                cursor.execute(s)
            except Exception,e:
                self.log.debug('%s' % e)
                None

