COMMENT
*** Non-Unicode Edition ***
This script performs all the actions that you performed manually in the "Getting Started with ACL Analytics" tutorial.
END

COMMENT Allows overwriting of tables without a user confirmation.
SET SAFETY OFF

COMMENT Imports the three Excel worksheets.

IMPORT EXCEL TO Trans1_May_s "C:\Users\user_account_name\Documents\ACL Data\Sample Data Files\Trans1_May_s.fil" FROM "Trans_May.xls" TABLE "Trans1_May$" KEEPTITLE  FIELD "CARDNUM" C WID 19 AS "" FIELD "CODES" N WID 4 DEC 0 AS "" FIELD "DATE" D WID 19 PIC "YYYY-MM-DD hh:mm:ss" AS "" FIELD "CUSTNO" C WID 6 AS "" FIELD "DESCRIPTION" C WID 95 AS "" FIELD "AMOUNT" C WID 9 AS ""

IMPORT EXCEL TO Trans2_May_s "C:\Users\user_account_name\Documents\ACL Data\Sample Data Files\Trans2_May_s.fil" FROM "Trans_May.xls" TABLE "Trans2_May$" KEEPTITLE  FIELD "CARDNUM" C WID 19 AS "" FIELD "CODES" N WID 4 DEC 0 AS "" FIELD "DATE" D WID 19 PIC "YYYY-MM-DD hh:mm:ss" AS "" FIELD "CUSTNO" C WID 6 AS "" FIELD "DESCRIPTION" C WID 155 AS "" FIELD "AMOUNT" C WID 9 AS ""

IMPORT EXCEL TO Trans_Apr_s "C:\Users\user_account_name\Documents\ACL Data\Sample Data Files\Trans_Apr_s.fil" FROM "Trans_April.xls" TABLE "Trans_Apr$" KEEPTITLE FIELD "CARDNUM" N WID 16 DEC 0 AS "" FIELD "AMOUNT" N WID 6 DEC 2 AS "" FIELD "DATE" D WID 10 PIC "YYYY-MM-DD" AS "" FIELD "CODES" N WID 4 DEC 0 AS "" FIELD "CUSTNO" C WID 6 AS "" FIELD "DESCRIPTION" C WID 45 AS ""

COMMENT Adjusts the table layouts of the three new ACL tables.

OPEN Trans_Apr_s
DELETE FIELD CARDNUM OK 
DEFINE FIELD CARDNUM ASCII 1 16 WIDTH 19  
DELETE FIELD CODES OK 
DEFINE FIELD CODES ASCII 33 4 WIDTH 7  

OPEN Trans1_May_s
DELETE FIELD CODES OK 
DEFINE FIELD CODES ASCII 20 4 WIDTH 7  
DELETE FIELD AMOUNT OK 
DEFINE FIELD AMOUNT PRINT 144 9 2 WIDTH 9  
DELETE FIELD DATE OK 
DEFINE FIELD DATE DATETIME 24 10 PICTURE "YYYY-MM-DD" WIDTH 27  

OPEN Trans2_May_s
DELETE FIELD CODES OK 
DEFINE FIELD CODES ASCII 20 4 WIDTH 7  
DELETE FIELD AMOUNT OK 
DEFINE FIELD AMOUNT PRINT 204 9 2 WIDTH 9  
DELETE FIELD DATE OK 
DEFINE FIELD DATE DATETIME 24 10 PICTURE "YYYY-MM-DD" WIDTH 27  

COMMENT Verifies the imported data and provides user notifications.

OPEN Trans_Apr_s
VERIFY FIELDS CARDNUM AMOUNT DATE CODES CUSTNO DESCRIPTION ERRORLIMIT 10
IF WRITE1=0 DIALOG (DIALOG TITLE "User Dialog" WIDTH 490 HEIGHT 100 ) (BUTTONSET TITLE "&OK;&Cancel" AT 360 12 DEFAULT 1 ) (TEXT TITLE "Trans_Apr_s table:  0 data validity errors detected" AT 12 28 )
IF WRITE1>0 DIALOG (DIALOG TITLE "User Dialog" WIDTH 490 HEIGHT 100 ) (BUTTONSET TITLE "&OK;&Cancel" AT 360 12 DEFAULT 1 ) (TEXT TITLE "Trans_Apr_s table:  %WRITE1% data validity errors detected" AT 12 28 )

