DROP PROCEDURE IF EXISTS pro_tpss_daily_packg_hu;
DELIMITER //
CREATE PROCEDURE pro_tpss_daily_packg_hu(IN p_in VARCHAR(200))
BEGIN
DECLARE no_more_products, quantity_in_stock INT DEFAULT 0;
declare v_sql varchar(5000);
DECLARE group_sql VARCHAR(5000);
DECLARE mvn_code VARCHAR(50);
DECLARE detail_temp_table VARCHAR(200);
DECLARE detail_total_table VARCHAR(200);
DECLARE cur_product CURSOR FOR SELECT DISTINCT mvno_code FROM t_tpss_daily_dim_hu t where t.task_status=0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_products = 1;
OPEN cur_product;
REPEAT
FETCH cur_product INTO mvn_code;
-- 创建表名称
set detail_total_table = concat('daily_total_',mvn_code ,'_',p_in);
set detail_temp_table = concat('daily_temp_',mvn_code);
update t_tpss_daily_dim_hu t
set t.task_status=1
where t.mvno_code=mvn_code;
set v_sql=concat('DROP TABLE IF EXISTS ',detail_temp_table );
set @v_sql=v_sql;
prepare stmt from @v_sql;
EXECUTE stmt;
deallocate prepare stmt;
-- 聚合sql
set group_sql = concat('select userName, mvnoCode, orgCode, month, day, pkgId, mcc, visitCountry, imei, SUM(flowSize) as flowSize
from m_flow_',mvn_code ,'_',p_in,'
group by userName, visitCountry, orgCode, day, pkgId
order by orgCode,day,visitCountry,flowSize desc');
set v_sql=concat('CREATE table ',detail_temp_table,' as ', group_sql);
set @v_sql=v_sql;
prepare stmt from @v_sql;
EXECUTE stmt;
deallocate prepare stmt;
set v_sql=concat('DROP TABLE IF EXISTS ',detail_total_table );
set @v_sql=v_sql;
prepare stmt from @v_sql;
EXECUTE stmt;
deallocate prepare stmt;
-- 关联表数据
set group_sql = concat('select d.userName, d.mvnoCode, d.orgCode, d.month, d.day, d.pkgId, d.mcc, d.visitCountry, d.imei, d.flowSize/1024 as flowSize, t.fee as money, t.threshold as threshold
from ',detail_temp_table,
' d left join t_tpss_daily_dim_hu t
on d.mvnoCode=t.mvno_code and (t.org_code like CONCAT("%",d.orgCode,"%") or (t.org_code=-1 and t.ex_org_code not like CONCAT("%",d.orgCode,"%")))
and (t.country like CONCAT("%",d.visitCountry,"%") or (t.country=-1 and t.ex_country not like CONCAT("%",d.visitCountry,"%")))');
-- 创建统计表
set v_sql=concat('CREATE table ',detail_total_table,' as ', group_sql);
set @v_sql=v_sql;
prepare stmt from @v_sql;
EXECUTE stmt;
deallocate prepare stmt;
-- 包天没有超过阀值的用户不收钱
set v_sql=concat('update ',detail_total_table,' t set t.money=0 where t.flowSize<t.threshold');
set @v_sql=v_sql;
prepare stmt from @v_sql;
EXECUTE stmt;
deallocate prepare stmt;
update t_tpss_daily_dim_hu t
set t.task_status=2
where t.mvno_code=mvn_code;
UNTIL no_more_products END REPEAT;
CLOSE cur_product;
END //
DELIMITER
DELIMITER //
CREATE PROCEDURE pro_tpss_daily_packg_hu(IN p_in VARCHAR(200))
BEGIN
DECLARE no_more_products, quantity_in_stock INT DEFAULT 0;
declare v_sql varchar(5000);
DECLARE group_sql VARCHAR(5000);
DECLARE mvn_code VARCHAR(50);
DECLARE detail_temp_table VARCHAR(200);
DECLARE detail_total_table VARCHAR(200);
DECLARE cur_product CURSOR FOR SELECT DISTINCT mvno_code FROM t_tpss_daily_dim_hu t where t.task_status=0;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_products = 1;
OPEN cur_product;
REPEAT
FETCH cur_product INTO mvn_code;
-- 创建表名称
set detail_total_table = concat('daily_total_',mvn_code ,'_',p_in);
set detail_temp_table = concat('daily_temp_',mvn_code);
update t_tpss_daily_dim_hu t
set t.task_status=1
where t.mvno_code=mvn_code;
set v_sql=concat('DROP TABLE IF EXISTS ',detail_temp_table );
set @v_sql=v_sql;
prepare stmt from @v_sql;
EXECUTE stmt;
deallocate prepare stmt;
-- 聚合sql
set group_sql = concat('select userName, mvnoCode, orgCode, month, day, pkgId, mcc, visitCountry, imei, SUM(flowSize) as flowSize
from m_flow_',mvn_code ,'_',p_in,'
group by userName, visitCountry, orgCode, day, pkgId
order by orgCode,day,visitCountry,flowSize desc');
set v_sql=concat('CREATE table ',detail_temp_table,' as ', group_sql);
set @v_sql=v_sql;
prepare stmt from @v_sql;
EXECUTE stmt;
deallocate prepare stmt;
set v_sql=concat('DROP TABLE IF EXISTS ',detail_total_table );
set @v_sql=v_sql;
prepare stmt from @v_sql;
EXECUTE stmt;
deallocate prepare stmt;
-- 关联表数据
set group_sql = concat('select d.userName, d.mvnoCode, d.orgCode, d.month, d.day, d.pkgId, d.mcc, d.visitCountry, d.imei, d.flowSize/1024 as flowSize, t.fee as money, t.threshold as threshold
from ',detail_temp_table,
' d left join t_tpss_daily_dim_hu t
on d.mvnoCode=t.mvno_code and (t.org_code like CONCAT("%",d.orgCode,"%") or (t.org_code=-1 and t.ex_org_code not like CONCAT("%",d.orgCode,"%")))
and (t.country like CONCAT("%",d.visitCountry,"%") or (t.country=-1 and t.ex_country not like CONCAT("%",d.visitCountry,"%")))');
-- 创建统计表
set v_sql=concat('CREATE table ',detail_total_table,' as ', group_sql);
set @v_sql=v_sql;
prepare stmt from @v_sql;
EXECUTE stmt;
deallocate prepare stmt;
-- 包天没有超过阀值的用户不收钱
set v_sql=concat('update ',detail_total_table,' t set t.money=0 where t.flowSize<t.threshold');
set @v_sql=v_sql;
prepare stmt from @v_sql;
EXECUTE stmt;
deallocate prepare stmt;
update t_tpss_daily_dim_hu t
set t.task_status=2
where t.mvno_code=mvn_code;
UNTIL no_more_products END REPEAT;
CLOSE cur_product;
END //
DELIMITER