secInvMd

Asking for a modular breakdown of each functionality in the same format you've shown:

Foramt : For each functionality, want:

The stored procedure (.sql)

The ASP.NET markup (.aspx)

A natural-language breakdown (.txt)

The VB.NET codebehind logic (.vb)

Below is the complete breakdown — one block per functionality (loading users, adding new user, updating access, cleaning up, etc.)

✅ 1. Load Mapped Users into GridView

-- 1. sp_GetAllUsers: Load existing mapped users
IF OBJECT_ID('dbo.sp_GetAllUsers', 'P') IS NOT NULL
    DROP PROCEDURE dbo.sp_GetAllUsers
GO
CREATE PROCEDURE dbo.sp_GetAllUsers
AS
BEGIN
    SET NOCOUNT ON;
    SELECT 
        username,
        ManageBallotBox,
        ManageEVM
    FROM sec.AppUsersMapping
    ORDER BY username;
END
GO

<asp:GridView ID="gvUserAccess" runat="server" AutoGenerateColumns="false">
    <Columns>
        <asp:BoundField DataField="Username" HeaderText="User ID" />

        <asp:TemplateField HeaderText="Ballot Box Access">
            <ItemTemplate>
                <asp:CheckBox ID="chkBallotBox" runat="server"
                    Checked='<%# Convert.ToString(Eval("ManageBallotBox")) = "Y" %>' />
            </ItemTemplate>
        </asp:TemplateField>

        <asp:TemplateField HeaderText="EVM Access">
            <ItemTemplate>
                <asp:CheckBox ID="chkEVM" runat="server"
                    Checked='<%# Convert.ToString(Eval("ManageEVM")) = "Y" %>' />
            </ItemTemplate>
        </asp:TemplateField>

        <asp:TemplateField HeaderText="Action">
            <ItemTemplate>
                <asp:Button ID="btnSave" runat="server" CommandName="Save"
                            CommandArgument='<%# Eval("Username") %>' Text="Save" />
            </ItemTemplate>
        </asp:TemplateField>
    </Columns>
</asp:GridView>

natural-language instruction breakdown

Load all mapped users by executing sp_GetAllUsers. Bind the returned data to GridView gvUserAccess using gvUserAccess.DataSource = dt and gvUserAccess.DataBind(). GridView contains checkboxes for each access right.

''' Load existing mappings into GridView "Data-binding"
Private Sub LoadMappedUsers()
    Dim dt As New DataTable()

    Try
        Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("SecConnection").ConnectionString)
            Using cmd As New SqlCommand("sp_GetAllUsers", conn)
                cmd.CommandType = CommandType.StoredProcedure
                conn.Open()
                dt.Load(cmd.ExecuteReader())
            End Using
        End Using

        gvUserAccess.DataSource = dt
        gvUserAccess.DataBind()
    Catch ex As Exception
        ClientScript.RegisterStartupScript(Me.GetType(), "alert",
            $"alert('Error loading users: {ex.Message.Replace("'", "\'")}');", True)
    End Try
End Sub

✅ 2. Add New User Mapping via Modal

-- 2. sp_InsertUserAccessIfNotExists: Insert new user with logging
IF OBJECT_ID('dbo.sp_InsertUserAccessIfNotExists', 'P') IS NOT NULL
    DROP PROCEDURE dbo.sp_InsertUserAccessIfNotExists
GO
CREATE PROCEDURE dbo.sp_InsertUserAccessIfNotExists
    @Username NVARCHAR(50),
    @ManageBallotBox CHAR(1),
    @ManageEVM CHAR(1),
    @AssignedBy NVARCHAR(50),
    @LastModifiedIP NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT 1 FROM sec.AppUsersMapping WHERE username = @Username)
        RETURN;

    EXEC dbo.sp_LogUserMappingHistory 
        @Username = @Username,
        @ManageBallotBox = @ManageBallotBox,
        @ManageEVM = @ManageEVM,
        @AssignedBy = @AssignedBy,
        @LastModifiedIP = @LastModifiedIP,
        @ActionType = 'Insert';

    INSERT INTO sec.AppUsersMapping (username, allowedAppCode, ManageBallotBox, ManageEVM)
    VALUES (@Username, 1, @ManageBallotBox, @ManageEVM);
END
GO

<!-- Modal for Adding New User -->
<asp:DropDownList ID="ddlNewUserPopup" runat="server" CssClass="form-control">
    <asp:ListItem Value="">-- Select User to Add --</asp:ListItem>
</asp:DropDownList>

<asp:CheckBox ID="chkPopupBallotBox" runat="server" />
<label>Ballot Box Access</label>

<asp:CheckBox ID="chkPopupEVM" runat="server" />
<label>EVM Access</label>

<asp:Button ID="btnAddPopup" runat="server" Text="Add User" OnClick="btnAddPopup_Click" />

natural-language instruction breakdown

Dropdown is populated with unmapped users from spGetAllUsersNotMapped. When user selects values and clicks "Add User", backend calls spInsertUserAccessIfNotExists. The user access mapping is inserted and logged into history.

''' Click handler for “Add User” in the modal
Protected Sub btnAddPopup_Click(sender As Object, e As EventArgs)
    Dim username As String = ddlNewUserPopup.SelectedValue
    Dim manageBallotBox As String = If(chkPopupBallotBox.Checked, "Y", "N")
    Dim manageEVM As String = If(chkPopupEVM.Checked, "Y", "N")

    If String.IsNullOrEmpty(username) Then
        ClientScript.RegisterStartupScript(Me.GetType(), "alert", "alert('Select a user.');", True)
        Return
    End If

    If manageBallotBox = "N" AndAlso manageEVM = "N" Then
        ClientScript.RegisterStartupScript(Me.GetType(), "alert", "alert('Grant at least one access.');", True)
        Return
    End If

    InsertUserAccessIfNotExists(username, manageBallotBox, manageEVM)
