public function sCreateSql()
{
$sql_add_join = array();
$sql_add_select = array();
$sql_add_where = array();
$skipBackend = $this->shop->get('skipbackend');
$isoCode = $this->shop->get('isocode');
if (empty($skipBackend) && !empty($isoCode)) {
$sql_isocode = $this->db->quote($isoCode);
$sql_add_join[] = "\n LEFT JOIN s_core_translations as ta\n ON ta.objectkey=a.id AND ta.objecttype='article' AND ta.objectlanguage={$sql_isocode}\n\n LEFT JOIN s_core_translations as td\n ON td.objectkey=d.id AND td.objecttype='variant' AND td.objectlanguage={$sql_isocode}\n ";
$sql_add_select[] = "ta.objectdata as article_translation";
$sql_add_select[] = "td.objectdata as detail_translation";
//read the fallback for the case the translation is not going to be set
$fallbackId = $this->shop->getFallback() ? $this->shop->getFallback()->getId() : null;
if (!empty($fallbackId)) {
$sqlFallbackLanguageId = $this->db->quote($fallbackId);
$sql_add_join[] = "\n LEFT JOIN s_core_translations as taf\n ON taf.objectkey=a.id AND taf.objecttype='article' AND taf.objectlanguage={$sqlFallbackLanguageId}\n\n LEFT JOIN s_core_translations as tdf\n ON tdf.objectkey=d.id AND tdf.objecttype='variant' AND tdf.objectlanguage={$sqlFallbackLanguageId}\n ";
$sql_add_select[] = "taf.objectdata as article_translation_fallback";
$sql_add_select[] = "tdf.objectdata as detail_translation_fallback";
}
}
if (!empty($this->sSettings["categoryID"])) {
$sql_add_join[] = "\n INNER JOIN s_articles_categories_ro act\n ON act.articleID = a.id\n AND act.categoryID = {$this->sSettings["categoryID"]}\n INNER JOIN s_categories c\n ON c.id = act.categoryID\n AND c.active = 1\n ";
}
if (empty($this->sSettings["image_filter"])) {
$sql_add_join[] = "\n LEFT JOIN s_articles_img as i\n ON i.articleID = a.id AND i.main=1 AND i.article_detail_id IS NULL\n ";
} else {
$sql_add_join[] = "\n JOIN s_articles_img as i\n ON i.articleID = a.id AND i.main=1 AND i.article_detail_id IS NULL\n ";
}
if (!empty($this->sCustomergroup["groupkey"]) && empty($this->sCustomergroup["mode"]) && $this->sCustomergroup["groupkey"] != "EK") {
$sql_add_join[] = "\n LEFT JOIN s_articles_prices as p2\n ON p2.articledetailsID = d.id AND p2.`from`=1\n AND p2.pricegroup='{$this->sCustomergroup["groupkey"]}'\n AND p2.price!=0\n ";
$pricefield = "IFNULL(p2.price, p.price)";
$pseudoprice = "IFNULL(p2.pseudoprice, p.pseudoprice)";
$baseprice = "IFNULL(p2.baseprice, p.baseprice)";
} else {
$pricefield = "p.price";
$pseudoprice = "p.pseudoprice";
$baseprice = "p.baseprice";
}
if (empty($this->sSettings["variant_export"]) || $this->sSettings["variant_export"] == 1) {
$sql_add_select[] = "IF(COUNT(d.ordernumber) <= 1, '', GROUP_CONCAT(DISTINCT(CONCAT('\"', d.id, ':', REPLACE(d.ordernumber,'\"','\"\"'),'\"')) SEPARATOR ';')) as group_ordernumber";
$sql_add_select[] = "IF(COUNT(d.additionaltext) <= 1, '', GROUP_CONCAT(DISTINCT(CONCAT('\"', d.id, ':', REPLACE(d.additionaltext,'\"','\"\"'),'\"')) SEPARATOR ';')) as group_additionaltext";
$sql_add_select[] = "IF(COUNT({$pricefield})<=1,'',GROUP_CONCAT(ROUND(CAST({$pricefield}*(100-IF(pd.discount,pd.discount,0)-{$this->sCustomergroup["discount"]})/100*{$this->sCurrency["factor"]} AS DECIMAL(10,3)),2) SEPARATOR ';')) as group_pricenet";
$sql_add_select[] = "IF(COUNT({$pricefield})<=1,'',GROUP_CONCAT(ROUND(CAST({$pricefield}*(100+t.tax-IF(pd.discount,pd.discount,0)-{$this->sCustomergroup["discount"]})/100*{$this->sCurrency["factor"]} AS DECIMAL(10,3)),2) SEPARATOR ';')) as group_price";
$sql_add_select[] = "IF(COUNT(d.active)<=1,'',GROUP_CONCAT(d.active SEPARATOR ';')) as group_active";
$sql_add_select[] = "IF(COUNT(d.instock)<=1,'',GROUP_CONCAT(d.instock SEPARATOR ';')) as group_instock";
$sql_add_group_by = "a.id";
$sql_add_article_detail_join_condition = "AND d.kind=1";
} elseif ($this->sSettings["variant_export"] == 2) {
$sql_add_group_by = "d.id";
$sql_add_article_detail_join_condition = '';
}
$grouppricefield = "gp.price";
if (empty($this->sSettings["variant_export"]) || $this->sSettings["variant_export"] == 2 || $this->sSettings["variant_export"] == 1) {
$sql_add_join[] = "\n JOIN (SELECT NULL as `articleID` , NULL as `valueID` , NULL as `attr1` , NULL as `attr2` , NULL as `attr3` , NULL as `attr4` , NULL as `attr5` , NULL as `attr6` , NULL as `attr7` , NULL as `attr8` , NULL as `attr9` , NULL as `attr10` , NULL as `standard` , NULL as `active` , NULL as `ordernumber` , NULL as `instock`, NULL as `minpurchase`) as v\n ";
$sql_add_join[] = "\n JOIN (SELECT NULL as articleID, NULL as valueID, NULL as groupkey, NULL as price, NULL as optionID) as gp\n ";
}
if (!empty($this->sSettings["active_filter"])) {
$sql_add_where[] = "(a.active = 1 AND (v.active=1 OR (v.active IS NULL AND d.active=1)))";
}
if (!empty($this->sSettings["stockmin_filter"])) {
$sql_add_where[] = "(v.instock>=d.stockmin OR (v.instock IS NULL AND d.instock>=d.stockmin))";
}
if (!empty($this->sSettings["instock_filter"])) {
$sql_add_where[] = "(v.instock>={$this->sSettings["instock_filter"]} OR (v.instock IS NULL AND d.instock>={$this->sSettings["instock_filter"]}))";
}
if (!empty($this->sSettings["price_filter"])) {
$sql_add_where[] = "ROUND(CAST(IFNULL({$grouppricefield},{$pricefield})*(100+t.tax-IF(pd.discount IS NULL,0,pd.discount)-{$this->sCustomergroup["discount"]})/100*{$this->sCurrency["factor"]} AS DECIMAL(10,3)),2)>=" . $this->sSettings["price_filter"];
}
if (!empty($this->sSettings["own_filter"]) && trim($this->sSettings["own_filter"])) {
$sql_add_where[] = "(" . $this->sSettings["own_filter"] . ")";
}
if ($this->config->offsetGet('hideNoInstock')) {
$sql_add_where[] = "(\n (a.laststock * v.instock >= a.laststock * v.minpurchase)\n OR\n (a.laststock * d.instock >= a.laststock * d.minpurchase)\n )";
}
$sql_add_join = implode(" ", $sql_add_join);
if (!empty($sql_add_select)) {
$sql_add_select = ", " . implode(", ", $sql_add_select);
} else {
$sql_add_select = "";
}
if (!empty($sql_add_where)) {
$sql_add_where = " AND " . implode(" AND ", $sql_add_where);
} else {
$sql_add_where = "";
}
if (!empty($sql_add_group_by)) {
$sql_add_group_by = "GROUP BY ({$sql_add_group_by})";
} else {
$sql_add_group_by = "";
}
$sql = "\n SELECT\n a.id as `articleID`,\n a.name,\n a.description,\n a.description_long,\n a.main_detail_id,\n d.shippingtime,\n d.shippingfree,\n a.topseller,\n a.keywords,\n d.minpurchase,\n d.purchasesteps,\n d.maxpurchase,\n d.purchaseunit,\n d.referenceunit,\n a.taxID,\n a.filtergroupID,\n a.supplierID,\n d.unitID,\n IF(a.changetime!='0000-00-00 00:00:00',a.changetime,'') as `changed`,\n IF(a.datum!='0000-00-00',a.datum,'') as `added`,\n IF(d.releasedate!='0000-00-00',d.releasedate,'') as `releasedate`,\n a.active as active,\n\n d.id as `articledetailsID`,\n IF(v.ordernumber IS NOT NULL,v.ordernumber,d.ordernumber) as ordernumber,\n\n d.suppliernumber,\n d.ean,\n d.width,\n d.height,\n d.length,\n d.kind,\n IF(v.standard=1||kind=1,1,0) as standard,\n d.additionaltext,\n COALESCE(sai.impressions, 0) as impressions,\n d.sales,\n\n IF(v.active IS NOT NULL,IF(a.active=0,0,v.active),a.active) as active,\n IF(v.instock IS NOT NULL,v.instock,d.instock) as instock,\n (\n SELECT AVG(av.points)\n FROM s_articles_vote as av WHERE active=1\n AND articleID=a.id\n ) as sVoteAverage,\n (\n SELECT COUNT(*)\n FROM s_articles_vote as av WHERE active=1\n AND articleID=a.id\n ) as sVoteCount,\n d.stockmin,\n d.weight,\n d.position,\n\n at.attr1, at.attr2, at.attr3, at.attr4, at.attr5, at.attr6, at.attr7, at.attr8, at.attr9, at.attr10,\n at.attr11, at.attr12, at.attr13, at.attr14, at.attr15, at.attr16, at.attr17, at.attr18, at.attr19, at.attr20,\n\n s.name as supplier,\n u.unit,\n u.description as unit_description,\n t.tax,\n CONCAT(i.img, '.', i.extension) as image,\n\n a.configurator_set_id as configurator,\n\n ROUND(CAST(IFNULL({$grouppricefield}, {$pricefield})*(100-IF(pd.discount,pd.discount,0)-{$this->sCustomergroup["discount"]})/100*{$this->sCurrency["factor"]} AS DECIMAL(10,3)),2) as netprice,\n ROUND(CAST(IFNULL({$grouppricefield}, {$pricefield})*(100+t.tax)/100*(100-IF(pd.discount,pd.discount,0)-{$this->sCustomergroup["discount"]})/100*{$this->sCurrency["factor"]} AS DECIMAL(10,3)),2) as price,\n pd.discount,\n ROUND(CAST({$pseudoprice}*{$this->sCurrency["factor"]} AS DECIMAL(10,3)),2) as netpseudoprice,\n ROUND(CAST({$pseudoprice}*(100+t.tax)*{$this->sCurrency["factor"]}/100 AS DECIMAL(10,3)),2) as pseudoprice,\n {$baseprice},\n IF(file IS NULL,0,1) as esd\n\n {$sql_add_select}\n\n FROM s_articles a\n INNER JOIN s_articles_details d\n ON d.articleID = a.id\n {$sql_add_article_detail_join_condition}\n LEFT JOIN s_articles_attributes at\n ON d.id = at.articledetailsID\n\n LEFT JOIN `s_core_units` as `u`\n ON d.unitID = u.id\n LEFT JOIN `s_core_tax` as `t`\n ON a.taxID = t.id\n LEFT JOIN `s_articles_supplier` as `s`\n ON a.supplierID = s.id\n\n LEFT JOIN s_core_pricegroups_discounts pd\n ON a.pricegroupActive=1\n AND\ta.pricegroupID=groupID\n AND customergroupID = 1\n AND discountstart=1\n\n LEFT JOIN s_articles_esd e ON e.articledetailsID=d.id\n\n LEFT JOIN (\n SELECT articleID\n FROM\n s_export_categories as ec,\n s_articles_categories_ro as ac\n WHERE feedID={$this->sFeedID}\n AND ec.categoryID=ac.categoryID\n GROUP BY articleID\n ) AS bc\n ON bc.articleID=a.id\n\n LEFT JOIN s_export_suppliers AS bs\n ON (bs.supplierID=s.id AND bs.feedID={$this->sFeedID})\n\n LEFT JOIN s_export_articles AS ba\n ON (ba.articleID=a.id AND ba.feedID={$this->sFeedID})\n\n LEFT JOIN s_articles_prices AS p\n ON p.articledetailsID = d.id\n AND p.`from`=1\n AND p.pricegroup='EK'\n\n LEFT JOIN\n (\n SELECT articleId AS id, SUM(s.impressions) AS impressions\n FROM s_statistics_article_impression s\n GROUP BY articleId\n ) sai ON sai.id = a.id\n\n {$sql_add_join}\n\n WHERE bc.articleID IS NULL\n AND bs.supplierID IS NULL\n AND a.mode = 0\n AND d.kind != 3\n AND ba.articleID IS NULL\n {$sql_add_where}\n\n {$sql_add_group_by}\n ";
if (!empty($this->sSettings["count_filter"])) {
$sql .= "LIMIT " . $this->sSettings["count_filter"];
}
return $sql;
}
