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

[Dan B <db@cyclonehq.dnsalias.net>]

         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