End Sub

Private Sub InsertUserAccessIfNotExists(username As String, ballotBox As String, evm As String)
    Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("SecConnection").ConnectionString)
        Using cmd As New SqlCommand("sp_InsertUserAccessIfNotExists", conn)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@Username", username)
            cmd.Parameters.AddWithValue("@ManageBallotBox", ballotBox)
            cmd.Parameters.AddWithValue("@ManageEVM", evm)
            cmd.Parameters.AddWithValue("@AssignedBy", If(Session("UserID"), "SYSTEM"))
            cmd.Parameters.AddWithValue("@LastModifiedIP", GetClientIP())
            conn.Open()
            cmd.ExecuteNonQuery()
        End Using
    End Using

    LoadMappedUsers()
    PopulateAddUserDropdown()
End Sub

✅ 3. Update Access for Existing User (via Save in GridView)

-- 3. sp_UpdateUserAccess: Update existing user with logging and cleanup
IF OBJECT_ID('dbo.sp_UpdateUserAccess', 'P') IS NOT NULL
    DROP PROCEDURE dbo.sp_UpdateUserAccess
GO
CREATE PROCEDURE dbo.sp_UpdateUserAccess
    @Username NVARCHAR(50),
    @ManageBallotBox CHAR(1),
    @ManageEVM CHAR(1),
    @UpdatedBy NVARCHAR(50),
    @LastModifiedIP NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    EXEC dbo.sp_LogUserMappingHistory 
        @Username = @Username,
        @AssignedBy = @UpdatedBy,
        @LastModifiedIP = @LastModifiedIP,
        @ActionType = 'Update';

    UPDATE sec.AppUsersMapping
    SET ManageBallotBox = @ManageBallotBox,
        ManageEVM = @ManageEVM
    WHERE username = @Username;

    EXEC dbo.sp_CleanupInvalidUserMappings @CleanedBy = @UpdatedBy, @CleanedFromIP = @LastModifiedIP;
END
GO

<!-- Already shown inside gvUserAccess GridView above -->
<asp:Button ID="btnSave" runat="server" CommandName="Save"
            CommandArgument='<%# Eval("Username") %>' Text="Save" />

natural-language instruction breakdown

When Save is clicked in a row, GridView triggers RowCommand → calls sp_UpdateUserAccess. Access values are updated and logged into history. If both values are "N", user may be removed via cleanup.

