<jupyter_start><jupyter_text>Monday.com Timesheet Detailed Version---------  Import Libraries<jupyter_code>import requests
import json
import csv
import openpyxl
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.drawing.image import Image
from openpyxl.styles import Font, Alignment, Border, Side, PatternFill
from PIL import Image
from monday import MondayClient
import datetime
from datetime import timedelta
import time
import configparser 
config = configparser.ConfigParser()
config.read('config.ini')
api_key = config['DEFAULT']['API-Key']
timesheet_date = config['DEFAULT']['Timesheet-Date']
board_ids = config['DEFAULT']['Board-ID-List']
date = config['DEFAULT']['Current-Date']
time_difference = config['DEFAULT']['Time-Difference']<jupyter_output><empty_output><jupyter_text>Supporting Functions<jupyter_code>def digit_or_dot(s):
    return all(c.isdigit() or c == '.' for c in s)
def get_congif_info(person):
    surname = config[person]['Surname']
    given_names = config[person]['Given-Names']
    employee_number = config[person]['Employee-Number']
    dob = config[person]['Date-Of-Birth']
    phone_number = config[person]['Phone-Number']
    rate = config[person]['Rate']
    pay_code = config[person]['Pay-Code']
    
    return surname,given_names,employee_number,dob,phone_number,rate,pay_code
def create_cell(column, text, align):
    sheet["{}{}".format(column,row_num)] = text
    sheet["{}{}".format(column,row_num)].font = table_font
    sheet["{}{}".format(column,row_num)].border = thin_full_border
    if align == 1:
        sheet["{}{}".format(column,row_num)].alignment = Alignment(wrap_text = True, horizontal='center', vertical='center')
    else:
        sheet["{}{}".format(column,row_num)].alignment = Alignment(wrap_text = True, vertical='center')
def create_excel():
    workbook = Workbook()
    sheet = workbook.active
    
    return workbook,sheet
def set_column_width(sheet):
    sheet.column_dimensions['A'].width = 14
    sheet.column_dimensions['B'].width = 15.17
    sheet.column_dimensions['C'].width = 14
    sheet.column_dimensions['D'].width = 20
    sheet.column_dimensions['E'].width = 20
    sheet.column_dimensions['F'].width = 20
    sheet.column_dimensions['G'].width = 20
    sheet.column_dimensions['H'].width = 13
    sheet.column_dimensions['I'].width = 13
    sheet.column_dimensions['J'].width = 17.17
    sheet.column_dimensions['K'].width = 11.5
    sheet.column_dimensions['L'].width = 19.67
    sheet.column_dimensions['M'].width = 21.83
    sheet.column_dimensions['N'].width = 28.5
    sheet.column_dimensions['O'].width = 28.5
def add_logo(sheet):
    width = 88
    height = 182

    img = Image.open('timesheet_logo.png')
    img = img.resize((width,height),Image.NEAREST)
    img.save('timesheet_logo.png')

    img = openpyxl.drawing.image.Image('timesheet_logo.png')
    sheet.add_image(img,'A1')
def add_timesheet_info(sheet):
    sheet["B1"] = "Timesheet Sessional Academic Staff"
    sheet["B1"].font = Font(bold=True, underline="single", name='Arial', size=16)

    sheet["B2"] = "  Please  Note : "
    sheet["B2"].font = Font(bold=True, underline="single", name='Calibri (Body)', size=12)

    sheet["B3"] = """
     - This timesheet is used for sessional academic staff who work as Research Assistants, Project work or Claims that fall outside usual teaching activities logged via sessional Teaching /Marking spreadsheets.  
     -  Please do not print out this T5 form, type in your timesheet details in this spreadsheet and save as “name.xlsx” file, attach it in your staff email and send to your supervisor for approval.
     - Timesheet excel format files are to be submitted from your Swinburne staff email account to your Department Administrator/Managers for appropriate approvals by no later than 5pm Tuesday.
     - Administrators/ Managers must email submit this approved Timesheets with the text in the subject: THE ATTACHED TIMESHEET IS APPROVED to <EMAIL> by Wednesday 12 noon immediately following fortnight end date.  Please refer to paydates and deadline tab of this spreadsheet.
     - Please fill out the correct employee number and job number failure to do that will cause the incorrect payment. Please refer to Help Notes tab of this spreadsheet or contact payroll for any queries about how to fill out this timesheet. 
    """
    sheet["B3"].font = Font(name='Calibri (Body)', size=12)
    sheet["B3"].alignment = Alignment(wrap_text = True)
    sheet.row_dimensions[3].height = 125

    sheet.merge_cells('B3:O3')
