Итак, у нас есть часто применяемый в государственных структурах 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 и оформления кода.
