How to show pre-calculated value on form

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
User avatar
dnaorem
Posts: 19
Joined: 2018-10-16 16:06
Location: Imphal, Manipur, India
Contact:

How to show pre-calculated value on form

Post by dnaorem » 2018-10-28 07:04

Dear Everyone, I am new in this forum.
I need help to show the pre-calculated value on form before submitting. I tried by using the tricks in hook folder (tablename.php) but it shows only after submission. And in the lesson in udemy shows a java scripting which can shows the value before submitting. But as I am not expert in Java, please someone help me. I have attached here a design of my form and table.
Attachments
table_form_design.png
table_form_design.png (39.2 KiB) Viewed 5939 times
With regard,
Debenkumar Naorem

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: How to show pre-calculated value on form

Post by pbottcher » 2018-10-28 15:21

Hi,

you need to put into the hooks/<tablename>-dv.js file your javascript.

This could be something like:

Code: Select all

function calc_load(id) {

$j.ajax({ 
       url: 'ajax_calc_load.php?&id='+id,
       success: function(res){}
	});

}

$j(function() { 
    var nameid=parseInt($j('#NAME').val();   // if you need also the user input for Capital Return you need to expand the calls to retrieve this value and pass it to the ajax call 
    if (nameid) calc_load(parseInt(nameid);
    
   $j('#NAME').on('change', calc_load(parseInt($j('#NAME').val())
});
Also you need to add a php file to your application root directory (something like ajax_calc_load.php).
This could look like:

Code: Select all

<?php
	$currDir = dirname(__FILE__);
	include("$currDir/defaultLang.php");
	include("$currDir/language.php");
	include("$currDir/lib.php");

	header('Content-type: text/javascript; charset=' . datalist_db_encoding);

	$id = makeSafe($_GET['id']);

	if($id){
			$current_balance = sqlvalue("PUT YOUR SQL HERE TO CALCULATE YOUR CURRENT BALANCE BASED ON THE ID");
			$interest = sqlvalue("PUT YOUR SQL HERE TO CALCULATE YOUR INTEREST BASED ON THE ID");
?>
			$j('#CURRENTBALANCE').val('<?php echo $current_balance; ?>');
			$j('#INTEREST').val('<?php echo $interest; ?>');
			<?php
	}
?>
Note that the information provided is not 100% clear to have a complete sample. So you need to figure out your fields and statements (and maybe also more logic if needed). But it could give you a starting point. The Code is not tested and may contain some syntax errors (hopefully not many :-) ).
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
dnaorem
Posts: 19
Joined: 2018-10-16 16:06
Location: Imphal, Manipur, India
Contact:

Re: How to show pre-calculated value on form

Post by dnaorem » 2018-10-28 15:26

Thanks to pböttcher,
Sir, I will try with this and let you know the result.

Thanks for your kindness.

With love,
Deben
With regard,
Debenkumar Naorem

User avatar
dnaorem
Posts: 19
Joined: 2018-10-16 16:06
Location: Imphal, Manipur, India
Contact:

Re: How to show pre-calculated value on form

Post by dnaorem » 2018-10-28 17:12

Sir have tried with my changed data, some screenshots are attached, unfortunately nothing has come up, neither error nor working. Please have a look.
Code in ajax_cal_load.php which is kept in root

<?php
$currDir = dirname(__FILE__);
include("$currDir/defaultLang.php");
include("$currDir/language.php");
include("$currDir/lib.php");

header('Content-type: text/javascript; charset=' . datalist_db_encoding);

$id = makeSafe($_GET['id']);

if($id){
$loan_interest=sqlValue("SELECT loan_interest FROM `loan_account` WHERE ID='{$data['member_name']}'");

$sum_capital=sqlValue("SELECT SUM(capital_return) FROM `loan_ledger` WHERE ID='{$data['member_name']}'");

$loan_amount=sqlValue("SELECT loan_amount FROM `loan_account` WHERE ID='{$data['member_name']}'");

$loan_balance=sqlValue("SELECT loan_balance FROM `loan_account` WHERE ID='{$data['member_name']}'");

$capital_return=sqlValue("SELECT capital_return FROM `loan_ledger` WHERE ID='{$data['member_name']}'";

?>

$j('#interest').val('<?php echo ($loan_interest * $loan_balance)/100; ?>');
$j('#amount_paid').val('<?php echo $capital_return + {($loan_interest * $loan_balance)/100}; ?>');
$j('#loan_balance').val('<?php echo $loan_amount - $sum_capital; ?>');
<?php
}
?>

code in loan_ledger-dv.js which is kept in hook folder

function calc_load(id) {

$j.ajax({
url: 'ajax_calc_load.php?&id='+id,
success: function(res){}
});

}

$j(function() {
var member_nameid=parseInt($j('#member_name').val(); // if you need also the user input for Capital Return you need to expand the calls to retrieve this value and pass it to the ajax call
if (member_nameid) calc_load(parseInt(member_nameid);

$j('#member_name').on('change', calc_load(parseInt($j('#member_name').val())
});
Attachments
loan_account_table.png
loan_account_table.png (32.34 KiB) Viewed 5918 times
loan_ledger_form.png
loan_ledger_form.png (39.49 KiB) Viewed 5918 times
With regard,
Debenkumar Naorem

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: How to show pre-calculated value on form

Post by pbottcher » 2018-10-28 17:45

Hi,
can you change

WHERE ID='{$data['member_name']}'

to

WHERE ID='{$id}'

Also, is the Capital return entered manually by the user or is this already available in the database?
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
dnaorem
Posts: 19
Joined: 2018-10-16 16:06
Location: Imphal, Manipur, India
Contact:

Re: How to show pre-calculated value on form

Post by dnaorem » 2018-10-28 17:54

Thanks again sir,
I will change the ID as you you have mentioned here, and the Capital Return is entered manually. I am showing the screen after entered the data in that Capital Return but no information comes in the Interest, Amount Paid and Loan Balance.

I will let you know by changing the ID matter.

With love,
Deben
With regard,
Debenkumar Naorem

User avatar
dnaorem
Posts: 19
Joined: 2018-10-16 16:06
Location: Imphal, Manipur, India
Contact:

Re: How to show pre-calculated value on form

Post by dnaorem » 2018-10-28 18:02

Sir I have changed as:
<?php
$currDir = dirname(__FILE__);
include("$currDir/defaultLang.php");
include("$currDir/language.php");
include("$currDir/lib.php");

header('Content-type: text/javascript; charset=' . datalist_db_encoding);

$id = makeSafe($_GET['id']);

if($id){
$loan_interest=sqlValue("SELECT loan_interest FROM `loan_account` WHERE ID='{$id}'");

$sum_capital=sqlValue("SELECT SUM(capital_return) FROM `loan_ledger` WHERE ID='{$id}'");

$loan_amount=sqlValue("SELECT loan_amount FROM `loan_account` WHERE ID='{$id}'");

$loan_balance=sqlValue("SELECT loan_balance FROM `loan_account` WHERE ID='{$id}'");

$capital_return=sqlValue("SELECT capital_return FROM `loan_ledger` WHERE ID='{$id}'";

?>

$j('#interest').val('<?php echo ($loan_interest * $loan_balance)/100; ?>');
$j('#amount_paid').val('<?php echo $capital_return + {($loan_interest * $loan_balance)/100}; ?>');
$j('#loan_balance').val('<?php echo $loan_amount - $sum_capital; ?>');
<?php
}
?>

Still no change.

With love,
Deben
With regard,
Debenkumar Naorem

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: How to show pre-calculated value on form

Post by pbottcher » 2018-10-28 21:39

Hi,

if you retreive the Capital Return from the input page you need to pass this as data to the ajax - php.

Code: Select all

function calc_load(id, cap) {

$j.ajax({ 
       url: 'ajax_calc_load.php?&id='+id+'&cap='+cap,
       success: function(res){}
	});

}

$j(function() { 
    var nameid=parseInt($j('#member_name').val();   // if you need also the user input for Capital Return you need to expand the calls to retrieve this value and pass it to the ajax call 
    if (nameid) calc_load(parseInt(nameid);
    
   $j('#member_name').on('change', nameid=parseInt($j('#member_name').val());
   $j('#Capital Return').on('change', calc_load(parseInt(nameid,parseInt($j('#Capital Return').val() )
});
In the php file change

Code: Select all

$id = makeSafe($_GET['id']);
to

Code: Select all

$id = makeSafe($_GET['id']);
$capital_return = makeSafe($_GET['cap']);
and

Code: Select all

$sum_capital=sqlValue("SELECT SUM(capital_return) FROM `loan_ledger` WHERE ID='{$id}'");
to

Code: Select all

$sum_capital=sqlValue("SELECT SUM(capital_return) FROM `loan_ledger` WHERE MEMBERNAME='{$id}'");
and remove

$capital_return=sqlValue("SELECT capital_return FROM `loan_ledger` WHERE ID='{$id}'";

and replace Capital Return and MEMBERNAME with your correct data.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
dnaorem
Posts: 19
Joined: 2018-10-16 16:06
Location: Imphal, Manipur, India
Contact:

Re: How to show pre-calculated value on form

Post by dnaorem » 2018-10-29 02:37

Dear sir,
You have done a great help, but unfortunately, it's not working. I have done all the required changing as below:

In ajax_calc_load.php
__________________________
<?php
$currDir = dirname(__FILE__);
include("$currDir/defaultLang.php");
include("$currDir/language.php");
include("$currDir/lib.php");

header('Content-type: text/javascript; charset=' . datalist_db_encoding);

$id = makeSafe($_GET['id']);
$capital_return = makeSafe($_GET['cap']);

if($id){
$loan_interest=sqlValue("SELECT loan_interest FROM `loan_account` WHERE ID='{$id}'");

$sum_capital=sqlValue("SELECT SUM(capital_return) FROM `loan_ledger` WHERE MEMBER_NAME='{$id}'");

$loan_amount=sqlValue("SELECT loan_amount FROM `loan_account` WHERE ID='{$id}'");

$loan_balance=sqlValue("SELECT loan_balance FROM `loan_account` WHERE ID='{$id}'");


?>

$j('#interest').val('<?php echo ($loan_interest * $loan_balance)/100; ?>');
$j('#amount_paid').val('<?php echo $capital_return + {($loan_interest * $loan_balance)/100}; ?>');
$j('#loan_balance').val('<?php echo $loan_amount - $sum_capital; ?>');
<?php
}
?>
________________________________________

and in loan_ledger-dv.js
_____________________
function calc_load(id) {

$j.ajax({
url: 'ajax_calc_load.php?&id='+id+'&cap='+cap,
success: function(res){}
});

}

$j(function() {
var member_nameid=parseInt($j('#member_name').val(); // if you need also the user input for Capital Return you need to expand the calls to retrieve this value and pass it to the ajax call
if (member_nameid) calc_load(parseInt(member_nameid);

$j('#member_name').on('change', calc_load(parseInt($j('#member_name').val());
$j('#capital_return').on('change', calc_load(parseInt(member_nameid,parseInt($j('#capital_return').val())
});
_____________________________________________________________________________________________

still can't auto display interest, amount paid and loan balance.

I am thinking to send you the whole application, so that, you can test it from there and solve. It will be grateful if you allow me sending you the project file.

Thanks and regards,
Deben
With regard,
Debenkumar Naorem

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: How to show pre-calculated value on form

Post by pbottcher » 2018-10-29 15:24

Hi,

please try ajax_calc_load.php

Code: Select all

<?php
	$currDir = dirname(__FILE__);
	include("$currDir/defaultLang.php");
	include("$currDir/language.php");
	include("$currDir/lib.php");

	header('Content-type: text/javascript; charset=' . datalist_db_encoding);

	$id = makeSafe($_GET['id']);
	$capital_return = makeSafe($_GET['cap']);

	if($id){
			$loan_interest=sqlValue("SELECT loan_interest FROM `loan_account` WHERE account_number='{$id}'");
			$sum_capital=sqlValue("SELECT SUM(capital_return) FROM `loan_ledger` WHERE member_name='{$id}'");
			$loan_amount=sqlValue("SELECT loan_amount FROM `loan_account` WHERE account_number='{$id}'");
			$loan_balance=sqlValue("SELECT loan_balance FROM `loan_account` WHERE account_number='{$id}'");
?>
			
			$j('#interest').val('<?php echo ($loan_interest * $loan_balance)/100; ?>');
			$j('#amount_paid').val('<?php echo $capital_return + (($loan_interest * $loan_balance)/100); ?>');
			$j('#loan_balance').val('<?php echo $loan_amount - $sum_capital; ?>');
<?php
	}
?>
and loan_ledger-dv.js

Code: Select all

$j(function() { 

var calc_load=function () {

    var id=parseInt($j('#member_name').val());   
    var cap= parseInt($j('#capital_return').val());
    if (id && cap) {
		$j.ajax({ 
			   url: 'ajax_calc_load.php?&id=' + id + '&cap=' + cap,
			   success: function(res){}
			});
    }
}

   $j('#member_name-container').on("change", calc_load);    
   $j('#capital_return').on('change', calc_load);

});
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
dnaorem
Posts: 19
Joined: 2018-10-16 16:06
Location: Imphal, Manipur, India
Contact:

Re: How to show pre-calculated value on form

Post by dnaorem » 2018-10-29 15:42

Yes sir,
It's fantastic, it is now working.

But a small mistake is as attached screenshot, that the loan balance is not the actual substraction from previous balance. This is because we have given the command to show (loan balance) - (sum of capital return) but before entering any data, there will be nothing to sum in the capital return. Let me add something in the calculation and let's see what happen again.

Thanks a lot for solving my problem.
Attachments
wrong_balance.png
wrong_balance.png (31.09 KiB) Viewed 5873 times
With regard,
Debenkumar Naorem

User avatar
dnaorem
Posts: 19
Joined: 2018-10-16 16:06
Location: Imphal, Manipur, India
Contact:

Re: How to show pre-calculated value on form

Post by dnaorem » 2018-10-29 15:47

I have changed from
$j('#loan_balance').val('<?php echo $loan_amount - $sum_capital; ?>');

into
$j('#loan_balance').val('<?php echo ($loan_amount - $sum_capital) - $capital_return; ?>'); and it's showing exact answer I want.

Thank you very much sir. You are great.
With regard,
Debenkumar Naorem

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: How to show pre-calculated value on form

Post by pbottcher » 2018-10-29 16:05

:P
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

User avatar
dnaorem
Posts: 19
Joined: 2018-10-16 16:06
Location: Imphal, Manipur, India
Contact:

Re: How to show pre-calculated value on form

Post by dnaorem » 2018-11-01 16:39

Hi Sir,
A new question arise in my mind again,
If I want to apply the same in Insurance ledger too, should I use the same ajax_calc_load.php and insurance_ledger-dv.js ?
I tried but not functioning. Please help me again.

with regard,
Debenkumar Naorem
With regard,
Debenkumar Naorem

User avatar
dnaorem
Posts: 19
Joined: 2018-10-16 16:06
Location: Imphal, Manipur, India
Contact:

Re: How to show pre-calculated value on form

Post by dnaorem » 2018-11-01 16:51

In insurance_ledger-dv.js

Code: Select all

$j(function() { 

var calc_load=function () {

    var id=parseInt($j('#member_name').val());   
 
    if (id && cap) {
		$j.ajax({ 
			   url: 'ajax_calc_load.php?&id=' + id,
			   success: function(res){}
			});
    }
}

   $j('#member_name-container').on("change", calc_load);    
   

});
and in ajax_calc_load.php I mixed with the previous code like this

Code: Select all

<?php
	$currDir = dirname(__FILE__);
	include("$currDir/defaultLang.php");
	include("$currDir/language.php");
	include("$currDir/lib.php");

	header('Content-type: text/javascript; charset=' . datalist_db_encoding);

	$id = makeSafe($_GET['id']);
	$capital_return = makeSafe($_GET['cap']);

	if($id){
			$loan_interest=sqlValue("SELECT loan_interest FROM `loan_account` WHERE account_number='{$id}'");
			$sum_capital=sqlValue("SELECT SUM(capital_return) FROM `loan_ledger` WHERE member_name='{$id}'");
			$loan_amount=sqlValue("SELECT loan_amount FROM `loan_account` WHERE account_number='{$id}'");
			$loan_balance=sqlValue("SELECT loan_balance FROM `loan_account` WHERE account_number='{$id}'");
			$sum_deposit=sqlvalue("SELECT sum(premium_deposit) FROM `insurance_ledger` where account_number='{$id}'");
			$sum_withdraw=sqlvalue("SELECT sum(premium_withdraw) FROM `insurance_ledger` where account_number='{$id}'");
			$premium_deposit=sqlValue("SELECT premium_deposit FROM `insurance_ledger` WHERE account_number='{$id}'");
?>
			
			$j('#interest').val('<?php echo ($loan_interest * $loan_balance)/100; ?>');
			$j('#amount_paid').val('<?php echo $capital_return + (($loan_interest * $loan_balance)/100); ?>');
			$j('#loan_balance').val('<?php echo ($loan_amount - $sum_capital) - $capital_return; ?>');
			$j('#balance').val('<?php echo ($sum_deposit + $premium_deposit) - $sum_withdraw; ?>');
<?php
	}
?>
I want the balance field in Insurance ledger form to be autofilled, but this can't help. But the sql works by putting in the hooks folder insurance_ledger before insert. That doesn't show while filling the form. I want this to as last time.
With regard,
Debenkumar Naorem

User avatar
dnaorem
Posts: 19
Joined: 2018-10-16 16:06
Location: Imphal, Manipur, India
Contact:

Re: How to show pre-calculated value on form

Post by dnaorem » 2018-11-01 16:57

Sir, it's working now.

I copied all the codes from loan_ledger-dv.js and edited as required, but forgot to remove &&cap in this line

Code: Select all

    if (id && cap) {
		$j.ajax({ 
			   url: 'ajax_calc_load.php?&id=' + id,
			   success: function(res){}
			});
With regard,
Debenkumar Naorem

Post Reply