After adding fields to view table, date element is showing null but database is populated

[Egocentralis]

Hi,

We have some view tables what are created by a query in php my admin. In the elements we have elements containing the tables names and showing the content of the database. Normally this is working great.

We have a fabrik scheduled task what is looking into the view table and checks if a employee has submitted his working hours of last week. If not, an email will be generated and in the email there is some data from the view table. This works good. After adding some fields to te view table and creating the elements in the list the tables in the database get populated. However the date fields in the list/form are empty but the dates are visible in the table.

First i was thinking it was a mysql error but i couldnt find a sollution. Now, i think its something with fabrikar. Or a combination of things. It would be really nice if someone can point me in the good direction, or understands what or why this is happening. I will post info about the old situation with working view and date elements and then the new situation.

Old view situation.

The Query:

select `p`.`id` AS `id`,`p`.`persid` AS `persid`,`p`.`roepnaam` AS `roepnaam`,`p`.`email` AS `email`,(curdate() - interval (dayofweek(curdate()) + 5) day) AS `van`,(curdate() - interval (dayofweek(curdate()) - 1) day) AS `tot` from `dbummbqh72uvyy`.`g4e_orakel_personeel` `p` where ((`p`.`status` = '1') and (not((`p`.`ac` like '%"Uren"%'))) and (cast(`p`.`in_dienst_sinds` as date) <= (curdate() - interval (dayofweek(curdate()) + 5) day)) and (not(exists(select `z`.`persid` from `dbummbqh72uvyy`.`g4e_orakel_personeel_ziekteuren` `z` where ((`z`.`persid` = `p`.`persid`) and (`z`.`van` <> '0000-00-00 00:00:00') and (cast(`z`.`van` as date) <= (curdate() - interval (dayofweek(curdate()) - 1) day)) and ((cast(`z`.`tot` as date) >= (curdate() - interval (dayofweek(curdate()) + 5) day)) or (`z`.`tot` = '0000-00-00 00:00:00')))))) and (not(exists(select `v`.`parent_id` from `dbummbqh72uvyy`.`g4e_orakel_personeel_verlof` `v` where ((`v`.`parent_id` = `p`.`id`) and (`v`.`van` <> '0000-00-00 00:00:00') and (cast(`v`.`van` as date) <= (curdate() - interval (dayofweek(curdate()) - 1) day)) and ((cast(`v`.`tot` as date) >= (curdate() - interval (dayofweek(curdate()) + 5) day)) or (`v`.`tot` = '0000-00-00 00:00:00')))))) and (not(exists(select `u`.`persid` from `dbummbqh72uvyy`.`g4e_orakel_urenreg` `u` where ((`u`.`persid` = `p`.`persid`) and (cast(`u`.`datum` as date) >= (curdate() - interval (dayofweek(curdate()) + 5) day)) and (cast(`u`.`datum` as date) <= (curdate() - interval (dayofweek(curdate()) - 1) day)))))))

In the show details of administrator it looks like:

Its about the "van" and "tot" fields.

in the database it looks like this:


In the scheduled task/plan I have an email plugin enabled. To grab the info we use:

$eind_dat = strtotime("{g4e_orakel_urenlijst_1w___tot}");
$beg_dat = strtotime("{g4e_orakel_urenlijst_1w___van}");

$week = date("W", $beg_dat);

$beg_dat = date("d-m-Y", $beg_dat);
$eind_dat = date("d-m-Y", $eind_dat);

$bericht = 'Beste {g4e_orakel_urenlijst_1w___roepnaam}, <br /><br />';
$bericht .= 'blabla ' . $week . ' (' . $beg_dat . ' - ' . $eind_dat . ') blabla<br /><br />';

return $bericht;

I have shortened the message. This is returning week 19 (03-05-2021 -09-05-2021) So thats good.

New view situation:

The query:

