Wrox Programmer Forums In need help with query design....
 |
 MySQL General discussion about the MySQL database.
 Welcome to the p2p.wrox.com Forums. You are currently viewing the MySQL section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com

March 28th, 2007, 09:03 AM
 seananderson Friend of Wrox Join Date: Oct 2006 Posts: 114 Thanks: 0 Thanked 0 Times in 0 Posts
In need help with query design....

Based on the number of products, their weight and dimensions, I need to calculate the shipping cost, number of parcels and a handling fee, which is a combination of (a) percentage of shipping cost and (b) multiple of the number of packages, along the lines of 5% of the cost plus Â£1 per parcel.

Whether this can be done from what I have written so far, a variation of what I have or something totally new, I don't mind so long as I have a working solution.

-----

The following script creates the SQL version of my tables and records.

You will see that there is one purchase order for a total of 11 items which are packed in two package types (flat and cube). What I need is to determine the total cost for shipping, handling and number of parcels, based on the following:

The basic cost of a package is fixed (eg: Â£10.00) for the first 8kg and an extra cost Â£0.50/kg up to the maximum package weight.

So my inventory is

Package Type 1
4x product 1 @ 3.0kg = 12.0kg
2x product 2 @ 3.5kg = 7.0kg
5x product 5 @ 1.0kg = 5.0kg
== 2 Parcels totalling Â£24.00 as
1 @ 16kg (Â£10 base + 8 x 50p extra = Â£14.00)
1 @ 8kg (Â£10 base)

Package Type 2
1x product 2 @ 6.0kg = 6.0kg
== 1 Parcel totalling Â£10.00 as
1 @ 6kg (Â£10 base)

*** RESULTS THAT I NEED ***
Shipping = Â£34.00
Parcels = 3
Handling = ((Shipping / 100) x HandlingPercentageRate) + (Parcels x HandlingPackageRate)

------------ START OF SCRIPT ------------

/* Create the PRODUCTS table */

create table products
(
product_id int identity primary key,
title varchar(255),
details varchar(2000),
price smallmoney,
package_type_id int,
weight real
)

/* Assign some products */

insert into products (title, details, price, package_type_id, weight)
values ('Title 1', '1000 x 400 x 40mm (Economy)', 10.00, 1, 3.0)

insert into products (title, details, price, package_type_id, weight)
values ('Title 2', '1000 x 400 x 50mm (Standard)', 12.50, 1, 3.5)

insert into products (title, details, price, package_type_id, weight)
values ('Title 3', '1000 x 400 x 60mm (Deluxe)', 15.00, 1, 4.0)

insert into products (title, details, price, package_type_id, weight)
values ('Title 4', '600 x 600 x 150mm', 25.00, 2, 6.0)

insert into products (title, details, price, package_type_id, weight)
values ('Title 5', '1000 x 20 x 20', 3.50, 1, 1.0)

/* Create the PACKAGE TYPES */

create table package_types
(
package_type_id int identity primary key,
max_weight real,
)

/* Assign package types */

insert into package_types (max_weight, comments) values (16, 'Flat')
insert into package_types (max_weight, comments) values (12, 'Cube')

/* Create a PURCHASE ORDER table */

create table purchase_order
(
purchase_order_id int identity primary key,
item_count int default 0,
packages int default 0,
shipping smallmoney default 0
)

/* Add a purchase order */

/* Create an INVENTORY table */

create table purchase_order_items
(
purchase_order_item_id int identity primary key,
purchase_order_id int,
product_id int,
quantity int,
)

/* Assign some items to the purachse order */

insert into purchase_order_items (purchase_order_id, product_id, quantity)
values (1, 1, 4)
insert into purchase_order_items (purchase_order_id, product_id, quantity)
values (1, 2, 2)
insert into purchase_order_items (purchase_order_id, product_id, quantity)
values (1, 4, 1)
insert into purchase_order_items (purchase_order_id, product_id, quantity)
values (1, 5, 5)

/* View the results */

select * from products
select * from package_types
select * from purchase_order
select * from purchase_order_items

------------ END OF SCRIPT ------------

Regards,

Sean Anderson
__________________
Regards,

Sean Anderson

March 29th, 2007, 10:02 AM
 seananderson Friend of Wrox Join Date: Oct 2006 Posts: 114 Thanks: 0 Thanked 0 Times in 0 Posts

Extended the script and now I do get the results, in SQL.

Just need to turn the calculation work in SQL into PHP and the rest of it into MySQL.

Not a five minute job, I guess....

------------ START OF SCRIPT ------------
/* Create the PRODUCTS table */
create table products
(
product_id int identity primary key,
title varchar(255),
details varchar(2000),
price smallmoney,
package_type_id int,
weight real
)

/* Assign some products */
insert into products (title, details, price, package_type_id, weight)
values ('Title 1', '1000 x 400 x 40mm (Economy)', 10.00, 1, 3.5)
insert into products (title, details, price, package_type_id, weight)
values ('Title 2', '1000 x 400 x 50mm (Standard)', 12.50, 1, 3.75)
insert into products (title, details, price, package_type_id, weight)
values ('Title 3', '1000 x 400 x 60mm (Deluxe)', 15.00, 1, 4.0)
insert into products (title, details, price, package_type_id, weight)
values ('Title 4', '600 x 600 x 150mm', 25.00, 2, 6.0)
insert into products (title, details, price, package_type_id, weight)
values ('Title 5', '1000 x 20 x 20', 3.50, 1, 0.5)

