' Database_Select.iwb

AUTODEFINE "off"

$INCLUDE "windowssdk.inc"
$INCLUDE "commctrl.inc"
$INCLUDE "Richedit.inc"
$INCLUDE "ctl.inc"
$INCLUDE "..\ColorDef140.inc"

ENUM win_codes
	win_Table = 2000
	win_SQL
	win_RunSQL
	win_Clear_RE
	win_Quit
	main_Static3
	main_Static4
	main_Static5
	main_Static6
	main_RunSQL
	main_Clear_RE
	main_Quit
	main_RichEdit
	main_ListView
ENDENUM

CONST k2 = 2 * 1024

DEF win:DIALOG

DEF fontname:STRING
DEF size, bold, effects:INT

' DB fields
DEF pDB:POINTER
DEF iDB:UINT
DEF sDB[k2]:ISTRING
DEF errorcode:STRING

' DB Initial fields
DEF database[12]="DATABASE", user[12]="USERID", pw[12]="PASSWORD":ISTRING

DEF fldnm[60], fldv[60]:STRING
'DEF lv:NMLISTVIEW

DEF flds, lvi, lvcntr:INT
DEF i, j, l:INT

fontname = "Consolas"
size = 12
bold = 400
effects = 0

pDB = NULL

OPENWINDOW win,0,0,1024,768,@SIZE|@MINBOX|@MAXBOX|@NOAUTODRAW,0,"Database Select",&win_handler
CONTROL win,@STATIC,"     0",20,10,50,20,@SS_RIGHT,main_Static5
CONTROL win,@STATIC,"Columns",80,10,60,20,0,main_Static3
CONTROL win,@STATIC,"     0",220,10,50,20,@SS_RIGHT,main_Static6
CONTROL win,@STATIC,"Records",280,10,60,20,0,main_Static4
CONTROL win,@BUTTON,"Run SQL",580,6,120,23,0,main_RunSQL
CONTROL win,@BUTTON,"Clear RE",730,6,120,23,0,main_Clear_RE
CONTROL win,@BUTTON,"Quit",870,6,120,23,0,main_Quit
CONTROL win,@RICHEDIT,"Enter SQL here",20,30,970,100,0x50B010C4,main_RichEdit
CONTROL win,@LISTVIEW,"Table Selects",20,130,970,565,@LVSREPORT|@LVSSHOWSELALWAYS|@VSCROLL|@BORDER,main_ListView

SETFONT win,fontname,size,bold,effects

SETWINDOWCOLOR win,grey
SETCONTROLCOLOR win,main_Static3,cyan,grey
SETCONTROLCOLOR win,main_Static4,cyan,grey
SETCONTROLCOLOR win,main_Static5,cyan,grey
SETCONTROLCOLOR win,main_Static6,cyan,grey

SETFONT win,"Arial",12,700,0,main_RunSQL
SETFONT win,"Arial",12,700,0,main_Clear_RE
SETFONT win,"Arial",12,700,0,main_Quit

SETCONTROLCOLOR win,main_RunSQL,black,cyan
SETCONTROLCOLOR win,main_Clear_RE,black,gold
SETCONTROLCOLOR win,main_Quit,black,red

'margins
CONTROLCMD win,main_RichEdit,@RTSETMARGINS,10,10
'?? chars
CONTROLCMD win,main_RichEdit,@RTSETLINEWIDTH,(970-29) * 15
'2K text limit
CONTROLCMD win,main_RichEdit,@RTSETLIMITTEXT,k2

BEGINMENU win
	MENUTITLE "File"
	MENUITEM "Run SQL\t(f1)",0,win_RunSQL
	MENUITEM "Clear RE\t(f2)",0,win_Clear_RE
	SEPARATOR
	MENUITEM "Quit\t(f3)",0,win_Quit
ENDMENU

ADDACCELERATOR win,@FVIRTKEY,0x70,win_RunSQL   :'F1
ADDACCELERATOR win,@FVIRTKEY,0x71,win_Clear_RE :'F2
ADDACCELERATOR win,@FVIRTKEY,0x72,win_Quit     :'F3

SETFOCUS win,main_RichEdit

WAITUNTIL win.hWnd = 0
END

'====================
SUB win_handler(),INT
'====================
SELECT @MESSAGE
	CASE @IDCREATE
		CENTERWINDOW win
		SETFOCUS win,main_RichEdit
	CASE @IDCLOSEWINDOW
		CLOSEWINDOW win
	CASE @IDMENUPICK
		SELECT @MENUNUM
			CASE win_RunSQL
				Dump_RE()
				DestroyWindow(GETCONTROLHANDLE(win,main_ListView))
				DefineListView()
				ProcessListView()
				FixListView()
				SETFOCUS win,main_RichEdit
			CASE win_Clear_RE
				' Clear RichEdit
				CONTROLCMD win,main_RichEdit,@RTSETSELECTION,0,-1
				CONTROLCMD win,main_RichEdit,@RTREPLACESEL,""
				SETFOCUS win,main_RichEdit
			CASE win_Quit
				CLOSEWINDOW win
		ENDSELECT
	CASE @IDCONTROL
		SELECT @CONTROLID
			CASE main_RunSQL
				Dump_RE()
				DestroyWindow(GETCONTROLHANDLE(win,main_ListView))
				DefineListView()
				ProcessListView()
				FixListView()
				SETFOCUS win,main_RichEdit
			CASE main_Clear_RE
				' Clear RichEdit
				CONTROLCMD win,main_RichEdit,@RTSETSELECTION,0,-1
				CONTROLCMD win,main_RichEdit,@RTREPLACESEL,""
				SETFOCUS win,main_RichEdit
			CASE main_Quit
				CLOSEWINDOW win
		ENDSELECT
