[ic] Custom shipping
Interchange user
interchange-users@icdevgroup.org
Wed Jul 3 21:00:01 2002
Bill,
Thanks for your fast and helpful reply last month. I have put something
together, but cannot get it to work. I get the following message on the
checkout page:
"Shipping mode 'DEFAULT': bad formula. Returning 0.No match found for mode
'DEFAULT', quantity '1', returning 0."
Probably a syntax error somewhere. Could you (or someone else) please help me
get this working? Here are the details:
In catalog.cfg:
UserTag calc_shipping Order weight zone
UserTag calc_shipping Routine <<EOF
sub {
my ($weight, $zone) = @_;
$zone = 'zone'.$zone;
my $qty_packtype_a = 0;
my $qty_packtype_b = 0;
my $qty_packtype_c = 0;
my $qty_packtype_d = 0;
my $shipping_charge = $Tag->query(sql="SELECT $zone FROM shipping WHERE
weight = $weight");
for (@$Items) {
if ($Tag->query(sql="SELECT packaging_type FROM products WHERE sku =
'$_->{sku}'") == 'A')
$qty_packtype_a ++;
else if ($Tag->query(sql="SELECT packaging_type FROM products WHERE sku
= '$_->{sku}'") == 'B')
$qty_packtype_b ++;
else if ($Tag->query(sql="SELECT packaging_type FROM products WHERE sku
= '$_->{sku}'") == 'C')
$qty_packtype_c ++;
else if ($Tag->query(sql="SELECT packaging_type FROM products WHERE sku
= '$_->{sku}'") == 'D')
$qty_packtype_d ++;
}
my $packaging_charge = 0;
if ($qty_packtype_a)
$packaging_charge += $Tag->query(sql="SELECT packtype_a FROM packaging
WHERE quantity = $qty_packtype_a");
if ($qty_packtype_b)
$packaging_charge += $Tag->query(sql="SELECT packtype_b FROM packaging
WHERE quantity = $qty_packtype_b");
if ($qty_packtype_c)
$packaging_charge += $Tag->query(sql="SELECT packtype_c FROM packaging
WHERE quantity = $qty_packtype_c");
if ($qty_packtype_d)
$packaging_charge += $Tag->query(sql="SELECT packtype_d FROM packaging
WHERE quantity = $qty_packtype_d");
return $shipping_charge + $packaging_charge;
}
EOF
In shipping.asc:
DEFAULT: Default Shipping
criteria weight
min 0
max 0
cost e Nothing to ship!
min 1
max 150
cost f [calc_shipping weight=@@TOTAL@@ zone=[query sql="SELECT zone FROM
country WHERE code = '[value country]'"][/query]]
min 151
max 99999
cost e @@TOTAL@@ lb too heavy - please contact us.
In dbconf/mysql/shipping.mysql:
Database shipping shipping.txt __SQLDSN__
ifdef SQLUSER
Database shipping USER __SQLUSER__
endif
ifdef SQLPASS
Database shipping PASS __SQLPASS__
endif
Database shipping COLUMN_DEF "weight=CHAR(4) DEFAULT '0'"
Database shipping COLUMN_DEF "zone1=DECIMAL(4,2) DEFAULT '0.00'"
Database shipping COLUMN_DEF "zone2=DECIMAL(4,2) DEFAULT '0.00'"
Database shipping COLUMN_DEF "zone3=DECIMAL(4,2) DEFAULT '0.00'"
Database shipping COLUMN_DEF "zone4=DECIMAL(4,2) DEFAULT '0.00'"
In dbconf/mysql/packaging.mysql:
Database packaging packaging.txt __SQLDSN__
ifdef SQLUSER
Database packaging USER __SQLUSER__
endif
ifdef SQLPASS
Database packaging PASS __SQLPASS__
endif
Database packaging COLUMN_DEF "quantity=CHAR(4) DEFAULT '0'"
Database packaging COLUMN_DEF "packtype_a=DECIMAL(4,2) DEFAULT '0.00'"
Database packaging COLUMN_DEF "packtype_b=DECIMAL(4,2) DEFAULT '0.00'"
Database packaging COLUMN_DEF "packtype_c=DECIMAL(4,2) DEFAULT '0.00'"
Database packaging COLUMN_DEF "packtype_d=DECIMAL(4,2) DEFAULT '0.00'"
Added to dbconf/mysql/products.mysql:
Database products COLUMN_DEF "packaging_type=CHAR(1)"
Added to dbconf/mysql/country.mysql:
Database country COLUMN_DEF "zone=CHAR(1)"
Thank you.
On Thursday 13 June 2002 11:01 pm, you wrote:
> On Thu, 2002-06-13 at 17:23, Interchange user wrote:
> > Hello,
> >
> > I've been trying to come up with a solution for a custom shipping
> > routine, and would appreciate any comments or help implementing this. I'm
> > not sure whether this can be done using IC tags in 'shipping.asc', or if
> > I will need to define a user tag, in which case, I am most comfortable
> > with C-style Perl.
> >
> > OK, here's the deal:
> >
> > Shipping cost will be calculated by weight and zone (there are 4 zones),
> > but items will have different packaging types. For example, posters will
> > use poster tubes, and books will use boxes. Also, there are limits to how
> > many items will fit into any given packaging type. For example, up to 5
> > posters can fit into a tube. An item can also have more than one
> > packaging type. For example, on their own, a set of postcards would
> > probably go in a padded envelope, but if they were ordered with a book,
> > everything would go into a single box.
> >
> > A rough idea of the steps required (I'm using MySQL):
> >
> > - Loop through each item in cart,
> > - Add weight of all items together,
> > - Count the quantities of each packing type required from a
> > 'packaging_type' field in the 'products' table,
> > - Index into a 'shipping' table to find a price for the 'weight/zone'
> > pair,
> > - Index into a 'packaging' table to find a price for the
> > 'quantity/packaging_type' pair, for each required packaging type,
> > - Add the looked up shipping cost and packaging cost(s) together
> >
> > This method is not perfect, but it's the best I have come up with so far.
> > For example, if more than two packaging types are required (say, a poster
> > tube and a box), it does not account for the fact that it is more
> > expensive to send split orders than just one of the same weight. Then
> > there is the situation where someone orders items from various
> > categories, that together use a different packaging type than they would
> > individually, and the customer could be charged for packaging they don't
> > need. Again, taking the above example; on their own, a set of postcards
> > would probably go in a padded envelope, but if they were ordered with a
> > book, everything would go into a single box.
> >
> > However, I think it will be better to lose some here and gain some there,
> > than to have a horribly complicated and cycle-hogging routine that takes
> > into consideration every possible combination of items ordered.
>
> Here is a set up I use for a Wine merchant. It is simpler than your
> setup but it should help.
>
> My checkout page sets scratch weight.
>
> catalog.cfg:
> UserTag shipping_cost Order mode weight zip country
> UserTag shipping_cost Routine <<EOR
> sub {
> my ($mode, $weight, $zip, $country) = @_;
> $mode = $Variable->{SHIPPING_DEFAULT_MODE} unless $mode;
> if ($Values->{zip} =~ /^(\d{5})/) {
> $zip = $1;
> } else {
> $zip = $Variable->{SHIP_DEFAULT_ZIP};
> }
> my $ups_charge = $Tag->ups_query( {
> zip => $zip,
> mode => $mode,
> weight => $weight,
> country => $country
> } );
> return $ups_charge + &container_charge();
> sub container_charge {
> my $bottles = 0;
> for (@$Items) {
> next if $_->{mv_ib} eq 'products';
> $bottles += $_->{quantity} * $_->{pack};
> }
> return 3 if $bottles <= 1;
> return 4 if $bottles <= 2;
> return 5 if $bottles <= 3;
> return 6 if $bottles <= 6;
> return 12 if $bottles <= 12;
> return $bottles * 1.30;
> }
> }
> EOR
>
> shipping.asc:
> 1DA: UPS Next Day Air
> criteria [scratch weight]
> min 1
> max 150
> at_least 5
> cost f [shipping_cost mode=1DA weight=@@TOTAL@@
> zip='[value zip]']
>
> 2DA: 2nd Day Air
> criteria [scratch weight]
> min 1
> max 150
> at_least 5
> cost f [shipping_cost mode=2DA weight=@@TOTAL@@
> zip='[value zip]']
>
> GNDRES: UPS Ground
> criteria [scratch weight]
> min 1
> max 150
> at_least 5
> cost f [shipping_cost mode=GNDRES weight=@@TOTAL@@
> zip='[value zip]']
>
> XPD: UPS Worldwide Expedited
> criteria [scratch weight]
> min 1
> max 150
> at_least 5
> cost f [shipping_cost mode=XPD weight=@@TOTAL@@
> zip='[value zip]' country='[value country]']
>
> XPR: UPS Worldwide Express
> criteria [scratch weight]
> min 1
> max 150
> at_least 5
> cost f [shipping_cost mode=XPR weight=@@TOTAL@@
> zip='[value zip]' country='[value country]']
>
> > Thank you in advance!