Odyssey
cuDataModel.i
1 <?php
2 /**
3  * FILE: cuDataModel.i
4  *
5  * PURPOSE: This script will be used for creating a repository of functions for data retrieval
6  * from the postgres database. These functions should only be concerned with the actual
7  * query and retrieving the recordset handle. They should not perform any action with
8  * the data.
9  * ?? NOT SURE - about the purpose of this script.. But it would be nice if NO SQL was done here..
10  *
11  * MWS 10/20/2015
12  *
13  */
14 
15 
16 /**
17  *
18  * Update User Related Data In A Structured Table defined in GetTableDefinition
19  *
20  * @param integer $pDbh - Database Handle for current Credit Union Session
21  * @param array[] $pHbEnv - The current session HB_ENV -- This is the array that contains session/environment variables.
22  * This function requires the following variables in HB_ENV
23  * cu - the CU CODE
24  * @param class $pMc - The current session MC (Multi-Language) class
25  * @param array[] $pUpdTable
26  * @param integer $pAuditUser - The user id related to the data change
27  * @param string $pAction - Identifies the action that triggered this audit record. Some codes include
28  * U_ADD - User Account was added.
29  * U_DEL_A - User Account Deleted
30  * U_DEL_H - User History Deleted.
31  * U_RESET - User Password was reset
32  * U_UPD - User Account Was updated
33  * U_OMA - Override MICRs
34  * U_EST - E-statemnt Flag Changed
35  * @param string $pAppContext - Application Context {DSK, MBL, APP, ADA}
36  * @param string $pCodeContext - Name of the PHP script that called this function.
37  * @param string $pAuditType - The type of user that triggered this audit
38  * U - user, the user themselves made this particular change
39  * A - admin user, an admin user made the change to the user account.
40  * H - HomeCU made the change to the user record
41  * @param string $pFullDesc - A full description of why the audit record was necessary. {Force Password, Update Profile..etc}
42  * @param string $pUserName - The User Name value that made the triggered user. The source of this user_name
43  * depends on the type defined in pAuditSrcType
44  * @param string $pEmail - The email associated with the user
45  * @param string $pIp - The source IP address that triggered the audit record
46  * @param boolean $pSuppressAudit - {true/false(default)} When true ,the audit record will NOT be recorded regardless of table settings
47  * @param string $pAccountNumber - Either empty or an account number. This refers to audits that are account-centric, not user-centric.
48 
49  *
50  * @return array[]
51  *
52  */
53 function DataUserTableUpdate($pDbh, $pHbEnv, $pMc, $pUpdTable, $pAuditUser, $pAction, $pAppContext, $pCodeContext, $pAuditType, $pFullDesc, $pUserName, $pEmail, $pIp, $pSuppressAudit = false,
54  $pAccountNumber = "") {
55  // ** BUILD SQL ??
56  /*
57  * Should validation be done here? Or previous to calling this function
58  *
59 
60  *
61  * If snapshot is needed - Get this here.. This will perform the select query to get data, and return the information in an array with the table key as the key
62  *
63 
64  As CRUD statements are created, the snapshot is also taken at that time. It may be able to leverage the same query information this way.
65 
66 
67 
68  *
69  * Build/execute the table sql command
70  *
71 
72  *
73  * Should this function handle doing transactions? or build,execute method
74  *
75 
76  *
77  *
78  */
79 
80  /**
81  * RETHINK 2.23.2016
82  * Before I tried to make the called functions do all the work,
83  * BUT I think it best that this function perform the looping and call each function.
84  * SIMPLIFYING the scope of each function to only need to be one at a time
85  *
86  * ** NOTE ** FOR THE MOST PART THIS FUNCTION SHOULD ONLY BE UPDATING ONE TABLE AT A TIME
87  */
88 
89  /**
90  * This will contain an array of the updated values
91  */
92  $retSnapshot = array();
93  $updatedDataSnapShot = array();
94 
95  try {
96  // ** Snapshots take place before data update
97 
98  // * be sure the pUpdTable is an array
99  if (!is_array($pUpdTable)) {
100  // * ERROR - Update Table is NOT an array
101  throw new exception('Update Table Not Defined', 900);
102  }
103 
104  // * Get the table schema
105  // * tablecode located is the base key of the pUpdTable array
106 
107  $tableCodeList = array_keys($pUpdTable);
108  // * Should now have indexed list of tables.
109  if (count($tableCodeList) == 0) {
110  // ** NO TABLES DEFINED
111  throw new exception('Update Table Empty', 901);
112  }
113 
114  /**
115  * Potential for multiple table update.
116  * I will loop through the options and get the different record snapshots.
117  * Each snapshot should be an individual record
118  */
119 
120  for ($updIdx = 0; $updIdx < count($tableCodeList); $updIdx++) {
121 
122  /**
123  * LOOP THROUGH EACH TABLE UPDATE
124  */
125 
126  $tableCode = $tableCodeList[$updIdx];
127 
128  $recordAudit = (!$pSuppressAudit);
129 
130  /**
131  *
132  * GET Snapshot of ALL data ROWS prior to update
133  *
134  * @var array[] This will be an empty array if there was not snapshot??
135  *
136  */
137  if ($recordAudit) {
138  $saveBeforeDataSnapshot = GetDataSnapshot($pDbh, $pHbEnv, $pMc, $tableCode, $pUpdTable[$tableCode]);
139  } else {
140  $saveBeforeDataSnapshot = false;
141  }
142 
143 
144  /**
145  * NOW HAVE Data Snapshot Array
146  */
147 
148 // * 2/29/2016 - - Should snapshot be for ONLY the records being updated. The array storing the data can aggregate the addt'l lines if need be
149  /**
150  * LOOP THROUGH EACH ROW AND EXECUTE THE SQL
151  */
152  $savedRecords = $pUpdTable[$tableCode];
153  for ($rowIdx = 0; $rowIdx < count($savedRecords); $rowIdx++) {
154  /**
155  *
156  * Create SQL for updating the actual record (why we are here)
157  * Code execution is handled in this function as well
158  *
159  */
160  $updTableResp = SetDataTableUpdate( $pDbh, $pHbEnv, $pMc, $tableCode, $savedRecords[$rowIdx]);
161  /**
162  * CHECK the response from SetDataTableUpdate
163  */
164  if ($updTableResp['code'] == '000') {
165  foreach($updTableResp['data']['row'] as $table => $rows) // Append new records in previous snapshot
166  {
167  $retSnapshot[$table]= isset($retSnapshot[$table]) ? array_merge($retSnapshot[$table], $rows) : $rows;
168  }
169  } else {
170  throw new exception ('Data Update Error', '902');
171  }
172 
173  }
174 
175  /**
176  * FINISHED LOOPING THROUGH TABLES IN UPDATE ARRAY
177  */
178 
179  if ($saveBeforeDataSnapshot !== false) {
180  /**
181  * snapshot not false, so save...
182  *
183  */
184  /**
185  *
186  * Create Audit Insert Record SQL
187  *
188  */
189 
190  // IF $pAuditUser IS NULL: if null, is create user
191  if ($pAuditUser == null) {
192 
193  // CHECK: USER_ID SET IN $updTableResp
194  $updTableResp_id = HCU_array_key_exists("user", $updTableResp['data']['row']) ? $updTableResp['data']['row']['user'][0]['user_id'] : null;
195 
196  if (isset($updTableResp_id)) {
197  $pAuditUser = $updTableResp_id;
198  } else {
199  $pAuditUser = 0;
200  }
201  }
202 
203  $dataAuditRecord = SetAuditRecord($pDbh, $pHbEnv, $pMc, $pAuditUser, $pAction, $pAppContext, $pCodeContext, $pAuditType, $pFullDesc, $pUserName, $pEmail, $pIp,
204  HCU_JsonEncode($saveBeforeDataSnapshot), HCU_JsonEncode($retSnapshot), false, $pAccountNumber);
205 
206 
207  }
208 
209  }
210 
211  } catch (exception $e) {
212  // * For now when an error occurs I want to return an empty array
213  // ** $retSnapshot = Array();
214  // OR
215  $retSnapshot = false;
216  }
217 
218  return $retSnapshot;
219 
220 
221 }
222 
223 /**
224  * Update Admin user Related Data In A Structured Table defined in GetTableDefinition
225  *
226  * @param integer $pDbh - Database Handle for current Credit Union Session
227  * @param array[] $pHbEnv - The current session HB_ENV -- This is the array that contains session/environment variables.
228  * This function requires the following variables in HB_ENV
229  * cu - the CU CODE
230  * @param array[] $pUpdTable
231  * @param integer $pAuditUser - The user id related to the data change
232  * @param string $pAction - Identifies the action that triggered this audit record.
233  * @param string $pCodeContext - Name of the PHP script that called this function.
234  * @param string $pAuditType - The type of user that triggered this audit
235  * U - user, the user themselves made this particular change
236  * A - admin user, an admin user made the change to the user account.
237  * H - HomeCU made the change to the user record
238  * @param string $pFullDesc - A full description of why the audit record was necessary. {Force Password, Update Profile..etc}
239  * @param string $pUserName - The User Name value that made the triggered user. The source of this user_name
240  * depends on the type defined in pAuditSrcType
241  * @param string $pEmail - The email associated with the user
242  * @param string $pIp - The source IP address that triggered the audit record
243  * @param boolean $pSuppressAudit - {true/false(default)} When true ,the audit record will NOT be recorded regardless of table settings
244  *
245  * @return array[]
246  *
247  */
248 function DataAdminTableUpdate($pDbh, $pHbEnv, $pUpdTable, $pAuditUser, $pAction, $pCodeContext, $pAuditType, $pFullDesc, $pUserName, $pEmail, $pIp, $pSuppressAudit = false)
249 {
250  $retSnapshot= array();
251  try
252  {
253  if (!is_array($pUpdTable))
254  throw new exception('Update Table Not Defined', 900);
255  if (count($pUpdTable) == 0)
256  throw new exception('Update Table Empty', 901);
257 
258  foreach($pUpdTable as $table => $tableRecord)
259  {
260  $saveBeforeDataSnapshot = $pSuppressAudit ? false : GetDataSnapshot($pDbh, $pHbEnv, null, $table, $tableRecord); // Get snapshot if audit is not suppressed
261  foreach($tableRecord as $rowRecord)
262  {
263  $results= SetDataTableUpdate($pDbh, $pHbEnv, null, $table, $rowRecord);
264  if ($results["code"] != "000")
265  throw new exception ('Data Update Error', '902');
266 
267  foreach($results['data']['row'] as $table => $rows) // Append new records in previous snapshot
268  {
269  $retSnapshot[$table]= isset($retSnapshot[$table]) ? array_merge($retSnapshot[$table], $rows) : $rows;
270  }
271  }
272  }
273 
274  if ($saveBeforeDataSnapshot !== false)
275  {
276  $dataAuditRecord = SetAdminAuditRecord($pDbh, $pHbEnv, $pAuditUser, $pAction, $pCodeContext, $pAuditType, $pFullDesc, $pUserName, $pEmail, $pIp,
277  HCU_JsonEncode($saveBeforeDataSnapshot), HCU_JsonEncode($retSnapshot) );
278  }
279  }
280  catch (exception $e)
281  {
282  $retSnapshot = false;
283  }
284 
285  return $retSnapshot;
286 }
287 
288 /**
289  *
290  *
291  * This will take the list of arrays and build the large array for the snapshot.
292  * The snapshot will be an array with the tablenamekey as the array index (associative)
293  * At this time the function will only build the Record data array
294  *
295  * @param integer $pDbh - This is the current session database connection
296  * @param array[] $pHbEnv - Current HB_ENV environment array
297  * @param class $pMc - Current Translation Class
298  * @param string $pTableCode - This is the table code to take the snapshot of
299  * @param mixed[] $pUpdRows - This is an array of the updated column values for the tables being updated. It should be formatted as:
300  * {"user": [{"_action":"create|update|delete","column1":"value2:"},{"_action":"update","col2":"val2"}]}
301  *
302  * @return mixed
303  * returns FALSE if no snapshot OR ERROR
304  *
305  */
306 function GetDataSnapshot($pDbh, $pHbEnv, $pMc, $pTableCode, $pUpdRows) {
307  $retSnapShot = array();
308 
309  try {
310  // ** Snapshots take place before data update
311 
312  // * be sure the pUpdTable is an array
313  if ($pTableCode == '') {
314  // * ERROR - Update Table is NOT an array
315  throw new exception('Update Table Not Defined', 900);
316  }
317 
318  // * Get the table schema
319  // * tablecode located is the base key of the pUpdTable array
320 
321  $tableCodeList = $pTableCode;
322  // * Should now have indexed list of tables.
323  if (count($tableCodeList) == '') {
324  // ** NO TABLES DEFINED
325  throw new exception('Update Table Empty', 901);
326  }
327 
328  /**
329  * Potential for multiple table update.
330  * I will loop through the options and get the different record snapshots.
331  * Each snapshot should be an individual record
332  */
333 
334  /**
335  * First Get the table schema
336  */
337  $tblSchemaList = GetTableDefinition ($pHbEnv, $tableCodeList);
338 
339  if (!HCU_array_key_exists($tableCodeList, $tblSchemaList)) {
340  throw new exception('Update Table Empty', 902);
341  }
342  $tblSchema = $tblSchemaList[$tableCodeList];
343  if (HCU_array_key_value('_takesnapshot', $tblSchema)) {
344  // Table schema determines if we take snapshot --
345  /**
346  * Each table update will be an array. with one or more records.
347  * On error simply continue.
348  */
349  $recordsToSave = $pUpdRows;
350  if (is_array($recordsToSave)) {
351  // * The record is an array.
352  // * Loop through the records to get their snapshot
353  for ($recordIdx = 0; $recordIdx < count($recordsToSave); $recordIdx++) {
354 
355  $singleRecord = $recordsToSave[$recordIdx];
356  if (HCU_array_key_value('_action', $singleRecord) == 'create') {
357  // * For the CREATE snapshot, simply set this to an empty row
358  $retSnapShot[$tableCodeList][] = array();
359  } else {
360  // * Get the Snapshot (SELECT QUERY)
361  $sql = GetTableSelectSql($tblSchema, $singleRecord);
362  /*
363  * Data Snapshot will ONLY get one record at a time. This should preserve the desired order for the records.
364  * By default the GetAllRowsFromSql returns a [0] based array, if directly applied to the returned array a snapshot
365  * of multiple records would only show one record. Not all
366  */
367  $getSingleDataRow = GetAllRowsFromSql($pDbh, $sql);
368  $retSnapShot[$tableCodeList][] = (is_array($getSingleDataRow) ? $getSingleDataRow[0] : Array());
369  /**
370  * at this point it could be added to only return the snapshot values of fields being updated.
371  * for now it will return all columns
372  */
373  }
374  }
375  }
376  } else {
377  $retSnapShot = false;
378  }
379 
380  } catch (exception $e) {
381  // * For now when an error occurs I want to return an empty array
382  $retSnapShot = false;
383  }
384  return $retSnapShot;
385 }
386 
387 /** UpdateMemberFailedLogin
388  *
389  * Purpose: Update the member information to set a failed login attempt on their record
390  *
391  * @param integer $pDbh - This is the current database handle
392  * @param string $pCu - This is the credit union code for the CU being accessed
393  * @param string $pLogin - This is the login id of the user trying to access home banking
394  * @param integer $pFailedType - This is the integer value identifying what part of the login failed
395  *
396  * @return int - Returns the resulting recordset handle
397  */
398 function UpdateMemberFailedLogin($pDbh, $pCu, $pLogin, $pFailedType) {
399 
400  $sql = "SELECT hcumbrloginfailed('" . prep_save($pCu, 10). "', '" . prep_save($pLogin, 50) . "', " . intval($pFailedType) . ")";
401 
402  $selRs = db_query($sql, $pDbh);
403 
404  return $selRs;
405 }
406 
407 
408 /** UpdateMemberTrackLogin
409  *
410  * Purpose: Update the member information to track a successful login
411  *
412  * Parameters
413  * @param integer pDbh - This is the current database handle
414  * @param string pCu - This is the credit union code for the CU being accessed
415  * @param string pLogin - This is the login id of the user trying to access home banking
416  * @param string pFchange - Is the member required to change their password {'Y', 'N'}
417  * @param string pLastPwdChg - The timestamp when the password was last changed. Used to force member to change password on their next login. {12/15/2010 12:00}
418  * @param string pLoginType - The source of the current login, this is meta data used for tracking logins. current values includes {'MBL', 'DSK', 'CLS', 'APP', 'ADA'}
419  * @param array pMfaQuest - The mfaquest array for the current user -- this function will prepare the array to be saved
420  *
421  * return int - Returns the resulting recordset handle
422  */
423 function UpdateMemberLoginTrack($pDbh, $pCu, $pLogin, $pFchange, $pLastPwdChg, $pLoginType, $pMfaQuest) {
424  /*
425  * SANITIZE
426  */
427  $pFchange = ($pFchange == 'Y' ? 'Y' : 'N');
428 
429  $sql = "SELECT hcumbrlogintrack('" . prep_save($pCu, 10). "', '" . prep_save($pLogin, 50) . "', '$pFchange', '" . prep_save($pLastPwdChg) . "', '" . prep_save($pLoginType) . "', '" . prep_save(PrepareMfaQuestString($pMfaQuest)) . "');";
430 
431 
432  $selRs = db_query($sql, $pDbh);
433 
434  return $selRs;
435 }
436 
437 
438 /**
439  * PrepareExpressionList
440  *
441  * This function will take the accepted column list array and combine with values array
442  * to create the sql syntax of column1 = 'value1', etc. This could be used for update
443  * columns as well as where clause statements
444  *
445  * @param array $pColumnListAry - This is the list of columns.
446  * The key is the column name, the value will define the data type
447  * I - Integer
448  * C?? - Character, followed by length, if there is one
449  * N - Numeric Standard 12,2
450  * @param array $pValueListAry - This is the array of values.
451  * key is the column name
452  * value is the field value
453  * @param boolean (optional) $pAllValuesSet -- (default: false) If true the function will return ALL items in pColumnListAry (with or without value in pValueListAry)
454  * If false, the function will only return values that exist in pValueListAry
455  *
456  */
457 
458 function PrepareExpressionList ($pColumnListAry, $pValueListAry, $pAllValuesSet=false) {
459 
460  $retValueString = '';
461  try {
462  /*
463  * VALIDATION
464  * both parameters must be arrays
465  */
466  if (is_array($pColumnListAry) && is_array($pValueListAry)) {
467  // ** First get the JOINED list of the fields that will be updated
468  if ($pAllValuesSet) {
469  // ** INCLUDE ALL FIELDS
470  $updateFieldsAry = $pColumnListAry;
471  } else {
472  // * ONLY INCLUDE FIELDS THAT HAVE VALUES
473  $updateFieldsAry = array_intersect_key($pColumnListAry, $pValueListAry);
474  }
475 
476  // ** Goal is to maybe create one place to update the cuusers table, however,
477  // ** Not all fields need to be saved at the same time.
478  foreach ($updateFieldsAry as $fieldname => $fieldtype) {
479  // We will looped through the joined fields to ensure we are looking at the
480  // allowed list
481  switch (substr($fieldtype, 0, 1)) {
482  case "C":
483  case "S":
484  $maxlen = (intval(substr($fieldtype, 1)) > 0 ? intval(substr($fieldtype, 1)) : 0);
485  $retValueString .= ($retValueString != '' ? "," : "") . "{$fieldname} " . (substr($fieldtype, 0, 1) == 'S' ? 'ilike' : '=') ." '" . prep_save($pValueListAry[$fieldname], $maxlen) . "'";
486  break;
487  case "I":
488  $retValueString .= ($retValueString != '' ? "," : "") . "{$fieldname} = '" . intval($pValueListAry[$fieldname]) . "'";
489  break;
490  case "N":
491  $retValueString .= ($retValueString != '' ? "," : "") . "{$fieldname} = '" . floatval($pValueListAry[$fieldname]) . "'";
492  break;
493  default:
494  // ** DO NOTHING WITH THE FIELD
495 
496  }
497  }
498  } else {
499  throw new Exception('Validation Error', '999');
500  }
501 
502  } catch (Exception $e) {
503  $retValueString = '';
504  }
505 
506  return $retValueString;
507 }
508 
509 /**
510  *
511  * Get the array structure for a specific system table.
512  *
513  * @param array[] $pHbEnv - HB_ENV Session environment
514  * required values
515  * cu - CU Code for current session
516  * @param string $pTableCode - This is the specific table code to find
517  * @param array[] $pTableVariables - (optional) This is the list of columns to return
518  *
519  * @return mixed[] When an array is returned it will return the following structure
520  * empty array if table was NOT found
521  * OR
522  * array['tablecode'] - array that defines the table schema
523  * ['_primary-columns'] - An array of column keys that are the primary key and used for looking up values
524  * ['_tablename'] - The table name as defined in the database. Sometimes this is prefixed with cucode
525  * ['_required-columns'] - Columns that must be set for data interaction. This can help ensure we don't override
526  * a record not assigned to session member 2/17 -- DON'T LIKE THIS OPTION REMOVE IF CONTINUE TO UNUSE
527  * ['_takesnapshot'] - When an update occurs is a snapshot required?? {true, false}
528  * array['_cols'] - Array defining the column attributes
529  * ['type'] - column data type, one of:
530  * serial
531  * integer
532  * smallint
533  * bigint
534  * numeric
535  * boolean
536  * char
537  * varchar
538  * date
539  * timestamptz
540  * timestamp
541  * ['maxlength']
542  * ['required'] - assume false
543  * ['display'] - Array (title key...), display properties for the column
544  * ['precision'] - for numeric type. sets size of the numeric column
545  *
546  */
547 function GetTableDefinition ($pHbEnv, $pTableCode, $pTableVariables = array()) {
548 
549  /**
550  * Table Definition Array
551  */
552  $retTDAry = Array();
553 
554 
555  switch ($pTableCode){
556  case "group":
557  case $pHbEnv['cu'] . "group":
558  $retTDAry = array(
559  "_primary-columns" => array("group_id"),
560  "_tablename" => $pHbEnv['cu'] . "group",
561  "_required-columns" => array("group_id", "group_name", "profile_id", "contact"),
562  "_takesnapshot" => true,
563  "_cols" => array(
564  "group_id" => array("type" => DBTYPE_SERIAL),
565  "group_name" => array("type" => DBTYPE_VARCHAR, "maxlength" => 50),
566  "profile_id" => array("type" => DBTYPE_INTEGER),
567  "contact" => array("type" => DBTYPE_INTEGER)
568  )
569  );
570  break;
571  case "grouprights":
572  case $pHbEnv['cu'] . "grouprights":
573  $retTDAry = array(
574  "_primary-columns" => array("group_id", "feature_code"),
575  "_tablename" => $pHbEnv['cu'] . "grouprights",
576  "_required-columns" => array("group_id", "feature_code"),
577  "_takesnapshot" => true,
578  "_cols" => array(
579  "group_id" => array("type" => DBTYPE_INTEGER),
580  "feature_code" => array("type" => DBTYPE_VARCHAR, "maxlength" => 10),
581  "amount_per_transaction" => array("type" => DBTYPE_NUMERIC, "precision" => "11,2"),
582  "amount_per_day" => array("type" => DBTYPE_NUMERIC, "precision" => "11,2"),
583  "amount_per_month" => array("type" => DBTYPE_NUMERIC, "precision" => "11,2"),
584  "amount_per_account_per_day" => array("type" => DBTYPE_NUMERIC, "precision" => "11,2"),
585  "count_per_day" => array("type" => DBTYPE_INTEGER),
586  "count_per_month" => array("type" => DBTYPE_INTEGER),
587  "count_per_account_per_day" => array("type" => DBTYPE_INTEGER),
588  "confirm_required" => array("type" => DBTYPE_BOOLEAN)
589  )
590  );
591  break;
592  case 'user':
593  case $pHbEnv['cu'] . 'user':
594  $retTDAry = array(
595  "_primary-columns" => array("user_id"),
596  "_tablename" => $pHbEnv['cu'] . 'user',
597  "_required-columns" => array("user_id"),
598  "_takesnapshot" => true,
599  "_cols" => array(
600  "user_id" => array("type" => DBTYPE_SERIAL, "label" => "Login Id"),
601  "group_id" => array("type" => DBTYPE_INTEGER),
602  "user_name" => array("type" => DBTYPE_VARCHAR, "maxlength" => 50, "label" => "User Name"),
603  "passwd" => array("type" => DBTYPE_VARCHAR, "maxlength" => 255, "label" => "Password"),
604  "email" => array("type" => DBTYPE_VARCHAR, "maxlength" => 255, "label" => "Email"),
605  "primary_account" => array("type" => DBTYPE_CHAR, "maxlength" => 12, "label" => "Primary Accnt #"),
606  "egenl_flag" => array("type" => DBTYPE_CHAR, "maxlength" => 1),
607  "failedremain" => array("type" => DBTYPE_SMALLINT, "label" => "Failed Tries Left"),
608  "forcechange" => array("type" => DBTYPE_CHAR, "maxlength" => 1, "label" => "Force Change?"),
609  "forceremain" => array("type" => DBTYPE_SMALLINT, "label" => "Force Tries Left"),
610  "lastlogin" => array("type" => DBTYPE_CHAR, "maxlength" => 20, "label" => "Last Login"),
611  "priorlogin" => array("type" => DBTYPE_CHAR, "maxlength" => 20, "label" => "Prior Login"),
612  "failedlogin" => array("type" => DBTYPE_CHAR, "maxlength" => 20, "label" => "Failed Login"),
613  "pwchange" => array("type" => DBTYPE_TIMESTAMPTZ, "label" => "Password Change Date"),
614  "msg_tx" => array("type" => DBTYPE_SMALLINT, "label" => "Message Code"),
615  "employee" => array("type" => DBTYPE_CHAR, "maxlength" => 1, "label" => "Employee?"),
616  "userflags" => array("type" => DBTYPE_INTEGER, "label" => "User Options"),
617  "confidence" => array("type" => DBTYPE_CHAR, "maxlength" => 20, "label" => "Confidence"),
618  "challenge_quest_id" => array("type" => DBTYPE_INTEGER),
619  "pkattempt" => array("type" => DBTYPE_BIGINT, "label" => "Package Attempt"),
620  "other_rights" => array("type" => DBTYPE_CHAR, "maxlength" => 10, "label" => "Other Rights"),
621  "is_group_primary" => array("type" => DBTYPE_BOOLEAN, "label" => "Primary?"),
622  "name" => array("type" => DBTYPE_VARCHAR, "maxlength" => 100, "label" => "Name"),
623  "contact" => array("type" => DBTYPE_INTEGER),
624  "schedule_code" => array("type" => DBTYPE_CHAR, "maxlength" => 12),
625  "ip" => array("type" => DBTYPE_VARCHAR, "maxlength" => 255),
626  "mfaquest" => array("type" => DBTYPE_VARCHAR)
627  )
628  );
629  break;
630  case 'userrights':
631  case $pHbEnv['cu'] . 'userrights':
632  $retTDAry= array( "_label" => "User Rights",
633  "_primary-columns" => array("user_id", "feature_code"),
634  "_tablename" => $pHbEnv['cu'] . 'userrights',
635  "_required-columns" => array("user_id", "feature_code"),
636  "_takesnapshot" => true,
637  "_cols" => array(
638  "user_id" => array("type" => "integer", "label" => "Login Id"),
639  "feature_code" => array("type" => "char", "maxlength" => 10, "label" => "Feature"),
640  "amount_per_transaction" => array("type" => "numeric", "precision" => "11,2", "label" => "Amount Per Transaction", "nullable" => true),
641  "amount_per_day" => array("type" => "numeric", "precision" => "11,2", "label" => "Amount Per Day", "nullable" => true),
642  "amount_per_month" => array("type" => "numeric", "precision" => "11,2", "label" => "Amount Per Month", "nullable" => true),
643  "amount_per_account_per_day" => array("type" => "numeric", "percision" => "11,2", "label" => "Amount Per Account Per Day", "nullable" => true),
644  "deny_access" => array("type" => "boolean", "label" => "Access?", "nullable" => true),
645  "deny_create" => array("type" => "boolean", "label" => "Create?", "nullable" => true),
646  "deny_confirm" => array("type" => "boolean", "label" => "Approve?", "nullable" => true),
647  "deny_decline" => array("type" => "boolean", "label" => "Reject?", "nullable" => true),
648  "deny_report" => array("type" => "boolean", "maxlength" => 1, "label" => "Report Value", "nullable" => true),
649  "confirm_required" => array("type" => "boolean", "label" => "Confirm Required", "nullable" => true),
650  "count_per_day" => array("type" => "integer", "label" => "Count Per Day", "nullable" => true),
651  "count_per_month" => array("type" => "integer", "label" => "Count Per Month", "nullable" => true),
652  "count_per_account_per_day" => array("type" => "integer", "label" => "Count Per Account Per Day", "nullable" => true)
653  )
654  );
655  break;
656  case 'useraccount':
657  case "useraccounts":
658  case $pHbEnv['cu'] . 'useraccounts':
659  $retTDAry = array(
660  "_label" => "User Account",
661  "_primary-columns" => array("user_id", "accountnumber", "accounttype", "certnumber", "recordtype"),
662  "_tablename" => $pHbEnv['cu'] . 'useraccounts',
663  "_required-columns" => Array("user_id", "accountnumber", "accounttype", "certnumber", "recordtype"),
664  "_takesnapshot" => true,
665  "_cols" => array(
666  "user_id" => array("type" => DBTYPE_INTEGER, "label" => "User Id"),
667  "accountnumber" => array("type" => DBTYPE_CHAR, "maxlength" => 12, "label" => "Accnt #"),
668  "accounttype" => array("type" => DBTYPE_CHAR, "maxlength" => 25, "label" => "Accnt Type"),
669  "certnumber" => array("type" => DBTYPE_INTEGER, "label" => "Cert Number"),
670  "recordtype" => array("type" => DBTYPE_CHAR, "maxlength" => 1, "label" => "Record Type"),
671  "display_name" => array("type" => DBTYPE_CHAR, "maxlength" => 255, "label" => "Name"),
672  "view_balances" => array("type" => DBTYPE_BOOLEAN, "label" => "View Balances?"),
673  "view_transactions" => array("type" => DBTYPE_BOOLEAN, "label" => "View Transactions?"),
674  "int_deposit" => array("type" => DBTYPE_BOOLEAN, "label" => "Internal Deposit?"),
675  "int_withdraw" => array("type" => DBTYPE_BOOLEAN, "label" => "Internal Withdraw?"),
676  "ext_deposit" => array("type" => DBTYPE_BOOLEAN, "label" => "External Deposit?"),
677  "ext_withdraw" => array("type" => DBTYPE_BOOLEAN, "label" => "External Withdraw?"),
678  "display_order" => array("type" => DBTYPE_SMALLINT, "label" => "Display Order"),
679  "display_qty" => array("type" => DBTYPE_SMALLINT, "label" => "Display Quantity"),
680  "display_qty_type" => array("type" => DBTYPE_CHAR, "label" => "Display Quantity Type")
681  )
682  );
683  break;
684  case 'alert':
685  case "cu_alerts":
686  $retTDAry = array(
687  "_primary-columns" => array("id"),
688  "_label" => "Alert",
689  "_tablename" => "cu_alerts",
690  "_takesnapshot" => true,
691  "_cols" => array(
692  "id" => array("type" => DBTYPE_SERIAL),
693  "alerttype" => array("type" => DBTYPE_CHAR, "maxlength" => 1),
694  "cu" => array("type" => DBTYPE_CHAR, "maxlength" => 10),
695  "user_id" => array("type" => DBTYPE_INTEGER),
696  "accountnumber" => array("type" => DBTYPE_CHAR, "maxlength" => 12),
697  "accounttype" => array("type" => DBTYPE_CHAR, "maxlength" => 25),
698  "certnumber" => array("type" => DBTYPE_INTEGER),
699  "emailtype" => array("type" => DBTYPE_CHAR, "maxlength" => 1),
700  "notifyto" => array("type" => DBTYPE_CHAR, "maxlength" => 50),
701  "provider_id" => array("type" => DBTYPE_CHAR, "maxlength" => 35),
702  "notifymsg" => array("type" => DBTYPE_VARCHAR, "maxlength" => 255),
703  "incbal" => array("type" => DBTYPE_SMALLINT),
704  "incamt" => array("type" => DBTYPE_SMALLINT),
705  "inctransdesc" => array("type" => DBTYPE_SMALLINT),
706  "lastalert" => array("type" => DBTYPE_TIMESTAMPTZ),
707  "notifyamt" => array("type" => DBTYPE_NUMERIC, "precision" => "12"),
708  "useavailbal" => array("type" => DBTYPE_SMALLINT),
709  "notifyrange" => array("type" => DBTYPE_SMALLINT),
710  "notifyamtmin" => array("type" => DBTYPE_NUMERIC, "precision" => "12"),
711  "notifyamtmax" => array("type" => DBTYPE_NUMERIC, "precision" => "12"),
712  "notifydesc" => array("type" => DBTYPE_VARCHAR, "maxlength" => 90),
713  "notifytranstype" => array("type" => DBTYPE_CHAR, "maxlength" => 1),
714  "notifychknum" => array("type" => DBTYPE_CHAR, "maxlength" => 8),
715  "notifyloandaysprior" => array("type" => DBTYPE_SMALLINT),
716  "alertstatus" => array("type" => DBTYPE_SMALLINT, "label" => "Enabled?"),
717  "lasttrace" => array("type" => DBTYPE_CHAR, "maxlength" => 16)
718  )
719  );
720  break;
721  case 'cu_scheduledtxn':
722  $retTDAry = array(
723  "_primary-columns" => array("id"),
724  "_label" => "Scheduled",
725  "_tablename" => "cu_scheduledtxn",
726  "_takesnapshot" => true,
727  "_cols" => array(
728  "id" => array("type" => DBTYPE_INTEGER, "nullable" => false),
729  "cu" => array("type" => DBTYPE_VARCHAR, "maxlength" => 10, "nullable" => false),
730  "name" => array("type" => DBTYPE_VARCHAR, "maxlength" => 100),
731  "feature_code" => array("type" => DBTYPE_VARCHAR, "maxlength" => 10, "nullable" => false),
732  "user_id" => array("type" => DBTYPE_INTEGER, "nullable" => false),
733  "create_date" => array("type" => DBTYPE_TIMESTAMP),
734  "approved_by" => array("type" => DBTYPE_INTEGER),
735  "approved_date" => array("type" => DBTYPE_TIMESTAMP),
736  "last_edit_by" => array("type" => DBTYPE_INTEGER),
737  "last_edit_date" => array("type" => DBTYPE_DATE),
738  "start_date" => array("type" => DBTYPE_DATE),
739  "end_date" => array("type" => DBTYPE_DATE),
740  "next_trigger_date" => array("type" => DBTYPE_DATE),
741  "interval_count" => array("type" => DBTYPE_SMALLINT),
742  "failure_count" => array("type" => DBTYPE_SMALLINT),
743  "status" => array("type" => DBTYPE_CHAR, "maxlength" => 1),
744  "repeating_parameters" => array("type" => DBTYPE_VARCHAR),
745  "txn_data" => array("type" => DBTYPE_VARCHAR),
746  "approved_status" => array("type" => DBTYPE_SMALLINT)
747  )
748  );
749  break;
750  case 'scheduled':
751  break;
752  case 'audituser':
753  case $pHbEnv['cu'] . 'audituser':
754  $retTDAry = array(
755  "_primary-columns" => array("user_id", "auditdate", "auditaction", "accountnumber"),
756  "_tablename" => $pHbEnv['cu'] . 'audituser',
757  "_required-columns" => Array("user_id", "auditdate", "auditaction", "accountnumber"),
758  "_takesnapshot" => false,
759  "_cols" => array(
760  "user_id" => array("type" => DBTYPE_INTEGER),
761  "auditdate" => array("type" => DBTYPE_TIMESTAMPTZ),
762  "auditaction" => array("type" => DBTYPE_VARCHAR, "maxlength" => 10),
763  "auditsrctype" => array("type" => DBTYPE_CHAR, "maxlength" => 1),
764  "auditsrcuser_name" => array("type" => DBTYPE_VARCHAR, "maxlength" => 50),
765  "auditsrcemail" => array("type" => DBTYPE_VARCHAR, "maxlength" => 255),
766  "auditrecbefore" => array("type" => DBTYPE_VARCHAR),
767  "auditrecafter" => array("type" => DBTYPE_VARCHAR),
768  "auditsrcip" => array("type" => DBTYPE_VARCHAR, "maxlength" => 15),
769  "auditsrcapp_context" => array("type" => DBTYPE_VARCHAR, "maxlength" => 10),
770  "auditsrccode_context" => array("type" => DBTYPE_VARCHAR, "maxlength" => 20),
771  "auditfulldesc" => array("type" => DBTYPE_VARCHAR, "maxlength" => 255),
772  "accountnumber" => array("type" => DBTYPE_VARCHAR, "maxlength" => 12)
773  )
774  );
775  break;
776  case "memberacct":
777  case $pHbEnv['cu'] . "memberacct":
778  $retTDAry = array(
779  "_primary-columns" => array("accountnumber"),
780  "_tablename" => $pHbEnv['cu'] . "memberacct",
781  "_required-columns" => array("primary_user", "accountnumber", "estmnt_flag"),
782  "_takesnapshot" => true,
783  "_cols" => array(
784  "primary_user" => array("type" => DBTYPE_INTEGER),
785  "accountnumber" => array("type" => DBTYPE_CHAR, "maxlength" => 12),
786  "estmnt_flag" => array("type" => DBTYPE_CHAR, "maxlength" => 1),
787  "billpayid" => array("type" => DBTYPE_CHAR, "maxlength" => 12),
788  "rdcsetting" => array("type" => DBTYPE_INTEGER, "nullable" => true),
789  "restrictions" => array("type" => DBTYPE_CHAR, "maxlength" => 1, "nullable" => true),
790  "balance_stamp" => array("type" => DBTYPE_BIGINT, "nullable" => true),
791  "balance_attempt" => array("type" => DBTYPE_BIGINT, "nullable" => true),
792  "allowenroll" => array("type" => DBTYPE_BOOLEAN, "nullable" => true)
793  )
794  );
795  break;
796  case "cuovermicr":
797  $retTDAry= array(
798  "_primary-columns" => array("micrid"),
799  "_tablename" => "cuovermicr",
800  "_required-columns" => array("micrid", "cu", "accountnumber", "accounttype", "startcheck"),
801  "_takesnapshot" => true,
802  "_cols" => array(
803  "micrid" => array("type" => DBTYPE_SERIAL),
804  "cu" => array("type" => DBTYPE_CHAR, "maxlength" => 10),
805  "accountnumber" => array("type" => DBTYPE_CHAR, "maxlength" => 12),
806  "accounttype" => array("type" => DBTYPE_CHAR, "maxlength" => 12),
807  "startcheck" => array("type" => DBTYPE_INTEGER),
808  "rt" => array("type" => DBTYPE_CHAR, "maxlength" => 12),
809  "micraccount" => array("type" => DBTYPE_CHAR, "maxlength" => 17)
810  )
811  );
812  break;
813  case 'auditadmin':
814  $retTDAry = array(
815  "_primary-columns" => array("cu", "user_name", "auditdate", "auditaction"),
816  "_tablename" => 'cuauditadmin',
817  "_required-columns" => array("cu", "user_name", "auditdate", "auditaction"),
818  "_takesnapshot" => false,
819  "_cols" => array(
820  "cu" => array("type" => DBTYPE_CHAR, "maxlength" => 10, "defaultValue" => $pHbEnv['cu']),
821  "user_name" => array("type" => DBTYPE_VARCHAR, "maxlength" => 50),
822  "auditdate" => array("type" => DBTYPE_TIMESTAMPTZ),
823  "auditaction" => array("type" => DBTYPE_VARCHAR, "maxlength" => 10),
824  "auditsrctype" => array("type" => DBTYPE_CHAR, "maxlength" => 1),
825  "auditsrcuser_name" => array("type" => DBTYPE_VARCHAR, "maxlength" => 50),
826  "auditsrcemail" => array("type" => DBTYPE_VARCHAR, "maxlength" => 255),
827  "auditrecbefore" => array("type" => DBTYPE_VARCHAR),
828  "auditrecafter" => array("type" => DBTYPE_VARCHAR),
829  "auditsrcip" => array("type" => DBTYPE_VARCHAR, "maxlength" => 15),
830  "auditsrccode_context" => array("type" => DBTYPE_VARCHAR, "maxlength" => 20),
831  "auditfulldesc" => array("type" => DBTYPE_VARCHAR, "maxlength" => 255)
832  )
833  );
834  break;
835  case "cuadminusers":
836  $retTDAry = array(
837  "_primary-columns" => array("cu", "user_name"),
838  "_tablename" => "cuadminusers",
839  "_required-columns" => array("cu", "user_name", "passwd", "realname", "failedremain", "forcechange", "forceremain"),
840  "_takesnapshot" => true,
841  "_cols" => array(
842  "cu" => array("type" => DBTYPE_CHAR, "maxlength" => 10),
843  "user_name" => array("type" => DBTYPE_VARCHAR, "maxlength" => 50),
844  "passwd" => array("type" => DBTYPE_VARCHAR, "maxlength" => 255),
845  "realname" => array("type" => DBTYPE_VARCHAR, "maxlength" => 50),
846  "failedremain" => array("type" => DBTYPE_INTEGER),
847  "forcechange" => array("type" => DBTYPE_CHAR, "maxlength" => 1),
848  "forceremain" => array("type" => DBTYPE_INTEGER),
849  "lastlogin" => array("type" => DBTYPE_VARCHAR, "maxlength" => 20),
850  "priorlogin" => array("type" => DBTYPE_VARCHAR, "maxlength" => 20),
851  "failedlogin" => array("type" => DBTYPE_VARCHAR, "maxlength" => 20),
852  "pwchange" => array("type" => DBTYPE_TIMESTAMP),
853  "email" => array("type" => DBTYPE_VARCHAR, "maxlength" => 50),
854  "userflags" => array("type" => DBTYPE_INTEGER),
855  "confidence" => array("type" => DBTYPE_CHAR, "maxlength" => 20),
856  "remoteip" => array("type" => DBTYPE_VARCHAR, "maxlength" => 15),
857  "usersms" => array("type" => DBTYPE_VARCHAR, "maxlength" => 40),
858  "userconfirm" => array("type" => DBTYPE_VARCHAR, "maxlength" => 50),
859  "mfaquest" => array("type" => DBTYPE_VARCHAR)
860  )
861  );
862  break;
863  case "cuadminallow":
864  $retTDAry = array(
865  "_primary-columns" => array("cu", "user_name", "program"),
866  "_tablename" => "cuadminallow",
867  "_required-columns" => array("cu", "user_name", "program"),
868  "_takesnapshot" => true,
869  "_cols" => array(
870  "cu" => array("type" => DBTYPE_CHAR, "maxlength" => 10),
871  "user_name" => array("type" => DBTYPE_VARCHAR, "maxlength" => 50),
872  "program" => array("type" => DBTYPE_VARCHAR, "maxlength" => 20)
873  )
874  );
875  break;
876  case $pHbEnv['cu'] . "memberacctrights":
877  case "memberacctrights":
878  $retTDAry = array(
879  "_primary-columns" => array("user_id", "accountnumber", "whichright"),
880  "_tablename" => $pHbEnv['cu'] . "memberacctrights",
881  "_required-columns" => array("user_id", "accountnumber", "whichright", "allowed"),
882  "_takesnapshot" => true,
883  "_cols" => array(
884  "user_id" => array("type" => DBTYPE_INTEGER),
885  "accountnumber" => array("type" => DBTYPE_CHAR, "maxlength" => 12),
886  "whichright" => array("type" => DBTYPE_VARCHAR, "maxlength" => 6),
887  "allowed" => array("type" => DBTYPE_BOOLEAN),
888  "platform" => array("type" => DBTYPE_VARCHAR, "maxlength" => 255)
889  )
890  );
891  break;
892  case $pHbEnv['cu'] . "accountbalance":
893  case "accountbalance":
894  $retTDAry = array(
895  "_primary-columns" => array("accountnumber", "accounttype", "certnumber"),
896  "_tablename" => $pHbEnv['cu'] . "accountbalance",
897  "_required-columns" => array("accountnumber", "accounttype", "certnumber"),
898  "_takesnapshot" => false,
899  "_cols" => array(
900  "accountnumber" => array("type" => DBTYPE_CHAR, "maxlength" => 12),
901  "accounttype" => array("type" => DBTYPE_CHAR, "maxlength" => 25),
902  "certnumber" => array("type" => DBTYPE_INTEGER),
903  "deposittype" => array("type" => DBTYPE_CHAR, "maxlength" => 1),
904  "description" => array("type" => DBTYPE_CHAR, "maxlength" => 255),
905  "amount" => array("type" => DBTYPE_NUMERIC, "precision" => "11,2"),
906  "ytdinterest" => array("type" => DBTYPE_NUMERIC, "precision" => "11,2"),
907  "available" => array("type" => DBTYPE_NUMERIC, "precision" => "11,2"),
908  "micraccount" => array("type" => DBTYPE_CHAR, "maxlength" => "17"),
909  "may_deposit" => array("type" => DBTYPE_BOOLEAN),
910  "may_withdraw" => array("type" => DBTYPE_BOOLEAN),
911  "interestrate" => array("type" => DBTYPE_NUMERIC, "precision" => "11,4"),
912  "maturitydate" => array("type" => DBTYPE_TIMESTAMP),
913  "misc1" => array("type" => DBTYPE_CHAR, "maxlength" => 255),
914  "balance_stamp" => array("type" => DBTYPE_BIGINT),
915  "history_stamp" => array("type" => DBTYPE_BIGINT),
916  "balance_attempt" => array("type" => DBTYPE_BIGINT),
917  "history_attempt" => array("type" => DBTYPE_BIGINT),
918  "regdcount" => array("type" => DBTYPE_SMALLINT),
919  "lastyrinterest" => array("type" => DBTYPE_NUMERIC, "precision" => "11,2")
920 
921  )
922  );
923  break;
924  case $pHbEnv['cu'] . "accounthistory":
925  case "accounthistory":
926  $retTDAry = array(
927  "_primary-columns" => array("accountnumber", "accounttype", "certnumber", "tracenumber"),
928  "_tablename" => $pHbEnv['cu'] . "accounthistory",
929  "_required-columns" => array("accountnumber", "accounttype", "certnumber", "tracenumber", "date", "amount", "balance"),
930  "_takesnapshot" => true,
931  "_cols" => array(
932  "accountnumber" => array("type" => DBTYPE_CHAR, "maxlength" => 12),
933  "accounttype" => array("type" => DBTYPE_CHAR, "maxlength" => 25),
934  "certnumber" => array("type" => DBTYPE_INTEGER),
935  "tracenumber" => array("type" => DBTYPE_CHAR, "maxlength" => 20),
936  "checknumber" => array("type" => DBTYPE_CHAR, "maxlength" => 9),
937  "date" => array("type" => DBTYPE_TIMESTAMP),
938  "amount" => array("type" => DBTYPE_NUMERIC, "precision" => "11,2"),
939  "description" => array("type" => DBTYPE_VARCHAR, "maxlength" => 255),
940  "balance" => array("type" => DBTYPE_NUMERIC, "precision" => "11,2"),
941  "loaddate" => array("type" => DBTYPE_TIMESTAMP),
942  "sortkey" => array("type" => DBTYPE_CHAR, "maxlength" => 16)
943  )
944  );
945  break;
946  case $pHbEnv['cu'] . "loanbalance":
947  case "loanbalance":
948  $retTDAry = array(
949  "_primary-columns" => array("accountnumber", "loannumber"),
950  "_tablename" => $pHbEnv['cu'] . "loanbalance",
951  "_required-columns" => array("accountnumber", "loannumber"),
952  "_takesnapshot" => false,
953  "_cols" => array(
954  "accountnumber" => array("type" => DBTYPE_CHAR, "maxlength" => 12),
955  "loannumber" => array("type" => DBTYPE_CHAR, "maxlength" => 25),
956  "currentbalance" => array("type" => DBTYPE_NUMERIC, "precision" => "11,2"),
957  "payoff" => array("type" => DBTYPE_NUMERIC, "precision" => "11,2"),
958  "paymentamount" => array("type" => DBTYPE_NUMERIC, "precision" => "11,2"),
959  "nextduedate" => array("type" => DBTYPE_TIMESTAMP),
960  "description" => array("type" => DBTYPE_CHAR, "maxlength" => 255),
961  "interestrate" => array("type" => DBTYPE_NUMERIC, "precision" => "11,4"),
962  "creditlimit" => array("type" => DBTYPE_NUMERIC, "precision" => "11,2"),
963  "ytdinterest" => array("type" => DBTYPE_NUMERIC, "precision" => "11,2"),
964  "misc1" => array("type" => DBTYPE_CHAR, "maxlength" => 255),
965  "cbtype" => array("type" => DBTYPE_CHAR, "maxlength" => "17"),
966  "lastpaymentdate" => array("type" => DBTYPE_TIMESTAMP),
967  "balance_stamp" => array("type" => DBTYPE_BIGINT),
968  "history_stamp" => array("type" => DBTYPE_BIGINT),
969  "balance_attempt" => array("type" => DBTYPE_BIGINT),
970  "history_attempt" => array("type" => DBTYPE_BIGINT),
971  "may_payment" => array("type" => DBTYPE_BOOLEAN),
972  "may_addon" => array("type" => DBTYPE_BOOLEAN),
973  "currentdue" => array("type" => DBTYPE_NUMERIC, "precision" => "11,2"),
974  "lastyrinterest" => array("type" => DBTYPE_NUMERIC, "precision" => "11,2"),
975  "unpaidinterest" => array("type" => DBTYPE_NUMERIC, "precision" => "11,2"),
976  "frequencyperyear" => array("type" => DBTYPE_SMALLINT),
977  "originalamount" => array("type" => DBTYPE_NUMERIC, "precision" => "11,2"),
978  "originaldate" => array("type" => DBTYPE_TIMESTAMP),
979  "term" => array("type" => DBTYPE_INTEGER),
980  "creditdisability" => array("type" => DBTYPE_CHAR, "maxlength" => "1"),
981  "creditlife" => array("type" => DBTYPE_CHAR, "maxlength" => "1")
982  )
983  );
984  break;
985  case $pHbEnv['cu'] . "loanhistory":
986  case "loanhistory":
987  $retTDAry = array(
988  "_primary-columns" => array("accountnumber", "loannumber", "tracenumber"),
989  "_tablename" => $pHbEnv['cu'] . "loanhistory",
990  "_required-columns" => array("accountnumber", "loannumber", "tracenumber", "date", "principleamount", "interestamount"),
991  "_takesnapshot" => true,
992  "_cols" => array(
993  "accountnumber" => array("type" => DBTYPE_CHAR, "maxlength" => 12),
994  "loannumber" => array("type" => DBTYPE_CHAR, "maxlength" => 25),
995  "tracenumber" => array("type" => DBTYPE_CHAR, "maxlength" => 20),
996  "date" => array("type" => DBTYPE_TIMESTAMP),
997  "principleamount" => array("type" => DBTYPE_NUMERIC, "precision" => "11,2"),
998  "interestamount" => array("type" => DBTYPE_NUMERIC, "precision" => "11,2"),
999  "description" => array("type" => DBTYPE_VARCHAR, "maxlength" => 255),
1000  "balance" => array("type" => DBTYPE_NUMERIC, "precision" => "11,2"),
1001  "sortkey" => array("type" => DBTYPE_CHAR, "maxlength" => 16),
1002  "fee" => array("type" => DBTYPE_NUMERIC, "precision" => "11,2"),
1003  "escrow" => array("type" => DBTYPE_NUMERIC, "precision" => "11,2")
1004  )
1005  );
1006  break;
1007  case $pHbEnv['cu'] . "holds":
1008  case "holds":
1009  $retTDAry = array(
1010  "_primary-columns" => array("accountnumber", "accounttype", "certnumber", "tracenumber"),
1011  "_tablename" => $pHbEnv['cu'] . "loanhistory",
1012  "_required-columns" => array("accountnumber", "accounttype", "certnumber", "holdtype", "tracenumber", "postdate", "amount", "description"),
1013  "_takesnapshot" => true,
1014  "_cols" => array(
1015  "accountnumber" => array("type" => DBTYPE_CHAR, "maxlength" => 12),
1016  "accounttype" => array("type" => DBTYPE_CHAR, "maxlength" => 25),
1017  "certnumber" => array("type" => DBTYPE_INTEGER,),
1018  "holdtype" => array("type" => DBTYPE_CHAR, "maxlength" => 1),
1019  "tracenumber" => array("type" => DBTYPE_CHAR, "maxlength" => 20),
1020  "postdate" => array("type" => DBTYPE_TIMESTAMPTZ),
1021  "expiredate" => array("type" => DBTYPE_TIMESTAMPTZ),
1022  "amount" => array("type" => DBTYPE_NUMERIC, "precision" => "11,2"),
1023  "description" => array("type" => DBTYPE_VARCHAR, "maxlength" => 255)
1024  )
1025  );
1026  break;
1027 
1028  case $pHbEnv['cu'] . "crossaccounts":
1029  case "crossaccounts":
1030  $retTDAry = array(
1031  "_primary-columns" => array("accountnumber", "tomember", "accounttype", "deposittype"),
1032  "_tablename" => $pHbEnv['cu'] . "crossaccounts",
1033  "_required-columns" => array("accountnumber", "tomember", "accounttype", "deposittype"),
1034  "_takesnapshot" => true,
1035  "_cols" => array(
1036  "accountnumber" => array("type" => DBTYPE_CHAR, "maxlength" => 12),
1037  "tomember" => array("type" => DBTYPE_CHAR, "maxlength" => 12),
1038  "accounttype" => array("type" => DBTYPE_CHAR, "maxlength" => 12),
1039  "deposittype" => array("type" => DBTYPE_CHAR, "maxlength" => 1),
1040  "description" => array("type" => DBTYPE_VARCHAR, "maxlength" => 255),
1041  "misc1" => array("type" => DBTYPE_VARCHAR, "maxlength" => 255)
1042  )
1043  );
1044  break;
1045  case "cuadmin":
1046  $retTDAry = array(
1047  "_primary-columns" => array("cu"),
1048  "_tablename" => "cuadmin",
1049  "_required-columns" => array("cu"),
1050  "_takesnapshot" => true,
1051  "_cols" => array(
1052  "cu" => array("type" => DBTYPE_CHAR, "maxlength" => 10),
1053  "user_name" => array("type" => DBTYPE_CHAR, "maxlength" => 12),
1054  "passwd" => array("type" => DBTYPE_VARCHAR, "maxlength" => 255),
1055  "vendor" => array("type" => DBTYPE_CHAR, "maxlength" => 15),
1056  "nextbatch" => array("type" => DBTYPE_INTEGER),
1057  "fid" => array("type" => DBTYPE_CHAR, "maxlength" => 6),
1058  "t" => array("type" => DBTYPE_CHAR, "maxlength" => 3),
1059  "db" => array("type" => DBTYPE_CHAR, "maxlength" => 1),
1060  "mtx" => array("type" => DBTYPE_CHAR, "maxlength" => 1),
1061  "mxcu" => array("type" => DBTYPE_CHAR, "maxlength" => 36),
1062  "img" => array("type" => DBTYPE_CHAR, "maxlength" => 10),
1063  "rt" => array("type" => DBTYPE_CHAR, "maxlength" => 12),
1064  "orgname" => array("type" => DBTYPE_CHAR, "maxlength" => 36),
1065  "livebatch" => array("type" => DBTYPE_CHAR, "maxlength" => 1),
1066  "cc" => array("type" => DBTYPE_CHAR, "maxlength" => 1),
1067  "pname" => array("type" => DBTYPE_CHAR, "maxlength" => 20),
1068  "liveport" => array("type" => DBTYPE_INTEGER),
1069  "livewait" => array("type" => DBTYPE_INTEGER),
1070  "lastupdate" => array("type" => DBTYPE_CHAR, "maxlength" => 30),
1071  "from_name" => array("type" => DBTYPE_CHAR, "maxlength" => 35),
1072  "from_email" => array("type" => DBTYPE_CHAR, "maxlength" => 50),
1073  "stmt_msg" => array("type" => "text"),
1074  "email_footer" => array("type" => "text"),
1075  "liveserver" => array("type" => DBTYPE_VARCHAR, "maxlength" => 100),
1076  "retrylimit" => array("type" => DBTYPE_INTEGER),
1077  "upllaststart" => array("type" => DBTYPE_TIMESTAMPTZ),
1078  "upllaststat" => array("type" => DBTYPE_INTEGER),
1079  "tranformat" => array("type" => DBTYPE_SMALLINT),
1080  "ahdropdays" => array("type" => DBTYPE_CHAR, "maxlength" => 4),
1081  "lhdropdays" => array("type" => DBTYPE_CHAR, "maxlength" => 4),
1082  "upllastend" => array("type" => DBTYPE_TIMESTAMPTZ),
1083  "trandropdays" => array("type" => DBTYPE_CHAR, "maxlength" => 4),
1084  "trandropbatches" => array("type" => DBTYPE_CHAR, "maxlength" => 4),
1085  "tz" => array("type" => DBTYPE_CHAR, "maxlength" => 25),
1086  "histtrunc" => array("type" => DBTYPE_CHAR, "maxlength" => 1),
1087  "showavailable" => array("type" => DBTYPE_CHAR, "maxlength" => 1),
1088  "gracelimit" => array("type" => DBTYPE_INTEGER),
1089  "pwdays" => array("type" => DBTYPE_INTEGER),
1090  "flagset" => array("type" => DBTYPE_BIGINT),
1091  "histdays" => array("type" => DBTYPE_INTEGER),
1092  "surveyintro" => array("type" => DBTYPE_VARCHAR),
1093  "tranfldsep" => array("type" => DBTYPE_INTEGER),
1094  "traneol" => array("type" => DBTYPE_INTEGER),
1095  "bp_cuid" => array("type" => DBTYPE_VARCHAR, "maxlength" => 9),
1096  "bp_secret" => array("type" => DBTYPE_VARCHAR, "maxlength" => 255),
1097  "flagset2" => array("type" => DBTYPE_BIGINT),
1098  "dep_exp_days" => array("type" => DBTYPE_SMALLINT),
1099  "ccinfourl" => array("type" => DBTYPE_VARCHAR),
1100  "min_chlng_qst" => array("type" => DBTYPE_SMALLINT),
1101  "cookie_ver" => array("type" => DBTYPE_CHAR, "maxlength" => 1),
1102  "loginscript" => array("type" => DBTYPE_VARCHAR, "maxlength" => 50),
1103  "offlinestat" => array("type" => DBTYPE_CHAR, "maxlength" => 1),
1104  "offlineblurb" => array("type" => DBTYPE_VARCHAR, "maxlength" => 255),
1105  "bp_vendor" => array("type" => DBTYPE_CHAR, "maxlength" => 10),
1106  "ssovendor" => array("type" => DBTYPE_CHAR, "maxlength" => 10),
1107  "ckhexkey" => array("type" => DBTYPE_VARCHAR, "maxlength" => 50),
1108  "ckorgid" => array("type" => DBTYPE_VARCHAR, "maxlength" => 20),
1109  "ckurl" => array("type" => DBTYPE_VARCHAR),
1110  "trmemomaxlen" => array("type" => DBTYPE_SMALLINT),
1111  "flagset3" => array("type" => DBTYPE_BIGINT),
1112  "livesetup" => array("type" => DBTYPE_BIGINT),
1113  "pwdconfig" => array("type" => DBTYPE_VARCHAR),
1114  "ip_acl" => array("type" => DBTYPE_VARCHAR, "maxlength" => 500),
1115  "maskconfig" => array("type" => DBTYPE_VARCHAR)
1116  )
1117  );
1118  break;
1119  case "cuadmemailtmpl":
1120  $retTDAry = array(
1121  "_primary-columns" => array("tmpl_id"),
1122  "_tablename" => "cuadmemailtmpl",
1123  "_required-columns" => array("tmpl_id"),
1124  "_takesnapshot" => true,
1125  "_cols" => array(
1126  "tmpl_id" => array("type" => DBTYPE_SERIAL),
1127  "tmpl_name" => array("type" => DBTYPE_VARCHAR, "maxlength" => 50),
1128  "tmpl_email" => array("type" => DBTYPE_VARCHAR, "maxlength" => 100),
1129  "tmpl_email_name" => array("type" => DBTYPE_VARCHAR, "maxlength" => 100),
1130  "tmpl_subject" => array("type" => DBTYPE_VARCHAR, "maxlength" => 255),
1131  "tmpl_message" => array("type" => DBTYPE_VARCHAR),
1132  "tmpl_opt_mailto" => array("type" => DBTYPE_SMALLINT),
1133  "tmpl_opt_mbr" => array("type" => DBTYPE_SMALLINT),
1134  "tmpl_lastmodified" => array("type" => DBTYPE_TIMESTAMPTZ),
1135  "tmpl_lastused" => array("type" => DBTYPE_TIMESTAMPTZ),
1136  "cu" => array("type" => DBTYPE_CHAR, "maxlength" => 10)
1137  )
1138  );
1139  break;
1140  case "cuadmnotify":
1141  $retTDAry = array(
1142  "_primary-columns" => array("cu", "role"),
1143  "_tablename" => "cuadmnotify",
1144  "_required-columns" => array("cu", "role"),
1145  "_takesnapshot" => true,
1146  "_cols" => array(
1147  "cu" => array("type" => DBTYPE_CHAR, "maxlength" => 10),
1148  "role" => array("type" => DBTYPE_CHAR, "maxlength" => 15),
1149  "email" => array("type" => DBTYPE_VARCHAR)
1150  )
1151  );
1152  break;
1153  case "cucontact":
1154  $retTDAry = array(
1155  "_primary-columns" => array("contactid"),
1156  "_tablename" => "cucontact",
1157  "_required-columns" => array("user_name", "priority", "contactid"),
1158  "_takesnapshot" => true,
1159  "_cols" => array(
1160  "user_name" => array("type" => DBTYPE_CHAR, "maxlength" => 12),
1161  "priority" => array("type" => DBTYPE_SMALLINT),
1162  "name" => array("type" => DBTYPE_CHAR, "maxlength" => 35),
1163  "phone" => array("type" => DBTYPE_CHAR, "maxlength" => 15),
1164  "comments" => array("type" => DBTYPE_CHAR, "maxlength" => 255),
1165  "contactid" => array("type" => DBTYPE_SERIAL)
1166  )
1167  );
1168  break;
1169 
1170  case $pHbEnv['cu'] ."usercontact":
1171  case "usercontact":
1172  $retTDAry = array(
1173  "_primary-columns" => array("contact_id"),
1174  "_tablename" => $pHbEnv['cu'] ."usercontact",
1175  "_required-columns" => array("contact_id"),
1176  "_takesnapshot" => true,
1177  "_cols" => array(
1178  "contact_id" => array("type" => DBTYPE_SERIAL),
1179  "address1" => array("type" => DBTYPE_VARCHAR, "maxlength" => 100),
1180  "address2" => array("type" => DBTYPE_VARCHAR, "maxlength" => 100),
1181  "city" => array("type" => DBTYPE_VARCHAR, "maxlength" => 30),
1182  "state" => array("type" => DBTYPE_VARCHAR, "maxlength" => 30),
1183  "zip" => array("type" => DBTYPE_VARCHAR, "maxlength" => 9),
1184  "phones" => array("type" => DBTYPE_VARCHAR)
1185  )
1186  );
1187  break;
1188 
1189  // Needed for audits migrated from Mammoth
1190  case "cuusers":
1191  $retTDAry = array(
1192  "_primary-columns" => array(""),
1193  "_tablename" => "cuusers",
1194  "_required-columns" => array("cu", "user_name", "passwd"),
1195  "_takesnapshot" => true,
1196  "_cols" => array(
1197  "cu" => array("type" => DBTYPE_CHAR, "maxlength" => 10),
1198  "user_name" => array("type" => DBTYPE_CHAR, "maxlength" => 12),
1199  "passwd" => array("type" => DBTYPE_CHAR, "maxlength" => 34),
1200  "pktdate" => array("type" => DBTYPE_CHAR, "maxlength" => 30),
1201  "pktstamp" => array("type" => DBTYPE_BIGINT),
1202  "email" => array("type" => DBTYPE_VARCHAR, "maxlength" => 50),
1203  "estmt_flag" => array("type" => DBTYPE_CHAR, "maxlength" => 1),
1204  "egenl_flag" => array("type" => DBTYPE_CHAR, "maxlength" => 1),
1205  "failedremain" => array("type" => DBTYPE_INTEGER),
1206  "forcechange" => array("type" => DBTYPE_CHAR, "maxlength" => 1),
1207  "forceremain" => array("type" => DBTYPE_INTEGER),
1208  "lastlogin" => array("type" => DBTYPE_CHAR, "maxlength" => 20),
1209  "priorlogin" => array("type" => DBTYPE_CHAR, "maxlength" => 20),
1210  "failedlogin" => array("type" => DBTYPE_CHAR, "maxlength" => 20),
1211  "pwchange" => array("type" => DBTYPE_TIMESTAMPTZ),
1212  "msg_tx" => array("type" => DBTYPE_SMALLINT),
1213  "billpayid" => array("type" => DBTYPE_CHAR, "maxlength" => 12),
1214  "employee" => array("type" => DBTYPE_CHAR, "maxlength" => 1),
1215  "depositlimit" => array("type" => DBTYPE_NUMERIC, "precision" => "12,2"),
1216  "userflags" => array("type" => DBTYPE_INTEGER),
1217  "confidence" => array("type" => DBTYPE_CHAR, "maxlength" => 20),
1218  "user_alias" => array("type" => DBTYPE_VARCHAR, "maxlength" => 50),
1219  "challenge_quest_id" => array("type" => DBTYPE_INTEGER),
1220  "pktattempt" => array("type" => DBTYPE_BIGINT),
1221  "txlist" => array("type" => DBTYPE_VARCHAR)
1222  )
1223  );
1224  break;
1225 
1226  case "cucorerequests":
1227  $retTDAry = array(
1228  "_primary-columns" => array("id"),
1229  "_tablename" => "cucorerequests",
1230  "_required-columns" => array("id"),
1231  "_takesnapshot" => true,
1232  "_cols" => array(
1233  "id" => array("type" => DBTYPE_INTEGER),
1234  "cu" => array("type" => DBTYPE_CHAR, "maxlength" => 12),
1235  "appliance_ip" => array("type" => DBTYPE_VARCHAR, "maxlength" => 100),
1236  "accountnumber" => array("type" => DBTYPE_CHAR, "maxlength" => 12),
1237  "request_type" => array("type" => DBTYPE_VARCHAR, "maxlength" => 20),
1238  "request_url" => array("type" => DBTYPE_VARCHAR),
1239  "request_start" => array("type" => DBTYPE_TIMESTAMP),
1240  "request_end" => array("type" => DBTYPE_TIMESTAMP),
1241  # request_elapsed: actual type is integer, but we evaluate a string
1242  # expression during the update to get the value for this column
1243  "request_elapsed" => array("type" => DBTYPE_CUSTOM_EXPRESSION),
1244  "request_status" => array("type" => DBTYPE_VARCHAR),
1245  "remote_ip" => array("type" => DBTYPE_CHAR, "maxlength" => 15)
1246  )
1247  );
1248  break;
1249 
1250  }
1251 
1252  if (count($pTableVariables) > 0)
1253  {
1254  foreach($retTDAry["_cols"] as $col)
1255  {
1256  if(!isset($retTDAry["_primary-columns"][$col]) && !isset($pTableVariables[$col])) // If the column doesn't exist in the primary columns or the $pTableVariables array then remove it.
1257  unset($retTDAry["_cols"][$col]);
1258  }
1259  }
1260 
1261  $retTDAry= array($pTableCode => $retTDAry);
1262  return $retTDAry;
1263 }
1264 
1265 /**
1266  *
1267  * Build the SQL to execute
1268  * Do this by retrieving the table definition
1269  * Then build sql code from the fields
1270  *
1271  * @param string $pTableSchema - This is the table schema to which we want data
1272  * @param array $pValuesAry - (optional) This is the array of values to be used in the WHERE clause
1273  * This should be for ONE Record, THE RECORD NEEDING THE SNAPSHOT
1274  * @param boolean $pAllValues - (optional) If true, whatever is the $pValuesAry shows up in the where clause.
1275  */
1276 function GetTableSelectSql($pTableSchema, $pValuesAry = array(), $pAllValues=false) {
1277 
1278  $sqlSelect = '';
1279  $sqlWhere = '';
1280  $retSql = '';
1281 
1282  $pValuesAry = (is_array($pValuesAry) ? $pValuesAry : array());
1283 
1284  if (HCU_array_key_exists('_cols', $pTableSchema) && HCU_array_key_value('_tablename', $pTableSchema) != '') {
1285  // * Found the table definition -- Build SELECT ?
1286  $primaryColumns = HCU_array_key_value('_primary-columns', $pTableSchema);
1287  $primaryColumns = is_array($primaryColumns) ? array_flip($primaryColumns) : array(); // Ensure the primaryColumns is an array of some sort -- make the values keys
1288 
1289  foreach ($pTableSchema['_cols'] as $colName => $colAttr) {
1290  $sqlSelect .= ($sqlSelect != '' ? ', ' : '') . $colName;
1291 
1292  $addToWhere= false;
1293 
1294  // * If the colName is found in primary columns; ** THIS COLUMN IS A PRIMARY -- CHECK FOR VALUE
1295  if (HCU_array_key_value($colName, $pValuesAry) && (HCU_array_key_exists($colName, $primaryColumns) || $pAllValues))
1296  {
1297  $colValue = $pValuesAry[$colName];
1298  $addToWhere= true;
1299  }
1300 
1301  // There is a value, build the where statement
1302  if ($addToWhere)
1303  $sqlWhere .= ($sqlWhere != '' ? " AND " : "") . " $colName = " . db_build_expr_value($colValue, $colAttr);
1304  }
1305 
1306  // ** Now build the sql to return
1307  $retSql = "SELECT $sqlSelect FROM {$pTableSchema['_tablename']} " . ($sqlWhere != '' ? " WHERE $sqlWhere" : "");
1308 
1309  }
1310 
1311 
1312  return $retSql;
1313 }
1314 
1315 
1316 /**
1317  *
1318  * Return the main table columns. This will ignore any configuration columns
1319  *
1320  * @param array[] pSingleRecord - This should be only ONE data record update
1321  *
1322  * @return array[] This will return the updated record MINUS any setting/configuration columns
1323  *
1324  */
1325 function GetDataUpdatedColumnsOnly($pSingleRecord) {
1326 
1327  $ignoreList = array("_action");
1328  $retData = array();
1329 
1330  $retData = array_diff_key($pSingleRecord, array_flip($ignoreList));
1331 
1332  return $retData;
1333 }
1334 
1335 
1336 /**
1337  *
1338  * HomeCU Audit Record Function
1339  * This will insert a record into the <cucode>audituser table.
1340  * It may eventually audit changes for several types of tables. (user, userrights, useraccounts, alert, scheduled)
1341  *
1342  * ** STANDARD PARAMETERS FOR FUNCTIONS
1343  * @param integer $pDbh - Database Handle for current Credit Union Session
1344  * @param array[] $pHbEnv - The current session HB_ENV -- This is the array that contains session/environment variables.
1345  * This function requires the following variables in HB_ENV
1346  * cu - the CU CODE
1347  * @param class $pMc - The current session MC (Multi-Language) class
1348  *
1349  * ** Function Specific Parameters
1350  * ??? @param string pTableName - The table key name that is associated with this change.
1351  * @param integer $pAuditUser - The user id related to the data change
1352  * @param string $pAction - Identifies the action that triggered this audit record. Some codes include
1353  * U_ADD - User Account was added.
1354  * U_DEL_A - User Account Deleted
1355  * U_DEL_H - User History Deleted.
1356  * U_RESET - User Password was reset
1357  * U_UPD - User Account Was updated
1358  * U_OMA - Override MICRs
1359  * U_EST - E-statemnt Flag Changed
1360  * @param string $pAppContext - Application Context {DSK, MBL, APP, ADA}
1361  * @param string $pCodeContext - Name of the PHP script that called this function.
1362  * @param string $pAuditType - The type of user that triggered this audit
1363  * U - user, the user themselves made this particular change
1364  * A - admin user, an admin user made the change to the user account.
1365  * H - HomeCU made the change to the user record
1366  * @param string $pFullDesc - A full description of why the audit record was necessary. {Force Password, Update Profile..etc}
1367  * @param string $pUserName - The User Name value that made the triggered user. The source of this user_name
1368  * depends on the type defined in pAuditSrcType
1369  * @param string $pEmail - The email associated with the user
1370  * @param string $pIp - The source IP address that triggered the audit record
1371  * @param string $pRecBefore - This is a json encoded array of a "snapshot" prior to any changes that will be occurring.
1372  * @param string $pRecAfter - This is a json encoded array of the "delta" values associated with the table
1373  * @param boolean $pBuildSqlOnly - {true,false (default)} -
1374  * True - the sql will not be sent to the database. The sql is returned in the [data][sql] value
1375  * False - The sql will be sent to the database.
1376  *
1377  * @return array[] - This will return a standard return array
1378  * [code] - Base return code {000-success, 999-error}
1379  * [errors] - Indexed array of error descriptions (in case there could be more than one)
1380  * [data] - Extra Data
1381  * [sql] - The SQL that was created will always be returned.
1382  */
1383 function SetAuditRecord ($pDbh, $pHbEnv, $pMc, $pAuditUser, $pAction, $pAppContext, $pCodeContext, $pAuditType, $pFullDesc, $pUserName, $pEmail, $pIp, $pRecBefore, $pRecAfter,
1384  $pBuildSqlOnly = false, $pAccountNumber = "") {
1385 
1386  /**
1387  * @var array[] Return array
1388  */
1389  $retVal = array("code" => "000",
1390  "errors" => array(),
1391  "data" => array());
1392 
1393  try {
1394  /* VALIDATION */
1395  /* CU CODE */
1396  if (!HCU_array_key_exists('cu', $pHbEnv)) {
1397  throw new exception ('Environment Config Error', '900');
1398  } else if (trim($pHbEnv['cu']) == '') {
1399  throw new exception ('CU Config Error', '901');
1400  }
1401 
1402  $insAuditRec = array(
1403  "_action" => 'create',
1404  "user_id" => $pAuditUser,
1405  "auditdate" => DBTIMESTAMP_USETS,
1406  "auditaction" => $pAction,
1407  "auditsrctype" => $pAuditType,
1408  "auditsrcuser_name" => $pUserName,
1409  "auditsrcemail" => $pEmail,
1410  "auditrecbefore" => $pRecBefore,
1411  "auditrecafter" => $pRecAfter,
1412  "auditsrcip" => $pIp,
1413  "auditsrcapp_context" => $pAppContext,
1414  "auditsrccode_context" => $pCodeContext,
1415  "auditfulldesc" => $pFullDesc,
1416  "accountnumber" => $pAccountNumber
1417  );
1418  $updResult = SetDataTableUpdate($pDbh, $pHbEnv, $pMc, "audituser", $insAuditRec, $pBuildSqlOnly);
1419 
1420  if ($updResult) {
1421  // ** Set the Return SQL
1422  $retVal['data']['sql'] = HCU_array_key_value('sql', $updResult['data']);
1423  } else {
1424  throw new exception ('Audit Record Update Error', '903');
1425  }
1426 /*
1427 ** OLD METHOD START**
1428  * CREATE SQL *
1429  $sql = "INSERT INTO " . prep_save($pHbEnv['cu'], 12) . "audituser
1430  (user_id, auditdate, auditaction,
1431  auditsrctype, auditsrcuser_name, auditsrcemail,
1432  auditsrcip, auditsrcapp_context, auditsrccode_context,
1433  auditfulldesc, auditrecbefore, auditrecafter)
1434  VALUES
1435  (" . intval($pAuditUser) . ", now(), '" . prep_save($pAction, 10) . "',
1436  '" . prep_save($pAuditType, 1) . "', '" . prep_save($pUserName, 50) . "', '" . prep_save($pEmail, 255) . "',
1437  '" . prep_save($pIp, 15) . "', '" . prep_save($pAppContext, 10) . "', '" . prep_save($pCodeContext, 20) . "',
1438  '" . prep_save($pFullDesc, 255) . "', '" . prep_save($pRecBefore) . "', '" . prep_save($pRecAfter) . "'
1439  ); ";
1440 
1441  $retVal['data']['sql'] = $sql;
1442  if (!$pBuildSqlOnly) {
1443  * EXECUTE THE QUERY *
1444  // ** Should this evalute the dbh?
1445  if (pg_connection_status($pDbh) == PGSQL_CONNECTION_BAD) {
1446  throw new exception ('Database Connection Interruption', '902');
1447  }
1448 
1449  $queryResult = db_query($sql, $pDbh);
1450  if ($queryResult == false) {
1451  throw new exception ('Audit Record Update Error', '903');
1452  }
1453  }
1454 ** OLD METHOD END **
1455 Set Data Table Update should be handling
1456 */
1457 
1458 
1459  } catch (exception $e){
1460  $retVal['code'] = $e->getCode();
1461  $retVal['error'][] = $e->getMessage();
1462  $retVal['data'] = array();
1463  }
1464 
1465  return $retVal;
1466 }
1467 
1468 /**
1469  * HomeCU Audit Record Function
1470  * This will insert a record into the <cucode>auditadmin table. It may eventually audit changes for several types of tables. (user, userrights, useraccounts, alert, scheduled)
1471  *
1472  * ** STANDARD PARAMETERS FOR FUNCTIONS
1473  * @param integer $pDbh - Database Handle for current Credit Union Session
1474  * @param array[] $pHbEnv - The current session HB_ENV -- This is the array that contains session/environment variables.
1475  * This function requires the following variables in HB_ENV: cu - the CU CODE
1476  *
1477  * @param integer $pAuditUser - The user id related to the data change
1478  * @param string $pAction - Identifies the action that triggered this audit record.
1479  * @param string $pCodeContext - Name of the PHP script that called this function.
1480  * @param string $pAuditType - The type of user that triggered this audit
1481  * A - admin user, an admin user made the change to the admin user account.
1482  * H - HomeCU made the change to the user record
1483  * @param string $pFullDesc - A full description of why the audit record was necessary. {Force Password, Update Profile..etc}
1484  * @param string $pUserName - The User Name value that made the triggered user. The source of this user_name
1485  * depends on the type defined in pAuditSrcType
1486  * @param string $pEmail - The email associated with the user
1487  * @param string $pIp - The source IP address that triggered the audit record
1488  * @param string $pRecBefore - This is a json encoded array of a "snapshot" prior to any changes that will be occurring.
1489  * @param string $pRecAfter - This is a json encoded array of the "delta" values associated with the table
1490  * @param boolean $pBuildSqlOnly - {true,false (default)} -
1491  * True - the sql will not be sent to the database. The sql is returned in the [data][sql] value
1492  * False - The sql will be sent to the database.
1493  *
1494  * @return array[] - This will return a standard return array
1495  * [code] - Base return code {000-success, 999-error}
1496  * [errors] - Indexed array of error descriptions (in case there could be more than one)
1497  * [data] - Extra Data
1498  * [sql] - The SQL that was created will always be returned.
1499  */
1500 function SetAdminAuditRecord ($pDbh, $pHbEnv, $pAuditUser, $pAction, $pCodeContext, $pAuditType, $pFullDesc, $pUserName, $pEmail, $pIp, $pRecBefore, $pRecAfter, $pBuildSqlOnly=false)
1501 {
1502  try
1503  {
1504  if (!HCU_array_key_exists('cu', $pHbEnv))
1505  throw new exception ('Environment Config Error', '900');
1506  if (trim($pHbEnv['cu']) == '')
1507  throw new exception ('CU Config Error', '901');
1508 
1509  $insAuditRec = array (
1510  "_action" => 'create',
1511  "user_name" => $pAuditUser,
1512  "auditdate" => DBTIMESTAMP_USENOW,
1513  "auditaction" => $pAction,
1514  "auditsrctype" => $pAuditType,
1515  "auditsrcuser_name" => $pUserName,
1516  "auditsrcemail" => $pEmail,
1517  "auditrecbefore" => $pRecBefore,
1518  "auditrecafter" => $pRecAfter,
1519  "auditsrcip" => $pIp,
1520  "auditsrccode_context" => $pCodeContext,
1521  "auditfulldesc" => $pFullDesc
1522  );
1523 
1524  $updResult = SetDataTableUpdate($pDbh, $pHbEnv, null, "auditadmin", $insAuditRec, $pBuildSqlOnly);
1525 
1526  if (!$updResult)
1527  throw new exception ('Audit Record Update Error', '903');
1528  }
1529  catch (exception $e)
1530  {
1531  return array("code" => $e->getCode(), "error" => array($e->getMessage()), "data" => array());
1532  }
1533 
1534  return array("code" => "000", "error" => array(), "data" => array());
1535 }
1536 
1537 
1538 /**
1539  *
1540  * This function will create SQL for CRUD from the data arrays.
1541  * The arrays will provide the type of operation (Create, Update, Delete)
1542  *
1543  * This function does NOT need to update the database, it can also be used to build sql
1544  *
1545  *
1546  * ** STANDARD PARAMETERS FOR FUNCTIONS
1547  * @param integer pDbh - Database Handle for current Credit Union Session
1548  * @param array[] pHbEnv - The current session HB_ENV -- This is the array that contains session/environment variables.
1549  * This function requires the following variables in HB_ENV
1550  * cu - the CU CODE
1551  * @param class pMc - The current session MC (Multi-Language) class
1552  *
1553  * ** Function Specific Parameters
1554  * @param string pUpdTableCode - Table "key" for data being updated
1555  * @param array[] pUpdValues - This is the array of data values to be set in the table
1556  * @param boolean pBuildSqlOnly - {true,false (default)} -
1557  * True - the sql will not be sent to the database. The sql is returned in the [data][sql] value
1558  * False - The sql will be sent to the database.
1559  *
1560  * @return array[] - This will return a standard return array
1561  * [code] - Base return code {000-success, 9XX-error}
1562  * [errors] - Indexed array of error descriptions (in case there could be more than one)
1563  * [data] - Extra Data
1564  * [sql] - The SQL that was created will always be returned.
1565  * [row] - This is the returned row for ALL the columns in the table (uses postgres RETURNING *)
1566  */
1567 function SetDataTableUpdate($pDbh, $pHbEnv, $pMC, $pUpdTableCode, $pUpdValues, $pBuildSqlOnly = false) {
1568  /**
1569  * @var array[] Return array
1570  */
1571 
1572  $retVal = array("code" => "000",
1573  "errors" => array(),
1574  "data" => "");
1575  $retRows= array();
1576 
1577  try {
1578 
1579  /**
1580  * Verify the table exists in the Table Definitions
1581  */
1582  $tblSchemaList = GetTableDefinition($pHbEnv, $pUpdTableCode);
1583  if (!HCU_array_key_exists($pUpdTableCode, $tblSchemaList)) {
1584  // * NOT FOUND
1585  throw new Exception("Table Schema Not Found", '901');
1586  }
1587  $tblSchema = $tblSchemaList[$pUpdTableCode];
1588  // Add default values to updValues if doesn't exist
1589  foreach($tblSchema["_cols"] as $col => $colInfo)
1590  {
1591  if (isset($colInfo["defaultValue"]) && !isset($pUpdValues[$col]))
1592  $pUpdValues[$col]= $colInfo["defaultValue"];
1593  }
1594  /**
1595  * VERIFY the _action is defined and set in the pUpdValues array
1596  */
1597  if (!HCU_array_key_exists('_action', $pUpdValues)) {
1598  throw new Exception ("Unknown data action encountered", "902");
1599  }
1600  /**
1601  * Verify the required-columns exist for ALL ACTIONS
1602  * Do this by counting the required-columns elements and compare to the intersection results array
1603  * of the required columns and the Updated Column Values
1604  */
1605 
1606  $reqColumns = HCU_array_key_value("_primary-columns", $tblSchema);
1607  if (count($reqColumns) !== count(array_intersect_key(array_flip($reqColumns), $pUpdValues))) {
1608  throw new Exception ("Required Columns Missing", "903");
1609  }
1610 
1611  /**
1612  *
1613  * CREATE SQL STATEMENT
1614  * Based on Query Action
1615  *
1616  *
1617  */
1618  switch (strtolower($pUpdValues['_action'])) {
1619  /**
1620  * INSERT STATEMENT -- DIFFICULT AS I NEED TO BE ABLE TO USE SERIAL
1621  * It may be possible to leverage the "RETURNING" syntax.
1622  * INSERT INTO table .. RETURNING *
1623  */
1624  case "create":
1625  case "insert":
1626  /**
1627  * Build Value Updates
1628  * This will be all the values in pUpdValues INCLUDING PRIMARY COLUMNS
1629  */
1630  $sqlInsertCols= "";
1631  $sqlInsertVals= "";
1632  foreach ( GetDataUpdatedColumnsOnly($pUpdValues) as $colName => $colValue) {
1633  /**
1634  * colName - This is the column name as defined in the table structure
1635  * colValue - This is the value to be used for updatings
1636  * colAttr - Look this up from the table schema
1637  */
1638  $colAttr = HCU_array_key_value($colName, $tblSchema['_cols']);
1639 
1640  /**
1641  * BUILD THE EXRESSION
1642  */
1643  $sqlInsertCols .= ($sqlInsertCols != '' ? ', ' : '') . $colName;
1644  $sqlInsertVals .= ($sqlInsertVals != '' ? ', ' : '') . db_build_expr_value($colValue, $colAttr);
1645  }
1646 
1647  $retSql = "INSERT INTO {$tblSchema['_tablename']} ($sqlInsertCols) VALUES ($sqlInsertVals) RETURNING " . implode(', ', array_keys(GetDataUpdatedColumnsOnly($pUpdValues))) . "; ";
1648  break;
1649  /**
1650  * UPDATE STATEMENT -- UPDATE FIELDS WHERE PRIMARY MEETS CONDITION
1651  */
1652  case "update":
1653  /**
1654  * Build Value Updates
1655  * This will be all the values in pUpdValues MINUS any _primary-columns
1656  */
1657  // ** get the list of primary columns
1658  $sqlUpdate= "";
1659  foreach ( array_diff_key(GetDataUpdatedColumnsOnly($pUpdValues), array_flip(HCU_array_key_value("_primary-columns", $tblSchema))) as $colName => $colValue) {
1660  /**
1661  * colName - This is the column name as defined in the table structure
1662  * colValue - This is the value to be used for updatings
1663  * colAttr - Look this up from the table schema
1664  */
1665  $colAttr = HCU_array_key_value($colName, $tblSchema['_cols']);
1666 
1667  /**
1668  * BUILD THE EXRESSION
1669  */
1670  $sqlUpdate .= ($sqlUpdate != '' ? ", " : "") . " $colName = " . db_build_expr_value($colValue, $colAttr);
1671  }
1672  // * *BUILD THE WHERE Expression FROM the primary columns -- they should all exist in this instance
1673  $primaryColumns = HCU_array_key_value('_primary-columns', $tblSchema);
1674  $primaryColumns = is_array($primaryColumns) ? array_flip($primaryColumns) : array(); // Ensure the primaryColumns is an array of some sort -- make the values keys
1675  $sqlWhere= "";
1676  foreach (array_keys($primaryColumns) as $colName) {
1677  if (HCU_array_key_exists($colName, $primaryColumns)) {
1678  // Primary column found in list of values --
1679  $colValue = $pUpdValues[$colName];
1680  $colAttr = HCU_array_key_value($colName, $tblSchema['_cols']);
1681 
1682  $sqlWhere .= ($sqlWhere != '' ? " AND " : "") . " $colName = " . db_build_expr_value($colValue, $colAttr);
1683  } else {
1684  // Primary Column was NOT FOUND -- throw an error -- SHOULD HAVE BEEN CAUGHT error 903
1685  }
1686  }
1687 
1688  // ** Require the WHERE statement
1689  $retSql = "UPDATE {$tblSchema['_tablename']} SET $sqlUpdate WHERE $sqlWhere RETURNING " . implode(', ', array_keys(GetDataUpdatedColumnsOnly($pUpdValues))) . "; ";
1690  break;
1691  /**
1692  * DELETE STATEMENT -- DELETE ROWS WHERE PRIMARY MEETS CONDITION
1693  */
1694  case "delete":
1695  /**
1696  * DELETE only REQUIRES the DELETE statement
1697  * This will be all the values in pUpdValues MINUS any _primary-columns
1698  */
1699  // ** get the list of primary columns
1700 
1701  // * *BUILD THE WHERE Expression FROM the primary columns -- they should all exist in this instance
1702  $primaryColumns = HCU_array_key_value('_primary-columns', $tblSchema);
1703  $primaryColumns = is_array($primaryColumns) ? array_flip($primaryColumns) : array(); // Ensure the primaryColumns is an array of some sort -- make the values keys
1704 
1705  $sqlWhere= "";
1706  foreach (array_keys($primaryColumns) as $colName) {
1707  if (HCU_array_key_exists($colName, $primaryColumns)) {
1708  // Primary column found in list of values --
1709  $colValue = $pUpdValues[$colName];
1710  $colAttr = HCU_array_key_value($colName, $tblSchema['_cols']);
1711 
1712  $sqlWhere .= ($sqlWhere != '' ? " AND " : "") . " $colName = " . db_build_expr_value($colValue, $colAttr);
1713  } else {
1714  // Primary Column was NOT FOUND -- throw an error -- SHOULD HAVE BEEN CAUGHT error 903
1715  }
1716  }
1717 
1718  // ** Require the WHERE statement
1719  $retSql = "DELETE FROM {$tblSchema['_tablename']} WHERE $sqlWhere; ";
1720 
1721  break;
1722  default:
1723  throw new Exception ("Unknown Data Action", "904");
1724  }
1725 
1726  if (!$pBuildSqlOnly) {
1727  // * EXECUTE the query
1728  if (strtolower($pUpdValues['_action']) == 'delete') {
1729  // * Handle delete
1730  if (db_query($retSql, $pDbh)) {
1731  $retVal['data']['row'][$pUpdTableCode][] = array();
1732  } else {
1733  /**
1734  * ERROR DURING DB EXECUTION
1735  */
1736  throw new exception ('Database Query Failed', '902');
1737  }
1738  } else {
1739  $retRows[$pUpdTableCode] = GetAllRowsFromSql($pDbh, $retSql);
1740  if ($retRows[$pUpdTableCode] !== false) {
1741  $retVal['data']['row'] = $retRows;
1742  } elseif (db_last_error() != "") {
1743  /**
1744  * ERROR DURING DB EXECUTION
1745  */
1746  throw new exception ('Database Query Failed', '902');
1747  } else {
1748  // ** no error - most likely no update -- return empty row
1749  $retVal['data']['row'] = $retRows;
1750  }
1751  }
1752  }
1753  $retVal['data']['sql'] = $retSql;
1754 
1755  } catch (exception $e){
1756  $retVal['code'] = $e->getCode();
1757  $retVal['errors'][] = $e->getMessage();
1758  }
1759 
1760  return $retVal;
1761 
1762 }
1763 
1764 /**
1765  * Update_User_Settings - Project Odyssey
1766  *
1767  * This function will accept the fields to change and update the tables accordingly
1768  *
1769  * @param integer $p_dbh - database handle
1770  * @param array $p_hb_env - value of the array HB_ENV
1771  * @param class $p_mc - This is the reference to the MC class that describes the currently selected language
1772  * @param array $p_upd_fields - Array of fields that are being updated
1773  * @param bol $p_transaction - Do I need to use a transaction for updating the query
1774  *
1775  */
1776 function Update_User_Settings($p_dbh, $p_hb_env, $p_mc, $p_upd_fields, $p_transaction) {
1777  /* ALLOWED FIELDS with DATATYPE -- MULTI DIMENSION ARRAY
1778  * First Key is the category on where the flag is set
1779  * {settings_email, settings_password, settings_useralias, settings_questions, settings_confidence}
1780  * settings_email
1781  * [email] - 'email' The member email to update
1782  * [egenl] - 'include on email list' Does the member want to be included on the member list?
1783  * [verify] - 'verify email' has the member been flagged to verify the email
1784  * [valid] - 'email valid' Has the member declared this email to be valid
1785  *
1786  * settings_password - 'char (*)' The member password to the save to the dB, in PLAIN form
1787  * [current] - This the current password the member would be entered
1788  * [newpasswd] - This is the members new password
1789  * [confpasswd] - This is the members entry for confirming the password
1790  * [review_guidelines] - {Y, N} - Did the user review the Guidelines?
1791  *
1792  * settings_useralias -
1793  * [username] - 'char (50)' The member alias, used for altnerate login
1794  * [username_confirm] - 'char (50)' A CONFIRMATION of the username the member entered
1795  * [username_required] - {Y, N} Yes / No
1796  *
1797  * settings_questions - 'array' This is an array of the security question answers.
1798  * Each array element should be an array of cqid, display
1799  * These are Named in correlation with retrieving challenge questions
1800  * ['cqid'] - Challenge question ID
1801  * ['display'] - Challenge response
1802  *
1803  * key is the ChallengeQuestionID for the question
1804  * value is the answer given by the member
1805  * settings_confidence -
1806  * [confword] - 'char (20)' The confidence word for the members account
1807  *
1808  */
1809 
1810  $retAry = Array("status" => Array ("code" => "", "severity" => "", "errors" => Array()));
1811 
1812  $whatUpdated = array( "email" => false, "password" => false, "questions" => false,
1813  "alias" => false, "confidence" => false, "old_email" => "",
1814  "new_email" => "" );
1815 
1816  $trans_failed = false;
1817 
1818  $EMAIL_FLAG = GetMsgTxValue('MSGTX_FORCE_EM');
1819 
1820  /**
1821  * 4/8/2016
1822  *
1823  * In 'mammoth' the ability to create a postgres "TRANSACTION" was added to ensure data integrity and to fail if any part failed.
1824  * However, if I can put all the changes into ONE update then it would achieve the same thing.
1825  * This may be possible if I call the DataUserTableUpdate function instead of the individual Update Functions
1826  *
1827  */
1828 
1829  /**
1830  *
1831  * This function is really a layer
1832  * between the Validate_Settings scripts and the DataUserTableUpdate function
1833  *
1834  */
1835 
1836 
1837  /**
1838  * SNAPSHOT HANDLED BY THE DataUserTableUpdate function
1839  */
1840 
1841  $userDataRecordChanges = Array("_action" => "update",
1842  "user_id" => $p_hb_env['Uid'],
1843  "user_name" => $p_hb_env['Cn']
1844  );
1845 
1846  // Set the contact if it exists.
1847  HCU_array_key_exists("settings_contact", $p_upd_fields) ? $userDataRecordChanges["contact"] = $p_upd_fields["settings_contact"] : null;
1848 
1849  // Set the mfa date if it exists.
1850  HCU_array_key_exists("settings_mfadate", $p_upd_fields) ? $userDataRecordChanges["mfaquest"] = $p_upd_fields["settings_mfadate"] : null;
1851 
1852  // ** EMAIL
1853  // ** egenl flag for Include on E-mail list
1854  if (isset($p_upd_fields['settings_email']) && !$trans_failed) {
1855  // ** Email needs to be updated
1856 // ** $trans_failed = Update_SettingsEmail($p_dbh, $p_hb_env, $p_upd_fields['settings_email'], $p_transaction, $sBuildSql);
1857 
1858  if ( !$trans_failed && strcmp( $p_hb_env["Ml"], $p_upd_fields['settings_email']["email"] ) != 0 ) {
1859  $whatUpdated["email"] = true;
1860 
1861  // save the new email
1862  $whatUpdated["new_email"] = $p_upd_fields["settings_email"]["email"];
1863 
1864  // save the old email
1865  $whatUpdated["old_email"] = $p_hb_env["Ml"];
1866  }
1867 
1868  /**
1869  * SETUP EMAIL FIELDS TO SAVE
1870  * consider 'verify' and 'valid' options from the previous
1871  * no need to add extra logic in lower function. if they are to be changed then change them
1872  * ALSO need to keep track of information being modified to later notify the member
1873  *
1874  */
1875  $userDataRecordChanges['email'] = $p_upd_fields["settings_email"]["email"];
1876  $userDataRecordChanges['egenl_flag'] = $p_upd_fields["settings_email"]["egenl"];
1877 
1878  /**
1879  * Verify e-mail
1880  * Verify e-mail is set to force the member to change their password. However, the flag does not always 'require' the member to change the email
1881  * The
1882  */
1883  if (HCU_array_key_exists('verify', $p_upd_fields['settings_email'])) {
1884  // * Did the member tag the e-mail as 'valid'??
1885  // * IF tagged as YES -- THEN UPDATE msg_tx
1886 
1887  // * IF not TAGGED, then only update if the email being saved is different
1888  $curEmail = $p_hb_env['Ml'];
1889  if (($curEmail != $p_upd_fields["settings_email"]["email"]) || (HCU_array_key_value('valid', $p_upd_fields["settings_email"]) == "Y")) {
1890  // ** Subtract out the Temp flag bits FROM the current Fmsg_tx
1891  $tmpVal = ~(~(int)$p_hb_env['Fmsg_tx'] | HCUTEMP_FLAGS);
1892 
1893  // ** Subtract out the Email Settings Flag
1894  $tmpVal = ~(~$tmpVal | $EMAIL_FLAG);
1895 
1896  $userDataRecordChanges['msg_tx'] = $tmpVal;
1897  }
1898  }
1899 
1900  }
1901  /**
1902  * UPDATE PASSWORD
1903  */
1904  if (isset($p_upd_fields['settings_password']) && !$trans_failed) {
1905  /**
1906  * Only Update the password IF newpasswd is set
1907  * ** Otherwise, the structure may exist to confirm the current password 'current'
1908  */
1909  if (HCU_array_key_exists('newpasswd', $p_upd_fields['settings_password'])) {
1910  $userDataRecordChanges['passwd'] = password_hash($p_upd_fields['settings_password']['newpasswd'], PASSWORD_DEFAULT);
1911  $userDataRecordChanges['forcechange'] = 'N';
1912  $userDataRecordChanges['pwchange'] = DBTIMESTAMP_USENOW;
1913 
1914  $whatUpdated["password"] = true;
1915  }
1916  }
1917 
1918  /**
1919  * UPDATE ALIAS
1920  */
1921  if (isset($p_upd_fields['settings_alias']) && !$trans_failed) {
1922  $userDataRecordChanges['user_name'] = $p_upd_fields['settings_alias']['username'];
1923 
1924  $whatUpdated["alias"] = true;
1925  }
1926 
1927  /**
1928  * CONFIDENCE WORD
1929  */
1930  if (isset($p_upd_fields['settings_confidence']) && !$trans_failed) {
1931  $userDataRecordChanges['confidence'] = $p_upd_fields['settings_confidence']['confword'];
1932 
1933  if (HCU_array_key_exists('userflags', $p_upd_fields['settings_confidence'])) {
1934  $userDataRecordChanges['userflags'] = $p_upd_fields['settings_confidence']['userflags'];
1935  }
1936 
1937  $whatUpdated["confidence"] = true;
1938  }
1939 
1940  /**
1941  * SECURITY QUESTIONS
1942  */
1943  if (isset($p_upd_fields['settings_questions']) && !$trans_failed) {
1944  # note mfaquest array may contain Array("answers", "challenge", "authcode", "authexpires", "mfadate");
1945  # this will throw away everything and only save the answers - and now I'm adding the mfadate
1946  $mfaAnswers = Array();
1947  if (is_array($p_upd_fields['settings_questions'])) {
1948  // ** Iterate through the questions and create new mfa json value
1949  for ($qIdx = 0; $qIdx < count($p_upd_fields['settings_questions']); $qIdx++) {
1950  $mfaAnswers[$p_upd_fields['settings_questions'][$qIdx]['cqid']] = $p_upd_fields['settings_questions'][$qIdx]['display'];
1951  }
1952  }
1953  $mfaArray = Array("answers" => $mfaAnswers);
1954  $mfaArray['mfadate'] = time();
1955  $userDataRecordChanges['mfaquest'] = PrepareMfaQuestString($mfaArray);
1956 
1957  $whatUpdated["questions"] = true;
1958  }
1959 
1960 // /**
1961 // * ACCOUNT DESCRIPTIONS
1962 // * ** NOT COMPLETE MWS 4/11/2016
1963 // */
1964 
1965  $updateTableArray = Array("user" => Array($userDataRecordChanges));
1966 
1967  // * Start the transaction
1968  if (!($trans_rs = db_query("begin transaction", $p_dbh))) {
1969  $trans_failed = true;
1970  }
1971 
1972  if ( !$trans_failed ) {
1973 
1974  // * Update the records -- ONLY SAVE AUDIT RECORD IF UPDATING EMAIL
1975  $updateResults = DataUserTableUpdate($p_dbh, $p_hb_env, $p_mc, $updateTableArray, $p_hb_env['Uid'], 'U_UPD', 'U', $p_hb_env['currentscript'], 'U', "Required Profile Update", $p_hb_env['Cn'], $p_hb_env['Ml'], $p_hb_env['remoteIp']);
1976  if ( !$updateResults ) {
1977  $trans_failed = true;
1978  }
1979  }
1980 
1981  // * EITHER POST THE ROLLBACK OR COMMIT
1982  if ($trans_failed) {
1983  // ** UPDATE FAILED -- ROLLBACK
1984  $upd_rs = db_query("rollback transaction", $p_dbh);
1985 
1986  $retAry['status']['code'] = '999';
1987  $retAry['status']['severity'] = 'ERROR';
1988  $retAry['status']['errors'][] = $p_mc->msg("Error Occurred updating settings");
1989  } else {
1990  // ** UPDATE SUCCEEDED-- COMMIT
1991  if (!$upd_rs = db_query("commit transaction", $p_dbh)) {
1992  // ** COMMIT FAILED
1993  $retAry['status']['code'] = '999';
1994  $retAry['status']['severity'] = 'ERROR';
1995  $retAry['status']['errors'][] = $p_mc->msg("Error Occurred updating settings");
1996  } else {
1997  // send an email to the registered user to let them know a change occurred
1998  NotifyUserOfChange( $p_dbh, $p_hb_env, $whatUpdated, $p_mc );
1999 
2000  // return success
2001  $retAry['status']['code'] = '000';
2002  $retAry['status']['severity'] = "SUCCESS";
2003  }
2004  }
2005 
2006  /**
2007  *
2008  * END OF NEW CODE -- FOLLOWING CODE SHOULD BE REMOVED
2009  *
2010  */
2011 
2012 /*
2013 // $PostOrBuild = ($p_transaction ? "POST" : "BUILD"); // ** THIS WILL TELL US WHETHER WE POST OR BUILD AN SQL
2014  // * Build is used for a one sql call at the end of this function
2015  // * all sql queries will be appended together
2016  // * Post is used for a 'sql transaction' query , each query is separtely
2017  // * executed and at the end , either a rollback or commit will be executed
2018  $sBuildSql = ""; // This is the accumulated sql that will be executed at the end
2019 
2020  // ** DECLARE SETTINGS FOR THIS FUNCTION
2021 
2022  // * GET THE CURRENT dB time
2023  if ($time_rs = db_query("select now();", $p_dbh)) {
2024  list($chnow) = db_fetch_array($time_rs, 0);
2025  } else {
2026  // ** For some reason unable to get the current time
2027  $trans_failed = true;
2028  }
2029 
2030  // * Start the transaction if needed
2031  if ($p_transaction && !$trans_failed) {
2032  if (!($trans_rs = db_query("begin transaction", $p_dbh))) {
2033  $trans_failed = true;
2034  }
2035  }
2036 
2037  // save the old email
2038  $whatUpdated["old_email"] = $p_hb_env["Ml"];
2039 
2040  // ** SNAPSHOT OF AUDIT USERS
2041  $trans_failed = Update_AuditMember($p_dbh, $p_hb_env, 0, $chnow, $p_transaction, $sBuildSql);
2042 
2043  // ** EMAIL
2044  // ** egenl flag for Include on E-mail list
2045  if (isset($p_upd_fields['settings_email']) && !$trans_failed) {
2046  // ** Email needs to be updated
2047  $trans_failed = Update_SettingsEmail($p_dbh, $p_hb_env, $p_upd_fields['settings_email'], $p_transaction, $sBuildSql);
2048 
2049  if ( !$trans_failed && strcmp( $p_hb_env["Ml"], $p_upd_fields['settings_email']["email"] ) != 0 ) {
2050  $whatUpdated["email"] = true;
2051 
2052  // save the new email
2053  $whatUpdated["new_email"] = $p_upd_fields["settings_email"]["email"];
2054  }
2055  }
2056 
2057  // ** PASSWORD
2058  if (isset($p_upd_fields['settings_password']) && !$trans_failed) {
2059  // ** CRYPT THE PASSWORD FOR SAVING
2060  $trans_failed = Update_SettingsPassword($p_dbh, $p_hb_env, $p_upd_fields['settings_password'], $p_transaction, $sBuildSql);
2061 
2062  if ( !$trans_failed && array_key_exists('newpasswd', $p_upd_fields['settings_password']) ) {
2063  $whatUpdated["password"] = true;
2064  }
2065  }
2066 
2067 
2068  // ** USER ALIAS
2069  if (isset($p_upd_fields['settings_alias']) && !$trans_failed) {
2070  $trans_failed = Update_SettingsAlias($p_dbh, $p_hb_env, $p_upd_fields['settings_alias'], $p_transaction, $sBuildSql);
2071 
2072  if ( !$trans_failed ) {
2073  $whatUpdated["alias"] = true;
2074  }
2075  }
2076 
2077  // ** CONFIDENCE WORD
2078  if (isset($p_upd_fields['settings_confidence']) && !$trans_failed) {
2079  $trans_failed = Update_SettingsConfidence($p_dbh, $p_hb_env, $p_upd_fields['settings_confidence'], $p_transaction, $sBuildSql);
2080 
2081  if ( !$trans_failed ) {
2082  $whatUpdated["confidence"] = true;
2083  }
2084  }
2085 
2086  // ** SECURITY QUESTIONS
2087  if (isset($p_upd_fields['settings_questions']) && !$trans_failed) {
2088  // ** Create the update query for challenge questions
2089  $trans_failed = Update_SettingsChallenge($p_dbh, $p_hb_env, $p_upd_fields['settings_questions'], $p_transaction, $sBuildSql);
2090 
2091  if ( !$trans_failed ) {
2092  $whatUpdated["questions"] = true;
2093  }
2094  }
2095 
2096  // ** ACCOUNT DESCRIPTIONS
2097  if (isset($p_upd_fields['settings_desc']) && !$trans_failed) {
2098  // ** Create the update query for challenge questions
2099  $trans_failed = Update_SettingsDesc($p_dbh, $p_hb_env, $p_upd_fields['settings_desc'], $p_transaction, $sBuildSql);
2100  }
2101 
2102  // ** ONE FOR CLEARING FORCE_RESET?? -- CURRENTLY -- CONFIDENCE WORD UPDATE WILL CLEAR FORCE RESET
2103 
2104  // ** TAKE ANOTHER SNAPSHOT OF THE MEMBER
2105  $trans_failed = Update_AuditMember($p_dbh, $p_hb_env, 1, $chnow, $p_transaction, $sBuildSql);
2106 */
2107 /*
2108 HOW TO HANDLE UPDATE QUERY
2109 
2110  // END THE PROCESS
2111  if ($p_transaction) {
2112  // * EITHER POST THE ROLLBACK OR COMMIT
2113  if ($trans_failed) {
2114  // ** UPDATE FAILED -- ROLLBACK
2115  $upd_rs = db_query("rollback transaction", $p_dbh);
2116 
2117  $retAry['status']['code'] = '999';
2118  $retAry['status']['severity'] = 'ERROR';
2119  $retAry['status']['errors'][] = $p_mc->msg("Error Occurred updating settings");
2120  } else {
2121  // ** UPDATE SUCCEEDED-- COMMIT
2122  if (!$upd_rs = db_query("commit transaction", $p_dbh)) {
2123  // ** COMMIT FAILED
2124  $retAry['status']['code'] = '999';
2125  $retAry['status']['severity'] = 'ERROR';
2126  $retAry['status']['errors'][] = $p_mc->msg("Error Occurred updating settings");
2127  } else {
2128  // send an email to the registered user to let them know a change occurred
2129  NotifyUserOfChange( $p_dbh, $p_hb_env, $whatUpdated, $p_mc );
2130 
2131  // return success
2132  $retAry['status']['code'] = '000';
2133  $retAry['status']['severity'] = "SUCCESS";
2134  }
2135  }
2136  } else {
2137 
2138 print "<br /><br />PRINT SQL<br />". $sBuildSql;
2139  // * WE NOW POST THE ACCUMULATED SQL
2140  if ($upd_rs = db_query($sBuildSql, $p_dbh)) {
2141  // send an email to the registered user to let them know a change occurred
2142 // MWS TEMP REMOVE NotifyUserOfChange( $p_dbh, $p_hb_env, $whatUpdated, $p_mc );
2143 
2144  // return success
2145  $retAry['status']['code'] = '000';
2146  $retAry['status']['severity'] = "SUCCESS";
2147  } else {
2148  // ** RECORD ERROR HERE
2149  $retAry['status']['code'] = '999';
2150  $retAry['status']['severity'] = 'ERROR';
2151  $retAry['status']['errors'][] = $p_mc->msg("Error Occurred updating settings");
2152  }
2153  }
2154 */
2155 
2156  // ** RETURN THE status array
2157  return $retAry;
2158 }
2159 
2160 function Update_User_Security($p_dbh, $p_hb_env, $p_mc, &$p_upd_fields) {
2161  try {
2162  $securityPhones = HCU_JsonEncode($p_upd_fields['settings_phones']);
2163  $shones = prep_save($securityPhones);
2164  $shones = trim($securityPhones);
2165 
2166  // check if contact record exists for the user
2167  // this will determine update or create.
2168  $sql = "SELECT contact, mfaquest FROM {$p_hb_env['Cu']}user WHERE user_name = '{$p_hb_env['Cn']}'";
2169  $sqlRs = db_query($sql, $p_dbh);
2170  if (!$sqlRs) {
2171  throw new Exception("Error reading user security settings.", 100);
2172  }
2173 
2174  $sqlData = db_fetch_assoc($sqlRs, 0);
2175  $sqlContact = intval($sqlData['contact']);
2176  $sqlMfaQuest = HCU_JsonDecode($sqlData['mfaquest']);
2177 
2178  // update mfadate in mfaquest column
2179  $sqlMfaQuest['mfadate'] = time();
2180  $sqlMfaQuest = HCU_JsonEncode($sqlMfaQuest);
2181 
2182  $p_upd_fields ["settings_mfadate"] = $sqlMfaQuest; // Send to the audit for the user table.
2183 
2184  $contactUpdate = array("phones" => $securityPhones);
2185 
2186  if ($sqlContact === 0) { // Will need to get the next value
2187  $sql = "select nextval('" . $p_hb_env['Cu'] . "usercontact_contact_id_seq'::regclass)";
2188  $sth = db_query($sql, $p_dbh);
2189  if (!$sth) {
2190  throw new exception("Error getting next contact id.", 101);
2191  }
2192 
2193  $sqlContact = db_fetch_row($sth, 0);
2194  $sqlContact = intval($sqlContact[0]);
2195  $contactUpdate["_action"] = "insert";
2196  $contactUpdate["contact_id"] = $sqlContact;
2197  } else { // Otherwise, update the contact record for the contact_id.
2198  $contactUpdate["_action"] = "update";
2199  $contactUpdate["contact_id"] = $sqlContact;
2200  }
2201 
2202  $p_upd_fields ["settings_contact"] = $sqlContact; // Send to the audit for the user table.
2203 
2204  $userUpdate = array("usercontact" => array($contactUpdate));
2205 
2206  if (!DataUserTableUpdate($p_dbh, $p_hb_env, $p_mc, $userUpdate, $p_hb_env['Uid'], 'U_UPD', 'U', $p_hb_env['currentscript'], 'U', "Update User Security", $p_hb_env['Cn'],
2207  $p_hb_env['Ml'], $p_hb_env['remoteIp'])) {
2208  throw new exception("User Update Failed.", 103);
2209  }
2210 
2211  // Prepare and send the return array structure like before.
2212  $returnArray = array("status" => array("code" => "000", "severity" => "SUCCESS", "errors" => array()));
2213  } catch (exception $e) {
2214  $returnArray = array("status" => array("code" => "999", "severity" => "ERROR", "errors" => array($e->getMessage())));
2215  }
2216 
2217  return $returnArray;
2218 }
2219 
2220 /**
2221  * Update_Settings
2222  * This function will evaluate the
2223  *
2224  * @param integer $p_dbh - database handle
2225  * @param array $p_hb_env - value of the array HB_ENV
2226  * @param array $p_upd_fields - Array of fields that are being updated
2227  * @param bol $p_transaction - Do I need to use a transaction for updating the query
2228  * @param class $p_mc - This is the reference to the MC class that describes the currently selected language
2229  *
2230  */
2231 function DEPRECATED_Update_Settings($p_dbh, $p_hb_env, $p_upd_fields, $p_transaction, $p_mc) {
2232 
2233  /* ALLOWED FIELDS with DATATYPE -- MULTI DIMENSION ARRAY
2234  * First Key is the category on where the flag is set
2235  * {settings_email, settings_password, settings_useralias, settings_questions, settings_confidence}
2236  * settings_email
2237  * [email] - 'email' The member email to update
2238  * [egenl] - 'include on email list' Does the member want to be included on the member list?
2239  * [verify] - 'verify email' has the member been flagged to verify the email
2240  * [valid] - 'email valid' Has the member declared this email to be valid
2241  *
2242  * settings_password - 'char (*)' The member password to the save to the dB, in PLAIN form
2243  * [current] - This the current password the member would be entered
2244  * [newpasswd] - This is the members new password
2245  * [confpasswd] - This is the members entry for confirming the password
2246  * [review_guidelines] - {Y, N} - Did the user review the Guidelines?
2247  *
2248  * settings_useralias -
2249  * [username] - 'char (50)' The member alias, used for altnerate login
2250  * [username_confirm] - 'char (50)' A CONFIRMATION of the username the member entered
2251  * [username_required] - {Y, N} Yes / No
2252  *
2253  * settings_questions - 'array' This is an array of the security question answers.
2254  * Each array element should be an array of cqid, display
2255  * These are Named in correlation with retrieving challenge questions
2256  * ['cqid'] - Challenge question ID
2257  * ['display'] - Challenge response
2258  *
2259  * key is the ChallengeQuestionID for the question
2260  * value is the answer given by the member
2261  * settings_confidence -
2262  * [confword] - 'char (20)' The confidence word for the members account
2263  *
2264  */
2265 
2266  $retAry = Array("status" => Array ("code" => "", "severity" => "", "errors" => Array()));
2267 
2268  $whatUpdated = array( "email" => false, "password" => false, "questions" => false,
2269  "alias" => false, "confidence" => false, "old_email" => "",
2270  "new_email" => "" );
2271 
2272  $trans_failed = false;
2273 
2274 // $PostOrBuild = ($p_transaction ? "POST" : "BUILD"); // ** THIS WILL TELL US WHETHER WE POST OR BUILD AN SQL
2275  // * Build is used for a one sql call at the end of this function
2276  // * all sql queries will be appended together
2277  // * Post is used for a 'sql transaction' query , each query is separtely
2278  // * executed and at the end , either a rollback or commit will be executed
2279  $sBuildSql = ""; // This is the accumulated sql that will be executed at the end
2280 
2281  // ** DECLARE SETTINGS FOR THIS FUNCTION
2282 
2283  // * GET THE CURRENT dB time
2284  if ($time_rs = db_query("select now();", $p_dbh)) {
2285  list($chnow) = db_fetch_array($time_rs, 0);
2286  } else {
2287  // ** For some reason unable to get the current time
2288  $trans_failed = true;
2289  }
2290 
2291  // * Start the transaction if needed
2292  if ($p_transaction && !$trans_failed) {
2293  if (!($trans_rs = db_query("begin transaction", $p_dbh))) {
2294  $trans_failed = true;
2295  }
2296  }
2297 
2298  // save the old email
2299  $whatUpdated["old_email"] = $p_hb_env["Ml"];
2300 
2301  // ** SNAPSHOT OF AUDIT USERS
2302  $trans_failed = Update_AuditMember($p_dbh, $p_hb_env, 0, $chnow, $p_transaction, $sBuildSql);
2303 
2304  // ** EMAIL
2305  // ** egenl flag for Include on E-mail list
2306  if (isset($p_upd_fields['settings_email']) && !$trans_failed) {
2307  // ** Email needs to be updated
2308  $trans_failed = Update_SettingsEmail($p_dbh, $p_hb_env, $p_upd_fields['settings_email'], $p_transaction, $sBuildSql);
2309 
2310  if ( !$trans_failed && strcmp( $p_hb_env["Ml"], $p_upd_fields['settings_email']["email"] ) != 0 ) {
2311  $whatUpdated["email"] = true;
2312 
2313  // save the new email
2314  $whatUpdated["new_email"] = $p_upd_fields["settings_email"]["email"];
2315  }
2316  }
2317 
2318  // ** PASSWORD
2319  if (isset($p_upd_fields['settings_password']) && !$trans_failed) {
2320  // ** CRYPT THE PASSWORD FOR SAVING
2321  $trans_failed = Update_SettingsPassword($p_dbh, $p_hb_env, $p_upd_fields['settings_password'], $p_transaction, $sBuildSql);
2322 
2323  if ( !$trans_failed && array_key_exists('newpasswd', $p_upd_fields['settings_password']) ) {
2324  $whatUpdated["password"] = true;
2325  }
2326  }
2327 
2328 
2329  // ** USER ALIAS
2330  if (isset($p_upd_fields['settings_alias']) && !$trans_failed) {
2331  $trans_failed = Update_SettingsAlias($p_dbh, $p_hb_env, $p_upd_fields['settings_alias'], $p_transaction, $sBuildSql);
2332 
2333  if ( !$trans_failed ) {
2334  $whatUpdated["alias"] = true;
2335  }
2336  }
2337 
2338  // ** CONFIDENCE WORD
2339  if (isset($p_upd_fields['settings_confidence']) && !$trans_failed) {
2340  $trans_failed = Update_SettingsConfidence($p_dbh, $p_hb_env, $p_upd_fields['settings_confidence'], $p_transaction, $sBuildSql);
2341 
2342  if ( !$trans_failed ) {
2343  $whatUpdated["confidence"] = true;
2344  }
2345  }
2346 
2347  // ** SECURITY QUESTIONS
2348  if (isset($p_upd_fields['settings_questions']) && !$trans_failed) {
2349  // ** Create the update query for challenge questions
2350  $trans_failed = Update_SettingsChallenge($p_dbh, $p_hb_env, $p_upd_fields['settings_questions'], $p_transaction, $sBuildSql);
2351 
2352  if ( !$trans_failed ) {
2353  $whatUpdated["questions"] = true;
2354  }
2355  }
2356 
2357  // ** ACCOUNT DESCRIPTIONS
2358  if (isset($p_upd_fields['settings_desc']) && !$trans_failed) {
2359  // ** Create the update query for challenge questions
2360  $trans_failed = Update_SettingsDesc($p_dbh, $p_hb_env, $p_upd_fields['settings_desc'], $p_transaction, $sBuildSql);
2361  }
2362 
2363  // ** ONE FOR CLEARING FORCE_RESET?? -- CURRENTLY -- CONFIDENCE WORD UPDATE WILL CLEAR FORCE RESET
2364 
2365  // ** TAKE ANOTHER SNAPSHOT OF THE MEMBER
2366  $trans_failed = Update_AuditMember($p_dbh, $p_hb_env, 1, $chnow, $p_transaction, $sBuildSql);
2367 
2368 
2369  // END THE PROCESS
2370  if ($p_transaction) {
2371  // * EITHER POST THE ROLLBACK OR COMMIT
2372  if ($trans_failed) {
2373  // ** UPDATE FAILED -- ROLLBACK
2374  $upd_rs = db_query("rollback transaction", $p_dbh);
2375 
2376  $retAry['status']['code'] = '999';
2377  $retAry['status']['severity'] = 'ERROR';
2378  $retAry['status']['errors'][] = $p_mc->msg("Error Occurred updating settings");
2379  } else {
2380  // ** UPDATE SUCCEEDED-- COMMIT
2381  if (!$upd_rs = db_query("commit transaction", $p_dbh)) {
2382  // ** COMMIT FAILED
2383  $retAry['status']['code'] = '999';
2384  $retAry['status']['severity'] = 'ERROR';
2385  $retAry['status']['errors'][] = $p_mc->msg("Error Occurred updating settings");
2386  } else {
2387  // send an email to the registered user to let them know a change occurred
2388  NotifyUserOfChange( $p_dbh, $p_hb_env, $whatUpdated, $p_mc );
2389 
2390  // return success
2391  $retAry['status']['code'] = '000';
2392  $retAry['status']['severity'] = "SUCCESS";
2393  }
2394  }
2395  } else {
2396 
2397  // * WE NOW POST THE ACCUMULATED SQL
2398  if ($upd_rs = db_query($sBuildSql, $p_dbh)) {
2399  // send an email to the registered user to let them know a change occurred
2400  NotifyUserOfChange( $p_dbh, $p_hb_env, $whatUpdated, $p_mc );
2401 
2402  // return success
2403  $retAry['status']['code'] = '000';
2404  $retAry['status']['severity'] = "SUCCESS";
2405  } else {
2406  // ** RECORD ERROR HERE
2407  $retAry['status']['code'] = '999';
2408  $retAry['status']['severity'] = 'ERROR';
2409  $retAry['status']['errors'][] = $p_mc->msg("Error Occurred updating settings");
2410  }
2411  }
2412 
2413 
2414 
2415  // ** RETURN THE status array
2416  return $retAry;
2417 }
2418 
2419 // This is a function that silently notifies the user of any changes to security settings. The
2420 // $whatUpdated variable has the old email address as well as flags for what might have been updated.
2421 // If the email was updated send the notice to both the old and new email address.
2422 function NotifyUserOfChange( $p_dbh, $p_hb_env, $whatUpdated, $p_mc ) {
2423  // make sure we have an email address
2424  $email = $whatUpdated["old_email"];
2425 
2426  if ( strlen( $email ) > 0 ) {
2427  // get the organization and product name
2428  $Cu = $p_hb_env["Cu"];
2429 
2430  // get the "From" email address
2431  $sql= "select email from cuadmnotify
2432  where role='securitychgfrom' and cu='$Cu'";
2433  $sth = db_query($sql,$p_dbh);
2434  list($admFrom) = db_fetch_array($sth,0);
2435  $admFrom = preg_replace("/ +$/","",$admFrom);
2436  $admFrom = trim( $admFrom );
2437  db_free_result($sth);
2438 
2439  if ( strlen( $admFrom ) > 0 ) {
2440  // build the notice string that explains what changed
2441  $reasonString = "";
2442 
2443  if ( $whatUpdated["email"] ) {
2444  $reasonString .= "<li>{$p_mc->msg( "Email Address" )}</li>\n";
2445  }
2446  if ( $whatUpdated["password"] ) {
2447  $reasonString .= "<li>{$p_mc->msg( "Password" )}</li>\n";
2448  }
2449  if ( $whatUpdated["questions"] ) {
2450  $reasonString .= "<li>{$p_mc->msg( "Updated Questions" )}</li>\n";
2451  }
2452  if ( $whatUpdated["alias"] ) {
2453  $reasonString .= "<li>{$p_mc->msg( "Username" )}</li>\n";
2454  }
2455  if ( $whatUpdated["confidence"] ) {
2456  $reasonString .= "<li>{$p_mc->msg( "Updated Confidence" )}</li>\n";
2457  }
2458 
2459  if ( strlen( $reasonString ) > 0 ) {
2460  $reasonReply = "<ul>$reasonString</ul>";
2461 
2462  $sql = "select orgname, pname from cuadmin where cu='$Cu'";
2463  $sth = db_query($sql,$p_dbh);
2464  if($sth) { list( $orgName, $pName ) = db_fetch_array($sth,0); }
2465  $orgName = trim( $orgName );
2466  $pName = trim( $pName );
2467 
2468  // build the subject
2469  $msgSubject = $p_mc->combo_msg( "Updated Settings Subject", HCU_DISPLAY_AS_HTML, "#ORGNAME#", $orgName );
2470 
2471  // build the message body
2472  $searchArray = array( "#ORGNAME#", "#PRODNAME#", "#REASON#" );
2473  $replaceArray = array( "$orgName", "$pName", $reasonReply );
2474  $msgBody = $p_mc->combo_msg( "Updated Settings Message", HCU_DISPLAY_AS_HTML, $searchArray, $replaceArray );
2475 
2476  // mail to the user
2477  $notify = new ErrorMail;
2478  $notify->header = "Content-type: text/html";
2479  $notify->mailto = $email;
2480  $notify->mailfromname = $orgName;
2481  $notify->replyto = $admFrom;
2482  $notify->subject = $msgSubject;
2483  $notify->msgbody = "$msgBody";
2484  $notify->callingfunction = __FUNCTION__;
2485  $notify->file = __FILE__;
2486  $notify->cu = $Cu;
2487 
2488  $notify->SendMail();
2489 
2490  // if updated email also mail to new address
2491  if ( $whatUpdated["email"] && strlen( $whatUpdated["new_email"] ) > 0 ) {
2492  $notify->mailto = $whatUpdated["new_email"];
2493 
2494  $notify->SendMail();
2495  }
2496  }
2497  }
2498  }
2499 } // end NotifyUserOfChange
2500 
2501 /**
2502  *
2503  * Update_SettingsEmail
2504  *
2505  * Create the sql queries to update the email and all the pieces that may also be saved
2506  *
2507  *
2508  * @param type $p_dbh
2509  * @param type $p_hb_env
2510  * @param type $p_values
2511  * @param bool $p_transaction
2512  * @param string $p_build_sql -- REFERENCE --
2513  */
2514 function DEPRECATED_Update_SettingsEmail($p_dbh, $p_hb_env, $p_values, $p_transaction, &$p_build_sql) {
2515 
2516  $retFuncFailed = false; // **Did the query execute as expected
2517 // global $EMAIL_FLAG;
2518  $EMAIL_FLAG = GetMsgTxValue('MSGTX_FORCE_EM');
2519 
2520  // ** VERIFY EMAIL IS A SEPARATE QUERY FROM THE NORMAL EMAIL UPDATE
2521  if (array_key_exists('verify', $p_values)) {
2522 
2523  $sql = "UPDATE cuusers
2524  SET msg_tx = (coalesce(msg_tx,0)::int # $EMAIL_FLAG)::int2
2525  WHERE cu = '{$p_hb_env['Cu']}'
2526  AND lower(user_name) = '" . strtolower($p_hb_env['Cn']) . "'
2527  AND (coalesce(msg_tx, 0)::int & $EMAIL_FLAG)::int2 = $EMAIL_FLAG ";
2528  if ($p_values['valid'] != "Y") {
2529  $sql .= " AND email <> '" . prep_save($p_values['email'], 50) . "' ";
2530  }
2531  $sql .= "; "; // END SQL STATEMENT
2532 
2533  if ($p_transaction) {
2534  // * TRANSACTION -- IMMEDIATELY POST
2535  if (!$trans_rs = db_query($sql, $p_dbh)) {
2536  $retFuncFailed = true;
2537  }
2538  } else {
2539  $p_build_sql .= $sql;
2540  }
2541 
2542  }
2543 
2544  // ** UPDATE THE OTEHR EMAIL VALUES
2545 
2546  $sql .= "UPDATE cuusers
2547  SET email='" . prep_save($p_values['email'], 50) . "' ";
2548  if (array_key_exists('egenl', $p_values)) {
2549  $egenl = (strtoupper($p_values['egenl'])=="Y" ? "Y" : "N");
2550  $sql .= ", egenl_flag = '$egenl' ";
2551  }
2552  $sql .= "WHERE user_name='{$p_hb_env['Cn']}' and cu='{$p_hb_env['Cu']}'; ";
2553 
2554  if ($p_transaction) {
2555  if (!$trans_rs = db_query($sql, $p_dbh)) {
2556  $retFuncFailed = true;
2557  }
2558 
2559  } else {
2560  $p_build_sql .= $sql;
2561  }
2562 
2563  return $retFuncFailed;
2564 }
2565 
2566 /**
2567  *
2568  * Update_SettingsPassword
2569  *
2570  * Create the sql queries to update the password
2571  *
2572  *
2573  * @param type $p_dbh
2574  * @param type $p_hb_env
2575  * @param type $p_values
2576  * @param bool $p_transaction
2577  * @param string $p_build_sql -- REFERENCE --
2578  */
2579 function DEPRECATED_Update_SettingsPassword($p_dbh, $p_hb_env, $p_values, $p_transaction, &$p_build_sql) {
2580 
2581  $retFuncFailed = false; // **Did the query execute as expected
2582 
2583  // ** ONLY UPDATE IF newpasswd is defined in the p_values array
2584  // ** That way I may be able to use the current password as part of valiation and NO updated will occur
2585  if (array_key_exists('newpasswd', $p_values)) {
2586  // ** Create the password hash
2587  $hash = crypt($p_values['newpasswd']);
2588 
2589  $sql = "UPDATE cuusers
2590  SET passwd = '$hash',
2591  forcechange = 'N',
2592  pwchange = now()
2593  WHERE lower(user_name) = '" . strtolower($p_hb_env['Cn']) . "' and cu = '{$p_hb_env['Cu']}'; ";
2594 
2595  if ($p_transaction) {
2596  if (!$trans_rs = db_query($sql, $p_dbh)) {
2597  $retFuncFailed = true;
2598  }
2599 
2600  } else {
2601  $p_build_sql .= $sql;
2602  }
2603  }
2604 
2605  return $retFuncFailed;
2606 }
2607 
2608 /**
2609  * Update_SettingsAlias
2610  * Update the user_alias piece of the member information
2611  *
2612  * @param integer $p_dbh -- Current database handle
2613  * @param array $p_hb_env -- Current HB_ENV values
2614  * @param array $p_values -- Array of the values being passed to be updated
2615  * @param boolean $p_transaction -- {true, false} Are we running udpate as transaction queries?
2616  * @param string $p_build_sql -- BYREF The current SQL to build if we are NOT doing transaction
2617  * @return boolean -- Did the function fail in execuing the query
2618  */
2619 function DEPRECATED_Update_SettingsAlias($p_dbh, $p_hb_env, $p_values, $p_transaction, &$p_build_sql) {
2620  $retFuncFailed = false; // ** Did the query execute as expected?
2621 
2622  // ** Build the SQL to execute
2623  $sql = "UPDATE cuusers
2624  SET user_alias='" . prep_save($p_values['username'], 50) . "'
2625  WHERE user_name = '{$p_hb_env['Cn']}' and cu = '{$p_hb_env['Cu']}'
2626  AND NOT EXISTS
2627  (SELECT * FROM cuusers WHERE cu = '{$p_hb_env['Cu']}'
2628  and user_name <> '{$p_hb_env['Cn']}'
2629  and user_alias = '" . prep_save($p_values['username'], 50) . "'); ";
2630 
2631  if ($p_transaction) {
2632  if(!$trans_rs = db_query($sql, $p_dbh)) {
2633  $retFuncFailed = true;
2634  }
2635  } else {
2636  // ** Build SQL
2637  $p_build_sql .= $sql;
2638  }
2639 
2640  return $retFuncFailed;
2641 }
2642 
2643 /**
2644  * Update_SettingsConfidence
2645  * Update the members' confidence word
2646  *
2647  * @param integer $p_dbh -- Current database handle
2648  * @param array $p_hb_env -- Current HB_ENV values
2649  * @param array $p_values -- Array of the values being passed to be updated
2650  * @param boolean $p_transaction -- {true, false} Are we running udpate as transaction queries?
2651  * @param string $p_build_sql -- BYREF The current SQL to build if we are NOT doing transaction
2652  * @return boolean -- Did the function fail in execuing the query
2653  */
2654 function DEPRECATED_Update_SettingsConfidence($p_dbh, $p_hb_env, $p_values, $p_transaction, &$p_build_sql) {
2655  global $MEM_FORCE_RESET;
2656 
2657  $retFuncFailed = false; // ** Did the function execute as expected
2658 
2659  // ** Build the SQL to execute
2660 
2661  // * *WHEN I set the confidence I will also RESET THE userflags for FORCE_RESET
2662  //coalesce(userflags - (userflags::int4 & {$MEM_FORCE_RESET}::int4), 0),
2663 
2664  $sql = "UPDATE cuusers
2665  SET confidence = '" . prep_save($p_values['confword'], 20) . "',
2666  userflags = coalesce(userflags - (userflags::int4 & {$MEM_FORCE_RESET}::int4), 0)
2667 
2668  WHERE user_name = '{$p_hb_env['Cn']}' and cu = '{$p_hb_env['Cu']}'; ";
2669 
2670 
2671  if ($p_transaction) {
2672  if(!$trans_rs = db_query($sql, $p_dbh)) {
2673  $retFuncFailed = true;
2674  }
2675  } else {
2676  // ** Build SQL
2677  $p_build_sql .= $sql;
2678  }
2679 
2680  return $retFuncFailed;
2681 }
2682 /**
2683  * Update_SettingsConfidence
2684  * Update the members' challenge questions
2685  *
2686  * @param integer $p_dbh -- Current database handle
2687  * @param array $p_hb_env -- Current HB_ENV values
2688  * @param array $p_values -- Array of the values being passed to be updated
2689  * @param boolean $p_transaction -- {true, false} Are we running udpate as transaction queries?
2690  * @param string $p_build_sql -- BYREF The current SQL to build if we are NOT doing transaction
2691  * @return boolean -- Did the function fail in execuing the query
2692  */
2693 function DEPRECATED_Update_SettingsChallenge($p_dbh, $p_hb_env, $p_values, $p_transaction, &$p_build_sql) {
2694  $retFuncFailed = false; // ** Did the function execute as expected
2695 
2696  // ** Build the SQL to execute
2697 
2698  // * First DELETE ALL
2699  $sql = "DELETE FROM cuquestselect
2700  WHERE accountnumber = '{$p_hb_env['Cn']}' and cu = '{$p_hb_env['Cu']}'; ";
2701 
2702  // ** NOW ADD BACK THE INSERTS
2703  foreach ($p_values as $qst_info) {
2704  $qst_id = $qst_info['cqid'];
2705  $qst_value = $qst_info['display'];
2706  //FROM THIS WE will form the sql query
2707  // * validate the information here?
2708  $sql .= "INSERT INTO cuquestselect
2709  (cu, accountnumber, quest_id, answer)
2710  VALUES
2711  ('{$p_hb_env['Cu']}',
2712  '{$p_hb_env['Cn']}',
2713  '" . intval($qst_id) . "',
2714  '" . prep_save($qst_value, 50) . "'); ";
2715  }
2716 
2717 
2718  if ($p_transaction) {
2719  if(!$trans_rs = db_query($sql, $p_dbh)) {
2720  $retFuncFailed = true;
2721  }
2722  } else {
2723  // ** Build SQL
2724  $p_build_sql .= $sql;
2725  }
2726 
2727  return $retFuncFailed;
2728 }
2729 
2730 /**
2731  * Update_SettingsDesc
2732  * Update the members' account descriptions
2733  *
2734  * @param integer $p_dbh -- Current database handle
2735  * @param array $p_hb_env -- Current HB_ENV values
2736  * @param array $p_values -- Array of the values being passed to be updated
2737  * @param boolean $p_transaction -- {true, false} Are we running udpate as transaction queries?
2738  * @param string $p_build_sql -- BYREF The current SQL to build if we are NOT doing transaction
2739  * @return boolean -- Did the function fail in execuing the query
2740  */
2741 function DEPRECATED_Update_SettingsDesc($p_dbh, $p_hb_env, $p_values, $p_transaction, &$p_build_sql) {
2742  $retFuncFailed = false; // ** Did the function execute as expected
2743  if ($p_hb_env['live']) {
2744  $cutx = "culivetx";
2745  } else {
2746  $cutx = "cubatchtx";
2747  }
2748  // ** Build the SQL to execute
2749 
2750  $sql = "";
2751  if (is_array($p_values['swaccts']) && sizeof($p_values['swaccts']) > 0) {
2752  foreach ($p_values['swaccts'] as $swacct) {
2753  $id = trim($swacct[0]);
2754  $description = trim($swacct[1]);
2755 
2756  // ** -- Strip [\;`"] from the string
2757  $description = preg_replace('/[\\\]/', "", $description);
2758 
2759  $sql .= "update {$cutx} set description = '" . prep_save($description) . "'
2760  where id = $id and cu = '{$p_hb_env['Cu']}' and accountnumber = '{$p_hb_env['Cn']}';";
2761  }
2762  }
2763 #
2764  if (is_array($p_values['xaccts']) && sizeof($p_values['xaccts']) > 0) {
2765  foreach ($p_values['xaccts'] as $xacct) {
2766  $id = trim($xacct[0]);
2767  $description = trim($xacct[1]);
2768 
2769  // ** -- Strip [\;`"] from the string
2770  $description = preg_replace('/[\\\]/', "", $description);
2771 
2772  $sql .= "update {$cutx} set description = '" . prep_save($description) . "'
2773  where id = $id and cu = '{$p_hb_env['Cu']}' and accountnumber = '{$p_hb_env['Cn']}';";
2774  }
2775  }
2776 
2777  $set_myaccount = $p_values['set_myaccount'];
2778  $set_myaccount = preg_replace('/[;"\\\]/', "", $set_myaccount);
2779 
2780  $sql .= "delete from {$cutx}
2781  where cu = '{$p_hb_env['Cu']}'
2782  and accountnumber = '{$p_hb_env['Cn']}'
2783  and tomember = '{$p_hb_env['Cn']}';";
2784 
2785 
2786  $sql .= "insert into {$cutx} (cu, accountnumber,
2787  tomember, accounttype, deposittype, description, misc1)
2788  values ('{$p_hb_env['Cu']}','{$p_hb_env['Cn']}',
2789  '{$p_hb_env['Cn']}','00','W','" . prep_save($set_myaccount) . "',NULL);";
2790 
2791  if ($p_transaction) {
2792  if (!$trans_rs = db_query($sql, $p_dbh)) {
2793  $retFuncFailed = true;
2794  }
2795  } else {
2796  // ** Build SQL
2797  $p_build_sql .= $sql;
2798  }
2799 
2800  return $retFuncFailed;
2801 }
2802 
2803 /**
2804  * Get_PwdRules
2805  *
2806  * Return the password rules.
2807  *
2808  * @param integer $p_dbh - Current Database Pointer
2809  * @param array $p_hb_env - Current value of the HB_ENV array
2810  *
2811  * @return array - Returns an array of the rules.
2812  */
2813 function Get_PwdRules($p_dbh, $p_hb_env) {
2814  // read the password requirements, if any
2815  $sql = "select pwdconfig from cuadmin where cu='{$p_hb_env["cu"]}'";
2816  $sth = db_query($sql,$p_dbh);
2817 
2818  $row = db_fetch_array($sth,0);
2819  // this is a JSON object
2820  $pwdConfigAry = json_decode( $row["pwdconfig"], true );
2821 
2822  // see if the "use" flag is not set (meaning we need to use the default requirements)
2823  if ( $pwdConfigAry["use"] == 1 ) {
2824  // strip out the "use" since the rules should always be used
2825  unset( $pwdConfigAry["use"] );
2826  } else {
2827  $pwdConfigAry = array( "len" => 6, "digit" => 1);
2828  }
2829 
2830  return $pwdConfigAry;
2831 } // end Get_PwdRules
2832 
2833 /**
2834  * Validate_PwdRules
2835  *
2836  * This will accept the same settings as Validate_Settings, BUT will VALIDATE
2837  * that the password rules are being met. If the requirements are not set up
2838  * then use the default checks that are tested down inside Validate_SettingsPassword().
2839  * Only call this if you want to validate.
2840  *
2841  * @param integer $p_dbh - Current Database Pointer
2842  * @param array $p_hb_env - Current value of the HB_ENV array
2843  * @param array $p_upd_fields - These are the fields that will be validated. Their
2844  * layout is the same as Update_Settings
2845  * $p_upd_fields['settings_password']['current'] : current password;
2846  * $p_upd_fields['settings_password']['newpasswd'] : new password;
2847  * $p_upd_fields['settings_password']['confpasswd'] : confirming new password;
2848  * @param object $p_mc - This is the currently used Language Class
2849  *
2850  * @return array - Returns a status array containing any errors that may have
2851  * been encountered
2852  */
2853 function Validate_PwdRules($p_dbh, $p_hb_env, $p_upd_fields, $p_mc) {
2854  $retAry = Array("status" => Array ("code" => "", "severity" => "", "errors" => Array()));
2855 
2856  // see if password requirements are set
2857  $sql = "select pwdconfig from cuadmin where cu='{$p_hb_env["cu"]}'";
2858  $sth = db_query($sql,$p_dbh);
2859 
2860  $row = db_fetch_array($sth,0);
2861  // this is a JSON object
2862  $pwdConfigAry = json_decode( $row["pwdconfig"], true );
2863  db_free_result($sth);
2864 
2865  if ( count( $pwdConfigAry ) && $pwdConfigAry["use"] > 0 ) {
2866  $pwdErrorCount = 0;
2867  if ( $pwdConfigAry["len"] > 0 &&
2868  strlen( $p_upd_fields['settings_password']['newpasswd'] ) < $pwdConfigAry["len"] ) {
2869  $pwdErrorCount++;
2870  }
2871 
2872  // make an array for easier searching
2873  $specialCharAry = explode( ",", Get_PwdSpecialCharacters() );
2874 
2875  // gather the other requirement test values
2876  $countUpper = 0;
2877  $countLower = 0;
2878  $countSpecial = 0;
2879  $countDigits = 0;
2880  $countLetters = 0; // upper or lower
2881  $testString = trim( $p_upd_fields['settings_password']['newpasswd'] );
2882  for ( $i = 0; $i < strlen( $testString ); $i++ ) {
2883  $testChar = substr( $testString, $i, 1 );
2884  if (preg_match("/[a-z]/", $testChar) ) {
2885  $countLower++;
2886  $countLetters++;
2887  } else if (preg_match("/[A-Z]/", $testChar) ) {
2888  $countUpper++;
2889  $countLetters++;
2890  } else if (preg_match("/\d/", $testChar) ) {
2891  $countDigits++;
2892  } else if (in_array($testChar, $specialCharAry ) ) {
2893  $countSpecial++;
2894  }
2895  }
2896 
2897  // now test against the requirements
2898  if (HCU_array_key_value("len", $pwdConfigAry) > 0) {
2899  if ( strlen( $testString ) < $pwdConfigAry["len"] ) {
2900  $pwdErrorCount++;
2901  }
2902  }
2903  if (HCU_array_key_value("upper", $pwdConfigAry) > 0) {
2904  if ( $countUpper < $pwdConfigAry["upper"] ) {
2905  $pwdErrorCount++;
2906  }
2907  }
2908  if (HCU_array_key_value("lower", $pwdConfigAry) > 0) {
2909  if ( $countLower < $pwdConfigAry["lower"] ) {
2910  $pwdErrorCount++;
2911  }
2912  }
2913  if (HCU_array_key_value("spec", $pwdConfigAry) > 0) {
2914  if ( $countSpecial < $pwdConfigAry["spec"] ) {
2915  $pwdErrorCount++;
2916  }
2917  }
2918  if (HCU_array_key_value("digit", $pwdConfigAry) > 0) {
2919  if ( $countDigits < $pwdConfigAry["digit"] ) {
2920  $pwdErrorCount++;
2921  }
2922  }
2923 
2924  // check if the requirements were met
2925  if ( $pwdErrorCount > 0 ) {
2926  $retAry['status']['errors'][] = $p_mc->msg('Minimum password requirement not met');
2927  $FailedCurrPassword = true;
2928  }
2929  } else {
2930  $FailedCurrPassword = Validate_SettingsPassword($p_dbh, $p_hb_env, $p_upd_fields['settings_password'], $retAry['status']['errors'], $p_mc);
2931  }
2932 
2933  if (count($retAry['status']['errors']) > 0) {
2934  // * validation errors occurred
2935  $retAry['status']['code'] = '999';
2936  $retAry['status']['severity'] = 'ERRORS';
2937 
2938  if ( $FailedCurrPassword ) {
2939  $retAry['status']['failed_curr_pass'] = TRUE;
2940  }
2941  } else {
2942  // * SUCCESS
2943  $retAry['status']['code'] = '000';
2944  $retAry['status']['severity'] = 'SUCCESS';
2945  }
2946 
2947  return $retAry;
2948 } // end Validate_PwdRules
2949 
2950 /**
2951  * Validate_Settings
2952  *
2953  * This will accept the same settings as Update_Settings, BUT will VALIDATE
2954  * The information ONLY
2955  *
2956  * @param integer $p_dbh - Current Database Pointer
2957  * @param array $p_hb_env - Current value of the HB_ENV array
2958  * @param array $p_upd_fields - These are the fields that will be validated. Their
2959  * layout is the same as Update_Settings
2960  * @param object $p_mc - This is the currently used Language Class
2961  *
2962  * @return array - Returns a status array containing any errors that may have
2963  * been encountered
2964  */
2965 function Validate_Settings($p_dbh, $p_hb_env, $p_upd_fields, $p_mc) {
2966  $retAry = Array("status" => Array ("code" => "", "severity" => "", "errors" => Array()));
2967 
2968  // * For each included piece, call it's validation routine.
2969  // ** any error message will be accumulated on the errors array that will be passed to each routine
2970  $trans_failed = false;
2971 
2972  // ** Validate Email
2973 
2974  if (isset($p_upd_fields['settings_email']) && !$trans_failed) {
2975  Validate_SettingsEmail($p_dbh, $p_hb_env, $p_upd_fields['settings_email'], $retAry['status']['errors'], $p_mc);
2976  }
2977 
2978  // ** Validate Password
2979  if (isset($p_upd_fields['settings_password']) && !$trans_failed) {
2980  $FailedCurrPassword = Validate_SettingsPassword($p_dbh, $p_hb_env, $p_upd_fields['settings_password'], $retAry['status']['errors'], $p_mc);
2981  }
2982 
2983  // ** Validate Security Phone Numbers
2984  if (isset($p_upd_fields['settings_phones']) && !$trans_failed) {
2985  Validate_SettingsPhones($p_dbh, $p_hb_env, $p_upd_fields['settings_phones'], $retAry['status']['errors'], $p_mc);
2986  }
2987 
2988  // ** Validate Challenge Questions
2989  if (isset($p_upd_fields['settings_questions']) && !$trans_failed) {
2990  Validate_SettingsChallenge($p_dbh, $p_hb_env, $p_upd_fields['settings_questions'], $retAry['status']['errors'], $p_mc);
2991  }
2992 
2993  // ** Validate User Alias
2994  if (isset($p_upd_fields['settings_alias']) && !$trans_failed) {
2995  Validate_SettingsAlias($p_dbh, $p_hb_env, $p_upd_fields['settings_alias'], $retAry['status']['errors'], $p_mc);
2996  }
2997 
2998  // ** Validate Confidence Word
2999  if (isset($p_upd_fields['settings_confidence']) && !$trans_failed) {
3000  Validate_SettingsConfidence($p_dbh, $p_hb_env, $p_upd_fields['settings_confidence'], $retAry['status']['errors'], $p_mc);
3001  }
3002 
3003  // ** Validate Account Descriptions
3004  if (isset($p_upd_fields['settings_desc']) && !$trans_failed) {
3005  Validate_SettingsDesc($p_dbh, $p_hb_env, $p_upd_fields['settings_desc'], $retAry['status']['errors'], $p_mc);
3006  }
3007 
3008  if (count($retAry['status']['errors']) > 0) {
3009  // * validation errors occurred
3010  $retAry['status']['code'] = '999';
3011  $retAry['status']['severity'] = 'ERRORS';
3012 
3013 
3014  if ( $FailedCurrPassword ) {
3015  $retAry['status']['failed_curr_pass'] = TRUE;
3016  }
3017  } else {
3018  // * SUCCESS
3019  $retAry['status']['code'] = '000';
3020  $retAry['status']['severity'] = 'SUCCESS';
3021  }
3022 
3023  return $retAry;
3024 }
3025 
3026 /**
3027  *
3028  * Validate_SettingsEmail
3029  *
3030  * This will evaluate the email in p_values. If an error occurred the error will
3031  * be added to the p_errors_ary array
3032  *
3033  * @param integer $p_dbh
3034  * @param array $p_hb_env
3035  * @param array $p_values
3036  * @param array $p_errors_ary REFERENCE
3037  * @param object $p_mc
3038  *
3039  * @return NO RETURN
3040  */
3041 function Validate_SettingsEmail($p_dbh, $p_hb_env, $p_values, &$p_errors_ary, $p_mc) {
3042 
3043  if (trim($p_values['email']) == '') {
3044  // ** EMPTY EMAIL
3045  $p_errors_ary[] = $p_mc->msg('EMail Missing');
3046  } else {
3047  if (!validateEmail($p_values['email'])) {
3048  $p_errors_ary[] = $p_mc->msg('Email appears invalid');
3049  }
3050  }
3051 
3052  // ** NO RETURN
3053 }
3054 /**
3055  * Validate_SettingsPassword
3056  *
3057  * Validate the password security fields.
3058  *
3059  * @param integer $p_dbh
3060  * @param array $p_hb_env
3061  * @param array $p_values
3062  * @param array $p_errors_ary REFERENCE
3063  * @param object $p_mc
3064  *
3065  * @return TRUE if failed current password check, FALSE otherwise
3066  */
3067 function Validate_SettingsPassword($p_dbh, $p_hb_env, $p_values, &$p_errors_ary, $p_mc) {
3068  $FailedCurrPassword = false;
3069 
3070  $pwd_maxlength = 20;
3071  // ** Did the user review password guidelines??
3072  if (array_key_exists('review_guidelines', $p_values)) {
3073  if ($p_values['review_guidelines'] != 'Y') {
3074  $p_errors_ary[] = $p_mc->msg("Please review the password guidelines");
3075  }
3076  }
3077 
3078  // ** Get the saved member password hash
3079  $mbrPwd = GetUserInfo($p_dbh, $p_hb_env, $p_mc, Array("user_id" => $p_hb_env['Uid'], "cu" => $p_hb_env['Cu']));
3080 
3081  if ($mbrPwd['status']['code'] != '000') {
3082  // ** An error occurred retrieving the saved password hash value
3083  $p_errors_ary = array_merge($p_errors_ary, $mbrPwd['status']['errors']);
3084  } else {
3085  $mbr_pwdhash = $mbrPwd['data']['cuusers_passwd'];
3086 
3087  // ** if Current password is included -- verify it matches current password for member
3088  if (array_key_exists('current', $p_values)) {
3089 
3090  $saved_passwd = preg_replace("/ +$/", "", $mbr_pwdhash);
3091  $current_hashed = password_verify($p_values['current'], $saved_passwd);
3092  if (!$current_hashed) {
3093  $p_errors_ary[] = $p_mc->msg('Invalid login or password');
3094 
3095  $FailedCurrPassword = true;
3096  }
3097 
3098  // * Be sure the NEW and CURRENT do NOT match
3099  if (array_key_exists('newpasswd', $p_values)) {
3100  if ($p_values['newpasswd'] == $p_values['current']) {
3101  $p_errors_ary[] = $p_mc->msg('New password and Current password cannot match');
3102  }
3103  }
3104  }
3105 
3106  // ** Validate new passwords
3107  if (array_key_exists('newpasswd', $p_values)) {
3108  // ** Do they match?
3109  if ($p_values['newpasswd'] != $p_values['confpasswd']) {
3110  $p_errors_ary[] = $p_mc->msg("New passwords do not match");
3111  }
3112 
3113  // ** validate the password length
3114  if (strlen($p_values['newpasswd']) < 6 || strlen($p_values['newpasswd']) > 20 ) {
3115  $p_errors_ary[] = $p_mc->combo_msg('NEW password must be X characters long', 0, "#max#", $pwd_maxlength);
3116  }
3117 
3118  // ** Do they meet security criteria?
3119  if (preg_match("/['\"]/",$p_values['newpasswd'])){
3120  $p_errors_ary[] = $p_mc->msg("Invalid Characters in New password");
3121  }
3122 
3123  if (strlen($p_values['newpasswd']) > 0 && !(preg_match("/\d/",$p_values['newpasswd']) && preg_match("/\D/",$p_values['newpasswd']))) {
3124  $p_errors_ary[] = $p_mc->msg("Password must contain both number and letter characters");
3125  }
3126  }
3127  }
3128 
3129  return $FailedCurrPassword;
3130 }
3131 
3132 /**
3133  * Validate_SettingsAlias
3134  *
3135  * Validate the User Alias, It must be unique and meet certain security requirements
3136  *
3137  * @param integer $p_dbh
3138  * @param array $p_hb_env
3139  * @param array $p_values
3140  * @param array $p_errors_ary REFERENCE
3141  * @param object $p_mc
3142  *
3143  * @return NO RETURN
3144  */
3145 function Validate_SettingsAlias($p_dbh, $p_hb_env, $p_values, &$p_errors_ary, $p_mc) {
3146 
3147  // * Is username Required? -- If so, it may not be blank
3148  if (strlen($p_values['username']) == 0 && (($p_hb_env['Fset2'] & $GLOBALS['CU2_ALIAS_REQ']) == $GLOBALS['CU2_ALIAS_REQ'])) {
3149  $p_errors_ary[] = $p_mc->msg("Username Required");
3150  }
3151 
3152  // ** Validate the user alias if it IS entered
3153  // * Validate user alias length
3154  if (strlen($p_values['username']) > 0 && strlen($p_values['username']) < 6 ) {
3155  $p_errors_ary[] = $p_mc->msg('Username too short');
3156  }
3157  // * Validate the useralias and confirmation match
3158  if ($p_values['username'] != $p_values['username_confirm']) {
3159  $p_errors_ary[] = $p_mc->msg('Username nomatch');
3160  }
3161  // * Validate the useralias is correct format
3162  if (strlen($p_values['username']) > 5 && !check_alias_format($p_values['username'])) {
3163  $p_errors_ary[] = $p_mc->msg('Username appears invalid');
3164  }
3165  // The user alias MUST be unique -- This is a database lookup
3166  // ** If there are NO errors reported, then go ahead and also evaluate the user alias availability
3167 
3168  if (count($p_errors_ary) == 0) {
3169  if (strlen($p_values['username']) > 5 && !check_alias_available($p_dbh, $p_hb_env, $p_values['username'])) {
3170  $p_errors_ary[] = $p_mc->msg('Username not available');
3171  }
3172  }
3173 
3174 
3175 }
3176 
3177 /**
3178  *
3179  * Validate_SettingsConfidence
3180  *
3181  * This will evaluate the information in settings_confidence. It will ensure
3182  * the confidence word is valid
3183  *
3184  * @param integer $p_dbh
3185  * @param array $p_hb_env
3186  * @param array $p_values [settings_confidence]
3187  * @param array $p_errors_ary REFERENCE
3188  * @param object $p_mc
3189  *
3190  * @return NO RETURN
3191  */
3192 function Validate_SettingsConfidence($p_dbh, $p_hb_env, $p_values, &$p_errors_ary, $p_mc) {
3193 
3194  // Confidence word MUST be entered
3195  if (strlen($p_values['confword']) == 0) {
3196  $p_errors_ary[] = $p_mc->msg("Set Config Enter Word");
3197  }
3198 
3199  // ** NO RETURN
3200 }
3201 
3202 /**
3203  *
3204  * Validate_SettingsPhones
3205  *
3206  * This will evaluate the information in settings_phones. It will ensure
3207  * the phone numbers are valid.
3208  *
3209  * @param integer $p_dbh
3210  * @param array $p_hb_env
3211  * @param array $p_values [settings_phones]
3212  * @param array $p_errors_ary REFERENCE
3213  * @param object $p_mc
3214  *
3215  * @return NO RETURN
3216  */
3217 function Validate_SettingsPhones($p_dbh, $p_hb_env, $p_values, &$p_errors_ary, $p_mc) {
3218  $valid = true;
3219  if (is_array($p_values)) {
3220  foreach ($p_values as $type => $numbers) {
3221  if (is_array($numbers)) {
3222  for ($i = 0; $i < count($numbers); $i++) {
3223  $value = $numbers[$i];
3224  $valid = preg_match('/^[2-9]\d{2}-\d{3}-\d{4}$/', $value);
3225  if (!$valid) {
3226  $p_errors_ary[] = $p_mc->msg("Phone Invalid", HCU_DISPLAY_AS_JS);
3227  break;
3228  }
3229  }
3230  } else {
3231  $value = $numbers;
3232  if ($value != '') {
3233  $valid = preg_match('/^[2-9]\d{2}-\d{3}-\d{4}$/', $value);
3234  if (!$valid) {
3235  $p_errors_ary[] = $p_mc->msg("Phone Invalid", HCU_DISPLAY_AS_JS);
3236  break;
3237  }
3238  }
3239  }
3240  if (!$valid) { break; }
3241  }
3242  }
3243 }
3244 
3245 /**
3246  * Validate_SettingsChallenge
3247  *
3248  * Validate the challenge questions/responses
3249  *
3250  * @param integer $p_dbh
3251  * @param array $p_hb_env
3252  * @param array $p_values - information set in the settings_questions section
3253  * @param array $p_errors_ary BY REFERENCE
3254  * @param object $p_mc - This is the current MC class for the selected language
3255  *
3256  * @return NO RETURN
3257  */
3258 function Validate_SettingsChallenge($p_dbh, $p_hb_env, $p_values, &$p_errors_ary, $p_mc) {
3259 
3260  $loop_idx = 0;
3261  $selected_questions_list = Array();
3262  foreach ($p_values as $quest_info) {
3263  $qst_id = $quest_info['cqid'];
3264  $qst_resp = $quest_info['display'];
3265 
3266  $loop_idx++;
3267  // ** loop through each id -> response
3268 
3269  // * No Question ID selected
3270  if (intval($qst_id) == 0) {
3271  $p_errors_ary[] = $p_mc->combo_msg("Set Config Question Select", 0, "#num#", $loop_idx);
3272  } else {
3273  // * Duplicated Questions
3274  // * if the question was answered, check to see if it was selected before
3275  if (array_search(intval($qst_id), $selected_questions_list) !== False) {
3276  // * It has been duplicated
3277  $p_errors_ary[] = $p_mc->combo_msg("Set Config Question Duplicate", 0, "#num#", $loop_idx);
3278  }
3279  // * Add the current Question ID to the list of selected questions
3280  $selected_questions_list[] = intval($qst_id);
3281  }
3282 
3283  // * Question response is BLANK
3284  if (strlen($qst_resp) == 0) {
3285  $p_errors_ary[] = $p_mc->combo_msg("Set Config Question Blank", 0, "#num#", $loop_idx);
3286  }
3287  }
3288 
3289  // ** NO RETURN
3290 }
3291 /**
3292  * Validate_SettingsDesc
3293  *
3294  * Validate the account descriptions
3295  *
3296  * @param integer $p_dbh
3297  * @param array $p_hb_env
3298  * @param array $p_values - information set in the settings_desc section
3299  * myaccount string member account nickname
3300  * swaccts array descriptions for switch accounts
3301  * xaccts array descriptions for cross-accounts
3302  * subaccts array descriptions for myaccount subaccts (inc. joint accts)
3303  * @param array $p_errors_ary BY REFERENCE
3304  * @param object $p_mc - This is the current MC class for the selected language
3305  *
3306  * @return NO RETURN
3307  */
3308 function Validate_SettingsDesc($p_dbh, $p_hb_env, $p_values, &$p_errors_ary, $p_mc) {
3309 
3310  $set_myaccount = trim($p_values['set_myaccount']);
3311  if ($set_myaccount == "") {
3312  $p_errors_ary[] = $p_mc->msg('Account Descriptions cannot be blank');
3313  }
3314 
3315  # if we have switch accounts, validate the descriptions are unique
3316  if (is_array($p_values['swaccts']) && sizeof($p_values['swaccts']) > 0) {
3317  $membercnt = array("$set_myaccount" => 1);
3318  foreach ($p_values['swaccts'] as $swacct) {
3319  $description = trim($swacct[1]);
3320  $membercnt{$description} += 1;
3321 
3322  if (trim($description) != "" && $membercnt{$description} > 1) {
3323  $p_errors_ary[] = $p_mc->msg('Account Descriptions must be unique');
3324  }
3325  if (trim($description) == "") {
3326  $p_errors_ary[] = $p_mc->msg('Account Descriptions cannot be blank');
3327  }
3328  }
3329  }
3330 
3331  # if we have cross accounts, validate the descriptions are unique
3332  if (is_array($p_values['xaccts']) && sizeof($p_values['xaccts']) > 0) {
3333  $membercnt = array("$set_myaccount" => 1);
3334  foreach ($p_values['xaccts'] as $xacct) {
3335  $description = trim($xacct[1]);
3336  $membercnt{$description} += 1;
3337 
3338  if (trim($description) != "" && $membercnt{$description} > 1) {
3339  $p_errors_ary[] = $p_mc->msg('Account Descriptions must be unique');
3340  }
3341  if (trim($description) == "") {
3342  $p_errors_ary[] = $p_mc->msg('Account Descriptions cannot be blank');
3343  }
3344  }
3345  }
3346 
3347  // ** NO RETURN
3348 }
Generated by   doxygen 1.8.15