Subversion Repositories DevTools

Rev

Rev 5054 | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 5054 Rev 5055
Line 24... Line 24...
24 
'------------ Variable Definition -------------
 24 
'------------ Variable Definition -------------
25 
Dim result : result = -1
 25 
Dim result : result = -1
26 
Dim SqlQry
 26 
Dim SqlQry
27 
Dim rsQry
 27 
Dim rsQry
28 
Dim sdktag_id : sdktag_id = Request("sdktag_id")
 28 
Dim sdktag_id : sdktag_id = Request("sdktag_id")
- 
 
 29 
Dim sdk_reftag_id : sdk_reftag_id = Request("sdk_reftag_id") : if sdk_reftag_id = "" Then sdk_reftag_id = 0
29 
Dim sdk_statefilter : sdk_statefilter = Request("sdk_statefilter")
 30 
Dim sdk_statefilter : sdk_statefilter = Request("sdk_statefilter")
30 
 
 31 
 
31 
' Init the output JSON class
 32 
' Init the output JSON class
32 
'   Operations can add data
 33 
'   Operations can add data
33 
'   Default data will be added at the end
 34 
'   Default data will be added at the end
Line 72... Line 73...
72 
result = 0
 73 
result = 0
73 
dim firstRow,lastRow
 74 
dim firstRow,lastRow
74 
firstRow = CInt(Request.QueryString("start"))
 75 
firstRow = CInt(Request.QueryString("start"))
75 
lastRow = firstRow + CInt(Request.QueryString("length"))
 76 
lastRow = firstRow + CInt(Request.QueryString("length"))
76 
 
 77 
 
77 
' Define the data items to extract from the database
 - 
 
78 
' An array of items to extract
 - 
 
79 
'
 78 
'
80 
Dim dataCols: dataCols = Array ( _
- 
 
81 
        "sc.pv_id", _
 - 
 
82 
        "p.pkg_name", _
 - 
 
83 
        "pv.pkg_version", _
 - 
 
84 
        "sc.sdkpkg_state" )
 - 
 
85 
 
 - 
 
86 
'   Define array of colums to sort by
 79 
'   Define array of colums to sort by
87 
'       Must match user sort column request
 80 
'       Must match user sort column request
88 
Dim sortCols: sortCols = Array ( _
 81 
Dim sortCols: sortCols = Array ( _
89 
        "sc.pv_id", _
 82 
        "pv_id", _
90 
        "UPPER(p.pkg_name)", _
 83 
        "UPPER(pkg_name)", _
91 
        "UPPER(pv.pkg_version)", _
 84 
        "UPPER(pkg_version)", _
- 
 
 85 
        "UPPER(ref_pkg_version)", _
- 
 
 86 
        "UPPER(NVL(ref_sdkpkg_state,'-'))" , _
92 
        "UPPER(sc.sdkpkg_state)" )
 87 
        "UPPER(NVL(sdkpkg_state,'-'))" )
93 
 
 88 
 
94 
' Dim determine sorting options
 89 
' Dim determine sorting options
95 
'On Error goto 0
 90 
'   Sort by specified colum
96 
'Response.Write "<pre>"
 91 
'   Then sort by package name
- 
 
 92 
'   Default - sort by package name
97 
Dim sortString
 93 
Dim sortString
- 
 
 94 
Dim sortJoin : sortJoin = " ORDER BY "
98 
If Request.QueryString("order[0][column]") <> "" Then
 95 
If Request.QueryString("order[0][column]") <> "" Then
- 
 
 96 
 
99 
    sortString = " ORDER BY " & sortCols(CInt(Request.QueryString("order[0][column]")))
 97 
    Dim sortCol : sortCol = CInt(Request.QueryString("order[0][column]"))
100 
    sortString = sortString & " " & Request.QueryString("order[0][dir]")
 98 
    Dim sortDir : sortDir = " " & Request.QueryString("order[0][dir]")
- 
 
 99 
 
- 
 
 100 
    sortString = sortJoin & sortCols(sortCol)
- 
 
 101 
    sortString = sortString & sortDir
- 
 
 102 
    sortJoin = ","
- 
 
 103 
 
- 
 
 104 
    If sortCol <> 1 Then
101 
    sortString = sortString & "," & sortCols(CInt(1)) & " asc"
 105 
        sortString = sortString &sortJoin & sortCols(CInt(1))& sortDir
- 
 
 106 
    End If
102 
Else
 107 
Else
103 
    sortString = " ORDER BY " & sortCols(CInt(1)) & " asc"
 108 
    sortString = sortJoin & sortCols(CInt(1)) & " asc"
104 
End If
 109 
End If
105 
 
 110 
 
106 
' Filter (search )
 111 
' Filter (search )
- 
 
 112 
'   Filter by search string
- 
 
 113 
'   Filter by state
- 
 
 114 
