Difference between revisions of "Accounting exports 4"

Jump to: navigation, search
(Payments list ordered by type)
(Balance per account category)
(13 intermediate revisions by the same user not shown)
Line 8: Line 8:
  
 
<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
+
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
 
FROM account
 
LEFT JOIN accounting ON (accounting.id=account.accounting_id)
 
LEFT JOIN accounting ON (accounting.id=account.accounting_id)
Line 32: Line 23:
  
 
<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
+
       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 56: Line 47:
  
 
<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
+
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  
Line 136: Line 127:
  
 
<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,
formatDecimal(sumAccountEntry(account.id, '$endDate')) AS balance
+
sumAccountEntry(account.id, '$endDate') AS balance
 
FROM account
 
FROM account
 
LEFT JOIN accounting ON accounting.id=account.accounting_id  
 
LEFT JOIN accounting ON accounting.id=account.accounting_id  
Line 154: Line 145:
 
     personWithProfile.first_name AS PRENOM,
 
     personWithProfile.first_name AS PRENOM,
 
     personWithProfile.person_profile AS Profil,
 
     personWithProfile.person_profile AS Profil,
     formatDecimal(IFNULL(sumAccountEntry(account.id,'$endDate'),0)) AS Solde
+
     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  
Line 190: Line 181:
 
         ELSE account.name
 
         ELSE account.name
 
     END AS account_name,
 
     END AS account_name,
     formatDecimal( IF( (@sumAccountEntry := sumAccountEntry(account.id,'$endDate')) < 0, @sumAccountEntry, 0 ) )  AS Debit,
+
     IF( (@sumAccountEntry := sumAccountEntry(account.id,'$endDate')) < 0, @sumAccountEntry, 0 )  AS Debit,
     formatDecimal( IF( @sumAccountEntry < 0, 0, @sumAccountEntry ) ) AS Credit
+
     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 219: Line 210:
 
         ELSE account.name
 
         ELSE account.name
 
     END AS 'Account type',
 
     END AS 'Account type',
     formatDecimal(IFNULL(SUM( sumAccountEntry(account.id,NOW())),0)) AS Solde
+
     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 246: Line 237:
 
(',account_type.name,')'),account.name)) AS Nom,
 
(',account_type.name,')'),account.name)) AS Nom,
 
     export_account AS Code_comptable,
 
     export_account AS Code_comptable,
     formatDecimal(debit) AS 'Debit',
+
     debit AS 'Debit',
     formatDecimal(credit) AS 'Credit'
