Отчёт по непроданной недвижимости

Месторасположение: Отчёты → Ассортимент → Отчёт по непроданной недвижимости

Отчёт показывает объекты, которые выставлены на продажу и ещё не проданы. По каждому объекту в нём есть площадь, этаж, стоимость, цена за м² и текущий статус. Для объектов в брони и в сделке видны покупатель, менеджер и дата окончания брони. Отчёт помогает оценить остатки ассортимента по домам и категориям.

#Работа с отчётом

#Фильтры

Чтобы сформировать отчёт, задайте нужные фильтры и нажмите Найти. Чтобы сбросить все фильтры, нажмите Очистить.

• Категория — категория объекта: квартира, коммерческое помещение и т. д.
• Статус — статус объекта. Доступны статусы Нецелевой, Проверка, Подбор, Бронь, Маркетинговый резерв, Сделка в работе и Маркетинговая сделка. По умолчанию выбраны первые пять. Если очистить фильтр, отчёт снова построится по пяти статусам по умолчанию.
• ЖК/Дом — дом, по которому строится отчёт. Дома в списке сгруппированы по ЖК.
• Бронь до (с — по) — дата окончания брони. Если фильтр заполнен, в отчёт попадают только объекты с бронью или сделкой в работе, у которых дата входит в указанный период.
• Стоимость (от — до) — стоимость объекта из карточки.
• Покупатель — покупатель из брони или сделки. В списке показаны покупатели найденных объектов.
• Продавец — контакт-собственник из карточки объекта. Это может быть физлицо или юрлицо. В списке показаны продавцы найденных объектов.
• № Квартиры (с — по) и № Машино-места (от — до) — номер объекта. Указывайте целые числа.
• Комнат (от — до) — количество комнат.
• Цена за м² (от — до) — цена за квадратный метр. Объекты без площади при заполненном фильтре в отчёт не попадают.
• Этаж (от — до) — этаж объекта.
• Площадь (от — до) — общая площадь объекта.
• Класс квартиры — класс, указанный в карточке объекта. В списке показаны все значения, которые заданы в карточках объектов компании.
• Способ обеспечения — Фонд, Эскроу или Страховка.
• Теги — теги объекта. В отчёт попадают объекты, у которых есть хотя бы один из выбранных тегов.

#Опции

Показывать столбцы — дополнительные столбцы таблицы. По умолчанию они скрыты. Можно включить следующие столбцы:

• Продавец — контакт-собственник из карточки объекта: физлицо или юрлицо. Столбец выводится сразу после столбца «№».
• Класс квартиры — класс, указанный в карточке объекта. Столбец выводится сразу после столбца «Категория».
• Общая площадь ЛП — сумма площадей лоджий, балконов и террас из карточки объекта. Учитываются до трёх лоджий, до двух балконов и до двух террас.
• Площадь без ЛП — общая площадь объекта за вычетом общей площади ЛП.
• Количество санузлов — количество санузлов, для которых в карточке объекта указана площадь. Учитывается до четырёх санузлов.
• Почтовый номер — почтовый номер объекта из карточки. Если он не заполнен, выводится номер объекта. Столбец выводится сразу после столбца «№ объекта».
• Теги — теги, присвоенные объекту.
• Служебные отметки — служебные отметки из карточки объекта.
• Способ обеспечения — способ обеспечения из карточки объекта: Фонд, Эскроу или Страховка.
• Кадастровый номер — кадастровый номер объекта из карточки. Столбец выводится после столбца «№ объекта», а если включён «Почтовый номер» — после него.

Чтобы выгрузить отчёт в Excel, нажмите → XLS. В файл попадает та же таблица, что на экране: с выбранными фильтрами, столбцами, группами и итогами.

#Структура отчёта

Над таблицей выводится показатель Всего не продано — количество объектов, которые подходят под заданные фильтры.

