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);
31 use vars
qw(@ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
44 use constant XMLSTR
=> '<?xml version="1.0" encoding="UTF-8"?>
45 <?mso-application progid="Excel.Sheet"?>
47 xmlns:x="urn:schemas-microsoft-com:office:excel"
48 xmlns="urn:schemas-microsoft-com:office:spreadsheet"
49 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
52 <Style ss:ID="Default" ss:Name="Normal">
53 <Alignment ss:Vertical="Bottom"/>
61 <Font x:Family="Swiss" ss:Color="#0000FF" ss:Bold="1"/>
64 <NumberFormat ss:Format="yyyy\-mm\-dd"/>
67 <NumberFormat ss:Format="yyyy\-mm\-dd\ hh:mm:ss"/>
70 <NumberFormat ss:Format="hh:mm:ss"/>
78 use constant ODSSTR
=> '<?xml version="1.0" encoding="UTF-8"?>
79 <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">
81 <office:font-face-decls/>
82 <office:automatic-styles/>
83 </office:document-content>';
86 use constant ODS_STYLES_STR
=> '<?xml version="1.0" encoding="UTF-8"?>
87 <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">
88 <office:font-face-decls></office:font-face-decls>
89 <office:styles></office:styles>
90 <office:automatic-styles></office:automatic-styles>
91 <office:master-styles></office:master-styles>
92 </office:document-styles>';
95 use constant ODS_SETTINGS_STR
=> '<?xml version="1.0" encoding="UTF-8"?>
96 <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>
97 <config:config-item-set config:name="ooo:view-settings">
98 <config:config-item config:name="VisibleAreaTop" config:type="int">0</config:config-item>
99 <config:config-item config:name="VisibleAreaLeft" config:type="int">0</config:config-item>
100 <config:config-item config:name="VisibleAreaWidth" config:type="int">2000</config:config-item>
101 <config:config-item config:name="VisibleAreaHeight" config:type="int">900</config:config-item>
102 <config:config-item-map-indexed config:name="Views"><config:config-item-map-entry>
103 <config:config-item config:name="ViewId" config:type="string">View1</config:config-item>
104 <config:config-item-map-named config:name="Tables">
105 <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>
106 </config:config-item-map-entry>
107 </config:config-item-map-named>
108 <config:config-item config:name="ActiveTable" config:type="string">Sheet1</config:config-item>
109 <config:config-item config:name="HorizontalScrollbarWidth" config:type="int">270</config:config-item>
110 <config:config-item config:name="ZoomType" config:type="short">0</config:config-item>
111 <config:config-item config:name="ZoomValue" config:type="int">100</config:config-item>
112 <config:config-item config:name="PageViewZoomValue" config:type="int">50</config:config-item>
113 <config:config-item config:name="ShowPageBreakPreview" config:type="boolean">false</config:config-item>
114 <config:config-item config:name="ShowZeroValues" config:type="boolean">true</config:config-item>
115 <config:config-item config:name="ShowNotes" config:type="boolean">true</config:config-item>
116 <config:config-item config:name="ShowGrid" config:type="boolean">true</config:config-item>
117 <config:config-item config:name="GridColor" config:type="long">12632256</config:config-item>
118 <config:config-item config:name="ShowPageBreaks" config:type="boolean">true</config:config-item>
119 <config:config-item config:name="HasColumnRowHeaders" config:type="boolean">true</config:config-item>
120 <config:config-item config:name="HasSheetTabs" config:type="boolean">true</config:config-item>
121 <config:config-item config:name="IsOutlineSymbolsSet" config:type="boolean">true</config:config-item>
122 <config:config-item config:name="IsSnapToRaster" config:type="boolean">false</config:config-item>
123 <config:config-item config:name="RasterIsVisible" config:type="boolean">false</config:config-item>
124 <config:config-item config:name="IsRasterAxisSynchronized" config:type="boolean">true</config:config-item></config:config-item-map-entry></config:config-item-map-indexed>
125 </config:config-item-set>
126 <config:config-item-set config:name="ooo:configuration-settings">
127 <config:config-item config:name="ShowZeroValues" config:type="boolean">true</config:config-item>
128 <config:config-item config:name="ShowNotes" config:type="boolean">true</config:config-item>
129 <config:config-item config:name="ShowGrid" config:type="boolean">true</config:config-item>
130 <config:config-item config:name="GridColor" config:type="long">12632256</config:config-item>
131 <config:config-item config:name="ShowPageBreaks" config:type="boolean">true</config:config-item>
132 <config:config-item config:name="LinkUpdateMode" config:type="short">3</config:config-item>
133 <config:config-item config:name="HasColumnRowHeaders" config:type="boolean">true</config:config-item>
134 <config:config-item config:name="HasSheetTabs" config:type="boolean">true</config:config-item>
135 <config:config-item config:name="IsOutlineSymbolsSet" config:type="boolean">true</config:config-item>
136 <config:config-item config:name="IsSnapToRaster" config:type="boolean">false</config:config-item>
137 <config:config-item config:name="RasterIsVisible" config:type="boolean">false</config:config-item>
138 <config:config-item config:name="IsRasterAxisSynchronized" config:type="boolean">true</config:config-item>
139 <config:config-item config:name="AutoCalculate" config:type="boolean">true</config:config-item>
140 <config:config-item config:name="PrinterName" config:type="string">Generic Printer</config:config-item>
141 <config:config-item config:name="ApplyUserData" config:type="boolean">true</config:config-item>
142 <config:config-item config:name="CharacterCompressionType" config:type="short">0</config:config-item>
143 <config:config-item config:name="SaveVersionOnClose" config:type="boolean">false</config:config-item>
144 <config:config-item config:name="UpdateFromTemplate" config:type="boolean">false</config:config-item>
145 <config:config-item config:name="AllowPrintJobCancel" config:type="boolean">true</config:config-item>
146 <config:config-item config:name="LoadReadonly" config:type="boolean">false</config:config-item>
147 </config:config-item-set>
148 </office:settings></office:document-settings>';
151 use constant ODS_MANIFEST_STR
=> '<?xml version="1.0" encoding="UTF-8"?>
152 <manifest:manifest xmlns:manifest="urn:oasis:names:tc:opendocument:xmlns:manifest:1.0">
153 <manifest:file-entry manifest:media-type="application/vnd.oasis.opendocument.spreadsheet" manifest:full-path="/"/>
154 <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/statusbar/"/>
155 <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/accelerator/"/>
156 <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/floater/"/>
157 <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/popupmenu/"/>
158 <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/progressbar/"/>
159 <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/menubar/"/>
160 <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/toolbar/"/>
161 <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/images/Bitmaps/"/>
162 <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/images/"/>
163 <manifest:file-entry manifest:media-type="application/vnd.sun.xml.ui.configuration" manifest:full-path="Configurations2/"/>
164 <manifest:file-entry manifest:media-type="text/xml" manifest:full-path="content.xml"/>
165 <manifest:file-entry manifest:media-type="text/xml" manifest:full-path="styles.xml"/>
166 <manifest:file-entry manifest:media-type="text/xml" manifest:full-path="meta.xml"/>
167 <manifest:file-entry manifest:media-type="" manifest:full-path="Thumbnails/"/>
168 <manifest:file-entry manifest:media-type="text/xml" manifest:full-path="settings.xml"/>
169 </manifest:manifest>';
174 C4::ImportExportFramework - Import/Export Framework to Excel-xml/ODS Module Functions
178 use C4::ImportExportFramework;
182 Module to Import/Export Framework to Excel-xml/ODS on intranet administration - MARC Frameworks section
184 Module to Import/Export Framework to Excel-xml/ODS on intranet administration - MARC Frameworks section
185 exporting the tables marc_tag_structure, marc_subfield_structure to excel-xml/ods or viceversa
187 Functions for handling import/export.
194 =head2 ExportFramework
196 Export all the information of a Framework to an excel "xml" file or OpenDocument SpreadSheet "ods" file.
205 my ($frameworkcode, $xmlStrRef, $mode) = @_;
207 my $dbh = C4
::Context
->dbh;
212 if ($mode eq 'ods' || $mode eq 'excel') {
214 my $parser = XML
::LibXML
->new();
215 $dom = $parser->parse_string(($mode && $mode eq 'ods')?ODSSTR
:XMLSTR
);
217 $root = $dom->documentElement();
218 if ($mode && $mode eq 'ods') {
219 my $elementBody = $dom->createElement('office:body');
220 $root->appendChild($elementBody);
221 $elementSS = $dom->createElement('office:spreadsheet');
222 $elementBody->appendChild($elementSS);
227 $debug and warn "Error ExportFramework $@\n";
232 if (_export_table
('marc_tag_structure', $dbh, ($mode eq 'csv')?
$xmlStrRef:$dom, ($mode eq 'ods')?
$elementSS:$root, $frameworkcode, $mode)) {
233 if (_export_table
('marc_subfield_structure', $dbh, ($mode eq 'csv')?
$xmlStrRef:$dom, ($mode eq 'ods')?
$elementSS:$root, $frameworkcode, $mode)) {
234 $$xmlStrRef = $dom->toString(1) if ($mode eq 'ods' || $mode eq 'excel');
245 # Export all the data from a mysql table to an spreadsheet.
248 my ($table, $dbh, $dom, $root, $frameworkcode, $mode) = @_;
249 if ($mode eq 'csv') {
250 _export_table_csv
($table, $dbh, $dom, $root, $frameworkcode);
251 } elsif ($mode eq 'ods') {
252 _export_table_ods
($table, $dbh, $dom, $root, $frameworkcode);
254 _export_table_excel
($table, $dbh, $dom, $root, $frameworkcode);
258 # Export the mysql table to an csv file
259 sub _export_table_csv
261 my ($table, $dbh, $strCSV, $root, $frameworkcode) = @_;
264 # First row with the name of the columns
265 my $query = 'SHOW COLUMNS FROM ' . $table;
266 my $sth = $dbh->prepare($query);
269 while (my $hashRef = $sth->fetchrow_hashref) {
270 $$strCSV .= '"' . $hashRef->{Field
} . '",';
271 push @fields, $hashRef->{Field
};
275 # Populate rows with the data from mysql
276 $query = 'SELECT * FROM ' . $table . ' WHERE frameworkcode=?';
277 $sth = $dbh->prepare($query);
278 $sth->execute($frameworkcode);
280 while (my $hashRef = $sth->fetchrow_hashref) {
282 $hashRef->{$_} =~ s/[\r\n]//g;
283 $$strCSV .= '"' . $hashRef->{$_} . '",';
290 # Separator for change of table
291 $$strCSV .= '"#-#",';
298 $debug and warn "Error _export_table_csv $@\n";
305 # Export the mysql table to an ods file
306 sub _export_table_ods
308 my ($table, $dbh, $dom, $root, $frameworkcode) = @_;
311 my $elementTable = $dom->createElement('table:table');
312 $elementTable->setAttribute('table:name', $table);
313 $elementTable->setAttribute('table:print', 'false');
314 $root->appendChild($elementTable);
315 my $elementRow = $dom->createElement('table:table-row');
316 $elementTable->appendChild($elementRow);
320 # First row with the name of the columns
321 my $query = 'SHOW COLUMNS FROM ' . $table;
322 my $sth = $dbh->prepare($query);
325 while (my $hashRef = $sth->fetchrow_hashref) {
326 $elementCell = $dom->createElement('table:table-cell');
327 $elementCell->setAttribute('office:value-type', 'string');
328 $elementCell->setAttribute('office:value', $hashRef->{Field
});
329 $elementRow->appendChild($elementCell);
330 $elementData = $dom->createElement('text:p');
331 $elementCell->appendChild($elementData);
332 $elementData->appendTextNode($hashRef->{Field
});
333 push @fields, {name
=> $hashRef->{Field
}, type
=> ($hashRef->{Type
} =~ /int/i)?
'float':'string'};
335 # Populate rows with the data from mysql
336 $query = 'SELECT * FROM ' . $table . ' WHERE frameworkcode=?';
337 $sth = $dbh->prepare($query);
338 $sth->execute($frameworkcode);
340 while (my $hashRef = $sth->fetchrow_hashref) {
341 $elementRow = $dom->createElement('table:table-row');
342 $elementTable->appendChild($elementRow);
344 $data = $hashRef->{$_->{name
}};
345 if ($_->{type
} eq 'float' && !defined($data)) {
347 } elsif ($_->{type
} eq 'string' && (!$data && $data ne '0')) {
350 $data = _parseContent2Xml
($data) if ($_->{type
} eq 'string');
351 $elementCell = $dom->createElement('table:table-cell');
352 $elementCell->setAttribute('office:value-type', $_->{type
});
353 $elementCell->setAttribute('office:value', $data);
354 $elementRow->appendChild($elementCell);
355 $elementData = $dom->createElement('text:p');
356 $elementCell->appendChild($elementData);
357 $elementData->appendTextNode($data);
362 $debug and warn "Error _export_table_ods $@\n";
369 # Export the mysql table to an excel-xml (openoffice/libreoffice compatible) file
370 sub _export_table_excel
372 my ($table, $dbh, $dom, $root, $frameworkcode) = @_;
375 my $elementWS = $dom->createElement('Worksheet');
376 $elementWS->setAttribute('ss:Name', $table);
377 $root->appendChild($elementWS);
378 my $elementTable = $dom->createElement('ss:Table');
379 $elementWS->appendChild($elementTable);
380 my $elementRow = $dom->createElement('ss:Row');
381 $elementTable->appendChild($elementRow);
383 # First row with the name of the columns
386 my $query = 'SHOW COLUMNS FROM ' . $table;
387 my $sth = $dbh->prepare($query);
390 while (my $hashRef = $sth->fetchrow_hashref) {
391 $elementCell = $dom->createElement('ss:Cell');
392 $elementCell->setAttribute('ss:StyleID', 's27');
393 $elementRow->appendChild($elementCell);
394 $elementData = $dom->createElement('ss:Data');
395 $elementData->setAttribute('ss:Type', 'String');
396 $elementCell->appendChild($elementData);
397 $elementData->appendTextNode($hashRef->{Field
});
398 push @fields, {name
=> $hashRef->{Field
}, type
=> ($hashRef->{Type
} =~ /int/i)?
'Number':'String'};
400 # Populate rows with the data from mysql
401 $query = 'SELECT * FROM ' . $table . ' WHERE frameworkcode=?';
402 $sth = $dbh->prepare($query);
403 $sth->execute($frameworkcode);
405 while (my $hashRef = $sth->fetchrow_hashref) {
406 $elementRow = $dom->createElement('ss:Row');
407 $elementTable->appendChild($elementRow);
409 $elementCell = $dom->createElement('ss:Cell');
410 $elementRow->appendChild($elementCell);
411 $elementData = $dom->createElement('ss:Data');
412 $elementData->setAttribute('ss:Type', $_->{type
});
413 $elementCell->appendChild($elementData);
414 $data = $hashRef->{$_->{name
}};
415 if ($_->{type
} eq 'Number' && !defined($data)) {
417 } elsif ($_->{type
} eq 'String' && (!$data && $data ne '0')) {
420 $elementData->appendTextNode(($_->{type
} eq 'String')?_parseContent2Xml
($data):$data);
425 $debug and warn "Error _export_table_excel $@\n";
429 }#_export_table_excel
437 # Format chars problematics to a correct format for xml.
438 sub _parseContent2Xml
442 $content =~ s/\&(?![a-zA-Z#0-9]{1,4};)/&/g;
443 $content =~ s/</</g;
444 $content =~ s/>/>/g;
449 # Get the tmp directory on the system
453 if ($ENV{'TMP'} && -d
$ENV{'TMP'}) {
455 } elsif ($ENV{'TMPDIR'} && -d
$ENV{'TMPDIR'}) {
456 $tmp = $ENV{'TMPDIR'};
457 } elsif ($ENV{'TEMP'} && -d
$ENV{'TEMP'}) {
464 # Create our tempdir directory for the ods process
469 my $tempdir = (-d
$tmp)?
$tmp . '/':'./';
470 $tempdir .= 'tmp_ods_' . Digest
::MD5
::md5_hex
(Digest
::MD5
::md5_hex
(time().{}.rand().{}.$$));
483 Creates a temporary directory to create the ods file and read it to store its content in a string.
492 my ($strContent, $lang, $strODSRef) = @_;
499 import File
::Temp qw
/ tempfile tempdir /;
500 $tempdir = tempdir
( 'tmp_ods_' . $$ . '_XXXXXXXX', DIR
=> (-d
$tmp)?
$tmp:'.', CLEANUP
=> 1);
504 $tempdir = _createTmpDir
($tmp);
508 # populate tempdir directory with the ods elements
510 if (open($fh, '>', "$tempdir/content.xml")) {
511 print {$fh} $strContent;
514 if (open($fh, '>', "$tempdir/mimetype")) {
515 print {$fh} 'application/vnd.oasis.opendocument.spreadsheet';
518 if (open($fh, '>', "$tempdir/meta.xml")) {
519 print {$fh} _getMeta
($lang);
522 if (open($fh, '>', "$tempdir/styles.xml")) {
523 print {$fh} ODS_STYLES_STR
;
526 if (open($fh, '>', "$tempdir/settings.xml")) {
527 print {$fh} ODS_SETTINGS_STR
;
530 mkdir($tempdir.'/META-INF/');
531 mkdir($tempdir.'/Configurations2/');
532 mkdir($tempdir.'/Configurations2/acceleator/');
533 mkdir($tempdir.'/Configurations2/images/');
534 mkdir($tempdir.'/Configurations2/popupmenu/');
535 mkdir($tempdir.'/Configurations2/statusbar/');
536 mkdir($tempdir.'/Configurations2/floater/');
537 mkdir($tempdir.'/Configurations2/menubar/');
538 mkdir($tempdir.'/Configurations2/progressbar/');
539 mkdir($tempdir.'/Configurations2/toolbar/');
541 if (open($fh, '>', "$tempdir/META-INF/manifest.xml")) {
542 print {$fh} ODS_MANIFEST_STR
;
547 $debug and warn "Error createODS $@\n";
549 # create ods file from tempdir directory
551 require Archive
::Zip
;
552 import Archive
::Zip
qw( :ERROR_CODES :CONSTANTS );
553 my $zip = Archive
::Zip
->new();
554 $zip->addTree( $tempdir, '' );
555 $zip->writeToFileNamed($tempdir . '/new.ods');
558 my $cmd = qx(which zip
2>/dev/null
|| whereis zip
);
560 $cmd = 'zip' if (!$cmd || !-x
$cmd);
561 system("cd $tempdir && $cmd -r new.ods ./");
564 # read ods file and return as a string
565 if (-f
"$tempdir/new.ods") {
566 if (open ($fh, '<', "$tempdir/new.ods")) {
569 while (read ($fh, $buffer, 65536)) {
570 $$strODSRef .= $buffer;
576 # delete tempdir directory
577 if (!$tempModule && $tempdir) {
580 import File
::Temp qw
/ rmtree /;
584 system("rm -rf $tempdir");
594 # return Meta content for ods file
599 my $myDate = strftime
("%Y-%m-%dT%H:%M:%S", localtime(time()));
600 my $meta = '<?xml version="1.0" encoding="UTF-8"?>
601 <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">
603 <meta:generator>ods-php</meta:generator>
604 <meta:creation-date>' . $myDate . '</meta:creation-date>
605 <dc:date>' . $myDate . '</dc:date>
606 <dc:language>' . $lang . '</dc:language>
607 <meta:editing-cycles>2</meta:editing-cycles>
608 <meta:editing-duration>PT15S</meta:editing-duration>
609 <meta:user-defined meta:name="Info 1"/>
610 <meta:user-defined meta:name="Info 2"/>
611 <meta:user-defined meta:name="Info 3"/>
612 <meta:user-defined meta:name="Info 4"/>
614 </office:document-meta>';
619 =head2 ImportFramework
621 Import all the information of a Framework from a excel-xml/ods file.
630 my ($filename, $frameworkcode, $deleteFilename) = @_;
634 my $dbh = C4
::Context
->dbh;
635 if (-r
$filename && $dbh) {
637 if ($filename =~ /\.(csv|ods|xml)$/i) {
640 unlink ($filename) if ($deleteFilename); # remove temporary file
643 if ($extension eq 'ods') {
644 ($tempdir, $filename) = _openODS
($filename, $deleteFilename);
649 if ($extension eq 'ods' || $extension eq 'xml') {
650 # They have xml structure, so read it on a dom object
651 my $parser = XML
::LibXML
->new();
652 $dom = $parser->parse_file($filename);
654 my $root = $dom->documentElement();
657 # They are text files, so open it to read
658 open($dom, '<', $filename);
661 # Process both tables
663 my $numDeletedAux = 0;
664 if (($numDeletedAux = _import_table
($dbh, 'marc_tag_structure', $frameworkcode, $dom, ['frameworkcode', 'tagfield'], $extension)) >= 0) {
665 $numDeleted += $numDeletedAux if ($numDeletedAux > 0);
666 if (($numDeletedAux = _import_table
($dbh, 'marc_subfield_structure', $frameworkcode, $dom, ['frameworkcode', 'tagfield', 'tagsubfield'], $extension)) >= 0) {
667 $numDeleted += $numDeletedAux if ($numDeletedAux > 0);
668 $ok = ($numDeleted > 0)?
$numDeleted:0;
672 $debug and warn "Error ImportFramework couldn't create dom\n";
676 $debug and warn "Error ImportFramework $@\n";
678 if ($extension eq 'csv') {
679 close($dom) if ($dom);
683 unlink ($filename) if ($deleteFilename); # remove temporary file
685 $debug and warn "Error ImportFramework no conex to database or not readeable $filename\n";
687 if ($deleteFilename && $tempdir && -d
$tempdir && -w
$tempdir) {
690 import File
::Temp qw
/ rmtree /;
694 system("rm -rf $tempdir");
700 # Open (uncompress) ods file and return the content.xml file
703 my ($filename, $deleteFilename) = @_;
710 import File
::Temp qw
/ tempfile tempdir /;
711 $tempdir = tempdir
( 'tmp_ods_' . $$ . '_XXXXXXXX', DIR
=> (-d
$tmp)?
$tmp:'.', CLEANUP
=> 1);
715 $tempdir = _createTmpDir
($tmp);
719 require Archive
::Zip
;
720 import Archive
::Zip
qw( :ERROR_CODES :CONSTANTS );
721 my $zip = Archive
::Zip
->new($filename);
722 foreach my $file ($zip->members) {
723 next if ($file->isDirectory);
724 (my $extractName = $file->fileName) =~ s{.*/}{};
725 next unless ($extractName eq 'content.xml');
726 $file->extractToFileNamed("$tempdir/$extractName");
730 my $cmd = qx(which unzip
2>/dev/null
|| whereis unzip
);
732 $cmd = 'unzip' if (!$cmd || !-x
$cmd);
733 system("$cmd $filename -d $tempdir");
735 if (-f
"$tempdir/content.xml") {
736 unlink ($filename) if ($deleteFilename);
737 return ($tempdir, "$tempdir/content.xml");
740 unlink ($filename) if ($deleteFilename);
741 return ($tempdir, undef);
746 # Check the table and columns corresponds with worksheet
747 sub _check_validity_worksheet
749 my ($dbh, $table, $nodeFields, $fieldsA, $format) = @_;
753 my $query = 'DESCRIBE ' . $table;
754 my $sth = $dbh->prepare($query);
757 $query = 'SHOW COLUMNS FROM ' . $table;
758 $sth = $dbh->prepare($query);
761 while (my $hashRef = $sth->fetchrow_hashref) {
762 $fields->{$hashRef->{Field
}} = $hashRef->{Field
};
770 _getFields
($nodeFields, $fieldsR, $format);
774 unless (exists($fields->{$_})) {
781 }#_check_validity_worksheet
784 # Import the data from an excel-xml/ods to mysql tables.
787 my ($dbh, $table, $frameworkcode, $dom, $PKArray, $format) = @_;
791 # Create hash with all elements defined by primary key to know which ones to delete after parsing the spreadsheet
795 $query = 'SELECT ' . join(',', @fields) . ' FROM ' . $table . ' WHERE frameworkcode=?';
796 my $sth = $dbh->prepare($query);
797 $sth->execute($frameworkcode);
799 while (my $hashRef = $sth->fetchrow_hashref) {
801 map { $field .= $hashRef->{$_} . '_'; } @fields;
803 $fields2Delete{$field} = 1;
808 if ($format eq 'csv') {
811 my $query = 'SHOW COLUMNS FROM ' . $table;
812 my $sth = $dbh->prepare($query);
814 while (my $hashRef = $sth->fetchrow_hashref) {
815 push @fieldsName, $hashRef->{Field
};
818 $ok = _import_table_csv
($dbh, $table, $frameworkcode, $dom, $PKArray, \
%fields2Delete, \
@fieldsName);
819 } elsif ($format eq 'ods') {
820 $ok = _import_table_ods
($dbh, $table, $frameworkcode, $dom, $PKArray, \
%fields2Delete);
822 $ok = _import_table_excel
($dbh, $table, $frameworkcode, $dom, $PKArray, \
%fields2Delete);
825 if (($ok = scalar(keys %fields2Delete)) > 0) {
826 $query = 'DELETE FROM ' . $table . ' WHERE ';
827 map {$query .= $_ . '=? AND ';} @
$PKArray;
828 $query = substr($query, 0, -4);
829 my $sth = $dbh->prepare($query);
830 for (keys %fields2Delete) {
832 $sth->execute(($frameworkcode, split('_', $_)));
843 # Insert/Update the row from the spreadsheet in the database
846 my ($dbh, $db_scheme, $table, $fields, $dataStr, $updateStr, $dataFields, $dataFieldsHash, $PKArray, $fieldsPK, $fields2Delete) = @_;
850 if ($db_scheme eq 'mysql') {
851 $query = 'INSERT INTO ' . $table . ' (' . $fields . ') VALUES (' . $dataStr . ') ON DUPLICATE KEY UPDATE ' . $updateStr;
853 $query = 'INSERT INTO ' . $table . ' (' . $fields . ') VALUES (' . $dataStr . ')';
856 my $sth = $dbh->prepare($query);
857 if ($db_scheme eq 'mysql') {
858 $sth->execute((@
$dataFields, @
$dataFields));
860 $sth->execute(@
$dataFields);
864 unless ($db_scheme eq 'mysql') {
865 $query = 'UPDATE ' . $table . ' SET ' . $updateStr . ' WHERE ';
866 map {$query .= $_ . '=? AND ';} @
$PKArray;
867 $query = substr($query, 0, -4);
869 my $sth2 = $dbh->prepare($query);
871 map {push @dataPK, $dataFieldsHash->{$_};} @
$PKArray;
872 $sth2->execute((@
$dataFields, @dataPK));
876 $debug and warn "Error _processRows_Table $@\n";
882 map { $field .= $dataFieldsHash->{$_} . '_'; } @
$fieldsPK;
884 delete $fields2Delete->{$field} if (exists($fields2Delete->{$field}));
890 # Process the rows of a worksheet and insert/update them in a mysql table.
891 sub _processRows_Table
893 my ($dbh, $frameworkcode, $nodeR, $table, $PKArray, $format, $fields2Delete) = @_;
901 my $db_scheme = C4
::Context
->config("db_scheme");
903 my @fieldsPK = @
$PKArray;
906 if ($nodeR->nodeType == 1 && (($format && $format eq 'ods' && $nodeR->nodeName =~ /(?:table:)?table-row/) || ($nodeR->nodeName =~ /(?:ss:)?Row/)) && $nodeR->hasChildNodes()) {
909 _getFields
($nodeR, \
@fields, $format);
910 return 0 unless _check_validity_worksheet
($dbh, $table, $nodeR, \
@fields, $format);
911 $fields = join(',', @fields);
913 map { $dataStr .= '?,';} @fields;
914 chop($dataStr) if ($dataStr);
916 map { $updateStr .= $_ . '=?,';} @fields;
917 chop($updateStr) if ($updateStr);
920 my ($dataFields, $dataFieldsR) = _getDataFields
($frameworkcode, $nodeR, \
@fields, $format);
921 if (scalar(@fields) == scalar(@
$dataFieldsR)) {
922 $ok = _processRow_DB
($dbh, $db_scheme, $table, $fields, $dataStr, $updateStr, $dataFieldsR, $dataFields, $PKArray, \
@fieldsPK, $fields2Delete);
927 $nodeR = $nodeR->nextSibling;
935 # Import worksheet from the csv file to the mysql table
936 sub _import_table_csv
938 my ($dbh, $table, $frameworkcode, $dom, $PKArray, $fields2Delete, $fields) = @_;
942 my $numFields = @
$fields;
943 my $fieldsNameRead = 0;
945 my ($fieldsStr, $dataStr, $updateStr);
946 my $db_scheme = C4
::Context
->config("db_scheme");
947 my @fieldsPK = @
$PKArray;
954 # Check whether the line has an unfinished field, i.e., a field with CR/LF in its data
955 if ($row =~ /,"[^"]*[\r\n]+$/ || $row =~ /^[^"]+[\r\n]+$/) {
956 $row =~ s/[\r\n]+$//;
961 $row = $partialRow . $row;
964 # Line OK, process it
965 if ($row =~ /(?:".*?",?)+/) {
966 @arrData = split('","', $row);
967 $arrData[0] = substr($arrData[0], 1) if ($arrData[0] =~ /^"/);
968 $arrData[$#arrData] =~ s/[\r\n]+$//;
969 chop $arrData[$#arrData] if ($arrData[$#arrData] =~ /"$/);
971 if ($arrData[0] eq '#-#' && $arrData[$#arrData] eq '#-#') {
972 # Change of table with separators #-#
974 } elsif ($fieldsNameRead && $arrData[0] eq 'tagfield') {
975 # Change of table because we begin with field name with former field names read
979 if (scalar(@
$fields) == scalar(@arrData)) {
980 if (!$fieldsNameRead) {
981 # New table, we read the field names
983 for (my $i=0; $i < @arrData; $i++) {
984 if ($arrData[$i] ne $fields->[$i]) {
989 if ($fieldsNameRead) {
990 $fieldsStr = join(',', @
$fields);
992 map { $dataStr .= '?,';} @
$fields;
993 chop($dataStr) if ($dataStr);
995 map { $updateStr .= $_ . '=?,';} @
$fields;
996 chop($updateStr) if ($updateStr);
1001 my %dataFields = ();
1003 if ($fields->[$j] eq 'frameworkcode' && $_ ne $frameworkcode) {
1004 $dataFields{$fields->[$j]} = $frameworkcode;
1005 $arrData[$j] = $frameworkcode;
1007 $dataFields{$fields->[$j]} = $_;
1011 $ok = _processRow_DB
($dbh, $db_scheme, $table, $fieldsStr, $dataStr, $updateStr, \
@arrData, \
%dataFields, $PKArray, \
@fieldsPK, $fields2Delete);
1024 # Import worksheet from the ods content.xml file to the mysql table
1025 sub _import_table_ods
1027 my ($dbh, $table, $frameworkcode, $dom, $PKArray, $fields2Delete) = @_;
1029 my $xc = XML
::LibXML
::XPathContext
->new($dom);
1030 $xc->registerNs('xmlns:office','urn:oasis:names:tc:opendocument:xmlns:office:1.0');
1031 $xc->registerNs('xmlns:table','urn:oasis:names:tc:opendocument:xmlns:table:1.0');
1032 $xc->registerNs('xmlns:text','urn:oasis:names:tc:opendocument:xmlns:text:1.0');
1034 @nodes = $xc->findnodes('//table:table[@table:name="' . $table . '"]');
1035 if (@nodes == 1 && $nodes[0]->hasChildNodes()) {
1036 my $nodeR = $nodes[0]->firstChild;
1037 return _processRows_Table
($dbh, $frameworkcode, $nodeR, $table, $PKArray, 'ods', $fields2Delete);
1039 $debug and warn "Error _import_table_ods there's not worksheet for $table\n";
1045 # Import worksheet from the excel-xml file to the mysql table
1046 sub _import_table_excel
1048 my ($dbh, $table, $frameworkcode, $dom, $PKArray, $fields2Delete) = @_;
1050 my $xc = XML
::LibXML
::XPathContext
->new($dom);
1051 $xc->registerNs('xmlns','urn:schemas-microsoft-com:office:spreadsheet');
1052 $xc->registerNs('xmlns:ss','urn:schemas-microsoft-com:office:spreadsheet');
1053 $xc->registerNs('xmlns:x','urn:schemas-microsoft-com:office:excel');
1055 @nodes = $xc->findnodes('//ss:Worksheet[@ss:Name="' . $table . '"]');
1057 for (my $i=0; $i < @nodes; $i++) {
1058 my @nodesT = $nodes[$i]->getElementsByTagNameNS('urn:schemas-microsoft-com:office:spreadsheet', 'Table');
1059 if (@nodesT == 1 && $nodesT[0]->hasChildNodes()) {
1060 my $nodeR = $nodesT[0]->firstChild;
1061 return _processRows_Table
($dbh, $frameworkcode, $nodeR, $table, $PKArray, undef, $fields2Delete);
1065 $debug and warn "Error _import_table_excel there's not worksheet for $table\n";
1068 }#_import_table_excel
1071 # Get the data from a cell on a ods file through the value attribute or the text node
1078 if ($node->nodeType == 1 && $node->nodeName =~ /(?:table:)?table-cell/) {
1079 if ($node->hasAttributeNS('urn:oasis:names:tc:opendocument:xmlns:office:1.0', 'value')) {
1080 $data = $node->getAttributeNS('urn:oasis:names:tc:opendocument:xmlns:office:1.0', 'value');
1081 } elsif ($node->hasChildNodes()) {
1082 my @nodes2 = $node->getElementsByTagNameNS('urn:oasis:names:tc:opendocument:xmlns:text:1.0', 'p');
1083 if (@nodes2 == 1 && $nodes2[0]->hasChildNodes()) {
1084 $data = $nodes2[0]->firstChild->nodeValue;
1087 if ($node->hasAttributeNS('urn:oasis:names:tc:opendocument:xmlns:table:1.0', 'number-columns-repeated')) {
1088 $repeated = $node->getAttributeNS('urn:oasis:names:tc:opendocument:xmlns:table:1.0', 'number-columns-repeated');
1091 return ($data, $repeated);
1095 # Get the data from a row of a spreadsheet
1098 my ($frameworkcode, $node, $fields, $format) = @_;
1100 my $dataFields = {};
1101 my @dataFieldsA = ();
1102 if ($node && $node->hasChildNodes()) {
1103 my $node2 = $node->firstChild;
1104 my ($data, $repeated);
1109 if ($format && $format eq 'ods') {
1110 ($data, $repeated) = _getDataNodeODS
($node2) if ($repeated <= 0);
1112 $ok = 1 if (defined($data));
1114 if ($node2->nodeType == 1 && $node2->nodeName =~ /(?:ss:)?Cell/) {
1115 my @nodes3 = $node2->getElementsByTagNameNS('urn:schemas-microsoft-com:office:spreadsheet', 'Data');
1116 if (@nodes3 == 1 && $nodes3[0]->hasChildNodes()) {
1117 $data = $nodes3[0]->firstChild->nodeValue;
1123 $data = '' if ($data eq '#');
1124 $data = $frameworkcode if ($fields->[$i] eq 'frameworkcode');
1125 $dataFields->{$fields->[$i]} = $data;
1126 push @dataFieldsA, $data;
1130 $node2 = $node2->nextSibling if ($repeated <= 0);
1133 return ($dataFields, \
@dataFieldsA);
1137 # Get the data from the first row to know the column names
1140 my ($node, $fields, $format) = @_;
1142 if ($node && $node->hasChildNodes()) {
1143 my $node2 = $node->firstChild;
1144 my ($data, $repeated);
1146 if ($format && $format eq 'ods') {
1147 ($data, $repeated) = _getDataNodeODS
($node2);
1148 push @
$fields, $data if (defined($data));
1150 if ($node2->nodeType == 1 && $node2->nodeName =~ /(?:ss:)?Cell/) {
1151 my @nodes3 = $node2->getElementsByTagNameNS('urn:schemas-microsoft-com:office:spreadsheet', 'Data');
1152 if (@nodes3 == 1 && $nodes3[0]->hasChildNodes()) {
1153 $data = $nodes3[0]->firstChild->nodeValue;
1154 push @
$fields, $data;
1158 $node2 = $node2->nextSibling;
1171 Koha Development Team <http://koha-community.org/>