' ======================================================================================
' Run Basic Code Generator
'  Using SQLite databse tables, this generates a very simple DB list and
'  maintenance system. You can sort by clicking the heading, do wild card drill down searches
'  on multiple fields,  create CSV files, and security with user/pass.
'  It can create drop down tables and radio buttons for maintenance.
'  The output is placed in \public\ with the name of the table you requested + .bas
' ======================================================================================
ver$    = "2.0"

bf$	= "<SPAN STYLE='font-family:Arial; font-weight:700; font-size:10pt'>"

' ----------------------------------
' Load Database
' ----------------------------------
[doDbLoad]
cr$	= chr$(13)
dirOf$ = DefaultDir$ + "\*.*"
' -------------------------------------------
' Shell out directory
' -------------------------------------------
[dirShell]
cls
[dirShell1]
html "<table bgcolor=lightgray width=50%><TR><TD align=center>"
button #dbex,	"Exit",  [main1]
html "</td></tr></TABLE>"
loc$	= strRep$(dirOf$,"*.*","")
x$	= shell$("dir ";dirOf$)

i 	= 1
while word$(x$,i,cr$) <> ""
  a$		 = word$(x$,i,cr$)
  if trim$(a$)	 = ""	then goto [dirNxt]
  if left$(a$,1) = " "	then goto [dirNxt]
  if left$(a$,1) = cr$	then goto [dirNxt]
  type$		 = mid$(a$,26,3)
  size$		 = mid$(a$,30,9)
  size$		 = strRep$(size$,",","")
  size		 = val(size$)
  if type$ <> "DIR" and size = 0 then goto [dirNxt]
  name$		 = mid$(a$,40)
  a$		 = strRep$(a$,"<","[")
  a$		 = strRep$(a$,">","]")
  html left$(a$,39)
  link #ddir,name$, [doDir]
       #ddir setkey(type$;"|";loc$;name$)
  html "<BR>"
  goto [dirNxt1]
  [dirNxt]
  print a$
  [dirNxt1]
  i = i + 1
wend
wait

[doDir]
type$	= word$(EventKey$,1,"|")
name$	= word$(EventKey$,2,"|")

if type$ = "DIR" then
  dirOf$ = name$;"\*.*"
  goto [dirShell]
end if

