AppGini Community Forums

A place where AppGini users can exchange ideas and help each other.
https://forums.appgini.com:443/phpbb/

Working with Calculated Field Issue

https://forums.appgini.com:443/phpbb/viewtopic.php?f=7&t=3804

Page 1 of 1

Working with Calculated Field Issue

Posted: 2020-08-03 11:53
by epano
Hello,

I have build a contract management database with appgini and i have an issue on receiving automatic email notifications for contacts that are near expiration date. What i have done:
So I have created a calculated field that is called alert and it is calculated with an if statement depending of the result of the diff between the finish date and the Today date. Than i have used hooks before and after update to send email notification when the field alert is changed.

Code: Select all

      function Kontratat_before_update(&$data, $memberInfo, &$args) {
		
		$id_kontrata = makeSafe($data['selectedID']);
		
		$res = sql("select * from kontratat where id_kontrata='{$id_kontrata}'", $eo);
		
		$GLOBALS['old_data'] = db_fetch_assoc($res);
		
		return TRUE;
	}
	function Kontratat_after_update($data, $memberInfo, &$args) {
		
		
		$id_kontrata = makeSafe($data['selectedID']);	
		$data['alert'] = sqlValue("SELECT IF(status!='Ongoing','',
		IF(DATEDIFF(finish_date,NOW()) > 60,'',
		IF(DATEDIFF(finish_date, NOW()) > 30,'2 months',
		IF(DATEDIFF(finish_date, NOW()) > 0,'1 month','Expired'))))
			FROM Kontratat WHERE id_kontrata = '{$id_kontrata}'");
									
		if($data['alert'] != $GLOBALS['old_data']['alert'])
	
	         {
				sendmail(array(
				'to' => '[email protected]',
				'name' => 'Contract Alert',				
				'subject' => 'Alert Kontrata nr. ' . $data['id_kontrata'].' - '.$data['objekti_kontrata'],			
							
				'message' =>'old data'. $GLOBALS['old_data']['alert'].'new data'.$data['alert']
				));		
		}
		return TRUE;
	}
If i change the value of the finish date manually by clicking the button save it works, i receive the email perfectly.
But it doesn't work automatically. When the date is about to expire i see that the field alert is updated on the background but i dont receive email notification. Please any tip what should i do?

Re: Working with Calculated Field Issue

Posted: 2020-08-04 09:33
by epano
What i want is to send email notification when a calculated field is changed. It works when the field is updated manually in detail view by submitting the save button.
However the field is changed on the background by reloading the page in table view, i dont receive an email notification.
So does the hook after update works with calculated field? I have spent a lot of time in resolving these issue and now dont have any idea. Please any solution if someone can help!
Best regards!

Re: Working with Calculated Field Issue

Posted: 2020-08-04 09:48
by a.gneady
Your guess is right. Calculated fields don't get updated at the same time as the rest of the record currently (till AppGini 5.84) so they don't get detected in after_update hook. We plan to fix this in future releases of AppGini. As a temporary work-around, you could add this code into the after_update hook before checking if the calculated field is changed:

Code: Select all

$formulas = calculated_fields();
update_calc_fields('tablename', $id, $formulas['tablename']);
(where tablename is the name of the concerned table)

Re: Working with Calculated Field Issue

Posted: 2020-08-04 09:56
by jsetzer
Updating calculated fields in TV does NOT execute TABLENAME_after_update() function in hooks/TABLENAME.php, if I remember right.

You should consider...
  • doing calculations and sending emails in TABLENAME_init() hook (the old fashioned, but bulletproof way)
    and/or
  • configuring a cron-job which executes a serverside script for periodically updating records/sending emails
@Ahmed: Sorry, you were a couple of seconds faster than me ;)

Re: Working with Calculated Field Issue

Posted: 2020-08-04 11:06
by epano
Thank you a.gneady but still doesnt work. I used the code below on after_update hook:

Code: Select all

$formulas = calculated_fields();
update_calc_fields('kontratat', $id_kontrata, $formulas['kontratat']);
What can i do else?
Thanks

Re: Working with Calculated Field Issue

Posted: 2020-08-04 12:49
by a.gneady
epano wrote: ↑
2020-08-04 11:06
 Thank you a.gneady but still doesnt work. I used the code below on after_update hook:
Hmm ... Please post the entire code of the before_update and after_update hooks.

Re: Working with Calculated Field Issue

Posted: 2020-08-04 13:10
by epano

Code: Select all

