報告書き記入漏れアラート mysqlで曜日を取得してその日に記入しているかチェックしたい。

Question

mysqlのクエリ文について困っているため、お力をお借りしたいです。

実装したいこと。

###曜日を設定してその日に報告書の書き忘れがあった場合にアラートを出してその日付を取得したい。

環境：
php 5.6
mysql 5.6

◆上記の実装をするための環境づくり。
DBに対して２つのテーブルを用意。
###回答を頂いて、改めて記入しなおしました。

スタッフDB（user）

スタッフid	名前	メールアドレス
1	太郎	[e-mail]
2	次郎	[e-mail]
3	三郎	[e-mail]
4	四郎	[e-mail]
5	五郎	[e-mail]

報告書記先入会社DB(kaisha)

会社id	名前	メールアドレス
1	A会社	[e-mail]
2	B会社	[e-mail]
3	C会社	[e-mail]
4	D会社	[e-mail]
5	E会社	[e-mail]

報告書期限DB(kigen)

id	開始日	会社id	提出曜日	報告書の種類
1	2017-10-01	1	月曜日	A
2	2017-10-01	2	火曜日	A
3	2017-10-01	3	水曜日	A
4	2017-10-01	3	水曜日	B
5	2017-10-01	4	木曜日	A
6	2017-10-01	5	金曜日	A
7	2017-10-01	5	土曜日	A

自社の休日DB

id	休日
1	2017-10-06

報告書の記録()

id	スタッフid	会社id	期限id	記入日	内容
1	1	1	1	2017-10-01	よかった！
2	2	2	2	2017-10-02	営業頑張った
3	3	3	3	2017-10-03	いつも通り
4	5	5	5	2017-10-05	あまり出来が良くなかった。

2017-10-07の段階で
上記の場合、三郎さんが報告書を１つ書き忘れているのと
四郎さんが報告書を書き忘れています。
6日は自社が休みなため記入をする必要がありません。

###上記の時に、３つのセレクトしたいことがあります。

◎個別（三郎・四郎さん）の報告書記入漏れがある日付の取得
◎全体で、報告書の記入漏れがある数の取得
◎全体で、報告書の記入漏れがある日付の取得

上記の内容の具体的なクエリ文のご指導いただければ幸いです。

###他の曜日は？報告書は一切書かなくても良いの？
他の曜日は報告書は一切書く必要がありません。
##5w1hが書いてない。何時、誰が、それを知って、その結果どうしたい？
詳細が足りなくてすいません。DBなど追加情報など入れますのでどうかお付き合いお願いいたします。

何時：休日を除く毎週決められた会社に対して
誰が：スタッフ（スタッフは変動します。）が報告書をしっかり書いているかを確認したい。
それを知って：警告アラートをサイト内のメッセージ送信又は、サイト内のお知らせ一覧のようなところに表示したい。
その結果：報告書の記入漏れが防げる。

再度全体的に今の現状を出来るだけ再現しました。

指導ご鞭撻のほどお願いいたします。

Comments

[gouf]

この場合のたとえば「ひとの出入り(入社/退職)は考えないものとする」のような制約条件は他に存在しますか?

[kurofukuro]

制約条件があるとすれば、上記のテーブルにはないですが、休日のDBを作って、会社が休みの時はカウントしないというような条件を考えております。

Answer 1

ぱっとみまず、テーブルAの

2 2017-10-01 三郎

2 2017-10-01 三郎

はNGです。
同じデータが2つ存在するということはアクセスされたときに
どちらがヒットするか確定できません
（sqlには前のデータとか後のデータという考え方はありません）
データとして成立させたいのであればレコードにidを振る必要があるでしょう

当然テーブルBについては、テーブルAで振ったidに対して
リレーションしていく必要があります。
現在はおそらく名前と記入日をキーに処理する想定なのでしょうけど
前記した理由によりテーブルAのレコードを確定できていません。

