はじめに
２年前から私たちのチームは「フットサルのリーグ戦」にが参加させて頂いています。
このリーグ戦は15年続いているそうです！
リーグ戦の概要
・毎月1回開催（年間12回）
・各回、4チームの総当り戦を2セット行う
・年間のチームの勝点で順位を決める
・年間の得点王を決める
◆2019年1月に行われた試合で使用した対戦表
この紙に各試合の審判が得点、得点者を入力。
その後、主催者が毎回チームの勝点と得点者の得点を集計されています。
（よく見ると勝点でおかしい箇所があるような気がします）
作成した画面
成績の参照は見たい情報が見れるようにしました。
得点入力画面は簡単なボタンクリックのみでできる画面にしました。
成績の参照（参加者がみる画面）
管理者（得点入力、メンバー登録、集計処理）
本投稿は
GCE(Google Compute Engine) + WordPressでシステム化した内容を書いております。
システム化しようとした背景
・紙ベースの運用されていて、主催者がデータ集計をするのが大変だと思ったから
・チーム年間順位・得点王、試合結果をいつでも参照できるようにしたいと思ったから
・集計を正しくしたいと思ったから
上記内容を対応するために
「得点入力画面で得点をDBに格納して、試合終了後、結果を集計する」ことで
主催者の負荷を下げ、参加者はいつでも結果をみることができると思い、簡易システムを作成しました。
もう一つ理由があり
12月末から1月末まで骨折していたので、フットサルできなくて暇だったのがあります。。
実現方法
実現するにあたり、以下の３つを検討
１．どんな情報が見たいか、得点入力画面の検討
２．１．でやりたい事のデータの持たせ方の検討（DB設計）
３．環境の検討（サーバ、アプリの検討）
１．どんな情報が見たいか、得点入力画面の検討
まずは、Excelでイメージを書いてみる
一番欲しい情報は現在のチーム順位表と得点王
各節の試合結果と得点者
得点入力画面
得点入力は、入力させずに「＋」、「－」ボタンで得点がUP、DOWNするようにする。
※入力チェック処理しなくていいし、また、ボタンだけの操作なので、システム化して得点入力が面倒くさくなったと感じさせないためです。
２．１．でやりたい事のデータの持たせ方の検討（DB設計）
大きく分けて、「マスタ」と「データ入力用」と「データ参照用」とのテーブルを作成。
※年間順位、年間得点王は、各回の試合結果入力後に集計させ、参照させる。
各試合終了後、得点集計処理を実施して、データ参照用テーブルを更新。
※実際のテーブル名、フィールド名は英語にしてます
３．環境の検討（サーバ、アプリの検討）
無料でサーバを使いたいので、無料枠 の GCE(Google Compute Engine) を使用
https://cloud.google.com/free/?hl=ja
また、リーグ戦の参加者は、PC、スマホ(大部分はスマホ)で結果をみると思うので、
PC、スマホと勝手に見せ方を調整してくれるWordPressを選択。
データ格納DBは無料のmariaDB、phpはWordPressを動かす上で必要となります。
３-１ GCE(Google Compute Engine)の構築
以下の記事を参考にさせて頂きました。ありがとうございました。
GCP上でWordPressを無料で構築したい
GCP(Google Cloud Platform)での無料GCE(Google Compute Engine)インスタンス作成
GCE の無料枠のサーバを立るときに、初見でハマりそうなところ
無料のドメインを取得する
Google Cloud DNSでIPアドレスとドメイン名を紐付ける
WordPressのバージョン、PHP、Jqueryを最新にしないとGoogle Cloud Platformから警告がくる可能性がありますので詳細は以下の記事を参照ください
Google Cloud Platform から GCE(Google Compute Engine)+WordPress環境について「ご対応ください: 重大な問題」のメールが来た
GCE(Google Compute Engine)の無料枠だとメモリが少ないので WordPressの画面を開くと、mariaDBがメモリが確保できないためエラーとなってしまい、WordPressの画面がみれない状態になってしまいました。
追加の設定としてSWAPを設定しました。
設定後、mariaDBのエラーは発生してません。
$ sudo dd if=/dev/zero of=/swapfile bs=1M count=1024
$ sudo chmod 600 /swapfile
$ sudo mkswap /swapfile
$ sudo swapon /swapfile
インスタンスを再起動した時にswapが自動マウントされるようにfstabに設定をします。
※ /etc/fstab 以下の設定を追加
/swapfile none swap sw 0 0
３－２ WordPressでの開発（はまったところのメモ）
ちょっと躓いたところをメモとして残します。
結局自分でHTMLを書いたので、なにもレイアウトの設定がない「固定ページ」で全て書いています
その１. WordPressで自作のphpコードを実行する場合、WordPress固有の設定が必要
これGCEにWordPress環境構築してから気がつきました。。
WordPressで自分で書いたPHPを実行する場合、
ショートコードというものを使用する必要があります。
ショートコード API
◆ショートコード記載場所
WordPressインストールディレクトリの
「/wp-content/themes/テーマ名」直下のfunctions.phpがショートコードを記載するファイルとなります。
◆ショートコードを書いてみる
functions.php を編集
サンプルでは、phpのファンクション「sampletest」を定義して
そのファンクション「sampletest」をショートコード「f_sampletest」と紐付けています
※sudo vi functions.php で更新です
/* ショートコードサンプル */
function sampletest() {
return "Hello";
}
add_shortcode('f_sampletest', 'sampletest');
注意点はこのfunctions.phpはWordPressのページ表示/管理画面の表示でコールされます。
functions.phpで構文エラー(;がない等)の場合、WordPressがみれなくなりますので
編集する前にバックアップを取得しておくことをお勧めします。
◆WordPressからショートコードを呼び出す
画面が表示される際にショートコード(f_sampletest)が実行され、ショートコードで設定した文字が表示される
◆ショートコードに引数を渡す場合
/* ショートコード引数あり */
function sampletest_argv($attr) {
$msg = $attr[0];
return $msg;
}
add_shortcode('f_sampletest_argv', 'sampletest_argv');
[ショートコード 引数] を設定⇒[更新]クリック⇒[固定ページを表示]クリック
画面が表示される際にショートコード(f_sampletest_argv)が実行され、ショートコード実行時に設定した引数の文字が表示される
◆実際のアプリで使用したショートコード抜粋
function view_top($attr) {
global $wpdb;
//引数（年度）を取得
$yyyy = $attr[0];
$listresult = "</tbody></table>";
$listresult .= "<br><strong>◆得点ランキング(上位3まで)</strong><br>";
//得点王取得SQL
$strsql ="select ";
$strsql .=" r.rank,t.teamname,p.playername ,r.score";
$strsql .=" from ";
$strsql .=" topscore r, ";
$strsql .=" team t, ";
$strsql .=" player p ";
$strsql .=" where ";
$strsql .=" r.yyyy = %d ";
$strsql .=" and r.teamno = t.teamno ";
$strsql .=" and r.playerid = p.playerid ";
$strsql .=" and rank < 4 ";
$strsql .=" order by r.rank,p.playername ";
$sql = $wpdb->prepare($strsql, $yyyy);
$rows = $wpdb->get_results($sql);
//table ヘッダ情報
$listresult .= "<table class=\"wp-table-yoko\"><thead><tr><th>順位</th><th>氏名</th><th>チーム名</th><th>得点</th></tr></thead><tbody>";
//テーブルレコード設定
foreach($rows as $row){
$listresult .= "<tr><td>". $row->rank . "</td><td>" . $row->playername . "</td><td style =\"text-align: left;\">" . $row->teamname . "</td><td>" . $row->score . "</td></tr>";
}
$listresult .= "</tbody></table>";
//画面表示
return $listresult;
}
add_shortcode('f_viewtop', 'view_top');
ショートコード(f_viewtop)を設定⇒[固定ページを表示]クリック
ショートコード(f_viewtop)に引数を設定(2019)して実行した結果。
その２． 自作のJavaScriptを使用する場合、WordPress固有の設定が必要
これもWordPressの設定が必要です。
得点入力画面で「＋」、「－」ボタンのみで得点入力するようにしました。
そのため、「＋」、「－」ボタンが押されたタイミングで、JavaScriptで得点表示を
変更する必要がありました。
（赤枠の「＋」、「－」ボタンをクリックすると対応する得点が加算、減算されます）
手順１ JavaScriptを作成して格納
「/wp-content/themes/テーマ名/js」に 作成したJavaScript(score.js)を格納します
function incValue(idname){
// value値を取得する
var obj = document.getElementById(idname);
//valueに値をセットする
obj.value = parseInt(obj.value) + parseInt(1);
}
function decValue(idname){
// value値を取得する
var obj = document.getElementById(idname);
score = parseInt(obj.value)
if (score > 0){
//valueに値をセットする
obj.value = parseInt(obj.value) - parseInt(1);
}
}
手順２ functions.php にjavascriptに関する記載を書く
function score_scripts() {
wp_enqueue_script( 'score-script', get_template_directory_uri().'/js/score.js' , array(), date('U'));
}
add_action( 'wp_enqueue_scripts', 'score_scripts' );
この設定で自前のJavaScriptが動くようになります
その３．データ登録方法(POST送信)
データ登録方法は、「データ入力画面」からPOST送信して、
送信された側の「データ登録終了画面」に設定したショートコードでデータ登録させる方法にしました。
※実際のアプリで使用したデータ登録をサンプルとして残します。
手順１ POST送信する固定ページの設定
POST送信する固定ページにformタグを設定して、POST送信するように記載。
formタグの中でショートコードをコールするようにします。
（ショートコードの中で、submitボタンを作成してます）
手順２ POST受信する固定ページの設定
ショートコード(f_postdata)のみ記載
f_postdataのショートコード（抜粋）
function postdata() {
global $wpdb;
//POSTされたデータを受け取る
$yyyy = $_POST['yyyy'];
$mm = $_POST['mm'];
$seq = $_POST['seq'];
$team1 = $_POST['team1'];
$team2 = $_POST['team2'];
$score1 = $_POST['score1'];
$score2 = $_POST['score2'];
<処理省略>
//得点を登録
$strsql = "update matchresults ";
$strsql .= " set score1 = %d ,";
$strsql .= " score2 = %d ";
$strsql .=" where ";
$strsql .=" yyyy= %d ";
$strsql .=" and mm= %d ";
$strsql .=" and seq= %d ";
$sql = $wpdb->prepare($strsql,$score1,$score2, $yyyy,$mm,$seq);
$rows = $wpdb->get_results($sql);
<処理省略 この後、登録したデータを表示する処理>
}
add_shortcode('f_postdata', 'postdata');
POST送信⇒POST受信した固定ページでショートコード(f_postdata)を実行した結果
その４．ランク付け集計方法
mariaDBでOracleのRANK・DENSE_RANKを取得するSQL文使用できない
https://docs.oracle.com/cd/E16338_01/server.112/b56299/functions052.htm
簡単に言うと、RANKはある列の値に順位をつけてくれるものになります。
DENSE_RANKは、順位をつけるのですが、同一値の場合に順位を飛ばさずに順位をつけます。
※本リーグ戦の運用では、得点王はDENSE_RANKで順位をつけています。
mariaDBでDENSE_RANKするSQLを記載
ダミーテーブルとサンプルデータとRank取得するSQL
--得点データ格納するテーブル
create table test_scoring(
yyyy int NOT NULL,
mm int NOT NULL,
seq int NOT NULL,
playerid int NOT NULL,
score int,
PRIMARY KEY(yyyy,mm,seq,playerid)
);
--ダミーデータを入れる
insert into test_scoring values(2019,1,1,17,1);
insert into test_scoring values(2019,1,1,18,1);
insert into test_scoring values(2019,1,1,19,1);
insert into test_scoring values(2019,1,2,9,1);
insert into test_scoring values(2019,1,3,13,1);
insert into test_scoring values(2019,1,3,11,1);
insert into test_scoring values(2019,1,4,3,1);
insert into test_scoring values(2019,1,4,20,2);
insert into test_scoring values(2019,1,4,18,1);
insert into test_scoring values(2019,1,5,17,1);
insert into test_scoring values(2019,1,5,12,1);
insert into test_scoring values(2019,1,6,14,1);
insert into test_scoring values(2019,1,6,15,1);
insert into test_scoring values(2019,1,6,5,2);
insert into test_scoring values(2019,1,7,12,1);
insert into test_scoring values(2019,1,7,4,1);
insert into test_scoring values(2019,1,7,8,1);
insert into test_scoring values(2019,1,8,16,1);
insert into test_scoring values(2019,1,8,20,1);
insert into test_scoring values(2019,1,8,21,1);
insert into test_scoring values(2019,1,10,18,1);
insert into test_scoring values(2019,1,11,15,1);
insert into test_scoring values(2019,1,12,3,1);
insert into test_scoring values(2019,1,12,8,1);
insert into test_scoring values(2019,1,12,18,1);
insert into test_scoring values(2019,1,12,22,1);
--DENSE_RANKでランク付けするSQL
select
CASE WHEN @before_sumscore = d.sumscore THEN @rank
ELSE @rank:=@rank+1
END AS rank,
playerid,
@before_sumscore := d.sumscore as sumscore
from
(
select
playerid ,
sum(score) as sumscore
from
test_scoring
where
yyyy=2019
group by
playerid
) d,
(select @rank:=0, @before_sumscore:=0) s
order by
sumscore desc ;
取得結果
同一得点の場合、Rankは同じ順位に取得されています。
また、順位が飛ばされていません。
作成した画面と遷移図
成績の参照（参加者がみる画面）
管理者（得点入力、メンバー登録、集計処理）
成績の参照は見たい情報が見れるようにしました。
得点入力画面は簡単なボタンクリックのみでできる画面にしました。
2月リーグ戦で主催者への説明
タブレットを持っていき、主催者の前で得点入力、集計処理(得点・得点王)を行い説明をしました。
主催者とお話して以下の予定で進めることになりました。
３月は紙ベース運用とシステムの平行運用
４月からはシステムでの運用
※2019年から運用して、2020年も使用しています。
関連リンク
Google Cloud Platform から GCE(Google Compute Engine)+WordPress環境について「ご対応ください: 重大な問題」のメールが来た