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

22.08.18

Интеграция - WEB-интеграция

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

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

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

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

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

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

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

 
 Часть 1

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

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

 
 Часть 2. Варинт А

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

 
 Часть 2. Вариант Б

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

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

 
 Часть 2. Вариант SQL

Прокомментирую действия, выполняемые данным скриптом. Сначала создаем временную таблицу #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-сервис. Только он будет без капчи, что позволит успешно автоматизировать процесс проверки.

См. также

Интеграция Альфа Авто 5 / Альфа Авто 6 и AUTOCRM / Инфотек

Сайты и интернет-магазины WEB-интеграция Платформа 1С v8.3 Конфигурации 1cv8 1С:Управление торговлей 11 Автомобили, автосервисы Россия Управленческий учет Платные (руб)

Интеграционный модуль обмена между конфигурацией Альфа Авто 5 и Альфа Авто 6 и порталом AUTOCRM. Данный модуль универсален. Позволяет работать с несколькими обменами AUTOCRM разных брендов в одной информационной базе в ручном и автоматическом режиме.

36000 руб.

03.08.2020    15745    10    17    

11

Интеграция 1С — Битрикс24. Обмен задачами

Сайты и интернет-магазины Интеграция WEB-интеграция Платформа 1С v8.3 Конфигурации 1cv8 Управленческий учет Платные (руб)

Интеграция 1С и Битрикс24. Разработка имеет двухстороннюю синхронизацию 1С и Битрикс24 задачами. Решение позволяет создавать пользователя в 1С из Битрикс24 и наоборот. Данная разработка технически подходит под все основные конфигурации линейки продуктов 1С:Предприятие 8.3 (8.3.18.1289). При приобретении предоставляется 1 месяц бесплатных обновлений разработки. Доступна демо-версия продукта с подключением Вашего Битрикс24

5040 руб.

04.05.2021    17548    6    15    

13

Интеграция с сервисом vetmanager

WEB-интеграция Платформа 1С v8.3 Бухгалтерский учет 1С:Бухгалтерия 3.0 Бытовые услуги, сервис Платные (руб)

Внешняя обработка разрабатывалась для загрузки документов из Ветменеджер в 1С: Бухгалтерия 3.0

12000 руб.

02.02.2021    16358    42    49    

23

[Расширение] БОР-Навигатор.Культура

Зарплата Бюджетный учет WEB-интеграция Обмен с ГосИС Платформа 1С v8.3 Сложные периодические расчеты 1С:Зарплата и кадры государственного учреждения 3 Государственные, бюджетные структуры Россия Бюджетный учет Платные (руб)

Расширение конфигурации, включающее в себя объекты, необходимые для подготовки и сдачи отчета "Штатная численность" системы "БОР-Навигатор.Культура" в программе "1С:Зарплата и кадры государственного учреждения", редакция 3.1.

8400 руб.

01.02.2019    25737    9    0    

7

Заполнение по ИНН или наименованию реквизитов контрагента по данным сайта ФНС

Обмен с ГосИС WEB-интеграция Платформа 1С v8.3 Управляемые формы 1С:Комплексная автоматизация 1.х 1С:Бухгалтерия 2.0 1С:Управление торговлей 10 1С:Управление производственным предприятием 1С:Управление нашей фирмой 1.6 1С:Бухгалтерия государственного учреждения 1С:Документооборот 1С:ERP Управление предприятием 2 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х Платные (руб)

Обработка является альтернативой механизму, разработанному фирмой 1С и заполняющему реквизиты контрагента по ИНН или наименованию. Не требуется действующей подписки ИТС. Вызывается как внешняя дополнительная обработка, т.е. используется, непосредственно, из карточки контрагента. Заполнение по ИНН или наименованию реквизитов контрагента по данным сайта ФНС (egrul.nalog.ru) для БП 2.0, БП 3.0, БГУ 1.0, БГУ 2.0, УТ 10.3, УТ 11.x, КА 1.1, КА 2.x, УПП 1.x, ERP 2.x, УНФ 1.5, УНФ 1.6, УНФ 3.0, ДО 2.1

2400 руб.

28.04.2016    88579    160    215    

