/**
* Read data with translations from database
*
* @return array
*/
protected function readData()
{
$sql = "
SELECT
LOWER(REPLACE(e.name, '_', '')) as name,
IFNULL(IFNULL(v2.value, v1.value), e.value) as value
FROM s_core_config_elements e
LEFT JOIN s_core_config_values v1
ON v1.element_id = e.id
AND v1.shop_id = ?
LEFT JOIN s_core_config_values v2
ON v2.element_id = e.id
AND v2.shop_id = ?
";
$data = $this->_db->fetchPairs($sql, array(
1, //Shop parent id
isset($this->_shop) ? $this->_shop->getId() : null
));
$result = array();
foreach ($data as $key => $value) {
$result[$key] = unserialize($value);
}
$result['version'] = Shopware::VERSION;
$result['revision'] = Shopware::REVISION;
$result['versiontext'] = Shopware::VERSION_TEXT;
return $result;
}
/**
* Return list with suppliers
*
* @return array
*/
private function prepareSuppliers()
{
$suppliers = $this->db->fetchPairs('SELECT name, id FROM s_articles_supplier');
if (!is_array($suppliers)) {
return array();
}
return $suppliers;
}
/**
* Clear rewrite cache
*/
public function clearRewriteCache()
{
$cache = (int) $this->config->routerCache;
$cache = $cache < 360 ? 86400 : $cache;
$sql = "SELECT `id` FROM `s_core_config_elements` WHERE `name` LIKE 'routerlastupdate'";
$elementId = $this->db->fetchOne($sql);
$sql = "\n SELECT v.shop_id, v.value\n FROM s_core_config_values v\n WHERE v.element_id=?\n ";
$values = $this->db->fetchPairs($sql, array($elementId));
foreach ($values as $shopId => $value) {
$value = unserialize($value);
$value = min(strtotime($value), time() - $cache);
$value = date('Y-m-d H:i:s', $value);
$value = serialize($value);
$sql = '
UPDATE s_core_config_values SET value=?
WHERE shop_id=? AND element_id=?
';
$this->db->query($sql, array($value, $shopId, $elementId));
}
}
/**
* Get dispatch surcharge value for current basket and shipping method
* Used internally in sAdmin::sGetPremiumShippingcosts()
*
* @param $basket
* @param $type
* @return array|false
*/
public function sGetPremiumDispatchSurcharge($basket, $type = 2)
{
if (empty($basket)) {
return false;
}
$type = (int) $type;
$statements = $this->db->fetchPairs('
SELECT id, bind_sql
FROM s_premium_dispatch
WHERE active = 1 AND type = ?
AND bind_sql IS NOT NULL
', array($type));
$sql_where = '';
foreach ($statements as $dispatchID => $statement) {
$sql_where .= "\n AND ( d.id!={$dispatchID} OR ({$statement}))\n ";
}
$sql_basket = array();
foreach ($basket as $key => $value) {
$sql_basket[] = $this->db->quote($value) . " as `{$key}`";
}
$sql_basket = implode(', ', $sql_basket);
$sql = "\n SELECT d.id, d.calculation\n FROM s_premium_dispatch d\n\n JOIN ( SELECT {$sql_basket} ) b\n JOIN s_premium_dispatch_countries dc\n ON d.id = dc.dispatchID\n AND dc.countryID=b.countryID\n JOIN s_premium_dispatch_paymentmeans dp\n ON d.id = dp.dispatchID\n AND dp.paymentID=b.paymentID\n LEFT JOIN s_premium_holidays h\n ON h.date = CURDATE()\n LEFT JOIN s_premium_dispatch_holidays dh\n ON d.id=dh.dispatchID\n AND h.id=dh.holidayID\n\n LEFT JOIN (\n SELECT dc.dispatchID\n FROM s_order_basket b\n JOIN s_articles_categories_ro ac\n ON ac.articleID=b.articleID\n JOIN s_premium_dispatch_categories dc\n ON dc.categoryID=ac.categoryID\n WHERE b.modus=0\n AND b.sessionID='{$this->session->offsetGet('sessionId')}'\n GROUP BY dc.dispatchID\n ) as dk\n ON dk.dispatchID=d.id\n\n LEFT JOIN s_user u\n ON u.id=b.userID\n AND u.active=1\n\n LEFT JOIN s_user_billingaddress ub\n ON ub.userID=u.id\n\n LEFT JOIN s_user_shippingaddress us\n ON us.userID=u.id\n\n WHERE d.active=1\n AND (\n (bind_time_from IS NULL AND bind_time_to IS NULL)\n OR\n (IFNULL(bind_time_from,0) <= IFNULL(bind_time_to,86400) AND TIME_TO_SEC(DATE_FORMAT(NOW(),'%H:%i:00')) BETWEEN IFNULL(bind_time_from,0) AND IFNULL(bind_time_to,86400))\n OR\n (bind_time_from > bind_time_to AND TIME_TO_SEC(DATE_FORMAT(NOW(),'%H:%i:00')) NOT BETWEEN bind_time_to AND bind_time_from)\n )\n AND (\n (bind_weekday_from IS NULL AND bind_weekday_to IS NULL)\n OR\n (IFNULL(bind_weekday_from,1) <= IFNULL(bind_weekday_to,7) AND REPLACE(WEEKDAY(NOW()),0,6)+1 BETWEEN IFNULL(bind_weekday_from,1) AND IFNULL(bind_weekday_to,7))\n OR\n (bind_weekday_from > bind_weekday_to AND REPLACE(WEEKDAY(NOW()),0,6)+1 NOT BETWEEN bind_weekday_to AND bind_weekday_from)\n )\n AND (bind_weight_from IS NULL OR bind_weight_from <= b.weight)\n AND (bind_weight_to IS NULL OR bind_weight_to >= b.weight)\n AND (bind_price_from IS NULL OR bind_price_from <= b.amount)\n AND (bind_price_to IS NULL OR bind_price_to >= b.amount)\n AND (bind_instock=0 OR bind_instock IS NULL OR (bind_instock=1 AND b.instock) OR (bind_instock=2 AND b.stockmin))\n AND (bind_laststock=0 OR (bind_laststock=1 AND b.laststock))\n AND (bind_shippingfree=2 OR NOT b.shippingfree)\n AND dh.holidayID IS NULL\n AND (d.multishopID IS NULL OR d.multishopID=b.multishopID)\n AND (d.customergroupID IS NULL OR d.customergroupID=b.customergroupID)\n AND dk.dispatchID IS NULL\n AND d.type = {$type}\n AND (d.shippingfree IS NULL OR d.shippingfree > b.amount)\n {$sql_where}\n GROUP BY d.id\n ";
return $this->calculateDispatchSurcharge($basket, $this->db->fetchAll($sql));
}
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->db->fetchPairs($sql);
$sql_where = '';
foreach ($statements as $dispatchID => $statement) {
$sql_where .= "\n AND ( d.id!={$dispatchID} OR ({$statement}))\n ";
}
$sql_basket = array();
foreach ($basket as $key => $value) {
$sql_basket[] = $this->db->quote($value) . " as `{$key}`";
}
$sql_basket = implode(', ', $sql_basket);
$sql_add_join = "";
if (!empty($basket['paymentID'])) {
$sql_add_join .= "\n JOIN s_premium_dispatch_paymentmeans dp\n ON d.id = dp.dispatchID\n AND dp.paymentID={$basket['paymentID']}\n ";
}
if (!empty($basket['countryID'])) {
$sql_add_join .= "\n JOIN s_premium_dispatch_countries dc\n ON d.id = dc.dispatchID\n AND dc.countryID={$basket['countryID']}\n ";
}
$sql = "\n SELECT d.id, d.calculation\n FROM s_premium_dispatch d\n\n JOIN ( SELECT {$sql_basket} ) b\n\n {$sql_add_join}\n\n LEFT JOIN (\n SELECT dc.dispatchID\n FROM s_articles_categories_ro ac,\n s_premium_dispatch_categories dc\n WHERE ac.articleID={$basket['articleID']}\n AND dc.categoryID=ac.categoryID\n GROUP BY dc.dispatchID\n ) as dk\n ON dk.dispatchID=d.id\n\n LEFT JOIN s_user u\n ON u.id=b.userID\n AND u.active=1\n\n LEFT JOIN s_user_billingaddress ub\n ON ub.userID=u.id\n\n LEFT JOIN s_user_shippingaddress us\n ON us.userID=u.id\n\n WHERE d.active=1\n AND (bind_weight_from IS NULL OR bind_weight_from <= b.weight)\n AND (bind_weight_to IS NULL OR bind_weight_to >= b.weight)\n AND (bind_price_from IS NULL OR bind_price_from <= b.amount)\n AND (bind_price_to IS NULL OR bind_price_to >= b.amount)\n AND (bind_instock=0 OR bind_instock IS NULL OR (bind_instock=1 AND b.instock) OR (bind_instock=2 AND b.stockmin))\n AND (bind_laststock=0 OR (bind_laststock=1 AND b.laststock))\n AND (bind_shippingfree=2 OR NOT b.shippingfree)\n\n AND (d.multishopID IS NULL OR d.multishopID=b.multishopID)\n AND (d.customergroupID IS NULL OR d.customergroupID=b.customergroupID)\n AND dk.dispatchID IS NULL\n AND d.type = 2\n AND (d.shippingfree IS NULL OR d.shippingfree > b.amount)\n {$sql_where}\n GROUP BY d.id\n ";
$dispatches = $this->db->fetchAll($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 = "\n SELECT `value` , `factor`\n FROM `s_premium_shippingcosts`\n WHERE `from` <= {$from}\n AND `dispatchID` = {$dispatch['id']}\n ORDER BY `from` DESC\n LIMIT 1\n ";
$result = $this->db->fetchRow($sql);
if (!$result) {
continue;
}
$surcharge += $result['value'];
if (!empty($result['factor'])) {
$surcharge += $result['factor'] / 100 * $from;
}
}
}
return $surcharge;
}
/**
* @return array
*/
private function getOptions()
{
return $this->db->fetchPairs('SELECT `name`, `id` FROM s_filter_options');
}
/**
* @return array
*/
private function getCustomerGroup()
{
$groups = $this->db->fetchPairs('SELECT groupkey, taxinput FROM s_core_customergroups');
return $groups;
}
