From d2715c70b614ebc8dae2d4b183659f1656184703 Mon Sep 17 00:00:00 2001
From: martinlanghoff <martinlanghoff>
Date: Tue, 26 Sep 2006 05:06:44 +0000
Subject: [PATCH] mod/glossary, mod/hotpot: use sql_concat() and
 sql_concat_join()

Cleaned up several dbtype conditionals too.
---
 mod/glossary/sql.php  |  4 ++--
 mod/hotpot/index.php  | 10 ++--------
 mod/hotpot/lib.php    | 22 ++++++++++++++++++----
 mod/hotpot/report.php | 14 ++------------
 4 files changed, 24 insertions(+), 26 deletions(-)

diff --git a/mod/glossary/sql.php b/mod/glossary/sql.php
index c2ba24e93e..16ef6da89d 100644
--- a/mod/glossary/sql.php
+++ b/mod/glossary/sql.php
@@ -92,9 +92,9 @@
         break;
         case 'mysql':
             if ( $sqlsortkey == 'FIRSTNAME' ) {
-                $usernamefield = "CONCAT(CONCAT(u.firstname,' '), u.lastname)";
+                $usernamefield = sql_fullname('u.firstname' , 'u.lastname');
             } else {
-                $usernamefield = "CONCAT(CONCAT(u.lastname,' '), u.firstname)";
+                $usernamefield = sql_fullname('u.lastname' , 'u.firsttname');
             }
             $where = "AND left(ucase($usernamefield)," .  $textlib->strlen($hook, current_charset()) . ") = '$hook'";
         break;
diff --git a/mod/hotpot/index.php b/mod/hotpot/index.php
index 4a0051e6a2..4ad23d6ec9 100644
--- a/mod/hotpot/index.php
+++ b/mod/hotpot/index.php
@@ -203,14 +203,8 @@
         $questions = false;
         $regradehotpots = array();
 
-        switch (strtolower($CFG->dbtype)) {
-            case 'mysql' : 
-                $field = "CONCAT(hotpot, '_', name)";
-                break;
-            case 'postgres7' :
-                $field = "hotpot||'_'||name";
-                break;
-        }
+        $field = sql_concat('hotpot', "'_'", 'name');
+        
         if ($field) {
             $questions = get_records_sql("
                 SELECT $field, COUNT(*), hotpot, name
diff --git a/mod/hotpot/lib.php b/mod/hotpot/lib.php
index 336ca4b451..cb807fe1af 100644
--- a/mod/hotpot/lib.php
+++ b/mod/hotpot/lib.php
@@ -1089,17 +1089,31 @@ function hotpot_get_grades($hotpot, $user_ids='') {
             break;
         case HOTPOT_GRADEMETHOD_FIRST:
             if ($CFG->dbtype=='postgres7') {
-                $grade = "MIN(timestart||'_'||(CASE WHEN (score IS NULL) THEN '' ELSE TRIM(ROUND(score * $weighting, $precision)) END)) AS grade";
+                $grade = "(CASE WHEN (score IS NULL) 
+                                THEN '' 
+                                ELSE TRIM(ROUND(score * $weighting, $precision)) 
+                           END)";
             } else {
-                $grade = "MIN(CONCAT(timestart, '_', IF(score IS NULL, '', ROUND(score * $weighting, $precision)))) AS grade";
+                $grade = "IF(score IS NULL, 
+                             '', 
+                             ROUND(score * $weighting, $precision))";
             }
+            $grade = sql_concat('timestart', "'_'", $grade);
+            $grade = "MIN($grade) AS grade";
             break;
         case HOTPOT_GRADEMETHOD_LAST:
             if ($CFG->dbtype=='postgres7') {
-                $grade = "MAX(timestart||'_'||(CASE WHEN (score IS NULL) THEN '' ELSE TRIM(ROUND(score * $weighting, $precision)) END)) AS grade";
+                $grade = "(CASE WHEN (score IS NULL) 
+                                THEN '' 
+                                ELSE TRIM(ROUND(score * $weighting, $precision)) 
+                           END)";
             } else {
-                $grade = "MAX(CONCAT(timestart, '_', IF(score IS NULL, '', ROUND(score * $weighting, $precision)))) AS grade";
+                $grade = "IF(score IS NULL, 
+                             '', 
+                             ROUND(score * $weighting, $precision))";
             }
+            $grade = sql_concat('timestart', "'_'", $grade);
+            $grade = "MAX($grade) AS grade";
             break;
     }
 
diff --git a/mod/hotpot/report.php b/mod/hotpot/report.php
index 2ed7d2feb3..ba7e1e213c 100644
--- a/mod/hotpot/report.php
+++ b/mod/hotpot/report.php
@@ -594,18 +594,8 @@ function hotpot_get_records_groupby($function, $fieldnames, $table, $select, $gr
     // $function is an SQL aggregate function (MAX or MIN)
 
     global $CFG;
-
-    switch (strtolower($CFG->dbtype)) {
-        case 'mysql':
-            $fields = "$groupby, $function(CONCAT(".join(",'_',", $fieldnames).")) AS joinedvalues";
-            break;
-        case 'postgres7':
-            $fields = "$groupby, $function(".join("||'_'||", $fieldnames).") AS joinedvalues";
-            break;
-        default:
-            $fields = "";
-            break;
-    }
+    $fields = sql_concat_join("'_'", $fieldnames);
+    $fields = "$groupby, $function($fields) AS joinedvalues";
 
     if ($fields) {
         $records = get_records_sql("SELECT $fields FROM $table WHERE $select GROUP BY $groupby");
-- 
2.39.5