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

(-)a/C4/Letters.pm (-13 / +23 lines)
Lines 913-921 sub EnqueueLetter { Link Here
913
    my $dbh       = C4::Context->dbh();
913
    my $dbh       = C4::Context->dbh();
914
    my $statement = << 'ENDSQL';
914
    my $statement = << 'ENDSQL';
915
INSERT INTO message_queue
915
INSERT INTO message_queue
916
( borrowernumber, subject, content, metadata, letter_code, message_transport_type, status, time_queued, to_address, from_address, reply_address, content_type )
916
( borrowernumber, subject, content, metadata, letter_code, message_transport_type, status, time_queued, to_address, from_address, reply_address, content_type, delivery_note )
917
VALUES
917
VALUES
918
( ?,              ?,       ?,       ?,        ?,           ?,                      ?,      NOW(),       ?,          ?,            ?,           ? )
918
( ?,              ?,       ?,       ?,        ?,           ?,                      ?,      NOW(),       ?,          ?,            ?,           ?,              ? )
919
ENDSQL
919
ENDSQL
920
920
921
    my $sth    = $dbh->prepare($statement);
921
    my $sth    = $dbh->prepare($statement);
Lines 931-936 ENDSQL Link Here
931
        $params->{'from_address'},                # from_address
931
        $params->{'from_address'},                # from_address
932
        $params->{'reply_address'},               # reply_address
932
        $params->{'reply_address'},               # reply_address
933
        $params->{'letter'}->{'content-type'},    # content_type
933
        $params->{'letter'}->{'content-type'},    # content_type
934
        $params->{'delivery_note'}        || '',  # delivery_note
934
    );
935
    );
935
    return $dbh->last_insert_id(undef,undef,'message_queue', undef);
936
    return $dbh->last_insert_id(undef,undef,'message_queue', undef);
