Bài tập - Viết nghiệp vụ cho quản lý khách sạn - Khóa học SQL Server

-- Tao CSDL
create database BT2784
go

-- Kich hoat CSDL
use BT2784
go

-- Tao Tables
create table Roles (
	id int primary key identity(1,1),
	rolename nvarchar(50)
)
go

create table Users (
	id int primary key identity(1,1),
	fullname nvarchar(50),
	birthday date,
	gender nvarchar(20),
	email nvarchar(150),
	phone_number nvarchar(20),
	address nvarchar(200),
	role_id int references Roles (id)
)
go

create table Room (
	id int primary key identity(1,1),
	room_no nvarchar(20) not null,
	type nvarchar(20),
	max_num int,
	price float
)
go

create table Booking (
	id int primary key identity(1,1),
	staff_id int references Users (id),
	customer_id int references Users (id),
	checkin datetime,
	checkout datetime
)
go

create table BookingDetail (
	booking_id int references Booking (id),
	room_id int references Room (id),
	price float,
	unit float,
	primary key (booking_id, room_id)
)
go

create table UserDetail (
	booking_id int references Booking (id),
	room_id int references Room (id),
	customer_id int references Users (id),
	primary key (booking_id, room_id, customer_id)
)

create table Category (
	id int primary key identity(1,1),
	name nvarchar(50)
)
go

create table Product (
	id int primary key identity(1,1),
	category_id int references Category (id),
	title nvarchar(150),
	thumbnail nvarchar(500),
	description ntext,
	price float,
	amount int
)
go

create table Services (
	id int primary key identity(1,1),
	booking_id int references Booking (id),
	customer_id int references Users (id),
	product_id int references Product (id),
	price float,
	amount int,
	buy_date datetime
)
go

-- Insert Data
insert into Roles (rolename)
values
('Admin'),
('Staff'),
('Customer')
go

insert into Users (fullname, email, phone_number, role_id)
values
('Admin', 'admin@gmail.com', '233423423', 1),
('Staff', 'staff@gmail.com', '345345435', 2),
('TRAN VAN A', 'a@gmail.com', '233423423', 3),
('TRAN VAN B', 'b@gmail.com', '567567567', 3),
('TRAN VAN C', 'c@gmail.com', '233423423', 3)
go

insert into Category (name)
values
('Cafe'),
('Sinh To'),
('Nuoc Ngot')
go

insert into Product (title, price, category_id)
values
('Cafe Nau', 25000, 1),
('Cafe Da', 25000, 1),
('RedBull', 15000, 3),
('Sinh To Bo', 55000, 2)
go

insert into Room (room_no, price, type, max_num)
values
('R001', 1000, 'NORMAL', 2),
('R002', 2000, 'VIP', 2),
('R003', 3000, 'DIAMON', 2),
('R004', 1200, 'NORMAL', 2),
('R005', 1500, 'NORMAL', 2),
('R006', 1800, 'VIP', 2)
go

insert into Booking (staff_id, customer_id, checkin, checkout)
values
(2, 3, '2022-02-18', '2022-02-25'),
(2, 3, '2022-03-02', '2022-03-06'),
(2, 4, '2022-02-22', '2022-02-25')
go

insert into UserDetail (booking_id, customer_id, room_id)
values
(1, 3, 1),
(1, 4, 1),
(1, 5, 2),
(2, 3, 2),
(2, 5, 2),
(3, 4, 3)
go

insert into BookingDetail (booking_id, room_id, price, unit)
values
(1, 1, 1000, 7),
(1, 2, 2000, 7),
(2, 2, 2000, 4),
(3, 3, 3000, 3)
go

insert into Services (booking_id, customer_id, product_id, price, amount, buy_date)
values
(1, 3, 1, 25000, 2, '2022-02-20'),
(1, 4, 2, 25000, 1, '2022-02-22'),
(2, 5, 3, 15000, 2, '2022-02-22')
go

----------------------------------------------------------------
-- 2) Xem thông tin khách hàng đã tới khách sạn gồm: tên, sđt, ngày checkin, checkout, mã phòng tìm theo customer_id -> Sử dụng proc

create proc proc_view_customer
	@customer_id int
as
begin
	select Booking.id, Users.fullname, Users.phone_number, Booking.checkin, Booking.checkout, Room.room_no
	from Users, Booking, UserDetail, Room
	where Users.id = UserDetail.customer_id
		and Booking.id = UserDetail.booking_id
		and Room.id = UserDetail.room_id
		and Users.id = @customer_id
end

exec proc_view_customer 1
exec proc_view_customer 3
exec proc_view_customer 4

-- Xem danh sách khách hàng tới khách sạn: tên, sđt, ngày checkin, checkout, mã phòng tìm theo booking_id -> sử dụng proc
create proc proc_view_booking
	@bookingId int
as
begin
	select Booking.id, Users.fullname, Users.phone_number, Booking.checkin, Booking.checkout, Room.room_no
	from Users, Booking, UserDetail, Room
	where Users.id = UserDetail.customer_id
		and Booking.id = UserDetail.booking_id
		and Room.id = UserDetail.room_id
		and Booking.id = @bookingId
end

exec proc_view_booking 1
exec proc_view_booking 2

-- Tính tổng tiền sử dụng đặt phòng theo booking_id
create proc proc_cal_money_booking
	@bookingId int
as
begin
	select Booking.id, Booking.checkin, Booking.checkout, sum(BookingDetail.price * BookingDetail.unit) TotalMoney
	from Booking, BookingDetail
	where Booking.id = BookingDetail.booking_id
		and Booking.id = @bookingId
	group by Booking.id, Booking.checkin, Booking.checkout
end

exec proc_cal_money_booking 1

-- Tính tổng tiền sử dụng dịch vụ theo booking_id
create proc proc_cal_money_services
	@bookingId int
as
begin
	select Booking.id, Booking.checkin, Booking.checkout, sum(BookingDetail.price * BookingDetail.unit + Services.price * Services.amount) TotalMoney
	from Booking, Services
	where Booking.id = Services.booking_id
		and Booking.id = @bookingId
	group by Booking.id, Booking.checkin, Booking.checkout
end

exec proc_cal_money_services 1
exec proc_cal_money_booking 1

-- Tính tổng tiền đặt phòng và dịch vụ theo booking_id
create proc proc_cal_total_booking
	@bookingId int,
	@roomMoney int output,
	@servicesMoney int output
as
begin
	select @roomMoney = sum(BookingDetail.price * BookingDetail.unit)
	from Booking, BookingDetail
	where Booking.id = BookingDetail.booking_id
		and Booking.id = @bookingId

	select @servicesMoney = sum(Services.price * Services.amount)
	from Booking, Services
	where Booking.id = Services.booking_id
		and Booking.id = @bookingId
end

declare @roomMoney int
declare @servicesMoney int
exec proc_cal_total_booking 1, @roomMoney = @roomMoney output, @servicesMoney = @servicesMoney output
declare @totalMoney int
set @totalMoney = @roomMoney + @servicesMoney
print N'Tong tien thu duoc: ' + convert(nvarchar, @totalMoney)