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

def convertfloat(x):
    "some european countries use , as the decimal separator"
    x = str(x).strip()
    if len(x) > 0:
        return float(x.replace(',', '.'))
    else: 
        return 0.0
    
def add_ticket_change(db, ticket_id, author, change_date, hours, log):
    """tries to save a ticket change,

       dontinsert means do not add the change if it didnt already exist
    """
    import trac.util.datefmt
    import datetime
   
    if type(change_date) == datetime.datetime:
        change_time = trac.util.datefmt.to_timestamp(change_date)
        last_date = datetime.datetime(change_date.year, change_date.month, change_date.day,
                                      23, 59, 59, tzinfo=change_date.tzinfo)
        last_time = trac.util.datefmt.to_timestamp(last_date)
        
    hours = convertfloat(hours)
        
    cursor = db.cursor();
    
    #指定した日時に挿入する
    #既に存在する日時の場合は微妙に時刻をずらす
    cursor.execute("""SELECT time FROM ticket_change
                   WHERE ticket=%s and time >= %s and time <= %s and field=%s ORDER BY time DESC""",
                   (ticket_id, change_time, last_time, 'hours'))
    row = cursor.fetchone()
    if row:
        change_time = row[0] + 60 #+60s
        
    cursor.execute("""INSERT INTO ticket_change  (ticket, time, author, field, oldvalue, newvalue)
                   VALUES(%s, %s, %s, %s, %s, %s)""",
                   (ticket_id, change_time, author, 'hours', '0.0', str(hours)))
    cursor.execute("""INSERT INTO ticket_change  (ticket, time, author, field, oldvalue, newvalue)
                   VALUES(%s, %s, %s, %s, %s, %s)""",
                   (ticket_id, change_time, author, 'comment', '', ''))
    
    sql = """SELECT newvalue FROM ticket_change
             WHERE ticket=%s and time<%s and field=%s
             ORDER BY time DESC"""
    cursor.execute(sql, (ticket_id, change_time, 'totalhours'))
    row = cursor.fetchone()
    if row:
        totalhours = convertfloat(row[0])
    else:
        totalhours = 0.0
    
    cursor.execute("""INSERT INTO ticket_change (ticket, time, author, field, oldvalue, newvalue)
                   VALUES(%s, %s, %s, %s, %s, %s)""",
                   (ticket_id, change_time, author, 'totalhours', str(totalhours), str(totalhours + hours)))
    
    #残りの変更点に関してtotalhoursを更新する
    sql = """SELECT time, oldvalue, newvalue FROM ticket_change
             WHERE ticket=%s and time>%s and field=%s ORDER BY time ASC"""
    cursor.execute(sql, (ticket_id, change_time, 'totalhours'))
    rows = cursor.fetchall()
    
    latest_totalhours = str(totalhours + hours)
    for time, oldvalue, newvalue in rows:
        new_oldvalue = str(convertfloat(oldvalue) + hours)
        new_newvalue = str(convertfloat(newvalue) + hours)
        cursor.execute("""UPDATE ticket_change SET oldvalue=%s, newvalue=%s
                       WHERE ticket = %s and time=%s and field=%s""",
                       (new_oldvalue, new_newvalue, ticket_id, time, 'totalhours'))
        
        latest_totalhours = new_newvalue
        
    #ticket_customの整合性も合わせる
    cursor.execute("""SELECT ticket FROM ticket_custom WHERE ticket=%s and name=%s""",
                   (ticket_id, 'totalhours'))
    row = cursor.fetchone()
    if row:
        cursor.execute("""UPDATE ticket_custom SET value=%s
                       WHERE ticket=%s and name=%s""",
                       (latest_totalhours, ticket_id, 'totalhours'))
    else:
        cursor.execute("""INSERT INTO ticket_custom (ticket, name, value) VALUES (%s, %s, %s)""",
                       (ticket_id, 'totalhours', latest_totalhours))
    
    db.commit()
    
def get_my_works(db, ticket_id, authname, log):
    cursor = db.cursor()
    sql = """SELECT time, newvalue FROM ticket_change
             WHERE ticket=%s AND author=%s AND field=%s
             ORDER BY time DESC"""
    cursor.execute(sql, (ticket_id, authname, 'hours'))
    return cursor.fetchall()

def execute(com, sql, *params):
    """Executes the query on the given project"""
    db = com.env.get_db_cnx()
    cur = db.cursor()
    try:
        cur.execute(sql, params)
        db.commit()
    except Exception, e:
        com.log.error('There was a problem executing sql:%s \n \
with parameters:%s\nException:%s' % (sql, params, e));
        db.rollback();
    try:
        db.close()
    except:
        pass
    
