SQLQuery_QLThuVien_BTL_NET1.sql

USE BTL_NET1_QLThuVien
GO
--TAO KHOA NGOAI PRIMARYKEY
GO
ALTER TABLE SACHMUON ADD CONSTRAINT FRK_SACHMUON_PHIEUMUON FOREIGN KEY(MaPhieuMuon) REFERENCES PHIEUMUON(MaPhieuMuon)
ALTER TABLE SACHMUON ADD CONSTRAINT FRK_SACHMUON_SACH FOREIGN KEY (MaSach) REFERENCES SACH(MaSach)

ALTER TABLE PHIEUNHACTRA ADD CONSTRAINT FRK_PHIEUNHACTRA_NHANVIEN FOREIGN KEY (MaNhanVien) REFERENCES NHANVIEN(MaNhanVien)
ALTER TABLE PHIEUNHACTRA ADD CONSTRAINT FRK_PHIEUNHACTRA_SACH FOREIGN KEY (MaSach) REFERENCES SACH(MaSach)
ALTER TABLE PHIEUNHACTRA ADD CONSTRAINT FRK_PHIEUNHACTRA_THETHUVIEN FOREIGN KEY (MaThe) REFERENCES THETHUVIEN(MaThe)

ALTER TABLE PHIEUMUON ADD CONSTRAINT FRK_PHIEUMUON_SACH FOREIGN KEY (MaSach) REFERENCES SACH(MaSach)
ALTER TABLE PHIEUMUON ADD CONSTRAINT FRK_PHIEUMUON_THETHUVIEN FOREIGN KEY (MaThe) REFERENCES THETHUVIEN(MaThe)

alter table SACH add constraint def_SACH_SoLuong check(SoLuong>0)
alter table NHANVIEN add constraint unq_NHANVIEN_TenNV_DiaChiNV unique(TenNV,DiaChiNV),
constraint def_NHANVIEN_NgaySinh default(getdate()) for NgaySinh,
constraint def_NHANVIEN_NgayVaoLam default(getdate()) for NgayVaoLam,
constraint def_NHANVIEN_SoDienThoaiNV default(N'Chua co') for SoDienThoaiNV

alter table THETHUVIEN ADD CONSTRAINT UNQ_THETHUVIEN_TenSV unique(TenSV,DiaChiSV),
	constraint def_THETHUVIEN_NgayTaoThe DEFAULT(GETDATE()) for NgayTaoThe,
	constraint def_THETHUVIEN_NgayTheHetHan default(getdate()) for NgayTheHetHan,
	constraint def_THETHUVIEN_NgaySinh default(getdate()) for NgaySinh,
	constraint def_THETHUVIEN_SoDienThoaiSV default(getdate()) for SoDienThoaiSV

alter table SACHMUON ADD
	CONSTRAINT ckh_SACHMUON_SoLuongSachMuon check(SoLuongSachMuon>0),
	constraint def_SACHMUON_NgayTra default(getdate()) for NgayTra

alter table PHIEUNHACTRA add CONSTRAINT CHK_PHIEUNHACTRA_NgayLapThe default (getdate()) for NgayLapThe,
constraint ckh_PHIEUNHACTRA_DonGiaPhat check(DonGiaPhat>=0)

alter table PHIEUMUON add constraint chk_PHIEUMUON_NgayMuon default(getdate()) for NgayMuon
go
use BTL_NET1_QLThuVien
go

use BTL_NET1_QLThuVien
CREATE PROC sp_LOADSACH
AS
	BEGIN
		SELECT * FROM SACH
	END
GO

CREATE PROC sp_SUASACH
@MaSach CHAR(5),
@TenSach NVARCHAR(200),
@TheLoai NVARCHAR(50),
@TinhTrang NVARCHAR(20),
@SoLuong INT,
@NhaXuatBan NVARCHAR(200),
@NamXuatBan CHAR(4),
@TacGia NVARCHAR(200),
@Anh Image=NULL
AS
	BEGIN
	IF NOT EXISTS(SELECT MaSach FROM SACH WHERE MaSach=@MaSach)
		RETURN 1 ---KHONG TON TAI SACH
		ELSE IF(@Anh IS NULL)
		BEGIN
		UPDATE SACH
		SET TenSach=@TenSach,TheLoai=@TheLoai, TinhTrang=@TinhTrang,SoLuong=@SoLuong, NhaXuatBan=@NhaXuatBan,NamXuatBan=@NamXuatBan,TacGia=@TacGia
		WHERE MaSach=@MaSach
		END
		ELSE
		UPDATE SACH
		SET TenSach=@TenSach,TheLoai=@TheLoai,TinhTrang=@TinhTrang,SoLuong=@SoLuong,NhaXuatBan=@NhaXuatBan,NamXuatBan=@NamXuatBan,TacGia=@TacGia,Anh=@Anh
		WHERE MaSach = @MaSach
	END
