File Coverage

File:C4/ImportExportFramework.pm
Coverage:4.6%

linestmtbrancondsubtimecode
1package 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
2
2
2
2
2521
4
51
use strict;
21
2
2
2
2
9
4
163
use warnings;
22
2
2
2
2
10
2
48
use XML::LibXML;
23
2
2
2
2
497
3
53
use XML::LibXML::XPathContext;
24
2
2
2
2
9
2
214
use Digest::MD5 qw(md5_base64);
25
2
2
2
2
10
4
25
use POSIX qw(strftime);
26
27
2
2
2
2
123
2
21
use C4::Context;
28
2
2
2
2
5
1
1029
use C4::Debug;
29
30
31
2
2
2
2
20
6
300
use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
32
33BEGIN {
34
2
2
8
    $VERSION = 3.03; # set version for version checking
35
2
13
    require Exporter;
36
2
27
    @ISA = qw(Exporter);
37
2
133
    @EXPORT = qw(
38        &ExportFramework
39        &ImportFramework
40        &createODS
41    );
42}
43
44
45
2
31
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
2
2
2
29
3
';
77
78
79
2
13
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
2
2
2
267
4
</office:document-content>';
85
86
87
2
13
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
2
2
2
246
4
</office:document-styles>';
94
95
96
2
12
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
2
2
2
381
5
</office:settings></office:document-settings>';
150
151
152
2
14
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
2
2
2
276
4
</manifest:manifest>';
171
172
173 - 202
=head1 NAME

C4::ImportExportFramework - Import/Export Framework to Excel-xml/ODS Module Functions

=head1 SYNOPSIS

  use C4::ImportExportFramework;

=head1 DESCRIPTION

Module to Import/Export Framework to Excel-xml/ODS on intranet administration - MARC Frameworks section

Module to Import/Export Framework to Excel-xml/ODS on intranet administration - MARC Frameworks section
exporting the tables marc_tag_structure, marc_subfield_structure to excel-xml/ods or viceversa

Functions for handling import/export.


=head1 SUBROUTINES



=head2 ExportFramework

Export all the information of a Framework to an excel "xml" file or OpenDocument SpreadSheet "ods" file.

return :
succes

