ALTER PROCEDURE [dbo].[OMS_DISCOUNT_SHARING_PRO]
@lypt AS VARCHAR(10)
AS
DECLARE @orderStatus VARCHAR(10);
DECLARE @orderlypt VARCHAR(10);
DECLARE @orderEndStatus VARCHAR(10);
DECLARE @orderHeadId VARCHAR(10);
DECLARE @orderHeadSpje DECIMAL(18,2);
DECLARE @orderLineJe DECIMAL(18,2);
DECLARE @orderLineId VARCHAR(10);
DECLARE @orderLineSl INT;
DECLARE @mxhlx VARCHAR(10);
DECLARE @yf DECIMAL(18,2);
BEGIN
--获取订单初始状态
SELECT @orderStatus =
(SELECT
m.id
FROM
tb_shujuzd l,
tb_shujuzd m
WHERE
l.id = m.parentid
AND m.code = 'NEW'
AND
l.code = 'OM_ORDER_STATUS'
AND l.parentid = '1')
--订单明细行类型id
SELECT @mxhlx =
(SELECT
m.id
FROM
tb_shujuzd l,
tb_shujuzd m
WHERE
l.id = m.parentid
AND m.code = 'FREIGHT'
AND
l.code = 'ORDER_LINE_DETAIL_TYPE'
AND l.parentid = '1')
--获取订单结束状态
SELECT @orderEndStatus =
(SELECT
m.id
FROM
tb_shujuzd l,
tb_shujuzd m
WHERE
l.id = m.parentid
AND m.code = 'DISTRIBUTED'
AND
l.code = 'OM_ORDER_STATUS'
AND l.parentid = '1')
--获取来源平台
SELECT @orderlypt =
(
SELECT
l.id
FROM
tb_shujuzdlx
h,
tb_shujuzd l
WHERE
h.code = 'EB_PLATFORM'
AND h.id = l.lx
AND l.parentid
= 0
and l.code=@lypt)
--如果来源店铺为天猫
IF @lypt = 'TM'
Begin
--获取天猫订单头的运费数据
DECLARE orderHead CURSOR LOCAL SCROLL FOR
select t.yf,t.id,t.spje from tb_ddgl_xiaoshouddt t where t.lypt=@orderlypt and t.ddzt = @orderStatus
OPEN orderHead
FETCH NEXT FROM orderHead INTO @yf,@orderHeadId,@orderHeadSpje
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @yf
if @yf = 0
BEGIN
PRINT 'ling'
update tb_ddgl_xiaoshouddt set ddzt=@orderEndStatus where id=@orderHeadId
update tb_ddgl_xiaoshouddh set yfje=0, dwyf=0 where xsddid=@orderHeadId
END
else
BEGIN
PRINT 'feiling'
DECLARE orderLine CURSOR LOCAL SCROLL FOR select je,id,sl from tb_ddgl_xiaoshouddh where xsddid=@orderHeadId
OPEN orderLine
FETCH NEXT FROM orderLine INTO @orderLineJe,@orderLineId,@orderLineSl
WHILE @@FETCH_STATUS = 0
BEGIN
update tb_ddgl_xiaoshouddh set yfje=@yf*@orderLineJe/@orderHeadSpje, dwyf=@yf*@orderLineJe/(@orderHeadSpje*@orderLineSl) where id = @orderLineId
insert INTO tb_ddgl_xiaoshouddmx(ddhid,ddid,mxhlx,mxx,je,cjsj,zt)VALUES(@orderHeadId,@orderLineId,@mxhlx,'运费',@yf*@orderLineJe/@orderHeadSpje,GETDATE(),'Y')
FETCH NEXT FROM orderLine INTO @orderLineJe,@orderLineId,@orderLineSl
END
CLOSE orderLine
DEALLOCATE orderLine
update tb_ddgl_xiaoshouddt set ddzt=@orderEndStatus where id=@orderHeadId
END
FETCH NEXT FROM orderHead INTO @yf,@orderHeadId,@orderHeadSpje
END
CLOSE orderHead
DEALLOCATE orderHead
End