/**
* Logs all database process to the internal log object.
* Iterates all queries of the query profiler and writes the query,
* the parameter and the elapsed seconds for the query into a new row of the log.
*/
public function logResults(Logger $log)
{
/** @var $profiler \Zend_Db_Profiler */
$profiler = $this->db->getProfiler();
$rows = array(array('time', 'count', 'sql', 'params'));
$counts = array(10000);
$total_time = 0;
$queryProfiles = $profiler->getQueryProfiles();
if (!$queryProfiles) {
return;
}
/** @var $query \Zend_Db_Profiler_Query */
foreach ($queryProfiles as $query) {
$id = md5($query->getQuery());
$total_time += $query->getElapsedSecs();
if (!isset($rows[$id])) {
$rows[$id] = array(number_format($query->getElapsedSecs(), 5, '.', ''), 1, $query->getQuery(), $query->getQueryParams());
$counts[$id] = $query->getElapsedSecs();
} else {
$rows[$id][1]++;
$counts[$id] += $query->getElapsedSecs();
$rows[$id][0] = number_format($counts[$id], 5, '.', '');
}
}
array_multisort($counts, SORT_NUMERIC, SORT_DESC, $rows);
$rows = array_values($rows);
$total_time = round($total_time, 5);
$total_count = $profiler->getTotalNumQueries();
$label = "Database Querys ({$total_count} @ {$total_time} sec)";
$table = array($label, $rows);
$log->table($table);
}
/**
* @param array $containers
* @param array $orderData
* @return array
*/
public function getPayPalInvoiceContentInfo(array $containers, array $orderData)
{
$footer = $containers['Paypal_Content_Info'];
$translationComp = new Shopware_Components_Translation();
$translation = $translationComp->read($orderData['_order']['language'], 'documents', 1);
$query = "SELECT * FROM s_core_documents_box WHERE id = ?";
$rawFooter = $this->db->fetchAssoc($query, array($footer['id']));
if (!empty($translation[1]["Paypal_Content_Info_Value"])) {
$rawFooter["value"] = $translation[1]["Paypal_Content_Info_Value"];
}
return $rawFooter[$footer['id']];
}
/**
* Create rewrite rules for articles
*
* @param string $lastUpdate
* @param int $limit
* @return string
*/
public function sCreateRewriteTableArticles($lastUpdate, $limit = 1000)
{
$routerArticleTemplate = $this->config->get('sROUTERARTICLETEMPLATE');
if (empty($routerArticleTemplate)) {
return $lastUpdate;
}
$this->db->query('UPDATE `s_articles` SET `changetime`= NOW() WHERE `changetime`=?', array('0000-00-00 00:00:00'));
$sql = $this->getSeoArticleQuery();
$sql = $this->db->limit($sql, $limit);
$result = $this->db->fetchAll($sql, array(Shopware()->Shop()->get('parentID'), Shopware()->Shop()->getId(), $lastUpdate));
$result = $this->mapArticleTranslationObjectData($result);
$result = Shopware()->Events()->filter('Shopware_Modules_RewriteTable_sCreateRewriteTableArticles_filterArticles', $result, array('shop' => Shopware()->Shop()->getId()));
foreach ($result as $row) {
$this->data->assign('sArticle', $row);
$path = $this->template->fetch('string:' . $routerArticleTemplate, $this->data);
$path = $this->sCleanupPath($path, false);
$orgPath = 'sViewport=detail&sArticle=' . $row['id'];
$this->sInsertUrl($orgPath, $path);
$lastUpdate = $row['changed'];
$lastId = $row['id'];
}
if (!empty($lastId)) {
$this->db->query('UPDATE s_articles
SET changetime = DATE_ADD(changetime, INTERVAL 1 SECOND)
WHERE changetime=?
AND id > ?', array($lastUpdate, $lastId));
}
return $lastUpdate;
}
/**
* Returns the leaf category to which the
* article belongs, inside the category subtree.
*
* @param int $articleId Id of the article to look for
* @param int $parentId Category subtree root id. If null, the shop category is used.
* @param null $shopId
* @return int Id of the leaf category, or 0 if none found.
*/
public function sGetCategoryIdByArticleId($articleId, $parentId = null, $shopId = null)
{
if ($parentId === null) {
$parentId = $this->baseId;
}
if ($shopId === null) {
$shopId = Shopware()->Shop()->getId();
}
$id = (int) $this->db->fetchOne('SELECT category_id
FROM s_articles_categories_seo
WHERE article_id = :articleId
AND shop_id = :shopId', array(':articleId' => $articleId, ':shopId' => $shopId));
if ($id) {
return $id;
}
$sql = '
SELECT STRAIGHT_JOIN
ac.categoryID as id
FROM s_articles_categories_ro ac FORCE INDEX (category_id_by_article_id)
INNER JOIN s_categories c
ON ac.categoryID = c.id
AND c.active = 1
AND c.path LIKE ?
LEFT JOIN s_categories c2
ON c2.parent = c.id
WHERE ac.articleID = ?
AND c2.id IS NULL
ORDER BY ac.id
';
$id = (int) $this->db->fetchOne($sql, array('%|' . $parentId . '|%', $articleId));
return $id;
}
/**
* If the user confirm the order, this function sends the buyer protection request
*
* @param \Enlight_Event_EventArgs $args
*/
public function onSaveOrder(\Enlight_Event_EventArgs $args)
{
/* @var \sOrder $orderSubject */
$orderSubject = $args->getSubject();
$shopId = $orderSubject->sSYSTEM->sSubShop['id'];
if ($this->isShopware50) {
$shopContext = Shopware()->Container()->get('shopware_storefront.context_service')->getShopContext();
$shopId = $shopContext->getShop()->getId();
}
$article = $this->isTsArticleInOrder($orderSubject);
if (!empty($article)) {
$config = $this->tsConfig->getTrustedShopBasicConfig($shopId);
$returnValue = $this->tsSoapApi->sendBuyerProtectionRequest($orderSubject, $article['ordernumber'], $shopId);
if (is_int($returnValue) && $returnValue > 0) {
/*
* Inserts the order to the trusted shops order table
* The Status will be updated in the cronjob onRunTSCheckOrderState
* Status Description:
* Status 0 Pending
* Status 1 Success
* Status 3 Error
*/
$sql = "INSERT INTO `s_plugin_swag_trusted_shops_excellence_orders`\n\t\t\t\t\t\t(`ordernumber`, `shop_id`, `ts_applicationId`, `status`)\n\t\t\t\t\t\tVALUES (?,?,?,0)";
$this->db->query($sql, array($orderSubject->sOrderNumber, $shopId, $returnValue));
$comment = $config['stateWaiting']['description'];
$status = $config['stateWaiting']['id'];
} else {
//failed
$comment = $config['stateError']['description'];
$status = $config['stateError']['id'];
}
$sql = "UPDATE s_order\n\t\t\t\t\tSET internalcomment = ?, status = ?\n\t\t\t\t\tWHERE ordernumber = ?";
$this->db->query($sql, array($comment, $status, $orderSubject->sOrderNumber));
}
}
/**
* Clear search cache
*/
public function clearSearchCache()
{
$sql = "SELECT `id` FROM `s_core_config_elements` WHERE `name` LIKE 'fuzzysearchlastupdate'";
$elementId = $this->db->fetchOne($sql);
$sql = 'DELETE FROM s_core_config_values WHERE element_id=?';
$this->db->query($sql, array($elementId));
}
/**
* Helper function which creates all variants for
* the passed groups with options.
*
* @param $groups
* @param null $numberPrefix
* @param array $data
* @return array
*/
private function generateVariants($groups, $numberPrefix = null, $data = array())
{
$options = array();
foreach ($groups as $group) {
$groupOptions = array();
foreach ($group['options'] as $option) {
$groupOptions[] = array('groupId' => $group['id'], 'option' => $option['name']);
}
$options[] = $groupOptions;
}
$combinations = $this->combinations($options);
$combinations = $this->cleanUpCombinations($combinations);
$variants = array();
$count = 1;
if (!$numberPrefix) {
$numberPrefix = 'Unit-Test-Variant-';
}
$this->db->executeQuery("DELETE FROM s_articles_details WHERE ordernumber LIKE ?", array($numberPrefix . '%'));
foreach ($combinations as $combination) {
$variantData = array_merge(array('number' => $numberPrefix . $count), $data);
$variant = $this->getVariantData($variantData);
$variant['configuratorOptions'] = $combination;
$variants[] = $variant;
$count++;
}
return $variants;
}
/**
* @param $groupId
* @return int
*/
private function getNextOptionPosition($groupId)
{
$sql = "SELECT `position`\n FROM `s_article_configurator_options`\n WHERE `group_id` = ?\n ORDER BY `position` DESC LIMIT 1";
$position = $this->db->fetchOne($sql, $groupId);
$position = $position ? ++$position : 1;
return $position;
}
/**
* @param $articleId
* @return string
*/
protected function countOfMainImages($articleId)
{
$count = $this->db->fetchOne('SELECT COUNT(main)
FROM s_articles_img
WHERE main = 1 AND articleID = ?', array($articleId));
return $count;
}
/**
* 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;
}
/**
* Returns database timestamp
*
* @param string $timestamp
* @return string
*/
private function toTimeStamp($timestamp)
{
if (empty($timestamp) && $timestamp !== 0) {
return 'null';
}
$date = new \Zend_Date($timestamp);
return $this->db->quote($date->toString('Y-m-d H:i:s', 'php'));
}
/**
* @return bool
*/
public function deleteAllCategories()
{
$sql = 'SELECT category_id
FROM s_core_shops';
$shopCategoriesIds = $this->db->fetchCol($sql);
//don't delete shop's categories
if (empty($shopCategoriesIds)) {
$sql = 'TRUNCATE s_categories';
} else {
$ids = 'id != ' . implode(' AND id != ', $shopCategoriesIds);
$sql = 'DELETE FROM s_categories
WHERE parent IS NOT NULL
AND ' . $ids;
}
if ($this->db->exec($sql) === false) {
return false;
}
if ($this->db->exec('TRUNCATE s_articles_categories') === false) {
return false;
}
if ($this->db->exec('TRUNCATE s_emarketing_banners') === false) {
return false;
}
$sql = 'SELECT MAX(category_id)
FROM s_core_shops';
$lastCategoryId = $this->db->fetchOne($sql);
$auto_increment = empty($lastCategoryId) ? 2 : $lastCategoryId + 1;
$sql = 'ALTER TABLE s_categories AUTO_INCREMENT = ' . $auto_increment;
if ($this->db->exec($sql) === false) {
return false;
}
return true;
}
/**
* Read a specific, static page (E.g. terms and conditions, etc.)
*
* @param int $staticId The page id
* @return array|false Page data, or false if none found by given id
*/
public function sGetStaticPage($staticId = null)
{
if (empty($staticId)) {
$staticId = (int) $this->front->Request()->getQuery('sCustom', $staticId);
}
if (empty($staticId)) {
return false;
}
// Load static page data from database
$staticPage = $this->db->fetchRow("SELECT * FROM s_cms_static WHERE id = ?", array($staticId));
if ($staticPage === false) {
return false;
}
/**
* Add support for sub pages
*/
if (!empty($staticPage['parentID'])) {
$sql = '
SELECT p.id, p.description, p.link, p.target, IF(p.id=?, 1, 0) as active, p.page_title
FROM s_cms_static p
WHERE p.parentID = ?
ORDER BY p.position
';
$staticPage['siblingPages'] = $this->db->fetchAll($sql, array($staticId, $staticPage['parentID']));
$sql = '
SELECT p.id, p.description, p.link, p.target, p.page_title
FROM s_cms_static p
WHERE p.id = ?
';
$staticPage['parent'] = $this->db->fetchRow($sql, array($staticPage['parentID']));
$staticPage['parent'] = $staticPage['parent'] ?: array();
} else {
$sql = '
SELECT p.id, p.description, p.link, p.target, p.page_title
FROM s_cms_static p
WHERE p.parentID = ?
ORDER BY p.position
';
$staticPage['subPages'] = $this->db->fetchAll($sql, array($staticId));
}
return $staticPage;
}
/**
* Returns the article data for the trusted shop article calculated by the current basket amount
*
* @param $amount
* @return array
*/
private function getTsArticleByAmount($amount)
{
if (empty($amount)) {
return array();
}
$currency = $this->shop->getCurrency()->toArray();
$currencyForSelect = '%' . $currency['currency'];
$sql = "SELECT a.id AS id,\n REPLACE(ROUND(ap.price * (100+t.tax) / 100, 2), '.', ',') AS grossFee,\n ROUND(ap.price, 2) AS netFee,\n aa.swag_trusted_range AS protectedAmountDecimal,\n aa.swag_trusted_duration AS protectionDurationInt,\n ad.ordernumber AS tsProductID\n\n FROM s_articles AS a\n INNER JOIN s_articles_attributes AS aa\n ON a.id = aa.articleID\n INNER JOIN s_articles_details AS ad\n ON a.id = ad.articleID\n INNER JOIN s_core_tax AS t\n ON t.id = a.taxID\n INNER JOIN s_articles_prices AS ap\n ON a.id = ap.articleID\n AND ap.pricegroup = 'EK'\n AND ap.from = 1\n\n WHERE aa.swag_trusted_range >= :amount AND ad.ordernumber LIKE :currency\n ORDER BY aa.swag_trusted_range ASC LIMIT 1;";
$nextProtectionItem = $this->db->fetchRow($sql, array('amount' => $amount, 'currency' => $currencyForSelect));
$nextProtectionItem['currency'] = $currency['currency'];
return $nextProtectionItem;
}
/**
* generates a new attribute for the articles to
* mark Trusted Shops articles
*/
private function generateAttribute()
{
$this->em->addAttribute('s_articles_attributes', 'swag', 'is_trusted_shops_article', 'tinyint(1)', true, 0);
$this->em->addAttribute('s_articles_attributes', 'swag', 'trusted_range', 'int(11)', true, null);
$this->em->addAttribute('s_articles_attributes', 'swag', 'trusted_duration', 'varchar(20)', true, null);
$this->em->generateAttributeModels(array('s_articles_attributes'));
try {
$sql = "CREATE INDEX swag_trusted_range ON s_articles_attributes (swag_trusted_range);";
$this->db->query($sql);
} catch (Exception $e) {
}
}
/**
* @param int $shippingId
* @param array $postData
* @return array
*/
private function filterShippingAttributeData($shippingId, $postData)
{
$data = $this->db->fetchRow("SELECT * FROM s_user_shippingaddress_attributes WHERE shippingID = ?", [$shippingId]);
unset($data['id']);
unset($data['shippingID']);
$allowedKeys = array_keys($data);
foreach ($postData as $key => $value) {
if (!in_array($key, $allowedKeys)) {
continue;
}
$data[$key] = $value;
}
return $data;
}
private function removeAclResource()
{
$sql = "SELECT id FROM s_core_acl_resources\n WHERE pluginID = ?;";
$resourceId = $this->db->fetchOne($sql, [$this->getId()]);
if (!$resourceId) {
return;
}
$resource = $this->em->getRepository(\Shopware\Models\User\Resource::class)->find($resourceId);
foreach ($resource->getPrivileges() as $privilege) {
$this->em->remove($privilege);
}
$this->em->remove($resource);
$this->em->flush();
}
/**
* @param array $record
* @return string
*/
private function getSupplierId($record)
{
$this->suppliers = $this->getSuppliers();
$name = $record['supplierName'];
$supplierId = $this->suppliers[$name];
//creates supplier if does not exists
if (!$supplierId) {
$data = array('name' => $name);
$builder = $this->dbalHelper->getQueryBuilderForEntity($data, 'Shopware\\Models\\Article\\Supplier', false);
$builder->execute();
$supplierId = $this->db->lastInsertId();
$this->suppliers[$name] = $supplierId;
}
return $supplierId;
}
/**
* Get article data for sAddArticle
*
* @param int $id Article ordernumber
* @return array|false Article data, or false if none found
*/
private function getArticleForAddArticle($id)
{
$sql = "\n SELECT s_articles.id AS articleID, name AS articleName, taxID,\n additionaltext, s_articles_details.shippingfree, laststock, instock,\n s_articles_details.id as articledetailsID, ordernumber\n FROM s_articles, s_articles_details\n WHERE s_articles_details.ordernumber = ?\n AND s_articles_details.articleID = s_articles.id\n AND s_articles.active = 1\n AND (\n SELECT articleID\n FROM s_articles_avoid_customergroups\n WHERE articleID = s_articles.id AND customergroupID = ?\n ) IS NULL\n ";
$article = $this->db->fetchRow($sql, array($id, $this->sSYSTEM->sUSERGROUPDATA["id"]));
$article = $this->eventManager->filter('Shopware_Modules_Basket_getArticleForAddArticle_FilterArticle', $article, array("id" => $id, 'subject' => $this, "partner" => $this->sSYSTEM->_SESSION["sPartner"]));
if (!$article) {
return false;
}
$name = $this->moduleManager->Articles()->sGetArticleNameByOrderNumber($article["ordernumber"], true);
if (!empty($name)) {
$article["articleName"] = $name["articleName"];
$article["additionaltext"] = $name["additionaltext"];
}
return $article;
}
/**
* Read data with translations from database
*
* @return array
*/
protected function readData()
{
$sql = "\n SELECT\n LOWER(REPLACE(e.name, '_', '')) as name,\n IFNULL(IFNULL(v2.value, v1.value), e.value) as value,\n LOWER(REPLACE(forms.name, '_', '')) as form\n FROM s_core_config_elements e\n LEFT JOIN s_core_config_values v1\n ON v1.element_id = e.id\n AND v1.shop_id = ?\n LEFT JOIN s_core_config_values v2\n ON v2.element_id = e.id\n AND v2.shop_id = ?\n LEFT JOIN s_core_config_forms forms\n ON forms.id = e.form_id\n ";
$data = $this->_db->fetchAll($sql, array(1, isset($this->_shop) ? $this->_shop->getId() : null));
$result = array();
foreach ($data as $row) {
$result[$row['name']] = unserialize($row['value']);
// Take namespaces (form names) into account
$result[$row['form'] . '::' . $row['name']] = unserialize($row['value']);
}
$result['version'] = Shopware::VERSION;
$result['revision'] = Shopware::REVISION;
$result['versiontext'] = Shopware::VERSION_TEXT;
return $result;
}
/**
* @param int $articleId
* @param int $categoryId
*/
public function assignArticlesToCategory($articleId, $categoryId)
{
$categoryId = intval($categoryId);
$articleId = intval($articleId);
if (empty($categoryId) || empty($articleId)) {
return;
}
$sql = "INSERT IGNORE INTO s_articles_categories (articleID, categoryID)\n SELECT {$articleId} as articleID, c.id as categoryID\n FROM s_categories c\n WHERE c.id IN ({$categoryId})";
if ($this->db->query($sql) === false) {
return;
}
$categoryDenormalization = Shopware()->Container()->get('categorydenormalization');
$categoryDenormalization->addAssignment($articleId, $categoryId);
$categoryDenormalization->disableTransactions();
}
/**
* Deletes all dummy customer entity
*/
private function deleteDummyCustomer(\Shopware\Models\Customer\Customer $customer)
{
$billingId = $this->db->fetchOne('SELECT id FROM s_user_billingaddress WHERE userID = ?', array($customer->getId()));
$shippingId = $this->db->fetchOne('SELECT id FROM s_user_shippingaddress WHERE userID = ?', array($customer->getId()));
if ($billingId) {
$this->db->delete('s_user_billingaddress_attributes', 'billingID = ' . $billingId);
$this->db->delete('s_user_billingaddress', 'id = ' . $billingId);
}
if ($shippingId) {
$this->db->delete('s_user_shippingaddress_attributes', 'shippingID = ' . $shippingId);
$this->db->delete('s_user_shippingaddress', 'id = ' . $shippingId);
}
$this->db->delete('s_core_payment_data', 'user_id = ' . $customer->getId());
$this->db->delete('s_user_attributes', 'userID = ' . $customer->getId());
$this->db->delete('s_user', 'id = ' . $customer->getId());
}
/**
* Read data with translations from database
*
* @return array
*/
protected function readData()
{
$sql = "\n SELECT\n LOWER(REPLACE(e.name, '_', '')) as name,\n COALESCE(currentShop.value, parentShop.value, fallbackShop.value, e.value) as value,\n LOWER(REPLACE(forms.name, '_', '')) as form,\n currentShop.value as currentShopval,\n parentShop.value as parentShopval,\n fallbackShop.value as fallbackShopval\n\n FROM s_core_config_elements e\n\n LEFT JOIN s_core_config_values currentShop\n ON currentShop.element_id = e.id\n AND currentShop.shop_id = :currentShopId\n\n LEFT JOIN s_core_config_values parentShop\n ON parentShop.element_id = e.id\n AND parentShop.shop_id = :parentShopId\n\n LEFT JOIN s_core_config_values fallbackShop\n ON fallbackShop.element_id = e.id\n AND fallbackShop.shop_id = :fallbackShopId\n\n LEFT JOIN s_core_config_forms forms\n ON forms.id = e.form_id\n ";
$data = $this->_db->fetchAll($sql, array('fallbackShopId' => 1, 'parentShopId' => isset($this->_shop) && $this->_shop->getMain() !== null ? $this->_shop->getMain()->getId() : 1, 'currentShopId' => isset($this->_shop) ? $this->_shop->getId() : null));
$result = array();
foreach ($data as $row) {
$value = !empty($row['value']) ? @unserialize($row['value']) : null;
$result[$row['name']] = $value;
// Take namespaces (form names) into account
$result[$row['form'] . '::' . $row['name']] = $value;
}
$result['version'] = Shopware::VERSION;
$result['revision'] = Shopware::REVISION;
$result['versiontext'] = Shopware::VERSION_TEXT;
return $result;
}
/**
* Assigns the next CustomerNumber from s_order_number
* to given $userId and updates s_order_number
* in an atomic operation.
*
* @param int $userId
*/
private function assignCustomerNumber($userId)
{
$sql = <<<SQL
UPDATE
s_order_number,
s_user_billingaddress
SET
s_order_number.number = s_order_number.number+1,
s_user_billingaddress.customernumber = s_order_number.number
WHERE
s_order_number.name = 'user'
AND
s_user_billingaddress.userID = ?
SQL;
$this->db->query($sql, array($userId));
}
/**
* Returns all filterable properties depending on the given articles
*
* @param array $articles
* @return array
*/
public function sGetComparisonProperties($articles)
{
$shopContext = $this->contextService->getShopContext();
$properties = [];
foreach ($articles as $article) {
//get all properties in the right order
$sql = "SELECT\n options.id,\n options.name,\n translation.objectdata as translation,\n translationFallback.objectdata as translationFallback\n\n FROM s_filter_options as options\n LEFT JOIN s_filter_relations as relations ON relations.optionId = options.id\n LEFT JOIN s_filter as filter ON filter.id = relations.groupID\n\n LEFT JOIN s_core_translations AS translation\n ON translation.objecttype='propertyoption'\n AND translation.objectkey=options.id\n AND translation.objectlanguage=:shopId\n\n LEFT JOIN s_core_translations AS translationFallback\n ON translationFallback.objecttype='propertyoption'\n AND translationFallback.objectkey=options.id\n AND translationFallback.objectlanguage=:fallbackId\n\n WHERE relations.groupID = :groupId\n AND filter.comparable = 1\n ORDER BY relations.position ASC";
$articleProperties = $this->db->fetchAll($sql, ['groupId' => $article["filtergroupID"], 'shopId' => $shopContext->getShop()->getId(), 'fallbackId' => $shopContext->getShop()->getFallbackId()]);
foreach ($articleProperties as $articleProperty) {
if (!in_array($articleProperty['id'], array_keys($properties))) {
//the key is not part of the array so add it to the end
$properties[$articleProperty['id']] = $this->extractPropertyTranslation($articleProperty);
}
}
}
return $properties;
}
/**
* Get article data for sAddArticle
*
* @param int $id Article ordernumber
* @return array|false Article data, or false if none found
*/
private function getArticleForAddArticle($id)
{
$sql = "\n SELECT s_articles.id AS articleID, s_articles.main_detail_id, name AS articleName, taxID,\n additionaltext, s_articles_details.shippingfree, laststock, instock,\n s_articles_details.id as articledetailsID, ordernumber,\n s_articles.configurator_set_id\n FROM s_articles, s_articles_details\n WHERE s_articles_details.ordernumber = ?\n AND s_articles_details.articleID = s_articles.id\n AND s_articles.active = 1\n AND (\n SELECT articleID\n FROM s_articles_avoid_customergroups\n WHERE articleID = s_articles.id AND customergroupID = ?\n ) IS NULL\n ";
$article = $this->db->fetchRow($sql, array($id, $this->sSYSTEM->sUSERGROUPDATA["id"]));
$article = $this->eventManager->filter('Shopware_Modules_Basket_getArticleForAddArticle_FilterArticle', $article, array("id" => $id, 'subject' => $this, "partner" => $this->sSYSTEM->_SESSION["sPartner"]));
if (!$article) {
return false;
}
$article = $this->moduleManager->Articles()->sGetTranslation($article, $article['articleID'], "article");
$article = $this->moduleManager->Articles()->sGetTranslation($article, $article['articledetailsID'], "variant");
if ($article['configurator_set_id'] > 0) {
$context = $this->contextService->getProductContext();
$product = Shopware()->Container()->get('shopware_storefront.list_product_service')->get($article['ordernumber'], $context);
$product = $this->additionalTextService->buildAdditionalText($product, $context);
$article['additionaltext'] = $product->getAdditional();
}
return $article;
}
/**
* Gets related pages for the given page
*
* @param array $staticPage
* @param int|null $shopId
* @return mixed
*/
private function getRelatedForPage($staticPage, $shopId = null)
{
$andWhere = '';
$params = array('pageId' => $staticPage['id']);
if ($shopId) {
$andWhere .= ' AND (p.shop_ids IS NULL OR p.shop_ids LIKE :shopId)';
$params['shopId'] = '%|' . $shopId . '|%';
}
$sql = '
SELECT p.id, p.description, p.link, p.target, p.page_title
FROM s_cms_static p
WHERE p.parentID = :pageId
' . $andWhere . '
ORDER BY p.position
';
$staticPage['subPages'] = $this->db->fetchAll($sql, $params);
return $staticPage;
}
/**
* Helper method for sAdmin::sGetPremiumShippingcosts()
* Calculates payment mean surcharge
*
* @param $country
* @param $payment
* @param $currencyFactor
* @param $dispatch
* @param $discount_tax
*/
private function handlePaymentMeanSurcharge($country, $payment, $currencyFactor, $dispatch, $discount_tax)
{
$surcharge_name = $this->config->get('sPAYMENTSURCHARGEABSOLUTE', 'Zuschlag für Zahlungsart');
$surcharge_ordernumber = $this->config->get('sPAYMENTSURCHARGEABSOLUTENUMBER', 'PAYMENTSURCHARGEABSOLUTENUMBER');
$percent_ordernumber = $this->config->get('sPAYMENTSURCHARGENUMBER', "PAYMENTSURCHARGE");
// Country surcharge
if (!empty($payment['country_surcharge'][$country['countryiso']])) {
$payment['surcharge'] += $payment['country_surcharge'][$country['countryiso']];
}
$payment['surcharge'] = round($payment['surcharge'] * $currencyFactor, 2);
// Fixed surcharge
if (!empty($payment['surcharge']) && (empty($dispatch) || $dispatch['surcharge_calculation'] == 3)) {
$surcharge = round($payment['surcharge'], 2);
$payment['surcharge'] = 0;
if (empty($this->sSYSTEM->sUSERGROUPDATA["tax"]) && !empty($this->sSYSTEM->sUSERGROUPDATA["id"])) {
$surcharge_net = $surcharge;
//$tax_rate = 0;
} else {
$surcharge_net = round($surcharge / (100 + $discount_tax) * 100, 2);
}
$tax_rate = $discount_tax;
$this->db->insert('s_order_basket', array('sessionID' => $this->session->offsetGet('sessionId'), 'articlename' => $surcharge_name, 'articleID' => 0, 'ordernumber' => $surcharge_ordernumber, 'quantity' => 1, 'price' => $surcharge, 'netprice' => $surcharge_net, 'tax_rate' => $tax_rate, 'datum' => new Zend_Date(), 'modus' => 4, 'currencyFactor' => $currencyFactor));
}
// Percentage surcharge
if (!empty($payment['debit_percent']) && (empty($dispatch) || $dispatch['surcharge_calculation'] != 2)) {
$amount = $this->db->fetchOne('SELECT SUM(quantity*price) as amount
FROM s_order_basket
WHERE sessionID = ? GROUP BY sessionID', array($this->session->offsetGet('sessionId')));
$percent = round($amount / 100 * $payment['debit_percent'], 2);
if ($percent > 0) {
$percent_name = $this->config->get('sPAYMENTSURCHARGEADD');
} else {
$percent_name = $this->config->get('sPAYMENTSURCHARGEDEV');
}
if (empty($this->sSYSTEM->sUSERGROUPDATA["tax"]) && !empty($this->sSYSTEM->sUSERGROUPDATA["id"])) {
$percent_net = $percent;
} else {
$percent_net = round($percent / (100 + $discount_tax) * 100, 2);
}
$tax_rate = $discount_tax;
$this->db->insert('s_order_basket', array('sessionID' => $this->session->offsetGet('sessionId'), 'articlename' => $percent_name, 'articleID' => 0, 'ordernumber' => $percent_ordernumber, 'quantity' => 1, 'price' => $percent, 'netprice' => $percent_net, 'tax_rate' => $tax_rate, 'datum' => new Zend_Date(), 'modus' => 4, 'currencyFactor' => $currencyFactor));
}
return $payment;
}
/**
* Create CMS rewrite rules
* Used in multiple locations
*
* @param int $offset
* @param int $limit
*/
public function sCreateRewriteTableContent($offset = null, $limit = null)
{
$sql = "SELECT id, description as name FROM `s_emarketing_promotion_main`";
if ($limit !== null) {
$sql = $this->db->limit($sql, $limit, $offset);
}
$eMarketingPromotion = $this->db->fetchAll($sql);
foreach ($eMarketingPromotion as $row) {
$org_path = 'sViewport=campaign&sCampaign=' . $row['id'];
$path = $this->sCleanupPath($row['name']);
$this->sInsertUrl($org_path, $path);
}
$sql = "SELECT id, name, ticket_typeID FROM `s_cms_support`";
if ($limit !== null) {
$sql = $this->db->limit($sql, $limit, $offset);
}
$cmsSupport = $this->db->fetchAll($sql);
foreach ($cmsSupport as $row) {
$org_path = 'sViewport=ticket&sFid=' . $row['id'];
$path = $this->sCleanupPath($row['name']);
$this->sInsertUrl($org_path, $path);
}
$sql = "SELECT id, description as name FROM `s_cms_static` WHERE link=''";
if ($limit !== null) {
$sql = $this->db->limit($sql, $limit, $offset);
}
$cmsStatic = $this->db->fetchAll($sql);
foreach ($cmsStatic as $row) {
$org_path = 'sViewport=custom&sCustom=' . $row['id'];
$path = $this->sCleanupPath($row['name']);
$this->sInsertUrl($org_path, $path);
}
$sql = "SELECT id, description as name FROM `s_cms_groups`";
if ($limit !== null) {
$sql = $this->db->limit($sql, $limit, $offset);
}
$cmsGroups = $this->db->fetchAll($sql);
foreach ($cmsGroups as $row) {
$org_path = 'sViewport=content&sContent=' . $row['id'];
$path = $this->sCleanupPath($row['name']);
$this->sInsertUrl($org_path, $path);
}
}
/**
* @param string $tableName
* @param string $columnName
* @param string $prefixField
* @param string $prefixSelect
* @return array
*/
public function getAttributesFieldsByTableName($tableName, $columnName, $prefixField, $prefixSelect)
{
$stmt = $this->db->query("SHOW COLUMNS FROM {$tableName}");
$columns = $stmt->fetchAll();
$columnNames = [];
foreach ($columns as $column) {
if ($column['Field'] !== 'id' && $column['Field'] != $columnName) {
$columnNames[] = $column['Field'];
}
}
$attributesSelect = [];
foreach ($columnNames as $attribute) {
//underscore to camel case
//exmaple: underscore_to_camel_case -> underscoreToCamelCase
$attribute = str_replace(' ', '', ucwords(str_replace('_', ' ', $attribute)));
$attributesSelect[] = sprintf('%s.%s as %s%s', $prefixField, lcfirst($attribute), $prefixSelect, $attribute);
}
return $attributesSelect;
}
