<?php
/**
 * Visformsdata model for Visforms
 *
 * @author       Aicha Vack
 * @package      Joomla.Site
 * @subpackage   com_visforms
 * @link         https://www.vi-solutions.de
 * @license      GNU General Public License version 2 or later; see license.txt
 * @copyright    2012 vi-solutions
 * @since        Joomla 1.6
 */

namespace Visolutions\Component\Visforms\Site\Model;

// no direct access
defined('_JEXEC') or die('Restricted access');

use Joomla\CMS\Factory;
use Joomla\CMS\Form\FormHelper;
use Joomla\CMS\Language\Text;
use Joomla\CMS\MVC\Factory\MVCFactoryInterface;
use Joomla\Utilities\ArrayHelper;
use Joomla\Registry\Registry;
use Joomla\CMS\Pagination\Pagination;
use Joomla\CMS\HTML\HTMLHelper;
use Visolutions\Component\Visforms\Administrator\Service\HTML\Visformsselect;
use Joomla\Database\ParameterType;

class VisformsdataModel extends BaseVisformsdataModel {
	protected $datafields;
	protected $id;
	protected $detail;
	public $pparams;
	// dot free string to use as request-prefix and context for pagination, search-filter and sort fields
	public $paginationcontext;
	public $displayedDefaultDbFields = array();
	public $visform;
	public $pluginfieldlist;
	protected $radius = array('km' => 6371, 'usmiles' => 3959);
	protected $unSortable = array('signature');
	protected $fieldOrder;

	public function __construct($config = array(), MVCFactoryInterface $factory = null) {
		if (!empty($config['formid'])) {
			$id = $config['formid'];
		} 
		else {
			$id = Factory::getApplication()->input->getInt('id', -1);
		}
		$this->setId($id);
		if (isset($config['context']) && $config['context'] != "") {
			$this->context = $config['context'];
		}
		parent::__construct($config, $factory);

		// create/ues a unique context which is used to distinguish multiple adminForms on one page
		$itemid = $this->getMenuId();
		if (!empty($config['mid'])) {
			$itemid = $config['mid'];
		}

		$this->paginationcontext = str_replace('.', '_', $this->context . '_' . $itemid . '_' . $id . '_');

		if (isset($config['pparams']) && is_array($config['pparams'])) {
			$this->pparams = $config['pparams'];
		}

		// get an array of field names that can be used as search filter fields
		// basically we could list all possible filter fields for the form, but we keep it clean
		// which fields are actually used as filters is set in getFilterForm()
		if (empty($config['filter_fields'])) {
			$config['filter_fields'] = array();
		}
		if (isset($this->pparams) && is_array($this->pparams)) {
			$params = new Registry;
			$params->loadArray($this->pparams);
		} 
		else {
			$params = Factory::getApplication()->getParams();
		}
		$this->fieldOrder = $params->get('fieldorder', 'ordering');
		$this->datafields = $this->getDatafields();
		$fields = $this->datafields;
		if (!empty($fields)) {
			// add field id's to filter_fields
			foreach ($fields as $field) {
				if (in_array($field->typefield, array('select', 'radio', 'multicheckbox', 'checkbox', 'selectsql', 'radiosql', 'multicheckboxsql')) && !empty($field->isfilterfield)) {
					$config['filter_fields'][] = $this->paginationcontext . $field->name;
				}
				if ($field->typefield === 'location') {
					$config['filter_fields'][] = $this->paginationcontext . $field->name;
					$config['filter_fields'][] = $this->paginationcontext . $field->name . '_radius';
				}
				if ($field->typefield === 'date' && !empty($field->isfilterfield)) {
					$config['filter_fields'][] = $this->paginationcontext . $field->name . '_min';
					$config['filter_fields'][] = $this->paginationcontext . $field->name . '_max';
				}
			}
		}
		if ($canPublish = $this->canPublish()) {
			$config['filter_fields'][] = $this->paginationcontext . 'published';
		}
		if ($params->get('show_filter_created')) {
			$config['filter_fields'][] = $this->paginationcontext . 'mincreated';
			$config['filter_fields'][] = $this->paginationcontext . 'maxcreated';
		}

		$this->visform = $this->getForm();
		$this->displayedDefaultDbFields = $this->setDisplayedDefaultDbFields();
		if (array_key_exists('ismfd', $this->displayedDefaultDbFields)) {
			$config['filter_fields'][] = $this->paginationcontext . 'ismfd';
		}
		$this->pluginfieldlist = $this->setPluginFieldList();
		if (isset($config['filter_fields'])) {
			$this->filter_fields = $config['filter_fields'];
		}
	}

	// must stay public
	public function setId($id) {
		// set id and wipe data
		$this->id = $id;
	}

	public function getId() {
		return $this->id;
	}

	protected function populateState($ordering = null, $direction = null) {
		// initialize variables
		$app = Factory::getApplication();
		$itemid = 0;
		if (isset($this->pparams) && is_array($this->pparams)) {
			$params = new Registry;
			$params->loadArray($this->pparams);
		} 
		else {
			$params = $app->getParams();
			if ($menu = $this->getMenuItem()) {
				$itemid = ($menu->id) ? $menu->id : 0;
			}
		}
		$this->setState('params', $params);
		$this->setState('itemid', $itemid);
		// Param count comes from plugin, if we have a list view with a limited fix amount of recordsets
		$count = $params->get('count');
		$limit = (isset($count) && is_numeric($count)) ? intval($count) : $params->get('display_num', 20);
		$value = $app->input->get($this->paginationcontext . 'limit', $limit, 'uint');
		$this->setState('list.limit', $value);
		$value = $app->getUserStateFromRequest($this->paginationcontext . '.limitstart', $this->paginationcontext . 'limitstart', 0, 'uint');
		$app->setUserState($this->paginationcontext . '.limitstart', $value);
		$this->setState('list.start', $value);
		// only filters of form that was submitted are in request
		$requestFilters = $app->input->get('filter', array(), 'array');
		// stored filters of currently processed form
		$sessionFilters = $app->getUserState($this->paginationcontext . '.filter', array());
		$newFilters = array();
		$filtersChanged = false;
		// set filters of currently processed form according to stored session values and request values
		// note: filters in session are stored as array, filters in model state are stored as objects with name filter.filtername
		foreach ($requestFilters as $name => $value) {
			if (str_contains($name, $this->paginationcontext)) {
				$filtername = str_replace($this->paginationcontext, '', $name);
				// Exclude if blacklisted
				if (!in_array($name, $this->filterBlacklist)) {
					$newFilters[$name] = $value;
					$app->setUserState($this->paginationcontext . '.filter.' . $name, $value);
					$this->setState('filter.' . $filtername, $value);
					$filtersChanged = true;
				}
			}
		}
		if ($filtersChanged) {
			$app->setUserState($this->paginationcontext . '.filter', $newFilters);
		} 
		else {
			foreach ($sessionFilters as $name => $value) {
				// use stored filters as state filter
				if (str_contains($name, $this->paginationcontext)) {
					$filtername = str_replace($this->paginationcontext, '', $name);
					$this->setState('filter.' . $filtername, $value);
				}
			}
		}
		// out of the box, with Joomla! it is not possible to have more than one sortable table on a page (no prefix supported as for pagination), so one request can only handle one value for each parameter
		// we add a unique context everywhere to distinguish between different adminForms and make sure that always the right filter_order and filter_order_dir control is filled in the admin form
		$ordering = $app->getUserStateFromRequest($this->paginationcontext . '.ordering', $this->paginationcontext . 'filter_order', $this->getOrderingParamNameFromParams($params), 'string');
		$this->setState('list.ordering', $ordering);
		$direction = strtolower($app->getUserStateFromRequest($this->paginationcontext . '.direction', $this->paginationcontext . 'filter_order_Dir', $params->get('sortdirection', 'asc'), 'string'));
		$this->setState('list.direction', $direction);
		// filter.vfsortording is always submitted through the javascript that is used to order data on click on table header.
		// therefore, we can set the state and user state directly from the $ordering and $direction, without checking the old sessionFilter values
		$this->setState('filter.vfsortordering', $ordering . ' ' . $direction);
		$app->setUserState($this->paginationcontext . '.filter.'.$this->paginationcontext.'vfsortordering', $ordering . ' ' . $direction);
	}

