Автоматизация массовой проверки действительности российских паспортов

Предыстория данной задачи такая. Одна коллега вышла замуж. Как это заведено в нашем обществе, взяла фамилию мужа и ожидала получения нового паспорта. В одно прекрасное утро ей приходит смс от банка, клиентом которого она является, мол ваш паспорт недействителен, свяжитесь с нами. Она сначала напряглась, но разобравшись, поняла, что новый паспорт готов, а старый попал в Список недействительных паспортов. Тут же возникло рац.предложение: если Тинькофф так умеет, давайте внедрим у себя нечто подобное.

Точечная проверка при заключении договоров с новыми клиентами у нас налажена, есть сервис ФМС/МВД для этих целей, ранее я даже выкладывал обработку для выполнения этой проверки прямо из 1С, которую можно встраивать в типовые конфигурации, но для массовой проверки этот инструмент не подходит, т.к. при проверке каждого паспорта необходимо вводить капчу. К слову, упомянутый выше сервис не всегда корректно работает. Время от времени он выдаёт ошибочные результаты, согласно которым все проверяемые паспорта действительны.

Другое дело - Список недействительных (утраченных (похищенных), оформленных на утраченных (похищенных) бланках паспорта гражданина Российской Федерации, выданных в нарушение установленного порядка, а также признанных недействительными) паспортов, ссылка на который также расположена на странице сервиса проверки. Он ежедневно пополняется, его можно встроить в свою информационную систему, регулярно обновляя, после чего безо всяких ограничений использовать для автоматизированных проверок. Немного поразмыслив, решили поступить именно таким образом.

Первым делом большую задачу было решено разбить на несколько частей поменьше:

1. Скачать список из интернета и разархивировать;
2. Загрузить csv-файл в Базу данных;
3. Собственно, сама проверка действующих клиентов и рассылка результатов на эл.почту ответственных сотрудников.

Для выполнения данных задач по расписанию была реализована внешняя обработка с 3 командами, каждой из которых на выходных было назначено своё время выполнения. Обсудим решение каждой из подзадач чуть подробнее.

Процедура СкачатьСписокССайта()
	
	SSL = Новый ЗащищенноеСоединениеOpenSSL();
	fms_services = Новый HTTPСоединение("guvm.mvd.ru",,,,,,SSL);
	HTTPЗапрос = Новый HTTPЗапрос("/upload/expired-passports/list_of_expired_passports.csv.bz2");
	Ответ = fms_services.Получить(HTTPЗапрос, "c:\tmp\list_of_expired_passports.csv.bz2");
	Если Ответ.КодСостояния = 200 Тогда
		ЗапуститьПриложение("""C:\Program Files\7-Zip\7z.exe"" e -r -y ""c:\tmp\list_of_expired_passports.csv.bz2"" -o""c:\tmp""");
	КонецЕсли;
	
КонецПроцедуры

Первая часть кажется довольно простой. С помощью http-запроса скачиваем архив. Далее извлекаем из архива csv, например, с помощью программы 7-zip, которую необходимо заранее установить на сервере 1С. Важный момент: для выполнения команды через ЗапуститьПриложение у пользователя, под которым выполняется задание, должна быть отключена "Защита от опасных действий". Если с этой частью кода возникает ещё какая-либо загвоздка, можно вообще вынести его в Планировщик задач Windows - и не заморачиваться с выполнением его из 1С.

Перейдём ко второй части. На первый взгляд кажется, что в ней тоже ничего сложного. Загрузить csv в Регистр сведений - да раз плюнуть. Но это только на первый взгляд. В конфигурации, с которой мы работаем, есть специальный РС "Актуальный перечень недействительных паспортов" с двумя измерениями: Номер [тип Строка (6)] и Серия [тип Строка(4)]. В него и предполагалась загрузка. Файл большой, через ТекстовыйДокумент не открывается, поэтому теория подсказывает читать этот csv последовательно. У меня получился примерно такой код:

Процедура ЗагрузитьСписокВ_1С()
	
	Перечень = РегистрыСведений.АЭ_АктуальныйПереченьНедействительныхПаспортов.СоздатьНаборЗаписей();
	Перечень.Записать(Истина);

	Каталог = "c:\tmp\";
	ИмяФайла = "list_of_expired_passports.csv";
	МойФайл = Новый Файл(Каталог+ИмяФайла);
	Если Не МойФайл.Существует() Тогда
		Сообщить("Файл не найден!");
		Возврат;
	КонецЕсли;
	
	ТЗ = ПрочитатьCSV(Каталог, ИмяФайла, Перечень);
	
	Перечень.Загрузить(ТЗ);
	Попытка
		Перечень.Записать(Ложь);
	Исключение
		Сообщить(ОписаниеОшибки());
	КонецПопытки;
	
КонецПроцедуры

