Imports System.Data.OleDb
Imports AcggDoc.Acgg.dbClass

Namespace Acgg

    Public Class Users
        Inherits System.Web.UI.Page

#Region " Web Form Designer Generated Code "

        'This call is required by the Web Form Designer.
        <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()

        End Sub
        Protected WithEvents Label1 As System.Web.UI.WebControls.Label
        Protected WithEvents Label3 As System.Web.UI.WebControls.Label
        Protected WithEvents Label2 As System.Web.UI.WebControls.Label
        Protected WithEvents cmdOk As System.Web.UI.WebControls.Button
        Protected WithEvents txtPwd1 As System.Web.UI.WebControls.TextBox
        Protected WithEvents txtName As System.Web.UI.WebControls.TextBox
        Protected WithEvents txtUserID As System.Web.UI.WebControls.TextBox
        Protected WithEvents Label4 As System.Web.UI.WebControls.Label
        Protected WithEvents txtPwd2 As System.Web.UI.WebControls.TextBox
        Protected WithEvents Label5 As System.Web.UI.WebControls.Label
        Protected WithEvents cmbUserCat As System.Web.UI.WebControls.DropDownList
        Protected WithEvents Label6 As System.Web.UI.WebControls.Label
        Protected WithEvents txtPhone As System.Web.UI.WebControls.TextBox
        Protected WithEvents Label7 As System.Web.UI.WebControls.Label
        Protected WithEvents txtEmail As System.Web.UI.WebControls.TextBox
        Protected WithEvents Label9 As System.Web.UI.WebControls.Label
        Protected WithEvents lnkRefresh As System.Web.UI.WebControls.HyperLink
        Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
        Protected WithEvents cmdCancel As System.Web.UI.WebControls.Button
        Protected WithEvents lblError As System.Web.UI.WebControls.Label
        Protected WithEvents lblCap As System.Web.UI.WebControls.Label
        Protected WithEvents Label8 As System.Web.UI.WebControls.Label
        Protected WithEvents cmbActive As System.Web.UI.WebControls.DropDownList

        'NOTE: The following placeholder declaration is required by the Web Form Designer.
        'Do not delete or move it.
        Private designerPlaceholderDeclaration As System.Object

        Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
            'CODEGEN: This method call is required by the Web Form Designer
            'Do not modify it using the code editor.
            InitializeComponent()
        End Sub

