Changes between Initial Version and Version 1 of Procedures

Timestamp:

09/24/25 23:20:15 (3 months ago)

Author:

226026

Comment:

--

Procedures

@@ +1 @@
+= Процедури
+
+== Процедура за одбивање на 'PENDING' на пријави за корисници постари од 1 месец
+
+{{{
+
+CREATE OR REPLACE PROCEDURE mark_denied_reports_older_than_1month()
+LANGUAGE plpgsql
+AS $$
+DECLARE
+    older_reports RECORD;
+    user_id int;
+BEGIN
+    FOR older_reports IN
+        SELECT
+            r.id AS submission_id,
+            r.thread_id as topic
+        FROM report r
+                 JOIN submission s ON s.id = r.id
+        WHERE s.status = 'PENDING' and now() - s.created_at >= INTERVAL '1 month'
+    LOOP
+        BEGIN
+            UPDATE submission
+            SET status = 'DENIED'
+            WHERE id = older_reports.submission_id;
+
+            select u.id
+            into user_id
+            from thread t
+            join users u
+            on t.user_id = u.id
+            where t.id = older_reports.thread_id;
+
+
+            INSERT INTO feedback(description, submission_type, created_at, submission_id, created_by)
+            VALUES (
+                       'Stale report. Closing due to inactivity.',
+                       'R',
+                       now(),
+                       older_reports.submission_id,
+                       user_id
+                   );
+        END;
+    END LOOP;
+END;
+$$;
+}}}
+
+{{{
+@Transactional
+    @Scheduled(cron = "0 0 0 1 * ?")
+    public void markRejectedPendingReports() {
+        em
+                .createNativeQuery("CALL mark_denied_reports_older_than_1month();")
+                .executeUpdate();
+    }
+}}}
+
+== Процедура за одбивање на 'PENDING' за барања за учество во даден проект постари од 1 месец
+
+{{{
+CREATE OR REPLACE PROCEDURE mark_denied_pr_requests_older_than_1month()
+LANGUAGE plpgsql
+AS $$
+DECLARE
+older_reports RECORD;
+user_id int;
+BEGIN
+    FOR older_reports IN
+        SELECT
+            pr.id AS submission_id,
+            pr.project_id as thread_id
+        FROM project_request pr
+                 JOIN submission s ON s.id = pr.id
+        WHERE s.status = 'PENDING' and now() - s.created_at >= INTERVAL '1 month'
+            LOOP
+        BEGIN
+            UPDATE submission
+            SET status = 'DENIED'
+            WHERE id = older_reports.submission_id;
+
+            select u.id
+            into user_id
+            from thread t
+                     join users u
+                          on t.user_id = u.id
+            where older_reports.thread_id = t.id;
+
+            INSERT INTO feedback(description, submission_type, created_at, submission_id, created_by)
+            VALUES (
+                       'Stale report. Closing due to inactivity.',
+                       'P',
+                       now(),
+                       older_reports.submission_id,
+                       user_id
+                   );
+        END;
+    END LOOP;
+END;
+$$;
+}}}
+
+{{{
+  @Transactional
+    @Scheduled(cron = "0 0 0 1 * ?")
+    public void markRejectedPendingPrRequests() {
+        em
+                .createNativeQuery("CALL mark_denied_pr_requests_older_than_1month();")
+                .executeUpdate();
+    }
+}}}