eXtropia: the open web technology company
Technology | Support | Tutorials | Development | About Us | Users | Contact Us
Development resources
 ::   WebWare 2.1 (Perl)
 ::   WebWare 2.0 (Java)
 ::   Cool hacks
cool hack
WebStore hack for Inventory Management  
sent in the following cool and radical hack...

See Update BELOW

Thank you so much for what you are doing to keep code open, information free, and thought flowing. I work for Habitat for Humanity, a non-profit organization that builds simple, decent homes in partnership with people in local communities in the United States and throughout the world. You may have seen Jimmy Carter building homes for us. We have many information-processing needs that are unique to our organization. Off-the-shelf software doesn't always do what we need it to, and custom-built code is way too expensive. After learning a little perl, I've been able to customize your software to meet our needs. Extropia is making it possible for us to improve our efficency, control costs, and bring hope to thousands of families in need of decent, affordable shelter. After reading your statement on the philosophy of open code software and the extropic properties of complex systems, it seems that this use of your product is exactly what you are talking about and hoping for. I'm using web_store to manage our regional warehouse in Minneapolis. Local Habitat for Humanity affiliates from all over the midwest will be able to shop for surplus tools and construction materials on line, and use those tools to create more affordable housing in their communities. Until this point, the warehouse had been managed with an odd collection of spreadsheets, and if a local affiliate wanted to order something from the warehouse it had to get a paper copy of the inventory (which was never accurate), hope that the product was still in stock, and then fax in an order. Materials were sitting in the warehouse unused because the system was so archaic. Now affiliates will be able to order on line, and the inventory will be accurate. Your work is in the true Habitat for Humanity spirit: everyone brings what they have to the table, and by sharing, we empower the community. We're all better off when we're all better off. Well, enough of my gushing. Attached is a hack I came up with to help with inventory management. When an item is purchased, the quantity is removed from inventory. (i.e., the data.file is updated to reflect the new quantity in stock.) The attached text file has notes on and the script for a the adjust_inventory subroutine that adjusts quantities in inventory when a shopper makes a purchase. Any and all comments and fixes are welcome. Thanks again Dan Brill Finance and Technology Manager Habitat for Humanity of Minnesota brill@hfhmn.org Fellow web_store users, I use the flatfile database setup of webstore. I have only limited quantities of some items in stock, and more generally, just want the program to help keep track of what is going out of our inventory. I need a way for web_store to reduce the quantity of an item that is in inventory once an item is purchased, making updates to the data.file . This subroutine, called sub adjust_inventory, subtracts the quantity that has been ordered from the quantity that is in inventory. I should state up front that I've only been using perl for about 6 weeks, and I'm about as new to programming as I am to perl. In other words, this is a newbie script. It works, mind you, but I'm sure it could be improved. I suspect that the whole thing could be done more efficiently using the inplace edit variable $^I and @ARGV. (I played around with this but couldn't make it work, and sort of gave up on that track.) Also, the logic of the whole thing may be inefficient since it relies on comparing each line in the database to each item in the cart, and then each item in the cart to each line of the database. There may be a better way to do this, but not that I can think of. I can't guarantee that this will be fast when you have a large data.file. But, it does work. There are (at least)2 shortcomings that I plan to address in the near future. First, there is nothing to stop a shopper from ordering more than you have on hand, leaving you with a negative quantity in your inventory, and a backorder situation that you may not be able to resolve. I'd like to have it so that if a shoopers orders 8 of an item we have only 6 of, he or she is given a message stating that there are not enough, and that they need to revise their order. I may do this with perl, but it may work better to embed some javascript into the page where they are making their selection. I'll have to think about this. Second, I'd like to create an interface that the store manager can use to add items back into the inventory. I understand that some other hackers have created programs so that store managers can adjust prices, etc. I haven't played with these yet, but I plan to, and this might be the best place to allow the store manager to add to or otherwise edit inventories. Any comments on the script are welcome. Go easy. Remember, I'm new here. ;-) Setting up: _______________________________ Two of your data.file fields are crucial for making this routine work. The first is the product_id, which is a unique identifier for each record in the database. (This might already be require by the script -- I can't quite remember. In any case, if you don't have it, you need to have it now.) The other is the quantity of an item that you have in your inventory. For example, in my web_store_setup.db file I set up the fields as follows: $db{"product_id"} = 0; # product id number $db{"cat1"} = 1; # category 1 $db{"cat2"} = 2; # category 2 $db{"cat3"} = 3; # category 3 $db{"style_A"} = 4; # style a $db{"style_B"} = 5; # style b $db{"descript"} = 6; # long product description $db{"qoh"} = 7; # quantity on hand $db{"price"} = 8; # price Field 0 is the unique product id number, and field 7 is the quantity on hand (qoh) for that product. You will probably also want your customers to see how many of an item that you have on hand so that you don't run into backorder situations. If so, set up your @sc_db_display_fields and @sc_db_index_for_display accordingly. For example: @sc_db_display_fields = ("Description", "Number in Stock", "Price"); @sc_db_index_for_display = ($db{"descript"}, $db{"qoh"}, $db{"price"}, $db{"options"}); In the setup file you will also need to define two new variables so that we know where the product id and the quantity on hand are located in the database. In the setup file, right under $sc_db_index_of_price = $db{"price"}; Insert $sc_db_index_of_product_id = $db{"product_id"}; $sc_db_index_of_qoh = $db{"qoh"}; Locking the data.file __________________________ Since the adjust_inventory subroutine involves writing changes to your database, we don't want other shoppers accessing the database while it is being rewritten. So, any time the web_store script needs access to the database, we need to make it check to see if the adjust_inventory routine is currently rewriting the data.file. In other words, anywhere you'd find: open(DATAVIRTUAL, "$sc_data_file_path") || &file_open_error("$sc_data_file_path", "Read Database",__VIRTUAL__,__LINE__); Is where you need to see if data.file.lock exists before actually opening data.file (data.file.lock is created by the adjust_inventory routine as you'll see below.) Insert the following in web_store_db_lib near line 85 (where the check_db_with_product_id subroutine is checking to make sure that the cart id matches the database id), and again near line 197 (where the submit_query subroutine accesses the database to display items to customers). (By the way, if there is anyplace else that the script accesses the database, this code should be in place. Please let me know if I've missed a place.) Note: $mypath is your path to your web_store directory local ($wait); $wait = time + 30; while (-e "$mypath/data_files/data.file.lock" && time < $wait){ sleep(1); } Calling the subroutine: _______________________ The subroutine is called when the submit order button is pushed, at the very end of the process_order_form routine in web_store_order_lib.pl Insert the following around line 446 # Call Dan's Inventory Routine &adjust_inventory; print "<A href=$sc_server_url/cgi-bin/web_store/web_store.cgi> Click Here to Return <br>to the Storefront</a>"; } # End of process_order_form Note: " } # End of process_order_form " is in the original if you are looking for a landmark. I also stuck in the hyperlink to give shoppers a way back to the storefront with a new, empty cart. The subroutine: _______________________ Insert this subroutine in web_store_order_lib.pl ############################################################ # subroutine: adjust_inventory # by Dan brill # brill@hfhmn.org ############################################################ sub adjust_inventory { # declare variables as local local ($inventory_item); local (@db_record); local ($db_item_id); local ($x); local ($cart_contents); local (@cart_field); local ($quantity_ordered); local ($cart_item_id); local (@newdb); local ($database_quantity_on_hand); local ($new_inventory_item); local ($data_lock_file); local ($wait); # Open the shopper's cart to access the purchased items. open(CART, "$sc_cart_path") || &file_open_error("$sc_cart_path", "Can't Open Cart for Adjust Inventory", __VIRTUAL__,__LINE__); # create a lock for the data.file since we don't want to # reference it if it is currently being updated by another shopper $data_lock_file = "$mypath/data_files/data.file.lock"; $wait = 30; $wait = time + $wait; while (-e $data_lock_file && time < $wait){ sleep(1); } open(DATA_LOCK_VIRTUAL, ">$data_lock_file") || die $!; open(DATAVIRTUAL, "$sc_data_file_path") || &file_open_error("$sc_data_file_path", "Cannot Read Database for Adjust Inventory",__VIRTUAL__,__LINE__); open(NEWDATAVIRTUAL, ">$sc_data_file_path.new") || &file_open_error("$sc_data_file_path", "Cannot Create Newdata file for Adjust Inventory",__VIRTUAL__,__LINE__); # now all of the files we need are safely opened and created. # we are ready to start the real work. # Compare a line of the database to each line of the cart. # If there is no match, then that item was not purchased. # Hence, add the _original_ database record to @newdb TOP: while (<DATAVIRTUAL>) { $inventory_item=$_; $_=""; chomp $inventory_item; @db_record = split(/\|/,$inventory_item); $db_item_id=@db_record[$sc_db_index_of_product_id]; $x=0; seek (CART, 0, 0); while(<CART>){ $cart_contents=$_; $_=""; chomp $cart_contents; @cart_field = split (/\|/,$cart_contents); $quantity_ordered=@cart_field[$sc_db_index_of_product_id+1]; $cart_item_id=@cart_field[1]; if ($db_item_id == $cart_item_id){ $x=1; TOP; # once a match is made we know the item has been purchased # and there is no reason to compare the database item # to the rest of the cart, so start with the next iteration. } } # end of cart while if ($x==0){ # if a match was never made then the item was not purchased, push (@newdb, "$inventory_item \n"); # and $x is still 0. Hence, write the } # original line to @newdb } # end of DATAVIRTUAL while # Now we have recorded what is _not_ in the cart, and have pushed those to @newdb. # Now we need to see what _is_ in the cart. The same logic follows as above, but # instead of comparing each line in the database to each line in the cart, we compare # each line of the cart to each line of the database. If an item is in the cart, # then we need to adjust the quantity on hand and write the new record to @newdb. # read in a line from CART seek (CART, 0,0); TOP2; while(<CART>){ $cart_contents=$_; $_=""; chomp $cart_contents; @cart_field = split (/\|/,$cart_contents); $quantity_ordered=@cart_field[0]; $cart_item_id=@cart_field[$sc_db_index_of_product_id+1]; # Go to the top of the data.file, read a line, compare it to the line from the cart seek (DATAVIRTUAL ,0,0); while (<DATAVIRTUAL>) { $inventory_item=$_; $_=""; chomp $inventory_item; @db_record = split(/\|/,$inventory_item); $database_item_id=@db_record[$sc_db_index_of_product_id]; $database_quantity_on_hand=@db_record[$sc_db_index_of_qoh]; # if the item in the cart matches the item in the database, then subtract the number ordered # from the number on hand in the corresponding database record. if ($cart_item_id == $database_item_id){ $database_quantity_on_hand=$database_quantity_on_hand - $quantity_ordered; # update the quantity on hand information in the database record @db_record[$sc_db_index_of_qoh]=$database_quantity_on_hand; # Put the array back together into one line to go into the database. # In this case I have 9 fields in my database, so I join # the @db_record array elements 0 through 8. # If you have 12 fields in your data.file, for example, # then edit this to @db_record[0..11] $new_inventory_item = join '|', @db_record[0..8]; # add the new line to the array push (@newdb, "$new_inventory_item \n"); TOP2; # Once the match is made there is # no reason to keep comparing, so # start work on the next iteration. } #end of if } # End of DATAVIRTUAL while } # End of CART while # now print @newdb to the NEWDATAVIRTUAL print NEWDATAVIRTUAL @newdb; close (NEWDATAVIRTUAL); close (DATAVIRTUAL); close (CART); rename ($sc_data_file_path, "$sc_data_file_path.bak"); rename ("$sc_data_file_path.new", $sc_data_file_path); close (DATA_LOCK_VIRTUAL); unlink($data_lock_file); } # End of adjust_inventory

Here's an update by .

How's it goin, while implementing WebStore with Inventory Management - Control your webstore inventory with this hack. I noticed that a crucial step was left out and thought ya'll would be interested. I noticed that the qauntity on hand was displayed with the description because the hack never mentioned to modify the $sc_product_display_row or the $sc_product_display_header. Furthermore the qoh has to be put in the @sc_db_index_for_display before the description because the
tag in the $sc_product_display_row will automatically go to the next item in the database (or atleast that was my finding.) Take a look

$db{"product_id"} = 0; 
$db{"product"}    = 1; 
$db{"price"}      = 2; 
$db{"name"}       = 3; 
$db{"image_url"}  = 4; 
$db{"description"}= 5; 
$db{"options"}    = 6; 
$db{"qoh"}        = 7; 

@sc_db_display_fields = ("Image","Quantity On Hand","Description"); 

@sc_db_index_for_display = ($db{"image_url"},   

$sc_product_display_header = qq! 
  <TABLE BORDER = "0"> 
  <TD COLSPAN = "3"><HR></TD> 

$sc_product_display_row = qq~ 

  <TD ALIGN = "center"><INPUT TYPE = "text" 
             NAME = "item-%s" 
             SIZE = "3" MAXLENGTH = "4"></TD> 
  <TD ALIGN = "center">%s</TD> 
  <td align = "center">%s</td> 

Well anyway I think it's a great hack now that it works right and I wanted also to thank you for an excellent perl script and I hope this e-mail helps someone out.