Accounting exports

From OpenFlyers Documentation
Revision as of 11:12, 31 August 2024 by Claratte (talk | contribs) (Claratte moved page Accounting exports 4 to Accounting exports without leaving a redirect)
Jump to navigation Jump to search

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.

<syntaxhighlight lang="sql">[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</sql>

Breakdown by resource accounts of customer invoices

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

<syntaxhighlight lang="sql">SELECT customer_bill_entry.bill_date AS _tr(DATE), CONCAT(resource_account_type.name, ' - ', resource_account.name) AS _tr(RESOURCE_ACCOUNT), 

   product.label AS _tr(PRODUCT),
   customer_bill_entry.debit AS _tr(DEBIT),
   customer_bill_entry.credit AS _tr(CREDIT),
   CONCAT(person_account_type.name, ' - ', person.last_name, ' ', IFNULL(person.first_name, )) AS  _tr(ACCOUNT_MEMBER_ACCOUNT)

FROM customer_bill_entry

LEFT JOIN person ON person.id = customer_bill_entry.owner_id AND customer_bill_entry.owner_category = 2

LEFT JOIN account_entry AS person_account_entry ON person_account_entry.id = customer_bill_entry.account_entry_id LEFT JOIN account AS person_account ON person_account.id = person_account_entry.account_id LEFT JOIN account_type AS person_account_type ON person_account_type.id = person_account.account_type

LEFT JOIN product ON product.id = customer_bill_entry.product_id

INNER JOIN account_entry AS resource_account_entry ON resource_account_entry.flow_id = customer_bill_entry.account_entry_flow_id INNER JOIN account AS resource_account ON resource_account.id = resource_account_entry.account_id AND resource_account.category = 3 LEFT JOIN account_type AS resource_account_type ON resource_account_type.id = resource_account.account_type

WHERE bill_date >= $startDate AND bill_date < $endDate ORDER BY bill_date;</sql>

Carry forwards

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

<syntaxhighlight lang="sql">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</sql>

Category account balance

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

<syntaxhighlight lang="sql">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</sql>

Cheque deposit slip

<syntaxhighlight lang="sql">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</sql>

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

<syntaxhighlight lang="sql">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</sql>

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.

<syntaxhighlight lang="sql">SELECT account.name AS _tr(CUSTOMER), resource_type.name AS _tr(RESOURCE), product.label AS _tr(PRODUCT), SUM(customer_bill_entry.qty) AS _tr(QUANTITY), SUM(customer_bill_entry.debit) AS _tr(AMOUNT) 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 ORDER BY account.name</sql>

Get the details (specially the id) of an account according its name

<SQL>SELECT * FROM `account` WHERE `name` LIKE 'account name to search'</SQL>

Get the user owner of an account

<SQL>SELECT * FROM person RIGHT JOIN account ON person.id=account.owner_id WHERE account.id=114</SQL>

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.

<syntaxhighlight lang="sql">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</sql>

Global account balance last subscription

  • 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.

<syntaxhighlight lang="sql">SELECT 

   CONCAT(last_name, ' ', first_name) AS _tr(FULL_NAME),
   validity_type.name AS _tr(VALIDITY),
   grant_date AS _tr(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 AND validity.is_current_validity = 1 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
 AND validity_type.activated = 1

GROUP BY person.id, validity_type.id ORDER BY `_tr(FULL_NAME)`, validity_type.name</sql>

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.
  • Variable $accountingId should be defined first and should be of dbObject::Accounting value type.

<syntaxhighlight lang="sql">[OF_DYNAMIC_SQL] SELECT CONCAT( 

   'SELECT 
   CONCAT( \'[LINK=index.php?menuAction=account_journal&menuParameter=\', accountsSum.account_id, \']\', accountsSum.account_id, \'[/LINK]\' ) AS _tr(ID),
   accountLayout.export_account AS _tr(ACCOUNT_EXPORT),

CASE WHEN account_type.name IS NOT NULL AND accountLayout.category=2 THEN CONCAT(_tr(MEMBER_ACCOUNT), \' \', account_type.name, \' \', _tr(REPORTS_OF), \' \', person.last_name, \' \', IFNULL(person.first_name, \'\')) WHEN account_type.name IS NOT NULL AND accountLayout.category=3 THEN CONCAT(_tr(AIRCRAFT_ACCOUNT), \' \', account_type.name, \' \', _tr(REPORTS_OF), \' \', resource.name) WHEN accountLayout.category=1 THEN CONCAT(_tr(OTHER_ACCOUNT), \' \', accountLayout.name) WHEN accountLayout.category=4 THEN CONCAT(_tr(SUPPLIER_ACCOUNT), \' \', accountLayout.name) WHEN accountLayout.category=6 THEN CONCAT(_tr(VAT_ACCOUNT), \' \', accountLayout.name) WHEN accountLayout.category=7 THEN CONCAT(_tr(EXPENSE_ACCOUNT), \' \', accountLayout.name) WHEN accountLayout.category=8 THEN CONCAT(_tr(TREASURY_ACCOUNT), \' \', accountLayout.name) WHEN accountLayout.category=9 THEN CONCAT(_tr(PRODUCT_ACCOUNT), \' \', accountLayout.name) WHEN accountLayout.category=10 THEN CONCAT(_tr(BALANCE_SHEET_ACCOUNT), \' \', accountLayout.name) WHEN accountLayout.category=11 THEN CONCAT(_tr(CUSTOMER_ACCOUNT), \' \', accountLayout.name) ELSE accountLayout.name END AS _tr(ACCOUNT), accountsSum.SOLDE AS _tr(ACCOUNT_BALANCE) FROM ( SELECT accountEntriesExtract.account_id, (IFNULL(SUM(accountEntriesExtract.credit), 0.00) - IFNULL(SUM(accountEntriesExtract.debit), 0.00) ) AS SOLDE FROM ( (SELECT account_entry.account_id, IFNULL(account_entry.debit, 0.00) AS debit, IFNULL(account_entry.credit, 0.00) AS credit FROM account_entry INNER JOIN account ON (account.id = account_entry.account_id AND account.accounting_id=$accountingId AND (account.activated = 1 OR (account.activated=0 AND account.deactivated_date >= $endDate))) WHERE account_date >= \, balance_date.balance_date ,'\' AND account_date < $endDate ) UNION ALL ( SELECT account_id, IFNULL(balance.debit, 0.00) AS debit, IFNULL(balance.credit, 0.00) AS credit FROM balance INNER JOIN account ON (account.id = balance.account_id AND account.accounting_id=$accountingId AND (account.activated = 1 OR (account.activated=0 AND account.deactivated_date >= $endDate))) WHERE balance_date_id = ', balance_date.id, ' ) ) AS accountEntriesExtract GROUP BY accountEntriesExtract.account_id 

       HAVING SOLDE NOT IN (\'0 \', \'0.00\', \'0,00\')

) AS accountsSum INNER JOIN account accountLayout ON (accountLayout.id = accountsSum.account_id AND accountLayout.accounting_id=$accountingId AND (accountLayout.activated = 1 OR (accountLayout.activated=0 AND accountLayout.deactivated_date >= $endDate))) LEFT JOIN account_type ON (accountLayout.account_type = account_type.id AND accountLayout.category IN(2,3)) LEFT JOIN person ON (person.id=accountLayout.owner_id AND accountLayout.category=2) LEFT JOIN resource ON (resource.id=accountLayout.owner_id AND accountLayout.category=3) 

   GROUP BY accountLayout.id 
   ORDER BY accountLayout.export_account ASC
   '

) FROM balance_date WHERE balance_date.balance_date < $endDate ORDER BY balance_date DESC LIMIT 1 </sql>

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.

<syntaxhighlight lang="sql">[OF_DYNAMIC_SQL] SELECT CONCAT( 

   'SELECT accountLayout.export_account AS _tr(ACCOUNT_EXPORT),

CASE WHEN account_type.name IS NOT NULL AND accountLayout.category=2 THEN CONCAT(_tr(MEMBER_ACCOUNT), \' \', account_type.name, \' \', _tr(REPORTS_OF), \' \', person.last_name, \' \', IFNULL(person.first_name, \'\')) WHEN account_type.name IS NOT NULL AND accountLayout.category=3 THEN CONCAT(_tr(AIRCRAFT_ACCOUNT), \' \', account_type.name, \' \', _tr(REPORTS_OF), \' \', resource.name) WHEN accountLayout.category=1 THEN CONCAT(_tr(OTHER_ACCOUNT), \' \', accountLayout.name) WHEN accountLayout.category=4 THEN CONCAT(_tr(SUPPLIER_ACCOUNT), \' \', accountLayout.name) WHEN accountLayout.category=6 THEN CONCAT(_tr(VAT_ACCOUNT), \' \', accountLayout.name) WHEN accountLayout.category=7 THEN CONCAT(_tr(EXPENSE_ACCOUNT), \' \', accountLayout.name) WHEN accountLayout.category=8 THEN CONCAT(_tr(TREASURY_ACCOUNT), \' \', accountLayout.name) WHEN accountLayout.category=9 THEN CONCAT(_tr(PRODUCT_ACCOUNT), \' \', accountLayout.name) WHEN accountLayout.category=10 THEN CONCAT(_tr(BALANCE_SHEET_ACCOUNT), \' \', accountLayout.name) WHEN accountLayout.category=11 THEN CONCAT(_tr(CUSTOMER_ACCOUNT), \' \', accountLayout.name) ELSE accountLayout.name END AS _tr(ACCOUNT), IF((@sumAccountEntry := (accountsSum.CREDIT - accountsSum.DEBIT)) < 0, @sumAccountEntry, 0) AS _tr(DEBIT), IF(@sumAccountEntry < 0, 0, @sumAccountEntry) AS _tr(CREDIT) FROM ( SELECT accountEntriesExtract.account_id, IFNULL(SUM(accountEntriesExtract.debit), 0.00) AS DEBIT, IFNULL(SUM(accountEntriesExtract.credit), 0.00) AS CREDIT FROM ( (SELECT account_entry.account_id, IFNULL(account_entry.debit, 0.00) AS debit, IFNULL(account_entry.credit, 0.00) AS credit FROM account_entry INNER JOIN account ON (account.id = account_entry.account_id AND account.accounting_id=$accountingId AND (account.activated = 1 OR (account.activated=0 AND account.deactivated_date >= \, balance_date.balance_date ,'\'))) WHERE account_date >= \, balance_date.balance_date ,'\' AND account_date < $endDate ) UNION ALL ( SELECT account_id, IFNULL(balance.debit, 0.00) AS debit, IFNULL(balance.credit, 0.00) AS credit FROM balance INNER JOIN account ON (account.id = balance.account_id AND account.accounting_id=$accountingId AND (account.activated = 1 OR (account.activated=0 AND account.deactivated_date >= \, balance_date.balance_date ,'\'))) WHERE balance_date_id = ', balance_date.id, ' ) ) AS accountEntriesExtract GROUP BY accountEntriesExtract.account_id ) AS accountsSum INNER JOIN account accountLayout ON (accountLayout.id = accountsSum.account_id AND accountLayout.accounting_id=$accountingId AND (accountLayout.activated = 1 OR (accountLayout.activated=0 AND accountLayout.deactivated_date >= \, balance_date.balance_date ,'\'))) LEFT JOIN account_type ON (accountLayout.account_type = account_type.id AND accountLayout.category IN(2,3)) LEFT JOIN person ON (person.id=accountLayout.owner_id AND accountLayout.category=2) LEFT JOIN resource ON (resource.id=accountLayout.owner_id AND accountLayout.category=3) 

   GROUP BY accountLayout.id 
   ORDER BY accountLayout.export_account ASC
   '

) FROM balance_date WHERE balance_date.balance_date < $endDate ORDER BY balance_date DESC LIMIT 1 </sql>

Monthly distribution of revenue

  • In french accounting, this query reports each month's revenue distribution. It only concerns product accounts (prefixed 7xx) except those that are prefixed 709xxx
  • Additional field $year is needed

<syntaxhighlight lang="sql">SELECT account_name, 

   ( IF(febSum<0, febSum, 0) - IF(janSum<0, janSum, 0) + IF(febSum<0, 0, febSum) - IF(janSum<0, 0, janSum) ) AS Janu,
   ( IF(marSum<0, marSum, 0) - IF(febSum<0, febSum, 0) + IF(marSum<0, 0, marSum) - IF(febSum<0, 0, febSum) ) AS Febr,
   ( IF(aprSum<0, aprSum, 0) - IF(marSum<0, marSum, 0) + IF(aprSum<0, 0, aprSum) - IF(marSum<0, 0, marSum) ) AS Marc,
   ( IF(maySum<0, maySum, 0) - IF(aprSum<0, aprSum, 0) + IF(maySum<0, 0, maySum) - IF(aprSum<0, 0, aprSum) ) AS April,
   ( IF(junSum<0, junSum, 0) - IF(maySum<0, maySum, 0) + IF(junSum<0, 0, junSum) - IF(maySum<0, 0, maySum) ) AS May,
   ( IF(julSum<0, julSum, 0) - IF(junSum<0, junSum, 0) + IF(julSum<0, 0, julSum) - IF(junSum<0, 0, junSum) ) AS June,
   ( IF(augSum<0, augSum, 0) - IF(julSum<0, julSum, 0) + IF(augSum<0, 0, augSum) - IF(julSum<0, 0, julSum) ) AS July,
   ( IF(sepSum<0, sepSum, 0) - IF(augSum<0, augSum, 0) + IF(sepSum<0, 0, sepSum) - IF(augSum<0, 0, augSum) ) AS Augu,
   ( IF(octSum<0, octSum, 0) - IF(sepSum<0, sepSum, 0) + IF(octSum<0, 0, octSum) - IF(sepSum<0, 0, sepSum) ) AS Sept,
   ( IF(novSum<0, novSum, 0) - IF(octSum<0, octSum, 0) + IF(novSum<0, 0, novSum) - IF(octSum<0, 0, octSum) ) AS Octo,
   ( IF(decSum<0, decSum, 0) - IF(novSum<0, novSum, 0) + IF(decSum<0, 0, decSum) - IF(novSum<0, 0, novSum) ) AS Nove,
   ( IF(nextJanSum<0, nextJanSum, 0) - IF(decSum<0, decSum, 0) + IF(nextJanSum<0, 0, nextJanSum) - IF(decSum<0, 0, decSum) ) AS Dece

FROM ( 

   SELECT IF(account.category=3, CONCAT(account.name, ' - ', account_type.name), account.name) AS 'account_name',
   sumAccountEntry(account.id, '$year-01-01') AS 'janSum',
   sumAccountEntry(account.id, '$year-02-01') AS 'febSum',
   sumAccountEntry(account.id, '$year-03-01') AS 'marSum',
   sumAccountEntry(account.id, '$year-04-01') AS 'aprSum',
   sumAccountEntry(account.id, '$year-05-01') AS 'maySum',
   sumAccountEntry(account.id, '$year-06-01') AS 'junSum',
   sumAccountEntry(account.id, '$year-07-01') AS 'julSum',
   sumAccountEntry(account.id, '$year-08-01') AS 'augSum',
   sumAccountEntry(account.id, '$year-09-01') AS 'sepSum',
   sumAccountEntry(account.id, '$year-10-01') AS 'octSum',
   sumAccountEntry(account.id, '$year-11-01') AS 'novSum',
   sumAccountEntry(account.id, '$year-12-01') AS 'decSum',
   sumAccountEntry(account.id, DATE_ADD('$year-01-01', INTERVAL 12 MONTH)) AS 'nextJanSum'
   FROM account
   LEFT JOIN account_type ON (account_type.id = account.account_type)
   WHERE account.export_account LIKE '7%' AND account.export_account NOT LIKE '709%' AND account.activated=1

) AS sumAccountEntryForAccount</sql>

Movements total per account between two dates

  • Variable $accountingId should be defined first and should be of dbOject::Accounting value type
  • Variable $endDate should be defined first and should be of Date/Date and time value type
  • Variable $startDate should be defined first and should be of Date and time value type

<syntaxhighlight lang="sql">SELECT account.id, export_account, account.name, IFNULL( SUM(account_entry.credit) - SUM(account_entry.debit), 0 ) AS 'Total' FROM account LEFT JOIN accounting ON accounting.id=account.accounting_id LEFT JOIN account_entry ON (account.id=account_entry.account_id) WHERE accounting.id=$accountingId 

 AND account_entry.account_date >= $startDate AND account_entry.account_date < $endDate
 AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= $startDate) )

GROUP BY account.id UNION SELECT , '_', 'Total', IFNULL( SUM(account_entry.credit) - SUM(account_entry.debit), 0 ) AS 'Solde' FROM account LEFT JOIN accounting ON accounting.id=account.accounting_id LEFT JOIN account_entry ON (account.id=account_entry.account_id) WHERE accounting.id=$accountingId 

 AND account_entry.account_date >= $startDate AND account_entry.account_date < $endDate
 AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= $startDate) )

ORDER BY 2, 3</sql>

Non balanced flow

<syntaxhighlight lang="sql">SELECT flow_id, SUM(debit) AS totalDebit, SUM(credit) AS totalCredit FROM account_entry GROUP BY flow_id HAVING totalDebit <> totalCredit</sql>

Online payment attempts list

Required additional field:

  • $endDate Date type
  • $startDate Date type

<syntaxhighlight lang="sql">SELECT psp_transaction.transaction_date, account.name, psp_transaction.amount, psp_transaction.state FROM psp_transaction LEFT JOIN account ON account.id=psp_transaction.credit_account_id WHERE psp_transaction.transaction_date >= $startDate AND psp_transaction.transaction_date<=$endDate ORDER BY psp_transaction.transaction_date DESC</sql>

Payment dispatching

 <SQL>SELECT payment_type AS Num, payment_type.name AS name,

(SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 1 AND payment_type = Num) AS Janu, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 2 AND payment_type = Num) AS Febr, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 3 AND payment_type = Num) AS Marc, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 4 AND payment_type = Num) AS Apri, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 5 AND payment_type = Num) AS May, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 6 AND payment_type = Num) AS June, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 7 AND payment_type = Num) AS July, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 8 AND payment_type = Num) AS Augu, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 9 AND payment_type = Num) AS Sept, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 10 AND payment_type = Num) AS Octo, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 11 AND payment_type = Num) AS Nove, (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 12 AND payment_type = Num) AS Dece, SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS Total FROM account_entry LEFT JOIN payment_type ON payment_type = payment_type.id WHERE YEAR( account_date ) = $year AND payment_type IS NOT NULL GROUP BY payment_type UNION SELECT 'Sum per', 'month', (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 1 AND payment_type IS NOT NULL), (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 2 AND payment_type IS NOT NULL), (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 3 AND payment_type IS NOT NULL), (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 4 AND payment_type IS NOT NULL), (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 5 AND payment_type IS NOT NULL), (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 6 AND payment_type IS NOT NULL), (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 7 AND payment_type IS NOT NULL), (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 8 AND payment_type IS NOT NULL), (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 9 AND payment_type IS NOT NULL), (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 10 AND payment_type IS NOT NULL), (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 11 AND payment_type IS NOT NULL), (SELECT SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS credit FROM account_entry WHERE YEAR(account_date)= $year AND MONTH(account_date)= 12 AND payment_type IS NOT NULL), SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) ) AS Total FROM account_entry LEFT JOIN payment_type ON payment_type = payment_type.id WHERE YEAR( account_date ) = $year AND payment_type IS NOT NULL</SQL>

