MACRO

Отчёт по заключённым договорам

Месторасположение отчёта: Отчёты → Сделки → Отчёт по заключённым договорам

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

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

#Фильтры

  • Тип даты — поле, по которому применяется диапазон дат. По умолчанию: Дата проведения сделки. Доступные значения: Дата регистрации договора, Дата начала сделки, Дата предварительного договора, Дата ДДУ, Дата ДДУ или предварительного договора, Дата подписания договора, Дата проведения сделки, Дата расторжения договора, Дата сдачи в РР план, Планируемая дата сдачи в РР, Фактическая дата получения из РР, Дата окончания брони, Дата начала брони, Дата перевода в платную бронь, Дата платежа, Любая дата.

  • Статус — статус сделки: Бронь, Сделка в работе, Сделка проведена (по умолчанию), Сделка расторгнута.

  • Отдел продаж — отдел менеджера заявки.
  • Менеджер сделки — ответственный менеджер.
  • Сотрудник сопровождения сделки — сотрудник с ролью «Сопровождение» в сделке.
  • Менеджер колл-центра — сотрудник, обрабатывавший первичную заявку.
  • Продавец — продавец объекта.
  • Роли клиентов — тип участника сделки.
  • Теги — теги объекта или заявки.
  • ЖК / Дом — жилой комплекс или конкретный дом.
  • Категория комплекса, Категория объекта — тип недвижимости.
  • Класс квартиры, Количество комнат, Подъезд, Секция — характеристики объекта.
  • Тип планировки — Классическая, Студия или Европланировка. Если значение не выбрано, в отчёт попадают объекты с любым типом планировки.
  • Банк — ипотечный банк.
  • Программа покупки — программа или условия оплаты.
  • Источник денег — источник финансирования.
  • Тип поступлений — тип финансовой операции.
  • Тип договора — вид основного договора (ДДУ, ДКП, уступка и т. д.).
  • Агентство недвижимости, Агрегаторы — посредники в сделке.
  • Способ передачи в РР — метод подачи документов на регистрацию.
  • Отделка — вид отделки объекта.
  • Акции — маркетинговые акции, применённые к сделке. Фильтр показывает только акции, встречающиеся в сделках текущей выборки отчёта.
  • Опции — дополнительные настройки выборки и агрегации; описаны в разделе «Опции» ниже.

#Опции

  • Расширенная статистика по менеджерам — добавляет над сводной таблицей отдельную сводку по менеджерам. Под фильтрами появляется кнопка → XLS Расширенную статистику.

  • Показать UTM (xls) — добавляет UTM-метки заявки в Excel-выгрузку. В HTML-таблице эти столбцы не отображаются.

  • Источник денег и тип поступлений не влияют на выбор — отчёт строится по всем сделкам без учёта одноимённых фильтров, однако финансовые суммы в строках рассчитываются с их учётом. Возможна строка сделки с нулевой или частичной суммой.

  • Платные брони — отображает только платные брони. Применимо исключительно при статусе Бронь и типе даты «Дата окончания брони», «Дата начала брони» или «Дата перевода в платную бронь».

  • Сумма по графику не совпадает с суммой по договору — выводит договора, у которых сумма графика платежей не совпадает со стоимостью по договору. Типичные причины: график не сформирован; график создан до корректировок и не учитывает изменений; задаток не помечен как «не входит в сумму по договору».

  • Стоимость по договору отличается от стоимости по прайсу — показывает договоры, стоимость которых выше или ниже прайсовой стоимости объекта. Договоры с одинаковыми стоимостями исключаются. Сравнивается полная стоимость объекта, а не цена за м². Если прайсовая стоимость отсутствует, запись в выборку не попадает. Опция также применяется к Excel-выгрузке.

    При сравнении учитываются действующие настройки расчёта стоимости договора, в том числе опция «Учитывать дату регистрации доп. соглашения для расчёта стоимости по договору».

  • Искать в том числе по дате расторжения — при одновременном выборе активных и расторгнутых статусов добавляет расторжение как альтернативное условие периода. Влияет на строку «Итого»: проведённые в периоде и расторгнутые позже сделки суммируются; расторгнутые в периоде сделки прошлых периодов вычитаются; сделки, проведённые и расторгнутые в одном периоде, нейтрализуются.

  • Учитывать дату регистрации доп. соглашения для расчёта стоимости по договору — применяет ретроспективную стоимость на конец периода с учётом зарегистрированных ДС. Влияет на столбцы «Стоимость по договору, Р» и «Цена по договору, Р/м²», а также на все связанные с ними площади и суммы, включая Excel-выгрузку.

#Настройки отображения

Нажмите кнопку Шаблон: … под основной таблицей, чтобы открыть панель управления столбцами. На кнопке указано название шаблона, который сейчас применён к отчёту, — например, Шаблон: По умолчанию. В панели доступны включение/выключение столбцов, изменение порядка перетаскиванием, фильтрация по группам и поиск по названию и описанию.

