B4J=true
Group=Default Group
ModulesStructureVersion=1
Type=Class
Version=9.8
@EndOfDesignText@
#IgnoreWarnings:12
'
Private Sub Class_Globals
	Public const DB_BOOL As String = "BOOL"
	Public const DB_INT As String = "INT"
	Public const DB_REAL As String = "REAL"
	Public const DB_FLOAT As String = "FLOAT"
	Public const DB_INTEGER As String = "INTEGER"
	Public const DB_TEXT As String = "TEXT"
	Public const DB_DOUBLE As String = "DOUBLE"
	Public const DB_NUMBER As String = "NUMBER"
	Public const DB_DATE As String = "DATE"
	'
	Private mCallBack As Object			'ignore
	Private whereField As Map
	Private ops As List
	Private orderByL As List
	Private flds As List
	Public RowCount As Int
	Private lastPosition As Int
	Public Record As Map
	Public result As List
	Private BANano As BANano 'ignore
	Private mEvent As String		'ignore
	Public TableName As String
	Public Tag As Object
	Public Schema As Map
	Public PrimaryKey As String
	Public DisplayField As String
	Public Singular As String
	Public Plural As String
	Public DisplayValue As String
	Public IsAuthenticated As Boolean
	Private combineL As List
	Public ShowLog As Boolean
	Private baseURL As String
	Public AutoIncrement As String
	'
	Public OPERATOR_CONTAINS As String = "cs"
	Public OPERATOR_NOT_CONTAINS As String = "ncs"
	Public OPERATOR_STARTSWITH As String = "sw"
	Public OPERATOR_NOT_STARTSWITH As String = "nsw"
	Public OPERATOR_ENDS_WITH As String = "ew"
	Public OPERATOR_NOT_ENDS_WITH As String = "new"
	Public OPERATOR_EQ As String = "eq"
	Public OPERATOR_NEQ As String = "neq"
	Public OPERATOR_LT As String = "lt"
	Public OPERATOR_NLT As String = "nlt"
	Public OPERATOR_LTE As String = "le"
	Public OPERATOR_NOT_LTE As String = "nle"
	Public OPERATOR_GTE As String = "ge"
	Public OPERATOR_NGTE As String = "nge"
	Public OPERATOR_GT As String = "gt"
	Public OPERATOR_NGT As String = "ngt"
	Public OPERATOR_BETWEEN As String = "bt"
	Public OPERATOR_NBETWEEN As String = "nbt"
	Public OPERATOR_IN As String = "in"
	Public OPERATOR_NOT_IN As String = "nin"
	Public OPERATOR_ISNULL As String = "is"
	Public OPERATOR_NOT_ISNULL As String = "nis"
	Public OPERATOR_LIKE As String = "like"
	Public OPERATOR_BETWEEN As String = "between"
End Sub

'<code>
''initialize the connection to pocketbase with a collection to access
'Dim pb As SDUIMySQLAPI
'pb.Initialize(Me, "pb", "https://localhost/sdmysqlapi/assets/api.php", "projects")
'pb.SchemaAddText(Array("id", "name"))
'pb.PrimaryKey = "id"
'pb.AutoIncrement = "id"
'</code>
Public Sub Initialize(Module As Object, eventName As String, url As String, sTableName As String) As SDUIMySQLAPI
	TableName = sTableName
	mCallBack = Module
	whereField.Initialize
	ops.Initialize
	orderByL.Initialize
	flds.Initialize
	Record.Initialize
	lastPosition = -1
	result.Initialize
	mEvent = eventName
	PrimaryKey = "id"
	AutoIncrement = ""
	Schema.Initialize
	combineL.Initialize
	ShowLog = False
	baseURL = url
	mEvent = eventName
	Return Me
End Sub

'schema add boolean
Sub SchemaAddBoolean(bools As List) As SDUIMySQLAPI
	For Each b As String In bools
		Schema.Put(b, DB_BOOL)
	Next
	Return Me
End Sub
Sub SchemaAddBoolean1(b As String) As SDUIMySQLAPI
	Schema.Put(b, DB_BOOL)
	Return Me
End Sub
Sub SchemaAddDouble1(b As String) As SDUIMySQLAPI
	Schema.Put(b, DB_DOUBLE)
	Return Me
End Sub
'add double fields
Sub SchemaAddDouble(bools As List) As SDUIMySQLAPI
	For Each b As String In bools
		Schema.Put(b, DB_DOUBLE)
	Next
	Return Me
