//Selects rows from GS table coded S or G and puts them into CatSubject & CatNoteText
//to be modified later for MAPI instead of writing subject and notetext to a file...
//

//Declare type of all variables
datetime ThisDate
string CrLf, ThisEnterprise, CatDateTime, Descr, CatPrice, CatFileName, CatNoteText 
integer GSId, GSIdHash, CatEntryCounter, CatFile
long Price

//Connect to the database
CONNECT USING SQLCA;
if SQLCA.SQLCODE <> 0 and SQLCA.SQLCODE <> -1 then
    MessageBox("SQLCODE ->" + STRING(SQLCA.SQLCODE) + "<-",SQLCA.SQLERRTEXT)
    halt
end if

//Setup SQL for cursor, CatEntry
DECLARE CatEntry CURSOR FOR
SELECT 
    "goods_and_services"."id",
    "goods_and_services"."descr",
    "goods_and_services"."price"
FROM
    "goods_and_services" WHERE ("goods_and_services"."class" IN ('S','G'))
USING SQLCA;

//Handle an empty cursor (might setup control breaks here?)
OPEN CatEntry;
FETCH CatEntry INTO :GSId, :Descr, :Price;
if SQLCA.SQLCode <> 0 then
    messagebox("No records found to catalog", "There were no goods and services records with Class G or S.")
    halt
end if

//Initialize variables
CrLf = char(13) + char(10)
ThisEnterprise = "9009"

//Format date into CatDateTime, use last 6 digits in CatFilieName for testing
//also used in CAT* transaction segment and subject line of future email
ThisDate = DateTime(Today(),Now())
CatDateTime = string(ThisDate, "yyyymmddhhmmss")
CatFileName = "D:\!465SU00\T" + right(CatDateTime,6) + ".TXT"

//Setup CatNoteText, will be written to disk or used as NoteText of MAPI object
CatNoteText = "CAT*9009*" + CatDateTime + CrLf
do while SQLCA.SQLCode = 0
    CatEntryCounter ++
    GSIdHash = GSIdHash + GSId
    CatPrice = string(Price, "#0.00")
    CatPrice = left(CatPrice,len(CatPrice)-3) + right(CatPrice,2)
    CatNoteText = CatNoteText + "ITEM*" + string(GSId) + "*" + Descr + "*" + CatPrice +   CrLf
    FETCH CatEntry INTO :GSId, :Descr, :Price;
loop
CatNoteText = CatNoteText + "ECAT*" + string(CatEntryCounter + 2) + "*" + string(GSIdHash)

//Close the cursor
CLOSE CatEntry;

//Write CatNoteText to disk
CatFile = FileOpen(CatFileName, StreamMode!, Write!, LockWrite!, Replace!)
If CatFile = -1 then
messagebox("No Can Open File","So Sorry, but this file can't be opened for some reason or other.")
halt
end if
FileWrite(CatFile,CatNoteText)
FileClose(CatFile)

messagebox("File Written", CatFileName + "has been written. Check it out...")