<?php

namespace CarDealerPress\Includes\Database;

use CarDealerPress\includes\helpers\helpers as helpers;
use CarDealerPress\Includes\Helpers\Option;

if ( ! defined( 'ABSPATH' ) ) {
	exit;
}
// TODO rename to Db_Request or Db_Query, requests is too general
class Requests {

	public static $admin_options;
	public static $theme_options;
	private static $wpdb;
	private static $blog;
	private $sql_query_prepare;
	private $sql_query_params_prepare;
	private $parameters;
	private $page;
	private $sql_search_string;

	public function __construct() {
		global $wpdb;
		self::$wpdb          = $wpdb;
		self::$theme_options = Option::get_cdp_theme_options();
		self::$admin_options = Option::get_cdp_admin_options();
		self::$blog          = get_current_blog_id();
	}

	public function cdp_data_dealer_insert( $data ) {
		$table  = CDP_DATA;
		$insert = array(
			'blog_id'    => self::$blog,
			'data_key'   => 'dealer',
			'dealer_id'  => $data['dealer_id'],
			'data_value' => $data['dealer_id'],
		);
		$format = array( '%d', '%s', '%d', '%d' );
		// @codingStandardsIgnoreStart
		self::$wpdb->insert( $table, $insert, $format );
		// @codingStandardsIgnoreEnd
	}

	public function cdp_data_db_update( $data ) {
		$table        = CDP_DATA;
		$update       = array( 'data_value' => $data['value'] );
		$where        = array(
			'blog_id'  => self::$blog,
			'data_key' => $data['key'],
		);
		$format       = array( '%s' );
		$where_format = array( '%d', '%s' );
		// @codingStandardsIgnoreStart
		self::$wpdb->update( $table, $update, $where, $format, $where_format );
		// @codingStandardsIgnoreEnd
	}

	public function cdp_db_init( $key, $params = array() ) {
		$this->parameters = $params;
		$this->page       = $params['page'] ?? 1;
		$this->cdp_scrub_params();
		$function = 'cdp_' . $key . '_request';

		return $this->$function();
	}

	public function cdp_disclaimer_request() {
		$this->sql_query_params_prepare = array( self::$blog, 'disclaimer' );
		$this->sql_query_prepare        = 'SELECT data_value FROM ' . CDP_DATA . ' WHERE blog_id = %d AND data_key = %s';
		$results                        = $this->cdp_sql_request_prepare();
		if ( ! empty( $results[0]['data_value'] ) ) {
			return $results[0]['data_value'];
		} else {
			return '';
		}
	}

	public function cdp_show_companies_table_columns() {
		$this->sql_query_prepare        = 'SHOW COLUMNS FROM ' . CDP_COMPANIES;
		$results                        = $this->cdp_sql_request_prepare();
		return ! empty( $results ) ? $results : '';
	}

	public function cdp_cdp_companies_request() {
		$this->sql_query_params_prepare = array( self::$blog );
		$this->sql_query_prepare        = 'SELECT * FROM ' . CDP_COMPANIES . ' WHERE blog_id = %d';
		$results                        = $this->cdp_sql_request_prepare();
		return ! empty( $results ) ? $results : '';
	}

	public function cdp_show_grants() {
		$this->sql_query_params_prepare = array( self::$blog, 'disclaimer' );
		$this->sql_query_prepare        = 'SHOW GRANTS FOR CURRENT_USER;';
		$results                        = $this->cdp_sql_request_prepare();
		return ! empty( $results ) ? $results : '';
	}
	public function cdp_searchtotal_request() {
		/*Prepare patch on 10/27/2017*/
		$exclude = array( 'manual_sort' );
		$this->cdp_search_string_prepare( $exclude );
		$this->sql_query_prepare = 'SELECT count(*) Total FROM '. CDP_INVENTORY_TABLE . ( $this->sql_search_string ? $this->sql_search_string : '' );
		$results                 = $this->cdp_sql_request_prepare();

		return $results[0]['Total'];
	}

	public function cdp_saleclass_request() {
		/*Prepare patch on 10/27/2017*/
		$exclude = array( 'make', 'model', 'trim', 'manual_sort' );
		$this->cdp_search_string_prepare( $exclude );
		$this->sql_query_prepare = 'SELECT saleclass, count(*) Total FROM ' . CDP_INVENTORY_TABLE . ( $this->sql_search_string ? $this->sql_search_string : '' ) . ' GROUP BY saleclass ORDER BY saleclass';
		$results                 = $this->cdp_sql_request_prepare();

		return $results;
	}

