Squiz Matrix  4.12.2
 All Data Structures Namespaces Functions Variables Pages
form_action_db_query.inc
1 <?php
18 require_once SQ_PACKAGES_PATH.'/cms/form/form_action/form_action.inc';
19 
33 class Form_Action_DB_Query extends Form_Action
34 {
35 
36 
41  function __construct()
42  {
43  parent::__construct();
44 
45  }//end constructor
46 
47 
53  public static function paintInlineInterface(Form $form, $settings, Backend_Outputter $o, $prefix)
54  {
55  self::_fillDefaults($settings);
56  ?>
57  <script type="text/javascript">
58  updateDbMethod = function(form) {
59  method_db = form.<?php echo $prefix ?>_method_db;
60 
61  el_list = document.getElementById("<?php echo $prefix ?>_conn_details_div").getElementsByTagName("select");
62  for (i = 0; i < el_list.length; i++) {
63  el_list[i].disabled = !method_db[0].checked;
64  }
65 
66  el_list = document.getElementById("<?php echo $prefix ?>_conn_details_div").getElementsByTagName("input");
67  for (i = 0; i < el_list.length; i++) {
68  el_list[i].disabled = !method_db[0].checked;
69  }
70 
71  el_list = document.getElementById("<?php echo $prefix ?>_conn_string_div").getElementsByTagName("input");
72  for (i = 0; i < el_list.length; i++) {
73  el_list[i].disabled = !method_db[1].checked;
74  }
75 
76  // Disable user name/password section id using a DB Connector
77  el_list = document.getElementById("<?php echo $prefix ?>_login_section").getElementsByTagName("input");
78  for (i = 0; i < el_list.length; i++) {
79  el_list[i].disabled = method_db[2].checked;
80  }
81 
82  el_list = document.getElementById("<?php echo $prefix ?>_connector_div").getElementsByTagName("input");
83  for (i = 0; i < el_list.length; i++) {
84  // If we are moving off the DB Connector
85  if ((el_list[i].type == 'button') && (el_list[i].value == 'Cancel')) {
86  el_list[i].click();
87  }
88  el_list[i].disabled = !method_db[2].checked;
89  }
90  }
91  </script>
92  <?php $o->addOnLoad('updateDbMethod(document.main_form);'); ?>
93 
94  <p class="sq-backend-section-subheading">Database Details</p>
95 
96  <div id="<?php echo $prefix ?>_dsn_section" style="padding: 0.5em 0px 2em 30px;">
97 
98  <p><?php
99  radio_button($prefix.'_method_db', 'table', ($settings['method'] == 'table') ? TRUE : FALSE, 'updateDbMethod(this.form); return true;');
100  ?><strong>Specify database details</strong></p>
101 
102  <div id="<?php echo $prefix ?>_conn_details_div" style="padding-left: 30px">
103 
104  <?php
105  // Or use custom DB connection
106  $db_type = array_get_index($settings, 'db_type', '');
107  $database = array_get_index($settings, 'database', '');
108  $host = array_get_index($settings, 'host', '');
109 
110  ?>
111  <table class="sq-backend-table" style="width:auto">
112  <tr>
113  <td><?php echo translate('database_type');?></td>
114  <td>
115  <?php
116  $options = Array('pgsql' => 'PostgreSQL', 'oci' => 'Oracle', 'mysql' => 'MySQL');
117  combo_box($prefix.'_dsn[db_type]', $options, FALSE, $db_type);
118  ?>
119  </td>
120  </tr>
121  <tr>
122  <td><?php echo translate('database_name');?></td>
123  <td>
124  <?php
125  text_box($prefix.'_dsn[database]', $database, 35);
126  ?>
127  </td>
128  </tr>
129  <tr>
130  <td><?php echo translate('host_name');?></td>
131  <td>
132  <?php
133  text_box($prefix.'_dsn[host]', $host, 35);
134  ?>
135  </td>
136  </tr>
137  </table>
138  <?php
139 
140  // Or use connection string
141  ?>
142  </div>
143 
144  <p><?php
145  radio_button($prefix.'_method_db', 'string', ($settings['method'] == 'string') ? TRUE : FALSE, 'updateDbMethod(this.form); return true;');
146  ?><strong>Use a complete DSN</strong></p>
147  <div id="<?php echo $prefix ?>_conn_string_div" style="padding-left: 30px;">
148  <table class="sq-backend-table" style="width:auto">
149  <tr>
150  <td><p>Complete DSN</p></td>
151  <td><p><?php text_box($prefix.'_complete_dsn', ($settings['method'] == 'string') ? $settings['dsn'] : '', 70); ?></p></td>
152  </tr>
153  </table>
154  </div>
155 
156  <?php
157  // Or use DB Connector asset
158  ?><p><?php
159  radio_button($prefix.'_method_db', 'connector', ($settings['method'] == 'connector') ? TRUE : FALSE, 'updateDbMethod(this.form); return true;');
160  ?> <strong>Use the contents of a DB Connector asset</strong></p><?php
161 
162  ?>
163  <div id="<?php echo $prefix ?>_connector_div" style="padding-left: 30px;">
164  <table class="sq-backend-table" style="width:auto">
165  <tr>
166  <td><p>DB Connector Asset</p></td>
167  <td><p><?php asset_finder($prefix.'_connector', $settings['connector_assetid'], Array('db_connector'=>'D')); ?><br/>
168  Note: If a DB Connector asset is used, the user name and password fields below will be ignored, and the settings from that asset used instead.</p></td>
169  </tr>
170  </table>
171  </div>
172 
173  </div>
174 
175  <p class="sq-backend-section-subheading">Login Details</p>
176 
177  <div id="<?php echo $prefix ?>_login_section" style="padding: 0.5em 0px 2em 30px;">
178  <table style="width:auto">
179  <tr>
180  <td><strong><?php echo translate('user_name');?></strong></td>
181  <td>
182  <?php
183  text_box($prefix.'_username', $settings['username'], 35);
184  ?>
185  </td>
186  </tr>
187  <tr>
188  <td><strong><?php echo translate('password');?></strong></td>
189  <td>
190  <?php
191  password_box($prefix.'_password', $settings['password'], 35);
192  ?>
193  </td>
194  </tr>
195  </table>
196 
197  </div>
198 
199  <p class="sq-backend-section-subheading">SQL Query</p>
200 
201  <div id="<?php echo $prefix ?>_query_section" style="padding: 0.5em 0px 2em 30px;"><?php
202 
203  // Query field
204  text_area($prefix.'_sql', $settings['sql'], 100, 40, 0, 'style="width: 80%; height: 30em; font-family: \'Courier New\', Courier, monospace; display: block"');
205 
206  ?></div><?php
207 
208  }//end paintInlineInterface()
209 
210 
216  public static function processInlineInterface(Form $form, &$settings, Backend_Outputter $o, $prefix)
217  {
218  $settings = Array();
219 
220  $settings['method'] = array_get_index($_POST, $prefix.'_method_db', '');
221 
222  switch(array_get_index($_POST, $prefix.'_method_db', '')) {
223  case 'table':
224  $settings['dsn'] = $_POST[$prefix.'_dsn']['db_type'].':';
225  if (array_get_index($_POST[$prefix.'_dsn'], 'host', '') !== '') {
226  $settings['dsn'] .= 'host='.$_POST[$prefix.'_dsn']['host'].';';
227  }
228  if (array_get_index($_POST[$prefix.'_dsn'], 'database', '') !== '') {
229  $settings['dsn'] .= 'dbname='.$_POST[$prefix.'_dsn']['database'].';';
230  }
231 
232  $settings['dsn'] = trim($settings['dsn'], ';');
233 
234  $settings['db_type'] = array_get_index($_POST[$prefix.'_dsn'], 'db_type', '');
235  $settings['database'] = array_get_index($_POST[$prefix.'_dsn'], 'database', '');
236  $settings['host'] = array_get_index($_POST[$prefix.'_dsn'], 'host', '');
237 
238  $settings['username'] = array_get_index($_POST, $prefix.'_username', '');
239  $settings['password'] = array_get_index($_POST, $prefix.'_password', '');
240  break;
241 
242  case 'string':
243  $settings['dsn'] = $_POST[$prefix.'_complete_dsn'];
244  $settings['username'] = array_get_index($_POST, $prefix.'_username', '');
245  $settings['password'] = array_get_index($_POST, $prefix.'_password', '');
246  break;
247 
248  case 'connector':
249  $settings['connector_assetid'] = $_POST[$prefix.'_connector']['assetid'];
250  break;
251  }
252 
253  $settings['sql'] = array_get_index($_POST, $prefix.'_sql', '');
254 
255  return TRUE;
256 
257  }//end processInlineInterface()
258 
259 
265  public static function paintSummary(Form $form, $settings, Backend_Outputter $o, $prefix)
266  {
267  self::_fillDefaults($settings);
268 
269  ?><table class="no-borders">
270  <colgroup>
271  <col width="80" />
272  <col/>
273  </colgroup>
274  <tbody>
275  <tr>
276  <td class="sq-backend-table-cell" style="vertical-align: top"><p><strong>Data Source:</strong></p></td>
277  <td class="sq-backend-table-cell" style="vertical-align: top"><p><?php
278  if (!empty($settings['dsn'])) {
279  echo htmlspecialchars($settings['dsn']);
280  } else if (!empty($settings['connector_assetid'])) {
281  echo get_asset_tag_line($settings['connector_assetid']);
282  } else {
283  ?><span class="sq-backend-warning">No data source specified.</span><?php
284  }
285  ?></p></td>
286  </tr><?php
287  if (!empty($settings['dsn'])) {
288  ?><tr>
289  <td class="sq-backend-table-cell" style="vertical-align: top"><p><strong>User Name:</strong></p></td>
290  <td class="sq-backend-table-cell" style="vertical-align: top"><p><?php
291  if (!empty($settings['username'])) {
292  echo htmlspecialchars($settings['username']);
293  if (!empty($settings['password'])) {
294  ?> <em>(password supplied)</em><?php
295  }
296  } else {
297  ?>No user name specified.<?php
298  }
299  ?></p></td>
300  </tr><?php
301  }
302  ?><tr>
303  <td class="sq-backend-table-cell" style="vertical-align: top"><p><strong>SQL Query:</strong></p></td>
304  <td class="sq-backend-table-cell" style="vertical-align: top"><p><?php
305  if (!empty($settings['sql'])) {
306  echo ellipsisize(preg_replace('/\\n/', ' ', htmlspecialchars($settings['sql'])), 512);
307  } else {
308  ?><span class="sq-backend-warning">No SQL query specified.</span><?php
309  } ?></p></td>
310  </tr>
311  </tbody>
312  </table>
313  <?php
314 
315  }//end paintSummary()
316 
317 
322  public static function execute(Form $form, $settings)
323  {
324  $result = Array();
325 
326  $db = self::_connectToDB($form, $settings);
327  if ($db === FALSE) return FALSE;
328  $sql = trim($settings['sql'], ';');
329 
330  $found = 0;
331 
332  // We will replace all responses, including raw values.
333  // We will also replace keywords relating to the form submission asset
334  // (eg. submission assetid, time, IP address)
335  $matches = Array();
336  $found = preg_match_all('/%(response_(\\d+_)?q\\d+(_raw)?)%/U', $sql, $set_matches, PREG_SET_ORDER);
337  $matches = array_merge($matches, $set_matches);
338  $found = preg_match_all('/%(form_submission_.*)%/U', $sql, $set_matches, PREG_SET_ORDER);
339  $matches = array_merge($matches, $set_matches);
340 
341  $i = 1;
342  $bind_vars = Array();
343  foreach ($matches as $match) {
344  if (empty($match)) continue;
345  $count = 0;
346  do {
347  $sql = preg_replace('/%'.$match[1].'%/U', ':response_'.$i, $sql, 1, $count);
348  if ($count > 0) {
349  $bind_vars['response_'.$i] = html_entity_decode($form->_getThankYouKeywordReplacement($match[1]));
350  $i++;
351  }
352  } while ($count > 0);
353  }
354 
355  try {
356  MatrixDAL::changeDb('form_action_db_query_'.$form->id);
357  $query = MatrixDAL::preparePdoQuery($sql);
358  foreach ($bind_vars as $bind_var => $bind_value) {
359  MatrixDAL::bindValueToPdo($query, $bind_var, $bind_value);
360  }
361  MatrixDAL::execPdoQuery($query);
362  MatrixDAL::restoreDb();
363  } catch (Exception $e) {
364  trigger_localised_error('DATA0001', E_USER_WARNING, $e->getMessage(), $sql);
365  return $result;
366  }
367 
368  return TRUE;
369 
370  }//end execute()
371 
372 
377  public static function isValid(Form $form, $settings)
378  {
379  self::_fillDefaults($settings);
380 
381  // Must have either a DSN or a DB connector to be valid
382  if (empty($settings['dsn']) && empty($settings['connector_assetid'])) {
383  return FALSE;
384  }
385 
386  // SQL must also be filled in
387  if (empty($settings['sql'])) {
388  return FALSE;
389  }
390 
391  return TRUE;
392 
393  }//end isValid()
394 
395 
403  private static function _fillDefaults(&$settings) {
404  if (!isset($settings['method'])) {
405  $settings['method'] = 'table';
406  }
407 
408  if (!isset($settings['db_type'])) {
409  $settings['db_type'] = '';
410  }
411 
412  if (!isset($settings['database'])) {
413  $settings['database'] = '';
414  }
415 
416  if (!isset($settings['host'])) {
417  $settings['host'] = '';
418  }
419 
420  if (!isset($settings['dsn'])) {
421  $settings['dsn'] = '';
422  }
423 
424  if (!isset($settings['username'])) {
425  $settings['username'] = '';
426  }
427 
428  if (!isset($settings['password'])) {
429  $settings['password'] = '';
430  }
431 
432  if (!isset($settings['sql'])) {
433  $settings['sql'] = '';
434  }
435 
436  if (!isset($settings['connector_assetid'])) {
437  $settings['connector_assetid'] = 0;
438  }
439 
440  }//end _fillDefaults()
441 
442 
448  private static function _connectToDB(Form $form, $settings)
449  {
450  if (!empty($settings['dsn'])) {
451  $dsn_string = $settings['dsn'];
452  $username = $settings['username'];
453  $password = $settings['password'];
454 
455  $db_type = substr($dsn_string, 0, strpos($dsn_string, ':'));
456 
457  $dsn = Array(
458  'DSN' => $dsn_string,
459  'user' => $username,
460  'password' => $password,
461  'type' => $db_type,
462  );
463 
464  } else if (!empty($settings['connector_assetid'])) {
465  $connector = $GLOBALS['SQ_SYSTEM']->am->getAsset($settings['connector_assetid']);
466  $dsn = $connector->getDSN();
467  } else {
468  return FALSE;
469  }
470 
471  $db = MatrixDAL::dbConnect($dsn, 'form_action_db_query_'.$form->id);
472  return $db;
473 
474  }//end connectToDB()
475 
476 
477 }//end class
478 
479 ?>