Getting data from second table field with calculations

Discussions related to customizing hooks. Hooks are documented at http://bigprof.com/appgini/help/advanced-topics/hooks/
Post Reply
mansoor
Posts: 5
Joined: 2014-05-07 04:48

Getting data from second table field with calculations

Post by mansoor » 2014-05-07 05:08

Can anyone please guide me. I searched almost every topic in the forum but didn't found clear guide for my issue.

table1= employee

field1= id
field2= name
field3= annual_leaves (total leaves allotted to an employee. I set default value to 15)


table2= leaves

field1= id
field2= name (lookup field from table1)
field3= app_leave (applied leave. this is where an employee will enter his required number of leaves but this should not be greater then alloted leaves from table1 field annual_leaves)

field3= rem_al (Remaining annual leaves........... I want this field to be automatically calculated when a user enter his applied leaves value in app_leave field. This value should calculate values from the formula rem_al = annual_leaves - app_leave

i am a newbie with php. Can anyone guide me how can I achieve this?

peebee
AppGini Super Hero
AppGini Super Hero
Posts: 352
Joined: 2013-03-21 04:37

Re: Getting data from second table field with calculations

Post by peebee » 2014-05-07 06:47

Try applying a leaves_before_update() hook in your leaves.php hook file

See here: http://bigprof.com/appgini/help/advance ... ore_update

function leaves_before_update(&$data, $memberInfo, &$args){
// calculate Remaining Annual Leave
$annual_leaves=sqlValue("select annual_leaves from employee where id='{$data['selectedID']}'");
$data['rem_al'] = $annual_leaves - $data['app_leave'];
return TRUE;
}

I think that might work for you....

mansoor
Posts: 5
Joined: 2014-05-07 04:48

Re: Getting data from second table field with calculations

Post by mansoor » 2014-05-07 07:24

I applied your code but some problem.

1- I entered value 2 in applied field and save it, the remaining field value is not auto populating and not showing remaining leaves balance (still blank/ empty)

2- When I re-visit this entry again to open and click on save second time it shows me value of -2 in remaining field rem_al. Same is the case if I add 5 in applied leave it shows -5.

peebee
AppGini Super Hero
AppGini Super Hero
Posts: 352
Joined: 2013-03-21 04:37

Re: Getting data from second table field with calculations

Post by peebee » 2014-05-07 09:13

1- The hook won't update the rem_al field until the record is saved. The result should appear after the record is updated. That's the purpose of the tablename_before_update() hook.

2. From what you say, it appears as though the calculation is not taking into account the 15 that you say is already entered into the the annual_leaves field in the employee table as a default? That's why when you enter 2 in app_leave the result is -2 in rem_al. It is just calculating $data['rem_al'] = ? - $data['app_leave']. So enter 5, result is -5, etc...

Are you sure the correct table name is "employee" and field name is "annual_leaves". Have you checked the value in the annual_leaves field is 15? And when I think about it, you are likely going to have a problem I can see because while you might be able to calculate the remianing leave in the leaves table, you are also going to have to subtract the app_leave amount from the remaining annual_leaves days in the employee table so future calculations are correct? Would that be right?

mansoor
Posts: 5
Joined: 2014-05-07 04:48

Re: Getting data from second table field with calculations

Post by mansoor » 2014-05-07 10:21

Please find attached screenshot and inform me if I did anything wrong.
Attachments
leaves.png
leaves.png (121.95 KiB) Viewed 14910 times

peebee
AppGini Super Hero
AppGini Super Hero
Posts: 352
Joined: 2013-03-21 04:37

Re: Getting data from second table field with calculations

Post by peebee » 2014-05-07 23:11

Getting the value of annual_leaves from the employee table is the problem. There's a left join required to the employee table to get the value. Sadly, my coding skills are pretty poor too.

ref: http://www.w3schools.com/sql/sql_join_left.asp

something like.... (although you'll probably have to fiddle around with this)?

$annual_leaves=sqlValue("select e.annual_leaves from employee e left join leaves l on e.id=e.name where leave_id='{$data['selectedID']}'");
$data['rem_al'] = $annual_leaves - $data['app_leave'];

mansoor
Posts: 5
Joined: 2014-05-07 04:48

Re: Getting data from second table field with calculations

Post by mansoor » 2014-05-08 04:25

Still not working.

wplim
Veteran Member
Posts: 36
Joined: 2013-01-17 22:42

Re: Getting data from second table field with calculations

Post by wplim » 2014-05-12 05:25

Hi Mansoor,

Here's what you need:
Add these codes in leaves.php in the hook folder.
Use after insert instead of before because the calculation is performed after data is stored into database.
Also, I have set the rem_al as read only.

After insert:

Code: Select all

	function leaves_after_insert($data, $memberInfo, &$args){
			$id = makeSafe($data['selectedID']);
			$emp_id = makeSafe($data['name']);
			$apply = sqlValue("SELECT app_leave FROM leaves WHERE leave_id = '$id' ");
			$total = sqlValue("SELECT annual_leaves FROM employee WHERE id = '$emp_id' ");
			$balance = $total - $apply;
			sql("UPDATE leaves SET rem_al ='$balance' WHERE leave_id ='$id' ", $eo);	
		return TRUE;
After update:
- Recall after insert function to reuse the same code.
- Note: When recalling a function the $args parameter is without "&"

Code: Select all

	function leaves_after_update($data, $memberInfo, &$args){

		return leaves_after_insert($data, $memberInfo, $args);
	}
Cheers,
Weoi

mansoor
Posts: 5
Joined: 2014-05-07 04:48

Re: Getting data from second table field with calculations

Post by mansoor » 2014-05-12 06:17

Thankyou so much wplim. You are great. It worked.

lectura
Posts: 28
Joined: 2015-01-10 13:29

Re: Getting data from second table field with calculations

Post by lectura » 2017-12-19 19:36

Am still struggling with codes... i have tried this but in my results its gives me 0 value.. am not sure where i did wrong.. any help is welcome..

R Tammam
Veteran Member
Posts: 113
Joined: 2017-08-26 15:35

Re: Getting data from second table field with calculations

Post by R Tammam » 2018-01-08 17:32

Hello lectura,
could you tell me what exactly is the problem?

xbox2007
Veteran Member
Posts: 129
Joined: 2016-12-16 16:49

Re: Getting data from second table field with calculations

Post by xbox2007 » 2018-01-28 15:40

hello
i have same project and i try this code , it work only after i press Save changes !!!!

i want ask if there are any way to make update while inserting date

R Tammam
Veteran Member
Posts: 113
Joined: 2017-08-26 15:35

Re: Getting data from second table field with calculations

Post by R Tammam » 2018-02-20 04:58

Hello xbox,

redarding to your question , you should
1-make an ajax fie to get balance

$id = makeSafe($_REQUEST['selectedID']);
$emp_id = makeSafe($_REQUEST['name']);
$apply = sqlValue("SELECT app_leave FROM leaves WHERE leave_id = '$id' ");
$total = sqlValue("SELECT annual_leaves FROM employee WHERE id = '$emp_id' ");
$balance = $total - $apply;

2- then you should make javascript code to update the reurned balabce

xbox2007
Veteran Member
Posts: 129
Joined: 2016-12-16 16:49

Re: Getting data from second table field with calculations

Post by xbox2007 » 2018-02-20 07:16

i already fix , it i add this code in hock after update

Code: Select all

function vacation_after_update($data, $memberInfo, &$args){
		
		$id = intval($_REQUEST['id']);
		$name = intval($_REQUEST['name']);
		$bb_B = sqlValue("select bb_1 from vacation where id ='{$id}'");
		$bb_A = sqlValue("select bb from vacation where id ='{$id}'");

		$annual_1 = sqlValue("select annual_1 from Emp where id='{$name}'");
		$casual_1 = sqlValue("select casual_1 from Emp where id='{$name}'");
		
		$total_Aday = sqlValue("SELECT sum(days) from vacation where Action ='موافقة' And vcation = 1 AND name='{$name}'");
		$total_Cday = sqlValue("SELECT sum(days) from vacation where Action ='موافقة' And vcation = 3 AND name='{$name}'");
		
		$total_Annn = $annual_1 - $total_Aday;
		$total_casu = $casual_1 - $total_Cday;
		
		sql("update Emp set annual='{$total_Annn}' where id='{$name}' AND '{$bb_B}'=''", $eo);
		sql("update Emp set casual='{$total_casu}' where id='{$name}' AND '{$bb_A}'=''", $eo);
			return TRUE;
		}
i think its same your code

also we can use ajax but i find this is easy for me

thanks alot

Post Reply