Page 1 of 1

SUM OF COLUMN

Posted: 2019-07-23 14:21
by Moh Youba
Hello
Any easy solution to display total of
  • INCOME
    EXPENSE
    BALANCE
budgety.jpg
budgety.jpg (70.45 KiB) Viewed 4101 times
Thank you

Re: SUM OF COLUMN

Posted: 2019-07-23 15:05
by pbottcher
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.

Re: SUM OF COLUMN

Posted: 2019-07-23 17:26
by Moh Youba
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

Re: SUM OF COLUMN

Posted: 2019-07-23 18:08
by jsetzer
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

Re: SUM OF COLUMN

Posted: 2019-07-23 22:54
by Moh Youba
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,

Re: SUM OF COLUMN

Posted: 2019-07-29 00:46
by Moh Youba
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 4023 times

Re: SUM OF COLUMN

Posted: 2019-07-29 14:44
by pbottcher
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' ");

Re: SUM OF COLUMN

Posted: 2019-07-29 15:35
by Moh Youba
Hi
Thank you for comment, trying and back to you
Regards,

Re: SUM OF COLUMN

Posted: 2019-07-29 16:12
by Moh Youba
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 4002 times

Re: SUM OF COLUMN

Posted: 2019-08-13 11:25
by Moh Youba
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 3932 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 3932 times