Revision as of 15:40, 11 September 2018

Introduction

This page lists SQL requests for OpenFlyers release 4 export about Accounting.

Balances of resource accounts

• Variable $accountingId should be defined first and should be of dbObject::Accounting value type.
• Variable $endDate should be defined first and should be of value type Date and time value type.

[OF_DYNAMIC_SQL]
SELECT IFNULL(CONCAT(
    'SELECT resource.name,',
    GROUP_CONCAT(
        CONCAT(
            ' (SELECT sumAccountEntry(account.id, \'$endDate\') AS balance
               FROM account
               LEFT JOIN account_type ON (account.account_type=account_type.id)
               WHERE account_type.activated=1
                 AND account.category=3
                 AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= \'$endDate\') )
                 AND account.owner_id=resource.id
                 AND account.account_type=',
            account_type.id,
            ') AS \'',
            REPLACE(account_type.name, '\'', '\\\''),
            '\''
        )
    ),
    ', (SELECT SUM(sumAccountEntry(account.id, \'$endDate\')) AS balance
       FROM account
       LEFT JOIN account_type ON (account.account_type=account_type.id)
       WHERE account_type.activated=1
         AND account.category=3
         AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= \'$endDate\') )
         AND account.owner_id=resource.id
       GROUP BY account.owner_id
    ) AS \'Total\'
    FROM resource WHERE resource.physical=1 AND resource.activated=1 GROUP BY resource.id
    UNION
    SELECT \'Total global\',',
    GROUP_CONCAT(
        CONCAT(
            ' (SELECT SUM(sumAccountEntry(account.id, \'$endDate\')) AS balance
               FROM account
               LEFT JOIN account_type ON (account.account_type=account_type.id)
               WHERE account_type.activated=1
                 AND account.category=3
                 AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= \'$endDate\') )
                 AND account.account_type=',
            account_type.id,
            ') AS \'',
            REPLACE(account_type.name, '\'', '\\\''),
            '\''
        )
    ),
    ', (SELECT SUM(sumAccountEntry(account.id, \'$endDate\')) AS balance
       FROM account
       LEFT JOIN account_type ON (account.account_type=account_type.id)
       WHERE account_type.activated=1
         AND account.category=3
         AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= \'$endDate\') )
    ) AS \'Total\''
), 'SELECT \'\'')
FROM account_type
LEFT JOIN accounting ON accounting.id=account_type.accounting_id
WHERE account_type.category=3
  AND account_type.activated=1
  AND accounting.id='$accountingId'

Balances of user accounts

• Variable $accountingId should be defined first and should be of dbObject::Accounting value type.
• Variable $endDate should be defined first and should be of value type Date and time value type.

SELECT CONCAT( '[LINK=index.php?menuAction=account_journal&menuParameter=', account.id, ']', account.id, '[/LINK]' ) AS '_tr(ID)', person.last_name AS '_tr(LAST_NAME)', person.first_name AS '_tr(FIRST_NAME)', account_type.name AS '_tr(ACCOUNT_TYPE)',
sumAccountEntry(account.id, '$endDate') AS '_tr(BALANCE)'
FROM account
LEFT JOIN accounting ON accounting.id=account.accounting_id 
LEFT JOIN account_type ON (account.account_type=account_type.id)
LEFT JOIN person ON (account.owner_id=person.id)
WHERE account.category=2
  AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= '$endDate') )
  AND accounting.id='$accountingId'
ORDER BY person.last_name, person.first_name

Balances of user accounts whose got a specific profile

• Variable $accountingId should be defined first and should be of dbObject::Accounting value type.
• Variable $endDate should be defined first and should be of value type Date and time value type.
• Variable $profileId should be defined first and should be of dbObjectMulti::Profile value type.

SELECT
    personWithProfile.last_name AS NOM,
    personWithProfile.first_name AS PRENOM,
    personWithProfile.person_profile AS Profil,
    IFNULL(sumAccountEntry(account.id,'$endDate'),0) AS Solde
FROM account
LEFT JOIN accounting ON accounting.id=account.accounting_id 
LEFT JOIN (
    SELECT person.*, GROUP_CONCAT(profile.name ORDER BY profile.name) AS person_profile
    FROM person
    LEFT JOIN profile ON (person.profile & profile.id)
    WHERE profile.id IN ($profileId) OR '-' IN ($profileId)
    GROUP BY person.id
) AS personWithProfile ON personWithProfile.id=account.owner_id 
WHERE account.category=2
  AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= '$endDate') )
  AND CAST(accounting.id AS CHAR) = '$accountingId'
  AND personWithProfile.id IS NOT NULL
ORDER BY NOM, PRENOM

Balance per account category

• Variable $accountingId should be defined first and should be of dbObject::Accounting value type.

