Messaging System - How to show message Unread and change to Read?

[hominid4]

Hi. I have a ?messaging? table to allow clients to post messages to their relationship employees. Each client has assigned employees, and an employee can be assigned to multiple clients. When a client posts a message, only their assigned employees can see the message; so the one message will be viewed by multiple employees.

I?m currently using the three tables below to accomplish this. The client uses the ?portal_messages? form to post the message, and I have an onAfterProcess form PHP plugin that pulls all the ?employee_user_id? IDs from the ?portal_user_relationship? table that are related to the ?client_user_id? and inserts a row per ?employee_user_id? into the ?portal_messages_rel? table.

The employee views the ?portal_messages? list, and if they are related to the client they see the message.

====================
portal_messages
====================
message_id (AI) | client_user_id | subject | message | read
??????????
1 | 492 | Test Subject | Test message.

* ?subject?: Show in List, links to details.
* ?message?: Not shown in List.

====================
portal_user_relationship
====================
id (AI) | employee_user_id | client_user_id
??????????
1 | 18 | 492
2 | 24 | 492
3 | 21 | 492

====================
portal_messages_rel
====================
id (AI) | message_id | client_user_id | employee_user_id | read (0 unread / 1 read)
??????????
1 | 1 | 492 | 18 | 0
2 | 1 | 492 | 24 | 0
3 | 1 | 492 | 21 | 0


What I?m now needing is to add a ?Unread/Read? feature, so that when the employee views (reads) the message it shows that the message is read. And this is where I?m stuck. I currently have a ?read? column added to ?portal_messages_rel? that defaults to ?0? when inserted. The client is able to view the ?portal_messages_rel? list to see which employees have read the message(s).

Would anyone have a suggestion on, either, if an employee clicks on the ?subject? to view the message details it changes the ?read? in the ?portal_messages_rel? table from 0 to 1; or, if needed, the employee views the message and there?s a Yes/No element that they have to click ?Yes? to show that they read the message.

I thought maybe adding a hidden ?change-to-read? calc element to ?portal_messages?, and when the message details are viewed by the employee it runs onload and changes the employee?s ?read? who is reading the message from 0 to 1 in the ?portal_messages_rel? table. But not sure if that?s the best approach.

Will eventually need to show the unread message in the ?portal_messages? list as bold, but I'll tackle that after this step.

Any suggestions would be appreciated on what may be the best path to achieve this. Thanks!

 

[Ghuerren]

Maybe you could try the auto fill plugin....

 

[hominid4]

Thanks Gherkin for the suggestion, that looked promising but didn?t quite see how to change the ?portal_messages_rel___read? value to ?1? when viewing the message.

I had to get up something quick up before the holiday so I created a custom Details template just for this (which I was hoping to not have to do) and added the below PHP to that; I?m not sure if it?s fully correct but it?s doing what I?m needing in regards to setting the ?portal_messages_rel___read? value from ?0? to ?1?:

$myDb = JFactory::getDbo();
$myQuery = $myDb->getQuery(true);

$user = JFactory::getUser(); // get user ID
$userId = $user->get('id');
$userId = $myDb->quote($userId);

$app = JFactory::getApplication(); // get viewed row/message ID
$input  = JFactory::getApplication()->input;
$rowId = $input->get('rowid');
$rowId = $myDb->quote($rowId);

$messageTable = $myDb->quoteName('portal_messages_rel');

$myQuery = 'UPDATE ' . $messageTable . ' SET `read` = 1 WHERE (`message_id` = ' . $rowId . ') AND (`employee_user_id` = ' . $userId . ')';
$myDb->setQuery($myQuery);
$found = (int) $myDb->execute();

I also added a ?message_state? calc element to the ?portal_messages? list that selects the ?portal_messages_rel___read? value on load and I use this element as the row class in the ?portal_messages? List to use for my CSS to bold the unread rows.

Thanks again!