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

from genshi.builder import tag

from trac.core import *
from trac.web.chrome import add_stylesheet

def editelement(_self, req):
    req.perm.assert_permission('LIST_MANAGE_EDIT')
    data = {}
    db = _self.env.get_db_cnx()

    add_stylesheet(req, 'listmanage/css/listmanage.css')

    actionName = req.args.get('actionName')
    when = datetime.now()
    #when_ts = to_timestamp(when)
    when_ts = time.mktime(when.timetuple())
    cursor = db.cursor()
    list_id = req.args.get('list_id')
    element_id =''
    message = ''
    error = ''

    if actionName:
        if actionName=='delete':
            element_id = req.args.get('element_id')
            try:
                #カラムの削除
                sql = ("""delete from list_element where list_id = '%s' and element_id='%s'""" ) % (list_id,element_id)
                _self.log.debug('%s' % sql)
                cursor.execute(sql)
                #値の削除
                sql = ("""delete from list_value where element_id='%s'""" ) % (element_id)
                _self.log.debug('%s' % sql)
                cursor.execute(sql)
                db.commit()
                message = '削除しました。'

            except Exception,e:
                _self.log.debug('%s' % e)
                raise e
        elif actionName =='savesort':
            listorder = req.args.get('listorder').splitlines()
            for idx, row_id in enumerate(listorder):
                sql = ("""update list_element set column_no=%d where list_id='%s' and element_id='%s' """ ) % (idx, list_id, row_id)
                _self.log.debug('%s' % sql)
                cursor.execute(sql)
            db.commit()
            message = '並び順を保存しました。'

    #要素一覧検索
    title = ""
    try:
        sql = ("""SELECT title FROM list_title where list_id = '%s'""" ) % (list_id)
        _self.log.debug('%s' % sql)
        cursor.execute(sql)
        row = cursor.fetchone()
        title = row[0]

        sql = (("""SELECT list_id, element_id,  element_name, element_type, element_detail,default_value,column_no,length"""
                """ FROM list_element where list_id = '%s' order by column_no""" ) % (list_id))
        _self.log.debug('%s' % sql)
        cursor.execute(sql)

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

    element_list=[]
    for list_id, element_id, element_name, element_type, element_detail,default_value,column_no,length in cursor:
        element = {'list_id':list_id, 'element_id':element_id, 'element_name':element_name, 'element_type':element_type,
                   'element_detail':element_detail,'default_value':default_value,'column_no':column_no,'length':length}
        element_list.append(element)

    data = {'element_list': element_list,"list_id" : list_id,"title":title,'error':error,'message':message}

    return 'listelement.html', data, None

def editelementdetail(_self, req):
    data = {}
    question = {}
    db = _self.env.get_db_cnx()

    add_stylesheet(req, 'listmanage/css/listmanage.css')

    actionName = req.args.get('actionName')
    when = datetime.now()
    #when_ts = to_timestamp(when)
    when_ts = time.mktime(when.timetuple())
    message = ''
    error = ''

    cursor = db.cursor()
    list_id = req.args.get('list_id')
    if actionName:
        #要素の編集
        if actionName=='edit':
            element_id = req.args.get('element_id')
            try:
                sql = (("""SELECT list_id, element_id, element_name, element_type, element_detail,default_value,column_no,length"""
                        """ FROM list_element where list_id = '%s' and element_id='%s'""" ) % (list_id,element_id))
                _self.log.debug('%s' % sql)
                cursor.execute(sql)
                row = cursor.fetchone()
            except Exception,e:
                _self.log.debug('%s' % e)
                raise e

            element = {'list_id':row[0], 'element_id':row[1],  'element_name':row[2], 'element_type':row[3]
                       , 'element_detail':row[4], 'default_value':row[5] , 'column_no':row[6], 'length':row[7]}

        #要素の更新
        elif actionName=='update':
            element_id = req.args.get('element_id')
            element_name = req.args.get('element_name')
            element_type = req.args.get('element_type')
            element_detail = req.args.get('element_detail')
            default_value = req.args.get('default_value')
            element_length = req.args.get('length')
            #column_no = req.args.get('column_no')

            try:
                sql = ("""select count(*) from list_element where  list_id = '%s' and element_id='%s'""") % (list_id, element_id)
                _self.log.debug('%s' % sql)
                cursor.execute(sql)
                row = cursor.fetchone()

                if row and row[0] > 0:
                    sql = (("""update list_element set element_name='%s',element_type=%d, element_detail='%s', default_value='%s', length='%s'"""
                            """ where list_id = '%s' and element_id='%s'""" ) % (element_name ,int(element_type), element_detail,default_value, element_length, list_id, element_id))
                    _self.log.debug('%s' % sql)
                    cursor.execute(sql)
                else:
                    when = datetime.now(utc)
                    when_ts = to_timestamp(when)
                    sql = (("""insert into list_element values('%s','%s','%s',%d,'%s','%s' ,ifnull((select max(column_no) + 1 from list_element where list_id = '%s'),1),'%s')""" )
                           % (list_id ,element_id,element_name,int(element_type),element_detail,default_value,list_id,element_length))
                    _self.log.debug('%s' % sql)
                    cursor.execute(sql)
            except Exception,e:
                _self.log.debug('%s' % e)
                raise e
        else:
            element = {'list_id':list_id, 'element_id':'', 'element_name':'', 'element_type':'0', 'element_detail':'', 'default_value':'' , 'column_no':'', 'length':''}
    else:
        element = {'list_id':list_id, 'element_id':'', 'element_name':'', 'element_type':'0', 'element_detail':'', 'default_value':'' , 'column_no':'', 'length':''}

    db.commit()

    if actionName:
        if actionName=='update':
            req.redirect(_self.env.href.listmanage('editelement',{'list_id':list_id}))

    data = {'element': element,"list_id" : list_id,'error':error,'message':message}

    return 'listelementdetail.html', data, None

