Difference between revisions of "Accounting exports 4"
(→Customer bill between date) |
(→Validated entries for an account between two dates) |
||
Line 624: | Line 624: | ||
*Variable '''$accountId''' should be defined first and should be of '''dbObject::Account''' value type. | *Variable '''$accountId''' should be defined first and should be of '''dbObject::Account''' value type. | ||
− | <sql>SELECT '' AS 'Date', '' AS 'Numero flux', '' AS 'Comptes affectés', CONCAT( 'Solde au ', | + | <sql>SELECT '' AS 'Date', '' AS 'Numero flux', '' AS 'Comptes affectés', CONCAT( 'Solde au ', $startDate ) AS 'Commentaires', |
− | IF( (@startBalance := sumValidatedAccountEntry( | + | IF( (@startBalance := sumValidatedAccountEntry($accountId, $startDate)) < 0, @startBalance, '' ) AS 'Débit', |
IF( @startBalance >= 0, @startBalance, '' ) AS 'Crédit' | IF( @startBalance >= 0, @startBalance, '' ) AS 'Crédit' | ||
UNION | UNION | ||
Line 655: | Line 655: | ||
LEFT JOIN account AS affected_account ON (affected_account.id=affected_account_entry.account_id) | LEFT JOIN account AS affected_account ON (affected_account.id=affected_account_entry.account_id) | ||
LEFT JOIN account_type AS affected_account_type ON (affected_account_type.id=affected_account.account_type) | LEFT JOIN account_type AS affected_account_type ON (affected_account_type.id=affected_account.account_type) | ||
− | WHERE account_entry.account_id= | + | WHERE account_entry.account_id=$accountId |
− | AND account_entry.account_date>= | + | AND account_entry.account_date>=$startDate AND account_entry.account_date<$endDate |
AND account_entry.validated=1 | AND account_entry.validated=1 | ||
AND account.id <> affected_account.id | AND account.id <> affected_account.id | ||
Line 671: | Line 671: | ||
GROUP BY account_entry.id | GROUP BY account_entry.id | ||
UNION | UNION | ||
− | SELECT '_', '', '', CONCAT( 'Solde au ', | + | SELECT '_', '', '', CONCAT( 'Solde au ', $endDate ), |
− | IF( (@endBalance := sumValidatedAccountEntry( | + | IF( (@endBalance := sumValidatedAccountEntry($accountId, $endDate)) < 0, @endBalance, '' ), |
IF( @endBalance >= 0, @endBalance, '' ) | IF( @endBalance >= 0, @endBalance, '' ) | ||
ORDER BY 1, 2</sql> | ORDER BY 1, 2</sql> |
Revision as of 18:06, 5 October 2021
Contents
- 1 Introduction
- 2 Balances of resource accounts
- 3 Carry forwards
- 4 Category account balance
- 5 Cheque deposit slip
- 6 Customer bill between date
- 7 Customer bill per month and per accounting
- 8 Get the details (specially the id) of an account according its name
- 9 Get the user owner of an account
- 10 Global account balance
- 11 Global account balance last subscription
- 12 Global non null account balance
- 13 List of all account debit, credit
- 14 Monthly distribution of revenue
- 15 Movements total per account between two dates
- 16 Non balanced flow
- 17 Online payment attempts list
- 18 Payment dispatching
- 19 Payment type
- 20 Resource account balance
- 21 Statement serving as invoice
- 22 Statement serving as invoice with flight details
- 23 Supplier bill
- 24 Total of debits, credits, balances of each account at the 12/31
- 25 User account balance
- 26 Validated entries for an account between two dates
Introduction
This page lists SQL requests for OpenFlyers release 4 export about Accounting.
Balances of resource accounts
- Variable $accountingId should be defined first and should be of dbObject::Accounting value type.
- Variable $endDate should be defined first and should be of value type Date and time value type.
[OF_DYNAMIC_SQL] SELECT IFNULL(CONCAT( 'SELECT resource.name \'_tr(NAME)\',', GROUP_CONCAT( CONCAT( ' (SELECT sumAccountEntry(account.id, '$endDate') AS balance FROM account LEFT JOIN account_type ON (account.account_type=account_type.id) WHERE account_type.activated=1 AND account.category=3 AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= '$endDate') ) AND account.owner_id=resource.id AND account.account_type=', account_type.id, ') AS \'', REPLACE(account_type.name, '\'', '\\\''), '\'' ) ), ', (SELECT SUM(sumAccountEntry(account.id, '$endDate')) AS balance FROM account LEFT JOIN account_type ON (account.account_type=account_type.id) WHERE account_type.activated=1 AND account.category=3 AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= '$endDate') ) AND account.owner_id=resource.id GROUP BY account.owner_id ) AS \'_tr(TOTAL)\' FROM resource WHERE resource.physical=1 AND resource.activated=1 GROUP BY resource.id' ), 'SELECT \'\'') FROM account_type LEFT JOIN accounting ON accounting.id=account_type.accounting_id WHERE account_type.category=3 AND account_type.activated=1 AND accounting.id=$accountingId
Carry forwards
- Variable $accountingId should be defined first and should be of dbObject::Accounting value type.
SELECT IF (account.category = 2, CONCAT(person.last_name,' ',IFNULL(person.first_name, ''),' (',account_type.name,')'), IF (account.category = 3,CONCAT(resource.name,' (',account_type.name,')'),account.name)) AS Nom, export_account AS Code_comptable, debit AS 'Debit', credit AS 'Credit' FROM account LEFT JOIN accounting ON accounting.id=account.accounting_id LEFT JOIN balance ON balance.account_id=account.id LEFT JOIN person ON person.id=account.owner_id AND account.category = 2 LEFT JOIN resource ON resource.id=account.owner_id AND account.category = 3 LEFT JOIN account_type ON account_type.id=account.account_type WHERE account.activated = 1 AND balance.balance_date_id = (SELECT balance_date.id FROM balance_date ORDER BY balance_date.id DESC LIMIT 1) AND accounting.id='$accountingId' AND ( (person.id IS NULL AND resource.id IS NULL) OR (person.activated=1 AND account.category=2) OR (resource.activated=1 AND account.category=3) ) ORDER BY Nom ASC
Category account balance
- Variable $accountingId should be defined first and should be of dbObject::Accounting value type.
SELECT CASE WHEN account.category=1 THEN '_tr(OTHER_ACCOUNT)' WHEN account_type.name IS NOT NULL AND (account.category=2) THEN CONCAT('_tr(MEMBER_ACCOUNT)', ' - ', account_type.name) WHEN account_type.name IS NOT NULL AND (account.category=3) THEN CONCAT('_tr(RESOURCE_ACCOUNT)', ' - ', account_type.name) WHEN account.category=4 THEN '_tr(SUPPLIER_ACCOUNT)' WHEN account.category=6 THEN '_tr(ACCOUNT_VAT_ACCOUNT)' WHEN account.category=7 THEN '_tr(ACCOUNT_EXPENSE_ACCOUNT)' WHEN account.category=8 THEN '_tr(TREASURY_ACCOUNT)' WHEN account.category=9 THEN '_tr(PRODUCT_ACCOUNT)' WHEN account.category=10 THEN '_tr(BALANCE_SHEET_ACCOUNT)' WHEN account.category=11 THEN '_tr(CUSTOMER_ACCOUNT)' ELSE account.name END AS '_tr(ACCOUNT_TYPE)', IFNULL(SUM( sumAccountEntry(account.id,NOW())),0) AS '_tr(ACCOUNT_BALANCE)' FROM account LEFT JOIN accounting ON accounting.id=account.accounting_id LEFT JOIN account_type ON (account.account_type = account_type.id AND account.category IN(2,3)) LEFT JOIN person ON (account.owner_id = person.id AND account.category=2) LEFT JOIN resource ON (account.owner_id = resource.id AND account.category=3) WHERE account.activated = 1 AND accounting.id='$accountingId' AND ( (person.id IS NULL AND resource.id IS NULL) OR (person.activated=1 AND account.category=2) OR (resource.activated=1 AND account.category=3) ) GROUP BY account.category
Cheque deposit slip
SELECT date_format(ae.registration_date, "%d/%m/%Y") as "date", a.name, formatDecimal(ae.credit) as montant, ae.payment_description as libelle, (SELECT a2.name FROM account_entry ae2 JOIN account a2 ON a2.id = ae2.account_id WHERE ae2.flow_id = ae.flow_id AND ae2.debit > 0 LIMIT 0,1) as compte_banque, ae.comments as commentaire FROM account_entry ae JOIN account a ON ae.account_id = a.id WHERE a.account_type = 1 AND ae.validated = 1 AND ae.credit > 0 ORDER BY ae.registration_date DESC
Customer bill between date
- Variable $endDate should be defined first and should be of Date and time value type
- Variable $startDate should be defined first and should be of Date and time value type
SELECT customer_bill_entry.bill_date AS '_tr(DATE)', CONCAT( '[LINK=index.php?menuAction=download_customer_bill&menuParameter=', customer_bill_entry.flow_id, ']', customer_bill_entry.customer_bill_id, '[/LINK]' ) AS '_tr(BILL)' FROM customer_bill_entry WHERE customer_bill_entry.bill_date >= '$startDate' AND customer_bill_entry.bill_date < '$endDate' GROUP BY customer_bill_entry.customer_bill_id ORDER BY customer_bill_entry.customer_bill_id
Customer bill per month and per accounting
- Variable $accountingId should be defined first and should be of dbOject::Accounting value type.
- Variable $month should be defined first and should be of Month value type.
- Variable $year should be defined first and should be of Year value type.
SELECT account.name AS '_tr(CUSTOMER)', resource_type.name AS '_tr(RESOURCE)', product.label AS '_tr(PRODUCT)', SUM(customer_bill_entry.qty) AS '_tr(QUANTITY)', SUM(customer_bill_entry.debit) AS '_tr(AMOUNT)' FROM customer_bill_entry LEFT JOIN account_entry ON (account_entry.flow_id=customer_bill_entry.account_entry_flow_id AND account_entry.id=customer_bill_entry.account_entry_id) LEFT JOIN account ON (account.id=account_entry.account_id) LEFT JOIN accounting ON (accounting.id=account.accounting_id) LEFT JOIN product ON (product.id=customer_bill_entry.product_id) LEFT JOIN flight_account_entry ON (account_entry.flow_id=flight_account_entry.account_entry_id) LEFT JOIN flight ON (flight.id=flight_account_entry.flight_id) LEFT JOIN resource ON (resource.id=flight.aircraft_id) LEFT JOIN resource_type ON (resource_type.id=resource.resource_type_id) WHERE account.category = 2 AND accounting.id = '$accountingId' AND YEAR(account_entry.account_date) = '$year' AND MONTH(account_entry.account_date) = '$month' GROUP BY account.id, resource_type.id, product.id ORDER BY account.name
Get the details (specially the id) of an account according its 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
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.
SELECT account.id, export_account, account.name, IFNULL(sumAccountEntry(account.id, '$endDate'), 0) AS solde FROM account LEFT JOIN accounting ON (accounting.id=account.accounting_id) LEFT JOIN person ON (person.id=account.owner_id AND account.category=2) LEFT JOIN resource ON (resource.id=account.owner_id AND account.category=3) WHERE accounting.id='$accountingId' AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= '$endDate') ) ORDER BY 2, 3
Global account balance last subscription
- Variable $accountingId should be defined first and should be of dbObject::Accounting value type.
- Variable $validityTypeId should be defined first and should be of dbOjectMulti::ValidityType value type.
SELECT CONCAT(last_name, ' ', first_name) AS '_tr(FULL_NAME)', validity_type.name AS '_tr(VALIDITY)', grant_date AS '_tr(VALIDITY_GRANT_DATE)', IFNULL(( SELECT SUM(account_entry.credit) - SUM(account_entry.debit) FROM account_entry WHERE account_entry.account_id = account.id AND account_entry.account_date > ( SELECT balance_date FROM balance_date ORDER BY balance_date DESC LIMIT 1 )), 0 ) + balance.credit - balance.debit AS Total FROM person LEFT JOIN validity ON validity.person_id = person.id AND validity.is_current_validity = 1 LEFT JOIN validity_type ON validity_type.id = validity.validity_type_id LEFT JOIN account ON account.owner_id = person.id AND account.category = 2 LEFT JOIN accounting ON accounting.id=account.accounting_id LEFT JOIN balance ON balance.account_id = account.id AND balance.balance_date_id = ( SELECT id FROM balance_date ORDER BY balance_date ASC LIMIT 1 ) WHERE grant_date < DATE_SUB(NOW(), INTERVAL 2 YEAR) AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) ) AND CAST(accounting.id AS CHAR) ='$accountingId' AND account.activated = 1 AND person.activated = 1 AND validity_type.activated = 1 GROUP BY person.id, validity_type.id ORDER BY `_tr(FULL_NAME)`, validity_type.name
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.
SELECT CONCAT( '[LINK=index.php?menuAction=account_journal&menuParameter=', tmp_accounts.id, ']', tmp_accounts.id, '[/LINK]' ) AS '_tr(ID)', tmp_accounts.export_account AS '_tr(ACCOUNT_EXPORT)', tmp_accounts.name AS '_tr(ACCOUNT)', tmp_accounts.balance AS '_tr(ACCOUNT_BALANCE)' FROM ( SELECT account.id, export_account, account.name, IFNULL(sumAccountEntry(account.id,'$endDate'), 0) AS balance 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') ) AND accounting.id='$accountingId' HAVING balance NOT IN ('0', '0.00', '0,00') ORDER BY export_account ) AS tmp_accounts
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.
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 := sumAccountEntry(account.id,'$endDate')) < 0, @sumAccountEntry, 0 ) AS Debit, 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
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