From 4cee97e695c889445c3146bc8169a89b132855ea Mon Sep 17 00:00:00 2001
From: Aqua-sama <aqua@iserlohn-fortress.net>
Date: Thu, 29 Apr 2021 18:04:56 +0300
Subject: Finish user management

---
 etc/setup.sql         | 28 +++++++++++++++++++++++
 index.php             | 62 ++++++++++++++++++++++++++++++++++++++++++---------
 library/functions.php |  7 +++++-
 readme.md             | 37 ++++++++++++++++++++++++++++++
 user/delete.php       | 25 +++++++++++++++++++++
 user/index.php        |  6 +++++
 6 files changed, 153 insertions(+), 12 deletions(-)
 create mode 100644 etc/setup.sql
 create mode 100644 readme.md
 create mode 100644 user/delete.php

diff --git a/etc/setup.sql b/etc/setup.sql
new file mode 100644
index 0000000..35ddea4
--- /dev/null
+++ b/etc/setup.sql
@@ -0,0 +1,28 @@
+-- users table
+CREATE TABLE users (
+id SERIAL PRIMARY KEY,
+username varchar(50) NOT NULL,
+password varchar(255) NOT NULL,
+email varchar(50) NOT NULL,
+can_edit_bugs boolean DEFAULT true,
+can_edit_reports boolean DEFAULT true
+);
+
+-- bugs table
+CREATE TABLE bugs (
+id SERIAL PRIMARY KEY,
+title varchar(50) NOT NULL,
+description text NOT NULL,
+author integer NOT NULL REFERENCES users(id),
+assignee integer REFERENCES users(id)
+);
+
+-- reports table
+CREATE TABLE reports (
+id SERIAL PRIMARY KEY,
+bug integer REFERENCES bugs(id),
+author integer NOT NULL REFERENCES users(id),
+title varchar(50) NOT NULL,
+description text NOT NULL
+);
+
diff --git a/index.php b/index.php
index 5e79ca7..f63f3af 100644
--- a/index.php
+++ b/index.php
@@ -6,22 +6,62 @@ require_once(LIBRARY_PATH . "/functions.php");
 
 $conn = new PDO($config['db']['dsn'], $config['db']['username'], $config['db']['password']);
 
