Windows Việt

CREATE TRIGGER TRIGGER_CTHH_03 ON CTHD FOR UPDATE
AS
BEGIN
   DECLARE @MAHD NVARCHAR(11)
   DECLARE @MAHH NVARCHAR(5)
   DECLARE @SLBAN1 FLOAT
   DECLARE @SLBAN2 FLOAT
   DECLARE @THANHTIEN1 FLOAT
   DECLARE @THANHTIEN2 FLOAT
   SET @MAHD=(SELECT MAHD FROM DELETED)
   SET @MAHH=(SELECT MAHH FROM DELETED)
   SET @SLBAN1=(SELECT SLBAN FROM INSERTED)
   SET @SLBAN2=(SELECT SLBAN FROM DELETED)
   SET @THANHTIEN1=(SELECT THANHTIEN FROM INSERTED)
   SET @THANHTIEN2=(SELECT THANHTIEN FROM DELETED)      
   IF((SELECT * FROM CTHD WHERE MAHD=@MAHD AND MAHH=@MAHH) IS NULL)
   BEGIN
      RAISE RROR('MA HANG HOA KHONG TON TAI',16,1)
      ROLLBACK TRAN
      RETURN
   END
   IF(@SLBAN1>(SELECT SLTON FROM HANG_HOA WHERE MAHH=@MAHH))
   BEGIN
      RAISERROR('SO LUONG BAN LON HON SO LUONG TON KHO',16,1)
      ROLLBACK TRAN
      RETURN
   END
   UPDATE HOA_DON SET TONGTIEN=TONGTIEN+(@THANHTIEN1-@THANHTIEN2) WHERE MAHD=@MAHD
   UPDATE HANG_HOA SET SLTON=SLTON+(@SLBAN2-@SLBAN1) WHERE MAHH=@MAHH
END
GO

CREATE PROC SP_XOAPHIEUXUAT
@MAPX NVARCHAR(11)
AS
   IF( NOT EXISTS(SELECT * FROM PHIEU_XUAT WHERE MAPX=@MAPX))
   BEGIN
      PRINT 'MA PHIEU XUAT TRUYEN VAO KHONG TON TAI'
      RETURN
   END
   DECLARE @CUR_PHIEUXUAT CURSOR
   SET @CUR_PHIEUXUAT=CURSOR FOR SELECT MAHH,SOLUONG FROM CTPX WHERE MAPX=@MAPX
   DECLARE @MAHH NVARCHAR(11)
   DECLARE @SOLUONG INT
   OPEN @CUR_PHIEUXUAT
   FETCH NEXT FROM @CUR_PHIEUXUAT INTO @MAHH,@SOLUONG
   WHILE(@@FETCH_STATUS=0)
   BEGIN
      DELETE FROM CTPX WHERE MAPX=@MAPX AND MAHH=@MAHH
      UPDATE HANG_HOA SET SLTON=SLTON+@SOLUONG WHERE MAHH=@MAHH
      FETCH NEXT FROM @CUR_PHIEUXUAT INTO @MAHH,@SOLUONG
   END
   DELETE FROM PHIEU_XUAT WHERE MAPX=@MAPX
   PRINT 'DA XOA PHIEU XUAT CO MA : '+@MAPX+' RA KHOI HE THONG'
   CLOSE @CUR_PHIEUXUAT
   DEALLOCATE @CUR_PHIEUXUAT
GO

CREATE TRIGGER TRIGGER_CTHH_01 ON CTHD FOR INSERT
AS
BEGIN
   DECLARE @MAHD NVARCHAR(11)
   DECLARE @MAHH NVARCHAR(5)
   DECLARE @SLBAN FLOAT
   DECLARE @THANHTIEN FLOAT
   SET @MAHD=(SELECT MAHD FROM INSERTED)
   SET @MAHH=(SELECT MAHH FROM INSERTED)
   SET @SLBAN=(SELECT SLBAN FROM INSERTED)
   SET @THANHTIEN=(SELECT THANHTIEN FROM INSERTED)
   IF(NOT EXISTS (SELECT * FROM INSERTED WHERE MAHD=@MAHD AND MAHH=@MAHH))
   BEGIN
      RAISERROR('MA HANG HOA DA TON TAI',16,1)
      ROLLBACK TRAN
      RETURN
   END
   IF(@SLBAN>(SELECT SLTON FROM HANG_HOA WHERE MAHH=@MAHH))
   BEGIN
      RAISERROR('SO LUONG BAN LON HON SO LUONG TON KHO',16,1)
      ROLLBACK TRAN
      RETURN
   END
   UPDATE HOA_DON SET TONGTIEN=TONGTIEN+@THANHTIEN WHERE MAHD=@MAHD
   UPDATE HANG_HOA SET SLTON=SLTON-@SLBAN WHERE MAHH=@MAHH
