Imports System.Data.SqlClient
Partial Public Class Admin
    Inherits Functions

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim id As String
        Authenticate()

        id = Trim(Request.QueryString("id"))
        If IsNumeric(id) Then
            Page.Header.Title = ConfigurationManager.AppSettings("title") & "Edit User"
            txtID.Text = id
            If Not Page.IsPostBack Then
                LoadAdmin(id)
                btnDelete.Attributes.Add("onclick", "return confirm('Confirm Delete Of This User?');")
            End If
        Else
            Page.Header.Title = ConfigurationManager.AppSettings("title") & "Add User"
            btnDelete.Visible = False
            If Not Page.IsPostBack Then
                LoadReports("")
            End If

        End If

        txtName.Focus()
    End Sub

    Protected Sub LoadAdmin(ByVal id)
        Dim cmd As New SqlCommand
        Dim DR As SqlDataReader
        Dim DBConn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)

        Try
            cmd.CommandText = ("SELECT * FROM ReportServerUsers WHERE ID = @ID")
            cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ID", SqlDbType.Int)).Value = id
            cmd.Connection = DBConn
            DBConn.Open()
            DR = cmd.ExecuteReader()
            If DR.Read Then
                txtID.Text = id
                txtName.Text = DR("Name")
                txtUserName.Text = DR("UserName")
                txtPassword.Text = DR("Password")
                txtPassword2.Text = DR("Password")
                optUserType.SelectedValue = DR("UserType")
                If txtPassword2.Text <> "" Then
                    Me.RequiredFieldValidator2.Enabled = False
                End If

                chkDisabled.Checked = IIf(DR("InActive") = True, True, False)

                LoadReports(id)

            End If
        Catch ex As Exception
            Master.ShowMessage(ex.ToString)
        Finally
            DBConn.Close()
        End Try

    End Sub

    Protected Sub LoadReports(ByVal id As String)
        Dim cmd As New SqlCommand
        Dim DR As SqlDataReader
        Dim DBConn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
        Try
            cmd.Connection = DBConn
            DBConn.Open()

            If id <> "" Then
                cmd.CommandText = "SELECT ReportServerReports.ID,ReportServerReportCategory.Name AS CategoryName,ReportServerReports.ReportName FROM ReportServerReports LEFT JOIN ReportServerReportCategory ON ReportServerReports.ReportCategoryID=ReportServerReportCategory.ID WHERE (ReportServerReports.InActive = 0) AND ReportServerReports.ID NOT IN (SELECT ReportID FROM ReportServerUserReports WHERE UserID=@ID) ORDER BY ReportServerReportCategory.OrderBy,ReportServerReportCategory.Name,ReportServerReports.OrderBy,ReportServerReports.ReportName"
                cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ID", SqlDbType.Int)).Value = id
            Else
                cmd.CommandText = "SELECT ReportServerReports.ID,ReportServerReportCategory.Name AS CategoryName,ReportServerReports.ReportName FROM ReportServerReports LEFT JOIN ReportServerReportCategory ON ReportServerReports.ReportCategoryID=ReportServerReportCategory.ID WHERE (ReportServerReports.InActive = 0) ORDER BY ReportServerReportCategory.OrderBy,ReportServerReportCategory.Name,ReportServerReports.OrderBy,ReportServerReports.ReportName"
            End If

            DR = cmd.ExecuteReader()
            While DR.Read
                Dim li As New ListItem
                li.Text = DR("CategoryName") & " - " & DR("ReportName")
                li.Value = DR.Item("ID").ToString
                lstAvailableReports.Items.Add(li)
            End While
            DR.Close()

            If id <> "" Then
                cmd.CommandText = "SELECT ReportServerReports.ID,ReportServerReportCategory.Name AS CategoryName,ReportServerReports.ReportName FROM ReportServerReports LEFT JOIN ReportServerReportCategory ON ReportServerReports.ReportCategoryID=ReportServerReportCategory.ID WHERE (ReportServerReports.InActive = 0) AND ReportServerReports.ID IN (SELECT ReportID FROM ReportServerUserReports WHERE UserID=@ID) ORDER BY ReportServerReportCategory.OrderBy,ReportServerReportCategory.Name,ReportServerReports.OrderBy,ReportServerReports.ReportName"
                DR = cmd.ExecuteReader()
                While DR.Read
                    Dim li As New ListItem
                    li.Text = DR("CategoryName") & " - " & DR("ReportName")
                    li.Value = DR.Item("ID").ToString
                    lstCurrentReports.Items.Add(li)
                End While
                DR.Close()
            End If
        Catch ex As Exception
            Master.ShowMessage(ex.ToString)
        Finally
            DBConn.Close()
        End Try

    End Sub

    Protected Sub btnAddReport_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAddReport.Click
        If lstAvailableReports.SelectedIndex >= 0 Then
            lstCurrentReports.Items.Add(lstAvailableReports.SelectedItem)
            lstAvailableReports.Items.RemoveAt(lstAvailableReports.SelectedIndex)
            lstAvailableReports.SelectedIndex = -1
            lstCurrentReports.SelectedIndex = -1
        End If
    End Sub

    Protected Sub btnRemoveReport_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnRemoveReport.Click
        If lstCurrentReports.SelectedIndex >= 0 Then
            lstAvailableReports.Items.Add(lstCurrentReports.SelectedItem)
            lstCurrentReports.Items.RemoveAt(lstCurrentReports.SelectedIndex)
            lstAvailableReports.SelectedIndex = -1
            lstCurrentReports.SelectedIndex = -1
        End If

    End Sub

    Protected Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
        Dim id As String
        id = txtID.Text
        If CheckPassword(txtPassword.Text, txtPassword2.Text, txtUserName.Text) Then
            If Not UsernameExists(txtUserName.Text, id) Then
                Dim cmd As New System.Data.SqlClient.SqlCommand
                Dim DBConn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)

                Try
                    cmd.Connection = DBConn
                    DBConn.Open()
                    If txtID.Text <> "" Then
                        If (txtPassword.Text <> "") And (txtPassword.Text <> Me.txtPassword2.Text) Then
                            cmd.CommandText = ("UPDATE ReportServerUsers SET Name=@Name,UserName=@UserName,Password=@Password,InActive=@InActive,UserType=@UserType WHERE ID = @ID")
                        Else
                            cmd.CommandText = ("UPDATE ReportServerUsers SET Name=@Name,UserName=@UserName,InActive=@InActive,UserType=@UserType WHERE ID = @ID")
                        End If
                        cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ID", SqlDbType.Int)).Value = txtID.Text
                    Else
                        cmd.CommandText = ("INSERT INTO ReportServerUsers (Name,Username,Password,InActive,UserType) VALUES(@Name,@Username,@Password,@InActive,@UserType);select scope_identity();")
                    End If
                    cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@UserType", SqlDbType.VarChar)).Value = optUserType.SelectedValue
                    cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Name", SqlDbType.VarChar)).Value = txtName.Text
                    cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Username", SqlDbType.VarChar)).Value = txtUserName.Text
                    cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@Password", SqlDbType.VarChar)).Value = txtPassword.Text
                    cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@InActive", SqlDbType.Bit)).Value = IIf(chkDisabled.Checked, 1, 0)
                    If txtID.Text <> "" Then
                        cmd.ExecuteNonQuery()
                    Else
                        id = cmd.ExecuteScalar()
                    End If

                    cmd.CommandText = "DELETE FROM ReportServerUserReports WHERE UserID=@UserID"
                    cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@UserID", SqlDbType.Int)).Value = id
                    cmd.ExecuteNonQuery()

                    cmd.CommandText = "INSERT INTO ReportServerUserReports (ReportID,UserID) VALUES (@ReportID,@UserID)"
                    cmd.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ReportID", SqlDbType.Int)).Value = 0
                    For i = 0 To lstCurrentReports.Items.Count - 1
                        cmd.Parameters("@ReportID").Value = lstCurrentReports.Items(i).Value
                        cmd.ExecuteNonQuery()
                    Next

                Catch ex As Exception
                    Master.ShowMessage(ex.ToString)
                Finally
                    DBConn.Close()
                    Response.Redirect("/admin/Admins.aspx")
                End Try
            Else
                Master.ShowMessage("Username already exists")
            End If
        Else
            Master.ShowMessage("Invalid Password")
        End If
    End Sub

    Protected Function CheckPassword(ByVal PW1 As String, ByVal PW2 As String, ByVal UserName As String)
        If (PW1 = "") And (PW2 <> "") Then
            CheckPassword = True
        Else
            If (Len(PW1) >= 4) And (PW1 <> UserName) Then
                CheckPassword = True
            Else
                CheckPassword = False
            End If
        End If
    End Function

    Protected Function UsernameExists(ByVal UserName As String, ByVal id As String) As Boolean
        Dim cmd As New SqlCommand
        Dim UserCount As Integer
        Dim DBConn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)

        Try
            If id <> "" Then
                cmd.CommandText = ("SELECT COUNT(ID) FROM ReportServerUsers WHERE Username = @UserName AND ID <> @ID")
                cmd.Parameters.Add(New SqlParameter("@ID", SqlDbType.Int)).Value = id
            Else
                cmd.CommandText = ("SELECT COUNT(ID) FROM ReportServerUsers WHERE Username = @UserName")
            End If
            cmd.Parameters.Add(New SqlParameter("@UserName", SqlDbType.VarChar)).Value = UserName
            cmd.Connection = DBConn
            DBConn.Open()
            UserCount = cmd.ExecuteScalar
            If UserCount <> 0 Then
                UsernameExists = True
            Else
                UsernameExists = False
            End If
        Catch ex As Exception
            UsernameExists = False
            Master.ShowMessage(ex.ToString)
        Finally
            DBConn.Close()
        End Try
    End Function

    Protected Sub btnCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCancel.Click
        Response.Redirect("/admin/Admins.aspx")
    End Sub

    Protected Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        Dim cmd As New SqlCommand
        Dim DBConn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)

        Try
            cmd.CommandText = ("DELETE FROM ReportServerUsers WHERE ID = @ID")
            cmd.Parameters.Add(New SqlParameter("@ID", SqlDbType.BigInt)).Value = txtID.Text
            cmd.Connection = DBConn
            DBConn.Open()
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            Master.ShowMessage(ex.ToString)
        Finally
            DBConn.Close()
            Response.Redirect("/admin/Admins.aspx")
        End Try
    End Sub



End Class