После настройки закройте панель — нажмите на область вне панели или на крестик. Изменения сохранятся в выбранном шаблоне, и отчёт обновится. Excel-выгрузка учитывает выбранные столбцы и опции выгрузки.

Поля сгруппированы по источнику данных:

  • Объект — идентификатор и характеристики объекта: ЖК, город, район/объект, Б/с, категория, номер, комнатность, студия («Да» — у объекта тип планировки «Студия» или «Европланировка», «Нет» — «Классическая»), этаж, класс квартиры, площадь (общая, приведённая, БТИ, полная), прайсовые цены, служебные отметки, подъезд, секция.
  • Сделка — ID сделки, статус, площадь и стоимость по договору, программа покупки, тип и номер договора, ключевые даты, данные покупателя (в т. ч. паспортные данные, ИНН и СНИЛС), продавец и телефон продавца, менеджер сделки, сотрудник сопровождения, уступка, акции, наценки, сумма наценок, сумма скидок, изменение стоимости без корректировки, примечания, теги и другие атрибуты.
  • Ипотека — банк, ставка, срок, сумма ипотеки, первоначальный взнос, комиссия банка.
  • Регистрация — даты сдачи и получения из Росреестра (план/факт), способ передачи, дата и номер регистрации.
  • Финансы — суммы по графику, сумма первого платежа, оплаченные суммы, дебиторская задолженность, суммы текущего месяца, расходы, стоимость уступки, график платежей со статусами.
  • Платное бронирование — дата перевода в платную бронь, сумма, номер и дата договора бронирования.
  • Отделка — вид отделки, цена отделки, стоимость строительства.
  • Посредник — агент, телефон агента, агентство, агрегатор, плановая и фактическая комиссии, комиссия компании.

Как рассчитывается «Сумма первого платежа»

Если в графике платежей сделки есть платежи с отметкой «Входит в 1-й платеж», в столбце отображается сумма всех отмеченных платежей. Их даты могут совпадать или различаться. Платежи без отметки в эту сумму не включаются.

Если ни один платёж не отмечен, отображается сумма первого платежа по графику. Если отмеченные платежи есть, но их общая сумма равна нулю, отображается 0.

Например, в графике три платежа: 100 000, 200 000 и 300 000 ₽. Если второй и третий отмечены как входящие в первый платёж, отчёт покажет 500 000 ₽. Если отметок нет — 100 000 ₽.

Расчёт применяется автоматически и действует также в Excel-выгрузке. Для включения платежей в расчёт установите отметку «Входит в 1-й платеж» у нужных строк графика в сделке и сохраните изменения.

Как стоимость по договору складывается из составляющих

КолонкаЧто учитывается
Стоимость по прайсу до переоценкиСтоимость объекта, сохранённая при оформлении сделки. Если она не задана или равна нулю, используется текущая прайсовая стоимость объекта.
АкцииКорректировки, связанные с акциями: наценки, стоимость отделки и удорожания по рассрочке — со знаком «+»; скидки и снижения стоимости — со знаком «−».
НаценкиДополнительные опции, добавленные инструментом «Наценки».
Сумма наценокНаценки, стоимость отделки и удорожания по рассрочке на втором шаге сделки, не связанные с акциями.
Сумма скидокСкидки и снижения стоимости на втором шаге сделки, не связанные с акциями. Отображаются со знаком «−».
Изменение стоимости без корректировкиРазница между стоимостью по договору и перечисленными составляющими. Отражает, например, изменение стоимости на втором шаге сделки или дополнительным соглашением без отдельной корректировки. Может быть положительной или отрицательной; при отсутствии разницы отображается 0.

При суммировании процентных акций общая скидка учитывается один раз. Зачёркнутые акции, включённые в общую скидку, остаются в детализации с отметкой «Применена», без отдельной суммы. Это правило действует на экране и в выгрузке с детализацией акций.

Формула разложения:

Стоимость по прайсу до переоценки + Акции + Наценки + Сумма наценок + Сумма скидок + Изменение стоимости без корректировки = Стоимость по договору.

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

«Цена отделки» отдельно к формуле не прибавляется: стоимость отделки уже учитывается в соответствующей корректировке.

Колонки «Сумма скидок» и «Изменение стоимости без корректировки» по умолчанию выключены и расположены после «Суммы наценок». Их значения суммируются в строке «Итого» и включаются в выгрузки Excel и CSV, если колонки выбраны для отображения.

#Шаблоны настроек отображения

Шаблон — это сохранённый под своим названием набор столбцов отчёта: какие столбцы включены и в каком порядке они идут. Шаблоны удобны, если вы регулярно выгружаете отчёт в разных форматах — например, один набор полей для сверки с бухгалтерией, другой для обработчика, который загружает Excel в другую систему. Вместо того чтобы каждый раз заново включать и переставлять столбцы, достаточно выбрать нужный шаблон.

