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

Jump to: navigation, search
(sumAccountEntry)
(sexa2HoursMinute)
 
(8 intermediate revisions by 2 users not shown)
Line 13: Line 13:
 
*Output parameters:
 
*Output parameters:
 
**distance
 
**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=
 
=nearestPoint=
Line 25: Line 41:
  
 
=saleValidity=
 
=saleValidity=
*This procedure exists with OF 3.0+
+
*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:
Line 31: Line 47:
 
**personId (id of the person)
 
**personId (id of the person)
 
**validityTypeCost (validity type cost)
 
**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 50: Line 82:
  
 
=sumAccountEntryCredit=
 
=sumAccountEntryCredit=
*This function exists with OF 3.5+
+
*This function exists with OF 3.6+
 
*Stored function to compute the current sum of credit lines of an account at a given date
 
*Stored function to compute the current sum of credit lines of an account at a given date
 
*Input parameters:
 
*Input parameters:
Line 58: Line 90:
 
**totalCredit (total credit)
 
**totalCredit (total credit)
  
=sumAccountEntryDedit=
+
=sumAccountEntryDebit=
*This function exists with OF 3.5+
+
*This function exists with OF 3.6+
 
*Stored function to compute the current sum of debit lines of an account at a given date
 
*Stored function to compute the current sum of debit lines of an account at a given date
 
*Input parameters:
 
*Input parameters:
Line 65: Line 97:
 
**endDate (to compute only account entries from opening accounting date to this date)
 
**endDate (to compute only account entries from opening accounting date to this date)
 
*Output parameters:
 
*Output parameters:
**totalDedit (total dedit)
+
**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)

Latest revision as of 12: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)