CommonFormula
Contents
- 1 conditional processing
- 2 conditional processing with operator OR/AND
- 3 functions
- 3.1 max(a,b)
- 3.2 min(a,b)
- 3.3 abs(a)
- 3.4 roundCeil(a,b)
- 3.5 getAccount(a, %PILOT)
- 3.6 getBalance(a)
- 3.7 formatDate('pattern',%SOME_DATE)
- 3.8 addTime(%SOME_DATE, %SOME_TZ)
- 3.9 subTime(%SOME_DATE, %SOME_TZ)
- 3.10 sumFlightTime(%PILOT, 'year', 'month', 'day', 'hour', 'minute', 'position' )
- 3.11 getFlowSumBetweenAccount('accound id 1', 'account id 2', 'start date', 'end date')
- 4 variables
- 5 Date and time format
conditional processing
(test) ? true-case : false-case
conditional processing with operator OR/AND
( test1 OR test2 ) ? true-case : false-case ( test1 AND test2 ) ? true-case : false-case
functions
max(a,b)
return the maximum between a and b
min(a,b)
return the minimum between a and b
abs(a)
return the absolute value of a
roundCeil(a,b)
return the a value round top to b
Example:
roundCeil(106,5) return 110
getAccount(a, %PILOT)
return the account id of the account type id "a" from the member %PILOT
Example:
getAccount(1, %PILOT) return 4
getBalance(a)
return the balance of the account a
Example:
getBalance(1) return 25.01 getBalance(getAccount(1, %PILOT)) return 35.01
formatDate('pattern',%SOME_DATE)
return the formatted %SOME_DATE. See Date and time format for more details about pattern.
Example:
formatDate('w',%START_DATE) returns the day of week of START_DATE
addTime(%SOME_DATE, %SOME_TZ)
return %SOME_DATE with added time depending of %SOME_TZ
Example:
addTime(%NOW_DATE, %USER_TZ) returns the current date converted to user timezone by adding timezone time difference
subTime(%SOME_DATE, %SOME_TZ)
return %SOME_DATE with substracted time depeding of %SOME_TZ
Example:
subTime(%NOW_DATE, 'Europe/Paris') returns the current date converted to France timezone by substracting timezone time difference
sumFlightTime(%PILOT, 'year', 'month', 'day', 'hour', 'minute', 'position' )
return the total flight time of a pilot since a starting date. Position at 0 is first pilot, position at 1 is second pilot
sumFlightTime(%PILOT, 2008, 01, 01, 00, 00, 0 ) returns the total flight time of first pilot since 2008-01-01 00:00:00
getFlowSumBetweenAccount('accound id 1', 'account id 2', 'start date', 'end date')
return the balance difference between account 1 and account 2 from a start date to an end date
getFlowSumBetweenAccount(%ACCOUNT1, %ACCOUNT2, '2008-01-01', %NOW_DATE)
variables
%DURATION
flight time input into the form
%COUNTER_DEPARTURE
counter departure input into the form
%COUNTER_ARRIVAL
counter arrival input into the form
%PILOT
pilot id
%USER_ID
user id
%LASTNAME
member lastname
%FIRSTNAME
member firstname
%NOW_DATE
current date (format is YYYY-MM-DD hh:mm:ss)
%START_DATE
date of flight beginning (format is YYYY-MM-DD hh:mm:ss)
%ACCOUNTING_START_DATE
accounting start date (format is YYYY-MM-DD hh:mm:ss)
%USER_TZ
member timezone
%ENTITY_TZ
structure/entity timezone
%QTY
quantity of purcharsed item(s)
%ACCOUNT1
debit account id
%ACCOUNT2
credit account id
%AIRFIELD_DEPARTURE
flight airfield departure input into the form (only for OF 3.0 and above)
%AIRFIELD_ARRIVAL
flight airfield arrival input into the forum (only for OF 3.0 and above)
Date and time format
Syntax
To specify the format use a pattern string. In this pattern, all ASCII letters are reserved as pattern letters, which are defined as the following:
Symbol Meaning Presentation Example ------ ------- ------------ ------- G era designator (Text) AD y year (Number) 1996 M month in year (Text & Number) July & 07 d day in month (Number) 10 h hour in am/pm (1~12) (Number) 12 H hour in day (0~23) (Number) 0 m minute in hour (Number) 30 s second in minute (Number) 55 S millisecond (Number) 978 E day in week (Text) Tuesday D day in year (Number) 189 F day of week in month (Number) 2 (2nd Wed in July) w week in year (Number) 27 W week in month (Number) 2 a am/pm marker (Text) PM k hour in day (1~24) (Number) 24 K hour in am/pm (0~11) (Number) 0 z time zone (Text) Pacific Standard Time ' escape for text (Delimiter) '' single quote (Literal) '
The count of pattern letters determine the format.
(Text): 4 or more pattern letters--use full form, < 4--use short or abbreviated form if one exists.
(Number): the minimum number of digits. Shorter numbers are zero-padded to this amount. Year is handled specially; that is, if the count of 'y' is 2, the Year will be truncated to 2 digits.
(Text & Number): 3 or over, use text, otherwise use number.
Any characters in the pattern that are not in the ranges of ['a'..'z'] and ['A'..'Z'] will be treated as quoted text. For instance, characters like ':', '.', ' ', '#' and '@' will appear in the resulting time text even they are not embraced within single quotes.
A pattern containing any invalid pattern letter will result in a thrown exception during formatting or parsing.
Examples Using the local unit system
Format Pattern Result -------------- ------- "yyyy.MM.dd G 'at' HH:mm:ss z" ->> 1996.07.10 AD at 15:08:56 PDT "EEE, MMM d, 'yy" ->> Wed, July 10, '96 "KK:mm a, z" ->> 00:08 AM, PST "h:mm a" ->> 12:08 PM "h 'o''clock' a, zzzz" ->> 12 o'clock PM, Pacific Daylight Time "yyyyy.MMMMM.dd GGG h:mm aaa" ->> 1996.July.10 AD 0:08 PM "dd/MMM/yyyyy HH:mm" ->> 10/07/1996 00:08