318
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. МихаилМ 23.08.18 12:37 Сейчас в теме
хранение этой таблицы недействительных паспортов в oltp базе - крайне не профессионально и повод для увольнения.
и мвд давно уже выкладывает файлы обновлений. так что полное пересоздание не требуется.
d4rkmesa; starik-2005; +2 Ответить
2. VladimirElohov 129 23.08.18 13:00 Сейчас в теме
(1)
Напишите об этом в Аудит-Эскорт. Как я и сказал в конце статьи, есть вариант вынести таблицу за пределы БД, это также есть в планах. Принципиально на технику решения 1 и 2 подзадач это не повлияет, а значит, ничуть не уменьшает полезность описанных в статье приёмов. Но в настоящий момент для экономии времени решил использовать имеющийся РС, чтобы не переделывать обращения к нему в типовых запросах на проверку, которые написаны разработчиком конфигурации "Управление МФО и КПК".
maxopik2; +1 Ответить
16. starik-2005 3033 02.09.18 21:05 Сейчас в теме
Решал данную задачу на С++ - база грузится 16 секунд, поиск номера за О(1). Все это барахло с базами данных - для тех, кто не умеет программировать. На 1С аналогичный алгоритм работает 15 минут. И никаких суток, и никакого питона и 5 часов. Просто нужно мозг включить.
17. Aletar 23.10.18 05:22 Сейчас в теме
(16)
Решал данную задачу на С++ - база грузится 16 секунд, поиск номера за О(1). Все это барахло с базами данных - для тех, кто не умеет программировать. На 1С аналогичный алгоритм работает 15 минут. И никаких суток, и никакого питона и 5 часов. Просто нужно мозг включить.

Поделитесь решением, пожалуйста.
18. starik-2005 3033 23.10.18 13:36 Сейчас в теме
(17)
Поделитесь решением, пожалуйста.
А сколько Вы готовы за него заплатить?
19. Aletar 23.10.18 13:39 Сейчас в теме
(18) Ну мне интересен принцип, хотя бы даже в общих чертах, нежели конкретная реализация. Глядишь и программировать научусь и мозг включится )
20. starik-2005 3033 23.10.18 15:06 Сейчас в теме
(19)
Ну мне интересен принцип, хотя бы даже в общих чертах
У нас есть решение по ФЗ115 - там как раз проверка паспортов производится за секунды, и загрузка туда осуществляется в районе 15 минут. А суть - в организации данных.
21. Aletar 24.10.18 06:31 Сейчас в теме
(20)
У нас есть решение по ФЗ115 - там как раз проверка паспортов производится за секунды, и загрузка туда осуществляется в районе 15 минут. А суть - в организации данных.

Так не расскажите сообществу как у Вас в этом процессе данные организованы? Это же очень интересно.
22. starik-2005 3033 24.10.18 10:10 Сейчас в теме
(21)
Так не расскажите сообществу как у Вас в этом процессе данные организованы?
А может Вам еще и ключ от квартиры, где деньги лежат? ))) За деньги могу рассказать., но это дорогая консультация, ибо профит от этого может быть весьма нехилый. Бесплатно такой информацией я делиться не готов, ибо мне - кукиш, а Вам - ...
23. Aletar 24.10.18 10:12 Сейчас в теме
(22)
А может Вам еще и ключ от квартиры, где деньги лежат? ))) За деньги могу рассказать., но это дорогая консультация, ибо профит от этого может быть весьма нехилый. Бесплатно такой информацией я делиться не готов, ибо мне - кукиш, а Вам - ...

Ах, блин, прям нагоняете саспенс. Любопытство растет и растет. Сколько стоит Ваша консультация по данной теме?
24. starik-2005 3033 24.10.18 10:45 Сейчас в теме
40. Мастер1С 780 02.08.19 12:10 Сейчас в теме
(23) не поделитесь, сколько стоила консультация?
28. andr2510 10 16.05.19 11:30 Сейчас в теме
(1) Тогда в первую очередь увольняться должны пойти 1С ники которые хранят адресный классификатор в БД, который тоже плоская таблица по сути. А более профессионально это как? Поднять под такую задачу отдельный сервер с mongoDB или прикрутить ellasticSearch туда?
30. starik-2005 3033 16.05.19 13:12 Сейчас в теме
(28)
Поднять под такую задачу отдельный сервер с mongoDB или прикрутить ellasticSearch туда?
Человек говорит о том, что bigdata в виде постоянно обновляемого классификатора в базе с OLTP нагрузкой (много маленьких запросов на чтение и запись) - это полный бред. И я с ним на 100% согласен.

