axaptapedia: Packing date range values in queries

[Blog bot]

Источник: http://www.axaptapedia.com/Packing_d...ues_in_queries
==============

Summary:
<div>A query in default Ax can be packed for patch processing of userdata. When a query is packed, ranges supplied by the user are packed as a text value and this includes date ranges. There is however a problem when the range is unpacked on a computer with a different regional setting than the computer issueing the request.


For example: a dutch user wants a report for sales orders created after the 2nd of november 2007. Enters the value ‘>2-11-2007’ (‘D-M-Y’) in the range field. This is placed in a batch group and run on a batch server with regional settings english (‘D\M\Y’). The report is empty, because the date range resulted in no machtes.

Changing the regional settings so all clients, server and batch server match is offcourse the best solution. But in international scenario's endusers are still able to supply their own formats (there is no validation) and this basicly contradicts the purpose of regional settings. Therefor, the solution could be changing the daterange-value in a generic way. There are two generic storages available: the date2strxpp format : d\m\y (see Global::date2strxpp(...)) and date2num (number of days since 01/01/1900). Since the date2strxpp format is not easy converted back (any2date and str2date does not work) converting to a number is the 'easiest'.


== Alteration in Dynamics Ax ==
For this to work, two alteration have to be made:
- Logic to change the daterage-value to a generic value
- Hook into the packing and unpacking of a query

''Note that this affects all packing of queries, both in batch as in usagedata. It does not affect the UI: user still have to enter the dates using the formatting in their own regional settings, and after unpacking the date value will be in the regional settings of the users.''
''After this change, any saved batch jobs with date ranges and usagedate will have to be re-entered.''

== Logic to change the daterage-value to a generic value ==

To change a daterange-value, the logic has to take into account complex values like:
"!107, 207, 307..407, =507, 707"
So, in case of complex values, this is a challange. For a generic approach the following code looks up the allowed control character like !,= and '..' Any date in between those characters are presumed dates.

Add to following code to the SysQuery class:

// ---------------------------------------------------
// Method : SysQuery::convertDateRangeValue()
// Author : Gerrit Hulleman
// Created : 20071108
// Modified : 20071108
// Purpose : Convert a date range from and to a generic store value.
// History : 20071108 - Initial version
//
// Parm _conversionType: 0 = Regional 2 Generic
// !0 = Generic 2 Regional
// ---------------------------------------------------
static str convertDateRangeValue(str _rangeValue, int _conversionType)
{
str dateRangeValue;

int tokenIdx;
int tokenLength;
str tokenValue;
boolean tokenValueIsDate;

str result;

//
// Find the first entry of a 'control' token(not a date value)
// Sets: tokenIdx
// tokenLength
//
void loadNextIndex()
{
int foundIdx;
;
tokenIdx = strlen(dateRangeValue)+1; // Default -> set to full length

//
// Find the single-char control tokens
//
foundIdx = strfind(dateRangeValue, " ,=!'\"", 1, strlen(dateRangeValue));
if (foundIdx && foundIdx < tokenIdx)
{
tokenLength = 1;
tokenIdx = foundIdx;
}

//
// Find the multi char control tokens.
//
foundIdx = strscan(dateRangeValue, "..", 0, strlen(dateRangeValue));
if (foundIdx && foundIdx < tokenIdx)
{
tokenLength = 2;
tokenIdx = foundIdx;
}
}

//
// Load the next token
// Sets: tokenValue
//
void loadNextToken()
{
;
//
// Load the first token data: tokenIdx, tokenLength
//
loadNextIndex();

if (tokenIdx == 1)
{
//
// Found a control token at the beginning of the value. Load token.
//
tokenValueIsDate = false;
tokenValue = substr(dateRangeValue, tokenIdx, tokenLength);
dateRangeValue = substr(dateRangeValue, tokenLength+1, strlen(dateRangeValue)-tokenLength);
}
else
{
//
// Found a control token further in the value. Date present before the control token.
//
tokenValueIsDate = true;
tokenValue = substr(dateRangeValue, 1, tokenIdx-1);
dateRangeValue = substr(dateRangeValue, tokenIdx, strlen(dateRangeValue)-tokenIdx+1);
}
}

//
// Converts a date string to and from regional values as string
//
str convertDateValue()
{
int dateValueInt;
str localResult;
date dateValue;
;
if (_conversionType == 0)
{
//
// Convert to generic
//
dateValue = str2date(tokenValue, -1);
dateValueInt = date2num(dateValue); // Number representation of the value
localResult = strfmt("%1", dateValueInt);
}
else
{
//
// Convert to regional
//
dateValueInt = str2int(tokenValue); // Number representation of the value
dateValue = num2date(dateValueInt);
localResult = date2str(dateValue, -1, -1, -1, -1, -1, -1);
}

return localResult;
}
;

dateRangeValue = _rangeValue;

//
// Progress the value provided.
//
while (dateRangeValue)
{
//
// Retrieve the next token
//
loadNextToken();

//
// If the token is a date, convert. Otherwise, token is a control value and add unconverted.
//
if (tokenValueIsDate)
result += convertDateValue();
else
result += tokenValue;
}

return result;

}




== Hook into the packing and unpacking of a query ==

Now it is up to Ax to convert the daterange-value to a generic value. Ax has a lovely place for this: SysQuery::packDatasource for packing and SysQuery::unpackDataSource for (you guessed it...) unpacking.

In the pack datasource, look for the following code:

// Pack ranges
... // code removed
for (i=1; i = _queryNextUniqueId)
{
... // code removed

if(! range)
range = queryBuildRange.value();

// Begin modification, 08-11-2007, Ghull
if (range != "")
{
//
// Convert a date-range value from regional to generic value
//
dictField = new SysDictField(queryBuildRange.table(), queryBuildRange.field());
if (dictField.baseType() == Types::Date)
range = SysQuery::convertDateRangeValue(range, 0);
}
// End modification, 08-11-2007, Ghull
}
}

Add the code indicated by the modification comments. Your code might look a bit differently, depending on version and custom code.

For unpacking, look for the following sections in the SysQuery.unpackDataSource

// unpack ranges
_queryBuildDataSource.clearRanges();

rangeNoAddSet = new Set(Types::Integer);
if (_origRangeMap &&
_origRangeMap.exists(_queryBuildDataSource.uniqueId()))
{
origRangePack = _origRangeMap.lookup(_queryBuildDataSource.uniqueId());
elements = conLen(origRangePack);
for (i=1; i

[kashperuk]

На самом деле интересно.
В смысле, это бы из Майкрософт кому-то почитать, кто ответственный за локализацию