DELIMITER $$

DROP PROCEDURE IF EXISTS `travian_cc_s2_2`.`p_test`$$

CREATE DEFINER=`postrock`@`58.246.137.218` PROCEDURE `p_test`()
begin
declare l_db_name varchar(300);
declare l_file_name varchar(300);
declare l_cur cursor for select schema_name from information_schema.schemata where schema_name like 'travian_%' and substring(schema_name,-1,2)=2;
open l_cur;
fetch l_cur into l_db_name;
while (l_db_name is not null) do
set @a=concat(l_db_name,'user_reg_inf','.txt');
set @sql=concat('Select *,''0'' as PASSPORT_CODE,''Travian'' as GAME,''CN1'' as SERVER_NAME from (SELECT a.uid as uid,a.name as user_id,FROM_UNIXTIME( b.signup,''%Y-%m-%d %H:%i:%S'') as user_reg_time
FROM ',l_db_name,'.s_details2 a LEFT JOIN  ',l_db_name,'.s_details1 b
ON a.uid=b.uid) a left join
(SELECT uid,ip as reg_ip,min(zeit) FROM  ',l_db_name,'.x_login
WHERE zeit between  (unix_timestamp(CURDATE())-86400) AND (unix_timestamp(CURDATE()))
GROUP BY uid)
 b on a.uid=b.uid  into outfile','"','/home/chenyl/',@a,'"');
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;
fetch l_cur into l_db_name;
end while;
close l_cur;
end$$

DELIMITER ;