Odyssey
MbrExDMI.data
1 <?php
2 
3 /*
4  * File: MbrExEZCARD.data
5  * Purpose: Handle the CRUD portion of the MbrExtKey maintenance script
6  * when trustid = HcuDMI
7  * When returning the requested data do it in a JSON format, for the client to
8  * display accordingly.
9  *
10  * Call this script with the following parameters
11  * cu to identify appropriate {$Cu}extkey table (might use $Cu out of cookie?)
12  * action - what the client side is requesting.
13  * trustid for data related to a particular providermode in {$Cu}extkey table
14  * ***trustid must match existing cutrusteddetail record for given $Cu
15  * member - to identify member for whom edits occur
16  * accounttype - to identify loan number for which edits occur
17  *
18  * Returns JSON OBJECT.
19  *
20  * based on trustid / providermode, load appropriate include to define
21  * parms_parse - explode parms into component values
22  * parms_validate - check entries before attemtping db update
23  * includes readying for db write
24  * parms_disp4edit - layout screen for display / edit values
25  */
26 
27 $iClass= $HcuDMIi;
28 extract($POSTED["HCUPOST"]);
29 try {
30  $returnMsgData = Array();
31  switch ($POSTED['HCUPOST']['action']) {
32  case 'read':
33  if (!isset($Cu) || !isset($trustid)) {
34  throw new Exception('Missing Expected Parameters');
35  }
36  # should only be one record, but just in case, limit 1
37 
38  $where= array("providermode='$trustid'");
39  $doLimit= false;
40  # should only be one record, but just in case, limit 1
41  if (isset($member))
42  {
43  $where[]= "accountnumber='$member'";
44  $doLimit= true;
45  }
46  if (isset($accounttype))
47  $where[]= "accounttype='$accounttype'";
48  if (isset($userid))
49  $where[]= "user_id=" . intval($userid);
50 
51  $sql = "select user_id as userid, id as keyid, trim(accountnumber) as accountnumber, trim(accounttype) as accounttype, parms from {$Cu}extkey
52  where " . implode(" and ", $where) . ($doLimit ? " limit 1" : "");
53 
54  $sqlRs = db_query($sql, $dbh);
55  if (!($sqlRs)) {
56  throw new Exception("SQL failed ($sql).");
57  }
58 
59  $sIdx = 0;
60  while ($dRecord = db_fetch_assoc($sqlRs, $sIdx)) {
61  $payload = array();
62  /*
63  * now parse the payload as needed for this provider mode
64  * HcuDMI needs this: list($dmiloan) = explode(',', $payload);
65  *
66  */
67  $payload = $iClass->parms_parse($dRecord['parms']); #array('dmiloan' => $dmiloan);
68  $returnMsgData[] = $dRecord + $payload;
69  $sIdx++;
70  }
71 
72  $retStatus_ary['homecuData'] = $returnMsgData;
73 
74  // Get DDLs
75  $sql= "select u.user_id, row_number() over(order by lb.accountnumber, lb.loannumber) as rown, lb.accountnumber, lb.loannumber, coalesce(ek.id, 0) as extkeyid from ${Cu}user u
76  inner join ${Cu}loanbalance lb on u.primary_account = lb.accountnumber and lb.loannumber not like '%@%'
77  left join ${Cu}extkey ek on lb.accountnumber = ek.accountnumber and lb.loannumber = ek.accounttype and ek.providermode = '" . prep_save($POSTED['HCUPOST']['trustid'], 20) . "'";
78  if (isset($userid))
79  $sql.= "where u.user_id=" . intval($userid);
80 
81  $sth= db_query($sql, $dbh);
82  if (!$sth)
83  throw new exception("DDL query failed!", 1);
84  $loanRecords= array();
85  $accountRecords= array();
86  for($i=0; $record= db_fetch_assoc($sth, $i); $i++)
87  {
88  $accountnumber= trim($record["accountnumber"]);
89  if (!isset($accountRecords[$accountnumber]))
90  $accountRecords[$accountnumber]= array("accountnumber" => $accountnumber);
91  if (!isset($loanRecords[$accountnumber]))
92  $loanRecords[$accountnumber]= array();
93  $loanRecords[$accountnumber][]= array("loannumber" => trim($record["loannumber"]), "extkeyid" => intval($record["extkeyid"]), "userid" => $record["user_id"]);
94  }
95  $retStatus_ary["loanData"] = $loanRecords;
96  $retStatus_ary["accountData"]= array_values($accountRecords);
97  break;
98 
99  case 'new':
100  /*
101  * save member data to {$Cu}extkey table
102  * expects that $payload is ready to write -
103  * call payload_validate to check data
104  * and payload_format to prepare it for saving first
105  */
106  if (!isset($Cu) || !isset($trustid) || !isset($accountnumber) || !isset($accounttype)) {
107 
108  throw new Exception('Missing Expected Parameters');
109  }
110  /*
111  * Validate the data coming in
112  */
113 
114  // * Make sure the member exists
115  $sql = "SELECT count(*) as count_rec
116  FROM ${Cu}user where primary_account = '" . prep_save($POSTED['HCUPOST']['accountnumber'], 12) . "'";
117  $cntRs = db_query($sql, $dbh);
118  list($recordCount) = db_fetch_array($cntRs);
119  if ($recordCount == 0) {
120  throw new Exception('Unable to save entry, Invalid Member Number.');
121  }
122  // * Make sure the HCU Loan exists
123  $sql = "SELECT count(*) as count_rec
124  FROM {$Cu}loanbalance
125  WHERE accountnumber = '" . prep_save($POSTED['HCUPOST']['accountnumber'], 12) . "'
126  AND loannumber = '" . prep_save($POSTED['HCUPOST']['accounttype'], 25) . "'";
127  $cntRs = db_query($sql, $dbh);
128  list($recordCount) = db_fetch_array($cntRs);
129  if ($recordCount == 0) {
130  throw new Exception('Unable to save entry, Invalid HCU Loan Number.');
131  }
132 
133  // ** only one entry per member / hcu Loan
134  $sql = "SELECT count(*) as count_rec
135  FROM {$Cu}extkey
136  WHERE accountnumber = '" . prep_save($POSTED['HCUPOST']['accountnumber'], 12) . "'
137  AND accounttype = '" . prep_save($POSTED['HCUPOST']['accounttype'], 25) . "'
138  AND providermode = '" . prep_save($POSTED['HCUPOST']['trustid'], 20) . "'";
139  $cntRs = db_query($sql, $dbh);
140  list($recordCount) = db_fetch_array($cntRs);
141  if ($recordCount > 0) {
142  throw new Exception('Unable to save entry, Only one entry per Member / HCU Loan allowed.');
143  }
144 
145  $payload = $iClass->parms_validate($POSTED['HCUPOST']);
146  if (is_array($payload[errors])) {
147  throw new Exception(json_encode($payload[errors]));
148  }
149 
150  $sql = "INSERT INTO {$Cu}extkey (accountnumber, user_id, providermode, accounttype, parms)
151  VALUES (
152  '" . prep_save($POSTED['HCUPOST']['accountnumber'], 12) . "',
153  '" . prep_save($POSTED['HCUPOST']['userid']) . "',
154  '" . prep_save($POSTED['HCUPOST']['trustid'], 20) . "',
155  '" . prep_save($POSTED['HCUPOST']['accounttype'], 25) . "',
156  '" . prep_save($payload['data']) . "');
157  select currval('${Cu}extkey_id_seq')";
158  $updRs = db_query($sql, $dbh);
159 
160  if (!$updRs) {
161  // ** FAILED
162  throw new Exception('A problem occurred, unable to save entry.');
163  } else {
164  list($keyid) = db_fetch_array($updRs,0);
165  // ** SUCCESS
166  $retStatus_ary['homecuInfo'][] = "Entry successfully saved.";
167  $retStatus_ary['homecuData'] = $iClass->parms_parse($payload['data']);
168  $retStatus_ary['homecuData']['keyid'] = $keyid;
169  $retStatus_ary['homecuData']['accountnumber'] = $POSTED['HCUPOST']['accountnumber'];
170  $retStatus_ary['homecuData']['accounttype'] = $POSTED['HCUPOST']['accounttype'];
171  }
172  break;
173 
174  case 'update':
175  /*
176  * save member data to {$Cu}extkey table
177  * expects that $payload is ready to write -
178  * call payload_validate to check data
179  * and payload_format to prepare it for saving first
180  */
181  if (!isset($Cu) || !isset($trustid) || !isset($accountnumber) || !isset($accounttype)) {
182 
183  throw new Exception('Missing Expected Parameters');
184  }
185  /*
186  * Validate the data coming in
187  */
188  // * Make sure the member exists
189  $sql = "SELECT count(*) as count_rec
190  FROM ${Cu}user where primary_account = '" . prep_save($POSTED['HCUPOST']['accountnumber'], 12) . "'";
191  $cntRs = db_query($sql, $dbh);
192  list($recordCount) = db_fetch_array($cntRs);
193  if ($recordCount == 0) {
194  throw new Exception('Update failed, Invalid Member Number.');
195  }
196  // * Make sure the HCU Loan exists
197  $sql = "SELECT count(*) as count_rec
198  FROM {$Cu}loanbalance
199  WHERE accountnumber = '" . prep_save($POSTED['HCUPOST']['accountnumber'], 12) . "'
200  AND loannumber = '" . prep_save($POSTED['HCUPOST']['accounttype'], 25) . "'";
201  $cntRs = db_query($sql, $dbh);
202  list($recordCount) = db_fetch_array($cntRs);
203  if ($recordCount == 0) {
204  throw new Exception('Update failed, Invalid HCU Loan Number.');
205  }
206 
207  // ** only one entry per member / HCU Loan
208  $sql = "SELECT count(*) as count_rec
209  FROM {$Cu}extkey
210  WHERE accountnumber = '" . prep_save($POSTED['HCUPOST']['accountnumber'], 12) . "'
211  AND accounttype = '" . prep_save($POSTED['HCUPOST']['accounttype'], 25) . "'
212  AND providermode = '" . prep_save($POSTED['HCUPOST']['trustid'], 20) . "'
213  AND id <> {$POSTED['HCUPOST']['keyid']}";
214  $cntRs = db_query($sql, $dbh);
215  list($recordCount) = db_fetch_array($cntRs);
216  if ($recordCount > 0) {
217  throw new Exception('Update failed, Only one entry per Member / HCU Loan allowed.');
218  }
219 
220  $payload = $iClass->parms_validate($POSTED['HCUPOST']);
221  if (is_array($payload[errors])) {
222  throw new Exception(implode("<br>\n",$payload[errors]));
223  }
224 
225  $sql = "UPDATE {$Cu}extkey SET accountnumber = '" . prep_save($POSTED['HCUPOST']['accountnumber'], 12) . "',
226  providermode = '" . prep_save($POSTED['HCUPOST']['trustid'], 20) . "',
227  accounttype = '" . prep_save($POSTED['HCUPOST']['accounttype'], 25) . "',
228  parms='" . prep_save($payload['data']) . "'
229  WHERE id = {$POSTED['HCUPOST']['keyid']}";
230  $updRs = db_query($sql, $dbh);
231  if (!$updRs) {
232  // ** FAILED
233  throw new Exception('A problem occurred, update failed.');
234  } elseif (db_affected_rows($updRs) != 1) {
235  throw new Exception('Record not found.');
236  } else {
237  // ** SUCCESS
238  $retStatus_ary['homecuInfo'][] = "Entry successfully updated.";
239  }
240  break;
241 
242  case 'delete':
243  $sql = "DELETE FROM {$Cu}extkey
244  WHERE id = {$POSTED['HCUPOST']['keyid']}";
245 
246  $updRs = db_query($sql, $dbh);
247  if (!$updRs) {
248  // ** FAILED
249  throw new Exception('A problem occurred. delete failed.');
250  } elseif (db_affected_rows($updRs) != 1) {
251  throw new Exception('Record not found. delete failed.');
252  } else {
253  // ** SUCCESS
254  $retStatus_ary['homecuInfo'][] = "Entry successfully deleted.";
255  }
256 
257  break;
258 
259  default:
260  throw new Exception("Unexpected action: {$action}. Action cancelled.");
261 
262  break;
263  }
264 } catch (Exception $ex) {
265  $exerror = $ex->getMessage();
266  $retStatus_ary['homecuErrors'][] = $ex->getMessage();
267 }
268 $retStatus_ary["type"]= $POSTED['HCUPOST']['action'];
269 
270 // ** Prepare the package for returning
271 header('Content-type: application/json');
272 
273 print json_encode(Array("Results" => Array($retStatus_ary)));
274 
275 ?>
Generated by   doxygen 1.8.15