def edit(_self, req):
    data = {}
    db = _self.env.get_db_cnx()

    add_stylesheet(req, 'listmanage/css/listmanage.css')

    actionName = req.args.get('actionName')
    when = datetime.now()
    #when_ts = to_timestamp(when)
    when_ts = time.mktime(when.timetuple())

    cursor = db.cursor()
    list_id = req.args.get('list_id')
    element_id =''
    message = ''
    error = ''

    if actionName:
        #値の削除
        if actionName=='delete':
            row_id = req.args.get('row_id')
            delete_sql = [
                 ("""delete from list_row where row_id=%d""" ) % (int(row_id)),
                 ("""delete from list_value where row_id =%d""" ) % (int(row_id))
            ]
            for s in delete_sql:
                try:
                    _self.log.debug('%s' % s)
                    cursor.execute(s)
                except Exception,e:
                    _self.log.debug('%s' % e)
            db.commit()
            message = '削除しました。'
        elif actionName =='savesort':
            listorder = req.args.get('listorder').splitlines()
            for idx, row_id in enumerate(listorder):
                sql = '''update list_row set row_no=%d where row_id=%s''' % (idx, row_id)
                _self.log.debug('%s' % sql)
                cursor.execute(sql)
            db.commit()
            message = '並び順を保存しました。'

    #一覧検索
    title = ""
    wiki_name = ""
    element_list=[]
    value_list=[]
    try:
        #title
        sql = ("""SELECT title,wiki_name FROM list_title where list_id = '%s'""" % (list_id))
        _self.log.debug('%s' % sql)
        cursor.execute(sql)
        row = cursor.fetchone()
        title = row[0]
        wiki_name = row[1]

        #要素x値
        element_list,value_list,num_element = getRowData(_self, req, list_id, None)

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

    #wiki出力
    if actionName:
        if actionName=='publish':
            wiki_text = '== %s ==\n' % (title)
            comment = req.args.get('comment')
            for idx,element in enumerate(element_list):
                wiki_text = wiki_text + "||%s" % (element['element_name'])
            wiki_text = wiki_text + "||更新者||更新日||\n"

            for idx2, v in enumerate(value_list):
                for idx3 in range(len(element_list)):
                    wiki_text = wiki_text + "||%s" % (v['value%d'%(idx3)])
                wiki_text = wiki_text + "||%s||%s||\n"  % (v['update_user'],v['update_date'])


            #wiki登録
            sql = (("""insert into wiki values('%s',ifnull((select max(version) + 1 from wiki where name = '%s'),1),%d,'%s','%s','%s','%s',null)""" )
                    % (wiki_name , wiki_name, when_ts,req.authname,'127.0.0.1',wiki_text,comment))
            _self.log.debug('%s' % sql)
            cursor.execute(sql)

            #release_date
            sql = ("""update list_title set release_date='%s' where list_id='%s'""") %(when,list_id)
            _self.log.debug('%s' % sql)
            cursor.execute(sql)
            db.commit()

            #出力後、メイン画面にリダイレクト
            req.redirect(_self.env.href.listmanage())
        elif actionName=="excel":
            req.send_response(200)
            req.send_header('Content-Type','application/vnd.ms-excel')
            req.send_header('Content-Disposition','attachment; filename="list.xls"')
            req.end_headers()

            excel_text = '<html><body><table border="1"><tr>'
            for idx,element in enumerate(element_list):
                excel_text = excel_text + '<th bgcolor="#C0C0C0">%s</th>' % (element['element_name'])
            excel_text = excel_text + '<th bgcolor="#C0C0C0">更新者</th><th bgcolor="#C0C0C0">更新日</th></tr>\n'
            for idx2, v in enumerate(value_list):
                excel_text = excel_text + '<tr>'
                for idx3 in range(len(element_list)):
                    excel_text = excel_text + '<td>%s</td>' % (v['value%d'%(idx3)])
                excel_text = excel_text + '<td>%s</td><td>%s</td></tr>\n' % (v['update_user'],v['update_date'][0:18])
            excel_text = excel_text + '</table></body></html>'

            req.write(excel_text)

            return None

    data = {'value_list': value_list,'element_list':element_list,'list_id' : list_id,'title':title,'error':error,'message':message}

    return 'list.html', data, None


