Признаться, меня одолевали сомнения в нужности этого текста. Однако, вспомнив максиму «лучше сделать и жалеть, чем не сделать и жалеть», решил все же написать.
Речь пойдет об еще одной (наравне с офисной телефонией) очень любимой всеми системными администраторами (сарказм) зоне ответственности — системах контроля и управления доступом (СКУД).
Основной восторг вызывают три вещи:
Если вам знаком этот восторг, прошу под кат.
Собственно, кроме сомнений, я так же был и мотивирован. Мотивом написания все так же стало полное отсутствие информации в этих наших интернетах и, как следствие, некая надежда на востребованность изложенной ниже информации теми, кто, как и я, матерился при всякой необходимости иметь дело с Кодосом.
Речь пойдет о том, как получать данные от СКУД, при этом по-минимуму взаимодействуя с оболочкой самого Кодоса и полностью обходя ее ограничения.
Да, хватит нытья. В сущности, 99.9% взаимодействия с СКУД — это получение отчетов о проходах сотрудников через точки контроля, внесение учетных записей в базу доступа и извлечение оных оттуда.
Кодос является клиент-серверным приложением. Сервер и СУБД находятся на одной машине. Данные хранит в Firebird. Рискну предположить, что эти утверждения справедливы для всех старых версий Кодоса.
Ну что ж, тряхнем стариной.
Нам понадобится:
Подключение к БД
Будем считать, что наш Кодос живет по адресу 192.168.1.1
Файл базы данных на этой машине находится по пути C:\SSA\SKD\CODOS_DB\CODOS.GDB
Пользователь и пароль дефолтовые для Firebird: sysdba и masterkey соответственно.
Для подключения выбираем следующие настройки:
Server / Protocol: Remote, TCP/IP
Server name: 192.168.1.1
Port: gds_db
Database File: C:\SSA\SKD\CODOS_DB\CODOS.GDB
Server version: Firebird 2.0
Database Alias: произвольная строка, псевдоним для подключения, я ввел Codos
User Name: sysdba
Password: masterkey
Client Library File: путь до gds32.dll, в моем случае C:\IBExpress\IBE\gds32.dll
Выполняем двойной клик по свежесозданной регистрации. Если настройки были введены верно и учетные данные пользователя с административными правами в СУБД совпадают с введенными, то откроется дерево объектов.
Само собой, нас интересует ветка Tables и ее дочерние объекты — таблицы с данными.
Наибольший практический интерес представляют следующие таблицы:
LOGTAB — логи событий на контрольных точках. Именно тут хранятся события прохода через турникеты, двери и так далее. Идеальная отправная точка при формировании отчетов.
CLITAB — учетные карточки пользователей, включая их полные ФИО, коды их электронных пропусков в десятичном и шестнадцатеричном представлении и так далее. Идеальная точка для экспорта учетных данных.
Для импорта нам понадобится еще две таблицы:
CLIDOORACCESS — подпоручик, не ржать, — это таблица с данными о допусках к проходу через точки контроля доступа.
CLI_EXPIRE — таблица с датами, когда заканчивается срок действия пропуска пользователя.
Для вызова редактора SQL-запросов жмакаем F12 или в меню основного окна IBExpress следуем Tools → SQL Editor.
На этом все самое интересное заканчивается и начинается рутина SQL-запросов.
По этой причине (и в силу природной лени) был написан vba-макрос, который получает «сырые» данные из Кодоса без всяких SQL-хитростей, а далее все эти данные пропускает через мясорубку бизнес-логики, выдавая на выходе аккуратно оформленные списки злостных нарушителей трудовой дисциплины.
Я не стану утомлять читателя листингами скриптов, демонстрируя кроме не самого хорошего знания языка SQL-запросов еще и не очень убедительные навыки программирования. Моя задача направить по наиболее короткому пути к получению результатов выборки данных из Кодоса прямиком на лист в Excel'е.
Последовательность шагов следующая:
Теперь подробнее.
Драйвер. Не смотря на кажущееся обилие ODBC-драйверов, фактически единственным сразу заработавшим вариантом в моем случае стал опенсорсный драйвер.
Запрос к Кодосу. Для выполнения подключения из VBA-проекта, понадобится добавить в проект ссылку на библиотеку Microsoft Office 16.0 Object Library. Разумеется, вместо 16.0 может быть что угодно другое, в зависимости от версии установленного у вас офиса.
Обратите внимание на синтаксис SQL-запроса. Все строки с кириллицей должны начинаться с объявления кодировки:
Как было отмечено выше, каждая учетная запись раскидана по трем таблицам: карточка владельца электронного пропуска, срок действия пропуска и права доступа этого пропуска на контрольных точках.
И если никаких сложностей с оператором INSERT в большинство таблиц в IBExpress нет, то с таблицей CLIDOORACCESS не все интуитивно понятно.
Некоторая неочевидность заключается в том, что тип данных поля DOOR — BLOB, а подтип хранимых значений — двоичные. Иными словами, на глазок оценить уровень доступа пропуска при просмотре его записи в данной таблице невозможно.
Однако, IBExpress при двойном клике на таком значении открывает инструмент Blob Viewer/Editor, который предоставляет возможность его просмотра в том виде, в каком он хранится в БД, а так же возможность его изменения через загрузку значения из файла и возможность сохранения его значения в файл.
Таким образом, задача импорта сводится к записи текстовых значений в таблицы CLITAB, CLI_EXPIRE и CLIDOORACCESS с загрузкой BLOB-значений из файла.
По не вполне понятной для меня причине, выполнение скрипта в Script Executive:
И выполнение скрипта с применением оператора UPDATE так же не приводит к появлению данных, загруженных из файла. Однако, если выполнить UDPATE из SQL Editor'а, то после запроса пути к blob-файлу значения в поле DOOR обновляются успешно.
Хотелось бы думать, что этот краткий howto по обходу некоторых неприятных особенностей Кодоса не закроет тему, а напротив ее откроет. Надеюсь, что в комментариях появятся дополнения и к другим СКУД. Но так же не удивлюсь, если тема увянет.
Буду признателен за замечания по существу, за найденные неточности и за описание личного опыта.
Речь пойдет об еще одной (наравне с офисной телефонией) очень любимой всеми системными администраторами (сарказм) зоне ответственности — системах контроля и управления доступом (СКУД).
Disclaimer: речь пойдет о СКУД Кодос версии 1.10.8.0. Возможно, в более поздних версиях пробелы в функциональности и производительности были устранены.Прелесть работы с такой софтиной образца десятилетней давности вытекает из немного внеземной эргономики интерфейса, немного нечеловеческой логики работы с фронт-эндом операторской части и из прочих плодов творчества людей, работавших под руководством людей в погонах (не могу представить себе идеолога разработки СКУД без погонов в прошлом).
Основной восторг вызывают три вещи:
- Отчет о проходах составляется более 10 минут.
- Ограничение длины отчета в 1000 записей.
- Отсутствие экспорта и импорта учетных записей.
Если вам знаком этот восторг, прошу под кат.
Собственно, кроме сомнений, я так же был и мотивирован. Мотивом написания все так же стало полное отсутствие информации в этих наших интернетах и, как следствие, некая надежда на востребованность изложенной ниже информации теми, кто, как и я, матерился при всякой необходимости иметь дело с Кодосом.
Речь пойдет о том, как получать данные от СКУД, при этом по-минимуму взаимодействуя с оболочкой самого Кодоса и полностью обходя ее ограничения.
Disclaimer: все операции придется выполнять в «живой» базе, поэтому вы имеете все шансы завалить СКУД своими неосторожными действиями. Делайте бэкапы, выполняйте групповые операции с осторожностью.
К сути
Да, хватит нытья. В сущности, 99.9% взаимодействия с СКУД — это получение отчетов о проходах сотрудников через точки контроля, внесение учетных записей в базу доступа и извлечение оных оттуда.
Кодос является клиент-серверным приложением. Сервер и СУБД находятся на одной машине. Данные хранит в Firebird. Рискну предположить, что эти утверждения справедливы для всех старых версий Кодоса.
Ну что ж, тряхнем стариной.
Нам понадобится:
- IBExpert — GUI-оболочка, предназначенная для разработки и администрирования баз данных InterBase и Firebird, а также для выбора и изменения данных, хранящихся в базах. (wiki). Бесплатная полнофункциональная версия — Ссылка для скачивания специальной лицензии для бывшего СССР.
- firebird.msg — файл трансляции кодов сообщений СУБД в человекочитаемые сообщения. IBExpress этот файл по умолчанию ищет в каталоге уровнем выше своего исполняемого файла. Поэтому сам IBExpress будет вполне разумно распаковывать во вложенный каталог, находящийся на одном уровне с firebird.msg.
- Библиотека gds32.dll из дистрибутива Firebird. Путь к ней указывается при настройке подключения к БД.
Подключение к БД
Будем считать, что наш Кодос живет по адресу 192.168.1.1
Файл базы данных на этой машине находится по пути C:\SSA\SKD\CODOS_DB\CODOS.GDB
Пользователь и пароль дефолтовые для Firebird: sysdba и masterkey соответственно.
Если вдруг не сразу понятно, как создать новое подключение
После запуска IBExpress, согласившись на дефолтовое представление рабочего пространства, видим характерный многооконный интерфейс. Слева расположен Database Explorer, выполняем по нему правый клик и выбираем Register Database, либо жмакаем Shift+Alt+R.
Для подключения выбираем следующие настройки:
Server / Protocol: Remote, TCP/IP
Server name: 192.168.1.1
Port: gds_db
Database File: C:\SSA\SKD\CODOS_DB\CODOS.GDB
Server version: Firebird 2.0
Database Alias: произвольная строка, псевдоним для подключения, я ввел Codos
User Name: sysdba
Password: masterkey
Client Library File: путь до gds32.dll, в моем случае C:\IBExpress\IBE\gds32.dll
Скриншот настроек
Выполняем двойной клик по свежесозданной регистрации. Если настройки были введены верно и учетные данные пользователя с административными правами в СУБД совпадают с введенными, то откроется дерево объектов.
Скриншот дерева объектов
Само собой, нас интересует ветка Tables и ее дочерние объекты — таблицы с данными.
Наибольший практический интерес представляют следующие таблицы:
LOGTAB — логи событий на контрольных точках. Именно тут хранятся события прохода через турникеты, двери и так далее. Идеальная отправная точка при формировании отчетов.
CLITAB — учетные карточки пользователей, включая их полные ФИО, коды их электронных пропусков в десятичном и шестнадцатеричном представлении и так далее. Идеальная точка для экспорта учетных данных.
Для импорта нам понадобится еще две таблицы:
CLIDOORACCESS — подпоручик, не ржать, — это таблица с данными о допусках к проходу через точки контроля доступа.
CLI_EXPIRE — таблица с датами, когда заканчивается срок действия пропуска пользователя.
Для вызова редактора SQL-запросов жмакаем F12 или в меню основного окна IBExpress следуем Tools → SQL Editor.
На этом все самое интересное заканчивается и начинается рутина SQL-запросов.
Получаем данные
Торжествующая ремарка: если вы выполните выборку прямо в IBExpress, то оцените скорость выполнения SQL-запроса, особенно после продолжительных периодов ожидания тех же данных в самой программной оболочке Кодоса.Я не особо силен в написании красивых SQL-запросов, чтобы вот прямо бабах! и все нужное получил, а ненужное отсеял, да еще и в Firebird'е. Кроме того, полученные данные все равно требуют прически, которую, ессно, выполняет Excel.
По этой причине (и в силу природной лени) был написан vba-макрос, который получает «сырые» данные из Кодоса без всяких SQL-хитростей, а далее все эти данные пропускает через мясорубку бизнес-логики, выдавая на выходе аккуратно оформленные списки злостных нарушителей трудовой дисциплины.
Я не стану утомлять читателя листингами скриптов, демонстрируя кроме не самого хорошего знания языка SQL-запросов еще и не очень убедительные навыки программирования. Моя задача направить по наиболее короткому пути к получению результатов выборки данных из Кодоса прямиком на лист в Excel'е.
Последовательность шагов следующая:
- Устанавливаем ODBC-драйвер для подключения к Firebird.
- Настраиваем ODBC-подключение к Кодосу.
- Используя данное подключение, выполняем запрос к Кодосу в волшебном VBA.
Теперь подробнее.
Драйвер. Не смотря на кажущееся обилие ODBC-драйверов, фактически единственным сразу заработавшим вариантом в моем случае стал опенсорсный драйвер.
Обратите внимание! Разрядность ODBC-драйвера должна совпадать с разрядностью СУБД, к которой происходит подключение. С вероятностью, приближающейся к 100%, Кодос крутится под 32-битным Firebird'ом, соответственно и драйверы следует выбирать 32-битные.ODBC-подключение. Последовательность шагов по настройке ODBC-подключения к Firebird все желающие могут изучить здесь: Подключение к InterBase или Firebird из Excel через ODBC. Мануал исчерпывающий и без внезапных поворотов сюжета. Настройки подключения полностью аналогичны таковым в настройках подключения IBExpress. Единственное — рекомендую символьную таблицу указать явно: WIN1251.
Скриншот настроек ODBC
Запрос к Кодосу. Для выполнения подключения из VBA-проекта, понадобится добавить в проект ссылку на библиотеку Microsoft Office 16.0 Object Library. Разумеется, вместо 16.0 может быть что угодно другое, в зависимости от версии установленного у вас офиса.
Добавление ссылки в проект
Tools → References
Обратите внимание на синтаксис SQL-запроса. Все строки с кириллицей должны начинаться с объявления кодировки:
SELECT * FROM TableName WHERE Field1 LIKE _win1251'Кириллица'
Короткий листинг, получаем данные и пишем результат поячеячно на лист
Dim row As Long
Set conn = New ADODB.Connection
Set rst = New ADODB.RecordsetSet rst = New ADODB.Recordset
rst.Open "SELECT dt, ev_text, cli_text FROM logtab WHERE cli_text not like _win1251'%ОХРАНА%' and dt BETWEEN '01.01.2018 00:01' and '01.01.2018 23:59' order by dt asc", conn, adOpenDynamic, adLockOptimistic
With Worksheets("Лист1")
Do While Not rst.EOF
.Range("A" & CStr(row)).NumberFormat = "@"
.Range("A" & CStr(row)).Value = CStr(rst.Fields(0))
.Range("b" & CStr(row)).Value = rst.Fields(1)
.Range("c" & CStr(row)).Value = rst.Fields(2)
rst.MoveNext
row = row + 1
Loop
End WithИмпорт учетных записей
Как было отмечено выше, каждая учетная запись раскидана по трем таблицам: карточка владельца электронного пропуска, срок действия пропуска и права доступа этого пропуска на контрольных точках.
И если никаких сложностей с оператором INSERT в большинство таблиц в IBExpress нет, то с таблицей CLIDOORACCESS не все интуитивно понятно.
Некоторая неочевидность заключается в том, что тип данных поля DOOR — BLOB, а подтип хранимых значений — двоичные. Иными словами, на глазок оценить уровень доступа пропуска при просмотре его записи в данной таблице невозможно.
Однако, IBExpress при двойном клике на таком значении открывает инструмент Blob Viewer/Editor, который предоставляет возможность его просмотра в том виде, в каком он хранится в БД, а так же возможность его изменения через загрузку значения из файла и возможность сохранения его значения в файл.
Таким образом, задача импорта сводится к записи текстовых значений в таблицы CLITAB, CLI_EXPIRE и CLIDOORACCESS с загрузкой BLOB-значений из файла.
По не вполне понятной для меня причине, выполнение скрипта в Script Executive:
Приводит к созданию соответствующих записей в таблице, но с нулевыми значениями BLOB.SET BLOBFILE 'doors1.lob'; INSERT INTO CLIDOORACCESS (CLI_N, DOORS) VALUES (2, :h00000000_00000016);
И выполнение скрипта с применением оператора UPDATE так же не приводит к появлению данных, загруженных из файла. Однако, если выполнить UDPATE из SQL Editor'а, то после запроса пути к blob-файлу значения в поле DOOR обновляются успешно.
ЧЯДНТ?
Возможно, хабрачитатели знают, в чем моя ошибка, и любезно поделятся решением в камментах. Буду признателен.
Следует отметить, что множественные операции INSERT, UPDATE в одном скрипте выполняются только в Script Executive (вызов его по нажатии Ctrl+F12), SQL Editor такой возможности не предоставляет.Всю прелесть этого решения возможно ощутить уже начиная с 10 регистраций новых пользователей в день. А если регистрации идут пачками по 7-8 десятков каждые три дня, то переоценить решение не удастся при всем желании.
Кроме того, при выполнении скриптов в Script Executive не забывайте ставить галку на Use current connection, иначе получите ошибку соединения с базой.
Заключение
Хотелось бы думать, что этот краткий howto по обходу некоторых неприятных особенностей Кодоса не закроет тему, а напротив ее откроет. Надеюсь, что в комментариях появятся дополнения и к другим СКУД. Но так же не удивлюсь, если тема увянет.
Буду признателен за замечания по существу, за найденные неточности и за описание личного опыта.
