Changes between Version 1 and Version 2 of DatabaseProgramming

Timestamp:

06/15/26 20:26:17 (4 days ago)

Author:

231067

Comment:

--

DatabaseProgramming

@@ -169 +169 @@
 == Фунцкии
 
+Функцијата `fn_eligibility_matrix` е дизајнирана да ја пресмета матрицата на компатибилност помеѓу одреден студент и ментор, враќајќи ги резултатите во структуриран JSONB формат. По почетната проверка за постоење на корисниците во системот, таа го анализира бројот на заеднички предмети кои ги слушаат или предаваат, како и бројот на заеднички интереси кои ги делат, преку кои се одредува компатибилност. Исто така, функцијата утврдува дали двајцата корисници припаѓаат на ист факултет. Финалната одлука за подобност се носи врз основа на тоа дали корисниците имаат барем еден заеднички предмет или најмалку два заеднички интереси. Сите овие пресметани метрики се подредени во прегледен објект за понатамошна обработка во апликацискиот слој.
+
+{{{
+CREATE OR REPLACE FUNCTION fn_eligibility_matrix(p_student_id BIGINT, p_mentor_id BIGINT)
+RETURNS JSONB
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_shared_subjects INT;
+    v_shared_interests INT;
+    v_same_faculty BOOLEAN;
+    v_student_fac BIGINT;
+    v_mentor_fac BIGINT;
+    v_is_eligible BOOLEAN;
+BEGIN
+    -- Проверки дали постојат
+    IF NOT EXISTS (SELECT 1 FROM "User" WHERE ID = p_student_id) OR NOT EXISTS (SELECT 1 FROM "User" WHERE ID = p_mentor_id) THEN
+        RETURN jsonb_build_object('error', 'Корисниците не постојат');
+    END IF;
+
+    -- Заеднички предмети
+    SELECT COUNT(*) INTO v_shared_subjects FROM User_Subject us1
+    JOIN User_Subject us2 ON us1.SubjectID = us2.SubjectID
+    WHERE us1.UserID = p_student_id AND us2.UserID = p_mentor_id;
+
+    -- Заеднички интереси
+    SELECT COUNT(*) INTO v_shared_interests FROM User_Interest ui1
+    JOIN User_Interest ui2 ON ui1.InterestID = ui2.InterestID
+    WHERE ui1.UserID = p_student_id AND ui2.UserID = p_mentor_id;
+
+    -- Факултет
+    SELECT FacultyID INTO v_student_fac FROM "User" WHERE ID = p_student_id;
+    SELECT FacultyID INTO v_mentor_fac FROM "User" WHERE ID = p_mentor_id;
+    v_same_faculty := (v_student_fac = v_mentor_fac);
+
+    -- Логика за подобност
+    v_is_eligible := (v_shared_subjects > 0 OR v_shared_interests >= 2);
+
+    RETURN jsonb_build_object(
+        'student_id', p_student_id,
+        'mentor_id', p_mentor_id,
+        'is_eligible', v_is_eligible,
+        'metrics', jsonb_build_object(
+            'shared_subjects', v_shared_subjects,
+            'shared_interests', v_shared_interests,
+            'same_faculty', v_same_faculty
+        )
+    );
+END;
+$$;
+
+SELECT fn_eligibility_matrix(1, 2);
+}}}
+
+----
+
+Функцијата `fn_mentor_bottleneck_score` служи за пресметување на нумерички индекс кој го рефлектира просечното доцнење на одреден ментор при реализација на задачите со неговите студенти. Таа ги анализира сите активни задачи за кои е задолжен менторот, а чиј краен рок е веќе поминат, па го собира вкупниот број на денови на доцнење во однос на тековниот датум. Доколку менторот нема активни задачи кои доцнат, функцијата веднаш враќа вредност нула. Во спротивно, таа го пресметува просечниот број на денови на доцнење по задача и го заокружува овој резултат на две децимали, овозможувајќи лесна детекција на ментори кои претставуваат тесно грло во процесот.
+
+{{{
+CREATE OR REPLACE FUNCTION fn_mentor_bottleneck_score(p_mentor_id BIGINT)
+RETURNS NUMERIC
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_total_overdue_days NUMERIC;
+    v_active_tasks INT;
+BEGIN
+    SELECT 
+        COALESCE(SUM(EXTRACT(DAY FROM (CURRENT_DATE - t.EndDate))), 0), 
+        COUNT(t.ID)
+    INTO v_total_overdue_days, v_active_tasks
+    FROM Task t
+    JOIN Mentorship m ON t.MentorshipID = m.ID
+    WHERE m.MentorID = p_mentor_id AND t.Status = 0 AND t.EndDate < CURRENT_DATE;
+
+    IF v_active_tasks = 0 THEN
+        RETURN 0.00;
+    END IF;
+
+    -- Формула: Просечно доцнење во денови по задача
+    RETURN ROUND(v_total_overdue_days / v_active_tasks, 2);
+END;
+$$;
+
+SELECT fn_mentor_bottleneck_score(2);
+}}}
+
+----
+
+Функцијата `fn_predict_completion_date` користи едноставен алгоритам за предвидување на точниот датум и време кога би завршило одредено менторство врз основа на досегашната динамика на работа. Таа го извлекува бројот на завршени и преостанати задачи за менторството, како и датумот кога е започната првата задача. Доколку нема доволно историски податоци, односно ако нема ниту една завршена или преостаната задача, функцијата враќа празна (NULL) вредност. Ако податоците се достапни, таа го пресметува просечното време потребно за извршување на една задача и го множи со бројот на преостанати задачи, додавајќи го овој временски интервал на тековното време за да пресмета крајна временска прогноза.
+
+{{{
+CREATE OR REPLACE FUNCTION fn_predict_completion_date(p_mentorship_id BIGINT)
+RETURNS TIMESTAMP
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    v_completed INT;
+    v_pending INT;
+    v_avg_days_per_task NUMERIC;
+    v_start_date TIMESTAMP;
+BEGIN
+    SELECT COUNT(*) FILTER (WHERE Status = 1), COUNT(*) FILTER (WHERE Status = 0), MIN(StartDate)
+    INTO v_completed, v_pending, v_start_date
+    FROM Task WHERE MentorshipID = p_mentorship_id;
+
+    -- Нема доволно податоци
+    IF v_completed = 0 OR v_pending = 0 THEN
+        RETURN NULL;
+    END IF;
+
+    -- Колку денови му требале во просек за една задача
+    v_avg_days_per_task := EXTRACT(DAY FROM (CURRENT_TIMESTAMP - v_start_date)) / v_completed;
+    
+    -- Прогноза: Денес + (Просек * Останати Задачи)
+    RETURN CURRENT_TIMESTAMP + (v_avg_days_per_task * v_pending || ' days')::INTERVAL;
+END;
+$$;
+
+SELECT fn_predict_completion_date(9887604);
+}}}
+
 == Тригери