Difference between revisions of "Accounting exports 4"

Jump to: navigation, search
(→‎Global non null account balance)
(→‎List of all account debit, credit)
 
(18 intermediate revisions by 2 users not shown)
Line 41: Line 41:
 
   AND account_type.activated=1
 
   AND account_type.activated=1
 
   AND accounting.id=$accountingId</sql>
 
   AND accounting.id=$accountingId</sql>
 +
 +
=Breakdown by resource accounts of customer invoices=
 +
*Variable '''$startDate''' should be defined first and should be of value type '''Date and time''' value type.
 +
*Variable '''$endDate''' should be defined first and should be of value type '''Date and time''' value type.
 +
 +
<sql>SELECT customer_bill_entry.bill_date  AS _tr(DATE),
 +
CONCAT(resource_account_type.name, ' - ', resource_account.name) AS _tr(RESOURCE_ACCOUNT),
 +
    product.label AS _tr(PRODUCT),
 +
    customer_bill_entry.debit AS _tr(DEBIT),
 +
    customer_bill_entry.credit AS _tr(CREDIT),
 +
    CONCAT(person_account_type.name, ' - ', person.last_name, ' ', IFNULL(person.first_name, '')) AS  _tr(ACCOUNT_MEMBER_ACCOUNT)
 +
FROM customer_bill_entry
 +
 +
LEFT JOIN person ON person.id = customer_bill_entry.owner_id AND customer_bill_entry.owner_category = 2
 +
 +
LEFT JOIN account_entry AS person_account_entry ON person_account_entry.id = customer_bill_entry.account_entry_id
 +
LEFT JOIN account AS person_account ON person_account.id = person_account_entry.account_id
 +
LEFT JOIN account_type AS person_account_type ON person_account_type.id = person_account.account_type
 +
 +
LEFT JOIN product ON product.id = customer_bill_entry.product_id
 +
 +
INNER JOIN account_entry AS resource_account_entry ON resource_account_entry.flow_id = customer_bill_entry.account_entry_flow_id
 +
INNER JOIN account AS resource_account ON resource_account.id = resource_account_entry.account_id AND resource_account.category = 3
 +
LEFT JOIN account_type AS resource_account_type ON resource_account_type.id = resource_account.account_type
 +
 +
WHERE bill_date >= $startDate AND bill_date < $endDate
 +
ORDER BY bill_date;</sql>
  
 
=Carry forwards=
 
=Carry forwards=
Line 77: Line 104:
 
<sql>SELECT  
 
<sql>SELECT  
 
     CASE
 
     CASE
−
         WHEN account.category=1 THEN '_tr(OTHER_ACCOUNT)'
+
         WHEN account.category=1 THEN _tr(OTHER_ACCOUNT)
−
         WHEN account_type.name IS NOT NULL AND (account.category=2) THEN CONCAT('_tr(MEMBER_ACCOUNT)', ' - ', account_type.name)
+
         WHEN account_type.name IS NOT NULL AND (account.category=2) THEN CONCAT(_tr(MEMBER_ACCOUNT), ' - ', account_type.name)
−
         WHEN account_type.name IS NOT NULL AND (account.category=3) THEN CONCAT('_tr(RESOURCE_ACCOUNT)', ' - ', account_type.name)
+
         WHEN account_type.name IS NOT NULL AND (account.category=3) THEN CONCAT(_tr(RESOURCE_ACCOUNT), ' - ', account_type.name)
−
         WHEN account.category=4 THEN '_tr(SUPPLIER_ACCOUNT)'
+
         WHEN account.category=4 THEN _tr(SUPPLIER_ACCOUNT)
−
         WHEN account.category=6 THEN '_tr(ACCOUNT_VAT_ACCOUNT)'
+
         WHEN account.category=6 THEN _tr(ACCOUNT_VAT_ACCOUNT)
−
         WHEN account.category=7 THEN '_tr(ACCOUNT_EXPENSE_ACCOUNT)'
+
         WHEN account.category=7 THEN _tr(ACCOUNT_EXPENSE_ACCOUNT)
−
         WHEN account.category=8 THEN '_tr(TREASURY_ACCOUNT)'
+
         WHEN account.category=8 THEN _tr(TREASURY_ACCOUNT)
−
         WHEN account.category=9 THEN '_tr(PRODUCT_ACCOUNT)'
+
         WHEN account.category=9 THEN _tr(PRODUCT_ACCOUNT)
−
         WHEN account.category=10 THEN '_tr(BALANCE_SHEET_ACCOUNT)'
