Accounting reports 3

From OpenFlyers Documentation
Revision as of 18:53, 12 August 2024 by imported>Claratte (Text replacement - "<sql>" to "<syntaxhighlight lang="sql">")
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

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>