hooks - Conditional calculation & get system date

The recommended method of customizing your AppGini-generated application is through hooks. But sometimes you might need to add functionality not accessible through hooks. You can discuss this here.
Post Reply
Nicolas
Veteran Member
Posts: 43
Joined: 2015-12-08 08:08
Location: Barcelona
Contact:

hooks - Conditional calculation & get system date

Post by Nicolas » 2019-06-24 09:52

Hello all, with appgini 5.51

I have an invoicing database and I need to improve the "aging" of unpaid invoices and I am trying to write some code but it does not perform properly on a hook file assotitated to the invoice db.
this is the code I have and for which I would ask some help to correct it as it is not working. The idea is as follows:
There is a field Invoices.FechaPago wich has two possible date values:
01/01/1900 -> for any unpaid invoice
xx/xx/20xx -> we write the payment date for each invoice we have collected

function invoices_after_update($data, $memberInfo, &$args){
$data['T1'] = $data['C1'] * $data['V1'];
$data['T2'] = $data['C2'] * $data['V2'];
$data['T12'] = $data['T1'] + $data['T2'];
$data['T3'] = $data['C3'] * $data['V3'];
$data['T4'] = $data['C4'] * $data['V4'];
$data['T34'] = $data['T3'] + $data['T4'];
$data['I4Val'] = ($data['T34'] * $data['I4Porc'] /100);
$data['T5IVA'] = ($data['T34'] + $data['I4Val']);
$data['TotalFac'] = $data['T12'] + $data['T5IVA'];
$data['InvMarg100'] = (($data['T12'] + $data['T34'] -$data['InvMargen']) / ($data['T12']+$data['T34'])) * 100 ;
if (('td.invoices-FechaPago') == '01/01/1900'){
UpdateAgingNotPaid ("");
}
else{
UpdateAgingPaid ("");
};

Year("");
Month("");
Quarter("");

return TRUE;
}

