Domify

Напредни извештаи од базата (SQL и складирани процедури)

1. Извештај за процент на изнајмени недвижности во однос на објавени огласи за секоја година.

WITH total_listings AS (
    SELECT 
        EXTRACT(YEAR FROM available_from) AS year,
        COUNT(*) AS listing_count
    FROM Listing
    GROUP BY year
),
converted_listings AS (
    SELECT 
        EXTRACT(YEAR FROM l.start_date) AS year,
        COUNT(DISTINCT li.id) AS converted
    FROM Lease l
    JOIN Listing li ON l.listing_id = li.id
    GROUP BY year
)
SELECT 
    tl.year,
    tl.listing_count,
    COALESCE(cl.converted, 0) AS converted,
    ROUND(100.0 * COALESCE(cl.converted, 0) / tl.listing_count, 2) AS conversion_rate
FROM total_listings tl
LEFT JOIN converted_listings cl ON tl.year = cl.year
ORDER BY tl.year;

2. Извештај за раст на приходи (извршени успешни плаќања за изнајмувања) за претходна и тековна година за секој град изразена во проценти.

WITH yearly_income AS (
    SELECT 
        a.city,
        EXTRACT(YEAR FROM p.payment_date) AS year,
        SUM(p.amount) AS total_revenue
    FROM Payment p
    JOIN Lease l ON l.id = p.lease_id
    JOIN Listing li ON li.id = l.listing_id
    JOIN Unit u ON li.unit_id = u.id
    JOIN Property pr ON u.property_id = pr.id
    JOIN Address a ON pr.address_id = a.id
    WHERE p.status = 'завршено'
    GROUP BY a.city, year
),
diffs AS (
    SELECT 
        curr.city,
        curr.total_revenue AS this_year,
        prev.total_revenue AS last_year,
        ROUND(((curr.total_revenue - prev.total_revenue) / NULLIF(prev.total_revenue, 0)) * 100, 2) AS growth_percent
    FROM yearly_income curr
    JOIN yearly_income prev ON prev.city = curr.city AND curr.year = prev.year + 1
    WHERE curr.year = EXTRACT(YEAR FROM CURRENT_DATE)
)
SELECT * 
FROM diffs
ORDER BY growth_percent DESC;

3. Детален месечен извештај за приходите, наплатата, останатиот долг, ненаплатените и активните изнајмувања и нерешените сервисни барања на изнајмувачите

