411 lines
15 KiB
PHP
411 lines
15 KiB
PHP
<?php
|
|
|
|
namespace Hura8\Components\Product\Model;
|
|
|
|
use Hura8\Interfaces\iEntityModel;
|
|
use Hura8\System\Config;
|
|
use Hura8\System\Model\aEntityBaseModel;
|
|
use Hura8\Interfaces\EntityType;
|
|
use Hura8\System\Security\DataClean;
|
|
use Hura8\System\Security\DataType;
|
|
|
|
|
|
class ProductModel extends aEntityBaseModel implements iEntityModel
|
|
{
|
|
|
|
static $url_module = "product";
|
|
static $url_view = "detail";
|
|
static $url_type = "product:detail";
|
|
|
|
protected $tb_product_per_category = 'tb_product_per_category';
|
|
protected $tb_collection_product = "tb_collection_product";
|
|
protected $tb_product_info = 'tb_product_info';
|
|
protected $tb_product_hot = "tb_product_hot";
|
|
|
|
protected $richtext_fields = [
|
|
//'special_offer',
|
|
];
|
|
|
|
public function __construct() {
|
|
parent::__construct(
|
|
EntityType::PRODUCT,
|
|
"",
|
|
new ProductSearchModel(),
|
|
$this->richtext_fields
|
|
);
|
|
}
|
|
|
|
|
|
protected function extendedFilterOptions() : array
|
|
{
|
|
return [
|
|
"price" => array('max' => 0, 'min'=> 0),
|
|
"brand" => array(), // array(1,2,3,)
|
|
"collection" => array(), // array(1,2,3,)
|
|
"supplier" => array(), // array(1,2,3,)
|
|
"rating" => array(), // array(1,2,3,)
|
|
"category" => array(), // array(1,2,3,)
|
|
"status" => array(), // array(1,2,3,)
|
|
"hotType" => array(),// array(saleoff | not | new)
|
|
"attribute" => array(), // array(1,2,3,)
|
|
"promotion" => "",
|
|
"other_filter" => array(), // array(in-stock, has-promotion etc...)
|
|
"spec_group_id" => array(), // array(1,2,3,)
|
|
];
|
|
}
|
|
|
|
|
|
public function getProductInfoBySKU(string $sku) {
|
|
$query = $this->db->runQuery(
|
|
"SELECT * FROM `".$this->tb_entity."` basic, `".$this->tb_product_info."` info
|
|
WHERE basic.`id` = info.`id` AND basic.sku = ?
|
|
LIMIT 1 ",
|
|
['d'], [$sku]
|
|
);
|
|
|
|
if( $item_info = $this->db->fetchAssoc($query)){
|
|
return $item_info;
|
|
}
|
|
|
|
return false;
|
|
}
|
|
|
|
|
|
public function getFullInfo($id) {
|
|
$query = $this->db->runQuery(
|
|
"SELECT * FROM `".$this->tb_entity."` basic, `".$this->tb_product_info."` info
|
|
WHERE basic.`id` = info.`id` AND basic.id = ?
|
|
LIMIT 1 ",
|
|
['d'], [$id]
|
|
);
|
|
|
|
if( $item_info = $this->db->fetchAssoc($query)){
|
|
return $item_info;
|
|
}
|
|
|
|
return null;
|
|
}
|
|
|
|
|
|
public function getProductCategoryList($pro_id) {
|
|
$query = $this->db->runQuery(
|
|
"SELECT `category_id` FROM ".$this->tb_product_per_category." WHERE `item_id` = ? ",
|
|
['d'], [ $pro_id ]
|
|
) ;
|
|
|
|
$pro_cat = [];
|
|
foreach ( $this->db->fetchAll($query) as $rs ) {
|
|
$pro_cat[] = $rs['category_id'];
|
|
}
|
|
|
|
return $pro_cat;
|
|
}
|
|
|
|
|
|
protected function _buildQueryConditionExtend(array $filter_condition) : ?array
|
|
{
|
|
$where_query = [];
|
|
$bind_types = [];
|
|
$bind_values = [];
|
|
|
|
//-------------------
|
|
|
|
//other filters
|
|
if( array_key_exists("other_filter", $filter_condition) && sizeof($filter_condition["other_filter"])) {
|
|
foreach ($filter_condition["other_filter"] as $_filter) {
|
|
switch ($_filter) {
|
|
case "in-stock";
|
|
$where_query[] = " AND quantity > 0 ";
|
|
break;
|
|
case "has-vat";
|
|
$where_query[] = " AND `has_vat` = 1 ";
|
|
break;
|
|
case "out-stock";
|
|
$where_query[] = " AND `quantity` = 0 ";
|
|
break;
|
|
case "has-market-price";
|
|
$where_query[] = " AND `market_price` > 0 ";
|
|
break;
|
|
case "no-price";
|
|
$where_query[] = " AND `price` = 0 ";
|
|
break;
|
|
case "no-warranty";
|
|
$where_query[] = " AND LENGTH(`warranty`) < 2 ";
|
|
break;
|
|
case "no-sku";
|
|
$where_query[] = " AND LENGTH(`sku`) < 2 ";
|
|
break;
|
|
case "has-config";
|
|
$where_query[] = " AND `config_count` > 0 ";
|
|
break;
|
|
case "no-image";
|
|
$where_query[] = " AND `image_count` = 0 ";
|
|
break;
|
|
case "no-category";
|
|
$where_query[] = " AND `category` = '0' ";
|
|
break;
|
|
case "display-off";
|
|
$where_query[] = " AND `status`=0 ";
|
|
break;
|
|
case "display-on";
|
|
$where_query[] = " AND `status`=1 ";
|
|
break;
|
|
case "has-promotion":
|
|
$where_query[] = " AND LENGTH(`special_offer`) < 5 ";
|
|
break;
|
|
//...add more
|
|
}
|
|
}
|
|
}
|
|
|
|
|
|
//- brand id or ids or brand_indexes
|
|
if( array_key_exists("brand", $filter_condition) && sizeof($filter_condition["brand"])) {
|
|
|
|
$condition = array();
|
|
foreach ($filter_condition["brand"] as $brand_id) {
|
|
if(!intval($brand_id)) continue;
|
|
$condition[] = " brand_id = ? ";
|
|
$bind_types[] = 'd';
|
|
$bind_values[] = $brand_id;
|
|
}
|
|
|
|
if(sizeof($condition)) {
|
|
$where_query[] = " AND ( ".join(" OR ", $condition)." )";
|
|
}
|
|
}
|
|
|
|
|
|
//- collection id or ids
|
|
if( array_key_exists("collection", $filter_condition) && sizeof($filter_condition["collection"])) {
|
|
$condition = array();
|
|
|
|
foreach ($filter_condition["collection"] as $_id) {
|
|
if(!intval($_id)) continue;
|
|
|
|
$condition[] = " `id` IN ( SELECT `product_id` FROM ".$this->tb_collection_product." WHERE `collection_id` = ? ) ";
|
|
$bind_types[] = 'd';
|
|
$bind_values[] = $_id;
|
|
}
|
|
|
|
$where_query[] = " AND ( ".join(" OR ", $condition)." )";
|
|
}
|
|
|
|
|
|
//- rating: 1-> 5
|
|
if( array_key_exists("rating", $filter_condition) && sizeof($filter_condition["rating"])) {
|
|
$condition = array();
|
|
foreach ($filter_condition["rating"] as $_id) {
|
|
$condition[] = " `rating` = ? ";
|
|
$bind_types[] = 'd';
|
|
$bind_values[] = $_id;
|
|
}
|
|
|
|
$where_query[] = " AND ( ".join(" OR ", $condition)." )";
|
|
}
|
|
|
|
|
|
//- category id or ids
|
|
if( array_key_exists("category", $filter_condition) && sizeof($filter_condition["category"])) {
|
|
|
|
$objProductCategoryModel = new ProductCategoryModel();
|
|
|
|
$condition = array();
|
|
foreach ($filter_condition["category"] as $cat_id) {
|
|
|
|
$cat_info = $objProductCategoryModel->getInfo($cat_id);
|
|
if(!$cat_info) continue;
|
|
|
|
if($cat_info["is_parent"]) {
|
|
$childListId = ($cat_info["child_ids"]) ?: '0';
|
|
$condition[] = " `category_id` IN (".$childListId .") ";
|
|
}else{
|
|
$condition[] = " `category_id` = ? ";
|
|
$bind_types[] = 'd';
|
|
$bind_values[] = $cat_id;
|
|
}
|
|
}
|
|
|
|
if(sizeof($condition)) {
|
|
$where_query[] = " AND `id` IN ( SELECT DISTINCT `item_id` FROM ".$this->tb_product_per_category." WHERE " . join(" OR ", $condition) . " )";
|
|
}
|
|
}
|
|
|
|
|
|
// spec_group_id
|
|
if( array_key_exists("spec_group_id", $filter_condition) && sizeof($filter_condition["spec_group_id"])) {
|
|
|
|
$condition = array();
|
|
foreach ($filter_condition["spec_group_id"] as $_id) {
|
|
if(!$_id) continue;
|
|
|
|
$condition[] = " `spec_group_id` = ? ";
|
|
$bind_types[] = 'd';
|
|
$bind_values[] = $_id;
|
|
}
|
|
|
|
$where_query[] = " AND ( ".join(" OR ", $condition )." ) ";
|
|
}
|
|
|
|
|
|
//- hotType: saleoff | not | new | or combination of types
|
|
if( array_key_exists("hotType", $filter_condition) && sizeof($filter_condition["hotType"])) {
|
|
$config_hottype = Config::getProductHotTypeList();
|
|
$condition = array();
|
|
foreach ($filter_condition["hotType"] as $_id) {
|
|
if(!array_key_exists($_id, $config_hottype)) continue;
|
|
|
|
$condition[] = " `hot_type` = ? ";
|
|
$bind_types[] = 's';
|
|
$bind_values[] = $_id;
|
|
}
|
|
|
|
if(sizeof($condition)) {
|
|
$where_query[] = " AND `id` IN (SELECT `pro_id` FROM ".$this->tb_product_hot." WHERE 1 AND ( ".join(" OR ", $condition)." ) ) ";
|
|
}
|
|
|
|
}
|
|
|
|
|
|
//- attribute values
|
|
/*
|
|
if( array_key_exists("attribute", $filter_condition) && sizeof($filter_condition["attribute"])) {
|
|
$filter_attr_value_list = $filter_condition["attribute"];
|
|
//filter = attr_value_1-attr_value_2-attr_value_3,
|
|
$query_attr_id = [];
|
|
$count_filter = 0;
|
|
|
|
foreach($filter_attr_value_list as $attr_id){
|
|
$attr_id = (int) $attr_id;
|
|
if($attr_id) {
|
|
$query_attr_id[] = $attr_id;
|
|
$count_filter ++;
|
|
}
|
|
}
|
|
|
|
$product_filter_id_match = array();
|
|
if(sizeof($query_attr_id)) {
|
|
$query = $this->db->runQuery("
|
|
SELECT DISTINCT pro_id , COUNT(*) as num_pro
|
|
FROM ".TB_PRODUCT_ATTRIBUTE."
|
|
WHERE attr_value_id IN (".join(',', $query_attr_id).")
|
|
GROUP BY pro_id
|
|
HAVING num_pro = ".$count_filter."
|
|
LIMIT 10000
|
|
");
|
|
foreach ( $this->db->fetchAll($query) as $rs ) {
|
|
$product_filter_id_match[] = $rs["pro_id"];
|
|
}
|
|
}
|
|
|
|
$where_query[] = (sizeof($product_filter_id_match)) ? " AND `id` IN (".join(', ', $product_filter_id_match).") " : " AND `id` = 0 " ;
|
|
|
|
//xay lai url de back
|
|
if(!$this->objCategoryProduct) $this->objCategoryProduct = new CategoryProduct();
|
|
|
|
foreach($filter_attr_value_list as $value_id ){
|
|
$att_name = $this->objCategoryProduct->atrValueName($value_id);
|
|
|
|
$filterPath["attribute"][] = array(
|
|
"id" => $value_id,
|
|
"name" => $att_name,
|
|
);
|
|
$filter_messages[] = [
|
|
'title' => $att_name,
|
|
'reset' => Url::buildUrl($this->_request_url, ['filter' => join(FILTER_VALUE_SEPARATOR, remove_item_from_array($filter_condition["attribute"], $value_id))] ),
|
|
];
|
|
}
|
|
|
|
}
|
|
|
|
*/
|
|
|
|
//- attribute values
|
|
if( array_key_exists("attribute", $filter_condition) && sizeof($filter_condition["attribute"])) {
|
|
|
|
$filter_attr_value_list = $filter_condition["attribute"];
|
|
|
|
$attr_values_per_attribute = $this->groupAttributeValuesByAttributeId($filter_attr_value_list);
|
|
|
|
$having_match_count = sizeof(array_keys($attr_values_per_attribute));
|
|
|
|
$attribute_info_list = $this->groupAttributeListInfo(array_keys($attr_values_per_attribute));
|
|
|
|
$attr_where_query = [];
|
|
foreach ($attr_values_per_attribute as $_att_id => $_att_value_list) {
|
|
// if same attribute : find products that match either ONE of these values (not match ALL values)
|
|
$_att_info = $attribute_info_list[$_att_id];
|
|
if (!$_att_info) continue;
|
|
|
|
// this attribute requires all products must have ALL selected values
|
|
if ($_att_info['value_match_all'] && sizeof($_att_value_list) > 1) {
|
|
|
|
$_product_id_match_all = $this->getProductMatchAllAttributeValueIds($_att_value_list);
|
|
|
|
if (sizeof($_product_id_match_all) > 0) {
|
|
$attr_where_query[] = " ( `id` IN (" . join(",", $_product_id_match_all) . ") ) ";
|
|
} else {
|
|
$attr_where_query[] = " ( `id` = -1 ) ";
|
|
}
|
|
|
|
} else {
|
|
$attr_where_query[] = " ( SELECT DISTINCT `pro_id` FROM " . TB_PRODUCT_ATTRIBUTE . " WHERE (" .
|
|
join(" OR ", array_map(function ($_item) { return " `attr_value_id` = '" . intval($_item['id']) . "' "; }, $_att_value_list))
|
|
. " ) ) ";
|
|
}
|
|
|
|
}
|
|
|
|
$product_filter_id_match = array();
|
|
|
|
if (sizeof($attr_where_query)) {
|
|
$query = $this->db->runQuery("
|
|
SELECT `pro_id` , COUNT(*) AS num_pro
|
|
FROM ( " . join(" UNION ALL ", $attr_where_query) . " ) AS tpm_tb
|
|
GROUP BY pro_id
|
|
HAVING num_pro = " . $having_match_count . "
|
|
LIMIT 10000
|
|
");
|
|
foreach ($this->db->fetchAll($query) as $rs) {
|
|
$product_filter_id_match[] = $rs["pro_id"];
|
|
}
|
|
}
|
|
|
|
$where_query[] = (sizeof($product_filter_id_match)) ? " AND " . TB_PRODUCT_LIGHT . ".`id` IN (" . join(', ', $product_filter_id_match) . ") " : " AND " . TB_PRODUCT_LIGHT . ".`id` = 0 ";
|
|
|
|
}
|
|
|
|
|
|
//- price range
|
|
if(
|
|
isset($filter_condition["price"])
|
|
&& sizeof($filter_condition["price"])
|
|
&& ($filter_condition["price"]['min'] > 0 || $filter_condition["price"]['max'] > 0)
|
|
) {
|
|
|
|
//limit by price range
|
|
$maxPrice = DataClean::makeInputSafe($filter_condition["price"]['max'], DataType::INTEGER);
|
|
$minPrice = DataClean::makeInputSafe($filter_condition["price"]['min'], DataType::INTEGER);
|
|
|
|
$price_range_query = '';
|
|
if($maxPrice > 0 && $minPrice > 0){
|
|
$price_range_query = " ( `price` BETWEEN '".$minPrice."' AND '".$maxPrice."' ) ";
|
|
|
|
}else if($maxPrice > 0){
|
|
$price_range_query = " `price` < '".$maxPrice."' ";
|
|
|
|
}else if($minPrice > 0){
|
|
$price_range_query = " `price` >='".$minPrice."' ";
|
|
}
|
|
|
|
$where_query[] = " AND ". $price_range_query;
|
|
}
|
|
|
|
// ------------
|
|
|
|
return array( join(" ", $where_query), $bind_types, $bind_values);
|
|
}
|
|
|
|
|
|
}
|