	public function cdp_make_request() {
		/*Prepare patch on 10/27/2017*/
		$exclude = array( 'make', 'model', 'trim', 'manual_sort' );
		$this->cdp_search_string_prepare( $exclude );
		$this->sql_query_prepare = 'SELECT make, count(*) Total FROM ' . CDP_INVENTORY_TABLE . ( $this->sql_search_string ? $this->sql_search_string : '' ) . ' GROUP BY make ORDER BY make';
		$results                 = $this->cdp_sql_request_prepare();

		return $results;
	}

	public function cdp_model_request() {
		/*Prepare patch on 10/27/2017*/
		$exclude = array( 'model', 'trim', 'manual_sort' );
		$this->cdp_search_string_prepare( $exclude );
		$this->sql_query_prepare = 'SELECT model, count(*) Total FROM ' . CDP_INVENTORY_TABLE . ( $this->sql_search_string ? $this->sql_search_string : '' ) . ' GROUP BY model ORDER BY model';
		$results                 = $this->cdp_sql_request_prepare();

		return $results;
	}

	public function cdp_trim_request() {
		/*Prepare patch on 10/27/2017*/
		$exclude = array( 'trim', 'manual_sort' );
		$this->cdp_search_string_prepare( $exclude );
		$this->sql_query_prepare = 'SELECT trim, count(*) Total FROM ' . CDP_INVENTORY_TABLE . ( $this->sql_search_string ? $this->sql_search_string : '' ) . ' GROUP BY trim ORDER BY trim';
		$results                 = $this->cdp_sql_request_prepare();

		return $results;
	}

	public function cdp_year_request() {
		/*Prepare patch on 10/27/2017*/
		$exclude = array( 'year_to', 'year_from', 'manual_sort' );
		$this->cdp_search_string_prepare( $exclude );
		$this->sql_query_prepare = 'SELECT year, count(*) Total FROM ' . CDP_INVENTORY_TABLE . ( $this->sql_search_string ? $this->sql_search_string : '' ) . ' GROUP BY year ORDER BY year DESC';
		$results                 = $this->cdp_sql_request_prepare();

		return $results;
	}

	public function cdp_odometer_request() {
		/*Prepare patch on 10/27/2017*/
		$exclude = array( 'odometer_to', 'odometer_from', 'manual_sort' );
		$this->cdp_search_string_prepare( $exclude );
		//BETWEEN value1 AND value2
		$this->sql_query_prepare = 'SELECT odometer, count(*) Total FROM ' . CDP_INVENTORY_TABLE . ( $this->sql_search_string ? $this->sql_search_string : '' ) . ' GROUP BY odometer ORDER BY odometer DESC';
		$results                 = $this->cdp_sql_request_prepare();

		return $results;
	}

	public function cdp_price_request() {
		/*Prepare patch on 10/27/2017*/
		$exclude = array( 'price_to', 'price_from', 'manual_sort' );
		$this->cdp_search_string_prepare( $exclude );
		$this->sql_query_prepare = 'SELECT sortprice, count(*) Total FROM ' . CDP_INVENTORY_TABLE . ( $this->sql_search_string ? $this->sql_search_string : '' ) . ' GROUP BY sortprice ORDER BY sortprice DESC';
		$results                 = $this->cdp_sql_request_prepare();
		return $results;
	}

	public function cdp_vehicleclass_request() {
		/*Prepare patch on 10/27/2017*/
		$exclude = array(
			'vehicleclass',
			'manual_sort',
		);
		$this->cdp_search_string_prepare( $exclude );
		$this->sql_query_prepare = 'SELECT vehicleclass, count(*) Total FROM ' . CDP_INVENTORY_TABLE . ( $this->sql_search_string ? $this->sql_search_string : '' ) . ' GROUP BY vehicleclass ORDER BY vehicleclass';
		$results                 = $this->cdp_sql_request_prepare();
		return $results;
	}

	public function cdp_body_request() {
		/*Prepare patch on 10/27/2017*/
		$exclude = array( 'body', 'make', 'model', 'trim', 'manual_sort' );
		$this->cdp_search_string_prepare( $exclude );
		$this->sql_query_prepare = 'SELECT body, count(*) Total FROM ' . CDP_INVENTORY_TABLE . ( $this->sql_search_string ? $this->sql_search_string : '' ) . ' GROUP BY body ORDER BY body';
		$results                 = $this->cdp_sql_request_prepare();

		return $results;
	}

