From cdea8caa5617f0cb77bcbc9803759abd2df50644 Mon Sep 17 00:00:00 2001
From: Niklas Olmes <niklas@olmes.de>
Date: Fri, 24 Apr 2026 19:30:00 +0200
Subject: stipcrm

---
 patronspersons_common.php | 115 ++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 115 insertions(+)
 create mode 100644 patronspersons_common.php

(limited to 'patronspersons_common.php')

diff --git a/patronspersons_common.php b/patronspersons_common.php
new file mode 100644
index 0000000..8a24b51
--- /dev/null
+++ b/patronspersons_common.php
@@ -0,0 +1,115 @@
+<?php
+/*
+ * This program is free software; you can redistribute it and/or modify
+ * it under the terms of the GNU General Public License as published by
+ * the Free Software Foundation; either version 2 of the License, or
+ * (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful,
+ * but WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
+ * GNU General Public License for more details.
+ */
+?>
+<?php
+/* vim: set ts=4 sw=4 et : */
+
+require_once __DIR__ . "/check_auth.php";
+require_once __DIR__ . "/../includes/common.php";
+include_once __DIR__ . "/header.php";
+
+doTitle();
+
+(function () use ($_constraint, $_year) {
+    $sql = "
+SELECT
+    Personen_Organisationen.ID AS PersOrgaID,
+    Personen.ID AS PersID, Personen.Nachname, Personen.Vorname,
+    Förderer.ID AS FoerdID, Organisationen.Name AS Förderer,
+    legal.name AS Rechtsform,
+    PersonenSFOWL.Nachname AS Zuständig,
+
+    Personen_Organisationen.Funktion, Personen_Organisationen.Abteilung,
+
+    Personen_Organisationen.apstip,
+    Personen_Organisationen.apsf,
+    Personen_Organisationen.apop,
+    Personen_Organisationen.apstr,
+    Personen_Organisationen.apfi,
+    Personen_Organisationen.elsv,
+    Personen_Organisationen.elfa,
+    Personen_Organisationen.elsft,
+    Personen_Organisationen.elosv,
+    Personen_Organisationen.ernl,
+    Personen_Organisationen.ernl,
+    Personen_Organisationen.erwk,
+    Personen_Organisationen.erwm,
+    IF(Förderer.`Ansprechpartner Stipendiaten` = Personen.ID, '1', '0') AS `Erster AP/Danksagung Stips`,
+
+    Personen.Ort, Personen.Email, Personen.Telefon, Personen.Handy,
+    (Personen_Organisationen.apop=1 OR Personen_Organisationen.apstr=1 OR Personen_Organisationen.apstip=1) AS `ap-op-str-stip`,
+    IF(event_participants.validated=1 AND event_participants.declined=0, 1, 0) AS Event631
+FROM Personen
+INNER JOIN Personen_Organisationen ON Personen.ID = Personen_Organisationen.Person
+LEFT JOIN Organisationen ON Personen_Organisationen.Organisation = Organisationen.ID
+LEFT JOIN Förderer ON Organisationen.ID = Förderer.Organisation
+LEFT JOIN Personen AS PersonenSFOWL ON Förderer.zuständig = PersonenSFOWL.ID
+LEFT JOIN legal ON Förderer.legal = legal.id
+LEFT JOIN event_participants ON Personen.ID = event_participants.persid AND event_participants.eventid=631
+";
+    $sql .= $_constraint;
+    $id = "patronspersons_common";
+    $getthdef = true;
+    $order = '[[3, "asc"], [4, "asc"]]';
+    $checkboxes = true;
+    $ajax = $nospinner = true;
+    include __DIR__ . '/autotable.php';
+
+    $pdf_sql = $email_sql = "
+SELECT Organisationen.Name AS Foerderer, CONCAT(Förderer.ID, 'patron-', Personen.ID, 'person') AS uid,
+    Organisationen.strasse AS Strasse, Organisationen.plz AS PLZ, Organisationen.ort AS Ort,
+    Personen.Geschlecht,
+    Personen.`informale Ansprache`,
+    Personen.Ansprache,
+    Personen.Anrede,
+    Personen.Titel,
+    Personen.Nachname,
+    Personen.Vorname,
+    Personen.Email,
+    Personen.Telefon,
+    Personen.Handy,
+    Personen.ID AS PersID,
+    Förderer.ID AS FoerdID
+FROM Personen
+INNER JOIN Personen_Organisationen ON Personen.ID = Personen_Organisationen.Person
+LEFT JOIN Organisationen ON Personen_Organisationen.Organisation = Organisationen.ID
+LEFT JOIN Förderer ON Organisationen.ID = Förderer.Organisation
+WHERE Personen_Organisationen.ID IN ";
+    $payload_sql = "
+SELECT eventid, persid, token FROM event_participants WHERE eventid=631 AND '999999999' NOT IN 
+";
+    $ipyear = 2023;
+    if (isset($_year) && $_year > 2000) {
+        $ipyear = intval($_year);
+    }
+    $independent_payload_sql = "
+SELECT
+    Stipendien.ID AS StipID, Stipendien.Förderer AS FoerdID, Stipendien.Förderbeginn AS Foerderbeginn, Stipendien.Förderende AS Foerderende,
+    Stipendien.Förderart AS Foerderart, Hochschulen.KanonischerName AS Hochschule,
+    Studiengänge.Name AS Studiengang,
+    ORD(`Weitergabe Daten an Förderer`) AS Weitergabe, Personen.Nachname, Personen.Vorname, Personen.Email, Personen.Geschlecht,
+    IF(event_participants.validated=1 AND event_participants.declined=0, 1, 0) AS stipvergabe
+FROM Stipendien
+INNER JOIN Personen ON Stipendien.Person = Personen.ID
+INNER JOIN Hochschulen ON Stipendien.Hochschule = Hochschulen.ID
+INNER JOIN Studiengänge ON Stipendien.Studiengang = Studiengänge.ID
+LEFT JOIN event_participants ON Personen.ID = event_participants.persid AND 598 = event_participants.eventid
+WHERE Jahr IN (" . $ipyear . ") AND Förderart NOT IN (2, 6)
+ORDER BY Personen.Nachname, Personen.Vorname;";
+    include __DIR__ . '/autoactions.php';
+    include __DIR__ . '/autoemail.php';
+    include __DIR__ . '/autopdf.php';
+    include __DIR__ . '/autoevent.php';
+})();
+
+include_once __DIR__ . "/footer.php";
-- 
cgit v1.3.1