MDL-14018 fixed ugly query - thanks Eloy; merged from MOODLE_19_STABLE
[moodle.git] / mod / data / view.php
CommitLineData
3d4b223a 1<?php // $Id$
2///////////////////////////////////////////////////////////////////////////
3// //
4// NOTICE OF COPYRIGHT //
5// //
6// Moodle - Modular Object-Oriented Dynamic Learning Environment //
7// http://moodle.org //
8// //
9// Copyright (C) 2005 Martin Dougiamas http://dougiamas.com //
10// //
11// This program is free software; you can redistribute it and/or modify //
12// it under the terms of the GNU General Public License as published by //
13// the Free Software Foundation; either version 2 of the License, or //
14// (at your option) any later version. //
15// //
16// This program is distributed in the hope that it will be useful, //
17// but WITHOUT ANY WARRANTY; without even the implied warranty of //
18// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the //
19// GNU General Public License for more details: //
20// //
21// http://www.gnu.org/copyleft/gpl.html //
22// //
23///////////////////////////////////////////////////////////////////////////
24
25 require_once('../../config.php');
26 require_once('lib.php');
27 require_once($CFG->libdir.'/blocklib.php');
a593aeee 28 require_once("$CFG->libdir/rsslib.php");
3d4b223a 29
3d4b223a 30 require_once('pagelib.php');
9f7f1a74 31
3d45b8e5 32/// One of these is necessary!
3d4b223a 33 $id = optional_param('id', 0, PARAM_INT); // course module id
34 $d = optional_param('d', 0, PARAM_INT); // database id
3d45b8e5 35 $rid = optional_param('rid', 0, PARAM_INT); //record id
36
37 $mode = optional_param('mode', '', PARAM_ALPHA); // Force the browse mode ('single')
38
d2b23346 39 $edit = optional_param('edit', -1, PARAM_BOOL);
7900ecb0 40 $page = optional_param('page', 0, PARAM_INT);
3d45b8e5 41/// These can be added to perform an action on a record
473dd288 42 $approve = optional_param('approve', 0, PARAM_INT); //approval recordid
43 $delete = optional_param('delete', 0, PARAM_INT); //delete recordid
aab98aaf 44
3d4b223a 45 if ($id) {
f9d5371b 46 if (! $cm = get_coursemodule_from_id('data', $id)) {
714bec74 47 error('Course Module ID was incorrect');
3d4b223a 48 }
49 if (! $course = get_record('course', 'id', $cm->course)) {
714bec74 50 error('Course is misconfigured');
3d4b223a 51 }
52 if (! $data = get_record('data', 'id', $cm->instance)) {
714bec74 53 error('Course module is incorrect');
3d4b223a 54 }
3d45b8e5 55 $record = NULL;
3d4b223a 56
3d45b8e5 57 } else if ($rid) {
58 if (! $record = get_record('data_records', 'id', $rid)) {
714bec74 59 error('Record ID is incorrect');
3d45b8e5 60 }
61 if (! $data = get_record('data', 'id', $record->dataid)) {
714bec74 62 error('Data ID is incorrect');
3d45b8e5 63 }
64 if (! $course = get_record('course', 'id', $data->course)) {
714bec74 65 error('Course is misconfigured');
3d45b8e5 66 }
67 if (! $cm = get_coursemodule_from_instance('data', $data->id, $course->id)) {
714bec74 68 error('Course Module ID was incorrect');
3d45b8e5 69 }
70 } else { // We must have $d
3d4b223a 71 if (! $data = get_record('data', 'id', $d)) {
714bec74 72 error('Data ID is incorrect');
3d4b223a 73 }
74 if (! $course = get_record('course', 'id', $data->course)) {
714bec74 75 error('Course is misconfigured');
3d4b223a 76 }
77 if (! $cm = get_coursemodule_from_instance('data', $data->id, $course->id)) {
714bec74 78 error('Course Module ID was incorrect');
3d4b223a 79 }
3d45b8e5 80 $record = NULL;
3d4b223a 81 }
82
7ddda9db 83 require_course_login($course, true, $cm);
aab98aaf 84
dabfd0ed 85 $context = get_context_instance(CONTEXT_MODULE, $cm->id);
20821a12 86 require_capability('mod/data:viewentry', $context);
3d1c33ef 87
3d45b8e5 88/// If we have an empty Database then redirect because this page is useless without data
0468976c 89 if (has_capability('mod/data:managetemplates', $context)) {
ed69c723 90 if (!record_exists('data_fields','dataid',$data->id)) { // Brand new database!
5bac6d10 91 redirect($CFG->wwwroot.'/mod/data/field.php?d='.$data->id); // Redirect to field entry
3d4b223a 92 }
93 }
e0279f63 94
3d45b8e5 95
96/// Check further parameters that set browsing preferences
97 if (!isset($SESSION->dataprefs)) {
98 $SESSION->dataprefs = array();
e0279f63 99 }
3d45b8e5 100 if (!isset($SESSION->dataprefs[$data->id])) {
101 $SESSION->dataprefs[$data->id] = array();
102 $SESSION->dataprefs[$data->id]['search'] = '';
7900ecb0 103 $SESSION->dataprefs[$data->id]['search_array'] = array();
3d45b8e5 104 $SESSION->dataprefs[$data->id]['sort'] = $data->defaultsort;
7900ecb0 105 $SESSION->dataprefs[$data->id]['advanced'] = 0;
3d45b8e5 106 $SESSION->dataprefs[$data->id]['order'] = ($data->defaultsortdir == 0) ? 'ASC' : 'DESC';
3d4b223a 107 }
de8ff581 108
eeeb4f2a 109 // reset advanced form
110 if (!is_null(optional_param('resetadv', null, PARAM_RAW))) {
111 $SESSION->dataprefs[$data->id]['search_array'] = array();
112 // we need the redirect to cleanup the form state properly
113 redirect("view.php?id=$cm->id&amp;mode=$mode&amp;search=&amp;advanced=1");
114 }
115
de8ff581 116 $advanced = optional_param('advanced', -1, PARAM_INT);
117 if ($advanced == -1) {
118 $advanced = $SESSION->dataprefs[$data->id]['advanced'];
119 } else {
120 if (!$advanced) {
121 // explicitly switched to normal mode - discard all advanced search settings
122 $SESSION->dataprefs[$data->id]['search_array'] = array();
123 }
832123e1 124 $SESSION->dataprefs[$data->id]['advanced'] = $advanced;
de8ff581 125 }
126
7900ecb0 127 $search_array = $SESSION->dataprefs[$data->id]['search_array'];
eeeb4f2a 128
7900ecb0 129 if (!empty($advanced)) {
130 $search = '';
69c0a609 131 $vals = array();
7900ecb0 132 $fields = get_records('data_fields', 'dataid', $data->id);
133
134 //Added to ammend paging error. This error would occur when attempting to go from one page of advanced
135 //search results to another. All fields were reset in the page transfer, and there was no way of determining
136 //whether or not the user reset them. This would cause a blank search to execute whenever the user attempted
137 //to see any page of results past the first.
138 //This fix works as follows:
139 //$paging flag is set to false when page 0 of the advanced search results is viewed for the first time.
140 //Viewing any page of results after page 0 passes the false $paging flag though the URL (see line 523) and the
141 //execution falls through to the second condition below, allowing paging to be set to true.
142 //Paging remains true and keeps getting passed though the URL until a new search is performed
143 //(even if page 0 is revisited).
144 //A false $paging flag generates advanced search results based on the fields input by the user.
145 //A true $paging flag generates davanced search results from the $SESSION global.
146 //(See lines 147-158)
147
148 $paging = optional_param('paging', NULL, PARAM_BOOL);
149 if($page == 0 && !isset($paging)) {
150 $paging = false;
151 }
152 else {
153 $paging = true;
154 }
714bec74 155 if (!empty($fields)) {
7900ecb0 156 foreach($fields as $field) {
157 $searchfield = data_get_field_from_id($field->id, $data);
158 //Get field data to build search sql with. If paging is false, get from user.
159 //If paging is true, get data from $search_array which is obtained from the $SESSION (see line 116).
160 if(!$paging) {
161 $val = $searchfield->parse_search_field();
714bec74 162 } else {
7900ecb0 163 //Set value from session if there is a value @ the required index.
714bec74 164 if (isset($search_array[$field->id])) {
7900ecb0 165 $val = $search_array[$field->id]->data;
714bec74 166 } else { //If there is not an entry @ the required index, set value to blank.
7900ecb0 167 $val = '';
168 }
7900ecb0 169 }
714bec74 170 if (!empty($val)) {
171 $search_array[$field->id] = new object();
172 $search_array[$field->id]->sql = $searchfield->generate_sql('c'.$field->id, $val);
173 $search_array[$field->id]->data = $val;
69c0a609 174 $vals[] = $val;
714bec74 175 } else {
176 // clear it out
177 unset($search_array[$field->id]);
7900ecb0 178 }
179 }
180 }
714bec74 181
182 if (!$paging) {
183 // name searching
184 $fn = optional_param('u_fn', '', PARAM_NOTAGS);
185 $ln = optional_param('u_ln', '', PARAM_NOTAGS);
186 } else {
de8ff581 187 $fn = isset($search_array[DATA_FIRSTNAME]) ? $search_array[DATA_FIRSTNAME]->data : '';
188 $ln = isset($search_array[DATA_LASTNAME]) ? $search_array[DATA_LASTNAME]->data : '';
714bec74 189 }
190 if (!empty($fn)) {
191 $search_array[DATA_FIRSTNAME] = new object();
192 $search_array[DATA_FIRSTNAME]->sql = '';
193 $search_array[DATA_FIRSTNAME]->field = 'u.firstname';
194 $search_array[DATA_FIRSTNAME]->data = $fn;
69c0a609 195 $vals[] = $fn;
714bec74 196 } else {
197 unset($search_array[DATA_FIRSTNAME]);
198 }
199 if (!empty($ln)) {
200 $search_array[DATA_LASTNAME] = new object();
201 $search_array[DATA_LASTNAME]->sql = '';
202 $search_array[DATA_LASTNAME]->field = 'u.lastname';
203 $search_array[DATA_LASTNAME]->data = $ln;
69c0a609 204 $vals[] = $ln;
714bec74 205 } else {
206 unset($search_array[DATA_LASTNAME]);
207 }
208
7900ecb0 209 $SESSION->dataprefs[$data->id]['search_array'] = $search_array; // Make it sticky
714bec74 210
69c0a609 211 // in case we want to switch to simple search later - there might be multiple values there ;-)
212 if ($vals) {
213 $val = reset($vals);
214 if (is_string($val)) {
215 $search = $val;
216 }
217 }
218
714bec74 219 } else {
7900ecb0 220 $search = optional_param('search', $SESSION->dataprefs[$data->id]['search'], PARAM_NOTAGS);
221 //Paging variable not used for standard search. Set it to null.
222 $paging = NULL;
223 }
224
8e1ec6be 225 $textlib = textlib_get_instance();
76a2fd82 226 if ($textlib->strlen($search) < 2) {
227 $search = '';
228 }
3d45b8e5 229 $SESSION->dataprefs[$data->id]['search'] = $search; // Make it sticky
230
231 $sort = optional_param('sort', $SESSION->dataprefs[$data->id]['sort'], PARAM_INT);
232 $SESSION->dataprefs[$data->id]['sort'] = $sort; // Make it sticky
233
234 $order = (optional_param('order', $SESSION->dataprefs[$data->id]['order'], PARAM_ALPHA) == 'ASC') ? 'ASC': 'DESC';
235 $SESSION->dataprefs[$data->id]['order'] = $order; // Make it sticky
236
237
238 $oldperpage = get_user_preferences('data_perpage_'.$data->id, 10);
239 $perpage = optional_param('perpage', $oldperpage, PARAM_INT);
240
241 if ($perpage < 2) {
242 $perpage = 2;
243 }
244 if ($perpage != $oldperpage) {
245 set_user_preference('data_perpage_'.$data->id, $perpage);
246 }
247
3d4b223a 248 add_to_log($course->id, 'data', 'view', "view.php?id=$cm->id", $data->id, $cm->id);
249
250
251// Initialize $PAGE, compute blocks
252 $PAGE = page_create_instance($data->id);
253 $pageblocks = blocks_setup($PAGE);
254 $blocks_preferred_width = bounded_number(180, blocks_preferred_width($pageblocks[BLOCK_POS_LEFT]), 210);
255
d2b23346 256 if (($edit != -1) and $PAGE->user_allowed_editing()) {
257 $USER->editing = $edit;
3d4b223a 258 }
3d4b223a 259
b0100852 260/// RSS and CSS and JS meta
68635e6f 261 $meta = '';
64452eb4 262 if (!empty($CFG->enablerssfeeds) && !empty($CFG->data_enablerssfeeds) && $data->rssarticles > 0) {
c853304e 263 $rsspath = rss_get_url($course->id, $USER->id, 'data', $data->id);
68635e6f 264 $meta .= '<link rel="alternate" type="application/rss+xml" ';
6ba65fa0 265 $meta .= 'title ="'. format_string($course->shortname) .': %fullname%" href="'.$rsspath.'" />';
68635e6f 266 }
267 if ($data->csstemplate) {
268 $meta .= '<link rel="stylesheet" type="text/css" href="'.$CFG->wwwroot.'/mod/data/css.php?d='.$data->id.'" /> ';
c853304e 269 }
b0100852 270 if ($data->jstemplate) {
271 $meta .= '<script type="text/javascript" src="'.$CFG->wwwroot.'/mod/data/js.php?d='.$data->id.'"></script>';
272 }
273
aab98aaf 274
c853304e 275/// Print the page header
68635e6f 276 $PAGE->print_header($course->shortname.': %fullname%', '', $meta);
aab98aaf 277
3d4b223a 278
236d839f 279/// If we have blocks, then print the left side here
280 if (!empty($CFG->showblocksonmodpages)) {
281 echo '<table id="layout-table"><tr>';
282 if ((blocks_have_content($pageblocks, BLOCK_POS_LEFT) || $PAGE->user_is_editing())) {
283 echo '<td style="width: '.$blocks_preferred_width.'px;" id="left-column">';
9f7f1a74 284 print_container_start();
236d839f 285 blocks_print_group($PAGE, $pageblocks, BLOCK_POS_LEFT);
9f7f1a74 286 print_container_end();
236d839f 287 echo '</td>';
288 }
289 echo '<td id="middle-column">';
9f7f1a74 290 print_container_start();
3d4b223a 291 }
292
3b27b0fe 293/// Check to see if groups are being used here
13534ef7
ML
294 $returnurl = 'view.php?d='.$data->id.'&amp;search='.s($search).'&amp;sort='.s($sort).'&amp;order='.s($order).'&amp;';
295 groups_print_activity_menu($cm, $returnurl);
296 $currentgroup = groups_get_activity_group($cm);
297 $groupmode = groups_get_activity_groupmode($cm);
3b27b0fe 298
3d4b223a 299 print_heading(format_string($data->name));
aab98aaf 300
a593aeee 301 // Do we need to show a link to the RSS feed for the records?
64452eb4 302 if (!empty($CFG->enablerssfeeds) && !empty($CFG->data_enablerssfeeds) && $data->rssarticles > 0) {
a593aeee 303 echo '<div style="float:right;">';
ed69c723 304 rss_print_link($course->id, $USER->id, 'data', $data->id, get_string('rsstype'));
a593aeee 305 echo '</div>';
306 echo '<div style="clear:both;"></div>';
307 }
aab98aaf 308
9e08cf6e 309 if ($data->intro and empty($page) and empty($record) and $mode != 'single') {
c1afef80 310 print_box(format_text($data->intro), 'generalbox', 'intro');
56135f6b 311 }
312
473dd288 313/// Delete any requested records
314
046dd7dc 315 if ($delete && confirm_sesskey() && (has_capability('mod/data:manageentries', $context) or data_isowner($delete))) {
3d45b8e5 316 if ($confirm = optional_param('confirm',0,PARAM_INT)) {
317 if ($deleterecord = get_record('data_records', 'id', $delete)) { // Need to check this is valid
318 if ($deleterecord->dataid == $data->id) { // Must be from this database
319 if ($contents = get_records('data_content','recordid', $deleterecord->id)) {
320 foreach ($contents as $content) { // Delete files or whatever else this field allows
321 if ($field = data_get_field_from_id($content->fieldid, $data)) { // Might not be there
322 $field->delete_content($content->recordid);
323 }
3d4b223a 324 }
325 }
3d45b8e5 326 delete_records('data_content','recordid', $deleterecord->id);
327 delete_records('data_records','id', $deleterecord->id);
f0497d6f 328
3d45b8e5 329 add_to_log($course->id, 'data', 'record delete', "view.php?id=$cm->id", $data->id, $cm->id);
f0497d6f 330
3d45b8e5 331 notify(get_string('recorddeleted','data'), 'notifysuccess');
332 }
3d4b223a 333 }
3d45b8e5 334
335 } else { // Print a confirmation page
64452eb4 336 if ($deleterecord = get_record('data_records', 'id', $delete)) { // Need to check this is valid
337 if ($deleterecord->dataid == $data->id) { // Must be from this database
aab98aaf 338 notice_yesno(get_string('confirmdeleterecord','data'),
64452eb4 339 'view.php?d='.$data->id.'&amp;delete='.$delete.'&amp;confirm=1&amp;sesskey='.sesskey(),
340 'view.php?d='.$data->id);
3d45b8e5 341
64452eb4 342 $records[] = $deleterecord;
3f9672d3 343 echo data_print_template('singletemplate', $records, $data, '', 0, true);
64452eb4 344
345 print_footer($course);
346 exit;
347 }
348 }
349 }
350 }
351
352
353
354/// Print the tabs
355
356 if ($record or $mode == 'single') {
357 $currenttab = 'single';
7900ecb0 358 } elseif($mode == 'asearch') {
359 $currenttab = 'asearch';
360 }
361 else {
64452eb4 362 $currenttab = 'list';
363 }
aab98aaf 364 include('tabs.php');
64452eb4 365
eeeb4f2a 366 if ($mode == 'asearch') {
367 $maxcount = 0;
368
369 } else {
7900ecb0 370 /// Approve any requested records
64452eb4 371
7900ecb0 372 if ($approve && confirm_sesskey() && has_capability('mod/data:approve', $context)) {
373 if ($approverecord = get_record('data_records', 'id', $approve)) { // Need to check this is valid
374 if ($approverecord->dataid == $data->id) { // Must be from this database
375 $newrecord->id = $approverecord->id;
376 $newrecord->approved = 1;
377 if (update_record('data_records', $newrecord)) {
378 notify(get_string('recordapproved','data'), 'notifysuccess');
379 }
64452eb4 380 }
381 }
3d4b223a 382 }
87518137 383
eeeb4f2a 384 // Check the number of entries required against the number of entries already made (doesn't apply to teachers)
385 $requiredentries_allowed = true;
386 $numentries = data_numentries($data);
387 if ($data->requiredentries > 0 && $numentries < $data->requiredentries && !has_capability('mod/data:manageentries', $context)) {
388 $data->entriesleft = $data->requiredentries - $numentries;
389 $strentrieslefttoadd = get_string('entrieslefttoadd', 'data', $data);
390 notify($strentrieslefttoadd);
391 $requiredentries_allowed = false;
392 }
3d4b223a 393
7900ecb0 394 /// We need to examine the whole dataset to produce the correct paging
33819735 395
7900ecb0 396 if ((!has_capability('mod/data:managetemplates', $context)) && ($data->approval)) {
397 if (isloggedin()) {
398 $approveselect = ' AND (r.approved=1 OR r.userid='.$USER->id.') ';
399 } else {
400 $approveselect = ' AND r.approved=1 ';
401 }
4431d2e0 402 } else {
7900ecb0 403 $approveselect = ' ';
4431d2e0 404 }
3d4b223a 405
7900ecb0 406 if ($currentgroup) {
407 $groupselect = " AND (r.groupid = '$currentgroup' OR r.groupid = 0)";
64452eb4 408 } else {
7900ecb0 409 $groupselect = ' ';
64452eb4 410 }
3d45b8e5 411
026d562e 412 $ilike = sql_ilike(); //Be case-insensitive
413
7900ecb0 414 /// Find the field we are sorting on
fb9d1e09 415 if ($sort == DATA_FIRSTNAME or $sort == DATA_LASTNAME or empty($sort)) {
714bec74 416
fb9d1e09 417 if ($sort == DATA_LASTNAME) {
714bec74 418 $ordering = "u.lastname $order, u.firstname $order";
6a5357f4 419 } else if ($sort == DATA_FIRSTNAME) {
714bec74 420 $ordering = "u.firstname $order, u.lastname $order";
fb9d1e09 421 } else {
422 $ordering = "r.timecreated $order";
714bec74 423 }
424
425 $what = ' DISTINCT r.id, r.approved, r.timecreated, r.timemodified, r.userid, u.firstname, u.lastname';
426 $count = ' COUNT(DISTINCT c.recordid) ';
eeeb4f2a 427 $rids = ' DISTINCT c.recordid ';
714bec74 428 $tables = $CFG->prefix.'data_content c,'.$CFG->prefix.'data_records r,'.$CFG->prefix.'data_content cs, '.$CFG->prefix.'user u ';
429 $where = 'WHERE c.recordid = r.id
430 AND r.dataid = '.$data->id.'
431 AND r.userid = u.id
432 AND cs.recordid = r.id ';
433 $sortorder = ' ORDER BY '.$ordering.', r.id ASC ';
434 $searchselect = '';
435
436 // If requiredentries is not reached, only show current user's entries
437 if (!$requiredentries_allowed) {
438 $where .= ' AND u.id = ' . $USER->id;
439 }
440
441 if (!empty($advanced)) { //If advanced box is checked.
442 foreach($search_array as $key => $val) { //what does $search_array hold?
443 if ($key == DATA_FIRSTNAME or $key == DATA_LASTNAME) {
026d562e 444 $searchselect .= " AND $val->field $ilike '%{$val->data}%'";
714bec74 445 continue;
446 }
447 $tables .= ', '.$CFG->prefix.'data_content c'.$key.' ';
448 $where .= ' AND c'.$key.'.recordid = r.id';
449 $searchselect .= ' AND ('.$val->sql.') ';
450 }
451 } else if ($search) {
714bec74 452 $searchselect = " AND (cs.content $ilike '%$search%' OR u.firstname $ilike '%$search%' OR u.lastname $ilike '%$search%' ) ";
453 } else {
454 $searchselect = ' ';
455 }
456
fb9d1e09 457 } else if ($sort > 0 and $sortfield = data_get_field_from_id($sort, $data)) {
7900ecb0 458
459 $sortcontent = $sortfield->get_sort_field();
460 $sortcontentfull = $sortfield->get_sort_sql('c.'.$sortcontent);
714bec74 461
462 $what = ' DISTINCT r.id, r.approved, r.timecreated, r.timemodified, r.userid, u.firstname, u.lastname, c.'.$sortcontent.', '.$sortcontentfull.' AS _order ';
7900ecb0 463 $count = ' COUNT(DISTINCT c.recordid) ';
eeeb4f2a 464 $rids = ' DISTINCT c.recordid ';
714bec74 465 $tables = $CFG->prefix.'data_content c,'.$CFG->prefix.'data_records r,'.$CFG->prefix.'data_content cs, '.$CFG->prefix.'user u ';
7900ecb0 466 $where = 'WHERE c.recordid = r.id
467 AND c.fieldid = '.$sort.'
468 AND r.dataid = '.$data->id.'
469 AND r.userid = u.id
714bec74 470 AND cs.recordid = r.id ';
b99b25ea 471 $sortorder = ' ORDER BY _order '.$order.' , r.id ASC ';
7900ecb0 472 $searchselect = '';
714bec74 473
87518137 474 // If requiredentries is not reached, only show current user's entries
475 if (!$requiredentries_allowed) {
476 $where .= ' AND u.id = ' . $USER->id;
477 }
714bec74 478
479 if (!empty($advanced)) { //If advanced box is checked.
480 foreach($search_array as $key => $val) { //what does $search_array hold?
481 if ($key == DATA_FIRSTNAME or $key == DATA_LASTNAME) {
026d562e 482 $searchselect .= " AND $val->field $ilike '%{$val->data}%'";
714bec74 483 continue;
484 }
7900ecb0 485 $tables .= ', '.$CFG->prefix.'data_content c'.$key.' ';
486 $where .= ' AND c'.$key.'.recordid = r.id';
487 $searchselect .= ' AND ('.$val->sql.') ';
488 }
714bec74 489 } else if ($search) {
714bec74 490 $searchselect = " AND (cs.content $ilike '%$search%' OR u.firstname $ilike '%$search%' OR u.lastname $ilike '%$search%' ) ";
7900ecb0 491 } else {
492 $searchselect = ' ';
714bec74 493 }
494
7900ecb0 495 } else if ($search) {
714bec74 496 $what = ' DISTINCT r.id, r.approved, r.timecreated, r.timemodified, r.userid, u.firstname, u.lastname ';
7900ecb0 497 $count = ' COUNT(DISTINCT c.recordid) ';
eeeb4f2a 498 $rids = ' DISTINCT c.recordid ';
7900ecb0 499 $tables = $CFG->prefix.'data_content c,'.$CFG->prefix.'data_records r, '.$CFG->prefix.'user u ';
500 $where = 'WHERE c.recordid = r.id
501 AND r.userid = u.id
502 AND r.dataid = '.$data->id;
503 $sortorder = ' ORDER BY r.id ASC ';
504 $searchselect = '';
714bec74 505
87518137 506 // If requiredentries is not reached, only show current user's entries
507 if (!$requiredentries_allowed) {
508 $where .= ' AND u.id = ' . $USER->id;
509 }
714bec74 510
7900ecb0 511 if (!empty($advanced)) { //Advanced search box again.
512 foreach($search_array as $key => $val) {
714bec74 513 if ($key == DATA_FIRSTNAME or $key == DATA_LASTNAME) {
026d562e 514 $searchselect .= " AND $val->field $ilike '%{$val->data}%'";
714bec74 515 continue;
516 }
7900ecb0 517 $tables .= ', '.$CFG->prefix.'data_content c'.$key.' ';
518 $where .= ' AND c'.$key.'.recordid = r.id ';
519 $searchselect .= ' AND ('.$val->sql.') ';
520 }
714bec74 521 } else {
714bec74 522 $searchselect = " AND (c.content $ilike '%$search%' OR u.firstname $ilike '%$search%' OR u.lastname $ilike '%$search%' ) ";
7900ecb0 523 }
3d45b8e5 524
714bec74 525
7900ecb0 526 } else {
c95034f2 527 $what = ' DISTINCT r.id, r.approved, r.timecreated, r.timemodified, r.userid, u.firstname, u.lastname ';
7900ecb0 528 $count = ' COUNT(r.id) ';
eeeb4f2a 529 $rids = ' COUNT(r.id) ';
7900ecb0 530 $tables = $CFG->prefix.'data_records r, '.$CFG->prefix.'user u ';
531 $where = 'WHERE r.dataid = '.$data->id. ' AND r.userid = u.id ';
532 $sortorder = ' ORDER BY r.timecreated '.$order. ' ';
533 $searchselect = ' ';
714bec74 534
87518137 535 // If requiredentries is not reached, only show current user's entries
536 if (!$requiredentries_allowed) {
537 $where .= ' AND u.id = ' . $USER->id;
538 }
7900ecb0 539 }
3d4b223a 540
64452eb4 541
7900ecb0 542 /// To actually fetch the records
3d45b8e5 543
eeeb4f2a 544 $fromsql = "FROM $tables $where $groupselect $approveselect $searchselect";
545 $sqlselect = "SELECT $what $fromsql $sortorder";
546 $sqlcount = "SELECT $count $fromsql"; // Total number of records when searching
547 $sqlrids = "SELECT $rids $fromsql";
548 $sqlmax = "SELECT $count FROM $tables $where $groupselect $approveselect"; // number of all recoirds user may see
3d45b8e5 549
eeeb4f2a 550 /// Work out the paging numbers and counts
3d45b8e5 551
7900ecb0 552 $totalcount = count_records_sql($sqlcount);
eeeb4f2a 553 if (empty($searchselect)) {
554 $maxcount = $totalcount;
555 } else {
556 $maxcount = count_records_sql($sqlmax);
557 }
3d4b223a 558
7900ecb0 559 if ($record) { // We need to just show one, so where is it in context?
560 $nowperpage = 1;
561 $mode = 'single';
de89899b 562
7900ecb0 563 $page = 0;
eeeb4f2a 564 if ($allrecords = get_records_sql($sqlrids)) { // Kludgey but accurate at least!
565 foreach ($allrecords as $key => $unused) {
714bec74 566 if ($key == $record->id) {
567 break;
568 }
569 $page++;
7900ecb0 570 }
eeeb4f2a 571 unset($allrecords);
3d45b8e5 572 }
3d45b8e5 573
7900ecb0 574 } else if ($mode == 'single') { // We rely on ambient $page settings
575 $nowperpage = 1;
3d45b8e5 576
7900ecb0 577 } else {
578 $nowperpage = $perpage;
579 }
3d45b8e5 580
7900ecb0 581 /// Get the actual records
582
eeeb4f2a 583 if (!$records = get_records_sql($sqlselect, $page * $nowperpage, $nowperpage)) {
584 // Nothing to show!
7900ecb0 585 if ($record) { // Something was requested so try to show that at least (bug 5132)
586 if (has_capability('mod/data:manageentries', $context) || empty($data->approval) ||
587 $record->approved || (isloggedin() && $record->userid == $USER->id)) {
588 if (!$currentgroup || $record->groupid == $currentgroup || $record->groupid == 0) {
eeeb4f2a 589 // OK, we can show this one
590 $records = array($record->id => $record);
591 $totalcount = 1;
7900ecb0 592 }
3dec563c 593 }
594 }
eeeb4f2a 595 }
596
597 if (empty($records)) {
598 if ($maxcount){
599 $a = new object();
600 $a->max = $maxcount;
601 $a->reseturl = "view.php?id=$cm->id&amp;mode=$mode&amp;search=&amp;advanced=0";
602 notify(get_string('foundnorecords','data', $a));
7900ecb0 603 } else {
604 notify(get_string('norecords','data'));
605 }
cf3e199b 606
eeeb4f2a 607 } else { // We have some records to print
608
609 if ($maxcount != $totalcount) {
610 $a = new object();
611 $a->num = $totalcount;
612 $a->max = $maxcount;
613 $a->reseturl = "view.php?id=$cm->id&amp;mode=$mode&amp;search=&amp;advanced=0";
614 notify(get_string('foundrecords', 'data', $a), 'notifysuccess');
615 }
68c88622 616
7900ecb0 617 if ($mode == 'single') { // Single template
618 $baseurl = 'view.php?d='.$data->id.'&amp;mode=single&amp;';
3d4b223a 619
7900ecb0 620 print_paging_bar($totalcount, $page, $nowperpage, $baseurl, $pagevar='page');
3d45b8e5 621
7900ecb0 622 if (empty($data->singletemplate)){
623 notify(get_string('nosingletemplate','data'));
624 data_generate_default_template($data, 'singletemplate', 0, false, false);
625 }
3d4b223a 626
7900ecb0 627 data_print_template('singletemplate', $records, $data, $search, $page);
3d45b8e5 628
7900ecb0 629 print_paging_bar($totalcount, $page, $nowperpage, $baseurl, $pagevar='page');
3d45b8e5 630
7900ecb0 631 } else { // List template
632 $baseurl = 'view.php?d='.$data->id.'&amp;';
633 //send the advanced flag through the URL so it is remembered while paging.
634 $baseurl .= 'advanced='.$advanced.'&amp;';
635 //pass variable to allow determining whether or not we are paging through results.
636 $baseurl .= 'paging='.$paging.'&amp;';
3d45b8e5 637
7900ecb0 638 print_paging_bar($totalcount, $page, $nowperpage, $baseurl, $pagevar='page');
3d45b8e5 639
7900ecb0 640 if (empty($data->listtemplate)){
641 notify(get_string('nolisttemplate','data'));
642 data_generate_default_template($data, 'listtemplate', 0, false, false);
643 }
644 echo $data->listtemplateheader;
645 data_print_template('listtemplate', $records, $data, $search, $page);
646 echo $data->listtemplatefooter;
647
648 print_paging_bar($totalcount, $page, $nowperpage, $baseurl, $pagevar='page');
3d45b8e5 649 }
3d45b8e5 650
3d45b8e5 651 }
7900ecb0 652 }
653
654 $search = trim($search);
655 if (empty($records)) {
656 $records = array();
f852d652 657 }
658
7900ecb0 659 //Advanced search form doesn't make sense for single (redirects list view)
eeeb4f2a 660 if (($maxcount || $mode == 'asearch') && $mode != 'single') {
7900ecb0 661 data_print_preference_form($data, $perpage, $search, $sort, $order, $search_array, $advanced, $mode);
64452eb4 662 }
3d4b223a 663
236d839f 664/// If we have blocks, then print the left side here
665 if (!empty($CFG->showblocksonmodpages)) {
9f7f1a74 666 print_container_end();
236d839f 667 echo '</td>'; // Middle column
668 if ((blocks_have_content($pageblocks, BLOCK_POS_RIGHT) || $PAGE->user_is_editing())) {
669 echo '<td style="width: '.$blocks_preferred_width.'px;" id="right-column">';
9f7f1a74 670 print_container_start();
236d839f 671 blocks_print_group($PAGE, $pageblocks, BLOCK_POS_RIGHT);
9f7f1a74 672 print_container_end();
236d839f 673 echo '</td>';
674 }
78e02af9 675 echo '</tr></table>';
236d839f 676 }
677
3d45b8e5 678 print_footer($course);
0997e51a 679?>