Attachment #13960 for bug #8443

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




# with Koha; if not, write to the Free Software Foundation, Inc.,
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.


use strict;

#use warnings; FIXME - Bug 2505
use CGI;


use C4::SQLHelper qw(:all);
use C4::Debug;
use C4::Letters;
use List::MoreUtils qw(any);
use C4::Dates qw(format_date_in_iso);
use base qw(Exporter);

our $VERSION = 3.07.00.049;
our @EXPORT  = qw(
  ConnectSuggestionAndBiblio
  CountSuggestion
  DelSuggestion
  GetSuggestion
  GetSuggestionByStatus
  GetSuggestionFromBiblionumber
  GetSuggestionInfoFromBiblionumber
  GetSuggestionInfo
  ModStatus
  ModSuggestion
  NewSuggestion
  SearchSuggestion
  DelSuggestionsOlderThan
);

=head1 NAME



=cut

sub SearchSuggestion {
    my ($suggestion) = @_;
    my $dbh = C4::Context->dbh;
    my @sql_params;
    my @query = (
        q{
        SELECT suggestions.*,
            U1.branchcode       AS branchcodesuggestedby,
            B1.branchname       AS branchnamesuggestedby,
            U1.surname          AS surnamesuggestedby,
            U1.firstname        AS firstnamesuggestedby,
            U1.email            AS emailsuggestedby,
            U1.borrowernumber   AS borrnumsuggestedby,
            U1.categorycode     AS categorycodesuggestedby,
            C1.description      AS categorydescriptionsuggestedby,
            U2.surname          AS surnamemanagedby,
            U2.firstname        AS firstnamemanagedby,
            B2.branchname       AS branchnamesuggestedby,
            U2.email            AS emailmanagedby,
            U2.branchcode       AS branchcodemanagedby,
            U2.borrowernumber   AS borrnummanagedby
        FROM suggestions
            LEFT JOIN borrowers     AS U1 ON suggestedby=U1.borrowernumber
            LEFT JOIN branches      AS B1 ON B1.branchcode=U1.branchcode
            LEFT JOIN categories    AS C1 ON C1.categorycode=U1.categorycode
            LEFT JOIN borrowers     AS U2 ON managedby=U2.borrowernumber
            LEFT JOIN branches      AS B2 ON B2.branchcode=U2.branchcode
            LEFT JOIN categories    AS C2 ON C2.categorycode=U2.categorycode
        WHERE 1=1
    }
        push @sql_params,'%'.$s.'%'; 
        " and suggestions.$_ like ? ";
        } else { () }
    } qw( title author isbn publishercode copyrightdate collectiontitle )
    );

    # filter on biblio informations
    foreach my $field (
        qw( title author isbn publishercode copyrightdate collectiontitle ))
    {
        if ( $suggestion->{$field} ) {
            push @sql_params, '%' . $suggestion->{$field} . '%';
            push @query,      qq{ AND suggestions.$field LIKE ? };
        }
    }

    # filter on user branch
    if ( C4::Context->preference('IndependantBranches') ) {
        my $userenv = C4::Context->userenv;
        if ($userenv) {
            if ( ( $userenv->{flags} % 2 ) != 1 && !$suggestion->{branchcode} )
            {
                push @sql_params, $$userenv{branch};
                push @query,      q{
                    AND (suggestions.branchcode=? OR suggestions.branchcode='')
                };
            }
        }
    }

    # filter on nillable fields
    foreach my $field (
        qw( STATUS branchcode itemtype suggestedby managedby acceptedby budgetid biblionumber )
      )
    {
        if ( exists $suggestion->{$field} ) {
            if ( defined $suggestion->{$field} and $suggestion->{$field} ne '' )
            {
                push @sql_params, $suggestion->{$field};
                push @query,      qq{ AND suggestions.$field=? };
            }
            else {
                push @query, qq{
                    AND (suggestions.$field='' OR suggestions.$field IS NULL)
                };
            }
        }
    }

    # filter on date fields
    my $today = C4::Dates->today('iso');
    foreach my $field (qw( suggesteddate manageddate accepteddate )) {
        my $from = $field . "_from";
        my $to   = $field . "_to";
        if ( $suggestion->{$from} || $suggestion->{$to} ) {
            push @query, qq{ AND suggestions.$field BETWEEN ? AND ? };
            push @sql_params,
              format_date_in_iso( $suggestion->{$from} ) || '0000-00-00';
            push @sql_params,
              format_date_in_iso( $suggestion->{$to} ) || $today;
        }
    }

    $debug && warn "@query";
    my $sth = $dbh->prepare("@query");
    $sth->execute(@sql_params);
    my @results;

    # add status as field
    while ( my $data = $sth->fetchrow_hashref ) {
        $data->{ $data->{STATUS} } = 1;
        push( @results, $data );
    }

    return ( \@results );
}

=head2 GetSuggestion


sub GetSuggestion {
    my ($ordernumber) = @_;
    my $dbh           = C4::Context->dbh;
    my $query         = q{
        SELECT *
        FROM   suggestions
        WHERE  suggestionid=?
    };
    my $sth = $dbh->prepare($query);
    $sth->execute($ordernumber);
    return ( $sth->fetchrow_hashref );
}

=head2 GetSuggestionFromBiblionumber

        FROM   suggestions
        WHERE  biblionumber=? LIMIT 1
    };
    my $dbh = C4::Context->dbh;
    my $sth = $dbh->prepare($query);
    $sth->execute($biblionumber);
    my ($suggestionid) = $sth->fetchrow;