	public function cdp_transmission_request() {
		/*Prepare patch on 10/27/2017*/
		$exclude = array( 'transmission', 'manual_sort' );
		$this->cdp_search_string_prepare( $exclude );
		$this->sql_query_prepare = 'SELECT transmission, count(*) Total FROM ' . CDP_INVENTORY_TABLE . ( $this->sql_search_string ? $this->sql_search_string : '' ) . ' GROUP BY transmission ORDER BY transmission';
		$results                 = $this->cdp_sql_request_prepare();

		return $results;
	}

	public function cdp_drivetrain_request() {
		/*Prepare patch on 10/27/2017*/
		$exclude = array( 'drivetrain', 'manual_sort' );
		$this->cdp_search_string_prepare( $exclude );
		$this->sql_query_prepare = 'SELECT drivetrain, count(*) Total FROM ' . CDP_INVENTORY_TABLE . ( $this->sql_search_string ? $this->sql_search_string : '' ) . ' GROUP BY drivetrain ORDER BY drivetrain';
		$results                 = $this->cdp_sql_request_prepare();

		return $results;
	}

	public function cdp_fuel_type_request() {
		/*Prepare patch on 10/27/2017*/
		$exclude = array( 'fuel_type', 'manual_sort' );
		$this->cdp_search_string_prepare( $exclude );
		$this->sql_query_prepare = 'SELECT fuel_type, count(*) Total FROM ' . CDP_INVENTORY_TABLE . ( $this->sql_search_string ? $this->sql_search_string : '' ) . ' GROUP BY fuel_type ORDER BY fuel_type';
		$results                 = $this->cdp_sql_request_prepare();

		return $results;
	}

	public function cdp_highway_request() { // mpg
		$exclude = array( 'highway_to', 'highway_from', 'manual_sort' );
		$this->cdp_search_string_prepare( $exclude );
		$this->sql_query_prepare = 'SELECT highway, count(*) Total FROM ' . CDP_INVENTORY_TABLE . ( $this->sql_search_string ? $this->sql_search_string : '' ) . ' GROUP BY highway ORDER BY length(highway),highway';
		$results                 = $this->cdp_sql_request_prepare();

		return $results;
	}

	public function cdp_city_request() { // mpg
		$exclude = array( 'city_to', 'city_from', 'manual_sort' );
		$this->cdp_search_string_prepare( $exclude );
		$this->sql_query_prepare = 'SELECT city, count(*) Total FROM ' . CDP_INVENTORY_TABLE . ( $this->sql_search_string ? $this->sql_search_string : '' ) . ' GROUP BY city ORDER BY length(city),city';
		$results                 = $this->cdp_sql_request_prepare();

		return $results;
	}
	public function cdp_color_request() {
		/*Prepare patch on 10/27/2017*/
		$exclude = array( 'manual_sort', 'color' );
		$this->cdp_search_string_prepare( $exclude );
		$this->sql_query_prepare = 'SELECT color_map as color, count(*) Total FROM ' . CDP_INVENTORY_TABLE . ( $this->sql_search_string ? $this->sql_search_string : '' ) . ' GROUP BY color_map ORDER BY color_map';
		$results                 = $this->cdp_sql_request_prepare();

		return $results;
	}

	public function cdp_color_int_request() {
		/*Prepare patch on 10/27/2017*/
		$exclude = array( 'manual_sort', 'color_int' );
		$this->cdp_search_string_prepare( $exclude );
		$this->sql_query_prepare = 'SELECT color_map_int as color_int, count(*) Total FROM ' . CDP_INVENTORY_TABLE . ( $this->sql_search_string ? $this->sql_search_string : '' ) . ' GROUP BY color_map_int ORDER BY color_map_int';
		$results                 = $this->cdp_sql_request_prepare();

		return $results;
	}

	public function cdp_states_request() {
		/*Prepare patch on 10/27/2017*/
		$exclude = array(
			'vehicleclass',
			'geo_state',
			'geo_city',
			'dealer',
			'make',
			'model',
			'trim',
			'manual_sort',
			'search',
		);
		$this->cdp_search_string_prepare( $exclude );
		$this->sql_query_prepare = 'SELECT geo_state, count(*) Total FROM ' . CDP_INVENTORY_TABLE . ( $this->sql_search_string ? $this->sql_search_string : '' ) . ' GROUP BY geo_state ORDER BY geo_state';
		$results                 = $this->cdp_sql_request_prepare();

		return $results;
	}

	public function cdp_cities_request() {
		/*Prepare patch on 10/27/2017*/
		$exclude = array( 'vehicleclass', 'geo_city', 'dealer', 'make', 'model', 'trim', 'manual_sort' );
		$this->cdp_search_string_prepare( $exclude );
		$this->sql_query_prepare = 'SELECT geo_city, count(*) Total FROM ' . CDP_INVENTORY_TABLE . ( $this->sql_search_string ? $this->sql_search_string : '' ) . ' GROUP BY geo_city ORDER BY geo_city';
		$results                 = $this->cdp_sql_request_prepare();

		return $results;
	}

