[ic] Custom shipping
Interchange user
interchange-users@icdevgroup.org
Mon Jul 8 21:17:06 2002
Can anyone help me out with this? I guess the 4th of July was a bad time to
post to the list.
On Thursday 04 July 2002 01:30 am, you wrote:
> 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!