/**
* 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'));
}
/**
* 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));
}
/**
* Finaly save order and send order confirmation to customer
* @access public
*/
public function sSaveOrder()
{
$this->sComment = stripslashes($this->sComment);
$this->sComment = stripcslashes($this->sComment);
$this->sShippingData["AmountNumeric"] = $this->sShippingData["AmountNumeric"] ? $this->sShippingData["AmountNumeric"] : "0";
if ($this->isTransactionExist($this->bookingId)) {
return false;
}
// Insert basic-data of the order
$orderNumber = $this->sGetOrderNumber();
$this->sOrderNumber = $orderNumber;
if (!$this->sShippingcostsNumeric) {
$this->sShippingcostsNumeric = "0";
}
if (!$this->sBasketData["AmountWithTaxNumeric"]) {
$this->sBasketData["AmountWithTaxNumeric"] = $this->sBasketData["AmountNumeric"];
}
if ($this->isTaxFree($this->sSYSTEM->sUSERGROUPDATA["tax"], $this->sSYSTEM->sUSERGROUPDATA["id"])) {
$net = "1";
} else {
$net = "0";
}
if ($this->dispatchId) {
$dispatchId = $this->dispatchId;
} else {
$dispatchId = "0";
}
$this->sBasketData["AmountNetNumeric"] = round($this->sBasketData["AmountNetNumeric"], 2);
if (empty($this->sSYSTEM->sCurrency["currency"])) {
$this->sSYSTEM->sCurrency["currency"] = "EUR";
}
if (empty($this->sSYSTEM->sCurrency["factor"])) {
$this->sSYSTEM->sCurrency["factor"] = "1";
}
$shop = Shopware()->Shop();
$mainShop = $shop->getMain() !== null ? $shop->getMain() : $shop;
$taxfree = "0";
if (!empty($this->sNet)) {
// Complete net delivery
$net = "1";
$this->sBasketData["AmountWithTaxNumeric"] = $this->sBasketData["AmountNetNumeric"];
$this->sShippingcostsNumeric = $this->sShippingcostsNumericNet;
$taxfree = "1";
}
$partner = $this->getPartnerCode($this->sUserData["additional"]["user"]["affiliate"]);
$orderParams = array('ordernumber' => $orderNumber, 'userID' => $this->sUserData["additional"]["user"]["id"], 'invoice_amount' => $this->sBasketData["AmountWithTaxNumeric"], 'invoice_amount_net' => $this->sBasketData["AmountNetNumeric"], 'invoice_shipping' => floatval($this->sShippingcostsNumeric), 'invoice_shipping_net' => floatval($this->sShippingcostsNumericNet), 'ordertime' => new Zend_Db_Expr('NOW()'), 'status' => 0, 'cleared' => 17, 'paymentID' => $this->sUserData["additional"]["user"]["paymentID"], 'transactionID' => (string) $this->bookingId, 'customercomment' => $this->sComment, 'net' => $net, 'taxfree' => $taxfree, 'partnerID' => (string) $partner, 'temporaryID' => (string) $this->uniqueID, 'referer' => (string) $this->getSession()->offsetGet('sReferer'), 'language' => $shop->getId(), 'dispatchID' => $dispatchId, 'currency' => $this->sSYSTEM->sCurrency["currency"], 'currencyFactor' => $this->sSYSTEM->sCurrency["factor"], 'subshopID' => $mainShop->getId(), 'remote_addr' => (string) $_SERVER['REMOTE_ADDR'], 'deviceType' => $this->deviceType);
$orderParams = $this->eventManager->filter('Shopware_Modules_Order_SaveOrder_FilterParams', $orderParams, array('subject' => $this));
try {
$this->db->beginTransaction();
$affectedRows = $this->db->insert('s_order', $orderParams);
$orderID = $this->db->lastInsertId();
$this->db->commit();
} catch (Exception $e) {
$this->db->rollBack();
throw new Enlight_Exception("Shopware Order Fatal-Error {$_SERVER["HTTP_HOST"]} :" . $e->getMessage(), 0, $e);
}
if (!$affectedRows || !$orderID) {
throw new Enlight_Exception("Shopware Order Fatal-Error {$_SERVER["HTTP_HOST"]} : No rows affected or no order id created.", 0);
}
try {
$paymentData = Shopware()->Modules()->Admin()->sGetPaymentMeanById($this->sUserData["additional"]["user"]["paymentID"], Shopware()->Modules()->Admin()->sGetUserData());
$paymentClass = Shopware()->Modules()->Admin()->sInitiatePaymentClass($paymentData);
if ($paymentClass instanceof \ShopwarePlugin\PaymentMethods\Components\BasePaymentMethod) {
$paymentClass->createPaymentInstance($orderID, $this->sUserData["additional"]["user"]["id"], $this->sUserData["additional"]["user"]["paymentID"]);
}
} catch (\Exception $e) {
//Payment method code failure
}
$attributeSql = "INSERT INTO s_order_attributes (orderID, attribute1, attribute2, attribute3, attribute4, attribute5, attribute6)\n VALUES (\n " . $orderID . ",\n " . $this->db->quote((string) $this->o_attr_1) . ",\n " . $this->db->quote((string) $this->o_attr_2) . ",\n " . $this->db->quote((string) $this->o_attr_3) . ",\n " . $this->db->quote((string) $this->o_attr_4) . ",\n " . $this->db->quote((string) $this->o_attr_5) . ",\n " . $this->db->quote((string) $this->o_attr_6) . "\n )";
$attributeSql = $this->eventManager->filter('Shopware_Modules_Order_SaveOrderAttributes_FilterSQL', $attributeSql, array('subject' => $this));
$this->db->executeUpdate($attributeSql);
$attributes = $this->getOrderAttributes($orderID);
unset($attributes['id']);
unset($attributes['orderID']);
$position = 0;
foreach ($this->sBasketData["content"] as $key => $basketRow) {
$position++;
$basketRow = $this->formatBasketRow($basketRow);
$preparedQuery = "\n INSERT INTO s_order_details\n (orderID,\n ordernumber,\n articleID,\n articleordernumber,\n price,\n quantity,\n name,\n status,\n releasedate,\n modus,\n esdarticle,\n taxID,\n tax_rate,\n ean,\n unit,\n pack_unit\n )\n VALUES (%d, %s, %d, %s, %f, %d, %s, %d, %s, %d, %d, %d, %f, %s, %s, %s)\n ";
$sql = sprintf($preparedQuery, $orderID, $this->db->quote((string) $orderNumber), $basketRow["articleID"], $this->db->quote((string) $basketRow["ordernumber"]), $basketRow["priceNumeric"], $basketRow["quantity"], $this->db->quote((string) $basketRow["articlename"]), 0, $this->db->quote((string) $basketRow["releasedate"]), $basketRow["modus"], $basketRow["esdarticle"], $basketRow["taxID"], $basketRow["tax_rate"], $this->db->quote((string) $basketRow["ean"]), $this->db->quote((string) $basketRow["itemUnit"]), $this->db->quote((string) $basketRow["packunit"]));
$sql = $this->eventManager->filter('Shopware_Modules_Order_SaveOrder_FilterDetailsSQL', $sql, array('subject' => $this, 'row' => $basketRow, 'user' => $this->sUserData, 'order' => array("id" => $orderID, "number" => $orderNumber)));
// Check for individual voucher - code
if ($basketRow["modus"] == 2) {
//reserve the basket voucher for the current user.
$this->reserveVoucher($basketRow["ordernumber"], $this->sUserData["additional"]["user"]["id"], $basketRow["articleID"]);
}
if ($basketRow["esdarticle"]) {
$esdOrder = true;
}
try {
$this->db->executeUpdate($sql);
$orderdetailsID = $this->db->lastInsertId();
} catch (Exception $e) {
throw new Enlight_Exception("Shopware Order Fatal-Error {$_SERVER["HTTP_HOST"]} :" . $e->getMessage(), 0, $e);
}
$this->sBasketData['content'][$key]['orderDetailId'] = $orderdetailsID;
//new attribute tables
$attributeSql = "INSERT INTO s_order_details_attributes (detailID, attribute1, attribute2, attribute3, attribute4, attribute5, attribute6)\n VALUES (" . $orderdetailsID . "," . $this->db->quote((string) $basketRow["ob_attr1"]) . "," . $this->db->quote((string) $basketRow["ob_attr2"]) . "," . $this->db->quote((string) $basketRow["ob_attr3"]) . "," . $this->db->quote((string) $basketRow["ob_attr4"]) . "," . $this->db->quote((string) $basketRow["ob_attr5"]) . "," . $this->db->quote((string) $basketRow["ob_attr6"]) . ")";
$attributeSql = $this->eventManager->filter('Shopware_Modules_Order_SaveOrderAttributes_FilterDetailsSQL', $attributeSql, array('subject' => $this, 'row' => $basketRow, 'user' => $this->sUserData, 'order' => array("id" => $orderID, "number" => $orderNumber)));
$this->db->executeUpdate($attributeSql);
$detailAttributes = $this->getOrderDetailAttributes($orderdetailsID);
unset($detailAttributes['id']);
unset($detailAttributes['detailID']);
$this->sBasketData['content'][$key]['attributes'] = $detailAttributes;
// Update sales and stock
if ($basketRow["priceNumeric"] >= 0) {
$this->refreshOrderedVariant($basketRow["ordernumber"], $basketRow["quantity"]);
}
// For esd-articles, assign serial number if needed
// Check if this article is esd-only (check in variants, too -> later)
if ($basketRow["esdarticle"]) {
$basketRow = $this->handleESDOrder($basketRow, $orderID, $orderdetailsID);
// Add assignedSerials to basketcontent
if (!empty($basketRow['assignedSerials'])) {
$this->sBasketData["content"][$key]['serials'] = $basketRow['assignedSerials'];
}
}
}
// For every article in basket
$this->eventManager->notify('Shopware_Modules_Order_SaveOrder_ProcessDetails', array('subject' => $this, 'details' => $this->sBasketData['content']));
$this->sUserData = $this->getUserDataForMail($this->sUserData);
$details = $this->getOrderDetailsForMail($this->sBasketData["content"]);
$variables = array("sOrderDetails" => $details, "billingaddress" => $this->sUserData["billingaddress"], "shippingaddress" => $this->sUserData["shippingaddress"], "additional" => $this->sUserData["additional"], "sShippingCosts" => $this->sSYSTEM->sMODULES['sArticles']->sFormatPrice($this->sShippingcosts) . " " . $this->sSYSTEM->sCurrency["currency"], "sAmount" => $this->sAmountWithTax ? $this->sSYSTEM->sMODULES['sArticles']->sFormatPrice($this->sAmountWithTax) . " " . $this->sSYSTEM->sCurrency["currency"] : $this->sSYSTEM->sMODULES['sArticles']->sFormatPrice($this->sAmount) . " " . $this->sSYSTEM->sCurrency["currency"], "sAmountNet" => $this->sSYSTEM->sMODULES['sArticles']->sFormatPrice($this->sBasketData["AmountNetNumeric"]) . " " . $this->sSYSTEM->sCurrency["currency"], "sTaxRates" => $this->sBasketData["sTaxRates"], "ordernumber" => $orderNumber, "sOrderDay" => date("d.m.Y"), "sOrderTime" => date("H:i"), "sComment" => $this->sComment, 'attributes' => $attributes, "sEsd" => $esdOrder);
if ($dispatchId) {
$variables["sDispatch"] = $this->sSYSTEM->sMODULES['sAdmin']->sGetPremiumDispatch($dispatchId);
}
if ($this->bookingId) {
$variables['sBookingID'] = $this->bookingId;
}
// Save Billing and Shipping-Address to retrace in future
$this->sSaveBillingAddress($this->sUserData["billingaddress"], $orderID);
$this->sSaveShippingAddress($this->sUserData["shippingaddress"], $orderID);
// Completed - Garbage basket / temporary - order
$this->sDeleteTemporaryOrder();
$this->db->executeUpdate("DELETE FROM s_order_basket WHERE sessionID=?", array($this->getSession()->offsetGet('sessionId')));
$confirmMailDeliveryFailed = false;
try {
$this->sendMail($variables);
} catch (\Exception $e) {
$confirmMailDeliveryFailed = true;
$email = $this->sUserData['additional']['user']['email'];
$this->logOrderMailException($e, $orderNumber, $email);
}
// Check if voucher is affected
$this->sTellFriend();
if ($this->getSession()->offsetExists('sOrderVariables')) {
$variables = $this->getSession()->offsetGet('sOrderVariables');
$variables['sOrderNumber'] = $orderNumber;
$variables['confirmMailDeliveryFailed'] = $confirmMailDeliveryFailed;
$this->getSession()->offsetSet('sOrderVariables', $variables);
}
return $orderNumber;
}
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;
}
/**
* Read translation for one or more articles
* @param $data
* @param $object
* @return array
*/
public function sGetTranslations($data, $object)
{
if (Shopware()->Shop()->get('skipbackend') || empty($data)) {
return $data;
}
$language = Shopware()->Shop()->getId();
$fallback = Shopware()->Shop()->get('fallback');
$ids = $this->db->quote(array_keys($data));
switch ($object) {
case 'article':
$map = array('txtshortdescription' => 'description', 'txtlangbeschreibung' => 'description_long', 'txtArtikel' => 'articleName', 'txtzusatztxt' => 'additionaltext', 'txtkeywords' => 'keywords', 'txtpackunit' => 'packunit');
break;
case 'configuratorgroup':
$map = array('description' => 'groupdescription', 'name' => 'groupname');
break;
default:
return $data;
}
$object = $this->db->quote($object);
$sql = '';
if (!empty($fallback)) {
$sql .= "\n SELECT s.objectdata, s.objectkey\n FROM s_core_translations s\n WHERE\n s.objecttype = {$object}\n AND\n s.objectkey IN ({$ids})\n AND\n s.objectlanguage = '{$fallback}'\n UNION ALL\n ";
}
$sql .= "\n SELECT s.objectdata, s.objectkey\n FROM s_core_translations s\n WHERE\n s.objecttype = {$object}\n AND\n s.objectkey IN ({$ids})\n AND\n s.objectlanguage = '{$language}'\n ";
$translations = $this->db->fetchAll($sql);
if (empty($translations)) {
return $data;
}
foreach ($translations as $translation) {
$article = (int) $translation['objectkey'];
$object = unserialize($translation['objectdata']);
foreach ($object as $translateKey => $value) {
if (isset($map[$translateKey])) {
$key = $map[$translateKey];
} else {
$key = $translateKey;
}
if (!empty($value) && array_key_exists($key, $data[$article])) {
$data[$article][$key] = $value;
}
}
}
return $data;
}