End Sub
Sub SchemaAddFloat1(b As String) As SDUIMySQLAPI
	Schema.Put(b, DB_FLOAT)
	Return Me
End Sub
Sub SchemaAddFloat(bools As List) As SDUIMySQLAPI
	For Each b As String In bools
		Schema.Put(b, DB_FLOAT)
	Next
	Return Me
End Sub
Sub SchemaAddText1(b As String) As SDUIMySQLAPI
	Schema.Put(b, DB_TEXT)
	Return Me
End Sub
Sub SchemaAddText(bools As List) As SDUIMySQLAPI
	For Each b As String In bools
		Schema.Put(b, DB_TEXT)
	Next
	Return Me
End Sub
Sub SchemaAddInt1(b As String) As SDUIMySQLAPI
	Schema.Put(b, DB_INT)
	Return Me
End Sub
Sub SchemaAddInt(bools As List) As SDUIMySQLAPI
	For Each b As String In bools
		Schema.Put(b, DB_INT)
	Next
	Return Me
End Sub

Sub SchemaAddNumber(bools As List) As SDUIMySQLAPI
	For Each b As String In bools
		Schema.Put(b, DB_NUMBER)
	Next
	Return Me
End Sub

Sub SchemaAddNumber1(b As String) As SDUIMySQLAPI
	Schema.Put(b, DB_NUMBER)
	Return Me
End Sub

'get records from a collection
'<code>
'BANano.Await(pb.SELECT_ALL)
'Do While pb.NextRow
'Dim rec As Map = pb.Record
'Dim sid As String = pb.GetString("id")
'Loop
'</code>
Sub SELECT_ALL As List
	result.Initialize
	If ShowLog Then
		Log($"SDUIMySQLAPI.SELECT_ALL"$)
	End If
	If Schema.Size = 0 Then
		BANano.Throw($"SDUIMySQLAPI.SELECT_ALL. Please execute SchemaAdd?? first to define your table schema!"$)
		Return result
	End If
	'
	Dim qflds As String = SDUIShared.Join("&", flds)
	qflds = SDUIShared.RemDelim(qflds, "&")
	
	Dim qsort As String = SDUIShared.Join("&", orderByL)
	qsort = SDUIShared.RemDelim(qsort, "&")
	'
	Dim xcommand As List
	xcommand.Initialize
	If qflds <> "" Then xcommand.add(qflds)
	If qsort <> "" Then xcommand.Add(qsort)
	'
	Dim scommand As String = SDUIShared.Join("&", xcommand)
	scommand = SDUIShared.RemDelim(scommand, "&")
	
	lastPosition = -1
	Dim fetch As SDUIFetch
	fetch.Initialize(baseURL)
	If scommand = "" Then
		fetch.SetURL($"/records/${TableName}"$)
	Else
		fetch.SetURL($"/records/${TableName}?${scommand}"$)
		If ShowLog Then
			Log($"SDUIMySQLAPI.SELECT_ALL(${scommand})"$)
		End If
	End If
	BANano.Await(fetch.GetWait)
	If fetch.Success Then
		Dim Response As Map = fetch.response
		If Response.ContainsKey("records") Then
			result = Response.Get("records")
		End If
	End If
	RowCount = result.size
	Return result
End Sub

'record is saved as json string
'returns the record id
'<code>
'pb.PrepareRecord
'pb.SetField("id", 1)
'pb.SetField("username", "user1")
'BANano.Await(pb.CREATE)
'</code>
Sub CREATE As String
	If ShowLog Then
		Log($"SDUIMySQLAPI.CREATE"$)
	End If
	If Schema.Size = 0 Then
		BANano.Throw($"SDUIMySQLAPI.CREATE. Please execute SchemaAdd?? first to define your table schema!"$)
		Return ""
	End If
	Try
		Dim output As Object
		Dim fetch As SDUIFetch
		fetch.Initialize(baseURL)
		fetch.SetURL($"/records/${TableName}"$)
		fetch.SetData(Record)
		BANano.Await(fetch.PostWait)
		If fetch.Success Then
			RowCount = 1
			Dim Response As Map = fetch.response
			output = Response
		End If
		Return output
	Catch
		Return ""
	End Try
End Sub

