/** * Generate CSV array for export. * * @param array $export */ public function makeExport($export) { // getting data from admin page $prefixValue = Civi::settings()->get('contribution_invoice_settings'); foreach ($export as $batchId => $dao) { $financialItems = array(); $this->_batchIds = $batchId; $batchItems = array(); $queryResults = array(); while ($dao->fetch()) { $creditAccountName = $creditAccountType = $creditAccount = NULL; if ($dao->credit_account) { $creditAccountName = $dao->credit_account_name; $creditAccountType = $dao->credit_account_type_code; $creditAccount = $dao->credit_account; } else { $creditAccountName = $dao->from_credit_account_name; $creditAccountType = $dao->from_credit_account_type_code; $creditAccount = $dao->from_credit_account; } $invoiceNo = CRM_Utils_Array::value('invoice_prefix', $prefixValue) . "" . $dao->contribution_id; $financialItems[] = array('Batch ID' => $dao->batch_id, 'Invoice No' => $invoiceNo, 'Contact ID' => $dao->contact_id, 'Financial Trxn ID/Internal ID' => $dao->financial_trxn_id, 'Transaction Date' => $dao->trxn_date, 'Debit Account' => $dao->to_account_code, 'Debit Account Name' => $dao->to_account_name, 'Debit Account Type' => $dao->to_account_type_code, 'Debit Account Amount (Unsplit)' => $dao->debit_total_amount, 'Transaction ID (Unsplit)' => $dao->trxn_id, 'Debit amount (Split)' => $dao->amount, 'Payment Instrument' => $dao->payment_instrument, 'Check Number' => $dao->check_number, 'Source' => $dao->source, 'Currency' => $dao->currency, 'Transaction Status' => $dao->status, 'Amount' => $dao->amount, 'Credit Account' => $creditAccount, 'Credit Account Name' => $creditAccountName, 'Credit Account Type' => $creditAccountType, 'Item Description' => $dao->item_description); end($financialItems); $batchItems[] =& $financialItems[key($financialItems)]; $queryResults[] = get_object_vars($dao); } CRM_Utils_Hook::batchItems($queryResults, $batchItems); $financialItems['headers'] = self::formatHeaders($financialItems); self::export($financialItems); } parent::initiateDownload(); }
/** * @param $export */ function makeIIF($export) { // Keep running list of accounts and contacts used in this batch, since we need to // include those in the output. Only want to include ones used in the batch, not everything in the db, // since would increase the chance of messing up user's existing Quickbooks entries. foreach ($export as $batchId => $dao) { $accounts = $contacts = $journalEntries = $exportParams = array(); $this->_batchIds = $batchId; while ($dao->fetch()) { // add to running list of accounts if (!empty($dao->from_account_id) && !isset($accounts[$dao->from_account_id])) { $accounts[$dao->from_account_id] = array('name' => $this->format($dao->from_account_name), 'account_code' => $this->format($dao->from_account_code), 'description' => $this->format($dao->from_account_description), 'type' => $this->format($dao->from_account_type_code)); } if (!empty($dao->to_account_id) && !isset($accounts[$dao->to_account_id])) { $accounts[$dao->to_account_id] = array('name' => $this->format($dao->to_account_name), 'account_code' => $this->format($dao->to_account_code), 'description' => $this->format($dao->to_account_description), 'type' => $this->format($dao->to_account_type_code)); } // add to running list of contacts if (!empty($dao->contact_from_id) && !isset($contacts[$dao->contact_from_id])) { $contacts[$dao->contact_from_id] = array('name' => $this->format($dao->contact_from_name), 'first_name' => $this->format($dao->contact_from_first_name), 'last_name' => $this->format($dao->contact_from_last_name)); } if (!empty($dao->contact_to_id) && !isset($contacts[$dao->contact_to_id])) { $contacts[$dao->contact_to_id] = array('name' => $this->format($dao->contact_to_name), 'first_name' => $this->format($dao->contact_to_first_name), 'last_name' => $this->format($dao->contact_to_last_name)); } // set up the journal entries for this financial trxn $journalEntries[$dao->financial_trxn_id] = array('to_account' => array('trxn_date' => $this->format($dao->trxn_date, 'date'), 'trxn_id' => $this->format($dao->trxn_id), 'account_name' => $this->format($dao->to_account_name), 'amount' => $this->format($dao->debit_total_amount, 'money'), 'contact_name' => $this->format($dao->contact_to_name), 'payment_instrument' => $this->format($dao->payment_instrument), 'check_number' => $this->format($dao->check_number)), 'splits' => array()); /* * splits has two possibilities depending on FROM account */ if (empty($dao->from_account_id)) { // In this case, split records need to use the individual financial_item account for each item in the trxn $item_sql = "SELECT\n fa.id AS account_id,\n fa.name AS account_name,\n fa.accounting_code AS account_code,\n fa.description AS account_description,\n fi.description AS description,\n fi.id AS financial_item_id,\n fi.currency AS currency,\n cov.label AS payment_instrument,\n ft.check_number AS check_number,\n fi.transaction_date AS transaction_date,\n fi.amount AS amount,\n fa.account_type_code AS account_type_code,\n contact.id AS contact_id,\n contact.display_name AS contact_name,\n contact.first_name AS contact_first_name,\n contact.last_name AS contact_last_name\n FROM civicrm_entity_financial_trxn eft\n LEFT JOIN civicrm_financial_item fi ON eft.entity_id = fi.id\n LEFT JOIN civicrm_financial_trxn ft ON ft.id = eft.financial_trxn_id\n LEFT JOIN civicrm_option_group cog ON cog.name = 'payment_instrument'\n LEFT JOIN civicrm_option_value cov ON (cov.value = ft.payment_instrument_id AND cov.option_group_id = cog.id)\n LEFT JOIN civicrm_financial_account fa ON fa.id = fi.financial_account_id\n LEFT JOIN civicrm_contact contact ON contact.id = fi.contact_id\n WHERE eft.entity_table = 'civicrm_financial_item'\n AND eft.financial_trxn_id = %1"; $itemParams = array(1 => array($dao->financial_trxn_id, 'Integer')); $itemDAO = CRM_Core_DAO::executeQuery($item_sql, $itemParams); while ($itemDAO->fetch()) { // add to running list of accounts if (!empty($itemDAO->account_id) && !isset($accounts[$itemDAO->account_id])) { $accounts[$itemDAO->account_id] = array('name' => $this->format($itemDAO->account_name), 'account_code' => $this->format($itemDAO->account_code), 'description' => $this->format($itemDAO->account_description), 'type' => $this->format($itemDAO->account_type_code)); } if (!empty($itemDAO->contact_id) && !isset($contacts[$itemDAO->contact_id])) { $contacts[$itemDAO->contact_id] = array('name' => $this->format($itemDAO->contact_name), 'first_name' => $this->format($itemDAO->contact_first_name), 'last_name' => $this->format($itemDAO->contact_last_name)); } // add split line for this item $journalEntries[$dao->financial_trxn_id]['splits'][$itemDAO->financial_item_id] = array('trxn_date' => $this->format($itemDAO->transaction_date, 'date'), 'spl_id' => $this->format($itemDAO->financial_item_id), 'account_name' => $this->format($itemDAO->account_name), 'amount' => '-' . $this->format($itemDAO->amount, 'money'), 'contact_name' => $this->format($itemDAO->contact_name), 'payment_instrument' => $this->format($itemDAO->payment_instrument), 'description' => $this->format($itemDAO->description), 'check_number' => $this->format($itemDAO->check_number), 'currency' => $this->format($itemDAO->currency)); } // end items loop $itemDAO->free(); } else { // In this case, split record just uses the FROM account from the trxn, and there's only one record here $journalEntries[$dao->financial_trxn_id]['splits'][] = array('trxn_date' => $this->format($dao->trxn_date, 'date'), 'spl_id' => $this->format($dao->financial_trxn_id), 'account_name' => $this->format($dao->from_account_name), 'amount' => '-' . $this->format($dao->debit_total_amount, 'money'), 'contact_name' => $this->format($dao->contact_from_name), 'description' => $this->format($dao->item_description), 'payment_instrument' => $this->format($dao->payment_instrument), 'check_number' => $this->format($dao->check_number), 'currency' => $this->format($dao->currency)); } } $exportParams = array('accounts' => $accounts, 'contacts' => $contacts, 'journalEntries' => $journalEntries); self::export($exportParams); } parent::initiateDownload(); }
/** * Generate CSV array for export. * * @param array $export */ public function makeCSV($export) { foreach ($export as $batchId => $dao) { $financialItems = array(); $this->_batchIds = $batchId; while ($dao->fetch()) { $creditAccountName = $creditAccountType = $creditAccount = NULL; if ($dao->credit_account) { $creditAccountName = $dao->credit_account_name; $creditAccountType = $dao->credit_account_type_code; $creditAccount = $dao->credit_account; } else { $creditAccountName = $dao->from_credit_account_name; $creditAccountType = $dao->from_credit_account_type_code; $creditAccount = $dao->from_credit_account; } $financialItems[] = array('Internal ID' => $dao->financial_trxn_id, 'Transaction Date' => $dao->trxn_date, 'Debit Account' => $dao->to_account_code, 'Debit Account Name' => $dao->to_account_name, 'Debit Account Type' => $dao->to_account_type_code, 'Debit Account Amount (Unsplit)' => $dao->debit_total_amount, 'Transaction ID (Unsplit)' => $dao->trxn_id, 'Debit amount (Split)' => $dao->amount, 'Payment Instrument' => $dao->payment_instrument, 'Check Number' => $dao->check_number, 'Source' => $dao->source, 'Currency' => $dao->currency, 'Transaction Status' => $dao->status, 'Amount' => $dao->amount, 'Credit Account' => $creditAccount, 'Credit Account Name' => $creditAccountName, 'Credit Account Type' => $creditAccountType, 'Item Description' => $dao->item_description); } $financialItems['headers'] = self::formatHeaders($financialItems); self::export($financialItems); } parent::initiateDownload(); }