function Kontratat_before_update(&$data, $memberInfo, &$args) {
		
		$id_kontrata = makeSafe($data['selectedID']);
		
		$res = sql("select * from kontratat where id_kontrata='{$id_kontrata}'", $eo);
		
		$GLOBALS['old_data'] = db_fetch_assoc($res);
		
		return TRUE;
	}
	function Kontratat_after_update($data, $memberInfo, &$args) {
		
		
		$id_kontrata = makeSafe($data['selectedID']);	
		$data['alert'] = sqlValue("SELECT IF(status!='Ongoing','',
		IF(DATEDIFF(finish_date,NOW()) > 60,'',
		IF(DATEDIFF(finish_date, NOW()) > 30,'2 months',
		IF(DATEDIFF(finish_date, NOW()) > 0,'1 month','Expired'))))
			FROM Kontratat WHERE id_kontrata = '{$id_kontrata}'");
			
	        $formulas = calculated_fields();
                update_calc_fields('Kontratat', $id_kontrata, $formulas['Kontratat']);
									
		if($data['alert'] != $GLOBALS['old_data']['alert'])
	
	         {
				sendmail(array(
				'to' => '[email protected]',
				'name' => 'Contract Alert',				
				'subject' => 'Alert Kontrata nr. ' . $data['id_kontrata'].' - '.$data['objekti_kontrata'],			
							
				'message' =>'old data'. $GLOBALS['old_data']['alert'].'new data'.$data['alert']
				));		
		}
		return TRUE;
	}
I test it by changing the PC date...the values of alert field are changed but i dont receive email notification.

Re: Working with Calculated Field Issue

Posted: 2020-08-04 15:22
by a.gneady
jsetzer wrote: ↑
2020-08-04 09:56
 @Ahmed: Sorry, you were a couple of seconds faster than me ;)
No worries. Thanks so much for your help. Always appreciated :)

Re: Working with Calculated Field Issue

Posted: 2020-08-04 15:32
by a.gneady
@epano, if I understand correctly form your code, the 'alert' field is a calculated field. In this case, you don't need to repeat the calculation SQL in the after_update hook ... You should just call update_calc_fields() then check the field's new value:

Code: Select all

function Kontratat_after_update($data, $memberInfo, &$args) {
	$id_kontrata = makeSafe($data['selectedID']);	

	$formulas = calculated_fields();
	update_calc_fields('Kontratat', $id_kontrata, $formulas['Kontratat']);

	$alert = sqlValue("SELECT alert FROM Kontratat WHERE id_kontrata='$id_kontrata'");
								
	if($alert != $GLOBALS['old_data']['alert']) {
		sendmail(array(
			'to' => '[email protected]',
			'name' => 'Contract Alert',				
			'subject' => 'Alert Kontrata nr. ' . $data['id_kontrata'].' - '.$data['objekti_kontrata'],									
			'message' =>'old data '. $GLOBALS['old_data']['alert'].', new data '. $alert
		));		
	}

	return TRUE;
}

Re: Working with Calculated Field Issue

Posted: 2020-08-04 20:19
by epano
Im sorry but still i dont have result. In table view even though the calculated field is changed in the background, i dont receive an email notification. I dont know but it seems like the update does not respond with_after update hook; loading the page in table view doesnt affect it.

Re: Working with Calculated Field Issue

Posted: 2020-08-04 20:26
by epano
jsetzer wrote: ↑
2020-08-04 09:56
 Updating calculated fields in TV does NOT execute TABLENAME_after_update() function in hooks/TABLENAME.php, if I remember right.

You should consider...
  • doing calculations and sending emails in TABLENAME_init() hook (the old fashioned, but bulletproof way)
Is the code that i have used compatible with table_init hook? How can i use it?
Thank you very much all for supporting me :)

Re: Working with Calculated Field Issue

Posted: 2020-08-05 15:49
by a.gneady
epano wrote: ↑
2020-08-04 20:19
 Im sorry but still i dont have result. In table view even though the calculated field is changed in the background, i dont receive an email notification. I dont know but it seems like the update does not respond with_after update hook; loading the page in table view doesnt affect it.
OK, that would need some debugging .. you could try outputting the data before and after update to a log file and inspecting it ... this code would do so:

Code: Select all

@file_put_contents('data.log', json_encode($data) . "\n", FILE_APPEND);
You should put the above line into before_update hook and after_update hook.

You should also add this one after the lines for updating calculated fields in after_update:

Code: Select all

@file_put_contents('data.log', "alert: $alert\n", FILE_APPEND);
After each record update, the data.log file would include 3 new lines: the first line is the record data before update, the second is data after update, and the third line is the new value of alert after updating calculated fields. Inspecting this would give a better idea of what's going on.

Re: Working with Calculated Field Issue

Posted: 2020-08-06 10:08
by epano
The output from 2 point of view:

1) I changed the date of server and after opening the table view "Kontratat" the field alert value changed from "2 months" to "1 month", BUT the data.log was empty and i didnt receive an email notification.

2) I entered in detail view by clicking a random contact, I changed the finish date manually and saved it. In these case the data.log included 3 lines where the field alert was changed after calculation. And i received the email notif perfectly.

* I think that rendering the page in TV isn't related and does not execute before or after_update hook.
All times are UTC
Page 1 of 1
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/