From 7b7f10ba3917a6d5229f13d27aa4e94a9685b00e Mon Sep 17 00:00:00 2001
From: root <root@treefish.org>
Date: Mon, 8 Oct 2012 15:37:09 +0200
Subject: [PATCH 1/1] improved blackboard submits.

---
 admin/blackboard.php  | 16 ++++++++++------
 admin/nextsession.php |  2 +-
 admin/summary.php     |  2 +-
 include/db.inc        |  2 +-
 4 files changed, 13 insertions(+), 9 deletions(-)

diff --git a/admin/blackboard.php b/admin/blackboard.php
index 814a879..9642c59 100644
--- a/admin/blackboard.php
+++ b/admin/blackboard.php
@@ -3,11 +3,14 @@
   require 'include/db.inc';
   require 'include/common.inc';
   
-  if($_GET['mnumber']!="") {
-    // would be better to store timestamp in blackboard db!
-    if( time()-$_GET['tstamp'] < 5 ) {
-      $db->query("UPDATE students SET blackboard=blackboard+1 WHERE mnumber=" . trim($_GET['mnumber']));
-      block("lightgreen", "added blackboard");
+  if($_GET['mnumber']!="" &&
+     $db->query("SELECT COUNT(*) FROM students WHERE mnumber=" . trim($_GET['mnumber']) . " AND lastblackboard=" . trim($_GET['tstamp']))->fetch(PDO::FETCH_NUM)[0] == 0) {
+     
+    if( $_GET['sign'] == 0 ) {
+      $db->query("UPDATE students SET blackboard=blackboard+1, lastblackboard=" . $_GET['tstamp'] . " WHERE mnumber=" . trim($_GET['mnumber']));
+    }
+    else {
+      $db->query("UPDATE students SET blackboard=blackboard-1, lastblackboard=" . $_GET['tstamp'] . " WHERE mnumber=" . trim($_GET['mnumber']));
     }
   }
 
@@ -30,7 +33,8 @@
     print "<td>" . $student['lastname'] . "&nbsp;&nbsp;</td>";
     print "<td>" . $student['mnumber'] . "&nbsp;&nbsp;</td>";
     print "<td>" . $student['blackboard'] . "&nbsp;&nbsp;</td>";
-    print "<td>" . "<a href='blackboard.php?mnumber=" . $student['mnumber'] . "&tstamp=" . time() . "'>+1" . "</a></td>";
+    print "<td>" . "<a href='blackboard.php?mnumber=" . $student['mnumber'] . "&tstamp=" . time() . "&sign=0'>+1" . "</a></td>";
+    print "<td>" . "<a href='blackboard.php?mnumber=" . $student['mnumber'] . "&tstamp=" . time() . "&sign=1'>-1" . "</a></td>";
     print "</tr>";
   }
   print "</table>";
diff --git a/admin/nextsession.php b/admin/nextsession.php
index 18efdc4..ee94d50 100644
--- a/admin/nextsession.php
+++ b/admin/nextsession.php
@@ -11,7 +11,7 @@
 
   block("black", "summary for session " . $SESSIONS[$SESSIONS_actual]['timestr'] . " (" . $SESSIONS[$SESSIONS_actual]['examples'] . ")");
 
-  $result = $db->query("SELECT firstname, lastname, mnumber, blackboard FROM students ORDER BY lastname");
+  $result = $db->query("SELECT firstname, lastname, mnumber, blackboard FROM students ORDER BY LOWER(lastname)");
 
   print "<table>";
   print "<td><b>First name&nbsp;</b></td><td><b>Last name&nbsp;</b></td><td><b>Matr.nummer&nbsp;</b></td><td><b>Examples&nbsp;</b></td><td><b>Blackboard&nbsp;</b></td>";
diff --git a/admin/summary.php b/admin/summary.php
index cac27fc..68c026f 100644
--- a/admin/summary.php
+++ b/admin/summary.php
@@ -11,7 +11,7 @@
 
   block("black", "summary of everything for " . date("d.m.Y H:i"));
 
-  $result = $db->query("SELECT firstname, lastname, mnumber, blackboard FROM students ORDER BY lastname");
+  $result = $db->query("SELECT firstname, lastname, mnumber, blackboard FROM students ORDER BY LOWER(lastname)");
 
   print "<table>";
   print "<td><b>First name&nbsp;</b></td><td><b>Last name&nbsp;</b></td><td><b>Matr.nummer&nbsp;</b></td><td><b>Examples&nbsp;</b></td><td><b>Blackboard&nbsp;</b></td>";
diff --git a/include/db.inc b/include/db.inc
index 3612374..90ce96a 100644
--- a/include/db.inc
+++ b/include/db.inc
@@ -2,7 +2,7 @@
   if (! isset($db)) {
     if ($db = new PDO('sqlite:banane.db')) {
       $db->query('CREATE TABLE IF NOT EXISTS students 
-      	  (mnumber int, firstname string, lastname string, email string, password string, blackboard int DEFAULT 0, PRIMARY KEY (mnumber))');
+      	  (mnumber int, firstname string, lastname string, email string, password string, blackboard int DEFAULT 0, lastblackboard TIMESTAMP, PRIMARY KEY (mnumber))');
       $db->query('CREATE TABLE IF NOT EXISTS workdone
           (mnumber int, session int, manydone int, thisdone string)');
       $db->query('CREATE TABLE IF NOT EXISTS prefs
-- 
2.39.5