Fixed sql stuff...
authorIgor Scheller <igor.scheller@igorshp.de>
Fri, 1 Dec 2017 13:10:02 +0000 (14:10 +0100)
committerIgor Scheller <igor.scheller@igorshp.de>
Fri, 1 Dec 2017 13:26:08 +0000 (14:26 +0100)
Removed constants & table prefix, moved installation files to install.sql

13 files changed:
UPGRADE.txt [deleted file]
include/cryptography.php
include/database.php
include/earnings.php
include/events.php
include/expenses.php
include/finances.php
include/includes.php
include/installation.php [deleted file]
include/members.php
include/payments.php
install.sql [new file with mode: 0644]
update.sql [new file with mode: 0644]

diff --git a/UPGRADE.txt b/UPGRADE.txt
deleted file mode 100644 (file)
index 91c3b3c..0000000
+++ /dev/null
@@ -1,4 +0,0 @@
-
-// Upgrade existing database:
-// ALTER TABLE `memberdb_members` ADD `ddmandatesigdate` datetime NOT NULL AFTER `directdebit`;
-// UPDATE memberdb_members, memberdb_events SET memberdb_members.ddmandatesigdate = memberdb_events.event_date WHERE memberdb_members.id = memberdb_events.member_id AND memberdb_members.ddmandatesigdate = '0000-00-00 00:00:00' AND memberdb_events.event_type = 'joined' AND memberdb_members.directdebit IN (1, 2, 3);
index 74ded63..484fb82 100644 (file)
@@ -52,30 +52,17 @@ PROBLEM: AES uses a random initialization vector and creates different output fo
 
 }}} */
 
