Difference between revisions of "CommonFormula"

Jump to: navigation, search
(getDebit(a))
(getCredit(a))
Line 63: Line 63:
 
<pre>
 
<pre>
 
getCredit(14,0) return 104.12 )
 
getCredit(14,0) return 104.12 )
getCredit(getAccount(%PILOT,1)) return  43.10
+
getCredit(getAccount(1,%PILOT)) return  43.10
 
</pre>
 
</pre>
  
 
<pre>
 
<pre>
 
Note: carry forward is not added to the sum of debit or credit,  
 
Note: carry forward is not added to the sum of debit or credit,  
getCredit(getAccount(%PILOT,1)) - getDebit(getAccount(%PILOT,1)) <> getBalance(%PILOT)
+
getCredit(getAccount(1,%PILOT)) - getDebit(getAccount(1,%PILOT)) <> getBalance(%PILOT)
 
</pre>
 
</pre>
  

Revision as of 04:15, 25 April 2011

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(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)

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