Джерело: Тексти.org.ua, автор: Петро Зуйок

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Поїзди і тренажер для машиніста

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

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

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

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

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

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

P.S. Наразі в базі:

• інфа про закупівлі на майже 2 трильйони гривень
• загальна сума – 1 976 813,87 мільйонів гривень
• всього угод – 788 523 контрактів.