END
GO


CREATE PROC SP_TONGTIENCHIECKHAUDAILY
@MADL NVARCHAR(5),@NGAYBATDAU DATETIME,@NGAYKETTHUC DATETIME
AS
   IF( NOT EXISTS(SELECT * FROM DAI_LY WHERE MADL=@MADL))
   BEGIN
      PRINT 'MA DAI LY TRUYEN VAO KHONG TON TAI'
      RETURN
   END
   IF(DATEDIFF(DD,@NGAYKETTHUC,@NGAYBATDAU)>=0 ORDATEDIFF(DD,GETDATE(),@NGAYKETTHUC)>0)
   BEGIN
      PRINT 'NGAY BAT DAU KHONG DUOC LON HON NGAY KET THUC VA NGAY KET THUC KHONG DUOC LON HON NGAY HIEN TAI'
      RETURN
   END
   DECLARE @TONGTIEN FLOAT
   SET @TONGTIEN=0
   DECLARE @TIENCK FLOAT
   SET @TIENCK=0
   DECLARE @CUR_DAILY CURSOR
   SET @CUR_DAILY=CURSOR FOR SELECT MAHD,NGAYLAP FROM HOA_DON WHERE DATEDIFF(DD,@NGAYBATDAU,NGAYLAP)>=0 AND DATEDIFF(DD,NGAYLAP,@NGAYKETTHUC)>=0 AND @MADL=MADL
   DECLARE @MAHD NVARCHAR(11)
   DECLARE @NGAYLAP DATETIME
   OPEN @CUR_DAILY
   FETCH NEXT FROM @CUR_DAILY INTO @MAHD,@NGAYLAP
   WHILE(@@FETCH_STATUS=0)
   BEGIN
      SET @TIENCK=(SELECT SUM(CT.THANHTIEN*CT.CKBAN) FROM CTHD CT WHERE MAHD=@MAHD)
      SET @TONGTIEN=@TONGTIEN+@TIENCK
      FETCH NEXT FROM @CUR_DAILY INTO @MAHD,@NGAYLAP
   END
   PRINT 'TONG TIEN CHIEC KHAU MA UNILEVER GIAM CHO DAI LY '+@MADL+' LA :'+CAST(@TONGTIEN AS VARCHAR(100))   
   CLOSE @CUR_DAILY
   DEALLOCATE @CUR_DAILY
GO

CREATE TRIGGER TRIGGER_CTHH_02 ON CTHD FOR DELETE
AS
BEGIN
   DECLARE @MAHD NVARCHAR(11)
   DECLARE @MAHH NVARCHAR(5)
   DECLARE @SLBAN FLOAT
   DECLARE @THANHTIEN FLOAT
   SET @MAHD=(SELECT MAHD FROM DELETED)
   SET @MAHH=(SELECT MAHH FROM DELETED)
   SET @SLBAN=(SELECT SLBAN FROM DELETED)
   SET @THANHTIEN=(SELECT THANHTIEN FROM DELETED)
   IF(NOT EXISTS (SELECT * FROM DELETED WHERE MAHD=@MAHD AND MAHH=@MAHH))
   BEGIN
      RAISERROR('MA HANG HOA KHONG TON TAI',16,1)
      ROLLBACK TRAN
      RETURN
   END
   UPDATE HOA_DON SET TONGTIEN=TONGTIEN-@THANHTIEN WHERE MAHD=@MAHD
   UPDATE HANG_HOA SET SLTON=SLTON+@SLBAN WHERE MAHH=@MAHH
   IF(NOT EXISTS(SELECT * FROM CTHD WHERE MAHD=@MAHD))
      DELETE FROM HOA_DON WHERE MAHD=@MAHD
