# -*- coding: utf-8 -*-
# Burnown Compatible Plugin.
# This plugin provide compatible layer for Scrum Burndown Chart Plugin.
# Copyright (C) 2006 Sam Bloomquist <spooninator@hotmail.com>
# Copyright (C) 2006-2008 Daan van Etten <daan@stuq.nl>
# All rights reserved.

# Author: Sam Bloomquist <spooninator@hotmail.com>
# Author: Daan van Etten <daan@stuq.nl>

import time
import datetime
import sys

from tracsteinschart import dbhelper

from trac import __version__ as tracversion_runtime

from datetime import datetime, date

from trac.core import *
from trac.config import BoolOption
from trac.env import IEnvironmentSetupParticipant
from trac.perm import IPermissionRequestor
from trac.web.chrome import add_stylesheet, add_script
from trac.util import escape, Markup, format_date
from trac.ticket import ITicketChangeListener
from trac.ticket import model
from trac.util.datefmt import to_datetime, LocalTimezone
from tracsteinschart.api import _, tag_, N_, add_domain

class BurndownCompatComponent(Component):
    implements(IEnvironmentSetupParticipant, ITicketChangeListener)

    tracversion=tracversion_runtime[:4]

    def __init__(self):
        from pkg_resources import resource_filename
        locale_dir = resource_filename('tracsteinschart', 'locale')
        add_domain(self.env.path, locale_dir)
    
    #---------------------------------------------------------------------------
    # IEnvironmentSetupParticipant methods
    #---------------------------------------------------------------------------
    def environment_created(self):
        """Called when a new Trac environment is created."""
        if self.environment_needs_upgrade(None):
            self.upgrade_environment(None)

    def environment_needs_upgrade(self, db):
        if not db:
            db = self.env.get_db_cnx()            

        needsUpgrade = True

        # See if the burndown table exists, if not, we need an upgrade
        if dbhelper.table_exists(db, "burndown"):
            needsUpgrade = False
            if dbhelper.table_field_exists(db, "burndown", "week"):
                needsUpgrade = True

        if dbhelper.table_field_exists(db, "milestone", "started"):
            needsUpgrade = False

        return needsUpgrade

    def upgrade_environment(self, db):
        db = self.env.get_db_cnx()
        
        needsCreate = True
        needsUpgrade_milestone = True
        needsUpgrade_burndown = False
        
        if dbhelper.table_exists(db, "burndown"):
            needsCreate = False
            if dbhelper.table_field_exists(db, "burndown", "week"):
                needsUpgrade_burndown = True

        if dbhelper.table_field_exists(db, "milestone", "started"):
            needsUpgrade_milestone = False
        
        if needsCreate:
            print >> sys.stderr, 'Attempting to create the burndown table'
            dbhelper.create_burndown_table(db, self.env)

        if needsUpgrade_milestone:
            print >> sys.stderr, 'Attempting to modify the milestone table'
            dbhelper.upgrade_milestone_table(db, self.env)
            
        if needsUpgrade_burndown:
            print >> sys.stderr, 'Attempting to modify the burndown table'
            dbhelper.upgrade_burndown_table(db, self.env)

        db.commit()
            


    #---------------------------------------------------------------------------
    # ITicketChangeListener methods
    #---------------------------------------------------------------------------
    
    def ticket_created(self, ticket):
        self.log.debug('burndown plugin - ticket_created')
        self.update_burndown_data()
        
    def ticket_changed(self, ticket, comment, author, old_values):
        self.log.debug('burndown plugin - ticket_changed')
        self.update_burndown_data()
        
    def ticket_deleted(self, ticket):
        self.log.debug('burndown plugin - ticket_modified')
        self.update_burndown_data()

    #------------------------------------------------------------------------
    # update_burndown_data
    #  - add up the hours remaining for the open tickets for each open milestone and put the sums into the burndown table
    #------------------------------------------------------------------------
    def update_burndown_data(self):
        db = self.env.get_db_cnx()
        cursor = db.cursor()
        
        # today's date
        today = format_date(int(time.time()),tzinfo=LocalTimezone())

        milestones = dbhelper.get_milestones(db)
        components = dbhelper.get_components(db)
        if len(components)==0:
           components = [{'name':'-'}]
        for mile in milestones:
            if mile['started'] and not mile['completed']: # milestone started, but not completed
                for comp in components:
                    if comp['name']=='-':
                        # コンポーネントが未定義の場合
                        sqlSelect = "SELECT est.value AS estimate, ts.value AS spent "\
                                    "FROM ticket t "\
                                    "    JOIN ticket_custom est ON (t.id = est.ticket AND est.name = 'estimatedhours') "\
                                    "    JOIN ticket_custom ts ON (t.id = ts.ticket AND ts.name = 'totalhours') "\
                                    "    JOIN ticket_custom bill ON (t.id = bill.ticket AND bill.name = 'billable' AND bill.value = '1') "\
                                    "WHERE t.milestone = %s"\
                                    "    AND status IN ('new', 'assigned', 'reopened', 'accepted') "
                        cursor.execute(sqlSelect, [mile['name']])
                    else:
                        sqlSelect = "SELECT est.value AS estimate, ts.value AS spent "\
                                    "FROM ticket t "\
                                    "    JOIN ticket_custom est ON (t.id = est.ticket AND est.name = 'estimatedhours') "\
                                    "    JOIN ticket_custom ts ON (t.id = ts.ticket AND ts.name = 'totalhours') "\
                                    "    JOIN ticket_custom bill ON (t.id = bill.ticket AND bill.name = 'billable' AND bill.value = '1') "\
                                    "WHERE t.component = %s AND t.milestone = %s"\
                                    "    AND status IN ('new', 'assigned', 'reopened', 'accepted') "
                        cursor.execute(sqlSelect, [comp['name'], mile['name']])

                    rows = cursor.fetchall()
		    
                    hours = 0
                    estimate = 0
                    spent = 0
                    if rows:
                        for estimate, spent in rows:
                            if not estimate:
                                estimate = 0
                            if not spent:
                                spent = 0

                            if (float(estimate) - float(spent)) > 0:
                                hours += float(estimate) - float(spent)

                    cursor.execute("SELECT id FROM burndown WHERE date = %s AND milestone_name = %s"\
                                        "AND component_name = %s", [today, mile['name'], comp['name']])

                    row = cursor.fetchone()
                    
                    try:
                        if row:
                            cursor.execute("UPDATE burndown SET hours_remaining = %s WHERE date = %s AND milestone_name = %s"\
                                           "AND component_name = %s", [hours, today, mile['name'], comp['name']])
                        else:
                            cursor.execute("INSERT INTO burndown(component_name, milestone_name, date, hours_remaining) "\
                                           "    VALUES(%s,%s,%s,%s)", [comp['name'], mile['name'], today, hours])
                    except Exception, inst:
                        self.log.debug(type(inst))     # the exception instance
                        self.log.debug(inst.args)      # arguments stored in .args
                        self.log.debug(inst)           # __str__ allows args to printed directly
                        cursor.connection.rollback()
                    else:
                        db.commit()
                if len(components)==0:
                    comp = {'name':'-'}
                
                    rows = cursor.fetchall()
                    hours = 0
                    estimate = 0
                    spent = 0
                    if rows:
                        for estimate, spent in rows:
                            if not estimate:
                                estimate = 0
                            if not spent:
                                spent = 0
                        
                            if (float(estimate) - float(spent)) > 0:
                                hours += float(estimate) - float(spent)

                    cursor.execute("SELECT id FROM burndown WHERE date = %s AND milestone_name = %s"\
                                        "AND component_name = %s", [today, mile['name'], comp['name']])
            
                    row = cursor.fetchone()
                    
                    try:
                        if row:
                            cursor.execute("UPDATE burndown SET hours_remaining = %s WHERE date = %s AND milestone_name = %s"\
                                           "AND component_name = %s", [hours, today, mile['name'], comp['name']])
                        else:
                            cursor.execute("INSERT INTO burndown(component_name, milestone_name, date, hours_remaining) "\
                                           "    VALUES(%s,%s,%s,%s)", [comp['name'], mile['name'], today, hours])
                    except Exception, inst:
                        self.log.debug(type(inst))     # the exception instance
                        self.log.debug(inst.args)      # arguments stored in .args
                        self.log.debug(inst)           # __str__ allows args to printed directly
                        cursor.connection.rollback()
                    else:
                        db.commit()
                                         