+
     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 256: Line 247:
 
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.id=$accountingId
+
   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 (
 
   AND (
 
       (person.id IS NULL AND resource.id IS NULL)
 
       (person.id IS NULL AND resource.id IS NULL)
Line 292: Line 263:
 
* 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 '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'
+
<sql>SELECT account.name AS 'Client', resource_type.name AS 'Ressource', product.label AS 'Produit', SUM(customer_bill_entry.qty) AS 'Qte', SUM(customer_bill_entry.debit) AS 'Prix'
 
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 314: Line 285:
 
* Variable '''$startDate''' 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 account.id, export_account, account.name, formatDecimal( IFNULL( SUM(account_entry.credit) - SUM(account_entry.debit), 0 ) ) AS 'Total'
+
<sql>SELECT account.id, export_account, account.name, IFNULL( SUM(account_entry.credit) - SUM(account_entry.debit), 0 ) AS 'Total'
 
FROM account
 
FROM account
 
LEFT JOIN accounting ON accounting.id=account.accounting_id
 
LEFT JOIN accounting ON accounting.id=account.accounting_id
Line 323: Line 294:
 
GROUP BY account.id
 
GROUP BY account.id
 
UNION
 
UNION
SELECT '', '_', 'Total', formatDecimal( IFNULL( SUM(account_entry.credit) - SUM(account_entry.debit), 0 ) ) AS 'Solde'
+
SELECT '', '_', 'Total', IFNULL( SUM(account_entry.credit) - SUM(account_entry.debit), 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 340: Line 311:
 
       IF(account.category = 2, CONCAT( person.last_name, ' ', person.first_name), account.name) AS '_tr(NAME)',  
 
       IF(account.category = 2, CONCAT( person.last_name, ' ', person.first_name), account.name) AS '_tr(NAME)',  
 
       DATE(account_entry.account_date) AS '_tr(DATE)',  
 
       DATE(account_entry.account_date) AS '_tr(DATE)',  
       account_entry.payment_description AS '_tr(DESCRIPTION)', formatDecimal(account_entry.credit) AS '_tr(AMOUNT)'
+
       account_entry.payment_description AS '_tr(DESCRIPTION)', account_entry.credit AS '_tr(AMOUNT)'
 
FROM account_entry
 
FROM account_entry
 
LEFT JOIN payment_type ON payment_type.id = account_entry.payment_type
 
LEFT JOIN payment_type ON payment_type.id = account_entry.payment_type
Line 360: Line 331:
  
 
=Payment dispatching=
 
=Payment dispatching=
   <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 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',
  formatDecimal(SUM( CAST( account_entry.credit AS DECIMAL(10,2) ) )) AS Total  
+
'month',
  FROM account_entry
+
(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),
  LEFT JOIN payment_type ON payment_type = payment_type.id
+
(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),
  WHERE YEAR( account_date ) = $year 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),
  GROUP BY payment_type
+
(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),
  UNION
+
(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 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)= 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)= 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)= 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)= 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)= 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)= 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)= 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)= 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)= 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)= 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)= 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)= 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)= 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)= 7 AND payment_type IS NOT NULL)),
+
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>
  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</SQL>
+
  
 
=Cheque deposit slip=
 
=Cheque deposit slip=
Line 414: Line 376:
  
 
<sql>SELECT account_name,
 
