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 tset t.task_status=1where 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, pkgIdorder 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 thresholdfrom ',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 tset t.task_status=2where t.mvno_code=mvn_code;UNTIL no_more_products  END REPEAT;  CLOSE cur_product;END //DELIMITER