Работа с шаблонами идёт в панели настроек отображения. В её верхней части находятся:

  • список шаблонов — в нём выбирается шаблон, который применяется к отчёту;
  • кнопка настройки шаблона — кнопка без подписи справа от списка. Через неё создаётся новый шаблон, меняются название и доступ, удаляется шаблон.

Важно! Как сохраняются столбцы шаблона

Столбцы любого шаблона — и общего, и вашей личной настройки «По умолчанию» — сохраняются только при закрытии панели настроек отображения:

  1. выберите шаблон в списке;
  2. включите, выключите или переставьте столбцы;
  3. закройте панель — нажмите на область вне панели или на крестик.

Кнопка Сохранить в окне изменения шаблона сохраняет только название и доступ, а столбцы — нет.

Если обновить страницу или уйти из отчёта, не закрыв панель, изменения столбцов пропадут.

Что хранит шаблон

  • название;
  • включённые столбцы и их порядок;
  • список сотрудников, у которых есть доступ к шаблону.

Период, фильтры, опции и сортировка в шаблон не входят — их вы задаёте отдельно при каждой работе с отчётом.

«По умолчанию» — ваша личная настройка

В списке шаблонов всегда есть вариант По умолчанию. Это ваша личная настройка столбцов — та, что была в отчёте до появления шаблонов. Её видите только вы, удалить её нельзя. Применение других шаблонов её не меняет: вернуться к ней можно в любой момент, выбрав «По умолчанию» в списке.

#Как создать шаблон
  1. Откройте панель настроек отображения и выберите в списке По умолчанию.
  2. Включите нужные столбцы и расставьте их в нужном порядке.
  3. Нажмите кнопку настройки шаблона справа от списка — откроется окно Создание шаблона.
  4. Укажите Название шаблона.
  5. В поле Доступ выберите сотрудников, которым нужен этот шаблон. Вы сами уже включены в доступ.
  6. Нажмите Сохранить шаблон.

В новый шаблон попадут столбцы в том виде, в каком они были в панели в момент создания. Шаблон сразу применится к отчёту, а на кнопке под таблицей появится его название. Ваша личная настройка «По умолчанию» при этом не меняется.

Важно! Новый шаблон создаётся только из «По умолчанию». Если в списке выбран другой шаблон, кнопка настройки шаблона открывает окно изменения этого шаблона, а не создания нового.

Требования к названию:

  • название обязательно и не длиннее 255 символов;
  • название должно быть уникальным в компании. Большие и маленькие буквы, а также пробелы в начале и конце не учитываются: «Сверка» и « сверка » считаются одинаковыми названиями.

Если название пустое или уже занято, шаблон не сохранится, и система покажет сообщение — например, «Шаблон с таким названием уже существует».

#Как применить шаблон
  1. Откройте панель настроек отображения.
  2. Выберите нужный шаблон в списке.
  3. Закройте панель.

Отчёт перестроится по шаблону: таблица и Excel-выгрузка получат одинаковый состав и порядок столбцов. Выбранный шаблон запоминается — он останется применённым при смене фильтров, повторном формировании отчёта и следующем входе в отчёт.

#Как изменить столбцы в шаблоне
  1. Выберите шаблон в списке.
  2. Включите, выключите или переставьте столбцы.
  3. Закройте панель — только в этот момент изменения сохранятся в выбранном шаблоне, и отчёт обновится.

Так же меняются столбцы и в вашей личной настройке «По умолчанию».

Важно! Шаблон — это одна общая версия для всех, у кого к нему есть доступ, а не личная копия. Если вы измените столбцы в общем шаблоне, изменения увидят все его участники.

Если переключить шаблон, не сохранив изменения. Если вы изменили столбцы и, не закрывая панель, выбираете в списке другой шаблон, появится окно Несохранённые изменения:

  • Сохранить и переключить — сохранить изменения в текущем шаблоне и перейти к выбранному;
  • Переключить без сохранения — перейти к выбранному шаблону, изменения в текущем пропадут;
  • Отмена — остаться в текущем шаблоне и продолжить правку.

Если шаблон одновременно правит коллега. Сохранятся изменения того, кто закрыл панель первым. Второй увидит сообщение «Изменения не сохранены: поля шаблона уже изменены другим пользователем», и в панели загрузится актуальная версия шаблона — свои правки нужно будет повторить.

#Как переименовать шаблон или изменить доступ
  1. Выберите шаблон в списке.
  2. Нажмите кнопку настройки шаблона справа от списка — откроется окно Изменение шаблона.
  3. Измените название или список сотрудников в поле Доступ.
  4. Нажмите Сохранить.