<sql>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,
+
     ( 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,
+
     ( 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,
+
     ( 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,
+
     ( 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,
+
     ( 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,
+
     ( 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,
+
     ( 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,
+
     ( 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,
+
     ( 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,
+
     ( 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,
+
     ( 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
+
     ( 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 443: Line 405:
 
     FROM account
 
     FROM account
 
     LEFT JOIN account_type ON (account_type.id = account.account_type)
 
     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
 
     WHERE account.export_account LIKE '7%' AND account.export_account NOT LIKE '709%' AND account.activated=1
 
) AS sumAccountEntryForAccount</sql>
 
) AS sumAccountEntryForAccount</sql>
Line 664: Line 594:
  
 
<sql>SELECT '' AS 'Date', '' AS 'Numero flux', '' AS 'Comptes affectés', CONCAT( 'Solde au ', '$startDate' ) AS 'Commentaires',
 
<sql>SELECT '' AS 'Date', '' AS 'Numero flux', '' AS 'Comptes affectés', CONCAT( 'Solde au ', '$startDate' ) AS 'Commentaires',
       formatDecimal( IF( (@startBalance := sumValidatedAccountEntry('$accountId', '$startDate')) < 0, @startBalance, '' ) ) AS 'Débit',
+
       IF( (@startBalance := sumValidatedAccountEntry('$accountId', '$startDate')) < 0, @startBalance, '' ) AS 'Débit',
       formatDecimal( IF( @startBalance >= 0, @startBalance, '' ) ) AS 'Crédit'
+
       IF( @startBalance >= 0, @startBalance, '' ) AS 'Crédit'
 
UNION
 
UNION
 
SELECT account_entry.account_date, account_entry.flow_id,  
 
SELECT account_entry.account_date, account_entry.flow_id,  
Line 678: Line 608:
 
             )
 
             )
 
       ) AS comments,
 
       ) AS comments,
       formatDecimal( account_entry.debit ),
+
       account_entry.debit,
       formatDecimal( account_entry.credit )
+
       account_entry.credit
 
FROM account_entry
 
FROM account_entry
 
LEFT JOIN account ON (account.id=account_entry.account_id)
 
LEFT JOIN account ON (account.id=account_entry.account_id)
Line 711: Line 641:
 
UNION
 
UNION
 
SELECT '_', '', '', CONCAT( 'Solde au ', '$endDate' ),
 
SELECT '_', '', '', CONCAT( 'Solde au ', '$endDate' ),
       formatDecimal( IF( (@endBalance := sumValidatedAccountEntry('$accountId', '$endDate')) < 0, @endBalance, '' ) ),
+
       IF( (@endBalance := sumValidatedAccountEntry('$accountId', '$endDate')) < 0, @endBalance, '' ),
       formatDecimal( IF( @endBalance >= 0, @endBalance, '' ) )
+
       IF( @endBalance >= 0, @endBalance, '' )
 
ORDER BY 1, 2</sql>
 
ORDER BY 1, 2</sql>

Revision as of 09:39, 12 April 2018

Introduction

This page lists SQL requests for OpenFlyers release 4 export about Accounting.

Global account balance

List whole account balance at the end of a given date :

  • Variable $endDate should be defined first and should be of Date and time value type.
  • Variable $accountingId should be defined first and should be of dbObject::Accounting value type.
SELECT account.id, export_account, account.name,
IFNULL(sumAccountEntry(account.id, '$endDate'), 0) AS solde
FROM account
LEFT JOIN accounting ON (accounting.id=account.accounting_id)
LEFT JOIN person ON (person.id=account.owner_id AND account.category=2)
LEFT JOIN resource ON (resource.id=account.owner_id AND account.category=3)
WHERE accounting.id='$accountingId'
  AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= '$endDate') )
ORDER BY 2, 3

Global account balance of users who last subscription was 2 years ago

  • Variable $accountingId should be defined first and should be of dbObject::Accounting value type.
  • Variable $validityTypeId should be defined first and should be of dbOjectMulti::ValidityType value type.
SELECT last_name, first_name, validity_type.name AS 'Validity', grant_date,
      IFNULL((SELECT  SUM(account_entry.credit)-SUM(account_entry.debit) FROM account_entry  WHERE account_entry.account_id = account.id
              AND account_entry.account_date > (SELECT balance_date  FROM balance_date ORDER BY balance_date DESC LIMIT  1)),0)+balance.credit-balance.debit AS Total
FROM person
LEFT JOIN validity ON validity.person_id = person.id
LEFT JOIN validity_type ON validity_type.id = validity.validity_type_id
LEFT JOIN account ON account.owner_id = person.id AND account.category = 2
LEFT JOIN accounting ON accounting.id=account.accounting_id
LEFT JOIN balance ON balance.account_id = account.id
          AND balance.balance_date_id = (SELECT id FROM balance_date  ORDER BY balance_date ASC LIMIT 1)
WHERE grant_date < DATE_SUB(NOW(), INTERVAL 2 YEAR)
  AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )
  AND CAST(accounting.id AS CHAR) ='$accountingId'
  AND account.activated=1
  AND person.activated=1
GROUP BY person.id, validity_type.id
ORDER BY last_name,first_name, validity_type.name

Global non null account balance

  • List whole account balance at the end of a given date but for only non null account balance
  • Variable $endDate should be defined first and should be of Date and time value type.
  • Variable $accountingId should be defined first and should be of dbObject::Accounting value type.
SELECT account.id, export_account, account.name,
IFNULL(sumAccountEntry(account.id,'$endDate'), 0) AS solde
FROM account
LEFT JOIN accounting ON accounting.id=account.accounting_id 
LEFT JOIN person ON (person.id=account.owner_id AND account.category=2)
LEFT JOIN resource ON (resource.id=account.owner_id AND account.category=3)
WHERE account.activated=1
   OR (account.activated=0 AND account.deactivated_date >= '$endDate')
HAVING solde NOT IN ('0', '0.00', '0,00')
ORDER BY export_account

Balances of accounts

  • Variable $accountingId should be defined first and should be of dbObject::Accounting value type.
  • Variable $endDate should be defined first and should be of value type Date and time value type.

Balances of resource accounts

[OF_DYNAMIC_SQL]
SELECT IFNULL(CONCAT(
    'SELECT resource.name,',
    GROUP_CONCAT(
        CONCAT(
            ' (SELECT sumAccountEntry(account.id, \'$endDate\') AS balance
               FROM account
               LEFT JOIN account_type ON (account.account_type=account_type.id)
               WHERE account_type.activated=1
                 AND account.category=3
                 AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= \'$endDate\') )
                 AND account.owner_id=resource.id
                 AND account.account_type=',
            account_type.id,
            ') AS \'',
            REPLACE(account_type.name, '\'', '\\\''),
            '\''
        )
    ),
    ', (SELECT SUM(sumAccountEntry(account.id, \'$endDate\')) AS balance
       FROM account
       LEFT JOIN account_type ON (account.account_type=account_type.id)
       WHERE account_type.activated=1
         AND account.category=3
         AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= \'$endDate\') )
         AND account.owner_id=resource.id
       GROUP BY account.owner_id
    ) AS \'Total\'
    FROM resource WHERE resource.physical=1 AND resource.activated=1 GROUP BY resource.id
    UNION
    SELECT \'Total global\',',
    GROUP_CONCAT(
        CONCAT(
            ' (SELECT SUM(sumAccountEntry(account.id, \'$endDate\')) AS balance
               FROM account
               LEFT JOIN account_type ON (account.account_type=account_type.id)
               WHERE account_type.activated=1
                 AND account.category=3
                 AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= \'$endDate\') )
                 AND account.account_type=',
            account_type.id,
            ') AS \'',
            REPLACE(account_type.name, '\'', '\\\''),
            '\''
        )
    ),
    ', (SELECT SUM(sumAccountEntry(account.id, \'$endDate\')) AS balance
       FROM account
       LEFT JOIN account_type ON (account.account_type=account_type.id)
       WHERE account_type.activated=1
         AND account.category=3
         AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= \'$endDate\') )
    ) AS \'Total\''
), 'SELECT \'\'')
FROM account_type
LEFT JOIN accounting ON accounting.id=account_type.accounting_id
WHERE account_type.category=3
  AND account_type.activated=1
  AND accounting.id='$accountingId'

Balances of user accounts

SELECT person.last_name, person.first_name, account_type.name AS account_type_name,
sumAccountEntry(account.id, '$endDate') AS balance
FROM account
LEFT JOIN accounting ON accounting.id=account.accounting_id 
LEFT JOIN account_type ON (account.account_type=account_type.id)
LEFT JOIN person ON (account.owner_id=person.id)
WHERE account.category=2
  AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= '$endDate') )
  AND accounting.id='$accountingId'
