<%@LANGUAGE="VBSCRIPT"%>
<%
'=====================================================
'|                                                   |
'|        RequestTop10ChangesInLastWeek.asp          |
'|                                                   |
'=====================================================
%>
<%
Option explicit
' Good idea to set when using redirect
Response.Expires = 0   ' always load the page, dont store
%>
<!--#include file="common/conf.asp"-->
<!--#include file="common/globals.asp"-->
<!--#include file="common/formating.asp"-->
<!--#include file="common/qstr.asp"-->
<!--#include file="common/common_subs.asp"-->
<%
'------------ Variable Definition -------------
Dim rsTemp
Dim parInterval
'------------ Constants Declaration -----------
'------------ Variable Init -------------------
parInterval = Request("Interval")
'----------------------------------------------
%>
<%
'------------------------------------------------------------------------------------------------------------------------
' Thus function formulates a query that is intended to find the top 10 packages that have changed the most in a period of
' time, limited in scope to those packages that have at least one version in the specified release.
' The function relies upon the user of this ASP file having setup the Interval parameter as needed (see RequestReleasePackages.asp)
Function formQueryString

   Dim qs
   Dim date_Interval_expression

   ' Insert the correct date interval condition
   Select Case parInterval
      Case 1
         date_Interval_expression = " AND changed_package_versions.MODIFIED_STAMP > (ORA_SYSDATE - interval '7' day)"
      Case 2
         date_Interval_expression = " AND changed_package_versions.MODIFIED_STAMP > (ORA_SYSDATE - interval '30' day)"
      Case 3
         date_Interval_expression = " AND changed_package_versions.MODIFIED_STAMP > (ORA_SYSDATE - interval '90' day)"
      Case Else
         Call RaiseMsg(enum_MSG_ERROR, "Internal Error, date interval not specified.")
   End Select

   ' For each package ID in a release
   '    For each package version across all projects and releases (have to do this due to schema limitations)
   '       If the package version extension is the same as that being used currently in the release
   '          If the package version is locked, and is not a ripple
   '             Increment a count for this package ID
   ' Return the 10 package IDs with the highest counts
   qs  = " SELECT ordered_changed.pkg_id, ordered_changed.pkg_name, pv_outer.pv_id, pv_outer.pkg_version, ordered_changed.NumberOfChanges" _
       & " FROM RELEASE_CONTENT rc_outer, PACKAGE_VERSIONS pv_outer,                                        " _
       & " (                                                                                                " _
       & "    SELECT * FROM (                                                                               " _
       & "       SELECT changed.pkg_name, changed.pkg_id, COUNT(changed.pkg_name) as NumberOfChanges FROM ( " _
       & "          SELECT PACKAGES.pkg_name, PACKAGES.pkg_id                                               " _
       & "          FROM   PACKAGE_VERSIONS changed_package_versions, PACKAGES                              " _
       & "          WHERE  changed_package_versions.pkg_id      = PACKAGES.pkg_id                           " & date_Interval_expression _
       & "          AND    changed_package_versions.build_type <> 'Y'                                       " _
       & "          AND    changed_package_versions.dlocked     = 'Y'                                       " _
       & "          AND    changed_package_versions.pkg_id IN (                                             " _
       & "                    SELECT pkg_id FROM PACKAGE_VERSIONS WHERE pv_id IN (                          " _
       & "                       SELECT pv_id                                                               " _
       & "                       FROM   RELEASE_CONTENT                                                     " _
       & "                       WHERE  RELEASE_CONTENT.rtag_id = :rtag_id                                  " _
       & "                    )                                                                             " _
       & "                 )                                                                                " _
       & "          AND    changed_package_versions.v_ext IN (                                              " _
       & "                    SELECT v_ext FROM PACKAGE_VERSIONS, RELEASE_CONTENT                           " _
       & "                    WHERE  PACKAGE_VERSIONS.pv_id  = RELEASE_CONTENT.pv_id                        " _
       & "                    AND    PACKAGE_VERSIONS.pkg_id = changed_package_versions.pkg_id              " _
       & "                    AND    RELEASE_CONTENT.rtag_id = :rtag_id                                     " _
       & "                 )                                                                                " _
       & "       ) changed                                                                                  " _
       & "       GROUP BY changed.pkg_name, changed.pkg_id                                                  " _
       & "       ORDER BY NumberOfChanges DESC                                                              " _
       & "    ) WHERE ROWNUM < 11                                                                           " _
       & " ) ordered_changed                                                                                " _
       & " WHERE                                                                                            " _
       & "   ordered_changed.pkg_id = pv_outer.pkg_id                                                       " _
       & "   AND rc_outer.rtag_id   = :rtag_id                                                              " _
       & "   AND rc_outer.pv_id     = pv_outer.pv_id                                                        "

   formQueryString = qs
End Function



'------------------------------------------------------------------------------------------------------------------------
%>
<%
'------------------------ MAIN LINE ---------------------------------
'--------------------------------------------------------------------
%>
<table width="100%" border="0" cellspacing="1" cellpadding="1">

   <tr>
      <td background="images/bg_form_lightbluedark.gif">
         <table width="100%" border="0" cellspacing="1" cellpadding="1">
            <tr>
               <td nowrap class="body_txt" valign="top" width="70%" background="images/bg_form_lightbluedark.gif">Package Name</td>
               <td nowrap class="body_txt" valign="top" width="30%" background="images/bg_form_lightbluedark.gif">Changes<%=Quick_Help("Top10ChangeCounts")%></td>
            </tr>

            <%
            OraDatabase.Parameters.Add "RTAG_ID", Request("rtag_id"),    ORAPARM_INPUT, ORATYPE_NUMBER

            Set rsTemp = OraDatabase.DbCreateDynaset( formQueryString, cint(0))

            OraDatabase.Parameters.Remove "RTAG_ID"

            If rsTemp.RecordCount < 1 Then
            %>
               <tr>
                  <td colspan="3" background="images/bg_form_lightgray.gif" nowrap>&nbsp;</td>
               </tr>
            <%
            Else

               While ((NOT rsTemp.BOF) AND (NOT rsTemp.EOF))
               %>
                  <tr>
                     <td nowrap class="body_txt" background="images/bg_form_lightgray.gif">
                        <a class="txt_linked" href="dependencies.asp?pv_id=<%=rsTemp("pv_id")%>&rtag_id=<%=Request("rtag_id")%>">
                           <%=rsTemp("pkg_name")%>
                        </a>
                     </td>

                     <td nowrap class="body_txt" background="images/bg_form_lightgray.gif">
                        <%=rsTemp("NumberOfChanges")%>
                     </td>
                  </tr>
                  <%rsTemp.MoveNext
               WEnd
               rsTemp.Close
               Set rsTemp = nothing

            End If
            %>
         </table>
      </td>
   </tr>
</table>
<br>

<%
Call Destroy_All_Objects
%>