CreateDatabase.sql

USE MASTER;
go
-- Tạo Database QuanLyTuýeninh
IF DB_ID('QuanLyNhaHangProject') IS NOT NULL
	DROP DATABASE QuanLyNhaHangProject;
GO

CREATE DATABASE QuanLyNhaHangProject;
GO
-- Tạo Table.
USE QuanLyNhaHangProject;
GO
----TẠO TABLE TÀI KHOẢN.
IF OBJECT_ID('TAIKHOAN') IS NOT NULL
	DROP TABLE TAIKHOAN;
GO

CREATE TABLE TAIKHOAN
(
	IDNhanVien VARCHAR (5) NOT NULL,
	TaiKhoan VARCHAR (30) NOT NULL UNIQUE,
	MatKhau VARCHAR (30) NOT NULL,
	CONSTRAINT PK_TAIKHOAN PRIMARY KEY (IDNhanVien)
)
GO

IF OBJECT_ID('CONGVIEC') IS NOT NULL
	DROP TABLE CONGVIEC;
GO

CREATE TABLE CONGVIEC
(
	IDCongViec INT IDENTITY (1,1),
	TenCongViec VARCHAR(30) NOT NULL,
	LuongCongViec INT NOT NULL,
	CONSTRAINT PK_CONGVIEC PRIMARY KEY (IDCongViec)
)
GO

IF OBJECT_ID('FUNC_CREATE_ID_NHANVIEN') IS NOT NULL 
	DROP FUNCTION FUNC_CREATE_ID_NHANVIEN;
GO

CREATE FUNCTION FUNC_CREATE_ID_NHANVIEN()
RETURNS VARCHAR (5)
AS
BEGIN
	DECLARE @ID VARCHAR(5)
	IF (SELECT COUNT (IDNhanVien) from NHANVIEN) = 0
			SET @ID = '0'
	ELSE 
		SELECT @ID = MAX(RIGHT(IDNhanVien, 3)) FROM NHANVIEN
		SELECT @ID = CASE
					WHEN @ID >= 0 AND @ID < 9 THEN 'NV00' + CONVERT(CHAR, CONVERT(INT , @ID) + 1)
					WHEN @ID >= 9 THEN 'NV0' + CONVERT(CHAR, CONVERT(INT , @ID) + 1)
		END
	RETURN @ID
END
GO

IF OBJECT_ID('NHANVIEN') IS NOT NULL
	DROP TABLE NHANVIEN;
GO

CREATE TABLE NHANVIEN
(
	IDNhanVien VARCHAR (5) NOT NULL DEFAULT DBO.FUNC_CREATE_ID_NHANVIEN(),
	Ho NVARCHAR (10) NOT NULL,
	Ten NVARCHAR (20) NOT NULL,
	NgaySinh DATE NOT NULL,
	SDT VARCHAR(11) NULL,
	DiaChi NVARCHAR(100) NULL,
	Email VARCHAR(30) NULL,
	HinhNV VARCHAR(100) NULL,
	IDCongViec INT NOT NULL,
	TrangThai BIT NOT NULL,
	CONSTRAINT PK_NHANVIEN PRIMARY KEY (IDNhanVien)
)
GO

IF OBJECT_ID('DANHMUC') IS NOT NULL 
	DROP TABLE DANHMUC;
GO

CREATE TABLE DANHMUC 
(
	IDDanhMuc INT IDENTITY (1,1) NOT NULL,
	TenDanhMuc NVARCHAR(30) NOT NULL,
	TrangThai BIT NOT NULL,
	CONSTRAINT PK_DANHMUC PRIMARY KEY (IDDanhMuc)
)
go



IF OBJECT_ID('FUNC_CREATE_ID_MONAN') IS NOT NULL 
	DROP FUNCTION FUNC_CREATE_ID_MONAN;
GO

CREATE FUNCTION FUNC_CREATE_ID_MONAN()
RETURNS VARCHAR (5)
AS
BEGIN
	DECLARE @ID VARCHAR(5)
	IF (SELECT COUNT (IDMonAn) from MONAN) = 0
			SET @ID = '0'
	ELSE 
		SELECT @ID = MAX(RIGHT(IDMonAn, 3)) FROM MONAN
		SELECT @ID = CASE
					WHEN @ID >= 0 AND @ID < 9 THEN 'MA00' + CONVERT(CHAR, CONVERT(INT , @ID) + 1)
					WHEN @ID >= 9 THEN 'MA0' + CONVERT(CHAR, CONVERT(INT , @ID) + 1)
		END
	RETURN @ID
END
GO

IF OBJECT_ID('MONAN') IS NOT NULL 
	DROP TABLE MONAN;
GO

