/**
* generate signup stats by custom period
* @param string $_period [description]
* @return [type] [description]
*/
public static function signup($_period)
{
if (!$_period) {
$_period = "%Y-%m";
}
$qry = "SELECT\n\t\t\t\tDATE_FORMAT(user_createdate, '{$_period}') as date,\n\t\t\t\tcount(id) as total\n\t\t\tFROM users\n\t\t\tWHERE user_createdate != 0\n\t\t\tGROUP BY date\n\t\t";
$result = \lib\db::get($qry);
return $result;
}
/**
* [get_posts description]
* @param boolean $_forcheck [description]
* @return [type] [description]
*/
public function get_posts($_forcheck = false, $_args = null)
{
// check shortURL
$shortURL = \lib\db\url::checkShortURL();
if ($shortURL & is_array($shortURL)) {
// set datarow
$datarow = $shortURL;
} else {
$url = $this->url('path');
if (substr($url, 0, 7) == 'static/') {
return false;
}
$language = \lib\define::get_language();
$preview = \lib\utility::get('preview');
// search in url field if exist return row data
$post_status = "";
if (!$preview) {
$post_status = " AND post_status = 'publish' ";
}
$qry = "\n\t\t\t\tSELECT\n\t\t\t\t\t*\n\t\t\t\tFROM\n\t\t\t\t\tposts\n\t\t\t\tWHERE\n\t\t\t\t\tpost_url = '{$url}'\n\t\t\t\t\t{$post_status}\n\t\t\t\tLIMIT 1\n\t\t\t";
$datarow = \lib\db::get($qry, null, true);
// we have more than one record
if (isset($datarow[0])) {
$datarow = false;
}
}
if (isset($datarow['id'])) {
$post_id = $datarow['id'];
} else {
$datarow = false;
$post_id = 0;
}
if ($datarow && $post_id) {
if ($_forcheck && isset($datarow['post_type']) && isset($datarow['post_slug'])) {
return ['table' => 'posts', 'type' => $datarow['post_type'], 'slug' => $datarow['post_slug']];
} else {
foreach ($datarow as $key => $value) {
// if field contain json, decode it
if (substr($value, 0, 1) == '{') {
$datarow[$key] = json_decode($value, true);
if (is_null($datarow[$key]) && preg_match("/meta\$/", $key)) {
$datarow[$key] = json_decode(html_entity_decode($value), true);
}
}
}
// get meta of this post
$meta = \lib\db\posts::get_post_meta($post_id);
$datarow['postmeta'] = $meta;
return $datarow;
}
}
return false;
}
/**
* save session id database only one time
* if exist use old one
* else insert new one to database
* @param [type] $_userid [description]
* @param boolean $_meta [description]
* @return [type] [description]
*/
public static function save_once($_userid, $_meta = false, $_like = false)
{
if (!$_userid) {
$_userid = null;
$userStr = 'IS NULL';
} else {
$userStr = '= ' . $_userid;
// `user_id` $_userid AND
}
// create key value
$op_key = session_name();
// create query string
$qry = "SELECT *\n\t\t\tFROM options\n\t\t\tWHERE\n\n\t\t\t\t`option_cat` = 'session' AND\n\t\t\t\t`option_key` = '{$op_key}'\n\t\t";
// if we have meta then add it to query
if ($_meta) {
if ($_like) {
$_like = "LIKE";
} else {
$_like = '=';
}
$qry .= "AND `option_meta` {$_like} '{$_meta}'";
}
// run query and get result
$session_exist = \lib\db::get($qry, null, true);
$session_id = null;
// if record is not exist save session for first time
if (!isset($session_exist['option_value'])) {
$session_id = self::save($_userid, $_meta);
} else {
// get variables from datarow
$session_id = $session_exist['option_value'];
$option_id = $session_exist['id'];
$option_user_id = $session_exist['user_id'];
// if session id is not true return false!
if (!$session_id) {
return false;
}
// restart session to use our session id
self::restart($session_id);
// if user_id is not set for this user
// and this is first time we want to add to database
// call save to save existing session record
if ($_userid && !$option_user_id) {
$session_id = self::save($_userid, $_meta, $option_id);
}
}
// if successfully changed session return session id
return $session_id;
}
/**
* [find_url_from_shortURL description]
* @param [type] $_shortURL [description]
* @return [type] [description]
*/
public static function checkShortURL($_shortURL = null)
{
// set this shorturl, real url:)
if (!\lib\utility\option::get('config', 'meta', 'shortURL')) {
return null;
}
if (!$_shortURL) {
$_shortURL = \lib\router::get_url();
}
$table = null;
$field = null;
$urlPrefix = substr($_shortURL, 0, 3);
switch ($urlPrefix) {
case 'sp_':
// if this is url of one post
$table = 'post';
$field = "*";
break;
case 'st_':
// else if this is url of one term
$table = 'term';
$field = 'term_url as url';
break;
}
// if prefix is not correct return false
if (!$table) {
return null;
}
// remove prefix from url
$_shortURL = substr($_shortURL, 3);
$id = \lib\utility\shortURL::decode($_shortURL);
$table .= 's';
$qry = "SELECT {$field} FROM {$table} WHERE id = {$id}";
$result = \lib\db::get($qry, null, true);
if (!is_array($result)) {
return false;
}
if (!\lib\utility\option::get('config', 'meta', 'forceShortURL') && isset($result['post_url'])) {
$post_url = $result['post_url'];
// redirect to url of this post
$myredirect = new \lib\redirector();
$myredirect->set_url($post_url)->redirect();
}
// if not force simulate this url
return $result;
}
/**
* Searches for the first match.
*
* @param <type> $_title The title
*
* @return <type> ( description_of_the_return_value )
*/
public static function search($_title, $_term_type = 'tag')
{
$query = "\n\t\t\tSELECT\n\t\t\t\tterms.id,\n\t\t\t\tterms.term_title,\n\t\t\t\tterms.term_count,\n\t\t\t\tterms.term_url\n\t\t\tFROM\n\t\t\t\tterms\n\t\t\tWHERE\n\t\t\t\tterms.term_type = '{$_term_type}' AND\n\t\t\t\tterms.term_title LIKE '%{$_title}%'\n\t\t\tLIMIT 0,20\n\t\t";
return \lib\db::get($query);
}
/**
* check user id scored the comment
*
* @param <type> $_user_id The user identifier
* @param <type> $_comment_id The comment identifier
*/
public static function check($_user_id, $_comment_id)
{
$query = "\n\t\t\tSELECT\n\t\t\t\ttype\n\t\t\tFROM\n\t\t\t\tcommentdetails\n\t\t\tWHERE\n\t\t\t\tuser_id = {$_user_id} AND\n\t\t\t\tcomment_id = {$_comment_id}\n\t\t\tLIMIT 1\n\t\t";
$result = \lib\db::get($query, 'type', true);
return $result;
}
/**
* save once telegram user details
* @param [type] $_telegram_id [description]
* @param [type] $_fromDetail [description]
* @return [type] [description]
*/
private static function catchTelegramUser($_telegram_id, $_fromDetail = null)
{
// if user_id is not set try to give user_id from database
// search in db to find user_id
$qry = "SELECT `user_id`\n\t\t\tFROM options\n\t\t\tWHERE\n\t\t\t\t`option_cat` LIKE 'telegram\\_%' AND\n\t\t\t\t`option_key` LIKE 'user\\_%' AND\n\t\t\t\t`option_value` = {$_telegram_id}\n\t\t";
$my_user_id = \lib\db::get($qry, 'user_id', true);
if (is_numeric($my_user_id)) {
self::$user_id = $my_user_id;
}
// if user does not exist in db, signup it
if (!self::$user_id) {
// calc full_name of user
$fullName = trim(self::response('from', 'first_name') . ' ' . self::response('from', 'last_name'));
$mobile = 'tg_' . $_telegram_id;
// generate password
$password = \lib\utility\filter::temp_password();
\lib\db\users::signup($mobile, $password, true, $fullName);
self::$user_id = \lib\db\users::$user_id;
// save telegram user detail like name and username into options
$userDetail = ['cat' => 'telegram_' . self::$user_id, 'key' => 'user_' . self::response('from', 'username'), 'value' => $_telegram_id, 'meta' => $_fromDetail];
if (isset(self::$user_id)) {
$userDetail['user'] = self::$user_id;
$userDetail['status'] = 'enable';
} else {
$userDetail['status'] = 'disable';
}
// save in options table
\lib\utility\option::set($userDetail, true);
}
// save session id database only one time
// if exist use old one else insert new one to database
\lib\utility\session::save_once(self::$user_id, 'telegram_' . $_telegram_id);
if (!array_key_exists('tg', $_SESSION) || !is_array($_SESSION['tg'])) {
$_SESSION['tg'] = array();
}
if (self::$user_id) {
return true;
}
return false;
}
/**
* Gets the logitem id by logitem title
*
* @param <type> $_logitem_title The logitem title
*
* @return <type> The identifier.
*/
public static function get_id($_logitem_title)
{
$query = "\n\t\t\tSELECT\n\t\t\t\tid\n\t\t\tFROM\n\t\t\t\tlogitems\n\t\t\tWHERE\n\t\t\t\tlogitems.logitem_title = '{$_logitem_title}'\n\t\t\tLIMIT 1\n\t\t";
$id = \lib\db::get($query, 'id', true);
return $id;
}
/**
* generate temp mobile
*
* @return string ( description_of_the_return_value )
*/
public static function temp_mobile()
{
// get auto increment id from users table
$query = "\n\t\t\tSELECT\n\t\t\t\tAUTO_INCREMENT AS 'NEXTID'\n\t\t\tFROM\n\t\t\t\tinformation_schema.tables\n\t\t\tWHERE\n\t\t\t\ttable_name = 'users' AND\n\t\t\t\ttable_schema = DATABASE()\n\t\t";
$result = \lib\db::get($query, "NEXTID", true);
$next_id = intval($result) + 1;
$next_id = self::temp_password($next_id);
return "temp_" . $next_id;
}
public static function usage($_usageid, $_foreign = 'posts', $_type = null)
{
if ($_foreign === null) {
$_foreign = 'posts';
}
$type = null;
if ($_type) {
$type = " AND terms.term_type = '{$_type}' ";
}
$query = "\n\t\t\tSELECT\n\t\t\t\t*\n\t\t\tFROM\n\t\t\t\tterms\n\t\t\tINNER JOIN termusages ON termusages.term_id = terms.id\n\t\t\tWHERE\n\t\t\t\ttermusages.termusage_foreign = '{$_foreign}' AND\n\t\t\t\ttermusages.termusage_id = {$_usageid}\n\t\t\t\t{$type}\n\t\t";
return \lib\db::get($query);
}
/**
* Gets all comments for admin accept
*
* @param integer $_limit The limit
*
* @return <type> All.
*/
public static function admin_get($_limit = 50)
{
if (!is_numeric($_limit)) {
$_limit = 50;
}
$pagenation_query = "SELECT\tid\tFROM comments WHERE\tcomments.comment_type = 'comment' AND comments.comment_status = 'unapproved'\n\t\t -- comments::admin_get() for pagenation ";
list($limit_start, $_limit) = \lib\db::pagnation($pagenation_query, $_limit);
$limit = " LIMIT {$limit_start}, {$_limit} ";
$query = "\n\t\t\tSELECT\n\t\t\t\tcomments.*,\n\t\t\t\tposts.post_title AS 'title',\n\t\t\t\tposts.post_url AS 'url',\n\t\t\t\tusers.user_status AS 'status',\n\t\t\t\tusers.user_email AS 'email'\n\t\t\tFROM\n\t\t\t\tcomments\n\t\t\tINNER JOIN posts ON posts.id = comments.post_id\n\t\t\tINNER JOIN users ON users.id = comments.user_id\n\t\t\tWHERE\n\t\t\t\tcomments.comment_type = 'comment' AND\n\t\t\t\tcomments.comment_status = 'unapproved'\n\t\t\tORDER BY id ASC\n\t\t\t{$limit}\n\t\t\t-- comments::admin_get()\n\t\t";
return \lib\db::get($query);
}
/**
* Gets the post similar.
* default get the post id and return similar post
* you can set the poll is null and send list of tags in array or string of tags has splitable by ','
* and get post similar this tags
*
* @param <type> $_post_id The post identifier
* @param array $_options The options
*
* @return <type> The post similar.
*/
public static function get_post_similar($_post_id, $_limit = 5)
{
if (!is_numeric($_limit)) {
$_limit = 5;
}
$query = "\n\t\t\tSELECT\n\t\t\t\tposts.id AS 'id',\n\t\t\t\tposts.post_title AS 'title',\n\t\t\t\tposts.post_url AS 'url'\n\t\t\tFROM\n\t\t\t\ttermusages\n\t\t\tINNER JOIN posts ON posts.id = termusages.termusage_id\n\t\t\tWHERE\n\t\t\t\ttermusages.termusage_foreign = 'posts' AND\n\t\t\t\ttermusages.termusage_id != {$_post_id} AND\n\t\t\t\ttermusages.term_id IN\n\t\t\t\t(\n\t\t\t\t\tSELECT\n\t\t\t\t\t\tterm_id\n\t\t\t\t\tFROM\n\t\t\t\t\t\ttermusages\n\t\t\t\t\tWHERE\n\t\t\t\t\t\ttermusages.termusage_foreign = 'posts' AND\n\t\t\t\t\t\ttermusages.termusage_id = {$_post_id}\n\t\t\t\t)\n\t\t\tGROUP BY title,url,id\n\t\t\tORDER BY id DESC\n\t\t\tLIMIT {$_limit}\n\t\t";
$result = \lib\db::get($query);
return $result;
}
/**
* get the list of word
*
* @param <type> $_words The words
*/
public static function get($_words, $_only_words = false)
{
if (!$_words) {
return [];
}
$_words = self::to_array($_words);
$where = [];
foreach ($_words as $key => $value) {
$where[] = " words.word = '" . $value . "' ";
}
$where = join($where, "OR");
$fields = '*';
if ($_only_words) {
$fields = "words.word AS 'word'";
}
$query = "SELECT {$fields} FROM words WHERE {$where}";
if ($_only_words) {
$result = \lib\db::get($query, 'word');
} else {
$result = \lib\db::get($query);
}
return $result;
}
/**
* Gets the user language.
*
* @return <type> The language.
*/
public static function get_language($_user_id = null)
{
if ($_user_id === null) {
$user_id = self::$user_id;
} else {
$user_id = $_user_id;
}
$query = "\n\t\t\tSELECT\n\t\t\t\toption_value AS 'language'\n\t\t\tFROM\n\t\t\t\toptions\n\t\t\tWHERE\n\t\t\t\tpost_id IS NULL AND\n\t\t\t\tuser_id = {$user_id} AND\n\t\t\t\toption_cat = 'user_detail_{$user_id}' AND\n\t\t\t\toption_key = 'language'\n\t\t\tLIMIT 1\n\t\t";
return \lib\db::get($query, 'language', true);
}
/**
* get list of polls
* @param [type] $_user_id set userid
* @param [type] $_return set return field value
* @param string $_type set type of post
* @return [type] an array or number
*/
public static function get($_user_id = null, $_return = null, $_type = 'post')
{
// calc type if needed
if ($_type === null) {
$_type = "post_type LIKE NOT NULL";
} else {
$_type = "post_type = '" . $_type . "'";
}
// calc user id if exist
if ($_user_id) {
$_user_id = "AND user_id = {$_user_id}";
} else {
$_user_id = null;
}
// generate query string
$qry = "SELECT * FROM posts WHERE {$_type} {$_user_id}";
// run query
if ($_return && $_return !== 'count') {
$result = \lib\db::get($qry, $_return);
} else {
$result = \lib\db::get($qry);
}
// if user want count of result return count of it
if ($_return === 'count') {
return count($result);
}
// return last insert id
return $result;
}