View | Details | Raw Unified | Return to bug 23892
Collapse All | Expand All

(-)a/C4/ImportExportFramework.pm (-157 / +17 lines)
Lines 40-80 BEGIN { Link Here
40
    );
40
    );
41
}
41
}
42
42
43
44
use constant XMLSTR => '<?xml version="1.0" encoding="UTF-8"?>
45
<?mso-application progid="Excel.Sheet"?>
46
<Workbook
47
  xmlns:x="urn:schemas-microsoft-com:office:excel"
48
  xmlns="urn:schemas-microsoft-com:office:spreadsheet"
49
  xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
50
51
<Styles>
52
 <Style ss:ID="Default" ss:Name="Normal">
53
  <Alignment ss:Vertical="Bottom"/>
54
  <Borders/>
55
  <Font/>
56
  <Interior/>
57
  <NumberFormat/>
58
  <Protection/>
59
 </Style>
60
 <Style ss:ID="s27">
61
  <Font x:Family="Swiss" ss:Color="#0000FF" ss:Bold="1"/>
62
 </Style>
63
 <Style ss:ID="s21">
64
  <NumberFormat ss:Format="yyyy\-mm\-dd"/>
65
 </Style>
66
 <Style ss:ID="s22">
67
  <NumberFormat ss:Format="yyyy\-mm\-dd\ hh:mm:ss"/>
68
 </Style>
69
 <Style ss:ID="s23">
70
  <NumberFormat ss:Format="hh:mm:ss"/>
71
 </Style>
72
</Styles>
73
74
</Workbook>
75
';
76
77
78
use constant ODSSTR => '<?xml version="1.0" encoding="UTF-8"?>
43
use constant ODSSTR => '<?xml version="1.0" encoding="UTF-8"?>
79
<office:document-content xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" xmlns:style="urn:oasis:names:tc:opendocument:xmlns:style:1.0" xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0" xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0" xmlns:draw="urn:oasis:names:tc:opendocument:xmlns:drawing:1.0" xmlns:fo="urn:oasis:names:tc:opendocument:xmlns:xsl-fo-compatible:1.0" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:meta="urn:oasis:names:tc:opendocument:xmlns:meta:1.0" xmlns:number="urn:oasis:names:tc:opendocument:xmlns:datastyle:1.0" xmlns:svg="urn:oasis:names:tc:opendocument:xmlns:svg-compatible:1.0" xmlns:chart="urn:oasis:names:tc:opendocument:xmlns:chart:1.0" xmlns:dr3d="urn:oasis:names:tc:opendocument:xmlns:dr3d:1.0" xmlns:math="http://www.w3.org/1998/Math/MathML" xmlns:form="urn:oasis:names:tc:opendocument:xmlns:form:1.0" xmlns:script="urn:oasis:names:tc:opendocument:xmlns:script:1.0" xmlns:ooo="http://openoffice.org/2004/office" xmlns:ooow="http://openoffice.org/2004/writer" xmlns:oooc="http://openoffice.org/2004/calc" xmlns:dom="http://www.w3.org/2001/xml-events" xmlns:xforms="http://www.w3.org/2002/xforms" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" office:version="1.0">
44
<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">
80
<office:scripts/>
45
<office:scripts/>
Lines 171-177 use constant ODS_MANIFEST_STR => '<?xml version="1.0" encoding="UTF-8"?> Link Here
171
136
172
=head1 NAME
137
=head1 NAME
173
138
174
C4::ImportExportFramework - Import/Export Framework to Excel-xml/ODS Module Functions
139
C4::ImportExportFramework - Import/Export Framework to CSV or ODS Module Functions
175
140
176
=head1 SYNOPSIS
141
=head1 SYNOPSIS
177
142
Lines 179-188 C4::ImportExportFramework - Import/Export Framework to Excel-xml/ODS Module Func Link Here
179
144
180
=head1 DESCRIPTION
145
=head1 DESCRIPTION
181
146
182
Module to Import/Export Framework to Excel-xml/ODS on intranet administration - MARC Frameworks section
147
Module to Import/Export Framework to CSV or ODS on intranet administration - MARC Frameworks section
183
148
184
Module to Import/Export Framework to Excel-xml/ODS on intranet administration - MARC Frameworks section
149
Module to Import/Export Framework to CSV or ODS on intranet administration - MARC Frameworks section
185
exporting the tables marc_tag_structure, marc_subfield_structure to excel-xml/ods or viceversa
150
exporting the tables marc_tag_structure, marc_subfield_structure to csv/ods or viceversa
186
151
187
Functions for handling import/export.
152
Functions for handling import/export.
188
153
Lines 193-199 Functions for handling import/export. Link Here
193
158
194
=head2 ExportFramework
159
=head2 ExportFramework
195
160
196
Export all the information of a Framework to an excel "xml" file or OpenDocument SpreadSheet "ods" file.
161
Export all the information of a Framework to a CSV file or OpenDocument SpreadSheet "ods" file.
197
162
198
return :
163
return :
199
succes
164
succes
Lines 209-226 sub ExportFramework Link Here
209
        my $dom;
