3 class Tour extends DatabaseTable
6 protected $_is_planned;
8 public function __construct($id, $column=false)
10 parent::__construct('tour', $id, $column);
13 public function isAdmin()
15 if (is_null($this->_is_admin)) {
16 $sql = sprintf("SELECT admin FROM tour_member WHERE tour_id = %d AND member_id = %d",
17 $this->id, $_SESSION['userid']);
18 $this->_is_admin = $this->db->fetchValue($sql);
21 return $this->_is_admin;
24 public function isPlanned()
26 if (is_null($this->_is_planned)) {
27 $sql = sprintf("SELECT key = 'plan' FROM tour_status WHERE id = %d", $this->data->tour_status_id);
28 $this->_is_planned = $this->db->fetchValue($sql);
31 return $this->_is_planned;
34 public function getBaseData()
43 extract(day from start_date) ||'.'|| extract(month from start_date) ||'.'|| extract(year from start_date) AS start_datum,
44 tour_status.key AS status_key,
45 tour_status.name AS status
47 JOIN tour_status ON tour_status_id = tour_status.id
48 LEFT JOIN tour_date ON tour_date_id = tour_date.id
49 WHERE tour.id = {$this->id}
52 return $this->db->fetchObject($sql);
55 public function toggleDateStatus($tour_date_id, $sys_user_id)
57 $date = new Tour_Date($tour_date_id);
60 SELECT tour_date_member.id,tour_date_status_id
62 JOIN tour_date_member ON tour_date_id = tour_date.id
63 JOIN tour_member ON tour_member_id = tour_member.id
64 WHERE tour_date.id = %d AND tour_member.member_id = %d
66 $sql = sprintf($sql, $tour_date_id, $sys_user_id);
68 $row = $this->db->fetchObject($sql);
74 JOIN tour ON tour_id = tour.id
75 JOIN tour_member using(tour_id)
76 WHERE tour_date.id = %d AND member_id = %d
78 $sql = sprintf($sql, $tour_date_id, $sys_user_id);
79 $member_id = $this->db->fetchValue($sql);
81 $sql = "SELECT id FROM tour_date_status ORDER BY priority OFFSET 1 LIMIT 1";
82 $status_id = $this->db->fetchValue($sql);
84 $this->db->insertInto('tour_date_member', ['tour_date_id' => $tour_date_id,
85 'tour_member_id' => $member_id,
86 'tour_date_status_id' => $status_id]);
87 $status = new Tour_Date_Status($status_id);
88 if ($sys_user_id == $_SESSION['userid'])
89 Tour_Log::add($this->id(), sprintf("Verfügbarkeit am %s: %s",
90 $date->germanDate(), $status->get('name')));
92 $user = new Sys_User($sys_user_id);
93 Tour_Log::add($this->id(), sprintf("Verfügbarkeit von %s am %s: %s",
95 $date->germanDate(), $status->get('name')));
98 $sql = sprintf("SELECT id FROM tour_date_status WHERE id > %d ORDER BY priority LIMIT 1",
99 $row->tour_date_status_id);
100 $status_id = $this->db->fetchValue($sql);
102 if ($status_id === false) {
103 $sql = "SELECT id FROM tour_date_status ORDER BY priority LIMIT 1";
104 $status_id = $this->db->fetchValue($sql);
107 $this->db->update('tour_date_member', ['tour_date_status_id' => $status_id], 'id = ' . $row->id);
109 $status = new Tour_Date_Status($status_id);
110 $status_name = $status->get('name');
111 if (!strlen($status_name))
112 $status_name = 'n/a';
114 if ($sys_user_id == $_SESSION['userid']) {
115 Tour_Log::addRewrite($this->id(),
116 sprintf("Verfügbarkeit am %s: ", $date->germanDate()),
119 $user = new Sys_User($sys_user_id);
120 Tour_Log::addRewrite($this->id(),
121 sprintf("Verfügbarkeit von %s am %s: ", $user->get('name'), $date->germanDate()),
127 public function inviteMember($sys_user_id)
129 if (!$this->isAdmin())
130 throw new Exception("Keine Berechtigung zum Einladen");
132 $user = new Sys_User($sys_user_id);
135 throw new Exception("User $sys_user_id not found");
137 $this->db->insertInto('tour_member',
138 ['tour_id' => $this->id(),
139 'member_id' => $sys_user_id]);
141 $myself = new Sys_User($_SESSION['userid']);
142 $request = Application::get()->getRequest();
143 $url = sprintf('%s://%s%s', $request->getScheme(), $request->getHttpHost(), Application::get()->getBaseURL());
144 $body = Template::render('tour/invitemail',
145 ['inviter' => $myself->get('name'),
146 'tour_name' => $this->get('name'),
147 'tour_year' => $this->get('year'),
148 'tour_duration' => $this->get('duration'),
149 'name' => $user->get('name'),
150 'email' => $user->get('email'),
154 $mail->env_from(MAIL_FROM);
155 $mail->set('From', mb_encode_mimeheader(utf8_decode(sprintf("%s <%s>", MAIL_FROM_NAME, MAIL_FROM)),'latin1'));
156 $mail->set('To', $user->get('email'));
157 $mail->set('Subject', mb_encode_mimeheader(utf8_decode(sprintf("Einladung zur Tour %s", $this->get('name'))),'latin1'));
162 public function getDates()
168 extract(day from start_date) ||'.'|| extract(month from start_date) ||'.'|| extract(year from start_date) AS start_datum
170 WHERE tour_date.tour_id = {$this->id()}
174 $list = $this->db->fetchObjectList($sql);
176 foreach ($list as &$row) {
177 $dt = new DateTime($row->start_date);
178 $row->year = $dt->format('Y');
179 $row->start_short = $dt->format('d.m.');
180 $dt->add(new DateInterval('P'.$this->get('duration').'D'));
181 $row->end_short = $dt->format('d.m.');
187 public function getMembers($admin=null)
189 if (!is_null($admin))
190 $cond_admin = ' AND tour_member.admin = ' . ($admin ? 'true' : 'false');
197 sys_user.single_room,
201 JOIN tour_member ON member_id = sys_user.id
202 WHERE tour_id = {$this->id}{$cond_admin}
206 $list = $this->db->fetchObjectList($sql);
211 public function getAvailability($user_id)
213 $list = $this->getDates();
215 if (!count($list)) return [];
218 foreach ($list as &$row)
220 $ids = join(',', $ids);
225 tour_date_status.key AS status_key,
226 tour_date_status.name AS status_text
227 FROM tour_date_member
228 JOIN tour_member ON tour_member_id = tour_member.id AND member_id = {$user_id}
229 JOIN tour_date_status ON tour_date_status_id = tour_date_status.id
230 WHERE tour_date_id IN ({$ids})
233 foreach ($this->db->fetchObjectList($sql) as $item) {
234 foreach ($list as &$row) {
235 if ($row->id == $item->tour_date_id) {
236 $row->status_key = $item->status_key;
237 $row->status_text = $item->status_text;
246 public function getAvailabilityMatrix()
248 $list = $this->getDates();
251 foreach ($list as &$row)
253 $ids = join(',', $ids);
260 tour_date_status.key AS status_key,
261 tour_date_status.name AS status_text
262 FROM tour_date_member
263 JOIN tour_member ON tour_member_id = tour_member.id
264 JOIN sys_user ON sys_user.id = member_id
265 JOIN tour_date_status ON tour_date_status_id = tour_date_status.id
266 WHERE tour_date_id IN ({$ids})
270 foreach ($this->db->fetchObjectList($sql) as $item) {
271 foreach ($list as &$row) {
272 if ($row->id == $item->tour_date_id) {
273 if (!is_array($row->avail))
276 if (!in_array($item->member_id, $userlist)) {
278 foreach (explode(' ', $item->name) as $word)
279 $nick .= substr($word,0,1);
281 $user = new stdClass();
283 $user->name = $item->name;
284 $userlist[$item->member_id] = $user;
287 $av = new stdClass();
288 $av->member_id = $item->member_id;
289 $av->nick = $userlist[$item->member_id]->nick;
290 $av->status_key = $item->status_key;
291 $av->status_text = $item->status_text;
293 $row->avail[$item->member_id] = $av;
299 return ['user' => $userlist, 'list' => $list];
302 public function getNotes($all=false)
305 $condition = ' AND tour_note_seen.id IS NULL';
310 tour_note.sys_user_id = {$_SESSION['userid']} AS own,
313 extract(day from sys_user.sys_edit) ||'.'|| extract(month from sys_user.sys_edit) ||'.'|| extract(year from sys_user.sys_edit) AS datum
315 JOIN sys_user ON tour_note.sys_user_id = sys_user.id
316 LEFT JOIN tour_note_seen ON tour_note_id = tour_note.id
317 WHERE tour_id = {$this->id} AND deleted = false{$condition}
318 ORDER BY tour_note.sys_edit DESC
321 $list = $this->db->fetchObjectList($sql);
324 foreach ($list as &$row) {
325 $row->note_html = Wiki::renderHTML($row->note);
327 $this->db->insertInto('tour_note_seen', ['tour_note_id' => $row->id]);