Accounting reports 3

Revision as of 10:42, 10 March 2014 by Claratte (Talk | contribs)

Jump to: navigation, search

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 (Extra field $endDate, type dateTime, should be defined)

SELECT account.id, export_account, IFNULL(account.name, CONCAT(person.last_name, ' ', person.first_name)) AS nom,
IFNULL(sumAccountEntry(account.id,'$endDate'),0) AS solde
FROM account
LEFT JOIN person ON person.id=account.owner_id 
WHERE account.activated=1
ORDER BY export_account

Global account balance for specific accounting

Replace XXXX values by chosen accounting's label

SELECT account.id, export_account, IFNULL(account.name, CONCAT(person.last_name, ' ', person.first_name)) AS nom,
IFNULL(sumAccountEntry(account.id,'$endDate'),0) AS solde
FROM account
LEFT JOIN person ON person.id=account.owner_id 
LEFT JOIN accounting ON accounting.id=account.accounting_id 
WHERE account.activated=1 AND accounting.name='XXXX'
ORDER BY export_account

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.

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 = a.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 AS a ON a.owner_id = person.id AND category = 2
LEFT JOIN balance ON balance.account_id = a.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'
GROUP BY person.id
ORDER BY last_name,first_name

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

Replace XXXX values by chosen accounting's label

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 = a.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 AS a ON a.owner_id = person.id AND category = 2
LEFT JOIN accounting ON accounting.id=a.accounting_id 
LEFT JOIN balance ON balance.account_id = a.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 accounting.name='XXXX'
  AND validity_type.id = '$validityTypeId'
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 (Extra field $endDate has to be defined and should be of DateTime value type) but for only non null account balance :
SELECT account.id, export_account, IFNULL(account.name, CONCAT(person.last_name, ' ', person.first_name)) AS nom,
IFNULL(sumAccountEntry(account.id,'$endDate'),0) AS solde
FROM account
LEFT JOIN person ON person.id=account.owner_id 
WHERE account.activated=1 AND sumAccountEntry(account.id,'$endDate') <> 0
ORDER BY export_account 
  • List whole account balance at the end of a given year (Extra field $year has to be defined and should be of Year value type) but for only non null account balance :
SELECT id, export_account, name, sumAccountEntry(id,'$year-12-31 23:59:59') AS solde
FROM account WHERE activated=1 AND sumAccountEntry(id,'$year-12-31 23:59:59') <> 0
ORDER BY export_account

Global non null account balance for specific accounting

Replace XXXX values by chosen accounting's label

SELECT a.id, export_account, a.name, sumAccountEntry(a.id,'$year-12-31 23:59:59') AS solde
FROM account AS a
LEFT JOIN accounting ON accounting.id=a.accounting_id 
WHERE activated=1 AND sumAccountEntry(a.id,'$year-12-31 23:59:59') <> 0 AND accounting.name='XXXX'
ORDER BY export_account

Balances of accounts

Extra field $endDate has to be defined first and should be of value type DateTime.

Balances of user accounts

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 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
ORDER BY last_name, first_name

Balances of user accounts whose got a specific profile

Replace X and Y by right profile id.

SELECT
    person.last_name AS NOM,
    person.first_name AS PRENOM,
    profile.name AS Profil,
    IFNULL(sumAccountEntry(account.id,NOW()),0) AS Solde
FROM account
LEFT JOIN person ON person.id=account.owner_id 
LEFT JOIN profile ON (person.profile & profile.id)
WHERE (person.activated = 1) AND (account.activated = 1) AND ((profile.id = X) OR (profile.id = Y))
ORDER BY NOM, PRENOM

Balances of user accounts for specific accounting

Replace XXXX values by chosen accounting's label

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 AND accounting.name='XXXX'
ORDER BY last_name, first_name

List of resource account balance

SELECT account.name AS resource, account_type.name AS account_type_name, sumAccountEntry(account.id, '$endDate') AS balance
FROM account
LEFT JOIN account_type ON (account.account_type=account_type.id)
WHERE account.category=3 AND account.activated=1
ORDER BY account.name

List of all account debit, credit

List whole account balance at the end of a given date (Extra field $endDate, type dateTime, should be defined)

