Untitled

mail@pastecode.io avatar
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