AdminListController.php

<?php

/**
 * Copyright © OXID eSales AG. All rights reserved.
 * See LICENSE file for license details.
 */

namespace OxidEsales\EshopCommunity\Application\Controller\Admin;

use stdClass;
use OxidEsales\Eshop\Core\Str;
use OxidEsales\Eshop\Core\Registry;
use OxidEsales\Eshop\Core\TableViewNameGenerator;

/**
 * Admin selectlist list manager.
 */
class AdminListController extends \OxidEsales\Eshop\Application\Controller\Admin\AdminController
{
    /**
     * Name of chosen object class (default null).
     *
     * @var string
     */
    protected $_sListClass = null;

    /**
     * Type of list.
     *
     * @var string
     */
    protected $_sListType = 'oxlist';

    /**
     * List of objects (default null).
     *
     * @var \OxidEsales\Eshop\Core\Model\ListModel
     */
    protected $_oList = null;

    /**
     * Position in list of objects (default 0).
     *
     * @var int
     */
    protected $_iCurrListPos = 0;

    /**
     * Size of object list (default 0).
     *
     * @var int
     */
    protected $_iListSize = 0;

    /**
     * Array of SQL query conditions (default null).
     *
     * @var array
     */
    protected $_aWhere = null;

    /**
     * Enable/disable sorting by DESC (SQL) (default false - disable).
     *
     * @var bool
     */
    protected $_blDesc = false;

    /**
     * Set to true to enable multi language
     *
     * @var bool
     */
    protected $_blEmployMultilanguage = null;

    /**
     * (default null).
     *
     * @var int
     */
    protected $_iOverPos = null;

    /**
     * Viewable list size
     *
     * @var int
     */
    protected $_iViewListSize = 0;

    /**
     * Viewable default list size (used in list_*.php views)
     *
     * @var int
     */
    protected $_iDefViewListSize = 50;

    /**
     * List sorting array
     *
     * @var array
     */
    protected $_aCurrSorting = null;

    /**
     * Default sorting field
     *
     * @var string
     */
    protected $_sDefSortField = null;

    /**
     * List filter array
     *
     * @var array
     */
    protected $_aListFilter = null;

    /**
     * Returns sorting fields array
     *
     * @return array
     */
    public function getListSorting()
    {
        if ($this->_aCurrSorting === null) {
            $this->_aCurrSorting = Registry::getRequest()->getRequestEscapedParameter('sort');

            if (!$this->_aCurrSorting && $this->_sDefSortField && ($baseObject = $this->getItemListBaseObject())) {
                $this->_aCurrSorting[$baseObject->getCoreTableName()] = [$this->_sDefSortField => "asc"];
            }
        }

        return $this->_aCurrSorting;
    }

    /**
     * Returns list filter array
     *
     * @return array
     */
    public function getListFilter()
    {
        if ($this->_aListFilter === null) {
            $request = \OxidEsales\Eshop\Core\Registry::getRequest();
            $filter = $request->getRequestParameter("where");
            $request->checkParamSpecialChars($filter);

            $this->_aListFilter = $filter;
        }

        return $this->_aListFilter;
    }

    /**
     * Viewable list size getter
     *
     * @return int
     */
    public function getViewListSize()
    {
        if (!$this->_iViewListSize) {
            $config = \OxidEsales\Eshop\Core\Registry::getConfig();
            if ($profile = \OxidEsales\Eshop\Core\Registry::getSession()->getVariable('profile')) {
                if (isset($profile[1])) {
                    $config->setConfigParam('iAdminListSize', (int)$profile[1]);
                }
            }

            $this->_iViewListSize = (int)$config->getConfigParam('iAdminListSize');
            if (!$this->_iViewListSize) {
                $this->_iViewListSize = 10;
                $config->setConfigParam('iAdminListSize', $this->_iViewListSize);
            }
        }

        return $this->_iViewListSize;
    }

