Отчёт по ипотечным платежам

Месторасположение: Отчёты → Финансы → Отчёт по ипотечным платежам

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

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

#Фильтры

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

Тип даты и период (с — по) — в выпадающем списке выберите, по какой дате отбирать данные. По умолчанию выбрана «Дата регистрации договора».

• Дата регистрации договора;
• Дата начала сделки;
• Дата ДДУ;
• Дата сдачи в РР;
• Планируемая дата сдачи в РР;
• Дата проведения сделки;
• Дата подписания договора;
• Дата платежа.

Все даты, кроме «Даты платежа», относятся к сделке. Если дата сделки входит в период, в отчёт попадают все её ипотечные платежи, независимо от дат самих платежей. «Дата платежа» отбирает сами платежи по их дате.

Можно заполнить только одну границу периода. Если «с» не заполнено, отчёт строится с 01.04.2000. Если не заполнено «по», отчёт строится по дату на год вперёд от сегодняшней. Итоговый период выводится в заголовке отчёта.

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

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

В отчёт попадают финансовые операции с источником денег «Ипотека», если они относятся к текущей сделке объекта в статусе Сделка в работе или Сделка проведена. Тип операции не учитывается. Платежи по расторгнутым и отменённым сделкам в отчёт не попадают.

Строки отсортированы по доле банка, по убыванию.

(1) Банк — ипотечный банк, указанный в сделке. В столбце выводится любой контакт из этого поля, поэтому здесь могут оказаться организации, которые не являются банками. Строки формируются по контакту, а не по названию. Если в системе есть два контакта с одинаковым названием, например два контакта «ВТБ», они выводятся отдельными строками.

(2) Сделок — количество сделок банка, у которых есть хотя бы один ипотечный платёж за выбранный период. Учитываются и сделки, все платежи которых отложены или отклонены.

(3) Проведено, ₽ — сумма ипотечных платежей в статусе Проведено.

(4) К оплате, ₽ — сумма ипотечных платежей в статусе К оплате. Сюда входят как просроченные, так и будущие платежи.

(5) Итого, ₽ — сумма столбцов «Проведено» и «К оплате». Это не сумма кредита и не сумма сделки.

(6) Доля, % — доля банка в общей сумме «Итого» по всем банкам, с точностью до сотых.

Суммы выводятся в целых рублях, копейки отбрасываются. Суммы в столбцах «Проведено», «К оплате» и «Итого» — ссылки. По клику в новой вкладке открывается список «Финансы» с операциями этого банка в соответствующем статусе.

Итого — сумма по всем банкам в столбцах «Сделок», «Проведено», «К оплате» и «Итого». Доля — 100.

Банк не указан — платежи по сделкам, в которых не указан ипотечный банк. Строка выводится после «Итого» и в него не входит, поэтому доля для неё не рассчитывается.

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

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

Финансовая операция (finances) попадает в отчёт, если выполнены все условия:

• источник денег — ипотека: finances.money_source = 'mortgage';
• операция не удалена: finances.status > 0. В MacroData удалённые операции не выгружаются;
• операция относится к текущей сделке объекта: finances.estate_sell_id = estate_sells.estate_sell_id и finances.deal_id = estate_sells.deal_id;
• статус сделки estate_deals.deal_status — 110 (Сделка в работе) или 150 (Сделка проведена).

Тип операции (finances.types_id) и направление (приход или расход) не проверяются. Статус и категория объекта, а также дом не проверяются.

Коды статусов финансовой операции:

Поля дат для фильтра по периоду:

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

Банк и платежи — (1) Банк, (2) Сделок, (3) Проведено, (4) К оплате, (5) Итого, строка «Банк не указан»

SELECT
    IFNULL(NULLIF(d.ipoteka_bank_name, ''), 'Банк не указан')  AS `Банк`,
    COUNT(DISTINCT f.deal_id)                                   AS `Сделок`,
    FLOOR(SUM(IF(f.status = 1, f.summa, 0)))                    AS `Проведено, ₽`,
    FLOOR(SUM(IF(f.status = 3, f.summa, 0)))                    AS `К оплате, ₽`,
    FLOOR(SUM(IF(f.status IN (1, 3), f.summa, 0)))              AS `Итого, ₽`
FROM finances f
INNER JOIN estate_sells s
    ON s.estate_sell_id = f.estate_sell_id
   AND s.deal_id = f.deal_id
INNER JOIN estate_deals d ON d.deal_id = f.deal_id
WHERE f.money_source = 'mortgage'
  AND f.status > 0
  AND d.deal_status IN (110, 150)
  AND d.justice_date BETWEEN DATE('{{start_date}}') AND DATE('{{end_date}}')
GROUP BY `Банк`
ORDER BY `Итого, ₽` DESC;

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

• (5) Итого = (3) Проведено + (4) К оплате.
• (6) Доля, % = ROUND(Итого банка / Итого по всем банкам × 100, 2). В сумму по всем банкам строка «Банк не указан» не входит.
• Строка «Итого» — суммы столбцов (2)–(5) по всем банкам, кроме «Банк не указан». Доля = 100.
• Суммы округляются вниз до целого рубля.

SELECT
    b.`Банк`,
    b.`Итого, ₽`,
    ROUND(b.`Итого, ₽` * 100 / t.total_all, 2)                  AS `Доля, %`
FROM (
    SELECT
        d.ipoteka_bank_name                                     AS `Банк`,
        FLOOR(SUM(IF(f.status IN (1, 3), f.summa, 0)))          AS `Итого, ₽`
    FROM finances f
    INNER JOIN estate_sells s
        ON s.estate_sell_id = f.estate_sell_id
       AND s.deal_id = f.deal_id
    INNER JOIN estate_deals d ON d.deal_id = f.deal_id
    WHERE f.money_source = 'mortgage'
      AND f.status > 0
      AND d.deal_status IN (110, 150)
      AND d.ipoteka_bank_name IS NOT NULL
      AND d.ipoteka_bank_name <> ''
      AND d.justice_date BETWEEN DATE('{{start_date}}') AND DATE('{{end_date}}')
    GROUP BY d.ipoteka_bank_name
) b
CROSS JOIN (
    SELECT
        FLOOR(SUM(IF(f.status IN (1, 3), f.summa, 0)))          AS total_all
    FROM finances f
    INNER JOIN estate_sells s
        ON s.estate_sell_id = f.estate_sell_id
       AND s.deal_id = f.deal_id
    INNER JOIN estate_deals d ON d.deal_id = f.deal_id
    WHERE f.money_source = 'mortgage'
      AND f.status > 0
      AND d.deal_status IN (110, 150)
      AND d.ipoteka_bank_name IS NOT NULL
      AND d.ipoteka_bank_name <> ''
      AND d.justice_date BETWEEN DATE('{{start_date}}') AND DATE('{{end_date}}')
) t
ORDER BY `Доля, %` DESC;