Accounting reports 3

Revision as of 18:55, 4 January 2016 by Claratte (Talk | contribs) (→‎Global non null account balance)

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, account.name,
formatDecimal(IFNULL(sumAccountEntry(account.id,'$endDate'), 0)) AS solde
FROM account
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 (
      (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 export_account

Global account balance for specific accounting

Replace XXXX values by chosen accounting's label 

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
  AND accounting.name='XXXX'
  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 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,
      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 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 account.activated=1
  AND person.activated=1
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,
      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.name='XXXX'
  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 (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, account.name,
formatDecimal(IFNULL(sumAccountEntry(account.id,'$endDate'), 0)) AS solde
FROM account
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 (
      (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)
  )
HAVING solde <> 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, formatDecimal(sumAccountEntry(id,'$year-12-31 22:59:59')) AS solde
FROM account WHERE activated=1 HAVING solde <> 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, formatDecimal(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 accounting.name='XXXX' HAVING solde <> 0
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,
formatDecimal(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 AND person.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,
    formatDecimal(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,
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 AND person.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,
formatDecimal(sumAccountEntry(account.id, '$endDate')) AS balance
FROM account
LEFT JOIN account_type ON (account.account_type=account_type.id)
LEFT JOIN resource ON (account.owner_id=resource.id)
WHERE account.category=3 AND account.activated=1 AND resource.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('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(account.id,'$endDate')<0, sumAccountEntry(account.id,'$endDate'), 0))  AS Debit,
    formatDecimal(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 resource ON (account.owner_id = resource.id AND account.category=3)
WHERE account.activated = 1
  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.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('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(account.id,'$endDate')<0, sumAccountEntry(account.id,'$endDate'), 0))  AS Debit,
    formatDecimal(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 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.name='XXXX'
  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.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)
        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 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 (
      (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

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