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

---
 contracts_common.php | 146 +++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 146 insertions(+)
 create mode 100644 contracts_common.php

(limited to 'contracts_common.php')

diff --git a/contracts_common.php b/contracts_common.php
new file mode 100644
index 0000000..84b68ff
--- /dev/null
+++ b/contracts_common.php
@@ -0,0 +1,146 @@
+<?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";
+require_once __DIR__ . "/lookup.php";
+
+doTitle();
+
+if (isset($_GET['apfi']) && $_GET['apfi'] == 1) {
+?>
+    <p>Der/Die Ansprechpartner Finanzen (apfi) werden für den E-Mail-Versand benutzt. <a href="?apfi=0">Auf AP Zuwendungen zurückschalten.</a></p>
+<p>Der Ansprechpartner Zuwendungen ist Adressat des PDF-Dokuments.</p>
+<? } else { ?>
+    <p>Der Ansprechpartner Zuwendungen des Förderers wird für den E-Mail-Versand benutzt. <a href="?apfi=1">Auf AP Finanzen schalten.</a></p>
+    <p>Der Ansprechpartner Zuwendungen ist Adressat des PDF-Dokuments.</p>
+<?
+}
+
+(function () use ($mysqli, $_constraint) {
+    $sql = "
+SELECT
+    contracts.id AS VertrID, CONCAT(calls.name, ' [', `call`, ']') as 'Call', patron AS FoerdID, Organisationen.Name AS Förderer,
+    Personen.Nachname AS Zuständig,
+    ls AS Stipendien, 
+    contracts.remark,
+    CONCAT(IF(Förderer.`SEPA-Lastschrift erteilt`, 'SEPA|', ''), IF(SUM(Spenden.Betrag) > 0, SUM(Spenden.Betrag), 0)) AS Eingang,
+    IF(Förderer.`SEPA-Lastschrift erteilt`, 0, ((150 * contracts.ss + 150 * contracts.ls) * 12) - IF(SUM(Spenden.Betrag) > 0, SUM(Spenden.Betrag), 0)) AS Fehlbetrag,
+    GROUP_CONCAT(DATE(Spenden.Geldeingang) SEPARATOR ', ') AS Geldeingänge,
+    CONCAT(DATE(contracts.valid_from), ' bis ', DATE(contracts.valid_to)) AS 'Gültigkeit',
+    DATE(Förderer.`Vertrag läuft aus`) AS 'allgemeiner Auslauf',
+    contracts.kontrolliert,
+    `Zahlungsaufforderung gewünscht`
+FROM contracts
+LEFT JOIN Förderer ON contracts.patron = Förderer.ID
+LEFT JOIN Organisationen ON Förderer.Organisation = Organisationen.ID
+LEFT JOIN Spenden ON contracts.id = Spenden.contract
+LEFT JOIN calls ON contracts.`call` = calls.shorthand
+LEFT JOIN Personen ON Förderer.Zuständig = Personen.ID
+GROUP BY contracts.id
+";
+    $sql .= $_constraint;
+    $stmt = $mysqli->prepare($sql);
+    $stmt->execute();
+    $r = $stmt->get_result();
+        $id = "contractscommon4";
+    $idcell = "VertrID";
+    $idcellreal = "id";
+    $order = '[[4, "asc"]]';
+    $entrytable = 'contracts';
+    $types = [
+        'kontrolliert' => 'checkbox',
+    ];
+    $editable = [
+        'kontrolliert',
+        'remark',
+    ];
+    $checkboxes = true;
+    $bottom = <<<EOD
+    <div class="admin_actionsbelow">
+        Ausgewählte
+        <button class="medium" style="background: darkorange;" onclick="return delContract();"><i class="fas fa-trash-alt"></i> löschen</button>
+    </div>
+EOD;
+    $email_sql = "
+SELECT Förderer.ID AS FoerdID, Organisationen.Name AS Foerderer, CONCAT(Förderer.ID, 'patron', '-', Personen.ID, 'person', '-', contracts.id, 'contract') AS uid, Personen.*, Organisationen.strasse AS Strasse, Organisationen.PLZ as PLZ, Organisationen.Ort AS Ort, Organisationen.Adresszusatz as Adresszusatz, Organisationen.Name AS Organisation, CONCAT(Förderer.ID, 'patron', '-', Personen.ID, 'person', '-', contracts.id, 'contract') AS genuid,
+
+ls AS Leistung, ss AS Sozial, (ls * 150 * 12 + ss * 150 * 12) AS Summe,
+IF(SUM(Spenden.Betrag) > 0, SUM(Spenden.Betrag), 0) AS Eingang,
+((150 * contracts.ss + 150 * contracts.ls) * 12) - IF(SUM(Spenden.Betrag) > 0, SUM(Spenden.Betrag), 0) AS Fehlbetrag
+
+FROM contracts
+
+INNER JOIN Förderer ON contracts.patron = Förderer.ID
+LEFT JOIN Organisationen ON Förderer.Organisation = Organisationen.ID
+LEFT JOIN Personen ON Förderer.zuwendungen = Personen.ID
+LEFT JOIN Spenden ON contracts.id = Spenden.contract
+
+WHERE contracts.id IN ";
+
+    $email_sql_apfi = "
+SELECT Förderer.ID AS FoerdID, Organisationen.Name AS Foerderer, CONCAT(Förderer.ID, 'patron', '-', Personen2.ID, 'person', '-', contracts.id, 'contract') AS uid, Personen.*, Organisationen.strasse AS Strasse, Organisationen.PLZ as PLZ, Organisationen.Ort AS Ort, Organisationen.Adresszusatz as Adresszusatz, Organisationen.Name AS Organisation, CONCAT(Förderer.ID, 'patron', '-', Personen2.ID, 'person', '-', contracts.id, 'contract') AS genuid,
+
+ls AS Leistung, ss AS Sozial, (ls * 150 * 12 + ss * 150 * 12) AS Summe,
+IF(SUM(Spenden.Betrag) > 0, SUM(Spenden.Betrag), 0) AS Eingang,
+((150 * contracts.ss + 150 * contracts.ls) * 12) - IF(SUM(Spenden.Betrag) > 0, SUM(Spenden.Betrag), 0) AS Fehlbetrag
+
+FROM contracts
+
+INNER JOIN Förderer ON contracts.patron = Förderer.ID
+INNER JOIN Organisationen ON Förderer.Organisation = Organisationen.ID
+
+LEFT JOIN Personen_Organisationen ON Organisationen.ID = Personen_Organisationen.Organisation
+LEFT JOIN Personen ON Personen_Organisationen.Person = Personen.ID 
+LEFT JOIN Personen AS Personen2 ON Förderer.zuwendungen = Personen2.ID
+LEFT JOIN Spenden ON contracts.id = Spenden.contract
+
+WHERE Personen_Organisationen.apfi = 1
+AND contracts.id IN ";
+
+    $pdf_sql = $email_sql;
+    if (isset($_GET['apfi']) && $_GET['apfi'] == 1) $email_sql = $email_sql_apfi;
+    $post_sql = ' GROUP BY contracts.id, Personen.ID';
+    $exactmatch = 1;
+
+    include __DIR__ . '/autotable.php';
+    include __DIR__ . '/autoactions.php';
+    include __DIR__ . '/autoemail.php';
+    include __DIR__ . '/autopdf.php';
+    $stmt->reset();
+})();
+?>
+
+<script>
+function delContract() {
+    $.ajaxSetup({async:false});
+    $.ajax({
+        type: 'POST',
+        url: '/db/main/delcontract.php',
+        data: {
+            'ids': getIDs_contractscommon3()
+        }
+    });
+    $.ajaxSetup({async:true});
+    location.reload();
+    return false;
+}
+</script>
+
+<?php
+include_once __DIR__ . "/footer.php";
+?>
-- 
cgit v1.3.1