SUM OF COLUMN

Discussions related to customizing hooks. Hooks are documented at http://bigprof.com/appgini/help/advanced-topics/hooks/
Post Reply
Moh Youba
Veteran Member
Posts: 228
Joined: 2017-03-12 09:31

SUM OF COLUMN

Post by Moh Youba » 2019-07-23 14:21

Hello
Any easy solution to display total of
  • INCOME
    EXPENSE
    BALANCE
budgety.jpg
budgety.jpg (70.45 KiB) Viewed 4090 times
Thank you

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

Re: SUM OF COLUMN

Post by pbottcher » 2019-07-23 15:05

Hi,
can you explain a little more what you want to acheive?

If I understand what this picture shall explain, you would either need to the income expense budget data from your database, by setting the values, whenever you add/modify/delete an entry in the income / expense table.

Otherwise you may run set the values through the hooks/ detailview function by retiieve the 3 values from the database for the client and setting the data via jquery.
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.

Moh Youba
Veteran Member
Posts: 228
Joined: 2017-03-12 09:31

Re: SUM OF COLUMN

Post by Moh Youba » 2019-07-23 17:26

Hi
as you can see, I have 3 tables
I want to display the total of amount column for table "incomes" and "expenses" on the table clients and then calculate the balance

sorry for my very bad English

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: SUM OF COLUMN

Post by jsetzer » 2019-07-23 18:08

If I understand right:

On ...
  • insert
  • update or
  • delete
...of...
  • incomes or
  • expenses
...you'd like to update the fields...
  • Total income,
  • Total expense and
  • Balance
of the master record, right?

Of cause there are multiple ways to achieve this. You can do it this way:
  1. create a function budgets_update($id) { ... } which you can call from hooks/expenses.php and from hooks/income.php. It's useful to put it in a separate .php-file which then can be included in both hooks-files.
  2. call that function from expenses_after_insert, expenses_after_update and expenses_before_delete...
  3. call that function from incomes_after_insert, incomes_after_update and incomes_before_delete...
  4. and always pass the client's id, which should be $selectedID or $data["client_id"] or something like this.
  5. in your budgets_update-function calculate the sum of amount from income and the sum of amount from expenses using AppGini's sqlValue()-function, for example:
    $total_income = sqlValue("select coalesce(sum(amount), 0) from incomes where client_id='{$id}'");
    $total_expenses = sqlValue("select coalesce(sum(amount), 0) from expenses where client_id='{$id}'");
  6. calculate the balance by total_income - total_expenses, for example:
    $balance = $total_income - $total_expenses;
  7. set totals and balance on your budgets-table using AppGini's sql()-function, for example
    $sql = "update balances set total_income={$total_income}, total_expensed={$total_expenses}, balance={$balance} WHERE id={$id}";
    sql($sql, $eo);
That't it. This should be a good starting point for you.

Regards,
Jan
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

Moh Youba
Veteran Member
Posts: 228
Joined: 2017-03-12 09:31

Re: SUM OF COLUMN

Post by Moh Youba » 2019-07-23 22:54

Hello Jan
Thank you for your support, this is a good lesson for me. I am going to practice and back to you with results.

Best regards,

Moh Youba
Veteran Member
Posts: 228
Joined: 2017-03-12 09:31

Re: SUM OF COLUMN

Post by Moh Youba » 2019-07-29 00:46

Hello, here what I have done for now, but still have some error

sorties.php
function sorties_after_insert($data, $memberInfo, &$args){

$id = makeSafe($data['matricule']);
$tots = sqlValue("SELECT SUM(total_sortie) FROM sorties WHERE matricule='$id' ");
sql("UPDATE sorties SET total_tous_sortie = '$tots' WHERE matricule='$id'", $eo);

return TRUE;
}

sorties-dv.js
$j(function(){
/* set montant total entree field to read only*/
$j('#total_sortie').prop('readonly', true);

/* calcul du champs total entree */
$j('#qts_sortie, #montant_sortie, #total_tous_sortie, #total_sortie').change(function(){
var quantity = parseFloat($j('#qts_sortie').val()) || 0;
var unitPrice = parseFloat($j('#montant_sortie').val()) || 0;

var totalPrice = quantity * unitPrice;
$j('#total_sortie').val(totalPrice);
});

/* calculate total price on opening the detail view form*/
$j('#montant_sortie').change();

})

entrees.php
function entrees_after_insert($data, $memberInfo, &$args){

$id = makeSafe($data['matricule']);
$tote = sqlValue("SELECT SUM(total_entree) FROM entrees WHERE matricule='$id' ");
sql("UPDATE entrees SET total_tous_entree = '$tote' WHERE matricule='$id'", $eo);

return TRUE;
}


