Быстрая выгрузка результатов опроса из 1С-Битрикс и как передать в Excel произвольное количество столбцов SQL-запросом

Моя цель - предложение широкого ассортимента товаров и услуг на постоянно высоком качестве обслуживания по самым выгодным ценам.

Итак, у нас есть часто применяемый в государственных структурах 1С-Битрикс и опрос с 30 или 50 вопросами и несколькими десятками тысяч ответов. При попытке экспортировать результаты опроса силами стандартных механизмов 1С-Битрикс все это выполняется от получаса и более, что является не очень рабочим сценарием. Конечно, можно запустить процесс формирования выгрузки фоном, и в конце высылать на почту сформированный xls-файлик... Но практика показала, что при формировании больших xls-файлов силами php серверу еще частенько и памяти не хватает... Окончательным "гвоздем" в подобные решения стало то, что автор стати не владеет PHP, но владеет SQL. Отсюда и родилась мысль написать SQL-запрос, который можно выполнить через Workbench или DBeaver (кто его ставит - не забудьте сразу снять галочку, что пустые строки разделяют запросы), и его результаты загрузить в Excel. В дальнейшем этот запрос можно будет запускать и из PHP с дальнейшим формированием файла с выгрузкой.

Первой же проблемой, с которой столкнулся автор, стало то, что мы заранее не знаем сколько столбцов в результатах опроса, потому что в общем случае мы не знаем количество вопросов. Можно, конечно, каждый раз переписывать запрос по какому-то шаблону, но лень - двигатель прогресса, который решил сделать следующим образом. Из-за того, что нам в подавляющем количестве случаев результаты все равно нужны в Excel, мы формируем в результатах запроса одно текстовое поле, которое будет строкой csv-файла. При этом пришлось пойти на небольшие допущения. Пришлось внести изменения в данные в процессе выгрузки: двойные кавычки (их используем в качестве ограничителя строк в csv) заменяем на одинарные, а переносы строк просто удаляем.

Второй проблемой стало то, что автор не сумел "запихать" всю логику в один запрос без использования CTE. А CTE до определенного момента не поддерживались в mySQL. Поэтому в своё время была написана хранимая процедура, формирующую временную таблицу и работающая уже с ней.

Ниже привожу два варианта SQL-кода. Первый представляет собой одиночный запрос с использованием CTE (может использоваться в относительно новых версиях СУБД mySQL и MariaDB). Если ваша СУБД не поддерживает CTE, то используйте второй вариант с созданием хранимой процедуры. Подставляете в нужные места идентификаторы опроса и сроки для выгрузки и используете в соответствии с комментариями (особое внимание на сохранение в ANSI). Лично я копипасчу результаты выполнения запроса из DBeaver в Notepad++ и сохраняю в csv-файл. Такой csv-файл Excel корректно открывает по умолчанию по двум кликам. Но если этого не произошло - используйте функционал импорта из текста в соответствии с изображением:

В общем, запускайте запросы, встраивайте их в свой код, формируйте ответы и радуйте своих заказчиков.

Запрос с CTE

/*запрос позволяет вытащить результаты любого голосования в Битрикс Управление Сайтом в форме csv
 Внесите идентификатор опроса в двух местах в запросе (сейчас поставлено 24), укажите период
 за который нужна выгрузка, выполните запрос, сохраните результаты в csv-файле (в ANSI) и отройте его в Excel
 В текстах будут удалены переносы и табуляции, а двойные кавычки заменены на одинарные
 */
with 
cte_headers (id, uuser, ddate, answers) as ( 	-- формируем строку, которая станет заголовком столбцов в csv. 
	select 	'ID',								-- потом склеим её с результатами через union
			'Посетитель (ID)' ,
			'Дата',
-- меняем двойные кавычки на одинарные (чтобы корректно работал разделитель). Здесь и далее ячейки в csv будут обернуты двойными кавычками и разделены точкой с запятой
			(select group_concat(replace(coalesce(b_vote_question.question,''),'"',Char(39)) separator '";"')       
    from b_vote_question 
    where b_vote_question.vote_id=24  -- внести идентификатор опроса
    order by  b_vote_question.c_sort)),
