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,
comments varchar(255)
)
/* Assign package types */
insert into package_types (max_weight, comments)
values (16, 'Flat')
insert into package_types (max_weight, comments)
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,
email_address varchar(255),
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 */
insert into purchase_order(email_address)
values ('
[email protected]')
/* 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
In need help with query design....
