/*
1, 20일 금요일에 주문항목중 PointHistory에 없는것 최초(OrderDate)를 찾는다.
2, 23일 월요일 주문항목중 PointHistory에 없는 마지막(OrderDate)를 찾는다.
3, 주문항목들의 마일리지가 쌓여 있지 않음으로 해당 OrderGood의 Bonus를 찾아 합하여 PointHistory에 더한다.(Acctype=3)
4, MallUser 테이블에 마일리지를 Point, Bonus에 더한다.
*/
--주문에서 마일리지가 마지막으로 쌓인 데이터 ('2002-12-20 21:07:46.347')
Select OrderNum from CusOrder
Where OrderDate>'2002-12-23 00:00:00.000' and OrderDate<'2002-12-24 23:59:59.000'
Select * from SQL2.Logics.dbo.OrderGood as OG join PointHistory as PH
On OG.OrderNum=PH.OrderNum
Where OrderDate>'2002-12-20 21:07:46.347' and OrderDate<'2002-12-20 23:59:59.000'
and AccType=3
Order by OrderDate desc
Select * from PointHistory Where AccType=3
Select OrderNum from CusOrder Where OrderDate>'2002-12-23 00:00:00.000' and OrderDate<'2002-12-23 00:59:59.000' order by ordernum asc
1397589
Select OrderNum from CusOrder Where OrderDate>'2002-12-23 23:30:00.000' and OrderDate<'2002-12-24 23:59:59.000' order by ordernum asc
1405561
Select OrderNum from CusOrder Where OrderDate>'2002-12-21 17:11:34.217' and OrderDate<'2002-12-21 17:21:34.217' order by ordernum asc
1393846
Select OrderNum from CusOrder Where OrderDate>'2002-12-23 09:03:30.907' and OrderDate<'2002-12-23 09:33:30.907' order by ordernum asc
1398252
Select top 1 * from OrderGood Where OrderDate>'2002-12-20 21:07:46.347' and OrderDate<'2002-12-20 23:59:59.000'
Select * from PointHistory Where RegDate>'2002-12-23 09:33:30.907' and RegDate<'2002-12-24 09:33:30.907'
and AccType=3
Order by RegDate Asc
sp_helptext childbestbook
/*
2002-12-20 21:07:46.347
마일리지 입력안됨
2002-12-21 09:52:14.613
---------------------
2002-12-21 17:11:34.217
마일리지 입력안됨
2002-12-23 09:33:30.907
*/
Select Sum(Point) from SQL1.shop.dbo.PointHistory Where UserID='NewLeader88'
Truncate table TempNURAC
Select * from TempNURAC Where BuHap=10716.0000
Select * from OrderGood Where OrderDate='2002-12-20 21:07:46.347'
Select * from OrderGood Where OrderDate='2002-12-21 09:52:14.613'
Select RTrim(Substring(AccDesc,8,10)) from SQL1.Shop.dbo.PointHistory Where AccType=3 and AccDesc like '주문번호 : %'
and RTrim(Substring(AccDesc,8,10))<>''
Select * from SQL1.shop.dbo.PointHistory Where Acctype=3 and AccDesc like '주문번호 : %' and RTrim(Substring(AccDesc,8,10)) <>''
--23일 이후주문자중 포인트 히스토리에 쌓인 놈들이다
--Truncate table TempPH
Insert TempPH
Select RTrim(Substring(AccDesc,8,10)) as OrderNum
from SQL1.shop.dbo.PointHistory Where AccType=3 and AccDesc like '주문번호 : %'
and RTrim(Substring(AccDesc,8,10))<>'' and RegDate>'2002-12-23 00:00:00.000' and RegDate<'2002-12-31 00:00:00.000'
--요까지
--23일자 주문 리스트를 가져 온다.
insert TempNURAC
Select og.OrderNum, MAX(co.UserID) as UserID ,SUM(og.Bonus) as BuHap from OrderGood as og join CusOrder as co
on co.OrderNum=og.OrderNum
Where og.OrderNum>=1397589 and og.orderNum<=1405561
and og.ShopCD>0 and PaymethodCD=1200
Group by og.OrderNum
--요까지
Select * from tempNuRac
--아웃터 조인을 이용해서 누락 되었다고 생각 되는 주문번호를 넣는다.
--Truncate Table tempDAP
insert TempDAP
Select rc.OrderNum, rc.UserID, rc.BuHap from TempNuRAC as rc Left outer join TempPH as ph
on rc.OrderNum=ph.OrderNum
where ph.OrderNum is null
--요까지
Select * from tempDAP
insert SQL1.Shop.dbo.PointHistory
Select UserID,Getdate(),3, BuHap, '주문번호 : '+Cast(OrderNum as varchar(12)), 'System' from TempDap
Update SQL1.Shop.dbo.MallUser Set Point=Point+tn.BuHap, Bonus=Bonus+tn.BuHap
From SQL1.Shop.dbo.MallUser as mu join TempDap as tn
on mu.UserID=tn.UserID
/*
Update HANJIN_EDI_DETAIL_TBL Set State = 'N'
From WMS_COMPLETE_INVOICE_TBL With (NoLock) Inner Join HANJIN_EDI_DETAIL_TBL
On WMS_COMPLETE_INVOICE_TBL.InvoiceNo = HANJIN_EDI_DETAIL_TBL.InvoiceNo
And WMS_COMPLETE_INVOICE_TBL.SeqNo > HANJIN_EDI_DETAIL_TBL.SeqNo_WMS
Where HANJIN_EDI_DETAIL_TBL.State = 'S' And WMS_COMPLETE_INVOICE_TBL.InterfaceType = 'D'
Select Sum(bonus) from OrderGood Where OrderNum=1391057
Select top 1 * from SQL1.Shop.Dbo.PointHistory Where AccDESC='주문번호 : 1391057'
Select * from SQL1.Shop.Dbo.MallUser Where UserID='glanzen'
*/
