' C2A

autodefine "off"

$include "windowssdk.inc"

CONST WM_CTLCOLORDLG=0x0136

ENUM controls
	CSVPROGRESS=100
	STATIC1
	CSV_ED2
	CSV_BTN3
	STATIC4
	ACCESS_ED5
	PROCESS_BTN6
ENDENUM

DIALOG dlg

' CSV vars for binding
String DateMeet,Horse,SPrice,Meet
int RNO,SubJmp,Tab,Rank,Scud,Rtng,Neu,Brr,Pts,PtsRnk,T1
double T1W
' File And Table name
ISTRING CSVFile[260]
ISTRING AccessTable[260]
' Access database
POINTER pDB,pCSV
UINT hStmt

CREATEDIALOG dlg,0,0,458,250,@CAPTION|@SYSMENU,0,"CSV to Access",&dlg_Handler
	CONTROL dlg,@EDIT,"",24,217,412,12,0,CSVPROGRESS
	CONTROL dlg,@STATIC,"CSV File to Import",24,17,130,18,0x5000000B,STATIC1
	CONTROL dlg,@EDIT,"",24,38,412,24,0x50800000|@TABSTOP,CSV_ED2
	CONTROL dlg,@BUTTON,"Browse",154,70,140,28,0x50000000|@TABSTOP,CSV_BTN3
	CONTROL dlg,@STATIC,"Access Table Name",24,110,140,18,0x5000000B,STATIC4
	CONTROL dlg,@EDIT,"",24,135,412,24,0x50800000|@TABSTOP,ACCESS_ED5
	CONTROL dlg,@BUTTON,"Process",154,169,140,28,0x50000000|@TABSTOP,PROCESS_BTN6

DoModal dlg

WAITUNTIL ISWINDOWCLOSED(dlg)
	dbFreeSQL(hStmt)
	dbDisconnect(pCSV)
	dbDisconnect(pDB)
END

SUB dlg_Handler(),INT	
	SELECT @MESSAGE
CASE @IDINITDIALOG
	SETICON dlg,LOADIMAGE(GETSTARTPATH+"C2A.ico",@IMGICON)
	CenterWindow dlg
 	SetFont dlg,"MS Sans Serif",10,600,0,STATIC1
	SetControlColor dlg,STATIC1,0x0,RGB(121,150,222)
	SetFont dlg,"MS Sans Serif",10,400,0,CSV_ED2
	SetControlColor dlg,CSV_ED2,0x0,RGB(255,255,255)
	SetFont dlg,"Verdana",10,400,0,CSV_BTN3
	SetControlColor dlg,CSV_BTN3,0x0,GetSysColor(15)
	SetFont dlg,"MS Sans Serif",10,600,0,STATIC4
	SetControlColor dlg,STATIC4,0x0,RGB(121,150,222)
	SetFont dlg,"MS Sans Serif",10,400,0,ACCESS_ED5
	SetControlColor dlg,ACCESS_ED5,0x0,RGB(255,255,255)
	SetFont dlg,"Verdana",10,400,0,PROCESS_BTN6
	SetControlColor dlg,PROCESS_BTN6,0x0,GetSysColor(15)
CASE @IDCLOSEWINDOW
CASE& @IDDESTROY
	CloseDialog dlg,@IDOK

' Colour the dialog window
CASE WM_CTLCOLORDLG
	RETURN CreateSolidBrush(RGB(121,150,222))

CASE @IDCONTROL
	SELECT @CONTROLID
CASE CSV_BTN3 'Select the CSV File to import and the Access Database Table Name
	SetControlColor dlg,CSVPROGRESS,0x0,RGB(18,176,0) 'green
	SelectCSV()
CASE PROCESS_BTN6 'Process the import and saving of Access Table
	SetControlColor dlg,CSVPROGRESS,0x0,RGB(255,126,0) 'amber
	WAIT 5
	ProcessData()
ENDSELECT
ENDSELECT
RETURN 0
ENDSUB

SUB SelectCSV()
	int pos,selLen
	Istring Sels[260],aFile[260],CSVFolder[260]
	CSVFolder=GETSTARTPATH
	Sels=filerequest("Select CSV File",0,1,"CSV Files (*.csv)|*.csv|Text Files (*.txt)|*.txt||","csv",0,GETSTARTPATH)
