/* This is a demo script showing how to perform a relational update under direct arexx control
	(The same could also be done from the Update window without programming, but this script can be
	 used as a skelleton if more advanced updates are needed)

	The program will look for two files TWIST:upd_customer and TWIST:upd_sales if not alreade
	present the files will be created with a few dummy records

	The customer file holds information on customers. 
	The TotalSales should tell the total monetary amount	purchased for so far.

	The sales file holds information on sale. 
	The Cnr field is used to identify the customer to whom the item was sold.
	The TotalFlag is TRUE if the sales amount has already been totaled in the customer file
	
	The program goes trough all the sales records not already processed.
	For each sales record a nested SELECTALL in the customer file is performed to find the
	customer record. The Pprice multiplied by Units is added to the customer.TotalSales field.
	Finally the TotalFlag is set to TRUE to ensure that the sales record it not used again the
	next time the arexx script is run.
	
	
	Try running the arexx script
	Then open the two created DB files and add a few new sales records by hand and run the arexx program again.
	The manually added records will now also be totaled in the customer file.
	
	Beware that the program does not perform any safety check of onfound customers
	Try yourself to refine it by adding an error check after line 129 (the SELECTALL in the customer file)
	

	Hasse Wehner, Mermaid Group
*/

options results
address twist
custfilename = "TWIST:upd_customer"

open custfilename
exit




salesfilename = "TWIST:upd_sales"

if exists(custfilename || ".DB") == 1 then OPEN custfilename
else
do
	CREATEDB custfilename
	CREATEFIELD "Cnr" INTEGER
	CREATEFIELD "Name"
	CREATEFIELD "Address" 50
	CREATEFIELD "TotalSales" NUMBER ZERO
	ENDCREATEDB
	open custfilename
	if RC!=0 then
		do
		say "unable to create " custfilename
		exit
	end

	customer.Cnr = "1"
	customer.Name = "Jones, Bill"
	customer.Address = "5th avenue 123, N.Y, USA"
	INSERT stem customer

	customer.Cnr = "2"
	customer.Name = "Smith, David"
	customer.Address = "The silent road 1, Dublin, Ireland"
	INSERT stem customer

	customer.Cnr = "3"
	customer.Name = "Josephson, Gary"
	customer.Address = "Ryesgade 100, Copenhagen, Denmark"
	INSERT stem customer

end

if exists(salesfilename || .DB) == 1 then open salesfilename
else
do
	CREATEDB salesfilename
	CREATEFIELD "Cnr" INTEGER
	CREATEFIELD "Pnr" INTEGER
	CREATEFIELD "Ptext" 
	CREATEFIELD "Pprice" NUMBER
	CREATEFIELD "Units" INTEGER
	CREATEFIELD "TotalFlag" INTEGER ZERO
	ENDCREATEDB
	open salesfilename
	if RC!=0 then
	do
		say "unable to create " salesfilename
		exit
	end
	
	sales.Cnr = "1"
	sales.Pnr = "101"
	sales.Ptext = "Twist2"
	sales.Pprice = "299"
	sales.Units = "1"
	INSERT stem sales
		
	sales.Cnr = "2"
	sales.Pnr = "102"
	sales.Ptext = "OS3.1 upgrade"
	sales.Pprice = "78"
	sales.Units = "1"
	INSERT stem sales
		
	sales.Cnr = "1"
	sales.Pnr = "103"
	sales.Ptext = "Floppies"
	sales.Pprice = "2.50"
	sales.Units = "20"
	INSERT stem sales
		
	sales.Cnr = "3"
	sales.Pnr = "102"
	sales.Ptext = "OS3.1 upgrade"
	sales.Pprice = "78"
	sales.Units = "1"
	INSERT stem sales
		
	sales.Cnr = "2"
	sales.Pnr = "101"
	sales.Ptext = "Twist2"
	sales.Pprice = "299"
	sales.Units = "1"
	INSERT stem sales
		
end

exit
SELECTALL WHERE "!TotalFlag" STEM sales /* only select those sales records who haven't yet been totalled */
do while RC==0
	/* Go trough all records in the sales file */
	OPEN custfilename	/* make the customer file the current file */
	SELECTALL WHERE '"' || "Cnr == " || sales.Cnr || '"' STEM customer
	customer.TotalSales = customer.TotalSales + sales.Units * sales.Pprice
	CHANGE "TotalSales" as customer.TotalSales
	if RC!=0 then break

	OPEN salesfilename		/* make the sales file the current file */
	CHANGE "TotalFlag" as "1"  /* now the record will not be included the next time update.rexx is run */
	SELECTNEXT
end

CLOSE salesfilename
CLOSE custfilename