/*'절판','품절','판매금지','거래정리','표시안함'
주문한 상품중에 엥꼬난거 쿼리해서 샵마스터에 보여 주기
단 이 쿼리는 도서에 국한된 것입니다.
작성자 : 한영민
작성일 : 2003-04-26(화창한 토요일 오전 10:27)*/
Create Proc ShopMasterGood_EngKo
as
if exists (select * from dbo.sysobjects where id = object_id(N'[tmpCenterStock]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tmpCenterStock]
if exists (select * from dbo.sysobjects where id = object_id(N'[tmpBook]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tmpBook]
--센터 현재고가 0인 상품을 tmp에 담는다.
Select GoodCD, GoodPubs, GoodName, CurrentStock, OrderStock, PositionCD
INTO tmpCenterStock
from CenterStock
where GoodVerify = 1 and OrderStock > 0
--상품중에 판매 불가능 한 상품을 tmp에 담는다.
Select BookCD as GoodCD
INTO tmpBook
from SQL1.Shop.dbo.Book
where StateCD IN (3,5,6,7,9)
--현재고가 0인것과 판매가 불가능한 상품을 조인하면 실재고가 0개인 판매 불가 상품이 나온다.
if exists (select * from dbo.sysobjects where id = object_id(N'[tmpunAbleGood]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [tmpunAbleGood]
Select tcs.PositionCD,tb.GoodCD,tcs.GoodPubs,tcs.GoodName,tcs.CurrentStock,tcs.OrderStock
into tmpunAbleGood From tmpCenterStock as tcs INNER JOIN tmpBook as tb On tcs.GoodCD = tb.GoodCD
Select og.OrderNum, og.GoodSEQ, og.SEQ, co.UserID, co.UserName, og.GoodCD, og.GoodName, co.UserEmail, co.UserPhone, og.OrderDate
From OrderGood as og
inner join (Select GoodCD From tmpunAbleGood) as unAble on og.GoodCD = unAble.GoodCD and og.GoodVerify = 1 and og.DoStatusCD = 1000
inner join CusOrder as co on og.OrderNum=co.OrderNum
drop table tmpCenterStock
drop table tmpBook
drop table tmpunAbleGood
----------프로시저 끝-----------
exec ShopMasterGood_EngKo
--평균 12초
/*
--이건 아무것도 아님
Select * from OrderGood Where Convert(varchar(10), OrderNum) +'-'+ Convert(varchar(4), GoodSEQ) +'-'+ Convert(varchar(4), SEQ) in
(
Select Convert(varchar(10), og.OrderNum) +'-'+ Convert(varchar(4), og.GoodSEQ) +'-'+ Convert(varchar(4), og.SEQ)
From OrderGood as og
inner join (Select GoodCD From tmpunAbleGood) as unAble on og.GoodCD = unAble.GoodCD and og.GoodVerify = 1 and og.DoStatusCD = 1000
inner join CusOrder as co on og.OrderNum=co.OrderNum
)
*/
