From 4ad0e50ad6cd81d0448b6303dedce631218c8835 Mon Sep 17 00:00:00 2001 From: Tony Cook Date: Thu, 25 Feb 2016 23:10:12 +1100 Subject: [PATCH] save the product tier on ordering --- schema/bse.sql | 11 ++++++++-- site/cgi-bin/modules/BSE/DB/Mysql.pm | 10 ++++----- site/cgi-bin/modules/BSE/TB/OrderItem.pm | 23 +++++++++++++++++++-- site/cgi-bin/modules/BSE/UI/Shop.pm | 6 ++++-- site/templates/admin/order_detail.tmpl | 26 ++++++++++++++---------- site/util/mysql.str | 2 ++ 6 files changed, 55 insertions(+), 23 deletions(-) diff --git a/schema/bse.sql b/schema/bse.sql index eddf4f48..c0e6ea3e 100644 --- a/schema/bse.sql +++ b/schema/bse.sql @@ -402,9 +402,11 @@ create table order_item ( product_code varchar(80) not null default '', + tier_id integer null default null, + primary key (id), index order_item_order(orderId, id) -); +) engine=InnoDB; drop table if exists other_parents; create table other_parents ( @@ -1291,7 +1293,7 @@ create table bse_price_tier_prices ( retailPrice integer not null, unique tier_product(tier_id, product_id) -); +) engine=InnoDB; create table bse_tags ( id integer not null auto_increment primary key, @@ -1387,3 +1389,8 @@ create table bse_coupon_tiers ( foreign key (tier_id) references bse_price_tiers(id) on delete cascade on update restrict ) engine=InnoDB; + +alter table order_item add constraint tier_id + foreign key (tier_id) references bse_price_tiers(id) + on delete restrict on update restrict; + diff --git a/site/cgi-bin/modules/BSE/DB/Mysql.pm b/site/cgi-bin/modules/BSE/DB/Mysql.pm index a42872c3..755da7b6 100644 --- a/site/cgi-bin/modules/BSE/DB/Mysql.pm +++ b/site/cgi-bin/modules/BSE/DB/Mysql.pm @@ -5,11 +5,9 @@ use vars qw/@ISA/; use Carp 'confess'; @ISA = qw(BSE::DB); -our $VERSION = "1.013"; +our $VERSION = "1.014"; -use vars qw($VERSION $MAX_CONNECTION_AGE); - -use Constants 0.1 qw/$DSN $UN $PW $DBOPTS/; +use vars qw($MAX_CONNECTION_AGE); use Carp; @@ -149,8 +147,8 @@ SQL getOrderItemByOrderId => 'select * from order_item where orderId = ?', #addOrder => 'insert orders values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', #replaceOrder => 'replace orders values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', - addOrderItem => 'insert order_item values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', - replaceOrderItem => 'replace order_item values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', + #addOrderItem => 'insert order_item values(null,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', + #replaceOrderItem => 'replace order_item values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', #getOrderByUserId => 'select * from orders where userId = ?', deleteOrdersItems => 'delete from order_item where orderId = ?', diff --git a/site/cgi-bin/modules/BSE/TB/OrderItem.pm b/site/cgi-bin/modules/BSE/TB/OrderItem.pm index 5a309ccf..eaf968b3 100644 --- a/site/cgi-bin/modules/BSE/TB/OrderItem.pm +++ b/site/cgi-bin/modules/BSE/TB/OrderItem.pm @@ -5,13 +5,19 @@ use Squirrel::Row; use vars qw/@ISA/; @ISA = qw/Squirrel::Row/; -our $VERSION = "1.004"; +our $VERSION = "1.005"; + +sub table { "order_item" } sub columns { return qw/id productId orderId units price wholesalePrice gst options customInt1 customInt2 customInt3 customStr1 customStr2 customStr3 title description subscription_id subscription_period max_lapsed - session_id product_code/; + session_id product_code tier_id/; +} + +sub db_columns { + return map { $_ eq "description" ? "summary" : $_ } $_[0]->columns; } sub defaults { @@ -101,4 +107,17 @@ sub extended { return $self->units * $self->$name(); } +sub tier { + my ($self) = @_; + + my $tier_id = $self->tier_id + or return; + + require BSE::TB::PriceTiers; + my $tier = BSE::TB::PriceTiers->getByPkey($tier_id) + or return; + + return $tier; +} + 1; diff --git a/site/cgi-bin/modules/BSE/UI/Shop.pm b/site/cgi-bin/modules/BSE/UI/Shop.pm index 7b59bd64..d67639e7 100644 --- a/site/cgi-bin/modules/BSE/UI/Shop.pm +++ b/site/cgi-bin/modules/BSE/UI/Shop.pm @@ -18,7 +18,7 @@ use BSE::Countries qw(bse_country_code); use BSE::Util::Secure qw(make_secret); use BSE::Template; -our $VERSION = "1.048"; +our $VERSION = "1.049"; =head1 NAME @@ -1801,7 +1801,9 @@ sub _build_items { for my $col (@prodcols) { $work{$col} = $product->$col() unless exists $work{$col}; } - $work{price} = $product->price(user => scalar $req->siteuser); + my ($price, $tier) = $product->price(user => scalar $req->siteuser); + $work{price} = $price; + $work{tier_id} = $tier ? $tier->id : undef; $work{extended_retailPrice} = $work{units} * $work{price}; $work{extended_gst} = $work{units} * $work{gst}; $work{extended_wholesale} = $work{units} * $work{wholesalePrice}; diff --git a/site/templates/admin/order_detail.tmpl b/site/templates/admin/order_detail.tmpl index 38bd109c..363a27a6 100644 --- a/site/templates/admin/order_detail.tmpl +++ b/site/templates/admin/order_detail.tmpl @@ -86,19 +86,23 @@ Unit Price Extension -<:iterator begin items:> +<:.for item in [order.items] -:> + <:.set product = @undef -:> + <:.if item.productId > 0 -:> + <:.set product = bse.products.getByPkey(item.productId) -:> + <:.end if -:> - <:ifOr [ifEq [item productId] -1] [ifEq [product id] ""]:><:item title:> (product deleted)<:or:><:product - title:><:eif:> <:options:> - <:item units:> - <:money item wholesalePrice:> - <:extension wholesalePrice:> - <:money item gst:> - <:extension gst:> - <:money item price:> - <:extension price:> + <:.if product :><:= product.title :><:.else:><:= item.title :> (product deleted)<:.end if:> <:= item.nice_options:> + <:.if item.tier_id:>
<:= item.tier.description :><:.end if:> + <:= item.units:> + <:= bse.number("money", item.wholesalePrice) :> + <:= bse.number("money", item.extended("wholesalePrice")) :> + <:= bse.number("money", item.gst):> + <:= bse.number("money", item.extended("gst")):> + <:= bse.number("money", item.price):> + <:= bse.number("money", item.extended("price")) :> -<:iterator end items:> +<:.end for -:> Total Price of goods: <:= bse.number("money", order.total_cost) -:> diff --git a/site/util/mysql.str b/site/util/mysql.str index f122d056..457e3ac8 100644 --- a/site/util/mysql.str +++ b/site/util/mysql.str @@ -597,8 +597,10 @@ Column subscription_period;int(11);NO;0; Column max_lapsed;int(11);NO;0; Column session_id;int(11);NO;-1; Column product_code;varchar(80);NO;; +Column tier_id;int(11);YES;NULL; Index PRIMARY;1;[id] Index order_item_order;0;[orderId;id] +Index tier_id;0;[tier_id] Table orders Engine InnoDB Column id;int(11);NO;NULL;auto_increment -- 2.20.1