' check that the file is a '.csv' file
IF instr(Sels,".csv")>0
' obtain the filename
	pos=0
WHILE INSTR(Sels,"\\")>0
	pos=INSTR(Sels,"\\")
	Sels=MID$(Sels,pos+1)
ENDWHILE
' work out the Access Table Name - suggest same as CSV
	selLen=LEN(Sels)
	aFile=Left$(Sels,selLen-4)
' set the edit controls with the CSV File
	SETCONTROLTEXT dlg,CSV_ED2,Sels
' set the Access Table name
	SETCONTROLTEXT dlg,ACCESS_ED5,aFile
ELSE
	Messagebox(dlg,"File selected does not have a 'CSV' extension!","File Error")
ENDIF
ENDSUB

SUB ProcessData()
	INT rets=0
	STRING msg
	SetCursor dlg,@CSWAIT
' check CSV entry
	CSVFile=GETCONTROLTEXT dlg,CSV_ED2
IF CSVFile=""
	Messagebox(dlg,"CSV File entry required in CSV File to Import edit box","File Error")
return
ENDIF
' Check Access entry
	AccessTable=GETCONTROLTEXT dlg,ACCESS_ED5
IF AccessTable=""
	messagebox(dlg,"Access File entry required in Access Table Name edit box","File Error")
return
ENDIF
' prepare Access Table
	rets=SetupAccess()
IF rets=0
' read the CSV and Save it into the DBase
	ProcessCSV()
ELSE
	SELECT rets
CASE 1
	msg="Could not Open Access Database"
	SetControlColor dlg,CSVPROGRESS,0x0,RGB(210,3,3) 'red
CASE 2
	msg="Access Table already exists"
	SetControlColor dlg,CSVPROGRESS,0x0,RGB(210,3,3) 'red
CASE 3
	msg="Access Table was not created"
	SetControlColor dlg,CSVPROGRESS,0x0,RGB(210,3,3) 'red
ENDSELECT
	messagebox(dlg,msg,"Access Database Error")
ENDIF
	SetCursor dlg,@CSARROW
ENDSUB

SUB ProcessCSV()
	INT cols
	string errorx
	string strErr
	string sqlStmt
	pCSV=dbConnect("Microsoft Text Driver (*.txt; *.csv)",GETSTARTPATH,"")
IF pCSV <> NULL
	sqlStmt="SELECT * FROM " + CSVFile
	hStmt=dbExecSql(pCSV, sqlStmt)
	errorx=dbGetErrorCode(hStmt)
IF LEN(errorx)
	strErr="Error Code: " + errorx
	strErr += "\nError Text: " + dbGetErrorText(hStmt)
	messagebox(dlg,strErr, "CSV File Error")
ELSE
' Get number of Columns from the above query
	cols=dbGetNumCols(hStmt) 
IF cols > 0
' bind columns to vars
	dbBindVariable(hStmt,1,DateMeet)
	dbBindVariable(hStmt,2,SubJmp)
	dbBindVariable(hStmt,3,Meet)
	dbBindVariable(hStmt,4,RNO)
	dbBindVariable(hStmt,5,Tab)
	dbBindVariable(hStmt,6,Horse)
	dbBindVariable(hStmt,7,Rank)
	dbBindVariable(hStmt,8,Scud)
	dbBindVariable(hStmt,9,Rtng)
	dbBindVariable(hStmt,10,Neu)
	dbBindVariable(hStmt,11,Brr)
	dbBindVariable(hStmt,12,Pts)
	dbBindVariable(hStmt,13,PtsRnk)
	dbBindVariable(hStmt,14,SPrice)
	dbBindVariable(hStmt,15,T1)
	dbBindVariable(hStmt,16,T1W)
' get records from recordset
WHILE dbGet(hStmt) 
' add to the AccessTable
	AddToAccess()
ENDWHILE 
ELSE 
	Messagebox(dlg,"Could not find CSV File Data","CSV Data Error")
ENDIF
ENDIF ' LEN(errorx)
' Free the memory for hStmt handle 
	dbFreeSQL(hStmt) 
' finished with database for CSV
	dbDisconnect(pCSV)
' finished with Access Database
	dbDisconnect(pDB)
	SetControlColor dlg,CSVPROGRESS,0x0,RGB(210,3,3) 'red
ENDIF  ' pCSV <> NULL
ENDSUB

