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                 $hashRef->{$_} =~ s/[\r\n]//g;
 326                 $$strCSV .= '"' . $hashRef->{$_} . '",';
 327             }
 328             chop $$strCSV;
 329             $$strCSV .= chr(10);
 330         }
 331         $$strCSV .= chr(10);
 332         for (@fields) {
 333             # Separator for change of table
 334             $$strCSV .= '"#-#",';
 335         }
 336         chop $$strCSV;
 337         $$strCSV .= chr(10);
 338         $$strCSV .= chr(10);
 339     };
 340     if ($@) {
 341         $debug and warn "Error _export_table_csv $@\n";
 342         return 0;
 343     }
 344     return 1;
 345 }#_export_table_csv
 346
 347
 348 # Export the mysql table to an ods file
 349 sub _export_table_ods
 350 {
 351     my ($table, $dbh, $dom, $root, $frameworkcode) = @_;
 352
 353     eval {
 354         my $elementTable = $dom->createElement('table:table');
 355         $elementTable->setAttribute('table:name', $table);
 356         $elementTable->setAttribute('table:print', 'false');
 357         $root->appendChild($elementTable);
 358         my $elementRow = $dom->createElement('table:table-row');
 359         $elementTable->appendChild($elementRow);
 360
 361         my $elementCell;
 362         my $elementData;
 363         # First row with the name of the columns
 364         my $query = 'SHOW COLUMNS FROM ' . $table;
 365         my $sth = $dbh->prepare($query);
 366         $sth->execute();
 367         my @fields = ();
 368         while (my $hashRef = $sth->fetchrow_hashref) {
 369             $elementCell = $dom->createElement('table:table-cell');
 370             $elementCell->setAttribute('office:value-type', 'string');
 371             $elementCell->setAttribute('office:value', $hashRef->{Field});
 372             $elementRow->appendChild($elementCell);
 373             $elementData = $dom->createElement('text:p');
 374             $elementCell->appendChild($elementData);
 375             $elementData->appendTextNode($hashRef->{Field});
 376             push @fields, {name => $hashRef->{Field}, type => ($hashRef->{Type} =~ /int/i)?'float':'string'};
 377         }
 378         # Populate rows with the data from mysql
 379         $query = 'SELECT * FROM ' . $table . ' WHERE frameworkcode=?';
 380         $sth = $dbh->prepare($query);
 381         $sth->execute($frameworkcode);
 382         my $data;
 383         while (my $hashRef = $sth->fetchrow_hashref) {
 384             $elementRow = $dom->createElement('table:table-row');
 385             $elementTable->appendChild($elementRow);
 386             for (@fields) {
 387                 $data = $hashRef->{$_->{name}};
 388                 if ($_->{type} eq 'float' && !defined($data)) {
 389                     $data = '0';
 390                 } elsif ($_->{type} eq 'string' && (!$data && $data ne '0')) {
 391                     $data = '#';
 392                 }
 393                 $data = _parseContent2Xml($data) if ($_->{type} eq 'string');
 394                 $elementCell = $dom->createElement('table:table-cell');
 395                 $elementCell->setAttribute('office:value-type', $_->{type});
 396                 $elementCell->setAttribute('office:value', $data);
 397                 $elementRow->appendChild($elementCell);
 398                 $elementData = $dom->createElement('text:p');
 399                 $elementCell->appendChild($elementData);
 400                 $elementData->appendTextNode($data);
 401             }
 402         }
 403     };
 404     if ($@) {
 405         $debug and warn "Error _export_table_ods $@\n";
 406         return 0;
 407     }
 408     return 1;
 409 }#_export_table_ods
 410
 411
 412 # Export the mysql table to an excel-xml (openoffice/libreoffice compatible) file
 413 sub _export_table_excel
 414 {
 415     my ($table, $dbh, $dom, $root, $frameworkcode) = @_;
 416
 417     eval {
 418         my $elementWS = $dom->createElement('Worksheet');
 419         $elementWS->setAttribute('ss:Name', $table);
 420         $root->appendChild($elementWS);
 421         my $elementTable = $dom->createElement('ss:Table');
 422         $elementWS->appendChild($elementTable);
 423         my $elementRow = $dom->createElement('ss:Row');
 424         $elementTable->appendChild($elementRow);
 425
 426         # First row with the name of the columns
 427         my $elementCell;
 428         my $elementData;
 429         my $query = 'SHOW COLUMNS FROM ' . $table;
 430         my $sth = $dbh->prepare($query);
 431         $sth->execute();
 432         my @fields = ();
 433         while (my $hashRef = $sth->fetchrow_hashref) {
 434             $elementCell = $dom->createElement('ss:Cell');
 435             $elementCell->setAttribute('ss:StyleID', 's27');
 436             $elementRow->appendChild($elementCell);
 437             $elementData = $dom->createElement('ss:Data');
 438             $elementData->setAttribute('ss:Type', 'String');
 439             $elementCell->appendChild($elementData);
 440             $elementData->appendTextNode($hashRef->{Field});
 441             push @fields, {name => $hashRef->{Field}, type => ($hashRef->{Type} =~ /int/i)?'Number':'String'};
 442         }
 443         # Populate rows with the data from mysql
 444         $query = 'SELECT * FROM ' . $table . ' WHERE frameworkcode=?';
 445         $sth = $dbh->prepare($query);
 446         $sth->execute($frameworkcode);
 447         my $data;
 448         while (my $hashRef = $sth->fetchrow_hashref) {
 449             $elementRow = $dom->createElement('ss:Row');
 450             $elementTable->appendChild($elementRow);
 451             for (@fields) {
 452                 $elementCell = $dom->createElement('ss:Cell');
 453                 $elementRow->appendChild($elementCell);
 454                 $elementData = $dom->createElement('ss:Data');
 455                 $elementData->setAttribute('ss:Type', $_->{type});
 456                 $elementCell->appendChild($elementData);
 457                 $data = $hashRef->{$_->{name}};
 458                 if ($_->{type} eq 'Number' && !defined($data)) {
 459                     $data = '0';
 460                 } elsif ($_->{type} eq 'String' && (!$data && $data ne '0')) {
 461                     $data = '#';
 462                 }
 463                 $elementData->appendTextNode(($_->{type} eq 'String')?_parseContent2Xml($data):$data);
 464             }
 465         }
 466     };
 467     if ($@) {
 468         $debug and warn "Error _export_table_excel $@\n";
 469         return 0;
 470     }
 471     return 1;
 472 }#_export_table_excel
 473
 474
 475
 476
 477
 478
 479
 480 # Format chars problematics to a correct format for xml.
 481 sub _parseContent2Xml
 482 {
 483     my $content = shift;
 484
 485     $content =~ s/\&(?![a-zA-Z#0-9]{1,4};)/&amp;/g;
 486     $content =~ s/</&lt;/g;
 487     $content =~ s/>/&gt;/g;
 488     return $content;
 489 }#_parseContent2Xml
 490
 491
 492 # Get the tmp directory on the system
 493 sub _getTmp
 494 {
 495     my $tmp = '/tmp';
 496     if ($ENV{'TMP'} && -d $ENV{'TMP'}) {
 497         $tmp = $ENV{'TMP'};
 498     } elsif ($ENV{'TMPDIR'} && -d $ENV{'TMPDIR'}) {
 499         $tmp = $ENV{'TMPDIR'};
 500     } elsif ($ENV{'TEMP'} && -d $ENV{'TEMP'}) {
 501         $tmp = $ENV{'TEMP'};
 502     }
 503     return $tmp;
 504 }#_getTmp
 505
 506
 507 # Create our tempdir directory for the ods process
 508 sub _createTmpDir
 509 {
 510     my $tmp = shift;
 511
 512     my $tempdir = (-d $tmp)?$tmp . '/':'./';
 513     $tempdir .= 'tmp_ods_' . Digest::MD5::md5_hex(Digest::MD5::md5_hex(time().{}.rand().{}.$$));
 514     eval {
 515         mkdir $tempdir;
 516     };
 517     if ($@) {
 518         return undef;
 519     } else {
 520         return $tempdir;
 521     }
 522 }#_createTmpDir
 523
 524 =head2 createODS
 525
 526 Creates a temporary directory to create the ods file and read it to store its content in a string.
 527
 528 return :
 529 success
 530
 531 =cut
 532
 533 sub createODS
 534 {
 535     my ($strContent, $lang, $strODSRef) = @_;
 536
 537     my $tmp = _getTmp();
 538     my $tempModule = 1;
 539     my $tempdir;
 540     eval {
 541         require File::Temp;
 542         import File::Temp qw/ tempfile tempdir /;
 543         $tempdir = tempdir ( 'tmp_ods_' . $$ . '_XXXXXXXX', DIR => (-d $tmp)?$tmp:'.', CLEANUP => 1);
 544     };
 545     if ($@) {
 546         $tempModule = 0;
 547         $tempdir = _createTmpDir($tmp);
 548     }
 549     if ($tempdir) {
 550         # populate tempdir directory with the ods elements
 551         eval {
 552             if (open(OUT, "> $tempdir/content.xml")) {
 553                 print OUT $strContent;
 554                 close(OUT);
 555             }
 556             if (open(OUT, "> $tempdir/mimetype")) {
 557                 print OUT 'application/vnd.oasis.opendocument.spreadsheet';
 558                 close(OUT);
 559             }
 560             if (open(OUT, "> $tempdir/meta.xml")) {
 561                 print OUT _getMeta($lang);
 562                 close(OUT);
 563             }
 564             if (open(OUT, "> $tempdir/styles.xml")) {
 565                 print OUT ODS_STYLES_STR;
 566                 close(OUT);
 567             }
 568             if (open(OUT, "> $tempdir/settings.xml")) {
 569                 print OUT ODS_SETTINGS_STR;
 570                 close(OUT);
 571             }
 572             mkdir($tempdir.'/META-INF/');
 573             mkdir($tempdir.'/Configurations2/');
 574             mkdir($tempdir.'/Configurations2/acceleator/');
 575             mkdir($tempdir.'/Configurations2/images/');
 576             mkdir($tempdir.'/Configurations2/popupmenu/');
 577             mkdir($tempdir.'/Configurations2/statusbar/');
 578             mkdir($tempdir.'/Configurations2/floater/');
 579             mkdir($tempdir.'/Configurations2/menubar/');
 580             mkdir($tempdir.'/Configurations2/progressbar/');
 581             mkdir($tempdir.'/Configurations2/toolbar/');
 582             if (open(OUT, "> $tempdir/META-INF/manifest.xml")) {
 583                 print OUT ODS_MANIFEST_STR;
 584                 close(OUT);
 585             }
 586         };
 587         if ($@) {
 588             $debug and warn "Error createODS $@\n";
 589         } else {
 590             # create ods file from tempdir directory
 591             eval {
 592                 require Archive::Zip;
 593                 import Archive::Zip qw( :ERROR_CODES :CONSTANTS );
 594                 my $zip = Archive::Zip->new();
 595                 $zip->addTree( $tempdir, '' );
 596                 $zip->writeToFileNamed($tempdir . '/new.ods');
 597             };
 598             if ($@) {
 599                 my $cmd = qx(which zip 2>/dev/null || whereis zip);
 600                 chomp $cmd;
 601                 $cmd = 'zip' if (!$cmd || !-x $cmd);
 602                 system("cd $tempdir && $cmd -r new.ods ./");
 603             }
 604             my $ok = 0;
 605             # read ods file and return as a string
 606             if (-f "$tempdir/new.ods") {
 607                 if (open (MYFILE, "$tempdir/new.ods")) {
 608                     binmode MYFILE;
 609                     my $buffer;
 610                     while (read (MYFILE, $buffer, 65536)) {
 611                         $$strODSRef .= $buffer;
 612                     }
 613                     close(MYFILE);
 614                     $ok = 1;
 615                 }
 616             }
 617             # delete tempdir directory
 618             if (!$tempModule && $tempdir) {
 619                 eval {
 620                     require File::Path;
 621                     import File::Temp qw/ rmtree /;
 622                     rmtree($tempdir);
 623                 };
 624                 if ($@) {
 625                     system("rm -rf $tempdir");
 626                 }
 627             }
 628             return 1 if ($ok);
 629         }
 630     }
 631     return 0;
 632 }#createODS
 633
 634
 635 # return Meta content for ods file
 636 sub _getMeta
 637 {
 638     my $lang = shift;
 639
 640     my $myDate = strftime ("%Y-%m-%dT%H:%M:%S", localtime(time()));
 641     my $meta = '<?xml version="1.0" encoding="UTF-8"?>
 642     <office:document-meta xmlns:office="urn:oasis:names:tc:opendocument:xmlns:office:1.0" xmlns:xlink="http://www.w3.org/1999/xlink" xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:meta="urn:oasis:names:tc:opendocument:xmlns:meta:1.0" xmlns:ooo="http://openoffice.org/2004/office" office:version="1.0">
 643         <office:meta>
 644             <meta:generator>ods-php</meta:generator>
 645             <meta:creation-date>' . $myDate . '</meta:creation-date>
 646             <dc:date>' . $myDate . '</dc:date>
 647             <dc:language>' . $lang . '</dc:language>
 648             <meta:editing-cycles>2</meta:editing-cycles>
 649             <meta:editing-duration>PT15S</meta:editing-duration>
 650             <meta:user-defined meta:name="Info 1"/>
 651             <meta:user-defined meta:name="Info 2"/>
 652             <meta:user-defined meta:name="Info 3"/>
 653             <meta:user-defined meta:name="Info 4"/>
 654         </office:meta>
 655     </office:document-meta>';
 656     return $meta;
 657 }#_getMeta
 658
 659
 660 =head2 ImportFramework
 661
 662 Import all the information of a Framework from a excel-xml/ods file.
 663
 664 return :
 665 success
 666
 667 =cut
 668
 669 sub ImportFramework
 670 {
 671     my ($filename, $frameworkcode, $deleteFilename) = @_;
 672
 673     my $tempdir;
 674     my $ok = -1;
 675     my $dbh = C4::Context->dbh;
 676     if (-r $filename && $dbh) {
 677         my $extension = '';
 678         if ($filename =~ /\.(csv|ods|xml|sql)$/i) {
 679             $extension = lc($1);
 680         } else {
 681             unlink ($filename) if ($deleteFilename); # remove temporary file
 682             return -1;
 683         }
 684         if ($extension eq 'ods') {
 685             ($tempdir, $filename) = _openODS($filename, $deleteFilename);
 686         }
 687         if ($filename) {
 688             my $dom;
 689             eval {
 690                 if ($extension eq 'ods' || $extension eq 'xml') {
 691                     # They have xml structure, so read it on a dom object
 692                     my $parser = XML::LibXML->new();
 693                     $dom = $parser->parse_file($filename);
 694                     if ($dom) {
 695                         my $root = $dom->documentElement();
 696                     }
 697                 } else {
 698                     # They are text files, so open it to read
 699                     open($dom, '<', $filename);
 700                 }
 701                 if ($dom) {
 702                     # For sql we execute the line
 703                     if ($extension eq 'sql') {
 704                         _parseSQLLine($dbh, $dom, $frameworkcode);
 705                         $ok = 0;
 706                     } else {
 707                         # Process both tables
 708                         my $numDeleted = 0;
 709                         my $numDeletedAux = 0;
 710                         if (($numDeletedAux = _import_table($dbh, 'marc_tag_structure', $frameworkcode, $dom, ['frameworkcode', 'tagfield'], $extension)) >= 0) {
 711                             $numDeleted += $numDeletedAux if ($numDeletedAux > 0);
 712                             if (($numDeletedAux = _import_table($dbh, 'marc_subfield_structure', $frameworkcode, $dom, ['frameworkcode', 'tagfield', 'tagsubfield'], $extension)) >= 0) {
 713                                 $numDeleted += $numDeletedAux if ($numDeletedAux > 0);
 714                                 $ok = ($numDeleted > 0)?$numDeleted:0;
 715                             }
 716                         }
 717                     }
 718                 } else {
 719                     $debug and warn "Error ImportFramework couldn't create dom\n";
 720                 }
 721             };
 722             if ($@) {
 723                 $debug and warn "Error ImportFramework $@\n";
 724             } else {
 725                 if ($extension eq 'sql' || $extension eq 'csv') {
 726                     close($dom) if ($dom);
 727                 }
 728             }
 729         }
 730         unlink ($filename) if ($deleteFilename); # remove temporary file
 731     } else {
 732         $debug and warn "Error ImportFramework no conex to database or not readeable $filename\n";
 733     }
 734     if ($deleteFilename && $tempdir && -d $tempdir && -w $tempdir) {
 735         eval {
 736             require File::Path;
 737             import File::Temp qw/ rmtree /;
 738             rmtree($tempdir);
 739         };
 740         if ($@) {
 741             system("rm -rf $tempdir");
 742         }
 743     }
 744     return $ok;
 745 }#ImportFramework
 746
 747
 748 # Parse the sql statement to see if the frameworkcode is correct
 749 # We're checking only the delete and insert SQL commands generated in the export process
 750 sub _parseSQLLine
 751 {
 752     my ($dbh, $dom, $frameworkcode) = @_;
 753
 754     my $parser;
 755     eval {
 756         require SQL::Statement;
 757         $parser = SQL::Parser->new('AnyData');
 758         $parser->{RaiseError}=1;
 759         $parser->{PrintError}=0;
 760     };
 761     my $literalEscape = (C4::Context->config("db_scheme") eq 'mysql')?'\\':'\'';
 762     my $line;
 763     my $numLines = 0;
 764     while (<$dom>) {
 765         s/[\r\n]+$//;
 766         $line = $_;
 767         # we don't want to execute any sql statement, only the ones dealing with frameworks
 768         next unless ($line =~ /^\s*(?i:DELETE\s+FROM|INSERT\s+INTO)\s+(?:marc_tag_structure|marc_subfield_structure)/);
 769         $numLines++;
 770         # We check if the frameworkcode is the same, if not we change it
 771         unless ($line =~ /'$frameworkcode'/) {
 772             my $error = 0;
 773             if ($parser) {
 774                 eval {
 775                     $line = substr($line, 0 ,-1) if ($line =~ /;$/);
 776                     my $stmt = SQL::Statement->new($line, $parser);
 777                     my $where = $stmt->where();
 778                     if ($where && $where->op() eq '=' && $line =~ /^\s*DELETE/) {
 779                         $line =~ s/frameworkcode='.+?'/frameworkcode='$frameworkcode';/ unless ($_ =~ /frameworkcode='$frameworkcode'/);
 780                     } else {
 781                         my @arrFields;
 782                         my @arrValues;
 783                         my $table;
 784                         # Due to lacking of backward compatibility
 785                         if ($parser->VERSION < 1.30) {
 786                             $table = lc($stmt->tables(0)->name());
 787                             @arrFields = map{lc($_->name)} $stmt->columns;
 788                             @arrValues = $stmt->row_values();
 789                         } else {
 790                             $table = $stmt->tables(0)->name();
 791                             @arrValues = $stmt->row_values(0);
 792                             my @aux = $stmt->column_defs();
 793                             for (@{$aux[0]}) {
 794                                 push @arrFields, $_->{value};
 795                             }
 796                         }
 797                         if (scalar(@arrFields) == scalar(@arrValues)) {
 798                             my $j = 0;
 799                             my $modified = 0;
 800                             for (@arrFields) {
 801                                 if ($_ eq 'frameworkcode' && $arrValues[$j] ne $frameworkcode) {
 802                                     $arrValues[$j] = $dbh->quote($frameworkcode);
 803                                     $modified = 1;
 804                                 } else {
 805                                     $arrValues[$j] = $dbh->quote($arrValues[$j]);
 806                                 }
 807                                 $j++;
 808                             }
 809                             $line = 'INSERT INTO ' . $table . ' (' . join(',', @arrFields) . ') VALUES (' . join(',', @arrValues) . ');' if ($modified);
 810                         }
 811                     }
 812                 };
 813                 $error = 1 if ($@);
 814             } else {
 815                 $error = 1;
 816             }
 817             if ($error) {
 818                 $line .= ';' unless ($line =~ /;$/);
 819                 if ($line =~ /^\s*DELETE/) {
 820                     $line =~ s/frameworkcode='.+?'/frameworkcode='$frameworkcode'/ unless ($_ =~ /frameworkcode='$frameworkcode'/);
 821                 } elsif ($line =~ /^\s*INSERT\s+INTO\s+(.*?)\s+\((.*?frameworkcode.*?)\)\s+VALUES\s+\((.+)\)\s*;\s*$/) {
 822                     my $table = $1;
 823                     my $fields = $2;
 824                     my $values = $3;
 825                     my @arrFields = split(/\s*,\s*/, $fields);
 826                     my @arrValues;
 827                     if ($values) {
 828                         _parseSQLInsertValues($values, $literalEscape, \@arrValues);
 829                     }
 830                     if (scalar(@arrFields) == scalar(@arrValues)) {
 831                         my $modified = 0;
 832                         for (my $i=0; $i < @arrFields; $i++) {
 833                             if ($arrFields[$i] eq 'frameworkcode' && $arrValues[$i]->{value} ne $frameworkcode) {
 834                                 $arrValues[$i]->{value} = $dbh->quote($frameworkcode);
 835                                 $modified = 1;
 836                             } elsif ($arrValues[$i]->{literal}) {
 837                                 $arrValues[$i]->{value} = $dbh->quote($arrValues[$i]->{value});
 838                             }
 839                         }
 840                         if ($modified) {
 841                             $line = "INSERT INTO $table ($fields) VALUES (" . join(',', map {$_->{value}} @arrValues) . ');';
 842                         }
 843                     }
 844                 }
 845             }
 846         }
 847         eval {
 848             $dbh->do($line);
 849         };
 850     }
 851 }#_parseSQLLine
 852
 853
 854 # Simple sub to get the values from the insert sentence
 855 sub _parseSQLInsertValues
 856 {
 857     my ($values, $literalEscape, $arrValues) = @_;
 858
 859     my ($posBegin, $posLiteral, $currentPos, $lengthValues, $currentChar);
 860     $lengthValues = length($values);
 861     $currentPos = 0;
 862     while ($currentPos < $lengthValues) {
 863         $currentChar = substr($values, $currentPos++, 1);
 864         next if ($currentChar =~ /^\s$/);
 865         next if ($posBegin && $currentChar !~ /^[,']$/);
 866         unless ($posBegin) {
 867             if ($currentChar eq '\'') {
 868                 $posBegin = $currentPos;
 869                 $posLiteral = $posBegin;
 870             } else {
 871                 $posBegin = $currentPos -1;
 872             }
 873         } else {
 874             if ($currentChar eq ',') {
 875                 unless ($posLiteral) {
 876                     push @$arrValues, {literal => 0, value => substr($values, $posBegin, $currentPos -(1 + $posBegin))};
 877                     $posBegin = undef;
 878                 }
 879             } elsif ($currentChar eq '\'' && $posLiteral) {
 880                 next if ($literalEscape eq '\\' && substr($values, $currentPos -2, 1) eq $literalEscape);
 881                 if ($literalEscape eq '\'' && substr($values, $currentPos, 1) eq $literalEscape) {
 882                     $currentPos++;
 883                     next;
 884                 }
 885                 push @$arrValues, {literal => 1 , value => substr($values, $posBegin, $currentPos -( 1 + $posBegin))};
 886                 $currentPos++ if (substr($values, $currentPos, 1) eq ',');
 887                 $posBegin = undef;
 888                 $posLiteral = undef;
 889             } # We shouldn't get to here if the sql sentence is correct
 890         }
 891    }
 892    push @$arrValues, {literal => ($posLiteral)?1:0, value => substr($values, $posBegin, $currentPos - $posBegin)} if ($posBegin);
 893 }#_parseSQLInsertValues
 894
 895
 896 # Open (uncompress) ods file and return the content.xml file
 897 sub _openODS
 898 {
 899     my ($filename, $deleteFilename) = @_;
 900
 901     my $tmp = _getTmp();
 902     my $tempModule = 1;
 903     my $tempdir;
 904     eval {
 905         require File::Temp;
 906         import File::Temp qw/ tempfile tempdir /;
 907         $tempdir = tempdir ( 'tmp_ods_' . $$ . '_XXXXXXXX', DIR => (-d $tmp)?$tmp:'.', CLEANUP => 1);
 908     };
 909     if ($@) {
 910         $tempModule = 0;
 911         $tempdir = _createTmpDir($tmp);
 912     }
 913     if ($tempdir) {
 914         eval {
 915             require Archive::Zip;
 916             import Archive::Zip qw( :ERROR_CODES :CONSTANTS );
 917             my $zip = Archive::Zip->new($filename);
 918             foreach my $file ($zip->members) {
 919                 next if ($file->isDirectory);
 920                 (my $extractName = $file->fileName) =~ s{.*/}{};
 921                 next unless ($extractName eq 'content.xml');
 922                 $file->extractToFileNamed("$tempdir/$extractName");
 923             }
 924         };
 925         if ($@) {
 926             my $cmd = qx(which unzip 2>/dev/null || whereis unzip);
 927             chomp $cmd;
 928             $cmd = 'unzip' if (!$cmd || !-x $cmd);
 929             system("$cmd $filename -d $tempdir");
 930         }
 931         if (-f "$tempdir/content.xml") {
 932             unlink ($filename) if ($deleteFilename);
 933             return ($tempdir, "$tempdir/content.xml");
 934         }
 935     }
 936     unlink ($filename) if ($deleteFilename);
 937     return ($tempdir, undef);
 938 }#_openODS
 939
 940
 941
 942 # Check the table and columns corresponds with worksheet
 943 sub _check_validity_worksheet
 944 {
 945     my ($dbh, $table, $nodeFields, $fieldsA, $format) = @_;
 946
 947     my $ret = 0;
 948     eval {
 949         my $query = 'DESCRIBE ' . $table;
 950         my $sth = $dbh->prepare($query);
 951         $sth->execute();
 952         $sth->finish;
 953         $query = 'SHOW COLUMNS FROM ' . $table;
 954         $sth = $dbh->prepare($query);
 955         $sth->execute();
 956         my $fields = {};
 957         while (my $hashRef = $sth->fetchrow_hashref) {
 958             $fields->{$hashRef->{Field}} = $hashRef->{Field};
 959         }
 960         my @fields;
 961         my $fieldsR;
 962         if ($fieldsA) {
 963             $fieldsR = $fieldsA;
 964         } else {
 965             $fieldsR = \@fields;
 966             _getFields($nodeFields, $fieldsR, $format);
 967         }
 968         $ret = 1;
 969         for (@$fieldsR) {
 970             unless (exists($fields->{$_})) {
 971                 $ret = 0;
 972                 last;
 973             }
 974         }
 975     };
 976     return $ret;
 977 }#_check_validity_worksheet
 978
 979
 980 # Import the data from an excel-xml/ods to mysql tables.
 981 sub _import_table
 982 {
 983     my ($dbh, $table, $frameworkcode, $dom, $PKArray, $format) = @_;
 984     my %fields2Delete;
 985     my $query;
 986     my @fields;
 987     # Create hash with all elements defined by primary key to know which ones to delete after parsing the spreadsheet
 988     eval {
 989         @fields = @$PKArray;
 990         shift @fields;
 991         $query = 'SELECT ' . join(',', @fields) . ' FROM ' . $table . ' WHERE frameworkcode=?';
 992         my $sth = $dbh->prepare($query);
 993         $sth->execute($frameworkcode);
 994         my $field;
 995         while (my $hashRef = $sth->fetchrow_hashref) {
 996             $field = '';
 997             map { $field .= $hashRef->{$_} . '_'; } @fields;
 998             chop $field;
 999             $fields2Delete{$field} = 1;
1000         }
1001         $sth->finish;
1002     };
1003     my $ok = 0;
1004     if ($format eq 'csv') {
1005         my @fieldsName = ();
1006         eval {
1007             my $query = 'SHOW COLUMNS FROM ' . $table;
1008             my $sth = $dbh->prepare($query);
1009             $sth->execute();
1010             while (my $hashRef = $sth->fetchrow_hashref) {
1011                 push @fieldsName, $hashRef->{Field};
1012             }
1013         };
1014         $ok = _import_table_csv($dbh, $table, $frameworkcode, $dom, $PKArray, \%fields2Delete, \@fieldsName);
1015     } elsif ($format eq 'ods') {
1016         $ok = _import_table_ods($dbh, $table, $frameworkcode, $dom, $PKArray, \%fields2Delete);
1017     } else {
1018         $ok = _import_table_excel($dbh, $table, $frameworkcode, $dom, $PKArray, \%fields2Delete);
1019     }
1020     if ($ok) {
1021         if (($ok = scalar(keys %fields2Delete)) > 0) {
1022             $query = 'DELETE FROM ' . $table . ' WHERE ';
1023             map {$query .= $_ . '=? AND ';} @$PKArray;
1024             $query = substr($query, 0, -4);
1025             my $sth = $dbh->prepare($query);
1026             for (keys %fields2Delete) {
1027                 eval {
1028                     $sth->execute(($frameworkcode, split('_', $_)));
1029                 };
1030             }
1031         }
1032     } else {
1033         $ok = -1;
1034     }
1035     return $ok;
1036 }#_import_table
1037
1038
1039 # Insert/Update the row from the spreadsheet in the database
1040 sub _processRow_DB
1041 {
1042     my ($dbh, $db_scheme, $table, $fields, $dataStr, $updateStr, $dataFields, $dataFieldsHash, $PKArray, $fieldsPK, $fields2Delete) = @_;
1043
1044     my $ok = 0;
1045     my $query;
1046     if ($db_scheme eq 'mysql') {
1047         $query = 'INSERT INTO ' . $table . ' (' . $fields . ') VALUES (' . $dataStr . ') ON DUPLICATE KEY UPDATE ' . $updateStr;
1048     } else {
1049         $query = 'INSERT INTO ' . $table . ' (' . $fields . ') VALUES (' . $dataStr . ')';
1050     }
1051     eval {
1052         my $sth = $dbh->prepare($query);
1053         if ($db_scheme eq 'mysql') {
1054             $sth->execute((@$dataFields, @$dataFields));
1055         } else {
1056             $sth->execute(@$dataFields);
1057         }
1058     };
1059     if ($@) {
1060         unless ($db_scheme eq 'mysql') {
1061             $query = 'UPDATE ' . $table . ' SET ' . $updateStr . ' WHERE ';
1062             map {$query .= $_ . '=? AND ';} @$PKArray;
1063             $query = substr($query, 0, -4);
1064             eval {
1065                 my $sth2 = $dbh->prepare($query);
1066                 my @dataPK = ();
1067                 map {push @dataPK, $dataFieldsHash->{$_};} @$PKArray;
1068                 $sth2->execute((@$dataFields, @dataPK));
1069             };
1070             $ok = 1 unless ($@);
1071         }
1072         $debug and warn "Error _processRows_Table $@\n";
1073     } else {
1074         $ok = 1;
1075     }
1076     if ($ok) {
1077         my $field = '';
1078         map { $field .= $dataFieldsHash->{$_} . '_'; } @$fieldsPK;
1079         chop $field;
1080         delete $fields2Delete->{$field} if (exists($fields2Delete->{$field}));
1081     }
1082     return $ok;
1083 }#_processRow_DB
1084
1085
1086 # Process the rows of a worksheet and insert/update them in a mysql table.
1087 sub _processRows_Table
1088 {
1089     my ($dbh, $frameworkcode, $nodeR, $table, $PKArray, $format, $fields2Delete) = @_;
1090
1091     my $query;
1092     my @fields = ();
1093     my $fields = '';
1094     my $dataStr = '';
1095     my $updateStr = '';
1096     my $j = 0;
1097     my $db_scheme = C4::Context->config("db_scheme");
1098     my $ok = 0;
1099     my @fieldsPK = @$PKArray;
1100     shift @fieldsPK;
1101     while ($nodeR) {
1102         if ($nodeR->nodeType == 1 && (($format && $format eq 'ods' && $nodeR->nodeName =~ /(?:table:)?table-row/) || ($nodeR->nodeName =~ /(?:ss:)?Row/)) && $nodeR->hasChildNodes()) {
1103             if ($j == 0) {
1104                 # Get name columns
1105                 _getFields($nodeR, \@fields, $format);
1106                 return 0 unless _check_validity_worksheet($dbh, $table, $nodeR, \@fields, $format);
1107                 $fields = join(',', @fields);
1108                 $dataStr = '';
1109                 map { $dataStr .= '?,';} @fields;
1110                 chop($dataStr) if ($dataStr);
1111                 $updateStr = '';
1112                 map { $updateStr .= $_ . '=?,';} @fields;
1113                 chop($updateStr) if ($updateStr);
1114             } else {
1115                 # Get data from row
1116                 my ($dataFields, $dataFieldsR) = _getDataFields($frameworkcode, $nodeR, \@fields, $format);
1117                 if (scalar(@fields) == scalar(@$dataFieldsR)) {
1118                     $ok = _processRow_DB($dbh, $db_scheme, $table, $fields, $dataStr, $updateStr, $dataFieldsR, $dataFields, $PKArray, \@fieldsPK, $fields2Delete);
1119                 }
1120             }
1121             $j++;
1122         }
1123         $nodeR = $nodeR->nextSibling;
1124     }
1125     return 1;
1126 }#_processRows_Table
1127
1128
1129
1130
1131 # Import worksheet from the csv file to the mysql table
1132 sub _import_table_csv
1133 {
1134     my ($dbh, $table, $frameworkcode, $dom, $PKArray, $fields2Delete, $fields) = @_;
1135
1136     my $row = '';
1137     my $partialRow = '';
1138     my $numFields = @$fields;
1139     my $fieldsNameRead = 0;
1140     my @arrData;
1141     my ($fieldsStr, $dataStr, $updateStr);
1142     my $db_scheme = C4::Context->config("db_scheme");
1143     my @fieldsPK = @$PKArray;
1144     shift @fieldsPK;
1145     my $ok = 0;
1146     my $numRow = 0;
1147     my $pos = 0;
1148     while (<$dom>) {
1149         $row = $_;
1150         # Check whether the line has an unfinished field, i.e., a field with CR/LF in its data
1151         if ($row =~ /,"[^"]*[\r\n]+$/ || $row =~ /^[^"]+[\r\n]+$/) {
1152             $row =~ s/[\r\n]+$//;
1153             $partialRow .= $row;
1154             next;
1155         }
1156         if ($partialRow) {
1157             $row = $partialRow . $row;
1158             $partialRow = '';
1159         }
1160         # Line OK, process it
1161         if ($row =~ /(?:".*?",?)+/) {
1162             @arrData = split('","', $row);
1163             $arrData[0] = substr($arrData[0], 1) if ($arrData[0] =~ /^"/);
1164             $arrData[$#arrData] =~ s/[\r\n]+$//;
1165             chop $arrData[$#arrData] if ($arrData[$#arrData] =~ /"$/);
1166             if (@arrData) {
1167                 if ($arrData[0] eq '#-#' && $arrData[$#arrData] eq '#-#') {
1168                     # Change of table with separators #-#
1169                     return 1;
1170                 } elsif ($fieldsNameRead && $arrData[0] eq 'tagfield') {
1171                     # Change of table because we begin with field name with former field names read
1172                     seek($dom, $pos, 0);
1173                     return 1;
1174                 }
1175                 if (scalar(@$fields) == scalar(@arrData)) {
1176                     if (!$fieldsNameRead) {
1177                         # New table, we read the field names
1178                         $fieldsNameRead = 1;
1179                         for (my $i=0; $i < @arrData; $i++) {
1180                             if ($arrData[$i] ne $fields->[$i]) {
1181                                 $fieldsNameRead = 0;
1182                                 last;
1183                             }
1184                         }
1185                         if ($fieldsNameRead) {
1186                             $fieldsStr = join(',', @$fields);
1187                             $dataStr = '';
1188                             map { $dataStr .= '?,';} @$fields;
1189                             chop($dataStr) if ($dataStr);
1190                             $updateStr = '';
1191                             map { $updateStr .= $_ . '=?,';} @$fields;
1192                             chop($updateStr) if ($updateStr);
1193                         }
1194                     } else {
1195                         # Read data
1196                         my $j = 0;
1197                         my %dataFields = ();
1198                         for (@arrData) {
1199                             if ($fields->[$j] eq 'frameworkcode' && $_ ne $frameworkcode) {
1200                                 $dataFields{$fields->[$j]} = $frameworkcode;
1201                                 $arrData[$j] = $frameworkcode;
1202                             } else {
1203                                 $dataFields{$fields->[$j]} = $_;
1204                             }
1205                             $j++
1206                         }
1207                         $ok = _processRow_DB($dbh, $db_scheme, $table, $fieldsStr, $dataStr, $updateStr, \@arrData, \%dataFields, $PKArray, \@fieldsPK, $fields2Delete);
1208                     }
1209                 }
1210                 $pos = tell($dom);
1211             }
1212             @arrData = ();
1213         }
1214         $numRow++;
1215     }
1216     return $ok;
1217 }#_import_table_csv
1218
1219
1220 # Import worksheet from the ods content.xml file to the mysql table
1221 sub _import_table_ods
1222 {
1223     my ($dbh, $table, $frameworkcode, $dom, $PKArray, $fields2Delete) = @_;
1224
1225     my $xc = XML::LibXML::XPathContext->new($dom);
1226     $xc->registerNs('xmlns:office','urn:oasis:names:tc:opendocument:xmlns:office:1.0');
1227     $xc->registerNs('xmlns:table','urn:oasis:names:tc:opendocument:xmlns:table:1.0');
1228     $xc->registerNs('xmlns:text','urn:oasis:names:tc:opendocument:xmlns:text:1.0');
1229     my @nodes;
1230     @nodes = $xc->findnodes('//table:table[@table:name="' . $table . '"]');
1231     if (@nodes == 1 && $nodes[0]->hasChildNodes()) {
1232         my $nodeR = $nodes[0]->firstChild;
1233         return _processRows_Table($dbh, $frameworkcode, $nodeR, $table, $PKArray, 'ods', $fields2Delete);
1234     } else {
1235         $debug and warn "Error _import_table_ods there's not worksheet for $table\n";
1236     }
1237     return 0;
1238 }#_import_table_ods
1239
1240
1241 # Import worksheet from the excel-xml file to the mysql table
1242 sub _import_table_excel
1243 {
1244     my ($dbh, $table, $frameworkcode, $dom, $PKArray, $fields2Delete) = @_;
1245
1246     my $xc = XML::LibXML::XPathContext->new($dom);
1247     $xc->registerNs('xmlns','urn:schemas-microsoft-com:office:spreadsheet');
1248     $xc->registerNs('xmlns:ss','urn:schemas-microsoft-com:office:spreadsheet');
1249     $xc->registerNs('xmlns:x','urn:schemas-microsoft-com:office:excel');
1250     my @nodes;
1251     @nodes = $xc->findnodes('//ss:Worksheet[@ss:Name="' . $table . '"]');
1252     if (@nodes > 0) {
1253         for (my $i=0; $i < @nodes; $i++) {
1254             my @nodesT = $nodes[$i]->getElementsByTagNameNS('urn:schemas-microsoft-com:office:spreadsheet', 'Table');
1255             if (@nodesT == 1 && $nodesT[0]->hasChildNodes()) {
1256                 my $nodeR = $nodesT[0]->firstChild;
1257                 return _processRows_Table($dbh, $frameworkcode, $nodeR, $table, $PKArray, undef, $fields2Delete);
1258             }
1259         }
1260     } else {
1261         $debug and warn "Error _import_table_excel there's not worksheet for $table\n";
1262     }
1263     return 0;
1264 }#_import_table_excel
1265
1266
1267 # Get the data from a cell on a ods file through the value attribute or the text node
1268 sub _getDataNodeODS
1269 {
1270     my $node = shift;
1271
1272     my $data;
1273     my $repeated = 0;
1274     if ($node->nodeType == 1 && $node->nodeName =~ /(?:table:)?table-cell/) {
1275         if ($node->hasAttributeNS('urn:oasis:names:tc:opendocument:xmlns:office:1.0', 'value')) {
1276             $data = $node->getAttributeNS('urn:oasis:names:tc:opendocument:xmlns:office:1.0', 'value');
1277         } elsif ($node->hasChildNodes()) {
1278             my @nodes2 = $node->getElementsByTagNameNS('urn:oasis:names:tc:opendocument:xmlns:text:1.0', 'p');
1279             if (@nodes2 == 1 && $nodes2[0]->hasChildNodes()) {
1280                 $data = $nodes2[0]->firstChild->nodeValue;
1281             }
1282         }
1283         if ($node->hasAttributeNS('urn:oasis:names:tc:opendocument:xmlns:table:1.0', 'number-columns-repeated')) {
1284             $repeated = $node->getAttributeNS('urn:oasis:names:tc:opendocument:xmlns:table:1.0', 'number-columns-repeated');
1285         }
1286     }
1287     return ($data, $repeated);
1288 }#_getDataNodeODS
1289
1290
1291 # Get the data from a row of a spreadsheet
1292 sub _getDataFields
1293 {
1294     my ($frameworkcode, $node, $fields, $format) = @_;
1295
1296     my $dataFields = {};
1297     my @dataFieldsA = ();
1298     if ($node && $node->hasChildNodes()) {
1299         my $node2 = $node->firstChild;
1300         my ($data, $repeated);
1301         my $i = 0;
1302         my $ok = 0;
1303         $repeated = 0;
1304         while ($node2) {
1305             if ($format && $format eq 'ods') {
1306                 ($data, $repeated) = _getDataNodeODS($node2) if ($repeated <= 0);
1307                 $repeated--;
1308                 $ok = 1 if (defined($data));
1309             } else {
1310                 if ($node2->nodeType == 1 && $node2->nodeName  =~ /(?:ss:)?Cell/) {
1311                     my @nodes3 = $node2->getElementsByTagNameNS('urn:schemas-microsoft-com:office:spreadsheet', 'Data');
1312                     if (@nodes3 == 1 && $nodes3[0]->hasChildNodes()) {
1313                         $data = $nodes3[0]->firstChild->nodeValue;
1314                         $ok = 1;
1315                     }
1316                 }
1317             }
1318             if ($ok) {
1319                 $data = '' if ($data eq '#');
1320                 $data = $frameworkcode if ($fields->[$i] eq 'frameworkcode');
1321                 $dataFields->{$fields->[$i]} = $data;
1322                 push @dataFieldsA, $data;
1323                 $i++;
1324             }
1325             $ok = 0;
1326             $node2 = $node2->nextSibling if ($repeated <= 0);
1327         }
1328     }
1329     return ($dataFields, \@dataFieldsA);
1330 }#_getDataFields
1331
1332
1333 # Get the data from the first row to know the column names
1334 sub _getFields
1335 {
1336     my ($node, $fields, $format) = @_;
1337
1338     if ($node && $node->hasChildNodes()) {
1339         my $node2 = $node->firstChild;
1340         my ($data, $repeated);
1341         while ($node2) {
1342             if ($format && $format eq 'ods') {
1343                 ($data, $repeated) = _getDataNodeODS($node2);
1344                 push @$fields, $data if (defined($data));
1345             } else {
1346                 if ($node2->nodeType == 1 && $node2->nodeName =~ /(?:ss:)?Cell/) {
1347                     my @nodes3 = $node2->getElementsByTagNameNS('urn:schemas-microsoft-com:office:spreadsheet', 'Data');
1348                     if (@nodes3 == 1 && $nodes3[0]->hasChildNodes()) {
1349                         $data = $nodes3[0]->firstChild->nodeValue;
1350                         push @$fields, $data;
1351                     }
1352                 }
1353             }
1354             $node2 = $node2->nextSibling;
1355         }
1356     }
1357 }#_getFields
1358
1359
1360
1361
1362 1;
1363 __END__
1364
1365 =head1 AUTHOR
1366
1367 Koha Development Team <http://koha-community.org/>
1368
1369 =cut
1370
1371