def add_column_names(sheet):
    column_names = ["Surname", "<NAME>", "Employee Number", "Board Name", "Group Name", "Job Title", "JOB Number",  "Workday Position Number", "Workday Position Primary Flag (Y/N)", "Date", "Pay Code", "Total Hours (exc.meal break) or Units", "Rate", "Account Code", "Subject Code"]

    for col, val in enumerate(column_names, start=1):
        sheet.cell(row=4, column=col).value = val
        sheet.cell(row=4, column=col).border = thin_full_border
        sheet.cell(row=4, column=col).font = Font(bold=True, name='Calibri (Body)', size=14)
        sheet.cell(row=4, column=col).alignment = Alignment(wrap_text = True, horizontal='center', vertical='center')<jupyter_output><empty_output><jupyter_text>Get Person List & Project Code List<jupyter_code>person_list = []
project_code_list = {}
for s in config.sections():
    if digit_or_dot(s):
        project_code_list[s] = {}
    else:
        person_list.append(s)
for pc in project_code_list:
    project_code_list[pc]["name"] = config[pc]['Name']
    project_code_list[pc]["department_name"] = config[pc]['Department-Name']
    project_code_list[pc]["phone"] = config[pc]['Phone']
    project_code_list[pc]["date"] = date
    project_code_list[pc]["subject_code"] = config[pc]['Subject-Code']
    project_code_list[pc]["board_ids"] = []<jupyter_output><empty_output><jupyter_text>Connect To Monday.com<jupyter_code>apiUrl = "https://api.monday.com/v2"
headers = {"Authorization" : api_key}
monday = MondayClient(api_key)<jupyter_output><empty_output><jupyter_text>Get Board ID List<jupyter_code>board_id_list = []
if board_ids:
    for b in board_ids.split(","):
        board_id_list.append(b)
else:
    query = '{ boards {id}}'
    data = {'query' : query}
    r = requests.post(url=apiUrl, json=data, headers=headers)
    for bd in r.json()["data"]["boards"]:
        board_id_list.append(bd["id"])<jupyter_output><empty_output><jupyter_text>Get Board.Group As Prefix For Job ID<jupyter_code>group_item_dict = {}

for board_id in board_id_list:
    board_query = '{{boards (ids: {}){{description name groups{{id}}}}}}'.format(board_id)
    board_data = {'query' : board_query}
    board_result = requests.post(url=apiUrl, json=board_data, headers=headers)
    
    board_name = board_result.json()["data"]["boards"][0]["name"]
    board_project_code = board_result.json()["data"]["boards"][0]["description"].split("\n")[0].split(": ")[1]
        
    project_code_list[board_project_code]["board_ids"].append(board_id)
    
    for g in board_result.json()["data"]["boards"][0]["groups"]:
        group_query = '{{boards (ids: {}){{groups(ids: {}) {{title items {{id}}}}}}}}'.format(board_id,g["id"])
        group_data = {'query' : group_query}
        group_result = requests.post(url=apiUrl, json=group_data, headers=headers)
        group_title = group_result.json()["data"]["boards"][0]["groups"][0]["title"]
        for i in group_result.json()["data"]["boards"][0]["groups"][0]["items"]:
            group_item_dict[i["id"]] = [board_name,group_title]<jupyter_output><empty_output><jupyter_text>Setup Border & Font & Color<jupyter_code>thin_full_border = Border(left=Side(style='thin'), 
                     right=Side(style='thin'), 
                     top=Side(style='thin'), 
                     bottom=Side(style='thin'))

