· 7 years ago · Nov 03, 2018, 02:26 AM
1<?php
2$database = [
3 'name' => "name",
4 'user' => "user",
5 'pass' => "idk",
6];
7
8try
9{
10 $dsn = "mysql:host=127.0.0.1;dbname={$database['name']};";
11 $dbh = new PDO($dsn, $database['user'], $database['pass']);
12
13 # Disable emulation of prepared statements, use REAL prepared statements instead.
14 $dbh->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
15
16 # Set charset to uf8 (using query, that's the only way that make work SET NAMES).
17 $dbh->query("SET NAMES utf8mb4");
18}
19catch (PDOException $e)
20{
21 echo 'An error occured: ' . $e->getMessage();
22}
23
24<?php
25require_once('load.initialize.php');
26
27// Delete to-do list value.
28if (isset($_POST['delete']) && is_numeric($_POST['delete']))
29{
30 $stmt = $dbh->query("DELETE FROM tl_main WHERE id = {$_POST['delete']}");
31
32 if ($stmt)
33 {
34 $json_data['action'] = true; # true;
35 }
36}
37
38// Edit to-do list value.
39if (isset($_POST['edited_text']) && isset($_POST['value']) && is_numeric($_POST['value']))
40{
41 $stmt = $dbh->prepare("UPDATE tl_main SET text = :text, edit_date = NOW() WHERE id = {$_POST['value']}");
42 $stmt->bindParam(':text', $_POST['edited_text'], PDO::PARAM_STR);
43
44 if ($stmt->execute())
45 {
46 $stmt = $dbh->query("SELECT edit_date FROM tl_main WHERE id = {$_POST['value']} LIMIT 1");
47
48 $json_data['action'] = true; # true;
49 $json_data['edit_date'] = date("d/m/y H:i", strtotime($stmt->fetchColumn())); # Send it directly formatted as we setted in the page (instead of formatting it in jQuery)
50 }
51}
52
53// Add value to the to-do list
54if (isset($_POST['value_text']) && !empty($_POST['value_text']) && isset($_POST['type']) && is_numeric($_POST['type']))
55{
56 $stmt = $dbh->prepare("INSERT INTO tl_main (type, text, added_date) VALUES({$_POST['type']}, :text, NOW())");
57 $stmt->bindParam(':text', $_POST['value_text'], PDO::PARAM_STR);
58
59 if ($stmt->execute())
60 {
61 $json_data['action'] = true; # true;
62 }
63}
64
65// Set to-do list status to specific value.
66if (isset($_POST['status']) && isset($_POST['value']) && is_numeric($_POST['value']))
67{
68 switch ($_POST['status'])
69 {
70 case "completed":
71 $column_date = "completed_date";
72 $status = "completed = 1, tested = 0, completed_date = NOW()";
73 break;
74 case "tested":
75 $column_date = "tested_date";
76 $status = "completed = 0, tested = 1, tested_date = NOW()";
77 break;
78 case "indev":
79 $status = "completed = 0, tested = 0, completed_date = DEFAULT, tested_date = DEFAULT";
80 }
81
82 if ($status) {
83 $stmt = $dbh->query("UPDATE tl_main SET {$status} WHERE id = {$_POST['value']}");
84
85 if ($stmt)
86 {
87 if (isset($column_date))
88 {
89 $stmt = $dbh->query("SELECT {$column_date} FROM tl_main WHERE id = {$_POST['value']} LIMIT 1");
90 $json_data[$column_date] = date("d/m/y H:i", strtotime($stmt->fetchColumn())); # Send it directly formatted as we setted in the page (instead of formatting it in jQuery)
91 }
92
93 $json_data['action'] = true; # true;
94 }
95 }
96}
97
98// Display json infos for AJAX call for to-do list actions (delete, edit, add, status).
99if (isset($_POST['delete']) || isset($_POST['edited_text']) || isset($_POST['value_text']) || isset($_POST['status']))
100{
101 if (!isset($json_data))
102 $json_data['action'] = false;
103
104 header('Content-type: application/json');
105 echo json_encode($json_data);
106 exit;
107}
108
109// Fetch to-do list types.
110$sql = "SELECT * FROM tl_types";
111$types = $dbh->query($sql)->fetchAll();
112
113// Fetch to-do list content.
114$sql = "SELECT * FROM tl_main ORDER BY added_date DESC";
115$todolist = $dbh->query($sql)->fetchAll();
116
117// Apply/Fetch some operations to the todolist array.
118foreach ($todolist as &$value)
119{
120 // Formatting the text content.
121 // Catching http/https links.
122 $pattern = "@(http(s)?)?(://)+(([a-zA-Z])([-w]+.)+([^s.]+[^s]*)+[^,).s])@";
123 $value['text'] = preg_replace($pattern, '<a href="$0" target="_blank">$0</a>', $value['text']);
124
125 // Formatting datetimes.
126 $datetimes = [
127 'added_date' => $value['added_date'],
128 'edit_date' => $value['edit_date'],
129 'completed_date' => $value['completed_date'],
130 'tested_date' => $value['tested_date']
131 ];
132 foreach ($datetimes as $key => $datetime)
133 {
134 $value[$key] = strtotime($value[$key]);
135 }
136
137 // Build an array with type => array(edit_dates).
138 // Needed after for get the highest last edit for each type.
139 $type_edits[$value['type']][] = $value['edit_date'];
140}
141
142// Get in an array the highest edit date of the to-do list for every type in order to determine the last edit for each type.
143ksort($type_edits); # A whim just to have the array with edit dates ordered by type id.
144foreach ($type_edits as $type => $edits)
145{
146 $last_edit[$type] = date("d F H:i", max($edits));
147}
148
149// Check if last_edit array have missing types due to no content for the specific type (and so couldn't catch the last edit).
150foreach ($types as $type)
151{
152 if (!array_key_exists($type['id'], $last_edit))
153 {
154 $last_edit[$type['id']] = "foo";
155 }
156}
157
158<body>
159 <div class="background"></div>
160 <div id="container">
161 <ul id="menu">
162<?php foreach ($types as $type): ?>
163 <li><a href="#<?= $type['name'] ?>"><?= $type['name'] ?></a></li>
164<?php endforeach; ?>
165 <li id="special">[ <a class="toggle_all" style="cursor:pointer">toggle all</a> ]</li>
166 </ul>
167 <fieldset id="legend">
168 <legend>Legend</legend>
169 <div id="completed" class="row">
170 <span class="appearance"></span>
171 <span class="explanation">Completed</span>
172 </div>
173 <div id="tested" class="row">
174 <span class="appearance"></span>
175 <span class="explanation">Tested</span>
176 </div>
177 <div id="indev" class="row">
178 <span class="appearance"></span>
179 <span class="explanation">In development</span>
180 </div>
181 <div id="edited" class="row">
182 <span class="explanation">edited recently <span style="font-size:12px">(2 days)</span></span>
183 </div>
184 </fieldset>
185
186<?php foreach ($types as $type): ?>
187 <div id="<?= $type['name'] ?>" class="tab">
188 <div class="title group">
189 <div class="float_left">
190 <span class="main"><?= $type['name'] ?></span>
191 <span class="link">[ <a class="toggle" style="cursor:pointer">toggle</a> ]</span>
192 <span class="link">[ <a href="#<?= $type['name'] ?>">redirect</a> ]</span>
193 </div>
194 <div class="float_right">
195 <span class="last_edit">Last edit: <?= $last_edit[$type['id']] ?></span>
196 </div>
197 </div>
198 <table>
199 <tr>
200 <th class="subject"><span>Subject</span></th>
201 <th>Added</th>
202 <th>Mod</th>
203 </tr>
204 <tr>
205 <td class="subject"><textarea placeholder="Add new value..."></textarea></td>
206 <td class="date">00/00/00 00:00</td>
207 <td class="mod"><a id="add" data-type="<?= $type['id'] ?>"><i class="fas fa-plus"></i></a></td>
208 </tr>
209<?php foreach ($todolist as $content): ?>
210<?php if ($content['type'] === $type['id']): ?>
211<?php if ($content['completed']): ?>
212 <tr class="completed">
213<?php elseif ($content['tested']): ?>
214 <tr class="tested">
215<?php else: ?>
216 <tr>
217<?php endif; ?>
218 <td class="subject">
219 <div<?= ($content['edit_date'] > strtotime('-2 days')) ? ' style="font-style:italic"' : "" ?> data-value="<?= $content['id'] ?>" data-editable><?= $content['text'] ?></div>
220 </td>
221 <td class="date">
222 <span class="showhim"><?= date("d/m/y H:i", $content['added_date']) ?></span>
223 <div class="showme">
224 <ul>
225 <li><i class="fas fa-pencil-alt"></i> <span id="edit_date"><?= date("d/m/y H:i", $content['edit_date']) ?></span></li>
226 <li><i class="far fa-thumbs-up"></i> <span id="completed_date"><?= date("d/m/y H:i", $content['completed_date']) ?></span></li>
227 <li><i class="fas fa-check"></i> <span id="tested_date"><?= date("d/m/y H:i", $content['tested_date']) ?></span></li>
228 </ul>
229 </div>
230 </td>
231 <td class="mod">
232<?php if ($content['completed']): ?>
233 <a id="status" data-status="tested" data-value="<?= $content['id'] ?>"><i class="fas fa-check"></i></a>
234<?php elseif ($content['tested']): ?>
235 <a id="status" data-status="indev" data-value="<?= $content['id'] ?>"><i class="fas fa-undo-alt"></i></a>
236<?php else: ?>
237 <a id="status" data-status="completed" data-value="<?= $content['id'] ?>"><i class="far fa-thumbs-up"></i></a>
238<?php endif; ?>
239 <a id="delete" data-value="<?= $content['id'] ?>"><i class="far fa-trash-alt"></i></a>
240 </td>
241 </tr>
242<?php endif; ?>
243<?php endforeach; ?>
244 </table>
245 </div>
246<?php endforeach; ?>
247 </div>
248<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
249<script src="assets/general.js"></script>
250</body>
251
252// Return window confirm() method for ajax requests.
253function ajax_confirm(request) {
254 return confirm("Are you sure?!nAJAX request: " + request);
255}
256
257// Toggle specific table.
258$('.toggle').on('click', function() {
259 $(this).closest('.title').next('table').toggle();
260});
261
262// Toggle all tables.
263$('.toggle_all').on('click', function() {
264 $('table').toggle();
265});
266
267// AJAX POST request in order to delete a specific value.
268$('td.mod #delete').on('click', function() {
269 // Denied confirm alert doesn't run the script.
270 if (!ajax_confirm('delete')) return;
271
272 var $this = $(this);
273
274 var request = {
275 'delete': $this.data('value')
276 };
277
278 var posting = $.post(window.location, request, 'json');
279
280 posting.done(function(data) {
281 // Check if error query occurs.
282 if (!data.action) return;
283
284 $this.closest('tr').hide('fast', function() {
285 $this.closest('tr').remove();
286 });
287 })
288});
289
290// AJAX POST request in order to edit a specific value.
291$('table').on('dblclick', 'td div[data-editable]', function() {
292 var $this = $(this);
293
294 var $textarea = $('<textarea />').height($this.height()).val($this.text());
295 $this.replaceWith($textarea);
296
297 var save = function() {
298 // Denied confirm alert doesn't send the AJAX POST request.
299 // Text has not changed doesn't send the AJAX POST request.
300 if (!ajax_confirm('edit') || $textarea.val() == $this.text()) {
301 $textarea.replaceWith($this);
302 return;
303 }
304
305 var request = {
306 'edited_text': $textarea.val(),
307 'value': $this.data('value')
308 };
309
310 var posting = $.post(window.location, request, 'json');
311
312 posting.done(function(data) {
313 // Check if error query occurs.
314 if (!data.action) {
315 alert(data);
316 $textarea.replaceWith($this);
317 return;
318 }
319
320 $div = $this.text($textarea.val()).css('font-style', 'italic')
321 $textarea.replaceWith($div);
322
323 $this.closest('tr').find('span#edit_date').text(data.edit_date);
324 });
325 };
326
327 /**
328 We're defining the callback with `one`, because we know that
329 the element will be gone just after that, and we don't want
330 any callbacks leftovers take memory.
331 Next time `div` turns into `textarea` this single callback
332 will be applied again.
333 */
334 $textarea.one('blur', save).focus();
335});
336
337// AJAX POST request in order to add a value.
338$('td.mod #add').on('click', function() {
339 // Denied confirm alert doesn't run the script.
340 if (!ajax_confirm('add')) return;
341
342 var $this = $(this);
343 var $textarea = $this.closest('tr').find('textarea');
344
345 // Check if textarea is empty
346 if (!$.trim($textarea.val())) {
347 $this.closest('table').before('<div id="error" class="notice" style="display:none">Please fill out the textarea value.</div>');
348 $('#error').stop(true).fadeIn().delay(5000).fadeOut(function() {
349 $('#error').remove();
350 });
351 return;
352 }
353
354 var request = {
355 'value_text': $textarea.val(),
356 'type': $this.data('type')
357 };
358
359 var posting = $.post(window.location, request, 'json');
360
361 posting.done(function(data) {
362 // Check if error query occurs.
363 if (!data.action) return;
364
365 $this.closest('table').before('<div id="success" class="notice" style="display:none">Value added succesfully, refresh the page to view it.</div>');
366 $('#success').stop(true).fadeIn().delay(5000).fadeOut(function() {
367 $('#success').remove();
368 });
369
370 // Reset textarea value
371 $textarea.val('');
372 });
373});
374
375// AJAX POST request in order to set the status of a specific value.
376$('td.mod #status').on('click', function() {
377 // Denied confirm alert doesn't run the script.
378 if (!ajax_confirm('status')) return;
379
380 var $this = $(this);
381
382 var request = {
383 'status': $this.data('status'),
384 'value': $this.data('value')
385 };
386
387 var posting = $.post(window.location, request, 'json');
388
389 posting.done(function(data) {
390 // Check if error query occurs.
391 if (!data.action) return;
392
393 switch (request.status) {
394 case "completed":
395 /*
396 * If completed:
397 * Add completed class to tr.
398 * Update data-status to the next status (tested).
399 * Update icon class to the next status (tested) icon.
400 * Update completed datetime.
401 */
402 $this.closest('tr').addClass('completed');
403
404 $this.data('status', 'tested');
405
406 $this.children('svg').attr('data-prefix', 'fas').attr('data-icon', 'check');
407
408 $this.closest('tr').find('span#completed_date').text(data.completed_date);
409 break;
410 case "tested":
411 /*
412 * If tested:
413 * Remove completed class from tr. / Add tested class to tr.
414 * Update data-status to the next status (indev).
415 * Update icon class to the next status (indev) icon.
416 * Update tested datetime.
417 */
418 $this.closest('tr').removeClass('completed');
419 $this.closest('tr').addClass('tested');
420
421 $this.data('status', 'indev');
422
423 $this.children('svg').attr('data-prefix', 'fas').attr('data-icon', 'undo-alt');
424
425 $this.closest('tr').find('span#tested_date').text(data.tested_date);
426 break;
427 case "indev":
428 /*
429 * If in-dev:
430 * Remove tested class from tr. / No need to add class since indev take default background-color.
431 * Update data-status to the next status (completed).
432 * Update icon class to the next status (completed) icon.
433 * Remove completed and tested datetime.
434 */
435 $this.closest('tr').removeClass('tested');
436
437 $this.data('status', 'completed');
438
439 $this.children('svg').attr('data-prefix', 'far').attr('data-icon', 'thumbs-up');
440
441 $this.closest('tr').find('span#completed_date').text("foo");
442 $this.closest('tr').find('span#tested_date').text("foo");
443 break;
444 }
445 })
446});
447
448CREATE TABLE IF NOT EXISTS `tl_main` (
449 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
450 `type` tinyint(3) unsigned NOT NULL,
451 `text` mediumtext CHARACTER SET utf8 NOT NULL,
452 `added_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
453 `edit_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
454 `completed_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
455 `tested_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
456 `completed` tinyint(1) NOT NULL DEFAULT '0',
457 `tested` tinyint(1) NOT NULL DEFAULT '0',
458 PRIMARY KEY (`id`)
459) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 AUTO_INCREMENT=87 ;
460
461CREATE TABLE IF NOT EXISTS `tl_types` (
462 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
463 `name` varchar(45) NOT NULL,
464 PRIMARY KEY (`id`)
465) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;