|
Lines 266-277
sub _export_table_sql
Link Here
|
| 266 |
|
266 |
|
| 267 |
eval { |
267 |
eval { |
| 268 |
# First row with the name of the columns |
268 |
# First row with the name of the columns |
| 269 |
my $query = 'SHOW COLUMNS FROM ' . $table; |
269 |
my $sth = $dbh->column_info(undef, undef, $table, "%"); |
| 270 |
my $sth = $dbh->prepare($query); |
|
|
| 271 |
$sth->execute(); |
270 |
$sth->execute(); |
| 272 |
my @fields = (); |
271 |
my @fields = (); |
| 273 |
while (my $hashRef = $sth->fetchrow_hashref) { |
272 |
while (my $hashRef = $sth->fetchrow_hashref) { |
| 274 |
push @fields, $hashRef->{Field}; |
273 |
push @fields, $hashRef->{COLUMN_NAME}; |
| 275 |
} |
274 |
} |
| 276 |
my $fields = join(',', @fields); |
275 |
my $fields = join(',', @fields); |
| 277 |
$$strSQL .= 'DELETE FROM ' . $table . ' WHERE frameworkcode=' . $dbh->quote($frameworkcode) . ';'; |
276 |
$$strSQL .= 'DELETE FROM ' . $table . ' WHERE frameworkcode=' . $dbh->quote($frameworkcode) . ';'; |
|
Lines 305-317
sub _export_table_csv
Link Here
|
| 305 |
|
304 |
|
| 306 |
eval { |
305 |
eval { |
| 307 |
# First row with the name of the columns |
306 |
# First row with the name of the columns |
| 308 |
my $query = 'SHOW COLUMNS FROM ' . $table; |
307 |
my $sth = $dbh->column_info(undef, undef, $table, "%"); |
| 309 |
my $sth = $dbh->prepare($query); |
|
|
| 310 |
$sth->execute(); |
308 |
$sth->execute(); |
| 311 |
my @fields = (); |
309 |
my @fields = (); |
| 312 |
while (my $hashRef = $sth->fetchrow_hashref) { |
310 |
while (my $hashRef = $sth->fetchrow_hashref) { |
| 313 |
$$strCSV .= '"' . $hashRef->{Field} . '",'; |
311 |
$$strCSV .= '"' . $hashRef->{COLUMN_NAME} . '",'; |
| 314 |
push @fields, $hashRef->{Field}; |
312 |
push @fields, $hashRef->{COLUMN_NAME}; |
| 315 |
} |
313 |
} |
| 316 |
chop $$strCSV; |
314 |
chop $$strCSV; |
| 317 |
$$strCSV .= chr(10); |
315 |
$$strCSV .= chr(10); |
|
Lines 361-379
sub _export_table_ods
Link Here
|
| 361 |
my $elementCell; |
359 |
my $elementCell; |
| 362 |
my $elementData; |
360 |
my $elementData; |
| 363 |
# First row with the name of the columns |
361 |
# First row with the name of the columns |
| 364 |
my $query = 'SHOW COLUMNS FROM ' . $table; |
362 |
my $sth = $dbh->column_info(undef, undef, $table, "%"); |
| 365 |
my $sth = $dbh->prepare($query); |
|
|
| 366 |
$sth->execute(); |
363 |
$sth->execute(); |
| 367 |
my @fields = (); |
364 |
my @fields = (); |
| 368 |
while (my $hashRef = $sth->fetchrow_hashref) { |
365 |
while (my $hashRef = $sth->fetchrow_hashref) { |
| 369 |
$elementCell = $dom->createElement('table:table-cell'); |
366 |
$elementCell = $dom->createElement('table:table-cell'); |
| 370 |
$elementCell->setAttribute('office:value-type', 'string'); |
367 |
$elementCell->setAttribute('office:value-type', 'string'); |
| 371 |
$elementCell->setAttribute('office:value', $hashRef->{Field}); |
368 |
$elementCell->setAttribute('office:value', $hashRef->{COLUMN_NAME}); |
| 372 |
$elementRow->appendChild($elementCell); |
369 |
$elementRow->appendChild($elementCell); |
| 373 |
$elementData = $dom->createElement('text:p'); |
370 |
$elementData = $dom->createElement('text:p'); |
| 374 |
$elementCell->appendChild($elementData); |
371 |
$elementCell->appendChild($elementData); |
| 375 |
$elementData->appendTextNode($hashRef->{Field}); |
372 |
$elementData->appendTextNode($hashRef->{COLUMN_NAME}); |
| 376 |
push @fields, {name => $hashRef->{Field}, type => ($hashRef->{Type} =~ /int/i)?'float':'string'}; |
373 |
push @fields, {name => $hashRef->{COLUMN_NAME}, type => ($hashRef->{TYPE_NAME} =~ /int/i)?'float':'string'}; |
| 377 |
} |
374 |
} |
| 378 |
# Populate rows with the data from mysql |
375 |
# Populate rows with the data from mysql |
| 379 |
$query = 'SELECT * FROM ' . $table . ' WHERE frameworkcode=?'; |
376 |
$query = 'SELECT * FROM ' . $table . ' WHERE frameworkcode=?'; |
|
Lines 426-433
sub _export_table_excel
Link Here
|
| 426 |
# First row with the name of the columns |
423 |
# First row with the name of the columns |
| 427 |
my $elementCell; |
424 |
my $elementCell; |
| 428 |
my $elementData; |
425 |
my $elementData; |
| 429 |
my $query = 'SHOW COLUMNS FROM ' . $table; |
426 |
my $sth = $dbh->column_info(undef, undef, $table, "%"); |
| 430 |
my $sth = $dbh->prepare($query); |
|
|
| 431 |
$sth->execute(); |
427 |
$sth->execute(); |
| 432 |
my @fields = (); |
428 |
my @fields = (); |
| 433 |
while (my $hashRef = $sth->fetchrow_hashref) { |
429 |
while (my $hashRef = $sth->fetchrow_hashref) { |
|
Lines 437-444
sub _export_table_excel
Link Here
|
| 437 |
$elementData = $dom->createElement('ss:Data'); |
433 |
$elementData = $dom->createElement('ss:Data'); |
| 438 |
$elementData->setAttribute('ss:Type', 'String'); |
434 |
$elementData->setAttribute('ss:Type', 'String'); |
| 439 |
$elementCell->appendChild($elementData); |
435 |
$elementCell->appendChild($elementData); |
| 440 |
$elementData->appendTextNode($hashRef->{Field}); |
436 |
$elementData->appendTextNode($hashRef->{COLUMN_NAME}); |
| 441 |
push @fields, {name => $hashRef->{Field}, type => ($hashRef->{Type} =~ /int/i)?'Number':'String'}; |
437 |
push @fields, {name => $hashRef->{COLUMN_NAME}, type => ($hashRef->{TYPE_NAME} =~ /int/i)?'Number':'String'}; |
| 442 |
} |
438 |
} |
| 443 |
# Populate rows with the data from mysql |
439 |
# Populate rows with the data from mysql |
| 444 |
$query = 'SELECT * FROM ' . $table . ' WHERE frameworkcode=?'; |
440 |
$query = 'SELECT * FROM ' . $table . ' WHERE frameworkcode=?'; |
|
Lines 946-961
sub _check_validity_worksheet
Link Here
|
| 946 |
|
942 |
|
| 947 |
my $ret = 0; |
943 |
my $ret = 0; |
| 948 |
eval { |
944 |
eval { |
| 949 |
my $query = 'DESCRIBE ' . $table; |
945 |
my $sth = $dbh->column_info(undef, undef, $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(); |
946 |
$sth->execute(); |
| 956 |
my $fields = {}; |
947 |
my $fields = {}; |
| 957 |
while (my $hashRef = $sth->fetchrow_hashref) { |
948 |
while (my $hashRef = $sth->fetchrow_hashref) { |
| 958 |
$fields->{$hashRef->{Field}} = $hashRef->{Field}; |
949 |
$fields->{$hashRef->{COLUMN_NAME}} = $hashRef->{COLUMN_NAME}; |
| 959 |
} |
950 |
} |
| 960 |
my @fields; |
951 |
my @fields; |
| 961 |
my $fieldsR; |
952 |
my $fieldsR; |
|
Lines 1004-1014
sub _import_table
Link Here
|
| 1004 |
if ($format eq 'csv') { |
995 |
if ($format eq 'csv') { |
| 1005 |
my @fieldsName = (); |
996 |
my @fieldsName = (); |
| 1006 |
eval { |
997 |
eval { |
| 1007 |
my $query = 'SHOW COLUMNS FROM ' . $table; |
998 |
my $sth = $dbh->column_info(undef, undef, $table, "%"); |
| 1008 |
my $sth = $dbh->prepare($query); |
|
|
| 1009 |
$sth->execute(); |
999 |
$sth->execute(); |
| 1010 |
while (my $hashRef = $sth->fetchrow_hashref) { |
1000 |
while (my $hashRef = $sth->fetchrow_hashref) { |
| 1011 |
push @fieldsName, $hashRef->{Field}; |
1001 |
push @fieldsName, $hashRef->{COLUMN_NAME}; |
| 1012 |
} |
1002 |
} |
| 1013 |
}; |
1003 |
}; |
| 1014 |
$ok = _import_table_csv($dbh, $table, $frameworkcode, $dom, $PKArray, \%fields2Delete, \@fieldsName); |
1004 |
$ok = _import_table_csv($dbh, $table, $frameworkcode, $dom, $PKArray, \%fields2Delete, \@fieldsName); |