X-Git-Url: http://koha-dev.rot13.org:8081/gitweb/?a=blobdiff_plain;f=C4%2FImportExportFramework.pm;h=3d44d46ec3932a6432b91f37f942bd1c9454ccf1;hb=abbcf684c272239d3bf891160e2c59f3855c195d;hp=b81b393b7d2d0b36e26cc1e8bd674123a9183911;hpb=b93e6df3a1b7051bc92220cf6bd829c2229bfa70;p=koha-ffzg.git diff --git a/C4/ImportExportFramework.pm b/C4/ImportExportFramework.pm index b81b393b7d..3d44d46ec3 100644 --- a/C4/ImportExportFramework.pm +++ b/C4/ImportExportFramework.pm @@ -4,40 +4,39 @@ package C4::ImportExportFramework; # # This file is part of Koha. # -# Koha is free software; you can redistribute it and/or modify it under the -# terms of the GNU General Public License as published by the Free Software -# Foundation; either version 2 of the License, or (at your option) any later -# version. +# Koha is free software; you can redistribute it and/or modify it +# under the terms of the GNU General Public License as published by +# the Free Software Foundation; either version 3 of the License, or +# (at your option) any later version. # -# Koha is distributed in the hope that it will be useful, but WITHOUT ANY -# WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR -# A PARTICULAR PURPOSE. See the GNU General Public License for more details. +# Koha is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the +# GNU General Public License for more details. # -# You should have received a copy of the GNU General Public License along -# with Koha; if not, write to the Free Software Foundation, Inc., -# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. +# You should have received a copy of the GNU General Public License +# along with Koha; if not, see . use strict; use warnings; use XML::LibXML; use XML::LibXML::XPathContext; -use Digest::MD5 qw(md5_base64); -use POSIX qw(strftime); +use Digest::MD5; +use POSIX qw( strftime ); +use Text::CSV_XS; +use List::MoreUtils qw( indexes ); use C4::Context; -use C4::Debug; - - -use vars qw($VERSION @ISA @EXPORT @EXPORT_OK %EXPORT_TAGS); +use Koha::Logger; +our (@ISA, @EXPORT_OK); BEGIN { - $VERSION = 3.07.00.049; # set version for version checking require Exporter; @ISA = qw(Exporter); - @EXPORT = qw( - &ExportFramework - &ImportFramework - &createODS + @EXPORT_OK = qw( + ExportFramework + ImportFramework + createODS ); } @@ -194,7 +193,7 @@ Functions for handling import/export. =head2 ExportFramework -Export all the information of a Framework to an excel "xml" file or OpenDocument SpreadSheet "ods" file. +Export all information of a bibliographic or authority MARC framework to an Excel "xml" file, comma separated values "csv" or OpenDocument SpreadSheet "ods". return : succes @@ -203,7 +202,7 @@ succes sub ExportFramework { - my ($frameworkcode, $xmlStrRef, $mode) = @_; + my ($frameworkcode, $xmlStrRef, $mode, $frameworktype) = @_; my $dbh = C4::Context->dbh; if ($dbh) { @@ -225,13 +224,20 @@ sub ExportFramework } }; if ($@) { - $debug and warn "Error ExportFramework $@\n"; + Koha::Logger->get->warn("Error ExportFramework $@"); return 0; } } - if (_export_table('marc_tag_structure', $dbh, ($mode eq 'csv' || $mode eq 'sql')?$xmlStrRef:$dom, ($mode eq 'ods')?$elementSS:$root, $frameworkcode, $mode)) { - if (_export_table('marc_subfield_structure', $dbh, ($mode eq 'csv' || $mode eq 'sql')?$xmlStrRef:$dom, ($mode eq 'ods')?$elementSS:$root, $frameworkcode, $mode)) { + my $table = 'marc_tag_structure'; + my $subtable = 'marc_subfield_structure'; + if ($frameworktype eq "authority") { + $table = 'auth_tag_structure'; + $subtable = 'auth_subfield_structure'; + } + + if (_export_table($table, $dbh, ($mode eq 'csv')?$xmlStrRef:$dom, ($mode eq 'ods')?$elementSS:$root, $frameworkcode, $mode, $frameworktype)) { + if (_export_table($subtable, $dbh, ($mode eq 'csv')?$xmlStrRef:$dom, ($mode eq 'ods')?$elementSS:$root, $frameworkcode, $mode, $frameworktype)) { $$xmlStrRef = $dom->toString(1) if ($mode eq 'ods' || $mode eq 'excel'); return 1; } @@ -246,62 +252,20 @@ sub ExportFramework # Export all the data from a mysql table to an spreadsheet. sub _export_table { - my ($table, $dbh, $dom, $root, $frameworkcode, $mode) = @_; + my ($table, $dbh, $dom, $root, $frameworkcode, $mode, $frameworktype) = @_; if ($mode eq 'csv') { - _export_table_csv($table, $dbh, $dom, $root, $frameworkcode); - } elsif ($mode eq 'sql') { - _export_table_sql($table, $dbh, $dom, $root, $frameworkcode); + _export_table_csv($table, $dbh, $dom, $root, $frameworkcode, $frameworktype); } elsif ($mode eq 'ods') { - _export_table_ods($table, $dbh, $dom, $root, $frameworkcode); + _export_table_ods($table, $dbh, $dom, $root, $frameworkcode, $frameworktype); } else { - _export_table_excel($table, $dbh, $dom, $root, $frameworkcode); + _export_table_excel($table, $dbh, $dom, $root, $frameworkcode, $frameworktype); } } - -# Export the mysql table to an sql file -sub _export_table_sql -{ - my ($table, $dbh, $strSQL, $root, $frameworkcode) = @_; - - eval { - # First row with the name of the columns - my $query = 'SHOW COLUMNS FROM ' . $table; - my $sth = $dbh->prepare($query); - $sth->execute(); - my @fields = (); - while (my $hashRef = $sth->fetchrow_hashref) { - push @fields, $hashRef->{Field}; - } - my $fields = join(',', @fields); - $$strSQL .= 'DELETE FROM ' . $table . ' WHERE frameworkcode=' . $dbh->quote($frameworkcode) . ';'; - $$strSQL .= chr(10); - # Populate rows with the data from mysql - $query = 'SELECT * FROM ' . $table . ' WHERE frameworkcode=?'; - $sth = $dbh->prepare($query); - $sth->execute($frameworkcode); - while (my $hashRef = $sth->fetchrow_hashref) { - $$strSQL .= 'INSERT INTO ' . $table . ' (' . $fields . ') VALUES ('; - for (@fields) { - $$strSQL .= $dbh->quote($hashRef->{$_}) . ','; - } - chop $$strSQL; - $$strSQL .= ');' . chr(10); - } - $$strSQL .= chr(10) . chr(10); - }; - if ($@) { - $debug and warn "Error _export_table_sql $@\n"; - return 0; - } - return 1; -}#_export_table_sql - - # Export the mysql table to an csv file sub _export_table_csv { - my ($table, $dbh, $strCSV, $root, $frameworkcode) = @_; + my ($table, $dbh, $strCSV, $root, $frameworkcode, $frameworktype) = @_; eval { # First row with the name of the columns @@ -317,13 +281,17 @@ sub _export_table_csv $$strCSV .= chr(10); # Populate rows with the data from mysql $query = 'SELECT * FROM ' . $table . ' WHERE frameworkcode=?'; + if ($frameworktype eq "authority") { + $query = 'SELECT * FROM ' . $table . ' WHERE authtypecode=?'; + } $sth = $dbh->prepare($query); $sth->execute($frameworkcode); my $data; while (my $hashRef = $sth->fetchrow_hashref) { - for (@fields) { - $hashRef->{$_} =~ s/[\r\n]//g; - $$strCSV .= '"' . $hashRef->{$_} . '",'; + for my $field (@fields) { + my $value = $hashRef->{$field} // q||; + $value =~ s/[\r\n]//g; + $$strCSV .= '"' . $value . '",'; } chop $$strCSV; $$strCSV .= chr(10); @@ -338,7 +306,7 @@ sub _export_table_csv $$strCSV .= chr(10); }; if ($@) { - $debug and warn "Error _export_table_csv $@\n"; + Koha::Logger->get->warn("Error _export_table_csv $@"); return 0; } return 1; @@ -348,7 +316,7 @@ sub _export_table_csv # Export the mysql table to an ods file sub _export_table_ods { - my ($table, $dbh, $dom, $root, $frameworkcode) = @_; + my ($table, $dbh, $dom, $root, $frameworkcode, $frameworktype) = @_; eval { my $elementTable = $dom->createElement('table:table'); @@ -377,6 +345,9 @@ sub _export_table_ods } # Populate rows with the data from mysql $query = 'SELECT * FROM ' . $table . ' WHERE frameworkcode=?'; + if ($frameworktype eq "authority") { + $query = 'SELECT * FROM ' . $table . ' WHERE authtypecode=?'; + } $sth = $dbh->prepare($query); $sth->execute($frameworkcode); my $data; @@ -387,6 +358,8 @@ sub _export_table_ods $data = $hashRef->{$_->{name}}; if ($_->{type} eq 'float' && !defined($data)) { $data = '0'; + } elsif ($_->{type} eq 'string' && !defined($data)) { + $data = q{}; } elsif ($_->{type} eq 'string' && (!$data && $data ne '0')) { $data = '#'; } @@ -402,7 +375,7 @@ sub _export_table_ods } }; if ($@) { - $debug and warn "Error _export_table_ods $@\n"; + Koha::Logger->get->warn("Error _export_table_ods $@"); return 0; } return 1; @@ -412,7 +385,7 @@ sub _export_table_ods # Export the mysql table to an excel-xml (openoffice/libreoffice compatible) file sub _export_table_excel { - my ($table, $dbh, $dom, $root, $frameworkcode) = @_; + my ($table, $dbh, $dom, $root, $frameworkcode, $frameworktype) = @_; eval { my $elementWS = $dom->createElement('Worksheet'); @@ -442,6 +415,9 @@ sub _export_table_excel } # Populate rows with the data from mysql $query = 'SELECT * FROM ' . $table . ' WHERE frameworkcode=?'; + if ($frameworktype eq "authority") { + $query = 'SELECT * FROM ' . $table . ' WHERE authtypecode=?'; + } $sth = $dbh->prepare($query); $sth->execute($frameworkcode); my $data; @@ -457,6 +433,8 @@ sub _export_table_excel $data = $hashRef->{$_->{name}}; if ($_->{type} eq 'Number' && !defined($data)) { $data = '0'; + } elsif ($_->{type} eq 'String' && !defined($data)) { + $data = q{}; } elsif ($_->{type} eq 'String' && (!$data && $data ne '0')) { $data = '#'; } @@ -465,18 +443,12 @@ sub _export_table_excel } }; if ($@) { - $debug and warn "Error _export_table_excel $@\n"; + Koha::Logger->get->warn("Error _export_table_excel $@"); return 0; } return 1; }#_export_table_excel - - - - - - # Format chars problematics to a correct format for xml. sub _parseContent2Xml { @@ -515,7 +487,7 @@ sub _createTmpDir mkdir $tempdir; }; if ($@) { - return undef; + return; } else { return $tempdir; } @@ -547,27 +519,28 @@ sub createODS $tempdir = _createTmpDir($tmp); } if ($tempdir) { + my $fh; # populate tempdir directory with the ods elements eval { - if (open(OUT, "> $tempdir/content.xml")) { - print OUT $strContent; - close(OUT); + if (open($fh, '>', "$tempdir/content.xml")) { + print {$fh} $strContent; + close($fh); } - if (open(OUT, "> $tempdir/mimetype")) { - print OUT 'application/vnd.oasis.opendocument.spreadsheet'; - close(OUT); + if (open($fh, '>', "$tempdir/mimetype")) { + print {$fh} 'application/vnd.oasis.opendocument.spreadsheet'; + close($fh); } - if (open(OUT, "> $tempdir/meta.xml")) { - print OUT _getMeta($lang); - close(OUT); + if (open($fh, '>', "$tempdir/meta.xml")) { + print {$fh} _getMeta($lang); + close($fh); } - if (open(OUT, "> $tempdir/styles.xml")) { - print OUT ODS_STYLES_STR; - close(OUT); + if (open($fh, '>', "$tempdir/styles.xml")) { + print {$fh} ODS_STYLES_STR; + close($fh); } - if (open(OUT, "> $tempdir/settings.xml")) { - print OUT ODS_SETTINGS_STR; - close(OUT); + if (open($fh, '>', "$tempdir/settings.xml")) { + print {$fh} ODS_SETTINGS_STR; + close($fh); } mkdir($tempdir.'/META-INF/'); mkdir($tempdir.'/Configurations2/'); @@ -579,13 +552,14 @@ sub createODS mkdir($tempdir.'/Configurations2/menubar/'); mkdir($tempdir.'/Configurations2/progressbar/'); mkdir($tempdir.'/Configurations2/toolbar/'); - if (open(OUT, "> $tempdir/META-INF/manifest.xml")) { - print OUT ODS_MANIFEST_STR; - close(OUT); + + if (open($fh, '>', "$tempdir/META-INF/manifest.xml")) { + print {$fh} ODS_MANIFEST_STR; + close($fh); } }; if ($@) { - $debug and warn "Error createODS $@\n"; + Koha::Logger->get->warn("Error createODS $@"); } else { # create ods file from tempdir directory eval { @@ -604,13 +578,13 @@ sub createODS my $ok = 0; # read ods file and return as a string if (-f "$tempdir/new.ods") { - if (open (MYFILE, "$tempdir/new.ods")) { - binmode MYFILE; + if (open ($fh, '<', "$tempdir/new.ods")) { + binmode $fh; my $buffer; - while (read (MYFILE, $buffer, 65536)) { + while (read ($fh, $buffer, 65536)) { $$strODSRef .= $buffer; } - close(MYFILE); + close($fh); $ok = 1; } } @@ -659,7 +633,7 @@ sub _getMeta =head2 ImportFramework -Import all the information of a Framework from a excel-xml/ods file. +Import all the information of a MARC or Authority Type Framework from a excel-xml/ods file. return : success @@ -668,14 +642,15 @@ success sub ImportFramework { - my ($filename, $frameworkcode, $deleteFilename) = @_; + my ($filename, $frameworkcode, $deleteFilename, $frameworktype) = @_; my $tempdir; my $ok = -1; my $dbh = C4::Context->dbh; + $frameworktype ||= ''; if (-r $filename && $dbh) { my $extension = ''; - if ($filename =~ /\.(csv|ods|xml|sql)$/i) { + if ($filename =~ /\.(csv|ods|xml)$/i) { $extension = lc($1); } else { unlink ($filename) if ($deleteFilename); # remove temporary file @@ -698,38 +673,50 @@ sub ImportFramework # They are text files, so open it to read open($dom, '<', $filename); } + + my $table = 'marc_tag_structure'; + my $subtable = 'marc_subfield_structure'; + if ($frameworktype eq "authority") { + $table = 'auth_tag_structure'; + $subtable = 'auth_subfield_structure'; + } + if ($dom) { - # For sql we execute the line - if ($extension eq 'sql') { - _parseSQLLine($dbh, $dom, $frameworkcode); - $ok = 0; + # Process both tables + my $numDeleted = 0; + my $numDeletedAux = 0; + if ($frameworktype eq "authority"){ + if (($numDeletedAux = _import_table($dbh, $table, $frameworkcode, $dom, ['authtypecode', 'tagfield'], $extension, $frameworktype)) >= 0) { + $numDeleted += $numDeletedAux if ($numDeletedAux > 0); + if (($numDeletedAux = _import_table($dbh, $subtable, $frameworkcode, $dom, ['authtypecode', 'tagfield', 'tagsubfield'], $extension, $frameworktype)) >= 0) { + $numDeleted += $numDeletedAux if ($numDeletedAux > 0); + $ok = ($numDeleted > 0)?$numDeleted:0; + } + } } else { - # Process both tables - my $numDeleted = 0; - my $numDeletedAux = 0; - if (($numDeletedAux = _import_table($dbh, 'marc_tag_structure', $frameworkcode, $dom, ['frameworkcode', 'tagfield'], $extension)) >= 0) { + if (($numDeletedAux = _import_table($dbh, $table, $frameworkcode, $dom, ['frameworkcode', 'tagfield'], $extension, $frameworktype)) >= 0) { $numDeleted += $numDeletedAux if ($numDeletedAux > 0); - if (($numDeletedAux = _import_table($dbh, 'marc_subfield_structure', $frameworkcode, $dom, ['frameworkcode', 'tagfield', 'tagsubfield'], $extension)) >= 0) { + if (($numDeletedAux = _import_table($dbh, $subtable, $frameworkcode, $dom, ['frameworkcode', 'tagfield', 'tagsubfield'], $extension, $frameworktype)) >= 0) { $numDeleted += $numDeletedAux if ($numDeletedAux > 0); $ok = ($numDeleted > 0)?$numDeleted:0; } } } } else { - $debug and warn "Error ImportFramework couldn't create dom\n"; + Koha::Logger->get->warn("Error ImportFramework couldn't create dom"); } }; if ($@) { - $debug and warn "Error ImportFramework $@\n"; + Koha::Logger->get->warn("Error ImportFramework $@"); } else { - if ($extension eq 'sql' || $extension eq 'csv') { + if ($extension eq 'csv') { close($dom) if ($dom); } } } unlink ($filename) if ($deleteFilename); # remove temporary file } else { - $debug and warn "Error ImportFramework no conex to database or not readeable $filename\n"; + Koha::Logger->get->warn("Error ImportFramework no conex to database or not readeable $filename"); } if ($deleteFilename && $tempdir && -d $tempdir && -w $tempdir) { eval { @@ -744,155 +731,6 @@ sub ImportFramework return $ok; }#ImportFramework - -# Parse the sql statement to see if the frameworkcode is correct -# We're checking only the delete and insert SQL commands generated in the export process -sub _parseSQLLine -{ - my ($dbh, $dom, $frameworkcode) = @_; - - my $parser; - eval { - require SQL::Statement; - $parser = SQL::Parser->new('AnyData'); - $parser->{RaiseError}=1; - $parser->{PrintError}=0; - }; - my $literalEscape = (C4::Context->config("db_scheme") eq 'mysql')?'\\':'\''; - my $line; - my $numLines = 0; - while (<$dom>) { - s/[\r\n]+$//; - $line = $_; - # we don't want to execute any sql statement, only the ones dealing with frameworks - next unless ($line =~ /^\s*(?i:DELETE\s+FROM|INSERT\s+INTO)\s+(?:marc_tag_structure|marc_subfield_structure)/); - $numLines++; - # We check if the frameworkcode is the same, if not we change it - unless ($line =~ /'$frameworkcode'/) { - my $error = 0; - if ($parser) { - eval { - $line = substr($line, 0 ,-1) if ($line =~ /;$/); - my $stmt = SQL::Statement->new($line, $parser); - my $where = $stmt->where(); - if ($where && $where->op() eq '=' && $line =~ /^\s*DELETE/) { - $line =~ s/frameworkcode='.*?'/frameworkcode='$frameworkcode';/ unless ($_ =~ /frameworkcode='$frameworkcode'/); - } else { - my @arrFields; - my @arrValues; - my $table; - # Due to lacking of backward compatibility - if ($parser->VERSION < 1.30) { - $table = lc($stmt->tables(0)->name()); - @arrFields = map{lc($_->name)} $stmt->columns; - @arrValues = $stmt->row_values(); - } else { - $table = $stmt->tables(0)->name(); - @arrValues = $stmt->row_values(0); - my @aux = $stmt->column_defs(); - for (@{$aux[0]}) { - push @arrFields, $_->{value}; - } - } - if (scalar(@arrFields) == scalar(@arrValues)) { - my $j = 0; - my $modified = 0; - for (@arrFields) { - if ($_ eq 'frameworkcode' && $arrValues[$j] ne $frameworkcode) { - $arrValues[$j] = $dbh->quote($frameworkcode); - $modified = 1; - } else { - $arrValues[$j] = $dbh->quote($arrValues[$j]); - } - $j++; - } - $line = 'INSERT INTO ' . $table . ' (' . join(',', @arrFields) . ') VALUES (' . join(',', @arrValues) . ');' if ($modified); - } - } - }; - $error = 1 if ($@); - } else { - $error = 1; - } - if ($error) { - $line .= ';' unless ($line =~ /;$/); - if ($line =~ /^\s*DELETE/) { - $line =~ s/frameworkcode='.*?'/frameworkcode='$frameworkcode'/ unless ($_ =~ /frameworkcode='$frameworkcode'/); - } elsif ($line =~ /^\s*INSERT\s+INTO\s+(.*?)\s+\((.*?frameworkcode.*?)\)\s+VALUES\s+\((.+)\)\s*;\s*$/) { - my $table = $1; - my $fields = $2; - my $values = $3; - my @arrFields = split(/\s*,\s*/, $fields); - my @arrValues; - if ($values) { - _parseSQLInsertValues($values, $literalEscape, \@arrValues); - } - if (scalar(@arrFields) == scalar(@arrValues)) { - my $modified = 0; - for (my $i=0; $i < @arrFields; $i++) { - if ($arrFields[$i] eq 'frameworkcode' && $arrValues[$i]->{value} ne $frameworkcode) { - $arrValues[$i]->{value} = $dbh->quote($frameworkcode); - $modified = 1; - } elsif ($arrValues[$i]->{literal}) { - $arrValues[$i]->{value} = $dbh->quote($arrValues[$i]->{value}); - } - } - if ($modified) { - $line = "INSERT INTO $table ($fields) VALUES (" . join(',', map {$_->{value}} @arrValues) . ');'; - } - } - } - } - } - eval { - $dbh->do($line); - }; - } -}#_parseSQLLine - - -# Simple sub to get the values from the insert sentence -sub _parseSQLInsertValues -{ - my ($values, $literalEscape, $arrValues) = @_; - - my ($posBegin, $posLiteral, $currentPos, $lengthValues, $currentChar); - $lengthValues = length($values); - $currentPos = 0; - while ($currentPos < $lengthValues) { - $currentChar = substr($values, $currentPos++, 1); - next if ($currentChar =~ /^\s$/); - next if ($posBegin && $currentChar !~ /^[,']$/); - unless ($posBegin) { - if ($currentChar eq '\'') { - $posBegin = $currentPos; - $posLiteral = $posBegin; - } else { - $posBegin = $currentPos -1; - } - } else { - if ($currentChar eq ',') { - unless ($posLiteral) { - push @$arrValues, {literal => 0, value => substr($values, $posBegin, $currentPos -(1 + $posBegin))}; - $posBegin = undef; - } - } elsif ($currentChar eq '\'' && $posLiteral) { - next if ($literalEscape eq '\\' && substr($values, $currentPos -2, 1) eq $literalEscape); - if ($literalEscape eq '\'' && substr($values, $currentPos, 1) eq $literalEscape) { - $currentPos++; - next; - } - push @$arrValues, {literal => 1 , value => substr($values, $posBegin, $currentPos -( 1 + $posBegin))}; - $currentPos++ if (substr($values, $currentPos, 1) eq ','); - $posBegin = undef; - $posLiteral = undef; - } # We shouldn't get to here if the sql sentence is correct - } - } - push @$arrValues, {literal => ($posLiteral)?1:0, value => substr($values, $posBegin, $currentPos - $posBegin)} if ($posBegin); -}#_parseSQLInsertValues - - # Open (uncompress) ods file and return the content.xml file sub _openODS { @@ -980,15 +818,19 @@ sub _check_validity_worksheet # Import the data from an excel-xml/ods to mysql tables. sub _import_table { - my ($dbh, $table, $frameworkcode, $dom, $PKArray, $format) = @_; + my ($dbh, $table, $frameworkcode, $dom, $PKArray, $format, $frameworktype) = @_; my %fields2Delete; my $query; my @fields; + $frameworktype ||= ''; # Create hash with all elements defined by primary key to know which ones to delete after parsing the spreadsheet eval { @fields = @$PKArray; shift @fields; $query = 'SELECT ' . join(',', @fields) . ' FROM ' . $table . ' WHERE frameworkcode=?'; + if ($frameworktype eq "authority") { + $query = 'SELECT ' . join(',', @fields) . ' FROM ' . $table . ' WHERE authtypecode=?'; + } my $sth = $dbh->prepare($query); $sth->execute($frameworkcode); my $field; @@ -1011,11 +853,11 @@ sub _import_table push @fieldsName, $hashRef->{Field}; } }; - $ok = _import_table_csv($dbh, $table, $frameworkcode, $dom, $PKArray, \%fields2Delete, \@fieldsName); + $ok = _import_table_csv($dbh, $table, $frameworkcode, $dom, $PKArray, \%fields2Delete, \@fieldsName, $frameworktype); } elsif ($format eq 'ods') { - $ok = _import_table_ods($dbh, $table, $frameworkcode, $dom, $PKArray, \%fields2Delete); + $ok = _import_table_ods($dbh, $table, $frameworkcode, $dom, $PKArray, \%fields2Delete, $frameworktype); } else { - $ok = _import_table_excel($dbh, $table, $frameworkcode, $dom, $PKArray, \%fields2Delete); + $ok = _import_table_excel($dbh, $table, $frameworkcode, $dom, $PKArray, \%fields2Delete, $frameworktype); } if ($ok) { if (($ok = scalar(keys %fields2Delete)) > 0) { @@ -1039,37 +881,17 @@ sub _import_table # Insert/Update the row from the spreadsheet in the database sub _processRow_DB { - my ($dbh, $db_scheme, $table, $fields, $dataStr, $updateStr, $dataFields, $dataFieldsHash, $PKArray, $fieldsPK, $fields2Delete) = @_; + my ($dbh, $table, $fields, $dataStr, $updateStr, $dataFields, $dataFieldsHash, $PKArray, $fieldsPK, $fields2Delete) = @_; my $ok = 0; my $query; - if ($db_scheme eq 'mysql') { - $query = 'INSERT INTO ' . $table . ' (' . $fields . ') VALUES (' . $dataStr . ') ON DUPLICATE KEY UPDATE ' . $updateStr; - } else { - $query = 'INSERT INTO ' . $table . ' (' . $fields . ') VALUES (' . $dataStr . ')'; - } + $query = 'INSERT INTO ' . $table . ' (' . $fields . ') VALUES (' . $dataStr . ') ON DUPLICATE KEY UPDATE ' . $updateStr; eval { my $sth = $dbh->prepare($query); - if ($db_scheme eq 'mysql') { - $sth->execute((@$dataFields, @$dataFields)); - } else { - $sth->execute(@$dataFields); - } + $sth->execute((@$dataFields, @$dataFields)); }; if ($@) { - unless ($db_scheme eq 'mysql') { - $query = 'UPDATE ' . $table . ' SET ' . $updateStr . ' WHERE '; - map {$query .= $_ . '=? AND ';} @$PKArray; - $query = substr($query, 0, -4); - eval { - my $sth2 = $dbh->prepare($query); - my @dataPK = (); - map {push @dataPK, $dataFieldsHash->{$_};} @$PKArray; - $sth2->execute((@$dataFields, @dataPK)); - }; - $ok = 1 unless ($@); - } - $debug and warn "Error _processRows_Table $@\n"; + Koha::Logger->get->warn("Error _processRow_DB $@"); } else { $ok = 1; } @@ -1086,7 +908,7 @@ sub _processRow_DB # Process the rows of a worksheet and insert/update them in a mysql table. sub _processRows_Table { - my ($dbh, $frameworkcode, $nodeR, $table, $PKArray, $format, $fields2Delete) = @_; + my ($dbh, $frameworkcode, $nodeR, $table, $PKArray, $format, $fields2Delete, $frameworktype) = @_; my $query; my @fields = (); @@ -1094,7 +916,6 @@ sub _processRows_Table my $dataStr = ''; my $updateStr = ''; my $j = 0; - my $db_scheme = C4::Context->config("db_scheme"); my $ok = 0; my @fieldsPK = @$PKArray; shift @fieldsPK; @@ -1113,9 +934,11 @@ sub _processRows_Table chop($updateStr) if ($updateStr); } else { # Get data from row - my ($dataFields, $dataFieldsR) = _getDataFields($frameworkcode, $nodeR, \@fields, $format); + my ($dataFields, $dataFieldsR) = _getDataFields($frameworkcode, $nodeR, \@fields, $format, $frameworktype); if (scalar(@fields) == scalar(@$dataFieldsR)) { - $ok = _processRow_DB($dbh, $db_scheme, $table, $fields, $dataStr, $updateStr, $dataFieldsR, $dataFields, $PKArray, \@fieldsPK, $fields2Delete); + $ok = _processRow_DB($dbh, $table, $fields, $dataStr, $updateStr, $dataFieldsR, $dataFields, $PKArray, \@fieldsPK, $fields2Delete); + } else { + warn "$j don't match number of fields " . scalar(@fields) . ' vs ' . scalar(@$dataFieldsR) . "($dataStr)"; } } $j++; @@ -1131,87 +954,86 @@ sub _processRows_Table # Import worksheet from the csv file to the mysql table sub _import_table_csv { - my ($dbh, $table, $frameworkcode, $dom, $PKArray, $fields2Delete, $fields) = @_; - + my ($dbh, $table, $frameworkcode, $dom, $PKArray, $fields2Delete, $fields, $frameworktype) = @_; my $row = ''; my $partialRow = ''; my $numFields = @$fields; my $fieldsNameRead = 0; my @arrData; - my ($fieldsStr, $dataStr, $updateStr); - my $db_scheme = C4::Context->config("db_scheme"); + my ($fieldsStr, $dataStr, $updateStr, @empty_indexes); my @fieldsPK = @$PKArray; shift @fieldsPK; my $ok = 0; - my $numRow = 0; my $pos = 0; - while (<$dom>) { - $row = $_; - # Check whether the line has an unfinished field, i.e., a field with CR/LF in its data - if ($row =~ /,"[^"]*[\r\n]+$/ || $row =~ /^[^"]+[\r\n]+$/) { - $row =~ s/[\r\n]+$//; - $partialRow .= $row; - next; - } - if ($partialRow) { - $row = $partialRow . $row; - $partialRow = ''; - } - # Line OK, process it - if ($row =~ /(?:".*?",?)+/) { - @arrData = split('","', $row); - $arrData[0] = substr($arrData[0], 1) if ($arrData[0] =~ /^"/); - $arrData[$#arrData] =~ s/[\r\n]+$//; - chop $arrData[$#arrData] if ($arrData[$#arrData] =~ /"$/); - if (@arrData) { - if ($arrData[0] eq '#-#' && $arrData[$#arrData] eq '#-#') { - # Change of table with separators #-# - return 1; - } elsif ($fieldsNameRead && $arrData[0] eq 'tagfield') { - # Change of table because we begin with field name with former field names read - seek($dom, $pos, 0); - return 1; - } - if (scalar(@$fields) == scalar(@arrData)) { - if (!$fieldsNameRead) { - # New table, we read the field names - $fieldsNameRead = 1; - for (my $i=0; $i < @arrData; $i++) { - if ($arrData[$i] ne $fields->[$i]) { - $fieldsNameRead = 0; - last; - } - } - if ($fieldsNameRead) { - $fieldsStr = join(',', @$fields); - $dataStr = ''; - map { $dataStr .= '?,';} @$fields; - chop($dataStr) if ($dataStr); - $updateStr = ''; - map { $updateStr .= $_ . '=?,';} @$fields; - chop($updateStr) if ($updateStr); + my $csv = Text::CSV_XS->new ({ binary => 1 }); + while ( my $row = $csv->getline($dom) ) { + my @fields = @$row; + @arrData = @fields; + next if scalar @arrData == grep { $_ eq '' } @arrData; # Emtpy lines + #$arrData[0] = substr($arrData[0], 1) if ($arrData[0] =~ /^"/); + #$arrData[$#arrData] =~ s/[\r\n]+$//; + #chop $arrData[$#arrData] if ($arrData[$#arrData] =~ /"$/); + if (@arrData) { + if ($arrData[0] eq '#-#' && $arrData[$#arrData] eq '#-#') { + # Change of table with separators #-# + return 1; + } elsif ($fieldsNameRead && $arrData[0] eq 'tagfield') { + # Change of table because we begin with field name with former field names read + seek($dom, $pos, 0); + return 1; + } + if (!$fieldsNameRead) { + # New table, we read the field names + $fieldsNameRead = 1; + $fields = [@arrData]; + my $non_empty_fields = [ grep { $_ ne '' } @$fields ]; + @empty_indexes = indexes { $_ eq '' } @$fields; + $fieldsStr = join(',', @$non_empty_fields); + $dataStr = ''; + map { $dataStr .= '?,';} @$non_empty_fields; + chop($dataStr) if ($dataStr); + $updateStr = ''; + map { $updateStr .= $_ . '=?,';} @$non_empty_fields; + chop($updateStr) if ($updateStr); + } else { + # Read data + my $j = 0; + my %dataFields = (); + my @values; + for my $value (@arrData) { + if ( grep { $_ == $j } @empty_indexes ) { + # empty field + } elsif ($frameworktype eq "authority"){ + if ($fields->[$j] eq 'authtypecode' && $value ne $frameworkcode) { + $dataFields{$fields->[$j]} = $frameworkcode; + push @values, $frameworkcode; + } elsif ($fields->[$j] eq 'isurl' && defined $value && $value eq q{}) { + $dataFields{$fields->[$j]} = undef; + push @values, undef; + } else { + $dataFields{$fields->[$j]} = $value; + push @values, $value; } + $j++ } else { - # Read data - my $j = 0; - my %dataFields = (); - for (@arrData) { - if ($fields->[$j] eq 'frameworkcode' && $_ ne $frameworkcode) { - $dataFields{$fields->[$j]} = $frameworkcode; - $arrData[$j] = $frameworkcode; - } else { - $dataFields{$fields->[$j]} = $_; - } - $j++ + if ($fields->[$j] eq 'frameworkcode' && $value ne $frameworkcode) { + $dataFields{$fields->[$j]} = $frameworkcode; + push @values, $frameworkcode; + } elsif ($fields->[$j] eq 'isurl' && defined $value && $value eq q{}) { + $dataFields{$fields->[$j]} = undef; + push @values, undef; + } else { + $dataFields{$fields->[$j]} = $value; + push @values, $value; } - $ok = _processRow_DB($dbh, $db_scheme, $table, $fieldsStr, $dataStr, $updateStr, \@arrData, \%dataFields, $PKArray, \@fieldsPK, $fields2Delete); + $j++ } } - $pos = tell($dom); + $ok = _processRow_DB($dbh, $table, $fieldsStr, $dataStr, $updateStr, \@values, \%dataFields, $PKArray, \@fieldsPK, $fields2Delete); } - @arrData = (); + $pos = tell($dom); } - $numRow++; + @arrData = (); } return $ok; }#_import_table_csv @@ -1220,7 +1042,7 @@ sub _import_table_csv # Import worksheet from the ods content.xml file to the mysql table sub _import_table_ods { - my ($dbh, $table, $frameworkcode, $dom, $PKArray, $fields2Delete) = @_; + my ($dbh, $table, $frameworkcode, $dom, $PKArray, $fields2Delete, $frameworktype) = @_; my $xc = XML::LibXML::XPathContext->new($dom); $xc->registerNs('xmlns:office','urn:oasis:names:tc:opendocument:xmlns:office:1.0'); @@ -1230,9 +1052,9 @@ sub _import_table_ods @nodes = $xc->findnodes('//table:table[@table:name="' . $table . '"]'); if (@nodes == 1 && $nodes[0]->hasChildNodes()) { my $nodeR = $nodes[0]->firstChild; - return _processRows_Table($dbh, $frameworkcode, $nodeR, $table, $PKArray, 'ods', $fields2Delete); + return _processRows_Table($dbh, $frameworkcode, $nodeR, $table, $PKArray, 'ods', $fields2Delete, $frameworktype); } else { - $debug and warn "Error _import_table_ods there's not worksheet for $table\n"; + Koha::Logger->get->warn("Error _import_table_ods there's not worksheet for $table"); } return 0; }#_import_table_ods @@ -1241,7 +1063,7 @@ sub _import_table_ods # Import worksheet from the excel-xml file to the mysql table sub _import_table_excel { - my ($dbh, $table, $frameworkcode, $dom, $PKArray, $fields2Delete) = @_; + my ($dbh, $table, $frameworkcode, $dom, $PKArray, $fields2Delete, $frameworktype) = @_; my $xc = XML::LibXML::XPathContext->new($dom); $xc->registerNs('xmlns','urn:schemas-microsoft-com:office:spreadsheet'); @@ -1254,11 +1076,11 @@ sub _import_table_excel my @nodesT = $nodes[$i]->getElementsByTagNameNS('urn:schemas-microsoft-com:office:spreadsheet', 'Table'); if (@nodesT == 1 && $nodesT[0]->hasChildNodes()) { my $nodeR = $nodesT[0]->firstChild; - return _processRows_Table($dbh, $frameworkcode, $nodeR, $table, $PKArray, undef, $fields2Delete); + return _processRows_Table($dbh, $frameworkcode, $nodeR, $table, $PKArray, undef, $fields2Delete, $frameworktype); } } } else { - $debug and warn "Error _import_table_excel there's not worksheet for $table\n"; + Koha::Logger->get->warn("Error _import_table_excel there's not worksheet for $table"); } return 0; }#_import_table_excel @@ -1291,10 +1113,11 @@ sub _getDataNodeODS # Get the data from a row of a spreadsheet sub _getDataFields { - my ($frameworkcode, $node, $fields, $format) = @_; + my ($frameworkcode, $node, $fields, $format, $frameworktype) = @_; my $dataFields = {}; my @dataFieldsA = (); + $frameworktype ||= ''; if ($node && $node->hasChildNodes()) { my $node2 = $node->firstChild; my ($data, $repeated); @@ -1305,7 +1128,7 @@ sub _getDataFields if ($format && $format eq 'ods') { ($data, $repeated) = _getDataNodeODS($node2) if ($repeated <= 0); $repeated--; - $ok = 1 if (defined($data)); + $ok = 1; } else { if ($node2->nodeType == 1 && $node2->nodeName =~ /(?:ss:)?Cell/) { my @nodes3 = $node2->getElementsByTagNameNS('urn:schemas-microsoft-com:office:spreadsheet', 'Data'); @@ -1316,8 +1139,23 @@ sub _getDataFields } } if ($ok) { + $data //= ''; $data = '' if ($data eq '#'); - $data = $frameworkcode if ($fields->[$i] eq 'frameworkcode'); + if ($frameworktype eq "authority") { + if ( $fields->[$i] eq 'authtypecode' ) { + $data = $frameworkcode; + } + elsif ( $fields->[$i] eq 'isurl' ) { + $data = undef if defined $data && $data eq q{}; + } + } else { + if ( $fields->[$i] eq 'frameworkcode' ) { + $data = $frameworkcode; + } + elsif ( $fields->[$i] eq 'isurl' ) { + $data = undef if defined $data && $data eq q{}; + } + } $dataFields->{$fields->[$i]} = $data; push @dataFieldsA, $data; $i++;