936
}
937
}
Lines 1072-1078 sub GetQueuedMessages { Link Here
1072
1073
1073
    my $dbh = C4::Context->dbh();
1074
    my $dbh = C4::Context->dbh();
1074
    my $statement = << 'ENDSQL';
1075
    my $statement = << 'ENDSQL';
1075
SELECT message_id, borrowernumber, subject, content, message_transport_type, status, time_queued, updated_on
1076
SELECT message_id, borrowernumber, subject, content, message_transport_type, status, time_queued, updated_on, delivery_note
1076
FROM message_queue
1077
FROM message_queue
1077
ENDSQL
1078
ENDSQL
1078
1079
Lines 1126-1132 sub GetMessage { Link Here
1126
    return unless $message_id;
1127
    return unless $message_id;
1127
    my $dbh = C4::Context->dbh;
1128
    my $dbh = C4::Context->dbh;
1128
    return $dbh->selectrow_hashref(q|
1129
    return $dbh->selectrow_hashref(q|
1129
        SELECT message_id, borrowernumber, subject, content, metadata, letter_code, message_transport_type, status, time_queued, updated_on, to_address, from_address, reply_address, content_type
1130
        SELECT message_id, borrowernumber, subject, content, metadata, letter_code, message_transport_type, status, time_queued, updated_on, to_address, from_address, reply_address, content_type, delivery_note
1130
        FROM message_queue
1131
        FROM message_queue
1131
        WHERE message_id = ?
1132
        WHERE message_id = ?
1132
    |, {}, $message_id );
1133
    |, {}, $message_id );
Lines 1230-1236 sub _get_unsent_messages { Link Here
1230
1231
1231
    my $dbh = C4::Context->dbh();
1232
    my $dbh = C4::Context->dbh();
1232
    my $statement = qq{
1233
    my $statement = qq{
1233
        SELECT mq.message_id, mq.borrowernumber, mq.subject, mq.content, mq.message_transport_type, mq.status, mq.time_queued, mq.from_address, mq.reply_address, mq.to_address, mq.content_type, b.branchcode, mq.letter_code
1234
        SELECT mq.message_id, mq.borrowernumber, mq.subject, mq.content, mq.message_transport_type, mq.status, mq.time_queued, mq.from_address, mq.reply_address, mq.to_address, mq.content_type, b.branchcode, mq.letter_code, mq.delivery_note
1234
        FROM message_queue mq
1235
        FROM message_queue mq
1235
        LEFT JOIN borrowers b ON b.borrowernumber = mq.borrowernumber
1236
        LEFT JOIN borrowers b ON b.borrowernumber = mq.borrowernumber
1236
        WHERE status = ?
1237
        WHERE status = ?
Lines 1277-1283 sub _send_message_by_email { Link Here
1277
        unless ($patron) {
1278
        unless ($patron) {
1278
            warn "FAIL: No 'to_address' and INVALID borrowernumber ($message->{borrowernumber})";
1279
            warn "FAIL: No 'to_address' and INVALID borrowernumber ($message->{borrowernumber})";
1279
            _set_message_status( { message_id => $message->{'message_id'},
1280
            _set_message_status( { message_id => $message->{'message_id'},
1280
                                   status     => 'failed' } );
1281
                                   status     => 'failed',
1282
                                   delivery_note => 'Invalid borrowernumber '.$message->{borrowernumber} } );
1281
            return;
1283
            return;
1282
        }
1284
        }
1283
        $to_address = $patron->notice_email_address;
1285
        $to_address = $patron->notice_email_address;
Lines 1285-1291 sub _send_message_by_email { Link Here
1285
            # warn "FAIL: No 'to_address' and no email for " . ($member->{surname} ||'') . ", borrowernumber ($message->{borrowernumber})";
1287
            # warn "FAIL: No 'to_address' and no email for " . ($member->{surname} ||'') . ", borrowernumber ($message->{borrowernumber})";
1286
            # warning too verbose for this more common case?
1288
            # warning too verbose for this more common case?
1287
            _set_message_status( { message_id => $message->{'message_id'},
1289
            _set_message_status( { message_id => $message->{'message_id'},
1288
                                   status     => 'failed' } );
1290
                                   status     => 'failed',
1291
                                   delivery_note => 'Unable to find an email address for this borrower' } );
1289
            return;
1292
            return;
1290
        }
1293
        }
1291
    }
1294
    }
Lines 1330-1340 sub _send_message_by_email { Link Here
1330
1333
1331
    if ( Mail::Sendmail::sendmail( %sendmail_params ) ) {
1334
    if ( Mail::Sendmail::sendmail( %sendmail_params ) ) {
1332
        _set_message_status( { message_id => $message->{'message_id'},
1335
        _set_message_status( { message_id => $message->{'message_id'},
1333
                status     => 'sent' } );
1336
                status     => 'sent',
1337
                delivery_note => '' } );
1334
        return 1;
1338
        return 1;
1335
    } else {
1339
    } else {
1336
        _set_message_status( { message_id => $message->{'message_id'},
1340
        _set_message_status( { message_id => $message->{'message_id'},
1337
                status     => 'failed' } );
1341
                status     => 'failed',
1342
                delivery_note => $Mail::Sendmail::error } );
1338
        carp $Mail::Sendmail::error;
1343
        carp $Mail::Sendmail::error;
1339
        return;
1344
        return;
1340
    }
1345
    }
Lines 1383-1395 sub _send_message_by_sms { Link Here
1383
1388
1384
    unless ( $patron and $patron->smsalertnumber ) {
1389
    unless ( $patron and $patron->smsalertnumber ) {
1385
        _set_message_status( { message_id => $message->{'message_id'},
1390
        _set_message_status( { message_id => $message->{'message_id'},
1386
                               status     => 'failed' } );
1391
                               status     => 'failed',
1392
                               delivery_note => 'Missing SMS number' } );
1387
        return;
1393
        return;
1388
    }
1394
    }
1389
1395
1390
    if ( _is_duplicate( $message ) ) {
1396
    if ( _is_duplicate( $message ) ) {
1391
        _set_message_status( { message_id => $message->{'message_id'},
1397
        _set_message_status( { message_id => $message->{'message_id'},
1392
                               status     => 'failed' } );
1398
                               status     => 'failed',
1399
                               delivery_note => 'Message is duplicate' } );
1393
        return;
1400
        return;
1394
    }
1401
    }
1395
1402
Lines 1397-1403 sub _send_message_by_sms { Link Here
1397
                                       message     => $message->{'content'},
1404
                                       message     => $message->{'content'},
1398
                                     } );
1405
                                     } );
1399
    _set_message_status( { message_id => $message->{'message_id'},
1406
    _set_message_status( { message_id => $message->{'message_id'},
1400
                           status     => ($success ? 'sent' : 'failed') } );
1407
                           status     => ($success ? 'sent' : 'failed'),
1408
                           delivery_note => ($success ? '' : 'No notes from SMS driver') } );
1409
1401
    return $success;
1410
    return $success;
1402
}
1411
}
1403
1412
Lines 1421-1429 sub _set_message_status { Link Here
1421
    }