def editdetail(_self, req):
    data = {}
    question = {}
    db = _self.env.get_db_cnx()

    add_stylesheet(req, 'listmanage/css/listmanage.css')

    actionName = req.args.get('actionName')
    when = datetime.now()
    #when_ts = to_timestamp(when)
    when_ts = time.mktime(when.timetuple())
    BizError='BizError'
    message = ''
    error = ''


    cursor = db.cursor()
    list_id = req.args.get('list_id')
    row_id = req.args.get('row_id')
    row_no = req.args.get('row_no')
    parent_row_id = req.args.get('parent_row_id')
    if parent_row_id == None or parent_row_id=='':
        parent_row_id = 0

    element_list=[]
    value_list=[]
    value={}
    num_element = 0
    if actionName:

        element_list,value_list,num_element = getRowData(_self, req, list_id, row_id)
        #行の編集
        if actionName=='edit':
            #要素x値
            value = value_list[0]

        #行の更新
        elif actionName=='update':
            row_id = req.args.get('row_id')
            update_date = req.args.get('update_date')

            try:
                #list_row
                if row_id:
                    sql = (("""update list_row set update_user='%s',update_date='%s'"""
                            """ where row_id = %d and strftime('%%Y-%%m-%%d %%H:%%M:%%f',update_date)='%s'""" ) % (req.authname, when, int(row_id),update_date))
                    _self.log.debug('%s' % sql)
                    upd_result = cursor.execute(sql)
                    _self.log.debug('update_count=%d' % upd_result.rowcount)
                    if upd_result.rowcount != 1:
                        error = '他のユーザに更新または削除されている可能性があります。再検索後、もう一度入力して下さい。'
                        db.rollback()
                        raise BizError, error
                else:
                    sql = (("""insert into list_row values(%d,'%s',%d,ifnull((select max(row_no) + 1 from list_row where list_id = '%s'),1),'%s','%s')""" )
                            % (when_ts , list_id, int(parent_row_id), list_id, req.authname, when))
                    row_id = "%d"  % when_ts
                    _self.log.debug('%s' % sql)
                    cursor.execute(sql)

                #list_value
                num =  req.args.get('num_element')
                #value_id = req.args.get('value_id')
                #values = req.args.get('value')
                #elements = req.args.get('element_id')
                #for idx,vid in enumerate(value_id):
                for idx in range(int(num)):
                    value_id = req.args.get('value_id%d' % idx)
                    element = req.args.get('element_id%d' % idx)
                    values = req.args.get('value%d' % idx)
                    if(isinstance(values,list)):
                        #ラジオボタンはいったん強制削除
                        sql = ("""delete from list_value where row_id=%d and element_id='%s'""" ) % (int(row_id), element)
                        _self.log.debug('%s' % sql)
                        cursor.execute(sql)

                        for idx2,value in enumerate(values):
                            when_ts2 = when_ts + idx2 + 1
                            try:
                                sql = ("""insert into list_value values(%d,%d,'%s','%s')""" ) % (int(when_ts2) , int(row_id), element, value)
                            except IndexError,ierr:
                                sql = ("""insert into list_value values(%d,%d,'%s','')""" ) % (int(when_ts2) , int(row_id), element)
                            _self.log.debug('%s' % sql)
                            upd_result = cursor.execute(sql)
                            if upd_result.rowcount != 1:
                                error = '他のユーザに更新または削除されている可能性があります。再検索後、もう一度入力して下さい。'
                                db.rollback()
                                raise BizError, error
                    else:
                        value = values
                        if value_id == '':
                            when_ts2 = when_ts + idx + 1
                            try:
                                sql = ("""insert into list_value values(%d,%d,'%s','%s')""" ) % (int(when_ts2) , int(row_id), element, value)
                            except IndexError,ierr:
                                sql = ("""insert into list_value values(%d,%d,'%s','')""" ) % (int(when_ts2) , int(row_id), element)
                        else:
                            try:
                                sql = ("""update list_value set value='%s' where value_id=%d""" ) % (value,int(value_id))
                            except IndexError,ierr:
                                sql = ("""update list_value set value='' where value_id=%d""" ) % (int(value_id))
                        _self.log.debug('%s' % sql)
                        upd_result = cursor.execute(sql)
                        if upd_result.rowcount != 1:
                            error = '他のユーザに更新または削除されている可能性があります。再検索後、もう一度入力して下さい。'
                            db.rollback()
                            raise BizError, error
                db.commit()
            except BizError,err:
                _self.log.debug('%s' % err)
                data = {'error':error,'redirect':_self.env.href.listmanage('edit',{'list_id':list_id})}
                return 'listerror.html', data, None
            except Exception,e:
                _self.log.debug('%s' % e)
                raise e
        else:
            for element in element_list:
                value[element['element_id']] = ''
    else:
            for element in element_list:
                 value[element['element_id']] = ''

    if actionName:
        if actionName=='update':
            req.redirect(_self.env.href.listmanage('edit',{'list_id':list_id}))

    data = {'element_list': element_list,'list_id':list_id,"value" : value,'num_element':num_element,'error':error,'message':message ,'title':req.args.get('title')}

    return 'listdetail.html', data, None