sub GetSuggestionInfoFromBiblionumber {
    my ($biblionumber) = @_;
    my $query = q{
        SELECT suggestions.*,
            U1.surname          AS surnamesuggestedby,
            U1.firstname        AS firstnamesuggestedby,
            U1.borrowernumber   AS borrnumsuggestedby
        FROM suggestions
            LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
        WHERE biblionumber=?
        LIMIT 1
    };
    my $dbh = C4::Context->dbh;
    my $sth = $dbh->prepare($query);


sub GetSuggestionInfo {
    my ($suggestionid) = @_;
    my $query = q{
        SELECT suggestions.*,
            U1.surname          AS surnamesuggestedby,
            U1.firstname        AS firstnamesuggestedby,
            U1.borrowernumber   AS borrnumsuggestedby
        FROM suggestions
            LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
        WHERE suggestionid=?
        LIMIT 1
    };
    my $dbh = C4::Context->dbh;
    my $sth = $dbh->prepare($query);

=cut

sub GetSuggestionByStatus {
    my $status     = shift;
    my $branchcode = shift;
    my $dbh        = C4::Context->dbh;
    my @sql_params = ($status);
    my $query      = q{
        SELECT suggestions.*,
            U1.surname          AS surnamesuggestedby,
            U1.firstname        AS firstnamesuggestedby,
            U1.branchcode       AS branchcodesuggestedby,
            B1.branchname       AS branchnamesuggestedby,
            U1.borrowernumber   AS borrnumsuggestedby,
            U1.categorycode     AS categorycodesuggestedby,
            C1.description      AS categorydescriptionsuggestedby,
            U2.surname          AS surnamemanagedby,
            U2.firstname        AS firstnamemanagedby,
            U2.borrowernumber   AS borrnummanagedby
        FROM suggestions
            LEFT JOIN borrowers     AS U1 ON suggestedby=U1.borrowernumber
            LEFT JOIN borrowers     AS U2 ON managedby=U2.borrowernumber
            LEFT JOIN categories    AS C1 ON C1.categorycode=U1.categorycode
            LEFT JOIN branches      AS B1 on B1.branchcode=U1.branchcode
        WHERE status = ?
    };

    # filter on branch
    if ( C4::Context->preference("IndependantBranches") || $branchcode ) {
        my $userenv = C4::Context->userenv;
        if ($userenv) {
            unless ( $userenv->{flags} % 2 == 1 ) {
                push @sql_params, $userenv->{branch};
                $query .= q{ AND (U1.branchcode = ? OR U1.branchcode ='') };
            }
        }
        if ($branchcode) {
            push @sql_params, $branchcode;
            $query .= q{ AND (U1.branchcode = ? OR U1.branchcode ='') };
        }
    }
    
    my $sth = $dbh->prepare($query);
    $sth->execute(@sql_params);
    
    my $results;
    $results = $sth->fetchall_arrayref( {} );
    return $results;
}


    my ($status) = @_;
    my $dbh = C4::Context->dbh;
    my $sth;
    my $userenv = C4::Context->userenv;
    if ( C4::Context->preference("IndependantBranches")
        && $userenv->{flags} % 2 != 1 )
    {
        my $query = q{
            SELECT count(*)
            FROM suggestions
                LEFT JOIN borrowers ON borrowers.borrowernumber=suggestions.suggestedby
            WHERE STATUS=?
                AND (borrowers.branchcode='' OR borrowers.branchcode=?)
        };
        $sth = $dbh->prepare($query);
        $sth->execute( $status, $userenv->{branch} );
                SELECT count(*)
                FROM suggestions LEFT JOIN borrowers ON borrowers.borrowernumber=suggestions.suggestedby
                WHERE STATUS=?
                AND (borrowers.branchcode='' OR borrowers.branchcode =?)
            |;
            $sth = $dbh->prepare($query);
            $sth->execute($status,$userenv->{branch});
        }
    }
    else {
        my $query = q{
            SELECT count(*)
            FROM suggestions
            WHERE STATUS=?
        };
        $sth = $dbh->prepare($query);
        $sth->execute($status);
    }


sub NewSuggestion {
    my ($suggestion) = @_;
    $suggestion->{STATUS} = "ASKED" unless $suggestion->{STATUS};
    return InsertInTable( "suggestions", $suggestion );
}

=head2 ModSuggestion

=cut

sub ModSuggestion {
    my ($suggestion) = @_;
    my $status_update_table = UpdateInTable( "suggestions", $suggestion );

    if ( $suggestion->{STATUS} ) {

    if ($suggestion->{STATUS}) {
        # fetch the entire updated suggestion so that we can populate the letter
        my $full_suggestion = GetSuggestion( $suggestion->{suggestionid} );
        if (
            my $letter = C4::Letters::GetPreparedLetter(
                module      => 'suggestions',
                letter_code => $full_suggestion->{STATUS},
                branchcode  => $full_suggestion->{branchcode},
                tables      => {
                    'branches'    => $full_suggestion->{branchcode},
                    'borrowers'   => $full_suggestion->{suggestedby},
                    'suggestions' => $full_suggestion,
                    'biblio'      => $full_suggestion->{biblionumber},
                },
            )
          )
        {
            C4::Letters::EnqueueLetter(
                {
                    letter         => $letter,
                    borrowernumber => $full_suggestion->{suggestedby},
                    suggestionid   => $full_suggestion->{suggestionid},
                    LibraryName    => C4::Context->preference("LibraryName"),
                    message_transport_type => 'email',
                }
            ) or warn "can't enqueue letter $letter";
        }
    }
    return $status_update_table;

=cut

sub ConnectSuggestionAndBiblio {
    my ( $suggestionid, $biblionumber ) = @_;
    my $dbh   = C4::Context->dbh;
    my $query = q{
        UPDATE suggestions
        SET    biblionumber=?
        WHERE  suggestionid=?
    };
    my $sth = $dbh->prepare($query);
    $sth->execute( $biblionumber, $suggestionid );
}

=head2 DelSuggestion

=cut

sub DelSuggestion {
    my ( $borrowernumber, $suggestionid, $type ) = @_;
    my $dbh = C4::Context->dbh;

    # check that the suggestion comes from the suggestor
    my $query = q{
        SELECT suggestedby
        FROM   suggestions
        WHERE  suggestionid=?
    };
    my $sth = $dbh->prepare($query);
    $sth->execute($suggestionid);
    my ($suggestedby) = $sth->fetchrow;
    if ( $type eq 'intranet' || $suggestedby eq $borrowernumber ) {
        my $queryDelete = q{
            DELETE FROM suggestions
            WHERE suggestionid=?
        };
        $sth = $dbh->prepare($queryDelete);
        my $suggestiondeleted = $sth->execute($suggestionid);
        return $suggestiondeleted;
    }
}


    Delete all suggestions older than TODAY-$days , that have be accepted or rejected.
    
=cut

sub DelSuggestionsOlderThan {
    my ($days) = @_;
    return unless $days;
    my $dbh = C4::Context->dbh;
    my $sth = $dbh->prepare(
        q{
        DELETE FROM suggestions
        WHERE STATUS<>'ASKED'
            AND date < ADDDATE(NOW(), ?)
    }
    );
    $sth->execute("-$days");
}





                    <li><label for="suggesteddate_from"> Suggested on:</label>
                        <table class="invis">
                        <tr>
                        <td><label for="suggesteddate_from">from:</label></td><td><input type="text" id="suggesteddate_from" name="suggesteddate_from" value="[% suggesteddate_from %]" style="width:76px;" /></td>
                        </tr>
                        <tr>
                        <td><label for="suggesteddate_to">to:</label></td><td><input type="text" id="suggesteddate_to" name="suggesteddate_to" value="[% suggesteddate_to %]" style="width:76px;" /></td>
                        </tr>
                        </table>
                    </li>

                    <li><label for="manageddate_from"> Management date:</label>
                        <table class="invis">
                        <tr>
                        <td><label for="manageddate_from">from:</label></td><td><input type="text" id="manageddate_from" name="manageddate_from" value="[% manageddate_from %]" style="width:76px;" /></td>
                        </tr>
                        <tr>
                        <td><label for="manageddate_to">to:</label></td><td><input type="text" id="manageddate_to" name="manageddate_to" value="[% manageddate_to %]" style="width:76px;" /></td>
                        </tr>
                        </table>
                    </li>

                    <li><label for="accepteddate_from"> Accepted on:</label>
                        <table class="invis">
                        <tr>
                        <td><label for="accepteddate_from">from:</label></td><td><input type="text" id="accepteddate_from" name="accepteddate_from" value="[% accepteddate_from %]" style="width:76px;" /></td>
                        </tr>
                        <tr>
                        <td><label for="accepteddate_to">to:</label></td><td><input type="text" id="accepteddate_to" name="accepteddate_to" value="[% accepteddate_to %]" style="width:76px;" /></td>
                        </tr>
                        </table>
                    </li>
- 

Return to bug 8443

Lines 514-523 $(document).ready(function() { calcNewsuggTotal(); }); Link Here

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/suggestion/suggestion.tt (-7 / +6 lines)
514	<li><label for="suggesteddate_from"> Suggested on:</label>	514	<li><label for="suggesteddate_from"> Suggested on:</label>
515	<table class="invis">	515	<table class="invis">
516	<tr>	516	<tr>
517	<td><label for="suggesteddate_from">from:</label></td><td><input type="text" id="suggesteddate_from" name="suggesteddate_from" value="[% suggesteddate_from %]" style="width:80px;" /></td>	517	<td><label for="suggesteddate_from">from:</label></td><td><input type="text" id="suggesteddate_from" name="suggesteddate_from" value="[% suggesteddate_from %]" style="width:76px;" /></td>
518	</tr>	518	</tr>
519	<tr>	519	<tr>
520	<td><label for="suggesteddate_to">to:</label></td><td><input type="text" id="suggesteddate_to" name="suggesteddate_to" value="[% suggesteddate_to %]" style="width:80px;" /></td>	520	<td><label for="suggesteddate_to">to:</label></td><td><input type="text" id="suggesteddate_to" name="suggesteddate_to" value="[% suggesteddate_to %]" style="width:76px;" /></td>
521	</tr>	521	</tr>
522	</table>	522	</table>
523	</li>	523	</li>
Lines 527-536 $(document).ready(function() { calcNewsuggTotal(); }); Link Here
527	<li><label for="manageddate_from"> Management date:</label>	527	<li><label for="manageddate_from"> Management date:</label>
528	<table class="invis">	528	<table class="invis">
529	<tr>	529	<tr>
530	<td><label for="manageddate_from">from:</label></td><td><input type="text" id="manageddate_from" name="manageddate_from" value="[% manageddate_from %]" style="width:80px;" /></td>	530	<td><label for="manageddate_from">from:</label></td><td><input type="text" id="manageddate_from" name="manageddate_from" value="[% manageddate_from %]" style="width:76px;" /></td>
531	</tr>	531	</tr>
532	<tr>	532	<tr>
533	<td><label for="manageddate_to">to:</label></td><td><input type="text" id="manageddate_to" name="manageddate_to" value="[% manageddate_to %]" style="width:80px;" /></td>	533	<td><label for="manageddate_to">to:</label></td><td><input type="text" id="manageddate_to" name="manageddate_to" value="[% manageddate_to %]" style="width:76px;" /></td>
534	</tr>	534	</tr>
535	</table>	535	</table>
536	</li>	536	</li>
Lines 540-549 $(document).ready(function() { calcNewsuggTotal(); }); Link Here
540	<li><label for="accepteddate_from"> Accepted on:</label>	540	<li><label for="accepteddate_from"> Accepted on:</label>
541	<table class="invis">	541	<table class="invis">
542	<tr>	542	<tr>
543	<td><label for="accepteddate_from">from:</label></td><td><input type="text" id="accepteddate_from" name="accepteddate_from" value="[% accepteddate_from %]" style="width:80px;" /></td>	543	<td><label for="accepteddate_from">from:</label></td><td><input type="text" id="accepteddate_from" name="accepteddate_from" value="[% accepteddate_from %]" style="width:76px;" /></td>
544	</tr>	544	</tr>
545	<tr>	545	<tr>
546	<td><label for="accepteddate_to">to:</label></td><td><input type="text" id="accepteddate_to" name="accepteddate_to" value="[% accepteddate_to %]" style="width:80px;" /></td>	546	<td><label for="accepteddate_to">to:</label></td><td><input type="text" id="accepteddate_to" name="accepteddate_to" value="[% accepteddate_to %]" style="width:76px;" /></td>
547	</tr>	547	</tr>
548	</table>	548	</table>
549	</li>	549	</li>
550	-

Lines 18-25 package C4::Suggestions; Link Here

(-)a/C4/Suggestions.pm (-190 / +219 lines)
18	# with Koha; if not, write to the Free Software Foundation, Inc.,	18	# with Koha; if not, write to the Free Software Foundation, Inc.,
19	# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.	19	# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
20		20
21
22	use strict;	21	use strict;
		22
23	#use warnings; FIXME - Bug 2505	23	#use warnings; FIXME - Bug 2505
24	use CGI;	24	use CGI;
25		25
Lines 29-53 use C4::Dates qw(format_date format_date_in_iso); Link Here
29	use C4::SQLHelper qw(:all);	29	use C4::SQLHelper qw(:all);
30	use C4::Debug;	30	use C4::Debug;
31	use C4::Letters;	31	use C4::Letters;
32	use List::MoreUtils qw<any>;	32	use List::MoreUtils qw(any);
33	use C4::Dates qw(format_date_in_iso);	33	use C4::Dates qw(format_date_in_iso);
34	use base qw(Exporter);	34	use base qw(Exporter);
		35
35	our $VERSION = 3.07.00.049;	36	our $VERSION = 3.07.00.049;
36	our @EXPORT = qw<	37	our @EXPORT = qw(
37	ConnectSuggestionAndBiblio	38	ConnectSuggestionAndBiblio
38	CountSuggestion	39	CountSuggestion
39	DelSuggestion	40	DelSuggestion
40	GetSuggestion	41	GetSuggestion
41	GetSuggestionByStatus	42	GetSuggestionByStatus
42	GetSuggestionFromBiblionumber	43	GetSuggestionFromBiblionumber
43	GetSuggestionInfoFromBiblionumber	44	GetSuggestionInfoFromBiblionumber
44	GetSuggestionInfo	45	GetSuggestionInfo
45	ModStatus	46	ModStatus
46	ModSuggestion	47	ModSuggestion
47	NewSuggestion	48	NewSuggestion
48	SearchSuggestion	49	SearchSuggestion
49	DelSuggestionsOlderThan	50	DelSuggestionsOlderThan
50	>;	51	);
51		52
52	=head1 NAME	53	=head1 NAME
53		54
Lines 88-174 Note the status is stored twice : Link Here
88		89
89	=cut	90	=cut
90		91
91	sub SearchSuggestion {	92	sub SearchSuggestion {
92	my ($suggestion)=@_;	93	my ($suggestion) = @_;
93	my $dbh = C4::Context->dbh;	94	my $dbh = C4::Context->dbh;
94	my @sql_params;	95	my @sql_params;
95	my @query = (	96	my @query = (
96	q{ SELECT suggestions.*,	97	q{
97	U1.branchcode AS branchcodesuggestedby,	98	SELECT suggestions.*,
98	B1.branchname AS branchnamesuggestedby,	99	U1.branchcode AS branchcodesuggestedby,
99	U1.surname AS surnamesuggestedby,	100	B1.branchname AS branchnamesuggestedby,
100	U1.firstname AS firstnamesuggestedby,	101	U1.surname AS surnamesuggestedby,
101	U1.email AS emailsuggestedby,	102	U1.firstname AS firstnamesuggestedby,
102	U1.borrowernumber AS borrnumsuggestedby,	103	U1.email AS emailsuggestedby,
103	U1.categorycode AS categorycodesuggestedby,	104	U1.borrowernumber AS borrnumsuggestedby,
104	C1.description AS categorydescriptionsuggestedby,	105	U1.categorycode AS categorycodesuggestedby,
105	U2.surname AS surnamemanagedby,	106	C1.description AS categorydescriptionsuggestedby,
106	U2.firstname AS firstnamemanagedby,	107	U2.surname AS surnamemanagedby,
107	B2.branchname AS branchnamesuggestedby,	108	U2.firstname AS firstnamemanagedby,
108	U2.email AS emailmanagedby,	109	B2.branchname AS branchnamesuggestedby,
109	U2.branchcode AS branchcodemanagedby,	110	U2.email AS emailmanagedby,
110	U2.borrowernumber AS borrnummanagedby	111	U2.branchcode AS branchcodemanagedby,
111	FROM suggestions	112	U2.borrowernumber AS borrnummanagedby
112	LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber	113	FROM suggestions
113	LEFT JOIN branches AS B1 ON B1.branchcode=U1.branchcode	114	LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
114	LEFT JOIN categories AS C1 ON C1.categorycode = U1.categorycode	115	LEFT JOIN branches AS B1 ON B1.branchcode=U1.branchcode
115	LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber	116	LEFT JOIN categories AS C1 ON C1.categorycode=U1.categorycode
116	LEFT JOIN branches AS B2 ON B2.branchcode=U2.branchcode	117	LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
117	LEFT JOIN categories AS C2 ON C2.categorycode = U2.categorycode	118	LEFT JOIN branches AS B2 ON B2.branchcode=U2.branchcode
118	WHERE 1=1	119	LEFT JOIN categories AS C2 ON C2.categorycode=U2.categorycode
119	} , map {	120	WHERE 1=1
120	if ( my $s = $suggestion->{$_} ) {	121	}
121	push @sql_params,'%'.$s.'%';
122	" and suggestions.$_ like ? ";
123	} else { () }
124	} qw( title author isbn publishercode copyrightdate collectiontitle )
125	);	122	);
126		123
127	my $userenv = C4::Context->userenv;	124	# filter on biblio informations
128	if (C4::Context->preference('IndependantBranches')) {	125	foreach my $field (
129	if ($userenv) {	126	qw( title author isbn publishercode copyrightdate collectiontitle ))
130	if (($userenv->{flags} % 2) != 1 && !$suggestion->{branchcode}){	127	{
131	push @sql_params,$$userenv{branch};	128	if ( $suggestion->{$field} ) {
132	push @query,q{ and (suggestions.branchcode = ? or suggestions.branchcode ='')};	129	push @sql_params, '%' . $suggestion->{$field} . '%';
133	}	130	push @query, qq{ AND suggestions.$field LIKE ? };
		131	}
		132	}
		133
		134	# filter on user branch
		135	if ( C4::Context->preference('IndependantBranches') ) {
		136	my $userenv = C4::Context->userenv;
		137	if ($userenv) {
		138	if ( ( $userenv->{flags} % 2 ) != 1 && !$suggestion->{branchcode} )
		139	{
		140	push @sql_params, $$userenv{branch};
		141	push @query, q{
		142	AND (suggestions.branchcode=? OR suggestions.branchcode='')
		143	};
134	}	144	}
		145	}
135	}	146	}
136		147
137	foreach my $field (grep { my $fieldname=$_;	148	# filter on nillable fields
138	any {$fieldname eq $_ } qw<	149	foreach my $field (
139	STATUS branchcode itemtype suggestedby managedby acceptedby	150	qw( STATUS branchcode itemtype suggestedby managedby acceptedby budgetid biblionumber )
140	budgetid biblionumber	151	)
141	>} keys %$suggestion	152	{
142	) {	153	if ( exists $suggestion->{$field} ) {
143	if ($$suggestion{$field}){	154	if ( defined $suggestion->{$field} and $suggestion->{$field} ne '' )
144	push @sql_params,$suggestion->{$field};	155	{
145	push @query, " and suggestions.$field=?";	156	push @sql_params, $suggestion->{$field};
146	}	157	push @query, qq{ AND suggestions.$field=? };
147	else {	158	}
148	push @query, " and (suggestions.$field='' OR suggestions.$field IS NULL)";	159	else {
		160	push @query, qq{
		161	AND (suggestions.$field='' OR suggestions.$field IS NULL)
		162	};
		163	}
149	}	164	}
150	}	165	}
151		166
		167	# filter on date fields
152	my $today = C4::Dates->today('iso');	168	my $today = C4::Dates->today('iso');
153		169	foreach my $field (qw( suggesteddate manageddate accepteddate )) {
154	foreach ( qw( suggesteddate manageddate accepteddate ) ) {	170	my $from = $field . "_from";
155	my $from = $_ . "_from";	171	my $to = $field . "_to";
156	my $to = $_ . "_to";	172	if ( $suggestion->{$from} \|\| $suggestion->{$to} ) {
157	if ($$suggestion{$from} \|\| $$suggestion{$to}) {	173	push @query, qq{ AND suggestions.$field BETWEEN ? AND ? };
158	push @query, " AND suggestions.$_ BETWEEN '"	174	push @sql_params,
159	. (format_date_in_iso($$suggestion{$from}) \|\| 0000-00-00) . "' AND '" . (format_date_in_iso($$suggestion{$to}) \|\| $today) . "'";	175	format_date_in_iso( $suggestion->{$from} ) \|\| '0000-00-00';
160	}	176	push @sql_params,
		177	format_date_in_iso( $suggestion->{$to} ) \|\| $today;
		178	}
161	}	179	}
162		180
163	$debug && warn "@query";	181	$debug && warn "@query";
164	my $sth=$dbh->prepare("@query");	182	my $sth = $dbh->prepare("@query");
165	$sth->execute(@sql_params);	183	$sth->execute(@sql_params);
166	my @results;	184	my @results;
167	while ( my $data=$sth->fetchrow_hashref ){	185
168	$$data{$$data{STATUS}} = 1;	186	# add status as field
169	push(@results,$data);	187	while ( my $data = $sth->fetchrow_hashref ) {
		188	$data->{ $data->{STATUS} } = 1;
		189	push( @results, $data );
170	}	190	}
171	return (\@results);	191
		192	return ( \@results );
172	}	193	}
173		194
174	=head2 GetSuggestion	195	=head2 GetSuggestion
Lines 184-198 return : Link Here
184		205
185	sub GetSuggestion {	206	sub GetSuggestion {
186	my ($ordernumber) = @_;	207	my ($ordernumber) = @_;
187	my $dbh = C4::Context->dbh;	208	my $dbh = C4::Context->dbh;
188	my $query = "	209	my $query = q{
189	SELECT *	210	SELECT *
190	FROM suggestions	211	FROM suggestions
191	WHERE suggestionid=?	212	WHERE suggestionid=?
192	";	213	};
193	my $sth = $dbh->prepare($query);	214	my $sth = $dbh->prepare($query);
194	$sth->execute($ordernumber);	215	$sth->execute($ordernumber);
195	return($sth->fetchrow_hashref);	216	return ( $sth->fetchrow_hashref );
196	}	217	}
197		218
198	=head2 GetSuggestionFromBiblionumber	219	=head2 GetSuggestionFromBiblionumber
Lines 213-219 sub GetSuggestionFromBiblionumber { Link Here
213	FROM suggestions	234	FROM suggestions
214	WHERE biblionumber=? LIMIT 1	235	WHERE biblionumber=? LIMIT 1
215	};	236	};
216	my $dbh=C4::Context->dbh;	237	my $dbh = C4::Context->dbh;
217	my $sth = $dbh->prepare($query);	238	my $sth = $dbh->prepare($query);
218	$sth->execute($biblionumber);	239	$sth->execute($biblionumber);
219	my ($suggestionid) = $sth->fetchrow;	240	my ($suggestionid) = $sth->fetchrow;
Lines 231-244 all informations (suggestion and borrower) of the suggestion which is related to Link Here
231		252
232	sub GetSuggestionInfoFromBiblionumber {	253	sub GetSuggestionInfoFromBiblionumber {
233	my ($biblionumber) = @_;	254	my ($biblionumber) = @_;
234	my $query = qq{	255	my $query = q{
235	SELECT suggestions.*,	256	SELECT suggestions.*,
236	U1.surname AS surnamesuggestedby,	257	U1.surname AS surnamesuggestedby,
237	U1.firstname AS firstnamesuggestedby,	258	U1.firstname AS firstnamesuggestedby,
238	U1.borrowernumber AS borrnumsuggestedby	259	U1.borrowernumber AS borrnumsuggestedby
239	FROM suggestions	260	FROM suggestions
240	LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber	261	LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
241	WHERE biblionumber = ? LIMIT 1	262	WHERE biblionumber=?
		263	LIMIT 1
242	};	264	};
243	my $dbh = C4::Context->dbh;	265	my $dbh = C4::Context->dbh;
244	my $sth = $dbh->prepare($query);	266	my $sth = $dbh->prepare($query);
Lines 257-270 all informations (suggestion and borrower) of the suggestion which is related to Link Here
257		279
258	sub GetSuggestionInfo {	280	sub GetSuggestionInfo {
259	my ($suggestionid) = @_;	281	my ($suggestionid) = @_;
260	my $query = qq{	282	my $query = q{
261	SELECT suggestions.*,	283	SELECT suggestions.*,
262	U1.surname AS surnamesuggestedby,	284	U1.surname AS surnamesuggestedby,
263	U1.firstname AS firstnamesuggestedby,	285	U1.firstname AS firstnamesuggestedby,
264	U1.borrowernumber AS borrnumsuggestedby	286	U1.borrowernumber AS borrnumsuggestedby
265	FROM suggestions	287	FROM suggestions
266	LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber	288	LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
267	WHERE suggestionid = ? LIMIT 1	289	WHERE suggestionid=?
		290	LIMIT 1
268	};	291	};
269	my $dbh = C4::Context->dbh;	292	my $dbh = C4::Context->dbh;
270	my $sth = $dbh->prepare($query);	293	my $sth = $dbh->prepare($query);
Lines 284-329 all the suggestion with C<$status> Link Here
284	=cut	307	=cut
285		308
286	sub GetSuggestionByStatus {	309	sub GetSuggestionByStatus {
287	my $status = shift;	310	my $status = shift;
288	my $branchcode = shift;	311	my $branchcode = shift;
289	my $dbh = C4::Context->dbh;	312	my $dbh = C4::Context->dbh;
290	my @sql_params=($status);	313	my @sql_params = ($status);
291	my $query = qq(SELECT suggestions.*,	314	my $query = q{
292	U1.surname AS surnamesuggestedby,	315	SELECT suggestions.*,
293	U1.firstname AS firstnamesuggestedby,	316	U1.surname AS surnamesuggestedby,
294	U1.branchcode AS branchcodesuggestedby,	317	U1.firstname AS firstnamesuggestedby,
295	B1.branchname AS branchnamesuggestedby,	318	U1.branchcode AS branchcodesuggestedby,
296	U1.borrowernumber AS borrnumsuggestedby,	319	B1.branchname AS branchnamesuggestedby,
297	U1.categorycode AS categorycodesuggestedby,	320	U1.borrowernumber AS borrnumsuggestedby,
298	C1.description AS categorydescriptionsuggestedby,	321	U1.categorycode AS categorycodesuggestedby,
299	U2.surname AS surnamemanagedby,	322	C1.description AS categorydescriptionsuggestedby,
300	U2.firstname AS firstnamemanagedby,	323	U2.surname AS surnamemanagedby,
301	U2.borrowernumber AS borrnummanagedby	324	U2.firstname AS firstnamemanagedby,
302	FROM suggestions	325	U2.borrowernumber AS borrnummanagedby
303	LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber	326	FROM suggestions
304	LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber	327	LEFT JOIN borrowers AS U1 ON suggestedby=U1.borrowernumber
305	LEFT JOIN categories AS C1 ON C1.categorycode=U1.categorycode	328	LEFT JOIN borrowers AS U2 ON managedby=U2.borrowernumber
306	LEFT JOIN branches AS B1 on B1.branchcode = U1.branchcode	329	LEFT JOIN categories AS C1 ON C1.categorycode=U1.categorycode
307	WHERE status = ?);	330	LEFT JOIN branches AS B1 on B1.branchcode=U1.branchcode
308	if (C4::Context->preference("IndependantBranches") \|\| $branchcode) {	331	WHERE status = ?
		332	};
		333
		334	# filter on branch
		335	if ( C4::Context->preference("IndependantBranches") \|\| $branchcode ) {
309	my $userenv = C4::Context->userenv;	336	my $userenv = C4::Context->userenv;
310	if ($userenv) {	337	if ($userenv) {
311	unless ($userenv->{flags} % 2 == 1){	338	unless ( $userenv->{flags} % 2 == 1 ) {
312	push @sql_params,$userenv->{branch};	339	push @sql_params, $userenv->{branch};
313	$query .= " and (U1.branchcode = ? or U1.branchcode ='')";	340	$query .= q{ AND (U1.branchcode = ? OR U1.branchcode ='') };
314	}	341	}
315	}	342	}
316	if ($branchcode) {	343	if ($branchcode) {
317	push @sql_params,$branchcode;	344	push @sql_params, $branchcode;
318	$query .= " and (U1.branchcode = ? or U1.branchcode ='')";	345	$query .= q{ AND (U1.branchcode = ? OR U1.branchcode ='') };
319	}	346	}
320	}	347	}
321		348
322	my $sth = $dbh->prepare($query);	349	my $sth = $dbh->prepare($query);
323	$sth->execute(@sql_params);	350	$sth->execute(@sql_params);
324
325	my $results;	351	my $results;
326	$results= $sth->fetchall_arrayref({});	352	$results = $sth->fetchall_arrayref( {} );
327	return $results;	353	return $results;
328	}	354	}
329		355
Lines 355-388 sub CountSuggestion { Link Here
355	my ($status) = @_;	381	my ($status) = @_;
356	my $dbh = C4::Context->dbh;	382	my $dbh = C4::Context->dbh;
357	my $sth;	383	my $sth;
358	if (C4::Context->preference("IndependantBranches")){	384	my $userenv = C4::Context->userenv;
359	my $userenv = C4::Context->userenv;	385	if ( C4::Context->preference("IndependantBranches")
360	if ($userenv->{flags} % 2 == 1){	386	&& $userenv->{flags} % 2 != 1 )
361	my $query = qq \|	387	{
362	SELECT count(*)	388	my $query = q{
363	FROM suggestions	389	SELECT count(*)
364	WHERE STATUS=?	390	FROM suggestions
365	\|;	391	LEFT JOIN borrowers ON borrowers.borrowernumber=suggestions.suggestedby
366	$sth = $dbh->prepare($query);	392	WHERE STATUS=?
367	$sth->execute($status);	393	AND (borrowers.branchcode='' OR borrowers.branchcode=?)
368	}	394	};
369	else {	395	$sth = $dbh->prepare($query);
370	my $query = qq \|	396	$sth->execute( $status, $userenv->{branch} );
371	SELECT count(*)
372	FROM suggestions LEFT JOIN borrowers ON borrowers.borrowernumber=suggestions.suggestedby
373	WHERE STATUS=?
374	AND (borrowers.branchcode='' OR borrowers.branchcode =?)
375	\|;
376	$sth = $dbh->prepare($query);
377	$sth->execute($status,$userenv->{branch});
378	}
379	}	397	}
380	else {	398	else {
381	my $query = qq \|	399	my $query = q{
382	SELECT count(*)	400	SELECT count(*)
383	FROM suggestions	401	FROM suggestions
384	WHERE STATUS=?	402	WHERE STATUS=?
385	\|;	403	};
386	$sth = $dbh->prepare($query);	404	$sth = $dbh->prepare($query);
387	$sth->execute($status);	405	$sth->execute($status);
388	}	406	}
Lines 401-408 Insert a new suggestion on database with value given on input arg. Link Here
401		419
402	sub NewSuggestion {	420	sub NewSuggestion {
403	my ($suggestion) = @_;	421	my ($suggestion) = @_;
404	$suggestion->{STATUS}="ASKED" unless $suggestion->{STATUS};	422	$suggestion->{STATUS} = "ASKED" unless $suggestion->{STATUS};
405	return InsertInTable("suggestions",$suggestion);	423	return InsertInTable( "suggestions", $suggestion );
406	}	424	}
407		425
408	=head2 ModSuggestion	426	=head2 ModSuggestion
Lines 419-448 Note that there is no function to modify a suggestion. Link Here
419	=cut	437	=cut
420		438
421	sub ModSuggestion {	439	sub ModSuggestion {
422	my ($suggestion)=@_;	440	my ($suggestion) = @_;
423	my $status_update_table=UpdateInTable("suggestions", $suggestion);	441	my $status_update_table = UpdateInTable( "suggestions", $suggestion );
		442
		443	if ( $suggestion->{STATUS} ) {
424		444
425	if ($suggestion->{STATUS}) {
426	# fetch the entire updated suggestion so that we can populate the letter	445	# fetch the entire updated suggestion so that we can populate the letter
427	my $full_suggestion = GetSuggestion($suggestion->{suggestionid});	446	my $full_suggestion = GetSuggestion( $suggestion->{suggestionid} );
428	if ( my $letter = C4::Letters::GetPreparedLetter (	447	if (
429	module => 'suggestions',	448	my $letter = C4::Letters::GetPreparedLetter(
430	letter_code => $full_suggestion->{STATUS},	449	module => 'suggestions',
431	branchcode => $full_suggestion->{branchcode},	450	letter_code => $full_suggestion->{STATUS},
432	tables => {	451	branchcode => $full_suggestion->{branchcode},
433	'branches' => $full_suggestion->{branchcode},	452	tables => {
434	'borrowers' => $full_suggestion->{suggestedby},	453	'branches' => $full_suggestion->{branchcode},
435	'suggestions' => $full_suggestion,	454	'borrowers' => $full_suggestion->{suggestedby},
436	'biblio' => $full_suggestion->{biblionumber},	455	'suggestions' => $full_suggestion,
437	},	456	'biblio' => $full_suggestion->{biblionumber},
438	) ) {	457	},
439	C4::Letters::EnqueueLetter({	458	)
440	letter => $letter,	459	)
441	borrowernumber => $full_suggestion->{suggestedby},	460	{
442	suggestionid => $full_suggestion->{suggestionid},	461	C4::Letters::EnqueueLetter(
443	LibraryName => C4::Context->preference("LibraryName"),	462	{
444	message_transport_type => 'email',	463	letter => $letter,
445	}) or warn "can't enqueue letter $letter";	464	borrowernumber => $full_suggestion->{suggestedby},
		465	suggestionid => $full_suggestion->{suggestionid},
		466	LibraryName => C4::Context->preference("LibraryName"),
		467	message_transport_type => 'email',
		468	}
		469	) or warn "can't enqueue letter $letter";
446	}	470	}
447	}	471	}
448	return $status_update_table;	472	return $status_update_table;
Lines 457-471 connect a suggestion to an existing biblio Link Here
457	=cut	481	=cut
458		482
459	sub ConnectSuggestionAndBiblio {	483	sub ConnectSuggestionAndBiblio {
460	my ($suggestionid,$biblionumber) = @_;	484	my ( $suggestionid, $biblionumber ) = @_;
461	my $dbh=C4::Context->dbh;	485	my $dbh = C4::Context->dbh;
462	my $query = "	486	my $query = q{
463	UPDATE suggestions	487	UPDATE suggestions
464	SET biblionumber=?	488	SET biblionumber=?
465	WHERE suggestionid=?	489	WHERE suggestionid=?
466	";	490	};
467	my $sth = $dbh->prepare($query);	491	my $sth = $dbh->prepare($query);
468	$sth->execute($biblionumber,$suggestionid);	492	$sth->execute( $biblionumber, $suggestionid );
469	}	493	}
470		494
471	=head2 DelSuggestion	495	=head2 DelSuggestion
Lines 477-501 Delete a suggestion. A borrower can delete a suggestion only if he is its owner. Link Here
477	=cut	501	=cut
478		502
479	sub DelSuggestion {	503	sub DelSuggestion {
480	my ($borrowernumber,$suggestionid,$type) = @_;	504	my ( $borrowernumber, $suggestionid, $type ) = @_;
481	my $dbh = C4::Context->dbh;	505	my $dbh = C4::Context->dbh;
		506
482	# check that the suggestion comes from the suggestor	507	# check that the suggestion comes from the suggestor
483	my $query = "	508	my $query = q{
484	SELECT suggestedby	509	SELECT suggestedby
485	FROM suggestions	510	FROM suggestions
486	WHERE suggestionid=?	511	WHERE suggestionid=?
487	";	512	};
488	my $sth = $dbh->prepare($query);	513	my $sth = $dbh->prepare($query);
489	$sth->execute($suggestionid);	514	$sth->execute($suggestionid);
490	my ($suggestedby) = $sth->fetchrow;	515	my ($suggestedby) = $sth->fetchrow;
491	if ($type eq "intranet" \|\| $suggestedby eq $borrowernumber ) {	516	if ( $type eq 'intranet' \|\| $suggestedby eq $borrowernumber ) {
492	my $queryDelete = "	517	my $queryDelete = q{
493	DELETE FROM suggestions	518	DELETE FROM suggestions
494	WHERE suggestionid=?	519	WHERE suggestionid=?
495	";	520	};
496	$sth = $dbh->prepare($queryDelete);	521	$sth = $dbh->prepare($queryDelete);
497	my $suggestiondeleted=$sth->execute($suggestionid);	522	my $suggestiondeleted = $sth->execute($suggestionid);
498	return $suggestiondeleted;	523	return $suggestiondeleted;
499	}	524	}
500	}	525	}
501		526
Lines 505-518 sub DelSuggestion { Link Here
505	Delete all suggestions older than TODAY-$days , that have be accepted or rejected.	530	Delete all suggestions older than TODAY-$days , that have be accepted or rejected.
506		531
507	=cut	532	=cut
		533
508	sub DelSuggestionsOlderThan {	534	sub DelSuggestionsOlderThan {
509	my ($days) = @_;	535	my ($days) = @_;
510	return if not $days;	536	return unless $days;
511	my $dbh = C4::Context->dbh;	537	my $dbh = C4::Context->dbh;
512		538	my $sth = $dbh->prepare(
513	my $sth = $dbh->prepare("	539	q{
514	DELETE FROM suggestions WHERE STATUS <> 'ASKED' AND date < ADDDATE(NOW(), ?);	540	DELETE FROM suggestions
515	");	541	WHERE STATUS<>'ASKED'
		542	AND date < ADDDATE(NOW(), ?)
		543	}
		544	);
516	$sth->execute("-$days");	545	$sth->execute("-$days");
517	}	546	}
518		547