174
        my $dom;
210
        my $root;
175
        my $root;
211
        my $elementSS;
176
        my $elementSS;
212
        if ($mode eq 'ods' || $mode eq 'excel') {
177
        if ($mode eq 'ods') {
213
            eval {
178
            eval {
214
                my $parser = XML::LibXML->new();
179
                my $parser = XML::LibXML->new();
215
                $dom = $parser->parse_string(($mode && $mode eq 'ods')?ODSSTR:XMLSTR);
180
                $dom = $parser->parse_string(ODSSTR);
216
                if ($dom) {
181
                if ($dom) {
217
                    $root = $dom->documentElement();
182
                    my $elementBody = $dom->createElement('office:body');
218
                    if ($mode && $mode eq 'ods') {
183
                    $root->appendChild($elementBody);
219
                        my $elementBody = $dom->createElement('office:body');
184
                    $elementSS = $dom->createElement('office:spreadsheet');
220
                        $root->appendChild($elementBody);
185
                    $elementBody->appendChild($elementSS);
221
                        $elementSS = $dom->createElement('office:spreadsheet');
222
                        $elementBody->appendChild($elementSS);
223
                    }
224
                }
186
                }
225
            };
187
            };
226
            if ($@) {
188
            if ($@) {
Lines 229-237 sub ExportFramework Link Here
229
            }
191
            }
230
        }
192
        }
231
193
232
        if (_export_table('marc_tag_structure', $dbh, ($mode eq 'csv')?$xmlStrRef:$dom, ($mode eq 'ods')?$elementSS:$root, $frameworkcode, $mode)) {
194
        if (_export_table('marc_tag_structure', $dbh, ($mode eq 'csv')?$xmlStrRef:$dom, $elementSS, $frameworkcode, $mode)) {
233
            if (_export_table('marc_subfield_structure', $dbh, ($mode eq 'csv')?$xmlStrRef:$dom, ($mode eq 'ods')?$elementSS:$root, $frameworkcode, $mode)) {
195
            if (_export_table('marc_subfield_structure', $dbh, ($mode eq 'csv')?$xmlStrRef:$dom, $elementSS, $frameworkcode, $mode)) {
234
                $$xmlStrRef = $dom->toString(1) if ($mode eq 'ods' || $mode eq 'excel');
196
                $$xmlStrRef = $dom->toString(1) if ($mode eq 'ods');
235
                return 1;
197
                return 1;
236
            }
198
            }
237
        }
199
        }
Lines 250-257 sub _export_table Link Here
250
        _export_table_csv($table, $dbh, $dom, $root, $frameworkcode);
212
        _export_table_csv($table, $dbh, $dom, $root, $frameworkcode);
251
    } elsif ($mode eq 'ods') {
213
    } elsif ($mode eq 'ods') {
252
        _export_table_ods($table, $dbh, $dom, $root, $frameworkcode);
214
        _export_table_ods($table, $dbh, $dom, $root, $frameworkcode);
253
    } else {
254
        _export_table_excel($table, $dbh, $dom, $root, $frameworkcode);
255
    }
215
    }
