Attachment #123670 for bug #28813

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




    my $dbh       = C4::Context->dbh();
    my $statement = << 'ENDSQL';
INSERT INTO message_queue
( borrowernumber, subject, content, metadata, letter_code, message_transport_type, status, time_queued, to_address, from_address, reply_address, content_type, failure_code )
VALUES
( ?,              ?,       ?,       ?,        ?,           ?,                      ?,      CAST(NOW() AS DATETIME),       ?,          ?,            ?,           ?,              ? )
ENDSQL

        $params->{'from_address'},                # from_address
        $params->{'reply_address'},               # reply_address
        $params->{'letter'}->{'content-type'},    # content_type
        $params->{'failure_code'}        || '',   # failure_code
    );
    return $dbh->last_insert_id(undef,undef,'message_queue', undef);
}


    my $dbh = C4::Context->dbh();
    my $statement = << 'ENDSQL';
SELECT message_id, borrowernumber, subject, content, message_transport_type, status, time_queued, updated_on, failure_code
FROM message_queue
ENDSQL


    return unless $message_id;
    my $dbh = C4::Context->dbh;
    return $dbh->selectrow_hashref(q|
        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, failure_code
        FROM message_queue
        WHERE message_id = ?
    |, {}, $message_id );


    my $dbh = C4::Context->dbh();
    my $statement = qq{
        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.failure_code
        FROM message_queue mq
        LEFT JOIN borrowers b ON b.borrowernumber = mq.borrowernumber
        WHERE status = ?

    unless ($to_address) {
        unless ($patron) {
            warn "FAIL: No 'to_address' and INVALID borrowernumber ($message->{borrowernumber})";
            _set_message_status(
                {
                    message_id   => $message->{'message_id'},
                    status       => 'failed',
                    failure_code => 'INVALID_BORNUMBER'
                }
            );
            return;
        }
        $to_address = $patron->notice_email_address;
        unless ($to_address) {  
            # warn "FAIL: No 'to_address' and no email for " . ($member->{surname} ||'') . ", borrowernumber ($message->{borrowernumber})";
            # warning too verbose for this more common case?
            _set_message_status(
                {
                    message_id   => $message->{'message_id'},
                    status       => 'failed',
                    failure_code => 'NO_EMAIL'
                }
            );
            return;
        }
    }

      || $branch_email
      || C4::Context->preference('KohaAdminEmailAddress');
    if( !$from_address ) {
        _set_message_status(
            {
                message_id   => $message->{'message_id'},
                status       => 'failed',
                failure_code => 'NO_FROM',
            }
        );
        return;
    };
    my $email = Koha::Email->create(

            {
                message_id => $message->{'message_id'},
                status     => 'sent',
                failure_code => ''
            }
        );
        return 1;

            {
                message_id => $message->{'message_id'},
                status     => 'failed',
                failure_code => 'SENDMAIL'
            }
        );
        carp "$_";
        carp "$Mail::Sendmail::error";
        return;
    };
}

    unless ( $patron and $patron->smsalertnumber ) {
        _set_message_status( { message_id => $message->{'message_id'},
                               status     => 'failed',
                               failure_code => 'MISSING_SMS' } );
                               error_code => 'MISSING_SMS' } );
        return;
    }

    if ( _is_duplicate( $message ) ) {
        _set_message_status(
            {
                message_id   => $message->{'message_id'},
                status       => 'failed',
                failure_code => 'DUPLICATE_MESSAGE'
            }
        );
        return;
    }

    my $success = C4::SMS->send_sms(
        {
            destination => $patron->smsalertnumber,
            message     => $message->{'content'},
        }
    );

    if ($success) {
        _set_message_status(
            {
                message_id   => $message->{'message_id'},
                status       => 'sent',
                failure_code => ''
            }
        );
    }
    else {
        _set_message_status(
            {
                message_id   => $message->{'message_id'},
                status       => 'failed',
                failure_code => 'NO_NOTES'
            }
        );
    }

    return $success;
}

    }

    my $dbh = C4::Context->dbh();
    my $statement = 'UPDATE message_queue SET status= ?, failure_code= ? WHERE message_id = ?';
    my $sth = $dbh->prepare( $statement );
    my $result = $sth->execute( $params->{'status'},
                                $params->{'failure_code'} || '',
                                $params->{'message_id'} );
    return $result;
}

Lines 92-103 Link Here

