<%@ CodePage="65001" %>
<%
Response.Buffer = EW_RESPONSE_BUFFER
%>
<!--#include file="ewcfg.asp"-->
<!--#include file="aspfn.asp"-->
<!--#include file="userfn.asp"-->
<%
Call ew_Header(False, "utf-8", True)
Dim modallookup
Set modallookup = New cewmodallookup
Call modallookup.Page_Main
Set modallookup = Nothing

'
' Page class for modal lookup
'
Class cewmodallookup

	' Page ID
	Public Property Get PageID
		PageID = "modallookup"
	End Property

	' Page Object Name
	Public Property Get PageObjName
		PageObjName = "modallookup"
	End Property

	' Page Name
	Public Property Get PageName
		PageName = ew_CurrentPage()
	End Property

	' Page URL
	Public Property Get PageUrl
		PageUrl = ew_CurrentPage() & "?"
	End Property
	Dim Connection
	Dim DBID
	Dim DbType ' ASP
	Dim SQL
	Dim Recordset
	Dim TotalRecs
	Dim RowCnt
	Dim ColSpan
	Dim RecCount
	Dim StartOffset
	Dim LookupTable
	Dim LookupTableCaption
	Dim LinkField
	Dim LinkFieldCaption
	Dim DisplayFields(4)
	Dim DisplayFieldCaptions(4)
 	Dim DisplayFieldExpressions(4)
	Dim ParentFields(4)
	Dim Multiple
	Dim PageSize
	Dim SearchValue
	Dim SearchFilter
	Dim SearchType
	Dim PostData

	'
	' Page Main
	'
	Sub Page_Main()
		On Error Resume Next
		Dim sSql, value, arValue
		Dim filters, filter, filter2, i, j, fldtype, flddatatype, bValidData
		Dim pattern, Item, fn, fldvar, fldcaption

		' Init default value
		ColSpan = 1
		StartOffset = 0 ' 0-based, not StartRec which is 1-based
		Multiple = False
		PageSize = 10
		SearchValue = ""
		SearchFilter = ""
		SearchType = "" ' Auto ("=" => Exact Match, "AND" => All Keywords, "OR" => Any Keywords)
		Dim bPostBack
		bPostBack = (Request.Form <> "")
		If Not bPostBack Then
			Call Page_Error("Missing post data.")
		End If
		Dim Language
		Set Language = New cLanguage
		If Request.Form("lang").Count > 0 Then
			Language.LanguageId = Request.Form("lang")
		End If
		Call Language.LoadPhrases
		sSql = Request.Form("s") & ""
		sSql = ew_Decrypt(sSql)
		If sSql = "" Then
			Call Page_Error("Missing SQL.")
		End If
		filter = Request.Form("f0")
		filter = ew_Decrypt(filter)
		DBID = ew_IIf(Request.Form("d") & "" = "", "DB", Request.Form("d") & "")
		Multiple = ew_IIf(Request.Form("m") = "1", True, False)
		If Request.Form("recperpage").Count > 0 Then
			If IsNumeric(Request.Form("recperpage")) Then PageSize = CInt(Request.Form("recperpage"))
		End If
		If IsNumeric(Request.Form("start")) Then
			StartOffset = CInt(Request.Form("start"))
		Else
			StartOffset = 0
		End If

		' Load lookup table/field names
		LookupTable = Request.Form("lt")
		If LookupTable = "" Then
			Call Page_Error("Missing lookup table.")
		End If
		LookupTableCaption = Language.TablePhrase(LookupTable, "TblCaption")
		LinkField = Request.Form("lf")
		If LinkField = "" Then
			Call Page_Error("Missing link field.")
		End If
		LinkFieldCaption = Language.FieldPhrase(LookupTable, LinkField, "FldCaption")
		pattern = "^ldf\d+$"
		For Each Item In Request.Form
			If ew_RegExTest(pattern, Item) Then
				fldvar = Request.Form(Item)
				If Not fldvar = "" Then
					i = Replace(Item,"ldf","")
					fldcaption = Language.FieldPhrase(LookupTable, fldvar, "FldCaption")
					If fldcaption = "" Then
						fldcaption = fldvar
					End If
					DisplayFields(i-1) = fldvar
					DisplayFieldCaptions(i-1) = fldcaption
					DisplayFieldExpressions(i-1) = ew_Decrypt(Request.Form("dx" & i))
					ColSpan = ColSpan + 1
				End If
			End If
		Next

		' Load search filter / selected key values
		Dim arKeys, filterwrk, cnt
		fldtype = Request.Form("t0")&""
		flddatatype = ew_FieldDataType(fldtype)
		If Request.Form("sv").Count > 0 Then
			SearchValue = Request.Form("sv")
			SearchFilter = GetSearchFilter()
			filter = ""
		ElseIf Request.Form("keys").Count > 0 Then
			arKeys = Split(Request.Form("keys"), EW_LOOKUP_FILTER_VALUE_SEPARATOR)
			If UBound(arKeys) >= 0 Then
				filterwrk = ""
				cnt = UBound(arKeys)
				For i = 0 To cnt
					arKeys(i) = ew_QuotedValueBase(arKeys(i), flddatatype, DBID)
					filterwrk = filterwrk & ew_IIf(filterwrk = "", "", " OR ") & Replace(filter, "{filter_value}", arKeys(i))
				Next
				filter = filterwrk
				PageSize = -1
			Else
				filter = "1=0"
			End If
		Else
			filter = ""
		End If
		If ew_ContainsStr(sSql, "{filter}") Then
			pattern = "^f\d+$"
			For Each Item In Request.Form

				' Get the filter values (for "IN")
				If ew_RegExTest(pattern, Item) Then
					filter2 = ew_Decrypt(Request.Form(Item))
					If filter2 <> "" Then

						'i = Replace(Item,"f","")
						i = ew_RegExReplace("^f", Item, "")
						value = Request.Form("v" & i) & ""
						If value = "" Then
							If i > 0 Then ' Empty parent field

								'Do nothing ' Allow
								Call ew_AddFilter(filters, "1=0") ' Disallow
							End If
						Else
							ParentFields(i) = i
							arValue = Split(value, EW_LOOKUP_FILTER_VALUE_SEPARATOR)
							fldtype = Request.Form("t" & i) & ""
							flddatatype = ew_FieldDataType(fldtype)
							bValidData = True
							For j = 0 To UBound(arValue)
								If flddatatype = EW_DATATYPE_NUMBER And Not IsNumeric(arValue(j)) Then
									bValidData = False
									Exit For
								Else
									arValue(j) = ew_QuotedValueBase(arValue(j), flddatatype, DBID)
								End If
							Next
							If bValidData Then
								filter2 = Replace(filter2, "{filter_value}", Join(arValue, ","))
							Else
								filter2 = "1=0"
							End If
							Call ew_AddFilter(filters, filter2)
						End If
					End If
				End If
			Next
		End If
		Dim where
		where = "" ' Initialize
		If SearchFilter&"" <> "" And SearchValue&"" <> "" Then
			Call ew_AddFilter(where, SearchFilter)
		End If
		If Not filter = "" Then
			Call ew_AddFilter(where, filter)
		End If
		If Not filters = "" Then
			Call ew_AddFilter(where, filters)
		End If
		sSql = Replace(sSql, "{filter}", ew_IIf(where = "", "1=1", where))
		SQL = sSql

		' Call Page_Error(sSql) ' Show SQL for debugging
		' Get records

		DbType = ew_GetConnectionType(DBID)
		If DbType = "MSSQL" Then
			If ew_IsMsSql2012(DBID) Then DbType = "MSSQL2012" ' ASP
		End If
		Set Connection = ew_GetConn(DBID)
		TotalRecs = ew_GetRecordCount(SQL, DBID) ' ASP
		If PageSize > 0 Then ' ASP
			pattern = "\/\*BeginOrderBy\*\/[\s\S]+\/\*EndOrderBy\*\/"
			SQL = ew_GetSelectLimitSql(SQL, PageSize, StartOffset, ew_RegExTest(pattern, sql), DbType)
		End If
		Set Recordset = Connection.Execute(SQL)

		' Return JSON
		Call Page_Response
	End Sub

	' Get search filter
	Function GetSearchFilter()
		Dim sSearchStr, sSearch, sSearchType, str, sKeyword, Matches, Match, ar
		If Trim(SearchValue) = "" Then
			GetSearchFilter = ""
		End If
		sSearchStr = ""
		sSearch = Trim(SearchValue)
		sSearchType = SearchType
		sKeyword = ""
		If Not sSearchType = "=" Then

			' Match quoted keywords (i.e.: "...")
			If ew_RegExMatch("""([^""]*)""", sSearch, Matches) Then
				For Each Match In Matches
					p = InStr(sSearch, Matches.SubMatches(0))
					str = Mid(sSearch, 1, p - 2)
					sSearch = Mid(sSearch, p + Len(Match.SubMatches(0)) + 1)
					If Len(Trim(str)) > 0 Then
						ar = ew_ArrayMerge(ar, Split(Trim(str), " "))
					End If
					ar = ew_ArrayMerge(ar, Array(Match.SubMatches(0))) ' Save quoted keyword
				Next
			End If

			' Match individual keywords
			If Len(Trim(sSearch)) > 0 Then
				ar = ew_ArrayMerge(ar, Split(Trim(sSearch), " "))
			End If

			' Search keyword in any fields
			If sSearchType = "OR" Or sSearchType = "AND" Then
				For Each sKeyword In ar
					If sKeyword <> "" Then
						sSearchFilter = GetSearchSQL(Array(sKeyword))
						If sSearchFilter <> "" Then sSearchStr = sSearchStr & " " & sSearchType & " "
						sSearchStr = sSearchStr & "(" & sSearchFilter & ")"
					End If
				Next
			Else
				sSearchStr = GetSearchSQL(ar)
			End If
		Else
			sSearchStr = GetSearchSQL(Array(sSearch))
		End If
		GetSearchFilter = sSearchStr
	End Function

	' Get search SQL
	Function GetSearchSQL(arKeywords)
		Dim sWhere
		sWhere = ""
		If IsArray(arKeywords) Then
			For Each sql In DisplayFieldExpressions
				If Not sql = "" Then
					Call BuildSearchSQL(sWhere, sql, arKeywords)
				End If
			Next
		End If
		GetSearchSQL = sWhere
	End Function

	' Build search SQL
	Sub BuildSearchSQL(Where, FldExpr, arKeywords)
		Dim sSearchType, sDefCond, arSQL, arCond, cnt, i, j, bQuoted, sSql, Keyword, ar, sWrk
		sSearchType = SearchType
		sDefCond = ew_IIf(sSearchType = "OR", "OR", "AND")
		arSQL = Array() ' Array for SQL parts
		arCond = Array() ' Array for search conditions
		cnt = UBound(arKeywords)+1
		j = 0 ' Number of SQL parts
		For i = 0 To cnt - 1
			Keyword = arKeywords(i)
			Keyword = Trim(Keyword)
			If EW_BASIC_SEARCH_IGNORE_PATTERN <> "" Then
				Keyword = ew_RegExReplace(EW_BASIC_SEARCH_IGNORE_PATTERN, Keyword, "\")
				ar = Split(Keyword, "\")
			Else
				ar = Array(Keyword)
			End If
			For Each Keyword In ar
				If Keyword <> "" Then
					sWrk = ""
					If Keyword = "OR" And sSearchType = "" Then
						If j > 0 Then
							arCond(j-1) = "OR"
						End If
					Else
						sWrk = FldExpr & ew_Like(ew_QuotedValueBase("%" & Keyword & "%", EW_DATATYPE_STRING, DBID), DBID)
					End If
					If Not sWrk = "" Then
						If j > 0 Then
							ReDim Preserve arSQL(j)
							ReDim Preserve arCond(j)
						Else
							ReDim arSQL(0)
							ReDim arCond(0)
						End If
						arSQL(j) = sWrk
						arCond(j) = sDefCond
						j = j + 1
					End If
				End If
			Next
		Next
		cnt = UBound(arSQL)+1
		bQuoted = False
		sSql = ""
		If cnt > 0 Then
			For i = 0 To cnt - 2
				If arCond(i) = "OR" Then
					If Not bQuoted Then sSql = sSql & "("
					bQuoted = True
				End If
				sSql = sSql & arSQL(i)
				If bQuoted And arCond(i) <> "OR" Then
					sSql = sSql & ")"
					bQuoted = False
				End If
				sSql = sSql & " " & arCond(i) & " "
			Next
			sSql = sSql & arSQL(cnt-1)
			If bQuoted Then
				sSql = sSql & ")"
			End If
		End If
		If sSql <> "" Then
			If Where <> "" Then Where = Where & " OR "
			Where = Where & "(" & sSql & ")"
		End If
	End Sub

	' Show page response
	Sub Page_Response()
		Dim rowcnt, fldcnt, rsarr, i, j, str, dtFormat, result, json
		If ew_Empty(Recordset) Then
			Call ew_CloseConn
			Set result = Dictionary()
			Call result.Add("Result", "ERROR")
			Call result.Add("Message", "Failed to execute SQL")
			If EW_DEBUG_ENABLED Then
				Call result.Set("Message", result.Get("Message") & ": " & SQL) ' To be viewed in browser Network panel for debugging
			End If
			Call ResponseJson
			Response.Write "[" & result.ToJson() & "]"
			Set result = Nothing
			Exit Sub
		End If
		fldcnt = 4
		If TotalRecs - StartOffset > PageSize Then
			rowcnt = PageSize - 1
		Else
			rowcnt = TotalRecs - StartOffset - 1
		End If
		If Not Recordset.EOF Then
			If PageSize > 0 Then
				ReDim rsarr(fldcnt, rowcnt)
				If DbType = "ORACLE" Or DbType = "ACCESS" Or DbType = "MSSQL" Then
					Recordset.move StartOffset
				End If
				j = 0
				Do While Not Recordset.EOF And j < PageSize
					For i = 0 To fldcnt
						rsarr(i, j) = Recordset(i).Value
					Next
					Recordset.MoveNext
					j = j + 1
				Loop
			Else
				rsarr = Recordset.GetRows
			End If
		End If
		Recordset.Close
		Call ew_CloseConn

		' Format date
		Dim ardt
		ReDim ardt(fldcnt)
		For i = 0 To fldcnt
			ardt(i) = Request.Form("df" & i) ' Get date formats
		Next

		' Output
		Call ResponseJson
		If IsArray(rsarr) Then
			For j = 0 To UBound(rsarr, 2)
				For i = 0 To UBound(rsarr, 1)
					str = rsarr(i, j) & ""
					If Request.Form("keepCRLF").Count > 0 Then
						str = Replace(str, vbCr, "\\r")
						str = Replace(str, vbLf, "\\n")
						str = Replace(str, vbTab, "\\t")
					Else
						str = Replace(str, vbCr, " ")
						str = Replace(str, vbLf, " ")
						str = Replace(str, vbTab, " ")
					End If
					If Request.Form("df" & i).Count > 0 Then
						dtFormat = Request.Form("df" & i)
						If CInt(dtFormat) >= 0 Then
							str = ew_FormatDateTime(str, dtFormat)
						End If
					End If
					rsarr(i, j) = str
				Next
			Next
			json = ew_ArrayToJson(rsarr)
		Else ' ASP
			json = "[]"
		End If
		Response.Write "{""Result"": ""OK"", ""Records"": " & json & ", ""TotalRecordCount"": " & TotalRecs & "}"
	End Sub

	' Show page error
	Sub Page_Error(msg)
		Call ResponseJson
		Response.Write "{""Result"": ""ERROR"", ""Message"": """ & msg & """}"
		Response.End
	End Sub
End Class
%>