Merge branch 'master' of ssh://git.ccc-ffm.de/memberdb
[memberdb.git] / include / members.php
index 7abcc9d..a98ff46 100644 (file)
@@ -12,6 +12,7 @@ $SQL_CREATE_MEMBERS = sprintf('
       `bankcode` blob NOT NULL,
       `bankname` blob NOT NULL,
       `directdebit` tinyint(1) NOT NULL,
+      `ddmandatesigdate` datetime NOT NULL,
       `street` varchar(255) NOT NULL,
       `housenumber` varchar(255) NOT NULL,
       `postcode` varchar(255) NOT NULL,
@@ -35,6 +36,13 @@ $SQL_CREATE_MEMBERS = sprintf('
     ) ENGINE=InnoDB DEFAULT CHARSET=%2$s
     ', DB_TABLE_MEMBERS, DB_CHARSET);
 
+$DIRECTDEBIT_STATES = array(
+    '' => '- (Barzahler)',
+    1 => 'DTAUS',
+    2 => 'SEPA Ersteinzug',
+    3 => 'SEPA Folgeeinzug'
+); 
+    
 /* DB functions {{{ */
 
 function db_create_member($member) {
@@ -91,32 +99,33 @@ function db_get_member_with_number($member_number) {
 
 function validate_member($userdata, &$dbdata, &$validation) {/*{{{*/
     $fields = array(
-        'id'            => 'number',
-        'number'        => 'number',
-        'firstname'     => 'string',
-        'lastname'      => 'string',
-        'nickname'      => 'string',
-        'accountholder' => 'string',
-        'accountnumber' => 'string',
-        'bankcode'      => 'string',
-        'bankname'      => 'string',
-        'directdebit'   => 'bool',
-        'street'        => 'string',
-        'housenumber'   => 'string',
-        'postcode'      => 'string',
-        'city'          => 'string',
-        'country'       => 'string',
-        'email'         => 'string',
-        'phone'         => 'string',
-        'fax'           => 'string',
-        'mobile'        => 'string',
-        'jabber'        => 'string',
-        'icq'           => 'string',
-        'msn'           => 'string',
-        'aim'           => 'string',
-        'skype'         => 'string',
-        'url'           => 'string',
-        'twitter'       => 'string',
+        'id'               => 'number',
+        'number'           => 'number',
+        'firstname'        => 'string',
+        'lastname'         => 'string',
+        'nickname'         => 'string',
+        'accountholder'    => 'string',
+        'accountnumber'    => 'string',
+        'bankcode'         => 'string',
+        'bankname'         => 'string',
+        'directdebit'      => 'number',
+        'ddmandatesigdate' => 'string',
+        'street'           => 'string',
+        'housenumber'      => 'string',
+        'postcode'         => 'string',
+        'city'             => 'string',
+        'country'          => 'string',
+        'email'            => 'string',
+        'phone'            => 'string',
+        'fax'              => 'string',
+        'mobile'           => 'string',
+        'jabber'           => 'string',
+        'icq'              => 'string',
+        'msn'              => 'string',
+        'aim'              => 'string',
+        'skype'            => 'string',
+        'url'              => 'string',
+        'twitter'          => 'string',
     );
     $orig = $dbdata;
     foreach ($fields as $name => $type) {
@@ -156,9 +165,131 @@ function validate_member_number($member_number, $member_id = NULL) {/*{{{*/
     return false;
 }/*}}}*/
 
+function action_export_ibanhin() {/*{{{*/
+    $members = db_get_members();
+    if (empty($members)) return;
+    js_modal_windows();
+    js_get_master_key();
+?>
+    <noscript>
+        <p class="error">
+            <strong>Achtung:</strong> Ohne JavaScript geht hier gar nichts. Bitte aktivieren!
+        </p>
+    </noscript>
+
+    <h2>IBAN-hin</h2>
+<input class="submit" type="button" name="btn_ibanhin" value="IBAN-hin erzeugen" onclick="ask_for_password()" id="btn_export_iban_hin"/>
+<br/>
+<br/>
+
+
+<div id="password_popup" class="modal_window">
+    <fieldset>
+        <legend>Passwortabfrage</legend>
+        <?=html_password_field('Passwort', 'password')?>
+        <input class="submit" type="button" name="btn_export_ibanhin" value="IBAN-hin erzeugen" onclick="export_ibanhin()"/>
+        <input class="submit" type="button" name="btn_cancel" value="Abbrechen" onclick="cancel_ask_for_password()"/>
+    </fieldset>
+</div>
+<div id="ibanhin_popup" class="modal_window">
+    <fieldset>
+        <legend>IBAN-hin</legend>
+            <textarea id="ibanhin_content" style="width: 100%" rows="30" readonly="readonly"></textarea>
+            <input class="submit" type="button" name="btn_ibanhin_close" value="Schließen" onclick="ibanhin_close()"/>
+    </fieldset>
+</div>
+
+<script type="text/javascript">
+
+  var members = new Array(<?=join(",\n", array_map('json_encode', $members)) ?>);
+
+
+
+$(document).ready(function(){  
+    $('#password').keypress( function(event) { if (event.keyCode == '13') { export_ibanhin(); } });
+    $('#btn_ibanhin').focus();
+});  
+
+
+function ask_for_password() {/*{{{*/
+    modal_window_show($("#password_popup"));
+    $("#password").focus();
+    return;
+}/*}}}*/
+
+function cancel_ask_for_password() {/*{{{*/
+    modal_window_hide();
+    $("#btn_ibanhin").focus();
+    return;
+}/*}}}*/
+
+
+function export_ibanhin() {/*{{{*/
+    
+    var ibanhin_content = "";
+
+    var password = $('#password').val();
+    $("#password").val('');
+
+    // We pass a closure so that get_master_key may defer execution
+    get_master_key(password, function(masterkey) {
+
+    modal_window_replace($("#ibanhin_popup"));
+
+    for (var i = 0; i < members.length; i++) {
+      if (members[i]['directdebit'] == "1") {
+       var accountnumber = members[i]['accountnumber'];
+       var bankcode      = members[i]['bankcode'];
+       if (accountnumber != "") accountnumber = Crypto.charenc.UTF8.bytesToString(Crypto.AES.decrypt(accountnumber, masterkey));
+       if (bankcode      != "") bankcode      = Crypto.charenc.UTF8.bytesToString(Crypto.AES.decrypt(bankcode,      masterkey));
+        if (accountnumber != "" && bankcode != "") {
+          /* ZKA-Spezifikation-IBAN-hin-IBAN-rueck
+             01. Satzart
+             02. Anwender
+             03. Internes Kennzeichen des Anwenders
+             04. BIC-ALT
+             IBAN-ALT
+             05. LK/PZ-ALT
+             06. BLZ-ALT
+             07. KTONR-ALT
+             IBAN-NEU
+             08. LK/PZ-NEU
+             09. BLZ-NEU
+             10. KTONR-NEU
+             11. UMST-KZ
+           */
+          ibanhin_content += '"DE";;"' + members[i]['number'] + '";;;"' + bankcode + '";"' + accountnumber + '";;;;;' + "\n";
+        }
+      }
+    }
+
+    $('#ibanhin_content').val(ibanhin_content);
+    $('#ibanhin_content').focus();
+    $('#ibanhin_content').select();
+    
+    });
+
+}/*}}}*/
+
+function ibanhin_close() {/*{{{*/
+    modal_window_hide();
+    $("#ibanhin_content").val('');
+}/*}}}*/
+
+
+</script>
+
+<?php
+
+}/*}}}*/
+
 
 function action_list_members() {/*{{{*/
     $members = db_get_members();
+    $count   = 0;
+    $regular = 0;
+    $patron = 0;
+    $honorary = 0;
     ?>
     <h2>Liste der Mitglieder</h2>
     <?php if (isset($members)) : ?>
@@ -170,7 +301,17 @@ function action_list_members() {/*{{{*/
                 <th>Nachname</th>
             </tr>
         <?php foreach ($members as $member) : ?>
-            <tr>
+            <?php $membership_info = fees_info_for_member($member['id'], time()); ?>
+           <?php 
+                if (!empty($membership_info['is_member'])) {
+                    switch($membership_info['member_type']) {
+                        case 'regular': $regular++; break;
+                        case 'honorary': $honorary++; break;
+                        case 'patron': $patron++; break;
+                    }
+                }
+           ?>
+            <tr <?php if (empty($membership_info['is_member'])) : ?>class="inactive"<?php endif ?>>
                 <td><a href="<?=html_escape(link_to('view_member', array('id'=> $member['id'])))?>"><?=html_escape($member['number'])?></a></td>
                 <td><?=html_escape($member['nickname'])?></td>
                 <td><?=html_escape($member['firstname'])?></td>
@@ -179,10 +320,22 @@ function action_list_members() {/*{{{*/
         <?php endforeach ?>
         </table>
     <?php else : ?>
-        <p>Bisher gibt's noch keine Mitglieder.</p>
-    <?php endif ?>
+        <p>Bisher gibt es noch keine Mitglieder.</p>
+    <?php
+        endif;
+        $count = $regular + $honorary + $patron;
+    ?>
+        <p>Derzeit gibt es <?=$count?> Vereinsmitglieder. Davon:
+            <ul>
+                <li><?=$regular?> ordentliche Mitglieder</li>
+                <li><?=$honorary?> Ehrenmitglieder</li>
+                <li style="font-weight: bold;"><?=$regular+$honorary?> stimmberechtigte Mitglieder</li>
+                <li><?=$patron?> Fördermitglieder</li>
+            </ul>
+        </p>
         <a href="<?=link_to('create_member')?>">Neues Mitglied</a>
         <a href="<?=link_to('export_email')?>">E-Mail-Adressen</a>
+        <a href="<?=link_to('export_members')?>">XLS Export</a>
     <?php
 }/*}}}*/
 
@@ -190,7 +343,6 @@ function action_view_member() {/*{{{*/
     if (!isset($_REQUEST['id'])) redirect(link_to());
     $member = db_get_member_with_id($_REQUEST['id']);
     if (!isset($member)) redirect(link_to());
-
     form_member($member, true);
     action_list_events($member);
     action_create_event($member);
@@ -201,8 +353,18 @@ function action_create_member() {/*{{{*/
     $log_messages = array();
     if (isset($_POST['submit'])) {
         unset($_POST['id']);
+        
         if (validate_member($_POST, $member, $log_messages)) {
             if (empty($member['number'])) $member['number'] = db_get_next_free_member_number();
+            
+            if (($member['ddmandatesigdate'] == 'TT.MM.JJJJ') ||
+                ($member['ddmandatesigdate'] == ''))
+            {
+              $member['ddmandatesigdate'] = '0000-00-00 00:00:00';
+            } else {
+              $member['ddmandatesigdate'] = db_unixtime2datetime(ui_date2unixtime($member['ddmandatesigdate']));
+            }
+            
             if ($member_id = db_create_member($member)) {
                 redirect(link_to('view_member', array('id' => $member_id)) . '#events');
             }
@@ -220,6 +382,15 @@ function action_change_member() {/*{{{*/
     if (isset($_POST['submit'])) {
         if (validate_member($_POST, $member, $log_messages)) {
             if (empty($member['number'])) $member['number'] = db_get_next_free_member_number();
+                        
+            if (($member['ddmandatesigdate'] == 'TT.MM.JJJJ') ||
+                ($member['ddmandatesigdate'] == ''))
+            {
+              $member['ddmandatesigdate'] = '0000-00-00 00:00:00';
+            } else {
+              $member['ddmandatesigdate'] = db_unixtime2datetime(ui_date2unixtime($member['ddmandatesigdate']));
+            }
+
             if (db_change_member($member)) {
                 redirect(link_to('view_member', array('id' => $member['id'])));
             }
@@ -236,7 +407,7 @@ function action_export_email() {/*{{{*/
     ?>
     <h2>E-Mail-Adressen</h2>
     <?php if (isset($members)) : ?>
-        <?php foreach ($members as $member) : ?><?php if (empty($member['email'])) continue; ?><?php if ($first) : $first = false; else :?>,<?php endif ?> <?=$member['email']?><?php endforeach ?>
+        <?php foreach ($members as $member) : ?><?php $membership_info = fees_info_for_member($member['id'], time()); ?><?php if (!empty($membership_info['is_member'])) : ?><?php if (empty($member['email'])) continue; ?><?php if ($first) : $first = false; else :?>,<?php endif ?> <?=$member['email']?><?php endif ?><?php endforeach ?>
     <?php else : ?>
         <p>Bisher gibt's noch keine Mitglieder.</p>
     <?php endif ?>
@@ -257,6 +428,8 @@ function html_member_list_box($label, $name, $selected, $readonly = false, $id =
 
 function form_member($member = array(), $readonly = false, $log_messages = array()) {/*{{{*/
     
+    global $DIRECTDEBIT_STATES;
+    
     $keys = db_get_crypto_keys();
     if (empty($keys)) {
 ?>
@@ -296,10 +469,9 @@ function form_member($member = array(), $readonly = false, $log_messages = array
         <fieldset id="bank_details">
             <legend>Bankverbindung</legend>
             <?=html_text_field('Kontoinhaber', 'accountholder', $member, true)?>
-            <?=html_text_field('Kontonummer', 'accountnumber', $member, true)?>
-            <?=html_text_field('Bankleitzahl', 'bankcode', $member, true)?>
+            <?=html_text_field('Kontonummer/IBAN', 'accountnumber', $member, true)?>
+            <?=html_text_field('Bankleitzahl/BIC', 'bankcode', $member, true)?>
             <?=html_text_field('Name der Bank', 'bankname', $member, true)?>
-            <?=html_checkbox('Lastschrifteinzug', 'directdebit', $member, 1, $readonly)?>
             <?php if (!isset($member['id'])) : ?>
                 <input class="submit" type="button" name="btn_bank_details" value="Bankverbindung erfassen" onclick="bank_details_ask_pass()" id="btn_bank_details"/>
             <?php elseif ($readonly) : ?>
@@ -307,6 +479,18 @@ function form_member($member = array(), $readonly = false, $log_messages = array
             <?php else : ?>
                 <input class="submit" type="button" name="btn_bank_details" value="Bankverbindung &auml;ndern" onclick="bank_details_ask_pass()" id="btn_bank_details"/>
             <?php endif ?>
+            <?=html_list_box('Beitragseinzug', 'directdebit', $DIRECTDEBIT_STATES, $member, $readonly)?>
+            <?php
+              if ((!isset($member['ddmandatesigdate'])) ||
+                  ($member['ddmandatesigdate'] == '') ||
+                  ($member['ddmandatesigdate'] == '0000-00-00 00:00:00'))
+              {
+                $member['ddmandatesigdate'] = ($readonly) ? '' : html_escape('TT.MM.JJJJ');
+              } else {              
+                $member['ddmandatesigdate'] = html_escape(format_date(db_date2unixtime($member['ddmandatesigdate'])));
+              }
+            ?>
+            <?=html_text_field('Mandatsunterschrift', 'ddmandatesigdate', $member, $readonly)?>
         </fieldset>
         <fieldset style="clear: left;">
             <legend>Anschrift</legend>
@@ -369,8 +553,8 @@ function form_member($member = array(), $readonly = false, $log_messages = array
         <fieldset>
             <legend>Bankverbindung</legend>
             <?=html_text_field('Kontoinhaber', 'tmp_accountholder', $member, $readonly)?>
-            <?=html_text_field('Kontonummer', 'tmp_accountnumber', $member, $readonly)?>
-            <?=html_text_field('Bankleitzahl', 'tmp_bankcode', $member, $readonly)?>
+            <?=html_text_field('Kontonummer/IBAN', 'tmp_accountnumber', $member, $readonly)?>
+            <?=html_text_field('Bankleitzahl/BIC', 'tmp_bankcode', $member, $readonly)?>
             <?=html_text_field('Name der Bank', 'tmp_bankname', $member, $readonly)?>
             <?php if (!isset($member['id'])) : ?>
                 <input class="submit" type="button" name="btn_bank_details_save" value="&Uuml;bernehmen" onclick="bank_details_save()"/>
@@ -521,7 +705,9 @@ function bank_details_save() {/*{{{*/
     $("#tmp_accountnumber").val('');
     $("#tmp_bankcode").val('');
     $("#tmp_bankname").val('');
-
+    
+    accountnumber = accountnumber.replace(/\s+/g, '');
+    bankcode = bankcode.replace(/\s+/g, '');
     // 4.2 Encrypt/Decrypt data using AES with masterkey
     if (accountholder != "") { $('#accountholder').val(Crypto.AES.encrypt(Crypto.charenc.UTF8.stringToBytes(accountholder), the_masterkey)); } else { $('#accountholder').val(''); }
     if (accountnumber != "") { $('#accountnumber').val(Crypto.AES.encrypt(Crypto.charenc.UTF8.stringToBytes(accountnumber), the_masterkey)); } else { $('#accountnumber').val(''); }