(-)a/koha-tmpl/intranet-tmpl/prog/en/modules/members/notices.tt (-7 / +8 lines)
92	<td data-order="[% QUEUED_MESSAGE.updated_on \| html %]">[% QUEUED_MESSAGE.updated_on \| $KohaDates with_hours => 1 %]</td>	92	<td data-order="[% QUEUED_MESSAGE.updated_on \| html %]">[% QUEUED_MESSAGE.updated_on \| $KohaDates with_hours => 1 %]</td>
93	<td data-order="[% QUEUED_MESSAGE.time_queued \| html %]">[% QUEUED_MESSAGE.time_queued \| $KohaDates with_hours => 1 %]</td>	93	<td data-order="[% QUEUED_MESSAGE.time_queued \| html %]">[% QUEUED_MESSAGE.time_queued \| $KohaDates with_hours => 1 %]</td>
94	<td>	94	<td>
95	[% IF ( QUEUED_MESSAGE.error_code ) %]	95	[% IF ( QUEUED_MESSAGE.failure_code ) %]
96	[% IF ( QUEUED_MESSAGE.error_code == "INVALID_BORNUMBER" ) %]Invalid borrowernumber [% borrowernumber \| html %]	96	[% IF ( QUEUED_MESSAGE.failure_code == "INVALID_BORNUMBER" ) %]Invalid borrowernumber [% borrowernumber \| html %]
97	[% ELSIF ( QUEUED_MESSAGE.error_code == 'NO_EMAIL' ) %]Unable to find an email address for this borrower	97	[% ELSIF ( QUEUED_MESSAGE.failure_code == 'NO_EMAIL' ) %]Unable to find an email address for this borrower
98	[% ELSIF ( QUEUED_MESSAGE.error_code == 'MISSING_SMS' ) %]Missing SMS number	98	[% ELSIF ( QUEUED_MESSAGE.failure_code == 'NO_FROM' ) %]Missing from email address
99	[% ELSIF ( QUEUED_MESSAGE.error_code == 'DUPLICATE_MESSAGE' ) %]Message is duplicate	99	[% ELSIF ( QUEUED_MESSAGE.failure_code == 'MISSING_SMS' ) %]Missing SMS number
100	[% ELSIF ( QUEUED_MESSAGE.error_code == 'NO_NOTES' ) %]No notes from SMS driver	100	[% ELSIF ( QUEUED_MESSAGE.failure_code == 'DUPLICATE_MESSAGE' ) %]Message is duplicate
		101	[% ELSIF ( QUEUED_MESSAGE.failure_code == 'NO_NOTES' ) %]No notes from SMS driver
		102	[% ELSIF ( QUEUED_MESSAGE.failure_code == 'SENDMAIL' ) %]Unhandled email failure, check the logs for further details
101	[% ELSE %]Error occurred while sending email.	103	[% ELSE %]Error occurred while sending email.
102	[% END %]	104	[% END %]
103	[% END %]	105	[% END %]
104	-

Return to bug 28813

