/**
* @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;
}
/**
* This function controls the buyer protection item in the basket.
*
* @param \Enlight_Controller_Action $controller
* @param \Enlight_Controller_Request_RequestHttp $request
* @param $basketAmount
* @return void
*/
private function controlBasketTsArticle($controller, $request, $basketAmount)
{
//get total basket amount
$amount = $this->getAmount($controller->getShippingCosts(), $basketAmount);
$basketArticle = $this->isTsArticleInBasket();
//Always use the brutto-value
if ($controller->View()->sAmountWithTax) {
$amount = $controller->View()->sAmountWithTax;
}
if (empty($basketArticle)) {
return;
}
$sql = "SELECT COUNT(id)\n\t\t\t\tFROM s_order_basket\n\t\t\t\tWHERE sessionID = ?\n\t\t\t\t\tAND modus = 0";
$articleAmount = $this->db->fetchOne($sql, array($this->sessionId));
if ($articleAmount > 1) {
if ($amount > 0) {
//get trusted shop article data
$toAddArticle = $this->getTsArticleByAmount($amount);
if ($toAddArticle['tsProductID'] == $basketArticle['ordernumber']) {
return;
}
}
}
$sql = "DELETE FROM s_order_basket\n\t\t\t\tWHERE id = ?\n\t\t\t\t\tAND sessionID = ?";
$this->db->query($sql, array($basketArticle['id'], $this->sessionId));
$controller->View()->sTsArticleRemoved = true;
$controller->forward($request->getActionName());
}
/**
* @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;
}
/**
* Insert articles in comparision chart
* @param int $articleId s_articles.id
* @throws Enlight_Exception
* @return bool true/false
*/
public function sAddComparison($articleId)
{
$articleId = (int) $articleId;
if (!$articleId) {
return null;
}
// Check if this article is already noted
$checkForArticle = $this->db->fetchRow("SELECT id FROM s_order_comparisons WHERE sessionID=? AND articleID=?", [$this->session->offsetGet('sessionId'), $articleId]);
// Check if max. numbers of articles for one comparison-session is reached
$checkNumberArticles = $this->db->fetchRow("SELECT COUNT(id) AS countArticles FROM s_order_comparisons WHERE sessionID=?", [$this->session->offsetGet('sessionId')]);
if ($checkNumberArticles["countArticles"] >= $this->config->offsetGet("sMAXCOMPARISONS")) {
return "max_reached";
}
if (!$checkForArticle["id"]) {
$articleName = $this->db->fetchOne("SELECT s_articles.name AS articleName FROM s_articles WHERE id = ?", [$articleId]);
if (!$articleName) {
return false;
}
$sql = "\n INSERT INTO s_order_comparisons (sessionID, userID, articlename, articleID, datum)\n VALUES (?,?,?,?,now())\n ";
$queryNewPrice = $this->db->executeUpdate($sql, [$this->session->offsetGet('sessionId'), empty($this->session["sUserId"]) ? 0 : $this->session["sUserId"], $articleName, $articleId]);
if (!$queryNewPrice) {
throw new Enlight_Exception("sArticles##sAddComparison##01: Error in SQL-query");
}
}
return true;
}
/**
* 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));
}
/**
* 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;
}
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();
}
/**
* 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());
}
/**
* Delete article method
*
* @param int $articleID
* @return bool
*/
private function deleteArticle($articleID)
{
$article['articleID'] = intval($articleID);
$article['kind'] = 1;
$sql = "SELECT id\n FROM s_articles_details\n WHERE articleID = {$article['articleID']}";
$article['articledetailsID'] = $this->db->fetchOne($sql);
$sql = "SELECT ordernumber\n FROM s_articles_details\n WHERE articleID = {$article['articleID']}";
$article['ordernumber'] = $this->db->fetchOne($sql);
if (empty($article['articledetailsID'])) {
return false;
}
$this->deleteImages($article['articleID']);
$this->deleteDownloads($article['articleID']);
$this->deleteArticleLinks($article);
$this->deletePermissions($article['articleID']);
// delete products
$sql = 'DELETE FROM s_articles
WHERE id = ' . $article['articleID'];
$this->db->query($sql);
// delete esd products
$sql = 'SELECT id
FROM s_articles_esd
WHERE articleID = ' . $article['articleID'];
$esdIDs = $this->db->fetchCol($sql);
if (!empty($esdIDs)) {
$sql = 'DELETE FROM s_articles_esd_serials
WHERE esdID = ' . implode(' OR esdID=', $esdIDs);
$this->db->query($sql);
}
$tables = ['s_articles_details', 's_articles_attributes', 's_articles_esd', 's_articles_prices', 's_articles_relationships', 's_articles_similar', 's_articles_vote', 's_articles_categories', 's_articles_translations', 's_export_articles', 's_emarketing_lastarticles', 's_articles_avoid_customergroups'];
foreach ($tables as $table) {
$sql = "DELETE FROM {$table}\n WHERE articleID = {$article['articleID']}";
$this->db->query($sql);
}
$this->deleteTranslation(['article', 'configuratoroption', 'configuratorgroup', 'accessoryoption', 'accessorygroup', 'propertyvalue'], $article['articleID']);
$sql = 'DELETE FROM s_core_rewrite_urls
WHERE org_path = ?';
$this->db->query($sql, ['sViewport=detail&sArticle=' . $article['articleID']]);
$tables = ['s_articles_similar' => 'relatedarticle', 's_articles_relationships' => 'relatedarticle'];
foreach ($tables as $table => $row) {
$sql = "DELETE FROM {$table} WHERE {$row} = ?";
$this->db->query($sql, [$article['ordernumber']]);
}
$this->deleteTranslation('objectkey', $article['articledetailsID']);
return true;
}
/**
* 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->snippetManager->getNamespace('backend/static/discounts_surcharges')->get('payment_surcharge_absolute', 'Surcharge for payment');
$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->snippetManager->getNamespace('backend/static/discounts_surcharges')->get('payment_surcharge_add');
} else {
$percent_name = $this->snippetManager->getNamespace('backend/static/discounts_surcharges')->get('payment_surcharge_dev');
}
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;
}
/**
* @param $voucherDetails
* @return array
*/
private function calculateVoucherValues($voucherDetails)
{
$taxRate = 0;
if (!$this->sSYSTEM->sUSERGROUPDATA["tax"] && $this->sSYSTEM->sUSERGROUPDATA["id"] || $voucherDetails["taxconfig"] == "none") {
// if net customer group - calculate without tax
$tax = $voucherDetails["value"] * -1;
if ($voucherDetails["taxconfig"] == "default" || empty($voucherDetails["taxconfig"])) {
$taxRate = $this->config->get('sVOUCHERTAX');
} elseif ($voucherDetails["taxconfig"] == "auto") {
$taxRate = $this->getMaxTax();
} elseif (intval($voucherDetails["taxconfig"])) {
$temporaryTax = $voucherDetails["taxconfig"];
$getTaxRate = $this->db->fetchOne('SELECT tax FROM s_core_tax WHERE id = ?', array($temporaryTax));
$taxRate = $getTaxRate;
}
} else {
if ($voucherDetails["taxconfig"] == "default" || empty($voucherDetails["taxconfig"])) {
$tax = round($voucherDetails["value"] / (100 + $this->config->get('sVOUCHERTAX')) * 100, 3) * -1;
$taxRate = $this->config->get('sVOUCHERTAX');
// Pre 3.5.4 behaviour
} elseif ($voucherDetails["taxconfig"] == "auto") {
// Check max. used tax-rate from basket
$tax = $this->getMaxTax();
$taxRate = $tax;
$tax = round($voucherDetails["value"] / (100 + $tax) * 100, 3) * -1;
} elseif (intval($voucherDetails["taxconfig"])) {
// Fix defined tax
$temporaryTax = $voucherDetails["taxconfig"];
$getTaxRate = $this->db->fetchOne('SELECT tax FROM s_core_tax WHERE id = ?', array($temporaryTax));
$taxRate = $getTaxRate;
$tax = round($voucherDetails["value"] / (100 + intval($getTaxRate)) * 100, 3) * -1;
} else {
// No tax
$tax = $voucherDetails["value"] * -1;
}
}
$voucherDetails["value"] = $voucherDetails["value"] * -1;
if ($voucherDetails["shippingfree"]) {
$freeShipping = "1";
} else {
$freeShipping = "0";
}
return array($taxRate, $tax, $voucherDetails, $freeShipping);
}
/**
* This function creates the database table for the buyer protection articles
*
* @return void
*/
private function createDatabaseTables()
{
$this->generateAttribute();
//creates new database table for the trusted shops orders this table should not be deleted on uninstall
$sql = "CREATE TABLE IF NOT EXISTS `s_plugin_swag_trusted_shops_excellence_orders` (\n\t\t\t\t\t`id` INT(11) NOT NULL AUTO_INCREMENT,\n\t\t\t\t\t`ordernumber` VARCHAR(30) NOT NULL,\n\t\t\t\t\t`shop_id` INT(11) NOT NULL,\n\t\t\t\t\t`ts_applicationId` VARCHAR(30) NOT NULL,\n\t\t\t\t\t`status` INT(1) DEFAULT NULL,\n\t\t\t\t\tPRIMARY KEY (`id`)\n\t\t\t\t) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;";
$this->db->query($sql);
$sql = "SELECT MAX(id)\n\t\t\t\tFROM s_core_states";
$id = $this->db->fetchOne($sql);
$states = array(array('description' => 'TS - Antrag in Bearbeitung', 'position' => 100, 'group' => 'state', 'mail' => 0), array('description' => 'TS - Antrag erfolgreich', 'position' => 100, 'group' => 'state', 'mail' => 0), array('description' => 'TS - Antrag fehlgeschlagen', 'position' => 100, 'group' => 'state', 'mail' => 0));
$selectSQL = "SELECT id FROM s_core_states WHERE description = ?";
$sql = "INSERT INTO s_core_states (`id`, `description`, `position`, `group`, `mail`) VALUES (?,?,?,?,?)";
$i = 1;
foreach ($states as $state) {
$record = $this->db->fetchOne($selectSQL, array($state['description']));
if (!$record) {
$recordId = $id + $i;
$this->db->query($sql, array($recordId, $state['description'], $state['position'], $state['group'], $state['mail']));
$i++;
}
}
}
/**
* A helper function that returns the localeId of a locale string
* @param $locale
* @return int|null|string
*/
private function getLocaleId($locale)
{
$localeId = null;
switch (strlen($locale)) {
case 2:
//sometimes only en, de, or es will be transmitted
//Select the first matching language from the database
$localeId = $this->db->fetchOne("SELECT `id` FROM `s_core_locales` WHERE `locale` LIKE :locale ORDER BY `id` ASC", array('locale' => $locale . '%'));
break;
case 5:
// the standard format e.g en_GB
$localeId = $this->db->fetchOne("SELECT `id` FROM `s_core_locales` WHERE `locale`=:locale", array('locale' => $locale));
break;
}
if (is_null($localeId)) {
$fallbackLocaleCode = $this->pluginBootstrap->get("config")->fallbackLanguage;
$localeId = $this->getLocaleId($fallbackLocaleCode);
}
//returns 2 (en_GB) if 1.) the browser language was not found and 2.) the fallback language was not found
return is_null($localeId) ? 2 : $localeId;
}
/**
* @param array $customer
* @return array
*/
private function newsletterSubscribe(array $customer)
{
if (!isset($customer['newsletter'])) {
return $customer;
}
if (empty($customer['newsletter'])) {
$sql = 'DELETE FROM S_CAMPAIGNS_MAILADDRESSES
WHERE EMAIL = ' . $customer['email'];
$this->db->query($sql);
} else {
$customer['newslettergroupID'] = $this->getNewsletterGroupId($customer['newslettergroupID']);
$sql = 'SELECT ID
FROM S_CAMPAIGNS_MAILADDRESSES
WHERE EMAIL = ' . $customer['email'];
$result = $this->db->fetchOne($sql);
if (empty($result)) {
$sql = "INSERT INTO s_campaigns_mailaddresses (customer, groupID, email)\n VALUES (1, {$customer['newslettergroupID']}, {$customer['email']});";
$this->db->query($sql);
}
}
return $customer;
}
/**
* @param array $category
* @return bool|int
*/
public function import(array $category)
{
$category = $this->prepareCategoryData($category);
// Try to find an existing category by name and parent
$model = null;
if (isset($category['parent']) && isset($category['name'])) {
$model = $this->repository->findOneBy(['parent' => $category['parent'], 'name' => $category['name']]);
}
if (!$model instanceof Category) {
$model = new Category();
}
$parentModel = null;
if (isset($category['parent'])) {
$parentModel = $this->repository->find((int) $category['parent']);
if (!$parentModel instanceof Category) {
$this->logger->error("Parent category {$category['parent']} not found!");
return false;
}
}
$model->fromArray($category);
$model->setParent($parentModel);
$this->em->persist($model);
$this->em->flush();
// Set category attributes
$attributes = $this->prepareCategoryAttributesData($category);
unset($category);
$categoryId = $model->getId();
if (!empty($attributes)) {
$attributeID = $this->db->fetchOne("SELECT id FROM s_categories_attributes WHERE categoryID = ?", [$categoryId]);
if ($attributeID === false) {
$attributes['categoryID'] = $categoryId;
$this->db->insert('s_categories_attributes', $attributes);
} else {
$this->db->update('s_categories_attributes', $attributes, ['categoryID = ?' => $categoryId]);
}
}
return $categoryId;
}
/**
* @param $number
*/
public function removeArticle($number)
{
$articleId = $this->db->fetchOne("SELECT articleID FROM s_articles_details WHERE ordernumber = ?", array($number));
if (!$articleId) {
return;
}
$article = $this->entityManager->find('Shopware\\Models\\Article\\Article', $articleId);
$this->entityManager->remove($article);
$this->entityManager->flush();
$this->entityManager->clear();
$detailIds = $this->db->fetchCol("SELECT id FROM s_articles_details WHERE articleID = ?", array($articleId));
if (empty($detailIds)) {
return;
}
foreach ($detailIds as $id) {
$detail = $this->entityManager->find('Shopware\\Models\\Article\\Detail', $id);
if ($detail) {
$this->entityManager->remove($detail);
$this->entityManager->flush();
}
}
$this->entityManager->clear();
}
/**
* @param int $subShopId
* @return string
*/
private function getMainShopDefaultPaymentId($subShopId)
{
$query = "SELECT `value`.value\n FROM s_core_config_elements AS element\n JOIN s_core_config_values AS `value` ON `value`.element_id = element.id\n WHERE `value`.shop_id = (SELECT main_id FROM s_core_shops WHERE id = ?)\n AND element.name = 'defaultpayment'";
return $this->db->fetchOne($query, array($subShopId));
}
/**
* Checks whether the category is a leaf
*
* @param int $categoryId
* @return bool
*/
protected function isLeaf($categoryId)
{
$isLeaf = $this->db->fetchOne("SELECT id FROM s_categories WHERE parent = ?", [$categoryId]);
return is_numeric($isLeaf);
}
/**
* Helper function which returns the current order status of the passed order
* id.
*
* @param $orderId
* @return string
*/
private function getOrderStatus($orderId)
{
return $this->db->fetchOne('SELECT status FROM s_order WHERE id= :orderId;', array(':orderId' => $orderId));
}
/**
* @param int $articleId
* @return int
*/
private function getPosition($articleId)
{
$sql = "SELECT MAX(position) FROM s_articles_img WHERE articleID = ?;";
$result = $this->db->fetchOne($sql, $articleId);
return isset($result) ? (int) $result + 1 : 1;
}
/**
* Checks whether this relation exists.
*
* @param $relationId
* @param $articleId
* @return bool
*/
protected function isRelationExists($relationId, $articleId)
{
$isRelationExists = $this->db->fetchOne("SELECT id FROM {$this->table} WHERE relatedarticle = ? AND articleID = ?", [$relationId, $articleId]);
return is_numeric($isRelationExists);
}
/**
* @param int $articleDetailId
* @return string
*/
protected function getArticleOrderNumber($articleDetailId)
{
$sql = 'SELECT ordernumber FROM s_articles_details WHERE id = ?';
$orderNumber = $this->db->fetchOne($sql, [$articleDetailId]);
return $orderNumber;
}
/**
* @param $articleId
* @return string
*/
private function getOrdernumberByArticleId($articleId)
{
$number = $this->db->fetchOne("SELECT ordernumber\n FROM s_articles_details\n INNER JOIN s_articles\n ON s_articles.main_detail_id = s_articles_details.id\n WHERE articleID = ?", [$articleId]);
return $number;
}