'Removes the key and value mapped to this key.
'<code>
'Dim b As Boolean = BANano.Await(pb.DELETE("a"))
'</code>
Sub DELETE(id As String) As Boolean
	If ShowLog Then
		Log($"SDUIMySQLAPI.DELETE(${id})"$)
	End If
	If Schema.Size = 0 Then
		BANano.Throw($"SDUIMySQLAPI.DELETE. Please execute SchemaAdd?? first to define your table schema!"$)
		Return False
	End If
	Try
		Dim output As Object
		Dim fetch As SDUIFetch
		fetch.Initialize(baseURL)
		fetch.SetURL($"/records/${TableName}/${id}"$)
		BANano.Await(fetch.DeleteWait)
		If fetch.Success Then
			Dim Response As Map = fetch.response
			output = Response
			RowCount = SDUIShared.CInt(output)
			Return True
		Else
			Return False
		End If
	Catch
		Return False
	End Try
End Sub


'record is saved as json string
'returns the record id
'<code>
'pb.PrepareRecord
'pb.SetField("id", 1)
'pb.SetField("username", "user1")
'BANano.Await(pb.UPDATE)
'</code>
Sub UPDATE As String
	If ShowLog Then
		Log($"SDUIMySQLAPI.UPDATE"$)
	End If
	If Schema.Size = 0 Then
		BANano.Throw($"SDUIMySQLAPI.UPDATE. Please execute SchemaAdd?? first to define your table schema!"$)
		Return ""
	End If
	Try
		'update an existing record by primary key
		Dim pkValue As String = Record.GetDefault(PrimaryKey, "")
		If pkValue = "" Then
			Return ""
		End If
		Dim output As Object
		Dim fetch As SDUIFetch
		fetch.Initialize(baseURL)
		'this is a post
		fetch.SetURL($"/records/${TableName}/${pkValue}"$)
		fetch.SetData(Record)
		BANano.Await(fetch.PutWait)
		If fetch.Success Then
			Dim Response As Map = fetch.response
			output = Response
			RowCount = SDUIShared.CInt(output)
		End If
		Return RowCount
	Catch
		Return ""
	End Try
End Sub

'get a value using key
'<code>
'Dim res As Map = BANano.Await(pb.READ(2))
'</code>
Sub READ(id As String) As Map
	Dim m As Map = CreateMap()
	Record = m
	lastPosition = -1
	If ShowLog Then
		Log($"SDUIMySQLAPI.READ(${id})"$)
	End If
	If Schema.Size = 0 Then
		BANano.Throw($"SDUIMySQLAPI.READ. Please execute SchemaAdd?? first to define your table schema!"$)
		Return m
	End If
	Dim m As Map = CreateMap()
	Try
		'this is a get
		Dim fetch As SDUIFetch
		fetch.Initialize(baseURL)
		fetch.SetURL($"/records/${TableName}/${id}"$)
		BANano.Await(fetch.GetWait)
		If fetch.Success Then
			Dim Response As Map = fetch.response
			Log(Response)
			RowCount = result.size
			Record = Response
			result.Add(Record)
		End If
		Return Record
	Catch
		Record = m
		Return m
	End Try
End Sub

'<code>
'Dim result As Map = BANano.Await(pbComponents.READ_BY("name", "Anele"))
'</code>
Sub READ_BY(fldName As String, fldValue As Object) As Map
	If ShowLog Then
		Log($"SDUIMySQLAPI.READ_BY(${fldName},${fldValue})"$)
	End If
	CLEAR_WHERE
	ADD_WHERE(fldName, "=", fldValue)
	Dim res As List = BANano.Await(SELECT_WHERE)
	Dim m As Map = CreateMap()
	If res.size = 0 Then
		Return m
	Else
		m = res.Get(0)
		Return m
	End If
End Sub

'Tests whether a key is available in the store.
'<code>
'Dim res as boolean = BANano.Await(pb.ContainsKey(2))
'</code>
Sub ContainsKey(id As String) As Boolean
	If ShowLog Then
		Log($"SDUIMySQLAPI.ContainsKey(${id})"$)
	End If
	If Schema.Size = 0 Then
		BANano.Throw($"SDUIMySQLAPI.ContainsKey. Please execute SchemaAdd?? first to define your table schema!"$)
		Return ""
	End If
	CLEAR_WHERE
	ADD_WHERE(PrimaryKey, "=", id)
	Dim res As List = BANano.Await(SELECT_WHERE)
	If res.Size = 0 Then
		Return False
	Else
		Return True
	End If
End Sub

'<code>
'Dim res as boolean = BANano.Await(pb.ContainsField("name", "Anele"))
'</code>
Sub ContainsField(fldName As String, id As String) As Boolean
	If ShowLog Then
		Log($"SDUIMySQLAPI.ContainsField(${fldName},${id})"$)
	End If
	If Schema.Size = 0 Then
		BANano.Throw($"SDUIMySQLAPI.ContainsField. Please execute SchemaAdd?? first to define your table schema!"$)
		Return ""
	End If
	CLEAR_WHERE
	ADD_WHERE(fldName, "=", id)
	Dim res As List = BANano.Await(SELECT_WHERE)
	If res.Size = 0 Then
		Return False
	Else
		Return True
	End If
