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 base qw(Exporter);
30 our @EXPORT = qw(get_all_templates
40 get_text_justification_types
58 #=head2 C4::Creators::Lib::_SELECT()
60 # This function returns a recordset upon success and 1 upon failure. Errors are logged to the Apache log.
64 # my $field_value = _SELECT(field_name, table_name, condition);
70 my $fields_list = $params[0];
71 if (index($fields_list, ' ')==-1 && index($fields_list,',')==-1 && $fields_list ne '*') {
72 $fields_list = "`$fields_list`";
74 my $query = "SELECT $fields_list FROM $params[1]";
75 $params[2] ? $query .= " WHERE $params[2];" : $query .= ';';
76 my $sth = C4::Context->dbh->prepare($query);
77 # $sth->{'TraceLevel'} = 3;
80 warn sprintf('Database returned the following error: %s', $sth->errstr);
84 while (my $row = $sth->fetchrow_hashref()) {
85 push(@$record_set, $row);
91 {type => 'CODE39', name => 'Code 39', desc => 'Translates the characters 0-9, A-Z, \'-\', \'*\', \'+\', \'$\', \'%\', \'/\', \'.\' and \' \' to a barcode pattern.', selected => 0},
92 {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},
93 {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},
94 {type => 'COOP2OF5', name => 'COOP2of5', desc => 'Creates COOP2of5 barcodes from a string consisting of the numeric characters 0-9', selected => 0},
95 {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},
96 # {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},
97 # {type => 'IATA2of5', name => 'IATA2of5', desc => 'Creates IATA2of5 barcodes from a string consisting of the numeric characters 0-9', selected => 0},
98 {type => 'INDUSTRIAL2OF5', name => 'Industrial2of5', desc => 'Creates Industrial2of5 barcodes from a string consisting of the numeric characters 0-9', selected => 0},
99 # {type => 'ITF', name => 'Interleaved2of5', desc => 'Translates the characters 0-9 to a barcodes. These barcodes could also be called 'Interleaved2of5'.', selected => 0},
100 # {type => 'MATRIX2OF5', name => 'Matrix2of5', desc => 'Creates Matrix2of5 barcodes from a string consisting of the numeric characters 0-9', selected => 0},
101 # {type => 'NW7', name => 'NW7', desc => 'Creates a NW7 barcodes from a string consisting of the numeric characters 0-9', selected => 0},
102 # {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},
103 # {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},
107 {type => 'BIB', name => 'Biblio', desc => 'Only the bibliographic data is printed.', selected => 0},
108 {type => 'BARBIB', name => 'Barcode/Biblio', desc => 'Barcode proceeds bibliographic data.', selected => 0},
109 {type => 'BIBBAR', name => 'Biblio/Barcode', desc => 'Bibliographic data proceeds barcode.', selected => 0},
110 {type => 'ALT', name => 'Alternating', desc => 'Barcode and bibliographic data are printed on alternating labels.', selected => 0},
111 {type => 'BAR', name => 'Barcode', desc => 'Only the barcode is printed.', selected => 0},
115 {type => 'TR', name => 'Times-Roman', selected => 0},
116 {type => 'TB', name => 'Times-Bold', selected => 0},
117 {type => 'TI', name => 'Times-Italic', selected => 0},
118 {type => 'TBI', name => 'Times-Bold-Italic', selected => 0},
119 {type => 'C', name => 'Courier', selected => 0},
120 {type => 'CB', name => 'Courier-Bold', selected => 0},
121 {type => 'CO', name => 'Courier-Oblique', selected => 0},
122 {type => 'CBO', name => 'Courier-Bold-Oblique', selected => 0},
123 {type => 'H', name => 'Helvetica', selected => 0},
124 {type => 'HO', name => 'Helvetica-Oblique', selected => 0},
125 {type => 'HB', name => 'Helvetica-Bold', selected => 0},
126 {type => 'HBO', name => 'Helvetica-Bold-Oblique', selected => 0},
129 my $text_justification_types = [
130 {type => 'L', name => 'Left', selected => 0},
131 {type => 'C', name => 'Center', selected => 0},
132 {type => 'R', name => 'Right', selected => 0},
133 # {type => 'F', name => 'Full', selected => 0},
137 {type => 'POINT', desc => 'PostScript Points', value => 1, selected => 0},
138 {type => 'AGATE', desc => 'Adobe Agates', value => 5.1428571, selected => 0},
139 {type => 'INCH', desc => 'US Inches', value => 72, selected => 0},
140 {type => 'MM', desc => 'SI Millimeters', value => 2.83464567, selected => 0},
141 {type => 'CM', desc => 'SI Centimeters', value => 28.3464567, selected => 0},
144 my $output_formats = [
145 {type => 'pdf', desc => 'PDF File'},
146 {type => 'csv', desc => 'CSV File'},
150 my ( $params, $table ) = @_;
151 my @fields = exists $params->{fields} ? @{ $params->{fields} } : ();
153 foreach my $field_name (@fields) {
154 if (index($field_name,' ')==-1 && $field_name ne '*') {
155 push @fields2, "`$field_name`";
157 push @fields2, $field_name;
161 my $query = "SELECT " . ( @fields ? join(', ', @fields ) : '*' ) . " FROM $table";
163 if ( exists $params->{filters} ) {
164 $query .= ' WHERE 1 ';
165 while ( my ( $field, $values ) = each %{ $params->{filters} } ) {
166 if ( ref( $values ) ) {
167 $query .= " AND `$field` IN ( " . ( ('?,') x (@$values-1) ) . "? ) "; # a comma separates elements in a list...
168 push @where_args, @$values;
170 $query .= " AND `$field` = ? ";
171 push @where_args, $values;
175 $query .= (exists $params->{orderby} ? " ORDER BY $params->{orderby} " : '');
176 return ( $query, @where_args );
179 =head2 C4::Creators::Lib::get_all_templates()
181 my $templates = get_all_templates();
183 This function returns a reference to a hash containing all templates upon success and 1 upon failure. Errors are logged to the Apache log.
187 sub get_all_templates {
190 my ( $query, @where_args ) = _build_query( $params, 'creator_templates' );
191 my $sth = C4::Context->dbh->prepare($query);
192 $sth->execute( @where_args );
194 warn sprintf('Database returned the following error: %s', $sth->errstr);
198 while (my $template = $sth->fetchrow_hashref) {
199 push(@templates, $template);
204 =head2 C4::Creators::Lib::get_all_layouts()
206 my $layouts = get_all_layouts();
208 This function returns a reference to a hash containing all layouts upon success and 1 upon failure. Errors are logged to the Apache log.
212 sub get_all_layouts {
215 my ( $query, @where_args ) = _build_query( $params, 'creator_layouts' );
216 my $sth = C4::Context->dbh->prepare($query);
217 $sth->execute( @where_args );
219 warn sprintf('Database returned the following error: %s', $sth->errstr);
223 while (my $layout = $sth->fetchrow_hashref) {
224 push(@layouts, $layout);
229 =head2 C4::Creators::Lib::get_all_profiles()
231 my $profiles = get_all_profiles();
233 my $profiles = get_all_profiles({ fields => [@fields], filters => { filters => [$value1, $value2] } });
235 This function returns an arrayref whose elements are hashes containing all profiles upon success and 1 upon failure. Errors are logged
236 to the Apache log. Two parameters are accepted. The first limits the field(s) returned. This parameter should be string of comma separted
237 fields. ie. "field_1, field_2, ...field_n" The second limits the records returned based on a string containing a valud SQL 'WHERE' filter.
239 NOTE: Do not pass in the keyword 'WHERE.'
243 sub get_all_profiles {
246 my ( $query, @where_args ) = _build_query( $params, 'printers_profile' );
247 my $sth = C4::Context->dbh->prepare($query);
248 $sth->execute( @where_args );
250 warn sprintf('Database returned the following error: %s', $sth->errstr);
254 while (my $profile = $sth->fetchrow_hashref) {
255 push(@profiles, $profile);
260 =head2 C4::Creators::Lib::get_all_image_names()
264 sub get_all_image_names {
265 my $image_names = [];
266 my $query = "SELECT image_name FROM creator_images";
267 my $sth = C4::Context->dbh->prepare($query);
268 # $sth->{'TraceLevel'} = 3 if $debug;
271 warn sprintf('Database returned the following error: %s', $sth->errstr);
274 grep {push @$image_names, {type => $$_[0], name => $$_[0], selected => 0}} @{$sth->fetchall_arrayref([0])};
278 =head2 C4::Creators::Lib::get_batch_summary()
280 my $batches = get_batch_summary();
282 my $batches = get_batch_summary(filter => filter_string);
284 This function returns an arrayref whose elements are hashes containing the batch_ids of current batches along with the item count
285 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.
286 One parameter is accepted which limits the records returned based on a string containing a valud SQL 'WHERE' filter.
288 NOTE: Do not pass in the keyword 'WHERE.'
292 sub get_batch_summary {
295 $params->{fields} = ['batch_id', 'count(batch_id) as _item_count'];
296 my ( $query, @where_args ) = _build_query( $params, 'creator_batches' );
297 $query .= " GROUP BY batch_id";
298 my $sth = C4::Context->dbh->prepare($query);
299 $sth->execute( @where_args );
301 warn sprintf('Database returned the following error on attempted SELECT: %s', $sth->errstr);
304 while (my $batch = $sth->fetchrow_hashref) {
305 push(@batches, $batch);
310 =head2 C4::Creators::Lib::get_label_summary()
312 my $labels = get_label_summary();
314 my $labels = get_label_summary(items => @item_list);
316 This function returns an arrayref whose elements are hashes containing the label_ids of current labels along with the item count
317 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.
318 One parameter is accepted which limits the records returned based on a string containing a valud SQL 'WHERE' filter.
320 NOTE: Do not pass in the keyword 'WHERE.'
324 sub get_label_summary {
326 my $label_number = 0;
327 my @label_summaries = ();
328 my $query = " SELECT b.title, b.author, bi.itemtype, i.barcode, i.itemcallnumber, i.biblionumber, i.itype
329 FROM creator_batches AS c LEFT JOIN items AS i ON (c.item_number=i.itemnumber)
330 LEFT JOIN biblioitems AS bi ON (i.biblioitemnumber=bi.biblioitemnumber)
331 LEFT JOIN biblio AS b ON (bi.biblionumber=b.biblionumber)
332 WHERE itemnumber=? AND batch_id=?;
334 my $sth = C4::Context->dbh->prepare($query);
335 foreach my $item (@{$params{'items'}}) {
337 $sth->execute($item->{'item_number'}, $params{'batch_id'});
339 warn sprintf('Database returned the following error on attempted SELECT: %s', $sth->errstr);
342 my $record = $sth->fetchrow_hashref;
344 $label_summary->{'_label_number'} = $label_number;
345 $record->{'author'} =~ s/[^\.|\w]$// if $record->{'author'}; # strip off ugly trailing chars... but not periods or word chars
346 $record->{'title'} =~ s/\W*$//; # strip off ugly trailing chars
347 # FIXME contructing staff interface URLs should be done *much* higher up the stack - for the most part, C4 module code
348 # should not know that it's part of a web app
349 $record->{'title'} = '<a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=' . $record->{'biblionumber'} . '"> ' . $record->{'title'} . '</a>';
350 $label_summary->{'_summary'} = $record->{'title'} . " | " . ($record->{'author'} ? $record->{'author'} : 'N/A');
351 $label_summary->{'_item_type'} = C4::Context->preference("item-level_itypes") ? $record->{'itype'} : $record->{'itemtype'};
352 $label_summary->{'_barcode'} = $record->{'barcode'};
353 $label_summary->{'_item_number'} = $item->{'item_number'};
354 $label_summary->{'_item_cn'} = $record->{'itemcallnumber'};
355 $label_summary->{'_label_id'} = $item->{'label_id'};
356 push (@label_summaries, $label_summary);
358 return \@label_summaries;
361 =head2 C4::Creators::Lib::get_card_summary()
363 my $cards = get_card_summary();
365 my $cards = get_card_summary(items => @item_list);
367 This function returns an arrayref whose elements are hashes containing the label_ids of current cards along with the item count
368 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.
369 One parameter is accepted which limits the records returned based on a string containing a valud SQL 'WHERE' filter.
371 NOTE: Do not pass in the keyword 'WHERE.'
375 sub get_card_summary {
378 my @card_summaries = ();
379 my $query = "SELECT CONCAT_WS(', ', surname, firstname) AS name, cardnumber FROM borrowers WHERE borrowernumber=?;";
380 my $sth = C4::Context->dbh->prepare($query);
381 foreach my $item (@{$params{'items'}}) {
383 $sth->execute($item->{'borrower_number'});
385 warn sprintf('Database returned the following error on attempted SELECT: %s', $sth->errstr);
388 my $record = $sth->fetchrow_hashref;
389 my $card_summary->{'_card_number'} = $card_number;
390 $card_summary->{'_summary'} = $record->{'name'};
391 $card_summary->{'borrowernumber'} = $item->{'borrower_number'};
392 $card_summary->{'_label_id'} = $item->{'label_id'};
393 push (@card_summaries, $card_summary);
395 return \@card_summaries;
398 =head2 C4::Creators::Lib::get_barcode_types()
400 my $barcode_types = get_barcode_types();
402 This function returns a reference to an array of hashes containing all barcode types along with their name and description.
406 sub get_barcode_types {
407 return $barcode_types;
410 =head2 C4::Creators::Lib::get_label_types()
412 my $label_types = get_label_types();
414 This function returns a reference to an array of hashes containing all label types along with their name and description.
418 sub get_label_types {
422 =head2 C4::Creators::Lib::get_font_types()
424 my $font_types = get_font_types();
426 This function returns a reference to an array of hashes containing all font types along with their name and description.
434 =head2 C4::Creators::Lib::get_text_justification_types()
436 my $text_justification_types = get_text_justification_types();
438 This function returns a reference to an array of hashes containing all text justification types along with their name and description.
442 sub get_text_justification_types {
443 return $text_justification_types;
446 =head2 C4::Creators::Lib::get_unit_values()
448 my $unit_values = get_unit_values();
450 This function returns a reference to an array of hashes containing all unit types along with their description and multiplier.
451 NOTE: All units are relative to a PostScript Point.
452 There are 72 PS points to the inch.
456 sub get_unit_values {
460 =head2 C4::Creators::Lib::get_output_formats()
462 my $label_output_formats = get_output_formats();
464 This function returns a reference to an array of hashes containing all label output formats along with their description.
468 sub get_output_formats {
469 return $output_formats;
473 =head2 C4::Creators::Lib::get_table_names($search_term)
475 Return an arrayref of an array containing the table names which contain the supplied search term.
479 sub get_table_names {
480 my $search_term = shift;
481 my $dbh = C4::Context->dbh();
482 my $table_names = [];
483 my $sth = $dbh->table_info(undef,undef,"%$search_term%");
484 while (my $info = $sth->fetchrow_hashref()){
485 push (@$table_names, $info->{'TABLE_NAME'});
490 =head2 C4::Creators::Lib::html_table()
492 This function returns an arrayref of an array of hashes contianing the supplied data formatted suitably to
493 be passed off as a template parameter and used to build an html table.
495 my $table = html_table(header_fields, array_of_row_data);
497 table_loop => $table,
503 [% FOREACH table_loo IN table_loop %]
504 [% IF ( table_loo.header_fields ) %]
506 [% FOREACH header_field IN table_loo.header_fields %]
507 <th>[% header_field.field_label %]</th>
512 [% FOREACH text_field IN table_loo.text_fields %]
513 [% IF ( text_field.select_field ) %]
514 <td><input type="checkbox" name="action" value="[% text_field.field_value %]"></td>
516 <td>[% text_field.field_value %]</td>
529 return undef if scalar(@$data) == 0; # no need to generate a table if there is not data to display
532 my @table_columns = ();
533 my ($row_index, $col_index) = (0,0);
534 my $cols = 0; # number of columns to wrap on
536 my $select_value = undef;
537 my $link_field = undef;
539 foreach my $header (@$headers) {
540 my @key = keys %$header;
541 if ($key[0] eq 'select' ) {
542 push (@table_columns, $key[0]);
543 $$fields[$col_index] = {hidden => 0, select_field => 0, field_name => ($key[0]), field_label => $header->{$key[0]}{'label'}};
544 # do special formatting stuff....
545 $select_value = $header->{$key[0]}{'value'};
548 # do special formatting stuff....
549 $link_field->{$key[0]} = ($header->{$key[0]}{'link_field'} == 1 ? 1 : 0);
550 push (@table_columns, $key[0]);
551 $$fields[$col_index] = {hidden => 0, select_field => 0, field_name => ($key[0]), field_label => $header->{$key[0]}{'label'}};
556 $$table[$row_index] = {header_fields => $fields};
558 $field_count *= scalar(@$data); # total fields to be displayed in the table
563 foreach my $db_row (@$data) {
565 foreach my $table_column (@table_columns) {
566 if (grep {$table_column eq $_} keys %$db_row) {
567 $$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}};
571 elsif ($table_column =~ m/^_((.*)_(.*$))/) { # this a special case
572 my $table_name = get_table_names('creator_'.$2); #Bug 14143 fix to remove ambiguity with table 'club_template_enrollment_fields'
573 my $record_set = _SELECT($1, @$table_name[0], $2 . "_id = " . $db_row->{$2 . "_id"});
574 $$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}};
578 elsif ($table_column eq 'select' ) {
579 $$fields[$col_index] = {hidden => 0, select_field => 1, field_name => 'select', field_value => $db_row->{$select_value}};
582 $$table[$row_index] = {text_fields => $fields};
595 Chris Nighswonger <cnighswonger AT foundations DOT edu>