ALTER PROCEDURE [AgentUPDATEBCStockBPStockByCenterShopStock]
as
DECLARE @GoodVerify INT
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
--센터재고의 현재수량-주문수량이 0보다 큰 것을 BCStock을 true로, 아니면 false로
SELECT @GoodVerify = 1
UPDATE Book
SET BCStock = 1
WHERE BookCD in
(SELECT GoodCD
from SQL2.Logics.dbo.CenterStock
WHERE CenterCD = 1 and MallVerify = 1 and GoodVerify = 1 and CurrentStock > OrderStock)
and BCStock = 0
UPDATE Book
SET BCStock = 0
WHERE BookCD in
(SELECT GoodCD
from SQL2.Logics.dbo.CenterStock
WHERE CenterCD = 1 and MallVerify = 1 and GoodVerify = @GoodVerify and CurrentStock <= OrderStock)
and BCStock = 1
SELECT @GoodVerify = 2
UPDATE Album
SET BCStock = 1
WHERE AlbumCD in
(SELECT GoodCD
from SQL2.Logics.dbo.CenterStock
WHERE CenterCD = 1 and MallVerify = 1 and GoodVerify = @GoodVerify and CurrentStock > OrderStock)
and BCStock = 0
UPDATE Album
SET BCStock = 0
WHERE AlbumCD in
(SELECT GoodCD
from SQL2.Logics.dbo.CenterStock
WHERE CenterCD = 1 and MallVerify = 1 and GoodVerify = @GoodVerify and CurrentStock <= OrderStock)
and BCStock = 1
SELECT @GoodVerify = 3
UPDATE DVDGood
SET BCStock = 1
WHERE DVDCD in
(SELECT convert(DECIMAL(20, 0), GoodCD)
from SQL2.Logics.dbo.CenterStock
WHERE CenterCD = 1 and MallVerify = 1 and GoodVerify = @GoodVerify and CurrentStock > OrderStock)
and BCStock = 0
UPDATE DVDGood
SET BCStock = 0
WHERE DVDCD in
(SELECT convert(DECIMAL(20, 0), GoodCD)
from SQL2.Logics.dbo.CenterStock
WHERE CenterCD = 1 and MallVerify = 1 and GoodVerify = @GoodVerify and CurrentStock <= OrderStock)
and BCStock = 1
SELECT @GoodVerify = 4
UPDATE ArtGood
SET BCStock = 1
WHERE ArtCD in
(SELECT convert(DECIMAL(20, 0), GoodCD)
from SQL2.Logics.dbo.CenterStock
WHERE CenterCD = 1 and MallVerify = 1 and GoodVerify = @GoodVerify and CurrentStock > OrderStock)
and BCStock = 0
UPDATE ArtGood
SET BCStock = 0
WHERE ArtCD in
(SELECT convert(DECIMAL(20, 0), GoodCD)
from SQL2.Logics.dbo.CenterStock
WHERE CenterCD = 1 and MallVerify = 1 and GoodVerify = @GoodVerify and CurrentStock <= OrderStock)
and BCStock = 1
/*
SELECT @GoodVerify = 5
SELECT @GoodVerify = 1000
UPDATE EventProduct
SET BCStock = 1
WHERE EventCD in
(SELECT GoodCD
from SQL2.Logics.dbo.CenterStock
WHERE CenterCD = 1 and MallVerify = 1 and GoodVerify = @GoodVerify and CurrentStock > OrderStock)
and BCStock = 0
UPDATE EventProduct
SET BCStock = 0
WHERE EventCD in
(SELECT GoodCD
from SQL2.Logics.dbo.CenterStock
WHERE CenterCD = 1 and MallVerify = 1 and GoodVerify = @GoodVerify and CurrentStock <= OrderStock)
and BCStock = 1
*/
--샵재고의 현재수량-주문수량이 0보다 큰 상품이 하나라도 있으면 BPStock을 true로, 아니면 false로
/*
SELECT @GoodVerify = 1
UPDATE Book
SET BPStock = 1
WHERE BookCD in
(SELECT GoodCD
from SQL2.Logics.dbo.ShopStock
WHERE MallVerify = 1 and GoodVerify = @GoodVerify and (CurrentStock > OrderStock)
group by GoodCD having (sum(CurrentStock) > sum(OrderStock)))
and BPStock = 0
UPDATE Book
SET BPStock = 0
WHERE BookCD in
(SELECT GoodCD
from SQL2.Logics.dbo.ShopStock
WHERE MallVerify = 1 and GoodVerify = @GoodVerify and (CurrentStock <= OrderStock)
group by GoodCD having (sum(CurrentStock) <= sum(OrderStock)))
and BPStock = 1
SELECT @GoodVerify = 2
UPDATE Album
SET BPStock = 1
WHERE AlbumCD in
(SELECT GoodCD
from SQL2.Logics.dbo.ShopStock
WHERE MallVerify = 1 and GoodVerify = @GoodVerify and (CurrentStock > OrderStock)
group by GoodCD having (sum(CurrentStock) > sum(OrderStock)))
and BPStock = 0
UPDATE Album
SET BPStock = 0
WHERE AlbumCD not in
(SELECT GoodCD
from SQL2.Logics.dbo.ShopStock
WHERE MallVerify = 1 and GoodVerify = @GoodVerify and (CurrentStock <= OrderStock)
group by GoodCD having (sum(CurrentStock) <= sum(OrderStock)))
and BPStock = 1
SELECT @GoodVerify = 3
UPDATE DVDGood
SET BPStock = 1
WHERE DVDCD in
(SELECT convert(DECIMAL(20, 0), GoodCD)
from SQL2.Logics.dbo.ShopStock
WHERE MallVerify = 1 and GoodVerify = @GoodVerify and (CurrentStock > OrderStock)
group by GoodCD having (sum(CurrentStock) > sum(OrderStock)))
and BPStock = 0
UPDATE DVDGood
SET BPStock = 0
WHERE DVDCD not in
(SELECT convert(DECIMAL(20, 0), GoodCD)
from SQL2.Logics.dbo.ShopStock
WHERE MallVerify = 1 and GoodVerify = @GoodVerify and (CurrentStock <= OrderStock)
group by GoodCD having (sum(CurrentStock) <= sum(OrderStock)))
and BPStock = 1
/*
아트, 하드웨어, 패션상품, 조이인박스는 BPStock은 항상 0이어야 함.
*/
*/
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
