Location: PHPKode > projects > Shopware > engine/core/class/sAdmin.php
<?php
/**
 * Shopware 4.0
 * Copyright © 2012 shopware AG
 *
 * According to our dual licensing model, this program can be used either
 * under the terms of the GNU Affero General Public License, version 3,
 * or under a proprietary license.
 *
 * The texts of the GNU Affero General Public License with an additional
 * permission and of our proprietary license can be found at and
 * in the LICENSE file you have received along with this program.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU Affero General Public License for more details.
 *
 * "Shopware" is a registered trademark of shopware AG.
 * The licensing of the program under the AGPLv3 does not imply a
 * trademark license. Therefore any rights, title and interest in
 * our trademarks remain entirely with us.
 *
 * @category   Shopware
 * @package    Shopware_Core
 * @subpackage Class
 * @copyright  Copyright (c) 2012, shopware AG (http://www.shopware.de)
 * @version    $Id$
 * @author     Stefan Hamann
 * @author     $Author$
 */

/**
 * Deprecated Shopware Class that handles several
 * functions around customer / order related things
 *
 * hide@address.com: Documentation
 */
class sAdmin
{
	/**
     * Pointer to sSystem object
     *
     * @var object
     */
	var $sSYSTEM;

    /**
     * @var Shopware_Components_Snippet_Manager
     */
    var $snippetObject;

    /**
     * Check if current active shop has own registration
     * @var bool s_core_multilanguage.scoped_registration
     */
    var $scopedRegistration;

    /**
     * Id of current active shop
     * @var int s_core_multilanguage.id
     */
    var $subshopId;

    public function __construct(){
       $this->snippetObject = Shopware()->Snippets()->getNamespace('frontend/account/internalMessages');
       $shop = Shopware()->Shop()->getMain() !== null ? Shopware()->Shop()->getMain() : Shopware()->Shop();
       $this->scopedRegistration = $shop->getCustomerScope();
       $this->subshopId = $shop->getId();
    }

