Untitled
unknown
plain_text
2 years ago
11 kB
4
Indexable
Never
USE [CNPM] GO /****** Object: Table [dbo].[BANGGIATT] Script Date: 4/13/2022 10:43:28 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[BANGGIATT]( [MACP] [nchar](7) NOT NULL, [GM3] [float] NULL, [KLM3] [int] NULL, [GM2] [float] NULL, [KLM2] [int] NULL, [GM1] [float] NULL, [KLM1] [int] NULL, [GK] [float] NULL, [KLK] [int] NULL, [GB1] [float] NULL, [KLB1] [int] NULL, [GB2] [float] NULL, [KLB2] [int] NULL, [GB3] [float] NULL, [KLB3] [int] NULL, [TONGKL] [float] NULL, CONSTRAINT [PK_BANGGIATT_1] PRIMARY KEY CLUSTERED ( [MACP] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[LENHDAT] Script Date: 4/13/2022 10:43:28 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[LENHDAT]( [ID] [int] IDENTITY(1,1) NOT NULL, [MACP] [nchar](7) NOT NULL, [NGAYDAT] [datetime] NULL, [LOAIGD] [nchar](1) NOT NULL, [LOAILENH] [nchar](10) NOT NULL, [SOLUONG] [int] NOT NULL, [GIADAT] [float] NOT NULL, [TRANGTHAILENH] [nvarchar](30) NOT NULL, CONSTRAINT [PK_LENHDAT] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] GO /****** Object: Table [dbo].[LENHKHOP] Script Date: 4/13/2022 10:43:28 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[LENHKHOP]( [IDKHOP] [int] IDENTITY(1,1) NOT NULL, [NGAYKHOP] [datetime] NOT NULL, [SOLUONGKHOP] [int] NOT NULL, [GIAKHOP] [float] NOT NULL, [IDLENHDAT] [int] NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[LENHKHOP] WITH CHECK ADD CONSTRAINT [FK_LENHKHOP_LENHDAT] FOREIGN KEY([IDLENHDAT]) REFERENCES [dbo].[LENHDAT] ([ID]) GO ALTER TABLE [dbo].[LENHKHOP] CHECK CONSTRAINT [FK_LENHKHOP_LENHDAT] GO /****** Object: StoredProcedure [dbo].[CursorLoaiGD] Script Date: 4/13/2022 10:43:28 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[CursorLoaiGD] @OutCrsr CURSOR VARYING OUTPUT, @macp NVARCHAR( 10), @Ngay NVARCHAR( 10), @LoaiGD CHAR AS SET DATEFORMAT DMY IF (@LoaiGD='M') SET @OutCrsr=CURSOR KEYSET FOR SELECT ID,NGAYDAT, SOLUONG, GIADAT FROM LENHDAT WHERE MACP=@macp AND DAY(NGAYDAT)=DAY(@Ngay) AND MONTH(NGAYDAT)= MONTH(@Ngay) AND YEAR(NGAYDAT)=YEAR(@Ngay) AND LOAIGD=@LoaiGD AND SOLUONG >0 ORDER BY GIADAT DESC, NGAYDAT ELSE SET @OutCrsr=CURSOR KEYSET FOR SELECT ID,NGAYDAT, SOLUONG, GIADAT FROM LENHDAT WHERE MACP=@macp AND DAY(NGAYDAT)=DAY(@Ngay) AND MONTH(NGAYDAT)= MONTH(@Ngay) AND YEAR(NGAYDAT)=YEAR(@Ngay) AND LOAIGD=@LoaiGD AND SOLUONG >0 ORDER BY GIADAT, NGAYDAT OPEN @OutCrsr GO /****** Object: StoredProcedure [dbo].[SP_KHOPLENH_LO] Script Date: 4/13/2022 10:43:28 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROC [dbo].[SP_KHOPLENH_LO] @IDLENHDAT int,@macp NVARCHAR( 10), @Ngay NVARCHAR( 10), @LoaiGD CHAR, @soluongMB INT, @giadatMB FLOAT AS SET DATEFORMAT DMY DECLARE @CrsrVar CURSOR ,@ID INT, @ngaydat NVARCHAR( 10), @soluong INT, @giadat FLOAT, @soluongkhop INT, @giakhop FLOAT, @FLAG INT =-1; IF (@LoaiGD='B') EXEC CursorLoaiGD @CrsrVar OUTPUT, @macp,@Ngay, 'M' ELSE EXEC CursorLoaiGD @CrsrVar OUTPUT, @macp,@Ngay, 'B' FETCH NEXT FROM @CrsrVar INTO @ID, @ngaydat , @soluong , @giadat --SELECT @ngaydat , @soluong , @giadat WHILE (@@FETCH_STATUS <> -1 AND @soluongMB >0) BEGIN IF(@LoaiGD='B' ) IF(@giadatMB <= @giadat) BEGIN IF @soluongMB >= @soluong BEGIN SET @soluongkhop = @soluong SET @giakhop = @giadat SET @soluongMB = @soluongMB - @soluong UPDATE dbo.LENHDAT SET SOLUONG = 0, TRANGTHAILENH=N'Khớp hết' WHERE CURRENT OF @CrsrVar END ELSE BEGIN SET @soluongkhop = @soluongMB SET @giakhop = @giadat UPDATE dbo.LENHDAT SET SOLUONG = SOLUONG - @soluongMB, TRANGTHAILENH=N'Khớp lệnh một phần' WHERE CURRENT OF @CrsrVar SET @soluongMB = 0 END -- Cập nhật table LENHKHOP INSERT INTO dbo.LenhKhop(NGAYKHOP,SOLUONGKHOP,GIAKHOP,IDLENHDAT) VALUES (GETDATE(),@soluongkhop,@giakhop, @ID ) INSERT INTO dbo.LenhKhop(NGAYKHOP,SOLUONGKHOP,GIAKHOP,IDLENHDAT) VALUES (GETDATE(),@soluongkhop,@giakhop, @IDLENHDAT ) SET @FLAG+=1 -- bật cờ cho biết đã có khớp. END ELSE GOTO THOAT ELSE --@LoaiGD='M' IF(@giadatMB >= @giadat) BEGIN IF @soluongMB > @soluong BEGIN SET @soluongkhop = @soluong SET @giakhop = @giadat SET @soluongMB = @soluongMB - @soluong UPDATE dbo.LENHDAT SET SOLUONG = 0, TRANGTHAILENH=N'Khớp hết' WHERE CURRENT OF @CrsrVar END ELSE BEGIN SET @soluongkhop = @soluongMB SET @giakhop = @giadat UPDATE dbo.LENHDAT SET SOLUONG = SOLUONG - @soluongMB, TRANGTHAILENH=N'Khớp lệnh một phần' WHERE CURRENT OF @CrsrVar SET @soluongMB = 0 END -- Cập nhật table LENHKHOP INSERT INTO dbo.LenhKhop(NGAYKHOP,SOLUONGKHOP,GIAKHOP,IDLENHDAT) VALUES (GETDATE(),@soluongkhop,@giakhop, @ID ) INSERT INTO dbo.LenhKhop(NGAYKHOP,SOLUONGKHOP,GIAKHOP,IDLENHDAT) VALUES (GETDATE(),@soluongkhop,@giakhop, @IDLENHDAT ) SET @FLAG+=1 -- bật cờ cho biết đã có khớp. END ELSE GOTO THOAT FETCH NEXT FROM @CrsrVar INTO @ID, @ngaydat , @soluong , @giadat END THOAT: --Cập nhật số lượng cho bảng LENHDAT UPDATE LENHDAT SET SOLUONG=@soluongMB WHERE ID=@IDLENHDAT --Cập nhật trạng thái cho bảng LENHDAT IF(@soluongMB=0) BEGIN UPDATE LENHDAT SET TRANGTHAILENH=N'Khớp hết' WHERE ID=@IDLENHDAT END IF(@soluongMB>0) BEGIN IF (@FLAG=-1) BEGIN UPDATE LENHDAT SET TRANGTHAILENH=N'Chờ khớp' WHERE ID=@IDLENHDAT END ELSE BEGIN UPDATE LENHDAT SET TRANGTHAILENH=N'Khớp lệnh một phần' WHERE ID=@IDLENHDAT END END --Đóng Cursor CLOSE @CrsrVar DEALLOCATE @CrsrVar GO /****** Object: Trigger [dbo].[AFTER_LENHDAT_BGTT] Script Date: 4/13/2022 10:43:28 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [dbo].[AFTER_LENHDAT_BGTT] ON [dbo].[LENHDAT] AFTER INSERT, UPDATE AS BEGIN DECLARE @MACP NCHAR(7) =(SELECT MACP FROM inserted) IF (EXISTS(SELECT MACP FROM BANGGIATT WHERE MACP= @MACP))-- Nếu cổ phiếu này đã có trong table BANGGIATT BEGIN DECLARE @GIABAN1 FLOAT, @GIABAN2 FLOAT, @GIABAN3 FLOAT, @KLBAN1 INT, @KLBAN2 INT, @KLBAN3 INT, @GIAMUA1 FLOAT ,@GIAMUA2 FLOAT,@GIAMUA3 FLOAT, @KLMUA1 INT, @KLMUA2 INT, @KLMUA3 INT, @RNMUA INT =1, @RNBAN INT =1 select top(3) @GIABAN1 = case when @RNBAN = 1 then GIADAT else @GIABAN1 end, @KLBAN1 = case when @RNBAN = 1 then SUM(SOLUONG) else @KLBAN1 end, @GIABAN2 = case when @RNBAN = 2 then GIADAT else @GIABAN2 end, @KLBAN2 = case when @RNBAN = 2 then SUM(SOLUONG) else @KLBAN2 end, @GIABAN3 = case when @RNBAN = 3 then GIADAT else @GIABAN3 end, @KLBAN3 = case when @RNBAN = 3 then SUM(SOLUONG) else @KLBAN3 end, @RNBAN += 1 from LENHDAT where LOAIGD='B' AND CAST(NGAYDAT AS DATE) = CAST(GETDATE() AS DATE) AND SOLUONG > 0 AND MACP=@MACP GROUP BY GIADAT order by GIADAT select top(3) @GIAMUA1 = case when @RNMUA = 1 then GIADAT else @GIAMUA1 end, @KLMUA1 = case when @RNMUA = 1 then SUM(SOLUONG) else @KLMUA1 end, @GIAMUA2 = case when @RNMUA = 2 then GIADAT else @GIAMUA2 end, @KLMUA2 = case when @RNMUA = 2 then SUM(SOLUONG) else @KLMUA2 end, @GIAMUA3 = case when @RNMUA = 3 then GIADAT else @GIAMUA3 end, @KLMUA3 = case when @RNMUA = 3 then SUM(SOLUONG) else @KLMUA3 end, @RNMUA += 1 from LENHDAT where LOAIGD='M' AND CAST(NGAYDAT AS DATE) = CAST(GETDATE() AS DATE) AND SOLUONG > 0 AND MACP=@MACP GROUP BY GIADAT order by GIADAT DESC --UPDATE table BANGGIATT UPDATE BANGGIATT SET GB1=@GIABAN1, GB2=@GIABAN2, GB3=@GIABAN3, KLB1=@KLBAN1, KLB2=@KLBAN2, KLB3=@KLBAN3, GM1=@GIAMUA1, GM2=@GIAMUA2, GM3=@GIAMUA3, KLM1=@KLMUA1, KLM2=@KLMUA2, KLM3=@KLMUA3 WHERE MACP=@MACP END ELSE -- Nếu cổ phiếu này chưa có trong table BANGGIATT BEGIN DECLARE @LOAIGD NCHAR(1) = (SELECT LOAIGD FROM inserted) IF(@LOAIGD='M') BEGIN DECLARE @GIAMUA FLOAT,@KLMUA INT SELECT @GIAMUA=GIADAT, @KLMUA=SOLUONG FROM inserted INSERT INTO BANGGIATT(MACP,GM1,KLM1,TONGKL) VALUES(@MACP,@GIAMUA,@KLMUA,0) END ELSE--@LOAIGD='B' BEGIN DECLARE @GIABAN FLOAT,@KLBAN INT SELECT @GIABAN=GIADAT, @KLBAN=SOLUONG FROM inserted INSERT INTO BANGGIATT(MACP,GB1,KLB1,TONGKL) VALUES(@MACP,@GIABAN,@KLBAN,0) END END END GO ALTER TABLE [dbo].[LENHDAT] ENABLE TRIGGER [AFTER_LENHDAT_BGTT] GO /****** Object: Trigger [dbo].[AFTER_LENHDAT_INSERT] Script Date: 4/13/2022 10:43:28 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [dbo].[AFTER_LENHDAT_INSERT] ON [dbo].[LENHDAT] AFTER INSERT AS BEGIN declare @IDLENHDAT int,@macp NVARCHAR( 10), @Ngay NVARCHAR( 10), @LoaiGD CHAR, @soluongMB INT, @giadatMB FLOAT SELECT @IDLENHDAT=ID,@macp=MACP,@Ngay=CONVERT(varchar(10),NGAYDAT,121), @LoaiGD=LOAIGD,@soluongMB=SOLUONG,@giadatMB=GIADAT FROM inserted EXEC [SP_KHOPLENH_LO] @IDLENHDAT,@macp,@Ngay, @LoaiGD, @soluongMB, @giadatMB END GO ALTER TABLE [dbo].[LENHDAT] ENABLE TRIGGER [AFTER_LENHDAT_INSERT] GO /****** Object: Trigger [dbo].[AFTER_LENHKHOP_INSERT] Script Date: 4/13/2022 10:43:28 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [dbo].[AFTER_LENHKHOP_INSERT] ON [dbo].[LENHKHOP] AFTER INSERT AS BEGIN DECLARE @MACP NVARCHAR(7), @LOAIGD NCHAR(1) SELECT @MACP=LD.MACP,@LOAIGD=LD.LOAIGD FROM LENHDAT LD, inserted INS WHERE LD.ID = INS.IDLENHDAT IF(@LOAIGD='M')--Vì mỗi lần khớp lệnh sẽ có 2 lệnh được đưa vào table lệnh khớp với số lượng và giá như nhau, nên chỉ sử dụng 1 trong 2 lệnh đó BEGIN UPDATE BANGGIATT SET GK = (SELECT GIAKHOP FROM inserted), KLK = (SELECT SOLUONGKHOP FROM inserted), TONGKL = TONGKL+(SELECT SOLUONGKHOP FROM inserted) WHERE MACP = @MACP END END GO ALTER TABLE [dbo].[LENHKHOP] ENABLE TRIGGER [AFTER_LENHKHOP_INSERT] GO