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

(-)a/C4/Serials.pm (-82 / +6 lines)
Lines 38-44 BEGIN { Link Here
38
    require Exporter;
38
    require Exporter;
39
    @ISA    = qw(Exporter);
39
    @ISA    = qw(Exporter);
40
    @EXPORT = qw(
40
    @EXPORT = qw(
41
      &NewSubscription    &ModSubscription    &DelSubscription    &GetSubscriptions
41
      &NewSubscription    &ModSubscription    &DelSubscription
42
      &GetSubscription    &CountSubscriptionFromBiblionumber      &GetSubscriptionsFromBiblionumber
42
      &GetSubscription    &CountSubscriptionFromBiblionumber      &GetSubscriptionsFromBiblionumber
43
      &SearchSubscriptions
43
      &SearchSubscriptions
44
      &GetFullSubscriptionsFromBiblionumber   &GetFullSubscription &ModSubscriptionHistory
44
      &GetFullSubscriptionsFromBiblionumber   &GetFullSubscription &ModSubscriptionHistory
Lines 547-631 sub GetFullSubscriptionsFromBiblionumber { Link Here
547
    return $subscriptions;
547
    return $subscriptions;
548
}
548
}
549
549
550
=head2 GetSubscriptions
551
552
@results = GetSubscriptions($title,$ISSN,$ean,$biblionumber);
553
this function gets all subscriptions which have title like $title,ISSN like $ISSN,EAN like $ean and biblionumber like $biblionumber.
554
return:
555
a table of hashref. Each hash containt the subscription.
556
557
=cut
558
559
sub GetSubscriptions {
560
    my ( $string, $issn, $ean, $biblionumber ) = @_;
561
562
    #return unless $title or $ISSN or $biblionumber;
563
    my $dbh = C4::Context->dbh;
564
    my $sth;
565
    my $sql = qq(
566
            SELECT subscriptionhistory.*, subscription.*, biblio.title,biblioitems.issn,biblio.biblionumber
567
            FROM   subscription
568
            LEFT JOIN subscriptionhistory USING(subscriptionid)
569
            LEFT JOIN biblio ON biblio.biblionumber = subscription.biblionumber
570
            LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
571
    );
572
    my @bind_params;
573
    my $sqlwhere = q{};
574
    if ($biblionumber) {
575
        $sqlwhere = "   WHERE biblio.biblionumber=?";
576
        push @bind_params, $biblionumber;
577
    }
578
    if ($string) {
579
        my @sqlstrings;
580
        my @strings_to_search;
581
        @strings_to_search = map { "%$_%" } split( / /, $string );
582
        foreach my $index (qw(biblio.title subscription.callnumber subscription.location subscription.notes subscription.internalnotes)) {
583
            push @bind_params, @strings_to_search;
584
            my $tmpstring = "AND $index LIKE ? " x scalar(@strings_to_search);
585
            $debug && warn "$tmpstring";
586
            $tmpstring =~ s/^AND //;
587
            push @sqlstrings, $tmpstring;
588
        }
589
        $sqlwhere .= ( $sqlwhere ? " AND " : " WHERE " ) . "((" . join( ") OR (", @sqlstrings ) . "))";
590
    }
591
    if ($issn) {
592
        my @sqlstrings;
593
        my @strings_to_search;
594
        @strings_to_search = map { "%$_%" } split( / /, $issn );
595
        foreach my $index ( qw(biblioitems.issn subscription.callnumber)) {
596
            push @bind_params, @strings_to_search;
597
            my $tmpstring = "OR $index LIKE ? " x scalar(@strings_to_search);
598
            $debug && warn "$tmpstring";
599
            $tmpstring =~ s/^OR //;
600
            push @sqlstrings, $tmpstring;
601
        }
602
        $sqlwhere .= ( $sqlwhere ? " AND " : " WHERE " ) . "((" . join( ") OR (", @sqlstrings ) . "))";
603
    }
604
    if ($ean) {
605
        my @sqlstrings;
606
        my @strings_to_search;
607
        @strings_to_search = map { "$_" } split( / /, $ean );
608
        foreach my $index ( qw(biblioitems.ean) ) {
609
            push @bind_params, @strings_to_search;
610
            my $tmpstring = "OR $index = ? " x scalar(@strings_to_search);
611
            $debug && warn "$tmpstring";
612
            $tmpstring =~ s/^OR //;
613
            push @sqlstrings, $tmpstring;
614
        }
615
        $sqlwhere .= ( $sqlwhere ? " AND " : " WHERE " ) . "((" . join( ") OR (", @sqlstrings ) . "))";
616
    }
617
618
    $sql .= "$sqlwhere ORDER BY title";
619
    $debug and warn "GetSubscriptions query: $sql params : ", join( " ", @bind_params );
620
    $sth = $dbh->prepare($sql);
621
    $sth->execute(@bind_params);
622
    my $subscriptions = $sth->fetchall_arrayref( {} );
623
    for my $subscription ( @$subscriptions ) {
624
        $subscription->{cannotedit} = not can_edit_subscription( $subscription );
625
    }
626
    return @$subscriptions;
627
}
628
629
=head2 SearchSubscriptions
550
=head2 SearchSubscriptions
630
551
631
@results = SearchSubscriptions($args);
552
@results = SearchSubscriptions($args);
Lines 641-654 a table of hashref. Each hash containt the subscription. Link Here
641
sub SearchSubscriptions {
562
sub SearchSubscriptions {
642
    my ( $args ) = @_;
563
    my ( $args ) = @_;
643
564
644
    my $query = qq{
565
    my $query = q{
645
        SELECT
566
        SELECT
646
            subscription.notes AS publicnotes,
567
            subscription.notes AS publicnotes,
647
            subscription.*,
648
            subscriptionhistory.*,
568
            subscriptionhistory.*,
569
            subscription.*,
649
            biblio.notes AS biblionotes,
570
            biblio.notes AS biblionotes,
650
            biblio.title,
571
            biblio.title,
651
            biblio.author,
572
            biblio.author,
573
            biblio.biblionumber,
652
            biblioitems.issn
574
            biblioitems.issn
653
        FROM subscription
575
        FROM subscription
654
            LEFT JOIN subscriptionhistory USING(subscriptionid)
576
            LEFT JOIN subscriptionhistory USING(subscriptionid)
Lines 714-719 sub SearchSubscriptions { Link Here
714
        $query .= " WHERE " . join(" AND ", @where_strs);
636
        $query .= " WHERE " . join(" AND ", @where_strs);
715
    }
637
    }
716
638
639
    $query .= " ORDER BY " . $args->{orderby} if $args->{orderby};
640
717
    my $dbh = C4::Context->dbh;
641
    my $dbh = C4::Context->dbh;
718
    my $sth = $dbh->prepare($query);
642
    my $sth = $dbh->prepare($query);
719
    $sth->execute(@where_args);
643
    $sth->execute(@where_args);
(-)a/catalogue/detail.pl (-1 / +1 lines)
Lines 142-148 my $dat = &GetBiblioData($biblionumber); Link Here
142
142
143
#coping with subscriptions
143
#coping with subscriptions
144
my $subscriptionsnumber = CountSubscriptionFromBiblionumber($biblionumber);
144
my $subscriptionsnumber = CountSubscriptionFromBiblionumber($biblionumber);
145
my @subscriptions       = GetSubscriptions( $dat->{title}, $dat->{issn}, undef, $biblionumber );
145
my @subscriptions       = SearchSubscriptions({ biblionumber => $biblionumber, orderby => 'title' });
146
my @subs;
146
my @subs;
147
147
148
foreach my $subscription (@subscriptions) {
148
foreach my $subscription (@subscriptions) {
(-)a/opac/opac-ISBDdetail.pl (-1 / +1 lines)
Lines 124-130 my $subscriptionsnumber = CountSubscriptionFromBiblionumber($biblionumber); Link Here
124
my $dbh = C4::Context->dbh;
124
my $dbh = C4::Context->dbh;
125
my $dat                 = TransformMarcToKoha( $dbh, $record );
125
my $dat                 = TransformMarcToKoha( $dbh, $record );
126
126
127
my @subscriptions       = GetSubscriptions( $dat->{title}, $dat->{issn}, undef, $biblionumber );
127
my @subscriptions       = SearchSubscriptions({ biblionumber => $biblionumber, orderby => 'title' });
128
my @subs;
128
my @subs;
129
foreach my $subscription (@subscriptions) {
129
foreach my $subscription (@subscriptions) {
130
    my %cell;
130
    my %cell;
(-)a/opac/opac-detail.pl (-1 / +1 lines)
Lines 499-505 my $copynumbers = GetKohaAuthorisedValues('items.copynumber',$dat->{'frameworkco Link Here
499
499
500
#coping with subscriptions
500
#coping with subscriptions
501
my $subscriptionsnumber = CountSubscriptionFromBiblionumber($biblionumber);
501
my $subscriptionsnumber = CountSubscriptionFromBiblionumber($biblionumber);
502
my @subscriptions       = GetSubscriptions($dat->{'title'}, $dat->{'issn'}, $ean, $biblionumber );
502
my @subscriptions       = SearchSubscriptions({ biblionumber => $biblionumber, orderby => 'title' });
503
503
504
my @subs;
504
my @subs;
505
$dat->{'serial'}=1 if $subscriptionsnumber;
505
$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 71-86 my $subscriptionid = NewSubscription( Link Here
71
71
72
my $subscriptioninformation = GetSubscription( $subscriptionid );
72
my $subscriptioninformation = GetSubscription( $subscriptionid );
73
73
74
my @subscriptions = GetSubscriptions( $$subscriptioninformation{bibliotitle} );
74
my @subscriptions = SearchSubscriptions({string => $subscriptioninformation->{bibliotitle}, orderby => 'title' });
75
isa_ok( \@subscriptions, 'ARRAY' );
75
isa_ok( \@subscriptions, 'ARRAY' );
76
76
77
@subscriptions = GetSubscriptions( undef, $$subscriptioninformation{issn} );
77
@subscriptions = SearchSubscriptions({ issn => $subscriptioninformation->{issn}, orderby => 'title' });
78
isa_ok( \@subscriptions, 'ARRAY' );
78
isa_ok( \@subscriptions, 'ARRAY' );
79
79
80
@subscriptions = GetSubscriptions( undef, undef, $$subscriptioninformation{ean} );
80
@subscriptions = SearchSubscriptions({ ean => $subscriptioninformation->{ean}, orderby => 'title' });
81
isa_ok( \@subscriptions, 'ARRAY' );
81
isa_ok( \@subscriptions, 'ARRAY' );
82
82
83
@subscriptions = GetSubscriptions( undef, undef, undef, $$subscriptioninformation{bibnum} );
83
@subscriptions = SearchSubscriptions({ biblionumber => $subscriptioninformation->{bibnum}, orderby => 'title' });
84
isa_ok( \@subscriptions, 'ARRAY' );
84
isa_ok( \@subscriptions, 'ARRAY' );
85
85
86
my $frequency = GetSubscriptionFrequency($subscriptioninformation->{periodicity});
86
my $frequency = GetSubscriptionFrequency($subscriptioninformation->{periodicity});
87
- 

Return to bug 11337