Процедури

Процедура 1

Оваа процедура е одговорна за автоматско потсетување на изнајмувачите на станови да ја платат месечната кирија доколку не е платена во првите пет дена од месецот. На овој начин би се подобрила комуникацијата помеѓу издавачот и изнјамувачот на станот.

CREATE OR REPLACE PROCEDURE domify.send_monthly_payment_reminders()
LANGUAGE plpgsql
AS $$
DECLARE
    lease_record RECORD;
    current_month DATE;
    macedonian_month TEXT;
BEGIN
    current_month := DATE_TRUNC('month', CURRENT_DATE);

    macedonian_month := CASE EXTRACT(MONTH FROM current_month)::INT
        WHEN 1 THEN 'јануари'
        WHEN 2 THEN 'февруари'
        WHEN 3 THEN 'март'
        WHEN 4 THEN 'април'
        WHEN 5 THEN 'мај'
        WHEN 6 THEN 'јуни'
        WHEN 7 THEN 'јули'
        WHEN 8 THEN 'август'
        WHEN 9 THEN 'септември'
        WHEN 10 THEN 'октомври'
        WHEN 11 THEN 'ноември'
        WHEN 12 THEN 'декември'
        ELSE ''
    END;

    FOR lease_record IN 
        SELECT l.id, l.landlord_id, l.tenant_id, l.start_date, l.end_date, l.rent_amount
        FROM domify.Lease l
        JOIN domify.Listing ls ON l.listing_id = ls.id
        JOIN domify.Unit u ON ls.unit_id = u.id
        WHERE l.start_date <= CURRENT_DATE 
          AND l.end_date >= CURRENT_DATE
    LOOP
        IF NOT EXISTS (
            SELECT 1 FROM domify.Payment p
            WHERE p.lease_id = lease_record.id
              AND DATE_TRUNC('month', p.payment_date) = current_month
              AND p.status IN ('completed', 'paid')
        ) THEN
            IF NOT EXISTS (
                SELECT 1 FROM domify.MessageD 
                WHERE lease_id = lease_record.id 
                  AND DATE_TRUNC('month', sent_at) = current_month
                  AND content LIKE 'Потсетување:%'
            ) THEN
                INSERT INTO domify.MessageD (
                    content,
                    sent_at,
                    from_user_id,
                    to_user_id,
                    lease_id
                ) VALUES (
                    'Потсетување: Потребно е да ја подмирите Вашата кирија во износ од ' || lease_record.rent_amount || 
                    ' денари за месец ' || macedonian_month || ' ' || EXTRACT(YEAR FROM current_month)::TEXT || 
                    ' во најбрз можен рок.',
                    CURRENT_DATE,
                    lease_record.landlord_id,
                    lease_record.tenant_id,
                    lease_record.id
                );
            END IF;
        END IF;
    END LOOP;
END;
$$;

Процедурата работи на тој начин што прво ги бара сите договори кои имаат валидни рокови на траење (сеуште се активни), по што се прави проверка дали во табелата Payment постои плаќање за овој месец, доколку не постои се прави проверка дали веќе е испратена пораката, доколку не е се испраќа од издавачот до изнајмувачот на станот со што е известен дека е потребно да ја плати киријата.

    @Scheduled(cron = "0 0 15 3-5 * *")
    @Transactional
    public void callReminderProcedure() {
        entityManager
                .createNativeQuery("CALL domify.send_monthly_payment_reminders()")
                .executeUpdate();
    }

За автоматско повикување на оваа процедура се користи Spring Scheduler со cron. Со тоа се овозможува процедурата да се извршува автоматски во точно определено време. Во овој случај, изразот @Scheduled(cron = "0 0 15 3-5 * *") значи дека процедурата ќе се повикува секој месец на 3-ти, 4-ти и 5-ти ден во 15:00 часот, при што ќе се изврши проверката и испраќањето на пораката до изнајмувачите.

Процедура 2

Оваа процедура е одговорна за автоматско потсетување на издавачите на станови за нерешени сервисни барања кои се постари од 7 дена. На овој начин се обезбедува навремено решавање на проблемите и подобрување на квалитетот на услугите за изнајмувачите.

