Свой BI для стройки без интернета: как реестр документов уехал из общего Excel в PostgreSQL

Типичный выбор пользователя общего свода.

На стройке завода учёт документов поставщиков жил в двух книгах Excel с макросами. Книги лежали на общем диске, правили их два десятка человек, и кто первым открыл свод утром, тот и работал, остальным доставалось «Только чтение». Комментарии жили в одном столбце, и выигрывал тот, кто сохранил последним.

За три месяца всё это переехало в PostgreSQL, а поверх базы вырос свой маленький BI: дашборды, S-кривые, тепловые карты просрочек и карточки поставщиков. Без Power BI, без облака и без интернета — одна HTML-страница и сервер на Python в закрытой сети завода. Ниже — как это выглядит, как переезжали и на чём спотыкались.

Коротко:

• около 8 тысяч документов двух линий живут в базе, книги Excel больше не открываются;

• отчёт — свой BI на одной странице: дашборд, отчёт за неделю, переходы кодов, просрочки, карточка каждого поставщика;

• переезд засчитали, только когда сверка с Excel показала ноль необъяснённых расхождений;

• новый документ встаёт на своё место в 81 % случаев вместо 47 % и ни разу под удалённой строкой;

• замечания заказчика видны прямо у кода рассмотрения, лист открывается в том же окне;

• сервер стоит в закрытой сети, обновления ставит человек на месте, с копией базы и откатом.

Завод, заказчик и поставщики убраны, шифры выдуманные, цифры изменены: соотношения и выводы те же, точные значения — нет.

Что за реестр

К каждому насосу, компрессору или щиту поставщик обязан сдать пачку документов: чертежи, расчёты, паспорта, программы испытаний. Их перечень называется VDR (Vendor Document Register, реестр документации поставщика): одна строка — один документ с шифром, ревизией и плановой датой.

Дальше документ ходит по кругу. Поставщик присылает его с трансмиталом (TRM, сопроводительное письмо), заказчик возвращает лист замечаний CRS (Comment Review Sheet) и ставит код: A — принято, B — принято с замечаниями, C — переделать. Поставщик вздыхает, выпускает новую ревизию, и круг повторяется, пока не будет A. Некоторые документы ходят по этому кругу дольше, чем строится сам насос.

У нас две линии завода, на каждую свой свод .xlsm: вкладки поставщиков по 27 столбцов, лист итогов на формулах и макросы, которые всё пересчитывают. Поверх сводов я когда-то сделал отчёт — HTML-страницу с дашбордом и просрочками, её раз в час собирает из книг небольшой сервер на Python.

Как это выглядит сейчас

Power BI или Tableau под один реестр — стрельба из пушки по воробьям, а облачные сервисы в закрытую сеть завода не пустят. Поэтому BI получился свой: одна HTML-страница, которую раздаёт сервер, данные из PostgreSQL, графики на SVG, нарисованные самим отчётом, без внешних библиотек. Страницу можно даже скачать одним файлом и открыть без сервера — например, на совещании, где сети нет.

Главный экран. Всё, ради чего раньше открывали три листа свода и калькулятор.

Мой любимый экран — «Переходы кодов». Слева код, который документ получил, справа — какой получил на следующей ревизии, толщина ленты — сколько документов так прошли. Розовые ленты идут вниз, от A к B или C: это документы, которым стало хуже.

Справа рейтинг поставщиков: чем длиннее красная полоска, тем грустнее поставщику.

Отчёт за неделю. Неделя считается от вторника, потому что совещание по вторникам, — так попросили.

Тепловая карта просрочек. Самая тёмная ячейка — 433 дня, у этого документа уже можно праздновать день рождения.

У каждого поставщика своя карточка: сколько сдал, что висит у заказчика, что у самого поставщика и как он шёл по плану.

Данные на снимках обезличены: поставщики, шифры и числа выдуманные.

