/** * 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; }