diff options
Diffstat (limited to 'contracts_common.php')
| -rw-r--r-- | contracts_common.php | 146 |
1 files changed, 146 insertions, 0 deletions
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"; +?> |