[ic] Re: Custom shipping (for the archives)

[Interchange user]

Sorry for the late response; pretty overloaded with work at the moment. I got 
the routine working, but I may still make some changes. For example, the 
weight of packaging is not calculated. I could include that cost in the 
packaging prices in the packaging table, which is fine, but it's a bummer to 
update.

In catalog.cfg (excuse the messy broken lines):


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 $shipping_charge = $Tag->query( { sql => "SELECT $zone FROM shipping 
WHERE weight = $weight",
                                         wantarray => 1 } );
    $shipping_charge = $shipping_charge->[0]->[0];

    my $qty_packtype_a = 0;
    my $qty_packtype_b = 0;
    my $qty_packtype_c = 0;
    my $qty_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_packtype_a += $_->{quantity};
      }
      elsif ($packtype == 'B'){
        $qty_packtype_b += $_->{quantity};
      }
      elsif ($packtype == 'C'){
        $qty_packtype_c += $_->{quantity};
      }
      elsif ($packtype == 'D'){
        $qty_packtype_d += $_->{quantity};
      }
    }

    my $packaging_charge = 0;

    if ($qty_packtype_a) {
      my $result = $Tag->query( { sql => "SELECT packtype_a FROM packaging 
WHERE quantity = $qty_packtype_a",
                                  wantarray => 1 } );
      $result = $result->[0]->[0];
      $packaging_charge += $result;
    }
    if ($qty_packtype_b) {
      my $result = $Tag->query( { sql => "SELECT packtype_b FROM packaging 
WHERE quantity = $qty_packtype_b",
                                  wantarray => 1 } );
      $result = $result->[0]->[0];
      $packaging_charge += $result;
    }
    if ($qty_packtype_c) {
      my $result = $Tag->query( { sql => "SELECT packtype_c FROM packaging 
WHERE quantity = $qty_packtype_c",
                                  wantarray => 1 } );
      $result = $result->[0]->[0];
      $packaging_charge += $result;
    }
    if ($qty_packtype_d) {
      my $result = $Tag->query( { sql => "SELECT packtype_d FROM packaging 
WHERE quantity = $qty_packtype_d",
                                  wantarray => 1 } );
      $result = $result->[0]->[0];
      $packaging_charge += $result;
    }

    return $shipping_charge + $packaging_charge;
    }
EOF



On Tuesday 09 July 2002 11:38 am, Bill Carr wrote:
>
> In shipping.asc hard code the zone (and perhaps other parameters) in the
> shipping_calc tag untill you get it too work. Maybe you have a quoting
> issue there.

That's what I did; hard coded stuff, and tested sections of the code bit by 
bit until I got it to work. I made some pretty major modifications.



On Friday 12 July 2002 01:26 pm, Kevin Walsh wrote:
> > Can anyone help me out with this? I guess the 4th of July was a bad time
> > to post to the list.
>
> Why?  What happened on the 4th? :-)

I'm in the UK (moving to the US soon though), so I kind of forgot that most 
of y'all were probably busy partying. :-)



> OK, first things first.  All of your $Tag->query() calls are wrong.
> See the Interchange Tags Reference manual:
>
>     $Tag->query({ sql => 'some sql here' });
>
> Do you see the difference?
>
> Secondly, you can't expect $Tag->query() to work from a usertag unless
> you're calling it from [perl], or a similar Perl block, with the table
> pre-opened ("table" parameter).  Try this sort of thing instead:
>
>     my $db = ::database_exists_ref('packaging');
>     my $resultset = $db->query('some sql here');
>
> Lastly, the $resultset will be an arrayref with each element pointing
> to an arrayref (giving you rows and columns).  Your queries seem to
> return one row with one column, so you'll need to use $resultset->[0]->[0]
> to get at your single value result.
>
> You could create yourself a subroutine in your UserTag (or even a new
> UserTag) that expects two args (table name and sql query), checks that
> data was returned by the query and then returns the $resultset->[0]->[0]
> value, as expected by your code.
>
> I hope this helps.

Yeah, I figured all that out myself after re-reading the documentation 
several times, and by much trial & error. As you can tell, my Perl is a 
little rusty. The query() function worked just fine without the db handle 
line though.

Thanks for taking the time to help anyhow.