Difference between revisions of "Accounting reports 3"
(→Global account balance of users who last subscription was 2 years ago) |
(→Balances of resource accounts) |
||
(79 intermediate revisions by 2 users not shown) | |||
Line 3: | Line 3: | ||
=Global account balance= | =Global account balance= | ||
− | List whole account balance at the end of a given date | + | 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. | ||
<sql>SELECT account.id, export_account, account.name, | <sql>SELECT account.id, export_account, account.name, | ||
− | formatDecimal(IFNULL(sumAccountEntry(account.id,'$endDate'), 0)) AS solde | + | formatDecimal(IFNULL(sumAccountEntry(account.id, '$endDate'), 0)) AS solde |
FROM account | 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 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 | + | WHERE accounting.id='$accountingId' |
− | AND ( | + | 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 | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | formatDecimal(IFNULL(sumAccountEntry(account.id,'$endDate'), 0)) AS solde | + | |
FROM account | FROM account | ||
LEFT JOIN accounting ON (accounting.id=account.accounting_id) | LEFT JOIN accounting ON (accounting.id=account.accounting_id) | ||
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 | + | WHERE accounting.id='$accountingId' |
− | + | AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= '$endDate') ) | |
− | AND ( | + | ORDER BY 2, 3</sql> |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | ORDER BY | + | |
=Global account balance of users who last subscription was 2 years ago= | =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. | + | *Variable '''$accountingId''' should be defined first and should be of '''dbObject::Accounting''' value type. |
+ | *Variable '''$validityTypeId''' should be defined first and should be of '''dbOject::ValidityType''' value type. | ||
<sql>SELECT last_name, first_name, validity_type.name AS 'Validity', grant_date, | <sql>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 | 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 | 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 | ||
Line 74: | Line 43: | ||
WHERE grant_date < DATE_SUB(NOW(), INTERVAL 2 YEAR) | WHERE grant_date < DATE_SUB(NOW(), INTERVAL 2 YEAR) | ||
AND validity_type.id = '$validityTypeId' | AND validity_type.id = '$validityTypeId' | ||
− | AND accounting. | + | AND accounting.id='$accountingId' |
AND account.activated=1 | AND account.activated=1 | ||
AND person.activated=1 | AND person.activated=1 | ||
Line 81: | Line 50: | ||
=Global non null account balance= | =Global non null account balance= | ||
− | *List whole account balance at the end of a given date | + | |
+ | *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. | ||
<sql>SELECT account.id, export_account, account.name, | <sql>SELECT account.id, export_account, account.name, | ||
− | IFNULL(sumAccountEntry(account.id,'$endDate'), 0) AS solde | + | formatDecimal(IFNULL(sumAccountEntry(account.id,'$endDate'), 0)) AS solde |
FROM account | 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 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') | |
− | + | HAVING solde NOT IN ('0', '0.00', '0,00') | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | HAVING solde | + | |
ORDER BY export_account</sql> | ORDER BY export_account</sql> | ||
− | + | =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== |
− | + | ||
− | + | <sql>[OF_DYNAMIC_SQL] | |
− | FROM account AS | + | SELECT IFNULL(CONCAT( |
− | LEFT JOIN accounting ON accounting.id= | + | 'SELECT resource.name,', |
− | WHERE activated=1 AND accounting. | + | 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.virtual=0 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'</sql> | ||
− | |||
− | |||
==Balances of user accounts== | ==Balances of user accounts== | ||
− | + | ||
<sql>SELECT person.last_name, person.first_name, account_type.name AS account_type_name, | <sql>SELECT person.last_name, person.first_name, account_type.name AS account_type_name, | ||
− | sumAccountEntry(account.id, '$endDate') AS balance | + | formatDecimal(sumAccountEntry(account.id, '$endDate')) AS balance |
FROM account | FROM account | ||
+ | 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) | ||
LEFT JOIN person ON (account.owner_id=person.id) | LEFT JOIN person ON (account.owner_id=person.id) | ||
− | WHERE account.category=2 AND account.activated=1 | + | 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</sql> | ORDER BY last_name, first_name</sql> | ||
===Balances of user accounts whose got a specific profile=== | ===Balances of user accounts whose got a specific profile=== | ||
− | + | ||
+ | *Variable '''$profileId''' should be defined first and should be of '''dbObject::Profile''' value type. | ||
+ | |||
<sql>SELECT | <sql>SELECT | ||
person.last_name AS NOM, | person.last_name AS NOM, | ||
person.first_name AS PRENOM, | person.first_name AS PRENOM, | ||
profile.name AS Profil, | profile.name AS Profil, | ||
− | IFNULL(sumAccountEntry(account.id, | + | formatDecimal(IFNULL(sumAccountEntry(account.id,'$endDate'),0)) AS Solde |
FROM account | FROM account | ||
+ | LEFT JOIN accounting ON accounting.id=account.accounting_id | ||
LEFT JOIN person ON person.id=account.owner_id | LEFT JOIN person ON person.id=account.owner_id | ||
LEFT JOIN profile ON (person.profile & profile.id) | LEFT JOIN profile ON (person.profile & profile.id) | ||
− | WHERE ( | + | WHERE account.category=2 |
+ | AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= '$endDate') ) | ||
+ | AND accounting.id=$accountingId | ||
+ | AND profile.id = $profileId | ||
ORDER BY NOM, PRENOM</sql> | ORDER BY NOM, PRENOM</sql> | ||
− | == | + | ==Resource account balance== |
− | + | <sql>[OF_DYNAMIC_SQL] | |
− | <sql>SELECT | + | SELECT IFNULL(CONCAT( |
− | sumAccountEntry(account.id, '$endDate') AS balance | + | 'SELECT resource.name,', |
− | FROM account | + | GROUP_CONCAT( |
− | LEFT JOIN | + | CONCAT( |
− | LEFT JOIN account_type ON (account.account_type=account_type.id) | + | ' (SELECT sumAccountEntry(account.id, \'$endDate\') AS balance |
− | LEFT JOIN | + | FROM account |
− | WHERE account.category= | + | 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.virtual=0 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'</sql> | ||
− | ==List of | + | ==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. | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | |||
− | |||
<sql>SELECT | <sql>SELECT | ||
account.export_account AS 'Export account', | account.export_account AS 'Export account', | ||
Line 209: | Line 246: | ||
ELSE account.name | ELSE account.name | ||
END AS account_name, | END AS account_name, | ||
− | IF (sumAccountEntry(account.id,'$endDate')<0, sumAccountEntry | + | formatDecimal( IF( (@sumAccountEntry := sumAccountEntry(account.id,'$endDate')) < 0, @sumAccountEntry, 0 ) ) AS Debit, |
− | IF (sumAccountEntry | + | formatDecimal( IF( @sumAccountEntry < 0, 0, @sumAccountEntry ) ) AS Credit |
FROM account | FROM account | ||
LEFT JOIN accounting ON accounting.id=account.accounting_id | LEFT JOIN accounting ON accounting.id=account.accounting_id | ||
Line 216: | Line 253: | ||
LEFT JOIN person ON (account.owner_id = person.id AND account.category=2) | 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 (account.owner_id = resource.id AND account.category=3) | ||
− | WHERE | + | WHERE accounting.id=$accountingId |
− | + | AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= '$endDate') ) | |
− | AND ( | + | |
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
− | + | ||
GROUP BY account.id | GROUP BY account.id | ||
ORDER BY account.export_account ASC</sql> | ORDER BY account.export_account ASC</sql> | ||
=Balance per account category= | =Balance per account category= | ||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | |||
− | + | *Variable '''$accountingId''' should be defined first and should be of '''dbObject::Accounting''' value type. | |
− | + | ||
<sql>SELECT | <sql>SELECT | ||
CASE | CASE | ||
Line 272: | Line 275: | ||
ELSE account.name | ELSE account.name | ||
END AS 'Account type', | END AS 'Account type', | ||
− | IFNULL(SUM( sumAccountEntry(account.id,NOW())),0) AS Solde | + | formatDecimal(IFNULL(SUM( sumAccountEntry(account.id,NOW())),0)) AS Solde |
FROM account | FROM account | ||
LEFT JOIN accounting ON accounting.id=account.accounting_id | LEFT JOIN accounting ON accounting.id=account.accounting_id | ||
Line 279: | Line 282: | ||
LEFT JOIN resource ON (account.owner_id = resource.id AND account.category=3) | LEFT JOIN resource ON (account.owner_id = resource.id AND account.category=3) | ||
WHERE account.activated = 1 | WHERE account.activated = 1 | ||
− | AND accounting. | + | AND accounting.id=$accountingId |
AND ( | AND ( | ||
(person.id IS NULL AND resource.id IS NULL) | (person.id IS NULL AND resource.id IS NULL) | ||
Line 291: | Line 294: | ||
=Carry forwards= | =Carry forwards= | ||
− | + | *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,' | IF (account.category = 2, CONCAT(person.last_name,' | ||
Line 340: | Line 302: | ||
(',account_type.name,')'),account.name)) AS Nom, | (',account_type.name,')'),account.name)) AS Nom, | ||
export_account AS Code_comptable, | export_account AS Code_comptable, | ||
− | debit AS 'Debit', | + | formatDecimal(debit) AS 'Debit', |
− | credit AS 'Credit' | + | formatDecimal(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 | ||
Line 350: | Line 312: | ||
WHERE account.activated = 1 | 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 balance.balance_date_id = (SELECT balance_date.id FROM balance_date ORDER BY balance_date.id DESC LIMIT 1) | ||
− | AND accounting. | + | AND accounting.id=$accountingId |
AND ( | AND ( | ||
(person.id IS NULL AND resource.id IS NULL) | (person.id IS NULL AND resource.id IS NULL) | ||
Line 362: | Line 324: | ||
'zzzzzzz', | 'zzzzzzz', | ||
'Total', | 'Total', | ||
− | SUM(debit) AS 'Debit', | + | formatDecimal(SUM(debit)) AS 'Debit', |
− | SUM(credit) AS 'Credit' | + | formatDecimal(SUM(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 | ||
Line 370: | Line 332: | ||
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 AND balance.balance_date_id = (SELECT balance_date.id FROM balance_date ORDER BY balance_date.id DESC LIMIT 1) | 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. | + | AND accounting.id=$accountingId |
AND ( | AND ( | ||
(person.id IS NULL AND resource.id IS NULL) | (person.id IS NULL AND resource.id IS NULL) | ||
Line 401: | Line 363: | ||
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</sql> | ||
+ | |||
+ | =Movements total per account between two dates= | ||
+ | |||
+ | * Variable '''$accountingId''' should be defined first and should be of '''dbOject::Accounting''' value type | ||
+ | * Variable '''$endDate''' should be defined first and should be of '''Date/Date and time''' value type | ||
+ | * Variable '''$startDate''' should be defined first and should be of '''Date and time''' value type | ||
+ | |||
+ | <sql>SELECT account.id, export_account, account.name, formatDecimal( IFNULL( SUM(account_entry.credit) - SUM(account_entry.debit), 0 ) ) AS 'Total' | ||
+ | FROM account | ||
+ | LEFT JOIN accounting ON accounting.id=account.accounting_id | ||
+ | LEFT JOIN account_entry ON (account.id=account_entry.account_id) | ||
+ | WHERE accounting.id='$accountingId' | ||
+ | AND account_entry.account_date >= '$startDate' AND account_entry.account_date < '$endDate' | ||
+ | AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= '$startDate') ) | ||
+ | GROUP BY account.id | ||
+ | UNION | ||
+ | SELECT '', '_', 'Total', formatDecimal( IFNULL( SUM(account_entry.credit) - SUM(account_entry.debit), 0 ) ) AS 'Solde' | ||
+ | FROM account | ||
+ | LEFT JOIN accounting ON accounting.id=account.accounting_id | ||
+ | LEFT JOIN account_entry ON (account.id=account_entry.account_id) | ||
+ | WHERE accounting.id='$accountingId' | ||
+ | AND account_entry.account_date >= '$startDate' AND account_entry.account_date < '$endDate' | ||
+ | AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= '$startDate') ) | ||
+ | ORDER BY 2, 3</sql> | ||
=Payments list ordered by type= | =Payments list ordered by type= | ||
− | * Variable '''$ | + | *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 payment_type.name AS 'Type', | <sql>SELECT payment_type.name AS 'Type', | ||
Line 413: | Line 401: | ||
LEFT JOIN account ON account.id = account_entry.account_id | LEFT JOIN account ON account.id = account_entry.account_id | ||
LEFT JOIN person ON person.id = account.owner_id | LEFT JOIN person ON person.id = account.owner_id | ||
− | WHERE account_entry.payment_type IS NOT NULL AND credit > 0 AND | + | WHERE |
+ | CASE | ||
+ | WHEN ("$paymentType"="-") THEN account_entry.payment_type IS NOT NULL | ||
+ | ELSE account_entry.payment_type="$paymentType" | ||
+ | END | ||
+ | AND credit > 0 AND account_entry.account_date >= "$startDate" AND account_entry.account_date <= "$endDate" | ||
ORDER BY account_entry.payment_type, account_entry.account_date, person.last_name, person.first_name</sql> | ORDER BY account_entry.payment_type, account_entry.account_date, person.last_name, person.first_name</sql> | ||
Line 425: | Line 418: | ||
<SQL>SELECT | <SQL>SELECT | ||
payment_type as Num, payment_type.name as name, | 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, | + | formatDecimal((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, | + | formatDecimal((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, | + | formatDecimal((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, | + | formatDecimal((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, | + | formatDecimal((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, | + | formatDecimal((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, | + | formatDecimal((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, | + | formatDecimal((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, | + | formatDecimal((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, | + | formatDecimal((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, | + | formatDecimal((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, | + | formatDecimal((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 | + | formatDecimal(SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) )) AS Total |
FROM account_entry | FROM account_entry | ||
LEFT JOIN payment_type ON payment_type = payment_type.id | LEFT JOIN payment_type ON payment_type = payment_type.id | ||
Line 444: | Line 437: | ||
UNION | UNION | ||
SELECT 'Sum per', 'month', | 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), | + | formatDecimal((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), | + | formatDecimal((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), | + | formatDecimal((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), | + | formatDecimal((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), | + | formatDecimal((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), | + | formatDecimal((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), | + | formatDecimal((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), | + | formatDecimal((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), | + | formatDecimal((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), | + | formatDecimal((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), | + | formatDecimal((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), | + | formatDecimal((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 | + | formatDecimal(SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) )) AS Total |
FROM account_entry | FROM account_entry | ||
LEFT JOIN payment_type ON payment_type = payment_type.id | LEFT JOIN payment_type ON payment_type = payment_type.id | ||
Line 462: | Line 455: | ||
=Cheque deposit slip= | =Cheque deposit slip= | ||
− | <sql>SELECT date_format(ae.registration_date, "%d/%m/%Y") as "date", a.name, ae.credit as montant, ae.payment_description as libelle, | + | <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 | (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 | FROM account_entry ae | ||
Line 477: | Line 470: | ||
<sql>SELECT account_name, | <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, | + | formatDecimal( ( 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, | + | formatDecimal( ( 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, | + | formatDecimal( ( 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, | + | formatDecimal( ( 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, | + | formatDecimal( ( 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, | + | formatDecimal( ( 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, | + | formatDecimal( ( 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, | + | formatDecimal( ( 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, | + | formatDecimal( ( 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, | + | formatDecimal( ( 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, | + | formatDecimal( ( 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 | + | formatDecimal( ( IF(nextJanSum<0, nextJanSum, 0) - IF(decSum<0, decSum, 0) + IF(nextJanSum<0, 0, nextJanSum) - IF(decSum<0, 0, decSum) ) ) AS Dece |
FROM ( | FROM ( | ||
SELECT IF(account.category=3, CONCAT(account.name, ' - ', account_type.name), account.name) AS 'account_name', | SELECT IF(account.category=3, CONCAT(account.name, ' - ', account_type.name), account.name) AS 'account_name', | ||
Line 510: | Line 503: | ||
UNION | UNION | ||
SELECT 'Total', | SELECT 'Total', | ||
− | ( IF(febSum<0, febSum, 0) - IF(janSum<0, janSum, 0) + IF(febSum<0, 0, febSum) - IF(janSum<0, 0, janSum) ) AS Janu, | + | formatDecimal( ( 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, | + | formatDecimal( ( 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, | + | formatDecimal( ( 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, | + | formatDecimal( ( 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, | + | formatDecimal( ( 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, | + | formatDecimal( ( 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, | + | formatDecimal( ( 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, | + | formatDecimal( ( 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, | + | formatDecimal( ( 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, | + | formatDecimal( ( 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, | + | formatDecimal( ( 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 | + | formatDecimal( ( IF(nextJanSum<0, nextJanSum, 0) - IF(decSum<0, decSum, 0) + IF(nextJanSum<0, 0, nextJanSum) - IF(decSum<0, 0, decSum) ) ) AS Dece |
FROM ( | FROM ( | ||
SELECT | SELECT | ||
Line 540: | Line 533: | ||
WHERE account.export_account LIKE '7%' AND account.export_account NOT LIKE '709%' AND account.activated=1 | WHERE account.export_account LIKE '7%' AND account.export_account NOT LIKE '709%' AND account.activated=1 | ||
) AS sumAccountEntryForAccount</sql> | ) AS sumAccountEntryForAccount</sql> | ||
+ | |||
+ | =Non balanced flow= | ||
+ | |||
+ | <sql>SELECT flow_id, SUM(debit) AS totalDebit, SUM(credit) AS totalCredit | ||
+ | FROM account_entry | ||
+ | GROUP BY flow_id | ||
+ | HAVING totalDebit <> totalCredit</sql> | ||
=online payment attempts list= | =online payment attempts list= | ||
Line 546: | Line 546: | ||
*$startDate Date type | *$startDate Date type | ||
− | <sql>SELECT psp_transaction.transaction_date, account.name, psp_transaction.amount, psp_transaction.state FROM psp_transaction | + | <sql>SELECT psp_transaction.transaction_date, account.name, formatDecimal(psp_transaction.amount), psp_transaction.state FROM psp_transaction |
LEFT JOIN account ON account.id=psp_transaction.credit_account_id | LEFT JOIN account ON account.id=psp_transaction.credit_account_id | ||
WHERE psp_transaction.transaction_date >= "$startDate" AND psp_transaction.transaction_date<="$endDate" | WHERE psp_transaction.transaction_date >= "$startDate" AND psp_transaction.transaction_date<="$endDate" | ||
ORDER BY psp_transaction.transaction_date DESC</sql> | ORDER BY psp_transaction.transaction_date DESC</sql> | ||
− | =Total of debits | + | =Supplier bill= |
+ | |||
+ | <sql>SELECT * FROM supplier_bill ORDER BY bill_date</sql> | ||
+ | |||
+ | =Total of debits, credits, balances of each account at the 12/31= | ||
Required additional field: | Required additional field: | ||
*$year Year type | *$year Year type | ||
− | <sql>SELECT id, export_account, name, | + | <sql>SELECT id, export_account, name, @debit:=sumAccountEntryDebit(id,'$year-12-31 22:59:59') AS Debit, @credit:=sumAccountEntryCredit(id,'$year-12-31 22:59:59') AS Credit, ROUND(@credit-@debit,2) AS solde FROM account WHERE activated=1 ORDER BY export_account</sql> |
Latest revision as of 09:52, 24 October 2017
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
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 :
- 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 dbOject::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 = '$validityTypeId' AND accounting.id='$accountingId' 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 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.virtual=0 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 dbObject::Profile value type.
SELECT person.last_name AS NOM, person.first_name AS PRENOM, profile.name AS Profil, 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 LEFT JOIN profile ON (person.profile & profile.id) WHERE account.category=2 AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= '$endDate') ) AND accounting.id=$accountingId AND profile.id = $profileId 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.virtual=0 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 Integer 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 (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
Movements total per account between two dates
- Variable $accountingId should be defined first and should be of dbOject::Accounting value type
- Variable $endDate should be defined first and should be of Date/Date and time value type
- Variable $startDate should be defined first and should be of Date and time value type
SELECT account.id, export_account, account.name, formatDecimal( IFNULL( SUM(account_entry.credit) - SUM(account_entry.debit), 0 ) ) AS 'Total' FROM account LEFT JOIN accounting ON accounting.id=account.accounting_id LEFT JOIN account_entry ON (account.id=account_entry.account_id) WHERE accounting.id='$accountingId' AND account_entry.account_date >= '$startDate' AND account_entry.account_date < '$endDate' AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= '$startDate') ) GROUP BY account.id UNION SELECT '', '_', 'Total', formatDecimal( IFNULL( SUM(account_entry.credit) - SUM(account_entry.debit), 0 ) ) AS 'Solde' FROM account LEFT JOIN accounting ON accounting.id=account.accounting_id LEFT JOIN account_entry ON (account.id=account_entry.account_id) WHERE accounting.id='$accountingId' AND account_entry.account_date >= '$startDate' AND account_entry.account_date < '$endDate' AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= '$startDate') ) ORDER BY 2, 3
Payments 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.
SELECT payment_type.name AS 'Type', IF(account.category = 2, CONCAT( person.last_name, ' ', person.first_name), account.name) AS Name, DATE_FORMAT(account_entry.account_date, '%d %m %Y' ) AS Date, account_entry.payment_description, formatDecimal(account_entry.credit) AS Amount FROM account_entry LEFT JOIN payment_type ON payment_type.id = account_entry.payment_type LEFT JOIN account ON account.id = account_entry.account_id LEFT JOIN person ON person.id = account.owner_id WHERE CASE WHEN ("$paymentType"="-") THEN account_entry.payment_type IS NOT NULL ELSE account_entry.payment_type="$paymentType" END AND credit > 0 AND account_entry.account_date >= "$startDate" AND account_entry.account_date <= "$endDate" ORDER BY account_entry.payment_type, account_entry.account_date, person.last_name, person.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, formatDecimal((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, formatDecimal((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, formatDecimal((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, formatDecimal((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, formatDecimal((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, formatDecimal((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, formatDecimal((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, formatDecimal((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, formatDecimal((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, formatDecimal((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, formatDecimal((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, formatDecimal((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, formatDecimal(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', formatDecimal((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)), formatDecimal((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)), formatDecimal((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)), formatDecimal((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)), formatDecimal((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)), formatDecimal((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)), formatDecimal((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)), formatDecimal((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)), formatDecimal((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)), formatDecimal((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)), formatDecimal((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)), formatDecimal((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)), formatDecimal(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, 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
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, formatDecimal( ( IF(febSum<0, febSum, 0) - IF(janSum<0, janSum, 0) + IF(febSum<0, 0, febSum) - IF(janSum<0, 0, janSum) ) ) AS Janu, formatDecimal( ( IF(marSum<0, marSum, 0) - IF(febSum<0, febSum, 0) + IF(marSum<0, 0, marSum) - IF(febSum<0, 0, febSum) ) ) AS Febr, formatDecimal( ( IF(aprSum<0, aprSum, 0) - IF(marSum<0, marSum, 0) + IF(aprSum<0, 0, aprSum) - IF(marSum<0, 0, marSum) ) ) AS Marc, formatDecimal( ( IF(maySum<0, maySum, 0) - IF(aprSum<0, aprSum, 0) + IF(maySum<0, 0, maySum) - IF(aprSum<0, 0, aprSum) ) ) AS April, formatDecimal( ( IF(junSum<0, junSum, 0) - IF(maySum<0, maySum, 0) + IF(junSum<0, 0, junSum) - IF(maySum<0, 0, maySum) ) ) AS May, formatDecimal( ( IF(julSum<0, julSum, 0) - IF(junSum<0, junSum, 0) + IF(julSum<0, 0, julSum) - IF(junSum<0, 0, junSum) ) ) AS June, formatDecimal( ( IF(augSum<0, augSum, 0) - IF(julSum<0, julSum, 0) + IF(augSum<0, 0, augSum) - IF(julSum<0, 0, julSum) ) ) AS July, formatDecimal( ( IF(sepSum<0, sepSum, 0) - IF(augSum<0, augSum, 0) + IF(sepSum<0, 0, sepSum) - IF(augSum<0, 0, augSum) ) ) AS Augu, formatDecimal( ( IF(octSum<0, octSum, 0) - IF(sepSum<0, sepSum, 0) + IF(octSum<0, 0, octSum) - IF(sepSum<0, 0, sepSum) ) ) AS Sept, formatDecimal( ( IF(novSum<0, novSum, 0) - IF(octSum<0, octSum, 0) + IF(novSum<0, 0, novSum) - IF(octSum<0, 0, octSum) ) ) AS Octo, formatDecimal( ( IF(decSum<0, decSum, 0) - IF(novSum<0, novSum, 0) + IF(decSum<0, 0, decSum) - IF(novSum<0, 0, novSum) ) ) AS Nove, formatDecimal( ( 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', formatDecimal( ( IF(febSum<0, febSum, 0) - IF(janSum<0, janSum, 0) + IF(febSum<0, 0, febSum) - IF(janSum<0, 0, janSum) ) ) AS Janu, formatDecimal( ( IF(marSum<0, marSum, 0) - IF(febSum<0, febSum, 0) + IF(marSum<0, 0, marSum) - IF(febSum<0, 0, febSum) ) ) AS Febr, formatDecimal( ( IF(aprSum<0, aprSum, 0) - IF(marSum<0, marSum, 0) + IF(aprSum<0, 0, aprSum) - IF(marSum<0, 0, marSum) ) ) AS Marc, formatDecimal( ( IF(maySum<0, maySum, 0) - IF(aprSum<0, aprSum, 0) + IF(maySum<0, 0, maySum) - IF(aprSum<0, 0, aprSum) ) ) AS April, formatDecimal( ( IF(junSum<0, junSum, 0) - IF(maySum<0, maySum, 0) + IF(junSum<0, 0, junSum) - IF(maySum<0, 0, maySum) ) ) AS May, formatDecimal( ( IF(julSum<0, julSum, 0) - IF(junSum<0, junSum, 0) + IF(julSum<0, 0, julSum) - IF(junSum<0, 0, junSum) ) ) AS June, formatDecimal( ( IF(augSum<0, augSum, 0) - IF(julSum<0, julSum, 0) + IF(augSum<0, 0, augSum) - IF(julSum<0, 0, julSum) ) ) AS July, formatDecimal( ( IF(sepSum<0, sepSum, 0) - IF(augSum<0, augSum, 0) + IF(sepSum<0, 0, sepSum) - IF(augSum<0, 0, augSum) ) ) AS Augu, formatDecimal( ( IF(octSum<0, octSum, 0) - IF(sepSum<0, sepSum, 0) + IF(octSum<0, 0, octSum) - IF(sepSum<0, 0, sepSum) ) ) AS Sept, formatDecimal( ( IF(novSum<0, novSum, 0) - IF(octSum<0, octSum, 0) + IF(novSum<0, 0, novSum) - IF(octSum<0, 0, octSum) ) ) AS Octo, formatDecimal( ( IF(decSum<0, decSum, 0) - IF(novSum<0, novSum, 0) + IF(decSum<0, 0, decSum) - IF(novSum<0, 0, novSum) ) ) AS Nove, formatDecimal( ( 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
Non balanced flow
SELECT flow_id, SUM(debit) AS totalDebit, SUM(credit) AS totalCredit FROM account_entry GROUP BY flow_id HAVING totalDebit <> totalCredit
online payment attempts list
Required additional field:
- $endDate Date type
- $startDate Date type
SELECT psp_transaction.transaction_date, account.name, formatDecimal(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
Supplier bill
SELECT * FROM supplier_bill ORDER BY bill_date
Total of debits, credits, balances of each account at the 12/31
Required additional field:
- $year Year type
SELECT id, export_account, name, @debit:=sumAccountEntryDebit(id,'$year-12-31 22:59:59') AS Debit, @credit:=sumAccountEntryCredit(id,'$year-12-31 22:59:59') AS Credit, ROUND(@credit-@debit,2) AS solde FROM account WHERE activated=1 ORDER BY export_account