[ic] Multi box shipping
ICdev
interchange.mail at virgin.net
Fri Nov 28 21:56:44 EST 2003
On Tuesday 25 November 2003 08:09 pm, Ron Phipps wrote:
> Hello,
>
> Has anyone built shipping tags that work in a multi box situation?
> Here's the deal. A client ships parts each weighing an ounce to 100lbs.
> I have identified the following scenarios:
>
> 1. Total weight is less than max weight for shipmode
> 2. Total weight is more than max weight for shipmode, but each item
> alone does not weight more than the max weight
> 3. Total weight is more than max weight for shipmode, but 1 or more
> items along in the order weight more then max weight
>
> Scenario 1 is easy, you price it like IC does now based on total weight
> of the order
>
> Scenario 2 is more difficult because you need to figure out how many
> boxes (not taking into account dimensions) and the weight of each box.
> You want to fill the first box to as close as max weight as possible,
> then the second box would be the left overs (or there could be 3 boxes,
> 2 being as close to full as possible and the 3rd being the leftovers).
> In this situation it's almost as though you need to arrange the parts
> being ordered into containers and then price each container separately
> then the total shipping price is made up from that. I'm not sure how to
> write the algorithms to determine which parts go in which container.
>
> Scenario 3 is simple, loop over the cart and find out if any parts are
> over the max weight. If they are you cannot ship the order and special
> arrangements need to be made.
>
> I guess I'm looking for some input on how others have handled this
> situation. In the meantime I'm going to be doing some studying on
> algorithms to determine best fit.
>
> Thanks,
> -Ron
This is what I did, and although not perfect, it does the job OK for the
moment. One issue with this is that packaging weights are not considered
until the UserTag is called, so for the time being I have set my max weight
in shipping.asc to be lower than the max weight in the shipping tables as a
workaround.
UserTag calc_shipping Order weight country
UserTag calc_shipping Routine <<EOF
sub {
my ($weight, $country) = @_;
my $zone = $Tag->query( { sql => "SELECT zone FROM country WHERE code =
'$country'",
wantarray => 1 } );
$zone = 'zone'.$zone->[0]->[0];
my %qty_req_packtype;
for (@$Items) {
my $packtype = $Tag->query( { sql => "SELECT packaging_type FROM
products WHERE sku = '$_->{code}'",
wantarray => 1 } );
$packtype = $packtype->[0]->[0];
$qty_req_packtype{$packtype} += $_->{quantity};
}
my $packaging_charge = 0;
for (keys %qty_req_packtype) {
my $price = $Tag->query( { sql => "SELECT price FROM packaging WHERE
description = '$_'",
wantarray => 1 } );
$price = $price->[0]->[0];
my $pack_weight = $Tag->query( { sql => "SELECT weight FROM packaging
WHERE description = '$_'",
wantarray => 1 } );
$pack_weight = $pack_weight->[0]->[0];
my $items_per_packtype = $Tag->query( { sql => "SELECT items FROM
packaging WHERE description = '$_'",
wantarray => 1 } );
$items_per_packtype = $items_per_packtype->[0]->[0];
my $required = $qty_req_packtype{$_} / $items_per_packtype;
if($qty_req_packtype{$_} % $items_per_packtype)
{
$required++;
$required = int($required);
}
$packaging_charge += $required * $price;
$weight += $required * $pack_weight;
}
my $shipping_charge = 0;
if ($country eq 'UK') {
$shipping_charge = $Tag->query( { sql => "SELECT $zone FROM shipping
WHERE $weight >= min_weight AND $weight <= max_weight",
wantarray => 1 } );
$shipping_charge = $shipping_charge->[0]->[0];
}
else {
$shipping_charge = $Tag->query( { sql => "SELECT $zone FROM
int_shipping WHERE $weight >= min_weight AND $weight <= max_weight",
wantarray => 1 } );
$shipping_charge = $shipping_charge->[0]->[0];
}
return $shipping_charge + $packaging_charge;
}
EOF
I created new database tables, added new columns to existing ones, and updated
the definitions:
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 "description=VARCHAR(24)"
Database packaging COLUMN_DEF "price=DECIMAL(4,2) DEFAULT '0.00'"
Database packaging COLUMN_DEF "weight=VARCHAR(4)"
Database packaging COLUMN_DEF "items=CHAR(2)"
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 "min_weight=VARCHAR(5)"
Database shipping COLUMN_DEF "max_weight=VARCHAR(5)"
Database shipping COLUMN_DEF "zone1=DECIMAL(6,2) DEFAULT '0.00'"
dbconf/mysql/int_shipping.mysql:
Database int_shipping int_shipping.txt __SQLDSN__
ifdef SQLUSER
Database int_shipping USER __SQLUSER__
endif
ifdef SQLPASS
Database int_shipping PASS __SQLPASS__
endif
Database int_shipping COLUMN_DEF "min_weight=VARCHAR(5)"
Database int_shipping COLUMN_DEF "max_weight=VARCHAR(5)"
Database int_shipping COLUMN_DEF "zone5=DECIMAL(6,2) DEFAULT '0.00'"
Database int_shipping COLUMN_DEF "zone6=DECIMAL(6,2) DEFAULT '0.00'"
Database int_shipping COLUMN_DEF "zone7=DECIMAL(6,2) DEFAULT '0.00'"
Database int_shipping COLUMN_DEF "zone8=DECIMAL(6,2) DEFAULT '0.00'"
Database int_shipping COLUMN_DEF "zone9=DECIMAL(6,2) DEFAULT '0.00'"
Database int_shipping COLUMN_DEF "zone10=DECIMAL(6,2) DEFAULT '0.00'"
Database int_shipping COLUMN_DEF "zone11=DECIMAL(6,2) DEFAULT '0.00'"
Database int_shipping COLUMN_DEF "zone12=DECIMAL(6,2) DEFAULT '0.00'"
appended to dbconf/mysql/country.mysql:
Database country COLUMN_DEF "zone=CHAR(2)"
appended to dbconf/mysql/products.mysql:
Database products COLUMN_DEF "packaging_type=VARCHAR(24)"
products/shipping.asc:
DEFAULT Default Shipping weight 0 0 e Nothing to ship! {'ui_ship_type' =>
"weight",'ups' => "0",}
DEFAULT Default Shipping weight 1 20000 f [calc_shipping weight=@@TOTAL@@
country="[value country]"] {'PriceDivide' => "1",}
DEFAULT Default Shipping weight 20001 999999 e Too heavy for standard shipping
- Please contact us. {'PriceDivide' => "1",}
Hope I haven't left anything out.
More information about the interchange-users
mailing list