public function getDispatchmethodPrice()
{
if (Session::getActiveShippingCountryId() == NULL) {
$shippingCountryId = $this->registry->loader->getParam('countryid');
} else {
$shippingCountryId = Session::getActiveShippingCountryId();
}
$cartData = Session::getActiveCartForDelivery();
$globalprice = round(isset($cartData['price']) ? $cartData['price'] : 0, 2);
$globalweight = isset($cartData['weight']) ? $cartData['weight'] : 0;
$Data = array();
$sql = "SELECT \n\t\t\t\t\tDP.dispatchmethodid, \n\t\t\t\t\tDP.`from`, \n\t\t\t\t\tDP.`to`, \n\t\t\t\t\tV.value, \n\t\t\t\t\tDP.iddispatchmethodprice,\n\t\t\t\t\tROUND(DP.dispatchmethodcost * CR.exchangerate, 4) AS dispatchmethodcostnetto,\n\t\t\t\t\tIF(DP.vat IS NOT NULL, ROUND((DP.dispatchmethodcost + (DP.dispatchmethodcost*(V.`value`/100))) * CR.exchangerate,4), DP.dispatchmethodcost * CR.exchangerate) as dispatchmethodcost,\n\t\t\t\t\tCASE\n \t\t\t\t\t\tWHEN (`from` <> 0 AND `from` <= :globalprice AND `to` = 0 AND DP.dispatchmethodcost =0) THEN DMT.name\n \t\t\t\t\t \tWHEN (:globalprice BETWEEN `from` AND `to`) THEN DMT.name\n \t\t\t\t\t\tWHEN (`to` = 0 AND `from` <= :globalprice AND DP.dispatchmethodcost <> 0) THEN DMT.name\n \t\t\t\t\t\tWHEN (`from` = 0 AND `to` = 0 AND DP.dispatchmethodcost = 0) THEN DMT.name\n\t\t\t\t\tEND as name,\n\t\t\t\t\tD.description,\n\t\t\t\t\tD.photo,\n\t\t\t\t\tD.countryids\n\t\t\t\tFROM dispatchmethodprice DP\n\t\t\t\tLEFT JOIN dispatchmethod D ON D.iddispatchmethod = DP.dispatchmethodid\n LEFT JOIN dispatchmethodtranslation DMT ON DMT.dispatchmethodid = D.iddispatchmethod AND DMT.languageid = :languageid\n\t\t\t\tLEFT JOIN currencyrates CR ON CR.currencyfrom = D.currencyid AND CR.currencyto = :currencyto\n\t\t\t\tLEFT JOIN vat V ON V.idvat = DP.vat\n\t\t\t\tLEFT JOIN dispatchmethodview DV ON DV.dispatchmethodid = D.iddispatchmethod\n\t\t\t\tWHERE DV.viewid= :viewid AND IF(D.maximumweight IS NOT NULL, D.maximumweight >= :globalweight, 1) AND D.type = 1\n\t\t\t\tORDER BY D.hierarchy ASC";
$stmt = Db::getInstance()->prepare($sql);
$stmt->bindValue('languageid', Helper::getLanguageId());
$stmt->bindValue('globalprice', $globalprice);
$stmt->bindValue('globalweight', $globalweight);
$stmt->bindValue('currencyto', Session::getActiveCurrencyId());
$stmt->bindValue('viewid', Helper::getViewId());
$stmt->execute();
while ($rs = $stmt->fetch()) {
$dispatchmethodid = $rs['dispatchmethodid'];
if ($rs['name'] != NULL) {
$dispatchmethodcost = $rs['dispatchmethodcost'];
$dispatchmethodcostnetto = $rs['dispatchmethodcostnetto'];
$countryIds = explode(',', $rs['countryids']);
if (in_array($shippingCountryId, $countryIds) || $rs['countryids'] == '') {
$Data[$dispatchmethodid] = array('dispatchmethodid' => $dispatchmethodid, 'name' => _($rs['name']), 'from' => $rs['from'], 'to' => $rs['to'], 'countryids' => $countryIds, 'vatvalue' => $rs['value'], 'dispatchmethodcost' => $dispatchmethodcost, 'dispatchmethodcostnetto' => $dispatchmethodcostnetto, 'description' => $rs['description'], 'photo' => App::getModel('gallery')->getImagePath(App::getModel('gallery')->getMediumImageById($rs['photo'])));
}
}
}
$sql = "SELECT\n\t\t\t\t\tDW.dispatchmethodid, \n\t\t\t\t\tDW.`from`, \n\t\t\t\t\tDW.`to`, \n\t\t\t\t\tDW.iddispatchmethodweight,\n\t\t\t\t\tROUND(DW.cost * CR.exchangerate, 4) AS dispatchmethodcostnetto,\n\t\t\t\t\tIF(DW.vat IS NOT NULL, ROUND((DW.cost+(DW.cost*(V.`value`/100))) * CR.exchangerate,4), DW.cost * CR.exchangerate) as dispatchmethodcost, \n\t\t\t\t\tD.freedelivery,\n\t\t\t\t\tCASE\n \t\t\t\t\t\tWHEN (`from` <> 0 AND `from` <= :globalweight AND `to` = 0 AND DW.cost = 0) THEN DMT.name\n \t\t\t\t\t \tWHEN (:globalweight BETWEEN `from` AND `to`) THEN DMT.name\n \t\t\t\t\t\tWHEN (`to` = 0 AND `from` <= :globalweight AND DW.cost <> 0) THEN DMT.name\n \t\t\t\t\t\tWHEN (`from` = 0 AND `to`=0 AND DW.cost = 0) THEN DMT.name\n\t\t\t\t\tEND as name,\n\t\t\t\t\tD.description,\n\t\t\t\t\tD.photo,\n\t\t\t\t\tV.value,\n\t\t\t\t\tD.countryids\n\t\t\t\tFROM dispatchmethodweight DW\n\t\t\t\tLEFT JOIN dispatchmethod D ON D.iddispatchmethod = DW.dispatchmethodid\n LEFT JOIN dispatchmethodtranslation DMT ON DMT.dispatchmethodid = D.iddispatchmethod AND DMT.languageid = :languageid\n\t\t\t\tLEFT JOIN currencyrates CR ON CR.currencyfrom = D.currencyid AND CR.currencyto = :currencyto\n\t\t\t\tLEFT JOIN vat V ON V.idvat = DW.vat\n\t\t\t\tLEFT JOIN dispatchmethodview DV ON DV.dispatchmethodid = D.iddispatchmethod\n\t\t\t\tWHERE DV.viewid= :viewid AND D.type = 2\n\t\t\t\tORDER BY D.hierarchy ASC";
$stmt = Db::getInstance()->prepare($sql);
$stmt->bindValue('globalprice', $globalprice);
$stmt->bindValue('globalweight', $globalweight);
$stmt->bindValue('currencyto', Session::getActiveCurrencyId());
$stmt->bindValue('viewid', Helper::getViewId());
$stmt->bindValue('languageid', Helper::getLanguageId());
$stmt->execute();
while ($rs = $stmt->fetch()) {
$dispatchmethodid = $rs['dispatchmethodid'];
if ($rs['name'] != NULL) {
if ($rs['freedelivery'] > 0 && $rs['freedelivery'] <= $globalprice) {
$dispatchmethodcost = 0.0;
$dispatchmethodcostnetto = 0;
} else {
$dispatchmethodcost = $rs['dispatchmethodcost'];
$dispatchmethodcostnetto = $rs['dispatchmethodcostnetto'];
}
$countryIds = explode(',', $rs['countryids']);
if (in_array($shippingCountryId, $countryIds) || $rs['countryids'] == '') {
$Data[$dispatchmethodid] = array('dispatchmethodid' => $dispatchmethodid, 'name' => _($rs['name']), 'from' => $rs['from'], 'to' => $rs['to'], 'countryids' => $countryIds, 'vatvalue' => $rs['value'], 'dispatchmethodcost' => $dispatchmethodcost, 'dispatchmethodcostnetto' => $dispatchmethodcostnetto, 'description' => $rs['description'], 'photo' => App::getModel('gallery')->getImagePath(App::getModel('gallery')->getMediumImageById($rs['photo'])));
}
}
}
return $Data;
}
