/**
* fetch options from db then fix and return result
* @param boolean $_pemissionDetails [description]
* @return [type] [description]
*/
public static function fetch()
{
// connect to default database
\lib\db::connect(true);
// set query string
$qry = "SELECT `options`.*\n\t\t\tFROM `options`\n\t\t\tWHERE user_id IS NULL AND\n\t\t\t\tpost_id IS NULL AND\n\t\t\t\t(\n\t\t\t\t\toption_cat like 'option%' OR\n\t\t\t\t\toption_cat like 'permissions'\n\t\t\t\t)";
// run query and give result
$result = @mysqli_query(\lib\db::$link, $qry);
// if result is not mysqli result return false
if (!is_a($result, 'mysqli_result')) {
// no record exist
return false;
}
// fetch all records
$result = \lib\db::fetch_all($result);
$permList = [];
$qry_result = null;
foreach ($result as $key => $row) {
// save permissions to query result
if ($row['option_cat'] == 'permissions') {
// if status is enable
if ($row['option_status'] == 'enable') {
$qry_result['permissions']['meta'][$row['option_key']] = json_decode($row['option_meta'], true);
$qry_result['permissions']['meta'][$row['option_key']]['id'] = $row['option_key'];
$qry_result['permissions']['meta'][$row['option_key']]['name'] = $row['option_value'];
// save current user permission as option permission value
if (isset($_SESSION['user']['permission']) && $row['option_key'] == $_SESSION['user']['permission']) {
$qry_result['permissions']['value'] = $row['option_key'];
}
}
} else {
$myValue = $row['option_value'];
$myMeta = $row['option_meta'];
$myStatus = $row['option_status'];
if ($myStatus === 'enable' || $myStatus === 'on' || $myStatus === 'active') {
$myStatus = true;
} else {
$myStatus = false;
}
if (substr($myValue, 0, 1) == '{') {
$myValue = json_decode($myValue, true);
}
if (substr($myMeta, 0, 1) == '{') {
$myMeta = json_decode($myMeta, true);
}
// save result
$qry_result[$row['option_key']] = ['value' => $myValue, 'meta' => $myMeta, 'status' => $myStatus];
}
}
return $qry_result;
}
public function query_search($_parameter = array())
{
$search = array_key_exists('search', $_parameter) ? $_parameter['search'] : null;
$image = array_key_exists('image', $_parameter) ? $_parameter['image'] : null;
$video = array_key_exists('video', $_parameter) ? $_parameter['video'] : null;
$audio = array_key_exists('audio', $_parameter) ? $_parameter['audio'] : null;
$other = array_key_exists('other', $_parameter) ? $_parameter['other'] : null;
$where = '';
if ($search) {
$where .= "(post_title LIKE '%{$search}%' OR post_content LIKE '%{$search}%')";
}
$_type = ['image', 'audio', 'video'];
$type = array();
if ($image) {
array_push($type, 'image');
}
if ($video) {
array_push($type, 'video');
}
if ($audio) {
array_push($type, 'audio');
}
if ($other) {
array_push($type, 'other');
}
if (count($type) > 0 && count($type) < 4) {
$where .= empty($where) ? '' : " AND ";
if ($other) {
if (count($type) == 1) {
$_type = join("\"' ,'\"", $_type);
$where .= "json_extract(post_meta, '\$.type') NOT IN ('\"{$_type}\"')";
} else {
$_type = join("\"' ,'\"", array_diff($_type, $type));
$type = count($type) > 1 ? "\"" . join("\"' ,'\"", $type) . "\"" : $type[0];
$where .= "(json_extract(post_meta, '\$.type') IN ('{$type}')";
$where .= " OR json_extract(post_meta, '\$.type') NOT IN ('\"{$_type}\"'))";
}
} else {
$type = count($type) > 1 ? "\"" . join("\"' ,'\"", $type) . "\"" : $type[0];
$where .= "json_extract(post_meta, '\$.type') in ('{$type}')";
}
}
$where .= empty($where) ? '' : " AND ";
$where .= "post_type = 'attachment'";
$length = 5;
$start = 0;
if ($_parameter['pagnation']) {
list($start, $length) = $this->controller->pagnation_make_limit($length);
}
$query = "SELECT SQL_CALC_FOUND_ROWS posts.*, FOUND_ROWS() FROM posts WHERE {$where} LIMIT {$start}, {$length}";
$result = \lib\db::query($query);
$query_rows = "SELECT FOUND_ROWS() as rows";
$result_rows = \lib\db::query($query_rows);
$rows = $result_rows->fetch_assoc()['rows'];
if ($_parameter['pagnation']) {
$this->controller->pagnation_make($rows);
$pagnation = $this->controller->pagnation;
} else {
$pagnation['total_pages'] = intval(ceil($rows / $length));
$pagnation['current'] = 1;
$pagnation['next'] = $pagnation['current'] + 1 <= $pagnation['total_pages'] ? $pagnation['current'] + 1 : false;
$pagnation['prev'] = $pagnation['current'] - 1 >= 1 ? $pagnation['current'] - 1 : false;
$pagnation['count_link'] = 7;
$pagnation['current_url'] = \lib\router::get_class() . '/attachments_data';
$pagnation['length'] = $length;
}
$decode_result = \lib\utility\filter::meta_decode(\lib\db::fetch_all($result));
return ['data' => $decode_result, 'pagnation' => $pagnation];
}
/**
* delete session file with given perm name
* @param [type] $_permName [description]
* @param boolean $_exceptMe [description]
* @return [type] [description]
*/
public static function deleteByPerm($_permName)
{
$permList = \lib\utility\option::permList(true);
$deleteResult = [];
// if permission exist
if (isset($permList[$_permName])) {
// find user with this permission
$perm_id = $permList[$_permName];
// connect to database
\lib\db::connect(true);
$qry = "SELECT `options`.option_value\n\t\t\t\tFROM users\n\t\t\t\tINNER JOIN `options` ON `options`.user_id = `users`.id\n\t\t\t\tWHERE `options`.option_cat = 'session' AND\n\t\t\t\t\tuser_permission = {$perm_id};";
// run query and give result
$result = @mysqli_query(\lib\db::$link, $qry);
// fetch all records
$result = \lib\db::fetch_all($result, 'option_value');
if ($result) {
$deleteResult = self::delete($result);
// for each file in delete
foreach ($deleteResult as $key => $value) {
// if file is deleted
if ($value === true) {
$qry = "DELETE FROM options WHERE option_cat = 'session' AND option_value = '{$key}';";
@mysqli_query(\lib\db::$link, $qry);
}
}
return $deleteResult;
}
}
return null;
}
/**
* return top pages visited on this site
* @return [type] [description]
*/
public static function top_pages($_count = 10)
{
self::createLink();
$service_id = self::service();
$qry = "SELECT\n\t\t\t\turls.url_url as url,\n\t\t\t\tcount(visitors.id) as total\n\t\t\tFROM urls\n\t\t\tINNER JOIN visitors ON urls.id = visitors.url_id\n\t\t\tWHERE visitors.`service_id` = {$service_id}\n\n\t\t\tGROUP BY visitors.url_id\n\t\t\tORDER BY total DESC\n\t\t\tLIMIT 0, {$_count}";
$result = @mysqli_query(self::$link, $qry);
if (!$result) {
return false;
}
$result = \lib\db::fetch_all($result);
foreach ($result as $key => $row) {
$result[$key]['url'] = urldecode($row['url']);
if (strpos($result[$key]['url'], 'http://') !== false) {
$result[$key]['text'] = substr($result[$key]['url'], 7);
}
}
return $result;
}
