p
delimiter $$
create procedure p_vipshop_to_actual_day_shop_sale
(
in p_shop_id int,
in p_sale_date date,
out p_update_insert varchar(50)
)
#毛利率
begin
SELECT
@margin:=sum(profit) / sum(sale_amt)
FROM
(
SELECT DISTINCT
tmp1.product_id,
tmp1.sale_amt,
tmp1.sale_num * t2.cost_price AS total_cost,
t2.cost_price AS cost,
tmp1.sale_amt - tmp1.sale_num * t2.cost_price AS profit
FROM
(
SELECT
t1.product_id,
al_sale_amount) AS sale_amt,
al_sale_number) AS sale_num
FROM
order_vipshop_daily_detail_info t1
WHERE
(
shop_id = p_shop_id
AND sale_date = p_sale_date
)
GROUP BY
t1.product_id
) tmp1
JOIN l3_product_inherent_property_code t2 ON tmp1.product_id = t2.product_id
) tmp;
#销售额，销量，件单价
SELECT
@amt:= sum(total_sale_amount),
@num:= sum(total_sale_number) ,
@price:= sum(total_sale_amount) / sum(total_sale_number)
FROM
order_vipshop_daily_detail_info
WHERE
(
shop_id = p_shop_id
AND sale_date = p_sale_date
);
# 写入数据更新字段 rate_of_margin,unit_price,place_num,place_amount
if not exists(
select 1 from actual_day_shop_sale
where shop_id=p_shop_id and the_date=p_sale_date
)
then
insert actual_day_shop_sale(the_date,shop_brand_id,`year`,`month`,`day`,shop_id,shop_name,
brand_id,actual_sale,description,rate_of_margin,unit_price,place_num,place_amount)
values(
p_sale_date
,(SELECT shop_brand_id from base_shop_brand where shop_id=p_shop_id)
,year(p_sale_date)
,month(p_sale_date)
,day(p_sale_date)
,p_shop_id
,(select shop_name from base_shop where shop_id=p_shop_id)
,(SELECT brand_id from base_shop_brand where shop_id=p_shop_id)
,@amt
,'ETL Input'
,@margin
,@price
,@num
,@amt
);
set p_update_insert='insert';
else
update actual_day_shop_sale
set rate_of_margin=@margin,place_amount=@amt,place_num=@num,unit_price=@price
where shop_id=p_shop_id and the_date=p_sale_date;
set p_update_insert='update';
end if ;
end ;
$$
