Koha/AdditionalField.pm

   1 package Koha::AdditionalField;
   2
   3 use Modern::Perl;
   4
   5 use base qw(Class::Accessor);
   6
   7 use C4::Context;
   8
   9 __PACKAGE__->mk_accessors(qw( id tablename name authorised_value_category marcfield searchable values ));
  10
  11 sub new {
  12     my ( $class, $args ) = @_;
  13
  14     my $additional_field = {
  15         id => $args->{id} // q||,
  16         tablename => $args->{tablename} // q||,
  17         name => $args->{name} // q||,
  18         authorised_value_category => $args->{authorised_value_category} // q||,
  19         marcfield => $args->{marcfield} // q||,
  20         searchable => $args->{searchable} // 0,
  21         values => $args->{values} // {},
  22     };
  23
  24     my $self = $class->SUPER::new( $additional_field );
  25
  26     bless $self, $class;
  27     return $self;
  28 }
  29
  30 sub fetch {
  31     my ( $self ) = @_;
  32     my $dbh = C4::Context->dbh;
  33     my $field_id = $self->id;
  34     return unless $field_id;
  35     my $data = $dbh->selectrow_hashref(
  36         q|
  37             SELECT id, tablename, name, authorised_value_category, marcfield, searchable
  38             FROM additional_fields
  39             WHERE id = ?
  40         |,
  41         {}, ( $field_id )
  42     );
  43
  44     die "This additional field does not exist (id=$field_id)" unless $data;
  45     $self->{id} = $data->{id};
  46     $self->{tablename} = $data->{tablename};
  47     $self->{name} = $data->{name};
  48     $self->{authorised_value_category} = $data->{authorised_value_category};
  49     $self->{marcfield} = $data->{marcfield};
  50     $self->{searchable} = $data->{searchable};
  51     return $self;
  52 }
  53
  54 sub update {
  55     my ( $self ) = @_;
  56
  57     die "There is no id defined for this additional field. I cannot update it" unless $self->{id};
  58
  59     my $dbh = C4::Context->dbh;
  60     local $dbh->{RaiseError} = 1;
  61
  62     return $dbh->do(q|
  63         UPDATE additional_fields
  64         SET name = ?,
  65             authorised_value_category = ?,
  66             marcfield = ?,
  67             searchable = ?
  68         WHERE id = ?
  69     |, {}, ( $self->{name}, $self->{authorised_value_category}, $self->{marcfield}, $self->{searchable}, $self->{id} ) );
  70 }
  71
  72 sub delete {
  73     my ( $self ) = @_;
  74     return unless $self->{id};
  75     my $dbh = C4::Context->dbh;
  76     local $dbh->{RaiseError} = 1;
  77     return $dbh->do(q|
  78         DELETE FROM additional_fields WHERE id = ?
  79     |, {}, ( $self->{id} ) );
  80 }
  81
  82 sub insert {
  83     my ( $self ) = @_;
  84     my $dbh = C4::Context->dbh;
  85     local $dbh->{RaiseError} = 1;
  86     $dbh->do(q|
  87         INSERT INTO additional_fields
  88         ( tablename, name, authorised_value_category, marcfield, searchable )
  89         VALUES ( ?, ?, ?, ?, ? )
  90     |, {}, ( $self->{tablename}, $self->{name}, $self->{authorised_value_category}, $self->{marcfield}, $self->{searchable} ) );
  91     $self->{id} = $dbh->{mysql_insertid};
  92 }
  93
  94 sub insert_values {
  95     my ( $self )  = @_;
  96
  97     my $dbh = C4::Context->dbh;
  98     local $dbh->{RaiseError} = 1;
  99     while ( my ( $record_id, $value ) = each %{$self->{values}} ) {
 100         next unless defined $value;
 101         my $updated = $dbh->do(q|
 102             UPDATE additional_field_values
 103             SET value = ?
 104             WHERE field_id = ?
 105             AND record_id = ?
 106         |, {}, ( $value, $self->{id}, $record_id ));
 107         if ( $updated eq '0E0' ) {
 108             $dbh->do(q|
 109                 INSERT INTO additional_field_values( field_id, record_id, value )
 110                 VALUES( ?, ?, ?)
 111             |, {}, ( $self->{id}, $record_id, $value ));
 112         }
 113     }
 114 }
 115
 116 sub fetch_values {
 117     my ( $self, $args ) = @_;
 118     my $record_id = $args->{record_id};
 119     my $dbh = C4::Context->dbh;
 120     my $values = $dbh->selectall_arrayref(
 121         q|
 122             SELECT *
 123             FROM additional_fields af, additional_field_values afv
 124             WHERE af.id = afv.field_id
 125                 AND af.tablename = ?
 126                 AND af.name = ?
 127         | . ( $record_id ? q|AND afv.record_id = ?| : '' ),
 128         {Slice => {}}, ( $self->{tablename}, $self->{name}, ($record_id ? $record_id : () ) )
 129     );
 130
 131     $self->{values} = {};
 132     for my $v ( @$values ) {
 133         $self->{values}{$v->{record_id}} = $v->{value};
 134     }
 135 }
 136
 137 sub delete_values {
 138     my ($self, $args) = @_;
 139
 140     my $record_id = $args->{record_id};
 141
 142     my $dbh = C4::Context->dbh;
 143
 144     my @where_strs = ('field_id = ?');
 145     my @where_args = ($self->{id});
 146
 147     if ($record_id) {
 148         push @where_strs, 'record_id = ?';
 149         push @where_args, $record_id;
 150     }
 151
 152     my $query = q{
 153         DELETE FROM additional_field_values
 154         WHERE
 155     } . join (' AND ', @where_strs);
 156
 157     $dbh->do($query, undef, @where_args);
 158 }
 159
 160 sub all {
 161     my ( $class, $args ) = @_;
 162     die "BAD CALL: Don't use fetch_all_values as an instance method"
 163         if ref $class and UNIVERSAL::can($class,'can');
 164     my $tablename = $args->{tablename};
 165     my $searchable = $args->{searchable};
 166     my $dbh = C4::Context->dbh;
 167     my $query = q|
 168         SELECT * FROM additional_fields WHERE 1
 169     |;
 170     $query .= q| AND tablename = ?|
 171         if $tablename;
 172
 173     $query .= q| AND searchable = ?|
 174         if defined $searchable;
 175
 176     my $results = $dbh->selectall_arrayref(
 177         $query, {Slice => {}}, (
 178             $tablename ? $tablename : (),
 179             defined $searchable ? $searchable : ()
 180         )
 181     );
 182     my @fields;
 183     for my $r ( @$results ) {
 184         push @fields, Koha::AdditionalField->new({
 185             id => $r->{id},
 186             tablename => $r->{tablename},
 187             name => $r->{name},
 188             authorised_value_category => $r->{authorised_value_category},
 189             marcfield => $r->{marcfield},
 190             searchable => $r->{searchable},
 191         });
 192     }
 193     return \@fields;
 194
 195 }
 196
 197 sub fetch_all_values {
 198     my ( $class, $args ) = @_;
 199     die "BAD CALL: Don't use fetch_all_values as an instance method"
 200         if ref $class and UNIVERSAL::can($class,'can');
 201
 202     my $record_id = $args->{record_id};
 203     my $tablename = $args->{tablename};
 204     return unless $tablename;
 205
 206     my $dbh = C4::Context->dbh;
 207     my $values = $dbh->selectall_arrayref(
 208         q|
 209             SELECT afv.record_id, af.name, afv.value
 210             FROM additional_fields af, additional_field_values afv
 211             WHERE af.id = afv.field_id
 212                 AND af.tablename = ?
 213         | . ( $record_id ? q| AND afv.record_id = ?| : q|| ),
 214         {Slice => {}}, ( $tablename, ($record_id ? $record_id : ()) )
 215     );
 216
 217     my $r;
 218     for my $v ( @$values ) {
 219         $r->{$v->{record_id}}{$v->{name}} = $v->{value};
 220     }
 221     return $r;
 222 }
 223
 224 sub get_matching_record_ids {
 225     my ( $class, $args ) = @_;
 226     die "BAD CALL: Don't use fetch_all_values as an instance method"
 227         if ref $class and UNIVERSAL::can($class,'can');
 228
 229     my $fields = $args->{fields} // [];
 230     my $tablename = $args->{tablename};
 231     my $exact_match = $args->{exact_match} // 1;
 232     return [] unless @$fields;
 233
 234     my $dbh = C4::Context->dbh;
 235     my $query = q|SELECT * FROM |;
 236     my ( @subqueries, @args );
 237     my $i = 0;
 238     for my $field ( @$fields ) {
 239         $i++;
 240         my $subquery = qq|(
 241             SELECT record_id, field$i.name AS field${i}_name
 242             FROM additional_field_values afv
 243             LEFT JOIN
 244                 (
 245                     SELECT afv.id, af.name, afv.value
 246                     FROM additional_field_values afv, additional_fields af
 247                     WHERE afv.field_id = af.id
 248                     AND af.name = ?
 249                     AND af.tablename = ?
 250                     AND value LIKE ?
 251                 ) AS field$i USING (id)
 252             WHERE field$i.id IS NOT NULL
 253         ) AS values$i |;
 254         $subquery .= ' USING (record_id)' if $i > 1;
 255         push @subqueries, $subquery;
 256         push @args, $field->{name}, $tablename, ( ( $exact_match or $field->{authorised_value_category} ) ? $field->{value} : "%$field->{value}%" );
 257     }
 258     $query .= join( ' LEFT JOIN ', @subqueries ) . ' WHERE 1';
 259     for my $j ( 1 .. $i ) {
 260             $query .= qq| AND field${j}_name IS NOT NULL|;
 261     }
 262     my $values = $dbh->selectall_arrayref( $query, {Slice => {}}, @args );
 263     return [
 264         map { $_->{record_id} } @$values
 265     ]
 266 }
 267
 268 1;
 269
 270 __END__
 271
 272 =head1 NAME
 273
 274 Koha::AdditionalField
 275
 276 =head1 SYNOPSIS
 277
 278     use Koha::AdditionalField;
 279     my $af1 = Koha::AdditionalField->new({id => $id});
 280     my $af2 = Koha::AuthorisedValue->new({
 281         tablename => 'my_table',
 282         name => 'a_name',
 283         authorised_value_category => 'LOST',
 284         marcfield => '200$a',
 285         searchable => 1,
 286     });
 287     $av1->delete;
 288     $av2->{name} = 'another_name';
 289     $av2->update;
 290
 291 =head1 DESCRIPTION
 292
 293 Class for managing additional fields into Koha.
 294
 295 =head1 METHODS
 296
 297 =head2 new
 298
 299 Create a new Koha::AdditionalField object. This method can be called using several ways.
 300 Either with the id for existing field or with different values for a new one.
 301
 302 =over 4
 303
 304 =item B<id>
 305
 306     The caller just knows the id of the additional field and want to retrieve all values.
 307
 308 =item B<tablename, name, authorised_value_category, marcfield and searchable>
 309
 310     The caller wants to create a new additional field.
 311
 312 =back
 313
 314 =head2 fetch
 315
 316 The information will be retrieved from the database.
 317
 318 =head2 update
 319
 320 If the AdditionalField object has been modified and the values have to be modified into the database, call this method.
 321
 322 =head2 delete
 323
 324 Remove a the record in the database using the id the object.
 325
 326 =head2 insert
 327
 328 Insert a new AdditionalField object into the database.
 329
 330 =head2 insert_values
 331
 332 Insert new values for a record.
 333
 334     my $af = Koha::AdditionalField({ id => $id })->fetch;
 335     my $af->{values} = {
 336         record_id1 => 'my value',
 337         record_id2 => 'another value',
 338     };
 339     $af->insert_values;
 340
 341 =head2 fetch_values
 342
 343 Retrieve values from the database for a given record_id.
 344 The record_id argument is optional.
 345
 346     my $af = Koha::AdditionalField({ id => $id })->fetch;
 347     my $values = $af->fetch_values({record_id => $record_id});
 348
 349     $values will be equal to something like:
 350     {
 351         record_id => {
 352             field_name1 => 'value1',
 353             field_name2 => 'value2',
 354         }
 355     }
 356
 357 =head2 delete_values
 358
 359 Delete values from the database for a given record_id.
 360 The record_id argument is optional.
 361
 362     my $af = Koha::AdditionalField({ id => $id })->fetch;
 363     $af->delete_values({record_id => $record_id});
 364
 365 =head2 all
 366
 367 Retrieve all additional fields in the database given some parameters.
 368 Parameters are optional.
 369 This method returns a list of AdditionalField objects.
 370 This is a static method.
 371
 372     my $fields = Koha::AdditionalField->all;
 373     or
 374     my $fields = Koha::AdditionalField->all{(tablename => 'my_table'});
 375     or
 376     my $fields = Koha::AdditionalField->all({searchable => 1});
 377
 378 =head2 fetch_all_values
 379
 380 Retrieve all values for a table name.
 381 This is a static method.
 382
 383     my $values = Koha::AdditionalField({ tablename => 'my_table' });
 384
 385     $values will be equel to something like:
 386     {
 387         record_id1 => {
 388             field_name1 => 'value1',
 389             field_name2 => 'value2',
 390         },
 391         record_id2 => {
 392             field_name1 => 'value3',
 393             field_name2 => 'value4',
 394         }
 395
 396     }
 397
 398 =head2 get_matching_record_ids
 399
 400 Retrieve all record_ids for records matching the field values given in parameter.
 401 This method returns a list of ids.
 402 This is a static method.
 403
 404     my $fields = [
 405         {
 406             name => 'field_name',
 407             value => 'field_value',
 408         }
 409     ];
 410     my $ids = Koha::AdditionalField->get_matching_record_ids(
 411         {
 412             tablename => 'subscription',
 413             fields => $fields
 414         }
 415     );
 416
 417 =head1 AUTHOR
 418
 419 Jonathan Druart <jonathan.druart at biblibre.com>
 420
 421 =head1 COPYRIGHT
 422
 423 Copyright 2013 BibLibre
 424
 425 =head1 LICENSE
 426
 427 This file is part of Koha.
 428
 429 Koha is free software; you can redistribute it and/or modify it under the
 430 terms of the GNU General Public License as published by the Free Software
 431 Foundation; either version 3 of the License, or (at your option) any later
 432 version.
 433
 434 Koha is distributed in the hope that it will be useful, but WITHOUT ANY
 435 WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR
 436 A PARTICULAR PURPOSE. See the GNU General Public License for more details.
 437
 438 You should have received a copy of the GNU General Public License along
 439 with Koha; if not, see <http://www.gnu.org/licenses>.