ЗЫ: Вот тупо самый простой алгоритм, который будет быстрее, чем загрузка этой всей хрени в БД: 1. Берем исходный файл с паспортами и пишем его в файлы по сериям. В итоге у нас будет максимум 1000 файлов с не более чем лямом недействительных паспортов. 2. Читаем все паспорта, которые нужно проверить, группируем по сериям. 3. Обходим по сериям. Если файл с серией есть - смотрим внутрь для каждого (подгрузить файл в таблицу значений вообще не проблема, особенно если файл сохранялся как ЗначениеВФайл). Предположу, что скорость проверки будет не ниже, чем эта вся возня с СУБД. Также можно атомарно обновлять данные для проверки путем развертывания в новый каталог и последующего переименовывания нового каталога в старый (или можно дату в каталоге указывать, тогда система будет его юзать по дате). Ну и хранилище значений со сжатием можно пользовать - позволит сэкономить место на диске. А если раздербаниватель исходного файла на файлы серий написать на С, то скорость будет равна примерно скорости записи на диск - можно на каждую серию свой файл открыть.
3. МихаилМ 23.08.18 13:27 Сейчас в теме
когда мвд не выкладывало обновлений, я их генерировал сам: сравнивал длины файлов , обрезал новый до длины старого, сравнивал хэши,
если совпадали то догружал новый кусок. а загрузку большого файла разбивал на мелкие по 10 мегабайт. выравнивал текст по границе записей, и загружал в несколькими фз .
4. VladimirElohov 129 23.08.18 13:50 Сейчас в теме
(3)
когда мвд не выкладывало обновлений, я их генерировал сам

Про генерацию файлов сравнения и дозагрузку информацию в интернете встречал. А вот про то, что мвд отдельно выкладывает обновления не видел. Это открытая информация? Может, ссылочкой поделитесь?
5. МихаилМ 23.08.18 14:15 Сейчас в теме
простите. про отдельные обновления обманул (спутал с каким-то другим классификатором типа фиас.)

а файл list_of_expired_passports вырос в размере с 300 мегабайт до 400.
6. roman77 297 24.08.18 12:18 Сейчас в теме
Я бы поместил таблицу в отдельную базу ms sql и обращался бы к ней через внешний источник.

http://v8.1c.ru/overview/Term_000000795.htm
7. VladimirElohov 129 24.08.18 12:54 Сейчас в теме
(6)
Не спорю - это отличный вариант. Но пока совсем не припёрло, держим конфигурацию на полной поддержке. Все доработки вносятся через расширения и внешние отчеты/обработки. К сожалению, механизм расширений пока не дошёл до того уровня, чтобы добавлять в него свои внешние источники данных. Поэтому этот вариант у себя я не стал использовать.
8. roman77 297 24.08.18 14:30 Сейчас в теме
(7) Ну это уже фобия какая-то. Достаточно только для корня конфигурации поставить "редактируется с сохранением поддержки", а все остальные типовые объекты оставить на "..не редактируется".
9. 🅵🅾️🆇 522 25.08.18 08:29 Сейчас в теме
(7)

В расширении или обработке не обязательно добавлять метаданные внешнего источника.
Достаточно подключить коннектор как COMОбъект.

Вот пример для MySQL как делал пару лет назад через драйвер от Оракла:
#Область Конструктор_и_деструктор
// ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~
//						КОНСТРУКТОР 
// ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~ ~~~~~
// Самая первая функция, которую необходимо вызвать после 
// инициализации обработки.
//
// Функция возвращает булево:
//	Истина			- Успешно установлено подключение к БД.
//	Ложь			- В случае неудачи, также будет выведено
//					СообщениеПользователю с описанием ошибки.
//
// Параметры:
//	Драйвер			- [обязательный](строка) 
//					параметром передается название драйвера,
//					необходимого для подключения к 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Объект");
	Если Не Подключение Тогда		
		Сообщение		= Новый СообщениеПользователю();
		Сообщение.Текст	= "Перед вызовом методов объекта вначале запустите конструктор!";
		Сообщение.Сообщить();
	КонецЕсли;
	Возврат Подключение;	
КонецФункции // ПроверитьПодключение()
#КонецОбласти
Показать


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



