File: /var/www/vhosts/creativefellows.nl/education.creativefellows.nl/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;
$client_settings = $this->getClientSettings();
$this->config = new BaseController($settings,$client_settings);
}
/*
* View page
*/
public function viewPage($request, $response, $args)
{
// get topbar navigation
$navigation = $this->getMainNavigation(true,$args["name"]);
// get page contents
$section_name = isset($args["name"]) ? $args["name"] : null;
$page = $this->getPage($section_name);
$this->pageCount = count($page);
// 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->getPageCanvas($page[0]->page_canvas), array(
"cassette" => $this,
"config" => $this->config,
"router" => $this->router,
"page" => $page,
"navigation" => $navigation,
"pagecount" => $this->getPageCount()
));
}
private function getPageCount()
{
return $this->pageCount;
}
/*
* Return a 404 page
*/
private function get404Page($request, $response, $args)
{
$page = [];
$page[] = new EntryController(null,null,null,null,null);
// get topbar navigation
// render 404 page
$this->view->render($response, '404.php',array(
"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";
$sth = $this->db->prepare($sql);
$sth->execute(["url" => $post_url]);
return $sth->fetch();
}
private function getPageCanvas($canvas_name=null)
{
return "view.". $canvas_name .".php";
}
public function getPage($post_url=null)
{
/*
* Default homepage, not post data
*/
if($post_url === null)
{
$section = $this->settings["defaultHomeSection"];
$section = $this->getSection($section);
$uid_array = null;
}
else
{
/*
* Check if post is a section
*/
$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 = $this->getQueryLimit();
/*
* Get toplevel entries
*/
if($uid_array == null)
{
$sql = "SELECT
unique_ids.sectie_id,
unique_ids.template_id,
unique_ids.entry_id,
unique_ids.sortable,
unique_ids.url,
unique_ids.created,
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
$sort_query $limit_query";
$sth = $this->db->prepare($sql);
$sth->execute([
"sectie_id" => $section["sectie_id"]
]);
}
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"]
]);
}
$uids = [];
while($entry = $sth->fetch())
{
//d($entry);
// 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 the form data
$form = $this->getForm($entry_data["form_id"]);
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"]);
}
//die();
$uids[] = new EntryController($entry,$fields,$entry_data,$form,$section["naam"]);
}
return $uids;
}
/*
* get form
*/
private 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);
}
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:
$d["type"] = "datepicker";
$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 18:
$d["type"] = "bijlage";
$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();
}
/*
* Get the html contens on a db field
*/
private function getFieldHTML($unique_id,$field_user,$element,$value,$template_field_id=null)
{
$field_user = preg_replace("/[^0-9a-zA-Z]/","_",strtolower($field_user));
$value = stripslashes($value);
$data = array();
switch($element)
{
default:
return $value;
break;
case "image":
return explode("|*|",$value);
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":
if(trim($value) != "")
{
$files = explode("|*|",$value);
foreach($files as $f)
{
$file_data = explode("|-|",$f);
array_push($data,$file_data);
}
}
else $data = array();
return $data;
break;
case "checkbox":
case "radio":
return explode("|*|",$value);
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) $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}/"),
array($s["html"],$background_style,$s["element_id"],$s["element_class"]),$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) $ss["html"] = $this->getLinkedData($ss);
$section_sub_html .= preg_replace(
array("/{section_htmlwrapper}/","/{style}/","/{element-id}/","/{element-class}/"),
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 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)
{
// 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"]);
// get entries
$entries = $this->getPage($link_data["section"]);
// replace all 1:1
if($link_data["repeat_items"] == 0)
{
$replace_total = substr_count($original_content, $link_data["find"]);
for($i=0; $i<$replace_total; $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"];
$value = $entries[$i]->$field;
if( $tp_fld["array"] == 1 ) $value = $value[$tp_fld["index"]];
$value = $tp_fld["intro"] != 0 ? $this->makeIntro($value,$tp_fld["intro"]) : $value;
$template = preg_replace("/".$tp_fld["template"]."/i", $value, $template);
}
$original_content = $this->str_replace_nth($link_data["find"], $template, $original_content, $i);
}
}
// replace single template by all entries
else
{
$content_str = "";
for($i=0; $i<=$link_data["repeat_items"]; $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"];
$value = $entries[$i]->$field;
if( $tp_fld["array"] == 1 ) $value = $value[$tp_fld["index"]];
$value = $tp_fld["intro"] != 0 ? $this->makeIntro($value,$tp_fld["intro"]) : $value;
$template = preg_replace("/".$tp_fld["template"]."/i", $value, $template);
}
$content_str .= $template;
//$original_content = $this->str_replace_nth($link_data["find"], $template, $original_content, $i);
}
$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();
}
private function getSection($name,$return=null)
{
if(is_numeric($name)) $sql = "SELECT * FROM `secties` LEFT JOIN stramiens ON secties.stramien = stramiens.stramien_id WHERE secties.active = '1' AND secties.sectie_id = :name";
else $sql = "SELECT * 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)";
$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;
}
private 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($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");
}
}
?>