select COUNT(*) as vei_rev_total from geodesic_userdata_cities lig_user_city, geodesic_userdata a, geodesic_user_groups_price_plans b, geodesic_logins l, sinaliza_cidade d where a.id = b.id and group_id = 13 and price_plan_id = 24 and storefront_header <> '' and a.optional_field_9 <> 1 and a.id = l.id and l.status = 1 /* and d.id = a.optional_field_7 */ and d.id = lig_user_city.id_cidade and a.id = lig_user_city.seller and (select count(*) from geodesic_classifieds where live = 1 and hide = 0 and seller = a.id) > 0 ORDER BY firstname ASC

select COUNT(*) as total from geodesic_userdata_cities lig_user_city, geodesic_userdata a, geodesic_user_groups_price_plans b, geodesic_logins l, sinaliza_cidade d where lig_user_city.id_cidade = d.id and lig_user_city.seller = a.id and a.id = b.id and group_id = 13 and price_plan_id = 24 and storefront_header <> '' and a.optional_field_9 <> 1 /* and d.id = a.optional_field_7*/ and a.id = l.id and l.status = 1 and (select count(*) from geodesic_classifieds where live = 1 and hide = 0 and seller = a.id) > 0

select (select count(*) from geodesic_userdata_cities where seller = a.id ) as loja_contador, a.id, lig_user_city.id_loja as loja_filial, /*(firstname) as company_name,*/ lig_user_city.loja_nome as company_name, d.id as cod_cidade, (d.nome) as cidade, d.uf as estado, /* Fiz inner join com geodesic_classifieds pois o campo endereco é gravado na tabela geodesic_classifieds Problema ocorre quando é por importação XML pois é uma revenda em cidades diferentes Retorno da geodesic_classifieds tem que ser unico ( LIMIT 1 ) para nao gerar erro */ (select upper(location_address) from geodesic_classifieds c where c.optional_field_7 =lig_user_city.id_loja and c.seller =lig_user_city.seller limit 1 ) as loja_endereco, (select c.email from geodesic_classifieds c where c.optional_field_7 =lig_user_city.id_loja and c.seller =lig_user_city.seller limit 1 ) as loja_email, (select c.location_zip from geodesic_classifieds c where c.optional_field_7 =lig_user_city.id_loja and c.seller =lig_user_city.seller limit 1 ) as loja_cep, (select c.phone from geodesic_classifieds c where c.optional_field_7 =lig_user_city.id_loja and c.seller =lig_user_city.seller limit 1 ) as loja_telefone1, (select case when length(replace(c.phone2, ' ', '')) = 3 then '' else c.phone2 end from geodesic_classifieds c where c.optional_field_7 =lig_user_city.id_loja and c.seller =lig_user_city.seller limit 1 ) as loja_telefone2, (select case when length(replace(c.phone3, ' ', '')) = 3 then '' else c.phone3 end from geodesic_classifieds c where c.optional_field_7 =lig_user_city.id_loja and c.seller =lig_user_city.seller limit 1 ) as loja_telefone3, (select c.optional_field_16 from geodesic_classifieds c where c.optional_field_7 =lig_user_city.id_loja and c.seller =lig_user_city.seller limit 1 ) as loja_bairo, (select c.optional_field_15 from geodesic_classifieds c where c.optional_field_7 =lig_user_city.id_loja and c.seller =lig_user_city.seller limit 1 ) as loja_referencia, /* a.email as loja_email, */ /* a.address as loja_endereco, */ /* a.zip as loja_cep,*/ /* a.phone as loja_telefone1, case when length(replace(a.phone2, ' ', '')) = 3 then '' else a.phone2 end as loja_telefone2, case when length(replace(a.phone3, ' ', '')) = 3 then '' else a.phone3 end as loja_telefone3,*/ a.fax as loja_fax, /* Modificado em 03/08/16 - Mostar Site apenas das revedas que possuem site conosco */ a.clk_mostra_site_portal as mostra_link_acessar_site, trim(a.url) as loja_site, /*(select fen_con_dominio_dominio from fen_revenda_contrato where fen_rev_id = a.id and fen_con_tipo = 2 and fen_con_status = 1) as loja_site,*/ /*a.bairro as loja_bairo, a.referencia as loja_referencia,*/ case when optional_field_2 = 'LOCADORA' then concat('/henji/storefrontImages/',optional_field_10) else concat('/storefrontImages/',optional_field_10) end as loja_logo, /* case when a.expose_optional_10 = 2 then 'selo-autofest-maior.gif' when a.expose_optional_10 = 51 then 'selo-locadoras-maior.gif' else '' end as loja_autoshopping */ /* o LOGO DOS AUTOSHOPPINGS SE ENCONTRA EM CLICKFENAUTO (Outra base) */ ( select fen_ash_logo from stmotors_fenauto.fen_autoshopping clickfenauto where clickfenauto.fen_ash_id = a. expose_optional_10) as loja_autoshopping from geodesic_userdata_cities lig_user_city, geodesic_userdata a, geodesic_user_groups_price_plans b, geodesic_logins l, sinaliza_cidade d where a.id = b.id and group_id = 13 and price_plan_id = 24 and storefront_header <> '' and a.optional_field_9 <> 1 and a.id = l.id and l.status = 1 /* and d.id = a.optional_field_7 */ and d.id = lig_user_city.id_cidade and a.id = lig_user_city.seller and (select count(*) from geodesic_classifieds where live = 1 and hide = 0 and seller = a.id) > 0 ORDER BY firstname ASC LIMIT 0,30