Util.php 11.5 KB
Newer Older
Monish Deb's avatar
Monish Deb committed
1 2 3 4 5 6 7 8
<?php

/**
 * Class to send Moodle API request
 */
class CRM_Syncintacct_Util {


Monish Deb's avatar
Monish Deb committed
9 10 11 12 13 14 15 16 17 18 19 20 21 22 23
  /**
   * IF the given array of batch IDs consist of any transactions related to grant payment
   */
  public static function batchesByEntityTable($batchIDs, $entityTable) {
      $sql = "SELECT COUNT(eb.batch_id)
      FROM civicrm_entity_batch eb
      INNER JOIN civicrm_financial_trxn tx ON tx.id = eb.entity_id AND eb.entity_table = 'civicrm_financial_trxn'
      INNER JOIN civicrm_entity_financial_trxn eft ON eft.financial_trxn_id = tx.id AND eft.entity_table = '{$entityTable}'
      INNER JOIN civicrm_batch b ON b.id = eb.batch_id
      WHERE eb.batch_id IN (" . implode(',', $batchIDs) . ")
      GROUP BY eb.batch_id";
      $dao = CRM_Core_DAO::executeQuery($sql);
       return $dao->N;
  }

24
  public static function fetchEntries($batchID, $entityType) {
25
    $entityTable = ($entityType == 'GL') ? 'civicrm_contribution' : 'civicrm_grant';
Monish Deb's avatar
Monish Deb committed
26 27 28 29 30 31 32 33 34 35 36 37
    $sql = "SELECT
      ft.id as financial_trxn_id,
      ft.trxn_date,
      fa_to.accounting_code AS to_account_code,
      fa_to.name AS to_account_name,
      fa_to.account_type_code AS to_account_type_code,
      ft.total_amount AS debit_total_amount,
      ft.trxn_id AS trxn_id,
      cov.label AS payment_instrument,
      ft.check_number,
      c.source AS source,
      c.id AS contribution_id,
38
      c.total_amount AS contribution_amount,
Monish Deb's avatar
Monish Deb committed
39
      c.contact_id AS contact_id,
Monish Deb's avatar
Monish Deb committed
40
      cc.display_name,
Monish Deb's avatar
Monish Deb committed
41 42 43 44 45 46 47 48 49 50 51 52 53 54
      eb.batch_id AS batch_id,
      ft.currency AS currency,
      cov_status.label AS status,
      CASE
        WHEN efti.entity_id IS NOT NULL
        THEN efti.amount
        ELSE eftc.amount
      END AS amount,
      fa_from.account_type_code AS credit_account_type_code,
      fa_from.accounting_code AS credit_account,
      fa_from.name AS credit_account_name,
      fac.account_type_code AS from_credit_account_type_code,
      fac.accounting_code AS from_credit_account,
      fac.name AS from_credit_account_name,
Monish Deb's avatar
Monish Deb committed
55
      fi.description AS item_description,
56 57
      fi.id AS financial_item_id,
      eftc.entity_id AS entity_id
Monish Deb's avatar
Monish Deb committed
58 59 60 61 62 63
      FROM civicrm_entity_batch eb
      LEFT JOIN civicrm_financial_trxn ft ON (eb.entity_id = ft.id AND eb.entity_table = 'civicrm_financial_trxn')
      LEFT JOIN civicrm_financial_account fa_to ON fa_to.id = ft.to_financial_account_id
      LEFT JOIN civicrm_financial_account fa_from ON fa_from.id = ft.from_financial_account_id
      LEFT JOIN civicrm_option_group cog ON cog.name = 'payment_instrument'
      LEFT JOIN civicrm_option_value cov ON (cov.value = ft.payment_instrument_id AND cov.option_group_id = cog.id)
64
      LEFT JOIN civicrm_entity_financial_trxn eftc ON (eftc.financial_trxn_id  = ft.id AND eftc.entity_table = '{$entityTable}')
Monish Deb's avatar
Monish Deb committed
65
      LEFT JOIN civicrm_contribution c ON c.id = eftc.entity_id
Monish Deb's avatar
Monish Deb committed
66
      LEFT JOIN civicrm_contact cc ON cc.id = c.contact_id
Monish Deb's avatar
Monish Deb committed
67 68 69 70 71 72 73 74
      LEFT JOIN civicrm_option_group cog_status ON cog_status.name = 'contribution_status'
      LEFT JOIN civicrm_option_value cov_status ON (cov_status.value = ft.status_id AND cov_status.option_group_id = cog_status.id)
      LEFT JOIN civicrm_entity_financial_trxn efti ON (efti.financial_trxn_id  = ft.id AND efti.entity_table = 'civicrm_financial_item')
      LEFT JOIN civicrm_financial_item fi ON fi.id = efti.entity_id
      LEFT JOIN civicrm_financial_account fac ON fac.id = fi.financial_account_id
      LEFT JOIN civicrm_financial_account fa ON fa.id = fi.financial_account_id
      WHERE eb.batch_id = ( %1 )";

Monish Deb's avatar
Monish Deb committed
75
    $params = array(1 => array($batchID, 'Integer'));
Monish Deb's avatar
Monish Deb committed
76
    $dao = CRM_Core_DAO::executeQuery($sql, $params);
Monish Deb's avatar
Monish Deb committed
77

78
    if ($entityType == 'AP') {
79
      return self::formatAPBatchParams($dao, $batchID);
80 81
    }
    else {
82
      return self::formatGLBatchParams($dao, $batchID);
83 84 85
    }
  }

86 87 88 89 90 91 92 93 94