+
         WHEN account.category=10 THEN _tr(BALANCE_SHEET_ACCOUNT)
−
         WHEN account.category=11 THEN '_tr(CUSTOMER_ACCOUNT)'
+
         WHEN account.category=11 THEN _tr(CUSTOMER_ACCOUNT)
 
         ELSE account.name
 
         ELSE account.name
−
     END AS '_tr(ACCOUNT_TYPE)',
+
     END AS _tr(ACCOUNT_TYPE),
−
     IFNULL(SUM( sumAccountEntry(account.id,NOW())),0) AS '_tr(ACCOUNT_BALANCE)'
+
     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 96: Line 123:
 
LEFT JOIN resource ON (account.owner_id = resource.id AND account.category=3)
 
LEFT JOIN resource ON (account.owner_id = resource.id AND account.category=3)
 
WHERE account.activated = 1
 
WHERE account.activated = 1
−
   AND accounting.id='$accountingId'
+
   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 121: Line 148:
 
* 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 customer_bill_entry.bill_date AS '_tr(DATE)', CONCAT( '[LINK=index.php&#63;menuAction=download_customer_bill&menuParameter=', customer_bill_entry.flow_id, ']', customer_bill_entry.customer_bill_id, '[/LINK]' ) AS '_tr(BILL)'
+
<sql>SELECT customer_bill_entry.bill_date AS _tr(DATE), CONCAT( '[LINK=index.php[QUESTION_MARK]menuAction=download_customer_bill&menuParameter=', customer_bill_entry.flow_id, ']', customer_bill_entry.customer_bill_id, '[/LINK]' ) AS _tr(BILL)
 
FROM customer_bill_entry
 
FROM customer_bill_entry
−
WHERE customer_bill_entry.bill_date >= '$startDate' AND customer_bill_entry.bill_date < '$endDate'
+
WHERE customer_bill_entry.bill_date >= $startDate AND customer_bill_entry.bill_date < $endDate
 
GROUP BY customer_bill_entry.customer_bill_id
 
GROUP BY customer_bill_entry.customer_bill_id
 
ORDER BY customer_bill_entry.customer_bill_id</sql>
 
ORDER BY customer_bill_entry.customer_bill_id</sql>
Line 216: Line 243:
 