CREATE TABLE MONAN
(
	IDMonAn VARCHAR(5) NOT NULL DEFAULT DBO.FUNC_CREATE_ID_MONAN(),
	IDDanhMuc INT NOT NULL,
	TenMon NVARCHAR(30) NOT NULL UNIQUE,
	GiaTien INT NOT NULL,
	HinhMA VARCHAR(100) NULL,
	TrangThai BIT NOT NULL,
	CONSTRAINT PK_MONAN PRIMARY KEY (IDMonAn)
)
GO

IF OBJECT_ID('BAN') IS NOT NULL
	DROP TABLE BAN
GO

CREATE TABLE BAN
(
	IDBan INT IDENTITY (1 , 1) NOT NULL,
	TenBan NVARCHAR (20) NOT NULL UNIQUE,
	TrangThai BIT NOT NULL,
	SucChua INT NOT NULL,
	TrangThaiXoa BIT NOT NULL,
	CONSTRAINT PK_BAN PRIMARY KEY (IDBan)
)
GO

IF OBJECT_ID('getNewID') IS NOT NULL
	DROP VIEW getNewID
GO

CREATE VIEW getNewID AS SELECT NEWID() AS new_id
GO

IF OBJECT_ID('FUNC_CREATE_ID_HOADON') IS NOT NULL
	DROP FUNCTION FUNC_CREATE_ID_HOADON
GO

CREATE FUNCTION FUNC_CREATE_ID_HOADON()
RETURNS VARCHAR(10)
AS 
BEGIN 
	DECLARE @ID VARCHAR(10) = ''
	WHILE (@ID = '' OR (SELECT IDHoaDon FROM HOADON WHERE IDHoaDon = @ID) IS NOT NULL)
	BEGIN
		SET @ID = CONVERT(nvarchar(10),LEFT(REPLACE((select new_id from getNewID),'-',''),10))
	END
	RETURN @ID
END
GO

IF OBJECT_ID('HOADON') IS NOT NULL
	DROP TABLE HOADON
GO

CREATE TABLE HOADON
(
	IDHoaDon VARCHAR(10) NOT NULL DEFAULT DBO.FUNC_CREATE_ID_HOADON(),
	IDBan INT NOT NULL,
	IDNhanVien VARCHAR(5) NOT NULL,
	NgayLap DATETIME NOT NULL,
	NgayThanhToan DATETIME,
	TongTien INT NOT NULL,
	KhuyenMai VARCHAR(10) NULL,
	CONSTRAINT PK_HOADON PRIMARY KEY (IDHoaDon)
)
GO

IF OBJECT_ID('CHITIETHOADON') IS NOT NULL
	DROP TABLE CHITIETHOADON
GO


CREATE TABLE CHITIETHOADON
(
	IDHoaDon VARCHAR(10) NOT NULL,
	IDMonAn VARCHAR(5) NOT NULL,
	SoLuong INT NOT NULL,
	CONSTRAINT PK_CHITIETHOADON PRIMARY KEY (IDHoaDon,IDMonAn)
)
GO

ALTER TABLE TAIKHOAN
WITH NOCHECK ADD CONSTRAINT FK_TAIKHOAN_NHANVIEN
FOREIGN KEY (IDNhanVien) REFERENCES NHANVIEN (IDNhanVien)
GO

ALTER TABLE MONAN
WITH NOCHECK ADD CONSTRAINT FK_MONAN_DANHMUC
FOREIGN KEY (IDDanhMuc) REFERENCES DANHMUC (IDDanhMuc)
GO

ALTER TABLE HOADON
WITH NOCHECK ADD CONSTRAINT FK_HOADON_NHANVIEN
FOREIGN KEY (IDNhanVien) REFERENCES NHANVIEN (IDNhanVien)
GO

ALTER TABLE CHITIETHOADON
WITH NOCHECK ADD CONSTRAINT FK_CHITIETHOADON_HOADON
FOREIGN KEY (IDHoaDon) REFERENCES HOADON (IDHoaDon)
GO

ALTER TABLE HOADON
WITH NOCHECK ADD CONSTRAINT FK_HOADON_BAN
FOREIGN KEY (IDBan) REFERENCES BAN (IDBan)
GO

ALTER TABLE CHITIETHOADON
WITH NOCHECK ADD CONSTRAINT FK_CHITIETHOADON_MONAN
FOREIGN KEY (IDMonAn) REFERENCES MONAN (IDMonAn)
GO

ALTER TABLE NHANVIEN
WITH NOCHECK ADD CONSTRAINT FK_NHANVIEN_CONGVIEC
FOREIGN KEY (IDCongViec) REFERENCES CONGVIEC (IDCongViec)
GO