<?php
/**
* Model class <i>SIB_Model_Users</i> represents account
*
* @package SIB_Model
*/
class SIB_Model_Users {
/**
* Tab table name
*/
const TABLE_NAME = 'sib_model_users';
/**
* Holds found campaign count
*
* @var $found_count
*/
static $found_count;
/**
* Holds all campaign count
*
* @var $all_count
*/
static $all_count;
/** Create Table */
public static function createTable() {
global $wpdb;
$table_name = $wpdb->prefix . self::TABLE_NAME;
//Check if table exists
$table_check_query = 'SHOW TABLES LIKE ' . "'" . $table_name. "'" . ';';
$wpdb->query($table_check_query);
//table doesnot exist
if (empty($wpdb->last_result)) {
// create list table.
$creation_query =
'CREATE TABLE IF NOT EXISTS ' . $table_name . ' (
`id` int(20) NOT NULL AUTO_INCREMENT,
`email` varchar(255),
`code` varchar(100),
`listIDs` longtext,
`redirectUrl` varchar(255),
`info` TEXT CHARACTER SET utf8 COLLATE utf8_unicode_ci,
`frmid` int(2),
`user_added_date` DATETIME NOT NULL,
PRIMARY KEY (`id`)
);';
require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
$wpdb->query($creation_query);
}
}
/**
* Remove table
*/
public static function removeTable() {
global $wpdb;
$query = 'DROP TABLE IF EXISTS ' . $wpdb->prefix . self::TABLE_NAME . ';';
$wpdb->query( $query ); // db call ok; no-cache ok.
}
/**
* Get data by id
*
* @param int $id - user id.
* @return bool|mixed
*/
public static function get_data( $id ) {
global $wpdb;
$query = $wpdb->prepare("SELECT * from " . $wpdb->prefix . self::TABLE_NAME . " where id = %d",array(esc_sql($id)));
$results = $wpdb->get_results( $query, ARRAY_A ); // db call ok; no-cache ok.
if ( is_array( $results ) ) {
return $results[0];
} else {
return false;
}
}
/**
* Get data by code
*
* @param string $code - code.
* @return array|bool|null|object|void
*/
public static function get_data_by_code( $code ) {
global $wpdb;
$query = $wpdb->prepare("SELECT * from " . $wpdb->prefix . self::TABLE_NAME . " where code like %s",array(esc_sql($code)));
$results = $wpdb->get_row( $query,ARRAY_A ); // db call ok; no-cache ok.
if ( is_array( $results ) && count( $results ) > 0 ) {
return $results;
} else {
return false;
}
}
/**
* Get code by email.
*
* @param string $email - email.
* @param int $formID - form ID.
* @return array|bool|null|object|void
*/
public static function get_data_by_email( $email, $formID ) {
global $wpdb;
$query = $wpdb->prepare("SELECT * from " . $wpdb->prefix . self::TABLE_NAME . " where email = %s and frmid = %d",array(esc_sql($email),esc_sql($formID)));
$results = $wpdb->get_row( $query,ARRAY_A ); // db call ok; no-cache ok.
if ( is_array( $results ) && count( $results ) > 0 ) {
return $results;
} else {
return false;
}
}
/**
* Add record
*
* @param array $data - record data.
* @return null|string
*/
public static function add_record( $data ) {
global $wpdb;
foreach ($data as $key => $value) {
if(!in_array($key, array("listIDs","info")))
$data[$key] = esc_sql($value);
}
$query = $wpdb->prepare('INSERT INTO ' . $wpdb->prefix . self::TABLE_NAME . ' (email,code,info,frmid,listIDs,redirectUrl,user_added_date) VALUES (%s, %s, %s, %d, %s, %s, %s) ',array( $data["email"], $data["code"], $data["info"], $data["frmid"], $data["listIDs"], $data["redirectUrl"], $data["user_added_date"] ));
$wpdb->query( $query ); // db call ok; no-cache ok.
$index = $wpdb->get_var( 'SELECT LAST_INSERT_ID();' ); // db call ok; no-cache ok.
return $index;
}
/**
* Check email exist
*
* @param string $email - email.
* @param string $id - id.
* @return bool
*/
public static function is_exist_same_email( $email, $id = '' ) {
global $wpdb;
$query = $wpdb->prepare("SELECT * from " . $wpdb->prefix . self::TABLE_NAME . " where email like %s",array(esc_sql($email)));
$results = $wpdb->get_results( $query, ARRAY_A ); // db call ok; no-cache ok.
if ( is_array( $results ) && (count( $results ) > 0) ) {
if ( '' === $id ) {
return true;
}
if ( isset( $results ) && is_array( $results ) ) {
foreach ( $results as $result ) {
if ( $result['id'] != $id ) {
return true;
}
}
}
}
return false;
}
/**
* Remove guest
*
* @param int $id - id.
*/
public static function remove_record( $id ) {
global $wpdb;
$query = $wpdb->prepare("DELETE from " . $wpdb->prefix . self::TABLE_NAME . " where id = %d",array(esc_sql($id)));
$wpdb->query( $query ); // db call ok; no-cache ok.
}
/**
* Get all guests by pagenum, per_page
*
* @param string $orderby - ORDER BY.
* @param string $order - sort order.
* @param int $pagenum - page number.
* @param int $per_page - count per page.
* @return array|null|object
*/
public static function get_all( $orderby = 'email', $order = 'asc', $pagenum = 1, $per_page = 15 ) {
global $wpdb;
$limit = ($pagenum - 1) * $per_page;
$query = 'SELECT * FROM ' . $wpdb->prefix . self::TABLE_NAME . ' ORDER BY %s %s LIMIT %d,%d';
$query = $wpdb->prepare($query,array(esc_sql($orderby), esc_sql($order), esc_sql($limit), esc_sql($per_page)));
$results = $wpdb->get_results( $query, ARRAY_A ); // db call ok; no-cache ok.
self::$found_count = self::get_count_element();
if ( ! is_array( $results ) ) {
$results = array();
return $results;
}
return $results;
}
/** Get all records of table */
public static function get_all_records() {
global $wpdb;
$query = $wpdb->prepare('select * from ' . $wpdb->prefix . self::TABLE_NAME . ' order by %s %s;', array("email","asc"));
$results = $wpdb->get_results( $query, ARRAY_A ); // db call ok; no-cache ok.
if ( ! is_array( $results ) ) {
$results = array();
return $results;
}
return $results;
}
/** Get count of row */
public static function get_count_element() {
global $wpdb;
$query = 'Select count(*) from ' . $wpdb->prefix . self::TABLE_NAME . ';';
$count = $wpdb->get_var( $query ); // db call ok; no-cache ok.
return $count;
}
/** Add prefix to the table */
public static function add_prefix() {
global $wpdb;
if ( $wpdb->get_var( "SHOW TABLES LIKE '" . self::TABLE_NAME . "'" ) == self::TABLE_NAME ) {
$query = 'ALTER TABLE ' . self::TABLE_NAME . ' RENAME TO ' . $wpdb->prefix . self::TABLE_NAME . ';';
$wpdb->query( $query ); // db call ok; no-cache ok.
}
}
/** Add column to the table */
public static function add_user_added_date_column() {
global $wpdb;
$user_added = 'user_added_date';
$result = $wpdb->query( $wpdb->prepare( 'SHOW COLUMNS FROM ' . $wpdb->prefix . self::TABLE_NAME . ' LIKE %s ', $user_added ) ); // db call ok; no-cache ok.
if ( empty( $result ) ) {
$query = 'ALTER TABLE ' . $wpdb->prefix . self::TABLE_NAME . ' ADD COLUMN user_added_date DATETIME NOT NULL';
$wpdb->query( $query );
}
}
/**
* add new column to the table which will reflect sent DOI flag
*/
public static function add_flag_doi_sent() {
global $wpdb;
$doi_sent = 'doi_sent';
$result = $wpdb->query( $wpdb->prepare( 'SHOW COLUMNS FROM ' . $wpdb->prefix . self::TABLE_NAME . ' LIKE %s ', $doi_sent ) ); // db call ok; no-cache ok.
if ( empty( $result ) ) {
$query = 'ALTER TABLE ' . $wpdb->prefix . self::TABLE_NAME . ' ADD COLUMN ' . $doi_sent . ' int(20) NOT NULL DEFAULT 0';
$wpdb->query( $query );
}
}
/** Update user data to the table */
public static function update_element( $data ) {
global $wpdb;
$query = $wpdb->prepare( 'update ' . $wpdb->prefix . self::TABLE_NAME . ' set info= %s where id= %d;', array( $data['info'], esc_sql( $data['id'] ) ) );
$wpdb->query( $query );
return true;
}
/**
* update to mark the DOI sent
*/
public static function make_doi_sent($email) {
global $wpdb;
$query = $wpdb->prepare('update ' . $wpdb->prefix . self::TABLE_NAME . ' set doi_sent= %d where email= %s;', array(esc_sql(1), esc_sql($email) ));
$wpdb->query($query);
return true;
}
}