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