スケジュール管理システムDB
==

# DDL
## sms データベース削除
DROP DATABASE sms;

## sms データベース作成
CREATE DATABASE sms;

## sms データベースに接続
\c sms

## 社員マスター
CREATE TABLE m_employee (
emp_id SERIAL NOT NULL PRIMARY KEY,
registered_date TIMESTAMP,
update_date TIMESTAMP,
password CHARACTER VARYING(60),
emp_name CHARACTER VARYING(15),
department CHARACTER VARYING(10),
contact_extension CHARACTER VARYING(4),
contact_mobile CHARACTER VARYING(11)
);

## グループマスター
CREATE TABLE m_group (
group_id SERIAL NOT NULL PRIMARY KEY,
administrator_id INT NOT NULL,
registered_date TIMESTAMP,
update_date TIMESTAMP,
group_name CHARACTER VARYING(10),
FOREIGN KEY (administrator_id) REFERENCES m_employee (emp_id)
);

## グループ割当情報
CREATE TABLE t_group_allocation (
group_id INT NOT NULL,
emp_id INT NOT NULL,
registered_date TIMESTAMP,
update_date TIMESTAMP,
delete_flag BOOLEAN,
PRIMARY KEY (group_id,emp_id),
FOREIGN KEY (group_id) REFERENCES m_group (group_id),
FOREIGN KEY (emp_id) REFERENCES m_employee (emp_id)
);

## 予定情報
CREATE TABLE t_schedule (
schedule_id SERIAL NOT NULL PRIMARY KEY,
emp_id INT NOT NULL,
registered_date TIMESTAMP,
update_date TIMESTAMP,
start_date TIMESTAMP,
end_date TIMESTAMP,
schedule_contents CHARACTER VARYING(30),
open_range INT NOT NULL,
FOREIGN KEY (emp_id) REFERENCES m_employee (emp_id)
);

## 出退勤情報
CREATE TABLE t_attendance (
attendance_id SERIAL NOT NULL PRIMARY KEY,
emp_id INT NOT NULL,
registered_date TIMESTAMP,
attendance_status INT,
FOREIGN KEY (emp_id) REFERENCES m_employee (emp_id)
);

## 予定公開割当範囲情報
CREATE TABLE t_schedule_range_allocation (
schedule_id INT NOT NULL,
allocation_group_id INT NOT NULL,
PRIMARY KEY (schedule_id, allocation_group_id),
FOREIGN KEY (allocation_group_id) REFERENCES m_group (group_id),
FOREIGN KEY (schedule_id) REFERENCES t_schedule (schedule_id)
);

## 暗号モジュールを有効にする
CREATE EXTENSION pgcrypto;

# DML

## 社員マスター
INSERT INTO m_employee(registered_date, update_date, password, emp_name, department, contact_extension, contact_mobile)
VALUES (current_timestamp, current_timestamp, crypt('1', gen_salt('bf')), '総務一郎', '総務部', '1000', '09011112222')
, (current_timestamp, current_timestamp, crypt('2', gen_salt('bf')), '総務二子', '総務部', '1000', '09012341234')
, (current_timestamp, current_timestamp, crypt('3', gen_salt('bf')), '開発三郎', '開発部', '1001', '09055556666')
, (current_timestamp, current_timestamp, crypt('4', gen_salt('bf')), '開発四朗', '開発部', '1001', '07012341234')
, (current_timestamp, current_timestamp, crypt('5', gen_salt('bf')), '営業五郎', '営業部', '1002', '07011111111');

## パスワードの確認は下記のようなSELECT文
SELECT * FROM m_employee WHERE emp_id = 1 AND password = crypt('1', password);

## グループマスター
INSERT INTO m_group(registered_date, update_date, administrator_id, group_name)
VALUES (current_timestamp, current_timestamp, 2, '新人研修チーム')
, (current_timestamp, current_timestamp, 4, '新商品開発チーム');

## グループ割当情報
INSERT INTO t_group_allocation(group_id, emp_id, registered_date, update_date, delete_flag)
VALUES (1, 2, current_timestamp, current_timestamp, false)
, (1, 1, current_timestamp, current_timestamp, false)
, (2, 4, current_timestamp, current_timestamp, false)
, (2, 2, current_timestamp, current_timestamp, true)
, (2, 3, current_timestamp, current_timestamp, false);

## 予定情報
INSERT INTO t_schedule(emp_id, registered_date, update_date, start_date, end_date, schedule_contents, open_range)
VALUES (1, current_timestamp, current_timestamp, '2020-06-01 12:00:00', '2020-06-01 13:00:00', 'ランチミーティング', 0)
, (1, current_timestamp, current_timestamp, '2020-06-03 9:00:00', '2020-06-01 18:00:00', '健康診断', 2)
, (1, current_timestamp, current_timestamp, '2020-06-05 11:00:00', '2020-06-05 12:00:00', 'リーダーミーティング', 1)
, (3, current_timestamp, current_timestamp, '2020-06-30 15:00:00', '2020-06-30 17:00:00', '新商品開発定例会', 1)
, (5, current_timestamp, current_timestamp, '2020-07-01 09:00:00', '2020-07-01 10:00:00', '営業部ミーティング', 0);

## 予定公開割当範囲情報
INSERT INTO t_schedule_range_allocation(schedule_id, allocation_group_id)
VALUES (3, 1)
,(3, 2)
,(4, 2);

## 出退勤情報
本日の日付に変更してから利用してください。
INSERT INTO t_attendance(emp_id, registered_date, attendance_status)
VALUES (1, '2020-06-01 8:45:00', 1)
, (1, '2020-06-01 18:05:30', 2)
, (2, '2020-06-01 8:47:25', 1)
, (2, '2020-06-01 19:30:20', 2)
, (3, '2020-06-01 0:00:00', 7)
, (1, '2020-06-02 8:45:00', 1)
, (2, '2020-06-02 8:47:25', 1)
, (3, '2020-06-02 8:50:10', 1)
, (3, '2020-06-02 11:00:00', 4);

***
###### tags: `スケジュール管理システムTeam`