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"},   
                            $db{"qoh"}, 
                    $db{"description"}, 
                    $db{"options"}); 

$sc_product_display_header = qq! 
  <TABLE BORDER = "0"> 
  <TR> 
  <TH>Quantity</TH> 
  <TH>%s</TH> 
  <TH>%s</TH> 
  <TH>%s</TH> 
  </TR> 
  <TR> 
  <TD COLSPAN = "3"><HR></TD> 
  </TR>!; 

$sc_product_display_row = qq~ 

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