select c.partner_status_id,c.rooms_c,c.match_type, c.users_registry_id as id, c.vat, c.address, c.base_country, c.city, c.company_type_id,
c.creator_id,c.description,c.iata,c.languages,c.most_covered_countries,c.longitude,c.latitude,c.name,
c.phone,c.secondary_email,c.website
 from ( select c.*,
    CASE WHEN (tmp2.days is false) THEN 'all'
        WHEN (tmp2.rooms = 1 and tmp2.price = 1) THEN 'full'
        WHEN (tmp2.rooms = 2 and tmp2.price = 1) THEN 'price'
        WHEN (tmp2.rooms = 1 and tmp2.price = 2) THEN 'rooms'
        WHEN (tmp2.rooms = 2 and tmp2.price = 2) THEN 'all'
        ELSE null
    END as match_type,
    (select
        CASE WHEN com.users_registry_id = p1.second_company_id and p1.status_id = ps2.partners_statuses_id THEN ps3.partners_statuses_id
           ELSE p1.status_id END
        from company com
        left join company c2 on com.users_registry_id != c2.users_registry_id  and c2.users_registry_id = c.users_registry_id
        left join partners p1 on (c2.users_registry_id = p1.first_company_id and p1.second_company_id = com.users_registry_id)
            or (c2.users_registry_id = p1.second_company_id and p1.first_company_id = com.users_registry_id)
        left join partners_statuses ps2 on ps2.name = 'PENDING'
        left join partners_statuses ps3 on ps3.name = 'NEW'
        left join partners_statuses ps4 on ps4.name = 'REMOVED'
        where com.users_registry_id = :currentCompanyId) as partner_status_id,
    CASE WHEN (null is not null and tmp2.rooms = 1) THEN tmp2.rooms_c
        ELSE null
    END as rooms_c
from company c
join
    (select
        tmp.users_registry_id as company_id, min(tmp.room_count) as rooms_c,
        CASE
            WHEN (select (cast(:dateTo AS date) - cast(:dateFrom AS date)) = count (*)) THEN true
            ELSE false
        END as days,
        CASE
            WHEN (null is null or sum(case when tmp.room_count >= null then 1 else 0 end) = count (*)) THEN 1
            WHEN (sum(case when tmp.room_count < null  then 1 else 0 end) > 0) THEN 0
            ELSE 2
        END as rooms,
        CASE
            WHEN (11.0 is null or sum(case when tmp.min_price <= 11.0 then 1 else 0 end) = count (*)) THEN 1
            WHEN (sum(case when tmp.min_price > 11.0 then 1 else 0 end) > 0)  THEN 0
            ELSE 2
        END as price
    from (
        select t.* from (select distinct
            c.users_registry_id,
            cast( concat_ws( '-',
            pc.year,
            pcd.month + 1,
            pcd.day ) as date ) as c_date,
            pcd.room_count,
            hs.min_price,
            hs.max_price
        from
            company c
            left join company_type ct on ct.company_type_id = c.company_type_id and ct.name = 'HOTEL'
            left join countries cntr on cntr.country_id = c.base_country
            left join hotel_detail det on det.company_id = c.users_registry_id
            left join price_calendar pc on pc.company_id = c.users_registry_id and pc.year is not null
            left join price_calendar_detales pcd on pcd.price_calendar_id = pc.price_calendar_id
                and pcd.day is not null and pcd.month is not null
            join hotel_seasons hs on hs.id = pcd.season_id
        where 1=1
        ) t
            where t.c_date between :dateFrom and :dateTo
        ) tmp group by tmp.users_registry_id
    ) tmp2 on tmp2.company_id = c.users_registry_id) c where c.match_type is not null