Untitled
unknown
plain_text
3 years ago
11 kB
4
Indexable
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