How easy Kaiten and Adesk integration saves time
Hello everyone, today I want to describe the work on a task that we did in the company in order to get rid of routine operations.
I am a beginner developer in the MarkOnlineStudio team, and I want to talk about my work experience.
The fact is that our manager, Mark, uses Adesk to account for finances, where he enters expenses, income and other transactions. Our company also uses Kaiten as an internal Kanban board in which we keep track of task completion.
In order to understand how much time (and therefore money) was spent on the client, Mark manually “broke down” each expense transaction and entered the amount there according to the formula *formula* which he calculated using a table in Excel.
Each month, Mark sat down to complete this task to more transparently track time and money. It took him from 3 to 5 hours of working time to complete.
To get rid of this routine operation, we have developed a simple integration that automates the process. The script's algorithm is as follows:
The script analyzes data from Kaiten, including the amount of time spent on a client in minutes.
Then the script:
Checks the name of the counterparty.
Retrieves from Adesk all transactions of the “expense” type for the counterparty for the current month, where the expense item contains the word “Salary”.
The script summarizes all transactions for a given counterparty and the total number of minutes spent.
Next, he divides the sum of transactions by the total number of minutes, obtaining the cost of one minute of work.
This value is rounded to two decimal places.
Based on the received cost per minute, the script calculates the amount for each task.
A check is made to ensure that the sum of all tasks matches the total sum of operations.
The balance is entered into the “Cash” value
Transaction Breakdown
We also studied our Kaiten and realized that the human factor has a great influence on the formation of the upload, since if there is no label (or the presence of more than 1 label) on the task, the script does not work correctly. And since the tags in Kaiten and projects in Adesk must be the same, there should be no errors. To fix this, we have prepared a telegram bot that will check for tags on a task, and if it is missing, or has more than one tag, it will send a message to the administrator saying that this ticket was made incorrectly. Its algorithm is also quite simple:
The Telegram bot receives a request from the administrator which consists of the year and month, for example 2023-09
Connect via API to our Kaiten and make a request to receive the first 100 cards
Check for tags on cards
If there is no tag, send a message to the bot with the text No tag
If there is more than 1 tag, send a message to the bot
Repeat 25 times
After we receive problematic cards, we correct them; this action cannot be automated, since only the employee who created this card knows which project it belongs to.
Knowing that downloading from kaiten no longer has any problems, we can start working with the application.
We just need to select the upload file and specify the month and year to break down the transactions.
For the sake of autonomy of this integration, I added a simple UI made using customTkinter to it. The program looks like this:
After testing on the Adesk test portal, we started testing on real data, and found that everything worked fine, except for some errors in missing old projects. It took us 10 minutes instead of 4 hours to complete the breakdown of operations for 1 month.
So Mark saved ~3 hours and 50 minutes of work time per month with a very simple integration.
This is what the UI code looks like:
import customtkinter as ctk
import tkinter.filedialog
import tkinter as tk
import main # Импортируйте ваш основной скрипт здесь
class App(ctk.CTk):
WIDTH = 800
HEIGHT = 600
def __init__(self):
super().__init__()
self.title("Adesk Operation Splitter")
self.geometry(f"{self.WIDTH}x{self.HEIGHT}")
# Левая часть интерфейса
left_frame = ctk.CTkFrame(self)
left_frame.pack(side=tk.LEFT, fill=tk.BOTH, expand=True)
self.label_file = ctk.CTkLabel(left_frame, text="Вас приветсвует Адеск Operation Splitter!\n\n\nВыберите файл CSV:")
self.label_file.pack(pady=10)
self.button_file = ctk.CTkButton(left_frame, text="Обзор...", command=self.open_file_dialog)
self.button_file.pack(pady=10)
self.entry_file = ctk.CTkEntry(left_frame)
self.entry_file.pack(pady=10)
self.label_year = ctk.CTkLabel(left_frame, text="Введите год:")
self.label_year.pack(pady=10)
self.entry_year = ctk.CTkEntry(left_frame)
self.entry_year.pack(pady=10)
self.label_month = ctk.CTkLabel(left_frame, text="Введите месяц:")
self.label_month.pack(pady=10)
self.entry_month = ctk.CTkEntry(left_frame)
self.entry_month.pack(pady=10)
self.run_button = ctk.CTkButton(left_frame, text="Запустить", command=self.run_script)
self.run_button.pack(pady=20)
# Правая часть интерфейса для вывода консоли
right_frame = ctk.CTkFrame(self)
right_frame.pack(side=tk.RIGHT, fill=tk.BOTH, expand=True)
self.console_output = tk.Text(right_frame, height=20, width=100)
self.console_output.pack(pady=10, padx=10)
def open_file_dialog(self):
file_path = tkinter.filedialog.askopenfilename(filetypes=[("CSV files", "*.csv")])
self.entry_file.delete(0, ctk.END)
self.entry_file.insert(0, file_path)
def run_script(self):
print('Running script...')
file_path = self.entry_file.get()
year = self.entry_year.get()
month = self.entry_month.get()
# Здесь вам нужно будет модифицировать функцию main в main_script для логирования в виджет Text
main.main(file_path, year, month, self.console_output) # Обновите вызов функции main
if __name__ == "__main__":
app = App()
app.mainloop()And this is what the integration code looks like:
import requests
import pandas as pd
from datetime import datetime
import json
import urllib.parse
import ui
def read_kaiten_data(file_path):
df = pd.read_csv(file_path, delimiter=";", encoding='utf-8')
return df
def get_adesk_operations(api_token):
api_url = f'https://api.adesk.ru/v1/transactions?api_token={api_token}'
response = requests.get(api_url)
response.raise_for_status()
return response.json()
def get_adesk_projects(api_token):
api_url = f'https://api.adesk.ru/v1/projects?api_token={api_token}'
response = requests.get(api_url)
response.raise_for_status()
return response.json().get('projects', [])
def check_project_exists(project_name, projects):
return any(project.get('name') == project_name for project in projects)
def create_project_in_adesk(name, api_token):
print('Creating project in Adesk...')
api_url = f'https://api.adesk.ru/v1/project?api_token={api_token}'
data = {'name': name}
headers = {'Content-Type': 'application/x-www-form-urlencoded'}
response = requests.post(api_url, data=data, headers=headers)
response.raise_for_status()
print(response.json())
return response.json() # Предполагаем, что API возвращает информацию о созданном проекте
def read_kaiten_data(file_path):
df = pd.read_csv(file_path, delimiter=";", encoding='utf-8')
return df
def validate_kaiten_data(df):
for index, row in df.iterrows():
if pd.isna(row['Метки']):
print(f"Найдена строка с пустым значением 'Проект': {row}")
confirmation = input("Введите 'Y' для продолжения: ")
while confirmation.lower() != 'y':
confirmation = input("Введите 'Y' для продолжения: ")
def update_adesk_operation(operation_id, parts, api_token):
print(operation_id, 'update_adesk_operation')
api_url = f'https://api.adesk.ru/v1/transaction/{operation_id}?api_token={api_token}'
headers = {'Content-Type': 'application/x-www-form-urlencoded'}
parts_json = json.dumps(parts)
data = {
'is_splitted': 'true',
'parts': parts_json
}
data_encoded = urllib.parse.urlencode(data)
response = requests.post(api_url, headers=headers, data=data_encoded)
return response
def main(kaiten_file_path, year, month, console_output):
print(kaiten_file_path, year, month, console_output)
api_token = “ВАШ_ТОКЕН_ТУТ”'
kaiten_data = read_kaiten_data(kaiten_file_path)
validate_kaiten_data(kaiten_data)
existing_projects = get_adesk_projects(api_token)
adesk_operations = get_adesk_operations(api_token)
selected_year = int(year)
selected_month = int(month)
kassa_project_id = next((p['id'] for p in existing_projects if p['name'] == "КАССА"), None)
for contractor_name, group in kaiten_data.groupby('Пользователь'):
total_time = group['Сумма (м)'].sum()
projects = group.groupby('Метки')['Сумма (м)'].sum()
for operation in adesk_operations['transactions']:
if operation['dateIso'].startswith(f'{selected_year}-{selected_month:02d}') and "Зарплата" in operation.get(
'category', {}).get('name', '') and operation.get('contractor', {}).get('name') == contractor_name:
print(contractor_name)
total_operation_amount = float(operation['amount'])
operation_date = operation['dateIso']
category_id = operation.get('category', {}).get('id')
contractor_id = operation.get('contractor', {}).get('id')
parts = []
for i, (project_name, project_time) in enumerate(projects.items()):
if project_name in ["Внутренняя задача", "Первый контакт"]:
project_id = kassa_project_id
else:
if not check_project_exists(project_name, existing_projects):
raise ValueError(f"Ошибка: Проект '{project_name}' не найден в Adesk.")
project_id = next((p['id'] for p in existing_projects if p['name'] == project_name), None)
project_cost = total_operation_amount * (project_time / total_time)
if i < len(projects) - 1:
project_cost = round(project_cost, 2)
else:
print(project_cost)
if project_id:
part = {
"project": project_id,
"contractor": contractor_id,
"category": category_id,
"amount": project_cost,
"related_date": operation['dateIso']
}
parts.append(part)
else:
raise ValueError(f"Ошибка: Проект '{project_name}' не найден в Adesk.")
if parts:
total_parts_sum = sum(part['amount'] for part in parts[:-1])
parts[-1]['amount'] = round(total_operation_amount - total_parts_sum, 2)
response = update_adesk_operation(operation['id'], parts, api_token)
console_output.insert(ui.tk.END,
f"Операция по \"{contractor_name}\" суммой \"{total_operation_amount}\" от \"{operation_date}\"\n")
console_output.see(ui.tk.END)
else:
print(f"Operation {operation['id']} has no parts to update.")
response = update_adesk_operation(operation['id'], parts, api_token)
print(f"Operation {operation['id']} response: {response.json()}")
else:
print(operation['dateIso'], selected_year, selected_month, (operation.get('category', {}).get('name', '')), (operation.get('contractor', {}).get('name')),contractor_name)
if __name__ == '__main__':
try:
main()
except ValueError as e:
print(e)Technically, it is possible to go deeper into automating this process, but control is still necessary at intermediate stages. Maybe the bot will crash, maybe the project will be recorded incorrectly, and right now there will be an opportunity to quickly correct errors.
