<%@LANGUAGE="VBSCRIPT"%> <% '===================================================== ' project_log_json.asp ' ' AJAX SUpport for project_log.asp '===================================================== %> <% Option explicit Response.ContentType = "text/html" Response.AddHeader "Content-Type", "text/html;charset=UTF-8" Response.CodePage = 65001 Response.CharSet = "UTF-8" %> <% '------------ Variable Definition ------------- Dim result : result = -1 Dim SqlQry Dim rsQry ' Init the output JSON class ' Operations can add data ' Default data will be added at the end Dim oJSON :Set oJSON = New aspJSON Dim newitem ' ' Determine the size of the record set Dim MaxCount : MaxCount = 0 SqlQry = "select count(*) as count from PROJECT_ACTION_LOG" On Error Resume Next objEH.ErrorRedirect = FALSE objEH.TryORA ( OraSession ) Set rsQry = OraDatabase.DbCreateDynaset( SqlQry, ORADYN_DEFAULT ) objEH.CatchORA ( OraSession ) If ((NOT rsQry.BOF) AND (NOT rsQry.EOF)) Then MaxCount = rsQry("COUNT") End If rsQry.Close Set rsQry = Nothing ' Basic Header oJSON.data("draw") = CInt(Request.QueryString("draw")) oJSON.data("recordsTotal") = MaxCount oJSON.data("recordsFiltered") = MaxCount ' Assist in debug oJSON.data("start") = CInt(Request.QueryString("start")) oJSON.data("length") = CInt(Request.QueryString("length")) Dim vName for each vName in Request.QueryString oJSON.data("sReq_" & vName) = Request.QueryString(vName) next ' Extract selected range result = 0 dim firstRow,lastRow firstRow = CInt(Request.QueryString("start")) lastRow = firstRow + CInt(Request.QueryString("length")) ' Define the data items to extract from the database ' Format is: ' 1) Name of Database column ' 2) Expression::Name of Database column ' Name will be used for sorting puposes ' Expression will be used for data retrieval ' Dim dataCols: dataCols = Array ( _ "TO_CHAR(PL.ACTION_DATETIME, 'Dy DD-Mon-YYYY HH:MI:SS')::PL.ACTION_DATETIME", _ "PL.DESCRIPTION", _ "PROJ_ID", _ "RTAG_ID", _ "AT.DESCRIPTION", _ "FULL_NAME") ' Split dataCols into two parts based on :: ' FirstPart - Complex Expression ' SecondPart - Simple Name for Result ' If no '::' then Expression to be the simple bit Dim x, pos ReDim dataExt(Ubound(dataCols)) For x = Lbound(dataCols) to Ubound(dataCols) pos = InStr( dataCols(x), "::" ) If pos > 0 Then dataExt(x) = Mid( dataCols(x), 1, pos - 1 ) dataCols(x) = Mid(dataCols(x), pos + 2) Else dataExt(x) = dataCols(x) End If Next ' Dim determine sorting options Dim sortString If Request.QueryString("order[0][column]") <> "" Then sortString = " ORDER BY " & dataCols(CInt(Request.QueryString("order[0][column]"))) sortString = sortString & " " & Request.QueryString("order[0][dir]") End If ' Create a list of cols that we need. Avoids ambiguity in selections Dim colList,colListJoin For x = Lbound(dataCols) to Ubound(dataCols) colList = colList & colListJoin & dataExt(x) Dim asName asName = Replace(dataCols(x) , "." , "_") if dataCols(x) <> asName Then colList = colList & " as " & asName colListJoin = "," Next Dim BasicSql BasicSql = "select " & colList &_ " from PROJECT_ACTION_LOG pl,USERS u, ACTION_TYPE at" &_ " where pl.USER_ID = u.USER_ID and pl.acttype_id = at.acttype_id" &_ sortString SqlQry = "select * from ( "&_ "select a.*, ROWNUM rnum from (" & BasicSql &_ ") a where ROWNUM <= " & lastRow &_ ") where rnum >= " & firstRow ' Assist in debug oJSON.data("BasicSql") = BasicSql 'oJSON.data("SqlQry") = SqlQry ' Perform the database query Set oJSON.data("data") = oJSON.Collection() On Error Resume Next objEH.ErrorRedirect = FALSE objEH.TryORA ( OraSession ) Set rsQry = OraDatabase.DbCreateDynaset( SqlQry, ORADYN_DEFAULT ) objEH.CatchORA ( OraSession ) ' Return all fields to the user as an array If objEH.Finally Then While (NOT rsQry.BOF) AND (NOT rsQry.EOF) Set newitem = oJSON.AddToCollection(oJSON.data("data")) Dim ii for ii = 0 to rsQry.Fields.Count - 1 newitem(ii) = rsQry.Fields(ii) Next rsQry.MoveNext Wend End IF rsQry.Close Set rsQry = Nothing ' ' SQL error detection and reporting If objEH.LastOraFailed Then oJSON.data("error") = 1 oJSON.data("emsgSummary") = objEH.MessageSummary oJSON.data("emsgDetails") = objEH.MessageDetails oJSON.data("SqlQry") = SqlQry End If 'Return the object Response.Write oJSON.JSONoutput() Set oJSON = Nothing Call Destroy_All_Objects %>