#!-*- coding:utf-8 -*-"

import csv
import pkg_resources

from decimal import Decimal as D
from datetime import datetime, date, timedelta
from StringIO import StringIO

from genshi.builder import tag

from trac.core import *
from trac.mimeview.api import Mimeview, IContentConverter, Context
from trac.perm import IPermissionGroupProvider, PermissionSystem, PermissionError
from trac.util.datefmt import to_timestamp, utc, format_date, parse_date
from trac.util.text import to_unicode, unicode_urlencode
from trac.util.translation import _
from trac.web.api import IRequestHandler, RequestDone
from trac.web.api import ITemplateStreamFilter
from trac.web.chrome import ITemplateProvider
from trac.web.chrome import add_ctxtnav, add_link, add_script, add_stylesheet

# local imports
from dbutils import *
from utils import *
from dateutils import *

REPORT_GROUP_FIELD = ['type', 'component', 'milestone', 'version']
TWOPLACES = D(10) ** -2 

class TracWorkTimeReportModule(Component):
    implements(IRequestHandler)
    
    group_providers = ExtensionPoint(IPermissionGroupProvider)

    ### methods for IRequestHandler
    def match_request(self, req):
        path = req.path_info.rstrip('/')
        if path.startswith('/worktime/report'):
            return True
        else:
            return False

    def process_request(self, req):
        return self._process_report(req)

    def _process_report(self, req):
        data = {}
        
        group_field = req.args.get('selected_ticket_field', 'id')
        unit = req.args.get('selected_unit', 'hours')
        
        cday = handle_current_date(req)
        
        renderer = ReportRenderer(self.env, self.group_providers)
        data = renderer.get_report_by_month(req, group_field, unit, cday)
        
        #return html
        self._add_ctxnavs(req)
        self._add_alternate_links(req, {'selected_ticket_field': group_field})
        
        return 'worktime_report.html', data, None
    
    def _add_ctxnavs(self, req):
        add_ctxtnav(req, u'作業時間入力', href=req.href.worktime())
        add_ctxtnav(req, u'レポート')
        
        from importer import TracWorkTimeImportModule
        if self.env.is_component_enabled(TracWorkTimeImportModule):
            add_ctxtnav(req, u'作業時間インポート', href=req.href.worktime('importer'))
        
        from burndown import TracWorkTimeBurndownChartModule
        if self.env.is_component_enabled(TracWorkTimeBurndownChartModule):
            add_ctxtnav(req, u'Burndown Chart', href=req.href.worktime('burndown/daily'))
    
    def _add_alternate_links(self, req, args={}):
        params = args.copy()
        if 'year' in req.args:
            params['year'] = req.args['year']
        if 'month' in req.args:
            params['month'] = req.args['month']
        if 'selected_unit' in req.args:
            params['selected_unit'] = req.args['selected_unit']
        href = ''
        if params:
            href = '&' + unicode_urlencode(params)
        add_link(req, 'alternate', '?format=csv' + href,
                 _('Comma-delimited Text'), 'text/plain')
        add_link(req, 'alternate', '?format=tab' + href,
                 _('Tab-delimited Text'), 'text/plain')
        
def is_custom_field(field):
    return field not in TICKET_COLUMNS

