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

## hooks - Conditional calculation & get system date

### Re: hooks - Conditional calculation & get system date

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 :

Assuming that you have set the FFactura field already.

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);
};
```

### Re: hooks - Conditional calculation & get system date

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

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

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

### Re: hooks - Conditional calculation & get system date

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

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

### Re: hooks - Conditional calculation & get system date

Hi,

yes use curdate().

$sql = "update `invoices` set `InvDiasPdte` = datediff(curdate(),`FFactura`)";

yes use curdate().

$sql = "update `invoices` set `InvDiasPdte` = datediff(curdate(),`FFactura`)";

### Re: hooks - Conditional calculation & get system date

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;

}

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;

}

### Re: hooks - Conditional calculation & get system date

Hi,

have a look at the code I posted above. The way your IF is set-up will not work.

have a look at the code I posted above. The way your IF is set-up will not work.

### Re: hooks - Conditional calculation & get system date

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 ("");

};

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 ("");

};

### Re: hooks - Conditional calculation & get system date

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;

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;

### Re: hooks - Conditional calculation & get system date

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!

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!

### Re: hooks - Conditional calculation & get system date

can you post your full working code here ??Nicolas wrote: ↑2019-06-30 10:29Hi 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!

### Re: hooks - Conditional calculation & get system date

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);

};

?>

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);

};

?>