ORDER BY last_name, first_name

Balances of user accounts whose got a specific profile

  • Variable $profileId should be defined first and should be of dbObjectMulti::Profile value type.
SELECT
    personWithProfile.last_name AS NOM,
    personWithProfile.first_name AS PRENOM,
    personWithProfile.person_profile AS Profil,
    IFNULL(sumAccountEntry(account.id,'$endDate'),0) AS Solde
FROM account
LEFT JOIN accounting ON accounting.id=account.accounting_id 
LEFT JOIN (
    SELECT person.*, GROUP_CONCAT(profile.name ORDER BY profile.name) AS person_profile
    FROM person
    LEFT JOIN profile ON (person.profile & profile.id)
    WHERE profile.id IN ($profileId) OR '-' IN ($profileId)
    GROUP BY person.id
) AS personWithProfile ON personWithProfile.id=account.owner_id 
WHERE account.category=2
  AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= '$endDate') )
  AND CAST(accounting.id AS CHAR) = '$accountingId'
  AND personWithProfile.id IS NOT NULL
ORDER BY NOM, PRENOM

List of all account debit, credit

  • Variable $accountingId should be defined first and should be of dbObject::Accounting value type.
  • Variable $endDate should be defined first and should be of Date and time value type.
SELECT
    account.export_account AS 'Export account',
    CASE
        WHEN account_type.name IS NOT NULL AND account.category=2 THEN CONCAT('User account ', account_type.name, ' of ', person.last_name, ' ', IFNULL(person.first_name, ''))
        WHEN account_type.name IS NOT NULL AND account.category=3 THEN CONCAT('Aircraft account ', account_type.name, ' of ', resource.name)
        WHEN account.category=1 THEN CONCAT('Other account ', account.name)
        WHEN account.category=4 THEN CONCAT('Supplier account ', account.name)
        WHEN account.category=6 THEN CONCAT('VAT account ', account.name)
        WHEN account.category=7 THEN CONCAT('Expense account ', account.name)
        WHEN account.category=8 THEN CONCAT('Treasury account ', account.name)
        WHEN account.category=9 THEN CONCAT('Product account ', account.name)
        WHEN account.category=10 THEN CONCAT('Balance sheet account ', account.name)
        WHEN account.category=11 THEN CONCAT('Customer account ', account.name)
        ELSE account.name
    END AS account_name,
    IF( (@sumAccountEntry := sumAccountEntry(account.id,'$endDate')) < 0, @sumAccountEntry, 0 )  AS Debit,
    IF( @sumAccountEntry < 0, 0, @sumAccountEntry ) AS Credit
