Difference between revisions of "Accounting exports 4"

Jump to: navigation, search
(→‎Global account balance of users who last subscription was 2 years ago)
(→‎Payments list ordered by type)
(32 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 
=Introduction=
 
=Introduction=
 
This page lists SQL requests for OpenFlyers release 4 export about Accounting.
 
This page lists SQL requests for OpenFlyers release 4 export about Accounting.
−
 
−
=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.
 
−
 
−
<sql>SELECT CONCAT( '[LINK=index.php?menuAction=account_journal&menuParameter=', tmp_accounts.id, ']', tmp_accounts.id, '[/LINK]' ) AS '_tr(ID)', tmp_accounts.export_account AS '_tr(ACCOUNT_EXPORT)', tmp_accounts.name AS '_tr(ACCOUNT)',
 
−
tmp_accounts.balance AS '_tr(ACCOUNT_BALANCE)' FROM (
 
−
    SELECT account.id, export_account, account.name,
 
−
    IFNULL(sumAccountEntry(account.id,'$endDate'), 0) AS balance
 
−
    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 balance NOT IN ('0', '0.00', '0,00')
 
−
    ORDER BY export_account
 
−
) AS tmp_accounts</sql>
 
  
 
=Balances of resource accounts=
 
=Balances of resource accounts=
Line 85: Line 66:
 
   AND accounting.id='$accountingId'</sql>
 
   AND accounting.id='$accountingId'</sql>
  
−
=Balances of user accounts=
+
=Carry forwards=
−
*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.
+
  
−
<sql>SELECT CONCAT( '[LINK=index.php?menuAction=account_journal&menuParameter=', account.id, ']', account.id, '[/LINK]' ) AS '_tr(ID)', person.last_name AS '_tr(LAST_NAME)', person.first_name AS '_tr(FIRST_NAME)', account_type.name AS '_tr(ACCOUNT_TYPE)',
 
−
sumAccountEntry(account.id, '$endDate') AS '_tr(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 person.last_name, person.first_name</sql>
 
−
 
−
=Balances of user accounts whose got a specific profile=
 
 
*Variable '''$accountingId''' should be defined first and should be of '''dbObject::Accounting''' value type.
 
*Variable '''$accountingId''' should be defined first and should be of '''dbObject::Accounting''' value type.
−
*Variable '''$endDate''' should be defined first and should be of value type '''Date and time''' value type.
 
−
*Variable '''$profileId''' should be defined first and should be of '''dbObjectMulti::Profile''' value type.
 
  
 
<sql>SELECT
 
<sql>SELECT
−
     personWithProfile.last_name AS NOM,
+
     IF (account.category = 2, CONCAT(person.last_name,' 
−
    personWithProfile.first_name AS PRENOM,
+
',IFNULL(person.first_name, ''),' (',account_type.name,')'),
−
    personWithProfile.person_profile AS Profil,
+
     IF (account.category = 3,CONCAT(resource.name,'
−
     IFNULL(sumAccountEntry(account.id,'$endDate'),0) AS Solde
+
(',account_type.name,')'),account.name)) AS Nom,
 +
    export_account AS Code_comptable,
 +
    debit AS 'Debit',
 +
    credit AS 'Credit'
 
FROM account
 
FROM account
 
LEFT JOIN accounting ON accounting.id=account.accounting_id  
 
LEFT JOIN accounting ON accounting.id=account.accounting_id  
−
LEFT JOIN (
+
LEFT JOIN balance ON balance.account_id=account.id
−
    SELECT person.*, GROUP_CONCAT(profile.name ORDER BY profile.name) AS person_profile
+
LEFT JOIN person ON person.id=account.owner_id AND account.category = 2
−
    FROM person
+
LEFT JOIN resource ON resource.id=account.owner_id AND account.category = 3
−
    LEFT JOIN profile ON (person.profile & profile.id)
+
LEFT JOIN account_type ON account_type.id=account.account_type
−
    WHERE profile.id IN ($profileId) OR '-' IN ($profileId)
+
WHERE account.activated = 1
−
    GROUP BY person.id
+
   AND balance.balance_date_id = (SELECT balance_date.id FROM balance_date ORDER BY balance_date.id DESC LIMIT 1)
−
) AS personWithProfile ON personWithProfile.id=account.owner_id  
+
   AND accounting.id='$accountingId'
−
WHERE account.category=2
+
  AND (
−
  AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= '$endDate') )
+
      (person.id IS NULL AND resource.id IS NULL)
−
   AND CAST(accounting.id AS CHAR) = '$accountingId'
+
      OR
−
   AND personWithProfile.id IS NOT NULL
+
      (person.activated=1 AND account.category=2)
−
ORDER BY NOM, PRENOM</sql>
+
      OR
−
 
+
      (resource.activated=1 AND account.category=3)
−
=List of all account debit, credit=
+
   )
−
*Variable '''$accountingId''' should be defined first and should be of '''dbObject::Accounting''' value type.
+
ORDER BY Nom ASC</sql>
−
*Variable '''$endDate''' should be defined first and should be of '''Date and time''' value type.
+
−
 
+
−
<sql>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</sql>
+
−
 
+
−
=Balance per account category=
+
  
 +
=Category account balance=
 
*Variable '''$accountingId''' should be defined first and should be of '''dbObject::Accounting''' value type.
 
*Variable '''$accountingId''' should be defined first and should be of '''dbObject::Accounting''' value type.
  
 
<sql>SELECT  
 
<sql>SELECT  
 
     CASE
 
     CASE
−
         WHEN account_type.name IS NOT NULL AND (account.category=2 OR account.category=3) THEN account_type.name
+
        WHEN account.category=1 THEN '_tr(OTHER_ACCOUNT)'
−
         WHEN account.category=1 THEN CONCAT('Other account ', account.name)
+
         WHEN account_type.name IS NOT NULL AND (account.category=2) THEN CONCAT('_tr(MEMBER_ACCOUNT)', ' - ', account_type.name)
−
         WHEN account.category=4 THEN CONCAT('Supplier account ', account.name)
+
         WHEN account_type.name IS NOT NULL AND (account.category=3) THEN CONCAT('_tr(RESOURCE_ACCOUNT)', ' - ', account_type.name)
−
         WHEN account.category=6 THEN CONCAT('VAT account ', account.name)
+
         WHEN account.category=4 THEN '_tr(SUPPLIER_ACCOUNT)'
−
         WHEN account.category=7 THEN CONCAT('Expense account ', account.name)
+
         WHEN account.category=6 THEN '_tr(ACCOUNT_VAT_ACCOUNT)'
−
         WHEN account.category=8 THEN CONCAT('Treasury account ', account.name)
+
         WHEN account.category=7 THEN '_tr(ACCOUNT_EXPENSE_ACCOUNT)'
−
         WHEN account.category=9 THEN CONCAT('Product account ', account.name)
+
         WHEN account.category=8 THEN '_tr(TREASURY_ACCOUNT)'
−
         WHEN account.category=10 THEN CONCAT('Balance sheet account ', account.name)
+
         WHEN account.category=9 THEN '_tr(PRODUCT_ACCOUNT)'
−
         WHEN account.category=11 THEN CONCAT('Customer account ', account.name)
+
         WHEN account.category=10 THEN '_tr(BALANCE_SHEET_ACCOUNT)'
 +
         WHEN account.category=11 THEN '_tr(CUSTOMER_ACCOUNT)'
 
         ELSE account.name
 
         ELSE account.name
−
     END AS 'Account type',
+
     END AS '_tr(ACCOUNT_TYPE)',
−
     IFNULL(SUM( sumAccountEntry(account.id,NOW())),0) AS Solde
+
     IFNULL(SUM( sumAccountEntry(account.id,NOW())),0) AS '_tr(ACCOUNT_BALANCE)'
 
FROM account
 
FROM account
 
LEFT JOIN accounting ON accounting.id=account.accounting_id  
 
LEFT JOIN accounting ON accounting.id=account.accounting_id  
Line 190: Line 130:
 
GROUP BY account.category</sql>
 
GROUP BY account.category</sql>
  
−
=Carry forwards=
+
=Cheque deposit slip=
 +
<sql>SELECT date_format(ae.registration_date, "%d/%m/%Y") as "date", a.name, formatDecimal(ae.credit) as montant, ae.payment_description as libelle,
 +
(SELECT a2.name FROM account_entry ae2 JOIN account a2 ON a2.id = ae2.account_id WHERE ae2.flow_id = ae.flow_id AND ae2.debit > 0 LIMIT 0,1) as compte_banque, ae.comments as commentaire
 +
FROM account_entry ae
 +
JOIN account a
 +
  ON ae.account_id = a.id
 +
WHERE a.account_type = 1
 +
  AND ae.validated = 1
 +
  AND ae.credit > 0
 +
ORDER BY ae.registration_date DESC</sql>
  
−
*Variable '''$accountingId''' should be defined first and should be of '''dbObject::Accounting''' value type.
+
=Customer bill between date=
 +
* Variable '''$endDate''' should be defined first and should be of '''Date and time''' value type
 +
* Variable '''$startDate''' should be defined first and should be of '''Date and time''' value type
  
−
<sql>SELECT
+
<sql>SELECT customer_bill_entry.bill_date AS '_tr(DATE)', CONCAT( '[LINK=index.php?menuAction=download_customer_bill&menuParameter=', customer_bill_entry.flow_id, ']', customer_bill_entry.customer_bill_id, '[/LINK]' ) AS '_tr(BILL)'
−
    IF (account.category = 2, CONCAT(person.last_name,'
+
FROM customer_bill_entry
−
',IFNULL(person.first_name, ''),' (',account_type.name,')'),
+
WHERE customer_bill_entry.bill_date >= '$startDate' AND customer_bill_entry.bill_date < '$endDate'
−
    IF (account.category = 3,CONCAT(resource.name,'
+
GROUP BY customer_bill_entry.customer_bill_id
−
(',account_type.name,')'),account.name)) AS Nom,
+
ORDER BY customer_bill_entry.customer_bill_id</sql>
−
    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</sql>
+
  
 
=Customer bill per month and per accounting=
 
=Customer bill per month and per accounting=
−
 
 
* Variable '''$accountingId''' should be defined first and should be of '''dbOject::Accounting''' value type.
 
* Variable '''$accountingId''' should be defined first and should be of '''dbOject::Accounting''' value type.
 
* Variable '''$month''' should be defined first and should be of '''Month''' value type.
 
* Variable '''$month''' should be defined first and should be of '''Month''' value type.
 
* Variable '''$year''' should be defined first and should be of '''Year''' value type.
 
* Variable '''$year''' should be defined first and should be of '''Year''' value type.
  
−
<sql>SELECT account.name AS 'Client', resource_type.name AS 'Ressource', product.label AS 'Produit', SUM(customer_bill_entry.qty) AS 'Qte', SUM(customer_bill_entry.debit) AS 'Prix'
+
<sql>SELECT account.name AS '_tr(CUSTOMER)', resource_type.name AS '_tr(RESOURCE)', product.label AS '_tr(PRODUCT)', SUM(customer_bill_entry.qty) AS '_tr(QUANTITY)', SUM(customer_bill_entry.debit) AS '_tr(AMOUNT)'
 
FROM customer_bill_entry
 
FROM customer_bill_entry
 
LEFT JOIN account_entry ON (account_entry.flow_id=customer_bill_entry.account_entry_flow_id AND account_entry.id=customer_bill_entry.account_entry_id)
 
LEFT JOIN account_entry ON (account_entry.flow_id=customer_bill_entry.account_entry_flow_id AND account_entry.id=customer_bill_entry.account_entry_id)
Line 240: Line 170:
 
   AND YEAR(account_entry.account_date) = '$year'
 
   AND YEAR(account_entry.account_date) = '$year'
 
   AND MONTH(account_entry.account_date) = '$month'
 
   AND MONTH(account_entry.account_date) = '$month'
−
GROUP BY account.id, resource_type.id, product.id</sql>
+
GROUP BY account.id, resource_type.id, product.id
 +
ORDER BY account.name</sql>
  
−
=Movements total per account between two dates=
+
=Get the details (specially the id) of an account according its name=
 +
<SQL>SELECT * FROM `account` WHERE `name` LIKE 'account name to search'</SQL>
  
−
* Variable '''$accountingId''' should be defined first and should be of '''dbOject::Accounting''' value type
+
=Get the user owner of an account=
−
* Variable '''$endDate''' should be defined first and should be of '''Date/Date and time''' value type
+
<SQL>SELECT * FROM person RIGHT JOIN account ON person.id=account.owner_id WHERE account.id=114</SQL>
−
* Variable '''$startDate''' should be defined first and should be of '''Date and time''' value type
+
  
−
<sql>SELECT account.id, export_account, account.name, IFNULL( SUM(account_entry.credit) - SUM(account_entry.debit), 0 ) AS 'Total'
+
=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.
 +
 
 +
<sql>SELECT account.id, export_account, account.name,
 +
IFNULL(sumAccountEntry(account.id, '$endDate'), 0) AS solde
 
FROM account
 
FROM account
−
LEFT JOIN accounting ON accounting.id=account.accounting_id
+
LEFT JOIN accounting ON (accounting.id=account.accounting_id)
−
LEFT JOIN account_entry ON (account.id=account_entry.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 accounting.id='$accountingId'
 
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 >= '$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</sql>
 
ORDER BY 2, 3</sql>
  
−
=Payments list ordered by type=
+
=Global account balance last subscription=
−
*Variable '''$startDate''' should be defined first and should be of '''Date''' value type.
+
−
*Variable '''$endDate''' should be defined first and should be of '''Date''' value type.
+
−
*Variable '''$paymentType''' should be defined first and should be of '''dbObject::PaymentType''' value type.
+
  
−
<sql>SELECT payment_type.name AS '_tr(ENCASHMENT_TYPE)',
+
*Variable '''$accountingId''' should be defined first and should be of '''dbObject::Accounting''' value type.
−
      IF(account.category = 2, CONCAT( person.last_name, ' ', person.first_name), account.name) AS '_tr(NAME)',
+
*Variable '''$validityTypeId''' should be defined first and should be of '''dbOjectMulti::ValidityType''' value type.
−
      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</sql>
+
  
−
=Get the details (specially the id) of an account according its name=
+
<sql>SELECT
−
<SQL>SELECT * FROM `account` WHERE `name` LIKE 'account name to search'</SQL>
+
    CONCAT(last_name, ' ', first_name) AS '_tr(FULL_NAME)',
 +
    validity_type.name AS '_tr(VALIDITY)',
 +
    grant_date AS '_tr(VALIDITY_GRANT_DATE)',
 +
    IFNULL((
 +
        SELECT SUM(account_entry.credit) - SUM(account_entry.debit)
 +
        FROM account_entry
 +
        WHERE account_entry.account_id = account.id
 +
          AND account_entry.account_date > (
 +
              SELECT balance_date
 +
              FROM balance_date
 +
              ORDER BY balance_date DESC
 +
              LIMIT 1
 +
          )), 0
 +
    ) + balance.credit - balance.debit AS Total
 +
FROM person
 +
LEFT JOIN validity ON validity.person_id = person.id AND validity.is_current_validity = 1
 +
LEFT JOIN validity_type ON validity_type.id = validity.validity_type_id
 +
LEFT JOIN 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
 +
  AND validity_type.activated = 1
 +
GROUP BY person.id, validity_type.id
 +
ORDER BY `_tr(FULL_NAME)`, validity_type.name</sql>
  
−
=Get the user owner of an account=
+
=Global non null account balance=
−
<SQL>SELECT * FROM person RIGHT JOIN account ON person.id=account.owner_id WHERE account.id=114</SQL>
+
*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.
  
−
=Payment dispatching=
+
<sql>SELECT CONCAT( '[LINK=index.php?menuAction=account_journal&menuParameter=', tmp_accounts.id, ']', tmp_accounts.id, '[/LINK]' ) AS '_tr(ID)', tmp_accounts.export_account AS '_tr(ACCOUNT_EXPORT)', tmp_accounts.name AS '_tr(ACCOUNT)',
−
  <SQL>SELECT payment_type AS Num, payment_type.name AS name,
+
tmp_accounts.balance AS '_tr(ACCOUNT_BALANCE)' FROM (
−
(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 account.id, export_account, account.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)= 2 AND payment_type = Num) AS Febr,
+
    IFNULL(sumAccountEntry(account.id,'$endDate'), 0) AS balance
−
(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,
+
    FROM account
−
(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,
+
    LEFT JOIN accounting ON accounting.id=account.accounting_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)= 5 AND payment_type = Num) AS May,
+