From f4661457dbcb73207af1c11c1dcab6d2252ee146 Mon Sep 17 00:00:00 2001 From: Jonathan Druart <jonathan.druart@biblibre.com> Date: Tue, 10 Sep 2013 15:20:09 +0200 Subject: [PATCH] [SIGNED-OFF] Bug 10853: Add DB field export_format.type ('marc' or 'sql'). This patch: - adds a new column 'type' to the export_format table. - renames the field name export_format.marcfields with export_format.content. Feel free to propose another name. Test plan: - Check that existing profiles have the type "marc" selected by default - Create a new profile with a type "sql" - Save and verify the profile is correctly displayed when you select it. Signed-off-by: Bernardo Gonzalez Kriegel <bgkriegel@gmail.com> Comment: Work as described. koha-qa reports Small tabs errors, corrected in followup Test: 1) go to Tools > CSVProfiles, Create profile, current 2) Apply patch, run updatedatabase 3) Go to Tools > CSVProfiles, new option present old profile with type MARC 4) Create new profile MARC, save and show correct 5) Create new profile SQL, save and show correct --- C4/Csv.pm | 10 ++- C4/Record.pm | 2 +- installer/data/mysql/kohastructure.sql | 3 +- installer/data/mysql/updatedatabase.pl | 18 +++++ .../prog/en/modules/tools/csv-profiles.tt | 83 +++++++++++++++++--- tools/csv-profiles.pl | 21 +++-- 6 files changed, 112 insertions(+), 25 deletions(-) diff --git a/C4/Csv.pm b/C4/Csv.pm index 74270ff..13ab1a4 100644 --- a/C4/Csv.pm +++ b/C4/Csv.pm @@ -43,11 +43,15 @@ $VERSION = 3.07.00.049; # Returns all informations about csv profiles sub GetCsvProfiles { + my ( $type ) = @_; my $dbh = C4::Context->dbh; my $query = "SELECT * FROM export_format"; + if ( $type ) { + $query .= " WHERE type = ?"; + } $sth = $dbh->prepare($query); - $sth->execute; + $sth->execute( $type ? $type : () ); $sth->fetchall_arrayref({}); @@ -82,12 +86,12 @@ sub GetMarcFieldsForCsv { my ($id) = @_; my $dbh = C4::Context->dbh; - my $query = "SELECT marcfields FROM export_format WHERE export_format_id=?"; + my $query = "SELECT content FROM export_format WHERE export_format_id=?"; $sth = $dbh->prepare($query); $sth->execute($id); - return ($sth->fetchrow_hashref)->{marcfields}; + return ($sth->fetchrow_hashref)->{content}; } diff --git a/C4/Record.pm b/C4/Record.pm index be1912c..c010888 100644 --- a/C4/Record.pm +++ b/C4/Record.pm @@ -461,7 +461,7 @@ sub marcrecord2csv { $csv->sep_char($csvseparator); # Getting the marcfields - my $marcfieldslist = $profile->{marcfields}; + my $marcfieldslist = $profile->{content}; # Getting the marcfields as an array my @marcfieldsarray = split('\|', $marcfieldslist); diff --git a/installer/data/mysql/kohastructure.sql b/installer/data/mysql/kohastructure.sql index fdab879..3e8e0ea 100644 --- a/installer/data/mysql/kohastructure.sql +++ b/installer/data/mysql/kohastructure.sql @@ -921,11 +921,12 @@ CREATE TABLE `export_format` ( `export_format_id` int(11) NOT NULL auto_increment, `profile` varchar(255) NOT NULL, `description` mediumtext NOT NULL, - `marcfields` mediumtext NOT NULL, + `content` mediumtext NOT NULL, `csv_separator` varchar(2) NOT NULL, `field_separator` varchar(2) NOT NULL, `subfield_separator` varchar(2) NOT NULL, `encoding` varchar(255) NOT NULL, + `type` varchar(255) DEFAULT 'marc', PRIMARY KEY (`export_format_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Used for CSV export'; diff --git a/installer/data/mysql/updatedatabase.pl b/installer/data/mysql/updatedatabase.pl index 31f494a..89aa61f 100755 --- a/installer/data/mysql/updatedatabase.pl +++ b/installer/data/mysql/updatedatabase.pl @@ -7139,6 +7139,24 @@ if ( CheckVersion($DBversion) ) { SetVersion ($DBversion); } + + + + + + +$DBversion = "3.13.00.XXX"; +if ( C4::Context->preference("Version") < TransformToNum($DBversion) ) { + $dbh->do(q{ + ALTER TABLE export_format ADD type VARCHAR(255) DEFAULT 'marc' AFTER encoding + }); + $dbh->do(q{ + ALTER TABLE export_format CHANGE marcfields content mediumtext NOT NULL + }); + print "Upgrade to $DBversion done (Bug 10853: Add new field export_format.type and rename export_format.marcfields with export_format.content)\n"; + SetVersion($DBversion); +} + =head1 FUNCTIONS =head2 TableExists($table) diff --git a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/csv-profiles.tt b/koha-tmpl/intranet-tmpl/prog/en/modules/tools/csv-profiles.tt index 01f5166..9c58751 100644 --- a/koha-tmpl/intranet-tmpl/prog/en/modules/tools/csv-profiles.tt +++ b/koha-tmpl/intranet-tmpl/prog/en/modules/tools/csv-profiles.tt @@ -7,6 +7,30 @@ function reloadPage(p) { } $(document).ready(function() { $('#csvexporttabs').tabs(); + + $("#profile_type").find("option:first").attr("selected", "selected"); + $("#csvnew").find("li.marc_specific").show(); + $("#csvnew").find("li.sql_specific").hide(); + + $("#profile_type").change(function(){ + if ( $(this).find("option:selected").val() == "marc" ) { + $("#csvnew li.marc_specific").show(); + $("#csvnew li.sql_specific").hide(); + } else { + $("#csvnew li.marc_specific").hide(); + $("#csvnew li.sql_specific").show(); + } + }); + $("#modify_profile_type").change(function(){ + if ( $(this).find("option:selected").val() == "marc" ) { + $("#csvedit li.marc_specific").show(); + $("#csvedit li.sql_specific").hide(); + } else { + $("#csvedit li.marc_specific").hide(); + $("#csvedit li.sql_specific").show(); + } + }); + $("#modify_profile_type").change(); }); //]]> </script> @@ -53,9 +77,18 @@ function reloadPage(p) { <form action="/cgi-bin/koha/tools/csv-profiles.pl" method="post"> <fieldset class="rows"> - <ol><li><label for="profile_name" class="required">Profile name: </label> + <ol> + <li><label for="profile_name" class="required">Profile name: </label> <input type="text" id="profile_name" name="profile_name" /></li> + <li> + <label for="profile_type" class="required">Profile type: </label> + <select id="profile_type" name="profile_type"> + <option value="marc" selected="selected">MARC</option> + <option value="sql">SQL</option> + </select> + </li> + <li><label for="profile_description">Profile description: </label> <textarea cols="50" rows="2" name="profile_description" id="profile_description"></textarea></li> @@ -72,7 +105,7 @@ function reloadPage(p) { </select> </li> - <li><label for="new_field_separator">Field separator: </label> + <li class="marc_specific"><label for="new_field_separator">Field separator: </label> <select name="field_separator" id="new_field_separator"> <option value=":">Colon (:)</option> <option value=",">Comma (,)</option> @@ -85,7 +118,7 @@ function reloadPage(p) { </select> </li> - <li><label for="new_subfield_separator">Subfield separator: </label> + <li class="marc_specific"><label for="new_subfield_separator">Subfield separator: </label> <select name="subfield_separator" id="new_subfield_separator"> <option value=":">Colon (:)</option> <option value=",">Comma (,)</option> @@ -98,7 +131,7 @@ function reloadPage(p) { </select> </li> - <li><label for="new_encoding">Encoding: </label> + <li class="marc_specific"><label for="new_encoding">Encoding: </label> <select name="encoding" id="new_encoding"> [% FOREACH encoding IN encodings %] [% IF ( encoding.encoding == 'utf8' ) %] @@ -110,12 +143,19 @@ function reloadPage(p) { </select></li> - <li><label for="new_profile_content">Profile MARC fields: </label> - <textarea cols="50" rows="2" name="profile_content" id="new_profile_content"></textarea> + <li class="marc_specific"><label for="new_profile_marc_content">Profile MARC fields: </label> + <textarea cols="50" rows="2" name="profile_marc_content" id="new_profile_marc_content"></textarea> <p>You have to define which fields or subfields you want to export, separated by pipes.</p> <p>You can also use your own headers (instead of the ones from Koha) by prefixing the field number with an header, followed by the equal sign.</p> <p>Example: Personal name=200|Entry element=210$a|300|009</p> </li> + <li class="sql_specific"> + <label for="new_profile_sql_content">Profile SQL fields: </label> + <textarea cols="50" rows="2" name="profile_sql_content" id="new_profile_sql_content"></textarea> + <p>You have to define which fields you want to export, separated by pipes.</p> + <p>You can also use your own headers (instead of the ones from Koha) by prefixing the field name with an header, followed by the equal sign.</p> + <p>Example: Name=subscription.name|Title=subscription.title|Issue number=serial.serialseq</p> + </li> </ol> </fieldset> <fieldset class="action"><input type="hidden" name="action" value="create" /> @@ -129,7 +169,8 @@ function reloadPage(p) { <form action="/cgi-bin/koha/tools/csv-profiles.pl" method="post"> <fieldset class="rows"> - <ol><li><label for="modify_profile_name">Profile name: </label> + <ol> + <li><label for="modify_profile_name">Profile name: </label> <select id="modify_profile_name" name="profile_name" onchange="javascript:reloadPage(this)"> <option value="0">-- Choose One --</option> [% FOREACH existing_profile IN existing_profiles %] @@ -141,6 +182,19 @@ function reloadPage(p) { [% END %] </select></li> + <li> + <label for="modify_profile_type">Profile type: </label> + <select id="modify_profile_type" name="profile_type"> + <option value="marc">MARC</option> + [% IF selected_profile_type == "sql" %] + <option value="sql" selected="selected">SQL</option> + [% ELSE %] + <option value="sql">SQL</option> + [% END %] + </select> + </li> + + <li><label for="modify_profile_description">Profile description: </label> <textarea cols="50" rows="2" name="profile_description" id="modify_profile_description">[% selected_profile_description %]</textarea></li> @@ -184,7 +238,7 @@ function reloadPage(p) { [% END %] </select></li> - <li><label for="field_separator">Field separator: </label> + <li class="marc_specific"><label for="field_separator">Field separator: </label> <select name="field_separator" id="field_separator"> <option value=":">Colon (:)</option> @@ -231,7 +285,7 @@ function reloadPage(p) { </select></li> - <li><label for="subfield_separator">Subfield separator: </label> + <li class="marc_specific"><label for="subfield_separator">Subfield separator: </label> <select name="subfield_separator" id="subfield_separator"> <option value=":">Colon (:)</option> @@ -278,7 +332,7 @@ function reloadPage(p) { </select></li> - <li><label for="encoding">Encoding: </label> + <li class="marc_specific"><label for="encoding">Encoding: </label> <select name="encoding" id="encoding"> [% FOREACH encoding IN encodings %] [% IF ( selected_encoding == encoding.encoding ) %] @@ -289,8 +343,13 @@ function reloadPage(p) { [% END %] </select></li> - <li><label for="modify_profile_content">Profile MARC fields: </label> - <textarea cols="50" rows="2" name="profile_content" id="modify_profile_content">[% selected_profile_marcfields %]</textarea></li> + <li class="marc_specific"><label for="modify_profile_marc_content">Profile MARC fields: </label> + <textarea cols="50" rows="2" name="profile_marc_content" id="modify_profile_marc_content">[% selected_profile_content %]</textarea></li> + + <li class="sql_specific"> + <label for="modify_profile_sql_content">Profile SQL fields: </label> + <textarea cols="50" rows="2" name="profile_sql_content" id="modify_profile_sql_content">[% selected_profile_content %]</textarea> + </li> <li class="radio"> <label for="delete">Delete selected profile ?</label> <input type="checkbox" name="delete" id="delete" /></li> diff --git a/tools/csv-profiles.pl b/tools/csv-profiles.pl index 32f2b98..d144ed7 100755 --- a/tools/csv-profiles.pl +++ b/tools/csv-profiles.pl @@ -67,30 +67,34 @@ $template->param(encodings => \@encodings_loop); my $profile_name = $input->param("profile_name"); my $profile_description = $input->param("profile_description"); -my $profile_content = $input->param("profile_content"); my $csv_separator = $input->param("csv_separator"); my $field_separator = $input->param("field_separator"); my $subfield_separator = $input->param("subfield_separator"); my $encoding = $input->param("encoding"); +my $type = $input->param("profile_type"); my $action = $input->param("action"); my $delete = $input->param("delete"); my $id = $input->param("id"); if ($delete) { $action = "delete"; } +my $profile_content = $type eq "marc" + ? $input->param("profile_marc_content") + : $input->param("profile_sql_content"); + if ($profile_name && $profile_content && $action) { my $rows; if ($action eq "create") { - my $query = "INSERT INTO export_format(export_format_id, profile, description, marcfields, csv_separator, field_separator, subfield_separator, encoding) VALUES (NULL, ?, ?, ?, ?, ?, ?, ?)"; + my $query = "INSERT INTO export_format(export_format_id, profile, description, content, csv_separator, field_separator, subfield_separator, encoding, type) VALUES (NULL, ?, ?, ?, ?, ?, ?, ?, ?)"; my $sth = $dbh->prepare($query); - $rows = $sth->execute($profile_name, $profile_description, $profile_content, $csv_separator, $field_separator, $subfield_separator, $encoding); + $rows = $sth->execute($profile_name, $profile_description, $profile_content, $csv_separator, $field_separator, $subfield_separator, $encoding, $type); } if ($action eq "edit") { - my $query = "UPDATE export_format SET description=?, marcfields=?, csv_separator=?, field_separator=?, subfield_separator=?, encoding=? WHERE export_format_id=? LIMIT 1"; + my $query = "UPDATE export_format SET description=?, content=?, csv_separator=?, field_separator=?, subfield_separator=?, encoding=?, type=? WHERE export_format_id=? LIMIT 1"; my $sth = $dbh->prepare($query); - $rows = $sth->execute($profile_description, $profile_content, $csv_separator, $field_separator, $subfield_separator, $encoding, $profile_name); + $rows = $sth->execute($profile_description, $profile_content, $csv_separator, $field_separator, $subfield_separator, $encoding, $type, $profile_name); } if ($action eq "delete") { @@ -108,7 +112,7 @@ if ($profile_name && $profile_content && $action) { # If a profile has been selected for modification if ($id) { - my $query = "SELECT export_format_id, profile, description, marcfields, csv_separator, field_separator, subfield_separator, encoding FROM export_format WHERE export_format_id = ?"; + my $query = "SELECT export_format_id, profile, description, content, csv_separator, field_separator, subfield_separator, encoding, type FROM export_format WHERE export_format_id = ?"; my $sth; $sth = $dbh->prepare($query); @@ -118,11 +122,12 @@ if ($profile_name && $profile_content && $action) { selected_profile_id => $selected_profile->[0], selected_profile_name => $selected_profile->[1], selected_profile_description => $selected_profile->[2], - selected_profile_marcfields => $selected_profile->[3], + selected_profile_content => $selected_profile->[3], selected_csv_separator => $selected_profile->[4], selected_field_separator => $selected_profile->[5], selected_subfield_separator => $selected_profile->[6], - selected_encoding => $selected_profile->[7] + selected_encoding => $selected_profile->[7], + selected_profile_type => $selected_profile->[8] ); } -- 1.7.9.5