DateUtils error

Classic Servoy
1

Hi there,

we use the DateUtils solution inside our solution, to calculate weeknumbers,
so far so good.

But most of the time, when we open our solution, and the weeknumber is touched in on of our calculations, we get this error:

jun 06, 2018 12:12:33 PM com.servoy.j2db.util.Debug error
SEVERE: error executing calc: weeknumber
java.lang.RuntimeException: Exceptie bij het uitvoeren van berekening: weeknumber van tabel quotes, fout org.mozilla.javascript.EcmaError: ReferenceError: "calendar" is not defined. (core_DirectManager/scopes/DateUtils/getWeekOfYear#14)
	at com.servoy.j2db.scripting.TableScope.getCalculationValue(TableScope.java:193)
	at com.servoy.j2db.scripting.TableScope.getCalculationValue(TableScope.java:215)
	at com.servoy.j2db.dataprocessing.FoundSet.getCalculationValue(FoundSet.java:3403)
	at com.servoy.j2db.dataprocessing.Record.getValue(Record.java:206)
	at com.servoy.j2db.dataprocessing.Record.getValue(Record.java:169)
	at com.servoy.j2db.smart.dataui.CellAdapter$5.run(CellAdapter.java:1185)
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
	at java.lang.Thread.run(Unknown Source)
Caused by: org.mozilla.javascript.EcmaError: ReferenceError: "calendar" is not defined. (core_DirectManager/scopes/DateUtils/getWeekOfYear#14)

if we look this up inside the DateUtils scope, we see this:

/**
 * A java.util.Calendar instance used to do the math
 * @private
 * @properties={typeid:35,uuid:"6FEE1C0C-ECD0-4E98-A5BA-5420B77D0917",variableType:-4}
 */
var calendar = java.util.Calendar.getInstance();



/**
 * @properties={typeid:24,uuid:"A92A0657-35C4-4ECC-8F5C-8A7D2C5C0CBF"}
 */
function getWeekOfYear(date) {
	calendar.setTimeInMillis(date.getTime());
	return calendar.get(java.util.Calendar.WEEK_OF_YEAR);
}

So somehow, the first time, var calendar is not initialized somehow…
How do we make sure, that this global scope is initialized before we touch a function inside a calculation?

2

we still have this same issue…
Does somebody have the solution??

3

Hi Harjo,

I’ve seen the error just once, but it disappeared after restart.
Does it help when you reference “scopes.svyDateUtils” in your onOpenSolution function?
That should just touch the scope without actually executing a function.

4

Harjo:
weeknumber is touched in one of our calculations

Do you mean a table calculation?

In case there is no solution, I would do a workaround, like creating a new field for the weeknumber in the table, always updating that field when the date changes, and then using that field in the calculation instead of the function that does not work on startup.

Violating Codd’s normal form is needed sometimes… :)

5

Hi Harjo,

Seems that the scoped variable gets garbage collected somehow. Maybe add some sanity checking like so?

/**
* A java.util.Calendar instance used to do the math
* @private
* @properties={typeid:35,uuid:"6FEE1C0C-ECD0-4E98-A5BA-5420B77D0917",variableType:-4}
*/
var calendar = java.util.Calendar.getInstance();


/**
* @properties={typeid:24,uuid:"A92A0657-35C4-4ECC-8F5C-8A7D2C5C0CBF"}
*/
function getWeekOfYear(date) {
   if (!calendar) {
      calendar = java.util.Calendar.getInstance();
   }
   calendar.setTimeInMillis(date.getTime());
   return calendar.get(java.util.Calendar.WEEK_OF_YEAR);
}

Another (fast) way of handeling time functions is using a time dimension table.
Create a table with a date column as the PK, then add columns for year, month, day, weekday, weeknr, quarteryr, halfyr, etc… and fill it up with data for 2 centuries (1900-2100) which should cover most use-cases (adjust as needed). This will only be 73048 records. Now you only have to join against the date column and you get all the other values in the row without calculating them.
This (data warehousing) technique is also extremely fast with reporting. You just join over the dates and filter your result over the time_dimension columns.

How to create that data? PostgreSQL has a handy function called generate_series. This can generate rows with a lot of different data types. In this case you want to generate timestamps (which we convert to dates), like so:

SELECT CAST(dateval AS date) date
    , extract('year' from dateval) as year
    , extract('month' from dateval) as month
    , extract('day' from dateval) as day
    , extract('isodow' from dateval) as weekday -- ISO 8601 day of the week numbering (starts with monday) 
    , extract('week' from dateval) as weeknr -- ISO 8601 week numbering
    , extract('quarter' from dateval) as quarter_yr
    , ceil(extract('quarter' from dateval)/2) as half_yr
    , extract('doy' from dateval) as day_of_year
    -- add your own columns like accounting calendars like 445, etc.
FROM generate_series('1900-01-01'::timestamptz,
    '2099-12-31'::timestamptz,
    '1 day'
) dateval

We could create a table manually and then insert the data but there is a faster way to do all this in one SQL query:

CREATE TABLE time_dimension AS (
    SELECT CAST(dateval AS date) date
        , extract('year' from dateval) as year
        , extract('month' from dateval) as month
        , extract('day' from dateval) as day
        , extract('isodow' from dateval) as weekday -- ISO 8601 day of the week numbering (starts with monday) 
        , extract('week' from dateval) as weeknr -- ISO 8601 week numbering
        , extract('quarter' from dateval) as quarter_yr
        , ceil(extract('quarter' from dateval)/2) as half_yr
        , extract('doy' from dateval) as day_of_year
        -- add your own columns like accounting calendars like 445, etc.
    FROM generate_series('1900-01-01'::timestamptz,
        '2099-12-31'::timestamptz,
        '1 day'
    ) dateval
)

However this doesn’t create the PK on the date column so we need to add it like so:

ALTER TABLE time_dimension ADD CONSTRAINT time_dimension_pk PRIMARY KEY(date)

You can of course add extra indexes as needed. As this is a static lookup table there is no real ‘cost’ here other than disk space.

Hope either solution helps.

6

mboegem:
Hi Harjo,

I’ve seen the error just once, but it disappeared after restart.
Does it help when you reference “scopes.svyDateUtils” in your onOpenSolution function?
That should just touch the scope without actually executing a function.

I have put just a line in th onSolutionOpen method: scopes.svyDateUtils
and this fixes the issue!

Still a bit strange, because Servoy should ‘do’ this automaticly.

7

Seems like a loading order issue.
Maybe Johan can comment on this.

Otherwise create a JIRA ticket so Servoy can look into this.

For now it’s good to see it being resolved although only while using a workaround

8

As a workaround you can also use this function I wrote as a part of the VFP (Visual FoxPro) plugin. Hope it helps.

/**
* Returns the week number for the date parameter dDate.
* It supports both ISO 8601 and USA/Canada week numbering. 
* 
* @param {Date} dDate
* @param {Number} nOverruleFirstDayOfWeek (optional) - 0=Sunday 1=Monday 
* @return {Number} number
* @author Omar van Galen
*
 * @properties={typeid:24,uuid:"29442D35-3149-4B55-8816-B46C09B0BC71"}
 */
function week(dDate, nOverruleFirstDayOfWeek) {
 // Remove time components of date
 var justTheDate = new Date(dDate.getFullYear(), dDate.getMonth(), dDate.getDate()) 
 var weekDiff = 0
 // determine if ISO 8601 (european) or Canada/USA calculation should be used 
 if ((!nOverruleFirstDayOfWeek && utils.isMondayFirstDayOfWeek()) || nOverruleFirstDayOfWeek == 1) {
     // ISO 8601 (week 1 contains Jan 4th and first Thursday is in week 1)   Change date to Thursday same week
     var targetThursday = new Date(justTheDate.setDate(justTheDate.getDate() - ((justTheDate.getDay() + 6) % 7) + 3))
     // Take January 4th as it is always in week 1 (see ISO 8601)
     var targetJan4 = new Date(targetThursday.getFullYear(), 0, 4)
     // Change date to Thursday same week
     var firstThursday = new Date(targetJan4.setDate(targetJan4.getDate() - ((targetJan4.getDay() + 6) % 7) + 3))
     // Number of weeks between target Thursday and first Thursday
     weekDiff = (targetThursday - firstThursday) / (86400000 * 7)
 } else {
     // Canada/USA (week 1 contains Jan 1st and first Saturday is in week 1)   Change date to Saturday same week
     var targetSaturday = new Date(justTheDate.setDate(justTheDate.getDate() - ((justTheDate.getDay() + 6) % 7) + 5)) 
     // Take January 1st as it is always in week 1 
     var targetJan1 = new Date(targetSaturday.getFullYear(), 0, 1)
     // Change date to Saturday same week
     var firstSaturday = new Date(targetJan1.setDate(targetJan1.getDate() - ((targetJan1.getDay() + 6) % 7) + 5))
     // Number of weeks between target Saturday and first Saturday
     weekDiff = (targetSaturday - firstSaturday) / (86400000 * 7)
 }
 // return the result   
 return 1 + weekDiff   
}