Важно! Кнопка Сохранить в этом окне сохраняет только название и доступ. Если вы перед этим меняли столбцы, они сохранятся только после закрытия панели настроек отображения.

Как устроен доступ

  • Все сотрудники из списка доступа равноправны — у шаблона нет отдельного «владельца». Любой из них может изменить столбцы и название, добавить или убрать других сотрудников, удалить шаблон.
  • Сотрудник, получивший доступ, может сам открыть его следующим коллегам.
  • Если у сотрудника забрали доступ, у тех, кому он успел открыть шаблон, доступ сохраняется.
  • Себя из списка доступа убрать нельзя. Если шаблон вам больше не нужен, попросите коллегу из списка доступа убрать вас.
  • В шаблоне должен остаться хотя бы один сотрудник.
  • Добавить в доступ можно только действующих сотрудников компании. Уволенные сотрудники, которые уже были в списке, в нём остаются.
#Как удалить шаблон
  1. Выберите шаблон в списке.
  2. Нажмите кнопку настройки шаблона справа от списка.
  3. Нажмите Удалить шаблон и подтвердите удаление.

Шаблон удаляется для всех сотрудников, у кого был к нему доступ. Отчёт переключится на вашу настройку «По умолчанию».

Если шаблон, который у вас применён, удалил коллега или у вас забрали к нему доступ, при следующем открытии отчёта он пропадёт из списка, и применится ваша настройка «По умолчанию». Отчёт и выгрузка продолжат работать.

#Новые поля отчёта и шаблоны

Когда в отчёте появляются новые поля, в существующие шаблоны они не добавляются автоматически: в панели настроек они появятся выключенными в конце списка. Так состав и порядок столбцов в выгрузке не изменятся неожиданно — это важно, если Excel обрабатывается автоматически. Чтобы добавить новое поле в шаблон, включите его вручную и закройте панель.

#Паспортные данные покупателя

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

Данные каждого покупателя выводятся отдельной строкой в одной ячейке, части строки — через запятую, в таком порядке:

  1. данные документа: тип, номер, дата выдачи, кем выдан;
  2. ИНН: для физического лица — ИНН физлица, для организации — ИНН организации;
  3. СНИЛС.

Пример ячейки для сделки с тремя покупателями:

Паспорт 4510 123456 01.10.2020 ОВД Тестовый, ИНН: 001234567890, СНИЛС: 001-002-003 04
ИНН: 0098765432
—
  • Значения выводятся в том виде, в каком сохранены в карточке контакта.
  • Незаполненная часть не выводится вместе с подписью.
  • Если у покупателя нет ни документа, ни ИНН, ни СНИЛС, в его строке стоит «—». Если данных нет ни у одного покупателя сделки, в ячейке один «—».
  • Если номер документа заполнен, а тип не выбран, тип выводится как «Паспорт».

#Форматы выгрузки

  • → XLS — выгружает основную таблицу в Excel с выбранными столбцами. Многострочные ячейки (например, «Паспортные данные покупателя» при нескольких покупателях) выгружаются с переносом строк внутри одной ячейки — так же, как на экране.
  • → XLS Расширенную статистику — доступна при включённой опции «Расширенная статистика по менеджерам». Выгружает дополнительный свод по менеджерам.

#Сводная таблица

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

#Цветовая индикация строк

Цветовая заливка строк основной таблицы активируется при одновременном выполнении двух условий: включена опция «Искать в том числе по дате расторжения» И в настройках отображения включён столбец «Статус».

ЦветЧто означаетУчёт в итогах
Без заливкиОбычная активная сделкаСуммируется
ОранжеваяСделка проведена в периоде, расторгнута позже. Статус в столбце: «Сделка проведена и расторгнута»Суммируется
КраснаяСделка прошлых периодов, расторгнутая в отчётном периоде. Статус в столбце: «Сделка расторгнута»Вычитается
СераяСделка проведена и расторгнута в одном периоде. Статус в столбце: «Сделка проведена и расторгнута»Не учитывается

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

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

Основа отчёта — объекты продажи (estate_sells) со связанной сделкой. Связи: estate_deals.estate_sell_id = estate_sells.estate_sell_id → estate_deals.house_id = estate_houses.house_id. Данные покупателя — из contacts через estate_deals.contacts_buy_id. Данные менеджера — из users через estate_deals.deal_manager_id. По умолчанию в UI установлен статус Сделка проведена (deal_status = 150).

Группа «Объект»

