shipping calculations are not correctly calculated
Hi, I have a shipping system that calculates basket based on weight X size X ChargeDelivery(ChargeDelivery is a checkbox on products table).
(Also please see screenshots)
In my eCart General tab:
Number format: 10,000.00
In my eCart Columns tab:
ChargeDelivery = (this is a number)
Width = (this is a number)
Height = (this is a number)
Depth = (this is a number)
In my Calculations tab:
TotalWeight = [Weight] * [Quantity] * [ChargeDelivery]
TotalSize = ([Width] * [Height] * [Depth]) * [Quantity] * [ChargeDelivery]
QUESTION 1: is the above how it should be set up for the type of calculation I want?
My PHP code:
<?php
$rsShippingZone = new WA_MySQLi_RS("rsShippingZone",$ecartdb,1);
$rsShippingZone->setQuery("SELECT * FROM shippingzones WHERE ShippingZoneCode = ? AND ShippingZoneEnabled = 1");
$rsShippingZone->bindParam("s", "".(isset($_SESSION['ZonePostCode'])?$_SESSION['ZonePostCode']:"") ."", "-1"); //colname
$rsShippingZone->execute();
?>
<?php
$rsDeliveryCost = new WA_MySQLi_RS("rsDeliveryCost",$ecartdb,1);
$rsDeliveryCost->setQuery("SELECT * FROM shippingcosts WHERE ShippingCostZone = ? AND ShippingCostEnabled = 1 AND ShippingCostWeightTo >= ? AND ShippingCostSize >= ? ORDER BY ShippingCostValue ASC");
$rsDeliveryCost->bindParam("s", "".$rsShippingZone->getColumnVal("ShippingZone") ."", "-1"); //colname
$rsDeliveryCost->bindParam("s", "".$eCart->TotalColumn("TotalWeight") ."", "-1"); //colname2
$rsDeliveryCost->bindParam("s", "".$eCart->TotalColumn("TotalSize") ."", "-1"); //colname3
$rsDeliveryCost->execute();?>
QUESTION 2 : in the above PHP code, is that how I should be defining the SQL query?
On many test of the basket, the delivery cost ends up being FREE (so obviously its unable to select a SQL query) or the delivery cost ends up being £2.5 (max size 900cm3) Even though the basket totalsize is way over 900cm3).
Can you see any obvious mistakes in my logic?
Take a look at my test screenshots and compare to the shipping rules screenshot. if you look at test3.png its showing £12.99 for delivery cost by the total size is 13,585cm3 and totalweight is 3kg (so I cant figure out why its selecting £12.99 if you compare the sipping rules screenshot.
the code that displays FREE is:
<?php if ($eCart->GetShipping() > 0) { ?>
<strong>£<?php echo $eCart->GetShipping(); ?></strong>
<?php } else { ?>
<strong>Free</strong>
<?php } ?>
***UPDATE***
I think it maybe the sql query as I done a test outputting some sessions variables based on the 2 recordsets above. and it only displays a value from my rsShippingZone recordset. On the test page nothing is displayed from my sessions I used to grab some bindings from the rsDeliveryCost. Which would suggest that this recordset sql query is returning 0 results. Not sure why though?
I edited the recordset to the following and I am getting better results....but does the logic make sense as I remember Ray you suggested using >= and ASC for order by.
<?php
$rsDeliveryCost = new WA_MySQLi_RS("rsDeliveryCost",$ecartdb,1);
$rsDeliveryCost->setQuery("SELECT * FROM shippingcosts WHERE ShippingCostZone = ? AND ShippingCostWeightTo <= ? AND ShippingCostSize <= ? ORDER BY ShippingCostValue DESC");
$rsDeliveryCost->bindParam("s", "".$rsShippingZone->getColumnVal("ShippingZone") ."", "-1"); //colname
$rsDeliveryCost->bindParam("s", "".$eCart->TotalColumn("TotalWeight") ."", "-1"); //colname2
$rsDeliveryCost->bindParam("s", "".$eCart->TotalColumn("TotalSize") ."", "-1"); //colname3
$rsDeliveryCost->execute();?>
Chris
