Получение данных из СКУД Сигур (бывш. Сфинкс)

Обработка содержит публичные переменные/реквизиты:
ВремяДома (Целое число [2]), Limit (Строка [10]), Табель (Строка [100])
Их описание дано в функции "Конструктор"

Приватные переменные:
Connection - COM Объект ADODB.Connection
Recordset   - COM Объект ADODB.Recordset

// // // // // // // // // // // // // // // // // // // // //
// Проверка соединения с БД
Сигур	= Обработки.Сигур.Создать();
Если Соединение.Конструктор("MySQL ODBC 5.3 ANSI Driver") 
И Соединение.Деструктор() Тогда
	Сообщение		= Новый СообщениеПользователю();
	Сообщение.Текст	= "Успех!";
	Сообщение.Сообщить();			
КонецЕсли;

// // // // // // // // // // // // // // // // // // // // //
// Получение информации о сотрудниках
Сигур	= Обработки.Сигур.Создать();
Сигур.Конструктор(Драйвер);
ТЗ		= Соединение.ПолучитьСотрудников("Gebau", "1");
Сигур.Деструктор();

// // // // // // // // // // // // // // // // // // // // //	
// Получение отладочной информации по посещаемости
Сигур		= Обработки.Сигур.Создать();
Сигур.Конструктор(Драйвер, , , , , , Структура);
мДверей		= Новый Массив;
мДверей.Добавить("Проходная (вертушка)");
мДверей.Добавить("Ворота (машины)");
Структура	= Соединение.ПолучитьПосещаемость(	Период.ДатаНачала,
												Период.ДатаОкончания, 
												мДверей,
												Сотрудник,
												Истина);
ТабДок		= Структура.ТабДок;
Сигур.Деструктор();

// // // // // // // // // // // // // // // // // // // // //	
// Получение таблицы значений из произвольного запроса к БД
Сигур	= Обработки.Сигур.Создать();
Сигур.Конструктор(Драйвер);
ТЗ		= ВыполнитьЗапрос("SELECT * FROM `tc-db-main`.personal LIMIT 100");
Сигур.Деструктор();

#Область Конструктор_и_деструктор
// ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~
//						КОНСТРУКТОР 
// ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~
// Самая первая функция, которую необходимо вызвать после 
// инициализации обработки.
//
// Функция возвращает булево:
//	Истина			- Успешно установлено подключение к БД.
//	Ложь			- В случае неудачи, также будет выведено
//					СообщениеПользователю с описанием ошибки.
//
// Параметры:
//	Драйвер			- [обязательный](строка) 
//					параметром передается название драйвера,
//					необходимого для подключения к MySQL. 
//					На Win Server 2012 задается тут:
//					Панель управления -> Все элементы панели управления
//					-> Администрирование -> Источники данных ODBC (32/64-разрядная версия)
//					-> Драйверы
//					Пример: "MySQL ODBC 5.3 ANSI Driver"
//	Сервер			- [необязательный](строка)
//					Адрес сервера для подключения к БД.
//					Пример: "127.0.0.1"
//	Порт			- [необязательный](строка или число)
//					Номер порта для подключения к БД 
//					Пример: "3305"
//	База			- [необязательный](строка)
//					Имя БД в MySql Sigur 
//					Пример: "mysql"
//	Пользователь	- [необязательный](строка)
//					Имя пользователя для подключения к БД 
//					Пример: "root"
//	Пароль			- [необязательный](строка)
//					Пароль пользователя, по умолчанию без пароля. 
//					Пример: "password"
//	Параметры		- [необязательный](структура)
//					Если передать структуру содержающую свойства с
//					именами как у глобальных переменных (реквизитов),
//					то они заменяться значениями из структуры.
//					В будующем их можно заменить напрямую, если необходимо.
//					Пример: Новый Структура("ВремяДома", 6)
Функция Конструктор(	Знач Драйвер, 
						Знач Сервер			= "127.0.0.1", 
						Знач Порт			= "3305", 
						Знач База			= "mysql", 
						Знач Пользователь	= "root", 
						Знач Пароль			= "",
						Знач Параметры		= Неопределено) Экспорт
						
	// Смотрю, были ли переданы дополнительные параметры
	// для заполнения публичных переменных
	Если Параметры = Неопределено Тогда Параметры = Новый Структура КонецЕсли;					
						
	// Ограничение для выборок из BD, нужно для подстраховки, чтоб окончательно не
	// подвесить 1с и MySql
	Limit		= ?(Параметры.Свойство("Limit"), Параметры.Limit, "100000");
	
	// Константа для расчета рабочего времени (в часах)
	// При отсутсвии использования карточки в течении 
	// этого времени после "Выхода" - считается,
	// что человек ушел домой.
	ВремяДома	= ?(Параметры.Свойство("ВремяДома"), Параметры.ВремяДома, 5);
	
	// Названия поля для связи/соответсвия справочника Сотрудников с полем "Табель" Сигур'а
	// Нужно если в дополнении к имени сотрудника из сигура, нужно еще получать и ссылку на
	// справочник "Сотрудники".
	Табель		= ?(Параметры.Свойство("Табель"), Параметры.Табель, "Код");
	
	// Устанавливаю строку подключения
	СтрокаПодключения	= СтрШаблон("DRIVER={%1};SERVER=%2;PORT=%3;DATABASE=%4;uid=%5;pwd=%6;", 
										Драйвер, Сервер, Порт, База, Пользователь, Пароль);
	// Подключаюсь к БД
	Возврат ПодключитьБД(СтрокаПодключения);