/* Create the PACKAGE TYPES */
create table package_types
(
package_type_id int identity primary key,
max_weight real,
)

/* Assign package types */
values (16, 'Flat')
values (12, 'Cube')

/* Create COSTS */
create table costs
(
cost_id int identity primary key,
postage_basic smallmoney,
postage_extra_kg smallmoney,
packaging_percentage int,
packaging_per_pack smallmoney,
)

/* Set the current rates (Example used is: Â£10 up to 8kg, 50p p/kg thereafer. Packaging @ 5%, plus Â£1 per pack)*/
insert into costs (postage_basic, postage_extra_kg, packaging_percentage, packaging_per_pack)
values (10.00, 0.50, 5, 1)

/* Create a PURCHASE ORDER table */
create table purchase_order
(
purchase_order_id int identity primary key,
item_count int default 0,
items_cost smallmoney default 0,
packages int default 0,
shipping smallmoney default 0,
handling smallmoney default 0
)

/* Add a purchase order */

/* Create an INVENTORY table */
create table purchase_order_items
(
purchase_order_item_id int identity primary key,
purchase_order_id int,
product_id int,
quantity int,
)

/* Assign some items to the purachse order */
insert into purchase_order_items (purchase_order_id, product_id, quantity)
values (1, 2, 10)
insert into purchase_order_items (purchase_order_id, product_id, quantity)
values (1, 3, 10)

/* Create a table to calculate postage costs*/
create table postage
(
postage_id int identity,
purchase_order_id int,
package_type_id int,
item_total_cost smallmoney,
item_total_weight real,
package_max_weight real,
extra_kg real default 0
)

go

/* Start calculating postage */

delete from postage where purchase_order_id = 1

insert into postage (purchase_order_id, package_type_id, item_total_cost, item_total_weight, package_max_weight)
select
po.purchase_order_id,
p.package_type_id,
sum(p.price * poi.quantity) as item_total_cost,
sum(p.weight * poi.quantity) as item_total_weight,
pt.max_weight
from purchase_order po
join purchase_order_items poi on poi.purchase_order_id = po.purchase_order_id
join products p on p.product_id = poi.product_id
join package_types pt on pt.package_type_id = p.package_type_id
where po.purchase_order_id = 1
group by po.purchase_order_id, p.package_type_id, pt.max_weight
order by p.package_type_id

declare @postage_id int
declare @package_type_id int
declare @purchase_order_id int
declare @package_max_weight real

while
(
select count(purchase_order_id)
from postage
where item_total_weight > package_max_weight
) > 0
begin
select
@postage_id = postage_id,
@package_type_id = package_type_id,
@purchase_order_id = purchase_order_id,
@package_max_weight = package_max_weight
from postage
where item_total_weight > package_max_weight

update postage
set item_total_weight = item_total_weight - package_max_weight
where postage_id = @postage_id

insert into postage (purchase_order_id, package_type_id, item_total_cost, item_total_weight, package_max_weight)
values (@purchase_order_id, @package_type_id, 0, @package_max_weight, @package_max_weight)

end

update postage
set extra_kg = item_total_weight - 8
where item_total_weight > 8

declare @package_count int
declare @extra smallmoney
declare @inventory smallmoney
declare @quantity int

/* number of packages, extra KG weight and cost of physical items */
select @extra = sum(extra_kg), @package_count = count(*), @inventory = sum(item_total_cost)
from postage
where purchase_order_id = 1

/* number of items */
select @quantity = sum(quantity)
from purchase_order_items
where purchase_order_id = 1

declare @cost_basic smallmoney
declare @cost_extra smallmoney
declare @cost_percent int
declare @cost_perpack smallmoney

/* Get the costs*/
select top 1
@cost_basic = postage_basic,
@cost_extra = postage_extra_kg,
@cost_percent = packaging_percentage,
@cost_perpack = packaging_per_pack
from costs
order by cost_id desc

/* Update the totals on the purchase order */
update purchase_order
set
item_count = @quantity,
items_cost = @inventory,
packages = @package_count,
shipping = (@package_count * @cost_basic) + (@extra * @cost_extra),
handling = (((@package_count * @cost_basic) + (@extra * @cost_extra)) / 100 * @cost_percent) + (@package_count * @cost_perpack)
where purchase_order_id = 1

/* View the invoice (2 recordsets for ease of view in SQL) */
select p.title, p.details, poi.quantity, p.price
from purchase_order po
join purchase_order_items poi on poi.purchase_order_id = po.purchase_order_id
join products p on p.product_id = poi.product_id
where po.purchase_order_id = 1

select packages, items_cost as sub_total, (shipping + handling) as postage_and_packaging, (items_cost + shipping + handling) as order_total
from purchase_order
where purchase_order_id = 1

Regards,

Sean Anderson

 Similar Threads Thread Thread Starter Forum Replies Last Post IS-A , not CONTAINS-A design possible? LawrenceHickey XML 3 May 16th, 2007 03:34 PM Design patterns for web design ceadge HTML Code Clinic 0 June 19th, 2006 11:26 AM How to design a query for this bounded form method Access 0 June 24th, 2005 05:47 AM Design help j.gonsalves@ntlworld.com Access 2 August 16th, 2004 10:01 AM Java Design issue with UML and Design Patterns the_logical_way Apache Tomcat 0 May 31st, 2004 04:02 AM