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

(→saleQualification) |
(→sexa2HoursMinute) |
||

(14 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. | ||

− | = | + | =distanceBetween2Point= |

− | *This | + | *This procedure exists with OF 3.0+ |

− | *Stored | + | *Stored function to calculate distance between two points (of a coordinate) |

*Input parameters: | *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= | =stripChars= | ||

Line 23: | Line 77: | ||

*Input parameters: | *Input parameters: | ||

**accountId (account Id) | **accountId (account Id) | ||

− | **endDate (to compute only account entries | + | **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 11:03, 13 May 2020

## Contents

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