[ic] Filling perl hashes with [query] and searching the hash for product display restriction

[Jeff Carnahan]

>-----Original Message-----
>Sent: Tuesday, January 23, 2001 4:59 PM
>
>Hopefully some of this makes sense to some of you.  If anyone has an
>umption to comment on:
>         - storing the datatypes (hash, 2D array) I discussed in an
>Interchange-accessible way
>         - filling those datatypes from [query] type data input.
>         - checking those datatypes for given values
>I would appreciate it greatly.  Thank you,
>

(Mike please see the closing for a suggestion/feature request)

You have an interesting problem. Initially I wanted to say that you should
have a single select statement, something along the lines (note this isn't
valid SQL):

select unique category from products WHERE product_sku IN { SELECT hcpcs
FROM coverage WHERE carrier_id = [scratch cust_carrier_id] }

However, this doesn't fetch the proper copay information, it also assumes
you have a database system that can support sub-select statements.

The copay information is what throws me, and looking at your requirements, I
can't see any other way to do it. However, there are a few things you can do
to make your life easier. If your DB supports UNIQUE, use it... =)  MySQL
and others support this so your category selection would simply be:

SELECT UNIQUE category FROM products WHERE product_sku =
$allowed_products[$i] ORDER BY category

(for a sorted unique category list).

Also, I HIGHLY recommend you use a hash to store covered product
information.

%coverage_hash = {
    "0001" => "5.00",
    "0005" => "6.40",
    "0014" => "4.39",
}

It's a product_sku => copay hash.

Also, use another hash to store category identifiers keyed to arrays of
COVERED products they contain.

%coverage_cats = {
    1 => [001, 005, 006],
    8 => [0014, 002],
}

Once you've generated this, don't bother doing it again. If the covered
products/categories/copayments isn't likely to change during the suers
session, generate this once to cut down on page latency. Have something
similar to the following on the top of each page:

[if !defined coverage_hash]
  [generate-coverage-hash]
  [generate-coverage-cats]
[/if]

Where the coverage_hash refers to the hash above (stored in a scratch
variable) and generate-coverage-hash is a routine that will generate the
above hash and store it in a scratch varaiable. generate-coverage-cats will
generate the hash of available categoriee keyed to arrays of the covered
products they contain.

Now, allow the user to select one of the categories listed as a key in
coverage_cats. Use the keys() perl function to get an array of these.

Finally, when you want to display a page of all products in a category do
something similar to the following:

foreach $sku (@{ $coverage_cats{[SELECTED CATEGORY]} }) {

  # --> We have a covered product, SKU.
  # --> Copayment information is: $coverage_hash{$sku}

}

In closing, I highly recommend that you only generate these two hashes once
if you can do so.

Looking at this problem, it seems that a facility such as:

[item-list products="[scratch array]"] is necessary, where the "array"
scratch variable is an array of product SKU's. Mike, I've often created a
listing of products I want to display information about, and my client is
more familiar with the [item-field blah] syntax of referencing data, yet I
can't feed my product sku's into an [item-list]... What do you think?

Thanks!

--
Jeff Carnahan - jcarnahan@networq.com