Payment type

Payment types list ordered by type

  • Variable $startDate should be defined first and should be of Date value type.
  • Variable $endDate should be defined first and should be of Date value type.
  • Variable $paymentType should be defined first and should be of dbObject::PaymentType value type.

<syntaxhighlight lang="sql">SELECT payment_type.name AS _tr(ENCASHMENT_TYPE), 

      IF(account.category = 2, CONCAT( person.last_name, ' ', person.first_name), account.name) AS _tr(NAME), 
      DATE(account_entry.account_date) AS _tr(DATE), 
      account_entry.payment_description AS _tr(DESCRIPTION), account_entry.credit AS _tr(AMOUNT)

FROM account_entry LEFT JOIN payment_type ON payment_type.id = account_entry.payment_type LEFT JOIN account ON account.id = account_entry.account_id LEFT JOIN person ON person.id = account.owner_id WHERE 

   CASE
       WHEN ($paymentType=) THEN account_entry.payment_type IS NOT NULL
       ELSE account_entry.payment_type=$paymentType
   END
AND credit > 0 AND account_entry.account_date >= $startDate AND account_entry.account_date  <= $endDate

ORDER BY 1, 3, 2</sql>

Resource account balance

  • 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.

<syntaxhighlight lang="sql">[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'</sql>

Statement serving as invoice

  • Variable $startDate should be of Date and time value type.
  • Variable $endDate should be of Date and time value type.
  • Variable $profileId should be of Integer value type.
  • Variable $occupiedSeat should be of dbOjectMulti::Profile value type.

<syntaxhighlight lang="sql">SELECT CONCAT(person.last_name,' ',person.first_name) AS _tr(FULL_NAME), CONCAT(FLOOR(SUM( flight_list.duree )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( flight_list.duree )/600 - FLOOR(SUM( flight_list.duree )/600))*3600),'%i')) AS _tr(COMPLETED_HOURS_NUMBER), SUM(flight_list.montant) AS _tr(TOTAL_AMOUNT_ACTIVITIES) FROM person JOIN (SELECT person.id AS person_id, flight.id AS vol, flight.duration AS duree, SUM(account_entry.debit)-SUM(account_entry.credit) AS montant FROM person LEFT JOIN profile ON person.profile&profile.id LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id LEFT JOIN flight ON flight.id=flight_pilot.flight_id LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id LEFT JOIN account ON account.id=account_entry.account_id WHERE $startDate <= flight.start_date AND flight.start_date <= $endDate 

   AND (IF((($occupiedSeat=0)OR($occupiedSeat=)), 0, -1) = flight_pilot.num
   OR IF((($occupiedSeat=1)OR($occupiedSeat=)), 1, -1) = flight_pilot.num)
       AND ( profile.id IN ($profileId) OR '-' IN ($profileId) ) AND (account.category = 11 OR account.category = 2)

GROUP BY flight.id) AS flight_list ON flight_list.person_id=person.id GROUP BY person.id UNION SELECT _tr(TOTAL), 

   CONCAT( 
       (
            SELECT FLOOR(SUM(flight_list.duree)/600)
            FROM person
            JOIN
            (SELECT person.id AS person_id, flight.id AS vol, flight.duration AS duree, SUM(account_entry.debit)-SUM(account_entry.credit) AS montant FROM person

LEFT JOIN profile ON person.profile&profile.id 

            LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id
            LEFT JOIN flight ON flight.id=flight_pilot.flight_id
            LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id
            LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id
            LEFT JOIN account ON account.id=account_entry.account_id
            WHERE $startDate <= flight.start_date AND flight.start_date <= $endDate
            AND (IF((($occupiedSeat=0)OR($occupiedSeat=)), 0, -1) = flight_pilot.num
            OR IF((($occupiedSeat=1)OR($occupiedSeat=)), 1, -1) = flight_pilot.num)
               AND ( profile.id IN ($profileId) OR '-' IN ($profileId) ) AND (account.category = 11 OR account.category = 2)
            GROUP BY flight.id) AS flight_list ON flight_list.person_id=person.id
       ),':',
       TIME_FORMAT(SEC_TO_TIME((
           (
               SELECT SUM(flight_list.duree)/600
               FROM person
               JOIN
               (SELECT person.id AS person_id, flight.id AS vol, flight.duration AS duree, SUM(account_entry.debit)-SUM(account_entry.credit) AS montant FROM person

LEFT JOIN profile ON person.profile&profile.id 

               LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id
               LEFT JOIN flight ON flight.id=flight_pilot.flight_id
               LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id
               LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id
               LEFT JOIN account ON account.id=account_entry.account_id
               WHERE $startDate <= flight.start_date AND flight.start_date <= $endDate
               AND (IF((($occupiedSeat=0)OR($occupiedSeat=)), 0, -1) = flight_pilot.num
               OR IF((($occupiedSeat=1)OR($occupiedSeat=)), 1, -1) = flight_pilot.num)
                   AND ( profile.id IN ($profileId) OR '-' IN ($profileId) ) AND (account.category = 11 OR account.category = 2)
               GROUP BY flight.id) AS flight_list ON flight_list.person_id=person.id
           )
           - 
           (
               SELECT FLOOR(SUM(flight_list.duree)/600)
               FROM person
               JOIN
               (SELECT person.id AS person_id, flight.id AS vol, flight.duration AS duree, SUM(account_entry.debit)-SUM(account_entry.credit) AS montant FROM person

LEFT JOIN profile ON person.profile&profile.id 

               LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id
               LEFT JOIN flight ON flight.id=flight_pilot.flight_id
               LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id
               LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id
               LEFT JOIN account ON account.id=account_entry.account_id
               WHERE $startDate <= flight.start_date AND flight.start_date <= $endDate
               AND (IF((($occupiedSeat=0)OR($occupiedSeat=)), 0, -1) = flight_pilot.num
               OR IF((($occupiedSeat=1)OR($occupiedSeat=)), 1, -1) = flight_pilot.num)
                   AND ( profile.id IN ($profileId) OR '-' IN ($profileId) ) AND (account.category = 11 OR account.category = 2)
               GROUP BY flight.id) AS flight_list ON flight_list.person_id=person.id
           )
       )*3600),'%i')
   ),
   (
       SELECT SUM(flight_list.montant)
       FROM person
       JOIN
       (SELECT person.id AS person_id, flight.id AS vol, flight.duration AS duree, SUM(account_entry.debit)-SUM(account_entry.credit) AS montant FROM person

LEFT JOIN profile ON person.profile&profile.id 

       LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id
       LEFT JOIN flight ON flight.id=flight_pilot.flight_id
       LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id
       LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id
       LEFT JOIN account ON account.id=account_entry.account_id
       WHERE $startDate <= flight.start_date AND flight.start_date <= $endDate
       AND (IF((($occupiedSeat=0)OR($occupiedSeat=)), 0, -1) = flight_pilot.num
       OR IF((($occupiedSeat=1)OR($occupiedSeat=)), 1, -1) = flight_pilot.num)
           AND ( profile.id IN ($profileId) OR '-' IN ($profileId) ) AND (account.category = 11 OR account.category = 2)
       GROUP BY flight.id) AS flight_list ON flight_list.person_id=person.id
   )</sql>