SELECT
    account.export_account AS 'Export account',
    CASE
        WHEN account_type.name IS NOT NULL AND account.category=2 THEN CONCAT('Member account ', account_type.name, ' of ', person.last_name, ' ', 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)
        ELSE account.name
    END AS account_name,
    IF (sumAccountEntry(account.id,'$endDate')<0, sumAccountEntry(account.id,'$endDate'), 0)  AS Debit,
    IF (sumAccountEntry(account.id,'$endDate')<0, 0, sumAccountEntry(account.id,'$endDate') ) AS Credit
FROM account
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 aircraft ON (account.owner_id = aircraft.id AND account.category=3)
LEFT JOIN resource ON aircraft.id = resource.id
WHERE person.activated = 1
GROUP BY account.id 
ORDER BY account.export_account ASC

List of all account debit, credit for specific accounting

Replace XXXX values by chosen accounting's label

SELECT
    account.export_account AS 'Export account',
    CASE
        WHEN account_type.name IS NOT NULL AND account.category=2 THEN CONCAT('Member account ', account_type.name, ' of ', person.last_name, ' ', 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)
        ELSE account.name
    END AS account_name,
    IF (sumAccountEntry(account.id,'$endDate')<0, sumAccountEntry(account.id,'$endDate'), 0)  AS Debit,
    IF (sumAccountEntry(account.id,'$endDate')<0, 0, sumAccountEntry(account.id,'$endDate') ) AS Credit
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
LEFT JOIN aircraft ON account.owner_id = aircraft.id
LEFT JOIN resource ON aircraft.id = resource.id
WHERE person.activated = 1 AND accounting.name='XXXX'
GROUP BY account.id
ORDER BY account.export_account ASC

Balance per account category

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)
        ELSE account.name
    END AS 'Account type',
    IFNULL(SUM( sumAccountEntry(account.id,NOW())),0) AS Solde
FROM account
LEFT JOIN account_type ON (account.account_type = account_type.id AND account.category IN(2,3))
GROUP BY account.category

Balance per account category for specific accounting

Replace XXXX values by chosen accounting's label

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)
        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))
WHERE accounting.name='XXXX'
GROUP BY account.category

Carry forwards

SELECT
    IF (account.category = 2, CONCAT(person.last_name,'  
',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 balance ON balance.account_id=account.id
LEFT JOIN person ON person.id=account.owner_id AND account.category = 2
LEFT JOIN aircraft ON aircraft.id=account.owner_id AND account.category = 3
LEFT JOIN resource ON resource.id=aircraft.id
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)
UNION
SELECT
    'zzzzzzz',
    'Total',
    SUM(debit) AS 'Debit',
    SUM(credit) AS 'Credit'
FROM account
LEFT JOIN balance ON balance.account_id=account.id
WHERE account.activated = 1 AND balance.balance_date_id = (SELECT balance_date.id FROM balance_date ORDER BY balance_date.id DESC LIMIT 1)
ORDER BY Nom ASC

Carry forwards for specific accounting

Replace XXXX values by chosen accounting's label

SELECT
    IF (account.category = 2, CONCAT(person.last_name,'  
',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 aircraft ON aircraft.id=account.owner_id AND account.category = 3
LEFT JOIN resource ON resource.id=aircraft.id
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.name='XXXX'
UNION
SELECT
    'zzzzzzz',
    'Total',
    SUM(debit) AS 'Debit',
    SUM(credit) AS 'Credit'
FROM account
LEFT JOIN accounting ON accounting.id=account.accounting_id 
LEFT JOIN balance ON balance.account_id=account.id
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.name='XXXX'
ORDER BY Nom ASC

Payments list ordered by type

SELECT payment_type.name AS Type, if(account.category = 2, 
	CONCAT( p.last_name, ' ', p.first_name),account.name) AS Name, 
	DATE_FORMAT(ae.account_date, '%d %m %Y' ) AS Date, 
	ae.payment_description, ae.credit AS Description
FROM account_entry AS ae
LEFT JOIN payment_type ON payment_type.id = ae.payment_type
LEFT JOIN account ON account.id = ae.account_id
LEFT JOIN person AS p ON p.id = account.owner_id
WHERE ae.payment_type IS NOT NULL AND credit > 0 AND YEAR(ae.account_date) >= $year
ORDER BY ae.payment_type, ae.account_date, p.last_name, p.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,
  (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, 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
UNION
SELECT 'Total',
    ( 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
    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