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 under the
8 # terms of the GNU General Public License as published by the Free Software
9 # Foundation; either version 2 of the License, or (at your option) any later
12 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
13 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
14 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
16 # You should have received a copy of the GNU General Public License along
17 # with Koha; if not, write to the Free Software Foundation, Inc.,
18 # 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
23 use XML::LibXML::XPathContext;
24 use Digest::MD5 qw(md5_base64);
25 use POSIX qw(strftime);
31 use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
34 $VERSION = 3.03; # set version for version checking
45 use constant XMLSTR => '<?xml version="1.0" encoding="UTF-8"?>
46 <?mso-application progid="Excel.Sheet"?>
48 xmlns:x="urn:schemas-microsoft-com:office:excel"
49 xmlns="urn:schemas-microsoft-com:office:spreadsheet"
50 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
53 <Style ss:ID="Default" ss:Name="Normal">
54 <Alignment ss:Vertical="Bottom"/>
62 <Font x:Family="Swiss" ss:Color="#0000FF" ss:Bold="1"/>
65 <NumberFormat ss:Format="yyyy\-mm\-dd"/>
68 <NumberFormat ss:Format="yyyy\-mm\-dd\ hh:mm:ss"/>
71 <NumberFormat ss:Format="hh:mm:ss"/>
79 use constant ODSSTR => '<?xml version="1.0" encoding="UTF-8"?>
80 <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">
82 <office:font-face-decls/>
83 <office:automatic-styles/>
84 </office:document-content>';
87 use constant ODS_STYLES_STR => '<?xml version="1.0" encoding="UTF-8"?>
88 <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">
89 <office:font-face-decls></office:font-face-decls>
90 <office:styles></office:styles>
91 <office:automatic-styles></office:automatic-styles>
92 <office:master-styles></office:master-styles>
93 </office:document-styles>';
96 use constant ODS_SETTINGS_STR => '<?xml version="1.0" encoding="UTF-8"?>
97 <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>
98 <config:config-item-set config:name="ooo:view-settings">
99 <config:config-item config:name="VisibleAreaTop" config:type="int">0</config:config-item>
100 <config:config-item config:name="VisibleAreaLeft" config:type="int">0</config:config-item>
101 <config:config-item config:name="VisibleAreaWidth" config:type="int">2000</config:config-item>
102 <config:config-item config:name="VisibleAreaHeight" config:type="int">900</config:config-item>
103 <config:config-item-map-indexed config:name="Views"><config:config-item-map-entry>
104 <config:config-item config:name="ViewId" config:type="string">View1</config:config-item>
105 <config:config-item-map-named config:name="Tables">
106 <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>
107 </config:config-item-map-entry>
108 </config:config-item-map-named>
109 <config:config-item config:name="ActiveTable" config:type="string">Sheet1</config:config-item>
110 <config:config-item config:name="HorizontalScrollbarWidth" config:type="int">270</config:config-item>
111 <config:config-item config:name="ZoomType" config:type="short">0</config:config-item>
112 <config:config-item config:name="ZoomValue" config:type="int">100</config:config-item>
113 <config:config-item config:name="PageViewZoomValue" config:type="int">50</config:config-item>
114 <config:config-item config:name="ShowPageBreakPreview" config:type="boolean">false</config:config-item>
115 <config:config-item config:name="ShowZeroValues" config:type="boolean">true</config:config-item>
116 <config:config-item config:name="ShowNotes" config:type="boolean">true</config:config-item>
117 <config:config-item config:name="ShowGrid" config:type="boolean">true</config:config-item>
118 <config:config-item config:name="GridColor" config:type="long">12632256</config:config-item>
119 <config:config-item config:name="ShowPageBreaks" config:type="boolean">true</config:config-item>
120 <config:config-item config:name="HasColumnRowHeaders" config:type="boolean">true</config:config-item>
121 <config:config-item config:name="HasSheetTabs" config:type="boolean">true</config:config-item>
122 <config:config-item config:name="IsOutlineSymbolsSet" config:type="boolean">true</config:config-item>
123 <config:config-item config:name="IsSnapToRaster" config:type="boolean">false</config:config-item>
124 <config:config-item config:name="RasterIsVisible" config:type="boolean">false</config:config-item>
125 <config:config-item config:name="IsRasterAxisSynchronized" config:type="boolean">true</config:config-item></config:config-item-map-entry></config:config-item-map-indexed>
126 </config:config-item-set>
127 <config:config-item-set config:name="ooo:configuration-settings">
128 <config:config-item config:name="ShowZeroValues" config:type="boolean">true</config:config-item>
129 <config:config-item config:name="ShowNotes" config:type="boolean">true</config:config-item>
130 <config:config-item config:name="ShowGrid" config:type="boolean">true</config:config-item>
131 <config:config-item config:name="GridColor" config:type="long">12632256</config:config-item>
132 <config:config-item config:name="ShowPageBreaks" config:type="boolean">true</config:config-item>
133 <config:config-item config:name="LinkUpdateMode" config:type="short">3</config:config-item>
134 <config:config-item config:name="HasColumnRowHeaders" config:type="boolean">true</config:config-item>
135 <config:config-item config:name="HasSheetTabs" config:type="boolean">true</config:config-item>
136 <config:config-item config:name="IsOutlineSymbolsSet" config:type="boolean">true</config:config-item>
137 <config:config-item config:name="IsSnapToRaster" config:type="boolean">false</config:config-item>
138 <config:config-item config:name="RasterIsVisible" config:type="boolean">false</config:config-item>
139 <config:config-item config:name="IsRasterAxisSynchronized" config:type="boolean">true</config:config-item>
140 <config:config-item config:name="AutoCalculate" config:type="boolean">true</config:config-item>
141 <config:config-item config:name="PrinterName" config:type="string">Generic Printer</config:config-item>
142 <config:config-item config:name="ApplyUserData" config:type="boolean">true</config:config-item>
143 <config:config-item config:name="CharacterCompressionType" config:type="short">0</config:config-item>
144 <config:config-item config:name="SaveVersionOnClose" config:type="boolean">false</config:config-item>
145 <config:config-item config:name="UpdateFromTemplate" config:type="boolean">false</config:config-item>
146 <config:config-item config:name="AllowPrintJobCancel" config:type="boolean">true</config:config-item>
147 <config:config-item config:name="LoadReadonly" config:type="boolean">false</config:config-item>
148 </config:config-item-set>
149 </office:settings></office:document-settings>';
152 use constant ODS_MANIFEST_STR => '<?xml version="1.0" encoding="UTF-8"?>
153 <manifest:manifest xmlns:manifest="urn:oasis:names:tc:opendocument:xmlns:manifest:1.0">
154 <manifest:file-entry manifest:media-type="application/vnd.oasis.opendocument.spreadsheet" manifest:full-path="/"/>
155 <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/statusbar/"/>
156 <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/accelerator/"/>
157 <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/floater/"/>
158 <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/popupmenu/"/>
159 <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/progressbar/"/>
160 <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/menubar/"/>
161 <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/toolbar/"/>
162 <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/images/Bitmaps/"/>
163 <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/images/"/>
164 <manifest:file-entry manifest:media-type="application/vnd.sun.xml.ui.configuration" manifest:full-path="Configurations2/"/>
165 <manifest:file-entry manifest:media-type="text/xml" manifest:full-path="content.xml"/>
166 <manifest:file-entry manifest:media-type="text/xml" manifest:full-path="styles.xml"/>
167 <manifest:file-entry manifest:media-type="text/xml" manifest:full-path="meta.xml"/>
168 <manifest:file-entry manifest:media-type="" manifest:full-path="Thumbnails/"/>
169 <manifest:file-entry manifest:media-type="text/xml" manifest:full-path="settings.xml"/>
170 </manifest:manifest>';
175 C4::ImportExportFramework - Import/Export Framework to Excel-xml/ODS Module Functions
179 use C4::ImportExportFramework;
183 Module to Import/Export Framework to Excel-xml/ODS on intranet administration - MARC Frameworks section
185 Module to Import/Export Framework to Excel-xml/ODS on intranet administration - MARC Frameworks section
186 exporting the tables marc_tag_structure, marc_subfield_structure to excel-xml/ods or viceversa
188 Functions for handling import/export.
195 =head2 ExportFramework
197 Export all the information of a Framework to an excel "xml" file or OpenDocument SpreadSheet "ods" file.
206 my ($frameworkcode, $xmlStrRef, $mode) = @_;
208 my $dbh = C4::Context->dbh;
213 if ($mode eq 'ods' || $mode eq 'excel') {
215 my $parser = XML::LibXML->new();
216 $dom = $parser->parse_string(($mode && $mode eq 'ods')?ODSSTR:XMLSTR);
218 $root = $dom->documentElement();
219 if ($mode && $mode eq 'ods') {
220 my $elementBody = $dom->createElement('office:body');
221 $root->appendChild($elementBody);
222 $elementSS = $dom->createElement('office:spreadsheet');
223 $elementBody->appendChild($elementSS);
228 $debug and warn "Error ExportFramework $@\n";
233 if (_export_table('marc_tag_structure', $dbh, ($mode eq 'csv' || $mode eq 'sql')?$xmlStrRef:$dom, ($mode eq 'ods')?$elementSS:$root, $frameworkcode, $mode)) {
234 if (_export_table('marc_subfield_structure', $dbh, ($mode eq 'csv' || $mode eq 'sql')?$xmlStrRef:$dom, ($mode eq 'ods')?$elementSS:$root, $frameworkcode, $mode)) {
235 $$xmlStrRef = $dom->toString(1) if ($mode eq 'ods' || $mode eq 'excel');
246 # Export all the data from a mysql table to an spreadsheet.
249 my ($table, $dbh, $dom, $root, $frameworkcode, $mode) = @_;
250 if ($mode eq 'csv') {
251 _export_table_csv($table, $dbh, $dom, $root, $frameworkcode);
252 } elsif ($mode eq 'sql') {
253 _export_table_sql($table, $dbh, $dom, $root, $frameworkcode);
254 } elsif ($mode eq 'ods') {
255 _export_table_ods($table, $dbh, $dom, $root, $frameworkcode);
257 _export_table_excel($table, $dbh, $dom, $root, $frameworkcode);
262 # Export the mysql table to an sql file
263 sub _export_table_sql
265 my ($table, $dbh, $strSQL, $root, $frameworkcode) = @_;
268 # First row with the name of the columns
269 my $query = 'SHOW COLUMNS FROM ' . $table;
270 my $sth = $dbh->prepare($query);
273 while (my $hashRef = $sth->fetchrow_hashref) {
274 push @fields, $hashRef->{Field};
276 my $fields = join(',', @fields);
277 $$strSQL .= 'DELETE FROM ' . $table . ' WHERE frameworkcode=' . $dbh->quote($frameworkcode) . ';';
279 # Populate rows with the data from mysql
280 $query = 'SELECT * FROM ' . $table . ' WHERE frameworkcode=?';
281 $sth = $dbh->prepare($query);
282 $sth->execute($frameworkcode);
283 while (my $hashRef = $sth->fetchrow_hashref) {
284 $$strSQL .= 'INSERT INTO ' . $table . ' (' . $fields . ') VALUES (';
286 $$strSQL .= $dbh->quote($hashRef->{$_}) . ',';
289 $$strSQL .= ');' . chr(10);
291 $$strSQL .= chr(10) . chr(10);
294 $debug and warn "Error _export_table_sql $@\n";
301 # Export the mysql table to an csv file
302 sub _export_table_csv
304 my ($table, $dbh, $strCSV, $root, $frameworkcode) = @_;
307 # First row with the name of the columns
308 my $query = 'SHOW COLUMNS FROM ' . $table;
309 my $sth = $dbh->prepare($query);
312 while (my $hashRef = $sth->fetchrow_hashref) {
313 $$strCSV .= '"' . $hashRef->{Field} . '",';
314 push @fields, $hashRef->{Field};
318 # Populate rows with the data from mysql
319 $query = 'SELECT * FROM ' . $table . ' WHERE frameworkcode=?';
320 $sth = $dbh->prepare($query);
321 $sth->execute($frameworkcode);
323 while (my $hashRef = $sth->fetchrow_hashref) {
325 $$strCSV .= '"' . $hashRef->{$_} . '",';
332 # Separator for change of table
333 $$strCSV .= '"#-#",';
340 $debug and warn "Error _export_table_csv $@\n";
347 # Export the mysql table to an ods file
348 sub _export_table_ods
350 my ($table, $dbh, $dom, $root, $frameworkcode) = @_;
353 my $elementTable = $dom->createElement('table:table');
354 $elementTable->setAttribute('table:name', $table);
355 $elementTable->setAttribute('table:print', 'false');
356 $root->appendChild($elementTable);
357 my $elementRow = $dom->createElement('table:table-row');
358 $elementTable->appendChild($elementRow);
362 # First row with the name of the columns
363 my $query = 'SHOW COLUMNS FROM ' . $table;
364 my $sth = $dbh->prepare($query);
367 while (my $hashRef = $sth->fetchrow_hashref) {
368 $elementCell = $dom->createElement('table:table-cell');
369 $elementCell->setAttribute('office:value-type', 'string');
370 $elementCell->setAttribute('office:value', $hashRef->{Field});
371 $elementRow->appendChild($elementCell);
372 $elementData = $dom->createElement('text:p');
373 $elementCell->appendChild($elementData);
374 $elementData->appendTextNode($hashRef->{Field});
375 push @fields, {name => $hashRef->{Field}, type => ($hashRef->{Type} =~ /int/i)?'float':'string'};
377 # Populate rows with the data from mysql
378 $query = 'SELECT * FROM ' . $table . ' WHERE frameworkcode=?';
379 $sth = $dbh->prepare($query);
380 $sth->execute($frameworkcode);
382 while (my $hashRef = $sth->fetchrow_hashref) {
383 $elementRow = $dom->createElement('table:table-row');
384 $elementTable->appendChild($elementRow);
386 $data = $hashRef->{$_->{name}};
387 if ($_->{type} eq 'float' && !defined($data)) {
389 } elsif ($_->{type} eq 'string' && (!$data && $data ne '0')) {
392 $data = _parseContent2Xml($data) if ($_->{type} eq 'string');
393 $elementCell = $dom->createElement('table:table-cell');
394 $elementCell->setAttribute('office:value-type', $_->{type});
395 $elementCell->setAttribute('office:value', $data);
396 $elementRow->appendChild($elementCell);
397 $elementData = $dom->createElement('text:p');
398 $elementCell->appendChild($elementData);
399 $elementData->appendTextNode($data);
404 $debug and warn "Error _export_table_ods $@\n";
411 # Export the mysql table to an excel-xml (openoffice/libreoffice compatible) file
412 sub _export_table_excel
414 my ($table, $dbh, $dom, $root, $frameworkcode) = @_;
417 my $elementWS = $dom->createElement('Worksheet');
418 $elementWS->setAttribute('ss:Name', $table);
419 $root->appendChild($elementWS);
420 my $elementTable = $dom->createElement('ss:Table');
421 $elementWS->appendChild($elementTable);
422 my $elementRow = $dom->createElement('ss:Row');
423 $elementTable->appendChild($elementRow);
425 # First row with the name of the columns
428 my $query = 'SHOW COLUMNS FROM ' . $table;
429 my $sth = $dbh->prepare($query);
432 while (my $hashRef = $sth->fetchrow_hashref) {
433 $elementCell = $dom->createElement('ss:Cell');
434 $elementCell->setAttribute('ss:StyleID', 's27');
435 $elementRow->appendChild($elementCell);
436 $elementData = $dom->createElement('ss:Data');
437 $elementData->setAttribute('ss:Type', 'String');
438 $elementCell->appendChild($elementData);
439 $elementData->appendTextNode($hashRef->{Field});
440 push @fields, {name => $hashRef->{Field}, type => ($hashRef->{Type} =~ /int/i)?'Number':'String'};
442 # Populate rows with the data from mysql
443 $query = 'SELECT * FROM ' . $table . ' WHERE frameworkcode=?';
444 $sth = $dbh->prepare($query);
445 $sth->execute($frameworkcode);
447 while (my $hashRef = $sth->fetchrow_hashref) {
448 $elementRow = $dom->createElement('ss:Row');
449 $elementTable->appendChild($elementRow);
451 $elementCell = $dom->createElement('ss:Cell');
452 $elementRow->appendChild($elementCell);
453 $elementData = $dom->createElement('ss:Data');
454 $elementData->setAttribute('ss:Type', $_->{type});
455 $elementCell->appendChild($elementData);
456 $data = $hashRef->{$_->{name}};
457 if ($_->{type} eq 'Number' && !defined($data)) {
459 } elsif ($_->{type} eq 'String' && (!$data && $data ne '0')) {
462 $elementData->appendTextNode(($_->{type} eq 'String')?_parseContent2Xml($data):$data);
467 $debug and warn "Error _export_table_excel $@\n";
471 }#_export_table_excel
479 # Format chars problematics to a correct format for xml.
480 sub _parseContent2Xml
484 $content =~ s/\&(?![a-zA-Z#0-9]{1,4};)/&/g;
485 $content =~ s/</</g;
486 $content =~ s/>/>/g;
491 # Get the tmp directory on the system
495 if ($ENV{'TMP'} && -d $ENV{'TMP'}) {
497 } elsif ($ENV{'TMPDIR'} && -d $ENV{'TMPDIR'}) {
498 $tmp = $ENV{'TMPDIR'};
499 } elsif ($ENV{'TEMP'} && -d $ENV{'TEMP'}) {
506 # Create our tempdir directory for the ods process
511 my $tempdir = (-d $tmp)?$tmp . '/':'./';
512 $tempdir .= 'tmp_ods_' . Digest::MD5::md5_hex(Digest::MD5::md5_hex(time().{}.rand().{}.$$));
525 Creates a temporary directory to create the ods file and read it to store its content in a string.
534 my ($strContent, $lang, $strODSRef) = @_;
541 import File::Temp qw/ tempfile tempdir /;
542 $tempdir = tempdir ( 'tmp_ods_' . $$ . '_XXXXXXXX', DIR => (-d $tmp)?$tmp:'.', CLEANUP => 1);
546 $tempdir = _createTmpDir($tmp);
549 # populate tempdir directory with the ods elements
551 if (open(OUT, "> $tempdir/content.xml")) {
552 print OUT $strContent;
555 if (open(OUT, "> $tempdir/mimetype")) {
556 print OUT 'application/vnd.oasis.opendocument.spreadsheet';
559 if (open(OUT, "> $tempdir/meta.xml")) {
560 print OUT _getMeta($lang);
563 if (open(OUT, "> $tempdir/styles.xml")) {
564 print OUT ODS_STYLES_STR;
567 if (open(OUT, "> $tempdir/settings.xml")) {
568 print OUT ODS_SETTINGS_STR;
571 mkdir($tempdir.'/META-INF/');
572 mkdir($tempdir.'/Configurations2/');
573 mkdir($tempdir.'/Configurations2/acceleator/');
574 mkdir($tempdir.'/Configurations2/images/');
575 mkdir($tempdir.'/Configurations2/popupmenu/');
576 mkdir($tempdir.'/Configurations2/statusbar/');
577 mkdir($tempdir.'/Configurations2/floater/');
578 mkdir($tempdir.'/Configurations2/menubar/');
579 mkdir($tempdir.'/Configurations2/progressbar/');
580 mkdir($tempdir.'/Configurations2/toolbar/');
581 if (open(OUT, "> $tempdir/META-INF/manifest.xml")) {
582 print OUT ODS_MANIFEST_STR;
587 $debug and warn "Error createODS $@\n";
589 # create ods file from tempdir directory
591 require Archive::Zip;
592 import Archive::Zip qw( :ERROR_CODES :CONSTANTS );
593 my $zip = Archive::Zip->new();
594 $zip->addTree( $tempdir, '' );
595 $zip->writeToFileNamed($tempdir . '/new.ods');
598 my $cmd = qx(which zip 2>/dev/null || whereis zip);
600 $cmd = 'zip' if (!$cmd || !-x $cmd);
601 system("cd $tempdir && $cmd -r new.ods ./");
604 # read ods file and return as a string
605 if (-f "$tempdir/new.ods") {
606 if (open (MYFILE, "$tempdir/new.ods")) {
609 while (read (MYFILE, $buffer, 65536)) {
610 $$strODSRef .= $buffer;
616 # delete tempdir directory
617 if (!$tempModule && $tempdir) {
620 import File::Temp qw/ rmtree /;
624 system("rm -rf $tempdir");
634 # return Meta content for ods file
639 my $myDate = strftime ("%Y-%m-%dT%H:%M:%S", localtime(time()));
640 my $meta = '<?xml version="1.0" encoding="UTF-8"?>
641 <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">
643 <meta:generator>ods-php</meta:generator>
644 <meta:creation-date>' . $myDate . '</meta:creation-date>
645 <dc:date>' . $myDate . '</dc:date>
646 <dc:language>' . $lang . '</dc:language>
647 <meta:editing-cycles>2</meta:editing-cycles>
648 <meta:editing-duration>PT15S</meta:editing-duration>
649 <meta:user-defined meta:name="Info 1"/>
650 <meta:user-defined meta:name="Info 2"/>
651 <meta:user-defined meta:name="Info 3"/>
652 <meta:user-defined meta:name="Info 4"/>
654 </office:document-meta>';
659 =head2 ImportFramework
661 Import all the information of a Framework from a excel-xml/ods file.
670 my ($filename, $frameworkcode, $deleteFilename) = @_;
674 my $dbh = C4::Context->dbh;
675 if (-r $filename && $dbh) {
677 if ($filename =~ /\.(csv|ods|xml|sql)$/i) {
680 unlink ($filename) if ($deleteFilename); # remove temporary file
683 if ($extension eq 'ods') {
684 ($tempdir, $filename) = _openODS($filename, $deleteFilename);
689 if ($extension eq 'ods' || $extension eq 'xml') {
690 # They have xml structure, so read it on a dom object
691 my $parser = XML::LibXML->new();
692 $dom = $parser->parse_file($filename);
694 my $root = $dom->documentElement();
697 # They are text files, so open it to read
698 open($dom, '<', $filename);
701 # For sql we execute the line
702 if ($extension eq 'sql') {
703 _parseSQLLine($dbh, $dom, $frameworkcode);
706 # Process both tables
708 my $numDeletedAux = 0;
709 if (($numDeletedAux = _import_table($dbh, 'marc_tag_structure', $frameworkcode, $dom, ['frameworkcode', 'tagfield'], $extension)) >= 0) {
710 $numDeleted += $numDeletedAux if ($numDeletedAux > 0);
711 if (($numDeletedAux = _import_table($dbh, 'marc_subfield_structure', $frameworkcode, $dom, ['frameworkcode', 'tagfield', 'tagsubfield'], $extension)) >= 0) {
712 $numDeleted += $numDeletedAux if ($numDeletedAux > 0);
713 $ok = ($numDeleted > 0)?$numDeleted:0;
718 $debug and warn "Error ImportFramework couldn't create dom\n";
722 $debug and warn "Error ImportFramework $@\n";
724 if ($extension eq 'sql' || $extension eq 'csv') {
725 close($dom) if ($dom);
729 unlink ($filename) if ($deleteFilename); # remove temporary file
731 $debug and warn "Error ImportFramework no conex to database or not readeable $filename\n";
733 if ($deleteFilename && $tempdir && -d $tempdir && -w $tempdir) {
736 import File::Temp qw/ rmtree /;
740 system("rm -rf $tempdir");
747 # Parse the sql statement to see if the frameworkcode is correct
748 # We're checking only the delete and insert SQL commands generated in the export process
751 my ($dbh, $dom, $frameworkcode) = @_;
755 require SQL::Statement;
756 $parser = SQL::Parser->new('AnyData');
757 $parser->{RaiseError}=1;
758 $parser->{PrintError}=0;
760 my $literalEscape = (C4::Context->config("db_scheme") eq 'mysql')?'\\':'\'';
766 # we don't want to execute any sql statement, only the ones dealing with frameworks
767 next unless ($line =~ /^\s*(?i:DELETE\s+FROM|INSERT\s+INTO)\s+(?:marc_tag_structure|marc_subfield_structure)/);
769 # We check if the frameworkcode is the same, if not we change it
770 unless ($line =~ /'$frameworkcode'/) {
774 $line = substr($line, 0 ,-1) if ($line =~ /;$/);
775 my $stmt = SQL::Statement->new($line, $parser);
776 my $where = $stmt->where();
777 if ($where && $where->op() eq '=' && $line =~ /^\s*DELETE/) {
778 $line =~ s/frameworkcode='.+?'/frameworkcode='$frameworkcode';/ unless ($_ =~ /frameworkcode='$frameworkcode'/);
783 # Due to lacking of backward compatibility
784 if ($parser->VERSION < 1.30) {
785 $table = lc($stmt->tables(0)->name());
786 @arrFields = map{lc($_->name)} $stmt->columns;
787 @arrValues = $stmt->row_values();
789 $table = $stmt->tables(0)->name();
790 @arrValues = $stmt->row_values(0);
791 my @aux = $stmt->column_defs();
793 push @arrFields, $_->{value};
796 if (scalar(@arrFields) == scalar(@arrValues)) {
800 if ($_ eq 'frameworkcode' && $arrValues[$j] ne $frameworkcode) {
801 $arrValues[$j] = $dbh->quote($frameworkcode);
804 $arrValues[$j] = $dbh->quote($arrValues[$j]);
808 $line = 'INSERT INTO ' . $table . ' (' . join(',', @arrFields) . ') VALUES (' . join(',', @arrValues) . ');' if ($modified);
817 $line .= ';' unless ($line =~ /;$/);
818 if ($line =~ /^\s*DELETE/) {
819 $line =~ s/frameworkcode='.+?'/frameworkcode='$frameworkcode'/ unless ($_ =~ /frameworkcode='$frameworkcode'/);
820 } elsif ($line =~ /^\s*INSERT\s+INTO\s+(.*?)\s+\((.*?frameworkcode.*?)\)\s+VALUES\s+\((.+)\)\s*;\s*$/) {
824 my @arrFields = split(/\s*,\s*/, $fields);
827 _parseSQLInsertValues($values, $literalEscape, \@arrValues);
829 if (scalar(@arrFields) == scalar(@arrValues)) {
831 for (my $i=0; $i < @arrFields; $i++) {
832 if ($arrFields[$i] eq 'frameworkcode' && $arrValues[$i]->{value} ne $frameworkcode) {
833 $arrValues[$i]->{value} = $dbh->quote($frameworkcode);
835 } elsif ($arrValues[$i]->{literal}) {
836 $arrValues[$i]->{value} = $dbh->quote($arrValues[$i]->{value});
840 $line = "INSERT INTO $table ($fields) VALUES (" . join(',', map {$_->{value}} @arrValues) . ');';
853 # Simple sub to get the values from the insert sentence
854 sub _parseSQLInsertValues
856 my ($values, $literalEscape, $arrValues) = @_;
858 my ($posBegin, $posLiteral, $currentPos, $lengthValues, $currentChar);
859 $lengthValues = length($values);
861 while ($currentPos < $lengthValues) {
862 $currentChar = substr($values, $currentPos++, 1);
863 next if ($currentChar =~ /^\s$/);
864 next if ($posBegin && $currentChar !~ /^[,']$/);
866 if ($currentChar eq '\'') {
867 $posBegin = $currentPos;
868 $posLiteral = $posBegin;
870 $posBegin = $currentPos -1;
873 if ($currentChar eq ',') {
874 unless ($posLiteral) {
875 push @$arrValues, {literal => 0, value => substr($values, $posBegin, $currentPos -(1 + $posBegin))};
878 } elsif ($currentChar eq '\'' && $posLiteral) {
879 next if ($literalEscape eq '\\' && substr($values, $currentPos -2, 1) eq $literalEscape);
880 if ($literalEscape eq '\'' && substr($values, $currentPos, 1) eq $literalEscape) {
884 push @$arrValues, {literal => 1 , value => substr($values, $posBegin, $currentPos -( 1 + $posBegin))};
885 $currentPos++ if (substr($values, $currentPos, 1) eq ',');
888 } # We shouldn't get to here if the sql sentence is correct
891 push @$arrValues, {literal => ($posLiteral)?1:0, value => substr($values, $posBegin, $currentPos - $posBegin)} if ($posBegin);
892 }#_parseSQLInsertValues
895 # Open (uncompress) ods file and return the content.xml file
898 my ($filename, $deleteFilename) = @_;
905 import File::Temp qw/ tempfile tempdir /;
906 $tempdir = tempdir ( 'tmp_ods_' . $$ . '_XXXXXXXX', DIR => (-d $tmp)?$tmp:'.', CLEANUP => 1);
910 $tempdir = _createTmpDir($tmp);
914 require Archive::Zip;
915 import Archive::Zip qw( :ERROR_CODES :CONSTANTS );
916 my $zip = Archive::Zip->new($filename);
917 foreach my $file ($zip->members) {
918 next if ($file->isDirectory);
919 (my $extractName = $file->fileName) =~ s{.*/}{};
920 next unless ($extractName eq 'content.xml');
921 $file->extractToFileNamed("$tempdir/$extractName");
925 my $cmd = qx(which unzip 2>/dev/null || whereis unzip);
927 $cmd = 'unzip' if (!$cmd || !-x $cmd);
928 system("$cmd $filename -d $tempdir");
930 if (-f "$tempdir/content.xml") {
931 unlink ($filename) if ($deleteFilename);
932 return ($tempdir, "$tempdir/content.xml");
935 unlink ($filename) if ($deleteFilename);
936 return ($tempdir, undef);
941 # Check the table and columns corresponds with worksheet
942 sub _check_validity_worksheet
944 my ($dbh, $table, $nodeFields, $fieldsA, $format) = @_;
948 my $query = 'DESCRIBE ' . $table;
949 my $sth = $dbh->prepare($query);
952 $query = 'SHOW COLUMNS FROM ' . $table;
953 $sth = $dbh->prepare($query);
956 while (my $hashRef = $sth->fetchrow_hashref) {
957 $fields->{$hashRef->{Field}} = $hashRef->{Field};
965 _getFields($nodeFields, $fieldsR, $format);
969 unless (exists($fields->{$_})) {
976 }#_check_validity_worksheet
979 # Import the data from an excel-xml/ods to mysql tables.
982 my ($dbh, $table, $frameworkcode, $dom, $PKArray, $format) = @_;
986 # Create hash with all elements defined by primary key to know which ones to delete after parsing the spreadsheet
990 $query = 'SELECT ' . join(',', @fields) . ' FROM ' . $table . ' WHERE frameworkcode=?';
991 my $sth = $dbh->prepare($query);
992 $sth->execute($frameworkcode);
994 while (my $hashRef = $sth->fetchrow_hashref) {
996 map { $field .= $hashRef->{$_} . '_'; } @fields;
998 $fields2Delete{$field} = 1;
1003 if ($format eq 'csv') {
1004 my @fieldsName = ();
1006 my $query = 'SHOW COLUMNS FROM ' . $table;
1007 my $sth = $dbh->prepare($query);
1009 while (my $hashRef = $sth->fetchrow_hashref) {
1010 push @fieldsName, $hashRef->{Field};
1013 $ok = _import_table_csv($dbh, $table, $frameworkcode, $dom, $PKArray, \%fields2Delete, \@fieldsName);
1014 } elsif ($format eq 'ods') {
1015 $ok = _import_table_ods($dbh, $table, $frameworkcode, $dom, $PKArray, \%fields2Delete);
1017 $ok = _import_table_excel($dbh, $table, $frameworkcode, $dom, $PKArray, \%fields2Delete);
1020 if (($ok = scalar(keys %fields2Delete)) > 0) {
1021 $query = 'DELETE FROM ' . $table . ' WHERE ';
1022 map {$query .= $_ . '=? AND ';} @$PKArray;
1023 $query = substr($query, 0, -4);
1024 my $sth = $dbh->prepare($query);
1025 for (keys %fields2Delete) {
1027 $sth->execute(($frameworkcode, split('_', $_)));
1038 # Insert/Update the row from the spreadsheet in the database
1041 my ($dbh, $db_scheme, $table, $fields, $dataStr, $updateStr, $dataFields, $dataFieldsHash, $PKArray, $fieldsPK, $fields2Delete) = @_;
1045 if ($db_scheme eq 'mysql') {
1046 $query = 'INSERT INTO ' . $table . ' (' . $fields . ') VALUES (' . $dataStr . ') ON DUPLICATE KEY UPDATE ' . $updateStr;
1048 $query = 'INSERT INTO ' . $table . ' (' . $fields . ') VALUES (' . $dataStr . ')';
1051 my $sth = $dbh->prepare($query);
1052 if ($db_scheme eq 'mysql') {
1053 $sth->execute((@$dataFields, @$dataFields));
1055 $sth->execute(@$dataFields);
1059 unless ($db_scheme eq 'mysql') {
1060 $query = 'UPDATE ' . $table . ' SET ' . $updateStr . ' WHERE ';
1061 map {$query .= $_ . '=? AND ';} @$PKArray;
1062 $query = substr($query, 0, -4);
1064 my $sth2 = $dbh->prepare($query);
1066 map {push @dataPK, $dataFieldsHash->{$_};} @$PKArray;
1067 $sth2->execute((@$dataFields, @dataPK));
1069 $ok = 1 unless ($@);
1071 $debug and warn "Error _processRows_Table $@\n";
1077 map { $field .= $dataFieldsHash->{$_} . '_'; } @$fieldsPK;
1079 delete $fields2Delete->{$field} if (exists($fields2Delete->{$field}));
1085 # Process the rows of a worksheet and insert/update them in a mysql table.
1086 sub _processRows_Table
1088 my ($dbh, $frameworkcode, $nodeR, $table, $PKArray, $format, $fields2Delete) = @_;
1096 my $db_scheme = C4::Context->config("db_scheme");
1098 my @fieldsPK = @$PKArray;
1101 if ($nodeR->nodeType == 1 && (($format && $format eq 'ods' && $nodeR->nodeName =~ /(?:table:)?table-row/) || ($nodeR->nodeName =~ /(?:ss:)?Row/)) && $nodeR->hasChildNodes()) {
1104 _getFields($nodeR, \@fields, $format);
1105 return 0 unless _check_validity_worksheet($dbh, $table, $nodeR, \@fields, $format);
1106 $fields = join(',', @fields);
1108 map { $dataStr .= '?,';} @fields;
1109 chop($dataStr) if ($dataStr);
1111 map { $updateStr .= $_ . '=?,';} @fields;
1112 chop($updateStr) if ($updateStr);
1115 my ($dataFields, $dataFieldsR) = _getDataFields($frameworkcode, $nodeR, \@fields, $format);
1116 if (scalar(@fields) == scalar(@$dataFieldsR)) {
1117 $ok = _processRow_DB($dbh, $db_scheme, $table, $fields, $dataStr, $updateStr, $dataFieldsR, $dataFields, $PKArray, \@fieldsPK, $fields2Delete);
1122 $nodeR = $nodeR->nextSibling;
1125 }#_processRows_Table
1130 # Import worksheet from the csv file to the mysql table
1131 sub _import_table_csv
1133 my ($dbh, $table, $frameworkcode, $dom, $PKArray, $fields2Delete, $fields) = @_;
1136 my $numFields = @$fields;
1137 my $fieldsNameRead = 0;
1139 my ($fieldsStr, $dataStr, $updateStr);
1140 my $db_scheme = C4::Context->config("db_scheme");
1141 my @fieldsPK = @$PKArray;
1148 if ($row =~ /(?:".*?",?)+/) {
1149 @arrData = split('","', $row);
1150 $arrData[0] = substr($arrData[0], 1) if ($arrData[0] =~ /^"/);
1151 chomp $arrData[$#arrData];
1152 chop $arrData[$#arrData] if ($arrData[$#arrData] =~ /"$/);
1154 if ($arrData[0] eq '#-#' && $arrData[$#arrData] eq '#-#') {
1155 # Change of table with separators #-#
1157 } elsif ($fieldsNameRead && $arrData[0] eq 'tagfield') {
1158 # Change of table because we begin with field name with former field names read
1159 seek($dom, $pos, 0);
1162 if (scalar(@$fields) == scalar(@arrData)) {
1163 if (!$fieldsNameRead) {
1164 # New table, we read the field names
1165 $fieldsNameRead = 1;
1166 for (my $i=0; $i < @arrData; $i++) {
1167 if ($arrData[$i] ne $fields->[$i]) {
1168 $fieldsNameRead = 0;
1172 if ($fieldsNameRead) {
1173 $fieldsStr = join(',', @$fields);
1175 map { $dataStr .= '?,';} @$fields;
1176 chop($dataStr) if ($dataStr);
1178 map { $updateStr .= $_ . '=?,';} @$fields;
1179 chop($updateStr) if ($updateStr);
1184 my %dataFields = ();
1186 if ($fields->[$j] eq 'frameworkcode' && $_ ne $frameworkcode) {
1187 $dataFields{$fields->[$j]} = $frameworkcode;
1188 $arrData[$j] = $frameworkcode;
1190 $dataFields{$fields->[$j]} = $_;
1194 $ok = _processRow_DB($dbh, $db_scheme, $table, $fieldsStr, $dataStr, $updateStr, \@arrData, \%dataFields, $PKArray, \@fieldsPK, $fields2Delete);
1207 # Import worksheet from the ods content.xml file to the mysql table
1208 sub _import_table_ods
1210 my ($dbh, $table, $frameworkcode, $dom, $PKArray, $fields2Delete) = @_;
1212 my $xc = XML::LibXML::XPathContext->new($dom);
1213 $xc->registerNs('xmlns:office','urn:oasis:names:tc:opendocument:xmlns:office:1.0');
1214 $xc->registerNs('xmlns:table','urn:oasis:names:tc:opendocument:xmlns:table:1.0');
1215 $xc->registerNs('xmlns:text','urn:oasis:names:tc:opendocument:xmlns:text:1.0');
1217 @nodes = $xc->findnodes('//table:table[@table:name="' . $table . '"]');
1218 if (@nodes == 1 && $nodes[0]->hasChildNodes()) {
1219 my $nodeR = $nodes[0]->firstChild;
1220 return _processRows_Table($dbh, $frameworkcode, $nodeR, $table, $PKArray, 'ods', $fields2Delete);
1222 $debug and warn "Error _import_table_ods there's not worksheet for $table\n";
1228 # Import worksheet from the excel-xml file to the mysql table
1229 sub _import_table_excel
1231 my ($dbh, $table, $frameworkcode, $dom, $PKArray, $fields2Delete) = @_;
1233 my $xc = XML::LibXML::XPathContext->new($dom);
1234 $xc->registerNs('xmlns','urn:schemas-microsoft-com:office:spreadsheet');
1235 $xc->registerNs('xmlns:ss','urn:schemas-microsoft-com:office:spreadsheet');
1236 $xc->registerNs('xmlns:x','urn:schemas-microsoft-com:office:excel');
1238 @nodes = $xc->findnodes('//ss:Worksheet[@ss:Name="' . $table . '"]');
1240 for (my $i=0; $i < @nodes; $i++) {
1241 my @nodesT = $nodes[$i]->getElementsByTagNameNS('urn:schemas-microsoft-com:office:spreadsheet', 'Table');
1242 if (@nodesT == 1 && $nodesT[0]->hasChildNodes()) {
1243 my $nodeR = $nodesT[0]->firstChild;
1244 return _processRows_Table($dbh, $frameworkcode, $nodeR, $table, $PKArray, undef, $fields2Delete);
1248 $debug and warn "Error _import_table_excel there's not worksheet for $table\n";
1251 }#_import_table_excel
1254 # Get the data from a cell on a ods file through the value attribute or the text node
1261 if ($node->nodeType == 1 && $node->nodeName =~ /(?:table:)?table-cell/) {
1262 if ($node->hasAttributeNS('urn:oasis:names:tc:opendocument:xmlns:office:1.0', 'value')) {
1263 $data = $node->getAttributeNS('urn:oasis:names:tc:opendocument:xmlns:office:1.0', 'value');
1264 } elsif ($node->hasChildNodes()) {
1265 my @nodes2 = $node->getElementsByTagNameNS('urn:oasis:names:tc:opendocument:xmlns:text:1.0', 'p');
1266 if (@nodes2 == 1 && $nodes2[0]->hasChildNodes()) {
1267 $data = $nodes2[0]->firstChild->nodeValue;
1270 if ($node->hasAttributeNS('urn:oasis:names:tc:opendocument:xmlns:table:1.0', 'number-columns-repeated')) {
1271 $repeated = $node->getAttributeNS('urn:oasis:names:tc:opendocument:xmlns:table:1.0', 'number-columns-repeated');
1274 return ($data, $repeated);
1278 # Get the data from a row of a spreadsheet
1281 my ($frameworkcode, $node, $fields, $format) = @_;
1283 my $dataFields = {};
1284 my @dataFieldsA = ();
1285 if ($node && $node->hasChildNodes()) {
1286 my $node2 = $node->firstChild;
1287 my ($data, $repeated);
1292 if ($format && $format eq 'ods') {
1293 ($data, $repeated) = _getDataNodeODS($node2) if ($repeated <= 0);
1295 $ok = 1 if (defined($data));
1297 if ($node2->nodeType == 1 && $node2->nodeName =~ /(?:ss:)?Cell/) {
1298 my @nodes3 = $node2->getElementsByTagNameNS('urn:schemas-microsoft-com:office:spreadsheet', 'Data');
1299 if (@nodes3 == 1 && $nodes3[0]->hasChildNodes()) {
1300 $data = $nodes3[0]->firstChild->nodeValue;
1306 $data = '' if ($data eq '#');
1307 $data = $frameworkcode if ($fields->[$i] eq 'frameworkcode');
1308 $dataFields->{$fields->[$i]} = $data;
1309 push @dataFieldsA, $data;
1313 $node2 = $node2->nextSibling if ($repeated <= 0);
1316 return ($dataFields, \@dataFieldsA);
1320 # Get the data from the first row to know the column names
1323 my ($node, $fields, $format) = @_;
1325 if ($node && $node->hasChildNodes()) {
1326 my $node2 = $node->firstChild;
1327 my ($data, $repeated);
1329 if ($format && $format eq 'ods') {
1330 ($data, $repeated) = _getDataNodeODS($node2);
1331 push @$fields, $data if (defined($data));
1333 if ($node2->nodeType == 1 && $node2->nodeName =~ /(?:ss:)?Cell/) {
1334 my @nodes3 = $node2->getElementsByTagNameNS('urn:schemas-microsoft-com:office:spreadsheet', 'Data');
1335 if (@nodes3 == 1 && $nodes3[0]->hasChildNodes()) {
1336 $data = $nodes3[0]->firstChild->nodeValue;
1337 push @$fields, $data;
1341 $node2 = $node2->nextSibling;
1354 Koha Development Team <http://koha-community.org/>