/**
* list thr routes by conditions
*
* @param array $options available options: search, sort, orders, offset, limit, list_for, unlimited
* @return type
*/
public function listRoutes(array $options = [])
{
$output = [];
// create query and prepare. first step is for count total.-----------------------------------------
$sql = 'SELECT * FROM `' . $this->Db->getTableName('routes') . '`';
$sql .= ' WHERE 1';
if (isset($options['search'])) {
$sql .= ' AND (';
$sql .= 'route_method LIKE :search';
$sql .= ' OR route_uri LIKE :search';
$sql .= ' OR route_controller LIKE :search';
$sql .= ' OR route_assert LIKE :search';
$sql .= ' OR route_bind LIKE :search';
$sql .= ' OR route_match_method LIKE :search';
$sql .= ')';
}
// sort and order
if (!isset($options['sort']) || isset($options['sort']) && !in_array($options['sort'], ['route_id', 'module_id', 'route_method', 'route_uri', 'route_controller', 'route_assert', 'route_bind', 'route_match_method', 'route_core', 'route_order'])) {
$options['sort'] = '`route_order`';
}
if (!isset($options['orders']) || isset($options['orders']) && !in_array($options['orders'], $this->allowed_orders)) {
$options['orders'] = 'ASC';
}
$sql .= ' ORDER BY ' . $options['sort'] . ' ' . $options['orders'];
$stmt = $this->Conn->prepare($sql);
// bind values
$this->bindValues($stmt, $options);
$stmt->execute();
$result = $stmt->fetchAll();
$output['total'] = count($result);
unset($stmt);
// re-create query and prepare. second step is for set limit and fetch all items.------------------
if (!isset($options['unlimited']) || isset($options['unlimited']) && $options['unlimited'] == false) {
if (!isset($options['offset']) || isset($options['offset']) && !is_numeric($options['offset'])) {
$options['offset'] = 0;
}
if (!isset($options['limit']) || isset($options['limit']) && !is_numeric($options['limit'])) {
$configdb = new \System\Core\Models\ConfigDb($this->Db);
if (isset($options['list_for']) && $options['list_for'] == 'admin') {
$options['limit'] = $configdb->getConfigValue('content_admin_items_perpage', 10);
} else {
$options['limit'] = $configdb->getConfigValue('content_items_perpage', 10);
}
unset($configdb);
}
$sql .= ' LIMIT ' . $options['offset'] . ', ' . $options['limit'];
$stmt = $this->Conn->prepare($sql);
// bind values
$this->bindValues($stmt, $options);
$stmt->execute();
$result = $stmt->fetchAll();
}
$output['items'] = $result;
unset($result, $sql, $stmt);
return $output;
}
/**
* setup i18n URI and detect the language in URI.
*/
public function i18nUri()
{
if ($this->Profiler != null) {
$this->Profiler->Console->timeload('Initialize the language locale uri.', __FILE__, __LINE__);
$this->Profiler->Console->memoryUsage('Initialize the language locale uri.', __FILE__, __LINE__ - 1);
}
$configdb = new \System\Core\Models\ConfigDb($this->Silexapp['Db']);
if ($configdb->getConfigValue('site_lang_method', 'uri') == 'cookie') {
// if detect language method is using cookie.
$language_cookie_name = $_SERVER['HTTP_HOST'] . '_CMS_LANGUAGE_LOCALE_URI';
$request = new \Symfony\Component\HttpFoundation\Request($_GET, $_POST, [], $_COOKIE);
if ($request->cookies->has($language_cookie_name)) {
$this->language_locale_uri = $request->cookies->get($language_cookie_name);
} else {
$LangDb = new \System\Core\Models\LanguagesDb($this->Silexapp['Db']);
$this->language_locale_uri = $LangDb->getDefaultLanguageUri();
$cookie = new \Symfony\Component\HttpFoundation\Cookie($language_cookie_name, $LangDb->getDefaultLanguageUri(), time() + 60 * 60 * 24 * 30, '/', null, isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] != 'off' ? true : false);
$response = new \Symfony\Component\HttpFoundation\Response();
$response->headers->setCookie($cookie);
$response->send();
}
unset($configdb, $cookie, $LangDb, $language_cookie_name, $request, $response);
} else {
// if detect language method is using URI.
$uri = new \System\Libraries\Uri();
$uris_arr = $uri->getUriSegments(true);
// get language locale uri from REQUEST_URI. this does not check the exists language yet.
if (isset($uris_arr[1])) {
$language_locale_uri = $uris_arr[1];
} else {
$language_locale_uri = $uris_arr[count($uris_arr) - 1];
}
// check that language locale uri is in language list or not. non-match or empty language locale uri will be use default language locale uri.
$LangDb = new \System\Core\Models\LanguagesDb($this->Silexapp['Db']);
$this->language_locale_uri = $LangDb->getLanguageUri($language_locale_uri);
// check and detect language redirect.
if ($this->language_locale_uri != $language_locale_uri) {
// if checked language locale URI is not match the detected one. example: th != page, en-US != fr-FR where page, or fr-FR is not exists in language db.
// set language locale uri to global variable $_SERVER.
$_SERVER['CMS_LANGUAGE_LOCALE_URI'] = $this->language_locale_uri;
if ($configdb->getConfigValue('site_lang_uri_default_visible', '1') == '1') {
// if settings was set to force show language URI, redirect to new url with language prefixed.
$uris_arr[0] = $this->language_locale_uri;
$new_uri = implode('/', $uris_arr);
unset($configdb, $LangDb, $language_locale_uri, $uris_arr);
// redirect from EX. http://domain.tld/myapp/index.php to http://domain.tld/myapp/index.php/th, http://domain.tld/myapp/index.php/page/subpage -> http://domain.tld/index.php/th/page/subpage
header('Cache-Control: no-store, no-cache, must-revalidate, max-age=0');
header('Cache-Control: post-check=0, pre-check=0', false);
header('Pragma: no-cache');
header('HTTP/1.1 301 Moved Permanently');
header('Location: ' . $uri->createUrl($new_uri, 'auto', false));
exit;
}
} else {
// if checked language locale URI is match detected one.
// set language locale uri to global variable $_SERVER.
$_SERVER['CMS_LANGUAGE_LOCALE_URI'] = $language_locale_uri;
if ($configdb->getConfigValue('site_lang_uri_default_visible', '1') == '0') {
// if default language is set to not visible, redirect to the URL where it has no language locale URI.
$default_language_locale_uri = $LangDb->getDefaultLanguageUri();
if ($language_locale_uri == $default_language_locale_uri) {
// if detected language locale uri is default. example: default is 'th'; url is http://domain.tld/myapp/index.php/th/page/subpage; this language locale uri is default language!
unset($_SERVER['CMS_LANGUAGE_LOCALE_URI']);
$uris_arr = $uri->getUriSegments(true);
if (isset($uris_arr[1]) && $uris_arr[1] != null) {
unset($uris_arr[1]);
}
$new_uri = implode('/', $uris_arr);
unset($configdb, $LangDb, $language_locale_uri, $uris_arr);
// redirect from IE. http://domain.tld/myapp/index.php/th to http://domain.tld/myapp/index.php where th is default language.
header('Cache-Control: no-store, no-cache, must-revalidate, max-age=0');
header('Cache-Control: post-check=0, pre-check=0', false);
header('Pragma: no-cache');
header('HTTP/1.1 301 Moved Permanently');
header('Location: ' . $uri->createUrl($new_uri, 'auto', false));
exit;
}
}
}
// endif check and detect language redirect.
// re-setting request uri key. --------------------------------------------------------------
// in case that the url contain language locale uri. for example: uri is http://domain.tld/myapp/index.php/th/page/subpage; the original REQUEST_URI will be /myapp/index.php/th/page/subpage
// with this re-setting request uri key, the modified REQUEST_URI will be /myapp/index.php/page/subpage. so the routes can works properly.
$index_file = $uri->getIndexFile();
$uris_arr = $uri->getUriSegments(true);
if (isset($uris_arr[1]) && $uris_arr[1] == $this->language_locale_uri) {
unset($uris_arr[1]);
}
$uri_string = implode('/', $uris_arr);
$_SERVER['REQUEST_URI'] = $uri->getBaseUri() . ($index_file != null ? '/' . $index_file : '') . $uri_string;
unset($configdb, $default_language_locale_uri, $index_file, $language_locale_uri, $uri, $uris_arr, $uri_string);
}
// endif; site_lang_method
// set share variable via Silex\Application.
$this->Silexapp['language_locale_uri'] = $this->language_locale_uri;
$this->Silexapp->register(new \Silex\Provider\TranslationServiceProvider(), ['locale' => $LangDb->getLanguageData($this->language_locale_uri), 'locale_fallbacks' => [$LangDb->getLanguageData('')]]);
putenv('LC_ALL=' . $this->language_locale_uri);
setlocale(LC_ALL, explode(',', str_replace(', ', ',', $LangDb->getLanguageData($this->language_locale_uri))));
// start to working with php-gettext.
$this->Silexapp['Language'] = $this->Silexapp->share(function () {
return new \System\Libraries\Language($this->language_locale_uri);
});
unset($LangDb);
if ($this->Profiler != null) {
$this->Profiler->Console->timeload('Finished the language locale uri.', __FILE__, __LINE__);
$this->Profiler->Console->memoryUsage('Finished the language locale uri.', __FILE__, __LINE__ - 1);
}
}
/**
* list modules up to conditions provided by options in Query Builder code style.
*
* @param array $options available options: module_system_name, module_enable, site_ids (array), site_id (int), search, sort, orders, offset, limit, list_for, unlimited
* @return array return array with total and items in keys.
*/
public function listModulesQb(array $options = [])
{
$output = [];
// create query and prepare. first step is for count total.-----------------------------------------
$qb = $this->Conn->createQueryBuilder();
$qb->select('*')->from($this->Db->getTableName('modules'), 'm')->leftJoin('m', $this->Db->getTableName('module_sites'), 'ms', 'm.module_id = ms.module_id');
if (isset($options['module_system_name'])) {
$qb->andWhere('module_system_name = :module_system_name');
}
if ((!isset($options['list_for']) || isset($options['list_for']) && $options['list_for'] == 'front') && !isset($options['module_enable'])) {
$options['module_enable'] = '1';
}
if (isset($options['module_enable'])) {
$qb->andwhere('module_enable = :module_enable');
}
if (isset($options['site_ids'])) {
$qb->andWhere($qb->expr()->in('site_id', ':site_ids'));
}
if (isset($options['site_id'])) {
$qb->andWhere('site_id = :site_id');
}
if (isset($options['search'])) {
$qb->andWhere($qb->expr()->orX($qb->expr()->like('module_system_name', ':search'), $qb->expr()->like('module_version', ':search')));
}
$qb->groupBy('`m`.`module_id`');
// sort and order
if (!isset($options['sort']) || isset($options['sort']) && !in_array($options['sort'], ['module_id', 'm.module_id', 'module_system_name', 'module_version', 'site_id', 'module_enable'])) {
$options['sort'] = '`m`.`module_id`';
}
if (!isset($options['orders']) || isset($options['orders']) && !in_array($options['orders'], $this->allowed_orders)) {
$options['orders'] = 'ASC';
}
$qb->orderBy($options['sort'], $options['orders']);
// set parameter
if (isset($options['module_system_name'])) {
$qb->setParameter('module_system_name', $options['module_system_name'], \Pdo::PARAM_STR);
}
if (isset($options['module_enable'])) {
$qb->setParameter('module_enable', $options['module_enable'], \Pdo::PARAM_INT);
}
if (isset($options['site_ids'])) {
if (!is_array($options['site_ids']) && is_string($options['site_ids'])) {
$options['site_ids'] = explode(',', str_replace(', ', ',', $options['site_ids']));
}
$qb->setParameter('site_ids', $options['site_ids'], \Doctrine\DBAL\Connection::PARAM_INT_ARRAY);
}
if (isset($options['site_id'])) {
$qb->setParameter('site_id', $options['site_id'], \Pdo::PARAM_INT);
}
if (isset($options['search'])) {
$qb->setParameter('search', '%' . $options['search'] . '%', \Pdo::PARAM_STR);
}
$stmt = $qb->execute();
$result = $stmt->fetchAll();
$output['total'] = count($result);
unset($stmt);
// re-create query and prepare. second step is for set limit and fetch all items.------------------
if (!isset($options['unlimited']) || isset($options['unlimited']) && $options['unlimited'] == false) {
if (!isset($options['offset']) || isset($options['offset']) && !is_numeric($options['offset'])) {
$options['offset'] = 0;
}
if (!isset($options['limit']) || isset($options['limit']) && !is_numeric($options['limit'])) {
$configdb = new \System\Core\Models\ConfigDb($this->Db);
if (isset($options['list_for']) && $options['list_for'] == 'admin') {
$options['limit'] = $configdb->getConfigValue('content_admin_items_perpage', 10);
} else {
$options['limit'] = $configdb->getConfigValue('content_items_perpage', 10);
}
unset($configdb);
}
$qb->setFirstResult($options['offset'])->setMaxResults($options['limit']);
$stmt = $qb->execute();
$result = $stmt->fetchAll();
unset($stmt);
}
// get all related module_sites to module_sites property.
if ($output['total'] > '0' && is_array($result)) {
$new_result = [];
$i = 0;
foreach ($result as $row) {
$new_row = $row;
$qb->resetQueryParts();
$stmt = $qb->select('*')->from($this->Db->getTableName('module_sites'))->andWhere('module_id = ' . $row->module_id)->execute();
$result_ms = $stmt->fetchAll();
$new_row->module_sites = $result_ms;
$new_result[$i] = $new_row;
$i++;
unset($new_row, $result_ms);
}
$result = $new_result;
unset($i, $new_result);
}
$output['items'] = $result;
unset($qb, $result, $stmt);
return $output;
}
/**
* list themes up to conditions provided by options.
*
* @param array $options available options: theme_enable, theme_default, theme_default_admin, site_id, search, sort, orders, offset, limit, list_for, unlimited
* @return array return array with total and items in keys.
*/
public function listThemes(array $options = [])
{
$output = [];
$cache = new \System\Libraries\Cache();
$cache->setSubFolder('system/Core/Models/ThemesDb.php');
$cache_name = 'ThemesDb-listThemes-' . $this->Db->getCurrentSiteId() . '_options_' . md5(serialize($options));
if ($cache->contains($cache_name)) {
$output = $cache->fetch($cache_name);
} else {
// create query and prepare. first step is for count total.-----------------------------------------
$sql = 'SELECT * FROM `' . $this->Db->getTableName('themes') . '` AS `t`';
$sql .= ' LEFT JOIN `' . $this->Db->getTableName('theme_sites') . '` AS `ts` ON t.theme_id = ts.theme_id';
$sql .= ' WHERE 1';
if ((!isset($options['list_for']) || isset($options['list_for']) && $options['list_for'] == 'front') && !isset($options['theme_enable'])) {
$options['theme_enable'] = '1';
}
if (isset($options['theme_enable'])) {
$sql .= ' AND `ts`.`theme_enable` = :theme_enable';
}
if (isset($options['theme_default'])) {
$sql .= ' AND `ts`.`theme_default` = :theme_default';
}
if (isset($options['theme_default_admin'])) {
$sql .= ' AND `ts`.`theme_default_admin` = :theme_default_admin';
}
if (isset($options['site_id'])) {
$sql .= ' AND `ts`.`site_id` = :site_id';
}
if (isset($options['search'])) {
$sql .= ' AND (';
$sql .= 't.theme_system_name LIKE :search';
$sql .= ' OR t.theme_version LIKE :search';
$sql .= ')';
}
$sql .= ' GROUP BY `t`.`theme_id`';
// sort and order
if (isset($options['sort']) && $options['sort'] == 'theme_id') {
$options['sort'] = '`t`.`theme_id`';
}
if (!isset($options['sort']) || isset($options['sort']) && !in_array($options['sort'], ['theme_id', 't.theme_id', 'theme_system_name', 'theme_version', 'site_id', 'theme_enable', 'theme_default', 'theme_default_admin'])) {
$options['sort'] = '`t`.`theme_id`';
}
if (!isset($options['orders']) || isset($options['orders']) && !in_array($options['orders'], $this->allowed_orders)) {
$options['orders'] = 'ASC';
}
$sql .= ' ORDER BY ' . $options['sort'] . ' ' . $options['orders'];
$stmt = $this->Conn->prepare($sql);
// bind values
$this->bindValues($stmt, $options);
$stmt->execute();
$result = $stmt->fetchAll();
$output['total'] = count($result);
unset($stmt);
// re-create query and prepare. second step is for set limit and fetch all items.------------------
if (!isset($options['unlimited']) || isset($options['unlimited']) && $options['unlimited'] == false) {
if (!isset($options['offset']) || isset($options['offset']) && !is_numeric($options['offset'])) {
$options['offset'] = 0;
}
if (!isset($options['limit']) || isset($options['limit']) && !is_numeric($options['limit'])) {
$configdb = new \System\Core\Models\ConfigDb($this->Db);
if (isset($options['list_for']) && $options['list_for'] == 'admin') {
$options['limit'] = $configdb->getConfigValue('content_admin_items_perpage', 10);
} else {
$options['limit'] = $configdb->getConfigValue('content_items_perpage', 10);
}
unset($configdb);
}
$sql .= ' LIMIT ' . $options['offset'] . ', ' . $options['limit'];
$stmt = $this->Conn->prepare($sql);
// bind values
$this->bindValues($stmt, $options);
$stmt->execute();
$result = $stmt->fetchAll();
}
// get all related theme_sites to theme_sites property.
if ($output['total'] > '0' && is_array($result)) {
$new_result = [];
$i = 0;
unset($sql);
foreach ($result as $row) {
$new_row = $row;
$sql = 'SELECT * FROM ' . $this->Db->getTableName('theme_sites') . ' WHERE theme_id = :theme_id';
$stmt = $this->Conn->prepare($sql);
$this->bindValues($stmt, ['theme_id' => $row->theme_id]);
$stmt->execute();
$result_ts = $stmt->fetchAll();
$new_row->theme_sites = $result_ts;
$new_result[$i] = $new_row;
$i++;
unset($new_row, $result_ts, $sql);
}
$result = $new_result;
unset($i, $new_result);
}
$output['items'] = $result;
unset($result, $sql, $stmt);
$cache->save($cache_name, $output, 60 * 60 * 24 * 30);
}
unset($cache, $cache_name);
return $output;
}