File "class.constructor.php"
Full Path: /var/www/bvnghean.vn/save_bvnghean.vn/wp-content/plugins/wpDataTables_v1.6.1/source/class.constructor.php
File size: 81.22 KB
MIME-type: text/x-php
Charset: utf-8
<?php
/**
* Class Constructor contains methods and properties for constructing the tables
* in wpDataTables WordPress plugin
*
* @author cjbug@ya.ru
*
* @since June 2014
*/
class wpDataTableConstructor {
private $_name;
private $_index;
private $_db;
private $_id;
/*** For the WP DB type query ***/
private $_tables_fields = array();
private $_select_arr = array();
private $_where_arr = array();
private $_group_arr = array();
private $_from_arr = array();
private $_inner_join_arr = array();
private $_left_join_arr = array();
private $_table_aliases = array();
private $_column_aliases = array();
private $_column_headers = array();
private $_has_groups = false;
/** Query text **/
private $_query = '';
/**
* The constructor
*/
public function __construct(){
if(WDT_ENABLE_MYSQL && get_option('wdtUseSeparateCon')){
$this->_db = new PDTSql(WDT_MYSQL_HOST, WDT_MYSQL_DB, WDT_MYSQL_USER, WDT_MYSQL_PASSWORD);
}
}
/**
* Sets ID;
* @param type $id
*/
public function setTableId( $id ){
$this->_id = $id;
}
/**
* Gets table ID
*/
public function getTableId(){
return $this->_id;
}
/**
* Generate the new unique table name (For MySQL)
*/
public function generateTableName(){
$this->_index = (int) get_option('wdtGeneratedTablesCount', 0);
$this->_index += 1;
$this->_name = 'wpdatatable_'.$this->_index;
if(!get_option('wdtUseSeparateCon')){
global $wpdb;
$this->_name = $wpdb->prefix.$this->_name;
}
$this->_name = apply_filters( 'wpdatatables_before_generate_constructed_table_name', $this->_name );
return $this->_name;
}
/**
* Helper function to translate special UTF-8 to latin for MySQL
*/
private static function slugify($text){
// replace non letter or digits by _
$text = preg_replace('#[^\\pL\d]+#u', '_', $text);
// trim
$text = trim($text, '_');
// transliterate
if (function_exists('iconv')){
$text = iconv('UTF-8', 'ASCII//TRANSLIT//IGNORE', $text);
}
// lowercase
$text = strtolower($text);
// remove unwanted characters
$text = preg_replace('#[^-\w]+#', '', $text);
// WP sanitize
$text = str_replace( array( '-', '_' ), '', sanitize_title( $text ) );
if ( empty($text) || is_numeric($text)){
return 'wdt_column';
}
return $text;
}
/**
* Helper function to generate unique MySQL column headers
*/
private static function generateMySQLColumnName( $header, $existing_headers ){
// Prepare the column MySQL title
$column_header = self::slugify( $header );
// Add index until column header becomes unique
if( in_array( $column_header, $existing_headers ) ){
$index = 0;
do {
$index++;
$try_column_header = $column_header.'_'.$index;
} while( in_array( $try_column_header, $existing_headers ) );
$column_header = $try_column_header;
}
return $column_header;
}
/**
* Helper function to prepare the filter, editor, column types, and create statement
*/
private static function defineColumnProperties( $column_header, $column_type ){
// Defaults
$columnProperties = array(
'editor_type' => 'text',
'column_type' => 'string',
'filter_type' => 'text',
'create_block' => "`{$column_header}` VARCHAR(2000) "
);
switch( $column_type ){
case 'input':
$columnProperties = array(
'editor_type' => 'text',
'column_type' => 'string',
'filter_type' => 'text',
'create_block' => "`{$column_header}` VARCHAR(255) "
);
break;
case 'integer':
$columnProperties = array(
'editor_type' => 'text',
'column_type' => 'int',
'filter_type' => 'number',
'create_block' => "`{$column_header}` INT(11) "
);
break;
case 'float':
$columnProperties = array(
'editor_type' => 'text',
'column_type' => 'float',
'filter_type' => 'number',
'create_block' => "`{$column_header}` FLOAT(16,4) "
);
break;
case 'memo':
$columnProperties = array(
'editor_type' => 'textarea',
'column_type' => 'string',
'filter_type' => 'text',
'create_block' => "`{$column_header}` TEXT "
);
break;
case 'select':
$columnProperties = array(
'editor_type' => 'selectbox',
'column_type' => 'string',
'filter_type' => 'select',
'create_block' => "`{$column_header}` VARCHAR(2000) "
);
break;
case 'multiselect':
$columnProperties = array(
'editor_type' => 'multi-selectbox',
'column_type' => 'string',
'filter_type' => 'select',
'create_block' => "`{$column_header}` VARCHAR(2000) "
);
break;
case 'date':
$columnProperties = array(
'editor_type' => 'date',
'column_type' => 'date',
'filter_type' => 'date-range',
'create_block' => "`{$column_header}` DATE "
);
break;
case 'email':
$columnProperties = array(
'editor_type' => 'email',
'column_type' => 'email',
'filter_type' => 'text',
'create_block' => "`{$column_header}` VARCHAR(2000) "
);
break;
case 'link':
$columnProperties = array(
'editor_type' => 'link',
'column_type' => 'link',
'filter_type' => 'text',
'create_block' => "`{$column_header}` VARCHAR(2000) "
);
break;
case 'file':
$columnProperties = array(
'editor_type' => 'link',
'column_type' => 'attachment',
'filter_type' => 'none',
'create_block' => "`{$column_header}` VARCHAR(2000) "
);
break;
case 'image':
$columnProperties = array(
'editor_type' => 'image',
'column_type' => 'attachment',
'filter_type' => 'none',
'create_block' => "`{$column_header}` VARCHAR(2000) "
);
break;
default:
$columnProperties = array(
'editor_type' => 'text',
'column_type' => 'string',
'filter_type' => 'text',
'create_block' => "`{$column_header}` VARCHAR(2000) "
);
break;
}
return $columnProperties;
}
/**
* Generates and saves a new MySQL table and a new wpDataTable
*/
public function generateManualTable( $table_data ){
global $wpdb;
$this->_table_data = apply_filters( 'wdt_before_generate_manual_table', $table_data );
// Generate the MySQL table name
$this->generateTableName();
// Create the wpDataTable metadata
$wpdb->insert(
$wpdb->prefix."wpdatatables",
array(
'title' => $this->_table_data['name'],
'table_type' => 'manual',
'content' => 'SELECT * FROM '.$this->_name,
'server_side' => 1,
'mysql_table_name' => $this->_name
)
);
// Store the new table metadata ID
$wpdatatable_id = $wpdb->insert_id;
// Prepare the create statement for the table itself
$create_statement = "CREATE TABLE ".$this->_name." (
wdt_ID INT( 11 ) NOT NULL AUTO_INCREMENT,";
$column_headers = array();
$column_index = 0;
// Add metadata for ID column
$wpdb->insert(
$wpdb->prefix."wpdatatables_columns",
array(
'table_id' => $wpdatatable_id,
'orig_header' => 'wdt_ID',
'display_header' => 'wdt_ID',
'filter_type' => 'null_str',
'column_type' => 'int',
'visible' => 0,
'pos' => $column_index,
'id_column' => 1
)
);
$column_index++;
foreach( $this->_table_data['columns'] as $column ){
$column_header = self::generateMySQLColumnName( $column['name'], $column_headers );
$column_headers[] = $column_header;
if( !isset( $column['orig_header'] ) ){
$column['orig_header'] = $column['name'];
}
$this->_column_headers[ $column['orig_header'] ] = $column_header;
$columnProperties = self::defineColumnProperties($column_header, $column['type']);
// Create the column metadata in WPDB
$wpdb->insert(
$wpdb->prefix."wpdatatables_columns",
array(
'table_id' => $wpdatatable_id,
'orig_header' => $column_header,
'display_header' => $column['name'],
'filter_type' => $columnProperties['filter_type'],
'column_type' => $columnProperties['column_type'],
'pos' => $column_index,
'possible_values' => str_replace( ',,;,|', '|', $column['possible_values'] ),
'default_value' => $column['default_value'],
'input_type' => $columnProperties['editor_type']
)
);
$create_statement .= $columnProperties['create_block'].', ';
$column_index++;
}
// Add the ID unique key
$create_statement .= " UNIQUE KEY wdt_ID (wdt_ID)) CHARACTER SET=utf8 COLLATE utf8_general_ci";
// Call the create statement on WPDB or on external DB if it is defined
if(get_option('wdtUseSeparateCon')){
// External DB
$this->_db->doQuery( $create_statement, array() );
}else{
$wpdb->query( $create_statement );
}
// Update the index in WPDB
update_option('wdtGeneratedTablesCount',$this->_index);
return $wpdatatable_id;
}
/**
* Generates and returns a MySQL query based on user's visual input
*/
public function generateMySQLBasedQuery( $table_data ){
global $wpdb;
$this->_table_data = apply_filters( 'wdt_before_generate_mysql_based_query', $table_data );
if( !isset( $this->_table_data['where_conditions'] ) ){
$this->_table_data['where_conditions'] = array();
}
if( isset( $this->_table_data['grouping_rules'] ) ){
$this->_has_groups = true;
}
if( !isset( $table_data['mysql_columns'] ) ){
$table_data['mysql_columns'] = array();
}
// Initializing structure for the SELECT part of query
$this->_prepareMySQLSelectBlock( $table_data['mysql_columns'] );
// Initializing structure for the WHERE part of query
$this->_prepareMySQLWhereBlock();
// Prepare the GROUP BY block
$this->_prepareMySQLGroupByBlock();
// Prepare the join rules
$this->_prepareMySQLJoinedQueryStructure();
// Prepare the query itself
$this->_query = $this->_buildMySQLQuery();
}
/**
* Generates and returns a WP based MySQL query based on user's visual input
*/
public function generateWPBasedQuery( $table_data ){
global $wpdb;
$this->_table_data = apply_filters( 'wdt_before_generate_wp_based_query', $table_data );
if( !isset( $this->_table_data['where_conditions'] ) ){
$this->_table_data['where_conditions'] = array();
}
if( isset( $this->_table_data['grouping_rules'] ) ){
$this->_has_groups = true;
}
// Prepare the tables fields
$this->_tables_fields = self::generateTablesFieldsStructureWPBased( $this->_table_data['post_columns'] );
// Initializing structure for the SELECT part of query
$this->_prepareWPSelectBlock();
// We need to go through the rest of where conditions and add 'inner join' parts for them if needed
$this->_prepareWPWhereBlock();
// We need to add 'GROUP BY' blocks
$this->_prepareWPGroupByBlock();
if( ( $this->_table_data['handle_post_types'] == 'join' )
|| count( $this->_table_data['post_types'] ) == 1
){
// We do JOINs
$this->_prepareWPJoinedQueryStructure();
$this->_query = $this->_buildWPJoinedQuery();
}else{
// We do UNIONs
$this->_query = $this->_buildWPJoinedQuery();
}
}
/**
* Helper function to generate the fields structure from MySQL tables
*/
private function _prepareMySQLSelectBlock(){
foreach( $this->_table_data['mysql_columns'] as $mysql_column ){
$mysql_column_arr = explode( '.', $mysql_column );
if( !isset($this->_select_arr[$mysql_column_arr[0]]) ){
$this->_select_arr[$mysql_column_arr[0]] = array();
}
$this->_select_arr[$mysql_column_arr[0]][] = $mysql_column;
if( !in_array( $mysql_column_arr[0], $this->_from_arr ) ){
$this->_from_arr[] = $mysql_column_arr[0];
}
}
}
/**
* Helper function to generate the fields structire from WP Posts data
*/
public static function generateTablesFieldsStructureWPBased( $columns ){
$tables_fields = array();
// Parse the columns list, generate table aliases and the columns
foreach( $columns as $post_column ){
$post_column_arr = explode('.',$post_column);
if(count($post_column_arr) == 2){
// This is a column of a post table
if(!isset($tables_fields[$post_column_arr[0]])){
$tables_fields[$post_column_arr[0]] = array(
'table' => 'posts',
'post_type' => $post_column_arr[0],
'sql_alias' => self::prepareSqlAlias( 'posts_' . $post_column_arr[0] ),
'columns' => array()
);
}
$tables_fields[$post_column_arr[0]]['columns'][] = array(
'field' => $post_column_arr[1],
'col_alias' => self::prepareSqlAlias( $post_column ),
'col_internal_name' => $post_column
);
}else{
// This is a taxonomy or a meta value
if($post_column_arr[1] == 'meta'){
// This is a meta value
$tables_fields[$post_column_arr[2]] = array(
'table' => 'postmeta',
'sql_alias' => self::prepareSqlAlias( $post_column.'_tbl' ),
'col_alias' => self::prepareSqlAlias( $post_column ),
'col_internal_name' => $post_column,
'post_type' => $post_column_arr[0]
);
}elseif($post_column_arr[1] == 'taxonomy'){
// This is a taxonomy value
$tables_fields[$post_column_arr[2]] = array(
'table' => 'taxonomy',
'sql_alias' => self::prepareSqlAlias( $post_column.'_tbl' ),
'col_alias' => self::prepareSqlAlias( $post_column ),
'col_internal_name' => $post_column,
'post_type' => $post_column_arr[0]
);
}
}
}
return $tables_fields;
}
public static function prepareSqlAlias( $alias ){
$sqlAlias = str_replace( '.', '_', $alias );
$sqlAlias = str_replace( '-', '_', $sqlAlias );
return $sqlAlias;
}
public static function buildWhereCondition( $leftOperand, $operator, $rightOperand, $isValue = true ){
$rightOperand = stripslashes_deep( $rightOperand );
$wrap = $isValue ? "'" : "";
switch($operator){
case 'eq':
return "{$leftOperand} = {$wrap}{$rightOperand}{$wrap}";
case 'neq':
return "{$leftOperand} != {$wrap}{$rightOperand}{$wrap}";
case 'gt':
return "{$leftOperand} > {$wrap}{$rightOperand}{$wrap}";
case 'gtoreq':
return "{$leftOperand} >= {$wrap}{$rightOperand}{$wrap}";
case 'lt':
return "{$leftOperand} < {$wrap}{$rightOperand}{$wrap}";
case 'ltoreq':
return "{$leftOperand} <= {$wrap}{$rightOperand}{$wrap}";
case 'in':
return "{$leftOperand} IN ({$rightOperand})";
case 'like':
return "{$leftOperand} LIKE {$wrap}{$rightOperand}{$wrap}";
case 'plikep':
return "{$leftOperand} LIKE {$wrap}%{$rightOperand}%{$wrap}";
}
}
/**
* Prepares the SELECT part for the WP-based tables
*/
private function _prepareWPSelectBlock(){
global $wpdb;
if(empty($this->_tables_fields)){ return; }
$thumbSizeString = self::getThumbSizeString();
foreach($this->_tables_fields as $valueName => &$fields){
// Fill in the SQL alias of the table
$this->_table_aliases[] = $fields['sql_alias'];
if($fields['table'] == 'posts'){
foreach($fields['columns'] as $table_column){
if(!isset($this->_select_arr[$fields['sql_alias']])){
$this->_select_arr[$fields['sql_alias']] = array();
}
if( $table_column['field'] == 'title_with_link_to_post' ){
// Generating an "<a href="..."" link to the post
$this->_select_arr[$fields['sql_alias']][] = 'CONCAT(\'<a href="\','.$fields['sql_alias'].'.guid,\'">\','.$fields['sql_alias'].'.post_title,\'</a>\') AS '.$table_column['col_alias'];
}elseif( $table_column['field'] == 'thumbnail_with_link_to_post' ){
// Generating an "<a href="..."" link to the post and a thumbnail URL depending on WP settings
$this->_select_arr[$fields['sql_alias'].'_img'][] = 'CONCAT(
\'<a href="\',
'.$fields['sql_alias'].'.guid,
\'"><img src="\',
REPLACE(
'.$fields['sql_alias'].'_img'.'.guid,
CONCAT(
\'.\',
SUBSTRING_INDEX(
'.$fields['sql_alias'].'_img'.'.guid,
\'.\',
-1
)
),
CONCAT(
\''.$thumbSizeString.'\' ,
SUBSTRING_INDEX(
'.$fields['sql_alias'].'_img'.'.guid,
\'.\',
-1
)
)
),
\'" /></a>\'
) AS '.$table_column['col_alias'];
$this->_left_join_arr[$fields['sql_alias'].'_img'] = '(SELECT '.$fields['sql_alias'].'_imgposts.guid AS guid, '.$fields['sql_alias'].'_imgpostmeta.post_id AS post_id
FROM '.$wpdb->postmeta.' AS '.$fields['sql_alias'].'_imgpostmeta
INNER JOIN '. $wpdb->posts .' AS '.$fields['sql_alias'].'_imgposts
ON '.$fields['sql_alias'].'_imgpostmeta.meta_value = '.$fields['sql_alias'].'_imgposts.ID
WHERE '.$fields['sql_alias']."_imgpostmeta.meta_key = '_thumbnail_id' ".
') AS '.$fields['sql_alias'].'_img';
$this->_where_arr[$fields['sql_alias'].'_img'][] = $fields['sql_alias'].'_img.post_id = '.$fields['sql_alias'].'.ID';
}elseif( $table_column['field'] == 'post_author' ){
// Get the author nicename instead of ID
$this->_select_arr[$fields['sql_alias'].'_author'][] = $fields['sql_alias'].'_author.display_name AS '.$table_column['col_alias'];
$this->_inner_join_arr[$fields['sql_alias'].'_author'] = $wpdb->users.' AS '.$fields['sql_alias'].'_author';
$this->_where_arr[$fields['sql_alias'].'_author'][] = $fields['sql_alias'].'_author.ID = '.$fields['sql_alias'].'.post_author';
}elseif( $table_column['field'] == 'post_content_limited_100_chars' ){
// Get post content limited to 100 chars
$this->_select_arr[$fields['sql_alias']][] = 'LEFT( '.$fields['sql_alias'].'.post_content, 100) AS '.$table_column['col_alias'];
}else{
$this->_select_arr[$fields['sql_alias']][] = $fields['sql_alias'].'.'.$table_column['field'].' AS '.$table_column['col_alias'];
}
$this->_column_aliases[$table_column['col_internal_name']] = $table_column['col_alias'];
// Look up for this column in additional 'where' conditions
foreach( $this->_table_data['where_conditions'] as $where_key=>&$where_condition ){
$where_column_arr = explode( '.', $where_condition['column'] );
if( ( count( $where_column_arr ) == 2 )
&& ( $valueName == $where_column_arr[0] ) ){
if(!isset($this->_where_arr[$fields['sql_alias']])){
$this->_where_arr[$fields['sql_alias']] = array();
}
$this->_where_arr[$fields['sql_alias']][] = self::buildWhereCondition(
'posts_'.$where_condition['column'],
$where_condition['operator'],
$where_condition['value']
);
unset( $this->_table_data['where_conditions'][$where_key] );
}
}
}
$this->_from_arr[$fields['sql_alias']] = $wpdb->posts.' AS '.$fields['sql_alias'];
if( $fields['post_type'] != 'all' ){
$this->_where_arr[$fields['sql_alias']][] = $fields['sql_alias'].".post_type = '".$fields['post_type']."'";
}
}elseif($fields['table'] == 'postmeta'){
if(!isset($this->_select_arr[$fields['sql_alias']])){
$this->_select_arr[$fields['sql_alias']] = array();
}
if( !$this->_has_groups ){
$this->_select_arr[$fields['sql_alias']][] = $fields['sql_alias'].'.meta_value AS '.$fields['col_alias'];
}else{
$this->_select_arr[$fields['sql_alias']][] = 'GROUP_CONCAT(distinct '.$fields['sql_alias'].'.meta_value) AS '.$fields['col_alias'];
}
$this->_inner_join_arr[$fields['sql_alias']] = self::preparePostMetaSubquery( $fields['sql_alias'], $fields['post_type'] );
if( !isset( $this->_where_arr[$fields['sql_alias']] ) ){
$this->_where_arr[$fields['sql_alias']] = array();
}
$this->_where_arr[$fields['sql_alias']][] = $fields['sql_alias'].".meta_key = '".$valueName."' AND ".$fields['sql_alias'].".id = posts_".$fields['post_type'].".ID ";
$this->_column_aliases[$fields['col_internal_name']] = $fields['col_alias'];
foreach( $this->_table_data['where_conditions'] as $where_key=>&$where_condition ){
$where_column_arr = explode( '.', $where_condition['column'] );
if( ( count( $where_column_arr ) == 3 )
&& ( $where_column_arr[1] == 'meta' )
&& ( $valueName == $where_column_arr[2] ) ){
if(!isset($this->_where_arr[$fields['sql_alias']])){
$this->_where_arr[$fields['sql_alias']] = array();
}
$this->_where_arr[$fields['sql_alias']][] = self::buildWhereCondition(
$fields['col_alias'],
$where_condition['operator'],
$where_condition['value']
);
unset($this->_table_data['where_conditions'][$where_key]);
}
}
}elseif($fields['table'] == 'taxonomy'){
if(!isset($this->_select_arr[$fields['sql_alias']])){
$this->_select_arr[$fields['sql_alias']] = array();
}
if( !$this->_has_groups ){
$this->_select_arr[$fields['sql_alias']][] = $fields['sql_alias'].'.name AS '.$fields['col_alias'];
}else{
$this->_select_arr[$fields['sql_alias']][] = 'GROUP_CONCAT(distinct '.$fields['sql_alias'].'.name) AS '.$fields['col_alias'];
}
$this->_inner_join_arr[$fields['sql_alias']] = self::preparePostTaxonomySubquery( $fields['sql_alias'], $valueName );
$this->_where_arr[$fields['sql_alias']][] = $fields['sql_alias'].".ID = posts_".$fields['post_type'].".id ";
$this->_column_aliases[$fields['col_internal_name']] = $fields['col_alias'];
foreach( $this->_table_data['where_conditions'] as $where_key=>&$where_condition ){
$where_column_arr = explode( '.', $where_condition['column'] );
if( ( count( $where_column_arr ) == 3 )
&& ( $where_column_arr[1] == 'taxonomy' )
&& ( $valueName == $where_column_arr[2] ) ){
if(!isset($this->_where_arr[$fields['sql_alias']])){
$this->_where_arr[$fields['sql_alias']] = array();
}
$this->_where_arr[$fields['sql_alias']][] = self::buildWhereCondition(
$fields['col_alias'],
$where_condition['operator'],
$where_condition['value']
);
unset($this->_table_data['where_conditions'][$where_key]);
}
}
}
}
}
private function _prepareMySQLWhereBlock(){
if( empty( $this->_table_data['where_conditions'] ) ){
return;
}
foreach( $this->_table_data['where_conditions'] as $where_condition ){
$where_column_arr = explode( '.', $where_condition['column'] );
if( !in_array( $where_column_arr[0], $this->_from_arr ) ){
$this->_from_arr[] = $where_column_arr[0];
}
$this->_where_arr[$where_column_arr[0]][] = self::buildWhereCondition(
$where_condition['column'],
$where_condition['operator'],
$where_condition['value']
);
}
}
/**
* Prepares the WHERE block for WP-based query
*/
private function _prepareWPWhereBlock(){
global $wpdb;
if( empty( $this->_table_data['where_conditions'] ) ){
return;
}
foreach( $this->_table_data['where_conditions'] as $where_condition ){
$where_column_arr = explode('.',$where_condition['column']);
if(count($where_column_arr) == 2){
$tbl_alias = 'posts_'.$where_column_arr[0];
$tbl_alias = str_replace( '-','_',$tbl_alias );
$this->_from_arr[$tbl_alias] = $wpdb->posts.'_'.$where_column_arr[0].' AS '.$tbl_alias;
$this->_where_arr[$tbl_alias] = array();
$this->_where_arr[$tbl_alias][] = self::buildWhereCondition(
$where_column_arr[1],
$where_condition['operator'],
$where_condition['value']
);
}else{
if(count($where_column_arr) == 3){
$tbl_alias = str_replace( '.','_',$where_condition['column'] ).'_tbl';
$tbl_alias = str_replace( '-','_',$tbl_alias );
if($where_column_arr[1] == 'meta'){
$this->_inner_join_arr[$tbl_alias] = self::preparePostMetaSubquery(
$tbl_alias,
$where_column_arr[0],
$where_column_arr[2]
);
$this->_where_arr[$tbl_alias] = array();
$this->_where_arr[$tbl_alias][] = self::buildWhereCondition(
$tbl_alias.'.meta_value',
$where_condition['operator'],
$where_condition['value']
);
}elseif($where_column_arr[1] == 'taxonomy'){
$this->_inner_join_arr[$tbl_alias] = self::preparePostTaxonomySubquery( $tbl_alias, $where_column_arr[2] );
$this->_where_arr[$tbl_alias] = array();
$this->_where_arr[$tbl_alias][] = self::buildWhereCondition(
$tbl_alias.'.name',
$where_condition['operator'],
$where_condition['value']
);
}
$this->_where_arr[$tbl_alias][] = self::buildWhereCondition(
$tbl_alias.'.id',
'eq',
'posts_'.$where_column_arr[0].'.ID'
);
}
}
}
}
public static function preparePostMetaSubquery( $alias, $post_type, $meta_key = '' ){
global $wpdb;
if(empty($alias) || empty($post_type)){ return ''; }
$post_meta_subquery = "(SELECT {$alias}_posts.ID as id, meta_value, meta_key ";
$post_meta_subquery .= " FROM {$wpdb->postmeta} AS {$alias}_postmeta ";
$post_meta_subquery .= " INNER JOIN {$wpdb->posts} AS {$alias}_posts ";
$post_meta_subquery .= " ON {$alias}_postmeta.post_id = {$alias}_posts.ID ";
if( !empty( $meta_key ) ){
$post_meta_subquery .= " AND {$alias}_postmeta.meta_key = '{$meta_key}'";
}
$post_meta_subquery .= " AND {$alias}_posts.post_type = '{$post_type}') AS {$alias}";
return $post_meta_subquery;
}
/**
* Prepare the query text for the WP based wpDataTable
*/
private function _buildWPJoinedQuery(){
// Build the final output
$query = "SELECT ";
$i = 0;
foreach($this->_select_arr as $table_alias=>$select_block){
$query .= implode( ",\n ", $select_block );
$i++;
if($i<count($this->_select_arr)){
$query .= ",\n ";
}
}
$query .= "\nFROM ";
$query .= implode( ', ', $this->_from_arr )."\n";
if(!empty($this->_inner_join_arr)){
$i = 0;
foreach($this->_inner_join_arr as $table_alias => $inner_join_block){
$query .= " INNER JOIN ".$inner_join_block."\n";
if( !empty( $this->_where_arr[$table_alias] ) ){
$query .= " ON ".implode( "\n AND ", $this->_where_arr[$table_alias] )."\n";
unset( $this->_where_arr[$table_alias] );
}
}
}
if(!empty($this->_left_join_arr)){
foreach($this->_left_join_arr as $table_alias => $left_join_block){
$query .= " LEFT JOIN ".$left_join_block."\n";
if( !empty( $this->_where_arr[$table_alias] ) ){
$query .= " ON ".implode( "\n AND ", $this->_where_arr[$table_alias] )."\n";
unset( $this->_where_arr[$table_alias] );
}
}
}
if(!empty($this->_where_arr)){
$query .= "WHERE 1=1 \n AND ";
$i = 0;
foreach($this->_where_arr as $table_alias => $where_block){
$query .= implode("\n AND ", $where_block);
$i++;
if($i<count($this->_where_arr)){
$query .= "\n AND ";
}
}
}
if( !empty( $this->_group_arr ) ){
$query .= "\nGROUP BY ". implode( ', ', $this->_group_arr );
}
return $query;
}
/**
* Prepares the structure of the JOIN rules for MySQL based tables
*/
private function _prepareMySQLJoinedQueryStructure(){
if( !isset( $this->_table_data['join_rules'] ) ){ return; }
foreach( $this->_table_data['join_rules'] as $join_rule ){
if( empty( $join_rule['initiator_column'] )
|| empty( $join_rule['connected_column'] ) ){
continue;
}
$connected_column_arr = explode( '.', $join_rule['connected_column'] );
if( in_array( $connected_column_arr[0], $this->_from_arr )
&& count( $this->_from_arr ) > 1 ){
if( $join_rule['type'] == 'left' ){
$this->_left_join_arr[$connected_column_arr[0]] = $connected_column_arr[0];
}else{
$this->_inner_join_arr[$connected_column_arr[0]] = $connected_column_arr[0];
}
unset( $this->_from_arr[array_search($connected_column_arr[0], $this->_from_arr)] );
}else{
if( $join_rule['type'] == 'left' ){
$this->_left_join_arr[$connected_column_arr[0]] = $connected_column_arr[0];
}else{
$this->_inner_join_arr[$connected_column_arr[0]] = $connected_column_arr[0];
}
}
$this->_where_arr[$connected_column_arr[0]][] = self::buildWhereCondition(
$join_rule['initiator_table'].'.'.$join_rule['initiator_column'],
'eq',
$join_rule['connected_column'],
false
);
}
}
/**
* Prepares the query text for MySQL based table
*/
private function _buildMySQLQuery(){
// Build the final output
$query = "SELECT ";
$i = 0;
foreach($this->_select_arr as $table_alias=>$select_block){
$query .= implode( ",\n ", $select_block );
$i++;
if($i<count($this->_select_arr)){
$query .= ",\n ";
}
}
$query .= "\nFROM ";
$query .= implode( ', ', $this->_from_arr )."\n";
if(!empty($this->_inner_join_arr)){
$i = 0;
foreach($this->_inner_join_arr as $table_alias => $inner_join_block){
$query .= " INNER JOIN ".$inner_join_block."\n";
if( !empty( $this->_where_arr[$table_alias] ) ){
$query .= " ON ".implode( "\n AND ", $this->_where_arr[$table_alias] )."\n";
unset( $this->_where_arr[$table_alias] );
}
}
}
if(!empty($this->_left_join_arr)){
foreach($this->_left_join_arr as $table_alias => $left_join_block){
$query .= " LEFT JOIN ".$left_join_block."\n";
if( !empty( $this->_where_arr[$table_alias] ) ){
$query .= " ON ".implode( "\n AND ", $this->_where_arr[$table_alias] )."\n";
unset( $this->_where_arr[$table_alias] );
}
}
}
if(!empty($this->_where_arr)){
$query .= "WHERE 1=1 \n AND ";
$i = 0;
foreach($this->_where_arr as $table_alias => $where_block){
$query .= implode("\n AND ", $where_block);
$i++;
if($i<count($this->_where_arr)){
$query .= "\n AND ";
}
}
}
if( !empty( $this->_group_arr ) ){
$query .= "\nGROUP BY ". implode( ', ', $this->_group_arr );
}
return $query;
}
/**
* Prepares the Joined query structure for WP-based wpDataTables
*/
private function _prepareWPJoinedQueryStructure(){
global $wpdb;
if( !isset( $this->_table_data['join_rules'] ) ){ return; }
// Need to go through each post type and define the join rule
foreach( $this->_table_data['join_rules'] as $join_rule ){
if( empty( $join_rule['initiator_column'] )
|| empty( $join_rule['connected_column'] ) ){
continue;
}
$connected_column_arr = explode( '.',$join_rule['connected_column'] );
if( count( $connected_column_arr ) == 2 ){
// Joining by posts table column
$tbl_alias = self::prepareSqlAlias( 'posts_'.$connected_column_arr[0] );
if(!isset($this->_where_arr[$tbl_alias])){
$this->_where_arr[$tbl_alias] = array();
}
if( isset( $this->_from_arr[$tbl_alias] )
&& count( $this->_from_arr ) > 1 ){
if( $join_rule['type'] == 'left' ){
$this->_left_join_arr[$tbl_alias] = $this->_from_arr[$tbl_alias];
}else{
$this->_inner_join_arr[$tbl_alias] = $this->_from_arr[$tbl_alias];
}
unset( $this->_from_arr[$tbl_alias] );
}else{
if( $join_rule['type'] == 'left' ){
$this->_left_join_arr[$tbl_alias] = $wpdb->posts.' AS '.$tbl_alias;
}else{
$this->_inner_join_arr[$tbl_alias] = $wpdb->posts.' AS '.$tbl_alias;
}
$this->_where_arr[$tbl_alias][] = $tbl_alias.".post_type = '".$connected_column_arr[0]."'";
}
$this->_where_arr[$tbl_alias][] = self::buildWhereCondition(
str_replace( '-', '_','posts_'.$join_rule['connected_column'] ),
'eq',
self::prepareSqlAlias( 'posts_' . $join_rule['initiator_post_type'] )
. '.' . self::prepareSqlAlias( $join_rule['initiator_column'] ),
false
);
}else{
if($connected_column_arr[1] == 'meta'){
// joining by a meta value
$tbl_alias = self::prepareSqlAlias( $join_rule['connected_column'].'_tbl' );
if(!isset($this->_where_arr[$tbl_alias])){
$this->_where_arr[$tbl_alias] = array();
}
if( isset( $this->_from_arr[$tbl_alias] )
&& count( $this->_from_arr ) > 1 ){
if( $join_rule['type'] == 'left' ){
$this->_left_join_arr[$tbl_alias] = $this->_from_arr[$tbl_alias];
}else{
$this->_inner_join_arr[$tbl_alias] = $this->_from_arr[$tbl_alias];
}
unset( $this->_from_arr[$tbl_alias] );
}elseif( !isset( $this->_inner_join_arr[$tbl_alias] ) && !isset( $this->_left_join_arr[$tbl_alias] ) ){
$rule = self::preparePostMetaSubquery( $tbl_alias, $connected_column_arr[0], $connected_column_arr[2] );
$this->_where_arr[$tbl_alias] = array();
if( $join_rule['type'] == 'left' ){
$this->_left_join_arr[$tbl_alias] = $rule;
if( isset($this->_from_arr['posts_'.$connected_column_arr[0]]) && count($this->_from_arr) > 1 ){
$this->_left_join_arr['posts_'.$connected_column_arr[0]] = $this->_from_arr['posts_'.$connected_column_arr[0]];
unset($this->_from_arr['posts_'.$connected_column_arr[0]]);
}else{
$this->_left_join_arr['posts_'.$connected_column_arr[0]] = $wpdb->posts.'AS posts_'.$connected_column_arr[0];
}
}else{
$this->_inner_join_arr[$tbl_alias] = $rule;
if( isset($this->_from_arr['posts_'.$connected_column_arr[0]]) && count($this->_from_arr) > 1 ){
$this->_inner_join_arr['posts_'.$connected_column_arr[0]] = $this->_from_arr['posts_'.$connected_column_arr[0]];
unset($this->_from_arr['posts_'.$connected_column_arr[0]]);
}else{
$this->_inner_join_arr['posts_'.$connected_column_arr[0]] = $wpdb->posts.'AS posts_'.$connected_column_arr[0];
}
}
}
$this->_where_arr[$tbl_alias][] = self::buildWhereCondition(
$tbl_alias.'.meta_value',
'eq',
'posts_' . $join_rule['initiator_post_type']
. '.' . $join_rule['initiator_column'],
false
);
}elseif($connected_column_arr[1] == 'taxonomy'){
// joining by taxonomy
$tbl_alias = self::prepareSqlAlias( $join_rule['connected_column'].'_tbl' );
if(!isset($this->_where_arr[$tbl_alias])){
$this->_where_arr[$tbl_alias] = array();
}
if( isset( $this->_from_arr[$tbl_alias] )
&& count( $this->_from_arr ) > 1 ){
if( $join_rule['type'] == 'left' ){
$this->_left_join_arr[$tbl_alias] = $this->_from_arr[$tbl_alias];
}else{
$this->_inner_join_arr[$tbl_alias] = $this->_from_arr[$tbl_alias];
}
unset( $this->_from_arr[$tbl_alias] );
}elseif( !isset( $this->_inner_join_arr[$tbl_alias] ) && !isset( $this->_left_join_arr[$tbl_alias] ) ){
$rule = self::preparePostTaxonomySubquery( $tbl_alias, $connected_column_arr[2] );
$this->_where_arr[$tbl_alias] = array();
$this->_where_arr[$tbl_alias][] = self::buildWhereCondition(
$tbl_alias.'.name',
'eq',
$where_condition['value']
);
$this->_where_arr[$tbl_alias][] = self::buildWhereCondition(
$tbl_alias.'.id',
'eq',
'posts_'.$where_column_arr[0].'.ID',
false
);
if( $join_rule['type'] == 'left' ){
$this->_left_join_arr[$tbl_alias] = $rule;
if( isset($this->_from_arr['posts_'.$connected_column_arr[0]]) && count($this->_from_arr) > 1 ){
$this->_left_join_arr['posts_'.$connected_column_arr[0]] = $this->_from_arr['posts_'.$connected_column_arr[0]];
unset($this->_from_arr['posts_'.$connected_column_arr[0]]);
}else{
$this->_left_join_arr['posts_'.$connected_column_arr[0]] = $wpdb->posts.'AS posts_'.$connected_column_arr[0];
}
}else{
$this->_inner_join_arr[$tbl_alias] = $rule;
if( isset($this->_from_arr['posts_'.$connected_column_arr[0]]) && count($this->_from_arr) > 1 ){
$this->_inner_join_arr['posts_'.$connected_column_arr[0]] = $this->_from_arr['posts_'.$connected_column_arr[0]];
unset($this->_from_arr['posts_'.$connected_column_arr[0]]);
}else{
$this->_inner_join_arr['posts_'.$connected_column_arr[0]] = $wpdb->posts.'AS posts_'.$connected_column_arr[0];
}
}
}
$this->_where_arr[$tbl_alias][] = self::buildWhereCondition(
$tbl_alias.'.meta_value',
'eq',
'posts_' . $join_rule['initiator_post_type']
. '.' . $join_rule['initiator_column'],
false
);
}
}
}
}
/**
* Prepare a GROUP BY block for MySQL based wpDataTables
*/
private function _prepareMySQLGroupByBlock(){
if( !$this->_has_groups ){
return;
}
foreach( $this->_table_data['grouping_rules'] as $grouping_rule ){
if( empty( $grouping_rule ) ){ continue; }
$this->_group_arr[] = $grouping_rule;
}
}
/**
* Prepare a GROUP BY block for WP based wpDataTables
*/
private function _prepareWPGroupByBlock(){
if( !$this->_has_groups ){
return;
}
foreach( $this->_table_data['grouping_rules'] AS $grouping_rule ){
if( empty( $grouping_rule ) ){ continue; }
$this->_group_arr[] = $this->_column_aliases[$grouping_rule];
}
}
public static function preparePostTaxonomySubquery( $alias, $taxonomy ){
global $wpdb;
if(empty($alias) || empty($taxonomy)){ return ''; }
$taxonomy_subquery = "(SELECT name, object_id as id";
$taxonomy_subquery .= " FROM {$wpdb->terms} AS {$alias}_terms";
$taxonomy_subquery .= " INNER JOIN {$wpdb->term_taxonomy} AS {$alias}_termtaxonomy";
$taxonomy_subquery .= " ON {$alias}_termtaxonomy.term_id = {$alias}_terms.term_id ";
$taxonomy_subquery .= " AND {$alias}_termtaxonomy.taxonomy = '{$taxonomy}'";
$taxonomy_subquery .= " INNER JOIN {$wpdb->term_relationships} AS rel_{$alias}";
$taxonomy_subquery .= " ON {$alias}_termtaxonomy.term_taxonomy_id = rel_{$alias}.term_taxonomy_id";
$taxonomy_subquery .= ") AS {$alias}";
return $taxonomy_subquery;
}
public function setQuery( $query ){
$this->_query = wpdatatables_sanitize_query( $query );
}
public function getQuery(){
return $this->_query;
}
public function getQueryPreview(){
if( get_option( 'wdtUseSeparateCon' ) ){
$sql = new PDTSql(WDT_MYSQL_HOST, WDT_MYSQL_DB, WDT_MYSQL_USER, WDT_MYSQL_PASSWORD);
$result = $sql->getAssoc( $this->_query.' LIMIT 5', array() );
}else{
global $wpdb;
$result = $wpdb->get_results( $this->_query.' LIMIT 5', ARRAY_A );
}
if( !empty( $result ) ){
ob_start();
include( WDT_TEMPLATE_PATH . 'constructor_preview.inc.php' );
$ret_val = ob_get_contents();
ob_end_clean();
}else{
$ret_val = __('No results found','wpdatatables');
}
return $ret_val;
}
/**
* Returns the ending of the thumbnail URL string
* @return type
*/
public static function getThumbSizeString(){
return '-' . get_option( 'thumbnail_size_w' ) . 'x' . get_option( 'thumbnail_size_h' ).'.';
}
/**
* Generates a wpDataTable based on WP data query
*/
public function generateWdtBasedOnQuery( $table_data ){
global $wpdb;
$table_data['query'] = wpdatatables_sanitize_query( $table_data['query'] );
$table_array = array(
'title' => '',
'table_type' => 'mysql',
'content' => '',
'filtering' => 1,
'filtering_form' => 0,
'sorting' => 1,
'fixed_layout' => 0,
'responsive' => 0,
'word_wrap' => 1,
'tools' => 1,
'display_length' => 10,
'fixed_columns' => 0,
'chart' => 'none',
'chart_title' => '',
'server_side' => 0,
'editable' => 0,
'editor_roles' => '',
'mysql_table_name' => '',
'hide_before_load' => 1
);
$table_array['content'] = $table_data['query'];
$res = wdt_try_generate_table( 'mysql', $table_array['content'] );
$wpdb->insert($wpdb->prefix .'wpdatatables', $table_array);
// get the newly generated table ID
$table_id = $wpdb->insert_id;
$res['table_id'] = $table_id;
// creating default columns for the new table
$res['columns'] = wdt_create_columns_from_table( $res['table'], $table_id );
do_action( 'wpdatatables_after_save_table', $table_id );
return $res;
}
/**
* Returns a list of tables in the chosen DB
* @return array
*/
public static function listMySQLTables(){
$tables = array();
if( get_option( 'wdtUseSeparateCon' ) ){
try{
$sql = new PDTSql(WDT_MYSQL_HOST, WDT_MYSQL_DB, WDT_MYSQL_USER, WDT_MYSQL_PASSWORD);
} catch (Exception $ex) {
return $tables;
}
$result = $sql->getArray( 'SHOW TABLES', array() );
if( empty( $result ) ){
return $tables;
}
}else{
global $wpdb;
$result = $wpdb->get_results( 'SHOW TABLES', ARRAY_N );
}
// Formatting the result to plain array
foreach( $result as $row ){
$tables[] = $row[0];
}
return $tables;
}
/**
* Return a list of columns for the selected tables
*/
public static function listMySQLColumns( $tables ){
$columns = array( 'all_columns' => array(), 'sorted_columns' => array() );
if( !empty( $tables ) ){
if( get_option( 'wdtUseSeparateCon' ) ){
try{
$sql = new PDTSql(WDT_MYSQL_HOST, WDT_MYSQL_DB, WDT_MYSQL_USER, WDT_MYSQL_PASSWORD);
} catch (Exception $ex) {
return $columns;
}
foreach( $tables as $table ){
$columns['sorted_columns'][$table] = array();
$columns_query = "SHOW COLUMNS FROM {$table}";
$table_columns = $sql->getAssoc( $columns_query );
foreach( $table_columns as $table_column ){
$columns['sorted_columns'][$table][] = "{$table_column['Field']}";
$columns['all_columns'][] = "{$table}.{$table_column['Field']}";
}
}
}else{
global $wpdb;
foreach( $tables as $table ){
$columns['sorted_columns'][$table] = array();
$table_columns = $wpdb->get_results( "SHOW COLUMNS FROM {$table};", ARRAY_A );
foreach( $table_columns as $table_column ){
$columns['sorted_columns'][$table][] = "{$table}.{$table_column['Field']}";
$columns['all_columns'][] = "{$table}.{$table_column['Field']}";
}
}
}
}
return $columns;
}
/**
* Generates a table based on the provided file and shows a preview
*/
public function previewFileTable( $table_data ){
if( !empty( $table_data['file'] ) ){
$xls_url = urldecode( $table_data['file'] );
if (strtoupper(substr(PHP_OS, 0, 3)) === 'WIN') {
$xls_url = str_replace( site_url(), str_replace('\\', '/', ABSPATH), $xls_url );
}else{
$xls_url = str_replace( site_url(), ABSPATH, $xls_url );
}
}else{
return array( 'result' => 'error', 'message' => __( 'Empty file', 'wpdatatables' ) );
}
if( strpos( strtolower($xls_url), 'https://docs.google.com/spreadsheets' ) !== false ){
// Preview from Google Spreadsheet
$namedDataArray = WDTTools::extractGoogleSpreadsheetArray( $xls_url );
if( !empty( $namedDataArray ) ){
$headingsArray = array_keys( $namedDataArray[0] );
$namedDataArray = array_slice( $namedDataArray, 0, 5 );
}else{
return array(
'result' => 'error',
'message' => __(
'Could not read Google spreadsheet, please check if the URL is correct and the spreadsheet is published to everyone',
'wpdatatables'
)
);
}
}else{
require_once(WDT_ROOT_PATH.'/lib/phpExcel/PHPExcel.php');
$objPHPExcel = new PHPExcel();
if(strpos(strtolower($xls_url), '.xlsx')){
$objReader = new PHPExcel_Reader_Excel2007();
$objReader->setReadDataOnly(true);
}elseif(strpos(strtolower($xls_url), '.xls')){
$objReader = new PHPExcel_Reader_Excel5();
$objReader->setReadDataOnly(true);
}elseif(strpos(strtolower($xls_url), '.ods')){
$objReader = new PHPExcel_Reader_OOCalc();
$objReader->setReadDataOnly(true);
}elseif(strpos(strtolower($xls_url), '.csv')){
$objReader = new PHPExcel_Reader_CSV();
}else{
return array( 'result' => 'error', 'message' => __( 'Could not read input file!', 'wpdatatables' ) );
}
$objPHPExcel = $objReader->load($xls_url);
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$highestRow = $highestRow > 5 ? 5 : $highestRow;
$highestColumn = $objWorksheet->getHighestColumn();
$headingsArray = $objWorksheet->rangeToArray('A1:'.$highestColumn.'1',null, true, true, true);
$headingsArray = $headingsArray[1];
$r = -1;
$namedDataArray = array();
for ($row = 2; $row <= $highestRow; ++$row) {
$dataRow = $objWorksheet->rangeToArray('A'.$row.':'.$highestColumn.$row,null, true, true, true);
if ((isset($dataRow[$row]['A'])) && ($dataRow[$row]['A'] > '')) {
++$r;
foreach($headingsArray as $dataColumnIndex => $dataColumnHeading) {
$namedDataArray[$r][$dataColumnHeading] = $dataRow[$row][$dataColumnIndex];
if(WDT_DETECT_DATES_IN_EXCEL){
$cellID = $dataColumnIndex.$row;
if(PHPExcel_Shared_Date::isDateTime($objPHPExcel->getActiveSheet()->getCell($cellID))){
$namedDataArray[$r][$dataColumnHeading] = PHPExcel_Shared_Date::ExcelToPHP($dataRow[$row][$dataColumnIndex]);
}
}
}
}
}
}
$columnTypeArray = WDTTools::detectColumnDataTypes( $namedDataArray, $headingsArray );
$possibleColumnTypes = WDTTools::getPossibleColumnTypes();
$ret_val = '';
if( !empty( $namedDataArray ) ){
ob_start();
include( WDT_TEMPLATE_PATH . 'constructor_file_preview.inc.php' );
$ret_val = ob_get_contents();
ob_end_clean();
}
return array( 'result' => 'success', 'message' => $ret_val );
}
/**
* Reads the data from file in the DB and generates a wpDataTable
*/
public function readFileData( $table_data ){
$columnTypes = array();
if( !empty( $table_data['file'] ) ){
$xls_url = urldecode( $table_data['file'] );
if (strtoupper(substr(PHP_OS, 0, 3)) === 'WIN') {
$xls_url = str_replace( site_url(), str_replace('\\', '/', ABSPATH), $xls_url );
}else{
$xls_url = str_replace( site_url(), ABSPATH, $xls_url );
}
}else{
return _( 'Empty file', 'wpdatatables' );
}
for( $i=0; $i < count($table_data['columns']); $i++ ){
if( $table_data['columns'][$i]['orig_header'] == '%%NEW_COLUMN%%' ){
$table_data['columns'][$i]['orig_header'] = 'column'.$i;
}
$columnTypes[$table_data['columns'][$i]['orig_header']] = $table_data['columns'][$i]['type'];
}
$this->_id = $this->generateManualTable( $table_data );
if ( strpos( strtolower( $xls_url ), 'https://docs.google.com/spreadsheets' ) !== false ) {
$table_type = 'google';
$namedDataArray = WDTTools::extractGoogleSpreadsheetArray( $xls_url );
$headingsArray = array_keys( $namedDataArray[0] );
$highestRow = count( $namedDataArray ) - 1;
} else {
$table_type = 'excel';
require_once(WDT_ROOT_PATH . '/lib/phpExcel/PHPExcel.php');
$objPHPExcel = new PHPExcel();
if (strpos(strtolower($xls_url), '.xlsx')) {
$objReader = new PHPExcel_Reader_Excel2007();
$objReader->setReadDataOnly(true);
} elseif (strpos(strtolower($xls_url), '.xls')) {
$objReader = new PHPExcel_Reader_Excel5();
$objReader->setReadDataOnly(true);
} elseif (strpos(strtolower($xls_url), '.ods')) {
$objReader = new PHPExcel_Reader_OOCalc();
$objReader->setReadDataOnly(true);
} elseif (strpos(strtolower($xls_url), '.csv')) {
$objReader = new PHPExcel_Reader_CSV();
} else {
return _('File format not supported!', 'wpdatatables');
}
$objPHPExcel = $objReader->load($xls_url);
$objWorksheet = $objPHPExcel->getActiveSheet();
$highestRow = $objWorksheet->getHighestRow();
$highestColumn = $objWorksheet->getHighestColumn();
$headingsArray = $objWorksheet->rangeToArray( 'A1:'.$highestColumn.'1',null, true, true, true );
$headingsArray = $headingsArray[1];
}
$r = -1;
$insertArray = array();
// Insert statement default beginning
$insert_statement_beginning = "INSERT INTO "
. $this->_name . " ("
. implode(
', ',
array_map(
function( $header ){
return "`{$header}`";
},
array_values( $this->_column_headers )
)
)
.") ";
$insert_blocks = array();
for ($row = 0; $row <= $highestRow; ++$row) {
if( ( $row <= 1 ) && ( $table_type == 'excel' ) ){ continue; }
// Set all cells in the row to their defaults
foreach( $table_data['columns'] as $column ){
$insertArray[ $this->_column_headers[ $column['orig_header'] ] ] = "'" . esc_sql( $column['default_value'] ) . "'";
}
if ( $table_type == 'google' ) {
foreach( $headingsArray as $dataColumnIndex => $dataColumnHeading ) {
if( !in_array( $dataColumnHeading, array_keys( $this->_column_headers ) ) ) {
continue;
}
if( $columnTypes[$dataColumnHeading] != 'date' ) {
$insertArray[$this->_column_headers[$dataColumnHeading]] = "'" . esc_sql($namedDataArray[$row][$dataColumnHeading]) . "'";
} else {
$date = strtotime(str_replace('/', '-', $namedDataArray[$row][$dataColumnHeading]));
$insertArray[$this->_column_headers[$dataColumnHeading]] = "'" . date('Y-m-d', $date) . "'";
}
}
} else {
$dataRow = $objWorksheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, null, true, true, true);
if ((isset($dataRow[$row]['A'])) && ($dataRow[$row]['A'] > '')) {
++$r;
foreach ($headingsArray as $dataColumnIndex => $dataColumnHeading) {
if (!in_array($dataColumnHeading, array_keys($this->_column_headers))) {
continue;
}
if ($columnTypes[$dataColumnHeading] != 'date') {
$insertArray[$this->_column_headers[$dataColumnHeading]] = "'" . esc_sql($dataRow[$row][$dataColumnIndex]) . "'";
} else {
if ($objReader instanceof PHPExcel_Reader_CSV) {
$date = strtotime(str_replace('/', '-', $dataRow[$row][$dataColumnIndex]));
} else {
$date = esc_sql(PHPExcel_Shared_Date::ExcelToPHP($dataRow[$row][$dataColumnIndex]));
}
$insertArray[$this->_column_headers[$dataColumnHeading]] = "'" . date('Y-m-d', $date) . "'";
}
}
}
}
$insert_blocks[] = '(' . implode( ', ', $insertArray ) . ')';
if( $row % 100 == 0 ){
$this->insertRowsChunk( $insert_statement_beginning, $insert_blocks );
$insert_blocks = array();
}
}
$this->insertRowsChunk( $insert_statement_beginning, $insert_blocks );
}
/**
* Inserts a blocks of rows read from file
*/
private function insertRowsChunk( $insert_statement_beginning, $insert_blocks ){
global $wpdb;
if( count( $insert_blocks ) > 0 ){
$insert_statement = $insert_statement_beginning . " VALUES " . implode( ', ', $insert_blocks );
if(get_option('wdtUseSeparateCon')){
// External DB
$this->_db->doQuery( $insert_statement, array() );
}else{
$wpdb->query( $insert_statement );
}
}
}
/**
* Delete a column from a manually generated table
*/
public static function deleteManualColumn( $table_id, $column_name ){
global $wpdb;
$table_data = wdt_get_table_by_id( $table_id );
$existing_columns = wdt_get_columns_by_table_id( $table_id );
$delete_column_id = 0;
$delete_column_index = 0;
foreach( $existing_columns as $existing_column ){
if( $existing_column->orig_header == $column_name ){
$delete_column_index = $existing_column->pos;
$delete_column_id = $existing_column->id;
break;
}
}
$drop_statement = "ALTER TABLE {$table_data['mysql_table_name']} DROP COLUMN {$column_name}";
// First delete the column from the MySQL table
if(get_option('wdtUseSeparateCon')){
// External DB
$Sql = new PDTSql(WDT_MYSQL_HOST, WDT_MYSQL_DB, WDT_MYSQL_USER, WDT_MYSQL_PASSWORD);
$Sql->doQuery( $drop_statement, array() );
}else{
$wpdb->query( $drop_statement );
}
if( $delete_column_id != 0 ){
// Delete the column from wp_wpdatatable_columns
$wpdb->delete(
$wpdb->prefix.'wpdatatables_columns',
array( 'id' => $delete_column_id )
);
// Update the order of other columns
$update_statement = "UPDATE ".$wpdb->prefix."wpdatatables_columns
SET pos = pos - 1
WHERE table_id = {$table_id}
AND pos >= ".(int) $delete_column_index;
$wpdb->query( $update_statement );
}
}
/**
* Add a new column to manually generated table
*/
public static function addNewManualColumn( $table_id, $column_data ){
global $wpdb;
$table_data = wdt_get_table_by_id( $table_id );
$existing_columns = wdt_get_columns_by_table_id( $table_id );
$existing_headers = array();
$column_index = 0;
foreach( $existing_columns as $existing_column ){
$existing_headers[] = $existing_column->orig_header;
if( $existing_column->orig_header == $column_data['insert_after'] ){
$column_index = $existing_column->pos + 1;
}
}
$new_column_mysql_name = self::generateMySQLColumnName( $column_data['name'], $existing_headers );
$columnProperties = self::defineColumnProperties( $new_column_mysql_name, $column_data['type'] );
// Add the column to MySQL table
$alter_table_statement = "ALTER TABLE {$table_data['mysql_table_name']}
ADD COLUMN {$columnProperties['create_block']} ";
if( $column_data['insert_after'] == '%%beginning%%' ){
$alter_table_statement .= " FIRST";
}else if( $column_data['insert_after'] != '%%end%%' ){
$alter_table_statement .= " AFTER `{$column_data['insert_after']}`";
}
// Call the create statement on WPDB or on external DB if it is defined
if(get_option('wdtUseSeparateCon')){
// External DB
$Sql = new PDTSql(WDT_MYSQL_HOST, WDT_MYSQL_DB, WDT_MYSQL_USER, WDT_MYSQL_PASSWORD);
$Sql->doQuery( $alter_table_statement, array() );
}else{
$wpdb->query( $alter_table_statement );
}
// Fill in with default value if requested
if( $column_data['fill_default'] == 1 ){
$update_fill_default = "UPDATE {$table_data['mysql_table_name']}
SET `{$new_column_mysql_name}` = '{$column_data['default_value']}'
WHERE 1";
if(get_option('wdtUseSeparateCon')){
// External DB
$this->_db->doQuery( $update_fill_default, array() );
}else{
$wpdb->query( $update_fill_default );
}
}
// Move the existing columns if necessary
if( $column_data['insert_after'] == '%%end%%' ){
$column_index = count ($existing_columns );
}else{
$update_statement = "UPDATE ".$wpdb->prefix."wpdatatables_columns
SET pos = pos + 1
WHERE table_id = {$table_id}
AND pos >= ".(int) $column_index;
$wpdb->query( $update_statement );
}
// Add the column to wp_wpdatatables_columns
$wpdb->insert(
$wpdb->prefix."wpdatatables_columns",
array(
'table_id' => $table_id,
'orig_header' => $new_column_mysql_name,
'display_header' => $column_data['name'],
'filter_type' => $columnProperties['filter_type'],
'column_type' => $columnProperties['column_type'],
'pos' => $column_index,
'possible_values' => str_replace( ',,;,|', '|', $column_data['possible_values'] ),
'default_value' => $column_data['default_value'],
'input_type' => $columnProperties['editor_type']
)
);
}
}
?>