256
}
216
}
257
217
Lines 369-444 sub _export_table_ods Link Here
369
}#_export_table_ods
329
}#_export_table_ods
370
330
371
331
372
# Export the mysql table to an excel-xml (openoffice/libreoffice compatible) file
373
sub _export_table_excel
374
{
375
    my ($table, $dbh, $dom, $root, $frameworkcode) = @_;
376
377
    eval {
378
        my $elementWS = $dom->createElement('Worksheet');
379
        $elementWS->setAttribute('ss:Name', $table);
380
        $root->appendChild($elementWS);
381
        my $elementTable = $dom->createElement('ss:Table');
382
        $elementWS->appendChild($elementTable);
383
        my $elementRow = $dom->createElement('ss:Row');
384
        $elementTable->appendChild($elementRow);
385
386
        # First row with the name of the columns
387
        my $elementCell;
388
        my $elementData;
389
        my $query = 'SHOW COLUMNS FROM ' . $table;
390
        my $sth = $dbh->prepare($query);
391
        $sth->execute();
392
        my @fields = ();
393
        while (my $hashRef = $sth->fetchrow_hashref) {
394
            $elementCell = $dom->createElement('ss:Cell');
395
            $elementCell->setAttribute('ss:StyleID', 's27');
396
            $elementRow->appendChild($elementCell);
397
            $elementData = $dom->createElement('ss:Data');
398
            $elementData->setAttribute('ss:Type', 'String');
399
            $elementCell->appendChild($elementData);
400
            $elementData->appendTextNode($hashRef->{Field});
401
            push @fields, {name => $hashRef->{Field}, type => ($hashRef->{Type} =~ /int/i)?'Number':'String'};
402
        }
403
        # Populate rows with the data from mysql
404
        $query = 'SELECT * FROM ' . $table . ' WHERE frameworkcode=?';
405
        $sth = $dbh->prepare($query);
406
        $sth->execute($frameworkcode);
407
        my $data;
408
        while (my $hashRef = $sth->fetchrow_hashref) {
409
            $elementRow = $dom->createElement('ss:Row');
410
            $elementTable->appendChild($elementRow);
411
            for (@fields) {
412
                $elementCell = $dom->createElement('ss:Cell');
413
                $elementRow->appendChild($elementCell);
414
                $elementData = $dom->createElement('ss:Data');
415
                $elementData->setAttribute('ss:Type', $_->{type});
416
                $elementCell->appendChild($elementData);
417
                $data = $hashRef->{$_->{name}};
418
                if ($_->{type} eq 'Number' && !defined($data)) {
419
                    $data = '0';
420
                } elsif ($_->{type} eq 'String' && !defined($data)) {
421
                    $data = q{};
422
                } elsif ($_->{type} eq 'String' && (!$data && $data ne '0')) {
423
                    $data = '#';
424
                }
425
                $elementData->appendTextNode(($_->{type} eq 'String')?_parseContent2Xml($data):$data);
426
            }
427
        }
428
    };
429
    if ($@) {
430
        $debug and warn "Error _export_table_excel $@\n";
431
        return 0;
432
    }
433
    return 1;
434
}#_export_table_excel
435
436
437
438
439
440
441
442
# Format chars problematics to a correct format for xml.
332
# Format chars problematics to a correct format for xml.
443
sub _parseContent2Xml
333
sub _parseContent2Xml
444
{
334
{
Lines 623-629 sub _getMeta Link Here
623
513
624
=head2 ImportFramework
514
=head2 ImportFramework
625
515
626
Import all the information of a Framework from a excel-xml/ods file.
516
Import all the information of a Framework from a csv or ods file.
627
517
628
return :
518
return :
629
success
519
success
Lines 786-792 sub _check_validity_worksheet Link Here
786
}#_check_validity_worksheet
676
}#_check_validity_worksheet
787
677
788
678
789
# Import the data from an excel-xml/ods to mysql tables.
679
# Import the data from a csv or ods to mysql tables.
790
sub _import_table
680
sub _import_table
791
{
681
{
792
    my ($dbh, $table, $frameworkcode, $dom, $PKArray, $format) = @_;
682
    my ($dbh, $table, $frameworkcode, $dom, $PKArray, $format) = @_;
Lines 823-830 sub _import_table Link Here
823
        $ok = _import_table_csv($dbh, $table, $frameworkcode, $dom, $PKArray, \%fields2Delete, \@fieldsName);
713
        $ok = _import_table_csv($dbh, $table, $frameworkcode, $dom, $PKArray, \%fields2Delete, \@fieldsName);
824
    } elsif ($format eq 'ods') {
714
    } elsif ($format eq 'ods') {
825
        $ok = _import_table_ods($dbh, $table, $frameworkcode, $dom, $PKArray, \%fields2Delete);
715
        $ok = _import_table_ods($dbh, $table, $frameworkcode, $dom, $PKArray, \%fields2Delete);
826
    } else {
827
        $ok = _import_table_excel($dbh, $table, $frameworkcode, $dom, $PKArray, \%fields2Delete);
828
    }
716
    }