	public function cdp_zips_request() {
		/*Prepare patch on 10/27/2017*/
		$exclude = array( 'geo_zip', 'manual_sort' );
		$this->cdp_search_string_prepare( $exclude );
		$this->sql_query_prepare = 'SELECT geo_zip, count(*) Total FROM ' . CDP_INVENTORY_TABLE . ( $this->sql_search_string ? $this->sql_search_string : '' ) . ' GROUP BY geo_zip ORDER BY geo_zip';
		$results                 = $this->cdp_sql_request_prepare();

		return $results;
	}

	public function cdp_dealers_request() {
		/*Prepare patch on 10/27/2017*/
		$exclude = array( 'vehicleclass', 'dealer', 'make', 'model', 'trim', 'manual_sort' );
		$this->cdp_search_string_prepare( $exclude );
		$this->sql_query_prepare = 'SELECT dealer, count(*) Total FROM ' . CDP_INVENTORY_TABLE . ( $this->sql_search_string ? $this->sql_search_string : '' ) . ' GROUP BY dealer ORDER BY dealer';
		$results                 = $this->cdp_sql_request_prepare();

		return $results;
	}

	public function cdp_tags_request() {
		/*Prepare patch on 10/27/2017*/
		$results = array();
		$exclude = array( 'manual_sort' );
		$this->cdp_search_string_prepare( $exclude );
		$this->sql_query_prepare = 'SELECT tags FROM ' . CDP_INVENTORY_TABLE . ( $this->sql_search_string ? $this->sql_search_string : '' ) . ' GROUP BY tags ';
		$tags                    = $this->cdp_sql_request_prepare();
		if ( $tags ) {
			foreach ( $tags as $tag_string ) {
				$tag_array = explode( ',', $tag_string['tags'] );
				$tag_array = array_filter( $tag_array ); // remove empty strings
				foreach ( $tag_array as $tag ) {
					if ( ! in_array( $tag, $results, true ) ) {
						$results[] = $tag;
					}
				}
			}
		}
		sort( $results );

		return $results;
	}

	public function cdp_keywords_request() {
		/*Prepare patch on 10/27/2017*/
		$results = array();
		$exclude = array( 'manual_sort' );
		$this->cdp_search_string_prepare( $exclude );
		$this->sql_query_prepare = 'SELECT make,model,trim FROM ' . CDP_INVENTORY_TABLE . ( $this->sql_search_string ? $this->sql_search_string : '' ) . ' GROUP BY make ';
		$keyword_data            = $this->cdp_sql_request_prepare();

		$keyword_array = array();
		$item_array    = array( 'make', 'model', 'trim' );
		if ( ! empty( $keyword_data ) ) {
			foreach ( $item_array as $item ) {
				foreach ( $keyword_data as $data ) {
					if ( ! in_array( $data[ $item ], $keyword_array, true ) && ! empty( $data[ $item ] ) ) {
						$keyword_array[] = trim( $data[ $item ] );
					}
				}
			}
		}

		return $keyword_array;
	}

	public function cdp_company_request( $company_id = null ) {
		/*Prepare patch on 10/27/2017*/
		$company_id                     = $company_id ?? helpers::company_id();
		$results                        = array();
		$this->sql_query_prepare        = 'SELECT * FROM ' . CDP_COMPANIES . ' WHERE blog_id = %d AND company_id = %d';
		$this->sql_query_params_prepare = array( self::$blog, $company_id );
		$raw                            = $this->cdp_sql_request_prepare();
		foreach ( $raw as $key => $row ) {
			$new_row = array();
			foreach ( $row as $column => $value ) {
				$new_row[ $column ] = maybe_unserialize( $value );
			}
			$results[ $key ] = $new_row;
		}

		return $results;
	}

	public function cdp_company_dealer_request( $dealer = 0, $company_id = null ) {
		/*Prepare patch on 10/27/2017*/
		$company_id             = $company_id ?? helpers::company_id();
		$results                        = array();
		$this->sql_query_prepare        = 'SELECT * FROM ' . CDP_COMPANIES . ' WHERE blog_id = %d AND company_id = %d AND dealer_id = %d';
		$this->sql_query_params_prepare = array( self::$blog, $company_id, $dealer );
		$raw                            = $this->cdp_sql_request_prepare();
		foreach ( $raw as $key => $row ) {
			$new_row = array();
			foreach ( $row as $column => $value ) {
				$new_row[ $column ] = maybe_unserialize( $value );
			}
			$results[ $key ] = $new_row;
		}

		return $results;
	}