''' GridView "Save" command
Protected Sub gvUserAccess_RowCommand(sender As Object, e As GridViewCommandEventArgs)
    If e.CommandName = "Save" Then
        HandleSaveExistingUser(e)
    End If
End Sub

Private Sub HandleSaveExistingUser(e As GridViewCommandEventArgs)
    Dim username As String = e.CommandArgument.ToString()
    Dim row As GridViewRow = CType(CType(e.CommandSource, Button).NamingContainer, GridViewRow)

    Dim chkBallotBox As CheckBox = CType(row.FindControl("chkBallotBox"), CheckBox)
    Dim chkEVM As CheckBox = CType(row.FindControl("chkEVM"), CheckBox)

    Dim manageBallotBox As String = If(chkBallotBox.Checked, "Y", "N")
    Dim manageEVM As String = If(chkEVM.Checked, "Y", "N")

    Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings("SecConnection").ConnectionString)
        Using cmd As New SqlCommand("sp_UpdateUserAccess", conn)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@Username", username)
            cmd.Parameters.AddWithValue("@ManageBallotBox", manageBallotBox)
            cmd.Parameters.AddWithValue("@ManageEVM", manageEVM)
            cmd.Parameters.AddWithValue("@UpdatedBy", If(Session("UserID"), "SYSTEM"))
            cmd.Parameters.AddWithValue("@LastModifiedIP", GetClientIP())
            conn.Open()
            cmd.ExecuteNonQuery()
        End Using
    End Using

    LoadMappedUsers()
    PopulateAddUserDropdown()
End Sub

✅ 4. History Logging (Used by Insert/Update/Delete)

-- 4. sp_LogUserMappingHistory: Log entry to history table
IF OBJECT_ID('dbo.sp_LogUserMappingHistory', 'P') IS NOT NULL
    DROP PROCEDURE dbo.sp_LogUserMappingHistory
GO
CREATE PROCEDURE dbo.sp_LogUserMappingHistory
    @Username NVARCHAR(50),
    @ManageBallotBox CHAR(1) = NULL,
    @ManageEVM CHAR(1) = NULL,
    @AssignedBy NVARCHAR(50) = NULL,
    @LastModifiedIP NVARCHAR(50) = NULL,
    @ActionType NVARCHAR(10)
AS
BEGIN
    IF @ActionType = 'Update'
    BEGIN
        INSERT INTO sec.AppUsersMappingHist (...)
        SELECT ..., @AssignedBy, GETDATE(), ..., @ActionType
        FROM sec.AppUsersMapping
        WHERE username = @Username;
    END
    ELSE IF @ActionType = 'Insert'
    BEGIN
        INSERT INTO sec.AppUsersMappingHist (...)
        VALUES (@Username, 1, @ManageBallotBox, @ManageEVM, @AssignedBy, GETDATE(), ..., @ActionType);
    END
END
GO

natural-language instruction breakdown

Before inserting or updating user access, current or new values are logged into a history table. Called from spUpdateUserAccess and spInsertUserAccessIfNotExists.

✅ 5. Cleanup: Auto-Remove Users with No Access

-- 5. sp_CleanupInvalidUserMappings: Remove rows with N,N
IF OBJECT_ID('dbo.sp_CleanupInvalidUserMappings', 'P') IS NOT NULL
    DROP PROCEDURE dbo.sp_CleanupInvalidUserMappings
GO
CREATE PROCEDURE dbo.sp_CleanupInvalidUserMappings
    @CleanedBy NVARCHAR(50),
    @CleanedFromIP NVARCHAR(50)
AS
BEGIN
    DECLARE @Username NVARCHAR(50);
    DECLARE cur CURSOR FOR
        SELECT username FROM sec.AppUsersMapping WHERE ManageBallotBox = 'N' AND ManageEVM = 'N';

    OPEN cur; FETCH NEXT FROM cur INTO @Username;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC dbo.sp_LogUserMappingHistory @Username = @Username, @ActionType = 'Delete', ...
        DELETE FROM sec.AppUsersMapping WHERE username = @Username;
        FETCH NEXT FROM cur INTO @Username;
    END
    CLOSE cur; DEALLOCATE cur;
END
GO

natural-language instruction breakdown

Removes users who have both access flags set to 'N'. For each such user, logs the deletion before removing the row. Called automatically after update.