entrees-dv.js
$j(function(){
/* set montant total entree field to read only*/
$j('#total_entree').prop('readonly', true);

/* calcul du champs total entree */
$j('#qts_entree, #montant_entree, #total_tous_entree, #total_entree').change(function(){
var quantity = parseFloat($j('#qts_entree').val()) || 0;
var unitPrice = parseFloat($j('#montant_entree').val()) || 0;

var totalPrice = quantity * unitPrice;
$j('#total_entree').val(totalPrice);
});

/* calculate total price on opening the detail view form*/
$j('#montant_entree').change();

})


clients.php
function clients_after_insert($data, $memberInfo, &$args){

$id = makeSafe($data['selectedID']);
$idclient = makeSafe($data['matricule']);

$total_entrees = sqlValue("SELECT total_tous_entree FROM entrees WHERE matricule = '$id' ");
$total_sorties = sqlValue("SELECT total_tous_sortie FROM sorties WHERE matricule = '$id' ");

$soldes = ($total_entrees - $total_sorties); // calcul des benefice

sql("UPDATE clients SET total_entree ='$total_entrees' WHERE idclient ='$id' ", $eo);

sql("UPDATE clients SET total_sortie ='$total_sorties' WHERE idclient ='$id' ", $eo);

sql("UPDATE clients SET solde ='$soldes' WHERE idclient ='$id' ", $eo);

return TRUE;
}


but when I try to add a client from clients table, I get this error message

Incorrect decimal value: '' for column 'total_sortie' at row 1
Recherche:
UPDATE clients SET total_sortie ='' WHERE idclient ='4'
The above info is displayed because you are currently signed in as the super admin. Other users won't see this.

You could try fixing this issue from the Admin Area : Utilities menu : Rebuild Fields.

Any help is welcome
Thank you
tables.jpg
tables.jpg (39.52 KiB) Viewed 4012 times

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

Re: SUM OF COLUMN

Post by pbottcher » 2019-07-29 14:44

Hi,

could it be that the values you try to retrieve from the table entrees ond/or sorties do not exist yet?

If so, try the SQL with

$total_entrees = sqlValue("SELECT IFNULL(total_tous_entree,0) FROM entrees WHERE matricule = '$id' ");
$total_sorties = sqlValue("SELECT IFNULL(total_tous_sortie,0) FROM sorties WHERE matricule = '$id' ");
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.

Moh Youba
Veteran Member
Posts: 228
Joined: 2017-03-12 09:31

Re: SUM OF COLUMN

Post by Moh Youba » 2019-07-29 15:35

Hi
Thank you for comment, trying and back to you
Regards,

Moh Youba
Veteran Member
Posts: 228
Joined: 2017-03-12 09:31

Re: SUM OF COLUMN

Post by Moh Youba » 2019-07-29 16:12

OK, try your code and now there is no error message
function clients_after_insert($data, $memberInfo, &$args){

$id = makeSafe($data['selectedID']);
$idclient = makeSafe($data['matricule']);

$total_entrees = sqlValue("SELECT IFNULL(total_tous_entree,0) FROM entrees WHERE matricule = '$id' ");
$total_sorties = sqlValue("SELECT IFNULL(total_tous_sortie,0) FROM sorties WHERE matricule = '$id' ");

$soldes = ($total_entrees - $total_sorties); // calcul des benefice

sql("UPDATE clients SET total_entree ='$total_entrees' WHERE matricule ='$id' ", $eo);

sql("UPDATE clients SET total_sortie ='$total_sorties' WHERE matricule ='$id' ", $eo);

sql("UPDATE clients SET solde ='$soldes' WHERE matricule ='$id' ", $eo);

return TRUE;
}

but the probleme now, in "clients" table, no data is showing
clients.jpg
clients.jpg (58.09 KiB) Viewed 3991 times

Moh Youba
Veteran Member
Posts: 228
Joined: 2017-03-12 09:31

Re: SUM OF COLUMN

Post by Moh Youba » 2019-08-13 11:25

Hello guys, I hope you all are doing well !

Please have a look on this and advice. Is it good practice to work on dml file instead of hooks

This solve my problem, but am not sure if it is good practice.

Thank you
Screen Shot 2019-08-13 at 11.22.04 AM.png
Screen Shot 2019-08-13 at 11.22.04 AM.png (166.86 KiB) Viewed 3921 times
Screen Shot 2019-08-13 at 11.21.51 AM.png
Screen Shot 2019-08-13 at 11.21.51 AM.png (155.58 KiB) Viewed 3921 times

Post Reply