	public function cdp_inventory_update_check() {
		/*Prepare patch on 10/27/2017*/
		$this->sql_query_prepare        = 'SELECT * FROM ' . CDP_COMPANIES . ' WHERE blog_id = %d AND company_id = %d AND inventory_last_updated <= %d';
		$this->sql_query_params_prepare = array(
			self::$blog,
			helpers::company_id(),
			( current_time( 'timestamp', 0 ) - ( 3600 * 4 ) ),
		);
		$results                        = $this->cdp_sql_request_prepare();

		return ( empty( $results ) ? false : true );
	}

	public function cdp_inventory_request() {
		/*Prepare patch on 10/27/2017*/
		$exclude  = array( 'manual_sort' );
		$per_page = $this->parameters['per_page'] ?? self::$theme_options['theme']['overrides']['per_page'];
		if ( ! is_numeric( $this->page ) || $this->page <= 0 ) {
			$this->page = 1;
		}
		$limit = ' LIMIT ' . ( ( $this->page - 1 ) * $per_page ) . ',' . $per_page;
		$this->cdp_search_string_prepare( $exclude );
		$this->sql_query_prepare = 'SELECT * FROM ' . CDP_INVENTORY_TABLE . ( $this->sql_search_string ? $this->sql_search_string : '' ) . ' ORDER BY ' . $this->cdp_sort_filters() . '  ' . $limit;
		$results                 = $this->cdp_sql_request_prepare();

		return $results;
	}

	public function cdp_inventory_special_request($request = '', $param = '', $group_by = '', $order_by = '') {
		$group_by = empty($group_by) ? $request : $group_by;
		$order_by = empty($order_by) ? 'make,model':$order_by;
		$columns = $request ? $request : 'vin,saleclass,make,model,trim,year,vehicleclass,description,geo_state,geo_city,video,thumbnail,updated,vms_id';
		$sql_query  = 'SELECT ' . esc_sql($columns) . ' FROM ' . CDP_INVENTORY_TABLE . ' WHERE blog_id = %s AND company_id = %s';
		$query_params = array( self::$blog, helpers::company_id() );
		if ( ! empty( $param ) ) {
			$sql_query .= ' AND ' . $param;
		}
		if ( ! empty( $group_by ) ) {
			$sql_query .= ' GROUP BY ' . esc_sql($group_by);
		}
		if ( ! empty( $order_by ) ) {
			$sql_query .= ' ORDER BY ' . esc_sql($order_by);
		}
		$this->sql_query_prepare = $sql_query;
		$this->sql_query_params_prepare = $query_params;
		$results                        = $this->cdp_sql_request_prepare();
		return $results;
	}

	public function cdp_inventory_video_check() {
		/*Prepare patch on 10/27/2017*/
		$this->sql_query_prepare        = 'SELECT count(*) Total FROM ' . CDP_INVENTORY_TABLE . ' WHERE blog_id = %d AND company_id = %d AND video <> "" ';
		$this->sql_query_params_prepare = array( self::$blog, helpers::company_id() );
		$results                        = $this->cdp_sql_request_prepare();

		return $results;
	}

	public function cdp_sort_filters() {
		if ( isset( $this->parameters['manual_sort'] ) && '0' !== $this->parameters['manual_sort'] ) {
			$results = ' ';
			$type    = $this->parameters['manual_sort'];
		} else {
			$results = '(Sort > 0 ) desc, sort asc, ';
			$type    = self::$theme_options['theme']['inventory_display']['list']['sort']['default'];
		}

		switch ( $type ) {
			case 'makeModel':
				$results .= 'Make, Model';
				break;
			case 'makeModelD':
				$results .= 'Make Desc, Model';
				break;
			case 'createdA':
				$results .= 'Created';
				break;
			case 'createdD':
				$results .= 'Created Desc';
				break;
			case 'yearA':
				$results .= 'Year';
				break;
			case 'yearD':
				$results .= 'Year Desc';
				break;
			case 'priceA':
				$results .= 'Pricesort, Sortprice';
				break;
			case 'priceD':
				$results .= 'Pricesort, Sortprice Desc';
				break;
			case 'odometerA':
				$results .= 'Odometer';
				break;
			case 'odometerD':
				$results .= 'Odometer Desc';
				break;
		}

		return $results;
	}

