Difference between revisions of "Accounting exports 4"
(→Get the details (specially the id) of an account according its name) |
(→Payments list ordered by type) |
||
(27 intermediate revisions by 2 users not shown) | |||
Line 66: | Line 66: | ||
AND accounting.id='$accountingId'</sql> | AND accounting.id='$accountingId'</sql> | ||
− | = | + | =Carry forwards= |
− | + | ||
− | + | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
*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. | ||
− | |||
− | |||
<sql>SELECT | <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, | ||
+ | debit AS 'Debit', | ||
+ | credit AS 'Credit' | ||
FROM account | FROM account | ||
LEFT JOIN accounting ON accounting.id=account.accounting_id | LEFT JOIN accounting ON accounting.id=account.accounting_id | ||
− | LEFT JOIN | + | 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) | |
− | AND | + | OR |
− | AND | + | (person.activated=1 AND account.category=2) |
− | + | OR | |
− | + | (resource.activated=1 AND account.category=3) | |
− | = | + | ) |
+ | ORDER BY Nom ASC</sql> | ||
+ | =Category account balance= | ||
*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. | ||
<sql>SELECT | <sql>SELECT | ||
CASE | CASE | ||
− | WHEN account_type.name IS NOT NULL AND (account.category=2 | + | WHEN account.category=1 THEN '_tr(OTHER_ACCOUNT)' |
− | WHEN account.category= | + | WHEN account_type.name IS NOT NULL AND (account.category=2) THEN CONCAT('_tr(MEMBER_ACCOUNT)', ' - ', account_type.name) |
− | WHEN account.category=4 THEN | + | WHEN account_type.name IS NOT NULL AND (account.category=3) THEN CONCAT('_tr(RESOURCE_ACCOUNT)', ' - ', account_type.name) |
− | WHEN account.category=6 THEN | + | WHEN account.category=4 THEN '_tr(SUPPLIER_ACCOUNT)' |
− | WHEN account.category=7 THEN | + | WHEN account.category=6 THEN '_tr(ACCOUNT_VAT_ACCOUNT)' |
− | WHEN account.category=8 THEN | + | WHEN account.category=7 THEN '_tr(ACCOUNT_EXPENSE_ACCOUNT)' |
− | WHEN account.category=9 THEN | + | WHEN account.category=8 THEN '_tr(TREASURY_ACCOUNT)' |
− | WHEN account.category=10 THEN | + | WHEN account.category=9 THEN '_tr(PRODUCT_ACCOUNT)' |
− | WHEN account.category=11 THEN | + | WHEN account.category=10 THEN '_tr(BALANCE_SHEET_ACCOUNT)' |
+ | WHEN account.category=11 THEN '_tr(CUSTOMER_ACCOUNT)' | ||
ELSE account.name | ELSE account.name | ||
− | END AS ' | + | END AS '_tr(ACCOUNT_TYPE)', |
− | IFNULL(SUM( sumAccountEntry(account.id,NOW())),0) AS | + | IFNULL(SUM( sumAccountEntry(account.id,NOW())),0) AS '_tr(ACCOUNT_BALANCE)' |
FROM account | FROM account | ||
LEFT JOIN accounting ON accounting.id=account.accounting_id | LEFT JOIN accounting ON accounting.id=account.accounting_id | ||
Line 140: | Line 130: | ||
GROUP BY account.category</sql> | GROUP BY account.category</sql> | ||
− | = | + | =Cheque deposit slip= |
+ | <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> | ||
− | *Variable '''$ | + | =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 | + | <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> | |
− | + | ||
− | + | ||
− | + | ||
− | FROM | + | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | WHERE | + | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | ORDER BY | + | |
=Customer bill per month and per accounting= | =Customer bill per month and per accounting= | ||
− | |||
* Variable '''$accountingId''' should be defined first and should be of '''dbOject::Accounting''' value type. | * 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 '''$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. | * 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 190: | 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= | ||
+ | <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> | ||
=Global account balance= | =Global account balance= | ||
Line 207: | 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= | ||
*List whole account balance at the end of a given date but for only 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 '''$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. | ||
<sql>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)', | <sql>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)', | ||
Line 242: | Line 248: | ||
LEFT JOIN person ON (person.id=account.owner_id AND account.category=2) | 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 resource ON (resource.id=account.owner_id AND account.category=3) | ||
− | WHERE account.activated=1 | + | 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') | HAVING balance NOT IN ('0', '0.00', '0,00') | ||
ORDER BY export_account | ORDER BY export_account | ||
Line 278: | Line 284: | ||
GROUP BY account.id | GROUP BY account.id | ||
ORDER BY account.export_account ASC</sql> | ORDER BY account.export_account ASC</sql> | ||
+ | |||
+ | =Monthly distribution of revenue= | ||
+ | *In [http://fr.wikipedia.org/wiki/Plan_comptable_g%C3%A9n%C3%A9ral_%28France%29#Cadre_comptable french accounting], this query reports each month's revenue distribution. It only concerns [http://doc-fr.openflyers.com/index.php?title=Account#Comptes_de_produits product accounts] (prefixed 7xx) except those that are prefixed 709xxx | ||
+ | *Additional field $year is needed | ||
+ | |||
+ | <sql>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</sql> | ||
=Movements total per account between two dates= | =Movements total per account between two dates= | ||
Line 302: | Line 345: | ||
ORDER BY 2, 3</sql> | ORDER BY 2, 3</sql> | ||
− | = | + | =Non balanced flow= |
− | + | <sql>SELECT flow_id, SUM(debit) AS totalDebit, SUM(credit) AS totalCredit | |
− | + | ||
− | + | ||
− | + | ||
− | <sql>SELECT | + | |
− | + | ||
− | + | ||
− | + | ||
FROM account_entry | FROM account_entry | ||
− | + | GROUP BY flow_id | |
− | + | HAVING totalDebit <> totalCredit</sql> | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | = | + | =Online payment attempts list= |
− | < | + | Required additional field: |
+ | *$endDate Date type | ||
+ | *$startDate Date type | ||
+ | |||
+ | <sql>SELECT psp_transaction.transaction_date, account.name, 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> | ||
=Payment dispatching= | =Payment dispatching= | ||
Line 356: | 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 '''$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. |
− | <sql>SELECT | + | <sql>SELECT payment_type.name AS '_tr(ENCASHMENT_TYPE)', |
− | + | IF(account.category = 2, CONCAT( person.last_name, ' ', person.first_name), account.name) AS '_tr(NAME)', | |
− | + | DATE(account_entry.account_date) AS '_tr(DATE)', | |
− | + | account_entry.payment_description AS '_tr(DESCRIPTION)', account_entry.credit AS '_tr(AMOUNT)' | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + |