From cdea8caa5617f0cb77bcbc9803759abd2df50644 Mon Sep 17 00:00:00 2001 From: Niklas Olmes Date: Fri, 24 Apr 2026 19:30:00 +0200 Subject: stipcrm --- .htaccess | 4 + LICENSE | 339 ++++++++++++++ Makefile | 18 + addcomm.php | 32 ++ addtemplate.php | 42 ++ allocation.php | 59 +++ alumni.pers.php | 64 +++ alumni.php | 19 + auto_common.php | 180 ++++++++ autoactions.php | 48 ++ autoeemail.php | 31 ++ autoemail.php | 80 ++++ autoform.php | 646 ++++++++++++++++++++++++++ autopdf.php | 79 ++++ autotable.php | 1103 ++++++++++++++++++++++++++++++++++++++++++++ autotable_explain.php | 36 ++ autotable_fontsettings.php | 41 ++ calls.php | 65 +++ commission.php | 74 +++ commissioners.php | 161 +++++++ commissioners_person.php | 113 +++++ commissions.php | 61 +++ composer.json | 9 + contract.php | 111 +++++ contracts.php | 19 + contracts_common.php | 146 ++++++ curfutpatrons.php | 19 + curfutpatronspersons.php | 27 ++ curpatrons.php | 19 + curstips.php | 19 + curstipspers.php | 58 +++ delay.php | 22 + delcomm.php | 20 + delcontract.php | 20 + delcontractc.php | 140 ++++++ delcontractdo.php | 41 ++ deldocument.php | 46 ++ deldonation.php | 20 + delorga.php | 284 ++++++++++++ delorgado.php | 41 ++ delorgaperson.php | 20 + delpatron.php | 179 +++++++ delpatrondo.php | 41 ++ delperson.php | 385 ++++++++++++++++ delpersondo.php | 70 +++ delstip.php | 133 ++++++ delstipdo.php | 41 ++ deltemplate.php | 37 ++ donation.php | 206 +++++++++ donations.php | 197 ++++++++ donationsXdonation.php | 40 ++ donationsXorga.php | 43 ++ donationsXpers.php | 39 ++ dopdf.php | 202 ++++++++ email.php | 708 ++++++++++++++++++++++++++++ emailhtml.php | 454 ++++++++++++++++++ form.scm | 989 +++++++++++++++++++++++++++++++++++++++ futstips.php | 19 + handle_css.php | 18 + handle_js.php | 18 + lang-tags.scm | 8 + orga.php | 83 ++++ orgaXpers.php | 49 ++ orgapersons.php | 142 ++++++ orgas.php | 42 ++ patron.php | 224 +++++++++ patrons.php | 19 + patrons_by_year.php | 41 ++ patrons_common.php | 155 +++++++ patronspersons.php | 28 ++ patronspersons_by_year.php | 42 ++ patronspersons_common.php | 115 +++++ pdf.php | 516 +++++++++++++++++++++ pers.php | 177 +++++++ persXorga.php | 130 ++++++ persons.php | 50 ++ php.scm | 66 +++ robots.txt | 2 + searchorga.php | 64 +++ searchpatron.php | 67 +++ searchperson.php | 100 ++++ searchstip.php | 20 + sendmail.php | 267 +++++++++++ stips.php | 29 ++ stipspers.php | 57 +++ template.email.php | 63 +++ templater_common.php | 567 +++++++++++++++++++++++ templates.email.php | 72 +++ unwrap-php.sh | 5 + update.php | 95 ++++ 90 files changed, 11490 insertions(+) create mode 100644 .htaccess create mode 100644 LICENSE create mode 100644 Makefile create mode 100644 addcomm.php create mode 100644 addtemplate.php create mode 100644 allocation.php create mode 100644 alumni.pers.php create mode 100644 alumni.php create mode 100644 auto_common.php create mode 100644 autoactions.php create mode 100644 autoeemail.php create mode 100644 autoemail.php create mode 100644 autoform.php create mode 100644 autopdf.php create mode 100644 autotable.php create mode 100644 autotable_explain.php create mode 100644 autotable_fontsettings.php create mode 100644 calls.php create mode 100644 commission.php create mode 100644 commissioners.php create mode 100644 commissioners_person.php create mode 100644 commissions.php create mode 100644 composer.json create mode 100644 contract.php create mode 100644 contracts.php create mode 100644 contracts_common.php create mode 100644 curfutpatrons.php create mode 100644 curfutpatronspersons.php create mode 100644 curpatrons.php create mode 100644 curstips.php create mode 100644 curstipspers.php create mode 100644 delay.php create mode 100644 delcomm.php create mode 100644 delcontract.php create mode 100644 delcontractc.php create mode 100644 delcontractdo.php create mode 100644 deldocument.php create mode 100644 deldonation.php create mode 100644 delorga.php create mode 100644 delorgado.php create mode 100644 delorgaperson.php create mode 100644 delpatron.php create mode 100644 delpatrondo.php create mode 100644 delperson.php create mode 100644 delpersondo.php create mode 100644 delstip.php create mode 100644 delstipdo.php create mode 100644 deltemplate.php create mode 100644 donation.php create mode 100644 donations.php create mode 100644 donationsXdonation.php create mode 100644 donationsXorga.php create mode 100644 donationsXpers.php create mode 100644 dopdf.php create mode 100644 email.php create mode 100644 emailhtml.php create mode 100644 form.scm create mode 100644 futstips.php create mode 100644 handle_css.php create mode 100644 handle_js.php create mode 100644 lang-tags.scm create mode 100644 orga.php create mode 100644 orgaXpers.php create mode 100644 orgapersons.php create mode 100644 orgas.php create mode 100644 patron.php create mode 100644 patrons.php create mode 100644 patrons_by_year.php create mode 100644 patrons_common.php create mode 100644 patronspersons.php create mode 100644 patronspersons_by_year.php create mode 100644 patronspersons_common.php create mode 100644 pdf.php create mode 100644 pers.php create mode 100644 persXorga.php create mode 100644 persons.php create mode 100644 php.scm create mode 100644 robots.txt create mode 100644 searchorga.php create mode 100644 searchpatron.php create mode 100644 searchperson.php create mode 100644 searchstip.php create mode 100644 sendmail.php create mode 100644 stips.php create mode 100644 stipspers.php create mode 100644 template.email.php create mode 100644 templater_common.php create mode 100644 templates.email.php create mode 100755 unwrap-php.sh create mode 100644 update.php diff --git a/.htaccess b/.htaccess new file mode 100644 index 0000000..15f94b7 --- /dev/null +++ b/.htaccess @@ -0,0 +1,4 @@ +Options -Indexes + +RewriteEngine On +RewriteRule .* - [F,L,NC] diff --git a/LICENSE b/LICENSE new file mode 100644 index 0000000..d511905 --- /dev/null +++ b/LICENSE @@ -0,0 +1,339 @@ + GNU GENERAL PUBLIC LICENSE + Version 2, June 1991 + + Copyright (C) 1989, 1991 Free Software Foundation, Inc., + 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA + Everyone is permitted to copy and distribute verbatim copies + of this license document, but changing it is not allowed. + + Preamble + + The licenses for most software are designed to take away your +freedom to share and change it. By contrast, the GNU General Public +License is intended to guarantee your freedom to share and change free +software--to make sure the software is free for all its users. This +General Public License applies to most of the Free Software +Foundation's software and to any other program whose authors commit to +using it. (Some other Free Software Foundation software is covered by +the GNU Lesser General Public License instead.) You can apply it to +your programs, too. + + When we speak of free software, we are referring to freedom, not +price. Our General Public Licenses are designed to make sure that you +have the freedom to distribute copies of free software (and charge for +this service if you wish), that you receive source code or can get it +if you want it, that you can change the software or use pieces of it +in new free programs; and that you know you can do these things. + + To protect your rights, we need to make restrictions that forbid +anyone to deny you these rights or to ask you to surrender the rights. +These restrictions translate to certain responsibilities for you if you +distribute copies of the software, or if you modify it. + + For example, if you distribute copies of such a program, whether +gratis or for a fee, you must give the recipients all the rights that +you have. You must make sure that they, too, receive or can get the +source code. And you must show them these terms so they know their +rights. + + We protect your rights with two steps: (1) copyright the software, and +(2) offer you this license which gives you legal permission to copy, +distribute and/or modify the software. + + Also, for each author's protection and ours, we want to make certain +that everyone understands that there is no warranty for this free +software. If the software is modified by someone else and passed on, we +want its recipients to know that what they have is not the original, so +that any problems introduced by others will not reflect on the original +authors' reputations. + + Finally, any free program is threatened constantly by software +patents. We wish to avoid the danger that redistributors of a free +program will individually obtain patent licenses, in effect making the +program proprietary. To prevent this, we have made it clear that any +patent must be licensed for everyone's free use or not licensed at all. + + The precise terms and conditions for copying, distribution and +modification follow. + + GNU GENERAL PUBLIC LICENSE + TERMS AND CONDITIONS FOR COPYING, DISTRIBUTION AND MODIFICATION + + 0. This License applies to any program or other work which contains +a notice placed by the copyright holder saying it may be distributed +under the terms of this General Public License. The "Program", below, +refers to any such program or work, and a "work based on the Program" +means either the Program or any derivative work under copyright law: +that is to say, a work containing the Program or a portion of it, +either verbatim or with modifications and/or translated into another +language. (Hereinafter, translation is included without limitation in +the term "modification".) Each licensee is addressed as "you". + +Activities other than copying, distribution and modification are not +covered by this License; they are outside its scope. The act of +running the Program is not restricted, and the output from the Program +is covered only if its contents constitute a work based on the +Program (independent of having been made by running the Program). +Whether that is true depends on what the Program does. + + 1. You may copy and distribute verbatim copies of the Program's +source code as you receive it, in any medium, provided that you +conspicuously and appropriately publish on each copy an appropriate +copyright notice and disclaimer of warranty; keep intact all the +notices that refer to this License and to the absence of any warranty; +and give any other recipients of the Program a copy of this License +along with the Program. + +You may charge a fee for the physical act of transferring a copy, and +you may at your option offer warranty protection in exchange for a fee. + + 2. You may modify your copy or copies of the Program or any portion +of it, thus forming a work based on the Program, and copy and +distribute such modifications or work under the terms of Section 1 +above, provided that you also meet all of these conditions: + + a) You must cause the modified files to carry prominent notices + stating that you changed the files and the date of any change. + + b) You must cause any work that you distribute or publish, that in + whole or in part contains or is derived from the Program or any + part thereof, to be licensed as a whole at no charge to all third + parties under the terms of this License. + + c) If the modified program normally reads commands interactively + when run, you must cause it, when started running for such + interactive use in the most ordinary way, to print or display an + announcement including an appropriate copyright notice and a + notice that there is no warranty (or else, saying that you provide + a warranty) and that users may redistribute the program under + these conditions, and telling the user how to view a copy of this + License. (Exception: if the Program itself is interactive but + does not normally print such an announcement, your work based on + the Program is not required to print an announcement.) + +These requirements apply to the modified work as a whole. If +identifiable sections of that work are not derived from the Program, +and can be reasonably considered independent and separate works in +themselves, then this License, and its terms, do not apply to those +sections when you distribute them as separate works. But when you +distribute the same sections as part of a whole which is a work based +on the Program, the distribution of the whole must be on the terms of +this License, whose permissions for other licensees extend to the +entire whole, and thus to each and every part regardless of who wrote it. + +Thus, it is not the intent of this section to claim rights or contest +your rights to work written entirely by you; rather, the intent is to +exercise the right to control the distribution of derivative or +collective works based on the Program. + +In addition, mere aggregation of another work not based on the Program +with the Program (or with a work based on the Program) on a volume of +a storage or distribution medium does not bring the other work under +the scope of this License. + + 3. You may copy and distribute the Program (or a work based on it, +under Section 2) in object code or executable form under the terms of +Sections 1 and 2 above provided that you also do one of the following: + + a) Accompany it with the complete corresponding machine-readable + source code, which must be distributed under the terms of Sections + 1 and 2 above on a medium customarily used for software interchange; or, + + b) Accompany it with a written offer, valid for at least three + years, to give any third party, for a charge no more than your + cost of physically performing source distribution, a complete + machine-readable copy of the corresponding source code, to be + distributed under the terms of Sections 1 and 2 above on a medium + customarily used for software interchange; or, + + c) Accompany it with the information you received as to the offer + to distribute corresponding source code. (This alternative is + allowed only for noncommercial distribution and only if you + received the program in object code or executable form with such + an offer, in accord with Subsection b above.) + +The source code for a work means the preferred form of the work for +making modifications to it. For an executable work, complete source +code means all the source code for all modules it contains, plus any +associated interface definition files, plus the scripts used to +control compilation and installation of the executable. However, as a +special exception, the source code distributed need not include +anything that is normally distributed (in either source or binary +form) with the major components (compiler, kernel, and so on) of the +operating system on which the executable runs, unless that component +itself accompanies the executable. + +If distribution of executable or object code is made by offering +access to copy from a designated place, then offering equivalent +access to copy the source code from the same place counts as +distribution of the source code, even though third parties are not +compelled to copy the source along with the object code. + + 4. You may not copy, modify, sublicense, or distribute the Program +except as expressly provided under this License. Any attempt +otherwise to copy, modify, sublicense or distribute the Program is +void, and will automatically terminate your rights under this License. +However, parties who have received copies, or rights, from you under +this License will not have their licenses terminated so long as such +parties remain in full compliance. + + 5. You are not required to accept this License, since you have not +signed it. However, nothing else grants you permission to modify or +distribute the Program or its derivative works. These actions are +prohibited by law if you do not accept this License. Therefore, by +modifying or distributing the Program (or any work based on the +Program), you indicate your acceptance of this License to do so, and +all its terms and conditions for copying, distributing or modifying +the Program or works based on it. + + 6. Each time you redistribute the Program (or any work based on the +Program), the recipient automatically receives a license from the +original licensor to copy, distribute or modify the Program subject to +these terms and conditions. You may not impose any further +restrictions on the recipients' exercise of the rights granted herein. +You are not responsible for enforcing compliance by third parties to +this License. + + 7. If, as a consequence of a court judgment or allegation of patent +infringement or for any other reason (not limited to patent issues), +conditions are imposed on you (whether by court order, agreement or +otherwise) that contradict the conditions of this License, they do not +excuse you from the conditions of this License. If you cannot +distribute so as to satisfy simultaneously your obligations under this +License and any other pertinent obligations, then as a consequence you +may not distribute the Program at all. For example, if a patent +license would not permit royalty-free redistribution of the Program by +all those who receive copies directly or indirectly through you, then +the only way you could satisfy both it and this License would be to +refrain entirely from distribution of the Program. + +If any portion of this section is held invalid or unenforceable under +any particular circumstance, the balance of the section is intended to +apply and the section as a whole is intended to apply in other +circumstances. + +It is not the purpose of this section to induce you to infringe any +patents or other property right claims or to contest validity of any +such claims; this section has the sole purpose of protecting the +integrity of the free software distribution system, which is +implemented by public license practices. Many people have made +generous contributions to the wide range of software distributed +through that system in reliance on consistent application of that +system; it is up to the author/donor to decide if he or she is willing +to distribute software through any other system and a licensee cannot +impose that choice. + +This section is intended to make thoroughly clear what is believed to +be a consequence of the rest of this License. + + 8. If the distribution and/or use of the Program is restricted in +certain countries either by patents or by copyrighted interfaces, the +original copyright holder who places the Program under this License +may add an explicit geographical distribution limitation excluding +those countries, so that distribution is permitted only in or among +countries not thus excluded. In such case, this License incorporates +the limitation as if written in the body of this License. + + 9. The Free Software Foundation may publish revised and/or new versions +of the General Public License from time to time. Such new versions will +be similar in spirit to the present version, but may differ in detail to +address new problems or concerns. + +Each version is given a distinguishing version number. If the Program +specifies a version number of this License which applies to it and "any +later version", you have the option of following the terms and conditions +either of that version or of any later version published by the Free +Software Foundation. If the Program does not specify a version number of +this License, you may choose any version ever published by the Free Software +Foundation. + + 10. If you wish to incorporate parts of the Program into other free +programs whose distribution conditions are different, write to the author +to ask for permission. For software which is copyrighted by the Free +Software Foundation, write to the Free Software Foundation; we sometimes +make exceptions for this. Our decision will be guided by the two goals +of preserving the free status of all derivatives of our free software and +of promoting the sharing and reuse of software generally. + + NO WARRANTY + + 11. BECAUSE THE PROGRAM IS LICENSED FREE OF CHARGE, THERE IS NO WARRANTY +FOR THE PROGRAM, TO THE EXTENT PERMITTED BY APPLICABLE LAW. EXCEPT WHEN +OTHERWISE STATED IN WRITING THE COPYRIGHT HOLDERS AND/OR OTHER PARTIES +PROVIDE THE PROGRAM "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER EXPRESSED +OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF +MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. THE ENTIRE RISK AS +TO THE QUALITY AND PERFORMANCE OF THE PROGRAM IS WITH YOU. SHOULD THE +PROGRAM PROVE DEFECTIVE, YOU ASSUME THE COST OF ALL NECESSARY SERVICING, +REPAIR OR CORRECTION. + + 12. IN NO EVENT UNLESS REQUIRED BY APPLICABLE LAW OR AGREED TO IN WRITING +WILL ANY COPYRIGHT HOLDER, OR ANY OTHER PARTY WHO MAY MODIFY AND/OR +REDISTRIBUTE THE PROGRAM AS PERMITTED ABOVE, BE LIABLE TO YOU FOR DAMAGES, +INCLUDING ANY GENERAL, SPECIAL, INCIDENTAL OR CONSEQUENTIAL DAMAGES ARISING +OUT OF THE USE OR INABILITY TO USE THE PROGRAM (INCLUDING BUT NOT LIMITED +TO LOSS OF DATA OR DATA BEING RENDERED INACCURATE OR LOSSES SUSTAINED BY +YOU OR THIRD PARTIES OR A FAILURE OF THE PROGRAM TO OPERATE WITH ANY OTHER +PROGRAMS), EVEN IF SUCH HOLDER OR OTHER PARTY HAS BEEN ADVISED OF THE +POSSIBILITY OF SUCH DAMAGES. + + END OF TERMS AND CONDITIONS + + How to Apply These Terms to Your New Programs + + If you develop a new program, and you want it to be of the greatest +possible use to the public, the best way to achieve this is to make it +free software which everyone can redistribute and change under these terms. + + To do so, attach the following notices to the program. It is safest +to attach them to the start of each source file to most effectively +convey the exclusion of warranty; and each file should have at least +the "copyright" line and a pointer to where the full notice is found. + + + Copyright (C) + + 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. + + You should have received a copy of the GNU General Public License along + with this program; if not, write to the Free Software Foundation, Inc., + 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA. + +Also add information on how to contact you by electronic and paper mail. + +If the program is interactive, make it output a short notice like this +when it starts in an interactive mode: + + Gnomovision version 69, Copyright (C) year name of author + Gnomovision comes with ABSOLUTELY NO WARRANTY; for details type `show w'. + This is free software, and you are welcome to redistribute it + under certain conditions; type `show c' for details. + +The hypothetical commands `show w' and `show c' should show the appropriate +parts of the General Public License. Of course, the commands you use may +be called something other than `show w' and `show c'; they could even be +mouse-clicks or menu items--whatever suits your program. + +You should also get your employer (if you work as a programmer) or your +school, if any, to sign a "copyright disclaimer" for the program, if +necessary. Here is a sample; alter the names: + + Yoyodyne, Inc., hereby disclaims all copyright interest in the program + `Gnomovision' (which makes passes at compilers) written by James Hacker. + + , 1 April 1989 + Ty Coon, President of Vice + +This General Public License does not permit incorporating your program into +proprietary programs. If your program is a subroutine library, you may +consider it more useful to permit linking proprietary applications with the +library. If this is what you want to do, use the GNU Lesser General +Public License instead of this License. diff --git a/Makefile b/Makefile new file mode 100644 index 0000000..bc545b7 --- /dev/null +++ b/Makefile @@ -0,0 +1,18 @@ +%.php: %.scm + @echo Creating $@ + @(./speakhtml.scm $< > $@ && ( tidy -q --indent yes --wrap 0 --vertical-space no --preserve-entities yes --drop-empty-elements no --drop-empty-paras no --show-body-only yes --warn-proprietary-attributes no --wrap-php no --keep-tabs yes -ashtml -m $@ ; test $$? -lt 2 && true ; ./unwrap-php.sh $@ ) ) || ( rm -f $@ ; false ) + +SCMFILES = $(wildcard *.scm) +SCMFILES := $(filter-out $(wildcard common.scm php.scm main.scm html4*.scm lang-tags.scm form.scm spinkit.scm speakhtml*.scm),$(SCMFILES)) +PHPFILES = $(SCMFILES:.scm=.php) + +all: php + +rebuild: clean all + +php: $(PHPFILES) + +phpclean: + rm -f $(PHPFILES) + +clean: phpclean diff --git a/addcomm.php b/addcomm.php new file mode 100644 index 0000000..6dbc29a --- /dev/null +++ b/addcomm.php @@ -0,0 +1,32 @@ + +prepare($sql); +$stmt->bind_param('i', $_POST['persid']); +$stmt->execute(); +$stmt->reset(); +$mysqli->close(); + +echo "1"; + +exit(0); diff --git a/addtemplate.php b/addtemplate.php new file mode 100644 index 0000000..29bc05f --- /dev/null +++ b/addtemplate.php @@ -0,0 +1,42 @@ + +prepare($sql); + $stmt->bind_param('ssssss', $_POST['name'], $_POST['text'], $_POST['from'], $_POST['cc'], $_POST['bcc'], $_POST['subject']); + $stmt->execute(); +} else if (isset($_POST['pdf'])) { + $sql = "INSERT INTO pdf_templates (name, text, subject, date) VALUES (?, ?, ?, ?);"; + + $stmt = $mysqli->prepare($sql); + $stmt->bind_param('ssss', $_POST['name'], $_POST['text'], $_POST['subject'], $_POST['date']); + $stmt->execute(); +} + +echo $stmt->insert_id; + +$stmt->reset(); +$mysqli->close(); + +exit(0); diff --git a/allocation.php b/allocation.php new file mode 100644 index 0000000..e6fd038 --- /dev/null +++ b/allocation.php @@ -0,0 +1,59 @@ + + + +

+ +prepare($sql); + $stmt->bind_param("i", $year); + $stmt->execute(); + $r = $stmt->get_result(); + $id = "Mittelverwendung"; + $order = '[[1, "asc"], [3, "asc"], [4, "asc"]]'; + $ajax = false; + $entrytable = 'Mittelverwendung'; + $nospinner = true; + include __DIR__ . '/autotable.php'; + $stmt->reset(); +})(); + +include_once __DIR__ . "/footer.php"; diff --git a/alumni.pers.php b/alumni.pers.php new file mode 100644 index 0000000..2183203 --- /dev/null +++ b/alumni.pers.php @@ -0,0 +1,64 @@ + + '2000-01-01 00:00:00' AND Personen.ID NOT IN (SELECT Person FROM Stipendien WHERE Stipendien.Förderende >= NOW()) +GROUP BY Personen.ID +"; + $id = "alumnipers"; + $getthdef = true; + $order = '[[2, "asc"], [3, "asc"]]'; + $checkboxes = true; + $idcell = "PersID"; + $ajax = true; + $nospinner = true; + include __DIR__ . '/autotable.php'; + + $pdf_sql = $email_sql = "SELECT *, ID AS PersID FROM Personen WHERE ID IN "; + include __DIR__ . '/autoactions.php'; + include __DIR__ . '/autoemail.php'; + include __DIR__ . '/autopdf.php'; + include __DIR__ . '/autoevent.php'; +})(); + +include_once __DIR__ . "/footer.php"; diff --git a/alumni.php b/alumni.php new file mode 100644 index 0000000..a42c6da --- /dev/null +++ b/alumni.php @@ -0,0 +1,19 @@ + + '2000-01-01 00:00:00' AND Personen.ID NOT IN (SELECT Person FROM Stipendien WHERE Stipendien.Förderende >= NOW())"; +require_once __DIR__ . '/stips_common.php'; diff --git a/auto_common.php b/auto_common.php new file mode 100644 index 0000000..9b74adb --- /dev/null +++ b/auto_common.php @@ -0,0 +1,180 @@ + + '/db/person/', + 'APID' => '/db/person/', + 'APZuwID' => '/db/person/', + 'ZustID' => '/db/person/', + 'StipID' => '/db/stip/', + 'FoerdID' => '/db/patron/', + 'VorhFoerdID' => '/db/patron/', + 'OrgaID' => '/db/orga/', + 'SuperOrgaID' => '/db/orga/', + 'SpendenID' => '/db/donation/', + 'SuperSpendenID' => '/db/donation/', + 'EventID' => '/db/event/', + 'SuperEventID' => '/db/event/', + 'EventCatID' => '/db/event/category/', + 'NotizID' => '/db/note/', + 'EmailID' => '/db/email/', + 'VertrID' => '/db/contract/', + 'KanbanID' => '/db/kanban/', + 'KanbanViewID' => '/db/kanban/view/', + 'KanbanBoardID' => '/db/kanban/board/', + 'ETID' => '/db/templates/email/', + 'PTID' => '/db/templates/pdf/', + 'AppNewsID' => '/db/main/news.php?id=', + 'AppSettingID' => '/db/main/appsetting.php?id=', + 'AppProfileID' => '/db/main/appprofile.php?id=', + 'AppCampaignID' => '/db/main/appcampaign.php?id=', + 'AppBillboardID' => '/db/main/appbillboard.php?id=', + 'AppNagscreenID' => '/db/main/appnagscreen.php?id=', + ]; + if (!is_array($links)) + return $links_default; + return array_merge($links_default, $links); +} + +function ajaxLinks() { +?> + { + render: function (data, type, row, meta) { + if (data === "" || data == null) return data; + return '' + data + ''; + }, targets: ['at_col_persid', 'at_col_PersID', 'at_col_APID', 'at_col_APZuwID', 'at_col_ZustID'] + }, + { + render: function (data, type, row, meta) { + if (data === "" || data == null) return data; + return '' + data + ''; + }, targets: 'at_col_StipID' + }, + { + render: function (data, type, row, meta) { + if (data === "" || data == null) return data; + return '' + data + ''; + }, targets: ['at_col_FoerdID', 'at_col_VorhFoerdID'] + }, + { + render: function (data, type, row, meta) { + if (data === "" || data == null) return data; + return '' + data + ''; + }, targets: ['at_col_OrgaID', 'at_col_SuperOrgaID'] + }, + { + render: function (data, type, row, meta) { + if (data === "" || data == null) return data; + return '' + data + ''; + }, targets: 'at_col_NotizID' + }, + { + render: function (data, type, row, meta) { + if (data === "" || data == null) return data; + return '' + data + ''; + }, targets: ['at_col_EventID', 'at_col_SuperEventID'] + }, + { + render: function (data, type, row, meta) { + if (data === "" || data == null) return data; + return '' + data + ''; + }, targets: 'at_col_EventCatID' + }, + { + render: function (data, type, row, meta) { + if (data === "" || data == null) return data; + return '' + data + ''; + }, targets: 'at_col_EmailID' + }, + { + render: function (data, type, row, meta) { + if (data === "" || data == null) return data; + return '' + data + ''; + }, targets: ['at_col_SpendenID', 'at_col_SuperSpendenID'] + }, + { + render: function (data, type, row, meta) { + if (data === "" || data == null) return data; + return '' + data + ''; + }, targets: 'at_col_VertrID' + }, + { + render: function (data, type, row, meta) { + if (data === "" || data == null) return data; + return '' + data + ''; + }, targets: 'at_col_KanbanID' + }, + { + render: function (data, type, row, meta) { + if (data === "" || data == null) return data; + return '' + data + ''; + }, targets: 'at_col_KanbanBoardID' + }, + { + render: function (data, type, row, meta) { + if (data === "" || data == null) return data; + return '' + data + ''; + }, targets: 'at_col_KanbanViewID' + }, + { + render: function (data, type, row, meta) { + if (data === "" || data == null) return data; + return '' + data + ''; + }, targets: 'at_col_AppNewsID' + }, + { + render: function (data, type, row, meta) { + if (data === "" || data == null) return data; + return '' + data + ''; + }, targets: 'at_col_AppSettingID' + }, + { + render: function (data, type, row, meta) { + if (data === "" || data == null) return data; + return '' + data + ''; + }, targets: 'at_col_AppProfileID' + }, + { + render: function (data, type, row, meta) { + if (data === "" || data == null) return data; + return '' + data + ''; + }, targets: 'at_col_AppCampaignID' + }, + { + render: function (data, type, row, meta) { + if (data === "" || data == null) return data; + return '' + data + ''; + }, targets: 'at_col_AppBillboardID' + }, + { + render: function (data, type, row, meta) { + if (data === "" || data == null) return data; + return '' + data + ''; + }, targets: 'at_col_AppNagscreenID' + }, + + + diff --git a/autoactions.php b/autoactions.php new file mode 100644 index 0000000..d37e18c --- /dev/null +++ b/autoactions.php @@ -0,0 +1,48 @@ + + + +
+ Ausgewählte +
+ +
+
+ + + diff --git a/autoeemail.php b/autoeemail.php new file mode 100644 index 0000000..aa1450c --- /dev/null +++ b/autoeemail.php @@ -0,0 +1,31 @@ + +
+
+ +
+
+ + diff --git a/autoemail.php b/autoemail.php new file mode 100644 index 0000000..cac5647 --- /dev/null +++ b/autoemail.php @@ -0,0 +1,80 @@ + + + +
+
+ + + + + + + + + +
+
+ + + + diff --git a/autoform.php b/autoform.php new file mode 100644 index 0000000..21f9caa --- /dev/null +++ b/autoform.php @@ -0,0 +1,646 @@ + + + + + + + + +
+ + + +
+ + + + + + + +
+ + + + + + diff --git a/autopdf.php b/autopdf.php new file mode 100644 index 0000000..9afe246 --- /dev/null +++ b/autopdf.php @@ -0,0 +1,79 @@ + + + +
+
+ + + + + + + + + + +
+
+ + + + diff --git a/autotable.php b/autotable.php new file mode 100644 index 0000000..93307f7 --- /dev/null +++ b/autotable.php @@ -0,0 +1,1103 @@ + +prepare($sql); + $stmt->execute(); + $r = $stmt->get_result(); + } + + if (!isset($noautolinks)) + $noautolinks = false; + + if (!isset($insert)) + $insert = pathinfo(basename(__FILE__), PATHINFO_FILENAME); +?> + +
+
+ + +

+ + + id="Table_off" class="fas fa-toggle-on"> + id="Table_on" class="fas fa-toggle-off"> +

+ + + + +
id="Table"> + + + + +
+ + +num_rows < 1)) { +?> + Keine Ergebnisse! + + + + +
+
+ + + + + + + + + + + +
+
+ + + +
+
+ + + + + + + + + + + +fetch_object(); + foreach ($l as $k => $v) { + $bk = str_replace(' ', '
', $k); + if (substr($k, 0, 1) === '%') { + $k = substr($k, 1); + } + + if ($editable[$k]) { +?> + + + + + +', $k); + if ($editable[$k]) { +?> + + + + + + + + + + + + += $r->num_rows) { +?> + +num_rows) { +?> + + + +$idcell; + else if (isset($l->ID)) $dataid = $l->ID; + else if (isset($l->uid)) $dataid = $l->uid; + else if (isset($l->UID)) $dataid = $l->UID; + else if (isset($l->id)) $dataid = $l->id; + else if (isset($l->PersID)) $dataid = $l->PersID; + else if (isset($l->StipID)) $dataid = $l->StipID; + else if (isset($l->EventID)) $dataid = $l->EventID; + else if (isset($l->FoerdID)) $dataid = $l->FoerdID; + else if (isset($l->OrgaID)) $dataid = $l->OrgaID; + else if (isset($l->NotizID)) $dataid = $l->NotizID; + else if (isset($l->EmailID)) $dataid = $l->EmailID; + else if (isset($l->AppNewsID)) $dataid = $l->AppNewsID; + else if (isset($l->AppSettingID)) $dataid = $l->AppSettingID; + else if (isset($l->AppProfileID)) $dataid = $l->AppProfileID; + else if (isset($l->ETID)) $dataid = $l->ETID; + + if ($makeseq) $dataid = $autotable_i; + + $persid = ""; + if (isset($l->PersID)) $persid = $l->PersID; + + $stipid = ""; + if (isset($l->StipID)) $stipid = $l->StipID; + + $orgaid = ""; + if (isset($l->OrgaID)) $orgaid = $l->OrgaID; + + $foerdid = ""; + if (isset($l->FoerdID)) $foerdid = $l->FoerdID; + + $eventid = ""; + if (isset($l->EventID)) $eventid = $l->EventID; +?> + + + + + + $v) { + if (substr($k, 0, 1) === '%') { + $v = utf8_encode(urldecode($v)); + $k = substr($k, 1); + } + if (isset($links[$k])) { +?> + + + + + + + +fetch_object())) +?> + + + + + + + + + + + + + + + +
+ + + + + +num_rows)) + $num_rows = $r->num_rows; + if (isset($stmt)) + $stmt->reset(); +})(); +?> diff --git a/autotable_explain.php b/autotable_explain.php new file mode 100644 index 0000000..76dec14 --- /dev/null +++ b/autotable_explain.php @@ -0,0 +1,36 @@ + +
+ + Filter + + + + Spalten + +
+ diff --git a/autotable_fontsettings.php b/autotable_fontsettings.php new file mode 100644 index 0000000..006b7d8 --- /dev/null +++ b/autotable_fontsettings.php @@ -0,0 +1,41 @@ + + + + + + + + + + diff --git a/calls.php b/calls.php new file mode 100644 index 0000000..51ff544 --- /dev/null +++ b/calls.php @@ -0,0 +1,65 @@ + + + +

+ +prepare($sql); + $stmt->execute(); + $r = $stmt->get_result(); + $id = "calls"; + $order = '[[2, "asc"], [8, "desc"]]'; + $editable = [ + 'app_start', + 'app_end', + 'default_valid_from', + 'default_valid_to' + ]; + $ajax = false; + $idcell = "shorthand"; + $entrytable = 'calls'; + $nospinner = true; + include __DIR__ . '/autotable.php'; + $stmt->reset(); +})(); +?> + +

Aktuell für Bewerbungen zugeordnete Calls

