[ic] RE: Options Problem
joe at cyberseals.net
joe at cyberseals.net
Thu Sep 2 06:43:20 EDT 2004
Well, I guess nobody could think of a solution...
But I figured it out, so I'll put this out there for anybody else who
wants to use the standard options support without having to key the
options on the product SKU...
A little background: the catalog I am working on gets its products from an
existing, external database that is updated through existing software
systems. I treat this database as a black box, and Interchange uses a
separate SQL database for its own tables in this installation (though
"ProductFiles" is set to the external database, so the "products" table
isn't actually used). The products in question are classified into types
in the external database by a "type_id" field, and the requirement here is
that options should be applied to the products based on this type
designation.
The existing IC approach of mapping options to products by their SKU would
require that the options table contain a number of records for each
product, and further that every time a new product is added, more records
would need to be added to the options table. The external products
database I am working with here has tens of thousands of records, and is
updated around the clock every day, not to mention that it is updated by
existing systems that I do not control and cannot modify, so this approach
clearly isn't practical for this application.
The only approach that does make sense is for the options to be defined in
such a way that they are mapped to products based on the product "type_id"
field, rather than the product SKU. That way, you only have to define the
options once for all products of a given type, and no extraneous DB
updates need occur to enable the options for new products as they are
added.
So, based on an email I found from Mike Heins explaining how to add new
options modules, I came up with a solution...
First, we need basically the exact same thing as the standard IC "options"
table, but instead of a "SKU" field, we need a "type_id" field. You could
just reuse the same table (or duplicate it exactly), using the "SKU" field
as "type_id", but I decided to make it separate and keep the column name
from being misleading. So I created another table by duplicating
dbconf/mysql/options.mysql and products/options.txt, added a name prefix
(let's say it's "type_"), and renamed the "SKU" field to "type_id". I also
changed the type of the "type_id" field to "int", since type IDs are
represented as integers in the external database. And so I end up with
dbconf/mysql/type_options.mysql and products/type_options.txt. I'll
include these files at the end of this email.
Now that I have a table in which to define options based on type ID, I
need to tie it into Interchange. So I added an Options module, based on
the standard IC Simple options module, in the lib/Vend/Options directory
of the interchange installation. This is to change the logic by which the
options are gathered from the database for a given product (as long as
that product is flagged to use options of that type, but more on that
later). I copied the lib/Vend/Options/Simple.pm module and modified the
query that gathers the options from the database to use the "type_id"
field in the type_options table we just created, rather than the "SKU"
field it had previously. I named the module TypeSimple.pm to reflect its
intent. The changes were actually very simple - aside from changing the
package name, I just assign different values to a couple of variables
(specifically, $fsel and $rsel in price_options and display_options):
my $fsel = $map->{sku} || 'sku';
my $rsel = $db->quote($sku, $fsel);
becomes:
my $fsel = 'type_id';
my $rsel = $db->quote($type_id, $fsel);
And of course, I add a simple query just before this to get the value of
the "type_id" field from the product record in question, and assign it to
$type_id. These two variables are subsequently used to form the WHERE
clause of the SELECT statement that gathers the options for the current
product, as in "WHERE $fsel = $rsel". So this effectively enables IC to
gather the options from the database based on the "type_id" field of a
product record, rather than the product's SKU. Well, almost. I'll include
a full diff of this Options module at the end of this email for anyone
that wants to use it...
Almost there. Now to add a few lines to the catalog.cfg to enable the new
options module to be used. Pretty straightforward:
# Enable type-based options support
OptionsEnable option_type
Options TypeSimple enable 1
Options TypeSimple table type_options
Options TypeSimple sort o_sort,o_group
The "OptionsEnable" line is, of course, required to enable the newer-style
options support (more on that in a minute). The following three lines
enable the new Options module, set the table that it will use (the
"type_options" table we created), and set the sorting order for options of
that type, respectively.
So the only issue remaining now is how to associate products in the
external database with this new options type. In the foundation catalog, a
product must have a value in the "option_type" field in order to use the
newer-style options. The value in this field references the Options module
to use for this product (so here we clearly want the value to be
"TypeSimple" for all of the products in the external database).
Unfortunately, I haven't figured out an elegant solution to this problem,
so what I ended up doing is *temporarily* (hehe) breaking the rules and
adding an "option_type" field to the external products database table.
Since I always want it to have the same value for all products in that
database, I just set the default value for the field to "TypeSimple" and
voila!
Now, options defined in the new type_options table by type ID are showing
up and functioning properly, and there was only one real kludge (the
"option_type" field) used to make it work. And if I figure out a better
solution for this hack, I'll certainly post it...
Thanks,
Joe Zimmerlin
> I am using IC 5.2.0, and I am looking for the functionality provided by
> options, but since inventory is driven by an external (non-IC) database on
> this catalog, and that data is updated separately by existing systems, I
> cannot rely on having an entry in the IC options table for every item.
>
> I was thinking that maybe order groups would be the way to go, but the
> foundation cart always seems to treat grouped items as separate, rather
> than grouping them as I would expect. For example, with a master and a
> sub-item grouped in the cart, you can still delete either individually.
> Any ideas?
>
> Thanks,
> Joe
TypeSimple.pm
=============
# Vend::Options::Simple - Interchange Simple product options
#
# $Id: Simple.pm,v 1.4 2004/01/08 22:36:31 mheins Exp $
#
# Copyright (C) 2002-2003 Mike Heins <mikeh at perusion.net>
# Copyright (C) 2002-2003 Interchange Development Group
<interchange at icdevgroup.org>
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public
# License along with this program; if not, write to the Free
# Software Foundation, Inc., 59 Temple Place, Suite 330, Boston,
# MA 02111-1307 USA.
#
package Vend::Options::TypeSimple;
$VERSION = substr(q$Revision: 1.4 $, 10);
=head1 NAME
Vend::Options::Simple - Interchange Simple Options Support
=head1 SYNOPSIS
[item-options]
or
[price code=SKU]
=head1 PREREQUISITES
Vend::Options
=head1 DESCRIPTION
The Vend::Options::Simple module implements simple product options for
Interchange. It is compatible with Interchange 4.8.x simple options.
If the Interchange Variable MV_OPTION_TABLE is not set, it defaults
to "options", which combines options for Simple, Matrix, and
Modular into that one table. This goes along with foundation and
construct demos up until Interchange 4.9.8.
The "options" table remains the default for simple options.
=head1 AUTHORS
Mike Heins <mikeh at perusion.net>
=head1 CREDITS
Jon Jensen <jon at swelter.net>
=cut
use Vend::Util;
use Vend::Data;
use Vend::Interpolate;
use Vend::Options;
use strict;
use vars qw/%Default/;
%Default = (
option_template =>
'{LABEL} {PRICE?}({NEGATIVE?}subtract{/NEGATIVE?}{NEGATIVE:}add{/NEGATIVE:} {ABSOLUTE}) {/PRICE?}'
);
my $Admin_page;
sub price_options {
my ($item, $table, $final, $loc) = @_;
$loc ||= $Vend::Cfg->{Options_repository}{TypeSimple} || {};
my $map = $loc->{map} || {};
my $db = database_exists_ref($table || $loc->{table} || 'templ_options');
if(! $db) {
logOnce('Non-existent price option table %s', $table);
return;
}
my $tname = $db->name();
my $sku = $item->{code};
# JAZ: Template type ID options selection
my $type_id = $Tag->data('templates', 'type_id', $sku);
if (!$type_id) {
logOnce('No type ID for SKU: %s', $sku);
return;
}
#::logDebug("Simple module price_options found enabled record");
my $fsel = 'type_id';
my $rsel = $db->quote($type_id, $fsel);
my @rf;
for(qw/o_group price/) {
push @rf, ($map->{$_} || $_);
}
my $q = "SELECT " . join (",", @rf) . " FROM $tname where $fsel = $rsel
and $rf[1] <> ''";
#::logDebug("Simple module price_options query=$q");
my $ary = $db->query($q);
return if ! $ary->[0];
my $ref;
my $price = 0;
my $f;
foreach $ref (@$ary) {
#::logDebug("checking option " . uneval_it($ref));
next unless defined $item->{$ref->[0]};
next unless length($ref->[1]);
$ref->[1] =~ s/^\s+//;
$ref->[1] =~ s/\s+$//;
$ref->[1] =~ s/==/=:/g;
my %info = split /\s*[=,]\s*/, $ref->[1];
if(defined $info{ $item->{$ref->[0]} } ) {
my $atom = $info{ $item->{$ref->[0]} };
if($atom =~ s/^://) {
$f = $atom;
next;
}
elsif ($atom =~ s/\%$//) {
$f = $final if ! defined $f;
$f += ($atom * $final / 100);
}
else {
$price += $atom;
}
}
}
#::logDebug("price_options returning price=$price f=$f");
return ($price, $f);
}
sub display_options {
my ($item, $opt, $loc) = @_;
#::logDebug("Simple options, item=" . ::uneval($item) . "\nopt=" .
::uneval($opt));
#::logDebug("Simple options by module, old");
$loc ||= $Vend::Cfg->{Options_repository}{TypeSimple} || {};
my $map = $loc->{map} || {};
my $sku = $item->{code};
my $db;
my $tab;
if(not $db = $opt->{options_db}) {
$tab = $opt->{table} ||= $loc->{table}
||= $::Variable->{MV_OPTION_TABLE}
||= 'templ_options';
$db = database_exists_ref($tab)
or do {
logOnce(
"TypeSimple options: unable to find table %s for item %s",
$tab,
$sku,
);
return undef;
};
}
my $tname = $db->name();
my @rf;
my @out;
my $out;
use constant CODE => 0;
use constant GROUP => 1;
use constant VALUE => 2;
use constant LABEL => 3;
use constant WIDGET => 4;
use constant PRICE => 5;
use constant HEIGHT => 6;
use constant WIDTH => 7;
for(qw/code o_group o_value o_label o_widget price o_height o_width/) {
push @rf, ($map->{$_} || $_);
}
# JAZ: Template type ID options selection
my $type_id = $Tag->data('templates', 'type_id', $sku);
if (!$type_id) {
logOnce('No type ID for SKU: %s', $sku);
return;
}
my $fsel = 'type_id';
my $rsel = $db->quote($type_id, $fsel);
my $q = "SELECT " . join (",", @rf) . " FROM $tname where $fsel = $rsel";
if(my $rsort = find_sort($opt, $db, $loc)) {
$q .= ' ' . $rsort;
}
#::logDebug("tag_options simple query: $q");
my $ary = $db->query($q)
or return;
#::logDebug("tag_options simple ary: " . ::uneval($ary));
#::logDebug("tag_options item=" . ::uneval($item));
my $ishash = defined $item->{mv_ip} ? 1 : 0;
my $ref;
$opt->{option_template} ||= $loc->{option_template};
foreach $ref (@$ary) {
# skip unless o_value
next unless $ref->[VALUE];
#::logDebug("tag_options attribute=" . GROUP);
if ($opt->{label}) {
$ref->[LABEL] = "<B>$ref->[LABEL]</b>" if $opt->{bold};
push @out, $ref->[LABEL];
}
my $precursor = $opt->{report}
? "$ref->[GROUP]$opt->{separator}"
: qq{<input type=hidden name="mv_item_option" value="$ref->[GROUP]">};
my $passed = $ref->[VALUE];
if($opt->{blank_label}) {
$passed = "=$opt->{blank_label}, $passed";
}
push @out, $precursor . Vend::Interpolate::tag_accessories(
$sku,
'',
{
attribute => $ref->[GROUP],
default => undef,
extra => $opt->{extra},
item => $item,
js => $opt->{js},
name => $ishash ? undef : "mv_order_$ref->[GROUP]",
option_template => $opt->{option_template},
passed => $passed,
price => $opt->{price},
price_data => $ref->[PRICE],
height => $opt->{height} || $ref->[HEIGHT],
width => $opt->{width} || $ref->[WIDTH],
type => $opt->{type} || $ref->[WIDGET] || 'select',
},
$item || undef,
);
}
if($opt->{td}) {
for(@out) {
$out .= "<td>$_</td>";
}
}
else {
$opt->{joiner} = '<BR>' if ! $opt->{joiner};
$out .= join $opt->{joiner}, @out;
}
#::logDebug("display_options out size=" . length($out));
return $out;
}
sub admin_page {
my $item = shift;
my $opt = shift;
my $page = $Tag->file('include/Options/Simple') || $Admin_page;
Vend::Util::parse_locale(\$page);
return interpolate_html($page);
}
$Admin_page = <<'EoAdminPage';
[update values]
[if cgi ui_clone_options]
[and cgi ui_clone_id]
[perl interpolate=1 tables="[cgi mv_data_table]"]
my $db = $Db{[cgi mv_data_table]}
or return;
my ($k,$v);
$db->clone_row($CGI->{ui_clone_id}, $CGI->{sku});
$db->clone_set('sku', $CGI->{ui_clone_id}, $CGI->{sku});
return;
[/perl]
[/if]
[if cgi sku]
[tag flag write]options[/tag]
[perl tables="options __UI_ITEM_TABLES__"]
my $otab = 'options';
my $odb = $Db{$otab};
foreach(sort keys %{$CGI}) {
next unless /^opt_group_(.*)/;
my $key = $1;
my $name = $CGI->{"opt_group_$key"};
my $value = $CGI->{"opt_value_$key"};
my $label = $CGI->{"opt_label_$key"};
next unless $name && $value;
unless($key) { $key = $CGI->{sku}."-$name"; }
my @value = split("\r\n",$value);
my %seen = ();
my $hasdefault = 0;
my($left,$right);
map {
my $default = 0;
s/[,\r\n]//g;
if(s/\*//g) { $default = 1; $hasdefault = 1; }
if($v) {
if(/=/) {
($left,$right) = split('=',$_);
} else {
$right = $_;
$left = substr($right,0,3);
}
while($seen{$left}++) { $left++; }
$_ = join('=',$left,$right);
if($default) { $_ .= "*"; }
}
} @value;
my $value = join(",\n", at value);
$key =~ s/_/-/g; # javascript won't handle form names with '-'
$odb->set_field($key,'sku',$CGI->{sku});
$odb->set_field($key,'o_group',$name);
$odb->set_field($key,'o_value',$value);
$odb->set_field($key,'o_widget','select');
$odb->set_field($key,'o_label',$label);
}
return '';
[/perl]
[/if]
<FORM ACTION="[area @@MV_PAGE@@]" METHOD="post">
[if scratch ui_failure]
<P>
<BLOCKQUOTE>
<FONT COLOR="__CONTRAST__">[scratch ui_failure][set ui_failure][/set]</FONT>
</BLOCKQUOTE>
<P>
[/if]
[if scratch ui_message]
<P>
<BLOCKQUOTE>
<FONT COLOR="__CONTRAST__">[scratch ui_message][set ui_message][/set]</FONT>
</BLOCKQUOTE>
<P>
[/if]
<INPUT TYPE=hidden NAME=sku VALUE="[cgi item_id]">
<INPUT TYPE=hidden NAME=ui_page_title VALUE="[cgi ui_page_title]">
<INPUT TYPE=hidden NAME=ui_page_title VALUE="[cgi ui_page_banner]">
<INPUT TYPE=hidden NAME=ui_return_to VALUE="@@MV_PAGE@@">
<INPUT TYPE=hidden NAME=mv_action VALUE=back>
<TABLE BORDER=0><TR><TD VALIGN=TOP>
[query list=1 sql="select * from options where sku='[filter op=sql
interpolate=1][cgi item_id][/filter]' and o_group is not null"]
[list]
[if-sql-data options o_group]
[calc] $Scratch->{mod_code} = q{[sql-code]}; $Scratch->{mod_code} =~
s/-/_/g; return;[/calc]
<TABLE BORDER=0 CELLSPACING=0 CELLPADDING=3 BGCOLOR="[sql-alternate
2]__UI_T_ROW_EVEN__[else]__UI_T_ROW_ODD__[/else][/sql-alternate]">
<TR><TD VALIGN=CENTER>Name: <INPUT TYPE=text SIZE=20
NAME="opt_group_[scratch mod_code]" VALUE="[filter entities][sql-param
o_group][/filter]">
<A HREF="[area href='@@MV_PAGE@@'
form='deleterecords=1
ui_delete_id=[sql-code]
item_id=[cgi item_id]
mv_data_table=options
mv_click=db_maintenance
mv_action=back
mv_nextpage=@@MV_PAGE@@
'
]"><IMG SRC="delete.gif" ALT="[L]Delete[/L]" ALIGN=CENTER
BORDER=0></A>
<br>[L]Label[/L]: <INPUT TYPE=text SIZE=20 NAME="opt_label_[scratch
mod_code]" VALUE="[filter entities][sql-param o_label][/filter]">
<INPUT TYPE=hidden NAME="reset_[scratch mod_code]" VALUE="[filter
entities][sql-param o_label][/filter]">
<script><!--
document.write('<br><INPUT TYPE=checkbox [sql-calc]q{[sql-param o_label]}
eq q{[sql-param o_group]} ? 'CHECKED' : undef;[/sql-calc]\n' +
' onClick="if (this.checked) { this.form.opt_label_[scratch
mod_code].value = this.form.opt_group_[scratch mod_code].value; } else {
this.form.opt_label_[scratch mod_code].value = this.form.reset_[scratch
mod_code].value; }">\n' +
'<font size=2>[L]Set label to name[/L]</font>');
// -->
</script>
</TD></TR>
[tmp o_value][perl]
my @vals = split(',',q{[sql-param o_value]});
map { s/[\r\n]//g; } @vals;
return join("\n", at vals);
[/perl][/tmp]
<TR><TD>
<TEXTAREA ROWS=5 COLS=30 NAME="opt_value_[scratch mod_code]">[scratch
o_value]</TEXTAREA><br>
[page href="admin/flex_editor"
form="
mv_data_table=options
item_id=[sql-code]
ui_return_to=admin/item_option
ui_return_to=item_id=[cgi item_id]
ui_data_fields=code o_widget o_width o_height
"]Widget type edit</A>
</TD></TR>
</TABLE>
[/if-sql-data]
[/list]
[/query]
<BR><BR><BR>
[button text="[L]Commit Changes[/L]"]
</TD><TD><PRE> </PRE></TD><TD VALIGN=TOP>
<B>[L]Create a new option[/L]:</B><BR>
[L]Name[/L]: <INPUT TYPE=text SIZE=20 NAME="opt_group_" VALUE="">
<br>[L]Label[/L]: <INPUT TYPE=text SIZE=20 NAME="opt_label_">
<script><!--
document.write('<br><INPUT TYPE=checkbox\n' +
' onClick="if (this.checked) { this.form.opt_label_.value =
this.form.opt_group_.value; } else { this.form.opt_label_.value = \'\';
}">\n' +
'<font size=2>[L]Set label to name[/L]</font>');
// -->
</script>
<BR>
<TEXTAREA ROWS=5 COLS=30 NAME="opt_value_"></TEXTAREA>
<BR>
[button text="[L]Create option[/L]"]
<BR><BR>
<HR>
<BR><BR><B>[L]Clone an existing option set[/L]:</B><BR>
[query
list=1
prefix=clone
sql="select DISTINCT sku from [cgi mv_data_table]"
more=1]
<SELECT NAME=ui_clone_id>
<OPTION VALUE=""> --
[list]
[if-clone-data options o_enable]
<OPTION VALUE="[clone-code]">[clone-filter
20][clone-description][/clone-filter]
[/if-clone-data]
[/list]
</SELECT>[more-list]<BR>[more]<BR>[/more-list][/query] [button
text="[L]Clone options[/L]"]<BR>
</FORM>
</TD></TR></TABLE>
EoAdminPage
1;
dbconf/mysql/type_options.mysql
===============================
Database type_options type_options.txt __SQLDSN__
ifdef SQLUSER
Database type_options USER __SQLUSER__
endif
ifdef SQLPASS
Database type_options PASS __SQLPASS__
endif
Database type_options DEFAULT_TYPE text
Database type_options AUTO_NUMBER 100001
Database type_options NO_SEARCH 1
Database type_options ChopBlanks 1
Database type_options COLUMN_DEF "code=varchar(64) primary key NOT NULL"
Database type_options COLUMN_DEF "type_id=int NOT NULL"
Database type_options COLUMN_DEF "o_group=varchar(64) NOT NULL DEFAULT ''"
Database type_options COLUMN_DEF "o_sort=varchar(16) NOT NULL DEFAULT ''"
Database type_options COLUMN_DEF "o_default=varchar(128)"
Database type_options COLUMN_DEF "o_height=integer"
Database type_options COLUMN_DEF "o_width=integer"
Database type_options COLUMN_DEF "price=varchar(250)"
Database type_options COLUMN_DEF "wholesale=varchar(250)"
Database type_options PREFER_NULL o_height o_width
Database type_options NUMERIC type_id o_height o_width
Database type_options INDEX type_id o_group o_sort
products/type_options.txt
=========================
code type_id o_group o_sort o_default o_label o_value o_widget o_height o_width description price wholesale display_type
More information about the interchange-users
mailing list