In PHPMaker (v2019) i have the following need:
Generate a caption for an column which contains a dynamic part - the year. Currently I'm using the system-date with following code:
$this->COLUMN_IN_DATABASE ->Caption .= "MyColumnName " . (date("Y") + 0)
This works finde but i need change this to an value in the database. There is an column which contains the year and an month like this:
YYYY-MM
Therefore I need to extract the YYYY and use it in the caption.
Any ideas?
The solution is:
Put the following code in the top of "PageLoad" event:
($YEAR = ExecuteScalar ("select DISTINCT (CAST((SUBSTR(YEARANDMONTH,0,4)) AS NUMBER(4))) YEAR FROM TABLE WHERE YEARANDMONTH = (SELECT MAX(YEARANDMONTH) FROM TABLE )"));
($MONTHNUM= ExecuteScalar ("select DISTINCT (CAST((SUBSTR(YEARANDMONTH,6,2)) AS NUMBER(4))) MONTHNUMBER from TABLE WHERE YEARANDMONTH= (SELECT MAX(YEARANDMONTH) FROM TABLE )"));
Then, after that, you can use the results in every column caption of your choice like this: (We assume the result of the above mentioned SQL-result is "2021" as $YEAR and "5" as $MONTHNUM)
$this->YEAR2 ->Caption .= "In two Years it will be the year " . (($YEAR)+2) . " and the current month number is " . ($MONTHNUM);
Then the caption if the "YEAR2"-column will be: "In two Years it will be the year 2023 and the month number is 5"
Extra: In my case I had in some columns the need to display the full month name based on the month number. So i have added the third line of code in the top section:
($MONTHNAME = ["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"]);
And to display the month name the code is like this: (We assume the result of the above mentioned SQL-result is "2021" as $YEAR and "5" as $MONTHNUM)
$this->YEAR2 ->Caption .= "In two Years it will be the year " . (($YEAR)+2) . " and the month name will be " . ($MONTHNAME[$MONTHNUM-1]);
Then the caption if the "YEAR2"-column will be: "In two Years it will be the year 2023 and the month name will be May"