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