End Sub

'Returns a list with all the keys.
'<code>
'Dim res As List = BANano.Await(pb.ListKeys)
'</code>
Public Sub ListKeys As List
	If ShowLog Then
		Log($"SDUIMySQLAPI.ListKeys"$)
	End If
	If Schema.Size = 0 Then
		BANano.Throw($"SDUIMySQLAPI.ListKeys. Please execute SchemaAdd?? first to define your table schema!"$)
		Return Null
	End If
	CLEAR_WHERE
	result = BANano.Await(SELECT_WHERE)
	Dim res As List
	res.Initialize
	For Each rec As Map In result
		Dim k As String = rec.GetDefault(PrimaryKey, "")
		k = SDUIShared.CStr(k)
		res.Add(k)
	Next
	Return res
End Sub

'<code>
'BANano.Await(pb.DELETE_ALL)
'</code>
Sub DELETE_ALL
	If ShowLog Then
		Log($"SDUIMySQLAPI.DELETE_ALL"$)
	End If
	Dim keys As List = BANano.Await(ListKeys)
	If keys.size = 0 Then Return
	For Each k As String In keys
		BANano.Await(DELETE(k))
	Next
End Sub

'get keyvalues for combos etc
'<code>
'Dim mExpenseCategories As Map = banano.Await(pb.GetKeyValues(True, "id", "name"))
'</code>
Sub GetKeyValues(bHasNothing As Boolean, k As String, v As String) As Map
	If ShowLog Then
		Log($"SDUIMySQLAPI.GetKeyValues(${bHasNothing},${k},${v})"$)
	End If
	Dim mx As Map = CreateMap()
	If bHasNothing Then
		mx.Put("", "-- Nothing Selected --")
	End If
	CLEAR_WHERE
	ADD_FIELD(k)
	ADD_FIELD(v)
	ADD_ORDER_BY(v)
	result = BANano.Await(SELECT_WHERE)
	For Each row As Map In result
		Dim f1 As String = row.get(k)
		Dim f2 As String = row.get(v)
		mx.Put(f1, f2)
	Next
	Return mx
End Sub

'record is saved as json string
'returns the record id
'<code>
'BANano.Await(pb.CREATE_OR_UPDATE)
'</code>
Sub CREATE_OR_UPDATE As String
	If ShowLog Then
		Log($"SDUIMySQLAPI.CREATE_OR_UPDATE"$)
	End If
	If Schema.Size = 0 Then
		BANano.Throw($"SDUIMySQLAPI.CREATE_OR_UPDATE. Please execute SchemaAdd?? first to define your table schema!"$)
		Return ""
	End If
	Try
		'does the record exist
		Dim Key As String = Record.GetDefault(PrimaryKey, "")
		DisplayValue = Record.GetDEfault(DisplayField, "")
		'this will be blank if we are using PB own keys
		'if we use our keys, those need to be 15 in length
		Dim bThere As Boolean = False
		If Key <> "" Then
			bThere = BANano.Await(ContainsKey(Key))
			If ShowLog Then
				Log($"SDUIMySQLAPI.CREATE_OR_UPDATE:ContainsKey(id,${Key},${bThere})"$)
			End If
		End If
		'does not exit
		If bThere = False Then
			Dim ar As String = BANano.Await(CREATE)
			Return ar
		Else
			Dim ar As String = BANano.Await(UPDATE)
			Return ar
		End If
	Catch
		Return ""
	End Try			' ignore
End Sub

