From 08f33928b99dc7271552bd3c1b949bc9bef650cf Mon Sep 17 00:00:00 2001
From: Habu <habu@users.sourceforge.jp>
Date: Sun, 18 Mar 2018 16:52:01 +0900
Subject: [PATCH] =?utf8?q?[modify]=E6=AD=BB=E5=9B=A0=E3=81=AE=E6=96=87?=
 =?utf8?q?=E5=AD=97=E5=88=97=E3=82=92PHP=E5=81=B4=E3=81=A7=E3=81=AA?=
 =?utf8?q?=E3=81=8FSQL=E6=96=87=E3=81=A7=E4=BD=9C=E6=88=90=E3=81=99?=
 =?utf8?q?=E3=82=8B?=
MIME-Version: 1.0
Content-Type: text/plain; charset=utf8
Content-Transfer-Encoding: 8bit

---
 db_common.inc     | 40 +++++++++++++++++++++++++++++++---------
 score_ranking.php | 22 ++++------------------
 2 files changed, 35 insertions(+), 27 deletions(-)

diff --git a/db_common.inc b/db_common.inc
index 0586ee4..cb19b6f 100644
--- a/db_common.inc
+++ b/db_common.inc
@@ -102,15 +102,37 @@ class ScoreDB
      */
     private function get_search_query($offset, $limit, $where, $order_by)
     {
-        switch ($this->sort_mode) {
-        case "score":
-            $query = "SELECT *, group_concat(realm_name) AS realms_name from (select * from scores ${where} {$order_by} LIMIT ${offset}, {$limit}) NATURAL JOIN races NATURAL JOIN classes NATURAL JOIN personalities NATURAL LEFT JOIN score_realms NATURAL LEFT JOIN realms GROUP BY score_id {$order_by}";
-            break;
-        case "newcome":
-            $query = "SELECT *, group_concat(realm_name) AS realms_name from (select * from scores ${where} {$order_by} LIMIT ${offset}, {$limit}) NATURAL JOIN races NATURAL JOIN classes NATURAL JOIN personalities NATURAL LEFT JOIN score_realms NATURAL LEFT JOIN realms GROUP BY score_id {$order_by}";
-            break;
-        }
-
+        $query = <<<EOM
+SELECT
+  *,
+  group_concat(realm_name) AS realms_name,
+  CASE
+    WHEN killer = 'ripe' THEN '勝利の後引退'
+    WHEN killer = 'Seppuku' THEN '勝利の後切腹'
+    ELSE killer || 'に殺された'
+  END AS death_reason
+FROM
+ (SELECT
+    *
+  FROM
+    scores
+  {$where}
+  {$order_by}
+  LIMIT {$offset}, {$limit}) AS s
+NATURAL INNER JOIN
+  races
+NATURAL INNER JOIN
+  classes
+NATURAL INNER JOIN
+  personalities
+NATURAL LEFT JOIN
+  score_realms
+NATURAL LEFT JOIN
+  realms
+GROUP BY
+  score_id
+{$order_by}
+EOM;
         return $query;
     }
 
diff --git a/score_ranking.php b/score_ranking.php
index 99deec8..3ccdfbb 100644
--- a/score_ranking.php
+++ b/score_ranking.php
@@ -9,18 +9,6 @@ include "dump_file.inc";
 
 ini_set('zlib.output_compression', 'On');
 
-function get_death_reason($killer)
-{
-    switch ($killer) {
-    case "ripe":
-        return "勝利の後引退";
-    case "Seppuku":
-        return "勝利の後切腹";
-    default:
-        return $killer."に殺された";
-    }
-}
-
 
 /**
  * ページ情報を計算する
@@ -117,17 +105,15 @@ EOM;
 
     foreach($scores as $idx => $score) {
         $rank = $rank_start + $idx + 1;
-        $score_id = $score['score_id'];
-        $date = substr($score['date'], 0, 10);
+        $date = substr($score['date'], 0, 10); // 日時から日付部分を取り出す
         $sex_str = $score['sex'] ? "男" : "女";
-        $death_reason = get_death_reason($score['killer']);
         $depth = !$score['winner'] ? $score['depth']."階, " : "";
         $realms = isset($score['realms_name']) ? "(".$score['realms_name'].")" : "";
-        $dumpfile = new DumpFile($score_id);
+        $dumpfile = new DumpFile($score['score_id']);
 
         echo "<tr>\n";
         if ($dumpfile->exists('dumps', 'txt')) {
-            $name = "<a href=\"show_dump.php?score_id={$score_id}\">{$score['personality_name']}{$score['name']}</a>\n";
+            $name = "<a href=\"show_dump.php?score_id={$score['score_id']}\">{$score['personality_name']}{$score['name']}</a>\n";
         } else {
             $name = "{$score['personality_name']}{$score['name']}";
         }
@@ -142,7 +128,7 @@ EOM;
 
 EOM;
         if ($dumpfile->exists('screens', 'html')) {
-            echo "<td><a href=\"show_screen.php?score_id={$score_id}\">{$death_reason}</a>";
+            echo "<td><a href=\"show_screen.php?score_id={$score['score_id']}\">{$score['death_reason']}</a>";
         } else {
             echo "<td>{$death_reason}";
         }
-- 
2.11.0