1 package C4::Labels::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 under the
8 # terms of the GNU General Public License as published by the Free Software
9 # Foundation; either version 2 of the License, or (at your option) any later
12 # Koha is distributed in the hope that it will be useful, but WITHOUT ANY
13 # WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
14 # A PARTICULAR PURPOSE. See the GNU General Public License for more details.
16 # You should have received a copy of the GNU General Public License along with
17 # Koha; if not, write to the Free Software Foundation, Inc., 59 Temple Place,
18 # Suite 330, Boston, MA 02111-1307 USA
23 use Sys::Syslog qw(syslog);
30 use version; our $VERSION = qv('1.0.0_1');
31 use base qw(Exporter);
32 our @EXPORT_OK = qw(get_all_templates
40 get_text_justification_types
41 get_label_output_formats
49 #=head2 C4::Labels::Lib::_SELECT()
51 # This function returns a recordset upon success and 1 upon failure. Errors are logged to the syslog.
55 # my $field_value = _SELECT(field_name, table_name, condition);
61 my $query = "SELECT $params[0] FROM $params[1]";
62 $params[2] ? $query .= " WHERE $params[2];" : $query .= ';';
63 my $sth = C4::Context->dbh->prepare($query);
64 # $sth->{'TraceLevel'} = 3;
67 syslog("LOG_ERR", "C4::Labels::Lib::get_single_field_value : Database returned the following error: %s", $sth->errstr);
71 while (my $row = $sth->fetchrow_hashref()) {
72 push(@$record_set, $row);
78 {type => 'CODE39', name => 'Code 39', desc => 'Translates the characters 0-9, A-Z, \'-\', \'*\', \'+\', \'$\', \'%\', \'/\', \'.\' and \' \' to a barcode pattern.', selected => 0},
79 {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},
80 {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},
81 {type => 'COOP2OF5', name => 'COOP2of5', desc => 'Creates COOP2of5 barcodes from a string consisting of the numeric characters 0-9', selected => 0},
82 # {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},
83 # {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},
84 # {type => 'IATA2of5', name => 'IATA2of5', desc => 'Creates IATA2of5 barcodes from a string consisting of the numeric characters 0-9', selected => 0},
85 {type => 'INDUSTRIAL2OF5', name => 'Industrial2of5', desc => 'Creates Industrial2of5 barcodes from a string consisting of the numeric characters 0-9', selected => 0},
86 # {type => 'ITF', name => 'Interleaved2of5', desc => 'Translates the characters 0-9 to a barcodes. These barcodes could also be called 'Interleaved2of5'.', selected => 0},
87 # {type => 'MATRIX2OF5', name => 'Matrix2of5', desc => 'Creates Matrix2of5 barcodes from a string consisting of the numeric characters 0-9', selected => 0},
88 # {type => 'NW7', name => 'NW7', desc => 'Creates a NW7 barcodes from a string consisting of the numeric characters 0-9', selected => 0},
89 # {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},
90 # {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},
94 {type => 'BIB', name => 'Biblio', desc => 'Only the bibliographic data is printed.', selected => 0},
95 {type => 'BARBIB', name => 'Barcode/Biblio', desc => 'Barcode proceeds bibliographic data.', selected => 0},
96 {type => 'BIBBAR', name => 'Biblio/Barcode', desc => 'Bibliographic data proceeds barcode.', selected => 0},
97 {type => 'ALT', name => 'Alternating', desc => 'Barcode and bibliographic data are printed on alternating labels.', selected => 0},
98 {type => 'BAR', name => 'Barcode', desc => 'Only the barcode is printed.', selected => 0},
102 {type => 'TR', name => 'Times-Roman', selected => 0},
103 {type => 'TB', name => 'Times-Bold', selected => 0},
104 {type => 'TI', name => 'Times-Italic', selected => 0},
105 {type => 'TBI', name => 'Times-Bold-Italic', selected => 0},
106 {type => 'C', name => 'Courier', selected => 0},
107 {type => 'CB', name => 'Courier-Bold', selected => 0},
108 {type => 'CO', name => 'Courier-Oblique', selected => 0},
109 {type => 'CBO', name => 'Courier-Bold-Oblique', selected => 0},
110 {type => 'H', name => 'Helvetica', selected => 0},
111 {type => 'HB', name => 'Helvetica-Bold', selected => 0},
112 {type => 'HBO', name => 'Helvetica-Bold-Oblique', selected => 0},
115 my $text_justification_types = [
116 {type => 'L', name => 'Left', selected => 0},
117 {type => 'C', name => 'Center', selected => 0},
118 {type => 'R', name => 'Right', selected => 0},
119 # {type => 'F', name => 'Full', selected => 0},
123 {type => 'POINT', desc => 'PostScript Points', value => 1, selected => 0},
124 {type => 'AGATE', desc => 'Adobe Agates', value => 5.1428571, selected => 0},
125 {type => 'INCH', desc => 'US Inches', value => 72, selected => 0},
126 {type => 'MM', desc => 'SI Millimeters', value => 2.83464567, selected => 0},
127 {type => 'CM', desc => 'SI Centimeters', value => 28.3464567, selected => 0},
130 my $label_output_formats = [
131 {type => 'pdf', desc => 'PDF File'},
132 {type => 'csv', desc => 'CSV File'},
135 =head2 C4::Labels::Lib::get_all_templates()
137 This function returns a reference to a hash containing all templates upon success and 1 upon failure. Errors are logged to the syslog.
141 my $templates = get_all_templates();
145 sub get_all_templates {
148 my $query = "SELECT " . ($params{'field_list'} ? $params{'field_list'} : '*') . " FROM labels_templates";
149 $query .= ($params{'filter'} ? " WHERE $params{'filter'};" : ';');
150 my $sth = C4::Context->dbh->prepare($query);
153 syslog("LOG_ERR", "C4::Labels::Lib::get_all_templates : Database returned the following error: %s", $sth->errstr);
157 while (my $template = $sth->fetchrow_hashref) {
158 push(@templates, $template);
163 =head2 C4::Labels::Lib::get_all_layouts()
165 This function returns a reference to a hash containing all layouts upon success and 1 upon failure. Errors are logged to the syslog.
169 my $layouts = get_all_layouts();
173 sub get_all_layouts {
176 #my $query = "SELECT * FROM labels_layouts;";
177 my $query = "SELECT " . ($params{'field_list'} ? $params{'field_list'} : '*') . " FROM labels_layouts";
178 $query .= ($params{'filter'} ? " WHERE $params{'filter'};" : ';');
179 my $sth = C4::Context->dbh->prepare($query);
182 syslog("LOG_ERR", "C4::Labels::Lib::get_all_layouts : Database returned the following error: %s", $sth->errstr);
186 while (my $layout = $sth->fetchrow_hashref) {
187 push(@layouts, $layout);
192 =head2 C4::Labels::Lib::get_all_profiles()
194 This function returns an arrayref whose elements are hashes containing all profiles upon success and 1 upon failure. Errors are logged
195 to the syslog. Two parameters are accepted. The first limits the field(s) returned. This parameter should be string of comma separted
196 fields. ie. "field_1, field_2, ...field_n" The second limits the records returned based on a string containing a valud SQL 'WHERE' filter.
197 NOTE: Do not pass in the keyword 'WHERE.'
201 my $profiles = get_all_profiles();
202 my $profiles = get_all_profiles(field_list => field_list, filter => filter_string);
206 sub get_all_profiles {
209 my $query = "SELECT " . ($params{'field_list'} ? $params{'field_list'} : '*') . " FROM printers_profile";
210 $query .= ($params{'filter'} ? " WHERE $params{'filter'};" : ';');
211 my $sth = C4::Context->dbh->prepare($query);
212 # $sth->{'TraceLevel'} = 3 if $debug;
215 syslog("LOG_ERR", "C4::Labels::Lib::get_all_profiles : Database returned the following error: %s", $sth->errstr);
219 while (my $profile = $sth->fetchrow_hashref) {
220 push(@profiles, $profile);
225 =head2 C4::Labels::Lib::get_batch_summary()
227 This function returns an arrayref whose elements are hashes containing the batch_ids of current batches along with the item count
228 for each batch upon success and 1 upon failure. Item counts are stored under the key '_item_count' Errors are logged to the syslog.
229 One parameter is accepted which limits the records returned based on a string containing a valud SQL 'WHERE' filter.
231 NOTE: Do not pass in the keyword 'WHERE.'
235 my $batches = get_batch_summary();
236 my $batches = get_batch_summary(filter => filter_string);
240 sub get_batch_summary {
243 my $query = "SELECT DISTINCT batch_id FROM labels_batches";
244 $query .= ($params{'filter'} ? " WHERE $params{'filter'};" : ';');
245 my $sth = C4::Context->dbh->prepare($query);
246 # $sth->{'TraceLevel'} = 3;
249 syslog("LOG_ERR", "C4::Labels::Lib::get_batch_summary : Database returned the following error on attempted SELECT: %s", $sth->errstr);
253 while (my $batch = $sth->fetchrow_hashref) {
254 my $query = "SELECT count(item_number) FROM labels_batches WHERE batch_id=?;";
255 my $sth1 = C4::Context->dbh->prepare($query);
256 $sth1->execute($batch->{'batch_id'});
258 syslog("LOG_ERR", "C4::Labels::Lib::get_batch_summary : Database returned the following error on attempted SELECT count: %s", $sth1->errstr);
261 my $count = $sth1->fetchrow_arrayref;
262 $batch->{'_item_count'} = @$count[0];
263 push(@batches, $batch);
268 =head2 C4::Labels::Lib::get_label_summary()
270 This function returns an arrayref whose elements are hashes containing the label_ids of current labels along with the item count
271 for each label upon success and 1 upon failure. Item counts are stored under the key '_item_count' Errors are logged to the syslog.
272 One parameter is accepted which limits the records returned based on a string containing a valud SQL 'WHERE' filter.
274 NOTE: Do not pass in the keyword 'WHERE.'
278 my $labels = get_label_summary();
279 my $labels = get_label_summary(items => @item_list);
283 sub get_label_summary {
285 my $label_number = 0;
286 my @label_summaries = ();
287 my $query = "SELECT b.title, b.author, bi.itemtype, i.barcode, i.biblionumber FROM biblio AS b, biblioitems AS bi ,items AS i, labels_batches AS l WHERE itemnumber=? AND l.item_number=i.itemnumber AND i.biblioitemnumber=bi.biblioitemnumber AND bi.biblionumber=b.biblionumber AND l.batch_id=?;";
288 my $sth = C4::Context->dbh->prepare($query);
289 foreach my $item (@{$params{'items'}}) {
291 $sth->execute($item->{'item_number'}, $params{'batch_id'});
293 syslog("LOG_ERR", "C4::Labels::Lib::get_label_summary : Database returned the following error on attempted SELECT: %s", $sth->errstr);
296 my $record = $sth->fetchrow_hashref;
297 my $label_summary->{'_label_number'} = $label_number;
298 $record->{'author'} =~ s/[^\.|\w]$// if $record->{'author'}; # strip off ugly trailing chars... but not periods or word chars
299 $record->{'title'} =~ s/\W*$//; # strip off ugly trailing chars
300 $record->{'title'} = '<a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=' . $record->{'biblionumber'} . '"> ' . $record->{'title'} . '</a>';
301 $label_summary->{'_summary'} = $record->{'title'} . " | " . ($record->{'author'} ? $record->{'author'} : 'N/A');
302 $label_summary->{'_item_type'} = $record->{'itemtype'};
303 $label_summary->{'_barcode'} = $record->{'barcode'};
304 $label_summary->{'_item_number'} = $item->{'item_number'};
305 $label_summary->{'_label_id'} = $item->{'label_id'};
306 push (@label_summaries, $label_summary);
308 return \@label_summaries;
311 =head2 C4::Labels::Lib::get_barcode_types()
313 This function returns a reference to an array of hashes containing all barcode types along with their name and description.
317 my $barcode_types = get_barcode_types();
321 sub get_barcode_types {
322 return $barcode_types;
325 =head2 C4::Labels::Lib::get_label_types()
327 This function returns a reference to an array of hashes containing all label types along with their name and description.
331 my $label_types = get_label_types();
335 sub get_label_types {
339 =head2 C4::Labels::Lib::get_font_types()
341 This function returns a reference to an array of hashes containing all font types along with their name and description.
345 my $font_types = get_font_types();
353 =head2 C4::Labels::Lib::get_text_justification_types()
355 This function returns a reference to an array of hashes containing all text justification types along with their name and description.
359 my $text_justification_types = get_text_justification_types();
363 sub get_text_justification_types {
364 return $text_justification_types;
367 =head2 C4::Labels::Lib::get_unit_values()
369 This function returns a reference to an array of hashes containing all unit types along with their description and multiplier. NOTE: All units are relative to a PostScript Point.
370 There are 72 PS points to the inch.
374 my $unit_values = get_unit_values();
378 sub get_unit_values {
382 =head2 C4::Labels::Lib::get_label_output_formats()
384 This function returns a reference to an array of hashes containing all label output formats along with their description.
388 my $label_output_formats = get_label_output_formats();
392 sub get_label_output_formats {
393 return $label_output_formats;
396 =head2 C4::Labels::Lib::get_column_names($table_name)
398 Return an arrayref of an array containing the column names of the supplied table.
402 sub get_column_names {
404 my $dbh = C4::Context->dbh();
405 my $column_names = [];
406 my $sth = $dbh->column_info(undef,undef,$table,'%');
407 while (my $info = $sth->fetchrow_hashref()){
408 $$column_names[$info->{'ORDINAL_POSITION'}] = $info->{'COLUMN_NAME'};
410 return $column_names;
413 =head2 C4::Labels::Lib::get_table_names($search_term)
415 Return an arrayref of an array containing the table names which contain the supplied search term.
419 sub get_table_names {
420 my $search_term = shift;
421 my $dbh = C4::Context->dbh();
422 my $table_names = [];
423 my $sth = $dbh->table_info(undef,undef,"%$search_term%");
424 while (my $info = $sth->fetchrow_hashref()){
425 push (@$table_names, $info->{'TABLE_NAME'});
430 =head2 C4::Labels::Lib::html_table()
432 This function returns an arrayref of an array of hashes contianing the supplied data formatted suitably to
433 be passed off as a T::P template parameter and used to build an html table.
437 my $table = html_table(header_fields, array_of_row_data);
444 return undef if scalar(@$data) == 0; # no need to generate a table if there is not data to display
448 my @table_columns = ();
449 my ($row_index, $col_index) = (0,0);
450 my $cols = 0; # number of columns to wrap on
452 my $select_value = undef;
453 my $link_field = undef;
455 foreach my $header (@$headers) {
456 my @key = keys %$header;
457 if ($key[0] eq 'select' ) {
458 push (@table_columns, $key[0]);
459 $$fields[$col_index] = {hidden => 0, select_field => 0, field_name => ($key[0]), field_label => $header->{$key[0]}{'label'}};
460 # do special formatting stuff....
461 $select_value = $header->{$key[0]}{'value'};
464 # do special formatting stuff....
465 $link_field->{$key[0]} = ($header->{$key[0]}{'link_field'} == 1 ? 1 : 0);
466 push (@table_columns, $key[0]);
467 $$fields[$col_index] = {hidden => 0, select_field => 0, field_name => ($key[0]), field_label => $header->{$key[0]}{'label'}};
472 $$table[$row_index] = {header_fields => $fields};
474 $field_count *= scalar(@$data); # total fields to be displayed in the table
479 foreach my $db_row (@$data) {
481 foreach my $table_column (@table_columns) {
482 if (grep {$table_column eq $_} keys %$db_row) {
483 $$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}};
487 elsif ($table_column =~ m/^_((.*)_(.*$))/) { # this a special case
488 my $table_name = get_table_names($2);
489 my $record_set = _SELECT($1, @$table_name[0], $2 . "_id = " . $db_row->{$2 . "_id"});
490 $$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}};
494 elsif ($table_column eq 'select' ) {
495 $$fields[$col_index] = {hidden => 0, select_field => 1, field_name => 'select', field_value => $db_row->{$select_value}};
498 $$table[$row_index] = {text_fields => $fields};
511 Chris Nighswonger <cnighswonger AT foundations DOT edu>