	protected function getOrderingParamNameFromParams($params) {
		$test = $params->get('sortorder', 'id');
		if (is_numeric($test)) {
			return 'a.F'.$test;
		}
		if (!str_starts_with($test, 'a.')) {
			return 'a.'.$test;
		}
		return $test;
	}

	public function getPagination() {
		// get a storage key.
		$store = $this->getStoreId('getPagination');

		// try to load the data from internal storage.
		if (isset($this->cache[$store])) {
			return $this->cache[$store];
		}

		// create the pagination object.
		$limit = (int) $this->getState('list.limit') - (int) $this->getState('list.links');
		$page = new Pagination($this->getTotal(), $this->getStart(), $limit, $this->paginationcontext);

		// add the object to the internal cache.
		$this->cache[$store] = $page;

		return $this->cache[$store];
	}

	protected function getStoreId($id = '') {
		// compile the store id.
		$id .= ':' . $this->getState('filter.search');
		return parent::getStoreId($id);
	}

	protected function getListQuery() {
		// create a new query object.
		$db = $this->getDbo();
		$query = $db->getQuery(true);
		$user = Factory::getApplication()->getIdentity();
		$userId = $user->get('id');
		$canDo = \VisformsHelper::getActions($this->id);
		$canPublish = $this->canPublish();
		$canPublishOwn= $this->canPublish('.own');
		$fields = $this->datafields;
		$menu_params = $this->getState('params', new Registry());
		$layout = Factory::getApplication()->input->get('layout', 'data', 'string');
		$isEditLayout = ($layout == "detailedit" || $layout == "dataeditlist") ? true : false;
		$editableonly = ($isEditLayout) ? $menu_params->get('editableonly', 1) : $menu_params->get('editableonly', 0);

		// detail view in content plugin data view uses the component detail view
        // it is created, by loading the detail view via ajax
        // list box for document download in detail view checks, for each item, if a pdf download is available
        // detail item can be any stored record set,
        // so we need a full list of all items
        // set limit and start to 0
        // the ajay request uniquely has an url param loadedApi = gMap
		if (Factory::getApplication()->getInput()->get('loadedApi', '', 'cmd') === 'gMap') {
		    $this->setState('list.limit', 0);
		    $this->setState('list.start', 0);
        }
		// select the required fields from the table
		$query->select($this->getState('list.select', 'a.*'))
        ->select( [$db->quoteName('ue.name', 'editor'),$db->quoteName('uc.name', 'creator')]);
		$tn = "#__visforms_" . $this->id;
		$query->from($db->quoteName($tn) . ' AS a')
            ->join('LEFT', $db->quoteName('#__users', 'ue'), $db->quoteName('ue.id') . ' = ' . $db->quoteName('a.modified_by'))
            ->join('LEFT', $db->quoteName('#__users', 'uc'), $db->quoteName('uc.id') . ' = ' . $db->quoteName('a.created_by'));
		if (!empty($canPublish) || !empty($canPublishOwn)) {
			$searchFilter = $this->getState('filter.' . 'published');
			// search filter is set
			if ((isset($searchFilter)) && ($searchFilter != '') && in_array((int) $searchFilter, array(0,1))) {
			    // can only publish own; only display published records an unpublished own records according to filter settings
			    if (empty($canPublish) && (int) $searchFilter === 0) {
                    $query->where($db->quoteName('a.published') . ' = ' . $searchFilter . ' AND ' . $db->quoteName('a.created_by') . " = " . $userId);
                }
			    // search-filter is set to published. Show all published record sets
			    else  {
                    $query->where($db->quoteName('a.published') . ' = ' . $searchFilter);
                }
			}
			// no search-filter set and user canPublishOwn
            // option "editable only" is stronger than can Publish. If it is enabled. Do not select by userid then
            else if (empty($canPublish) && !($editableonly == 1)) {
                $extraWhere = '';
                if (!($editableonly == 1)) {
                    $extraWhere = ' OR (' . $db->quoteName('a.published') .' = ' . 0 . ' AND '. $db->quoteName('a.created_by') . ' = ' . $userId.')';
                }

                $query->where($db->quoteName('a.published') . ' = ' . 1 . $extraWhere);
            }
            else {
                $query->where($db->quoteName('a.published') . ' IN (0,1)');
            }
		}
		else {
			$query->where($db->quoteName('a.published') . ' = ' . 1);
		}
		// only use the items specified in the field-select list
		if (isset($this->pparams['fieldselect']) && is_array($this->pparams['fieldselect']) && (!empty($fields))) {
			foreach ($this->pparams['fieldselect'] as $name => $value) {
			    if ($name === 'created' && $value === 'currentdate') {
                    $query->where('DATE_FORMAT(a.created, \'%Y-%m-%d\') = CURDATE()');
                }
				else if (is_numeric($name)) {
					$name = "F" . $name;
                    $name = $db->escape($name);
                    $value = $db->escape($value);
                    foreach ($fields as $field) {
                        // different approach for fields with multi select options
                        if ('F' . $field->id == $name) {
                            if (in_array($field->typefield, array('select', 'multicheckbox', 'selectsql', 'multicheckboxsql'))) {
                                $viewSelection = '%' .Visformsselect::$msdbseparator . $value . Visformsselect::$msdbseparator . '%';
                                $storedSelections = $query->concatenate(array($db->q(Visformsselect::$msdbseparator), $db->quoteName('a.'.$name), $db->q(Visformsselect::$msdbseparator)));
                                $query->where('(' . $storedSelections . ' like ' . $db->q($viewSelection) . ')');
                            }
                            else {
                                $query->where($db->quoteName('a.'.$name) . " = " . $db->quote($value), "AND");
                            }
                        }
                    }
				}
			}
		}

		if (!empty(\VisformsAEF::checkAEF(\VisformsAEF::$subscription))) {
			if ($editableonly == 1) {
				if ($canDo->get('core.edit.data')) {
					// get all record sets
				}
				else if ($canDo->get('core.edit.own.data')) {
					$query->where($db->quoteName('a.created_by') . " = " . $userId);
				}
				else {
					// don't return any record sets, userid -1 does never exist
					$query->where($db->quoteName('a.created_by') . " = -1 ");
				}
			}
		}
		if (!empty($this->visform->ownrecordsonly) && !($isEditLayout)) {
			if (!empty($userId)) {
				$query->where($db->quoteName('a.created_by') . " = " . $userId);
			}
			else {
				// don't return any record sets
				$query->where($db->quoteName('a.created_by') . " = -1 ");
			}
		}
		// filter by search
        $query = $this->getFilter($query);
		if (!empty($fields)) {
			// apply select filter selections
			foreach ($fields as $field) {
				// in plugin context use only fields which ar in the plugin field display list
				if ((!empty($this->pparams)) && (!(in_array($field->id, $this->pluginfieldlist)))) {
					continue;
				}
				if (in_array($field->typefield, array('select', 'radio', 'multicheckbox', 'selectsql', 'radiosql', 'multicheckboxsql')) && !empty($field->isfilterfield)) {
					$selectFilters = $this->getState('filter.' . $field->name);
                    if (!\is_array($selectFilters)) {
                        $selectFilters = $selectFilters ? [$selectFilters] : [];
                    }
                    $subSelectFilterWhere = [];
                    if (\count($selectFilters)) {
                        foreach ($selectFilters as $selectFilter) {
                            // 0 is a valid option
                            if ((!isset($selectFilter)) || ($selectFilter === '')) {
                                continue;
                            }
                            $selectFilter = $db->escape($selectFilter);
                            // select recordsets
                            $viewSelection = '%' .Visformsselect::$msdbseparator . $selectFilter . Visformsselect::$msdbseparator . '%';
                            $storedSelections = $query->concatenate(array($db->q(Visformsselect::$msdbseparator), $db->quoteName('a.F' . $field->id), $db->q(Visformsselect::$msdbseparator)));
                            $subSelectFilterWhere[] = '(' . $storedSelections . ' like ' . $db->q($viewSelection) . ')';
                        }
                        // searching for an exact match from a multiselection is not supported and difficult to implement
                        $condition = (!empty($field->multiselectsearchfiltercondition)) ? ' AND ' : ' OR ';
                        $query->where('(' . implode($condition, $subSelectFilterWhere) . ')');
                        continue;
                    }
				}
				// checkbox
				if ($field->typefield == 'checkbox' && !empty($field->isfilterfield)) {
					$selectFilter = $this->getState('filter.' . $field->name);
					if ((!isset($selectFilter)) || ($selectFilter === '')) {
						continue;
					}
                    $selectFilter = $db->escape($selectFilter);
					if ($selectFilter == 'checked') {
						$query->where($db->quoteName('a.F'. $field->id) .' = ' . $db->q($field->attribute_value));
					}
					else {
						$query->where('NOT' . $db->quoteName('a.F'. $field->id) .' = ' . $db->q($field->attribute_value));
					}
				}
				// radius search
				if ($field->typefield === "location" && !empty($field->allowferadiussearch)) {
					$selectFilterLocation = $this->getState('filter.' . $field->name . '_location');
                    $selectFilterLocation = HTMLHelper::_('visformslocation.extractDbValue', $selectFilterLocation);
					$selectFilterRadius = $this->getState('filter.' . $field->name . '_radius');
					// empty radius means everywhere
					if (empty($selectFilterRadius) || !isset($selectFilterLocation['lat']) || $selectFilterLocation['lat'] === "" || !isset($selectFilterLocation['lng']) || $selectFilterLocation['lng'] === "") {
						continue;
					}
                    $selectFilterLocation['lat'] = $db->escape($selectFilterLocation['lat']);
                    $selectFilterLocation['lng'] = $db->escape($selectFilterLocation['lng']);
                    $selectFilterRadius = $db->escape($selectFilterRadius);
					$earthRadius = (!empty($field->distanceunit) && isset($this->radius[$field->distanceunit])) ? $this->radius[$field->distanceunit] : $this->radius['km'];
					$query->where("SUBSTRING(SUBSTRING_INDEX(a.F" . $field->id . ", ',', 1),9, (LENGTH(SUBSTRING_INDEX(a.F" . $field->id . ", ',', 1))-9)) != '' ");
					$query->where("SUBSTRING(SUBSTRING_INDEX(a.F" . $field->id . ", ',', -1),8, (LENGTH(SUBSTRING_INDEX(a.F" . $field->id . ", ',', 1))-9)) != '' ");
					$query->where($earthRadius . " * acos( cos( radians(" . $selectFilterLocation['lat'] . "*1) ) * cos( radians( (SUBSTRING(SUBSTRING_INDEX(a.F" . $field->id . ", ',', 1),9, (LENGTH(SUBSTRING_INDEX(a.F" . $field->id . ", ',', 1))-9))) *1 ) ) * cos( radians(" . $selectFilterLocation['lng'] . "*1 ) - radians((SUBSTRING(SUBSTRING_INDEX(a.F" . $field->id . ", ',', -1),8, (LENGTH(SUBSTRING_INDEX(a.F" . $field->id . ", ',', 1))-9)))*1) ) + sin( radians(" . $selectFilterLocation['lat'] . "*1) ) * sin( radians( (SUBSTRING(SUBSTRING_INDEX(a.F" . $field->id . ", ',', 1),9, (LENGTH(SUBSTRING_INDEX(a.F" . $field->id . ", ',', 1))-9)))*1 ) ) ) < " . $selectFilterRadius);
				}
				if ($field->typefield === "date" && !empty($field->isfilterfield)) {
					$formats = explode(';', $field->format);
					$format = $formats[1];
					$searchFilter = $this->getState('filter.' . $field->name . '_min');
					if ((isset($searchFilter)) && ($searchFilter != '') && !empty($searchFilter)) {
                        $searchFilter = $db->escape($searchFilter);
						$query->where(' STR_TO_DATE(' . $db->quoteName('a.F'. $field->id)  . ', ' . $db->quote($format) . ')  >  STR_TO_DATE(' . $db->q($searchFilter)  . ', ' . $db->quote($format) . ')');
					}
					$searchFilter = $this->getState('filter.' . $field->name . '_max');
					if ((isset($searchFilter)) && ($searchFilter != '') && !empty($searchFilter)) {
                        $searchFilter = $db->escape($searchFilter);
						$query->where(' STR_TO_DATE(' . $db->quoteName('a.F'. $field->id)  . ', ' . $db->quote($format) . ')  <  STR_TO_DATE(' . $db->q($searchFilter)  . ', ' . $db->quote($format) . ')');
					}
				}
			}
		}
		if ((!empty($this->displayedDefaultDbFields)) && isset($this->displayedDefaultDbFields['ismfd'])) {
			$searchFilter = $this->getState('filter.' . 'ismfd');
			if ((isset($searchFilter)) && ($searchFilter != '')) {
                $searchFilter = (int) $searchFilter;
				$query->where($db->quoteName('a.ismfd') . ' = :ismfd')
                    ->bind(':ismfd', $searchFilter, ParameterType::INTEGER);
			}
		}
		$searchFilter = $db->escape($this->getState('filter.' . 'mincreated'));
		if ((isset($searchFilter)) && ($searchFilter != '') && $searchFilter != $db->getNullDate()) {
            $searchFilter = $db->escape($searchFilter);
			$searchFilter = Factory::getDate($searchFilter)->toSql();
			$query->where($db->quoteName('a.created') . ' > ' . $db->q($searchFilter));
		}
		$searchFilter = $this->getState('filter.' . 'maxcreated');
		if ((isset($searchFilter)) && ($searchFilter != '') && $searchFilter != $db->getNullDate()) {
            $searchFilter = $db->escape($searchFilter);
			$searchFilter = Factory::getDate($searchFilter)->toSql();
			$query->where($db->quoteName('a.created') . ' < ' . $db->q($searchFilter));
		}
		// add the list ordering clause
		$orderCol = $this->state->get('list.ordering', 'id');
		if (is_numeric($orderCol)) {
			$orderCol = "F" . $orderCol;
		}
        $orderCol = $db->escape($orderCol);
		$this->setState('list.ordering', $orderCol);
		$orderDirn = $this->state->get('list.direction', 'asc');
		// we store dates as strings in database. If sort order field is of type date we have to convert the strings before we order the recordsets
		if (!empty($fields)) {
			foreach ($fields as $field) {
				$fname = 'F' . $field->id;
				if (($field->typefield == 'date') && (($orderCol == $fname) || ($orderCol == 'a.' . $fname))) {
					$formats = explode(';', $field->format);
					$format = $formats[1];
					$orderCol = ' STR_TO_DATE(' . $orderCol . ', ' . $db->quote($format) . ') ';
					break;
				}
				if ((($field->typefield == 'number') || ($field->typefield == 'calculation')) && (($orderCol == $fname) || ($orderCol == 'a.' . $fname))) {
					$orderCol = '(' . $orderCol . ' * 1)';
				}
			}
		}
		$query->order($orderCol . ' ' . $db->escape($orderDirn));
		return $query;
	}