-$query = 'SELECT bugs.id AS id, title, description, users.username AS submitter FROM bugs JOIN users ON bugs.author = users.id';
+?>
+<h2>Bugs</h2>
+
+<?php
+# bugs query
+$bugs_q= 'SELECT bugs.id AS id, title, description, users.username AS submitter FROM bugs JOIN users ON bugs.author = users.id';
 if(isset($_GET['term']) && $_GET['term'] != "") {
     echo "where the title contains: $_GET[term]";
-    $query = $query . " WHERE title LIKE '%$_GET[term]%'";
+    $bugs_q = $bugs_q . " WHERE title LIKE '%$_GET[term]%'";
+}
+
+$bugs_r = $conn->query($bugs_q);
+if($bugs_r->rowCount() > 0) {
+?>
+<table style='width:80%'>
+<tr><th>Title</th><th>Description</th><th>Owner</th></tr>
+<?php
+foreach ($bugs_r as $row) {
+    echo "<tr><td><a href=view.php?id=$row[id]>$row[title]</a></td> <td>" . truncate($row['description']) . "</td> <td>$row[submitter]</td></tr>\n";
+}
+?>
+</table>
+
+<?php
+} else {
+    echo "<p>Zero boogs found.</p>\n";
+}
+?>
+
+<h2>Reports</h2>
+<?php
+
+if(session_set()) {
+    echo "<p><a href='{$config['urls']['base']}/reports/edit.php'>Create</a></p>\n";
+}
+
+# reports query
+$reports_q = 'SELECT title, description, author FROM reports WHERE bug IS NULL';
+
+$reports_r = $conn->query($reports_q);
+if($reports_r->rowCount() > 0) {
+?>
+
+<table style='width:80%'>
+<tr><th>Title</th><th>Description</th><th>Submitted by</th></tr>
+<?php
+foreach ($reports_r as $row) {
+    echo "<tr><td><a href=view.php?id=$row[id]>$row[title]</a></td> <td>" . truncate($row['description']) . "</td> <td>$row[submitter]</td></tr>\n";
 }
+?>
+</table>
 
-echo "<table style='width:80%'>\n";
-echo "<tr><th>Title</th><th>Description</th><th>Submitter</th></tr>\n";
-foreach ($conn->query($query) as $row) {
-    echo "<tr>\n";
-    echo "<td><a href=view.php?id=$row[id]>$row[title]</a></td>\n";
-    echo "<td>" . truncate($row['description']) . "</td>\n";
-    echo "<td>$row[submitter]</td>\n";
-    echo "</tr>\n";
+<?php
+} else {
+    echo "<p>No unassigned reports.</p>\n";
 }
-echo "</table>\n";
 
 require_once(TEMPLATES_PATH . "/footer.php");
 ?>
diff --git a/library/functions.php b/library/functions.php
index ee3a0d0..9ea37b1 100644
--- a/library/functions.php
+++ b/library/functions.php
@@ -1,5 +1,4 @@
 <?php
-session_start();
 
 function truncate($text, $chars = 25) {
     if (strlen($text) <= $chars) {
@@ -11,5 +10,11 @@ function truncate($text, $chars = 25) {
     $text = $text."...";
     return $text;
 }
+
+function session_set() {
+    if(isset($_SESSION['user_id']) && $_SESSION['user_id'] != "") {
+        return true;
+    }
+}
 ?>
 
diff --git a/readme.md b/readme.md
new file mode 100644
index 0000000..1da26fc
--- /dev/null
+++ b/readme.md
@@ -0,0 +1,37 @@
+## What is this?
+A simple bugtracker written in php.
+
+- Code uses PDO, so it should be able to use multiple db backends;
+- all tables given as postgresql tables, all queries tests on postgresql
+
+## Usage
+- See etc/ for sample config files
+- See etc/setup.sql for database setup
+
+## Source code guide
+
+### users
+ action |
+--------|--------
+ create | user/register.php
+ read   | user/index.php
+ update | user/update.php
+ delete | user/delete.php
+ login  | user/login.php
+ logout | user/logout.php
+
+## bugs
+
+### create
+### read
+### update
+### delete
+
+## reports
+
+### create
+### read
+### update
+### delete
+
+## index page
diff --git a/user/delete.php b/user/delete.php
new file mode 100644
index 0000000..b46cdce
--- /dev/null
+++ b/user/delete.php
@@ -0,0 +1,25 @@
+<?php
+require '../config.php';
+require_once(TEMPLATES_PATH . "/header.php");
+require_once(TEMPLATES_PATH . "/panel.php");
+require_once(LIBRARY_PATH . "/functions.php");
+
+if(session_set()) {
+$conn = new PDO($config['db']['dsn'], $config['db']['username'], $config['db']['password']);
+$query = $conn->prepare("DELETE FROM users WHERE id = :id");
+$query->bindParam(':id', $_SESSION['user_id']);
+
+if($query->execute()) {
+    echo '<h2>Account successfully deleted</h2>';
+    unset($_SESSION['user_name']);
+    unset($_SESSION['user_id']);
+} else {
+    echo '<h2>Account deletion failed</h2>';
+}
+}
+
+header("Refresh: 2; URL={$config['urls']['base']}");
+
+require_once(TEMPLATES_PATH . "/footer.php");
+?>
+
diff --git a/user/index.php b/user/index.php
index 90b3a84..dec49ba 100644
--- a/user/index.php
+++ b/user/index.php
@@ -32,6 +32,12 @@ if (isset($_SESSION['user_id']) && $_SESSION['user_id'] != "") {
 </form>
 </div>
 
+<div id='user_delete_account'>
+<form action='<?php echo "{$config['urls']['base']}/user/delete.php"; ?>' method='post'>
+    <input type='submit' value='delete account' >
+</form>
+</div>
+
 <?php
 } else {
     # not logged in
-- 
cgit v1.2.1