-
-$SQL_CREATE_CRYPTO = sprintf('
-    CREATE TABLE IF NOT EXISTS `%1$s` (
-      `description` VARCHAR(255) NOT NULL,
-      `masterkey` BLOB NOT NULL,
-      `salt` BLOB NOT NULL,
-      `control` BLOB NOT NULL,
-      `created_at` DATETIME NOT NULL,
-      `modified_at` DATETIME NOT NULL
-    ) ENGINE=InnoDB DEFAULT CHARSET=%2$s
-    ', DB_TABLE_CRYPTO, 'utf8');
-
 /* DB functions {{{ */
 function db_get_crypto_keys()
 {
-    $sql = 'SELECT * FROM %1$s';
-    return db_select_multi(sprintf($sql, DB_TABLE_CRYPTO));
+    return db_select_multi('SELECT * FROM `cryptography`');
 }
 
 function db_add_crypto_key($key)
 {
     $key['created_at'] = db_unixtime2datetime(time());
     $key['modified_at'] = db_unixtime2datetime(time());
-    return (db_insert_single(DB_TABLE_CRYPTO, $key));
+    return (db_insert_single('cryptography', $key));
 }
 
 /* }}} */
index 7c9fe80..d9cda8d 100644 (file)
@@ -4,11 +4,6 @@
 
 use MemberDB\Config\Config;
 
-define('DB_TABLE_CRYPTO', 'memberdb_crypto');
-define('DB_TABLE_EVENTS', 'memberdb_events');
-define('DB_TABLE_MEMBERS', 'memberdb_members');
-define('DB_TABLE_EARNINGS', 'memberdb_earnings');
-define('DB_TABLE_EXPENSES', 'memberdb_expenses');
 /* }}} */
 
 
index 9744751..8de3d67 100644 (file)
@@ -2,24 +2,6 @@
 
 use MemberDB\Config\Config;
 
-$SQL_CREATE_EARNINGS = sprintf('
-    CREATE TABLE IF NOT EXISTS `%1$s` (
-      `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
-      `date` DATE NOT NULL,
-      `type` ENUM(\'donation\', \'fee\', \'old_fee\', \'interest\', \'account_transfer\', \'room\', \'catering\') NULL,
-      `status` ENUM(\'open\',\'paid\') NOT NULL,
-      `account` ENUM(\'bank\',\'cash\') NOT NULL,
-      `value` DECIMAL(10,2) NULL,
-      `member_id` INT(10) UNSIGNED NOT NULL,
-      `description` TEXT NOT NULL,
-      `created_at` DATETIME NOT NULL,
-      `modified_at` DATETIME NOT NULL,
-      PRIMARY KEY  (`id`),
-      INDEX `idx_member_id` (`member_id`),
-      INDEX `idx_date` (`date`)
-    ) ENGINE=InnoDB DEFAULT CHARSET=%2$s
-    ', DB_TABLE_EARNINGS, 'utf8');
-
 $EARNING_TYPES = array(
     'fee'              => 'Mitgliedsbeitrag',
     'donation'         => 'Spende',
@@ -35,8 +17,8 @@ $EARNING_TYPES = array(
 
 function db_get_earning_with_id($earning_id)
 {
-    $sql = 'SELECT * FROM %1$s WHERE id=\'%2$d\' LIMIT 1';
-    return db_select_single(sprintf($sql, DB_TABLE_EARNINGS, db_escape($earning_id)));
+    $sql = 'SELECT * FROM `earnings` WHERE id=\'%d\' LIMIT 1';
+    return db_select_single(sprintf($sql, db_escape($earning_id)));
 }
 
 function db_create_earning($earning)
@@ -46,7 +28,7 @@ function db_create_earning($earning)
     $earning['value'] = ui_money2float($earning['value']);
     $earning['created_at'] = db_unixtime2datetime(time());
     $earning['modified_at'] = db_unixtime2datetime(time());
-    return (db_insert_single(DB_TABLE_EARNINGS, $earning));
+    return (db_insert_single('earnings', $earning));
 }
 
 function db_change_earning($earning)
@@ -56,12 +38,12 @@ function db_change_earning($earning)
     $earning['date'] = db_unixtime2datetime(ui_date2unixtime($earning['date']));
     $earning['value'] = ui_money2float($earning['value']);
     $earning['modified_at'] = db_unixtime2datetime(time());
-    return db_update_single(DB_TABLE_EARNINGS, $earning, sprintf('id=\'%1$d\'', db_escape($id)));
+    return db_update_single('earnings', $earning, sprintf('id=\'%d\'', db_escape($id)));
 }
 
 function db_delete_earning($earning)
 {
-    return db_delete_single(DB_TABLE_EARNINGS, sprintf('id=\'%1$d\'', db_escape($earning['id'])));
+    return db_delete_single('earnings', sprintf('id=\'%d\'', db_escape($earning['id'])));
 }
 
 /* }}} */
@@ -243,8 +225,8 @@ function action_create_earnings()
     foreach ($_POST['earnings'] as $post_data) {
         $member['modified_at'] = db_unixtime2datetime(time());
         $member['directdebit'] = 3; // RCUR
-        db_update_single(DB_TABLE_MEMBERS, $member,
-            sprintf('directdebit=\'2\' AND id=\'%1$d\'', db_escape($post_data['member_id'])));
+        db_update_single('members', $member,
+            sprintf('directdebit=\'2\' AND id=\'%d\'', db_escape($post_data['member_id'])));
     }
 
     redirect(link_to('finances'));
index 0122345..6da4c64 100644 (file)
@@ -1,21 +1,5 @@
 <?php
 
-$SQL_CREATE_EVENTS = sprintf('
-    CREATE TABLE IF NOT EXISTS `%1$s` (
-      `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
-      `event_date` DATE NOT NULL,
-      `event_type` ENUM(\'joined\',\'changed\',\'leaved\') NOT NULL,
-      `member_id` INT(10) UNSIGNED NOT NULL,
-      `member_type` ENUM(\'regular\',\'patron\',\'honorary\') NULL,
-      `fee` DECIMAL(10,2) NULL,
-      `payment_interval` ENUM(\'monthly\', \'quarterly\', \'halfyearly\', \'yearly\') NULL,
-      `created_at` DATETIME NOT NULL,
-      `modified_at` DATETIME NOT NULL,
-      PRIMARY KEY  (`id`),
-      UNIQUE KEY `member_id_event_date` (`member_id`, `event_date`)
-    ) ENGINE=InnoDB DEFAULT CHARSET=%2$s
-    ', DB_TABLE_EVENTS, 'utf8');
-
 $EVENT_TYPES = array(
     'joined'  => 'Eintritt',
     'changed' => 'Änderung',
@@ -44,7 +28,7 @@ function db_create_event($event)
     $event['fee'] = ui_money2float($event['fee']);
     $event['created_at'] = db_unixtime2datetime(time());
     $event['modified_at'] = db_unixtime2datetime(time());
-    return (db_insert_single(DB_TABLE_EVENTS, $event));
+    return (db_insert_single('events', $event));
 }
 
 function db_change_event($event)
@@ -65,12 +49,12 @@ function db_change_event($event)
     if (!isset($event['payment_interval'])) {
         $special .= ',`payment_interval`=NULL';
     }
-    return db_update_single(DB_TABLE_EVENTS, $event, sprintf('id=\'%1$d\'', db_escape($id)), $special);
+    return db_update_single('events', $event, sprintf('id=\'%d\'', db_escape($id)), $special);
 }
 
 function db_delete_event($event)
 {
-    return db_delete_single(DB_TABLE_EVENTS, sprintf('id=\'%1$d\'', db_escape($event['id'])));
+    return db_delete_single('events', sprintf('id=\'%d\'', db_escape($event['id'])));
 }
 
 function db_get_events_for_member($member_id, $unixtime_start = null, $unixtime_end = null)
@@ -85,20 +69,20 @@ function db_get_events_for_member($member_id, $unixtime_start = null, $unixtime_
         $conditions[] = sprintf('event_date<=\'%s\'', db_unixtime2date($unixtime_end));
     }
 
-    $sql = 'SELECT * FROM %1$s WHERE %2$s ORDER BY event_date ASC';
-    return db_select_multi(sprintf($sql, DB_TABLE_EVENTS, join(' AND ', $conditions)));
+    $sql = 'SELECT * FROM `events` WHERE %s ORDER BY event_date ASC';
+    return db_select_multi(sprintf($sql, join(' AND ', $conditions)));
 }
 
 function db_get_event_with_member_and_date($member_id, $event_date)
 {
-    $sql = 'SELECT * FROM %1$s WHERE member_id=\'%2$d\' AND event_date=\'%3$s\' LIMIT 1';
-    return db_select_single(sprintf($sql, DB_TABLE_EVENTS, db_escape($member_id), db_escape($event_date)));
+    $sql = 'SELECT * FROM `events` WHERE member_id=\'%d\' AND event_date=\'%s\' LIMIT 1';
+    return db_select_single(sprintf($sql, db_escape($member_id), db_escape($event_date)));
 }
 
 function db_get_event_with_id($event_id)
 {
-    $sql = 'SELECT * FROM %1$s WHERE id=\'%2$d\' LIMIT 1';
-    return db_select_single(sprintf($sql, DB_TABLE_EVENTS, db_escape($event_id)));
+    $sql = 'SELECT * FROM `events` WHERE id=\'%d\' LIMIT 1';
+    return db_select_single(sprintf($sql, db_escape($event_id)));
 }
 
 /* }}} */
index 5aed50f..3df08bf 100644 (file)
@@ -1,23 +1,5 @@
 <?php
 
-$SQL_CREATE_EXPENSES = sprintf('
-    CREATE TABLE IF NOT EXISTS `%1$s` (
-      `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
-      `date` DATE NOT NULL,
-      `type` ENUM(\'invoice\', \'gains_tax\', \'bankcharges\', \'account_transfer\', \'member_fee\', \'catering\') NULL,
-      `status` ENUM(\'open\',\'paid\') NOT NULL,
-      `account` ENUM(\'bank\',\'cash\') NOT NULL,
-      `value` DECIMAL(10,2) NULL,
-      `member_id` INT(10) UNSIGNED NOT NULL,
-      `description` TEXT NOT NULL,
-      `created_at` DATETIME NOT NULL,
-      `modified_at` DATETIME NOT NULL,
-      PRIMARY KEY  (`id`),
-      INDEX `idx_member_id` (`member_id`),
-      INDEX `idx_date` (`date`)
-    ) ENGINE=InnoDB DEFAULT CHARSET=%2$s
-    ', DB_TABLE_EXPENSES, 'utf8');
-
 $EXPENSE_TYPES = array(
     'invoice'          => 'Rechnung',
     'gains_tax'        => 'Kapitalertragssteuer',
@@ -32,8 +14,8 @@ $EXPENSE_TYPES = array(
 
 function db_get_expense_with_id($expense_id)
 {
-    $sql = 'SELECT * FROM %1$s WHERE id=\'%2$d\' LIMIT 1';
-    return db_select_single(sprintf($sql, DB_TABLE_EXPENSES, db_escape($expense_id)));
+    $sql = 'SELECT * FROM `expenses` WHERE id=\'%d\' LIMIT 1';
+    return db_select_single(sprintf($sql, db_escape($expense_id)));
 }
 
 function db_create_expense($expense)
@@ -43,7 +25,7 @@ function db_create_expense($expense)
     $expense['value'] = ui_money2float($expense['value']);
     $expense['created_at'] = db_unixtime2datetime(time());
     $expense['modified_at'] = db_unixtime2datetime(time());
-    return (db_insert_single(DB_TABLE_EXPENSES, $expense));
+    return (db_insert_single('expenses', $expense));
 }
 
 function db_change_expense($expense)
@@ -53,12 +35,12 @@ function db_change_expense($expense)
     $expense['date'] = db_unixtime2datetime(ui_date2unixtime($expense['date']));
     $expense['value'] = ui_money2float($expense['value']);
     $expense['modified_at'] = db_unixtime2datetime(time());
-    return db_update_single(DB_TABLE_EXPENSES, $expense, sprintf('id=\'%1$d\'', db_escape($id)));
+    return db_update_single('expenses', $expense, sprintf('id=\'%d\'', db_escape($id)));
 }
 
 function db_delete_expense($expense)
 {
-    return db_delete_single(DB_TABLE_EXPENSES, sprintf('id=\'%1$d\'', db_escape($expense['id'])));
+    return db_delete_single('expenses', sprintf('id=\'%d\'', db_escape($expense['id'])));
 }
 
 /* }}} */
index bd8a677..12fec88 100644 (file)
@@ -5,12 +5,8 @@ use MemberDB\Config\Config;
 
 function finance_get_total_paid_fees()
 {
-    $paid = db_select_single(sprintf('SELECT SUM(value) AS paid_fees FROM %1$s WHERE type IN(\'old_fee\', \'fee\') AND `status`=\'paid\'',
-        DB_TABLE_EARNINGS
-    ));
-    $refunded = db_select_single(sprintf('SELECT SUM(value) AS refunded_fees FROM %1$s WHERE type IN(\'member_fee\') AND `status`=\'paid\'',
-        DB_TABLE_EXPENSES
-    ));
+    $paid = db_select_single('SELECT SUM(value) AS paid_fees FROM `earnings` WHERE type IN(\'old_fee\', \'fee\') AND `status`=\'paid\'');
+    $refunded = db_select_single('SELECT SUM(value) AS refunded_fees FROM `expenses` WHERE type IN(\'member_fee\') AND `status`=\'paid\'');
 
     return bcsub($paid['paid_fees'], $refunded['refunded_fees']);
 }
@@ -21,8 +17,8 @@ function finance_get_paid_fees_for_member($member_id, $old_fees = false)
     // $old_fees: Beiträge die nicht durch Lastschrift eingezogen werden.
     //            Falls zu viel bezahlt, müssen diese auf die per Lastschrift
     //            eingezogenen Beiträge angerechnet werden.
-    $row = db_select_single(sprintf('SELECT SUM(value) AS paid_fees FROM %1$s WHERE member_id=\'%2$d\' AND type=\'%3$s\' AND `status`=\'paid\'',
-        DB_TABLE_EARNINGS,
+    $row = db_select_single(sprintf(
+        'SELECT SUM(value) AS paid_fees FROM `earnings` WHERE member_id=\'%d\' AND type=\'%s\' AND `status`=\'paid\'',
         db_escape($member_id),
         $old_fees ? 'old_fee' : 'fee'
     ));
@@ -42,8 +38,8 @@ function finance_get_paid_fees_for_member($member_id, $old_fees = false)
     }
 
 
-    $refunded = db_select_single(sprintf('SELECT SUM(value) AS refunded_fees FROM %1$s WHERE member_id=\'%2$d\' AND type IN(\'member_fee\') AND `status`=\'paid\'',
-        DB_TABLE_EXPENSES,
+    $refunded = db_select_single(sprintf(
+        'SELECT SUM(value) AS refunded_fees FROM `expenses` WHERE member_id=\'%d\' AND type IN(\'member_fee\') AND `status`=\'paid\'',
         db_escape($member_id)
     ));
     return bcsub($paid_fees, $refunded['refunded_fees']);
@@ -52,11 +48,10 @@ function finance_get_paid_fees_for_member($member_id, $old_fees = false)
 function finance_list_paid_fees_for_member($member_id)
 {
     return db_select_multi(sprintf('
-               (SELECT id, date, type, status, account, value, member_id, description, created_at, modified_at FROM %1$s WHERE member_id=\'%3$d\' AND type IN(\'old_fee\', \'fee\')) UNION
-               (SELECT id, date, type, status, account, value*-1, member_id, description, created_at, modified_at FROM %2$s WHERE member_id=\'%3$d\' AND type IN(\'member_fee\'))
+               (SELECT id, date, type, status, account, value, member_id, description, created_at, modified_at FROM `earnings` WHERE member_id=\'%1$d\' AND type IN(\'old_fee\', \'fee\'))
+               UNION
+               (SELECT id, date, type, status, account, value*-1, member_id, description, created_at, modified_at FROM `expenses` WHERE member_id=\'%1$d\' AND type IN(\'member_fee\'))
                ORDER BY `date` DESC',
-        DB_TABLE_EARNINGS,
-        DB_TABLE_EXPENSES,
         db_escape($member_id)
     ));
 }
index e2d59bf..ae4c894 100644 (file)
@@ -2,7 +2,6 @@
 
 require_once(__DIR__ . '/database.php');
 require_once(__DIR__ . '/misc.php');
-require_once(__DIR__ . '/installation.php');
 require_once(__DIR__ . '/html.php');
 
 require_once(__DIR__ . '/finances.php');
diff --git a/include/installation.php b/include/installation.php
deleted file mode 100644 (file)
index bb849b5..0000000
+++ /dev/null
@@ -1,29 +0,0 @@
-<?php
-
-function install_database()
-{
-    if (!mysql_query($GLOBALS['SQL_CREATE_MEMBERS'])) {
-        echo db_error();
-    }
-    if (!mysql_query($GLOBALS['SQL_CREATE_EVENTS'])) {
-        echo db_error();
-    }
-    if (!mysql_query($GLOBALS['SQL_CREATE_CRYPTO'])) {
-        echo db_error();
-    }
-    if (!mysql_query($GLOBALS['SQL_CREATE_EARNINGS'])) {
-        echo db_error();
-    }
-    if (!mysql_query($GLOBALS['SQL_CREATE_EXPENSES'])) {
-        echo db_error();
-    }
-}
-
-function install_clear_database()
-{
-    mysql_query('DROP TABLE ' . DB_TABLE_MEMBERS);
-    mysql_query('DROP TABLE ' . DB_TABLE_EVENTS);
-    mysql_query('DROP TABLE ' . DB_TABLE_CRYPTO);
-    mysql_query('DROP TABLE ' . DB_TABLE_EARNINGS);
-    mysql_query('DROP TABLE ' . DB_TABLE_EXPENSES);
-}
index af9d571..b965c37 100644 (file)
@@ -1,41 +1,5 @@
 <?php
 
-$SQL_CREATE_MEMBERS = sprintf('
-    CREATE TABLE IF NOT EXISTS `%1$s` (
-      `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
-      `number` INT(10) UNSIGNED NOT NULL,
-      `firstname` VARCHAR(255) NOT NULL,
-      `lastname` VARCHAR(255) NOT NULL,
-      `nickname` VARCHAR(255) NOT NULL,
-      `accountholder` BLOB NOT NULL,
-      `accountnumber` BLOB NOT NULL,
-      `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,
-      `city` VARCHAR(255) NOT NULL,
-      `country` VARCHAR(255) NOT NULL,
-      `email` VARCHAR(255) NOT NULL,
-      `phone` VARCHAR(255) NOT NULL,
-      `fax` VARCHAR(255) NOT NULL,
-      `mobile` VARCHAR(255) NOT NULL,
-      `jabber` VARCHAR(255) NOT NULL,
-      `icq` VARCHAR(255) NOT NULL,
-      `msn` VARCHAR(255) NOT NULL,
-      `aim` VARCHAR(255) NOT NULL,
-      `skype` VARCHAR(255) NOT NULL,
-      `url` VARCHAR(255) NOT NULL,
-      `twitter` VARCHAR(255) NOT NULL,
-      `created_at` DATETIME NOT NULL,
-      `modified_at` DATETIME NOT NULL,
-      PRIMARY KEY  (`id`),
-      UNIQUE KEY `number` (`number`)
-    ) ENGINE=InnoDB DEFAULT CHARSET=%2$s
-    ', DB_TABLE_MEMBERS, 'utf8');
-
 $DIRECTDEBIT_STATES = array(
     '' => '- (Barzahler)',
     1  => 'DTAUS',
@@ -50,7 +14,7 @@ function db_create_member($member)
     unset($member['id']);
     $member['created_at'] = db_unixtime2datetime(time());
     $member['modified_at'] = db_unixtime2datetime(time());
-    if (!db_insert_single(DB_TABLE_MEMBERS, $member)) {
+    if (!db_insert_single('members', $member)) {
         return false;
     }
     return db_insert_id();
@@ -61,24 +25,23 @@ function db_change_member($member)
     $id = $member['id'];
     unset($member['id']);
     $member['modified_at'] = db_unixtime2datetime(time());
-    return db_update_single(DB_TABLE_MEMBERS, $member, sprintf('id=\'%1$d\'', db_escape($id)));
+    return db_update_single('members', $member, sprintf('id=\'%d\'', db_escape($id)));
 }
 
 function db_get_members()
 {
-    $sql = 'SELECT * FROM %1$s ORDER BY NUMBER ASC';
-    return db_select_multi(sprintf($sql, DB_TABLE_MEMBERS));
+    return db_select_multi('SELECT * FROM `members` ORDER BY NUMBER ASC');
 }
 
 function db_get_member_with_id($member_id)
 {
-    $sql = 'SELECT * FROM %1$s WHERE id=\'%2$d\' LIMIT 1';
-    return db_select_single(sprintf($sql, DB_TABLE_MEMBERS, db_escape($member_id)));
+    $sql = 'SELECT * FROM `m̀embers` WHERE id=\'%d\' LIMIT 1';
+    return db_select_single(sprintf($sql, db_escape($member_id)));
 }
 
 function db_get_next_free_member_number()
 {/*{{{*/
-    $row = db_select_single(sprintf('SELECT MAX(number) AS number FROM %1$s', DB_TABLE_MEMBERS));
+    $row = db_select_single('SELECT MAX(number) AS number FROM `members`');
     if (!isset($row)) {
         return 2;
     }
@@ -103,8 +66,8 @@ function db_get_next_free_member_number()
 
 function db_get_member_with_number($member_number)
 {
-    $sql = 'SELECT * FROM %1$s WHERE NUMBER=\'%2$d\' LIMIT 1';
-    return db_select_single(sprintf($sql, DB_TABLE_MEMBERS, db_escape($member_number)));
+    $sql = 'SELECT * FROM `members` WHERE NUMBER=\'%d\' LIMIT 1';
+    return db_select_single(sprintf($sql, db_escape($member_number)));
 }
 
 /* }}} */
index 8864aeb..7fb2425 100644 (file)
@@ -12,12 +12,21 @@ function db_get_payments($offset, $count, $where)
 {
     $limit = '';
     if (isset($offset) && isset($count)) {
-        $limit = sprintf(' LIMIT %1$d,%2$d', (int)$offset, (int)$count);
+        $limit = sprintf(' LIMIT %d,%d', (int)$offset, (int)$count);
     } elseif (isset($offset)) {
-        $limit = sprintf(' LIMIT %1$d', (int)$count);
+        $limit = sprintf(' LIMIT %d', (int)$count);
     }
-    $sql = 'SELECT t.*, %4$s.nickname AS nickname FROM ((SELECT \'earning\' AS ptype, %1$s.* FROM %1$s %3$s) UNION (SELECT \'expense\', %2$s.* FROM %2$s %3$s)) AS t LEFT JOIN %4$s ON t.member_id=%4$s.id ORDER BY `date` DESC, t.id DESC' . $limit;
-    return db_select_multi(sprintf($sql, DB_TABLE_EARNINGS, DB_TABLE_EXPENSES, $where, DB_TABLE_MEMBERS));
+    $sql = '
+        SELECT t.*, members.nickname AS nickname
+        FROM (
+            (SELECT \'earning\' AS ptype, `earnings`.* FROM `earnings` `earnings`)
+            UNION
+            (SELECT \'expense\', expenses.* FROM `expenses` `earnings`)
+        ) AS t
+        LEFT JOIN `members` ON t.member_id=members.id
+        ORDER BY `date` DESC, t.id DESC
+    ' . $limit;
+    return db_select_multi(sprintf($sql, $where));
 }
 
 function db_get_finished_payments($offset, $count)
@@ -34,12 +43,12 @@ function db_get_finished_payments($offset, $count)
         $sql = 'SELECT SUM(IF(`account`=\'bank\', `value`, 0)) AS `bank`, SUM(IF(`account`=\'cash\', `value`, 0)) AS `cash`, SUM(`value`) AS `total` 
                 FROM (
                     SELECT `value`, `account` 
-                    FROM (( SELECT `value`,                 `id`, `date`, `account` FROM %1$s %3$s) 
-                    UNION ( SELECT `value` * -1 AS `value`, `id`, `date`, `account` FROM %2$s %3$s)
+                    FROM (( SELECT `value`,                 `id`, `date`, `account` FROM `earnings` %1$s) 
+                    UNION ( SELECT `value` * -1 AS `value`, `id`, `date`, `account` FROM `expenses` %1$s)
                     ) AS t
                     ORDER BY `date` DESC, t.id DESC' . $limit . '
                 ) AS u';
-        $row = db_select_single(sprintf($sql, DB_TABLE_EARNINGS, DB_TABLE_EXPENSES, $where));
+        $row = db_select_single(sprintf($sql, $where));
         $bank = $row['bank'];
         $cash = $row['cash'];
         $total = $row['total'];
@@ -70,8 +79,8 @@ function db_get_finished_payments($offset, $count)
 function db_count_finished_payments()
 {
     $where = ' WHERE status=\'paid\'';
-    $sql = 'SELECT count(*) AS `count` FROM ((SELECT * FROM %1$s %3$s) UNION (SELECT * FROM %2$s %3$s)) AS t';
-    $row = db_select_single(sprintf($sql, DB_TABLE_EARNINGS, DB_TABLE_EXPENSES, $where));
+    $sql = 'SELECT count(*) AS `count` FROM ((SELECT * FROM `earnings` %1$s) UNION (SELECT * FROM `expenses` %1$s)) AS t';
+    $row = db_select_single(sprintf($sql, $where));
     return $row['count'];
 }
 
diff --git a/install.sql b/install.sql
new file mode 100644 (file)
index 0000000..10ed465
--- /dev/null
@@ -0,0 +1,98 @@
+CREATE TABLE IF NOT EXISTS `members` (
+  `id`               INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
+  `number`           INT(10) UNSIGNED NOT NULL,
+  `firstname`        VARCHAR(255)     NOT NULL,
+  `lastname`         VARCHAR(255)     NOT NULL,
+  `nickname`         VARCHAR(255)     NOT NULL,
+  `accountholder`    BLOB             NOT NULL,
+  `accountnumber`    BLOB             NOT NULL,
+  `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,
+  `city`             VARCHAR(255)     NOT NULL,
+  `country`          VARCHAR(255)     NOT NULL,
+  `email`            VARCHAR(255)     NOT NULL,
+  `phone`            VARCHAR(255)     NOT NULL,
+  `fax`              VARCHAR(255)     NOT NULL,
+  `mobile`           VARCHAR(255)     NOT NULL,
+  `jabber`           VARCHAR(255)     NOT NULL,
+  `icq`              VARCHAR(255)     NOT NULL,
+  `msn`              VARCHAR(255)     NOT NULL,
+  `aim`              VARCHAR(255)     NOT NULL,
+  `skype`            VARCHAR(255)     NOT NULL,
+  `url`              VARCHAR(255)     NOT NULL,
+  `twitter`          VARCHAR(255)     NOT NULL,
+  `created_at`       DATETIME         NOT NULL,
+  `modified_at`      DATETIME         NOT NULL,
+  PRIMARY KEY (`id`),
+  UNIQUE KEY `number` (`number`)
+)
+  ENGINE = InnoDB
+  DEFAULT CHARSET = utf8;
+
+CREATE TABLE IF NOT EXISTS `events` (
+  `id`               INT(10) UNSIGNED                                      NOT NULL AUTO_INCREMENT,
+  `event_date`       DATE                                                  NOT NULL,
+  `event_type`       ENUM ('joined', 'changed', 'leaved')                  NOT NULL,
+  `member_id`        INT(10) UNSIGNED                                      NOT NULL,
+  `member_type`      ENUM ('regular', 'patron', 'honorary')                NULL,
+  `fee`              DECIMAL(10, 2)                                        NULL,
+  `payment_interval` ENUM ('monthly', 'quarterly', 'halfyearly', 'yearly') NULL,
+  `created_at`       DATETIME                                              NOT NULL,
+  `modified_at`      DATETIME                                              NOT NULL,
+  PRIMARY KEY (`id`),
+  UNIQUE KEY `member_id_event_date` (`member_id`, `event_date`)
+)
+  ENGINE = InnoDB
+  DEFAULT CHARSET = utf8;
+
+CREATE TABLE IF NOT EXISTS `cryptography` (
+  `description` VARCHAR(255) NOT NULL,
+  `masterkey`   BLOB         NOT NULL,
+  `salt`        BLOB         NOT NULL,
+  `control`     BLOB         NOT NULL,
+  `created_at`  DATETIME     NOT NULL,
+  `modified_at` DATETIME     NOT NULL
+)
+  ENGINE = InnoDB
+  DEFAULT CHARSET = utf8;
+
+CREATE TABLE IF NOT EXISTS `earnings` (
+  `id`          INT(10) UNSIGNED                                                                        NOT NULL AUTO_INCREMENT,
+  `date`        DATE                                                                                    NOT NULL,
+  `type`        ENUM ('donation', 'fee', 'old_fee', 'interest', 'account_transfer', 'room', 'catering') NULL,
+  `status`      ENUM ('open', 'paid')                                                                   NOT NULL,
+  `account`     ENUM ('bank', 'cash')                                                                   NOT NULL,
+  `value`       DECIMAL(10, 2)                                                                          NULL,
+  `member_id`   INT(10) UNSIGNED                                                                        NOT NULL,
+  `description` TEXT                                                                                    NOT NULL,
+  `created_at`  DATETIME                                                                                NOT NULL,
+  `modified_at` DATETIME                                                                                NOT NULL,
+  PRIMARY KEY (`id`),
+  INDEX `idx_member_id` (`member_id`),
+  INDEX `idx_date` (`date`)
+)
+  ENGINE = InnoDB
+  DEFAULT CHARSET = utf8;
+
+CREATE TABLE IF NOT EXISTS `expenses` (
+  `id`          INT(10) UNSIGNED                                                                           NOT NULL AUTO_INCREMENT,
+  `date`        DATE                                                                                       NOT NULL,
+  `type`        ENUM ('invoice', 'gains_tax', 'bankcharges', 'account_transfer', 'member_fee', 'catering') NULL,
+  `status`      ENUM ('open', 'paid')                                                                      NOT NULL,
+  `account`     ENUM ('bank', 'cash')                                                                      NOT NULL,
+  `value`       DECIMAL(10, 2)                                                                             NULL,
+  `member_id`   INT(10) UNSIGNED                                                                           NOT NULL,
+  `description` TEXT                                                                                       NOT NULL,
+  `created_at`  DATETIME                                                                                   NOT NULL,
+  `modified_at` DATETIME                                                                                   NOT NULL,
+  PRIMARY KEY (`id`),
+  INDEX `idx_member_id` (`member_id`),
+  INDEX `idx_date` (`date`)
+)
+  ENGINE = InnoDB
+  DEFAULT CHARSET = utf8;
diff --git a/update.sql b/update.sql
new file mode 100644 (file)
index 0000000..1b1f55d
--- /dev/null
@@ -0,0 +1,16 @@
+/** After refactoring **/
+
+RENAME TABLE
+    `memberdb_members` TO `members`;
+
+RENAME TABLE
+    `memberdb_events` TO `events`;
+
+RENAME TABLE
+    `memberdb_crypto` TO `cryptography`;
+
+RENAME TABLE
+    `memberdb_earnings` TO `earnings`;
+
+RENAME TABLE
+    `memberdb_expenses` TO `expenses`;