Z for ZАКУПІВЛІ. Як ми створили веб-аплікацію, що відстежує шлях державних грошей у приватні руки

Лупати сю скалу взявся Влад Герасименко з його командою роботів. Місяцями вони структурували хаос отриманих даних. Зрештою з величезного числа таблиць Влад вибрав лишень ті, які містили потрібну для створення нашого інструменту інфу. Але це був тільки початок довгого шляху...

Текст: Петро ЗУЙОК

В березні 2012 року ТЕКСТИ створили інструмент для дослідження державних закупівель.

Кожен охочий міг серфити наявною на той момент базою даних (БД), обираючи з-поміж кількох ключових параметрів: період часу, продавець, покупець, сума контракту, галузь.

Інструмент назвали просто - “Закупівлі”:

Цей проект став одним із найпопулярніших на ТЕКСТАХ за відвідуваністю і мав постійну аудиторію. Того ж року він став фіналістом міжнародного конкурсу Data Journalism Awards.

Версія 1.0 - партизанська. Як ми її робили

В основу Зaкупівель 1.0 лягла інформація, отримана за допомогою скрипта-партизана (невеликої програми), котрий “скрейпив” (скачував) і “парсив” (обробляв) дані з порталу державних закупівель на сайті Мінекономіки.

Викачування всього наявного на той момент архіву (з 2008 року) зайняло тиждень. Далі ми оновлювали базу в напівавтоматичному режимі, кожні 7-10 днів вручну запускаючи нашого робота у свіжовикладені номери “Вісника”.

Так тривало до весни 2014 року, коли Мінекономіки очолив Павло Шеремета. На порталі держзакупівель встановили “капчу” - тест, що блокує доступ комп’ютерним програмам. “Введіть символи, які ви бачите на картинці”, оце все.

До серпня 2014-го вдавалося обходити “капчу” за допомогою натренованої нейронної мережі, але потім захист посилили. На цьому наш напівлегальний “скрейпінґ” закінчився.

Це був кінець і для першої версії Закупівель. База припинила оновлюватися взагалі.

Напіввідкриті дані

У 2015 році в інтернеті з’явився свіжий архів даних із “Вісника…”. Оприлюднили його волонтери - з ГО "Прозорі Закупівлі" та інших ініціатив.

Вони домовились із “Зовнішторгвидавом” й отримали всю наявну на той момент базу.

450 гігабайт інформації у форматі бази даних Oracle. Всі тендери з 2008-го року. Останній бюлетень у цьому архіві датувався 20 березня 2015-го.

Також ці небайдужі люди описали скачану базу. Опис займав близько 140 сторінок. Там розповідалося про таблиці, з яких складалися дані, про значення полів у цих таблицях, про типи даних та сотні зв’язків між полями у базі.

Ілюстрація indigocean з Devianart

Наприклад, таблиця CONTRACT (для угод) поміж інших містила в собі поля Contract_ID - “номер контракту” і Purchase_ID - “номер закупівлі” - який вказує на зовсім іншу таблицю, з даними по закупівлях.

А з таблиці WINNER можна було витягнути реєстраційні дані переможців торгів.

Дані найважливішої для нашого проекту таблиці - "Угоди":

Це тільки одна таблиця. Подібних у базі - десятки і сотні.

Уявіть: з 2008 року команда програмістів - а то й не одна - оновлювала портал з держзакупівель, архівуючи найрізноманітніші параметри торгів.

Таблиці народжувалися та вмирали, залишаючи сліди, що накопичувалися в базі, ніби археологічні шари (власне, щоб зрозуміти, як працює БД, треба провести її “розкопки”).

Таке нагромадження інфи важко назвати ВІДКРИТИМИ даними. Робота з Oracle потребує неабиякої підготовки, що виключає доступ до бази для більшості зацікавлених.

Скоріше це були НАПІВВІДКРИТІ ДАНІ - сировина, з якої треба було видобути потрібну для оновленого проекту інформацію.

Бо таблиць було дуже багато. Як для наших скромних планів - аж занадто.

Так перед ТЕКСТАМИ постала

ГОЛОВНА ПРОБЛЕМА: відфільтрувати з півтерабайта інфи потрібні нам дані