[dbLoad]
cls
loadDb$	= name$
open loadDb$ for input as #1			' see if the file is sqlite format
a$	= "x"
'if lof(#1) > 12 then
  a$	= input$(#1,13)
  a$	= lower$(a$)
'end if
close #1
if left$(a$,13) <> "sqlite format" then ' check file format
  print "This is not a SQLite file"
  html "<table bgcolor=pink width=50%><TR><TD align=center> this is not a SQLite file</TD></TR></TABLE>"
  goto [dirShell1]
end if
dbFile$	= loadDb$
sqliteconnect #sql,dbFile$  ' Connect to the DB"

CSSClass ".hide", "{visibility: hidden; height:0px; border:none}"	' CSS to hide buttons -------
' -----------------------------------
' get table names in the DB
' -----------------------------------
sql$   = "
SELECT  name
FROM    sqlite_master
WHERE   type = 'table'
ORDER BY name"
#sql execute(sql$)
rows      = #sql ROWCOUNT()
numddTbls = rows
dim ddTbls$(rows)
for i = 1 to rows
  #row       = #sql #nextrow()
  ddTbls$(i) = #row name$()
next i

wordWrap$ = "style='white-space: pre-wrap;";_
"white-space: -moz-pre-wrap;";_
"white-space: -pre-wrap;";_
"white-space: -o-pre-wrap;";_
"word-wrap: break-word'"

q$	= """"
dim type$(28)
dim typeVal$(28)

type$(01) = ""            : typeVal$(01)  = ""
type$(02) = "BIGINT"      : typeVal$(02)  = "I"	
type$(03) = "BLOB"        : typeVal$(03)  = "C"	
type$(04) = "BOOLEAN"     : typeVal$(04)  = "B"	
type$(05) = "CHAR"        : typeVal$(05)  = "C"	
type$(06) = "CLOB"        : typeVal$(06)  = "C"	
type$(07) = "DATE"        : typeVal$(07)  = "D"	
type$(08) = "DATETIME"    : typeVal$(08)  = "DT"
type$(09) = "DECIMAL"     : typeVal$(09)  = "I"	
type$(10) = "DOUBLE"      : typeVal$(10)  = "I"	
type$(11) = "FLOAT"       : typeVal$(11)  = "I"	
type$(12) = "INT"         : typeVal$(12)  = "I"	
type$(13) = "INTEGER"     : typeVal$(13)  = "I"	
type$(14) = "LONGBLOB"    : typeVal$(14)  = "C"	
type$(15) = "LONGTEXT"    : typeVal$(15)  = "C"	
type$(16) = "MEDIUMBLOB"  : typeVal$(16)  = "C"	
type$(17) = "MEDIUMINT"   : typeVal$(17)  = "I"	
type$(18) = "MEDIUMTEXT"  : typeVal$(18)  = "I"	
type$(19) = "SMALLINT"    : typeVal$(19)  = "I"	
type$(20) = "TEXT"        : typeVal$(20)  = "T"	
type$(21) = "TIME"        : typeVal$(21)  = "TI"
type$(22) = "TIMESTAMP"   : typeVal$(22)  = "C"	
type$(23) = "TINYBLOB"    : typeVal$(23)  = "C"	
type$(24) = "TINYINT"     : typeVal$(24)  = "I"	
type$(25) = "TINYTEXT"    : typeVal$(25)  = "C"	
type$(26) = "VARCHAR"     : typeVal$(26)  = "C"	
type$(27) = "VARCHAR"     : typeVal$(27)  = "C"	
type$(28) = "YEAR"        : typeVal$(28)  = "I"	

numSqlType	= 23
dim sqlType$(numSqlType)
numHtType	= 23
dim htType$(numHtType)
sqlType$(01) ="INT"		: htType$(01) ="<input type='number' step='1'>"
sqlType$(02) ="INTEGER" 	: htType$(02) ="<input type='number' step='1'>"
sqlType$(03) ="TINYINT" 	: htType$(03) ="<input type='number' step='1'>"
sqlType$(04) ="SMALLINT" 	: htType$(04) ="<input type='number' step='1'>"
sqlType$(05) ="MEDIUMINT"	: htType$(05) ="<input type='number' step='1'>"
sqlType$(06) ="BIGINT"		: htType$(06) ="<input type='number' step='1'>"
sqlType$(07) ="INT2"		: htType$(07) ="<input type='number' step='1'>"
sqlType$(08) ="INT8"		: htType$(08) ="<input type='number' step='1'>"
sqlType$(09) ="CHAR()"		: htType$(09) ="<input type='text' maxlength='?'>"     
sqlType$(10) ="VARCHAR()"	: htType$(10) ="<input type='text' maxlength='?'>"     
sqlType$(11) ="NCHAR()"		: htType$(11) ="<input type='text' maxlength='?'>"     
sqlType$(12) ="NVARCHAR()"	: htType$(12) ="<input type='text' maxlength='?'>"     
sqlType$(13) ="TEXT"		: htType$(13) ="<input type='text'>"
sqlType$(14) ="CLOB"		: htType$(14) ="<textarea>"
sqlType$(15) ="BLOB"		: htType$(15) ="<input type='file'>"
sqlType$(16) ="REAL"		: htType$(16) ="<input type='number' step='any'>"
sqlType$(17) ="DOUBLE"		: htType$(17) ="<input type='number' step='any'>"
sqlType$(18) ="FLOAT"		: htType$(18) ="<input type='number' step='any'>"
sqlType$(19) ="NUMERIC"		: htType$(19) ="<input type='number'>"
sqlType$(20) ="DECIMAL()"	: htType$(20) ="<input type='number' step='0.00001'>" 	' step num of decimals
sqlType$(21) ="BOOLEAN"		: htType$(21) ="<input type='checkbox'>"		' check box is either 0 or 1
sqlType$(22) ="DATE"		: htType$(22) ="<input type='date'>"			
sqlType$(23) ="DATETIME"	: htType$(23) ="<input type='datetime-local'>"		


dim ddSel$(100)
dim ddSelddField$(100)
dim ddSelddTbl$(100)
dim ddSelddComp$(100)
dim ddSelddDisp$(100)
dim ddSelddType(100)

dim dtSel$(100)
dim dtSelddField$(100)
dim dtSelddTbl$(100)
dim dtSelddComp$(100)
dim dtSelddDisp$(100)
dim dtSelddType(100)

[main]
gosub [heading]
doGen	= 0
'dbFile$ = "c:\"
ctlField$ = "rowid"
auto$	= "rowid"
button	#ato, "AutoSeq", [findAuto]	' RB label to go to
        #ato setid("gotAuto")		' the id of the button
        #ato cssclass("hide")		' hide the button
button	#pho, "PhotoId", [findPho]	' RB label to go to
        #pho setid("gotPho")		' the id of the button
        #pho cssclass("hide")		' hide the button

html bf$;"<TABLE width=50% BORDER=1 CELLPADDING=0 CELLSPACING=0>"
html "<TR><TD COLSPAN=3 BGCOLOR=#FFCC99 ALIGN=CENTER>RB Code Generator</TD></TR>"
html "<TR><TD BGCOLOR=#FFCC99>DataBase</TD><TD colspan=2>"
dbName$ = DefaultDir$ + "\"

html "<input size=50 tabindex=1 name='dbFile' id='dbFile' value='";dbFile$;"'>"
html "</TD></TR><TR><TD BGCOLOR=#FFCC99>Table Name</TD><TD align=center>"
'html "<input size=30 tabindex=2 name='tbl' id='tbl' value='";tbl$;"'>"
'listbox #tbl,ddTbls$(),1

'----------------------------------
'          Table Selection
'-----------------------------------
html "<input type='hidden' id='selectedTable' name='selectedTable'>" 			' Hidden input to store selection
html "<select id='selTable' onchange=""document.getElementById('selectedTable').value = this.value; document.getElementById('gotTable').click();"">";cr$
html "<option value=''>Table</option>";cr$
for i = 1 to numddTbls
  html "<option value='";ddTbls$(i);"'>";ddTbls$(i);"</option>"
next i
html "</select>";cr$
button	#tbl, "Table", [findTbl]	' RB label to go to
#tbl setid("gotTable")			' the id of the button
#tbl cssclass("hide")			' hide the button
html "</TD><TD id='shoTbl'></TD></TR>"

'----------------------------------
'   Auto Seq Field Selection
'-----------------------------------
html "<TR><TD BGCOLOR=#FFCC99>Auto Seq Field</TD><TD align=center id='shoAuto' name='shoAuto'>"
html "</TD><TD id='shoAutoField'></TD></TR>"

html "</TD></TR><TR><TD BGCOLOR=#FFCC99>Image/Video Field</TD><TD align=center id='shoPho' name='shoPho'>"
html "</TD><TD id='shoPhoField'></TD></TR><TD BGCOLOR=#FFCC99>Use Image Magick</TD><TD align=center>"
html "<input type='radio' name='magick' value='Y'> Yes"
html "<input type='radio' name='magick' value='N' checked> No"
html "</TD></TR><TR><TD COLSPAN=3 BGCOLOR=#FFCC99 ALIGN=CENTER>"

button  #gen, "Gen Code", [doCodeGen]
        #gen cssclass("sBtn")
html "   "
button  #dd, "Drop Down", [doDropDown]
html "   "
button  #dd, "Radio", [doRadio]
html "   "
button  #dd, "Check Box", [doCheckBox]
html "   "
button #ex, "Exit", [doExit]
       #ex cssclass("sBtn")
html "</TD></TR></TABLE>"
html "<div id=ddSel></div>"
html "<div id=ddc></div>"

html bf$
html "<TABLE width=50% BORDER=1 CELLPADDING=0 CELLSPACING=0>"
html "<TR "+wordWrap$+"><TD  BGCOLOR=#FFCC99 ALIGN=CENTER>Database</TD>"
html "<TD>Enter complete directory and database name</TD></TR>"

html "<TR "+wordWrap$+"><TD  BGCOLOR=#FFCC99 ALIGN=CENTER>Table</TD>"
html "<TD>A table within the database.</TD></TR>"

html "<TR "+wordWrap$+"><TD  BGCOLOR=#FFCC99 ALIGN=CENTER>Auto Seq<BR>Field</TD>"
html "<TD>A field that is use to Automatic Sequence. If none, it will use the records rowid. "
html "If there are gaps in the auto seq field the system will use that gap.</TD></TR>"

html "<TR "+wordWrap$+"><TD  BGCOLOR=#FFCC99 ALIGN=CENTER>Image or Video<BR>Pointer</TD>"
html "<TD>A field that contains the name of your media. Used to point to the disk address of media. "
html "This may contain a photo, video, or music "
html "It will give you the option to list and maintain the pointer. "
html "In the program change <FONT COLOR='red'>imgDir$</FONT> "
html " and <FONT COLOR='red'>imgPath$</FONT> to the directory of your images"
html "</TD></TR>"

html "<TR "+wordWrap$+"><TD  BGCOLOR=#FFCC99 ALIGN=CENTER>URL</TD>"
html "<TD>If the field name begins or ends with 'url' it will validate URL format "
html " Inpit must begin with http:/ to be valid"
html "</TD></TR>"

html "<TR "+wordWrap$+"><TD  BGCOLOR=#FFCC99 ALIGN=CENTER>eMail</TD>"
html "<TD>If the field name  begins or ends with 'email' it will validate eMail format "
html "</TD></TR>"


html "<TR "+wordWrap$+"><TD  BGCOLOR=#FFCC99 ALIGN=CENTER>Use Image Magick</TD>"
html "<TD>You can select to use Image Magic. You need to have it installed. Change the "
html " program field <FONT COLOR='red'>mgkLoc$</FONT> to point to the disk location of mediaMagick.exe  "
html " If your program includes images the the use of Image Magic greatly increases "
html " speed of your program by creating thumbnails of the images."
html "<TD></TR>"

html "<TR "+wordWrap$+"><TD  BGCOLOR=#FFCC99 ALIGN=CENTER>Drop Down or Radio</TD>"
html "<TD wrap>This will create Drop Down, Radio or Check Box input fields.<br>"
html "1. Click the [Drop Down] button<br>"
html "2. Select a field you want a drop down on<br>"
html "3. Select a table to get the drop down information from<br>"
html "4. Click the [Accept] button. This will bring up the field to compare and what you want to display.<br>"
html "5. Select something to 'Compare' to<br>"
html "6. Select something to 'Display' in the drop down<br>"
html "7, Click [Make Drop]. This will show you the data that will make the drop down. "
html "   You can [Delete] any drop downs you created.<br>"
html "8. Click [Gen Code]. This will create your program.<br>"
html "</TD></TR>"

html "<TR "+wordWrap$+"><TD  BGCOLOR=#FFCC99 ALIGN=CENTER>Auto Sequence field</TD>"
html "<TD wrap>Optional field.<br>"
html "This is a control field for the table. When adding a new record the system will automatically assign"
html "a unique sequence number to it. For example if the talbe is customer,"
html "and the control field for customer is custNum."
html "Then you can have the system assign a new custNum when you create a new Customer.</TD>"
html "</TR></TABLE>"

bf1$	= "<SPAN STYLE='font-family:Arial; font-weight:700; font-size:10pt'>"
html bf1$;"<TABLE width=50% BORDER=0 CELLPADDING=2 CELLSPACING=0>"
html "<TR><TD COLSPAN=9 BGCOLOR=#FFCC99 ALIGN='CENTER'>Directory of how this was tested</TD></TR>"
html "<TR BGCOLOR=#FFCC99>"
html "<TD>rbp(version)</TD>"
html "<TD>--></TD>"
html "<TD>projects</TD>"
html "<TD>--></TD>"
html "<TD>(YourProject)_project</TD>"
html "<TD>--></TD>"
html "<TD>data</TD>"
html "<TD>..</TD><TD>Database backups etc</TD></TR>"
html "<TR><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD>"
html "<TD BGCOLOR=#FFCC99>--></TD>"
html "<TD BGCOLOR=#FFCC99>images</TD>"
html "<TD BGCOLOR=#FFCC99>.</TD>"
html "<TD BGCOLOR=#FFCC99>photos video</TD>"
html "</TR></TABLE>"
wait

' --------------------------------
' Heading and message area
' --------------------------------
[heading]
cls
html bf$;"<CENTER><TABLE BORDER=0 CELLPADDING=0 CELLSPACING=0 width=100% BGCOLOR=tan>"
html "<TR><TD width=5%>";bf$;" V ";ver$;"</TD>"
html "<TD ALIGN=CENTER>";bh$;"Code Generator</TD><TD width=5%>"
button #exit, "Exit",[doExit]
       #exit cssclass("sBtn")
html "</TD></TR></TABLE>"

html "<TABLE BORDER=0 CELLPADDING=0 CELLSPACING=0 >"
html "<TR><TD BGCOLOR=#cc9999 id=errMsg></TD></TR>"
html "<TR><TD BGCOLOR=yellow  id=wrnMsg></TD></TR>"
html "<TR><TD BGCOLOR=lime    id=infoMsg></TD></TR>"
html "</TABLE>"
gosub [doMsg]
RETURN

[findTbl]
tbl$	= #request get$("selectedTable")  ' Get the value from the hidden input field
x$	= shoHTML$(tbl$,"shoTbl")
stbl$	= tbl$
gosub [getTblInfo]
x$	= shoHTML$("","shoAuto")
x$	= shoHTML$("","shoAutoField")
x$	= shoHTML$("","shoPho")
x$	= shoHTML$("","shoPhoField")

' ------ auto sequence control field
'auto$ = ""
'ctlField$ = ""
for i = 1 to snumFields
  if instr("DA CH VA TE",left$(scolType$(i),2)) = 0 then			' auto seq cannot be DATE,CHAR, VARCHAR , TEXT
    auto$ = auto$ + "<option value='";scolName$(i);"'>";scolName$(i);"</option>";cr$
  end if
next i
if auto$ <> "" then
  a$ 	= "<input type='hidden' id='selectedAuto' name='selectedAuto'>"  			' Hidden input to store selection
  a$ 	= a$ + "<select id='selAuto' onchange=""document.getElementById('selectedAuto').value = this.value; document.getElementById('gotAuto').click();"">";cr$
  a$ 	= a$ + "<option value=''>Auto Seq</option>";cr$
  a$ 	= a$ + "<option value=''>None</option>";cr$
  auto$ 	= a$ + auto$ + "</select>";cr$
  x$	= shoHTML$(auto$,"shoAuto")
end if

' ------ Photo field optional -----------
pho$ = ""
imgField$ = ""
for i = 1 to snumFields
  if instr("CH VA TE",left$(scolType$(i),2)) <> 0 then					' photo id must be CHAR, VARCHAR, TEXT
    pho$ = pho$ + "<option value='";scolName$(i);"'>";scolName$(i);"</option>";cr$
  end if
next i
if pho$ <> "" then
  a$ 	= "<input type='hidden' id='selectedPho' name='selectedPho'>"			' Hidden input to store selection
  a$ 	= a$ + "<select id='selPho' onchange=""document.getElementById('selectedPho').value = this.value; document.getElementById('gotPho').click();"">";cr$
  a$ 	= a$ + "<option value=''>Image Ptr</option>";cr$
  a$ 	= a$ + "<option value=''>None</option>";cr$
  pho$ 	= a$ + pho$ + "</select>";cr$
  x$	= shoHTML$(pho$,"shoPho")
end if
wait

[findAuto]
ctlField$ = #request get$("selectedAuto")  ' Get the value from the hidden input field
auto$	  = ctlField$
x$	  = shoHTML$(auto$,"shoAutoField")
wait

[findPho]
photoId$  = #request get$("selectedPho")  ' Get the value from the hidden input field
imgField$ = photoId$
x$	  = shoHTML$(photoId$,"shoPhoField")
wait


FUNCTION shoHTML$(shoWhat$,shoWhere$)
html "<script> document.getElementById('";shoWhere$;"').innerHTML = ";chr$(96);shoWhat$;chr$(96);"</script>"
END FUNCTION

' -----------------------------
' Create Drop Down or Radio
' -----------------------------
[doRadio]
doGen	= 1
typGen	= 1
hdrGen$	= "Radio"
goto [doCodeGen]

[doDropDown]
doGen	= 1
typGen	= 2
hdrGen$	= "Drop Down"
goto [doCodeGen]

[doCheckBox]
doGen	= 1
typGen	= 3
hdrGen$	= "Check Box"
goto [doCodeGen]

' -----------------------------
' Generate Program Maintenance
' -----------------------------
[doCodeGen]
dbFile$     = trim$(#request get$("dbFile"))
useMgk$     = #request get$("magick")
if imgField$ = "" then useMgk$ = "N"

dbFile$     = strRep$(dbFile$,"/","\")			' change fwd slashes to back
refTbl$     = strRep$(tbl$,"_",".")
refTbl$     = strRep$(refTbl$,"-",".")

'on error goto [fileErr]
files   #f, dbFile$
if  #f HASANSWER() = 0 then    				' does file exist
  goto [fileErr]
  else
  goto [fileOk]
end if

[fileErr]
errNum  = errNum + 1
errMsg$(errNum) = "DB file ";strRep$(dbFile$,"\","\\");" is not valid"
[fileOk]
if tbl$ = "" then
  errNum  = errNum + 1
  errMsg$(errNum) = "Please specify a table"
end if
if ctlField$ = "" then
  wrnNum  = wrnNum + 1
  wrnMsg$(wrnNum) = "No Auto Seq Field. (Optional)"
end if
if errNum > 0 then goto [main]
if wrnNum > 0 then gosub [doMsg]


' --------------------------------------------------------
' Get table column information
' --------------------------------------------------------
'on error goto [sqlErr]

stbl$ = tbl$
gosub [getTblInfo]

numFields    = snumFields
colNames$    = scolNames$
tblColNames$ = stblColNames$
colTypes$    = scolTypes$
colSizes$    = scolSizes$

dim colName$(numFields)
dim colType$(numFields)
dim colSize$(numFields)
'dim ddType$(numFields)				' [R]adio or [D]ropDown

if ctlField$ = "" then                  	' also the ctl field must exist
  ctlFieldOk$ = "Y"
  else
  ctlFieldOk$ = "N"
end if
c$  = lower$(ctlField$)

for i = 1 to numFields
  colName$(i) = scolName$(i)
  colType$(i) = scolType$(i)
  colSize$(i) = scolSize$(i)
  if c$ = lower$(colName$(i)) then ctlFieldOk$ = "Y"
  'print i;" ";colName$(i);" ";colType$(i);" ";colSize$(i)
next i

if numFields < 1 then
  errNum  = errNum + 1
  errMsg$(errNum) = "Table ";tbl$;" is not valid"
end if

if ctlFieldOk$ = "N" then
  errNum  = errNum + 1
  errMsg$(errNum) = "Control Field ";ctlField$;" is not one of these firld Names:";colNames$
end if

if errNum > 0 then
  gosub [doMsg]
  goto [main]
end if

if doGen = 0 then goto [noDropDown]
doGen	= 0

' ----------------------------
' get tables in database
' ----------------------------
sql$   = "
SELECT  name
FROM    sqlite_master
WHERE   type = 'table'
ORDER BY name"
#sql execute(sql$)
rows    = #sql ROWCOUNT()
dim ddTbls$(rows)
for i = 1 to rows
  #row = #sql #nextrow()
  ddTbls$(i) = #row name$()
next i

xdb$ = strRep$(dbFile$,"\","\\")
html "<script> document.getElementById('dbFile').value   = '";xdb$;"';</script>"
html "<script> document.getElementById('tbl').value      = '";tbl$;"';</script>"
html "<script> document.getElementById('ctlField').value = '";ctlField$;"';</script>"
html "<script> document.getElementById('ddc').innerHTML = '"
html "<TABLE BORDER=0 CELLPADDING=0 CELLSPACING=0>"
html "<TR><TD COLSPAN=2 BGCOLOR=wheat ALIGN=CENTER>";hdrGen$;"</TD></TR>"
html "<TR><TD VALIGN=TOP>"
html "<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>"
html "<TR><TD BGCOLOR=wheat ALIGN=CENTER>";tbl$;".field</TD>"
html "<TD>=</TD><TD BGCOLOR=wheat ALIGN=CENTER>Table.</TD>"
html "</TR><TR><TD>"

listbox #ddFields,colName$(),1

html "</TD><TD>=</TD><TD>"

listbox #ddTbls,ddTbls$(),1

html "</TD></TR><TR>"
html "<TD COLSPAN=3 BGCOLOR=wheat ALIGN=CENTER>"
if typGen = 1 then button #ddAccept, "Accept Radio",[ddAccept]
if typGen = 2 then button #ddAccept, "Accept Drop", [ddAccept]
if typGen = 3 then button #ddAccept, "Accept Check",[ddAccept]

html "</TD></TR></TABLE></TD><TD VALIGN=TOP id=ddTblFlds>"
html "</TD></TR></TABLE>"
html "';</script>"
wait

[ddAccept]
' ---------------------------------------
' get drop down table information
' ---------------------------------------
ddField$ = #ddFields	selection$()
ddTbl$	 = #ddTbls	selection$()

stbl$    = ddTbl$
gosub [getTblInfo]

numdd	= snumFields
dim ddTblFlds$(numdd)
for i = 1 to numdd
  ddTblFlds$(i) = scolName$(i)
next i

html "<script> document.getElementById('ddTblFlds').innerHTML = '"
html "<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>"
html "<TR><TD BGCOLOR=wheat ALIGN=CENTER>Compare</TD>"
html "<TD BGCOLOR=wheat ALIGN=CENTER>Display</TD></TR>"
html "<TR><TD>"

listbox #ddComp ,ddTblFlds$(),1

html "</TD><TD>"

listbox #ddDisp ,ddTblFlds$(),1

html "</TD></TR><TR><TD COLSPAN=2 BGCOLOR=wheat ALIGN=CENTER>"
if typGen = 1 then button #ddMakeDrop, "Make Radio", [ddMakeDrop]
if typGen = 2 then button #ddMakeDrop, "Make Drop",  [ddMakeDrop]
if typGen = 3 then button #ddMakeDrop, "Make Check", [ddMakeDrop]
html "</TD></TR></TABLE>"

html "';</script>"
wait

[ddMakeDrop]
html "<script> document.getElementById('ddTblFlds').innerHTML = '';</script>"

ddComp$	= #ddComp selection$()
ddDisp$ = #ddDisp selection$()
ddSel	= ddSel + 1
if typGen = 1 then x$ = "Radio "
if typGen = 2 then x$ = "Drop "
if typGen = 3 then x$ = "Check "

ddSel$(ddSel) = x$;" ";tbl$;".";ddField$;" = ";ddTbl$;".";ddComp$;" Display ";ddTbl$;".";ddDisp$
ddSelddField$(ddSel)	= ddField$
ddSelddTbl$(ddSel)	= ddTbl$
ddSelddComp$(ddSel)	= ddComp$
ddSelddDisp$(ddSel)	= ddDisp$
ddSelddType(ddSel)	= typGen

[ddSho]
html "<script> document.getElementById('ddSel').innerHTML = '"
html "<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>"
for i = 1 to ddSel
html "<TR><TD>"
button  #ddDel, "Delete", [ddDel]
        #ddDel setkey(i)
html i;" ";ddSel$(i);"<BR>"
html "</TD></TR>"
next i
html "</TABLE>"
html "';</script>"
wait

[ddDel]
j = val(EventKey$)
for i = j to ddSel - 1
  ddSel$(i)		= ddSel$(i + 1)
  ddSelddField$(i)	= ddSelddField$(i + 1)
  ddSelddTbl$(i)	= ddSelddTbl$(i + 1)	
  ddSelddComp$(i)	= ddSelddComp$(i + 1)
  ddSelddDisp$(i)	= ddSelddDisp$(i + 1)
next i
ddSel$(ddSel) = ""
ddSel = ddSel - 1
goto [ddSho]

' =======================================
[noDropDown]

for i = 1 to ddSel
  dtSel$(i)		= strRep$(ddSel$(i),"_","")
  dtSelddField$(i)	= strRep$(ddSelddField$(i),"_","")
  dtSelddTbl$(i)	= strRep$(ddSelddTbl$(i),"_","")	
  dtSelddComp$(i)	= strRep$(ddSelddComp$(i),"_","")
  dtSelddDisp$(i)	= strRep$(ddSelddDisp$(i),"_","")
next i

sql$    = "
SELECT  sql
FROM    sqlite_master
WHERE   type = 'table'
AND tbl_name = '";tbl$;"'"
#sql    execute(sql$)
fieldNum    = 0
WHILE #sql hasanswer()
  result$ = #sql nextrow$(" |")
  result$ = strRep$(result$,chr$(10),"")
  result$ = strRep$(result$,"''","")
  result$ = strRep$(result$,","," , ")
  result$ = strRep$(result$,"("," ( ")
  result$ = strRep$(result$,"     "," ")
  result$ = strRep$(result$,"    "," ")
  result$ = strRep$(result$,"   "," ")
  result$ = strRep$(result$,"  "," ")
  t1$     = word$(result$,1,"(")
  t2$     = word$(result$,2,t1$)
WEND

fld$    = ctlField$
gosub   [getTypeVal]
#sql disconnect()
fo$ = DefaultDir$ + "\public\" + tbl$ + ".bas"

html "<script> document.getElementById('ddSel').innerHTML = '"
html "Output File: ";strRep$(fo$,"\","\\")
html "';</script>"

open fo$ for output as #f
infoNum = infoNum + 1
infoMsg$(infoNum) = "Output at ";fo$

print #f, "' ***********************************************************"
print #f, "' ";tbl$;" ";"System"
print #f, "' Code created by RBgen. Author: Unknown"
print #f, "' Date: ";date$("yyyy-mm-dd");" ";time$()
print #f, "' ***********************************************************"
print #f, "call SetCSS"
print #f, "NullColumn$	= """""
print #f, "' set username/password"
print #f, "keyUser$	= """""
print #f, "keyPass$	= """""
print #f, "seeTile$	= ""N"""
print #f, refTbl$;"groupBy$	= """""
print #f, refTbl$;"dbJoin$	= """""
print #f, "q$		= ""','"""
print #f, "cr$		= chr$(10)"
print #f, "global #sql"
print #f, "bf$	= ""<SPAN STYLE='font-family:Arial; font-weight:700; font-size:12pt'>"""
print #f, ""
print #f, "' Set your color scheme here"
print #f, "clrHdr$ = ""PapayaWhip"" ' Header Color"
print #f, "clrBkg$ = ""Bisque""	    ' Background Color"
print #f, "clr0bg$ = ""OldLace""    ' Even line color"
print #f, "clr1bg$ = ""white""      ' Odd Line color"
print #f, "clrTbl$ = ""tan""        ' Table line color"
print #f, "clrF$   = ""#AFC4DE""    ' From color"
print #f, "clrT$   = ""#FFC1A4""    ' To color"

ddb$ = word$(dbFile$,2,"projects\")
print dbFile$

print #f, ""
print #f, "' ---------------------------------------------"
print #f, "' Get RUN pass information "
print #f, "' information is passed in UserInfo$"
print #f, "'  Delete this section if you do not need it"
print #f, "'  Or change it to your pass info"
print #f, "'  This is just a suggestion"
print #f, "' ---------------------------------------------"
print #f, "loginUserNum$   = trim$(word$(UserInfo$,1,""|""))"
print #f, "loginUserName$  = trim$(word$(UserInfo$,2,""|""))"
print #f, "loginCanMaint$  = trim$(word$(UserInfo$,3,""|""))"
print #f, "loginKeyKeeper$ = trim$(word$(UserInfo$,4,""|""))"
print #f, "runProg$        = trim$(word$(UserInfo$,5,""|""))"
print #f, "runProgData$    = trim$(word$(UserInfo$,6,""|""))"
print #f, "runPass$        = trim$(word$(UserInfo$,7,""|""))"
print #f, "runExit$        = trim$(word$(UserInfo$,8,""|""))"
print #f, "if runExit$     = """" then runExit$ = ""exitApp"""
print #f, "loginUserNum	   = val(loginUserNum$)"
print #f, "if runPass$ <> ""Y"" then titlebar """;tbl$;" System"""
print #f, "' Change imgDir$ and imgPath to point to your photos if used"
print #f, "projectDir$	   = """;word$(ddb$,1,"\");""""
print #f, "if left$(Platform$,3) = ""win"" then"
ldb$ = strRep$(ddb$,"\","/")
if ddb$ = "" then
  print #f, "	";refTbl$;"Db$	= """;dbFile$;""""
  else
  print #f, "	";refTbl$;"Db$	= DefaultDir$ + ""\projects\";ddb$;""""
end if
print #f, "	incdir$ = DefaultDir$ + ""\projects\"" + projectDir$ + ""\"""
print #f, "	imgDir$ = DefaultDir$ + ""\projects\"" + projectDir$ + ""\photo\"""
if useMgk$ = "Y" then
 print #f, "     mgkLoc$ = ""c:\Program Files\ImageMagick\magick.exe"" 		' image magick program location"
end if
print #f, "else"
a$ = strRep$(a$,"\","/")
if ddb$ = "" then
  x$ = strRep$(dbFile$,"\","/")
  print #f, "	";refTbl$;"Db$	= """;x$;""""
  else
  print #f, "	";refTbl$;"Db$	= DefaultDir$ + ""/projects/";ldb$;""""
end if
print #f, "	incdir$  = DefaultDir$ + ""/projects/"" + projectDir$ + ""/"""
print #f, "	imgDir$  = DefaultDir$ + ""/projects/"" + projectDir$ + ""/photo/"""
if useMgk$ = "Y" then
 print #f, "     mgkLoc$ = ""c:/Program Files/ImageMagick/magick.exe"" 		' image magick program location"
end if
print #f, "end if"
print #f, "	imgPath$ = ""../"";projectDir$;""/photo/"""

' print #f, tbl$;"Db$ = """;dbFile$;""""
' print #f, tbl$;"Fields$ = """;tblColNames$;""""
if ctlField$ <> "" then
  print #f, refTbl$;"orderBy$  = "" ORDER BY "";""";refTbl$;".";ctlField$;""""
  else
  print #f, refTbl$;"orderBy$  = "" ORDER BY "";""";refTbl$;".rowid"""
end if
print #f, refTbl$;"sortBy$   = """;refTbl$;".";ctlField$;""""
print #f, ""
print #f, "' ---------------------------------------------------------------------------------------------------------------------------"
print #f, "' Possible header sorts. Make changes here"
print #f, "' Header sorts the ^^ is changed to "" asc ^^ or ""desc"" descending when that heading is clicked"
print #f, "' add fields after the ^^ If you are sorting on lastName you may want to add ,firstName,middleName"
print #f, "' ---------------------------------------------------------------------------------------------------------------------------"
print #f, "numHdrSorts	= ";numFields
print #f, "dim  hdrSort$(numHdrSorts)"
for i = 1 to numFields
  f$  = refTbl$;".";colName$(i)
  if ctlField$ <> "" then
    f1$ = ",";refTbl$;".";ctlField$
    else
    f1$ = ",";refTbl$;".rowid"
  end if
  if ctlField$ = colName$(i) then f1$ = ""
  print #f,"hdrSort$(";i;")	= """;f$;"	^^";f1$;""""
next i
print #f, ""
print #f, "' -------------------------------------------------------------"
print #f, "' Possible Search Array"
print #f, "' Make changes to the searches you want to allow here"
print #f, "' srchDescr - is the description displayed to the user"
print #f, "' srchSel   - is the fields to Search"
print #f, "' -------------------------------------------------------------"
print #f, refTbl$;"numSrch  = ";numFields
print #f, "dim  ";refTbl$;"srchDescr$(";refTbl$;"numSrch)"
print #f, "dim  ";refTbl$;"srchSel$(";refTbl$;"numSrch)"

for i = 1 to numFields
  f$  = trim$(word$(colNames$,i,","))
  f1$ = upper$(left$(f$,1)) + mid$(f$,2)
  print #f, refTbl$;"srchDescr$(";i;")	= """;f1$;"""	: ";refTbl$;"srchSel$(";i;")	= """;tbl$;".";f$;""""
next i
' ---------------------------------------------
' convert underscore (_) to next char as caps
' ---------------------------------------------
cma$ = ""
for i = 1 to numFields
  p = 0
  f$  = trim$(word$(colNames$,i,","))
  [us2cap]
  p = instr(f$,"_",p)
  if p > 0 then
    f$ = left$(f$,p - 1) + upper$(mid$(f$,p+1,1)) + mid$(f$,p + 2)
    goto [us2cap]
  end if
  colDotNames$ = colDotNames$ + cma$ + f$
  cma$	= ","
next i

print #f, ""
print #f, "gosub [connectDb]			' Connect to the DB"	
if ddSel > 0 then
  print #f, "' -----------------------------------------------------"
  print #f, "' Drop Down, Radiogroup, and Checkbox lookup"
  print #f, "'  Make any changes to the select statements here"
  print #f, "' -----------------------------------------------------"
  for i = 1 to ddSel
    print #f, "'------------- ";dtSelddField$(i);" from ";ddSelddTbl$(i);" --------------------"
    print #f, "sql$ = ""SELECT ";ddSelddComp$(i);" as ";dtSelddComp$(i);",";ddSelddDisp$(i);" as ";dtSelddDisp$(i)
    print #f, "FROM ";ddSelddTbl$(i)
    print #f, "ORDER BY ";dtSelddDisp$(i);""""
    print #f, "#sql execute(sql$)"
    print #f, "rows = #sql ROWCOUNT()	'Get the number of rows"
    
    print #f, "cma$	= """""
    print #f, "sel";dtSelddField$(i);"Rows = rows		'Get the number of rows"
    print #f, "dim sel";dtSelddField$(i);"Comp$(rows)       'Compare field"
    print #f, "dim sel";dtSelddField$(i);"Disp$(rows)       'Display field"
    print #f, ""
    print #f, "for i = 1 to sel";dtSelddField$(i);"Rows"
    print #f, "  #row  = #sql #nextrow()"
    print #f, "  sel";dtSelddField$(i);"Comp$(i) = #row ";dtSelddComp$(i);"$()"
    print #f, "  sel";dtSelddField$(i);"Disp$(i) = #row ";dtSelddDisp$(i);"$()"
    print #f, "next i"
    'print #f, ""
  next i
  
  'print #f, "#sql disconnect()"
end if

print #f, ""
print #f, "' ================================================="
print #f, "' ";refTbl$;" List"
print #f, "' ================================================="
print #f, "[";refTbl$;"List]"
print #f, "' ------------------------------------------------"
print #f, "' how many records"
print #f, "' If there is a search it will be defined in dbWhere$"
print #f, "' if joins are needed it is in dbJoin$"
print #f, "' ------------------------------------------------"

print #f, " cls"
'print #f, "sqliteconnect    #sql,  ";refTbl$;"Db$  ' Connect to the DB"
print #f, "if numRecords = 0 then"
print #f, "	sql$	= ""SELECT count(*) as numRecords FROM ";tbl$;""" + ";refTbl$;"dbJoin$ + ";refTbl$;"dbWhere$"
print #f, "	#sql execute(sql$)"
print #f, "	result$		= #sql nextrow$("" |"")"
print #f, "	numRecords	= val(word$(result$,1,""|""))"
print #f, "end if"
print #f, ""
print #f, "gosub [";refTbl$;"Heading]          ' display headin and message area"
print #f, ""

if photoId$ <> "" then
print #f, "' --------------------------------------"
print #f, "'     Tile image video music display 5x5"
print #f, "' --------------------------------------"
print #f, "if seeTile$ = ""Y"" then"
print #f, "     sql$ = ""SELECT ";tbl$;".rowid	as rowid,* "
print #f, "              FROM ";tbl$;""" + ";refTbl$;"dbJoin$ +  ";refTbl$;"dbWhere$ + ";refTbl$;"groupBy$ + ";refTbl$;"orderBy$ + limit$"
print #f, "     html ""<center><div id='bigImg' name='bigImg'></div>"""
print #f, "     html ""<div id='bigExit' name='bigExit'></div>"""
print #f, "     html ""<TABLE BORDER=2 CELLPADDING=0 CELLSPACING=0 WIDTH=100% BGCOLOR=wheat>"""
print #f, ""	
print #f, "     #sql	execute(sql$)"
print #f, "     for r = 1 to lpp step 5"
print #f, "        html ""<TR>"""
print #f, "        for c = 1 to 5"
print #f, "           html ""<TD align=center valign=top width=20%>"""
print #f, "           if #sql hasanswer() then"
print #f, "                   #row      = #sql #nextrow()"
print #f, "                   gosub [";refTbl$;"GetData]"
print #f, "                   medId$    = ";imgField$;"$"

if useMgk$ = "Y" then
print #f, "                   img$ = imgDir$ + ""TN_"" + ";imgField$;"$   ' check for thumbnail"
print #f, "                   files #f,img$                               ' does thumbmail"
print #f, "                  if #f HASANSWER() <> 0 then			'    exist"
print #f, "                    medId$    = ""TN_"" + ";imgField$;"$       ' thumbnail"
print #f, "                     else"
print #f, "                    medId$    = ";imgField$;"$"
print #f, "                  end if"
end if

print #f, "                   mediaBig$ = ""N"""
print #f, "                   gosub [media]                          ' show media with controls"
print #f, "                   html ""<BR><B><SPAN STYLE='Font-Size :8pt'><FONT FACE='Arial'>"""
print #f, "                   link #pop,";ctlField$;"$, [popImg]"
print #f, "                        #pop setkey(";imgField$;"$)       ' set key to image field"
print #f, "                   html "" "";left$(medId$,20);""</FONT></SPAN></B>"""
print #f, "           html ""</td>"""
print #f, "           end if"
print #f, "           html ""</TD>"""
print #f, "        next c"
print #f, "        html ""</TR>"""
print #f, "     next r"
print #f, "html ""</TR></TABLE>"""
print #f, "wait"
print #f, "end if"
end if

print #f, "' -------------------------------------------------------------"
print #f, "'                Record Heading"
print #f, "' -------------------------------------------------------------"
print #f, "html bf$;""<CENTER><TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0 bordercolor="";clrTbl$;"" width=100%>"""
print #f, "html ""<TR bgcolor="";clrBkg$;"" align=center valign=bottom>"""
print #f, "html ""<TD>Maint"""
for i = 1 to numFields
  f$ = colName$(i)
  f$ = upper$(left$(f$,1)) + mid$(f$,2)
  f$ = strRep$(f$,"_",chr$(13))
  f$ = hdrBreak$(f$)
  print #f, "html ""</TD><TD>"" :link #hdr,  """;f$;""",	[sortHdr] :#hdr setkey(";i;") :#hdr CSSClass(""hdr"")"
next i
if imgField$ <> "" then	print #f, "html ""</TD><TD colspan=4>Image""" 	' make heading for image if requested

print #f, "html ""</TR>"""
print #f, ""

print #f, "sql$ = ""SELECT ";tbl$;".rowid as rowid";
for i = 1 to numFields
  f$  = colName$(i)
  'f1$ = colName$(i)
  print #f, ",";chr$(10);tbl$;".";f$;"	as ";f$;
next i

print #f, "  FROM ";tbl$;""" + ";refTbl$;"dbJoin$ +  ";refTbl$;"dbWhere$ + ";refTbl$;"groupBy$ + ";refTbl$;"orderBy$ + limit$"
print #f, "dispLine = 0"
print #f, "#sql	execute(sql$)"
print #f, "WHILE  #sql hasanswer()"
print #f, "     #row = #sql #nextrow()"
print #f, ""
print #f, "     gosub [";refTbl$;"GetData]"
print #f, ""
print #f, "     dispLine = dispLine + 1"
print #f, "     if dispLine AND 1 then"
print #f, "          html ""<TR BGCOLOR="";clr0bg$;"">"""
print #f, "      else"
print #f, "          html ""<TR BGCOLOR="";clr1bg$;"">"""
print #f, "     end if"
print #f, "     html ""<TD align=center>"""
print #f, "     if upper$(loginCanMaint$) = ""Y"" then"
print #f, "          link #lke, ""Add"", [";refTbl$;"Like";"]"
print #f, "               #lke setkey(rowid$)"
print #f, "               #lke cssclass(""lBtn"")"
print #f, ""
print #f, "          link #chg, ""Chg"", [";refTbl$;"Chg";"]"
print #f, "               #chg setkey(rowid$)"
print #f, "               #chg cssclass(""lBtn"")"
print #f, ""
print #f, "          link #del, ""Del"", [";refTbl$;"Del";"]"
print #f, "               #del setkey(rowid$)"
print #f, "               #del cssclass(""lBtn"")"
if imgField$ <> "" then
 print #f, "          link #upl, ""Up"", [";refTbl$;"Upl";"]"
 print #f, "               #upl setkey(rowid$)"
 print #f, "               #upl cssclass(""lBtn"")"
end if
print #f, "     end if"
print #f, "     link #vue, ""View"", [";refTbl$;"View]"
print #f, "          #vue setkey(rowid$)"
print #f, "          #vue cssclass(""lBtn"")"
print #f, "    html ""</TD>"""

' -------------------------------------------
' align fields in the <table>
' based on numeric or character
' -------------------------------------------
for i = 1 to numFields   
  fldDot$ = tbl$ + "." + colName$(i)
  fld$    = colName$(i)
  gosub [getTypeVal]
  if thisTypeVal$ = "I" OR thisTypeVal$ = "F" then
    print #f, "     html ""<TD align=right>"";";fld$;";""</TD>"""
    else
    if thisLen < 4 then
      print #f, "     html ""<TD align=center>"";";fld$;"$;""</TD>"""
      else
      print #f, "     html ""<TD>"";";fld$;"$;""</TD>"""
    end if
  end if
next i
' -------------------------------------------
' if using photos music or vidio
'  generate this code
' -------------------------------------------
if imgField$ <> "" then
  print #f, "' -----------------------------------"
  print #f, "' Display photos music and video"
  print #f, "' -----------------------------------"
  print #f, "if seePhoto$ = ""Y"" and ";imgField$;"$ <> """" then"
  print #f, " img$ = imgDir$ + ";imgField$;"$"
  print #f, " files #f,img$"
  print #f, "   if #f HASANSWER() <> 0 then"
  print #f, "	#f nextfile$()"
  print #f, "	imgId$ = #f NAME$()"
  print #f, "	j	= dispLine mod 3"
  print #f, "	for i = 1 to j"
  print #f, "		html ""<td width=1px style='border-left:none; border-right:none;'></TD>"""
  print #f, "	next i"
  print #f, "	html ""<TD align=center valign=top rowspan=4 style='border-left:none; border-right:none;'>"""
  print #f, "   medId$    = ";imgField$;"$"
  
  if useMgk$ = "Y" then
  print #f, "   img$ = imgDir$ + ""TN_"" + ";imgField$;"$   ' check for thumbnail"
  print #f, "   files #f,img$                               ' does thumbmail"
  print #f, "   if #f HASANSWER() <> 0 then			'    exist"
  print #f, "     medId$    = ""TN_"" + ";imgField$;"$       ' thumbnail"
  print #f, "      else"
  print #f, "     medId$    = ";imgField$;"$"
  print #f, "   end if"
  end if
 
  print #f, "   mediaBig$ = ""N"""
  print #f, "   bigger    = 0"
  print #f, "   gosub [media]                          ' show media with controls"
  print #f, "   html ""<BR><B><SPAN STYLE='Font-Size :8pt'><FONT FACE='Arial'>"";left$(imgId$,20);""</FONT></SPAN></B>"""
  print #f, "   html ""</td>"""
  print #f, "  end if"
  print #f, "end if"
  print #f, "     html ""</TR>"""
end if
print #f, "WEND"
print #f, "html ""</TABLE>"""
print #f, "wait"
print #f, ""
print #f, "' -----------------------------------"
print #f, "' Get data from database SQL command"
print #f, "'  numeric fields use val("
print #f, "'  other fields use trim$("
print #f, "' -----------------------------------"
print #f, "[";refTbl$;"GetData]"
for i = 1 to numFields
  fld$	= colName$(i)
  fldDot$	= tbl$ + "." + colName$(i)
  print #f, "	";fld$;"$		= #row ";fld$;"$()"
  gosub [getTypeVal]
  if thisTypeVal$ = "I" OR thisTypeVal$ = "F" then
    print #f, "	";fld$;"		= #row ";fld$;"()"
  end if
    
next i

print #f, "	rowid$		= #row rowid$()"
print #f, "RETURN"
print #f, ""
print #f, "[";tbl$;"ClearData]"

for i = 1 to numFields
  fld$		= colName$(i)
  fldDot$	= tbl$ + "." + colName$(i)
  print #f, "	";fld$;"$		= """""
  gosub [getTypeVal]
  if thisTypeVal$ = "I" OR thisTypeVal$ = "F" then
    print #f, "	";fld$;"		= 0"
  end if
  if thisTypeVal$ = "D" then
    print #f, "	";fld$;"$	= date$(""yyyy-mm-dd"")"
  end if
next i
print #f, "	rowid$		= """""
print #f, "RETURN"
print #f, ""
print #f, "' ------------------------------------"
print #f, "' ";tbl$;" Maintenance"
print #f, "' ------------------------------------"
print #f, ""
print #f, "[";refTbl$;"View]"
print #f, "acd$ = ""View"""
print #f, "goto [";refTbl$;"Mnt]"
print #f, ""
print #f, "[";refTbl$;"Like]"
print #f, "acd$ = ""AddLike"""
print #f, "goto [";refTbl$;"Mnt]"
print #f, ""
print #f, "[";refTbl$;"Chg]"
print #f, "acd$ = ""Change"""
print #f, "goto [";refTbl$;"Mnt]"
print #f, ""
print #f, "[";refTbl$;"Del]"
print #f, "acd$ = ""Delete"""
print #f, "goto [";refTbl$;"Mnt]"
print #f, ""
print #f, "[";refTbl$;"Add]"
print #f, "acd$ = ""Add"""
print #f, "goto [";refTbl$;"Mnt]"
print #f, ""
if imgField$ <> "" then
 print #f, "[";refTbl$;"Upl]"
 print #f, "acd$ = ""Accept Upload"""
 print #f, ""
end if
print #f, "[";refTbl$;"Mnt]"
print #f, "this";refTbl$;"rowid$   = EventKey$"
print #f, ""
print #f, "cls"
print #f, "gosub [headingMsg]"
print #f, ""
'print #f, "sqliteconnect    #sql,  ";refTbl$;"Db$  ' Connect to the DB"
print #f, "if acd$ <> ""Add"" then"
print #f, "	sql$ = ""SELECT ";chr$(10);tbl$;".rowid	as rowid";
for i = 1 to numFields
  f$  = colName$(i)
  'f1$ = tbl$ + "." + colName$(i)
  print #f, ",";chr$(10);tbl$;".";f$;"	as ";f$;
next i
print #f, chr$(10);"	 FROM ";tbl$;" WHERE ";tbl$;".rowid = '"";this";refTbl$;"rowid$;""'"""
print #f, "	#sql execute(sql$)"
print #f, "	#row = #sql #nextrow()"
print #f, "	gosub [";refTbl$;"GetData]"
print #f, "end if"
print #f, ""
print #f, "if acd$ = ""Add"" then"
print #f, "	gosub [";refTbl$;"ClearData]"
print #f, "end if"
'print #f, "#sql disconnect()            ' disconnect"
print #f, ""
print #f, "html bf$;""<center><TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0 bgcolor="";clrBkg$;"">"""
print #f, "html ""<caption>";tbl$;" Maintenance</caption><TR>"""
for i = 1 to numFields
  fld$    = colName$(i)
  f$      = upper$(left$(fld$,1)) + mid$(fld$,2)
  f$      = strRep$(f$,"_"," ")
  print #f, "html ""</td><TD bgcolor="";clrHdr$;"" align=right>";f$;"</TD><TD>"""
  gosub [getTypeVal]
  if fld$ = ctlField$ then
    print #f, "if acd$ = ""Add"" or acd$ = ""AddLike"" then"
    print #f, "	html ""Auto Assigned"""
    print #f, " ";ctlField$;"$ = """"" ' 
    print #f, "  else"
    print #f, "	html ";fld$
    print #f, "end if"
    goto [nxtFld]
  end if
  
  for j = 1 to ddSel
    if fld$	= ddSelddField$(j) then
      ' ---------------------------
      ' Check box
      ' ---------------------------
      if ddSelddType(j)	= 3 then
        print #f, "html ""<table border=1 CELLPADDING=0 CELLSPACING=0><tr>"""
        print #f, "for i = 1 to sel";fld$;"Rows"	
        print #f, "  if instr(";fld$;"$,sel";fld$;"Comp$(i)) > 0  then chk$ = ""checked"" else chk$ = """""
        print #f, "  id$ = """;fld$;""" + str$(i)"
        print #f, "  html ""<td><input type='checkbox' name='"";id$;""' id='"";id$;""' value='"";sel";fld$;"Comp$(i);""' "";chk$;"">"";sel";fld$;"Disp$(i)"
        print #f, "  html ""</td>"""
        print #f, "  if i mod 5 = 0 then html ""</tr><tr>"""
        print #f, "next i"
        print #f, "html ""</tr></table>"""
        goto [nxtFld]
      end if
      ' ---------------------------
      ' Drop Down
      ' ---------------------------
      if ddSelddType(j)	= 2 then
        print #f, "html ""<SELECT name='";fld$;"'>"""
        print #f, "for i = 1 to sel";fld$;"Rows"
        gosub [getTypeVal]
        if thisTypeVal$ = "I" OR thisTypeVal$ = "F" then
          print #f," if ";fld$;" = val(sel";fld$;"Comp$(i)) then sel$ = ""selected"" else sel$ = """""
          else
          print #f," if ";fld$;"$ = sel";fld$;"Comp$(i) then sel$ = ""selected"" else sel$ = """""
        end if
        print #f, "html  ""<option value='"";sel";fld$;"Comp$(i);""' "";sel$;"">"";sel";fld$;"Disp$(i);""</option>"""
        print #f, "next i"
        print #f, "html ""</select>"""
        goto [nxtFld]
      end if
      ' ---------------------------
      ' Radio
      ' ---------------------------
      if ddSelddType(j)	= 1 then
        'print #f, "rrdo";fld$;"$ = ";fld$;"$"
        print #f, "for i = 1 to sel";dtSelddField$(j);"Rows"	
        
        print #f," if ";fld$;"$ = sel";fld$;"Comp$(i) then chk$ = ""checked"" else chk$ = """""
        print #f, "html  ""<input type='radio' name='";fld$;"' id='";fld$;"' value='"";sel";fld$;"Comp$(i);""' "";chk$;"" />"";sel";fld$;"Disp$(i)"
        
        print #f, "next i"
        goto [nxtFld]
      end if
    end if
  next j
  if thisTypeVal$ = "I" then
    print #f, "html ""<input type='number' name='";fld$;"' id='";fld$;"' value='"";";fld$;"$;""' size=";thisLen$;"/>"""
    goto [nxtFld]
  end if
  if thisTypeVal$ = "F" then
    print #f, "html ""<input type='decimal' name='";fld$;"' id='";fld$;"' value='"";";fld$;"$;""' size=";thisLen$;"/>"""
    goto [nxtFld]
  end if
  if thisTypeVal$ = "D" then
    print #f, "html ""<input type='date' name='";fld$;"' id='";fld$;"' value='"";";fld$;"$;""' size=";thisLen$;"/>"""
    goto [nxtFld]
  end if
  if thisTypeVal$ = "DT" then
    print #f, "html ""<input type='datetime-local' name='";fld$;"' id='";fld$;"' value='"";";fld$;"$;""' size=";thisLen$;"/>"""
    goto [nxtFld]
  end if
  if thisTypeVal$ = "TI" then
    print #f, "html ""<input type='time' name='";fld$;"' id='";fld$;"' value='"";";fld$;"$;""' size=";thisLen$;"/>"""
    goto [nxtFld]
  end if
  
  if thisTypeVal$ = "Dx" or thisTypeVal$ = "DTx" or thisTypeVal$ = "TI" then
    print #f, "html ""<TABLE BORDER=0 CELLPADDING=0 CELLSPACING=0><TR><TD>"""
    print #f, "     textbox #";fld$;",";fld$;"$,";thisLen$
    print #f, "             #";fld$;" setid(""";fld$;""")"
    if thisTypeVal$ <> "TI" then
      print #f, "     button  #";fld$;"Pop,""D"", [";fld$;"]"
      print #f, "             #";fld$;"Pop setkey(""";fld$;""")"
    end if
    print #f, "     button  #";fld$;"Pop,""T"", [";fld$;".t]"
    print #f, "             #";fld$;"Pop setkey(""";fld$;""")"
    print #f, "html ""<TD id=";fld$;"Pop></TD></TR></TABLE>"""
    goto [nxtFld]
  end if
  
  if thisTypeVal$ = "T" then
    print #f, "html ""<textarea name='";fld$;"' id='";fld$;"' rows=4 cols=35>"";";fld$;"$;""</textarea>"""
    goto [nxtFld]
  end if
 
 if upper$(right$(fld$,3)) = "URL" or upper$(left$(fld$,3)) = "URL" then
  print #f,  "html ""<input type='url' name='";fld$;"' id='";fld$;"' value='"";";fld$;"$;""' size=";thisLen$;"/>"""
  goto [nxtFld]
 end if

 if upper$(right$(fld$,5)) = "EMAIL" or upper$(left$(fld$,5)) = "EMAIL"  then
  print #f,  "html ""<input type='email' name='";fld$;"' id='";fld$;"' value='"";";fld$;"$;""' size=";thisLen$;"/>"""
  goto [nxtFld]
 end if
 
  print #f, "html ""<input type='text' name='";fld$;"' id='";fld$;"' value='"";";fld$;"$;""' size=";thisLen$;"/>"""
  
  [nxtFld]
  if i mod 3 <> 0 then
    print #f, "html ""</TD>"""
    else
    print #f, "html ""</TD></TR><TR>"""
  end if
next i
print #f, "html ""<TR bgcolor="";clrHdr$;""><TD colspan=6 ALIGN=CENTER>"""
print #f, "if acd$ <> """" and acd$ <> ""View"" then"
print #f, "     button #acd, acd$, [";refTbl$;"doAcd]"
print #f, "            #acd setkey(this";refTbl$;"rowid$)"
'if imgField$ <>"" then
'  print #f, "button #upload,""Upload"" ,[doUpload]"
'  print #f, "          #upload setid(""upload"")"
'  print #f, "          #upload setkey(this";refTbl$;"rowid$)"
'end if
print #f, "end if"
print #f, "html ""        """
print #f, "     button #ex, ""Exit"", [";refTbl$;"List]"
print #f, "	       #ex cssclass(""extBtn"")"
print #f, "html ""</TD></TR></TABLE>"""
print #f, ""
if imgField$ <>"" then
 print #f, "if acd$ = ""Accept Upload"" then gosub [doUpload]" 
 print #f, ""
end if
if imgField$ <>"" then
  print #f, "if ";imgField$;"$ <> """" then"
  print #f, "  html ""<center><div style='width:80%; height:100vh;'>"""
  print #f, "  img$ = imgDir$ + ";imgField$;"$"
  print #f, "  files #f,img$"
  print #f, "  if #f HASANSWER() <> 0 then"
  print #f, "     #f nextfile$()"
  print #f, "     ext$ = upper$(word$(";imgField$;"$,2,"".""))"
  print #f, "     imgId$ = #f NAME$()"
  print #f, "     medId$    = ";imgField$;"$"
  print #f, "     mediaBig$ = ""Y"""
  print #f, "     gosub [media]                          ' show media with controls"
  print #f, "  end if"
  print #f, "  html ""</div>"""
  print #f, "end if"
end if
print #f, "wait"
print #f, ""
print #f, "' ====================================="
print #f, "' Do requested Add Change Delete"
print #f, "' ====================================="
print #f, "[";refTbl$;"doAcd]"
print #f, ""
print #f, "if acd$  = ""Delete"" then"
if imgField$ <> "" then
print #f, "  if ";imgField$;"$ <> """" then"
print #f, "    img$ = imgDir$ + ";imgField$;"$"
print #f, "    files #f,img$"
print #f, "    if #f HASANSWER() <> 0 then kill img$    ' kill image on disk"
print #f, ""
print #f, "    img$ = imgDir$ + ""TN_"" + ";imgField$;"$"
print #f, "    files #f,img$"
print #f, "    if #f HASANSWER() <> 0 then kill img$   ' kill thumbnail image on disk"
print #f, "  end if"
end if
print #f, "  sql$ = ""DELETE FROM ";tbl$;" WHERE rowid = '"";this";refTbl$;"rowid$;""'"""
print #f, "  goto  [";refTbl$;"execACD]"             ' execute Add Change Delete
print #f, "end if"
print #f, ""
if ctlField$ = "rowid" then colNames$ = colNames$ + "rowid,"		
print #f, "dbFields$ = """;left$(colNames$,len(colNames$)-1);""" "
print #f, ""
print #f, "' ---------------------------------------------"
print #f, "' Get data from the screen"
print #f, "' ---------------------------------------------"

for i = 1 to numFields
  fld$    = colName$(i)
  gosub [getTypeVal]
  for j = 1 to ddSel
    if fld$	= ddSelddField$(j) and ddSelddType(j) = 3 then ' Test for checkboxs
      print #f, ""
      print #f, "'-------------- ";fld$;" -- checkbox --------"
      print #f, fld$;"$	= """""
      print #f, "sep$	= """""
      print #f, "for i = 1 to sel";fld$;"Rows"
      print #f, "    id$ = """;fld$;""" + str$(i)"
      print #f, "    x$      = #request get$(id$)"
      print #f, "    if x$ <> """" then"
      print #f, "	  ";fld$;"$ = ";fld$;"$ + sep$ + x$"
      print #f, "	  sep$	= "","""
      print #f, "    end if"
      print #f, "next i"
      goto [nxtInFld]
    end if
  next j
  if fld$ <> ctlField$ then
    print #f, fld$;"$	= #request get$(""";fld$;""")	'- ";fld$;" -"
    gosub [getTypeVal]
    if thisTypeVal$ = "I"  then 
      print #f, fld$;" = val(";fld$;"$)"
    end if
  end if

  [nxtInFld]
next i

'print #f, "if errNum > 0 then"
'print #f, "   gosub [doMsg]"
'print #f, "   wait"
'print #f, "end if"
'print #f, ""

q$ = ""
a$ = ""
b$ = ""
preC$ = ",'"";"
aftC$ = ";""'"
preI$ = ","";"
aftI$ = ";"""

for i = 1 to numFields
  fld$    = colName$(i)
  gosub [getTypeVal]

print fld$;"--->";thisTypeVal$
  if ctlField$ <> "rowid" and fld$ = "rowid" then goto [nxtSqFld]			
  if thisTypeVal$ = "C" or thisTypeVal$ = "T" or thisTypeVal$ = "D" then 
     print #f, fld$;"$ = dblQuote$(";fld$;"$)"
     a$ = a$ + preC$;fld$;"$";aftC$
    else
     a$ = a$ + preI$ + "val(" + fld$ + "$)" + aftI$
  end if
[nxtSqFld]
next i

'print "B4:";a$
if left$(a$,7) = ","";val(" then a$ = mid$(a$,4)
if left$(a$,4) <> "val(" then a$ = """'"";" + a$
if right$(a$,2) = """'"  then a$ = a$ + """"
if right$(a$,2) = ";"""  then a$ = left$(a$,len(a$) -2)

'print a$
'input xxx

' ------- create SQL update command
c$   = chr$(10)
s$ = """UPDATE ";tbl$;" SET "
for i = 1 to numFields
  fld$    = colName$(i)
  if fld$ = "rowid" then goto [nxtSq1Fld]  			
  gosub [getTypeVal]
  if thisTypeVal$ = "I" or thisTypeVal$ = "F" then 
     s$ = s$ + c$ + fld$;"	= "";";fld$;";"""
    else 
     s$ = s$ + c$ + fld$;"	= '"";";fld$;"$;""'"
  end if
  c$ = ",";chr$(10)
[nxtSq1Fld]
next i

print #f, ""
print #f, "if acd$ = ""Change"" then "
print #f, "sql$ = ";s$;" WHERE ";ctlField$;" = '"";this";refTbl$;"rowid$;""'"""

print #f, "goto [";refTbl$;"execACD]"
print #f, "END IF"
print #f, ""

print #f, "if acd$ = ""Add"" or acd$ = ""AddLike"" then "
print #f, "	";ctlField$;"   = useNum(""";tbl$;""",""";ctlField$;""")"
print #f, "   ";ctlField$;"$  = str$(";ctlField$;")"
print #f, "END IF"

print #f, "dbVals$ = ";a$
print #f, "sql$ = ""INSERT or REPLACE into ";tbl$;" (""; dbFields$; "") VALUES (""; dbVals$; "")"""
print #f, ""
print #f, "[";refTbl$;"execACD]"
print #f, "#sql execute(sql$)"
'print #f, "#sql disconnect()"
print #f, ""
print #f, "goto [";refTbl$;"List]"
print #f, ""
print #f, "' ----------------------------"
print #f, "' They want Lines per page"
print #f, "' ----------------------------"
print #f, "[doLpp]"
print #f, "if lpp = 0 then lpp = 20"
print #f, "lpp	= min(10,lpp)"
print #f, "goto [";refTbl$;"List]"
print #f, ""
print #f, "' ----------------------------"
print #f, "' They want next page"
print #f, "' ----------------------------"
print #f, "[doNext]"
print #f, "lastPageNum	= val(EventKey$)"
print #f, "pageNum	= val(#pageNum contents$())"
print #f, "if lastPageNum   = pageNum then pageNum = pageNum + 1"
print #f, "goto [";refTbl$;"List]"
print #f, ""
print #f, "' ----------------------------"
print #f, "' They want prev page"
print #f, "' ----------------------------"
print #f, "[doPrev]"
print #f, "lastPageNum	= val(EventKey$)"
print #f, "pageNum	= val(#pageNum contents$())"
print #f, "if lastPageNum   = pageNum then pageNum = pageNum - 1"
print #f, "if pageNum < 1 then pageNum  = 1"
print #f, "goto [";refTbl$;"List]"
print #f, ""
print #f, "' -----------------------------------"
print #f, "' They want to see Tile"
print #f, "' -----------------------------------"
print #f, "[seeTile]"
print #f, "seeTile$	= EventKey$"
print #f, "if seeTile$	= ""Y"" then"
print #f, "	seeTile$ = ""N"""
print #f, "	else"
print #f, "	seeTile$ = ""Y"""
print #f, "end if"
print #f, "goto [";refTbl$;"List]"
print #f, ""
print #f, "' -----------------------------------"
print #f, "' They want to see or not see Photos"
print #f, "' -----------------------------------"
print #f, "[seePhoto]"
print #f, "seePhoto$	= EventKey$"
print #f, "if seePhoto$	= ""Y"" then"
print #f, "	seePhoto$ = ""N"""
print #f, "	else"
print #f, "	seePhoto$ = ""Y"""
print #f, "end if"
print #f, "goto [";refTbl$;"List]"
print #f, ""
print #f,"' ---------------------------------------------"
print #f, "' User Search screen"
print #f, "'  Change srchFields$ for allowed search fields"
print #f, "' ---------------------------------------------"
print #f, "[";refTbl$;"doSearch]"
print #f, " cls"
print #f, "wa$      = """""
print #f, "html bf$;""<TABLE BORDER=0 CELLPADDING=0 CELLSPACING=0 BGCOLOR="";clrHdr$;"" WIDTH=100%>"
print #f, "<TR><TD ALIGN=CENTER>Search for ";tbl$;""""
print #f, "html ""</TD></TR></TABLE>"""
print #f, "html bf$;""<table border=1 CELLPADDING=0 CELLSPACING=0 bgcolor="";clrHdr$;"" width=100%><TR>"""
print #f, "for i = 1 to ";refTbl$;"numSrch"
print #f, " html ""<TD align=right>"";";refTbl$;"srchDescr$(i);""</TD><TD>"""
print #f, " sVar$ = ""#srch("";i;"")"""
print #f, " textbox #sVar$,"""" "
print #f, " html ""</TD>"" "
print #f, " if i mod 4 = 0 then html ""</TR><TR>"""
print #f, "next i"
print #f, "html ""</TR></TABLE>"""
print #f, "html bf$;""<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0 WIDTH=100% BGCOLOR="";clrHdr$;""><TR><TD ALIGN=CENTER>"""
print #f, "     button #find, ""Search"",  [";refTbl$;"doFind]"
print #f, ""
print #f, "html ""</TD><TD align=center>"""
print #f, "     button #exit, ""Exit"", [";refTbl$;"List]"
print #f, "	       #exit cssclass(""extBtn"")"
print #f, "html ""</TD></TR></TABLE>"""
print #f, ""
print #f, "html bf$;""<CENTER><TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>"
print #f, " <TR><TD ALIGN=CENTER COLSPAN=2 BGCOLOR="";clrHdr$;"" ALIGN=CENTER>Using wild card (*)</TD></TR>"
print #f, " <TR><TD>*before</TD><TD>Search for ends with</TD></TR>"
print #f, " <TR><TD>after*</TD><TD>Search for begins with</TD></TR>"
print #f, " <TR><TD>*both*</TD><TD>Contains the value somewhere</TD></TR>"
print #f, " <TR><TD>None</TD><TD>Must match</TD></TR>"
print #f, " <TR><TD COLSPAN=2 BGCOLOR="";clrHdr$;"" ALIGN=CENTER>Using operation Code</TD></TR>"
print #f, " <TR><TD align=center>></TD><TD>Greater Than</TD></TR>"
print #f, " <TR><TD align=center><</TD><TD>Less Than</TD></TR>"
print #f, " <TR><TD align=center><></TD><TD>Not Equal To</TD></TR>"
print #f, " <TR><TD align=center>=</TD><TD>Equal To</TD></TR>"
print #f, " <TR><TD align=center><=</TD><TD>Less Than or Equal To</TD></TR>"
print #f, " <TR><TD align=center>>=</TD><TD>Greater Than or Equal To</TD></TR>"
print #f, " <TR><TD COLSPAN=2 ALIGN=CENTER>Note: To find items with values use <> ''</TD></TR>"
print #f, " </TABLE>"""
print #f, ""
print #f, "wait"
print #f, ""
print #f, "[";refTbl$;"doFind]"
print #f, refTbl$;"dbWhere$ = """""
print #f, "for i = 1 to ";refTbl$;"numSrch"
print #f, "	oVar$	= ""#srch("";i;"")"""
print #f, "	srch$	= trim$(#oVar$ contents$())"
print #f, ""
print #f, "	if srch$ <> """" then"
print #f, "		wld$	= goWild$(";refTbl$;"srchSel$(i),srch$)"
print #f, "		";refTbl$;"dbWhere$	= ";refTbl$;"dbWhere$ + wld$"
print #f, "	end if"
print #f, "next i"
print #f, "if ";refTbl$;"dbWhere$ <> """" then ";refTbl$;"dbWhere$ = "" WHERE "" + ";refTbl$;"dbWhere$"
print #f, "numRecords   = 0"
print #f, "goto [";refTbl$;"List]"
print #f, "wait"
print #f, ""
print #f, " FUNCTION goWild$(fldDot$,str$)"
print #f, " global wa$"
print #f, " goWild$ = trim$(str$)"
print #f, " l   = len(goWild$)"
print #f, " if l > 0 then"

print #f, "  if left$(goWild$,2) = ""<>"" then op$ = "" <> """
print #f, "  if left$(goWild$,2) = ""!="" then op$ = "" <> """
print #f, "  if left$(goWild$,2) = ""<="" then op$ = "" <= """
print #f, "  if left$(goWild$,2) = ""=<"" then op$ = "" <= """
print #f, "  if left$(goWild$,2) = "">="" then op$ = "" >= """
print #f, "  if left$(goWild$,2) = ""=>"" then op$ = "" >= """
print #f, ""
print #f, " if op$ <> """" then"
print #f, " 	goWild$ = trim$(mid$(goWild$,3))"
print #f, " 	if goWild$ = ""''"" then goWild$ = """""
print #f, " 	goto [endIt]"
print #f, " end if"
print #f, "  if left$(goWild$,1) = "">""  then op$ = "" > """
print #f, "  if left$(goWild$,1) = ""<""  then op$ = "" < """
print #f, "  if left$(goWild$,1) = ""=""  then op$ = "" = """
print #f, ""
print #f, " if op$ <> """" then"
print #f, " 	goWild$ = trim$(mid$(goWild$,2))"
print #f, " 	if goWild$ = ""''"" then goWild$ = """""
print #f, " 	goto [endIt]"
print #f, " end if"
print #f, "   op$ = "" LIKE """
print #f, ""
print #f, "  if INSTR(goWild$,""*"") <> 0 then wld$ = ""Y"""
print #f, "  if INSTR(goWild$,""%"") <> 0 then wld$ = ""Y"""
print #f, "  if wld$ <> ""Y"" then goWild$ = goWild$ + ""%"""
print #f, "  if right$(goWild$,1)   = ""*"" then goWild$ = left$(goWild$,l - 1) + ""%"""
print #f, "  if left$(goWild$,1)    = ""*"" then goWild$ = ""%"" + mid$(goWild$,2)"
print #f, "[endIt]"
print #f, "  goWild$    = wa$;fldDot$;op$;""'"";goWild$;""'"""
print #f, "  wa$    = "" AND """
print #f, "  else"
print #f, "  goWild$    = """""
print #f, " end if"
print #f, " END FUNCTION"
print #f, ""
print #f, "' -----------------------------------------"
print #f, "' sort header"
print #f, "' -----------------------------------------"
print #f, "[sortHdr]"
print #f, "sortNum    = val(EventKey$)"
print #f, "if sortNum = preSortNum then"
print #f, "	if ad$ = """" then ad$ = "" desc"" else ad$	= """""
print #f, "end if"
print #f, "if sortNum <> preSortNum then ad$ = """""
print #f, refTbl$;"orderBy$	= "" ORDER BY "" + strRep$(hdrSort$(sortNum),""^^"",ad$)"
print #f, refTbl$;"sortBy$   = hdrSort$(sortNum)"
print #f, "preSortNum	= sortNum"
print #f, "goto [";refTbl$;"List]"
print #f, ""
print #f, "' ============================================"
print #f, "' List Heading"
print #f, "' ============================================"
print #f, "[";refTbl$;"Heading]"
print #f, "' ---------------------------------------"
print #f, "' Did they change the lines per page lpp"
print #f, "' ---------------------------------------"
print #f, "x = #lpp ISNULL()"
print #f, "if x = 0 then lpp = val(#lpp contents$())"
print #f, ""
print #f, "pageNum  = max(1,pageNum)  ' make sure it has a page number"
print #f, "if lpp < 1 then lpp = 30   ' lines per page must be specified"
print #f, "lpp	= max(5,lpp)          ' make sure it has a least 5 lines per page"
print #f, "lpp	= min(100,lpp)        ' do not allow over 100 lines per page"
print #f, ""
print #f, "totPages = int(numRecords / lpp)"
print #f, "if lpp * totPages <> numRecords then totPages = totPages + 1"
print #f, "pageNum  = min(totPages,pageNum)"
print #f, "pageNum  = max(1,pageNum)"
print #f, "limitBeg = (pageNum * lpp) - lpp  'limit begin value"
print #f, ""
print #f, "dispLine = 0"
print #f, ""
print #f, "limit$   = "" LIMIT "" ; limitBeg ; "","" ; lpp"
print #f, ""
print #f, "html bf$;""<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0 WIDTH=100% BGCOLOR="";clrHdr$;"">"""
print #f, "html ""<TR>"""
print #f, ""
print #f, "if runPass$ <> ""Y"" then"
print #f, "html ""<TD ALIGN=center>"""
print #f, "     button #add, ""Login"",[doLogin]"
print #f, "            #add setkey(0)"
print #f, "html ""</TD>"""
print #f, "end if"
print #f, ""
print #f, "if upper$(loginCanMaint$) = ""Y"" then"
print #f, "html ""<TD ALIGN=center>"""
print #f, "     button #add, ""Add"", [";refTbl$;"Add]"
print #f, "            #add setkey(0)"
print #f, "html ""</TD>"""
print #f, "end if"
print #f, ""
print #f, "html ""<TD ALIGN=center><A title='Export to Spreadsheet'>"""
print #f, "     button #exp, ""CSV"", [exportCsv]"
print #f, "html ""</A></TD>"""
print #f, ""
print #f, "html ""<TD ALIGN=center>"""
print #f, "	button	#help, ""Help"",[";refTbl$;"doHelp]"
print #f, "		#help cssclass(""lBtn"")"
print #f, ""
print #f, "html ""</TD><TD ALIGN=center>"""
print #f, "	button	#exit, ""Exit"",[doExit]"
print #f, "		#exit setid(""exitMe"")"
print #f, "	        #exit cssclass(""extBtn"")"
print #f, "if runPass$ = ""Y"" then"
print #f, "	html ""<script>"
print #f, "	document.getElementById('exitMe').name = document.getElementById('"";runExit$;""').name;"
print #f, "	</script>"""
print #f, "end if"
print #f, "html ""</TD><TD>Records:"";numRecords"
print #f, "html ""</TD><TD>Pages:"";totPages"
print #f, "html ""</TD><TD ALIGN=right>"""
print #f, "html ""<A title='List Previous page'>"""
print #f, "     button #prev, ""Prev"",[doPrev]"
print #f, "            #prev setkey(pageNum)"
print #f, "html ""</A>"""
print #f, "html ""</TD><TD width=2%>"""
print #f, "     TEXTBOX #pageNum, pageNum,2"
print #f, ""
print #f, "html ""</TD><TD>"""
print #f, "html ""<A title='List next page'>"""
print #f, "     button #next, ""Next"",[doNext]"
print #f, "            #next setkey(pageNum)"
print #f, "html ""</A>"""
print #f, "html ""<TD ALIGN=right><A title='Lines Per Page'>"""
print #f, "     button	#lpp, ""Lpp"",[doLpp]"
print #f, "html ""</A></TD><TD align=left>"""
print #f, " TEXTBOX #lpp, lpp,2"
print #f, "html ""</TD><TD>"""
if imgField$ <>"" then
  print #f, "' -----------------------------------------"
  print #f, "' flip/flop media Tile and See buttons"
  print #f, "' -----------------------------------------"
  print #f, "html ""<A title='Grid of images 5 across'>"""
  print #f, "button #til, ""Tile"",[seeTile]"
  print #f, "       #til setkey(seeTile$)"
  print #f, "html ""</A>"""
  print #f, "if seeTile$ = ""Y"" then"
  print #f, "	html ""Yes"""
  print #f, "	else"
  print #f, "	html ""No"""
  print #f, "end if"
  print #f, "html ""</TD><TD>"""
  print #f, "html ""<A title='Show photos and media with list'>"""
  print #f, "button #sep, ""See Photo"",[seePhoto]"
  print #f, "       #sep setkey(seePhoto$)"
  print #f, "html ""</A>"""
  print #f, "if seePhoto$ = ""Y"" then"
  print #f, "	html ""Yes"""
  print #f, "	else"
  print #f, "	html ""No"""
  print #f, "end if"
end if

print #f, "html ""</TD></TR></TABLE>"""
'TEXTBOX #lpp, lpp,2
html "</td></TR></TABLE>"

print #f, "html bf$;""<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0 WIDTH=100% BGCOLOR="";clrHdr$;"">"""
print #f, "html ""<TR><TD>Sort"""
print #f, "html ""<span style='background-color:white;'>"";";refTbl$;"sortBy$"
print #f, "html ""</TD><TD>"""
print #f, "     button #srch, ""Search"",[";refTbl$;"doSearch]"
print #f, "            #srch setkey(srchData$)"
print #f, "html ""<span style='background-color:white;'>"";";refTbl$;"dbWhere$"
print #f, "html ""</TD></TR></TABLE>"""

print #f, "[headingMsg]"
print #f, "html ""<TABLE BORDER=0 CELLPADDING=0 CELLSPACING=0 >"""
print #f, "html ""<TR><TD BGCOLOR=pink   id=errMsg></TD></TR>"""
print #f, "html ""<TR><TD BGCOLOR=yellow id=wrnMsg></TD></TR>"""
print #f, "html ""<TR><TD BGCOLOR=lime   id=infoMsg></TD></TR>"""
print #f, "html ""</TABLE>"""
print #f, "gosub [doMsg]"
print #f, "RETURN"
print #f, ""

if imgField$ <>"" then
  print #f,  "' -----------------------------------------"
  print #f,  "' Do Upload image, video, music"
  print #f,  "' -----------------------------------------"
  print #f,  "[doUpload]"
  print #f,  ""
  print #f,  "upload """"; uploadId$"
  print #f,  "upFileName$	= word$(uploadId$,1,""."")"
  print #f,  "upFileExt$	= word$(uploadId$,2,""."")"
  print #f,  "toUpFileName$	= upFileName$"
  print #f,  ""
  print #f, "f$ = upper$(left$(""";tbl$;""",3))"  'first 3 letters
  print #f, "f$ = f$ + right$(""0000""+";ctlField$;"$,4)"
  print #f, "f$ = f$ + ""."" + upFileExt$"
  print #f, "toUpFileName$	= f$"
  print #f, "photoId$ = toUpFileName$"
  print #f, "f$	      = imgDir$;uploadId$"
  print #f, "upCount		= 0"
  print #f, "tf$    = imgDir$;toUpFileName$"
  print #f, "files  #f, tf$"
  print #f, "if #f HASANSWER() then kill tf$"
  print #f, ""
  print #f, "' -------------------------------------"
  print #f, "' load media to photo directory"
  print #f, "' -------------------------------------"
  print #f, "OPEN uploadId$ FOR binary AS #f"
  print #f, "filedata$  = input$(#f, LOF(#f))"
  print #f, "CLOSE #f"
  print #f, ""
  print #f, "OPEN tf$ FOR binary AS #f"
  print #f, "PRINT  #f, filedata$"
  print #f, "CLOSE  #f"
  print #f, ""
  print #f, "sql$ = ""UPDATE ";tbl$;" SET ";imgField$;" = '"";photoId$;""' WHERE ";ctlField$;" = "";";ctlField$;"$"
  print #f, "#sql execute(sql$)"
  print #f, ""
if useMgk$ = "Y" then
 print #f, "' ----------------------------------------------"
 print #f, "' create thumbnail file with TN_ file prefix"
 print #f, "' ----------------------------------------------"
 print #f, "filename$     = ";imgField$;"$         'photo name"
 print #f, "newWidth	= 150                         ' desired width if thumbnail"
 print #f, "newFilename$  = ""TN_"" + filename$      ' Create the new filename with ""tn_"" prepended"
 print #f, ""
 print #f, "' Full path to magick.exe with quotes around it"
 print #f, "magickPath$  = chr$(34) + mgkLoc$ + chr$(34)"
 print #f, ""
 print #f, "inputFile$   = chr$(34) + imgDir$ + filename$ + chr$(34)	' Quote the image file paths"
 print #f, "outputFile$  = chr$(34) + imgDir$ + newFilename$ + chr$(34)"
 print #f, ""
 print #f, "' Complete command string"
 print #f, "command$ = magickPath$ + "" convert "" + inputFile$ + "" -resize "" + str$(newWidth) + ""x "" + outputFile$"
 print #f, "x$ = shell$(command$)                       ' shell out image magic command"
 print #f, ""
 print #f, "' Optionally check if file was created"
 print #f, "'open imgDir$ + newFilename$ for binary as #f"
 print #f, "'print #f,  ""Thumbnail created, size = ""; lof(#f); "" bytes"""
 print #f, "'close #f"
end if
 
 print #f, "html ""<script> document.getElementById('photoId').value = '"";photoId$;""';</script>"""
  print #f, "html "";</script>"""
  print #f, "html ""<script> document.getElementById('shoPhoto').innerHTML   = "";chr$(96)"
  print #f, "html ""<IMG SRC='""; imgPath$; photoId$; ""?t=""; str$(int(rnd(1) * 1000000)); ""' height=400>"""
  print #f, "html chr$(96);""</script>"""
  print #f, "RETURN"
end if
print #f, ""
print #f, "' -------------------------------------------"
print #f, "' Export to CSV based on sort and search"
print #f, "' -------------------------------------------"
print #f, "[exportCsv]"
print #f, "q1$	= chr$(34) + "","" + chr$(34)"
print #f, "q2$	= chr$(34)"
print #f, "html ""<script> document.getElementById('infoMsg').innerHTML = '"""
print #f, " a$  = ""Output File: "";DefaultDir$;""\public\";refTbl$;".csv"""
print #f, " a$  = strRep$(a$,""\"",""\\"")"
print #f, " html a$"
print #f, "html ""';</script>"""
print #f, ""
print #f, "#sql execute(sql$)"
print #f, "colNames$ = #sql columnnames$()"
print #f, "colNames$ = strRep$(colNames$,"" "","""")"
print #f, "csvHead$  = q2$ + strRep$(colNames$,"","",q1$) + q2$"
print #f, ""
print #f, "open DefaultDir$ + ""\public\";refTbl$;".csv"" for output as #f"
print #f, ""
print #f, "print #f,csvHead$"
print #f, ""
print #f, "WHILE   #sql hasanswer()"
print #f, "   result$ = #sql nextrow$(q1$)"
print #f, "   print #f,q2$;result$;q2$"
print #f, "WEND"
print #f, "close #f"
print #f, "wait"
print #f, ""
print #f, "' -------------------------------"
print #f, "' Messages and error handling"
print #f, "' -------------------------------"
print #f, "[doMsg]"
print #f, " html ""<script> document.getElementById('infoMsg').innerHTML = '"""
print #f, " br$ = """" "
print #f, " for i = 1 to infoNum"
print #f, "     html br$;infoMsg$(i)"
print #f, "     br$ = ""<BR>"""
print #f, " next i"
print #f, " html ""';</script>"""
print #f, ""
print #f, " html ""<script> document.getElementById('wrnMsg').innerHTML = '"""
print #f, " br$ = """" "
print #f, " for i = 1 to wrnNum"
print #f, "     html br$;wrnMsg$(i)"
print #f, "     br$ = ""<BR>"""
print #f, " next i"
print #f, " html ""';</script>"""
print #f, ""
print #f, " html ""<script> document.getElementById('errMsg').innerHTML = '"""
print #f, " br$ = """" "
print #f, " for i = 1 to errNum"
print #f, "     html br$;errMsg$(i)"
print #f, "     br$ = ""<BR>"""
print #f, " next i"
print #f, " html ""';</script>"""
print #f, "infoNum  = 0"
print #f, "wrnNum   = 0"
print #f, "errNum   = 0"
print #f, "'on error goto [handler]"
print #f, "RETURN"
print #f, ""
print #f, "[handler]"
print #f, "errNum = errNum + 1"
print #f, "errMsg$(errNum) = ""Err number:"";Err;"" Description:"";Err$"
print #f, "gosub [doMsg]"
print #f, "'on error goto [handler1]"
'print #f, " #sql disconnect()"
print #f, "'on error goto [handler]"
print #f, "WAIT"
print #f, "[handler1]"
print #f, "'on error goto [handler]"
print #f, "wait"
print #f, ""
print #f, "' ================================================"
print #f, "'   Signin - set keyUser and keyPass for user/pass"
print #f, "'    Normally done with a db user file"
print #f, "' ================================================"
print #f, "[doLogin]"
print #f, "CLS"
print #f, "loginCanMaint$    = ""N"" "
print #f, "html bf$;""<CENTER><TABLE CELLPADDING=0 CELLSPACING=0 border=1 bgcolor="";clrBkg$;"">"
print #f, "<TR><TD colspan=2 bgcolor="";clrHdr$;"" align=center>LOGIN</TD></TR>"
print #f, "<TR><TD align=right>UserName</TD><TD>"" "
print #f, " TEXTBOX #userName, """" "
print #f, ""
print #f, "html ""</TR></TD><TR><TD align=right>Password:</TD><TD>"""
print #f, "PasswordBox  #passWord, """" "
print #f, ""
print #f, "html ""</TD></TR><TD colspan=2>"""
print #f, "html bf$;""<TABLE BORDER=0 CELLPADDING=0 CELLSPACING=0 WIDTH=100% BGCOLOR="";clrHdr$;""><TR><TD align=center>"""
print #f, "button #si, ""Signin"", [doSignin]"
print #f, "html ""</TD><TD align=center>"""
print #f, "button #ex, ""Exit"", [";refTbl$;"List]"
print #f, "       #ex cssclass(""extBtn"")"
print #f, "html ""</TD></TR></TABLE>"""
print #f, "html ""</TD></TR></TABLE>"""
print #f, "WAIT"
print #f, ""
print #f, "[doSignin]"
print #f, "loginUserName$ = trim$(#userName contents$())"
print #f, "loginPassWord$ = trim$(#passWord contents$())"
print #f, "if (loginUserName$ = keyUser$) and (loginPassWord$ = keyPass$) then"
print #f, "     loginCanMaint$   = ""Y"" "
print #f, " cls"
print #f, " infoNum = infoNum + 1"
print #f, " infoMsg$(infoNum) = ""Maintenance Privliges Granted"" "
print #f, " goto [";refTbl$;"List]"
print #f, " else"
print #f, "      loginCanMaint$  = ""N"" "
print #f, " cls"
print #f, " errNum  = errNum + 1"
print #f, " errMsg$(errNum) = ""Invalid Username Or Password"" "
print #f, " goto [";refTbl$;"List]"
print #f, "end if"
print #f, "wait"
print #f, ""
print #f, "' -----------------------------------------"
print #f, "' Get outta here"
print #f, "' -----------------------------------------"
print #f, "[doExit]"
print #f, ""
print #f, "html ""<script language='javascript' type='text/javascript'>"
print #f, "var a = history.length;"
print #f, "a = a - 1;"
print #f, "window.open('','_parent','');"
print #f, "window.close();"
print #f, "history.go(-a);"
print #f, "</script>"""
print #f, "wait"
print #f, ""

print #f, "' -------------------------------------"
print #f, "' help - please help me I can't get up"
print #f, "' -------------------------------------"
print #f, "[";refTbl$;"doHelp]"
print #f, "cls"
print #f, "numHelp  = 11    ' set number of help items"
print #f, "dim  helpItem$(numHelp)"
print #f, "if helpItem$(1) = """" then"
print #f, "helpItem$(1)  = ""[Add]|Use the [Add] or the [A]dd in the list to add a record"""
print #f, "helpItem$(2)  = ""[Sort]|You can sort in different sequences by selecting the field from the drop down list"""
print #f, "helpItem$(3)  = ""[Search]|This helps you find information. You can use any or all of them at once."""
print #f, "helpItem$(4)  = ""[Exit]|Leave the system"""
print #f, "helpItem$(5)  = ""[Prev]|Go to the previous page"""
print #f, "helpItem$(6)  = ""Page Num|Enter a page number to go directly to that page. Then use [Next]"""
print #f, "helpItem$(7)  = ""[Next]|Go to the next page"""
print #f, "helpItem$(8)  = ""LPP|Set Lines Per Page. The default is 30"""
print #f, "helpItem$(9)  = ""[View]|View record detail"""
print #f, "helpItem$(10) = ""[C]|Change a record"""
print #f, "helpItem$(11) = ""[D]|Delete a record"""
print #f, "end if"
print #f, "html bf$;""<center>"
print #f, "<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>"
print #f, "<TR BGCOLOR="";clrHdr$;"" ALIGN=CENTER>"
print #f, "<TD><B>Item</TD><TD><B>Description</TD></TR>"""
print #f, "for i = 1 to numHelp"
print #f, " hitm$   = word$(helpItem$(i),1,""|"")"
print #f, " hdsc$   = word$(helpItem$(i),2,""|"")"
print #f, " html ""<TR><TD BGCOLOR="";clrHdr$;"" ALIGN=CENTER>"";hitm$;""</TD><TD>"";hdsc$;""</TD></TR>"""
print #f, "next i"
print #f, "html ""<TR><TD BGCOLOR="";clrHdr$;"" ALIGN=CENTER colspan=2>"""
print #f, " button  #ex, ""Return to List"", [";refTbl$;"List]"
print #f, "html ""</TD></TR></TABLE>"""
print #f, ""
print #f, "wait"
print #f, ""
print #f, "' -----------------------------------------"
print #f, "' Convert single quotes to double quotes"
print #f, "' -----------------------------------------"
print #f, "FUNCTION dblQuote$(str$)"
print #f, "i    = 1"
print #f, "qq$  = """" "
print #f, "while (word$(str$,i,""'"")) <> """" "
print #f, " dblQuote$   = dblQuote$;qq$;word$(str$,i,""'"")"
print #f, " qq$ = ""''"" "
print #f, " i   = i + 1"
print #f, "WEND"
print #f, "END FUNCTION"
print #f, ""
print #f, "' --------------------------------"
print #f, "' string replace rep str with"
print #f, "' --------------------------------"
print #f, "FUNCTION strRep$(str$,rep$,with$)"
print #f, "ln  = len(rep$)"
print #f, "ln1 = ln - 1"
print #f, "i = 1"
print #f, "while i <= len(str$)"
print #f, "	if mid$(str$,i,ln) = rep$ then"
print #f, "		strRep$ = strRep$ + with$"
print #f, "		i = i + ln1"
print #f, "		else"
print #f, "		strRep$ = strRep$ + mid$(str$,i,1)"
print #f, "	end if"
print #f, "	i = i + 1"
print #f, "WEND"
print #f, "END FUNCTION"
print #f, ""
if ctlField$ <> "" then
  print #f, ""
  print #f, "' ------------------------------------------------------------------------------------"
  print #f, "' Find available record number"
  print #f, "' This does not always find the next number but finds holes in the numbers and uses it"
  print #f, "' Therefore, you never run out of numbers and you never need to reorganize the files"
  print #f, "' ------------------------------------------------------------------------------------"
  print #f, "FUNCTION useNum(ffile$,ffield$)"
  print #f, "useNum   = 1"
  print #f, "sql$ = ""SELECT a."";ffield$;"" AS aa, c."";ffield$;"" AS cc,a."";ffield$;"" - 1 AS useNum"
  print #f, "FROM "";ffile$;"" AS a"
  print #f, ""
  print #f, "LEFT JOIN "";ffile$;"" as c"
  print #f, "ON    c."";ffield$;"" = a."";ffield$;"" - 1"
  print #f, "WHERE c."";ffield$;"" is null"
  print #f, "AND   a."";ffield$;"" > 1"
  print #f, "ORDER BY a."";ffield$;"" LIMIT 1"""
  print #f, ""
  print #f, "#sql execute(sql$)"
  print #f, "rows =   #sql ROWCOUNT() 'Get the number of rows"
  print #f, "if rows > 0 then"
  print #f, "result$ = #sql nextrow$("" |"")"
  print #f, "aa      = val(word$(result$,1,""|""))"
  print #f, "cc      = val(word$(result$,2,""|""))"
  print #f, "useNum  = val(word$(result$,3,""|""))"
  print #f, " else"
  print #f, " sql$ = ""SELECT max("";ffield$;"") as useNum FROM "";ffile$"
  print #f, " #sql execute(sql$)"
  print #f, " rows    = #sql ROWCOUNT() 'Get the number of rows"
  print #f, " if rows > 0 then"
  print #f, "     result$ = #sql nextrow$("" |"")"
  print #f, "     useNum  = val(word$(result$,1,""|"")) + 1"
  print #f, " end if"
  print #f, "end if"
  print #f, "END FUNCTION"
end if
print #f, ""
print #f, "' -------------------------------------"
print #f, "' Connect to Database"
print #f, "' --------------------------------------"
print #f, "[connectDb]			' Connect to the DB"	
print #f, "sqliteconnect    #sql,  ";refTbl$;"Db$  ' Connect to the DB"
' PRAGMA was used when RB had lock problems.. Probably not needed any longer
'print #f, "#sql  execute(""PRAGMA journal_mode=WAL"")"
'print #f, "#sql  execute(""PRAGMA locking_mode=NORMAL"")"
'print #f, "#sql  execute(""PRAGMA synchronous=0"")"
'print #f, "#sql  execute(""PRAGMA wal_autocheckpoint=0"")"
'print #f, "#sql  execute(""PRAGMA cache_spill=0"")"
'print #f, "#sql  execute(""PRAGMA journal_size_limit=0"")"
print #f, "RETURN"
print #f, ""

if photoId$ <> "" then
print #f, "' -------------------------------------"
print #f, "' Popup for big midea"
print #f, "' -------------------------------------"
print #f, "[popImg]"
print #f, "img$   = imgPath$ + EventKey$  ' Construct the full image path"
print #f, "medId$ = EventKey$"
print #f, "[media1]"
print #f, "bigger = val(EventKey$) ' get media resized"
print #f, "html ""<script> document.getElementById('bigImg').innerHTML = "";chr$(96)"
print #f, "mediaBig$ = ""Y"""
print #f, "gosub [media] ' show big media with controls"
print #f, "bigger = 0"
print #f, "html  chr$(96);""</script>"""
print #f, "html ""<script> document.getElementById('bigExit').innerHTML = '"""
print #f, "button #exBig, ""Close"", [closeBig]"
print #f, "       #exBig cssclass(""extBtn"")"
print #f, "html "" Size->"""
print #f, "button #b, ""-"",  [media1]"
print #f, "       #b setkey(""250"")"
print #f, "button #b0, ""0"", [media1]"
print #f, "       #b0 setkey(""400"")"
print #f, "button #b1, ""1"", [media1]"
print #f, "       #b1 setkey(""500"")"
print #f, "button #b2, ""2"", [media1]"
print #f, "       #b2 setkey(""600"")"
print #f, "button #b3, ""3"", [media1]"
print #f, "       #b3 setkey(""800"")"
print #f, "button #b4, ""4"", [media1]"
print #f, "       #b4 setkey(""1000"")"
print #f, "html ""';</script>"""
print #f, "wait"
print #f, "[closeBig]	' close big image"
print #f, "html ""<script> document.getElementById('bigImg').innerHTML = '';</script>"""
print #f, "html ""<script> document.getElementById('bigExit').innerHTML = '';</script>"""
print #f, "wait"
print #f, ""
print #f, "' ---------------------------------------"
print #f, "' media for video,photos, music"
print #f, "'       shows media with interface"
print #f, "' supply "
print #f, "' medId$    - the id of the media"
print #f, "' impPath$  - the directory path where"
print #f, "'            media is located"
print #f, "' mediaBig$ - ""Y"" for big media display"
print #f, "' ---------------------------------------"
print #f, "[media]"           
print #f, "if medId$ = """" then"
print #f, " html ""None"""
print #f, " RETURN"
print #f, "end if"
print #f, "b = 80"
print #f, "if mediaBig$ = ""Y"" then b = 400"
print #f, "if bigger <> 0 then b = bigger"
print #f, "img$ = imgPath$;medId$"
print #f, "ext$ = upper$(word$(medId$,2,"".""))"
print #f, "if instr(""JPEG,JPG,PNG,GIF,SVG,WEBP,AVIF,BMP,ICO,APNG,SVG"",ext$) > 0 then ext$ = ""JPG"""
print #f, "select case ext$"
print #f, "case ""JPG"""
print #f, "     html ""<IMG SRC='"";img$;""' height="";b;"">"""
print #f, "case ""MP3"""
print #f, "     html ""<audio controls style='width:100%; height:"";b;"";' preload='none'><source src='"";img$;""' type='audio/mp3'>Not Supported</audio>"""
print #f, "case ""WAV"""
print #f, "     html ""<audio controls style='width:100%; height:"";b;"";' preload='none'><source src='"";img$;""' type='audio/wav'>Not Supported</audio>"""
print #f, "case ""OGG""" 
print #f, "     html ""<audio controls style='width:100%; height:"";b;"";' preload='none'><source src='"";img$;""' type='audio/ogg'>Not Supported</audio>"""
print #f, "case ""MPG""" 
print #f, "     html ""<video controls height="";b;"" preload='none'><source src='"";img$;""' type='video/mpeg'>Not Supported</video>"""
print #f, "case ""MP4""" 
print #f, "     html ""<video controls height="";b;"" preload='none'><source src='"";img$;""' type='video/mp4'>Not Supported</video>"""
print #f, "case ""WEBM"""
print #f, "     html ""<video controls height="";b;"" preload='none'><source src='"";img$;""' type='video/webm'>Not Supported</video>"""
print #f, "case ""PDF"""
print #f, "     if b = 0 then"
print #f, "       html ""<embed src='"";img$;""' height=70 width=80px type='application/pdf'>"""
print #f, "     else"
print #f, "       html ""<embed src='"";img$;""' height="";b;"" width=100% type='application/pdf'>"""
print #f, "     end if"
print #f, "case ""TXT"""
print #f, "	html ""<iframe src='"";img$;""' height="";b;"" width=100%;></iframe>"""
print #f, "case ""HTML"""
print #f, "	html ""<iframe src='"";img$;""' height="";b;"" width=100%;></iframe>"""
print #f, "case ""HTM"""
print #f, "	html ""<iframe src='"";img$;""' height="";b;"" width=100%;></iframe>"""
print #f, "case else"
print #f, "     html ""Unknown ext:"";ext$"
print #f, "end select"
print #f, "RETURN"
end if
print #f, ""
print #f, "' -------------------------------------"
print #f, "' set CSS"
print #f, "' --------------------------------------"
print #f, "SUB SetCSS"
print #f, ""
print #f, "CSSClass ""a.hdr"", ""{"
print #f, "    text-decoration: none;"
print #f, "    font-family: Arial, sans-serif;"
print #f, "    font-size: 15px;"
print #f, "    font-weight: bold;"
print #f, "    background-color: wheat;"
print #f, "    color: black;"
print #f, "    transition: background-color 0.3s ease;"
print #f, "}"" "
print #f, ""
print #f, "CSSClass ""a.hdr:hover"", ""{"
print #f, "    background-color: green;"
print #f, "    color: red;"
print #f, "}"""
print #f, ""
print #f, "CSSClass ""a.lBtn"", ""{"
print #f, "Text-Align:Center;"
print #f, "Border-Width:1px;"
print #f, "Border-Style:solid;"
print #f, "background:#FDDD8C;"
print #f, "Border-Color:black;"
print #f, "Font-Size:10pt;"
print #f, "Font-Weight:Bold;"
print #f, "Font-Family:  Arial;"
print #f, "Text-Decoration:  None;"
print #f, "}"" "
print #f, "CSSClass ""a.sortHdr1"", ""{"
print #f, "Text-Decoration: None;"
print #f, "Color: red;"
print #f, "}"""
print #f, "CSSClass ""caption"",""{background-color:"";clrHdr$;""color:black;border:2px solid #000;font-weight: bold;}"""
print #f, "CSSClass "".extBtn"", ""{background-color:pink;}"""
print #f, "END SUB"
close #f
wait

' -------------------------------------------------------
' get table info
' supply stbl$ = table name
' returns
' -------------------------------------------------------
[getTblInfo]
snumFields    = 0
scolNames$    = ""
stblColNames$ = ""
scolTypes$    = ""
scolSizes$    = ""

sqliteconnect   #sql, dbFile$            ' Connect to the DB
sql$ = "pragma table_info("+stbl$+")"
#sql execute(sql$)
snumFields = #sql ROWCOUNT()
dim scolName$(snumFields +1)
dim scolType$(snumFields +1)
dim scolSize$(snumFields +1)

for i = 1 to snumFields
  result$       = #sql nextrow$(" |")
  thisCol$      = word$(result$,2," |")
  scolName$(i)  = thisCol$
  scolNames$    = scolNames$ + thisCol$ + ","
  stblColNames$ = stblColNames$ + tbl$ + "." + thisCol$ + ","
  a$            = word$(result$,3," |") + "( )"
  scolType$(i)  = word$(a$,1,"(")
  a$            = word$(a$,2,"(")
  a$            = word$(a$,1,")")
  scolSize$(i)  = strRep$(a$,",",".")
  ' print i;" ";result$;"->";scolName$(i);":";scolType$(i);":";scolSize$(i)
next i

if ctlField$ = "rowid" then
  snumFields = snumFields +1
  scolName$(snumFields)  = "rowid"
  scolType$(snumFields)  = "int"
  scolSize$(snumFields)  = "6"
end if

RETURN

' -------------------------------------------
' need fld$
' returns thisTypeVal$  [I]nteger or [C]har
' -------------------------------------------
[getTypeVal]
bField$	= " " + trim$(fld$) + " "
t3$     = word$(t2$,2,bField$)
t3$	= word$(t3$,1,",")
t4$	= word$(t3$,2,"(")
thisLen = val(word$(t4$,1,")"))
thisLen = min(thisLen,60)

'print fld$;" t3:";t3$;" t4:";t4$;" thisLen:";thisLen
if thisLen = 0 then
  thisLen$ = "16"
  else
  thisLen$ = str$(thisLen)
end if
thisType$   = upper$(word$(t3$,1," "))
thisType$   = word$(thisType$,1,")")
thisTypeVal$    = ""
'print "thisType:";thisType$;" thisTypeVal:";thisTypeVal$
for ii = 1 to 28
  if thisType$      = type$(ii)    then
    thisTypeVal$    = typeVal$(ii)
    goto [getTypeEnd]
  end if
next ii

[getTypeEnd]
if thisTypeVal$ = "DT" then thisLen$ = "19"
if thisTypeVal$ = "D"  then thisLen$ = "17"
if thisTypeVal$ = "T"  then thisLen$ = "200"
if thisTypeVal$ = "TI" then thisLen$ = "8"
thisLen = val(thisLen$)
'print "================= t3:";bField$;"|";t3$;"|len:";thisLen$;" thisTyp:";thisType$;" typ:";thisTypeVal$
RETURN

' --------------------------------
' string replace rep str with
' --------------------------------
FUNCTION strRep$(str$,rep$,with$)
ln  = len(rep$)
ln1 = ln - 1
i = 1
while i <= len(str$)
  if mid$(str$,i,ln) = rep$ then
    strRep$ = strRep$ + with$
    i = i + ln1
    else
    strRep$ = strRep$ + mid$(str$,i,1)
  end if
  i = i + 1
WEND
END FUNCTION

' -----------------------------------------
' strip junk 
' -----------------------------------------
FUNCTION strip$(str$)
strip$ = ""
for i = 1 to len(str$)
  a$ = MID$(str$,i,1)
  a = ASC(a$)
  if a > 31 then
    if a <  127 then
      if a$ <> "'" then
        if a$ <> """" then
          strip$ = strip$ + a$
        end if
      end if
    end if
  end if
next i
END FUNCTION

' -----------------------------------------------------------
' break on Caps.. ItemNum becomes 2 lines of Item Num
' -----------------------------------------------------------
FUNCTION hdrBreak$(a$)
for i = 1 to len(a$) 
  a1$  = mid$(a$,i,1)
  a2$  = mid$(a$,i+1,1)
  a11$ = lower$(a1$)
  a22$ = lower$(a2$)
  hdrBreak$   = hdrBreak$ + a1$
  if a22$ <> a2$ and a11$ = a1$ then hdrBreak$ = hdrBreak$ + """+cr$+"""
next i
END FUNCTION

[sqlErr]
errNum  = errNum + 1
errMsg$(errNum) = "sql Error:";sql$
goto [main]
wait

' -----------------------------
' Error Handler
' -----------------------------
[errFile]
errNum          = errNum + 1
errMsg$(errNum) = "Data base master file error:";sqliteDb$
gosub [doMsg]
wait
[errSQL1]
errNum          = errNum + 1
errMsg$(errNum) = "sqlError:";sql1$
gosub [doMsg]
#sql1 disconnect()
wait

[errDbFile]
errNum  = errNum + 1
errMsg$(errNum) = "File:";dbFile$;" does not exist"
gosub [doMsg]
wait

[doMsg]
if infoNum > 0 then
  html "<script> document.getElementById('infoMsg').innerHTML = '"
  br$ = ""
  for i = 1 to infoNum
  html br$;bf$;infoMsg$(i)
  br$ = "<BR>"
  next i
  html "';</script>"
end if

if wrnNum > 0 then
  html "<script> document.getElementById('wrnMsg').innerHTML = '"
  br$ = ""
  for i = 1 to wrnNum
  html br$;bf$;wrnMsg$(i)
  br$ = "<BR>"
  next i
  html "';</script>"
end if

if errNum > 0 then
  html "<script> document.getElementById('errMsg').innerHTML = '"
  br$ = ""
  for i = 1 to errNum
  html br$;bw$;errMsg$(i)
  br$ = "<BR>"
  next i
  html "';</script>"
end if
infoNum = 0
wrnNum  = 0
errNum  = 0
'on error goto [handler]
RETURN

[handler]
html "<script> document.getElementById('errMsg').innerHTML = '"
html "Error msg:";Err$
html "<BR>Error Number:";Err
html "';</script>"
'on error goto [handler]
WAIT

' -----------------------------------------
' Get outta here
' -----------------------------------------
[doExit]
html "<script language='javascript' type='text/javascript'>
var a = history.length;
a = a - 1;
window.open('','_parent','');
window.close();
history.go(-a);
</script>"