File: /var/www/vhosts/creativefellows.nl/jhtaxatie.creativefellows.nl/classes/EnquiryController.php
<?php
/**
*
*/
class EnquiryController extends BaseController
{
function __construct($db_connection,$router,$user_id,$view=null,$user_role=null,$settings=null)
{
$this->db = $db_connection;
$this->router = $router;
$this->view = $view;
$this->user_role = $user_role;
$this->user_id = $user_id;
$this->settings = $settings;
$this->fields = $this->settings['enquiry_fields'];
$this->fields_v2 = $this->settings['enquiry_fields_v2'];
$this->siteUrl = $this->settings['siteUrl'];
// hide status and coordinator for all except admin,
if( !in_array($this->user_role,[2,3,4]) )
{
// $this->fields["Klant"][8][2] = "hidden";
$this->fields["Klant"][9][2] = "hidden";
$this->fields["Klant"][12][2] = "hidden";
$this->fields["Klant"][13][2] = "hidden";
$this->fields["Klant"][14][2] = "hidden";
$this->fields["Klant"][15][2] = "hidden";
$this->fields["Klant"][16][2] = "hidden";
$this->fields["Klant"][9][5] = 1;
}
}
/***********
** Views **
***********/
/*
* Show a users dashboard
*/
public function viewUserEnquiries($request, $response, $args)
{
$routeName = $this->getCurrentRouteName($request);
$params = $request->getQueryParams();
$view_page = isset($params["p"]) ? $params["p"] : 1;
$enquiries = $this->getEnquiries(array(1),$view_page,"ASC");
return $this->view->render($response, 'enquiries/dashboard_enquiries.php', [
"router" => $this->router,
"label" => "Aanvragen",
"enquiries" => $enquiries,
"pagination" => $this->pagination,
"active_page" => $view_page,
"page" => $this->router->pathFor("enquiry.dashboard")
]);
}
public function getTruck($request, $response, $args)
{
$routeName = $this->getCurrentRouteName($request);
$params = $request->getQueryParams();
$view_page = isset($params["p"]) ? $params["p"] : 1;
$enquiries = $this->getEnquiries(array(1),$view_page,"ASC");
return $this->view->render($response, 'enquiries/get_truck.php', [
"router" => $this->router,
"label" => "Aanvragen",
"enquiries" => $enquiries,
"pagination" => $this->pagination,
"active_page" => $view_page,
"page" => $this->router->pathFor("enquiry.dashboard"),
"form" => new FormData($this->fields_v2["Klant"])
]);
}
public function viewStatusEnquiries($request, $response, $args)
{
$params = $request->getQueryParams();
$view_page = isset($params["p"]) ? $params["p"] : 1;
$enquiries = $this->getEnquiries(array(1),$view_page);
return $this->view->render($response, 'enquiries/dashboard_enquiries_status.php', [
"router" => $this->router,
"label" => "Aanvragen status",
"enquiries" => $enquiries,
"pagination" => $this->pagination,
"active_page" => $view_page,
"page" => $this->router->pathFor("status.dashboard")
]);
}
/*
* View archived items
*/
public function viewArchive($request, $response, $args)
{
$params = $request->getQueryParams();
$view_page = isset($params["p"]) ? $params["p"] : 1;
$enquiries = $this->getEnquiries(array(2,3,4),$view_page,"DESC");//,"offer_date");
return $this->view->render($response, 'enquiries/dashboard_enquiries.php', [
"router" => $this->router,
"label" => "Archief",
"enquiries" => $enquiries,
"pagination" => $this->pagination,
"active_page" => $view_page,
"page" => $this->router->pathFor("enquiry.archive.landing")
]);
}
public function viewEnquiry($request, $response, $args)
{
$enquiry = $this->getEnquiryById($args["id"]);
$fields = $this->getFields("user");
return $this->view->render($response, 'enquiries/view_enquiry.php', [
"router" => $this->router,
"enquiry" => $enquiry,
"fields" => $fields
]);
}
public function viewEditEnquiry($request, $response, $args)
{
$enquiry = $this->getEnquiryById($args["id"]);
$fields = $this->getHTMLFields( $this->getFields("user") );
return $this->view->render($response, 'enquiries/edit_enquiry.php', [
"router" => $this->router,
"enquiry" => $enquiry,
"fields" => $fields
]);
}
public function viewEnquiryForm($request, $response, $args)
{
$fields = $this->getHTMLFields( $this->getFields("all") );
return $this->view->render($response, 'enquiries/new_enquiry.php',[
"router" => $this->router,
"fields" => $fields
]);
}
public function viewTruckForm($request, $response, $args)
{
$enquiry = $this->getEnquiryById($args["id"]);
$fields = $this->getHTMLFields( $this->getFields("truck") );
return $this->view->render($response, 'enquiries/new_truck.php',[
"router" => $this->router,
"enquiry" => $enquiry,
"fields" => $fields
]);
}
public function viewTruck($request, $response, $args)
{
$enquiry = $this->getEnquiryById($args["id"],$args["truckid"]);
// rack fields
$fields = $this->getFields("truck");
return $this->view->render($response, 'enquiries/view_truck.php',[
"router" => $this->router,
"enquiry" => $enquiry,
"fields" => $fields
]);
}
public function viewEditTruck($request, $response, $args)
{
$enquiry = $this->getEnquiryById($args["id"],$args["truckid"]);
$fields = $this->getHTMLFields( $this->getFields("truck") );
return $this->view->render($response, 'enquiries/view_edit_truck.php', [
"router" => $this->router,
"enquiry" => $enquiry,
"fields" => $fields
]);
}
public function viewUpload($request, $response, $args)
{
$file = $this->getUploadById( $args["upload"] );
$image = file_get_contents( $file->getFileLink() );
if ($image === false) {
$response->write('Not found');
return $response->withStatus(404);
}
$response->write($image);
return $response->withHeader('Content-Type', 'image/jpeg');
}
/*
* SEND email
*/
private function getEnquiryTypeEmail($type_id)
{
return $this->settings['enquiry_emails'][$type_id];
}
public function sendEmail($request, $response, $args)
{
// get id
$enquiry_id = $args["id"];
// get enquiry
$enquiry = $this->getEnquiryById($enquiry_id);
// url to view
$enquiry_url = $this->getSiteUrl() . $this->router->pathFor('enquiry.view', ['id' => $enquiry_id]);
// Send to
$send_email_to = $this->getEnquiryTypeEmail( $enquiry->getEnquiryTypeId() );
// update enquiry visibility
$this->setEnquiryVisibility($enquiry_id);
/*
* new email
*/
$email = new TaxationEmail();
$advisor = $this->getUser( $enquiry->getUserId() );
// set email template
$email->setTemplate("enquiry",["/{advisor}/" => $advisor->getFullName(),"/{company}/"=> $enquiry->getCompany(), "/{route}/" => $enquiry_url]);
$email->setSubject("Nieuwe aanvraag: " . $enquiry->getCompany());
$email->setFromAddress( $advisor->getEmailAddress() );
$email->setReceiver( $send_email_to );
$email->sendEmail();
return $response->withStatus(200);//->write(json_encode($data));
// return to taxation view
// return $response->withRedirect( $this->router->pathFor("enquiry.view",["id" => $enquiry->getId()]) );
}
/*************
** SETTERS **
*************/
/*
* Post new enquiry
*/
public function postEnquiry($request, $response, $args)
{
// get form data
$post = $request->getParsedBody();
// prepare sql
$sth = $this->db->prepare(
"INSERT INTO enquiries(
user_id,
type_enquiry,
status,
coordinator,
company_name,
company_city,
company_contact,
company_email,
origin,
follow_up,
date,
notes
)
VALUES(
:user_id,
:type_enquiry,
:status,
:coordinator,
:company_name,
:company_city,
:company_contact,
:company_email,
:origin,
:follow_up,
:date,
:notes
)"
);
$sth->execute([
"user_id" => $this->user_id,
"type_enquiry" => $post["type_enquiry"],
"status" => $post["status"],
"coordinator" => $post["coordinator"],
"company_name" => $post["company_name"],
"company_city" => $post["company_city"],
"company_contact" => $post["company_contact"],
"company_email" => $post["company_email"],
"origin" => $post["origin"],
"follow_up" => $post["follow_up"],
"date" => strftime("%Y-%m-%d",strtotime($post["date"])),
"notes" => $post["notes"],
]);
// get enquiry id
$id = $this->db->lastInsertId();
// set enquiry ID
$args["id"] = $id;
// post truck directly
$this->postTruck($request, $response, $args);
// return to view
$redirect = $this->router->pathFor('enquiry.view',["id" => $id]);
return $response->withRedirect($redirect);
}
/*
* Add the taxation images
*/
private function postUploads($truck_id,$image_array)
{
$sth = $this->db->prepare(
"INSERT INTO enquiry_img(
truck_id,
img_name,
org_name
)
VALUES(
:truck_id,
:img_name,
:org_name
)
");
foreach($image_array as $img)
{
if($img == "" || $img == "0") continue;
$sth->execute(
array(
"truck_id" => $truck_id,
"img_name" => $img[0],
"org_name" => $img[1]
)
);
}
}
/*
* Post new truck in
*/
public function postTruck($request, $response, $args)
{
// get form data
$post = $request->getParsedBody();
// prepare sql
$sth = $this->db->prepare(
"INSERT INTO enquiry_trucks(
enquiry_id,
budget_rent,
date_from,
date_untill,
hours,
buylease,
period,
budget_occasion,
stars,
truck_type,
truck_amount,
truck_load,
truck_height,
truck_drive_height,
truck_fork_length,
truck_mug,
truck_options,
truck_options_other,
truck_operating_hours_year,
truck_usage,
truck_pallet_length,
truck_pallet_width,
truck_power,
truck_remarks
)
VALUES(
:enquiry_id,
:budget_rent,
:date_from,
:date_untill,
:hours,
:buylease,
:period,
:budget_occasion,
:stars,
:truck_type,
:truck_amount,
:truck_load,
:truck_height,
:truck_drive_height,
:truck_fork_length,
:truck_mug,
:truck_options,
:truck_options_other,
:truck_operating_hours_year,
:truck_usage,
:truck_pallet_length,
:truck_pallet_width,
:truck_power,
:truck_remarks
)"
);
$sth->execute([
"enquiry_id" => $args["id"],
"budget_rent" => $post["budget_rent"],
"date_from" => $post["date_from"],
"date_untill" => $post["date_untill"],
"hours" => $post["hours"],
"buylease" => implode(", ",$post["buylease"]),
"period" => $post["period"],
"budget_occasion" => $post["budget_occasion"],
"stars" => implode(", ",$post["stars"]),
"truck_type" => $post["truck_type"],
"truck_amount" => $post["truck_amount"],
"truck_load" => $post["truck_load"],
"truck_height" => $post["truck_height"],
"truck_drive_height" => $post["truck_drive_height"],
"truck_fork_length" => $post["truck_fork_length"],
"truck_mug" => $post["truck_mug"],
"truck_options" => implode(", ",$post["truck_options"]),
"truck_options_other" => $post["truck_options_other"],
"truck_operating_hours_year" => $post["truck_operating_hours_year"],
"truck_usage" => implode(", ",$post["truck_usage"]),
"truck_pallet_length" => $post["truck_pallet_length"],
"truck_pallet_width" => $post["truck_pallet_width"],
"truck_power" => $post["truck_power"],
"truck_remarks" => $post["truck_remarks"]
]);
// get truck id
$id = $this->db->lastInsertId();
// upload images
$uploads = new UploadController($this->settings['upload_path_hidden']);
$fileNames = $uploads->handleUploads($request);
//save images in db
$this->postUploads($id,$fileNames);
$redirect = $this->router->pathFor('enquiry.view',["id" => $args["id"]]);
return $response->withRedirect($redirect);
}
/*
* Delete enquiry
*/
public function deleteEnquiry($request, $response, $args)
{
$sql = 'UPDATE enquiries SET status = 0 WHERE enquiry_id = :enquiry_id';
$sth = $this->db->prepare($sql);
$sth->execute([
"enquiry_id" => $args["id"],
]);
$redirect = $this->router->pathFor('enquiry.dashboard');
return $response->withRedirect($redirect);
}
private function setEnquiryVisibility($enquiry_id)
{
$sql = 'UPDATE enquiries SET visible_for_user = 0 WHERE enquiry_id = :enquiry_id';
$sth = $this->db->prepare($sql);
$sth->execute([
"enquiry_id" => $enquiry_id,
]);
}
/*
* Patch enquiry
*/
public function patchEnquiry($request, $response, $args)
{
// get form data
$post = $request->getParsedBody();
// prepare sql
$sth = $this->db->prepare(
"UPDATE enquiries
SET
type_enquiry = :type_enquiry,
status = :status,
coordinator = :coordinator,
company_name = :company_name,
company_city = :company_city,
company_contact = :company_contact,
company_email = :company_email,
origin = :origin,
date = :date,
follow_up = :follow_up,
notes = :notes,
offer_type = :offer_type,
offer_description = :offer_description,
offer_stars = :offer_stars,
offer_price = :offer_price,
offer_date = :offer_date
WHERE enquiry_id = :enquiry_id");
$sth->execute([
"type_enquiry" => $post["type_enquiry"],
"status" => $post["status"],
"coordinator" => $post["coordinator"],
"company_name" => $post["company_name"],
"company_city" => $post["company_city"],
"company_contact" => $post["company_contact"],
"company_email" => $post["company_email"],
"origin" => $post["origin"],
"date" => $post["date"],
"follow_up" => $post["follow_up"],
"notes" => $post["notes"],
"enquiry_id" => $args["id"],
"offer_type" => $post["offer_type"],
"offer_description" => $post["offer_description"],
"offer_stars" => implode(", ",$post["offer_stars"]),
"offer_price" => $post["offer_price"],
"offer_date" => $post["offer_date"]
]);
$redirect = $this->router->pathFor('enquiry.view',["id" => $args["id"]]);
return $response->withRedirect($redirect);
}
public function setArrayFileStatus($image_array,$status=0)
{
$sth = $this->db->prepare("UPDATE enquiry_img SET status = :status WHERE img_id = :imgid");
foreach($image_array as $img)
{
$sth->execute([
"imgid" => $img,
"status" => $status
]);
}
}
/*
* Delete Truck from enquiry
*/
public function deleteTruck($request, $response, $args)
{
$sql = 'UPDATE enquiry_trucks SET status = 0 WHERE truck_id = :truck_id';
$sth = $this->db->prepare($sql);
$sth->execute([
"truck_id" => $args["truckid"],
]);
$redirect = $this->router->pathFor('enquiry.view',["id" => $args["id"]] );
return $response->withRedirect($redirect);
}
/*
* Update Truck information
*/
public function patchTruck($request, $response, $args)
{
// upload images
$uploads = new UploadController($this->settings['upload_path_hidden']);
$fileNames = $uploads->handleUploads($request);
// remove overriden files from enquiry
$this->setArrayFileStatus($uploads->getOverridenFiles(),0);
// save uploaded images in db
$this->postUploads($args["truckid"],$fileNames);
// get form data
$post = $request->getParsedBody();
// prepare sql
$sth = $this->db->prepare(
"UPDATE enquiry_trucks
SET
budget_rent = :budget_rent,
date_from = :date_from,
date_untill = :date_untill,
hours = :hours,
buylease = :buylease,
period = :period,
budget_occasion = :budget_occasion,
stars = :stars,
truck_type = :truck_type,
truck_amount = :truck_amount,
truck_load = :truck_load,
truck_height = :truck_height,
truck_drive_height = :truck_drive_height,
truck_fork_length = :truck_fork_length,
truck_mug = :truck_mug,
truck_options = :truck_options,
truck_options_other = :truck_options_other,
truck_operating_hours_year = :truck_operating_hours_year,
truck_usage = :truck_usage,
truck_pallet_length = :truck_pallet_length,
truck_pallet_width = :truck_pallet_width,
truck_power = :truck_power,
truck_remarks = :truck_remarks
WHERE truck_id = :truck_id");
$sth->execute([
"truck_id" => $args["truckid"],
"budget_rent" => $post["budget_rent"],
"date_from" => $post["date_from"],
"date_untill" => $post["date_untill"],
"hours" => $post["hours"],
"buylease" => implode(", ",$post["buylease"]),
"period" => $post["period"],
"budget_occasion" => $post["budget_occasion"],
"stars" => implode(", ",$post["stars"]),
"truck_type" => $post["truck_type"],
"truck_amount" => $post["truck_amount"],
"truck_load" => $post["truck_load"],
"truck_height" => $post["truck_height"],
"truck_drive_height" => $post["truck_drive_height"],
"truck_fork_length" => $post["truck_fork_length"],
"truck_mug" => $post["truck_mug"],
"truck_options" => implode(", ",$post["truck_options"]),
"truck_options_other" => $post["truck_options_other"],
"truck_operating_hours_year" => $post["truck_operating_hours_year"],
"truck_usage" => implode(", ",$post["truck_usage"]),
"truck_pallet_length" => $post["truck_pallet_length"],
"truck_pallet_width" => $post["truck_pallet_width"],
"truck_power" => $post["truck_power"],
"truck_remarks" => $post["truck_remarks"]
]);
$redirect = $this->router->pathFor("enquiry.truckview",["id" => $args["id"], "truckid" => $args["truckid"] ]);
return $response->withRedirect($redirect);
}
/*************
** GETTERS **
*************/
/*
* Get enquiry on ID
*/
private function getEnquiryById($enquiry_id,$truck_id = null)
{
$sth = $this->db->prepare('SELECT * FROM enquiries WHERE enquiry_id = :enquiry_id');
$taxation = $sth->execute(["enquiry_id" => $enquiry_id]);
if( $sth->rowCount() > 0 )
{
$data = $sth->fetch();
// set coordinator
if($data["coordinator"] == 0 && ($this->getUserRole() == 2 || $this->getUserRole() == 3) ) $data["coordinator"] = $this->getUserId();
// get trucks
$data["trucks"] = $this->getEnquiryTrucks($enquiry_id,$truck_id);
$data["user_name"] = $this->getUserDetails($data["user_id"],"full_name");
$data["coordinator_name"] = $this->getUserDetails($data["coordinator"],"full_name");
return new EnquiryEntity($this->fields,$data);
}
}
private function getEnquiryUploads($truck_id)
{
$sql = 'SELECT * FROM enquiry_img WHERE truck_id = :truck_id AND status = 1';
$sth = $this->db->prepare($sql);
$sth->execute([
"truck_id" => $truck_id
]);
$images = [];
while($row = $sth->fetch()) {
$images[] = new UploadController($this->settings['upload_path_hidden'],$row);
}
return $images;
}
private function getUploadById($img_id)
{
$sql = 'SELECT * FROM enquiry_img WHERE img_id = :img_id AND status = 1';
$sth = $this->db->prepare($sql);
$sth->execute([
"img_id" => $img_id
]);
return new UploadController($this->settings['upload_path_hidden'],$sth->fetch());
}
private function getEnquiryTrucks($enquiry_id,$truck_id=null)
{
if($truck_id == null)
{
$sql = 'SELECT * FROM enquiry_trucks WHERE enquiry_id = :enquiry_id AND status = 1';
$sth = $this->db->prepare($sql);
$sth->execute(["enquiry_id" => $enquiry_id]);
}
else
{
$sql = 'SELECT * FROM enquiry_trucks WHERE enquiry_id = :enquiry_id AND truck_id = :truck_id AND status = 1';
$sth = $this->db->prepare($sql);
$sth->execute([
"enquiry_id" => $enquiry_id,
"truck_id" => $truck_id
]);
}
$trucks = [];
while($row = $sth->fetch()) {
// get images
$row["images"] = $this->getEnquiryUploads($row["truck_id"]);
$trucks[] = new EnquiryTruck($this->fields,$row);
}
return $trucks;
}
/*
* Get all user enquiries from database
*/
private function getEnquiries($status=1,$view_page = 1,$sort = "ASC",$sort_column = "date")
{
$view_page = is_numeric($view_page) && $view_page > 0 ? $view_page : 1;
$start = ($view_page - 1 ) * 20;
$limit_query = " LIMIT $start,20";
if(is_array($status)) $status_sql = "status IN (". implode(",",$status) .")";
else $status_sql = 'status = '.$status;
//echo $this->getUserRole();
switch($this->getUserRole())
{
default:
$sql = 'SELECT * FROM enquiries WHERE user_id = :user_id AND visible_for_user = 1 AND '. $status_sql .' ORDER BY '. $sort_column .' '. $sort .', enquiry_id DESC '.$limit_query;
$sth = $this->db->prepare($sql);
$sth->execute(["user_id" => $this->user_id]);
$sql1 = 'SELECT * FROM enquiries WHERE user_id = :user_id AND '. $status_sql .' ORDER BY '. $sort_column .' '.$sort.', enquiry_id DESC';
$sth1 = $this->db->prepare($sql1);
$sth1->execute(["user_id" => $this->user_id]);
$this->pagination = ceil($sth1->rowCount()/20);
break;
// Admin + afdeling see all enquiries
case 2:
case 3:
case 14:
$sql = 'SELECT * FROM enquiries WHERE '. $status_sql .' ORDER BY '. $sort_column .' '. $sort .' '.$limit_query;
$sth = $this->db->prepare($sql);
$taxations = $sth->execute();
$sql1 = 'SELECT * FROM enquiries WHERE '.$status_sql .' ORDER BY '. $sort_column .' '. $sort;
$sth1 = $this->db->prepare($sql1);
$sth1->execute();
$this->pagination = ceil($sth1->rowCount()/20);
break;
}
$inspections = [];
while($row = $sth->fetch()) {
$row["user"] = $this->getUser($row["user_id"]);
$inspections[] = new EnquiryEntity($this->fields,$row);
}
return $inspections;
}
/*
* Search DB
*/
public function search($query,$view_page)
{
$view_page = is_numeric($view_page) && $view_page > 0 ? $view_page : 1;
$start = ($view_page - 1 ) * $this->getPageMax();
$limit_query = " LIMIT $start,".$this->getPageMax();
$sql1 = 'SELECT count(*) AS count
FROM enquiries
WHERE
company_name LIKE :search OR
company_city LIKE :search OR
company_contact LIKE :search OR
offer_description LIKE :search OR
notes LIKE :search';
$sth1 = $this->db->prepare($sql1);
$sth1->execute(["search" => "%$query%"]);
$count = $sth1->fetch();
$this->pagination = ceil($count["count"]/$this->getPageMax());
$sql = 'SELECT * FROM enquiries
WHERE
company_name LIKE :search OR
company_city LIKE :search OR
company_contact LIKE :search OR
offer_description LIKE :search OR
notes LIKE :search
ORDER BY company_name '.$limit_query;
$sth = $this->db->prepare($sql);
$sth->execute(["search" => "%$query%"]);
$items = [];
while($row = $sth->fetch()) {
$items[] = new EnquiryEntity($this->fields,$row);
}
return $items;
}
private function getUser($user_id)
{
$sth = $this->db->prepare('SELECT * FROM users WHERE user_id = :userid AND status = 1');
$sth->execute([
"userid" => $user_id
]);
$row = $sth->fetch() ?? [];
if(!$row ) $row = [];
return new JhUser( $row );
}
private function getUserDetails($user_id = null,$return=null)
{
if($user_id === null) return;
$sth = $this->db->prepare('SELECT * FROM users WHERE user_id = :user_id');
$sth->execute([
"user_id" => $user_id
]);
$data = $sth->fetch();
return $return == null ? $data : $data[$return];
}
/*
* Get all taxation fields
*/
private function getFields($type=null)
{
//return $this->fields;
switch($type){
default:
$fields = $this->fields;
unset($fields["Algemeen"]);
unset($fields["Truck"]);
unset($fields["Inzet & Gebruik"]);
unset($fields["Aanbieding"]);
//$fields["Coordinator"][0][5] = $this->getCoordinators();
return $fields;
break;
case "all":
$fields = $this->fields;
unset($fields["Aanbieding"]);
return $fields;
break;
case "truck":
$fields = $this->fields;
unset($fields["Klant"]);
unset($fields["Aanbieding"]);
return $fields;
break;
}
}
}
?>