Statement serving as invoice with flight details

  • Variable $startDate should be of Date and time value type.
  • Variable $endDate should be of Date and time value type.
  • Variable $profileId should be of Integer value type.
  • Variable $occupiedSeat should be of dbOjectMulti::Profile value type.

<syntaxhighlight lang="sql">SELECT ( SELECT CONCAT(last_name,' ',first_name) FROM person LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id WHERE flight_pilot.flight_id=flight.id AND flight_pilot.num=0

) AS _tr(LEFT_PLACE), ( SELECT CONCAT(last_name,' ',first_name) FROM person LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id WHERE flight_pilot.flight_id=flight.id AND flight_pilot.num=1

) AS _tr(RIGHT_PLACE), flight.start_date AS _tr(START_DATE), resource.name AS _tr(RESOURCE), sexa2HoursMinute(flight.duration) AS _tr(DURATION), ( SELECT GROUP_CONCAT( activity_type.name SEPARATOR ', ') FROM flight AS tmp_flight LEFT JOIN activity_type ON (tmp_flight.activity_type_id & activity_type.id) WHERE tmp_flight.id=flight.id ) AS _tr(ACTIVITY_TYPE), flight.landing_number AS _tr(FLIGHT_LANDING_NUMBER), SUM(account_entry.debit)-SUM(account_entry.credit) AS _tr(AMOUNT), business_field_content.content AS _tr(COMMENT) FROM person LEFT JOIN profile ON person.profile&profile.id LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id LEFT JOIN flight ON flight.id=flight_pilot.flight_id LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id LEFT JOIN account ON account.id=account_entry.account_id LEFT JOIN resource ON resource.id = flight.aircraft_id LEFT JOIN business_field_content ON business_field_content.category_id=flight.id LEFT JOIN business_field ON (business_field.id=business_field_content.business_field_id AND business_field.variable='activityComment') WHERE $startDate <= flight.start_date AND flight.start_date <= $endDate AND (IF((($occupiedSeat=0)OR($occupiedSeat=)), 0, -1) = flight_pilot.num OR IF((($occupiedSeat=1)OR($occupiedSeat=)), 1, -1) = flight_pilot.num) AND ( profile.id IN ($profileId) OR '-' IN ($profileId) ) AND (account.category = 11 OR account.category = 2) GROUP BY flight.id ORDER BY flight.start_date</sql>

