URL: http://www.uburst.com/cgi-bin/dcforum/dcboard.cgi
Forum: ushop_prog_tips
Thread Number: 147
[ Go back to previous page ]

Original Message
"Support for New Zoned Priority Mail Rates"

Posted by Brian Levine [blevine@cfl.rr.com] on at 09:30 AM
I have worked out and implemented a system for dealing with the new priority mail rates going into effect. The rates will now vary by postal zone, meaning that the correct zone will have to be calculated. Using the custom shipping function in the ushop.pl, this is possible.

Two tables have to be created on the server. For my system, I call them ShippingRate and ShippingZone.

ShippingRate - rates by weight per zone.
Fields:
Id - unique Id
Type - shipping type ('priority', etc.)
Zone - zone number
WeightLow - low end of weight range
WeightHigh - high end of weight range
Price - price for this weight in zone

ShippingZone - zone chart for your zipcode
Id - unique Id
PreStart - starting prefix for zone range
PreEnd - ending prefix for zone range
Zone - zone number in this range

I have a built and working ShippingRate table, and anyone is welcome to it (I can supply it in access format, but you will need to upload it to your mySql database)

Shipping Zone is unique for every zipcode, so it must be populated with data supplied by the postal service. Here is a link to the page for calculating the zones:

http://postcalc.usps.gov/Zonecharts/

Finally, here is the perl code for the calculations, which goes at the end of the custom shipping charges function in ushop.pl:

#code to handle new priority mail rates
use DBI;
$dbh = DBI->connect('dbi:mysql:yourdb','yourid,'yourpassword');

$sql = "select Zone from ShippingZone where '$billing_zip_code' >= PreStart and '$billing_zip_code' prepare($sql);
$sth->execute || die "Could not execute SQL statement ... maybe invalid?";

@row=$sth->fetchrow_array;
$prefix = $row[0];

$sql = "select Price from ShippingRate where Type = 'priority' and $totals_total_weight > WeightLow and $totals_total_weight prepare($sql);
$sth->execute || die "Could not execute SQL statement ... maybe invalid?";

@row=$sth->fetchrow_array;
$shipprice = $row[0];
$ret_value += $shipprice;
$delivery_confirmation = 0.45;
$ret_value += $delivery_confirmation;
$dbh->disconnect;

return ($ret_value);

My sure to supply the correct info the the connect statement. I have this working on my site. Feel free to use this code, and email me with questions, or to get a copy of the tables I used.

One item that I will have to address is that I am using the billing zipcode. I need to check to see if the shipping zipcode is not empty and use it if so.

Good luck!


Table of contents

Messages in this discussion
"More on Sample Code"
Posted by Brian Levine [blevine@cfl.rr.com] on at 09:37 AM
As usual, the forum ate up some of the code tags. If you would like the complete code snippet, email me.

"Almost Forgot..."
Posted by Brian Levine [blevine@cfl.rr.com] on at 11:20 AM
All we need is the 3 charachter prefix of the zipcode, so add the following to the beginning:

$zipcode = substr($billing_zip_code, 0, 3);

Then use $zipcode in the where clause of the first sql statement.


"RE: Almost Forgot..."
Posted by Bill Weiner on at 05:20 AM
Thanks for the contribution/information, Brian!


"Final Comment"
Posted by Brian Levine [blevine@cfl.rr.com] on at 05:15 PM
One final note: the reason I added a Type field to the ShippingRate table, is so that the same function can support various types (priority, 1st class, UPS, etc.). You would simply set up different shipping names for each shipping type and then pass $shipping_name to the query as the type to get the correct rate.