'record is saved as json string
'returns the record id
'<code>
'BANano.Await(pb.CREATE_OR_UPDATE_BY("email"))
'</code>
Sub CREATE_OR_UPDATE_BY(fldName As String) As String
	If ShowLog Then
		Log($"SDUIMySQLAPI.CREATE_OR_UPDATE_BY_FIELD(${fldName})"$)
	End If
	If Schema.Size = 0 Then
		BANano.Throw($"SDUIMySQLAPI.CREATE_OR_UPDATE_BY_FIELD. Please execute SchemaAdd?? first to define your table schema!"$)
		Return ""
	End If
	Try
		'does the record exist
		Dim sKey As String = Record.GetDefault(fldName, "")
		DisplayValue = Record.GetDEfault(DisplayField, "")
		'this will be blank if we are using PB own keys
		'if we use our keys, those need to be 15 in length
		Dim bThere As Boolean = False
		Dim id As String = ""
		If sKey <> "" Then
			Dim rec As Map = BANano.Await(READ_BY(fldName, sKey))
			id = rec.GetDefault(PrimaryKey, "")
			If id <> "" Then bThere = True
		End If
		'does not exit
		If bThere = False Then
			Dim ar As String = BANano.Await(CREATE)
			Return ar
		Else
			'execute an update
			Record.Put(PrimaryKey, id)
			Dim ar As String = BANano.Await(UPDATE)
			Return ar
		End If
	Catch
		Return ""
	End Try			' ignore
End Sub

Sub GET_ID_BY_FIELD(fldName As String, fldValue As String) As String
	If ShowLog Then
		Log($"SDUIMySQLAPI.GET_ID_BY_FIELD(${fldName},${fldValue})"$)
	End If
	Dim rec As Map = BANano.Await(READ_BY(fldName, fldValue))
	Dim sid As String = rec.GetDefault(PrimaryKey, "")
	Return sid
End Sub

Sub UPDATE_BY(fldName As String, fldValue As String) As String
	If ShowLog Then
		Log($"SDUIMySQLAPI.UPDATE_BY(${fldName},${fldValue})"$)
	End If
	Dim rec As Map = BANano.Await(READ_BY(fldName, fldValue))
	Dim sid As String = rec.GetDefault(PrimaryKey, "")
	Record.Put(PrimaryKey, sid)
	Dim nid As String = BANano.Await(UPDATE)
	Return nid
End Sub

'to delete the file set the fldValue = null
Sub SetField(fldName As String, fldValue As Object) As SDUIMySQLAPI
	If ShowLog Then
		Log($"SDUIMySQLAPI.SetField(${fldName}, ${fldValue})"$)
	End If
	Dim dt As String = Schema.Get(fldName)
	Select Case dt
		Case DB_BOOL
			fldValue = SDUIShared.CBool(fldValue)
		Case DB_INT
			fldValue = SDUIShared.CInt(fldValue)
		Case DB_REAL
			fldValue = SDUIShared.CDbl(fldValue)
		Case DB_FLOAT
			fldValue = SDUIShared.CDbl(fldValue)
		Case DB_INTEGER
			fldValue = SDUIShared.CInt(fldValue)
		Case DB_TEXT
			fldValue = SDUIShared.CStr(fldValue)
		Case DB_DOUBLE
			fldValue = SDUIShared.CDbl(fldValue)
	End Select
	Record.Put(fldName, fldValue)
	Return Me
End Sub

Sub FirstRecord As Map
	Dim rec As Map = result.Get(0)
	Return rec
End Sub

'clear where clause
Sub CLEAR_WHERE As SDUIMySQLAPI
	If ShowLog Then
		Log($"SDUIMySQLAPI.CLEAR_WHERE"$)
	End If
	whereField.Initialize
	ops.Initialize
	orderByL.Initialize
	flds.Initialize
	combineL.Initialize
	Return Me
End Sub

Sub Reset As SDUIMySQLAPI
	whereField.Initialize
	ops.Initialize
	orderByL.Initialize
	flds.Initialize
	combineL.Initialize
	RowCount = 0
	lastPosition = -1
	Record.Initialize
	result.Initialize
	Tag = Null
	Schema.Initialize
	DisplayField = ""
	Singular = ""
	Plural = ""
	DisplayValue = ""
	IsAuthenticated = False
	PrimaryKey = ""
	AutoIncrement = ""
	Return Me
End Sub

Sub ADD_FIELD(fld As String) As SDUIMySQLAPI
	flds.Add($"include=${fld}"$)
	Return Me
End Sub

Sub ADD_FIELDS(fields As List) As SDUIMySQLAPI
	For Each fld As String In fields
		ADD_FIELD(fld)
	Next
	Return Me
End Sub

'add a where clause for your select where
'use operators pb.operator
Sub ADD_WHERE(fld As String, operator As String, value As Object) As SDUIMySQLAPI
	If ShowLog Then
		Log($"SDUIMySQLAPI.ADD_WHERE(${fld},${operator},${value})"$)
	End If
	Select Case operator
	Case ">"
		operator = "gt"
	Case ">="
		operator = "ge"
	Case "<"
		operator = "lt"
	Case "<="	
		operator = "le"
	Case "="
		operator = "eq"
	Case "<>"
		operator = "neq"
	Case "like"
		operator = "cs"
	Case "between"
		operator = "bt"
	Case "in"
		operator = "in"
	End Select
	whereField.Put(fld, value)
	ops.Add(operator)
	combineL.Add("")
	Return Me