Строки сгруппированы по типу дома: Многоквартирный дом, Малоэтажная недвижимость, Паркинг, Земельные участки, Коммерческая недвижимость. Если в отчёт попали дома из разных районов, группы выводятся отдельно для каждого района. Внутри группы объекты отсортированы по ЖК, дому и номеру объекта, жилые объекты идут перед коммерческими.

(1) Район/объект — внутреннее название из карточки объекта. Если оно не заполнено, выводится адрес дома. По клику открывается карточка объекта.

(2) Подъезд/секция — секция объекта. Если она не заполнена, выводится номер подъезда.

(3) Категория — категория или подтип объекта, например «Квартира», «Офис», «Кладовка».

(4) № объекта — номер объекта, указанный в карточке.

(5) Комнат — количество комнат.

(6) Площадь — общая площадь объекта, м².

(7) Этаж — этаж объекта.

(8) Цена за м² — стоимость объекта, разделённая на площадь и округлённая до целого. Цена указана в валюте компании.

(9) Стоимость — текущая стоимость объекта из карточки, без учёта скидок и спецпредложений.

(10) Статус — текущий статус объекта.

(11) Бронь до — дата окончания брони. Заполняется для объектов в статусе Бронь.

(12) Покупатель — покупатель из брони или сделки. Заполняется для объектов в статусах Бронь и Сделка в работе.

(13) Менеджер — сотрудник, ответственный за бронь или сделку в работе.

В конце каждой группы выводится строка Итого по группе, в конце таблицы — строка Итого по компании. В итоговых строках показаны:

• количество объектов — в столбце «Категория»;
• суммарная площадь и суммарная стоимость;
• средняя цена за м² — суммарная стоимость, разделённая на суммарную площадь.

#Технический паспорт отчёта

#Условия попадания в отчёт

Объект (estate_sells) попадает в отчёт, если выполнены все условия:

• объект выставлен на продажу: estate_sells.activity = 'sell';
• статус объекта estate_sells.estate_sell_status входит в список: 3, 10, 20, 30, 32, 50, 52. Статус заявки или сделки на отбор не влияет;
• дом, к которому относится объект (estate_sells.house_id → estate_houses.house_id), не удалён и не в архиве: estate_houses.status > 0 и estate_houses.status <> 1;
• дом принадлежит компании или открыт ей через партнёрский доступ. Данные о партнёрском доступе не выгружаются в MacroData, поэтому запросы ниже строятся только по домам компании.

Коды статусов объекта (справочник estate_statuses):

Группа в таблице определяется категорией дома estate_houses.house_category:

Строки сортируются в таком порядке:

1. район дома (данные не выгружаются в MacroData);
2. тип дома — estate_houses.house_category;
3. ЖК — estate_houses.geo_city_complex_id;
4. сначала жилые объекты, потом коммерческие — estate_sells.estate_sell_type;
5. категория объекта — estate_sells.estate_sell_category;
6. улица — estate_houses.geo_street_name;
7. группа домов (данные не выгружаются в MacroData);
8. номер дома — estate_houses.geo_house;
9. номер объекта — estate_sells.geo_flatnum.

ЖК сортируются по id записи справочника, а не по названию, поэтому в отчёте они могут идти не по алфавиту. Номера дома и объекта сравниваются как числа: сначала по длине строки, потом по значению.

#Данные в системе

Объект — (2) Подъезд/секция, (3) Категория, (4) № объекта, (5) Комнат, (6) Площадь, (7) Этаж, (9) Стоимость, (10) Статус