=cut
203
204sub ExportFramework
205{
206
0
0
    my ($frameworkcode, $xmlStrRef, $mode) = @_;
207
208
0
    my $dbh = C4::Context->dbh;
209
0
0
    if ($dbh) {
210
0
        my $dom;
211
0
        my $root;
212
0
        my $elementSS;
213
0
0
0
        if ($mode eq 'ods' || $mode eq 'excel') {
214
0
            eval {
215
0
                my $parser = XML::LibXML->new();
216
0
0
0
                $dom = $parser->parse_string(($mode && $mode eq 'ods')?ODSSTR:XMLSTR);
217
0
0
                if ($dom) {
218
0
                    $root = $dom->documentElement();
219
0
0
0
                    if ($mode && $mode eq 'ods') {
220
0
                        my $elementBody = $dom->createElement('office:body');
221
0
                        $root->appendChild($elementBody);
222
0
                        $elementSS = $dom->createElement('office:spreadsheet');
223
0
                        $elementBody->appendChild($elementSS);
224                    }
225                }
226            };
227
0
0
            if ($@) {
228
0
0
                $debug and warn "Error ExportFramework $@\n";
229
0
                return 0;
230            }
231        }
232
233
0
0
0
0
0
        if (_export_table('marc_tag_structure', $dbh, ($mode eq 'csv' || $mode eq 'sql')?$xmlStrRef:$dom, ($mode eq 'ods')?$elementSS:$root, $frameworkcode, $mode)) {
234
0
0
0
0
0
            if (_export_table('marc_subfield_structure', $dbh, ($mode eq 'csv' || $mode eq 'sql')?$xmlStrRef:$dom, ($mode eq 'ods')?$elementSS:$root, $frameworkcode, $mode)) {
235
0
0
0
                $$xmlStrRef = $dom->toString(1) if ($mode eq 'ods' || $mode eq 'excel');
236
0
                return 1;
237            }
238        }
239    }
240
0
    return 0;
241}#ExportFramework
242
243
244
245
246# Export all the data from a mysql table to an spreadsheet.
247sub _export_table
248{
249
0
0
    my ($table, $dbh, $dom, $root, $frameworkcode, $mode) = @_;
250
0
0
0
0
    if ($mode eq 'csv') {
251
0
        _export_table_csv($table, $dbh, $dom, $root, $frameworkcode);
252    } elsif ($mode eq 'sql') {
253
0
        _export_table_sql($table, $dbh, $dom, $root, $frameworkcode);
254    } elsif ($mode eq 'ods') {
255
0
        _export_table_ods($table, $dbh, $dom, $root, $frameworkcode);
256    } else {
257
0
        _export_table_excel($table, $dbh, $dom, $root, $frameworkcode);
258    }
259}
260
261
262# Export the mysql table to an sql file
263sub _export_table_sql
264{
265
0
0
    my ($table, $dbh, $strSQL, $root, $frameworkcode) = @_;
266
267
0
    eval {
268        # First row with the name of the columns
269
0
        my $query = 'SHOW COLUMNS FROM ' . $table;
270
0
        my $sth = $dbh->prepare($query);
271
0
        $sth->execute();
272
0
        my @fields = ();
273
0
        while (my $hashRef = $sth->fetchrow_hashref) {
274
0
            push @fields, $hashRef->{Field};
275        }
276
0
        my $fields = join(',', @fields);
277
0
        $$strSQL .= 'DELETE FROM ' . $table . ' WHERE frameworkcode=' . $dbh->quote($frameworkcode) . ';';
278
0
        $$strSQL .= chr(10);
279        # Populate rows with the data from mysql
280
0
        $query = 'SELECT * FROM ' . $table . ' WHERE frameworkcode=?';
281
0
        $sth = $dbh->prepare($query);
282
0
        $sth->execute($frameworkcode);
283
0
        while (my $hashRef = $sth->fetchrow_hashref) {
284
0
            $$strSQL .= 'INSERT INTO ' . $table . ' (' . $fields . ') VALUES (';
285
0
            for (@fields) {
286
0
                $$strSQL .= $dbh->quote($hashRef->{$_}) . ',';
287            }
288
0
            chop $$strSQL;
289
0
            $$strSQL .= ');' . chr(10);
290        }
291
0
        $$strSQL .= chr(10) . chr(10);
292    };
293
0
0
    if ($@) {
294
0
0
        $debug and warn "Error _export_table_sql $@\n";
295
0
        return 0;
296    }
297
0
    return 1;
298}#_export_table_sql
299
300
301# Export the mysql table to an csv file
302sub _export_table_csv
303{
304
0
0
    my ($table, $dbh, $strCSV, $root, $frameworkcode) = @_;
305
306
0
    eval {
307        # First row with the name of the columns
308
0
        my $query = 'SHOW COLUMNS FROM ' . $table;
309
0
        my $sth = $dbh->prepare($query);
310
0
        $sth->execute();
311
0
        my @fields = ();
312
0
        while (my $hashRef = $sth->fetchrow_hashref) {
313
0
            $$strCSV .= '"' . $hashRef->{Field} . '",';
314
0
            push @fields, $hashRef->{Field};
315        }
316
0
        chop $$strCSV;
317
0
        $$strCSV .= chr(10);
318        # Populate rows with the data from mysql
319
0
        $query = 'SELECT * FROM ' . $table . ' WHERE frameworkcode=?';
320
0
        $sth = $dbh->prepare($query);
321
0
        $sth->execute($frameworkcode);
322
0
        my $data;
323
0
        while (my $hashRef = $sth->fetchrow_hashref) {
324
0
            for (@fields) {
325
0
                $hashRef->{$_} =~ s/[\r\n]//g;
326
0
                $$strCSV .= '"' . $hashRef->{$_} . '",';
327            }
328
0
            chop $$strCSV;
329
0
            $$strCSV .= chr(10);
330        }
331
0
        $$strCSV .= chr(10);
332
0
        for (@fields) {
333            # Separator for change of table
334
0
            $$strCSV .= '"#-#",';
335        }
336
0
        chop $$strCSV;
337
0
        $$strCSV .= chr(10);
338
0
        $$strCSV .= chr(10);
339    };
340
0
0
    if ($@) {
341
0
0
        $debug and warn "Error _export_table_csv $@\n";
342
0
        return 0;
343    }
344
0
    return 1;
345}#_export_table_csv
346
347
348# Export the mysql table to an ods file
349sub _export_table_ods
350{
351
0
0
    my ($table, $dbh, $dom, $root, $frameworkcode) = @_;
352
353
0
    eval {
354
0
        my $elementTable = $dom->createElement('table:table');
355
0
        $elementTable->setAttribute('table:name', $table);
356
0
        $elementTable->setAttribute('table:print', 'false');
357
0
        $root->appendChild($elementTable);
358
0
        my $elementRow = $dom->createElement('table:table-row');
359
0
        $elementTable->appendChild($elementRow);
360
361
0
        my $elementCell;
362
0
        my $elementData;
363        # First row with the name of the columns
364
0
        my $query = 'SHOW COLUMNS FROM ' . $table;
365
0
        my $sth = $dbh->prepare($query);
366
0
        $sth->execute();
367
0
        my @fields = ();
368
0
        while (my $hashRef = $sth->fetchrow_hashref) {
369
0
            $elementCell = $dom->createElement('table:table-cell');
370
0
            $elementCell->setAttribute('office:value-type', 'string');
371
0
            $elementCell->setAttribute('office:value', $hashRef->{Field});
372
0
            $elementRow->appendChild($elementCell);
373
0
            $elementData = $dom->createElement('text:p');
374
0
            $elementCell->appendChild($elementData);
375
0
            $elementData->appendTextNode($hashRef->{Field});
376
0
0
            push @fields, {name => $hashRef->{Field}, type => ($hashRef->{Type} =~ /int/i)?'float':'string'};
377        }
378        # Populate rows with the data from mysql
379
0
        $query = 'SELECT * FROM ' . $table . ' WHERE frameworkcode=?';
380
0
        $sth = $dbh->prepare($query);
381
0
        $sth->execute($frameworkcode);
382
0
        my $data;
383
0
        while (my $hashRef = $sth->fetchrow_hashref) {
384
0
            $elementRow = $dom->createElement('table:table-row');
385
0
            $elementTable->appendChild($elementRow);
386
0
            for (@fields) {
387
0
                $data = $hashRef->{$_->{name}};
388
0
0
0
0
0
0
                if ($_->{type} eq 'float' && !defined($data)) {
389
0
                    $data = '0';
390                } elsif ($_->{type} eq 'string' && (!$data && $data ne '0')) {
391
0
                    $data = '#';
392                }
393
0
0
                $data = _parseContent2Xml($data) if ($_->{type} eq 'string');
394
0
                $elementCell = $dom->createElement('table:table-cell');
395
0
                $elementCell->setAttribute('office:value-type', $_->{type});
396
0
                $elementCell->setAttribute('office:value', $data);
397
0
                $elementRow->appendChild($elementCell);
398
0
                $elementData = $dom->createElement('text:p');
399
0
                $elementCell->appendChild($elementData);
400
0
                $elementData->appendTextNode($data);
401            }
402        }
403    };
404
0
0
    if ($@) {
405
0
0
        $debug and warn "Error _export_table_ods $@\n";
406
0
        return 0;
407    }
408
0
    return 1;
409}#_export_table_ods
410
411
412# Export the mysql table to an excel-xml (openoffice/libreoffice compatible) file
413sub _export_table_excel
414{
415
0
0
    my ($table, $dbh, $dom, $root, $frameworkcode) = @_;
416
417
0
    eval {
418
0
        my $elementWS = $dom->createElement('Worksheet');
419
0
        $elementWS->setAttribute('ss:Name', $table);
420
0
        $root->appendChild($elementWS);
421
0
        my $elementTable = $dom->createElement('ss:Table');
422
0
        $elementWS->appendChild($elementTable);
423
0
        my $elementRow = $dom->createElement('ss:Row');
424
0
        $elementTable->appendChild($elementRow);
425
426        # First row with the name of the columns
427
0
        my $elementCell;
428
0
        my $elementData;
429
0
        my $query = 'SHOW COLUMNS FROM ' . $table;
430
0
        my $sth = $dbh->prepare($query);
431
0
        $sth->execute();
432
0
        my @fields = ();
433
0
        while (my $hashRef = $sth->fetchrow_hashref) {
434
0
            $elementCell = $dom->createElement('ss:Cell');
435
0
            $elementCell->setAttribute('ss:StyleID', 's27');
436
0
            $elementRow->appendChild($elementCell);
437
0
            $elementData = $dom->createElement('ss:Data');
438
0
            $elementData->setAttribute('ss:Type', 'String');
439
0
            $elementCell->appendChild($elementData);
440
0
            $elementData->appendTextNode($hashRef->{Field});
441
0
0
            push @fields, {name => $hashRef->{Field}, type => ($hashRef->{Type} =~ /int/i)?'Number':'String'};
442        }
443        # Populate rows with the data from mysql
444
0
        $query = 'SELECT * FROM ' . $table . ' WHERE frameworkcode=?';
445
0
        $sth = $dbh->prepare($query);
446
0
        $sth->execute($frameworkcode);
447
0
        my $data;
448
0
        while (my $hashRef = $sth->fetchrow_hashref) {
449
0
            $elementRow = $dom->createElement('ss:Row');
450
0
            $elementTable->appendChild($elementRow);
451
0
            for (@fields) {
452
0
                $elementCell = $dom->createElement('ss:Cell');
453
0
                $elementRow->appendChild($elementCell);
454
0
                $elementData = $dom->createElement('ss:Data');
455
0
                $elementData->setAttribute('ss:Type', $_->{type});
456
0
                $elementCell->appendChild($elementData);
457
0
                $data = $hashRef->{$_->{name}};
458
0
0
0
0
0
0
                if ($_->{type} eq 'Number' && !defined($data)) {
459
0
                    $data = '0';
460                } elsif ($_->{type} eq 'String' && (!$data && $data ne '0')) {
461
0
                    $data = '#';
462                }
463
0
0
                $elementData->appendTextNode(($_->{type} eq 'String')?_parseContent2Xml($data):$data);
464            }
465        }
466    };
467
0
0
    if ($@) {
468
0
0
        $debug and warn "Error _export_table_excel $@\n";
469
0
        return 0;
470    }
471
0
    return 1;
472}#_export_table_excel
473
474
475
476
477
478
479
480# Format chars problematics to a correct format for xml.
481sub _parseContent2Xml
482{
483
0
0
    my $content = shift;
484
485
0
    $content =~ s/\&(?![a-zA-Z#0-9]{1,4};)/&amp;/g;
486
0
    $content =~ s/</&lt;/g;
487
0
    $content =~ s/>/&gt;/g;
488
0
    return $content;
489}#_parseContent2Xml
490
491
492# Get the tmp directory on the system
493sub _getTmp
494{
495
0
0
    my $tmp = '/tmp';
496
0
0
0
0
0
0
0
    if ($ENV{'TMP'} && -d $ENV{'TMP'}) {
497
0
        $tmp = $ENV{'TMP'};
498    } elsif ($ENV{'TMPDIR'} && -d $ENV{'TMPDIR'}) {
499
0
        $tmp = $ENV{'TMPDIR'};
500    } elsif ($ENV{'TEMP'} && -d $ENV{'TEMP'}) {
501
0
        $tmp = $ENV{'TEMP'};
502    }
503
0
    return $tmp;
504}#_getTmp
505
506
507# Create our tempdir directory for the ods process
508sub _createTmpDir
509{
510
0
0
    my $tmp = shift;
511
512
0
0
    my $tempdir = (-d $tmp)?$tmp . '/':'./';
513
0
    $tempdir .= 'tmp_ods_' . Digest::MD5::md5_hex(Digest::MD5::md5_hex(time().{}.rand().{}.$$));
514
0
    eval {
515
0
        mkdir $tempdir;
516    };
517
0
0
    if ($@) {
518
0
        return undef;
519    } else {
520
0
        return $tempdir;
521    }
522}#_createTmpDir
523
524 - 531
=head2 createODS

Creates a temporary directory to create the ods file and read it to store its content in a string.

return :
success

=cut
532
533sub createODS
534{
535
0
0
    my ($strContent, $lang, $strODSRef) = @_;
536
537
0
    my $tmp = _getTmp();
538
0
    my $tempModule = 1;
539
0
    my $tempdir;
540
0
    eval {
541
0
        require File::Temp;
542
0
        import File::Temp qw/ tempfile tempdir /;
543
0
0
        $tempdir = tempdir ( 'tmp_ods_' . $$ . '_XXXXXXXX', DIR => (-d $tmp)?$tmp:'.', CLEANUP => 1);
544    };
545
0
0
    if ($@) {
546
0
        $tempModule = 0;
547
0
        $tempdir = _createTmpDir($tmp);
548    }
549
0
0
    if ($tempdir) {
550        # populate tempdir directory with the ods elements
551
0
        eval {
552
0
0
            if (open(OUT, "> $tempdir/content.xml")) {
553
0
                print OUT $strContent;
554
0
                close(OUT);
555            }
556
0
0
            if (open(OUT, "> $tempdir/mimetype")) {
557
0
                print OUT 'application/vnd.oasis.opendocument.spreadsheet';
558
0
                close(OUT);
559            }
560
0
0
            if (open(OUT, "> $tempdir/meta.xml")) {
561
0
                print OUT _getMeta($lang);
562
0
                close(OUT);
563            }
564
0
0
            if (open(OUT, "> $tempdir/styles.xml")) {
565
0
                print OUT ODS_STYLES_STR;
566
0
                close(OUT);
567            }
568
0
0
            if (open(OUT, "> $tempdir/settings.xml")) {
569
0
                print OUT ODS_SETTINGS_STR;
570
0
                close(OUT);
571            }
572
0
            mkdir($tempdir.'/META-INF/');
573
0
            mkdir($tempdir.'/Configurations2/');
574
0
            mkdir($tempdir.'/Configurations2/acceleator/');
575
0
            mkdir($tempdir.'/Configurations2/images/');
576
0
            mkdir($tempdir.'/Configurations2/popupmenu/');
577
0
            mkdir($tempdir.'/Configurations2/statusbar/');
578
0
            mkdir($tempdir.'/Configurations2/floater/');
579
0
            mkdir($tempdir.'/Configurations2/menubar/');
580
0
            mkdir($tempdir.'/Configurations2/progressbar/');
581
0
            mkdir($tempdir.'/Configurations2/toolbar/');
582
0
0
            if (open(OUT, "> $tempdir/META-INF/manifest.xml")) {
583
0
                print OUT ODS_MANIFEST_STR;
584
0
                close(OUT);
585            }
586        };
587
0
0
        if ($@) {
588
0
0
            $debug and warn "Error createODS $@\n";
589        } else {
590            # create ods file from tempdir directory
591
0
            eval {
592
0
                require Archive::Zip;
593
0
                import Archive::Zip qw( :ERROR_CODES :CONSTANTS );
594
0
                my $zip = Archive::Zip->new();
595
0
                $zip->addTree( $tempdir, '' );
596
0
                $zip->writeToFileNamed($tempdir . '/new.ods');
597            };
598
0
0
            if ($@) {
599
0
                my $cmd = qx(which zip 2>/dev/null || whereis zip);
600
0
                chomp $cmd;
601
0
0
0
                $cmd = 'zip' if (!$cmd || !-x $cmd);
602
0
                system("cd $tempdir && $cmd -r new.ods ./");
603            }
604
0
            my $ok = 0;
605            # read ods file and return as a string
606
0
0
            if (-f "$tempdir/new.ods") {
607
0
0
                if (open (MYFILE, "$tempdir/new.ods")) {
608
0
                    binmode MYFILE;
609
0
                    my $buffer;
610
0
                    while (read (MYFILE, $buffer, 65536)) {
611
0
                        $$strODSRef .= $buffer;
612                    }
613
0
                    close(MYFILE);
614
0
                    $ok = 1;
615                }
616            }
617            # delete tempdir directory
618
0
0
0
            if (!$tempModule && $tempdir) {
619
0
                eval {
620
0
                    require File::Path;
621
0
                    import File::Temp qw/ rmtree /;
622
0
                    rmtree($tempdir);
623                };
624
0
0
                if ($@) {
625
0
                    system("rm -rf $tempdir");
626                }
627            }
628
0
0
            return 1 if ($ok);
629        }
630    }
631
0
    return 0;
632}#createODS
633
634
635# return Meta content for ods file
636sub _getMeta
637{
638
0
0
    my $lang = shift;
639
640
0
    my $myDate = strftime ("%Y-%m-%dT%H:%M:%S", localtime(time()));
641
0
    my $meta = '<?xml version="1.0" encoding="UTF-8"?>
642    <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        <office:meta>
644            <meta:generator>ods-php</meta:generator>
645            <meta:creation-date>' . $myDate . '</meta:creation-date>
646            <dc:date>' . $myDate . '</dc:date>
647            <dc:language>' . $lang . '</dc:language>
648            <meta:editing-cycles>2</meta:editing-cycles>
649            <meta:editing-duration>PT15S</meta:editing-duration>
650            <meta:user-defined meta:name="Info 1"/>
651            <meta:user-defined meta:name="Info 2"/>
652            <meta:user-defined meta:name="Info 3"/>
653            <meta:user-defined meta:name="Info 4"/>
654        </office:meta>
655    </office:document-meta>';
656
0
    return $meta;
657}#_getMeta
658
659
660 - 667
=head2 ImportFramework

Import all the information of a Framework from a excel-xml/ods file.

return :
success

=cut
668
669sub ImportFramework
670{
671
0
0
    my ($filename, $frameworkcode, $deleteFilename) = @_;
672
673
0
    my $tempdir;
674
0
    my $ok = -1;
675
0
    my $dbh = C4::Context->dbh;
676
0
0
0
    if (-r $filename && $dbh) {
677
0
        my $extension = '';
678
0
0
        if ($filename =~ /\.(csv|ods|xml|sql)$/i) {
679
0
            $extension = lc($1);
680        } else {
681
0
0
            unlink ($filename) if ($deleteFilename); # remove temporary file
682
0
            return -1;
683        }
684
0
0
        if ($extension eq 'ods') {
685
0
            ($tempdir, $filename) = _openODS($filename, $deleteFilename);
686        }
687
0
0
        if ($filename) {
688
0
            my $dom;
689
0
            eval {
690
0
0
0
                if ($extension eq 'ods' || $extension eq 'xml') {
691                    # They have xml structure, so read it on a dom object
692
0
                    my $parser = XML::LibXML->new();
693
0
                    $dom = $parser->parse_file($filename);
694
0
0
                    if ($dom) {
695
0
                        my $root = $dom->documentElement();
696                    }
697                } else {
698                    # They are text files, so open it to read
699
0
                    open($dom, '<', $filename);
700                }
701
0
0
                if ($dom) {
702                    # For sql we execute the line
703
0
0
                    if ($extension eq 'sql') {
704
0
                        _parseSQLLine($dbh, $dom, $frameworkcode);
705
0
                        $ok = 0;
706                    } else {
707                        # Process both tables
708
0
                        my $numDeleted = 0;
709
0
                        my $numDeletedAux = 0;
710
0
0
                        if (($numDeletedAux = _import_table($dbh, 'marc_tag_structure', $frameworkcode, $dom, ['frameworkcode', 'tagfield'], $extension)) >= 0) {
711
0
0
                            $numDeleted += $numDeletedAux if ($numDeletedAux > 0);
712
0
0
                            if (($numDeletedAux = _import_table($dbh, 'marc_subfield_structure', $frameworkcode, $dom, ['frameworkcode', 'tagfield', 'tagsubfield'], $extension)) >= 0) {
713
0
0
                                $numDeleted += $numDeletedAux if ($numDeletedAux > 0);
714
0
0
                                $ok = ($numDeleted > 0)?$numDeleted:0;
715                            }
716                        }
717                    }
718                } else {
719
0
0
                    $debug and warn "Error ImportFramework couldn't create dom\n";
720                }
721            };
722
0
0
            if ($@) {
723
0
0
                $debug and warn "Error ImportFramework $@\n";
724            } else {
725
0
0
0
                if ($extension eq 'sql' || $extension eq 'csv') {
726
0
0
                    close($dom) if ($dom);
727                }
728            }
729        }
730
0
0
        unlink ($filename) if ($deleteFilename); # remove temporary file
731    } else {
732
0
0
        $debug and warn "Error ImportFramework no conex to database or not readeable $filename\n";
733    }
734
0
0
0
    if ($deleteFilename && $tempdir && -d $tempdir && -w $tempdir) {
735
0
        eval {
736
0
            require File::Path;
737
0
            import File::Temp qw/ rmtree /;
738
0
            rmtree($tempdir);
739        };
740
0
0
        if ($@) {
741
0
            system("rm -rf $tempdir");
742        }
743    }
744
0
    return $ok;
745}#ImportFramework
746
747
748# Parse the sql statement to see if the frameworkcode is correct
749# We're checking only the delete and insert SQL commands generated in the export process
750sub _parseSQLLine
751{
752
0
0
    my ($dbh, $dom, $frameworkcode) = @_;
753
754
0
    my $parser;
755
0
    eval {
756
0
        require SQL::Statement;
757
0
        $parser = SQL::Parser->new('AnyData');
758
0
        $parser->{RaiseError}=1;
759
0
        $parser->{PrintError}=0;
760    };
761
0
0
    my $literalEscape = (C4::Context->config("db_scheme") eq 'mysql')?'\\':'\'';
762
0
    my $line;
763
0
    my $numLines = 0;
764
0
    while (<$dom>) {
765
0
        s/[\r\n]+$//;
766
0
        $line = $_;
767        # we don't want to execute any sql statement, only the ones dealing with frameworks
768
0
0
        next unless ($line =~ /^\s*(?i:DELETE\s+FROM|INSERT\s+INTO)\s+(?:marc_tag_structure|marc_subfield_structure)/);
769
0
        $numLines++;
770        # We check if the frameworkcode is the same, if not we change it
771
0
0
        unless ($line =~ /'$frameworkcode'/) {
772
0
            my $error = 0;
773
0
0
            if ($parser) {
774
0
                eval {
775
0
0
                    $line = substr($line, 0 ,-1) if ($line =~ /;$/);
776
0
                    my $stmt = SQL::Statement->new($line, $parser);
777
0
                    my $where = $stmt->where();
778
0
0
0
                    if ($where && $where->op() eq '=' && $line =~ /^\s*DELETE/) {
779
0
0
                        $line =~ s/frameworkcode='.*?'/frameworkcode='$frameworkcode';/ unless ($_ =~ /frameworkcode='$frameworkcode'/);
780                    } else {
781
0
                        my @arrFields;
782
0
                        my @arrValues;
783
0
                        my $table;
784                        # Due to lacking of backward compatibility
785
0
0
                        if ($parser->VERSION < 1.30) {
786
0
                            $table = lc($stmt->tables(0)->name());
787
0
0
                            @arrFields = map{lc($_->name)} $stmt->columns;
788
0
                            @arrValues = $stmt->row_values();
789                        } else {
790
0
                            $table = $stmt->tables(0)->name();
791
0
                            @arrValues = $stmt->row_values(0);
792
0
                            my @aux = $stmt->column_defs();
793
0
0
                            for (@{$aux[0]}) {
794
0
                                push @arrFields, $_->{value};
795                            }
796                        }
797
0
0
                        if (scalar(@arrFields) == scalar(@arrValues)) {
798
0
                            my $j = 0;
799
0
                            my $modified = 0;
800
0
                            for (@arrFields) {
801
0
0
0
                                if ($_ eq 'frameworkcode' && $arrValues[$j] ne $frameworkcode) {
802
0
                                    $arrValues[$j] = $dbh->quote($frameworkcode);
803
0
                                    $modified = 1;
804                                } else {
805
0
                                    $arrValues[$j] = $dbh->quote($arrValues[$j]);
806                                }
807
0
                                $j++;
808                            }
809
0
0
                            $line = 'INSERT INTO ' . $table . ' (' . join(',', @arrFields) . ') VALUES (' . join(',', @arrValues) . ');' if ($modified);
810                        }
811                    }
812                };
813
0
0
                $error = 1 if ($@);
814            } else {
815
0
                $error = 1;
816            }
817
0
0
            if ($error) {
818
0
0
                $line .= ';' unless ($line =~ /;$/);
819
0
0
0
                if ($line =~ /^\s*DELETE/) {
820
0
0
                    $line =~ s/frameworkcode='.*?'/frameworkcode='$frameworkcode'/ unless ($_ =~ /frameworkcode='$frameworkcode'/);
821                } elsif ($line =~ /^\s*INSERT\s+INTO\s+(.*?)\s+\((.*?frameworkcode.*?)\)\s+VALUES\s+\((.+)\)\s*;\s*$/) {
822
0
                    my $table = $1;
823
0
                    my $fields = $2;
824
0
                    my $values = $3;
825
0
                    my @arrFields = split(/\s*,\s*/, $fields);
826
0
                    my @arrValues;
827
0
0
                    if ($values) {
828
0
                        _parseSQLInsertValues($values, $literalEscape, \@arrValues);
829                    }
830
0
0
                    if (scalar(@arrFields) == scalar(@arrValues)) {
831
0
                        my $modified = 0;
832                        for (my $i=0; $i < @arrFields; $i++) {
833
0
0
0
0
                            if ($arrFields[$i] eq 'frameworkcode' && $arrValues[$i]->{value} ne $frameworkcode) {
834
0
                                $arrValues[$i]->{value} = $dbh->quote($frameworkcode);
835
0
                                $modified = 1;
836                            } elsif ($arrValues[$i]->{literal}) {
837
0
                                $arrValues[$i]->{value} = $dbh->quote($arrValues[$i]->{value});
838                            }
839
0
                        }
840
0
0
                        if ($modified) {
841
0
0
                            $line = "INSERT INTO $table ($fields) VALUES (" . join(',', map {$_->{value}} @arrValues) . ');';
842                        }
843                    }
844                }
845            }
846        }
847
0
        eval {
848
0
            $dbh->do($line);
849        };
850    }
851}#_parseSQLLine
852
853
854# Simple sub to get the values from the insert sentence
855sub _parseSQLInsertValues
856{
857
0
0
    my ($values, $literalEscape, $arrValues) = @_;
858
859
0
    my ($posBegin, $posLiteral, $currentPos, $lengthValues, $currentChar);
860
0
    $lengthValues = length($values);
861
0
    $currentPos = 0;
862
0
    while ($currentPos < $lengthValues) {
863
0
        $currentChar = substr($values, $currentPos++, 1);
864
0
0
        next if ($currentChar =~ /^\s$/);
865
0
0
0
        next if ($posBegin && $currentChar !~ /^[,']$/);
866
0
0
        unless ($posBegin) {
867
0
0
            if ($currentChar eq '\'') {
868
0
                $posBegin = $currentPos;
869
0
                $posLiteral = $posBegin;
870            } else {
871
0
                $posBegin = $currentPos -1;
872            }
873        } else {
874
0
0
0
0
            if ($currentChar eq ',') {
875
0
0
                unless ($posLiteral) {
876
0
                    push @$arrValues, {literal => 0, value => substr($values, $posBegin, $currentPos -(1 + $posBegin))};
877
0
                    $posBegin = undef;
878                }
879            } elsif ($currentChar eq '\'' && $posLiteral) {
880
0
0
0
                next if ($literalEscape eq '\\' && substr($values, $currentPos -2, 1) eq $literalEscape);
881
0
0
0
                if ($literalEscape eq '\'' && substr($values, $currentPos, 1) eq $literalEscape) {
882
0
                    $currentPos++;
883
0
                    next;
884                }
885
0
                push @$arrValues, {literal => 1 , value => substr($values, $posBegin, $currentPos -( 1 + $posBegin))};
886
0
0
                $currentPos++ if (substr($values, $currentPos, 1) eq ',');
887
0
                $posBegin = undef;
888
0
                $posLiteral = undef;
889            } # We shouldn't get to here if the sql sentence is correct
890        }
891   }
892
0
0
0
   push @$arrValues, {literal => ($posLiteral)?1:0, value => substr($values, $posBegin, $currentPos - $posBegin)} if ($posBegin);
893}#_parseSQLInsertValues
894
895
896# Open (uncompress) ods file and return the content.xml file
897sub _openODS
898{
899
0
0
    my ($filename, $deleteFilename) = @_;
900
901
0
    my $tmp = _getTmp();
902
0
    my $tempModule = 1;
903
0
    my $tempdir;
904
0
    eval {
905
0
        require File::Temp;
906
0
        import File::Temp qw/ tempfile tempdir /;
907
0
0
        $tempdir = tempdir ( 'tmp_ods_' . $$ . '_XXXXXXXX', DIR => (-d $tmp)?$tmp:'.', CLEANUP => 1);
908    };
909
0
0
    if ($@) {
910
0
        $tempModule = 0;
911
0
        $tempdir = _createTmpDir($tmp);
912    }
913
0
0
    if ($tempdir) {
914
0
        eval {
915
0
            require Archive::Zip;
916
0
            import Archive::Zip qw( :ERROR_CODES :CONSTANTS );
917
0
            my $zip = Archive::Zip->new($filename);
918
0
            foreach my $file ($zip->members) {
919
0
0
                next if ($file->isDirectory);
920
0
                (my $extractName = $file->fileName) =~ s{.*/}{};
921
0
0
                next unless ($extractName eq 'content.xml');
922
0
                $file->extractToFileNamed("$tempdir/$extractName");
923            }
924        };
925
0
0
        if ($@) {
926
0
            my $cmd = qx(which unzip 2>/dev/null || whereis unzip);
927
0
            chomp $cmd;
928
0
0
0
            $cmd = 'unzip' if (!$cmd || !-x $cmd);
929
0
            system("$cmd $filename -d $tempdir");
930        }
931
0
0
        if (-f "$tempdir/content.xml") {
932
0
0
            unlink ($filename) if ($deleteFilename);
933
0
            return ($tempdir, "$tempdir/content.xml");
934        }
935    }
936
0
0
    unlink ($filename) if ($deleteFilename);
937
0
    return ($tempdir, undef);
938}#_openODS
939
940
941
942# Check the table and columns corresponds with worksheet
943sub _check_validity_worksheet
944{
945
0
0
    my ($dbh, $table, $nodeFields, $fieldsA, $format) = @_;
946
947
0
    my $ret = 0;
948
0
    eval {
949
0
        my $query = 'DESCRIBE ' . $table;
950
0
        my $sth = $dbh->prepare($query);
951
0
        $sth->execute();
952
0
        $sth->finish;
953
0
        $query = 'SHOW COLUMNS FROM ' . $table;
954
0
        $sth = $dbh->prepare($query);
955
0
        $sth->execute();
956
0
        my $fields = {};
957
0
        while (my $hashRef = $sth->fetchrow_hashref) {
958
0
            $fields->{$hashRef->{Field}} = $hashRef->{Field};
959        }
960
0
        my @fields;
961
0
        my $fieldsR;
962
0
0
        if ($fieldsA) {
963
0
            $fieldsR = $fieldsA;
964        } else {
965
0
            $fieldsR = \@fields;
966
0
            _getFields($nodeFields, $fieldsR, $format);
967        }
968
0
        $ret = 1;
969
0
        for (@$fieldsR) {
970
0
0
            unless (exists($fields->{$_})) {
971
0
                $ret = 0;
972
0
                last;
973            }
974        }
975    };
976
0
    return $ret;
977}#_check_validity_worksheet
978
979
980# Import the data from an excel-xml/ods to mysql tables.
981sub _import_table
982{
983
0
0
    my ($dbh, $table, $frameworkcode, $dom, $PKArray, $format) = @_;
984
0
    my %fields2Delete;
985
0
    my $query;
986
0
    my @fields;
987    # Create hash with all elements defined by primary key to know which ones to delete after parsing the spreadsheet
988
0
    eval {
989
0
        @fields = @$PKArray;
990
0
        shift @fields;
991
0
        $query = 'SELECT ' . join(',', @fields) . ' FROM ' . $table . ' WHERE frameworkcode=?';
992
0
        my $sth = $dbh->prepare($query);
993
0
        $sth->execute($frameworkcode);
994
0
        my $field;
995
0
        while (my $hashRef = $sth->fetchrow_hashref) {
996
0
            $field = '';
997
0
0
            map { $field .= $hashRef->{$_} . '_'; } @fields;
998
0
            chop $field;
999
0
            $fields2Delete{$field} = 1;
1000        }
1001
0
        $sth->finish;
1002    };
1003
0
    my $ok = 0;
1004
0
0
0
    if ($format eq 'csv') {
1005
0
        my @fieldsName = ();
1006
0
        eval {
1007
0
            my $query = 'SHOW COLUMNS FROM ' . $table;
1008
0
            my $sth = $dbh->prepare($query);
1009
0
            $sth->execute();
1010
0
            while (my $hashRef = $sth->fetchrow_hashref) {
1011
0
                push @fieldsName, $hashRef->{Field};
1012            }
1013        };
1014
0
        $ok = _import_table_csv($dbh, $table, $frameworkcode, $dom, $PKArray, \%fields2Delete, \@fieldsName);
1015    } elsif ($format eq 'ods') {
1016
0
        $ok = _import_table_ods($dbh, $table, $frameworkcode, $dom, $PKArray, \%fields2Delete);
1017    } else {
1018
0
        $ok = _import_table_excel($dbh, $table, $frameworkcode, $dom, $PKArray, \%fields2Delete);
1019    }
1020
0
0
    if ($ok) {
1021
0
0
        if (($ok = scalar(keys %fields2Delete)) > 0) {
1022
0
            $query = 'DELETE FROM ' . $table . ' WHERE ';
1023
0
0
            map {$query .= $_ . '=? AND ';} @$PKArray;
1024
0
            $query = substr($query, 0, -4);
1025
0
            my $sth = $dbh->prepare($query);
1026
0
            for (keys %fields2Delete) {
1027
0
                eval {
1028
0
                    $sth->execute(($frameworkcode, split('_', $_)));
1029                };
1030            }
1031        }
1032    } else {
1033
0
        $ok = -1;
1034    }
1035
0
    return $ok;
1036}#_import_table
1037
1038
1039# Insert/Update the row from the spreadsheet in the database
1040sub _processRow_DB
1041{
1042
0
0
    my ($dbh, $db_scheme, $table, $fields, $dataStr, $updateStr, $dataFields, $dataFieldsHash, $PKArray, $fieldsPK, $fields2Delete) = @_;
1043
1044
0
    my $ok = 0;
1045
0
    my $query;
1046
0
0
    if ($db_scheme eq 'mysql') {
1047
0
        $query = 'INSERT INTO ' . $table . ' (' . $fields . ') VALUES (' . $dataStr . ') ON DUPLICATE KEY UPDATE ' . $updateStr;
1048    } else {
1049
0
        $query = 'INSERT INTO ' . $table . ' (' . $fields . ') VALUES (' . $dataStr . ')';
1050    }
1051
0
    eval {
1052
0
        my $sth = $dbh->prepare($query);
1053
0
0
        if ($db_scheme eq 'mysql') {
1054
0
            $sth->execute((@$dataFields, @$dataFields));
1055        } else {
1056
0
            $sth->execute(@$dataFields);
1057        }
1058    };
1059
0
0
    if ($@) {
1060
0
0
        unless ($db_scheme eq 'mysql') {
1061
0
            $query = 'UPDATE ' . $table . ' SET ' . $updateStr . ' WHERE ';
1062
0
0
            map {$query .= $_ . '=? AND ';} @$PKArray;
1063
0
            $query = substr($query, 0, -4);
1064
0
            eval {
1065
0
                my $sth2 = $dbh->prepare($query);
1066
0
                my @dataPK = ();
1067
0
0
                map {push @dataPK, $dataFieldsHash->{$_};} @$PKArray;
1068
0
                $sth2->execute((@$dataFields, @dataPK));
1069            };
1070
0
0
            $ok = 1 unless ($@);
1071        }
1072
0
0
        $debug and warn "Error _processRows_Table $@\n";
1073    } else {
1074
0
        $ok = 1;
1075    }
1076
0
0
    if ($ok) {
1077
0
        my $field = '';
1078
0
0
        map { $field .= $dataFieldsHash->{$_} . '_'; } @$fieldsPK;
1079
0
        chop $field;
1080
0
0
        delete $fields2Delete->{$field} if (exists($fields2Delete->{$field}));
1081    }
1082
0
    return $ok;
1083}#_processRow_DB
1084
1085
1086# Process the rows of a worksheet and insert/update them in a mysql table.
1087sub _processRows_Table
1088{
1089
0
0
    my ($dbh, $frameworkcode, $nodeR, $table, $PKArray, $format, $fields2Delete) = @_;
1090
1091
0
    my $query;
1092
0
    my @fields = ();
1093
0
    my $fields = '';
1094
0
    my $dataStr = '';
1095
0
    my $updateStr = '';
1096
0
    my $j = 0;
1097
0
    my $db_scheme = C4::Context->config("db_scheme");
1098
0
    my $ok = 0;
1099
0
    my @fieldsPK = @$PKArray;
1100
0
    shift @fieldsPK;
1101
0
    while ($nodeR) {
1102
0
0
0
        if ($nodeR->nodeType == 1 && (($format && $format eq 'ods' && $nodeR->nodeName =~ /(?:table:)?table-row/) || ($nodeR->nodeName =~ /(?:ss:)?Row/)) && $nodeR->hasChildNodes()) {
1103
0
0
            if ($j == 0) {
1104                # Get name columns
1105
0
                _getFields($nodeR, \@fields, $format);
1106
0
0
                return 0 unless _check_validity_worksheet($dbh, $table, $nodeR, \@fields, $format);
1107
0
                $fields = join(',', @fields);
1108
0
                $dataStr = '';
1109
0
0
                map { $dataStr .= '?,';} @fields;
1110
0
0
                chop($dataStr) if ($dataStr);
1111
0
                $updateStr = '';
1112
0
0
                map { $updateStr .= $_ . '=?,';} @fields;
1113
0
0
                chop($updateStr) if ($updateStr);
1114            } else {
1115                # Get data from row
1116
0
                my ($dataFields, $dataFieldsR) = _getDataFields($frameworkcode, $nodeR, \@fields, $format);
1117
0
0
                if (scalar(@fields) == scalar(@$dataFieldsR)) {
1118
0
                    $ok = _processRow_DB($dbh, $db_scheme, $table, $fields, $dataStr, $updateStr, $dataFieldsR, $dataFields, $PKArray, \@fieldsPK, $fields2Delete);
1119                }
1120            }
1121
0
            $j++;
1122        }
1123
0
        $nodeR = $nodeR->nextSibling;
1124    }
1125
0
    return 1;
1126}#_processRows_Table
1127
1128
1129
1130
1131# Import worksheet from the csv file to the mysql table
1132sub _import_table_csv
1133{
1134
0
0
    my ($dbh, $table, $frameworkcode, $dom, $PKArray, $fields2Delete, $fields) = @_;
1135
1136
0
    my $row = '';
1137
0
    my $partialRow = '';
1138
0
    my $numFields = @$fields;
1139
0
    my $fieldsNameRead = 0;
1140
0
    my @arrData;
1141
0
    my ($fieldsStr, $dataStr, $updateStr);
1142
0
    my $db_scheme = C4::Context->config("db_scheme");
1143
0
    my @fieldsPK = @$PKArray;
1144
0
    shift @fieldsPK;
1145
0
    my $ok = 0;
1146
0
    my $numRow = 0;
1147
0
    my $pos = 0;
1148
0
    while (<$dom>) {
1149
0
        $row = $_;
1150        # Check whether the line has an unfinished field, i.e., a field with CR/LF in its data
1151
0
0
0
        if ($row =~ /,"[^"]*[\r\n]+$/ || $row =~ /^[^"]+[\r\n]+$/) {
1152
0
            $row =~ s/[\r\n]+$//;
1153
0
            $partialRow .= $row;
1154
0
            next;
1155        }
1156
0
0
        if ($partialRow) {
1157
0
            $row = $partialRow . $row;
1158
0
            $partialRow = '';
1159        }
1160        # Line OK, process it
1161
0
0
        if ($row =~ /(?:".*?",?)+/) {
1162
0
            @arrData = split('","', $row);
1163
0
0
            $arrData[0] = substr($arrData[0], 1) if ($arrData[0] =~ /^"/);
1164
0
            $arrData[$#arrData] =~ s/[\r\n]+$//;
1165
0
0
            chop $arrData[$#arrData] if ($arrData[$#arrData] =~ /"$/);
1166
0
0
            if (@arrData) {
1167
0
0
0
0
0
                if ($arrData[0] eq '#-#' && $arrData[$#arrData] eq '#-#') {
1168                    # Change of table with separators #-#
1169
0
                    return 1;
1170                } elsif ($fieldsNameRead && $arrData[0] eq 'tagfield') {
1171                    # Change of table because we begin with field name with former field names read
1172
0
                    seek($dom, $pos, 0);
1173
0
                    return 1;
1174                }
1175
0
0
                if (scalar(@$fields) == scalar(@arrData)) {
1176
0
0
                    if (!$fieldsNameRead) {
1177                        # New table, we read the field names
1178
0
                        $fieldsNameRead = 1;
1179                        for (my $i=0; $i < @arrData; $i++) {
1180
0
0
                            if ($arrData[$i] ne $fields->[$i]) {
1181
0
                                $fieldsNameRead = 0;
1182
0
                                last;
1183                            }
1184
0
                        }
1185
0
0
                        if ($fieldsNameRead) {
1186
0
                            $fieldsStr = join(',', @$fields);
1187
0
                            $dataStr = '';
1188
0
0
                            map { $dataStr .= '?,';} @$fields;
1189
0
0
                            chop($dataStr) if ($dataStr);
1190
0
                            $updateStr = '';
1191
0
0
                            map { $updateStr .= $_ . '=?,';} @$fields;
1192
0
0
                            chop($updateStr) if ($updateStr);
1193                        }
1194                    } else {
1195                        # Read data
1196
0
                        my $j = 0;
1197
0
                        my %dataFields = ();
1198
0
                        for (@arrData) {
1199
0
0
0
                            if ($fields->[$j] eq 'frameworkcode' && $_ ne $frameworkcode) {
1200
0
                                $dataFields{$fields->[$j]} = $frameworkcode;
1201
0
                                $arrData[$j] = $frameworkcode;
1202                            } else {
1203
0
                                $dataFields{$fields->[$j]} = $_;
1204                            }
1205
0
                            $j++
1206                        }
1207
0
                        $ok = _processRow_DB($dbh, $db_scheme, $table, $fieldsStr, $dataStr, $updateStr, \@arrData, \%dataFields, $PKArray, \@fieldsPK, $fields2Delete);
1208                    }
1209                }
1210
0
                $pos = tell($dom);
1211            }
1212
0
            @arrData = ();
1213        }
1214
0
        $numRow++;
1215    }
1216
0
    return $ok;
1217}#_import_table_csv
1218
1219
1220# Import worksheet from the ods content.xml file to the mysql table
1221sub _import_table_ods
1222{
1223
0
0
    my ($dbh, $table, $frameworkcode, $dom, $PKArray, $fields2Delete) = @_;
1224
1225
0
    my $xc = XML::LibXML::XPathContext->new($dom);
1226
0
    $xc->registerNs('xmlns:office','urn:oasis:names:tc:opendocument:xmlns:office:1.0');
1227
0
    $xc->registerNs('xmlns:table','urn:oasis:names:tc:opendocument:xmlns:table:1.0');
1228
0
    $xc->registerNs('xmlns:text','urn:oasis:names:tc:opendocument:xmlns:text:1.0');
1229
0
    my @nodes;
1230
0
    @nodes = $xc->findnodes('//table:table[@table:name="' . $table . '"]');
1231
0
0
0
    if (@nodes == 1 && $nodes[0]->hasChildNodes()) {
1232
0
        my $nodeR = $nodes[0]->firstChild;
1233
0
        return _processRows_Table($dbh, $frameworkcode, $nodeR, $table, $PKArray, 'ods', $fields2Delete);
1234    } else {
1235
0
0
        $debug and warn "Error _import_table_ods there's not worksheet for $table\n";
1236    }
1237
0
    return 0;
1238}#_import_table_ods
1239
1240
1241# Import worksheet from the excel-xml file to the mysql table
1242sub _import_table_excel
1243{
1244
0
0
    my ($dbh, $table, $frameworkcode, $dom, $PKArray, $fields2Delete) = @_;
1245
1246
0
    my $xc = XML::LibXML::XPathContext->new($dom);
1247
0
    $xc->registerNs('xmlns','urn:schemas-microsoft-com:office:spreadsheet');
1248
0
    $xc->registerNs('xmlns:ss','urn:schemas-microsoft-com:office:spreadsheet');
1249
0
    $xc->registerNs('xmlns:x','urn:schemas-microsoft-com:office:excel');
1250
0
    my @nodes;
1251
0
    @nodes = $xc->findnodes('//ss:Worksheet[@ss:Name="' . $table . '"]');
1252
0
0
    if (@nodes > 0) {
1253        for (my $i=0; $i < @nodes; $i++) {
1254
0
            my @nodesT = $nodes[$i]->getElementsByTagNameNS('urn:schemas-microsoft-com:office:spreadsheet', 'Table');
1255
0
0
0
            if (@nodesT == 1 && $nodesT[0]->hasChildNodes()) {
1256
0
                my $nodeR = $nodesT[0]->firstChild;
1257
0
                return _processRows_Table($dbh, $frameworkcode, $nodeR, $table, $PKArray, undef, $fields2Delete);
1258            }
1259
0
        }
1260    } else {
1261
0
0
        $debug and warn "Error _import_table_excel there's not worksheet for $table\n";
1262    }
1263
0
    return 0;
1264}#_import_table_excel
1265
1266
1267# Get the data from a cell on a ods file through the value attribute or the text node
1268sub _getDataNodeODS
1269{
1270
0
0
    my $node = shift;
1271
1272
0
    my $data;
1273
0
    my $repeated = 0;
1274
0
0
0
    if ($node->nodeType == 1 && $node->nodeName =~ /(?:table:)?table-cell/) {
1275
0
0
0
        if ($node->hasAttributeNS('urn:oasis:names:tc:opendocument:xmlns:office:1.0', 'value')) {
1276
0
            $data = $node->getAttributeNS('urn:oasis:names:tc:opendocument:xmlns:office:1.0', 'value');
1277        } elsif ($node->hasChildNodes()) {
1278
0
            my @nodes2 = $node->getElementsByTagNameNS('urn:oasis:names:tc:opendocument:xmlns:text:1.0', 'p');
1279
0
0
0
            if (@nodes2 == 1 && $nodes2[0]->hasChildNodes()) {
1280
0
                $data = $nodes2[0]->firstChild->nodeValue;
1281            }
1282        }
1283
0
0
        if ($node->hasAttributeNS('urn:oasis:names:tc:opendocument:xmlns:table:1.0', 'number-columns-repeated')) {
1284
0
            $repeated = $node->getAttributeNS('urn:oasis:names:tc:opendocument:xmlns:table:1.0', 'number-columns-repeated');
1285        }
1286    }
1287
0
    return ($data, $repeated);
1288}#_getDataNodeODS
1289
1290
1291# Get the data from a row of a spreadsheet
1292sub _getDataFields
1293{
1294
0
0
    my ($frameworkcode, $node, $fields, $format) = @_;
1295
1296
0
    my $dataFields = {};
1297
0
    my @dataFieldsA = ();
1298
0
0
0
    if ($node && $node->hasChildNodes()) {
1299
0
        my $node2 = $node->firstChild;
1300
0
        my ($data, $repeated);
1301
0
        my $i = 0;
1302
0
        my $ok = 0;
1303
0
        $repeated = 0;
1304
0
        while ($node2) {
1305
0
0
0
            if ($format && $format eq 'ods') {
1306
0
0
                ($data, $repeated) = _getDataNodeODS($node2) if ($repeated <= 0);
1307
0
                $repeated--;
1308
0
0
                $ok = 1 if (defined($data));
1309            } else {
1310
0
0
0
                if ($node2->nodeType == 1 && $node2->nodeName =~ /(?:ss:)?Cell/) {
1311
0
                    my @nodes3 = $node2->getElementsByTagNameNS('urn:schemas-microsoft-com:office:spreadsheet', 'Data');
1312
0
0
0
                    if (@nodes3 == 1 && $nodes3[0]->hasChildNodes()) {
1313
0
                        $data = $nodes3[0]->firstChild->nodeValue;
1314
0
                        $ok = 1;
1315                    }
1316                }
1317            }
1318
0
0
            if ($ok) {
1319
0
0
                $data = '' if ($data eq '#');
1320
0
0
                $data = $frameworkcode if ($fields->[$i] eq 'frameworkcode');
1321
0
                $dataFields->{$fields->[$i]} = $data;
1322
0
                push @dataFieldsA, $data;
1323
0
                $i++;
1324            }
1325
0
            $ok = 0;
1326
0
0
            $node2 = $node2->nextSibling if ($repeated <= 0);
1327        }
1328    }
1329
0
    return ($dataFields, \@dataFieldsA);
1330}#_getDataFields
1331
1332
1333# Get the data from the first row to know the column names
1334sub _getFields
1335{
1336
0
0
    my ($node, $fields, $format) = @_;
1337
1338
0
0
0
    if ($node && $node->hasChildNodes()) {
1339
0
        my $node2 = $node->firstChild;
1340
0
        my ($data, $repeated);
1341
0
        while ($node2) {
1342
0
0
0
            if ($format && $format eq 'ods') {
1343
0
                ($data, $repeated) = _getDataNodeODS($node2);
1344
0
0
                push @$fields, $data if (defined($data));
1345            } else {
1346
0
0
0
                if ($node2->nodeType == 1 && $node2->nodeName =~ /(?:ss:)?Cell/) {
1347
0
                    my @nodes3 = $node2->getElementsByTagNameNS('urn:schemas-microsoft-com:office:spreadsheet', 'Data');
1348
0
0
0
                    if (@nodes3 == 1 && $nodes3[0]->hasChildNodes()) {
1349
0
                        $data = $nodes3[0]->firstChild->nodeValue;
1350
0
                        push @$fields, $data;
1351                    }
1352                }
1353            }
1354
0
            $node2 = $node2->nextSibling;
1355        }
1356    }
1357}#_getFields
1358
1359
1360
1361
13621;