SELECT
    sell.estate_sell_id                     AS `ID объекта`,
    h.complex_name                          AS `ЖК`,
    h.geo_city_name                         AS `Город`,
    h.geo_block                             AS `Б/с`,
    sell.estate_sell_category               AS `Категория`,
    sell.geo_flatnum                        AS `№`,
    sell.estate_rooms                       AS `К`,
    IF(COALESCE(sell.estate_studia, 0) > 0, 'Да', 'Нет') AS `Студия`,
    sell.estate_floor                       AS `Этаж`,
    sell.flatClass                          AS `Класс квартиры`,
    sell.estate_area                        AS `Площ., м²`,
    sell.estate_areaBti                     AS `Площ. БТИ, м²`,
    esa.attr_value                          AS `Площ. приведённая, м²`,
    sell.estate_price_m2                    AS `Цена по прайсу, Р/м²`,
    sell.estate_price                       AS `Стоимость по прайсу, Р`,
    sell.special_notes                      AS `Служебные отметки`,
    sell.geo_house_entrance                 AS `Подъезд`
FROM estate_sells sell
INNER JOIN estate_houses h ON h.house_id = sell.house_id
LEFT JOIN estate_sells_attr esa
    ON esa.estate_sell_id = sell.estate_sell_id
    AND esa.attr_name = 'estate_area_reduced';

Поля Район/объект, Площ. полная, м² и Секция (отдельный столбец) — данные не выгружаются в MacroData.

Стоимость по прайсу до переоценки, Р — estate_deals.deal_price, если значение больше 0; иначе используется estate_sells.estate_price связанного объекта.

Группа «Сделка»

SELECT
    ed.deal_id                              AS `ID сделки`,
    ed.deal_status                          AS `Статус`,
    ed.deal_area                            AS `Площ. по договору, м²`,
    ed.deal_sum                             AS `Стоимость по договору, Р`,
    ed.deal_program_name                    AS `Прогр. покупки`,
    ed.agreement_type                       AS `Тип договора`,
    ed.agreement_number                     AS `Номер договора`,
    ed.estate_buy_date_added                AS `Дата добавления заявки`,
    ed.agreement_date                       AS `Дата договора`,
    ed.deal_date_start                      AS `Дата начала сделки`,
    ed.signed_date                          AS `Дата подписания договора`,
    ed.deal_date                            AS `Дата проведения сделки`,
    ed.deal_date_cancelled                  AS `Дата расторжения договора`,
    ed.agreement_owner_date                 AS `Дата подписания акта`,
    ed.preliminary_number                   AS `Предварительный договор (номер)`,
    ed.preliminary_date                     AS `Предварительный договор (дата)`,
    ed.is_concession                        AS `Уступка`,
    ed.seller_contacts_name                 AS `Продавец`,
    c.contacts_buy_name                     AS `Покупатель`,
    c.passport_address                      AS `Паспортные данные: прописка`,
    c.passport_bithplace                    AS `Паспортные данные: место рождения`,
    u.users_name                            AS `Менеджер сделки`
FROM estate_deals ed
LEFT JOIN contacts c ON c.contacts_id = ed.contacts_buy_id
LEFT JOIN users u    ON u.id          = ed.deal_manager_id
WHERE ed.deal_status >= 105;

Коды статусов estate_deals.deal_status: 105 — Бронь, 110 — Сделка в работе, 140 — Сделка расторгнута, 150 — Сделка проведена.

Поля Договор-основание, Покупатель по договору-основанию, Сотрудник сопровождения сделки, Примечания по сделке, Теги, Согласие на отправку уведомлений, Менеджер колл-центра, Вид собственности, Источник денег — данные не выгружаются в MacroData.

Расчёт составляющих стоимости

В CRM корректировки хранятся в estate_shows_discount_markup и связаны со сделкой через estate_shows_id.

  • Акции — записи с заполненным promo_id. Типы increase, restoration, instalment_increase учитываются с плюсом; discount и drop — с минусом. Записи с sum_type = 'source' отображаются как «Применена» и не увеличивают сумму.
  • Сумма наценок — сумма amount для типов increase, restoration, instalment_increase, только при promo_id IS NULL и sum_type IS NULL либо sum_type = 'target'.
  • Сумма скидок — сумма amount со знаком минус для типов discount и drop, с теми же условиями по promo_id и sum_type.
  • Изменение стоимости без корректировки — рассчитанная стоимость договора минус стоимость до переоценки, акции, сумма наценок, сумма скидок и включённые в стоимость договора дополнительные опции. Результат округляется до двух знаков после запятой; разница по модулю меньше 0.005 считается нулевой.

В MacroData корректировки доступны в estate_deals_discounts, дополнительные опции — в estate_deals_addons. Однако поле sum_type не выгружается. Поэтому по этим данным нельзя надёжно исключить слагаемые суммированных акций и полностью воспроизвести разложение стоимости отчёта.

Отбор договоров с отличием от прайса

Опция сравнивает рассчитанную стоимость договора с текущей прайсовой стоимостью объекта. Проверяется любое неравенство — как превышение прайса, так и снижение относительно него. Отсутствующая прайсовая стоимость (NULL) не заменяется нулём.

