public static function getIdsBadgesToValidate()
{
$ids = array();
$query = new DbQuery();
$query->select('b.`id_badge`');
$query->from('badge', 'b');
$query->join('
LEFT JOIN `' . _DB_PREFIX_ . 'condition_badge` cb ON cb.`id_badge` = b.`id_badge`
LEFT JOIN `' . _DB_PREFIX_ . 'condition` c ON c.`id_condition` = cb.`id_condition` AND c.`validated` = 1');
$query->where('b.validated = 0');
$query->groupBy('b.`id_badge`');
$query->having('count(*) = SUM(c.validated)');
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
foreach ($result as $badge) {
$ids[] = $badge['id_badge'];
}
return $ids;
}
public static function getIdsAdviceToUnvalidate()
{
$ids = array();
$query = new DbQuery();
$query->select('a.`id_advice`');
$query->from('advice', 'a');
$query->join('
LEFT JOIN `' . _DB_PREFIX_ . 'condition_advice` ca ON ca.`id_advice` = a.`id_advice` AND ca.`display` = 0
LEFT JOIN `' . _DB_PREFIX_ . 'condition` c ON c.`id_condition` = ca.`id_condition` AND c.`validated` = 1');
$query->where('a.validated = 1');
$query->groupBy('a.`id_advice`');
$query->having('count(*) = SUM(c.validated)');
$result = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS($query);
foreach ($result as $advice) {
$ids[] = $advice['id_advice'];
}
return $ids;
}
public function getAffectedProducts($products = false)
{
$conditions_group = $this->getConditions();
$query = new DbQuery();
$query->select('p.id_product');
$query->from('product', 'p');
$query->join(Shop::addSqlAssociation('product', 'p'));
$query->groupBy('p.id_product');
$attributes = false;
$categories = false;
$features = false;
$suppliers = false;
$where = '1';
if ($conditions_group) {
$where .= ' AND (';
foreach ($conditions_group as $id_condition_group => $condition_group) {
$fields = array('category' => array('name' => 'cp.id_category', 'values' => array()), 'manufacturer' => array('name' => 'p.id_manufacturer', 'values' => array()), 'supplier' => array('name' => 'pss.id_supplier', 'values' => array()), 'feature' => array('name' => 'fp.id_feature_value', 'values' => array()), 'attribute' => array('name' => 'pac.id_attribute', 'values' => array()));
foreach ($condition_group as $condition) {
if ($condition['type'] == 'category') {
$categories = true;
} elseif ($condition['type'] == 'feature') {
$features = true;
} elseif ($condition['type'] == 'attribute') {
$attributes = true;
} elseif ($condition['type'] == 'supplier') {
$suppliers = true;
}
$fields[$condition['type']]['values'][] = $condition['value'];
}
foreach ($fields as $field) {
if (!($n_conditions = count($field['values']))) {
continue;
}
$where .= $field['name'] . ' IN (' . implode(',', array_map('intval', $field['values'])) . ') AND ';
if ($n_conditions > 1) {
$query->having('COUNT(' . bqSQL($field['name']) . ') >=' . (int) $n_conditions);
}
}
$where = rtrim($where, ' AND ') . ') OR (';
}
$where = rtrim($where, 'OR (');
}
if ($products && count($products)) {
$where .= ' AND p.id_product IN (' . implode(', ', array_map('intval', $products)) . ')';
}
if ($attributes) {
$query->select('pa.id_product_attribute');
$query->leftJoin('product_attribute', 'pa', 'p.id_product = pa.id_product');
$query->join(Shop::addSqlAssociation('product_attribute', 'pa', false));
$query->leftJoin('product_attribute_combination', 'pac', 'pa.id_product_attribute = pac.id_product_attribute');
$query->groupBy('pa.id_product_attribute');
} else {
$query->select('NULL as id_product_attribute');
}
if ($features) {
$query->leftJoin('feature_product', 'fp', 'p.id_product = fp.id_product');
}
if ($categories) {
$query->leftJoin('category_product', 'cp', 'p.id_product = cp.id_product');
}
if ($suppliers) {
$query->leftJoin('product_supplier', 'pss', 'p.id_product = pss.id_product');
}
if ($where) {
$query->where($where);
}
return Db::getInstance()->executeS($query);
}
/**
* Add HAVING restriction on query using real SQL syntax
*
* @param string $sql
* @return Collection
*/
public function sqlHaving($sql)
{
$this->query->having($this->parseFields($sql));
return $this;
}