Алсо, на сегодняшний день Я бы сделал иначе.
Я бы оформил эту базу отдельным микросервисом и обращался бы к ней через REST (запросы get, post, pull и тп и тд).
Это и упростило бы потдержку, позволило бы обращаться к базе не только из 1с и позволило бы вынести сервер с ней куда угодно, лишь бы один порт был прокинут.
Для этих целей использовал бы Python + Flask, на самом деле это крайне просто.
10. VladimirElohov 129 25.08.18 09:09 Сейчас в теме
(9)
Примерно об этом я и писал в последнем абзаце статьи. Спасибо, что раскрыли тему чуть подробнее и набросали варианты.
Сам склоняюсь к последнему варианту. Осталось всего ничего: найти время, абстрагироваться от 1С, изучить по верхам необходимые технологии и реализовать такой сервис.
11. user718500 52 27.08.18 10:05 Сейчас в теме
Задача очень громоздкая для 1с. Для данной задачи необходимо поднимать отдельный сервак с SQL или.подключать к API проверки паспортов сторонних разработчиков. Как вариант здесь можно подключить API passport.bg-c.РУ( НЕ реклама!)
12. user718500 52 27.08.18 10:19 Сейчас в теме
Решили подобную задачу написав свой API сервис на python c БД на postgres. Скорость обновления базы 5 часов. Обновляемся вечером в 20-00, т.к до 18-30 обновляется файл источника.
14. VladimirElohov 129 27.08.18 11:28 Сейчас в теме
(11)(12)(13)
Задача очень громоздкая для 1с.

Именно об этом и рассказывается в статье. Поэтому обновление базы вынесено непосредственно в SQL.

Скорость обновления базы 5 часов.

В принципе, время приемлемое. Но на своих мощностях с MS SQL мне удалось добиться меньшего времени обновления (1 час).

PS. Базу нужно полностью обновлять, т.к. в ней бывают ошибочно введены паспорта, которые в дальнейшем удаляются.

Такие мысли тоже посещали мою голову. Не даром же МВД выгружает их целиком.
13. user718500 52 27.08.18 10:23 Сейчас в теме
PS. Базу нужно полностью обновлять, т.к. в ней бывают ошибочно введены паспорта, которые в дальнейшем удаляются.
15. user718500 52 27.08.18 11:38 Сейчас в теме
У нас 1 процессорный сервер 2004 года выпуска на Linux с докером сервиса 1с + докер postgress и докер с сервисом проверки паспортов, поэтому скорость обновления базы 5 часов. Если иметь современную 2- процессорную систему, то скорость обновления базы будет в разы быстрее.
VladimirElohov; +1 Ответить
25. andr2510 10 16.05.19 10:40 Сейчас в теме
Мне буквально недавно поставили задачу и "мы-же документаций не читаем" и на инфостарт не полез. В общем мой велосипед оказался достаточно производительным. Делюсь.
1. с помощью wget идет загрузка
2. Была поднята база данных на скуле. Дальше с помощью пакета SSIS (dtx) был сделан скрипт который из csv грузит данные в таблицу. На тормозном сервере процесс занимает 10 минут. Это в разы быстрее ибо база чистится truncate и кладутся данные bulk insert-ом (воторое не проверял, но скорость поражает)
1-й пукнт ограничен интернетом. Во втором пункте больше времени занимает распаковка данных чем их загрузка:)
3. Дальше можно либо работать в рамках своих знаний - либо через внешний источник данных либо прямой sql, но это для проверки одного, а что нужно сделать для отчета по всем?
4. Отчет с получением данных из внешнего источника мега медленный (все-таки 120 миллионов записей было на момент написания комментов). Я сделал так. Создал таблицу с данными физиков (тех, кого проверяем). С помощью аналогичного пакета из пп 2 грузанул данные напрямую из 1С-ной базы. Дальше сделал view которая состоит из inner join-а двух таблиц - паспорта и данные 1С. И данные из этой view можно опять же взять напрямую из внешнего источника, а можно и прямым sql. Я был удивлен. пакет по импорту данных клиентов на массиве 30 тысяч отрабатывает за 2 секунды. Запрос на этом массиве 15 секунд. Если думаете, что тут мега железо, то для примера могу сказать. Я сначала хотел построить кластерный индекс по серии и номеру. Так вот. Ждал 4 часа и не дождался:) Железо и винты виртуальные и крайне тормознутые. Тоесть получение отчета вместе со всеми подготовками и переносами данных укладывается в одну минуту.