Функция ПрочитатьCSV(Папка, ИмяФайла, Перечень)

    // Параметры:

    // "Папка" - путь к файлу без имени файла с завершающим слешем,
    // "ИмяФайла" - имя файла,
    // Функция возвращает ТаблицуЗначений с данными файла.

    Текст = "[" + ИмяФайла + "]
    |ColNameHeader=False
    |Format=Delimited(;)
    |TextDelimiter=none
    |CharacterSet=ANSI
    |Col1=Field1 Text";

    ТекстДок = Новый ТекстовыйДокумент;
    ТекстДок.УстановитьТекст(Текст);
    ТекстДок.Записать(Папка + "Schema.ini", КодировкаТекста.ANSI);
    objRec = Новый COMОбъект("ADODB.Recordset");

    strQuery = "SELECT * FROM [" + ИмяФайла + "]";

    strConn = 
	"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Папка + ";Extended Properties=""text;""";

    adOpenStatic = 3;

    adLockOptimistic = 3;

    adCmdText = 1;

    ТаблЗнач = Новый ТаблицаЗначений;

    ТаблЗнач.Колонки.Добавить("Серия");
    ТаблЗнач.Колонки.Добавить("Номер");

    objRec.Open(strQuery, strConn, adOpenStatic, adLockOptimistic, adCmdText);

    КолСтрок = objRec.RecordCount;

    Ном = 1;
	objRec.MoveNext();

    Пока Не objRec.EOF Цикл

		Если Ном % 100000 = 0 Тогда

			Перечень.Загрузить(ТаблЗнач);
			Попытка
				Перечень.Записать(Ложь);
			Исключение
				Сообщить(ОписаниеОшибки());
			КонецПопытки;
			ТаблЗнач.Очистить();

		КонецЕсли;

        НовСтр = ТаблЗнач.Добавить();

		МассивСтрок = СтроковыеФункцииКлиентСервер.РазложитьСтрокуВМассивПодстрок(Строка(objRec.Fields(0).Value));
		НовСтр.Серия = МассивСтрок[0];
		НовСтр.Номер = МассивСтрок[1];

        objRec.MoveNext();

        Ном = Ном + 1;

    КонецЦикла;

    objRec.Close();

    Возврат ТаблЗнач;

КонецФункции

Метод ПрочитатьCSV найден на просторах интернета и адаптирован таким образом, чтобы не держать в оперативной памяти многомиллионную таблицу значений, а записывать наборы по 100 тысяч записей в регистр. Впоследствии у разработчиков конфигурации был найден "типовой" вариант загрузки. От моего он отличается тем, что они использовали ЧтениеТекста для последовательного обхода записей файла вместо ADO:

Процедура ФоноваяЗагрузкаДанных(СтруктураПараметров, АдресХранилища) Экспорт
	
	Если СтруктураПараметров.Свойство("ДвоичныеДанные") Тогда
		ИмяФайла = ПолучитьИмяВременногоФайла("csv");		
		Файл = Новый Файл(ИмяФайла);		
		ИмяФайла = Файл.Путь + Лев(Файл.ИмяБезРасширения,8) + Файл.Расширение;
		СтруктураПараметров.ДвоичныеДанные.Записать(ИмяФайла);		
	Иначе
		ИмяФайла = СтруктураПараметров.ИмяФайла;
    КонецЕсли;
    
    МассивИмениФайла = СтроковыеФункцииКлиентСервер.РазложитьСтрокуВМассивПодстрок(ИмяФайла, "\");
    КолЭдементов = МассивИмениФайла.Количество()-1;
    
    Каталог = "";
    Для Инд = 0 По КолЭдементов-1 Цикл
        Каталог = Каталог + ?(Инд = 0, "", "\") + МассивИмениФайла[Инд];
    КонецЦикла;
    Каталог = Каталог + "\";
    
    НаборЗаписей = РегистрыСведений.АЭ_АктуальныйПереченьНедействительныхПаспортов.СоздатьНаборЗаписей();        
    НаборЗаписей.Записать(Истина);
    
    ПотокЧтенияФайла = Новый ЧтениеТекста(ИмяФайла,КодировкаТекста.ANSI);
    СтрокаФайла = ПотокЧтенияФайла.ПрочитатьСтроку();
	
	СтрокаФайла = ПотокЧтенияФайла.ПрочитатьСтроку();
    Пока СтрокаФайла <> Неопределено Цикл 
        
        СтрокаФайла = ПотокЧтенияФайла.ПрочитатьСтроку();
        МассивЗначение = СтроковыеФункцииКлиентСервер.РазложитьСтрокуВМассивПодстрок(СтрокаФайла,",");
        
        НовСтр = НаборЗаписей.Добавить();

        Если МассивЗначение.Количество() = 0 Тогда
            Продолжить;
        КонецЕсли;
        
        НовСтр.Серия = МассивЗначение[0];
        НовСтр.Номер = МассивЗначение[1];
        
        Если НаборЗаписей.Количество() = 100000 Тогда 
            НаборЗаписей.Записать(Ложь);
            НаборЗаписей.Очистить();
        КонецЕсли;
		
	КонецЦикла;
	
	Если НаборЗаписей.Количество() > 0 Тогда 
            НаборЗаписей.Записать(Ложь);            
    КонецЕсли;
	
	ПотокЧтенияФайла.Закрыть();
    
    Если СтруктураПараметров.Свойство("ДвоичныеДанные") Тогда
		УдалитьФайлы(ИмяФайла);
	КонецЕсли;
	
КонецПроцедуры

В ближайшие выходные после реализации первых двух блоков был выполнен тестовый запуск. В воскресенье никто не работал, я уехал отдыхать. Загрузка в 1С длилась сутки. К вечеру после возвращения я решил зайти на сервер посмотреть результат. Результат был плачевный - сервер стоял колом. После принудительной перезагрузки проверка показала, что в регистр было загружено ~32 млн. записей. А в файле было >115 млн. записей. Я немного приуныл, а на следующей неделе начал искать альтернативный путь решения.

Для ускорения массовых действий опыт подсказывал написать скрипт для загрузки прямо в MS SQL. Я в этом не слишком силён, но голова, руки, наличие интернета и времени дали свои плоды:

USE [tempdb]

create table #import
(
    _Fld35079 NVARCHAR(8) NOT NULL, /* Серия */
    _Fld35078 NVARCHAR(8) NOT NULL /* Номер */
)

bulk insert #import
from 'c:\tmp\list_of_expired_passports.csv'
with
(
    firstrow = 2,
    fieldterminator = ',',
    rowterminator = '0x0a',
    tablock
)

USE [your_1C_DataBase]

DROP TABLE
    [_InfoRg35077]

SELECT DISTINCT
    cast(SUBSTRING(_Fld35078,1,6) as NVARCHAR(6)) as _Fld35078,
    cast(SUBSTRING(_Fld35079,1,4) as NVARCHAR(4)) as _Fld35079,
    cast(0 as NUMERIC(7, 0)) as _Fld1570
INTO  [_InfoRg35077]
FROM #import 

CREATE UNIQUE CLUSTERED INDEX [_InfoRg35077_ByDims] ON [_InfoRg35077]
(
	[_Fld1570] ASC,
	[_Fld35078] ASC,
	[_Fld35079] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

DROP TABLE
    #import

Прокомментирую действия, выполняемые данным скриптом. Сначала создаем временную таблицу #import в базе tempdb. Опытным путём было установлено, что в исходном файле есть нестандартные записи. Для загрузки данных без ошибок Серию и Номер пришлось сделать строкой длиной 8. Далее методом bulk insert загружаем записи из фала во временную таблицу. У меня эта операция занимает около 2 минут. Замерив это время, я очень порадовался промежуточному результату.

_InfoRg35077 - это таблица БД, соответствующая нашему РС "Актуальный перечень недействительных паспортов". Кто не в теме, эту информацию можно получить при помощи метода ПолучитьСтруктуруХраненияБазыДанных().

 Что происходит во второй части скрипта? Удаляем таблицу (drop table) _InfoRg35077, выбираем различные значение полей таблицы #import, попутно приводя их к структуре данных нашего регистра, и пересоздаем с их использованием таблицу _InfoRg35077 (select...into). Далее пересоздаем кластерный индекс (CREATE UNIQUE CLUSTERED INDEX), который был у таблицы при создании её платформой 1С. И в конце удаляем временную таблицу #import.

Вариант без удаления и пересоздания таблицы _InfoRg35077 также в процессе отладки скрипта был опробован, но с ним были проблемы, как и при записи в РС из 1С. Всё дело в том, что наличие кластерного индекса по нашим измерениям предполагает уникальность записей в таблице. Именно проверка уникальности при вставке записей оказывается самой ресурсоемкой операцией. Мы же её обходим благодаря отсутствию индекса при вставке записей. А с помощью Select Distinct (аналог выражения Выбрать Различные) мы можем быть уверены, что записи всё же уникальны и при создании индекса проблем не возникнет.

Тестирование скрипта показало, что он успешно отрабатывает в течение часа. После чего он был добавлен в план заданий Агента SQL сервера.

На этом всё. Третий этап расписывать не буду. Он чисто прикладной и зависит от того, какую задачу решаете именно вы: проверяете действующих клиентов или сотрудников, оповещаете их или ответственных лиц и т.п.

Ещё несколько слов напоследок. Наличие такой большой таблицы в базе данных, конечно, значительно увеличивает её размер. При желании можно создавать её в отдельной базе данных. Или даже на отдельном сервере, организовав доступ к данным через web-сервис. Только он будет без капчи, что позволит успешно автоматизировать процесс проверки.