Line 0
Link Here
|
|
|
1 |
package C4::ImportExportFramework; |
2 |
|
3 |
# Copyright 2010-2011 MASmedios.com y Ministerio de Cultura |
4 |
# |
5 |
# This file is part of Koha. |
6 |
# |
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 |
10 |
# version. |
11 |
# |
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. |
15 |
# |
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. |
19 |
|
20 |
use strict; |
21 |
use warnings; |
22 |
use XML::LibXML; |
23 |
use XML::LibXML::XPathContext; |
24 |
use Digest::MD5 qw(md5_base64); |
25 |
use POSIX qw(strftime); |
26 |
|
27 |
use C4::Context; |
28 |
use C4::Debug; |
29 |
|
30 |
|
31 |
use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS); |
32 |
|
33 |
BEGIN { |
34 |
$VERSION = 3.03; # set version for version checking |
35 |
require Exporter; |
36 |
@ISA = qw(Exporter); |
37 |
@EXPORT = qw( |
38 |
&ExportFramework |
39 |
&ImportFramework |
40 |
&createODS |
41 |
); |
42 |
} |
43 |
|
44 |
|
45 |
use constant XMLSTR => '<?xml version="1.0" encoding="UTF-8"?> |
46 |
<?mso-application progid="Excel.Sheet"?> |
47 |
<Workbook |
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"> |
51 |
|
52 |
<Styles> |
53 |
<Style ss:ID="Default" ss:Name="Normal"> |
54 |
<Alignment ss:Vertical="Bottom"/> |
55 |
<Borders/> |
56 |
<Font/> |
57 |
<Interior/> |
58 |
<NumberFormat/> |
59 |
<Protection/> |
60 |
</Style> |
61 |
<Style ss:ID="s27"> |
62 |
<Font x:Family="Swiss" ss:Color="#0000FF" ss:Bold="1"/> |
63 |
</Style> |
64 |
<Style ss:ID="s21"> |
65 |
<NumberFormat ss:Format="yyyy\-mm\-dd"/> |
66 |
</Style> |
67 |
<Style ss:ID="s22"> |
68 |
<NumberFormat ss:Format="yyyy\-mm\-dd\ hh:mm:ss"/> |
69 |
</Style> |
70 |
<Style ss:ID="s23"> |
71 |
<NumberFormat ss:Format="hh:mm:ss"/> |
72 |
</Style> |
73 |
</Styles> |
74 |
|
75 |
</Workbook> |
76 |
'; |
77 |
|
78 |
|
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"> |
81 |
<office:scripts/> |
82 |
<office:font-face-decls/> |
83 |
<office:automatic-styles/> |
84 |
</office:document-content>'; |
85 |
|
86 |
|
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>'; |
94 |
|
95 |
|
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>'; |
150 |
|
151 |
|
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>'; |
171 |
|
172 |
|
173 |
=head1 NAME |
174 |
|
175 |
C4::ImportExportFramework - Import/Export Framework to Excel-xml/ODS Module Functions |
176 |
|
177 |
=head1 SYNOPSIS |
178 |
|
179 |
use C4::ImportExportFramework; |
180 |
|
181 |
=head1 DESCRIPTION |
182 |
|
183 |
Module to Import/Export Framework to Excel-xml/ODS on intranet administration - MARC Frameworks section |
184 |
|
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 |
187 |
|
188 |
Functions for handling import/export. |
189 |
|
190 |
|
191 |
=head1 SUBROUTINES |
192 |
|
193 |
|
194 |
|
195 |
=head2 ExportFramework |
196 |
|
197 |
Export all the information of a Framework to an excel "xml" file or OpenDocument SpreadSheet "ods" file. |
198 |
|
199 |
return : |
200 |
succes |
201 |
|
202 |
=cut |
203 |
|
204 |
sub ExportFramework |
205 |
{ |
206 |
my ($frameworkcode, $xmlStrRef, $mode) = @_; |
207 |
|
208 |
my $dbh = C4::Context->dbh; |
209 |
if ($dbh) { |
210 |
my $dom; |
211 |
my $root; |
212 |
my $elementSS; |
213 |
if ($mode eq 'ods' || $mode eq 'excel') { |
214 |
eval { |
215 |
my $parser = XML::LibXML->new(); |
216 |
$dom = $parser->parse_string(($mode && $mode eq 'ods')?ODSSTR:XMLSTR); |
217 |
if ($dom) { |
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); |
224 |
} |
225 |
} |
226 |
}; |
227 |
if ($@) { |
228 |
$debug and warn "Error ExportFramework $@\n"; |
229 |
return 0; |
230 |
} |
231 |
} |
232 |
|
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'); |
236 |
return 1; |
237 |
} |
238 |
} |
239 |
} |
240 |
return 0; |
241 |
}#ExportFramework |
242 |
|
243 |
|
244 |
|
245 |
|
246 |
# Export all the data from a mysql table to an spreadsheet. |
247 |
sub _export_table |
248 |
{ |
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); |
256 |
} else { |
257 |
_export_table_excel($table, $dbh, $dom, $root, $frameworkcode); |
258 |
} |
259 |
} |
260 |
|
261 |
|
262 |
# Export the mysql table to an sql file |
263 |
sub _export_table_sql |
264 |
{ |
265 |
my ($table, $dbh, $strSQL, $root, $frameworkcode) = @_; |
266 |
|
267 |
eval { |
268 |
# First row with the name of the columns |
269 |
my $query = 'SHOW COLUMNS FROM ' . $table; |
270 |
my $sth = $dbh->prepare($query); |
271 |
$sth->execute(); |
272 |
my @fields = (); |
273 |
while (my $hashRef = $sth->fetchrow_hashref) { |
274 |
push @fields, $hashRef->{Field}; |
275 |
} |
276 |
my $fields = join(',', @fields); |
277 |
$$strSQL .= 'DELETE FROM ' . $table . ' WHERE frameworkcode=' . $dbh->quote($frameworkcode) . ';'; |
278 |
$$strSQL .= chr(10); |
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 ('; |
285 |
for (@fields) { |
286 |
$$strSQL .= $dbh->quote($hashRef->{$_}) . ','; |
287 |
} |
288 |
chop $$strSQL; |
289 |
$$strSQL .= ');' . chr(10); |
290 |
} |
291 |
$$strSQL .= chr(10) . chr(10); |
292 |
}; |
293 |
if ($@) { |
294 |
$debug and warn "Error _export_table_sql $@\n"; |
295 |
return 0; |
296 |
} |
297 |
return 1; |
298 |
}#_export_table_sql |
299 |
|
300 |
|
301 |
# Export the mysql table to an csv file |
302 |
sub _export_table_csv |
303 |
{ |
304 |
my ($table, $dbh, $strCSV, $root, $frameworkcode) = @_; |
305 |
|
306 |
eval { |
307 |
# First row with the name of the columns |
308 |
my $query = 'SHOW COLUMNS FROM ' . $table; |
309 |
my $sth = $dbh->prepare($query); |
310 |
$sth->execute(); |
311 |
my @fields = (); |
312 |
while (my $hashRef = $sth->fetchrow_hashref) { |
313 |
$$strCSV .= '"' . $hashRef->{Field} . '",'; |
314 |
push @fields, $hashRef->{Field}; |
315 |
} |
316 |
chop $$strCSV; |
317 |
$$strCSV .= chr(10); |
318 |
# Populate rows with the data from mysql |
319 |
$query = 'SELECT * FROM ' . $table . ' WHERE frameworkcode=?'; |
320 |
$sth = $dbh->prepare($query); |
321 |
$sth->execute($frameworkcode); |
322 |
my $data; |
323 |
while (my $hashRef = $sth->fetchrow_hashref) { |
324 |
for (@fields) { |
325 |
$$strCSV .= '"' . $hashRef->{$_} . '",'; |
326 |
} |
327 |
chop $$strCSV; |
328 |
$$strCSV .= chr(10); |
329 |
} |
330 |
$$strCSV .= chr(10); |
331 |
for (@fields) { |
332 |
# Separator for change of table |
333 |
$$strCSV .= '"#-#",'; |
334 |
} |
335 |
chop $$strCSV; |
336 |
$$strCSV .= chr(10); |
337 |
$$strCSV .= chr(10); |
338 |
}; |
339 |
if ($@) { |
340 |
$debug and warn "Error _export_table_csv $@\n"; |
341 |
return 0; |
342 |
} |
343 |
return 1; |
344 |
}#_export_table_csv |
345 |
|
346 |
|
347 |
# Export the mysql table to an ods file |
348 |
sub _export_table_ods |
349 |
{ |
350 |
my ($table, $dbh, $dom, $root, $frameworkcode) = @_; |
351 |
|
352 |
eval { |
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); |
359 |
|
360 |
my $elementCell; |
361 |
my $elementData; |
362 |
# First row with the name of the columns |
363 |
my $query = 'SHOW COLUMNS FROM ' . $table; |
364 |
my $sth = $dbh->prepare($query); |
365 |
$sth->execute(); |
366 |
my @fields = (); |
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'}; |
376 |
} |
377 |
# Populate rows with the data from mysql |
378 |
$query = 'SELECT * FROM ' . $table . ' WHERE frameworkcode=?'; |
379 |
$sth = $dbh->prepare($query); |
380 |
$sth->execute($frameworkcode); |
381 |
my $data; |
382 |
while (my $hashRef = $sth->fetchrow_hashref) { |
383 |
$elementRow = $dom->createElement('table:table-row'); |
384 |
$elementTable->appendChild($elementRow); |
385 |
for (@fields) { |
386 |
$data = $hashRef->{$_->{name}}; |
387 |
if ($_->{type} eq 'float' && !defined($data)) { |
388 |
$data = '0'; |
389 |
} elsif ($_->{type} eq 'string' && (!$data && $data ne '0')) { |
390 |
$data = '#'; |
391 |
} |
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); |
400 |
} |
401 |
} |
402 |
}; |
403 |
if ($@) { |
404 |
$debug and warn "Error _export_table_ods $@\n"; |
405 |
return 0; |
406 |
} |
407 |
return 1; |
408 |
}#_export_table_ods |
409 |
|
410 |
|
411 |
# Export the mysql table to an excel-xml (openoffice/libreoffice compatible) file |
412 |
sub _export_table_excel |
413 |
{ |
414 |
my ($table, $dbh, $dom, $root, $frameworkcode) = @_; |
415 |
|
416 |
eval { |
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); |
424 |
|
425 |
# First row with the name of the columns |
426 |
my $elementCell; |
427 |
my $elementData; |
428 |
my $query = 'SHOW COLUMNS FROM ' . $table; |
429 |
my $sth = $dbh->prepare($query); |
430 |
$sth->execute(); |
431 |
my @fields = (); |
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'}; |
441 |
} |
442 |
# Populate rows with the data from mysql |
443 |
$query = 'SELECT * FROM ' . $table . ' WHERE frameworkcode=?'; |
444 |
$sth = $dbh->prepare($query); |
445 |
$sth->execute($frameworkcode); |
446 |
my $data; |
447 |
while (my $hashRef = $sth->fetchrow_hashref) { |
448 |
$elementRow = $dom->createElement('ss:Row'); |
449 |
$elementTable->appendChild($elementRow); |
450 |
for (@fields) { |
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)) { |
458 |
$data = '0'; |
459 |
} elsif ($_->{type} eq 'String' && (!$data && $data ne '0')) { |
460 |
$data = '#'; |
461 |
} |
462 |
$elementData->appendTextNode(($_->{type} eq 'String')?_parseContent2Xml($data):$data); |
463 |
} |
464 |
} |
465 |
}; |
466 |
if ($@) { |
467 |
$debug and warn "Error _export_table_excel $@\n"; |
468 |
return 0; |
469 |
} |
470 |
return 1; |
471 |
}#_export_table_excel |
472 |
|
473 |
|
474 |
|
475 |
|
476 |
|
477 |
|
478 |
|
479 |
# Format chars problematics to a correct format for xml. |
480 |
sub _parseContent2Xml |
481 |
{ |
482 |
my $content = shift; |
483 |
|
484 |
$content =~ s/\&(?![a-zA-Z#0-9]{1,4};)/&/g; |
485 |
$content =~ s/</</g; |
486 |
$content =~ s/>/>/g; |
487 |
return $content; |
488 |
}#_parseContent2Xml |
489 |
|
490 |
|
491 |
# Get the tmp directory on the system |
492 |
sub _getTmp |
493 |
{ |
494 |
my $tmp = '/tmp'; |
495 |
if ($ENV{'TMP'} && -d $ENV{'TMP'}) { |
496 |
$tmp = $ENV{'TMP'}; |
497 |
} elsif ($ENV{'TMPDIR'} && -d $ENV{'TMPDIR'}) { |
498 |
$tmp = $ENV{'TMPDIR'}; |
499 |
} elsif ($ENV{'TEMP'} && -d $ENV{'TEMP'}) { |
500 |
$tmp = $ENV{'TEMP'}; |
501 |
} |
502 |
return $tmp; |
503 |
}#_getTmp |
504 |
|
505 |
|
506 |
# Create our tempdir directory for the ods process |
507 |
sub _createTmpDir |
508 |
{ |
509 |
my $tmp = shift; |
510 |
|
511 |
my $tempdir = (-d $tmp)?$tmp . '/':'./'; |
512 |
$tempdir .= 'tmp_ods_' . Digest::MD5::md5_hex(Digest::MD5::md5_hex(time().{}.rand().{}.$$)); |
513 |
eval { |
514 |
mkdir $tempdir; |
515 |
}; |
516 |
if ($@) { |
517 |
return undef; |
518 |
} else { |
519 |
return $tempdir; |
520 |
} |
521 |
}#_createTmpDir |
522 |
|
523 |
=head2 createODS |
524 |
|
525 |
Creates a temporary directory to create the ods file and read it to store its content in a string. |
526 |
|
527 |
return : |
528 |
success |
529 |
|
530 |
=cut |
531 |
|
532 |
sub createODS |
533 |
{ |
534 |
my ($strContent, $lang, $strODSRef) = @_; |
535 |
|
536 |
my $tmp = _getTmp(); |
537 |
my $tempModule = 1; |
538 |
my $tempdir; |
539 |
eval { |
540 |
require File::Temp; |
541 |
import File::Temp qw/ tempfile tempdir /; |
542 |
$tempdir = tempdir ( 'tmp_ods_' . $$ . '_XXXXXXXX', DIR => (-d $tmp)?$tmp:'.', CLEANUP => 1); |
543 |
}; |
544 |
if ($@) { |
545 |
$tempModule = 0; |
546 |
$tempdir = _createTmpDir($tmp); |
547 |
} |
548 |
if ($tempdir) { |
549 |
# populate tempdir directory with the ods elements |
550 |
eval { |
551 |
if (open(OUT, "> $tempdir/content.xml")) { |
552 |
print OUT $strContent; |
553 |
close(OUT); |
554 |
} |
555 |
if (open(OUT, "> $tempdir/mimetype")) { |
556 |
print OUT 'application/vnd.oasis.opendocument.spreadsheet'; |
557 |
close(OUT); |
558 |
} |
559 |
if (open(OUT, "> $tempdir/meta.xml")) { |
560 |
print OUT _getMeta($lang); |
561 |
close(OUT); |
562 |
} |
563 |
if (open(OUT, "> $tempdir/styles.xml")) { |
564 |
print OUT ODS_STYLES_STR; |
565 |
close(OUT); |
566 |
} |
567 |
if (open(OUT, "> $tempdir/settings.xml")) { |
568 |
print OUT ODS_SETTINGS_STR; |
569 |
close(OUT); |
570 |
} |
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; |
583 |
close(OUT); |
584 |
} |
585 |
}; |
586 |
if ($@) { |
587 |
$debug and warn "Error createODS $@\n"; |
588 |
} else { |
589 |
# create ods file from tempdir directory |
590 |
eval { |
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'); |
596 |
}; |
597 |
if ($@) { |
598 |
system("cd $tempdir && zip -r new.ods ./"); |
599 |
} |
600 |
my $ok = 0; |
601 |
# read ods file and return as a string |
602 |
if (-f "$tempdir/new.ods") { |
603 |
if (open (MYFILE, "$tempdir/new.ods")) { |
604 |
binmode MYFILE; |
605 |
my $buffer; |
606 |
while (read (MYFILE, $buffer, 65536)) { |
607 |
$$strODSRef .= $buffer; |
608 |
} |
609 |
close(MYFILE); |
610 |
$ok = 1; |
611 |
} |
612 |
} |
613 |
# delete tempdir directory |
614 |
if (!$tempModule && $tempdir) { |
615 |
eval { |
616 |
require File::Path; |
617 |
import File::Temp qw/ rmtree /; |
618 |
rmtree($tempdir); |
619 |
}; |
620 |
if ($@) { |
621 |
system("rm -rf $tempdir"); |
622 |
} |
623 |
} |
624 |
return 1 if ($ok); |
625 |
} |
626 |
} |
627 |
return 0; |
628 |
}#createODS |
629 |
|
630 |
|
631 |
# return Meta content for ods file |
632 |
sub _getMeta |
633 |
{ |
634 |
my $lang = shift; |
635 |
|
636 |
my $myDate = strftime ("%Y-%m-%dT%H:%M:%S", localtime(time())); |
637 |
my $meta = '<?xml version="1.0" encoding="UTF-8"?> |
638 |
<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"> |
639 |
<office:meta> |
640 |
<meta:generator>ods-php</meta:generator> |
641 |
<meta:creation-date>' . $myDate . '</meta:creation-date> |
642 |
<dc:date>' . $myDate . '</dc:date> |
643 |
<dc:language>' . $lang . '</dc:language> |
644 |
<meta:editing-cycles>2</meta:editing-cycles> |
645 |
<meta:editing-duration>PT15S</meta:editing-duration> |
646 |
<meta:user-defined meta:name="Info 1"/> |
647 |
<meta:user-defined meta:name="Info 2"/> |
648 |
<meta:user-defined meta:name="Info 3"/> |
649 |
<meta:user-defined meta:name="Info 4"/> |
650 |
</office:meta> |
651 |
</office:document-meta>'; |
652 |
return $meta; |
653 |
}#_getMeta |
654 |
|
655 |
|
656 |
=head2 ImportFramework |
657 |
|
658 |
Import all the information of a Framework from a excel-xml/ods file. |
659 |
|
660 |
return : |
661 |
success |
662 |
|
663 |
=cut |
664 |
|
665 |
sub ImportFramework |
666 |
{ |
667 |
my ($filename, $frameworkcode, $deleteFilename) = @_; |
668 |
|
669 |
my $tempdir; |
670 |
my $ok = -1; |
671 |
my $dbh = C4::Context->dbh; |
672 |
if (-r $filename && $dbh) { |
673 |
my $extension = ''; |
674 |
if ($filename =~ /\.(csv|ods|xml|sql)$/i) { |
675 |
$extension = lc($1); |
676 |
} else { |
677 |
unlink ($filename) if ($deleteFilename); # remove temporary file |
678 |
return -1; |
679 |
} |
680 |
if ($extension eq 'ods') { |
681 |
($tempdir, $filename) = _openODS($filename, $deleteFilename); |
682 |
} |
683 |
if ($filename) { |
684 |
my $dom; |
685 |
eval { |
686 |
if ($extension eq 'ods' || $extension eq 'xml') { |
687 |
# They have xml structure, so read it on a dom object |
688 |
my $parser = XML::LibXML->new(); |
689 |
$dom = $parser->parse_file($filename); |
690 |
if ($dom) { |
691 |
my $root = $dom->documentElement(); |
692 |
} |
693 |
} else { |
694 |
# They are text files, so open it to read |
695 |
open($dom, '<', $filename); |
696 |
} |
697 |
if ($dom) { |
698 |
# For sql we just execute the line |
699 |
if ($extension eq 'sql') { |
700 |
while (<$dom>) { |
701 |
$dbh->do($_); |
702 |
} |
703 |
$ok = 0; |
704 |
} else { |
705 |
# Process both tables |
706 |
my $numDeleted = 0; |
707 |
my $numDeletedAux = 0; |
708 |
if (($numDeletedAux = _import_table($dbh, 'marc_tag_structure', $frameworkcode, $dom, ['frameworkcode', 'tagfield'], $extension)) >= 0) { |
709 |
$numDeleted += $numDeletedAux if ($numDeletedAux > 0); |
710 |
if (($numDeletedAux = _import_table($dbh, 'marc_subfield_structure', $frameworkcode, $dom, ['frameworkcode', 'tagfield', 'tagsubfield'], $extension)) >= 0) { |
711 |
$numDeleted += $numDeletedAux if ($numDeletedAux > 0); |
712 |
$ok = ($numDeleted > 0)?$numDeleted:0; |
713 |
} |
714 |
} |
715 |
} |
716 |
} else { |
717 |
$debug and warn "Error ImportFramework couldn't create dom\n"; |
718 |
} |
719 |
}; |
720 |
if ($@) { |
721 |
$debug and warn "Error ImportFramework $@\n"; |
722 |
} else { |
723 |
if ($extension eq 'sql' || $extension eq 'csv') { |
724 |
close($dom) if ($dom); |
725 |
} |
726 |
} |
727 |
} |
728 |
unlink ($filename) if ($deleteFilename); # remove temporary file |
729 |
} else { |
730 |
$debug and warn "Error ImportFramework no conex to database or not readeable $filename\n"; |
731 |
} |
732 |
if ($deleteFilename && $tempdir && -d $tempdir && -w $tempdir) { |
733 |
eval { |
734 |
require File::Path; |
735 |
import File::Temp qw/ rmtree /; |
736 |
rmtree($tempdir); |
737 |
}; |
738 |
if ($@) { |
739 |
system("rm -rf $tempdir"); |
740 |
} |
741 |
} |
742 |
return $ok; |
743 |
}#ImportFramework |
744 |
|
745 |
|
746 |
# Open (uncompress) ods file and return the content.xml file |
747 |
sub _openODS |
748 |
{ |
749 |
my ($filename, $deleteFilename) = @_; |
750 |
|
751 |
my $tmp = _getTmp(); |
752 |
my $tempModule = 1; |
753 |
my $tempdir; |
754 |
eval { |
755 |
require File::Temp; |
756 |
import File::Temp qw/ tempfile tempdir /; |
757 |
$tempdir = tempdir ( 'tmp_ods_' . $$ . '_XXXXXXXX', DIR => (-d $tmp)?$tmp:'.', CLEANUP => 0); |
758 |
}; |
759 |
if ($@) { |
760 |
$tempModule = 0; |
761 |
$tempdir = _createTmpDir($tmp); |
762 |
} |
763 |
if ($tempdir) { |
764 |
eval { |
765 |
require Archive::Zip; |
766 |
import Archive::Zip qw( :ERROR_CODES :CONSTANTS ); |
767 |
my $zip = Archive::Zip->new(); |
768 |
$zip->extractTree( $filename, $tempdir ); |
769 |
}; |
770 |
if ($@) { |
771 |
system("unzip $filename -d $tempdir"); |
772 |
} |
773 |
if (-f "$tempdir/content.xml") { |
774 |
unlink ($filename) if ($deleteFilename); |
775 |
return ($tempdir, "$tempdir/content.xml"); |
776 |
} |
777 |
} |
778 |
unlink ($filename) if ($deleteFilename); |
779 |
return ($tempdir, undef); |
780 |
}#_openODS |
781 |
|
782 |
|
783 |
|
784 |
# Check the table and columns corresponds with worksheet |
785 |
sub _check_validity_worksheet |
786 |
{ |
787 |
my ($dbh, $table, $nodeFields, $fieldsA, $format) = @_; |
788 |
|
789 |
my $ret = 0; |
790 |
eval { |
791 |
my $query = 'DESCRIBE ' . $table; |
792 |
my $sth = $dbh->prepare($query); |
793 |
$sth->execute(); |
794 |
$sth->finish; |
795 |
$query = 'SHOW COLUMNS FROM ' . $table; |
796 |
$sth = $dbh->prepare($query); |
797 |
$sth->execute(); |
798 |
my $fields = {}; |
799 |
while (my $hashRef = $sth->fetchrow_hashref) { |
800 |
$fields->{$hashRef->{Field}} = $hashRef->{Field}; |
801 |
} |
802 |
my @fields; |
803 |
my $fieldsR; |
804 |
if ($fieldsA) { |
805 |
$fieldsR = $fieldsA; |
806 |
} else { |
807 |
$fieldsR = \@fields; |
808 |
_getFields($nodeFields, $fieldsR, $format); |
809 |
} |
810 |
$ret = 1; |
811 |
for (@$fieldsR) { |
812 |
unless (exists($fields->{$_})) { |
813 |
$ret = 0; |
814 |
last; |
815 |
} |
816 |
} |
817 |
}; |
818 |
return $ret; |
819 |
}#_check_validity_worksheet |
820 |
|
821 |
|
822 |
# Import the data from an excel-xml/ods to mysql tables. |
823 |
sub _import_table |
824 |
{ |
825 |
my ($dbh, $table, $frameworkcode, $dom, $PKArray, $format) = @_; |
826 |
my %fields2Delete; |
827 |
my $query; |
828 |
my @fields; |
829 |
# Create hash with all elements defined by primary key to know which ones to delete after parsing the spreadsheet |
830 |
eval { |
831 |
@fields = @$PKArray; |
832 |
shift @fields; |
833 |
$query = 'SELECT ' . join(',', @fields) . ' FROM ' . $table . ' WHERE frameworkcode=?'; |
834 |
my $sth = $dbh->prepare($query); |
835 |
$sth->execute($frameworkcode); |
836 |
my $field; |
837 |
while (my $hashRef = $sth->fetchrow_hashref) { |
838 |
$field = ''; |
839 |
map { $field .= $hashRef->{$_} . '_'; } @fields; |
840 |
chop $field; |
841 |
$fields2Delete{$field} = 1; |
842 |
} |
843 |
$sth->finish; |
844 |
}; |
845 |
my $ok = 0; |
846 |
if ($format eq 'csv') { |
847 |
my @fieldsName = (); |
848 |
eval { |
849 |
my $query = 'SHOW COLUMNS FROM ' . $table; |
850 |
my $sth = $dbh->prepare($query); |
851 |
$sth->execute(); |
852 |
while (my $hashRef = $sth->fetchrow_hashref) { |
853 |
push @fieldsName, $hashRef->{Field}; |
854 |
} |
855 |
}; |
856 |
$ok = _import_table_csv($dbh, $table, $frameworkcode, $dom, $PKArray, \%fields2Delete, \@fieldsName); |
857 |
} elsif ($format eq 'ods') { |
858 |
$ok = _import_table_ods($dbh, $table, $frameworkcode, $dom, $PKArray, \%fields2Delete); |
859 |
} else { |
860 |
$ok = _import_table_excel($dbh, $table, $frameworkcode, $dom, $PKArray, \%fields2Delete); |
861 |
} |
862 |
if ($ok) { |
863 |
if (($ok = scalar(keys %fields2Delete)) > 0) { |
864 |
$query = 'DELETE FROM ' . $table . ' WHERE '; |
865 |
map {$query .= $_ . '=? AND ';} @$PKArray; |
866 |
$query = substr($query, 0, -4); |
867 |
my $sth = $dbh->prepare($query); |
868 |
for (keys %fields2Delete) { |
869 |
eval { |
870 |
$sth->execute(($frameworkcode, split('_', $_))); |
871 |
}; |
872 |
} |
873 |
} |
874 |
} else { |
875 |
$ok = -1; |
876 |
} |
877 |
return $ok; |
878 |
}#_import_table |
879 |
|
880 |
|
881 |
# Insert/Update the row from the spreadsheet in the database |
882 |
sub _processRow_DB |
883 |
{ |
884 |
my ($dbh, $db_scheme, $table, $fields, $dataStr, $updateStr, $dataFields, $dataFieldsHash, $PKArray, $fieldsPK, $fields2Delete) = @_; |
885 |
|
886 |
my $ok = 0; |
887 |
my $query; |
888 |
if ($db_scheme eq 'mysql') { |
889 |
$query = 'INSERT INTO ' . $table . ' (' . $fields . ') VALUES (' . $dataStr . ') ON DUPLICATE KEY UPDATE ' . $updateStr; |
890 |
} else { |
891 |
$query = 'INSERT INTO ' . $table . ' (' . $fields . ') VALUES (' . $dataStr . ')'; |
892 |
} |
893 |
eval { |
894 |
my $sth = $dbh->prepare($query); |
895 |
if ($db_scheme eq 'mysql') { |
896 |
$sth->execute((@$dataFields, @$dataFields)); |
897 |
} else { |
898 |
$sth->execute(@$dataFields); |
899 |
} |
900 |
}; |
901 |
if ($@) { |
902 |
unless ($db_scheme eq 'mysql') { |
903 |
$query = 'UPDATE ' . $table . ' SET ' . $updateStr . ' WHERE '; |
904 |
map {$query .= $_ . '=? AND ';} @$PKArray; |
905 |
$query = substr($query, 0, -4); |
906 |
eval { |
907 |
my $sth2 = $dbh->prepare($query); |
908 |
my @dataPK = (); |
909 |
map {push @dataPK, $dataFieldsHash->{$_};} @$PKArray; |
910 |
$sth2->execute((@$dataFields, @dataPK)); |
911 |
}; |
912 |
$ok = 1 unless ($@); |
913 |
} |
914 |
$debug and warn "Error _processRows_Table $@\n"; |
915 |
} else { |
916 |
$ok = 1; |
917 |
} |
918 |
if ($ok) { |
919 |
my $field = ''; |
920 |
map { $field .= $dataFieldsHash->{$_} . '_'; } @$fieldsPK; |
921 |
chop $field; |
922 |
delete $fields2Delete->{$field} if (exists($fields2Delete->{$field})); |
923 |
} |
924 |
return $ok; |
925 |
}#_processRow_DB |
926 |
|
927 |
|
928 |
# Process the rows of a worksheet and insert/update them in a mysql table. |
929 |
sub _processRows_Table |
930 |
{ |
931 |
my ($dbh, $frameworkcode, $nodeR, $table, $PKArray, $format, $fields2Delete) = @_; |
932 |
|
933 |
my $query; |
934 |
my @fields = (); |
935 |
my $fields = ''; |
936 |
my $dataStr = ''; |
937 |
my $updateStr = ''; |
938 |
my $j = 0; |
939 |
my $db_scheme = C4::Context->config("db_scheme"); |
940 |
my $ok = 0; |
941 |
my @fieldsPK = @$PKArray; |
942 |
shift @fieldsPK; |
943 |
while ($nodeR) { |
944 |
if ($nodeR->nodeType == 1 && (($format && $format eq 'ods' && $nodeR->nodeName =~ /(?:table:)?table-row/) || ($nodeR->nodeName =~ /(?:ss:)?Row/)) && $nodeR->hasChildNodes()) { |
945 |
if ($j == 0) { |
946 |
# Get name columns |
947 |
_getFields($nodeR, \@fields, $format); |
948 |
return 0 unless _check_validity_worksheet($dbh, $table, $nodeR, \@fields, $format); |
949 |
$fields = join(',', @fields); |
950 |
$dataStr = ''; |
951 |
map { $dataStr .= '?,';} @fields; |
952 |
chop($dataStr) if ($dataStr); |
953 |
$updateStr = ''; |
954 |
map { $updateStr .= $_ . '=?,';} @fields; |
955 |
chop($updateStr) if ($updateStr); |
956 |
} else { |
957 |
# Get data from row |
958 |
my ($dataFields, $dataFieldsR) = _getDataFields($frameworkcode, $nodeR, \@fields, $format); |
959 |
if (scalar(@fields) == scalar(@$dataFieldsR)) { |
960 |
$ok = _processRow_DB($dbh, $db_scheme, $table, $fields, $dataStr, $updateStr, $dataFieldsR, $dataFields, $PKArray, \@fieldsPK, $fields2Delete); |
961 |
} |
962 |
} |
963 |
$j++; |
964 |
} |
965 |
$nodeR = $nodeR->nextSibling; |
966 |
} |
967 |
return 1; |
968 |
}#_processRows_Table |
969 |
|
970 |
|
971 |
|
972 |
|
973 |
# Import worksheet from the csv file to the mysql table |
974 |
sub _import_table_csv |
975 |
{ |
976 |
my ($dbh, $table, $frameworkcode, $dom, $PKArray, $fields2Delete, $fields) = @_; |
977 |
|
978 |
my $row = ''; |
979 |
my $numFields = @$fields; |
980 |
my $fieldsNameRead = 0; |
981 |
my @arrData; |
982 |
my ($fieldsStr, $dataStr, $updateStr); |
983 |
my $db_scheme = C4::Context->config("db_scheme"); |
984 |
my @fieldsPK = @$PKArray; |
985 |
shift @fieldsPK; |
986 |
my $ok = 0; |
987 |
my $numRow = 0; |
988 |
my $pos = 0; |
989 |
while (<$dom>) { |
990 |
$row = $_; |
991 |
if ($row =~ /(?:".*?",?)+/) { |
992 |
@arrData = split('","', $row); |
993 |
$arrData[0] = substr($arrData[0], 1) if ($arrData[0] =~ /^"/); |
994 |
chomp $arrData[$#arrData]; |
995 |
chop $arrData[$#arrData] if ($arrData[$#arrData] =~ /"$/); |
996 |
if (@arrData) { |
997 |
if ($arrData[0] eq '#-#' && $arrData[$#arrData] eq '#-#') { |
998 |
# Change of table with separators #-# |
999 |
return 1; |
1000 |
} elsif ($fieldsNameRead && $arrData[0] eq 'tagfield') { |
1001 |
# Change of table because we begin with field name with former field names read |
1002 |
seek($dom, $pos, 0); |
1003 |
return 1; |
1004 |
} |
1005 |
if (scalar(@$fields) == scalar(@arrData)) { |
1006 |
if (!$fieldsNameRead) { |
1007 |
# New table, we read the field names |
1008 |
$fieldsNameRead = 1; |
1009 |
for (my $i=0; $i < @arrData; $i++) { |
1010 |
if ($arrData[$i] ne $fields->[$i]) { |
1011 |
$fieldsNameRead = 0; |
1012 |
last; |
1013 |
} |
1014 |
} |
1015 |
if ($fieldsNameRead) { |
1016 |
$fieldsStr = join(',', @$fields); |
1017 |
$dataStr = ''; |
1018 |
map { $dataStr .= '?,';} @$fields; |
1019 |
chop($dataStr) if ($dataStr); |
1020 |
$updateStr = ''; |
1021 |
map { $updateStr .= $_ . '=?,';} @$fields; |
1022 |
chop($updateStr) if ($updateStr); |
1023 |
} |
1024 |
} else { |
1025 |
# Read data |
1026 |
my $j = 0; |
1027 |
my %dataFields = map { $fields->[$j++] => $_} @arrData; |
1028 |
$ok = _processRow_DB($dbh, $db_scheme, $table, $fieldsStr, $dataStr, $updateStr, \@arrData, \%dataFields, $PKArray, \@fieldsPK, $fields2Delete); |
1029 |
} |
1030 |
} |
1031 |
$pos = tell($dom); |
1032 |
} |
1033 |
@arrData = (); |
1034 |
} |
1035 |
$numRow++; |
1036 |
} |
1037 |
return $ok; |
1038 |
}#_import_table_csv |
1039 |
|
1040 |
|
1041 |
# Import worksheet from the ods content.xml file to the mysql table |
1042 |
sub _import_table_ods |
1043 |
{ |
1044 |
my ($dbh, $table, $frameworkcode, $dom, $PKArray, $fields2Delete) = @_; |
1045 |
|
1046 |
my $xc = XML::LibXML::XPathContext->new($dom); |
1047 |
$xc->registerNs('xmlns:office','urn:oasis:names:tc:opendocument:xmlns:office:1.0'); |
1048 |
$xc->registerNs('xmlns:table','urn:oasis:names:tc:opendocument:xmlns:table:1.0'); |
1049 |
$xc->registerNs('xmlns:text','urn:oasis:names:tc:opendocument:xmlns:text:1.0'); |
1050 |
my @nodes; |
1051 |
@nodes = $xc->findnodes('//table:table[@table:name="' . $table . '"]'); |
1052 |
if (@nodes == 1 && $nodes[0]->hasChildNodes()) { |
1053 |
my $nodeR = $nodes[0]->firstChild; |
1054 |
return _processRows_Table($dbh, $frameworkcode, $nodeR, $table, $PKArray, 'ods', $fields2Delete); |
1055 |
} else { |
1056 |
$debug and warn "Error _import_table_ods there's not worksheet for $table\n"; |
1057 |
} |
1058 |
return 0; |
1059 |
}#_import_table_ods |
1060 |
|
1061 |
|
1062 |
# Import worksheet from the excel-xml file to the mysql table |
1063 |
sub _import_table_excel |
1064 |
{ |
1065 |
my ($dbh, $table, $frameworkcode, $dom, $PKArray, $fields2Delete) = @_; |
1066 |
|
1067 |
my $xc = XML::LibXML::XPathContext->new($dom); |
1068 |
$xc->registerNs('xmlns','urn:schemas-microsoft-com:office:spreadsheet'); |
1069 |
$xc->registerNs('xmlns:ss','urn:schemas-microsoft-com:office:spreadsheet'); |
1070 |
$xc->registerNs('xmlns:x','urn:schemas-microsoft-com:office:excel'); |
1071 |
my @nodes; |
1072 |
@nodes = $xc->findnodes('//ss:Worksheet[@ss:Name="' . $table . '"]'); |
1073 |
if (@nodes > 0) { |
1074 |
for (my $i=0; $i < @nodes; $i++) { |
1075 |
my @nodesT = $nodes[$i]->getElementsByTagNameNS('urn:schemas-microsoft-com:office:spreadsheet', 'Table'); |
1076 |
if (@nodesT == 1 && $nodesT[0]->hasChildNodes()) { |
1077 |
my $nodeR = $nodesT[0]->firstChild; |
1078 |
return _processRows_Table($dbh, $frameworkcode, $nodeR, $table, $PKArray, undef, $fields2Delete); |
1079 |
} |
1080 |
} |
1081 |
} else { |
1082 |
$debug and warn "Error _import_table_excel there's not worksheet for $table\n"; |
1083 |
} |
1084 |
return 0; |
1085 |
}#_import_table_excel |
1086 |
|
1087 |
|
1088 |
# Get the data from a cell on a ods file through the value attribute or the text node |
1089 |
sub _getDataNodeODS |
1090 |
{ |
1091 |
my $node = shift; |
1092 |
|
1093 |
my $data; |
1094 |
my $repeated = 0; |
1095 |
if ($node->nodeType == 1 && $node->nodeName =~ /(?:table:)?table-cell/) { |
1096 |
if ($node->hasAttributeNS('urn:oasis:names:tc:opendocument:xmlns:office:1.0', 'value')) { |
1097 |
$data = $node->getAttributeNS('urn:oasis:names:tc:opendocument:xmlns:office:1.0', 'value'); |
1098 |
} elsif ($node->hasChildNodes()) { |
1099 |
my @nodes2 = $node->getElementsByTagNameNS('urn:oasis:names:tc:opendocument:xmlns:text:1.0', 'p'); |
1100 |
if (@nodes2 == 1 && $nodes2[0]->hasChildNodes()) { |
1101 |
$data = $nodes2[0]->firstChild->nodeValue; |
1102 |
} |
1103 |
} |
1104 |
if ($node->hasAttributeNS('urn:oasis:names:tc:opendocument:xmlns:table:1.0', 'number-columns-repeated')) { |
1105 |
$repeated = $node->getAttributeNS('urn:oasis:names:tc:opendocument:xmlns:table:1.0', 'number-columns-repeated'); |
1106 |
} |
1107 |
} |
1108 |
return ($data, $repeated); |
1109 |
}#_getDataNodeODS |
1110 |
|
1111 |
|
1112 |
# Get the data from a row of a spreadsheet |
1113 |
sub _getDataFields |
1114 |
{ |
1115 |
my ($frameworkcode, $node, $fields, $format) = @_; |
1116 |
|
1117 |
my $dataFields = {}; |
1118 |
my @dataFieldsA = (); |
1119 |
if ($node && $node->hasChildNodes()) { |
1120 |
my $node2 = $node->firstChild; |
1121 |
my ($data, $repeated); |
1122 |
my $i = 0; |
1123 |
my $ok = 0; |
1124 |
$repeated = 0; |
1125 |
while ($node2) { |
1126 |
if ($format && $format eq 'ods') { |
1127 |
($data, $repeated) = _getDataNodeODS($node2) if ($repeated <= 0); |
1128 |
$repeated--; |
1129 |
$ok = 1 if (defined($data)); |
1130 |
} else { |
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 |
$ok = 1; |
1136 |
} |
1137 |
} |
1138 |
} |
1139 |
if ($ok) { |
1140 |
$data = '' if ($data eq '#'); |
1141 |
$data = $frameworkcode if ($fields->[$i] eq 'frameworkcode'); |
1142 |
$dataFields->{$fields->[$i]} = $data; |
1143 |
push @dataFieldsA, $data; |
1144 |
$i++; |
1145 |
} |
1146 |
$ok = 0; |
1147 |
$node2 = $node2->nextSibling if ($repeated <= 0); |
1148 |
} |
1149 |
} |
1150 |
return ($dataFields, \@dataFieldsA); |
1151 |
}#_getDataFields |
1152 |
|
1153 |
|
1154 |
# Get the data from the first row to know the column names |
1155 |
sub _getFields |
1156 |
{ |
1157 |
my ($node, $fields, $format) = @_; |
1158 |
|
1159 |
if ($node && $node->hasChildNodes()) { |
1160 |
my $node2 = $node->firstChild; |
1161 |
my ($data, $repeated); |
1162 |
while ($node2) { |
1163 |
if ($format && $format eq 'ods') { |
1164 |
($data, $repeated) = _getDataNodeODS($node2); |
1165 |
push @$fields, $data if (defined($data)); |
1166 |
} else { |
1167 |
if ($node2->nodeType == 1 && $node2->nodeName =~ /(?:ss:)?Cell/) { |
1168 |
my @nodes3 = $node2->getElementsByTagNameNS('urn:schemas-microsoft-com:office:spreadsheet', 'Data'); |
1169 |
if (@nodes3 == 1 && $nodes3[0]->hasChildNodes()) { |
1170 |
$data = $nodes3[0]->firstChild->nodeValue; |
1171 |
push @$fields, $data; |
1172 |
} |
1173 |
} |
1174 |
} |
1175 |
$node2 = $node2->nextSibling; |
1176 |
} |
1177 |
} |
1178 |
}#_getFields |
1179 |
|
1180 |
|
1181 |
|
1182 |
|
1183 |
1; |
1184 |
__END__ |
1185 |
|
1186 |
=head1 AUTHOR |
1187 |
|
1188 |
Koha Development Team <http://koha-community.org/> |
1189 |
|
1190 |
=cut |
1191 |
|
1192 |
|