Lines 963-969 sub EnqueueLetter { Link Here

(-)a/C4/Letters.pm (-35 / +64 lines)
963	my $dbh = C4::Context->dbh();	963	my $dbh = C4::Context->dbh();
964	my $statement = << 'ENDSQL';	964	my $statement = << 'ENDSQL';
965	INSERT INTO message_queue	965	INSERT INTO message_queue
966	( borrowernumber, subject, content, metadata, letter_code, message_transport_type, status, time_queued, to_address, from_address, reply_address, content_type, delivery_note )	966	( borrowernumber, subject, content, metadata, letter_code, message_transport_type, status, time_queued, to_address, from_address, reply_address, content_type, failure_code )
967	VALUES	967	VALUES
968	( ?, ?, ?, ?, ?, ?, ?, CAST(NOW() AS DATETIME), ?, ?, ?, ?, ? )	968	( ?, ?, ?, ?, ?, ?, ?, CAST(NOW() AS DATETIME), ?, ?, ?, ?, ? )
969	ENDSQL	969	ENDSQL
Lines 981-987 ENDSQL Link Here
981	$params->{'from_address'}, # from_address	981	$params->{'from_address'}, # from_address
982	$params->{'reply_address'}, # reply_address	982	$params->{'reply_address'}, # reply_address
983	$params->{'letter'}->{'content-type'}, # content_type	983	$params->{'letter'}->{'content-type'}, # content_type
984	$params->{'delivery_note'} \|\| '', # delivery_note	984	$params->{'failure_code'} \|\| '', # failure_code
985	);	985	);
986	return $dbh->last_insert_id(undef,undef,'message_queue', undef);	986	return $dbh->last_insert_id(undef,undef,'message_queue', undef);
987	}	987	}
Lines 1124-1130 sub GetQueuedMessages { Link Here
1124		1124
1125	my $dbh = C4::Context->dbh();	1125	my $dbh = C4::Context->dbh();
1126	my $statement = << 'ENDSQL';	1126	my $statement = << 'ENDSQL';
1127	SELECT message_id, borrowernumber, subject, content, message_transport_type, status, time_queued, updated_on, delivery_note	1127	SELECT message_id, borrowernumber, subject, content, message_transport_type, status, time_queued, updated_on, failure_code
1128	FROM message_queue	1128	FROM message_queue
1129	ENDSQL	1129	ENDSQL
1130		1130
Lines 1178-1184 sub GetMessage { Link Here
1178	return unless $message_id;	1178	return unless $message_id;
1179	my $dbh = C4::Context->dbh;	1179	my $dbh = C4::Context->dbh;
1180	return $dbh->selectrow_hashref(q\|	1180	return $dbh->selectrow_hashref(q\|
1181	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	1181	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, failure_code
1182	FROM message_queue	1182	FROM message_queue
1183	WHERE message_id = ?	1183	WHERE message_id = ?
1184	\|, {}, $message_id );	1184	\|, {}, $message_id );
Lines 1283-1289 sub _get_unsent_messages { Link Here
1283		1283
1284	my $dbh = C4::Context->dbh();	1284	my $dbh = C4::Context->dbh();
1285	my $statement = qq{	1285	my $statement = qq{
1286	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	1286	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.failure_code
1287	FROM message_queue mq	1287	FROM message_queue mq
1288	LEFT JOIN borrowers b ON b.borrowernumber = mq.borrowernumber	1288	LEFT JOIN borrowers b ON b.borrowernumber = mq.borrowernumber
1289	WHERE status = ?	1289	WHERE status = ?
Lines 1331-1350 sub _send_message_by_email { Link Here
1331	unless ($to_address) {	1331	unless ($to_address) {
1332	unless ($patron) {	1332	unless ($patron) {
1333	warn "FAIL: No 'to_address' and INVALID borrowernumber ($message->{borrowernumber})";	1333	warn "FAIL: No 'to_address' and INVALID borrowernumber ($message->{borrowernumber})";
1334	_set_message_status( { message_id => $message->{'message_id'},	1334	_set_message_status(
1335	status => 'failed',	1335	{
1336	delivery_note => 'Invalid borrowernumber '.$message->{borrowernumber},	1336	message_id => $message->{'message_id'},
1337	error_code => 'INVALID_BORNUMBER' } );	1337	status => 'failed',
		1338	failure_code => 'INVALID_BORNUMBER'
		1339	}
		1340	);
1338	return;	1341	return;
1339	}	1342	}
1340	$to_address = $patron->notice_email_address;	1343	$to_address = $patron->notice_email_address;
1341	unless ($to_address) {	1344	unless ($to_address) {
1342	# warn "FAIL: No 'to_address' and no email for " . ($member->{surname} \|\|'') . ", borrowernumber ($message->{borrowernumber})";	1345	# warn "FAIL: No 'to_address' and no email for " . ($member->{surname} \|\|'') . ", borrowernumber ($message->{borrowernumber})";
1343	# warning too verbose for this more common case?	1346	# warning too verbose for this more common case?
1344	_set_message_status( { message_id => $message->{'message_id'},	1347	_set_message_status(
1345	status => 'failed',	1348	{
1346	delivery_note => 'Unable to find an email address for this borrower',	1349	message_id => $message->{'message_id'},
1347	error_code => 'NO_EMAIL' } );	1350	status => 'failed',
		1351	failure_code => 'NO_EMAIL'
		1352	}
		1353	);
1348	return;	1354	return;
1349	}	1355	}
1350	}	1356	}
Lines 1374-1384 sub _send_message_by_email { Link Here
1374	\|\| $branch_email	1380	\|\| $branch_email
1375	\|\| C4::Context->preference('KohaAdminEmailAddress');	1381	\|\| C4::Context->preference('KohaAdminEmailAddress');
1376	if( !$from_address ) {	1382	if( !$from_address ) {
1377	_set_message_status({	1383	_set_message_status(
1378	message_id => $message->{'message_id'},	1384	{
1379	status => 'failed',	1385	message_id => $message->{'message_id'},
1380	delivery_note => 'No from address',	1386	status => 'failed',
1381	});	1387	failure_code => 'NO_FROM',
		1388	}
		1389	);
1382	return;	1390	return;
1383	};	1391	};
1384	my $email = Koha::Email->create(	1392	my $email = Koha::Email->create(
Lines 1435-1441 sub _send_message_by_email { Link Here
1435	{	1443	{
1436	message_id => $message->{'message_id'},	1444	message_id => $message->{'message_id'},
1437	status => 'sent',	1445	status => 'sent',
1438	delivery_note => ''	1446	failure_code => ''
1439	}	1447	}
1440	);	1448	);
1441	return 1;	1449	return 1;
Lines 1445-1454 sub _send_message_by_email { Link Here
1445	{	1453	{
1446	message_id => $message->{'message_id'},	1454	message_id => $message->{'message_id'},
1447	status => 'failed',	1455	status => 'failed',
1448	delivery_note => $Mail::Sendmail::error	1456	failure_code => 'SENDMAIL'
1449	}	1457	}
1450	);	1458	);
1451	carp "$_";	1459	carp "$_";
		1460	carp "$Mail::Sendmail::error";
1452	return;	1461	return;
1453	};	1462	};
1454	}	1463	}
Lines 1497-1522 sub _send_message_by_sms { Link Here
1497	unless ( $patron and $patron->smsalertnumber ) {	1506	unless ( $patron and $patron->smsalertnumber ) {
1498	_set_message_status( { message_id => $message->{'message_id'},	1507	_set_message_status( { message_id => $message->{'message_id'},
1499	status => 'failed',	1508	status => 'failed',
1500	delivery_note => 'Missing SMS number',	1509	failure_code => 'MISSING_SMS' } );
1501	error_code => 'MISSING_SMS' } );
1502	return;	1510	return;
1503	}	1511	}
1504		1512
1505	if ( _is_duplicate( $message ) ) {	1513	if ( _is_duplicate( $message ) ) {
1506	_set_message_status( { message_id => $message->{'message_id'},	1514	_set_message_status(
1507	status => 'failed',	1515	{
1508	delivery_note => 'Message is duplicate',	1516	message_id => $message->{'message_id'},
1509	error_code => 'DUPLICATE_MESSAGE' } );	1517	status => 'failed',
		1518	failure_code => 'DUPLICATE_MESSAGE'
		1519	}
		1520	);
1510	return;	1521	return;
1511	}	1522	}
1512		1523
1513	my $success = C4::SMS->send_sms( { destination => $patron->smsalertnumber,	1524	my $success = C4::SMS->send_sms(
1514	message => $message->{'content'},	1525	{
1515	} );	1526	destination => $patron->smsalertnumber,
1516	_set_message_status( { message_id => $message->{'message_id'},	1527	message => $message->{'content'},
1517	status => ($success ? 'sent' : 'failed'),	1528	}
1518	delivery_note => ($success ? '' : 'No notes from SMS driver'),	1529	);
1519	error_code => 'NO_NOTES' } );	1530
		1531	if ($success) {
		1532	_set_message_status(
		1533	{
		1534	message_id => $message->{'message_id'},
		1535	status => 'sent',
		1536	failure_code => ''
		1537	}
		1538	);
		1539	}
		1540	else {
		1541	_set_message_status(
		1542	{
		1543	message_id => $message->{'message_id'},
		1544	status => 'failed',
		1545	failure_code => 'NO_NOTES'
		1546	}
		1547	);
		1548	}
1520		1549
1521	return $success;	1550	return $success;
1522	}	1551	}
Lines 1541-1550 sub _set_message_status { Link Here
1541	}	1570	}
1542		1571
1543	my $dbh = C4::Context->dbh();	1572	my $dbh = C4::Context->dbh();
1544	my $statement = 'UPDATE message_queue SET status= ?, delivery_note= ? WHERE message_id = ?';	1573	my $statement = 'UPDATE message_queue SET status= ?, failure_code= ? WHERE message_id = ?';
1545	my $sth = $dbh->prepare( $statement );	1574	my $sth = $dbh->prepare( $statement );
1546	my $result = $sth->execute( $params->{'status'},	1575	my $result = $sth->execute( $params->{'status'},
1547	$params->{'delivery_note'} \|\| '',	1576	$params->{'failure_code'} \|\| '',
1548	$params->{'message_id'} );	1577	$params->{'message_id'} );
1549	return $result;	1578	return $result;
1550	}	1579	}