cte_b_vote_event_id as ( -- выбираем факты голосования, которые нам нужны
	select id, date_vote, vote_user_id 
	from b_vote_event 
	where 	b_vote_event.vote_id=24 and -- внести идентификатор опроса
			b_vote_event.date_vote>'2017-01-01 00:00:00' and  	-- внести дату начала выборки
			b_vote_event.date_vote<'2023-01-01 00:00:00'),   	-- внести дату окончания выборки
cte_vote_data (id, uuser, ddate, answers) as( -- выбираем все ответы, "склеиваем" их в CSV. Ячейка ограничена двойными кавычками. Разделитель - точка с запятой
	select 	cte_b_vote_event_id.id as id, 
			coalesce(concat(b_user.login,' ', coalesce(b_user.name,''),' ',coalesce(b_user.last_name,''),' (',b_user.id,')'),'неавторизованный пользователь') as user, 
			cte_b_vote_event_id.date_vote as ddate,
			(select group_concat(coalesce((	select group_concat(trim(concat(replace(coalesce(b_vote_answer.message,' '),'"',Char(39)),' ',replace(coalesce(b_vote_event_answer.message,' '),'"',Char(39)) )) separator ' && ') -- && разделяет если ответов несколько
											from b_vote_event_answer -- так как ответ может быть внесен пользователем "руками" или выбран из готового, то просто конкатим эти строки
												left join b_vote_answer on b_vote_answer.id=b_vote_event_answer.answer_id
											where b_vote_event_answer.event_question_id=b_vote_event_question.id 
								),' ') separator '";"')
			from b_vote_question
					left join b_vote_event_question on b_vote_event_question.question_id=b_vote_question.id and b_vote_event_question.event_id=cte_b_vote_event_id.id              
			where b_vote_question.vote_id=24) as content -- внести идентификатор опроса
	from  cte_b_vote_event_id
				left join b_vote_user on b_vote_user.id=cte_b_vote_event_id.vote_user_id
				left join b_user on b_user.id=b_vote_user.auth_user_id
	order by id desc)              
-- в итоговом запросе склеиваем заголовки с ответами и убираем переносы,  чтобы не "ломался" csv-файл
select replace(replace(concat('"',id,'";"',uuser,'";"',ddate,'";"',answers,'"'),char(10),''),char(13),'') 
from cte_headers
union
select replace(replace(concat('"',id,'";"',uuser,'";"',ddate,'";"',answers,'"'),char(10),''),char(13),'') 
from cte_vote_data

Запрос без CTE (если в вашей СУБД CTE не поддерживается)

/*скрипт позволяет вытащить результаты любого голосования в Битрикс Управление Сайтом в форме csv
 Внесите идентификатор опроса в двух местах в запросе (сейчас поставлено 24), укажите период
 за который нужна выгрузка, выполните запрос, сохраните результаты в csv-файле (в ANSI) и отройте его в Excel
 В текстах будут удалены переносы и табуляции, а двойные кавычки заменены на одинарные
 
 */

/* При вызове всего этого скрипта из кода необходимо проверять существоваение хранимки get_b_votes и только если она есть - выполнять дальнейший код, что бы не пересекаться с другими сессиями */
SET @@session.group_concat_max_len = 100000; /*этот параметр можно выставить в кофигурации mysql, он ограничивает максимальную длину, возвращаемую GROUP_CONCAT*/
SET SQL_SAFE_UPDATES = 0;
DROP PROCEDURE IF EXISTS get_b_votes;
delimiter $$
create procedure get_b_votes(
in _vote_id int,
in _date_from timestamp,
in _date_to timestamp
)
begin
/*Создаем курсор и обвязку для формирования осташейся части таблицы*/
    Declare done int default 0;
    Declare now_vote_event_id int;  
    Declare vote_event_cursor Cursor for (select id from b_vote_event where b_vote_event.vote_id=_vote_id and b_vote_event.DATE_VOTE<_date_to and b_vote_event.DATE_VOTE>_date_from order by id desc);
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

