<?php
class Filter
{
private $db;
private $attributes;
private $filteredgoods;
private $idcat;
public function __construct($dbh, $idcat=1, $param=array())
{
$this->db=$dbh;
$idcat=intval($idcat);
if(!$idcat)
{
$idcat=1;
}
$this->idcat=$idcat;
//надо проверить $param на валидность
$this->attributes=$param;
}
public function runFilter()
{
if(!empty($this->attributes))
{
$this->filterGoods();
$this->filterByGoods();
}
else $this->filterByCategory();
}
private function filterByCategory()
{
$sth=$this->db->prepare("SELECT DISTINCT(`parameters`.`id`), `parameters`.`name` FROM `parameters` LEFT JOIN `attributes` ON `attributes`.`attribute`=`parameters`.`id` LEFT JOIN `goods` ON `goods`.`id`=`attributes`.`good_id` WHERE `goods`.`category`=:id");
$sth->bindParam(":id", $this->idcat);
$sth->execute();
$result=array();
while($row=$sth->fetch(PDO::FETCH_ASSOC))
{
$obj=new StdClass();
$obj->attr=$row['name'];
$obj->id=$row['id'];
$sthv=$this->db->prepare("SELECT DISTINCT(`attributes`.`value`) FROM `attributes` WHERE `attributes`.`attribute` = :row AND `good_id` IN (SELECT `id` FROM `goods` WHERE `category`=:id)");
$sthv->bindParam(":row", $row['id']);
$sthv->bindParam(":id", $this->idcat);
$sthv->execute();
$value=array();
while ($rowv = $sthv->fetch(PDO::FETCH_ASSOC))
{
$value[]=$rowv['value'];
}
$obj->value=$value;
$result[]=$obj;
}
$this->attributes=$result;
}
public function countOfGoods()
{
if(!empty($this->filteredgoods))
return count($this->filteredgoods);
else
{
$sth=$this->db->prepare("SELECT COUNT(`id`) FROM `goods` WHERE `category`=:id");
$sth->bindParam(":id", $this->idcat);
$sth->execute();
$row=$sth->fetch();
return $row[0];
}
}
private function filterGoods()
{
$str="SELECT t1.good_id FROM attributes t1 LEFT JOIN goods ON goods.id=t1.good_id";
if (count($this->attributes)>1)
for ($i=2; $i<=count($this->attributes);$i++)
$str = $str." left join attributes t$i ON t$i.good_id = t".($i-1).".good_id";
$first = true;
$id = 1;
foreach ($this->attributes as $key => $value)
{
$or = false;
foreach ($value as $key2=>$value2)
{
if ($key2>0)
$or=true;
if ($first)
{
$str = $str." WHERE goods.category='{$this->idcat}' AND ((t$id.attribute='$key' and t$id.`value`='$value2')";
$first=false;
}
else
{
if ($key2==0)
{
if ($or) $str = $str." OR ((t$id.attribute='$key' and t$id.`value`='$value2')";
else $str = $str." AND ((t$id.attribute='$key' and t$id.`value`='$value2')";
}
else
{
if ($or) $str = $str." OR (t$id.attribute='$key' and t$id.`value`='$value2')";
else $str = $str." AND (t$id.attribute='$key' and t$id.`value`='$value2')";
}
}
}
$str = $str.")";
$id++;
}
$sth=$this->db->prepare($str);
$sth->execute();
$res=array();
while($row=$sth->fetch())
{
$res[]=$row[0];
}
$this->filteredgoods=$res;
}
private function filterByGoods()
{
$in='(';
for($i=0; $i<count($this->filteredgoods); $i++)
$in=$in.$this->filteredgoods[$i].",";
$in=mb_substr($in,0,-1).")";
$where ="good_id IN $in";
$sth=$this->db->prepare("SELECT DISTINCT(`parameters`.`id`), `parameters`.`name` FROM `parameters` LEFT JOIN `attributes` ON `attributes`.`attribute`=`parameters`.`id` LEFT JOIN `goods` ON `goods`.`id`=`attributes`.`good_id` WHERE $where");
$sth->execute();
$result=array();
while($row=$sth->fetch(PDO::FETCH_ASSOC))
{
$obj=new StdClass();
$obj->attr=$row['name'];
$obj->id=$row['id'];
$sthv=$this->db->prepare("SELECT DISTINCT(`attributes`.`value`) FROM `attributes` WHERE `attributes`.`attribute` = :row AND $where");
$sthv->bindParam(":row", $row['id']);
$sthv->execute();
$value=array();
while ($rowv = $sthv->fetch(PDO::FETCH_ASSOC))
{
$value[]=$rowv['value'];
}
$obj->value=$value;
$result[]=$obj;
}
$this->attributes=$result;
}
private function filt($param)
{
$str="SELECT DISTINCT(good_id) FROM attributes WHERE ";
$attr='';
$val='';
foreach($param as $key=>$value)
{
$attr=$attr.' OR attribute='.$key;
foreach ($value as $key2=>$value2)
{
$val=$val.' OR value="'.$value2.'"';
}
}
$attr=mb_substr($attr,3);
$val=mb_substr($val,3);
$str=$str."(".$attr.") AND (".$val.")";
$sth=$this->db->prepare($str);
$sth->execute();
$res=array();
while($row=$sth->fetch())
{
$res[]=$row[0];
}
return $res;
}
public function getAttributes()
{
return $this->attributes;
}
public function getfilteredGoods()
{
return $this->filteredgoods;
}
}
?>