1 package C4::ImportExportFramework;
3 # Copyright 2010-2011 MASmedios.com y Ministerio de Cultura
5 # This file is part of Koha.
7 # Koha is free software; you can redistribute it and/or modify it
8 # under the terms of the GNU General Public License as published by
9 # the Free Software Foundation; either version 3 of the License, or
10 # (at your option) any later version.
12 # Koha is distributed in the hope that it will be useful, but
13 # WITHOUT ANY WARRANTY; without even the implied warranty of
14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 # GNU General Public License for more details.
17 # You should have received a copy of the GNU General Public License
18 # along with Koha; if not, see <http://www.gnu.org/licenses>.
23 use XML::LibXML::XPathContext;
25 use POSIX qw(strftime);
27 use List::MoreUtils qw(indexes);
33 use vars qw(@ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
46 use constant XMLSTR => '<?xml version="1.0" encoding="UTF-8"?>
47 <?mso-application progid="Excel.Sheet"?>
49 xmlns:x="urn:schemas-microsoft-com:office:excel"
50 xmlns="urn:schemas-microsoft-com:office:spreadsheet"
51 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
54 <Style ss:ID="Default" ss:Name="Normal">
55 <Alignment ss:Vertical="Bottom"/>
63 <Font x:Family="Swiss" ss:Color="#0000FF" ss:Bold="1"/>
66 <NumberFormat ss:Format="yyyy\-mm\-dd"/>
69 <NumberFormat ss:Format="yyyy\-mm\-dd\ hh:mm:ss"/>
72 <NumberFormat ss:Format="hh:mm:ss"/>
80 use constant ODSSTR => '<?xml version="1.0" encoding="UTF-8"?>
81 <office:document-content xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" xmlns:style="urn:oasis:names:tc:opendocument:xmlns:style:1.0" xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0" xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0" xmlns:draw="urn:oasis:names:tc:opendocument:xmlns:drawing:1.0" xmlns:fo="urn:oasis:names:tc:opendocument:xmlns:xsl-fo-compatible:1.0" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:meta="urn:oasis:names:tc:opendocument:xmlns:meta:1.0" xmlns:number="urn:oasis:names:tc:opendocument:xmlns:datastyle:1.0" xmlns:svg="urn:oasis:names:tc:opendocument:xmlns:svg-compatible:1.0" xmlns:chart="urn:oasis:names:tc:opendocument:xmlns:chart:1.0" xmlns:dr3d="urn:oasis:names:tc:opendocument:xmlns:dr3d:1.0" xmlns:math="http://www.w3.org/1998/Math/MathML" xmlns:form="urn:oasis:names:tc:opendocument:xmlns:form:1.0" xmlns:script="urn:oasis:names:tc:opendocument:xmlns:script:1.0" xmlns:ooo="http://openoffice.org/2004/office" xmlns:ooow="http://openoffice.org/2004/writer" xmlns:oooc="http://openoffice.org/2004/calc" xmlns:dom="http://www.w3.org/2001/xml-events" xmlns:xforms="http://www.w3.org/2002/xforms" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" office:version="1.0">
83 <office:font-face-decls/>
84 <office:automatic-styles/>
85 </office:document-content>';
88 use constant ODS_STYLES_STR => '<?xml version="1.0" encoding="UTF-8"?>
89 <office:document-styles xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" xmlns:style="urn:oasis:names:tc:opendocument:xmlns:style:1.0" xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0" xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0" xmlns:draw="urn:oasis:names:tc:opendocument:xmlns:drawing:1.0" xmlns:fo="urn:oasis:names:tc:opendocument:xmlns:xsl-fo-compatible:1.0" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:meta="urn:oasis:names:tc:opendocument:xmlns:meta:1.0" xmlns:number="urn:oasis:names:tc:opendocument:xmlns:datastyle:1.0" xmlns:svg="urn:oasis:names:tc:opendocument:xmlns:svg-compatible:1.0" xmlns:chart="urn:oasis:names:tc:opendocument:xmlns:chart:1.0" xmlns:dr3d="urn:oasis:names:tc:opendocument:xmlns:dr3d:1.0" xmlns:math="http://www.w3.org/1998/Math/MathML" xmlns:form="urn:oasis:names:tc:opendocument:xmlns:form:1.0" xmlns:script="urn:oasis:names:tc:opendocument:xmlns:script:1.0" xmlns:ooo="http://openoffice.org/2004/office" xmlns:ooow="http://openoffice.org/2004/writer" xmlns:oooc="http://openoffice.org/2004/calc" xmlns:dom="http://www.w3.org/2001/xml-events" office:version="1.0">
90 <office:font-face-decls></office:font-face-decls>
91 <office:styles></office:styles>
92 <office:automatic-styles></office:automatic-styles>
93 <office:master-styles></office:master-styles>
94 </office:document-styles>';
97 use constant ODS_SETTINGS_STR => '<?xml version="1.0" encoding="UTF-8"?>
98 <office:document-settings xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:config="urn:oasis:names:tc:opendocument:xmlns:config:1.0" xmlns:ooo="http://openoffice.org/2004/office" office:version="1.0"><office:settings>
99 <config:config-item-set config:name="ooo:view-settings">
100 <config:config-item config:name="VisibleAreaTop" config:type="int">0</config:config-item>
101 <config:config-item config:name="VisibleAreaLeft" config:type="int">0</config:config-item>
102 <config:config-item config:name="VisibleAreaWidth" config:type="int">2000</config:config-item>
103 <config:config-item config:name="VisibleAreaHeight" config:type="int">900</config:config-item>
104 <config:config-item-map-indexed config:name="Views"><config:config-item-map-entry>
105 <config:config-item config:name="ViewId" config:type="string">View1</config:config-item>
106 <config:config-item-map-named config:name="Tables">
107 <config:config-item-map-entry config:name="Sheet1"><config:config-item config:name="CursorPositionX" config:type="int">0</config:config-item><config:config-item config:name="CursorPositionY" config:type="int">1</config:config-item><config:config-item config:name="HorizontalSplitMode" config:type="short">0</config:config-item><config:config-item config:name="VerticalSplitMode" config:type="short">0</config:config-item><config:config-item config:name="HorizontalSplitPosition" config:type="int">0</config:config-item><config:config-item config:name="VerticalSplitPosition" config:type="int">0</config:config-item><config:config-item config:name="ActiveSplitRange" config:type="short">2</config:config-item><config:config-item config:name="PositionLeft" config:type="int">0</config:config-item><config:config-item config:name="PositionRight" config:type="int">0</config:config-item><config:config-item config:name="PositionTop" config:type="int">0</config:config-item><config:config-item config:name="PositionBottom" config:type="int">0</config:config-item>
108 </config:config-item-map-entry>
109 </config:config-item-map-named>
110 <config:config-item config:name="ActiveTable" config:type="string">Sheet1</config:config-item>
111 <config:config-item config:name="HorizontalScrollbarWidth" config:type="int">270</config:config-item>
112 <config:config-item config:name="ZoomType" config:type="short">0</config:config-item>
113 <config:config-item config:name="ZoomValue" config:type="int">100</config:config-item>
114 <config:config-item config:name="PageViewZoomValue" config:type="int">50</config:config-item>
115 <config:config-item config:name="ShowPageBreakPreview" config:type="boolean">false</config:config-item>
116 <config:config-item config:name="ShowZeroValues" config:type="boolean">true</config:config-item>
117 <config:config-item config:name="ShowNotes" config:type="boolean">true</config:config-item>
118 <config:config-item config:name="ShowGrid" config:type="boolean">true</config:config-item>
119 <config:config-item config:name="GridColor" config:type="long">12632256</config:config-item>
120 <config:config-item config:name="ShowPageBreaks" config:type="boolean">true</config:config-item>
121 <config:config-item config:name="HasColumnRowHeaders" config:type="boolean">true</config:config-item>
122 <config:config-item config:name="HasSheetTabs" config:type="boolean">true</config:config-item>
123 <config:config-item config:name="IsOutlineSymbolsSet" config:type="boolean">true</config:config-item>
124 <config:config-item config:name="IsSnapToRaster" config:type="boolean">false</config:config-item>
125 <config:config-item config:name="RasterIsVisible" config:type="boolean">false</config:config-item>
126 <config:config-item config:name="IsRasterAxisSynchronized" config:type="boolean">true</config:config-item></config:config-item-map-entry></config:config-item-map-indexed>
127 </config:config-item-set>
128 <config:config-item-set config:name="ooo:configuration-settings">
129 <config:config-item config:name="ShowZeroValues" config:type="boolean">true</config:config-item>
130 <config:config-item config:name="ShowNotes" config:type="boolean">true</config:config-item>
131 <config:config-item config:name="ShowGrid" config:type="boolean">true</config:config-item>
132 <config:config-item config:name="GridColor" config:type="long">12632256</config:config-item>
133 <config:config-item config:name="ShowPageBreaks" config:type="boolean">true</config:config-item>
134 <config:config-item config:name="LinkUpdateMode" config:type="short">3</config:config-item>
135 <config:config-item config:name="HasColumnRowHeaders" config:type="boolean">true</config:config-item>
136 <config:config-item config:name="HasSheetTabs" config:type="boolean">true</config:config-item>
137 <config:config-item config:name="IsOutlineSymbolsSet" config:type="boolean">true</config:config-item>
138 <config:config-item config:name="IsSnapToRaster" config:type="boolean">false</config:config-item>
139 <config:config-item config:name="RasterIsVisible" config:type="boolean">false</config:config-item>
140 <config:config-item config:name="IsRasterAxisSynchronized" config:type="boolean">true</config:config-item>
141 <config:config-item config:name="AutoCalculate" config:type="boolean">true</config:config-item>
142 <config:config-item config:name="PrinterName" config:type="string">Generic Printer</config:config-item>
143 <config:config-item config:name="ApplyUserData" config:type="boolean">true</config:config-item>
144 <config:config-item config:name="CharacterCompressionType" config:type="short">0</config:config-item>
145 <config:config-item config:name="SaveVersionOnClose" config:type="boolean">false</config:config-item>
146 <config:config-item config:name="UpdateFromTemplate" config:type="boolean">false</config:config-item>
147 <config:config-item config:name="AllowPrintJobCancel" config:type="boolean">true</config:config-item>
148 <config:config-item config:name="LoadReadonly" config:type="boolean">false</config:config-item>
149 </config:config-item-set>
150 </office:settings></office:document-settings>';
153 use constant ODS_MANIFEST_STR => '<?xml version="1.0" encoding="UTF-8"?>
154 <manifest:manifest xmlns:manifest="urn:oasis:names:tc:opendocument:xmlns:manifest:1.0">
155 <manifest:file-entry manifest:media-type="application/vnd.oasis.opendocument.spreadsheet" manifest:full-path="/"/>
156 <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/statusbar/"/>
157 <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/accelerator/"/>
158 <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/floater/"/>
159 <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/popupmenu/"/>
160 <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/progressbar/"/>
161 <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/menubar/"/>
162 <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/toolbar/"/>
163 <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/images/Bitmaps/"/>
164 <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/images/"/>
165 <manifest:file-entry manifest:media-type="application/vnd.sun.xml.ui.configuration" manifest:full-path="Configurations2/"/>
166 <manifest:file-entry manifest:media-type="text/xml" manifest:full-path="content.xml"/>
167 <manifest:file-entry manifest:media-type="text/xml" manifest:full-path="styles.xml"/>
168 <manifest:file-entry manifest:media-type="text/xml" manifest:full-path="meta.xml"/>
169 <manifest:file-entry manifest:media-type="" manifest:full-path="Thumbnails/"/>
170 <manifest:file-entry manifest:media-type="text/xml" manifest:full-path="settings.xml"/>
171 </manifest:manifest>';
176 C4::ImportExportFramework - Import/Export Framework to Excel-xml/ODS Module Functions
180 use C4::ImportExportFramework;
184 Module to Import/Export Framework to Excel-xml/ODS on intranet administration - MARC Frameworks section
186 Module to Import/Export Framework to Excel-xml/ODS on intranet administration - MARC Frameworks section
187 exporting the tables marc_tag_structure, marc_subfield_structure to excel-xml/ods or viceversa
189 Functions for handling import/export.
196 =head2 ExportFramework
198 Export all the information of a Framework to an excel "xml" file or OpenDocument SpreadSheet "ods" file.
207 my ($frameworkcode, $xmlStrRef, $mode) = @_;
209 my $dbh = C4::Context->dbh;
214 if ($mode eq 'ods' || $mode eq 'excel') {
216 my $parser = XML::LibXML->new();
217 $dom = $parser->parse_string(($mode && $mode eq 'ods')?ODSSTR:XMLSTR);
219 $root = $dom->documentElement();
220 if ($mode && $mode eq 'ods') {
221 my $elementBody = $dom->createElement('office:body');
222 $root->appendChild($elementBody);
223 $elementSS = $dom->createElement('office:spreadsheet');
224 $elementBody->appendChild($elementSS);
229 $debug and warn "Error ExportFramework $@\n";
234 if (_export_table('marc_tag_structure', $dbh, ($mode eq 'csv')?$xmlStrRef:$dom, ($mode eq 'ods')?$elementSS:$root, $frameworkcode, $mode)) {
235 if (_export_table('marc_subfield_structure', $dbh, ($mode eq 'csv')?$xmlStrRef:$dom, ($mode eq 'ods')?$elementSS:$root, $frameworkcode, $mode)) {
236 $$xmlStrRef = $dom->toString(1) if ($mode eq 'ods' || $mode eq 'excel');
247 # Export all the data from a mysql table to an spreadsheet.
250 my ($table, $dbh, $dom, $root, $frameworkcode, $mode) = @_;
251 if ($mode eq 'csv') {
252 _export_table_csv($table, $dbh, $dom, $root, $frameworkcode);
253 } elsif ($mode eq 'ods') {
254 _export_table_ods($table, $dbh, $dom, $root, $frameworkcode);
256 _export_table_excel($table, $dbh, $dom, $root, $frameworkcode);
260 # Export the mysql table to an csv file
261 sub _export_table_csv
263 my ($table, $dbh, $strCSV, $root, $frameworkcode) = @_;
266 # First row with the name of the columns
267 my $query = 'SHOW COLUMNS FROM ' . $table;
268 my $sth = $dbh->prepare($query);
271 while (my $hashRef = $sth->fetchrow_hashref) {
272 $$strCSV .= '"' . $hashRef->{Field} . '",';
273 push @fields, $hashRef->{Field};
277 # Populate rows with the data from mysql
278 $query = 'SELECT * FROM ' . $table . ' WHERE frameworkcode=?';
279 $sth = $dbh->prepare($query);
280 $sth->execute($frameworkcode);
282 while (my $hashRef = $sth->fetchrow_hashref) {
283 for my $field (@fields) {
284 my $value = $hashRef->{$field} // q||;
285 $value =~ s/[\r\n]//g;
286 $$strCSV .= '"' . $value . '",';
293 # Separator for change of table
294 $$strCSV .= '"#-#",';
301 $debug and warn "Error _export_table_csv $@\n";
308 # Export the mysql table to an ods file
309 sub _export_table_ods
311 my ($table, $dbh, $dom, $root, $frameworkcode) = @_;
314 my $elementTable = $dom->createElement('table:table');
315 $elementTable->setAttribute('table:name', $table);
316 $elementTable->setAttribute('table:print', 'false');
317 $root->appendChild($elementTable);
318 my $elementRow = $dom->createElement('table:table-row');
319 $elementTable->appendChild($elementRow);
323 # First row with the name of the columns
324 my $query = 'SHOW COLUMNS FROM ' . $table;
325 my $sth = $dbh->prepare($query);
328 while (my $hashRef = $sth->fetchrow_hashref) {
329 $elementCell = $dom->createElement('table:table-cell');
330 $elementCell->setAttribute('office:value-type', 'string');
331 $elementCell->setAttribute('office:value', $hashRef->{Field});
332 $elementRow->appendChild($elementCell);
333 $elementData = $dom->createElement('text:p');
334 $elementCell->appendChild($elementData);
335 $elementData->appendTextNode($hashRef->{Field});
336 push @fields, {name => $hashRef->{Field}, type => ($hashRef->{Type} =~ /int/i)?'float':'string'};
338 # Populate rows with the data from mysql
339 $query = 'SELECT * FROM ' . $table . ' WHERE frameworkcode=?';
340 $sth = $dbh->prepare($query);
341 $sth->execute($frameworkcode);
343 while (my $hashRef = $sth->fetchrow_hashref) {
344 $elementRow = $dom->createElement('table:table-row');
345 $elementTable->appendChild($elementRow);
347 $data = $hashRef->{$_->{name}};
348 if ($_->{type} eq 'float' && !defined($data)) {
350 } elsif ($_->{type} eq 'string' && !defined($data)) {
352 } elsif ($_->{type} eq 'string' && (!$data && $data ne '0')) {
355 $data = _parseContent2Xml($data) if ($_->{type} eq 'string');
356 $elementCell = $dom->createElement('table:table-cell');
357 $elementCell->setAttribute('office:value-type', $_->{type});
358 $elementCell->setAttribute('office:value', $data);
359 $elementRow->appendChild($elementCell);
360 $elementData = $dom->createElement('text:p');
361 $elementCell->appendChild($elementData);
362 $elementData->appendTextNode($data);
367 $debug and warn "Error _export_table_ods $@\n";
374 # Export the mysql table to an excel-xml (openoffice/libreoffice compatible) file
375 sub _export_table_excel
377 my ($table, $dbh, $dom, $root, $frameworkcode) = @_;
380 my $elementWS = $dom->createElement('Worksheet');
381 $elementWS->setAttribute('ss:Name', $table);
382 $root->appendChild($elementWS);
383 my $elementTable = $dom->createElement('ss:Table');
384 $elementWS->appendChild($elementTable);
385 my $elementRow = $dom->createElement('ss:Row');
386 $elementTable->appendChild($elementRow);
388 # First row with the name of the columns
391 my $query = 'SHOW COLUMNS FROM ' . $table;
392 my $sth = $dbh->prepare($query);
395 while (my $hashRef = $sth->fetchrow_hashref) {
396 $elementCell = $dom->createElement('ss:Cell');
397 $elementCell->setAttribute('ss:StyleID', 's27');
398 $elementRow->appendChild($elementCell);
399 $elementData = $dom->createElement('ss:Data');
400 $elementData->setAttribute('ss:Type', 'String');
401 $elementCell->appendChild($elementData);
402 $elementData->appendTextNode($hashRef->{Field});
403 push @fields, {name => $hashRef->{Field}, type => ($hashRef->{Type} =~ /int/i)?'Number':'String'};
405 # Populate rows with the data from mysql
406 $query = 'SELECT * FROM ' . $table . ' WHERE frameworkcode=?';
407 $sth = $dbh->prepare($query);
408 $sth->execute($frameworkcode);
410 while (my $hashRef = $sth->fetchrow_hashref) {
411 $elementRow = $dom->createElement('ss:Row');
412 $elementTable->appendChild($elementRow);
414 $elementCell = $dom->createElement('ss:Cell');
415 $elementRow->appendChild($elementCell);
416 $elementData = $dom->createElement('ss:Data');
417 $elementData->setAttribute('ss:Type', $_->{type});
418 $elementCell->appendChild($elementData);
419 $data = $hashRef->{$_->{name}};
420 if ($_->{type} eq 'Number' && !defined($data)) {
422 } elsif ($_->{type} eq 'String' && !defined($data)) {
424 } elsif ($_->{type} eq 'String' && (!$data && $data ne '0')) {
427 $elementData->appendTextNode(($_->{type} eq 'String')?_parseContent2Xml($data):$data);
432 $debug and warn "Error _export_table_excel $@\n";
436 }#_export_table_excel
444 # Format chars problematics to a correct format for xml.
445 sub _parseContent2Xml
449 $content =~ s/\&(?![a-zA-Z#0-9]{1,4};)/&/g;
450 $content =~ s/</</g;
451 $content =~ s/>/>/g;
456 # Get the tmp directory on the system
460 if ($ENV{'TMP'} && -d $ENV{'TMP'}) {
462 } elsif ($ENV{'TMPDIR'} && -d $ENV{'TMPDIR'}) {
463 $tmp = $ENV{'TMPDIR'};
464 } elsif ($ENV{'TEMP'} && -d $ENV{'TEMP'}) {
471 # Create our tempdir directory for the ods process
476 my $tempdir = (-d $tmp)?$tmp . '/':'./';
477 $tempdir .= 'tmp_ods_' . Digest::MD5::md5_hex(Digest::MD5::md5_hex(time().{}.rand().{}.$$));
490 Creates a temporary directory to create the ods file and read it to store its content in a string.
499 my ($strContent, $lang, $strODSRef) = @_;
506 import File::Temp qw/ tempfile tempdir /;
507 $tempdir = tempdir ( 'tmp_ods_' . $$ . '_XXXXXXXX', DIR => (-d $tmp)?$tmp:'.', CLEANUP => 1);
511 $tempdir = _createTmpDir($tmp);
515 # populate tempdir directory with the ods elements
517 if (open($fh, '>', "$tempdir/content.xml")) {
518 print {$fh} $strContent;
521 if (open($fh, '>', "$tempdir/mimetype")) {
522 print {$fh} 'application/vnd.oasis.opendocument.spreadsheet';
525 if (open($fh, '>', "$tempdir/meta.xml")) {
526 print {$fh} _getMeta($lang);
529 if (open($fh, '>', "$tempdir/styles.xml")) {
530 print {$fh} ODS_STYLES_STR;
533 if (open($fh, '>', "$tempdir/settings.xml")) {
534 print {$fh} ODS_SETTINGS_STR;
537 mkdir($tempdir.'/META-INF/');
538 mkdir($tempdir.'/Configurations2/');
539 mkdir($tempdir.'/Configurations2/acceleator/');
540 mkdir($tempdir.'/Configurations2/images/');
541 mkdir($tempdir.'/Configurations2/popupmenu/');
542 mkdir($tempdir.'/Configurations2/statusbar/');
543 mkdir($tempdir.'/Configurations2/floater/');
544 mkdir($tempdir.'/Configurations2/menubar/');
545 mkdir($tempdir.'/Configurations2/progressbar/');
546 mkdir($tempdir.'/Configurations2/toolbar/');
548 if (open($fh, '>', "$tempdir/META-INF/manifest.xml")) {
549 print {$fh} ODS_MANIFEST_STR;
554 $debug and warn "Error createODS $@\n";
556 # create ods file from tempdir directory
558 require Archive::Zip;
559 import Archive::Zip qw( :ERROR_CODES :CONSTANTS );
560 my $zip = Archive::Zip->new();
561 $zip->addTree( $tempdir, '' );
562 $zip->writeToFileNamed($tempdir . '/new.ods');
565 my $cmd = qx(which zip 2>/dev/null || whereis zip);
567 $cmd = 'zip' if (!$cmd || !-x $cmd);
568 system("cd $tempdir && $cmd -r new.ods ./");
571 # read ods file and return as a string
572 if (-f "$tempdir/new.ods") {
573 if (open ($fh, '<', "$tempdir/new.ods")) {
576 while (read ($fh, $buffer, 65536)) {
577 $$strODSRef .= $buffer;
583 # delete tempdir directory
584 if (!$tempModule && $tempdir) {
587 import File::Temp qw/ rmtree /;
591 system("rm -rf $tempdir");
601 # return Meta content for ods file
606 my $myDate = strftime ("%Y-%m-%dT%H:%M:%S", localtime(time()));
607 my $meta = '<?xml version="1.0" encoding="UTF-8"?>
608 <office:document-meta xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:meta="urn:oasis:names:tc:opendocument:xmlns:meta:1.0" xmlns:ooo="http://openoffice.org/2004/office" office:version="1.0">
610 <meta:generator>ods-php</meta:generator>
611 <meta:creation-date>' . $myDate . '</meta:creation-date>
612 <dc:date>' . $myDate . '</dc:date>
613 <dc:language>' . $lang . '</dc:language>
614 <meta:editing-cycles>2</meta:editing-cycles>
615 <meta:editing-duration>PT15S</meta:editing-duration>
616 <meta:user-defined meta:name="Info 1"/>
617 <meta:user-defined meta:name="Info 2"/>
618 <meta:user-defined meta:name="Info 3"/>
619 <meta:user-defined meta:name="Info 4"/>
621 </office:document-meta>';
626 =head2 ImportFramework
628 Import all the information of a Framework from a excel-xml/ods file.
637 my ($filename, $frameworkcode, $deleteFilename) = @_;
641 my $dbh = C4::Context->dbh;
642 if (-r $filename && $dbh) {
644 if ($filename =~ /\.(csv|ods|xml)$/i) {
647 unlink ($filename) if ($deleteFilename); # remove temporary file
650 if ($extension eq 'ods') {
651 ($tempdir, $filename) = _openODS($filename, $deleteFilename);
656 if ($extension eq 'ods' || $extension eq 'xml') {
657 # They have xml structure, so read it on a dom object
658 my $parser = XML::LibXML->new();
659 $dom = $parser->parse_file($filename);
661 my $root = $dom->documentElement();
664 # They are text files, so open it to read
665 open($dom, '<', $filename);
668 # Process both tables
670 my $numDeletedAux = 0;
671 if (($numDeletedAux = _import_table($dbh, 'marc_tag_structure', $frameworkcode, $dom, ['frameworkcode', 'tagfield'], $extension)) >= 0) {
672 $numDeleted += $numDeletedAux if ($numDeletedAux > 0);
673 if (($numDeletedAux = _import_table($dbh, 'marc_subfield_structure', $frameworkcode, $dom, ['frameworkcode', 'tagfield', 'tagsubfield'], $extension)) >= 0) {
674 $numDeleted += $numDeletedAux if ($numDeletedAux > 0);
675 $ok = ($numDeleted > 0)?$numDeleted:0;
679 $debug and warn "Error ImportFramework couldn't create dom\n";
683 $debug and warn "Error ImportFramework $@\n";
685 if ($extension eq 'csv') {
686 close($dom) if ($dom);
690 unlink ($filename) if ($deleteFilename); # remove temporary file
692 $debug and warn "Error ImportFramework no conex to database or not readeable $filename\n";
694 if ($deleteFilename && $tempdir && -d $tempdir && -w $tempdir) {
697 import File::Temp qw/ rmtree /;
701 system("rm -rf $tempdir");
707 # Open (uncompress) ods file and return the content.xml file
710 my ($filename, $deleteFilename) = @_;
717 import File::Temp qw/ tempfile tempdir /;
718 $tempdir = tempdir ( 'tmp_ods_' . $$ . '_XXXXXXXX', DIR => (-d $tmp)?$tmp:'.', CLEANUP => 1);
722 $tempdir = _createTmpDir($tmp);
726 require Archive::Zip;
727 import Archive::Zip qw( :ERROR_CODES :CONSTANTS );
728 my $zip = Archive::Zip->new($filename);
729 foreach my $file ($zip->members) {
730 next if ($file->isDirectory);
731 (my $extractName = $file->fileName) =~ s{.*/}{};
732 next unless ($extractName eq 'content.xml');
733 $file->extractToFileNamed("$tempdir/$extractName");
737 my $cmd = qx(which unzip 2>/dev/null || whereis unzip);
739 $cmd = 'unzip' if (!$cmd || !-x $cmd);
740 system("$cmd $filename -d $tempdir");
742 if (-f "$tempdir/content.xml") {
743 unlink ($filename) if ($deleteFilename);
744 return ($tempdir, "$tempdir/content.xml");
747 unlink ($filename) if ($deleteFilename);
748 return ($tempdir, undef);
753 # Check the table and columns corresponds with worksheet
754 sub _check_validity_worksheet
756 my ($dbh, $table, $nodeFields, $fieldsA, $format) = @_;
760 my $query = 'DESCRIBE ' . $table;
761 my $sth = $dbh->prepare($query);
764 $query = 'SHOW COLUMNS FROM ' . $table;
765 $sth = $dbh->prepare($query);
768 while (my $hashRef = $sth->fetchrow_hashref) {
769 $fields->{$hashRef->{Field}} = $hashRef->{Field};
777 _getFields($nodeFields, $fieldsR, $format);
781 unless (exists($fields->{$_})) {
788 }#_check_validity_worksheet
791 # Import the data from an excel-xml/ods to mysql tables.
794 my ($dbh, $table, $frameworkcode, $dom, $PKArray, $format) = @_;
798 # Create hash with all elements defined by primary key to know which ones to delete after parsing the spreadsheet
802 $query = 'SELECT ' . join(',', @fields) . ' FROM ' . $table . ' WHERE frameworkcode=?';
803 my $sth = $dbh->prepare($query);
804 $sth->execute($frameworkcode);
806 while (my $hashRef = $sth->fetchrow_hashref) {
808 map { $field .= $hashRef->{$_} . '_'; } @fields;
810 $fields2Delete{$field} = 1;
815 if ($format eq 'csv') {
818 my $query = 'SHOW COLUMNS FROM ' . $table;
819 my $sth = $dbh->prepare($query);
821 while (my $hashRef = $sth->fetchrow_hashref) {
822 push @fieldsName, $hashRef->{Field};
825 $ok = _import_table_csv($dbh, $table, $frameworkcode, $dom, $PKArray, \%fields2Delete, \@fieldsName);
826 } elsif ($format eq 'ods') {
827 $ok = _import_table_ods($dbh, $table, $frameworkcode, $dom, $PKArray, \%fields2Delete);
829 $ok = _import_table_excel($dbh, $table, $frameworkcode, $dom, $PKArray, \%fields2Delete);
832 if (($ok = scalar(keys %fields2Delete)) > 0) {
833 $query = 'DELETE FROM ' . $table . ' WHERE ';
834 map {$query .= $_ . '=? AND ';} @$PKArray;
835 $query = substr($query, 0, -4);
836 my $sth = $dbh->prepare($query);
837 for (keys %fields2Delete) {
839 $sth->execute(($frameworkcode, split('_', $_)));
850 # Insert/Update the row from the spreadsheet in the database
853 my ($dbh, $table, $fields, $dataStr, $updateStr, $dataFields, $dataFieldsHash, $PKArray, $fieldsPK, $fields2Delete) = @_;
857 $query = 'INSERT INTO ' . $table . ' (' . $fields . ') VALUES (' . $dataStr . ') ON DUPLICATE KEY UPDATE ' . $updateStr;
859 my $sth = $dbh->prepare($query);
860 $sth->execute((@$dataFields, @$dataFields));
864 $debug and warn "Error _processRow_DB $@\n";
870 map { $field .= $dataFieldsHash->{$_} . '_'; } @$fieldsPK;
872 delete $fields2Delete->{$field} if (exists($fields2Delete->{$field}));
878 # Process the rows of a worksheet and insert/update them in a mysql table.
879 sub _processRows_Table
881 my ($dbh, $frameworkcode, $nodeR, $table, $PKArray, $format, $fields2Delete) = @_;
890 my @fieldsPK = @$PKArray;
893 if ($nodeR->nodeType == 1 && (($format && $format eq 'ods' && $nodeR->nodeName =~ /(?:table:)?table-row/) || ($nodeR->nodeName =~ /(?:ss:)?Row/)) && $nodeR->hasChildNodes()) {
896 _getFields($nodeR, \@fields, $format);
897 return 0 unless _check_validity_worksheet($dbh, $table, $nodeR, \@fields, $format);
898 $fields = join(',', @fields);
900 map { $dataStr .= '?,';} @fields;
901 chop($dataStr) if ($dataStr);
903 map { $updateStr .= $_ . '=?,';} @fields;
904 chop($updateStr) if ($updateStr);
907 my ($dataFields, $dataFieldsR) = _getDataFields($frameworkcode, $nodeR, \@fields, $format);
908 if (scalar(@fields) == scalar(@$dataFieldsR)) {
909 $ok = _processRow_DB($dbh, $table, $fields, $dataStr, $updateStr, $dataFieldsR, $dataFields, $PKArray, \@fieldsPK, $fields2Delete);
911 warn "$j don't match number of fields " . scalar(@fields) . ' vs ' . scalar(@$dataFieldsR) . "($dataStr)";
916 $nodeR = $nodeR->nextSibling;
924 # Import worksheet from the csv file to the mysql table
925 sub _import_table_csv
927 my ($dbh, $table, $frameworkcode, $dom, $PKArray, $fields2Delete, $fields) = @_;
931 my $numFields = @$fields;
932 my $fieldsNameRead = 0;
934 my ($fieldsStr, $dataStr, $updateStr, @empty_indexes);
935 my @fieldsPK = @$PKArray;
939 my $csv = Text::CSV_XS->new ({ binary => 1 });
940 while ( my $row = $csv->getline($dom) ) {
943 next if scalar @arrData == grep { $_ eq '' } @arrData; # Emtpy lines
944 #$arrData[0] = substr($arrData[0], 1) if ($arrData[0] =~ /^"/);
945 #$arrData[$#arrData] =~ s/[\r\n]+$//;
946 #chop $arrData[$#arrData] if ($arrData[$#arrData] =~ /"$/);
948 if ($arrData[0] eq '#-#' && $arrData[$#arrData] eq '#-#') {
949 # Change of table with separators #-#
951 } elsif ($fieldsNameRead && $arrData[0] eq 'tagfield') {
952 # Change of table because we begin with field name with former field names read
956 if (!$fieldsNameRead) {
957 # New table, we read the field names
959 $fields = [@arrData];
960 my $non_empty_fields = [ grep { $_ ne '' } @$fields ];
961 @empty_indexes = indexes { $_ eq '' } @$fields;
962 $fieldsStr = join(',', @$non_empty_fields);
964 map { $dataStr .= '?,';} @$non_empty_fields;
965 chop($dataStr) if ($dataStr);
967 map { $updateStr .= $_ . '=?,';} @$non_empty_fields;
968 chop($updateStr) if ($updateStr);
974 for my $value (@arrData) {
975 if ( grep { $_ == $j } @empty_indexes ) {
977 } elsif ($fields->[$j] eq 'frameworkcode' && $value ne $frameworkcode) {
978 $dataFields{$fields->[$j]} = $frameworkcode;
979 push @values, $frameworkcode;
980 } elsif ($fields->[$j] eq 'isurl' && defined $value && $value eq q{}) {
981 $dataFields{$fields->[$j]} = undef;
984 $dataFields{$fields->[$j]} = $value;
985 push @values, $value;
989 $ok = _processRow_DB($dbh, $table, $fieldsStr, $dataStr, $updateStr, \@values, \%dataFields, $PKArray, \@fieldsPK, $fields2Delete);
999 # Import worksheet from the ods content.xml file to the mysql table
1000 sub _import_table_ods
1002 my ($dbh, $table, $frameworkcode, $dom, $PKArray, $fields2Delete) = @_;
1004 my $xc = XML::LibXML::XPathContext->new($dom);
1005 $xc->registerNs('xmlns:office','urn:oasis:names:tc:opendocument:xmlns:office:1.0');
1006 $xc->registerNs('xmlns:table','urn:oasis:names:tc:opendocument:xmlns:table:1.0');
1007 $xc->registerNs('xmlns:text','urn:oasis:names:tc:opendocument:xmlns:text:1.0');
1009 @nodes = $xc->findnodes('//table:table[@table:name="' . $table . '"]');
1010 if (@nodes == 1 && $nodes[0]->hasChildNodes()) {
1011 my $nodeR = $nodes[0]->firstChild;
1012 return _processRows_Table($dbh, $frameworkcode, $nodeR, $table, $PKArray, 'ods', $fields2Delete);
1014 $debug and warn "Error _import_table_ods there's not worksheet for $table\n";
1020 # Import worksheet from the excel-xml file to the mysql table
1021 sub _import_table_excel
1023 my ($dbh, $table, $frameworkcode, $dom, $PKArray, $fields2Delete) = @_;
1025 my $xc = XML::LibXML::XPathContext->new($dom);
1026 $xc->registerNs('xmlns','urn:schemas-microsoft-com:office:spreadsheet');
1027 $xc->registerNs('xmlns:ss','urn:schemas-microsoft-com:office:spreadsheet');
1028 $xc->registerNs('xmlns:x','urn:schemas-microsoft-com:office:excel');
1030 @nodes = $xc->findnodes('//ss:Worksheet[@ss:Name="' . $table . '"]');
1032 for (my $i=0; $i < @nodes; $i++) {
1033 my @nodesT = $nodes[$i]->getElementsByTagNameNS('urn:schemas-microsoft-com:office:spreadsheet', 'Table');
1034 if (@nodesT == 1 && $nodesT[0]->hasChildNodes()) {
1035 my $nodeR = $nodesT[0]->firstChild;
1036 return _processRows_Table($dbh, $frameworkcode, $nodeR, $table, $PKArray, undef, $fields2Delete);
1040 $debug and warn "Error _import_table_excel there's not worksheet for $table\n";
1043 }#_import_table_excel
1046 # Get the data from a cell on a ods file through the value attribute or the text node
1053 if ($node->nodeType == 1 && $node->nodeName =~ /(?:table:)?table-cell/) {
1054 if ($node->hasAttributeNS('urn:oasis:names:tc:opendocument:xmlns:office:1.0', 'value')) {
1055 $data = $node->getAttributeNS('urn:oasis:names:tc:opendocument:xmlns:office:1.0', 'value');
1056 } elsif ($node->hasChildNodes()) {
1057 my @nodes2 = $node->getElementsByTagNameNS('urn:oasis:names:tc:opendocument:xmlns:text:1.0', 'p');
1058 if (@nodes2 == 1 && $nodes2[0]->hasChildNodes()) {
1059 $data = $nodes2[0]->firstChild->nodeValue;
1062 if ($node->hasAttributeNS('urn:oasis:names:tc:opendocument:xmlns:table:1.0', 'number-columns-repeated')) {
1063 $repeated = $node->getAttributeNS('urn:oasis:names:tc:opendocument:xmlns:table:1.0', 'number-columns-repeated');
1066 return ($data, $repeated);
1070 # Get the data from a row of a spreadsheet
1073 my ($frameworkcode, $node, $fields, $format) = @_;
1075 my $dataFields = {};
1076 my @dataFieldsA = ();
1077 if ($node && $node->hasChildNodes()) {
1078 my $node2 = $node->firstChild;
1079 my ($data, $repeated);
1084 if ($format && $format eq 'ods') {
1085 ($data, $repeated) = _getDataNodeODS($node2) if ($repeated <= 0);
1089 if ($node2->nodeType == 1 && $node2->nodeName =~ /(?:ss:)?Cell/) {
1090 my @nodes3 = $node2->getElementsByTagNameNS('urn:schemas-microsoft-com:office:spreadsheet', 'Data');
1091 if (@nodes3 == 1 && $nodes3[0]->hasChildNodes()) {
1092 $data = $nodes3[0]->firstChild->nodeValue;
1099 $data = '' if ($data eq '#');
1100 if ( $fields->[$i] eq 'frameworkcode' ) {
1101 $data = $frameworkcode;
1103 elsif ( $fields->[$i] eq 'isurl' ) {
1104 $data = undef if defined $data && $data eq q{};
1106 $dataFields->{$fields->[$i]} = $data;
1107 push @dataFieldsA, $data;
1111 $node2 = $node2->nextSibling if ($repeated <= 0);
1114 return ($dataFields, \@dataFieldsA);
1118 # Get the data from the first row to know the column names
1121 my ($node, $fields, $format) = @_;
1123 if ($node && $node->hasChildNodes()) {
1124 my $node2 = $node->firstChild;
1125 my ($data, $repeated);
1127 if ($format && $format eq 'ods') {
1128 ($data, $repeated) = _getDataNodeODS($node2);
1129 push @$fields, $data if (defined($data));
1131 if ($node2->nodeType == 1 && $node2->nodeName =~ /(?:ss:)?Cell/) {
1132 my @nodes3 = $node2->getElementsByTagNameNS('urn:schemas-microsoft-com:office:spreadsheet', 'Data');
1133 if (@nodes3 == 1 && $nodes3[0]->hasChildNodes()) {
1134 $data = $nodes3[0]->firstChild->nodeValue;
1135 push @$fields, $data;
1139 $node2 = $node2->nextSibling;
1152 Koha Development Team <http://koha-community.org/>