[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