View | Details | Raw Unified | Return to bug 11337
Collapse All | Expand All

(-)a/C4/Serials.pm (-82 / +6 lines)
Lines 39-45 BEGIN { Link Here
39
    require Exporter;
39
    require Exporter;
40
    @ISA    = qw(Exporter);
40
    @ISA    = qw(Exporter);
41
    @EXPORT = qw(
41
    @EXPORT = qw(
42
      &NewSubscription    &ModSubscription    &DelSubscription    &GetSubscriptions
42
      &NewSubscription    &ModSubscription    &DelSubscription
43
      &GetSubscription    &CountSubscriptionFromBiblionumber      &GetSubscriptionsFromBiblionumber
43
      &GetSubscription    &CountSubscriptionFromBiblionumber      &GetSubscriptionsFromBiblionumber
44
      &SearchSubscriptions
44
      &SearchSubscriptions
45
      &GetFullSubscriptionsFromBiblionumber   &GetFullSubscription &ModSubscriptionHistory
45
      &GetFullSubscriptionsFromBiblionumber   &GetFullSubscription &ModSubscriptionHistory
Lines 496-580 sub GetFullSubscriptionsFromBiblionumber { Link Here
496
    return $subscriptions;
496
    return $subscriptions;
497
}
497
}
498
498
499
=head2 GetSubscriptions
500
501
@results = GetSubscriptions($title,$ISSN,$ean,$biblionumber);
502
this function gets all subscriptions which have title like $title,ISSN like $ISSN,EAN like $ean and biblionumber like $biblionumber.
503
return:
504
a table of hashref. Each hash containt the subscription.
505
506
=cut
507
508
sub GetSubscriptions {
509
    my ( $string, $issn, $ean, $biblionumber ) = @_;
510
511
    #return unless $title or $ISSN or $biblionumber;
512
    my $dbh = C4::Context->dbh;
513
    my $sth;
514
    my $sql = qq(
515
            SELECT subscriptionhistory.*, subscription.*, biblio.title,biblioitems.issn,biblio.biblionumber
516
            FROM   subscription
517
            LEFT JOIN subscriptionhistory USING(subscriptionid)
518
            LEFT JOIN biblio ON biblio.biblionumber = subscription.biblionumber
519
            LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
520
    );
521
    my @bind_params;
522
    my $sqlwhere = q{};
523
    if ($biblionumber) {
524
        $sqlwhere = "   WHERE biblio.biblionumber=?";
525
        push @bind_params, $biblionumber;
526
    }
527
    if ($string) {
528
        my @sqlstrings;
529
        my @strings_to_search;
530
        @strings_to_search = map { "%$_%" } split( / /, $string );
531
        foreach my $index (qw(biblio.title subscription.callnumber subscription.location subscription.notes subscription.internalnotes)) {
532
            push @bind_params, @strings_to_search;
533
            my $tmpstring = "AND $index LIKE ? " x scalar(@strings_to_search);
534
            $debug && warn "$tmpstring";
535
            $tmpstring =~ s/^AND //;
536
            push @sqlstrings, $tmpstring;
537
        }
538
        $sqlwhere .= ( $sqlwhere ? " AND " : " WHERE " ) . "((" . join( ") OR (", @sqlstrings ) . "))";
539
    }
540
    if ($issn) {
541
        my @sqlstrings;
542
        my @strings_to_search;
543
        @strings_to_search = map { "%$_%" } split( / /, $issn );
544
        foreach my $index ( qw(biblioitems.issn subscription.callnumber)) {
545
            push @bind_params, @strings_to_search;
546
            my $tmpstring = "OR $index LIKE ? " x scalar(@strings_to_search);
547
            $debug && warn "$tmpstring";
548
            $tmpstring =~ s/^OR //;
549
            push @sqlstrings, $tmpstring;
550
        }
551
        $sqlwhere .= ( $sqlwhere ? " AND " : " WHERE " ) . "((" . join( ") OR (", @sqlstrings ) . "))";
552
    }
553
    if ($ean) {
554
        my @sqlstrings;
555
        my @strings_to_search;
556
        @strings_to_search = map { "$_" } split( / /, $ean );
557
        foreach my $index ( qw(biblioitems.ean) ) {
558
            push @bind_params, @strings_to_search;
559
            my $tmpstring = "OR $index = ? " x scalar(@strings_to_search);
560
            $debug && warn "$tmpstring";
561
            $tmpstring =~ s/^OR //;
562
            push @sqlstrings, $tmpstring;
563
        }
564
        $sqlwhere .= ( $sqlwhere ? " AND " : " WHERE " ) . "((" . join( ") OR (", @sqlstrings ) . "))";
565
    }
566
567
    $sql .= "$sqlwhere ORDER BY title";
568
    $debug and warn "GetSubscriptions query: $sql params : ", join( " ", @bind_params );
569
    $sth = $dbh->prepare($sql);
570
    $sth->execute(@bind_params);
571
    my $subscriptions = $sth->fetchall_arrayref( {} );
572
    for my $subscription ( @$subscriptions ) {
573
        $subscription->{cannotedit} = not can_edit_subscription( $subscription );
574
    }
575
    return @$subscriptions;
576
}
577
578
=head2 SearchSubscriptions
499
=head2 SearchSubscriptions
579
500
580
  @results = SearchSubscriptions($args);
501
  @results = SearchSubscriptions($args);
Lines 604-617 subscription expiration date. Link Here
604
sub SearchSubscriptions {
525
sub SearchSubscriptions {
605
    my ( $args ) = @_;
526
    my ( $args ) = @_;
606
527
607
    my $query = qq{
528
    my $query = q{
608
        SELECT
529
        SELECT
609
            subscription.notes AS publicnotes,
530
            subscription.notes AS publicnotes,
610
            subscription.*,
611
            subscriptionhistory.*,
531
            subscriptionhistory.*,
532
            subscription.*,
612
            biblio.notes AS biblionotes,
533
            biblio.notes AS biblionotes,
613
            biblio.title,
534
            biblio.title,
614
            biblio.author,
535
            biblio.author,
536
            biblio.biblionumber,
615
            biblioitems.issn
537
            biblioitems.issn
616
        FROM subscription
538
        FROM subscription
617
            LEFT JOIN subscriptionhistory USING(subscriptionid)
539
            LEFT JOIN subscriptionhistory USING(subscriptionid)
Lines 677-682 sub SearchSubscriptions { Link Here
677
        $query .= " WHERE " . join(" AND ", @where_strs);
599
        $query .= " WHERE " . join(" AND ", @where_strs);
678
    }
600
    }
679
601
602
    $query .= " ORDER BY " . $args->{orderby} if $args->{orderby};
603
680
    my $dbh = C4::Context->dbh;
604
    my $dbh = C4::Context->dbh;
681
    my $sth = $dbh->prepare($query);
605
    my $sth = $dbh->prepare($query);
682
    $sth->execute(@where_args);
606
    $sth->execute(@where_args);
(-)a/catalogue/detail.pl (-1 / +1 lines)
Lines 141-147 my $dat = &GetBiblioData($biblionumber); Link Here
141
141
142
#coping with subscriptions
142
#coping with subscriptions
143
my $subscriptionsnumber = CountSubscriptionFromBiblionumber($biblionumber);
143
my $subscriptionsnumber = CountSubscriptionFromBiblionumber($biblionumber);
144
my @subscriptions       = GetSubscriptions( $dat->{title}, $dat->{issn}, undef, $biblionumber );
144
my @subscriptions       = SearchSubscriptions({ biblionumber => $biblionumber, orderby => 'title' });
145
my @subs;
145
my @subs;
146
146
147
foreach my $subscription (@subscriptions) {
147
foreach my $subscription (@subscriptions) {
(-)a/opac/opac-ISBDdetail.pl (-1 / +1 lines)
Lines 121-127 my $subscriptionsnumber = CountSubscriptionFromBiblionumber($biblionumber); Link Here
121
my $dbh = C4::Context->dbh;
121
my $dbh = C4::Context->dbh;
122
my $dat                 = TransformMarcToKoha( $dbh, $record );
122
my $dat                 = TransformMarcToKoha( $dbh, $record );
123
123
124
my @subscriptions       = GetSubscriptions( $dat->{title}, $dat->{issn}, undef, $biblionumber );
124
my @subscriptions       = SearchSubscriptions({ biblionumber => $biblionumber, orderby => 'title' });
125
my @subs;
125
my @subs;
126
foreach my $subscription (@subscriptions) {
126
foreach my $subscription (@subscriptions) {
127
    my %cell;
127
    my %cell;
(-)a/opac/opac-detail.pl (-1 / +1 lines)
Lines 530-536 my $copynumbers = GetKohaAuthorisedValues('items.copynumber',$dat->{'frameworkco Link Here
530
530
531
#coping with subscriptions
531
#coping with subscriptions
532
my $subscriptionsnumber = CountSubscriptionFromBiblionumber($biblionumber);
532
my $subscriptionsnumber = CountSubscriptionFromBiblionumber($biblionumber);
533
my @subscriptions       = GetSubscriptions($dat->{'title'}, $dat->{'issn'}, $ean, $biblionumber );
533
my @subscriptions       = SearchSubscriptions({ biblionumber => $biblionumber, orderby => 'title' });
534
534
535
my @subs;
535
my @subs;
536
$dat->{'serial'}=1 if $subscriptionsnumber;
536
$dat->{'serial'}=1 if $subscriptionsnumber;
(-)a/serials/checkexpiration.pl (-1 / +1 lines)
Lines 70-76 my $issn = $query->param('issn'); Link Here
70
my $date  = format_date_in_iso($query->param('date'));
70
my $date  = format_date_in_iso($query->param('date'));
71
71
72
if ($date) {
72
if ($date) {
73
    my @subscriptions = GetSubscriptions( $title, $issn );
73
    my @subscriptions = SearchSubscriptions({ title => $title, issn => $issn, orderby => 'title' });
74
    my @subscriptions_loop;
74
    my @subscriptions_loop;
75
75
76
    foreach my $subscription ( @subscriptions ) {
76
    foreach my $subscription ( @subscriptions ) {
(-)a/t/db_dependent/Serials.t (-5 / +4 lines)
Lines 85-100 my $subscription_history = C4::Serials::GetSubscriptionHistoryFromSubscriptionId Link Here
85
is( $subscription_history->{opacnote}, '', 'NewSubscription should not set subscriptionhistory opacnotes' );
85
is( $subscription_history->{opacnote}, '', 'NewSubscription should not set subscriptionhistory opacnotes' );
86
is( $subscription_history->{librariannote}, '', 'NewSubscription should not set subscriptionhistory librariannotes' );
86
is( $subscription_history->{librariannote}, '', 'NewSubscription should not set subscriptionhistory librariannotes' );
87
87
88
my @subscriptions = GetSubscriptions( $$subscriptioninformation{bibliotitle} );
88
my @subscriptions = SearchSubscriptions({string => $subscriptioninformation->{bibliotitle}, orderby => 'title' });
89
isa_ok( \@subscriptions, 'ARRAY' );
89
isa_ok( \@subscriptions, 'ARRAY' );
90
90
91
@subscriptions = GetSubscriptions( undef, $$subscriptioninformation{issn} );
91
@subscriptions = SearchSubscriptions({ issn => $subscriptioninformation->{issn}, orderby => 'title' });
92
isa_ok( \@subscriptions, 'ARRAY' );
92
isa_ok( \@subscriptions, 'ARRAY' );
93
93
94
@subscriptions = GetSubscriptions( undef, undef, $$subscriptioninformation{ean} );
94
@subscriptions = SearchSubscriptions({ ean => $subscriptioninformation->{ean}, orderby => 'title' });
95
isa_ok( \@subscriptions, 'ARRAY' );
95
isa_ok( \@subscriptions, 'ARRAY' );
96
96
97
@subscriptions = GetSubscriptions( undef, undef, undef, $$subscriptioninformation{bibnum} );
97
@subscriptions = SearchSubscriptions({ biblionumber => $subscriptioninformation->{bibnum}, orderby => 'title' });
98
isa_ok( \@subscriptions, 'ARRAY' );
98
isa_ok( \@subscriptions, 'ARRAY' );
99
99
100
my $frequency = GetSubscriptionFrequency($subscriptioninformation->{periodicity});
100
my $frequency = GetSubscriptionFrequency($subscriptioninformation->{periodicity});
101
- 

Return to bug 11337