CiviCRM Community Forums (archive)

I have a client that is being audited and needs to know what their Accounts Receivable was at close of their year end books. Is all transaction data (when created, payment made, completed) kept in the activity table (where I am looking now) as the contribution table itself doesn't seem complete in this regard.  I'll post what I find, but am looking for pointers along the way.

Thanks for the reply, but would those catch even manually entered transactions? (Have to admit I'm not completely sure how/where/when those tables are updated.)

The following query - simple as it is - may do the trick. It generates a report of all contributions that began before one second into the day of 2013-07-01 and were completed after that time. (thankyou_date is locally used as invoice_date - coded before my time.)

mysql -B example_civicrm -e "SELECT i.invoice_id,
       i.contact_id     AS cid,
       c.display_name   AS name,
       i.total_amount,
       t.name           AS type,
       i.source,
       i.receive_date,
       i.thankyou_date  AS invoice_date,
       i.receipt_date,
       o.name           AS status
     FROM civicrm_contribution      i
LEFT JOIN civicrm_contact           c ON i.contact_id = c.id
LEFT JOIN civicrm_contribution_type t ON i.contribution_type_id = t.id
LEFT JOIN civicrm_option_value      o ON (i.contribution_status_id = o.value AND o.option_group_id = 11)
    WHERE i.cancel_date is NULL
      AND (i.thankyou_date <= '2013-07-01 00:00:01' OR i.receive_date <= '2013-07-01 00:00:01')
      AND i.receipt_date >  '2013-07-01 00:00:01'" > pending.csv