ENDSELECT
RETURN 0
ENDSUB

SUB DefineListView()
IF !CONTROLEXISTS(win,main_ListView)
	CONTROL win,@LISTVIEW,"Table Selects",20,160,970,535,@LVSREPORT|@LVSSHOWSELALWAYS|@VSCROLL|@BORDER,main_ListView
ENDIF
lvcntr = -1
FOR i = 0 TO flds
	lvcntr++
	CONTROLCMD win,main_ListView,@LVINSERTCOLUMN,lvcntr,fldnm[i]
NEXT i

'change some of the listview extended styles
SENDMESSAGE win,LVM_SETEXTENDEDLISTVIEWSTYLE,0, _
		LVS_EX_GRIDLINES|LVS_EX_FULLROWSELECT,main_ListView
'Set text foreground colour */
SENDMESSAGE win.hWnd,0x1024,0,Black,main_ListView
'Set text background colour */
SENDMESSAGE win.hWnd,0x1026,0,RGB(144,232,255),main_ListView
'Set background colour */
SENDMESSAGE win.hWnd,0x1001,0,RGB(144,232,255),main_ListView

RETURN
ENDSUB

SUB LoadListView()
lvi++
CONTROLCMD win,main_ListView,@LVINSERTITEM,lvi,fldv[0]
FOR i = 1 TO flds
	CONTROLCMD win,main_ListView,@LVSETTEXT,lvi,i,fldv[i]
NEXT i
RETURN
ENDSUB

SUB FixListView()
FOR i = 0 TO lvcntr
	'set column width
	CONTROLCMD win,main_ListView,@LVSETCOLWIDTH,i,-1
NEXT i
CONTROLCMD win,main_ListView,@LVSETCOLWIDTH,lvcntr,-2
RETURN
ENDSUB

SUB ProcessListView()
lvi = -1
OpenDB()

iDB=DBEXECSQL(pDB,sDB)
errorcode=DBGETERRORCODE(iDB)
IF LEN(errorcode)>0
	MESSAGEBOX win,"("+STR$(__LINE__)+")\n"+DBGETERRORTEXT(iDB)+"\n"+sDB+"\n"+errorcode,"Error"
ELSE
	IF iDB
		FOR i = 0 TO flds
			dbBindVariable(iDB,i+1,fldv[i])
		NEXT i
		WHILE DBGET(iDB)
			LoadListView()
		ENDWHILE
	ENDIF
ENDIF
DBFREESQL(iDB)
iDB=NULL

CloseDB()
SETCONTROLTEXT win,main_Static6,STR$(lvi+1)
RETURN
ENDSUB

SUB CloseDB()
IF pDB<>NULL THEN DBDISCONNECT(pDB)
pDB=NULL
RETURN
ENDSUB

SUB OpenDB()
IF pDB=NULL THEN pDB=DBCONNECTDSN(database,"UID="+user+";PWD="+pw)
IF pDB=NULL THEN MESSAGEBOX win,database + " not active\nTry again later","Problem",@MB_ICONEXCLAMATION
RETURN
ENDSUB

SUB Dump_RE()
DEF curr, prev:STRING

FOR i = 0 TO 59
	fldnm[i] = ""
	fldv[i] = ""
NEXT i

l = CONTROLCMD (win,main_RichEdit,@RTGETTEXTLENGTH)
IF l > 0
	CONTROLCMD win,main_RichEdit,@RTSETSELECTION,0,l
	CONTROLCMD win,main_RichEdit,@RTGETSELTEXT,sDB
	sDB = LCASE$(sDB)
	FOR i = 0 TO 59
		fldnm[i] = ""
	NEXT i
	j = 0
	prev = "z"
	FOR i = 1 TO l + 1
		curr = MID$(sDB,i,1)
		IF asc(mid$(curr,1,1)) < 32 OR curr = "," THEN curr = " "
		IF curr <> " "
			IF prev = " "
				IF fldnm[j] = "from"
					j--
					i = l + 2
					curr = ""
				ELSE
					IF fldnm[j] <> "select"
						IF fldnm[j] = "as"
							fldnm[j] = ""
							j--
							fldnm[j] = ""
						ELSE
							j++
						ENDIF
					ELSE
						fldnm[j] = ""
					ENDIF
				ENDIF
			ENDIF
			fldnm[j] += curr
		ENDIF
		prev = curr
	NEXT i
	flds = j
ELSE
	MESSAGEBOX win,"There is nothing in the SQL Box","Error"
ENDIF
SETCONTROLTEXT win,main_Static5,STR$(flds+1)
RETURN
ENDSUB