Difference between revisions of "Accounting reports 3"
m (→List of all account debit, credit) |
(→List of all account debit, credit for specific accounting) |
||
Line 197: | Line 197: | ||
account.export_account AS 'Export account', | account.export_account AS 'Export account', | ||
CASE | CASE | ||
− | WHEN account_type.name IS NOT NULL AND account.category=2 THEN CONCAT(' | + | 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_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=1 THEN CONCAT('Other account ', account.name) | ||
Line 205: | Line 205: | ||
WHEN account.category=8 THEN CONCAT('Treasury 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=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 | ELSE account.name | ||
END AS account_name, | END AS account_name, | ||
Line 211: | Line 213: | ||
FROM account | FROM account | ||
LEFT JOIN accounting ON accounting.id=account.accounting_id | LEFT JOIN accounting ON accounting.id=account.accounting_id | ||
− | LEFT JOIN account_type ON account.account_type = account_type.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 | + | 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) | |
− | LEFT JOIN resource ON | + | WHERE account.activated = 1 |
− | WHERE account.activated = 1 AND accounting.name='XXXX' | + | AND accounting.name='XXXX' |
− | GROUP BY account.id | + | 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.id | ||
ORDER BY account.export_account ASC</sql> | ORDER BY account.export_account ASC</sql> | ||
Revision as of 12:36, 12 March 2014
Contents
- 1 Introduction
- 2 Global account balance
- 3 Global account balance of users who last subscription was 2 years ago
- 4 Global non null account balance
- 5 Balances of accounts
- 6 Balance per account category
- 7 Carry forwards
- 8 Payments list ordered by type
- 9 Get the details (specially the id) of an account according is name
- 10 Get the user owner of an account
- 11 Payment dispatching
- 12 Cheque deposit slip
- 13 Monthly distribution of revenue
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 (Extra field $endDate, type dateTime, should be defined)
SELECT account.id, export_account, account.name, IFNULL(sumAccountEntry(account.id,'$endDate'), 0) AS solde FROM account 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 ( (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 export_account
Global account balance for specific accounting
Replace XXXX values by chosen accounting's label
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 account.activated=1 AND accounting.name='XXXX' 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 export_account
Global account balance of users who last subscription was 2 years ago
Variable $validityTypeId should be defined first and should be of dbOject::ValidityType value type.
SELECT last_name, first_name, validity_type.name AS 'Validity', grant_date, IFNULL((SELECT SUM(account_entry.credit)-SUM(account_entry.debit) FROM account_entry WHERE account_entry.account_id = account.id AND account_entry.account_date > (SELECT balance_date FROM balance_date ORDER BY balance_date DESC LIMIT 1)),0)+balance.credit-balance.debit AS Total FROM person LEFT JOIN validity ON validity.person_id = person.id LEFT JOIN validity_type ON validity_type.id = validity.validity_type_id LEFT JOIN account ON account.owner_id = person.id AND account.category = 2 LEFT JOIN 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 account.activated=1 AND person.activated=1 GROUP BY person.id ORDER BY last_name,first_name
Global account balance of users who last subscription was 2 years ago for specific accounting
Replace XXXX values by chosen accounting's label
SELECT last_name, first_name, validity_type.name AS 'Validity', grant_date, IFNULL((SELECT SUM(account_entry.credit)-SUM(account_entry.debit) FROM account_entry WHERE account_entry.account_id = account.id AND account_entry.account_date > (SELECT balance_date FROM balance_date ORDER BY balance_date DESC LIMIT 1)),0)+balance.credit-balance.debit AS Total FROM person LEFT JOIN validity ON validity.person_id = person.id LEFT JOIN validity_type ON validity_type.id = validity.validity_type_id LEFT JOIN account ON account.owner_id = person.id AND account.category = 2 LEFT JOIN accounting ON accounting.id=account.accounting_id LEFT JOIN balance ON balance.account_id = account.id AND balance.balance_date_id = (SELECT id FROM balance_date ORDER BY balance_date ASC LIMIT 1) WHERE grant_date < DATE_SUB(NOW(), INTERVAL 2 YEAR) AND validity_type.id = '$validityTypeId' AND accounting.name='XXXX' AND account.activated=1 AND person.activated=1 GROUP BY person.id ORDER BY last_name,first_name
Global non null account balance
- List whole account balance at the end of a given date (Extra field $endDate has to be defined and should be of DateTime value type) but for only non null account balance :
SELECT account.id, export_account, account.name, IFNULL(sumAccountEntry(account.id,'$endDate'), 0) AS solde FROM account 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 ( (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) ) HAVING solde <> 0 ORDER BY export_account
- List whole account balance at the end of a given year (Extra field $year has to be defined and should be of Year value type) but for only non null account balance :
SELECT id, export_account, name, sumAccountEntry(id,'$year-12-31 23:59:59') AS solde FROM account WHERE activated=1 HAVING solde <> 0 ORDER BY export_account
Global non null account balance for specific accounting
Replace XXXX values by chosen accounting's label
SELECT a.id, export_account, a.name, sumAccountEntry(a.id,'$year-12-31 23:59:59') AS solde FROM account AS a LEFT JOIN accounting ON accounting.id=a.accounting_id WHERE activated=1 AND accounting.name='XXXX' HAVING solde <> 0 ORDER BY export_account
Balances of accounts
Extra field $endDate has to be defined first and should be of value type DateTime.
Balances of user accounts
Balances of user accounts
SELECT person.last_name, person.first_name, account_type.name AS account_type_name, sumAccountEntry(account.id, '$endDate') AS balance FROM account 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 AND person.activated=1 ORDER BY last_name, first_name
Balances of user accounts whose got a specific profile
Replace X and Y by right profile id.
SELECT person.last_name AS NOM, person.first_name AS PRENOM, profile.name AS Profil, IFNULL(sumAccountEntry(account.id,NOW()),0) AS Solde FROM account LEFT JOIN person ON person.id=account.owner_id LEFT JOIN profile ON (person.profile & profile.id) WHERE (person.activated = 1) AND (account.activated = 1) AND ((profile.id = X) OR (profile.id = Y)) ORDER BY NOM, PRENOM
Balances of user accounts for specific accounting
Replace XXXX values by chosen accounting's label
SELECT person.last_name, person.first_name, account_type.name AS account_type_name, 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 AND person.activated=1 AND accounting.name='XXXX' ORDER BY last_name, first_name
List of resource account balance
SELECT account.name AS resource, account_type.name AS account_type_name, sumAccountEntry(account.id, '$endDate') AS balance FROM account LEFT JOIN account_type ON (account.account_type=account_type.id) LEFT JOIN resource ON (account.owner_id=resource.id) WHERE account.category=3 AND account.activated=1 AND resource.activated=1 ORDER BY account.name
List of all account debit, credit
List whole account balance at the end of a given date (Extra field $endDate, type dateTime, should be defined)
SELECT account.export_account AS 'Export account', CASE WHEN account_type.name IS NOT NULL AND account.category=2 THEN CONCAT('User account ', account_type.name, ' of ', person.last_name, ' ', IFNULL(person.first_name, '')) WHEN account_type.name IS NOT NULL AND account.category=3 THEN CONCAT('Aircraft account ', account_type.name, ' of ', resource.name) WHEN account.category=1 THEN CONCAT('Other account ', account.name) WHEN account.category=4 THEN CONCAT('Supplier account ', account.name) WHEN account.category=6 THEN CONCAT('VAT account ', account.name) WHEN account.category=7 THEN CONCAT('Expense account ', account.name) WHEN account.category=8 THEN CONCAT('Treasury account ', account.name) WHEN account.category=9 THEN CONCAT('Product account ', account.name) WHEN account.category=10 THEN CONCAT('Balance sheet account ', account.name) WHEN account.category=11 THEN CONCAT('Customer account ', account.name) ELSE account.name END AS account_name, IF (sumAccountEntry(account.id,'$endDate')<0, sumAccountEntry(account.id,'$endDate'), 0) AS Debit, IF (sumAccountEntry(account.id,'$endDate')<0, 0, sumAccountEntry(account.id,'$endDate') ) AS Credit FROM account 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 ( (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.id ORDER BY account.export_account ASC
List of all account debit, credit for specific accounting
Replace XXXX values by chosen accounting's label
SELECT account.export_account AS 'Export account', CASE WHEN account_type.name IS NOT NULL AND account.category=2 THEN CONCAT('User account ', account_type.name, ' of ', person.last_name, ' ', IFNULL(person.first_name, '')) WHEN account_type.name IS NOT NULL AND account.category=3 THEN CONCAT('Aircraft account ', account_type.name, ' of ', resource.name) WHEN account.category=1 THEN CONCAT('Other account ', account.name) WHEN account.category=4 THEN CONCAT('Supplier account ', account.name) WHEN account.category=6 THEN CONCAT('VAT account ', account.name) WHEN account.category=7 THEN CONCAT('Expense account ', account.name) WHEN account.category=8 THEN CONCAT('Treasury account ', account.name) WHEN account.category=9 THEN CONCAT('Product account ', account.name) WHEN account.category=10 THEN CONCAT('Balance sheet account ', account.name) WHEN account.category=11 THEN CONCAT('Customer account ', account.name) ELSE account.name END AS account_name, IF (sumAccountEntry(account.id,'$endDate')<0, sumAccountEntry(account.id,'$endDate'), 0) AS Debit, IF (sumAccountEntry(account.id,'$endDate')<0, 0, sumAccountEntry(account.id,'$endDate') ) 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 account.activated = 1 AND accounting.name='XXXX' 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.id ORDER BY account.export_account ASC
Balance per account category
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) ELSE account.name END AS 'Account type', IFNULL(SUM( sumAccountEntry(account.id,NOW())),0) AS Solde FROM account LEFT JOIN account_type ON (account.account_type = account_type.id AND account.category IN(2,3)) GROUP BY account.category
Balance per account category for specific accounting
Replace XXXX values by chosen accounting's label
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) ELSE account.name END AS 'Account type', IFNULL(SUM( sumAccountEntry(account.id,NOW())),0) AS Solde FROM account LEFT JOIN accounting ON accounting.id=account.accounting_id LEFT JOIN account_type ON (account.account_type = account_type.id AND account.category IN(2,3)) WHERE accounting.name='XXXX' GROUP BY account.category
Carry forwards
SELECT IF (account.category = 2, CONCAT(person.last_name,' ',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 balance ON balance.account_id=account.id LEFT JOIN person ON person.id=account.owner_id AND account.category = 2 LEFT JOIN aircraft ON aircraft.id=account.owner_id AND account.category = 3 LEFT JOIN resource ON resource.id=aircraft.id 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) UNION SELECT 'zzzzzzz', 'Total', SUM(debit) AS 'Debit', SUM(credit) AS 'Credit' FROM account LEFT JOIN balance ON balance.account_id=account.id WHERE account.activated = 1 AND balance.balance_date_id = (SELECT balance_date.id FROM balance_date ORDER BY balance_date.id DESC LIMIT 1) ORDER BY Nom ASC
Carry forwards for specific accounting
Replace XXXX values by chosen accounting's label
SELECT IF (account.category = 2, CONCAT(person.last_name,' ',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 aircraft ON aircraft.id=account.owner_id AND account.category = 3 LEFT JOIN resource ON resource.id=aircraft.id 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.name='XXXX' UNION SELECT 'zzzzzzz', 'Total', SUM(debit) AS 'Debit', SUM(credit) AS 'Credit' FROM account LEFT JOIN accounting ON accounting.id=account.accounting_id LEFT JOIN balance ON balance.account_id=account.id 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.name='XXXX' ORDER BY Nom ASC
Payments list ordered by type
SELECT payment_type.name AS Type, if(account.category = 2, CONCAT( p.last_name, ' ', p.first_name),account.name) AS Name, DATE_FORMAT(ae.account_date, '%d %m %Y' ) AS Date, ae.payment_description, ae.credit AS Description FROM account_entry AS ae LEFT JOIN payment_type ON payment_type.id = ae.payment_type LEFT JOIN account ON account.id = ae.account_id LEFT JOIN person AS p ON p.id = account.owner_id WHERE ae.payment_type IS NOT NULL AND credit > 0 AND YEAR(ae.account_date) >= $year ORDER BY ae.payment_type, ae.account_date, p.last_name, p.first_name
Get the details (specially the id) of an account according is name
SELECT * FROM `account` WHERE `name` LIKE 'account name to search'
Get the user owner of an account
SELECT * FROM person RIGHT JOIN account ON person.id=account.owner_id WHERE account.id=114
Payment dispatching
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
Cheque deposit slip
SELECT date_format(ae.registration_date, "%d/%m/%Y") as "date", a.name, 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
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
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 UNION SELECT 'Total', ( 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 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