/*Создаем временные таблицу, содержимое которых потом вернем*/
    DROP TABLE IF EXISTS get_b_votes_temp_table;
    CREATE TEMPORARY TABLE get_b_votes_temp_table (
    `id` varchar(255),
    `user` varchar(255),
    `ddate` varchar(255),
    `content` longtext
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    DROP TABLE IF EXISTS get_b_votes_return_table;
    CREATE TEMPORARY TABLE get_b_votes_return_table (
    `content` longtext
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Формируем первую строку таблицы (заголовки)*/    
    insert into get_b_votes_temp_table (id, user, ddate, content)
    (
    select 'ID','Посетитель (ID)' ,'Дата',
        (select group_concat(replace(coalesce(b_vote_question.question,''),'"',Char(39)) separator '","')       
    from b_vote_question 
    where b_vote_question.vote_ID=_vote_id 
    order by  b_vote_question.c_sort));

/*Формируем оставшуюся часть таблицы*/    
    open vote_event_cursor;

    read_loop:LOOP
        FETCH vote_event_cursor INTO now_vote_event_id;
        IF done THEN LEAVE read_loop;  END IF;
            insert into get_b_votes_temp_table (id,user,ddate,content)       
            select 
                b_vote_event.id as id, 
                coalesce(concat(b_user.login,' ', coalesce(b_user.name,''),' ',coalesce(b_user.last_name,''),' (',b_user.id,')'),'неавторизованный пользователь') as user, 
                b_vote_event.date_vote as ddate,
                   (select group_concat(coalesce((select group_concat(trim(concat(replace(coalesce(b_vote_answer.message,' '),'"',Char(39)),' ',replace(coalesce(b_vote_event_answer.message,' '),'"',Char(39)) )) separator ' && ') -- && разделяет если ответов несколько
                                                from b_vote_event_answer
                                                left join b_vote_answer on b_vote_answer.ID=b_vote_event_answer.answer_id
                                                where b_vote_event_answer.event_question_ID=b_vote_event_question.id 
                                                ),' ') separator '","')
                from b_vote_question
                left join b_vote_event_question on b_vote_event_question.question_ID=b_vote_question.id and b_vote_event_question.EVENT_ID=now_vote_event_id               
                where b_vote_question.VOTE_ID=_vote_id) as content
                from  b_vote_event
                    left join b_vote_user on b_vote_user.id=b_vote_event.vote_user_id
                    left join b_user on b_user.id=b_vote_user.auth_user_id
                    where b_vote_event.id=now_vote_event_id;

    END LOOP;
/*закрываем курсор*/         
    close vote_event_cursor;    
/*Проводим окончательную обработку - сливаем все в один столбец*/    
    insert into get_b_votes_return_table (content) select replace(replace(concat('"',get_b_votes_temp_table.id,'","',get_b_votes_temp_table.user,'","',get_b_votes_temp_table.ddate,'","',get_b_votes_temp_table.content,'"'),char(10),''),char(13),'') from get_b_votes_temp_table;
/*Возвращаем что получилось*/    
    select * from get_b_votes_return_table as result;
  end $$
delimiter ;
/*это - пример вызова созданной процедуры. Сюда нужно подставлять индентификатор опроса (зд. опрос битрикса а не хайлоад-блок) и сроки для выборки с... по...*/
call get_b_votes(24,'2017-01-01 00:00:00','2023-05-01 00:00:00');

DROP PROCEDURE IF EXISTS get_b_votes;

Для желающих поковырять выкладываю схему взаимосвязей таблиц модуля Vote с основными полями:

P.S. Автор не занимается продвижением или критикой 1С-Битрикс, а взаимодействует с ним таким, какой он есть. Также автор не претендует на звание гуру SQL и оформления кода.

Источник: https://habr.com/ru/post/678532/

Интересные статьи

Интересные статьи

Вопрос сбора информации с приборов учета при разнообразии существующих протоколов обмена стоит очень остро. В статье рассмотрена работа с европейским протоколом DLMS\COSEM и его российской локализацие...
Пару дней назад мне написал знакомый студент-физик с просьбой помочь открыть данные, снятые с лабораторного оборудования в Excel и скинул мне несколько файлов странного формата .mmd. Интернет на запро...
Данная статья - это не научный прорыв, а лишь помощник быстрее понять как работает стандартный функционал в BitrixДавайте представим, что в разделе каталога у нас 150 запросов к БД. Вроде бы немного п...
Каждый, кто использовал Чистую архитектуру в разработке, сталкивался с проблемой передачи данных между слоями. Суть проблемы всегда одинакова: необходимо вернуть либо рез...
Как-то у нас исторически сложилось, что Менеджеры сидят в Битрикс КП, а Разработчики в Jira. Менеджеры привыкли ставить и решать задачи через КП, Разработчики — через Джиру.