Difference between revisions of "Accounting exports 4"
(→Statement serving as invoice) |
(→Payments list ordered by type) |
||
(6 intermediate revisions by 2 users not shown) | |||
Line 140: | Line 140: | ||
AND ae.credit > 0 | AND ae.credit > 0 | ||
ORDER BY ae.registration_date DESC</sql> | ORDER BY ae.registration_date DESC</sql> | ||
+ | |||
+ | =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 | ||
+ | |||
+ | <sql>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</sql> | ||
=Customer bill per month and per accounting= | =Customer bill per month and per accounting= | ||
Line 146: | Line 156: | ||
* Variable '''$year''' should be defined first and should be of '''Year''' value type. | * Variable '''$year''' should be defined first and should be of '''Year''' value type. | ||
− | <sql>SELECT account.name AS ' | + | <sql>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 | 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_entry ON (account_entry.flow_id=customer_bill_entry.account_entry_flow_id AND account_entry.id=customer_bill_entry.account_entry_id) | ||
Line 160: | Line 170: | ||
AND YEAR(account_entry.account_date) = '$year' | AND YEAR(account_entry.account_date) = '$year' | ||
AND MONTH(account_entry.account_date) = '$month' | AND MONTH(account_entry.account_date) = '$month' | ||
− | GROUP BY account.id, resource_type.id, product.id</sql> | + | GROUP BY account.id, resource_type.id, product.id |
+ | ORDER BY account.name</sql> | ||
=Get the details (specially the id) of an account according its name= | =Get the details (specially the id) of an account according its name= | ||
Line 183: | Line 194: | ||
ORDER BY 2, 3</sql> | ORDER BY 2, 3</sql> | ||
− | =Global account balance | + | =Global account balance last subscription= |
*Variable '''$accountingId''' should be defined first and should be of '''dbObject::Accounting''' value type. | *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. | *Variable '''$validityTypeId''' should be defined first and should be of '''dbOjectMulti::ValidityType''' value type. | ||
− | <sql>SELECT last_name, first_name, validity_type.name AS ' | + | <sql>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 | FROM person | ||
− | LEFT JOIN validity ON validity.person_id = person.id | + | 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 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 account ON account.owner_id = person.id AND account.category = 2 | ||
LEFT JOIN accounting ON accounting.id=account.accounting_id | LEFT JOIN accounting ON accounting.id=account.accounting_id | ||
LEFT JOIN balance ON balance.account_id = account.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) | WHERE grant_date < DATE_SUB(NOW(), INTERVAL 2 YEAR) | ||
AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) ) | AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) ) | ||
AND CAST(accounting.id AS CHAR) ='$accountingId' | AND CAST(accounting.id AS CHAR) ='$accountingId' | ||
− | AND account.activated=1 | + | AND account.activated = 1 |
− | AND person.activated=1 | + | AND person.activated = 1 |
+ | AND validity_type.activated = 1 | ||
GROUP BY person.id, validity_type.id | GROUP BY person.id, validity_type.id | ||
− | ORDER BY | + | ORDER BY `_tr(FULL_NAME)`, validity_type.name</sql> |
=Global non null account balance= | =Global non null account balance= | ||
Line 362: | Line 391: | ||
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> | 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> | ||
− | = | + | =Payment type= |
+ | Payment types list ordered by type | ||
+ | |||
*Variable '''$startDate''' should be defined first and should be of '''Date''' value 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 '''$endDate''' should be defined first and should be of '''Date''' value type. | ||
Line 377: | Line 408: | ||
WHERE | WHERE | ||
CASE | CASE | ||
− | WHEN ('$paymentType'=' | + | WHEN ('$paymentType'='') THEN account_entry.payment_type IS NOT NULL |
ELSE account_entry.payment_type='$paymentType' | ELSE account_entry.payment_type='$paymentType' | ||
END | END |
Revision as of 19:45, 15 February 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,', 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'
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