class ReportRenderer(object):
    
    def __init__(self, env, group_providers):
        self.env = env
        self.group_providers = group_providers

    def get_report_by_month(self, req, group_field, unit, month):
        next =  next_monthtop(month, 1)
        prev =  prev_monthtop(month, 1)
        
        table, workers, sum_by_workers, all_sum = self._calc_by_date(req, group_field, unit,
                                                                     monthtop(month), monthlast(month))
        
        #export csv or tab or macro
        format = req.args.get('format')
        if format:
            #raise RequestDone if send response directly
            return self._send_convert_content(req, format, group_field, workers, table)

        ticket_fields, ticket_field_map = get_ticket_fileds(self.env)
        
        data = {}
        data.update({'workers': workers,
                     'table': table,
                     'ticket_fields': ticket_fields, 'selected_ticket_field': group_field,
                     'selected_unit': unit,
                     'ticket_field_map': ticket_field_map,
                     'sum_by_workers': sum_by_workers, 'all_sum': all_sum
                    })
        data.update({'current': month, 'prev': prev, 'next': next})
        
        return data
    
    def _calc_by_date(self, req, group_field, unit, from_day, to_day,
                      target_workers=[], conditions=[]):
        accessable_workers = self._get_target_workers(req)
        
        if len(accessable_workers) > 0:
            if len(target_workers) > 0:
                target_workers = list(set(accessable_workers) & set(target_workers))
            else:
                target_workers = accessable_workers
        
        table = self._get_worktime_table_by_date(req, from_day, to_day,
                                                 group=group_field,
                                                 target_workers=target_workers,
                                                 conditions=conditions)
        workers = [x['worker'] for x in table]
        workers = list(set(workers))
        
        table = self._to_table(req, table)
        
        #calc sum
        sum_by_workers, all_sum = self._calc_sum(req, table, workers)
        table = self._calc_unit(table, workers, sum_by_workers, unit)
        
        return table, workers, sum_by_workers, all_sum

    def _send_convert_content(self, req, key, group_field, workers, table, opts={}):
        filename = 'worktime_report.%s' % key
        if key == 'csv':
            return self._export_csv(req, mimetype='text/csv', filename=filename,
                                    group=group_field, workers=workers, table=table)
        elif key == 'tab':
            return self._export_csv(req, '\t',
                                    mimetype='text/tab-separated-values',
                                    filename=filename,
                                    group=group_field, workers=workers, table=table)
            
    def get_table_by_date(self, req, group_field, unit, from_day, to_day,
                          target_workers=[], opts={}):
        u"""Ajax用にHTMLテーブルを生成して返す。戻り値は、データ件数とHTMLのタプル。
        """
        
        conditions = self._parse_condition_query(opts)
        
        table, workers, sum_by_workers, all_sum = self._calc_by_date(req, group_field, unit,
                                                                     from_day, to_day, target_workers, conditions)
        count, html_table = self._render_ajaxtable(req, group=group_field, workers=workers,
                                      data=table, opts=opts)
        return table, count, html_table
        
    def _parse_condition_query(self, opts):
        con_query = opts['condition']
        if not con_query or con_query == '':
            return []
        
        conditions = []
        for con in con_query.split('&'):
            try:
                key, value = con.split('=')
                in_value = value.split('|')
                if len(in_value) > 0:
                    conditions.append((key.strip(), in_value))
                else:
                    conditions.append((key.strip(), value.strip()))
            except ValueError:
                continue
        return conditions
                

    def _render_ajaxtable(self, req, group='id', workers=[], data=[], opts={}):
        u"""Ajax用にHTMLテーブルを生成して返す。戻り値は、データ件数とHTMLのタプル。
        """
        
        if len(data) == 0:
            return 0, tag.div(u"参照可能なデータはありません。")
        
        if opts['table'] == 'hide':
            table_display = 'none'
        else:
            table_display = 'inline'
        index = opts['index']
        
        table = tag.table(class_="listing tickets",
                          id="worktimetable_%s" %(index),
                          style="display:%s" %(table_display))
        caption = tag.caption(opts['title'], align='center')
        table.append(caption)
        
        
        #table > thead
        tr = tag.tr()
        tr.append(tag.th(group))
        
        if opts['transpose']:
            for row in data:
                tr.append(tag.th(row.get('value')))
            table.append(tag.thead(tr))
            
            #table > tbody
            tbody = tag.tbody()
            table.append(tbody)
            
            for idx, worker in enumerate(workers):
                odd_or_even = (idx % 2 == 0) and 'odd' or 'even'
                tr = tag.tr()
                css_class = ''
                
                tr = tr(tag.td(worker))
                
                for idx, row in enumerate(data):
                    if type(row.get('__worker__' + worker)) == Decimal:
                        tr = tr(tag.td(row['__worker__' + worker]))
                    else:
                        tr = tr(tag.td('0'))
                    
                css_class = css_class + odd_or_even
                tr = tr(class_ = css_class)
                tbody.append(tr)
        else:
            for worker in workers:
                tr.append(tag.th('%s' % worker))
            table.append(tag.thead(tr))
            
            #table > tbody
            tbody = tag.tbody()
            table.append(tbody)
            
            for idx, row in enumerate(data):
                odd_or_even = (idx % 2 == 0) and 'odd' or 'even'
                tr = tag.tr()
                css_class = ''
                
                tr = tr(tag.td(row.get('value')))
                
                for worker in workers:
                    if type(row.get('__worker__' + worker)) == Decimal:
                        tr = tr(tag.td(row['__worker__' + worker]))
                    else:
                        tr = tr(tag.td('0'))
                    
                css_class = css_class + odd_or_even
                tr = tr(class_ = css_class)
                tbody.append(tr)
                
        return len(data), table

    def _export_csv(self, req, sep=',', mimetype='text/plain',
                    filename='worktime_report.csv', group='id', workers=[], table=[]):
        out = StringIO()
        writer = csv.writer(out, delimiter=sep)

        if group == 'id':
            self._write_table_for_ticket(writer, workers, table)
        else:
            self._write_table(writer, workers, table, group)
        
        data = out.getvalue()
        req.send_response(200)
        req.send_header('Content-Type', mimetype + ';charset=utf-8')
        req.send_header('Content-Length', len(data))
        if filename:
            req.send_header('Content-Disposition', 'filename=' + filename)
        req.end_headers()
        req.write(data)
        raise RequestDone
    
    def _write_table_for_ticket(self, writer, workers, table):
        #ヘッダ行
        if len(table) > 0:
            row = ['id', 'type', 'component', 'summary']
            row.extend(workers)
            writer.writerow(row)
        #Body
        for t in table:
            row = [t['value'], enc(t['type']), enc(t['component']), enc(t['summary'])]
            for worker in workers:
                hours = t.get('__worker__' + worker, 0)
                row.append(hours)
            writer.writerow(row)

    def _write_table(self, writer, workers, table, group):
        #ヘッダ行
        if len(table) > 0:
            row = [group]
            row.extend(workers)
            writer.writerow(row)
        #Body
        for t in table:
            row = [enc(t['value'])]
            for worker in workers:
                hours = t.get('__worker__' + worker, 0)
                row.append(hours)
            writer.writerow(row)
    
    def _get_target_workers(self, req):
        if 'WORKTIME_REPORT_VIEW' in req.perm:
            #空配列を返すと全workerを対象とする
            return []
        
        target_workers = get_accessable_users(self.group_providers, self.env,
                                              req, 'WORKTIME_REPORT_VIEW_')
        return target_workers
        
    def _get_worktime_table_by_month(self, req, cday, group='id', target_workers=[]):
        u"""指定月の作業時間テーブルを取得して返す。
                引数groupに指定したフィールド名でグルーピングして、作業時間を集計する。
        """
        return self._get_worktime_table_by_date(req, monthtop(cday),
                                                monthlast(cday), group, target_workers)
    
    def _get_worktime_table_by_date(self, req, from_day, to_day, group='id', target_workers=[], conditions=[]):
        u"""指定期間の作業時間テーブルを取得して返す。
                引数conditonに指定した条件でチケットを抽出する。
                引数groupに指定したフィールド名でグルーピングして、作業時間を集計する。
        """
        db = self.env.get_db_cnx()
        cursor = db.cursor()
        
        from_day = format_date(parse_date(from_day.isoformat()), '%Y-%m-%d')
        to_day = format_date(parse_date(to_day.isoformat()), '%Y-%m-%d')
        
        sql = self._create_sql(req, group, target_workers, conditions)
        self.env.log.debug("get_worktime SQL:" + sql)
        cursor.execute(sql, (list(target_workers) + [from_day, to_day]))
        results = []
        
        if group == 'id':
            for date, worker, ticket_id, type, component, summary, hours in cursor:
                results.append({
                                'date': date,
                                'worker': worker,
                                'name': 'id',
                                'value': ticket_id,
                                'type': type,
                                'component': component,
                                'summary': summary,
                                'hours': D(str(float(hours)))
                                })
        else:
            for date, worker, name, value, hours in cursor:
                results.append({
                                'date': date,
                                'worker': worker,
                                'name': name,
                                'value': value,
                                'hours': D(str(float(hours)))
                                })
        return results
    
    def _create_sql(self, req, group, target_workers=[], conditions=[]):
        
        def column(for_group=False):
            if is_custom_field(group):
                return 'tc1.name, tc1.value,'
            else:
                if group == 'id':
                    return 'tw.ticket, t.type, t.component, t.summary,'
                elif group in REPORT_GROUP_FIELD:
                    if for_group:
                        return "t.%s," % (group)
                    else:
                        return "'%s', t.%s," % (group, group)
        
        def left_outer_join():
            join = ''
            if is_custom_field(group):
                join = """
                LEFT OUTER JOIN ticket_custom tc1
                ON tc1.ticket = t.id AND tc1.name = '%s'
                """ % group
            return join
        
        def group_by():
            group_by = ''
            if is_custom_field(group):
                group_by = """
                GROUP BY substr(tw.date, 1, 7), tc1.name, tw.worker, %s
                ORDER BY tc1.name ASC, tw.worker
                """ % column(True)[:-1]
            else:
                group_by = """
                GROUP BY substr(tw.date, 1, 7), t.%s, tw.worker, %s
                ORDER BY t.%s ASC, tw.worker
                """ % (group, column(True)[:-1], group)
            return group_by
        
        def where_workers():
            if len(target_workers) > 0:
                where_workers = """
                tw.worker IN (%s) AND
                """ % ','.join(['%s']*len(target_workers))
                return where_workers
            return ''
        
        def where_ticket():
            rtn = ''
            for key, value in conditions:
                if type(value) == list:
                    rtn += "t.%s IN (%s) AND " % (key, "'%s'" % ("', '".join(value)))
                else:
                    rtn += "t.%s = '%s' AND " % (key, value)
            return rtn
                
        sql = """
        SELECT 
            substr(tw.date, 1, 7) AS date,
            tw.worker, %s sum(tw.hours_worked)
        FROM ticket_worktime tw, ticket t
        %s
        WHERE
            %s
            %s
            (substr(tw.date, 1, 10) BETWEEN %s AND %s) AND t.id = tw.ticket
        %s
        """ % (column(), left_outer_join(),
               where_workers(), where_ticket(), '%s', '%s', group_by())
        
        return sql
    
    def _to_table(self, req, row_table):
        table = {}
        for t in row_table:
            row = table.get(t['value'])
            if not row:
                t.update({
                          '__worker__' + t['worker']: t['hours'],
                          'sum': t['hours']
                          })
                table[t['value']] = t
            else:
                #他の人の作業時間をこの行に追加する
                row['__worker__' + t['worker']] = t['hours']
                
                #合計時間に作業時間を足して算出する
                row['sum'] += t['hours']
                
        values = table.values()
        values.sort(lambda x,y: cmp(x['value'], y['value']))
        return values
    
    def _calc_sum(self, req, table, workers):
        results = {}
        for worker in workers:
            hours = [x['__worker__' + worker] for x in table if x.get('__worker__' + worker) != None]
            results[worker] = reduce(lambda x,y: x+y, hours, 0)
        return results, reduce(lambda x,y: x+y, results.values(), 0)
    
    def _calc_unit(self, table, workers, sum_by_workers, selected_unit):
        if selected_unit != 'ratio':
            return table
        
        for t in table:
            row_sum = D(0)
            for worker in workers:
                sum = sum_by_workers.get(worker)
                hours = t.get('__worker__' + worker)
                if hours:
                    ratio = (hours/sum)
                    row_sum += ratio
                    t.update({'__worker__' + worker: ratio.quantize(TWOPLACES)})
            t.update({'sum': row_sum.quantize(TWOPLACES)})
                              
        return table