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

Mark Johnson mark.johnson@akopia.com
Wed, 24 Jan 2001 09:53:19 -0500

I'll give this a shot, but frankly I can't tell what you're really
trying to get at. So, let me generalize.

First, to my knowledge (perhaps Mike can verify), there is no means of
storing complex data via session which you can access directly within
IC. If you need to do this, you will most likely be looking at
developing a usertag where you can eval{} a properly stored structure.

Second, within a page, you can dump the results of a query into a
complex data structure which you can (hopefully) use for your intended
purpose. So,"SELECT hcpcs,copay FROM coverage WHERE carrier_idr =
[scratch cust_carrier_idr];" would end up as a array of hashes, exactly
like the cart object:
   { hcpcs => '1st value', copay => 'coordinated 1st value },
   { hcpcs => '2nd value', copay => 'coordinated 2nd value },
    { hcpcs => 'nth value', copay => 'coordinated nth value }

This object would be accessible only through a [perl] tag following a
[query] tag. Something like:

[query st=db hashref=myref sql='your query here']
  my $arr_of_hashes = $Tmp->{myref};

Third, the quickest way to generate a unique array is to make it the
keys of a hash and return the keys. This is, of course, sensitive to any
possible variant such as case:

my %tmp;
@tmp{@arr_with_dups} = ();
my @unq_arr = keys %tmp;

Fourth, key your data into a hash (with complex values, if necessary) so
you have an index at your disposal, with subsequent keyed indices in the
case of complex values. So, presumably you have some scalar or complex
data in %db you'd like to relate to a sku. Then,

my $item_of_interest = $db{q{[item-code]}};

immediately returns the data of interest without doing some inefficient
or loop.

Dan B wrote:
>          I am working on restricting the display of products for a given
> customer to just what is covered under their insurance.  To do so, I've
> created a separate 'coverage' database that can tell me what products are
> covered for a given insurance company.   (It tells me with some simple
> SELECTS of 'coverage' and 'products' for certain matching keys).  Further,
> I would like to present the user with the 'copay' price that is left over
> after calculating the insurance benefit.  (i.e. Blue Cross pays 80% of
> product xyz, so your copay is only $10.00).
> It seems that I would end up with 2 related arrays, or 1 hash, depending on
> how I can store the data:
>          covered_product_sku:    0001,0005,0014
>          covered_product_copay:  5.00,6.40,4.39
> Then, when a user searches for a given category, I just check all the skus
> in that category, and throw out the ones that can't be found anywhere in
> 'covered_product_sku', so the user is left viewing the skus that *are*
> covered.
> I've read the Templates/Database pdfs back and forward, trying to grok this
> problem deeply.  It looks like a lot of [perl] is going to be needed
> though.  If anyone would care to comment on where I could get started as
> far as storing the hashes/arrays and accessing them from IC?  After being
> able to access it, how would I grep it to see if a given value is present
> in the hash?
> The best I have been able to do so far is pseudocode.  (Hopefully some of
> this will end up as interchange tags, but more likely it will all be [calc]
> and [perl]'ed )
>          STEP ONE: Generate a Hash or 2-dimensional array that has all the
> covered product-skus, and their copays:
>          Input:  cust_carrier_idr
>          Output: cust_covered_product_skus[2 dimensional array:
> sku,copay]  (kind of like having [item field sku] [item field copay] )
>          User has just selected insurance type - [set
> cust_carrier_idr]123[/set]
>          my HASH_OR_2D_TABLE $cust_covered_product_sku_and_copay =
>                  "SELECT hcpcs,copay FROM coverage WHERE carrier_idr =
> [scratch cust_carrier_idr];"
>          STEP TWO: Build a list of all the covered categories, by
> generating a unique list of categories from the list of covered products.
>          To build hash of all the categories:
>          my $covered_categories_list;
>          foreach ($cust_covered_product_sku_and_copay) {
>                  $covered_categories_list[n] =
>                          "SELECT category FROM products WHERE product_sku =
> $cust_covered_product_sku_and_copay[n];"
>                  n++;
>          }
>          Make sure each category only listed once.
>          unique($covered_categories_list);  #Is there a 'unique()'
> function?  Or should I restrict duplicates in the foreach{} loop?
>          STEP THREE:  For a given [item-code], check it against the Hash
> (or 2-D array) to see if it is indeed listed as covered.
>          [comment]Any ideas?[/comment]
>          STEP FOUR:  Save the information in the user session database or
> maybe the userdb database so it can be later retrieved without forcing the
> user to input their insurance type again.
>          TABLES:
>          [comment] coverage table:
> create table coverage (
>          coverage_id             INT PRIMARY KEY,
>          carrier_idr             INT,
>          hcpcs                   VARCHAR(12),
>          allowable               NUMERIC(6,2),
>          percent                 INT,
>          copay                   NUMERIC(6,2),
>          mod_date                TIMESTAMP,
>          comment                 VARCHAR(128)
>          );
>          # the products table has a 'hcpcs VARCHAR(12)' field as well,
> which relates it to the coverage database.
>          OTHER IDEAS:
>          [perl tables="coverage"][/perl]
>                  [loop prefix=coveredproducts list=[loop search="..."]]
>                          [coveredproducts-sku] = ...
>                          [coveredproducts-copay] = ...
>                  [/loop]
>          my $this_product_hcpcs_cat;  # regular variable
>          $this_product_hcpcs_cat = "SELECT hcpcs_cat FROM products WHERE
> product_sku = [scratch itemcode];"
> 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,
> Dan Browning, Cyclone Computer Systems, danb@cyclonecomputers.com
> _______________________________________________
> Interchange-users mailing list
> Interchange-users@lists.akopia.com
> http://lists.akopia.com/mailman/listinfo/interchange-users

Mark Johnson
Akopia Professional Services