select sweepstake.id, sweepstake.name titulo, sweepstake.description descripcion, sweepstake.short_text , ifnull(sweepstake.short_url , concat('http://www.legislandoconvos.org/neuquen/verproyecto/?parProy=',sweepstake.id)) short_url , sweepstake.thumbnail, ifnull(user.name,user_admin.fullname) usuario, case when user_admin.id is not null then lower(user_admin.hash) else concat('@',replace( user.name, ' ','')) end userhash, '' dipuarroba, case when user.id_facebook is not null then concat('http://graph.facebook.com/',user.id_facebook,'/picture?width=150&height=150') when user_admin.id is not null then concat('/neuquen/img/profile/', user_admin.profilepic) when user.thumbnail is not null then concat('/neuquen/uploads/users/',user.id,'/profile/',user.thumbnail) else concat('/neuquen/img/noavatar.png') end foto_perfil, sweepstake.id_sweepstake_type modo_num, partido.id grupo_id, sweepstake.video, sweepstake.id_area, case when sweepstake.id_sweepstake_type = 15 then case when user_admin.presidente = 1 then 'Presidente de la Legislatura' when user_admin.presidente = 2 then 'Gestión Legislativa' else 'Diputado' end when sweepstake.id_sweepstake_type = 17 then 'Ciudadano' else '' end modo, case when sweepstake.id_sweepstake_type = 15 then '903d54' when sweepstake.id_sweepstake_type = 17 then '8067fb' else '' end modo_color, case when user_admin.id is not null then partido.nombre else '' end grupo, case when user_admin.id is not null then '585858' else '' end grupo_color, (select count(*) from sweepstake_action sa where sa.id_sweepstake = sweepstake.id and sa.id_action = 5) likes, (select count(*) from sweepstake_action sa where sa.id_sweepstake = sweepstake.id and sa.id_action = 16) dislikes, (select count(*) from sweepstake_action sa where sa.id_sweepstake = sweepstake.id and sa.id_action = 6 ) shares, (select count(*) from sweepstake_action sa where sa.id_sweepstake = sweepstake.id and sa.id_action = 6 and sa.id_userref = -999) i_share, (select count(*) from sweepstake_action sa where sa.id_sweepstake = sweepstake.id and sa.id_action = 10 and sa.id_userref = -999) i_comment, (select count(*) from sweepstake_action sa where sa.id_sweepstake = sweepstake.id and sa.id_action = 5 and sa.id_userref = -999) i_likes, (select count(*) from sweepstake_action sa where sa.id_sweepstake = sweepstake.id and sa.id_action = 16 and sa.id_userref = -999) i_dislikes, user.id userid, user_admin.id adminId from sweepstake left join user on sweepstake.id_user = user.id left join user_admin on sweepstake.id_client = user_admin.id left join partido on partido.id = user_admin.id_partido where sweepstake.status = 'active' and sweepstake.id_sweepstake_type in (15, 17) and ( sweepstake.id_area like concat('%','energia','%') ) order by sweepstake.id desc limit 0,10