Отчёт о прибылях и убытках из Айко в Google Sheets
Если вам нужно регулярно и автоматически выгружать из iiko отчёт о прибылях и убытках (PL, PnL, P&L, profit and loss) в Google Sheets и/или далее во внешнюю систему аналитики, то есть решение.
- Выгружать из Айко отчёт о прибылях и убытках по запросу, по расписанию или как угодно. Используя iikoServer API и отслеживая актуальный план счетов.
- Выполнять произвольную обработку этого отчёта: группировки, сортировки, обогащения данных.
- Отправлять собранные данные куда угодно: в Гугл Таблицы в нужные ячейки нужных листов, через telegram бота управляющему, в коннекторы к DataLens и PowerBI.
Постановка и решение задачи
На FOOD EXPO 2024 мы познакомились с Александром Митраковым, ресторатором и основателем ресторанной группы 075 Group. Поговорили об автоматизации ресторанного дела вообще, об iiko конкретно, рассказали о наших возможностях и... разъехались по домам.
А уже через пару недель приступили к решению большой задачи от Александра: выгрузить отчёт о прибылях и убытках из Айко во внешнюю систему аналитики.
PL отчёт формировали впервые, поэтому на исследование и сборку прототипа ушёл почти месяц. Это одно из наших самых объёмных исследований, которое началось с реверс-инжиниринга iikoBackOffice, HTTPS-MITM и разбора непубличного метода API /resto/services/accounting_reports?methodName=selectProfitAndLossByDateDepartmentConception, а завершилось построением дерева счетов на основании OLAP отчёта по проводкам.
Разработчикам WireShark и Postman: спасибо 💪! Без вас и ваших инструментов не справились бы!
На каких условиях разрабатываются такие интеграции Айко?
Обсуждаем с заказчиком проблему и пути решения, согласовываем итоговую форма отчёта и приступаем к исследованиям и разработке. Если идея на миллион (мы уверены, что отчёт будет полезен многим пользователям платформы Fudov Smart), то разработка за наш счёт. А заказчик приобретает подписку на отчёт по типовой цене из прайса.
Да, все эти данные можно найти и в интерфейсе iiko. Но вот показать всё в одном окне браузера на любом устройстве, обогатить данные, добавить нужные фильтры, формулы для накопления и усреднения, сделать индивидуальную раскраску полей, требующих внимания, — тут поможет Семён Фудов.
Мы уже более 5 лет занимаемся разработкой интеграций Айко и подобных сервисов. А ещё у нас своё курьерское приложение и telegram боты, которые беспощадно истребляют рутину и поднимают автоматизацию ресторана и доставки на новый уровень. Если заинтересовались — пишите на почту hello@fudov.ru.
Технические детали решения
Как это всё устроено внутри, по шагам:
- Подключиться к облачному серверу Айко заказчика с помощью iikoServer API.
- Выгрузить OLAP отчёт по проводкам (транзакциям).
- Правильно разобрать ответ и построить дерево счетов.
- Правильно разобрать подытоги по строкам и столбцам.
- Сформировать отчёт о прибылях и убытках.
- Выполнить все необходимые постобработки и обогащения.
- Подключиться к точке назначения: Google Sheets, Telegram, DataLens, PowerBI, куда угодно.
- Отправить сформированный отчёт.
Вуаля! Кажется, что всё просто. Но мы помним про дьявола и про то, где он прячется.
Подводные камни
Вложенность уровня 4
Строим дерево (план) счетов на основании OLAP отчёта по проводкам и сравниваем с отчётом о прибылях и убытках в Айко. Находим расхождения, когда вложенность счёта более 3.
{ "Account.AccountHierarchyFull": "РАСХОДЫ ОТЧЕТ/2. Персонал, ФОТ/2.1. Зарплата /2.1.1. Зарплата по ведомостям/2.1.1.11 ФОТ Доставка", "Account.AccountHierarchySecond": "2. Персонал, ФОТ", "Account.AccountHierarchyThird": "2.1. Зарплата ", "Account.AccountHierarchyTop": "РАСХОДЫ ОТЧЕТ", "Account.Code": "", "Account.Group": "INCOME_EXPENSES", "Account.Name": "2.1.1.11 ФОТ Доставка", "Account.Type": "EXPENSES", "Department": "Мой ресторан", "Department.Code": null, "Sum.ResignedSum": 13415, "TransactionType": "CUSTOM" }
Счёт с названием "2.1.1.11 ФОТ Доставка" имеет "вложенность уровня 4" и должен быть виден в дереве счетов вот так:
РАСХОДЫ ОТЧЕТ 2. Персонал, ФОТ 2.1. Зарплата 2.1.1. Зарплата по ведомостям 2.1.1.11 ФОТ Доставка
Однако в OLAP отчёте предусмотрено всего лишь три поля для получения иерархии:
- "Account.AccountHierarchyTop": "РАСХОДЫ ОТЧЕТ"
- "Account.AccountHierarchySecond": "2. Персонал, ФОТ"
- "Account.AccountHierarchyThird": "2.1. Зарплата "
Выход нашли такой: взять полное название счета "Account.AccountHierarchyFull": "РАСХОДЫ ОТЧЕТ/2. Персонал, ФОТ/2.1. Зарплата /2.1.1. Зарплата по ведомостям/2.1.1.11 ФОТ Доставка", поделить на части по символу "прямой слэш" и так узнать полную иерархию. И вообще, решили для себя добавить виртуальные поля "Account.AccountHierarchyFourth" и так далее.
Вложенность счетов уровня >3 и прямой слэш в названии счета
Усложним предыдущую задачу. Пусть в плане счетов задана такая вложенность счетов:
В счёте "Зарплата" есть подсчёт "ЗП 1", в нём есть подсчёт "ЗП 1 / 1" и так далее. Заметим, что в названии счета "ЗП 1 / 1" умышленно стоит прямой слэш. Сейчас увидим, какие сложности при этом возникают.
Создадим проводку по самому вложенному счёту "ЗП 1 / 1 / 1 / 1 / 1". И найдём эту проводку в OLAP отчёте по проводкам.
{ "Account.AccountHierarchyFull": "Зарплата/ЗП 1/ЗП 1 / 1/ЗП 1 / 1 / 1/ЗП 1 / 1 / 1 / 1/ЗП 1 / 1 / 1 / 1 / 1", "Account.AccountHierarchySecond": "ЗП 1", "Account.AccountHierarchyThird": "ЗП 1 / 1", "Account.AccountHierarchyTop": "Зарплата", "Account.Code": "", "Account.Group": "INCOME_EXPENSES", "Account.Name": "ЗП 1 / 1 / 1 / 1 / 1", "Account.Type": "EXPENSES", "Contr-Account.Code": "", "Contr-Account.Group": "INCOME_EXPENSES", "Contr-Account.Name": "ЗП 1 / 1 / 1 / 1 / 1", "Contr-Account.Type": "EXPENSES", "Department": "Мой ресторан", "Department.Code": null, "Sum.ResignedSum": 0, "TransactionType": "CUSTOM" }
А теперь вопрос для знатоков: как построить дерево (план) счетов? Если в OLAP отчёте предусмотрено всего лишь три поля для получения иерархии?
Вариант "разобрать полное название счета Account.AccountHierarchyFull по символу разделителя — прямому слэшу" не предлагать. Мы же специально усложнили задачу: вставили этот символ в название счета (и это кейс из реальной практики, в Айко даже по умолчанию есть счёт "Доходы/Расходы").
Задали вопрос поддержке API и получили вот такой ответ:
Сейчас есть возможность получить только три уровня вложенности
Account.AccountHierarchyTop (Счет 1-го уровня); тип: STRING
Account.AccountHierarchySecond (Счет 2-го уровня); тип: STRING
Account.AccountHierarchyThird (Счет 3-го уровня); тип: STRING
Это оптимальный уровень вложенности для большинства случаев, для более сложных случаев вы можете использовать Account.AccountHierarchyFull (согласуйте замену символа с заказчиком) или вы можете использовать "Account.Code": "" (6.01.1 --> 6.01.4) для формирования иерархии.
Можно было бы попросить бухгалтерию переименовать счета, в названии которых есть прямой слэш. Но в управляющей компании нашего клиента план счетов уже устоялся, ведётся давно, над этим планом сделано множество интеграций, связи в которых сделаны и по названиям счетов.
Поэтому выкручиваемся на своей стороне. Точнее, пока проблем нет: xорошо, что счёт такой один и он только на четвёртом уровне иерархии.
Проводки по родительскому счёту
При построении дерева счетов почему-то мы подумали, что проводок по родительскому счёту не может быть. Только по его подсчетам. Но это не так, такие случаи возможны. Пример проводки:
{ "Account.AccountHierarchyFull": "РАСХОДЫ ОТЧЕТ/11. Текущие ремонты и ТО в ресторане", "Account.AccountHierarchySecond": "11. Текущие ремонты и ТО в ресторане", "Account.AccountHierarchyThird": null, "Account.AccountHierarchyTop": "РАСХОДЫ ОТЧЕТ", "Account.Code": "", "Account.Group": "INCOME_EXPENSES", "Account.Name": "11. Текущие ремонты и ТО в ресторане", "Account.Type": "EXPENSES", "Department": "Мой ресторан", "Department.Code": null, "Sum.ResignedSum": 80000, "TransactionType": "INCOMING_SERVICE" }
Видно, что название счёта "Account.Name" совпадает с иерархией второго уровня "Account.AccountHierarchySecond". Айко в PL отчёте делает хитро: добавляет такой счёт самым последним подсчётом с припиской "прочие". Мы делаем так же.