Отже, з купи таблиць Oracle треба було витягнути:

- закупівлі, які відбулися (відкинувши всі інші, приміром, скасовані);

- угоди. Власне, все інше нас і не цікавило, тому з таблиці CONTRACT ми й почали. З Oracle ми отримали такі незмінні дані про кожну угоду:

- дата підписання контракту,

- чи включено ПДВ,

- сума контракту у гривні (а оскільки деякі угоди були в іноземній валюті, Владу довелося написати програму-калькулятор, яка дивиться на сайті НБУ курс валют на відповідну дату і перераховує),

- номер угоди не був незмінним - просто порядковий номер рядку в таблиці. Унікальний номер треба було шукати в інший таблиці.

- замовник (державна установа) і його реєстраційні дані;

- переможець тендеру (приватна фірма) і його реєстраційні дані;

- номер “Вісника…”, в якому оголошено переможця.

Лупати сю скалу взявся учасник команди ТЕКСТІВ Влад Герасименко.

Як структурувати хаос

Схема структури бази даних Oracle - з таблицями, зв’язками та підкресленими змінними, які нас найбільше цікавили - була такого масштабу, що сподобалася би навіть Керрі Метісон, аналітику ЦРУ з серіалу “Homeland” :)

Ось так виглядала робоча схема Керрі Метісон:

А ось - НЕЗНАЧНА ЧАСТИНА робочої схеми Влада Герасименка:

КЛІКАЙТЕ, ЩОБ ЗБІЛЬШИТИ

Це тільки шматочок таблиць бази Oracle і зв’язків між ними. Влад вибрав і зобразив лишень ті з них, які містять потрібну для створення нашої віб-аплікації інформацію.

Інші таблиці на схемі не показані - було би набагато більше і значно заплутаніше. Куди там аналітикам ЦРУ.

Кожен прямокутник на Владовій схемі - таблиця. Рядки у прямокутнику - стовпці даних. Із них ми обирали тільки найнеобхідніші, а інші безжалісно різали.

Влад упорядковує хаос :)

Сортуючи хитросплетіння інформаційного матеріалу, ми поступово відкидали все несуттєве.

Робота просувалася: за хаотичними фавелами таблиць уже потроху вимальовувалася струнка споруда оновленої бази.

…аж тут з’явилося Prozorro.

Дві платформи і різниця між ними

Було одне джерело інформації про тендери (“Вісник…”), тепер стало два.

Держтендери переходять на майданчик Prozorro поступово, зараз частина тендерів організовуються через Вісник, частина - через Prozorro. Всі закупівлі мають перейти на Prozorro з осені.

Структура даних у цих двох платформ відрізняється.

Дані “Вісника…” віднедавна викладаються у вигляді xml-файлів - текстових документів, які може прочитати і програма, і людина. Натомість на Prozorro є сервіс, який надає API - cвого роду набір готових програмних блоків, з якими можуть працювати сторонні програми.

Prozorro іноді не уніфікує дані, які нібито легко можна було б уніфікувати.

Наприклад, у їхніх формах немає випадаючого списку для регіону - в результаті маємо Луцьку область або Віницьку через латинську i. Або взагалі нічого. Можуть і “Середзем’я” написати.

Інший приклад - відсутня дата підписання угоди. Є дата проведення тендеру і дата публікації його результатів, а дати підписання - нема.

Трапляються дивні речі з полем Supplier (“постачальник”) - це аналог поля Winner (“переможець”) у “Віснику…” Скажімо, таке собі ТОВ “Тестова компанія” продала державі зефіру та пряників на 1,7 мільйона гривень.

Поля заповнені чим попало - справді схоже на тест

Сліди тестування системи? Але в полі “результат тендеру” зазначено “active” - тобто гроші реальні.

Пошукайте в Зaкупівлі 2.0 приватну фірму з словом “Тестова”, там кілька таких випадків. Все ж таки мільйони державних коштів (поки немає іншого пояснення).

Ще одна специфіка Prozorro - поля “методи торгів” і “типи торгів”, які відрізняються від аналогічних даних “Вісника…”

В своїй базі - своя правда

Структуруванням хаосу не обмежилося.

