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 $query = "SELECT $params[0] 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);
87 {type
=> 'CODE39', name
=> 'Code 39', desc
=> 'Translates the characters 0-9, A-Z, \'-\', \'*\', \'+\', \'$\', \'%\', \'/\', \'.\' and \' \' to a barcode pattern.', selected
=> 0},
88 {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},
89 {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},
90 {type
=> 'COOP2OF5', name
=> 'COOP2of5', desc
=> 'Creates COOP2of5 barcodes from a string consisting of the numeric characters 0-9', selected
=> 0},
91 {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},
92 # {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},
93 # {type => 'IATA2of5', name => 'IATA2of5', desc => 'Creates IATA2of5 barcodes from a string consisting of the numeric characters 0-9', selected => 0},
94 {type
=> 'INDUSTRIAL2OF5', name
=> 'Industrial2of5', desc
=> 'Creates Industrial2of5 barcodes from a string consisting of the numeric characters 0-9', selected
=> 0},
95 # {type => 'ITF', name => 'Interleaved2of5', desc => 'Translates the characters 0-9 to a barcodes. These barcodes could also be called 'Interleaved2of5'.', selected => 0},
96 # {type => 'MATRIX2OF5', name => 'Matrix2of5', desc => 'Creates Matrix2of5 barcodes from a string consisting of the numeric characters 0-9', selected => 0},
97 # {type => 'NW7', name => 'NW7', desc => 'Creates a NW7 barcodes from a string consisting of the numeric characters 0-9', selected => 0},
98 # {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},
99 # {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},
103 {type
=> 'BIB', name
=> 'Biblio', desc
=> 'Only the bibliographic data is printed.', selected
=> 0},
104 {type
=> 'BARBIB', name
=> 'Barcode/Biblio', desc
=> 'Barcode proceeds bibliographic data.', selected
=> 0},
105 {type
=> 'BIBBAR', name
=> 'Biblio/Barcode', desc
=> 'Bibliographic data proceeds barcode.', selected
=> 0},
106 {type
=> 'ALT', name
=> 'Alternating', desc
=> 'Barcode and bibliographic data are printed on alternating labels.', selected
=> 0},
107 {type
=> 'BAR', name
=> 'Barcode', desc
=> 'Only the barcode is printed.', selected
=> 0},
111 {type
=> 'TR', name
=> 'Times-Roman', selected
=> 0},
112 {type
=> 'TB', name
=> 'Times-Bold', selected
=> 0},
113 {type
=> 'TI', name
=> 'Times-Italic', selected
=> 0},
114 {type
=> 'TBI', name
=> 'Times-Bold-Italic', selected
=> 0},
115 {type
=> 'C', name
=> 'Courier', selected
=> 0},
116 {type
=> 'CB', name
=> 'Courier-Bold', selected
=> 0},
117 {type
=> 'CO', name
=> 'Courier-Oblique', selected
=> 0},
118 {type
=> 'CBO', name
=> 'Courier-Bold-Oblique', selected
=> 0},
119 {type
=> 'H', name
=> 'Helvetica', selected
=> 0},
120 {type
=> 'HO', name
=> 'Helvetica-Oblique', selected
=> 0},
121 {type
=> 'HB', name
=> 'Helvetica-Bold', selected
=> 0},
122 {type
=> 'HBO', name
=> 'Helvetica-Bold-Oblique', selected
=> 0},
125 my $text_justification_types = [
126 {type
=> 'L', name
=> 'Left', selected
=> 0},
127 {type
=> 'C', name
=> 'Center', selected
=> 0},
128 {type
=> 'R', name
=> 'Right', selected
=> 0},
129 # {type => 'F', name => 'Full', selected => 0},
133 {type
=> 'POINT', desc
=> 'PostScript Points', value
=> 1, selected
=> 0},
134 {type
=> 'AGATE', desc
=> 'Adobe Agates', value
=> 5.1428571, selected
=> 0},
135 {type
=> 'INCH', desc
=> 'US Inches', value
=> 72, selected
=> 0},
136 {type
=> 'MM', desc
=> 'SI Millimeters', value
=> 2.83464567, selected
=> 0},
137 {type
=> 'CM', desc
=> 'SI Centimeters', value
=> 28.3464567, selected
=> 0},
140 my $output_formats = [
141 {type
=> 'pdf', desc
=> 'PDF File'},
142 {type
=> 'csv', desc
=> 'CSV File'},
146 my ( $params, $table ) = @_;
147 my @fields = exists $params->{fields
} ? @
{ $params->{fields
} } : ();
148 my $query = "SELECT " . ( @fields ?
join(', ', @fields ) : '*' ) . " FROM $table";
150 if ( exists $params->{filters
} ) {
151 $query .= ' WHERE 1 ';
152 while ( my ( $field, $values ) = each %{ $params->{filters
} } ) {
153 if ( ref( $values ) ) {
154 $query .= " AND $field IN ( " . ( ('?,') x
(@
$values-1) ) . "? ) "; # a comma separates elements in a list...
155 push @where_args, @
$values;
157 $query .= " AND $field = ? ";
158 push @where_args, $values;
162 $query .= (exists $params->{orderby
} ?
" ORDER BY $params->{orderby} " : '');
163 return ( $query, @where_args );
166 =head2 C4::Creators::Lib::get_all_templates()
168 my $templates = get_all_templates();
170 This function returns a reference to a hash containing all templates upon success and 1 upon failure. Errors are logged to the Apache log.
174 sub get_all_templates
{
177 my ( $query, @where_args ) = _build_query
( $params, 'creator_templates' );
178 my $sth = C4
::Context
->dbh->prepare($query);
179 $sth->execute( @where_args );
181 warn sprintf('Database returned the following error: %s', $sth->errstr);
185 while (my $template = $sth->fetchrow_hashref) {
186 push(@templates, $template);
191 =head2 C4::Creators::Lib::get_all_layouts()
193 my $layouts = get_all_layouts();
195 This function returns a reference to a hash containing all layouts upon success and 1 upon failure. Errors are logged to the Apache log.
199 sub get_all_layouts
{
202 my ( $query, @where_args ) = _build_query
( $params, 'creator_layouts' );
203 my $sth = C4
::Context
->dbh->prepare($query);
204 $sth->execute( @where_args );
206 warn sprintf('Database returned the following error: %s', $sth->errstr);
210 while (my $layout = $sth->fetchrow_hashref) {
211 push(@layouts, $layout);
216 =head2 C4::Creators::Lib::get_all_profiles()
218 my $profiles = get_all_profiles();
220 my $profiles = get_all_profiles({ fields => [@fields], filters => { filters => [$value1, $value2] } });
222 This function returns an arrayref whose elements are hashes containing all profiles upon success and 1 upon failure. Errors are logged
223 to the Apache log. Two parameters are accepted. The first limits the field(s) returned. This parameter should be string of comma separted
224 fields. ie. "field_1, field_2, ...field_n" The second limits the records returned based on a string containing a valud SQL 'WHERE' filter.
226 NOTE: Do not pass in the keyword 'WHERE.'
230 sub get_all_profiles
{
233 my ( $query, @where_args ) = _build_query
( $params, 'printers_profile' );
234 my $sth = C4
::Context
->dbh->prepare($query);
235 $sth->execute( @where_args );
237 warn sprintf('Database returned the following error: %s', $sth->errstr);
241 while (my $profile = $sth->fetchrow_hashref) {
242 push(@profiles, $profile);
247 =head2 C4::Creators::Lib::get_all_image_names()
251 sub get_all_image_names
{
252 my $image_names = [];
253 my $query = "SELECT image_name FROM creator_images";
254 my $sth = C4
::Context
->dbh->prepare($query);
255 # $sth->{'TraceLevel'} = 3 if $debug;
258 warn sprintf('Database returned the following error: %s', $sth->errstr);
261 grep {push @
$image_names, {type
=> $$_[0], name
=> $$_[0], selected
=> 0}} @
{$sth->fetchall_arrayref([0])};
265 =head2 C4::Creators::Lib::get_batch_summary()
267 my $batches = get_batch_summary();
269 my $batches = get_batch_summary(filter => filter_string);
271 This function returns an arrayref whose elements are hashes containing the batch_ids of current batches along with the item count
272 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.
273 One parameter is accepted which limits the records returned based on a string containing a valud SQL 'WHERE' filter.
275 NOTE: Do not pass in the keyword 'WHERE.'
279 sub get_batch_summary
{
282 $params->{fields
} = ['batch_id', 'count(batch_id) as _item_count'];
283 my ( $query, @where_args ) = _build_query
( $params, 'creator_batches' );
284 $query .= " GROUP BY batch_id";
285 my $sth = C4
::Context
->dbh->prepare($query);
286 $sth->execute( @where_args );
288 warn sprintf('Database returned the following error on attempted SELECT: %s', $sth->errstr);
291 while (my $batch = $sth->fetchrow_hashref) {
292 push(@batches, $batch);
297 =head2 C4::Creators::Lib::get_label_summary()
299 my $labels = get_label_summary();
301 my $labels = get_label_summary(items => @item_list);
303 This function returns an arrayref whose elements are hashes containing the label_ids of current labels along with the item count
304 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.
305 One parameter is accepted which limits the records returned based on a string containing a valud SQL 'WHERE' filter.
307 NOTE: Do not pass in the keyword 'WHERE.'
311 sub get_label_summary
{
313 my $label_number = 0;
314 my @label_summaries = ();
315 my $query = " SELECT b.title, b.author, bi.itemtype, i.barcode, i.itemcallnumber, i.biblionumber, i.itype
316 FROM creator_batches AS c LEFT JOIN items AS i ON (c.item_number=i.itemnumber)
317 LEFT JOIN biblioitems AS bi ON (i.biblioitemnumber=bi.biblioitemnumber)
318 LEFT JOIN biblio AS b ON (bi.biblionumber=b.biblionumber)
319 WHERE itemnumber=? AND batch_id=?;
321 my $sth = C4
::Context
->dbh->prepare($query);
322 foreach my $item (@
{$params{'items'}}) {
324 $sth->execute($item->{'item_number'}, $params{'batch_id'});
326 warn sprintf('Database returned the following error on attempted SELECT: %s', $sth->errstr);
329 my $record = $sth->fetchrow_hashref;
331 $label_summary->{'_label_number'} = $label_number;
332 $record->{'author'} =~ s/[^\.|\w]$// if $record->{'author'}; # strip off ugly trailing chars... but not periods or word chars
333 $record->{'title'} =~ s/\W*$//; # strip off ugly trailing chars
334 # FIXME contructing staff interface URLs should be done *much* higher up the stack - for the most part, C4 module code
335 # should not know that it's part of a web app
336 $record->{'title'} = '<a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=' . $record->{'biblionumber'} . '"> ' . $record->{'title'} . '</a>';
337 $label_summary->{'_summary'} = $record->{'title'} . " | " . ($record->{'author'} ?
$record->{'author'} : 'N/A');
338 $label_summary->{'_item_type'} = C4
::Context
->preference("item-level_itypes") ?
$record->{'itype'} : $record->{'itemtype'};
339 $label_summary->{'_barcode'} = $record->{'barcode'};
340 $label_summary->{'_item_number'} = $item->{'item_number'};
341 $label_summary->{'_item_cn'} = $record->{'itemcallnumber'};
342 $label_summary->{'_label_id'} = $item->{'label_id'};
343 push (@label_summaries, $label_summary);
345 return \
@label_summaries;
348 =head2 C4::Creators::Lib::get_card_summary()
350 my $cards = get_card_summary();
352 my $cards = get_card_summary(items => @item_list);
354 This function returns an arrayref whose elements are hashes containing the label_ids of current cards along with the item count
355 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.
356 One parameter is accepted which limits the records returned based on a string containing a valud SQL 'WHERE' filter.
358 NOTE: Do not pass in the keyword 'WHERE.'
362 sub get_card_summary
{
365 my @card_summaries = ();
366 my $query = "SELECT CONCAT_WS(', ', surname, firstname) AS name, cardnumber FROM borrowers WHERE borrowernumber=?;";
367 my $sth = C4
::Context
->dbh->prepare($query);
368 foreach my $item (@
{$params{'items'}}) {
370 $sth->execute($item->{'borrower_number'});
372 warn sprintf('Database returned the following error on attempted SELECT: %s', $sth->errstr);
375 my $record = $sth->fetchrow_hashref;
376 my $card_summary->{'_card_number'} = $card_number;
377 $card_summary->{'_summary'} = $record->{'name'};
378 $card_summary->{'borrowernumber'} = $item->{'borrower_number'};
379 $card_summary->{'_label_id'} = $item->{'label_id'};
380 push (@card_summaries, $card_summary);
382 return \
@card_summaries;
385 =head2 C4::Creators::Lib::get_barcode_types()
387 my $barcode_types = get_barcode_types();
389 This function returns a reference to an array of hashes containing all barcode types along with their name and description.
393 sub get_barcode_types
{
394 return $barcode_types;
397 =head2 C4::Creators::Lib::get_label_types()
399 my $label_types = get_label_types();
401 This function returns a reference to an array of hashes containing all label types along with their name and description.
405 sub get_label_types
{
409 =head2 C4::Creators::Lib::get_font_types()
411 my $font_types = get_font_types();
413 This function returns a reference to an array of hashes containing all font types along with their name and description.
421 =head2 C4::Creators::Lib::get_text_justification_types()
423 my $text_justification_types = get_text_justification_types();
425 This function returns a reference to an array of hashes containing all text justification types along with their name and description.
429 sub get_text_justification_types
{
430 return $text_justification_types;
433 =head2 C4::Creators::Lib::get_unit_values()
435 my $unit_values = get_unit_values();
437 This function returns a reference to an array of hashes containing all unit types along with their description and multiplier.
438 NOTE: All units are relative to a PostScript Point.
439 There are 72 PS points to the inch.
443 sub get_unit_values
{
447 =head2 C4::Creators::Lib::get_output_formats()
449 my $label_output_formats = get_output_formats();
451 This function returns a reference to an array of hashes containing all label output formats along with their description.
455 sub get_output_formats
{
456 return $output_formats;
460 =head2 C4::Creators::Lib::get_table_names($search_term)
462 Return an arrayref of an array containing the table names which contain the supplied search term.
466 sub get_table_names
{
467 my $search_term = shift;
468 my $dbh = C4
::Context
->dbh();
469 my $table_names = [];
470 my $sth = $dbh->table_info(undef,undef,"%$search_term%");
471 while (my $info = $sth->fetchrow_hashref()){
472 push (@
$table_names, $info->{'TABLE_NAME'});
477 =head2 C4::Creators::Lib::html_table()
479 This function returns an arrayref of an array of hashes contianing the supplied data formatted suitably to
480 be passed off as a template parameter and used to build an html table.
482 my $table = html_table(header_fields, array_of_row_data);
484 table_loop => $table,
490 [% FOREACH table_loo IN table_loop %]
491 [% IF ( table_loo.header_fields ) %]
493 [% FOREACH header_field IN table_loo.header_fields %]
494 <th>[% header_field.field_label %]</th>
499 [% FOREACH text_field IN table_loo.text_fields %]
500 [% IF ( text_field.select_field ) %]
501 <td><input type="checkbox" name="action" value="[% text_field.field_value %]"></td>
503 <td>[% text_field.field_value %]</td>
516 return undef if scalar(@
$data) == 0; # no need to generate a table if there is not data to display
519 my @table_columns = ();
520 my ($row_index, $col_index) = (0,0);
521 my $cols = 0; # number of columns to wrap on
523 my $select_value = undef;
524 my $link_field = undef;
526 foreach my $header (@
$headers) {
527 my @key = keys %$header;
528 if ($key[0] eq 'select' ) {
529 push (@table_columns, $key[0]);
530 $$fields[$col_index] = {hidden
=> 0, select_field
=> 0, field_name
=> ($key[0]), field_label
=> $header->{$key[0]}{'label'}};
531 # do special formatting stuff....
532 $select_value = $header->{$key[0]}{'value'};
535 # do special formatting stuff....
536 $link_field->{$key[0]} = ($header->{$key[0]}{'link_field'} == 1 ?
1 : 0);
537 push (@table_columns, $key[0]);
538 $$fields[$col_index] = {hidden
=> 0, select_field
=> 0, field_name
=> ($key[0]), field_label
=> $header->{$key[0]}{'label'}};
543 $$table[$row_index] = {header_fields
=> $fields};
545 $field_count *= scalar(@
$data); # total fields to be displayed in the table
550 foreach my $db_row (@
$data) {
552 foreach my $table_column (@table_columns) {
553 if (grep {$table_column eq $_} keys %$db_row) {
554 $$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}};
558 elsif ($table_column =~ m/^_((.*)_(.*$))/) { # this a special case
559 my $table_name = get_table_names
('creator_'.$2); #Bug 14143 fix to remove ambiguity with table 'club_template_enrollment_fields'
560 my $record_set = _SELECT
($1, @
$table_name[0], $2 . "_id = " . $db_row->{$2 . "_id"});
561 $$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}};
565 elsif ($table_column eq 'select' ) {
566 $$fields[$col_index] = {hidden
=> 0, select_field
=> 1, field_name
=> 'select', field_value
=> $db_row->{$select_value}};
569 $$table[$row_index] = {text_fields
=> $fields};
582 Chris Nighswonger <cnighswonger AT foundations DOT edu>