Dim searchJoin : searchJoin = " WHERE "
107 
Dim searchString : searchString = ""
 115 
Dim searchString : searchString = ""
108 
If Request.QueryString("search[value]") <> "" Then
 116 
If Request.QueryString("search[value]") <> "" Then
109 
    searchString = " AND upper(pkg_name) || '_' || UPPER(pv.pkg_version) LIKE upper('%" & Request.QueryString("search[value]") & "%')"
117 
    searchString = searchJoin & "upper(pkg_name) || '_' || UPPER(pkg_version) LIKE upper('%" & Request.QueryString("search[value]") & "%')"
- 
 
 118 
    searchJoin = " AND "
110 
End If
 119 
End If
111 
 
 120 
 
112 
If sdk_statefilter Then
 121 
If sdk_statefilter Then
113 
    searchString = searchString & " AND sc.sdkpkg_state in ('E')"
 122 
    searchString = searchString & searchJoin & "sdkpkg_state in ('E') OR REF_SDKPKG_STATE in ('E')"
114 
End If
 123 
End If
115 
 
 124 
 
116 
' Create a list of cols that we need. Avoids ambiguity in selections
 - 
 
117 
Dim x,colList,colListJoin
 - 
 
118 
For x = Lbound(dataCols) to Ubound(dataCols)
 - 
 
119 
    colList = colList & colListJoin & dataCols(x)
 - 
 
120 
    colListJoin = ","
 - 
 
121 
Next
 - 
 
122 
 
 - 
 
123 
Dim whereString
 - 
 
124 
Dim BasicSql
 125 
Dim BasicSql
125 
BasicSql = "select " & colList &_
 126 
BasicSql =  "SELECT * from (" &_
- 
 
 127 
            "SELECT  " &_
- 
 
 128 
            "A.PV_ID," &_
- 
 
 129 
            "NVL(A.PKG_NAME, B.PKG_NAME) as PKG_NAME," &_
- 
 
 130 
            "A.PKG_VERSION," &_
- 
 
 131 
            "B.PKG_VERSION as REF_PKG_VERSION," &_
- 
 
 132 
            "A.sdkpkg_state," &_
- 
 
 133 
            "B.sdkpkg_state as REF_SDKPKG_STATE" &_
- 
 
 134 
            " FROM " &_
- 
 
 135 
            "(SELECT p.pkg_name || pv.v_ext as mname," &_
- 
 
 136 
            "  sc.pv_id," &_
- 
 
 137 
            "  p.pkg_name," &_
- 
 
 138 
            "  pv.pkg_version," &_
- 
 
 139 
            "  sc.sdkpkg_state" &_
- 
 
 140 
            " FROM SDK_CONTENT SC ," &_
126 
            " FROM SDK_CONTENT SC , PACKAGE_VERSIONS pv, PACKAGES p " &_
 141 
            "  PACKAGE_VERSIONS pv," &_
- 
 
 142 
            "  PACKAGES p" &_
127 
            " where sc.sdktag_id = "&sdktag_id&" AND pv.pv_id = sc.pv_id AND p.pkg_id = pv.pkg_id " &_
 143 
            " WHERE sc.sdktag_id = " & sdktag_id  &_
- 
 
 144 
            " AND pv.pv_id       = sc.pv_id" &_
- 
 
 145 
            " AND p.pkg_id       = pv.pkg_id" &_
- 
 
 146 
            ") A" &_
- 
 
 147 
            " FULL OUTER JOIN" &_
- 
 
 148 
            "(" &_
- 
 
 149 
            "SELECT p.pkg_name || pv.v_ext as mname," &_
128 
            whereString &_
 150 
            "  sc.pv_id," &_
- 
 
 151 
            "  p.pkg_name," &_
- 
 
 152 
            "  pv.pkg_version," &_
- 
 
 153 
            "  sc.sdkpkg_state" &_
- 
 
 154 
            " FROM SDK_CONTENT SC ," &_
- 
 
 155 
            "  PACKAGE_VERSIONS pv," &_
- 
 
 156 
            "  PACKAGES p" &_
- 
 
 157 
            " WHERE sc.sdktag_id = " & sdk_reftag_id &_
- 
 
 158 
            " AND pv.pv_id       = sc.pv_id" &_
- 
 
 159 
            " AND p.pkg_id       = pv.pkg_id" &_
- 
 
 160 
            " ) B" &_
- 
 
 161 
            " ON A.mname = B.mname)" &_
129 
            searchString &_
 162 
            searchString &_
130 
            sortString
 163 
            sortString
131 
 
 164 
 
132 
SqlQry = "select * from ( "&_
 165 
SqlQry = "select * from ( "&_
133 
            "select a.*, ROWNUM rnum from (" & BasicSql &_
 166 
            "select a.*, ROWNUM rnum from (" & BasicSql &_