    /**
     * Viewable list size getter (used in list_*.php views)
     *
     * @return int
     */
    protected function getUserDefListSize()
    {
        if (!$this->_iViewListSize) {
            if (!($viewListSize = (int)Registry::getRequest()->getRequestEscapedParameter('viewListSize'))) {
                $viewListSize = $this->_iDefViewListSize;
            }
            $this->_iViewListSize = $viewListSize;
        }

        return $this->_iViewListSize;
    }

    /**
     * Executes parent::render(), sets back search keys to view, sets navigation params
     *
     * @return null
     */
    public function render()
    {
        $return = parent::render();

        // assign our list
        $this->_aViewData['mylist'] = $this->getItemList();

        // set navigation parameters
        $this->setListNavigationParams();

        return $return;
    }

    /**
     * Deletes this entry from the database
     *
     * @return null
     */
    public function deleteEntry()
    {
        $delete = oxNew($this->_sListClass);

        //disabling deletion for derived items
        if ($delete->isDerived()) {
            return;
        }

        $blDelete = $delete->delete($this->getEditObjectId());

        // #A - we must reset object ID
        if ($blDelete && isset($_POST['oxid'])) {
            $_POST['oxid'] = -1;
        }

        $this->resetContentCache();

        $this->init();
    }

    /**
     * Calculates list items count
     *
     * @param string $sql SQL query used co select list items
     */
    protected function calcListItemsCount($sql)
    {
        $stringModifier = Str::getStr();

        // count SQL
        $sql = $stringModifier->preg_replace('/select .* from/i', 'select count(*) from ', $sql);

        // removing order by
        $sql = $stringModifier->preg_replace('/order by .*$/i', '', $sql);

        // We force reading from master to prevent issues with slow replications or open transactions (see ESDEV-3804).
        // con of list items which fits current search conditions
        $this->_iListSize = \OxidEsales\Eshop\Core\DatabaseProvider::getMaster()->getOne($sql);

        // set it into session that other frames know about size of DB
        \OxidEsales\Eshop\Core\Registry::getSession()->setVariable('iArtCnt', $this->_iListSize);
    }

    /**
     * Set current list position
     *
     * @param string $page jump page string
     */
    protected function setCurrentListPosition($page = null)
    {
        $adminListSize = $this->getViewListSize();

        $jumpToPage = $page ? ((int)$page) : ((int)((int)Registry::getRequest()->getRequestEscapedParameter('lstrt')) / $adminListSize);
        $jumpToPage = ($page && $jumpToPage) ? ($jumpToPage - 1) : $jumpToPage;

        $jumpToPage = $jumpToPage * $adminListSize;
        if ($jumpToPage < 1) {
            $jumpToPage = 0;
        } elseif ($jumpToPage >= $this->_iListSize) {
            $jumpToPage = floor($this->_iListSize / $adminListSize - 1) * $adminListSize;
        }

        $this->_iCurrListPos = $this->_iOverPos = (int)$jumpToPage;
    }

    /**
     * Adds order by to SQL query string.
     *
     * @param string $query sql string
     *
     * @return string
     */
    protected function prepareOrderByQuery($query = null)
    {
        // sorting
        $sortFields = $this->getListSorting();

        if (is_array($sortFields) && count($sortFields)) {
            // only add order by at full sql not for count(*)
            $query .= ' order by ';
            $addSeparator = false;

            $listItem = $this->getItemListBaseObject();
            $languageId = $listItem->isMultilang() ? $listItem->getLanguage() : \OxidEsales\Eshop\Core\Registry::getLang()->getBaseLanguage();

            $descending = Registry::getRequest()->getRequestEscapedParameter('adminorder');
            $descending = $descending !== null ? (bool)$descending : $this->_blDesc;

            foreach ($sortFields as $table => $fieldData) {
                $tableViewNameGenerator = oxNew(TableViewNameGenerator::class);
                $table = $table ? ($tableViewNameGenerator->getViewName($table, $languageId) . '.') : '';
                foreach ($fieldData as $column => $sortDirectory) {
                    $field = $table . $column;

                    //add table name to column name if no table name found attached to column name
                    $query .= ((($addSeparator) ? ', ' : '')) . \OxidEsales\Eshop\Core\DatabaseProvider::getDb()->quoteIdentifier($field);

                    //V oxActive field search always DESC
                    if ($descending || $column == "oxactive" || strcasecmp($sortDirectory, 'desc') == 0) {
                        $query .= ' desc ';
                    }

                    $addSeparator = true;
                }
            }
        }

        return $query;
    }

