Select OrderNum, UserID, BonusCost From CusOrder WITH (NOLOCK)
Where ShopCD = 0 AND Paymethodcd = 100 and MinDoStatusCD = 2000
and OrderDate > '@시작일자'
and OrderDate < '@마감일자'
Declare @OrderNum int
Declare @UserID Varchar(20)
Declare @BonusCost money
Declare @AccDesc varchar(200)
Declare @CancelCount int
Set @CancelCount = 0
Declare @Modify_OrderAndStock Cursor
Set @Modify_OrderAndStock = Cursor For
Select OrderNum, UserID, BonusCost From CusOrder WITH (NOLOCK)
Where ShopCD = 0 AND Paymethodcd = 100 and MinDoStatusCD = 2000 and OrderDate < '2003-03-16'
Open @Modify_OrderAndStock
Fetch Next From @Modify_OrderAndStock Into @OrderNum, @UserID, @BonusCost
While @@Fetch_Status = 0 And @CancelCount < 100 Begin
--1. 대체 주문 체크
IF NOT Exists(Select RequestMoney From CreditPayment Where OrderNum = @OrderNum And (repaybank like '%대체%' or accounter like '%대체%')) BEGIN
-- 2. 오케이캐쉬백 체크
IF NOT Exists(Select RequestMoney From CreditPayment Where OrderNum = @OrderNum And RequestType = 100 ) BEGIN
--3. 무통장입금 처리 여부 체크
IF NOT Exists(Select RequestMoney from CreditPayment Where OrderNum=@OrderNum and BManage=1 and RequestType=11) BEGIN
SET @AccDesc = Convert(varchar(20), @OrderNum)
--3. 마일리지 체크
IF @BonusCost > 0 BEGIN
Exec SQL1.Shop.dbo.MallInsertPoint @UserID, 6, @BonusCost, @AccDesc, 'system'
END
Update OrderGood Set DoStatusCD = DoStatusCD + 20000, DoDate=getdate(), DoID = 'system',
GoodDesc = '무통장입금전 주문 일괄 취소 : ' + Cast(Getdate() As Varchar(100))
Where OrderNum = @OrderNum and DostatusCD = 2000
Update CusOrder Set MinDoStatusCD = a.MinDoStatusCD
From (Select OrderNum, Min(DoStatusCD) as MinDostatusCD From OrderGood Where OrderNum = @OrderNum Group by OrderNum) a
Where CusOrder.OrderNum = a.OrderNum and CusOrder.OrderNum = @OrderNum
/**
Update CusOrder Set MinDoStatusCD = (Select Min(DoStatusCD) as MinDostatusCD From OrderGood Where OrderNum = @OrderNum )
Where CusOrder.OrderNum = @OrderNum
**/
Insert UserReplyOrder (UserID, OrderNum,ReplyType,ReplyDesc,ReplyID)
values( @UserID,@OrderNum, 19, '무통장입금전 주문 일괄 취소','system')
End
END
END
Fetch Next From @Modify_OrderAndStock Into @OrderNum, @UserID, @BonusCost
Set @CancelCount = @CancelCount + 1
End
Close @Modify_OrderAndStock
DeAllocate @Modify_OrderAndStock
