from jira import JIRA
from collections import defaultdict
import datetime, re, openpyxl


def convert_to_hour(time_str):
    replace_map = {'m':'/60', 'h': '*1', 'd':'*24', 'w':'*7*24'}
    time_str = time_str.replace(' ','')
    time_str = re.sub('([0-9]+[mhdw])(?=[0-9])',r'\1+', time_str)
    for key in replace_map.keys():
        time_str = time_str.replace(key, replace_map[key])
    return eval(time_str)

my_full_name = '<my_name>'
my_email = '<my_email>'
my_token = '<my_token>'
server_url = '<server_url>'

today = datetime.datetime.today()
this_monday = datetime.datetime.combine(today - datetime.timedelta(days=today.weekday()), datetime.time())
jiraOptions = {'server': server_url}
jira = JIRA(options=jiraOptions, basic_auth=(my_email, my_token))

# Collect this week timespent from Jira and sum the timespent for each ticket
ticket_time = defaultdict(int)
for singleIssue in jira.search_issues(jql_str='project = SNB', fields=['worklog']):
    for worklog in singleIssue.fields.worklog.worklogs:
        worklog_started = datetime.datetime.strptime(worklog.started, '%Y-%m-%dT%H:%M:%S.%f+0100')
        if worklog_started > this_monday and str(worklog.author) == my_full_name:
            ticket_time[str(singleIssue.key)] += convert_to_hour(worklog.timeSpent)

           
wb = openpyxl.load_workbook('Timesheet_template.xlsx')
sheet = wb.active

# Insert company logo
img = openpyxl.drawing.image.Image(r'C:\temp\Timesheet\logo.png')
img.anchor = 'C3'
sheet.add_image(img)

# Writing my name
sheet['D10'].value = my_full_name
sheet.merge_cells('C29:F29')
sheet['C29'].value = my_full_name

# Writing the dates of this week
for i in range(7):
    sheet['D' + str(16+i)].value = (this_monday + datetime.timedelta(days=i)).strftime("%d/%m/%Y")
sheet['I10'].value = (this_monday + datetime.timedelta(days=4)).strftime("%d/%m/%Y")
sheet['I26'].value = (this_monday + datetime.timedelta(days=4)).strftime("%d/%m/%Y")
sheet['I29'].value = (this_monday + datetime.timedelta(days=4)).strftime("%d/%m/%Y")

# Writing the ticket id and hours spent
spreadsheet_row = 6
for key in ticket_time.keys():
    #print(key + "\t" + str(ticket_time[key]))
    sheet['M'+str(spreadsheet_row)].value = key
    sheet['T'+str(spreadsheet_row)].value = ticket_time[key]
    spreadsheet_row += 1

sum_hours = sum(v for v in ticket_time.values())
sheet['AE'+str(spreadsheet_row)].value = 37.5 - sum_hours


wb.save('Timesheet_' + (this_monday + datetime.timedelta(days=4)).strftime("%m_%d_%Y") + '_' + my_full_name.replace(' ','_')+ '_v0.1.xlsx')