I've been working on trying to add a "Days Open" column and "Days Overdue" column to the statement advanced PDF template in NetSuite. However, I keep running into issues with handling the date values sourced in from NetSuite records. Trying to manipulate the date values keep resulting in unspecified errors. I'm a bit baffled as to whether or not I can even make this work now.
For example, if I apply the following to the template, it works to format any date and date/time values entered into the template:
<#setting date_format="dd-MM-yyyy">
<#setting datetime_format="dd-MM-yyyy hh:mm a">
However, if I try to work with the values as date/datetime objects, it errors. So trying to do something like the following fails:
<#assign d2 = line.duedate?long>
While this works:
<#assign d1 = .now?date?long>
Also, trying to assume the value is actually a string being passed in and converting to a date/datetime also fails. Doing the following causes an error:
<#assign d2 = line.datecol?date("M/d/yyyy")> // format used by default in NetSuite date output
The other thing that made no sense was checking against the due date and displaying a value only if there was a due date (guard against data display for statement entries that were not actually invoices). So, even if a due date was set and displayed in the completed PDF, the following failed to show correct results:
<#if line.duedate?has_content>${daysoverdue}<#else>empty</#if>
Instead of showing what would be the overdue value (at this time just a static value for testing), it would display "empty". Regardless of whether a due date value was available or not, the entire column for every row would display as though the field were empty. So, I don't know if that's related or not.
I've been poking at this for a while, and haaven't found anything useful searching so far.
There are a few things going on here:
line.duedate does not always have a valid date value - IE: when the line is a Credit Memo, no due date applies. In this case, attempting to assign line.duedate?long to a variable will result in an error. To avoid this, you can check first to ensure it's valid.?has_content does not work for this as you would expect. It's difficult to determine exactly why this is, but there is a clue as to why it might be in the Freemarker documentation (emphasis added):has_content
It is true if the variable exists (and isn't Java null) and is not "empty", otherwise it is false. The meaning of "empty" depends on the concrete case. This follows intuitive common-sense ideas. The following are empty: a string with 0 length, a markup output value with 0 length markup, a sequence or hash with no sub variables, a collection which has passed the last element. If the value is not of any of these types, then it counts as non-empty if it's a number or a date or a boolean (e.g. 0 and false are not empty), otherwise it counts as empty. Note that when your data-model implements multiple template model interfaces you may get unexpected results. However, when in doubt you can use always use expr!?size > 0 or expr!?length > 0 instead of expr?has_content.
This buit-in is exceptional in that you can use the parentheses trick like with the default value operator. That is, you can write both product.color?has_content and (product.color)?has_content. The first doesn't handle the case when product is missing, the last does.
line.datecol?date("M/d/yyyy") will not work, as datecol, like duedate is recognized by Freemarker as date_like - not a string. Where empty, duedate is recognized as a string, but then of course doesn't match the format, being empty.daysoverdue doesn't appear to be available in the data model for the statement, however this can be calculated.To bring all these points together, you can firstly, check if the due date is valid, then calculate the days overdue:
<#if line.duedate?is_date_like>
<#assign d2 = line.duedate?long>
<#assign daysoverdue = ((.now?date?long - d2) / (24*60*60*1000))?floor>
<#else>
<#assign d2 = "">
<#assign daysoverdue = "">
</#if>
Then you can use ${daysoverdue} in one of your columns. You can follow a similar process to calculate and display the days open.