End Sub

'change the selection at position from AND to OR
Sub WHERE_OR(pos As Int) As SDUIMySQLAPI
	If ShowLog Then
		Log($"SDUIMySQLAPI.WHERE_OR(${pos})"$)
	End If
	combineL.Set(pos, "2")
	Return Me
End Sub

'change the selection at position to AND
Sub WHERE_AND(pos As Int) As SDUIMySQLAPI
	If ShowLog Then
		Log($"SDUIMySQLAPI.WHERE_AND(${pos})"$)
	End If
	combineL.Set(pos, "")
	Return Me
End Sub

'add a sort field
Sub ADD_ORDER_BY(fld As String) As SDUIMySQLAPI
	If ShowLog Then
		Log($"SDUIMySQLAPI.ADD_ORDER_BY(${fld})"$)
	End If
	orderByL.Add($"order=${fld}"$)
	Return Me
End Sub

Sub ADD_ORDER_BY_DESC(fld As String) As SDUIMySQLAPI
	If ShowLog Then
		Log($"SDUIMySQLAPI.ADD_ORDER_BY_DESC(${fld})"$)
	End If
	orderByL.Add($"order=${fld},desc"$)
	Return Me
End Sub

'<code>
'Do while rs.NextRow
'Loop
'</code>
Sub NextRow As Boolean
	lastPosition = lastPosition + 1
	Dim realSize As Int = RowCount - 1
	If lastPosition > realSize Then
		Return False
	Else
		setPosition(lastPosition)
		Return True
	End If
End Sub

Sub SetRecord(rec As Map)
	Record.Initialize
	'only process schema fields
	For Each k As String In rec.Keys
		Dim fldPos As Boolean = Schema.ContainsKey(k)
		If fldPos = False Then rec.Remove(k)
	Next
	For Each k As String In rec.Keys
		Dim v As Object = rec.GetDefault(k, "")
		Dim dt As String = Schema.Get(k)
		Select Case dt
			Case DB_BOOL
				v = SDUIShared.CBool(v)
			Case DB_INT
				v = SDUIShared.CInt(v)
			Case DB_REAL
				v = SDUIShared.CDbl(v)
			Case DB_FLOAT
				v = SDUIShared.CDbl(v)
			Case DB_INTEGER
				v = SDUIShared.CInt(v)
			Case DB_TEXT
				v = SDUIShared.CStr(v)
			Case DB_DOUBLE
				v = SDUIShared.CDbl(v)
		End Select
		Record.Put(k, v)
	Next
	DisplayValue = Record.GetDEfault(DisplayField, "")
	If ShowLog Then
		Log($"SDUIMySQLAPI.SetRecord(${BANano.ToJson(Record)})"$)
	End If
End Sub

Sub DELETE_BY(fldName As String, fldValue As String)
	If ShowLog Then
		Log($"SDUIMySQLAPI.DELETE_BY(${fldName},${fldValue})"$)
	End If
	CLEAR_WHERE
	ADD_WHERE(fldName, "=", fldValue)
	BANano.Await(deleteWhere(PrimaryKey, whereField, ops))
End Sub

Sub DELETE_WHERE
	If ShowLog Then
		Log($"SDUIMySQLAPI.DELETE_WHERE"$)
	End If
	BANano.Await(deleteWhere(PrimaryKey, whereField, ops))
End Sub

'<code>
'Dim result As Boolean = BANano.Await(pbComponents.Exists("id", 1))
'</code>
Sub Exists(fldName As String, fldValue As Object) As Boolean
	If ShowLog Then
		Log($"SDUIMySQLAPI.Exists(${fldName},${fldValue})"$)
	End If
	CLEAR_WHERE
	ADD_WHERE(fldName, "=", fldValue)
	Dim res As List = BANano.Await(SELECT_WHERE)
	If res.size = 0 Then
		Return False
	Else
		Return True
	End If
End Sub

'<code>
'Dim result As Boolean = BANano.Await(pbComponents.Exists("id", 1))
'</code>
Sub ExistsWhere(where As Map) As Boolean
	If ShowLog Then
		Log($"SDUIMySQLAPI.ExistsWhere(${BANano.ToJson(where)})"$)
	End If
	CLEAR_WHERE
	For Each k As String In where.Keys
		Dim v As String = where.Get(k)
		ADD_WHERE(k, "=", v)
	Next
	Dim res As List = BANano.Await(SELECT_WHERE)
	If res.size = 0 Then
		Return False
	Else
		Return True
	End If
