DROP procedure IF EXISTS proc_mis;
DELIMITER ;;
/*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `proc_mis`(IN `sysdiff` INT(5))
BEGIN
	declare misdate datetime;
    DECLARE var_interval bigint(20); 
	DECLARE var_date date; 
    DECLARE var_date_time datetime;               
	DECLARE var_operator varchar(10);    
	DECLARE var_service_name varchar(30);    
	DECLARE var_serviceId bigint(20);
	DECLARE var_amount float(20);
	DECLARE var_shortcode bigint(20);
	DECLARE var_total_req bigint(20);              
	DECLARE var_uu_req bigint(20);                 
	DECLARE var_total_sub_hit bigint(20);          
	DECLARE var_uu_sub_hit bigint(20);             
	DECLARE var_succ_sub_hit bigint(20);           
	DECLARE var_total_cg_succ bigint(20);          
	DECLARE var_total_billing_hit bigint(20);      
	DECLARE var_uu_billing_hit bigint(20);         
	DECLARE var_success_billing_hit bigint(20);    
	DECLARE var_fail_billing_hit bigint(20);       
	DECLARE var_total_sub_charged bigint(20);      
	DECLARE var_uu_sub_charged bigint(20);         
	DECLARE var_sub_revenue float(20);            
	DECLARE var_total_renewal_hit bigint(20);      
	DECLARE var_uu_renewal_hit bigint(20);         
	DECLARE var_total_renewal_charged bigint(20);  
	DECLARE var_uu_renewal_charged bigint(20);     
	DECLARE var_renewal_revenue float(20); 
	DECLARE var_total_sub_base bigint(20); 
	DECLARE var_total_active_base bigint(20); 
	DECLARE var_total_unsub bigint(20);
	DECLARE v_done int default 0;
	DECLARE cur CURSOR FOR SELECT operator,service_id,service_name,amount,sender FROM tbl_service_info;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;
		set var_interval=0;
		set sysdiff=(sysdiff*1440)+var_interval;
		set var_date_time=DATE_SUB(now(), INTERVAL sysdiff minute);
		set var_date=date(var_date_time);
		delete from tbl_mis where date(date_time)=date(var_date);
		OPEN cur;
		FETCH cur into var_operator,var_serviceId,var_service_name,var_amount,var_shortcode;
		REPEAT
			select var_service_name;
			select count(1),count(1) into var_total_req,var_uu_req from tbl_agency_hits where service_name=var_service_name and date(sys_date_time)=date(var_date);
			select count(cTranRef),count(distinct(cTranRef)) into var_total_sub_hit,var_uu_sub_hit from tbl_Mos where cAction='REGISTER' and date(dtCreatedOn)=date(var_date) and cOperator = var_operator;
			select count(cTranRef) into var_succ_sub_hit from tbl_Mos where cAction='REGISTER' and cStatusCode='200' and date(dtCreatedOn)=date(var_date) and cOperator = var_operator;

			set var_total_cg_succ=var_succ_sub_hit; 
			set var_total_billing_hit=var_total_sub_hit;
			set var_uu_billing_hit=var_uu_sub_hit;   
			set var_success_billing_hit=var_total_sub_hit;
			set var_fail_billing_hit=0;
			set var_total_sub_charged=0;
			set var_uu_sub_charged=0;
			set var_sub_revenue=0;

			select count(cMSISDN),count(distinct(cMSISDN)) into var_total_renewal_charged,var_uu_renewal_charged from tbl_Dlrs where cStatusCode='200' and date(dtCreatedOn)=date(var_date) and cOperator = var_operator;
			select count(cMSISDN),count(distinct(cMSISDN)) into var_total_renewal_hit,var_uu_renewal_hit from tbl_Dlrs where date(dtCreatedOn)=date(var_date) and cOperator = var_operator;
			set var_renewal_revenue=var_total_renewal_charged*var_amount;

			select count(msisdn) into var_total_sub_base from tbl_sub where sid=var_serviceId and date(date_time)<=date(var_date) and operator = var_operator;
			select count(msisdn) into var_total_active_base from tbl_sub where sid=var_serviceId and sub_flag=1 and date(date_time)<=date(var_date) and operator = var_operator;
			select count(msisdn) into var_total_unsub from tbl_sub_log where sid=var_serviceId and date(unsub_date)=date(var_date) and operator = var_operator;

			insert into tbl_mis(date_time,service_name,total_req,uu_req,total_sub_hit,uu_sub_hit,succ_sub_hit,total_cg_succ,total_billing_hit,uu_billing_hit,success_billing_hit,fail_billing_hit,total_sub_charged,uu_sub_charged,sub_revenue,total_renewal_hit,uu_renewal_hit,total_renewal_charged,uu_renewal_charged,renewal_revenue,total_sub_base,total_active_base,total_unsub) values(var_date,var_service_name,var_total_req,var_uu_req,var_total_sub_hit,var_uu_sub_hit,var_succ_sub_hit,var_total_cg_succ,var_total_billing_hit,var_uu_billing_hit,var_success_billing_hit,var_fail_billing_hit,var_total_sub_charged,var_uu_sub_charged,var_sub_revenue,var_total_renewal_hit,var_uu_renewal_hit,var_total_renewal_charged,var_uu_renewal_charged,var_renewal_revenue,var_total_sub_base,var_total_active_base,var_total_unsub);

			FETCH cur into var_operator,var_serviceId,var_service_name,var_amount,var_shortcode;
		UNTIL v_done=1 END REPEAT;
		set v_done=0;
		CLOSE cur;
		select 'SUCCESS';
end */;;
DELIMITER ;