	public function getDatafields() {
		// load the data if it doesn't already exist
		// exclude all field-types that should not be published in frontend (submits, resets, fieldseparator)
		$datafields = $this->datafields;
		if (empty($datafields)) {
			$fieldorder = (!empty($this->fieldOrder)) ? $this->fieldOrder : 'ordering';
			$db = Factory::getDbO();
			$user = Factory::getApplication()->getIdentity();
			$groups = $user->getAuthorisedViewLevels();
			$frontAccess = implode(", ", $groups);
			$excludedFieldTypes = "'reset', 'submit', 'image', 'fieldsep'";
			$query = $db->getQuery(true);
			$query->select('*')
				->from($db->quoteName('#__visfields'))
				->where($db->quoteName('fid') . " = " . $this->id)
				->where($db->quoteName('published') . ' = ' . 1)
				->where($db->quoteName('frontaccess') . " in (" . $frontAccess . ")")
				->where($db->quoteName('typefield') . "not in (" . $excludedFieldTypes . ")")
				->where('(' . $db->qn('frontdisplay') . ' is null or ' . $db->qn('frontdisplay') . ' in (1,2,3))')
				->order($db->quoteName($fieldorder) . " asc");
			try {
				$db->setQuery($query);
				$datafields = $db->loadObjectList();
			}
			catch (\Exception $ex) {

			}
			$n = count($datafields);
			for ($i = 0; $i < $n; $i++) {

				$datafields[$i]->defaultvalue = \VisformsHelper::registryArrayFromString($datafields[$i]->defaultvalue);
				foreach ($datafields[$i]->defaultvalue as $name => $value) {
					// make names shorter and set all default values as properties of field object
					$prefix = 'f_' . $datafields[$i]->typefield . '_';
					if (str_contains($name, $prefix)) {
						$key = str_replace($prefix, "", $name);
						$datafields[$i]->$key = $value;
					}
				}

				// delete defaultvalue array
				unset($datafields[$i]->defaultvalue);
				if (in_array($datafields[$i]->typefield, $this->unSortable)){
					$datafields[$i]->unSortable = true;
				} 
				else {
					$datafields[$i]->unSortable = false;
				}
                if ($datafields[$i]->typefield == 'file') {
                    if (empty(\VisformsAEF::checkAEF(\VisformsAEF::$subscription))) {
                        $datafields[$i]->displayImgAsImgInList = 0;
                        $datafields[$i]->displayImgAsImgInDetail = 0;
                    }
                }
			}
			$this->datafields = $datafields;
		}
		return $datafields;
	}