END
GO
CREATE PROC SP_TONGTIENNHOMHANG
@MANHOM NVARCHAR(5),@NGAYBATDAU DATETIME,@NGAYKETTHUC DATETIME
AS
   IF( NOT EXISTS(SELECT * FROM NHOM_HANG WHERE MANHOM=@MANHOM))
   BEGIN
      PRINT 'MA NHOM HANG TRUYEN VAO KHONG TON TAI'
      RETURN
   END
   IF(DATEDIFF(DD,@NGAYKETTHUC,@NGAYBATDAU)>=0 OR DATEDIFF(DD,GETDATE(),@NGAYKETTHUC)>0)
   BEGIN
      PRINT 'NGAY BAT DAU KHONG DUOC LON HON NGAY KET THUC VA NGAY KET THUC KHONG DUOC LON HON NGAY HIEN TAI'
      RETURN
   END
   DECLARE @TONGTIEN FLOAT
   SET @TONGTIEN=0
   DECLARE @TIENBAN FLOAT
   SET @TIENBAN=0
   DECLARE @CUR_HANGHOA CURSOR
   SET @CUR_HANGHOA=CURSOR FOR SELECT MAHH FROM HANG_HOA WHERE MANHOM=@MANHOM
   DECLARE @MAHH NVARCHAR(5)
   OPEN @CUR_HANGHOA
   FETCH NEXT FROM @CUR_HANGHOA INTO @MAHH
   WHILE(@@FETCH_STATUS=0)
   BEGIN
      SET @TIENBAN=(SELECT SUM(CT.THANHTIEN) FROM CTHD CT,HOA_DON HD WHERE CT.MAHH=@MAHH AND CT.MAHD=HD.MAHD AND DATEDIFF(DD,@NGAYBATDAU,NGAYLAP)>=0 AND DATEDIFF(DD,NGAYLAP,@NGAYKETTHUC)>=0)
      IF(@TIENBAN!=0)
         SET @TONGTIEN=@TONGTIEN+@TIENBAN
      FETCH NEXT FROM @CUR_HANGHOA INTO @MAHH
   END
   PRINT 'TONG TIEN DOANG THU BAN DUOC CUA NHOM HANG '+@MANHOM+' LA :'+CAST(@TONGTIEN AS VARCHAR(100))   
   CLOSE @CUR_HANGHOA
   DEALLOCATE @CUR_HANGHOA
GO

CREATE TRIGGER TRIGGER_CTPX_04 ON CTPX FOR INSERT
AS
BEGIN
   DECLARE @MAPX NVARCHAR(11)
   DECLARE @MAHH NVARCHAR(5)
   DECLARE @SOLUONG INT
   SET @MAPX=(SELECT MAPX FROM INSERTED)
   SET @MAHH=(SELECT MAHH FROM INSERTED)
   SET @SOLUONG=(SELECT SOLUONG FROM INSERTED)
   IF(NOT EXISTS (SELECT * FROM INSERTED WHERE MAPX=@MAPX AND MAHH=@MAHH))
   BEGIN
      RAISERROR('MA HANG HOA KHONG TON TAI',16,1)
      ROLLBACK TRAN
      RETURN
   END
   IF(@SOLUONG>(SELECT SLTON FROM HANG_HOA WHERE MAHH=@MAHH))
   BEGIN
      RAISERROR('SO LUONG BAN LON HON SO LUONG TON KHO',16,1)
      ROLLBACK TRAN
      RETURN
   END
   UPDATE HANG_HOA SET SLTON=SLTON-@SOLUONG WHERE MAHH=@MAHH
END
GO

