CREATE TABLE users (
user_id int NOT NULL AUTO_INCREMENT,
user_name varchar(50) NOT NULL,
password varchar(50) NOT NULL,
role varchar(50) NOT NULL,
PRIMARY KEY (user_id),
INDEX (user_name),
INDEX (user_id)
);
CREATE TABLE records (
record_id int NOT NULL AUTO_INCREMENT,
user_name varchar(50),
user_id int,
work_type varchar(100),
time_start datetime,
time_end datetime,
record_date datetime,
approved boolean DEFAULT 0,
PRIMARY KEY (record_id),
FOREIGN KEY (user_id) REFERENCES users(user_id),
FOREIGN KEY (user_name) REFERENCES users(user_name)
);
INSERT INTO users(user_name,password,role)
VALUES ('Orlandson Asturiano','password123','employee');
INSERT INTO users(user_name,password,role)
VALUES ('Bob Billy','password12356','admin');
SELECT * FROM users;
INSERT INTO records (user_name, user_id, work_type, time_start, time_end, record_date, approved)
VALUES(
(SELECT user_name FROM users WHERE user_id = 1),
(SELECT user_id FROM users WHERE user_id = 1),
'Programming',
'2023-05-23 08:30:00',
'2023-05-23 14:30:00',
'2023-05-23',
false);
INSERT INTO records(user_name, user_id, work_type, time_start, time_end, record_date, approved)
VALUES(
(SELECT user_name FROM users WHERE user_id = 1),
(SELECT user_id FROM users WHERE user_id = 1),
'Testing',
'2023-05-24 010:30:00',
'2023-05-24 16:30:00',
'2023-05-24',
true);
SELECT * FROM records;
<?php
use Carbon\Carbon;
/**/
session_start();
//Login Info
$username="Orlandson Asturiano";
$password="password123";
$now = Carbon::now()->format('d/m/Y');
$employee_id = 1;//Represents
$record_id = 1;//Represents selected record
//Create record form data
$fuserName="Orlandson Asturiano";
$fuserId=1;
$fworkType="Final Draft";
$ftimeStart="2023-05-25 08:30:00";
$ftimeEnd="2023-05-25 07:30:00";
$frecordDate="2023-05-25";
$fapproved=false;
//Additional Test Data
$fworkType2="Documentation";
//Edited record form data
$fuserName3="Orlandson Asturiano";
$fuserId3=1;
$fworkType3="Documentation Draft";
$ftimeStart3="2023-05-26 08:30:00";
$ftimeEnd3="2023-05-26 07:30:00";
$frecordDate3="2023-05-26";
$fapproved3=false;
/*LOGIN*/
$sql = "SELECT * FROM users WHERE user_name = '{$username}' AND password = '{$password}'";
$result = $mysqli->query($sql);
if (mysqli_num_rows($result)!=0) {
while($row = $result->fetch_assoc()) {
//var_dump($row);
//printf($row["role"]);
$_SESSION["user_name"] = $row["user_name"];
$_SESSION["user_id"] = $row["user_id"];
$_SESSION["password"] = $row["password"];
$_SESSION["role"] = $row["role"];
$_SESSION["auth"] = true;
printf("Logged In!\n");
if($_SESSION["role"] == "admin"){
//TODO redirect to admin view
}else{
//TODO redirect to employee view
}
}
}else{
//Wront login
printf("Credentials Incorrect!\n");
}
/*Employee Create Records*/
$sql = "INSERT INTO records (user_name, user_id, work_type, time_start, time_end, record_date)
VALUES(
(SELECT user_name FROM users WHERE user_id = {$_SESSION["user_id"]}),
(SELECT user_id FROM users WHERE user_id = {$_SESSION["user_id"]}),
'{$fworkType}',
'{$ftimeStart}',
'{$ftimeEnd}',
'{$frecordDate}')";
$result = $mysqli->query($sql);
/*This is a test record*/
$sql = "INSERT INTO records (user_name, user_id, work_type, time_start, time_end, record_date)
VALUES(
(SELECT user_name FROM users WHERE user_id = {$_SESSION["user_id"]}),
(SELECT user_id FROM users WHERE user_id = {$_SESSION["user_id"]}),
'{$fworkType2}',
'{$ftimeStart}',
'{$ftimeEnd}',
'{$frecordDate}')";
$result = $mysqli->query($sql);
/*Employee Edit Records*/
$sql = "INSERT INTO records (user_name, user_id, work_type, time_start, time_end, record_date)
VALUES(
(SELECT user_name FROM users WHERE user_id = {$_SESSION["user_id"]}),
(SELECT user_id FROM users WHERE user_id = {$_SESSION["user_id"]}),
'{$fworkType3}',
'{$ftimeStart3}',
'{$ftimeEnd3}',
'{$frecordDate3}')";
$result = $mysqli->query($sql);
printf("\nShow all Records-------------------------------------\n");
printf("Today is %s\nCurrent PHP versi (on: %s \n\n", $now, phpversion());
//Testing
$sql = "SELECT * FROM records";
$result = $mysqli->query($sql);
$records = $result->fetch_object();
while($row = $result->fetch_assoc()) {
var_dump($records);
}
printf("\nEmployee Get Records-------------------------------------\n");
//Employee get unapproved records
$sql = "SELECT * FROM records WHERE user_id = {$_SESSION["user_id"]} AND approved = FALSE";
$result = $mysqli->query($sql);
while($row = $result->fetch_assoc()) {
var_dump($row);
}
printf("\nAdmin Get All Unapproved Records-------------------------------------\n");
//Admin get unapproved records
$sql = "SELECT * FROM records WHERE approved = FALSE";
$result = $mysqli->query($sql);
while($row = $result->fetch_assoc()) {
var_dump($row);
}
printf("\nAdmin Get All Records-------------------------------------\n");
//Admin get unapproved records
$sql = "SELECT * FROM records";
$result = $mysqli->query($sql);
while($row = $result->fetch_assoc()) {
var_dump($row);
}
printf("\nDelete Employee Records-------------------------------------\n");
//Employee delete records
$sql = "DELETE FROM records WHERE record_id = {$record_id} AND user_id = {$employee_id}";
$result = $mysqli->query($sql);
$sql = "SELECT * FROM records";
$result = $mysqli->query($sql);
while($row = $result->fetch_assoc()) {
var_dump($row);
}
printf("\nAdmin Delete Employee Records-------------------------------------\n");
//Admin Delete record
$sql = "DELETE FROM records WHERE record_id = {$record_id}";
$result = $mysqli->query($sql);
$sql = "SELECT * FROM records";
$result = $mysqli->query($sql);
while($row = $result->fetch_assoc()) {
var_dump($row);
}