select `p`.`id` AS `id`,`p`.`persid` AS `persid`,`p`.`roepnaam` AS `roepnaam`,`p`.`email` AS `email`,(curdate() - interval (dayofweek(curdate()) + 5) day) AS `van`,(curdate() - interval (dayofweek(curdate()) - 1) day) AS `tot`,`b`.`email_adres_bedrijf` AS `bedrijfemail`,`b`.`vol_naam` AS `cp_vol_naam`,`b`.`bedrijfsnaam` AS `bedrijfsnaam`,`q`.`te_gebruiken_email` AS `gebruikemail`,`q`.`te_gebruiken_bedrijfsnaam` AS `tegebrbedrijfsn`,`m`.`vol_naam_ontv_meld` AS `ontvangermelding`,`m`.`email_ontv_meld` AS `ontvangmeldemail` from (((`dbrqdg58wpf72x`.`g4e_orakel_personeel` `p` left join `dbrqdg58wpf72x`.`egocentralis_bedrijven` `b` on((`p`.`bedrijf_id` = `b`.`bedrijf_id`))) left join `dbrqdg58wpf72x`.`contactpers_bedrijven` `m` on((`p`.`bedrijf_id` = `m`.`bedrijf_id`))) left join `dbrqdg58wpf72x`.`bedrijf_variabelen` `q` on((`p`.`bedrijf_id` = `q`.`bedrijf_id`))) where ((`p`.`status` = '1') and (not((`p`.`ac` like '%"Uren"%'))) and (cast(`p`.`in_dienst_sinds` as date) <= (curdate() - interval (dayofweek(curdate()) + 5) day)) and (not(exists(select `z`.`persid` from `dbrqdg58wpf72x`.`g4e_orakel_personeel_ziekteuren` `z` where ((`z`.`persid` = `p`.`persid`) and (`z`.`van` <> '0000-00-00 00:00:00') and (cast(`z`.`van` as date) <= (curdate() - interval (dayofweek(curdate()) - 1) day)) and ((cast(`z`.`tot` as date) >= (curdate() - interval (dayofweek(curdate()) + 5) day)) or (`z`.`tot` = '0000-00-00 00:00:00')))))) and (not(exists(select `v`.`parent_id` from `dbrqdg58wpf72x`.`g4e_orakel_personeel_verlof` `v` where ((`v`.`parent_id` = `p`.`id`) and (`v`.`van` <> '0000-00-00 00:00:00') and (cast(`v`.`van` as date) <= (curdate() - interval (dayofweek(curdate()) - 1) day)) and ((cast(`v`.`tot` as date) >= (curdate() - interval (dayofweek(curdate()) + 5) day)) or (`v`.`tot` = '0000-00-00 00:00:00')))))) and (not(exists(select `u`.`persid` from `dbrqdg58wpf72x`.`g4e_orakel_urenreg` `u` where ((`u`.`persid` = `p`.`persid`) and (cast(`u`.`datum` as date) >= (curdate() - interval (dayofweek(curdate()) + 5) day)) and (cast(`u`.`datum` as date) <= (curdate() - interval (dayofweek(curdate()) - 1) day)))))))

I have added all extra elements in the list/form. The elements are populated in the database correctly. But, when the email got sended its retuning: week 01 (01-01-1970 - 01-01-1970) This is caused by strtotime is getting a NULL value. This is strange since in the database the dates are in the tables.

In the database it looks like this:

As you can see the dates are in "van" and "tot". The rest of the fields are also populated correctly.

But, when I go to show details in the administrator section of this list/form there is something strange.



Its not giving the date values, so thats why strtotime can only see NULL.

All other extra fields are populated:


So I tried to give the "van" and "tot" tables another name, i did create different ones, changed elements to the new tables.. but all without luck.

I tried left join, right join, inner join, etc.. to see if this would solve the issue.. unfortunately with no luck.

I checked all settings from the date elements, but they are exactly the same as the date element settings of the old view table situation.

I gave "van" and "tot" different positions.. in the database and later also as element.. but with no luck.

So what do I miss, why is it that the date fields cannot grab the dates from the tables in the database and keep empty. In the database the fields are populated .. but in details of the list/form .. empty.

Its driving me nuts.. been looking for a sollution for the whole week, but I cannot think of something different anymore.

Please, can someone tell me why this is happening.

My thanks would be forever and very big.

 

[troester]

What is the name of your MySQL view?

Do you have a Fabrik list pointing to this view and did you select this Fabrik list in your scheduled email plugin?
If you use {g4e_orakel_urenlijst_1w___tot} you must have an element with this full name in the list you have selected.
i.e.
Is the name of your MySQL view = g4e_orakel_urenlijst_1w ?