なお、テーブルAの実行とテーブルBの報告をわけて管理されていますが
テーブルA-テーブルB間で１対１でしかリレーションしないのであれば
正規化にならないので今回のケースについてはわざわざ分けるメリットが感じられません
テーブルAに対して報告カラムを設ければよいだけのような気がします
（集計のために報告フラグを設けてもよい）

命題にもどりますが、曜日が必要十分に利用されるケースが想定されないので
曜日による管理は現時点では見いだせません
ただし通常のレポート期限は２日で、営業日ベースで計算し特定の曜日に対しては
３日以上の猶予を与えるなど工夫があるなら曜日の管理も有効です

Answer 2

考え方おかしい！もっと簡単に出来るという場合は
是非入り口からご指導ください

DBがどうこう以前の入口ですが、一点、私が違和感を覚えるのは、
曜日中心に考えていることです。
日付中心に考えた方が良いと思います。

ご質問で想定している「曜日を設定して、日付を取得する」というよりかは、
「日付を設定して、曜日も取得する」という方が良いと思います。
曜日を主キーにすると、非常に使いにくいDBになると思います。

どうしてかというと、曜日だけだと、
週をまたぐだけでも、一周以上遅れているのかどうか、
周回遅れを区別できなくなってしまいます。

いや、日付も参照すれば区別は可能でしょうが、
それなら最初から日付を使えば、日付だけで済みます。
というか、日付から曜日を算出するようなこともできます。

---

質問者の方が曜日にこだわるのは、たとえば頼まれた要件に、
「曜日」という言葉があったのかもしれません。

しかし、祝日がどうで営業日がどうとか、夏期や年末は特別とか、
実際の業務ではどんどんルールが複雑化していきます。

そのとき、曜日だと7つしか区別できないですが、
日付だと千日でも一万日でも期間を設定できるので汎用的です。
最初から日付にした方が、後での変更がいろいろ楽でしょう。

---

あともうひとつだけ言うと、実際の業務にもよるとは思いますが、
「期限日」というデータを導入した方が、考えやすいと思います。

スケジュール管理系アプリを使うときに、何がしたいかといったら、
期限まであと何日か、期限を過ぎてないかとか、そういうことがしたいでしょう。

なお、今回は入口だけの話で、
自動連番のIDを振るかどうかなど、DB設計の詳細な話はまた別です。

Comments

[kurofukuro]

お返事遅くなりました。
お返事いただきありがとうございます。
おっしゃる通りで、曜日いうワードが念頭にあり上記のように書かせていただきました。
日付指定の案ですが、書いていただいたとおりに、この後に祝日や休みの日が含まれないという条件がついてきます。そこでも、困っているところなのです。

報告書の条件として○○社あての報告書を毎週〇曜日にもれなく書いてもらいたいというところで、
お返事いただいた日付指定という形をとり「期限日」というDBを新たに設けて行う際に、どのようなDB設計をしていけばよいのか・・・ｐｈｐでの構築を行っていますが
期限日DBに対して動的にある曜日を過ぎたら期限の日付を挿入するというような形にするような感じでしょうか？ここで「曜日」という概念を取り払おうとししているのですが、休み以外に毎週その曜日が来るもので、曜日の代わりに未来の日付を用意を複数用意する必要があるというようなことでしょうか。
毎週必ず期限が必要になりますので、DBに大量の期限日が出来てしまうかなと危惧しているのですが、そんなことは、些細なことなのでしょうか。
上手く頭で整理できていなくてもうしわけありません。

具体的な流れ等いただければ幸いです。

[LLman]

まず、すでにあるデータを他から取ってくるとかではなく、
PHPのWebアプリからのみ、DBを操作する、という前提でいいですか。

＞期限日DBに対して動的にある曜日を過ぎたら
＞期限の日付を挿入するというような形にするような感じでしょうか？

シンプルなシステムからスタートすることを想定して、期限日は不変と私は考えています。
つまり、最初に登録したときの期限日そのまま。（変更可能にしても構いません）