CREATE PROC SP_TONGTIENDOI
@MADOI NVARCHAR(3),@NGAYBATDAU DATETIME,@NGAYKETTHUC DATETIME
AS
   IF( NOT EXISTS(SELECT * FROM DOI WHERE MADOI=@MADOI))
   BEGIN
      PRINT 'MA DOI TRUYEN VAO KHONG TON TAI'
      RETURN
   END
   IF(DATEDIFF(DD,@NGAYKETTHUC,@NGAYBATDAU)>=0 OR DATEDIFF(DD,GETDATE(),@NGAYKETTHUC)>0)
   BEGIN
      PRINT 'NGAY BAT DAU KHONG DUOC LON HON NGAY KET THUC VA NGAY KET THUC KHONG DUOC LON HON NGAY HIEN TAI'
      RETURN
   END
   DECLARE @TONGTIEN FLOAT
   SET @TONGTIEN=0
   DECLARE @TIEN FLOAT
   SET @TIEN=0
   DECLARE @CUR_DOI CURSOR
   SET @CUR_DOI=CURSOR FOR SELECT MANV FROM NHAN_VIEN WHERE MADOI=@MADOI
   DECLARE @MANV NVARCHAR(10)
   OPEN @CUR_DOI
   FETCH NEXT FROM @CUR_DOI INTO @MANV
   WHILE(@@FETCH_STATUS=0)
   BEGIN
      SET @TIEN=(SELECT SUM(TONGTIEN) FROM HOA_DON WHERE MANV=@MANV AND DATEDIFF(DD,@NGAYBATDAU,NGAYLAP)>=0 AND DATEDIFF(DD,NGAYLAP,@NGAYKETTHUC)>=0)
      IF(@TIEN!=0)
         SET @TONGTIEN=@TONGTIEN+@TIEN
      FETCH NEXT FROM @CUR_DOI INTO @MANV
   END
   PRINT 'TONG TIEN BAN HANG CUA DOI '+@MADOI+' LA :'+CAST(@TONGTIEN AS VARCHAR(100))   
   CLOSE @CUR_DOI
   DEALLOCATE @CUR_DOI
GO

CREATE TRIGGER TRIGGER_CTPX_05 ON CTPX FOR DELETE
AS
BEGIN
   DECLARE @MAPX NVARCHAR(11)
   DECLARE @MAHH NVARCHAR(5)
   DECLARE @SOLUONG INT
   SET @MAPX=(SELECT MAPX FROM DELETED)
   SET @MAHH=(SELECT MAHH FROM DELETED)
   SET @SOLUONG=(SELECT SOLUONG FROM DELETED)
   IF(NOT EXISTS (SELECT * FROM DELETED WHERE MAPX=@MAPX AND MAHH=@MAHH))
   BEGIN
      RAISERROR('MA HANG HOA KHONG TON TAI',16,1)
      ROLLBACK TRAN
      RETURN
   END
   UPDATE HANG_HOA SET SLTON=SLTON+@SOLUONG WHERE MAHH=@MAHH
   IF(NOT EXISTS(SELECT * FROM CTPX WHERE MAPX=@MAPX))
      DELETE FROM PHIEU_XUAT WHERE MAPX=@MAPX
END
GO

CREATE PROC SP_XOAHOADON
@MAHD NVARCHAR(11)
AS
   IF( NOT EXISTS(SELECT * FROM HOA_DON WHERE MAHD=@MAHD))
   BEGIN
      PRINT 'MA HOA TRUYEN VAO KHONG TON TAI'
      RETURN
   END
   DECLARE @CUR_HOADON CURSOR
   SET @CUR_HOADON=CURSOR FOR SELECT MAHH,SLBAN FROM CTHD WHERE MAHD=@MAHD
   DECLARE @MAHH NVARCHAR(5)
   DECLARE @SLBAN INT
   OPEN @CUR_HOADON
   FETCH NEXT FROM @CUR_HOADON INTO @MAHH,@SLBAN
   WHILE(@@FETCH_STATUS=0)
   BEGIN
      DELETE FROM CTHD WHERE MAHD=@MAHD AND MAHH=@MAHH
      UPDATE HANG_HOA SET SLTON=SLTON+@SLBAN WHERE MAHH=@MAHH
      FETCH NEXT FROM @CUR_HOADON INTO @MAHH,@SLBAN
   END
   DELETE FROM HOA_DON WHERE MAHD=@MAHD
   PRINT 'DA XOA HOA DON CO MA : '+@MAHD+' RA KHOI HE THONG'
   CLOSE @CUR_HOADON
   DEALLOCATE @CUR_HOADON
GO

CREATE PROC SP_XOAHOADON
@MAHD NVARCHAR(11)
AS
  IF( NOT EXISTS(SELECT * FROM HOA_DON WHERE MAHD=@MAHD))
  BEGIN
      PRINT 'MA HOA don KHONG TON TAI'
      RETURN
  END
  UPDATE HANG_HOA hh SET SLTON = (select slban from cthd where cthd.mahd = @mahd and cthd.mahh = hh.mahh)
  DELETE FROM CTHD WHERE MAHD=@MAHD
  DELETE FROM HOA_DON WHERE MAHD=@MAHD
  PRINT 'DA XOA'

UPDATE HANG_HOA hh SET SLTON = slton + (select slban from cthd where cthd.mahd = @mahd and cthd.mahh = hh.mahh)