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
29 use version; our $VERSION = qv('1.0.0_1');
30 use base qw(Exporter);
31 our @EXPORT_OK = qw(get_all_templates
39 get_text_justification_types
40 get_label_output_formats
48 #=head2 C4::Labels::Lib::_SELECT()
50 # This function returns a recordset upon success and 1 upon failure. Errors are logged to the Apache log.
54 # my $field_value = _SELECT(field_name, table_name, condition);
60 my $query = "SELECT $params[0] FROM $params[1]";
61 $params[2] ? $query .= " WHERE $params[2];" : $query .= ';';
62 my $sth = C4::Context->dbh->prepare($query);
63 # $sth->{'TraceLevel'} = 3;
66 warn sprintf('Database returned the following error: %s', $sth->errstr);
70 while (my $row = $sth->fetchrow_hashref()) {
71 push(@$record_set, $row);
77 {type => 'CODE39', name => 'Code 39', desc => 'Translates the characters 0-9, A-Z, \'-\', \'*\', \'+\', \'$\', \'%\', \'/\', \'.\' and \' \' to a barcode pattern.', selected => 0},
78 {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},
79 {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},
80 {type => 'COOP2OF5', name => 'COOP2of5', desc => 'Creates COOP2of5 barcodes from a string consisting of the numeric characters 0-9', selected => 0},
81 # {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},
82 # {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},
83 # {type => 'IATA2of5', name => 'IATA2of5', desc => 'Creates IATA2of5 barcodes from a string consisting of the numeric characters 0-9', selected => 0},
84 {type => 'INDUSTRIAL2OF5', name => 'Industrial2of5', desc => 'Creates Industrial2of5 barcodes from a string consisting of the numeric characters 0-9', selected => 0},
85 # {type => 'ITF', name => 'Interleaved2of5', desc => 'Translates the characters 0-9 to a barcodes. These barcodes could also be called 'Interleaved2of5'.', selected => 0},
86 # {type => 'MATRIX2OF5', name => 'Matrix2of5', desc => 'Creates Matrix2of5 barcodes from a string consisting of the numeric characters 0-9', selected => 0},
87 # {type => 'NW7', name => 'NW7', desc => 'Creates a NW7 barcodes from a string consisting of the numeric characters 0-9', selected => 0},
88 # {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},
89 # {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},
93 {type => 'BIB', name => 'Biblio', desc => 'Only the bibliographic data is printed.', selected => 0},
94 {type => 'BARBIB', name => 'Barcode/Biblio', desc => 'Barcode proceeds bibliographic data.', selected => 0},
95 {type => 'BIBBAR', name => 'Biblio/Barcode', desc => 'Bibliographic data proceeds barcode.', selected => 0},
96 {type => 'ALT', name => 'Alternating', desc => 'Barcode and bibliographic data are printed on alternating labels.', selected => 0},
97 {type => 'BAR', name => 'Barcode', desc => 'Only the barcode is printed.', selected => 0},
101 {type => 'TR', name => 'Times-Roman', selected => 0},
102 {type => 'TB', name => 'Times-Bold', selected => 0},
103 {type => 'TI', name => 'Times-Italic', selected => 0},
104 {type => 'TBI', name => 'Times-Bold-Italic', selected => 0},
105 {type => 'C', name => 'Courier', selected => 0},
106 {type => 'CB', name => 'Courier-Bold', selected => 0},
107 {type => 'CO', name => 'Courier-Oblique', selected => 0},
108 {type => 'CBO', name => 'Courier-Bold-Oblique', selected => 0},
109 {type => 'H', name => 'Helvetica', selected => 0},
110 {type => 'HB', name => 'Helvetica-Bold', selected => 0},
111 {type => 'HBO', name => 'Helvetica-Bold-Oblique', selected => 0},
114 my $text_justification_types = [
115 {type => 'L', name => 'Left', selected => 0},
116 {type => 'C', name => 'Center', selected => 0},
117 {type => 'R', name => 'Right', selected => 0},
118 # {type => 'F', name => 'Full', selected => 0},
122 {type => 'POINT', desc => 'PostScript Points', value => 1, selected => 0},
123 {type => 'AGATE', desc => 'Adobe Agates', value => 5.1428571, selected => 0},
124 {type => 'INCH', desc => 'US Inches', value => 72, selected => 0},
125 {type => 'MM', desc => 'SI Millimeters', value => 2.83464567, selected => 0},
126 {type => 'CM', desc => 'SI Centimeters', value => 28.3464567, selected => 0},
129 my $label_output_formats = [
130 {type => 'pdf', desc => 'PDF File'},
131 {type => 'csv', desc => 'CSV File'},
134 =head2 C4::Labels::Lib::get_all_templates()
136 This function returns a reference to a hash containing all templates upon success and 1 upon failure. Errors are logged to the Apache log.
140 my $templates = get_all_templates();
144 sub get_all_templates {
147 my $query = "SELECT " . ($params{'field_list'} ? $params{'field_list'} : '*') . " FROM labels_templates";
148 $query .= ($params{'filter'} ? " WHERE $params{'filter'};" : ';');
149 my $sth = C4::Context->dbh->prepare($query);
152 warn sprintf('Database returned the following error: %s', $sth->errstr);
156 while (my $template = $sth->fetchrow_hashref) {
157 push(@templates, $template);
162 =head2 C4::Labels::Lib::get_all_layouts()
164 This function returns a reference to a hash containing all layouts upon success and 1 upon failure. Errors are logged to the Apache log.
168 my $layouts = get_all_layouts();
172 sub get_all_layouts {
175 #my $query = "SELECT * FROM labels_layouts;";
176 my $query = "SELECT " . ($params{'field_list'} ? $params{'field_list'} : '*') . " FROM labels_layouts";
177 $query .= ($params{'filter'} ? " WHERE $params{'filter'};" : ';');
178 my $sth = C4::Context->dbh->prepare($query);
181 warn sprintf('Database returned the following error: %s', $sth->errstr);
185 while (my $layout = $sth->fetchrow_hashref) {
186 push(@layouts, $layout);
191 =head2 C4::Labels::Lib::get_all_profiles()
193 This function returns an arrayref whose elements are hashes containing all profiles upon success and 1 upon failure. Errors are logged
194 to the Apache log. Two parameters are accepted. The first limits the field(s) returned. This parameter should be string of comma separted
195 fields. ie. "field_1, field_2, ...field_n" The second limits the records returned based on a string containing a valud SQL 'WHERE' filter.
196 NOTE: Do not pass in the keyword 'WHERE.'
200 my $profiles = get_all_profiles();
201 my $profiles = get_all_profiles(field_list => field_list, filter => filter_string);
205 sub get_all_profiles {
208 my $query = "SELECT " . ($params{'field_list'} ? $params{'field_list'} : '*') . " FROM printers_profile";
209 $query .= ($params{'filter'} ? " WHERE $params{'filter'};" : ';');
210 my $sth = C4::Context->dbh->prepare($query);
211 # $sth->{'TraceLevel'} = 3 if $debug;
214 warn sprintf('Database returned the following error: %s', $sth->errstr);
218 while (my $profile = $sth->fetchrow_hashref) {
219 push(@profiles, $profile);
224 =head2 C4::Labels::Lib::get_batch_summary()
226 This function returns an arrayref whose elements are hashes containing the batch_ids of current batches along with the item count
227 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.
228 One parameter is accepted which limits the records returned based on a string containing a valud SQL 'WHERE' filter.
230 NOTE: Do not pass in the keyword 'WHERE.'
234 my $batches = get_batch_summary();
235 my $batches = get_batch_summary(filter => filter_string);
239 sub get_batch_summary {
242 my $query = "SELECT DISTINCT batch_id FROM labels_batches";
243 $query .= ($params{'filter'} ? " WHERE $params{'filter'};" : ';');
244 my $sth = C4::Context->dbh->prepare($query);
245 # $sth->{'TraceLevel'} = 3;
248 warn sprintf('Database returned the following error on attempted SELECT: %s', $sth->errstr);
252 while (my $batch = $sth->fetchrow_hashref) {
253 my $query = "SELECT count(item_number) FROM labels_batches WHERE batch_id=?;";
254 my $sth1 = C4::Context->dbh->prepare($query);
255 $sth1->execute($batch->{'batch_id'});
257 warn sprintf('Database returned the following error on attempted SELECT count: %s', $sth1->errstr);
260 my $count = $sth1->fetchrow_arrayref;
261 $batch->{'_item_count'} = @$count[0];
262 push(@batches, $batch);
267 =head2 C4::Labels::Lib::get_label_summary()
269 This function returns an arrayref whose elements are hashes containing the label_ids of current labels along with the item count
270 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.
271 One parameter is accepted which limits the records returned based on a string containing a valud SQL 'WHERE' filter.
273 NOTE: Do not pass in the keyword 'WHERE.'
277 my $labels = get_label_summary();
278 my $labels = get_label_summary(items => @item_list);
282 sub get_label_summary {
284 my $label_number = 0;
285 my @label_summaries = ();
286 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=?;";
287 my $sth = C4::Context->dbh->prepare($query);
288 foreach my $item (@{$params{'items'}}) {
290 $sth->execute($item->{'item_number'}, $params{'batch_id'});
292 warn sprintf('Database returned the following error on attempted SELECT: %s', $sth->errstr);
295 my $record = $sth->fetchrow_hashref;
296 my $label_summary->{'_label_number'} = $label_number;
297 $record->{'author'} =~ s/[^\.|\w]$// if $record->{'author'}; # strip off ugly trailing chars... but not periods or word chars
298 $record->{'title'} =~ s/\W*$//; # strip off ugly trailing chars
299 # FIXME contructing staff interface URLs should be done *much* higher up the stack - for the most part, C4 module code
300 # should not know that it's part of a web app
301 $record->{'title'} = '<a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=' . $record->{'biblionumber'} . '"> ' . $record->{'title'} . '</a>';
302 $label_summary->{'_summary'} = $record->{'title'} . " | " . ($record->{'author'} ? $record->{'author'} : 'N/A');
303 $label_summary->{'_item_type'} = $record->{'itemtype'};
304 $label_summary->{'_barcode'} = $record->{'barcode'};
305 $label_summary->{'_item_number'} = $item->{'item_number'};
306 $label_summary->{'_label_id'} = $item->{'label_id'};
307 push (@label_summaries, $label_summary);
309 return \@label_summaries;
312 =head2 C4::Labels::Lib::get_barcode_types()
314 This function returns a reference to an array of hashes containing all barcode types along with their name and description.
318 my $barcode_types = get_barcode_types();
322 sub get_barcode_types {
323 return $barcode_types;
326 =head2 C4::Labels::Lib::get_label_types()
328 This function returns a reference to an array of hashes containing all label types along with their name and description.
332 my $label_types = get_label_types();
336 sub get_label_types {
340 =head2 C4::Labels::Lib::get_font_types()
342 This function returns a reference to an array of hashes containing all font types along with their name and description.
346 my $font_types = get_font_types();
354 =head2 C4::Labels::Lib::get_text_justification_types()
356 This function returns a reference to an array of hashes containing all text justification types along with their name and description.
360 my $text_justification_types = get_text_justification_types();
364 sub get_text_justification_types {
365 return $text_justification_types;
368 =head2 C4::Labels::Lib::get_unit_values()
370 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.
371 There are 72 PS points to the inch.
375 my $unit_values = get_unit_values();
379 sub get_unit_values {
383 =head2 C4::Labels::Lib::get_label_output_formats()
385 This function returns a reference to an array of hashes containing all label output formats along with their description.
389 my $label_output_formats = get_label_output_formats();
393 sub get_label_output_formats {
394 return $label_output_formats;
397 =head2 C4::Labels::Lib::get_column_names($table_name)
399 Return an arrayref of an array containing the column names of the supplied table.
403 sub get_column_names {
405 my $dbh = C4::Context->dbh();
406 my $column_names = [];
407 my $sth = $dbh->column_info(undef,undef,$table,'%');
408 while (my $info = $sth->fetchrow_hashref()){
409 $$column_names[$info->{'ORDINAL_POSITION'}] = $info->{'COLUMN_NAME'};
411 return $column_names;
414 =head2 C4::Labels::Lib::get_table_names($search_term)
416 Return an arrayref of an array containing the table names which contain the supplied search term.
420 sub get_table_names {
421 my $search_term = shift;
422 my $dbh = C4::Context->dbh();
423 my $table_names = [];
424 my $sth = $dbh->table_info(undef,undef,"%$search_term%");
425 while (my $info = $sth->fetchrow_hashref()){
426 push (@$table_names, $info->{'TABLE_NAME'});
431 =head2 C4::Labels::Lib::html_table()
433 This function returns an arrayref of an array of hashes contianing the supplied data formatted suitably to
434 be passed off as a T::P template parameter and used to build an html table.
438 my $table = html_table(header_fields, array_of_row_data);
445 return undef if scalar(@$data) == 0; # no need to generate a table if there is not data to display
449 my @table_columns = ();
450 my ($row_index, $col_index) = (0,0);
451 my $cols = 0; # number of columns to wrap on
453 my $select_value = undef;
454 my $link_field = undef;
456 foreach my $header (@$headers) {
457 my @key = keys %$header;
458 if ($key[0] eq 'select' ) {
459 push (@table_columns, $key[0]);
460 $$fields[$col_index] = {hidden => 0, select_field => 0, field_name => ($key[0]), field_label => $header->{$key[0]}{'label'}};
461 # do special formatting stuff....
462 $select_value = $header->{$key[0]}{'value'};
465 # do special formatting stuff....
466 $link_field->{$key[0]} = ($header->{$key[0]}{'link_field'} == 1 ? 1 : 0);
467 push (@table_columns, $key[0]);
468 $$fields[$col_index] = {hidden => 0, select_field => 0, field_name => ($key[0]), field_label => $header->{$key[0]}{'label'}};
473 $$table[$row_index] = {header_fields => $fields};
475 $field_count *= scalar(@$data); # total fields to be displayed in the table
480 foreach my $db_row (@$data) {
482 foreach my $table_column (@table_columns) {
483 if (grep {$table_column eq $_} keys %$db_row) {
484 $$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}};
488 elsif ($table_column =~ m/^_((.*)_(.*$))/) { # this a special case
489 my $table_name = get_table_names($2);
490 my $record_set = _SELECT($1, @$table_name[0], $2 . "_id = " . $db_row->{$2 . "_id"});
491 $$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}};
495 elsif ($table_column eq 'select' ) {
496 $$fields[$col_index] = {hidden => 0, select_field => 1, field_name => 'select', field_value => $db_row->{$select_value}};
499 $$table[$row_index] = {text_fields => $fields};
512 Chris Nighswonger <cnighswonger AT foundations DOT edu>