829
    if ($ok) {
717
    if ($ok) {
830
        if (($ok = scalar(keys %fields2Delete)) > 0) {
718
        if (($ok = scalar(keys %fields2Delete)) > 0) {
Lines 908-914 sub _processRows_Table Link Here
908
    my @fieldsPK = @$PKArray;
796
    my @fieldsPK = @$PKArray;
909
    shift @fieldsPK;
797
    shift @fieldsPK;
910
    while ($nodeR) {
798
    while ($nodeR) {
911
        if ($nodeR->nodeType == 1 && (($format && $format eq 'ods' && $nodeR->nodeName =~ /(?:table:)?table-row/) || ($nodeR->nodeName =~ /(?:ss:)?Row/)) && $nodeR->hasChildNodes()) {
799
        if ($nodeR->nodeType == 1 && (($nodeR->nodeName =~ /(?:table:)?table-row/) || ($nodeR->nodeName =~ /(?:ss:)?Row/)) && $nodeR->hasChildNodes()) {
912
            if ($j == 0) {
800
            if ($j == 0) {
913
                # Get name columns
801
                # Get name columns
914
                _getFields($nodeR, \@fields, $format);
802
                _getFields($nodeR, \@fields, $format);
Lines 1037-1069 sub _import_table_ods Link Here
1037
    return 0;
925
    return 0;
1038
}#_import_table_ods
926
}#_import_table_ods
1039
927
1040
1041
# Import worksheet from the excel-xml file to the mysql table
1042
sub _import_table_excel
1043
{
1044
    my ($dbh, $table, $frameworkcode, $dom, $PKArray, $fields2Delete) = @_;
1045
1046
    my $xc = XML::LibXML::XPathContext->new($dom);
1047
    $xc->registerNs('xmlns','urn:schemas-microsoft-com:office:spreadsheet');
1048
    $xc->registerNs('xmlns:ss','urn:schemas-microsoft-com:office:spreadsheet');
1049
    $xc->registerNs('xmlns:x','urn:schemas-microsoft-com:office:excel');
1050
    my @nodes;
1051
    @nodes = $xc->findnodes('//ss:Worksheet[@ss:Name="' . $table . '"]');
1052
    if (@nodes > 0) {
1053
        for (my $i=0; $i < @nodes; $i++) {
1054
            my @nodesT = $nodes[$i]->getElementsByTagNameNS('urn:schemas-microsoft-com:office:spreadsheet', 'Table');
1055
            if (@nodesT == 1 && $nodesT[0]->hasChildNodes()) {
1056
                my $nodeR = $nodesT[0]->firstChild;
1057
                return _processRows_Table($dbh, $frameworkcode, $nodeR, $table, $PKArray, undef, $fields2Delete);
1058
            }
1059
        }
1060
    } else {
1061
        $debug and warn "Error _import_table_excel there's not worksheet for $table\n";
1062
    }
1063
    return 0;
1064
}#_import_table_excel
1065
1066
1067
# Get the data from a cell on a ods file through the value attribute or the text node
928
# Get the data from a cell on a ods file through the value attribute or the text node
1068
sub _getDataNodeODS
929
sub _getDataNodeODS
1069
{
930
{
1070
- 

Return to bug 23892