Месторасположение отчёта: Отчёты → Сделки → Реестр расторгнутых договоров
Отчёт «Реестр расторгнутых договоров» предназначен для анализа сделок, по которым произошло расторжение договора. Он позволяет руководству и финансово-коммерческому блоку контролировать отток, отслеживать суммы возвратов покупателям и фиксировать причины расторжений в разрезе объектов и менеджеров.
#Работа с отчётом
#Фильтры
- Дата — период, по которому формируется выборка. Нажмите на тип даты, чтобы изменить событие, по которому будут отобраны сделки:
- Дата договора — дата подписания основного договора.
- Дата расторжения — фактическая дата расторжения сделки.
- Дата подписания договора — дата подписания в системе.
- Дата согласования договора — дата согласования договора.
- Дата регистрации расторжения — дата государственной регистрации расторжения.
- ЖК/Дом — ограничивает выборку по жилому комплексу или конкретному строению. При выборе ЖК входящие в него дома выбираются автоматически.
- Категория объекта — позволяет выбрать один или несколько типов объектов: Квартира, Машино-место, Малоэтажка, Земельный участок, Кладовая, Коммерческая недвижимость.
- Клиент — поиск и выбор конкретного покупателя. Начните вводить имя или номер телефона, затем выберите нужный контакт из выпадающего списка.
- Менеджер — ограничивает выборку по одному или нескольким менеджерам сделки.
- Сотрудник сопровождения сделки — ограничивает выборку по одному или нескольким сотрудникам сопровождения.
Нажмите Сформировать для построения отчёта. Для выгрузки данных используйте кнопку → XLS.
#Структура отчёта
Отчёт представляет собой построчный реестр расторгнутых сделок. Каждая строка соответствует одному расторжению. В нижней строке «Итого» суммируются площадь, стоимость по договору и сумма оплаченных средств.
Номер объекта в столбце «№ объекта» является активной ссылкой — нажмите на него, чтобы перейти на карточку объекта.
Блок «Сумма и дата возврата» отображает финансовые операции возврата средств покупателю по данной сделке. На экране каждая операция показывается отдельной строкой с датой, суммой и статусом, а внизу — подытог «Итого» по сделке. Возможные статусы операции:
- Проведено — возврат фактически выплачен.
- К оплате — возврат запланирован, но ещё не проведён.
В столбце «Сотрудник сопровождения» отображается ФИО сотрудника, сопровождающего сделку.
Столбец «Основание» содержит реквизиты дополнительного соглашения к сделке, на основании которого произошло расторжение. Если дополнительное соглашение не привязано — отображается значение «Не выбрано».
#Технический паспорт отчёта
#Данные в системе
Основа отчёта — сделки в статусе Сделка отменена (код 140 по справочнику estate_deals_statuses). Данные берутся из таблицы estate_deals с обязательными связями к estate_sells (объект продажи), estate_houses (дом), contacts (покупатель и продавец) и users (менеджеры). Данные о расположении объекта хранятся непосредственно в estate_sells и estate_houses; площади «Полная» и «Приведённая» — через estate_sells_attr. Финансовые операции оплат и возвратов формируются отдельными запросами из таблицы finances с классификацией типов через finances_types.
Тип договора (2), Предварительный договор (3), № договора (4), Дата договора (5)
Данные хранятся в estate_deals. Предварительный договор формируется объединением номера и даты предварительного договора и может быть заполнен для любого типа договора при наличии данных в сделке.
Принцип выбора данных в MacroData без учёта дополнительных фильтров:
SELECT
ed.agreement_type AS `Тип договора`,
CONCAT(
IFNULL(ed.preliminary_number, ''),
IF(ed.preliminary_date IS NOT NULL,
CONCAT(' от ', DATE_FORMAT(ed.preliminary_date, '%d.%m.%Y')), '')
) AS `Предварительный договор`,
ed.agreement_number AS `№ договора`,
ed.agreement_date AS `Дата договора`
FROM estate_deals ed
WHERE ed.deal_status = 140;Район (6), Микрорайон (7), № дома (8), № объекта (9), Этаж (10), Категория объекта (11), Кол-во комнат (12)
Данные о районе, микрорайоне и номере дома хранятся в estate_houses и являются общими для всех объектов в доме. Номер объекта, этаж, категория и комнатность — прямые поля estate_sells. Поле «Кол-во комнат» заполняется только для категории flat. Столбец «Микрорайон» отображается только при включённой настройке компании estate_show_construction_params. В HTML-версии отчёта значение столбца «№ объекта» является ссылкой на карточку объекта (/account/estate/view/{id}); в выгрузке XLS отображается как обычный текст.
Принцип выбора данных в MacroData без учёта дополнительных фильтров:
SELECT
ed.agreement_number AS `№ договора`,
h.geo_region_name AS `Район`,
h.geo_quarter AS `Микрорайон`,
h.name AS `№ дома`,
sell.geo_flatnum AS `№ объекта`,
sell.estate_floor AS `Этаж`,
sell.estate_sell_category AS `Категория объекта`,
sell.estate_rooms AS `Кол-во комнат`
FROM estate_deals ed
INNER JOIN estate_sells sell ON ed.estate_sell_id = sell.estate_sell_id
INNER JOIN estate_houses h ON ed.house_id = h.house_id
WHERE ed.deal_status = 140;Продавец (13), Покупатель (14)
Данные хранятся в таблице contacts. Продавец связывается через estate_deals.seller_contacts_id, покупатель — через estate_deals.contacts_buy_id. Имя контакта — поле contacts_buy_name.
SELECT
ed.agreement_number AS `№ договора`,
c_sell.contacts_buy_name AS `Продавец`,
c_buy.contacts_buy_name AS `Покупатель`
FROM estate_deals ed
LEFT JOIN contacts c_sell ON ed.seller_contacts_id = c_sell.contacts_id
LEFT JOIN contacts c_buy ON ed.contacts_buy_id = c_buy.contacts_id
WHERE ed.deal_status = 140;Площадь общ. проектная (15), Полная площадь (16), Приведённая площадь (17)
Три типа площадей имеют следующий смысл:
- Площадь общ. проектная — базовая проектная площадь объекта из карточки объекта (
estate_sells.estate_area). - Полная площадь — сумма всех помещений без понижающих коэффициентов, включая балконы и лоджии (
estate_sells_attr, ключestate_area_max_custom). - Приведённая площадь — площадь с коэффициентами для балконов (0,3) и лоджий (0,5) (
estate_sells_attr, ключestate_area_reduced).
В строке «Итого» суммируется значение столбца Площадь общ. проектная.
SELECT
ed.agreement_number AS `№ договора`,
sell.estate_area AS `Площадь общ. проектная`,
esa_max.attr_value AS `Полная площадь`,
esa_red.attr_value AS `Приведённая площадь`
FROM estate_deals ed
INNER JOIN estate_sells sell ON ed.estate_sell_id = sell.estate_sell_id
LEFT JOIN estate_sells_attr esa_max
ON esa_max.estate_sell_id = sell.estate_sell_id
AND esa_max.attr_name = 'estate_area_max_custom'
LEFT JOIN estate_sells_attr esa_red
ON esa_red.estate_sell_id = sell.estate_sell_id
AND esa_red.attr_name = 'estate_area_reduced'
WHERE ed.deal_status = 140;Цена за м² по договору (18), Стоимость объекта по договору (19)
Стоимость сделки хранится в estate_deals.deal_sum, цена за квадратный метр вычисляется как отношение суммы сделки к площади.
SELECT
ed.agreement_number AS `№ договора`,
ed.deal_sum AS `Стоимость объекта по договору`,
ROUND(ed.deal_sum / NULLIF(ed.deal_area, 0), 2) AS `Цена за м² по договору`
FROM estate_deals ed
WHERE ed.deal_status = 140;Оплачено, ₽ (20)
Суммарный объём поступивших платежей по сделке. Берётся из таблицы finances — суммируются подтверждённые операции (status_name = 'on'), тип которых в finances_types имеет признак is_income = 1. Операции сверх суммы сделки (is_over_deal_sum = 1) не учитываются.
SELECT
ed.agreement_number AS `№ договора`,
SUM(f.summa) AS `Оплачено`
FROM estate_deals ed
LEFT JOIN finances f ON f.deal_id = ed.deal_id
AND f.summa > 0
AND f.is_over_deal_sum = 0
AND f.status_name = 'on'
LEFT JOIN finances_types ft ON ft.id = f.types_id
AND ft.company_id = f.company_id
AND ft.is_income = 1
WHERE ed.deal_status = 140
GROUP BY ed.deal_id, ed.agreement_number;Сумма и дата возврата (21)
Расходные финансовые операции возврата средств покупателю — типы с признаком finances_types.is_income_lower = 1. В выборку попадают операции в статусах «Проведено» (on) и «К оплате» (reserved).
SELECT
ed.agreement_number AS `№ договора`,
f.date_added AS `Дата возврата`,
f.summa AS `Сумма возврата`,
f.status_name AS `Статус`
FROM estate_deals ed
LEFT JOIN finances f ON f.deal_id = ed.deal_id
AND f.summa > 0
AND f.status_name IN ('on', 'reserved')
LEFT JOIN finances_types ft ON ft.id = f.types_id
AND ft.company_id = f.company_id
AND ft.is_income_lower = 1
WHERE ed.deal_status = 140
ORDER BY ed.deal_id, f.date_added;Дата снятия залога (22)
Данные не выгружаются в MacroData.
Фактическая дата расторжения сделки (23)
Хранится в estate_deals.deal_date_cancelled.
SELECT
ed.agreement_number AS `№ договора`,
ed.deal_date_cancelled AS `Фактическая дата расторжения`
FROM estate_deals ed
WHERE ed.deal_status = 140;Дата регистрации расторжения (24), Номер регистрации расторжения (25)
Данные не выгружаются в MacroData.
Менеджер сделки (26)
Ответственный менеджер берётся из таблицы users через estate_deals.deal_manager_id.
SELECT
ed.agreement_number AS `№ договора`,
u.users_name AS `Менеджер сделки`
FROM estate_deals ed
LEFT JOIN users u ON u.id = ed.deal_manager_id
WHERE ed.deal_status = 140;Сотрудник сопровождения (27)
ФИО сотрудника сопровождения сделки. Берётся из estate_deals.deal_co_manager_id. Если сотрудник сопровождения не назначен — поле пустое.
SELECT
ed.agreement_number AS `№ договора`,
u_co.users_name AS `Сотрудник сопровождения`
FROM estate_deals ed
LEFT JOIN users u_co ON u_co.id = ed.deal_co_manager_id
WHERE ed.deal_status = 140;Причина расторжения (28), Основание (29)
Данные не выгружаются в MacroData.
Итоговый запрос
Полный запрос для получения данных реестра в MacroData. Финансовые столбцы (Оплачено, Сумма и дата возврата) выбираются отдельными запросами, приведёнными выше.
SELECT
ed.deal_id,
ed.agreement_type AS `Тип договора`,
CONCAT(
IFNULL(ed.preliminary_number, ''),
IF(ed.preliminary_date IS NOT NULL,
CONCAT(' от ', DATE_FORMAT(ed.preliminary_date, '%d.%m.%Y')), '')
) AS `Предварительный договор`,
ed.agreement_number AS `№ договора`,
ed.agreement_date AS `Дата договора`,
h.geo_region_name AS `Район`,
h.geo_quarter AS `Микрорайон`,
h.name AS `№ дома`,
sell.geo_flatnum AS `№ объекта`,
sell.estate_floor AS `Этаж`,
sell.estate_sell_category AS `Категория объекта`,
sell.estate_rooms AS `Кол-во комнат`,
c_sell.contacts_buy_name AS `Продавец`,
c_buy.contacts_buy_name AS `Покупатель`,
sell.estate_area AS `Площадь общ. проектная`,
esa_max.attr_value AS `Полная площадь`,
esa_red.attr_value AS `Приведённая площадь`,
ROUND(ed.deal_sum / NULLIF(ed.deal_area, 0), 2) AS `Цена за м² по договору`,
ed.deal_sum AS `Стоимость объекта по договору`,
ed.deal_date_cancelled AS `Фактическая дата расторжения`,
u.users_name AS `Менеджер сделки`,
u_co.users_name AS `Сотрудник сопровождения`
FROM estate_deals ed
INNER JOIN estate_sells sell ON ed.estate_sell_id = sell.estate_sell_id
INNER JOIN estate_houses h ON ed.house_id = h.house_id
LEFT JOIN contacts c_sell ON ed.seller_contacts_id = c_sell.contacts_id
LEFT JOIN contacts c_buy ON ed.contacts_buy_id = c_buy.contacts_id
LEFT JOIN users u ON u.id = ed.deal_manager_id
LEFT JOIN users u_co ON u_co.id = ed.deal_co_manager_id
LEFT JOIN estate_sells_attr esa_max
ON esa_max.estate_sell_id = sell.estate_sell_id
AND esa_max.attr_name = 'estate_area_max_custom'
LEFT JOIN estate_sells_attr esa_red
ON esa_red.estate_sell_id = sell.estate_sell_id
AND esa_red.attr_name = 'estate_area_reduced'
WHERE ed.deal_status = 140
ORDER BY ed.deal_date_cancelled DESC;Строка «Итого»
В строке «Итого» суммируются три показателя:
- Площадь общ. проектная — суммарная проектная площадь по всем расторгнутым сделкам в выборке.
- Стоимость объекта по договору — суммарная стоимость расторгнутых сделок.
- Оплачено, ₽ — суммарный объём поступивших платежей по всем сделкам в выборке.