<?php
/**
* Shopware 4.0
* Copyright © 2012 shopware AG
*
* According to our dual licensing model, this program can be used either
* under the terms of the GNU Affero General Public License, version 3,
* or under a proprietary license.
*
* The texts of the GNU Affero General Public License with an additional
* permission and of our proprietary license can be found at and
* in the LICENSE file you have received along with this program.
*
* This program is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU Affero General Public License for more details.
*
* "Shopware" is a registered trademark of shopware AG.
* The licensing of the program under the AGPLv3 does not imply a
* trademark license. Therefore any rights, title and interest in
* our trademarks remain entirely with us.
*
* @category Shopware
* @package Shopware_Core
* @subpackage Class
* @copyright Copyright (c) 2012, shopware AG (http://www.shopware.de)
* @version $Id$
* @author Stefan Hamann
* @author $Author$
*/
/**
* Deprecated Shopware Class to provide article export feeds
*
* todo@all: Documentation
*/
class sExport
{
var $sFeedID;
var $sHash;
var $sSettings;
var $sDB;
var $sApi;
var $sSystem;
var $sPath;
var $sTemplates;
var $sCurrency;
var $sCustomergroup;
var $sLanguage;
var $sMultishop;
/**
* @var \Enlight_Controller_Request_RequestHttp
*/
var $request;
var $shop;
/**
* @var \Shopware\Models\Article\Repository
*/
protected $articleRepository = null;
/**
* @var \Shopware\Models\Media\Repository
*/
protected $mediaRepository = null;
/**
* @var \Shopware\Models\Media\Album
*/
protected $articleMediaAlbum = null;
public function sGetCurrency ($currency)
{
static $cache = array();
if(empty($currency))
$currency = $this->sMultishop["defaultcurrency"];
if(isset($cache[$currency]))
return $cache[$currency];
if(is_numeric($currency))
$sql = "id=".$currency;
elseif(is_string($currency))
$sql = "currency=".$this->sDB->qstr(trim($currency));
else
return false;
$sql = "
SELECT *
FROM s_core_currencies
WHERE $sql
";
return $cache[$currency] = $this->sDB->GetRow($sql);
}
public function sGetCustomergroup ($customergroup)
{
static $cache = array();
if(empty($customergroup))
$customergroup = $this->sMultishop["defaultcustomergroup"];
if(isset($cache[$customergroup]))
return $cache[$customergroup];
if(is_int($customergroup))
$sql = "id=".$customergroup;
elseif(is_string($customergroup))
$sql = "groupkey=".$this->sDB->qstr(trim($customergroup));
else
return false;
$sql = "
SELECT *
FROM s_core_customergroups
WHERE $sql
";
return $cache[$customergroup] = $this->sDB->GetRow($sql);
}
public function sGetMultishop ($language)
{
static $cache = array();
if(isset($cache[$language]))
return $cache[$language];
if(empty($language))
$sql = "`default`=1";
elseif(is_numeric($language))
$sql = "id=".$language;
elseif(is_string($language))
$sql = "name=".$this->sDB->qstr(trim($language));
$sql = "
SELECT *
FROM s_core_multilanguage
WHERE $sql
";
return $cache[$language] = $this->sDB->GetRow($sql);
}
public function sGetLanguage ($language)
{
static $cache = array();
if(isset($cache[$language]))
return $cache[$language];
if(empty($language))
$sql = "default=1";
elseif(is_numeric($language))
$sql = "id=".$language;
elseif(is_string($language))
$sql = "isocode=".$this->sDB->qstr(trim($language));
$sql = "
SELECT *
FROM s_core_multilanguage
WHERE $sql
ORDER BY skipbackend
";
return $cache[$language] = $this->sDB->GetRow($sql);
}
/**
* Helper function to get access to the article repository.
* @return \Shopware\Models\Article\Repository
*/
private function getArticleRepository() {
if ($this->articleRepository === null) {
$this->articleRepository = Shopware()->Models()->getRepository('Shopware\Models\Article\Article');
}
return $this->articleRepository;
}
/**
* Helper function to get access to the media repository.
* @return \Shopware\Models\Media\Repository
*/
private function getMediaRepository() {
if ($this->mediaRepository === null) {
$this->mediaRepository = Shopware()->Models()->getRepository('Shopware\Models\Media\Media');
}
return $this->mediaRepository;
}
public function sInitSettings ()
{
$hash = $this->sDB->qstr($this->sHash);
$sql = "
SELECT
id as feedID, s_export.*
FROM
s_export
WHERE
id = {$this->sFeedID}
AND
hash = $hash
AND
`active`=1
";
$this->sSettings = $this->sDB->GetRow($sql);
if(empty($this->sSettings))
die();
$this->sSettings["dec_separator"] = ",";
if($this->sSettings["formatID"]==1)
{
$this->sSettings["fieldmark"] = "\"";
$this->sSettings["escaped_fieldmark"] = "\"\"";
$this->sSettings["separator"] = ";";
$this->sSettings["escaped_separator"] = ";";
$this->sSettings["line_separator"] = "\r\n";
$this->sSettings["escaped_line_separator"] = "\r\n";
}
elseif ($this->sSettings["formatID"]==2)
{
$this->sSettings["fieldmark"] = "";
$this->sSettings["escaped_fieldmark"] = "";
$this->sSettings["separator"] = "\t";
$this->sSettings["escaped_separator"] = "";
$this->sSettings["line_separator"] = "\r\n";
$this->sSettings["escaped_line_separator"] = "";
}
elseif ($this->sSettings["formatID"]==4)
{
$this->sSettings["fieldmark"] = "";
$this->sSettings["escaped_fieldmark"] = "";
$this->sSettings["separator"] = "|";
$this->sSettings["escaped_separator"] = "";
$this->sSettings["line_separator"] = "\r\n";
$this->sSettings["escaped_line_separator"] = "";
}
if(!empty($this->sSettings['encodingID']) && $this->sSettings['encodingID']==2) {
$this->sSettings['encoding'] = 'UTF-8';
} else {
$this->sSettings['encoding'] = 'ISO-8859-1';
}
$this->sMultishop = $this->sGetMultishop($this->sSettings["multishopID"]);
if(empty($this->sSettings["categoryID"]))
{
$this->sSettings["categoryID"] = $this->sMultishop["parentID"];
}
if(empty($this->sSettings["customergroupID"]))
{
$this->sSettings["customergroupID"] = $this->sMultishop["defaultcustomergroup"];
}
else
{
$this->sSettings["customergroupID"] = (int) $this->sSettings["customergroupID"];
}
if(empty($this->sSettings["currencyID"]))
{
$this->sSettings["currencyID"] = $this->sMultishop["defaultcurrency"];
}
if(empty($this->sSettings["languageID"]))
{
$this->sSettings["languageID"] = $this->sSettings["multishopID"];
}
$this->sLanguage = $this->sGetMultishop($this->sSettings["languageID"]);
$this->sCurrency = $this->sGetCurrency($this->sSettings["currencyID"]);
$this->sCustomergroup = $this->sGetCustomergroup($this->sSettings["customergroupID"]);
$this->articleMediaAlbum = $this->getMediaRepository()
->getAlbumWithSettingsQuery(-1)
->getOneOrNullResult(\Doctrine\ORM\AbstractQuery::HYDRATE_OBJECT);
$repository = Shopware()->Models()->getRepository('Shopware\Models\Shop\Shop');
$shop = $repository->getActiveById($this->sSettings['multishopID']);
//$shop = $repository->getActiveById($this->sLanguage['id']);
$repository = Shopware()->Models()->getRepository('Shopware\Models\Shop\Currency');
$shop->setCurrency($repository->find($this->sCurrency['id']));
$repository = Shopware()->Models()->getRepository('Shopware\Models\Shop\Locale');
$shop->setLocale($repository->find($this->sMultishop['locale']));
$shop->registerResources(Shopware()->Bootstrap());
$this->shop = $shop;
$this->sSystem->sCONFIG = Shopware()->Config();
}
public function sInitSmarty ()
{
$this->sSystem->sSMARTY->compile_id = "export_".$this->sFeedID;
$this->sSystem->sSMARTY->cache_lifetime = 0;
$this->sSystem->sSMARTY->debugging = 0;
$this->sSystem->sSMARTY->caching = 0;
$this->sSmarty->registerPlugin('modifier', 'format', array(&$this,'sFormatString'));
$this->sSmarty->registerPlugin('modifier', 'escape', array(&$this,'sEscapeString'));
$this->sSmarty->registerPlugin('modifier', 'category', array(&$this,'sGetArticleCategoryPath'));
$this->sSmarty->registerPlugin('modifier', 'link', array(&$this,'sGetArticleLink'));
$this->sSmarty->registerPlugin('modifier', 'image', array(&$this,'sGetImageLink'));
$this->sSmarty->registerPlugin('modifier', 'shippingcost', array(&$this,'sGetArticleShippingcost'));
$this->sSmarty->assign("sConfig",$this->sSystem->sCONFIG);
$this->sSmarty->assign("sLanguage",$this->sLanguage);
$this->sSmarty->assign("sMultishop",$this->sMultishop);
$this->sSmarty->assign("sCurrency",$this->sCurrency);
$this->sSmarty->assign("sCustomergroup",$this->sCustomergroup);
$this->sSmarty->assign("sSettings",$this->sSettings);
$this->sSmarty->config_vars["F"] = $this->sSettings["fieldmark"];
$this->sSmarty->config_vars["EF"] = $this->sSettings["escaped_separator"];
$this->sSmarty->config_vars["S"] = $this->sSettings["separator"];
$this->sSmarty->config_vars["ES"] = $this->sSettings["escaped_fieldmark"];
$this->sSmarty->config_vars["L"] = $this->sSettings["line_separator"];
$this->sSmarty->config_vars["EL"] = $this->sSettings["escaped_line_separator"];
if($this->sSettings['encoding'] == 'UTF-8') {
$this->sSmarty->config_vars['BOM'] = "\xEF\xBB\xBF";
} else {
$this->sSmarty->config_vars['BOM'] = '';
}
$this->sSmarty->config_vars['EN'] = $this->sSettings['encoding'];
}
public function sFormatString($string, $esc_type = '', $char_set = null)
{
return $this->sEscapeString($string, $esc_type, $char_set);
}
public function sEscapeString($string, $esc_type = '', $char_set = null)
{
if(empty($esc_type)) {
if(!empty($this->sSettings["formatID"]) && $this->sSettings["formatID"]==3) {
$esc_type = "html";
} else {
$esc_type = "csv";
}
}
if(empty($char_set)) {
$char_set = $this->sSettings['encoding'];
}
switch ($esc_type) {
case 'number':
return number_format($string,2,$this->sSettings["dec_separator"],'');
case 'csv':
if(empty($this->sSettings["escaped_line_separator"])) {
$string = preg_replace('#[\r\n]+#m', ' ', $string);
} elseif ($this->sSettings["escaped_line_separator"]!=$this->sSettings["line_separator"]) {
$string = str_replace($this->sSettings["line_separator"],$this->sSettings['escaped_line_separator'],$string);
}
if(!empty($this->sSettings["fieldmark"])) {
$string = str_replace($this->sSettings["fieldmark"],$this->sSettings['escaped_fieldmark'],$string);
} else {
$string = str_replace($this->sSettings['separator'],$this->sSettings['escaped_separator'],$string);
}
if ($char_set != 'UTF-8') {
$string = utf8_decode($string);
}
$string = html_entity_decode($string, ENT_NOQUOTES, $char_set);
return $this->sSettings["fieldmark"].$string.$this->sSettings["fieldmark"];
case 'xml':
if ($char_set != 'UTF-8') {
$string = utf8_decode($string);
}
return $string;
case 'html':
$string = html_entity_decode($string, ENT_NOQUOTES, $char_set);
return htmlspecialchars($string, ENT_QUOTES, $char_set, false);
case 'htmlall':
return htmlentities($string, ENT_QUOTES, $char_set);
case 'url':
return rawurlencode($string);
case 'urlpathinfo':
return str_replace('%2F','/',rawurlencode($string));
case 'quotes':
// escape unescaped single quotes
return preg_replace("%(?<!\\\\)'%", "\\'", $string);
case 'hex':
// escape every character into hex
$return = '';
for ($x=0; $x < strlen($string); $x++) {
$return .= '%' . bin2hex($string[$x]);
}
return $return;
case 'hexentity':
$return = '';
for ($x=0; $x < strlen($string); $x++) {
$return .= '&#x' . bin2hex($string[$x]) . ';';
}
return $return;
case 'decentity':
$return = '';
for ($x=0; $x < strlen($string); $x++) {
$return .= '&#' . ord($string[$x]) . ';';
}
return $return;
case 'javascript':
// escape quotes and backslashes, newlines, etc.
return strtr($string, array('\\'=>'\\\\',"'"=>"\\'",'"'=>'\\"',"\r"=>'\\r',"\n"=>'\\n','</'=>'<\/'));
case 'mail':
// safe way to display e-mail address on a web page
return str_replace(array('@', '.'),array(' [AT] ', ' [DOT] '), $string);
case 'nonstd':
// escape non-standard chars, such as ms document quotes
$_res = '';
for($_i = 0, $_len = strlen($string); $_i < $_len; $_i++) {
$_ord = ord(substr($string, $_i, 1));
// non-standard char, escape it
if($_ord >= 126){
$_res .= '&#' . $_ord . ';';
}
else {
$_res .= substr($string, $_i, 1);
}
}
return $_res;
}
}
public function sGetArticleLink ($articleID, $title="")
{
return $this->sSystem->rewriteLink(array(2=>$this->sSYSTEM->sCONFIG["sBASEFILE"]."?sViewport=detail&sArticle=$articleID",3=>$title),true).(empty($this->sSettings["partnerID"])?"":"?sPartner=".urlencode($this->sSettings["partnerID"]));
}
public function sGetImageLink($hash, $imageSize = null)
{
if (!empty($hash)) {
$sql = "SELECT articleID FROM s_articles_img WHERE img =?";
$articleId = Shopware()->Db()->fetchOne($sql, array($hash));
$imageSize = intval($imageSize);
$image = $this->getArticleRepository()->getArticleCoverImageQuery($articleId)->getOneOrNullResult(\Doctrine\ORM\AbstractQuery::HYDRATE_ARRAY);
if(empty($image)) {
return "";
}
//first we get all thumbnail sizes of the article album
$sizes = $this->articleMediaAlbum->getSettings()->getThumbnailSize();
//now we get the configured image and thumbnail dir.
$imageDir = 'http://'. $this->shop->getHost() . $this->request->getBasePath() . '/media/image/';
$thumbDir = $imageDir . 'thumbnail/';
foreach ($sizes as $key => $size) {
if (strpos($size, 'x') === 0) {
$size = $size . 'x' . $size;
}
$imageData[$key] = $thumbDir . $image['path'] . '_' . $size . '.' . $image['extension'];
}
if (!empty($imageData)) {
return $imageData[$imageSize];
}
}
return "";
}
public function sMapTranslation($object,$objectdata)
{
switch ($object){
case "detail":
case "article":
$map = array("txtshortdescription"=>"description","txtlangbeschreibung"=>"description_long","txtArtikel"=>"name","txtzusatztxt"=>"additionaltext");
for ($i=1;$i<=20;$i++)
$map["attr$i"] = "attr$i";
break;
case "link":
$map = array("linkname"=>"description");
break;
case "download":
$map = array("downloadname"=>"description");
break;
}
if (empty($objectdata))
return array();
$objectdata = unserialize($objectdata);
if (empty($objectdata))
return array();
$result = array();
foreach ($map as $key=>$value)
{
if(isset($objectdata[$key]))
$result[$value] = $objectdata[$key];
}
return $result;
}
public function _decode_line($line)
{
$separator = ";";
$fieldmark = "\"";
$elements = explode($separator, $line);
$tmp_elements = array();
for ($i = 0; $i < count($elements); $i++)
{
$nquotes = substr_count($elements[$i], $fieldmark);
if ($nquotes %2 == 1) {
if(isset($elements[$i+1]))
$elements[$i+1] = $elements[$i].$separator.$elements[$i+1];
}
else
{
if ($nquotes > 0)
{
if(substr($elements[$i],0,1)==$fieldmark)
$elements[$i] = substr($elements[$i],1);
if(substr($elements[$i],-1,1)==$fieldmark)
$elements[$i] = substr($elements[$i],0,-1);
$elements[$i] = str_replace($fieldmark.$fieldmark, $fieldmark, $elements[$i]);
}
$tmp_elements[] = $elements[$i];
}
}
return $tmp_elements;
}
public function sCreateSql ()
{
$sql_add_join = array();
$sql_add_select = array();
$sql_add_where = array();
if(empty($this->sLanguage["skipbackend"]) && !empty($this->sLanguage["isocode"]))
{
$sql_isocode = $this->sDB->qstr($this->sLanguage["isocode"]);
$sql_add_join[] = "
LEFT JOIN s_core_translations as ta
ON ta.objectkey=a.id AND ta.objecttype='article' AND ta.objectlanguage=$sql_isocode
LEFT JOIN s_core_translations as td
ON td.objectkey=d.id AND td.objecttype='variant' AND td.objectlanguage=$sql_isocode
";
$sql_add_select[] = "ta.objectdata as article_translation";
$sql_add_select[] = "td.objectdata as detail_translation";
}
if(!empty($this->sSettings["categoryID"]))
{
$sql_add_join[] = "
JOIN s_categories c
ON c.id = {$this->sSettings["categoryID"]}
LEFT JOIN s_categories c2
ON c2.left > c.left
AND c2.right <= c.right
JOIN s_articles_categories act
ON act.articleID = a.id
AND (
act.categoryID = c.id
OR act.categoryID = c2.id
)
";
}
if(empty($this->sSettings["image_filter"]))
{
$sql_add_join[] = "
LEFT JOIN s_articles_img as i
ON i.articleID = a.id AND i.main=1 AND i.article_detail_id IS NULL
";
}
else
{
$sql_add_join[] = "
JOIN s_articles_img as i
ON i.articleID = a.id AND i.main=1 AND i.article_detail_id IS NULL
";
}
if(!empty($this->sCustomergroup["groupkey"])&&empty($this->sCustomergroup["mode"])&&$this->sCustomergroup["groupkey"]!="EK")
{
$sql_add_join[] = "
LEFT JOIN s_articles_prices as p2
ON p2.articledetailsID = d.id AND p2.`from`=1
AND p2.pricegroup='{$this->sCustomergroup["groupkey"]}'
AND p2.price!=0
";
$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(CONCAT('\"',REPLACE(d.ordernumber,'\"','\"\"'),'\"') SEPARATOR ';')) as group_ordernumber";
$sql_add_select[] = "IF(COUNT(d.additionaltext)<=1,'',GROUP_CONCAT(CONCAT('\"',REPLACE(d.additionaltext,'\"','\"\"'),'\"') SEPARATOR ';')) as group_additionaltext";
$sql_add_select[] = "IF(COUNT($pricefield)<=1,'',GROUP_CONCAT(ROUND($pricefield*(100-IF(pd.discount,pd.discount,0)-{$this->sCustomergroup["discount"]})/100*{$this->sCurrency["factor"]},2) SEPARATOR ';')) as group_pricenet";
$sql_add_select[] = "IF(COUNT($pricefield)<=1,'',GROUP_CONCAT(ROUND($pricefield*(100+t.tax-IF(pd.discount,pd.discount,0)-{$this->sCustomergroup["discount"]})/100*{$this->sCurrency["factor"]},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";
}
$grouppricefield = "gp.price";
if(empty($this->sSettings["variant_export"])||$this->sSettings["variant_export"]==2||$this->sSettings["variant_export"]==1)
{
$sql_add_join[] = "
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`) as v
";
$sql_add_join[] = "
JOIN (SELECT NULL as articleID, NULL as valueID, NULL as groupkey, NULL as price, NULL as optionID) as gp
";
}
if(empty($this->sSettings["variant_export"])||$this->sSettings["variant_export"]==1)
{
$sql_add_group_by = "a.id";
}
elseif($this->sSettings["variant_export"]==2)
{
$sql_add_group_by = "d.id";
}
else
{
//$sql_add_group_by = "d.id, v.valueID";
}
if(!empty($this->sSettings["active_filter"]))
{
$sql_add_where[] = "(v.active=1 OR (v.active IS NULL AND a.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(IFNULL($grouppricefield,$pricefield)*(100+t.tax-IF(pd.discount IS NULL,0,pd.discount)-{$this->sCustomergroup["discount"]})/100*{$this->sCurrency["factor"]},2)>=".$this->sSettings["price_filter"];
}
if(!empty($this->sSettings["own_filter"])&&trim($this->sSettings["own_filter"]))
{
$sql_add_where[] = "(".$this->sSettings["own_filter"].")";
}
$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 = "
SELECT
a.id as `articleID`,
a.name,
a.description,
a.description_long,
d.shippingtime,
d.shippingfree,
a.topseller,
a.keywords,
d.minpurchase,
d.purchasesteps,
d.maxpurchase,
d.purchaseunit,
d.referenceunit,
a.taxID,
a.supplierID,
d.unitID,
IF(a.changetime!='0000-00-00 00:00:00',a.changetime,'') as `changed`,
IF(a.datum!='0000-00-00',a.datum,'') as `added`,
IF(d.releasedate!='0000-00-00',d.releasedate,'') as `releasedate`,
a.active as active,
d.id as `articledetailsID`,
IF(v.ordernumber IS NOT NULL,v.ordernumber,d.ordernumber) as ordernumber,
d.suppliernumber,
d.ean,
d.width,
d.height,
d.length,
d.kind,
IF(v.standard=1||kind=1,1,0) as standard,
d.additionaltext,
d.impressions,
d.sales,
IF(v.active IS NOT NULL,IF(a.active=0,0,v.active),a.active) as active,
IF(v.instock IS NOT NULL,v.instock,d.instock) as instock,
(
SELECT AVG(av.points)
FROM s_articles_vote as av WHERE active=1
AND articleID=a.id
) as sVoteAverage,
(
SELECT COUNT(*)
FROM s_articles_vote as av WHERE active=1
AND articleID=a.id
) as sVoteCount,
d.stockmin,
d.weight,
d.position,
at.attr1, at.attr2, at.attr3, at.attr4, at.attr5, at.attr6, at.attr7, at.attr8, at.attr9, at.attr10,
at.attr11, at.attr12, at.attr13, at.attr14, at.attr15, at.attr16, at.attr17, at.attr18, at.attr19, at.attr20,
s.name as supplier,
u.unit,
u.description as unit_description,
t.tax,
i.img as image,
a.configurator_set_id as configurator,
ROUND(IFNULL($grouppricefield, $pricefield)*(100-IF(pd.discount,pd.discount,0)-{$this->sCustomergroup["discount"]})/100*{$this->sCurrency["factor"]},2) as netprice,
ROUND(IFNULL($grouppricefield, $pricefield)*(100+t.tax)/100*(100-IF(pd.discount,pd.discount,0)-{$this->sCustomergroup["discount"]})/100*{$this->sCurrency["factor"]},2) as price,
pd.discount,
ROUND($pseudoprice*{$this->sCurrency["factor"]},2) as netpseudoprice,
ROUND($pseudoprice*(100+t.tax)*{$this->sCurrency["factor"]}/100,2) as pseudoprice,
$baseprice,
IF(file IS NULL,0,1) as esd
$sql_add_select
FROM s_articles a
INNER JOIN s_articles_details d
ON d.articleID = a.id
LEFT JOIN s_articles_attributes at
ON d.id = at.articledetailsID
LEFT JOIN `s_core_units` as `u`
ON d.unitID = u.id
LEFT JOIN `s_core_tax` as `t`
ON a.taxID = t.id
LEFT JOIN `s_articles_supplier` as `s`
ON a.supplierID = s.id
LEFT JOIN s_core_pricegroups_discounts pd
ON a.pricegroupActive=1
AND a.pricegroupID=groupID
AND customergroupID = 1
AND discountstart=1
LEFT JOIN s_articles_esd e ON e.articledetailsID=d.id
LEFT JOIN (
SELECT articleID
FROM
s_export_categories as ec,
s_articles_categories as ac
WHERE feedID={$this->sFeedID}
AND ec.categoryID=ac.categoryID
GROUP BY articleID
) AS bc
ON bc.articleID=a.id
LEFT JOIN s_export_suppliers AS bs
ON (bs.supplierID=s.id AND bs.feedID={$this->sFeedID})
LEFT JOIN s_export_articles AS ba
ON (ba.articleID=a.id AND ba.feedID={$this->sFeedID})
LEFT JOIN s_articles_prices as p
ON p.articledetailsID = d.id
AND p.`from`=1
AND p.pricegroup='EK'
$sql_add_join
WHERE bc.articleID IS NULL
AND bs.supplierID IS NULL
AND a.mode = 0
AND d.kind != 3
AND ba.articleID IS NULL
$sql_add_where
$sql_add_group_by
";
if(!empty($this->sSettings["count_filter"]))
{
$sql .= "LIMIT ".$this->sSettings["count_filter"];
}
return $sql;
}
public function sGetArticleCategoryPath ($articleID, $separator = " > ", $categoryID=null)
{
if(empty($categoryID))
$categoryID = $this->sSettings["categoryID"];
$articleCategoryId = $this->sSYSTEM->sMODULES["sCategories"]->sGetCategoryIdByArticleId($articleID,$categoryID);
$breadcrumb = array_reverse(Shopware()->Modules()->sCategories()->sGetCategoriesByParent($articleCategoryId));
foreach ($breadcrumb as $breadcrumbObj){
$breadcrumbs[] = $breadcrumbObj["name"];
}
return htmlspecialchars_decode(implode($separator,$breadcrumbs));
}
public function sGetCountry ($country)
{
static $cache = array();
if(empty($country))
return false;
if(isset($cache[$country]))
return $cache[$country];
if(is_numeric($country))
$sql = "c.id=".$country;
elseif(is_string($country))
$sql = "c.countryiso=".$this->sDB->qstr($country);
else
return false;
$sql = "
SELECT
c.id, c.id as countryID, countryname, countryiso,
countryen, c.position, notice,
c.shippingfree as shippingfree
FROM s_core_countries c
WHERE $sql
";
return $cache[$country] = $this->sDB->GetRow($sql);
}
public function sGetPaymentmean ($payment)
{
static $cache = array();
if(empty($payment))
return false;
if(isset($cache[$payment]))
return $cache[$payment];
if(is_numeric($payment))
$sql = "id=".$payment;
elseif(is_string($payment))
$sql = "name=".$this->sDB->qstr($payment);
else
return false;
$sql = "
SELECT * FROM s_core_paymentmeans
WHERE $sql
";
$cache[$payment] = $this->sDB->GetRow($sql);
$cache[$payment]["country_surcharge"] = array();
if (!empty($cache[$payment]["surchargestring"])){
foreach(explode(";",$cache[$payment]["surchargestring"]) as $countrySurcharge){
list($key,$value) = explode(":",$countrySurcharge);
$value = floatval(str_replace(",",".",$value));
if (!empty($value)){
$cache[$payment]["country_surcharge"][$key] = $value;
}
}
}
$cache[$payment]["surcharge"] = $cache[$payment]["surcharge"];
return $cache[$payment];
}
public function sGetDispatch ($dispatch = null, $country = null)
{
if(empty($dispatch))
$sql_order = "";
elseif(is_numeric($dispatch))
$sql_order = "IF(sd.id=".(int)$dispatch.",0,1),";
elseif(is_string($dispatch))
$sql_order = "IF(name=".$this->sDB->qstr($dispatch).",0,1),";
else
$sql_order = "";
if(empty($country))
$sql_where = "";
elseif(is_numeric($country))
$sql_where = "c.id=".$country;
elseif(is_string($country))
$sql_where = "c.countryiso=".$this->sDB->qstr($country);
else
$sql_where = "";
static $cache = array();
if(isset($cache[$sql_order."|".$sql_where]))
return $cache[$sql_order."|".$sql_where];
if(!empty($sql_where))
{
$sql_from = " s_premium_dispatch_countries sc, s_core_countries c";
$sql_where = "AND $sql_where AND c.id=sc.countryID";
}
else
{
$sql_from = "";
}
$sql = "
SELECT sd.id, name, sd.description, sd.shippingfree
FROM
s_premium_dispatch sd,
$sql_from
WHERE sd.active = 1
AND sd.id = sc.dispatchID
$sql_where
ORDER BY $sql_order sd.position ASC LIMIT 1
";
return $cache[$sql_order."|".$sql_where] = $this->sDB->GetRow($sql);
}
public function sGetDispatchBasket ($article, $countryID=null, $paymentID = null)
{
$sql_select = '';
if(!empty($this->sSystem->sCONFIG['sPREMIUMSHIPPIUNGASKETSELECT']))
{
$sql_select .= ', '.$this->sSystem->sCONFIG['sPREMIUMSHIPPIUNGASKETSELECT'];
}
$sql = 'SELECT id, calculation_sql FROM s_premium_dispatch WHERE calculation=3';
$calculations = $this->sDB->GetAssoc($sql);
if(!empty($calculations))
foreach ($calculations as $dispatchID => $calculation)
{
if(empty($calculation)) $calculation = $this->sSYSTEM->sDB_CONNECTION->qstr($calculation);
$sql_select .= ', ('.$calculation.') as calculation_value_'.$dispatchID;
}
$sql = "
SELECT
MIN(IFNULL(g.instock,d.instock)>=b.quantity) as instock,
MIN(IFNULL(g.instock,d.instock)>=(b.quantity+d.stockmin)) as stockmin,
MIN(a.laststock) as laststock,
SUM(d.weight*b.quantity) as weight,
SUM(IF(a.id,b.quantity,0)) as count_article,
MAX(b.shippingfree) as shippingfree,
SUM(IF(b.modus=0,b.quantity*b.price/b.currencyFactor,0)) as amount,
MAX(t.tax) as max_tax, u.id as userID
$sql_select
, b.articleID
FROM (
SELECT
NULL as sessionID,
? as articleID,
? as ordernumber,
? as shippingfree,
1 as quantity,
? as price,
? as netprice,
0 as modus,
? as esdarticle,
'' as config,
? as currencyFactor,
'' as ob_attr1,
'' as ob_attr2,
'' as ob_attr3,
'' as ob_attr4,
'' as ob_attr5,
'' as ob_attr6
) as b
LEFT JOIN s_articles a
ON b.articleID=a.id
AND b.modus=0
AND b.esdarticle=0
LEFT JOIN s_articles_groups_value g
ON g.ordernumber=b.ordernumber
AND g.articleID=a.id
LEFT JOIN s_articles_details d
ON (d.ordernumber=b.ordernumber OR g.valueID IS NOT NULL)
AND d.articleID=a.id
LEFT JOIN s_articles_attributes at
ON at.articledetailsID=d.id
LEFT JOIN s_core_tax t
ON t.id=a.taxID
LEFT JOIN s_user u
ON u.id=NULL
LEFT JOIN s_user_billingaddress ub
ON ub.userID=u.id
LEFT JOIN s_user_shippingaddress us
ON us.userID=u.id
GROUP BY b.sessionID
";
$basket = $this->sDB->GetRow($sql,array(
$article["articleID"],
$article["ordernumber"],
$article["shippingfree"],
$article["price"],
$article["netprice"],
$article["esd"],
$this->sCurrency["factor"]
));
if(empty($basket))
{
return false;
}
$basket['countryID'] = $countryID;
$basket['paymentID'] = $paymentID;
$basket['customergroupID'] = $this->sCustomergroup['id'];
$basket['multishopID'] = $this->sMultishop['id'];
$basket['sessionID'] = null;
return $basket;
}
public function sGetArticleShippingcost($article, $payment, $country, $dispatch = null)
{
if(empty($article)||!is_array($article)) return false;
$country = $this->sGetCountry($country);
if(empty($country)) return false;
$payment = $this->sGetPaymentmean($payment);
if(empty($payment)) return false;
if (!empty($payment["country_surcharge"][$country["countryiso"]]))
$payment["surcharge"] += $payment["country_surcharge"][$country["countryiso"]];
$payment['surcharge'] = round($payment['surcharge']*$this->sCurrency["factor"],2);
if (!empty($this->sSystem->sCONFIG['sPREMIUMSHIPPIUNG']))
{
return $this->sGetArticlePremiumShippingcosts($article,$payment,$country,$dispatch);
}
if(!empty($article["esd"])||(!empty($article["shippingfree"])&&!empty($dispatch["shippingfree"])))
return 0;
$dispatch = $this->sGetDispatch($dispatch,(int)$country["id"]);
if(empty($dispatch)) return false;
if (!empty($country["shippingfree"]) && !empty($dispatch["shippingfree"]) && $article["price"]>=$country["shippingfree"]*$this->sCurrency["factor"])
return 0;
return 0;
}
public function sGetPremiumDispatch ($basket, $dispatch = null)
{
if(empty($dispatch))
$sql_order = "";
elseif(is_numeric($dispatch))
$sql_order = "IF(d.id=".(int)$dispatch.",0,1),";
elseif(is_string($dispatch))
$sql_order = "IF(d.name=".$this->sDB->qstr($dispatch).",0,1),";
else
$sql_order = "";
$sql_add_join = "";
if(!empty($basket['paymentID']))
{
$sql_add_join .= "
JOIN s_premium_dispatch_paymentmeans dp
ON d.id = dp.dispatchID
AND dp.paymentID={$basket['paymentID']}
";
}
if(!empty($basket['countryID']))
{
$sql_add_join .= "
JOIN s_premium_dispatch_countries dc
ON d.id = dc.dispatchID
AND dc.countryID={$basket['countryID']}
";
}
$sql = "SELECT id, bind_sql FROM s_premium_dispatch WHERE type IN (0) AND bind_sql IS NOT NULL";
$statements = $this->sSYSTEM->sDB_CONNECTION->GetAssoc($sql);
$sql_where = "";
foreach ($statements as $dispatchID => $statement)
{
$sql_where .= "
AND ( d.id!=$dispatchID OR ($statement))
";
}
$sql_basket = array();
foreach ($basket as $key => $value)
{
$sql_basket[] = $this->sDB->qstr($value)." as `$key`";
}
$sql_basket = implode(', ',$sql_basket);
$sql = "
SELECT d.id, d.name, d.description, d.calculation, d.status_link, d.surcharge_calculation, d.bind_shippingfree, tax_calculation, t.tax as tax_calculation_value, d.shippingfree
FROM s_premium_dispatch d
JOIN ( SELECT $sql_basket ) b
$sql_add_join
LEFT JOIN (
SELECT dc.dispatchID
FROM s_articles_categories ac,
s_premium_dispatch_categories dc
WHERE ac.articleID={$basket['articleID']}
AND dc.categoryID=ac.categoryID
GROUP BY dc.dispatchID
) as dk
ON d.id = dk.dispatchID
LEFT JOIN s_core_tax t
ON t.id=d.tax_calculation
LEFT JOIN s_user u
ON u.id=0
AND u.active=1
LEFT JOIN s_user_billingaddress ub
ON ub.userID=u.id
LEFT JOIN s_user_shippingaddress us
ON us.userID=u.id
WHERE d.active = 1
AND (bind_weight_from IS NULL OR bind_weight_from <= b.weight)
AND (bind_weight_to IS NULL OR bind_weight_to >= b.weight)
AND (bind_price_from IS NULL OR bind_price_from <= b.amount)
AND (bind_price_to IS NULL OR bind_price_to >= b.amount)
AND (bind_laststock=0 OR (bind_laststock=1 AND b.instock) OR (bind_laststock=2 AND b.stockmin))
AND (bind_shippingfree!=1 OR NOT b.shippingfree)
AND (d.multishopID IS NULL OR d.multishopID= b.multishopID)
AND (d.customergroupID IS NULL OR d.customergroupID=b.customergroupID)
AND dk.dispatchID IS NULL
AND d.type IN (0)
$sql_where
ORDER BY $sql_order d.position, d.name
LIMIT 1
";
$dispatch = $this->sDB->GetRow($sql);
if(empty($dispatch))
{
$sql = "
SELECT
d.id, d.name,
d.description,
d.calculation,
d.status_link,
d.surcharge_calculation,
d.bind_shippingfree,
tax_calculation,
t.tax as tax_calculation_value
FROM s_premium_dispatch d
LEFT JOIN s_core_tax t
ON t.id=d.tax_calculation
WHERE d.active=1
AND d.type=1
ORDER BY d.position, d.name
LIMIT 1
";
$dispatch = $this->sDB->GetRow($sql);
}
return $dispatch;
}
public function sGetPremiumDispatchSurcharge ($basket)
{
if(empty($basket)) return false;
$sql = 'SELECT id, bind_sql FROM s_premium_dispatch WHERE type=2 AND bind_sql IS NOT NULL';
$statements = $this->sSYSTEM->sDB_CONNECTION->GetAssoc($sql);
$sql_where = '';
foreach ($statements as $dispatchID => $statement)
{
$sql_where .= "
AND ( d.id!=$dispatchID OR ($statement))
";
}
$sql_basket = array();
foreach ($basket as $key => $value)
{
$sql_basket[] = $this->sSYSTEM->sDB_CONNECTION->qstr($value)." as `$key`";
}
$sql_basket = implode(', ',$sql_basket);
$sql_add_join = "";
if(!empty($basket['paymentID']))
{
$sql_add_join .= "
JOIN s_premium_dispatch_paymentmeans dp
ON d.id = dp.dispatchID
AND dp.paymentID={$basket['paymentID']}
";
}
if(!empty($basket['countryID']))
{
$sql_add_join .= "
JOIN s_premium_dispatch_countries dc
ON d.id = dc.dispatchID
AND dc.countryID={$basket['countryID']}
";
}
$sql = "
SELECT d.id, d.calculation
FROM s_premium_dispatch d
JOIN ( SELECT $sql_basket ) b
$sql_add_join
LEFT JOIN (
SELECT dc.dispatchID
FROM s_articles_categories ac,
s_premium_dispatch_categories dc
WHERE ac.articleID={$basket['articleID']}
AND dc.categoryID=ac.categoryID
GROUP BY dc.dispatchID
) as dk
ON dk.dispatchID=d.id
LEFT JOIN s_user u
ON u.id=b.userID
AND u.active=1
LEFT JOIN s_user_billingaddress ub
ON ub.userID=u.id
LEFT JOIN s_user_shippingaddress us
ON us.userID=u.id
WHERE d.active=1
AND (bind_weight_from IS NULL OR bind_weight_from <= b.weight)
AND (bind_weight_to IS NULL OR bind_weight_to >= b.weight)
AND (bind_price_from IS NULL OR bind_price_from <= b.amount)
AND (bind_price_to IS NULL OR bind_price_to >= b.amount)
AND (bind_instock=0 OR bind_instock IS NULL OR (bind_instock=1 AND b.instock) OR (bind_instock=2 AND b.stockmin))
AND (bind_laststock=0 OR (bind_laststock=1 AND b.laststock))
AND (bind_shippingfree=2 OR NOT b.shippingfree)
AND (d.multishopID IS NULL OR d.multishopID=b.multishopID)
AND (d.customergroupID IS NULL OR d.customergroupID=b.customergroupID)
AND dk.dispatchID IS NULL
AND d.type = 2
AND (d.shippingfree IS NULL OR d.shippingfree > b.amount)
$sql_where
GROUP BY d.id
";
$dispatches = $this->sSYSTEM->sDB_CONNECTION->GetAll($sql);
$surcharge = 0;
if(!empty($dispatches))
foreach ($dispatches as $dispatch)
{
if(empty($dispatch['calculation']))
$from = round($basket['weight'],3);
elseif($dispatch['calculation']==1)
$from = round($basket['amount'],2);
elseif($dispatch['calculation']==2)
$from = round($basket['count_article']);
elseif($dispatch['calculation']==3)
$from = round($basket['calculation_value_'.$dispatch['id']]);
else
continue;
$sql = "
SELECT `value` , `factor`
FROM `s_premium_shippingcosts`
WHERE `from` <= $from
AND `dispatchID` = {$dispatch['id']}
ORDER BY `from` DESC
LIMIT 1
";
$result = $this->sSYSTEM->sDB_CONNECTION->GetRow($sql);
if(empty($result)) continue;
$surcharge += $result['value'];
if(!empty($result['factor']))
$surcharge += $result['factor']/100*$from;
}
return $surcharge;
}
public function sGetArticlePremiumShippingcosts($article, $payment, $country, $dispatch = null)
{
$basket = $this->sGetDispatchBasket($article,$country['id'],$payment['id']);
if(empty($basket)) return false;
$dispatch = $this->sGetPremiumDispatch($basket, $dispatch);
if(empty($dispatch)) return false;
if ((!empty($dispatch['shippingfree'])&&$dispatch['shippingfree']<=$basket['amount'])
||empty($basket['count_article'])
||(!empty($basket['shippingfree'])&&empty($dispatch['bind_shippingfree']))
)
{
if(empty($dispatch['surcharge_calculation']))
return $payment['surcharge'];
else
return 0;
}
if(empty($dispatch['calculation']))
$from = round($basket['weight'],3);
elseif($dispatch['calculation']==1)
$from = round($basket['amount'],2);
elseif($dispatch['calculation']==2)
$from = round($basket['count_article']);
elseif($dispatch['calculation']==3)
$from = round($basket['calculation_value_'.$dispatch['id']]);
else
return false;
$sql = "
SELECT `value` , `factor`
FROM `s_premium_shippingcosts`
WHERE `from`<=$from
AND `dispatchID`={$dispatch['id']}
ORDER BY `from` DESC
LIMIT 1
";
$result = $this->sDB->GetRow($sql);
if(empty($result)) return false;
$result['shippingcosts'] = $result['value'];
if(!empty($result['factor']))
$result['shippingcosts'] += $result['factor']/100*$from;
$result['surcharge'] = $this->sGetPremiumDispatchSurcharge($basket);
if(!empty($result['surcharge']))
$result['shippingcosts'] += $result['surcharge'];
$result['shippingcosts'] *= $this->sCurrency["factor"];
$result['shippingcosts'] = round($result['shippingcosts'],2);
if(!empty($payment['surcharge'])&&$dispatch['surcharge_calculation']!=2&&(empty($article['shippingfree'])||empty($dispatch['surcharge_calculation'])))
{
$result['shippingcosts'] += $payment['surcharge'];
}
return $result['shippingcosts'];
}
}
?>