	public function cdp_scrub_params() {
		$exclude = array( 'taxonomy', 'photo_view', 'page' );
		if ( $this->parameters ) {
			foreach ( $exclude as $item ) {
				if ( isset( $this->parameters[ $item ] ) ) {
					unset( $this->parameters[ $item ] );
				}
			}
		}
		// edge case
		if ( isset( $this->parameters['saleclass'] ) && 'all' === strtolower( $this->parameters['saleclass'] ) ) {
			unset( $this->parameters['saleclass'] );
		}
	}


	public function cdp_search_string_prepare( $exclude = array() ) {
		$this->sql_search_string        = ' WHERE company_id = %d AND blog_id = %d ';
		$this->sql_query_params_prepare = array( helpers::company_id(), self::$blog );

		$passable        = array(
			'dealer_id',
			'stock',
			'vin',
			'saleclass',
			'certified',
			'make',
			'model',
			'trim',
			'year_to',
			'year_from',
			'price_to',
			'price_from',
			'odometer_to',
			'odometer_from',
			'search',
			'tags',
			'body',
			'vehicleclass',
			'geo_state',
			'geo_city',
			'geo_zip',
			'dealer',
			'transmission',
			'drivetrain',
			'fuel_type',
			'model_code',
			'engine',
			'city_to',
			'city_from',
			'highway_to',
			'highway_from',
			'color',
			'color_int',
			'year',
			'vms_id',
		);
		$special         = array(
			'saleclass',
			'make',
			'tags',
			'price_to',
			'price_from',
			'odometer_to',
			'odometer_from',
			'year_to',
			'year_from',
			'certified',
			'search',
			'model_code',
			'engine',
			'city_to',
			'city_from',
			'highway_to',
			'highway_from',
			'color',
			'color_int',
			'year',
			'vin',
			'vms_id',
			'dealer',
		);
		$new_make_filter = self::$theme_options['theme']['overrides']['makes_new']['included'] ?? array();

		foreach ( $this->parameters as $key => $value ) {
			if ( empty( $value ) || ! trim( $value ) ) {
				unset( $this->parameters[ $key ] );
			}
		}

		if ( $this->parameters ) {
			foreach ( $this->parameters as $key => $param ) {
				if ( ( empty( $exclude ) || ! in_array( $key, $exclude, true ) ) && in_array( $key, $passable, true ) && ! empty( $param ) ) {
					//error_log('deb key ' . $key . ' param ' . $param);
					$this->sql_search_string .= ' AND ';
					if ( in_array( $key, $special, true ) ) {
						switch ( $key ) {
							case 'saleclass':
								if ( 'new' === strtolower( $param ) && ! empty( $new_make_filter ) ) {
									$search_placeholders = implode( ', ', array_fill( 0, count( $new_make_filter ), '%s' ) );
									// saleclass.
									$this->sql_search_string         .= $key . ' = %s';
									$this->sql_query_params_prepare[] = rawurldecode( $param );
									// new make check.
									$this->sql_search_string .= ' AND make in (' . $search_placeholders . ') ';
									foreach ( $new_make_filter as $id => $make ) {
										$this->sql_query_params_prepare[] = $make;
									}
								} else {
									$this->sql_search_string         .= $key . ' = %s';
									$this->sql_query_params_prepare[] = rawurldecode( $param );
								}
//error_log('debug sql - ' . print_r( $this->sql_query_params_prepare, true ));
								break;
							case 'make':
								if ( isset( $this->parameters['saleclass'] ) && 'new' === strtolower( $this->parameters['saleclass'] ) && ! empty( $new_make_filter ) ) {
									if ( in_array( $param, $new_make_filter, true ) ) {
										$this->sql_search_string         .= $key . ' = %s';
										$this->sql_query_params_prepare[] = rawurldecode( $param );
									} else {
										$this->sql_search_string         .= $key . ' = %s';
										$this->sql_query_params_prepare[] = '';
									}
								} else {
									$this->sql_search_string         .= $key . ' = %s';
									$this->sql_query_params_prepare[] = rawurldecode( $param );
								}
								break;
							case 'tags':
								$tags                     = explode( ',', rawurldecode( $param ) );
								$count                    = count( $tags );
								$i                        = 1;
								$search                   = '';
								$this->sql_search_string .= '(';
								foreach ( $tags as $tag ) {
									$more = ( $i !== $count ? ' AND ' : '' );
									$i ++;
									$this->sql_search_string         .= ' FIND_IN_SET(%s, tags) ' . $more;
									$this->sql_query_params_prepare[] = '' . $tag . '';
								}
								$this->sql_search_string .= ')';
								break;
							case 'price_to':
							case 'price_from':
								$this->sql_search_string         .= 'sortprice' . ( strpos( $key, '_to' ) !== false ? '<= %d' : '>= %d' );
								$this->sql_query_params_prepare[] = $param;
								break;
							case 'odometer_to':
							case 'odometer_from':
								$this->sql_search_string         .= 'odometer' . ( strpos( $key, '_to' ) !== false ? '<= %d' : '>= %d' );
								$this->sql_query_params_prepare[] = $param;
								break;
							case 'year':
								$this->sql_search_string         .= 'year = %d';
								$this->sql_query_params_prepare[] = $param;
								break;
							case 'year_to':
							case 'year_from':
								$this->sql_search_string         .= 'year' . ( strpos( $key, '_to' ) !== false ? '<= %d' : '>= %d' );
								$this->sql_query_params_prepare[] = $param;
								break;
							case 'certified':
								$this->sql_search_string         .= 'certified = %d';
								$this->sql_query_params_prepare[] = ( 'Yes' === $param ? '1' : '0' );
								break;
							case 'color':
								$this->sql_search_string         .= 'color_map = %s';
								$this->sql_query_params_prepare[] = $param;
								break;
							case 'color_int':
								$this->sql_search_string         .= 'color_map_int = %s';
								$this->sql_query_params_prepare[] = $param;
								break;
							case 'model_code':
								$this->sql_search_string         .= 'model_code like %s';
								$this->sql_query_params_prepare[] = '%' . $param . '%';
								break;
							case 'engine':
								$this->sql_search_string         .= 'engine like %s';
								$this->sql_query_params_prepare[] = '%' . $param . '%';
								break;
							case 'city_to':
							case 'city_from':
								$this->sql_search_string         .= 'city ' . ( strpos( $key, '_to' ) !== false ? '<= %d' : '>= %d' );
								$this->sql_query_params_prepare[] = $param;
								break;
							case 'highway_to':
							case 'highway_from':
								$this->sql_search_string         .= 'highway ' . ( strpos( $key, '_to' ) !== false ? '<= %d' : '>= %d' );
								$this->sql_query_params_prepare[] = $param;
								break;
							case 'dealer':
								$this->sql_search_string         .= $key . '= %s';
								$this->sql_query_params_prepare[] = sanitize_text_field(stripslashes($param));
								break;
							case 'search':
								$fields                   = array(
									'stock',
									'vin',
									'headline',
									'description',
									'make',
									'model',
									'trim',
									'exterior',
									'interior',
									'body',
									'vehicleclass',
									'tags',
									'engine',
									'transmission',
									'drivetrain',
									'extra',
								);
								$text_strings             = explode( ' ', trim( rawurldecode( $param ) ) );
								$count                    = count( $text_strings );
								$count_b                  = count( $fields );
								$i                        = 1;
								$result                   = $search = '';
								$this->sql_search_string .= '(';
								foreach ( $text_strings as $text_string ) {
									if ( ! empty( $text_string ) ) {
										$more = ( $i !== $count ? ' AND ' : '' );
										$i ++;
										$search                   = '';
										$i_b                      = 1;
										$this->sql_search_string .= '(';
										foreach ( $fields as $field ) {
											$more_b = ( $i_b !== $count_b ? ' OR ' : '' );
											$i_b ++;
											$this->sql_search_string         .= $field . ' like %s ' . $more_b;
											$this->sql_query_params_prepare[] = '%' . $text_string . '%';
										}
										// Numeric Year.
										if ( is_numeric( $text_string ) ) {
											$this->sql_search_string         .= ' OR year like %s ';
											$this->sql_query_params_prepare[] = '%' . $text_string . '%';
										}
										$this->sql_search_string .= ') ' . $more;
									} else {
										$i ++;
									}
								}
								$this->sql_search_string .= ')';
								break;
							case 'vin':
								$this->sql_search_string         .= 'vin like %s';
								$this->sql_query_params_prepare[] = '%' . $param . '%';
								break;
							case 'vms_id':
								$this->sql_search_string         .= 'vms_id = %d';
								$this->sql_query_params_prepare[] = $param;
								break;
						}
					} else {
						$this->sql_search_string         .= $key . '= %s';
						$this->sql_query_params_prepare[] = rawurldecode( $param );
					}
				}
			}
		}
	}

