WebSVN – DevTools – Diff – /ManagerSuite/Release_Manager/trunk/web/build_release_log_json.asp

     oJSON.data("sReq_" & vName) = Request.QueryString(vName)
 next
 ' Extract selected range
 result = 0
-dim firstRow,lastRow
+dim firstRow,lastRow,noBuildTime
 firstRow = CLng(Request.QueryString("iDisplayStart"))
 lastRow = firstRow + CLng(Request.QueryString("iDisplayLength"))
-' Define the data items to extract from the database
-' An array of items to extract
-'
-Dim dataCols: dataCols = Array ( _
-        "bi.PV_ID", _
-        "bi.RTAG_ID" ,_
-        "pv.pkg_id", _
-        "pj.PROJ_NAME", _
-        "pj.PROJ_ID", _
-        "RTAG_NAME", _
-        "p.PKG_NAME", _
-        "pv.PKG_VERSION", _
-        "pv.PV_DESCRIPTION", _
-        "pv.COMMENTS", _
-        "NVL(pv.V_EXT, '') as V_EXT", _
-        "TO_CHAR(bi.TIMESTAMP, 'Dy DD-Mon-YYYY HH24:MI:SS') as TIMESTAMPTXT", _
-        "DECODE(bi.reason, 'N', 'New Version', 'R', 'Ripple', 'T', 'Test', 'P', 'Restored', 'Unknown') as REASON" ,_
-        "DECODE(bi.state, 'B', 'Buiding', 'C', 'Complete', 'E', 'Error', 'S', 'SysErr', 'Unknown') as STATE",_
-        "bi.build_id" )
 '   Define array of colums to sort by
 '       Must match user sort column request
 Dim sortCols: sortCols = Array ( _
         "UPPER(pj.PROJ_NAME)",_
-        "UPPER(rt.RTAG_NAME)",_
+        "UPPER(rt.RTAG_NAME),build_id",_
-        "UPPER(p.PKG_NAME)",_
+        "UPPER(p.PKG_NAME),build_id",_
-        "UPPER(pv.PKG_VERSION)",_
+        "UPPER(p.PKG_NAME),UPPER(pv.PKG_VERSION)",_
         "bi.BUILD_ID",_
-        "UPPER(bi.reason)",_
+        "UPPER(bi.reason),build_id",_
+        "build_time,build_id,NOBUILDTIME",_
-        "UPPER(bi.state)",_
+        "UPPER(bi.state),build_id",_
         "build_id" )
 ' Dim determine sorting options
 'On Error goto 0
 'Response.Write "<pre>"
 Dim sortString
 If Request.QueryString("iSortCol_0") <> "" Then
-    sortString = " ORDER BY " & sortCols(CLng(Request.QueryString("iSortCol_0")))
+    Dim splitArg: splitArg = Split( sortCols(CLng(Request.QueryString("iSortCol_0"))), "," )
+    Dim splitItem, joiner
+    joiner = ""
+    for each splitItem in splitArg
+        If splitItem = "NOBUILDTIME" Then
+            noBuildTime = " AND PV.BUILD_TIME is not null"
+        Else
-    sortString = sortString & " " & Request.QueryString("sSortDir_0")
+            sortString = sortString & joiner & splitItem & " " & Request.QueryString("sSortDir_0")
+            joiner = ","
+        End If
+    next
+    sortString = " ORDER BY " & sortString
 End If
 ' Filter (search )
 Dim searchString
 If Request.QueryString("sSearch") <> "" Then
     searchString = " AND upper(p.PKG_NAME) LIKE upper('%" & Request.QueryString("sSearch") & "%')"
 End If
-' Create a list of cols that we need. Avoids ambiguity in selections
-Dim x,colList,colListJoin
-For x = Lbound(dataCols) to Ubound(dataCols)
-    colList = colList & colListJoin & dataCols(x)
-    colListJoin = ","
-Next
 Dim whereString
 If parRtagId <> "" Then
     whereString = " AND bi.rtag_id = " &  parRtagId
 End If
 Dim BasicSql
-BasicSql = "select " & colList &_
+BasicSql =  "SELECT bi.PV_ID, " &_
+            "  bi.RTAG_ID, " &_
+            "  pv.pkg_id, " &_
+            "  pj.PROJ_NAME, " &_
+            "  pj.PROJ_ID, " &_
+            "  RTAG_NAME, " &_
+            "  p.PKG_NAME, " &_
+            "  NVL(pv.V_EXT, '') AS V_EXT, " &_
+            "  TO_CHAR(bi.TIMESTAMP, 'Dy DD-Mon-YYYY HH24:MI:SS') AS TIMESTAMPTXT, " &_
+            "  DECODE(bi.reason, 'N', 'New Version', 'R', 'Ripple', 'T', 'Test', 'P', 'Restored', 'Unknown') AS REASON, " &_
+            "  DECODE(bi.state, 'B', 'Buiding', 'C', 'Complete', 'E', 'Error', 'S', 'SysErr', 'Unknown') AS STATE, " &_
+            "  bi.build_id " &_
-            " from BUILD_INSTANCES bi, " &_
+            "FROM BUILD_INSTANCES bi, " &_
-            "     projects pj, " &_
+            "  projects pj, " &_
-            "     RELEASE_TAGS rt, " &_
+            "  RELEASE_TAGS rt, " &_
-            "     packages p, " &_
+            "  packages p, " &_
-            "     PACKAGE_VERSIONS pv" &_
+            "  PACKAGE_VERSIONS pv " &_
-            " where bi.PV_ID = pv.pv_id " &_
+            "WHERE bi.PV_ID = pv.pv_id " &_
-            "  and pv.PKG_ID = p.PKG_ID" &_
+            "AND pv.PKG_ID  = p.PKG_ID " &_
-            "  and bi.RTAG_ID = rt.RTAG_ID" &_
+            "AND bi.RTAG_ID = rt.RTAG_ID " &_
-            "  and rt.proj_id = pj.proj_id" &_
+            "AND rt.proj_id = pj.proj_id " &_
+            noBuildTime &_
             whereString &_
             searchString &_
             sortString
