Revision as of 17:10, 18 January 2022

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 \'_tr(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 \'_tr(TOTAL)\'
    FROM resource WHERE resource.physical=1 AND resource.activated=1 GROUP BY resource.id'
), '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

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

Category account balance

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

SELECT 
    CASE
        WHEN account.category=1 THEN _tr(OTHER_ACCOUNT)
        WHEN account_type.name IS NOT NULL AND (account.category=2) THEN CONCAT(_tr(MEMBER_ACCOUNT), ' - ', account_type.name)
        WHEN account_type.name IS NOT NULL AND (account.category=3) THEN CONCAT(_tr(RESOURCE_ACCOUNT), ' - ', account_type.name)
        WHEN account.category=4 THEN _tr(SUPPLIER_ACCOUNT)
        WHEN account.category=6 THEN _tr(ACCOUNT_VAT_ACCOUNT)
        WHEN account.category=7 THEN _tr(ACCOUNT_EXPENSE_ACCOUNT)
        WHEN account.category=8 THEN _tr(TREASURY_ACCOUNT)
        WHEN account.category=9 THEN _tr(PRODUCT_ACCOUNT)
        WHEN account.category=10 THEN _tr(BALANCE_SHEET_ACCOUNT)
        WHEN account.category=11 THEN _tr(CUSTOMER_ACCOUNT)
        ELSE account.name
    END AS _tr(ACCOUNT_TYPE),
    IFNULL(SUM( sumAccountEntry(account.id,NOW())),0) AS _tr(ACCOUNT_BALANCE)
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

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 between date

• Variable $endDate should be defined first and should be of Date and time value type
• Variable $startDate should be defined first and should be of Date and time value type

SELECT customer_bill_entry.bill_date AS _tr(DATE), CONCAT( '[LINK=index.php[QUESTION_MARK]menuAction=download_customer_bill&menuParameter=', customer_bill_entry.flow_id, ']', customer_bill_entry.customer_bill_id, '[/LINK]' ) AS _tr(BILL)
FROM customer_bill_entry
WHERE customer_bill_entry.bill_date >= $startDate AND customer_bill_entry.bill_date < $endDate
GROUP BY customer_bill_entry.customer_bill_id
ORDER BY customer_bill_entry.customer_bill_id

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 _tr(CUSTOMER), resource_type.name AS _tr(RESOURCE), product.label AS _tr(PRODUCT), <