File: /var/www/vhosts/creativefellows.nl/test.creativefellows.nl/geomil/classes/CassetteController.php
<?php
class CassetteController extends BaseController{
function __construct($db_connection,$router=null,$view=null,$settings=null)
{
$this->db = $db_connection;
$this->router = $router;
$this->view = $view;
$this->settings = $settings;
$this->view_pagination_file = "view.pagination_default.php";
$this->link_level = 1;
$this->client_settings = $this->getClientSettings();
$this->config = new BaseController($settings,$this->client_settings);
}
/*
* View page
*/
public function viewPage($request, $response, $args)
{
// get url parameters
$this->section_name = isset($args["name"]) ? $args["name"] : 0;
$this->category_name = isset($args["category"]) ? $args["category"] : 0;
$this->entry_name = isset($args["entry"]) ? $args["entry"] : 0;
$this->get_uid = isset($args["uid"]) ? $args["uid"] : 0;
$current_pagination = isset($args["pagination"]) ? $args["pagination"] : 1;
// get topbar navigation
$navigation = $this->getMainNavigation(true,$this->section_name);
//die();
// get page contents
$page = $this->getPage($this->section_name,$this->category_name,$this->entry_name,$current_pagination,true,0,true);
// return 404 if no page has been found
if(empty($page)) return $this->get404Page($request, $response, $args);
// return the page
return $this->view->render($response, $this->getViewCanvas($page[0]->page_canvas,$page[0]->pagination_view), array(
"cassette" => $this,
"config" => $this->config,
"router" => $this->router,
"page" => $page,
"navigation" => $navigation,
"section_name" => $this->section_name,
"pagination" => [
"pagination_rows_total" => $this->pagination_rows_total,
"pagination_count" => $this->pagination_count,
"current_page" => $current_pagination,
"next_page" => $current_pagination + 1 ,
"previous_page" => $current_pagination - 1
]
));
}
public function viewCategory($request, $response, $args)
{
// get url parameters
$this->section_name = isset($args["name"]) ? $args["name"] : 0;
$this->category_name = isset($args["category"]) ? $args["category"] : 0;
$this->entry_name = isset($args["entry"]) ? $args["entry"] : 0;
$current_pagination = isset($args["pagination"]) ? $args["pagination"] : 1;
// get topbar navigation
$navigation = $this->getMainNavigation(true,$this->section_name);
// get page contents
$page = $this->getCategoryEntries($this->section_name,$this->category_name,$this->entry_name,$current_pagination);
// return 404 if no page has been found
if(empty($page)) return $this->get404Page($request, $response, $args);
//echo ($this->getViewCanvas($page[0]->page_canvas,$page[0]->pagination_view));
// return the page
return $this->view->render($response, $this->getViewCanvas($page[0]->page_canvas), array( // $this->getViewCanvas($page[0]->page_canvas)
"cassette" => $this,
"config" => $this->config,
"router" => $this->router,
"page" => $page,
"navigation" => $navigation,
"category_name" => $this->category_name,
"section_name" => $this->section_name,
"entry_name" => $this->entry_name,
"pagination" => [
"pagination_rows_total" => $this->pagination_rows_total,
"pagination_count" => $this->pagination_count,
"current_page" => $current_pagination,
"next_page" => $current_pagination + 1 ,
"previous_page" => $current_pagination - 1
]
));
}
/*
* Perview CMS page
*/
public function previewPage($request, $response, $args)
{
// get post uid
$this->get_uid = isset($args["uid"]) ? $args["uid"] : 0;
$this->hash = isset($args["hash"]) ? $args["hash"] : 0;
// check security hash
if($this->hash != $this->getHash($this->get_uid)) return $this->get404Page($request, $response, $args);
//entry uid data
$page = [$this->getEntryById($this->get_uid,null,false,1)];
$this->pagination_rows_total = 1;
// get topbar navigation
$navigation = $this->getMainNavigation();
// return the page
return $this->view->render($response, $this->getViewCanvas($page[0]->page_canvas,$page[0]->pagination_view), array(
"cassette" => $this,
"config" => $this->config,
"router" => $this->router,
"page" => $page,
"navigation" => $navigation,
"section_name" => $this->section_name,
"pagination" => [
"pagination_rows_total" => $this->pagination_rows_total,
"pagination_count" => $this->pagination_count,
"current_page" => $current_pagination,
"next_page" => $current_pagination + 1 ,
"previous_page" => $current_pagination - 1
]
));
}
public function getHashSalt()
{
return $this->settings["hash_salt"];
}
public function getHash($id)
{
return hash("sha256",$id.$this->getHashSalt());
}
public function getViewCanvas($canvas_name=null,$pagination_view=null)
{
// view page
if($this->pagination_rows_total == 1) return "view.". $canvas_name .".php";
elseif($pagination_view !== null) return $pagination_view;
else return $this->view_pagination_file;
}
private function getPageCount()
{
return $this->pageCount;
}
/*
* Return a 404 page
*/
public function get404Page($request, $response, $args)
{
$page = [];
$page[] = new EntryController(null,null,null,null,null,null);
// get topbar navigation
$navigation = $this->getMainNavigation(true,$args["name"]);
// render 404 page
$this->view->render($response, '404.php',array(
"cassette" => $this,
"config" => $this->config,
"page" => $page,
"navigation" => $navigation
));
return $response->withStatus(404);
}
/*
* Get the client settings
*/
private function getClientSettings()
{
$sql = "SELECT * FROM `instellingen` WHERE `id`='1'";
$sth = $this->db->prepare($sql);
$settings = $sth->execute();
if($settings) return $sth->fetch();
}
/*
* Get uid data on post url
*/
private function getPageData($post_url)
{
$sql = "SELECT * FROM unique_ids WHERE url = :url AND status = 2";
$sth = $this->db->prepare($sql);
$sth->execute(["url" => $post_url]);
return $sth->fetch();
}
/*
* Get uid data by id
*/
public function getPageDataById($uid_or_url,$section_id=null,$status = true)
{
if($status == false){
$sql = "SELECT *, unique_ids.unique_id AS uid FROM unique_ids WHERE unique_id = :uid"; //AND sectie_id = :section_id AND
$sth = $this->db->prepare($sql);
$sth->execute([
"uid" => $uid_or_url
]);
}
elseif(is_numeric($uid_or_url)){
$sql = "SELECT *, unique_ids.unique_id AS uid FROM unique_ids WHERE unique_id = :uid AND status = 2"; //AND sectie_id = :section_id AND
$sth = $this->db->prepare($sql);
$sth->execute([
"uid" => $uid_or_url
]);
}
else{
$sql = "SELECT *, unique_ids.unique_id AS uid FROM unique_ids WHERE url = :url AND sectie_id = :section_id AND status = 2";
$sth = $this->db->prepare($sql);
$sth->execute([
"url" => $uid_or_url,
"section_id" => $section_id
]); //,"section_id" => $section_id
}
return $sth->fetch();
}
private function setViewCanvas($view_name)
{
$this->view_pagination_file = $view_name;
}
public function getCategoryId($sectie_id,$category_name)
{
$sql = "SELECT * FROM categories WHERE sectie_id = :section_id AND (naam LIKE :cname OR url LIKE :cname) AND active =1";
$sth = $this->db->prepare($sql);
$sth->execute([
"cname" => $this->validDBpar($category_name),
"section_id" => $sectie_id
]);
$category = $sth->fetch();
return $category["category_id"];
}
public function getCategoryEntries($section_name=null,$category_name=null,$entry_name=0, $current_pagination=0,$set_pagination=true,$link_level=0)
{
//if($entry_name != null) return $this->getPage($entry_name);
$section = $this->getSection($section_name);
if($section["category_view"]) $this->setViewCanvas($section["category_view"]);
$category_id = $this->getCategoryId($section["sectie_id"],$category_name);
$sort_query = $this->getSectionSort($section["sort"]);
$limit_query = $set_pagination == true ? $this->getQueryLimit($current_pagination) : "";
$sql = "SELECT *,unique_ids.unique_id as uid
FROM `entry_categories`,`unique_ids`
WHERE `entry_categories`.`unique_id` = `unique_ids`.`unique_id`
AND category_id = :cat_id
AND `status`='2'";
// query results
$sth = $this->db->prepare($sql . $sort_query . $limit_query);
$sth->execute([
"cat_id" => $category_id
]);
// query all items
$count_sth = $this->db->prepare($sql);
$count_sth->execute([
"cat_id" => $category_id
]);
// set pagination
if($set_pagination == true) $this->setPagination($sth->rowCount(),$count_sth->rowCount());
$uids = [];
//echo "count="+ $count_sth->rowCount()." - ";
while($entry = $sth->fetch())
{
$uids[] = $this->getEntry($entry,$section,true,$link_level);
}
return $uids;
}
public function getEntriesIncategory($section_name=null,$category_name=null)
{
return $this->getCategoryEntries($section_name,$category_name);
}
public function getEntryById($uid=null,$section=null,$status=false,$link_level=0)
{
$entry = $this->getPageDataById($uid,null,$status);
return $this->getEntry($entry,null,false,$link_level);
}
public function getPage($post_url=0,$post_category=0, $post_entry=0, $current_pagination=0,$set_pagination=true,$limit=0,$show_related=false)
{
$uid_array = null;
$limit_query_items = $limit === 0 ? "" : " LIMIT 0,$limit";
$sort_query = "";
/*
* Default homepage, not post data
*/
if($post_url === 0 && $post_entry === 0 && $post_category === 0 )
{
$section = $this->settings["defaultHomeSection"];
$section = $this->getSection($section);
}
elseif($post_url !== 0 && $post_category != 0)
{
//$section = $this->getSection($post_url);
$sql = "SELECT *,unique_ids.unique_id as uid
FROM entry_categories
LEFT JOIN unique_ids
ON unique_ids.unique_id = entry_categories.unique_id
WHERE unique_ids.status = 2
AND unique_ids.language_id = 0
AND entry_categories.category_id = :category_id ORDER BY position";
$sth = $this->db->prepare($sql . $limit_query_items);
$sth->execute([
"category_id" => $post_category,
]);
$uids = [];
while($entry = $sth->fetch())
{
$section = $this->getSection($entry["sectie_id"]);
$uids[] = $this->getEntry($entry,$section);
}
return $uids;
}
elseif($post_entry != "0")
{
$section = $this->getSection($post_url);
/*
* Get entry data on post url
*/
$uid_array = $this->getPageDataById($post_entry,$section["sectie_id"]);
}
else
{
/*
* Check if post is a section
*/
//echo $post_url;
$section = $this->getSection($post_url);
if( empty($section) )
{
/*
* Get entry data on post url
*/
$uid_array = $this->getPageData($post_url);
if( !empty($uid_array) ) $section = $this->getSection($uid_array["sectie_id"]);
else return null;
}
}
/*
* Get section sort & limit
*/
$sort_query = $this->getSectionSort($section["sort"]);
$limit_query = $set_pagination == true ? $this->getQueryLimit($current_pagination) : $limit_query_items;
/*
* Get toplevel entries
*/
if($uid_array === null)
{
$sql = "SELECT *,unique_ids.unique_id as uid
FROM unique_ids
LEFT JOIN entry_categories ON unique_ids.unique_id = entry_categories.unique_id
WHERE unique_ids.sectie_id = :sectie_id
AND unique_ids.status = 2
AND unique_ids.language_id = '0'
AND entry_categories.entry_category_id IS NULL";
$sth = $this->db->prepare($sql . $sort_query . $limit_query);
$sth->execute([
"sectie_id" => $section["sectie_id"]
]);
$count_sth = $this->db->prepare($sql);
$count_sth->execute([
"sectie_id" => $section["sectie_id"]
]);
if($set_pagination == true) $this->setPagination($sth->rowCount(),$count_sth->rowCount());
}
else{
$sql = "SELECT *,unique_ids.unique_id as uid
FROM unique_ids
WHERE unique_ids.sectie_id = :sectie_id
AND unique_ids.status = 2
AND unique_ids.language_id = '0'
AND unique_ids.unique_id = :uid ";
$sth = $this->db->prepare($sql);
$sth->execute([
"sectie_id" => $section["sectie_id"],
"uid" => $uid_array["unique_id"]
]);
if($set_pagination == true) $this->setPagination($sth->rowCount(),$sth->rowCount());
}
$uids = [];
while($entry = $sth->fetch())
{
$uids[] = $this->getEntry($entry,$section,$show_related,1);
}
return $uids;
}
public function getEntry($entry,$section_arr=null,$show_related=false,$link_level=0)
{
// template fields
$fields = $this->getTemplateFields($entry["template_id"]);
// get tablename
$table = $this->getTableName($entry["template_id"]);
// get entry data
$entry_data = $this->getEntryData($table,$entry["entry_id"]);
// get entry data
$entry_category = $this->getEntryCategory($entry["uid"]);
// get the form data
$form = $this->getForm($entry_data["form_id"]);
if($entry_data["stramien_id"] != 0)
{
$entry["view"] = $this->replaceSpace($this->getGridName($entry_data["stramien_id"],"naam"));
}
foreach($fields as $i => $f)
{
$fields[$i]["html"] = $this->getFieldHTML($entry["uid"],$f["field_naam"],$f["form_element"],$entry_data[$f["field_naam"]],$f["template_field_id"],$show_related,$link_level);
}
if(!$section_arr) $section_arr = $this->getSection($entry["sectie_id"]);
return new EntryController($entry,$fields,$entry_data,$form,$section_arr,$entry_category);
}
/*
* Get the html contens on a db field
*/
private function getFieldHTML($unique_id,$field_user,$element,$value,$template_field_id=null,$show_related=false,$link_level=0)
{
$field_user = preg_replace("/[^0-9a-zA-Z]/","_",strtolower($field_user));
$value = stripslashes($value);
$data = array();
switch($element)
{
default:
return $value;
break;
case "shop":
$staffel_array = $this->getProductPricing($unique_id);
$this->prijs_voor = $staffel_array[0]["voor"];
$this->prijs_van = $staffel_array[0]["van"];
$this->prijs_desc = $staffel_array[0]["naam"];
return [$staffel_array["naam"], $staffel_array["van"], $staffel_array["voor"]];
break;
case "image":
$data = explode("|*|",$value);
foreach($data as $i=> $d){
if($d == "") unset($data[$i]);
}
return $data;//explode("|*|",$value);
//
break;
case "tag":
$tags = array_map('trim', explode(',', $value));
$entries_tagged = $show_related == true ? $this->getTaggedEntries($tags,$unique_id,"LIMIT 0,10") : [];
return ["tags" => $tags, "related" => $entries_tagged];
break;
case "files":
$files = explode("|*|",$value);
foreach($files as $f){
$file_data = explode("|-|",$f);
if($file_data[0] == "") continue;
if(end($file_data) == ""){
$parts = explode("/",preg_replace("/_/"," ",$file_data[0]));
$file_data[1] = end($parts);
}
array_push($data,$file_data);
}
return $data;
break;
case "link":
case "cols":
$data = [];
//echo $link_level ." - ";
if(trim($value) != "" && $link_level == 1)
{
$links = explode("|*|",$value);
$link_arr = [];
foreach($links as $link_str)
{
$arr = explode("|-|",$link_str);
$entry = $this->getLinkedEntries($arr[0],$link_level);
array_push($data,$entry);
}
}
return $data;
break;
case "checkbox":
case "radio":
return explode("|*|",$value);
break;
case "graph":
$value_arr = explode("|*|",$value);
$value = array();
foreach($value_arr as $group)
{
$arr = explode(" ",$group);
$value[] = $arr;
}
$arr_values = array();
$header = array();
foreach($value[0] as $y => $xas_value)
{
$row = array();
$row[] = $xas_value;
$header[] = $xas_value;
for($x=1; $x<count($value); $x++)
{
$row[] = floatval( str_replace( array("%",","), array("","."), $value[$x][$y]) );
if($y == 0) $header[] = $value[$x][$y];
}
//$row = array($xas_value,"x1","x2");
if($y == 0) $arr_values[] = $header;
else{
$str = array();
foreach($row as $z => $column)
{
$str[] = $column;//.",";//implode(",",$row) ;
}
$arr_values[] = $str;//substr($str,0,-1);
}
}
return $arr_values;
break;
case "dragdrop":
// get toplevel sections
$sections = $this->getSectionContent($unique_id);
$html = "";
foreach($sections as $i => $s)
{
// set html
$section_data_html = "";
// subitems
$section_subs = $this->getSectionContent($unique_id,$s["page_section_id"]);
// background style
$background_style = $s["css"] != "" ? $s["css"].";" : "";
// linked data
if($s["link_data"] != 0 && $show_related !== false){
$s["html"] = $this->getLinkedData($s);
}
// add first class if first item
$s["element_class"] = $i == 0 ? $s["element_class"]." first-section" : $s["element_class"];
// item is fullpage
if( $s["fullpage"] == 0 )
{
$section_data_html .= preg_replace(
array("/{section_htmlwrapper}/","/{style}/","/{element-id}/","/{element-class}/","/{data-attributes}/"),
array($s["html"],$background_style,$s["element_id"],$s["element_class"],""),
$this->settings["group_wrapper"]
);
}
else
{
$section_data_html .= preg_replace(
array("/{section_htmlwrapper}/","/{style}/","/{element-id}/","/{element-class}/","/{data-attributes}/"),
array($s["html"],$background_style,$s["element_id"],$s["element_class"]." collapse-grid",""),$this->settings["default_wrapper"]
);
}
// sub items
$section_sub_html = "";
foreach($section_subs as $ss)
{
$bg = $s["css"] != "" ? $s["css"].";" : "";
// get linked data
if($ss["link_data"] != 0 && $show_related !== false){
//die("linkdata");
$ss["html"] = $this->getLinkedData($ss);
//echo "test1";
}
$section_sub_html .= preg_replace(
array("/{section_htmlwrapper}/","/{style}/","/{element-id}/","/{element-class}/","/{data-attributes}/"),
array($ss["html"],$bg,$ss["element_id"],$ss["element_class"],""),
$this->settings["nested_wrapper"]
);
}
// set html to property
$html .= preg_replace("/{contents}/",$section_sub_html,$section_data_html);
}
return $html;
break;
}
}
private function getEntryCategory($uid)
{
$sql = "SELECT `naam`
FROM `entry_categories`,`categories`
WHERE `categories`.`category_id` = `entry_categories`.`category_id`
AND `entry_categories`.`unique_id` = :uid";
$sth = $this->db->prepare($sql);
$sth->execute(["uid" => $uid]);
$category = $sth->fetch();
return $category;
}
private function getGridName($grid_id,$return=null)
{
$sql = "SELECT * FROM stramiens WHERE stramien_id = :grid_id";
$sth = $this->db->prepare($sql);
$sth->execute(["grid_id" => $grid_id]);
$data = $sth->fetch();
return $return == null ? $data : $data[$return];
}
public function getCategories($section)
{
// get section data
$section = $this->getSection($section);
if(!$section) return;
$sql = "SELECT * FROM categories WHERE sectie_id = :section_id AND active = 1 ORDER BY position";
$sth = $this->db->prepare($sql);
$sth->execute([
"section_id" => $section["sectie_id"]
]);
$data = array();
while($category = $sth->fetch())
{
$category["entries"] = $this->getCategoryEntries($section["sectie_id"],$category["naam"]);
//$data[] = $category;
$data[] = new CategoryController($section,$category);
}
return $data;
}
public function getCategoryNameByUrl(){
}
public function getCategoryNames($section,$visible=1)
{
// get section data
$section = $this->getSection($section);
if(!$section) return;
$sql = "SELECT * FROM categories WHERE sectie_id = :section_id AND active = 1 AND zichtbaar = :visible ORDER BY position";
$sth = $this->db->prepare($sql);
$sth->execute([
"section_id" => $section["sectie_id"],
"visible" => $visible
]);
$data = [];
while($category = $sth->fetch())
{
$data[] = new CategoryController($section,$category);
}
return $data;
}
public function getVisibleCategories($section,$link_level=0)
{
// get section data
$section = $this->getSection($section);
if(!$section) return;
$sql = "SELECT * FROM categories WHERE sectie_id = :section_id AND active = 1 AND zichtbaar = 1 ORDER BY position";
$sth = $this->db->prepare($sql);
$sth->execute([
"section_id" => $section["sectie_id"]
]);
$data = array();
while($category = $sth->fetch())
{
$category["entries"] = $this->getCategoryEntries($section["sectie_id"],$category["naam"],0,0,true,$link_level);
$data[] = $category;
}
return $data;
}
public function searchInTable($table,$search_column_array,$search_values_array,$operator_array = array("LIKE"),$order_by=null){
$binds_arr = [];
$search = "WHERE ";
foreach($search_column_array as $i => $post_name)
{
$field_value = $search_values_array[$i];
$operator = $operator_array[$i];
$field_name = $post_name;
if($field_value == "") continue;
$search .= "$field_name $operator :".$field_name." OR"; //$field_value
$binds_arr[$field_name] = $field_value;
}
$sql = "SELECT * FROM $table LEFT JOIN unique_ids ON $table.unique_id = unique_ids.unique_id ". substr($search,0,-3) . $order_by;
$sth = $this->db->prepare($sql);
$sth->execute($binds_arr);
$uids = [];
while($entry = $sth->fetch())
{
$uids[] = $this->getEntry($entry);
}
return $uids;
}
/*
* Set pagination
*/
private function setPagination($rows,$total_rows)
{
//echo "ipp=".$this->settings["items_per_page"]." ";
$this->pagination_rows_total = $total_rows;
$this->pagination_count = ceil($total_rows / $this->settings["items_per_page"]);
}
/*
* Get form
*/
public function getForm($form_id)
{
if($form_id == 0) return null;
$form_data = $this->getFormData($form_id);
$form_fields = $this->getFormFields($form_id,$form_data["unique_id"]);
return new CassetteForm($this->settings,$form_data,$form_fields,$this->client_settings);
}
private function getFormData($form_id)
{
$sql = "SELECT * FROM forms WHERE form_id = :form_id";
$sth = $this->db->prepare($sql);
$sth->execute(["form_id" => $form_id]);
return $sth->fetch();
}
private function getFormFields($form_id, $unique_id)
{
$sql = "SELECT * FROM form_elements WHERE unique_id = :unique_id ORDER BY position ASC";
$sth = $this->db->prepare($sql);
$sth->execute(["unique_id" => $unique_id]);
$fields = [];
while($field = $sth->fetch())
{
$e = array();
$e["elementId"] = $field["form_element_id"];
$e["type"] = $field["field_id"];
$e["required"] = $field["value"];
$field_data = $this->getFormField($field);
foreach($field_data as $t => $v){
$e[$t] = $v;
}
$fields[] = $e;
}
// add form id
$fields[] = $this->addFormId($form_id);
// add form id
if($this->settings["captcha_sitekey"] != null) $fields[] = $this->addCaptcha();
// add a csrf
$fields[] = $this->addCsrf();
// add a button
$fields[] = $this->addButton();
return $fields;
}
private function addCaptcha()
{
$d = array();
$d["type"] = "captcha";
$d["name"] = $this->settings["captcha_sitekey"];
$d["desc"] = $this->settings["captcha_sitekey"];
return $d;
}
private function addMsg()
{
$d = array();
$d["type"] = "msg";
$d["name"] = "*) Mandatory field";
$d["desc"] = "";
return $d;
}
private function addButton()
{
$d = array();
$d["type"] = "submit";
$d["name"] = "";//$naam;
$d["desc"] = "";//$desc;
return $d;
}
private function addFormId($form_id)
{
$d = array();
$d["type"] = "hidden";
$d["name"] = "formid";
$d["desc"] = $form_id;
return $d;
}
private function addCsrf()
{
$d = array();
$d["type"] = "hidden";
$d["name"] = "csrf";
$d["desc"] = $this->settings["csrf_token"];
return $d;
}
private function getFormField($field_array)
{
$form_el_id = $field_array["form_element_id"];
$field_id = $field_array["field_id"];
$naam = $field_array["name"];
$desc = $field_array["description"];
$d = [];
$d["valueName"] = $form_el_id . preg_replace("/[^0-9a-zA-Z]/","",strtolower($naam));
$elementName = preg_replace("/ /","",$form_el_id."-".$naam);
switch($field_id){
// input
case 1:
$d["type"] = "input";
$d["name"] = $naam;
$d["desc"] = $desc;
break;
//dropdown
case 28:
$d["type"] = "select";
$d["name"] = $naam;
$d["desc"] = $desc;
$o = array();
$element_options = $this->getFormElementOptions($form_el_id);
foreach($element_options as $r)
{
array_push($o,$r['value']);
}
$d["options"] = $o;
break;
//radio
case 31:
$d["type"] = "radio";
$d["name"] = $naam;
$d["desc"] = $desc;
$o = array();
$element_options = $this->getFormElementOptions($form_el_id);
foreach($element_options as $r)
{
array_push($o,$r['value']);
}
$d["options"] = $o;
break;
//checkbox
case 24:
$d["type"] = "checkbox";
$d["name"] = $naam;
$d["desc"] = $desc;
$o = array();
$element_options = $this->getFormElementOptions($form_el_id);
foreach($element_options as $r){
array_push($o,$r['value']);
}
$d["options"] = $o;
break;
//multi text line
case 5:
$d["type"] = "textarea";
$d["name"] = $naam;
$d["desc"] = $desc;
break;
case 15:
$d["type"] = "deactivatedfield";
$d["name"] = $naam;
$d["desc"] = $desc;
break;
case 14:
case 30:
$d["type"] = "datepicker";
$d["name"] = $naam;
$d["desc"] = $desc;
break;
case 39:
$d["type"] = "timepicker";
$d["name"] = $naam;
$d["desc"] = $desc;
break;
case 16:
$d["type"] = "inactiveinput";
$d["name"] = $naam;
$d["desc"] = $desc;
break;
case 32:
$d["type"] = "tussenkop";
$d["name"] = $naam;
$d["desc"] = $desc;
break;
case 60:
case 18:
$d["type"] = "bijlage";
$d["name"] = $naam;
$d["desc"] = $desc;
break;
case 38:
$d["type"] = "postdata";
$d["name"] = $naam;
$d["desc"] = $desc;
break;
}
return $d;
}
private function getFormElementOptions($form_element_id)
{
$sql = "SELECT * FROM form_elements_options WHERE form_element_id = :form_el_id ORDER BY form_option_id ASC";
$sth = $this->db->prepare($sql);
$sth->execute(["form_el_id" => $form_element_id]);
return $sth->fetchAll();
}
private function getTaggedEntries($tag_array,$current_uid=0,$limit_query="LIMIT 0,5",$set_pagination=false)
{
$sql = "SELECT
entry_tags.unique_id,
count(*) as filter_count,
unique_ids.template_id,
unique_ids.entry_id,
unique_ids.sectie_id,
unique_ids.url,
unique_ids.created,
unique_ids.unique_id AS uid
FROM tags
LEFT JOIN entry_tags
ON tags.tag_id = entry_tags.tag_id
LEFT JOIN unique_ids
ON unique_ids.unique_id = entry_tags.unique_id
WHERE tags.name
IN ('". implode("','",$tag_array)."')
AND entry_tags.unique_id != $current_uid
AND unique_ids.status = 2
GROUP BY entry_tags.unique_id ORDER BY filter_count DESC ";
$sth = $this->db->prepare($sql . $limit_query);
$sth->execute();
// set pagination on search all tagged entries
if($set_pagination == true){
$count_sth = $this->db->prepare($sql);
$count_sth->execute();
$this->setPagination($sth->rowCount(),$count_sth->rowCount());
}
$uids = [];
while($entry = $sth->fetch())
{
$uids[] = $this->getEntry($entry,null,false);
}
return $uids;
}
private function getLinkedEntries($uid=null,$link_level)
{
$sql = "SELECT *,unique_ids.unique_id as uid
FROM unique_ids
WHERE unique_ids.unique_id = :uid";
$sth = $this->db->prepare($sql);
$sth->execute(["uid" => $uid]);
$entry = $sth->fetch();
$section = $this->getSection($entry["sectie_id"]);
return $this->getEntry($entry,$section,false,1);
}
public function getAllTags()
{
$sql = "SELECT *
FROM tags
LEFT JOIN entry_tags
ON tags.tag_id = entry_tags.tag_id
GROUP BY tags.name
ORDER BY tags.name ASC";
$sth = $this->db->prepare($sql . $limit_query);
$sth->execute();
$tags = [];
while($tag = $sth->fetch())
{
$tags[] = new TagController($tag);
}
return $tags;
}
private function getProductPricing($unique_id)
{
$sql = "SELECT * FROM `prijzen_product` WHERE `unique_id` = :unique_id ";
$sth = $this->db->prepare($sql);
$sth->execute(["unique_id" => $unique_id]);
return $sth->fetch();
}
/*
* submit a form
*/
public function handleForm($request, $response, $args)
{
// get form data
$data = $request->getParsedBody();
// verify the captcha in the post
$recaptcha = new \ReCaptcha\ReCaptcha($this->settings["captcha_secretkey"]);
$resp = $recaptcha->verify($data['g-recaptcha-response'], $data['REMOTE_ADDR']);
// form referer
$form_referer = explode("?",$_SERVER['HTTP_REFERER']);
// return capthca error
if(!$resp->isSuccess())
{
$_SESSION["form".$data["formid"]] = $data;
return $response->withRedirect($form_referer[0] ."?s=0&id=". $data["formid"] ."&ReCaptcha=0" );
}
// form referer
$form_referer = explode("?",$_SERVER['HTTP_REFERER']);
if(is_numeric($data["formid"]) && $data["formid"] != "") // && isset($_POST["csrf"]) && $_POST["csrf"] == $_SESSION["csrf_token"]
{
$form_data = $this->getFormData($data["formid"]);
$form_fields = $this->getFormFields($data["formid"],$form_data["unique_id"]);
// get the images
$upload_files = $request->getUploadedFiles();
$form = new CassetteForm($this->settings,$form_data,$form_fields,$this->client_settings,$upload_files);
$form_is_send = $form->handleForm($data);
if($form_is_send == true){
//header("Location: ". $form_referer[0] ."?s=1&id=$formid&hash=".md5($security_key));
//die( $this->getFormHash() );
//$request = $request->withAttribute('loginError','Geen toegang voor 10 minuten');
return $response->withRedirect($form_referer[0] ."?s=1&id=". $data["formid"] ."&h=" . $this->getFormHash($data["formid"]) );//->withAttribute('success',1);
}else{
return $response->withRedirect($form_referer[0] ."?s=1&id=". $data["formid"] ."&h=" . $this->getFormHash($data["formid"]) );//->withAttribute('success',1);
}
}
}
public function getDownload($request, $response, $args)
{
$hash = isset($args["hash"]) ? $args["hash"] : null;
$filename = isset($args["filename"]) ? $args["filename"] : null;
// hash is valid
if($hash == $this->getFormHash($filename) )
{
$file = $this->config->getSetting("upload_path") . $filename;
if(file_exists($file))
{
$fh = fopen($file, 'rb');
$stream = new \Slim\Http\Stream($fh);
return $response->withHeader('Content-Type', 'application/force-download')
->withHeader('Content-Type', 'application/octet-stream')
->withHeader('Content-Type', 'application/download')
->withHeader('Content-Description', 'File Transfer')
->withHeader('Content-Transfer-Encoding', 'binary')
->withHeader('Content-Disposition', 'attachment; filename="' . basename($file) . '"')
->withHeader('Expires', '0')
->withHeader('Cache-Control', 'must-revalidate, post-check=0, pre-check=0')
->withHeader('Pragma', 'public')
->withBody($stream);
}
}
// return error page
return $this->get404Page($request, $response, $args);
}
private function getSectionContent($unique_id,$sub_of=0)
{
$sql = "SELECT * FROM page_section_content WHERE page_id = :uid AND sub_of = :sub_of AND status = '1' ORDER BY position ASC";
$sth = $this->db->prepare($sql);
$sth->execute(["uid" => $unique_id, "sub_of" => $sub_of]);
return $sth->fetchAll();
}
private function getLinkData($link_data_id)
{
$sql = "SELECT * FROM link_data WHERE link_data_id = :link_data_id";
$sth = $this->db->prepare($sql);
$sth->execute(["link_data_id" => $link_data_id]);
return $sth->fetch();
}
private function getLinkDataFields($link_data_id)
{
$sql = "SELECT * FROM `link_data_fields` WHERE `template_data_id` = :link_data_id";
$sth = $this->db->prepare($sql);
$sth->execute(["link_data_id" => $link_data_id]);
return $sth->fetchAll();
}
private function getLinkedData($section_data)
{
//d($section_data);
// org content
$original_content = $section_data["html"];
// template
$link_data = $this->getLinkData($section_data["link_data"]);
// field to replace in template
$template_fields = $this->getLinkDataFields($section_data["link_data"]);
/*
* inline replace string has cid in regex
*/
preg_match_all("/{veelgestelde_vragen:([0-9]+)}/i", $original_content, $matches,PREG_SET_ORDER);
if($matches){
// get category_id to load
$link_data["category"] = $matches[0][1];
}
// get entries
$entries = $this->getPage($link_data["section"],$link_data["category"],$link_data["entry"],0,false,0,false);
//d($entries);
// replace all 1:1
if($link_data["repeat_items"] == 0)
{
$replace_total = count($matches);//substr_count($original_content, $link_data["find"]);
for($entry_no=0; $entry_no<$replace_total; $entry_no++)
{
// set org template
$template = $link_data["template"];
// find and replace fields in template
foreach($template_fields as $tp_fld)
{
if(!$tp_fld["field"]) continue;
$field = $tp_fld["field"];
$value = $entries[$entry_no]->$field;
if( $tp_fld["array"] == 1 && $tp_fld["function"] == 0 ){
$value = $value[$tp_fld["index"]];
}
$value = $tp_fld["intro"] != 0 ? $this->makeIntro($value,$tp_fld["intro"]) : $value;
// replace data as chart
if( $tp_fld["function"] == 1 )
{
$value_str = array();
foreach($value as $i => $row)
{
if($i == 0) $value_str[] = "['". implode("','",$row) ."']";
else
{
$str = "";
foreach($row as $x => $val){
$str .= $x == 0 ? "'". $val ."'," : $val.",";
}
$value_str[] = "[".substr($str,0,-1)."]";
}
//$value_str[] = $i == 0 ? "['". implode("','",$row) ."']" : "[". implode(",",$row) ."]";
}
$value = implode(",",$value_str);
}
elseif( $tp_fld["function"] == 2 )
{
$data2 = [];
foreach($value as $i => $row)
{
for($x = 0; $x<count($row); $x++)
{
if($x == 0 || $i == 0) $data2[$x][$i] = "'". $row[$x] ."'";
else $data2[$x][$i] = "".$row[$x]."";
}
}
$str = "";
foreach($data2 as $row){
$str .= "[". implode(",",$row) ."],";
}
$value = substr($str,0,-1);
//die();
//$value = implode(",",$value_str);
}
$template = preg_replace("/".$tp_fld["template"]."/i", $value, $template);
}
$original_content = $this->str_replace_nth($link_data["find"], $template, $original_content, $entry_no);
}
}
// replace single template by all entries
else
{
$content_str = "";
for($i=0; $i < count($entries); $i++)
{
// set org template
$template = $link_data["template"];
// find and replace fields in template
foreach($template_fields as $tp_fld)
{
if(!$tp_fld["field"]) continue;
$field = $tp_fld["field"];
//echo $field." - ";
$value = $entries[$i]->$field;
//d($value);
//echo $tp_fld["array"]." -";
if( $tp_fld["array"] == 1 && is_array($value) ){
$value = isset($value[$tp_fld["index"]]) ? $value[ $tp_fld["index"] ] : "";
}
$value = $tp_fld["intro"] != 0 ? $this->makeIntro($value,$tp_fld["intro"]) : $value;
if( $tp_fld["function"] == 1 )
{
$value_str = array();
foreach($value as $i => $row)
{
if($i == 0) $value_str[] = "['". implode("','",$row) ."']";
else
{
$str = "";
foreach($row as $x => $val){
$str .= $x == 0 ? "'". $val ."'," : $val.",";
}
$value_str[] = "[".substr($str,0,-1)."]";
}
//$value_str[] = $i == 0 ? "['". implode("','",$row) ."']" : "[". implode(",",$row) ."]";
}
$value = implode(",",$value_str);
}
elseif( $tp_fld["function"] == 2)
{
$value = strftime('<span class="day">%e</span><span class="month">%b</span>',strtotime($value));
}
elseif( $tp_fld["function"] == 3)
{
$value = $value != "" ? strftime('%e %B %Y',strtotime($value)) : "";
}
$template = preg_replace("/".$tp_fld["template"]."/i", $value, $template);
}
$content_str .= $template;
}
$original_content = preg_replace("/".$link_data["find"]."/i", $content_str,$original_content);
}
return $original_content;
}
private function str_replace_nth($search, $replace, $subject, $nth)
{
$found = preg_match_all('/'.preg_quote($search).'/', $subject, $matches, PREG_OFFSET_CAPTURE);
if ($found !== false) {
return substr_replace($subject, $replace, $matches[0][0][1], strlen($search));
}
return $subject;
}
private function getEntryData($table,$table_entry_id)
{
$sql = "SELECT * FROM `$table` WHERE entry_id = :entry_id ORDER BY position ASC";
$sth = $this->db->prepare($sql);
$sth->execute(["entry_id" => $table_entry_id]);
return $sth->fetch();
}
private function getTableName($template_id)
{
$sql = "SELECT naam FROM templates WHERE template_id = :template_id";
$sth = $this->db->prepare($sql);
$sth->execute(["template_id" => $template_id]);
$data = $sth->fetch();
return "td_".$data["naam"];
}
private function getTemplateFields($template_id,$subof=null)
{
$sub_query = ($subof != null) ? "AND sub_of=$subof" : "AND sub_of=0";
$sql = "SELECT * FROM template_fields LEFT JOIN field_types ON template_fields.field_id = field_types.field_id WHERE template_id = :template_id $sub_query AND active=1 ORDER BY template_fields.position ASC";
$sth = $this->db->prepare($sql);
$sth->execute(["template_id" => $template_id]);
return $sth->fetchAll();
}
public function getSection($name,$return=null)
{
if(is_numeric($name))
{
$sql = "SELECT *,secties.naam AS section_name FROM `secties` LEFT JOIN stramiens ON secties.stramien = stramiens.stramien_id WHERE secties.active = '1' AND secties.sectie_id = :name";
}
else $sql = "SELECT *,secties.naam AS section_name FROM secties LEFT JOIN stramiens ON secties.stramien = stramiens.stramien_id WHERE secties.active = '1' AND (secties.naam LIKE :name OR secties.url LIKE :name)";
//echo $sql;
$sth = $this->db->prepare($sql);
$sth->execute(["name" => $this->validDBpar($name)]);
$data = $sth->fetch();
return $return == null ? $data : $data[$return];
}
private function getSectionSort($sort_id)
{
// get section sort
switch($sort_id)
{
case 1;
$sort_query = " ORDER BY `created` DESC";
break;
case 2;
$sort_query = " ORDER BY `created` ASC";
break;
default;
$sort_query = " ORDER BY `position` ASC, `created` DESC";
break;
}
return $sort_query;
}
public function getMainNavigation($show_visible=true,$active_page_name=null)
{
if($show_visible == true)
{
$sql = "SELECT * FROM `secties` WHERE `zichtbaar` = '1' AND `active` = '1' ORDER BY `position` ASC";
$sth = $this->db->prepare($sql);
$sth->execute();
$nav_elements = [];
while($section = $sth->fetch())
{
$nav_items = $this->getNavigationElements(1,$section);
$nav_elements[] = new NavigationController($nav_items,"",$section,$active_page_name);
}
return $nav_elements;
}
}
private function getNavigationElements($positie_id,$sectie_array=null,$category_name=null)
{
if($sectie_array !== null)
{
// new array
$data = array();
// categories
$data[] = $this->getCategoriesBySectionId($sectie_array["sectie_id"],1);
// entries
$data[] = $this->getEntriesInSection($sectie_array,1);
return $data;
}
else return $this->getEntriesOnPosition($positie_id);
}
private function getEntriesOnPosition($position_id)
{
$sql = "SELECT * FROM unique_ids,entry_posities WHERE unique_ids.unique_id = entry_posities.unique_id AND entry_posities.positie_id = :positie_id AND unique_ids.status = 2 ORDER BY unique_ids.position ASC";
$sth = $this->db->prepare($sql);
$sth->execute([
"positie_id" => $position_id
]);
return $sth->fetchAll();
}
private function getCategoriesBySectionId($section_id)
{
$sql = "SELECT * FROM `categories` WHERE `sectie_id` = :section_id AND `active`='1' AND `zichtbaar` = '1' ORDER BY position ASC";
$sth = $this->db->prepare($sql);
$sth->execute([
"section_id" => $section_id
]);
return $sth->fetchAll();
}
private function getEntriesInSection($sectie_array,$position_id)
{
$sql = "SELECT *,unique_ids.unique_id as uid
FROM unique_ids,entry_posities
WHERE unique_ids.unique_id = entry_posities.unique_id
AND unique_ids.sectie_id = :section_id
AND entry_posities.positie_id = :positie_id
AND unique_ids.status = 2
ORDER BY unique_ids.position ASC";
$sth = $this->db->prepare($sql);
$sth->execute([
"section_id" => $sectie_array["sectie_id"],
"positie_id" => $position_id
]);
$entries = [];
while($entry = $sth->fetch())
{
$entries[] = $this->getEntry($entry,$sectie_array);
}
return $entries;
}
function getSideBarNavigation($active_page_name=null)
{
// section
$sql = "SELECT * FROM secties WHERE naam = :name AND active = 1 ORDER BY position ASC";
$sth = $this->db->prepare($sql);
$sth->execute([
"name" => $active_page_name
]);
$nav_elements = [];
while($nav = $sth->fetch())
{
$nav_elements[] = new NavigationController($this->db,"",$nav,$active_page_name);
}
return $nav_elements;
//else $navs = $this->db->run("SELECT * FROM `secties` WHERE `active` = '1' ORDER BY `position` ASC");
}
public function getSectionName()
{
return preg_replace("/-/"," ",$this->section_name);
}
public function getNavigation($positie_id)
{
// new array
$data = array();
// entries
$sql = "SELECT *,unique_ids.unique_id as uid
FROM unique_ids,entry_posities
WHERE unique_ids.unique_id = entry_posities.unique_id
AND entry_posities.positie_id = :positie_id
AND unique_ids.status = 2
ORDER BY unique_ids.position ASC";
$sth = $this->db->prepare($sql);
$sth->execute(["positie_id" => $positie_id]);
while($entry = $sth->fetch())
{
$data[] = $this->getEntry($entry);
}
return $data;
}
/*
* Search DB with string
*/
public function getSearchString($request, $response, $args)
{
// get topbar navigation
$navigation = $this->getMainNavigation(true,$args["name"]);
// get url parameters
$this->section_name = "Zoekresultaten";
$current_pagination = isset($args["pagination"]) ? $args["pagination"] : 1;
// search par
$get = $request->getQueryParams();
$search_value = isset($get["q"]) ? $get["q"] : null;
$search_page = isset($get["p"]) ? $get["p"] : 1;
// get page contents
$search_tables = ["page_id" => "page_section_content"];
$search_columns = [["html"]];
$page = $this->searchPages($search_tables,$search_columns,$search_value,$search_page);
// return the page
return $this->view->render($response, "view.search.php", array(
"cassette" => $this,
"config" => $this->config,
"router" => $this->router,
"page" => $page,
"navigation" => $navigation,
"section_name" => $this->section_name,
"pagination" => [
"pagination_rows_total" => $this->pagination_rows_total,
"pagination_count" => $this->pagination_count,
"current_page" => $current_pagination,
"next_page" => $current_pagination + 1 ,
"previous_page" => $current_pagination - 1
]
));
}
private function searchPages($search_table_array,$search_column_array,$search_values,$view_page=1)
{
$results_arr = array();
$i = 0;
foreach($search_table_array as $identifier => $search_table)
{
$limit_query = $this->getQueryLimit($view_page);
$search_columns = implode(",",$search_column_array[$i]);
$sql = "SELECT *, MATCH ($search_columns) AGAINST (:search_values) AS relevance,
unique_ids.unique_id as uid
FROM $search_table
LEFT JOIN unique_ids
ON unique_ids.unique_id = $search_table.$identifier
WHERE MATCH ($search_columns) AGAINST (:search_values) AND $search_table.language_id = 0 AND unique_ids.status = 2 GROUP BY uid ORDER BY relevance DESC";
$sth = $this->db->prepare($sql . $limit_query);
$sth->execute([
"search_values" => $search_values
]);
while($row = $sth->fetch())
{
$results_arr[] = $row;
foreach ($row as $key => $value)
{
${$key}[] = $value;
}
}
}
// sort array
if($results_arr) array_multisort($relevance, SORT_DESC, $results_arr);
$entries = [];
foreach($results_arr as $entry)
{
$entries[] = $this->getEntryById($entry["page_id"]);
}
return $entries;
}
/*
* Search DB with tag
*/
public function getSearchTag($request, $response, $args)
{
// get topbar navigation
$navigation = $this->getMainNavigation(true);
$current_pagination = isset($args["pagination"]) ? $args["pagination"] : 1;
$limit_query = $this->getQueryLimit($current_pagination);
// get url parameters
$this->item_name = isset($args["name"]) ? $args["name"] : 0;
// get tagged entries
$taggged_entries = $this->getTaggedEntries( [$this->dp($this->item_name)], 0, $limit_query,true);
return $this->view->render($response, 'view.tags.php', array(
"cassette" => $this,
"config" => $this->config,
"router" => $this->router,
"page" => $taggged_entries,
"navigation" => $navigation,
"tagname" => $this->item_name,
"pagination" => [
"pagination_rows_total" => $this->pagination_rows_total,
"pagination_count" => $this->pagination_count,
"current_page" => $current_pagination,
"next_page" => $current_pagination + 1 ,
"previous_page" => $current_pagination - 1
]
));
}
public function getSections()
{
$sql = "SELECT * FROM secties WHERE active = 1";
$sth = $this->db->prepare($sql);
$sth->execute([]);
return $sth->fetchAll();
}
public function exportSite(){
$sections = $this->getSections();
$data = [];
foreach($sections as $s)
{
$entries = $this->getPage($s["naam"]);
$data[$s["naam"]]["entries"] = $entries;
$cats = $this->getCategoriesBySectionId($s["sectie_id"]);
foreach($cats as $c)
{
$entries2 = $this->getPage($s["naam"],$c["category_id"]);
$data[$s["naam"]]["categories"][$c["naam"]] = $entries2;
}
}
echo "<style>img{max-width:100%;}</style>";
foreach($data as $section => $array){
echo "<h1 style=\"color:#006fba;\">". ucfirst($section) ."</h1>";
echo '<hr>';
foreach($array["entries"] as $e)
{
echo $e->getProperty("title","h2");
echo $e->getProperty("contents");
}
foreach($array["categories"] as $c => $entries)
{
echo "<h2 style=\"color:#006fba;\">". $c ."</h2>";
echo '<hr>';
foreach($entries as $e)
{
echo $e->getProperty("title","h2");
echo $e->getProperty("contents");
}
}
}
}
}
?>