GO
drop proc sp_SUASACH
go

CREATE PROC sp_LUUSACH
@MaSach CHAR(5),
@TenSach NVARCHAR(200),
@TheLoai NVARCHAR(50),
@TinhTrang NVARCHAR(20),
@SoLuong INT,
@NhaXuatBan NVARCHAR(200),
@NamXuatBan CHAR(4),
@TacGia NVARCHAR(200),
@Anh IMAGE
AS
	BEGIN
		IF EXISTS(SELECT * FROM SACH WHERE MaSach=@MaSach)
		RETURN 1 ---TON TAI SACH
		INSERT INTO SACH VALUES (@MaSach,@TenSach,@TheLoai,@TinhTrang,@SoLuong,@NhaXuatBan,@NamXuatBan,@TacGia,@Anh)
	END

GO
CREATE PROC sp_XOASACH
@MaSach CHAR(5)
AS
	BEGIN 
		IF EXISTS(SELECT * FROM PHIEUMUON WHERE MaSach=@MaSach)
		RETURN 1 ---TON TAI MASACH TRONG PHIEUMUON
		IF EXISTS(SELECT * FROM PHIEUNHACTRA WHERE MaSach=@MaSach)
		RETURN 2 ---TON TAI MASACH TRONG PHIEUNHACTRA
		IF EXISTS(SELECT * FROM SACHMUON WHERE MaSach = @MaSach)
		RETURN 3 ---TON TAI MASACH TRONG PHIEUNHACTRA
		DELETE FROM SACH
		WHERE MaSach = @MASACH
	END
GO
-------TRUY VAN DU LIEU SACHMUON
CREATE PROC	sp_SUASACHMUON
AS
	BEGIN 
		SELECT MaPhieuMuon,MaSach,TinhTrangSachMuon,CONVERT(CHAR(10),NgayTra,103) as NgayTra FROM SACHMUON
	END
GO
SELECT * FROM SACHMUON
GO
CREATE PROC sp_SUASACHMUON
	@MaPhieuMuon CHAR(5),
	@MaSach CHAR(5),
	@TinhTrang NVARCHAR(20),
	@SoLuongSachMuon INT,
	@NgayTra DATETIME
AS
	BEGIN
		IF NOT EXISTS(SELECT * FROM SACH WHERE MaSach=@MaSach)
		RETURN 1 ---KHONG TON TAI SACH
		IF NOT EXISTS(SELECT * FROM PHIEUMUON WHERE MaPM = @MaPhieuMuon)
		RETURN 2 ---KHONG TON TAI PHIEUMUON
		UPDATE SACHMUON
		SET TinhTrangSachMuon=@TinhTrang, 
		SoLuongSachMuon=@SoLuongSachMuon,
		NgayTra=@NgayTra
		WHERE MaPhieuMuon=@MaPhieuMuon AND MaSach=@MaSach
	END
GO
CREATE PROC sp_LUUSACHMUON
	@MaPhieuMuon CHAR(5),
	@MaSach CHAR(5),
	@SoLuongSachMuon INT,
	@TinhTrang NVARCHAR(20),
	@NgayTra DATETIME
AS
	BEGIN
		IF NOT EXISTS(SELECT * FROM SACH WHERE MaSach=@MaSach)
		RETURN 1 ---KHONG TON TAI SACH
		IF NOT EXISTS(SELECT * FROM PHIEUMUON WHERE MaPM = @MaPhieuMuon)
		RETURN 2 ---KHONG TON TAI PHIEUMUON
		INSERT INTO SACHMUON VALUES (@MaPhieuMuon,@MaSach,@TinhTrang,@SoLuongSachMuon,@NgayTra)
	END
