<%@LANGUAGE="VBSCRIPT"%> <% '===================================================== ' sdk_details_json.asp ' Ajax support for table of SDK Content ' Designed to be driven by the jquery tablescroller ' Uses DataTables 1.10 format data '===================================================== %> <% Option explicit ' Essential to get UTF through all the hoops. ie: VÄSTTRAFIK (VTK) 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 Dim sdktag_id : sdktag_id = Request("sdktag_id") Dim sdk_reftag_id : sdk_reftag_id = Request("sdk_reftag_id") : if sdk_reftag_id = "" Then sdk_reftag_id = 0 Dim sdk_statefilter : sdk_statefilter = Request("sdk_statefilter") Dim sdk_difffilter : sdk_difffilter = Request("sdk_difffilter") ' 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 ' Gives bad results when searching or with diff filter Dim MaxCount : MaxCount = 0 SqlQry = "select count(*) as count from SDK_CONTENT skc where SDKTAG_ID = '" & sdktag_id &"'" If sdk_statefilter Then SqlQry = SqlQry & " AND skc.sdkpkg_state in ('E')" End If 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 ' iTotalRecords = total records without any filtering/limits ' iTotalDisplayRecords = filtered result count oJSON.data("draw") = CInt(Request.QueryString("draw")) oJSON.data("recordsTotal") = MaxCount 'oJSON.data("recordsFiltered") = MaxCount 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 array of colums to sort by ' Must match user sort column request Dim sortCols: sortCols = Array ( _ "pv_id", _ "UPPER(pkg_name)", _ "UPPER(pkg_version)", _ "UPPER(ref_pkg_version)", _ "UPPER(NVL(ref_sdkpkg_state,'-'))" , _ "UPPER(NVL(sdkpkg_state,'-'))" ) ' Dim determine sorting options ' Sort by specified colum ' Then sort by package name ' Default - sort by package name Dim sortString Dim sortJoin : sortJoin = " ORDER BY " If Request.QueryString("order[0][column]") <> "" Then Dim sortCol : sortCol = CInt(Request.QueryString("order[0][column]")) Dim sortDir : sortDir = " " & Request.QueryString("order[0][dir]") sortString = sortJoin & sortCols(sortCol) sortString = sortString & sortDir sortJoin = "," If sortCol <> 1 Then sortString = sortString &sortJoin & sortCols(CInt(1))& sortDir End If Else sortString = sortJoin & sortCols(CInt(1)) & " asc" End If ' Filter (search ) ' Filter by search string ' Filter by state Dim searchJoin : searchJoin = " WHERE " Dim searchString : searchString = "" If Request.QueryString("search[value]") <> "" Then searchString = searchJoin & "upper(pkg_name) || '_' || UPPER(pkg_version) LIKE upper('%" & Request.QueryString("search[value]") & "%')" searchJoin = " AND " End If If sdk_statefilter Then searchString = searchString & searchJoin & "(sdkpkg_state in ('E') OR REF_SDKPKG_STATE in ('E'))" searchJoin = " AND " End If If sdk_difffilter Then searchString = searchString & searchJoin & "diff != 0" End If Dim BasicSql BasicSql = "SELECT * from (" &_ "SELECT " &_ "A.PV_ID," &_ "NVL(A.PKG_NAME, B.PKG_NAME) as PKG_NAME," &_ "A.PKG_VERSION," &_ "B.PKG_VERSION as REF_PKG_VERSION," &_ "A.sdkpkg_state," &_ "B.sdkpkg_state as REF_SDKPKG_STATE," &_ "CASE " &_ " WHEN "& sdk_reftag_id &" = 0 THEN 0 " &_ " WHEN A.PV_ID is null THEN 1 " &_ " WHEN B.PV_ID is null THEN 2 " &_ " WHEN A.PKG_VERSION != B.PKG_VERSION THEN 3 " &_ " WHEN A.sdkpkg_state != B.sdkpkg_state THEN 4 " &_ " ELSE 0 " &_ " END AS diff " &_ " FROM " &_ "(SELECT p.pkg_name || pv.v_ext as mname," &_ " sc.pv_id," &_ " p.pkg_name," &_ " pv.pkg_version," &_ " sc.sdkpkg_state" &_ " FROM SDK_CONTENT SC ," &_ " PACKAGE_VERSIONS pv," &_ " PACKAGES p" &_ " WHERE sc.sdktag_id = " & sdktag_id &_ " AND pv.pv_id = sc.pv_id" &_ " AND p.pkg_id = pv.pkg_id" &_ ") A" &_ " FULL OUTER JOIN" &_ "(" &_ "SELECT p.pkg_name || pv.v_ext as mname," &_ " sc.pv_id," &_ " p.pkg_name," &_ " pv.pkg_version," &_ " sc.sdkpkg_state" &_ " FROM SDK_CONTENT SC ," &_ " PACKAGE_VERSIONS pv," &_ " PACKAGES p" &_ " WHERE sc.sdktag_id = " & sdk_reftag_id &_ " AND pv.pv_id = sc.pv_id" &_ " AND p.pkg_id = pv.pkg_id" &_ " ) B" &_ " ON A.mname = B.mname)" &_ searchString &_ sortString SqlQry = "select * from ( "&_ "select a.*, ROWNUM rnum from (" & BasicSql &_ ") a where ROWNUM <= " & lastRow &_ ") where rnum >= " & firstRow ' ' Perform a query without pagination ' Assume the record set is small enough that it won't impact speed ' ' ' Determine the size of the record set ' Gives bad results when searching Dim SqlCount: SqlCount = "select count(*) as count from (" & BasicSql & ")" On Error Resume Next objEH.ErrorRedirect = FALSE objEH.TryORA ( OraSession ) Set rsQry = OraDatabase.DbCreateDynaset( SqlCount, 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 ' iTotalRecords = total records without any filtering/limits ' iTotalDisplayRecords = filtered result count 'oJSON.data("recordsTotal") = MaxCount oJSON.data("recordsFiltered") = MaxCount ' Assist in debug oJSON.data("BasicSql") = BasicSql 'oJSON.data("SqlQry") = SqlQry ' Perform the database query Set oJSON.data("aaData") = oJSON.Collection() On Error Resume Next objEH.ErrorRedirect = FALSE objEH.TryORA ( OraSession ) Set rsQry = OraDatabase.DbCreateDynaset( SqlQry, ORADYN_DEFAULT ) objEH.CatchORA ( OraSession ) ' Process each row and return required fields to the user If objEH.Finally Then On Error goto 0 While (NOT rsQry.BOF) AND (NOT rsQry.EOF) Set newitem = oJSON.AddToCollection(oJSON.data("aaData")) Dim ii for ii = 0 to rsQry.Fields.Count - 2 newitem (rsQry.FieldName(ii)) = rsQry.Fields(ii) 'newitem (ii) = rsQry.Fields(ii) Next rsQry.MoveNext Wend End IF rsQry.Close Set rsQry = Nothing ' ' SQL error detection and reporting ' The content of 'error' will be dsplayed to the user. If objEH.LastOraFailed Then oJSON.data("error") = 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 %>