FROM account
LEFT JOIN accounting ON accounting.id=account.accounting_id 
LEFT JOIN account_type ON (account.account_type = account_type.id AND account.category IN(2,3))
LEFT JOIN person ON (account.owner_id = person.id AND account.category=2)
LEFT JOIN resource ON (account.owner_id = resource.id AND account.category=3)
WHERE accounting.id='$accountingId'
  AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= '$endDate') )
GROUP BY account.id 
ORDER BY account.export_account ASC

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',
    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,
    debit AS 'Debit',
    credit AS 'Credit'
FROM account
LEFT JOIN accounting ON accounting.id=account.accounting_id 
LEFT JOIN balance ON balance.account_id=account.id
LEFT JOIN person ON person.id=account.owner_id AND account.category = 2
LEFT JOIN resource ON resource.id=account.owner_id AND account.category = 3
LEFT JOIN account_type ON account_type.id=account.account_type
WHERE account.activated = 1
  AND balance.balance_date_id = (SELECT balance_date.id FROM balance_date ORDER BY balance_date.id DESC LIMIT 1)
  AND accounting.id='$accountingId'
  AND (
      (person.id IS NULL AND resource.id IS NULL)
      OR
      (person.activated=1 AND account.category=2)
      OR
      (resource.activated=1 AND account.category=3)
  )
ORDER BY Nom ASC

Customer bill per month and per accounting

  • Variable $accountingId should be defined first and should be of dbOject::Accounting value type.
  • Variable $month should be defined first and should be of Month value type.
  • Variable $year should be defined first and should be of Year value type.
SELECT account.name AS 'Client', resource_type.name AS 'Ressource', product.label AS 'Produit', SUM(customer_bill_entry.qty) AS 'Qte', 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, 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', 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 '_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)'
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 1, 3, 2

Get the details (specially the id) of an account according its name

SELECT * FROM `account` WHERE `name` LIKE 'account name to search'

Get the user owner of an account

SELECT * FROM person RIGHT JOIN account ON person.id=account.owner_id WHERE account.id=114

Payment dispatching