GO
CREATE PROC sp_XOASACHMUON
@MaPhieuMuon CHAR(5),
@MaSach CHAR(5)
AS
	BEGIN 
		IF NOT EXISTS(SELECT * FROM SACHMUON WHERE MaPhieuMuon=@MaPhieuMuon)
		RETURN 1 ---KHONG TON TAI MAPHIEUMUON
		IF NOT EXISTS(SELECT * FROM SACHMUON WHERE MaSach=@MaSach)
		RETURN 2 ---KHONG TON TAI MASACH
		DELETE FROM SACHMUON
		WHERE @MaPhieuMuon=MaPhieuMuon AND @MaSach=MaSach
	END
GO
SELECT * FROM SACHMUON
SELECT * FROM PHIEUMUON
--------TRUY VAN DU LIEU THETHUVIEN
GO
CREATE PROC sp_LOADTHETHUVIEN
AS
	BEGIN
		SELECT MaTheThuVien,TenSinhVien,
		CASE WHEN GioiTinh=1 THEN 'Nam' ELSE N'Nu' END AS GioiTinh, 
		NgaySinh,DiaChiSV,SoDienThoaiSV,NgayTaoThe,NgayTheHetHan FROM THETHUVIEN
	END
GO
CREATE PROC sp_SUATHETHUVIEN
	@MaThe CHAR(5),
	@TenSV NVARCHAR(100),
	@GioiTinh Bit,
	@NgaySinh DATETIME,
	@DiaChiSV NVARCHAR(200),
	@DienThoai NVARCHAR(20),
	@NgayTaoThe DATETIME,
	@NgayTheHetHan DATETIME
AS
	BEGIN
		IF NOT EXISTS(SELECT * FROM THETHUVIEN WHERE MaTheThuVien=@MaThe)
		RETURN 1 ---KHONG TON TAI THETHUVIEN
		UPDATE THETHUVIEN
		SET TenSinhVien=@TenSV,
		GioiTinh=@GioiTinh,
		NgaySinh=@NgaySinh,
		DiaChiSV=@DiaChiSV,
		SoDienThoaiSV=@DienThoai,
		NgayTaoThe=@NgayTaoThe,
		NgayTheHetHan=@NgayTheHetHan
		WHERE MaTheThuVien=@MaThe
	END
GO
CREATE PROCEDURE sp_LUUTHETHUVIEN
@MaThe CHAR(5),
@TenSinhVien NVARCHAR(100),
@GioiTinh Bit,
@NgaySinh DATETIME,
@DiaChiSinhVien NVARCHAR(200),
@SoDienThoai NVARCHAR(20),
@NgayTaoThe DATETIME,
@NgayHetHanThe DATETIME
AS
	BEGIN
	IF EXISTS(SELECT * FROM THETHUVIEN WHERE MaTheThuVien=@MaThe)
	RETURN 1
	INSERT INTO THETHUVIEN VALUES (@MaThe,@TenSinhVien,@GioiTinh,@NgaySinh,@DiaChiSinhVien,@SoDienThoai,@NgayTaoThe,@NgayHetHanThe)
	END
GO
CREATE PROCEDURE sp_XOATHETHUVIEN
@Mathetv CHAR(5)
AS
	BEGIN
		IF EXISTS(SELECT * FROM PHIEUMUON WHERE MaThe=@Mathetv)
		RETURN 1 ---TON TAI THETV
		IF EXISTS(SELECT * FROM PHIEUNHACTRA WHERE MaTheThuVien=@Mathetv)
		RETURN 1 ---TON TAI THETV
		DELETE FROM THETHUVIEN
		WHERE @Mathetv=MaTheThuVien
	END
GO
-----TRUY VAN DU LIEU NHANVIEN
CREATE PROCEDURE sp_SUANHANVIEN
@MaNhanVien CHAR(5),
@TenNhanVien NVARCHAR(100),
@NgaySinh DATETIME,
@NgayVaoLam DATETIME,
@GioiTinh Bit,
@ChucVuNV NVARCHAR(50),
@DiaChiNV NVARCHAR(200),
@DienThoai NVARCHAR(20)
AS
	BEGIN
	IF NOT EXISTS(SELECT * FROM NHANVIEN WHERE MaNV=@MaNhanVien)
	RETURN 1 ---KHONG TON TAI MA NHAN VIEN
	UPDATE NHANVIEN
	SET TenNV=@TenNhanVien,NgaySinh=@NgaySinh,GioiTinh=@GioiTinh,DiaChiNV=@DiaChiNV,SoDienThoaiNV=@DienThoai,ChucVuNV=@ChucVuNV,NgayVaoLam=@NgayVaoLam
	WHERE MaNV=@MaNhanVien
	END