	 /** Logout user and destroy session
	 * @access public
	 * @return -
	 */
	public function sLogout (){
		if (Enlight()->Events()->notifyUntil('Shopware_Modules_Admin_Logout_Start', array('subject'=>$this))){
			return false;
		}

		unset($this->sSYSTEM->_SESSION);
		unset($_SESSION);
		unset($this->sSYSTEM->sUSERGROUPDATA);

		$this->sSYSTEM->sUSERGROUPDATA = $this->sSYSTEM->sDB_CONNECTION->GetRow("
		SELECT * FROM s_core_customergroups WHERE `groupkey` = 'EK'
		");
        session_destroy();
		$this->sSYSTEM->_SESSION["sUserGroup"] = "EK";
        $this->sSYSTEM->_SESSION["sUserGroupData"] = $this->sSYSTEM->sUSERGROUPDATA;

		session_regenerate_id();
	}

	 /**
	 * DEPRECATED , old vatid check
     * @deprecated
	 * @access public
	 * @return -
	 */
	public function sCheckTaxID($id, $country)
	{
		return array();
	}

	/**
	 * Checks vat id with webservice
	 * @access public
	 * @return array assoziative array with success / error codes
	 */
	public function sValidateVat()
	{
        if (empty($this->sSYSTEM->sCONFIG['sVATCHECKENDABLED'])) {
            return array();
        }
        if (empty($this->sSYSTEM->_POST["ustid"]) && empty($this->sSYSTEM->sCONFIG['sVATCHECKREQUIRED'])) {
            return array();
        }

        $messages = array();
        $ustid = preg_replace('#[^0-9A-Z\+\*\.]#', '', strtoupper($this->sSYSTEM->_POST['ustid']));
        $country = $this->sSYSTEM->sDB_CONNECTION->GetOne(
            'SELECT countryiso FROM s_core_countries WHERE id=?',
            array($this->sSYSTEM->_POST['country'])
        );
        if (empty($this->sSYSTEM->_POST["ustid"])) {
            $messages[] = $this->snippetObject->get('VatFailureEmpty', 'Please enter a vat id');
        } elseif (empty($ustid) || !preg_match("#^([A-Z]{2})([0-9A-Z+*.]{2,12})$#", $ustid, $vat)) {
            $messages[] = $this->snippetObject->get('VatFailureInvalid', 'The vat id entered is invalid');
        }
        elseif (empty($country) || $country != $vat[1]) {
            $field_names = explode(',', $this->snippetObject->get('VatFailureErrorFields', 'Company,City,Zip,Street,Country'));
            $field_name = isset($field_names[4]) ? $field_names[4] : 'Land';
            $messages[] = sprintf($this->snippetObject->get('VatFailureErrorField', 'The field %s does not match to the vat id entered'), $field_name);
        }
        elseif ($country == 'DE') {

        }
        elseif (!empty($this->sSYSTEM->sCONFIG['sVATCHECKADVANCEDNUMBER'])) {
            $data = array(
                'UstId_1' => $this->sSYSTEM->sCONFIG['sVATCHECKADVANCEDNUMBER'],
                'UstId_2' => $vat[1] . $vat[2],
                'Firmenname' => '',
                'Ort' => '',
                'PLZ' => '',
                'Strasse' => '',
                'Druck' => empty($this->sSYSTEM->sCONFIG['sVATCHECKCONFIRMATION']) ? 'nein' : 'ja'
            );

            if (!empty($this->sSYSTEM->sCONFIG['sVATCHECKADVANCED'])
                && strpos($this->sSYSTEM->sCONFIG['sVATCHECKADVANCEDCOUNTRIES'], $vat[1]) !== false
            ) {
                $data['Firmenname'] = $this->sSYSTEM->_POST['company'];
                $data['Ort'] = $this->sSYSTEM->_POST['city'];
                $data['PLZ'] = $this->sSYSTEM->_POST['zipcode'];
                $data['Strasse'] = $this->sSYSTEM->_POST['street'] . ' ' . $this->sSYSTEM->_POST['streetnumber'];
            }

            $request = 'http://evatr.bff-online.de/evatrRPC?';
            $request .= http_build_query($data, '', '&');

            $context = stream_context_create(array('http' => array(
                'method' => 'GET',
                'header' => 'Content-Type: text/html; charset=utf-8',
                'timeout' => 5,
                'user_agent' => 'Shopware/' . $this->sSYSTEM->sCONFIG['sVERSION']
            )));
            $response = @file_get_contents($request, false, $context);

            $reg = '#<param>\s*<value><array><data>\s*<value><string>([^<]*)</string></value>\s*<value><string>([^<]*)</string></value>\s*</data></array></value>\s*</param>#msi';
            if (!empty($response) && preg_match_all($reg, $response, $matches)) {
                $response = array_combine($matches[1], $matches[2]);
                $messages = $this->sCheckVatResponse($response);
            } elseif (empty($this->sSYSTEM->sCONFIG['sVATCHECKNOSERVICE'])) {
                $messages[] = sprintf($this->snippetObject->get('VatFailureUnknownError', 'An unknown error occurs while checking your vat id. Error code %d'), 10);
            }
        } elseif (false && class_exists('SoapClient')) {
            $url = 'http://ec.europa.eu/taxation_customs/vies/services/checkVatService.wsdl';
            if (!file_get_contents($url)) {
                $messages[] = sprintf($this->snippetObject->get('VatFailureUnknownError', 'An unknown error occurs while checking your vat id. Error code %d'), 11);
            } else {
                $client = new SoapClient($url, array('exceptions' => 0, 'connection_timeout' => 5));
                $response = $client->checkVat(array('countryCode' => $vat[1], 'vatNumber' => $vat[2]));
            }
            if (is_soap_fault($response)) {
                $messages[] = sprintf($this->snippetObject->get('VatFailureUnknownError', 'An unknown error occurs while checking your vat id. Error code %d'), 12);
                if (!empty($this->sSYSTEM->sCONFIG['sVATCHECKDEBUG'])) {
                    $messages[] = "SOAP-error: (errorcode: {$response->faultcode}, errormsg: {$response->faultstring})";
                }
            } elseif (empty($response->valid)) {
                $messages[] = $this->snippetObject->get('VatFailureInvalid', 'The vat id entered is invalid');
            }
        }
        else {
            $messages[] = sprintf($this->snippetObject->get('VatFailureUnknownError', 'An unknown error occurs while checking your vat id. Error code %d'), 20);
        }
        if (!empty($messages) && empty($this->sSYSTEM->sCONFIG['sVATCHECKREQUIRED'])) {
            $messages[] = $this->snippetObject->get('VatFailureErrorInfo', ''); // hide@address.com In the case vat is not required in registration, this info message should occur
        }
        $messages = Enlight()->Events()->filter('Shopware_Modules_Admin_CheckTaxID_MessagesFilter', $messages,
            array('subject' => $this, "post" => $this->sSYSTEM->_POST)
        );
        return $messages;
	}

	/**
	 * Process answer from german vat webservice
	 * @param  $response
	 * @return array
	 */
	public function sCheckVatResponse ($response)
	{
        if (!empty($this->sSYSTEM->sCONFIG['sVATCHECKNOSERVICE'])) {
            if (in_array($response['ErrorCode'], array(999, 205, 218, 208, 217, 219))) {
                return array();
            }
        }
        // hide@address.com remove if no longer needed, else fix this unicode mess
        if (!empty($this->sSYSTEM->sCONFIG['sVATCHECKDEBUG'])) {
            switch ($response['ErrorCode']) {
				case 200: break;
				case 201: $msg = 'Die eingegebene USt-IdNr. ist ungültig.'; break;
				case 202: $msg = 'Die eingegebene USt-IdNr. ist ungültig. Sie ist nicht in der Unternehmerdatei des betreffenden EU-Mitgliedstaates registriert.'; break;
				case 203: $msg = 'Die eingegebene USt-IdNr. ist ungültig. Sie ist erst ab dem '.$response['Gueltig_ab'].' gültig.'; break;
				case 204: $msg = 'Die eingegebene USt-IdNr. ist ungültig. Sie war im Zeitraum von '.$response['Gueltig_ab'].' bis '.$response['Gueltig_bis'].' gültig.'; break;
				case 209: $msg = 'Die eingegebene USt-IdNr. ist ungültig. Sie entspricht nicht dem Aufbau der für diesen EU-Mitgliedstaat gilt.'; break;
				case 210: $msg = 'Die eingegebene USt-IdNr. ist ungültig. Sie entspricht nicht den Prüfziffernregeln die für diesen EU-Mitgliedstaat gelten.'; break;
				case 211: $msg = 'Die eingegebene USt-IdNr. ist ungültig. Sie enthält unzulässige Zeichen.'; break;
				case 212: $msg = 'Die eingegebene USt-IdNr. ist ungültig. Sie enthält ein unzulässiges Länderkennzeichen.'; break;

				case 207: $msg = 'Ihnen wurde die deutsche USt-IdNr. ausschliesslich zu Zwecken der Besteuerung des innergemeinschaftlichen Erwerbs erteilt. Sie sind somit nicht berechtigt, Bestätigungsanfragen zu stellen.'; break;
				case 206: $msg = 'Ihre deutsche USt-IdNr. ist ungültig. Eine Bestätigungsanfrage ist daher nicht möglich. Den Grund hierfür können Sie beim Bundeszentralamt für Steuern - Dienstsitz Saarlouis - erfragen.'; break;
				case 208: $msg = 'Für die von Ihnen angefragte USt-IdNr. läuft gerade eine Anfrage von einem anderen Nutzer. Eine Bearbeitung ist daher nicht möglich. Bitte versuchen Sie es später noch einmal.'; break;
				case 213: $msg = 'Die Abfrage einer deutschen USt-IdNr. ist nicht möglich.'; break;
				case 214: $msg = 'Ihre deutsche USt-IdNr. ist fehlerhaft. Sie beginnt mit \'DE\' gefolgt von 9 Ziffern.'; break;
				case 215: $msg = 'Ihre Anfrage enthält nicht alle notwendigen Angaben für eine einfache Bestätigungsanfrage'; break;
				case 216: $msg = 'Ihre Anfrage enthält nicht alle notwendigen Angaben für eine qualifizierte Bestätigungsanfrage'; break;
				case 217: $msg = 'Bei der Verarbeitung der Daten aus dem angefragten EU-Mitgliedstaat ist ein Fehler aufgetreten. Ihre Anfrage kann deshalb nicht bearbeitet werden.'; break;
				case 218: $msg = 'Eine qualifizierte Bestätigung ist zur Zeit nicht möglich.'; break;
				case 219: $msg = 'Bei der Durchführung der qualifizierten Bestätigungsanfrage ist ein Fehler aufgetreten.'; break;
				case 220: $msg = 'Bei der Anforderung der amtlichen Bestätigungsmitteilung ist ein Fehler aufgetreten. Sie werden kein Schreiben erhalten.'; break;
				case 999: $msg = 'Eine Bearbeitung Ihrer Anfrage ist zurzeit nicht möglich. Bitte versuchen Sie es später noch einmal.'; break;
				case 205: $msg = 'Ihre Anfrage kann derzeit durch den angefragten EU-Mitgliedstaat oder aus anderen Gründen nicht beantwortet werden'; break;

				default:  $msg = sprintf($this->snippetObject->get('VatFailureUnknownError','An unknown error occurs while checking your vat id. Error code %d'), 30); break;
            }
        } else {
            switch ($response['ErrorCode']) {
                case 200:
                    break;
                case 201:
                case 202:
                case 204:
                case 209:
                case 210:
                case 211:
                case 212:
                    $msg = $this->snippetObject->get('VatFailureInvalid', 'The vat id entered is invalid');
                    break;
                case 203:
                    $msg = sprintf($this->snippetObject->get('VatFailureDate', 'The vat id entered is invalid.Is valid from %s'), $response['Gueltig_ab']);
                    break;
                default:
                    $msg = sprintf($this->snippetObject->get('VatFailureUnknownError', 'An unknown error occurs while checking your vat id. Error code %d'), 31);
                    break;
            }
        }
        $result = array();
        if (!empty($msg)) {
            $result[] = $msg;
        } else {
            $fields = array('Erg_Name', 'Erg_Ort', 'Erg_PLZ', 'Erg_Str');
            $field_names = explode(',', $this->snippetObject->get('VatFailureErrorFields', 'Company,City,Zip,Street,Country'));
            foreach ($fields as $key => $field) {
                if (isset($response[$field]) && strpos($this->sSYSTEM->sCONFIG['sVATCHECKVALIDRESPONSE'], $response[$field]) === false) {
                    $name = isset($field_names[$key]) ? $field_names[$key] : $field;
                    $result[] = sprintf($this->snippetObject->get('VatFailureErrorField', 'The field %s does not match to the vat id entered'), $name);
                }
            }
        }
        return $result;
	}
	 /**
	 * Get data from a certain payment
	 * @param int $id - s_core_paymentmeans.id
	 * @param array $user - array with user data (sGetUserData)
	 * @access public
	 * @return array - payment data
	 */
	public function sGetPaymentMeanById($id,$user=false){
		$id = intval($id);
		$sql = "
		SELECT * FROM s_core_paymentmeans
		WHERE id=?
		";
		$data =  $this->sSYSTEM->sDB_CONNECTION->GetRow($sql,array($id));


		if ($this->sSYSTEM->sMODULES['sBasket']->sCheckForESD()){
			$sEsd = true;
		}

		if (!count($user)){
			$user = array();
		}

        $this->sSYSTEM->sMODULES['sBasket']->sBASKET = $this->sSYSTEM->sMODULES['sBasket']->sGetBasket();
		$basket = $this->sSYSTEM->sMODULES['sBasket']->sBASKET;

		// Check for risk-management
		// If rule matches, reset to default paymentmean if this paymentmean was not
		// set by shop-owner

		// Hide paymentmeans which are not active
		if (!$data["active"] && $data["id"]!=$user["additional"]["user"]["paymentpreset"]){
			$resetPayment = $this->sSYSTEM->sCONFIG["sPAYMENTDEFAULT"];
		}

		// If esd - order, hide payment-means, whih
		// are not accessible for esd
		if (!$data["esdactive"] && $sEsd){
			$resetPayment = $this->sSYSTEM->sCONFIG["sPAYMENTDEFAULT"];
		}

		// Check additional rules
		if ($this->sManageRisks($data["id"],$basket,$user) && $data["id"]!=$user["additional"]["user"]["paymentpreset"]){
			$resetPayment = $this->sSYSTEM->sCONFIG["sPAYMENTDEFAULT"];
		}

		if(!empty($user['additional']['countryShipping']['id'])) {
			$sql = "
				SELECT 1
				FROM s_core_paymentmeans p

				LEFT JOIN s_core_paymentmeans_subshops ps
				ON ps.subshopID=?
				AND ps.paymentID=p.id

				LEFT JOIN s_core_paymentmeans_countries pc
				ON pc.countryID=?
				AND pc.paymentID=p.id

				WHERE (ps.paymentID IS NOT NULL OR (SELECT paymentID FROM s_core_paymentmeans_subshops WHERE paymentID=p.id LIMIT 1) IS NULL)
				AND (pc.paymentID IS NOT NULL OR (SELECT paymentID FROM s_core_paymentmeans_countries WHERE paymentID=p.id LIMIT 1) IS NULL)

				AND id=?
			";
			$active = $this->sSYSTEM->sDB_CONNECTION->GetOne($sql, array(
				$this->sSYSTEM->sSubShop['id'],
				$user['additional']['countryShipping']['id'],
				$id
			));
			if(empty($active)) {
				$resetPayment = $this->sSYSTEM->sCONFIG["sPAYMENTDEFAULT"];
			}
		}

		if ($resetPayment && $user["additional"]["user"]["id"]){
			$updateAccount =  $this->sSYSTEM->sDB_CONNECTION->Execute("
			UPDATE s_user SET paymentID = ? WHERE id = ?
			",array($resetPayment,$user["additional"]["user"]["id"]));
			$sql = "
			SELECT * FROM s_core_paymentmeans
			WHERE id=?
			";
			$data =  $this->sSYSTEM->sDB_CONNECTION->GetRow($sql,array($resetPayment));
		}

		// Get Translation
		$data = $this->sGetPaymentTranslation($data);

		$data = Enlight()->Events()->filter('Shopware_Modules_Admin_GetPaymentMeanById_DataFilter', $data, array('subject'=>$this,"id"=>$id,"user"=>$user));

		return $data;
	}

	 /**
	 * Get all available payments
	 *
	 * @access public
	 * @return array - payments data
	 */
	public function sGetPaymentMeans(){


		/*
			Get User / Basket-Data
		*/
		if (!count($this->sSYSTEM->sMODULES['sBasket']->sBASKET)){
			$this->sSYSTEM->sMODULES['sBasket']->sBASKET = $this->sSYSTEM->sMODULES['sBasket']->sGetBasket();
		}

		$basket = $this->sSYSTEM->sMODULES['sBasket']->sBASKET;

		$user = $this->sGetUserData();

		if ($this->sSYSTEM->sMODULES['sBasket']->sCheckForESD()){
			$sEsd = true;
		}else {
			$sEsd = false;
		}

		$countryID = (int) $user['additional']['countryShipping']['id'];
		$subshopID = (int) $this->sSYSTEM->sSubShop['id'];
		if (empty($countryID)){
			$countryID = $this->sSYSTEM->sDB_CONNECTION->GetOne("
			SELECT id FROM s_core_countries ORDER BY position ASC LIMIT 1
			");
		}
		$sql = "
			SELECT p.*
			FROM s_core_paymentmeans p

			LEFT JOIN s_core_paymentmeans_subshops ps
			ON ps.subshopID=$subshopID
			AND ps.paymentID=p.id

			LEFT JOIN s_core_paymentmeans_countries pc
			ON pc.countryID=$countryID
			AND pc.paymentID=p.id

			WHERE (ps.paymentID IS NOT NULL OR (SELECT paymentID FROM s_core_paymentmeans_subshops WHERE paymentID=p.id LIMIT 1) IS NULL)
			AND (pc.paymentID IS NOT NULL OR (SELECT paymentID FROM s_core_paymentmeans_countries WHERE paymentID=p.id LIMIT 1) IS NULL)

			ORDER BY position, name
		";


		$getPaymentMeans = $this->sSYSTEM->sDB_CONNECTION->GetAll($sql);

		if($getPaymentMeans===false)
		{
			$sql = "SELECT * FROM s_core_paymentmeans ORDER BY position, name";
			$getPaymentMeans = $this->sSYSTEM->sDB_CONNECTION->GetAll($sql);
		}

		foreach ($getPaymentMeans as $payKey => $payValue) {

				// Hide paymentmeans which are not active
				if (empty($payValue["active"]) && $payValue["id"]!=$user["additional"]["user"]["paymentpreset"]){
					unset($getPaymentMeans[$payKey]);
					continue;
				}

				// If esd - order, hide payment-means, whih
				// are not accessible for esd
				if (empty($payValue["esdactive"]) && $sEsd){
					unset($getPaymentMeans[$payKey]);
					continue;
				}

				// Check additional rules
				if ($this->sManageRisks($payValue["id"],$basket,$user) && $payValue["id"]!=$user["additional"]["user"]["paymentpreset"]){
					unset($getPaymentMeans[$payKey]);
					continue;
				}

				// Get possible translation
				$getPaymentMeans[$payKey] = $this->sGetPaymentTranslation($getPaymentMeans[$payKey]);

		}

		if (!count($getPaymentMeans)){
			$this->sSYSTEM->E_CORE_WARNING("sGetPaymentMeans #00","Could not get any payment-means".$sql);
			return;
		}

        $getPaymentMeans = Enlight()->Events()->filter('Shopware_Modules_Admin_GetPaymentMeans_DataFilter', $getPaymentMeans, array('subject'=>$this));

		return $getPaymentMeans;

	}

	/**
	 * Loads the system class of the specified payment (engine/core/class/paymentmeans)
	 * @param array $paymentData - Array with payment data
	 * @access public
	 * @return object or false -
	 */
	public function sInitiatePaymentClass($paymentData){
		include_once("paymentmeans/".$paymentData['class']);
			$sPaymentObject = new sPaymentMean();
			$sPaymentObject->sSYSTEM = &$this->sSYSTEM;

			if (!$sPaymentObject){
				$this->sSYSTEM->E_CORE_WARNING("sValidateStep3 #02","Payment-Class not found");
				return false;
			}else {
				return $sPaymentObject;
			}

	}

	 /**
	 * Last step of the registration - validate all user fields that exists in session and
	 * stores the data into database
	 * @param array $paymentmeans - Array with payment data
	 * @access public
	 * @return -
	 */
	public function sValidateStep3 ($paymentmeans = array())
	{
		if (empty($this->sSYSTEM->_POST['sPayment'])){
			$this->sSYSTEM->E_CORE_WARNING("sValidateStep3 #00","No payment-id");
			return;
		}

		$user = $this->sGetUserData();
		$paymentData = $this->sGetPaymentMeanById($this->sSYSTEM->_POST['sPayment'],$user);

		if (!count($paymentData)){
			$this->sSYSTEM->E_CORE_ERROR("sValidateStep3 #01","Could not load paymentmean");
			return;
		}else {
			// Include management-class and check input-data
			if(!empty($paymentData['class'])) {
				$sPaymentObject = $this->sInitiatePaymentClass($paymentData);
				$checkPayment = $sPaymentObject->sInit($this->sSYSTEM);
			}
			return array("checkPayment"=>$checkPayment,"paymentData"=>$paymentData,"sProcessed"=>true,"sPaymentObject"=>&$sPaymentObject);
		}
		return;
	}

    /**
	 * Updates the billing address of the user
	 *
	 * @access public
	 * @return bool
	 */
	public function sUpdateBilling (){

		$userObject = $this->sSYSTEM->_POST;

		if (!empty($userObject['birthmonth']) && !empty($userObject['birthday']) && !empty($userObject['birthyear']))
		{
			$userObject['birthday'] = mktime(0,0,0, (int) $userObject['birthmonth'], (int) $userObject['birthday'], (int) $userObject['birthyear']);
			if($userObject['birthday']>0)
			{
				$userObject['birthday'] = date('Y-m-d', $userObject['birthday']);
			}
			else
			{
				$userObject['birthday'] = '0000-00-00';
			}
		}
		else
		{
			unset($userObject['birthday']);
		}

		$fields = array(
			'company',
			'department',
			'salutation',
			'firstname',
			'lastname',
			'street',
			'streetnumber',
			'zipcode',
			'city',
			'phone',
			'fax',
			'countryID',
            'stateID',
			'ustid',
			'birthday'
		);

		$data = array();
		foreach ($fields as $field)
		{
			if(isset($userObject[$field]))
			{
				$data[$field] = $userObject[$field];
			}
		}

		$data["countryID"] = $userObject["country"];

		$where = array(
			'userID='.(int) $this->sSYSTEM->_SESSION['sUserId']
		);

		list($data,$where) = Enlight()->Events()->filter('Shopware_Modules_Admin_UpdateBilling_FilterSql', array($data,$where), array('subject'=>$this,"id"=>$this->sSYSTEM->_SESSION['sUserId'],"user"=>$userObject));

		$result = Shopware()->Db()->update('s_user_billingaddress', $data, $where);


		if ($this->sSYSTEM->sDB_CONNECTION->ErrorMsg()){
			$this->sSYSTEM->E_CORE_WARNING("sUpdateBilling #01","Could not save data (billing-adress)".$this->sSYSTEM->sDB_CONNECTION->ErrorMsg());
			return false;
		}

        //new attribute tables.
        $data = array(
            "text1" => $userObject['text1'],
            "text2" => $userObject['text2'],
            "text3" => $userObject['text3'],
            "text4" => $userObject['text4'],
            "text5" => $userObject['text5'],
            "text6" => $userObject['text6'],
        );

        $sql= "SELECT id FROM s_user_billingaddress WHERE userID = " . (int) $this->sSYSTEM->_SESSION['sUserId'];
        $billingId = Shopware()->Db()->fetchOne($sql);
        $where = array(" billingID = " . $billingId);

        list($data,$where) = Enlight()->Events()->filter('Shopware_Modules_Admin_UpdateBillingAttributes_FilterSql', array($data,$where), array('subject'=>$this,"id"=>$this->sSYSTEM->_SESSION['sUserId'],"user"=>$userObject));

        Shopware()->Db()->update('s_user_billingaddress_attributes', $data, $where);

		return true;
	}

	 /**
	 * Add, remove customer from maillinglist
	 * @param bool $status 1 = Insert, 0 = Delete
	 * @param string $email - mail address
	 * @access public
	 * @return -
	 */
	public function sUpdateNewsletter ($status,$email,$customer=false){
		if (!$status){
			// Delete
			$changeLetterState = $this->sSYSTEM->sDB_CONNECTION->Execute("
			DELETE FROM s_campaigns_mailaddresses WHERE email=?
			",array($email));
		}else {
            // Check if mail address receives already our newsletter
            if ($this->sSYSTEM->sDB_CONNECTION->getOne("SELECT id FROM s_campaigns_mailaddresses WHERE email = ?",array($email))){
                return false;
            }
			$groupID = $this->sSYSTEM->sCONFIG['sNEWSLETTERDEFAULTGROUP'];
			if (!$groupID) $groupID = "0";
			// Insert
			if (!empty($customer)){
				$changeLetterState = $this->sSYSTEM->sDB_CONNECTION->Execute("
				INSERT INTO s_campaigns_mailaddresses (customer, email)
				VALUES (?,?)
				",array(1,$email));
			}else {
				$changeLetterState = $this->sSYSTEM->sDB_CONNECTION->Execute("
				INSERT INTO s_campaigns_mailaddresses (groupID, email)
				VALUES (?,?)
				",array($groupID,$email));
			}
		}

        return true;
	}


	 /**
	 * Set previous used address to default address
	 * @param string $type  shipping / billing
	 * @param string $request_hash  secure hash
	 * @access public
	 * @return -
	 */
	public function sGetPreviousAddresses($type, $request_hash=null)
	{
		if (empty($type)) return false;
		if (empty($this->sSYSTEM->_SESSION['sUserId'])) return false;

		if ($type=='shipping') {
			$type = 'shipping';
		} else {
			$type = 'billing';
		}

		$sql = '
			SELECT
				MD5(CONCAT(company, department, salutation, firstname, lastname, street, streetnumber, zipcode, city, countryID)) as hash,
				company, department, salutation, firstname, lastname,
				street, streetnumber, zipcode, city, countryID as country, countryID, countryname
			FROM s_order_'.$type.'address AS a
			LEFT JOIN s_core_countries co
			ON a.countryID=co.id
			WHERE a.userID=?
			GROUP BY hash
			ORDER BY MAX(a.id) DESC
		';

		$addresses = $this->sSYSTEM->sDB_CONNECTION->GetAll($sql, array($this->sSYSTEM->_SESSION['sUserId']));

		foreach ($addresses as $address)
		{
			if(!empty($request_hash)&&$address['hash']==$request_hash)
			{
				return $address;
			}
			$address[$address['hash']]['country'] = array();
			$address[$address['hash']]['country']['id'] = $address['countryID'];
			$address[$address['hash']]['country']['countryname'] = $address['countryname'];
			$address[$address['hash']]['country'] = $this->sGetCountryTranslation($address["country"]);
		}

		if(!empty($request_hash))
		{
			return false;
		}

		return $addresses;
	}

	 /**
	 * Updates the delivery address of the user
	 *
	 * @access public
	 * @return bool
	 */
	public function sUpdateShipping (){
		$userObject = $this->sSYSTEM->_POST;

		if (empty($this->sSYSTEM->_SESSION["sUserId"])){
			return false;
		}

		$sql = 'SELECT id FROM s_user_shippingaddress WHERE userID=?';
		$shippingID = Shopware()->Db()->fetchOne($sql, array($this->sSYSTEM->_SESSION['sUserId']));

		$fields = array(
			'company',
			'department',
			'salutation',
			'firstname',
			'lastname',
			'street',
			'streetnumber',
			'zipcode',
			'city',
			'countryID',
            'stateID'
		);

		$data = array();
		foreach ($fields as $field)
		{
			if(isset($userObject[$field]))
			{
				$data[$field] = $userObject[$field];
			}
		}
		$data["countryID"] = isset($userObject["country"]) ? $userObject["country"] : 0;


		list($data) = Enlight()->Events()->filter('Shopware_Modules_Admin_UpdateShipping_FilterSql', array($data), array('subject'=>$this,"id"=>$this->sSYSTEM->_SESSION['sUserId'],"user"=>$userObject));

		if(empty($shippingID))
		{
			$data["userID"] = (int)$this->sSYSTEM->_SESSION['sUserId'];
			$result = Shopware()->Db()->insert('s_user_shippingaddress', $data);

            $shippingID = Shopware()->Db()->lastInsertId('s_user_shippingaddress');
            $attributeData = array(
                'shippingID' => $shippingID,
                'text1' => $userObject['text1'],
                'text2' => $userObject['text2'],
                'text3' => $userObject['text3'],
                'text4' => $userObject['text4'],
                'text5' => $userObject['text5'],
                'text6' => $userObject['text6']
            );
            list($attributeData) = Enlight()->Events()->filter('Shopware_Modules_Admin_UpdateShippingAttributes_FilterSql', array($attributeData), array('subject'=>$this,"id"=>$this->sSYSTEM->_SESSION['sUserId'],"user"=>$userObject));
            Shopware()->Db()->insert('s_user_shippingaddress_attributes', $attributeData);
		}
		else
		{
			$where = array('id='.(int)$shippingID);
			$result = Shopware()->Db()->update('s_user_shippingaddress', $data,$where);

            $attributeData = array(
                'text1' => $userObject['text1'],
                'text2' => $userObject['text2'],
                'text3' => $userObject['text3'],
                'text4' => $userObject['text4'],
                'text5' => $userObject['text5'],
                'text6' => $userObject['text6']
            );
            $where = array('shippingID='.(int)$shippingID);
            list($attributeData) = Enlight()->Events()->filter('Shopware_Modules_Admin_UpdateShippingAttributes_FilterSql', array($attributeData), array('subject'=>$this,"id"=>$this->sSYSTEM->_SESSION['sUserId'],"user"=>$userObject));
            Shopware()->Db()->update('s_user_shippingaddress_attributes', $attributeData,$where);
		}

		if ($this->sSYSTEM->sDB_CONNECTION->ErrorMsg()){
			$this->sSYSTEM->E_CORE_WARNING("sUpdateShipping #01","Could not save data (billing-adress)".$this->sSYSTEM->sDB_CONNECTION->ErrorMsg());
			return false;
		}
		return true;
	}

	 /**
	 * Updates the payment of the user
	 *
	 * @access public
	 * @return bool
	 */
	public function sUpdatePayment (){
		if (empty($this->sSYSTEM->_SESSION["sUserId"])){
			return false;
		}
		$sqlPayment = "
		UPDATE s_user SET paymentID=? WHERE id=?";

		$sqlPayment = Enlight()->Events()->filter('Shopware_Modules_Admin_UpdatePayment_FilterSql', $sqlPayment, array('subject'=>$this,"id"=>$this->sSYSTEM->_SESSION['sUserId']));

		$saveUserData = $this->sSYSTEM->sDB_CONNECTION->Execute($sqlPayment,array($this->sSYSTEM->_POST["sPayment"],$this->sSYSTEM->_SESSION["sUserId"]));

		if ($this->sSYSTEM->sDB_CONNECTION->ErrorMsg()){
			$this->sSYSTEM->E_CORE_WARNING("sUpdatePayment #01","Could not save data (payment)".$this->sSYSTEM->sDB_CONNECTION->ErrorMsg());
			return false;
		}
		return true;
	}

	 /**
	 * Update  email address and password of the user
	 *
	 * @access public
	 * @return bool
	 */
	public function sUpdateAccount (){
		$p = $this->sSYSTEM->_POST;

		$email = strtolower($p["email"]);

		$password = $p["password"];
		$passwordConfirmation = $p["passwordConfirmation"];


		if ($password && $passwordConfirmation){
			$password = md5($password);
			$this->sSYSTEM->_SESSION["sUserMail"] = $email;
			$this->sSYSTEM->_SESSION["sUserPassword"] = $password;
			$sqlAccount = "
			UPDATE s_user SET email=?, password=? WHERE id=?";
			$sqlAccount = Enlight()->Events()->filter('Shopware_Modules_Admin_UpdateAccount_FilterPasswordSql', $sqlAccount, array('email'=>$email,'password'=>$password,'subject'=>$this,"id"=>$this->sSYSTEM->_SESSION['sUserId']));

			$saveUserData = $this->sSYSTEM->sDB_CONNECTION->Execute($sqlAccount,array($email,$password,$this->sSYSTEM->_SESSION["sUserId"]));
		}else {
			$this->sSYSTEM->_SESSION["sUserMail"] = $email;
			$sqlAccount = "
			UPDATE s_user SET email=? WHERE id=?";
			$sqlAccount = Enlight()->Events()->filter('Shopware_Modules_Admin_UpdateAccount_FilterEmailSql', $sqlAccount, array('email'=>$email,'password'=>$password,'subject'=>$this,"id"=>$this->sSYSTEM->_SESSION['sUserId']));

			$saveUserData = $this->sSYSTEM->sDB_CONNECTION->Execute($sqlAccount,array($email,$this->sSYSTEM->_SESSION["sUserId"]));
		}


		if ($this->sSYSTEM->sDB_CONNECTION->ErrorMsg()){
			$this->sSYSTEM->E_CORE_WARNING("sUpdateAccount #01","Could not save data (account)".$this->sSYSTEM->sDB_CONNECTION->ErrorMsg());
			return false;
		}
		return true;

	}

	 /**
	 * Checks the billing address (Registration Step 2)
	 * @param array $rules Defined rules / required fields from controller (register.php)
	 * @param boolean $edit
	 * @access public
	 * @return array Array with errors that may have occurred
	 */
	public function sValidateStep2 ($rules,$edit=false){

		$sErrorMessages = array();
		$sErrorFlag = array();

		list($sErrorMessages,$sErrorFlag) = Enlight()->Events()->filter('Shopware_Modules_Admin_ValidateStep2_FilterStart', array($sErrorMessages,$sErrorFlag), array('edit'=>$edit,'rules'=>$rules,'subject'=>$this,"post"=>$this->sSYSTEM->_POST));

		$p = $this->sSYSTEM->_POST;

		foreach ($rules as $ruleKey => $ruleValue){
			if (empty($p[$ruleKey]) && !empty($rules[$ruleKey]["required"]) && empty($rules[$ruleKey]["addicted"])){
				$sErrorFlag[$ruleKey] = true;
			}
		}

		if (count($sErrorFlag)){
			// Some error occurs
			$sErrorMessages[] = $this->snippetObject->get('ErrorFillIn','Please fill in all red fields');
		}

		if(isset($rules['ustid'])) {
			$sVatMessages = $this->sValidateVat();
			if(!empty($sVatMessages)) {
				$sErrorFlag["ustid"] = true;
				$sErrorMessages = array_merge($sErrorMessages, $sVatMessages);
			}
		}

		if (!$edit){
			if (!count($sErrorMessages)){
				foreach ($rules as $ruleKey => $ruleValue){
					$this->sSYSTEM->_SESSION["sRegister"]["billing"][$ruleKey] = $p[$ruleKey];
				}
			}else {
				foreach ($rules as $ruleKey => $ruleValue){
					unset($this->sSYSTEM->_SESSION["sRegister"]["billing"][$ruleKey]);
				}
			}
		}
		list($sErrorMessages,$sErrorFlag) = Enlight()->Events()->filter('Shopware_Modules_Admin_ValidateStep2_FilterResult', array($sErrorMessages,$sErrorFlag), array('edit'=>$edit,'rules'=>$rules,'subject'=>$this,"post"=>$this->sSYSTEM->_POST));

		return array("sErrorFlag"=>$sErrorFlag,"sErrorMessages"=>$sErrorMessages);
	}

	 /**
	 * Checks the shipping address (Registration)
	 * @param array $rules Defined rules / required fields from controller (register.php)
	 * @param boolean $edit
	 * @access public
	 * @return array Array with errors that may have occurred
	 */
	public function sValidateStep2ShippingAddress ($rules, $edit=false){

		$p = $this->sSYSTEM->_POST;

		foreach ($rules as $ruleKey => $ruleValue){
			if ($rules[$ruleKey]["addicted"]){
				$addictedField = array_keys($rules[$ruleKey]["addicted"]);
				if ($p[$addictedField[0]]==$rules[$ruleKey]["addicted"][$addictedField[0]] && !$p[$ruleKey]){
					$sErrorFlag[$ruleKey] = true;
				}
			}else  {
				if (!$p[$ruleKey] && $rules[$ruleKey]["required"]){
					$sErrorFlag[$ruleKey] = true;
				}

				// Fix, to support billing- and shipping-address in one step
				if (preg_match("/SHIPPING/",$ruleKey)){
					$clearedRuleKey = str_replace("SHIPPING","",$ruleKey);
					//unset($rules[$ruleKey]);
					$p[$clearedRuleKey] = $p[$ruleKey];
					$rules[$clearedRuleKey] = $rules[$ruleKey];
					unset($rules[$ruleKey]);
				}
				// --
			}
		}

		if (count($sErrorFlag)){
			// Some error occurs
			$sErrorMessages[] = $this->snippetObject->get('ErrorFillIn','Please fill in all red fields');
		}
		if (!$edit){
			if (!count($sErrorMessages)){
				foreach ($rules as $ruleKey => $ruleValue){
					$this->sSYSTEM->_SESSION["sRegister"]["shipping"][$ruleKey] = $p[$ruleKey];
				}
			}else {
				foreach ($rules as $ruleKey => $ruleValue){
					unset($this->sSYSTEM->_SESSION["sRegister"]["shipping"][$ruleKey]);
				}
			}
		}
		list($sErrorMessages,$sErrorFlag) = Enlight()->Events()->filter('Shopware_Modules_Admin_ValidateStep2Shipping_FilterResult', array($sErrorMessages,$sErrorFlag), array('edit'=>$edit,'rules'=>$rules,'subject'=>$this,"post"=>$this->sSYSTEM->_POST));

		return array("sErrorFlag"=>$sErrorFlag,"sErrorMessages"=>$sErrorMessages);
	}

	 /**
	 * Validate accont information (register)
	 * @param boolean $edit
	 * @access public
	 * @return array Array with errors that may have occurred
	 */
	public function sValidateStep1 ($edit = false)
	{
		$p = $this->sSYSTEM->_POST;

		if(isset($p["emailConfirmation"]) || isset($p["email"])) {
			$p["email"] = strtolower($p["email"]);
			// Check email
			if (empty($p["email"]) || !preg_match("/^.+@.+\\..+$/", $p["email"])){
				$sErrorFlag["email"] = true;
				$sErrorMessages[] = $this->snippetObject->get('MailFailure','Please enter a valid mail address');
			}

			// Check email confirmation if needed
			if(isset($p["emailConfirmation"])) {
				$p["emailConfirmation"] = strtolower($p["emailConfirmation"]);
				if($p["email"] != $p["emailConfirmation"]){
					$sErrorFlag["emailConfirmation"] = true;
					$sErrorMessages[] = $this->snippetObject->get('MailFailureNotEqual','The mail addresses entered are not equal');
				}
			}
		} elseif($edit && empty($p["email"])) {
			$this->sSYSTEM->_POST["email"] = $p["email"] = $this->sSYSTEM->_SESSION["sUserMail"];
		}

		if (empty($this->sSYSTEM->_SESSION['sRegister']))
		{
			$this->sSYSTEM->_SESSION['sRegister'] = new ArrayObject(array(), ArrayObject::ARRAY_AS_PROPS);
		}

		// Check password if account should be created
		if (!$p["skipLogin"] || $edit){
			if ($edit && (!$p["password"] && !$p["passwordConfirmation"])){

			}else {
				if (!$p["password"] || !$p["passwordConfirmation"] || (strlen($p["password"])<$this->sSYSTEM->sCONFIG['sMINPASSWORD'])){

					$sErrorMessages[] = Shopware()->Snippets()->getNamespace("frontend")->get('RegisterPasswordLength','',true);

					$sErrorFlag["password"] = true;
					$sErrorFlag["passwordConfirmation"] = true;
				} elseif ($p["password"]!=$p["passwordConfirmation"])  {
					$sErrorMessages[] = Shopware()->Snippets()->getNamespace("frontend")->get('AccountPasswordNotEqual', 'The passwords are not equal', true);
					$sErrorFlag["password"] = true;
					$sErrorFlag["passwordConfirmation"] = true;
				}
			}
			$this->sSYSTEM->_SESSION["sRegister"]["auth"]["accountmode"] = "0";	// Setting account-mode to ACCOUNT
		}else {
			// Assign random password to account
			$p["password"] = md5(uniqid(rand()));
			$this->sSYSTEM->_SESSION["sRegister"]["auth"]["accountmode"] = "1";	// Setting account-mode to NO_ACCOUNT
		}

		// Check if email is already registered

		if (isset($p["email"]) && ($p["email"]!=$this->sSYSTEM->_SESSION["sUserMail"])){
            $addScopeSql = "";
            if ($this->scopedRegistration == true){
                  $addScopeSql = "
                  AND subshopID = ".$this->subshopId;
            }
			$checkIfMailExists = $this->sSYSTEM->sDB_CONNECTION->GetRow("SELECT id FROM s_user WHERE email=? AND accountmode!=1 $addScopeSql",array($p["email"]));
			if (!empty($checkIfMailExists) && !$p["skipLogin"]){
				$sErrorFlag["email"] = true;
				$sErrorMessages[] = $this->snippetObject->get('MailFailureAlreadyRegistered','This mail address is already registered');
			}
		}

		// Save data in session
		if (!$edit){
			if (!count($sErrorFlag) && !count($sErrorMessages)){

				$this->sSYSTEM->_SESSION["sRegister"]["auth"]["email"] = $p["email"];
				// Receive Newsletter yes / no
				$this->sSYSTEM->_SESSION["sRegister"]["auth"]["receiveNewsletter"] = $p["receiveNewsletter"];
				if ($p["password"]){
					$this->sSYSTEM->_SESSION["sRegister"]["auth"]["password"] = md5($p["password"]);
				}else {
					unset($this->sSYSTEM->_SESSION["sRegister"]["auth"]["password"]);
				}
			}else {
				unset ($this->sSYSTEM->_SESSION["sRegister"]["auth"]["email"]);
				unset ($this->sSYSTEM->_SESSION["sRegister"]["auth"]["password"]);
			}
		}

		list($sErrorMessages,$sErrorFlag) = Enlight()->Events()->filter('Shopware_Modules_Admin_ValidateStep1_FilterResult', array($sErrorMessages,$sErrorFlag), array('edit'=>$edit,'subject'=>$this,"post"=>$this->sSYSTEM->_POST));

		return array("sErrorFlag"=>$sErrorFlag,"sErrorMessages"=>$sErrorMessages);
	}

	 /**
	 * Frontend user login
	 * @param boolean $ignoreAccountMode Allows customers who have chosen the fast registration, one-time login after registration
	 * @access public
	 * @return array Array with errors that may have occurred
	 */
	public function sLogin ($ignoreAccountMode = false){

		if (Enlight()->Events()->notifyUntil('Shopware_Modules_Admin_Login_Start', array('subject'=>$this,"ignoreAccountMode"=>$ignoreAccountMode,"post"=>$this->sSYSTEM->_POST))){
			return false;
		}

		// If fields are not set, markup these fields
		if (!$this->sSYSTEM->_POST["email"]) $sErrorFlag["email"] = true;
		if (!$this->sSYSTEM->_POST["password"] && !$this->sSYSTEM->_POST["passwordMD5"]) $sErrorFlag["password"] = true;

		if (!empty($sErrorFlag)){
			 $sErrorMessages[] = $this->snippetObject->get('LoginFailure','Wrong email or password');
  			 unset($this->sSYSTEM->_SESSION["sUserMail"]);
			 unset($this->sSYSTEM->_SESSION["sUserPassword"]);
			 unset($this->sSYSTEM->_SESSION["sUserId"]);
		}

		if (!count($sErrorMessages)){
            $addScopeSql = "";
            if ($this->scopedRegistration == true){
                $addScopeSql = "
                AND subshopID = ".$this->subshopId;
            }
			// If password is already md5-decrypted, use these one
			$password = $this->sSYSTEM->_POST["passwordMD5"] ? $this->sSYSTEM->_POST["passwordMD5"] : md5($this->sSYSTEM->_POST["password"]);
			$email = strtolower($this->sSYSTEM->_POST["email"]);

			if ($ignoreAccountMode){
				$sql = "SELECT id, customergroup FROM s_user WHERE password=? AND email=? AND active=1 AND (lockeduntil < now() OR lockeduntil IS NULL) ".$addScopeSql;
			}else {
				$sql = "SELECT id, customergroup FROM s_user WHERE password=? AND email=? AND active=1 AND accountmode!=1 AND (lockeduntil < now() OR lockeduntil IS NULL) ".$addScopeSql;
			}

			$getUser = $this->sSYSTEM->sDB_CONNECTION->GetRow($sql,array($password,$email));

			if(count($getUser))
			{
				$updateTime = $this->sSYSTEM->sDB_CONNECTION->Execute("UPDATE s_user SET lastlogin=NOW(),failedlogins = 0, lockeduntil = NULL, sessionID=? WHERE id=?",array($this->sSYSTEM->sSESSION_ID,$getUser["id"]));
				Enlight()->Events()->notify('Shopware_Modules_Admin_Login_Successful', array('subject'=>$this,'email'=>$email,'password'=>$password,'user'=>$getUser));
				$this->sSYSTEM->_SESSION["sUserMail"] = $email;
				$this->sSYSTEM->_SESSION["sUserPassword"] = $password;
				$this->sSYSTEM->_SESSION["sUserId"] = $getUser["id"];

				$this->sCheckUser();

			}else {
				// Check if account is disabled
				 $sql = "SELECT id FROM s_user WHERE password=? AND email=? AND active=0 ".$addScopeSql;
				 $getUser = $this->sSYSTEM->sDB_CONNECTION->GetOne($sql,array($password, $email));
				 if ($getUser){
				 	$sErrorMessages[] = $this->snippetObject->get('LoginFailureActive','Your account is disabled. Please contact us.');
				 } else {
					$getLockedUntilTime = Shopware()->Db()->fetchOne("
						SELECT 1 FROM s_user
						WHERE email = ? AND lockeduntil > NOW()
					",array($email));
					if (!empty($getLockedUntilTime)){
						$sErrorMessages[] = $this->snippetObject->get('LoginFailureLocked','Too many failed logins. Your account was temporary deactivated.');
					}else {
			 	 		$sErrorMessages[] = $this->snippetObject->get('LoginFailure','Wrong email or password');
					}
				 }

				 // Ticket #5427 - Prevent brute force logins
				 if (!empty($email)){
					// Update failed login counter
					$sql = "
						UPDATE s_user SET
							failedlogins = failedlogins + 1,
							lockeduntil = IF(
								failedlogins > 4,
								DATE_ADD(NOW(), INTERVAL (failedlogins + 1) * 30 SECOND),
								NULL
							)
						WHERE email = ? ".$addScopeSql;
					Shopware()->Db()->query($sql, array($email));
				 } // Ticket #5427 - Prevent brute force logins

				 Enlight()->Events()->notify('Shopware_Modules_Admin_Login_Failure', array('subject'=>$this,'email'=>$email,'password'=>$password,'error'=>$sErrorMessages));

				 unset($this->sSYSTEM->_SESSION["sUserMail"]);
				 unset($this->sSYSTEM->_SESSION["sUserPassword"]);
				 unset($this->sSYSTEM->_SESSION["sUserId"]);
			} // user was found
		} // All fields fill

		list($sErrorMessages,$sErrorFlag) = Enlight()->Events()->filter('Shopware_Modules_Admin_Login_FilterResult', array($sErrorMessages,$sErrorFlag), array('subject'=>$this,'email'=>$email,'password'=>$password,'error'=>$sErrorMessages));

		return array("sErrorFlag"=>$sErrorFlag,"sErrorMessages"=>$sErrorMessages);
	}

	 /**
	 * Verification of user authorization (logged in) on all secured pages (checkout,account)
	 * @access public
	 * @return boolean
	 */
	public function sCheckUser (){

		if (Enlight()->Events()->notifyUntil('Shopware_Modules_Admin_CheckUser_Start', array('subject'=>$this))){
			return false;
		}
		if (empty($this->sSYSTEM->_SESSION["sUserMail"]) || empty($this->sSYSTEM->_SESSION["sUserPassword"]) || empty($this->sSYSTEM->_SESSION["sUserId"])){
			 unset($this->sSYSTEM->_SESSION["sUserMail"]);
			 unset($this->sSYSTEM->_SESSION["sUserPassword"]);
			 unset($this->sSYSTEM->_SESSION["sUserId"]);
			 return false;
		}


		$sql = "
			SELECT * FROM s_user
			WHERE password=? AND email=? AND id=? AND UNIX_TIMESTAMP(lastlogin)>=(UNIX_TIMESTAMP(now())-?)
		";

        $timeOut = $this->sSYSTEM->sCONFIG['sUSERTIMEOUT'];
        $timeOut = !empty($timeOut) ? $timeOut : 7200;
		$getUser = $this->sSYSTEM->sDB_CONNECTION->GetRow($sql,array(
			$this->sSYSTEM->_SESSION["sUserPassword"],
			$this->sSYSTEM->_SESSION["sUserMail"],
			$this->sSYSTEM->_SESSION["sUserId"],
            $timeOut
		));

		$getUser = Enlight()->Events()->filter('Shopware_Modules_Admin_CheckUser_FilterGetUser', $getUser, array('subject'=>$this,'sql'=>$sql,'session'=>$this->sSYSTEM->_SESSION));


		if(!empty($getUser["id"]))
		{
			$this->sSYSTEM->sUSERGROUPDATA = $this->sSYSTEM->sDB_CONNECTION->GetRow("
				SELECT * FROM s_core_customergroups
				WHERE groupkey=?
			",array($getUser["customergroup"]));

			if ($this->sSYSTEM->sUSERGROUPDATA["mode"]){
				$this->sSYSTEM->sUSERGROUP = "EK";
			} else {
				$this->sSYSTEM->sUSERGROUP = $getUser["customergroup"];
			}
            $this->sSYSTEM->sUSERGROUP = $getUser["customergroup"];

			if(!empty($getUser["pricegroupID"]))
			{
				$sql = "SELECT id FROM s_core_customerpricegroups WHERE id=? AND active=1";
				$getUser["pricegroupID"] = $this->sSYSTEM->sDB_CONNECTION->GetOne($sql,array($getUser["pricegroupID"]));
				if(!empty($getUser["pricegroupID"]))
				{
					$this->sSYSTEM->sUSERGROUP = "PG".$getUser["pricegroupID"];
				}
			}

			$this->sSYSTEM->_SESSION["sUserGroup"] = $this->sSYSTEM->sUSERGROUP;
			$this->sSYSTEM->_SESSION["sUserGroupData"] = $this->sSYSTEM->sUSERGROUPDATA;

			$updateTime = $this->sSYSTEM->sDB_CONNECTION->Execute("UPDATE s_user SET lastlogin=NOW(), sessionID=? WHERE id=?",array($this->sSYSTEM->sSESSION_ID,$getUser["id"]));
			Enlight()->Events()->notify('Shopware_Modules_Admin_CheckUser_Successful', array('subject'=>$this,'session'=>$this->sSYSTEM->_SESSION,'user'=>$getUser));

			return true;
		} else {
			 unset($this->sSYSTEM->_SESSION["sUserMail"]);
			 unset($this->sSYSTEM->_SESSION["sUserPassword"]);
			 unset($this->sSYSTEM->_SESSION["sUserId"]);
			 Enlight()->Events()->notify('Shopware_Modules_Admin_CheckUser_Failure', array('subject'=>$this,'session'=>$this->sSYSTEM->_SESSION,'user'=>$getUser));

			 return false;
		}
	}

	 /**
	 * Loads the translation for the country table
	 * @param array $country - (optional) translation for a specific country
	 * @access public
	 * @return array - translated country data
	 */
	public function sGetCountryTranslation($country=""){
		// Load Translation
		$sql = "SELECT objectdata FROM s_core_translations WHERE objecttype='config_countries' AND objectlanguage=?";

        $param = array($this->sSYSTEM->sLanguageData[$this->sSYSTEM->sLanguage]["isocode"]);
		$getTranslation = $this->sSYSTEM->sDB_CONNECTION->CacheGetRow($this->sSYSTEM->sCONFIG['sCACHECOUNTRIES'],$sql, $param);

		if ($getTranslation["objectdata"]){
			$object = unserialize($getTranslation["objectdata"]);
		}

		if (!$country) return $object;

		// Pass (possible) translation to country
		if ($object[$country["id"]]["countryname"]){
			$country["countryname"] = $object[$country["id"]]["countryname"];
		}
		if ($object[$country["id"]]["notice"]){
			$country["notice"] = $object[$country["id"]]["notice"];
		}

        if ($object[$country["id"]]["active"]){
			$country["active"] = $object[$country["id"]]["active"];
		}

		return $country;
	}

	 /**
	 * Loads the translation for the different shipping methods
	 * @param array $dispatch - translation for a specific dispatch
	 * @access public
	 * @return array - translated dispatch
	 */
	public function sGetDispatchTranslation($dispatch=""){
		// Load Translation
		$sql = "
		SELECT objectdata FROM s_core_translations WHERE objecttype='config_dispatch' AND objectlanguage=?";
        $params = array($this->sSYSTEM->sLanguageData[$this->sSYSTEM->sLanguage]["isocode"]);
		$getTranslation = $this->sSYSTEM->sDB_CONNECTION->CacheGetRow($this->sSYSTEM->sCONFIG['sCACHECOUNTRIES'],$sql, $params);

		if ($getTranslation["objectdata"]){
			$object = unserialize($getTranslation["objectdata"]);
		}

		if (!$dispatch) return $object;

		// Pass (possible) translation to country
		if ($object[$dispatch["id"]]["dispatch_name"]){
			$dispatch["name"] = $object[$dispatch["id"]]["dispatch_name"];
		}
		if ($object[$dispatch["id"]]["dispatch_description"]){
			$dispatch["description"] = $object[$dispatch["id"]]["dispatch_description"];
		}
		if ($object[$dispatch["id"]]["dispatch_status_link"]){
			$dispatch["status_link"] = $object[$dispatch["id"]]["dispatch_status_link"];
		}

		return $dispatch;
	}

	 /**
	 * Loads translation for the different paymentmeans
	 * @param array $payment - translation for a specific payment
	 * @access public
	 * @return array - translated data
	 */
	public function sGetPaymentTranslation($payment=""){

		// Load Translation
		$sql = "
		SELECT objectdata FROM s_core_translations WHERE objecttype='config_payment' AND objectlanguage=?";
        $params = array($this->sSYSTEM->sLanguageData[$this->sSYSTEM->sLanguage]["isocode"]);

		$getTranslation = $this->sSYSTEM->sDB_CONNECTION->CacheGetRow($this->sSYSTEM->sCONFIG['sCACHECOUNTRIES'],$sql, $params);

		if (!empty($getTranslation["objectdata"])){
			$object = unserialize($getTranslation["objectdata"]);
		}

		if (!$payment) return $object;

		// Pass (possible) translation to payment
		if (!empty($object[$payment["id"]]["description"])){
			$payment["description"] = $object[$payment["id"]]["description"];
		}
		if (!empty($object[$payment["id"]]["additionalDescription"])){
			$payment["additionaldescription"] = $object[$payment["id"]]["additionalDescription"];
		}

		return $payment;
	}

    /**
     * @return array|mixed
     */
    public function sGetCountryStateTranslation()
    {
        if(Shopware()->Shop()->get('skipbackend')) {
            return array();
        }
        $language = Shopware()->Shop()->get('isocode');
        $fallback = Shopware()->Shop()->get('fallback');
        $cacheTime = Shopware()->Config()->get('cacheCountries');

        $sql = "
            SELECT objectdata FROM s_core_translations
            WHERE objecttype = 'config_country_states'
            AND objectkey = 1
            AND objectlanguage = ?
		";
        $translation = $this->sSYSTEM->sDB_CONNECTION->CacheGetOne(
            $cacheTime, $sql, array($language)
        );

        if (!empty($translation)) {
            $translation = unserialize($translation);
        } else {
            $translation = array();
        }
        if (!empty($fallback)) {
            $sql = "
                SELECT objectdata FROM s_core_translations
                WHERE objecttype = 'config_country_states'
                AND objectkey = 1
                AND objectlanguage = ?
            ";
            $translationFallback = $this->sSYSTEM->sDB_CONNECTION->CacheGetOne(
                $cacheTime, $sql, array($fallback)
            );
            if (!empty($translationFallback)) {
                $translationFallback = unserialize($translationFallback);
                $translation = array_merge($translationFallback, $translation);
            }
        }
        return $translation;
    }

	 /**
	 * Get country list
	 * @access public
	 * @return array - country list
	 */
	public function sGetCountryList()
	{
		$getCountries = $this->sSYSTEM->sDB_CONNECTION->CacheGetAll($this->sSYSTEM->sCONFIG['sCACHECOUNTRIES'],"SELECT * FROM s_core_countries WHERE active = 1 ORDER BY position, countryname ASC");

		$object = $this->sGetCountryTranslation();
        $stateTranslation = $this->sGetCountryStateTranslation();


		foreach ($getCountries as $key => $v)
		{

			if (isset($object[$v["id"]]["active"])){
                if (!$object[$v["id"]]["active"]) {
                    unset($getCountries[$key]);
                    continue;
                }
			}


            $getCountries[$key]["states"] = array();
            if (!empty($v["display_state_in_registration"])){
                // Get country states
                $states = Shopware()->Db()->fetchAssoc("
                    SELECT * FROM s_core_countries_states
                    WHERE countryID = ? AND active = 1
                    ORDER BY position, name ASC
                ", array($v["id"]));
                foreach($states as $stateId => $state){
                    if(isset($stateTranslation[$stateId])) {
                        $states[$stateId] = array_merge($state, $stateTranslation[$stateId]);
                    }
                }
                $getCountries[$key]["states"] = $states;
            }
			if (!empty($object[$v["id"]]["countryname"])){
				$getCountries[$key]["countryname"] = $object[$v["id"]]["countryname"];
			}
			if (!empty($object[$v["id"]]["notice"])){
				$getCountries[$key]["notice"] = $object[$v["id"]]["notice"];
			}
			if ($getCountries[$key]["id"]==$this->sSYSTEM->_POST['country'] || $getCountries[$key]["id"]==$this->sSYSTEM->_POST['countryID'] ){
				$getCountries[$key]["flag"] = true;
			} else {
				$getCountries[$key]["flag"] = false;
			}
		}

		$getCountries = Enlight()->Events()->filter('Shopware_Modules_Admin_GetCountries_FilterResult', $getCountries, array('subject'=>$this));

		return $getCountries;

	}


	 /**
	 * Registration - storage main user data (login data / entry in s_user)
	 * @param array $userObject - Array with all information from the registration process
	 * @access public
	 * @return int - insert id
	 */
	public function sSaveRegisterMainData($userObject){

		// Support for merchants
		if ($userObject["billing"]["sValidation"]){
			$sMerchant = $userObject["billing"]["sValidation"];
		}else {
			$sMerchant = "";
		}

		if (empty($this->sSYSTEM->sCONFIG["sDefaultCustomerGroup"])) $this->sSYSTEM->sCONFIG["sDefaultCustomerGroup"] = "EK";
		$referer = $this->sSYSTEM->_SESSION['sReferer'];

        if (!empty($this->sSYSTEM->_SESSION['sPartner']))
        {
        	$sql = 'SELECT id FROM s_emarketing_partner WHERE idcode = ?';
        	$partner = (int) $this->sSYSTEM->sDB_CONNECTION->GetOne($sql, array($this->sSYSTEM->_SESSION['sPartner']));
        }

        $data = array(
	        $userObject["auth"]["password"],
	        $userObject["auth"]["email"],
	        $userObject["payment"]["object"]["id"],
	        $userObject["auth"]["accountmode"],
	        empty($sMerchant) ? "" : $sMerchant,
	        $this->sSYSTEM->sSESSION_ID,
	        empty($partner) ? "" : $partner,
	        $this->sSYSTEM->sCONFIG["sDefaultCustomerGroup"],
	        $this->sSYSTEM->sLanguageData[$this->sSYSTEM->sLanguage]["isocode"],
            $this->subshopId,
	        empty($referer) ? "" : $referer

        );
		$sql = '
			INSERT INTO s_user
			(
				password, email, paymentID, active, accountmode,
			 	validation, firstlogin,sessionID, affiliate, customergroup,
			 	language, subshopID, referer
			)
			VALUES (?,?,?,1,?,?,NOW(),?,?,?,?,?,?)
		';

		list($sql,$data) = Enlight()->Events()->filter('Shopware_Modules_Admin_SaveRegisterMainData_FilterSql', array($sql,$data), array('subject'=>$this));

		$saveUserData = $this->sSYSTEM->sDB_CONNECTION->Execute($sql, $data);
		Enlight()->Events()->notify('Shopware_Modules_Admin_SaveRegisterMainData_Return', array('subject'=>$this,'insertObject'=>$saveUserData));

        $userId = $this->sSYSTEM->sDB_CONNECTION->Insert_ID();

        $sql = "
            INSERT INTO s_user_attributes (userID) VALUES (?)
        ";
        $data = array($userId);
        list($sql,$data) = Enlight()->Events()->filter('Shopware_Modules_Admin_SaveRegisterMainDataAttributes_FilterSql', array($sql,$data), array('subject'=>$this));
        $saveAttributeData = $this->sSYSTEM->sDB_CONNECTION->Execute($sql, $data);
        Enlight()->Events()->notify('Shopware_Modules_Admin_SaveRegisterMainDataAttributes_Return', array('subject'=>$this,'insertObject'=>$saveAttributeData));

		return $userId;
	}

	/**
	 * Registration - storage of email in maillist
	 * @param array $userObject - Array with all information from the registration process
	 * @access public
	 * @return null
	 */
	public function sSaveRegisterNewsletter($userObject){
		// Check for duplicates
		$checkDuplicate = $this->sSYSTEM->sDB_CONNECTION->GetRow("
		SELECT id FROM s_campaigns_mailaddresses
		WHERE email=?",array($userObject["auth"]["email"]));

		if (empty($checkDuplicate["id"])){
			$saveNewsletter = $this->sSYSTEM->sDB_CONNECTION->Execute("
			INSERT INTO s_campaigns_mailaddresses (customer, groupID, email)
			VALUES (1,0,?)",array($userObject["auth"]["email"]));
		}
	}

	/**
	 * Registration - storage of billing address
	 * @param int $userID - user id (s_user.id) from sSaveRegisterMain
	 * @param array $userObject - Array with all information from the registration process
	 * @access public
	 * @return int - insert id / row id
	 */
	public function sSaveRegisterBilling($userID, $userObject){
			if ($userObject["billing"]["birthmonth"]=="-") unset($userObject["billing"]["birthmonth"]);
			if ($userObject["billing"]["birthday"]=="--") unset($userObject["billing"]["birthday"]);
			if ($userObject["billing"]["birthyear"]=="----") unset($userObject["billing"]["birthyear"]);

			if (!empty($userObject["billing"]["birthmonth"]) &&
			!empty($userObject["billing"]["birthday"]) &&
			!empty($userObject["billing"]["birthyear"])){
				$date = $userObject["billing"]["birthyear"]."-".$userObject["billing"]["birthmonth"]."-".$userObject["billing"]["birthday"];

				$date = date("Y-m-d",strtotime($date));

			}else {
				$date = "0000-00-00";
			}
			$userObject = $userObject["billing"];
			$data = array(
				$userID,
				empty($userObject["company"]) ? "" : $userObject["company"],
				empty($userObject["department"]) ? "" : $userObject["department"],
				empty($userObject["salutation"]) ? "" : $userObject["salutation"],
				$userObject["firstname"],
				$userObject["lastname"],
				$userObject["street"],
				$userObject["streetnumber"],
				$userObject["zipcode"],
				$userObject["city"],
				empty($userObject["phone"]) ? "" : $userObject["phone"],
				empty($userObject["fax"]) ? "" : $userObject["fax"],
				$userObject["country"],
                empty($userObject["stateID"]) ? 0 : $userObject["stateID"] ,
                empty($userObject["ustid"]) ? "" : $userObject["ustid"],
				$date
			);

			$sqlBilling = "INSERT INTO s_user_billingaddress
			(userID,company,department, salutation,firstname,lastname,
			street,streetnumber,zipcode,city,phone,
			fax,countryID,stateID,ustid,birthday)
			VALUES
			(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

			// Trying to insert
			list($sqlBilling,$data) = Enlight()->Events()->filter('Shopware_Modules_Admin_SaveRegisterBilling_FilterSql', array($sqlBilling,$data), array('subject'=>$this));

			$saveUserData = $this->sSYSTEM->sDB_CONNECTION->Execute($sqlBilling,$data);
			Enlight()->Events()->notify('Shopware_Modules_Admin_SaveRegisterBilling_Return', array('subject'=>$this,'insertObject'=>$saveUserData));

            //new attribute tables.
            $billingID = $this->sSYSTEM->sDB_CONNECTION->Insert_ID();
            $attributeData = array(
                $billingID,
                empty($userObject["text1"]) ? "" : $userObject["text1"],
                empty($userObject["text2"]) ? "" : $userObject["text2"],
                empty($userObject["text3"]) ? "" : $userObject["text3"],
                empty($userObject["text4"]) ? "" : $userObject["text4"],
                empty($userObject["text5"]) ? "" : $userObject["text5"],
                empty($userObject["text6"]) ? "" : $userObject["text6"],
            );
            $sqlAttribute = "INSERT INTO s_user_billingaddress_attributes
     			(billingID, text1, text2, text3, text4, text5, text6)
     			VALUES
     			(?,?,?,?,?,?,?)";

            list($sqlAttribute,$attributeData) = Enlight()->Events()->filter('Shopware_Modules_Admin_SaveRegisterBillingAttributes_FilterSql', array($sqlAttribute,$attributeData), array('subject'=>$this));
            $saveAttributeData = $this->sSYSTEM->sDB_CONNECTION->Execute($sqlAttribute,$attributeData);
            Enlight()->Events()->notify('Shopware_Modules_Admin_SaveRegisterBillingAttributes_Return', array('subject'=>$this,'insertObject'=>$saveAttributeData));

			return $billingID;
	}

	/**
	 * Registration - storage of shipping address
	 * @param int $userID - user id (s_user.id) from sSaveRegisterMain
	 * @param array $userObject - Array with all information from the registration process
	 * @access public
	 * @return int - insert id / row id
	 */
	public function sSaveRegisterShipping($userID, $userObject){
			$sqlShipping = "INSERT INTO s_user_shippingaddress
			(userID,company,department, salutation,firstname,lastname,
			street,streetnumber,zipcode,city, countryID,stateID)
			VALUES
			(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )";
			$sqlShipping = Enlight()->Events()->filter('Shopware_Modules_Admin_SaveRegisterShipping_FilterSql', $sqlShipping, array('subject'=>$this,'user'=>$userObject,'id'=>$userID));
            $shippingParams = array(
                $userID,
                $userObject["shipping"]["company"],
                $userObject["shipping"]["department"],
                $userObject["shipping"]["salutation"],
                $userObject["shipping"]["firstname"],
                $userObject["shipping"]["lastname"],
                $userObject["shipping"]["street"],
                $userObject["shipping"]["streetnumber"],
                $userObject["shipping"]["zipcode"],
                $userObject["shipping"]["city"],
                $userObject["shipping"]["country"],
                $userObject["shipping"]["stateID"]
            );
			// Trying to insert
			$saveUserData = $this->sSYSTEM->sDB_CONNECTION->Execute($sqlShipping, $shippingParams);
			Enlight()->Events()->notify('Shopware_Modules_Admin_SaveRegisterShipping_Return', array('subject'=>$this,'insertObject'=>$saveUserData));

            //new attribute table
            $shippingId = $this->sSYSTEM->sDB_CONNECTION->Insert_ID();
            $sqlAttributes = "INSERT INTO s_user_shippingaddress_attributes
     			(shippingID, text1, text2, text3, text4, text5, text6)
     			VALUES
     			(?, ?, ?, ?, ?, ?, ?)";
            $sqlAttributes = Enlight()->Events()->filter('Shopware_Modules_Admin_SaveRegisterShippingAttributes_FilterSql', $sqlAttributes, array('subject'=>$this,'user'=>$userObject,'id'=>$userID));
            $attributeParams = array(
                $shippingId,
                $userObject["shipping"]["text1"],
                $userObject["shipping"]["text2"],
                $userObject["shipping"]["text3"],
                $userObject["shipping"]["text4"],
                $userObject["shipping"]["text5"],
                $userObject["shipping"]["text6"]
            );
            $saveAttributeData = $this->sSYSTEM->sDB_CONNECTION->Execute($sqlAttributes, $attributeParams);
            Enlight()->Events()->notify('Shopware_Modules_Admin_SaveRegisterShippingAttributes_Return', array('subject'=>$this,'insertObject'=>$saveAttributeData));

			return $shippingId;
	}

	/**
	 * Registration - Mail register confirmation
	 * @param string $email - Recipient mail
	 * @access public
	 * @return null
	 */
	public function sSaveRegisterSendConfirmation($email)
	{
		if (Enlight()->Events()->notifyUntil('Shopware_Modules_Admin_SaveRegisterSendConfirmation_Start', array('subject'=>$this,'email'=>$email))){
			return false;
		}

        $context = array(
            'sMAIL'     => $email,
            'sShop'     => Shopware()->Config()->ShopName,
            'sShopURL'  => 'http://' . Shopware()->Config()->BasePath,
            'sConfig'   => Shopware()->Config(),
        );


        $namespace = Shopware()->Snippets()->getNamespace('frontend/account/index');
        foreach ($this->sSYSTEM->_SESSION["sRegister"]["billing"] as $key => $value)
        {
            if ($key == "salutation" ) {
                $value = ($value == "ms") ? $namespace->get('AccountSalutationMrs', 'Ms') : $namespace->get('AccountSalutationMr', 'Mr');
            }

            $context[$key] = $value;
        }

        $mail = Shopware()->TemplateMail()->createMail('sREGISTERCONFIRMATION', $context);
        $mail->addTo($email);

        if (!empty($this->sSYSTEM->sCONFIG["sSEND_CONFIRM_MAIL"])) {
            $mail->addBcc($this->sSYSTEM->sCONFIG['sMAIL']);
        }

        Enlight()->Events()->notify('Shopware_Modules_Admin_SaveRegisterSendConfirmation_BeforeSend', array('subject'=>$this,'mail'=>$mail));

        $mail->send();
	}

	/**
	 * Register - finaly check data and call the different sub functions (saveBilling etc.)
	 * @param array $paymentObject - choosen payment data
	 * @return boolean
	 */
	public function sSaveRegister ($paymentObject=null){
		if (Enlight()->Events()->notifyUntil('Shopware_Modules_Admin_SaveRegister_Start', array('subject'=>$this))){
			return false;
		}
		if(!$this->sSYSTEM->_SESSION["sRegisterFinished"])
		{
			if (empty($this->sSYSTEM->_SESSION["sRegister"]["payment"]["object"]["id"])) $this->sSYSTEM->_SESSION["sRegister"]["payment"]["object"]["id"]  = $this->sSYSTEM->sCONFIG['sDEFAULTPAYMENT'];
			$neededFields["auth"] = array("email","password");
			$neededFields["billing"] = array("salutation","firstname","lastname","street","streetnumber","zipcode","city","country");
			$neededFields["payment"] = array("object"=>array("id"));

			$neededFields = Enlight()->Events()->filter('Shopware_Modules_Admin_SaveRegister_FilterNeededFields', $neededFields, array('subject'=>$this));

			// Check for needed fields
			foreach ($neededFields as $needKey => $needValue){
				foreach ($neededFields[$needKey] as $fieldKey => $fieldValue){
					if (is_array($fieldValue)){

						$objKey = $fieldValue[0];

						if (empty($this->sSYSTEM->_SESSION["sRegister"][$needKey][$fieldKey][$objKey])){
							$errorFields[] = $needKey."#1($needKey)($fieldKey)($objKey)->".$fieldValue;
						}
					}else {
						if (empty($this->sSYSTEM->_SESSION["sRegister"][$needKey][$fieldValue])){
							$errorFields[] = $needKey."#2->".$fieldValue;
						}
					}
				}
			}

			$errorFields = Enlight()->Events()->filter('Shopware_Modules_Admin_SaveRegister_FilterErrors', $errorFields, array('subject'=>$this));

			// Check for occured errors
			if (count($errorFields)){
				if (!$_COOKIE["SHOPWARESID"]){
					$noCookies = "NO SESSION-COOKIE";
				}
				$this->sSYSTEM->E_CORE_WARNING("sSaveRegister #00","Fields are missing $noCookies - ".$this->sSYSTEM->sSESSION_ID." - ".print_r($errorFields,true));
				die ("Session Lost - Bitte aktivieren Sie Cookies in Ihrem Browser!");
				return false;
			}else {

				$userObject = $this->sSYSTEM->_SESSION["sRegister"];

				if (!$userObject["payment"]["object"]["id"]) $userObject["payment"]["object"]["id"] = $this->sSYSTEM->sCONFIG['sPAYMENTDEFAULT'];

				$userID = $this->sSaveRegisterMainData($userObject);

				if ($this->sSYSTEM->sDB_CONNECTION->ErrorMsg() || !$userID){
					$this->sSYSTEM->E_CORE_WARNING("sSaveRegister #01","Could not save data".$this->sSYSTEM->sDB_CONNECTION->ErrorMsg().print_r($userObject));
					die("sSaveRegister #01"."Could not save data".$this->sSYSTEM->sDB_CONNECTION->ErrorMsg());
				}

				if ($userObject["auth"]["receiveNewsletter"]){
					$this->sSaveRegisterNewsletter($userObject);
				}

				// Insert billing-address
				$userBillingID = $this->sSaveRegisterBilling($userID,$userObject);

				if ($this->sSYSTEM->sDB_CONNECTION->ErrorMsg() || !$userBillingID){
					$this->sSYSTEM->E_CORE_WARNING("sSaveRegister #02","Could not save data (billing-adress)".$this->sSYSTEM->sDB_CONNECTION->ErrorMsg().print_r($userObject,true));
					die("Could not save data (billing-adress)".$this->sSYSTEM->sDB_CONNECTION->ErrorMsg());
				}


				if ($this->sSYSTEM->sCONFIG['sSHOPWAREMANAGEDCUSTOMERNUMBERS']){
					if (!Enlight()->Events()->notifyUntil('Shopware_Modules_Admin_SaveRegister_GetCustomerNumber', array('subject'=>$this,'id'=>$userID))){
						$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`+1 WHERE `s_order_number`.`name` ='user' AND `s_user_billingaddress`.`userID`=?";
						$this->sSYSTEM->sDB_CONNECTION->Execute($sql,array($userID));
					}
				}

				// Insert shipping-adress
				if (count($userObject["shipping"])){
					$userShippingID = $this->sSaveRegisterShipping($userID,$userObject);
					if ($this->sSYSTEM->sDB_CONNECTION->ErrorMsg() || !$userShippingID){
						$this->sSYSTEM->E_CORE_WARNING("sSaveRegister #02","Could not save data (shipping-address)".$this->sSYSTEM->sDB_CONNECTION->ErrorMsg().print_r($userObject,true));
						return false;
					}
				}

				$uMail = $userObject["auth"]["email"];
				$uPass = $userObject["auth"]["password"];

				if ($userObject["auth"]["accountmode"]<1){
					$this->sSaveRegisterSendConfirmation($uMail);
					$this->sSYSTEM->_SESSION["sOneTimeAccount"] = false;
				}else {
					$this->sSYSTEM->_SESSION["sOneTimeAccount"] = true;
				}

				// Save referer where user comes from
				if(!empty($this->sSYSTEM->_SESSION['sReferer']))
				{
					$referer = addslashes($this->sSYSTEM->_SESSION['sReferer']);
					$sql = "
					INSERT INTO
						`s_emarketing_referer` ( `userID` , `referer` , `date` )
					VALUES (
						?, ?, NOW()
					);";
					$this->sSYSTEM->sDB_CONNECTION->Execute($sql,array($userID,$referer));
				}

				$this->sSYSTEM->_POST["email"] = $uMail;
				$this->sSYSTEM->_POST["passwordMD5"] = $uPass;

				// Login user
				$chkUserLogin = $this->sLogin(true);

				// The user is now registered
				$this->sSYSTEM->_SESSION["sRegisterFinished"] = true;

				Enlight()->Events()->notify('Shopware_Modules_Admin_SaveRegister_Successful', array('subject'=>$this,'id'=>$userID,'billingID'=>$userBillingID,'shippingID'=>$userShippingID));

				// Garbage
				unset($this->sSYSTEM->_SESSION['sRegister']);
			}

			return true;
		}else {
			$this->sSYSTEM->_POST["email"] = $this->sSYSTEM->_SESSION['sUserMail'];
			$this->sSYSTEM->_POST["passwordMD5"] = $this->sSYSTEM->_SESSION['sUserPassword'];
			$chkUserLogin = $this->sLogin($this->sSYSTEM->_SESSION["sOneTimeAccount"]);
			return true;
		}

	}




	/**
	 * Account - get purchased instant downloads
	 * @access public
	 * @return array - Data from orders who contains instant downloads
	 */
	public function sGetDownloads (){
		$getOrders = $this->sSYSTEM->sDB_CONNECTION->GetAll("
		SELECT id, ordernumber, invoice_amount, invoice_amount_net, invoice_shipping, invoice_shipping_net, DATE_FORMAT(ordertime,'%d.%m.%Y %H:%i') AS datum, status, comment
		FROM s_order WHERE userID=? AND s_order.status>=0 ORDER BY ordertime DESC LIMIT 10
		",array($this->sSYSTEM->_SESSION["sUserId"]));

		foreach ($getOrders as $orderKey => $orderValue){

			if (($this->sSYSTEM->sCONFIG['sARTICLESOUTPUTNETTO'] && !$this->sSYSTEM->sUSERGROUPDATA["tax"]) || (!$this->sSYSTEM->sUSERGROUPDATA["tax"] && $this->sSYSTEM->sUSERGROUPDATA["id"])){
				$getOrders[$orderKey]["invoice_amount"] = $this->sSYSTEM->sMODULES['sArticles']->sFormatPrice($orderValue["invoice_amount_net"]);
				$getOrders[$orderKey]["invoice_shipping"] = $this->sSYSTEM->sMODULES['sArticles']->sFormatPrice($orderValue["invoice_shipping_net"]);
			}else {
				$getOrders[$orderKey]["invoice_amount"] = $this->sSYSTEM->sMODULES['sArticles']->sFormatPrice($orderValue["invoice_amount"]);
				$getOrders[$orderKey]["invoice_shipping"] = $this->sSYSTEM->sMODULES['sArticles']->sFormatPrice($orderValue["invoice_shipping"]);
			}

			$getOrderDetails = $this->sSYSTEM->sDB_CONNECTION->GetAll("
			SELECT * FROM s_order_details WHERE orderID={$orderValue["id"]}
			");

			if (!count($getOrderDetails)){
				unset($getOrders[$orderKey]);
			}else {
				$foundESD = false;
				foreach ($getOrderDetails as $orderDetailsKey => $orderDetailsValue){
					$getOrderDetails[$orderDetailsKey]["amount"] = $this->sSYSTEM->sMODULES['sArticles']->sFormatPrice(round($orderDetailsValue["price"] * $orderDetailsValue["quantity"],2));
					$getOrderDetails[$orderDetailsKey]["price"] = $this->sSYSTEM->sMODULES['sArticles']->sFormatPrice($orderDetailsValue["price"]);
					// Check for serial
					if ($getOrderDetails[$orderDetailsKey]["esdarticle"]){
						$foundESD = true;
						$numbers = array();
						$getSerial = $this->sSYSTEM->sDB_CONNECTION->GetAll("
						SELECT serialnumber FROM s_articles_esd_serials, s_order_esd WHERE userID=".$this->sSYSTEM->_SESSION["sUserId"]."
						AND orderID={$orderValue["id"]} AND orderdetailsID={$orderDetailsValue["id"]}
						AND s_order_esd.serialID=s_articles_esd_serials.id
						");
						foreach ($getSerial as $serial){
							$numbers[] = $serial["serialnumber"];
						}
						$getOrderDetails[$orderDetailsKey]["serial"] =  implode(",",$numbers);
						// Building download-link
						$getOrderDetails[$orderDetailsKey]["esdLink"] = $this->sSYSTEM->sCONFIG["sBASEFILE"].'?sViewport=account&sAction=download&esdID='.$orderDetailsValue['id'];
						//$getOrderDetails[$orderDetailsKey]["esdLink"] = "http://".$this->sSYSTEM->sCONFIG["sBASEPATH"]."/engine/core/php/loadesd.php?id=".$orderDetailsValue["id"];
					}else {
						unset($getOrderDetails[$orderDetailsKey]);
					}
					// -- End of serial check
				}
				if (!empty($foundESD)){
					$getOrders[$orderKey]["details"] = $getOrderDetails;
				}else {
					unset($getOrders[$orderKey]);
				}
			}
		}

		$getOrders = Enlight()->Events()->filter('Shopware_Modules_Admin_GetDownloads_FilterResult', $getOrders, array('subject'=>$this,'id'=>$this->sSYSTEM->_SESSION["sUserId"]));

		return $getOrders;

	}

	/**
	 * Account - Get all orders that the user did
	 * @access public
	 * @return array - Array with order data / positions
	 */
	public function sGetOpenOrderData ()
	{
		$sql = "
			SELECT o.*, cu.templatechar as currency_html, DATE_FORMAT(ordertime,'%d.%m.%Y %H:%i') AS datum
			FROM s_order o
			LEFT JOIN s_core_currencies as cu
			ON o.currency = cu.currency
			WHERE userID=? AND status != -1
			AND subshopID = ?
			ORDER BY ordertime DESC LIMIT 10
		";
		$getOrders = $this->sSYSTEM->sDB_CONNECTION->GetAll($sql,array($this->sSYSTEM->_SESSION["sUserId"],$this->sSYSTEM->sSubShop["id"]));

		foreach ($getOrders as $orderKey => $orderValue){

			$getOrders[$orderKey]["invoice_amount"] = $this->sSYSTEM->sMODULES['sArticles']->sFormatPrice($orderValue["invoice_amount"]);
			$getOrders[$orderKey]["invoice_shipping"] = $this->sSYSTEM->sMODULES['sArticles']->sFormatPrice($orderValue["invoice_shipping"]);


			$getOrderDetails = $this->sSYSTEM->sDB_CONNECTION->GetAll("
			SELECT * FROM s_order_details WHERE orderID={$orderValue["id"]} ORDER BY id ASC
			");

			if (!count($getOrderDetails)){
				unset($getOrders[$orderKey]);
			}else {

				/** GET ARTICLE DETAILS START - @date: 05-24-2011 */
				$active = 1;
				/** GET ARTICLE DETAILS END */

				foreach ($getOrderDetails as $orderDetailsKey => $orderDetailsValue){
					$getOrderDetails[$orderDetailsKey]["amount"] = $this->sSYSTEM->sMODULES['sArticles']->sFormatPrice(round($orderDetailsValue["price"] * $orderDetailsValue["quantity"],2));
					$getOrderDetails[$orderDetailsKey]["price"] = $this->sSYSTEM->sMODULES['sArticles']->sFormatPrice($orderDetailsValue["price"]);

					/** GET ARTICLE DETAILS START - @date: 05-24-2011 */
					$tmpArticle = $this->sSYSTEM->sMODULES['sArticles']->sGetPromotionById('fix', 0, $getOrderDetails[$orderDetailsKey]['articleID']);

					if(!empty($tmpArticle) && is_array($tmpArticle)) {

						// Set article in activate state
						$getOrderDetails[$orderDetailsKey]['active'] = 1;
						if(!empty($tmpArticle['purchaseunit'])) {
							$getOrderDetails[$orderDetailsKey]['purchaseunit'] = $tmpArticle['purchaseunit'];
						}

						if(!empty($tmpArticle['referenceunit'])) {
							$getOrderDetails[$orderDetailsKey]['referenceunit'] = $tmpArticle['referenceunit'];
						}

						if(!empty($tmpArticle['referenceprice'])) {
							$getOrderDetails[$orderDetailsKey]['referenceprice'] = $tmpArticle['referenceprice'];
						}

						if(!empty($tmpArticle['sUnit']) && is_array($tmpArticle['sUnit'])) {
							$getOrderDetails[$orderDetailsKey]['sUnit'] = $tmpArticle['sUnit'];
						}

						if(!empty($tmpArticle['price'])) {
							$getOrderDetails[$orderDetailsKey]['currentPrice'] = $tmpArticle['price'];
						}

						if(!empty($tmpArticle['pseudoprice'])) {
							$getOrderDetails[$orderDetailsKey]['currentPseudoprice'] = $tmpArticle['pseudoprice'];
						}

						// Set article in deactivate state if it's an variant or configurator article
						if($tmpArticle['sVariantArticle'] === true || $tmpArticle['sConfigurator'] === true) {
							$getOrderDetails[$orderDetailsKey]['active'] = 0;
							$active = 0;
						}
					} else {
						$getOrderDetails[$orderDetailsKey]['active'] = 0;
						$active = 0;
					}
					/** GET ARTICLE DETAILS END */

					// Check for serial
					if ($getOrderDetails[$orderDetailsKey]["esdarticle"]){
						$numbers = array();
						$sql = "
						SELECT serialnumber FROM s_articles_esd_serials, s_order_esd WHERE userID=?
						AND orderID=? AND orderdetailsID=?
						AND s_order_esd.serialID=s_articles_esd_serials.id
						";

						$getSerial = $this->sSYSTEM->sDB_CONNECTION->GetAll($sql,array($this->sSYSTEM->_SESSION["sUserId"],$orderValue["id"],$orderDetailsValue["id"]));
						foreach ($getSerial as $serial){
							$numbers[] = $serial["serialnumber"];
						}
						$getOrderDetails[$orderDetailsKey]["serial"] =  implode(",",$numbers);
						// Building download-link
						$getOrderDetails[$orderDetailsKey]["esdLink"] = $this->sSYSTEM->sCONFIG["sBASEFILE"].'?sViewport=account&sAction=download&esdID='.$orderDetailsValue['id'];
						//$getOrderDetails[$orderDetailsKey]["esdLink"] = "http://".$this->sSYSTEM->sCONFIG["sBASEPATH"]."/engine/core/php/loadesd.php?id=".$orderDetailsValue["id"];
					}
					// -- End of serial check
				}
				/** GET ARTICLE DETAILS START - @date: 05-24-2011 */
				$getOrders[$orderKey]['activeBuyButton'] = $active;
				/** GET ARTICLE DETAILS END */

				$getOrders[$orderKey]["details"] = $getOrderDetails;
			}
			$getOrders[$orderKey]["dispatch"] = $this->sGetDispatch($orderValue['dispatchID']);
		}

		$getOrders = Enlight()->Events()->filter('Shopware_Modules_Admin_GetOpenOrderData_FilterResult', $getOrders, array('subject'=>$this,'id'=>$this->sSYSTEM->_SESSION["sUserId"],'subshopID'=>$this->sSYSTEM->sSubShop["id"]));
		return $getOrders;
	}


	/**
	 * Get a user mail by id
	 * @access public
	 * @return string email
	 */
	public function sGetUserMailById(){
		$email = $this->sSYSTEM->sDB_CONNECTION->GetRow("
		SELECT email FROM
		s_user WHERE id=?",array($this->sSYSTEM->_SESSION["sUserId"]));

		return $email["email"];

	}

	/**
	 * Get user id by mail
	 * @access public
	 * @return  int id
	 */
	public function sGetUserByMail($email){
        $addScopeSql = "";
        if ($this->scopedRegistration == true){
           $addScopeSql = "
           AND subshopID = ".$this->subshopId;
        }
		$getUserData = $this->sSYSTEM->sDB_CONNECTION->GetRow("
		SELECT id FROM s_user WHERE email=? AND accountmode!=1 $addScopeSql
		",array($email));

		return $getUserData["id"];
	}

	/**
	 * Get user first- and lastname by id
	 * @access public
	 * @return array firstname/lastname
	 */
	public function sGetUserNameById($id){
		return $this->sSYSTEM->sDB_CONNECTION->GetRow("
        SELECT firstname, lastname FROM s_user_billingaddress
        WHERE userID=?
        ",array($id));
	}


	/**
	 *  Get all data from the current logged in user
	 * @access public
	 * @return array
	 */
	public function sGetUserData (){
		if (Enlight()->Events()->notifyUntil('Shopware_Modules_Admin_GetUserData_Start', array('subject'=>$this))){
			return false;
		}
		if (empty($this->sSYSTEM->_SESSION['sRegister']))
		{
			$this->sSYSTEM->_SESSION['sRegister'] = new ArrayObject(array(), ArrayObject::ARRAY_AS_PROPS);
		}

        $countryQuery = "SELECT c.*, a.`name` AS countryarea FROM s_core_countries c
                LEFT JOIN s_core_countries_areas a ON a.id = c.areaID AND a.active = 1
                WHERE c.id=?";

		// If user is logged in
		if (!empty($this->sSYSTEM->_SESSION["sUserId"])){

            // 1.) Get billing-address
            $sql = "SELECT *
                    FROM
                        s_user_billingaddress
                    WHERE
                        userID=?";

			$billing = $this->sSYSTEM->sDB_CONNECTION->GetRow($sql,array($this->sSYSTEM->_SESSION["sUserId"]));
            $attributes = $this->getUserBillingAddressAttributes($this->sSYSTEM->_SESSION["sUserId"]);
            $userData["billingaddress"] = array_merge($attributes, $billing);

			if(empty($userData["billingaddress"]['customernumber']) && $this->sSYSTEM->sCONFIG['sSHOPWAREMANAGEDCUSTOMERNUMBERS'])
			{
				$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`+1 WHERE `s_order_number`.`name` ='user' AND `s_user_billingaddress`.`userID`=?";
				$this->sSYSTEM->sDB_CONNECTION->Execute($sql, array($this->sSYSTEM->_SESSION["sUserId"]));
			}

			// 2.) Advanced infos
				// Query country-information
				$userData["additional"]["country"] =  $this->sSYSTEM->sDB_CONNECTION->GetRow("
				SELECT * FROM s_core_countries
				WHERE id=?
				",array($userData["billingaddress"]["countryID"]));
                // State selection
                $userData["additional"]["state"] =  $this->sSYSTEM->sDB_CONNECTION->GetRow("
                SELECT * FROM s_core_countries_states
                WHERE id=?
                ",array($userData["billingaddress"]["stateID"]));


				$userData["additional"]["country"] = $this->sGetCountryTranslation($userData["additional"]["country"]);

                $additional = $this->sSYSTEM->sDB_CONNECTION->GetRow("SELECT * FROM s_user WHERE id=?",array($this->sSYSTEM->_SESSION["sUserId"]));
                $attributes = $this->getUserAttributes($this->sSYSTEM->_SESSION["sUserId"]);
                $userData["additional"]["user"] = array_merge($attributes, $additional);

				// Newsletter-Properties
				$newsletter = $this->sSYSTEM->sDB_CONNECTION->GetRow("
				SELECT id FROM s_campaigns_mailaddresses
				WHERE email=?
				",array($userData["additional"]["user"]["email"]));
				if ($newsletter["id"]){
					$userData["additional"]["user"]["newsletter"] = 1;
				}else {
					$userData["additional"]["user"]["newsletter"] = 0;
				}

			// 3.) Get shipping-adress
			$shipping = $this->sSYSTEM->sDB_CONNECTION->GetRow("SELECT * FROM s_user_shippingaddress WHERE userID=?", array($this->sSYSTEM->_SESSION["sUserId"]));
            $attributes = $this->getUserShippingAddressAttributes($this->sSYSTEM->_SESSION["sUserId"]);
            $userData["shippingaddress"]= array_merge($attributes, $shipping);

			// If shipping-address is not available, billing address is coeval the shipping address
			if (!isset($userData["shippingaddress"]["firstname"])){
				$userData["shippingaddress"] = $userData["billingaddress"];
				$userData["shippingaddress"]["eqalBilling"] = true;
			}else {
				if (($userData["shippingaddress"]["countryID"] != $userData["billingaddress"]["countryID"]) && empty($this->sSYSTEM->sCONFIG["sCOUNTRYSHIPPING"])){
					$update = $this->sSYSTEM->sDB_CONNECTION->Execute("
					UPDATE s_user_shippingaddress SET countryID = ? WHERE id = ?
					",array($userData["billingaddress"]["countryID"],$userData["shippingaddress"]["id"]));
					$userData["shippingaddress"]["countryID"] = $userData["billingaddress"]["countryID"];
				}
			}

            if (empty($userData["shippingaddress"]["countryID"])){
                $targetCountryId = $userData["billingaddress"]["countryID"];
			} else {
                $targetCountryId = $userData["shippingaddress"]["countryID"];
			}
            $userData["additional"]["countryShipping"] = $this->sSYSTEM->sDB_CONNECTION->GetRow($countryQuery,array($targetCountryId));
			$userData["additional"]["countryShipping"] = $this->sGetCountryTranslation($userData["additional"]["countryShipping"]);
            $this->sSYSTEM->_SESSION["sCountry"] = $userData["additional"]["countryShipping"]["id"];
            // State selection
            $userData["additional"]["stateShipping"] =  $this->sSYSTEM->sDB_CONNECTION->GetRow("
            SELECT * FROM s_core_countries_states
            WHERE id=?
            ",array($userData["shippingaddress"]["stateID"]));
            // Add stateId to session
            $this->sSYSTEM->_SESSION["sState"] = $userData["additional"]["stateShipping"]["id"];
            // Add areaId to session
            $this->sSYSTEM->_SESSION["sArea"] = $userData["additional"]["countryShipping"]["areaID"];
			$userData["additional"]["payment"] = $this->sGetPaymentMeanById($userData["additional"]["user"]["paymentID"],$userData);
		}else {
			if ($this->sSYSTEM->_SESSION["sCountry"] && $this->sSYSTEM->_SESSION["sCountry"] != $this->sSYSTEM->_SESSION["sRegister"]["billing"]["country"]){
                $this->sSYSTEM->_SESSION["sRegister"]["billing"]["country"] = intval($this->sSYSTEM->_SESSION["sCountry"]);
			}

            $userData["additional"]["country"] = $this->sSYSTEM->sDB_CONNECTION->GetRow($countryQuery, array(intval($this->sSYSTEM->_SESSION["sRegister"]["billing"]["country"])));
            $userData["additional"]["countryShipping"] = $userData["additional"]["country"];
            $userData["additional"]["stateShipping"]["id"] = !empty($this->sSYSTEM->_SESSION["sState"]) ? $this->sSYSTEM->_SESSION["sState"] : 0;


            /* hide@address.com Do we need a translation here? */
		}

		$userData = Enlight()->Events()->filter('Shopware_Modules_Admin_GetUserData_FilterResult', $userData, array('subject'=>$this,'id'=>$this->sSYSTEM->_SESSION["sUserId"]));

        return  $userData;
	}

    private function getUserBillingAddressAttributes($userId)
    {
        $builder = Shopware()->Models()->createQueryBuilder();
        $attributes = $builder->select(array('attributes'))
                ->from('Shopware\Models\Attribute\CustomerBilling', 'attributes')
                ->innerJoin('attributes.customerBilling', 'billing')
                ->where('billing.customerId = :userId')
                ->setParameter('userId', $userId)
                ->setFirstResult(0)
                ->setMaxResults(1)
                ->getQuery()
                ->getOneOrNullResult(\Doctrine\ORM\AbstractQuery::HYDRATE_ARRAY);

        if (!is_array($attributes)) {
            return array();
        } else {
            unset($attributes['id']);
            return $attributes;
        }
    }

    private function getUserShippingAddressAttributes($userId)
    {
        $builder = Shopware()->Models()->createQueryBuilder();
        $attributes = $builder->select(array('attributes'))
                ->from('Shopware\Models\Attribute\CustomerShipping', 'attributes')
                ->innerJoin('attributes.customerShipping', 'shipping')
                ->where('shipping.customerId = :userId')
                ->setParameter('userId', $userId)
                ->setFirstResult(0)
                ->setMaxResults(1)
                ->getQuery()
                ->getOneOrNullResult(\Doctrine\ORM\AbstractQuery::HYDRATE_ARRAY);

        if (!is_array($attributes)) {
            return array();
        } else {
            unset($attributes['id']);
            return $attributes;
        }
    }

    private function getUserAttributes($userId)
    {
        $builder = Shopware()->Models()->createQueryBuilder();
        $attributes = $builder->select(array('attributes'))
                ->from('Shopware\Models\Attribute\Customer', 'attributes')
                ->where('attributes.customerId = :userId')
                ->setParameter('userId', $userId)
                ->setFirstResult(0)
                ->setMaxResults(1)
                ->getQuery()
                ->getOneOrNullResult(\Doctrine\ORM\AbstractQuery::HYDRATE_ARRAY);

        if (!is_array($attributes)) {
            return array();
        } else {
            unset($attributes['id']);
            return $attributes;
        }
    }

    /**
     * Get dispatch method by id
     * @deprecated is not in use
     * @access public
     * @param $dispatchID
     * @return array
     */
	public function sGetDispatch($dispatchID)
	{
        return $this->sGetPremiumDispatch($dispatchID);
		if (!empty($this->sSYSTEM->sCONFIG['sPREMIUMSHIPPIUNG']))
		{
			return $this->sGetPremiumDispatch($dispatchID);
		}
		$dispatchID = intval($dispatchID);

		$getDispatch = $this->sSYSTEM->sDB_CONNECTION->GetRow("
		SELECT id, name, description FROM s_shippingcosts_dispatch  WHERE
			active = 1
		AND
			s_shippingcosts_dispatch.id = $dispatchID
		");

		return $this->sGetDispatchTranslation($getDispatch);
	}

	/**
	 * DEPRECATED Get all dispatch methods, filtered by country
	 * @param int $countryID s_core_countries.id
	 * @access public
	 * @return array
	 */

	public function sGetDispatches($countryID)
	{
		return $this->sGetPremiumDispatches($countryID);
	}

	/**
	 * DEPRECATED Get shippingcosts
	 * @param array $countryInfo - data from shipping country
	 * @param double $surcharge - surcharge for current payment
	 * @access public
	 * @return array
	 */
	public function sGetShippingcosts($countryInfo=null,$surcharge=0,$surchargestring=""){
		return $this->sGetPremiumShippingcosts($countryInfo);
	}

	/**
	 * Shopware Risk-Management
	 * @param int $paymentID - payment id (s_core_paymentmeans.id)
	 * @param array $basket - current shoppingcart
	 * @param array $user -  user data
	 * @access public
	 * @return boolean
	 */
	public function sManageRisks($paymentID,$basket,$user) {
		// Get all assigned rules
		$queryRules = $this->sSYSTEM->sDB_CONNECTION->GetAll("
		SELECT rule1, value1, rule2, value2
		FROM s_core_rulesets
		WHERE paymentID = ?
		ORDER BY id ASC
		",array($paymentID));

		if (!count($queryRules)) return false;

		// Get-User-Data

		// Get Basket


		foreach ($queryRules as $rule) {
			if ($rule["rule1"] && !$rule["rule2"]){
				$rule["rule1"] = "sRisk".$rule["rule1"];
				if ($rule["rule2"]) $rule["rule2"] = "sRisk".$rule["rule2"];
				if ($this->$rule["rule1"]($user,$basket,$rule["value1"])){
					return true;
				}
			}elseif ($rule["rule1"] && $rule["rule2"]){
				$rule["rule1"] = "sRisk".$rule["rule1"];
				if ($rule["rule2"]) $rule["rule2"] = "sRisk".$rule["rule2"];
				// AND
				if ($this->$rule["rule1"]($user,$basket,$rule["value1"]) && $this->$rule["rule2"]($user,$basket,$rule["value2"])){
					return true;
				}
			}
		}
	}

	/**
	 * Risk-Management Order value greater then
	 * @param  $user
	 * @param  $order
	 * @param  $value
	 * @return bool
	 */
	public function sRiskORDERVALUEMORE ($user, $order, $value){
		#print_r($user);
		#print_r($order);
		$basketValue = $order["AmountNumeric"];

		if ($this->sSYSTEM->sCurrency["factor"]){
			$factor = $this->sSYSTEM->sCurrency["factor"];
			$basketValue /= $factor;
		}else {
			$factor = 1;
		}

		if ($basketValue>=$value){
			return true;
		}else {
			return false;
		}
	}

	/**
	 * Risk-Management Order value less then
	 * @param  $user
	 * @param  $order
	 * @param  $value
	 * @return bool
	 */
	public function sRiskORDERVALUELESS ($user, $order, $value){
		$basketValue = $order["AmountNumeric"];

		if ($this->sSYSTEM->sCurrency["factor"]){
			$factor = $this->sSYSTEM->sCurrency["factor"];
			$basketValue /= $factor;
		}else {
			$factor = 1;
		}

		if ($basketValue<=$value){
			return true;
		}else {
			return false;
		}
	}

	/**
	 * Risk-Management customer group match x
	 * @param  $user
	 * @param  $order
	 * @param  $value
	 * @return bool
	 */
	public function sRiskCUSTOMERGROUPIS ($user, $order, $value){
		if ($user["additional"]["user"]["customergroup"]==$value){
			return true;
		}else {
			return false;
		}
	}

	/**
	 * Risk-Management customer group match not
	 * @param  $user
	 * @param  $order
	 * @param  $value
	 * @return bool
	 */
	public function sRiskCUSTOMERGROUPISNOT ($user, $order, $value){
		if ($user["additional"]["user"]["customergroup"]!=$value){
			return true;
		}else {
			return false;
		}
	}

	/**
	 * Risk-Management zip code is
	 * @param  $user
	 * @param  $order
	 * @param  $value
	 * @return bool
	 */
	public function sRiskZIPCODE ($user, $order, $value){
		if ($value=="-1") $value = "";
		if ($user["shippingaddress"]["zipcode"]==$value || $user["billingaddress"]["zipcode"]==$value){
			return true;
		}else {
			return false;
		}
	}

	/**
	 * Risk-Management  country zone is
	 * @param  $user
	 * @param  $order
	 * @param  $value
	 * @return bool
	 */
	public function sRiskZONEIS ($user, $order, $value){
		if ($user["additional"]["countryShipping"]["countryarea"]==$value){
			return true;
		}else {
			return false;
		}
	}

	/**
	 * Risk-Management country zone is not
	 * @param  $user
	 * @param  $order
	 * @param  $value
	 * @return bool
	 */
	public function sRiskZONEISNOT ($user, $order, $value){

		if ($user["additional"]["countryShipping"]["countryarea"]!=$value){
			return true;
		}else {
			return false;
		}
	}

	/**
	 * Risk-Management country is
	 * @param  $user
	 * @param  $order
	 * @param  $value
	 * @return bool
	 */
	public function sRiskLANDIS ($user, $order, $value){
		if (preg_match("/$value/",$user["additional"]["countryShipping"]["countryiso"])){
			return true;
		}
		if ($user["additional"]["countryShipping"]["countryiso"]==$value){
			return true;
		}else {
			return false;
		}
	}

	/**
	 * Risk-Management country is not
	 * @param  $user
	 * @param  $order
	 * @param  $value
	 * @return bool
	 */
	public function sRiskLANDISNOT ($user, $order, $value){
		if (!preg_match("/$value/",$user["additional"]["countryShipping"]["countryiso"])){
			return true;
		}

		if ($user["additional"]["countryShipping"]["countryiso"]!=$value){
			return true;
		}else {
			return false;
		}
	}


	/**
	 * Risk-Management customer is new
	 * @param  $user
	 * @param  $order
	 * @param  $value
	 * @return bool
	 */
	public function sRiskNEWCUSTOMER ($user, $order, $value){
		if ($user["additional"]["user"]["firstlogin"]==date("Y-m-d") || !$user["additional"]["user"]["firstlogin"]){
			return true;
		}else {
			return false;
		}
	}

	/**
	 * Risk-Management order has more then x positions
	 * @param  $user
	 * @param  $order
	 * @param  $value
	 * @return bool
	 */
	public function sRiskORDERPOSITIONSMORE ($user, $order, $value){
		if (count($order["content"])>=$value){
			return true;
		}else {
			return false;
		}
	}

	/**
	 * Risk-Management Article attribute x from basket - positions is y
	 * @param  $user
	 * @param  $order
	 * @param  $value
	 * @return bool
	 */
	public function sRiskATTRIS ($user,$order,$value){
		if (!empty($order["content"])){

			$value = explode("|",$value);
			if (!empty($value[0]) && isset($value[1])){
                $number = (int) str_ireplace('attr', '', $value[0]);

                $sql = "
                SELECT s_articles_attributes.id
                FROM s_order_basket, s_articles_attributes, s_articles_details
                WHERE
                s_order_basket.sessionID=?
                AND s_order_basket.modus=0
                AND (
                s_order_basket.ordernumber = s_articles_details.ordernumber
                OR (s_order_basket.articleID = s_articles_details.articleID AND s_articles_details.kind = 1)
                )
                AND s_articles_details.id = s_articles_attributes.articledetailsID
                AND s_articles_attributes.attr{$number} = ?
                LIMIT 1
                ";

                $checkArticle = $this->sSYSTEM->sDB_CONNECTION->GetOne($sql,array($this->sSYSTEM->sSESSION_ID, $value[1]));
                if ($checkArticle){
                    return true;
                }else {
                    return false;
                }
			}else {
				return false;
			}
		}
	}

	/**
	 * Risk-Management article attribute x from basket is not y
	 * @param  $user
	 * @param  $order
	 * @param  $value
	 * @return bool
	 */
	public function sRiskATTRISNOT ($user,$order,$value){
		if (!empty($order["content"])){

			$value = explode("|",$value);
			if (!empty($value[0]) && isset($value[1])){
                $number = (int) str_ireplace('attr', '', $value[0]);

                $sql = "
                SELECT s_articles_attributes.id
                FROM s_order_basket, s_articles_attributes, s_articles_details
                WHERE
                s_order_basket.sessionID=?
                AND s_order_basket.modus=0
                AND (
                s_order_basket.ordernumber = s_articles_details.ordernumber
                OR (s_order_basket.articleID = s_articles_details.articleID AND s_articles_details.kind = 1)
                )
                AND s_articles_details.id = s_articles_attributes.articledetailsID
                AND s_articles_attributes.attr{$number}!= ?
                LIMIT 1
                ";
                $checkArticle = $this->sSYSTEM->sDB_CONNECTION->GetOne($sql,array($this->sSYSTEM->sSESSION_ID, $value[1]));


                if ($checkArticle){
                    return true;
                }else {
                    return false;
                }

			}else {
				return false;
			}

	}
	}

	/**
	 * Risk-Management customer had payment problems in past
	 * @param  $user
	 * @param  $order
	 * @param  $value
	 * @return bool
	 */
	public function sRiskINKASSO ($user, $order, $value){
		if ($this->sSYSTEM->_SESSION["sUserId"]){
			$checkOrder = $this->sSYSTEM->sDB_CONNECTION->GetRow("
			SELECT id FROM s_order WHERE cleared=16 AND userID=?", array($this->sSYSTEM->_SESSION["sUserId"]));
			if ($checkOrder["id"]){
				return true;
			}else {
				return false;
			}
		}else {
			return false;
		}
	}

	/**
	 * Risk-Management Last order less x days
	 * @param  $user
	 * @param  $order
	 * @param  $value
	 * @return bool
	 */
	public function sRiskLASTORDERLESS ($user, $order, $value){
		// A order from previous x days must exists
		if ($this->sSYSTEM->_SESSION["sUserId"]){
            $value = (int) $value;
			$sql = "
			SELECT id FROM s_order WHERE userID=?
			AND TO_DAYS(ordertime) <= (TO_DAYS(now())-$value) LIMIT 1
			";
			$checkOrder = $this->sSYSTEM->sDB_CONNECTION->GetRow($sql, array($this->sSYSTEM->_SESSION["sUserId"]));

			if (!$checkOrder["id"]){
				return true;
			}else {
				return false;
			}
		}else {
			return true;
		}
	}

	/**
	 * Risk-Management articles from a certain category
	 * @param  $user
	 * @param  $order
	 * @param  $value
	 * @return bool
	 */
	public function sRiskARTICLESFROM ($user, $order, $value){
		$checkArticle = $this->sSYSTEM->sDB_CONNECTION->GetOne("
			SELECT s_articles_categories.id as id
			FROM s_order_basket, s_articles_categories
			WHERE s_order_basket.articleID = s_articles_categories.articleID
			AND s_articles_categories.categoryID = ?
			AND s_order_basket.sessionID=?
			AND s_order_basket.modus=0
		", array($value, $this->sSYSTEM->sSESSION_ID));

		if (!empty($checkArticle))
			return true;
		else
			return false;
	}

	/**
	 * Risk-Management Order value greater then
	 * @param  $user
	 * @param  $order
	 * @param  $value
	 * @return bool
	 */
	public function sRiskLASTORDERSLESS($user,$order,$value){
		if ($this->sSYSTEM->_SESSION["sUserId"]){
			$checkOrder = $this->sSYSTEM->sDB_CONNECTION->GetAll("
			SELECT id FROM s_order WHERE status != -1 AND status != 4 AND userID=?", array($this->sSYSTEM->_SESSION["sUserId"]));
			if (count($checkOrder)<=$value){
				return true;
			}
		}else {
			return true;
		}

		return false;
	}

	/**
	 * Risk management Block if street contains pattern
	 * @param  $user
	 * @param  $order
	 * @param  $value
	 * @return bool
	 */
	public function sRiskPREGSTREET ($user, $order, $value){
		$value = strtolower($value);
		if (preg_match("/$value/",strtolower($user["shippingaddress"]["street"]))){
			return true;
		}else {
			return false;
		}
	}

	/**
	 * Risk-Management block if billing address not eqal to shipping address
	 * @param  $user
	 * @param  $order
	 * @param  $value
	 * @return bool
	 */
	public function sRiskDIFFER ($user, $order, $value){
		if (strtolower(trim($user["shippingaddress"]["street"]).trim($user["shippingaddress"]["streetnumber"])) != strtolower(trim($user["billingaddress"]["street"]).trim($user["billingaddress"]["streetnumber"]))) {
            return true;
        } elseif (trim($user["shippingaddress"]["zipcode"]) != trim($user["billingaddress"]["zipcode"])) {
            return true;
        } else {
			return false;
		}
	}

	/**
	 * Risk-Management block if customernumber matches pattern
	 * @param  $user
	 * @param  $order
	 * @param  $value
	 * @return bool
	 */
	public function sRiskCUSTOMERNR ($user, $order, $value){
			//echo "test";
		if ($user["billingaddress"]["customernumber"]==$value && !empty($value)){
			return true;
		}else {
			return false;
		}
	}

	/**
	 * Risk-Management block if lastname matches pattern
	 * @param  $user
	 * @param  $order
	 * @param  $value
	 * @return bool
	 */
	public function sRiskLASTNAME ($user, $order, $value){
		$value = strtolower($value);
		if (preg_match("/$value/",strtolower($user["shippingaddress"]["lastname"])) || preg_match("/$value/",strtolower($user["billingaddress"]["lastname"]))){
			return true;
		}else {
			return false;
		}
	}

	/**
	 * Risk-Management  Block if subshop id is x
	 * @param  $user
	 * @param  $order
	 * @param  $value
	 * @return bool
	 */
	public function sRiskSUBSHOP ($user, $order, $value){

		if ($this->sSYSTEM->sSubShop["id"]==$value){
			return true;
		}else {
			return false;
		}
	}

	/**
	 * Risk-Management  Block if subshop id is not x
	 * @param  $user
	 * @param  $order
	 * @param  $value
	 * @return bool
	 */
	public function sRiskSUBSHOPNOT ($user, $order, $value){

		if ($this->sSYSTEM->sSubShop["id"]!=$value){
			return true;
		}else {
			return false;
		}
	}

	/**
	 * Risk-Management Block if currency id is not x
	 * @param  $user
	 * @param  $order
	 * @param  $value
	 * @return bool
	 */
	public function sRiskCURRENCIESISOIS ($user, $order, $value){
		if(strtolower($this->sSYSTEM->sCurrency['currency']) == strtolower($value))
		{
			return true;
		}else{
			return false;
		}
	}

	/**
	 * Risk-Management Block if currency id is x
	 * @param  $user
	 * @param  $order
	 * @param  $value
	 * @return bool
	 */
	public function sRiskCURRENCIESISOISNOT ($user, $order, $value){
		if(strtolower($this->sSYSTEM->sCurrency['currency']) != strtolower($value))
		{
			return true;
		}else{
			return false;
		}
	}


	/**
	 * Subscribe / unsubscribe to mailing list
	 * @param string $email - mail
	 * @param boolean $unsubscribe
	 * @param id $groupID id of the mailinglist group
	 * @access public
	 * @return boolean
	 */
	public function sNewsletterSubscription ($email, $unsubscribe=false, $groupID=null)
	{
		if(empty($unsubscribe))
		{
			$errorflag = array();

			/**
			 * Only the mail address needs to be a mandatory item
			 * @ticket #5781
			 * @author S.Pohl <hide@address.com>
			 * @date 2011-07-27
			 */
			$fields = array('newsletter');
			foreach ($fields as $field)
			{
				if(isset($this->sSYSTEM->_POST[$field])&&empty($this->sSYSTEM->_POST[$field]))
				{
					$errorflag[$field] = true;
				}
			}
			if(!empty($errorflag))
			{
				return array(
					'code' => 5,
					'message' => $this->snippetObject->get('ErrorFillIn','Please fill in all red fields'),
					'sErrorFlag' => $errorflag
				);
			}
		}

		if(empty($groupID))
		{
			$groupID = $this->sSYSTEM->sCONFIG["sNEWSLETTERDEFAULTGROUP"];
			$sql = '
				INSERT IGNORE INTO s_campaigns_groups (id, name)
				VALUES (?, ?)
			';
			$this->sSYSTEM->sDB_CONNECTION->Execute($sql, array($groupID, 'Newsletter-Empfänger'));
		}

		$email = trim(strtolower(stripslashes($email)));
		if(empty($email))
			return array("code"=>6, "message"=>$this->snippetObject->get('NewsletterFailureMail','Enter eMail address'));
		$reg = "/^(([^<>()[\]\\\\.,;:\s@\"]+(\.[^<>()[\]\\\\.,;:\s@\"]+)*)|(\"([^\"\\\\\r]|(\\\\[\w\W]))*\"))@((\[([0-9]{1,3}\.){3}[0-9]{1,3}\])|(([a-z\-0-9áàäçéèêñóòôöüæøå]+\.)+[a-z]{2,}))$/i";
		if(!preg_match($reg, $email))
			return array("code"=>1, "message"=>$this->snippetObject->get('NewsletterFailureInvalid','Enter valid eMail address'));

		if(!$unsubscribe)
		{
			$sql = "SELECT * FROM s_campaigns_mailaddresses WHERE email=?";
			$result = $this->sSYSTEM->sDB_CONNECTION->Execute($sql, array($email));
			if($result===false) {
				$result = array("code"=>10,"message"=>$this->snippetObject->get('UnknownError','Unknown error'));
			} elseif($result->RecordCount()) {
				$result = array("code"=>2,"message"=>$this->snippetObject->get('NewsletterFailureAlreadyRegistered','You already receive our newsletter'));
			} else {
				$sql = "INSERT INTO s_campaigns_mailaddresses (`groupID`,email) VALUES(?, ?)";
				$result = $this->sSYSTEM->sDB_CONNECTION->Execute($sql, array($groupID, $email));

				if($result===false)
					$result = array("code"=>10,"message"=>$this->snippetObject->get('UnknownError','Unknown error'));
				else
					$result = array("code"=>3,"message"=>$this->snippetObject->get('NewsletterSuccess','Thank you for receiving our newsletter'));
			}
		}
		else
		{
			$sql = "DELETE FROM s_campaigns_mailaddresses WHERE email=?";
			$result1 = $this->sSYSTEM->sDB_CONNECTION->Execute($sql, array($email));
			$result = $this->sSYSTEM->sDB_CONNECTION->Affected_Rows();
			$sql = "UPDATE s_user SET newsletter=0 WHERE email=?";
			$result2 =$this->sSYSTEM->sDB_CONNECTION->Execute($sql, array($email));
			$result += $this->sSYSTEM->sDB_CONNECTION->Affected_Rows();
			if($result1===false||$result2===false)
				$result = array("code"=>10,"message"=>$this->snippetObject->get('UnknownError','Unknown error'));
			elseif(empty($result))
				$result = array("code"=>4,"message"=>$this->snippetObject->get('NewsletterFailureNotFound','This mail address could not be found'));
			else
				$result = array("code"=>5,"message"=>$this->snippetObject->get('NewsletterMailDeleted','Your mail address was deleted'));
		}
		if(!empty($result['code'])&&in_array($result['code'], array(2,3)))
		{
			$sql = '
				REPLACE INTO `s_campaigns_maildata` (`email`, `groupID`, `salutation`, `title`, `firstname`, `lastname`, `street`, `streetnumber`, `zipcode`, `city`, `added`)
				VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, '.$this->sSYSTEM->sDB_CONNECTION->sysTimeStamp.')
			';
			$this->sSYSTEM->sDB_CONNECTION->Execute($sql, array(
				$email,
				$groupID,
				$this->sSYSTEM->_POST['salutation'],
				$this->sSYSTEM->_POST['title'],
				$this->sSYSTEM->_POST['firstname'],
				$this->sSYSTEM->_POST['lastname'],
				$this->sSYSTEM->_POST['street'],
				$this->sSYSTEM->_POST['streetnumber'],
				$this->sSYSTEM->_POST['zipcode'],
				$this->sSYSTEM->_POST['city']
			));
		}
		elseif(!empty($unsubscribe))
		{
			$sql = 'DELETE FROM `s_campaigns_maildata` WHERE `email`=? AND `groupID`=?';
			$this->sSYSTEM->sDB_CONNECTION->Execute($sql, array($email, $groupID));
		}

		return $result;
	}

	/**
	 * Generate table with german holidays
	 * @access public
	 * @return boolean
	 */
	public function sCreateHolidaysTable ()
	{
		if(!function_exists('easter_days'))
		{
			function easter_days ($year)
			{
				$G = $year % 19;
				$C = (int)($year / 100);
				$H = (int)($C - (int)($C / 4) - (int)((8*$C+13) / 25) + 19*$G + 15) % 30;
				$I = (int)$H - (int)($H / 28)*(1 - (int)($H / 28)*(int)(29 / ($H + 1))*((int)(21 - $G) / 11));
				$J = ($year + (int)($year/4) + $I + 2 - $C + (int)($C/4)) % 7;
				$L = $I - $J;
				$m = 3 + (int)(($L + 40) / 44);
				$d = $L + 28 - 31 * ((int)($m / 4));
				$E = mktime(0,0,0, $m, $d, $year)-mktime(0,0,0,3,21,$year);
				return intval(round($E/(60*60*24),0));
			}
		}
		$sql = "
			SELECT id, calculation, `date`
			FROM `s_premium_holidays`
			WHERE `date`<CURDATE()
		";
		$holidays = $this->sSYSTEM->sDB_CONNECTION->CacheGetAssoc(60,$sql);
		if(empty($holidays)) return true;

		foreach ($holidays as $id => $holiday)
		{
			$calculation = $holiday['calculation'];
			$datestamp = strtotime($holiday['date']);
			$date = date('Y-m-d',$datestamp);
			$year = date('Y',$datestamp)+1;
			$easter_date = date('Y-m-d',mktime(0,0,0,3,21+easter_days($year),$year));

			$calculation = preg_replace("#DATE\('(\d+)[\-/](\d+)'\)#i","DATE(CONCAT(YEAR(),'-','\$1-\$2'))",$calculation);
			$calculation = str_replace("EASTERDATE()","'$easter_date'",$calculation);
			$calculation = str_replace("YEAR()","'$year'",$calculation);
			$calculation = str_replace("DATE()","'$date'",$calculation);
			$sql = "UPDATE s_premium_holidays SET `date`= $calculation WHERE id = $id";
			$this->sSYSTEM->sDB_CONNECTION->Execute($sql);
		}
	}

	/**
	 * Get a specific country
	 * @param int $country - s_core_countries.id
	 * @access public
	 * @return array
	 */
	public function sGetCountry ($country)
	{
		static $cache = array();
		if(empty($country))
			return false;
		if(isset($cache[$country]))
			return $cache[$country];
		if(is_numeric($country))
			$sql = "c.id=".$country;
		elseif(is_string($country))
			$sql = "c.countryiso=".$this->sSYSTEM->sDB_CONNECTION->qstr($country);
		else
			return false;
		$sql = "
			SELECT c.id, c.id as countryID, countryname, countryiso, (SELECT name FROM s_core_countries_areas WHERE id = areaID ) AS countryarea, countryen, c.position, notice, c.shippingfree as shippingfree
			FROM s_core_countries c
			WHERE $sql
		";
		$currencyFactor = empty($this->sSYSTEM->sCurrency["factor"]) ? 1 : $this->sSYSTEM->sCurrency["factor"];
		$cache[$country]["shippingfree"] = round($cache[$country]["shippingfree"]*$currencyFactor,2);
		return $cache[$country] = $this->sSYSTEM->sDB_CONNECTION->GetRow($sql);
	}

	/**
	 * Get a specific payment
	 * @param int $payment - s_core_paymentmeans.id
	 * @access public
	 * @return array
	 */
	public function sGetPaymentmean ($payment)
	{
		static $cache = array();
		if(empty($payment))
			return false;
		if(isset($cache[$payment]))
			return $cache[$payment];
		if(is_numeric($payment))
			$sql = "id=".$payment;
		elseif(is_string($payment))
			$sql = "name=".$this->sSYSTEM->sDB_CONNECTION->qstr($payment);
		else
			return false;
		$sql = "
			SELECT * FROM s_core_paymentmeans
			WHERE $sql
		";
		$cache[$payment] = $this->sSYSTEM->sDB_CONNECTION->GetRow($sql);

		$cache[$payment]["country_surcharge"] = array();
		if (!empty($cache[$payment]["surchargestring"])){
			foreach(explode(";",$cache[$payment]["surchargestring"]) as $countrySurcharge){
				list($key,$value) = explode(":",$countrySurcharge);
				$value = floatval(str_replace(",",".",$value));
				if (!empty($value)){
					$cache[$payment]["country_surcharge"][$key] = $value;
				}
			}
		}
		return $cache[$payment];
	}

	/**
	 * Get dispatch methods
	 * @param int $countryID
	 * @param int $paymentID
	 * @access public
	 * @return array
	 */
	public function sGetDispatchBasket ($countryID=null, $paymentID = null)
	{
		$sql_select = '';
		if(!empty($this->sSYSTEM->sCONFIG['sPREMIUMSHIPPIUNGASKETSELECT']))
		{
			$sql_select .= ', '.$this->sSYSTEM->sCONFIG['sPREMIUMSHIPPIUNGASKETSELECT'];
		}
		$sql = 'SELECT id, calculation_sql FROM s_premium_dispatch WHERE active=1 AND calculation=3';
		$calculations = $this->sSYSTEM->sDB_CONNECTION->GetAssoc($sql);
		if(!empty($calculations))
		foreach ($calculations as $dispatchID => $calculation)
		{
			if(empty($calculation)) $calculation = $this->sSYSTEM->sDB_CONNECTION->qstr($calculation);
			$sql_select .= ', ('.$calculation.') as calculation_value_'.$dispatchID;
		}
		if (empty($this->sSYSTEM->sUSERGROUPDATA["tax"]) && !empty($this->sSYSTEM->sUSERGROUPDATA["id"])){
			$amount = 'b.quantity*ROUND(CAST(b.price as DECIMAL(10,2))*(100+t.tax)/100,2)';
			$amount_net = 'b.quantity*CAST(b.price as DECIMAL(10,2))';
		} else {
			$amount = 'b.quantity*CAST(b.price as DECIMAL(10,2))';
			$amount_net = 'b.quantity*ROUND(CAST(b.price as DECIMAL(10,2))/(100+t.tax)*100,2)';
		}

		$sql = "
			SELECT
				MIN(d.instock>=b.quantity) as instock,
				MIN(d.instock>=(b.quantity+d.stockmin)) as stockmin,
				MIN(a.laststock) as laststock,
				SUM(d.weight*b.quantity) as weight,
				SUM(IF(a.id,b.quantity,0)) as count_article,
				MAX(b.shippingfree) as shippingfree,
				SUM(IF(b.modus=0,$amount/b.currencyFactor,0)) as amount,
				SUM(IF(b.modus=0,$amount_net/b.currencyFactor,0)) as amount_net,
				SUM(CAST(b.price as DECIMAL(10,2))*b.quantity) as amount_display,
				u.id as userID
				$sql_select
			FROM s_order_basket b

			LEFT JOIN s_articles a
			ON b.articleID=a.id
			AND b.modus=0
			AND b.esdarticle=0

			LEFT JOIN s_articles_details d
			ON (d.ordernumber=b.ordernumber)
			AND d.articleID=a.id

			LEFT JOIN s_articles_attributes at
			ON at.articledetailsID=d.id

            LEFT JOIN s_core_tax t
			ON t.id=a.taxID

			LEFT JOIN s_user u
			ON u.id=?
			AND u.active=1

			LEFT JOIN s_user_billingaddress ub
			ON ub.userID=u.id

			LEFT JOIN s_user_shippingaddress us
			ON us.userID=u.id

			WHERE b.sessionID=?

			GROUP BY b.sessionID
		";

		$basket = $this->sSYSTEM->sDB_CONNECTION->GetRow($sql,array(
			$this->sSYSTEM->_SESSION["sUserId"],
			empty($this->sSYSTEM->sSESSION_ID) ? session_id() : $this->sSYSTEM->sSESSION_ID
		));
		if(empty($basket))
		{
			return false;
		}

        $basket["max_tax"] = $this->sSYSTEM->sMODULES['sBasket']->getMaxTax();

		if(!empty($paymentID)) {
			$paymentID = (int) $paymentID;
		} elseif(!empty($this->sSYSTEM->_SESSION['sUserId'])) {
			$user = $this->sGetUserData();
			$paymentID = (int) $user['additional']['payment']['id'];
		} elseif(!empty($this->sSYSTEM->_POST['sPayment'])) {
			$paymentID = (int) $this->sSYSTEM->_POST['sPayment'];
		} elseif(!empty($this->sSYSTEM->_SESSION['sPaymentID'])) {
			$paymentID = (int) $this->sSYSTEM->_SESSION['sPaymentID'];
		}

		$paymentmeans = $this->sGetPaymentMeans();
		$paymentIDs = array();
		foreach ($paymentmeans as $paymentmean) {
			$paymentIDs[] = $paymentmean['id'];
		}
		if(!in_array($paymentID, $paymentIDs)) {
			$paymentID = reset($paymentIDs);
		}

		if(empty($countryID)&&!empty($user['additional']['countryShipping']['id']))
			$countryID = (int) $user['additional']['countryShipping']['id'];
		else
			$countryID = (int) $countryID;
        $sql = "
            SELECT main_id FROM s_core_shops WHERE id=".(int) $this->sSYSTEM->sSubShop['id']."
        ";
        $mainId = $this->sSYSTEM->sDB_CONNECTION->GetOne($sql);
        // MainId is null, so we use the current shop id
        if(is_null($mainId)) {
            $mainId = (int) $this->sSYSTEM->sSubShop['id'];
        }
		$basket['countryID'] = $countryID;
		$basket['paymentID'] = $paymentID;
		$basket['customergroupID'] = (int) $this->sSYSTEM->sUSERGROUPDATA['id'];
		$basket['multishopID'] = $mainId;
		$basket['sessionID'] = $this->sSYSTEM->sSESSION_ID;

		return $basket;
	}

	/**
	 * Get premium dispatch method
	 * @param int $dispatchID
	 * @access public
	 * @return array
	 */
	public function sGetPremiumDispatch ($dispatchID = null)
	{
		$sql = "
			SELECT d.id, name, d.description, calculation, status_link, surcharge_calculation, bind_shippingfree, shippingfree, tax_calculation, t.tax as tax_calculation_value
			FROM s_premium_dispatch d
			LEFT JOIN s_core_tax t
			ON t.id=d.tax_calculation
			WHERE active = 1
			AND d.id = ?
		";
		$dispatch = $this->sSYSTEM->sDB_CONNECTION->GetRow($sql,array($dispatchID));
		if(empty($dispatch)) return false;
		return $this->sGetDispatchTranslation($dispatch);
	}

	/**
	 * Get premium dispatch methods
	 * @param int $countryID
	 * @param int $paymentID
	 * @access public
	 * @return array
	 */
	public function sGetPremiumDispatches ($countryID=null, $paymentID = null)
	{
		$this->sCreateHolidaysTable();

		$basket = $this->sGetDispatchBasket($countryID, $paymentID);

		$sql = "SELECT id, bind_sql FROM s_premium_dispatch WHERE active=1 AND type IN (0) AND bind_sql IS NOT NULL AND bind_sql != ''";
		$statements = $this->sSYSTEM->sDB_CONNECTION->GetAssoc($sql);

        if(empty($basket)) return array();

		$sql_where = "";
		foreach ($statements as $dispatchID => $statement)
		{
			$sql_where .= " AND ( d.id != $dispatchID OR ($statement)) ";
		}

		$sql_basket = array();
		foreach ($basket as $key => $value)
		{
			$sql_basket[] = $this->sSYSTEM->sDB_CONNECTION->qstr($value)." as `$key`";
		}
		$sql_basket = implode(', ',$sql_basket);

		$sql = "
			SELECT
				d.id as `key`,
				d.id, d.name,
				d.description,
				d.calculation,
				d.status_link,
				b.*
			FROM s_premium_dispatch d

			JOIN ( SELECT $sql_basket ) b
			JOIN s_premium_dispatch_countries dc
			ON d.id = dc.dispatchID
			AND dc.countryID=b.countryID
			JOIN s_premium_dispatch_paymentmeans dp
			ON d.id = dp.dispatchID
			AND dp.paymentID=b.paymentID
			LEFT JOIN s_premium_holidays h
			ON h.date = CURDATE()
			LEFT JOIN s_premium_dispatch_holidays dh
			ON d.id=dh.dispatchID
			AND h.id=dh.holidayID

			LEFT JOIN (
				SELECT dc.dispatchID
				FROM s_order_basket b
				JOIN s_articles_categories ac
				ON ac.articleID=b.articleID
				JOIN s_premium_dispatch_categories dc
				ON dc.categoryID=ac.categoryID
				WHERE b.modus=0
				AND b.sessionID='{$this->sSYSTEM->sSESSION_ID}'
				GROUP BY dc.dispatchID
			) as dk
			ON dk.dispatchID=d.id

			LEFT JOIN s_user u
			ON u.id=b.userID
			AND u.active=1

			LEFT JOIN s_user_billingaddress ub
			ON ub.userID=u.id

			LEFT JOIN s_user_shippingaddress us
			ON us.userID=u.id

			WHERE d.active=1
			AND (
				(bind_time_from IS NULL AND bind_time_to IS NULL)
			OR
				(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))
			OR
				(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)
			)
			AND (
				(bind_weekday_from IS NULL AND bind_weekday_to IS NULL)
			OR
				(IFNULL(bind_weekday_from,1) <= IFNULL(bind_weekday_to,7) AND WEEKDAY(NOW())+1 BETWEEN IFNULL(bind_weekday_from,1) AND IFNULL(bind_weekday_to,7))
			OR
				(bind_weekday_from > bind_weekday_to AND WEEKDAY(NOW())+1 NOT BETWEEN bind_weekday_to AND bind_weekday_from)
			)
			AND (bind_weight_from IS NULL OR bind_weight_from <= b.weight)
			AND (bind_weight_to IS NULL OR bind_weight_to >= b.weight)
			AND (bind_price_from IS NULL OR bind_price_from <= b.amount)
			AND (bind_price_to IS NULL OR bind_price_to >= b.amount)
			AND (bind_instock=0 OR bind_instock IS NULL OR (bind_instock=1 AND b.instock) OR (bind_instock=2 AND b.stockmin))
			AND (bind_laststock=0 OR (bind_laststock=1 AND b.laststock))
			AND (bind_shippingfree!=1 OR NOT b.shippingfree)
			AND dh.holidayID IS NULL
			AND (d.multishopID IS NULL OR d.multishopID=b.multishopID)
			AND (d.customergroupID IS NULL OR d.customergroupID=b.customergroupID)
			AND dk.dispatchID IS NULL
			AND d.type IN (0)
			$sql_where
			GROUP BY d.id
			ORDER BY d.position, d.name
		";

		$dispatches = $this->sSYSTEM->sDB_CONNECTION->GetAssoc($sql);
		if(empty($dispatches))
		{
			$sql = "
				SELECT
					d.id as `key`,
					d.id, d.name,
					d.description,
					d.calculation,
					d.status_link
				FROM s_premium_dispatch d

				WHERE d.active=1
				AND d.type=1
				GROUP BY d.id

				ORDER BY d.position, d.name
				LIMIT 1
			";
			$dispatches = $this->sSYSTEM->sDB_CONNECTION->GetAssoc($sql);
		}

		$names = array();
		foreach ($dispatches as $dispatchID => $dispatch)
		{
			if(in_array($dispatch['name'],$names)) unset($dispatches[$dispatchID]);
			else $names[] = $dispatch['name'];
		}
		unset($names);

		$object = $this->sGetDispatchTranslation();
		foreach ($dispatches as &$v)
		{
			if (!empty($object[$v['id']]['dispatch_name'])){
				$v['name'] = $object[$v['id']]['dispatch_name'];
			}
			if (!empty($object[$v['id']]['dispatch_description'])){
				$v['description'] = $object[$v['id']]['dispatch_description'];
			}
		}
		return $dispatches;
	}

	/**
	 * Get premium dispatch surcharges
	 * @param $basket
	 * @param $type
	 * @access public
	 * @return array
	 */
	public function sGetPremiumDispatchSurcharge ($basket, $type=2)
	{

		if(empty($basket)) return false;
		$type = (int) $type;

		$sql = 'SELECT id, bind_sql FROM s_premium_dispatch WHERE active=1 AND type=? AND bind_sql IS NOT NULL';
		$statements = $this->sSYSTEM->sDB_CONNECTION->GetAssoc($sql,array($type));
		$sql_where = '';
		foreach ($statements as $dispatchID => $statement)
		{
			$sql_where .= "
			AND ( d.id!=$dispatchID OR ($statement))
			";
		}
		$sql_basket = array();
		foreach ($basket as $key => $value)
		{
			$sql_basket[] = $this->sSYSTEM->sDB_CONNECTION->qstr($value)." as `$key`";
		}
		$sql_basket = implode(', ',$sql_basket);

		$sql = "
			SELECT d.id, d.calculation
			FROM s_premium_dispatch d

			JOIN ( SELECT $sql_basket ) b
			JOIN s_premium_dispatch_countries dc
			ON d.id = dc.dispatchID
			AND dc.countryID=b.countryID
			JOIN s_premium_dispatch_paymentmeans dp
			ON d.id = dp.dispatchID
			AND dp.paymentID=b.paymentID
			LEFT JOIN s_premium_holidays h
			ON h.date = CURDATE()
			LEFT JOIN s_premium_dispatch_holidays dh
			ON d.id=dh.dispatchID
			AND h.id=dh.holidayID

			LEFT JOIN (
				SELECT dc.dispatchID
				FROM s_order_basket b
				JOIN s_articles_categories ac
				ON ac.articleID=b.articleID
				JOIN s_premium_dispatch_categories dc
				ON dc.categoryID=ac.categoryID
				WHERE b.modus=0
				AND b.sessionID='{$this->sSYSTEM->sSESSION_ID}'
				GROUP BY dc.dispatchID
			) as dk
			ON dk.dispatchID=d.id

			LEFT JOIN s_user u
			ON u.id=b.userID
			AND u.active=1

			LEFT JOIN s_user_billingaddress ub
			ON ub.userID=u.id

			LEFT JOIN s_user_shippingaddress us
			ON us.userID=u.id

			WHERE d.active=1
			AND (
				(bind_time_from IS NULL AND bind_time_to IS NULL)
			OR
				(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))
			OR
				(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)
			)
			AND (
				(bind_weekday_from IS NULL AND bind_weekday_to IS NULL)
			OR
				(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))
			OR
				(bind_weekday_from > bind_weekday_to AND REPLACE(WEEKDAY(NOW()),0,6)+1 NOT BETWEEN bind_weekday_to AND bind_weekday_from)
			)
			AND (bind_weight_from IS NULL OR bind_weight_from <= b.weight)
			AND (bind_weight_to IS NULL OR bind_weight_to >= b.weight)
			AND (bind_price_from IS NULL OR bind_price_from <= b.amount)
			AND (bind_price_to IS NULL OR bind_price_to >= b.amount)
			AND (bind_instock=0 OR bind_instock IS NULL OR (bind_instock=1 AND b.instock) OR (bind_instock=2 AND b.stockmin))
			AND (bind_laststock=0 OR (bind_laststock=1 AND b.laststock))
			AND (bind_shippingfree=2 OR NOT b.shippingfree)
			AND dh.holidayID IS NULL
			AND (d.multishopID IS NULL OR d.multishopID=b.multishopID)
			AND (d.customergroupID IS NULL OR d.customergroupID=b.customergroupID)
			AND dk.dispatchID IS NULL
			AND d.type = $type
			AND (d.shippingfree IS NULL OR d.shippingfree > b.amount)
			$sql_where
			GROUP BY d.id
		";
		$dispatches = $this->sSYSTEM->sDB_CONNECTION->GetAll($sql);
		$surcharge = 0;
		if(!empty($dispatches)){

		foreach ($dispatches as $dispatch)
		{
			if(empty($dispatch['calculation']))
				$from = round($basket['weight'],3);
			elseif($dispatch['calculation']==1){
                if (($this->sSYSTEM->sCONFIG['sARTICLESOUTPUTNETTO'] && !$this->sSYSTEM->sUSERGROUPDATA["tax"]) || (!$this->sSYSTEM->sUSERGROUPDATA["tax"] && $this->sSYSTEM->sUSERGROUPDATA["id"])){
                    $from = round($basket['amount_net'],2);
                }else {
                    $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 = "
				SELECT `value` , `factor`
				FROM `s_premium_shippingcosts`
				WHERE `from` <= $from
				AND `dispatchID` = {$dispatch['id']}
				ORDER BY `from` DESC
				LIMIT 1
			";
			$result = $this->sSYSTEM->sDB_CONNECTION->GetRow($sql);

			if(empty($result)) continue;
			$surcharge += $result['value'];
			if(!empty($result['factor'])){
                //die($result["factor"].">".$from);
				$surcharge +=  $result['factor']/100*$from;
            }
		}
        }
		return $surcharge;
	}

	/**
	 * Get premium shippingcosts
	 * @param int $country - s_core_countries.id
	 * @access public
	 * @return array
	 */
	public function sGetPremiumShippingcosts ($country=null)
	{
		$currencyFactor = empty($this->sSYSTEM->sCurrency['factor']) ? 1 : $this->sSYSTEM->sCurrency['factor'];

		$discount_tax = empty($this->sSYSTEM->sCONFIG['sDISCOUNTTAX']) ? 0 : (float) str_replace(',','.',$this->sSYSTEM->sCONFIG['sDISCOUNTTAX']);

		// Determinate tax automaticly
		if (!empty($this->sSYSTEM->sCONFIG["sTAXAUTOMODE"])){
			$discount_tax = $this->sSYSTEM->sMODULES['sBasket']->getMaxTax();
		}

		$surcharge_ordernumber = isset($this->sSYSTEM->sCONFIG['sPAYMENTSURCHARGEABSOLUTENUMBER']) ? $this->sSYSTEM->sCONFIG['sPAYMENTSURCHARGEABSOLUTENUMBER'] : 'PAYMENTSURCHARGEABSOLUTENUMBER';
		$surcharge_name = isset($this->sSYSTEM->sCONFIG["sPAYMENTSURCHARGEABSOLUTE"]) ? $this->sSYSTEM->sCONFIG["sPAYMENTSURCHARGEABSOLUTE"] : 'Zuschlag für Zahlungsart';
		$discount_ordernumber = isset($this->sSYSTEM->sCONFIG['sSHIPPINGDISCOUNTNUMBER']) ? $this->sSYSTEM->sCONFIG['sSHIPPINGDISCOUNTNUMBER'] : 'SHIPPINGDISCOUNT';
		$discount_name = isset($this->sSYSTEM->sCONFIG["sSHIPPINGDISCOUNTNAME"]) ? $this->sSYSTEM->sCONFIG["sSHIPPINGDISCOUNTNAME"] : 'Warenkorbrabatt';
		$percent_ordernumber = isset($this->sSYSTEM->sCONFIG['sPAYMENTSURCHARGENUMBER']) ? $this->sSYSTEM->sCONFIG['sPAYMENTSURCHARGENUMBER']: "PAYMENTSURCHARGE";
		$discount_basket_ordernumber = isset($this->sSYSTEM->sCONFIG['sDISCOUNTNUMBER']) ? $this->sSYSTEM->sCONFIG['sDISCOUNTNUMBER']: 'DISCOUNT';
		$discount_basket_name = isset($this->sSYSTEM->sCONFIG['sDISCOUNTNAME']) ? $this->sSYSTEM->sCONFIG['sDISCOUNTNAME']: 'Warenkorbrabatt';

		$sql = 'DELETE FROM s_order_basket WHERE sessionID=? AND modus IN (3, 4) AND ordernumber IN (?, ?, ?, ?)';
		$this->sSYSTEM->sDB_CONNECTION->Execute($sql, array(
			$this->sSYSTEM->sSESSION_ID,
			$surcharge_ordernumber,
			$discount_ordernumber,
			$percent_ordernumber,
			$discount_basket_ordernumber
		));

		$basket = $this->sGetDispatchBasket(empty($country['id']) ? null : $country['id']);
		if(empty($basket)) return false;
		$country = $this->sGetCountry($basket['countryID']);
		if(empty($country)) return false;
		$payment = $this->sGetPaymentmean($basket['paymentID']);
		if(empty($payment)) return false;

		$sql = 'SELECT SUM((CAST(price as DECIMAL(10,2))*quantity)/currencyFactor) as amount FROM s_order_basket WHERE sessionID=? GROUP BY sessionID';
		$amount = $this->sSYSTEM->sDB_CONNECTION->GetOne($sql, array($this->sSYSTEM->sSESSION_ID));

		$sql = '
			SELECT basketdiscount
			FROM s_core_customergroups_discounts
			WHERE groupID=?
			AND basketdiscountstart<=?
			ORDER BY basketdiscountstart DESC
		';
		$basket_discount = $this->sSYSTEM->sDB_CONNECTION->GetOne($sql,array($this->sSYSTEM->sUSERGROUPDATA['id'], $amount));

		if(!empty($basket_discount)){

			$percent = $basket_discount;
			$basket_discount = round($basket_discount/100*$amount, 2);

			if (empty($this->sSYSTEM->sUSERGROUPDATA["tax"]) && !empty($this->sSYSTEM->sUSERGROUPDATA["id"])){
				$basket_discount_net = $basket_discount;
			} else {
				$basket_discount_net = round($basket_discount/(100+$discount_tax)*100,2);

			}
            $tax_rate = $discount_tax;
			$basket_discount_net = $basket_discount_net *-1;
			$basket_discount = $basket_discount *-1;

			$sql = '
				INSERT INTO s_order_basket
					(sessionID, articlename, articleID, ordernumber, quantity, price, netprice, tax_rate, datum, modus, currencyFactor)
				VALUES
					(?, ?, 0, ?, 1, ?, ?, ?, NOW(), 3, ?)
			';
			$this->sSYSTEM->sDB_CONNECTION->Execute($sql,array(
				$this->sSYSTEM->sSESSION_ID,
				'- '.$percent.' % '.$discount_basket_name,
				$discount_basket_ordernumber,
				$basket_discount,
				$basket_discount_net,
                $tax_rate,
				$currencyFactor
			));
		}

		$discount = $this->sGetPremiumDispatchSurcharge($basket, 3);

		if(!empty($discount))
		{
			$discount *= -1;
			if (empty($this->sSYSTEM->sUSERGROUPDATA["tax"]) && !empty($this->sSYSTEM->sUSERGROUPDATA["id"])){
				$discount_net = $discount;
                //$tax_rate = 0;
			} else {
				$discount_net = round($discount/(100+$discount_tax)*100,2);
                //$tax_rate = $discount_tax;
			}
            $tax_rate = $discount_tax;
			$sql = '
				INSERT INTO s_order_basket
					(sessionID, articlename, articleID, ordernumber, quantity, price, netprice,tax_rate, datum, modus, currencyFactor)
				VALUES
					(?, ?, 0, ?, 1, ?, ?, ?, NOW(), 4, ?)
			';

			$this->sSYSTEM->sDB_CONNECTION->Execute($sql,array(
				$this->sSYSTEM->sSESSION_ID,
				$discount_name,
				$discount_ordernumber,
				$discount,
				$discount_net,
                $tax_rate,
				$currencyFactor
			));
		}

		$dispatch = $this->sGetPremiumDispatch((int)$this->sSYSTEM->_SESSION['sDispatch']);

		if (!empty($payment['country_surcharge'][$country['countryiso']]))
			$payment['surcharge'] += $payment['country_surcharge'][$country['countryiso']];
		$payment['surcharge'] = round($payment['surcharge']*$currencyFactor,2);

		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;
			$sql = '
				INSERT INTO s_order_basket
					(sessionID, articlename, articleID, ordernumber, quantity, price, netprice, tax_rate, datum, modus, currencyFactor)
				VALUES
					(?, ?, 0, ?, 1, ?, ?, ?,NOW(), 4, ?)
			';
			$this->sSYSTEM->sDB_CONNECTION->Execute($sql,array(
				$this->sSYSTEM->sSESSION_ID,
				$surcharge_name,
				$surcharge_ordernumber,
				$surcharge,
				$surcharge_net,
                $tax_rate,
				$currencyFactor
			));
		}
		if(!empty($payment['debit_percent'])&&(empty($dispatch)||$dispatch['surcharge_calculation']!=2))
		{
			$sql = 'SELECT SUM(quantity*price) as amount FROM s_order_basket WHERE sessionID=? GROUP BY sessionID';
			$amount = $this->sSYSTEM->sDB_CONNECTION->GetOne($sql, array($this->sSYSTEM->sSESSION_ID));

			$percent = round($amount / 100 * $payment['debit_percent'], 2);

			if ($percent>0) {
				$percent_name = $this->sSYSTEM->sCONFIG["sPAYMENTSURCHARGEADD"];
			} else {
				$percent_name = $this->sSYSTEM->sCONFIG["sPAYMENTSURCHARGEDEV"];
			}

			if (empty($this->sSYSTEM->sUSERGROUPDATA["tax"]) && !empty($this->sSYSTEM->sUSERGROUPDATA["id"])){
				$percent_net = $percent;
                //$tax_rate = 0;
			} else {
				$percent_net = round($percent/(100+$discount_tax)*100,2);

			}
            $tax_rate = $discount_tax;
			$sql = '
				INSERT INTO s_order_basket
					(sessionID, articlename, articleID, ordernumber, quantity, price, netprice, tax_rate, datum, modus, currencyFactor)
				VALUES
					(?, ?, 0, ?, 1, ?, ?, ?, NOW(), 4, ?)
			';
			$this->sSYSTEM->sDB_CONNECTION->Execute($sql,array(
				$this->sSYSTEM->sSESSION_ID,
				$percent_name,
				$percent_ordernumber,
				$percent,
				$percent_net,
                $tax_rate,
				$currencyFactor
			));
		}

		if(empty($dispatch)) return array('brutto'=>0, 'netto'=>0);

		if (empty($this->sSYSTEM->sUSERGROUPDATA["tax"]) && !empty($this->sSYSTEM->sUSERGROUPDATA["id"])){
			$dispatch['shippingfree'] = round($dispatch['shippingfree']/(100+$discount_tax)*100,2);
		} else {
			$dispatch['shippingfree'] = $dispatch['shippingfree'];
		}

		if ((!empty($dispatch['shippingfree'])&&$dispatch['shippingfree']<=$basket['amount_display'])
			||empty($basket['count_article'])
			||(!empty($basket['shippingfree'])&&empty($dispatch['bind_shippingfree']))
		)
		{
			if(empty($dispatch['surcharge_calculation'])&&!empty($payment['surcharge']))
				return array(
					'brutto'=>$payment['surcharge'],
					'netto'=>round($payment['surcharge']*100/(100+$this->sSYSTEM->sCONFIG['sTAXSHIPPING']),2)
				);
			else
				return array('brutto'=>0, 'netto'=>0);
		}

		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']],2);
		else
			return false;
		$sql = "
			SELECT `value` , `factor`
			FROM `s_premium_shippingcosts`
			WHERE `from` <= $from
			AND `dispatchID` = {$dispatch['id']}
			ORDER BY `from` DESC
			LIMIT 1
		";
		$result = $this->sSYSTEM->sDB_CONNECTION->GetRow($sql);
		if($result===false) return false;

		if(!empty($dispatch['shippingfree']))
		{
			$result['shippingfree'] = round($dispatch['shippingfree']*$currencyFactor,2);
			$difference = round(($dispatch['shippingfree']-$basket['amount_display'])*$currencyFactor,2);
			$result['difference'] = array("float"=>$difference,"formated"=>$this->sSYSTEM->sMODULES['sArticles']->sFormatPrice($difference));
		}
		$result['brutto'] = $result['value'];
		if(!empty($result['factor']))
			$result['brutto'] +=  $result['factor']/100*$from;
		$result['surcharge'] = $this->sGetPremiumDispatchSurcharge($basket);
		if(!empty($result['surcharge']))
			$result['brutto'] +=  $result['surcharge'];
		$result['brutto'] *= $currencyFactor;
		$result['brutto'] = round($result['brutto'],2);
		if(!empty($payment['surcharge'])&&$dispatch['surcharge_calculation']!=2&&(empty($basket['shippingfree'])||empty($dispatch['surcharge_calculation'])))
		{
			$result['surcharge'] = $payment['surcharge'];
			$result['brutto'] += $result['surcharge'];
		}
		if($result['brutto']<0)
		{
			return array('brutto'=>0, 'netto'=>0);
		}
		if(empty($dispatch['tax_calculation']))
			$result['tax'] = $basket['max_tax'];
		else
			$result['tax'] = $dispatch['tax_calculation_value'];
        $result['tax'] = (float)$result['tax'];
		$result['netto'] = round($result['brutto']*100/(100+$result['tax']),2);

		return $result;
	}
}
Return current item: Shopware