Schnelles Entladen von Umfrageergebnissen aus 1C-Bitrix und wie man eine beliebige Anzahl von Spalten mit einer SQL-Abfrage nach Excel überträgt

Wir haben also 1C-Bitrix, das oft in Regierungsstrukturen verwendet wird, und eine Umfrage mit 30 oder 50 Fragen und mehreren zehntausend Antworten. Beim Versuch, Umfrageergebnisse mit Standard-1C-Bitrix-Mechanismen zu exportieren, dauert dies alles eine halbe Stunde oder länger, was kein sehr funktionierendes Szenario ist. Natürlich kann man im Hintergrund den Prozess der Generierung des Uploads starten, und am Ende die generierte xls-Datei an die Mail schicken … Die Praxis hat aber gezeigt, dass beim Generieren großer xls-Dateien per php der Server oft nicht mitmacht genug Speicher … Der letzte “Nagel” bei solchen Entscheidungen war, dass der Autor des Artikels kein PHP besitzt, sondern SQL besitzt. Daher wurde die Idee geboren, eine SQL-Abfrage zu schreiben, die über Workbench oder DBeaver ausgeführt werden kann (wer auch immer es sagt – vergessen Sie nicht, sofort das Häkchen zu entfernen, dass leere Zeilen Abfragen trennen), und ihre Ergebnisse in Excel zu laden. Zukünftig kann dieser Request auch aus PHP unter weiterer Bildung einer Datei mit Upload gestartet werden.

Das erste Problem, auf das der Autor gestoßen ist, war, dass wir nicht im Voraus wissen, wie viele Spalten die Umfrageergebnisse haben, weil wir im Allgemeinen die Anzahl der Fragen nicht kennen. Sie können die Anfrage natürlich jedes Mal nach einer Vorlage neu schreiben, aber Faulheit ist der Motor des Fortschritts, für den ich mich wie folgt entschieden habe. Da wir die Ergebnisse in den allermeisten Fällen noch in Excel benötigen, bilden wir in den Abfrageergebnissen ein Textfeld, das die Zeile der csv-Datei sein wird. Gleichzeitig musste ich kleine Annahmen treffen. Während des Entladevorgangs musste ich Änderungen an den Daten vornehmen: doppelte Anführungszeichen (wir verwenden sie als Zeilentrenner in csv) werden durch einfache ersetzt, Zeilenumbrüche werden einfach gelöscht.

Das zweite Problem war, dass der Autor nicht in der Lage war, die gesamte Logik in eine Anfrage zu “schieben”, ohne CTE zu verwenden. Und CTEs wurden in mySQL bis zu einem bestimmten Punkt nicht unterstützt. Daher wurde einmal eine gespeicherte Prozedur geschrieben, die eine temporäre Tabelle bildet und mit ihr arbeitet.

Unten sind zwei Versionen des SQL-Codes. Die erste ist eine einzelne Abfrage mit CTE (kann in relativ neuen Versionen von mySQL und MariaDB verwendet werden). Wenn Ihr DBMS CTE nicht unterstützt, verwenden Sie die zweite Option mit der Erstellung einer gespeicherten Prozedur. Ersetzen Sie die Umfragekennungen und laden Sie Daten an den richtigen Stellen hoch und verwenden Sie sie gemäß den Kommentaren (besonderes Augenmerk auf die Speicherung in ANSI). Persönlich kopiere ich die Ergebnisse der Abfrage von DBeaver in Notepad++ und speichere sie in einer CSV-Datei. Eine solche Excel-csv-Datei öffnet sich standardmäßig mit zwei Klicks korrekt. Wenn dies jedoch nicht geschehen ist, verwenden Sie die Importfunktion aus Text gemäß dem Bild:

Führen Sie im Allgemeinen Anfragen aus, betten Sie sie in Ihren Code ein, bilden Sie Antworten und begeistern Sie Ihre Kunden.

Anfrage bei CTE