+SqlQry = "Select z.*," &_
-SqlQry = "Select z.*, (SELECT COUNT(*) FROM TEST_RUN tr WHERE tr.build_id = z.build_id ) AS test_count from (select * from ( "&_
+            "(SELECT COUNT(*) FROM TEST_RUN tr WHERE tr.build_id = z.build_id ) AS test_count, " &_
+            "(SELECT PKG_VERSION FROM PACKAGE_VERSIONS pv1 WHERE pv1.pv_id = z.pv_id ) AS PKG_VERSION, " &_
+            "(SELECT PV_DESCRIPTION FROM PACKAGE_VERSIONS pv1 WHERE pv1.pv_id = z.pv_id ) AS PV_DESCRIPTION, " &_
+            "(SELECT COMMENTS FROM PACKAGE_VERSIONS pv1 WHERE pv1.pv_id = z.pv_id ) AS COMMENTS, " &_
+            "(SELECT BUILD_TIME FROM PACKAGE_VERSIONS pv1 WHERE pv1.pv_id = z.pv_id  ) AS BUILD_TIME" &_
+            " from (select * from ( "&_
-            "select /*+ FIRST_ROWS(n) */ a.*, ROWNUM rnum from (" & BasicSql &_
+            "select /*+ FIRST_ROWS("& lastRow &") */ a.*, ROWNUM rnum from (" & BasicSql &_
                 ") a where ROWNUM <= " & lastRow &_
             ") where rnum >= " & firstRow & ") z"
 ' Assist in debug
 oJSON.data("BasicSql") = BasicSql
        ' Attempt to speed up access tot he data
        '    Extract all fields for the row
        '    Access fields by index
        Dim fields
        Set fields = rsQry.Fields
+       Dim pv_id        : pv_id = fields(0)
+       Dim rtag_id      : rtag_id = fields(1)
+       Dim pkg_id       : pkg_id = fields(2)
        Dim proj_name    : proj_name = fields(3)
        Dim proj_id      : proj_id = fields(4)
        Dim rtag_name    : rtag_name = fields(5)
        Dim pkg_name     : pkg_name = fields(6)
-       Dim pkg_id       : pkg_id = fields(2)
+       Dim v_ext        : v_ext = fields(7)
-       Dim v_ext        : v_ext = fields(10)
+       Dim timestamp    : timestamp = fields(8)
-       Dim description  : description = Server.HTMLEncode(fields(8))
+       Dim reason       : reason = fields(9)
-       Dim pv_id        : pv_id = fields(0)
+       Dim state        : state = fields(10)
-       Dim rtag_id      : rtag_id = fields(1)
+       Dim buildId      : buildId = fields(11)
-       Dim comments     : comments = Server.HTMLEncode(fields(9))
+       Dim tcount       : tcount = fields(13)
-       Dim pkg_version  : pkg_version = fields(7)
+       Dim pkg_version  : pkg_version = fields(14)
-       Dim timestamp    : timestamp = fields(11)
-       Dim reason       : reason = fields(12)
+       Dim description  : description = Server.HTMLEncode(fields(15))
-       Dim state        : state = fields(13)
+       Dim comments     : comments = Server.HTMLEncode(fields(16))
-       Dim tcount       : tcount = fields(16)
+       Dim buildTime    : buildTime = Server.HTMLEncode(fields(17))
        Set fields = nothing
+        If buildTime <= 0 Then buildTime = ""
+        If tcount <= 0 Then tcount = ""
-        newitem(0) = "<a href='rtree.asp?proj_id=" & proj_id & "'>" & proj_name & "</a>"
+        newitem(0) = "<a href='rtree.asp?proj_id=" & proj_id & "'>" & proj_name &"</a>"
         newitem(1) = "<a href='dependencies.asp?rtag_id=" & rtag_id & "'>" & rtag_name & "</a>"
         newitem(2) = "<a href=view_by_version.asp?pkg_id=" & pkg_id & "&fpkgversion=*" & v_ext & " title=""" & description & """>" & pkg_name & "</a>"
         newitem(3) =  "<a href='dependencies.asp?pv_id=" & pv_id & "&rtag_id=" & rtag_id &"' title=""" & comments & """>" & pkg_version & "</a>"
         newitem(4) = timestamp
         newitem(5) = reason
+        newitem(6) = buildTime
-        newitem(6) = "<a href='unit_test_log.asp?rtag_id=" & rtag_id & "&pv_id=" & pv_id & "'>" & state & "</a>"
+        newitem(7) = "<a href='unit_test_log.asp?rtag_id=" & rtag_id & "&pv_id=" & pv_id & "'>" & state & "</a>"
-        If tcount <= 0 Then tcount = ""
-        newitem(7) = tcount
+        newitem(8) = tcount
        rsQry.MoveNext
    Wend
 End IF

Subversion Repositories DevTools

(root)/ManagerSuite/Release_Manager/trunk/web/build_release_log_json.asp – Rev 5957 → 6538