Odyssey
sAPIAppl.std.i
1 <?php
2  /**
3  * Plugin for the sAPIAppl.i script
4  *
5  * These functions will execute when the API is using the PACKET_REQUEST_FULL type
6  *
7  * These are compatible with the the Existing Legacy type
8  *
9  */
10  /**
11  * *** LoadUserData
12  * *** SetbackUserStatus
13  * *** _ReturnAccountPacketData
14  * *** _ReturnMemberAcctList
15  * *** _FetchLastPacketStatus
16  * *** _SetPacketStatus
17  * *** _SaveMemberData
18  * *** _SaveMemberXAData
19  * *** _DeleteInsertCrossAcct
20  * *** _UpdateCopyIn
21  * *** _UpdateCopyInHistory
22  * *** _UpdateUserAccounts
23  * ***
24  */
25 
26 
27 /**
28  * Request member packets from the core for all the user's member accounts
29  * This is a standard api request. Both balances and history will be retrieved
30  * and updated in the database
31  *
32  * @param array $pHbEnv -- Current HB_ENV setting
33  * @param integer $pUserId -- User ID for data that is being requested
34  * @param array $pUserData -- Array of data for the user
35  * email - The email to send to core that goes with the member
36  * lastlogin - The lasttime the user logged in.. This is questionable.. at this time it will be the last time this USER logged in.
37  * @param boolean $pForceUpdate -- *optional (false [default], true - the function will ignore the 'livewait' value and force an update of the user accounts)
38  * @return array
39  * [code]
40  * {000, 900, 999}
41  * 000 - Success - This can be set if the response from the core was successful and was not one of the known error codes.
42  * 900 - NO ACTION - This can be returned for "too soon to ask again"
43  * 999 - Failure - This happens when the routine fails for any reason. From communication to core error.
44  * [error]
45  * [data]
46  * 'requestDate' => packet stamp formatted to 'D M j Y H:i:s T'
47  * 'requestDesc' => Message from Core
48 */
49 function LoadUserData($pHbEnv, $pUserId, $pUserData, $pForceUpdate=false) {
50  $retVal = Array("code" => "000", "data" => Array());
51 
52  // ** Record if any updates were needed -- IF ALL accounts were skipped, then return "TOO SOON"
53  $noUpdatesNeeded = true;
54 
55  $mbrReqType = PACKET_REQUEST_INQUIRY;
56  try {
57  /*
58  * Get CU Connection Settings
59  */
60  $cuConnResp = _ReturnCUSettings($pHbEnv);
61  if ($cuConnResp['code'] != '000') {
62  throw new Exception ("Unable to Load CU Settings");
63  }
64  $cuConnSet = HCU_array_key_value('data', $cuConnResp);
65 
66  _ApplPrintLogger($pHbEnv, $cuConnSet, "\n** Load User Data **\n");
67 
68  /*
69  * Get Member Account List
70  */
71  $mbrAcctList = _ReturnMemberAcctList($pHbEnv, $pUserId);
72  if ($mbrAcctList['code'] != '000') {
73  throw new Exception ("No Accounts Found");
74  }
75 
76  /*
77  * Loop through Member List
78  */
79 
80  /**
81  * Last Login
82  */
83  $mbrLastLogin = (HCU_array_key_value('lastlogin', $pUserData) == '' ? 'NULL' : HCU_array_key_value('lastlogin', $pUserData));
84 
85  $lastPktDate = date('D M j Y H:i:s T'); // set default
86  /*
87  * Retrieve Member Data
88  */
89  foreach ($mbrAcctList['data'] as $idx => $subAcctItem) {
90  /**
91  * @var string Account number
92  */
93  $reqMemberAcctNbr = $subAcctItem['accountnumber'];
94  // ** Using the email from the user isn't always the right answer
95  // ** we may want
96 
97  /**
98  * mbrEmail
99  */
100  $paramEmail = HCU_array_key_value('email', $pUserData);
101  $mbrEmail = (($paramEmail == '' || $paramEmail == 'no@email.com') ? "NULL" : trim($paramEmail));
102 
103  /**
104  * Member Account Packet Stamps
105  */
106  $lastAcctStatus = _FetchLastPacketStatus($pHbEnv, $reqMemberAcctNbr);
107  if ($lastAcctStatus['code'] == '999') {
108  throw new Exception ("Unable to read account status", 50115);
109  }
110 
111  $pktStamps = HCU_array_key_value('data', $lastAcctStatus);
112  _ApplPrintLogger($pHbEnv, $cuConnSet, "\nPacketStamps\n" . print_r($pktStamps, true));
113 
114  // ** Need Packet Stamp
115  $lastPktStamp = HCU_array_key_value('balance_stamp', $pktStamps);
116  /**
117  * Set the Last Packet Date
118  */
119  if ($lastPktStamp <= 1) {
120  // * for all 3 of these situations the Last packet Date will be a format of time()
121  $lastPktDate = date('D M j Y H:i:s T');
122  } else {
123  // ** Need Packet Date (human readable version of packet stamp -- mammoth may have had this properly formatted to appliance timezone as it was returned from appliance)
124  $lastPktDate = date('D M j Y H:i:s T', $lastPktStamp);
125  }
126  $lastPktAttempt = HCU_array_key_value('balance_attempt', $pktStamps);
127 
128  /**
129  * ** LAST CHECK TO SEE IF WE SHOULD GET DATA -- TOO SOON?
130  */
131 
132  $waitTimeEpoch = time() - HCU_array_key_value('livewait', $cuConnSet);
133  // if $pForceUpdate is true then skip this check as we want the function to continue
134  if ((abs($lastPktStamp) > $waitTimeEpoch || $lastPktAttempt > $waitTimeEpoch) && !$pForceUpdate) {
135  // ** We are in a WAITING TIMEOUT -- DO NOT HAMMER THE APPLIANCE
136 
137 // -- Don't exit LOOP -- Go to next throw new Exception($lastPktDate, 50111);
138 
139  continue;
140 
141  }
142  // * Any account request getting this far will return the packet request for that particular account
143  $noUpdatesNeeded = false;
144 
145  /**
146  * Additional logic for Last Packet Stamp
147  */
148  /* If last packet stamp < 0 then the last attempt failed
149  * If the packet stamp = 1 then we will be getting a full refresh
150  * If the packet stamp = 0 then this may be the first time
151  */
152  $lastPktStamp = ($lastPktStamp < 1 ? 1 : $lastPktStamp);
153 
154  $pktStamps['laststamp'] = $lastPktStamp;
155  $pktStamps['lastattempt'] = $lastPktAttempt;
156  /**
157  * ** GET CUTOFF DATE **
158  */
159  $packetCutoff = _ReturnCutoffDate($cuConnSet, $pktStamps, $mbrReqType);
160 
161  _ApplPrintLogger($pHbEnv, $cuConnSet, "\n Packet Cutoff $packetCutoff ");
162 
163  /**
164  * ** START THE PROCESS OF REQUESTING DATA FROM THE CORE
165  */
166  /**
167  * ** CREATE NEW PACKET ATTEMPT **
168  * ** NEGATE PACKET STAMP IN DB PRIOR TO REQUEST
169  */
170  $newPacketAttempt = time();
171 
172  $pktData = Array(
173  "accountnumber" => $reqMemberAcctNbr,
174  "balance_attempt" => $newPacketAttempt,
175  "history_attempt" => $newPacketAttempt
176  );
177 
178  /**
179  * ** SET PACKET STATUS TO PREVENT OVER QUERY
180  */
181  $respStatus = _SetPacketStatus($pHbEnv, $pktData);
182  if ($respStatus['code'] != '000') {
183  throw new Exception('Error updating Accounts: ' . HCU_JsonEncode($respStatus['error']), 50120);
184  }
185 
186  /**
187  * ** CREATE NEW PACKET ATTEMPT **
188  * ** NEGATE PACKET STAMP IN DB PRIOR TO REQUEST
189  */
190  $newPacketAttempt = time();
191 
192  $pktData = Array(
193  "accountnumber" => $reqMemberAcctNbr,
194  "balance_attempt" => $newPacketAttempt,
195  "history_attempt" => $newPacketAttempt
196  );
197 
198 
199  /**
200  * ** Build Data Request
201  */
202 
203  $dataArray = Array(
204  "member" => $reqMemberAcctNbr,
205  "type" => 'I',
206  "if_mod_since" => $lastPktStamp,
207  "ref3" => $mbrEmail,
208  "lastlogin" => $mbrLastLogin,
209  "cutoff" => $packetCutoff
210  );
211  _ApplPrintLogger($pHbEnv, $cuConnSet, print_r($dataArray, true));
212  /*
213  * Inquiry Packet Request
214  */
215  $acctResp = _RetrieveMemberInquiry($pHbEnv, $cuConnSet, $dataArray);
216 
217  if (!in_array($acctResp['code'], array('000', '001', '002', '003'))) {
218 // if ($acctResp['code'] != '000') {
219 // if (in_array($acctResp['code'], array('001', '002', '003'))) {
220 // throw new Exception ($acctResp['error'], 50102);
221 // }
222  throw new Exception ("Unable to retrieve list of accounts - " . $acctResp['error']);
223  }
224 
225 
226  // ** Get the stat code from the packet
227  $packetStatus = HCU_array_key_value("packet-status", $acctResp['data']);
228  _ApplPrintLogger($pHbEnv, $cuConnSet, " ** Packet Status Array ** " . print_r($packetStatus, true));
229 
230  switch (HCU_array_key_value('pktStatCode', $packetStatus)) {
231  case "100":
232 
233  // ** NO NEW DATA -- Update packet stamps only
234  $pktTime = HCU_array_key_value('pktTime', $packetStatus);
235 
236  _ApplPrintLogger($pHbEnv, $cuConnSet, " * 100 No New Data * $pktTime" );
237 
238  $newPacketAttempt = time();
239  $pktData = Array(
240  "accountnumber" => $reqMemberAcctNbr,
241  "balance_stamp" => $pktTime,
242  "balance_attempt" => $newPacketAttempt,
243  "history_stamp" => $pktTime,
244  "history_attempt" => $newPacketAttempt
245  );
246 
247  /**
248  * ** SET PACKET STATUS TO PREVENT OVER QUERY
249  */
250  $respStatus = _SetPacketStatus($pHbEnv, $pktData);
251  if ($respStatus['code'] != '000') {
252  throw new Exception('Error updating Accounts: ' . HCU_JsonEncode($respStatus['error']), 50120);
253  }
254 
255  break;
256  case "101":
257  // ** Save the information
258  $xmlPacket = $acctResp['data']['packet-xml'];
259 
260  /*
261  * Update tables balance/history/holds
262  */
263 
264  // ** Need Packet Stamp
265  $lastPktStamp = HCU_array_key_value('balance_stamp', $pktStamps);
266  $lastPktStamp = ($lastPktStamp == '' ? 1 : $lastPktStamp);
267  $newTimeStamp = time();
268  /**
269  * Set the Last Packet Date
270  */
271  if ($lastPktStamp <= 1) {
272  // * for all 3 of these situations the Last packet Date will be a format of time()
273  $lastPktDate = date('D M j Y H:i:s T');
274  } else {
275  // ** Need Packet Date (human readable version of packet stamp -- mammoth may have had this properly formatted to appliance timezone as it was returned from appliance)
276  $lastPktDate = date('D M j Y H:i:s T', HCU_array_key_value('pktTime', $packetStatus));
277  }
278 
279  /**
280  * ** LAST CHECK TO SEE IF WE SHOULD GET DATA -- TOO SOON?
281  */
282  $waitTimeEpoch = time() - HCU_array_key_value('livewait', $cuConnSet);
283  if (abs($lastPktStamp) > $waitTimeEpoch) {
284  // ** We are in a WAITING TIMEOUT -- DO NOT HAMMER THE APPLIANCE
285  throw new Exception($lastPktDate, '50111');
286  }
287 
288  /**
289  * PACKET STAMP -- LOAD FROM PACKET ??
290  *
291  $pktStamps['history_stamp'] = $acctResp['data']['packet-status']['pktTime'];
292  $pktStamps['balance_stamp'] = $acctResp['data']['packet-status']['pktTime'];
293  */
294 
295  /*
296  * LOOKS GOOD -- CONTINUE
297  * UPDATE DATABASE
298  */
299  $mbrData = Array(
300  'type' => PACKET_REQUEST_INQUIRY,
301  'member' => $reqMemberAcctNbr,
302  'cutoff' => $packetCutoff
303  );
304  $dataResp = _SaveMemberData($pHbEnv, $cuConnSet, $pktStamps, $mbrData, $xmlPacket);
305  if ($dataResp['code'] == '999') {
306  throw new Exception ("Update Failure", 999);
307  }
308  break;
309  case "001":
310  case "002":
311  case "003":
312  // ** Continue processing the other related accounts
313  break;
314  default:
315  // ** ERROR -- Unkonwn stat code
316  throw new Exception ("Unknown Packet Status Code", intval(HCU_array_key_value('pktStatCode', $packetStatus)));
317  }
318  }
319  $retVal = Array('code' => '000', 'data' => array('requestDate' => $lastPktDate, 'requestDesc' => 'Success'));
320  } catch (Exception $e) {
321  _ApplPrintLogger($pHbEnv, $cuConnSet, "\n Load User Data -- Catch Error :: ({$e->getCode()}) {$e->getMessage()}");
322  // ** Not sure how to handle here
323  if ($e->getCode() == 50111) {
324  // ** Too soon to ask again
325  // ** The last packet date is passed as the message in the thrown error
326  $retVal = Array('code' => '900', 'data' => array('requestDate' => $e->getMessage(), 'requestDesc' => 'Too soon to ask again'));
327  } else {
328  // ** Unknown Error
329  $retVal = Array('code' => '999', 'data' => array('requestDate' => 1, 'requestDesc' => 'Unable to retrieve data'));
330  }
331  }
332 
333  // ** Handle a new situation
334  // ** noUpdatesNeeded is true and NO ERROR occurred
335  if ($noUpdatesNeeded && $retVal['code'] == '000') {
336  // ** Return TOO SOON -- Use last set $lastPktDate .. We don't have a good method of displaying this information when we have more than 1 account associated
337  $retVal = Array('code' => '900', 'data' => array('requestDate' => $lastPktDate, 'requestDesc' => 'Too soon to ask again'));
338  }
339 
340  return $retVal;
341 }
342 
343 
344 /**
345  *
346  * Set the status time back in time so the next call to LoadUserData will get new data for the user.
347  *
348  * This is often times called after a transfer
349  *
350  * @param array $pHbEnv -- Current HB_ENV setting
351  * @param integer $pUserId -- User ID for data that is being requested
352  * @param array $pUserData -- Array of data for the user
353  * [memberaccounts] -- numeric array of MemberAccounts that need their packet stamp updated
354 
355  * @return array
356  * [code]
357  * [error]
358  * [data]
359 */
360 function SetbackMemberStamps($pHbEnv, $pUserId, $pUserData) {
361  $retVal = Array("code" => "000", "data" => Array());
362 
363  try {
364  /*
365  * Get CU Connection Settings
366  */
367  $cuConnResp = _ReturnCUSettings($pHbEnv);
368  if ($cuConnResp['code'] != '000') {
369  throw new Exception ("Unable to Load CU Settings");
370  }
371  $cuConnSet = HCU_array_key_value('data', $cuConnResp);
372 
373  _ApplPrintLogger($pHbEnv, $cuConnSet, "\n* SetbackMemberStamps *\n" . print_r($pUserData, true));
374 
375  /*
376  * Get Member Account List
377  */
378 
379  if (count(HCU_array_key_exists("accountnumbers", $pUserData)) == 0) {
380  throw new Exception ("No Member Accounts Listed");
381  }
382  if (!is_array(HCU_array_key_value("accountnumbers", $pUserData))) {
383  throw new Exception ("No Member Accounts Listed");
384  }
385  /**
386  * SET NEW STAMP VALUES
387  */
388  $waitBuffer = intval($pHbEnv['livewait']);
389 
390  // new stamp to set for the accounts
391  $newPktStamp = time() - (2 * $waitBuffer);
392  $stampThreshold = time() - (3 * $waitBuffer);
393 
394  $pktData = Array(
395  "accountnumber" => '',
396  "balance_attempt" => $newPktStamp,
397  "balance_stamp" => $newPktStamp,
398  "history_attempt" => $newPktStamp,
399  "history_stamp" => $newPktStamp
400  );
401  /*
402  * Loop through Member List
403  */
404  foreach (HCU_array_key_value("accountnumbers", $pUserData) as $idx => $reqMemberAcctNbr) {
405 
406  /**
407  * First -- Get the current packet stamp information
408  */
409 
410  $lastAcctStatus = _FetchLastPacketStatus($pHbEnv, $reqMemberAcctNbr);
411  if ($lastAcctStatus['code'] == '999') {
412  throw new Exception ("Unable to read account status", 50115);
413  }
414  $pktStamps = HCU_array_key_value('data', $lastAcctStatus);
415  _ApplPrintLogger($pHbEnv, $cuConnSet, "\nPacketStamps\n" . print_r($pktStamps, true));
416 
417  // ** Need Packet Stamp
418  $lastPktStamp = HCU_array_key_value('balance_stamp', $pktStamps);
419 
420  if ($lastPktStamp > $stampThreshold) {
421  /**
422  * Within range update stamp
423  * Update packet value with a lower value packet status to force a refresh
424  */
425 
426  $pktData['accountnumber'] = $reqMemberAcctNbr;
427 
428  /**
429  * ** SET PACKET STATUS TO PREVENT OVER QUERY
430  */
431  $respStatus = _SetPacketStatus($pHbEnv, $pktData);
432  if ($respStatus['code'] != '000') {
433  throw new Exception('Error updating Accounts: ' . HCU_JsonEncode($respStatus['error']), 50120);
434  }
435  }
436 
437  } // foreach
438 
439  } catch (exception $e) {
440  $retVal['code'] = '999';
441  $retVal['error'] = $e->getMessage();
442  }
443 
444  return $retVal;
445 }
446 
447 
448 /**
449  * Returns the packet information for an account
450  * ** HomeCU API -- For legacy this will apply to all accounts related to the accountnumber
451  * ** They should ALL have the same value and the update should set all at once
452  * ** HomeCU API Plus -- We are moving towards a granular approach where each accounts history
453  * ** will be updated separately.
454  * ** Balances will be all at once.
455  * ** History will be done on a request basis
456  * @param array $pHbEnv -- The environment array for Home Banking requests
457  * @param array $pMbrData -- Member request data
458  * used fields
459  * type - The type of packet request {One of "REQUEST_PACKET" Constants}
460  * member - primary member number of data to be retrieved
461  * acct-key - This is the account key that makes up the key parts of the account balance record.
462  * This is comprised of D|<acctnumber>|<accttype>|<certnumber>
463  * OR L|<acctnumber>|<loannumber>
464  *
465  *
466  */
467 function _ReturnAccountPacketData($pHbEnv, $pMbrData) {
468  $retVal = Array("code" => '000', "data" => Array());
469 
470  try {
471 
472  // ** Explode the acct-key to get the Account Number | Account Type | Cert Number
473  $acctData = explode("|", HCU_array_key_value('acct-key', $pMbrData));
474 
475  /* ** VALIDATION ** */
476  if ($acctData[0] == '') {
477  throw new Exception ("Unknown Account Number");
478  }
479 
480  $lastStatus = _FetchLastPacketStatus($pHbEnv, $acctData[0]);
481 
482  if ($lastStatus['code'] == '000') {
483  // ** Good data from the core
484  }
485 
486  } catch (Exception $e) {
487  $retVal['code'] = '999';
488  $retVal['error'] = "Unable to get last account status";
489  }
490 
491  return $retVal;
492 }
493 
494 
495 /**
496  * Get the packet stamp / packet attempt from the balance table for the requested account
497  * For HomeCU API Plus -- this will be granular
498  * For HOMECU API -- this will be the min of all the different columns.
499  *
500  * @param array $pHbEnv -- The Current HB ENV Environment array
501  * @param string $pMbrAcct -- The Member Account Number
502  *
503  * @return array
504  * balance_attempt
505  * balance_stamp
506  * history_attempt
507  * history_stamp
508  *
509  */
510 function _FetchLastPacketStatus($pHbEnv, $pMbrAcct) {
511 
512  $retVal = Array("code" => "000", "data" => Array());
513  try {
514  $cuCode = HCU_array_key_value('Cu', $pHbEnv);
515  $dbConn = HCU_array_key_value('dbh', $pHbEnv);
516 
517  if ($cuCode == '') {
518  // CU Code appears to be empty
519  throw new Exception("CU Not Set");
520  }
521 
522  // ** Explode the acct-key to get the Account Number | Account Type | Cert Number
523  /* ** VALIDATION ** */
524  if ($pMbrAcct == '') {
525  throw new Exception ("Unknown Account Number");
526  }
527 
528  if (db_connection_status($dbConn) !== PGSQL_CONNECTION_OK) {
529  // Database connection failed
530  throw new Exception("Problem connecting to database");
531  }
532 
533  /**
534  * Create the query to get the minimum values attempt/stamps from the core
535  * ALL records should be the same. Getting the minimum should be the best method.
536  * 4/7/17 -- It may be needed that later we add a coalesce value or ignore records where a value is null
537  * 5/12/17 -- Query ONLY the balance_attempt, balance_stamp fields from the <client>memberacct table.
538  * This record MUST exist for any query from the core
539  */
540 
541  // ** FAKING THE return of the history information -- standard gets the history at the same time.
542  $sql = "SELECT balance_attempt, balance_stamp, balance_attempt as history_attempt, balance_stamp as history_stamp
543  FROM " . prep_save($cuCode, 10) . "memberacct
544  WHERE accountnumber = '" . prep_save($pMbrAcct, 12) . "' ";
545 
546 /***
547  * PREVIOUS QUERY - MAY NEED TO BE USED FOR Plus
548  $sql = "SELECT min(balance_attempt) as balance_attempt, min(balance_stamp) as balance_stamp,
549  min(history_attempt) as history_attempt, min(history_stamp) as history_stamp
550  FROM (
551  SELECT balance_attempt, balance_stamp, history_attempt, history_stamp
552  FROM " . prep_save($cuCode, 10) . "accountbalance
553  WHERE accountnumber = '" . prep_save($pMbrAcct, 12) . "'
554  UNION ALL
555  SELECT balance_attempt, balance_stamp, history_attempt, history_stamp
556  FROM " . prep_save($cuCode, 10) . "loanbalance
557  WHERE accountnumber = '" . prep_save($pMbrAcct, 12) . "'
558  ) as accountlist
559  ";
560 * */
561  $sqlRs = db_query($sql, $dbConn);
562  if ($sqlRs) {
563  $retVal['data'] = db_fetch_assoc($sqlRs);
564  } else {
565  throw new Exception("Unable to retrieve records");
566  }
567 
568  } catch (Exception $e) {
569  // ** SOME ERROR OCCURRED
570  $retVal['code'] = '999';
571  $retVal['error'] = "Data retrieval failed";
572  $retVal['data'] = Array();
573  }
574 
575 
576  return $retVal;
577 
578 }
579 
580 /**
581  * This will update the packet status fields (balance_stamp, history_stamp, balance_attempt, history_attempt)
582  *
583  * @param array $pHbEnv -- The current HB Environment array:
584  * dbh - Database handle
585  * Cu - Current Credit Union Code
586  *
587  * @param array $pPktData -- The Packet data to be saved
588  * accountnumber - (required) The account number for the data being saved
589  * at least one of the following is required
590  * balance_stamp
591  * balance_attempt
592  * history_stamp
593  * history_attempt
594  *
595  * @return array
596  * "code" - {000, 999}
597  * "error" -- Error description -- only present when code == 999
598  * ** NO data returned on success
599  *
600  */
601 function _SetPacketStatus ($pHbEnv, $pPktData) {
602  $retVal = Array("code" => "000", 'data' => Array());
603 
604  try {
605  $dbConn = HCU_array_key_value('dbh', $pHbEnv);
606  $cuCode = HCU_array_key_value('Cu', $pHbEnv);
607  $acctNbr = HCU_array_key_value('accountnumber', $pPktData);
608 
609  /**
610  * Validate the columns that can be included and saved in the record
611  */
612  /*
613  * Database handle
614  */
615  if (db_connection_status($dbConn) !== PGSQL_CONNECTION_OK) {
616  // Database connection failed
617  throw new Exception("Problem connecting to database", 750);
618  }
619 
620  /*
621  * Credit Union Code
622  */
623  if ($cuCode == '') {
624  // CU Code appears to be empty
625  throw new Exception("CU Code Not Set", 751);
626  }
627 
628  /*
629  * Account Number
630  */
631  if ($acctNbr == '') {
632  // ** Account Number NOT set
633  throw new Exception ("Account Number not set", 752);
634  }
635 
636  $sql = "";
637 
638  $tableList = Array(
639  "memberacct" => Array(
640  'balance_attempt',
641  'balance_stamp'
642  ),
643  "accountbalance" => Array(
644  'balance_stamp',
645  'history_attempt',
646  'history_stamp'
647  ),
648  "loanbalance" => Array(
649  'balance_stamp',
650  'history_attempt',
651  'history_stamp'
652  )
653  );
654  // ** Update ONLY the memberacct table for STANDARD --
655  // ** accountbalance table is updated in _UpdateInsertAccountBalance
656  $tableList = Array(
657  "memberacct" => Array(
658  'balance_attempt',
659  'balance_stamp'
660  )
661  );
662 
663  // Good to go --- Update the tables
664  // ** STANDARD API **
665  // * Update the tables in the tableList array
666  foreach ($tableList as $tableName => $fieldList) {
667  $fieldCnt = 0;
668 
669  $fieldUpdList = array_intersect_key($pPktData, array_flip($fieldList));
670  if (count($fieldUpdList) > 0) {
671  // ** Declare Update
672  $sql .= "UPDATE " . prep_save($cuCode, 10) . $tableName . " SET ";
673  // ** Update Fields
674  foreach ($fieldUpdList as $fieldName => $fieldVal) {
675  $sqlSep = ($fieldCnt == 0 ? '' : ', ');
676  $sql .= $sqlSep . $fieldName . " = " . intval(HCU_array_key_value($fieldName, $pPktData));
677  $fieldCnt++;
678  }
679  // ** Where
680  $sql .= " WHERE accountnumber = '" . prep_save($acctNbr, 12) . "';\n";
681  }
682  }
683 
684  $sqlRs = db_query($sql, $dbConn);
685  if ($sqlRs) {
686  $retVal['data'] = db_fetch_all($sqlRs);
687  } else {
688  throw new Exception("Unable to retrieve records");
689  }
690 
691  } catch (Exception $e) {
692  switch ($e->getCode()) {
693  case 750: // Database Handle Not Set
694  case 751: // CU Code Not Set
695  $retVal['error'] = $e->getMessage();
696  break;
697  default:
698  // ** SOME ERROR OCCURRED
699  $retVal['error'] = "Unknown error while updating packet status. " . $e->getMessage();
700 
701  }
702  // ** SOME ERROR OCCURRED
703  $retVal['code'] = '999';
704  $retVal['data'] = Array();
705 
706  }
707 
708  return $retVal;
709 }
710 
711 /**
712  * This function will save the data from the core to their respective tables
713  *
714  * @param array $pHbEnv -- The current HB_ENV environment array
715  * @param array $pConnSet -- The CU Connection Settings
716  * @param array $pPktStamps -- The array containing the values for the last packet stamps
717  * @param array $pMbrData -- Array of data for retrieving a packet
718  * type - The type of packet request one of (PACKET_REQUEST) constants
719  * member - primary member number of data to be retrieved (API STANDARD (ONLY?))
720  * acct-key - This is the account key that makes up the key parts of the account balance record.
721  * This is comprised of D|<acctnumber>|<accttype>|<certnumber>
722  * OR L|<acctnumber>|<loannumber>
723  * cutoff - The data cutoff date
724  * @param string $pDataPacket -- the data packet to parse
725  *
726  * @return array This function will be a pass / fail
727  * [code] {000, 999}
728  */
729 function _SaveMemberData($pHbEnv, $pConnSet, $pPktStamps, $pMbrData, $pDataPacket) {
730  $retVal = array("code" => "000", "data" => array());
731 
732  _ApplPrintLogger($pHbEnv, $pConnSet, "\nSaveMemberData\n" . print_r($pPktStamps, true));
733 
734  try {
735 
736  $dbConn = HCU_array_key_value('dbh', $pHbEnv);
737 
738  /* UPDATE PACKET STAMP */
739  // Save the negative timestamp of right now
740  $newPktStamp = time();
741 
742  $pktData = Array(
743  "accountnumber" => HCU_array_key_value('member', $pMbrData),
744  "balance_stamp" => ($newPktStamp * -1)
745  );
746 
747  $respStatus = _SetPacketStatus($pHbEnv, $pktData);
748  if ($respStatus['code'] != '000') {
749  throw new Exception ("Unable to update the packet stamp prior to update.");
750  }
751 
752  $pPktStamps['balance_stamp'] = $newPktStamp;
753  $pPktStamps['history_stamp'] = $newPktStamp;
754 
755  // Validate pDataPacket is valid xml
756  if (!is_a($pDataPacket, "SimpleXMLElement")) {
757  // *NOT VALID XML OBJECT
758  throw new Exception ("Data Packet Error");
759  }
760 
761 
762  /* START TRANSACTION */
763  if (db_transaction_status($dbConn) === PGSQL_TRANSACTION_ACTIVE || db_transaction_status($dbConn) === PGSQL_TRANSACTION_INTRANS) {
764  $skipWork = true;
765  } else {
766  $startRs = db_work ($dbConn, HOMECU_WORK_BEGIN);
767  $skipWork = false;
768  }
769  // Get Member From pMbrData
770 
771  // ** Traverse to the Inquiry Node
772  $pktInquiryNode = $pDataPacket->Inquiry;
773  $updTableList = Array("accountbalance", "accounthistory", "loanbalance", "loanhistory");
774 
775  /**
776  * ** CHECK PROCESS HOLDS FLAG **
777  */
778  if ($pHbEnv['Fset2'] & GetFlagsetValue('CU2_SHOWHOLD')) {
779  array_push($updTableList, "holds");
780  }
781  _ApplPrintLogger($pHbEnv, $pConnSet, " ** Table List -- " . print_r($updTableList, true));
782 
783  /*
784  * cutoff adjustment
785  *
786  * The Parameters section may contain an altered cutoff date
787  *
788  * The format will be
789  * Member\t[newcutoff]
790  */
791  if (isset($pktInquiryNode->Parameters)) {
792  $newParameters = $pktInquiryNode->Parameters;
793  list($paramMbr, $paramCutoff) = explode("\t", $newParameters);
794  $paramCutoff = trim($paramCutoff);
795 
796  if ($paramCutoff != '' && strlen($paramCutoff) == 8) {
797  // new cutoff is NOT blank
798  // new cutoff is 8 characters YYYYMMDD
799  _ApplPrintLogger($pHbEnv, $pConnSet, "Cutoff Changed -- From {$pMbrData['cutoff']} to $paramCutoff");
800  $pMbrData['cutoff'] = $paramCutoff;
801  } else {
802  _ApplPrintLogger($pHbEnv, $pConnSet, "Cutoff NOT Changed -- {$pMbrData['cutoff']}");
803  }
804  } else {
805  // ** Parameters should always be a part of the packet, strange it is not there.
806  _ApplPrintLogger($pHbEnv, $pConnSet, "Parameters NOT SET");
807  }
808 
809 
810 
811  /* ** TABLE LOOP ** */
812  foreach ($updTableList as $updTableName) {
813  _ApplPrintLogger($pHbEnv, $pConnSet, "\nUpdate -- $updTableName\n");
814 
815  // ** Use the import-type (rename) as a variable function name to call .. Possibly the history would also be a function.. use same number of parameters
816  $updTableDef = _GetPacketColumnList($pHbEnv, $updTableName);
817  // ** Search for the expected <node> in the packet
818  $tblPktData = $pktInquiryNode->{HCU_array_key_value('node', $updTableDef)};
819 
820  $tblUpdFunction = HCU_array_key_value('import-function', $updTableDef);
821  if (!function_exists($tblUpdFunction)) {
822  throw new Exception ("Unknown Import Function");
823  }
824  $updTableResp = $tblUpdFunction($pHbEnv, $pConnSet, $pPktStamps, HCU_array_key_value('tablename', $updTableDef), $pMbrData, $tblPktData);
825 
826  if ($updTableResp['code'] != '000') {
827  // ** Data Update Error -- Throw away and rollback
828  throw new Exception ("Table Update Failed -- $updTableName");
829  }
830 
831  if (HCU_array_key_exists('insert', $updTableResp['data'])) {
832  // ** Inserted values from the lower level function
833  // ** All Users that access these records need to be updated.
834  // * by including the update here it will be inside a postgres transaction
835 
836  /** Update User Account Records **/
837  $updUserAcct = _UpdateUserAccounts($pHbEnv, $pConnSet, trim(HCU_array_key_value('member', $pMbrData)), $updTableResp['data']['insert']);
838  if ($updUserAcct['code'] != '000') {
839  // ** Data Update error -- Force to start over
840  throw new Exception ("Table Update Failed -- UpdateUserAccounts");
841  }
842 
843  }
844 
845 
846  }
847 
848  // ** Final update to update the balance_stamp on <client>memberacct
849 
850  $pktData["balance_stamp"] = $newPktStamp;
851  /**
852  * ** FINAL PACKET STAMP
853  */
854  $respStatus = _SetPacketStatus($pHbEnv, $pktData);
855 
856  /* UPDATE TRANSACTION */
857  if (!$skipWork) {
858  /* UPDATE TRANSACTION */
859  $commitRs = db_work ($dbConn, HOMECU_WORK_COMMIT);
860  if (!$commitRs) {
861  // ** FAILED COMMIT
862  throw new Exception ("Failed to Commit Work");
863  }
864  }
865 
866  /* FINISHED */
867 
868  } catch (Exception $e) {
869 
870  _ApplPrintLogger($pHbEnv, $pConnSet, "\nSave Member Failed\n{$e->getMessage()}, " . db_last_error() . ", " . print_r($pMbrData, true) . "");
871  /* ROLLBACK TRANSACTION */
872 
873  if (!$skipWork) {
874  $commitRs = db_work ($dbConn, HOMECU_WORK_ROLLBACK);
875  }
876 
877  $retVal['code'] = '999';
878  $retVal['error'] = "Error occurred while updating tables";
879  }
880 
881  return $retVal;
882 }
883 
884 /**
885  * This function will save the Cross-Account Authority (XA) data from the core to their respective tables
886  *
887  * @param array $pHbEnv -- The current HB_ENV environment array
888  * @param array $pConnSet -- The CU Connection Settings
889  * @param array $pMbrData -- Array of data for retrieving a packet
890  * type - The type of packet request one of (PACKET_REQUEST) constants
891  * member - primary member number of data to be retrieved (API STANDARD (ONLY?))
892  * acct-key - This is the account key that makes up the key parts of the account balance record.
893  * This is comprised of D|<acctnumber>|<accttype>|<certnumber>
894  * OR L|<acctnumber>|<loannumber>
895  * cutoff - The data cutoff date
896  * @param string $pDataPacket -- the data packet to parse
897  *
898  * @return array This function will be a pass / fail
899  * [code] {000, 999}
900  */
901 function _SaveMemberXAData($pHbEnv, $pConnSet, $pMbrData, $pDataPacket) {
902  $retVal = array("code" => "000", "data" => array());
903 
904  _ApplPrintLogger($pHbEnv, $pConnSet, "\nSaveMemberXAData\n" . print_r($pMbrData, true));
905 
906  try {
907  $dbConn = HCU_array_key_value('dbh', $pHbEnv);
908 
909  $newPktStamp = time();
910 
911  // Validate pDataPacket is valid xml
912  if (!is_a($pDataPacket, "SimpleXMLElement")) {
913  // *NOT VALID XML OBJECT
914  throw new Exception ("Data Packet Error");
915  }
916 
917  /* START TRANSACTION */
918  if (db_transaction_status($dbConn) === PGSQL_TRANSACTION_ACTIVE || db_transaction_status($dbConn) === PGSQL_TRANSACTION_INTRANS) {
919  $skipWork = true;
920  } else {
921  $startRs = db_work ($dbConn, HOMECU_WORK_BEGIN);
922  $skipWork = false;
923  }
924 
925  // Get Member From pMbrData
926 
927  // ** Traverse to the Inquiry Node
928  $pktInquiryNode = $pDataPacket->CrossAccount;
929 
930  $updTableName = "crossaccounts";
931  _ApplPrintLogger($pHbEnv, $pConnSet, "\nCross Account Update -- $updTableName\n");
932 
933  // ** Use the import-type (rename) as a variable function name to call .. Possibly the history would also be a function.. use same number of parameters
934  $updTableDef = _GetPacketColumnList($pHbEnv, $updTableName);
935 
936  // ** Search for the expected <node> in the packet
937  $tblPktData = $pktInquiryNode->{HCU_array_key_value('node', $updTableDef)};
938 
939  $tblUpdFunction = HCU_array_key_value('import-function', $updTableDef);
940 
941  if (!function_exists($tblUpdFunction)) {
942  throw new Exception ("Unknown Import Function");
943  }
944  _ApplPrintLogger($pHbEnv, $pConnSet, "\n CALL FUNCTION $tblUpdFunction \n");
945 
946  $updTableResp = $tblUpdFunction($pHbEnv, $pConnSet, HCU_array_key_value('tablename', $updTableDef), $pMbrData, $tblPktData);
947 
948  if ($updTableResp['code'] != '000') {
949  // ** Data Update Error -- Throw away and rollback
950  throw new Exception ("Table Update Failed -- $updTableName");
951  }
952 
953  if (HCU_array_key_exists('insert', $updTableResp['data'])) {
954  // ** Inserted values from the lower level function
955  // ** All Users that access these records need to be updated.
956  // * by including the update here it will be inside a postgres transaction
957 
958  /** Update User Account Records **/
959  $updUserAcct = _UpdateUserAccounts($pHbEnv, $pConnSet, trim(HCU_array_key_value('member', $pMbrData)), $updTableResp['data']['insert']);
960  if ($updUserAcct['code'] != '000') {
961 
962  // ** Data Update error -- Force to start over
963  throw new Exception ("Table Update Failed -- UpdateUserAccounts");
964  } else {
965  // ** SUCCESS -- RECORD IF A MEMBER ACCT WAS ADDED -- DETERMINED BY 'data' being > 0
966  $retVal['data']['memberacct-added'] = count($updUserAcct['data']);
967  }
968 
969  }
970 
971 
972  /* UPDATE TRANSACTION */
973  if (!$skipWork) {
974  /* UPDATE TRANSACTION */
975  $commitRs = db_work ($dbConn, HOMECU_WORK_COMMIT);
976  if (!$commitRs) {
977  // ** FAILED COMMIT
978  throw new Exception ("Failed to Commit Work");
979  }
980  }
981 
982  /* FINISHED */
983 
984  } catch (Exception $e) {
985  _ApplPrintLogger($pHbEnv, $pConnSet, "\nSave Cross Account Failed\n{$e->getMessage()}, " . db_last_error() . ", " . print_r($pMbrData, true) . "");
986  /* ROLLBACK TRANSACTION */
987  if (!$skipWork) {
988  $commitRs = db_work ($dbConn, HOMECU_WORK_ROLLBACK);
989  }
990 
991  $retVal['code'] = '999';
992  $retVal['error'] = "Error occurred while updating tables";
993  }
994  return $retVal;
995 }
996 
997 /**
998  * This function will create a structure that defines what the columns / data
999  * to expect in the data packet
1000  *
1001  * @param array $pTableType -- The Current table we are updating
1002  *
1003  * @return array
1004  * []
1005  */
1006 function _GetPacketColumnList ($pHbEnv, $pTableType) {
1007 
1008  $retVal = Array();
1009 
1010  try {
1011  /**
1012  * [tablename]
1013  * [import-type] -- Which type of Import {Truncate then Copy In, Update else Insert, ???}
1014  * [node] -- Tag in the packet where the data is located
1015  * [column-list] -- Comma separated list of the columns
1016  * [allow-missing] -- {true, false} If true, and a column is missing then an empty is saved.
1017  * [columns]
1018  * [column]...
1019  *
1020  */
1021 
1022  switch (strtolower($pTableType)) {
1023  case "accountbalance":
1024  /*
1025  * ACCOUNT BALANCE TABLE
1026  */
1027  $retVal = Array(
1028  'tablename' => 'accountbalance',
1029  'import-type' => "Update else import",
1030  'import-function' => '_UpdateInsertBalance',
1031  'data-model' => "",
1032  'node' => 'AccountBalance',
1033  'allow-missing' => false,
1034  'columns' => Array(
1035  'accountnumber',
1036  'accounttype',
1037  'certnumber',
1038  'deposittype',
1039  'description',
1040  'amount',
1041  'ytdinterest',
1042  'lastyrinterest',
1043  'available',
1044  'micraccount'
1045  )
1046  );
1047  break;
1048  case "loanbalance":
1049  $retVal = Array(
1050  'tablename' => 'loanbalance',
1051  'import-type' => "Update else import",
1052  'import-function' => '_UpdateInsertBalance',
1053  'node' => 'LoanBalance',
1054  'allow-missing' => false,
1055  'columns' => Array(
1056  'accountnumber',
1057  'loannumber',
1058  'currentbalance',
1059  'payoff',
1060  'paymentamount',
1061  'nextduedate',
1062  'description',
1063  'interestrate',
1064  'creditlimit',
1065  'ytdinterest',
1066  'lastyrinterest',
1067  'misc1',
1068  'cbtype',
1069  'lastpaymentdate'
1070  )
1071 /**
1072  * Possible Extended Table Values
1073  'term',
1074  'creditdisability',
1075  'creditlife',
1076  'ytdinterest',
1077  'lastyrinterest'
1078  )
1079 */
1080  );
1081  break;
1082  case "accounthistory":
1083  /*
1084  * ACCOUNT HISTORY TABLE
1085  */
1086  $retVal = Array(
1087  'tablename' => 'accounthistory',
1088  'import-type' => "Delete from cutoff then copy in",
1089  'import-function' => '_UpdateCopyInHistory',
1090  'data-model' => "",
1091  'node' => 'AccountHistory',
1092  'allow-missing' => false,
1093  'columns' => Array(
1094  'accountnumber',
1095  'accounttype',
1096  'certnumber',
1097  'tracenumber',
1098  'checknumber',
1099  'date',
1100  'amount',
1101  'description',
1102  'balance',
1103  'sortkey'
1104  )
1105  );
1106  break;
1107  case "loanhistory":
1108  /*
1109  * LOAN HISTORY TABLE
1110  */
1111  $retVal = Array(
1112  'tablename' => 'loanhistory',
1113  'import-type' => "Delete from cutoff then copy in",
1114  'import-function' => '_UpdateCopyInHistory',
1115  'data-model' => "",
1116  'node' => 'LoanHistory',
1117  'allow-missing' => false,
1118  'columns' => Array(
1119  'accountnumber',
1120  'loannumber',
1121  'tracenumber',
1122  'date',
1123  'principleamount',
1124  'interestamount',
1125  'description',
1126  'balance',
1127  'sortkey'
1128  )
1129 
1130  );
1131  // ** Append columns for loan history expanded packet - based on constant 'CU2_ESCHEMA'
1132 
1133  /**
1134  * Expanded Loan History Packet
1135  * Core will send the following columns for each record
1136  * fee
1137  * escrow
1138  */
1139  if (HCU_array_key_value('flagset2', $pHbEnv) & GetFlagsetValue('CU2_ESCHEMA')) {
1140  array_push($retVal['columns'], "fee", "escrow");
1141  }
1142  break;
1143  case "crossaccounts":
1144  /*
1145  * CROSS ACCOUNT RECORDS
1146  */
1147  $retVal = Array(
1148  'tablename' => 'crossaccounts',
1149  'import-style' => 'Update misc1 if exists, Insert if NOT, delete unused',
1150  'import-function' => '_DeleteInsertCrossAcct',
1151  'data-model' => '',
1152  'node' => 'TxAccount',
1153  'allow-missing' => true,
1154  'columns' => Array(
1155  'accountnumber',
1156  'tomember',
1157  'accounttype',
1158  'deposittype',
1159  'description',
1160  'misc1'
1161  )
1162  );
1163  break;
1164  case "holds":
1165  /*
1166  * ACCOUNT HOLDS
1167  */
1168  $retVal = Array(
1169  'tablename' => 'holds',
1170  'import-style' => 'Truncate Table, Insert all records',
1171  'import-function' => '_UpdateCopyIn',
1172  'data-model' => '',
1173  'node' => 'Holds',
1174  'allow-missing' => false,
1175  'columns' => Array(
1176  'accountnumber',
1177  'accounttype',
1178  'certnumber',
1179  'holdtype',
1180  'tracenumber',
1181  'postdate',
1182  'expiredate',
1183  'amount',
1184  'description'
1185  )
1186  );
1187 
1188  break;
1189 
1190  }
1191 
1192  // ** Create a comma-delimited list for the columns in key [column-list]
1193  if (HCU_array_key_exists('columns', $retVal)) {
1194  $retVal['column-list'] = implode (', ', $retVal['columns']);
1195  }
1196  /**
1197  *
1198  * GetFlagsetValue
1199  Append expanded packet fields to the loan history ..
1200  **/
1201  } catch (Exception $e) {
1202  // ** reset the array
1203  $retVal = Array();
1204  }
1205 
1206  return $retVal;
1207 }
1208 
1209 /**
1210  * Update / Insert Balance
1211  */
1212 
1213 /**
1214  * This function will evaluate the data being imported and either update
1215  * the balance record or insert a new record. This function will ONLY apply
1216  * to the balance tables (accountbalance, loanbalance)
1217  *
1218  * @param handle $pHbEnv -- This is the current database connection
1219  * @param array $pConnSet -- The CU Connection Settings
1220  * @param array $pPktStamps -- An array of packet stamps
1221  * @param string $pTableName -- Which table to update {accountbalance, loanbalance}
1222  * @param array $pMbrData -- Array of member information
1223  * @param string $pData -- This is expected to be the string from the api packet
1224  * -- between the specified tags
1225  *
1226  * @return array
1227  * [code] = {000, 999}
1228  */
1229 function _UpdateInsertBalance ($pHbEnv, $pConnSet, $pPktStamps, $pTableName, $pMbrData, $pData) {
1230  $retVal = array("code" => "000", "data" => array());
1231 
1232  $insertedRows = Array();
1233  try {
1234  $dbConn = HCU_array_key_value('dbh', $pHbEnv);
1235  $lMc = HCU_array_key_value('MC', $pHbEnv);
1236  // Loan or Account?
1237  $tbl2Update = _GetPacketColumnList($pHbEnv, $pTableName);
1238  $tblColumns = HCU_array_key_value("columns", $tbl2Update);
1239 /*
1240  $tblSchemaList = GetTableDefinition($pHbEnv, $pTableName);
1241  if (!HCU_array_key_exists($pUpdTableCode, $tblSchemaList)) {
1242  // * NOT FOUND
1243  throw new Exception("Table Schema Not Found", '901');
1244  }
1245  $tblSchema = $tblSchemaList[$pTableName];
1246 */
1247  // Need packet Stamp
1248 
1249  // Loop through the data lines
1250  $dataRow = strtok($pData, "\n");
1251  while ($dataRow !== false) {
1252 
1253  //** Validate we have a line
1254  if ($dataRow != '') {
1255  // Parse each line -- Combine the 'columns' list with data line exploded on \t
1256  $recordValues = explode("\t", $dataRow);
1257  /* Combine column / values */
1258  /*
1259  * First see if the table allows missing columns
1260  * If it does, then add the missing columns with an empty value
1261  * This will only be adjust cases where the expected count is greater than the actual count
1262  */
1263  if (count($tblColumns) > count($recordValues) && $tbl2Update["allow-missing"]) {
1264  /* Column Count mismatch - Actual count is LESS than Expected count */
1265  for ($colIdx = count($recordValues); $colIdx < count($tblColumns); $colIdx++) {
1266  // * For each missing column add an empty value to the Actual array
1267  $recordValues[] = "";
1268  }
1269  }
1270  // ** Validate the number of columns match before doing this.. Throw an error if they are mismatched
1271  if (count($tblColumns) != count($recordValues)) {
1272  throw new Exception ("Column Count Mismatch");
1273  }
1274  $updateRow = array_combine($tblColumns, $recordValues);
1275 
1276  $updateRow['_action'] = "update";
1277 
1278  /** ADD PACKET STAMP DATA **/
1279  $updateRow['balance_stamp'] = HCU_array_key_value('balance_stamp', $pPktStamps);
1280  $updateRow['history_stamp'] = HCU_array_key_value('history_stamp', $pPktStamps);
1281 
1282  /** Calculate may_deposit / may_withdraw **/
1283  $mayResp = _DetermineMayValues($pHbEnv, $pTableName, $updateRow);
1284 
1285  $updateRow = array_merge($updateRow, $mayResp);
1286  $updResult = SetDataTableUpdate($dbConn, $pHbEnv, $lMc, $pTableName, $updateRow, false);
1287 
1288  // * verify if the record was updated
1289  if ($updResult['code'] == '000') {
1290  // * verify it was updated
1291  if (HCU_array_key_value($pTableName, $updResult['data']['row']) === false) {
1292  // -- If not then INSERT
1293  $updateRow['_action'] = "insert";
1294  $updResult = SetDataTableUpdate($dbConn, $pHbEnv, $lMc, $pTableName, $updateRow, false);
1295 
1296  // ** Add to inserted list
1297  $insertedRows[$pTableName][] = $updateRow;
1298  _ApplPrintLogger($pHbEnv, $pConnSet, "\nInsert Balance Record\n" . print_r($updateRow, true));
1299  }
1300  }
1301 
1302  }
1303  $dataRow = strtok("\n");
1304  }
1305 
1306  if (count($insertedRows) > 0) {
1307  // ** Return the inserted rows
1308  $retVal['data']['insert'] = $insertedRows;
1309  }
1310 
1311 
1312  /* *** REMOVE MISSING RECORDS FROM TABLE *** */
1313  $delSql = "DELETE
1314  FROM {$pHbEnv['cu']}{$pTableName}
1315  WHERE balance_stamp < " . HCU_array_key_value('balance_stamp', $pPktStamps) . "
1316  AND accountnumber = '" . prep_save(HCU_array_key_value('member', $pMbrData), 12) . "'; ";
1317 
1318  _ApplPrintLogger($pHbEnv, $pConnSet, "\nSAMPLE DELETE RESULTS \n" . $delSql);
1319 
1320  $delResults = db_query($delSql, $dbConn);
1321 
1322  } catch (Exception $e) {
1323  _ApplPrintLogger($pHbEnv, $pConnSet, "\nUpdate Insert Failed\n" . db_last_error());
1324 
1325  $retVal['code'] = '999';
1326  $retVal['error'] = $e->getMessage();
1327  }
1328 
1329  _ApplPrintLogger($pHbEnv, $pConnSet, "\n Return from UpdateInsertBalance " . print_r($retVal, true));
1330  return $retVal;
1331 }
1332 
1333 /**
1334  * Update the database tables using a COPY rather than update statements.
1335  * This will perform a delete based on the date field, then the data will be inserted
1336  *
1337  * @param handle $pHbEnv -- The current HB_ENV array
1338  * @param array $pConnSet -- The CU Connection Settings
1339  * @param string $pPktStamps -- Array of packet stamps for the current requests
1340  * @param string $pTableName -- Which table to update {accountbalance, loanbalance}
1341  * @param string $pMbrData -- Array of member data for this request
1342  * [member] -- the current member number for this data
1343  * [cutoff] -- the cutoff for this data
1344  * @param string $pData -- This is expected to be the string from the api packet
1345  * -- between the specified tags
1346  *
1347  * @return array
1348  * [code]
1349  * {000, 999}
1350  * [error] -- returned when code = 999
1351  */
1352 function _UpdateCopyInHistory($pHbEnv, $pConnSet, $pPktStamps, $pTableName, $pMbrData, $pData) {
1353  $retVal = array("code" => "000", "data" => array());
1354 
1355  // *TEST ONLY -- _ApplPrintLogger($pHbEnv, $pConnSet, "\n** Update Copy In History **\n $pData");
1356 
1357  try {
1358  $dbConn = HCU_array_key_value('dbh', $pHbEnv);
1359  $lMc = HCU_array_key_value('MC', $pHbEnv);
1360  $cuCode = HCU_array_key_value('Cu', $pHbEnv);
1361 
1362  $mbrAcctNbr = HCU_array_key_value('member', $pMbrData);
1363  $dataCutoff = HCU_array_key_value('cutoff', $pMbrData);
1364  // Loan or Account?
1365  $tbl2Update = _GetPacketColumnList($pHbEnv, $pTableName);
1366 
1367  $tblColList = HCU_array_key_value("column-list", $tbl2Update);
1368 
1369  // ** First Delete based on the cutoff
1370  $sql = "DELETE FROM " . prep_save($cuCode, 12) . $tbl2Update['tablename'] . "
1371  WHERE accountnumber = '" . prep_save($mbrAcctNbr, 12) . "'
1372  AND date >= '" . prep_save($dataCutoff) . "'; ";
1373 
1374  _ApplPrintLogger($pHbEnv, $pConnSet, "\nCOPY IN DELETE: $sql");
1375  $sqlRs = db_query($sql, $dbConn);
1376  if (!$sqlRs) {
1377  throw new Exception("Unable to truncate history");
1378  }
1379 
1380  // * *Trim any \n from the beginning of the data feed
1381  $pData = ltrim($pData, "\n");
1382  // ** Trim any \n from the end of the data feed
1383  $pData = rtrim($pData, "\n");
1384  // ** A trim would have had same affect, but NOT during testing I may need to go back and forth
1385 
1386 
1387  if (strlen($pData) > 0) {
1388 
1389  // ** Later create a new function db_copy_in
1390  // * this will accept the copy in sql statement and the data
1391  // * it will then loop through the function calls
1392  // ** Now for the copy in
1393  $sql = "COPY " . prep_save($cuCode, 12) . $pTableName . " ($tblColList)
1394  FROM STDIN WITH NULL as ''";
1395  $copyRs = db_query($sql, $dbConn);
1396  if (!$copyRs) {
1397  throw new Exception ("Error starting the copy");
1398  }
1399  // *TEST ONLY -- _ApplPrintLogger($pHbEnv, $pConnSet, "\n$sql");
1400 
1401 
1402  $dataRecords = explode("\n", $pData);
1403 
1404  // *TEST ONLY -- _ApplPrintLogger($pHbEnv, $pConnSet, print_r($dataRecords, true));
1405 
1406  foreach ($dataRecords as $key => $line) {
1407 
1408  // ** massage some bits in the data -- sanitize special characters
1409  $line = preg_replace("/ ?\t ?/" , "\t", $line);
1410  $line = preg_replace("/ $/", "", $line);
1411  $line = preg_replace("/\\0/", " ", $line);
1412 
1413  _ApplPrintLogger($pHbEnv, $pConnSet, "\nCopy In Line [{$line}]");
1414 
1415  pg_put_line($dbConn, $line . "\n");
1416 
1417  // ** OTHERWISE DONE
1418  }
1419  // ** Terminate the Copy with a \.
1420 
1421  $endCopyRs = pg_put_line($dbConn, "\\.\n");
1422 
1423  if (!$endCopyRs) {
1424  // ** Error was found
1425  throw new Exception ("Error with the final pg_put_line");
1426  }
1427  $resultsRs = pg_end_copy($dbConn);
1428  if (!$resultsRs) {
1429  throw new Exception ("Error Performing the Copy");
1430  }
1431  }
1432  } catch (Exception $e) {
1433 
1434  _ApplPrintLogger($pHbEnv, $pConnSet, "\n_UpdateCopyInHistory -- Catch Error :: " . $e->getMessage() . " :: " . db_last_error() . "");
1435  $retVal['code'] = '999';
1436  $retVal['error'] = $e->getMessage();
1437  }
1438  return $retVal;
1439 }
1440 
1441 /**
1442  * Update the database tables using a COPY rather than update statements.
1443  * This will perform a delete based on the accountnumber, then the data will be inserted
1444  *
1445  * @param handle $pHbEnv -- The current HB_ENV array
1446  * @param array $pConnSet -- The CU Connection Settings
1447  * @param string $pPktStamps -- Array of packet stamps for the current requests
1448  * @param string $pTableName -- Which table to update {accountbalance, loanbalance}
1449  * @param string $pMbrData -- Array of member data for this request
1450  * [member] -- the current member number for this data
1451  * [cutoff] -- the cutoff for this data
1452  * @param string $pData -- This is expected to be the string from the api packet
1453  * -- between the specified tags
1454  *
1455  * @return array
1456  * [code]
1457  * {000, 999}
1458  * [error] -- returned when code = 999
1459  */
1460 function _UpdateCopyIn($pHbEnv, $pConnSet, $pPktStamps, $pTableName, $pMbrData, $pData) {
1461  $retVal = array("code" => "000", "data" => array());
1462 
1463 _ApplPrintLogger($pHbEnv, $pConnSet, "\n** Update Copy In **\n $pData");
1464 
1465  try {
1466  $dbConn = HCU_array_key_value('dbh', $pHbEnv);
1467  $lMc = HCU_array_key_value('MC', $pHbEnv);
1468  $cuCode = HCU_array_key_value('Cu', $pHbEnv);
1469 
1470  $mbrAcctNbr = HCU_array_key_value('member', $pMbrData);
1471 
1472  // ** Get affected table
1473  $tbl2Update = _GetPacketColumnList($pHbEnv, $pTableName);
1474 
1475  $tblColList = HCU_array_key_value("column-list", $tbl2Update);
1476 
1477  // ** First Delete based on the cutoff
1478  $sql = "DELETE FROM " . prep_save($cuCode, 12) . $tbl2Update['tablename'] . "
1479  WHERE accountnumber = '" . prep_save($mbrAcctNbr, 12) . "'; ";
1480 
1481 _ApplPrintLogger($pHbEnv, $pConnSet, "\nCOPY IN DELETE $sql");
1482  $sqlRs = db_query($sql, $dbConn);
1483  if (!$sqlRs) {
1484  throw new Exception("Unable to truncate " . $tbl2Update['tablename']);
1485  }
1486 
1487  // * *Trim any \n from the beginning of the data feed
1488  $pData = ltrim($pData, "\n");
1489  // ** Trim any \n from the end of the data feed
1490  $pData = rtrim($pData, "\n");
1491  // ** A trim would have had same affect, but NOT during testing I may need to go back and forth
1492 
1493  if (strlen($pData) > 0) {
1494 
1495  // ** Later create a new function db_copy_in
1496  // * this will accept the copy in sql statement and the data
1497  // * it will then loop through the function calls
1498  // ** Now for the copy in
1499  $sql = "COPY " . prep_save($cuCode, 12) . $tbl2Update['tablename'] . " ($tblColList)
1500  FROM STDIN WITH NULL as ''";
1501  $copyRs = db_query($sql, $dbConn);
1502  if (!$copyRs) {
1503  throw new Exception ("Error starting the copy");
1504  }
1505 _ApplPrintLogger($pHbEnv, $pConnSet, "\n$sql");
1506 
1507  $dataRecords = explode("\n", $pData);
1508 
1509 _ApplPrintLogger($pHbEnv, $pConnSet, print_r($dataRecords, true));
1510 
1511  foreach ($dataRecords as $key => $line) {
1512 
1513  // ** massage some bits in the data -- sanitize special characters
1514  $line = preg_replace("/ ?\t ?/" , "\t", $line);
1515  $line = preg_replace("/ $/", "", $line);
1516  $line = preg_replace("/\\0/", " ", $line);
1517 
1518  _ApplPrintLogger($pHbEnv, $pConnSet, "\nCopy In Line [{$line}]");
1519 
1520  pg_put_line($dbConn, $line . "\n");
1521 
1522  // ** OTHERWISE DONE
1523  }
1524  // ** Terminate the Copy with a \.
1525 
1526  $endCopyRs = pg_put_line($dbConn, "\\.\n");
1527 
1528  if (!$endCopyRs) {
1529  // ** Error was found
1530  throw new Exception ("Error with the final pg_put_line");
1531  }
1532  $resultsRs = pg_end_copy($dbConn);
1533  if (!$resultsRs) {
1534  throw new Exception ("Error Performing the Copy");
1535  }
1536  }
1537  } catch (Exception $e) {
1538 
1539  _ApplPrintLogger($pHbEnv, $pConnSet, "\n_UpdateCopyIn -- Catch Error :: " . $e->getMessage() . " :: " . db_last_error() . "");
1540  $retVal['code'] = '999';
1541  $retVal['error'] = $e->getMessage();
1542  }
1543  return $retVal;
1544 }
1545 
1546 /**
1547  * This function will first delete all the cross account references for the member
1548  * It will then insert the records from the packet.
1549  *
1550  * This applies to ONLY accounts that will be considered Cross Account
1551  *
1552  *
1553  * @param handle $pHbEnv -- This is the current database connection
1554  * @param array $pConnSet -- The CU Connection Settings
1555  * @param string $pTableName -- Which table to update {accountbalance, loanbalance}
1556  * @param array $pMbrData -- Array of member information
1557  * @param string $pData -- This is expected to be the string from the api packet
1558  * -- between the specified tags
1559  *
1560  * @return array
1561  * [code] = {000, 999}
1562  */
1563 function _DeleteInsertCrossAcct ($pHbEnv, $pConnSet, $pTableName, $pMbrData, $pData) {
1564  $retVal = array("code" => "000", "data" => array());
1565 
1566  $insertedRows = Array();
1567  try {
1568  $dbConn = HCU_array_key_value('dbh', $pHbEnv);
1569  $lMc = HCU_array_key_value('MC', $pHbEnv);
1570  $cuCode = HCU_array_key_value('Cu', $pHbEnv);
1571 
1572  $mbrAcctNbr = HCU_array_key_value('member', $pMbrData);
1573 
1574 
1575  $tbl2Update = _GetPacketColumnList($pHbEnv, $pTableName);
1576  $tblColumns = HCU_array_key_value("columns", $tbl2Update);
1577 
1578  /**
1579  * ** DELETE CURRENT CROSS ACCOUNTS FOR MEMBER
1580  */
1581 
1582  $delSql = "DELETE FROM " . prep_save($cuCode, 10) . "crossaccounts
1583  WHERE accountnumber = '" . prep_save($mbrAcctNbr, 12) . "';";
1584 
1585  $delRs = db_query($delSql, $dbConn);
1586  if (!$delRs) {
1587  // * AN Error occurred while deleting - bail out
1588  throw new Exception("Unable to truncate ${cuCode}crossaccounts");
1589  }
1590 
1591  // Loop through the data lines
1592  $dataRow = strtok($pData, "\n");
1593 
1594  while ($dataRow !== false) {
1595  //** Validate we have a line
1596  if ($dataRow != '') {
1597  // Parse each line -- Combine the 'columns' list with data line exploded on \t
1598  $recordValues = explode("\t", $dataRow);
1599  /* Combine column / values */
1600  /*
1601  * First see if the table allows missing columns
1602  * If it does, then add the missing columns with an empty value
1603  * This will only be adjust cases where the expected count is greater than the actual count
1604  */
1605  if (count($tblColumns) > count($recordValues) && $tbl2Update["allow-missing"]) {
1606  /* Column Count mismatch - Actual count is LESS than Expected count */
1607  for ($colIdx = count($recordValues); $colIdx < count($tblColumns); $colIdx++) {
1608  // * For each missing column add an empty value to the Actual array
1609  $recordValues[] = "";
1610  }
1611  }
1612  // ** Validate the number of columns match before doing this.. Throw an error if they are mismatched
1613  if (count($tblColumns) != count($recordValues)) {
1614  throw new Exception ("Column Count Mismatch");
1615  }
1616  $updateRow = array_combine($tblColumns, $recordValues);
1617 
1618  // ** All records for member should have been deleted -- so we should only need to insert
1619  $updateRow['_action'] = "insert";
1620 
1621  $updResult = SetDataTableUpdate($dbConn, $pHbEnv, $lMc, $pTableName, $updateRow, false);
1622 
1623  $insertedRows[$pTableName][] = $updateRow;
1624 
1625  if ($updResult['code'] != '000') {
1626  throw new Exception ("Unable to load cross accounts.");
1627  }
1628 
1629  }
1630  $dataRow = strtok("\n");
1631  }
1632 
1633  if (count($insertedRows) > 0) {
1634  // ** Return the inserted rows
1635  $retVal['data']['insert'] = $insertedRows;
1636  }
1637 
1638 
1639  } catch (Exception $e) {
1640  _ApplPrintLogger($pHbEnv, $pConnSet, "\nUpdate Insert Failed\n" . db_last_error());
1641 
1642  $retVal['code'] = '999';
1643  $retVal['error'] = $e->getMessage();
1644  }
1645 
1646  _ApplPrintLogger($pHbEnv, $pConnSet, "\n Return from DeleteInsertCrossAcct " . print_r($retVal, true));
1647  return $retVal;
1648 }
1649 
1650 /**
1651  * This function will determine the values of may_deposit may_withdraw values for the update
1652  * clause
1653  *
1654  *
1655  * @param array $pHbEnv -- Current HB_ENV --
1656  * Ffset -- flagset value
1657  * Ffset2 -- flagset2 value
1658  * @param string $pTableName -- The table that is being updated
1659  * @param array $pUpdRow -- The row values for the data being updated
1660  *
1661  * @return array
1662  * [may_deposit, may_withdraw] - for accountbalance
1663  * [may_payment, may_addon] - for loanbalance
1664  *
1665  * This function will always return the array combo of may_deposit/may_withdraw
1666  * An error will simply force the values to be false
1667  */
1668 function _DetermineMayValues($pHbEnv, $pTableName, $pUpdateRow) {
1669 
1670  $retVal = Array();
1671 
1672  if ($pTableName == 'loanbalance') {
1673  $retVal = Array("may_payment" => false, "may_addon" => false);
1674 
1675  // * May Pay -- ALWAYS TRUE
1676  $retVal['may_payment'] = true;
1677 
1678  // * May Add On -- IF
1679  // ** NOT A CREDIT CARD (Type 18) ** 18 designated as special process
1680  if (HCU_array_key_value('Ffset2', $pUpdateRow) & GetFlagsetValue('CU2_SPEC18') == 0 || HCU_array_key_value('cbype', $pUpdateRow) != '18'
1681  // ** credit limit > 0
1682  && HCU_array_key_value('creditlimit', $pUpdateRow) > 0) {
1683  $retVal['may_addon'] = true;
1684  }
1685 
1686  } elseif ($pTableName == 'accountbalance') {
1687  $retVal = Array("may_deposit" => false, "may_withdraw" => false);
1688 
1689  /* LIST of ALLOWED DEPOSIT TYPES that can make deposits
1690  * Generally this is an upper case letter.. Lower signifying closed.
1691  * Certificates, IRA and Otherrs may need to be added later per CU */
1692  $allowedDPTypes = array('Y', 'S', 'N');
1693  if (in_array(trim(HCU_array_key_value('deposittype', $pUpdateRow)), $allowedDPTypes)) {
1694  // Passed first test -- Upper case
1695  // expected values are {Y, N, S}
1696  $retVal["may_deposit"] = true;
1697  }
1698 
1699  // ** Allowed Withdraw Types
1700  $allowedWDTypes = array('Y', 'S', 'N');
1701  if (in_array(trim(HCU_array_key_value('deposittype', $pUpdateRow)), $allowedWDTypes)) {
1702  $retVal["may_withdraw"] = true;
1703  }
1704 
1705  } elseif ($pTableName == 'crossaccounts') {
1706  $retVal['may_withdraw'] = false;
1707  $retVal['may_deposit'] = true;
1708  }
1709 
1710  return $retVal;
1711 }
1712 /**
1713  * This function will convert the tab delimited rows from the packet response for either
1714  * {accountbalance, loanbalance} into an array with the table column names as the key value
1715  *
1716  * @param array $pHbEnv -- Current HB_ENV setting
1717  * @param array $pConnSet -- Current Connection Setting
1718  * @param string $pTableName -- The tablename/nodename to convert {accountbalance, loanbalance}
1719  *
1720  * @return array
1721  * [code]
1722  * {000, 999}
1723  * [data]
1724  * [0....X]
1725  * [table column names]
1726  */
1727 function _ConvertPacketLineToArray ($pHbEnv, $pConnSet, $pTableName, $pData) {
1728  $retVal = array("code" => "000", "data" => array());
1729 
1730  $retBalances = Array();
1731  _ApplPrintLogger($pHbEnv, $pConnSet, "\n* Convert Packet Line To Array *\n");
1732  try {
1733 
1734  // Loan or Account?
1735  $tbl2Update = _GetPacketColumnList($pHbEnv, $pTableName);
1736 
1737  // * Use these column names for the array key
1738  $tblColumns = HCU_array_key_value("columns", $tbl2Update);
1739 
1740  // Loop through the data lines
1741  $dataRow = strtok($pData, "\n");
1742  while ($dataRow !== false) {
1743  //** Validate we have a line
1744  if ($dataRow != '') {
1745  // Parse each line -- Combine the 'columns' list with data line exploded on \t
1746  $recordValues = explode("\t", $dataRow);
1747 
1748  /* Combine column / values */
1749  /*
1750  * First see if the table allows missing columns
1751  * If it does, then add the missing columns with an empty value
1752  * This will only be adjust cases where the expected count is greater than the actual count
1753  */
1754  if (count($tblColumns) > count($recordValues) && $tbl2Update["allow-missing"]) {
1755  /* Column Count mismatch - Actual count is LESS than Expected count */
1756  for ($colIdx = count($recordValues); $colIdx < count($tblColumns); $colIdx++) {
1757  // * For each missing column add an empty value to the Actual array
1758  $recordValues[] = "";
1759  }
1760  }
1761  // ** Validate the number of columns match before doing this.. Throw an error if they are mismatched
1762  if (count($tblColumns) != count($recordValues)) {
1763  throw new Exception ("Column Count Mismatch");
1764  }
1765  $newRow = array_combine($tblColumns, $recordValues);
1766 
1767  /** Calculate may_deposit / may_withdraw **/
1768  $mayResp = _DetermineMayValues($pHbEnv, $pTableName, $newRow);
1769 
1770  $newRow = array_merge($newRow, $mayResp);
1771  $retBalances[] = $newRow;
1772 
1773  }
1774  $dataRow = strtok("\n");
1775  }
1776 
1777 
1778  // Search for the item in the current table
1779  // Found -- Update
1780  // NOT Found -- Insert
1781 
1782 
1783  $retVal['data'] = $retBalances;
1784 
1785  } catch (Exception $e) {
1786  _ApplPrintLogger($pHbEnv, $pConnSet, "\nCATCH ERROR Bal Upd Insert {$e->getMessage()}\n");
1787  $retVal['code'] = '999';
1788  $retVal['error'] = $e->getMessage();
1789 
1790  }
1791 
1792  return $retVal;
1793 }
1794 /**
1795  * Function to get the list of member accounts associated with a user
1796  * -- This will ONLY return a list of member accounts
1797  *
1798  * @param array $pHbEnv - Current HB_ENV settings
1799  * dbh - Database handle
1800  * Cu - Credit Union Code
1801  * @param integer $pUserId - User ID
1802  * @param boolean $pAutoAddOnly - {false (default), true} - Only return member accounts with Auto-Add configured
1803  *
1804  * @return array
1805  * [code] -- {000, 999}
1806  * [error] -- * optional
1807  * [data] -- array of account numbers -- should at least be an empty array
1808  */
1809 function _ReturnMemberAcctList($pHbEnv, $pUserId, $pAutoAddOnly=false) {
1810  $retVal = Array("code" => "000", "data" => Array());
1811 
1812  try {
1813  $cuCode = HCU_array_key_value('Cu', $pHbEnv);
1814  $dbConn = HCU_array_key_value('dbh', $pHbEnv);
1815 
1816  if ($cuCode == '') {
1817  // CU Code appears to be empty
1818  throw new Exception("CU Not Set");
1819  }
1820 
1821  if (db_connection_status($dbConn) !== PGSQL_CONNECTION_OK) {
1822  // Database connection failed
1823  throw new Exception("Problem connecting to database");
1824  }
1825 
1826  /**
1827  * Create a query to get the distint member account numbers from
1828  * the useraccounts table. These will later be used to query the
1829  * core for data
1830  *
1831  * ** To try and remove accountnumber that are the result of a cross account
1832  * ** ONLY include records that have either view_balances or view_transactions set
1833  * ** This should be benign for accounts that get limited as it's not import to update
1834  * ** the balances since they can't see the value.
1835  */
1836  if ($pAutoAddOnly) {
1837  $sql = "SELECT distinct trim(useraccounts.accountnumber) as accountnumber
1838  FROM {$cuCode}useraccounts as useraccounts
1839  JOIN {$cuCode}memberacctrights as memberacctrights on memberacctrights.accountnumber = useraccounts.accountnumber
1840  AND memberacctrights.user_id = useraccounts.user_id AND memberacctrights.whichright = 'ACCESS'
1841  WHERE useraccounts.user_id = " . intval($pUserId) . "
1842  AND memberacctrights.allowed = true
1843  AND (view_balances OR view_transactions OR int_deposit OR int_withdraw OR ext_withdraw OR ext_deposit) ";
1844  } else {
1845  $sql = "SELECT distinct trim(accountnumber) as accountnumber
1846  FROM {$cuCode}useraccounts
1847  WHERE user_id = " . intval($pUserId) . "
1848  AND (view_balances OR view_transactions OR int_deposit OR int_withdraw OR ext_withdraw OR ext_deposit) ";
1849  }
1850 
1851  // After SOCU migration on August, 2019; many members migrated
1852  // with no accuntbalance and loanbalance records in the respective
1853  // tables but had eStatement flags enabled. This presented more
1854  // than 3800 unique memebrs to be listed in Orphaned Users,
1855  // Accounts and Groups report area in Odyssey. In general, if such
1856  // members have logged in atleast once in the past, and have
1857  // eStatements flag set, if configured, we pull their loan and
1858  // account balances from the core and populate back the respective
1859  // balances related tables in Odyssey.
1860  $sql .= "UNION
1861  SELECT distinct trim(memberacctrights.accountnumber) as accountnumber
1862  FROM {$cuCode}memberacctrights as memberacctrights
1863  WHERE user_id=" . intval($pUserId) . "
1864  AND memberacctrights.whichright='ACCESS'
1865  AND memberacctrights.allowed =true ; ";
1866 
1867  $sqlRs = db_query($sql, $dbConn);
1868  if ($sqlRs) {
1869  if (db_num_rows($sqlRs) > 0) {
1870  $retVal['data'] = db_fetch_all($sqlRs);
1871  }
1872  } else {
1873  throw new Exception("Unable to retrieve records");
1874  }
1875 
1876  } catch (Exception $e) {
1877  // ** SOME ERROR OCCURRED
1878  $retVal['code'] = '999';
1879  $retVal['error'] = $e->getMessage(); // "Data retrieval failed";
1880  $retVal['data'] = Array();
1881  }
1882 
1883  return $retVal;
1884 }
1885 /**
1886  * For ALL users that have ACCESS rights for a member account, add any new sub-accounts
1887  * to their list of the <client>UserAccounts table
1888  * This could affect users other than the current logged in user.. That is because we only get one chance for this insert.
1889  * so we need to look at all users that have access
1890  *
1891  *
1892  * This function will
1893  * check the ACCESS rights before insert
1894  * check that the record does not already exist in useraccounts? <decide> Or should it just try insert and let a failure be okay
1895  *
1896  *
1897  * @param array $pHbEnv -- The current HB_ENV environment array
1898  * [Cu] -- Credit Union Code
1899  * [dbh] -- database handle
1900  * @param array $pConnSet -- The CU Connection Settings
1901  * @param integer $pUserId -- user_id of user loading data
1902  * @param string $pMbrAcct -- Account Number of member data
1903  * @param array $pInsData -- Array of data identifying the records that were inserted
1904  * This should be an array with keys of {accountbalance, loanbalance}
1905  *
1906  * @return array
1907  * [code] -- {000, 999}
1908  * [error] -- * optional
1909  * [data] -- array of account numbers -- should at least be an empty array
1910  */
1911 function _UpdateUserAccounts($pHbEnv, $pConnSet, $pMbrAcct, $pInsData) {
1912 
1913  $retVal = Array("code" => "000", "data" => Array());
1914  try {
1915  $cuCode = HCU_array_key_value('Cu', $pHbEnv);
1916  $dbConn = HCU_array_key_value('dbh', $pHbEnv);
1917 
1918  $grantRights = Array("ext_deposit" => false,
1919  "int_deposit" => false,
1920  "ext_withdraw" => false,
1921  "int_withdraw" => false,
1922  "view_transactions" => false,
1923  "view_balances" => false
1924  );
1925  $displayOrder = 999;
1926  $context = "";
1927 
1928  // ** List of account numbers we processed during XAC updates
1929  // * list maintained to not over query an account we know we
1930  // * have processed.
1931  $processedMbrAcct = Array();
1932 
1933  // ** retrieve the users that can access the member account
1934  $userList = GetMemberAccessList($dbConn, $pHbEnv, $pMbrAcct);
1935 
1936  if (is_array($userList) && count($userList) > 0) {
1937  /* ** LOOP THROUGH EACH USER ** */
1938  foreach ($userList as $user_id) {
1939  $createRecords = Array(); // Reset for each user
1940 
1941  // * the user has ACCESS to the account -- we can insert the new records
1942  /* ** LOOP THROUGH EACH INSERTED RECORD ** */
1943  reset ($pInsData);
1944  foreach ($pInsData as $tableName => $insertedRecords) {
1945  for ($recIdx = 0; $recIdx < count($insertedRecords); $recIdx++) {
1946  $insertedRecord = $insertedRecords[$recIdx];
1947  $skipRec = true;
1948 
1949  // ** For each of the inserted records create an insert statement to create a useraccount record entry
1950  //if (HCU_array_key_exists("loannumber", $insertedRecord)) {
1951  if ($tableName == "loanbalance") {
1952  $skipRec = false;
1953  /* ** LOAN BALANCE RECORD ** */
1954  $uaAcctType = trim(HCU_array_key_value("loannumber", $insertedRecord));
1955  $uaCertNumber = 0;
1956  $uaRecordType = 'L';
1957  $grantRights = Array("ext_deposit" => true,
1958  "int_deposit" => true,
1959  "ext_withdraw" => false,
1960  "int_withdraw" => true,
1961  "view_transactions" => true,
1962  "view_balances" => true
1963  );
1964 
1965  } elseif ($tableName == "accountbalance") {
1966  $skipRec = false;
1967  /* ** DEPOSIT BALANCE RECORD ** */
1968  $uaAcctType = trim(HCU_array_key_value("accounttype", $insertedRecord));
1969  $uaCertNumber = intval(HCU_array_key_value("certnumber", $insertedRecord));
1970  $uaRecordType = 'D';
1971  $grantRights = Array("ext_deposit" => true,
1972  "int_deposit" => true,
1973  "ext_withdraw" => true,
1974  "int_withdraw" => true,
1975  "view_transactions" => true,
1976  "view_balances" => true
1977  );
1978  } elseif ($tableName == "crossaccounts") {
1979  /* * CROSS ACCOUNT RECORD * */
1980  /**
1981  * ** Option 1 **
1982  * ** - CU3_CREATE_ACCESS_CONTROL_FROM_XAC == 0
1983  *
1984  * Sub-Account becomes an overloaded value, but instead of using an '@'
1985  * the cross accounts will be indentified with a '#'
1986  */
1987  if ((GetFlagsetValue("CU3_CREATE_ACCESS_CONTROL_FROM_XAC") & HCU_array_key_value("flagset3", $pHbEnv)) == 0) {
1988  $skipRec = false;
1989  $uaAcctType = trim(HCU_array_key_value("accounttype", $insertedRecord)) . '#' . trim(HCU_array_key_value("tomember", $insertedRecord));
1990  $uaCertNumber = 0;
1991 
1992  /* Record type
1993  * P - Payment
1994  * T - Transaction
1995  */
1996  $uaRecordType = (trim(HCU_array_key_value('deposittype', $insertedRecord)) == 'L' ? 'P' : 'T');
1997  $grantRights = Array("ext_deposit" => false,
1998  "int_deposit" => true,
1999  "ext_withdraw" => false,
2000  "int_withdraw" => false,
2001  "view_transactions" => false,
2002  "view_balances" => false
2003  );
2004  } else {
2005  /**
2006  * ** Option 2 **
2007  * ** - CU3_CREATE_ACCESS_CONTROL_FROM_XAC > 0
2008  *
2009  * Cross Accounts get added to the useraccounts table as another account with access
2010  * The default permissions will default with only 'INTERNAL DEPOSIT' set
2011  */
2012  if (trim(HCU_array_key_value("tomember", $insertedRecord)) == trim(HCU_array_key_value("accountnumber", $insertedRecord))) {
2013  /* ** SKIP ** */
2014  // * Skip this XAC record IF the accountnumber matches the tomember. This means it is a circular reference.
2015  continue;
2016  }
2017  $skipRec = false;
2018  // For the purpose of this procedure we override the "accountnumber" column being saved
2019  $pMbrAcct = trim(HCU_array_key_value("tomember", $insertedRecord));
2020  $uaAcctType = trim(HCU_array_key_value("accounttype", $insertedRecord));
2021  // * Cert number from Cross Accounts default to 0 (mammoth didn't support certnumber as part of cross accounts)
2022  $uaCertNumber = '0';
2023  $uaRecordType = (trim(HCU_array_key_value('deposittype', $insertedRecord)) == "L" ? "L" : "D");
2024  $grantRights = Array("ext_deposit" => false,
2025  "int_deposit" => true,
2026  "ext_withdraw" => false,
2027  "int_withdraw" => false,
2028  "view_transactions" => false,
2029  "view_balances" => false
2030  );
2031 
2032 
2033  /* ** memberacct **
2034  * The "tomember" needs to have an entry in the memberacct table. If a record is NOT found then
2035  * add the record. If the record is being inserted then the 'allowenroll' column s/b true
2036  */
2037  if (!in_array($pMbrAcct, $processedMbrAcct)) {
2038  if (!FindMemberAccountExists ($dbConn, $cuCode, $pMbrAcct)) {
2039  // ** memberacct record NOT found
2040  // ** INSERT memberacct
2041  $retVal['data'][] = $pMbrAcct;
2042 
2043  $memberRecords = array("memberacct" => array(array("_action" => "create", "accountnumber" => $pMbrAcct, "primary_user" => 0, "allowenroll" => true)));
2044 
2045  if (DataUserTableUpdate($dbConn, $pHbEnv, null, $memberRecords, $user_id, "UM_ADD", HCU_array_key_value("platform", $pHbEnv), HCU_array_key_value('currentscript', $pHbEnv), "A", "Auto Add Member Acct", HCU_array_key_value('Cn', $pHbEnv), '', HCU_array_key_value('remoteIp', $pHbEnv)) === false) {
2046  throw new exception("Adding failed.", 108);
2047  }
2048 
2049  }
2050  // ** add the account number to the array of processed
2051  $processedMbrAcct[] = $pMbrAcct;
2052  }
2053  }
2054  }
2055 
2056 
2057  if (!$skipRec) {
2058  if (!FindUserAccountExists ($dbConn, $cuCode, $user_id, $uaRecordType, $pMbrAcct, $uaAcctType, $uaCertNumber)) {
2059  $createRecords[]= array(
2060  "_action" => "create", "user_id" => $user_id,
2061  "accountnumber" => $pMbrAcct, "accounttype" => $uaAcctType, "certnumber" => $uaCertNumber, "display_name" => "",
2062  "recordtype" => $uaRecordType,
2063  "ext_deposit" => $grantRights['ext_deposit'], "ext_withdraw" => $grantRights['ext_withdraw'],
2064  "int_deposit" => $grantRights['int_deposit'], "int_withdraw" => $grantRights['int_withdraw'],
2065  "view_transactions" => $grantRights['view_transactions'], "view_balances" => $grantRights['view_balances'],
2066  "display_order" => $displayOrder);
2067  }
2068  }
2069  }
2070  }
2071  if (count($createRecords) > 0) {
2072  // ** we have records to insert
2073 
2074  $createRecords = array("useraccounts" => $createRecords);
2075 
2076  if (DataUserTableUpdate($dbConn, $pHbEnv, null, $createRecords, $user_id, "UA_ADD", HCU_array_key_value("platform", $pHbEnv), HCU_array_key_value('currentscript', $pHbEnv),
2077  "A", "Auto Add User Accounts", HCU_array_key_value('Cn', $pHbEnv), '', HCU_array_key_value('remoteIp', $pHbEnv)) === false) {
2078  throw new exception("Adding failed", 106);
2079  }
2080  // ** If we return and something is amiss -- then throw an error
2081  if (db_transaction_status($dbConn) == PGSQL_TRANSACTION_INERROR) {
2082  throw new Exception("Audit Failure", 107);
2083  }
2084  }
2085 
2086 
2087 
2088  }
2089 
2090  }
2091 
2092 
2093  } catch (Exception $e) {
2094  // ** SOME ERROR OCCURRED
2095  $retVal['code'] = '999';
2096  $retVal['error'] = $e->getMessage(); // "User Account Update Failed";
2097  $retVal['data'] = Array();
2098  }
2099 
2100  return $retVal;
2101 }
2102 
2103 
2104 /**
2105  * Function to LOAD the balance records
2106  *
2107  * $pHbEnv,
2108  * $pConnSet,
2109  * $pPktStamps,
2110  * $pMbrData,
2111  * $pDataPacket
2112  *
2113  */
2114 
2115  /**
2116  * ** Which Table to Load {account / loan}**
2117  */
2118 
2119  /*
2120  * IDEAS
2121  *
2122  * Know what I want to do.
2123  * The function should know
2124  * It should know there could be balance records..
2125  * Need to create a function that will update then insert -- Should it be resourceful and use the cuDataModel method?? Or create it's own.
2126  * The benefit of cuDataModel is audit events may be easier to create
2127  *
2128  */
Generated by   doxygen 1.8.15