【瑞星系统】会员积分明细【5】

    insert into BASVIPDTL
    (UID,PLUID,QTY,SSLPRC,ASLPRC,SLPRCTYP,MGRAMT,SLDAT,ADDVALUE)
    select UID=A.UID,
 PLUID=A.PLUID,
 QTY=A.QTY,
 SLPRC=A.SLPRC,
 ASLPRC=case when A.QTY<>0 then (A.AMT-A.DISAMT-isnull(A.disc,0))/A.QTY else 0 end,
        '1',  
 MGRAMT=(A.AMT-A.DISAMT-isnull(A.disc,0))-(case when A.PROP=2 then A.AMT*A.AVGCSPRC else A.QTY*A.AVGCSPRC end),
        SLDAT=A.SLDAT, ----WZX,20040220
--            ADDVALUE= case when C.VIPRAT>0 then floor(B.QTY*C.VIPRAT)
--                               when C.VIPRAT<0 then 0
--                               when C.VIPRAT=0 then floor(B.AMT-B.DISAMT)
--                          end
        ADDVALUE = convert(numeric(9,2), isnull(D.VIPMARK,0))
    from
 (select UID=B.UID, PLUID=B.PLUID, PROP=MAX(C.PROP), SLDAT=MAX(A.SLDAT),
  QTY=SUM(B.QTY),
  SLPRC=MAX(C.SLPRC),
  AVGCSPRC=MAX(C.AVGCSPRC),
  AMT=SUM(B.AMT),
  DISAMT=SUM(B.DISAMT),
  DISC=SUM(ISNULL(B.DISC,0))
  from
  (select UID=PUID,SLDAT,PMNO=VIPNO 
   from   BASVIPADUP 
   where PUID NOT IN (SELECT UID FROM BASVIPDTL) --AND PUID='14052709180528636'
  ) A, POSSRLDT B, BASPLUPRC C
  where A.UID=B.UID AND B.PLUID=C.PLUID and B.ISDEL=0 and B.ISBACK = 0
  GROUP BY B.UID,B.PLUID 
 ) A, ---------------WZX,20050205,积分重复问题
    (select b.UID,  vipmark=isnull(b.VIPMARK,0), a.PLUID
     from   BASPLUMAIN a join POSVIPMARK b
     on a.PLUNO = b.PLUNO
    )D
    where left(A.UID,6)+right(A.UID,7)=left(d.UID,6)+right(d.UID,7) and A.PLUID = D.PLUID
go
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER     trigger POSUpdateValidVIP on POSSRLBF for update as
begin
  declare @SECTION varchar(80), @CODE varchar(80)
  declare @TAG varbinary(8)
  declare @ERROR int,@ERRMSG varchar(255)

  select @SECTION ='财务处理', @CODE = '启用会员积分处理'
  if dbo.GetIntVar(@SECTION, @CODE, 0) = 0
    return

  if update(VALID) and exists (select 1 from
                               INSERTED A, DELETED B
                               where A.UID = B.UID  and A.VALID = 1 and B.VALID = 0 and (A.FLAG = 1 or A.FLAG = 5 )
                              )
  begin
    select @TAG= max(TSTAMP) from BASVIPADUP
    select @tag=0 where @tag is null

    insert into BASVIPADUP( VIPNO,  PUID, SLAMT, SLDAT, ADDVALUE,MGRAMT)   
 select VIPNO,UID,SLAMT,SLDAT,ADDVALUE=floor(ADDVALUE),MGRAMT
 from
     (
      select VIPNO=left(max(A.PMNO),9),
      UID=A.UID,
      SLAMT=sum(A.SLAMT),
      SLDAT=max(A.SLDAT),
                    ADDVALUE = sum(convert(numeric(9,2), D.VIPMARK)),
--wjk修改于20041021，ADDVALUE 直接使用POSVIPMARK表中的VIP积分
--             ADDVALUE= CASE WHEN sum(B.AMT-B.DISAMT-ISNULL(B.DISC,0))>=10
--      THEN sum(case when C.VIPRAT>0 then (B.QTY*C.VIPRAT)
--                                  when C.VIPRAT<0 then 0
--                                  when C.VIPRAT=0 then (B.AMT-B.DISAMT-ISNULL(B.DISC,0)) ----WZX,2004-09-09
--        END)
--         ELSE 0 end,
             MGRAMT=sum(A.MGRAMT)
--             ADDVALUE= sum(case when C.VIPRAT>0 then (B.QTY*C.VIPRAT)
--                                when C.VIPRAT<0 then 0
--                                when C.VIPRAT=0 then (B.AMT-B.DISAMT-ISNULL(B.DISC,0)) ----WZX,2004-09-09
--                           end
--                          ),
--             MGRAMT=sum(B.AMT-B.DISAMT)-sum(case when C.PROP=2 then B.AMT*C.AVGCSPRC else B.QTY*C.AVGCSPRC end)

      from
 
   (SELECT UID=B.UID, PLUID=B.PLUID, SLDAT=MAX(A.SLDAT), PMNO=MAX(A.PMNO),
    MGRAMT= sum(B.AMT-B.DISAMT-ISNULL(B.DISC,0))-sum(case when C.PROP=2 then B.AMT*C.AVGCSPRC else B.QTY*C.AVGCSPRC end),
    SLAMT = sum(B.AMT-B.DISAMT-ISNULL(B.DISC,0))
    FROM
    (select C.UID,A.SLDAT,C.PMNO 
    from POSSRLPM C,INSERTED A, DELETED B
    where A.UID = B.UID
      and A.VALID = 1
      and B.VALID = 0
      and (A.FLAG = 1  or A.FLAG = 5 )
      and C.UID=A.UID and C.PMID = 0 and isnull(C.PMNO,'')<>''
    ) A, POSSRLDT B, BASPLUPRC C
   WHERE A.UID=B.UID AND B.PLUID=C.PLUID and B.ISDEL=0 and B.ISBACK = 0
   GROUP BY B.PLUID,B.UID  ---------------WZX,20050205,积分重复问题
   ) A, POSVIPMARK D, BASPLUMAIN E
      where left(A.UID,6)+right(A.UID,7) = left(D.UID,6)+right(D.UID,7)and D.PLUNO = E.PLUNO and A.PLUID = E.PLUID
      group  by A.UID
     ) A