SELECT 
    CASE
        WHEN account_type.name IS NOT NULL AND (account.category=2 OR account.category=3) THEN account_type.name
        WHEN account.category=1 THEN CONCAT('Other account ', account.name)
        WHEN account.category=4 THEN CONCAT('Supplier account ', account.name)
        WHEN account.category=6 THEN CONCAT('VAT account ', account.name)
        WHEN account.category=7 THEN CONCAT('Expense account ', account.name)
        WHEN account.category=8 THEN CONCAT('Treasury account ', account.name)
        WHEN account.category=9 THEN CONCAT('Product account ', account.name)
        WHEN account.category=10 THEN CONCAT('Balance sheet account ', account.name)
        WHEN account.category=11 THEN CONCAT('Customer account ', account.name)
        ELSE account.name
    END AS 'Account type',
    IFNULL(SUM( sumAccountEntry(account.id,NOW())),0) AS Solde
FROM account
LEFT JOIN accounting ON accounting.id=account.accounting_id 
LEFT JOIN account_type ON (account.account_type = account_type.id AND account.category IN(2,3))
LEFT JOIN person ON (account.owner_id = person.id AND account.category=2)
LEFT JOIN resource ON (account.owner_id = resource.id AND account.category=3)
WHERE account.activated = 1
  AND accounting.id='$accountingId'
  AND (
      (person.id IS NULL AND resource.id IS NULL)
      OR
      (person.activated=1 AND account.category=2)
      OR
      (resource.activated=1 AND account.category=3)
  )
GROUP BY account.category

Carry forwards

• Variable $accountingId should be defined first and should be of dbObject::Accounting value type.

