Attachment #23297 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 142-148 my $dat = &GetBiblioData($biblionumber); Link Here

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

Lines 124-130 my $subscriptionsnumber = CountSubscriptionFromBiblionumber($biblionumber); Link Here

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

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

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

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 ) {





my $subscriptioninformation = GetSubscription( $subscriptionid );

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 38-44 BEGIN { Link Here

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

Lines 71-86 my $subscriptionid = NewSubscription( Link Here

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