--    where ADDVALUE>0 ----WZX,无论积分值是否为0，都要传
  
    if @ERROR <> 0
    begin
      rollback tran
      select @ERRMSG = dbo.GetErrorMSG(@ERROR)
      raiserror('在审核会员积分时出错.原始错误号码是[%i],原始错误信息是[%s].', 16, 1, @ERROR, @ERRMSG) with seterror
      return
    end
 
    insert into BASVIPDTL
    (UID,PLUID,QTY,SSLPRC,ASLPRC,SLPRCTYP,MGRAMT,SLDAT,ADDVALUE)
    select UID=A.UID,
 PLUID=A.PLUID,
 QTY=A.QTY,
 SLPRC=A.SLPRC,
 ASLPRC=case when A.QTY<>0 then (A.AMT-A.DISAMT-isnull(A.disc,0))/A.QTY else 0 end,
        '1',  
 MGRAMT=(A.AMT-A.DISAMT-isnull(A.disc,0))-(case when A.PROP=2 then A.AMT*A.AVGCSPRC else A.QTY*A.AVGCSPRC end),
        SLDAT=A.SLDAT, ----WZX,20040220
--            ADDVALUE= case when C.VIPRAT>0 then floor(B.QTY*C.VIPRAT)
--                               when C.VIPRAT<0 then 0
--                               when C.VIPRAT=0 then floor(B.AMT-B.DISAMT)
--                          end
        ADDVALUE = convert(numeric(9,2), isnull(D.VIPMARK,0))
    from
 (select UID=B.UID, PLUID=B.PLUID, PROP=MAX(C.PROP), SLDAT=MAX(A.SLDAT),
  QTY=SUM(B.QTY),
  SLPRC=MAX(C.SLPRC),
  AVGCSPRC=MAX(C.AVGCSPRC),
  AMT=SUM(B.AMT),
  DISAMT=SUM(B.DISAMT),
  DISC=SUM(ISNULL(B.DISC,0))
  from
  (select UID=PUID,SLDAT,PMNO=VIPNO 
   from   BASVIPADUP 
   where TSTAMP>@TAG
  ) A, POSSRLDT B, BASPLUPRC C
  where A.UID=B.UID AND B.PLUID=C.PLUID and B.ISDEL=0 and B.ISBACK = 0
  GROUP BY B.UID,B.PLUID 
 ) A, ---------------WZX,20050205,积分重复问题
    (select b.UID,  vipmark=isnull(b.VIPMARK,0), a.PLUID
     from   BASPLUMAIN a join POSVIPMARK b
     on a.PLUNO = b.PLUNO
    )D
    where left(A.UID,6)+right(A.UID,7)=left(d.UID,6)+right(d.UID,7) and A.PLUID = D.PLUID

    if @ERROR <> 0
    begin
      rollback tran
      select @ERRMSG = dbo.GetErrorMSG(@ERROR)
      raiserror('在审核会员积分时出错.原始错误号码是[%i],原始错误信息是[%s].', 16, 1, @ERROR, @ERRMSG) with seterror
      return
    end

  end
end

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

转载于:https://www.cnblogs.com/nlg3413/p/3897123.html