<?php
/*
Copyright (C) Pimwick, LLC
This program is free software; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation; either version 2
of the License, or (at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program; if not, write to the Free Software
Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA.
*/
// Exit if accessed directly
if ( !defined( 'ABSPATH' ) ) {
exit;
}
if ( ! class_exists( 'PWBE_SQL_Builder' ) ) :
final class PWBE_SQL_Builder {
public function get_products( $post ) {
global $wpdb, $pw_bulk_edit;
$pw_bulk_edit->increase_memory_limit();
$wpdb->show_errors();
$common_fields = "
post.ID AS post_id,
post.post_content AS post_content,
post.post_excerpt AS post_excerpt,
post.post_status AS post_status,
post.menu_order AS menu_order,
parent.ID AS parent_post_id,
parent.post_title AS post_title,
meta__sku.meta_value AS _sku,
meta__regular_price.meta_value AS _regular_price,
meta__sale_price.meta_value AS _sale_price
";
$common_joins = "
LEFT JOIN
{$wpdb->postmeta} AS meta__sku ON (meta__sku.post_id = post.ID AND meta__sku.meta_key = '_sku')
LEFT JOIN
{$wpdb->postmeta} AS meta__regular_price ON (meta__regular_price.post_id = post.ID AND meta__regular_price.meta_key = '_regular_price')
LEFT JOIN
{$wpdb->postmeta} AS meta__sale_price ON (meta__sale_price.post_id = post.ID AND meta__sale_price.meta_key = '_sale_price')
";
if ( !empty( $post['order_by'] ) ) {
$column = PWBE_Columns::get_by_field( $post['order_by'] );
if ( !empty( $column ) ) {
if ( $column['field'] == '_visibility' ) {
$common_fields .= ",
visibility_exclude_from_catalog.product_id AS `visibility_exclude_from_catalog_product_id`,
visibility_exclude_from_search.product_id AS `visibility_exclude_from_search_product_id`
";
$common_joins = $this->maybe_add_catalog_visibility_joins( $common_joins );
} else if ( $column['table'] == 'meta' && !in_array( $column['field'], array( '_sku', '_regular_price', '_sale_price' ) ) ) {
if ( $column['field'] == '_featured' && PW_Bulk_Edit::wc_min_version( '3.0' ) ) {
$common_fields .= "
, CASE WHEN featured_products.product_id IS NOT NULL THEN 'yes' ELSE 'no' END AS `is_featured_product`
";
$common_joins .= "
LEFT JOIN (
SELECT
r.object_id AS product_id
FROM
{$wpdb->term_taxonomy} AS tax
JOIN
{$wpdb->terms} AS t ON (t.term_id = tax.term_id AND t.name = 'featured')
JOIN
{$wpdb->term_relationships} AS r ON (r.term_taxonomy_id = tax.term_taxonomy_id)
WHERE
tax.taxonomy = 'product_visibility'
) AS featured_products ON (featured_products.product_id = post.ID)
";
} else {
$common_fields .= '
, `meta_' . $column['field'] . '`.meta_value AS `' . $column['field'] . '`
';
$common_joins .= '
LEFT JOIN
' . $wpdb->postmeta . ' AS `meta_' . $column['field'] . '` ON (`meta_' . $column['field'] . '`.post_id = post.ID AND `meta_' . $column['field'] . '`.meta_key = \'' . $column['field'] . '\')
';
}
}
}
}
$common_where = $this->build_common_sql( '-0', $post['main_group_type'], $post, $common_fields, $common_joins );
$common_fields = apply_filters( 'pwbe_common_fields', $common_fields );
$common_joins = apply_filters( 'pwbe_common_joins', $common_joins );
$common_where = apply_filters( 'pwbe_common_where', $common_where );
@set_time_limit( 0 );
$wpdb->query("SET SQL_BIG_SELECTS=1");
$wpdb->query("DROP TABLE IF EXISTS pwbe_variations");
$wpdb->query("CREATE TEMPORARY TABLE pwbe_variations (post_id BIGINT(20) UNSIGNED, parent_post_id BIGINT(20) UNSIGNED, PRIMARY KEY (post_id) )");
$variations_sql = "
INSERT INTO pwbe_variations
SELECT
post.ID AS post_id,
MAX(parent.ID) AS parent_post_id
FROM
{$wpdb->posts} AS post
JOIN
{$wpdb->posts} AS parent ON (parent.ID = post.post_parent)
$common_joins
WHERE
post.post_type = 'product_variation'
AND ($common_where)
";
if ( PWBE_PREFILTER_VARIATIONS ) {
$variations_sql .= "
AND (
SELECT
terms.slug
FROM
{$wpdb->term_relationships} AS term_relationships
JOIN
{$wpdb->term_taxonomy} AS term_taxonomy ON (term_taxonomy.term_taxonomy_id = term_relationships.term_taxonomy_id)
JOIN
{$wpdb->terms} AS terms ON (terms.term_id = term_taxonomy.term_id)
WHERE
term_relationships.object_id = post.post_parent
AND term_taxonomy.taxonomy = 'product_type'
LIMIT 1
) IN ( 'variable', 'variable-subscription' )
";
}
$variations_sql .= "
GROUP BY
post.ID
";
$wpdb->query( $variations_sql );
$wpdb->query("DROP TABLE IF EXISTS pwbe_products");
$wpdb->query("CREATE TEMPORARY TABLE pwbe_products (post_id BIGINT(20) UNSIGNED, PRIMARY KEY (post_id) )");
$wpdb->query("
INSERT INTO pwbe_products
SELECT
DISTINCT
post.ID AS post_id
FROM
{$wpdb->posts} AS post
JOIN
{$wpdb->posts} AS parent ON (parent.ID = post.ID)
$common_joins
WHERE
post.post_type = 'product'
AND (
($common_where)
OR post.ID IN (SELECT parent_post_id FROM pwbe_variations)
)
");
$products = PWBE_DB::query( "
SELECT
DISTINCT
(
SELECT
terms.slug
FROM
{$wpdb->term_relationships} AS term_relationships
JOIN
{$wpdb->term_taxonomy} AS term_taxonomy ON (term_taxonomy.term_taxonomy_id = term_relationships.term_taxonomy_id)
JOIN
{$wpdb->terms} AS terms ON (terms.term_id = term_taxonomy.term_id)
WHERE
term_relationships.object_id = post.ID
AND term_taxonomy.taxonomy = 'product_type'
LIMIT 1
) AS product_type,
$common_fields
FROM
pwbe_products
JOIN
{$wpdb->posts} AS post ON (post.ID = pwbe_products.post_id)
JOIN
{$wpdb->posts} AS parent ON (parent.ID = post.ID)
$common_joins
WHERE
post.post_type = 'product'
UNION ALL
SELECT
DISTINCT
'variation' AS product_type,
$common_fields
FROM
pwbe_variations
JOIN
{$wpdb->posts} AS post ON (post.ID = pwbe_variations.post_id)
JOIN
{$wpdb->posts} AS parent ON (parent.ID = post.post_parent)
$common_joins
WHERE
post.post_type = 'product_variation'
AND (
SELECT
terms.slug
FROM
{$wpdb->term_relationships} AS term_relationships
JOIN
{$wpdb->term_taxonomy} AS term_taxonomy ON (term_taxonomy.term_taxonomy_id = term_relationships.term_taxonomy_id)
JOIN
{$wpdb->terms} AS terms ON (terms.term_id = term_taxonomy.term_id)
WHERE
term_relationships.object_id = post.post_parent
AND term_taxonomy.taxonomy = 'product_type'
LIMIT 1
) = 'variable'
ORDER BY
" . $this->build_order_by( $post ) . "
" );
if ( $products !== false ) {
return $products;
} else {
return 'MySQL Error: ' . PWBE_DB::error();
}
}
public function build_common_sql( $suffix, $group_type, $fields, &$sql_fields, &$sql_joins ) {
global $wpdb;
$sql_where = "(";
// Inside each group, loop through the nested statements.
for ($row_index = 0; $row_index < count( $fields['row'] ); $row_index++ ) {
$field_name = '';
$filter_type = '';
$field_value = '';
$field_value2 = '';
if ( isset ( $fields[$row_index . 'filter_name' . $suffix] ) ) {
$field_name = $fields[$row_index . 'filter_name' . $suffix];
}
if ( isset ( $fields[$row_index . 'filter_type' . $suffix] ) ) {
$filter_type = $fields[$row_index . 'filter_type' . $suffix];
}
// Value is either filter_value or filter_select.
if ( isset( $fields[$row_index . 'filter_value' . $suffix] ) ) {
$field_value = $fields[$row_index . 'filter_value' . $suffix];
// Value2 is optional
if ( isset( $fields[$row_index . 'filter_value2' . $suffix] ) ) {
$field_value2 = $fields[$row_index . 'filter_value2' . $suffix];
}
} else if ( isset( $fields[$row_index . 'filter_select' . $suffix] ) ) {
$field_value = $fields[$row_index . 'filter_select' . $suffix];
}
switch ( $field_name ) {
case 'pwbe_and':
case 'pwbe_or':
$row_sql = $this->build_common_sql( "$suffix-$row_index", $field_name, $fields, $sql_fields, $sql_joins );
break;
case 'categories':
$row_sql = $this->taxonomy_search( 'product_cat', $filter_type, $field_value );
break;
case 'tags':
$row_sql = $this->taxonomy_search( 'product_tag', $filter_type, $field_value );
break;
case 'post_content':
$row_sql = $this->string_search( 'parent.post_content', $filter_type, $field_value );
break;
case 'post_title':
$row_sql = $this->string_search( 'parent.post_title', $filter_type, $field_value );
break;
case 'regular_price':
$row_sql = $this->numeric_search( 'meta__regular_price.meta_value', $filter_type, $field_value, $field_value2 );
break;
case 'sale_price':
$row_sql = $this->numeric_search( 'meta__sale_price.meta_value', $filter_type, $field_value, $field_value2 );
break;
case 'post_excerpt':
$row_sql = $this->string_search( 'parent.post_excerpt', $filter_type, $field_value );
break;
case 'sku':
$row_sql = $this->string_search( 'meta__sku.meta_value', $filter_type, $field_value );
break;
default:
if ( $filter_type == 'is checked' || $filter_type == 'is not checked' ) {
$row_sql = $this->boolean_search( "`{$field_name}`.meta_value", $filter_type, $field_value );
} else {
if ( PW_Bulk_Edit::starts_with( 'pa_', $field_name ) ) {
$row_sql = $this->attributes_search( $field_name, $filter_type, $field_value );
} else {
if ( $group_type == 'pwbe_and' ) {
$row_sql = ' 1 = 1 ';
} else if ( $group_type == 'pwbe_or' ) {
$row_sql = ' 1 != 1 ';
}
}
}
break;
}
$sql_where .= apply_filters( 'pwbe_where_clause', $row_sql, $field_name, $filter_type, $field_value, $field_value2, $group_type );
if ( $group_type == 'pwbe_and' ) {
$sql_where .= " AND ";
} else if ( $group_type == 'pwbe_or' ) {
$sql_where .= " OR ";
}
}
// Yank the trailing AND/OR.
$sql_where = substr($sql_where, 0, -5);
$sql_where .= ") ";
return $sql_where;
}
public function string_search( $field_name, $filter_type, $value ) {
global $wpdb;
switch( $filter_type ) {
case 'is':
return $wpdb->prepare("$field_name = %s", $value);
break;
case 'is not':
return $wpdb->prepare("$field_name != %s", $value);
break;
case 'contains':
return $wpdb->prepare("$field_name LIKE %s", '%' . str_replace( '_', '\_', $value ) . '%');
break;
case 'does not contain':
return $wpdb->prepare("$field_name NOT LIKE %s", '%' . str_replace( '_', '\_', $value ) . '%');
break;
case 'begins with':
return $wpdb->prepare("$field_name LIKE %s", str_replace( '_', '\_', $value ) . '%');
break;
case 'ends with':
return $wpdb->prepare("$field_name LIKE %s", '%' . str_replace( '_', '\_', $value ) );
break;
}
}
public function numeric_search( $field_name, $filter_type, $value, $value2 ) {
global $wpdb;
//$field_sql = "$field_name IS NOT NULL AND $field_name != '' AND CAST($field_name AS DECIMAL(12, 2))";
$field_sql = "$field_name IS NOT NULL AND $field_name != '' AND $field_name";
switch( $filter_type ) {
case 'is':
return $wpdb->prepare("$field_sql = %f", $value);
break;
case 'is not':
return $wpdb->prepare("$field_sql != %f", $value);
break;
case 'is greater than':
return $wpdb->prepare("$field_sql > %f", $value);
break;
case 'is less than':
return $wpdb->prepare("$field_sql < %f", $value);
break;
case 'is in the range':
return $wpdb->prepare("($field_sql >= %f AND CAST($field_name AS DECIMAL(12, 2)) <= %f)", $value, $value2);
break;
}
}
public function taxonomy_search( $taxonomy, $filter_type, $values ) {
global $wpdb;
$placeholders = implode( ', ', array_fill( 0, count( $values ), '%s' ) );
if ( !empty( $values ) ) {
array_unshift( $values, $taxonomy );
}
// Allow parent taxonomy matching to be disabled in case it hurts performance on specific sites.
$maybe_search_parents = '';
if ( PWBE_SEARCH_PARENT_CATEGORIES ) {
$maybe_search_parents = 'OR t.term_id = tax.parent';
}
switch( $filter_type ) {
case 'is any of':
case 'is none of':
$negator = ( $filter_type == 'is none of' ) ? 'NOT' : '';
return $wpdb->prepare("$negator EXISTS (SELECT 1 FROM {$wpdb->term_taxonomy} AS tax JOIN {$wpdb->term_relationships} AS r ON (r.term_taxonomy_id = tax.term_taxonomy_id) JOIN {$wpdb->terms} AS t ON (t.term_id = tax.term_id $maybe_search_parents) WHERE tax.taxonomy = %s AND r.object_id = parent.ID AND t.slug IN ($placeholders))", $values);
break;
case 'is all of':
array_push( $values, count($values) - 1 );
return $wpdb->prepare("(SELECT COUNT(*) FROM {$wpdb->term_taxonomy} AS tax JOIN {$wpdb->term_relationships} AS r ON (r.term_taxonomy_id = tax.term_taxonomy_id) JOIN {$wpdb->terms} AS t ON (t.term_id = tax.term_id $maybe_search_parents) WHERE tax.taxonomy = %s AND r.object_id = parent.ID AND t.slug IN ($placeholders)) = %d", $values );
break;
case 'is empty':
case 'is not empty':
$negator = ( $filter_type == 'is empty' ) ? 'NOT' : '';
return $wpdb->prepare("$negator EXISTS (SELECT 1 FROM {$wpdb->term_taxonomy} AS tax JOIN {$wpdb->term_relationships} AS r ON (r.term_taxonomy_id = tax.term_taxonomy_id) JOIN {$wpdb->terms} AS t ON (t.term_id = tax.term_id $maybe_search_parents) WHERE tax.taxonomy = %s AND r.object_id = parent.ID)", $taxonomy);
break;
}
}
public function boolean_search( $field_name, $filter_type, $value ) {
global $wpdb;
switch( $filter_type ) {
case 'is checked':
return "LOWER(TRIM($field_name)) IN ('yes', 'true')";
break;
default:
return "COALESCE(NULLIF(LOWER(TRIM($field_name)), ''), 'no') IN ('no', 'false')";
break;
}
}
public function attributes_search( $field_name, $filter_type, $values ) {
global $wpdb;
$slugs = implode( ', ', array_fill( 0, count( $values ), '%s' ) );
switch( $filter_type ) {
case 'is any of':
case 'is none of':
$negator = ( $filter_type == 'is none of' ) ? 'NOT' : '';
$simple_attribute = $wpdb->prepare("
post.post_type != 'product_variation' AND (
$negator EXISTS (
SELECT 1
FROM {$wpdb->term_relationships} AS r
JOIN {$wpdb->term_taxonomy} AS tax ON (tax.term_taxonomy_id = r.term_taxonomy_id)
JOIN {$wpdb->terms} AS t ON (t.term_id = tax.term_id)
WHERE r.object_id = post.ID AND t.slug IN ($slugs)
)
)
",
$values);
$variable_attribute = $this->variation_attributes_search( $field_name, $filter_type, $values );
return "($simple_attribute OR $variable_attribute)";
break;
case 'is all of':
$wpdb_values = $values;
$wpdb_values[] = $field_name;
$wpdb_values[] = count( $values );
$simple_attribute = $wpdb->prepare("
post.post_type != 'product_variation' AND (
SELECT COUNT(*)
FROM {$wpdb->term_relationships} AS r
JOIN {$wpdb->term_taxonomy} AS tax ON (tax.term_taxonomy_id = r.term_taxonomy_id)
JOIN {$wpdb->terms} AS t ON (t.term_id = tax.term_id)
WHERE r.object_id = post.ID AND t.slug IN ($slugs) AND tax.taxonomy = %s
) = %d
",
$wpdb_values );
$variable_attribute = $this->variation_attributes_search( $field_name, $filter_type, $values );
return "($simple_attribute OR $variable_attribute)";
break;
}
}
public function variation_attributes_search( $field_name, $filter_type, $values ) {
global $wpdb;
$slugs = implode( ', ', array_fill( 0, count( $values ), '%s' ) );
switch( $filter_type ) {
case 'is any of':
case 'is none of':
$negator = ( $filter_type == 'is none of' ) ? 'NOT' : '';
array_unshift( $values, 'attribute_' . $field_name );
return $wpdb->prepare("
post.post_type = 'product_variation' AND (
$negator EXISTS (
SELECT 1
FROM {$wpdb->postmeta} AS m
WHERE
m.post_id = post.ID
AND m.meta_key = %s
AND m.meta_value IN ($slugs)
)
)
",
$values);
break;
case 'is all of':
array_push( $values, count($values) );
array_unshift( $values, 'attribute_' . $field_name );
return $wpdb->prepare("
post.post_type = 'product_variation' AND (
SELECT COUNT(*)
FROM {$wpdb->postmeta} AS m
WHERE
m.post_id = post.ID
AND m.meta_key = %s
AND m.meta_value IN ($slugs)
) = %d
",
$values);
break;
case 'is empty':
case 'is not empty':
$negator = ( $filter_type == 'is empty' ) ? 'NOT' : '';
return $wpdb->prepare("
post.post_type = 'product_variation' AND $negator EXISTS (
SELECT 1
FROM {$wpdb->postmeta} AS m
WHERE
m.post_id = post.ID
AND m.meta_key = %s
)
",
$field_name);
break;
}
}
public function build_order_by( $post ) {
$order_by = 'post_title';
$direction = 'ASC';
if ( !empty( $post['order_by_desc'] ) ) {
$direction = 'DESC';
}
if ( !empty( $post['order_by'] ) ) {
$column = PWBE_Columns::get_by_field( $post['order_by'] );
if ( !empty( $column ) ) {
if ( $column['type'] == 'currency' ) {
$order_by = "LENGTH(`$column[field]`) $direction, `$column[field]` $direction";
} else {
if ( $column['field'] == '_featured' && PW_Bulk_Edit::wc_min_version( '3.0' ) ) {
$order_by = "is_featured_product $direction";
} else if ( $column['field'] == '_visibility' ) {
$order_by = "
CASE
WHEN (visibility_exclude_from_catalog_product_id IS NULL AND visibility_exclude_from_search_product_id IS NULL) THEN 1
WHEN (visibility_exclude_from_catalog_product_id IS NOT NULL AND visibility_exclude_from_search_product_id IS NOT NULL) THEN 2
WHEN (visibility_exclude_from_catalog_product_id IS NOT NULL AND visibility_exclude_from_search_product_id IS NULL) THEN 3
ELSE 4
END $direction
";
} else if ( $column['type'] == 'number' ) {
$order_by = "CAST( `$column[field]` AS DECIMAL(10,6) ) $direction";
} else {
$order_by = "`$column[field]` $direction";
}
}
}
}
$order_by .= ", COALESCE(NULLIF(parent_post_id, 0), post_id), CASE WHEN product_type = 'variation' THEN menu_order ELSE -1000 END, post_id";
return $order_by;
}
public function maybe_add_catalog_visibility_joins( $sql_joins ) {
global $wpdb;
if ( strpos( $sql_joins, 'visibility_exclude_from_catalog' ) === false ) {
$sql_joins .= "
LEFT JOIN (
SELECT
r.object_id AS product_id
FROM
{$wpdb->term_taxonomy} AS tax
JOIN
{$wpdb->terms} AS t ON (t.term_id = tax.term_id AND t.name = 'exclude-from-catalog')
JOIN
{$wpdb->term_relationships} AS r ON (r.term_taxonomy_id = tax.term_taxonomy_id)
WHERE
tax.taxonomy = 'product_visibility'
) AS visibility_exclude_from_catalog ON (visibility_exclude_from_catalog.product_id = post.ID)
LEFT JOIN (
SELECT
r.object_id AS product_id
FROM
{$wpdb->term_taxonomy} AS tax
JOIN
{$wpdb->terms} AS t ON (t.term_id = tax.term_id AND t.name = 'exclude-from-search')
JOIN
{$wpdb->term_relationships} AS r ON (r.term_taxonomy_id = tax.term_taxonomy_id)
WHERE
tax.taxonomy = 'product_visibility'
) AS visibility_exclude_from_search ON (visibility_exclude_from_search.product_id = post.ID)
";
}
return $sql_joins;
}
}
endif;
?>