###########################################################################
# SIMPLE REPORT SCRIPT
# PROGRAMMED BY: EMILIO VAZQUEZ @ 02-05-2010
# WEB : http://www.emiliovazquez.com
#
############################################################################
# we will use this variable to store all data that we obtain from
# our query results (var_orders)
DEFINE var_orders RECORD LIKE orders.*
DEFINE var_orderdate1 DATE,
var_orderdate2 DATE
MAIN
DEFER INTERRUPT
DEFER QUIT
OPTIONS
MESSAGE LINE 23,
ERROR LINE LAST,
PROMPT LINE LAST,
HELP KEY CONTROL-W,
HELP FILE "interhlp.ex",
FORM LINE 3
INITIALIZE var_orders.* TO NULL
#stores date for orders to be selected
PROMPT " PLEASE ENTER INITIAL DATE FOR ORDERS: " FOR var_orderdate1
PROMPT " PLEASE ENTER FINAL DATE FOR ORDERS: " FOR var_orderdate2
CALL get_data()
END MAIN
#This function will contain the query to select our data
FUNCTION get_data()
DECLARE var_report CURSOR FOR
SELECT *
FROM orders
WHERE order_date BETWEEN var_orderdate1 AND var_orderdate2
START REPORT rept
#results from query above will be stored
#in "var_orders",we are using an '*' since
#we want multiple columns stored in a single variable
#These rows will be taken from the 'var_report' cursor
#declared above (DECLARE var_report CURSOR FOR )
FOREACH var_report INTO var_orders.*
OUTPUT TO REPORT rept(var_orders.*)
END FOREACH
FINISH REPORT rept
RETURN
END FUNCTION
#This function will receive the data
#from get_data()
REPORT rept(r_orders)
#variables para creacion de reportes
DEFINE r_orders RECORD LIKE orders.*
OUTPUT
LEFT MARGIN 0
TOP MARGIN 0
BOTTOM MARGIN 0
PAGE LENGTH 60
REPORT TO "report.txt"
# A text file will be generated into the directory
# where the program was executed
FORMAT
#page header will be printed once in first page
PAGE HEADER
#Page size is set to 80 columns and paper orientation to portrait
LET col = (80 - LENGTH(xt))/2 + 1
SKIP 2 LINES
PRINT COLUMN 01, today,
COLUMN col, xt CLIPPED,
COLUMN 77, pageno using "####"
SKIP 3 LINES
PRINT COLUMN 01, "SAMPLE REPORT --------------------------"
SKIP 2 LINES
PRINT COLUMN 01, "ORDER NUM.",
COLUMN 10, "DATE",
COLUMN 25, "CUST.NAME",
COLUMN 35, "ITEM NUM.",
COLUMN 40, "DESCRIPTION",
COLUMN 60, "TOTAL"
ON EVERY ROW # Each row will be printed below
PRINT COLUMN 01, r_orders.order_id,
COLUMN 10, r_orders.order_date,
COLUMN 25, r_orders.customer_name CLIPPED,# clipped is used to cut extra spaces in cells
COLUMN 35, r_orders.item_number CLIPPED,
COLUMN 40, r_orders.description CLIPPED,
COLUMN 60, r_orders.total USING "<<<<<.&&"
ON LAST ROW
#total number of rows will be printed here using count(*)
PRINT COLUMN 01, "total: ", count(*) USING "<<<"
END REPORT
0 Comments