Difference between revisions of "CommonFormula"
(→variables) |
(→functions) |
||
Line 7: | Line 7: | ||
=functions= | =functions= | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
==abs(a)== | ==abs(a)== | ||
return the absolute value of a | return the absolute value of a | ||
− | == | + | |
− | return | + | ==addTime(%SOME_DATE, %SOME_TZ)== |
+ | return %SOME_DATE with added time depending of %SOME_TZ | ||
Example: | Example: | ||
− | <pre> | + | <pre>addTime(%NOW_DATE, %USER_TZ) returns the current date converted to user timezone by adding timezone time difference</pre> |
− | + | Note : This function no longer exists on OF version 3.0 and later. | |
− | + | ||
− | + | ==changeTime(%SOME_DATE, 'year', 'month', 'day')== | |
− | + | return %SOME_DATE with changed values for year, month and day | |
− | + | Date given as parameter and returned date are in UTC. | |
− | + | Possible values for year, month, day parameters are : | |
+ | *"0" to not changing year/month/day | ||
+ | *"+X" to increment year/month/day by X | ||
+ | *"-X" to decrement year/month/day by X | ||
+ | *"X" to set year/month/day to X | ||
− | + | Example: | |
+ | <pre>changeTime( '2011-05-15', '0', '0', '0' ) returns 2011-05-15, the date stays unchanged | ||
+ | changeTime( '2011-05-15', '0', '-test', '0' ) returns 2011-05-15, the date stays unchanged because the parameter 'month' isn't valid | ||
+ | changeTime( '2011-05-15', '0', '-2', '0' ) returns 2011-03-15, month was decremented by 2 | ||
+ | changeTime( '2011-05-15', '0', '+2', '0' ) returns 2011-07-15, month was incremented by 2 | ||
+ | changeTime( '2011-05-15', '0', '2', '0' ) returns 2011-02-15, month was set to 2 (February) | ||
+ | changeTime( '2011-05-15', '0', '2', '-1' ) returns 2011-02-14 | ||
+ | </pre> | ||
+ | |||
+ | |||
+ | Note : This function exists only on OF version 3.0 and above. | ||
+ | |||
+ | ==convertTimezone(%SOME_DATE, %SOME_TZ1, %SOME_TZ2)== | ||
+ | return %SOME_DATE converted from %SOME_TZ1 to %SOME_TZ2 | ||
+ | |||
+ | If a timezone is not valid, UTC will be used as default. For timezone list, [[allowed_timezone|see here]]. | ||
+ | |||
+ | Example: | ||
+ | <pre>subTime(%NOW_DATE, 'UTC', 'Europe/Paris') returns the current date converted from Greenwich (UTC) to France timezone</pre> | ||
+ | |||
+ | Note : This function exists only on OF version 3.0 and above. | ||
+ | |||
+ | ==formatDate('pattern',%SOME_DATE)== | ||
+ | return the formatted %SOME_DATE. See [[CommonFormula#Date_and_time_format|Date and time format]] for more details about pattern. | ||
+ | |||
+ | Example: | ||
+ | <pre>formatDate('yyyy',%START_DATE) returns the year</pre> | ||
+ | |||
+ | Example available in OpenFlyers 3.0: | ||
+ | <pre>formatDate('e',%START_DATE) returns the dow (=day of the week)</pre> | ||
==getAccount(a,b)== | ==getAccount(a,b)== | ||
Line 77: | Line 107: | ||
</pre> | </pre> | ||
− | == | + | ==getFlowSumBetweenAccount('accound id 1', 'account id 2', 'start date', 'end date')== |
− | return the | + | return the balance difference between account 1 and account 2 from a start date to an end date |
+ | <pre>getFlowSumBetweenAccount(%ACCOUNT1, %ACCOUNT2, '2008-01-01', %NOW_DATE)</pre> | ||
− | + | ==getValue4Date(%RESOURCE_ID, 'date')== | |
− | + | ||
− | + | Return applicable variable value for the closest date. | |
− | + | ||
− | + | Example : | |
− | return | + | <pre>getValue4Date(%RESOURCE_ID, '2010-01-01 00:00:00') returns the previous and current applicable values for the booked resource id variable close to the date of 2010-01-01 00:00:00 |
+ | getValue4Date(%RESOURCE_ID, %NOW_DATE) returns the previous and current applicable values for the booked resource id variable close to current date | ||
+ | getValue4Date(%RESOURCE_ID, %BOOKING_START_DATE) returns the previous and current applicable values for the booked resource id variable close to booking starting date</pre> | ||
+ | |||
+ | Note : This function exists only on OF version 3.0 and above. | ||
+ | |||
+ | ==min(a,b)== | ||
+ | return the minimum between a and b | ||
+ | |||
+ | ==max(a,b)== | ||
+ | return the maximum between a and b | ||
+ | |||
+ | ==roundCeil(a,b)== | ||
+ | return the a value round top to b | ||
Example: | Example: | ||
− | <pre> | + | <pre>roundCeil(106,5) return 110</pre> |
− | + | to round at nearest integer 0.5 => 0 (Positive value only) | |
+ | <pre>roundCeil(107.5-0.5,1) return 107</pre> | ||
+ | |||
+ | to round at nearest integer 0.5 => 1 (Positive value with 2 decimals only) | ||
+ | <pre>roundCeil(107.5-0.499,1) return 108</pre> | ||
+ | |||
+ | ==substr(string,a,b)== | ||
+ | return the portion of string specified by a which represents the starting point (0 being "from the first character") and b which represents the number of character to get. | ||
+ | |||
+ | substr(%LASTNAME, 0, 5) | ||
==subTime(%SOME_DATE, %SOME_TZ)== | ==subTime(%SOME_DATE, %SOME_TZ)== | ||
Line 101: | Line 152: | ||
Note : This function no longer exists on OF version 3.0 and above. | Note : This function no longer exists on OF version 3.0 and above. | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
==sumFlightTime(%PILOT, 'year', 'month', 'day', 'hour', 'minute', 'position' )== | ==sumFlightTime(%PILOT, 'year', 'month', 'day', 'hour', 'minute', 'position' )== | ||
Line 119: | Line 160: | ||
return the total flight time done onto the flight type of a pilot since a starting date. Position at 0 is first pilot, position at 1 is second pilot | return the total flight time done onto the flight type of a pilot since a starting date. Position at 0 is first pilot, position at 1 is second pilot | ||
<pre>sumFlightTime(%PILOT, 2008, 01, 01, 00, 00, 0, 64 ) returns the total flight time done onto flight type id 64 of first pilot since 2008-01-01 00:00:00</pre> | <pre>sumFlightTime(%PILOT, 2008, 01, 01, 00, 00, 0, 64 ) returns the total flight time done onto flight type id 64 of first pilot since 2008-01-01 00:00:00</pre> | ||
− | |||
− | |||
− | |||
− | |||
==sumFlightHour('pilot id', 'position', 'day', 'aircraft type 1' , 'aircraft type 2', 'aircraft type X')== | ==sumFlightHour('pilot id', 'position', 'day', 'aircraft type 1' , 'aircraft type 2', 'aircraft type X')== | ||
Line 149: | Line 186: | ||
Example 2 : if second pilot has done more thant 5 landing in the last 15 days on any aircraft type. | Example 2 : if second pilot has done more thant 5 landing in the last 15 days on any aircraft type. | ||
sumLandingNumber(%PILOT,1,15) > 5 | sumLandingNumber(%PILOT,1,15) > 5 | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
=variables= | =variables= |
Revision as of 12:34, 17 April 2012
Contents
- 1 conditional processing
- 2 conditional processing with operator OR/AND
- 3 functions
- 3.1 abs(a)
- 3.2 addTime(%SOME_DATE, %SOME_TZ)
- 3.3 changeTime(%SOME_DATE, 'year', 'month', 'day')
- 3.4 convertTimezone(%SOME_DATE, %SOME_TZ1, %SOME_TZ2)
- 3.5 formatDate('pattern',%SOME_DATE)
- 3.6 getAccount(a,b)
- 3.7 getBalance(a,[option])
- 3.8 getDebit(a)
- 3.9 getCredit(a)
- 3.10 getFlowSumBetweenAccount('accound id 1', 'account id 2', 'start date', 'end date')
- 3.11 getValue4Date(%RESOURCE_ID, 'date')
- 3.12 min(a,b)
- 3.13 max(a,b)
- 3.14 roundCeil(a,b)
- 3.15 substr(string,a,b)
- 3.16 subTime(%SOME_DATE, %SOME_TZ)
- 3.17 sumFlightTime(%PILOT, 'year', 'month', 'day', 'hour', 'minute', 'position' )
- 3.18 sumFlightTime(%PILOT, 'year', 'month', 'day', 'hour', 'minute', 'position', 'flight type' )
- 3.19 sumFlightHour('pilot id', 'position', 'day', 'aircraft type 1' , 'aircraft type 2', 'aircraft type X')
- 3.20 sumLandingNumber('pilot id', 'position', 'day', 'aircraft type 1' , 'aircraft type 2', 'aircraft type X')
- 4 variables
- 4.1 %ACCOUNT_TYPE
- 4.2 %ACCOUNT1
- 4.3 %ACCOUNT2
- 4.4 %ACCOUNTING_START_DATE
- 4.5 %AIRFIELD_ARRIVAL
- 4.6 %AIRFIELD_DEPARTURE
- 4.7 %AUTHENTICATION_LOGIN
- 4.8 %AUTO_INCREMENT
- 4.9 %BOOKING_START_DATE
- 4.10 %COUNTER_ARRIVAL
- 4.11 %COUNTER_DEPARTURE
- 4.12 %DURATION
- 4.13 %ENTITY_TZ
- 4.14 %FIRSTNAME
- 4.15 %LASTNAME
- 4.16 %MEMBER_NUM
- 4.17 %NOW_DATE
- 4.18 %PILOT
- 4.19 %QTY
- 4.20 %RESOURCE_ID
- 4.21 %START_DATE
- 4.22 %USER_ID
- 4.23 %USER_TZ
- 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
abs(a)
return the absolute value of a
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
Note : This function no longer exists on OF version 3.0 and later.
changeTime(%SOME_DATE, 'year', 'month', 'day')
return %SOME_DATE with changed values for year, month and day
Date given as parameter and returned date are in UTC. Possible values for year, month, day parameters are :
- "0" to not changing year/month/day
- "+X" to increment year/month/day by X
- "-X" to decrement year/month/day by X
- "X" to set year/month/day to X
Example:
changeTime( '2011-05-15', '0', '0', '0' ) returns 2011-05-15, the date stays unchanged changeTime( '2011-05-15', '0', '-test', '0' ) returns 2011-05-15, the date stays unchanged because the parameter 'month' isn't valid changeTime( '2011-05-15', '0', '-2', '0' ) returns 2011-03-15, month was decremented by 2 changeTime( '2011-05-15', '0', '+2', '0' ) returns 2011-07-15, month was incremented by 2 changeTime( '2011-05-15', '0', '2', '0' ) returns 2011-02-15, month was set to 2 (February) changeTime( '2011-05-15', '0', '2', '-1' ) returns 2011-02-14
Note : This function exists only on OF version 3.0 and above.
convertTimezone(%SOME_DATE, %SOME_TZ1, %SOME_TZ2)
return %SOME_DATE converted from %SOME_TZ1 to %SOME_TZ2
If a timezone is not valid, UTC will be used as default. For timezone list, see here.
Example:
subTime(%NOW_DATE, 'UTC', 'Europe/Paris') returns the current date converted from Greenwich (UTC) to France timezone
Note : This function exists only on OF version 3.0 and above.
formatDate('pattern',%SOME_DATE)
return the formatted %SOME_DATE. See Date and time format for more details about pattern.
Example:
formatDate('yyyy',%START_DATE) returns the year
Example available in OpenFlyers 3.0:
formatDate('e',%START_DATE) returns the dow (=day of the week)
getAccount(a,b)
return the account identification for the account Type a of Pilot b
Example:
getAccount(1,25) return 75 getAccount(1,%PILOT) return 112
getBalance(a,[option])
return the balance of the pilot a [option] account type b.
Example:
getBalance(1) return -125.00 getBalance(%PILOT) return 25.51 getBalance(%PILOT,1) return 10.00 getBalance(%PILOT,2) return 10.50 getBalance(%PILOT,3) return 5.01 getBalance(%PILOT,1,3) return 15.01
getDebit(a)
return the sum of debit of the account a
Example:
getDebit(112) return 174.52 getDebit(getAccount(1,%PILOT)) return 53.17
getCredit(a)
return the sum of credit of the account a
Example:
getCredit(14,0) return 104.12 ) getCredit(getAccount(1,%PILOT)) return 43.10
Note: carry forward is not added to the sum of debit or credit, getCredit(getAccount(1,%PILOT)) - getDebit(getAccount(1,%PILOT)) <> getBalance(%PILOT)
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)
getValue4Date(%RESOURCE_ID, 'date')
Return applicable variable value for the closest date.
Example :
getValue4Date(%RESOURCE_ID, '2010-01-01 00:00:00') returns the previous and current applicable values for the booked resource id variable close to the date of 2010-01-01 00:00:00 getValue4Date(%RESOURCE_ID, %NOW_DATE) returns the previous and current applicable values for the booked resource id variable close to current date getValue4Date(%RESOURCE_ID, %BOOKING_START_DATE) returns the previous and current applicable values for the booked resource id variable close to booking starting date
Note : This function exists only on OF version 3.0 and above.
min(a,b)
return the minimum between a and b
max(a,b)
return the maximum between a and b
roundCeil(a,b)
return the a value round top to b
Example:
roundCeil(106,5) return 110
to round at nearest integer 0.5 => 0 (Positive value only)
roundCeil(107.5-0.5,1) return 107
to round at nearest integer 0.5 => 1 (Positive value with 2 decimals only)
roundCeil(107.5-0.499,1) return 108
substr(string,a,b)
return the portion of string specified by a which represents the starting point (0 being "from the first character") and b which represents the number of character to get.
substr(%LASTNAME, 0, 5)
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
Note : This function no longer exists on OF version 3.0 and above.
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
sumFlightTime(%PILOT, 'year', 'month', 'day', 'hour', 'minute', 'position', 'flight type' )
return the total flight time done onto the flight type 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, 64 ) returns the total flight time done onto flight type id 64 of first pilot since 2008-01-01 00:00:00
sumFlightHour('pilot id', 'position', 'day', 'aircraft type 1' , 'aircraft type 2', 'aircraft type X')
return the flight hours total of all the flights done by a pilot in the last "day" day(s) and onto the "aircraft type 1", "aircraft type 2" and "aircraft type X" (X represents another aircraft type)
Set "position" to 0 to count only flight hours where pilot is assigned as first pilot.
Set "position" to 1 to count only flight hours where pilot is assigned as second pilot.
Example 1 : total for first pilot in the last 90 days on aircraft type 1 and aircraft type 2
sumFlightHour(%PILOT, 0, 90, 1, 2)
Example 2 : total for first pilot in the last 90 days on any aircraft type
sumFlightHour(%PILOT, 0, 90)
sumLandingNumber('pilot id', 'position', 'day', 'aircraft type 1' , 'aircraft type 2', 'aircraft type X')
return the landing total of all the flights done by a pilot in the last "day" day(s) and onto the "aircraft type 1", "aircraft type 2" and "aircraft type X" (X represents another aircraft type)
Set "position" to 0 to count only flight hours where pilot is assigned as first pilot.
Set "position" to 1 to count only flight hours where pilot is assigned as second pilot.
Example 1 : if first pilot has done more than 5 landing in the last 30 days on aircraft type 1.
sumLandingNumber(%PILOT,0,30,1) > 5
Example 2 : if second pilot has done more thant 5 landing in the last 15 days on any aircraft type.
sumLandingNumber(%PILOT,1,15) > 5
variables
%ACCOUNT_TYPE
account type
%ACCOUNT1
debit account id
%ACCOUNT2
credit account id
%ACCOUNTING_START_DATE
accounting start date (format is YYYY-MM-DD hh:mm:ss)
%AIRFIELD_ARRIVAL
flight airfield arrival input into the forum (only for OF 3.0 and above)
%AIRFIELD_DEPARTURE
flight airfield departure input into the form (only for OF 3.0 and above)
%AUTHENTICATION_LOGIN
user login
%AUTO_INCREMENT
auto incremental value
%BOOKING_START_DATE
booking start date input into the form (only for OF 3.0 and above)
%COUNTER_ARRIVAL
counter arrival input into the form
%COUNTER_DEPARTURE
counter departure input into the form
%DURATION
flight time input into the form
%ENTITY_TZ
structure/entity timezone
%FIRSTNAME
user firstname
%LASTNAME
user lastname
%MEMBER_NUM
user member id
%NOW_DATE
current date (format is YYYY-MM-DD hh:mm:ss)
%PILOT
pilot id
%QTY
quantity of purcharsed item(s)
%RESOURCE_ID
resource id (only for OF 3.0 and above)
%START_DATE
date of flight beginning (format is YYYY-MM-DD hh:mm:ss)
%USER_ID
user id
%USER_TZ
member timezone
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:
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.
Syntax available in OpenFlyers 2.1
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) | ' |
Syntax available in OpenFlyers 3.0
Symbol | Meaning | Presentation | Example |
---|---|---|---|
e | dow (=day of week: 1 = monday, 7 = sunday) | (Number) | 1 (=monday) |
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 |