CREATE OR REPLACE PROCEDURE domify.send_unresolved_service_request_reminders()
LANGUAGE plpgsql
AS $$
DECLARE
    req RECORD;
    admin_user_id BIGINT := 6; -- Vo nas slucaj id na admin e 6
BEGIN
    FOR req IN
        SELECT sr.id AS service_request_id,
               sr.lease_id,
               sr.request_date,
               sr.status,
               l.landlord_id,
               ll.first_name || ' ' || ll.last_name AS landlord_name,
               tp.id AS tenant_id,
               tt.first_name || ' ' || tt.last_name AS tenant_name,
               p.title AS property_title,
               u.unit_number,
               a.street,
               a.number,
               a.municipality,
               a.city
        FROM domify.ServiceRequest sr
        JOIN domify.Lease l ON sr.lease_id = l.id
        JOIN domify.LandlordProfile lp ON l.landlord_id = lp.id
        JOIN domify.UserD ll ON lp.id = ll.id
        JOIN domify.TenantProfile tp ON l.tenant_id = tp.id
        JOIN domify.UserD tt ON tp.id = tt.id
        JOIN domify.Listing ls ON l.listing_id = ls.id
        JOIN domify.Unit u ON ls.unit_id = u.id
        JOIN domify.Property p ON u.property_id = p.id
        JOIN domify.Address a ON p.address_id = a.id
        WHERE sr.status IN ('pending', 'in_progress')
          AND sr.request_date <= CURRENT_DATE - INTERVAL '7 days'
    LOOP
        INSERT INTO domify.MessageD (
            content,
            sent_at,
            from_user_id,
            to_user_id,
            lease_id
        ) VALUES (
            'Потсетување: Сервисното барање #' || req.service_request_id ||
            ' за имотот "' || req.property_title || '", единица ' || req.unit_number ||
            ' на адреса ' || req.street || ' ' || req.number ||
            ', ' || req.municipality || ', ' || req.city ||
            ', поднесено од ' || req.tenant_name ||
            ' на ' || TO_CHAR(req.request_date, 'DD.MM.YYYY') ||
            ' е сè уште во статус "' || req.status || '". Ве молиме да постапите што е можно побрзо.',
            CURRENT_TIMESTAMP,
            admin_user_id,
            req.landlord_id,
            req.lease_id
        );
    END LOOP;
END;
$$;

Процедурата работи на тој начин што прво ги бара сите сервисни барања кои се во статус 'pending' или 'in_progress' и кои се постари од 7 дена. За секое такво барање се собираат детални информации за имотот, издавачот, изнајмувачот и адресата. Потоа се испраќа порака од системскиот администратор до издавачот на станот со детални информации за нерешеното сервисно барање.

@Scheduled(cron = "0 0 18 * * *")
@Transactional
public void callUnresolvedServiceReminderProcedure() {
    entityManager
            .createNativeQuery("CALL domify.send_unresolved_service_request_reminders()")
            .executeUpdate();
}

Во овој случај, изразот @Scheduled(cron = "0 0 18 * * *") значи дека процедурата ќе се повикува секој ден во 18:00 часот, при што ќе се изврши проверката и испраќањето на пораки до издавачите за сите нерешени сервисни барања постари од 7 дена.

Процедура 3

Оваа процедура е одговорна за автоматско санкционирање на издавачите кои имаат долготрајни нерешени сервисни барања (подолги од 14 дена). Процедурата го намалува рејтингот на издавачот и го известува за санкцијата, што придонесува за подобрување на квалитетот на услугите и одговорното однесување.

CREATE OR REPLACE PROCEDURE domify.flag_landlords_with_overdue_maintenance()
LANGUAGE plpgsql
AS $$
DECLARE
    rec RECORD;
    new_rating NUMERIC(3,2);
    system_admin_id BIGINT := 6;
