<%@ Page Language="VB" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.OleDB" %>
<%@ Register TagPrefix="uc0" TagName="GetPM" Src="GetPM.ascx"%>
<script runat="server">
Public StaffCode As String
Public Age As String
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Dim NetworkUser As String
NetworkUser = Page.User.Identity.Name
Dim CurrUser As String
CurrUser = Right(NetworkUser, 8)
Dim dbConn As OleDbConnection
Dim myConnectionString As String = ConfigurationManager.ConnectionStrings("MyDBConnection").ToString
dbConn = New OleDbConnection(myConnectionString)
dbConn.Open()
Dim myCommand As New OleDbCommand("SELECT [Name] FROM [ActiveCPAdmins] WHERE [Username] = @Username", dbConn)
myCommand.Parameters.Add("@Username", OleDbType.VarChar, 10)
myCommand.Parameters("@Username").Value = CurrUser
Dim myReader As OleDbDataReader = myCommand.ExecuteReader()
myReader.Read()
If myReader.HasRows = True Then
If Not IsPostBack Then
drpAgeList.ClearSelection()
drpAgeList.Items.Add("Young")
drpAgeList.Items.Add("Adult")
Dim myCommand1 As New OleDbCommand("SELECT [PM], [StaffCode] FROM [PeopleUsingWithCoursesRolled]", dbConn)
Dim myReader1 As OleDbDataReader = myCommand1.ExecuteReader()
While myReader1.Read()
drpPMList.ClearSelection()
Dim newListItem As New ListItem()
newListItem.Text = myReader1.GetString(0)
newListItem.Value = myReader1.GetString(1)
drpPMList.Items.Add(newListItem)
End While
End If
Else
Response.Redirect("GenError.aspx?ErrorCode=1")
End If
myReader.Close()
dbConn.Close()
End Sub
Sub rpCPDetail_ItemCommand(ByVal sender As Object, ByVal e As RepeaterCommandEventArgs)
End Sub
Private Function IsEntered(ByVal yoItsAParam As Boolean) As String
Dim WriteBack As String
If yoItsAParam = True Then
WriteBack = "<img src='Images/greenTick.gif' />"
Else
WriteBack = "<img src='Images/redX.gif' />"
End If
Return (WriteBack)
End Function
Protected Sub cmdView_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim dbConn As OleDbConnection
Dim myConnectionString As String = ConfigurationManager.ConnectionStrings("MyDBConnection").ToString
dbConn = New OleDbConnection(myConnectionString)
dbConn.Open()
Dim TempTable As New DataTable()
Dim Row As DataRow
Dim da As OleDbDataAdapter
TempTable.Columns.Add("aos_code", Type.GetType("System.String"))
TempTable.Columns.Add("Groups", Type.GetType("System.String"))
TempTable.Columns.Add("YOS", Type.GetType("System.String"))
TempTable.Columns.Add("Term", Type.GetType("System.String"))
TempTable.Columns.Add("Starts", Type.GetType("System.String"))
TempTable.Columns.Add("Retained", Type.GetType("System.String"))
TempTable.Columns.Add("Achieved", Type.GetType("System.String"))
TempTable.Columns.Add("Keyed", Type.GetType("System.Boolean"))
TempTable.Columns.Add("FullDesc", Type.GetType("System.String"))
StaffCode = drpPMList.SelectedItem.Value
Age = drpAgeList.SelectedItem.Value
Dim strSQLQuery As String
strSQLQuery = "SELECT [0002A].AosCode, [0002A].Groups, [0002A].YOS, [0002A].Term, [0002A].Starts, [0002A].Retention, [0002A].Achievement, [0002A].MaxOfKeyed, dbo_stcstatd.full_desc FROM 0002A INNER JOIN dbo_stcstatd ON [0002A].AosCode = dbo_stcstatd.aos_code WHERE [0002A].StaffCode=@staffcode AND [0002A].Age=@Age;"
da = New OleDbDataAdapter(strSQLQuery, dbConn)
da.SelectCommand.Parameters.Add("@staffcode", OleDbType.VarChar, 10)
da.SelectCommand.Parameters.Add("@Age", OleDbType.VarChar, 10)
da.SelectCommand.Parameters("@staffcode").Value = StaffCode
da.SelectCommand.Parameters("@Age").Value = Age
Dim dtSearch As DataTable = New DataTable
da.Fill(dtSearch)
Dim dr As DataRow
For Each dr In dtSearch.Rows
Row = TempTable.NewRow()
Row.BeginEdit()
Row("aos_code") = dr("aoscode")
Row("Groups") = dr("Groups")
Row("YOS") = dr("YOS")
Row("Term") = dr("Term")
Row("Starts") = dr("Starts")
Row("Retained") = dr("Retention")
Row("Achieved") = dr("Achievement")
Row("Keyed") = dr("MaxOfKeyed")
Row("FullDesc") = dr("full_desc")
Row.EndEdit()
TempTable.Rows.Add(Row)
Next
TempTable.AcceptChanges()
Dim RowsCount As Integer
RowsCount = TempTable.Rows.Count
If RowsCount = 0 Then
lblOutput.Text = "No courses found"
cmdSave.Visible = False
cmdUnLock.Visible = False
cmdLock.Visible = False
Else
lblOutput.Text = RowsCount & " Rows"
cmdSave.Visible = True
cmdUnLock.Visible = False
cmdLock.Visible = True
End If
rpCPDetail.DataSource = TempTable
rpCPDetail.DataBind()
dbConn.Close()
End Sub
Protected Sub cmdSearch_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim dbConn As OleDbConnection
Dim myConnectionString As String = ConfigurationManager.ConnectionStrings("MyDBConnection").ToString
dbConn = New OleDbConnection(myConnectionString)
dbConn.Open()
Dim AosCode As String
AosCode = txtSearch.Text
Dim TempTable As New DataTable()
Dim Row As DataRow
Dim da As OleDbDataAdapter
TempTable.Columns.Add("aos_code", Type.GetType("System.String"))
TempTable.Columns.Add("Groups", Type.GetType("System.String"))
TempTable.Columns.Add("YOS", Type.GetType("System.String"))
TempTable.Columns.Add("Term", Type.GetType("System.String"))
TempTable.Columns.Add("Starts", Type.GetType("System.String"))
TempTable.Columns.Add("Retained", Type.GetType("System.String"))
TempTable.Columns.Add("Achieved", Type.GetType("System.String"))
TempTable.Columns.Add("Keyed", Type.GetType("System.Boolean"))
TempTable.Columns.Add("FullDesc", Type.GetType("System.String"))
Dim strSQLQuery As String
strSQLQuery = "SELECT [0002A].AosCode, [0002A].Groups, [0002A].YOS, [0002A].Term, [0002A].Starts, [0002A].Retention, [0002A].Achievement, [0002A].MaxOfKeyed, dbo_stcstatd.full_desc, [0002A].StaffCode FROM 0002A INNER JOIN dbo_stcstatd ON [0002A].AosCode = dbo_stcstatd.aos_code WHERE (((Mid([AosCode],1,6)) Like [@AosCode]));"
da = New OleDbDataAdapter(strSQLQuery, dbConn)
da.SelectCommand.Parameters.Add("@AosCode", OleDbType.VarChar, 10)
da.SelectCommand.Parameters("@AosCode").Value = AosCode
Dim dtSearch As DataTable = New DataTable
da.Fill(dtSearch)
Dim dr As DataRow
For Each dr In dtSearch.Rows
Row = TempTable.NewRow()
Row.BeginEdit()
Row("aos_code") = dr("aoscode")
Row("Groups") = dr("Groups")
Row("YOS") = dr("YOS")
Row("Term") = dr("Term")
Row("Starts") = dr("Starts")
Row("Retained") = dr("Retention")
Row("Achieved") = dr("Achievement")
Row("Keyed") = dr("MaxOfKeyed")
Row("FullDesc") = dr("full_desc")
Row.EndEdit()
TempTable.Rows.Add(Row)
Next
Dim RowsCount As Integer
RowsCount = TempTable.Rows.Count
If RowsCount = 0 Then
lblOutput.Text = "No courses found"
cmdSave.Visible = False
cmdUnLock.Visible = False
cmdLock.Visible = False
Else
lblOutput.Text = RowsCount & " Rows"
cmdSave.Visible = True
cmdUnLock.Visible = False
cmdLock.Visible = True
End If
TempTable.AcceptChanges()
rpCPDetail.DataSource = TempTable
rpCPDetail.DataBind()
dbConn.Close()
End Sub
Protected Sub cmdLock_Click(ByVal sender As Object, ByVal e As System.EventArgs)
cmdLock.Visible = False
cmdUnLock.Visible = True
Dim NetworkUser As String
NetworkUser = Page.User.Identity.Name
Dim CurrUser As String
CurrUser = Right(NetworkUser, 8)
StaffCode = drpPMList.SelectedItem.Value
Dim dbConn As OleDbConnection
Dim myConnectionString As String = ConfigurationManager.ConnectionStrings("MyDBConnection").ToString
dbConn = New OleDbConnection(myConnectionString)
dbConn.Open()
Dim myCommand4 As New OleDbCommand("UPDATE [ActivePMs] SET [Locked] = TRUE, LockedBy = @Username WHERE [StaffCode] = @StaffCode", dbConn)
myCommand4.Parameters.Add("@Username", OleDbType.VarChar, 10)
myCommand4.Parameters.Add("@StaffCode", OleDbType.VarChar, 10)
myCommand4.Parameters("@Username").Value = CurrUser
myCommand4.Parameters("@StaffCode").Value = StaffCode
myCommand4.ExecuteNonQuery()
dbConn.Close()
End Sub
Protected Sub cmdUnLock_Click(ByVal sender As Object, ByVal e As System.EventArgs)
cmdLock.Visible = True
cmdUnLock.Visible = False
StaffCode = drpPMList.SelectedItem.Value
Dim dbConn As OleDbConnection
Dim myConnectionString As String = ConfigurationManager.ConnectionStrings("MyDBConnection").ToString
dbConn = New OleDbConnection(myConnectionString)
dbConn.Open()
Dim myCommand4 As New OleDbCommand("UPDATE [ActivePMs] SET [Locked] = FALSE, LockedBy = '' WHERE [StaffCode] = @StaffCode", dbConn)
myCommand4.Parameters.Add("@StaffCode", OleDbType.VarChar, 10)
myCommand4.Parameters("@StaffCode").Value = StaffCode
myCommand4.ExecuteNonQuery()
dbConn.Close()
End Sub
Protected Sub cmdSave_Click(ByVal sender As Object, ByVal e As System.EventArgs)
Dim Key As String
Dim CurrentID As String
For Each Key In Request.Form.Keys
If Key.StartsWith("sess_txtKeyed_") Then
CurrentID = Right(Key, 7)
Dim dbConn As OleDbConnection
Dim myConnectionString As String = ConfigurationManager.ConnectionStrings("MyDBConnection").ToString
dbConn = New OleDbConnection(myConnectionString)
dbConn.Open()
Dim myCommand4 As New OleDbCommand("UPDATE [CPFile_Sess] SET [Keyed] = TRUE WHERE [CPFileSess] = @ID", dbConn)
myCommand4.Parameters.Add("@ID", OleDbType.VarChar, 10)
myCommand4.Parameters("@ID").Value = CurrentID
myCommand4.ExecuteNonQuery()
dbConn.Close()
End If
Next
End Sub
</script>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<title>Curriculum Planning Software</title>
<link href="CSS/CPlan.css" type="text/css" rel="stylesheet" />
<meta http-equiv="expires" content="Wed, 19 Feb 2003 08:00:00 GMT" />
</head>
<body leftmargin="0" topmargin="0" rightmargin="0">
<div style="BORDER-RIGHT: gainsboro 1px solid; BORDER-TOP: gainsboro 1px solid; BORDER-LEFT: gainsboro 1px solid; BORDER-BOTTOM: gainsboro 1px solid; BACKGROUND-COLOR: gainsboro">Proposed
Course File for Programme Managers split down by Young and Adult
</div>
<form id="Form1" runat="server">
<div class="container">
<div class="topbar"><img alt="CP Banner" src="Images/Curriculum-planning-banner.jpg" />
<a href="CourseFileTeamSessionsUnKeyed.aspx">Stats</a>
<asp:Button ID="cmdLock" runat="server" Text="Lock Course File" OnClick="cmdLock_Click" Visible="false" />
<asp:Button id="cmdUnLock" onclick="cmdUnLock_Click" runat="server" Text="UnLock Course File" Visible="false"></asp:Button>
</div>
<div class="content">
<div class="middle">PM List
<asp:DropDownList id="drpPMList" runat="server"></asp:DropDownList>
Age Group
<asp:DropDownList id="drpAgeList" runat="server"></asp:DropDownList>
<asp:Button id="cmdView" onclick="cmdView_Click" runat="server" Text="View"></asp:Button>
Search via 6 character Aos_Code
<asp:TextBox id="txtSearch" runat="server"></asp:TextBox>
<asp:Button id="cmdSearch" onclick="cmdSearch_Click" runat="server" Text="Search"></asp:Button>
<asp:Label ID="lblOutput" runat="server"></asp:Label>
<asp:Repeater id="rpCPDetail" runat="server" OnItemCommand="rpCPDetail_ItemCommand">
<HeaderTemplate>
<table width="100%" border="1" bordercolor="white">
<tr>
<td>
<p>
<b>Session</b>
</p>
</td>
<td>
<p>
<b>Location</b>
</p>
</td>
<td>
<p>
<b>Qual Start Date</b>
</p>
</td>
<td>
<p>
<b>Qual End Date</b>
</p>
</td>
<td>
<p>
<b>Aca Start Date</b>
</p>
</td>
<td>
<p>
<b>Aca End Date</b>
</p>
</td>
<td>
<p>
<b>Day</b>
</p>
</td>
<td>
<p>
<b>Times</b>
</p>
</td>
<td>
<p>
<b>Post Code</b>
</p>
</td>
<td>
<p>
<b>Qual Aim</b>
</p>
</td>
<td>
<p>
<b>Waiver</b>
</p>
</td>
<td>
<p>
<b>Return Ind</b>
</p>
</td>
<td>
<p>
<b>FSteam</b>
</p>
</td>
<td>
<p>
<b>GLH</b>
</p>
</td>
<td>
<p><b>Keyed</b></p>
</td>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr>
<td class="detColDesc" colspan="9">
<p>
<%#Container.DataItem("aos_code") %> (Year <%#Container.DataItem("YOS")%> & Term <%#Container.dataitem("Term") %> )
- <%#Container.DataItem("FullDesc") %> - TARGETS - STARTS=<%#Container.DataItem("Starts")%> RET=<%#Container.dataitem("Retained") %> ACH=<%#Container.DataItem("Achieved")%>
</p>
<uc0:GetPM ID="GetPM" runat="server" GetAosCode=<%#container.dataitem("aos_code") %> GetTerm=<%#container.dataitem("Term") %> GetYOS=<%#container.dataitem("YOS") %>/>
</td>
</tr>
</ItemTemplate>
</asp:Repeater>
</div>
<asp:Button ID="cmdSave" runat="server" Text="Save" OnClick="cmdSave_Click" Visible="false" />
</div>
</div>
</form>
</body>
</html>