Пока все эти экраны читали Excel, они были красивой витриной над хрупкими книгами. Дальше — как витрина получила нормальный фундамент.

Как Excel сдался

Пока отчёт только читал книги, жили мирно. Потом коллеги резонно спросили: «А можно комментарий написать прямо в отчёте, не открывая этот свод?» Можно. Только записать его надо в книгу, а книга, как вы помните по обложке, всегда кем-то открыта.

Сначала сервер просто выкидывал человека из книги через Excel. Людям это предсказуемо не понравилось, и я сделал вежливо: операция отказывает и сообщает, кто держит книгу. Вежливость не спасла, потому что книгу держали всегда, просто теперь об этом сообщали культурно.

Понедельник, 9:02. Свод открыт у всех, кроме того, кому он нужен.

С комментариями было ещё веселее. Столбец L один на всех, побеждает последний сохранивший. Историю правок пришлось вести отдельно, в json-файлах рядом с отчётом, и каждый раз выяснять, какой текст правильный: в книге или в истории.

Добили формулы. Я сверил итоги отчёта со сводом до единицы и нашёл ошибки в самих книгах. Шесть документов без плановой даты выпадали из итога: формула считала 431 вместо 437, и никто этого не замечал. А у одного поставщика ячейка итога оказалась без формулы — там просто стоял ноль, и отдел-рецензент числился образцово безупречным: ноль замечаний вместо пяти.

Тормоза я, кстати, вылечил и без базы: лист поставщика рисовался 3,1 секунды, стало 0,26, а на ноутбуке в цехе с 19 секунд до 2,4. Но к этому моменту было понятно, что чинить Excel можно до пенсии.

Переезд: сначала сверка, потом всё остальное

Таблицы в базе нарисовать несложно. Сложно убедить людей, что база не потеряла ни одной их запятой. Поэтому сверку с Excel я написал раньше самого переноса: она проходит по каждому документу и каждому полю и раскладывает расхождения на две кучи. В первой — объяснённые, например итог на формуле с зашитым диапазоном, который в книге просто не дотягивается до новых строк. Во второй — необъяснённые, и переезд засчитывается, только когда она пустая.

Админку сделал похожей на Excel: таблица на Tabulator, те же столбцы, те же цвета. Ничего революционного, зато учиться заново никому не пришлось.

Репетиции гонял на копиях настоящих книг, последняя выглядела так:

Несовпавшие комментарии оказались одним и тем же общим шифром, который живёт в обеих книгах с разным текстом. Объяснено, оставлено как в книге своей линии.

Без позора не обошлось. Я запустил сервер из свежей копии репозитория, где не было собранной страницы отчёта. Сервер в таком случае заботливо ищет её на папку выше. Там лежали настоящие своды, и девять моих тестовых комментариев уехали прямо в рабочие книги. Спасла резервная копия, а у меня появилось правило, которое я теперь повторяю перед каждым запуском как молитву.

В день переезда книги закрыли, на сервере нажали перезапись, сверка показала ноль, и следом нажали «Больше не брать из Excel». С этой кнопки сервер книги не видит вообще. Свод L1 теперь просто файл на диске, который иногда открывают по старой памяти, чтобы убедиться, что он больше никому не нужен.

Вечерняя проверка: сначала повторить макрос

У людей был макрос «Проверить трансмиталы»: обходит папки TRM и CRS и сверяет их с реестром. Ему верили. Я сделал такую же проверку на сервере, каждый вечер, с отчётом в Excel, и первый же отчёт пришёл на 2 410 строк. В 588 из них было написано «CRS не прочитан — нет файла».

Выглядело так, будто сервер ослеп. Причина нашлась быстро: в папке CRS кроме бланка трансмитала лежат листы замечаний, ответы и прочее, моя проверка брала первую попавшуюся книгу, а макрос — книгу с номером трансмитала в имени. Ещё 131 строка упиралась в путь длиннее 260 символов: Windows помнит девяностые и не стесняется этого.