1й и 2-й пункты по подготовке и обновлению данных укалываются в один bat файл, не требуют программирования на 1С, c++, python-е итп, и судя по комментам, работают гораздо быстрее :)
26. starik-2005 3033 16.05.19 10:57 Сейчас в теме
(25)
1й и 2-й пункты по подготовке и обновлению данных укалываются в один bat файл, не требуют программирования на 1С, c++, python-е итп, и судя по комментам, работают гораздо быстрее :)
На С++ все паспорта грузятся на моем домашнем Ryzen 1600 1 минута 11 секунд (распаковка - основное время - библиотека zlib, чтение - 4 секунды, упаковка базы - остальное время, в итоге 300 метров превращается в 30 метров). Памяти процесс кушает 400 МиБ. Проверка в итоге за О(1), т.е. 120кк паспортов проверяется за время меньше, чем их чтение (< 4 секунды). Так что не сказал бы я, что содержание базы данных в 10 гигабайт (а в столько примерно разворачивается данные 120кк паспортов, если с индексами) - это отличное решение. Мы тоже сначала в скул грузили, но пришли к выводу, что оное никому не нужно.

ЗЫ: Сишный алгоритм чтения, реализованный на 1С, у коллеги на i9-9900K (4,6GHz на все ядра в таскменеджере) работает примерно за 430 секунд безо всякой СУБД. Но, конечно, видя такую разницу (4 сек против 430 сек) понимаешь, на сколько тормознута 1С в части интерпретатора.
27. andr2510 10 16.05.19 11:28 Сейчас в теме
(26) Я ничуть не умоляю того, что нормальный алгоритм на сях способен скушать все быстро. Я про то, что мелкомягкие тоже умеют делать все быстро и с помощью встроенных механизмов задача решается за считанные минуты. И мне нужен был отчет. И если даже проверка по одной записи будет занимать 0.1 сек то массив в 30к клиентов будет обрабатываться 50 минут (3к сек), а чтобы все отработало шустро то нужно изобрести собственную субд которая умеет делать join и всякие плюшки около него:)) но возможно отработает быстрее.

1С (да простят меня создатели) вообще нельзя рассматривать как средство построения алгоритмов анализа. Только сбор. А анализ запросами.
29. starik-2005 3033 16.05.19 13:08 Сейчас в теме
(27)
1С (да простят меня создатели) вообще нельзя рассматривать как средство построения алгоритмов анализа. Только сбор. А анализ запросами.
1C просто охрененно медленный интерпретатор, но, поверьте, для большинства алгоритмов анализа не нужно море данных. Я пробовал партионный учет реализовать без СУБД - работает этак на порядок быстрее (даже на два порядка).

СУБД - это механизм обеспечения перситентности состояния системы. Для обеспечения надежности необходимо сбрасывать кеш при фиксации транзакций на диск. Для ускорения выборки нужно создавать и хранить индекс. Ну и так далее..

Для проверки паспорта не нужны джоины - нужен GET по ключу - паспорту. Затянуть в тот же REDIS (NoSQL) базу паспортов не составляет труда - пять минут. И это, в принципе, правильный подход, т.к. нам не нужен здесь какой-то реляционный механизм. Проверить на нем паспорт со скоростью 2кк/сек (т.е. 120кк за минуту) - вполне посильно любому современному процессору. Использовать для этого MS SQL - это как пушкой по воробьям.

А реализация механизма Key -> Value на 1С для такого типа данных - это вообще примитивный алгоритм. И он даже на движке 1С работает не медленнее, чем MS SQL (если с умом подойти).
31. andr2510 10 17.05.19 11:31 Сейчас в теме
(29) Согласен что можно. Но я не верю, что написанный алгоритм с тестами итп был написан быстрее чем за 2-3 часа. Там скорее всего минимум день суммарно ушел. А я про то, что можно сделать быстро и результативно.

Я думаю, что именно под данную структуру данных вообще можно алгоритм поиска гораздо быстрее чем О(1). Если заморочиться.

