[Koha-devel] Trouble with email in 3.4

[Magnus Enger]

Hi all!

I'm having some trouble with email in both current master and 3.4.1.

* The setup

- EnhancedMessagingPreferences is set to "Allow"
- No circulation alerts are disabled under "Item Circulation Alerts"
- Turning things off and on in the "Default messaging preferences for
this patron category" does not affect the outcome
- Emails for the patron I'm testing is enabled for check in/check out
(and shows as enabled both in the OPAC and staff client)

* The symptom

When check ins and check outs are done for the patron I'm testing
with, no emails show up, and no messages are ever put in the
message_queue table in the database. The oldest circ message in there
is dated 2011-03-18, but I'm not sure how much it has been used after
that. I can see some HOLD messages from 2011-04-06 and one REJECTED
purchase suggestion from today, so some things are working.

The problem was reported after the upgrade from 3.2.6 to 3.4.1 this
last weekend. I can reproduce it on a live system running 3.4.1 (+ the
most recent batch of patches on the 3.4.x branch) and on a test system
running current master.

* The investigation

I have tried diggin around in the code, and found that in
SendCirculationAlert() in C4/Circulation.pm, $borrower_preferences is
empty:

sub SendCirculationAlert {
    my ($opts) = @_;
    my ($type, $item, $borrower, $branch) =
        ($opts->{type}, $opts->{item}, $opts->{borrower}, $opts->{branch});
    my %message_name = (
        CHECKIN  => 'Item Check-in',
        CHECKOUT => 'Item Checkout',
    );
    my $borrower_preferences = C4::Members::Messaging::GetMessagingPreferences({
        borrowernumber => $borrower->{borrowernumber},
        message_name   => $message_name{$type},
    });
...

This is in turn caused by the SQL GetMessagingPreferences() in
C4/Members/Messaging.pm:

SELECT borrower_message_preferences.*,
       borrower_message_transport_preferences.message_transport_type,
       message_attributes.message_name,
       message_attributes.takes_days,
       message_transports.is_digest,
       message_transports.letter_module,
       message_transports.letter_code
FROM   borrower_message_preferences
LEFT JOIN borrower_message_transport_preferences
ON     borrower_message_transport_preferences.borrower_message_preference_id
= borrower_message_preferences.borrower_message_preference_id
LEFT JOIN message_attributes
ON     message_attributes.message_attribute_id =
borrower_message_preferences.message_attribute_id
JOIN message_transports
ON     message_transports.message_attribute_id =
message_attributes.message_attribute_id
AND    message_transports.message_transport_type =
borrower_message_transport_preferences.message_transport_type
WHERE  message_attributes.message_name = 'Item Check-in'
AND borrower_message_preferences.borrowernumber = 51;

not retuning anything:

Empty set (0.00 sec)

Now I must confess that all those JOINs are making my head spin...
Here are the actual contents of the tables involved in that query:

mysql> select * from borrower_message_preferences where borrowernumber = 51;
+--------------------------------+----------------+--------------+----------------------+-----------------+--------------+
| borrower_message_preference_id | borrowernumber | categorycode |
message_attribute_id | days_in_advance | wants_digest |
+--------------------------------+----------------+--------------+----------------------+-----------------+--------------+
|                             56 |             51 | NULL         |
               2 |               0 |            0 |
|                             57 |             51 | NULL         |
               6 |            NULL |            0 |
|                             58 |             51 | NULL         |
               4 |            NULL |            0 |
|                             59 |             51 | NULL         |
               1 |            NULL |            0 |
|                             60 |             51 | NULL         |
               5 |            NULL |            0 |
+--------------------------------+----------------+--------------+----------------------+-----------------+--------------+

mysql> select * from message_attributes;
+----------------------+----------------+------------+
| message_attribute_id | message_name   | takes_days |
+----------------------+----------------+------------+
|                    1 | Item_Due       |          0 |
|                    2 | Advance_Notice |          1 |
|                    4 | Hold_Filled    |          0 |
|                    5 | Item_Check_in  |          0 |
|                    6 | Item_Checkout  |          0 |
+----------------------+----------------+------------+

mysql> select * from message_transports;
+----------------------+------------------------+-----------+---------------+-------------+
| message_attribute_id | message_transport_type | is_digest |
letter_module | letter_code |
+----------------------+------------------------+-----------+---------------+-------------+
|                    5 | email                  |         0 |
circulation   | CHECKIN     |
|                    5 | sms                    |         0 |
circulation   | CHECKIN     |
|                    6 | email                  |         0 |
circulation   | CHECKOUT    |
|                    6 | sms                    |         0 |
circulation   | CHECKOUT    |
|                    1 | email                  |         0 |
circulation   | DUE         |
|                    1 | sms                    |         0 |
circulation   | DUE         |
|                    1 | email                  |         1 |
circulation   | DUEDGST     |
|                    1 | sms                    |         1 |
circulation   | DUEDGST     |
|                    2 | email                  |         0 |
circulation   | PREDUE      |
|                    2 | sms                    |         0 |
circulation   | PREDUE      |
|                    2 | email                  |         1 |
circulation   | PREDUEDGST  |
|                    2 | sms                    |         1 |
circulation   | PREDUEDGST  |
|                    4 | email                  |         0 | reserves
     | HOLD        |
|                    4 | sms                    |         0 | reserves
     | HOLD        |
+----------------------+------------------------+-----------+---------------+-------------+

* The questions

- Is anyone else seeing the same behaviour?
- Can anyone spot what's going on? I'd be happy to create a bug for
this, I just thought I'd try and figure out if it *is* a bug, or if I
have done something silly (again), first...


Best regards,
Magnus Enger
libriotech.no