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

(-)a/C4/Suggestions.pm (-190 / +219 lines)
Lines 18-25 package C4::Suggestions; Link Here
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
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/suggestion/suggestion.tt (-7 / +6 lines)
Lines 514-523 $(document).ready(function() { calcNewsuggTotal(); }); Link Here
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
- 

Return to bug 8443