1 package C4::Creators::Lib;
3 # Copyright 2009 Foundations Bible College.
5 # This file is part of Koha.
7 # Koha is free software; you can redistribute it and/or modify it
8 # under the terms of the GNU General Public License as published by
9 # the Free Software Foundation; either version 3 of the License, or
10 # (at your option) any later version.
12 # Koha is distributed in the hope that it will be useful, but
13 # WITHOUT ANY WARRANTY; without even the implied warranty of
14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
15 # GNU General Public License for more details.
17 # You should have received a copy of the GNU General Public License
18 # along with Koha; if not, see <http://www.gnu.org/licenses>.
23 use autouse 'Data::Dumper' => qw(Dumper);
29 use version; our $VERSION = qv('3.07.00.049');
30 use base qw(Exporter);
31 our @EXPORT = qw(get_all_templates
41 get_text_justification_types
59 #=head2 C4::Creators::Lib::_SELECT()
61 # This function returns a recordset upon success and 1 upon failure. Errors are logged to the Apache log.
65 # my $field_value = _SELECT(field_name, table_name, condition);
71 my $query = "SELECT $params[0] FROM $params[1]";
72 $params[2] ? $query .= " WHERE $params[2];" : $query .= ';';
73 my $sth = C4::Context->dbh->prepare($query);
74 # $sth->{'TraceLevel'} = 3;
77 warn sprintf('Database returned the following error: %s', $sth->errstr);
81 while (my $row = $sth->fetchrow_hashref()) {
82 push(@$record_set, $row);
88 {type => 'CODE39', name => 'Code 39', desc => 'Translates the characters 0-9, A-Z, \'-\', \'*\', \'+\', \'$\', \'%\', \'/\', \'.\' and \' \' to a barcode pattern.', selected => 0},
89 {type => 'CODE39MOD', name => 'Code 39 + Modulo43', desc => 'Translates the characters 0-9, A-Z, \'-\', \'*\', \'+\', \'$\', \'%\', \'/\', \'.\' and \' \' to a barcode pattern. Encodes Mod 43 checksum.', selected => 0},
90 {type => 'CODE39MOD10', name => 'Code 39 + Modulo10', desc => 'Translates the characters 0-9, A-Z, \'-\', \'*\', \'+\', \'$\', \'%\', \'/\', \'.\' and \' \' to a barcode pattern. Encodes Mod 10 checksum.', selected => 0},
91 {type => 'COOP2OF5', name => 'COOP2of5', desc => 'Creates COOP2of5 barcodes from a string consisting of the numeric characters 0-9', selected => 0},
92 {type => 'EAN13', name => 'EAN13', desc => 'Creates EAN13 barcodes from a string of 12 or 13 digits. The check number (the 13:th digit) is calculated if not supplied.', selected => 0},
93 # {type => 'EAN8', name => 'EAN8', desc => 'Translates a string of 7 or 8 digits to EAN8 barcodes. The check number (the 8:th digit) is calculated if not supplied.', selected => 0},
94 # {type => 'IATA2of5', name => 'IATA2of5', desc => 'Creates IATA2of5 barcodes from a string consisting of the numeric characters 0-9', selected => 0},
95 {type => 'INDUSTRIAL2OF5', name => 'Industrial2of5', desc => 'Creates Industrial2of5 barcodes from a string consisting of the numeric characters 0-9', selected => 0},
96 # {type => 'ITF', name => 'Interleaved2of5', desc => 'Translates the characters 0-9 to a barcodes. These barcodes could also be called 'Interleaved2of5'.', selected => 0},
97 # {type => 'MATRIX2OF5', name => 'Matrix2of5', desc => 'Creates Matrix2of5 barcodes from a string consisting of the numeric characters 0-9', selected => 0},
98 # {type => 'NW7', name => 'NW7', desc => 'Creates a NW7 barcodes from a string consisting of the numeric characters 0-9', selected => 0},
99 # {type => 'UPCA', name => 'UPCA', desc => 'Translates a string of 11 or 12 digits to UPCA barcodes. The check number (the 12:th digit) is calculated if not supplied.', selected => 0},
100 # {type => 'UPCE', name => 'UPCE', desc => 'Translates a string of 6, 7 or 8 digits to UPCE barcodes. If the string is 6 digits long, '0' is added first in the string. The check number (the 8:th digit) is calculated if not supplied.', selected => 0},
104 {type => 'BIB', name => 'Biblio', desc => 'Only the bibliographic data is printed.', selected => 0},
105 {type => 'BARBIB', name => 'Barcode/Biblio', desc => 'Barcode proceeds bibliographic data.', selected => 0},
106 {type => 'BIBBAR', name => 'Biblio/Barcode', desc => 'Bibliographic data proceeds barcode.', selected => 0},
107 {type => 'ALT', name => 'Alternating', desc => 'Barcode and bibliographic data are printed on alternating labels.', selected => 0},
108 {type => 'BAR', name => 'Barcode', desc => 'Only the barcode is printed.', selected => 0},
112 {type => 'TR', name => 'Times-Roman', selected => 0},
113 {type => 'TB', name => 'Times-Bold', selected => 0},
114 {type => 'TI', name => 'Times-Italic', selected => 0},
115 {type => 'TBI', name => 'Times-Bold-Italic', selected => 0},
116 {type => 'C', name => 'Courier', selected => 0},
117 {type => 'CB', name => 'Courier-Bold', selected => 0},
118 {type => 'CO', name => 'Courier-Oblique', selected => 0},
119 {type => 'CBO', name => 'Courier-Bold-Oblique', selected => 0},
120 {type => 'H', name => 'Helvetica', selected => 0},
121 {type => 'HO', name => 'Helvetica-Oblique', selected => 0},
122 {type => 'HB', name => 'Helvetica-Bold', selected => 0},
123 {type => 'HBO', name => 'Helvetica-Bold-Oblique', selected => 0},
126 my $text_justification_types = [
127 {type => 'L', name => 'Left', selected => 0},
128 {type => 'C', name => 'Center', selected => 0},
129 {type => 'R', name => 'Right', selected => 0},
130 # {type => 'F', name => 'Full', selected => 0},
134 {type => 'POINT', desc => 'PostScript Points', value => 1, selected => 0},
135 {type => 'AGATE', desc => 'Adobe Agates', value => 5.1428571, selected => 0},
136 {type => 'INCH', desc => 'US Inches', value => 72, selected => 0},
137 {type => 'MM', desc => 'SI Millimeters', value => 2.83464567, selected => 0},
138 {type => 'CM', desc => 'SI Centimeters', value => 28.3464567, selected => 0},
141 my $output_formats = [
142 {type => 'pdf', desc => 'PDF File'},
143 {type => 'csv', desc => 'CSV File'},
147 my ( $params, $table ) = @_;
148 my @fields = exists $params->{fields} ? @{ $params->{fields} } : ();
149 my $query = "SELECT " . ( @fields ? join(', ', @fields ) : '*' ) . " FROM $table";
151 if ( exists $params->{filters} ) {
152 $query .= ' WHERE 1 ';
153 while ( my ( $field, $values ) = each %{ $params->{filters} } ) {
154 if ( ref( $values ) ) {
155 $query .= " AND $field IN ( " . ( ('?') x scalar( @$values ) ) . " ) ";
156 push @where_args, @$values;
158 $query .= " AND $field = ? ";
159 push @where_args, $values;
163 $query .= (exists $params->{orderby} ? " ORDER BY $params->{orderby} " : '');
164 return ( $query, @where_args );
167 =head2 C4::Creators::Lib::get_all_templates()
169 my $templates = get_all_templates();
171 This function returns a reference to a hash containing all templates upon success and 1 upon failure. Errors are logged to the Apache log.
175 sub get_all_templates {
178 my ( $query, @where_args ) = _build_query( $params, 'creator_templates' );
179 my $sth = C4::Context->dbh->prepare($query);
180 $sth->execute( @where_args );
182 warn sprintf('Database returned the following error: %s', $sth->errstr);
186 while (my $template = $sth->fetchrow_hashref) {
187 push(@templates, $template);
192 =head2 C4::Creators::Lib::get_all_layouts()
194 my $layouts = get_all_layouts();
196 This function returns a reference to a hash containing all layouts upon success and 1 upon failure. Errors are logged to the Apache log.
200 sub get_all_layouts {
203 my ( $query, @where_args ) = _build_query( $params, 'creator_layouts' );
204 my $sth = C4::Context->dbh->prepare($query);
205 $sth->execute( @where_args );
207 warn sprintf('Database returned the following error: %s', $sth->errstr);
211 while (my $layout = $sth->fetchrow_hashref) {
212 push(@layouts, $layout);
217 =head2 C4::Creators::Lib::get_all_profiles()
219 my $profiles = get_all_profiles();
221 my $profiles = get_all_profiles({ fields => [@fields], filters => { filters => [$value1, $value2] } });
223 This function returns an arrayref whose elements are hashes containing all profiles upon success and 1 upon failure. Errors are logged
224 to the Apache log. Two parameters are accepted. The first limits the field(s) returned. This parameter should be string of comma separted
225 fields. ie. "field_1, field_2, ...field_n" The second limits the records returned based on a string containing a valud SQL 'WHERE' filter.
227 NOTE: Do not pass in the keyword 'WHERE.'
231 sub get_all_profiles {
234 my ( $query, @where_args ) = _build_query( $params, 'printers_profile' );
235 my $sth = C4::Context->dbh->prepare($query);
236 $sth->execute( @where_args );
238 warn sprintf('Database returned the following error: %s', $sth->errstr);
242 while (my $profile = $sth->fetchrow_hashref) {
243 push(@profiles, $profile);
248 =head2 C4::Creators::Lib::get_all_image_names()
252 sub get_all_image_names {
253 my $image_names = [];
254 my $query = "SELECT image_name FROM creator_images";
255 my $sth = C4::Context->dbh->prepare($query);
256 # $sth->{'TraceLevel'} = 3 if $debug;
259 warn sprintf('Database returned the following error: %s', $sth->errstr);
262 grep {push @$image_names, {type => $$_[0], name => $$_[0], selected => 0}} @{$sth->fetchall_arrayref([0])};
266 =head2 C4::Creators::Lib::get_batch_summary()
268 my $batches = get_batch_summary();
270 my $batches = get_batch_summary(filter => filter_string);
272 This function returns an arrayref whose elements are hashes containing the batch_ids of current batches along with the item count
273 for each batch upon success and 1 upon failure. Item counts are stored under the key '_item_count' Errors are logged to the Apache log.
274 One parameter is accepted which limits the records returned based on a string containing a valud SQL 'WHERE' filter.
276 NOTE: Do not pass in the keyword 'WHERE.'
280 sub get_batch_summary {
283 $params->{fields} = ['batch_id', 'count(batch_id) as _item_count'];
284 my ( $query, @where_args ) = _build_query( $params, 'creator_batches' );
285 $query .= " GROUP BY batch_id";
286 my $sth = C4::Context->dbh->prepare($query);
287 $sth->execute( @where_args );
289 warn sprintf('Database returned the following error on attempted SELECT: %s', $sth->errstr);
292 while (my $batch = $sth->fetchrow_hashref) {
293 push(@batches, $batch);
298 =head2 C4::Creators::Lib::get_label_summary()
300 my $labels = get_label_summary();
302 my $labels = get_label_summary(items => @item_list);
304 This function returns an arrayref whose elements are hashes containing the label_ids of current labels along with the item count
305 for each label upon success and 1 upon failure. Item counts are stored under the key '_item_count' Errors are logged to the Apache log.
306 One parameter is accepted which limits the records returned based on a string containing a valud SQL 'WHERE' filter.
308 NOTE: Do not pass in the keyword 'WHERE.'
312 sub get_label_summary {
314 my $label_number = 0;
315 my @label_summaries = ();
316 my $query = " SELECT b.title, b.author, bi.itemtype, i.barcode, i.biblionumber, i.itype
317 FROM creator_batches AS c LEFT JOIN items AS i ON (c.item_number=i.itemnumber)
318 LEFT JOIN biblioitems AS bi ON (i.biblioitemnumber=bi.biblioitemnumber)
319 LEFT JOIN biblio AS b ON (bi.biblionumber=b.biblionumber)
320 WHERE itemnumber=? AND batch_id=?;
322 my $sth = C4::Context->dbh->prepare($query);
323 foreach my $item (@{$params{'items'}}) {
325 $sth->execute($item->{'item_number'}, $params{'batch_id'});
327 warn sprintf('Database returned the following error on attempted SELECT: %s', $sth->errstr);
330 my $record = $sth->fetchrow_hashref;
332 $label_summary->{'_label_number'} = $label_number;
333 $record->{'author'} =~ s/[^\.|\w]$// if $record->{'author'}; # strip off ugly trailing chars... but not periods or word chars
334 $record->{'title'} =~ s/\W*$//; # strip off ugly trailing chars
335 # FIXME contructing staff interface URLs should be done *much* higher up the stack - for the most part, C4 module code
336 # should not know that it's part of a web app
337 $record->{'title'} = '<a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=' . $record->{'biblionumber'} . '"> ' . $record->{'title'} . '</a>';
338 $label_summary->{'_summary'} = $record->{'title'} . " | " . ($record->{'author'} ? $record->{'author'} : 'N/A');
339 $label_summary->{'_item_type'} = C4::Context->preference("item-level_itypes") ? $record->{'itype'} : $record->{'itemtype'};
340 $label_summary->{'_barcode'} = $record->{'barcode'};
341 $label_summary->{'_item_number'} = $item->{'item_number'};
342 $label_summary->{'_label_id'} = $item->{'label_id'};
343 push (@label_summaries, $label_summary);
345 return \@label_summaries;
348 =head2 C4::Creators::Lib::get_card_summary()
350 my $cards = get_card_summary();
352 my $cards = get_card_summary(items => @item_list);
354 This function returns an arrayref whose elements are hashes containing the label_ids of current cards along with the item count
355 for each card upon success and 1 upon failure. Item counts are stored under the key '_item_count' Errors are logged to the Apache log.
356 One parameter is accepted which limits the records returned based on a string containing a valud SQL 'WHERE' filter.
358 NOTE: Do not pass in the keyword 'WHERE.'
362 sub get_card_summary {
365 my @card_summaries = ();
366 my $query = "SELECT CONCAT_WS(', ', surname, firstname) AS name, cardnumber FROM borrowers WHERE borrowernumber=?;";
367 my $sth = C4::Context->dbh->prepare($query);
368 foreach my $item (@{$params{'items'}}) {
370 $sth->execute($item->{'borrower_number'});
372 warn sprintf('Database returned the following error on attempted SELECT: %s', $sth->errstr);
375 my $record = $sth->fetchrow_hashref;
376 my $card_summary->{'_card_number'} = $card_number;
377 $card_summary->{'_summary'} = $record->{'name'};
378 $card_summary->{'borrowernumber'} = $item->{'borrower_number'};
379 $card_summary->{'_label_id'} = $item->{'label_id'};
380 push (@card_summaries, $card_summary);
382 return \@card_summaries;
385 =head2 C4::Creators::Lib::get_barcode_types()
387 my $barcode_types = get_barcode_types();
389 This function returns a reference to an array of hashes containing all barcode types along with their name and description.
393 sub get_barcode_types {
394 return $barcode_types;
397 =head2 C4::Creators::Lib::get_label_types()
399 my $label_types = get_label_types();
401 This function returns a reference to an array of hashes containing all label types along with their name and description.
405 sub get_label_types {
409 =head2 C4::Creators::Lib::get_font_types()
411 my $font_types = get_font_types();
413 This function returns a reference to an array of hashes containing all font types along with their name and description.
421 =head2 C4::Creators::Lib::get_text_justification_types()
423 my $text_justification_types = get_text_justification_types();
425 This function returns a reference to an array of hashes containing all text justification types along with their name and description.
429 sub get_text_justification_types {
430 return $text_justification_types;
433 =head2 C4::Creators::Lib::get_unit_values()
435 my $unit_values = get_unit_values();
437 This function returns a reference to an array of hashes containing all unit types along with their description and multiplier.
438 NOTE: All units are relative to a PostScript Point.
439 There are 72 PS points to the inch.
443 sub get_unit_values {
447 =head2 C4::Creators::Lib::get_output_formats()
449 my $label_output_formats = get_output_formats();
451 This function returns a reference to an array of hashes containing all label output formats along with their description.
455 sub get_output_formats {
456 return $output_formats;
460 =head2 C4::Creators::Lib::get_table_names($search_term)
462 Return an arrayref of an array containing the table names which contain the supplied search term.
466 sub get_table_names {
467 my $search_term = shift;
468 my $dbh = C4::Context->dbh();
469 my $table_names = [];
470 my $sth = $dbh->table_info(undef,undef,"%$search_term%");
471 while (my $info = $sth->fetchrow_hashref()){
472 push (@$table_names, $info->{'TABLE_NAME'});
477 =head2 C4::Creators::Lib::html_table()
479 This function returns an arrayref of an array of hashes contianing the supplied data formatted suitably to
480 be passed off as a template parameter and used to build an html table.
482 my $table = html_table(header_fields, array_of_row_data);
484 table_loop => $table,
490 [% FOREACH table_loo IN table_loop %]
491 [% IF ( table_loo.header_fields ) %]
493 [% FOREACH header_field IN table_loo.header_fields %]
494 <th>[% header_field.field_label %]</th>
499 [% FOREACH text_field IN table_loo.text_fields %]
500 [% IF ( text_field.select_field ) %]
501 <td><input type="checkbox" name="action" value="[% text_field.field_value %]"></td>
503 <td>[% text_field.field_value %]</td>
516 return undef if scalar(@$data) == 0; # no need to generate a table if there is not data to display
519 my @table_columns = ();
520 my ($row_index, $col_index) = (0,0);
521 my $cols = 0; # number of columns to wrap on
523 my $select_value = undef;
524 my $link_field = undef;
526 foreach my $header (@$headers) {
527 my @key = keys %$header;
528 if ($key[0] eq 'select' ) {
529 push (@table_columns, $key[0]);
530 $$fields[$col_index] = {hidden => 0, select_field => 0, field_name => ($key[0]), field_label => $header->{$key[0]}{'label'}};
531 # do special formatting stuff....
532 $select_value = $header->{$key[0]}{'value'};
535 # do special formatting stuff....
536 $link_field->{$key[0]} = ($header->{$key[0]}{'link_field'} == 1 ? 1 : 0);
537 push (@table_columns, $key[0]);
538 $$fields[$col_index] = {hidden => 0, select_field => 0, field_name => ($key[0]), field_label => $header->{$key[0]}{'label'}};
543 $$table[$row_index] = {header_fields => $fields};
545 $field_count *= scalar(@$data); # total fields to be displayed in the table
550 foreach my $db_row (@$data) {
552 foreach my $table_column (@table_columns) {
553 if (grep {$table_column eq $_} keys %$db_row) {
554 $$fields[$col_index] = {hidden => 0, link_field => $link_field->{$table_column}, select_field => 0, field_name => ($table_column . "_tbl"), field_value => $db_row->{$table_column}};
558 elsif ($table_column =~ m/^_((.*)_(.*$))/) { # this a special case
559 my $table_name = get_table_names($2);
560 my $record_set = _SELECT($1, @$table_name[0], $2 . "_id = " . $db_row->{$2 . "_id"});
561 $$fields[$col_index] = {hidden => 0, link_field => $link_field->{$table_column}, select_field => 0, field_name => ($table_column . "_tbl"), field_value => $$record_set[0]{$1}};
565 elsif ($table_column eq 'select' ) {
566 $$fields[$col_index] = {hidden => 0, select_field => 1, field_name => 'select', field_value => $db_row->{$select_value}};
569 $$table[$row_index] = {text_fields => $fields};
582 Chris Nighswonger <cnighswonger AT foundations DOT edu>