    /**
     * Builds and returns SQL query string.
     *
     * @param object $listObject list main object
     *
     * @return string
     */
    protected function buildSelectString($listObject = null)
    {
        return $listObject !== null ? $listObject->buildSelectString(null) : "";
    }


    /**
     * Prepares SQL where query according SQL condition array and attaches it to SQL end.
     * For each search value if german umlauts exist, adds them
     * and replaced by spec. char to query
     *
     * @param string $fieldValue Filters
     *
     * @return string
     */
    protected function processFilter($fieldValue)
    {
        $stringModifier = Str::getStr();

        //removing % symbols
        $fieldValue = $stringModifier->preg_replace("/^%|%$/", "", trim($fieldValue));

        return $stringModifier->preg_replace("/\s+/", " ", $fieldValue);
    }

    /**
     * Builds part of SQL query
     *
     * @param string $value         filter value
     * @param bool   $isSearchValue filter value type, true means surrount search key with '%'
     *
     * @return string
     */
    protected function buildFilter($value, $isSearchValue)
    {
        if ($isSearchValue) {
            //is search string, using LIKE
            $query = " like " . \OxidEsales\Eshop\Core\DatabaseProvider::getDb()->quote('%' . $value . '%') . " ";
        } else {
            //not search string, values must be equal
            $query = " = " . \OxidEsales\Eshop\Core\DatabaseProvider::getDb()->quote($value) . " ";
        }

        return $query;
    }

    /**
     * Checks if filter contains wildcards like %
     *
     * @param string $fieldValue filter value
     *
     * @return bool
     */
    protected function isSearchValue($fieldValue)
    {
        return (Str::getStr()->preg_match('/^%/', $fieldValue) && Str::getStr()->preg_match('/%$/', $fieldValue));
    }

    /**
     * Prepares SQL where query according SQL condition array and attaches it to SQL end.
     * For each search value if german umlauts exist, adds them
     * and replaced by spec. char to query
     *
     * @param array  $whereQuery SQL condition array
     * @param string $fullQuery  SQL query string
     *
     * @return string
     */
    protected function prepareWhereQuery($whereQuery, $fullQuery)
    {
        if (is_array($whereQuery) && count($whereQuery)) {
            $myUtilsString = \OxidEsales\Eshop\Core\Registry::getUtilsString();
            foreach ($whereQuery as $identifierName => $fieldValue) {
                $fieldValue = trim($fieldValue);

                //check if this is search string (contains % sign at beginning and end of string)
                $isSearchValue = $this->isSearchValue($fieldValue);

                //removing % symbols
                $fieldValue = $this->processFilter($fieldValue);

                if (strlen($fieldValue)) {
                    $values = explode(' ', $fieldValue);

                    //for each search field using AND action
                    $queryBoolAction = ' and (';

                    foreach ($values as $value) {
                        // trying to search spec chars in search value
                        // if found, add cleaned search value to search sql
                        $uml = $myUtilsString->prepareStrForSearch($value);
                        if ($uml) {
                            $queryBoolAction .= '(';
                        }

                        $quotedIdentifierName = \OxidEsales\Eshop\Core\DatabaseProvider::getDb()->quoteIdentifier($identifierName);
                        $fullQuery .= " {$queryBoolAction} {$quotedIdentifierName} ";

                        //for search in same field for different values using AND
                        $queryBoolAction = ' and ';

                        $fullQuery .= $this->buildFilter($value, $isSearchValue);

                        if ($uml) {
                            $fullQuery .= " or {$quotedIdentifierName} ";

                            $fullQuery .= $this->buildFilter($uml, $isSearchValue);
                            $fullQuery .= ')'; // end of OR section
                        }
                    }

                    // end for AND action
                    $fullQuery .= ' ) ';
                }
            }
        }

        return $fullQuery;
    }