Then, with the code what we wanto to have is:
1.- If the invoice is unpaid (Invoices.fechaPago == 01/01/1900 - I want to call the function UpdateAgingNotPaid so I get on field InvDiasPdte the number of days the given invoice (record) is due. calculating the system date and Invoice date
But I need to call a SQL system date to make the calculation... and I don't know if the right value can be obtained from `Now()`

function UpdateAgingNotPaid($where = ''){
$sql = "update `invoices` set `InvDiasPdte` = datediff(`Now()`,`FFactura`)";
if($where) $sql .= "where $where";
sql($sql, $eo);
};


2.- if the invoice is paid then the function calculates the difference between payment date (FechaPago) and Invoice date (FFactura)
function UpdateAgingPaid($where = ''){
$sql = "update `invoices` set `InvDiasPdte` = datediff(`FechaPago`,`FFactura`)";
if($where) $sql .= "where $where";
sql($sql, $eo);
};

I may need help on the IF statement and the systemdate comparison function (both in black and Italic), which seem not to be working

Thank you for sharing the knowledge!!!!
Nicolas

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

Re: hooks - Conditional calculation & get system date

Post by pbottcher » 2019-06-24 11:09

Hi,

what DB-type is your Invoices.FechaPago field? DATE?

As you would probably have a date in you MySQL DB = 1900-01-01 I would think that you could try :

Code: Select all

// get date for the record: 
$act_date = sqlvalue ("SELECT FechaPago from invoices where ID = ".$data['selectedID']);  // you need to check if ID is your pk and if you are case sensitiv, you may need to adjust the fieldname
if ($act_date == "1900-01-01") {
UpdateAgingNotPaid ($data['selectedID']);
}
else{
UpdateAgingPaid ($data['selectedID']);
};

Code: Select all

function UpdateAgingPaid($where = ''){
$sql = "update `invoices` set `InvDiasPdte` = datediff(`FechaPago`,`FFactura`)";
if($where) $sql .= "where ID=".$where;
sql($sql, $eo);
};
Assuming that you have set the FFactura field already.
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.

Nicolas
Veteran Member
Posts: 43
Joined: 2015-12-08 08:08
Location: Barcelona
Contact:

Re: hooks - Conditional calculation & get system date

Post by Nicolas » 2019-06-24 12:51

Yes, Invoices.FechaPago field? DATE? is a date field on the DB.

I'll try and come back. Thank you so much!!

Nicolas
Veteran Member
Posts: 43
Joined: 2015-12-08 08:08
Location: Barcelona
Contact:

Re: hooks - Conditional calculation & get system date

Post by Nicolas » 2019-06-25 07:14

Dear,

I've tried my formula again and I think that what fails is this function
function UpdateAgingNotPaid($where = ''){
$sql = "update `invoices` set `InvDiasPdte` = datediff(`Now()`,`FFactura`)";
if($where) $sql .= "where $where";
sql($sql, $eo);
};

as I am using the Now() inside the datediff and maybe I should use a temporary variable to hold current date (which I may use also Curdate() to get system date) but no idea on how to set a temporary variable which is not a field on the DB....Any hint???
Many thanks

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

Re: hooks - Conditional calculation & get system date

Post by pbottcher » 2019-06-27 06:52

Hi,
yes use curdate().

$sql = "update `invoices` set `InvDiasPdte` = datediff(curdate(),`FFactura`)";
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.

Nicolas
Veteran Member
Posts: 43
Joined: 2015-12-08 08:08
Location: Barcelona
Contact:

Re: hooks - Conditional calculation & get system date

Post by Nicolas » 2019-06-28 06:46

I've tried and does not work the first part of the if statement... it always do the ELSE. there must be a silly issue when comparing
function invoices_after_insert($data, $memberInfo, &$args){
$data['T1'] = $data['C1'] * $data['V1'];
$data['T2'] = $data['C2'] * $data['V2'];
$data['T12'] = $data['T1'] + $data['T2'];
$data['T3'] = $data['C3'] * $data['V3'];
$data['T4'] = $data['C4'] * $data['V4'];
$data['T34'] = $data['T3'] + $data['T4'];
$data['I4Val'] = ($data['T34'] * $data['I4Porc'] / 100);
$data['T5IVA'] = ($data['T34'] + $data['I4Val']);
$data['TotalFac'] = $data['T12'] + $data['T5IVA'];
$data['InvMarg100'] = (($data['T12'] + $data['T34'] -$data['InvMargen']) / ($data['T12']+$data['T34'])) * 100 ;

if (('td.invoices-FechaPago') == '01/01/1900'){
UpdateDiasSinPagar ("");
}
else{
UpdateDiasPagada ("");
};

Year("");
Month("");
Quarter("");

return TRUE;
}

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

Re: hooks - Conditional calculation & get system date

Post by pbottcher » 2019-06-28 10:03

Hi,

have a look at the code I posted above. The way your IF is set-up will not work.
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.

Nicolas
Veteran Member
Posts: 43
Joined: 2015-12-08 08:08
Location: Barcelona
Contact:

Re: hooks - Conditional calculation & get system date

Post by Nicolas » 2019-06-29 10:32

Hi, I've used you code and it seems it works, but does the calculation for all invoices, and not only the selected record. If I take an unpaid record, then all invoices take the aging from today. and if I take a paid invoice, then all are calculated as paid
The new code is the same for before_insert,after_insert, before_update, maybe I should remove the before insert?

Thank you
invoices_before_insert(&$data, $memberInfo, &$args){
$data['T1'] = $data['C1'] * $data['V1'];
$data['T2'] = $data['C2'] * $data['V2'];
$data['T12'] = $data['T1'] + $data['T2'];
$data['T3'] = $data['C3'] * $data['V3'];
$data['T4'] = $data['C4'] * $data['V4'];
$data['T34'] = $data['T3'] + $data['T4'];
$data['I4Val'] = ($data['T34'] * $data['I4Porc'] / 100);
$data['T5IVA'] = ($data['T34'] + $data['I4Val']);
$data['TotalFac'] = $data['T12'] + $data['T5IVA'];
$data['InvMarg100'] = (($data['T12'] + $data['T34'] -$data['InvMargen']) / ($data['T12']+$data['T34'])) * 100 ;

$act_date = sqlvalue ("SELECT FechaPago from invoices where InvoiceID = ".$data['FechaPago']); // you need to check if ID is your pk and if you are case sensitiv, you may need to adjust the fieldname
if ($act_date == "1900-01-01") {
UpdateDiasSinPagar ("");
}
else{
UpdateDiasPagada ("");
};

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

Re: hooks - Conditional calculation & get system date

Post by pbottcher » 2019-06-29 11:49

Hi,

if you look in the code I provided, you see

if ($act_date == "1900-01-01") {
UpdateAgingNotPaid ($data['selectedID']);
}
else{
UpdateAgingPaid ($data['selectedID']);
};

This will be used for the actual record you are working on.

Also make sure your where clause is correct (see post), note you might need an extra space to create the correct SQL statement.

if($where) $sql .= " where ID=".$where;
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.

Nicolas
Veteran Member
Posts: 43
Joined: 2015-12-08 08:08
Location: Barcelona
Contact:

Re: hooks - Conditional calculation & get system date

Post by Nicolas » 2019-06-30 10:29

Hi there, and congratulations!!!

Now all works smoothly and great!!! it does what expected and only for the modified element...
I am now going to play to update automatically only the ones which are unpaid.... as these are the ones changing the aging days.... the paid invoices do not need to change.
But thank you for all the support and right coding!!!
this forum and members is great!

tminh
Posts: 28
Joined: 2019-05-23 02:26

Re: hooks - Conditional calculation & get system date

Post by tminh » 2019-07-13 09:26

Nicolas wrote:
2019-06-30 10:29
Hi there, and congratulations!!!

Now all works smoothly and great!!! it does what expected and only for the modified element...
I am now going to play to update automatically only the ones which are unpaid.... as these are the ones changing the aging days.... the paid invoices do not need to change.
But thank you for all the support and right coding!!!
this forum and members is great!
can you post your full working code here ??

Nicolas
Veteran Member
Posts: 43
Joined: 2015-12-08 08:08
Location: Barcelona
Contact:

Re: hooks - Conditional calculation & get system date

Post by Nicolas » 2020-02-08 15:17

Hello, sorry I've been out of programming for a long time,. Here you have. Best regards
function invoices_after_update($data, $memberInfo, &$args){
$data['T1'] = $data['C1'] * $data['V1'];
$data['T2'] = $data['C2'] * $data['V2'];
$data['T12'] = $data['T1'] + $data['T2'];
$data['T3'] = $data['C3'] * $data['V3'];
$data['T4'] = $data['C4'] * $data['V4'];
$data['T34'] = $data['T3'] + $data['T4'];
$data['I4Val'] = ($data['T34'] * $data['I4Porc'] /100);
$data['T5IVA'] = ($data['T34'] + $data['I4Val']);
$data['TotalFac'] = $data['T12'] + $data['T5IVA'];
$data['InvMarg100'] = (($data['T12'] + $data['T34'] -$data['InvMargen']) / ($data['T12']+$data['T34'])) * 100 ;

$act_date = sqlvalue ("SELECT FechaPago from invoices where InvoiceID = ".$data['selectedID']); // you need to check if ID is your pk and if you are case sensitiv, you may need to adjust the fieldname
if ($act_date == "1900-01-01") {
UpdateDiasSinPagar ($data['selectedID']);
}
else{
UpdateDiasPagada ($data['selectedID']);
};

Year("");
Month("");
Quarter("");

return TRUE;
}

function invoices_before_delete($selectedID, &$skipChecks, $memberInfo, &$args){

return TRUE;
}

function invoices_after_delete($selectedID, $memberInfo, &$args){

}

function invoices_dv($selectedID, $memberInfo, &$html, &$args){
function(){
$j('#T1').prop('readonly', true);
};
}

function invoices_csv($query, $memberInfo, &$args){

return $query;
}
function invoices_batch_actions(&$args){

return array();
}
function Year($where = ''){
$sql = "update `invoices` set `InvAno` = YEAR(`FFactura`)";
if($where) $sql .= "where $where";
sql($sql, $eo);
};
function Month($where = ''){
$sql = "update `invoices` set `InvMes` = MONTH(`FFactura`)";
if($where) $sql .= "where $where";
sql($sql, $eo);
};
function Quarter($where = ''){
$sql = "update `invoices` set `InvTrim` = QUARTER(`FFactura`)";
if($where) $sql .= "where $where";
sql($sql, $eo);
};
function UpdateDiasSinPagar($where = ''){
$sql = "update `invoices` set `InvDiasPdte` = datediff(`FFactura`,curdate())";
if($where) $sql .= " where InvoiceID=".$where;
sql($sql, $eo);
};

function UpdateDiasPagada($where = ''){
$sql = "update `invoices` set `InvDiasPdte` = datediff(`FechaPago`,`FFactura`)";
if($where) $sql .= " where InvoiceID=".$where; /* if($where) $sql .= "where $where"; */
sql($sql, $eo);
};

?>

Post Reply