imported>Claratte: Text replacement - "" to ""

2024-08-12T18:53:07Z

Text replacement - "<php>" to "<syntaxhighlight lang="php">"

New page

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]] to check the availability in the appropriated section'''

=conditional processing=
(test) ? true-case : false-case

=conditional processing with operator OR/AND=

==OR operator==

<pre>( test1 OR test2 ) ? true-case : false-case</pre>

This syntax is also available in OpenFlyers 4.0 and above:
<pre>( test1 || test2 ) ? true-case : false-case</pre>

==AND operator==

<pre>( test1 AND test2 ) ? true-case : false-case</pre>

This syntax is also available in OpenFlyers 4.0 and above:
<pre>( test1 && test2 ) ? true-case : false-case</pre>

=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:
<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
formatDate('MM', %NOW_DATE) return the month
</pre>

Example available from OpenFlyers 3.0+:
<pre>formatDate('e',%START_DATE) returns the dow (=day of the week)</pre>

==getAccount(a,b)==
return the account identification for the account Type a of Pilot b

Example:
<pre>
getAccount(1,25) return 75
getAccount(1,%PILOT) return 112
</pre>

==getBalance(U,option B)==
return the balance of the user U
[option] account type B.

Example :
<pre>getBalance(1) return -125.00

Flight hours pricing:
getBalance(%PILOT) return 25.51
getBalance(%PILOT,1) return 10.00
getBalance(%PILOT,2) return 10.50
getBalance(%PILOT,3) return 5.01

Product sales:
getBalance(%USER_ID) return 25.51
getBalance(%USER_ID,1) return 10.00
</pre>

==getBirthdate('person id')==

Return the birthdate of a person. Return 1900-01-01 when birthdate is not retrievable for the person.

Example :
<pre>getBirthdate(1) return 1975-01-05
getBirthdate(0) return 1900-01-01
getBirthdate(%PILOT) return 1975-01-05</pre>

Note : This function exists only on OF version 3.0 and above.

==getDebit(a)==
Return the sum of all debits of the account "a" made onto the current accounting period. Carry forward is not added to the sum.

Example:
<pre>getDebit(112) return 174.52
getDebit(getAccount(1,%PILOT)) return 53.17</pre>

==getCredit(a)==
Return the sum of all credits of the account "a" made onto the current accounting period. Carry forward is not added to the sum.

Example:
<pre>
getCredit(14,0) return 104.12 )
getCredit(getAccount(1,%PILOT)) return 43.10
</pre>

==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
<pre>getFlowSumBetweenAccount(%ACCOUNT1, %ACCOUNT2, '2008-01-01', %NOW_DATE)</pre>

==getSex('person id')==

Return a number corresponding to the person sex :
* 0 for a man
* 1 for a woman
* 2 when it's not defined

Example :
<pre>getSex(1)
getSex(%USER_ID)</pre>

Example in product pricing formula to test if an user is a woman :
<pre>(getSex(%USER_ID) == 1) ? $womanReduction : 0</pre>

==getSumAccountBusinessField('business field id, 'person id', 'date')==

Return the sum of values from a business field of "Account entry" category. Only account entries corresponding to the person and from the given date are taking into account in the calculation. Date format is YYYY-MM-DD.

Example :
<pre>getSumAccountBusinessField(13, %PILOT, formatDate( 'yyyy-01-01', %NOW_DATE ) ) ? 100 : 200</pre>

==getValidityExpiredDate('person id', 'validity type id')==

''Note : This function exists only on OF version 3.5 and above. It's only available for flight hours pricing.''

* Return the validity expired date of the person when the validity has a time limitation and the expired date has been set. Date format is YYYY-MM-DD
* Return "0000-00-00" for others cases

Example :
<pre>getValidityExpiredDate(%PILOT, 1); // Return 2014-12-31
getValidityExpiredDate(0, 20); // Return 0000-00-00</pre>

==getValue4Date(%RESOURCE_ID, 'date')==

Return applicable variable value for the closest date.

Example :
<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.

==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 :
<pre>getYearsFromDiffDate( '1975-01-01', '2000-03-03') return 25
getYearsFromDiffDate( '1975-01-01' ) return 37</pre>

==hasValidity('person id', 'validity type id', 'check only holding')==

''Note : This function exists only on OF version 3 and above.''

'check only holding' is an optional parameter. The parameter is taken into account only if its value is equal to 1. It's ignored in others cases.
* If 'check only holding' is different than 1 :
** Return '''1''' when :
*** If it's a validity type with experience :
**** the person has the required experienced
*** If it's not a validity type with experience :
**** it's a validity type without time limitation, the person has the validity
**** it's a validity type with time limitation, the validity of the person has not expired
** Return '''0''' for any others cases
* If 'check only holding' is equal to 1 :
** Return '''1''' when :
*** If it's not a validity type with experience :
**** the person has the validity
** Return '''0''' for any others cases (even if it's a validity type with experience and that the person has the required experience)

Example :
<pre>hasValidity( %PILOT, 1); // on flight hours pricing formula
hasValidity( %USER_ID, 20); // on sale product formula</pre>

==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:
<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>

==sprintf('pattern', 'string')==

Format the string with a pattern. See [[#sprintf_format|sprintf format]] for more details about pattern.

Example to format member num to get 5 characters and prepending zero as much as possible :
<syntaxhighlight lang="php">sprintf('%05s', %MEMBER_NUM) returns 00010 if %MEMBER_NUM is 10 for exemple</php>

Example to format member num to get 5 characters and appending zero as much as possible :
<syntaxhighlight lang="php">sprintf('%-05d', %MEMBER_NUM) returns 10000 if %MEMBER_NUM is 10 for exemple</php>

Example to format member num to get 6 characters and prepending space as much as possible :
<syntaxhighlight lang="php">sprintf('%6s', %MEMBER_NUM) returns " 10" if %MEMBER_NUM is 10 for exemple</php>

Example to format member num to a decimal with 2 digit after the decimal point :
<syntaxhighlight lang="php">sprintf('%0.2f', %MEMBER_NUM) returns 10.00 if %MEMBER_NUM is 10 for exemple</php>

==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
<pre>substr(%LASTNAME, 0, 5)</pre>

Example :
<pre>substr('FIRST_NAME', 0, 5) returns FIRST
substr('FIRST_NAME', 0, -2) returns FIRST_NA</pre>

==subTime(%SOME_DATE, %SOME_TZ)==
return %SOME_DATE with substracted time depeding of %SOME_TZ

Example:
<pre>subTime(%NOW_DATE, 'Europe/Paris') returns the current date converted to France timezone by substracting timezone time difference</pre>

Note : This function no longer exists on OF version 3.0 and above.

==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
<pre>sumFlightHour(%PILOT, 0, 90, 1, 2)
Format sexacentimal
Conversion to decimal hours: Hour = Result/600</pre>

Example 2: return the flight time sum for the first pilot in the last 90 days onto any aircraft type
<pre>sumFlightHour(%PILOT, 0, 90)
Format sexacentimal
Conversion to decimal hours: Hour = Result/600</pre>

==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
<pre>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</pre>

==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.
<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
Format sexacentimal
Conversion to decimal hours: Hour = Result/600</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

==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
<pre>sumPreviousFlightTime(%PILOT, 0, 365, %START_FLIGHT, 1, 2)
Format sexacentimal
Conversion to decimal hours: Hour = Result/600</pre>

==strtolower(string)==
Return the lower cases of string.

Example:
<pre>strtolower('CamelBack') returns "camelback"</pre>

==strtoupper(string)==
Return the upper cases of string.

Example:
<pre>strtolower('CamelBack') returns "CAMELBACK"</pre>

=variables=

==%ACCOUNT_BALANCE==
Account balance. Can be used in the content of the "Account threshold alert" e-mail.

Note : This variable exists only on OF version 3.0.3 and above.

==%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

==%CURRENT_QUANTITY==

The value calculated from quantity formula (only for OF 3.5 and above)

==%CURRENT_UNIT_PRICE==

The value calculated from unit price formula (only for OF 3.5 and above)

==%DURATION==
flight time input into the form in sexacentimal

To get value in hour :
<pre>%DURATION/600</pre>

To get value in minute :
<pre>%DURATION/10</pre>

==%ENTITY_TZ==
structure/entity timezone
==%EXTRAFIELDxx==

*Access to the content of an extra field
*xx = Id of the extra field must be add after the designation %EXTRAFIELD
*For pricing formula, only extra fields of type integer, float or decimal can be used

==%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. available on flight hours pricing formula.

==%PILOT2==

Instructor id / Second person id. Available on flight hours pricing formula.

This variable exists only on OF 3.5 version and above.

==%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.

==%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. Available only for [[AdminDoc3#Accounting_formula|accounting formula]] and sale product formula.

==%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 and above===
{| {{prettytable}}
!Symbol!!Meaning!!Presentation!!Example
|-
|G||era designator||Text||AD
|-
|y||year||Number||1996
|-
|M||month in year||"MMM": Text<br />"MM" or "M" : Number||July<br />07 or 7
|-
|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||
|-
|<nowiki>''</nowiki>||single quote||(Literal)||'
|}

===Syntax available in OpenFlyers 3.0 and above===
{| {{prettytable}}
!Symbol!!Meaning!!Presentation!!Example
|-
|e||dow (=day of week: 1 = sunday, 2 = monday, )||(Number)||2 (=monday)
|}

==Examples Using the local unit system==
{| {{prettytable}}
!Format Pattern!!Result
|-
|<nowiki>"yyyy.MM.dd G 'at' HH:mm:ss z"</nowiki>||1996.07.10 AD at 15:08:56 PDT
|-
|<nowiki>"EEE, MMM d, 'yy"</nowiki>||Wed, July 10, '96
|-
|<nowiki>"KK:mm a, z"</nowiki>||00:08 AM, PST
|-
|<nowiki>"h:mm a"</nowiki>||12:08 PM
|-
|<nowiki>"h 'o''clock' a, zzzz"</nowiki>||12 o'clock PM, Pacific Daylight Time
|-
|<nowiki>"yyyyy.MMMMM.dd GGG h:mm aaa"</nowiki>||1996.July.10 AD 0:08 PM
|-
|<nowiki>"dd/MMM/yyyyy HH:mm"</nowiki>||10/07/1996 00:08
|}

=sprintf format=

Pattern is made with one or more of these elements, in order:
* The character "%"
* An optional character which will be used to pad the string to the right string size. Space is used by default
* The character "-" for a right padding or no character for a left padding
* An optional number to specify how many character in minimum the string should have in final
* The optional character "." followed by a digit that says how many decimal digits should be displayed for decimal
* One of some of these characters:
{| class="wikitable"
!Character!!Description
|-
|d||String is treated as an integer
|-
|f||String is treated as a decimal
|-
|s||String is treated as a string
|-
|}
See [http://php.net/manual/en/function.sprintf.php sprintf definition] for the complete description.

CommonFormula - Revision history

imported>Claratte: Text replacement - "" to ""