+ +
    +
  • +DS: +
  • +LÜ: + + diff --git a/commission.php b/commission.php new file mode 100644 index 0000000..ef410c9 --- /dev/null +++ b/commission.php @@ -0,0 +1,74 @@ + +Alle aktuellen Kommissionsmitglieder"; + +require_once __DIR__ . "/check_auth.php"; +require_once __DIR__ . "/../includes/common.php"; +include_once __DIR__ . "/header.php"; +require_once __DIR__ . "/lookup.php"; + +doTitle(); + +(function () use ($mysqli) { + $sql = " +SELECT + Kommissionsmitglieder.ID, Kommissionsmitglieder.Person AS PersID, Personen.Nachname, Personen.Vorname, + Hochschulen.KanonischerName AS Hochschule, Kommissionsmitglieder.Fakultät AS Fakultaet, + Kommissionsmitglieder.DezentraleKommission, Kommissionsmitglieder.ZentraleKommission, Kommissionsmitglieder.Sozialkommission, + Kommissionsmitglieder.Kommission, + Personen.Email, Personen.Straße, Personen.PLZ, Personen.Ort, Personen.Adresszusatz, + Kommissionen.Benutzername AS Kurzname, Kommissionen.Passwort, Kommissionen.`Webinterface Link` AS Onlineraum, Personen.Anrede, + Personen.Ansprache, Personen.Titel, Kommissionsmitglieder.Fakultät +FROM Kommissionsmitglieder +LEFT JOIN Personen ON Kommissionsmitglieder.Person = Personen.ID +LEFT JOIN Kommissionen ON Kommissionsmitglieder.Kommission = Kommissionen.ID +LEFT JOIN Hochschulen ON Kommissionsmitglieder.Hochschule = Hochschulen.ID +WHERE ((((Kommissionsmitglieder.Person)=Personen.ID) AND ((Kommissionsmitglieder.DezentraleKommission)=True)) OR (((Kommissionsmitglieder.ZentraleKommission)=True))) +"; + $stmt = $mysqli->prepare($sql); + $stmt->execute(); + $r = $stmt->get_result(); + $id = "commission"; + $order = "[[3, 'asc'], [4, 'asc']]"; + $checkboxes = true; + include __DIR__ . '/autotable.php'; + $stmt->reset(); + + $email_sql = $sql . ' AND Kommissionsmitglieder.ID IN '; + $payload_sql = "SELECT Personen.ID AS PersID, event_participants.token, event_participants.id AS PartID, events.* FROM event_participants INNER JOIN Personen ON event_participants.persid=Personen.ID INNER JOIN events ON event_participants.eventid = events.id WHERE events.id = 78 OR Personen.mpulsid IN "; + $independent_payload_sql = " +SELECT +Stipendien.*, Personen.*, + +ds.kommission, ds.bewertung, ds.foerderlimit, ds.rang, +leistung.commission + +FROM Stipendien + +LEFT JOIN ds ON Stipendien.mpulsid = ds.uid +LEFT JOIN leistung ON Stipendien.mpulsid = leistung.uid + +INNER JOIN Personen ON Stipendien.Person = Personen.ID + +WHERE Stipendien.Jahr = 2020 AND Stipendien.Förderbeginn >= '2020-10-01' +"; + include __DIR__ . '/autoactions.php'; + include __DIR__ . '/autoemail.php'; + include __DIR__ . '/autoevent.php'; +})(); + +include_once __DIR__ . "/footer.php"; diff --git a/commissioners.php b/commissioners.php new file mode 100644 index 0000000..ac389ca --- /dev/null +++ b/commissioners.php @@ -0,0 +1,161 @@ + + ['select', $kommissionen], + 'call' => ['', $calls] + ]; + $editable = [ + 'call', + 'Kommission' + ]; + $entrytable = 'commissioners'; + + $bottom = << + Ausgewählte + + + + +EOD; + $alwaysbottom = true; + + $pdf_sql = $email_sql = " +SELECT +CONCAT(commissioners.ID, 'commissioner', '-', Personen.ID, 'person') AS uid, +Personen.ID AS PersID, +Personen.*, +`call` AS `call`, +Kommissionen.Darstellungsname AS Kommission, +Kommissionen.`Webinterface Link` AS Link, +Kommissionen.`LinkLeistungsueberpruefung` AS LUeLink, +`call` AS `CallText`, +Kommissionen.Benutzername AS Shorthand + +FROM commissioners +LEFT JOIN Personen ON commissioners.Person = Personen.ID +LEFT JOIN Kommissionen ON commissioners.Kommission = Kommissionen.ID +WHERE commissioners.ID IN +"; + $independent_payload_sql = "SELECT Stipendien.*, Personen.*, leistung.commission FROM Stipendien INNER JOIN leistung ON Stipendien.mpulsid = leistung.uid INNER JOIN Personen ON Stipendien.Person = Personen.ID WHERE Stipendien.Jahr = 2022 AND Stipendien.Förderart=7 AND Stipendien.Förderbeginn >= '2022-10-01'"; + + $payload_sql = " +SELECT eventid, persid, token FROM event_participants WHERE eventid=647 AND '999999999' NOT IN +"; + + $independent_payload_sql = " +SELECT + + Stipendien.*, Personen.*, + ds.kommission AS komm_ds, ds.bewertung, ds.foerderlimit, ds.rang, + leistung.commission AS komm_leistung, + social.commission AS komm_sozial + +FROM Stipendien + +LEFT JOIN ds ON Stipendien.mpulsid = ds.uid +LEFT JOIN leistung ON Stipendien.mpulsid = leistung.uid +LEFT JOIN social ON Stipendien.mpulsid = social.commission + +INNER JOIN Personen ON Stipendien.Person = Personen.ID + +WHERE Stipendien.Jahr = 2023 AND Stipendien.Förderbeginn >= '2023-10-01' +"; + + $checkboxes = true; + include __DIR__ . '/autotable.php'; + include __DIR__ . '/autoactions.php'; + include __DIR__ . '/autoemail.php'; + include __DIR__ . '/autopdf.php'; + include __DIR__ . '/autoevent.php'; +})(); +?> + + + + +(neues Datenmodell)"; + +require_once __DIR__ . "/check_auth.php"; +require_once __DIR__ . "/../includes/common.php"; +include_once __DIR__ . "/header.php"; +require_once __DIR__ . "/lookup.php"; + +doTitle(); + +(function () use ($kommissionen, $calls) { + $sql = " +SELECT DISTINCT + Personen.ID AS PersID, + Personen.Nachname, + Personen.Vorname, + Personen.Email, + Personen.Straße, + Personen.Adresszusatz, + Personen.PLZ, + Personen.Ort, + GROUP_CONCAT(Hochschulen.KanonischerName SEPARATOR ', ') AS `Hochschule(n)`, + GROUP_CONCAT(Kommissionen.Darstellungsname SEPARATOR ', ') AS `Kommission(en) Name`, + GROUP_CONCAT(commissioners.`call` SEPARATOR ', ') AS `Calls`, + Personen.Anrede, + Personen.Ansprache, + Personen.`informale Ansprache`, + Personen.Titel + +FROM commissioners + +LEFT JOIN Personen ON commissioners.Person = Personen.ID +LEFT JOIN Kommissionen ON commissioners.Kommission = Kommissionen.ID +LEFT JOIN Hochschulen ON Kommissionen.Hochschule = Hochschulen.ID + +GROUP BY Personen.ID + +"; + $id = "commissioners_new3"; + $getthdef = true; + $order = '[[1, "desc"]]'; + $entrytable = 'Personen'; + $idcell = "PersID"; + $idcellreal = "ID"; + $editable = ['Nachname', 'Vorname', 'Email', 'Straße', 'Adresszusatz', 'PLZ', 'Ort', 'Anrede', 'Ansprache', 'informale Ansprache', 'Titel']; + + $email_sql = " +SELECT DISTINCT CONCAT(Personen.ID, 'person') AS uid, Personen.*, Personen.Straße AS Strasse, Personen.ID AS PersID +FROM Personen +WHERE Personen.ID IN +"; + $ajax = false; + $pdf_sql = $email_sql; + $checkboxes = true; + include __DIR__ . '/autotable.php'; + include __DIR__ . '/autoactions.php'; + include __DIR__ . '/autoemail.php'; + include __DIR__ . '/autopdf.php'; + include __DIR__ . '/autoevent.php'; +})(); +?> + + + + diff --git a/commissions.php b/commissions.php new file mode 100644 index 0000000..0c6f754 --- /dev/null +++ b/commissions.php @@ -0,0 +1,61 @@ + + + +

    + +prepare($sql); + $stmt->execute(); + $r = $stmt->get_result(); + $id = "commissions"; + $order = '[[2, "asc"]]'; + $entrytable = 'Kommissionen'; + $editable = [ + 'Name', + 'Quote', + ]; + $links = [ + ]; + include __DIR__ . '/autotable.php'; + $stmt->reset(); +})(); + +include_once __DIR__ . "/footer.php"; diff --git a/composer.json b/composer.json new file mode 100644 index 0000000..f30327f --- /dev/null +++ b/composer.json @@ -0,0 +1,9 @@ +{ + "require": { + "twig/twig": "<3.0", + "mpdf/mpdf": ">=7.1", + "phpoffice/phpspreadsheet": ">=1.6", + "matthiasmullie/minify": ">=1.3", + "symfony/http-foundation": "^5.4" + } +} diff --git a/contract.php b/contract.php new file mode 100644 index 0000000..e180142 --- /dev/null +++ b/contract.php @@ -0,0 +1,111 @@ + + 0, SUM(Spenden.Betrag), 0)) AS Eingang, + IF(Förderer.`SEPA-Lastschrift erteilt`, 0, ((150 * contracts.ss_months) + (150 * contracts.ls_months)) - IF(SUM(Spenden.Betrag) > 0, SUM(Spenden.Betrag), 0)) AS Fehlbetrag +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 +WHERE contracts.id=? +"; + $stmt = $mysqli->prepare($sql); + $stmt->bind_param('i', $_GET['id']); + $stmt->execute(); + $r = $stmt->get_result(); + $l = $r->fetch_object(); + $foerdID = $l->FoerdID; + $persID = $l->PersID; + $orgaID = $l->OrgaID; + + $_title = 'Vertrag (ID ' . $_GET['id'] . ')' . ' zu: ' . $l->Förderer . ''; + include_once __DIR__ . "/header.php"; +?> + +

    +  +  + +prepare($sql); + $stmt->bind_param('i', $orgaID); + $stmt->bind_result($id, $nachname, $vorname); + $stmt->execute(); + $persXorga = []; + while ($stmt->fetch()) { + $persXorga[$id] = $nachname . ", " . $vorname; + } + $stmt->reset(); + + $id = "contract"; + $entrytable = "contracts"; + $def = [ + "Förderer", + [10, '_FoerdID', '_=3Förderer', '_=3legal|rechtsformen~Rechtsform', '_=3Zuständig|demo'], + "Vertrag", + [10, '=2call#string|callsByID', 'valid_from#isodate~gültig von', 'valid_to#isodate~gültig bis', '=2ls#number~Anzahl Leistungsstipendien', 'ls_months#number~Monate', '=2ss#number~Anzahl Sozialstipendien', 'ss_months#number~Monate'], + [10, '_APID', '=5contact|persXorga~Ansprechpartner', '_Summe', '_Eingang', '_=2Fehlbetrag'], + [10, '=9remark~Anmerkung', '+=1kontrolliert'], + ['*widmung'], + ]; + + include __DIR__ . '/autoform.php'; +})(); + +(function () { + $title = "Spenden zum Vertrag"; + $id = "donationsXcontract2"; + $sql = " +SELECT + Spenden.ID AS SpendenID, + Spenden.Person AS PersID, + Personen.Nachname, + Personen.Vorname, + Spenden.Organisation AS OrgaID, + Organisationen.Name AS Organisation, + Spenden.Betrag, + DATE(Spenden.Geldeingang) AS Geldeingang +FROM Spenden +LEFT JOIN Personen ON Spenden.Person = Personen.ID +LEFT JOIN Organisationen ON Spenden.Organisation = Organisationen.ID +WHERE Spenden.contract=" . $_GET['id']; + + $thdef = ['SpendenID', 'PersID', 'Nachname', 'Vorname', 'OrgaID', 'Organisation', 'Betrag', 'Geldeingang']; + $order = '[[1, "desc"]]'; + $ajax = $nospinner = true; + include __DIR__ . '/autotable.php'; +})(); + +include_once __DIR__ . '/footer.php'; diff --git a/contracts.php b/contracts.php new file mode 100644 index 0000000..a9d4d53 --- /dev/null +++ b/contracts.php @@ -0,0 +1,19 @@ + + + +

    Der/Die Ansprechpartner Finanzen (apfi) werden für den E-Mail-Versand benutzt. Auf AP Zuwendungen zurückschalten.

    +

    Der Ansprechpartner Zuwendungen ist Adressat des PDF-Dokuments.

    + +

    Der Ansprechpartner Zuwendungen des Förderers wird für den E-Mail-Versand benutzt. Auf AP Finanzen schalten.

    +

    Der Ansprechpartner Zuwendungen ist Adressat des PDF-Dokuments.

    + 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 = << + Ausgewählte + + +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(); +})(); +?> + + + + diff --git a/curfutpatrons.php b/curfutpatrons.php new file mode 100644 index 0000000..de9642a --- /dev/null +++ b/curfutpatrons.php @@ -0,0 +1,19 @@ + +(anhand Verträge)"; +$_constraint = "WHERE (contracts.valid_to >= NOW() OR contracts.valid_to IS NULL) AND contracts.id > 0 GROUP BY Förderer.ID"; +require_once __DIR__ . "/patrons_common.php"; diff --git a/curfutpatronspersons.php b/curfutpatronspersons.php new file mode 100644 index 0000000..c075562 --- /dev/null +++ b/curfutpatronspersons.php @@ -0,0 +1,27 @@ + +(anhand Verträge)"; + +$call = '%'; +if (isset($_GET['call']) && $_GET['call'] != '') { + $call = filter_var($_GET['call'], FILTER_SANITIZE_STRING); + $_title .= " (Call " . $call . ")"; +} + +$_constraint = "WHERE Förderer.ID IN (SELECT contracts.patron FROM contracts WHERE (contracts.valid_to >= NOW()) AND (contracts.`call` LIKE '" . $call . "'))"; + +require __DIR__ . "/patronspersons_common.php"; diff --git a/curpatrons.php b/curpatrons.php new file mode 100644 index 0000000..e7afd1c --- /dev/null +++ b/curpatrons.php @@ -0,0 +1,19 @@ + +(altes Datenmodell, anhand Häkchen)"; +$_constraint = "WHERE Förderer.`aktuell Förderer` = 1"; +require_once __DIR__ . "/patrons_common.php"; diff --git a/curstips.php b/curstips.php new file mode 100644 index 0000000..7a46376 --- /dev/null +++ b/curstips.php @@ -0,0 +1,19 @@ + += NOW() AND Stipendien.Förderbeginn <= NOW()"; +require_once __DIR__ . '/stips_common.php'; diff --git a/curstipspers.php b/curstipspers.php new file mode 100644 index 0000000..57728e8 --- /dev/null +++ b/curstipspers.php @@ -0,0 +1,58 @@ + + NOW() +GROUP BY Personen.ID +"; + $stmt = $mysqli->prepare($sql); + $stmt->execute(); + $r = $stmt->get_result(); + $id = "curfutpersons"; + $order = '[[2, "asc"], [3, "asc"]]'; + $checkboxes = true; + include __DIR__ . '/autotable.php'; + $stmt->reset(); + + $pdf_sql = $email_sql = "SELECT Personen.*, Personen.ID AS PersID FROM Personen WHERE ID IN "; + include __DIR__ . '/autoactions.php'; + include __DIR__ . '/autoemail.php'; + include __DIR__ . '/autopdf.php'; + include __DIR__ . '/autoevent.php'; +})(); + +include_once __DIR__ . "/footer.php"; diff --git a/delay.php b/delay.php new file mode 100644 index 0000000..ca28a00 --- /dev/null +++ b/delay.php @@ -0,0 +1,22 @@ + + 1) + usleep($_POST['time']*1000); +else + usleep(60*1000); +echo "true"; +exit(0); diff --git a/delcomm.php b/delcomm.php new file mode 100644 index 0000000..611ba31 --- /dev/null +++ b/delcomm.php @@ -0,0 +1,20 @@ + + + +prepare($sql); + $stmt->bind_param('i', $contractID); + $stmt->execute(); + $r = $stmt->get_result(); + $l = $r->fetch_object(); + + $_title = '' . $l->Name . ', ' . $l->call . ' (ID ' . $contractID . ')'; + include_once __DIR__ . "/header.php"; +?> +

    Vertragsdatensatz löschen:

    +id < 1) { +?> +

    Kein Datensatz mit dieser ID gefunden!

    +

    Wenn Sie gerade gelöscht haben, war das Löschen erfolgreich.

    + + + +reset(); +})(); + +(function () use ($contractID) { + $globstring = "{" . $contractID . "contract}_"; + require_once __DIR__ . "/doc.php"; +})(); + +(function () use ($mysqli, $contractID, &$num_rows) { + $title = "Spenden"; + $nochosen = $nofilter = true; + $sql = " +SELECT + Spenden.ID AS SpendenID, + Spenden.Organisation AS OrgaID, + Organisationen.Name AS Organisation, + CONCAT(calls.name, ' [', contracts.call, '] - ', ContractOrga.Name) AS Vertrag, + Betrag, + DATE(Geldeingang) AS Geldeingang +FROM Spenden + LEFT JOIN Organisationen ON Spenden.Organisation = Organisationen.ID + LEFT JOIN contracts ON Spenden.contract = contracts.id + LEFT JOIN Förderer AS ContractPatron ON contracts.patron = ContractPatron.ID + LEFT JOIN Organisationen AS ContractOrga ON ContractPatron.Organisation = ContractOrga.ID + LEFT JOIN calls ON contracts.`call` = calls.shorthand +WHERE Spenden.contract = ? +"; + $stmt = $mysqli->prepare($sql); + $stmt->bind_param("i", $contractID); + $stmt->execute(); + $r = $stmt->get_result(); + include __DIR__ . '/autotable.php'; + $stmt->reset(); +})(); +if ($num_rows) { + $prohibit = true; +?> +

    Es existieren verknüpfte Spendendatensätze. Löschen daher nicht möglich.

    + + +
    + + + +

    Löschen derzeit nicht möglich. Zunächst Datensätze umwidmen bzw. Verknüpfungen entfernen.

    + + + + + + +prepare($sql); + $stmt->bind_param("i", $_POST['id']); + $stmt->execute(); + echo $mysqli->affected_rows; + $stmt->reset(); +} else { + echo "Ungültiger Aufruf (@2)."; +} + +exit(0); diff --git a/deldocument.php b/deldocument.php new file mode 100644 index 0000000..a677367 --- /dev/null +++ b/deldocument.php @@ -0,0 +1,46 @@ + + + +

    Das Dokument wurde gelöscht.

    + +

    + +

    + + diff --git a/deldonation.php b/deldonation.php new file mode 100644 index 0000000..b4621f2 --- /dev/null +++ b/deldonation.php @@ -0,0 +1,20 @@ + + +prepare($sql); + $stmt->bind_param('i', $orgaID); + $stmt->execute(); + $r = $stmt->get_result(); + $l = $r->fetch_object(); + + $_title = '' . $l->Name . ' (ID ' . $orgaID . ')'; + include_once __DIR__ . "/header.php"; +?> +

    Orgadatensatz löschen:

    +ID < 1) { +?> +

    Kein Datensatz mit dieser ID gefunden!

    +

    Wenn Sie gerade gelöscht haben, war das Löschen erfolgreich.

    + + + +reset(); +})(); + +(function () use ($orgaID) { + $globstring = "{" . $orgaID . "orga}_"; + require_once __DIR__ . "/doc.php"; +})(); + +(function () use ($mysqli, $orgaID, &$num_rows) { + $title = "Spenden"; + $nochosen = $nofilter = true; + $sql = " +SELECT + Spenden.ID AS SpendenID, + contract AS VertrID, + CONCAT(calls.name, ' [', contracts.call, '] - ', ContractOrga.Name) AS Vertrag, + Betrag, + DATE(Geldeingang) AS Geldeingang +FROM Spenden + LEFT JOIN contracts ON Spenden.contract = contracts.id + LEFT JOIN Förderer AS ContractPatron ON contracts.patron = ContractPatron.ID + LEFT JOIN Organisationen AS ContractOrga ON ContractPatron.Organisation = ContractOrga.ID + LEFT JOIN calls ON contracts.`call` = calls.shorthand +WHERE Spenden.Organisation=? OR ContractOrga.ID = ? +"; + $stmt = $mysqli->prepare($sql); + $stmt->bind_param("ii", $orgaID, $orgaID); + $stmt->execute(); + $r = $stmt->get_result(); + include __DIR__ . '/autotable.php'; + $stmt->reset(); +})(); +if ($num_rows) { + $prohibit = true; +?> +

    Es existieren Spendendatensätze, die direkt oder indirekt mit diesem Orgadatensatz verknüpft sind. Löschen daher nicht möglich.

    += '2000-01-01' +"; + $stmt = $mysqli->prepare($sql); + $stmt->bind_param("i", $orgaID); + $stmt->execute(); + $r = $stmt->get_result(); + include __DIR__ . '/autotable.php'; + $stmt->reset(); +})(); +if ($num_rows) { + $prohibit = true; +?> +

    Es existieren Stipendiendatensätze, die über den Fördererdatensatz mit diesem Orgadatensatz verknüpft sind. Löschen daher nicht möglich.

    +prepare($sql); + $stmt->bind_param("i", $orgaID); + $stmt->execute(); + $r = $stmt->get_result(); + include __DIR__ . '/autotable.php'; + $stmt->reset(); +})(); +if ($num_rows) { + $prohibit = true; +?> +

    Es existieren Notizendatensätze, die mit diesem Orgadatensatz verknüpft sind. Löschen daher nicht möglich.

    +prepare($sql); + $stmt->bind_param("i", $orgaID); + $stmt->execute(); + $r = $stmt->get_result(); + include __DIR__ . '/autotable.php'; + $stmt->reset(); +})(); +if ($num_rows) { + $prohibit = true; +?> +

    Es existieren Verknüpfungen zu Organisationen, bei der dieser diese Orga die übergeordnete Orga ist. Löschen daher nicht möglich.

    +prepare($sql); + $stmt->bind_param("i", $orgaID); + $stmt->execute(); + $r = $stmt->get_result(); + include __DIR__ . '/autotable.php'; + $stmt->reset(); +})(); +if ($num_rows) { + $prohibit = true; +?> +

    Dieser Orga ist ein Fördererdatensatz zugeordnet. Löschen daher nicht möglich. Bitte zunächst den Fördererdatensatz löschen.

    +prepare($sql); + $stmt->bind_param("i", $orgaID); + $stmt->execute(); + $r = $stmt->get_result(); + include __DIR__ . '/autotable.php'; + $stmt->reset(); +})(); +if ($num_rows) { + $prohibit = true; +?> +

    Es existieren Verknüpfungen in Vertragsdatensätzen. Löschen daher nicht möglich.

    +prepare($sql); + $stmt->bind_param("i", $orgaID); + $stmt->execute(); + $r = $stmt->get_result(); + include __DIR__ . '/autotable.php'; + $stmt->reset(); +})(); +if ($num_rows) { + $prohibit = true; +?> +

    Es existieren Verknüpfungen zu Events. Löschen daher nicht möglich.

    + + +
    + + + +

    Löschen derzeit nicht möglich. Zunächst Datensätze umwidmen bzw. Verknüpfungen entfernen.

    + + + + + + + +prepare($sql); + $stmt->bind_param("i", $_POST['id']); + $stmt->execute(); + echo $mysqli->affected_rows; + $stmt->reset(); +} else { + echo "Ungültiger Aufruf (@2)."; +} + +exit(0); diff --git a/delorgaperson.php b/delorgaperson.php new file mode 100644 index 0000000..578b5df --- /dev/null +++ b/delorgaperson.php @@ -0,0 +1,20 @@ + + +prepare($sql); + $stmt->bind_param('i', $foerdID); + $stmt->execute(); + $r = $stmt->get_result(); + $l = $r->fetch_object(); + + $_title = '' . $l->Name . ' (ID ' . $foerdID . ')'; + include_once __DIR__ . "/header.php"; +?> +

    Fördererdatensatz löschen:

    +ID < 1) { +?> +

    Kein Datensatz mit dieser ID gefunden!

    +

    Wenn Sie gerade gelöscht haben, war das Löschen erfolgreich.

    + + + +reset(); +})(); + +(function () use ($foerdID) { + $globstring = "{" . $foerdID . "patron}_"; + require_once __DIR__ . "/doc.php"; +})(); + +(function () use ($mysqli, $foerdID, &$num_rows) { + $title = "Stipendien"; + $nochosen = $nofilter = true; + $sql = " +SELECT + Stipendien.ID AS StipID, + Stipendien.Jahr AS Jahr, + Förderarten.Name AS Förderart +FROM Stipendien + LEFT JOIN Förderarten ON Stipendien.Förderart = Förderarten.ID +WHERE Stipendien.Förderer = ? +"; + $stmt = $mysqli->prepare($sql); + $stmt->bind_param("i", $foerdID); + $stmt->execute(); + $r = $stmt->get_result(); + include __DIR__ . '/autotable.php'; + $stmt->reset(); +})(); +if ($num_rows) { + $prohibit = true; +?> +

    Es existieren vernknüpfte Stipendiendatensätze. Löschen daher nicht möglich.

    +prepare($sql); + $stmt->bind_param("i", $foerdID); + $stmt->execute(); + $r = $stmt->get_result(); + include __DIR__ . '/autotable.php'; + $stmt->reset(); +})(); +if ($num_rows) { + $prohibit = true; +?> +

    Es existieren vernknüpfte Notizendatensätze. Löschen daher nicht möglich.

    +prepare($sql); + $stmt->bind_param("i", $foerdID); + $stmt->execute(); + $r = $stmt->get_result(); + include __DIR__ . '/autotable.php'; + $stmt->reset(); +})(); +if ($num_rows) { + $prohibit = true; +?> +

    Es existieren Verknüpfungen in Vertragsdatensätzen. Löschen daher nicht möglich.

    + + +
    + + + +

    Löschen derzeit nicht möglich. Zunächst Datensätze umwidmen bzw. Verknüpfungen entfernen.

    + + + + + + + +prepare($sql); + $stmt->bind_param("i", $_POST['id']); + $stmt->execute(); + echo $mysqli->affected_rows; + $stmt->reset(); +} else { + echo "Ungültiger Aufruf (@2)."; +} + +exit(0); diff --git a/delperson.php b/delperson.php new file mode 100644 index 0000000..001ad5e --- /dev/null +++ b/delperson.php @@ -0,0 +1,385 @@ + +prepare($sql); + $stmt->bind_param('i', $persID); + $stmt->execute(); + $r = $stmt->get_result(); + $l = $r->fetch_object(); + + $_title = '' . $l->Nachname . ', ' . $l->Vorname . ' (ID ' . $persID . ')'; + include_once __DIR__ . "/header.php"; +?> +

    Personendatensatz löschen:

    +ID < 1) { +?> +

    Kein Datensatz mit dieser ID gefunden!

    +

    Wenn Sie gerade gelöscht haben, war das Löschen erfolgreich.

    + + + +reset(); +})(); +?> + + + +prepare($sql); + $stmt->bind_param("i", $persID); + $stmt->execute(); + $r = $stmt->get_result(); + include __DIR__ . '/autotable.php'; + $stmt->reset(); +})(); +if ($num_rows) { + $prohibit = true; +?> +

    Es existieren Spendendatensätze, die mit diesem Personendatensatz verknüpft sind. Löschen daher nicht möglich.

    + + += '2000-01-01' +"; + $stmt = $mysqli->prepare($sql); + $stmt->bind_param("i", $persID); + $stmt->execute(); + $r = $stmt->get_result(); + include __DIR__ . '/autotable.php'; + $stmt->reset(); +})(); +if ($num_rows) { + $prohibit = true; +?> +

    Es existieren Stipendiendatensätze, die mit diesem Personendatensatz verknüpft sind. Löschen daher nicht möglich.

    + + +prepare($sql); + $stmt->bind_param("i", $persID); + $stmt->execute(); + $r = $stmt->get_result(); + include __DIR__ . '/autotable.php'; + $stmt->reset(); +})(); +if ($num_rows) { + $prohibit = true; +?> +

    Es existieren Notizendatensätze, die mit diesem Personendatensatz verknüpft sind. Löschen daher nicht möglich.

    + + +prepare($sql); + $stmt->bind_param("i", $persID); + $stmt->execute(); + $r = $stmt->get_result(); + include __DIR__ . '/autotable.php'; + $stmt->reset(); +})(); +if ($num_rows) { + $prohibit = true; +?> +

    Es existieren Verknüpfungen zu Organisationen. Löschen daher nicht möglich.

    + + + +prepare($sql); + $stmt->bind_param("ii", $persID, $persID); + $stmt->execute(); + $r = $stmt->get_result(); + include __DIR__ . '/autotable.php'; + $stmt->reset(); +})(); +if ($num_rows) { + $prohibit = true; +?> +

    Es existieren Verknüpfungen in Fördererdatensätzen. Löschen daher nicht möglich.

    + + +prepare($sql); + $stmt->bind_param("i", $persID); + $stmt->execute(); + $r = $stmt->get_result(); + include __DIR__ . '/autotable.php'; + $stmt->reset(); +})(); +if ($num_rows) { + $prohibit = true; +?> +

    Es existieren Verknüpfungen in Vertragsdatensätzen. Löschen daher nicht möglich.

    + + +prepare($sql); + $stmt->bind_param("i", $persID); + $stmt->execute(); + $r = $stmt->get_result(); + include __DIR__ . '/autotable.php'; + $stmt->reset(); +})(); +if ($num_rows) { + $prohibit = true; +?> +

    Es existieren Verknüpfungen zu Kommissionen. Löschen daher nicht möglich.

    + + +prepare($sql); + $stmt->bind_param("i", $persID); + $stmt->execute(); + $r = $stmt->get_result(); + include __DIR__ . '/autotable.php'; + $stmt->reset(); +})(); +if ($num_rows) { + $prohibit = true; +?> +

    Es existieren Verknüpfungen zu Events. Löschen daher nicht möglich.

    + + + +prepare($sql); + $stmt->bind_param("i", $persID); + $stmt->execute(); + $r = $stmt->get_result(); + include __DIR__ . '/autotable.php'; + $stmt->reset(); +})(); +if ($num_rows) { + $prohibit = true; +?> +

    Es existieren verknüpfte E-Mails. Löschen daher nicht möglich.

    + + + + +
    + + + +

    Löschen derzeit nicht möglich. Zunächst Datensätze umwidmen bzw. Verknüpfungen entfernen.

    + + + + + + diff --git a/delpersondo.php b/delpersondo.php new file mode 100644 index 0000000..0af06c7 --- /dev/null +++ b/delpersondo.php @@ -0,0 +1,70 @@ + +prepare($sql); + $stmt->bind_param("i", $_POST['id']); + $stmt->execute(); + echo $mysqli->affected_rows; + $stmt->reset(); +} else if ($what === 'event') { + $sql = "DELETE FROM event_participants WHERE persid=? LIMIT 500;"; + $stmt = $mysqli->prepare($sql); + $stmt->bind_param("i", $_POST['id']); + $stmt->execute(); + echo $mysqli->affected_rows; + $stmt->reset(); +} else if ($what === 'email') { + $sql = "DELETE FROM mails WHERE persid=? LIMIT 1000;"; + $stmt = $mysqli->prepare($sql); + $stmt->bind_param("i", $_POST['id']); + $stmt->execute(); + echo $mysqli->affected_rows; + $stmt->reset(); +} else if ($what === 'emailrewrite') { + $sql = "UPDATE mails SET persid=NULL WHERE persid=? LIMIT 1000;"; + $stmt = $mysqli->prepare($sql); + $stmt->bind_param("i", $_POST['id']); + $stmt->execute(); + echo $mysqli->affected_rows; + $stmt->reset(); +} else if ($what === 'person') { + $sql = "DELETE FROM Personen WHERE ID=? LIMIT 1;"; + $stmt = $mysqli->prepare($sql); + $stmt->bind_param("i", $_POST['id']); + $stmt->execute(); + echo $mysqli->affected_rows; + $stmt->reset(); +} else { + echo "Ungültiger Aufruf (@2)."; +} + +exit(0); +?> diff --git a/delstip.php b/delstip.php new file mode 100644 index 0000000..fd644a8 --- /dev/null +++ b/delstip.php @@ -0,0 +1,133 @@ + +prepare($sql); + $stmt->bind_param('i', $stipID); + $stmt->execute(); + $r = $stmt->get_result(); + $l = $r->fetch_object(); + + $_title = '' . $l->Nachname . ', ' . $l->Vorname . ' (' . $l->Jahr . ') (ID ' . $stipID . ')'; + include_once __DIR__ . "/header.php"; +?> +

    Stipendiendatensatz löschen:

    +ID < 1) { +?> +

    Kein Datensatz mit dieser ID gefunden!

    +

    Wenn Sie gerade gelöscht haben, war das Löschen erfolgreich.

    + + + +reset(); +})(); + +(function () use ($stipID) { + $globstring = "{" . $stipID . "stip}_"; + require_once __DIR__ . "/doc.php"; +})(); + +(function () use ($stipID) { + $globstring = $stipID . "_"; + require_once __DIR__ . "/doc.php"; +})(); + +(function () use ($mysqli, $stipID, &$num_rows) { + $title = "Notizen"; + $nochosen = $nofilter = true; + $sql = " +SELECT + id AS NotizID, + title AS Titel, + solved AS erledigt, + changets AS `Letzte Änderung` +FROM notes +WHERE stipid=? +"; + $stmt = $mysqli->prepare($sql); + $stmt->bind_param("i", $stipID); + $stmt->execute(); + $r = $stmt->get_result(); + include __DIR__ . '/autotable.php'; + $stmt->reset(); +})(); +if ($num_rows) { + $prohibit = true; +?> +

    Es existieren Notizendatensätze, die mit diesem Personendatensatz verknüpft sind. Löschen daher nicht möglich.

    + + +
    + + + +

    Löschen derzeit nicht möglich. Zunächst Datensätze umwidmen bzw. Verknüpfungen entfernen.

    + + + + + + +prepare($sql); + $stmt->bind_param("i", $_POST['id']); + $stmt->execute(); + echo $mysqli->affected_rows; + $stmt->reset(); +} else { + echo "Ungültiger Aufruf (@2)."; +} + +exit(0); diff --git a/deltemplate.php b/deltemplate.php new file mode 100644 index 0000000..bfe2126 --- /dev/null +++ b/deltemplate.php @@ -0,0 +1,37 @@ + +prepare($sql); +$stmt->bind_param('i', $_POST['id']); +$stmt->execute(); +$stmt->reset(); +$mysqli->close(); + +echo "1"; + +exit(0); diff --git a/donation.php b/donation.php new file mode 100644 index 0000000..dd5a29e --- /dev/null +++ b/donation.php @@ -0,0 +1,206 @@ + + 0, FPerson.Anrede, Personen.Anrede) AS Anrede, + IF(Förderer.zuwendungen > 0, FPerson.Ansprache, Personen.Ansprache) AS Ansprache, + IF(Förderer.zuwendungen > 0, FPerson.Vorname, Personen.Vorname) AS Vorname, + IF(Förderer.zuwendungen > 0, FPerson.Nachname, Personen.Nachname) AS Nachname, + IF(Förderer.zuwendungen > 0, FPerson.Email, Personen.Email) AS Email, + Förderer.zahlungsanmerkung AS Zahlungsanmerkung, + + SuperSpende.Betrag AS SupBetrag, + DATE(SuperSpende.Geldeingang) AS SupGeldeingang, + + SuperSpende.Person AS SupPerson, + CONCAT(SuperSpendePerson.Nachname, ', ', SuperSpendePerson.Vorname) AS SupPersonName, + + SuperSpende.Organisation AS SupOrga, + SuperSpendeOrga.Name AS SupOrgaName, + + (SuperSpende.Betrag - (SELECT SUM(SumSpende.Betrag) AS BetrUsed FROM Spenden AS SumSpende WHERE SumSpende.SuperID = Spenden.SuperID)) AS SupUnused + +FROM Spenden + +LEFT JOIN Personen ON Spenden.Person = Personen.ID +LEFT JOIN Organisationen ON Spenden.Organisation = Organisationen.ID +LEFT JOIN Förderer ON Förderer.Organisation = Organisationen.ID +LEFT JOIN Spendenarten ON Spenden.Förderart = Spendenarten.ID +LEFT JOIN Personen AS APerson ON Spenden.`Spendenquittung Person` = APerson.ID +LEFT JOIN Personen AS FPerson ON Förderer.zuwendungen = FPerson.ID + +LEFT JOIN Spenden AS SuperSpende ON Spenden.SuperID = SuperSpende.ID +LEFT JOIN Personen AS SuperSpendePerson ON SuperSpende.Person = SuperSpendePerson.ID +LEFT JOIN Organisationen AS SuperSpendeOrga ON SuperSpende.Organisation = SuperSpendeOrga.ID +"; + + $email_sql = $sql . " WHERE Spenden.ID IN "; + $sql .= "WHERE Spenden.ID=?"; + $stmt = $mysqli->prepare($sql); + $stmt->bind_param('i', $donationID); + $stmt->execute(); + $r = $stmt->get_result(); + $l = $r->fetch_object(); + $persID = $l->PersID; + $orgaID = $l->OrgaID; + + $_title = 'Spende (ID ' . $donationID . ')'; + include_once __DIR__ . "/header.php"; + + $pdf_sql = " +SELECT + Spenden.ID AS SpendenID, + Person AS PersID, + CONCAT ( + IF(Spenden.Organisation > 0, CONCAT(Organisationen.ID, 'orga', '-'), ''), + IF(Förderer.zuwendungen > 0, CONCAT(FPerson.ID, 'person', '-'), ''), + IF(Spenden.Person > 0, CONCAT(Spenden.Person, 'person', '-'), ''), + Spenden.ID, 'donation' + ) AS uid, + CONCAT(Personen.Nachname, ', ', Personen.Vorname) AS Name, + Spenden.Organisation AS OrgaID, + Organisationen.Name AS Organisation, + Kommentar, + Betrag, + DATE(Geldeingang) AS Geldeingang, + Förderjahr, + Spendenarten.Name AS Spendenart, + `Spendenquittung Person` AS QuittPersID, + CONCAT(APerson.Nachname, ', ', APerson.Vorname) AS `Spendenquittung Person`, + IF(Förderer.zuwendungen > 0, FPerson.Anrede, Personen.Anrede) AS Anrede, + IF(Förderer.zuwendungen > 0, FPerson.Ansprache, Personen.Ansprache) AS Ansprache, + IF(Förderer.zuwendungen > 0, FPerson.Vorname, Personen.Vorname) AS Vorname, + IF(Förderer.zuwendungen > 0, FPerson.Nachname, Personen.Nachname) AS Nachname, + IF(Förderer.zuwendungen > 0, FPerson.Email, Personen.Email) AS Email, + IF(Spenden.Organisation > 0, Organisationen.strasse, Personen.Straße) AS Strasse, + IF(Spenden.Organisation > 0, Organisationen.plz, Personen.PLZ) AS PLZ, + IF(Spenden.Organisation > 0, Organisationen.ort, Personen.Ort) AS Ort, + Förderer.zahlungsanmerkung AS Zahlungsanmerkung + +FROM Spenden + +LEFT JOIN Personen ON Spenden.Person = Personen.ID +LEFT JOIN Organisationen ON Spenden.Organisation = Organisationen.ID +LEFT JOIN Förderer ON Förderer.Organisation = Organisationen.ID +LEFT JOIN Spendenarten ON Spenden.Förderart = Spendenarten.ID +LEFT JOIN Personen AS APerson ON Spenden.`Spendenquittung Person` = APerson.ID +LEFT JOIN Personen AS FPerson ON Förderer.zuwendungen = FPerson.ID +"; + $pdf_sql .= " WHERE Spenden.ID IN "; + + $id = "donview"; + include __DIR__ . '/autoemail.php'; + include __DIR__ . '/autopdf.php'; +?> + +

    + +  +SuperID) { ?> +  +  + +  + +prepare($sql); + $stmt->bind_param('i', $orgaID); + $stmt->bind_result($id, $nachname, $vorname); + $stmt->execute(); + $persXorga = []; + while ($stmt->fetch()) { + $persXorga[$id] = $nachname . ", " . $vorname; + } + $stmt->reset(); + + if ($l->SuperID) { + $id = "subdonation"; + $entrytable = "Spenden"; + $def = [ + "Aufteilung der zugrundeliegenden Super-Spende", + [6, 'Betrag', '_VertrID', '=4contract|contractsall~an Vertrag'], + [1, 'Kommentar'], + "Zugrundliegende Super-Spende", + [5, '_SuperID~SuperSpendenID', '_SupBetrag~Betrag', '_SupGeldeingang#isodate~Geldeingang', '_=2SupUnused~nicht aufgeteilter Betrag'], + "Spender", + [10, '_SupPerson~PersID', '_=9SupPersonName~Name'], + [10, '_SupOrga~OrgaID', '_=9SupOrgaName~Organisation'], + ]; + } else { + $id = "donation"; + $entrytable = "Spenden"; + $def = [ + "Allgemein", + [5, 'Betrag', 'Geldeingang#isodate', 'Förderjahr', '_Spendenart'], + [1, 'Kommentar'], + [5, '_VertrID', '=4contract|contractsall~Vertrag'], + "Spender", + [10, '_PersID', '=2Person#search_pers_id~Person setzen', '_=5Name'], + [10, '_OrgaID', '=5Organisation|organisationen', '_=4Zahlungsanmerkung'], + ]; + $links = [ + 'QuittPersID' => '/db/person', + ]; + } + + include __DIR__ . '/autoform.php'; + $stmt->reset(); +})(); + +(function () use ($donationID) { + $globstring = "{*-" . $donationID . "donation*," . $donationID . "donation*}_"; + require_once __DIR__ . "/doc.php"; + })(); + +include_once __DIR__ . '/donationsXdonation.php'; + +include_once __DIR__ . '/persXorga.php'; + +include_once __DIR__ . '/footer.php'; diff --git a/donations.php b/donations.php new file mode 100644 index 0000000..428616c --- /dev/null +++ b/donations.php @@ -0,0 +1,197 @@ + + 2000) { + $_constraint = " WHERE Geldeingang >= '" . intval($_GET['year']) . "-01-01' AND Geldeingang < '" . (intval($_GET['year']) + 1) . "-01-01'"; + $_title .= " (Jahr " . $_GET['year'] . ")"; +} +if (isset($_GET['ay']) && $_GET['ay'] > 2000) { + $_constraint = " WHERE Geldeingang >= '" . intval($_GET['ay']) . "-09-01' AND Geldeingang < '" . (intval($_GET['ay']) + 1) . "-09-01'"; + $_title .= " (Förderjahr/Akad. Jahr an FH " . $_GET['ay'] . "/" . (intval($_GET['ay'])+1) . ")"; +} +if (isset($_GET['call']) && $_GET['call'] != "") { + $_constraint = " WHERE `call` = '" . $_GET['call'] . "'"; + $_title .= " (Call " . $_GET['call'] . ")"; +} +if (isset($_GET['last']) && $_GET['last'] != "") { + $_constraint = " ORDER BY Spenden.ID DESC LIMIT " . intval($_GET['last']); + $_title .= " (Letzte " . $_GET['last'] . " nach ID)"; +} + +include_once __DIR__ . "/header.php"; +require_once __DIR__ . "/../includes/common.php"; +require_once __DIR__ . "/../includes/db2.php"; + +doTitle(); + +(function () use ($_constraint, $mysqli2) { + $id = "donations5"; + $sql = " +SELECT + Spenden.ID AS SpendenID, + Spenden.SuperID AS SuperSpendenID, + Person AS PersID, + CONCAT(Personen.Nachname, ', ', Personen.Vorname) AS Name, + Spenden.Organisation AS OrgaID, + Organisationen.Name AS Organisation, + legal.name AS Rechtsform, + contract AS VertrID, + CONCAT(calls.name, ' [', contracts.call, '] - ', ContractOrga.Name) AS Vertrag, + Kommentar, + Betrag, + DATE(Geldeingang) AS Geldeingang, + Förderjahr, + Spendenarten.Name AS Spendenart, + FPerson.Anrede, FPerson.Ansprache, FPerson.Vorname, FPerson.Nachname, FPerson.Geschlecht, + Organisationen.strasse AS Strasse, + Organisationen.plz AS PLZ, + Organisationen.ort AS Ort, + Förderer.zahlungsanmerkung AS Zahlungsanmerkung + +FROM Spenden + +LEFT JOIN Personen ON Spenden.Person = Personen.ID +LEFT JOIN Organisationen ON Spenden.Organisation = Organisationen.ID +LEFT JOIN Förderer ON Förderer.Organisation = Organisationen.ID +LEFT JOIN Spendenarten ON Spenden.Förderart = Spendenarten.ID +LEFT JOIN Personen AS FPerson ON Förderer.zuwendungen = FPerson.ID +LEFT JOIN contracts ON Spenden.contract = contracts.id +LEFT JOIN Förderer AS ContractPatron ON contracts.patron = ContractPatron.ID +LEFT JOIN Organisationen AS ContractOrga ON ContractPatron.Organisation = ContractOrga.ID +LEFT JOIN calls ON contracts.`call` = calls.shorthand +LEFT JOIN legal ON Förderer.legal = legal.id +"; + $sql .= $_constraint; + $getthdef = true; + $order = '[[1, "desc"]]'; + $entrytable = 'Spenden'; + $idcell = 'SpendenID'; + $idcellreal = 'ID'; + $types = [ + 'Spendenquittung erstellt' => 'checkbox' + ]; + $editable = [ + 'Kommentar', + 'Betrag', + 'Geldeingang', + 'Förderjahr', + 'Spendenquittung erstellt', + 'Spendenquittung verschickt' + ]; + $callback_heading = "Dokumente u. Mails"; + + $globs = glob(__DIR__ . "/../uploads/*donation*_/*.pdf", GLOB_BRACE|GLOB_NOSORT|GLOB_ERR); + $callback = function ($x) use ($globs, $_constraint, $mysqli2) { + + foreach($globs as $g) { + //foreach (glob(__DIR__ . "/../uploads/{" . $x . "donation*,*-" . $x . "donation*" . "}_/*.pdf", GLOB_BRACE|GLOB_NOSORT|GLOB_ERR) as $d) { + if (strstr($g, "/" . $x . "donation") !== false || strstr($g, "-" . $x . "donation") !== false) { + echo '' . basename($g) . ''; + echo ' '; + echo '
    '; + } + } + + $sql2 = "select ID FROM mails WHERE MATCH (attached) AGAINST ('" . $x . "donation*' IN BOOLEAN MODE)"; + $stmt2 = $mysqli2->prepare($sql2); + $stmt2->bind_result($s); + $stmt2->execute(); + while ($stmt2->fetch()) { + echo "[Mail " . $s . "]
    "; + } + $stmt2->reset(); + }; + $checkboxes = true; + + $bottom = << + Ausgewählte + + +EOD; + + include __DIR__ . '/autotable.php'; + + $pdf_sql = " +SELECT + Spenden.ID AS SpendenID, + Person AS PersID, + CONCAT ( + IF(Spenden.Organisation > 0, CONCAT(Organisationen.ID, 'orga', '-'), ''), + IF(Förderer.zuwendungen > 0, CONCAT(FPerson.ID, 'person', '-'), ''), + IF(Spenden.Person > 0, CONCAT(Spenden.Person, 'person', '-'), ''), + Spenden.ID, 'donation' + ) AS uid, + CONCAT(Personen.Nachname, ', ', Personen.Vorname) AS Name, + Spenden.Organisation AS OrgaID, + Organisationen.Name AS Organisation, + Kommentar, + Betrag, + DATE(Geldeingang) AS Geldeingang, + Förderjahr, + Spendenarten.Name AS Spendenart, + `Spendenquittung erstellt`, + DATE(`Spendenquittung verschickt`) AS `Spendenquittung verschickt`, + `Spendenquittung Person` AS QuittPersID, + IF(Förderer.zuwendungen > 0, FPerson.Anrede, Personen.Anrede) AS Anrede, + IF(Förderer.zuwendungen > 0, FPerson.Ansprache, Personen.Ansprache) AS Ansprache, + IF(Förderer.zuwendungen > 0, FPerson.Vorname, Personen.Vorname) AS Vorname, + IF(Förderer.zuwendungen > 0, FPerson.Nachname, Personen.Nachname) AS Nachname, + IF(Förderer.zuwendungen > 0, FPerson.Geschlecht, Personen.Geschlecht) AS Geschlecht, + IF(Förderer.zuwendungen > 0, FPerson.Email, Personen.Email) AS Email, + IF(Spenden.Organisation > 0, Organisationen.strasse, Personen.Straße) AS Strasse, + IF(Spenden.Organisation > 0, Organisationen.plz, Personen.PLZ) AS PLZ, + IF(Spenden.Organisation > 0, Organisationen.ort, Personen.Ort) AS Ort + +FROM Spenden + +LEFT JOIN Personen ON Spenden.Person = Personen.ID +LEFT JOIN Organisationen ON Spenden.Organisation = Organisationen.ID +LEFT JOIN Förderer ON Förderer.Organisation = Organisationen.ID +LEFT JOIN Spendenarten ON Spenden.Förderart = Spendenarten.ID +LEFT JOIN Personen AS FPerson ON Förderer.zuwendungen = FPerson.ID +"; + $pdf_sql .= " WHERE Spenden.ID IN "; + $email_sql = $pdf_sql; + $pdf_file = "spendenbescheinigung"; + include __DIR__ . '/autoactions.php'; + include __DIR__ . '/autoemail.php'; + include __DIR__ . '/autopdf.php'; +?> + + + + + 0, CONCAT(contracts.`call`, '-', Organisationen.Name), '') AS Vertrag, + Spenden.Person AS PersID, + Personen.Nachname, + Personen.Vorname +FROM Spenden +LEFT JOIN Personen ON Spenden.Person = Personen.ID +LEFT JOIN contracts ON Spenden.contract = contracts.ID +LEFT JOIN Förderer ON contracts.patron = Förderer.ID +LEFT JOIN Organisationen ON Förderer.Organisation = Organisationen.ID +WHERE Spenden.Organisation=" . $orgaID; + + $order = '[[1, "desc"]]'; + $thdef = ['SpendenID', 'Betrag', 'Geldeingang', 'VertrID', 'Vertrag', 'PersID', 'Nachname', 'Vorname']; + $ajax = $nospinner = true; + include __DIR__ . '/autotable.php'; +})(); diff --git a/donationsXpers.php b/donationsXpers.php new file mode 100644 index 0000000..102028c --- /dev/null +++ b/donationsXpers.php @@ -0,0 +1,39 @@ + + 0, CONCAT(contracts.`call`, '-', Organisationen.Name), '') AS Vertrag, + Organisationen.Name AS Organisation +FROM Spenden +LEFT JOIN contracts ON Spenden.contract = contracts.ID +LEFT JOIN Förderer ON contracts.patron = Förderer.ID +LEFT JOIN Organisationen ON Förderer.Organisation = Organisationen.ID +WHERE Spenden.Person=" . $persID . " +"; + $ajax = $nospinner = true; + include __DIR__ . '/autotable.php'; +})(); diff --git a/dopdf.php b/dopdf.php new file mode 100644 index 0000000..ca254dc --- /dev/null +++ b/dopdf.php @@ -0,0 +1,202 @@ + +close(); + exit(0); +} + +$data = [ + 'organisation' => $_POST['organisation'], + 'anrede' => $_POST['anrede'], + 'anrede_briefkopf' => $_POST['anrede_briefkopf'], + 'titel' => $_POST['titel'], + 'vorname' => $_POST['vorname'], + 'nachname' => $_POST['nachname'], + 'strasse' => $_POST['strasse'], + 'adresszusatz' => $_POST['adresszusatz'], + 'plz' => $_POST['plz'], + 'ort' => $_POST['ort'], + 'betreff' => $_POST['subject'], + 'text' => $_POST['html'], + 'datum' => $_POST['date'], + 'data' => $_POST['data'], + 'fn' => $_POST['fn'] +]; + +define('NUMERAL_SIGN', 'minus'); +define('NUMERAL_HUNDREDS_SUFFIX', 'hundert'); +define('NUMERAL_INFIX', 'und'); + +$lNumeral = array('null', 'ein', 'zwei', 'drei', 'vier', + 'fünf', 'sechs', 'sieben', 'acht', 'neun', + 'zehn', 'elf', 'zwölf', 'dreizehn', 'vierzehn', + 'fünfzehn', 'sechzehn', 'siebzehn', 'achtzehn', 'neunzehn'); + +$lTenner = array('', '', 'zwanzig', 'dreißig', 'vierzig', + 'fünfzig', 'sechzig', 'siebzig', 'achtzig', 'neunzig'); + +$lGroupSuffix = array(array('s', ''), + array('tausend ', 'tausend '), + array('e Million ', ' Millionen '), + array('e Milliarde ', ' Milliarden '), + array('e Billion ', ' Billionen '), + array('e Billiarde ', ' Billiarden '), + array('e Trillion ', ' Trillionen ')); + +function num2text($pNumber) +{ + global $lNumeral; + if ($pNumber == 0) { + return $lNumeral[0]; + } elseif ($pNumber < 0) { + return NUMERAL_SIGN . ' ' . num2text_group(abs($pNumber)); + } else { + return num2text_group($pNumber); + } +} + +function num2text_group($pNumber, $pGroupLevel = 0) +{ + global $lNumeral, $lTenner, $lGroupSuffix; + if ($pNumber == 0) { + return ''; + } + $lGroupNumber = $pNumber % 1000; + if ($lGroupNumber == 1) { + $lResult = $lNumeral[1] . $lGroupSuffix[$pGroupLevel][0]; + } elseif ($lGroupNumber > 1) { + $lResult = ''; + $lFirstDigit = floor($lGroupNumber / 100); + if ($lFirstDigit > 0) { + $lResult .= $lNumeral[$lFirstDigit] . NUMERAL_HUNDREDS_SUFFIX; + } + $lLastDigits = $lGroupNumber % 100; + $lSecondDigit = floor($lLastDigits / 10); + $lThirdDigit = $lLastDigits % 10; + if ($lLastDigits == 1) { + $lResult .= $lNumeral[1] . 's'; + } elseif ($lLastDigits > 1 && $lLastDigits < 20) { + $lResult .= $lNumeral[$lLastDigits]; + } elseif ($lLastDigits >= 20) { + if ($lThirdDigit > 0) { + $lResult .= $lNumeral[$lThirdDigit] . NUMERAL_INFIX; + } + $lResult .= $lTenner[$lSecondDigit]; + } + $lResult .= $lGroupSuffix[$pGroupLevel][1]; + } + return num2text_group(floor($pNumber / 1000), $pGroupLevel + 1) . $lResult; +} + +try { + $ddata = json_decode($_POST['data']); + $data['data'] = $ddata; + $data['betrag'] = number_format($ddata->Betrag, 2, ",", "."); + $data['betrag_bs'] = num2text($ddata->Betrag) . ' Euro'; + $data['betrag_datum'] = explode('-', $ddata->Geldeingang)[2] . '.' . explode('-', $ddata->Geldeingang)[1] . '.' . explode('-', $ddata->Geldeingang)[0]; +} catch (Exception $e) { + ; +} + +$template = $_POST['template']; +$template = 'hsrw.twig'; + +$html = $twig->render($template, $data); + +$defaultConfig = (new Mpdf\Config\ConfigVariables())->getDefaults(); +$fontDirs = $defaultConfig['fontDir']; + +$defaultFontConfig = (new Mpdf\Config\FontVariables())->getDefaults(); +$fontData = $defaultFontConfig['fontdata']; + +$mpdf = new Mpdf\Mpdf([ + 'tempDir' => __DIR__ . '/tmp', + 'format' => 'A4', +]); + +//$mpdf->SetImportUse(); +$mpdf->useActiveForms = true; +if (!strstr($template, 'zahlung')) { + $mpdf->SetProtection(array('print')); +} +$mpdf->WriteHTML($html); +$mysqli->close(); + +if (isset($_GET['test'])) { + header("Content-Type: application/pdf"); + $mpdf->Output(); + exit(0); +} + +$fn = $_POST['fn']; + +mkdir("/var/www/uploads/" . $puid . "_"); +$file = "/var/www/uploads/" . $puid . "_/" . $fn . ".pdf"; +$mpdf->Output($file, \Mpdf\Output\Destination::FILE); + +if (isset($_GET['save'])) { + echo "1"; + exit(0); +} + +header("Content-Type: application/pdf"); +readfile($file); +exit(0); + +?> diff --git a/email.php b/email.php new file mode 100644 index 0000000..5505957 --- /dev/null +++ b/email.php @@ -0,0 +1,708 @@ + + + + + +

    E-Mail senden + + 1) { ?> +für Datensätze + +für Datensatz + + + +

    + + +
    + Demo-Flag ist an — es werden keine E-Mails gesendet, der Versand wird simuliert mit 3% Fehlerwahrscheinlichkeit. +
    + +
    + + + +
    + Demo-Self-Flag ist an — Der E-Mail-Empfänger (To) wird beim Versand durch den E-Mail-Absender (From) ersetzt. Cc und Bcc werden ignoriert. +
    + +
    + + +prepare($sql); + $stmt->bind_param('s', $_SESSION['auth_user']); + $stmt->bind_result($semail); + $stmt->execute(); + $stmt->fetch(); + $stmt->reset(); +?> + + + + + + + + + + + + + + + + + + + + + + + +
    Absender
    ID/ToPreview + + +
    Cc + + + +
    Bcc
    Betreff + + + +
    + +
    +
    + + + + +
    + +
    + + +
    + + +
    + + + Letzter Status: +

    +
    +
    + +
    + Gespeicherte Templates + + +
    +prepare($sql); + $stmt->bind_param('i', $_SESSION['auth_userid']); + $stmt->bind_result($id, $name, $text, $subject, $from, $cc, $bcc, $ts, $tags, $fav); + $stmt->execute(); +?> + +
    + + + + + +
    +  
    +  
    +  
    + Templates verwalten +
    +
    +
    +reset(); +?> + +
    +
    + +
    + Anhänge + (PDF, Word*, Excel*, PowerPoint*, JPG, PNG, ZIP) + + + + +
    +prepare($sql); + $stmt->bind_result($fid, $fname, $fmime); + $stmt->execute(); +?> + + +
    + + + + + + + + diff --git a/emailhtml.php b/emailhtml.php new file mode 100644 index 0000000..0b44ec3 --- /dev/null +++ b/emailhtml.php @@ -0,0 +1,454 @@ + + + + + +

    E-Mail senden

    + + +

    Demo-Flag ist an

    + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
    + an Empfänger +
    Absender
    To (Preview)
    ID (Preview)
    Cc
    Bcc
    Betreff
    + +
    +
    + + +
    + Letzter Status: +

    +
    +
    + +
    + Gespeicherte Templates +
    +prepare($sql); + $stmt->bind_result($id, $name, $text, $subject, $from, $cc, $bcc, $ts); + $stmt->execute(); +?> + +
    +
    + + + + + +reset(); +?> + +
    +
    + +
    + Anhang +prepare($sql); + $stmt->bind_result($fid, $fname); + $stmt->execute(); +?> + +
    + + + + + + + + diff --git a/form.scm b/form.scm new file mode 100644 index 0000000..628c6cf --- /dev/null +++ b/form.scm @@ -0,0 +1,989 @@ +; vim: set et : +(use-modules (ice-9 regex)) +(use-modules (srfi srfi-27)) +(load "lang-tags.scm") + +(define haddelitembtnjs #f) +(define haddelcmitembtnjs #f) + +(define (current-year) + (string->number (strftime "%Y" (localtime (current-time))))) + +(define (random-string len) + (define (iter n s) + (if (< n 1) + s + (iter (- n 1) (string-append s (string (integer->char (+ (random-integer 24) 65))))))) + (iter len "")) + +(define (decide-by-lang de en) + `((!? "if (strstr($_SERVER['REQUEST_URI'], '/application/') || strstr($_SERVER['REQUEST_URI'], '/review/') || basename(dirname($_SERVER['REQUEST_URI'])) === 'en') {") + ,en + (!? "} else {") + ,de + (!? "}"))) + +(define* (stepform num h1 h2 lis #:key (last #f) (elements-before-overlay `'()) (php-args "") (appname "ds") ) + `((!span "") ; dummy for tidy + (!php ,(string-append"/* vim: set ts=4 sw=4 et : */ if (!isset($doPDF)) {" php-args)) + (@p (define stepform_lis ',lis)) + (@p (main ,h1 ,h2 + (append + ,elements-before-overlay + `((!? "}") + (@p (overlay + `(@p (fieldset + (string-append ,,,appname (number->string ,,,num) "App") + (string-append "app" (number->string ,,,num) "Ctrl") + stepform_lis)))) + (!? "if (!isset($doPDF)) {") + (!p "* " (@p (parse-mess "?mandatory"))) + (!div #((id uibottom)(style "display: none;")) + ,(if (= ,num 2) + `((!div #((style "display: inline-block; width: 49%;")) + (!button #((style "background: darkgrey; margin-right: .5em;") + (onclick "return saves2();")) + ,(parse-mess "?save"))) + (!div #((style "display: inline-block; width: 49%; text-align: right;")) + (!button #(onclick "return continue_step3();") + ,(parse-mess "?next"))))) + ,(if (and (> ,num 2) (not ,last)) + `((!div #((style "display: inline-block; width: 49%;")) + (!button #((style "background: darkgrey;") + (onclick "return save();")) + ,(parse-mess "?save"))) + (!div #((style "display: inline-block; width: 49%; text-align: right;")) + (!button #((onclick ,(string-append "return return_step" (convert (- ,num 1)) "();")) + (style "margin-right: .5em;")) + ,(parse-mess "?back")) + (!button #(onclick ,(string-append "return continue_step" (convert (+ ,num 1)) "();")) + ,(parse-mess "?next"))))) + ,(if ,last + `((!div #((style "display: inline-block; width: 49%;")) + (!button #((style "background: darkgrey;") + (onclick "return save();")) + ,(parse-mess "?save"))) + (!div #((style "display: inline-block; width: 49%; text-align: right;")) + (!button #((onclick ,(string-append "return return_step" (convert (- ,num 1)) "();")) + (style "margin-right: .5em;")) + ,(parse-mess "?back")) + (!button #(onclick "return check_and_send();") + ,(parse-mess "?send")))))) + (!? "}"))))) + (@p (minjs + `((@p (stepdatajs ,,num ,,appname)) + (@p (stepsubmitjs ,,num ,,appname)) + (@p (stepbootstrapjs ,,num ,,appname)) + (@dump ,(string-append "s" (number->string ,num) ".js")) + (@p (stepjs ,,num ,,appname)) + ))))) + +(define (stepdatajs num appname) + (let ((n (number->string num))) + `(,(string-append + "window.s" n " = {};\n" + "try { window.s" n " = JSON.parse('');\n" + "} catch (e) { ; };\n")))) + +(define (stepsubmitjs num appname) + (let ((n (number->string num))) + `(,(string-append + "function submitValues() {\n" + "return safeSubmit({\n" + "'s': " n ",\n" + "'uid': '',\n" + "'value': acc()\n" + "});\n" + "}\n")))) + +(define (stepbootstrapjs num appname) + (let ((n (number->string num))) + `(,(string-append + "function stepBootstrap" (convert num) "() {\n" + "window.app" n " = angular.module('" appname n "App', []);\n" + "window.app" n ".controller('app" n "Ctrl', ['$scope', function ($scope) {\n" + "$scope.d = window.s" n ";\n" + "}]);\n" + "angular.bootstrap($('#" appname n "App'), ['" appname n "App']);\n" + "}\n" + "function stepActivate" (convert num) "() {\n" + "$('#" appname n "App').show();\n" + "$('#" appname n "App input').trigger('change');\n" + "$('#" appname n "App select').trigger('change');\n" + "$('#uibottom').show();\n" + "}\n" + )))) + +(define (stepjs num appname) + (define func (random-string 10)) + `((!? "if (isset($doPDF)) {") + ,(string-append "function " func "() { $('#" appname (convert num) "App input, #" appname (convert num) "App textarea, #" appname (convert num) "App select').prop('disabled', true); $('" appname (convert num) "App button').prop('disabled', true).css('display', 'none'); }") + ,(string-append "$(document).ready(function () {" func "(); let observer = new MutationObserver(function () { " func "(); }); observer.observe(document.querySelector('#" appname (convert num) "App'), {subtree: true, childList: true,}); });") + (!? "}"))) + +(define* (main h1 h2 lis #:key (style "padding: 0;")) + `(!div #((class main)(style ,style)) + (!h1 (@p (parse-mess ,h1))) + (!h2 (@p (parse-mess ,h2))) + ,lis)) + +(define (minjs lis) + `(!script (!? "minStart();" ) "\n" ,lis "\n" (!? "minEnd();"))) + +(define (newbuttonjs func variable) + `(,(minjs + (string-append + "function " func "(me) {\n" + " var v = JSON.parse(acc());\n" + " if (typeof v." variable " === 'object') v." variable ".push([]);\n" + " else v." variable " = [];\n" + " if (safeSubmit({\n" + " 's': $(me).closest('fieldset').attr('id').substr(2, 1),\n" + " 'uid': '',\n" + " 'value': JSON.stringify(v)\n" + " })) location.reload();\n" + " return false;\n" + "}")))) + +(define (delitembuttonjs) + (unless #f + (minjs + `(,(set! haddelitembtnjs #t) + ,(string-append + "function delItem(o) {\n" + " o.parent().parent().remove();\n" + " if (submitValues()) location.reload();\n" + " return false;\n" + "}"))))) + +(define (delcmitembuttonjs) + (unless haddelcmitembtnjs + (minjs + `(,(set! haddelitembtnjs #t) + ,(string-append + "function delCMItem(o) {\n" + " o.parent().remove();\n" + " if (submitValues()) location.reload();\n" + " return false;\n" + "}"))))) + +;(define (textarea ...) +(define (textarea text) + `(!textarea #((readonly "readonly")) ,text)) + +;(define (textareaonside ...) +(define (textareaonside width label text) + `((!div #(data-field-span ,width) + (!span ,label) + (!textarea #((readonly "readonly")) + ,text)))) + +;(define (checkboxsimple ...) +(define (checkboxsimple width name label) + `((!div #(data-field-span ,width) + (!input #((type "checkbox") + (name ,name) + (ng-checked ,(string-append "d." name))) + " " ,(parse-mess label))))) + +;(define (checkboxconglomerate (mehrere mit
    getrennt, siehe ds/s5.php ...) +(define (checkboxconglomerate width checkboxes textarea-label textarea-name) + (define (iter items) + (if (null? items) + '() + (let ((e (car items))) + (append + `((!input #((type "checkbox") (name ,(car e)) (ng-checked ,(string-append "d." (car e)))) ,(cadr e)) + (!br)) + (iter (cdr items)))))) + + `((!div #(data-row-span ,width) + (!div #(data-field-span ,width) + ,@(iter checkboxes) + (!br) + ,textarea-label ":" + (!textarea #((name ,textarea-name) (rows "2")) ,(string-append "{{ d." textarea-name " }}")) + (!br))))) + + +;(define (multicontainar form classes variable labeldelbtn labelnewbtn btnprefix lis) +;(@p (multicontainer "3" "study" "studies" "Studium loeschen" "Weiteres Studium anlegen" "study" `((@p (legend "...") ....)))) +;(define (multicontainer width form classes variable labeldelbtn labelnewbtn btnprefix lis) +; (if (null? lis) '() (let ((e (car items))) +; (append (list (( + +; #:funcnewbtn - explicitly name btn +; #:divargs - arguments for the outer div +; #:labelduration - label text for the duration +(define* (multicontainer width class name variable labeldelbtn labelnewbtn #:key (funcnewbtn (random-string 16)) (divargs #()) (labelduration "") (selectname "") (options '()) .opt) + `((!div ,(merge-html-args `#((class ,class) (ng-repeat ,(string-append "b in d." variable))) divargs) + ,(row width `( + (!div #((data-field-span 1) (class "mand")) ,(parse-mess name) "*") + ,(textareasimple 2 "" class #t #:prefix "b") + ,(if (string=? labelduration "") '() `("\n
    " + (!div #((style "clear: both;")) " ") + ;(!div #((data-field-span 1) (class "mand")) ,(parse-mess labelduration)) + ;(!div #((data-field-span 2)) + ,(selectonside 1 2 "" labelduration selectname options #t #:prefix "b" #:noempty #t) + (!div #((style "clear: both;")) " ") + + )) + (!button #((onclick "return delItem($(this));")) ,(parse-mess labeldelbtn)) + + ))) + (!button #((id ,(string-append "btn_" class)) (onclick ,(string-append "return " funcnewbtn "(this);"))) ,(parse-mess labelnewbtn)) + (@p (newbuttonjs ,funcnewbtn ,variable)) + (@p (delitembuttonjs)) + )) + +; #:funcnewbtn - explicitly name btn +(define* (custommulticontainer class legend variable labeldelbtn labelnewbtn options #:key (funcnewbtn (random-string 16))) + `((!legend ,(parse-mess legend)) + (!div ,(merge-html-args `#((style "margin-bottom: 1.5em;")(class ,class) (ng-repeat ,(string-append "b in d." variable)))) + (,options ((!button #((onclick "return delCMItem($(this));")) ,(parse-mess labeldelbtn))))) + (!button #((id ,(string-append "btn_" class)) (onclick ,(string-append "return " funcnewbtn "(this);"))) ,(parse-mess labelnewbtn)) + (@p (newbuttonjs ,funcnewbtn ,variable)) + (@p (delcmitembuttonjs)) + ) +) + +(define* (s5spacer #:key (includenbsp #f) . opt) + `(("
    ") (,(if includenbsp `(!div #((style "clear: both;")) " ") '())))) + + +(define* (social width1 width2 label inputs #:key (prefix "d") (message "") (textprefix "") (textarea #f) (comment_on '()) (aftermessage '()) (afterspan '())) + `((!legend ,(parse-mess label)) + ,(row width1 + `((!div + #((data-field-span ,width2)) + ,@(let iter ((i inputs)) + (if (null? i) + '() + (append + `((!input ,`#((type "checkbox") (name ,(car i)) (ng-checked ,(string-append prefix "." (car i))))) + " " ,(parse-mess (string-append textprefix (car i))) + (!div #((style "clear:both; margin-bottom: .4em;"))) + ,(if (member (car i) comment_on) + `((!div #((style "display: none; margin-top: .3em; margin-left: 2em; padding-bottom: .8em;") + (id ,(string-append (car i) "_comment"))) + ,(parse-mess "?social_comment") "*" + (!textarea #((class "mand") + (ng-value ,(string-append prefix "." (car i) "_comment")) + (name ,(string-append (car i) "_comment"))))) + (!script + ,(string-append + "$('input[name=" (car i) "]').on('change', function () {" + " if ($(this).is(':checked')) {" + " $('#" (car i) "_comment').show();" + " } else {" + " $('#" (car i) "_comment').hide();" + " }" + "});"))))) + (iter (cdr i))))) + ,(if textarea + `((!br) + ,(parse-mess-as-text (string-append textprefix "sonstiges")) + (!textarea #((name "sonstiges") (rows "2")) ,(string-append "{{ " prefix ".sonstiges }}"))) + `((!br) + ,(string-append (parse-mess-as-text (string-append textprefix "sonstiges")) " ") + (!textarea #((name "sonstiges")) ,(string-append "{{ " prefix ".sonstiges }}")))) + (!br) + ,(if (string=? message "") '() `(!p (!i #((class "fas fa-exclamation-triangle"))) " " ,(parse-mess message))) + ,aftermessage) + ,afterspan)))) + +; fieldsetargs - arguments for the fieldset element +(define* (fieldset app cntrl lis #:key (fieldsetargs #()) .opt) + `(!fieldset ,(merge-html-args `#((id ,app) (ng-controller ,cntrl) (style "display: none;")) fieldsetargs) + ,@lis)) + +; #:formargs - arguments for the grid-form +(define* (grid-form lis #:key (formargs #())) + `(!form ,(merge-html-args `#((class "grid-form")) formargs) ,@lis) +) + +; #:divargs - arguments for the overlay div +; #:formargs - arguments for the grid-form +(define* (overlay lis #:key (divargs #()) (formargs #()) . opt) + `(!div ,(merge-html-args `#((id "overlay")) divargs) (!form ,(merge-html-args `#((class "grid-form")) formargs) ,@lis))) + +;textsimple pruefen, sollte ds/s3.php "Geben Sie Ihre aktuelle ...." + +;(define (row width lis . style) +; `(!div #(data-row-span ,width) +; ,lis)) + +; #:divargs - arguments of the div +(define* (row width lis #:key (divargs #()) . opt) + `(!div ,(merge-html-args `#((data-row-span ,width)) divargs) ,lis)) + + +; (if (= (length class) 2) + +;(define (row width lis . style) +; `(!div #(data-row-span ,width) +; ,lis)) + +(define (tabular width1 width2 col1name col2name sumfield1 sumfield2 l) + (define (tabular-inner width1 width2 l) + (let iter ((lis l)) + (if (null? lis) + '() + (cons `(@p (row ,(+ width1 width2) + `((!div #(data-field-span ,,width1) ,,(caar lis)) + (!div #(data-field-span ,,width2) ,,(cadar lis))))) + (iter (cdr lis)))))) + + (append `((@p (row ,(+ width1 width2) + `((!div #(data-field-span ,,width1) ,,col1name) + (!div #(data-field-span ,,width2) ,,col2name))))) + + (tabular-inner width1 width2 l) + + `((@p (row ,(+ width1 width2) + `((!div #(data-field-span ,,width1) ,,sumfield1) + (!div #(data-field-span ,,width2) ,,sumfield2))))) + + )) + +;(define (tabular2 width1 width2 col1name col2name sumfield1 sumfield2 lis1 lis2) +; (let iter ((lis1 lis1) (lis2 lis2)) +; (if (or (null? lis1) (null? lis2)) +; '() +; (let ((e1 (car lis1)) +; (e2 (car lis2))) +; (cons `(@p (row ,(+ width1 width2) +; `((!div #(data-field-span ,width1) ,e1) +; (!div #(data-field-span ,width2) ,e2)))) +; (iter (cdr lis1) (cdr lis2)))))) +; +; (append `((@p (row ,(+ width1 width2)) +; `((!div #(data-field-span ,width1) ,col1name) +; (!div #(data-field-span ,width2) ,col2name)))) +; (iter lis1 lis2) +; `((@p (row ,(+ width1 width2)) +; `((!div #(data-field-span ,width1) ,sumfield1) +; (!div #(data-field-span ,width2) ,sumfield2)))))) + + +(define (tip1 name . tip) + (if (null? tip) '() `(!span #(("style" "font-size: 1.2rem; text-transform: none; cursor: pointer;") + ("onmouseover" ,(string-append "$('#" name "_tip').show();")) + ("onmouseleave" ,(string-append "$('#" name "_tip').hide();")) + ("onclick" ,(string-append "$('#" name "_tip').toggle();"))) + "ⓘ"))) + +(define (tip2 name . tip) + (if (null? tip) '() `(!p #(("id" ,(string-append name "_tip")) + ("style" "display: none; font-size: 1rem; text-align: justify; hyphens: auto;")) + ,tip))) + +;upload form used in s6 +; #:dataformat - a list of 2 elements specifying the type and accepted documents in input +; #:info - optional list of elements which will be placed below the upload fields +; #:inputclasses - html classes to be added to the file input field +; #:titlemandargs - a string, mainly used to add a " * " in the title infront of the " : ". The mand class would put it after the " : " +(define* (s6-upload-form doctype #:key (dataformat '("pdf" "application/pdf, .pdf")) (info `()) (inputclasses "mand") (titlemandargs "*") .options) + `(!div #(class "upload") + (!b #() ,(string-append (parse-mess-as-text (string-append "?s6_" doctype)) titlemandargs)) + (!php ,(string-append "$e = false; if (file_exists(\"../../uploads/\" . $puid . \"_\" . \"" doctype "." (car dataformat) "\")) { $e = true;")) + (!span " " (!a #((class "uploaded") (href ,(string-append "download.php?" doctype)) (target "_blank")) ,(parse-mess "?hochgeladen"))) + (!form #((style "display: inline-block;") (action "delupload.php") (method "post") (enctype "multipart/form-data")) + (!input #((type "hidden") (name ,doctype) (value "1"))) + (!input #((type "submit") (class "delete") (onclick "return submitValues();") (value ,(parse-mess-as-text "?del")))) + ) + (!input #((type "hidden") (name ,(string-append doctype "_done")) (class "done") (value "1"))) + (!input #((type "hidden") (name "done") (class "done") (value "1"))) + (!php "}") + (!form #((action "upload.php") (method "post") (enctype "multipart/form-data")) + (!input #((type "hidden") (name ,doctype) (value "1"))) + (!input #((type "file") (class ,inputclasses) (name "file") (accept ,(cadr dataformat)))) + (!input #((type "submit") (style "display: none;") (onclick "return submitValues();") (value "") (name "upload"))) + ) + ,info + ) +) + +; a small infoparagraph +; #:class - class to be added to the +; #:style - style of the

    +(define* (info-paragraph text #:key (class "fa fa-info") (style "padding-left: 2em; margin-top: .5em; margin-bottom: .5em; font-size: 80%;") (want-space #t) . opt) + `(!p ,(if (string=? style "") `#() `#((style ,style))) + ,(if want-space `((!i #((class ,class))) " ") `()) + ,(parse-mess text) + )) + +; a list containing an enumumeration of texts +; #:etradivstyleargs - name says it all (extra style arguments for the outer div) +; #:extralistitemargs - a list of extra style arguments for the enumerated texts +; -> if there are 7 items and only item 5 should have an extra style argument the list needs to be '("" "" "" "" "style") +; #:optionalelements - a list of optional elements to be added after the enumeration +; #:upperulmargintop - the margin for the

      +(define* (info-list title list-items #:key (extradivstyleargs "") (extralistitemargs '()) (optionalelements '()) (upperulmargintop ".2em"). opt) + `(!div #((style ,(string-append extradivstyleargs " padding-left: 2em; padding-top: .7em; font-size: 80%;"))) + (!i #((class "fa fa-info"))) " " + ,(parse-mess title) + (!ul #((style ,(string-append "margin-top: " upperulmargintop "; padding-left: 1.8em;"))) + ,@(let iter ((item list-items) (arg extralistitemargs)) + (if (null? item) + '() + (append `( + (!li ,(if (string-null? (getstringhelper item arg)) `#() `#((style ,(getstringhelper item arg)))) ,(parse-mess (car item))) + ,(iter (cdr item) (if (null? arg) '() (cdr arg)))))) + )) + ,@(let iter2 ((option optionalelements)) + (if (null? option) + '() + (append + `(,(car option)) + (iter2 (cdr option)))) + ) + )) + +; a helper method for info-list (see above) +(define (getstringhelper item arg) + (string-append (if (or (null? arg) (string-null? (car arg))) "" (car arg)) (if (null? (cdr item)) "" "padding-bottom: .3em;"))) + +; #:tip - the tooltip text +; #:tipname - name for the tooltip +; #:divargs - arguments for the outer div +; #:labelargs - arguments for the label +; #:selectargs - arguments for the select element +; #:optionargs - arguments for each option in the select (only if not php) +; #:noempty - removes the empty option from the select +(define* (selectsimple width classes label name options mand #:key (usesprefix #f) (tip "") (tipname "") (divargs #()) (labelargs #()) (selectargs #()) (optionargs #()) (noempty #f) (prefix "d") (afterselect `()) . opt) + `(!div ,(merge-vectors + `#((data-field-span ,width)) + (if (string=? classes "") + `#() + `#((class ,classes)) + ) + divargs) + (!label ,(merge-html-args (if (not mand) #() `#((class "mand"))) labelargs) + (,(parse-mess label) ,(if (string=? tip "") '() (tip1 (if (string=? tipname "") name tipname) (parse-mess tip))))) + (!select ,(merge-vectors + `#((name ,name)) + (if (string=? (string-append (if (not mand) "" "mand ") classes) "") + `#() + `#((class ,(string-append (if (not mand) "" "mand ") classes))) + ) + selectargs) + ,(if noempty '() `(!option ,(merge-html-args `#((value "")) optionargs) "")) + ,@(let iter ((opts options)) + (if (null? opts) + '() + (if (string=? (caar opts) "!php") + (append `(!php ,(cadar opts)) + (iter (cdr opts))) + (append `((!option ,(merge-html-args `#((value ,(caar opts)) + (ng-selected ,(string-append (if usesprefix "s." (string-append prefix ".")) name " == '" (caar opts) "'"))) optionargs) + ,(parse-mess (cadar opts)))) + (iter (cdr opts))))))) + ,(if (string=? tip "") '() (tip2 (if (string=? tipname "") name tipname) (parse-mess tip))) + ,afterselect)) + +; #:tip - the tooltip message +; #:div1args - arguments for the first div +; #:labelargs - arguments for the label in the first div +; #:div2args - arguments for the second div +; #:inputargs - arguments for the input in the second div +(define* (inputonside width1 width2 classes label name mand #:key (tip "") (div1args #()) (labelargs #()) (div2args #()) (inputargs #()) (prefix "d") . opt) + `((!div ,(merge-html-args `#((data-field-span ,width1)) div1args) + (!span ,(merge-html-args (if (not mand) #() `#((class "mand"))) labelargs) + (,(parse-mess label) ,(if (string=? tip "") '() (tip1 name (parse-mess tip)))))) + (!div ,(merge-html-args `#((data-field-span ,width2)) div2args) + (!input ,(merge-vectors + (if (string=? (string-append (if (not mand) "" "mand ") classes) "") + `#() + `#((class ,(string-append (if (not mand) "" "mand ") classes)))) + `#((name ,name)) + `#((type "text")) + `#((value ,(string-append "{{ " prefix "." name " }}"))) + inputargs)) + ,(if (string=? tip "") '() (tip2 name (parse-mess tip)))))) + +; #:tip - the tooltip message +; #:div1args - arguments for the first div +; #:labelargs - arguments for the label in the first div +; #:div2args - arguments for the second div +; #:textareaargs - arguments for the textarea in the second div +(define* (textareaonside width1 width2 classes label name mand #:key (tip "") (div1args #()) (labelargs #()) (div2args #()) (textareaargs #()) (prefix "d") . opt) + `((!div ,(merge-html-args `#((data-field-span ,width1)) div1args) + (!span ,(merge-html-args (if (not mand) #() `#((class "mand"))) labelargs) + (,(parse-mess label) ,(if (string=? tip "") '() (tip1 name (parse-mess tip)))))) + (!div ,(merge-html-args `#((data-field-span ,width2)) div2args) + (!textarea ,(merge-vectors + (if (string=? (string-append (if (not mand) "" "mand ") classes) "") + `#() + `#((class ,(string-append (if (not mand) "" "mand ") classes)))) + `#((name ,name)) + textareaargs) ,(string-append "{{ " prefix "." name " }}")) + ,(if (string=? tip "") '() (tip2 name (parse-mess tip)))))) + + + +(define (parse-mess text) + (if (and (> (string-length text) 0) (string=? (substring text 0 1) "?")) + `(!= ,(string-append "$mess['" (substring text 1 (string-length text)) "']")) + `(,text))) + +(define (parse-mess-as-text text) + (if (and (> (string-length text) 0) (string=? (substring text 0 1) "?")) + (string-append "") + text)) + +; #:legendargs - arguments for the legend +(define* (legend text #:key (legendargs #()) . opt) + `(!legend ,legendargs ,(parse-mess text))) + +; #:tip - the tooltip message +; #:div1args - arguments for the outer div +; #:labelargs - arguments for the label +; #:div2args - arguments for the inner div +; #:selectargs - arguments for the select element +; #:optionargs - arguments for each option of the select +; #:prefix - +; #:noempty - remove the empty option from the select +(define* (selectonside width1 width2 classes label name options mand #:key (tip "") (div1args #()) (labelargs #()) (div2args #()) (selectargs #()) (optionargs #()) (prefix "d") (noempty #f) . opt) + `((!div ,(merge-html-args `#((data-field-span ,width1)) div1args) + (!span ,(merge-html-args (if (not mand) '() #((class "mand"))) labelargs) + (,(parse-mess label) + ,(if (string=? tip "") '() `(!span #(("style" "font-size: 1.2rem; text-transform: none; cursor: pointer;") + ("onmouseover" ,(string-append "$('#" name "_tip').show();")) + ("onmouseleave" ,(string-append "$('#" name "_tip').hide();")) + ("onclick" ,(string-append "$('#" name "_tip').toggle();"))) + "ⓘ"))))) + (!div ,(merge-html-args `#((data-field-span ,width2)) div2args) + (!select ,(merge-html-args `#((class ,(string-append (if (not mand) "" "mand ") classes)) + (name ,name)) selectargs) + ,(if noempty '() `(!option ,(merge-html-args `#((value "")) optionargs) "")) + ,@(let iter ((opts options)) + (if (null? opts) + '() + (if (string=? (caar opts) "!php") + (append `((!php ,(cadar opts))) + (iter (cdr opts))) + (append `((!option ,(merge-html-args `#((value ,(caar opts)) + (ng-selected ,(string-append prefix "." name " == '" (caar opts) "'"))) optionargs) + ,(parse-mess (cadar opts)))) + (iter (cdr opts))))))) + ,(if (string=? tip "") '() `(!p #(("id" ,(string-append name "_tip")) + ("style" "display: none; font-size: 1rem; text-align: justify; hyphens: auto;")) + ,(parse-mess tip)))))) + +; #:div1args - arguments for the first div +; #:labelargs - arguments for the label in the first div +; #:div2args - arguments for the second div +; #:spanargs - arguments for the span in the second div +; #:tip - the tex of the tooltip +(define* (textsimple width classes label text mand #:key (div1args #()) (labelargs #()) (div2args #()) (spanargs #()) (tip "") . opt) + `((!div ,(merge-html-args `#((data-field-span ,width)) div1args) + (!label ,(merge-html-args (if (not mand) '() `#((class "mand"))) labelargs) ,(parse-mess label)) + ,(if (string=? tip "") '() (tip1 label tip))) + (!div ,(merge-html-args `#((data-field-span ,width)) div2args) + (!span ,(merge-html-args `#((class ,(string-append (if (not mand) "" "mand ") classes))) spanargs) + ,(parse-mess text)) + ,(if (string=? tip "") '() (tip2 label tip))))) + + +; #:divargs - arguments for the outer div of the calculationresult +; #:labelargs - arguments for the label +; #:inputargs - arguments for the input field +; #:readonly - if the inputfield should be read-only +; #:maxlength - the maxlength argument value +; #:size - the site argument value +(define* (calculationinput width classes label name mand #:key (divargs #()) (labelargs #()) (inputargs #()) (readonly #f) (maxlength -1) (size -1) . opt) + `(!div ,(merge-html-args `#((data-field-span ,width)(style "background: lightgrey;")) divargs) + (!label ,(merge-html-args `#((for ,name)) labelargs) ,(parse-mess label)) + (!input ,(merge-vectors `#((class ,(string-append (if mand "mand" "") classes)) + (type "text") + (name ,name) + (value "")) + (if readonly `#((readonly "readonly")) #()) + (if (not (= maxlength -1)) `#((maxlength ,maxlength)) #()) + (if (not (= size -1)) `#((size ,size)) #()) + inputargs)))) + +; #:divargs - arguments for the outer div of the button +; #:buttonargs - arguments for the button itself +(define* (simplebutton width onclick label #:key (divargs #()) (buttonargs #()) . opt) + `(!div ,(merge-html-args `#((data-field-span ,width)) divargs) + (!button ,(merge-html-args `#((onclick ,onclick)) buttonargs) ,(parse-mess label)))) + +;; Simple definition of fold-left (left fold) +(define (fold-left f acc lst) + (if (null? lst) + acc + (fold-left f (f acc (car lst)) (cdr lst)))) + + +(define (merge-vectors . vectors) + (define (merge-alists a1 a2) + (let ((filtered-a1 (filter (lambda (pair) (not (assoc (car pair) a2))) a1))) + (append a2 filtered-a1))) + + (define alists (map vector->list vectors)) + + (define merged-alist + (fold-left merge-alists '() alists)) + + (list->vector merged-alist)) + + +(define (merge-html-args old . new) + (merge-vectors old (if (null? new) #() (car new)))) + +(define (fieldsimple width lis) + `(!div #(data-field-span ,width) + ,lis)) + +; #:divargs - arguments for the outer div of the input +; #:tip - tooltip text +; #:usesprefix - if the input should use d.name or s.name, #t for s.name +; #:labelargs - arguments for the label +; #:inputargs - arguments for the input +; #:tipname - use this instead of the name parameter for the name of the tip +; #:warning - add a warning after the label-text in the label +; #:readonly - readonly/protected +; #:type - the type of the input field (default "text") +(define* (inputsimple width classes label name mand #:key (divargs #()) (tip "") (usesprefix #f) (labelargs #()) (inputargs #()) (tipname "") (warning "") (ng-value #t) (readonly #f) (type "text") (value "")) + `((!div ,(merge-html-args + `#((data-field-span ,width)) + divargs) + + (!label ,(merge-html-args + (if (not mand) #() `#((class "mand"))) + labelargs) + ,(string-append + (parse-mess-as-text label) + (if (string=? warning "") + "" + (string-append "" (parse-mess warning) ""))) + ,(if (string=? tip "") '() (tip1 (if (string=? tipname "") name tipname) (parse-mess tip)))) + + (!input ,(merge-vectors + (if (string=? (string-append (if (not mand) "" "mand ") classes) "") + `#((name ,name) (type "text")) + `#((class ,(string-append (if (not mand) "" "mand ") classes)) (name ,name) (type ,type)) + ) + (if ng-value + `#((value ,(string-append (if usesprefix "{{ s." "{{ d.") (if (string=? value "") name value) " }}"))) + `#() + ) + (if readonly + `#((protected protected)(readonly readonly)) + `#()) + inputargs)) + + ,(if (string=? tip "") '() (tip2 (if (string=? tipname "") name tipname) (parse-mess tip)))))) + +; OBSOLETE +(define* (inputwithwarning width classes label warning name mand #:key (divargs #()) (tip "") (usesprefix #f) (labelargs #()) (inputargs #()) (tipname "") . opt) + `(,(inputsimple width classes label name mand #:warning warning))) + +; OBSOLETE +(define (inputwithextras width classes style label name tipname mand . opt) + `(,(inputsimple width classes label name mand #:tipname tipname))) + + +(define (centeredtextinfo style1 title text) + `((!center #() (!b #() ,(parse-mess title))) + (!p #(style ,style1) ,(parse-mess text)) + )) + +; items - '(#(value labeltext) #(value labeltext)) +(define* (simpleradiobuttons name items #:key (backgroundcolor "#eee") (mand #f)) + `(!div #(style ,(string-append "background: " backgroundcolor "; border: 1px solid lightgrey; padding: .3em;")) + ,@(let iter ((item items)) + (if (null? item) + '() + (append + `((!div #() + (!p #() + (!input + ,(if mand + `#((type "radio") (id ,(if (<= (vector-length (car item)) 2) name (vector-ref (car item) 2))) (class "mand") (name ,name) (value ,(vector-ref (car item) 0))) + `#((type "radio") (id ,(if (<= (vector-length (car item)) 2) name (vector-ref (car item) 2))) (name ,name) (value ,(vector-ref (car item) 0))))) + (!label + #((for ,(vector-ref (car item) 0))) ,(parse-mess (vector-ref (car item) 1)))))) + (iter (cdr item)) + )) + ) + )) + +; texts - a list +(define* (legalstuffinfo title texts #:key (optionalelements `())) + `((!h3 #() ,(parse-mess title)) + ,@(let iter ((text texts)) + (if (null? text) + '() + (append + `(,(info-paragraph (car text) #:class "" #:style "" #:want-space #f)) + (iter (cdr text))))) + ,optionalelements)) + +(define (checkboxforlegalstuff name text) + `(!p #() + (!input #((class "mand") (type "checkbox") (name ,name) (id ,name))) + (!b #() ,(parse-mess text)))) + +(define (small width label) + `(!div #(data-field-span "1") + (!small #() ,(parse-mess label)))) + + +; #:divargs - arguments for the outer div of the textarea +; #:tip - tooltip text +; #:usesprefix - if the input should use d.name or s.name, #t for s.name +; #:labelargs - arguments for the label +; #:textareaargs - arguments for the textarea +; #:tipname - use this instead of the name parameter for the name of the tip +; #:warning - add a warning after the label-text in the label +; #:label - if the textare should have an aditional label +(define* (textareasimple width classes name mand #:key (divargs #()) (tip "") (labelargs #()) (textareaargs #()) (tipname "") (warning "") (nolabel #f) (prefix "s") (label "") . opt) + `((!div ,(merge-html-args + `#((data-field-span ,width)) + divargs) + + ,(if (string=? label "") '() `(!label ,(merge-html-args + (if (not mand) #() `#((class "mand"))) + labelargs) + ,(string-append + (parse-mess-as-text label) + (if (string=? warning "") + "" + (string-append "" (parse-mess warning) ""))) + ,(if (string=? tip "") '() (tip1 (if (string=? tipname "") name tipname) (parse-mess tip))))) + + (!textarea ,(merge-html-args + `#((class ,(string-append (if (not mand) "" "mand ") classes)) + (name ,name)) + textareaargs) ,(string-append "{{ " prefix "." name " }}"))) + + ,(if (string=? tip "") '() (tip2 (if (string=? tipname "") name tipname) (parse-mess tip))))) + +(define simple-from-until-as-month-and-year-descending + `(,(selectsimple 1 "" "?von_monat" "startmonat" + (let iter ((mth 1)) + (if (<= mth 12) + (cons `(,(convert mth) ,(convert mth)) (iter (+ mth 1))) + '())) + #t #:prefix "b") + ,(selectsimple 1 "" "?von_jahr" "startjahr" + (let iter ((yr (current-year))) + (if (>= yr 1970) + (cons `(,(convert yr) ,(convert yr)) (iter (- yr 1))) + '())) + #t #:prefix "b") + ,(selectsimple 1 "" "?bis_monat" "endemonat" + (let iter ((mth 1)) + (if (<= mth 12) + (cons `(,(convert mth) ,(convert mth)) (iter (+ mth 1))) + '())) + #t #:prefix "b") + ,(selectsimple 1 "" "?bis_jahr" "endejahr" + (let iter ((yr (current-year))) + (if (>= yr 1970) + (cons `(,(convert yr) ,(convert yr)) (iter (- yr 1))) + '())) + #t #:prefix "b"))) + +(define simple-from-as-month-and-year-descending + `(,(selectsimple 1 "" "?monat" "startmonat" + (let iter ((mth 1)) + (if (<= mth 12) + (cons `(,(convert mth) ,(convert mth)) (iter (+ mth 1))) + '())) + #t #:prefix "b") + ,(selectsimple 1 "" "?jahr" "startjahr" + (let iter ((yr (current-year))) + (if (>= yr 1970) + (cons `(,(convert yr) ,(convert yr)) (iter (- yr 1))) + '())) + #t #:prefix "b"))) + + +(define* (moneytable name in ass out lia prefix #:key (ng-prefix "d") (in-class-outer "in") (in-class "sum_in")) `( + (!p #((style "color:black;")) + ,(parse-mess name)) + + ,(if in + `( + ,(row 3 + `( + (!div #((data-field-span "2") (style "color: black;")) + (!b ,(parse-mess (string-append prefix "in")))) + + (!div #((data-field-span "1") (style "color: black;")) + (!b ,(parse-mess (string-append prefix "betrag")))) + )) + + (!div #(class ,in-class-outer) ,in) + ,(row 3 + `( + (!div #((data-field-span "2")) + (!b #((style "color: black;")) ,(parse-mess (string-append prefix "summe")))) + + (!div #((data-field-span "1")) + (!b #((style "color: black;") (class "sumfield") (id ,in-class)) + ,(string-append "{{ " ng-prefix "." in-class " }}"))) + ) + #:divargs #((style "background: darkgrey;"))) + + (!br) + )) + + ,(if ass + `( + ,(row 3 + `( + (!div #((data-field-span "2") (style "color: black;")) + (!b ,(parse-mess (string-append prefix "ass")))) + + (!div #((data-field-span "1") (style "color: black;")) + (!b ,(parse-mess (string-append prefix "betrag")))) + )) + + (!div #(class ass) ,ass) + + ,(row 3 + `( + (!div #((data-field-span "2")) + (!b #((style "color: black;")) ,(parse-mess (string-append prefix "summe")))) + + (!div #((data-field-span "1")) + (!b #((style "color: black;") (class "sumfield") (id "sum_ass")) + ,(string-append "{{ " ng-prefix ".sum_ass }}"))) + ) #:divargs #((style "background: darkgrey;"))) + + (!br) + )) + + ,(if out + `( + ,(row 3 + `( + (!div #((data-field-span "2") (style "color: black;")) + (!b ,(parse-mess (string-append prefix "out")))) + + (!div #((data-field-span "1") (style "color: black;")) + (!b ,(parse-mess (string-append prefix "betrag")))) + )) + + (!div #(class out) ,out) + + ,(row 3 + `( + (!div #((data-field-span "2")) + (!b #((style "color: black;")) ,(parse-mess (string-append prefix "summe")))) + + (!div #((data-field-span "1")) + (!b #((style "color: black;") (class "sumfield") (id "sum_out")) + ,(string-append "{{ " ng-prefix ".sum_out }}"))) + ) + #:divargs #((style "background: darkgrey;"))) + + ,(row 3 + `( + (!div #((data-field-span "2")) + (!b #((style "color: black;")) ,(parse-mess (string-append prefix "semfee")))) + + (!div #((data-field-span "1")) + (!input #((type "text") (class "value mand") (name "semfee") (value ,(string-append "{{ " ng-prefix ".semfee }}"))))) + ) + #:divargs #((style "background: #c1c1c1;"))) + + (!br) + )) + + ,(if lia + `( + ,(row 3 + `( + (!div #((data-field-span "2") (style "color: black;")) + (!b ,(parse-mess (string-append prefix "lia")))) + + (!div #((data-field-span "1") (style "color: black;")) + (!b ,(parse-mess (string-append prefix "betrag")))) + )) + + (!div #(class lia) ,lia) + + ,(row 3 + `( + (!div #((data-field-span "2")) + (!b #((style "color: black;")) ,(parse-mess (string-append prefix "summe")))) + + (!div #((data-field-span "1")) + (!b #((style "color: black;") (class "sumfield") (id "sum_lia")) + ,(string-append "{{ " ng-prefix ".sum_lia }}"))) + ) + #:divargs #((style "background: darkgrey;"))) + )) + )) + +(define* (moneytextinputrow class text input #:key (prefix "d")) `( + ,(row 3 `( + (!div #((data-field-span "2")) + (!b ,(parse-mess text)) + ) + (!div #((data-field-span "1")) + (!input #((name ,input) (class ,class) (value ,(string-append "{{ " prefix "." input " }}")) (type "text"))) + ) + )) +)) + +(define* (moneyinputinputrow class input1 input2 #:key (prefix "d") (placeholder1 "") (placeholder2 "")) `( + ,(row 3 `( + (!div #((data-field-span "2")) + (!input #((name ,input1) (value ,(string-append "{{ " prefix "." input1 " }}")) (type "text") (placeholder ,(parse-mess-as-text placeholder1)))) + + ) + (!div #((data-field-span "1")) + (!input #((name ,input2) (class ,class) (value ,(string-append "{{ " prefix "." input2 " }}")) (type "text") (placeholder ,(parse-mess-as-text placeholder2)))) + ) + )) +)) + +(define* (kontoangabe rows) `( + ,@(let iter ((r rows)) + (if (null? r) + '() + (append `( + ,(row (count-elements (car r)) `( + ,@(let iter ((e (car r))) + (if (null? e) + '() + (append `( + ,(inputsimple 1 "" (caar e) (cadar e) (caddr (cdar e)) #:value (caddar e) #:ng-value #t) + ) (iter (cdr e))) + ) + ) + )) + ) (iter (cdr r))) + ) + ) +)) + +(define (count-elements lst) + (if (null? lst) + 0 + (+ 1 (count-elements (cdr lst))))) + +(define bundeslaender + `(("bw" "?land_bw") + ("by" "?land_by") + ("be" "?land_be") + ("bb" "?land_bb") + ("hb" "?land_hb") + ("hh" "?land_hh") + ("he" "?land_he") + ("mv" "?land_mv") + ("ni" "?land_ni") + ("nw" "?land_nw") + ("rp" "?land_rp") + ("sl" "?land_sl") + ("sn" "?land_sn") + ("st" "?land_st") + ("sh" "?land_sh") + ("th" "?land_th"))) diff --git a/futstips.php b/futstips.php new file mode 100644 index 0000000..1991090 --- /dev/null +++ b/futstips.php @@ -0,0 +1,19 @@ + += NOW() AND Stipendien.Förderbeginn > NOW() AND (ds.accepted=1 OR ds.accepted IS NULL OR ds.accepted = -1)"; +require_once __DIR__ . '/stips_common.php'; diff --git a/handle_css.php b/handle_css.php new file mode 100644 index 0000000..f53892c --- /dev/null +++ b/handle_css.php @@ -0,0 +1,18 @@ +minify(); diff --git a/handle_js.php b/handle_js.php new file mode 100644 index 0000000..bfddadf --- /dev/null +++ b/handle_js.php @@ -0,0 +1,18 @@ +minify(); diff --git a/lang-tags.scm b/lang-tags.scm new file mode 100644 index 0000000..2a9305e --- /dev/null +++ b/lang-tags.scm @@ -0,0 +1,8 @@ +(define elements + (append elements + '((!php 3 "?php " " ?") + (!? 3 "?php " " ?") + (!= 3 "?php echo " "; ?") + (!twig 3 {{ }}) + (!asp 3 "% " " %") + (!% 3 "% " " %")))) diff --git a/orga.php b/orga.php new file mode 100644 index 0000000..ef61680 --- /dev/null +++ b/orga.php @@ -0,0 +1,83 @@ + +prepare($sql); + $stmt->bind_param('i', $_GET['id']); + $stmt->execute(); + $r = $stmt->get_result(); + $l = $r->fetch_object(); + $orgaID = $l->ID; + $superorgaID = $l->SuperorgaID; + + $_title = 'Organisation: ' . $l->Name . ' (ID '. $_GET['id'] . ')'; + include_once __DIR__ . "/header.php"; +?> + +

      +  +  + + '/db/orga/' + ]; + $entrytable = "Organisationen"; + + include __DIR__ . '/autoform.php'; + $stmt->reset(); +})(); + +(function () use ($orgaID) { + $globstring = "{" . $orgaID . "orga}*_"; + require_once __DIR__ . '/doc.php'; +})(); + +include_once __DIR__ . '/notesXorga.php'; +include_once __DIR__ . '/persXorga.php'; +include_once __DIR__ . '/donationsXorga.php'; +include_once __DIR__ . '/eventsXorga.php'; + +require_once __DIR__ . "/jumper.php"; +include_once __DIR__ . '/footer.php'; diff --git a/orgaXpers.php b/orgaXpers.php new file mode 100644 index 0000000..6f6f4a9 --- /dev/null +++ b/orgaXpers.php @@ -0,0 +1,49 @@ + +prepare($sql); + $stmt->bind_param('i', $persID); + $stmt->execute(); + $r = $stmt->get_result(); + + $order = '[[2, "asc"], [0, "asc"]]'; + $editable = [ + 'Ansprechpartner', + 'Funktion', + 'Abteilung' + ]; + $entrytable = 'Personen_Organisationen'; + $types = ['Ansprechpartner' => 'checkbox']; + + $nospinner = true; + include __DIR__ . '/autotable.php'; + $stmt->reset(); +})(); diff --git a/orgapersons.php b/orgapersons.php new file mode 100644 index 0000000..65ce0bd --- /dev/null +++ b/orgapersons.php @@ -0,0 +1,142 @@ + +prepare($sql); + if (isset($_GET['persid'])) $stmt->bind_param('i', $_GET['persid']); + $stmt->execute(); + $r = $stmt->get_result(); + $id = "orgapersons"; + $order = '[[1, "asc"]]'; + $types = [ + 'apstip' => 'checkboxinverse', + 'apsf' => 'checkboxinverse', + 'apop' => 'checkboxinverse', + 'apstr' => 'checkboxinverse', + 'apfi' => 'checkboxinverse', + + 'elsv' => 'checkboxinverse', + 'elfa' => 'checkboxinverse', + 'elsft' => 'checkboxinverse', + 'elosv' => 'checkboxinverse', + + 'ernl' => 'checkboxinverse', + 'erwk' => 'checkboxinverse', + 'erwm' => 'checkboxinverse', + + 'Ansprechpartner' => 'checkboxinverse', + 'Einladung_Stipendienvergabe' => 'checkboxinverse', + 'Einladung_SFT' => 'checkboxinverse', + 'Einladung_Fördereraustausch' => 'checkboxinverse', + ]; + $editable = [ + 'apstip', + 'apsf', + 'apop', + 'apstr', + 'apfi', + + 'elsv', + 'elfa', + 'elsft', + 'elosv', + + 'ernl', + 'erwk', + 'erwm', + + 'Funktion', + 'Abteilung', + + 'Ansprechpartner', + 'Einladung_Stipendienvergabe', + 'Einladung_SFT', + 'Einladung_Fördereraustausch', + ]; + $entrytable = 'Personen_Organisationen'; + $checkboxes = true; + $bottom = << + Ausgewählte + +
    + +EOD; + $pdf_sql = $email_sql = " +SELECT + Organisationen.Name AS Orga, CONCAT(Organisationen.ID, 'orga-', 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_Organisationen.* +FROM Personen_Organisationen +INNER JOIN Personen ON Personen_Organisationen.Person = Personen.ID +LEFT JOIN Organisationen ON Personen_Organisationen.Organisation = Organisationen.ID +WHERE Personen_Organisationen.ID IN "; + include __DIR__ . '/autotable.php'; + include __DIR__ . '/autoactions.php'; + include __DIR__ . '/autoemail.php'; + include __DIR__ . '/autopdf.php'; + include __DIR__ . '/autoevent.php'; + $stmt->reset(); +})(); + +include_once __DIR__ . "/footer.php"; diff --git a/orgas.php b/orgas.php new file mode 100644 index 0000000..90829aa --- /dev/null +++ b/orgas.php @@ -0,0 +1,42 @@ + + + +

    + + diff --git a/patron.php b/patron.php new file mode 100644 index 0000000..6a71ed3 --- /dev/null +++ b/patron.php @@ -0,0 +1,224 @@ + +prepare($sql); + $stmt->bind_param('i', $_GET['id']); + $stmt->execute(); + $r = $stmt->get_result(); + $l = $r->fetch_object(); + $foerdID = $l->FoerdID; + $persID = $l->PersID; + $orgaID = $l->OrgaID; + + $_title = 'Förderer: ' . $l->Organisation . ' (ID ' . $_GET['id'] . ')'; + include_once __DIR__ . "/header.php"; +?> + +

    +  +  + +prepare($sql); + $stmt->bind_param('i', $orgaID); + $stmt->bind_result($id, $nachname, $vorname); + $stmt->execute(); + $persXorga = []; + while ($stmt->fetch()) { + $persXorga[$id] = $nachname . ", " . $vorname; + } + $stmt->reset(); + + $id = "patron"; + $entrytable = "Förderer"; + $def = [ + "Allgemein", + [10, '_OrgaID', '_=6Organisation', '=3legal|rechtsformen~Rechtsform'], + [10, '_A~ ', '_=3strasse', '_=3adresszusatz', '_plz', '_=2ort'], + [10, '_A~ ', '=9altname~Abweichende Fördererbezeichnung (als Auftritt gegenüber Stipendiaten)'], + [10, '_ZustID', '=4Zuständig|demo', '=5Kontakt hergestellt'], + "Allgemeine Vertragsbeziehungen", + [4, 'AnfrageWF#isodate~Anfrage (Weiter)-finanzierung', 'Mittelanforderung#isodate~Mittelanforderung vom', 'Annahmeanordnung#isodate~Annahmeanordnung vom', 'Zusage#isodate~Zusage Förderung am'], + [4, 'Vertrag läuft aus#isodate', '+im Newsletter erwähnt', '+SEPA-Lastschrift erteilt~SEPA', '+Zahlungsaufforderung gewünscht'], + [10, '_APZuwID', '=4zuwendungen|persXorga~Ansprechpartner Zuwendungen', '_APID', '=4Ansprechpartner Stipendiaten|persXorga~Erster Ansprechp./Danksagungskontakt für die Stipendiaten'], + ['Zahlungsanmerkung'], + ['*Widmung'], + ['*notes~Generelle Bemerkungen'] + ]; + include __DIR__ . '/autoform.php'; + $stmt->reset(); +})(); + +(function () use ($foerdID, $orgaID) { + $globstring = "{" . $foerdID . "patron," . $orgaID . "orga}*_"; + require_once __DIR__ . '/doc.php'; +})(); + +(function () use ($foerdID) { + if (!assureInt($foerdID)) return; + + $id = "contractsXpatron3"; + $title = "Verträge zum Förderer"; + $off = true; + $sql_a = " +SELECT contracts.id AS VertrID, CONCAT(calls.name, ' [', `call`, ']') AS 'Call', ls AS Stipendien, remark, ss AS Sozial, (ls * 12 * 150 + ss * 12 * 150) AS Summe, +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, +IF(GROUP_CONCAT(DATE(Spenden.Geldeingang) SEPARATOR ', ') IS NULL, CONCAT(GROUP_CONCAT(DATE(SuperSpenden.Geldeingang) SEPARATOR ', '), '*'), GROUP_CONCAT(DATE(Spenden.Geldeingang) SEPARATOR ', ')) AS Geldeingänge, +contact AS PersID, CONCAT(Personen.Nachname, ', ', Personen.Vorname) AS `Ansprechpartner`, valid_from AS `Gültig von`, valid_to AS `Gültig bis` +FROM contracts +LEFT JOIN Personen ON contracts.contact = Personen.ID +LEFT JOIN calls ON contracts.`call` = calls.shorthand +LEFT JOIN Förderer ON contracts.patron = Förderer.ID +LEFT JOIN Spenden ON contracts.id = Spenden.contract +LEFT JOIN Spenden AS SuperSpenden ON Spenden.SuperID = SuperSpenden.ID +"; + $sql = $sql_a . ' WHERE contracts.patron=' . $foerdID; + $sql .= " GROUP BY contracts.id "; + $thdef = ['VertrID', 'Call', 'Stipendien', 'remark', 'Summe', 'Eingang', 'Fehlbetrag', 'Geldeingänge', 'PersID', 'Ansprechpartner', 'Gültig von', 'Gültig bis']; + $order = '[[2, "desc"], [5, "desc"]]'; + $checkboxes = true; + $idcell = "VertrID"; + $bottom = << + Ausgewählte + + + +EOD; + $ajax = $nospinner = true; + include __DIR__ . '/autotable.php'; +})(); + +include_once __DIR__ . '/notesXpatron.php'; + +(function () use ($foerdID) { + if (!assureInt($foerdID)) return; + + $id = "stipsXpatron_Patron"; + $title = "Stipendien zum Förderer"; + $off = true; + $sql_a = " +SELECT + Stipendien.ID AS StipID, + Personen.Nachname, Personen.Vorname, Personen.ID AS PersID, Personen.Email, + Stipendien.Jahr, + CONCAT(DATE(Stipendien.Förderbeginn), ' – ', DATE(Stipendien.Förderende)) AS Zeitraum, + HSOrga.Name AS Hochschule, + Studiengänge.Name AS Studiengang, + Förderarten.Name AS Förderart +FROM Stipendien +LEFT JOIN Förderer ON Stipendien.Förderer = Förderer.ID +LEFT JOIN Organisationen ON Förderer.Organisation = Organisationen.ID +LEFT JOIN Hochschulen ON Stipendien.Hochschule = Hochschulen.ID +LEFT JOIN Organisationen AS HSOrga ON Hochschulen.Organisation = HSOrga.ID +LEFT JOIN Studiengänge ON Stipendien.Studiengang = Studiengänge.ID +LEFT JOIN Förderarten ON Stipendien.Förderart = Förderarten.ID +LEFT JOIN Personen ON Stipendien.Person = Personen.ID +"; + $sql = $sql_a . ' WHERE Stipendien.Förderer=' . $foerdID; + $order = '[[6, "desc"], [2, "asc"], [3, "asc"]]'; + $thdef = ['StipID', 'Nachname', 'Vorname', 'PersID', 'Email', 'Jahr', 'Zeitraum', 'Hochschule', 'Studiengang', 'Förderart']; + $checkboxes = true; + $idcell = "StipID"; + $ajax = $nospinner = true; + include __DIR__ . '/autotable.php'; + + $email_sql = preg_replace('/^\s*SELECT\s/', 'SELECT Stipendien.ID AS RUID, ', $sql_a) . ' WHERE Stipendien.ID IN '; + include __DIR__ . '/autoactions.php'; + include __DIR__ . '/autoemail.php'; + include __DIR__ . '/autoevent.php'; +})(); +?> + + + + 0, CONCAT(contracts.`call`, '-', Organisationen.Name), '') AS Vertrag, + Spenden.Person AS PersID, + Personen.Nachname, + Personen.Vorname +FROM Spenden +LEFT JOIN Personen ON Spenden.Person = Personen.ID +LEFT JOIN contracts ON Spenden.contract = contracts.ID +LEFT JOIN Förderer ON contracts.patron = Förderer.ID +LEFT JOIN Organisationen ON Förderer.Organisation = Organisationen.ID +WHERE contracts.patron=" . $foerdID; + $order = '[[1, "desc"]]'; + $thdef = ['SpendenID', 'Betrag', 'Geldeingang', 'VertrID', 'Vertrag', 'PersID', 'Nachname', 'Vorname']; + $ajax = $nospinner = true; + include __DIR__ . '/autotable.php'; +})(); + +include_once __DIR__ . '/donationsXorga.php'; +include_once __DIR__ . '/eventsXorga.php'; +include_once __DIR__ . '/persXorga.php'; + +require_once __DIR__ . "/jumper.php"; +include_once __DIR__ . '/footer.php'; diff --git a/patrons.php b/patrons.php new file mode 100644 index 0000000..12d2901 --- /dev/null +++ b/patrons.php @@ -0,0 +1,19 @@ + +(unabhängig vom Status)"; +$_constraint = " GROUP BY Förderer.ID"; +require_once __DIR__ . "/patrons_common.php"; diff --git a/patrons_by_year.php b/patrons_by_year.php new file mode 100644 index 0000000..2f4050c --- /dev/null +++ b/patrons_by_year.php @@ -0,0 +1,41 @@ + + 0"; + $_title .= "Sozialstipendiums-"; +} + +$ds = ""; +if (isset($_GET['ds'])) { + $ds = "AND contracts.ls > 0"; + $_title .= "Leistungstipendiums-"; +} + +$ideell = ""; +if (isset($_GET['ideell'])) { + $ds = "AND (contracts.ls < 1 AND contracts.ss < 1)"; + $_title .= "Ideellen-"; +} + +$_title .= "Förderer im Förderjahr " . $_year . "/" . ($_year + 1) . " (anhand Verträge)"; + +$_constraint = "WHERE (contracts.valid_from >= '" . $_year . "-10-01' AND contracts.valid_from < '" . ($_year + 1) . "-10-01' AND contracts.id > 0) " . $social . " " . $ds . " " . $ideell . " GROUP BY Förderer.ID"; +require_once __DIR__ . "/patrons_common.php"; diff --git a/patrons_common.php b/patrons_common.php new file mode 100644 index 0000000..b01153b --- /dev/null +++ b/patrons_common.php @@ -0,0 +1,155 @@ + +prepare($sql); + $stmt->execute(); + $r = $stmt->get_result(); + $id = "patrons3"; + $order = '[[3, "asc"]]'; + $entrytable = 'Förderer'; + $idcell = "FoerdID"; + $idcellreal = "ID"; + $types = [ + 'Bewerbungsunterlagen verschickt' => 'checkbox', + 'gemeinsames Foto' => 'checkbox', + 'Pressemitteilung' => 'checkbox', + 'Im Matching-Tool eingetragen' => 'checkbox', + 'SEPA-Lastschrift erteilt' => 'checkbox', + 'Zahlungsaufforderung gewünscht' => 'checkbox', + 'im Newsletter erwähnt' => 'checkbox', + 'AnfrageWF' => 'date', + 'Annahmeanordnung' => 'date', + 'Mittelanforderung' => 'date', + 'Zusage' => 'date', + ]; + $editable = [ + 'Bewerbungsunterlagen verschickt', + 'gemeinsames Foto', + 'Pressemitteilung', + 'bezahlt bis', + 'Betrag bezahlt', + 'Vertrag läuft aus', + 'Anzahl Leistungsstipendien', + 'Anzahl Sozialstipendien', + 'Zahlungsanmerkung', + 'Kontakt hergestellt', + 'Im Matching-Tool eingetragen', + 'SEPA-Lastschrift erteilt', + 'Zahlungsaufforderung gewünscht', + 'im Newsletter erwähnt', + 'AnfrageWF', + 'Mittelanforderung', + 'Annahmeanordnung', + 'Zusage', + ]; + $checkboxes = true; + include __DIR__ . '/autotable.php'; + $stmt->reset(); + + $email_sql = " +SELECT Förderer.ID AS FoerdID, Organisationen.Name AS Foerderer, CONCAT(Förderer.ID, 'patron', '-', Personen.ID, 'person') AS uid, Personen.*, Organisationen.strasse AS Strasse, Organisationen.plz AS PLZ, Organisationen.ort AS Ort, Organisationen.Name AS Organisation, ROUND(Förderer.`Betrag bezahlt`, 0) AS Betrag, CONCAT(Förderer.ID, 'patron', '-', Personen.ID, 'person') AS genuid +FROM Förderer +LEFT JOIN Organisationen ON Förderer.Organisation = Organisationen.ID + "; + if ($_GET['head']) { + $email_sql .= " LEFT JOIN Personen ON Förderer.`Ansprechpartner Stipendiaten` = Personen.ID "; + } else { + $email_sql .= " LEFT JOIN Personen ON Förderer.`Ansprechpartner Stipendiaten` = Personen.ID "; + } + $email_sql .= " WHERE Förderer.ID IN "; + $pdf_sql = $email_sql; + $pdf_file = "anschreiben_katja"; + $payload_sql = "SELECT Personen.ID AS PersID, Förderer.ID AS FoerdID, Personen.Geschlecht, Personen.`informale Ansprache`, Personen.Anrede, Personen.Ansprache, Personen.Titel, Personen.Vorname AS Vorname, Personen.Nachname AS Nachname, Personen_Organisationen.* FROM Personen_Organisationen LEFT JOIN Förderer ON Personen_Organisationen.Organisation = Förderer.Organisation LEFT JOIN Personen ON Personen_Organisationen.Person = Personen.ID WHERE Förderer.ID 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_once __DIR__ . "/footer.php"; diff --git a/patronspersons.php b/patronspersons.php new file mode 100644 index 0000000..e899b47 --- /dev/null +++ b/patronspersons.php @@ -0,0 +1,28 @@ + +(anhand Verträge)"; +$_subtitle = '
    Als "aktueller" bzw. "zukünftiger" Förderer wird in dieser Abfrage jeder Förderer angesehen, der einen Vertrag zugewiesen hat, welcher zum aktuellen Zeitpunkt oder in der Zukunft gültig sein wird (anhand "gültig von"/"bis"). Zusätzlich kann ggf. noch auf die Ausschreibung (Call) eingegrenzt werden.
    '; + +$call = '%'; +if (isset($_GET['call']) && $_GET['call'] != '') { + $call = filter_var($_GET['call'], FILTER_SANITIZE_STRING); + $_title .= " (Call " . $call . ")"; +} + +$_constraint = "WHERE Förderer.ID IN (SELECT contracts.patron FROM contracts WHERE (contracts.valid_from <= NOW()) AND (contracts.valid_to >= NOW()) AND (contracts.`call` LIKE '" . $call . "'))"; + +require __DIR__ . "/patronspersons_common.php"; diff --git a/patronspersons_by_year.php b/patronspersons_by_year.php new file mode 100644 index 0000000..ad3ae4c --- /dev/null +++ b/patronspersons_by_year.php @@ -0,0 +1,42 @@ + + 0"; + $fragment .= "Sozialstipendiums-"; +} + +$ds = ""; +if (isset($_GET['ds'])) { + $ds = "AND contracts.ls > 0"; + $fragment .= "Leistungstipendiums-"; +} + +$ideell = ""; +if (isset($_GET['ideell'])) { + $ds = "AND (contracts.ls < 1 AND contracts.ss < 1)"; + $fragment .= "Idellen-"; +} + +$_title = "Alle Personen bei " . $fragment . "Förderern des Förderjahres " . $_year . "/" . ($_year + 1) . " (anhand Verträge)"; + +$_constraint = "WHERE Förderer.ID IN (SELECT contracts.patron FROM contracts WHERE (contracts.valid_from >= '" . $_year . "-10-01') AND (contracts.valid_from < '" . ($_year + 1) . "-10-01') " . $social . " " . $ds . " " . $ideell . ")"; + +require __DIR__ . "/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 @@ + + 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"; diff --git a/pdf.php b/pdf.php new file mode 100644 index 0000000..7c912ea --- /dev/null +++ b/pdf.php @@ -0,0 +1,516 @@ + + + + + +

    PDF-Dokumente generieren + 1) { ?> +für Datensätze + +für Datensatz + +

    + + +

    Demo-Flag ist an

    + + +
    + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
    OrganisationVä
    Anrede|BriefkopfV | TitelV
    VornameVNachnameV
    StraßeVäAdresszusatzVä
    PLZVäOrtVä
    V: Vorschau, ä: änderbar
    Dokument + + +
    Betreff
    Datum
    + + + + +
    + +
    +
    +
    + + +
    + Letzter Status: +

    +
    +
    + +
    + Templates +
    +prepare($sql); + $stmt->bind_result($id, $name, $text, $subject, $date, $fn, $ts); + $stmt->execute(); +?> + +
    +
    + + + + + +reset(); +?> + +
    +
    + + + + + + + + diff --git a/pers.php b/pers.php new file mode 100644 index 0000000..0b36f46 --- /dev/null +++ b/pers.php @@ -0,0 +1,177 @@ + +prepare($sql); + $stmt->bind_param('iii', $persID, $_SESSION['auth_userid'], $persID); + $stmt->execute(); + $r = $stmt->get_result(); + $l = $r->fetch_object(); + $l->Foto = $fotolink; + $email = $l->Email; + + $_title = '' . $l->Nachname . ', ' . $l->Vorname . ' (ID ' . $persID . ')'; + include_once __DIR__ . "/header.php"; + + $id = "persview"; + $pdf_sql = $email_sql = "SELECT *, DATE(Geburtsdatum), CONCAT('', Personen.ID, 'person') AS uid FROM Personen WHERE Personen.ID IN "; + include __DIR__ . '/autoemail.php'; + include __DIR__ . '/autopdf.php'; +?> + +

    +  +  +  +  +  +  +  + + + + + +    ', '_^m~m ', '_^d~div'], + ['informale Ansprache', 'Ansprache', 'Titel', 'salutation|salutations~Individ. autom. Ansprache (' . $_SESSION['auth_user'] . ')'], + ['Vorname', 'Nachname', 'Geburtsname', 'user'], + ['!Foto', 'Geburtsdatum#isodate', 'Geburtsort', 'Staatsangehörigkeit|laenderByID'], + "Kontaktdaten", + ['Straße', 'Adresszusatz'], + ['PLZ', 'Ort', 'Land|laenderByID'], + ['Email#email', 'Email-Privat#email', 'Email-Geschäftlich#email'], + ['Telefon', 'Handy', 'Fax', 'Homepage'], + "Spenderinformationen", + ['+Liste Privatspender', '+Ewige Liste Privatspender', '_Gesamtsumme', '_Summe letzte 365 Tage'], + "Sonstiges", + [6, '_Newsletter|threeWay~Anmeldung Newsletter', '+wuenscht_keine_Emails~wünscht keine Emails', '+stellenangebote~Erhalt Stellenangebote', '+ideellesfp~Ideelles Förderprogramm', '+infos~Erhalt Infos', '+community~Studienfonds Community'] + ]; + + if (in_array($persID, [1, 2, 3])) { + $def[] = ['*signature~Signatur']; + } + + $bottom = << + $('#persForm button.btn_w').click(function () { + $('#persForm input[name=Geschlecht]').val('weiblich').trigger('change'); + $('#persForm input[name=Anrede]').val('Frau').trigger('change'); + $('#persForm input[name=anrede_briefkopf]').val('Frau').trigger('change'); + $('#persForm input[name="informale Ansprache"]').val('Liebe').trigger('change'); + $('#persForm input[name=Ansprache]').val('Sehr geehrte Frau').trigger('change'); + return false; + }); + + $('#persForm button.btn_m').click(function () { + $('#persForm input[name=Geschlecht]').val('männlich').trigger('change'); + $('#persForm input[name=Anrede]').val('Herr').trigger('change'); + $('#persForm input[name=anrede_briefkopf]').val('Herrn').trigger('change'); + $('#persForm input[name="informale Ansprache"]').val('Lieber').trigger('change'); + $('#persForm input[name=Ansprache]').val('Sehr geehrter Herr').trigger('change'); + return false; + }); + + $('#persForm button.btn_d').click(function () { + $('#persForm input[name=Geschlecht]').val('divers').trigger('change'); + $('#persForm input[name=Anrede]').val('').trigger('change'); + $('#persForm input[name=anrede_briefkopf]').val('').trigger('change'); + $('#persForm input[name="informale Ansprache"]').val('Hallo').trigger('change'); + $('#persForm input[name=Ansprache]').val('Guten Tag').trigger('change'); + return false; + }); + +EOD; + + include __DIR__ . '/autoform.php'; + $stmt->reset(); +})(); + +(function () use ($persID) { + $globstring = "{*-" . $persID . "person*," . $persID . "person*}_"; + require_once __DIR__ . "/doc.php"; +})(); + +include_once __DIR__ . '/notesXpers.php'; +include_once __DIR__ . '/emailsXpers.php'; +include_once __DIR__ . '/stipsXpers.php'; +include_once __DIR__ . '/eventsXpers.php'; +include_once __DIR__ . '/donationsXpers.php'; +include_once __DIR__ . '/orgaXpers.php'; + +require_once __DIR__ . "/jumper.php"; +require_once __DIR__ . "/footer.php"; diff --git a/persXorga.php b/persXorga.php new file mode 100644 index 0000000..367e149 --- /dev/null +++ b/persXorga.php @@ -0,0 +1,130 @@ + + 'checkbox', + 'apstip' => 'checkbox', + 'apsf' => 'checkbox', + 'apop' => 'checkbox', + 'apstr' => 'checkbox', + 'apfi' => 'checkbox', + + 'elsv' => 'checkbox', + 'elfa' => 'checkbox', + 'elsft' => 'checkbox', + 'elosv' => 'checkbox', + + 'ernl' => 'checkbox', + 'erwk' => 'checkbox', + 'erwm' => 'checkbox', + ]; + + $bottom = << + Ausgewählte + + + + + + +EOD; + + $nospinner = true; + $insert = pathinfo(basename(__FILE__), PATHINFO_FILENAME); + include __DIR__ . '/autotable.php'; + + $pdf_sql = $email_sql = " + SELECT + CONCAT('', Personen_Organisationen.Organisation, 'orga', '-', Personen.ID, 'person') AS uid, + Personen.Geschlecht, + Personen.`informale Ansprache`, + Personen.Ansprache, + Personen.Anrede, + Personen.Titel, + Personen.Nachname, + Personen.Vorname, + Personen.Email, + Personen.Telefon, + Personen.Handy, + Organisationen.* + FROM Personen_Organisationen + INNER JOIN Personen ON Personen_Organisationen.Person = Personen.ID + LEFT JOIN Organisationen ON Personen_Organisationen.Organisation = Organisationen.ID + WHERE Personen_Organisationen.ID IN "; + include __DIR__ . '/autoactions.php'; + include __DIR__ . '/autoemail.php'; + include __DIR__ . '/autopdf.php'; + include __DIR__ . '/autoevent.php'; +})(); diff --git a/persons.php b/persons.php new file mode 100644 index 0000000..8adba95 --- /dev/null +++ b/persons.php @@ -0,0 +1,50 @@ + +prepare($sql); + $stmt->execute(); + $r = $stmt->get_result(); + $id = "persons"; + $order = '[[2, "asc"], [3, "asc"]]'; + $checkboxes = true; + $idcell = "PersID"; + $nospinner = true; + $ajax = true; + include __DIR__ . '/autotable.php'; + $stmt->reset(); + + $pdf_sql = $email_sql = "SELECT Personen.*, Personen.ID AS PersID FROM Personen WHERE ID IN "; + include __DIR__ . '/autoactions.php'; + include __DIR__ . '/autoemail.php'; + include __DIR__ . '/autopdf.php'; + include __DIR__ . '/autoevent.php'; +})(); + +include_once __DIR__ . "/footer.php"; diff --git a/php.scm b/php.scm new file mode 100644 index 0000000..4e4b82d --- /dev/null +++ b/php.scm @@ -0,0 +1,66 @@ +(define prepare-destatis-countries + '(!php "if ($stmt) $stmt->reset(); $sql = \"SELECT ID, Code, Name FROM Länder ORDER BY Prio DESC, Name ASC;\"; $stmt = $mysqli->prepare($sql); $stmt->bind_result($id, $code, $name); $stmt->execute();")) + +(define prepare-iso3166-countries-de + '(!php "if ($stmt) $stmt->reset(); $sql = \"SELECT code, de FROM iso3166 ORDER BY de;\"; $stmt = $mysqli->prepare($sql); $stmt->bind_result($code, $name); $stmt->execute();")) + +(define prepare-iso3166-countries-en + '(!php "if ($stmt) $stmt->reset(); $sql = \"SELECT code, en FROM iso3166 ORDER BY en;\"; $stmt = $mysqli->prepare($sql); $stmt->bind_result($code, $name); $stmt->execute();")) + +(define prepare-iso3166-countries + (decide-by-lang prepare-iso3166-countries-de prepare-iso3166-countries-en)) + +(define (iter-countries-php var) + (string-append "while ($stmt->fetch()) { ?> reset();$sql = \"SELECT `Standort` FROM Abschlusskombis WHERE (HochschuleText='" u "' " + "OR Hochschule='" u "') AND active=1 GROUP BY `Standort` ORDER BY `Standort`;\";" + "$stmt = $mysqli->prepare($sql);$stmt->bind_result($location);$stmt->execute();"))) + +(define (iter-u-location var) + (string-append "while ($stmt->fetch()) { ?> reset();$sql = \"SELECT `Text Studienfach` FROM Abschlusskombis WHERE (HochschuleText=? OR HochschuleText=?) AND active=1 GROUP BY `Text Studienfach` ORDER BY `Text Studienfach`;\";$stmt = $mysqli->prepare($sql);$stmt->bind_param('ss', $hsname, $hs);$stmt->bind_result($sname);$stmt->execute();")) + +(define (iter-course-of-study-php var) + (string-append "while ($stmt->fetch()) { ?> reset(); $sql = \"SELECT Organisationen.Name, KanonischerName FROM Hochschulen INNER JOIN Organisationen ON Hochschulen.Organisation = Organisationen.ID WHERE Code=?;\"; $stmt = $mysqli->prepare($sql); $stmt->bind_param('s', $hs); $stmt->bind_result($hsfull, $hsname); $stmt->execute(); $stmt->fetch(); echo $hsfull;")) + +(define prepare-fakultaet '(!php "if ($stmt) $stmt->reset(); $sql = \"SELECT Fakultät FROM Abschlusskombis WHERE (HochschuleText=? OR HochschuleText=?) AND TRIM(Fakultät) <> '' AND active=1 GROUP BY Fakultät;\"; $stmt = $mysqli->prepare($sql); $stmt->bind_param('ss', $hsname, $hs); $stmt->bind_result($fak); $stmt->execute();")) + +(define iter-fakultaet "while ($stmt->fetch()) { ?> reset(); $sql = \"SELECT `Art Studium` FROM Abschlusskombis WHERE (HochschuleText=? OR HochschuleText=?) AND `Art Studium` NOT LIKE '%Kein Abschluss%' AND active=1 GROUP BY `Art Studium`;\"; $stmt = $mysqli->prepare($sql); $stmt->bind_param('ss', $hsname, $hs); $stmt->bind_result($sart); $stmt->execute();")) + +(define iter-studiumart "while ($stmt->fetch()) { ?> reset();$sql = \"SELECT `Text Abschluss`, `Art Studium` FROM Abschlusskombis WHERE (HochschuleText=? OR HochschuleText=?) AND active=1 GROUP BY `Text Abschluss`, `Art Studium`;\";$stmt = $mysqli->prepare($sql);$stmt->bind_param('ss', $hsname, $hs);$stmt->bind_result($sabschluss, $sart);$stmt->execute();")) + +(define iter-abschlusskombis "while ($stmt->fetch()) { ?>reset();$sql = \"SELECT ID, `Text Studienfach`, `Art Studium`, `Text Abschluss` FROM Abschlusskombis WHERE (HochschuleText=? OR HochschuleText=?) AND active=1 ORDER BY `Text Studienfach`;\";$stmt = $mysqli->prepare($sql);$stmt->bind_param('ss', $hsname, $hs);$stmt->bind_result($id, $sname, $sart, $sabschluss);$stmt->execute();")) + +(define iter-studiengang "while ($stmt->fetch()) { ?>reset();$sql = \"SELECT ID, Code, Name FROM Abschlussarten;\";$stmt = $mysqli->prepare($sql);$stmt->bind_result($id, $code, $name);$stmt->execute();")) + +(define iter-abschlussarten "while ($stmt->fetch()) { ?>reset();$sql = \"SELECT ID, Name FROM AbschlüsseEinfach;\";$stmt = $mysqli->prepare($sql);$stmt->bind_result($id, $name);$stmt->execute();")) + +(define iter-abschluss "while ($stmt->fetch()) { ?> = 1970; $j--) { for ($i = 12; $i > 0; $i--) { ?> = 1970; $j--) { for ($i = date('Y') == $j ? 6 : 12; $i > 0; $i--) {?> = 1970; $j--) { for ($i = date('Y') == $j ? 6 : 12; $i > 0; $i--) {?> + + +

    + +

    Es wird gesucht in: Name, ID.

    + +prepare($sql); + $stmt->bind_param('si', $s, $_search); + $stmt->execute(); + $r = $stmt->get_result(); + if ($r->num_rows === 1 && !isset($_GET['action'])) { + $l = $r->fetch_object(); + $stmt->reset(); + $mysqli->close(); +?> + +reset(); +})(); + +include_once __DIR__ . "/footer.php"; +?> diff --git a/searchpatron.php b/searchpatron.php new file mode 100644 index 0000000..f760436 --- /dev/null +++ b/searchpatron.php @@ -0,0 +1,67 @@ + + + +

    + +

    Es wird gesucht in: Organisation, Zuständig, ID.

    + +prepare($sql); + $stmt->bind_param('ssi', $s, $s, $_search); + $stmt->execute(); + $r = $stmt->get_result(); + if ($r->num_rows === 1 && !isset($_GET['action'])) { + $l = $r->fetch_object(); + $stmt->reset(); + $mysqli->close(); +?> + +reset(); +})(); + +include_once __DIR__ . "/footer.php"; +?> diff --git a/searchperson.php b/searchperson.php new file mode 100644 index 0000000..48b1c09 --- /dev/null +++ b/searchperson.php @@ -0,0 +1,100 @@ + +Es wird gesucht in: Vorname (X) Nachname (X) { Email, Email-Privat, Email-Geschäftlich }, Ort, Geburtsdatum, ID.

    '; + +require_once __DIR__ . "/check_auth.php"; +require_once __DIR__ . "/../includes/common.php"; +require_once __DIR__ . "/lookup.php"; +include_once __DIR__ . "/header.php"; + +doTitle(); + +(function () use ($mysqli, $_search) { + $sql = ' +SELECT + ID AS PersID, Nachname, Vorname, Geschlecht, Ort, Email, Telefon, Handy, DATE(Geburtsdatum) AS Geburtsdatum, Ansprache, Anrede, `informale Ansprache` +FROM Personen +'; + $constraint = ' +WHERE + CONCAT_WS(", ", Nachname, Vorname, Email) LIKE ? + OR CONCAT_WS(", ", Nachname, Vorname, `Email-Privat`) LIKE ? + oR CONCAT_WS(", ", Nachname, Vorname, `Email-Geschäftlich`) LIKE ? + OR CONCAT_WS(" ", Vorname, Nachname) LIKE ? + OR Ort LIKE ? + OR DATE(Geburtsdatum) LIKE ? + OR ID = ? +'; + $s = '%' . $_search . '%'; + $stmt = $mysqli->prepare($sql . $constraint); + $stmt->bind_param('ssssssi', $s, $s, $s, $s, $s, $s, $_search); + $stmt->execute(); + $r = $stmt->get_result(); + if ($r->num_rows === 1 && !isset($_GET['action'])) { + $l = $r->fetch_object(); + $stmt->reset(); + $mysqli->close(); +?> + +reset(); + + if (!isset($_GET['action'])) { + $pdf_sql = $email_sql = 'SELECT Personen.ID AS PersID, Personen.* FROM Personen WHERE ID IN '; + include __DIR__ . '/autoactions.php'; + include __DIR__ . '/autoemail.php'; + include __DIR__ . '/autopdf.php'; + include __DIR__ . '/autoevent.php'; + } +})(); + +if (isset($_GET['action'])) { +?> +
    +
    + +
    +
    + + + + + + $prop) echo "0"; + else echo "1"; + + exit(0); +} + +$post_to = $_POST['to']; +if (!assureString($post_to) || strlen($post_to) < 3 || strstr($post_to, '@') === false) { + echo "1"; + exit(0); +} + +if (isset($_SESSION['demoself']) || isset($_GET['demoself']) || isset($_POST['demoself'])) { + $post_to = $_POST['from']; +} + +$sql = "SELECT ID FROM Personen WHERE (TRIM(LOWER(Email)) = ? OR TRIM(LOWER(`Email-Privat`)) = ? OR TRIM(LOWER(`Email-Geschäftlich`)) = ?) AND `wuenscht_keine_Emails` LIMIT 1;"; +$post_to_clean = trim(strtolower($post_to)); +$stmt = $mysqli->prepare($sql); +$stmt->bind_param('sss', $post_to_clean, $post_to_clean, $post_to_clean); +$stmt->bind_result($no_email); +$stmt->execute(); +$stmt->fetch(); +$stmt->reset(); + +if ($no_email > 0) { + echo "3"; + exit(0); +} + +if ( + stristr($_POST['from'], '@upb.de') === false +&& stristr($_POST['from'], '@uni-paderborn.de') === false +&& stristr($_POST['from'], '@uni-paderborn.de') === false +&& stristr($_POST['from'], '@hochschule-rhein-waal.de') === false +&& stristr($_POST['from'], '@hsrw.dein-stip.de') === false +) { + echo "4"; + exit(0); +} + +$mid = "" . time() . "-" . base_convert(bin2hex(random_bytes(8)), 16, 36) . "@hsrw.dein-stip.de"; +$boundary = "sfowl" . md5(uniqid('', true)); + +$h = "From: " . $_POST['from'] . "\r\n"; +$h .= "Sender: " . $_POST['from'] . "\r\n"; +$h .= "Reply-To: " . "deutschlandstipendium@hochschule-rhein-waal.de" . "\r\n"; +$h .= "Errors-To: " . "deutschlandstipendium@hochschule-rhein-waal.de" . "\r\n"; + +if (!isset($_SESSION['demoself'])) { + if (strlen($_POST['cc'])> 0) { + $h .= "Cc: " . $_POST['cc'] . "\r\n"; + } + + if (strlen($_POST['bcc'])> 0) { + $h .= "Bcc: " . $_POST['bcc'] . "\r\n"; + } +} + +$h .= "Message-ID: <" . $mid . ">\r\n"; +$h .= "MIME-Version: 1.0\r\n"; + +$full = ""; + +$html = '' . "\r\n" . $_POST['html']; +if (strstr($html, '/si', trim($tags), $tags); + $tags = array_unique($tags[1]); + if(is_array($tags) AND count($tags) > 0) { + if($invert == FALSE) { + return preg_replace('@<(?!(?:'. implode('|', $tags) .')\b)(\w+)\b.*?>.*?@si', '', $text); + } else { + return preg_replace('@<('. implode('|', $tags) .')\b.*?>.*?@si', '', $text); + } + } + elseif($invert == FALSE) { + return preg_replace('@<(\w+)\b.*?>.*?@si', '', $text); + } + return $text; +} + +$text = trim(strip_tags(str_replace("
    ", "\r\n", str_replace("

    ", "\r\n", str_replace("

    ", "\r\n", strip_tags(strip_tags_content($_POST['html'], ' + +

    +
    +
    + +

    Adressaten

    + + + +prepare($sql); + $stmt->execute(); + $r = $stmt->get_result(); + $id = "email"; + $order = '[]'; + $noautolinks = true; + $nodefaultlinks = true; + $links = [ + '#' => '#', + 'RUID' => '#', + 'ruid' => '#', + 'UID' => '#', + 'uid' => '#', + 'ID' => '#', + 'id' => '#', + ]; + $rowcallback = "redoRows"; + $norefresh = true; + include __DIR__ . '/autotable.php'; + $stmt->reset(); +})(); +?> + + + + + + diff --git a/templates.email.php b/templates.email.php new file mode 100644 index 0000000..6ab8e31 --- /dev/null +++ b/templates.email.php @@ -0,0 +1,72 @@ + + 'checkbox' + ]; + $editable = [ + 'active', + 'name', + 'from', + 'tags', + ]; + $checkboxes = true; + + $bottom = << + Ausgewählte + + +EOD; + + include __DIR__ . '/autotable.php'; +?> + +/dev/null || which sed` + +$sed -i -e 's/<?//g' "$1" diff --git a/update.php b/update.php new file mode 100644 index 0000000..e0e738a --- /dev/null +++ b/update.php @@ -0,0 +1,95 @@ + +close(); + exit(0); +} + +$t = filter_input(INPUT_POST, 'table', FILTER_SANITIZE_STRING, FILTER_FLAG_STRIP_BACKTICK); +$n = filter_input(INPUT_POST, 'n', FILTER_SANITIZE_STRING, FILTER_FLAG_STRIP_BACKTICK); + +$bps = "si"; +if (isset($_POST['num']) && $_POST['num'] == 'true') + $bps = "ii"; + +if ($n === 'etfav' && $t === 'email_templates') { + if ($_POST['v'] < 1) { + $sql = "DELETE FROM email_templates_fav WHERE tid=? AND userid=?"; + $stmt = $mysqli->prepare($sql); + $stmt->bind_param("ii", $_POST['id'], $_SESSION['auth_userid']); + $stmt->execute(); + $stmt->reset(); + } else { + $sql = "INSERT INTO email_templates_fav (tid, userid) VALUES (?, ?)"; + $stmt = $mysqli->prepare($sql); + $stmt->bind_param("ii", $_POST['id'], $_SESSION['auth_userid']); + $stmt->execute(); + $stmt->reset(); + } + + echo "true"; + + $mysqli->close(); + exit(0); +} + +if ($n === 'salutation' && $t === 'Personen') { + $sql = "INSERT INTO Personen_Prefs (persid, userid, salutation) VALUES (?, ?, ?)"; + $stmt = $mysqli->prepare($sql); + $stmt->bind_param("iii", $_POST['id'], $_SESSION['auth_userid'], $_POST['v']); + $stmt->execute(); + $stmt->reset(); + + $sql = "UPDATE Personen_Prefs SET salutation=? WHERE persid=? AND userid=?"; + $stmt = $mysqli->prepare($sql); + $stmt->bind_param("iii", $_POST['v'], $_POST['id'], $_SESSION['auth_userid']); + $stmt->execute(); + $stmt->reset(); + + echo "true"; + + $mysqli->close(); + exit(0); +} + +$sql = "UPDATE `" . $t . "` SET `" . $n . "`=? WHERE ID=? LIMIT 1;"; +if (isset($_POST['idcell']) && $_POST['idcell'] != 'false') { + $idcell = filter_input(INPUT_POST, 'idcell', FILTER_SANITIZE_STRING, FILTER_FLAG_STRIP_BACKTICK); + $sql = "UPDATE `" . $t . "` SET `" . $n . "`=? WHERE `" . $idcell . "`=? LIMIT 1;"; + $bps = 'ss'; + if (isset($_POST['num']) && $_POST['num'] == 'true') + $bps = 'is'; +} +$stmt = $mysqli->prepare($sql); +#echo $sql . "|" . $bps . "|" . $_POST['v'] . "|" . $_POST['id'] . "@"; + +$stmt->bind_param($bps, $_POST['v'], $_POST['id']); +$stmt->execute(); + +preg_match_all('!\d+!', $mysqli->info, $m); +if ($m[0][0] == 1 || $m[0][1] == 1 || $stmt->affected_rows == 1) + echo "true"; +else + echo "false"; + +$stmt->reset(); +$mysqli->close(); +exit(0); -- cgit v1.3.1