End Sub

'<code>
'pbComponents.CLEAR_WHERE
'pbComponents.ADD_WHERE_STRING("attractive", "=", "true")
'pbComponents.ADD_ORDER_BY("attrname")
'BANano.Await(pb.SELECT_WHERE)
'Do While pb.NextRow
'Dim rec As Map = pb.Record
'Dim sid As String = pb.GetString("id")
'Loop
'</code>
Sub SELECT_WHERE As List
	If ShowLog Then
		Log($"SDUIMySQLAPI.SELECT_WHERE"$)
	End If
	lastPosition = -1
	result.Initialize
	result = BANano.Await(findWhereOrderBy(whereField, ops, orderByL))
	RowCount = result.size
	Return result
End Sub

'get own unique key with 15 chars
Sub NextID As String
	Dim ni As String = SDUIShared.guidAlpha(15)
	Return ni
End Sub


'prepare a new record
Sub PrepareRecord
	Record.Initialize
End Sub

'set the position of the current record
Sub setPosition(pos As Int)
	If result.Size > pos Then
		Record = result.Get(pos)
		lastPosition = pos
	Else
		lastPosition = -1
		Record.Initialize
	End If
End Sub

Sub getPosition As Int
	Return lastPosition
End Sub

'get an integer from the current record
Sub GetInt(fld As String) As Int
	fld = fld.tolowercase
	If BANano.IsUndefined(Record) Then Return 0
	If Record.ContainsKey(fld) Then
		Dim obj As Int = Record.GetDefault(fld, 0)
		obj = SDUIShared.CInt(obj)
		Return obj
	Else
		Return 0
	End If
End Sub

'get a long from the current record
Sub GetLong(fld As String) As Long
	Return GetInt(fld)
End Sub

'get a string from the current record
Sub GetString(fld As String) As String
	fld = fld.tolowercase
	If BANano.IsUndefined(Record) Then Return ""
	If Record.ContainsKey(fld) Then
		Dim obj As String = Record.GetDefault(fld, "")
		obj = SDUIShared.cstr(obj)
		Return obj
	Else
		Return ""
	End If
End Sub

'returns a placeholder if there is no image
Sub GetImage(fld As String) As String
	Dim simage As String = GetString(fld)
	If simage = "" Then Return "./assets/placeholderimg.jpg"
	Return simage
End Sub

'get a boolean from the current record
Sub GetBoolean(fld As String) As Boolean
	fld = fld.tolowercase
	If BANano.IsUndefined(Record) Then Return False
	If Record.ContainsKey(fld) Then
		Dim obj As Boolean = Record.GetDefault(fld, False)
		obj = SDUIShared.parseBool(obj)
		Return obj
	Else
		Return False
	End If
End Sub

'get a double from the current record
Sub GetDouble(fld As String) As Double
	fld = fld.tolowercase
	If Record.ContainsKey(fld) Then
		Dim obj As Double = Record.GetDefault(fld, 0)
		obj = SDUIShared.CDbl(obj)
		Return obj
	Else
		Return 0
	End If
End Sub

'get record at position
Sub GetRecord(pos As Int) As Map
	Dim rec As Map = result.Get(pos)
	Return rec
End Sub

'movefirst
'<code>
'rs.MoveFirst
'Do while rs.NextRow
'Loop
'</code>
Sub MoveFirst
	setPosition(0)
End Sub

'movelast
'<code>
'rs.MoveLast
'Do while rs.NextRow
'Loop
'</code>
Sub MoveLast
	setPosition(RowCount - 1)
End Sub

'moveprevious
Sub MovePrevious
	lastPosition = lastPosition - 1
	If lastPosition < 0 Then
		lastPosition = 0
	End If
	setPosition(lastPosition)
End Sub

'movenext
Sub MoveNext
	lastPosition = lastPosition + 1
	If lastPosition > RowCount Then
		lastPosition = RowCount - 1
	End If
	setPosition(lastPosition)
End Sub

