Attachment #37739 for bug #11337

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




    require Exporter;
    @ISA    = qw(Exporter);
    @EXPORT = qw(
      &NewSubscription    &ModSubscription    &DelSubscription
      &GetSubscription    &CountSubscriptionFromBiblionumber      &GetSubscriptionsFromBiblionumber
      &SearchSubscriptions
      &GetFullSubscriptionsFromBiblionumber   &GetFullSubscription &ModSubscriptionHistory

    return $subscriptions;
}

=head2 GetSubscriptions

@results = GetSubscriptions($title,$ISSN,$ean,$biblionumber);
this function gets all subscriptions which have title like $title,ISSN like $ISSN,EAN like $ean and biblionumber like $biblionumber.
return:
a table of hashref. Each hash containt the subscription.

=cut

sub GetSubscriptions {
    my ( $string, $issn, $ean, $biblionumber ) = @_;

    #return unless $title or $ISSN or $biblionumber;
    my $dbh = C4::Context->dbh;
    my $sth;
    my $sql = qq(
            SELECT subscriptionhistory.*, subscription.*, biblio.title,biblioitems.issn,biblio.biblionumber
            FROM   subscription
            LEFT JOIN subscriptionhistory USING(subscriptionid)
            LEFT JOIN biblio ON biblio.biblionumber = subscription.biblionumber
            LEFT JOIN biblioitems ON biblio.biblionumber = biblioitems.biblionumber
    );
    my @bind_params;
    my $sqlwhere = q{};
    if ($biblionumber) {
        $sqlwhere = "   WHERE biblio.biblionumber=?";
        push @bind_params, $biblionumber;
    }
    if ($string) {
        my @sqlstrings;
        my @strings_to_search;
        @strings_to_search = map { "%$_%" } split( / /, $string );
        foreach my $index (qw(biblio.title subscription.callnumber subscription.location subscription.notes subscription.internalnotes)) {
            push @bind_params, @strings_to_search;
            my $tmpstring = "AND $index LIKE ? " x scalar(@strings_to_search);
            $debug && warn "$tmpstring";
            $tmpstring =~ s/^AND //;
            push @sqlstrings, $tmpstring;
        }
        $sqlwhere .= ( $sqlwhere ? " AND " : " WHERE " ) . "((" . join( ") OR (", @sqlstrings ) . "))";
    }
    if ($issn) {
        my @sqlstrings;
        my @strings_to_search;
        @strings_to_search = map { "%$_%" } split( / /, $issn );
        foreach my $index ( qw(biblioitems.issn subscription.callnumber)) {
            push @bind_params, @strings_to_search;
            my $tmpstring = "OR $index LIKE ? " x scalar(@strings_to_search);
            $debug && warn "$tmpstring";
            $tmpstring =~ s/^OR //;
            push @sqlstrings, $tmpstring;
        }
        $sqlwhere .= ( $sqlwhere ? " AND " : " WHERE " ) . "((" . join( ") OR (", @sqlstrings ) . "))";
    }
    if ($ean) {
        my @sqlstrings;
        my @strings_to_search;
        @strings_to_search = map { "$_" } split( / /, $ean );
        foreach my $index ( qw(biblioitems.ean) ) {
            push @bind_params, @strings_to_search;
            my $tmpstring = "OR $index = ? " x scalar(@strings_to_search);
            $debug && warn "$tmpstring";
            $tmpstring =~ s/^OR //;
            push @sqlstrings, $tmpstring;
        }
        $sqlwhere .= ( $sqlwhere ? " AND " : " WHERE " ) . "((" . join( ") OR (", @sqlstrings ) . "))";
    }

    $sql .= "$sqlwhere ORDER BY title";
    $debug and warn "GetSubscriptions query: $sql params : ", join( " ", @bind_params );
    $sth = $dbh->prepare($sql);
    $sth->execute(@bind_params);
    my $subscriptions = $sth->fetchall_arrayref( {} );
    for my $subscription ( @$subscriptions ) {
        $subscription->{cannotedit} = not can_edit_subscription( $subscription );
    }
    return @$subscriptions;
}

=head2 SearchSubscriptions

  @results = SearchSubscriptions($args);

sub SearchSubscriptions {
    my ( $args ) = @_;

    my $query = q{
        SELECT
            subscription.notes AS publicnotes,
            subscription.*,
            subscriptionhistory.*,
            subscription.*,
            biblio.notes AS biblionotes,
            biblio.title,
            biblio.author,
            biblio.biblionumber,
            biblioitems.issn
        FROM subscription
            LEFT JOIN subscriptionhistory USING(subscriptionid)

        $query .= " WHERE " . join(" AND ", @where_strs);
    }

    $query .= " ORDER BY " . $args->{orderby} if $args->{orderby};

    my $dbh = C4::Context->dbh;
    my $sth = $dbh->prepare($query);
    $sth->execute(@where_args);

Lines 141-147 my $dat = &GetBiblioData($biblionumber); Link Here

(-)a/catalogue/detail.pl (-1 / +1 lines)
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) {

Lines 121-127 my $subscriptionsnumber = CountSubscriptionFromBiblionumber($biblionumber); Link Here

(-)a/opac/opac-ISBDdetail.pl (-1 / +1 lines)
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;

Lines 530-536 my $copynumbers = GetKohaAuthorisedValues('items.copynumber',$dat->{'frameworkco Link Here

(-)a/opac/opac-detail.pl (-1 / +1 lines)
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;

Lines 70-76 my $issn = $query->param('issn'); Link Here

(-)a/serials/checkexpiration.pl (-1 / +1 lines)
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 ) {




is( $subscription_history->{opacnote}, '', 'NewSubscription should not set subscriptionhistory opacnotes' );
is( $subscription_history->{librariannote}, '', 'NewSubscription should not set subscriptionhistory librariannotes' );

my @subscriptions = SearchSubscriptions({string => $subscriptioninformation->{bibliotitle}, orderby => 'title' });
isa_ok( \@subscriptions, 'ARRAY' );

@subscriptions = SearchSubscriptions({ issn => $subscriptioninformation->{issn}, orderby => 'title' });
isa_ok( \@subscriptions, 'ARRAY' );

@subscriptions = SearchSubscriptions({ ean => $subscriptioninformation->{ean}, orderby => 'title' });
isa_ok( \@subscriptions, 'ARRAY' );

@subscriptions = SearchSubscriptions({ biblionumber => $subscriptioninformation->{bibnum}, orderby => 'title' });
isa_ok( \@subscriptions, 'ARRAY' );

my $frequency = GetSubscriptionFrequency($subscriptioninformation->{periodicity});
- 

Return to bug 11337

Lines 85-100 my $subscription_history = C4::Serials::GetSubscriptionHistoryFromSubscriptionId Link Here

(-)a/t/db_dependent/Serials.t (-5 / +4 lines)
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	-

Lines 39-45 BEGIN { Link Here

(-)a/C4/Serials.pm (-82 / +6 lines)
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);