    /**
     * Override this for individual search in admin.
     *
     * @param string $query SQL select to change
     *
     * @return string
     */
    protected function changeselect($query)
    {
        return $query;
    }

    /**
     * Builds and returns array of SQL WHERE conditions.
     *
     * @return array
     */
    public function buildWhere()
    {
        if ($this->_aWhere === null && ($list = $this->getItemList())) {
            $this->_aWhere = [];
            $filter = $this->getListFilter();
            if (is_array($filter)) {
                $listItem = $this->getItemListBaseObject();
                $languageId = $listItem->isMultilang() ? $listItem->getLanguage() : \OxidEsales\Eshop\Core\Registry::getLang()->getBaseLanguage();
                $localDateFormat = \OxidEsales\Eshop\Core\Registry::getConfig()->getConfigParam('sLocalDateFormat');

                foreach ($filter as $table => $filterData) {
                    foreach ($filterData as $name => $value) {
                        if ($value || '0' === (string)$value) {
                            $field = "{$table}__{$name}";

                            // if no table name attached to field name, add it
                            $tableViewNameGenerator = oxNew(TableViewNameGenerator::class);
                            $name = $table ? $tableViewNameGenerator->getViewName($table, $languageId) . ".{$name}" : $name;

                            // #M1260: if field is date
                            if ($localDateFormat && $localDateFormat != 'ISO' && isset($listItem->$field)) {
                                $fieldType = $listItem->{$field}->fldtype;
                                if ("datetime" == $fieldType || "date" == $fieldType) {
                                    $value = $this->convertToDBDate($value, $fieldType);
                                }
                            }

                            $this->_aWhere[$name] = "%{$value}%";
                        }
                    }
                }
            }
        }

        return $this->_aWhere;
    }

    /**
     * Converts date/datetime values to DB scheme (#M1260)
     *
     * @param string $value     Field value
     * @param string $fieldType Field type
     *
     * @return string
     */
    protected function convertToDBDate($value, $fieldType)
    {
        $convertedObject = new \OxidEsales\Eshop\Core\Field();
        $convertedObject->setValue($value);
        if ($fieldType == "datetime") {
            if (strlen($value) == 10 || strlen($value) == 22 || (strlen($value) == 19 && !stripos($value, "m"))) {
                \OxidEsales\Eshop\Core\Registry::getUtilsDate()->convertDBDateTime($convertedObject, true);
            } else {
                if (strlen($value) > 10) {
                    return $this->convertTime($value);
                } else {
                    return $this->convertDate($value);
                }
            }
        } elseif ($fieldType == "date") {
            if (strlen($value) == 10) {
                \OxidEsales\Eshop\Core\Registry::getUtilsDate()->convertDBDate($convertedObject, true);
            } else {
                return $this->convertDate($value);
            }
        }

        return $convertedObject->value;
    }

    /**
     * Converter for date field search. If not full date will be searched.
     *
     * @param string $date searched date
     *
     * @return string
     */
    protected function convertDate($date)
    {
        // regexps to validate input
        $datePatterns = [
            "/^([0-9]{2})\.([0-9]{4})/" => "EUR2", // MM.YYYY
            "/^([0-9]{2})\.([0-9]{2})/" => "EUR1", // DD.MM
            "/^([0-9]{2})\/([0-9]{4})/" => "USA2", // MM.YYYY
            "/^([0-9]{2})\/([0-9]{2})/" => "USA1" // DD.MM
        ];

        // date/time formatting rules
        $dateFormats = [
            "EUR1" => [2, 1],
            "EUR2" => [2, 1],
            "USA1" => [1, 2],
            "USA2" => [2, 1]
        ];

        // looking for date field
        $dateMatches = [];
        $stringModifier = Str::getStr();
        foreach ($datePatterns as $pattern => $type) {
            if ($stringModifier->preg_match($pattern, $date, $dateMatches)) {
                $date = $dateMatches[$dateFormats[$type][0]] . "-" . $dateMatches[$dateFormats[$type][1]];
                break;
            }
        }

        return $date;
    }