	public function getDetail() {
		$db = Factory::getDbO();
		$app = Factory::getApplication();
		$cIds = $app->input->get('cid', array(), 'ARRAY');
		ArrayHelper::toInteger($cIds);
		$menu_params = $this->getState('params', new Registry());
		$layout = $app->input->get('layout', 'data', 'string');
		$isEditLayout = ($layout == "detailedit" || $layout == "dataeditlist") ? true : false;
		$editableonly = ($isEditLayout) ? $menu_params->get('editableonly', 1) : $menu_params->get('editableonly', 0);
        $task = $app->input->get('task');
        $isPdfTask = ($task === 'renderPdf' || $task === 'renderPdfList');
		$canPublish = $this->canPublish();
		$canPublishOwn = $this->canPublish('.own');
		$canDo = \VisformsHelper::getActions($this->id);
		$id = (int) $cIds[0];
		$user = Factory::getApplication()->getIdentity();
		$userId = $user->get('id');
		$query = $db->getQuery(true);
		$query->select('a.*')
            ->select( [$db->quoteName('ue.name', 'editor'),$db->quoteName('uc.name', 'creator')])
                ->from($db->quoteName('#__visforms_' . $this->id) . ' AS a')
                ->join('LEFT', $db->quoteName('#__users', 'ue'), $db->quoteName('ue.id') . ' = ' . $db->quoteName('a.modified_by'))
                ->join('LEFT', $db->quoteName('#__users', 'uc'), $db->quoteName('uc.id') . ' = ' . $db->quoteName('a.created_by'))
			    ->where($db->quoteName('a.id') . " = " . $id);

		// if a user can publish/unpublish a recordset, unpublished recordsets are displayed in the list view and the user can display a details view of the unpublished record as well
		if (empty($canPublish) && empty($canPublishOwn)) {
			$query->where($db->quoteName('a.published') . ' = ' . 1);
		}
		else if (empty($canPublish)) {
            $query->where($db->quoteName('a.published') . ' = '. 1 . ' OR (' . $db->quoteName('a.published') .' = '. 0 .' AND '. $db->quoteName('a.created_by') . ' = ' . $userId.')');
        }
        else {
            $query->where($db->quoteName('a.published') . ' IN (0,1)');
        }

		if (!empty(\VisformsAEF::checkAEF(\VisformsAEF::$subscription))) {
			if ($editableonly == 1) {
				if ($canDo->get('core.edit.data')) {
					// get all record sets
				}
				else if ($canDo->get('core.edit.own.data')) {
					$query->where($db->quoteName('a.created_by') . " = " . $userId);
				}
				else {
					// don't return any record sets
					$query->where($db->quoteName('a.created_by') . " = -1 ");
				}
			}
		}

		// when getting data for pdf in data views $isEditLayout is always true and this code section is ignored
		if (!empty($this->visform->ownrecordsonly) && !($isEditLayout)) {
			if (!empty($userId)) {
				$query->where($db->quoteName('a.created_by') . " = " . $userId);
			} else {
				// don't return any record sets
				$query->where($db->quoteName('a.created_by') . " = -1 ");
			}
		}

		try {
			$db->setQuery($query);
			$detail = $db->loadObject();
		}
		catch (\Exception $ex) {
			return false;
		}

		// for fields of type select, radio, multicheckbox and checkbox display option label in data view instead of the stored option values
        // do not replace values in data, if data are used to create a pdf $isPdfTask
        // in pdfTasks, placeholder replacement will output the correct value
		if (!empty($detail) && !$isPdfTask) {
            if (empty($detail->creator)) {
                $detail->creator = '';
            }
            if (empty($detail->editor)) {
                $detail->editor = '';
            }
			$fields = $this->datafields;
			foreach ($fields as $field) {
				$detailFieldName = "F" . $field->id;
				if (in_array($field->typefield, array('select', 'radio', 'multicheckbox'))) {
					$detailFieldValue = $detail->$detailFieldName;
					if ((!isset($detailFieldValue)) || ($detailFieldValue === '') || (empty($field->list_hidden))) {
						continue;
					}
					$newExtractedItemFieldValues = HTMLHelper::_('visformsselect.mapDbValueToOptionLabel', $detailFieldValue, $field->list_hidden);
					$newItemFieldValue = implode('<br />', $newExtractedItemFieldValues);
					$detail->$detailFieldName = $newItemFieldValue;
				}
				if (in_array($field->typefield, array('selectsql', 'radiosql', 'multicheckboxsql'))) {
					$detailFieldValue = $detail->$detailFieldName;
					if ((!isset($detailFieldValue)) || ($detailFieldValue === '')) {
						continue;
					}
					$newExtractedItemFieldValues = HTMLHelper::_('visformsselect.mapDbValueToSqlOptionLabel', $detailFieldValue, $field->sql);
					$newItemFieldValue = implode('<br />', $newExtractedItemFieldValues);
					$detail->$detailFieldName = $newItemFieldValue;
				}

                if ($field->typefield == 'location') {
                    $detail->$detailFieldName = \VisformsHelper::registryArrayFromString($detail->$detailFieldName);
                    if (!empty($field->displayAsMapInDetail)) {
                        $detail->requiresJs = true;
                    }
                }
		    }
		}
		return $detail;
	}

	public function getForm() {
		$form = $this->visform;
		$hasSub = \VisformsAEF::checkAEF(\VisformsAEF::$subscription);
		if (empty($form)) {
			$db = Factory::getDbO();
			$query = $db->getQuery(true);
			$query->select('*')
				->from($db->quoteName('#__visforms'))
				->where($db->quoteName('id') . " = " . $this->id)
				->where($db->quoteName('published') . ' = ' . 1)
				->where($db->quoteName('saveresult') . ' = ' . 1);
			$db->setQuery($query);
			$form = $db->loadObject();
			if (empty($form)) {
				return $form;
			}
			// convert frontendsettings (= data view settings) to an array
            // fix invalid subscription option values
			$form->frontendsettings = \VisformsHelper::registryArrayFromString($form->frontendsettings);
			foreach ($form->frontendsettings as $name => $value) {
				if (($name == 'ownrecordsonly') && (empty($hasSub))) {
					$value = 0;
				}
				if (($name == 'displaycounter') && (empty($hasSub))) {
					$value = 0;
				}
                if (($name == 'displaycreatedby') && (empty($hasSub))) {
                    $value = 0;
                }
                if (($name == 'displaymodifiedby') && (empty($hasSub))) {
                    $value = 0;
                }
                if (($name == 'singleRecordPdfTemplate') && (empty($hasSub))) {
                    $value = array();
                }
                if (($name == 'listPdfTemplate') && (empty($hasSub))) {
                    $value = array();
                }
				// make names shorter and set all frontendsettings as properties of form object
				$form->$name = $value;
			}
			$form->mapCounter = 0;
			$form->hasLocationRadiusSearch = false;
			$form = $this->cleanForm($form);
		}
		return $form;
	}

