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>.
21 use Storable qw( dclone );
23 use autouse 'Data::Dumper' => qw(Dumper);
28 use base qw(Exporter);
29 our @EXPORT = qw(get_all_templates
39 get_text_justification_types
57 #=head2 C4::Creators::Lib::_SELECT()
59 # This function returns a recordset upon success and 1 upon failure. Errors are logged to the Apache log.
63 # my $field_value = _SELECT(field_name, table_name, condition);
69 my $fieldname = _add_backtics($params[0]);
70 my $query = "SELECT $fieldname FROM $params[1]";
71 $params[2] ? $query .= " WHERE $params[2];" : $query .= ';';
72 my $sth = C4::Context->dbh->prepare($query);
73 # $sth->{'TraceLevel'} = 3;
76 warn sprintf('Database returned the following error: %s', $sth->errstr);
80 while (my $row = $sth->fetchrow_hashref()) {
81 push(@$record_set, $row);
88 s/(?:^|\b)(\w+)(?:\b|$)/`$1`/g for @args;
89 # Too bad that we need to correct a few exceptions: aggregate functions
90 my @aggregates = ( 'COUNT', 'MAX', 'MIN', 'SUM' ); # add when needed..
91 foreach my $aggr (@aggregates) {
92 s/`$aggr`\(/$aggr\(/gi for @args;
95 s/(`|\))\s+`AS`\s+`/$1 AS `/gi for @args;
96 return wantarray ? @args : $args[0];
100 {type => 'CODE39', name => 'Code 39', desc => 'Translates the characters 0-9, A-Z, \'-\', \'*\', \'+\', \'$\', \'%\', \'/\', \'.\' and \' \' to a barcode pattern.', selected => 0},
101 {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},
102 {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},
103 {type => 'COOP2OF5', name => 'COOP2of5', desc => 'Creates COOP2of5 barcodes from a string consisting of the numeric characters 0-9', selected => 0},
104 {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},
105 # {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},
106 # {type => 'IATA2of5', name => 'IATA2of5', desc => 'Creates IATA2of5 barcodes from a string consisting of the numeric characters 0-9', selected => 0},
107 {type => 'INDUSTRIAL2OF5', name => 'Industrial2of5', desc => 'Creates Industrial2of5 barcodes from a string consisting of the numeric characters 0-9', selected => 0},
108 # {type => 'ITF', name => 'Interleaved2of5', desc => 'Translates the characters 0-9 to a barcodes. These barcodes could also be called 'Interleaved2of5'.', selected => 0},
109 # {type => 'MATRIX2OF5', name => 'Matrix2of5', desc => 'Creates Matrix2of5 barcodes from a string consisting of the numeric characters 0-9', selected => 0},
110 # {type => 'NW7', name => 'NW7', desc => 'Creates a NW7 barcodes from a string consisting of the numeric characters 0-9', selected => 0},
111 # {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},
112 # {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},
116 {type => 'BIB', name => 'Biblio', desc => 'Only the bibliographic data is printed.', selected => 0},
117 {type => 'BARBIB', name => 'Barcode/Biblio', desc => 'Barcode proceeds bibliographic data.', selected => 0},
118 {type => 'BIBBAR', name => 'Biblio/Barcode', desc => 'Bibliographic data proceeds barcode.', selected => 0},
119 {type => 'ALT', name => 'Alternating', desc => 'Barcode and bibliographic data are printed on alternating labels.', selected => 0},
120 {type => 'BAR', name => 'Barcode', desc => 'Only the barcode is printed.', selected => 0},
124 {type => 'TR', name => 'Times-Roman', selected => 0},
125 {type => 'TB', name => 'Times-Bold', selected => 0},
126 {type => 'TI', name => 'Times-Italic', selected => 0},
127 {type => 'TBI', name => 'Times-Bold-Italic', selected => 0},
128 {type => 'C', name => 'Courier', selected => 0},
129 {type => 'CB', name => 'Courier-Bold', selected => 0},
130 {type => 'CO', name => 'Courier-Oblique', selected => 0},
131 {type => 'CBO', name => 'Courier-Bold-Oblique', selected => 0},
132 {type => 'H', name => 'Helvetica', selected => 0},
133 {type => 'HO', name => 'Helvetica-Oblique', selected => 0},
134 {type => 'HB', name => 'Helvetica-Bold', selected => 0},
135 {type => 'HBO', name => 'Helvetica-Bold-Oblique', selected => 0},
138 my $text_justification_types = [
139 {type => 'L', name => 'Left', selected => 0},
140 {type => 'C', name => 'Center', selected => 0},
141 {type => 'R', name => 'Right', selected => 0},
142 # {type => 'F', name => 'Full', selected => 0},
146 {type => 'POINT', desc => 'PostScript Points', value => 1, selected => 0},
147 {type => 'AGATE', desc => 'Adobe Agates', value => 5.1428571, selected => 0},
148 {type => 'INCH', desc => 'US Inches', value => 72, selected => 0},
149 {type => 'MM', desc => 'SI Millimeters', value => 2.83464567, selected => 0},
150 {type => 'CM', desc => 'SI Centimeters', value => 28.3464567, selected => 0},
153 my $output_formats = [
154 {type => 'pdf', desc => 'PDF File'},
155 {type => 'csv', desc => 'CSV File'},
159 my ( $params, $table ) = @_;
160 my @fields = exists $params->{fields} ? _add_backtics( @{ $params->{fields} } ) : ('*');
161 my $query = "SELECT " . 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);
270 warn sprintf('Database returned the following error: %s', $sth->errstr);
273 grep {push @$image_names, {type => $$_[0], name => $$_[0], selected => 0}} @{$sth->fetchall_arrayref([0])};
277 =head2 C4::Creators::Lib::get_batch_summary()
279 my $batches = get_batch_summary();
281 my $batches = get_batch_summary(filter => filter_string);
283 This function returns an arrayref whose elements are hashes containing the batch_ids of current batches along with the item count
284 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.
285 One parameter is accepted which limits the records returned based on a string containing a valud SQL 'WHERE' filter.
287 NOTE: Do not pass in the keyword 'WHERE.'
291 sub get_batch_summary {
294 $params->{fields} = ['batch_id', 'description', 'count(batch_id) as _item_count'];
295 my ( $query, @where_args ) = _build_query( $params, 'creator_batches' );
296 $query .= " GROUP BY batch_id, description";
297 my $sth = C4::Context->dbh->prepare($query);
298 $sth->execute( @where_args );
300 warn sprintf('Database returned the following error on attempted SELECT: %s', $sth->errstr);
303 while (my $batch = $sth->fetchrow_hashref) {
304 push(@batches, $batch);
309 =head2 C4::Creators::Lib::get_label_summary()
311 my $labels = get_label_summary();
313 my $labels = get_label_summary(items => @item_list);
315 This function returns an arrayref whose elements are hashes containing the label_ids of current labels along with the item count
316 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.
317 One parameter is accepted which limits the records returned based on a string containing a valud SQL 'WHERE' filter.
319 NOTE: Do not pass in the keyword 'WHERE.'
323 sub get_label_summary {
325 my $label_number = 0;
326 my @label_summaries = ();
327 my $query = " SELECT b.title, b.author, bi.itemtype, i.barcode, i.itemcallnumber, i.biblionumber, i.itype
328 FROM creator_batches AS c LEFT JOIN items AS i ON (c.item_number=i.itemnumber)
329 LEFT JOIN biblioitems AS bi ON (i.biblioitemnumber=bi.biblioitemnumber)
330 LEFT JOIN biblio AS b ON (bi.biblionumber=b.biblionumber)
331 WHERE itemnumber=? AND batch_id=?;
333 my $sth = C4::Context->dbh->prepare($query);
334 foreach my $item (@{$params{'items'}}) {
336 $sth->execute($item->{'item_number'}, $params{'batch_id'});
338 warn sprintf('Database returned the following error on attempted SELECT: %s', $sth->errstr);
341 my $record = $sth->fetchrow_hashref;
343 $label_summary->{'_label_number'} = $label_number;
344 $record->{'author'} =~ s/[^\.|\w]$// if $record->{'author'}; # strip off ugly trailing chars... but not periods or word chars
345 $record->{'title'} =~ s/\W*$//; # strip off ugly trailing chars
346 # FIXME contructing staff interface URLs should be done *much* higher up the stack - for the most part, C4 module code
347 # should not know that it's part of a web app
348 $record->{'title'} = '<a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=' . $record->{'biblionumber'} . '"> ' . $record->{'title'} . '</a>';
349 $label_summary->{'_summary'} = $record->{'title'} . " | " . ($record->{'author'} ? $record->{'author'} : 'N/A');
350 $label_summary->{'_item_type'} = C4::Context->preference("item-level_itypes") ? $record->{'itype'} : $record->{'itemtype'};
351 $label_summary->{'_barcode'} = $record->{'barcode'};
352 $label_summary->{'_item_number'} = $item->{'item_number'};
353 $label_summary->{'_item_cn'} = $record->{'itemcallnumber'};
354 $label_summary->{'_label_id'} = $item->{'label_id'};
355 push (@label_summaries, $label_summary);
357 return \@label_summaries;
360 =head2 C4::Creators::Lib::get_card_summary()
362 my $cards = get_card_summary();
364 my $cards = get_card_summary(items => @item_list);
366 This function returns an arrayref whose elements are hashes containing the label_ids of current cards along with the item count
367 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.
368 One parameter is accepted which limits the records returned based on a string containing a valud SQL 'WHERE' filter.
370 NOTE: Do not pass in the keyword 'WHERE.'
374 sub get_card_summary {
377 my @card_summaries = ();
378 my $query = "SELECT CONCAT_WS(', ', surname, firstname) AS name, cardnumber FROM borrowers WHERE borrowernumber=?;";
379 my $sth = C4::Context->dbh->prepare($query);
380 foreach my $item (@{$params{'items'}}) {
382 $sth->execute($item->{'borrower_number'});
384 warn sprintf('Database returned the following error on attempted SELECT: %s', $sth->errstr);
387 my $record = $sth->fetchrow_hashref;
388 my $card_summary->{'_card_number'} = $card_number;
389 $card_summary->{'_summary'} = $record->{'name'};
390 $card_summary->{'borrowernumber'} = $item->{'borrower_number'};
391 $card_summary->{'_label_id'} = $item->{'label_id'};
392 push (@card_summaries, $card_summary);
394 return \@card_summaries;
397 =head2 C4::Creators::Lib::get_barcode_types()
399 my $barcode_types = get_barcode_types();
401 This function returns a reference to an array of hashes containing all barcode types along with their name and description.
405 sub get_barcode_types {
406 return dclone $barcode_types;
409 =head2 C4::Creators::Lib::get_label_types()
411 my $label_types = get_label_types();
413 This function returns a reference to an array of hashes containing all label types along with their name and description.
417 sub get_label_types {
418 return dclone $label_types;
421 =head2 C4::Creators::Lib::get_font_types()
423 my $font_types = get_font_types();
425 This function returns a reference to an array of hashes containing all font types along with their name and description.
430 return dclone $font_types;
433 =head2 C4::Creators::Lib::get_text_justification_types()
435 my $text_justification_types = get_text_justification_types();
437 This function returns a reference to an array of hashes containing all text justification types along with their name and description.
441 sub get_text_justification_types {
442 return dclone $text_justification_types;
445 =head2 C4::Creators::Lib::get_unit_values()
447 my $unit_values = get_unit_values();
449 This function returns a reference to an array of hashes containing all unit types along with their description and multiplier.
450 NOTE: All units are relative to a PostScript Point.
451 There are 72 PS points to the inch.
455 sub get_unit_values {
456 return dclone $unit_values;
459 =head2 C4::Creators::Lib::get_output_formats()
461 my $label_output_formats = get_output_formats();
463 This function returns a reference to an array of hashes containing all label output formats along with their description.
467 sub get_output_formats {
468 return dclone $output_formats;
472 =head2 C4::Creators::Lib::get_table_names($search_term)
474 Return an arrayref of an array containing the table names which contain the supplied search term.
478 sub get_table_names {
479 my $search_term = shift;
480 my $dbh = C4::Context->dbh();
481 my $table_names = [];
482 my $sth = $dbh->table_info(undef,undef,"%$search_term%");
483 while (my $info = $sth->fetchrow_hashref()){
484 push (@$table_names, $info->{'TABLE_NAME'});
489 =head2 C4::Creators::Lib::html_table()
491 This function returns an arrayref of an array of hashes contianing the supplied data formatted suitably to
492 be passed off as a template parameter and used to build an html table.
494 my $table = html_table(header_fields, array_of_row_data);
496 table_loop => $table,
502 [% FOREACH table_loo IN table_loop %]
503 [% IF ( table_loo.header_fields ) %]
505 [% FOREACH header_field IN table_loo.header_fields %]
506 <th>[% header_field.field_label %]</th>
511 [% FOREACH text_field IN table_loo.text_fields %]
512 [% IF ( text_field.select_field ) %]
513 <td><input type="checkbox" name="action" value="[% text_field.field_value %]"></td>
515 <td>[% text_field.field_value %]</td>
528 return if scalar(@$data) == 0; # no need to generate a table if there is not data to display
531 my @table_columns = ();
532 my ($row_index, $col_index) = (0,0);
533 my $cols = 0; # number of columns to wrap on
535 my $select_value = undef;
536 my $link_field = undef;
538 foreach my $header (@$headers) {
539 my @key = keys %$header;
540 if ($key[0] eq 'select' ) {
541 push (@table_columns, $key[0]);
542 $$fields[$col_index] = {hidden => 0, select_field => 0, field_name => ($key[0]), field_label => $header->{$key[0]}{'label'}};
543 # do special formatting stuff....
544 $select_value = $header->{$key[0]}{'value'};
547 # do special formatting stuff....
548 $link_field->{$key[0]} = ($header->{$key[0]}{'link_field'} == 1 ? 1 : 0);
549 push (@table_columns, $key[0]);
550 $$fields[$col_index] = {hidden => 0, select_field => 0, field_name => ($key[0]), field_label => $header->{$key[0]}{'label'}};
555 $$table[$row_index] = {header_fields => $fields};
557 $field_count *= scalar(@$data); # total fields to be displayed in the table
562 foreach my $db_row (@$data) {
564 foreach my $table_column (@table_columns) {
565 if (grep {$table_column eq $_} keys %$db_row) {
566 $$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}};
570 elsif ($table_column =~ m/^_((.*)_(.*$))/) { # this a special case
571 my $table_name = get_table_names('creator_'.$2); #Bug 14143 fix to remove ambiguity with table 'club_template_enrollment_fields'
572 my $record_set = _SELECT($1, @$table_name[0], $2 . "_id = " . $db_row->{$2 . "_id"});
573 $$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}};
577 elsif ($table_column eq 'select' ) {
578 $$fields[$col_index] = {hidden => 0, select_field => 1, field_name => 'select', field_value => $db_row->{$select_value}};
581 $$table[$row_index] = {text_fields => $fields};
594 Chris Nighswonger <cnighswonger AT foundations DOT edu>