Accounting exports 4
Revision as of 15:15, 7 November 2017 by Jcheng (Talk | contribs) (→Validated entries for an account between two dates)
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 Customer bill per month and per accounting
- 9 Movements total per account between two dates
- 10 Payments list ordered by type
- 11 Get the details (specially the id) of an account according is name
- 12 Get the user owner of an account
- 13 Payment dispatching
- 14 Cheque deposit slip
- 15 Monthly distribution of revenue
- 16 Non balanced flow
- 17 online payment attempts list
- 18 Supplier bill
- 19 Total of debits, credits, balances of each account at the 12/31
- 20 Validated entries for an account between two dates
Introduction
This page lists SQL requests for OpenFlyers release 4 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.
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
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 dbOjectMulti::ValidityType value type.
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 IN ($validityTypeId) OR '-' IN ($validityTypeId) ) AND CAST(accounting.id AS CHAR) ='$accountingId' AND account.activated=1 AND person.activated=1 GROUP BY person.id, validity_type.id ORDER BY last_name,first_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 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
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
[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.physical=1 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'
Balances of user accounts
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
Balances of user accounts whose got a specific profile
- Variable $profileId should be defined first and should be of dbObjectMulti::Profile value type.
SELECT personWithProfile.last_name AS NOM, personWithProfile.first_name AS PRENOM, personWithProfile.person_profile AS Profil, formatDecimal(IFNULL(sumAccountEntry(account.id,'$endDate'),0)) AS Solde FROM account LEFT JOIN accounting ON accounting.id=account.accounting_id LEFT JOIN ( SELECT person.*, GROUP_CONCAT(profile.name ORDER BY profile.name) AS person_profile FROM person LEFT JOIN profile ON (person.profile & profile.id) WHERE profile.id IN ($profileId) OR '-' IN ($profileId) GROUP BY person.id ) AS personWithProfile ON personWithProfile.id=account.owner_id WHERE account.category=2 AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= '$endDate') ) AND CAST(accounting.id AS CHAR) = '$accountingId' AND personWithProfile.id IS NOT NULL ORDER BY NOM, PRENOM
Resource account balance
[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.physical=1 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'
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, 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
Balance per account category
- Variable $accountingId should be defined first and should be of dbObject::Accounting value type.
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
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, 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
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 '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