Advanced PDF Template for Customer Statements: Calculating Date Differences for Invoice Aging

This FreeMarker script is designed to categorize overdue invoices on a customer statement by calculating the number of days between the invoice due date and the statement date. Here’s a concise breakdown of the process:

1. Extract and Convert Dates:

• The script retrieves the due date (line.duedate) and statement date (statement.trandate), converting them into strings.

1. Check Date Content:

• It ensures both dates are present before proceeding.

1. Calculate Difference in Days:

• The dates are formatted as “MM/dd/yyyy”. The difference in days is calculated by converting these dates to milliseconds, dividing by 86400000 (milliseconds in a day), and rounding the result.

1. Categorize Aging:

• The script assigns a category based on the number of days overdue:
• “1 to 30 days”
• “31 to 60 days”
• “61 to 90 days”
• “91 and over”
• “Current” (if the invoice is not overdue or dates are invalid)

1. Handle Missing Data:

• If the dates are missing or invalid, the script defaults to “Current”.

This automated categorization helps businesses easily identify and manage overdue invoices, facilitating efficient financial management.

<#assign due = line.duedate/>
<#assign Date = statement.trandate/>
<#assign duestr = due?string/>
<#assign Datestr = Date?string/>
  
  <#if (duestr?has_content) && (Datestr?has_content) >
       <#assign date1 =  (duestr)?date("MM/dd/yyyy") />
       <#assign date2 = (Datestr)?date("MM/dd/yyyy") />
       <#assign difference = (date2?long / 86400000)?round - (date1?long / 86400000)?round />
          
      <!--llllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllllll-->
    <#-- Set the custom description based on the difference value -->
    <#if difference?is_number>
       <!-- Set the custom description based on the difference value -->
        <#if (difference >= 1) && (difference <= 30)>
            <#assign customDescription = "1 to 30 days" />
        <#elseif (difference > 30) && (difference <= 60)>
            <#assign customDescription = "31 to 60 days" />
        <#elseif (difference > 60) && (difference <= 90)>
            <#assign customDescription = "61 to 90 days" />
        <#elseif (difference >= 91)>
            <#assign customDescription = "91 and over" />
        <#else>
            <#assign customDescription = "Current" />
        </#if>
    <#else>
        <#assign customDescription = "Current" />
    </#if>
     
  <#else>
        <#assign difference =""/>
           <#assign customDescription = "Current" />
  </#if>