Supplier bill

<syntaxhighlight lang="sql">SELECT * FROM supplier_bill ORDER BY bill_date</sql>

Total of debits, credits, balances of each account at the 12/31

Required additional field:

  • $year Year type

<syntaxhighlight lang="sql">SELECT id, export_account, name, @debit:=sumAccountEntryDebit(id,'$year-12-31 22:59:59') AS Debit, @credit:=sumAccountEntryCredit(id,'$year-12-31 22:59:59') AS Credit, ROUND(@credit-@debit,2) AS solde FROM account WHERE activated=1 ORDER BY export_account</sql>

User account balance

  • 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.

<syntaxhighlight lang="sql">SELECT 

   CONCAT( '[LINK=index.php[QUESTION_MARK]menuAction=account_journal&menuParameter=', account.id, ']', account.id, '[/LINK]' ) AS _tr(ID),
   CONCAT( personWithProfile.last_name, ' ', personWithProfile.first_name) AS _tr(LAST_NAME),
   personWithProfile.person_profile AS _tr(PROFILE),
   account_type.name AS _tr(ACCOUNT_TYPE),
   IFNULL(sumAccountEntry(account.id,$endDate),0) 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 ( 

   SELECT person.*, GROUP_CONCAT(profile.name ORDER BY profile.name SEPARATOR ', ') 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 personWithProfile.last_name, personWithProfile.first_name</sql>

Validated only or all entries for an account between two dates

  • Variable $startDate should be defined first and should be of Date and time value type.
  • Variable $endDate should be defined first and should be of Date and time value type.
  • Variable $accountId should be defined first and should be of dbObject::Account value type.

<syntaxhighlight lang="sql">SELECT AS _tr(DATE), AS _tr(FLOW_NUMBER), AS _tr(AFFECTED_ACCOUNTS), _tr(TEXT_BALANCE, $startDate) AS _tr(COMMENTS), 

      IF( (@startBalance := sumValidatedAccountEntry($accountId, $startDate)) < 0, @startBalance,  ) AS _tr(DEBIT),
      IF( @startBalance >= 0, @startBalance,  ) AS _tr(CREDIT)

UNION SELECT account_entry.account_date, account_entry.flow_id, 

      tmp_affected_account.merge_affected_account, 
      IF(
           flight.id,
           tmp_activity_comment.content,
           IF(
               account_entry.payment_type,
               CONCAT( payment_type.name, IF( account_entry.payment_description IS NOT NULL, CONCAT(' (', account_entry.payment_description, ')'),  ) ),
               account_entry.comments
           )
      ) AS comments,
      account_entry.debit,
      account_entry.credit

FROM account_entry LEFT JOIN account ON (account.id=account_entry.account_id) LEFT JOIN account_type ON (account_type.id=account.account_type) LEFT JOIN flight_account_entry ON (flight_account_entry.account_entry_id=account_entry.flow_id) LEFT JOIN flight ON (flight.id=flight_account_entry.flight_id) LEFT JOIN payment_type ON (payment_type.id=account_entry.payment_type) LEFT JOIN ( 

   SELECT account_entry.id,
          GROUP_CONCAT( IFNULL( affected_account.name,  ), IF( affected_account_type.name IS NOT NULL, CONCAT(' (', affected_account_type.name, ')'),  ) ) AS merge_affected_account
   FROM account_entry
   LEFT JOIN account ON (account.id=account_entry.account_id)
   LEFT JOIN account_entry AS affected_account_entry ON (affected_account_entry.flow_id=account_entry.flow_id)
   LEFT JOIN account AS affected_account ON (affected_account.id=affected_account_entry.account_id)
   LEFT JOIN account_type AS affected_account_type ON (affected_account_type.id=affected_account.account_type)
   WHERE account_entry.account_id=$accountId
     AND account_entry.account_date>=$startDate AND account_entry.account_date<$endDate
     AND  (account_entry.validated=1 OR $validatedOnly=0)
     AND account.id <> affected_account.id
   GROUP BY account_entry.id
   ORDER BY account_entry.account_date, account_entry.flow_id, account_entry.product_id

) AS tmp_affected_account ON (tmp_affected_account.id=account_entry.id) LEFT JOIN ( 

   SELECT category_id, business_field_content.content
   FROM business_field_content
   LEFT JOIN business_field ON business_field.id=business_field_content.business_field_id
   WHERE business_field.variable='activityComment'

) AS tmp_activity_comment ON (flight.id=tmp_activity_comment.category_id) WHERE tmp_affected_account.id IS NOT NULL GROUP BY account_entry.id UNION SELECT '_', , , _tr(TEXT_BALANCE, $endDate), 

      IF( (@endBalance := sumValidatedAccountEntry($accountId, $endDate)) < 0, @endBalance,  ),
      IF( @endBalance >= 0, @endBalance,  )

ORDER BY 1, 2</sql>

Retrieved from "https://doc-en-mirror.openflyers.com/index.php?title=Accounting-exports&oldid=333"