Changes between Version 6 and Version 7 of AdvancedReports

Timestamp:

08/15/25 23:10:44 (2 months ago)

Author:

221071

Comment:

--

AdvancedReports

@@ -4 +4 @@
 
 
-=== 1. Извештај за вкупни приходи на изнајмувачите на недвижности за нивните изнајмени единици како и просечен приход остварен по изнајмена единица во тековната година.
-{{{ 
-SELECT 
-    u.first_name,
-    u.last_name,
-    u.email,
-    u.rating,
-    lp.is_agent,
-    COUNT(DISTINCT l.id) AS lease_count,
-    COUNT(DISTINCT l.tenant_id) AS tenant_count,
-    COALESCE(SUM(p.amount), 0) AS total_revenue,
-    ROUND(
-        CASE WHEN COUNT(DISTINCT l.id) > 0 
-             THEN COALESCE(SUM(p.amount), 0) / COUNT(DISTINCT l.id)
-             ELSE 0
-        END, 2
-    ) AS avg_revenue_per_lease
-FROM LandlordProfile lp
-JOIN UserD u ON u.id = lp.id
-JOIN Lease l ON lp.id = l.landlord_id
-LEFT JOIN Payment p 
-    ON p.lease_id = l.id 
-    AND p.payment_date >= date_trunc('year', CURRENT_DATE)
-GROUP BY u.id, u.first_name, u.last_name, u.email, u.rating, lp.is_agent
-ORDER BY total_revenue DESC;
-}}}
-
-
-=== 2. Извештај за перформансите на недвижностите по градови: број на имоти, број на издавачки единици, активни единици, остварени приходи за тековната година по град како и просечна кирија по изнајмувачка единица како и остварен приход по објект.
-
-{{{
-SELECT 
-    a.city,
-    COUNT(DISTINCT p.id) as total_properties,
-    COUNT(DISTINCT u.id) as total_units,
-    COUNT(DISTINCT l.id) as active_leases,
-    SUM(pay.amount) as total_revenue,
-    ROUND(AVG(l.rent_amount),2) as avg_rent_per_lease,
-    ROUND (SUM(pay.amount) / COUNT(DISTINCT p.id),2) as revenue_per_property
-FROM Address a
-JOIN Property p ON a.id = p.address_id
-JOIN Unit u ON p.id = u.property_id
-JOIN Listing lst ON u.id = lst.unit_id
-JOIN Lease l ON lst.id = l.listing_id
-JOIN Payment pay ON l.id = pay.lease_id
-WHERE EXTRACT(YEAR FROM pay.payment_date) = EXTRACT(YEAR FROM CURRENT_DATE)
-    AND pay.status = 'завршено'
-GROUP BY a.city
-ORDER BY total_revenue DESC;
-}}}
-
-=== 3. Извештај за процент на изнајмени недвижности во однос на објавени огласи за секоја година.
+=== 1. Извештај за процент на изнајмени недвижности во однос на објавени огласи за секоја година.
 {{{
 WITH total_listings AS (
@@ -83 +32 @@
 
 
-=== 4. Извештај за раст на приходи (извршени успешни плаќања за изнајмувања) за претходна и тековна година  за секој град изразена во проценти.
+=== 2. Извештај за раст на приходи (извршени успешни плаќања за изнајмувања) за претходна и тековна година  за секој град изразена во проценти.
 {{{
 
@@ -116 +65 @@
 }}}
 
-=== 5. Извештај за цени за изнајмување на недвижности (просечна, минимална, максимална и цена по метар квадратен) согласно категорија на објект, град, број на соби, големина на објект.
-{{{
-
-SELECT 
-    pt.name AS property_type,
-    a.city,
-    u.bedrooms,
+=== 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 u.area_sq_m < 50 THEN 'Мала (<50м²)'
-        WHEN u.area_sq_m < 80 THEN 'Средна (50-80м²)'
-        ELSE 'Голема (>80м²)'
-    END AS size_category,
-    COUNT(l.id) AS lease_count,
-    ROUND(AVG(l.rent_amount), 2) AS avg_rent,
-    ROUND(MIN(l.rent_amount), 2) AS min_rent,
-    ROUND(MAX(l.rent_amount), 2) AS max_rent,
-    ROUND(AVG(l.rent_amount / u.area_sq_m), 2) AS price_per_sqm
-FROM Lease l
-JOIN Listing li ON l.listing_id = li.id
-JOIN Unit u ON li.unit_id = u.id
-JOIN Property p ON u.property_id = p.id
-JOIN PropertyType pt ON p.property_type_id = pt.id
-JOIN Address a ON p.address_id = a.id
-WHERE l.start_date >= CURRENT_DATE - INTERVAL '12 months'
-GROUP BY pt.name, a.city, u.bedrooms, size_category
-HAVING COUNT(l.id) >= 1
-ORDER BY a.city, pt.name, u.bedrooms;
-}}}
-
-=== 6. Извештај за последната година за тоа колку просечно денови во секој месец огласите биле изложени пред да биде склучен договор за нив.
-{{{
-SELECT 
-    EXTRACT(YEAR FROM li.available_from) AS year, 
-    EXTRACT(MONTH FROM li.available_from) AS month,
-    COUNT(*) AS total_listings,
-    AVG(l.start_date - li.available_from) AS avg_days_on_market
-FROM Listing li 
-JOIN Lease l ON li.id = l.listing_id 
-WHERE li.available_from >= CURRENT_DATE - INTERVAL '365 days' AND l.start_date BETWEEN li.available_from AND li.available_to
-GROUP BY year, month 
-ORDER BY year, month;
-}}}
-
-=== 7. Извештај за број на барања за поправка/сервисирање на недвижност по град
-{{{
-SELECT
-  a.city,
-  COUNT(sr.id) AS request_count
-FROM ServiceRequest sr
-JOIN Lease l ON sr.lease_id = l.id
-JOIN Listing li ON l.listing_id = li.id
-JOIN Unit u ON li.unit_id = u.id
-JOIN Property p ON u.property_id = p.id
-JOIN Address a ON p.address_id = a.id
-WHERE EXTRACT(YEAR FROM sr.request_date) = EXTRACT(YEAR FROM CURRENT_DATE)
-GROUP BY a.city
-ORDER BY request_count DESC
-}}}
-
-=== 8. Извештај за заинтересираност на изнајмувачи, издавачи со најмногу заинтересирани огласи сортирани по популарност
-
-{{{
-SELECT 
-    u.first_name, 
-    u.last_name,
-    u.email,
-    a.city,
-    COUNT(DISTINCT li.id) AS total_listings,
-    COUNT(i.tenant_profile_id) AS total_interested_tenants,
-        ROUND(COUNT(i.tenant_profile_id) / COUNT(DISTINCT li.id), 2) AS avg_interest_per_listing
-FROM LandlordProfile lp
-JOIN UserD u ON lp.id = u.id
-JOIN Property p ON p.owner_id = u.id
-JOIN Unit un ON un.property_id = p.id
-JOIN Listing li ON li.unit_id = un.id
-JOIN Address a ON p.address_id = a.id
-LEFT JOIN Interested i ON li.id = i.listing_id
-WHERE li.available_from >= CURRENT_DATE - INTERVAL '12 months'
-GROUP BY u.id, u.first_name, u.last_name, u.email, a.city
-HAVING COUNT(i.tenant_profile_id) > 0
-ORDER BY total_interested_tenants DESC;
-}}}
+        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. Детален извештај за сервисни барања, стапка на завршување и просечно време на решавање по општина и град
+{{{
+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 DATEDIFF(ml.maintenance_date, sr.request_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.Анализа на пазарни трендови и изнајмувачка понуда по општина, град и тип на недвижност
+{{{
+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 availability_rate > 75 THEN 'Висока понуда'
+        WHEN availability_rate > 50 THEN 'Умерена понуда'
+        WHEN availability_rate > 25 THEN 'Ограничена понуда'
+        ELSE 'Ниска Понуда'
+    END as market_availability_status
+FROM MarketTrends
+WHERE total_listings > 0
+ORDER BY municipality, avg_rent_per_sq_m DESC;
+
+}}}
+
 == Историја
 * [[html(<a href="https://develop.finki.ukim.mk/projects/domify/wiki/ProcedureV1" style="color: red;">Верзија1</a>)]]