Отчёт о прибылях и убытках из Айко в 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 и прямой слэш в названии счета

Усложним предыдущую задачу. Пусть в плане счетов задана такая вложенность счетов:

План счетов в iikoOffice со вложенностью счетов уровня >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 отчёте делает хитро: добавляет такой счёт самым последним подсчётом с припиской "прочие". Мы делаем так же.

#отчетысемёнафудова #iiko #айко