SELECT 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,
(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,
(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,
(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,
(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,
(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,
(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  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',
(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),
(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),
(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),
(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),
(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),
(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),
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,
    ( 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

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, 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

Statement serving as invoice

  • Variable $startDate should be of Date and time value type.
  • Variable $endDate should be of Date and time value type.
  • Variable $profileId should be of Integer value type.
  • Variable $occupiedSeat should be of dbOjectMulti::Profile value type.
SELECT CONCAT(person.last_name,' ',person.first_name) AS '_tr(FULL_NAME)',
CONCAT(FLOOR(SUM( flight_list.duree )/600),':',TIME_FORMAT(SEC_TO_TIME((SUM( flight_list.duree )/600 - FLOOR(SUM( flight_list.duree )/600))*3600),'%i')) AS '_tr(COMPLETED_HOURS_NUMBER)',
SUM(flight_list.montant) AS '_tr(TOTAL_AMOUNT_ACTIVITIES)' 
FROM person
JOIN
(SELECT person.id AS person_id, flight.id AS vol, flight.duration AS duree, SUM(account_entry.debit)-SUM(account_entry.credit) AS montant FROM person
LEFT JOIN profile ON person.profile&profile.id
LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id
LEFT JOIN flight ON flight.id=flight_pilot.flight_id
LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id
LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id
LEFT JOIN account ON account.id=account_entry.account_id
WHERE '$startDate' <= flight.start_date AND flight.start_date <= '$endDate'
    AND (IF((('$occupiedSeat'=0)OR('$occupiedSeat'='')), 0, -1) = flight_pilot.num
    OR IF((('$occupiedSeat'=1)OR('$occupiedSeat'='')), 1, -1) = flight_pilot.num)
        AND ( profile.id IN ($profileId) OR '-' IN ($profileId) ) AND (account.category = 11 OR account.category = 2)
GROUP BY flight.id) AS flight_list ON flight_list.person_id=person.id
GROUP BY person.id
UNION
SELECT '_tr(TOTAL)',
    CONCAT( 
        (
             SELECT FLOOR(SUM(flight_list.duree)/600)
             FROM person
             JOIN
             (SELECT person.id AS person_id, flight.id AS vol, flight.duration AS duree, SUM(account_entry.debit)-SUM(account_entry.credit) AS montant FROM person
			 LEFT JOIN profile ON person.profile&profile.id
             LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id
             LEFT JOIN flight ON flight.id=flight_pilot.flight_id
             LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id
             LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id
             LEFT JOIN account ON account.id=account_entry.account_id
             WHERE '$startDate' <= flight.start_date AND flight.start_date <= '$endDate'
             AND (IF((('$occupiedSeat'=0)OR('$occupiedSeat'='')), 0, -1) = flight_pilot.num
             OR IF((('$occupiedSeat'=1)OR('$occupiedSeat'='')), 1, -1) = flight_pilot.num)
                AND ( profile.id IN ($profileId) OR '-' IN ($profileId) ) AND (account.category = 11 OR account.category = 2)
             GROUP BY flight.id) AS flight_list ON flight_list.person_id=person.id
        ),':',
        TIME_FORMAT(SEC_TO_TIME((
            (
                SELECT SUM(flight_list.duree)/600
                FROM person
                JOIN
                (SELECT person.id AS person_id, flight.id AS vol, flight.duration AS duree, SUM(account_entry.debit)-SUM(account_entry.credit) AS montant FROM person
				LEFT JOIN profile ON person.profile&profile.id
                LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id
                LEFT JOIN flight ON flight.id=flight_pilot.flight_id
                LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id
                LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id
                LEFT JOIN account ON account.id=account_entry.account_id
                WHERE '$startDate' <= flight.start_date AND flight.start_date <= '$endDate'
                AND (IF((('$occupiedSeat'=0)OR('$occupiedSeat'='')), 0, -1) = flight_pilot.num
                OR IF((('$occupiedSeat'=1)OR('$occupiedSeat'='')), 1, -1) = flight_pilot.num)
                    AND ( profile.id IN ($profileId) OR '-' IN ($profileId) ) AND (account.category = 11 OR account.category = 2)
                GROUP BY flight.id) AS flight_list ON flight_list.person_id=person.id
            )
            - 
            (
                SELECT FLOOR(SUM(flight_list.duree)/600)
                FROM person
                JOIN
                (SELECT person.id AS person_id, flight.id AS vol, flight.duration AS duree, SUM(account_entry.debit)-SUM(account_entry.credit) AS montant FROM person
				LEFT JOIN profile ON person.profile&profile.id
                LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id
                LEFT JOIN flight ON flight.id=flight_pilot.flight_id
                LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id
                LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id
                LEFT JOIN account ON account.id=account_entry.account_id
                WHERE '$startDate' <= flight.start_date AND flight.start_date <= '$endDate'
                AND (IF((('$occupiedSeat'=0)OR('$occupiedSeat'='')), 0, -1) = flight_pilot.num
                OR IF((('$occupiedSeat'=1)OR('$occupiedSeat'='')), 1, -1) = flight_pilot.num)
                    AND ( profile.id IN ($profileId) OR '-' IN ($profileId) ) AND (account.category = 11 OR account.category = 2)
                GROUP BY flight.id) AS flight_list ON flight_list.person_id=person.id
            )
        )*3600),'%i')
    ),
    (
        SELECT SUM(flight_list.montant)
        FROM person
        JOIN
        (SELECT person.id AS person_id, flight.id AS vol, flight.duration AS duree, SUM(account_entry.debit)-SUM(account_entry.credit) AS montant FROM person
		LEFT JOIN profile ON person.profile&profile.id
        LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id
        LEFT JOIN flight ON flight.id=flight_pilot.flight_id
        LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id
        LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id
        LEFT JOIN account ON account.id=account_entry.account_id
        WHERE '$startDate' <= flight.start_date AND flight.start_date <= '$endDate'
        AND (IF((('$occupiedSeat'=0)OR('$occupiedSeat'='')), 0, -1) = flight_pilot.num
        OR IF((('$occupiedSeat'=1)OR('$occupiedSeat'='')), 1, -1) = flight_pilot.num)
            AND ( profile.id IN ($profileId) OR '-' IN ($profileId) ) AND (account.category = 11 OR account.category = 2)
        GROUP BY flight.id) AS flight_list ON flight_list.person_id=person.id
    )

Statement serving as invoice with flight details

  • Variable $startDate should be of Date and time value type.
  • Variable $endDate should be of Date and time value type.
  • Variable $profileId should be of Integer value type.
  • Variable $occupiedSeat should be of dbOjectMulti::Profile value type.
SELECT 
(
	SELECT CONCAT(last_name,' ',first_name)
	FROM person
	LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id
	WHERE flight_pilot.flight_id=flight.id AND flight_pilot.num=0
 
) AS '_tr(LEFT_PLACE)',
(
	SELECT CONCAT(last_name,' ',first_name)
	FROM person
	LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id
	WHERE flight_pilot.flight_id=flight.id AND flight_pilot.num=1
 
) AS '_tr(RIGHT_PLACE)',
flight.start_date AS '_tr(START_DATE)',
resource.name AS '_tr(RESOURCE)',
sexa2HoursMinute(flight.duration) AS '_tr(DURATION)',
(
	SELECT GROUP_CONCAT( activity_type.name SEPARATOR ', ')
	FROM flight AS tmp_flight
	LEFT JOIN activity_type ON (tmp_flight.activity_type_id & activity_type.id)
	WHERE tmp_flight.id=flight.id
) AS '_tr(ACTIVITY_TYPE)',
flight.landing_number AS '_tr(FLIGHT_LANDING_NUMBER)',
SUM(account_entry.debit)-SUM(account_entry.credit) AS '_tr(AMOUNT)',
business_field_content.content AS '_tr(COMMENT)'
FROM person
LEFT JOIN profile ON person.profile&profile.id
LEFT JOIN flight_pilot ON flight_pilot.pilot_id=person.id
LEFT JOIN flight ON flight.id=flight_pilot.flight_id
LEFT JOIN flight_account_entry ON flight_account_entry.flight_id = flight.id
LEFT JOIN account_entry ON account_entry.flow_id = flight_account_entry.account_entry_id
LEFT JOIN account ON account.id=account_entry.account_id
LEFT JOIN resource ON resource.id = flight.aircraft_id
LEFT JOIN business_field_content ON business_field_content.category_id=flight.id
LEFT JOIN business_field ON (business_field.id=business_field_content.business_field_id AND business_field.variable='activityComment')
WHERE '$startDate' <= flight.start_date AND flight.start_date <= '$endDate'
AND (IF((('$occupiedSeat'=0)OR('$occupiedSeat'='')), 0, -1) = flight_pilot.num
OR IF((('$occupiedSeat'=1)OR('$occupiedSeat'='')), 1, -1) = flight_pilot.num)
	AND ( profile.id IN ($profileId) OR '-' IN ($profileId) ) AND (account.category = 11 OR account.category = 2)
GROUP BY flight.id
ORDER BY flight.start_date

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

Validated entries for an account between two dates

  • Variable $startDate 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 $accountId should be defined first and should be of dbObject::Account value type.
SELECT '' AS 'Date', '' AS 'Numero flux', '' AS 'Comptes affectés', CONCAT( 'Solde au ', '$startDate' ) AS 'Commentaires',
       IF( (@startBalance := sumValidatedAccountEntry('$accountId', '$startDate')) < 0, @startBalance, '' ) AS 'Débit',
       IF( @startBalance >= 0, @startBalance, '' ) AS 'Crédit'
UNION
SELECT account_entry.account_date, account_entry.flow_id, 
       tmp_affected_account.merge_affected_account, 
       IF(
            flight.id,
            tmp_activity_comment.content,
            IF(
                account_entry.payment_type,
                CONCAT( payment_type.name, IF( account_entry.payment_description IS NOT NULL, CONCAT(' (', account_entry.payment_description, ')'), '' ) ),
                account_entry.comments
            )
       ) AS comments,
       account_entry.debit,
       account_entry.credit
FROM account_entry
LEFT JOIN account ON (account.id=account_entry.account_id)
LEFT JOIN account_type ON (account_type.id=account.account_type)
LEFT JOIN flight_account_entry ON (flight_account_entry.account_entry_id=account_entry.flow_id)
LEFT JOIN flight ON (flight.id=flight_account_entry.flight_id)
LEFT JOIN payment_type ON (payment_type.id=account_entry.payment_type)
LEFT JOIN (
    SELECT account_entry.id,
           GROUP_CONCAT( IFNULL( affected_account.name, '' ), IF( affected_account_type.name IS NOT NULL, CONCAT(' (', affected_account_type.name, ')'), '' ) ) AS merge_affected_account
    FROM account_entry
    LEFT JOIN account ON (account.id=account_entry.account_id)
    LEFT JOIN account_entry AS affected_account_entry ON (affected_account_entry.flow_id=account_entry.flow_id)
    LEFT JOIN account AS affected_account ON (affected_account.id=affected_account_entry.account_id)
    LEFT JOIN account_type AS affected_account_type ON (affected_account_type.id=affected_account.account_type)
    WHERE account_entry.account_id='$accountId'
      AND account_entry.account_date>='$startDate' AND account_entry.account_date<'$endDate'
      AND account_entry.validated=1
      AND account.id <> affected_account.id
    GROUP BY account_entry.id
    ORDER BY account_entry.account_date, account_entry.flow_id, account_entry.product_id
) AS tmp_affected_account ON (tmp_affected_account.id=account_entry.id)
LEFT JOIN (
    SELECT category_id, business_field_content.content
    FROM business_field_content
    LEFT JOIN business_field ON business_field.id=business_field_content.business_field_id
    WHERE business_field.variable='activityComment'
) AS tmp_activity_comment ON (flight.id=tmp_activity_comment.category_id)
WHERE tmp_affected_account.id IS NOT NULL
GROUP BY account_entry.id
UNION
SELECT '_', '', '', CONCAT( 'Solde au ', '$endDate' ),
       IF( (@endBalance := sumValidatedAccountEntry('$accountId', '$endDate')) < 0, @endBalance, '' ),
       IF( @endBalance >= 0, @endBalance, '' )
ORDER BY 1, 2