	// add missing values
    // fix invalid types
	protected function cleanForm($form) {
		if (!isset($form->displaydetail)) {
			$form->displaydetail = 0;
		}
		if (!isset($form->hideemptyfieldsindetail)) {
			$form->hideemptyfieldsindetail = 0;
		}
		$this->getPdfTemplates();
		// Fix: backwards incompatibility: Until 5.2.0 only one template could be selected. Value is string.
        // convert to array
        $form->singleRecordPdfTemplate = (!empty($form->singleRecordPdfTemplate)) ? \VisformsHelper::fixInvalidMultiSelectOption($form->singleRecordPdfTemplate) : array();
        // Fix: backwards incompatibility: Until 5.2.0 only one template could be selected. Value is string.
        // convert to array
        $form->listPdfTemplate = (!empty($form->listPdfTemplate)) ? \VisformsHelper::fixInvalidMultiSelectOption($form->listPdfTemplate) : array();
		// each data view has to check, whether they have to display the overhead fields or not
		// use only one boolean option for each view type, and make sure the option is set
		$displayParameters = array ('displayip', 'displayid', 'displaycreated', 'displaycreatedby', 'displaycreatedtime', 'displayismfd', 'displaymodifiedat', 'displaymodifiedattime', 'displaymodifiedby', 'displaypdfexportbutton');
		foreach ($displayParameters as $parameter) {
			if (!isset($form->$parameter)) {
				$form->$parameter = 0;
			}
			// display in list view
			$listParamName = $parameter . '_list';
			$form->$listParamName = ($form->$parameter == "1" || $form->$parameter == "2") ? true : false;
			// display in detail view
			$detailParamName = $parameter . '_detail';
			$form->$detailParamName = ($form->$parameter == "1" || $form->$parameter == "3") ? true : false;
			// display in any view of content plugin data view
			$plgParamName = $parameter . '_plg';
			$form->$plgParamName = (!empty($this->pparams) && $this->pparams[$parameter] == "true" && ($form->$parameter == "1" || $form->$parameter == "2" || $form->$parameter == "3")) ? true : false;
		}
		return $form;
	}

	private function getMenuItem() {
		$app = Factory::getApplication();
		$menu = $app->getMenu()->getActive();
		$lang = $app->getLanguage();
		if (!$menu) {
			$menu = $app->getMenu()->getDefault($lang->getTag());
		}

		return $menu;
	}

