TAOBANG-QLCUA_HANG_THUC_AN_NHANH.sql

create database QUAN_LY_CUA_HANG_THUC_AN_NHANH
GO
--
use QUAN_LY_CUA_HANG_THUC_AN_NHANH

select * from HOADON where MABAN='H001' and VITRI='trong nhà'
--
CREATE TABLE DANGNHAP
(
	MANV INT NOT NULL,
	TENDANGNHAP NVARCHAR(50) NOT NULL,
	MATKHAU NVARCHAR(50) NOT NULL,
	TENNV NVARCHAR(50),
	CHUCVU INT DEFAULT 0,	-- 0 QUẢN LÝ; 1 NHÂN VIÊN
	CONSTRAINT PK_DANGNHAP
	PRIMARY KEY (MANV, TENDANGNHAP)
)
GO


--
CREATE TABLE LOAISP
(
	MALOAISP CHAR(5),-- FL001; DL001;
	TENLOAISP NVARCHAR(50)
	CONSTRAINT PK_LOAISP
	PRIMARY KEY(MALOAISP)
)


select * from  cthoadon
SELECT * FROM LOAISP
select * from SANPHAM --where MALOAISP= 'mlsp'
select * from SANPHAM where MALOAISP= 'DL001'

CREATE TABLE SANPHAM
(
	MASP CHAR(4) NOT NULL, -- F001 HOẶC D001 (F: FOOD; D: DRINK)
	TENSP NVARCHAR(50) NOT NULL,
	DONGIA INT DEFAULT 1000,
	MALOAISP	CHAR(5),-- FL001; DL001
	CONSTRAINT PK_SANPHAM
	PRIMARY KEY (MASP)
)
GO
--


select * from tableinfo

CREATE TABLE TABLEINFO
(
	MABAN CHAR(4),	--TRONG NHA: H; TANG: F, VIP: V + 001;
	VITRI NVARCHAR(20),
	TENBAN NVARCHAR(10),
	TINHTRANG NVARCHAR(10), -- TRỐNG; CÓ KHÁCH
	CONSTRAINT PK_TABLEINFO
	PRIMARY KEY (MABAN,VITRI)
)
GO
--

CREATE TABLE HOADON
(
	MAHOADON CHAR(5),
	CHECKIN DATETIME default getdate(),
	CHECKOUT DATETIME null,
	MABAN CHAR(4),
	VITRI NVARCHAR(20),
	TINHTRANG NVARCHAR(20), -- ĐÃ THANH TOÁN, CHƯA THANH TOÁN
	GIAMGIA int default 0
	CONSTRAINT PK_HOADON
	PRIMARY KEY (MAHOADON)
)

CREATE TABLE THONGTINCHITIETHOADON
(
	MActHOADON CHAR(5),
	MAHOADON CHAR(5),
	MASP CHAR(4),
	SOLUONG INT,
	CONSTRAINT PK_THONGTINCHITIETHOADON
	PRIMARY KEY (MActHOADON)
)

select * from THONGTINCHITIETHOADON

alter table HOADON
add GIAMGIA INT

UPDATE HOADON SET GIAMGIA =0

SELECT * FROM HOADON
SELECT * FROM THONGTINCHITIETHOADON   -- billInFo
SELECT * FROM SANPHAM
SELECT * FROM LOAISP
select * from TABLEINFO


SELECT * FROM THONGTINCHITIETHOADON where MActHOADON = 'BL001'

-- load table lên flp
create proc USP_loadTable
as select * from TABLEINFO

exec dbo.USP_loadTable
go
--
--thông tin hóa đơn

SELECT SP.TENSP, HD_iF.SOLUONG, SP.DONGIA, SP.DONGIA*HD_iF.SOLUONG AS THANHTIEN
FROM SANPHAM AS SP, HOADON AS HD, THONGTINCHITIETHOADON AS HD_iF, TABLEINFO as TB_IF
WHERE HD.MAHOADON=HD_iF.MAHOADON AND HD_iF.MASP=SP.MASP AND HD.MABAN=TB_IF.MABAN

SELECT HD.VITRI, SP.TENSP, HD_iF.SOLUONG, SP.DONGIA, SP.DONGIA* HD_iF.SOLUONG AS THÀNH_TIỀN FROM SANPHAM AS SP, HOADON AS HD, THONGTINCHITIETHOADON AS HD_iF WHERE HD.MAHOADON = HD_iF.MAHOADON AND HD_iF.MASP = SP.MASP AND HD.MABAN =MABAN  AND HD.TINHTRANG= 'Chưa thanh toán'
SELECT hd.VITRI,hd.MABAN,SP.TENSP, HD_iF.SOLUONG, SP.DONGIA, SP.DONGIA* HD_iF.SOLUONG AS THANHTIEN FROM SANPHAM AS SP, HOADON AS HD, THONGTINCHITIETHOADON AS HD_iF WHERE HD.MAHOADON = HD_iF.MAHOADON AND HD_iF.MASP = SP.MASP AND HD.TINHTRANG =N'Chưa thanh toán'


SELECT * FROM LOAISP
select * from THONGTINCHITIETHOADON
select * from hoadon


	-- Proc cho nút thêm món ăn. bài 11
create Proc USP_InsertBill
@maHD char(5), @MABAN char(4), @VITRI NVARCHAR(20)
as
begin
	declare  @tinhtrang nvarchar(20) =N'Chưa thanh toán'
	insert HOADON(MAHOADON, CHECKIN, CHECKOUT, MABAN, VITRI, TINHTRANG,GIAMGIA)
	VALUES (@maHD, GETDATE(), null, @MABAN,	@VITRI,	@tinhtrang,0)
END
GO

--
-- Bài 11
--isExitBillInfo = MActHOADON là sai
--update thiếu dữ liệu cho MActHOADON-- nó ko được upd tự động tăng dần
ALTER Proc USP_InsertBillInfo
@CTmaHD char(5), @MAHOADON char(5), @MASP char(4) , @SOLUONG int
as
begin
	DECLARE @IsExitBillInfo char(5)
	DECLARE @FoodCount int =1

	select 
			@IsExitBillInfo = MActHOADON, 
			@FoodCount = THONGTINCHITIETHOADON.SOLUONG 
			FROM THONGTINCHITIETHOADON 
			WHERE MAHOADON = @MAHOADON AND MASP = @MASP

	IF (@IsExitBillInfo is not null)
	BEGIN
		DECLARE @NEWCOUNT INT = @FoodCount + @SOLUONG
		
		IF(@NEWCOUNT >0)
			UPDATE THONGTINCHITIETHOADON SET SOLUONG = @FoodCount + @SOLUONG where MASP = @MASP
		ELSE
			DELETE THONGTINCHITIETHOADON WHERE MAHOADON = @MAHOADON AND MASP =@MASP
	
	END

	ELSE
	
	BEGIN
		insert THONGTINCHITIETHOADON
			(MActHOADON,
	MAHOADON ,
	MASP ,
	SOLUONG)
		VALUES 
		(@CTmaHD, @MAHOADON, @MASP, @SOLUONG)
	END
	
END
GO

select * from THONGTINCHITIETHOADON

select * from HOADON where MABAN ='F001' and TINHTRANG = 'Chưa thanh toán'
-- getmahoadon() HoadonDAO

select count(*) from HOADON
select * from HOADON
select * from LOAISP
select sp.MASP, sp.tensp from SANPHAM sp where MALOAISP='DL001'