SELECT
    IF (account.category = 2, CONCAT(person.last_name,'  
',IFNULL(person.first_name, ''),' (',account_type.name,')'),
    IF (account.category = 3,CONCAT(resource.name,'  
(',account_type.name,')'),account.name)) AS Nom,
    export_account AS Code_comptable,
    debit AS 'Debit',
    credit AS 'Credit'
FROM account
LEFT JOIN accounting ON accounting.id=account.accounting_id 
LEFT JOIN balance ON balance.account_id=account.id
LEFT JOIN person ON person.id=account.owner_id AND account.category = 2
LEFT JOIN resource ON resource.id=account.owner_id AND account.category = 3
LEFT JOIN account_type ON account_type.id=account.account_type
WHERE account.activated = 1
  AND balance.balance_date_id = (SELECT balance_date.id FROM balance_date ORDER BY balance_date.id DESC LIMIT 1)
  AND accounting.id='$accountingId'
  AND (
      (person.id IS NULL AND resource.id IS NULL)
      OR
      (person.activated=1 AND account.category=2)
      OR
      (resource.activated=1 AND account.category=3)
  )
ORDER BY Nom ASC

Cheque deposit slip

SELECT date_format(ae.registration_date, "%d/%m/%Y") as "date", a.name, formatDecimal(ae.credit) as montant, ae.payment_description as libelle, 
(SELECT a2.name FROM account_entry ae2 JOIN account a2 ON a2.id = ae2.account_id WHERE ae2.flow_id = ae.flow_id AND ae2.debit > 0 LIMIT 0,1) as compte_banque, ae.comments as commentaire
FROM account_entry ae
JOIN account a
  ON ae.account_id = a.id
WHERE a.account_type = 1
  AND ae.validated = 1
  AND ae.credit > 0
ORDER BY ae.registration_date DESC

Customer bill per month and per accounting

• Variable $accountingId should be defined first and should be of dbOject::Accounting value type.
• Variable $month should be defined first and should be of Month value type.
• Variable $year should be defined first and should be of Year value type.

SELECT account.name AS 'Client', resource_type.name AS 'Ressource', product.label AS 'Produit', SUM(customer_bill_entry.qty) AS 'Qte', SUM(customer_bill_entry.debit) AS 'Prix'
FROM customer_bill_entry
LEFT JOIN account_entry ON (account_entry.flow_id=customer_bill_entry.account_entry_flow_id AND account_entry.id=customer_bill_entry.account_entry_id)
LEFT JOIN account ON (account.id=account_entry.account_id)
LEFT JOIN accounting ON (accounting.id=account.accounting_id)
LEFT JOIN product ON (product.id=customer_bill_entry.product_id)
LEFT JOIN flight_account_entry ON (account_entry.flow_id=flight_account_entry.account_entry_id)
LEFT JOIN flight ON (flight.id=flight_account_entry.flight_id)
LEFT JOIN resource ON (resource.id=flight.aircraft_id)
LEFT JOIN resource_type ON (resource_type.id=resource.resource_type_id)
WHERE account.category = 2
  AND accounting.id = '$accountingId'
  AND YEAR(account_entry.account_date) = '$year'
  AND MONTH(account_entry.account_date) = '$month'
GROUP BY account.id, resource_type.id, product.id

Global account balance

List whole account balance at the end of a given date :

• Variable $endDate should be defined first and should be of Date and time value type.
• Variable $accountingId should be defined first and should be of dbObject::Accounting value type.

SELECT account.id, export_account, account.name,
IFNULL(sumAccountEntry(account.id, '$endDate'), 0) AS solde
FROM account
LEFT JOIN accounting ON (accounting.id=account.accounting_id)
LEFT JOIN person ON (person.id=account.owner_id AND account.category=2)
LEFT JOIN resource ON (resource.id=account.owner_id AND account.category=3)
WHERE accounting.id='$accountingId'
  AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= '$endDate') )
ORDER BY 2, 3

Global account balance of users who last subscription was 2 years ago

• Variable $accountingId should be defined first and should be of dbObject::Accounting value type.
• Variable $validityTypeId should be defined first and should be of dbOjectMulti::ValidityType value type.

SELECT last_name, first_name, validity_type.name AS 'Validity', grant_date,
      IFNULL((SELECT  SUM(account_entry.credit)-SUM(account_entry.debit) FROM account_entry  WHERE account_entry.account_id = account.id
              AND account_entry.account_date > (SELECT balance_date  FROM balance_date ORDER BY balance_date DESC LIMIT  1)),0)+balance.credit-balance.debit AS Total
FROM person
LEFT JOIN validity ON validity.person_id = person.id
LEFT JOIN validity_type ON validity_type.id = validity.validity_type_id
LEFT JOIN account ON account.owner_id = person.id AND account.category = 2
LEFT JOIN accounting ON accounting.id=account.accounting_id
LEFT JOIN balance ON balance.account_id = account.id
          AND balance.balance_date_id = (SELECT id FROM balance_date  ORDER BY balance_date ASC LIMIT 1)
WHERE grant_date < DATE_SUB(NOW(), INTERVAL 2 YEAR)
  AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )
  AND CAST(accounting.id AS CHAR) ='$accountingId'
  AND account.activated=1
  AND person.activated=1
GROUP BY person.id, validity_type.id
ORDER BY last_name,first_name, validity_type.name

Global non null account balance

• List whole account balance at the end of a given date but for only non null account balance
• Variable $endDate should be defined first and should be of Date and time value type.

SELECT CONCAT( '[LINK=index.php?menuAction=account_journal&menuParameter=', tmp_accounts.id, ']', tmp_accounts.id, '[/LINK]' ) AS '_tr(ID)', tmp_accounts.export_account AS '_tr(ACCOUNT_EXPORT)', tmp_accounts.name AS '_tr(ACCOUNT)',
tmp_accounts.balance AS '_tr(ACCOUNT_BALANCE)' FROM (
    SELECT account.id, export_account, account.name,
    IFNULL(sumAccountEntry(account.id,'$endDate'), 0) AS balance
    FROM account
    LEFT JOIN accounting ON accounting.id=account.accounting_id 
    LEFT JOIN person ON (person.id=account.owner_id AND account.category=2)
    LEFT JOIN resource ON (resource.id=account.owner_id AND account.category=3)
    WHERE account.activated=1
        OR (account.activated=0 AND account.deactivated_date >= '$endDate')
    HAVING balance NOT IN ('0', '0.00', '0,00')
    ORDER BY export_account
) AS tmp_accounts

List of all account debit, credit

• Variable $accountingId should be defined first and should be of dbObject::Accounting value type.
• Variable $endDate should be defined first and should be of Date and time value type.

SELECT
    account.export_account AS 'Export account',
    CASE
        WHEN account_type.name IS NOT NULL AND account.category=2 THEN CONCAT('User account ', account_type.name, ' of ', person.last_name, ' ', IFNULL(person.first_name, ''))
        WHEN account_type.name IS NOT NULL AND account.category=3 THEN CONCAT('Aircraft account ', account_type.name, ' of ', resource.name)
        WHEN account.category=1 THEN CONCAT('Other account ', account.name)
        WHEN account.category=4 THEN CONCAT('Supplier account ', account.name)
        WHEN account.category=6 THEN CONCAT('VAT account ', account.name)
        WHEN account.category=7 THEN CONCAT('Expense account ', account.name)
        WHEN account.category=8 THEN CONCAT('Treasury account ', account.name)
        WHEN account.category=9 THEN CONCAT('Product account ', account.name)
        WHEN account.category=10 THEN CONCAT('Balance sheet account ', account.name)
        WHEN account.category=11 THEN CONCAT('Customer account ', account.name)
        ELSE account.name
    END AS account_name,
    IF( (@sumAccountEntry := sumAccountEntry(account.id,'$endDate')) < 0, @sumAccountEntry, 0 )  AS Debit,
    IF( @sumAccountEntry < 0, 0, @sumAccountEntry ) AS Credit
FROM account
LEFT JOIN accounting ON accounting.</