save the product tier on ordering

diff --git a/schema/bse.sql b/schema/bse.sql
index eddf4f482245bdde43d355eb9b6d5976a108593f..c0e6ea3e3cd30df9017bfa9a67e595b764f0987e 100644 (file)
--- 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 a42872c3b598c5a7c2d2acf11751350a2ba418ec..755da7b618d454b3507f848deaca153ddff8c62a 100644 (file)
--- 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 5a309ccf4360425f16977a1a369ae970b99d6980..eaf968b3d7841e1dc78e612f56056c096ee1d6b6 100644 (file)
--- 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 7b59bd646bea9dbf642c494089bcbc3dae35bbff..d67639e75d16a60c4dc97143b797ceaedde7c7fa 100644 (file)
--- 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 38bd109c401f9e4ecf232224037fb2cc489e0a1d..363a27a6395088d680402faa5e264dd1c1e36e33 100644 (file)
--- a/site/templates/admin/order_detail.tmpl
+++ b/site/templates/admin/order_detail.tmpl
@@ -86,19 +86,23 @@
           <th class="col_unitprice">Unit Price</th>
           <th class="col_extension">Extension</th>
 </tr>
-<:iterator begin items:>
+<:.for item in [order.items] -:>
+  <:.set product = @undef -:>
+  <:.if item.productId > 0 -:>
+    <:.set product = bse.products.getByPkey(item.productId) -:>
+  <:.end if -:>
         <tr> 
-          <td class="col_description"><:ifOr [ifEq [item productId] -1] [ifEq [product id] ""]:><:item title:> (product deleted)<:or:><a href="<:product admin:>"><:product
-            title:></a><:eif:> <:options:></td>
-          <td class="col_units"><:item units:></td>
-          <td class="col_unit_wsale"><:money item wholesalePrice:></td>
-          <td class="col_ext_wsale"><:extension wholesalePrice:></td>
-          <td class="col_unit_gst"><:money item gst:></td>
-          <td class="col_ext_gst"><:extension gst:></td>
-          <td class="col_unitprice"><:money item price:></td>
-          <td class="col_extension"><:extension price:></td>
+          <td class="col_description"><:.if product :><a href="<:= product.admin :>"><:= product.title :></a><:.else:><:= item.title :> (product deleted)<:.end if:> <:= item.nice_options:>
+         <:.if item.tier_id:><br><span class="price_tier"><:= item.tier.description :></span><:.end if:></td>
+          <td class="col_units"><:= item.units:></td>
+          <td class="col_unit_wsale"><:= bse.number("money", item.wholesalePrice) :></td>
+          <td class="col_ext_wsale"><:= bse.number("money", item.extended("wholesalePrice")) :></td>
+          <td class="col_unit_gst"><:= bse.number("money", item.gst):></td>
+          <td class="col_ext_gst"><:= bse.number("money", item.extended("gst")):></td>
+          <td class="col_unitprice"><:= bse.number("money", item.price):></td>
+          <td class="col_extension"><:= bse.number("money", item.extended("price")) :></td>
 </tr>
-<:iterator end items:>
+<:.end for -:>
 <tr>
   <td colspan="7" class="col_label_right">Total Price of goods:</td>
   <td  class="col_extension"><:= bse.number("money", order.total_cost) -:></td>

diff --git a/site/util/mysql.str b/site/util/mysql.str
index f122d056aa7a0e740afe7b3400a29872e3f8196c..457e3ac88b89b7ce37b9a6a9ff1e3bdca388c87f 100644 (file)
--- 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