[ic] Re: SOLVED: Database range-lookup question

[interchange-users@icdevgroup.org (Interchange user)]

On Friday 06 September 2002 06:14 pm, you wrote:

> > > >I want to make a change so that the exact weight of the items can be
> > > > used to make a range lookup in the table, for example:
> > > >
> > > >weight	zone1
> > > >
> > > >60	0.27
> > > >100	0.41
> > > >150	0.57
> > > >200	0.72
> > > >
> > > >A weight of 1 to 60 costs 0.27, a weight of 61 to 100 costs 0.41, and
> > > > so on.
>
> You can save yourself a lot of trouble by making the information in your
> records explicit, and not depend on the contents of other records... like
> that you need to find only one record to get the information that you need.
>
> If I were you I'd make them stand-alone, something like the following:
>
> weight_from	weight_to	zone1
> 1		60		0.27
> 60		100		0.41
> 100		150		0.57
> 150		200		0.72
>
> This would also prevent you from usingn a data value as key for the table.
>
> The lookup query for weight would simply be something like
>
> select zone1
> from weight_table
> where
>
> :weight between weight_from and weight_to
>
> (assuming :weight is a parameter of the SQL query)
>
> or if you want to be more certain about what is included
> and excluded in the weight range,
>
> select zone1
> from weight_table
> where :weight>weight_from
> and :weight <= weight_to
>
> Just my two cents,
>
> Regards,
>
> Marc Brevoort



> You could still use the shipping.asc file and just have a broad weight
> range, say .01 to 999999 and then for that line, use Perl to perform the
> actual database lookup based on the weight into your DB table...
>
> Barry



> Perhaps you can change your table structure to look like this:
>
> min_weight	max_weight	zone1
> 0		60		0.27
> 60		100		0.41
> 100		150		0.57
>
> Then you could do a query like:
>
> [tmp weight]79[/tmp]
>
> [query sql="SELECT zone1 FROM shipping WHERE [scratch weight] >
> min_weight AND [scratch weight] <= max_weight" type=list]
> 	The zone1 value for a weight of [scratch weight] is [sql-param
> zone1]
> [/query]
>
> Or you may be able to get the value with your existing structure by
> doing:
>
> SELECT MAX(weight) AS max_weight , zone1 FROM shipping WHERE [scratch
> weight] <= max_weight
>
> Good luck,
> -Ron





Marc, Barry, Ron:

Thanks for taking the time to help. This was really an SQL question; I didn't 
realize that you could use that syntax for a query. Now you've illustrated 
that, the extra table column seems SO obvious. It's working fine now.

In case anyone's interested, here's the UserTag (excuse the broken lines):


Section from shipping.asc:

DEFAULT	Default Shipping	weight	1	15000	f [calc_shipping weight=@@TOTAL@@ 
country="[value country]"]


UserTag in catalog.cfg (packaging price and weight is hard-coded, which works 
fine for me):

UserTag calc_shipping Order weight country
UserTag calc_shipping Routine <<EOF
sub {
    my ($weight, $country) = @_;

    my $zone = $Tag->query( { sql => "SELECT zone FROM country WHERE code = 
'$country'",
                              wantarray => 1 } );
    $zone = 'zone'.$zone->[0]->[0];

    my $qty_req_packtype_a = 0;
    my $qty_req_packtype_b = 0;
    my $qty_req_packtype_c = 0;
    my $qty_req_packtype_d = 0;

    for (@$Items) {
      my $packtype = $Tag->query( { sql => "SELECT packaging_type FROM 
products WHERE sku = '$_->{sku}'",
                                    wantarray => 1 } );
      $packtype = $packtype->[0]->[0];

      if ($packtype == 'A'){
        $qty_req_packtype_a += $_->{quantity};
      }
      elsif ($packtype == 'B'){
        $qty_req_packtype_b += $_->{quantity};
      }
      elsif ($packtype == 'C'){
        $qty_req_packtype_c += $_->{quantity};
      }
      elsif ($packtype == 'D'){
        $qty_req_packtype_d += $_->{quantity};
      }
    }

    my $packaging_charge = 0;

    if ($qty_req_packtype_a) {
      my $price_a = 0.60;
      my $weight_a = 100;

      my $required_a = $qty_req_packtype_a / 5;
      if($qty_req_packtype_a % 5)
      {
        $required_a++;
      }
      $required_a = int($required_a);
      $packaging_charge += $required_a * $price_a;

      $weight += $required_a * $weight_a;
    }

    if ($qty_req_packtype_b) {
      my $price_b = 0.60;
      my $weight_b = 100;

      my $required_b = $qty_req_packtype_b / 5;
      if($qty_req_packtype_b % 5)
      {
        $required_b++;
      }
      $required_b = int($required_b);
      $packaging_charge += $required_b * $price_b;

      $weight += $required_b * $weight_b;
    }

    if ($qty_req_packtype_c) {
      my $price_c = 0.60;
      my $weight_c = 100;

      my $required_c = $qty_req_packtype_c / 5;
      if($qty_req_packtype_c % 5)
      {
        $required_c++;
      }
      $required_c = int($required_c);
      $packaging_charge += $required_c * $price_c;

      $weight += $required_c * $weight_c;
    }

    if ($qty_req_packtype_d) {
      my $price_d = 0.60;
      my $weight_d = 100;

      my $required_d = $qty_req_packtype_d / 5;
      if($qty_req_packtype_d % 5)
      {
        $required_d++;
      }
      $required_d = int($required_d);
      $packaging_charge += $required_d * $price_d;

      $weight += $required_d * $weight_d;
    }

    my $shipping_charge = $Tag->query( { sql => "SELECT $zone FROM shipping 
WHERE $weight >= min_weight AND $weight <= max_weight",
                                         wantarray => 1 } );
    $shipping_charge = $shipping_charge->[0]->[0];

    return $shipping_charge + $packaging_charge;
    }
EOF