*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 CONCAT( '[LINK=index.php&#63;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>[OF_DYNAMIC_SQL]
−
tmp_accounts.balance AS _tr(ACCOUNT_BALANCE) FROM (
+
SELECT CONCAT(
−
    SELECT account.id, export_account, account.name,
+
    'SELECT
−
    IFNULL(sumAccountEntry(account.id,$endDate), 0) AS balance
+
    CONCAT( \'[LINK=index.php?menuAction=account_journal&menuParameter=\', accountsSum.account_id, \']\', accountsSum.account_id, \'[/LINK]\' ) AS _tr(ID),
−
    FROM account
+
    accountLayout.export_account AS _tr(ACCOUNT_EXPORT),  
−
    LEFT JOIN accounting ON accounting.id=account.accounting_id  
+
CASE
−
    LEFT JOIN person ON (person.id=account.owner_id AND account.category=2)
+
WHEN account_type.name IS NOT NULL AND accountLayout.category=2 THEN CONCAT(_tr(MEMBER_ACCOUNT), \' \', account_type.name,  \' \', _tr(REPORTS_OF), \' \', person.last_name, \' \', IFNULL(person.first_name, \'\'))
−
    LEFT JOIN resource ON (resource.id=account.owner_id AND account.category=3)
+
WHEN account_type.name IS NOT NULL AND accountLayout.category=3 THEN CONCAT(_tr(AIRCRAFT_ACCOUNT), \' \', account_type.name, \' \', _tr(REPORTS_OF), \' \', resource.name)
−
    WHERE (account.activated=1 OR (account.activated=0 AND account.deactivated_date >= $endDate) )
+
WHEN accountLayout.category=1 THEN CONCAT(_tr(OTHER_ACCOUNT), \' \', accountLayout.name)
−
    AND accounting.id=$accountingId
+
WHEN accountLayout.category=4 THEN CONCAT(_tr(SUPPLIER_ACCOUNT), \' \', accountLayout.name)
−
    HAVING balance NOT IN ('0', '0.00', '0,00')
+
WHEN accountLayout.category=6 THEN CONCAT(_tr(VAT_ACCOUNT), \' \', accountLayout.name)
−
     ORDER BY export_account
+
WHEN accountLayout.category=7 THEN CONCAT(_tr(EXPENSE_ACCOUNT), \' \', accountLayout.name)
−
) AS tmp_accounts</sql>
+
WHEN accountLayout.category=8 THEN CONCAT(_tr(TREASURY_ACCOUNT), \' \', accountLayout.name)
 +
WHEN accountLayout.category=9 THEN CONCAT(_tr(PRODUCT_ACCOUNT), \' \', accountLayout.name)
 +
WHEN accountLayout.category=10 THEN CONCAT(_tr(BALANCE_SHEET_ACCOUNT), \' \', accountLayout.name)
 +
WHEN accountLayout.category=11 THEN CONCAT(_tr(CUSTOMER_ACCOUNT), \' \', accountLayout.name)
 +
ELSE accountLayout.name
 +
END AS _tr(ACCOUNT),
 +
accountsSum.SOLDE AS _tr(ACCOUNT_BALANCE)
 +
FROM (
 +
SELECT accountEntriesExtract.account_id,
 +
(IFNULL(SUM(accountEntriesExtract.credit), 0.00)  - IFNULL(SUM(accountEntriesExtract.debit), 0.00) ) AS SOLDE
 +
FROM (
 +
(SELECT account_entry.account_id,
 +
IFNULL(account_entry.debit, 0.00) AS debit,
 +
IFNULL(account_entry.credit, 0.00) AS credit
 +
FROM account_entry
 +
INNER JOIN account ON (account.id = account_entry.account_id AND account.accounting_id=$accountingId AND (account.activated = 1 OR (account.activated=0 AND account.deactivated_date >= $endDate)))
 +
WHERE account_date >= \'', balance_date.balance_date ,'\' AND account_date < $endDate
 +
)
 +
UNION ALL
 +
(
 +
SELECT
 +
account_id,
 +
IFNULL(balance.debit, 0.00) AS debit,
 +
IFNULL(balance.credit, 0.00) AS credit
 +
FROM balance
 +
INNER JOIN account ON (account.id = balance.account_id AND account.accounting_id=$accountingId AND (account.activated = 1 OR (account.activated=0 AND account.deactivated_date >= $endDate)))
 +
WHERE balance_date_id = ',  balance_date.id, '
 +
)
 +
) AS accountEntriesExtract
 +
GROUP BY accountEntriesExtract.account_id
 +
        HAVING SOLDE NOT IN (\'0 \', \'0.00\', \'0,00\')
 +
) AS accountsSum
 +
INNER JOIN account accountLayout ON (accountLayout.id = accountsSum.account_id AND accountLayout.accounting_id=$accountingId AND (accountLayout.activated = 1 OR (accountLayout.activated=0 AND accountLayout.deactivated_date >= $endDate)))
 +
LEFT JOIN account_type ON (accountLayout.account_type = account_type.id AND accountLayout.category IN(2,3))
 +
LEFT JOIN person ON (person.id=accountLayout.owner_id AND accountLayout.category=2)
 +
LEFT JOIN resource ON (resource.id=accountLayout.owner_id AND accountLayout.category=3)  
 +
    GROUP BY accountLayout.id
 +
     ORDER BY accountLayout.export_account ASC
 +
    '
 +
)
 +
FROM balance_date
 +
WHERE balance_date.balance_date < $endDate
 +
ORDER BY balance_date DESC
 +
LIMIT 1
 +
</sql>
  
 
=List of all account debit, credit=
 
=List of all account debit, credit=
Line 234: Line 305:
 
*Variable '''$endDate''' 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.
  
−
<sql>SELECT
+
<sql>[OF_DYNAMIC_SQL]
−
     account.export_account AS 'Export account',
+
SELECT CONCAT(
−
    CASE
+
     'SELECT accountLayout.export_account AS _tr(ACCOUNT_EXPORT),  
−
        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, ''))
+
CASE
−
        WHEN account_type.name IS NOT NULL AND account.category=3 THEN CONCAT('Aircraft account ', account_type.name, ' of ', resource.name)
+
WHEN account_type.name IS NOT NULL AND accountLayout.category=2 THEN CONCAT(_tr(MEMBER_ACCOUNT), \' \', account_type.name, \' \', _tr(REPORTS_OF), \' \', person.last_name, \' \', IFNULL(person.first_name, \'\'))
−
        WHEN account.category=1 THEN CONCAT('Other account ', account.name)
+
WHEN account_type.name IS NOT NULL AND accountLayout.category=3 THEN CONCAT(_tr(AIRCRAFT_ACCOUNT), \' \', account_type.name, \' \', _tr(REPORTS_OF), \' \', resource.name)
−
        WHEN account.category=4 THEN CONCAT('Supplier account ', account.name)
+
WHEN accountLayout.category=1 THEN CONCAT(_tr(OTHER_ACCOUNT), \' \', accountLayout.name)
−
        WHEN account.category=6 THEN CONCAT('VAT account ', account.name)
+
WHEN accountLayout.category=4 THEN CONCAT(_tr(SUPPLIER_ACCOUNT), \' \', accountLayout.name)
−
        WHEN account.category=7 THEN CONCAT('Expense account ', account.name)
+
WHEN accountLayout.category=6 THEN CONCAT(_tr(VAT_ACCOUNT), \' \', accountLayout.name)
−
        WHEN account.category=8 THEN CONCAT('Treasury account ', account.name)
+
WHEN accountLayout.category=7 THEN CONCAT(_tr(EXPENSE_ACCOUNT), \' \', accountLayout.name)
−
        WHEN account.category=9 THEN CONCAT('Product account ', account.name)
+
WHEN accountLayout.category=8 THEN CONCAT(_tr(TREASURY_ACCOUNT), \' \', accountLayout.name)
−
        WHEN account.category=10 THEN CONCAT('Balance sheet account ', account.name)
+
WHEN accountLayout.category=9 THEN CONCAT(_tr(PRODUCT_ACCOUNT), \' \', accountLayout.name)
−
        WHEN account.category=11 THEN CONCAT('Customer account ', account.name)
+
WHEN accountLayout.category=10 THEN CONCAT(_tr(BALANCE_SHEET_ACCOUNT), \' \', accountLayout.name)
−
        ELSE account.name
+
WHEN accountLayout.category=11 THEN CONCAT(_tr(CUSTOMER_ACCOUNT), \' \', accountLayout.name)
−
    END AS account_name,
+
ELSE accountLayout.name
−
    IF( (@sumAccountEntry := sumAccountEntry(account.id,'$endDate')) < 0, @sumAccountEntry, 0 ) AS Debit,
+
END AS _tr(ACCOUNT),
−
    IF( @sumAccountEntry < 0, 0, @sumAccountEntry ) AS Credit
+
IF((@sumAccountEntry := (accountsSum.CREDIT - accountsSum.DEBIT)) < 0, @sumAccountEntry, 0) AS _tr(DEBIT),
−
FROM account
+
IF(@sumAccountEntry < 0, 0, @sumAccountEntry) AS _tr(CREDIT)
−
LEFT JOIN accounting ON accounting.id=account.accounting_id  
+
FROM (
−
LEFT JOIN account_type ON (account.account_type = account_type.id AND account.category IN(2,3))
+
SELECT accountEntriesExtract.account_id,
−
LEFT JOIN person ON (account.owner_id = person.id AND account.category=2)
+
IFNULL(SUM(accountEntriesExtract.debit), 0.00) AS DEBIT,
−
LEFT JOIN resource ON (account.owner_id = resource.id AND account.category=3)
+
IFNULL(SUM(accountEntriesExtract.credit), 0.00) AS CREDIT
−
WHERE accounting.id='$accountingId'
+
FROM (
−
  AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= '$endDate') )
+
(SELECT account_entry.account_id,
−
GROUP BY account.id  
+
IFNULL(account_entry.debit, 0.00) AS debit,
−
ORDER BY account.export_account ASC</sql>
+
IFNULL(account_entry.credit, 0.00) AS credit
 +
FROM account_entry
 +
INNER JOIN account ON (account.id = account_entry.account_id AND account.accounting_id=$accountingId AND (account.activated = 1 OR (account.activated=0 AND account.deactivated_date >= \'', balance_date.balance_date ,'\')))
 +
WHERE account_date >= \'', balance_date.balance_date ,'\' AND account_date < $endDate
 +
)
 +
UNION ALL
 +
(
 +
SELECT
 +
account_id,
 +
IFNULL(balance.debit, 0.00) AS debit,
 +
IFNULL(balance.credit, 0.00) AS credit
 +
FROM balance
 +
INNER JOIN account ON (account.id = balance.account_id AND account.accounting_id=$accountingId AND (account.activated = 1 OR (account.activated=0 AND account.deactivated_date >= \'', balance_date.balance_date ,'\')))
 +
WHERE balance_date_id = ',  balance_date.id, '
 +
)
 +
) AS accountEntriesExtract
 +
GROUP BY accountEntriesExtract.account_id
 +
) AS accountsSum
 +
INNER JOIN account accountLayout ON (accountLayout.id = accountsSum.account_id AND accountLayout.accounting_id=$accountingId AND (accountLayout.activated = 1 OR (accountLayout.activated=0 AND accountLayout.deactivated_date >= \'', balance_date.balance_date ,'\')))
 +
LEFT JOIN account_type ON (accountLayout.account_type = account_type.id AND accountLayout.category IN(2,3))
 +
LEFT JOIN person ON (person.id=accountLayout.owner_id AND accountLayout.category=2)
 +
LEFT JOIN resource ON (resource.id=accountLayout.owner_id AND accountLayout.category=3)
 +
    GROUP BY accountLayout.id  
 +
    ORDER BY accountLayout.export_account ASC
 +
    '
 +
)
 +
FROM balance_date
 +
WHERE balance_date.balance_date < $endDate
 +
ORDER BY balance_date DESC
 +
LIMIT 1
 +
</sql>
  
 
=Monthly distribution of revenue=
 
=Monthly distribution of revenue=
Line 307: Line 408:
 
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 account_entry ON (account.id=account_entry.account_id)
−
WHERE accounting.id='$accountingId'
+
WHERE accounting.id=$accountingId
−
   AND account_entry.account_date >= '$startDate' AND account_entry.account_date < '$endDate'
+
   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') )
+
   AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= $startDate) )
 
GROUP BY account.id
 
GROUP BY account.id
 
UNION
 
UNION
Line 316: Line 417:
 
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 account_entry ON (account.id=account_entry.account_id)
−
WHERE accounting.id='$accountingId'
+
WHERE accounting.id=$accountingId
−
   AND account_entry.account_date >= '$startDate' AND account_entry.account_date < '$endDate'
+
   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') )
+
   AND ( account.activated=1 OR (account.activated=0 AND account.deactivated_date >= $startDate) )
 
ORDER BY 2, 3</sql>
 
ORDER BY 2, 3</sql>
  
Line 334: Line 435:
 
<sql>SELECT psp_transaction.transaction_date, account.name, psp_transaction.amount, psp_transaction.state FROM psp_transaction
 
<sql>SELECT psp_transaction.transaction_date, account.name, psp_transaction.amount, psp_transaction.state FROM psp_transaction
 
LEFT JOIN account ON account.id=psp_transaction.credit_account_id
 
LEFT JOIN account ON account.id=psp_transaction.credit_account_id
−
WHERE psp_transaction.transaction_date >= "$startDate" AND psp_transaction.transaction_date<="$endDate"
+
WHERE psp_transaction.transaction_date >= $startDate AND psp_transaction.transaction_date<=$endDate
 
ORDER BY psp_transaction.transaction_date DESC</sql>
 
ORDER BY psp_transaction.transaction_date DESC</sql>
  
Line 374: Line 475:
 
*Variable '''$paymentType''' should be defined first and should be of '''dbObject::PaymentType''' 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)',
+
<sql>SELECT payment_type.name AS _tr(ENCASHMENT_TYPE),
−
       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)', 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 384: Line 485:
 
WHERE
 
WHERE
 
     CASE
 
     CASE
−
         WHEN ('$paymentType'='') THEN account_entry.payment_type IS NOT NULL
+
         WHEN ($paymentType='') THEN account_entry.payment_type IS NOT NULL
−
         ELSE account_entry.payment_type='$paymentType'
+
         ELSE account_entry.payment_type=$paymentType
 
     END
 
     END
−
  AND credit > 0 AND account_entry.account_date >= '$startDate' AND account_entry.account_date  <= '$endDate'
+
  AND credit > 0 AND account_entry.account_date >= $startDate AND account_entry.account_date  <= $endDate
 
ORDER BY 1, 3, 2</sql>
 
ORDER BY 1, 3, 2</sql>
  
Line 597: Line 698:
  
 
<sql>SELECT
 
<sql>SELECT
−
     CONCAT( '[LINK=index.php&#63;menuAction=account_journal&menuParameter=', account.id, ']', account.id, '[/LINK]' ) AS '_tr(ID)',
+
     CONCAT( '[LINK=index.php[QUESTION_MARK]menuAction=account_journal&menuParameter=', account.id, ']', account.id, '[/LINK]' ) AS _tr(ID),
−
     CONCAT( personWithProfile.last_name, ' ', personWithProfile.first_name) AS '_tr(LAST_NAME)',
+
     CONCAT( personWithProfile.last_name, ' ', personWithProfile.first_name) AS _tr(LAST_NAME),
−
     personWithProfile.person_profile AS '_tr(PROFILE)',
+
     personWithProfile.person_profile AS _tr(PROFILE),
−
     account_type.name AS '_tr(ACCOUNT_TYPE)',
+
     account_type.name AS _tr(ACCOUNT_TYPE),
−
     IFNULL(sumAccountEntry(account.id,'$endDate'),0) AS '_tr(BALANCE)'
+
     IFNULL(sumAccountEntry(account.id,$endDate),0) AS _tr(BALANCE)
 
FROM account
 
FROM account
 
LEFT JOIN accounting ON accounting.id=account.accounting_id  
 
LEFT JOIN accounting ON accounting.id=account.accounting_id  
Line 613: Line 714:
 
) AS personWithProfile ON personWithProfile.id=account.owner_id  
 
) AS personWithProfile ON personWithProfile.id=account.owner_id  
 
WHERE account.category=2
 
WHERE account.category=2
−
   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 >= $endDate) )
−
   AND CAST(accounting.id AS CHAR) = '$accountingId'
+
   AND CAST(accounting.id AS CHAR) = $accountingId
 
   AND personWithProfile.id IS NOT NULL
 
   AND personWithProfile.id IS NOT NULL
 
ORDER BY personWithProfile.last_name, personWithProfile.first_name</sql>
 
ORDER BY personWithProfile.last_name, personWithProfile.first_name</sql>
  
−
=Validated entries for an account between two dates=
+
=Validated only or all entries for an account between two dates=
  
 
*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.
Line 624: Line 725:
 
*Variable '''$accountId''' should be defined first and should be of '''dbObject::Account''' value type.
 
*Variable '''$accountId''' should be defined first and should be of '''dbObject::Account''' value type.
  
−
<sql>SELECT '' AS 'Date', '' AS 'Numero flux', '' AS 'Comptes affectés', CONCAT( 'Solde au ', $startDate ) AS 'Commentaires',
+
<sql>SELECT '' AS _tr(DATE), '' AS _tr(FLOW_NUMBER), '' AS _tr(AFFECTED_ACCOUNTS), CONCAT( _tr(TEXT_BALANCE), $startDate ) AS _tr(COMMENTS),
−
       IF( (@startBalance := sumValidatedAccountEntry($accountId, $startDate)) < 0, @startBalance, '' ) AS 'Débit',
+
       IF( (@startBalance := sumValidatedAccountEntry($accountId, $startDate)) < 0, @startBalance, '' ) AS _tr(DEBIT),
−
       IF( @startBalance >= 0, @startBalance, '' ) AS 'Crédit'
+
       IF( @startBalance >= 0, @startBalance, '' ) AS _tr(CREDIT)
 
UNION
 
UNION
 
SELECT account_entry.account_date, account_entry.flow_id,  
 
SELECT account_entry.account_date, account_entry.flow_id,  
Line 657: Line 758:
 
     WHERE account_entry.account_id=$accountId
 
     WHERE account_entry.account_id=$accountId
 
       AND account_entry.account_date>=$startDate AND account_entry.account_date<$endDate
 
       AND account_entry.account_date>=$startDate AND account_entry.account_date<$endDate
−
       AND account_entry.validated=1
+
       AND (account_entry.validated=1 OR $validatedOnly=0)
 
       AND account.id <> affected_account.id
 
       AND account.id <> affected_account.id
 
     GROUP BY account_entry.id
 
     GROUP BY account_entry.id
Line 671: Line 772:
 
GROUP BY account_entry.id
 
GROUP BY account_entry.id
 
UNION
 
UNION
−
SELECT '_', '', '', CONCAT( 'Solde au ', $endDate ),
+
SELECT '_', '', '', CONCAT( _tr(TEXT_BALANCE), $endDate ),
 
       IF( (@endBalance := sumValidatedAccountEntry($accountId, $endDate)) < 0, @endBalance, '' ),
 
       IF( (@endBalance := sumValidatedAccountEntry($accountId, $endDate)) < 0, @endBalance, '' ),
 
       IF( @endBalance >= 0, @endBalance, '' )
 
       IF( @endBalance >= 0, @endBalance, '' )
 
ORDER BY 1, 2</sql>
 
ORDER BY 1, 2</sql>

Latest revision as of 18:40, 27 February 2023