曜日でゴチャゴチャ計算したりしないで、
期限日と現在の日付を比較するだけで済むと思います。
つまり、【曜日がなくても可能です】。

さしあたり、曜日とか営業日とかは人間が判断して、
登録時に期限日を入力して、そのまま変えずに使えばシンプルです。

もちろん、曜日や営業日を算出する機能を追加しても構いません。
あるいは、カレンダーのUIとか作れば、入力の負担が減ります。

でもそれらは、絶対必須ではないでしょう。
慣れていないなら、まず必要最低限のシステムを作るのが無難です。
（ただし、依頼の要件によるでしょうけど）

＞毎週必ず期限が必要になりますので、
＞DBに大量の期限日が出来てしまうかなと危惧
MySQLはたとえば1万件くらいではビクともしません。節約する必要は薄いです。
ただ、ユーザが1万件から探すための、使いやすいUIとかの工夫は必要かも。

なお、自動連番のIDを振るのは有効だと思います（だから最後に少し触れました）。
というか、重複をなくすのにIDが一番確実だし、PHPで完結していれば確実にIDを振れます。
ただ要件が曖昧なまま、話を複雑にしたくなかったので、掘り下げなかっただけです。

Answer 3

火曜日に報告書が2つですか……
以下が不明確なのでなんとも言えませんね。

• 他の曜日は？報告書は一切書かなくても良いの？
• 5w1hが書いてない。何時、誰が、それを知って、その結果どうしたい？

いずれにせよ、テーブルが第三正規になってないのでそれに準拠させたほうが良いでしょう。
以下の2テーブルで管理する事以外は考えられません。

idはオートインクリメントで考えています、4Byteを削りたい目的で取り除かれるケースはありますが、
そこまでシビアな設計が求められない場合はとりあえず用意しておいた方が良いでしょう。

users (社員)
mailのように社員に付属する情報はこのテーブルで新しいカラムを用意して使ってください。

id	name	mail
1	太郎	[e-mail]
2	次郎	[e-mail]
3	三郎	[e-mail]
4	四郎	[e-mail]
5	伍郎	[e-mail]

reports (報告書)
created_on(作成日付)にはインデックスを張っておきましょう

id	user_id	body	created_on
1	1	太郎	よかった！
2	2	次郎	営業頑張った
3	3	三郎	いつも通り
4	5	五郎	あまり出来が良くなかった。

---

さて、運用フェイズの事を考えます。
サンプルとして2つ考えました。

• 水曜日の昼過ぎに、社長や総務の人、もしくは上司が部下の提出状況を確認したい
• 火曜日の夕方16時頃になったらまだ書いてない社員に自動的に催促メールを出したい

まず前者、Webページを用意してtableタグで書いてない社員の一覧表を見せる感じになります。
上司であれば部下の状態のみに絞る必要があります。
ただし、基本はパラメータを渡してその週の火曜日しか見れないという設計にしておきましょう。

PHPの日時を操るDateは曜日の概念が存在するので、
今日(木曜日)という日時から火曜日は2日前と一瞬で判断できます。
つまり、今週の火曜日の日付(YYYY-MM-DD)という文字列は簡単に作成出来ます。
曜日番号をテーブルに保存する必要は微塵も存在しません。

---

次に後者、これはタスクスケジューラの出番です。
Linux系ならcronが用意されていますし、
WindowsにもMacにもタスクスケジューラは存在します。

火曜日の夕方16時にPHPをコマンドライン起動で実行し、
今日の書いたレポートの数が1以下の社員におしかりメールを送信するようにしておけば良いでしょう。

ついでに水曜日の早朝も同じPHPファイルをコマンドライン起動、昨日書かずに書いた社員の上司にメールを飛ばすようにしましょう。
これで水曜日の早朝に上司に叱られた社員が日誌を作るので、社長や総務に叱られる事がなくなりますね。

昨日という情報はタスクスケジューラに登録する歳にコマンドライン引数を設定し、
PHPで読み込む事で1つのPHPファイルで火曜日も水曜日も対応出来るようになります。