Difference between revisions of "OpenFlyers SQL stored functions and procedures"

Jump to: navigation, search
(→‎saleQualification)
(→‎sexa2HoursMinute)
 
(12 intermediate revisions by 3 users not shown)
Line 3: Line 3:
 
This page is dedicated to list OpenFlyers SQL stored functions and procedures which are accessibles to OpenFlyers administrators via the SQL exports or imports.
 
This page is dedicated to list OpenFlyers SQL stored functions and procedures which are accessibles to OpenFlyers administrators via the SQL exports or imports.
  
−
=saleQualification=
+
=distanceBetween2Point=
−
*This procedure will be removed soon.
+
 
*This procedure exists with OF 3.0+
 
*This procedure exists with OF 3.0+
−
*Stored procedure to create account entries and update qualification expire date of an user (or add qualification to the user if it doesn't exist)
+
*Stored function to calculate distance between two points (of a coordinate)
 
*Input parameters:
 
*Input parameters:
−
**qualificationId (id of the qualification)
+
**latitude1
−
**authenticationId (id of the user)
+
**longitude1
−
**qualificationCost
+
**latitude2
 +
**longitude2
 +
*Output parameters:
 +
**distance
  
−
=saleValidity=
+
=formatDecimal=
 +
*This function exists with OF 3.6+
 +
*Stored function for SQL report to formal decimal by removing trailing zero after the decimal separator and by replacing the decimal separator with the one customized by the platform
 +
*Input parameters:
 +
**string (the decimal to format)
 +
*Output parameters:
 +
**replacedString (the formated decimal)
 +
 
 +
=isBalancedAccountEntryFlow=
 +
*This function exists with OF 3.6+
 +
*Stored function for SQL report to check if account entries from a flow are balanced
 +
*Input parameters:
 +
**flowId (flow id)
 +
*Output parameters:
 +
**isBalanced (1 when entries are balanced, 0/null when not)
 +
 
 +
=nearestPoint=
 
*This procedure exists with OF 3.0+
 
*This procedure exists with OF 3.0+
 +
*Stored function to find the nearest ICAO from a coordinate
 +
*Input parameters:
 +
**latRef (latitude of the coordinate)
 +
**longRef (longitude of the coordinate)
 +
**distanceMax (max distance to filter result)
 +
*Output parameters:
 +
**ICAO id
 +
 +
=saleValidity=
 +
*This procedure exists with OF 3.0alpha only
 
*Stored procedure to create account entries and update validity expire date of an user (or add validity to the user if it doesn't exist)
 
*Stored procedure to create account entries and update validity expire date of an user (or add validity to the user if it doesn't exist)
 
*Input parameters:
 
*Input parameters:
−
**validityId (id of the qualification)
+
**validityTypeId (id of the validity type)
−
**authenticationId (id of the user)
+
**personId (id of the person)
−
**validityCost
+
**validityTypeCost (validity type cost)
 +
 
 +
=sexa2HoursHundredths=
 +
*This function exists with OF 4+
 +
*Stored function to format sexacentimal to "hours:hundreths"
 +
*Input parameters:
 +
**sexacentimal
 +
*Output parameters:
 +
**convertedValue (hours:hundreths)
 +
 
 +
=sexa2HoursMinute=
 +
*This function exists with OF 3.6+
 +
*Stored function to format sexacentimal to "hours:minutes"
 +
*Input parameters:
 +
**sexacentimal
 +
*Output parameters:
 +
**convertedValue (hours:minutes)
  
 
=stripChars=
 
=stripChars=
Line 33: Line 77:
 
*Input parameters:
 
*Input parameters:
 
**accountId (account Id)
 
**accountId (account Id)
−
**endDate (to compute only account entries before this date)
+
**endDate (to compute only account entries from opening accounting date to this date)
 +
*Output parameters:
 +
**totalBalance (balance)
 +
 
 +
=sumAccountEntryCredit=
 +
*This function exists with OF 3.6+
 +
*Stored function to compute the current sum of credit lines of an account at a given date
 +
*Input parameters:
 +
**accountId (account Id)
 +
**endDate (to compute only account entries from opening accounting date to this date)
 +
*Output parameters:
 +
**totalCredit (total credit)
 +
 
 +
=sumAccountEntryDebit=
 +
*This function exists with OF 3.6+
 +
*Stored function to compute the current sum of debit lines of an account at a given date
 +
*Input parameters:
 +
**accountId (account Id)
 +
**endDate (to compute only account entries from opening accounting date to this date)
 +
*Output parameters:
 +
**totalDebit (total debit)
 +
 
 +
=sumValidatedAccountEntry=
 +
*This function exists with OF 4.0+
 +
*Stored function to compute the current balance of an account at a given date. Only validated account entries are taken into account
 +
*Input parameters:
 +
**accountId (account Id)
 +
**endDate (to compute only account entries from opening accounting date to this date)
 
*Output parameters:
 
*Output parameters:
 
**totalBalance (balance)
 
**totalBalance (balance)

Latest revision as of 13:03, 13 May 2020

Presentation

This page is dedicated to list OpenFlyers SQL stored functions and procedures which are accessibles to OpenFlyers administrators via the SQL exports or imports.

distanceBetween2Point

  • This procedure exists with OF 3.0+
  • Stored function to calculate distance between two points (of a coordinate)
  • Input parameters:
    • latitude1
    • longitude1
    • latitude2
    • longitude2
  • Output parameters:
    • distance

formatDecimal

  • This function exists with OF 3.6+
  • Stored function for SQL report to formal decimal by removing trailing zero after the decimal separator and by replacing the decimal separator with the one customized by the platform
  • Input parameters:
    • string (the decimal to format)
  • Output parameters:
    • replacedString (the formated decimal)

isBalancedAccountEntryFlow

  • This function exists with OF 3.6+
  • Stored function for SQL report to check if account entries from a flow are balanced
  • Input parameters:
    • flowId (flow id)
  • Output parameters:
    • isBalanced (1 when entries are balanced, 0/null when not)

nearestPoint

  • This procedure exists with OF 3.0+
  • Stored function to find the nearest ICAO from a coordinate
  • Input parameters:
    • latRef (latitude of the coordinate)
    • longRef (longitude of the coordinate)
    • distanceMax (max distance to filter result)
  • Output parameters:
    • ICAO id

saleValidity

  • This procedure exists with OF 3.0alpha only
  • Stored procedure to create account entries and update validity expire date of an user (or add validity to the user if it doesn't exist)
  • Input parameters:
    • validityTypeId (id of the validity type)
    • personId (id of the person)
    • validityTypeCost (validity type cost)

sexa2HoursHundredths

  • This function exists with OF 4+
  • Stored function to format sexacentimal to "hours:hundreths"
  • Input parameters:
    • sexacentimal
  • Output parameters:
    • convertedValue (hours:hundreths)

sexa2HoursMinute

  • This function exists with OF 3.6+
  • Stored function to format sexacentimal to "hours:minutes"
  • Input parameters:
    • sexacentimal
  • Output parameters:
    • convertedValue (hours:minutes)

stripChars

  • This function exists with OF 3.0+
  • Stored function to get a cleaned string (remove spaces and underscores)
  • Input parameters:
    • word (the string to be cleaned)
  • Output parameters:
    • stripWord (the string cleaned)

sumAccountEntry

  • This function exists with OF 2.1+
  • Stored function to compute the current balance of an account at a given date
  • Input parameters:
    • accountId (account Id)
    • endDate (to compute only account entries from opening accounting date to this date)
  • Output parameters:
    • totalBalance (balance)

sumAccountEntryCredit

  • This function exists with OF 3.6+
  • Stored function to compute the current sum of credit lines of an account at a given date
  • Input parameters:
    • accountId (account Id)
    • endDate (to compute only account entries from opening accounting date to this date)
  • Output parameters:
    • totalCredit (total credit)

sumAccountEntryDebit

  • This function exists with OF 3.6+
  • Stored function to compute the current sum of debit lines of an account at a given date
  • Input parameters:
    • accountId (account Id)
    • endDate (to compute only account entries from opening accounting date to this date)
  • Output parameters:
    • totalDebit (total debit)

sumValidatedAccountEntry

  • This function exists with OF 4.0+
  • Stored function to compute the current balance of an account at a given date. Only validated account entries are taken into account
  • Input parameters:
    • accountId (account Id)
    • endDate (to compute only account entries from opening accounting date to this date)
  • Output parameters:
    • totalBalance (balance)