КонецФункции // Конструктор()
// ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~


// ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~
//						ДЕСТРУКТОР 
// ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~
// Функция, которой, по хорошему, надо завершать  
// жизненный цикл обработки.
//
// Функция возвращает булево:
//	Истина			- Успешно отключено от БД.
//	Ложь			- В случае неудачи, также будет выведено
//					СообщениеПользователю с описанием ошибки.
Функция Деструктор() Экспорт
	// Отключаюсь от БД
	Возврат ОтключитьБД();
КонецФункции // Деструктор()
// ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~
#КонецОбласти


#Область Подключить_и_отключить_базу_данных
Функция ПодключитьБД(Знач СтрокаПодключения)
	Попытка	
		Connection	= Новый COMОбъект("ADODB.Connection");
		Recordset	= Новый COMОбъект("ADODB.Recordset");     
		Connection.Open(СтрокаПодключения);
		Возврат Истина;
	Исключение
		Сообщение		= Новый СообщениеПользователю();
		Сообщение.Текст	= СтрШаблон("Во время подключения к бд произошла ошибка.
									|Строка подключения: %1
									|Описание ошибки: %2", 
									СтрокаПодключения, 
									ОписаниеОшибки());
		Сообщение.Сообщить();
		Возврат Ложь;		
	КонецПопытки;
КонецФункции // ПодключитьБД()

Функция ОтключитьБД()
	Попытка	
		Connection.Close();
		Connection	= Неопределено;
		Recordset	= Неопределено;
		Возврат Истина;
	Исключение
		Сообщение		= Новый СообщениеПользователю();
		Сообщение.Текст	= "Во время отключения от бд произошла ошибка.
							|Описание ошибки:" + ОписаниеОшибки();
		Сообщение.Сообщить();
		Возврат Ложь;
	КонецПопытки;	
КонецФункции // ОтключитьБД()

Функция ПроверитьПодключение()
	Подключение	= ТипЗнч(Recordset) = Тип("COMОбъект") И ТипЗнч(Connection) = Тип("COMОбъект");
	Если Не Подключение Тогда		
		Сообщение		= Новый СообщениеПользователю();
		Сообщение.Текст	= "Перед вызовом методов объекта вначале запустите конструктор!";
		Сообщение.Сообщить();
	КонецЕсли;
	Возврат Подключение;	
КонецФункции // ПроверитьПодключение()
#КонецОбласти

#Область Запросы

// ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~
//				ВЫПОЛНИТЬ ПРОИЗВОЛЬНЫЙ ЗАПРОС 
// ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~
// Функция позволяющая выполнить произвольный запрос к БД Сигур.
// Ввозвращает таблицу значений с результатами запроса.
// 
// Параметры:
//	ТекстЗапроса	- [обязательный](строка) 
//					параметром передается текст запроса к БД.
//					Пример: "SELECT * FROM `tc-db-main`.personal LIMIT 100"   
Функция ВыполнитьЗапрос(Знач ТекстЗапроса) Экспорт
	Recordset.Open(ТекстЗапроса, Connection, 1);
	ТЗ	= НаборЗаписейВТаблицу();
	Возврат ТЗ;
КонецФункции // ВыполнитьЗапрос()
// ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~


// ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~
//						Получить сотрудников 
// ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~
// Получить список всех сотрудников и сопутстсвующее описание
//
// Функция возвращает таблицу значений с информацией по сотрудникам.
//
// Параметры:
//	Отдел		- [необязательный](строка) 
//					параметром передается название отдела,
//					можно передать отделы с разделителем "," 
//					пустая строка - все отделы
//					Пример: "IT,Склад,Охрана"
//	Уволенные	- [необязательный](строка или число)
//					0		- все, 
//					1		- действующие, 
//					2		- уволенные
//					Пример: "0"
//	сГруппами	- [необязательный](булево)
//					Истина	- оставить группы/отделы, 
//					Ложь	- исключить группы/отделы, 
//					Пример: Ложь
Функция ПолучитьСотрудников(	Знач Отдел = "", 
								Знач Уволенные = "1", 
								Знач сГруппами = Ложь) Экспорт
	Если Не ПроверитьПодключение() Тогда		
		Возврат Неопределено;
	КонецЕсли;
	
	// На всякий кастую входные параметры
	Отдел = Строка(Отдел); Уволенные = Строка(Уволенные);
	
	ТекстЗапроса = "
	|# ##### ##### ##### ##### #####
	|# 		ЗАПРОС СОТРУДНИКОВ
	|# ##### ##### ##### ##### #####
	|SELECT
	|  P.ID                    AS ID,          # ID
	|  P.PARENT_ID             AS PARENT_ID,   # PARENT_ID
	|  IF(P.TYPE=1,
	|    'Отдел',
	|    'Работник')           AS TYPE,        # Отдел (DEP) / Работник (EMP)
	|  P.NAME                  AS NAME,        # Имя
	|  P.DESCRIPTION           AS DESCRIPTION, # Описание
	|  P.POS                   AS POS,         # Должность / Модель
	|  P.TABID                 AS TABID,       # Табельный номер
	|  IF(P.STATUS=1, 
	|    'Работает', 
	|    'Удален')             AS STATUS,      # Работает / Удален
	|  P.CREATEDTIME           AS CREATEDTIME, # Время создания
	|  P.FIREDTIME             AS FIREDTIME,   # Время удаления (увольнения)
	|  HEX(P.CODEKEY)          AS CODEKEY      # Код карты в HEX
	|FROM
	|  `tc-db-main`.personal	AS P
	|WHERE
	|  IF("+Уволенные+" = '1', STATUS = 1, IF(@fired = '2', STATUS = 2, TRUE))
	|	OR P.TYPE = 1
	|LIMIT "+Limit+"
	|";	
	Recordset.Open(ТекстЗапроса, Connection, 1);	
	ТЗ	= НаборЗаписейВТаблицу();
	
	// Если нужны только заданные отделы и их предки,
	// Возвращает массив "Отделы" содержащий строки с ID подразделений
	ЕСЛИ Отдел <> "" ТОГДА
		Отделы	= Новый Массив;
		Для Каждого Строка Из СтрРазделить(Отдел, ",", Ложь) Цикл
			Строки	= ТЗ.НайтиСтроки(Новый Структура("NAME,TYPE", СокрЛП(Строка), "Отдел"));
			Отделы	= КонкатенацияМассивов(Новый Структура("Отделы,Строки", Отделы, Строки), Истина);
		КонецЦикла; // Для Каждого Строка Из СтрРазделить(Отдел, "," Ложь)
		Было	= 0;
		// Получаю все вложенные отделы
		Пока Было < Отделы.Количество() Цикл
			Было	= Отделы.Количество();
			Для Каждого Строка Из Отделы Цикл
				Строки	= ТЗ.НайтиСтроки(Новый Структура("PARENT_ID,TYPE", Строка.ID, "Отдел"));
				Отделы	= КонкатенацияМассивов(Новый Структура("Отделы,Строки", Отделы, Строки), Истина);
			КонецЦикла;
		КонецЦикла; // ПОКА Было < Отделы.Количество()
		Для Счетчик = 0 По Отделы.ВГраница() Цикл
			Отделы.Установить(Счетчик, Отделы[Счетчик].ID);	
		КонецЦикла;
	КОНЕЦЕСЛИ; // ЕСЛИ Отдел  ""
	
	ТЗ.Колонки.Вставить(0, "Сотрудник"); 
	
	// Перебираю все строки ТЗ
	мНаУдаление	= Новый Массив;	
	ДЛЯ КАЖДОГО Строка ИЗ ТЗ ЦИКЛ
		// Выкидываю строки содержащие описание подразделений
		// или не входящие в заданные отделы
		Если (Не сГруппами 
		И Строка.TYPE = "Отдел") 
		Или (Отдел  "" 
		И Отделы.Найти(Строка.PARENT_ID) = Неопределено) Тогда
			мНаУдаление.Добавить(Строка);
			Продолжить;
		КонецЕсли;
		РасшифроватьКарту(Строка.CODEKEY);
		Строка.Сотрудник	= ПолучитьСотрудникаПоТабелю(Строка.TABID);		
	КОНЕЦЦИКЛА; // ДЛЯ КАЖДОГО Строка ИЗ ТЗ
	
	// Удаляю ненужный мусор
	Для Каждого Строка Из мНаУдаление Цикл
		ТЗ.Удалить(Строка);	
	КонецЦикла; // Для Каждого Строка Из мНаУдаление
	
	// Установить русские названия колонок
	РусифицироватьКолонкиТаблицы(ТЗ);
	
	Возврат ТЗ;