/* Die Anfrage ermöglicht es Ihnen, die Ergebnisse jeder Abstimmung in Bitrix Site Management in Form von csv abzurufen. Geben Sie die Umfrage-ID an zwei Stellen in der Anfrage ein (24 ist derzeit festgelegt), geben Sie den Zeitraum an, für den Sie hochladen müssen, und führen Sie sie aus die Anfrage, speichern Sie die Ergebnisse in einer csv-Datei (in ANSI) und öffnen Sie sie in Excel. Die Texte werden mit Bindestrichen und Tabulatoren entfernt und doppelte Anführungszeichen werden durch einfache Anführungszeichen ersetzt */ mit cte_headers (id, uuser, ddate, answers ) als ( — Bilden Sie eine Zeichenfolge, die zur Überschrift der Spalten in CSV wird. Wählen Sie “ID” aus, — kleben Sie sie dann mit den Ergebnissen über Vereinigung “Besucher (ID)”, “Datum”, — ändern Sie doppelte Anführungszeichen in einfache Anführungszeichen (damit das Trennzeichen korrekt funktioniert) Hier und im Folgenden werden Zellen in CSV in doppelte Anführungszeichen gesetzt und durch ein Semikolon getrennt ( select group_concat(replace(coalesce(b_vote_question.question,”),”),”’,Char (39)) Trennzeichen ‘”;”‘) von b_vote_question wobei b_vote_question.vote_id=24 — Reihenfolge der Umfrage-IDs von b_vote_question.c_sort) einfügen), cte_b_vote_event_id als (- – Wählen Sie die Abstimmungsfakten aus, die wir benötigen, wählen Sie ID, date_vote, vote_user_id aus b_vote_event aus, wobei b_vote_event.vote_id=24 und — geben Sie die Umfrage-ID b_vote_event.date_vote>’2017-01-01 00:00:00’ ein und — geben Sie das Startdatum ein selections b_vote_event.date_vote<'2023-01-01 00:00:00'), -- geben Sie das Enddatum der Auswahl cte_vote_data (id, uuser, ddate, answers) ein als ( -- wählen Sie alle Antworten aus, "kleben" Sie sie in CSV. Die Zelle wird durch doppelte Anführungszeichen begrenzt. Trennzeichen - Semikolon wählen Sie cte_b_vote_event_id.id als ID, coalesce(concat(b_user.login,' ', coalesce(b_user.name,''),' ',coalesce(b_user.last_name,''),' (', b_user. id,')'),'nicht autorisierter Benutzer') als Benutzer, cte_b_vote_event_id.date_vote als Datum, (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 ' && ') -- && wird aufgeteilt, wenn es mehrere Antworten von b_vote_event_answer gibt - - da die Antwort vom Benutzer "von Hand" eingegeben oder aus der fertigen ausgewählt werden kann, verketten wir diese Zeilen einfach links und verbinden b_vote_answer mit b_vote_answer.id=b_vote_event_answer.answer_id wobei b_vote_event_answer.event_question_id=b_vote_event_question.id ),' ') Trennzeichen '";" ') von b_vote_question links beitreten b_vote_event_question auf b_vote_event_question.question_id=b_vote_question.id und b_vote_event_question.event_id=cte_b_vote_event_id.id wobei b_vote_ques tion.vote_id=24) als Inhalt -- Umfrage-ID von cte_b_vote_event_id hinzufügen links b_vote_user auf b_vote_user.id=cte_b_vote_event_id beitreten.vote_user_id links b_user beitreten auf b_user.id=b_vote_user.auth_user_id geordnet nach ID desc) - Kopfzeilen mit Antworten zusammenführen und entfernen Bindestriche, damit die CSV-Datei nicht beschädigt wird select replace(replace(concat('"',id,'";"',uuser,'";"',ddate,'";"',answers, '"') ,char(10),''),char(13),'') from cte_headers union select replace(replace(concat('"',id,'";"',uuser,'";" ',ddate, '";"',antworten,''"'),char(10),''),char(13),'') von cte_vote_data

Abfrage ohne CTE (wenn CTE in Ihrem DBMS nicht unterstützt wird)

/* Mit dem Skript können Sie die Ergebnisse jeder Abstimmung in Bitrix Site Management in Form von csv extrahieren. Geben Sie die Umfrage-ID an zwei Stellen in der Anfrage ein (derzeit auf 24 festgelegt), geben Sie den Zeitraum an, für den Sie herunterladen müssen, und führen Sie sie aus die Anfrage, speichern Sie die Ergebnisse in einer csv-Datei (in ANSI) und öffnen Sie diese in Excel In den Texten werden Bindestriche und Tabulatoren entfernt und doppelte Anführungszeichen durch einfache ersetzt */ /* Beim Aufruf dieses gesamten Skripts aus dem Code, es ist notwendig, die Existenz des get_b_votes-Speichers zu überprüfen und nur wenn er existiert, weiteren Code auszuführen, um sich nicht mit anderen Sitzungen zu überschneiden */ SET @@session.group_concat_max_len = 100000; /*diese Option kann in mysql config gesetzt werden, sie begrenzt die maximale Länge, die von GROUP_CONCAT zurückgegeben wird*/ SET SQL_SAFE_UPDATES = 0; LÖSCHVERFAHREN, WENN VORHANDEN get_b_votes; delimiter $$ create procedure get_b_votes( in _vote_id int, in _date_from timestamp, in _date_to timestamp ) begin /*Erzeuge einen Cursor und eine Bindung, um den Rest der Tabelle zu bilden*/ Deklariere erledigt int default 0; Deklarieren Sie now_vote_event_id int; Deklarieren Sie den vote_event_cursor-Cursor für (wählen Sie die ID aus b_vote_event aus, wobei b_vote_event.vote_id=_vote_id und b_vote_event.DATE_VOTE<_date_to und b_vote_event.DATE_VOTE>_date_from geordnet nach id desc); CONTINUE HANDLER FÜR SQLSTATE ‘02000’ ERKLÄREN SET done=1; /*Eine temporäre Tabelle erstellen, deren Inhalt später zurückgegeben wird*/ DROP TABLE IF EXISTS get_b_votes_temp_table; TEMPORÄRE TABELLE ERSTELLEN get_b_votes_temp_table ( `id` varchar(255), `user` varchar(255), `ddate` varchar(255), `content` longtext ) ENGINE=InnoDB DEFAULT CHARSET=utf8; TABELLE LÖSCHEN, WENN VORHANDEN get_b_votes_return_table; TEMPORÄRE TABELLE ERSTELLEN get_b_votes_return_table ( `content` longtext ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*Erste Tabellenzeile bilden (Überschriften)*/ einfügen in get_b_votes_temp_table (id, user, ddate, content) ( select ‘ID’,’Visitor (ID)’ ,’Date’, (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)); /*Den Rest der Tabelle bilden*/ 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,’)’),’nicht autorisierter Benutzer’) als Benutzer, b_vote_event.date_vote als Datum, ( 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)) )) Trennzeichen ‘ && ‘) — && trennt bei mehreren Antworten und 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_event; SCHLEIFE ENDE; /*Cursor schließen*/ vote_event_cursor schließen; /*Endbearbeitung durchführen – alles in einer Spalte zusammenführen*/ in get_b_votes_return_table (Inhalt) einfügen 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; /*Gib zurück, was passiert ist*/ wähle * aus get_b_votes_return_table als Ergebnis; Ende $$ Trennzeichen ; /*Dies ist ein Beispiel für den Aufruf der erstellten Prozedur. Hier müssen Sie die Poll-ID (Bitrix-Poll und nicht den Highload-Block) und das Timing für das Sampling von… bis…*/ call get_b_votes(24,’2017-01-01 00:00:00′, ‘2023-05-01 00:00:00’); LÖSCHVERFAHREN, WENN VORHANDEN get_b_votes;

Für diejenigen, die tiefer graben möchten, poste ich ein Diagramm der Beziehung zwischen den Tabellen des Vote-Moduls und den Hauptfeldern:

PS Der Autor bewirbt oder kritisiert 1C-Bitrix nicht, sondern interagiert damit so wie es ist. Außerdem erhebt der Autor nicht den Anspruch, ein SQL-Guru und Codedesigner zu sein.

Similar Posts

Leave a Reply

Your email address will not be published.