DROP PROCEDURE IF EXISTS get_user_charge_level;
CREATE DEFINER = newycsd @% PROCEDURE get_user_charge_level (
openid varchar(50)
)
BEGIN
set @per_user_amount = 0;
SET @csql = CONCAT( ‘select
ifnull((select sum(price) from orders o
inner join users u2 ON o.uid=u2.id where u2.openid=u.openid and o.status=”PAID”),0)/count(distinct u.id) as per_user_amount
into @per_user_amount
from users u
where u.openid=”‘, openid, ‘”
group by u.openid
having min(u.created_at) <= date_sub(now(), interval 72 HOUR)
‘ );
— SELECT @csql;
— select @ret_data;
PREPARE create_stmt FROM @csql;
EXECUTE create_stmt;
deallocate prepare create_stmt;
IF @per_user_amount > 30
THEN
set @user_charge_level = ‘HIGH’;
ELSEIF @per_user_amount >=10
THEN
set @user_charge_level = ‘MID’;
ELSEIF @per_user_amount > 0
THEN
set @user_charge_level = ‘LOW’;
ELSE
set @user_charge_level = ‘NONE’;
END IF;
select @per_user_amount;
select @user_charge_level;
END;
call get_user_charge_level(‘oAcqg1LRHNKN2jaEkJ5v56HOwPEQ’);
call get_user_charge_level(‘2222’);
