--- Tao CSDL -> QuanLyDiemDanh
create database QuanLyDiemDanh
go

--- Active CSDL
use QuanLyDiemDanh
go

--- Phan I. Thiet ke tables
----- Tao bang Student
create table Student (
	rollno nvarchar(20) primary key,
	fullname nvarchar(50) not null,
	gender nvarchar(15),
	address nvarchar(150),
	birthday date
)
go

create table Teacher(
	id int primary key identity(1, 1),
	email nvarchar(150) unique,
	fullname nvarchar(150) not null,
	birthday date,
	gender nvarchar(15)
)
go

create table Subject (
	id int primary key identity(1,1),
	name nvarchar(50) not null,
	session int default 0
)
go

create table Class (
	id int primary key identity(1, 1),
	name nvarchar(50) not null,
	note nvarchar(200)
)
go

create table ClassMember (
	rollno nvarchar(20) not null,
	classno int not null,
	joined_date date,
	outed_date date,
	constraint pk_class_member primary key (rollno, classno)
)
go

alter table ClassMember
add constraint fk_class_member_rollno foreign key (rollno) references Student (rollno)

alter table ClassMember
add constraint fk_class_member_classno foreign key (classno) references Class (id)

create table Schedule (
	id int primary key identity(1, 1),
	teacherId int references Teacher(id),
	subjectNo int references Subject (id),
	classNo int references Class (id),
	startDate date,
	endDate date
)
go

------ attendence1, attendence2 : -1, 0, 1 : Vang, Nghi Co Phep, Di Hoc
------ attendence1, attendence2 : A, PA, P : Vang, Nghi Co Phep, Di Hoc
create table Attendences (
	id int primary key identity(1, 1),
	scheduleId int references Schedule(id),
	checkin datetime,
	attendence1 nvarchar(5) default 'P',
	attendence2 nvarchar(5) default 'P',
	note nvarchar(100)
)
go

------- TEST
select * from Student
select * from Class
select * from ClassMember
select * from Teacher
select * from Subject
select * from Schedule
select * from Attendences
-------- Phan II. Them du lieu
insert into Student (rollno, fullname, gender, birthday, address)
values
('R001', 'Tran Van A', 'Nam', '1999-03-06', 'Ha Noi'),
('R002', 'Tran Van B', 'Nam', '1997-05-19', 'Nam Dinh'),
('R003', 'Tran Van C', 'Nam', '1993-02-09', 'Ha Nam'),
('R004', 'Tran Van D', 'Nam', '1998-01-19', 'Thai Binh'),
('R005', 'Tran Van E', 'Nam', '1991-09-29', 'Hung Yen')
go

insert into Class (name)
values
('C1803L'),
('C1610I'),
('C1808G'),
('C2002L'),
('C1812L')
go

select * from Class

insert into ClassMember (rollno, classno, joined_date, outed_date)
values
('R001', 1, '2018-02-15', '2020-06-06'),
('R002', 1, '2018-02-15', '2020-06-06'),
('R003', 2, '2018-12-15', null),
('R004', 2, '2018-12-15', null),
('R005', 3, '2019-02-15', null)
go

insert into ClassMember (rollno, classno, joined_date, outed_date)
values
('R001', 4, '2018-02-15', null),
('R002', 4, '2018-02-15', null),
('R003', 4, '2018-12-15', null),
('R004', 5, '2018-12-15', null),
('R005', 5, '2019-02-15', null)
go

insert into Teacher (fullname, birthday, email, gender)
values
('Tran Van Diep', '1986-02-02', 'tranvandiep.it@gmail.com', 'Nam'),
('Tran Van OK', '1986-12-02', 'tranvanabc.ok@gmail.com', 'Nam')
go

insert into Subject(name, session)
values
('Lap Trinh C', 10),
('Bootstrap/jQuery', 6),
('HTML/CSS/JS', 10),
('SQL Server', 10),
('PHP/Laravel', 10)
go

select * from Schedule

insert into Schedule(teacherId, subjectNo, classNo, startDate, endDate)
values
(1, 4, 4, '2020-07-16', '2020-09-16'),
(1, 5, 5, '2020-07-10', '2020-09-10')
go

select * from Attendences

--- FIX : checkin => checkin1 & checkin2
--- rollno 
alter table Attendences
drop column checkin