OPEN Trans1_May_s
VERIFY FIELDS CARDNUM CODES DATE CUSTNO DESCRIPTION AMOUNT ERRORLIMIT 10
IF WRITE1=0 DIALOG (DIALOG TITLE "User Dialog" WIDTH 490 HEIGHT 100 ) (BUTTONSET TITLE "&OK;&Cancel" AT 360 12 DEFAULT 1 ) (TEXT TITLE "Trans1_May_s table:  0 data validity errors detected" AT 12 28 )
IF WRITE1>0 DIALOG (DIALOG TITLE "User Dialog" WIDTH 490 HEIGHT 100 ) (BUTTONSET TITLE "&OK;&Cancel" AT 360 12 DEFAULT 1 ) (TEXT TITLE "Trans1_May_s table:  %WRITE1% data validity errors detected" AT 12 28 )

OPEN Trans2_May_s
VERIFY FIELDS CARDNUM CODES DATE CUSTNO DESCRIPTION AMOUNT ERRORLIMIT 10
IF WRITE1=0 DIALOG (DIALOG TITLE "User Dialog" WIDTH 490 HEIGHT 100 ) (BUTTONSET TITLE "&OK;&Cancel" AT 360 12 DEFAULT 1 ) (TEXT TITLE "Trans2_May_s table:  0 data validity errors detected" AT 12 28 )
IF WRITE1>0 DIALOG (DIALOG TITLE "User Dialog" WIDTH 490 HEIGHT 100 ) (BUTTONSET TITLE "&OK;&Cancel" AT 360 12 DEFAULT 1 ) (TEXT TITLE "Trans2_May_s table:  %WRITE1% data validity errors detected" AT 12 28 )

COMMENT Verifies the Badfile table and provides a user notification.
OPEN Badfile
VERIFY FIELDS InvoiceNo Prodno Price OrderQty ShipQty Total ERRORLIMIT 10
IF WRITE1=0 DIALOG (DIALOG TITLE "User Dialog" WIDTH 490 HEIGHT 100 ) (BUTTONSET TITLE "&OK;&Cancel" AT 360 12 DEFAULT 1 ) (TEXT TITLE "Badfile table:  0 data validity errors detected" AT 12 28 )
IF WRITE1>0 DIALOG (DIALOG TITLE "User Dialog" WIDTH 490 HEIGHT 100 ) (BUTTONSET TITLE "&OK;&Cancel" AT 360 12 DEFAULT 1 ) (TEXT TITLE "Badfile table:  %WRITE1% data validity errors detected" AT 12 28 )
CLOSE

COMMENT Appends the three new ACL tables into a single combined table.
APPEND Trans_Apr_s Trans1_May_s Trans2_May_s TO "Trans_All_s" OPEN
DIALOG (DIALOG TITLE "User Dialog" WIDTH 630 HEIGHT 100 ) (BUTTONSET TITLE "&OK;&Cancel" AT 500 12 DEFAULT 1 ) (TEXT TITLE "The combined transactions table (Trans_All_s) contains %WRITE1% records" AT 12 28 )

COMMENT Groups the combined table by merchant category code.
SUMMARIZE ON CODES SUBTOTAL AMOUNT OTHER DESCRIPTION TO "Trans_All_Grouped_s.FIL" OPEN PRESORT STATISTICS
DIALOG (DIALOG TITLE "User Dialog" WIDTH 700 HEIGHT 100 ) (BUTTONSET TITLE "&OK;&Cancel" AT 570 12 DEFAULT 1 ) (TEXT TITLE "The grouped transactions table (Trans_All_Grouped_s) contains %WRITE1% merchant category codes" AT 12 28 WIDTH 550 )

COMMENT Filters the combined table to show only prohibited transactions.
OPEN Trans_All_s
DEFINE FIELD f_Prohibited_codes COMPUTED MATCH(CODES, "5094", "5993", "7273", "7295", "7297", "7994", "7995", "7996", "8641")
SET FILTER TO f_Prohibited_codes

COMMENT Successful completion message.
DIALOG (DIALOG TITLE "User Dialog" WIDTH 490 HEIGHT 100 ) (BUTTONSET TITLE "&OK;&Cancel" AT 360 12 DEFAULT 1 ) (TEXT TITLE "The script successfully completed" AT 12 28 )

COMMENT A user confirmation is required before overwriting a table.
SET SAFETY ON