def getRowData(_self, req, lst_id, row_id):
    db = _self.env.get_db_cnx()
    cursor = db.cursor()
    element_list=[]
    value_list=[]
    try:
        #要素
        sql = (("""SELECT list_id, element_id,  element_name, element_type, element_detail,default_value,column_no,length as element_length"""
                """ FROM list_element where list_id = '%s' order by column_no""" % (lst_id)))
        _self.log.debug('%s' % sql)
        cursor.execute(sql)

        sql2 = ("""SELECT lr.row_id as row_id,lr.list_id as list_id,"""
                """ lr.row_no as row_no, lr.update_user as update_user, strftime('%Y-%m-%d %H:%M:%f',lr.update_date) as update_date""")
        sql3 = ''
        idx = 0
        for list_id, element_id, element_name, element_type, element_detail,default_value,column_no, element_length in cursor:
            element = {'list_id':list_id, 'element_id':element_id,  'element_name':element_name, 'element_type':element_type,
                       'element_detail':element_detail,'default_value':default_value,'column_no':column_no,'length':element_length}
            element_list.append(element)
            sql2 = sql2 + """ ,lv%d.value_id as value_id%d,lv%d.value as value%d,lv%d.element_id as element_id%d""" % (idx,idx,idx,idx,idx,idx)
            sql3 = sql3 + """ left outer join list_value lv%d on lr.row_id=lv%d.row_id and lv%d.element_id='%s'""" % (idx,idx,idx,element_id)
            idx = idx + 1

        if idx != 0:
            sql2 = sql2 +' FROM list_row lr'
            sql2 = sql2 + sql3

            if row_id:
                sql2 = sql2 +""" WHERE lr.list_id ='%s' and lr.row_id = %d order by row_no""" % (lst_id,int(row_id))
            else:
                sql2 = sql2 +""" WHERE lr.list_id ='%s' order by row_no""" % (lst_id)

            _self.log.debug('%s' % sql2)

            cursor.execute(sql2)

            for row in cursor:
                value = {}
                value['row_id'] = "%d" % row[0]
                value['list_id'] = row[1]
                value['row_no'] = row[2]
                value['update_user'] = row[3]
                value['update_date'] = row[4]
                for idx2 in range(idx):
                    value['value_id%d' % (idx2)] = row[idx2*3+5]
                    value['value%d' % (idx2)] = row[idx2*3+6]
                    value['element_id%d' % (idx2)] = row[idx2*3+7]
                value_list.append(value)

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

    return element_list,value_list,idx

def isSelected(row_id,type,e_id,v_map,value,default_value,num):
    if row_id:
        for i in range(num):
            if v_map.has_key('element_id%d'%(i)) and v_map['element_id%d'%(i)] == e_id:
                if value == v_map['value%d'%(i)]:
                    if (int(type) == 3):
                        return "checked"
                    else:
                        return "selected"
    else:
        if value == default_value:
            if (int(type)==3):
                return "checked"
            else:
                return "selected"
    return None

def getValue(e_id,v_map,default_value,num):
    for i in range(num):
        if v_map.has_key('element_id%d'%(i)) and  v_map['element_id%d'%(i)] == e_id:
            return  v_map['value%d'%(i)]
        elif default_value !='':
            return default_value
    return None
