<?php

[0] Fix | Delete

/**

[1] Fix | Delete

* Created by PhpStorm.

[2] Fix | Delete

* User: Andrew

[3] Fix | Delete

* Date: 26.07.2017

[4] Fix | Delete

* Time: 10:03

[5] Fix | Delete

*/

[6] Fix | Delete

namespace NetworkPosts\Components;

[7] Fix | Delete

class NetsPostsDBQuery {

[8] Fix | Delete

protected $wpdb;

[9] Fix | Delete

protected $wp_tables;

[10] Fix | Delete

protected $estore_tables;

[11] Fix | Delete

private function __construct( $db ) {

[12] Fix | Delete

$this->wpdb = $db;

[13] Fix | Delete

}

[14] Fix | Delete

public function init( $blog_id ) {

[15] Fix | Delete

if ( $blog_id == 1 ) {

[16] Fix | Delete

$this->wp_tables = array(

[17] Fix | Delete

'posts_table' => $this->wpdb->base_prefix . "posts",

[18] Fix | Delete

'term_relationship_table' => $this->wpdb->base_prefix . "term_relationships",

[19] Fix | Delete

'term_taxonomy_table' => $this->wpdb->base_prefix . "term_taxonomy",

[20] Fix | Delete

'term_table' => $this->wpdb->base_prefix . "terms",

[21] Fix | Delete

'post_meta' => $this->wpdb->base_prefix . 'postmeta'

[22] Fix | Delete

);

[23] Fix | Delete

$this->estore_tables = array(

[24] Fix | Delete

'product_table' => $this->wpdb->base_prefix . "wp_eStore_tbl",

[25] Fix | Delete

'category_table' => $this->wpdb->base_prefix . "wp_eStore_cat_tbl",

[26] Fix | Delete

'category_relationships' => $this->wpdb->base_prefix . "wp_eStore_cat_prod_rel_tbl"

[27] Fix | Delete

);

[28] Fix | Delete

} else {

[29] Fix | Delete

$this->wp_tables = array(

[30] Fix | Delete

'posts_table' => $this->wpdb->base_prefix . "{$blog_id}_posts",

[31] Fix | Delete

'term_relationship_table' => $this->wpdb->base_prefix . "{$blog_id}_term_relationships",

[32] Fix | Delete

'term_taxonomy_table' => $this->wpdb->base_prefix . "{$blog_id}_term_taxonomy",

[33] Fix | Delete

'term_table' => $this->wpdb->base_prefix . "{$blog_id}_terms",

[34] Fix | Delete

'post_meta' => $this->wpdb->base_prefix . "{$blog_id}_postmeta"

[35] Fix | Delete

);

[36] Fix | Delete

$this->estore_tables = array(

[37] Fix | Delete

'product_table' => $this->wpdb->base_prefix . "{$blog_id}_wp_eStore_tbl",

[38] Fix | Delete

'category_table' => $this->wpdb->base_prefix . "{$blog_id}_wp_eStore_cat_tbl",

[39] Fix | Delete

'category_relationships' => $this->wpdb->base_prefix . "{$blog_id}_wp_eStore_cat_prod_rel_tbl"

[40] Fix | Delete

);

[41] Fix | Delete

}

[42] Fix | Delete

}

[43] Fix | Delete

public function get_formatted_category_post_ids( $categories, $include_posts, $type = 'include' ) {

[44] Fix | Delete

$ids = $this->get_category_post_ids( $categories, $include_posts );

[45] Fix | Delete

$column_name = 'ID';

[46] Fix | Delete

if ( $type == 'include' ) {

[47] Fix | Delete

return format_inclusion( $this->wp_tables['posts_table'], $column_name, $ids );

[48] Fix | Delete

} else {

[49] Fix | Delete

return format_exclusion( $this->wp_tables['posts_table'], $column_name, $ids );

[50] Fix | Delete

}

[51] Fix | Delete

}

[52] Fix | Delete

public function get_category_post_ids( $categories, $include_posts = null ) {

[53] Fix | Delete

$result_ids = array();

[54] Fix | Delete

if ( is_array( $categories ) ) {

[55] Fix | Delete

$cat_arr = array();

[56] Fix | Delete

foreach ( $categories as $category ) {

[57] Fix | Delete

$cat_id = $this->wpdb->get_var( $this->wpdb->prepare( "SELECT term_id FROM {$this->wp_tables['term_table']} WHERE LOWER(slug) LIKE LOWER('%s') ", trim( $category ) ) );

[58] Fix | Delete

if ( $cat_id ) {

[59] Fix | Delete

$cat_arr[] = $cat_id;

[60] Fix | Delete

}

[61] Fix | Delete

}

[62] Fix | Delete

$cat_arr = array_unique( $cat_arr );

[63] Fix | Delete

$taxonomy_arr = array();

[64] Fix | Delete

foreach ( $cat_arr as $cat_id ) {

[65] Fix | Delete

$tax_id = $this->wpdb->get_var( $this->wpdb->prepare( "SELECT term_taxonomy_id FROM {$this->wp_tables['term_taxonomy_table']} WHERE term_id = %s", $cat_id ) );

[66] Fix | Delete

if ( $tax_id ) {

[67] Fix | Delete

$taxonomy_arr[] = $tax_id;

[68] Fix | Delete

}

[69] Fix | Delete

}

[70] Fix | Delete

$include = '';

[71] Fix | Delete

if ( $include_posts ) {

[72] Fix | Delete

$include = format_inclusion( $this->wp_tables['term_relationship_table'], 'object_id', $include_posts );

[73] Fix | Delete

$include = " AND $include";

[74] Fix | Delete

}

[75] Fix | Delete

foreach ( $taxonomy_arr as $tax_id ) {

[76] Fix | Delete

$post_ids = $this->wpdb->get_results( $this->wpdb->prepare( "SELECT object_id AS ID FROM {$this->wp_tables['term_relationship_table']} WHERE term_taxonomy_id = %d $include",

[77] Fix | Delete

$tax_id ), ARRAY_A );

[78] Fix | Delete

if ( $post_ids ) {

[79] Fix | Delete

$result_ids = array_merge_recursive( $result_ids, $post_ids );

[80] Fix | Delete

}

[81] Fix | Delete

}

[82] Fix | Delete

}

[83] Fix | Delete

return $result_ids;

[84] Fix | Delete

}

[85] Fix | Delete

public function get_post_categories( $post_id ) {

[86] Fix | Delete

$terms_table = $this->wp_tables['term_table'];

[87] Fix | Delete

$terms_relationship_table = $this->wp_tables['term_relationship_table'];

[88] Fix | Delete

$terms_taxonomy = $this->wp_tables['term_taxonomy_table'];

[89] Fix | Delete

return $this->wpdb->get_results( "SELECT $terms_table.name as cat_name, $terms_table.term_id as cat_id

[90] Fix | Delete

FROM $terms_table

[91] Fix | Delete

LEFT JOIN $terms_relationship_table ON $terms_relationship_table.term_taxonomy_id = $terms_table.term_id

[92] Fix | Delete

LEFT JOIN $terms_taxonomy ON $terms_taxonomy.term_id = $terms_table.term_id

[93] Fix | Delete

WHERE $terms_relationship_table.object_id = $post_id AND $terms_taxonomy.taxonomy = 'category'", ARRAY_A );

[94] Fix | Delete

}

[95] Fix | Delete

public function get_posts( $is_random, $paginate, $post_type_search, $ids, $old, $limit, array $meta_keys ) {

[96] Fix | Delete

$PostsTable = $this->wp_tables['posts_table'];

[97] Fix | Delete

$MetaTable = $this->wp_tables['post_meta'];

[98] Fix | Delete

$meta_full_condition = '';

[99] Fix | Delete

if(count( $meta_keys )){

[100] Fix | Delete

$meta_key_conditions = [];

[101] Fix | Delete

foreach ($meta_keys as $key){

[102] Fix | Delete

$meta_key_conditions[] = "$MetaTable.meta_key = '$key'";

[103] Fix | Delete

}

[104] Fix | Delete

$meta_full_condition = 'AND (' . join(' OR ', $meta_key_conditions) . ')';

[105] Fix | Delete

$join_query = " LEFT JOIN $MetaTable ON ($MetaTable.post_id = $PostsTable.ID)";

[106] Fix | Delete

$meta_columns = ", $MetaTable.meta_key, $MetaTable.meta_value ";

[107] Fix | Delete

}

[108] Fix | Delete

else{

[109] Fix | Delete

$join_query = "";

[110] Fix | Delete

$meta_columns = "";

[111] Fix | Delete

}

[112] Fix | Delete

if ( $is_random ) {

[113] Fix | Delete

if ( $paginate ) {

[114] Fix | Delete

return $this->wpdb->get_results( $this->wpdb->prepare(

[115] Fix | Delete

"SELECT $PostsTable.ID, $PostsTable.post_title, $PostsTable.post_excerpt, $PostsTable.post_content, $PostsTable.post_author, $PostsTable.post_date,

[116] Fix | Delete

$PostsTable.post_type $meta_columns

[117] Fix | Delete

FROM $PostsTable $join_query WHERE $PostsTable.post_status = %s $ids AND $post_type_search $meta_full_condition $old $limit", 'publish' ), ARRAY_A );

[118] Fix | Delete

} else {

[119] Fix | Delete

return $this->wpdb->get_results( $this->wpdb->prepare(

[120] Fix | Delete

"SELECT $PostsTable.ID, $PostsTable.post_title, $PostsTable.post_excerpt, $PostsTable.post_content, $PostsTable.post_author,

[121] Fix | Delete

$PostsTable.post_date, $PostsTable.post_type $meta_columns

[122] Fix | Delete

FROM $PostsTable $join_query WHERE $PostsTable.post_status = %s $ids AND $post_type_search $meta_full_condition $old ORDER BY RAND() $limit", 'publish' ), ARRAY_A );

[123] Fix | Delete

}

[124] Fix | Delete

} else {

[125] Fix | Delete

return $this->wpdb->get_results( $this->wpdb->prepare(

[126] Fix | Delete

"SELECT $PostsTable.ID, $PostsTable.post_title, $PostsTable.post_excerpt, $PostsTable.post_content, $PostsTable.post_author, $PostsTable.post_date,

[127] Fix | Delete

$PostsTable.post_type $meta_columns

[128] Fix | Delete

FROM $PostsTable $join_query WHERE $PostsTable.post_status = %s $ids AND $post_type_search $meta_full_condition $old $limit", 'publish' ), ARRAY_A );

[129] Fix | Delete

}

[130] Fix | Delete

}

[131] Fix | Delete

public function get_formatted_estore_category_post_ids( $categories, $type = 'include', $include_posts = null ) {

[132] Fix | Delete

$ids = $this->get_estore_category_post_ids( $categories, $include_posts );

[133] Fix | Delete

$column_name = 'id';

[134] Fix | Delete

if ( $type == 'include' ) {

[135] Fix | Delete

return format_inclusion( $this->estore_tables['product_table'], $column_name, $ids );

[136] Fix | Delete

} else {

[137] Fix | Delete

return format_exclusion( $this->estore_tables['product_table'], $column_name, $ids );

[138] Fix | Delete

}

[139] Fix | Delete

}

[140] Fix | Delete

public function get_estore_category_post_ids( $taxonomy, $include_posts = null ) {

[141] Fix | Delete

global $wpdb;

[142] Fix | Delete

if ( strpos( $taxonomy, ',' ) > 0 ) {

[143] Fix | Delete

$categories = explode( ',', $taxonomy );

[144] Fix | Delete

} else {

[145] Fix | Delete

$categories = [ $taxonomy ];

[146] Fix | Delete

}

[147] Fix | Delete

$estore_cat_array = array();

[148] Fix | Delete

$result_ids = array();

[149] Fix | Delete

foreach ( $categories as $category ) {

[150] Fix | Delete

$cat_id = $wpdb->get_var( $wpdb->prepare( "SELECT cat_id FROM {$this->estore_tables['category_table']} WHERE LOWER(cat_name) = LOWER('%s') ", trim( $category ) ) );

[151] Fix | Delete

if ( $cat_id ) {

[152] Fix | Delete

$estore_cat_array[] = $cat_id;

[153] Fix | Delete

}

[154] Fix | Delete

}

[155] Fix | Delete

$include = '';

[156] Fix | Delete

if ( $include_posts ) {

[157] Fix | Delete

$include = format_inclusion( $this->estore_tables['category_relationships'], 'prod_id', $include_posts );

[158] Fix | Delete

$include = " AND $include";

[159] Fix | Delete

}

[160] Fix | Delete

foreach ( $estore_cat_array as $estore_category ) {

[161] Fix | Delete

$estore_post_ids = $wpdb->get_results( $wpdb->prepare( "SELECT prod_id FROM {$this->estore_tables['category_relationships']} WHERE cat_id = %s $include", $estore_category ), ARRAY_A );

[162] Fix | Delete

if ( ! empty( $estore_post_ids ) ) {

[163] Fix | Delete

$result_ids = array_merge_recursive( $result_ids, $estore_post_ids );

[164] Fix | Delete

}

[165] Fix | Delete

}

[166] Fix | Delete

return $result_ids;

[167] Fix | Delete

}

[168] Fix | Delete

public function get_estore_products( $is_random, $paginate, $formatted_ids = '', $limit = '' ) {

[169] Fix | Delete

if ( ! empty( $formatted_ids ) ) {

[170] Fix | Delete

$estore_ids = ' WHERE ' . $formatted_ids;

[171] Fix | Delete

}

[172] Fix | Delete

$EStoreTable = $this->estore_tables['product_table'];

[173] Fix | Delete

if ( $is_random ) {

[174] Fix | Delete

if ( $paginate ) {

[175] Fix | Delete

return $this->wpdb->get_results( "SELECT $EStoreTable.id AS ID,'estore' AS post_type, $EStoreTable.name AS post_title, $EStoreTable.price AS price,

[176] Fix | Delete

$EStoreTable.description AS post_content, $EStoreTable.author_id AS post_author,

[177] Fix | Delete

$EStoreTable.product_url AS guid, $EStoreTable.thumbnail_url

[178] Fix | Delete

FROM $EStoreTable $estore_ids $limit;", ARRAY_A );

[179] Fix | Delete

} else {

[180] Fix | Delete

return $this->wpdb->get_results( "SELECT $EStoreTable.id AS ID,'estore' AS post_type, $EStoreTable.name AS post_title, $EStoreTable.price AS price,

[181] Fix | Delete

$EStoreTable.description AS post_content, $EStoreTable.author_id AS post_author, $EStoreTable.product_url AS guid,

[182] Fix | Delete

$EStoreTable.thumbnail_url

[183] Fix | Delete

FROM $EStoreTable $estore_ids ORDER BY RAND() $limit;", ARRAY_A );

[184] Fix | Delete

}

[185] Fix | Delete

} else {

[186] Fix | Delete

return $this->wpdb->get_results( "SELECT $EStoreTable.id AS ID,'estore' AS post_type, $EStoreTable.name AS post_title, $EStoreTable.price AS price,

[187] Fix | Delete

$EStoreTable.description AS post_content, $EStoreTable.author_id AS post_author, $EStoreTable.product_url AS guid,

[188] Fix | Delete

$EStoreTable.thumbnail_url

[189] Fix | Delete

FROM $EStoreTable $estore_ids $limit;", ARRAY_A );

[190] Fix | Delete

}

[191] Fix | Delete

}

[192] Fix | Delete

public function is_estore_installed() {

[193] Fix | Delete

$result = $this->wpdb->get_results( "SHOW TABLES LIKE '" . $this->wpdb->base_prefix . "wp_estore_tbl'", ARRAY_A );

[194] Fix | Delete

return count( $result ) == 1;

[195] Fix | Delete

}

[196] Fix | Delete

public function is_woocommerce_installed() {

[197] Fix | Delete

$result = $this->wpdb->get_results( "SHOW TABLES LIKE '" . $this->wpdb->base_prefix . "woocommerce_termmeta'", ARRAY_A );

[198] Fix | Delete

return count( $result ) == 1;

[199] Fix | Delete

}

[200] Fix | Delete

public function create_orderby_query_path( $column ) {

[201] Fix | Delete

return " ORDER BY $column ";

[202] Fix | Delete

}

[203] Fix | Delete

public function create_by_date_query_path( $column, $days ) {

[204] Fix | Delete

return "$column >= DATE_SUB(CURRENT_DATE(), INTERVAL $days DAY)";

[205] Fix | Delete

}

[206] Fix | Delete

public function get_skipped_ids( $categories, $count ) {

[207] Fix | Delete

$excluded = array();

[208] Fix | Delete

if ( $count > 0 ) {

[209] Fix | Delete

foreach ( $categories as $category ) {

[210] Fix | Delete

$posts = $this->get_category_post_ids( array( $category ) );

[211] Fix | Delete

$posts_count = count( $posts );

[212] Fix | Delete

if ( $posts_count > $count ) {

[213] Fix | Delete

$temp = array_slice( $posts, count( $posts ) - $count );

[214] Fix | Delete

} else {

[215] Fix | Delete

$temp = $posts;

[216] Fix | Delete

}

[217] Fix | Delete

$excluded = array_merge( $excluded, $temp );

[218] Fix | Delete

}

[219] Fix | Delete

}

[220] Fix | Delete

return $excluded;

[221] Fix | Delete

}

[222] Fix | Delete

public function get_blogs( $is_random, $is_paginate, $include, $exclude ) {

[223] Fix | Delete

$BlogsTable = $this->wpdb->base_prefix . 'blogs';

[224] Fix | Delete

if ( $is_random ) {

[225] Fix | Delete

if ( $is_paginate ) {

[226] Fix | Delete

return $this->wpdb->get_col( $this->wpdb->prepare(

[227] Fix | Delete

"SELECT blog_id FROM $BlogsTable WHERE public = %d AND archived = %d AND mature = %d AND spam = %d AND deleted = %d $include $exclude ", 1, 0, 0, 0, 0

[228] Fix | Delete

) );

[229] Fix | Delete

} else {

[230] Fix | Delete

return $this->wpdb->get_col( $this->wpdb->prepare(

[231] Fix | Delete

"SELECT blog_id FROM $BlogsTable WHERE public = %d AND archived = %d AND mature = %d AND spam = %d AND deleted = %d $include $exclude ORDER BY RAND() ", 1, 0, 0, 0, 0

[232] Fix | Delete

) );

[233] Fix | Delete

}

[234] Fix | Delete

} else {

[235] Fix | Delete

return $this->wpdb->get_col( $this->wpdb->prepare(

[236] Fix | Delete

"SELECT blog_id FROM $BlogsTable WHERE public = %d AND archived = %d AND mature = %d AND spam = %d AND deleted = %d $include $exclude ", 1, 0, 0, 0, 0

[237] Fix | Delete

) );

[238] Fix | Delete

}

[239] Fix | Delete

}

[240] Fix | Delete

public static function new_instance( $db ) {

[241] Fix | Delete

return new NetsPostsDBQuery( $db );

[242] Fix | Delete

}

[243] Fix | Delete

}

[244] Fix | Delete