	public function cdp_sql_request() {

		if ( $this->sql_query ) {
			// @codingStandardsIgnoreStart
			return self::$wpdb->get_results( $this->sql_query, ARRAY_A );
			// @codingStandardsIgnoreEnd
		} else {
			return array();
		}
	}

	public function cdp_sql_request_prepare() {
		// @codingStandardsIgnoreStart
		$query = self::$wpdb->prepare( $this->sql_query_prepare, $this->sql_query_params_prepare );
		return self::$wpdb->get_results( $query, ARRAY_A );
		// @codingStandardsIgnoreEnd
	}

	public function cdp_sql_query() {

		if ( $this->sql_query ) {
			// @codingStandardsIgnoreStart
			return self::$wpdb->query( $this->sql_query );
			// @codingStandardsIgnoreEnd
		} else {
			return array();
		}
	}

	public function cdp_admin_get_custom_imports( $id = 0 ) {
		if ( $id ) {
			$this->sql_query = 'SELECT * FROM ' . CDP_DATA . ' WHERE blog_id=' . self::$blog . ' AND data_key = "import" AND dealer_id = ' . $id;
		} else {
			$this->sql_query = 'SELECT * FROM ' . CDP_DATA . ' WHERE blog_id=' . self::$blog . ' AND data_key = "import"';
		}
		$results = $this->cdp_sql_request();

		return $results;
	}