КонецФункции // ПолучитьСотрудников()
// ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~


// ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~
//						Получить посещаемость 
// ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~
// Получить посещаемость сотрудников/сотрудника за выбранный период
//
// Функция возвращает структуру состоящую из:
//	ТЗ				- Тип: ТаблицаЗначений. 
//					ТЗ с данными полученными из запроса к базе Сигурд.
//	Массив			- Тип: Массив.
//					Массив содержащий данные в иерархическом виде.
//					* Массив с структурой сотрудников 
//					  (Сотрудник, Имя, Карточка, Описание, Статус, Табель, Посещаемость)
//						* Посещаемость - массив со структурой посещаемости по рабочим дням
//						(ВремяВхода, ВремяВыхода, ЧасовНаРаботе, Примечание, Ошибка) 
//	ТабДок			- Тип: ТабличныйДокумент
//					Содержит
//
// Параметры:
//	ДатаНачала		- [необязательный](дата) 
//					параметром передается дата начала отбора,
//					если опустить параметр - 
//					начало предыдущего месяца. 
//					Эта дата всегда начало дня!
//	ДатаОкончания	- [необязательный](дата) 
//					параметром передается дата начала отбора,
//					если опустить параметр - 
//					конец предыдущего месяца. 
//					Эта дата всегда конец дня!
//	мДверей			- [необязательный](массив строк)
//					массив содержащий полные названия входных дверей. 
//					По умолчанию регистрируются все двери 
//	Сотрудник		- [необязательный](строка)
//					имя сотрудника по которому получить данные. 
//					По умолчанию пустая строка - по всем сотрудникам
//	Отладка			- [необязательный](булево)
//					если передать Истина, то в результирующей структуре
//                  вернет в том числе и ТабличныйДокумент с
//                  временем проведенным на работе и отмеченными ошибочными расчетами.
//					По умолчанию - Ложь.
Функция ПолучитьПосещаемость(	Знач ДатаНачала		= Неопределено, 
								Знач ДатаОкончания	= Неопределено, 
								Знач мДверей		= Неопределено,
								Знач Сотрудник		= "",
								Знач Отладка		= Ложь) Экспорт
	Если Не ПроверитьПодключение() Тогда		
		Возврат Неопределено;
	КонецЕсли;
	
	// Обрабатываю переданные параметры
	Если ДатаНачала = Неопределено Тогда
		ДатаНачала	= НачалоМесяца(ДобавитьМесяц(ТекущаяДата(), -1));		
	КонецЕсли;
	Если ДатаОкончания = Неопределено Тогда
		ДатаОкончания	= КонецМесяца(ДобавитьМесяц(ТекущаяДата(), -1));		
	КонецЕсли;
	
	// Беру заданый период + 1 день к ожидаемому, тк люди могли работать в ночь
	// и желательно захватить статистику по полному рабочему дню накануне.
	Период	= СтрШаблон("AND L.logtime BETWEEN '%1-%2-%3 00:00:00' AND DATE_ADD('%4-%5-%6 23:59:59', INTERVAL 1 DAY)",
							Формат(Год(ДатаНачала), "ЧГ=0"),
							Месяц(ДатаНачала),
							День(ДатаНачала),
							Формат(Год(ДатаОкончания), "ЧГ=0"),
							Месяц(ДатаОкончания),
							День(ДатаОкончания));							
	
	Если мДверей <> Неопределено и мДверей.Количество() Тогда
		Двери	= "AND D.Name in (";
		Для Каждого Дверь Из мДверей Цикл
			Двери	= СтрШаблон("%1'%2', ", Двери, Дверь);	
		КонецЦикла;
		Двери	= Лев(Двери, СтрДлина(Двери)-2) + ")";
	Иначе
		Двери	= "";
	КонецЕсли;
	
	Если Сотрудник <> "" Тогда
		Сотрудник	= "AND P.Name LIKE '%"+Сотрудник+"%'";	
	КонецЕсли;	
	
	ТекстЗапроса = "	
	|# ##### ##### ##### ##### #####
	|# 		ЗАПРОС ПРОХОДОВ
	|# ##### ##### ##### ##### #####
	|SELECT
	|  L.id                           AS EventID,     # Код события
	|  L.logtime                      AS EventTime,   # Время события
	|  L.devhint                      AS DoorID,      # Код двери
	|  D.Name                         AS DoorName,    # Имя двери
	|  IF(ord(substr(L.logdata, 5, 1)) = 1,
	|    'Выход',
	|    'Вход')                      AS Dir,         # Направление прохода
	|  HEX(substr(L.logdata, 11, 8))  AS EventCodeKey,# Проход по карточке 
	|  P.ID                           AS EmployeeID,  # Код работника
	|  P.NAME                         AS Name,        # Имя
	|  P.DESCRIPTION                  AS Description, # Описание
	|  P.POS                          AS Pos,         # Должность / Модель
	|  P.TABID                        AS TabID,       # Табельный номер
	|  IF(P.STATUS=1,
	|    'Работает',
	|    'Удален')                    AS Status,      # Работает / Удаления
	|  HEX(P.CODEKEY)                 AS Codekey      # Код карты в HEX
	|FROM
	|  `tc-db-main`.devices  AS D
	|  LEFT JOIN `tc-db-log`.logs AS L
	|    LEFT JOIN `tc-db-main`.personal AS P
	|      ON (L.EMPHINT = P.ID)
	|    ON D.ID = L.devhint
	|WHERE
	|  substr(L.logdata,1,2) = 0xFE06 
	|  "+Период+"
	|  "+Двери+"
	|  "+Сотрудник+"
	|ORDER BY
	|  EmployeeID ASC 
	|LIMIT "+Limit+"	
	|";	
	Recordset.Open(ТекстЗапроса, Connection, 1);	
	ТЗ	= НаборЗаписейВТаблицу();
	
	// Расшифровываю поле с карточкой
	Для Каждого Строка Из ТЗ Цикл 
		РасшифроватьКарту(Строка.CODEKEY); 
		РасшифроватьКарту(Строка.EventCodeKey);		
	КонецЦикла;	
	
	// Установить русские названия колонок
	РусифицироватьКолонкиТаблицы(ТЗ);
	
	Запрос	= Новый Запрос("
	|ВЫБРАТЬ * ПОМЕСТИТЬ ВТ ИЗ &ТЗ КАК ТЗ;
	|ВЫБРАТЬ Имя,Карточка,Описание,Статус,Табель,					// Работник
	|ВремяСобытия, НаправлениеПрохода, ИмяДвери, КарточкаСобытия	// Проходы
	|ИЗ ВТ КАК ВТ УПОРЯДОЧИТЬ ПО ВремяСобытия Возр ИТОГИ ПО КодРаботника");
	Запрос.УстановитьПараметр("ТЗ", ТЗ);
	вРаботник	= Запрос.Выполнить().Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам);
	
	МассивРаботников	= Новый Массив;
	Посещаемость		= Новый Массив;
	ПОКА вРаботник.Следующий() ЦИКЛ		
		Работник			= Новый Структура("Сотрудник,Имя,Карточка,Описание,Статус,Табель,Посещаемость");
		вДанные				= вРаботник.Выбрать();
		вДанные.Следующий();
		ЗаполнитьЗначенияСвойств(Работник, вДанные);
		Работник.Сотрудник	= ПолучитьСотрудникаПоТабелю(Работник.Табель);		
		Посещаемость		= РассчитатьПосещаемость(вДанные, ДатаНачала, ДатаОкончания);
		
		Работник.Вставить("Посещаемость", Посещаемость);
		МассивРаботников.Добавить(Работник);
	КОНЕЦЦИКЛА; // ПОКА вРаботник.Следующий()
	
	// Формирую отчет
	ТабДок	= СформироватьТабДокПосещаемости(МассивРаботников, ДатаНачала, ДатаОкончания);
	
	Возврат Новый Структура("ТабДок,ТЗ,Массив", ТабДок, ТЗ, МассивРаботников);
КонецФункции // ПолучитьПосещаемость()
// ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~

#КонецОбласти