SELECT
    s.estate_sell_id                                AS `ID объекта`,
    s.geo_house_entrance                            AS `Подъезд/секция`,
    CASE
        WHEN s.estate_sell_type = 'comm'            THEN 'Коммерческий объект'
        WHEN s.estate_sell_category = 'flat'        THEN 'Квартира'
        WHEN s.estate_sell_category = 'garage'      THEN 'Машино-место'
        WHEN s.estate_sell_category = 'storageroom' THEN 'Кладовая'
        WHEN s.estate_sell_category = 'house'       THEN 'Строение'
        WHEN s.estate_sell_category = 'land'        THEN 'Земельный участок'
        ELSE s.estate_sell_category
    END                                             AS `Категория`,
    s.geo_flatnum                                   AS `№ объекта`,
    s.estate_rooms                                  AS `Комнат`,
    s.estate_area                                   AS `Площадь, м²`,
    s.estate_floor                                  AS `Этаж`,
    s.estate_price                                  AS `Стоимость`,
    st.status_name                                  AS `Статус`
FROM estate_sells s
INNER JOIN estate_houses h ON h.house_id = s.house_id
LEFT JOIN estate_statuses st ON st.status_id = s.estate_sell_status
WHERE s.activity = 'sell'
  AND s.estate_sell_status IN (3, 10, 20, 30, 32)
  AND h.status > 0
  AND h.status <> 1;

Дом — (1) Район/объект, группировка и сортировка

SELECT
    h.house_category                                AS `Группа`,
    h.name                                          AS `Район/объект`,
    c.geo_complex_name                              AS `ЖК`,
    h.geo_street_name                               AS `Улица`,
    h.geo_house                                     AS `Дом`,
    s.estate_sell_id                                AS `ID объекта`,
    s.geo_flatnum                                   AS `№ объекта`
FROM estate_sells s
INNER JOIN estate_houses h ON h.house_id = s.house_id
LEFT JOIN geo_city_complex c ON c.geo_complex_id = h.geo_city_complex_id
WHERE s.activity = 'sell'
  AND s.estate_sell_status IN (3, 10, 20, 30, 32)
  AND h.status > 0
  AND h.status <> 1
ORDER BY
    FIELD(h.house_category, 'apphouse', 'cottages', 'parking', 'landgroup', 'building'),
    h.geo_city_complex_id,
    CASE WHEN s.estate_sell_type = 'living' THEN 0 ELSE 1 END,
    s.estate_sell_category,
    h.geo_street_name,
    LENGTH(h.geo_house), h.geo_house,
    LENGTH(s.geo_flatnum), s.geo_flatnum;

Бронь и сделка — (11) Бронь до, (12) Покупатель, (13) Менеджер

Сделка объекта находится через estate_sells.deal_id → estate_deals.deal_id.

SELECT
    s.estate_sell_id                                AS `ID объекта`,
    s.geo_flatnum                                   AS `№ объекта`,
    CASE WHEN s.estate_sell_status = 30
        THEN d.reserve_date END                     AS `Бронь до`,
    CASE WHEN s.estate_sell_status IN (30, 50)
        THEN buyer.contacts_buy_name END            AS `Покупатель`,
    CASE WHEN d.deal_status IN (105, 110)
        THEN u.users_name END                       AS `Менеджер`
FROM estate_sells s
INNER JOIN estate_houses h ON h.house_id = s.house_id
LEFT JOIN estate_deals d ON d.deal_id = s.deal_id
LEFT JOIN contacts buyer ON buyer.contacts_id = d.contacts_buy_id
LEFT JOIN users u ON u.id = d.deal_manager_id
WHERE s.activity = 'sell'
  AND s.estate_sell_status IN (3, 10, 20, 30, 32)
  AND h.status > 0
  AND h.status <> 1;

Если заполнен фильтр «Бронь до», добавьте условие:

  AND d.reserve_date BETWEEN DATE('{{start_date}}') AND DATE('{{end_date}}')

В MacroData reserve_date хранится как дата без времени, а отчёт сравнивает дату вместе со временем. Поэтому на граничных датах периода результат может отличаться от отчёта.

Дополнительные столбцы