#End Region
        'Dim sConnString As String

        Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
            'Put user code to initialize the page here
            'sConnString = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=1;Jet OLEDB:Database Password=;Data Source=" & Server.MapPath("AcggDoc.mdb") & ";Password=;Jet OLEDB:Engine Type=5;Jet OLEDB:Global Bulk Transactions=1;Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:System database=;Jet OLEDB:SFP=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:New Database Password=;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLEDB:Encrypt Database=False"

            'cmdDeactvate.Attributes.Add("onclick", "return confirm('Are you sure you want to deactivate this user?');")

            If Session("UserId") = "" Then Response.End()

            'If Session("UserCat") = 1 Or Session("UserCat") = 2 Then
            'cmdOk.Enabled = True
            'Else
            '    Response.Redirect("Login.aspx")
            'End If


            If Not Page.IsPostBack Then
                BindUserTypeCombo()
                BindGrid()

                If Request.QueryString("UserId") <> "" Then
                    lblCap.Text = "Edit user details..."
                    lblCap.ForeColor = System.Drawing.Color.Red
                    cmdOk.Text = "Edit"
                    PopulateFields()
                Else
                    lblCap.Text = "Add user details..."
                    lblCap.ForeColor = System.Drawing.Color.SteelBlue
                    cmdOk.Text = "Add"
                End If
            End If

        End Sub

        Private Sub PopulateFields()
            Dim objDBClass As New Acgg.dbClass
            Dim objRead As OleDbDataReader
            Dim strQuery As String = "SELECT UserId,Name,Pwd,UserCat,Phone,Email,Active FROM Users WHERE UserId='" & Request.QueryString("UserId") & "'"
            Try
                'objDBClass.strConn = sConnString
                objDBClass.OpenDB()
                objRead = objDBClass.retReader(strQuery)
                cmbUserCat.ClearSelection()
                cmbActive.ClearSelection()
                Do While objRead.Read()
                    txtUserID.Text = objRead("UserID")
                    txtUserID.Enabled = False
                    txtName.Text = objRead("Name")
                    txtPwd1.Text = objRead("Pwd")
                    txtPwd2.Text = objRead("Pwd")
                    txtPhone.Text = objRead("Phone")
                    txtEmail.Text = objRead("Email")
                    cmbUserCat.Items.FindByValue(objRead("UserCat")).Selected = True
                    cmbActive.Items.FindByValue(objRead("Active")).Selected = True
                Loop
                objRead.Close()
                objDBClass.CloseDB()

            Catch Exc As Exception
                lblError.Text = "Error: " & Exc.Message
            Finally
                objRead = Nothing
                objDBClass = Nothing
            End Try
        End Sub

        Private Sub BindGrid()
            Dim objDBClass As New Acgg.dbClass
            Dim strQuery As String = "SELECT A.UserId,A.Name,A.Email,B.CatDesc FROM Users A,UserCategory B WHERE A.UserCat=B.UserCatId"
            Try
                objDBClass.OpenDB()
                DataGrid1.DataSource = objDBClass.retDataSet(strQuery)
                DataGrid1.DataBind()
                objDBClass.CloseDB()
                txtName.Text = ""
                txtUserID.Text = ""
                txtPwd1.Text = ""
                txtPwd2.Text = ""
                txtPhone.Text = ""
                txtEmail.Text = ""


            Catch Exc As Exception
                lblError.Text = "Error: " & Exc.Message
            Finally
                objDBClass = Nothing
            End Try
        End Sub

        Private Sub BindUserTypeCombo()
            Dim objDBClass As New Acgg.dbClass
            Dim strQuery As String = "SELECT UserCatId,CatDesc FROM UserCategory"
            Try
                objDBClass.OpenDB()
                cmbUserCat.DataSource = objDBClass.retReader(strQuery)
                cmbUserCat.DataMember = "UserCatId"
                cmbUserCat.DataTextField = "CatDesc"
                cmbUserCat.DataValueField = "UserCatId"
                cmbUserCat.DataBind()
                objDBClass.CloseDB()
            Catch Exc As Exception
                lblError.Text = "Error: " & Exc.Message
            Finally
                objDBClass = Nothing
            End Try

        End Sub

        Private Sub cmdOk_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdOk.Click
            Dim objDBClass As New Acgg.dbClass
            'Dim objRead As OleDb.OleDbDataReader
            Dim strQuery As String

            Try
                If boolValidateFields() = False Then
                    lblError.Text = "You have missed out on one or more compulsory fields."
                    Exit Sub
                End If
                If Request("UserId") = "" Then
                    If CheckUserID() Then
                        lblError.Text = "Duplicate UserId..."
                        Exit Try
                    Else
                        strQuery = "INSERT INTO Users(UserId,Name,Pwd,UserCat,Phone,Email,Active) VALUES('" & Request("txtUserID") & "','" & Request("txtName") & "','" & Request("txtPwd1") & "'," & Request("cmbUserCat") & ",'" & Request("txtPhone") & "','" & Request("txtEmail") & "'," & Request("cmbActive") & ")"
                    End If
                Else
                    strQuery = "UPDATE Users SET Name='" & Replace(Request("txtName"), "'", "") & "',Pwd='" & Replace(Request("txtPwd1"), "'", "") & "',UserCat=" & Request("cmbUserCat") & ",Phone='" & Replace(Request("txtPhone"), "'", "") & "',Email='" & Replace(Request("txtEmail"), "'", "") & "',Active=" & Request("cmbActive") & " WHERE UserId='" & Request("UserId") & "'"
                End If

                objDBClass.OpenDB()
                If objDBClass.boolManipulateData(strQuery) Then
                    lblError.Text = "Updation successful..."
                End If
                objDBClass.CloseDB()
                Response.Redirect("Users.aspx")
                BindGrid()

            Catch Exc As Exception
                lblError.Text = "Error: " & Exc.Message
            Finally
                'objRead = Nothing
                objDBClass = Nothing
            End Try
        End Sub

        Function CheckUserID() As Boolean
            Dim objDBClass As New Acgg.dbClass
            Dim objRead As OleDb.OleDbDataReader
            Dim strQuery As String

            Try
                strQuery = "SELECT UserID FROM Users WHERE UserID='" & txtUserID.Text & "'"
                objDBClass.OpenDB()
                objRead = objDBClass.retReader(strQuery)
                Return objRead.HasRows
                objDBClass.CloseDB()

            Catch Exc As Exception
                lblError.Text = "Error: " & Exc.Message
            Finally
                objRead = Nothing
                objDBClass = Nothing
            End Try

        End Function

        Function boolValidateFields() As Boolean
            If Trim(txtName.Text) = "" Or Trim(txtUserID.Text) = "" Or Trim(txtPwd1.Text) = "" Or Trim(txtPwd2.Text) = "" Or Trim(txtEmail.Text) = "" Then
                boolValidateFields = False
                Exit Function
            Else
                boolValidateFields = True
            End If
        End Function

        Private Sub cmdCancel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdCancel.Click
            Response.Redirect("Users.aspx")
        End Sub

        Sub Paginate(ByVal Source As Object, ByVal E As DataGridPageChangedEventArgs)
            DataGrid1.CurrentPageIndex = E.NewPageIndex
            BindGrid()
        End Sub
        Sub SortData(ByVal Sender As Object, ByVal e As DataGridSortCommandEventArgs)
            BindGridCol(e.SortExpression)
        End Sub
        Private Sub BindGridCol(ByVal colname As String)
            Dim objDBClass As New Acgg.dbClass
            Dim strQuery As String = "SELECT A.UserId,A.Name,A.Email,B.CatDesc FROM Users A,UserCategory B WHERE A.UserCat=B.UserCatId order by " & colname
            Try
                objDBClass.OpenDB()
                DataGrid1.DataSource = objDBClass.retDataSet(strQuery)
                DataGrid1.DataBind()
                objDBClass.CloseDB()
                txtName.Text = ""
                txtUserID.Text = ""
                txtPwd1.Text = ""
                txtPwd2.Text = ""
                txtPhone.Text = ""
                txtEmail.Text = ""
            Catch Exc As Exception
                lblError.Text = "Error: " & Exc.Message
            Finally
                objDBClass = Nothing
            End Try
        End Sub

        Private Sub DataGrid1_PageIndexChanged(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles DataGrid1.PageIndexChanged

        End Sub

        Private Sub DataGrid1_ItemCreated(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DataGridItemEventArgs) Handles DataGrid1.ItemCreated
            If e.Item.ItemType = ListItemType.Pager Then
                Dim oControl As Control
                Dim x As Integer
                x = 0

                For Each oControl In e.Item.Cells(0).Controls
                    If x > 0 Then Exit For
                    If oControl.GetType.ToString = "System.Web.UI.WebControls.DataGridLinkButton" Then
                        CType(oControl, LinkButton).Text = "Page " & CType(oControl, LinkButton).Text
                        x = x + 1
                    ElseIf TypeOf oControl Is Label Then
                        CType(oControl, Label).Text = "Page " & CType(oControl, Label).Text
                        x = x + 1
                    End If
                Next
            End If
        End Sub
    End Class
End Namespace