alter table Attendences
add checkin1 datetime

alter table Attendences
add checkin2 datetime

alter table Attendences
add rollno nvarchar(20) references Student (rollno)

select * from Attendences

insert into Attendences (scheduleId, rollno, checkin1, attendence1, checkin2, attendence2, note)
values
(1, 'R001', '2020-08-20 19:05:00', 'A', '2020-08-20 20:50:00', 'P', ''),
(1, 'R002', '2020-08-20 19:05:00', 'P', '2020-08-20 20:50:00', 'P', ''),
(1, 'R003', '2020-08-20 19:05:00', 'P', '2020-08-20 20:50:00', 'PA', '')

insert into Attendences (scheduleId, rollno, checkin1, attendence1, checkin2, attendence2, note)
values
(2, 'R004', '2020-08-20 19:05:00', 'P', '2020-08-20 20:50:00', 'P', ''),
(2, 'R005', '2020-08-20 19:05:00', 'P', '2020-08-20 20:50:00', 'P', '')
go

--- - Tạo procedure để xem thông tin học viên trong một lớp học - đầu vào là tên lớp
------- ClassName, RollNo, Fullname, Gender, Birthday, Address
------- ClassName => Class, Other => Student, ClassMember
create proc proc_view_students_in_class
	@ClassName nvarchar(50)
as
begin
	select Class.name ClassName, Student.rollno, Student.fullname, Student.gender, Student.birthday, Student.address
	from Class left join ClassMember on Class.id = ClassMember.classno
		left join Student on ClassMember.rollno = Student.rollno
	where Class.name = @ClassName
end

exec proc_view_students_in_class N'C2002L'

---- Tạo procedure để xem danh sách điểm danh của lớp học, của môn môn học cụ thể. - đầu vào là mã lớp học và môn học
---- ClassName, SubjectName, Fullname, RollNo, checkin1, attendence1, checkin2, attendence2
------- Class, Subject, Student, Attendences, Schedule
select Class.name ClassName, Subject.name SubjectName, Student.fullname, Student.rollno, Attendences.checkin1, Attendences.attendence1, Attendences.checkin2, Attendences.attendence2
from Class, Subject, Student, Attendences, Schedule
where Class.id = Schedule.classNo
	and Subject.id = Schedule.subjectNo
	and Student.rollno = Attendences.rollno
	and Schedule.id = Attendences.scheduleId
	and Class.name = 'C2002L'
	and Subject.name = 'PHP/Laravel'

create proc proc_xem_diem_danh
	@ClassName nvarchar(50),
	@SubjectName nvarchar(50)
as
begin
	select Class.name ClassName, Subject.name SubjectName, Student.fullname, Student.rollno, Attendences.checkin1, Attendences.attendence1, Attendences.checkin2, Attendences.attendence2
	from Class, Subject, Student, Attendences, Schedule
	where Class.id = Schedule.classNo
		and Subject.id = Schedule.subjectNo
		and Student.rollno = Attendences.rollno
		and Schedule.id = Attendences.scheduleId
		and Class.name = @ClassName
		and Subject.name = @SubjectName
end

create proc proc_xem_diem_danh_sinh_vien
	@ClassName nvarchar(50),
	@SubjectName nvarchar(50),
	@RollNo nvarchar(20)
as
begin
	select Class.name ClassName, Subject.name SubjectName, Student.fullname, Student.rollno, Attendences.checkin1, Attendences.attendence1, Attendences.checkin2, Attendences.attendence2
	from Class, Subject, Student, Attendences, Schedule
	where Class.id = Schedule.classNo
		and Subject.id = Schedule.subjectNo
		and Student.rollno = Attendences.rollno
		and Schedule.id = Attendences.scheduleId
		and Class.name = @ClassName
		and Subject.name = @SubjectName
		and Student.rollno = @RollNo
end

exec proc_xem_diem_danh N'C2002L', N'SQL Server'
exec proc_xem_diem_danh_sinh_vien N'C2002L', N'SQL Server', N'R001'
exec proc_xem_diem_danh_sinh_vien N'C2002L', N'SQL Server', N'R005'

Xây dựng CSDL quản lý điểm danh aptech - Lập trình SQL Server/MySQL