SELECT
    s.estate_sell_id                                AS `ID объекта`,
    seller.contacts_buy_name                        AS `Продавец`,
    s.flatClass                                     AS `Класс квартиры`,
    IFNULL(lp.area_lp, 0)                           AS `Общая площадь ЛП, м²`,
    s.estate_area - IFNULL(lp.area_lp, 0)           AS `Площадь без ЛП, м²`,
    IFNULL(sz.sanuzel_cnt, 0)                       AS `Количество санузлов`,
    COALESCE(NULLIF(s.geo_flatnum_postoffice, ''), s.geo_flatnum) AS `Почтовый номер`,
    tg.tags                                         AS `Теги`,
    s.special_notes                                 AS `Служебные отметки`
FROM estate_sells s
INNER JOIN estate_houses h ON h.house_id = s.house_id
LEFT JOIN contacts seller ON seller.contacts_id = s.seller_contacts_id
LEFT JOIN (
    SELECT estate_sell_id,
           SUM(CAST(attr_value AS DECIMAL(16,4))) AS area_lp
    FROM estate_sells_attr
    WHERE attr_name IN (
        'estate_area_loggia', 'estate_area_loggia_2', 'estate_area_loggia_3',
        'estate_area_balcony', 'estate_area_balcony_2',
        'estate_area_terrace', 'estate_area_terrace_2'
    )
    GROUP BY estate_sell_id
) lp ON lp.estate_sell_id = s.estate_sell_id
LEFT JOIN (
    SELECT estate_sell_id,
           COUNT(*) AS sanuzel_cnt
    FROM estate_sells_attr
    WHERE attr_name IN (
        'estate_area_sanuzel', 'estate_area_sanuzel_2',
        'estate_area_sanuzel_3', 'estate_area_sanuzel_4'
    )
      AND CAST(attr_value AS DECIMAL(16,4)) > 0
    GROUP BY estate_sell_id
) sz ON sz.estate_sell_id = s.estate_sell_id
LEFT JOIN (
    SELECT et.estate_id,
           GROUP_CONCAT(t.tags_name ORDER BY t.tags_name SEPARATOR ', ') AS tags
    FROM estate_tags et
    INNER JOIN tags t ON t.id = et.tags_id
    WHERE et.entity_type = 'sell'
    GROUP BY et.estate_id
) tg ON tg.estate_id = s.estate_sell_id
WHERE s.activity = 'sell'
  AND s.estate_sell_status IN (3, 10, 20, 30, 32)
  AND h.status > 0
  AND h.status <> 1;

#Расчётные данные

• (8) Цена за м² = ROUND(estate_sells.estate_price / estate_sells.estate_area). Если стоимость или площадь равна 0, значение не выводится. Валюта в заголовке столбца — валюта компании.
• Цена за м² в итоговых строках = ROUND(SUM(estate_price) / SUM(estate_area)) по объектам группы или компании.
• Площадь и Стоимость в итоговых строках — суммы по объектам группы или компании.
• Количество объектов в итоговых строках выводится в столбце «Категория».
• Всего не продано — количество объектов после применения всех фильтров.

Цена за м² по объекту:

CASE WHEN s.estate_price > 0 AND s.estate_area > 0
    THEN ROUND(s.estate_price / s.estate_area)
END AS `Цена за м²`

Итоговые строки: строки с кодом группы — это «Итого по группе», строка с пустой группой — «Итого по компании». В отчёте группы дополнительно делятся по районам. Район не выгружается в MacroData, поэтому в запросе группы по районам не делятся.

SELECT
    h.house_category                                AS `Группа`,
    COUNT(s.estate_sell_id)                         AS `Количество объектов`,
    SUM(s.estate_area)                              AS `Площадь, м²`,
    SUM(s.estate_price)                             AS `Стоимость`,
    ROUND(SUM(s.estate_price) / NULLIF(SUM(s.estate_area), 0)) AS `Цена за м²`
FROM estate_sells s
INNER JOIN estate_houses h ON h.house_id = s.house_id
WHERE s.activity = 'sell'
  AND s.estate_sell_status IN (3, 10, 20, 30, 32)
  AND h.status > 0
  AND h.status <> 1
GROUP BY h.house_category WITH ROLLUP;