	public function getFilterForm($data = array(), $loadData = true) {
		$menuParams = $this->state->get('params');
		// we need to add the path explicitly for use with plugin dataview
		FormHelper::addFieldPath("Visolutions\Component\Visforms\Site\Field");
		FormHelper::addFormPath(JPATH_ROOT ."/components/com_visforms/forms");
		$form = parent::getFilterForm($data, false);
		if (!empty($form)) {
			$fields = $this->datafields;
			$searchFieldXml = new \SimpleXMLElement('<field
                name="search"
                type="text"
                label="COM_VISFORMS_FILTER_SEARCH_DESC"
                labelclass="visually-hidden sr-only uk-invisible"
                hint="JSEARCH_FILTER"
            />');
			$form->setField($searchFieldXml, 'filter');
			$form->setFieldAttribute('search', 'name', $this->paginationcontext . 'search', 'filter');
			$showCreatedFilter = $menuParams->get('show_filter_created', false);
			if ($showCreatedFilter === "true" || $showCreatedFilter == 1) {
                $dateformat = Text::_('DATE_FORMAT_LC4');
                $mySqlDateFormat = str_replace('d', '%d', str_replace('m', '%m', str_replace('Y', '%Y', $dateformat)));
				$minCreatedFieldXml = new \SimpleXMLElement('<field
                name="mincreated"
                type="calendar"
                label="COM_VISFORMS_FILTER_CREATED_AFTER"
                hint="COM_VISFORMS_FILTER_CREATED_AFTER"
                format="'.$mySqlDateFormat.'"
                translateformat="true"
                >
            </field>');
				$form->setField($minCreatedFieldXml, 'filter');
				$form->setFieldAttribute('mincreated', 'name', $this->paginationcontext . 'mincreated', 'filter');
				$maxCreatedFieldXml = new \SimpleXMLElement('<field
                name="maxcreated"
                type="calendar"
                label="COM_VISFORMS_FILTER_CREATED_BEFORE"
                hint="COM_VISFORMS_FILTER_CREATED_BEFORE"
                format="'.$mySqlDateFormat.'"
                translateformat="true"
                >
            </field>');
				$form->setField($maxCreatedFieldXml, 'filter');
				$form->setFieldAttribute('maxcreated', 'name', $this->paginationcontext . 'maxcreated', 'filter');
			}
			if (array_key_exists('ismfd', $this->displayedDefaultDbFields)) {
				$isMfdFieldXml = new \SimpleXMLElement('<field
                    name="ismfd"
                    type="list"
                    class="form-control"
                    label="COM_VISFORMS_FILTER_ISMFD_DESCR"
                    description="COM_VISFORMS_FILTER_ISMFD_DESCR"
                    labelclass="visually-hidden sr-only uk-invisible"
                    >
                    <option value="">COM_VISFORMS_OPTION_SELECT_ISMFD</option>
                    <option value="1">
                            JYES</option>
                        <option value="0">
                            JNO</option>
                </field>');
				$form->setField($isMfdFieldXml, 'filter');
				$form->setFieldAttribute('ismfd', 'name', $this->paginationcontext . 'ismfd', 'filter');
			}
			$canPublish = $this->canPublish() || $this->canPublish('.own');
			if (!empty($canPublish)) {
				$publishedFieldXml = new \SimpleXMLElement('<field
                    name="published"
                    type="list"
                    class="form-control"
                    label="COM_VISFORMS_FILTER_PUBLISHED"
                    description="COM_VISFORMS_FILTER_PUBLISHED_DESC"
                    labelclass="visually-hidden sr-only uk-invisible"
                    >
                    <option value="">JOPTION_SELECT_PUBLISHED</option>
                    <option value="1">
                            JPUBLISHED</option>
                        <option value="0">
                            JUNPUBLISHED</option>
                </field>');
				$form->setField($publishedFieldXml, 'filter');
				$form->setFieldAttribute('published', 'name', $this->paginationcontext . 'published', 'filter');
			}
            $xml =
                '<field
            name="vfsortordering"
            type="list"
            class="form-control"
            label="COM_VISFORMS_LIST_FULL_ORDERING"
            description="COM_VISFORMS_LIST_FULL_ORDERING_DESC"
            onchange="vttableFullOrdering' . $this->paginationcontext . '(this);"
            default="a.id asc"
            labelclass="visually-hidden sr-only uk-invisible"
            >
            <option value="a.id asc">COM_VISFORMS_SORT_ID_ASC</option>
            <option value="a.id desc">COM_VISFORMS_SORT_ID_DESC</option>';
                if (!empty($canPublish)) {
                    $xml .= '<option value="a.published asc">COM_VISFORMS_SORT_PUBLISHED_ASC</option>
            <option value="a.published desc">COM_VISFORMS_SORT_PUBLISHED_DESC</option>';
                }
                if (array_key_exists('created', $this->displayedDefaultDbFields)) {
                    $xml .= '<option value="a.created asc">COM_VISFORMS_SORT_DATE_ASC</option>
            <option value="a.created desc">COM_VISFORMS_SORT_DATE_DESC</option>';
                }
            if (array_key_exists('creator', $this->displayedDefaultDbFields)) {
                $xml .= '<option value="creator asc">COM_VISFORMS_SORT_CREATED_BY_ASC</option>
            <option value="creator desc">COM_VISFORMS_SORT_CREATED_BY_DESC</option>';
            }
                if (array_key_exists('ipaddress', $this->displayedDefaultDbFields)) {
                    $xml .= '<option value="a.ipaddress asc">COM_VISFORMS_SORT_IP_ASC</option>
            <option value="a.ipaddress desc">COM_VISFORMS_SORT_IP_DESC</option>';
                }
                if (array_key_exists('ismfd', $this->displayedDefaultDbFields)) {
                    $xml .= '<option value="a.ismfd asc">COM_VISFORMS_SORT_ISMFD_ASC</option>
            <option value="a.ismfd desc">COM_VISFORMS_SORT_ISMFD_DESC</option>';
                }
                if (array_key_exists('modified', $this->displayedDefaultDbFields)) {
                    $xml .= '<option value="a.modified asc">COM_VISFORMS_SORT_MODIFIED_AT_ASC</option>
            <option value="a.modified desc">COM_VISFORMS_SORT_MODIFIED_AT_DESC</option>';
                }
            if (array_key_exists('editor', $this->displayedDefaultDbFields)) {
                $xml .= '<option value="editor asc">COM_VISFORMS_SORT_MODIFIED_BY_ASC</option>
            <option value="editor desc">COM_VISFORMS_SORT_MODIFIED_BY_DESC</option>';
            }

            foreach ($fields as $field) {
                if ((!empty($this->pparams))) {
                    if (!(in_array($field->id, $this->pluginfieldlist))) {
                        continue;
                    }
                } // only search filter for fields which are displayed in list view
                else if ((!empty($field->frontdisplay))) {
                    if ($field->frontdisplay == 3) {
                        continue;
                    }
                }
                if (empty($field->unSortable)) {
                    $xml .= '<option value="a.F' . $field->id . ' asc">' . htmlspecialchars($field->label, ENT_COMPAT) . ' ' . Text::_("COM_VISFORMS_ASC") . '</option>';
                    $xml .= '<option value="a.F' . $field->id . ' desc">' . htmlspecialchars($field->label, ENT_COMPAT) . ' ' . Text::_("COM_VISFORMS_DESC") . '</option>';
                }
            }

            $xml .= '</field>';

            $sortorderFieldXml = new \SimpleXMLElement($xml);
            $form->setField($sortorderFieldXml, 'filter');
            $form->setFieldAttribute('vfsortordering', 'name', $this->paginationcontext . 'vfsortordering', 'filter');

		// if we come from the dataview plugin, only show filter of fields which are in the plugins field-list
			foreach ($fields as $field) {
				if ((!empty($this->pparams))) {
					if (!(in_array($field->id, $this->pluginfieldlist))) {
						continue;
					}
				} // only search filter for fields which are displayed in list view
				else if ((!empty($field->frontdisplay))) {
					if ($field->frontdisplay == 3) {
						continue;
					}
				}
				if (in_array($field->typefield, array('select', 'radio', 'multicheckbox', 'selectsql', 'radiosql', 'multicheckboxsql')) && $field->isfilterfield) {
					if ($this->getListBoxFilterField($field) !== false) {
						$addFilterField = new \SimpleXMLElement($this->getListBoxFilterField($field));
						$form->setField($addFilterField, 'filter');
					}
				}
				if ($field->typefield == 'checkbox' && $field->isfilterfield) {
					if ($this->getCheckboxFilterField($field) !== false) {
						$addFilterField = new \SimpleXMLElement($this->getCheckboxFilterField($field));
						$form->setField($addFilterField, 'filter');
					}
				}
				if ($field->typefield === "location" && !empty($field->allowferadiussearch)) {
					$this->visform->hasLocationRadiusSearch = true;
					$locationSearchXml = '<field
						name="' . $this->paginationcontext . $field->name . '"
						class="placessearchbox"
						type="text"
						label="COM_VISFORMS_SEARCH_LOCATION"
						labelclass="visually-hidden sr-only uk-invisible"
						hint="COM_VISFORMS_SEARCH_LOCATION"
						onchange="geocodeSearchAddress(this);"
						/>';
					$addFilterField = new \SimpleXMLElement($locationSearchXml);
					$form->setField($addFilterField, 'filter');

					$locationLocationXml = '<field
						name="' . $this->paginationcontext . $field->name . '_location"
						type="hidden"
						/>';
					$addFilterField = new \SimpleXMLElement($locationLocationXml);
					$form->setField($addFilterField, 'filter');
                    $distanceUnit = (!empty($field->distanceunit)) ? $field->distanceunit : 'km';
                    $label = Text::sprintf('COM_VISFORMS_FILTER_SELECT_LABEL', Text::sprintf('COM_VISFORMS_RADIUS', $distanceUnit));
					$options = '<option value="">' . Text::sprintf('COM_VISFORMS_FILTER_SELECT_SELECT_A_VALUE', Text::sprintf('COM_VISFORMS_RADIUS', $distanceUnit)) . '</option>';
					foreach (array(10, 20, 50, 100, 1000) as $fieldoption) {
						$options .= '<option value="' . $fieldoption . '">' . $fieldoption . '</option>';
					}
					$locationRadiusXml = '<field
						name="' . $this->paginationcontext . $field->name . '_radius"
						type="list"
						class="form-control"
						label="'.$label.'"
						labelclass="visually-hidden sr-only uk-invisible"
						>' . $options . '
						
					</field>';
					$addFilterField = new \SimpleXMLElement($locationRadiusXml);
					$form->setField($addFilterField, 'filter');
				}
				if ($field->typefield === "date" && !empty($field->isfilterfield)) {
					$formats = explode(';', $field->format);
					$dateFieldSearchXml = '<field
					        addfieldprefix="Visolutions\Component\Visforms\Site\Field"
		                    name="' . $this->paginationcontext . $field->name . '_min"
		                    type="Vcalendarsite"
		                    label="COM_VISFORMS_FILTER_DATE_AFTER"
		                    hint="COM_VISFORMS_FILTER_DATE_AFTER"
		                    format="'.$formats[1].'"
		                    filterformat="'.$formats[0].'"
		                >
		            </field>';
					$addFilterField = new \SimpleXMLElement($dateFieldSearchXml);
					$form->setField($addFilterField, 'filter');
					$form->setFieldAttribute($this->paginationcontext . $field->name . '_min', 'label', $field->label . ' ' . Text::_('COM_VISFORMS_FILTER_DATE_AFTER'), 'filter');
					$form->setFieldAttribute($this->paginationcontext . $field->name . '_min', 'hint', $field->label . ' ' . Text::_('COM_VISFORMS_FILTER_DATE_AFTER'), 'filter');
					$dateFieldSearchXml = '<field
					        addfieldprefix="Visolutions\Component\Visforms\Site\Field"
		                    name="' . $this->paginationcontext . $field->name . '_max"
		                    type="Vcalendarsite"
		                    label="COM_VISFORMS_FILTER_DATE_BEFORE"
		                    hint="COM_VISFORMS_FILTER_DATE_BEFORE"
		                    format="'.$formats[1].'"
		                    filterformat="'.$formats[0].'"
		                >
		            </field>';
					$addFilterField = new \SimpleXMLElement($dateFieldSearchXml);
					$form->setField($addFilterField, 'filter');
					$form->setFieldAttribute($this->paginationcontext . $field->name . '_max', 'label', $field->label . ' ' . Text::_('COM_VISFORMS_FILTER_DATE_BEFORE'), 'filter');
					$form->setFieldAttribute($this->paginationcontext . $field->name . '_max', 'hint', $field->label . ' ' . Text::_('COM_VISFORMS_FILTER_DATE_BEFORE'), 'filter');
				}
			}
		}

		$data = $this->loadFormData();
		$form->bind($data);
		return $form;
	}

	public function getActiveFilters() {
		$activeFilters = array();

		if (!empty($this->filter_fields)) {
			foreach ($this->filter_fields as $filter) {
				$contextFreeFilterName = str_replace($this->paginationcontext, '', $filter);
				$filterName = 'filter.' . $contextFreeFilterName;

                if (!empty($this->state->get($filterName)) || is_numeric($this->state->get($filterName))) { // J5 ListModel
				// if (property_exists($this->state, $filterName) && (!empty($this->state->{$filterName}) || is_numeric($this->state->{$filterName}))) { // J4 ListModel
					$activeFilters[$filter] = $this->state->get($filterName);
				}
			}
		}

		return $activeFilters;
	}

	protected function loadFormData() {
		// check the session for previously entered form data
		$data = Factory::getApplication()->getUserState($this->paginationcontext, new \stdClass);

		// pre-fill the list options
		if (!property_exists($data, 'list')) {
            $data->list = [
                'direction' => $this->getState('list.direction'),
                'limit'     => $this->getState('list.limit'),
                'ordering'  => $this->getState('list.ordering'),
                'start'     => $this->getState('list.start'),
            ]; // J5
		}

		return $data;
	}

	protected function getFilter($query) {
	    $db = Factory::getDbo();
		// get filter parameters
		$fields = $this->datafields;
		$searchFilter = $this->getState('filter.search');
		$filter = '';
		$hasSingleFieldSearch = false;
		$singleSearchFieldId = 0;
		if (($searchFilter != '') && (!empty($fields))) {
		    // if a record id is specified, only search for the record id
            if (stripos($searchFilter, 'id:') === 0) {
                $search = (int)substr($searchFilter, 3);
                $query->where($db->quoteName('a.id') . ' = :searchid')
                    ->bind(':searchid', $search, ParameterType::INTEGER);
            }
            // if created_by is displayed a record creator is specified, only search for the records of this creator
            // if created_by is not displayed, do not search for this string.
            else if (stripos($searchFilter, 'creator:') === 0) {
                if (isset($this->displayedDefaultDbFields['creator'])) {
                    $search = substr($searchFilter, 8);
                    $query->where($db->quoteName('uc.name') . ' = :creator')
                        ->bind(':creator', $search, ParameterType::STRING);
                }
            }
            // if modified by is displayed and a record modified_by is specified, only search for the records with this modified_by
            // if modified is not displayed, do not search for this search.
            else if (stripos($searchFilter, 'editor:') === 0) {
                if (isset($this->displayedDefaultDbFields['editor'])) {
                    $search = substr($searchFilter, 7);
                    $query->where($db->quoteName('ue.name') . ' = :editor')
                        ->bind(':editor', $search, ParameterType::STRING);
                }
            }
            else {
                // check if left of search string matches a field name plus : => Only search in data of this field for search string
                foreach ($fields as $field) {
                    if ((!empty($this->pparams)) && (!(in_array($field->id, $this->pluginfieldlist)))) {
                        continue;
                    }
                    if ($field->typefield == 'signature') {
                        continue;
                    }
                    if (!empty($field->isfilterfield)) {
                        continue;
                    }
                    if (!empty($field->excludefieldfromsearch)) {
                        continue;
                    }
                    if (stripos($searchFilter, $field->name.':') === 0) {
                        $searchFilterText = trim(str_replace($field->name.':', '', $searchFilter));
                        // only use, if we actually have a text left
                        if (($searchFilterText) !== '') {
                            $singleSearchFieldId = $field->id;
                            $hasSingleFieldSearch = true;
                            $searchFilter = $searchFilterText;
                            break;
                        }
                    }
                }
                if (!empty($hasSingleFieldSearch && !empty($singleSearchFieldId))) {
                    $search = '%' . $searchFilter . '%';
                    $query->where($db->quoteName('a.F'. $singleSearchFieldId) . ' LIKE :search')
                        ->bind(':search', $search, ParameterType::STRING);
                }
                // search for string in all possible fields
                else {
                    // ToDo use query->where()->bind()
                    $searchFilter = $db->escape($searchFilter);
                    $referenceDate = Factory::getDate('now', 'UTC');
                    $userTimeZone = new \DateTimeZone(Factory::getConfig()->get('offset'));
                    $offsetInSeconds = $userTimeZone->getOffset($referenceDate);
                    $sign = ($offsetInSeconds < 0) ? '-' : '+';
                    $offsetInSeconds = abs($offsetInSeconds);
                    $filter .= " (";
                    foreach ($fields as $field) {
                        if ((!empty($this->pparams)) && (!(in_array($field->id, $this->pluginfieldlist)))) {
                            continue;
                        }
                        if ($field->typefield == 'signature') {
                            continue;
                        }
                        // string search in all fields which are not displayed as filter
                        // and which are not excluded from search
                        if (empty($field->isfilterfield) && empty($field->excludefieldfromsearch)) {
                            $prop = "F" . $field->id;
                            $filter .= " upper(" . $prop . ") like upper('%" . $searchFilter . "%') or ";
                        }
                    }
                    foreach ($this->displayedDefaultDbFields as $fieldname => $name) {
                        // ToDo Using Search String in created and modified should only happen, if these fields are not available as search filter.
                        // ToDo currently, if the created is displayed in data list, we always search for search string in created, regardless of a possible created filter
                        // The following line would prevent stringsearch in created, if a created filter is displayed.
                        // $createdIsFilter = (in_array($this->paginationcontext . 'mincreated', $this->filter_fields)) ? true : false;
                        $createdIsFilter = false;
                        $dateformat = Text::_('DATE_FORMAT_LC4');
                        $mySqlDateFormat = str_replace('d', '%d', str_replace('m', '%m', str_replace('Y', '%Y', $dateformat)));
                        if ((($fieldname == 'created') && ($name == 'displaycreated') && !$createdIsFilter) || (($fieldname == 'modified') && ($name == 'displaymodifiedat'))) {
                            $filter .= " from_unixtime((unix_timestamp(" . $fieldname . ") " . $sign . " " . $offsetInSeconds . "), '" . $mySqlDateFormat . "') like '%" . $searchFilter . "%' or ";
                        }
                        else if ((($fieldname == 'created') && ($name == 'displaycreatedtime') && !$createdIsFilter) || (($fieldname == 'modified') && ($name == 'displaymodifiedattime'))) {
                            $filter .= " from_unixtime((unix_timestamp(" . $fieldname . ") " . $sign . " " . $offsetInSeconds . "), '" . $mySqlDateFormat . " %H:%i:%s') like '%" . $searchFilter . "%' or ";
                        }
                        else if ($fieldname === 'editor') {
                            $filter .= " " . $db->qn('ue.name') . " like '%" . $searchFilter . "%' or ";
                        }
                        else if ($fieldname === 'creator') {
                            $filter .= " " . $db->qn('uc.name') . " like '%" . $searchFilter . "%' or ";
                        }
                        // all other except ismfd where we do not want to search with a search string
                        else if ($fieldname != 'ismfd') {
                            $filter .= " " . $fieldname . " like '%" . $searchFilter . "%' or ";
                        }
                    }
                    $filter = rtrim($filter, 'or ');
                    $filter = $filter . " )";
                }
            }
            if (!empty($filter)) {
                $query->where($filter);
            }
        }
		return $query;
	}

	public function getContext() {
		if (!empty($this->paginationcontext)) {
			return $this->paginationcontext;
		}
		return '';
	}

	protected function getListBoxFilterField($field) {
		if (in_array($field->typefield, array('select', 'radio', 'multicheckbox')) && empty($field->list_hidden)) {
			return false;
		}
		// Enable Multi Select in filter
		$hasMultiSelect = (!empty($field->ismultiselectsearchfilter)) ? true : false;
		if (in_array($field->typefield, array('select', 'radio', 'multicheckbox'))) {
			$fieldOptions = HTMLHelper::_('visformsselect.extractHiddenList', $field->list_hidden);
		}
		else {
            // selectsql, radiosql, multicheckboxsql
		    // try to get options directly from sql statement
            $fieldOptions = HTMLHelper::_('visformsselect.getOptionsFromSQL', $field->sql);
		    if (count($fieldOptions) === 0) {
                // sql statement with user or input placeholder does not return proper filter field option list
                // use stored form data as source for filter options
                $fieldOptions = HTMLHelper::_('visformsselect.getOptionListForSQLFilterFields', $field->id, $field->fid, $this->canPublish());
            }
		}
        $label = Text::sprintf('COM_VISFORMS_FILTER_SELECT_LABEL', htmlspecialchars($field->label, ENT_COMPAT));
		// We use joomla-fancy-select layout for multiselect
        // Somehow an option with value "" (the empty 'select a value') is always selected in that layout
        // only add the real options
        // in single select listbox, we need the option with value ""
        $options = '';
        if (!$hasMultiSelect) {
            $options .= '<option value="">' . Text::sprintf('COM_VISFORMS_FILTER_SELECT_SELECT_A_VALUE', htmlspecialchars($field->label, ENT_COMPAT)) . '</option>';
        }

		if (!empty($fieldOptions)) {
			foreach ($fieldOptions as $fieldoption) {
				$options .= '<option value="' . htmlspecialchars($fieldoption['value'], ENT_COMPAT) . '">' . htmlspecialchars($fieldoption['label'], ENT_COMPAT) . '</option>';
			}
		}
		// Custom attributes for multiselect
        if ($hasMultiSelect) {
            $customAttributes = ' multiple="true" layout="joomla.form.field.list-fancy-select" hint="'.$label.'" ';
        }
        else {
            $customAttributes = ' class="form-control" ';
        }

		return '<field'. $customAttributes . '
			name="' . $this->paginationcontext . $field->name . '"
			type="list"
			label="'.$label.'"
			labelclass="visually-hidden sr-only uk-invisible"
			>' . $options . '
		</field>';
	}

	protected function getCheckboxFilterField($field) {
		if ($field->typefield = 'checkbox' && !isset($field->attribute_value)) {
			return false;
		}
        $label = Text::sprintf('COM_VISFORMS_FILTER_SELECT_LABEL', htmlspecialchars($field->label, ENT_COMPAT));
		$options  = '<option value="">' . Text::sprintf('COM_VISFORMS_FILTER_SELECT_SELECT_A_VALUE', htmlspecialchars($field->label, ENT_COMPAT)) . '</option>';
		$options .= '<option value="checked">' . htmlspecialchars($field->attribute_value, ENT_COMPAT) . '</option>';
		$options .= '<option value="unchecked">' .Text::_('COM_VISFORMS_NOT')  . ' '. htmlspecialchars($field->attribute_value, ENT_COMPAT) . '</option>';

		return '<field
			name="' . $this->paginationcontext . $field->name . '"
			type="list"
			class="form-control"
			label="'.$label.'"
			labelclass="visually-hidden sr-only uk-invisible"
			>' . $options . '
		</field>';
	}

	public function getRawItems() {
		return parent::getItems();
	}

	public function getItems() {
		$items = parent::getItems();
		$fields = $this->datafields;
		if (empty($items)) {
			return $items;
		}
		$n = count($items);
		for ($i = 0; $i < $n; $i++) {
		    // creator/editor may by null, convert to empty string
		    if (empty($items[$i]->creator)) {
                $items[$i]->creator = '';
            }
            if (empty($items[$i]->editor)) {
                $items[$i]->editor = '';
            }
            if (empty($fields)) {
                continue;
            }
			foreach ($fields as $field) {
				$itemFieldName = "F" . $field->id;
				// display options labels for selects, radios, multi-checkboxes and checkboxes in frontend data views not the stored option values
				if (in_array($field->typefield, array('select', 'radio', 'multicheckbox'))) {
					$itemFieldValue = $items[$i]->$itemFieldName;
					if ((!isset($itemFieldValue)) || ($itemFieldValue === '') || (empty($field->list_hidden))) {
						continue;
					}
					$newExtractedItemFieldValues = HTMLHelper::_('visformsselect.mapDbValueToOptionLabel', $itemFieldValue, $field->list_hidden);
					$newItemFieldValue = implode('<br />', $newExtractedItemFieldValues);
					$items[$i]->$itemFieldName = $newItemFieldValue;
				}
				if (in_array($field->typefield, array('selectsql', 'radiosql', 'multicheckboxsql'))) {
					$itemFieldValue = $items[$i]->$itemFieldName;
					if ((!isset($itemFieldValue)) || ($itemFieldValue === '')) {
						continue;
					}
					$newExtractedItemFieldValues = HTMLHelper::_('visformsselect.mapDbValueToSqlOptionLabel', $itemFieldValue, $field->sql);
					$newItemFieldValue = implode('<br />', $newExtractedItemFieldValues);
					$items[$i]->$itemFieldName = $newItemFieldValue;
				}
				if ($field->typefield == 'location') {
					$items[$i]->$itemFieldName = \VisformsHelper::registryArrayFromString($items[$i]->$itemFieldName);
				}
			}
		}
		return $items;
	}

	// only use in list views!
	protected function setDisplayedDefaultDbFields() {
		$displayedDefaultDbFields = $this->displayedDefaultDbFields;
		if (empty($displayedDefaultDbFields)) {
			$form = $this->visform;
			$displayedDefaultDbFields = array();
			$formParamNames = array('displayip' => 'ipaddress', 'displaycreated' => 'created', 'displaycreatedtime' => 'created', 'displaycreatedby' => 'creator', 'displayismfd' => 'ismfd', 'displaymodifiedat' => 'modified', 'displaymodifiedattime' => 'modified', 'displaymodifiedby' => 'editor');
			foreach ($formParamNames as $name => $fieldname) {
				if ((isset($form->$name)) && (in_array($form->$name, array('1', '2')))) {
					if ((empty($this->pparams)) || ((isset($this->pparams[$name])) && ($this->pparams[$name] === 'true'))) {
						// use named array, in order to prevent two elements with value created
						$displayedDefaultDbFields[$fieldname] = $name;
					}
				}
			}
		}
		return $displayedDefaultDbFields;
	}

	protected function canPublish($own = '') {
	    $permission = 'core.edit'.$own.'.data.state';
		$canDo = \VisformsHelper::getActions($this->id);
		$layout = Factory::getApplication()->input->get('layout', 'data', 'string');
		if ((!empty(\VisformsAEF::checkAEF(\VisformsAEF::$subscription))) && ($canDo->get($permission))
			&& (($layout == 'detailedit') || ($layout == 'dataeditlist'))) {
			return true;
		}
		return false;
	}

	// only use in list views!
	protected function setPluginFieldList() {
		$pluginFieldList = array();
		if ((!empty($this->pparams)) && (!empty($this->pparams['fieldlist']))) {
			$rawPluginFieldList = explode(',', $this->pparams['fieldlist']);
			$fields = $this->datafields;
			foreach ($rawPluginFieldList as $value) {
				$fieldID = trim($value);
				foreach ($fields as $field) {
					// if any sort of frontdisplay is enabled for the field in field configuration, it is displayed by the plugin vfdataview
					if (($field->id == $fieldID) && (in_array($field->frontdisplay, array('1', '2', '3')))) {
						$pluginFieldList[] = $fieldID;
					}
				}
			}
		}
		return $pluginFieldList;
	}

	// deprecated, use JHTMLVisforms::checkDataViewMenuItemExists
	// keep for compatibility with older subscription versions
	public function checkDataViewMenuItemExists() {
		return HTMLHelper::_('visforms.checkDataViewMenuItemExists', $this->id);
	}

	public function getMenuId() {
		if ($menu = $this->getMenuItem()) {
			return ($menu->id) ? $menu->id : 0;
		}
		return 0;
	}
}