C4/ImportExportFramework.pm

   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
   8 # under the terms of the GNU General Public License as published by
   9 # the Free Software Foundation; either version 3 of the License, or
  10 # (at your option) any later version.
  11 #
  12 # Koha is distributed in the hope that it will be useful, but
  13 # WITHOUT ANY WARRANTY; without even the implied warranty of
  14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  15 # GNU General Public License for more details.
  16 #
  17 # You should have received a copy of the GNU General Public License
  18 # along with Koha; if not, see <http://www.gnu.org/licenses>.
  19
  20 use strict;
  21 use warnings;
  22 use XML::LibXML;
  23 use XML::LibXML::XPathContext;
  24 use Digest::MD5 qw();
  25 use POSIX qw(strftime);
  26
  27 use C4::Context;
  28 use C4::Debug;
  29
  30
  31 use vars qw(@ISA @EXPORT @EXPORT_OK %EXPORT_TAGS);
  32
  33 BEGIN {
  34     require Exporter;
  35     @ISA    = qw(Exporter);
  36     @EXPORT = qw(
  37         &ExportFramework
  38         &ImportFramework
  39         &createODS
  40     );
  41 }
  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"?>
  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">
  80 <office:scripts/>
  81 <office:font-face-decls/>
  82 <office:automatic-styles/>
  83 </office:document-content>';
  84
  85
  86 use constant ODS_STYLES_STR => '<?xml version="1.0" encoding="UTF-8"?>
  87 <office:document-styles xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" xmlns:style="urn:oasis:names:tc:opendocument:xmlns:style:1.0" xmlns:text="urn:oasis:names:tc:opendocument:xmlns:text:1.0" xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0" xmlns:draw="urn:oasis:names:tc:opendocument:xmlns:drawing:1.0" xmlns:fo="urn:oasis:names:tc:opendocument:xmlns:xsl-fo-compatible:1.0" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:meta="urn:oasis:names:tc:opendocument:xmlns:meta:1.0" xmlns:number="urn:oasis:names:tc:opendocument:xmlns:datastyle:1.0" xmlns:svg="urn:oasis:names:tc:opendocument:xmlns:svg-compatible:1.0" xmlns:chart="urn:oasis:names:tc:opendocument:xmlns:chart:1.0" xmlns:dr3d="urn:oasis:names:tc:opendocument:xmlns:dr3d:1.0" xmlns:math="http://www.w3.org/1998/Math/MathML" xmlns:form="urn:oasis:names:tc:opendocument:xmlns:form:1.0" xmlns:script="urn:oasis:names:tc:opendocument:xmlns:script:1.0" xmlns:ooo="http://openoffice.org/2004/office" xmlns:ooow="http://openoffice.org/2004/writer" xmlns:oooc="http://openoffice.org/2004/calc" xmlns:dom="http://www.w3.org/2001/xml-events" office:version="1.0">
  88 <office:font-face-decls></office:font-face-decls>
  89 <office:styles></office:styles>
  90 <office:automatic-styles></office:automatic-styles>
  91 <office:master-styles></office:master-styles>
  92 </office:document-styles>';
  93
  94
  95 use constant ODS_SETTINGS_STR => '<?xml version="1.0" encoding="UTF-8"?>
  96 <office:document-settings xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:config="urn:oasis:names:tc:opendocument:xmlns:config:1.0" xmlns:ooo="http://openoffice.org/2004/office" office:version="1.0"><office:settings>
  97 <config:config-item-set config:name="ooo:view-settings">
  98 <config:config-item config:name="VisibleAreaTop" config:type="int">0</config:config-item>
  99 <config:config-item config:name="VisibleAreaLeft" config:type="int">0</config:config-item>
 100 <config:config-item config:name="VisibleAreaWidth" config:type="int">2000</config:config-item>
 101 <config:config-item config:name="VisibleAreaHeight" config:type="int">900</config:config-item>
 102 <config:config-item-map-indexed config:name="Views"><config:config-item-map-entry>
 103 <config:config-item config:name="ViewId" config:type="string">View1</config:config-item>
 104 <config:config-item-map-named config:name="Tables">
 105 <config:config-item-map-entry config:name="Sheet1"><config:config-item config:name="CursorPositionX" config:type="int">0</config:config-item><config:config-item config:name="CursorPositionY" config:type="int">1</config:config-item><config:config-item config:name="HorizontalSplitMode" config:type="short">0</config:config-item><config:config-item config:name="VerticalSplitMode" config:type="short">0</config:config-item><config:config-item config:name="HorizontalSplitPosition" config:type="int">0</config:config-item><config:config-item config:name="VerticalSplitPosition" config:type="int">0</config:config-item><config:config-item config:name="ActiveSplitRange" config:type="short">2</config:config-item><config:config-item config:name="PositionLeft" config:type="int">0</config:config-item><config:config-item config:name="PositionRight" config:type="int">0</config:config-item><config:config-item config:name="PositionTop" config:type="int">0</config:config-item><config:config-item config:name="PositionBottom" config:type="int">0</config:config-item>
 106 </config:config-item-map-entry>
 107 </config:config-item-map-named>
 108 <config:config-item config:name="ActiveTable" config:type="string">Sheet1</config:config-item>
 109 <config:config-item config:name="HorizontalScrollbarWidth" config:type="int">270</config:config-item>
 110 <config:config-item config:name="ZoomType" config:type="short">0</config:config-item>
 111 <config:config-item config:name="ZoomValue" config:type="int">100</config:config-item>
 112 <config:config-item config:name="PageViewZoomValue" config:type="int">50</config:config-item>
 113 <config:config-item config:name="ShowPageBreakPreview" config:type="boolean">false</config:config-item>
 114 <config:config-item config:name="ShowZeroValues" config:type="boolean">true</config:config-item>
 115 <config:config-item config:name="ShowNotes" config:type="boolean">true</config:config-item>
 116 <config:config-item config:name="ShowGrid" config:type="boolean">true</config:config-item>
 117 <config:config-item config:name="GridColor" config:type="long">12632256</config:config-item>
 118 <config:config-item config:name="ShowPageBreaks" config:type="boolean">true</config:config-item>
 119 <config:config-item config:name="HasColumnRowHeaders" config:type="boolean">true</config:config-item>
 120 <config:config-item config:name="HasSheetTabs" config:type="boolean">true</config:config-item>
 121 <config:config-item config:name="IsOutlineSymbolsSet" config:type="boolean">true</config:config-item>
 122 <config:config-item config:name="IsSnapToRaster" config:type="boolean">false</config:config-item>
 123 <config:config-item config:name="RasterIsVisible" config:type="boolean">false</config:config-item>
 124 <config:config-item config:name="IsRasterAxisSynchronized" config:type="boolean">true</config:config-item></config:config-item-map-entry></config:config-item-map-indexed>
 125 </config:config-item-set>
 126 <config:config-item-set config:name="ooo:configuration-settings">
 127 <config:config-item config:name="ShowZeroValues" config:type="boolean">true</config:config-item>
 128 <config:config-item config:name="ShowNotes" config:type="boolean">true</config:config-item>
 129 <config:config-item config:name="ShowGrid" config:type="boolean">true</config:config-item>
 130 <config:config-item config:name="GridColor" config:type="long">12632256</config:config-item>
 131 <config:config-item config:name="ShowPageBreaks" config:type="boolean">true</config:config-item>
 132 <config:config-item config:name="LinkUpdateMode" config:type="short">3</config:config-item>
 133 <config:config-item config:name="HasColumnRowHeaders" config:type="boolean">true</config:config-item>
 134 <config:config-item config:name="HasSheetTabs" config:type="boolean">true</config:config-item>
 135 <config:config-item config:name="IsOutlineSymbolsSet" config:type="boolean">true</config:config-item>
 136 <config:config-item config:name="IsSnapToRaster" config:type="boolean">false</config:config-item>
 137 <config:config-item config:name="RasterIsVisible" config:type="boolean">false</config:config-item>
 138 <config:config-item config:name="IsRasterAxisSynchronized" config:type="boolean">true</config:config-item>
 139 <config:config-item config:name="AutoCalculate" config:type="boolean">true</config:config-item>
 140 <config:config-item config:name="PrinterName" config:type="string">Generic Printer</config:config-item>
 141 <config:config-item config:name="ApplyUserData" config:type="boolean">true</config:config-item>
 142 <config:config-item config:name="CharacterCompressionType" config:type="short">0</config:config-item>
 143 <config:config-item config:name="SaveVersionOnClose" config:type="boolean">false</config:config-item>
 144 <config:config-item config:name="UpdateFromTemplate" config:type="boolean">false</config:config-item>
 145 <config:config-item config:name="AllowPrintJobCancel" config:type="boolean">true</config:config-item>
 146 <config:config-item config:name="LoadReadonly" config:type="boolean">false</config:config-item>
 147 </config:config-item-set>
 148 </office:settings></office:document-settings>';
 149
 150
 151 use constant ODS_MANIFEST_STR => '<?xml version="1.0" encoding="UTF-8"?>
 152 <manifest:manifest xmlns:manifest="urn:oasis:names:tc:opendocument:xmlns:manifest:1.0">
 153  <manifest:file-entry manifest:media-type="application/vnd.oasis.opendocument.spreadsheet" manifest:full-path="/"/>
 154  <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/statusbar/"/>
 155  <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/accelerator/"/>
 156  <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/floater/"/>
 157  <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/popupmenu/"/>
 158  <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/progressbar/"/>
 159  <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/menubar/"/>
 160  <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/toolbar/"/>
 161  <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/images/Bitmaps/"/>
 162  <manifest:file-entry manifest:media-type="" manifest:full-path="Configurations2/images/"/>
 163  <manifest:file-entry manifest:media-type="application/vnd.sun.xml.ui.configuration" manifest:full-path="Configurations2/"/>
 164  <manifest:file-entry manifest:media-type="text/xml" manifest:full-path="content.xml"/>
 165  <manifest:file-entry manifest:media-type="text/xml" manifest:full-path="styles.xml"/>
 166  <manifest:file-entry manifest:media-type="text/xml" manifest:full-path="meta.xml"/>
 167  <manifest:file-entry manifest:media-type="" manifest:full-path="Thumbnails/"/>
 168  <manifest:file-entry manifest:media-type="text/xml" manifest:full-path="settings.xml"/>
 169 </manifest:manifest>';
 170
 171
 172 =head1 NAME
 173
 174 C4::ImportExportFramework - Import/Export Framework to Excel-xml/ODS Module Functions
 175
 176 =head1 SYNOPSIS
 177
 178   use C4::ImportExportFramework;
 179
 180 =head1 DESCRIPTION
 181
 182 Module to Import/Export Framework to Excel-xml/ODS on intranet administration - MARC Frameworks section
 183
 184 Module to Import/Export Framework to Excel-xml/ODS on intranet administration - MARC Frameworks section
 185 exporting the tables marc_tag_structure, marc_subfield_structure to excel-xml/ods or viceversa
 186
 187 Functions for handling import/export.
 188
 189
 190 =head1 SUBROUTINES
 191
 192
 193
 194 =head2 ExportFramework
 195
 196 Export all the information of a Framework to an excel "xml" file or OpenDocument SpreadSheet "ods" file.
 197
 198 return :
 199 succes
 200
 201 =cut
 202
 203 sub ExportFramework
 204 {
 205     my ($frameworkcode, $xmlStrRef, $mode) = @_;
 206
 207     my $dbh = C4::Context->dbh;
 208     if ($dbh) {
 209         my $dom;
 210         my $root;
 211         my $elementSS;
 212         if ($mode eq 'ods' || $mode eq 'excel') {
 213             eval {
 214                 my $parser = XML::LibXML->new();
 215                 $dom = $parser->parse_string(($mode && $mode eq 'ods')?ODSSTR:XMLSTR);
 216                 if ($dom) {
 217                     $root = $dom->documentElement();
 218                     if ($mode && $mode eq 'ods') {
 219                         my $elementBody = $dom->createElement('office:body');
 220                         $root->appendChild($elementBody);
 221                         $elementSS = $dom->createElement('office:spreadsheet');
 222                         $elementBody->appendChild($elementSS);
 223                     }
 224                 }
 225             };
 226             if ($@) {
 227                 $debug and warn "Error ExportFramework $@\n";
 228                 return 0;
 229             }
 230         }
 231
 232         if (_export_table('marc_tag_structure', $dbh, ($mode eq 'csv')?$xmlStrRef:$dom, ($mode eq 'ods')?$elementSS:$root, $frameworkcode, $mode)) {
 233             if (_export_table('marc_subfield_structure', $dbh, ($mode eq 'csv')?$xmlStrRef:$dom, ($mode eq 'ods')?$elementSS:$root, $frameworkcode, $mode)) {
 234                 $$xmlStrRef = $dom->toString(1) if ($mode eq 'ods' || $mode eq 'excel');
 235                 return 1;
 236             }
 237         }
 238     }
 239     return 0;
 240 }#ExportFramework
 241
 242
 243
 244
 245 # Export all the data from a mysql table to an spreadsheet.
 246 sub _export_table
 247 {
 248     my ($table, $dbh, $dom, $root, $frameworkcode, $mode) = @_;
 249     if ($mode eq 'csv') {
 250         _export_table_csv($table, $dbh, $dom, $root, $frameworkcode);
 251     } elsif ($mode eq 'ods') {
 252         _export_table_ods($table, $dbh, $dom, $root, $frameworkcode);
 253     } else {
 254         _export_table_excel($table, $dbh, $dom, $root, $frameworkcode);
 255     }
 256 }
 257
 258 # Export the mysql table to an csv file
 259 sub _export_table_csv
 260 {
 261     my ($table, $dbh, $strCSV, $root, $frameworkcode) = @_;
 262
 263     eval {
 264         # First row with the name of the columns
 265         my $query = 'SHOW COLUMNS FROM ' . $table;
 266         my $sth = $dbh->prepare($query);
 267         $sth->execute();
 268         my @fields = ();
 269         while (my $hashRef = $sth->fetchrow_hashref) {
 270             $$strCSV .= '"' . $hashRef->{Field} . '",';
 271             push @fields, $hashRef->{Field};
 272         }
 273         chop $$strCSV;
 274         $$strCSV .= chr(10);
 275         # Populate rows with the data from mysql
 276         $query = 'SELECT * FROM ' . $table . ' WHERE frameworkcode=?';
 277         $sth = $dbh->prepare($query);
 278         $sth->execute($frameworkcode);
 279         my $data;
 280         while (my $hashRef = $sth->fetchrow_hashref) {
 281             for (@fields) {
 282                 $hashRef->{$_} =~ s/[\r\n]//g;
 283                 $$strCSV .= '"' . $hashRef->{$_} . '",';
 284             }
 285             chop $$strCSV;
 286             $$strCSV .= chr(10);
 287         }
 288         $$strCSV .= chr(10);
 289         for (@fields) {
 290             # Separator for change of table
 291             $$strCSV .= '"#-#",';
 292         }
 293         chop $$strCSV;
 294         $$strCSV .= chr(10);
 295         $$strCSV .= chr(10);
 296     };
 297     if ($@) {
 298         $debug and warn "Error _export_table_csv $@\n";
 299         return 0;
 300     }
 301     return 1;
 302 }#_export_table_csv
 303
 304
 305 # Export the mysql table to an ods file
 306 sub _export_table_ods
 307 {
 308     my ($table, $dbh, $dom, $root, $frameworkcode) = @_;
 309
 310     eval {
 311         my $elementTable = $dom->createElement('table:table');
 312         $elementTable->setAttribute('table:name', $table);
 313         $elementTable->setAttribute('table:print', 'false');
 314         $root->appendChild($elementTable);
 315         my $elementRow = $dom->createElement('table:table-row');
 316         $elementTable->appendChild($elementRow);
 317
 318         my $elementCell;
 319         my $elementData;
 320         # First row with the name of the columns
 321         my $query = 'SHOW COLUMNS FROM ' . $table;
 322         my $sth = $dbh->prepare($query);
 323         $sth->execute();
 324         my @fields = ();
 325         while (my $hashRef = $sth->fetchrow_hashref) {
 326             $elementCell = $dom->createElement('table:table-cell');
 327             $elementCell->setAttribute('office:value-type', 'string');
 328             $elementCell->setAttribute('office:value', $hashRef->{Field});
 329             $elementRow->appendChild($elementCell);
 330             $elementData = $dom->createElement('text:p');
 331             $elementCell->appendChild($elementData);
 332             $elementData->appendTextNode($hashRef->{Field});
 333             push @fields, {name => $hashRef->{Field}, type => ($hashRef->{Type} =~ /int/i)?'float':'string'};
 334         }
 335         # Populate rows with the data from mysql
 336         $query = 'SELECT * FROM ' . $table . ' WHERE frameworkcode=?';
 337         $sth = $dbh->prepare($query);
 338         $sth->execute($frameworkcode);
 339         my $data;
 340         while (my $hashRef = $sth->fetchrow_hashref) {
 341             $elementRow = $dom->createElement('table:table-row');
 342             $elementTable->appendChild($elementRow);
 343             for (@fields) {
 344                 $data = $hashRef->{$_->{name}};
 345                 if ($_->{type} eq 'float' && !defined($data)) {
 346                     $data = '0';
 347                 } elsif ($_->{type} eq 'string' && (!$data && $data ne '0')) {
 348                     $data = '#';
 349                 }
 350                 $data = _parseContent2Xml($data) if ($_->{type} eq 'string');
 351                 $elementCell = $dom->createElement('table:table-cell');
 352                 $elementCell->setAttribute('office:value-type', $_->{type});
 353                 $elementCell->setAttribute('office:value', $data);
 354                 $elementRow->appendChild($elementCell);
 355                 $elementData = $dom->createElement('text:p');
 356                 $elementCell->appendChild($elementData);
 357                 $elementData->appendTextNode($data);
 358             }
 359         }
 360     };
 361     if ($@) {
 362         $debug and warn "Error _export_table_ods $@\n";
 363         return 0;
 364     }
 365     return 1;
 366 }#_export_table_ods
 367
 368
 369 # Export the mysql table to an excel-xml (openoffice/libreoffice compatible) file
 370 sub _export_table_excel
 371 {
 372     my ($table, $dbh, $dom, $root, $frameworkcode) = @_;
 373
 374     eval {
 375         my $elementWS = $dom->createElement('Worksheet');
 376         $elementWS->setAttribute('ss:Name', $table);
 377         $root->appendChild($elementWS);
 378         my $elementTable = $dom->createElement('ss:Table');
 379         $elementWS->appendChild($elementTable);
 380         my $elementRow = $dom->createElement('ss:Row');
 381         $elementTable->appendChild($elementRow);
 382
 383         # First row with the name of the columns
 384         my $elementCell;
 385         my $elementData;
 386         my $query = 'SHOW COLUMNS FROM ' . $table;
 387         my $sth = $dbh->prepare($query);
 388         $sth->execute();
 389         my @fields = ();
 390         while (my $hashRef = $sth->fetchrow_hashref) {
 391             $elementCell = $dom->createElement('ss:Cell');
 392             $elementCell->setAttribute('ss:StyleID', 's27');
 393             $elementRow->appendChild($elementCell);
 394             $elementData = $dom->createElement('ss:Data');
 395             $elementData->setAttribute('ss:Type', 'String');
 396             $elementCell->appendChild($elementData);
 397             $elementData->appendTextNode($hashRef->{Field});
 398             push @fields, {name => $hashRef->{Field}, type => ($hashRef->{Type} =~ /int/i)?'Number':'String'};
 399         }
 400         # Populate rows with the data from mysql
 401         $query = 'SELECT * FROM ' . $table . ' WHERE frameworkcode=?';
 402         $sth = $dbh->prepare($query);
 403         $sth->execute($frameworkcode);
 404         my $data;
 405         while (my $hashRef = $sth->fetchrow_hashref) {
 406             $elementRow = $dom->createElement('ss:Row');
 407             $elementTable->appendChild($elementRow);
 408             for (@fields) {
 409                 $elementCell = $dom->createElement('ss:Cell');
 410                 $elementRow->appendChild($elementCell);
 411                 $elementData = $dom->createElement('ss:Data');
 412                 $elementData->setAttribute('ss:Type', $_->{type});
 413                 $elementCell->appendChild($elementData);
 414                 $data = $hashRef->{$_->{name}};
 415                 if ($_->{type} eq 'Number' && !defined($data)) {
 416                     $data = '0';
 417                 } elsif ($_->{type} eq 'String' && (!$data && $data ne '0')) {
 418                     $data = '#';
 419                 }
 420                 $elementData->appendTextNode(($_->{type} eq 'String')?_parseContent2Xml($data):$data);
 421             }
 422         }
 423     };
 424     if ($@) {
 425         $debug and warn "Error _export_table_excel $@\n";
 426         return 0;
 427     }
 428     return 1;
 429 }#_export_table_excel
 430
 431
 432
 433
 434
 435
 436
 437 # Format chars problematics to a correct format for xml.
 438 sub _parseContent2Xml
 439 {
 440     my $content = shift;
 441
 442     $content =~ s/\&(?![a-zA-Z#0-9]{1,4};)/&amp;/g;
 443     $content =~ s/</&lt;/g;
 444     $content =~ s/>/&gt;/g;
 445     return $content;
 446 }#_parseContent2Xml
 447
 448
 449 # Get the tmp directory on the system
 450 sub _getTmp
 451 {
 452     my $tmp = '/tmp';
 453     if ($ENV{'TMP'} && -d $ENV{'TMP'}) {
 454         $tmp = $ENV{'TMP'};
 455     } elsif ($ENV{'TMPDIR'} && -d $ENV{'TMPDIR'}) {
 456         $tmp = $ENV{'TMPDIR'};
 457     } elsif ($ENV{'TEMP'} && -d $ENV{'TEMP'}) {
 458         $tmp = $ENV{'TEMP'};
 459     }
 460     return $tmp;
 461 }#_getTmp
 462
 463
 464 # Create our tempdir directory for the ods process
 465 sub _createTmpDir
 466 {
 467     my $tmp = shift;
 468
 469     my $tempdir = (-d $tmp)?$tmp . '/':'./';
 470     $tempdir .= 'tmp_ods_' . Digest::MD5::md5_hex(Digest::MD5::md5_hex(time().{}.rand().{}.$$));
 471     eval {
 472         mkdir $tempdir;
 473     };
 474     if ($@) {
 475         return;
 476     } else {
 477         return $tempdir;
 478     }
 479 }#_createTmpDir
 480
 481 =head2 createODS
 482
 483 Creates a temporary directory to create the ods file and read it to store its content in a string.
 484
 485 return :
 486 success
 487
 488 =cut
 489
 490 sub createODS
 491 {
 492     my ($strContent, $lang, $strODSRef) = @_;
 493
 494     my $tmp = _getTmp();
 495     my $tempModule = 1;
 496     my $tempdir;
 497     eval {
 498         require File::Temp;
 499         import File::Temp qw/ tempfile tempdir /;
 500         $tempdir = tempdir ( 'tmp_ods_' . $$ . '_XXXXXXXX', DIR => (-d $tmp)?$tmp:'.', CLEANUP => 1);
 501     };
 502     if ($@) {
 503         $tempModule = 0;
 504         $tempdir = _createTmpDir($tmp);
 505     }
 506     if ($tempdir) {
 507         my $fh;
 508         # populate tempdir directory with the ods elements
 509         eval {
 510             if (open($fh, '>',  "$tempdir/content.xml")) {
 511                 print {$fh} $strContent;
 512                 close($fh);
 513             }
 514             if (open($fh, '>', "$tempdir/mimetype")) {
 515                 print {$fh} 'application/vnd.oasis.opendocument.spreadsheet';
 516                 close($fh);
 517             }
 518             if (open($fh, '>', "$tempdir/meta.xml")) {
 519                 print {$fh} _getMeta($lang);
 520                 close($fh);
 521             }
 522             if (open($fh, '>', "$tempdir/styles.xml")) {
 523                 print {$fh} ODS_STYLES_STR;
 524                 close($fh);
 525             }
 526             if (open($fh, '>', "$tempdir/settings.xml")) {
 527                 print {$fh} ODS_SETTINGS_STR;
 528                 close($fh);
 529             }
 530             mkdir($tempdir.'/META-INF/');
 531             mkdir($tempdir.'/Configurations2/');
 532             mkdir($tempdir.'/Configurations2/acceleator/');
 533             mkdir($tempdir.'/Configurations2/images/');
 534             mkdir($tempdir.'/Configurations2/popupmenu/');
 535             mkdir($tempdir.'/Configurations2/statusbar/');
 536             mkdir($tempdir.'/Configurations2/floater/');
 537             mkdir($tempdir.'/Configurations2/menubar/');
 538             mkdir($tempdir.'/Configurations2/progressbar/');
 539             mkdir($tempdir.'/Configurations2/toolbar/');
 540
 541             if (open($fh, '>', "$tempdir/META-INF/manifest.xml")) {
 542                 print {$fh} ODS_MANIFEST_STR;
 543                 close($fh);
 544             }
 545         };
 546         if ($@) {
 547             $debug and warn "Error createODS $@\n";
 548         } else {
 549             # create ods file from tempdir directory
 550             eval {
 551                 require Archive::Zip;
 552                 import Archive::Zip qw( :ERROR_CODES :CONSTANTS );
 553                 my $zip = Archive::Zip->new();
 554                 $zip->addTree( $tempdir, '' );
 555                 $zip->writeToFileNamed($tempdir . '/new.ods');
 556             };
 557             if ($@) {
 558                 my $cmd = qx(which zip 2>/dev/null || whereis zip);
 559                 chomp $cmd;
 560                 $cmd = 'zip' if (!$cmd || !-x $cmd);
 561                 system("cd $tempdir && $cmd -r new.ods ./");
 562             }
 563             my $ok = 0;
 564             # read ods file and return as a string
 565             if (-f "$tempdir/new.ods") {
 566                 if (open ($fh, '<', "$tempdir/new.ods")) {
 567                     binmode $fh;
 568                     my $buffer;
 569                     while (read ($fh, $buffer, 65536)) {
 570                         $$strODSRef .= $buffer;
 571                     }
 572                     close($fh);
 573                     $ok = 1;
 574                 }
 575             }
 576             # delete tempdir directory
 577             if (!$tempModule && $tempdir) {
 578                 eval {
 579                     require File::Path;
 580                     import File::Temp qw/ rmtree /;
 581                     rmtree($tempdir);
 582                 };
 583                 if ($@) {
 584                     system("rm -rf $tempdir");
 585                 }
 586             }
 587             return 1 if ($ok);
 588         }
 589     }
 590     return 0;
 591 }#createODS
 592
 593
 594 # return Meta content for ods file
 595 sub _getMeta
 596 {
 597     my $lang = shift;
 598
 599     my $myDate = strftime ("%Y-%m-%dT%H:%M:%S", localtime(time()));
 600     my $meta = '<?xml version="1.0" encoding="UTF-8"?>
 601     <office:document-meta xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:meta="urn:oasis:names:tc:opendocument:xmlns:meta:1.0" xmlns:ooo="http://openoffice.org/2004/office" office:version="1.0">
 602         <office:meta>
 603             <meta:generator>ods-php</meta:generator>
 604             <meta:creation-date>' . $myDate . '</meta:creation-date>
 605             <dc:date>' . $myDate . '</dc:date>
 606             <dc:language>' . $lang . '</dc:language>
 607             <meta:editing-cycles>2</meta:editing-cycles>
 608             <meta:editing-duration>PT15S</meta:editing-duration>
 609             <meta:user-defined meta:name="Info 1"/>
 610             <meta:user-defined meta:name="Info 2"/>
 611             <meta:user-defined meta:name="Info 3"/>
 612             <meta:user-defined meta:name="Info 4"/>
 613         </office:meta>
 614     </office:document-meta>';
 615     return $meta;
 616 }#_getMeta
 617
 618
 619 =head2 ImportFramework
 620
 621 Import all the information of a Framework from a excel-xml/ods file.
 622
 623 return :
 624 success
 625
 626 =cut
 627
 628 sub ImportFramework
 629 {
 630     my ($filename, $frameworkcode, $deleteFilename) = @_;
 631
 632     my $tempdir;
 633     my $ok = -1;
 634     my $dbh = C4::Context->dbh;
 635     if (-r $filename && $dbh) {
 636         my $extension = '';
 637         if ($filename =~ /\.(csv|ods|xml)$/i) {
 638             $extension = lc($1);
 639         } else {
 640             unlink ($filename) if ($deleteFilename); # remove temporary file
 641             return -1;
 642         }
 643         if ($extension eq 'ods') {
 644             ($tempdir, $filename) = _openODS($filename, $deleteFilename);
 645         }
 646         if ($filename) {
 647             my $dom;
 648             eval {
 649                 if ($extension eq 'ods' || $extension eq 'xml') {
 650                     # They have xml structure, so read it on a dom object
 651                     my $parser = XML::LibXML->new();
 652                     $dom = $parser->parse_file($filename);
 653                     if ($dom) {
 654                         my $root = $dom->documentElement();
 655                     }
 656                 } else {
 657                     # They are text files, so open it to read
 658                     open($dom, '<', $filename);
 659                 }
 660                 if ($dom) {
 661                     # Process both tables
 662                     my $numDeleted = 0;
 663                     my $numDeletedAux = 0;
 664                     if (($numDeletedAux = _import_table($dbh, 'marc_tag_structure', $frameworkcode, $dom, ['frameworkcode', 'tagfield'], $extension)) >= 0) {
 665                         $numDeleted += $numDeletedAux if ($numDeletedAux > 0);
 666                         if (($numDeletedAux = _import_table($dbh, 'marc_subfield_structure', $frameworkcode, $dom, ['frameworkcode', 'tagfield', 'tagsubfield'], $extension)) >= 0) {
 667                             $numDeleted += $numDeletedAux if ($numDeletedAux > 0);
 668                             $ok = ($numDeleted > 0)?$numDeleted:0;
 669                         }
 670                     }
 671                 } else {
 672                     $debug and warn "Error ImportFramework couldn't create dom\n";
 673                 }
 674             };
 675             if ($@) {
 676                 $debug and warn "Error ImportFramework $@\n";
 677             } else {
 678                 if ($extension eq 'csv') {
 679                     close($dom) if ($dom);
 680                 }
 681             }
 682         }
 683         unlink ($filename) if ($deleteFilename); # remove temporary file
 684     } else {
 685         $debug and warn "Error ImportFramework no conex to database or not readeable $filename\n";
 686     }
 687     if ($deleteFilename && $tempdir && -d $tempdir && -w $tempdir) {
 688         eval {
 689             require File::Path;
 690             import File::Temp qw/ rmtree /;
 691             rmtree($tempdir);
 692         };
 693         if ($@) {
 694             system("rm -rf $tempdir");
 695         }
 696     }
 697     return $ok;
 698 }#ImportFramework
 699
 700 # Open (uncompress) ods file and return the content.xml file
 701 sub _openODS
 702 {
 703     my ($filename, $deleteFilename) = @_;
 704
 705     my $tmp = _getTmp();
 706     my $tempModule = 1;
 707     my $tempdir;
 708     eval {
 709         require File::Temp;
 710         import File::Temp qw/ tempfile tempdir /;
 711         $tempdir = tempdir ( 'tmp_ods_' . $$ . '_XXXXXXXX', DIR => (-d $tmp)?$tmp:'.', CLEANUP => 1);
 712     };
 713     if ($@) {
 714         $tempModule = 0;
 715         $tempdir = _createTmpDir($tmp);
 716     }
 717     if ($tempdir) {
 718         eval {
 719             require Archive::Zip;
 720             import Archive::Zip qw( :ERROR_CODES :CONSTANTS );
 721             my $zip = Archive::Zip->new($filename);
 722             foreach my $file ($zip->members) {
 723                 next if ($file->isDirectory);
 724                 (my $extractName = $file->fileName) =~ s{.*/}{};
 725                 next unless ($extractName eq 'content.xml');
 726                 $file->extractToFileNamed("$tempdir/$extractName");
 727             }
 728         };
 729         if ($@) {
 730             my $cmd = qx(which unzip 2>/dev/null || whereis unzip);
 731             chomp $cmd;
 732             $cmd = 'unzip' if (!$cmd || !-x $cmd);
 733             system("$cmd $filename -d $tempdir");
 734         }
 735         if (-f "$tempdir/content.xml") {
 736             unlink ($filename) if ($deleteFilename);
 737             return ($tempdir, "$tempdir/content.xml");
 738         }
 739     }
 740     unlink ($filename) if ($deleteFilename);
 741     return ($tempdir, undef);
 742 }#_openODS
 743
 744
 745
 746 # Check the table and columns corresponds with worksheet
 747 sub _check_validity_worksheet
 748 {
 749     my ($dbh, $table, $nodeFields, $fieldsA, $format) = @_;
 750
 751     my $ret = 0;
 752     eval {
 753         my $query = 'DESCRIBE ' . $table;
 754         my $sth = $dbh->prepare($query);
 755         $sth->execute();
 756         $sth->finish;
 757         $query = 'SHOW COLUMNS FROM ' . $table;
 758         $sth = $dbh->prepare($query);
 759         $sth->execute();
 760         my $fields = {};
 761         while (my $hashRef = $sth->fetchrow_hashref) {
 762             $fields->{$hashRef->{Field}} = $hashRef->{Field};
 763         }
 764         my @fields;
 765         my $fieldsR;
 766         if ($fieldsA) {
 767             $fieldsR = $fieldsA;
 768         } else {
 769             $fieldsR = \@fields;
 770             _getFields($nodeFields, $fieldsR, $format);
 771         }
 772         $ret = 1;
 773         for (@$fieldsR) {
 774             unless (exists($fields->{$_})) {
 775                 $ret = 0;
 776                 last;
 777             }
 778         }
 779     };
 780     return $ret;
 781 }#_check_validity_worksheet
 782
 783
 784 # Import the data from an excel-xml/ods to mysql tables.
 785 sub _import_table
 786 {
 787     my ($dbh, $table, $frameworkcode, $dom, $PKArray, $format) = @_;
 788     my %fields2Delete;
 789     my $query;
 790     my @fields;
 791     # Create hash with all elements defined by primary key to know which ones to delete after parsing the spreadsheet
 792     eval {
 793         @fields = @$PKArray;
 794         shift @fields;
 795         $query = 'SELECT ' . join(',', @fields) . ' FROM ' . $table . ' WHERE frameworkcode=?';
 796         my $sth = $dbh->prepare($query);
 797         $sth->execute($frameworkcode);
 798         my $field;
 799         while (my $hashRef = $sth->fetchrow_hashref) {
 800             $field = '';
 801             map { $field .= $hashRef->{$_} . '_'; } @fields;
 802             chop $field;
 803             $fields2Delete{$field} = 1;
 804         }
 805         $sth->finish;
 806     };
 807     my $ok = 0;
 808     if ($format eq 'csv') {
 809         my @fieldsName = ();
 810         eval {
 811             my $query = 'SHOW COLUMNS FROM ' . $table;
 812             my $sth = $dbh->prepare($query);
 813             $sth->execute();
 814             while (my $hashRef = $sth->fetchrow_hashref) {
 815                 push @fieldsName, $hashRef->{Field};
 816             }
 817         };
 818         $ok = _import_table_csv($dbh, $table, $frameworkcode, $dom, $PKArray, \%fields2Delete, \@fieldsName);
 819     } elsif ($format eq 'ods') {
 820         $ok = _import_table_ods($dbh, $table, $frameworkcode, $dom, $PKArray, \%fields2Delete);
 821     } else {
 822         $ok = _import_table_excel($dbh, $table, $frameworkcode, $dom, $PKArray, \%fields2Delete);
 823     }
 824     if ($ok) {
 825         if (($ok = scalar(keys %fields2Delete)) > 0) {
 826             $query = 'DELETE FROM ' . $table . ' WHERE ';
 827             map {$query .= $_ . '=? AND ';} @$PKArray;
 828             $query = substr($query, 0, -4);
 829             my $sth = $dbh->prepare($query);
 830             for (keys %fields2Delete) {
 831                 eval {
 832                     $sth->execute(($frameworkcode, split('_', $_)));
 833                 };
 834             }
 835         }
 836     } else {
 837         $ok = -1;
 838     }
 839     return $ok;
 840 }#_import_table
 841
 842
 843 # Insert/Update the row from the spreadsheet in the database
 844 sub _processRow_DB
 845 {
 846     my ($dbh, $db_scheme, $table, $fields, $dataStr, $updateStr, $dataFields, $dataFieldsHash, $PKArray, $fieldsPK, $fields2Delete) = @_;
 847
 848     my $ok = 0;
 849     my $query;
 850     if ($db_scheme eq 'mysql') {
 851         $query = 'INSERT INTO ' . $table . ' (' . $fields . ') VALUES (' . $dataStr . ') ON DUPLICATE KEY UPDATE ' . $updateStr;
 852     } else {
 853         $query = 'INSERT INTO ' . $table . ' (' . $fields . ') VALUES (' . $dataStr . ')';
 854     }
 855     eval {
 856         my $sth = $dbh->prepare($query);
 857         if ($db_scheme eq 'mysql') {
 858             $sth->execute((@$dataFields, @$dataFields));
 859         } else {
 860             $sth->execute(@$dataFields);
 861         }
 862     };
 863     if ($@) {
 864         unless ($db_scheme eq 'mysql') {
 865             $query = 'UPDATE ' . $table . ' SET ' . $updateStr . ' WHERE ';
 866             map {$query .= $_ . '=? AND ';} @$PKArray;
 867             $query = substr($query, 0, -4);
 868             eval {
 869                 my $sth2 = $dbh->prepare($query);
 870                 my @dataPK = ();
 871                 map {push @dataPK, $dataFieldsHash->{$_};} @$PKArray;
 872                 $sth2->execute((@$dataFields, @dataPK));
 873             };
 874             $ok = 1 unless ($@);
 875         }
 876         $debug and warn "Error _processRows_Table $@\n";
 877     } else {
 878         $ok = 1;
 879     }
 880     if ($ok) {
 881         my $field = '';
 882         map { $field .= $dataFieldsHash->{$_} . '_'; } @$fieldsPK;
 883         chop $field;
 884         delete $fields2Delete->{$field} if (exists($fields2Delete->{$field}));
 885     }
 886     return $ok;
 887 }#_processRow_DB
 888
 889
 890 # Process the rows of a worksheet and insert/update them in a mysql table.
 891 sub _processRows_Table
 892 {
 893     my ($dbh, $frameworkcode, $nodeR, $table, $PKArray, $format, $fields2Delete) = @_;
 894
 895     my $query;
 896     my @fields = ();
 897     my $fields = '';
 898     my $dataStr = '';
 899     my $updateStr = '';
 900     my $j = 0;
 901     my $db_scheme = C4::Context->config("db_scheme");
 902     my $ok = 0;
 903     my @fieldsPK = @$PKArray;
 904     shift @fieldsPK;
 905     while ($nodeR) {
 906         if ($nodeR->nodeType == 1 && (($format && $format eq 'ods' && $nodeR->nodeName =~ /(?:table:)?table-row/) || ($nodeR->nodeName =~ /(?:ss:)?Row/)) && $nodeR->hasChildNodes()) {
 907             if ($j == 0) {
 908                 # Get name columns
 909                 _getFields($nodeR, \@fields, $format);
 910                 return 0 unless _check_validity_worksheet($dbh, $table, $nodeR, \@fields, $format);
 911                 $fields = join(',', @fields);
 912                 $dataStr = '';
 913                 map { $dataStr .= '?,';} @fields;
 914                 chop($dataStr) if ($dataStr);
 915                 $updateStr = '';
 916                 map { $updateStr .= $_ . '=?,';} @fields;
 917                 chop($updateStr) if ($updateStr);
 918             } else {
 919                 # Get data from row
 920                 my ($dataFields, $dataFieldsR) = _getDataFields($frameworkcode, $nodeR, \@fields, $format);
 921                 if (scalar(@fields) == scalar(@$dataFieldsR)) {
 922                     $ok = _processRow_DB($dbh, $db_scheme, $table, $fields, $dataStr, $updateStr, $dataFieldsR, $dataFields, $PKArray, \@fieldsPK, $fields2Delete);
 923                 }
 924             }
 925             $j++;
 926         }
 927         $nodeR = $nodeR->nextSibling;
 928     }
 929     return 1;
 930 }#_processRows_Table
 931
 932
 933
 934
 935 # Import worksheet from the csv file to the mysql table
 936 sub _import_table_csv
 937 {
 938     my ($dbh, $table, $frameworkcode, $dom, $PKArray, $fields2Delete, $fields) = @_;
 939
 940     my $row = '';
 941     my $partialRow = '';
 942     my $numFields = @$fields;
 943     my $fieldsNameRead = 0;
 944     my @arrData;
 945     my ($fieldsStr, $dataStr, $updateStr);
 946     my $db_scheme = C4::Context->config("db_scheme");
 947     my @fieldsPK = @$PKArray;
 948     shift @fieldsPK;
 949     my $ok = 0;
 950     my $numRow = 0;
 951     my $pos = 0;
 952     while (<$dom>) {
 953         $row = $_;
 954         # Check whether the line has an unfinished field, i.e., a field with CR/LF in its data
 955         if ($row =~ /,"[^"]*[\r\n]+$/ || $row =~ /^[^"]+[\r\n]+$/) {
 956             $row =~ s/[\r\n]+$//;
 957             $partialRow .= $row;
 958             next;
 959         }
 960         if ($partialRow) {
 961             $row = $partialRow . $row;
 962             $partialRow = '';
 963         }
 964         # Line OK, process it
 965         if ($row =~ /(?:".*?",?)+/) {
 966             @arrData = split('","', $row);
 967             $arrData[0] = substr($arrData[0], 1) if ($arrData[0] =~ /^"/);
 968             $arrData[$#arrData] =~ s/[\r\n]+$//;
 969             chop $arrData[$#arrData] if ($arrData[$#arrData] =~ /"$/);
 970             if (@arrData) {
 971                 if ($arrData[0] eq '#-#' && $arrData[$#arrData] eq '#-#') {
 972                     # Change of table with separators #-#
 973                     return 1;
 974                 } elsif ($fieldsNameRead && $arrData[0] eq 'tagfield') {
 975                     # Change of table because we begin with field name with former field names read
 976                     seek($dom, $pos, 0);
 977                     return 1;
 978                 }
 979                 if (scalar(@$fields) == scalar(@arrData)) {
 980                     if (!$fieldsNameRead) {
 981                         # New table, we read the field names
 982                         $fieldsNameRead = 1;
 983                         for (my $i=0; $i < @arrData; $i++) {
 984                             if ($arrData[$i] ne $fields->[$i]) {
 985                                 $fieldsNameRead = 0;
 986                                 last;
 987                             }
 988                         }
 989                         if ($fieldsNameRead) {
 990                             $fieldsStr = join(',', @$fields);
 991                             $dataStr = '';
 992                             map { $dataStr .= '?,';} @$fields;
 993                             chop($dataStr) if ($dataStr);
 994                             $updateStr = '';
 995                             map { $updateStr .= $_ . '=?,';} @$fields;
 996                             chop($updateStr) if ($updateStr);
 997                         }
 998                     } else {
 999                         # Read data
1000                         my $j = 0;
1001                         my %dataFields = ();
1002                         for (@arrData) {
1003                             if ($fields->[$j] eq 'frameworkcode' && $_ ne $frameworkcode) {
1004                                 $dataFields{$fields->[$j]} = $frameworkcode;
1005                                 $arrData[$j] = $frameworkcode;
1006                             } else {
1007                                 $dataFields{$fields->[$j]} = $_;
1008                             }
1009                             $j++
1010                         }
1011                         $ok = _processRow_DB($dbh, $db_scheme, $table, $fieldsStr, $dataStr, $updateStr, \@arrData, \%dataFields, $PKArray, \@fieldsPK, $fields2Delete);
1012                     }
1013                 }
1014                 $pos = tell($dom);
1015             }
1016             @arrData = ();
1017         }
1018         $numRow++;
1019     }
1020     return $ok;
1021 }#_import_table_csv
1022
1023
1024 # Import worksheet from the ods content.xml file to the mysql table
1025 sub _import_table_ods
1026 {
1027     my ($dbh, $table, $frameworkcode, $dom, $PKArray, $fields2Delete) = @_;
1028
1029     my $xc = XML::LibXML::XPathContext->new($dom);
1030     $xc->registerNs('xmlns:office','urn:oasis:names:tc:opendocument:xmlns:office:1.0');
1031     $xc->registerNs('xmlns:table','urn:oasis:names:tc:opendocument:xmlns:table:1.0');
1032     $xc->registerNs('xmlns:text','urn:oasis:names:tc:opendocument:xmlns:text:1.0');
1033     my @nodes;
1034     @nodes = $xc->findnodes('//table:table[@table:name="' . $table . '"]');
1035     if (@nodes == 1 && $nodes[0]->hasChildNodes()) {
1036         my $nodeR = $nodes[0]->firstChild;
1037         return _processRows_Table($dbh, $frameworkcode, $nodeR, $table, $PKArray, 'ods', $fields2Delete);
1038     } else {
1039         $debug and warn "Error _import_table_ods there's not worksheet for $table\n";
1040     }
1041     return 0;
1042 }#_import_table_ods
1043
1044
1045 # Import worksheet from the excel-xml file to the mysql table
1046 sub _import_table_excel
1047 {
1048     my ($dbh, $table, $frameworkcode, $dom, $PKArray, $fields2Delete) = @_;
1049
1050     my $xc = XML::LibXML::XPathContext->new($dom);
1051     $xc->registerNs('xmlns','urn:schemas-microsoft-com:office:spreadsheet');
1052     $xc->registerNs('xmlns:ss','urn:schemas-microsoft-com:office:spreadsheet');
1053     $xc->registerNs('xmlns:x','urn:schemas-microsoft-com:office:excel');
1054     my @nodes;
1055     @nodes = $xc->findnodes('//ss:Worksheet[@ss:Name="' . $table . '"]');
1056     if (@nodes > 0) {
1057         for (my $i=0; $i < @nodes; $i++) {
1058             my @nodesT = $nodes[$i]->getElementsByTagNameNS('urn:schemas-microsoft-com:office:spreadsheet', 'Table');
1059             if (@nodesT == 1 && $nodesT[0]->hasChildNodes()) {
1060                 my $nodeR = $nodesT[0]->firstChild;
1061                 return _processRows_Table($dbh, $frameworkcode, $nodeR, $table, $PKArray, undef, $fields2Delete);
1062             }
1063         }
1064     } else {
1065         $debug and warn "Error _import_table_excel there's not worksheet for $table\n";
1066     }
1067     return 0;
1068 }#_import_table_excel
1069
1070
1071 # Get the data from a cell on a ods file through the value attribute or the text node
1072 sub _getDataNodeODS
1073 {
1074     my $node = shift;
1075
1076     my $data;
1077     my $repeated = 0;
1078     if ($node->nodeType == 1 && $node->nodeName =~ /(?:table:)?table-cell/) {
1079         if ($node->hasAttributeNS('urn:oasis:names:tc:opendocument:xmlns:office:1.0', 'value')) {
1080             $data = $node->getAttributeNS('urn:oasis:names:tc:opendocument:xmlns:office:1.0', 'value');
1081         } elsif ($node->hasChildNodes()) {
1082             my @nodes2 = $node->getElementsByTagNameNS('urn:oasis:names:tc:opendocument:xmlns:text:1.0', 'p');
1083             if (@nodes2 == 1 && $nodes2[0]->hasChildNodes()) {
1084                 $data = $nodes2[0]->firstChild->nodeValue;
1085             }
1086         }
1087         if ($node->hasAttributeNS('urn:oasis:names:tc:opendocument:xmlns:table:1.0', 'number-columns-repeated')) {
1088             $repeated = $node->getAttributeNS('urn:oasis:names:tc:opendocument:xmlns:table:1.0', 'number-columns-repeated');
1089         }
1090     }
1091     return ($data, $repeated);
1092 }#_getDataNodeODS
1093
1094
1095 # Get the data from a row of a spreadsheet
1096 sub _getDataFields
1097 {
1098     my ($frameworkcode, $node, $fields, $format) = @_;
1099
1100     my $dataFields = {};
1101     my @dataFieldsA = ();
1102     if ($node && $node->hasChildNodes()) {
1103         my $node2 = $node->firstChild;
1104         my ($data, $repeated);
1105         my $i = 0;
1106         my $ok = 0;
1107         $repeated = 0;
1108         while ($node2) {
1109             if ($format && $format eq 'ods') {
1110                 ($data, $repeated) = _getDataNodeODS($node2) if ($repeated <= 0);
1111                 $repeated--;
1112                 $ok = 1 if (defined($data));
1113             } else {
1114                 if ($node2->nodeType == 1 && $node2->nodeName  =~ /(?:ss:)?Cell/) {
1115                     my @nodes3 = $node2->getElementsByTagNameNS('urn:schemas-microsoft-com:office:spreadsheet', 'Data');
1116                     if (@nodes3 == 1 && $nodes3[0]->hasChildNodes()) {
1117                         $data = $nodes3[0]->firstChild->nodeValue;
1118                         $ok = 1;
1119                     }
1120                 }
1121             }
1122             if ($ok) {
1123                 $data = '' if ($data eq '#');
1124                 $data = $frameworkcode if ($fields->[$i] eq 'frameworkcode');
1125                 $dataFields->{$fields->[$i]} = $data;
1126                 push @dataFieldsA, $data;
1127                 $i++;
1128             }
1129             $ok = 0;
1130             $node2 = $node2->nextSibling if ($repeated <= 0);
1131         }
1132     }
1133     return ($dataFields, \@dataFieldsA);
1134 }#_getDataFields
1135
1136
1137 # Get the data from the first row to know the column names
1138 sub _getFields
1139 {
1140     my ($node, $fields, $format) = @_;
1141
1142     if ($node && $node->hasChildNodes()) {
1143         my $node2 = $node->firstChild;
1144         my ($data, $repeated);
1145         while ($node2) {
1146             if ($format && $format eq 'ods') {
1147                 ($data, $repeated) = _getDataNodeODS($node2);
1148                 push @$fields, $data if (defined($data));
1149             } else {
1150                 if ($node2->nodeType == 1 && $node2->nodeName =~ /(?:ss:)?Cell/) {
1151                     my @nodes3 = $node2->getElementsByTagNameNS('urn:schemas-microsoft-com:office:spreadsheet', 'Data');
1152                     if (@nodes3 == 1 && $nodes3[0]->hasChildNodes()) {
1153                         $data = $nodes3[0]->firstChild->nodeValue;
1154                         push @$fields, $data;
1155                     }
1156                 }
1157             }
1158             $node2 = $node2->nextSibling;
1159         }
1160     }
1161 }#_getFields
1162
1163
1164
1165
1166 1;
1167 __END__
1168
1169 =head1 AUTHOR
1170
1171 Koha Development Team <http://koha-community.org/>
1172
1173 =cut
1174
1175