質問: mysqlのクエリ文について困っているため、お力をお借りしたいです。 実装したいこと。 ### 曜日を設定してその日に報告書の書き忘れがあった場合にアラートを出してその日付を取得したい。 環境: php 5.6 mysql 5.6 ◆上記の実装をするための環境づくり。 DBに対して2つのテーブルを用意。 ### **回答を頂いて、改めて記入しなおしました。** スタッフDB(user) | スタッフid | 名前 | メールアドレス | | --- | --- | --- | | 1 | 太郎 | taro@aaa.com | | 2 | 次郎 | jiro@aaa.com | | 3 | 三郎 | saburo@aaa.com | | 4 | 四郎 | shiro@aaa.com | | 5 | 五郎 | goro@aaa.com | 報告書記先入会社DB(kaisha) | 会社id | 名前 | メールアドレス | | --- | --- | --- | | 1 | A会社 | a@aaa.com | | 2 | B会社 | b@aaa.com | | 3 | C会社 | c@aaa.com | | 4 | D会社 | d@aaa.com | | 5 | E会社 | e@aaa.com | 報告書期限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の段階で 上記の場合、三郎さんが報告書を1つ書き忘れているのと 四郎さんが報告書を書き忘れています。 6日は自社が休みなため記入をする必要がありません。 ### 上記の時に、3つのセレクトしたいことがあります。 ◎個別(三郎・四郎さん)の報告書記入漏れがある日付の取得 ◎全体で、報告書の記入漏れがある数の取得 ◎全体で、報告書の記入漏れがある日付の取得 上記の内容の具体的なクエリ文のご指導いただければ幸いです。 ### 他の曜日は?報告書は一切書かなくても良いの? 他の曜日は報告書は一切書く必要がありません。 5w1hが書いてない。何時、誰が、それを知って、その結果どうしたい? ---------------------------------- 詳細が足りなくてすいません。DBなど追加情報など入れますのでどうかお付き合いお願いいたします。 何時:休日を除く毎週決められた会社に対して 誰が:スタッフ(スタッフは変動します。)が報告書をしっかり書いているかを確認したい。 それを知って:警告アラートをサイト内のメッセージ送信又は、サイト内のお知らせ一覧のようなところに表示したい。 その結果:報告書の記入漏れが防げる。 再度全体的に今の現状を出来るだけ再現しました。 指導ご鞭撻のほどお願いいたします。
回答: 火曜日に報告書が2つですか...... 以下が不明確なのでなんとも言えませんね。 * 他の曜日は?報告書は一切書かなくても良いの? * 5w1hが書いてない。何時、誰が、それを知って、その結果どうしたい? いずれにせよ、テーブルが第三正規になってないのでそれに準拠させたほうが良いでしょう。 以下の2テーブルで管理する事以外は考えられません。 idはオートインクリメントで考えています、4Byteを削りたい目的で取り除かれるケースはありますが、 そこまでシビアな設計が求められない場合はとりあえず用意しておいた方が良いでしょう。 users (社員) mailのように社員に付属する情報はこのテーブルで新しいカラムを用意して使ってください。 | id | name | mail | | --- | --- | --- | | 1 | 太郎 | taro@example.com | | 2 | 次郎 | jiro@example.com | | 3 | 三郎 | saburo@example.com | | 4 | 四郎 | siro@example.com | | 5 | 伍郎 | goro@example.com | 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ファイルで火曜日も水曜日も対応出来るようになります。