Email notifications from CB user table

Hi to all
I'm using Community Builder to add custom fields to my user table. One of this field is named 'cb_scadenzacert', its type is date, and its content is the expiry date of medical certificate for an user.

Now I'm trying to create a semi-automatic notification mail: when the expiry date is <= six days from today, then the system sends an email to the given user.

To do this I just built a new form, with only the Submit button. So I set, first the DB multi record loader: it connects to cb-comprofiler table, it lists username, email, cb_anagrafica and cb_scadenzacert fields.

In the Advanced tab I set this Where statement:

(CURDATE()-cb_scadenzacert)<=6;

is there any syntax error?

Then, on Record Found I dragged the Email send action wishing to set email field (from cb-comprofiler table) in Dynamic To.

Is it possible?
Any suggestions?

I dragged show thanks message action in both cases (record found vs record not found), and actually the form behaves as it doesn't find any result. Actually there is one person whose certificate is expiring in 5 days...

Thanks to all

Hi davidakis,

is there any syntax error?

Err yes, arrays use => not <=

Bob

Same issue replacing with the code

(CURDATE()-cb_scadenzacert) =<6

Actually the first error is surely in the Where statement.

What about the email action? Can I configure it by putting in 'Dinamic To' email field fetched from Cb table?

Thanks a lot

Hi davidakis,

Please check your code.

I think that you can add any input name that has a valid email address in the Dynamic Email To box.

Bob

To clarify:
actually in my form there is no input field, only the submit button.

Then, on submit, I connect to comprofiler table, launch the query and wish to send email for each user whose expiry date is near. Maybe I can do this better by custom code action.

Hi Davidakis,

If you get my Email [GH] action for CFv4 that will accept an input name of an array of Email addresses and will, if you like, send separate emails to each of them. This is OK if the email is identical; if you need to personalise the emails then the easiest way is to use a Custom Code action to add a PHP loop to build and send the emails for you.

You'd need to use a Custom Code action to extract the email list (and any other info you need) from the CB Tables.

Bob

First step done. Here below the latest code i put in my Where statement:

DATEDIFF(cb_scadenzacert, CURDATE()) <=6
ORDER BY cb_scadenzacert

Actually I have no idea of how I can extract email address from CB tables, since CB itself connects to Joomla Users' table to list that.

Thank you a lot for any suggestions.

Update: I realised that the user id in Cb table is the same of Joomla users. So i dragged a Custom Code action in which I wrote down this snippet:

$user =& JFactory::getUser($user_id);
$form->data['user_email'] = $user->email;

then I dragged a debugger to check the form->data array. I found that email is empty. Where's the matter?

Thanks

The matter seems to be the connection to Joomla Users' table, to which I should pass the ID (loaded by Cb table) and then I can get the users' mail address.

New Update: I found tht Db record loader creates JhostComprofiler Array, so I wish to pass its Id value to get users' mail from Joomla native table. To do this I set this snippet:

$expiring = $form->data['JhostComprofiler']['user_id'];

foreach ($expiring as $n => $val) {
  $user =& JFactory::getUser($val);
  $form->data[$val] = $user->email;
  $form->data[$val] = $user->user_id;
}

The result is an empty array...

Update: reading <a href="http://www.chronoengine.com/faqs/2649-how-can-i-link-%3Cspan%20class=">this FAQ </a> I found that I probably have to drag two Multi DB Record Loaders, joining two tables. So I try to do this:

On Submit I dragged first Multi DB Record getting data from Joomla users' table.

Basic settings:
Model Id = Users
fields = empty;

Advanced Settings:
Load data = No
Enable Associations = No
Join Rule = 'user'.'email' = 'user'.'id'
Associated Models = user

Second Multi DB:
table: comprofiler
Model Id = expiring
Fields = empty

Advanced Settings:
Load data = Yes
Enable Associations = Yes
Associated Models = user

I put a Debugger and I got only results from second Multi DB Loader action, so I think I failed to get users' mail from Joomla users table.

Latest Update: now Ia have got the two arrays needed. I dragged, on submit, two MultiDB Loaders, the second one is inside the first (On record found). The first concerns the table comprofiler, the second Joomla users' table, extracting only ID and email.

So I finally have two arrays: scadenze, which contains the result of query on comprofiler, and users, with only id and email fields;

Then I put a Custom Code action outside de DB loaders to link the results by using id, but I failed. Here below the custom code:

$result1 = $form->data['scadenze']['id'];
    $result2 = $form->data['users'][$result1];

Thanks a lot for any suggestion.

Latest custom Code:

<?php

foreach ($form->data['scadenze'] as $n => $val) {
    
$form->data['paramID'] = $form->data['scadenze']['id'];  /* registra l'id come parametro di ricerca */

foreach ($form->data['paramID'] as $p => $val) {

if array_search($val,$form->data['users'])==TRUE {
$form->data['usermail']=$form->data['users'][$val]['email'];  /* registra la mail in un array */
}
}
}

?>

I got only 'scadenze' and 'users' arrays. Actually it seems that the Custom Code action is skipped.

Same issue after I modified If-clause:

if (array_search($val,$form->data['users'])==TRUE) {