    /**
     * Converter for datetime field search. If not full time will be searched.
     *
     * @param string $fullDate searched date
     *
     * @return string
     */
    protected function convertTime($fullDate)
    {
        $date = substr($fullDate, 0, 10);
        $convertedObject = new \OxidEsales\Eshop\Core\Field();
        $convertedObject->setValue($date);
        \OxidEsales\Eshop\Core\Registry::getUtilsDate()->convertDBDate($convertedObject, true);
        $stringModifier = Str::getStr();

        // looking for time field
        $time = substr($fullDate, 11);
        if ($stringModifier->preg_match("/([0-9]{2}):([0-9]{2}) ([AP]{1}[M]{1})$/", $time, $timeMatches)) {
            if ($timeMatches[3] == "PM") {
                $intVal = (int)$timeMatches[1];
                if ($intVal < 13) {
                    $time = ($intVal + 12) . ":" . $timeMatches[2];
                }
            } else {
                $time = $timeMatches[1] . ":" . $timeMatches[2];
            }
        } elseif ($stringModifier->preg_match("/([0-9]{2}) ([AP]{1}[M]{1})$/", $time, $timeMatches)) {
            if ($timeMatches[2] == "PM") {
                $intVal = (int)$timeMatches[1];
                if ($intVal < 13) {
                    $time = ($intVal + 12);
                }
            } else {
                $time = $timeMatches[1];
            }
        } else {
            $time = str_replace(".", ":", $time);
        }

        return $convertedObject->value . " " . $time;
    }

    /**
     * Set parameters needed for list navigation
     */
    protected function setListNavigationParams()
    {
        // list navigation
        $showNavigation = false;
        $adminListSize = $this->getViewListSize();
        if ($this->_iListSize > $adminListSize) {
            // yes, we need to build the navigation object
            $pageNavigation = new stdClass();
            $pageNavigation->pages = round((($this->_iListSize - 1) / $adminListSize) + 0.5, 0);
            $pageNavigation->actpage = round(
                ($this->_iCurrListPos / $adminListSize) + 0.5,
                0
            );
            $pageNavigation->lastlink = ($pageNavigation->pages - 1) * $adminListSize;
            $pageNavigation->nextlink = null;
            $pageNavigation->backlink = null;

            $position = $this->_iCurrListPos + $adminListSize;
            if ($position < $this->_iListSize) {
                $pageNavigation->nextlink = $position = $this->_iCurrListPos + $adminListSize;
            }

            if (($this->_iCurrListPos - $adminListSize) >= 0) {
                $pageNavigation->backlink = $position = $this->_iCurrListPos - $adminListSize;
            }

            // calculating list start position
            $start = $pageNavigation->actpage - 5;
            $start = ($start <= 0) ? 1 : $start;

            // calculating list end position
            $end = $pageNavigation->actpage + 5;
            $end = ($end < $start + 10) ? $start + 10 : $end;
            $end = ($end > $pageNavigation->pages) ? $pageNavigation->pages : $end;

            // once again adjusting start pos ..
            $start = ($end - 10 > 0) ? $end - 10 : $start;
            $start = ($pageNavigation->pages <= 11) ? 1 : $start;

            // navigation urls
            for ($i = $start; $i <= $end; $i++) {
                $page = new stdclass();
                $page->selected = 0;
                if ($i == $pageNavigation->actpage) {
                    $page->selected = 1;
                }
                $pageNavigation->changePage[$i] = $page;
            }

            $this->_aViewData['pagenavi'] = $pageNavigation;

            if (isset($this->_iOverPos)) {
                $position = $this->_iOverPos;
                $this->_iOverPos = null;
            } else {
                $position = Registry::getRequest()->getRequestEscapedParameter('lstrt');
            }

            if (!$position) {
                $position = 0;
            }

            $this->_aViewData['lstrt'] = $position;
            $this->_aViewData['listsize'] = $this->_iListSize;
            $showNavigation = true;
        }

        // determine not used space in List
        $listSizeToShow = $this->_iListSize - $this->_iCurrListPos;
        $adminListSize = $this->getViewListSize();
        $notUsed = $adminListSize - min($listSizeToShow, $adminListSize);
        $space = $notUsed * 15;

        if (!$showNavigation) {
            $space += 20;
        }

        $this->_aViewData['iListFillsize'] = $space;
    }

