Difference between revisions of "Accounting exports 4"

Jump to: navigation, search
(→‎Get the user owner of an account)
(→‎Payments list ordered by type)
(26 intermediate revisions by 2 users not shown)
Line 66: 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)
−
=Balance per account category=
+
  )
 +
ORDER BY Nom ASC</sql>
  
 +
=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 140: 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 190: 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>
 +
 
 +
=Get the details (specially the id) of an account according its name=
 +
<SQL>SELECT * FROM `account` WHERE `name` LIKE 'account name to search'</SQL>
 +
 
 +
=Get the user owner of an account=
 +
<SQL>SELECT * FROM person RIGHT JOIN account ON person.id=account.owner_id WHERE account.id=114</SQL>
  
 
=Global account balance=
 
=Global account balance=
Line 207: Line 194:
 
ORDER BY 2, 3</sql>
 
ORDER BY 2, 3</sql>
  
−
=Global account balance of users who last subscription was 2 years ago=
+
=Global account balance last subscription=
  
 
*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 '''$validityTypeId''' should be defined first and should be of '''dbOjectMulti::ValidityType''' value type.  
 
*Variable '''$validityTypeId''' should be defined first and should be of '''dbOjectMulti::ValidityType''' value type.  
  
−
<sql>SELECT last_name, first_name, validity_type.name AS 'Validity', grant_date,
+
<sql>SELECT
−
      IFNULL((SELECT SUM(account_entry.credit)-SUM(account_entry.debit) FROM account_entry WHERE account_entry.account_id = account.id
+
    CONCAT(last_name, ' ', first_name) AS '_tr(FULL_NAME)',
−
              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
+
    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
 
FROM person
−
LEFT JOIN validity ON validity.person_id = person.id
+
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 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 account ON account.owner_id = person.id AND account.category = 2
 
LEFT JOIN accounting ON accounting.id=account.accounting_id
 
LEFT JOIN accounting ON accounting.id=account.accounting_id
 
LEFT JOIN balance ON balance.account_id = account.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)
+
  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)
 
WHERE grant_date < DATE_SUB(NOW(), INTERVAL 2 YEAR)
 
   AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )
 
   AND ( validity_type.id IN ($validityTypeId) OR '-' IN ($validityTypeId) )
 
   AND CAST(accounting.id AS CHAR) ='$accountingId'
 
   AND CAST(accounting.id AS CHAR) ='$accountingId'
−
   AND account.activated=1
+
   AND account.activated = 1
−
   AND person.activated=1
+
   AND person.activated = 1
 +
  AND validity_type.activated = 1
 
GROUP BY person.id, validity_type.id
 
GROUP BY person.id, validity_type.id
−
ORDER BY last_name,first_name, validity_type.name</sql>
+
ORDER BY `_tr(FULL_NAME)`, validity_type.name</sql>
  
 
=Global non null account balance=
 
=Global non null account balance=
 