Мой спич про простоту реализации. Изучить Си для реализации, если его не знаешь или еще что то, мне кажется сложным. Если ты только 1С-ник.
32. starik-2005 3033 17.05.19 15:17 Сейчас в теме
(31)
Я думаю, что именно под данную структуру данных вообще можно алгоритм поиска гораздо быстрее чем О(1). Если заморочиться.
Быстрее чем О(1) в принципе быть не может, т.к. это самое О(1) говорит, что операция производится за единичное вычисление. Нельзя вычислить меньше раз, чем единица.

А по поводу С, то не нужно никакого С - достаточно прочитать в что-то номер паспорта и сохранить в файл с именем <серия>. Можно так:
  тхт = Новый ЧтениеТекста(файлСДаннымиПаспортов);
  Стр = тхт.ПрочитатьСтроку();
  Пока НЕ Стр = Неопределено Цикл
    Серия = Лев(Стр, 4);
    Если СоотвФайлов[Серия] = неопределено Тогда
      СоотвФайлов[Серия] = Новый ЗаписьТекста(Серия);
    КонецЕсли;
    СоотвФайлов[Серия].ЗаписатьСтроку(Прав(Стр, 6));
    Стр = тхт.ПрочитатьСтроку();
  КонецЦикла;

  Для Каждого Фл ИЗ СоотвФайлов Цикл Фл.Закрыть() КонецЦикла;
Показать


А проверку так:
  Пока Выборка.Следующий() Цикл
    Если БылаСерия[Серия] = неопределено Тогда
      Фл = Новый Файл(Выборка.Серия);
      тхт = Новый ТекствыйДокумент;
      Если Фл.Существует() Тогда
        тхт.Открыть(Выборка.Серия);
      КонецЕсли;
      БылаСерия[Серия] = Новый Массив;
    КонецЕсли;
    Если тхт.НайтиСтроку(Выборка.Номер) Тогда // да, у текстового документа нет найти строки, но придумайте что-нибудь! )))
       БылаСерия[Серия] .Добавить(Выборка.Номер);
    КонецЕсли;
  КонецЦикла;
Показать

Все, на выходе в БылаСерия список серий, а внутри массив номеров, которые обнаружены. Сдается мне, что эта штука будет работать не сильно дольше скульного запроса, а реализуется вот за 5 минут (у меня вот столько ушло на написание сообщения этого).

Ну и, ежу понятно, результат запроса нужно упорядосить по сериям.
33. andr2510 10 17.05.19 16:06 Сейчас в теме
(32) Да, перепутал O(n) и O(1) :)
Вот стало интересно. Что то мне кажется, что данный алгоритм на файле размером 1.4 гига, умрет.
Отпишусь:)) Интрига!)
34. starik-2005 3033 17.05.19 16:30 Сейчас в теме
(33) ну ежу понятно, что в (32) не О(1), а как-то типа О(n/10000) как максимум. Но тоже не плохо )))

ЗЫ: а умрет может быть из-за того, что одновременно 10к файлов открыть будет непросто венде. Хотя... Вроде на NT-ном ядре современные венды это умеют.
35. andr2510 10 18.05.19 19:33 Сейчас в теме
(34) Прогнал я тест. Код проверки я изменил. В выборке сортировал по серии и просто открывал по очереди тем самым не кушал память и исключил рандомное чтение, а поиск по сути свелся на поиск в памяти по строке. ПО скорости получилось 90 секнуд против 5ти (это если не актуализировать таблицу клиентов в скульной базе, иначе 15 секунд выходит) на массиве в 30к клиентов.
Обработка тоже уложилась гдето в 40 минут. В общем результат достаточно живой. Однако всеравно на доводку ушло время. И если скуль есть, то всеравно считаю изобретение велосипедов лишним. Проигрыш в скорости выполнения не считаю критичным, ибо это не те запросы которые выполняются часто. А вот время создания и тетсирования с использованием уже готовых механизмов скуля и того-же wget для скачки, существенно меньше. И все работает из коробки:) К примеру, как выяснилось спустя 20 минут, что вылезают левые ошибки доступа к файлу. Пока потестил и понял, что в исходном файле местами в серии лежит мусор и файлы со служебными символами в имени не создаются (что логично, но изначально грешил на код создания файла).

