File: /var/www/vhosts/creativefellows.nl/jhtaxatie.creativefellows.nl/classes/AdminController.php
<?php
class AdminController extends TaxationController
{
var $userFields = array(
array("username", "Gebruikersnaam", "input", "voer gebruikersnaam in"),
array("full_name", "Volledige naam", "input", "voer naam in"),
array("email", "E-mailadres", "input", "voer e-mail in"),
array("hash", "Wachtwoord", "password", "voer wachtwoord in",null,null,null,false),
array("hash_repeat","Herhaal wachtwoord","password","herhaal wachtwoord",null,null,null,false),
array("role", "Rol", "select", "kies rol", null, array(1=> "Verkoop", 3 => "GG medewerker",2 => "Administrator",4 => "Afdeling")),
array("district", "District", "select", "kies district", null, array(0=>"-- District --",1=> "NoordWest - 1",2=> "NoordOost - 2", 3 => "ZuidOost - 3",4 => "Zuid West - 4", 5 => "InSa - 5",6 => "KAM - 6")),
//array("company", "Bedrijfsnaam", "input", "voer bedrijfsnaam in",null,null,null,false),
//array("address", "Adres", "input", "voer adres in",null,null,null,false),
//array("zip", "Postcode", "input", "voer postcode in",null,null,null,false),
//array("city", "Plaats", "input", "voer plaats in",null,null,null,false),
//array("phone", "Telefoonummer", "input", "voer telefoonummer in",null,null,null,false),
);
var $exportFields = array(
//array("start", "Datum vanaf", "input", "selecteer datum", null, date("d-m-Y"),"calendar"),
array("start", "Datum vanaf", "input", "selecteer datum", null,null,"calendar"),
array("end", "Datum tot", "input", "selecteer datum", null,null,"calendar"),
array("filter", "Verfijn op", "select", "kies afdeling", null,array(0=>"- Selecteer -","afdeling" => "Afdeling", "gebruiker" => "Gebruiker", "kolom||waarde||operator" => "Kolom")),
array("role", "Afdeling", "select", "kies afdeling", "small-12 hidden afdeling", array(),true),
array("user", "Gebruiker", "select", "kies gebruiker", "small-12 hidden gebruiker", array()),
array("column", "Kolom", "select", "Filter op kolom", "small-12 hidden kolom", array()),
array("operator","operator", "select", "selecteer operator","small-12 hidden kolom", array("LIKE" => "=", ">=" => ">=", "<=" => "<=")),
array("value", "Waarde", "input", "voer zoekwaarde in","small-12 hidden waarde",null,null,false)
);
var $roleFields = array(
array("name", "Rol", "input", "voer naam rol in"),
array("routes", "Rechten", "checkbox", "Selecteer rechten",null,array()),
array("readonly", "Wijzigingen aanbrengen", "select", "Selecteer rechten",null,array(0=>"ja", 1=>"nee"))
);
var $labelFields = array(
array("delivery_5star", "Levertijd 5 sterren", "input", "voer levertijd in", "value" => "in overleg"),
array("delivery_4star", "Levertijd 4 sterren", "input", "voer levertijd in", "value" => "8-10 weken"),
array("delivery_3star", "Levertijd 3 sterren", "input", "voer levertijd in", "value" => "6-8 weken"),
);
var $adminPages = array(
array("users","Gebruikers"),
array("roles","Rollen"),
array("stats","Statistiek"),
array("password","Wachtwoord reset"),
array("labels","Labels"),
);
function __construct($db_connection,$router,$user_id,$view=null,$settings=null)
{
$this->db = $db_connection;
$this->router = $router;
$this->view = $view;
$this->user_id = $user_id;
$this->settings = $settings;
// Users route
$this->users_route = $this->router->pathFor('admin.users');
// Roles route
$this->roles_route = $this->router->pathFor('admin.roles');
// Roles route
$this->labels_route = $this->router->pathFor('admin.labels');
// Roles route
$this->admin_dash_route = $this->router->pathFor('admin.dashboard');
}
/*
* Get all admin pages
*/
public function getPageOptions($request,$response,$args)
{
return $this->view->render($response, 'admin/admin.dashboard.php',array("router" => $this->router, "pages" => $this->adminPages));
}
/*
* View Roles
*/
public function getRoles($request,$response,$args)
{
$roles = $this->getActiveRoles();
return $this->view->render($response, 'admin/admin.roles.php', array("router" => $this->router, "roles" => $roles, "fields"=> $this->getRoleFields()) );
}
public function getActiveRoles()
{
$sql = 'SELECT * FROM user_roles WHERE active = 1 ORDER BY role_id';
$sth = $this->db->prepare($sql);
$sth->execute();
$roles = [];
while($row = $sth->fetch())
{
$row["acronym"] = $this->getAcronym( $row["name"] );
$roles[] = $row;
}
return $roles;
}
/*
* Add new role
*/
public function newRole($request,$response,$args)
{
// Get form data
$post = $request->getParsedBody();
// Insert in db
$sth = $this->db->prepare("INSERT INTO user_roles(name,readonly) VALUES(:name,:readonly)");
$sth->execute(array(
"name" => $post["name"],
"readonly" => isset($post["readonly"]) ? 1 : 0
));
// Get role id
$role_id = $this->db->lastInsertId();
// Insert in db
$sth = $this->db->prepare("INSERT INTO role_permissions(role_id,route_id) VALUES (:role_id,:route_id)");
foreach($post["routes"] as $route_id){
$sth->execute(array(
"role_id" => $role_id,
"route_id" => $route_id
));
}
// return to admin
return $response->withRedirect($this->roles_route);
}
public function getRole($request,$response,$args)
{
$role = $this->getRoleById($args["id"]);
$role["routes"] = $this->getRoleRoute($args["id"]);
return $this->view->render($response, 'admin/admin.view_role.php', array("router" => $this->router, "role" => $role, "fields"=> $this->getRoleFields() ));
}
public function getRoleRoute($role_id)
{
$sql = 'SELECT * FROM role_permissions WHERE role_id = :role_id ORDER BY route_id';
$sth = $this->db->prepare($sql);
$sth->execute(array(
"role_id" => $role_id
));
$role_ids = [];
while($row = $sth->fetch())
{
$role_ids[] = $row["route_id"];
}
return $role_ids;
}
public function getRoleById($role_id)
{
//$sql = 'SELECT * FROM user_roles LEFT JOIN role_permissions ON user_roles.role_id = role_permissions.role_id WHERE user_roles.role_id = :role_id ORDER BY route_id';
$sql = 'SELECT * FROM user_roles WHERE role_id = :role_id';
$sth = $this->db->prepare($sql);
$sth->execute(array(
"role_id" => $role_id
));
return $sth->fetch();
}
public function deleteRole($request, $response, $args)
{
$sql = 'UPDATE user_roles SET active = 0 WHERE role_id = :role_id';
$sth = $this->db->prepare($sql);
$sth->execute(["role_id" => $args["id"]]);
return $response->withRedirect($this->roles_route);
}
public function patchRole($request, $response, $args)
{
// get form data
$post = $request->getParsedBody();
// update role data
$sth = $this->db->prepare(
"UPDATE user_roles
SET name = :name, readonly = :readonly
WHERE role_id = :role_id"
);
$sth->execute(
array(
"name" => $post["name"],
"readonly" => $post["readonly"],
"role_id" => $args["id"]
)
);
// delete current permissions
$sth = $this->db->prepare("DELETE FROM role_permissions WHERE role_id = :role_id");
$sth->execute(array(
"role_id" => $args["id"]
));
// insert new permissions
$sth = $this->db->prepare("INSERT INTO role_permissions(role_id,route_id) VALUES (:role_id,:route_id)");
//d($post["routes"]);
//die($args["id"]);
foreach($post["routes"] as $route_id){
$sth->execute(array(
"role_id" => $args["id"],
"route_id" => $route_id
));
}
return $response->withRedirect($this->router->pathFor('admin.viewrole',["id"=>$args["id"]]) );
}
/*
* View labels
*/
public function getLabels($request,$response,$args)
{
$labels = $this->getActiveLabels();
return $this->view->render($response, 'admin/admin.labels.php', array("router" => $this->router, "labels" => $labels, "fields"=> $this->getLabelFields()) );
}
public function getActiveLabels()
{
$sql = 'SELECT * FROM app_labels';
$sth = $this->db->prepare($sql);
$sth->execute();
return $sth->fetch();
}
public function getRoleFields()
{
$elements = [];
foreach( $this->roleFields as $groupName => $field)
{
if($field[0] == "routes") $field[5] = $this->getActiveRoutes();
$elements[] = new HtmlElement($field);
}
return $elements;
}
public function getLabelFields()
{
$elements = [];
foreach( $this->labelFields as $groupName => $field)
{
$elements[] = new HtmlElement($field);
}
return $elements;
}
public function patchLabels($request, $response, $args)
{
// get form data
$post = $request->getParsedBody();
// update regular data
$sth = $this->db->prepare(
"UPDATE app_labels
SET
delivery_5star = :delivery_5star,
delivery_4star = :delivery_4star,
delivery_3star = :delivery_3star
WHERE id = :id"
);
$sth->execute([
"delivery_5star" => $post["delivery_5star"],
"delivery_4star" => $post["delivery_4star"],
"delivery_3star" => $post["delivery_3star"],
"id" => 1
]);
return $response->withRedirect($this->labels_route);
}
/*
* Get routes assigned to role
*/
private function getActiveRoutes()
{
$sql = 'SELECT * FROM app_routes WHERE active= 1 AND static = 0';
$sth = $this->db->prepare($sql);
$routes = $sth->execute();
$roles = [];
while($row = $sth->fetch())
{
$roles[$row["route_id"]] = $row["name"];
}
return $roles;
}
/*
* Get all users
*/
public function getUsers($request,$response)
{
$sql = 'SELECT * FROM users WHERE status = 1 ORDER BY user_id';
$sth = $this->db->prepare($sql);
$taxations = $sth->execute();
$users = [];
while($row = $sth->fetch())
{
$row["acronym"] = $this->getAcronym( $row["full_name"] );
$users[] = new JhUser( $row );
}
return $this->view->render($response, 'admin/admin.users.php',array("router" => $this->router, "users" => $users,"fields" => $this->getUserFields() ));
}
/*
* View an user
*/
public function getUser($request,$response,$args)
{
$admin_login = $_SESSION['role'] == 14 ? true : false;
$user = $this->getUserById($args["id"]);
return $this->view->render($response, 'admin/admin.view_user.php', array("router" => $this->router, "user" => $user, "fields" => $this->getUserFields(),"admin" => $admin_login ));
}
/*
* get user by id
*/
public function getUserById($user_id)
{
$sql = 'SELECT * FROM users WHERE user_id = :user_id';
$sth = $this->db->prepare($sql);
$user = $sth->execute(["user_id" => $user_id]);
if($user)
{
return new JhUser( $sth->fetch() );
}
}
/*
* Get user fields
*/
public function getUserFields()
{
$elements = [];
foreach( $this->userFields as $groupName => $field)
{
if($field[0] == "role"){
$roles = [];
foreach($this->getActiveRoles() as $role){
$roles[$role["role_id"]] = $role["name"];
}
$field[5] = $roles;
}
$elements[] = new HtmlElement($field);
}
return $elements;
}
/*
* Add new user
*/
public function newUser($request, $response)
{
// get form data
$post = $request->getParsedBody();
$sth = $this->db->prepare(
"INSERT INTO users(
role,
district,
username,
full_name,
hash,
email,
password_valid_until
)
VALUES(
:role,
:district,
:username,
:full_name,
:password,
:email,
:valid_until
)
");
$sth->execute([
"role" => $post["role"],
"district" => $post["district"],
"username" => $post["username"],
"full_name" => $post["full_name"],
"password" => password_hash($post["hash"],PASSWORD_BCRYPT),
"email" => $post["email"],
"valid_until" => date('Y-m-d', strtotime("+3 months", strtotime(date("Y-m-d")))),
]);
// return to admin
return $response->withRedirect($this->users_route);
}
/*
* Get new user form
*/
public function getUserForm($request, $response)
{
$user_fields = $this->getUserFields();
return $this->view->render($response, 'admin/admin.add_user.php',array("fields" => $user_fields));
}
/*
* Update user
*/
public function patchUser($request, $response, $args)
{
// get form data
$post = $request->getParsedBody();
// check if password are set and match
$update_pw = isset($post["hash"]) && $post["hash"] != "" && $post["hash"] === $post["hash_repeat"] ? true : false;
// update regular data
$sth = $this->db->prepare(
"UPDATE users
SET
role = :role,
district = :district,
username = :username,
full_name = :full_name,
email = :email
WHERE user_id = :user_id"
);
$sth->execute([
"role" => $post["role"],
"district" => $post["district"],
"username" => $post["username"],
"full_name" => $post["full_name"],
"email" => $post["email"],
"user_id" => $args["id"]
]);
// update password
if($update_pw == true)
{
$sth = $this->db->prepare(
"UPDATE users SET hash = :hash WHERE user_id = :user_id"
);
$sth->execute(
array(
"hash" => password_hash($post["hash"],PASSWORD_BCRYPT),
"user_id" => $args["id"]
)
);
}
return $response->withRedirect($this->users_route);
}
/*
* Delete user
*/
public function deleteUser($request, $response, $args)
{
$sql = 'UPDATE users SET status = 0 WHERE user_id = :user_id';
$sth = $this->db->prepare($sql);
$sth->execute(["user_id" => $args["id"]]);
// Admin roles path
$users_route = $this->router->pathFor('admin.users');
return $response->withRedirect($this->users_route);
}
public function getPasswordOptions($request, $response, $args)
{
return $this->view->render($response, 'admin/admin.password_reset.php',array("router" => $this->router));
}
public function resetPasswords($request, $response, $args)
{
$sql = 'UPDATE users SET password_valid_until = :newdate';
$sth = $this->db->prepare($sql);
$sth->execute(["newdate" => date("Y-m-d", strtotime("yesterday"))]);
return $response->withRedirect($this->admin_dash_route);
}
public function getStats($request, $response, $args)
{
$params = $request->getQueryParams();
$start = $params["start"];
$end = $params["end"];
$result = isset($params["r"]) ? $params["r"] : null;
$export_fields = $this->getExportFields();
return $this->view->render($response, 'admin/admin.statistics.php',array("router" => $this->router, "fields" => $export_fields, "result" => $result, "start" => $start, "end" => $end));
}
private function getExportFields()
{
$elements = [];
foreach( $this->exportFields as $groupName => $field)
{
switch($field[0])
{
case "role":
$field[5] = $this->getUsersByRole(2);
break;
case "user":
$field[5] = $this->getActiveUsers();
break;
case "column":
$field[5] = $this->getColumnSort();
break;
}
$elements[] = new HtmlElement($field);
}
return $elements;
}
private function getUsersByRole($role=1)
{
$sql = 'SELECT * FROM users WHERE role = :role AND status = 1 ORDER BY username';
$sth = $this->db->prepare($sql);
$sth->execute(["role" => $role]);
$users = [];
$users[0] = "- Selecteer -";
while($row = $sth->fetch())
{
$users[ $row["user_id"] ] = $row["username"]." (". $row["full_name"] .")";
}
return $users;
}
private function getActiveUsers()
{
$sql = 'SELECT * FROM users WHERE status = 1 ORDER BY username';
$sth = $this->db->prepare($sql);
$taxations = $sth->execute();
$users = [];
$users[0] = "- Selecteer -";
while($row = $sth->fetch())
{
$users[ $row["user_id"] ] = $row["full_name"]." (". $row["username"] .")";
}
return $users;
}
private function getColumnSort()
{
$table_fields = $this->settings["taxation_fields"];
$hidden_fields = array("status","adviseur","cond_remarks","gg_remarks","images","date_report");
$fields = [];
$fields[0] = "- Selecteer -";
foreach($table_fields as $group => $arr_values){
foreach($arr_values as $option)
{
if(in_array($option[0],$hidden_fields)) continue;
$fields[ $option[0] ] = $option[1];
}
}
$fields["max_taxation_value"] = "Waardebepaling";
return $fields;
}
public function getExport($request,$response,$args)
{
// Get form data
$post = $request->getParsedBody();
$start = date("Y-m-d",strtotime($post["start"]));
$end = date("Y-m-d",strtotime($post["end"]));
$filter = $post["filter"];
$select_columns = 'taxation_id, user_id, gg_id, company, branche, truck_count, truck_brand, city, date_report, advisor, truck_type, truck_serial, truck_year, truck_capacity, truck_hours, truck_drive, truck_masttype, truck_vorklengte, truck_h1, truck_h3, truck_sideshift, truck_mug, truck_battery, truck_battery_build, cond_global, cond_hascharger, cond_drive, cond_lift, cond_cabin, cond_chair, cond_break, cond_dashboard, cond_tires, cond_power_stear, cond_dents, cond_remarks, taxation_value, gg_remarks, date, status';
switch($filter)
{
case "afdeling":
$sql = "SELECT
$select_columns
FROM taxations
WHERE (date BETWEEN :start AND :end)
AND gg_id = :gg_id";
$sth = $this->db->prepare($sql);
$sth->execute(["start" => $start,"end" => $end, "gg_id" => $post["role"]]);
break;
case "gebruiker":
$sql = "SELECT
$select_columns
FROM taxations
WHERE (date BETWEEN :start AND :end)
AND user_id = :user_id";
//echo str_replace(array(":start",":end",":user_id"),array($start,$end,$post["user"]),$sql);
//die();
$sth = $this->db->prepare($sql);
$sth->execute(["start" => $start,"end" => $end, "user_id" => $post["user"]]);
break;
case "kolom||waarde||operator":
$sql = "SELECT
$select_columns
FROM taxations
WHERE (date BETWEEN :start AND :end)
AND ". $post["column"]." ". $post["operator"]." :value";
$sth = $this->db->prepare($sql);
$sth->execute(["start" => $start,"end" => $end, "value" => $post["value"]]);
break;
default:
//die(date("Y-m-d",strtotime($start)));
$sql = "SELECT
$select_columns
FROM taxations
WHERE (date BETWEEN :start AND :end)";
$sth = $this->db->prepare($sql);
$sth->execute(["start" => $start, "end" => $end]);
break;
}
if( $sth->rowCount() > 0 )
{
// convert database to csv file
$csv = $this->str_putcsv($sth->fetchAll());
// set the response headers
$res = $response->withHeader('Content-Type','text/csv')
->withHeader('Content-Description', 'File Transfer')
->withHeader('Content-Type', 'application/force-download')
->withHeader('Content-Type', 'application/download')
->withHeader('Content-Disposition', 'attachment;filename="export.csv"')
->withHeader('Expires', '0')
->withHeader('Pragma', 'no-cache');
echo $csv;
return $res;
}
else{
return $response->withRedirect( $this->router->pathFor('admin.stats')."?r=0&start=". $post["start"] ."&end=".$post["end"] );
}
}
/**
* Convert a multi-dimensional, associative array to CSV data
* @param array $data the array of data
* @return string CSV text
*/
function str_putcsv($data) {
# Generate CSV data from array
$fh = fopen('php://temp', 'rw'); # don't create a file, attempt
# to use memory instead
# write out the headers
fputcsv($fh, array_keys(current($data)));
# write out the data
foreach ( $data as $row ) {
$arr = [];
foreach($row as $key => $value){
$arr[$key] = str_replace(array("\r\n", "\n\r", "\n", "\r"), ',', $value);
}
fputcsv($fh, $arr);
}
rewind($fh);
$csv = stream_get_contents($fh);
fclose($fh);
return $csv;
}
}
?>