Всё началось с задачи создания отчёта по взаиморасчётам с клиентами и поставщиками. Маячила перспектива собирать все данные руками. Целый отдел из 7 девочек не знал, как подступиться к этому, учитывая, что база состояла более чем из 180 тыс. строк и велась в MS Access.
О чём речь
В одном крупном рекламном агентстве есть «зоопарк». В смысле «зоопарк» различных информационных систем, которые создавались годами силами самих сотрудников, пытавшихся хоть как-то автоматизировать ручную работу. В этой статье речь пойдёт об автоматизации работы отдела контроля медиазакупок (сокращённо «КЗ»). Это такое подразделение, контролирующее какой площадке сколько денег заплачено и нет ли переплат/недоплат. Важные люди для бизнеса, проще говоря. Так сложилось, что в этом отделе с 2010 года ведётся своя база этих самых транзакций, закрывающих документов, контрактов. Базу создавал продвинутый менеджер, поэтому она изначально создавалась на Access 2007, с кучей макросов, но без форм. Хранение данных разумеется в самом файле базы.
Всё началось с задачи, которую бухгалтерия поставила КЗ: выгрузить сведения по всем закрывающим документам в рамках определённого периода. Грубо говоря, нужна была табличка из 20 колонок в числе которых были номер и дата контракта с поставщиком, статус оплат по проекту, наличие актов и тому подобное. Заурядный отчёт, который мог превратить в ад жизнь 7 сотрудниц отдела КЗ. А всё дело в том, что при попытке выгрузить нужную информацию база безнадёжно висла. Таблички Access были не рассчитаны на вывод десятков тысяч записей. Движок не предполагал запросы с десятком LEFT JOIN. Индексы не помогали. Руководитель отдела каждый день приходила на работу, открывала базу и не могла даже подступиться к ручному сведению таблички из пары десятков тысяч строк. Бухгалтерия, между тем, поставила достаточно жёсткий дедлайн и ждала данных. Сотрудницы, бросив детей и мужей, готовились ночевать в офисе.
Lean
После такого безрадостного введения, давайте поговорим немного о теории и управлении. Итак, рекламное агентство продаёт сервис. Не рекламу. Реклама — это лишь предмет для разговора, то что мы умеем делать хорошо. Но платят нам за то, что мы быстро и в рамках комиссии сделаем за клиента всю грязную работу и даже лучше, чем он ожидает. Клиент ждёт от нас также рациональных предложений по улучшениям своих кампаний, по оптимальным способам достижения своих показателей. Тех показателей, которыми он сам отчитывается своему руководству. Само собой разумеется, что клиент ожидает идеальный документооборот, безупречные закрытия и феноменальную точность в соблюдении дедлайнов по ним. Возвращаясь к нашим девочкам, я до сих пор не понимаю, как они при помощи обычной базы на обычном аксессе всё это обеспечивали. Но суть не в этом. Короче говоря, для качественного сервиса нужно время. И здесь мы немного коснёмся концепции бережливого производства, поскольку оно на практике имеет прямое отношение к разбираемой здесь задаче.
Бережливое производство (по-английски «lean»), если говорить простыми словами, это такой способ производства продукта, при котором доля потерь стремится к нулю. Эта концепция является творческой переработкой опыта компании Toyota. Основная идея, в выявлении потерь по типичным направлениям вроде бракованной продукции, неоправданных задержках на складах, избыточной обработки, простоях, усушке/утруске и так далее. По данным некоторых источников, экономия от внедрения концепции lean на крупных предприятиях, исчисляется миллиардами рублей. Вы меня спросите, какое это имеет отношение к сервисной компании, такой как рекламное агентство? Самое прямое.
Мы продаём сервис. Для качественного сервиса нужно время. Время и люди – вот наш ресурс. А если люди сутками сидят и копипастят в эксельках, то это потери. Потери времени и, со временем, – людей. Неэффективность производства сервиса, если хотите. Исчисляется методом умножения почасовой ставки оклада на число излишне потраченных часов. Допустим сотрудник отдела контроля закупок получает условных 40 тысяч рублей в месяц за 160 часов работы. Или 250 рублей в час. Она вместо того, чтобы нажать на одну кнопочку и получить требуемый отчёт за 1 минуту, копипастит в течение пары недель. Причём не одна сотрудница, а весь отдел: 250 (ставка) x 8 (часов в день) x 10 (рабочих дней) x 7 (девочек). Получаем 140 тысяч рублей, потраченных на копипаст. Потери для качественного сервиса вообще трудно посчитать, потому, что, когда собирается отчёт методом копипаста, остальная работа либо стоит, либо делается непонятно как.
Половинчатое решение, ускоряющее ручную работу
Давайте перейдём от теории к практике. Итак, есть не выполнимая стандартными средствами задача: выгрузить отчёт. Кроме того, в перспективе есть необходимость как-то поставить процесс создания отчёта на поток, причём желательно силами самого отдела контроля закупок. Ниже описание решения, однако, не хотелось бы, чтобы оно было воспринято как готовый рецепт из «Тостера», но скорее, как концепция для работы со всеми подобными задачами. Даже более. В идеале, надо бы проанализировать все процессы компании и выявить этапы, которые можно автоматизировать по образцу, предлагаемому ниже.
Для решения указанных выше двух задач нам потребовались: MS SQL Server 2012 Express, AutoIt, PowerQuery.
Поскольку Access не позволяет делать сложные запросы из таблиц, содержащих сотни тысяч строк, было принято решение воспользоваться тяжёлой артиллерией, а именно MS SQL Server 2012 Express. Установка базы описана достаточно подробно вендором, поэтому не буду на этом останавливаться. Из Access экспортировали вручную соответствующие таблицы (в которых данные для отчёта) через предварительно созданное соединение ODBC:
Далее повесили индексы на все экспортированные нами таблицы, что позволило увеличить скорость отработки запроса в 4-6 раз:
Далее написали многострочный запрос с кучей LEFT JOIN, GROUP BY и WHERE. Детали его здесь приводить не буду, но они и не значимы. Важен принцип. После отработки запроса делаем Ctrl+a (выделить все результаты). Далее идём в предварительно подготовленную эксельку и вставляем туда данные Ctrl+v. Приводим в порядок форматы, сохраняем, отправляем в бухгалтерию Процесс занял не две недели, а 10 минут. Но одни проблема: выполнить его под силу только очень хорошо прокаченному специалисту. И любые проблемы (например, ошибка экспорта таблицы из-за даты в неправильном формате) превращаются в те ещё танцы с бубном (любые ошибки формата решаются через экспорт в Excel и импорт его уже в MS SQL). Одним словом, эти костыли было необходимо автоматизировать.
Правильное решение с помощью AutoIt
И тут на сцену выходит всесильный AutoIt. Это крайне потенциальная вещь, если в компании работают хотя бы десятки сотрудников. Есть ряд действий, которые выполняет определённый процент людей: открыть таймшиты вовремя, запустить среду разработки/отладки (IDE+базу+окно с git+бэкап сделать), открыть систему учёта по нужному списку проектов и тому подобные мелочи, которые вроде бы незначительные, но отъедают десятки минут рабочего времени сотен людей. В сумме это получается гигантская напрасная работа, которую вполне можно поручить программному роботу. Но вернёмся к нашей задаче.
Итак, необходимо автоматизировать описанный выше процесс, причём действий должно быть всего два: запуск процесса, получение готового отчёта. Можно было бы написать некую прогу, которая подключается сначала к Access, выгружает из него данные, затем заливает их в MS SQL и запускает нужный запрос, результаты которого потом выгружает в результирующий Excel. На это ушли бы недели разработки, ловли ошибок. Было решено воспользоваться готовой инфраструктурой приложений, которой будет управлять скрипт на AutoIt. Выгрузку готового отчёта поручили PowerQuery, поскольку он заточен под такие задачи.
Для того, чтобы всё отработало, необходимо было заранее сделать в исходном файле Access макрос, который экспортирует данные в MS SQL:
Public Function TransferKz()
'Макрос экспортирует таблицы в локальную базу MSSQL
'Для выгрузки отчёта
Dim Tables(1 To 11) As String
Dim i As Integer
Tables(1) = "table1"
Tables(2) = " table2"
Tables(3) = " table3"
'…
For i = 1 To 11
DoCmd.TransferDatabase acExport, "ODBC Database", "ODBC;DSN=kz;", acTable, Tables(i), Tables(i)
Next i
MsgBox "Tables exported"
End Function
Кроме того, заранее подготовлен файл Excel, который содержит модель PowerQuery, выгружающую необходимые данные из базы. Здесь данная модель рассматриваться не будет. Но ознакомиться с принципами работы PowerQuery можно здесь и здесь.
При запуске скрипта AutoIt происходит следующее:
- Высвечивается окошко с предупреждением о том, что во время работы скрипта нельзя трогать клавиатуру и мышку:
MsgBox(0, «Мы начинаем», «Во время работы скрипта нельзя нажимать никакие кнопки и открывать окна. Лучше просто отойти от компьютера на 1-2 минуты».
- Копируется файл MS Access, содержащий нужные нам данные:
FileCopy($file_path_from, $file_path_to, $FC_OVERWRITE + $FC_CREATEPATH) - Запускается MS SQL Management Studio:
Run($ssms_path) WinWaitActive("Соединение с сервером") Send($sql_server_address & "{ENTER}") WinWaitActive("Microsoft SQL Server Management Studio") - Удаляются все таблицы в базе КЗ. $small_sleep – переменная содержащая время паузы перед командами. Необходимо т.к. в противном случае приложения иногда не успевают окончательно отработать команду перед запуском следующей:
Send("^{o}") Sleep($small_sleep) Send($sql_dir & $sql_file_Query_drop_tables) Sleep($small_sleep) Send("{ENTER}") Sleep($small_sleep) Send("{F5}") Sleep($small_sleep) ;Закрытие окна без сохранения Send("^{F4}{TAB}{ENTER}") - Запускается файл Access полученный в ходе шага №2:
;Запускаем базу Access и сразу же макрос для экспорта таблиц Run( $db_path & " /x TransferKz" ) ;Ждём окно: «Tables exported» WinWaitActive("[CLASS:#32770]") ;Закрываем окно Access Send("!+{F4}",0) - Создаём индексы на только что экспортированные таблицы:
;Открываем окно с скриптом, который предстоит запускать WinActivate("Solution1 - Microsoft SQL Server Management Studio") Send("^{o}") Sleep($small_sleep) Send($sql_file_indexes) Sleep($small_sleep) Send("{ENTER}") Sleep($small_sleep) Send("{F5}") Sleep($small_sleep) ;Закрываем окно MSSQL Management Studio Send("!+{F4}",0) - Запускается Excel содержащий необходимую нам модель выгрузки данных из MS SQl:
ShellExecute($file_ excel)
Работа скрипта AutoIt закончена. Теперь необходимо просто нажать на кнопку «Обновить» в открытом файле Excel и дождаться получения требуемого отчёта. Время отработки скрипта от щелчка мышкой до выгрузки отчёта – 3,5 минуты из которых 2 это паузы между шагами.
Выводы
Как вскользь упомянуто выше, мы прорабатывали вопрос решения проблемы следующими способами: радикально переписав базу отдела контроля закупок практически с нуля, либо переместив таблицы в MS SQL и продолжив использовать MS Access в качестве «фронтэнда». Основное препятствие для реализации этих планов – время. Да, можно сделать базу на 1С, потратив на это два-три месяца, деньги на разработчика. 1С не подходит для целостного решения учёта и документооборота в нашем агентстве. Была выбрана другая система, поэтому так или иначе нам придётся в скором времени переделываться всё с нуля уже на новой платформе. Реализация указанного выше набора методов заняла 3 дня. 2 из которых были потрачены на тестирование уже работающей системы и отладку запроса в MS SQL. Ни один профессиональный разработчик не приложил руку к указанному выше коду. Всё делалось своими силами, в свободное от остальных проектов время. Компания осталась при своих деньгах. Время сохранено на нечто более полезное, чем копипаст. Бережливое производство в действии.
Автор: nsuvorov