Базовый пример для MacroData, без корректировки суммы договора по дополнительным соглашениям и без исключения дополнительных платежей:

SELECT
    ed.deal_id AS `ID сделки`,
    ed.deal_sum AS `Стоимость по договору`,
    sell.estate_price AS `Стоимость по прайсу`
FROM estate_deals ed
INNER JOIN estate_sells sell
    ON sell.estate_sell_id = ed.estate_sell_id
WHERE ed.deal_status >= 105
  AND sell.estate_price IS NOT NULL
  AND ed.deal_sum <> sell.estate_price;

Для воспроизведения конкретной выборки отчёта необходимо применить те же фильтры и правила расчёта суммы договора. Если стоимость рассчитывается с учётом даты регистрации дополнительных соглашений, в сравнении используется эта рассчитанная сумма вместо текущего ed.deal_sum.

Группа «Ипотека»

SELECT
    ed.deal_id                              AS `id сделки`,
    ed.ipoteka_bank_name                    AS `Банк`,
    ed.ipoteka_rate                         AS `Ставка, %`,
    ed.bank_agreement_term                  AS `Срок, мес.`,
    ed.bank_first_income                    AS `Первоначальный взнос, Р`,
    ed.bank_commission                      AS `Комиссия банка, Р`
FROM estate_deals ed
WHERE ed.deal_status >= 105;

Поле Сумма ипотеки, Р — данные не выгружаются в MacroData.

Группа «Регистрация»

SELECT
    ed.deal_id                              AS `id сделки`,
    ed.justice_date_send_plan               AS `Дата сдачи в РР план`,
    ed.justice_date_send                    AS `Дата сдачи в РР факт`,
    ed.justice_registration_method          AS `Способ передачи в РР`,
    ed.justice_date                         AS `Дата регистрации в РР`,
    ed.justice_number                       AS `Номер регистрации РР`,
    ed.justice_date_received_plan           AS `Дата получ. из РР план`,
    ed.justice_date_received                AS `Дата получ. из РР факт`
FROM estate_deals ed
WHERE ed.deal_status >= 105;

Группа «Финансы»

Денормализованные финансовые поля из estate_deals:

SELECT
    ed.deal_id                              AS `id сделки`,
    ed.deal_sum_addons                      AS `Сумма доп.платежей, Р`,
    ed.finances_over_deal_sum               AS `Сумма платежей сверх сделки, Р`,
    ed.finances_income_reserved             AS `Всего оплачено, Р`,
    ed.finances_income_date_last            AS `Дата последнего платежа`
FROM estate_deals ed
WHERE ed.deal_status >= 105;

Сумма по графику и сумма первого платежа

Финансовые операции связаны со сделкой через finances.deal_id = estate_deals.deal_id.

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

WITH payment_rows AS (
    SELECT
        f.deal_id,
        f.id,
        f.is_first_payment,
        CASE
            WHEN acc.account_id IS NOT NULL THEN f.summa
            ELSE -f.summa
        END AS payment_sum,
        CASE
            WHEN f.status = 1 THEN f.date_added
            ELSE f.date_to
        END AS payment_date
    FROM finances f
    INNER JOIN estate_deals ed
        ON ed.deal_id = f.deal_id
    INNER JOIN finances_types ft
        ON ft.id = f.types_id
    LEFT JOIN finances_accounts acc
        ON acc.account_id = f.account_in_id
    WHERE f.status IN (1, 3)
      AND f.estate_sell_id > 0
      AND ed.deal_status IN (105, 110, 140, 150)
      AND COALESCE(ft.is_deal_expenses, 0) = 0
      AND (
          ft.is_income = 1
          OR ft.is_income_lower = 1
          OR ft.is_other_income = 1
          OR ft.is_commission = 1
          OR ft.is_arles = 1
      )
),
ordered_payments AS (
    SELECT
        payment_rows.*,
        ROW_NUMBER() OVER (
            PARTITION BY deal_id
            ORDER BY payment_date, id
        ) AS payment_number
    FROM payment_rows
)
SELECT
    deal_id AS `id сделки`,
    SUM(payment_sum) AS `Сумма по графику, Р`,
    CASE
        WHEN MAX(
            CASE WHEN is_first_payment > 0 THEN 1 ELSE 0 END
        ) = 1
        THEN SUM(
            CASE
                WHEN is_first_payment > 0
                THEN COALESCE(payment_sum, 0)
                ELSE 0
            END
        )
        ELSE MAX(
            CASE
                WHEN payment_number = 1
                THEN COALESCE(payment_sum, 0)
            END
        )
    END AS `Сумма первого платежа, Р`
FROM ordered_payments
GROUP BY deal_id;

Для сверки с конкретной выборкой отчёта необходимо применить те же фильтры. «Источник денег», «Тип поступлений» и опция исключения источника «Завышение» ограничивают состав финансовых операций до расчёта суммы и определения первого платежа.