'Execute a delete clause on the records
'<code>
'BANano.Await(pb.deleteWhere("id", m, array("=")))
'</code>
Sub deleteWhere(priKey As String, whereMap As Map, whereOps As List) As Boolean
	If ShowLog Then
		Log($"SDUIMySQLAPI.deleteWhere(${priKey},${BANano.ToJson(whereMap)},${whereOps})"$)
	End If
	If Schema.Size = 0 Then
		BANano.Throw($"SDUIMySQLAPI.deleteWhere. Please execute SchemaAdd?? first to define your table schema!"$)
		Return Null
	End If
	'get all the records
	Dim recs As List = BANano.Await(findWhereOrderBy(whereMap, whereOps,Null))
	For Each rec As Map In recs
		Dim pvalue As String = rec.Get(priKey)
		BANano.Await(DELETE(pvalue))
	Next
	Return True
End Sub

'Execute a where clause on the records
'The result is a list with the values.
'<code>
'Dim WhereRecords As List = BANano.Await(pb.findWhereOR(m, array("="))
'</code>
Sub findWhere(whereMap As Map, whereOps As List) As List
	If ShowLog Then
		Log($"SDUIMySQLAPI.findWhere(${BANano.ToJson(whereMap)},${whereOps})"$)
	End If
	Dim res As List = BANano.Await(findWhereOrderBy(whereMap, whereOps, Null))
	Return res
End Sub

'Execute a where clause on the records
'The result is a list with the values.
'<code>
'Dim WhereRecords As List = BANano.Await(pb.findWhereOrderBy(m, array("="), orderBy)
'</code>
Sub findWhereOrderBy(whereMap As Map, whereOps As List, orderBy As List) As List
	If ShowLog Then
		Log($"SDUIMySQLAPI.findWhereOrderBy(${BANano.ToJson(whereMap)},${whereOps},${orderBy})"$)
	End If
	If Schema.Size = 0 Then
		BANano.Throw($"SDUIMySQLAPI.findWhereOrderBy. Please execute SchemaAdd?? first to define your table schema!"$)
		Return Null
	End If
	'we build the filter
	Dim sb As StringBuilder
	sb.Initialize
	Dim i As Int
	Dim iWhere As Int = whereMap.Size - 1
	For i = 0 To iWhere
		Dim col As String = whereMap.GetKeyAt(i)
		Dim val As String = whereMap.GetValueAt(i)
		Dim opr As String = whereOps.Get(i)
		'Dim com As String = combineL.Get(i)
		'
		sb.Append($"filter[]="$).Append(col).Append(",")
		sb.Append(opr).Append(",").Append(val).Append("&")
	Next
	'
	Dim qfilter As String = sb.tostring
	Dim afilter As String = SDUIShared.remdelim(qfilter, "&")
	qfilter = afilter.Trim
	'
	Dim qsort As String = ""
	If BANano.IsNull(orderBy) = False Then
		qsort = SDUIShared.Join("&", orderBy)
		qsort = SDUIShared.RemDelim(qsort, "&")
	End If
	'
	Dim qflds As String = SDUIShared.Join("&", flds)
	qflds = SDUIShared.RemDelim(qflds, "&")
	'
	Dim xcommand As List
	xcommand.Initialize
	If qflds <> "" Then xcommand.add(qflds)
	If qfilter <> "" Then xcommand.add(qfilter)
	If qsort <> "" Then xcommand.Add(qsort)
	'
	Dim scommand As String = SDUIShared.Join("&", xcommand)
	scommand = SDUIShared.RemDelim(scommand, "&")

	Dim nresult As List
	nresult.Initialize
	' 
	'this is a get
	Dim fetch As SDUIFetch
	fetch.Initialize(baseURL)
	If scommand = "" Then
		fetch.SetURL($"/records/${TableName}"$)
	Else
		fetch.SetURL($"/records/${TableName}?${scommand}"$)
		If ShowLog Then
			Log($"SDUIMySQLAPI.findWhereOrderBy(${scommand})"$)
		End If
	End If
	BANano.Await(fetch.GetWait)
	If fetch.Success Then
		Dim Response As Map = fetch.response
		If Response.ContainsKey("records") Then
			nresult = Response.Get("records")
		End If
	End If
	Return nresult
End Sub

'get keyvalues for combos etc
'<code>
'Dim mExpenseCategories As Map = banano.Await(pbExpenseCategories.GetKeyValuesFromList(lst, True, "id", "name"))
'</code>
Sub GetKeyValuesFromList(source As List, bHasNothing As Boolean, k As String, v As String) As Map
	Dim mx As Map = CreateMap()
	If bHasNothing Then
		mx.Put("", "-- Nothing Selected --")
	End If
	For Each row As Map In source
		Dim f1 As String = row.get(k)
		Dim f2 As String = row.get(v)
		mx.Put(f1, f2)
	Next
	Return mx
End Sub