Introduction

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

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, formatDecimal(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') )

UNION SELECT , '_', 'Total', formatDecimal(SUM(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 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 dbOject::ValidityType value type.

<syntaxhighlight lang="sql">SELECT last_name, first_name, validity_type.name AS 'Validity', grant_date,

     formatDecimal(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 = '$validityTypeId'
 AND accounting.id='$accountingId'
 AND account.activated=1
 AND person.activated=1

GROUP BY person.id ORDER BY last_name,first_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">SELECT account.id, export_account, account.name, formatDecimal(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 account.activated=1

  OR (account.activated=0 AND account.deactivated_date >= '$endDate')

HAVING solde NOT IN ('0', '0.00', '0,00') ORDER BY export_account</sql>

Balances of 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.

Balances of resource accounts

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

Balances of user accounts

<syntaxhighlight lang="sql">SELECT person.last_name, person.first_name, account_type.name AS account_type_name, formatDecimal(sumAccountEntry(account.id, '$endDate')) AS 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 last_name, first_name</sql>

Balances of user accounts whose got a specific profile

Variable $profileId should be defined first and should be of dbObject::Profile value type.

<syntaxhighlight lang="sql">SELECT

   person.last_name AS NOM,
   person.first_name AS PRENOM,
   profile.name AS Profil,
   formatDecimal(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 LEFT JOIN profile ON (person.profile & profile.id) WHERE account.category=2

 AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= '$endDate') )
 AND accounting.id=$accountingId
 AND profile.id = $profileId

ORDER BY NOM, PRENOM</sql>

Resource account balance

<syntaxhighlight lang="sql">[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.virtual=0 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'</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">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,
   formatDecimal( IF( (@sumAccountEntry := sumAccountEntry(account.id,'$endDate')) < 0, @sumAccountEntry, 0 ) )  AS Debit,
   formatDecimal( IF( @sumAccountEntry < 0, 0, @sumAccountEntry ) ) AS Credit

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 accounting.id=$accountingId

 AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= '$endDate') )

GROUP BY account.id ORDER BY account.export_account ASC</sql>

Balance per account category

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

<syntaxhighlight lang="sql">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',
   formatDecimal(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</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,
   formatDecimal(debit) AS 'Debit',
   formatDecimal(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)
 )

UNION SELECT

   'zzzzzzz',
   'Total',
   formatDecimal(SUM(debit)) AS 'Debit',
   formatDecimal(SUM(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 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>

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 Integer value type.
Variable $year should be defined first and should be of Year value type.

<syntaxhighlight lang="sql">SELECT account.name AS 'Client', resource_type.name AS 'Ressource', product.label AS 'Produit', formatDecimal(SUM(customer_bill_entry.qty)) AS 'Qte', formatDecimal(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</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, formatDecimal( 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', formatDecimal( 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>

Payments 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 'Type',

      IF(account.category = 2, CONCAT( person.last_name, ' ', person.first_name), account.name) AS Name, 
      DATE_FORMAT(account_entry.account_date, '%d %m %Y' ) AS Date, 
      account_entry.payment_description, formatDecimal(account_entry.credit) AS 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 account_entry.payment_type, account_entry.account_date, person.last_name, person.first_name</sql>

Get the details (specially the id) of an account according is 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>

Payment dispatching

 <SQL>SELECT
 payment_type as Num, payment_type.name as name,
 formatDecimal((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,
 formatDecimal((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,
 formatDecimal((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,
 formatDecimal((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,
 formatDecimal((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,
 formatDecimal((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,
 formatDecimal((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,
 formatDecimal((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,
 formatDecimal((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,
 formatDecimal((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,
 formatDecimal((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,
 formatDecimal((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,
 formatDecimal(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',
 formatDecimal((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)),
 formatDecimal((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)),
 formatDecimal((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)),
 formatDecimal((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)),
 formatDecimal((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)),
 formatDecimal((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)),
 formatDecimal((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)),
 formatDecimal((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)),
 formatDecimal((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)),
 formatDecimal((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)),
 formatDecimal((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)),
 formatDecimal((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)),
 formatDecimal(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>

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>

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,

   formatDecimal( ( IF(febSum<0, febSum, 0) - IF(janSum<0, janSum, 0) + IF(febSum<0, 0, febSum) - IF(janSum<0, 0, janSum) ) ) AS Janu,
   formatDecimal( ( IF(marSum<0, marSum, 0) - IF(febSum<0, febSum, 0) + IF(marSum<0, 0, marSum) - IF(febSum<0, 0, febSum) ) ) AS Febr,
   formatDecimal( ( IF(aprSum<0, aprSum, 0) - IF(marSum<0, marSum, 0) + IF(aprSum<0, 0, aprSum) - IF(marSum<0, 0, marSum) ) ) AS Marc,
   formatDecimal( ( IF(maySum<0, maySum, 0) - IF(aprSum<0, aprSum, 0) + IF(maySum<0, 0, maySum) - IF(aprSum<0, 0, aprSum) ) ) AS April,
   formatDecimal( ( IF(junSum<0, junSum, 0) - IF(maySum<0, maySum, 0) + IF(junSum<0, 0, junSum) - IF(maySum<0, 0, maySum) ) ) AS May,
   formatDecimal( ( IF(julSum<0, julSum, 0) - IF(junSum<0, junSum, 0) + IF(julSum<0, 0, julSum) - IF(junSum<0, 0, junSum) ) ) AS June,
   formatDecimal( ( IF(augSum<0, augSum, 0) - IF(julSum<0, julSum, 0) + IF(augSum<0, 0, augSum) - IF(julSum<0, 0, julSum) ) ) AS July,
   formatDecimal( ( IF(sepSum<0, sepSum, 0) - IF(augSum<0, augSum, 0) + IF(sepSum<0, 0, sepSum) - IF(augSum<0, 0, augSum) ) ) AS Augu,
   formatDecimal( ( IF(octSum<0, octSum, 0) - IF(sepSum<0, sepSum, 0) + IF(octSum<0, 0, octSum) - IF(sepSum<0, 0, sepSum) ) ) AS Sept,
   formatDecimal( ( IF(novSum<0, novSum, 0) - IF(octSum<0, octSum, 0) + IF(novSum<0, 0, novSum) - IF(octSum<0, 0, octSum) ) ) AS Octo,
   formatDecimal( ( IF(decSum<0, decSum, 0) - IF(novSum<0, novSum, 0) + IF(decSum<0, 0, decSum) - IF(novSum<0, 0, novSum) ) ) AS Nove,
   formatDecimal( ( 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 UNION SELECT 'Total',

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

FROM (

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

) AS sumAccountEntryForAccount</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, formatDecimal(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>

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>

Accounting reports 3

Contents

Introduction

Global account balance

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

Global non null account balance

Balances of accounts

Balances of resource accounts

Balances of user accounts

Balances of user accounts whose got a specific profile

Resource account balance

List of all account debit, credit

Balance per account category

Carry forwards

Customer bill per month and per accounting

Movements total per account between two dates

Payments list ordered by type

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

Get the user owner of an account

Payment dispatching

Cheque deposit slip

Monthly distribution of revenue

Non balanced flow

online payment attempts list

Supplier bill

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

Navigation menu

Accounting reports 3

Introduction

Global account balance

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

Global non null account balance

Balances of accounts

Balances of resource accounts

Balances of user accounts

Balances of user accounts whose got a specific profile

Resource account balance

List of all account debit, credit

Balance per account category

Carry forwards

Customer bill per month and per accounting

Movements total per account between two dates

Payments list ordered by type

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

Get the user owner of an account

Payment dispatching

Cheque deposit slip

Monthly distribution of revenue

Non balanced flow

online payment attempts list

Supplier bill

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

Navigation menu

Search