Наличие отмеченных платежей проверяется отдельно от их суммы: нулевая сумма отмеченных платежей не должна заменяться суммой первой операции.

Поля Стоимость уступки, Р, Сумма расходов на сделку, Р, Комментарии к расходам, Дебиторская задолженность, Р, Оплачено в текущем месяце, Р, К оплате в текущем месяце, Р, Всего к оплате, Р и График платежей по сделке (статус платежей) — данные не выгружаются в MacroData.

Группа «Платное бронирование»

SELECT
    ed.deal_id                              AS `id сделки`,
    ed.arles_agreement_num                  AS `Номер договора бронирования`,
    ed.arles_agreement_date                 AS `Дата договора бронирования`,
    MIN(essl.log_date)                      AS `Дата перевода в платную бронь`
FROM estate_deals ed
LEFT JOIN estate_sells_statuses_log essl
    ON essl.deal_id = ed.deal_id
    AND essl.is_payed_reserve = 1
WHERE ed.is_payed_reserve = 1
GROUP BY ed.deal_id, ed.arles_agreement_num, ed.arles_agreement_date;

Поле Сумма платного бронирования, Р — данные не выгружаются в MacroData.

Группа «Отделка»

SELECT
    sell.estate_sell_id                     AS `id объекта`,
    er.name                                 AS `Вид отделки`,
    sell.estate_restoration_price           AS `Цена отделки`
FROM estate_sells sell
LEFT JOIN estate_restoration er ON er.id = sell.estate_restoration_id;

Поле Стоимость строительства — данные не выгружаются в MacroData.

Группа «Посредник»

SELECT
    ed.deal_id                              AS `id сделки`,
    ed.agent_name                           AS `Агент`,
    ed.agency_name                          AS `Агентство недвижимости`,
    ed.deal_mediator_comission              AS `Комиссия посредника план, Р`
FROM estate_deals ed
WHERE ed.has_agent = 1;

Поля Комиссия посредника факт, Р, Комиссия компании, Р и Агрегатор — данные не выгружаются в MacroData.

Телефоны продавца и агента

Телефоны берутся из карточек контактов. В отчёте CRM выводится первый номер:

  • продавец — контакт, указанный в estate_deals.seller_contacts_id;
  • агент — контакт, указанный в estate_deals.contacts_mediator_id. Если контакт имеет тип «Организация», телефон агента не выводится.

В MacroData телефоны доступны в поле contacts.contacts_buy_phones. Несмотря на название поля, оно содержит телефоны соответствующего контакта — в том числе продавца или агента.

Пример получения исходных телефонных данных:

SELECT
    ed.deal_id AS `ID сделки`,
    ed.seller_contacts_name AS `Продавец`,
    seller.contacts_buy_phones AS `Телефоны продавца`,
    ed.agent_name AS `Агент`,
    CASE
        WHEN agent.contacts_buy_type = 1 THEN NULL
        ELSE agent.contacts_buy_phones
    END AS `Телефоны агента`
FROM estate_deals ed
LEFT JOIN contacts seller
    ON seller.contacts_id = ed.seller_contacts_id
LEFT JOIN contacts agent
    ON agent.contacts_id = ed.contacts_mediator_id
WHERE ed.deal_status >= 105;

Запрос возвращает исходное поле телефонов, которое может содержать несколько номеров. Для воспроизведения колонок отчёта нужно выбрать первый номер и применить форматирование телефона.

Доступность полных номеров в MacroData зависит от настройки передачи персональных данных: номера могут быть замаскированы.

Сводная таблица

Агрегат по ЖК. Логика знаков +1/-1 при опции «Искать по дате расторжения» воспроизводится отчётным кодом CRM и не повторяется простым запросом.

SELECT
    h.complex_name                          AS `Жилой комплекс`,
    COUNT(ed.deal_id)                       AS `Договоров`,
    SUM(ed.has_agent)                       AS `Договоров с посредниками`,
    SUM(ed.deal_sum)                        AS `Сумма по договорам, Р`,
    SUM(ed.deal_area)                       AS `Площадь по договору, м²`,
    SUM(ed.finances_income_reserved)        AS `Всего оплачено, Р`
FROM estate_deals ed
INNER JOIN estate_sells sell ON sell.estate_sell_id = ed.estate_sell_id
INNER JOIN estate_houses h   ON h.house_id          = sell.house_id
WHERE ed.deal_status >= 105
GROUP BY h.complex_name;

Колонки Сумма по графикам, Р, Сальдо оборотов за период, Р, Дебиторская задолженность, Р и Оплачено в текущем месяце, Р — данные не выгружаются в MacroData.

Итоговый запрос

Полный запрос для доступных данных в MacroData. Группы с пометкой «Данные не выгружаются в MacroData» в запрос не включены.