	public function cdp_admin_update_sort( $data ) {
		// Used by Sort Settings - no user input.
		// Reset sort to 0, if no custom sort set up.
		$query = 'UPDATE ' . CDP_INVENTORY_TABLE . ' SET Sort = 0';
		// @codingStandardsIgnoreStart
		self::$wpdb->query( $query );
		// @codingStandardsIgnoreEnd
		if ( $data ) {
			foreach ( $data as $key => $values ) {
				$query = 'UPDATE ' . CDP_INVENTORY_TABLE . ' SET Sort = ' . $values['sort_value'] . ' ';
				unset( $values['name'] );
				unset( $values['sort_value'] );
				$this->parameters = array_filter( $values );
				$query           .= $this->cdp_sort_string();
				// @codingStandardsIgnoreStart
				self::$wpdb->query( $query );
				// @codingStandardsIgnoreEnd
			}
		}
	}

	public function cdp_admin_sort_count( $params = array() ) {
		/*Used by Sort Settings - no user input*/
		$query = 'SELECT count(*) Counts from ' . CDP_INVENTORY_TABLE . ' ';
		unset( $params['name'] );
		unset( $params['sort_values'] );
		if ( $params ) {
			$this->parameters = $params;
			$query           .= $this->cdp_sort_string();
			$this->sql_query  = $query;
			$results          = $this->cdp_sql_request();

			return $results[0]['Counts'];
		} else {
			return 0;
		}
	}

	public function cdp_sort_string() {
		/*Used by Sort Settings - no user input*/
		$results = ' WHERE company_id = ' . helpers::company_id() . ' and blog_id=' . self::$blog . ' ';
		foreach ( $this->parameters as $key => $param ) {
			if ( 'saleclass' !== $key || 'all' !== $param ) {
				$results .= 'AND ';
				if ( 'saleclass' === $key && 'cert' === $param ) {
					$results .= 'saleclass="used" and certified=1 ';
				} elseif ( 'tags' === $key ) {
					$results .= $key . ' like "%' . $param . '%" ';
				} else {
					$results .= $key . '="' . $param . '" ';
				}
			}
		}

		return $results;
	}

	public function delete_manual_dealer( $dealer = 0 ) {
		if ( $dealer ) {
			// Removes cdp_data.
			$this->sql_query = 'DELETE FROM ' . CDP_DATA . ' WHERE blog_id = "' . self::$blog . '" AND data_key = "dealer" AND dealer_id = "' . $dealer . '"';
			$this->cdp_sql_query();
			// Removes CDP_COMPANIES.
			$this->sql_query = 'DELETE FROM ' . CDP_COMPANIES . ' WHERE blog_id = "' . self::$blog . '" AND dealer_id = "' . $dealer . '"';
			$this->cdp_sql_query();
			// Removes Inventory.
			$this->sql_query = 'DELETE FROM ' . CDP_INVENTORY_TABLE . ' WHERE blog_id = "' . self::$blog . '" AND dealer_id = "' . $dealer . '"';
			$this->cdp_sql_query();
		}
	}

	public function delete_custom_import( $data_id = 0 ) {
		if ( $data_id ) {
			$this->sql_query = 'DELETE FROM ' . CDP_DATA . ' WHERE data_id = ' . $data_id . ' AND data_key = "import"';
			$this->cdp_sql_query();
			set_transient( 'cdp_custom_import_update', 'Inventory imports need to be re-ran to reflect recent updates to custom imports.', 3600 * 4 );
		}
	}
}