BEGIN
    FOR rec IN
        SELECT DISTINCT 
            l.landlord_id, 
            l.id AS lease_id,
            u.unit_number
        FROM domify.ServiceRequest sr
        JOIN domify.Lease l ON sr.lease_id = l.id
        JOIN domify.Listing li ON l.listing_id = li.id
        JOIN domify.Unit u ON li.unit_id = u.id
        WHERE sr.status NOT IN ('completed', 'resolved')
          AND sr.request_date < CURRENT_DATE - INTERVAL '14 days'
    LOOP
        UPDATE domify.UserD
        SET rating = GREATEST(0.00, rating - 0.10)
        WHERE id = rec.landlord_id
        RETURNING rating INTO new_rating;

        INSERT INTO domify.MessageD (
            content, sent_at, from_user_id, to_user_id, lease_id
        ) VALUES (
            'Вашиот рејтинг е намален заради неисполнето сервисно барање подолго од 14 дена за единицата ' 
           || rec.unit_number || '. Нов рејтинг: ' || new_rating,
            CURRENT_TIMESTAMP,
            system_admin_id,
            rec.landlord_id,
            rec.lease_id
        );
    END LOOP;
END;
$$;

Процедурата работи на тој начин што прво ги бара сите издавачи кои имаат нерешени сервисни барања (со статус различен од 'completed' или 'resolved') постари од 14 дена. За секој таков случај се намалува рејтингот на издавачот за 0.10 поени (со минимална вредност од 0.00), а потоа се испраќа порака од системскиот администратор до издавачот со информации за санкцијата и новиот рејтинг.

@Scheduled(cron = "0 0 12 * * *")
@Transactional
public void callOverdueMaintenanceFlagProcedure() {
    entityManager
            .createNativeQuery("CALL domify.flag_landlords_with_overdue_maintenance()")
            .executeUpdate();
}

Во овој случај, изразот @Scheduled(cron = "0 0 12 * * *") значи дека процедурата ќе се повикува секој ден во 12:00 часот, при што ќе се изврши проверката и санкционирањето на издавачите со долготрајни нерешени сервисни барања.

Процедура 4

Оваа процедура е одговорна за автоматско ажурирање на статусите на огласите во системот врз основа на нивната достапност и тековната состојба.

CREATE OR REPLACE PROCEDURE domify.update_listing_statuses()
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE domify.Listing
    SET status = 'expired'
    WHERE available_to < CURRENT_DATE 
      AND status != 'expired';
    
    UPDATE domify.Listing
    SET status = 'rented'
    WHERE EXISTS (
        SELECT 1 
        FROM domify.Lease l 
        WHERE l.listing_id = domify.Listing.id
          AND l.start_date <= CURRENT_DATE 
          AND l.end_date >= CURRENT_DATE
    )
    AND status != 'rented';

    UPDATE domify.Listing
    SET status = 'available'
    WHERE available_from <= CURRENT_DATE 
      AND available_to >= CURRENT_DATE
      AND status != 'expired'
      AND NOT EXISTS (
          SELECT 1 
          FROM domify.Lease l 
          WHERE l.listing_id = domify.Listing.id
            AND l.start_date <= CURRENT_DATE 
            AND l.end_date >= CURRENT_DATE
      );
END;

$$;

Процедурата работи во три чекори: прво, ги обележува како "истечени" сите огласи чијшто рок на достапност (available_to) е поминат, второ, ги обележува како "изнајмени" сите огласи кои имаат активни договори за изнајмување во моментот, и трето, ги обележува како "достапни" сите огласи кои се во рамките на нивниот период на достапност и немаат активни договори.

@Scheduled(cron = "0 0 2 * * *")
@Transactional
public void callUpdateListingStatusesProcedure() {
    entityManager
            .createNativeQuery("CALL domify.update_listing_statuses()")
            .executeUpdate();
}

Во овој случај, изразот @Scheduled(cron = "0 0 2 * * *") значи дека процедурата ќе се повикува секој ден во 2:00 часот наутро, при што ќе се изврши ажурирањето на сите статуси на огласите според тековната состојба и достапност.

На крајот процедурите кои ги имаме во нашата база на податоци се: