# encoding: utf-8

from trac.core import *
from trac.env import IEnvironmentSetupParticipant

class InformBase(Component):
    implements(IEnvironmentSetupParticipant)
    
    # アップグレードが必要か判定する
    def environment_needs_upgrade(self, db):
        cursor = db.cursor()
        
        sql = "SELECT ticket FROM inform"
        
        try:
            self.log.debug('%s' % sql)
            cursor.execute(sql)
            return False
        except Exception, e:
            self.log.debug('%s' % e)
            return True
    
    # アップグレード時にDBにテーブルを追加する
    def upgrade_environment(self, db):
        self.log.debug('InformPlugin upgrade_environment')
        cursor = db.cursor()
        
        sql = "CREATE TABLE inform ( ticket INTEGER NOT NULL, question TEXT, yes TEXT, no TEXT, yesuser TEXT, nouser TEXT, PRIMARY KEY (ticket))"
        
        try:
            self.log.debug('%s' % sql)
            cursor.execute(sql)
        except Exception, e:
            self.log.debug('%s' % e)
            None

class InformData:
    _ticket_id = '0'
    env = None

    def __init__(self, ticket_id, env):
        self._ticket_id = int(ticket_id)
        self.env = env

    def getInformDb(self):
        
        inform_list = []
        
        if self._ticket_id == 0:
            return inform_list;

        db = self.env.get_db_cnx()
        cursor = db.cursor();
        
        # 関係者情報取得
        sql = "SELECT cc FROM ticket WHERE id=%d"
        cursor.execute(sql % (self._ticket_id))
        row = cursor.fetchone()
        if row is None:
            return inform_list
        cc = row[0].replace(' ', '')
        
        # 周知事項情報取得
        sql = "SELECT yesuser,nouser,yes,no FROM inform WHERE ticket=%d"
        cursor.execute(sql % (self._ticket_id))
        row = cursor.fetchone()
        if row is None:
            return inform_list
        yesuser = row[0].replace(' ', '')
        nouser = row[1].replace(' ', '')
        yes = row[2]
        no = row[3]

        sql = ("SELECT s.sid, sa.value FROM session s JOIN session_attribute sa ON s.sid = sa.sid WHERE sa.name = 'name'")

        cursor.execute(sql)
        for sid, value in cursor:
            sid = sid.replace(' ', '')
            if sid in cc.split(','):
                answer = ''
                result = None
                if sid in yesuser.split(','):
                    answer = yes
                    result = True
                if sid in nouser.split(','):
                    answer = no
                    result = False
                inform_list.append({"id":sid, "name": value, "answer": answer, "result": result})
        
        return inform_list