Difference between revisions of "CommonFormula"
(→getAccount(a, %PILOT)) |
(→getBalance(a,[option])) |
||
Line 23: | Line 23: | ||
substr(%LASTNAME, 0, 5) | substr(%LASTNAME, 0, 5) | ||
+ | |||
+ | ==getAccount(a,b)== | ||
+ | return the account identification for the account Type a of Pilot b | ||
+ | |||
+ | Example: | ||
+ | <pre> | ||
+ | getAccount(25,1) return 75 | ||
+ | getAccount(%PILOT,1) return 112 | ||
+ | </pre> | ||
==getBalance(a,[option])== | ==getBalance(a,[option])== | ||
− | return the balance of the | + | return the balance of the pilot a |
[option] account type b. | [option] account type b. | ||
Example: | Example: | ||
− | <pre>getBalance(1) return | + | <pre> |
− | getBalance | + | getBalance(1) return -125.00 |
− | getBalance | + | getBalance(%PILOT) return 25.51 |
− | getBalance | + | getBalance(%PILOT,1) return 10.00 |
− | getBalance | + | getBalance(%PILOT,2) return 10.50 |
− | getBalance | + | getBalance(%PILOT,3) return 5.01 |
+ | getBalance(%PILOT,1,3) return 15.01 | ||
+ | |||
+ | </pre> | ||
+ | |||
+ | ==getDebitCredit(a,b)== | ||
+ | b = 0 return debit | ||
+ | b = 1 return credit | ||
+ | return the sum of debit (or credit) of the account a | ||
+ | |||
+ | <pre> | ||
+ | Note: carry forward is not added to the sum of debit or credit, | ||
+ | getDebitCredit(getAccount(%PILOT,1),1) - getDebitCredit(getAccount(%PILOT,1),0) <> getBalance(%PILOT) | ||
+ | </pre> | ||
+ | |||
+ | Example: | ||
+ | <pre> | ||
+ | getDebitCredit(112,0) return 174.52 (debit) | ||
+ | getDebitCredit(getAccount(%PILOT,1),1) return 53.17 (credit) | ||
</pre> | </pre> | ||
Revision as of 02:42, 22 April 2011
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 substr(string,a,b)
- 3.6 getAccount(a,b)
- 3.7 getBalance(a,[option])
- 3.8 getDebitCredit(a,b)
- 3.9 formatDate('pattern',%SOME_DATE)
- 3.10 addTime(%SOME_DATE, %SOME_TZ)
- 3.11 subTime(%SOME_DATE, %SOME_TZ)
- 3.12 convertTimezone(%SOME_DATE, %SOME_TZ1, %SOME_TZ2)
- 3.13 sumFlightTime(%PILOT, 'year', 'month', 'day', 'hour', 'minute', 'position' )
- 3.14 getFlowSumBetweenAccount('accound id 1', 'account id 2', 'start date', 'end date')
- 3.15 sumFlightHour('pilot id', 'position', 'day', 'aircraft type 1' , 'aircraft type 2', 'aircraft type X')
- 3.16 sumLandingNumber('pilot id', 'position', 'day', 'aircraft type 1' , 'aircraft type 2', 'aircraft type X')
- 3.17 changeTime(%SOME_DATE, 'year', 'month', 'day')
- 4 variables
- 4.1 %DURATION
- 4.2 %COUNTER_DEPARTURE
- 4.3 %COUNTER_ARRIVAL
- 4.4 %PILOT
- 4.5 %USER_ID
- 4.6 %LASTNAME
- 4.7 %FIRSTNAME
- 4.8 %NOW_DATE
- 4.9 %START_DATE
- 4.10 %ACCOUNTING_START_DATE
- 4.11 %USER_TZ
- 4.12 %ENTITY_TZ
- 4.13 %QTY
- 4.14 %ACCOUNT1
- 4.15 %ACCOUNT2
- 4.16 %AIRFIELD_DEPARTURE
- 4.17 %AIRFIELD_ARRIVAL
- 4.18 %MEMBER_NUM
- 4.19 %ACCOUNT_TYPE
- 4.20 %AUTO_INCREMENT
- 4.21 %AUTHENTICATION_LOGIN
- 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
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)
getAccount(a,b)
return the account identification for the account Type a of Pilot b
Example:
getAccount(25,1) return 75 getAccount(%PILOT,1) 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
getDebitCredit(a,b)
b = 0 return debit b = 1 return credit return the sum of debit (or credit) of the account a
Note: carry forward is not added to the sum of debit or credit, getDebitCredit(getAccount(%PILOT,1),1) - getDebitCredit(getAccount(%PILOT,1),0) <> getBalance(%PILOT)
Example:
getDebitCredit(112,0) return 174.52 (debit) getDebitCredit(getAccount(%PILOT,1),1) return 53.17 (credit)
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
Note : This function no longer exists on OF version 3.0 and later.
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.
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.
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)
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, 1, 2) // Any aircraft type</pre>
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
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.
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
user lastname
%FIRSTNAME
user 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)
%MEMBER_NUM
user member id
%ACCOUNT_TYPE
account type
%AUTO_INCREMENT
auto incremental value
%AUTHENTICATION_LOGIN
user login
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