GO
CREATE PROCEDURE sp_LUUNHANVIEN
@MaNV CHAR(5),
@TenNV NVARCHAR(100),
@GioiTinh Bit,
@ChucVuNV NVARCHAR(50),
@DiaChiNV NVARCHAR(200),
@DienThoai NVARCHAR(20),
@NgaySinhNV DATETIME,
@NgayVaoLam DATETIME
AS
	BEGIN 
	IF EXISTS(SELECT * FROM NHANVIEN WHERE MaNV = @MaNV)
	RETURN 1 ---KHONG TON TAI MANV
	INSERT INTO NHANVIEN VALUES (@MaNV,@TenNV,@NgaySinhNV,@GioiTinh,@DiaChiNV,@DienThoai,@NgayVaoLam,@ChucVuNV)
	END
GO 
CREATE PROCEDURE sp_XOANHANVIEN
@MaNhanVien CHAR(5)
AS
	BEGIN
	IF EXISTS(SELECT * FROM PHIEUNHACTRA WHERE MaNhanVien=@MaNhanVien)
	RETURN 1 ---TON TAI MA NV
	DELETE FROM NHANVIEN
	WHERE @MaNhanVien=MaNV
	END
GO
----- truy van du lieu PHIEUMUON
CREATE PROCEDURE sp_LOADPHIEUMUON
AS
	BEGIN 
	SELECT MaPM,MaSach,MaThe,CONVERT(CHAR(10),NgayMuon,103) as NgayMuon FROM PHIEUMUON
	END
GO
CREATE PROCEDURE sp_SUAPHIEUMUON
@MaPhieuMuon CHAR(5),
@MaSach CHAR(5),
@MaThe CHAR(5),
@NgayMuon DATETIME
AS
	BEGIN
	IF NOT EXISTS(SELECT * FROM PHIEUMUON WHERE MaPM=@MaPhieuMuon)
	RETURN 1 ---KHONG TON TAI MA PHIEU MUON
	IF NOT EXISTS(SELECT * FROM SACH WHERE MaSach=@MaSach)
	RETURN 2 ---KHONG TON TAI MA SACH
	IF NOT EXISTS(SELECT * FROM THETHUVIEN WHERE MaTheThuVien=@MaThe)
	RETURN 3 ---KHONG TON TAI MA THE THU VIEN
	UPDATE PHIEUMUON
	SET MaSach=@MaSach, MaThe=@MaThe,NgayMuon=@NgayMuon
	WHERE MaPM=@MaPhieuMuon
	END
GO
CREATE PROCEDURE sp_LUUPHIEUMUON
@MaPhieuMuon CHAR(5),
@MaSach CHAR(5),
@MaThe CHAR(5),
@NgayMuon DATETIME
AS
	BEGIN
	IF EXISTS(SELECT * FROM PHIEUMUON WHERE MaPM=@MaPhieuMuon)
	RETURN 1 ---TON TAI MA PHIEU MUON
	IF NOT EXISTS(SELECT * FROM SACH WHERE MaSach=@MaSach)
	RETURN 2 ---KHONG TON TAI MA SACH
	IF NOT EXISTS(SELECT * FROM THETHUVIEN WHERE MaTheThuVien=@MaThe)
	RETURN 3 ---KHONG TON TAI MA THE TV
	INSERT INTO PHIEUMUON VALUES (@MaPhieuMuon,@MaSach,@MaThe,@NgayMuon)
	END
GO
CREATE PROCEDURE sp_XOAPHIEUMUON
	@MaPM CHAR(5)
AS
	BEGIN
	IF EXISTS(SELECT * FROM SACHMUON WHERE MaPhieuMuon=@MaPM)
	RETURN 1 ---TON TAI MA PM
	DELETE FROM PHIEUMUON
	WHERE @MaPM=MaPM
	END
GO
---------TRUY VAN DU LIEU PHIEUNHACTRA
CREATE PROCEDURE sp_LOADPHIEUNHACTRA
AS
	BEGIN
	SELECT MaPNT,MaTheThuVien,CONVERT(CHAR(10),NgayLapPhieu,103) as NgayLapPhieu,DonGiaPhat,MaNhanVien,MaSach FROM PHIEUNHACTRA
	END
