[ic] Many to many price schema

[Stefan Hornburg Racke]

"Renato Enea" <renea@enter.it> writes:

> I have to create a price schema according to my database.
> This is the problem...for any product the customer can insert various prices
> according to item quantity for any users or for a specifical user.
> So i have a many to many relationship table with products and userdb tables
> with the following structure:
> 
> COUNTER - SKU - USER ID - QUANTITY RANGE - PRICE
> 
> So i can have for a single product (example: sku=1) many prices according to
> quantity and users....
> 
> 1 - 1 - 1 - 0,10 - 100   SKU=1 PRICE=100 QUANTITY RANGE = from 0 to 10 price
> specific for USERID = 1
> 2 - 1 -  - 0,10 - 110   SKU=1 PRICE=110 QUANTITY RANGE = from 0 to 10 price
> for any users (except for userid =1)
> 
> 3 - 1 - 1 - 10,20 - 90   SKU=1 PRICE=90 QUANTITY RANGE = from 10 to 20 price
> specific for USERID = 1
> 4 - 1 -  - 10,20 - 100   SKU=1 PRICE=100 QUANTITY RANGE = from 10 to 20
> price for any users (except for userid =1)
> 
> and so on....
> 
> How can i solve this problem ??

I don't know if this is the best solution for your problem,
but you can define your own price calculator like this:

# Pricing 
UserTag calc_price Order code quantity
UserTag calc_price Routine <<EOF
sub {
    my ($code, $quantity);

	if ($Vend::Interpolate::item) {
		$code = $Vend::Interpolate::item->{code};
		$quantity = $Vend::Interpolate::item->{quantity};
	} else {
		($code, $quantity) = @_;
    }

	# sanity check
	if ($code !~ /^\d+$/ || $quantity !~ /^\d+$/) {
		Log ("Wrong input for calc_price: CODE $code QUANTITY $quantity");
        return;
    }

    my $db = Vend::Data::database_exists_ref('price');
	my $keys = $db->query ("select price,min from price where component_idf = $code order by min");
	my $price;

	if (@$keys) {
		# fallback price
		$price = $keys->[0]->[0];
		# check for appropriate price
		for (my $i = 0; $i < @$keys; $i++) {
			if ($quantity > $keys->[$i]->[1]) {
				$price = $keys->[$i]->[0];
			}
		}
	}
	$price * 1.95583;
}
EOF

CommonAdjust [calc_price]

Ciao
        Racke

-- 
Master of Swiss Web 2001: http://www.zweifel.ch/

For projects and other business stuff please refer to COBOLT NetServices
(URL: http://www.cobolt.net; Email: info@cobolt.net; Phone: 0041-1-3884400)