<jupyter_start><jupyter_text>Create End Year Monday.com Report-------  Import Libraries<jupyter_code>import requests
import json
import csv
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']
board_ids = config['DEFAULT']['Board-ID-List']
time_difference = config['DEFAULT']['Time-Difference']
year_report = config['DEFAULT']['Year-Report']<jupyter_output><empty_output><jupyter_text>Supporting Functions<jupyter_code>def write_csv(output_file,row, mode):
    with open(output_file, mode, newline='') as csvfile:
        csvwriter = csv.writer(csvfile, quoting=csv.QUOTE_MINIMAL)
        csvwriter.writerow(row)<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"]
    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>Create End Of Year Report<jupyter_code>column_name = ["person", "job_title", "group_name", "board_name", "time_tracking (hrs)", "timesheet_date"]
write_csv(f'{year_report}_monday_report.csv',column_name,"w")
datetimeFormat = '%Y-%m-%dT%H:%M:%SZ'

for b in board_id_list:
    query = '{{items_by_column_values (board_id :{}, column_id: "status", column_value:"{}"){{id name column_values{{text title value}}}}}}'.format(b, "Timesheet Created")
    data = {'query' : query}
    r = requests.post(url=apiUrl, json=data, headers=headers)
    
    for v in r.json()["data"]["items_by_column_values"]:
        ignore = 0
        for cv in v["column_values"]:
            if cv["title"] == "Timesheet date":
                if cv["text"][:4] == year_report:
                    timesheet_date = cv["text"]
                else:
                    ignore = 1

        if ignore == 0:
            if v["id"] in group_item_dict:
                board_name = group_item_dict[v["id"]][0]
                group_name = group_item_dict[v["id"]][1]

            job_title = v["name"]

            for cv in v["column_values"]:
                if cv["title"] == "Time Tracking":
                    time_tracking = cv["text"]
                    time_used = 0
                    if time_tracking != "":
                        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)


                if cv["title"] == "Person":
                    person = cv["text"]

            for p in person.split(","):
                row = [p,job_title,group_name, board_name, time_used,timesheet_date]
                write_csv(f"{year_report}_monday_report.csv",row,"a+")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14776559865092748, 'fraction_numerical': 0.00442664418212479, 'mean_word_length': 2.967391304347826, 'pattern_counts': {'":': 4, '<': 25, '<?xml version=': 0, '>': 25, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '259561', 'license': 'MIT', 'n_tokens_mistral': 1521, 'n_tokens_neox': 1481, 'n_words': 270, 'path': 'monday_end_year_report.ipynb', 'repo_name': 'soda-lab/monday', 'size': '4744'}