*List whole account balance at the end of a given date but for only 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 '''$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 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 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)',
Line 242: Line 248:
 
     LEFT JOIN person ON (person.id=account.owner_id AND account.category=2)
 
     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 resource ON (resource.id=account.owner_id AND account.category=3)
−
     WHERE account.activated=1
+
     WHERE (account.activated=1 OR (account.activated=0 AND account.deactivated_date >= '$endDate') )
−
        OR (account.activated=0 AND account.deactivated_date >= '$endDate')
+
    AND accounting.id='$accountingId'
 
     HAVING balance NOT IN ('0', '0.00', '0,00')
 
     HAVING balance NOT IN ('0', '0.00', '0,00')
 
     ORDER BY export_account
 
     ORDER BY export_account
Line 278: Line 284:
 
GROUP BY account.id  
 
GROUP BY account.id  
 
ORDER BY account.export_account ASC</sql>
 
ORDER BY account.export_account ASC</sql>
 +
 +
=Monthly distribution of revenue=
 +
*In [http://fr.wikipedia.org/wiki/Plan_comptable_g%C3%A9n%C3%A9ral_%28France%29#Cadre_comptable french accounting], this query reports each month's revenue distribution. It only concerns [http://doc-fr.openflyers.com/index.php?title=Account#Comptes_de_produits product accounts] (prefixed 7xx) except those that are prefixed 709xxx
 +
*Additional field $year is needed
 +
 +
<sql>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</sql>
  
 
=Movements total per account between two dates=
 
=Movements total per account between two dates=
Line 302: Line 345:
 
ORDER BY 2, 3</sql>
 
ORDER BY 2, 3</sql>
  
−
=Payments list ordered by type=
+
=Non balanced flow=
−
*Variable '''$startDate''' should be defined first and should be of '''Date''' value type.
+
<sql>SELECT flow_id, SUM(debit) AS totalDebit, SUM(credit) AS totalCredit
−
*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)',
+
−
      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
 
FROM account_entry
−
LEFT JOIN payment_type ON payment_type.id = account_entry.payment_type
+
GROUP BY flow_id
−
LEFT JOIN account ON account.id = account_entry.account_id
+
HAVING totalDebit <> totalCredit</sql>
−
LEFT JOIN person ON person.id = account.owner_id
+
 
−
WHERE
+
=Online payment attempts list=
−
    CASE
+
Required additional field:
−
        WHEN ('$paymentType'='-') THEN account_entry.payment_type IS NOT NULL
+
*$endDate Date type
−
        ELSE account_entry.payment_type='$paymentType'
+
*$startDate Date type
−
    END
+
 
−
AND credit > 0 AND account_entry.account_date >= '$startDate' AND account_entry.account_date  <= '$endDate'
+
<sql>SELECT psp_transaction.transaction_date, account.name, psp_transaction.amount, psp_transaction.state FROM psp_transaction
−
ORDER BY 1, 3, 2</sql>
+
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</sql>
  
 
=Payment dispatching=
 
=Payment dispatching=
Line 353: Line 391:
 
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>
 
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=
+
=Payment type=
−
<sql>SELECT date_format(ae.registration_date, "%d/%m/%Y") as "date", a.name, formatDecimal(ae.credit) as montant, ae.payment_description as libelle,
+
Payment types list ordered by type
−
(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>
+
  
−
=Monthly distribution of revenue=
+
*Variable '''$startDate''' should be defined first and should be of '''Date''' value type.
−
*In [http://fr.wikipedia.org/wiki/Plan_comptable_g%C3%A9n%C3%A9ral_%28France%29#Cadre_comptable french accounting], this query reports each month's revenue distribution. It only concerns [http://doc-fr.openflyers.com/index.php?title=Account#Comptes_de_produits product accounts] (prefixed 7xx) except those that are prefixed 709xxx
+
*Variable '''$endDate''' should be defined first and should be of '''Date''' value type.
−
*Additional field $year is needed
+
*Variable '''$paymentType''' should be defined first and should be of '''dbObject::PaymentType''' value type.
  
−
<sql>SELECT account_name,
+
<sql>SELECT payment_type.name AS '_tr(ENCASHMENT_TYPE)',
−
    ( IF(febSum<0, febSum, 0) - IF(janSum<0, janSum, 0) + IF(febSum<0, 0, febSum) - IF(janSum<0, 0, janSum) ) AS Janu,
+
      IF(account.category = 2, CONCAT( person.last_name, ' ', person.first_name), account.name) AS '_tr(NAME)',  
−
    ( IF(marSum<0, marSum, 0) - IF(febSum<0, febSum, 0) + IF(marSum<0, 0, marSum) - IF(febSum<0, 0, febSum) ) AS Febr,
+
      DATE(account_entry.account_date) AS '_tr(DATE)',  
−
    ( IF(aprSum<0, aprSum, 0) - IF(marSum<0, marSum, 0) + IF(aprSum<0, 0, aprSum) - IF(marSum<0, 0, marSum) ) AS Marc,
+
      account_entry.payment_description AS '_tr(DESCRIPTION)', account_entry.credit AS '_tr(AMOUNT)'
−
    ( 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