GO
CREATE PROCEDURE sp_SUAPHIEUNHACTRA
	@MaPNT CHAR(5),
	@MaTheTV CHAr(5),
	@NgayLap DATETIME,
	@DonGiaPhat INT,
	@MaNV CHAR(5),
	@MaSach CHAR(5)
AS
	BEGIN
	IF NOT EXISTS(SELECT * FROM PHIEUNHACTRA WHERE MaPNT=@MaPNT)
	RETURN 1 ---KHONG TON TAI MA PNT
	IF NOT EXISTS(SELECT * FROM NHANVIEN WHERE MaNV=@MaNV)
	RETURN 2 ---KHONG TON TAI MA NV
	IF NOT EXISTS(SELECT * FROM THETHUVIEN WHERE MaTheThuVien=@MaTheTV)
	RETURN 3 ---KHONG TON TAI MA THE TV
	IF NOT EXISTS(SELECT * FROM SACH WHERE MaSach=@MaSach)
	RETURN 4 ---KHONG TON TAI MA SACH
	UPDATE PHIEUNHACTRA
	SET MaTheThuVien=@MaTheTV,
	NgayLapPhieu=@NgayLap,
	DonGiaPhat=@DonGiaPhat,
	MaNhanVien=@MaNV,
	MaSach=@MaSach
	WHERE MaPNT=@MaPNT
	END
GO
CREATE PROCEDURE sp_LUUPHIEUNHACTRA
	@MaPNT CHAR(5),
	@MaTheTV CHAR(5),
	@NgayLapPNT DATETIME,
	@DonGiaPhat INT,
	@MaNV CHAR(5),
	@MaSach CHAR(5)
AS
	BEGIN
	IF EXISTS(SELECT * FROM PHIEUNHACTRA WHERE MaPNT=@MaPNT)
	RETURN 1 ---TON TAI MA PNT
	IF NOT EXISTS(SELECT * FROM NHANVIEN WHERE MaNV=@MaNV)
	RETURN 2 ---KHONG TON TAI MA NV
	IF NOT EXISTS(SELECT * FROM THETHUVIEN WHERE MaTheThuVien=@MaTheTV)
	RETURN 3 ----KHONG TON TAI MA THETV
	IF NOT EXISTS(SELECT * FROM SACH WHERE MaSach=@MaSach)
	RETURN 4 ----KHONG TON TAI MA SACH
	INSERT INTO PHIEUNHACTRA VALUES (@MaPNT,@MaTheTV,@NgayLapPNT,@DonGiaPhat,@MaNV,@MaSach)
	END
GO
CREATE PROCEDURE sp_XOAPHIEUNHACTRA
@MaPNT CHAR(5)
AS
	BEGIN
	DELETE FROM PHIEUNHACTRA
	WHERE @MaPNT=MaPNT
	END
GO
-------------------
GO
CREATE TABLE TAIKHOAN
(
	TenDN NVARCHAR(100) not null primary key,
	MK NVARCHAR(100) not null,
	NHMK NVARCHAR(100) not null,
)
GO
CREATE PROCEDURE sp_DOCTK
	@TenDN NVARCHAR(100),
	@MK NVARCHAR(100)
AS
	BEGIN
	SELECT TAIKHOAN.TenDN,MK FROM TAIKHOAN WHERE TenDN=@TenDN
	END
GO
CREATE PROC sp_LUUTK
@TenDN NVARCHAR(100),
@MK NVARCHAR(100),
@NHMK NVARCHAR(100)
AS
	BEGIN 
	INSERT INTO TAIKHOAN VALUES (@TenDN,@MK,@NHMK)
	END
-----------DEM TONG
GO
CREATE PROC sp_DEMTK
AS
	BEGIN
	SELECT COUNT(*) FROM SACH
	END
/*GO
SELECT * FROM PHIEUNHACTRA
GO
CREATE PROC sp_DOCPHIEUMUONSINHVIEN
@MaThe CHAR(3)
AS
	BEGIN
	SELECT B.MaPM, A.MaSach,NgayMuon
	FROM SACH A, PHIEUMUON B
	WHERE A.MaSach = B.MaSach AND MaThe=@Mathe
	END

*/