SUB AddToAccess()
	UINT hRe=NULL
	ISTRING TheSql[400]
	TheSql="INSERT INTO " + AccessTable + " (DateMeet, SubJmp, Meet, RNO, Tab, Horse, Rank, Scud, Rtng, Neu, Brr, Pts, PtsRnk, SPrice, T1, T1W)"
	TheSql += " VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"
' PREPARE SQL for INSERT
	hRe=DBPREPARESQL(pDB, TheSql)
' if hRe ok then bind the fields 
IF hRe
	DBBINDPARAMETER(hRe,1,DateMeet,11)
	DBBINDPARAMETER(hRe,2,SubJmp)		
	DBBINDPARAMETER(hRe,3,Meet,24)
	DBBINDPARAMETER(hRe,4,RNO)
	DBBINDPARAMETER(hRe,5,Tab)
	DBBINDPARAMETER(hRe,6,Horse,30)
	DBBINDPARAMETER(hRe,7,Rank)
	DBBINDPARAMETER(hRe,8,Scud)
	DBBINDPARAMETER(hRe,9,Rtng)
	DBBINDPARAMETER(hRe,10,Neu)
	DBBINDPARAMETER(hRe,11,Brr)
	DBBINDPARAMETER(hRe,12,Pts)
	DBBINDPARAMETER(hRe,13,PtsRnk)
	DBBINDPARAMETER(hRe,14,SPrice,12)
	DBBINDPARAMETER(hRe,15,T1)
	DBBINDPARAMETER(hRe,16,T1W)
' Enter the data into database
IF DBEXECUTE(hRe)=FALSE
	MESSAGEBOX(dlg,"dbExecute error when adding data to Database","Database Input Error")
ENDIF
' Close hRe handle to Access Table
	DBFREESQL(hRe)	
	hRe=NULL
ELSE
	Messagebox(dlg,"Handle not received for SQL","Handle Error")
ENDIF
ENDSUB

SUB SetupAccess(),INT
	INT rets=0
	INT dOK=FALSE
	Pointer pTables
	ISTRING DTable[400]
' Connect to the Rating.mdb
	pDB=dbConnect("Microsoft Access Driver (*.mdb)",GETSTARTPATH+"Rating.mdb","")
IF pDB=NULL
' Couldn't open Rating.mdb so try creating Rating.mdb
	dbCreateMDB(GETSTARTPATH+"Rating.mdb")
' Connect to the Rating.mdb
	pDB=dbConnect("Microsoft Access Driver (*.mdb)",GETSTARTPATH+"Rating.mdb","")
ENDIF
' if pDB returned ok from the connect statement above 
IF pDB <> NULL
' Check and see if we have any tables in the database
	pTables=dbListTables(pDB)
' If pTables returned ok
IF pTables <> NULL
' Iterate through the Linked List pointed to by pTables
' To read the list of Tables available
FOR temp=EACH pTables as STRING
' Need to find if AccessTable is in the list of pTables
IF #temp=AccessTable
' Set dOK flag if table found
	dOK=TRUE
	rets=2
ENDIF
NEXT
' Finished with the pTables LinkedList so remove it and its contents
	ListRemoveAll(pTables,TRUE)
ENDIF
ELSE
	rets=1
return rets
ENDIF
' if dOK=FALSE then Table was not found in pTables - create the table
IF dOK=FALSE
	DTable="CREATE TABLE " + AccessTable + " (id COUNTER(1,1), DateMeet VARCHAR(11), "
	DTable += "SubJmp INT, Meet VARCHAR(24), RNO INT, Tab INT, "
	DTable += "Horse VARCHAR(30), Rank INT, Scud INT, Rtng INT, Neu INT, "
	DTable += "Brr INT, Pts INT, PtsRnk INT, SPrice VARCHAR(12), T1 INT, "
	DTable += "T1W DOUBLE )"
' Create AccessTable
	hStmt=dbExecSQL(pDB,DTable)
' Check for errors
IF LEN(dbGetErrorCode(hStmt))
	MESSAGEBOX dlg,"("+STR$(__LINE__)+") "+dbGetErrorText(hStmt)+"\n"+DTable,dbGetErrorCode(hStmt)
	rets=3
ELSE
	dOK=1
ENDIF
	dbFreeSQL(hStmt)
	hStmt=NULL
ENDIF
return rets
ENDSUB