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
22 use Sys::Syslog qw(syslog);
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
47 #=head2 C4::Labels::Lib::_SELECT()
49 # This function returns a recordset upon success and 1 upon failure. Errors are logged to the syslog.
53 # my $field_value = _SELECT(field_name, table_name, condition);
59 my $query = "SELECT $params[0] FROM $params[1]";
60 $params[2] ? $query .= " WHERE $params[2];" : $query .= ';';
61 my $sth = C4::Context->dbh->prepare($query);
62 # $sth->{'TraceLevel'} = 3;
65 syslog("LOG_ERR", "C4::Labels::Lib::get_single_field_value : Database returned the following error: %s", $sth->errstr);
69 while (my $row = $sth->fetchrow_hashref()) {
70 push(@$record_set, $row);
76 {type => 'CODE39', name => 'Code 39', desc => 'Translates the characters 0-9, A-Z, \'-\', \'*\', \'+\', \'$\', \'%\', \'/\', \'.\' and \' \' to a barcode pattern.', selected => 0},
77 {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},
78 {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},
79 {type => 'COOP2OF5', name => 'COOP2of5', desc => 'Creates COOP2of5 barcodes from a string consisting of the numeric characters 0-9', selected => 0},
80 # {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},
81 # {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},
82 # {type => 'IATA2of5', name => 'IATA2of5', desc => 'Creates IATA2of5 barcodes from a string consisting of the numeric characters 0-9', selected => 0},
83 {type => 'INDUSTRIAL2OF5', name => 'Industrial2of5', desc => 'Creates Industrial2of5 barcodes from a string consisting of the numeric characters 0-9', selected => 0},
84 # {type => 'ITF', name => 'Interleaved2of5', desc => 'Translates the characters 0-9 to a barcodes. These barcodes could also be called 'Interleaved2of5'.', selected => 0},
85 # {type => 'MATRIX2OF5', name => 'Matrix2of5', desc => 'Creates Matrix2of5 barcodes from a string consisting of the numeric characters 0-9', selected => 0},
86 # {type => 'NW7', name => 'NW7', desc => 'Creates a NW7 barcodes from a string consisting of the numeric characters 0-9', selected => 0},
87 # {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},
88 # {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},
92 {type => 'BIB', name => 'Biblio', desc => 'Only the bibliographic data is printed.', selected => 0},
93 {type => 'BARBIB', name => 'Barcode/Biblio', desc => 'Barcode proceeds bibliographic data.', selected => 0},
94 {type => 'BIBBAR', name => 'Biblio/Barcode', desc => 'Bibliographic data proceeds barcode.', selected => 0},
95 {type => 'ALT', name => 'Alternating', desc => 'Barcode and bibliographic data are printed on alternating labels.', selected => 0},
96 {type => 'BAR', name => 'Barcode', desc => 'Only the barcode is printed.', selected => 0},
100 {type => 'TR', name => 'Times-Roman', selected => 0},
101 {type => 'TB', name => 'Times-Bold', selected => 0},
102 {type => 'TI', name => 'Times-Italic', selected => 0},
103 {type => 'TBI', name => 'Times-Bold-Italic', selected => 0},
104 {type => 'C', name => 'Courier', selected => 0},
105 {type => 'CB', name => 'Courier-Bold', selected => 0},
106 {type => 'CO', name => 'Courier-Oblique', selected => 0},
107 {type => 'CBO', name => 'Courier-Bold-Oblique', selected => 0},
108 {type => 'H', name => 'Helvetica', selected => 0},
109 {type => 'HB', name => 'Helvetica-Bold', selected => 0},
110 {type => 'HBO', name => 'Helvetica-Bold-Oblique', selected => 0},
113 my $text_justification_types = [
114 {type => 'L', name => 'Left', selected => 0},
115 {type => 'C', name => 'Center', selected => 0},
116 {type => 'R', name => 'Right', selected => 0},
117 # {type => 'F', name => 'Full', selected => 0},
121 {type => 'POINT', desc => 'PostScript Points', value => 1, selected => 0},
122 {type => 'AGATE', desc => 'Adobe Agates', value => 5.1428571, selected => 0},
123 {type => 'INCH', desc => 'US Inches', value => 72, selected => 0},
124 {type => 'MM', desc => 'SI Millimeters', value => 2.83464567, selected => 0},
125 {type => 'CM', desc => 'SI Centimeters', value => 28.3464567, selected => 0},
128 =head2 C4::Labels::Lib::get_all_templates()
130 This function returns a reference to a hash containing all templates upon success and 1 upon failure. Errors are logged to the syslog.
134 my $templates = get_all_templates();
138 sub get_all_templates {
141 my $query = "SELECT " . ($params{'field_list'} ? $params{'field_list'} : '*') . " FROM labels_templates";
142 $query .= ($params{'filter'} ? " WHERE $params{'filter'};" : ';');
143 my $sth = C4::Context->dbh->prepare($query);
146 syslog("LOG_ERR", "C4::Labels::Lib::get_all_templates : Database returned the following error: %s", $sth->errstr);
150 while (my $template = $sth->fetchrow_hashref) {
151 push(@templates, $template);
156 =head2 C4::Labels::Lib::get_all_layouts()
158 This function returns a reference to a hash containing all layouts upon success and 1 upon failure. Errors are logged to the syslog.
162 my $layouts = get_all_layouts();
166 sub get_all_layouts {
168 my $query = "SELECT * FROM labels_layouts;";
169 my $sth = C4::Context->dbh->prepare($query);
172 syslog("LOG_ERR", "C4::Labels::Lib::get_all_layouts : Database returned the following error: %s", $sth->errstr);
176 while (my $layout = $sth->fetchrow_hashref) {
177 push(@layouts, $layout);
182 =head2 C4::Labels::Lib::get_all_profiles()
184 This function returns an arrayref whose elements are hashes containing all profiles upon success and 1 upon failure. Errors are logged
185 to the syslog. Two parameters are accepted. The first limits the field(s) returned. This parameter should be string of comma separted
186 fields. ie. "field_1, field_2, ...field_n" The second limits the records returned based on a string containing a valud SQL 'WHERE' filter.
187 NOTE: Do not pass in the keyword 'WHERE.'
191 my $profiles = get_all_profiles();
192 my $profiles = get_all_profiles(field_list => field_list, filter => filter_string);
196 sub get_all_profiles {
199 my $query = "SELECT " . ($params{'field_list'} ? $params{'field_list'} : '*') . " FROM printers_profile";
200 $query .= ($params{'filter'} ? " WHERE $params{'filter'};" : ';');
201 my $sth = C4::Context->dbh->prepare($query);
202 # $sth->{'TraceLevel'} = 3 if $debug;
205 syslog("LOG_ERR", "C4::Labels::Lib::get_all_profiles : Database returned the following error: %s", $sth->errstr);
209 while (my $profile = $sth->fetchrow_hashref) {
210 push(@profiles, $profile);
215 =head2 C4::Labels::Lib::get_batch_summary()
217 This function returns an arrayref whose elements are hashes containing the batch_ids of current batches along with the item count
218 for each batch upon success and 1 upon failure. Item counts are stored under the key '_item_count' Errors are logged to the syslog.
219 One parameter is accepted which limits the records returned based on a string containing a valud SQL 'WHERE' filter.
221 NOTE: Do not pass in the keyword 'WHERE.'
225 my $batches = get_batch_summary();
226 my $batches = get_batch_summary(filter => filter_string);
230 sub get_batch_summary {
233 my $query = "SELECT DISTINCT batch_id FROM labels_batches";
234 $query .= ($params{'filter'} ? " WHERE $params{'filter'};" : ';');
235 my $sth = C4::Context->dbh->prepare($query);
236 # $sth->{'TraceLevel'} = 3;
239 syslog("LOG_ERR", "C4::Labels::Lib::get_batch_summary : Database returned the following error on attempted SELECT: %s", $sth->errstr);
243 while (my $batch = $sth->fetchrow_hashref) {
244 my $query = "SELECT count(item_number) FROM labels_batches WHERE batch_id=?;";
245 my $sth1 = C4::Context->dbh->prepare($query);
246 $sth1->execute($batch->{'batch_id'});
248 syslog("LOG_ERR", "C4::Labels::Lib::get_batch_summary : Database returned the following error on attempted SELECT count: %s", $sth1->errstr);
251 my $count = $sth1->fetchrow_arrayref;
252 $batch->{'_item_count'} = @$count[0];
253 push(@batches, $batch);
258 =head2 C4::Labels::Lib::get_label_summary()
260 This function returns an arrayref whose elements are hashes containing the label_ids of current labels along with the item count
261 for each label upon success and 1 upon failure. Item counts are stored under the key '_item_count' Errors are logged to the syslog.
262 One parameter is accepted which limits the records returned based on a string containing a valud SQL 'WHERE' filter.
264 NOTE: Do not pass in the keyword 'WHERE.'
268 my $labels = get_label_summary();
269 my $labels = get_label_summary(items => @item_list);
273 sub get_label_summary {
275 my $label_number = 0;
276 my @label_summaries = ();
277 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=?;";
278 my $sth = C4::Context->dbh->prepare($query);
279 foreach my $item (@{$params{'items'}}) {
281 $sth->execute($item->{'item_number'}, $params{'batch_id'});
283 syslog("LOG_ERR", "C4::Labels::Lib::get_label_summary : Database returned the following error on attempted SELECT: %s", $sth->errstr);
286 my $record = $sth->fetchrow_hashref;
287 my $label_summary->{'_label_number'} = $label_number;
288 $record->{'author'} =~ s/[^\.|\w]$// if $record->{'author'}; # strip off ugly trailing chars... but not periods or word chars
289 $record->{'title'} =~ s/\W*$//; # strip off ugly trailing chars
290 $record->{'title'} = '<a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=' . $record->{'biblionumber'} . '"> ' . $record->{'title'} . '</a>';
291 $label_summary->{'_summary'} = $record->{'title'} . " | " . ($record->{'author'} ? $record->{'author'} : 'N/A');
292 $label_summary->{'_item_type'} = $record->{'itemtype'};
293 $label_summary->{'_barcode'} = $record->{'barcode'};
294 $label_summary->{'_item_number'} = $item->{'item_number'};
295 $label_summary->{'_label_id'} = $item->{'label_id'};
296 push (@label_summaries, $label_summary);
298 return \@label_summaries;
301 =head2 C4::Labels::Lib::get_barcode_types()
303 This function returns a reference to an array of hashes containing all barcode types along with their name and description.
307 my $barcode_types = get_barcode_types();
311 sub get_barcode_types {
312 return $barcode_types;
315 =head2 C4::Labels::Lib::get_label_types()
317 This function returns a reference to an array of hashes containing all label types along with their name and description.
321 my $label_types = get_label_types();
325 sub get_label_types {
329 =head2 C4::Labels::Lib::get_font_types()
331 This function returns a reference to an array of hashes containing all font types along with their name and description.
335 my $font_types = get_font_types();
343 =head2 C4::Labels::Lib::get_text_justification_types()
345 This function returns a reference to an array of hashes containing all text justification types along with their name and description.
349 my $text_justification_types = get_text_justification_types();
353 sub get_text_justification_types {
354 return $text_justification_types;
357 =head2 C4::Labels::Lib::get_unit_values()
359 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.
360 There are 72 PS points to the inch.
364 my $unit_values = get_unit_values();
368 sub get_unit_values {
372 =head2 C4::Labels::Lib::get_column_names($table_name)
374 Return an arrayref of an array containing the column names of the supplied table.
378 sub get_column_names {
380 my $dbh = C4::Context->dbh();
381 my $column_names = [];
382 my $sth = $dbh->column_info(undef,undef,$table,'%');
383 while (my $info = $sth->fetchrow_hashref()){
384 $$column_names[$info->{'ORDINAL_POSITION'}] = $info->{'COLUMN_NAME'};
386 return $column_names;
389 =head2 C4::Labels::Lib::get_table_names($search_term)
391 Return an arrayref of an array containing the table names which contain the supplied search term.
395 sub get_table_names {
396 my $search_term = shift;
397 my $dbh = C4::Context->dbh();
398 my $table_names = [];
399 my $sth = $dbh->table_info(undef,undef,"%$search_term%");
400 while (my $info = $sth->fetchrow_hashref()){
401 push (@$table_names, $info->{'TABLE_NAME'});
406 =head2 C4::Labels::Lib::html_table()
408 This function returns an arrayref of an array of hashes contianing the supplied data formatted suitably to
409 be passed off as a T::P template parameter and used to build an html table.
413 my $table = html_table(header_fields, array_of_row_data);
423 my @table_columns = ();
424 my ($row_index, $col_index) = (0,0);
425 my $cols = 0; # number of columns to wrap on
427 my $select_value = undef;
428 my $link_field = undef;
430 foreach my $header (@$headers) {
431 my @key = keys %$header;
432 if ($key[0] eq 'select' ) {
433 push (@table_columns, $key[0]);
434 $$fields[$col_index] = {hidden => 0, select_field => 0, field_name => ($key[0]), field_label => $header->{$key[0]}{'label'}};
435 # do special formatting stuff....
436 $select_value = $header->{$key[0]}{'value'};
439 # do special formatting stuff....
440 $link_field->{$key[0]} = ($header->{$key[0]}{'link_field'} == 1 ? 1 : 0);
441 push (@table_columns, $key[0]);
442 $$fields[$col_index] = {hidden => 0, select_field => 0, field_name => ($key[0]), field_label => $header->{$key[0]}{'label'}};
447 $$table[$row_index] = {header_fields => $fields};
449 $field_count *= scalar(@$data); # total fields to be displayed in the table
454 foreach my $db_row (@$data) {
456 foreach my $table_column (@table_columns) {
457 if (grep {$table_column eq $_} keys %$db_row) {
458 $$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}};
462 elsif ($table_column =~ m/^_((.*)_(.*$))/) { # this a special case
463 my $table_name = get_table_names($2);
464 my $record_set = _SELECT($1, @$table_name[0], $2 . "_id = " . $db_row->{$2 . "_id"});
465 $$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}};
469 elsif ($table_column eq 'select' ) {
470 $$fields[$col_index] = {hidden => 0, select_field => 1, field_name => 'select', field_value => $db_row->{$select_value}};
473 $$table[$row_index] = {text_fields => $fields};
486 Chris Nighswonger <cnighswonger AT foundations DOT edu>