{Solved} CRON php syntax

[thellie]

Hi, me again...

I would like to set a scheduled task, in order to check and change the user group of volunteers based on comparing their startdate and the current date. I have the MySQL query written and raring to go, but I'm not sure how to write the PHP code it gets wrapped in for this... Is there an example code that can be posted for me please?

I normally grab an existing piece of code from the forum, or wiki, or online somewhere, but can't find anything for the new J!4/F4 version.

Thanks in anticipation

My query is:

UPDATE nm7t_user_usergroup_map AS t
LEFT JOIN vol_appform AS e
ON e.user_id = t.user_id

SET t.group_id = CASE

WHEN (t.group_id =22) && (curdate() >= e.startdate && curdate()<=IF(e.actual_enddate,e.actual_enddate,e.enddate))
&& e.programme =22
THEN 19

WHEN (t.group_id =22) && (curdate() >= e.startdate && curdate()<=IF(e.actual_enddate,e.actual_enddate,e.enddate))
&& (e.programme =19 OR e.programme = 20)
THEN 20

WHEN (t.group_id =22) && (curdate() >= e.startdate && curdate()<=IF(e.actual_enddate,e.actual_enddate,e.enddate))
&& e.programme =23
THEN 18

WHEN (t.group_id =20 OR t.group_id =19) && (IF(e.actual_enddate,e.actual_enddate,e.enddate) < curdate())
THEN 25

WHEN (e.startdate >= curdate() && t.group_id != 21)
THEN 22

ELSE t.group_id

END;

 

[troester]

http://fabrikar.com/forums/index.php?wiki/php-common-tasks/&redirect=common-php-tasks

 

[thellie]

Okay, I'm sorry but I'm not getting very far with working out how to write it correctly, so I've dropped the query into some PHP code set for J!3, which I think should still work in J!4.

The MySQL works properly when running it in PHPMyAdmin, but when running the code through the Fabrik Scheduled Task, I'm getting an error message - MESSAGE BODY EMPTY.

$myDb = Joomla\CMS\Factory::getContainer()->get('DatabaseDriver');
$myQuery = $myDb->getQuery(true);
$myQuery="

UPDATE nm7t_user_usergroup_map AS t
LEFT JOIN vol_appform AS e
ON e.user_id = t.user_id

SET group_id = CASE

/* ON OUTDOOR VOL STARTDATE CHANGE FUTURE -> CURRENT_OUTDOOR */
WHEN (group_id =22) && (curdate() >= e.startdate && curdate()<=IF(e.actual_enddate,e.actual_enddate,e.enddate))
&& e.programme =22
THEN 19

/* ON CHILDCARE/TEACHING VOL STARTDATE FUTURE -> CURRENT_TEACHING */
WHEN (group_id =22) && (curdate() >= e.startdate && curdate()<=IF(e.actual_enddate,e.actual_enddate,e.enddate))
&& (e.programme =19 OR e.programme = 20)
THEN 20

/* ON ANIMALCARE VOL STARTDATE FUTURE -> CURRENT_ANIMAL */
WHEN (group_id =22) && (curdate() >= e.startdate && curdate()<=IF(e.actual_enddate,e.actual_enddate,e.enddate))
&& e.programme =23
THEN 18

/* WHEN PROGRAMME ENDS CHANGE ALL CURRENT VOLS TO PAST */
WHEN (group_id =20 OR group_id =19 OR group_id=18) && (IF(e.actual_enddate,e.actual_enddate,e.enddate) < curdate())
THEN 25

/* SET USERGROUP TO CANCELLED WHEN PROGRAMME CHANGES TO CANCELLED */
WHEN e.programme = 18
THEN 16

ELSE group_id

END;
";
$myDb->setQuery($myQuery);
$myDb->execute();

Any help greatly appreciated

 

[troester]

MESSAGE BODY EMPTY.

Which cron plugin are you running? Sounds like email but should be php?

I would start with a simple query. I'm not sure if comments are working and/or slashes must be escaped etc.
You can also select the infos you need from the database, then calculate group_id in php, the do a very simple update.

 

[thellie]

Dammit, apologies Troester - I was just looking to see which plugin I'd selected (it was the PHP plugin), but noticed that for some reason the list I had selected wasn't anymore. I've reselected it and the task is running.

 

[thellie]

One final thing I need help with, sorry - Correct me if I'm misunderstanding this, but isn't the Fabrik Scheduled Task meant to automatically run the task based on your settings?

I have the task set to run every 2 minutes and send out email logs to me to confirm it's running. But it will only run if I select the task in the tasks list and click on 'Run'....

How do you get it to run automatically?
UPDATE: I've just looked at the fabrik log table, and it's kind of running automatically - it's running, but not every two minutes... but I can live with that, especially as I'll only be running it twice a day when it's finished.

btw the query, when the task is run, is working perfectly.

 

[troester]

A Fabrik scheduled task is run/triggered only if there's some frontend activity.
So if there's little activity on your site and/or you must make sure it's triggered at certain times and/or you are using "Require querystring" you must set up e.g. a cron job on your server calling your J! site at defined times.

 

[thellie]

Okay, that clears it up, thanks Troester.

 

[thellie]

I finally set up the scheduled task and it works exactly as it should do.

But, I think there is a bug - if the scheduled task is published, it runs every time there's front end activity, correct?

When published, every time I save a form, the task performs properly but returns with a white screen, and I have to refresh once more to get the page to show again.

If the task is unpublished, everything is normal.

 

[thellie]

I've made a quick video to show you, but can't upload it yet... if I can't I'll put it online and put a link on here

EDIT: Here's the link

https://maeyao-local.com/videos/screengrab.mov

 

[troester]

You must not end your code with exit; if the scheduled task is published.
This will stop anything in the frontend.
(exit; is fine for debugging if you are testing the unpublished task in the backend via "Run")

 

[thellie]

You must not end your code with exit; if the scheduled task is published.
This will stop anything in the frontend.
(exit; is fine for debugging if you are testing the unpublished task in the backend via "Run")

Thanks. So just delete exit;

Is that what's causing the white screen then? I'll find out in a sec...

 

[thellie]

Thanks. So just delete exit;

Is that what's causing the white screen then? I'll find out in a sec...

Yes it is indeed...