SELECT
    h.complex_name                          AS `ЖК`,
    h.geo_city_name                         AS `Город`,
    h.geo_block                             AS `Б/с`,
    sell.estate_sell_id                     AS `ID объекта`,
    sell.estate_sell_category               AS `Категория`,
    sell.geo_flatnum                        AS `№`,
    sell.estate_rooms                       AS `К`,
    IF(COALESCE(sell.estate_studia, 0) > 0, 'Да', 'Нет') AS `Студия`,
    sell.estate_floor                       AS `Этаж`,
    sell.flatClass                          AS `Класс квартиры`,
    sell.estate_area                        AS `Площ., м²`,
    sell.estate_areaBti                     AS `Площ. БТИ, м²`,
    esa.attr_value                          AS `Площ. приведённая, м²`,
    sell.estate_price_m2                    AS `Цена по прайсу, Р/м²`,
    sell.estate_price                       AS `Стоимость по прайсу, Р`,
    sell.special_notes                      AS `Служебные отметки`,
    sell.geo_house_entrance                 AS `Подъезд`,
    ed.deal_id                              AS `ID сделки`,
    ed.deal_status                          AS `Статус`,
    ed.deal_area                            AS `Площ. по договору, м²`,
    ed.deal_sum                             AS `Стоимость по договору, Р`,
    ed.deal_program_name                    AS `Прогр. покупки`,
    ed.agreement_type                       AS `Тип договора`,
    ed.agreement_number                     AS `Номер договора`,
    ed.estate_buy_date_added                AS `Дата добавления заявки`,
    ed.agreement_date                       AS `Дата договора`,
    ed.deal_date_start                      AS `Дата начала сделки`,
    ed.signed_date                          AS `Дата подписания договора`,
    ed.deal_date                            AS `Дата проведения сделки`,
    ed.deal_date_cancelled                  AS `Дата расторжения`,
    ed.agreement_owner_date                 AS `Дата подписания акта`,
    ed.preliminary_number                   AS `Предварительный договор (номер)`,
    ed.preliminary_date                     AS `Предварительный договор (дата)`,
    ed.is_concession                        AS `Уступка`,
    ed.seller_contacts_name                 AS `Продавец`,
    c.contacts_buy_name                     AS `Покупатель`,
    c.contacts_buy_phones                   AS `Телефон покупателя`,
    c.contacts_buy_emails                   AS `Email покупателя`,
    c.passport_address                      AS `Паспортные данные: прописка`,
    u.users_name                            AS `Менеджер сделки`,
    ed.ipoteka_bank_name                    AS `Банк`,
    ed.ipoteka_rate                         AS `Ставка, %`,
    ed.bank_agreement_term                  AS `Срок, мес.`,
    ed.bank_first_income                    AS `Первоначальный взнос, Р`,
    ed.bank_commission                      AS `Комиссия банка, Р`,
    ed.justice_date_send_plan               AS `Дата сдачи в РР план`,
    ed.justice_date_send                    AS `Дата сдачи в РР факт`,
    ed.justice_registration_method          AS `Способ передачи в РР`,
    ed.justice_date                         AS `Дата регистрации в РР`,
    ed.justice_number                       AS `Номер регистрации РР`,
    ed.justice_date_received_plan           AS `Дата получ. из РР план`,
    ed.justice_date_received                AS `Дата получ. из РР факт`,
    ed.deal_sum_addons                      AS `Сумма доп.платежей, Р`,
    ed.finances_over_deal_sum               AS `Сумма платежей сверх сделки, Р`,
    ed.finances_income_reserved             AS `Всего оплачено, Р`,
    ed.finances_income_date_last            AS `Дата последнего платежа`,
    ed.arles_agreement_num                  AS `Номер договора бронирования`,
    ed.arles_agreement_date                 AS `Дата договора бронирования`,
    er.name                                 AS `Вид отделки`,
    sell.estate_restoration_price           AS `Цена отделки`,
    ed.agent_name                           AS `Агент`,
    ed.agency_name                          AS `Агентство недвижимости`,
    ed.deal_mediator_comission              AS `Комиссия посредника план, Р`
FROM estate_deals ed
INNER JOIN estate_sells sell ON sell.estate_sell_id  = ed.estate_sell_id
INNER JOIN estate_houses h   ON h.house_id           = sell.house_id
LEFT JOIN  contacts c        ON c.contacts_id        = ed.contacts_buy_id
LEFT JOIN  users u           ON u.id                 = ed.deal_manager_id
LEFT JOIN  estate_restoration er ON er.id            = sell.estate_restoration_id
LEFT JOIN  estate_sells_attr esa
    ON esa.estate_sell_id = sell.estate_sell_id
    AND esa.attr_name = 'estate_area_reduced'
WHERE ed.deal_status >= 105
ORDER BY h.complex_name, ed.deal_date DESC;