Rising system: Modify Member points

Set quoted_identifier on
Go
Set ansi_nulls on
Go

Alter procedure getvipmark (@ sldat datetime, @ PNO integer, @ SnO integer, @ vipno varchar (20), @ pluinfo text, @ curvipmark varchar (50) output, @ totalvipmark varchar (150) output, @ return bit output)
As
Begin
Declare @ uid bigint
Declare @ chartable table (pluno varchar (50), qty varchar (50), Mark numeric (12,4 ))
Declare @ TMP varchar (50)
Declare @ B SQL _variant, @ C SQL _variant
Declare @ Len int, -- String Length
@ B _pos int, -- start position
@ E_pos int, -- end position
@ C_pos int, -- Current Position
@ F_pos int,
@ G_pos int,
@ Summark numeric (), ------- wzx20040708, total amount of a transaction
@ Tmp_len int, @ I int
Declare @ PLU varchar (50), @ mark varchar (50), @ qty varchar (50)
Select @ return = 0
Select @ curvipmark = '0' ----- wzx2004-08-31
Select @ totalvipmark = '0' ----- wzx2004-08-31

-- Write the incoming text information to the temporary table, and use ';' to separate the records. The product code, quantity, and sales amount are separated.
Select @ Len = datalength (@ pluinfo), @ B _pos = 1, @ e_pos = 0
While @ B _pos <= @ Len
Begin
Set @ g_pos = 0
Set @ c_pos = patindex ('%; %', substring (@ pluinfo, @ B _pos, @ Len ))
Set @ e_pos = case @ c_pos when 0 then @ Len + 1 else @ e_pos + @ c_pos end
Set @ tmp_len = Len (substring (@ pluinfo, @ B _pos, @ e_pos-@ B _pos ))
Set @ f_pos = patindex ('%, %', substring (@ pluinfo, @ B _pos, @ e_pos-@ B _pos), 1, @ tmp_len ))
Set @ g_pos = case @ f_pos when 0 then @ tmp_len + 1 else @ g_pos + @ f_pos end
Select @ PLU = substring (@ pluinfo, @ B _pos, @ e_pos-@ B _pos), 1, @ g_pos-1)
Select @ mark = substring (@ pluinfo, @ B _pos, @ e_pos-@ B _pos), @ g_pos + 1, @ tmp_len)
Select @ TMP = convert (varchar (50), @ mark)
Select @ I = patindex ('%, %', @ TMP)
Select @ qty = substring (@ TMP, 1, @ I-1)
Select @ mark = substring (@ TMP, @ I + 1, @ tmp_len)
If (isnull (@ mark, '') ='') Select @ mark = '0'
If (isnull (@ qty, '') ='') Select @ qty = '0'
Insert into @ chartable (pluno, qty, mark)
Values (@ plu, convert (varchar (50), (convert (Numeric (100), @ qty)/), convert (varchar (50 ), (convert (Numeric (100), @ mark )))
Set @ B _pos = @ e_pos + 1
End

------ Points will not be returned if wjk or distinct does not exist.
If not exists (select 1 from @ chartable a join basplumain B on A. pluno = B. pluno)
Return

-- Adds a judgment on the sales amount. If the sales amount is less than 10 yuan, no processing is performed.
Select @ summark = sum (Mark) from @ chartable --------- wzx20040708
-- Update the credit information in the current consumption table, wjk, 20050204 (count the commodity recalculation quantity)
Update a set mark = case when C. viprat <0 then 0
When C. viprat = 0 and convert (Numeric (12, 2), @ summark)> = 1 then A. Mark ----- wzx20040708
When C. viprat> 0 and convert (Numeric (12, 2), @ summark)> = 1 then case when C. prop = 1 then case when C. slprc = 0 then C. viprat *. mark
Else C. viprat * convert (Numeric (12, 3), (A. Mark/C. slprc ))
End
Else C. viprat * A. Qty
End --------- wzx20040708
End
From @ chartable a join basplumain B on A. pluno = B. pluno join baspluprc C on B. pluid = C. pluid

Select @ curvipmark = floor (convert (varchar (50), convert (Numeric (16,2), isnull (sum (Mark), 0) -- round down
From @ chartable

Select @ totalvipmark = convert (varchar (50), convert (Numeric (30,2), isnull (sum (addvalue), 0 )))
From basvipadup
Where ltrim (rtrim (vipno) = ltrim (rtrim (@ vipno ))
Group by vipno

If (ltrim (rtrim (isnull (@ totalvipmark, '') = '')
Select @ totalvipmark = 0

Select @ totalvipmark = floor (convert (Numeric (30,2), @ totalvipmark ))
-- Select @ totalvipmark = convert (Numeric (16,2), @ curvipmark) + convert (Numeric (30,2), @ totalvipmark)
-- Wjk is modified to 20041020, And the foreground VIP data is directly written into the temporary table. When the account is closed at night, the POs stream is associated for VIP points processing.
Begin tran
Select @ uid = DBO. getuid (@ sldat, @ PNO, @ SnO)
If @ error> 0
Begin
Select @ curvipmark = 0
Select @ totalvipmark = 0
Rollback tran
Return
End

If not exists (select 1 from posvipmark (nolock) Where uid = @ UID)
Begin
Insert into posvipmark (UID, pluno, qty, vipmark)
Select uid = @ uid, pluno, qty, isnull (mark, 0)
From @ chartable
If @ error> 0
Begin
Select @ curvipmark = 0
Select @ totalvipmark = 0
Rollback tran
Return
End
End
Else
Begin
Select @ curvipmark = 0
Select @ totalvipmark = 0
Rollback tran
Return
End

Commit tran

Select @ return = 1
Select 1
End

Go
Set quoted_identifier off
Go
Set ansi_nulls on
Go