BEGIN
DECLARE finished INTEGER DEFAULT '0';
DECLARE clicks_var int default '0';
declare payout float default 0;
declare date_var date;
declare user_id_var int(11);
declare tool_id_var int(11);
declare leads_var int(11);
declare offer_id_var int(11);
declare type_var varchar(25);
declare country_var varchar(50);
declare os_var varchar(50);
declare delete_count_traffic int default 0;
declare clicks_stats CURSOR FOR SELECT SUM(`raw`) as `clicks` , `user_id` , COALESCE(SUM(`leads`),0) as `leads` , COALESCE(SUM(`payout`),0) as `payout` , DATE_FORMAT(date,'%Y-%m-%d') as `date` , `tool_id`, `type` FROM `count_traffic` GROUP BY `user_id` , `tool_id` , `date`;
declare country_stats CURSOR FOR SELECT SUM(`raw`) as `clicks` , `user_id` , COALESCE(SUM(`leads`),0) as `leads` , COALESCE(SUM(`payout`),0) as `payout` , DATE_FORMAT(date,'%Y-%m-%d') as `date` , `tool_id`, `type`, `country` FROM `count_traffic` GROUP BY `user_id` , `tool_id` , `date`, `country`;
declare offers_stats CURSOR FOR SELECT SUM(`raw`) as `clicks` , `user_id` , COALESCE(SUM(`leads`),0) as `leads` , COALESCE(SUM(`payout`),0) as `payout` , DATE_FORMAT(date,'%Y-%m-%d') as `date` , `tool_id`, `type`, `offer_id` FROM `count_traffic` GROUP BY `user_id` , `tool_id` , `date`, `offer_id`;
declare os_stats CURSOR FOR SELECT SUM(`raw`) as `clicks` , `user_id` , COALESCE(SUM(`leads`),0) as `leads` , COALESCE(SUM(`payout`),0) as `payout` , DATE_FORMAT(date,'%Y-%m-%d') as `date` , `tool_id`, `type`, `os` FROM `count_traffic` GROUP BY `user_id` , `tool_id` , `date`, `os`;
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET finished = 1;
open clicks_stats;
calculate_clicks_stats:LOOP
FETCH clicks_stats into clicks_var,user_id_var,leads_var,payout,date_var,tool_id_var,type_var;
IF finished = 1 THEN
set finished=0;
LEAVE calculate_clicks_stats;
END IF;
INSERT INTO `daily_clicks` (`user_id`, `tool_id`,`payout`,`leads`,`clicks`,`date`,`type`) VALUES (user_id_var,tool_id_var,payout,leads_var,clicks_var,date_var,type_var);
END LOOP calculate_clicks_stats;
close clicks_stats;
open country_stats;
calculate_country_stats:LOOP
FETCH country_stats into clicks_var,user_id_var,leads_var,payout,date_var,tool_id_var,type_var,country_var;
IF finished = 1 THEN
set finished=0;
LEAVE calculate_country_stats;
END IF;
INSERT INTO `daily_country` (`user_id`, `tool_id`,`payout`,`leads`,`clicks`,`date`,`type`,`country`) VALUES (user_id_var,tool_id_var,payout,leads_var,clicks_var,date_var,type_var,country_var);
END LOOP calculate_country_stats;
close country_stats;
open offers_stats;
calculate_offers_stats:LOOP
FETCH offers_stats into clicks_var,user_id_var,leads_var,payout,date_var,tool_id_var,type_var,offer_id_var;
IF finished = 1 THEN
set finished=0;
LEAVE calculate_offers_stats;
END IF;
INSERT INTO `daily_offers` (`user_id`, `tool_id`,`payout`,`leads`,`clicks`,`date`,`type`,`offer_id`) VALUES (user_id_var,tool_id_var,payout,leads_var,clicks_var,date_var,type_var,offer_id_var);
END LOOP calculate_offers_stats;
close offers_stats;
open os_stats;
calculate_os_stats:LOOP
FETCH os_stats into clicks_var,user_id_var,leads_var,payout,date_var,tool_id_var,type_var,os_var;
IF finished = 1 THEN
set finished=0;
LEAVE calculate_os_stats;
END IF;
INSERT INTO `daily_os` (`user_id`, `tool_id`,`payout`,`leads`,`clicks`,`date`,`type`,`os`) VALUES (user_id_var,tool_id_var,payout,leads_var,clicks_var,date_var,type_var,os_var);
END LOOP calculate_os_stats;
close os_stats;
DELETE FROM `count_traffic`;
END
Facebook
Twitter
