This topic is locked
[SOLVED]

Query on Date fields in Field Events

1/26/2023 8:00:32 AM
PHPRunner General questions
H
Henny Sol author

When working on FieldEvents I stumbled on a problem when I had to query in the OnServer part on a Date field (facd) that I "took" from the current record in the ClientBefore part.

I wanted to calculate the netto amount (nett) and the high/low vat amount (btwh/btwl) from the entered bruto amount (brut). I picked all fields I needed in the ClientBefore.

Then I had to find the VATperc (perc) of the VATtype (btws) in the VATpercentages table (BtwPerc) that I had to use given the documentdate/invoicedate (facd) of the cost.
Since VATpercentages do sometimes change my BtwPerc table contains the fields btws (Vattype), btwi (StartDate), btwv (EndDate, btwp (Percentage).

When the facd on the page was 19-1-2023 the facd that I picked in ClientBefore was 18-01-2023 23:00:00. After various attemtps my code worked OK by taking the 10 first chars of facd, creating a date, adding 1 day to the date and converting it to a string.

I'm a newbie at Javascript/PHP so I would like to know if there is a more elegant way to deal with Date fields in the OnServer part.

Below you'll find my coding:

ClientBefore:
var ctrlBtws = ctrl.getPeer("btws");
var ctrlFacd = ctrl.getPeer("facd");
params["btws"] = ctrlBtws.getValue();
params["facd"] = ctrlFacd.getValue();
params["brut"] = ctrl.getValue();
// Uncomment the following line to skip "Server" and "Client After" steps.
// return false;

OnServer:
$tmpd = substr($params["facd"],0,10);
$dto = new DateTime($tmpd);
$dto->modify('+1 day');
$facd = $dto->format('Y-m-d');

$sql = "select from BtwPerc where btws=".$params["btws"]." and (";
$sql = $sql."(btwi<='".$facd."' and btwv IS NULL ) or ";
$sql = $sql."(btwi<='".$facd."' and btwv>='".$facd."'))";
//echo $sql;
$rs = DB::Query($sql);
$data=$rs->fetchAssoc();
$result["perc"] = $data["btwp"];
$result["brut"] = $params["brut"];
$result["btws"] = $params["btws"];
$result["nett"] = round(100
$params["brut"]/(100+$result["perc"]),2,PHP_ROUND_HALF_UP);
if ($result["btws"] == 1) {
//Algemeen
$result["btwh"] = round($params["brut"]-$result["nett"],2,PHP_ROUND_HALF_UP);
$result["btwl"] = 0;
}
if ($result["btws"] == 3) {
//Verlaagd
$result["btwl"] = round($params["brut"]-$result["nett"],2,PHP_ROUND_HALF_UP);
$result["btwh"] = 0;
}

ClientAfter:
var ctrlBtwh = ctrl.getPeer("btwh");
var ctrlBtwl = ctrl.getPeer("btwl");
var ctrlNett = ctrl.getPeer("nett");
// Sample:
ctrlNett.setValue(result["nett"]);
ctrlBtwh.setValue(result["btwh"]);
ctrlBtwl.setValue(result["btwl"]);

admin 1/27/2023

You are using a wrong API function. If you need to pass date values to the server here is the API you need to use:
https://xlinesoft.com/phprunner/docs/getstringvalue.htm