Переписал выбор книги ровно как в макросе, и шум ушёл.

С тех пор у меня правило: если у людей есть макрос, которому они верят, проверка на сервере сначала повторяет его один в один, а умничает уже потом.

Куда вставить новый документ

Свежая история. Заказчик прислал фото окна «Добавление документа»: новый шифр встал не туда, а сверху висело грозное «номер вне диапазонов поставщика», которое было неправдой.

Правило порядка в реестре нигде не записано, мне его объясняли в три подхода, каждый раз добавляя «ну и ещё». В итоге получилось так: сначала поставщик, внутри него блок оборудования, дальше серия номеров, тип документа и только в самом конце алфавит. Удалённые и заменённые строки остаются в реестре чёрным цветом, в порядке не участвуют, и вставлять под них нельзя. Ставить новый документ под удалённый — примерно как подселять жильца в квартиру к привидению.

Старая логика искала соседей только среди документов с тем же номером и честно ставила новый чертёж к чужому блоку. Новая сначала находит блок, а уже потом место в нём:

блок = блок_поставщика(форма) or блок_по_номеру(шифр) or блок_по_серии(шифр)
кандидаты = той_же_серии(живые_строки_блока(блок), шифр)
место = ближайший_по_порядку(кандидаты, тип_и_алфавит(шифр))

На трёх примерах такое не проверишь, поэтому я устроил реестру экзамен: вынимал по очереди каждый из 5 874 живых документов и просил программу вставить его обратно. Встал между теми же соседями — зачёт.

Экзамен «вынуть и вставить обратно» по всему реестру.

Под удалённую строку старая логика ставила документ 1 170 раз, новая — ни разу. Оставшиеся 14 % промахов почти все приходятся на блоки, которые люди сами когда-то расставили не по своему же правилу. Тут программа права, но спорить с историей реестра себе дороже.

Ложное «вне диапазона» вышло из-за имён: форма подставляла короткое название поставщика из столбца реестра, а диапазоны номеров записаны по полному названию его вкладки. Под коротким именем жили совсем другие документы, с номерами 1–149, и программа добросовестно сообщила, что 4302 в этот диапазон не входит. Теперь она смотрит все вкладки с этим коротким названием разом.

Замечания прямо у кода

Раньше вопрос «за что нам поставили C?» решался квестом: найти папку CRS, найти в ней лист замечаний именно этого документа, открыть в Excel. Теперь сервер раз в 10 минут сам разбирает листы замечаний в базу, а рядом с кодом в отчёте появляется отметка: «· 4 откр.» красным или «· 16 ✓» серым. Щелчок — и окно со списком: кто написал, что ответили, что ещё открыто.

Кнопка «Открыть лист» в первой версии честно скачивала Excel. Почти сразу пришло: «А можно прямо тут посмотреть? И чтобы увеличить». Можно. Лист теперь открывается в том же окне на весь экран, крутится колесом, а «Скачать» осталась рядом для верных поклонников Excel.

Данные на макетах выдуманные.

Как туда попадают обновления

Сервер стоит в закрытой сети завода: ни интернета, ни удалённого доступа. Обновление ставит человек на месте по инструкции на одну страницу. Скрипт установки сам делает копию базы, меняет файлы и пишет журнал, а если что-то не встало, возвращает всё как было. Откат есть и в админке, одной кнопкой.

Однажды обновление уронило у заказчика внесение трансмиталов. Новый файл звал функцию соседнего модуля, которого в архиве не было, а у заказчика стояла старая версия этого модуля, без нужной функции. У меня все тесты были зелёные, потому что у меня-то всё свежее. С тех пор каждое обновление включает в себя все предыдущие, перед отправкой скрипт проверяет, что всё нужное новым файлам на сервере есть, а установку я сначала репетирую на копии.

Когда до сервера не дотянуться, любая ошибка обходится в день переписки и фотографию экрана, поэтому всё, что можно проверить до отправки, проверяется до отправки.