Here's my solution. It employs a nested loop. The records are arranged in index order, based on the invoice_nu field. This groups all items records for the same invoice together. The code then loops through all the records. When the script sees that the current invoice_nu has changed it "knows" that the last item for the previous invoice has been processed. It then loops through the next batch of records until the invoice_nu field value changes again.
Code:
'Date Created: 07-Jun-2011 09:40:06 AM
'Last Updated: 08-Jun-2011 06:02:59 AM
'Created By : Tom
'Updated By : Tom Cone Jr
'Puzzles and Problems
'
'Beginning Xbasic Programming
'
'Puzzle 5 - Nested loops
'
'An "invoice_items" table and its index file are attached. These came from the AlphaSports sample that ships with Alpha Five.
'
'Write an xbasic script that opens this table, arranges its records in ascending order based
'on the "invoice_number" field, and then computes the total for each invoice by summing
'the "extension" fields for all records that have the same invoice number. For each invoice
'write the invoice number, the number of different items on the invoice (not the item count,
'but the number of "lines" on the invoice), and the invoice total to the trace window. At
'the end, write the grand total of all the invoices to the trace window.
'
'Instructions:
'
'a) you may not use any of the built in summary functions, methods, or operations in Alpha Five. We're doing this one by hand.
'
'b) you may open the table only once
'
'c) you may not use any queries or filters
'
'
' Begin by declaring and initializng variables
dim Items as P ' will point to the invoice_items table
dim vc_curr_inv as C = "" ' will hold the current invoice number
dim vn_inv_tot as N = 0 ' will hold be used to accumulate the invoice total
dim vn_line_count as N = 0 ' will hold the line count for each invoice
dim vn_grand_tot as N = 0 ' will hold the grand total
' Now open the table and set the desired sort sequence
Items = table.open("invoice_items") 'Note: script assumes table will open
idx = Items.index_primary_put("Inv_Num") 'Invoice_nu, ascending, all
'NOTE: this groups all invoice items together
' that have the same invoice_nu field values
Items.fetch_first() ' start at the top of the table
while .not. items.fetch_eof() ' loop through all the records - loop ends when script
' tries to fetch past the end of file
vn_inv_tot = 0 ' clear the accumulator for invoice total
vn_line_count = 0 ' clear the accumulator for the line count
vc_curr_inv = Items.invoice_nu 'get the current invoice number
'now fetch through the sorted list of invoice items records, accumulating
'the invoice total; but stop when we fetch past end of file, or when then
'invoice_nu no longer matches the invoice number being summarized.
while .not. items.fetch_eof() .and. vc_curr_inv = items.invoice_nu
vn_inv_tot = vn_inv_tot + items.extension ' add current extension to invoice total
vn_line_count = vn_line_count + 1 ' increment the line counter
' add current extension value to grand total
vn_grand_tot = vn_grand_tot + items.extension
items.fetch_next()
end while
'on exit from inner loop vn_inv_tot holds the invoice total
'so now we write it to the trace window
trace.writeln("Invoice number " + vc_curr_inv + " has " + str(vn_line_count,3) + " lines" +\
", and totals: " + str(vn_inv_tot,10,2))
' now loop back to top of outer loop, and process next batch of items records
end while
' on exit of the outer loop vn_grand_tot holds the total of all invoices
trace.writeln("")
trace.writeln("Grand total of all invoices: " + ltrim(str(vn_grand_tot,12,2)))
' cleanup on the way out
' remember to close the open table
items.close()
end
Producing this in the trace window:
Code:
Invoice number 000001 has 6 lines, and totals: 949.29
Invoice number 000002 has 2 lines, and totals: 271.61
Invoice number 000003 has 6 lines, and totals: 2629.34
Invoice number 000004 has 4 lines, and totals: 987.17
Invoice number 000005 has 4 lines, and totals: 306.90
Invoice number 000006 has 4 lines, and totals: 184.84
Invoice number 000007 has 4 lines, and totals: 1245.83
Invoice number 000008 has 2 lines, and totals: 240.20
Invoice number 000009 has 1 lines, and totals: 107.10
Invoice number 000010 has 3 lines, and totals: 59.38
Invoice number 000011 has 4 lines, and totals: 194.15
Invoice number 000012 has 2 lines, and totals: 22.33
Invoice number 000013 has 4 lines, and totals: 210.64
Invoice number 000014 has 3 lines, and totals: 270.77
Invoice number 000015 has 3 lines, and totals: 155.35
Invoice number 000016 has 2 lines, and totals: 56.42
Invoice number 000017 has 2 lines, and totals: 100.08
Invoice number 000018 has 5 lines, and totals: 640.06
Invoice number 000019 has 3 lines, and totals: 329.80
Invoice number 000020 has 3 lines, and totals: 367.54
Invoice number 000021 has 5 lines, and totals: 422.36
Invoice number 000022 has 2 lines, and totals: 11.76
Invoice number 000023 has 1 lines, and totals: 45.90
Invoice number 000024 has 3 lines, and totals: 897.76
Invoice number 000025 has 4 lines, and totals: 602.35
Invoice number 000026 has 6 lines, and totals: 910.31
Invoice number 000027 has 7 lines, and totals: 343.71
Invoice number 000028 has 11 lines, and totals: 623.78
Invoice number 000029 has 2 lines, and totals: 400.52
Invoice number 000030 has 4 lines, and totals: 506.50
Invoice number 000031 has 1 lines, and totals: 11.77
Invoice number 000034 has 6 lines, and totals: 950.28
Invoice number 000035 has 6 lines, and totals: 950.28
Invoice number 000036 has 4 lines, and totals: 506.50
Grand total of all invoices: 16512.58
Programming Puzzle 5 - Nested loops
Originally Posted by Tom Cone Jr
Bookmarks