From 2df5a673112d41f2045720572fce7af74194f1fe Mon Sep 17 00:00:00 2001
From: Benjamin Franzke <benjaminfranzke@googlemail.com>
Date: Thu, 24 Jan 2013 17:18:46 +0100
Subject: Add report of queries for a sqlbox

---
 sqlbox/db/access.php      | 11 ++++++++++
 sqlbox/lang/en/sqlbox.php |  1 +
 sqlbox/lib.php            |  7 +++++++
 sqlbox/report.php         | 51 +++++++++++++++++++++++++++++++++++++++++++++++
 sqlbox/version.php        |  2 +-
 5 files changed, 71 insertions(+), 1 deletion(-)
 create mode 100644 sqlbox/report.php

diff --git a/sqlbox/db/access.php b/sqlbox/db/access.php
index 12f678c..5213fc1 100644
--- a/sqlbox/db/access.php
+++ b/sqlbox/db/access.php
@@ -62,6 +62,17 @@ $capabilities = array(
         'clonepermissionsfrom' => 'moodle/course:manageactivities'
     ),
 
+    'mod/sqlbox:readqueries' => array(
+
+        'captype' => 'read',
+        'contextlevel' => CONTEXT_MODULE,
+        'archetypes' => array(
+            'teacher' => CAP_ALLOW,
+            'editingteacher' => CAP_ALLOW,
+            'manager' => CAP_ALLOW
+        )
+    ),
+
 /***************************** remove these comment marks and modify the code as needed
     'mod/sqlbox:view' => array(
         'captype' => 'read',
diff --git a/sqlbox/lang/en/sqlbox.php b/sqlbox/lang/en/sqlbox.php
index a522198..b0164b6 100644
--- a/sqlbox/lang/en/sqlbox.php
+++ b/sqlbox/lang/en/sqlbox.php
@@ -40,3 +40,4 @@ $string['sqlbox'] = 'sqlbox';
 $string['sqlbox:addinstancce'] = 'Add a new sqlbox';
 $string['pluginadministration'] = 'sqlbox administration';
 $string['pluginname'] = 'sqlbox';
+$string['viewresults'] = 'View Results';
diff --git a/sqlbox/lib.php b/sqlbox/lib.php
index 0e9d5c5..f762532 100644
--- a/sqlbox/lib.php
+++ b/sqlbox/lib.php
@@ -390,4 +390,11 @@ function sqlbox_extend_navigation(navigation_node $navref, stdclass $course, std
  * @param navigation_node $sqlboxnode {@link navigation_node}
  */
 function sqlbox_extend_settings_navigation(settings_navigation $settingsnav, navigation_node $sqlboxnode=null) {
+    global $PAGE;
+
+    if (has_capability('mod/sqlbox:readqueries', $PAGE->cm->context)) {
+        $sqlboxnode->add(get_string('viewresults', 'sqlbox'),
+            new moodle_url('/mod/sqlbox/report.php',
+                array('id'=>$PAGE->cm->id)));
+    }
 }
diff --git a/sqlbox/report.php b/sqlbox/report.php
new file mode 100644
index 0000000..c39114d
--- /dev/null
+++ b/sqlbox/report.php
@@ -0,0 +1,51 @@
+<?php
+
+require_once(dirname(dirname(dirname(__FILE__))).'/config.php');
+require_once(dirname(__FILE__).'/lib.php');
+
+$id  = required_param('id', PARAM_INT);   //moduleid
+
+if ($id) {
+    $cm         = get_coursemodule_from_id('sqlbox', $id, 0, false, MUST_EXIST);
+    $course     = $DB->get_record('course', array('id' => $cm->course), '*', MUST_EXIST);
+    $sqlbox  = $DB->get_record('sqlbox', array('id' => $cm->instance), '*', MUST_EXIST);
+} else {
+    error('You must specify a course_module ID or an instance ID');
+}
+
+require_login($course, true, $cm);
+
+$context = get_context_instance(CONTEXT_MODULE, $cm->id);
+require_capability('mod/sqlbox:readqueries', $context);
+
+add_to_log($course->id, 'sqlbox', 'report', "report.php?id={$cm->id}", $sqlbox->name, $cm->id);
+
+$PAGE->set_url('/mod/sqlbox/report.php', array('id' => $cm->id));
+$PAGE->set_title(format_string($sqlbox->name));
+$PAGE->set_heading(format_string($course->fullname));
+$PAGE->set_context($context);
+
+echo $OUTPUT->header();
+
+$sql = "SELECT u.*, s.*
+          FROM {user} u, {sqlbox_solutions} s
+        WHERE
+          u.id = s.userid and
+          s.sqlboxid = :id";
+$records = $DB->get_records_sql($sql, array('id' => $sqlbox->id));
+if ($records) {
+    $table = new html_table;
+    $table->head = array("Lösung", "Nutzer", "Anfrage");
+    foreach ($records as $record) {
+        $table->data[] = array(
+            ($record->correct == 1) ? '☑' : '☒',
+            $record->firstname . ' ' . $record->lastname,
+            $record->query
+        );
+    }
+    echo html_writer::table($table);
+}
+
+echo $OUTPUT->footer();
+
+?>
diff --git a/sqlbox/version.php b/sqlbox/version.php
index e1a165d..5c4d1b6 100644
--- a/sqlbox/version.php
+++ b/sqlbox/version.php
@@ -30,7 +30,7 @@
 
 defined('MOODLE_INTERNAL') || die();
 
-$module->version   = 2013012403;      // The current module version (Date: YYYYMMDDXX)
+$module->version   = 2013012404;      // The current module version (Date: YYYYMMDDXX)
 $module->requires  = 2010031900;      // Requires this Moodle version
 $module->cron      = 0;               // Period for cron to check this module (secs)
 $module->component = 'mod_sqlbox'; // To check on upgrade, that module sits in correct place
-- 
cgit