1430
    }
1422
1431
1423
    my $dbh = C4::Context->dbh();
1432
    my $dbh = C4::Context->dbh();
1424
    my $statement = 'UPDATE message_queue SET status= ? WHERE message_id = ?';
1433
    my $statement = 'UPDATE message_queue SET status= ?, delivery_note= ? WHERE message_id = ?';
1425
    my $sth = $dbh->prepare( $statement );
1434
    my $sth = $dbh->prepare( $statement );
1426
    my $result = $sth->execute( $params->{'status'},
1435
    my $result = $sth->execute( $params->{'status'},
1436
                                $params->{'delivery_note'} || '',
1427
                                $params->{'message_id'} );
1437
                                $params->{'message_id'} );
1428
    return $result;
1438
    return $result;
1429
}
1439
}
(-)a/installer/data/mysql/atomicupdate/Bug-14723_-_Additional_delivery_notes_to_messages.perl (+8 lines)
Line 0 Link Here
1
$DBversion = 'XXX';  # will be replaced by the RM
2
if( CheckVersion( $DBversion ) ) {
3
    $dbh->do("ALTER TABLE message_queue ADD delivery_note TEXT");
4
5
    # Always end with this (adjust the bug info)
6
    SetVersion( $DBversion );
7
    print "Upgrade to $DBversion done (Bug 14723 - Additional delivery notes to messages)\n";
8
}
(-)a/installer/data/mysql/kohastructure.sql (+1 lines)
Lines 2405-2410 CREATE TABLE `message_queue` ( Link Here
2405
  `from_address` LONGTEXT,
2405
  `from_address` LONGTEXT,
2406
  `reply_address` LONGTEXT,
2406
  `reply_address` LONGTEXT,
2407
  `content_type` MEDIUMTEXT,
2407
  `content_type` MEDIUMTEXT,
2408
  `delivery_note` MEDIUMTEXT,
2408
  PRIMARY KEY `message_id` (`message_id`),
2409
  PRIMARY KEY `message_id` (`message_id`),
2409
  KEY `borrowernumber` (`borrowernumber`),
2410
  KEY `borrowernumber` (`borrowernumber`),
2410
  KEY `message_transport_type` (`message_transport_type`),
2411
  KEY `message_transport_type` (`message_transport_type`),
(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/members/notices.tt (-1 / +3 lines)
Lines 37-42 Link Here
37
		<th>Status</th>
37
		<th>Status</th>
38
        <th>Updated on</th>
38
        <th>Updated on</th>
39
        <th>Time created</th>
39
        <th>Time created</th>
40
        <th>Delivery note</th>
40
	    </tr>
41
	    </tr>
41
	</thead>
42
	</thead>
42
	<tbody>
43
	<tbody>
Lines 76-81 Link Here
76
        </td>
77
        </td>
77
        <td><span title="[% QUEUED_MESSAGE.time_status_changed | html %]">[% QUEUED_MESSAGE.updated_on | $KohaDates  with_hours => 1 %]</span></td>
78
        <td><span title="[% QUEUED_MESSAGE.time_status_changed | html %]">[% QUEUED_MESSAGE.updated_on | $KohaDates  with_hours => 1 %]</span></td>
78
        <td><span title="[% QUEUED_MESSAGE.time_queued | html %]">[% QUEUED_MESSAGE.time_queued | $KohaDates  with_hours => 1 %]</span></td>
79
        <td><span title="[% QUEUED_MESSAGE.time_queued | html %]">[% QUEUED_MESSAGE.time_queued | $KohaDates  with_hours => 1 %]</span></td>
80
        <td>[% QUEUED_MESSAGE.delivery_note | html %]</td>
79
	    </tr>
81
	    </tr>
80
	    [% END %]
82
	    [% END %]
81
	</tbody>
83
	</tbody>
Lines 100-106 Link Here
100
        $(document).ready(function() {
102
        $(document).ready(function() {
101
            $("#noticestable").dataTable($.extend(true, {}, dataTablesDefaults, {
103
            $("#noticestable").dataTable($.extend(true, {}, dataTablesDefaults, {
102
                "aaSorting": [[ 3, "desc" ]],
104
                "aaSorting": [[ 3, "desc" ]],
103
                "aoColumns": [ null,null,null, { "sType": "title-string" } ,{ "sType": "title-string" } ],
105
                "aoColumns": [ null,null,null, { "sType": "title-string" } ,{ "sType": "title-string" }, null ],
104
                "sPaginationType": "full"
106
                "sPaginationType": "full"
105
            }));
107
            }));
106
108
(-)a/t/db_dependent/Letters.t (-2 / +7 lines)
Lines 18-24 Link Here
18
# along with Koha; if not, see <http://www.gnu.org/licenses>.
18
# along with Koha; if not, see <http://www.gnu.org/licenses>.
19
19
20
use Modern::Perl;
20
use Modern::Perl;
21
use Test::More tests => 73;
21
use Test::More tests => 75;
22
use Test::MockModule;
22
use Test::MockModule;
23
use Test::Warn;
23
use Test::Warn;
24
24
Lines 133-138 is( $messages->[0]->{message_transport_type}, $my_message->{message_transport_ty Link Here
133
is( $messages->[0]->{status}, 'pending', 'EnqueueLetter stores the status pending correctly' );
133
is( $messages->[0]->{status}, 'pending', 'EnqueueLetter stores the status pending correctly' );
134
isnt( $messages->[0]->{time_queued}, undef, 'Time queued inserted by default in message_queue table' );
134
isnt( $messages->[0]->{time_queued}, undef, 'Time queued inserted by default in message_queue table' );
135
is( $messages->[0]->{updated_on}, $messages->[0]->{time_queued}, 'Time status changed equals time queued when created in message_queue table' );
135
is( $messages->[0]->{updated_on}, $messages->[0]->{time_queued}, 'Time status changed equals time queued when created in message_queue table' );
136
is( $messages->[0]->{delivery_note}, '', 'Delivery note for successful message correctly empty');
136
137
137
# Setting time_queued to something else than now
138
# Setting time_queued to something else than now
138
my $yesterday = dt_from_string->subtract( days => 1 );
139
my $yesterday = dt_from_string->subtract( days => 1 );
Lines 162-167 is( $resent, 0, 'The message should not have been resent again' ); Link Here
162
$resent = C4::Letters::ResendMessage();
163
$resent = C4::Letters::ResendMessage();
163
is( $resent, undef, 'ResendMessage should return undef if not message_id given' );
164
is( $resent, undef, 'ResendMessage should return undef if not message_id given' );
164
165
166
# Delivery notes
167
is($messages->[0]->{delivery_note}, 'Missing SMS number',
168
   'Delivery note for no smsalertnumber correctly set');
169
170
165
# GetLetters
171
# GetLetters
166
my $letters = C4::Letters::GetLetters();
172
my $letters = C4::Letters::GetLetters();
167
is( @$letters, 0, 'GetLetters returns the correct number of letters' );
173
is( @$letters, 0, 'GetLetters returns the correct number of letters' );
168
- 

Return to bug 14723