Для справжнього відкриття всіх цих різноманітних даних широкій публіці треба було створити ЄДИНУ БАЗУ ДАНИХ - з трьох різних форматів, з кількох неоднакових баз:

1. Стара база Zakupivli 1.0 - дані з “Вісника…” з 2008-го до серпня 2014 року,

2. База Oracle - скачаний волонтерами архів “Вісника…”, з 2008-го до 20 березня 2015 року,

3. Публікації “Вісника…” з 20 березня 2015 року і до сьогодні (xml-файли - вони викладаються окремо від сторінок сайту і не захищаються “капчею”. Тобто нарешті з’явилася можливість автоматичного оновлення),

4. База Prozorro, з її постійними оновленнями.

Через кілька місяців напруженої роботи Влад Герасименко вирішив усі ці завдання. І ще з десяток дрібніших.

Робота кипить

Вагомою частиною роботи стало ГРУПУВАННЯ - щоб одна й та ж установа не записувалася 15 разів під різними іменами, а була уніфікована.

Бо наразі у таблиці WINNER приватне підприємство з назвою, скажімо, “Беркут” могло бути записане як “ПП “Беркут”, “ПП Беркут” або просто “Беркут”.

"Беркути" серед учасників торгів. Тисячі їх!

Один із зручних параметрів уніфікації - унікальний код ЄДПРОУ (ідентифікаційний номер юридичної особи в Єдиному державному реєстрі підприємств та організацій) або - якщо йдеться про фізичних осіб-підприємців - індивідувальний податковий номер (ІПН).

Є і той "Беркут"

Версія 1.0 не містила інформації про коди й номери. Для отримання цих даних нашому роботу довелося б “пірнати” в надра урядового сайту на додатковий рівень глибше, що могло значно сповільнити отримання інфи.

Bлад і його команда

Для групування Влад написав чергового робота, який обирав для кожного підприємства якомога повніші дані - назву, яка фігурує найчастіше і найдовшу з адрес.

Також робот “чистив” код ЄДРПОУ (номер ІНН). Прибирав пробіли (перед кодом, після нього або всередині між цифрами), прибирав літери (у частини людей, які заповнюють форми на участь у тендерах, є порочна практика писати слово “код” перед цифрами ідентифікатора).

Нотатки-завдання з робочого місця Влада. На верхньому фото ліворуч - стопка вже виконаних

Часто у формі вказуються два коди - особливо коли йдеться про регіональне представництво материнської компанії.

Особливо цим страждають установи на кшталт “Укрпошти” чи “Укртелекому” - спершу пишуть код головного офісу, після чого додають “в особі N-ського районного відділення” і вказують власне свій код.

Іноді код ЄДРПОУ записують деінде - наприклад, у полі “Назва підприємства” або взагалі “Дата”. Але в 99% випадків цей унікальний ідентифікатор можна знайти.

1%, який залишився - фріки на кшталт ТОВ ЦЕСК “Еско-Центр”, котрі замість цифр вказують у відповідному полі “НЕ ДЛЯ ДРУКУ”.

Материнська компанія Hyundai не має ЄДРПОУ, бо знаходиться в Південній Кореї. Продала "Укрзалізниці" поїзди і тренажер для машиністів - на 2,5 млрд

В результаті роботи Влада і його працелюбних роботів ми СТВОРИЛИ СВОЮ ВЛАСНУ БАЗУ всіх державних закупівель - ~~з блекджеком~~ повну та уніфіковану.

В цій базі зберігаються наступні дані:

- дата;

- покупець (отримувач державних коштів) і дані по ньому - код ЄДРПОУ або номер ІПН, назва, адреса (не відображається в Закупівлях 2.0), контакти (не відображаються);

- продавець (державна установа) і дані по ньому - ті ж самі, що й у продавця;

- що саме купується/продається (на що витрачені державні кошти);

- cума угоди (скільки витрачено державних коштів) у гривнях.

Додавши пошук за ключовими словами і запакувавши нашу БД у зручний інтерфейс, ТЕКСТИ cтворили оновлену версію інструменту для дослідження тендерних даних.

В ній - усе суттєве про державні закупівлі в Україні з 2008 року.

Користуйтеся на здоров’я: для задоволення власної цікавості та примноження суспільного блага.