BEGIN
DECLARE Done INT DEFAULT 0;
DECLARE t_tjid INT DEFAULT 0;
DECLARE t_type INT DEFAULT 0;
DECLARE scount INT DEFAULT 0;
declare i int DEFAULT 0;
DECLARE app_except_ids VARCHAR(50) DEFAULT '';
DECLARE zt_except_ids VARCHAR(50) DEFAULT '';
#DECLARE t_sql_1 VARCHAR(350) DEFAULT '';
DECLARE cur_l CURSOR FOR select tjid,type from jptj;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done = 1;
delete from tempRecommand;
OPEN cur_l;
REPEAT
FETCH cur_l INTO t_tjid,t_type;
IF Done = 0 THEN
IF t_type=1 THEN
IF EXISTS(select * from soft s,article a,down d where s.aid=t_tjid and s.aid=a.id and d.aid=s.aid and (s.freesrc!='' or RIGHT(TRIM(s.iossrc),4)='.ipa')) THEN
INSERT INTO tempRecommand(rid,typeid,icon,`name`,starlev,iossrc,freesrc,iosver,hotcount) select a.id,a.typeid,s.titpic,a.shorttitle,s.pingfen,s.iossrc,s.freesrc,s.iosver,d.downloads from soft s,article a,down d where d.aid=s.aid and s.aid=t_tjid and s.aid=a.id and (s.freesrc!='' or RIGHT(TRIM(s.iossrc),4)='.ipa');
end if;
set app_except_ids = CONCAT(app_except_ids,',',t_tjid);
ELSE
IF EXISTS(select id,icon,`name`,starlev,subtit,browsetimes from zhuanti where id=t_tjid) THEN
insert into tempRecommand(rid,icon,`name`,starlev,subtit,hotcount) select id,icon,`name`,starlev,subtit,browsetimes from zhuanti where id=t_tjid;
end if;
set zt_except_ids = CONCAT(zt_except_ids,',',t_tjid);
END IF;
END IF;
UNTIL Done = 1 END REPEAT;
CLOSE cur_l;
set app_except_ids = CONCAT(0,app_except_ids);
set zt_except_ids = CONCAT(0,zt_except_ids);
set @t_count = CONCAT("select count(*) into @scount from soft s,article a where s.aid=a.id and a.id not in (",app_except_ids,") and (s.freesrc!='' or RIGHT(TRIM(s.iossrc),4)='.ipa');");
PREPARE data1 FROM @t_count;
EXECUTE data1;
#select count(*) INTO scount from soft s,article a where s.aid=a.id and a.id not in (app_except_ids) and (s.freesrc!='' or RIGHT(TRIM(s.iossrc),4)='.ipa');
loop1: WHILE i<@scount DO
set @t_sql_1 = CONCAT("INSERT INTO tempRecommand(rid,typeid,icon,`name`,starlev,iossrc,freesrc,iosver,hotcount) select a.id,a.typeid,s.titpic,a.shorttitle,s.pingfen,s.iossrc,s.freesrc,s.iosver,d.downloads from soft s,article a,down d where d.aid=s.aid and s.aid=a.id and (s.freesrc!='' or RIGHT(TRIM(s.iossrc),4)='.ipa') and a.id not in (",app_except_ids,") limit ",i,",",1);
PREPARE data1 FROM @t_sql_1;
EXECUTE data1;
set @t_sql_2 = CONCAT("insert into tempRecommand(rid,icon,`name`,starlev,subtit,hotcount) select id,icon,`name`,starlev,subtit,browsetimes from zhuanti where id not in (",zt_except_ids,") limit ",i,",",1);
PREPARE data2 FROM @t_sql_2;
EXECUTE data2;
set i=i+1;
END WHILE loop1;
#select i;
#select t_sql_1;
#INSERT INTO tempRecommand(rid,typeid,icon,`name`,starlev,iossrc,freesrc,iosver) select a.id,a.typeid,s.titpic,a.shorttitle,s.pingfen,s.iossrc,s.freesrc,s.iosver from soft s,article a where s.aid=10 and s.aid=a.id and (s.freesrc!='' or RIGHT(TRIM(s.iossrc),4)='.ipa');
END
DECLARE Done INT DEFAULT 0;
DECLARE t_tjid INT DEFAULT 0;
DECLARE t_type INT DEFAULT 0;
DECLARE scount INT DEFAULT 0;
declare i int DEFAULT 0;
DECLARE app_except_ids VARCHAR(50) DEFAULT '';
DECLARE zt_except_ids VARCHAR(50) DEFAULT '';
#DECLARE t_sql_1 VARCHAR(350) DEFAULT '';
DECLARE cur_l CURSOR FOR select tjid,type from jptj;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET Done = 1;
delete from tempRecommand;
OPEN cur_l;
REPEAT
FETCH cur_l INTO t_tjid,t_type;
IF Done = 0 THEN
IF t_type=1 THEN
IF EXISTS(select * from soft s,article a,down d where s.aid=t_tjid and s.aid=a.id and d.aid=s.aid and (s.freesrc!='' or RIGHT(TRIM(s.iossrc),4)='.ipa')) THEN
INSERT INTO tempRecommand(rid,typeid,icon,`name`,starlev,iossrc,freesrc,iosver,hotcount) select a.id,a.typeid,s.titpic,a.shorttitle,s.pingfen,s.iossrc,s.freesrc,s.iosver,d.downloads from soft s,article a,down d where d.aid=s.aid and s.aid=t_tjid and s.aid=a.id and (s.freesrc!='' or RIGHT(TRIM(s.iossrc),4)='.ipa');
end if;
set app_except_ids = CONCAT(app_except_ids,',',t_tjid);
ELSE
IF EXISTS(select id,icon,`name`,starlev,subtit,browsetimes from zhuanti where id=t_tjid) THEN
insert into tempRecommand(rid,icon,`name`,starlev,subtit,hotcount) select id,icon,`name`,starlev,subtit,browsetimes from zhuanti where id=t_tjid;
end if;
set zt_except_ids = CONCAT(zt_except_ids,',',t_tjid);
END IF;
END IF;
UNTIL Done = 1 END REPEAT;
CLOSE cur_l;
set app_except_ids = CONCAT(0,app_except_ids);
set zt_except_ids = CONCAT(0,zt_except_ids);
set @t_count = CONCAT("select count(*) into @scount from soft s,article a where s.aid=a.id and a.id not in (",app_except_ids,") and (s.freesrc!='' or RIGHT(TRIM(s.iossrc),4)='.ipa');");
PREPARE data1 FROM @t_count;
EXECUTE data1;
#select count(*) INTO scount from soft s,article a where s.aid=a.id and a.id not in (app_except_ids) and (s.freesrc!='' or RIGHT(TRIM(s.iossrc),4)='.ipa');
loop1: WHILE i<@scount DO
set @t_sql_1 = CONCAT("INSERT INTO tempRecommand(rid,typeid,icon,`name`,starlev,iossrc,freesrc,iosver,hotcount) select a.id,a.typeid,s.titpic,a.shorttitle,s.pingfen,s.iossrc,s.freesrc,s.iosver,d.downloads from soft s,article a,down d where d.aid=s.aid and s.aid=a.id and (s.freesrc!='' or RIGHT(TRIM(s.iossrc),4)='.ipa') and a.id not in (",app_except_ids,") limit ",i,",",1);
PREPARE data1 FROM @t_sql_1;
EXECUTE data1;
set @t_sql_2 = CONCAT("insert into tempRecommand(rid,icon,`name`,starlev,subtit,hotcount) select id,icon,`name`,starlev,subtit,browsetimes from zhuanti where id not in (",zt_except_ids,") limit ",i,",",1);
PREPARE data2 FROM @t_sql_2;
EXECUTE data2;
set i=i+1;
END WHILE loop1;
#select i;
#select t_sql_1;
#INSERT INTO tempRecommand(rid,typeid,icon,`name`,starlev,iossrc,freesrc,iosver) select a.id,a.typeid,s.titpic,a.shorttitle,s.pingfen,s.iossrc,s.freesrc,s.iosver from soft s,article a where s.aid=10 and s.aid=a.id and (s.freesrc!='' or RIGHT(TRIM(s.iossrc),4)='.ipa');
END