thin_top_bot_border = Border(top=Side(style='thin'), 
                     bottom=Side(style='thin'))

grey_fill = PatternFill(start_color='F2F2F2',
                   end_color='F2F2F2',
                   fill_type='solid')

table_font = Font(bold=True, name='Arial', size=12)<jupyter_output><empty_output><jupyter_text>Create Timesheet<jupyter_code># datetimeFormat = '%Y-%m-%d %H:%M:%S UTC'
datetimeFormat = '%Y-%m-%dT%H:%M:%SZ'

for p in person_list:
    for pc in project_code_list:
        if p.replace(" ","_") in config[pc]:
            position_id,primary_position = config[pc][p.replace(" ","_")].split(",")
            surname,given_names,employee_number,dob,phone_number,rate,pay_code = get_congif_info(p)
            
            # create new excel
            workbook,sheet = create_excel()
            
            set_column_width(sheet)
            add_logo(sheet)
            add_timesheet_info(sheet)
            add_column_names(sheet)
            
            # add tasks from Monday.com
            row_num = 5
            
            for b in project_code_list[pc]["board_ids"]:
                query = '{{items_by_column_values (board_id :{}, column_id: "status", column_value:"{}"){{id name column_values{{text title value}}}}}}'.format(b, "Waiting for timesheet")
                data = {'query' : query}
                
                r = requests.post(url=apiUrl, json=data, headers=headers) # make request
                
                for v in r.json()["data"]["items_by_column_values"]:
                    workday_time_dict = {}
                    if v["id"] in group_item_dict:
                        person_board_name = group_item_dict[v["id"]][0]
                        person_group_name = group_item_dict[v["id"]][1]
                        person_job_title = v["name"]
                        job_number = v["id"]
                        ignore = 1
                        
                        # check if person in the group
                        for cv in v["column_values"]:
                            if cv["title"] == "Person":
                                # only group "meeting" OR "meetings" allows multiple people
                                if group_item_dict[job_number][1].lower() in {"meeting","meetings"}:
                                    if p in cv["text"]:
                                        ignore = 0
                                    else:
                                        ignore = 1
                                        continue
                                else:
                                    if "," in cv["text"]:
                                        print("----------------------------------")
                                        print("Error Group Name / Job Number: {} / {}".format(group_item_dict[job_number][1],job_number))
                                        print("Assigned to persons: {}".format(cv["text"]))
                                        print("Error : multiple persons are assigned to same task & it is not under meeting(s)")
                                        continue
                                    else:
                                        if cv["text"] == p:
                                            ignore = 0
                                        else:
                                            ignore = 1
                                            continue
                        
                        if ignore == 0:
                            for cv in v["column_values"]:
                                if cv["title"] == "Time Tracking":
                                    time_tracking = cv["text"]
                                    if time_tracking == "":
                                        time_used = 0
                                    else:
                                        for av in json.loads(cv["value"])["additional_value"]:
                                            # convert date string to datetime
                                            start_at = datetime.datetime.strptime(av["started_at"], datetimeFormat)
                                            end_at = datetime.datetime.strptime(av["ended_at"], datetimeFormat)

                                            # change to correct time zone
                                            start_at += datetime.timedelta(hours=int(time_difference))
                                            end_at += datetime.timedelta(hours=int(time_difference))
                                            
                                            work_day = str(end_at)[:10]

                                            # calculate time range
                                            diff = end_at - start_at
                                            time_used = round(diff.seconds/3600, 2)

                                            # aggregate by work day
                                            if work_day in workday_time_dict:
                                                workday_time_dict[work_day] += time_used
                                            else:
                                                workday_time_dict[work_day] = time_used
                            
                            # add timesheet created date
                            monday.items.change_item_value(b, int(v["id"]), "date7", {"date":timesheet_date})

                            subject_code = project_code_list[pc]["subject_code"]
                            for workday in workday_time_dict:
                                row_list = [surname, given_names, employee_number, person_board_name, person_group_name, person_job_title, job_number, position_id, primary_position, workday, pay_code, workday_time_dict[workday], rate, pc, subject_code]
                                sheet.row_dimensions[row_num].height = 25

                                for col, val in enumerate(row_list, start=1):
                                    sheet.cell(row=row_num, column=col).value = val
                                    sheet.cell(row=row_num, column=col).border = thin_full_border
                                    sheet.cell(row=row_num, column=col).fill = grey_fill

                                row_num += 1
                        else:
                            continue
                        
                    else:
                        print("Missing item id {} in board id {}".format(v["id"],b))
                
            if row_num > 5:
                # add_personal_info
                sheet.row_dimensions[row_num].height = 25
                sheet["A{}".format(row_num)] = "AUTHORISATION:"
                sheet["A{}".format(row_num)].font = table_font
                sheet["A{}".format(row_num)].border = thin_top_bot_border
                sheet.merge_cells('A{}:O{}'.format(row_num,row_num))

                row_num += 1
                sheet.row_dimensions[row_num].height = 25
                sheet["A{}".format(row_num)] = "Claimant:"
                sheet["A{}".format(row_num)].font = table_font
                sheet["A{}".format(row_num)].border = thin_full_border
                sheet["A{}".format(row_num)].alignment = Alignment(wrap_text = True, vertical='center')
                sheet["I{}".format(row_num)] = "Authorisation :"
                sheet["I{}".format(row_num)].font = table_font
                sheet["I{}".format(row_num)].border = thin_full_border
                sheet["I{}".format(row_num)].alignment = Alignment(wrap_text = True, vertical='center')
                sheet.merge_cells('A{}:H{}'.format(row_num,row_num))
                sheet.merge_cells('I{}:O{}'.format(row_num,row_num))

                row_num += 1
                sheet.row_dimensions[row_num].height = 25
                create_cell("A", "Name:", 0)

                create_cell("B", "{} {}".format(given_names, surname), 1)
                sheet.merge_cells('B{}:D{}'.format(row_num,row_num))

                create_cell("E", "DOB:", 0)

                create_cell("F", dob, 1)
                sheet.merge_cells('F{}:H{}'.format(row_num,row_num))

                create_cell("I", "Name:", 0)
                create_cell("J", project_code_list[pc]["name"], 1)
                sheet.merge_cells('J{}:K{}'.format(row_num,row_num))
                create_cell("L", "Department Name:", 0)
                create_cell("M", project_code_list[pc]["department_name"], 1)
                sheet.merge_cells('M{}:O{}'.format(row_num,row_num))

                row_num += 1
                sheet.row_dimensions[row_num].height = 25
                create_cell("A", "Phone:", 0)

                create_cell("B", phone_number, 1)
                sheet.merge_cells('B{}:D{}'.format(row_num,row_num))

                create_cell("E", "Date:", 0)

                create_cell("F", date, 1)
                sheet.merge_cells('F{}:H{}'.format(row_num,row_num))

                create_cell("I", "Phone:", 0)
                create_cell("J", project_code_list[pc]["phone"], 1)
                sheet.merge_cells('J{}:K{}'.format(row_num,row_num))
                create_cell("L", "Date:", 0)
                create_cell("M", project_code_list[pc]["date"], 1)
                sheet.merge_cells('M{}:O{}'.format(row_num,row_num))

                sheet.title = 'Timesheet'
                #save changes in excel
                workbook.save(filename="{}_{}_{}({}).xlsx".format(given_names,surname,timesheet_date.replace('-','_'),pc)) 
                time.sleep(5)
            else:
                continue
        else:
            continue<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12904696762425902, 'fraction_numerical': 0.008492932056543548, 'mean_word_length': 2.02395725611858, 'pattern_counts': {'":': 5, '<': 35, '<?xml version=': 0, '>': 36, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 68, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '8', 'dir': 'jupyter-structured-clean-dedup', 'id': '245292', 'license': 'MIT', 'n_tokens_mistral': 5106, 'n_tokens_neox': 4796, 'n_words': 971, 'path': 'monday_api_timesheet_detail.ipynb', 'repo_name': 'soda-lab/monday', 'size': '17568'}