Time Booking Script

Workflow for time booking

• Find days to book for: https://youtrack.mway.io/timesheets
• Export from com.dynamicg.timerecording app:
  • Open app
  • Top-right -> search
  • Enter time range and hit search
  • Top-right -> Print View | Work Units
  • Excel | Send
  • Nextcloud
• Download
  • Nextcloud
  • share with expiration date / password
  • open link on mac
  • download
• Prep data
  • Open file (xls)
  • remove unnecessary columns
  • export to csv (remember the delimiter, will affect scripts if wrong delimiter is used)
• Book time
  • $ conda activate
  • $ jupyter notebook
  • navigate to ~/dev/tools/Python
  • follow in-script instructions:
    • Prep_Times
    • Time_Booking_YouTrack
  • $ conda deactivate

Jupyter Notebook 1: Prep Times

import csv
import datetime
import re


def createCleanTimes(inputFile, outputFile):
    rows = importTimes(inputFile)
    rows.pop(0)
    newRows = [['Date', 'Time', 'Notes']]
    for row in rows:
        newRows.append(buildBetterRow(row))
    writeOutput(outputFile, newRows)
    return

def checkOutput(filePath):
    #rows = [
    #    ["2022-12-01", "2022-12-01 08:00:00", "2022-12-01 10:00:00", "Ticket", "177"],
    #    ["2022-12-01", "2022-12-01 10:00:00", "2022-12-01 13:00:00", "Scrum", "Review+Planning"],
    #    ["2022-12-01", "2022-12-01 14:00:00", "2022-12-01 16:00:00", "Meeting", "Zeit buchen"]
    #]
    rows = importTimes(filePath)
    rows.pop(0)
    
    # don't output until all rows are done, to avoid missing an error
    toDisplay = []
    for row in rows:
        toDisplay.append(buildBetterRow(row))
        
    for display in toDisplay:
        print(display)

def importTimes(filePath):
    with open(filePath) as file:
        csvReader = csv.reader(file, delimiter=";", quotechar='"')
        timeData = [row for row in csvReader]
    return timeData

def buildBetterRow(row):
    date = betterDate(row[0])
    time = verifiedTime(row[1])
    task = row[2]
    notes = row[3]
    
    return [date, time, calculateTicketIfPossible(task, notes)]

def betterDate(dotSeparated):
    return "-".join(reversed(dotSeparated.split(".")))

def verifiedTime(time):
    if time == "00:00":
        raise ValueError("Invalid time " + time)
    return time

def calculateTicketIfPossible(task, notes):
    if isRelTicket(notes):
        return "REL-{}".format(notes)
    if isGeneralTicket(notes):
        return notes
    if task == "Scrum":
        return "REL-148"
    if task == "Meeting":
        return "REL-145"
    if task == "SpecOps":
        return "REL-376"
    if task == "Test Session":
        return "REL-374"
    if task == "Onboarding":
        return "REL-155"
    if task == "MTOP Lead":
        return "REL-156"
    if task == "Ticket":
        return notes
    return "{} - {}".format(task, notes)

def isRelTicket(possiblyTicket):
    pattern = re.compile("[0-9]{1,6}")
    return not pattern.fullmatch(possiblyTicket) == None

def isGeneralTicket(possiblyTicket):
    pattern = re.compile("[A-Z]{1,3}-[0-9]{1,6}")
    return not pattern.fullmatch(possiblyTicket) == None

def writeOutput(filePath, csvArray):
    with open(filePath, "w") as file:
        csvFile = csv.writer(file)
        for row in csvArray:
            csvFile.writerow(row)

fileDirectory = "/Users/jeffrey.maier/dev/resources/timebooking/2024_10_21-2024_11_18/"
inputFile = fileDirectory + "20241021_20241118.csv"
outputFile = fileDirectory + "updated_time_rec.csv"

checkOutput(inputFile)

createCleanTimes(inputFile, outputFile)

Jupyter Notebook 2: Book Times

Readme

Expects data with following structure:

Date,Time,Notes
2022-12-01,02:00,REL-177
2022-12-01,00:30,MWI-123
2022-12-01,03:00,REL-148
2022-12-01,00:30,REL-148
2022-12-01,02:00,REL-145
2022-12-02,00:45,REL-168
2022-12-02,01:45,REL-148
2022-12-02,01:00,REL-168

You also need a YouTrack token, which you can create here: https://youtrack.mway.io/users/me?tab=account-security

import csv
import requests
import json
import datetime
from getpass import getpass


def importTimeSheet(filePath):
    with open(filePath) as file:
        csvReader = csv.reader(file, delimiter=",", quotechar='"')
        timeData = [row for row in csvReader]
    return timeData

def checkStructure(timeArray):
    headers = timeArray[0]
    if headers[0] != 'Date':
        raise Exception("Date header missing")
    if headers[1] != 'Time':
        raise Exception("Time header missing")
    if headers[2] != 'Notes':
        raise Exception("Notes header missing")

def convertTime(time):
    hoursAndMinutes = time.split(":")
    if len(hoursAndMinutes) != 2:
        raise Exception("Time not properly formatted")
    hours = int(hoursAndMinutes[0])
    minutes = int(hoursAndMinutes[1])
    return "{}h {}m".format(hours, minutes)

def timeStringToMillis(timeString):
    timeSegments = timeString.split("-")
    if len(timeSegments) != 3:
        raise Exception("Date not properly formatted")
    year = int(timeSegments[0])
    month = int(timeSegments[1])
    day = int(timeSegments[2])
    date = datetime.datetime(year, month, day, hour=9, minute=30)
    return int(date.timestamp() * 1000)

def buildUrl(ticket):
    return "https://youtrack.mway.io/api/issues/{}/timeTracking/workItems".format(ticket)

def buildBody(formattedTime, dateInMillis):
    return {
        'date': dateInMillis,
        'duration': {
            'presentation': formattedTime
        }
    }

def buildHeaders(token):
    return {
        "Content-Type" : "application/json",
        "authorization": "Bearer {}".format(token)
    }

def sendRequest(url, body, token):
    headers = buildHeaders(token)
    return requests.post(url, headers = headers, json = body)

def logTime(ticket, date, time, token):
    formattedTime = convertTime(time)
    dateInMillis = timeStringToMillis(date)
    
    body = buildBody(formattedTime, dateInMillis)
    url = buildUrl(ticket)
    response = sendRequest(url, body, token)
    if not response.ok:
        print(response.status_code)
        print(response.reason)
        response.raise_for_status()

def logTimes(filePath):
    timeData = importTimeSheet(filePath)
    checkStructure(timeData)
    timeData.pop(0)
    token = getpass("Enter token:") # https://youtrack.mway.io/users/me?tab=account-security
    print("Time logged:", end="")
    for row in timeData:
        date = row[0]
        time = row[1]
        ticket = row[2]
        logTime(ticket, date, time, token)
        print(" " + date, end="")
    print(".")
    print("Finished")

logTimes("/Users/jeffrey.maier/dev/resources/timebooking/2024_11_19-2025_04_21/updated_time_rec.csv")