MACRO

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

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

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

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

#Фильтры

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

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

#Опции

Показать клоны — добавляет в отчёт объекты, созданные кнопкой «Клонировать» в карточке объекта. По умолчанию опция выключена.

Исходный объект и его клоны показываются вместе, если хотя бы один из них соответствует выбранным фильтрам. Клоны располагаются сразу под исходным объектом в порядке создания, с отступом в столбце «№». Для отбора по дому используется сохранённый у клона дом исходного объекта.

В столбце «№ объекта» появляются метки:

  • у исходного объекта — «Есть клон #…» или «Есть клоны #…, #…»;
  • у клона — «Клон, оригинал #…».

Номера в метках — ссылки на карточки соответствующих объектов. У исходного объекта перечисляются только клоны, показанные в отчёте.

Архивные и удалённые клоны не отображаются. Если исходный объект удалён, клон показывается без него, но сохраняет метку со ссылкой на оригинал. Клон клона располагается под самым первым исходным объектом.

Важно! Объекты, которые не соответствуют выбранным фильтрам, но показаны для связи с оригиналом или клонами, выделяются серым и не имеют порядкового номера. Они не входят в показатель «Всего не продано» и итоговые расчёты количества, площади, стоимости и цены за м². Например, если исходный объект продан, а клон находится в продаже, исходный объект показывается серым, а в итоги входит только клон.

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

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

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

При включённой опции «Показать клоны» в Excel сохраняются порядок объектов, серый фон справочных строк, отступы и метки со ссылками. Ссылки на оригинал и клоны размещаются в отдельных ячейках под номером объекта; остальные ячейки объекта объединяются по высоте. Дополнительные строки со ссылками не увеличивают количество объектов и итоги.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Опция «Показать клоны» и MacroData

Приведённые ниже условия и SQL-запросы описывают режим с выключенной опцией «Показать клоны».

В CRM клон определяется по полям исходной таблицы estate: parent_id = 0, from_source = 'clone' и source_id > 0. Поле source_id указывает на исходный объект, а source_parent_id — на сохранённый дом источника. Объекты, созданные при клонировании дома и привязанные к новому дому, обрабатываются как обычные объекты.

При включённой опции сначала отбираются объекты по фильтрам, затем добавляются доступные связанные объекты. Только строки первоначального отбора участвуют в нумерации и итоговых расчётах.

В MacroData у estate_sells доступно поле source_parent_id, но признаки from_source и source_id не выгружаются. Поэтому полностью воспроизвести связь «оригинал — клоны», справочные строки и метки приведёнными SQL-запросами нельзя.

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

Объект (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):

КодСтатусВыбран по умолчанию
3Нецелевойда
10Проверкада
20Подборда
30Броньда
32Маркетинговый резервда
50Сделка в работенет
52Маркетинговая сделканет

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

КодГруппа
apphouseМногоквартирный дом
cottagesМалоэтажная недвижимость
parkingПаркинг
landgroupЗемельные участки
buildingКоммерческая недвижимость

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

  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) Статус

СтолбецПолеЛогика
(2) Подъезд/секцияestate_sells.geo_house_entranceВ отчёте выводится секция, а если она не заполнена — подъезд. Секция не выгружается в MacroData, поэтому в запросе выводится только подъезд.
(3) Категорияestate_sells.estate_sell_type, estate_sells.estate_sell_categoryНазвание категории. Если у объекта (кроме квартир) задан подтип, например «Офис», отчёт выводит подтип. Подтип не выгружается в MacroData, поэтому в запросе всегда выводится название категории.
(4) № объектаestate_sells.geo_flatnumНомер выводится как есть. Номер дома для объектов категории house не выгружается в MacroData.
(5) Комнатestate_sells.estate_rooms—
(6) Площадьestate_sells.estate_areaОбщая площадь.
(7) Этажestate_sells.estate_floor—
(9) Стоимостьestate_sells.estate_priceСкидки и цена по спецпредложению (estate_price_action) не учитываются.
(10) Статусestate_sells.estate_sell_status → estate_statuses.status_name—
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) Район/объект, группировка и сортировка

СтолбецПолеЛогика
(1) Район/объектestate_houses.nameВнутреннее название дома. Если оно не заполнено, выводится название дома.
Группаestate_houses.house_categoryКоды групп — в таблице выше.
Сортировкаestate_houses.geo_city_complex_id, estate_houses.geo_street_name, estate_houses.geo_houseИспользуются только для порядка строк. Название ЖК (geo_city_complex.geo_complex_name) в запросе выводится для наглядности, сортировка идёт по id.
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.

СтолбецПолеЛогика
(11) Бронь доestate_deals.reserve_dateВыводится при статусе объекта 30.
(12) Покупательestate_deals.contacts_buy_id → contacts.contacts_buy_nameВыводится при статусах объекта 30 и 50.
(13) Менеджерestate_deals.deal_manager_id → users.users_nameВыводится, если статус сделки estate_deals.deal_status равен 105 (Бронь) или 110 (Сделка в работе). Уволенные сотрудники (users.is_fired = 1) не исключаются.
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 хранится как дата без времени, а отчёт сравнивает дату вместе со временем. Поэтому на граничных датах периода результат может отличаться от отчёта.

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

СтолбецПоле
Продавецestate_sells.seller_contacts_id → contacts.contacts_buy_name
Класс квартирыestate_sells.flatClass
Общая площадь ЛПСумма атрибутов estate_sells_attr: 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
Площадь без ЛПestate_sells.estate_area минус общая площадь ЛП. Не путать с полем estate_area_inside: его заполняют в карточке отдельно, отчёт его не использует.
Количество санузловКоличество атрибутов estate_area_sanuzel, estate_area_sanuzel_2, estate_area_sanuzel_3, estate_area_sanuzel_4 со значением больше 0
Почтовый номерestate_sells.geo_flatnum_postoffice. Если не заполнен — estate_sells.geo_flatnum.
Тегиestate_tags.tags_id → tags.tags_name, теги объектов (estate_tags.entity_type = 'sell')
Служебные отметкиestate_sells.special_notes
Способ обеспеченияДанные не выгружаются в MacroData.
Кадастровый номерДанные не выгружаются в MacroData.
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;