/
database.inc.php
162 lines (110 loc) · 4.72 KB
/
database.inc.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
<?php
if (!file_exists($dbfile)) {
echo "Database doesn't exist!";
exit;
}
if (!is_writable($dbfile)) {
echo "Database is not writable!";
exit;
}
if (!is_writable(dirname($dbfile))) {
echo "Database directory is not writable!";
exit;
}
$db = new PDO('sqlite:' . $dbfile);
$db->setAttribute(PDO::ATTR_ERRMODE,
PDO::ERRMODE_EXCEPTION);
function dbCreateEvent ($db, $event_name, $event_owner, $event_time, $event_rsvp = NULL, $event_note = NULL) {
$insert = "INSERT INTO events (event_name, event_owner, event_time, event_rsvp, event_note)
VALUES (:event_name, :event_owner, :event_time, :event_rsvp, :event_note)";
$stmt = $db->prepare($insert);
$stmt->bindParam(':event_name', $event_name);
$stmt->bindParam(':event_owner', $event_owner);
$stmt->bindParam(':event_time', $event_time);
$stmt->bindParam(':event_rsvp', $event_rsvp);
$stmt->bindParam(':event_note', $event_note);
return $stmt->execute();
}
function dbListEvents($db) {
echo "*List of currently open events:*\n\n";
$result = $db->query('SELECT events.*, IFNULL(SUM(attendees.attendee_num),0) AS attendee_num FROM events LEFT JOIN attendees ON events.id = attendees.event_id GROUP BY events.id ORDER BY `event_time` ASC');
foreach ($result as $r) {
echo '*' . $r['event_name'] . '* @ *' . my_date($r['event_time']) . '* by *' . $r['event_owner'] . '* (*' . $r['attendee_num'] . '*)';
if ($r['event_rsvp'] != NULL) {
echo ' (RSVP: ' . my_date($r['event_rsvp']) . ')';
}
if ($r['event_note'] != NULL) {
echo ' ' . $r['event_note'];
}
echo "\n\n";
}
}
function dbAttendEvent($db, $event_name, $attendee_name, $attendee_num, $attendee_text = NULL) {
if ($attendee_num === NULL)
$attendee_num = 1;
$now = time();
$select = 'SELECT id FROM events WHERE event_name LIKE :event_name AND event_time > :now AND (event_rsvp > :now OR event_rsvp IS NULL)';
$stmt = $db->prepare($select);
$stmt->bindParam(':event_name', $event_name);
$stmt->bindParam(':now', $now);
$stmt->execute();
$result = $stmt->fetchAll();
if (count($result) != 1) {
echo "Hmm, noway looser!";
exit;
}
$event_id = $result[0]['id'];
$insert = "REPLACE INTO attendees (event_id, attendee_name, attendee_num, attendee_text)
VALUES (:event_id, :attendee_name, :attendee_num, :attendee_text)";
$stmt = $db->prepare($insert);
$stmt->bindParam(':event_id', $event_id);
$stmt->bindParam(':attendee_name', $attendee_name);
$stmt->bindParam(':attendee_num', $attendee_num);
$stmt->bindParam(':attendee_text', $attendee_text);
return $stmt->execute();
}
function dbEventStatus($db, $event_name) {
echo "*List of attendees for " . $event_name . " :*\n\n";
$now = time();
$select = 'SELECT attendee_name, attendee_num, attendee_text FROM events,attendees WHERE events.event_name LIKE :event_name AND events.id = attendees.event_id AND event_time > :now ORDER BY attendees.id ASC';
$stmt = $db->prepare($select);
$stmt->bindParam(':event_name', $event_name);
$stmt->bindParam(':now', $now);
$stmt->execute();
$result = $stmt->fetchAll();
$total = 0;
foreach ($result as $r) {
echo '*' . $r['attendee_name'] . '* (*' . $r['attendee_num'] . '*) ' . $r['attendee_text'];
echo "\n";
$total += $r['attendee_num'];
}
echo "\n" . 'Total attendees: *' . $total . '*' . "\n\n";
}
function dbModifyEvent($db, $event_name, $event_owner, $event_time, $event_rsvp_time = NULL, $event_note = NULL) {
$select = 'SELECT SUM(attendee_num) as attendee_num FROM events,attendees WHERE events.event_name LIKE :event_name AND events.id = attendees.event_id AND event_time = :event_time';
$stmt = $db->prepare($select);
$stmt->bindParam(':event_name', $event_name);
$stmt->bindParam(':event_time', $event_time);
$stmt->execute();
$result = $stmt->fetch();
if ($result['attendee_num'] > 0) {
echo "You cannot change an event that already have attendees, dickhead!";
exit;
}
$stmt = $db->prepare('SELECT event_owner FROM events WHERE event_name = :event_name AND event_time = :event_time');
$stmt->bindParam(':event_name', $event_name);
$stmt->bindParam(':event_time', $event_time);
$stmt->execute();
$result = $stmt->fetch();
if ($result['event_owner'] != $event_owner) {
echo "You cannot change an event that isn't yours - cheater!";
exit;
}
$query = 'UPDATE events SET event_rsvp = :event_rsvp, event_note = :event_note WHERE event_name LIKE :event_name AND event_time = :event_time';
$stmt = $db->prepare($query);
$stmt->bindParam(':event_rsvp', $event_rsvp_time);
$stmt->bindParam(':event_note', $event_note);
$stmt->bindParam(':event_name', $event_name);
$stmt->bindParam(':event_time', $event_time);
return $stmt->execute();
}