Так, что мое мнение - нету скуля, то решить можно с помощью и C++ (я бы на C# .NET писал-бы так как 10 лет разработки на нем, там вообще есть таблицы с поиском по хэшу котрые вообще все мега шустро все сделают. С++ под .NET тоже есть. Я знаю:)) и с помощью 1С (результат вполне рабочий). Есть скуль - задача решается гораздо проще. Собственно мой пост и был про решение с помощью типовых механизмов которые тоже нужно знать и изучать, ибо скуль это не только хранилище плюс выборки. Там куча приблуд есть.

ЗЫ: Есть куча причин когда это плохой вариант. К примеру я использовал запуск exe файла для скачки, планировщик винды для регламента, и скуль как хранилище опять же с пакетами интеграции. Но! Все это может быть закрыто злобными админами и через файл + регламентное задание со скачиванием с помощью 1С может быть единственным решением.
36. starik-2005 3033 18.05.19 19:45 Сейчас в теме
(35)
Однако всеравно на доводку ушло время.
Фактически доводкой можно считать обернутое в try-exception создание файла серий с "мусором". Т.е. три минуты моего времени (и то только благодаря лени).

Что тут у нас получается? На медленном серваке обычный код 1С вполне может потягаться с MS SQL, хотя, конечно, 5 секунд против полутора минут - это проигрыш в 18 раз. Но я не виноват, что 1С такая тупая корова. На том же питоне данный код будет работать со скоростью чтения файла, как и на С.

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

Зато теперь ты знаешь, что "архитектура" - это не просто стены и крыша. И иногда можно сделать куда проще и без лишних телодвижений. Вот, например, развернуть у клиентов отдельную таблицу SQL - тот еще гемор, что просьба выделить на диске 10 гигов кажется шуточной.
38. andr2510 10 18.05.19 21:00 Сейчас в теме
(36)
Фактически доводкой можно считать обернутое в try-exception создание файла серий с "мусором"
перед этим нужно понять причину прежде чем оборачивать:)
А по поводу хеш-таблиц (фактически тип map) - памяти тупо не хватит
Ну естесьно не все, а в рамках серии. Самый большой текстовик 5 мегов занимал по серии. Так, что хватит.

А вообще если мы будем говорить про оптимизацию, то можно первоначально создать файл где 1 бит это наличие паспорта. Серия + номер это 10 в 10й, это 1.2 гига. Это меньше чем текстовое хранение текущих паспортов. Если на сервере есть лишняя оперативка то вообще в нее можно загнать. Тогда проверка на наличие будет заключаться в чтении бита по заданному адресу. С этим можно играться и добиться хороших результатов. И это при том, что этот файл будет всегда фиксированного объема и точно не увеличится. К примеру, если на вход подавать отсортированный массив, то можно читать страницами в рамках серии, учитывая, что заведомо будет известен адрес начала и окончания информации по серии.

Зато теперь ты знаешь, что "архитектура" - это не просто стены и крыша.
А кто спорил и где?:)
Вот, например, развернуть у клиентов отдельную таблицу SQL - тот еще гемор, что просьба выделить на диске 10 гигов кажется шуточной.
Я про это и написал в конце, что я против велосипедов, но иногда без них никуда.
39. starik-2005 3033 18.05.19 21:02 Сейчас в теме
(38)
А вообще если мы будем говорить про оптимизацию, то можно первоначально создать файл где 1 бит это наличие паспорта.
Никаму нирасказывай )))
37. tusv 210 18.05.19 20:18 Сейчас в теме
За статью адназначна плюс, но долго это не будет работать.
41. hogik 443 10.10.19 22:34 Сейчас в теме
Загрузка номеров паспортов в DBF файл.
1) Создание DBF файла без индекса в среде 1С.
2) Загрузка номеров программой на FoxPro.
3) Создание индекса в среде 1С.
Создание индекса быстро работает только в Win x64 на локальном диске. Т.к. требуется хорошее кэширование.
Прикрепленные файлы:
ЗагрузкаСерНом.zip
42. hogik 443 10.10.19 23:49 Сейчас в теме
+(41)
Время выполнения 7 минут.
Файлы лежат локально в VM.

CPU: i7-8550U @ 1.80GHz (Turbo Boost выключен)
Реальная RAM: 16 GB
Хост: Ubuntu 16.04.6 LTS
Виртуальная машина: QEMU
Логические CPU: 4
Виртуальная RAM: 8 GB
Гость: Windows XP x64 SP2
Оставьте свое сообщение