  public static function getAccountDataByCode($code) {
    $result = CRM_Core_DAO::executeQuery("
       SELECT ifad.* FROM civicrm_intacct_financial_account_data ifad
       INNER JOIN civicrm_financial_account fa ON ifad.financial_account_id = fa.id AND fa.accounting_code = '$code' ")
       ->fetchAll();
    return CRM_Utils_Array::value(0, $result);
  }

95
  public static function formatGLBatchParams($dao, $batchID) {
Monish Deb's avatar
Monish Deb committed
96 97 98 99 100 101 102
    $batch = civicrm_api3('Batch', 'getsingle', ['id' => $batchID]);
    $GLBatch = [
      'JOURNAL' => 'CIVIBATCH' . $batchID,
      'BATCH_DATE' => new DateTime($batch['created_date']),
      'BATCH_TITLE' => $batch['title'],
      'ENTRIES' => [],
    ];
Monish Deb's avatar
Monish Deb committed
103
    $queryResults = [];
Monish Deb's avatar
Monish Deb committed
104
    while ($dao->fetch()) {
105 106
      $accountCode = $dao->credit_account ?: $dao->from_credit_account;
      $values = self::getAccountDataByCode($accountCode);
107
      $GLBatch['ENTRIES'][] = [
108
        'ACCOUNTNO' => $accountCode,
109 110 111 112
        'VENDORID' => $dao->display_name,
        'CURRENCY' => $dao->currency,
        'AMOUNT' => -$dao->debit_total_amount,
        'DESCRIPTION' => $dao->item_description,
113 114
        'CLASSID' => $values['class_id'],
        'DEPARTMENT' => $values['dept_id'],
115
        'CONTRIBUTION_AMOUNT' => $dao->contribution_amount,
116 117
        'LOCATION' => $values['location'],
        'PROJECTID' => $values['project_id'],
118 119 120 121
        'customfields' => [
          'batch_id' => $batchID,
          'financial_trxn_id' => $dao->financial_trxn_id,
          'financial_item_id' => $dao->financial_item_id,
122
          'url' => CRM_Utils_System::url('civicrm/contact/view/contribution', "reset=1&id={$dao->entity_id}&cid={$dao->contact_id}&action=view", TRUE),
123 124
        ]
      ];
125
      $values = self::getAccountDataByCode($dao->to_account_code);
Monish Deb's avatar
Monish Deb committed
126 127 128 129 130 131
      $GLBatch['ENTRIES'][] = [
        'ACCOUNTNO' => $dao->to_account_code,
        'VENDORID' => $dao->display_name,
        'CURRENCY' => $dao->currency,
        'AMOUNT' => $dao->debit_total_amount,
        'DESCRIPTION' => $dao->item_description,
132 133 134 135
        'CLASSID' => $values['class_id'],
        'DEPARTMENT' => $values['dept_id'],
        'LOCATION' => $values['location'],
        'PROJECTID' => $values['project_id'],
136
        'CONTRIBUTION_AMOUNT' => $dao->contribution_amount,
Monish Deb's avatar
Monish Deb committed
137 138 139 140
        'customfields' => [
          'batch_id' => $batchID,
          'financial_trxn_id' => $dao->financial_trxn_id,
          'financial_item_id' => $dao->financial_item_id,
141
          'url' => CRM_Utils_System::url('civicrm/contact/view/contribution', "reset=1&id={$dao->entity_id}&cid={$dao->contact_id}&action=view", TRUE),
142
        ],
Monish Deb's avatar
Monish Deb committed
143
      ];
Monish Deb's avatar
Monish Deb committed
144
      $queryResults[] = get_object_vars($dao);
Monish Deb's avatar
Monish Deb committed
145 146
    }

Monish Deb's avatar
Monish Deb committed
147 148
    CRM_Utils_Hook::batchItems($queryResults, $GLBatch);

Monish Deb's avatar
Monish Deb committed
149
    return $GLBatch;
Monish Deb's avatar
Monish Deb committed
150 151
  }

152 153 154 155 156 157 158 159 160 161 162
  public static function formatAPBatchParams($dao, $batchID) {
    $APBatch = [];
    while ($dao->fetch()) {
      $APBatch[$dao->entity_id] = [
        'CURRENCY' => $dao->currency,
        'VENDORID' => $dao->display_name,
        'DESCRIPTION' => $dao->item_description,
        'TRXN_DATE' => new DateTime($dao->trxn_date),
        'DUE_DATE' => new DateTime(date('Ymd')),
        'ENTRIES' => [],
      ];
Monish Deb's avatar
Monish Deb committed
163 164
      $accountCode = $dao->credit_account ?: $dao->from_credit_account;
      $values = self::getAccountDataByCode($accountCode);
165
      $APBatch[$dao->entity_id]['ENTRIES'][] = [
Monish Deb's avatar
Monish Deb committed
166
        'ACCOUNTNO' => $accountCode,
167
        'AMOUNT' => -$dao->debit_total_amount,
Monish Deb's avatar
Monish Deb committed
168 169 170 171
        'CLASSID' => $values['class_id'],
        'DEPARTMENT' => $values['dept_id'],
        'LOCATION' => $values['location'],
        'PROJECTID' => $values['project_id'],
172 173 174 175
        'customfields' => [
          'batch_id' => $batchID,
          'financial_trxn_id' => $dao->financial_trxn_id,
          'financial_item_id' => $dao->financial_item_id,
176
          'url' => CRM_Utils_System::url('civicrm/contact/view/grant', "reset=1&id={$dao->entity_id}&cid={$dao->contact_id}&action=view", TRUE),
177 178 179 180 181 182 183
        ]
      ];
    }

    return $APBatch;
  }

184 185 186 187 188 189 190 191 192
  public static function createEntriesByType($batchEntries, $entityType) {
    if ($entityType == 'AP') {
      return self::createAPEntries($batchEntries);
    }
    else {
      return self::createGLEntries($batchEntries);
    }
  }

193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217
  public static function createAPEntries($batchEntries) {
    $syncIntacctConfig = CRM_Syncintacct_API::singleton();
    $fetchVendors = $syncIntacctConfig->getVendors(array_unique(CRM_Utils_Array::collect('VENDORID', $batchEntries)));
    $displayNames = [];
    $result = '';
    foreach ($fetchVendors as $vendor) {
      $key = (string) $vendor->NAME;
      $displayNames[$key] = (string) $vendor->VENDORID;
    }

    foreach ($batchEntries as $trxnID => &$entry) {
      $vendorID = CRM_Utils_Array::value($entry['VENDORID'], $displayNames);
      if (strstr($vendorID, 'VEN-')) {
        $entry['VENDORID'] = $vendorID;
      }
      else {
        $result = $syncIntacctConfig->createVendors($entry['VENDORID']);
        if (!empty($result[0])) {
          $entry['VENDORID'] = (string) $result[0]->VENDORID;
        }
      }
      foreach ($entry['ENTRIES'] as $key => $trxn) {
        $entry['ENTRIES'][$key] = $syncIntacctConfig->createAPEntry($trxn);
      }

Monish Deb's avatar
Monish Deb committed
218
      $response = $syncIntacctConfig->createAPBatch($entry);
219 220 221 222
    }
    return $response;
  }

Monish Deb's avatar
Monish Deb committed
223
  public static function createGLEntries($batchEntries) {
224 225
    $syncIntacctConfig = CRM_Syncintacct_API::singleton();
    $fetchVendors = $syncIntacctConfig->getVendors(array_unique(CRM_Utils_Array::collect('VENDORID', $batchEntries['ENTRIES'])));
Monish Deb's avatar
Monish Deb committed
226

227
    $displayNames = [];
228
    $result = '';
Monish Deb's avatar
Monish Deb committed
229
    foreach ($fetchVendors as $vendor) {
230 231
      $key = (string) $vendor->NAME;
      $displayNames[$key] = (string) $vendor->VENDORID;
Monish Deb's avatar
Monish Deb committed
232 233
    }

234
    foreach ($batchEntries['ENTRIES'] as $key => &$entry) {
Monish Deb's avatar
Monish Deb committed
235
      $vendorID = CRM_Utils_Array::value($entry['VENDORID'], $displayNames);
236 237
      if (strstr($vendorID, 'VEN-')) {
        $entry['VENDORID'] = $vendorID;
Monish Deb's avatar
Monish Deb committed
238 239
      }
      else {
240
        $result = $syncIntacctConfig->createVendors($entry['VENDORID']);
241 242 243
        if (!empty($result[0])) {
          $batchEntries['ENTRIES'][$key]['VENDORID'] = (string) $result[0]->VENDORID;
        }
Monish Deb's avatar
Monish Deb committed
244
      }
245
      $batchEntries['ENTRIES'][$key] = $syncIntacctConfig->createGLEntry($entry);
Monish Deb's avatar
Monish Deb committed
246 247
    }

248
    return $syncIntacctConfig->createGLBatch($batchEntries);
249 250 251 252 253 254 255 256 257 258 259 260
  }

  public static function processSyncIntacctResponse($batchID, $response) {
    $activity = civicrm_api3('Activity', 'getsingle', [
      'source_record_id' => $batchID,
      'status_id' => CRM_Core_PseudoConstant::getKey('CRM_Activity_BAO_Activity', 'status_id', 'Scheduled'),
      'activity_type_id' => CRM_Core_PseudoConstant::getKey('CRM_Activity_BAO_Activity', 'activity_type_id', 'Export Accounting Batch'),
    ]);

    $fileName = CRM_Core_Config::singleton()->uploadDir . 'Financial_Transactions_Response_' . date('YmdHis') . '.txt';
    $content = sprintf('Batch ID - %d: %s', $batchID, var_export($response, TRUE));
    file_put_contents($fileName, $content, FILE_APPEND);
Monish Deb's avatar
Monish Deb committed
261

262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291
    $activityParams = array(
      'id' => $activity['id'],
      'attachFile_2' => array(
        'uri' => $fileName,
        'type' => 'text/plain',
        'location' => $fileName,
        'upload_date' => date('YmdHis'),
      ),
    );
    if (!empty($response['is_error'])) {
      $email =  Civi::settings()->get('send_error_to_email');
      if ($email) {
        $params = [
          'toEmail' => $email,
          'subject' => ts('Intacct response error for Batch ID ' . $batchID),
          'text' => $content,
          'html' => $content,
        ];
        CRM_Utils_Mail::send($params);
      }
    }
    else {
      $activityParams['status_id'] = CRM_Core_PseudoConstant::getKey('CRM_Activity_BAO_Activity', 'status_id', 'Completed');
      civicrm_api3('Batch', 'create', [
        'id' => $batchID,
        'data' => 'Synchronization completed at ' . date('Y-m-d H:i:s'),
      ]);
      CRM_Core_DAO::executeQuery("DELETE FROM civicrm_intacct_batches WHERE batch_id = " . $batchID);
    }
    CRM_Activity_BAO_Activity::create($activityParams);
Monish Deb's avatar
Monish Deb committed
292
  }
Monish Deb's avatar
Monish Deb committed
293 294

}