public function buildProductQuery($cat_id)
{
$user_first_name = \Config::get('webshoppack::user_table') . '.' . \Config::get('webshoppack::user_fields')['fname'];
$user_last_name = \Config::get('webshoppack::user_table') . '.' . \Config::get('webshoppack::user_fields')['lname'];
$this->applicable_cats_ids = array();
$search_product_tags_sql = "";
if (\Input::has('tag_search') && \Input::get('tag_search') != '') {
$tagsearch_list = $this->remExcludeValuesFromSearchTags(\Input::get('tag_search'));
$this->exclude_tags = $tagsearch_list;
if (!empty($this->exclude_tags)) {
$excludetags_count = COUNT($this->exclude_tags);
$search_product_tags_sql = ', SUM(';
foreach ($this->exclude_tags as $exclude_key => $exclude_val) {
$priority_val = $excludetags_count - $exclude_key;
$search_product_tags_sql .= "\n\t\t\t\t\t\tIF( product.product_name LIKE '%" . addslashes($exclude_val) . "%', " . $priority_val . "*5, 0 ) +\n\t\t\t\t\t\tIF( product.product_tags LIKE '%" . addslashes($exclude_val) . "%', " . $priority_val . "*3, 0 ) +\n\t\t\t\t\t\tIF( product.product_description LIKE '%" . addslashes($exclude_val) . "%', " . $priority_val . "*1, 0 ) + ";
}
$search_product_tags_sql .= '0 ) AS relevant_count';
}
}
$this->qry = Product::Select(\DB::raw("product.id, product.product_status, product.url_slug, product.product_user_id, product.product_sold, product.product_added_date,\n\t\t\t\t\t\t\t\t\t product.product_category_id, product.product_tags, product.is_free_product, product.total_views, product.product_discount_price, product.product_discount_fromdate,\n\t\t\t\t\t\t\t\t\t product.product_discount_todate, product.product_price, product.product_name, product.product_description, product.product_highlight_text, product.demo_url, product.product_code,\n\t\t\t\t\t\t\t\t\t product_category.parent_category_id, product.date_activated, NOW() as date_current, IF( ( DATE( NOW() ) BETWEEN product.product_discount_fromdate AND product.product_discount_todate), 1, 0 ) AS have_discount,\n\t\t\t\t\t\t\t\t\t product.product_price_currency, product.product_price_usd, product.product_discount_price_usd" . $search_product_tags_sql));
$this->qry->join(\Config::get('webshoppack::user_table'), 'product.product_user_id', '=', \Config::get('webshoppack::user_table') . '.' . \Config::get('webshoppack::user_id_field'));
$this->qry->join('product_category', 'product.product_category_id', '=', 'product_category.id');
$this->qry->LeftJoin('shop_details', 'product.product_user_id', '=', 'shop_details.user_id');
$this->qry->Where('product.product_status', '=', 'Ok');
if ($cat_id > 0) {
$search_category_array = array($cat_id);
}
if (\Input::has('cat_search') && \Input::get('cat_search') != '') {
$search_category_array = \Input::get('cat_search');
}
if (isset($search_category_array) && count($search_category_array) > 0) {
foreach ($search_category_array as $c_id) {
//select the applicable categories to which the items may belong ..
$sub_cat_ids = $this->getSubCategoryIdsForProduct($c_id);
$this->applicable_cats_ids = array_unique(array_merge($this->applicable_cats_ids, $sub_cat_ids));
}
if (count($this->applicable_cats_ids)) {
$this->qry->whereRaw(\DB::raw('product.product_category_id IN (\'' . implode('\',\'', $this->applicable_cats_ids) . '\')'));
}
}
if (\Input::has('author_search') && \Input::get('author_search') != '') {
$name_arr = explode(" ", \Input::get('author_search'));
if (count($name_arr) > 0) {
foreach ($name_arr as $names) {
$this->qry->whereRaw("(" . $user_first_name . " LIKE '%" . addslashes($names) . "%' OR " . $user_last_name . " LIKE '%" . addslashes($names) . "%')");
}
}
}
if (\Input::has('price_range_start') or \Input::has('price_range_end')) {
$start_price = \Input::get('price_range_start');
$end_price = \Input::get('price_range_end');
$start_price = is_numeric($start_price) ? $start_price : 0;
$end_price = is_numeric($end_price) ? $end_price : 0;
//$start_price = CUtil::convertBaseCurrencyToUSD($start_price, $user_currency);
//$end_price = CUtil::convertBaseCurrencyToUSD($end_price, $user_currency);
$condn_to_check_discount = '((DATE(NOW()) BETWEEN product.product_discount_fromdate AND product.product_discount_todate) AND product.product_discount_price)';
if ($start_price != '' and $end_price != '') {
$this->qry->whereRaw(\DB::raw('(IF(' . $condn_to_check_discount . ',' . '(product.product_discount_price_usd BETWEEN ' . $start_price . ' AND ' . $end_price . '),' . '(product.product_price_usd BETWEEN ' . $start_price . ' AND ' . $end_price . ')))' . ' AND product.is_free_product = \'No\''));
} elseif ($start_price and !$end_price) {
$this->qry->whereRaw(\DB::raw('(IF(' . $condn_to_check_discount . ',' . '(product.product_discount_price_usd >= ' . $start_price . '),' . '(product.product_price_usd >= ' . $start_price . ')))' . ' AND product.is_free_product = \'No\''));
} elseif (!$start_price and $end_price) {
$this->qry->whereRaw(\DB::raw('(IF(' . $condn_to_check_discount . ',' . '(product.product_discount_price_usd <= ' . $end_price . '),' . '(product.product_price_usd <= ' . $end_price . ')))' . ' AND product.is_free_product = \'No\''));
}
}
if (\Input::has('tag_search') && \Input::get('tag_search') != '') {
$tags_condition = '';
$tagsearch_list = $this->exclude_tags;
if (!empty($tagsearch_list) and COUNT($tagsearch_list) > 0) {
if (\Config::get('webshoppack::product_search_include_title')) {
foreach ($tagsearch_list as $tag_key => $tag_val) {
if ($tags_condition != "") {
$tags_condition .= " OR ";
}
$tags_condition .= "((product.product_tags LIKE '%" . addslashes($tag_val) . "%') OR (product.product_name LIKE '%" . addslashes($tag_val) . "%')\n\t\t\t\t\t\t\t\t\t\t\tOR (product.product_description LIKE '%" . addslashes($tag_val) . "%') )";
}
if ($tags_condition != '') {
$this->qry->whereRaw(\DB::raw("(" . $tags_condition . ")"));
}
}
}
}
$this->order_by_field = $this->order_by;
if ($this->order_by == 'id') {
$this->order_by_field = 'date_activated';
}
if ($this->order_by == 'product_sold') {
$this->qry->whereRaw("(product.is_free_product = 'No' AND product.product_price_usd != 0 AND product.product_price_usd != '' ) AND (product.product_sold > 0)");
}
if ($this->order_by == 'featured') {
$this->order_by_field = 'date_activated';
$this->qry->Where('product.is_featured_product', '=', 'Yes');
}
if ($this->order_by == 'is_free_product') {
$this->qry->whereRaw(" ( product.is_free_product = 'Yes' OR (product.product_price_usd = 0 OR product.product_price_usd = '' ) )");
}
$this->qry->groupBy('product.id');
$this->qry->orderBy($this->order_by_field, 'DESC');
return $this->qry;
}