If you are pointing to the correct view:
Try {..._raw} or without placeholders $row->tot
http://fabrikar.com/forums/index.php?wiki/email-scheduled-task/#php-duration-example

 

[Egocentralis]

troester!!!!!

All things you asked about the list and the names are set correct.

I tried the raw before with no luck because in the details view the date fields stayed empty. But, now i did the _raw part and started the scheduled task. The emails are sended with the correct dates. (WOOHOO)

But still I have some things i dont understand and are not working.

1) Why is it in the old situation we do not need _raw and the emails are sended with the proper date. I did nothing in the element settings of the fields "van" and "tot" I only have added some fields in the vieuw query but we have to add _raw to get the dates in the email?

2) When I go to fabr/list and look into the "view data" of that list. .. i still see the "van" and "tot" fields empty?

This is the old situation without the extra fields:

This is the new situation with the extra fields and with _raw


In the database the dates are in the "van" and "tot" tables. So i do not understand.

In the section below Eval (yes) condition i had a query to update "g4e_orakel_ic_uren" I noticed all values where 000-00-00-00-00 .. I used this query for it:

$db_ic = FabrikWorker::getDbo();
$q_ic = $db_ic->getQuery(true);
$q_ic
  ->insert('g4e_orakel_ic_uren')
  ->columns('persid, van, tot, aanwezig, controle, week, weeknr, jaar')
  ->values($db_ic->quote('{g4e_orakel_urenlijst_1w___persid}') . ', ' . $db_ic->quote('{g4e_orakel_urenlijst_1w___van}') . ', ' . $db_ic->quote('{g4e_orakel_urenlijst_1w___tot}') . ', ' . $db_ic->quote('0') . ', ' . $db_ic->quote('1') . ', ' . $db_ic->quote(date('W', strtotime('{g4e_orakel_urenlijst_1w___van}'))) . ', ' . $db_ic->quote('{g4e_orakel_urenlijst_1w___van}') . ', ' . $db_ic->quote(date('Y', strtotime('{g4e_orakel_urenlijst_1w___van}'))));
 
$db_ic->setQuery($q_ic);
$db_ic->execute();

return true;

So i have added _raw to every quote.. and now the data is coming correctly in the g4e_orakel_ic_uren. But also, why is it I need to add raw.. while it did work first without the extra fields without _raw?

But.. thats less important. What i really need to know.. is why the dates are in the database, are also correct in the email by adding _raw .. but are not in the view data list/form? And how can i get the dates visible again.

 

[troester]

No idea.
Did you clear all Joomla and browser cache?
Enable Fabrik debug (in Fabrik options) and append &fabrikdebug=1 to your list URL.
Maybe the query shown in "list GetData" will give some hint.

BTW:
You are showing the "old" and "new" list with the same list name.
Is this on two different Joomla installations, the new site a copy of the old one?
In this case: did you open and resave the Fabrik connection?

 

[Egocentralis]

Yes, it are two different installations.. but I also tried in the same installation to change the fields etc.
Yes I resaved the connection.

Ok.. i am a noob.. how can I enable Fabrik Debug... where to find that setting?

 

[troester]

 

[henk]

Alway add _raw to date fields. So:
strtotime("{g4e_orakel_urenlijst_1w___tot}") should be: strtotime("{g4e_orakel_urenlijst_1w___tot_raw}")
strtotime("{g4e_orakel_urenlijst_1w___van}") should be: strtotime("{g4e_orakel_urenlijst_1w___van_raw}")
The "raw" value is the real value that is in the database.
The value without _raw may not be a valid date string as required by strtotime. This is the value used by fabrik.
In the old situation the value used by fabrik seems to be the same as the database value, lucky you....
If you want to debug then in your email plugin code add below the strtotime :
print_r('{g4e_orakel_urenlijst_1w___tot}'." ".'{g4e_orakel_urenlijst_1w___tot_raw}');
exit;
This will stop execution and dislpay the values, so you may see the difference.

 

[Egocentralis]

Sorry for late responds.. I did it with the _raw part and the date is correctly in email. But not showing in details in the list settings. But its not a problem, because i only need it in the email.