WITH month_period AS (
    SELECT 
        DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month' AS month_start,
        (DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month') + INTERVAL '1 month - 1 day' AS month_end
),
landlords AS (
    SELECT lp.id AS landlord_id, u.first_name || ' ' || u.last_name AS landlord_name
    FROM domify.LandlordProfile lp
    JOIN domify.UserD u ON lp.id = u.id
),
total_due_cte AS (
    SELECT 
        l.landlord_id,
        COALESCE(SUM(l.rent_amount), 0) AS total_due
    FROM domify.Lease l
    CROSS JOIN month_period mp
    WHERE l.start_date <= mp.month_end
      AND l.end_date >= mp.month_start
    GROUP BY l.landlord_id
),
total_paid_cte AS (
    SELECT 
        l.landlord_id,
        COALESCE(SUM(p.amount), 0) AS total_paid
    FROM domify.Payment p
    JOIN domify.Lease l ON p.lease_id = l.id
    CROSS JOIN month_period mp
    WHERE p.payment_date BETWEEN mp.month_start AND mp.month_end
      AND p.status IN ('completed','paid')
    GROUP BY l.landlord_id
),
late_count_cte AS (
    SELECT 
        l.landlord_id,
        COUNT(*) AS late_count
    FROM domify.Lease l
    CROSS JOIN month_period mp
    WHERE l.start_date <= mp.month_end
      AND l.end_date >= mp.month_start
      AND NOT EXISTS (
          SELECT 1 
          FROM domify.Payment p
          WHERE p.lease_id = l.id
            AND p.payment_date BETWEEN mp.month_start AND mp.month_end
            AND p.status IN ('completed','paid')
      )
    GROUP BY l.landlord_id
),
active_leases_cte AS (
    SELECT 
        l.landlord_id,
        COUNT(*) AS active_leases
    FROM domify.Lease l
    WHERE l.start_date <= CURRENT_DATE
      AND l.end_date >= CURRENT_DATE
    GROUP BY l.landlord_id
),
pending_services_cte AS (
    SELECT 
        l.landlord_id,
        COUNT(*) AS pending_services
    FROM domify.ServiceRequest sr
    JOIN domify.Lease l ON sr.lease_id = l.id
    WHERE sr.status IN ('pending','in_progress')
    GROUP BY l.landlord_id
)
SELECT 
    ld.landlord_id,
    ld.landlord_name,
    td.total_due,
    tp.total_paid,
    (td.total_due - tp.total_paid) AS remaining_debt, 
    lc.late_count,
    al.active_leases,
    ps.pending_services
FROM landlords ld
CROSS JOIN month_period mp
LEFT JOIN total_due_cte td ON ld.landlord_id = td.landlord_id
LEFT JOIN total_paid_cte tp ON ld.landlord_id = tp.landlord_id
LEFT JOIN late_count_cte lc ON ld.landlord_id = lc.landlord_id
LEFT JOIN active_leases_cte al ON ld.landlord_id = al.landlord_id
LEFT JOIN pending_services_cte ps ON ld.landlord_id = ps.landlord_id
ORDER BY ld.landlord_name;

4. Детален извештај за историјата на плаќања, задоцнети и тековни обврски, просечна кирија и оценка на платежната дисциплина на изнајмувачите

WITH TenantPaymentStats AS (
    SELECT 
        t.id as tenant_id,
        CONCAT(u.first_name, ' ', u.last_name) as tenant_name,
        u.rating as tenant_rating,
        COUNT(DISTINCT le.id) as total_leases,
        COUNT(DISTINCT pay.id) as total_payments,
        SUM(CASE WHEN pay.status = 'завршено' THEN pay.amount ELSE 0 END) as total_paid,
        SUM(CASE WHEN pay.status = 'доцнење' THEN pay.amount ELSE 0 END) as late_payments_amount,
        COUNT(CASE WHEN pay.status = 'доцнење' THEN 1 END) as late_payment_count,
        COUNT(CASE WHEN pay.status = 'во тек' THEN 1 END) as pending_payment_count,
        AVG(le.rent_amount) as avg_rent_amount,
        MIN(le.start_date) as first_lease_date,
        MAX(le.end_date) as latest_lease_end
    FROM TenantProfile t
    JOIN UserD u ON t.id = u.id
    LEFT JOIN Lease le ON t.id = le.tenant_id
    LEFT JOIN Payment pay ON le.id = pay.lease_id
    GROUP BY t.id, u.first_name, u.last_name, u.rating
)
SELECT 
    tenant_id,
    tenant_name,
    tenant_rating,
    total_leases,
    total_payments,
    total_paid,
    late_payments_amount,
    late_payment_count,
    pending_payment_count,
    ROUND((late_payment_count * 100.0 / NULLIF(total_payments, 0)), 2) as late_payment_percentage,
    avg_rent_amount,
    first_lease_date,
    latest_lease_end,
    CASE 
        WHEN late_payment_count = 0 THEN 'Одличен'
        WHEN late_payment_count <= 2 THEN 'Добар' 
        WHEN late_payment_count <= 5 THEN 'Среден'
        ELSE 'Ризичен'
    END as payment_reliability_score
FROM TenantPaymentStats
WHERE total_leases > 0
ORDER BY late_payment_percentage ASC, total_paid DESC;

5. Детален извештај за сервисни барања, стапка на завршување и просечно време на решавање по општина и град

SET search_path TO domify, public;

WITH MaintenanceAnalysis AS (
    SELECT 
        a.municipality,
        a.city,
        sc.name as service_category,
        COUNT(sr.id) as total_requests,
        COUNT(CASE WHEN sr.status = 'завршено' THEN 1 END) as completed_requests,
        COUNT(CASE WHEN sr.status = 'во тек' THEN 1 END) as in_progress_requests,
        COUNT(CASE WHEN sr.status = 'во очекување' THEN 1 END) as pending_requests,
        AVG(CASE 
            WHEN sr.status = 'завршено' AND ml.maintenance_date IS NOT NULL 
            THEN (ml.maintenance_date::date - sr.request_date::date)
        END) as avg_resolution_days
    FROM ServiceRequest sr
    JOIN ServiceCategory sc ON sr.service_category_id = sc.id
    JOIN Lease le ON sr.lease_id = le.id
    JOIN Listing l ON le.listing_id = l.id
    JOIN Unit u ON l.unit_id = u.id
    JOIN Property p ON u.property_id = p.id
    JOIN Address a ON p.address_id = a.id
    LEFT JOIN MaintenanceLog ml ON sr.id = ml.service_request_id
    GROUP BY a.municipality, a.city, sc.name
)
SELECT 
    municipality,
    city,
    service_category,
    total_requests,
    completed_requests,
    in_progress_requests,
    pending_requests,
    ROUND((completed_requests * 100.0 / NULLIF(total_requests, 0)), 2) as completion_rate_percent,
    ROUND(avg_resolution_days, 1) as avg_resolution_days,
    CASE 
        WHEN avg_resolution_days <= 2 THEN 'Брзо'
        WHEN avg_resolution_days <= 5 THEN 'Средно'
        WHEN avg_resolution_days <= 10 THEN 'Бавно'
        ELSE 'Многу бавно'
    END as response_time_rating
FROM MaintenanceAnalysis
ORDER BY municipality, total_requests DESC;

6.Анализа на пазарни трендови и изнајмувачка понуда по општина, град и тип на недвижност

SET search_path TO domify, public;

WITH MarketTrends AS (
    SELECT 
        a.municipality,
        a.city,
        pt.name as property_type,
        COUNT(DISTINCT l.id) as total_listings,
        COUNT(DISTINCT CASE WHEN l.status = 'available' THEN l.id END) as available_listings,
        AVG(u.rent_amount) as avg_rent_asking,
        AVG(le.rent_amount) as avg_rent_actual,
        AVG(u.area_sq_m) as avg_unit_size,
        AVG(u.rent_amount / u.area_sq_m) as avg_rent_per_sq_m,
        MIN(u.rent_amount) as min_rent,
        MAX(u.rent_amount) as max_rent,
        COUNT(DISTINCT in_list.tenant_profile_id) as total_interested_tenants
    FROM Address a
    JOIN Property p ON a.id = p.address_id
    JOIN PropertyType pt ON p.property_type_id = pt.id
    JOIN Unit u ON p.id = u.property_id
    JOIN Listing l ON u.id = l.unit_id
    LEFT JOIN Lease le ON l.id = le.listing_id
    LEFT JOIN Interested in_list ON l.id = in_list.listing_id
    GROUP BY a.municipality, a.city, pt.name
)
SELECT 
    municipality,
    city,
    property_type,
    total_listings,
    available_listings,
    ROUND((available_listings * 100.0 / NULLIF(total_listings, 0)), 2) as availability_rate,
    ROUND(avg_rent_asking, 2) as avg_rent_asking,
    ROUND(avg_rent_actual, 2) as avg_rent_actual,
    ROUND((avg_rent_actual - avg_rent_asking), 2) as rent_variance,
    ROUND(avg_unit_size, 1) as avg_unit_size_sq_m,
    ROUND(avg_rent_per_sq_m, 2) as avg_rent_per_sq_m,
    min_rent,
    max_rent,
    total_interested_tenants,
    ROUND(total_interested_tenants / NULLIF(available_listings, 0), 2) as interest_ratio,
    CASE 
        WHEN ROUND((available_listings * 100.0 / NULLIF(total_listings, 0)), 2) > 75 THEN 'Висока понуда'
        WHEN ROUND((available_listings * 100.0 / NULLIF(total_listings, 0)), 2) > 50 THEN 'Умерена понуда'
        WHEN ROUND((available_listings * 100.0 / NULLIF(total_listings, 0)), 2) > 25 THEN 'Ограничена понуда'
        ELSE 'Ниска Понуда'
    END as market_availability_status
FROM MarketTrends
WHERE total_listings > 0
ORDER BY municipality, avg_rent_per_sq_m DESC;

Историја

• Верзија1