    /**
     * Sets-up navigation parameters
     *
     * @param string $node active view id
     */
    protected function setupNavigation($node)
    {
        // navigation according to class
        if ($node) {
            $adminNavigation = $this->getNavigation();

            $objectId = $this->getEditObjectId();

            if ($objectId == -1) {
                //on first call or when pressed creating new item button, resetting active tab
                $activeTab = $this->_iDefEdit;
            } else {
                // active tab
                $activeTab = Registry::getRequest()->getRequestEscapedParameter('actedit');
                $activeTab = $activeTab ? $activeTab : $this->_iDefEdit;
            }

            // tabs
            $this->_aViewData['editnavi'] = $adminNavigation->getTabs($node, $activeTab);

            // active tab
            $this->_aViewData['actlocation'] = $adminNavigation->getActiveTab($node, $activeTab);

            // default tab
            $this->_aViewData['default_edit'] = $adminNavigation->getActiveTab($node, $this->_iDefEdit);

            // assign active tab number
            $this->_aViewData['actedit'] = $activeTab;
        }
    }

    /**
     * Returns items list
     *
     * @return \OxidEsales\Eshop\Core\Model\ListModel
     */
    public function getItemList()
    {
        if ($this->_oList === null && $this->_sListClass) {
            $this->_oList = oxNew($this->_sListType);
            $this->_oList->clear();
            $this->_oList->init($this->_sListClass);

            $where = $this->buildWhere();

            $listObject = $this->_oList->getBaseObject();

            \OxidEsales\Eshop\Core\Registry::getSession()->setVariable('tabelle', $this->_sListClass);
            $tableViewNameGenerator = oxNew(TableViewNameGenerator::class);
            $this->_aViewData['listTable'] = $tableViewNameGenerator->getViewName($listObject->getCoreTableName());
            \OxidEsales\Eshop\Core\Registry::getConfig()->setGlobalParameter('ListCoreTable', $listObject->getCoreTableName());

            if ($listObject->isMultilang()) {
                // is the object multilingual?
                /** @var \OxidEsales\Eshop\Core\Model\MultiLanguageModel $listObject */
                $listObject->setLanguage(\OxidEsales\Eshop\Core\Registry::getLang()->getBaseLanguage());

                if (isset($this->_blEmployMultilanguage)) {
                    $listObject->setEnableMultilang($this->_blEmployMultilanguage);
                }
            }

            $query = $this->buildSelectString($listObject);
            $query = $this->prepareWhereQuery($where, $query);
            $query = $this->prepareOrderByQuery($query);
            $query = $this->changeselect($query);

            // calculates count of list items
            $this->calcListItemsCount($query);

            // setting current list position (page)
            $this->setCurrentListPosition(Registry::getRequest()->getRequestEscapedParameter('jumppage'));

            // setting addition params for list: current list size
            $this->_oList->setSqlLimit($this->_iCurrListPos, $this->getViewListSize());

            $this->_oList->selectString($query);
        }

        return $this->_oList;
    }

    /**
     * Clear items list
     */
    public function clearItemList()
    {
        $this->_oList = null;
    }

    /**
     * Returns item list base object
     *
     * @return \OxidEsales\Eshop\Core\Model\BaseModel|null
     */
    public function getItemListBaseObject()
    {
        $baseObject = null;
        if (($itemsList = $this->getItemList())) {
            $baseObject = $itemsList->getBaseObject();
        }

        return $baseObject;
    }
}