Difference between revisions of "CommonFormula"
(→%QTY) |
(→%USER_ID) |
||
Line 324: | Line 324: | ||
==%START_DATE== | ==%START_DATE== | ||
date of flight beginning (format is YYYY-MM-DD hh:mm:ss) | date of flight beginning (format is YYYY-MM-DD hh:mm:ss) | ||
+ | |||
+ | ==%UNIT_PRICE_VALUE== | ||
+ | Unit price for the product | ||
==%USER_ID== | ==%USER_ID== |
Revision as of 15:11, 3 April 2013
All the functions are not available in all the OF versions and in all the formula types
- flight time formula,
- pricing management formula,
- recent experience formula,
- accounting formula
See AdminDoc2.1 or AdminDoc3.0 to check the availability in the appropriated section
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 getBirthdate('person id')
- 3.9 getDebit(a)
- 3.10 getCredit(a)
- 3.11 getFlowSumBetweenAccount('accound id 1', 'account id 2', 'start date', 'end date')
- 3.12 getValue4Date(%RESOURCE_ID, 'date')
- 3.13 getYearsFromDiffDate('first date', 'second date')
- 3.14 min(a,b)
- 3.15 max(a,b)
- 3.16 roundCeil(a,b)
- 3.17 substr(string,a,b)
- 3.18 subTime(%SOME_DATE, %SOME_TZ)
- 3.19 sumFlightTime(%PILOT, 'year', 'month', 'day', 'hour', 'minute', 'position' )
- 3.20 sumFlightTime(%PILOT, 'year', 'month', 'day', 'hour', 'minute', 'position', 'flight type' )
- 3.21 sumFlightHour('pilot id', 'position', 'day', 'aircraft type 1' , 'aircraft type 2', 'aircraft type X')
- 3.22 sumPreviousFlightTime('pilot id', 'position', 'day', 'endingDate', 'aircraft type 1' , 'aircraft type 2', 'aircraft type X')
- 3.23 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 %EXTRAFIELDxx
- 4.15 %FIRSTNAME
- 4.16 %LASTNAME
- 4.17 %MEMBER_NUM
- 4.18 %NOW_DATE
- 4.19 %PILOT
- 4.20 %PRODUCT_QUANTITY
- 4.21 %QTY
- 4.22 %RESOURCE_ID
- 4.23 %RESOURCE_NAME
- 4.24 %START_DATE
- 4.25 %UNIT_PRICE_VALUE
- 4.26 %USER_ID
- 4.27 %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
getBirthdate('person id')
Return the birthdate of a person. Return 1900-01-01 when birthdate is not retrievable for the person.
Example :
getBirthdate(1) return 1975-01-05 getBirthdate(0) return 1900-01-01 getBirthdate(%PILOT) return 1975-01-05
Note : This function exists only on OF version 3.0 and above.
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.
getYearsFromDiffDate('first date', 'second date')
- Return difference of year between two dates
- Date format is YYYY-MM-DD or YYYY-MM-DD hh:mm:ss
- When second date is not specified, current date is used
Example :
getYearsFromDiffDate( '1975-01-01', '2000-03-03') return 25 getYearsFromDiffDate( '1975-01-01' ) return 37
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. When b is a negative number then that many characters will be omitted from the end of string
substr(%LASTNAME, 0, 5)
Example :
substr('FIRST_NAME', 0, 5) returns FIRST_NAME substr('FIRST_NAME', 0, -2) returns FIRST_NA
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 Format sexacentimal Conversion to decimal hours: Hour = Result/600
sumFlightTime(%PILOT, 'year', 'month', 'day', 'hour', 'minute', 'position', 'flight type' )
Note : This function exists only on OF version 3.0 and above.
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. If the flight type is not specified, the total is done onto all flight type.
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 Format sexacentimal Conversion to decimal hours: Hour = Result/600
sumFlightHour('pilot id', 'position', 'day', 'aircraft type 1' , 'aircraft type 2', 'aircraft type X')
Return the flight time sum of all the flights done by a pilot in the last "day" day(s) from now 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: return the flight time sum for the first pilot in the last 90 days from now and onto the aircraft type 1 and 2
sumFlightHour(%PILOT, 0, 90, 1, 2) Format sexacentimal Conversion to decimal hours: Hour = Result/600
Example 2: return the flight time sum for the first pilot in the last 90 days onto any aircraft type
sumFlightHour(%PILOT, 0, 90) Format sexacentimal Conversion to decimal hours: Hour = Result/600
sumPreviousFlightTime('pilot id', 'position', 'day', 'endingDate', 'aircraft type 1' , 'aircraft type 2', 'aircraft type X')
Note : This function exists only on OF version 3.0 and above.
Example: Return the flight time sum of all the flights done by a pilot, in the days "day" preceding the date time "endingDate" (included) 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.
Return the flight time sum for the first pilot in the 365 days preceding the date of the flight and onto the aircraft type 1 and 2
sumFlightHour(%PILOT, 0, 365, %START_FLIGHT, 1, 2) Format sexacentimal Conversion to decimal hours: Hour = Result/600
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 in sexacentimal
To get value in hour :
%DURATION/600
To get value in minute :
%DURATION/10
%ENTITY_TZ
structure/entity timezone
%EXTRAFIELDxx
Access to the content of a extra field.
xx = Id of the extra field must be add after the designation %EXTRAFIELD
This identification number can be found by the query SELECT * FROM extrafiles
To use an extra field content in the formula the extra field must be type integer, float or decimal
%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
%PRODUCT_QUANTITY
Quantity of purcharsed item(s)
Note : This variable exists only on OF version 3.0.3 and above.
%QTY
Quantity of purcharsed item(s)
This variable exists only on OF version 3.0 and below.
%RESOURCE_ID
resource id (only for OF 3.0 and above)
%RESOURCE_NAME
Resource name (only for OF 3.0 and above)
%START_DATE
date of flight beginning (format is YYYY-MM-DD hh:mm:ss)
%UNIT_PRICE_VALUE
Unit price for the product
%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 |