· 8 years ago · Dec 19, 2017, 08:42 PM
1<!--
2 availabilty.php
3-->
4
5<?php
6
7 require_once 'database_connection.php';
8
9 require_once 'login_include.php';
10
11
12 if ($_POST) {
13 $query = $_POST['query'];
14
15 $sql =
16 "SELECT
17 p.id AS product_id,
18 p.name AS product_name,
19 p.price AS price,
20 p_l.quantity AS quantity,
21 l.name AS location_name
22
23 FROM products AS p
24
25 LEFT JOIN product_locations AS p_l ON p_l.product_id = p.id
26 LEFT JOIN locations AS l ON l.id = p_l.location_id
27
28 WHERE p.name LIKE '%$query%'
29 ";
30
31 $results = $db->query($sql);
32 }
33
34?>
35
36<!DOCTYPE html>
37<html>
38<head>
39 <meta charset="utf-8">
40 <title>Tools4Ever</title>
41</head>
42<body>
43<a href="index.php">Home</a><br>
44<br>
45<form action="availabilty.php" method="POST">
46 <input type="text" name="query" placeholder="Zoek een product" required><br>
47
48 <button type="submit">Zoek</button>
49</form>
50<br>
51
52<?php
53if (!empty($results)) {
54?>
55<table style="text-align: left">
56 <thead>
57 <th>id</th>
58 <th>naam</th>
59 <th>prijs</th>
60 <th>quantity</th>
61 <th>totaal prijs</th>
62 <th>location</th>
63 </thead>
64 <tbody>
65 <?php
66 if ($results->num_rows > 0) {
67 while ($row = $results->fetch_assoc()) {
68 ?>
69
70 <tr>
71 <td><?php echo $row['product_id']; ?></td>
72 <td><?php echo $row['product_name']; ?></td>
73 <td>€<?php echo number_format($row['price'],2,',','.'); ?></td>
74 <td><?php echo $row['quantity']; ?></td>
75 <td>€<?php echo number_format($row['price'] * $row['quantity'],2,',','.'); ?></td>
76 <td><?php echo $row['location_name']; ?></td>
77 </tr>
78
79 <?php
80 }
81 }
82 ?>
83 </tbody>
84</table>
85<?php
86}
87?>
88</body>
89</html>
90
91<!--
92 END availabilty.php END
93-->
94
95<!--
96 database_connection.php
97-->
98
99<?php
100
101$db_host = "localhost";
102$db_user = "root";
103$db_pass = "";
104$db_name = "toolsforever";
105
106$db = mysqli_connect($db_host, $db_user, $db_pass, $db_name);
107?>
108
109<!--
110 END database_connection.php END
111-->
112
113<!--
114 index.php
115-->
116
117<?php
118 require_once 'database_connection.php';
119
120 require_once 'login_include.php';
121?>
122
123<!DOCTYPE html>
124<html>
125 <head>
126 <meta charset="utf-8">
127 <title>Tools4Ever</title>
128 </head>
129 <body>
130 <a href="locations.php">Locations</a><br>
131 <a href="products.php">Producten</a><br>
132 <a href="availabilty.php">Beschikbaarheid</a><br>
133
134 <br>
135 <a href="logout.php">Log uit!</a>
136 </body>
137</html>
138
139<!--
140 END index.php END
141-->
142
143<!--
144 locations_add.php
145-->
146
147<?php
148
149require_once 'database_connection.php';
150require_once 'login_include.php';
151
152$location_name = strtoupper($_POST['name']);
153
154$sql = "INSERT INTO locations(name) VALUES('$location_name')";
155$db->query($sql);
156
157header('Location: locations.php');
158?>
159
160<!--
161 END locations_add.php END
162-->
163
164<!--
165 locations_delete.php
166-->
167
168<?php
169
170require_once 'database_connection.php';
171require_once 'login_include.php';
172
173$location_id = strtoupper($_GET['item_id']);
174
175$sql = "DELETE FROM locations WHERE id=$location_id";
176$deleted = $db->query($sql);
177
178if ($deleted) {
179 header('Location: locations.php');
180} else {
181 $sql = "SELECT * FROM locations WHERE id=$location_id";
182 $result = $db->query($sql);
183
184 $location = $result->fetch_assoc();
185
186 header('Location: locations.php?error=De+locatie+'.$location['name'].'+is+nog+in+gebruik%21');
187}
188?>
189
190<!--
191 END locations_delete.php END
192-->
193
194<!--
195 locations_show.php
196-->
197
198<?php
199require_once 'database_connection.php';
200require_once 'login_include.php';
201
202if ($_GET['id']) {
203 $total_worth = 0;
204
205 $location_id = $_GET['id'];
206
207 $sql = "SELECT * FROM locations WHERE id = $location_id";
208
209 $location = $db->query($sql)->fetch_assoc();
210
211 $sql = "SELECT
212 p.id AS product_id,
213 p.name AS product_name,
214 p.price AS product_price,
215 p_l.quantity AS quantity
216
217 FROM product_locations AS p_l
218 LEFT JOIN locations AS l ON l.id = p_l.location_id
219 LEFT JOIN products AS p ON p.id = p_l.product_id
220 WHERE p_l.location_id = $location_id";
221
222 $products = $db->query($sql);
223?>
224<a href="locations.php">Terug</a><br><br>
225Locatie: <?php echo $location['name']; ?>
226<br>
227<?php
228if ($products->num_rows > 0) {
229?>
230<br><br>
231<b>Producten:</b>
232<br>
233<table>
234 <thead>
235 <tr>
236 <th>id</th>
237 <th>naam</th>
238 <th>prijs</th>
239 <th>aantal</th>
240 <th>totaal</th>
241 </tr>
242 </thead>
243 <tbody>
244 <?php
245
246 while ($row = $products->fetch_assoc()) {
247
248 $total_worth += $row['product_price'] * $row['quantity'];
249
250 ?>
251 <tr>
252 <td><?php echo $row['product_id']; ?></td>
253 <td><?php echo $row['product_name']; ?></td>
254 <td>€<?php echo number_format($row['product_price'], 2, ',', '.'); ?></td>
255 <td><?php echo $row['quantity']; ?></td>
256 <td>€<?php echo number_format($row['product_price'] * $row['quantity'], 2, ',', '.'); ?></td>
257 </tr>
258 <?php
259 }
260 }
261}
262?>
263</tbody>
264</table>
265<br>
266Totale waarde: <b>€<?php echo number_format($total_worth,2,',','.'); ?></b>
267
268<!--
269 END locations_show.php END
270-->
271
272<!--
273 locations.php
274-->
275
276<?php
277require_once 'database_connection.php';
278require_once 'login_include.php';
279
280$sql = "
281 SELECT l.id AS location_id,
282 l.name AS location_name,
283 p.price AS product_price,
284 p_l.quantity AS quantity
285 FROM locations AS l
286 LEFT JOIN product_locations AS p_l ON p_l.location_id = l.id
287 LEFT JOIN products AS p ON p.id = p_l.product_id
288 ORDER BY l.id
289 ";
290
291$product_locations = $db->query($sql);
292
293
294$locations = [];
295if($product_locations->num_rows > 0) {
296 while ($row = $product_locations->fetch_assoc()) {
297
298 if (empty($locations[$row['location_id']])) {
299 $locations[$row['location_id']] = [
300 'id' => $row['location_id'],
301 'name' => $row['location_name']
302 ];
303 }
304
305 if (!empty($locations[$row['location_id']]['total'])) {
306 $locations[$row['location_id']]['total'] += $row['product_price'] * $row['quantity'];
307 } else {
308 $locations[$row['location_id']]['total'] = $row['product_price'] * $row['quantity'];
309 }
310
311 }
312}
313
314?>
315
316<!DOCTYPE html>
317<html>
318<head>
319 <meta charset="utf-8">
320 <title>Tools4Ever</title>
321</head>
322<body>
323<a href="index.php">Home</a><br>
324<br>
325<form action="locations_add.php" method="POST">
326 <input type="text" name="name" placeholder="Naam" required><br>
327
328 <button type="submit">Voeg locatie toe</button>
329</form>
330<br>
331<table style="text-align: left">
332 <thead>
333 <th>id</th>
334 <th>naam</th>
335 <th>totale waarde</th>
336 </thead>
337 <tbody>
338 <?php
339
340 if(!empty($_GET['error'])) {
341 echo $_GET['error'];
342 }
343
344 foreach ($locations as $location) {
345 ?>
346 <tr>
347 <td><?php echo $location['id']; ?></td>
348 <td><a href="locations_show.php?id=<?php echo $location['id']; ?>"><?php echo $location['name']; ?></a></td>
349 <td>€<?php echo number_format($location['total'],2, ',', '.');?></td>
350 <td><a href="locations_delete.php?item_id=<?php echo $location['id']; ?>">delete</a></td>
351 </tr>
352 <?php
353 }
354 ?>
355 </tbody>
356</table>
357
358</body>
359</html>
360
361
362<!--
363 END locations.php END
364-->
365
366<!--
367 login_include.php
368-->
369
370<?php
371session_start();
372if (!$_SESSION['username']) {
373 header('Location: login.php');
374}
375?>
376
377<!--
378 END login_include.php END
379-->
380
381<!--
382 login.php
383-->
384
385<?php
386
387require_once 'database_connection.php';
388
389session_start();
390if (!empty($_SESSION['username'])) {
391 header('Location: index.php');
392}
393
394if ($_POST) {
395 $username = $_POST['username'];
396 $password = md5($_POST['password']);
397
398 $sql = "SELECT * FROM users WHERE username = '$username' AND password = '$password'";
399 $user = $db->query($sql);
400
401 if ($user->num_rows == 1) {
402 session_start();
403
404 $row = $user->fetch_assoc();
405
406 $_SESSION['username'] = $row['username'];
407
408 header('Location: index.php');
409 }
410}
411
412?>
413
414<!DOCTYPE html>
415<html>
416<head>
417 <meta charset="utf-8">
418 <title>Tools4Ever</title>
419</head>
420<body>
421<form action="login.php" method="POST">
422 <input type="text" name="username" placeholder="Gebruikersnaam" required><br>
423 <input type="password" name="password" placeholder="Wachtwoord" required><br>
424
425 <button type="submit">Login</button>
426</form>
427</body>
428</html>
429
430<!--
431 END login.php END
432-->
433
434<!--
435 logout.php
436-->
437
438<?php
439session_start();
440
441if ($_SESSION['username']) {
442 session_destroy();
443 header('Location: login.php');
444}
445
446header('Location: login.php');
447?>
448
449<!--
450 END logout.php END
451-->
452
453<!--
454 products_add.php
455-->
456
457<?php
458
459require_once 'database_connection.php';
460require_once 'login_include.php';
461
462$product_name = strtoupper($_POST['name']);
463$product_price = $_POST['price'];
464$product_quantity = $_POST['quantity'];
465$location_id = $_POST['location'];
466
467$sql = "SELECT * FROM products WHERE name = '$product_name'";
468$match = $db->query($sql);
469
470if($match->num_rows > 0) {
471
472 $product = $match->fetch_assoc();
473
474 $sql = "SELECT * FROM product_locations WHERE product_id = ". $product['id'] ." AND location_id = ". $location_id;
475 $match = $db->query($sql);
476
477 if($match->num_rows > 0) {
478
479 $sql = "UPDATE product_locations SET quantity = quantity + $product_quantity WHERE product_id = ". $product['id'] ." AND location_id = ". $location_id ;
480 $db->query($sql);
481
482 } else {
483
484 $sql = "INSERT INTO product_locations(product_id, location_id, quantity) VALUES(".$product['id'].", $location_id, $product_quantity)";
485 $db->query($sql);
486
487 }
488
489} else {
490
491 $sql = "INSERT INTO products(name, price) VALUES('$product_name', '$product_price')";
492 $db->query($sql);
493
494 $id = $db->insert_id;
495
496 $sql = "INSERT INTO product_locations(product_id, location_id, quantity) VALUES($id, $location_id, $product_quantity)";
497 $db->query($sql);
498
499}
500
501
502header("Location: products.php");
503
504?>
505
506<!--
507 END products_add.php END
508-->
509
510<!--
511 products_move.php
512-->
513
514<?php
515
516require_once 'database_connection.php';
517require_once 'login_include.php';
518
519if ($_POST) {
520 $product_id = $_POST['product_id'];
521 $location_id = $_POST['location_id'];
522 $new_location_id = $_POST['new_location_id'];
523 $quantity = $_POST['quantity'];
524
525 $sql = "SELECT * FROM product_locations
526 WHERE location_id = $location_id
527 AND product_id = $product_id";
528 $record_quantity = $db->query($sql)->fetch_assoc();
529 $record_quantity = $record_quantity['quantity'];
530
531 $delete_old_record = false;
532 if ($quantity >= $record_quantity) {
533 $quantity = $record_quantity;
534
535 $delete_old_record = true;
536 }
537
538 $sql = "SELECT * FROM product_locations
539 WHERE location_id = $new_location_id
540 AND product_id = $product_id";
541 $match = $db->query($sql);
542
543 if ($match->num_rows == 1) {
544 $sql = "UPDATE product_locations
545 SET quantity=quantity+$quantity
546 WHERE location_id = $new_location_id
547 AND product_id = $product_id";
548 $db->query($sql);
549
550 if ($delete_old_record) {
551 $sql = "DELETE FROM product_locations
552 WHERE location_id = $location_id
553 AND product_id = $product_id";
554 $db->query($sql);
555 } else {
556 $sql = "UPDATE product_locations
557 SET quantity=quantity-$quantity
558 WHERE location_id = $location_id
559 AND product_id = $product_id";
560 $db->query($sql);
561 }
562 } else {
563 if ($delete_old_record) {
564 $sql = "UPDATE product_locations
565 SET location_id=$new_location_id
566 WHERE location_id = $location_id
567 AND product_id = $product_id";
568 $db->query($sql);
569 } else {
570 $sql = "INSERT INTO product_locations(product_id, location_id, quantity)
571 VALUES($product_id, $new_location_id, $quantity)";
572 $db->query($sql);
573
574 $sql = "UPDATE product_locations
575 SET quantity=quantity-$quantity
576 WHERE location_id = $location_id
577 AND product_id = $product_id";
578 $db->query($sql);
579 }
580 }
581
582 header('Location: products.php');
583}
584
585if (!empty($_GET['product_id']) && !empty($_GET['location_id'])) {
586
587
588 $product_id = $_GET['product_id'];
589 $location_id = $_GET['location_id'];
590
591 $sql = "SELECT * FROM locations WHERE id != $location_id";
592 $other_locations = $db->query($sql);
593
594 $sql = "
595 SELECT p_l.quantity AS quantity,
596 p.id AS product_id,
597 p.name AS product_name,
598 p.price AS price,
599 l.name AS location_name,
600 l.id AS location_id
601 FROM product_locations AS p_l
602 LEFT JOIN products AS p ON p.id = p_l.product_id
603 LEFT JOIN locations AS l ON l.id = p_l.location_id
604 WHERE p_l.product_id = $product_id AND p_l.location_id = $location_id";
605
606 $record = $db->query($sql);
607
608 if($record->num_rows == 1 && $other_locations->num_rows > 0) {
609 $record = $record->fetch_assoc();
610 } else {
611 header('Location: products.php');
612 }
613?>
614
615Waar wilt u <?php echo $record['quantity'] . 'x ' . $record['product_name'] . ' van ' . $record['location_name']; ?> heen verplaatsen?
616<br>
617<br>
618 <form action="products_move.php" method="POST">
619 <select name="new_location_id" required>
620 <option value="">Selecteer een locatie</option>
621 <?php
622 if($other_locations->num_rows > 0) {
623 while($row = $other_locations->fetch_assoc()) {
624 echo "<option value='". $row['id'] ."'>".$row['name']."</option>";
625 }
626 }
627 ?>
628 </select><br><br>
629 <input type="number" name="quantity" placeholder="Hoeveel?" required>
630 <input type="hidden" name="location_id" value="<?php echo $location_id; ?>">
631 <input type="hidden" name="product_id" value="<?php echo $product_id; ?>">
632
633 <button type="submit">Verplaats product</button>
634 </form>
635
636<?php } ?>
637
638
639<!--
640 END products_move.php END
641-->
642
643<!--
644 products.php
645-->
646
647<?php
648require_once 'database_connection.php';
649
650require_once 'login_include.php';
651
652
653$sql = "SELECT * FROM locations ORDER BY `name` ASC";
654$locations = $db->query($sql);
655
656
657$sql = "
658 SELECT p_l.quantity AS quantity,
659 p.id AS product_id,
660 p.name AS product_name,
661 p.price AS price,
662 l.name AS location_name,
663 l.id AS location_id
664 FROM product_locations AS p_l
665 LEFT JOIN products AS p ON p.id = p_l.product_id
666 LEFT JOIN locations AS l ON l.id = p_l.location_id";
667
668$products = $db->query($sql);
669
670?>
671
672<!DOCTYPE html>
673<html>
674<head>
675 <meta charset="utf-8">
676 <title>Tools4Ever</title>
677</head>
678<body>
679<a href="index.php">Home</a><br>
680<br>
681<form action="products_add.php" method="POST">
682 <input type="text" name="name" placeholder="Naam" required><br>
683 <input type="number" step="0.01" name="price" placeholder="Prijs" required><br>
684 <input type="number" name="quantity" placeholder="Quantity" required><br>
685
686 <select name="location" required>
687 <option value="">Selecteer een locatie</option>
688 <?php
689 if($locations->num_rows > 0) {
690 while($row = $locations->fetch_assoc()) {
691 echo "<option value='". $row['id'] ."'>".$row['name']."</option>";
692 }
693 }
694 ?>
695 </select><br><br>
696
697 <button type="submit">Voeg product toe</button>
698</form>
699<br>
700<table style="text-align: left">
701 <thead>
702 <th>id</th>
703 <th>naam</th>
704 <th>prijs</th>
705 <th>quantity</th>
706 <th>totaal prijs</th>
707 <th>location</th>
708 <th>verplaatsen</th>
709 </thead>
710 <tbody>
711 <?php
712 if($products->num_rows > 0) {
713 while($row = $products->fetch_assoc()) {
714 ?>
715 <tr>
716 <td><?php echo $row['product_id']; ?></td>
717 <td><?php echo $row['product_name']; ?></td>
718 <td>€<?php echo number_format($row['price'],2, ',', '.');?></td>
719 <td><?php echo $row['quantity'];?></td>
720 <td>€<?php echo number_format($row['price'] * $row['quantity'],2, ',', '.');?></td>
721 <td><?php echo $row['location_name'];?></td>
722 <td><a href="products_move.php?product_id=<?php echo $row['product_id']; ?>&location_id=<?php echo $row['location_id']; ?>">Verplaats</a></td>
723 </tr>
724 <?php
725 }
726 }
727 ?>
728 </tbody>
729</table>
730
731</body>
732</html>
733
734<!--
735 END products.php END
736-->
737
738<!--
739 toolsforever.sql
740-->
741
742-- --------------------------------------------------------
743-- Host: 127.0.0.1
744-- Server version: 10.1.21-MariaDB - mariadb.org binary distribution
745-- Server OS: Win32
746-- HeidiSQL Version: 9.4.0.5125
747-- --------------------------------------------------------
748
749/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
750/*!40101 SET NAMES utf8 */;
751/*!50503 SET NAMES utf8mb4 */;
752/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
753/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
754
755
756-- Dumping database structure for toolsforever
757CREATE DATABASE IF NOT EXISTS `toolsforever` /*!40100 DEFAULT CHARACTER SET latin1 */;
758USE `toolsforever`;
759
760-- Dumping structure for table toolsforever.locations
761CREATE TABLE IF NOT EXISTS `locations` (
762 `id` int(11) NOT NULL AUTO_INCREMENT,
763 `name` varchar(50) DEFAULT NULL,
764 PRIMARY KEY (`id`)
765) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;
766
767-- Dumping data for table toolsforever.locations: ~3 rows (approximately)
768DELETE FROM `locations`;
769/*!40000 ALTER TABLE `locations` DISABLE KEYS */;
770/*!40000 ALTER TABLE `locations` ENABLE KEYS */;
771
772-- Dumping structure for table toolsforever.products
773CREATE TABLE IF NOT EXISTS `products` (
774 `id` int(11) NOT NULL AUTO_INCREMENT,
775 `name` varchar(50) DEFAULT NULL,
776 `price` double DEFAULT NULL,
777 PRIMARY KEY (`id`)
778) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;
779
780-- Dumping data for table toolsforever.products: ~2 rows (approximately)
781DELETE FROM `products`;
782/*!40000 ALTER TABLE `products` DISABLE KEYS */;
783/*!40000 ALTER TABLE `products` ENABLE KEYS */;
784
785-- Dumping structure for table toolsforever.product_locations
786CREATE TABLE IF NOT EXISTS `product_locations` (
787 `product_id` int(11) NOT NULL,
788 `location_id` int(11) NOT NULL,
789 `quantity` int(11) DEFAULT NULL,
790 KEY `product_id` (`product_id`),
791 KEY `location_id` (`location_id`),
792 CONSTRAINT `product_locations_ibfk_1` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`),
793 CONSTRAINT `product_locations_ibfk_2` FOREIGN KEY (`location_id`) REFERENCES `locations` (`id`)
794) ENGINE=InnoDB DEFAULT CHARSET=latin1;
795
796-- Dumping data for table toolsforever.product_locations: ~3 rows (approximately)
797DELETE FROM `product_locations`;
798/*!40000 ALTER TABLE `product_locations` DISABLE KEYS */;
799/*!40000 ALTER TABLE `product_locations` ENABLE KEYS */;
800
801-- Dumping structure for table toolsforever.users
802CREATE TABLE IF NOT EXISTS `users` (
803 `id` int(11) NOT NULL AUTO_INCREMENT,
804 `username` varchar(50) DEFAULT NULL,
805 `password` varchar(50) DEFAULT NULL,
806 PRIMARY KEY (`id`)
807) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
808
809-- Dumping data for table toolsforever.users: ~1 rows (approximately)
810DELETE FROM `users`;
811/*!40000 ALTER TABLE `users` DISABLE KEYS */;
812INSERT INTO `users` (`id`, `username`, `password`) VALUES
813 (1, 'admin', '21232f297a57a5a743894a0e4a801fc3');
814/*!40000 ALTER TABLE `users` ENABLE KEYS */;
815
816/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
817/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
818/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
819
820<!--
821 END toolsforever.sql END
822-->