Views: 0

我請Codex寫了一個Group_Code維護介面，我之前訓練好的Codex完美的完全任務。

說明

這是我測試Codex的能力，若有個資料表，要讓Codex進行CRUD操作，它可以需要人工介入改Code的狀態完成。

程式碼

建議放在CDS/Tools下

TB_EB_GROUP.aspx

<%@ Page Title="" Language="C#" MasterPageFile="~/Master/DefaultMasterPage.master" AutoEventWireup="true" CodeFile="TB_EB_GROUP.aspx.cs" Inherits="CDS_Tools_TB_EB_GROUP" %>
<%@ Register TagPrefix="Fast" Namespace="Ede.Uof.Utility.Component" Assembly="Ede.Uof.Utility.Component.Grid" %>

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" runat="Server">
    <style>
        .group-code-message {
            display: block;
            margin: 8px 0;
            font-weight: bold;
        }

        .group-code-search {
            margin: 8px 0 12px 0;
        }

        .group-code-search input[type="text"] {
            width: 180px;
            margin-right: 8px;
        }
    </style>

    <asp:UpdatePanel ID="UpdatePanel1" runat="server" UpdateMode="Conditional">
        <ContentTemplate>
            <asp:Label ID="lblMessage" runat="server" CssClass="group-code-message" />

            <div class="group-code-search">
                群組名稱
                <asp:TextBox ID="txtSearchGroupName" runat="server" />
                GROUP_CODE
                <asp:TextBox ID="txtSearchGroupCode" runat="server" />
                組織
                <asp:DropDownList ID="ddlGroupTypeFilter" runat="server">
                    <asp:ListItem Text="全部" Value="all" Selected="True" />
                    <asp:ListItem Text="內部會員" Value="Department" />
                    <asp:ListItem Text="外部會員" Value="Group" />
                </asp:DropDownList>
                啟用狀態
                <asp:DropDownList ID="ddlActiveFilter" runat="server">
                    <asp:ListItem Text="顯示全部" Value="all" />
                    <asp:ListItem Text="顯示啟用" Value="1" Selected="True" />
                    <asp:ListItem Text="顯示停用" Value="0" />
                </asp:DropDownList>
                <asp:Button ID="btnSearch" runat="server" Text="查詢" OnClick="btnSearch_Click" CausesValidation="false" />
                <asp:Button ID="btnClearSearch" runat="server" Text="清除" OnClick="btnClearSearch_Click" CausesValidation="false" />
            </div>

            <Fast:Grid ID="gvItems" runat="server"
                Width="100%"
                CssClass="gv fixhead"
                AutoGenerateColumns="false"
                AutoGenerateCheckBoxColumn="False"
                AllowPaging="true"
                AllowSorting="true"
                PageSize="30"
                DataKeyNames="GROUP_ID"
                OnPageIndexChanging="gvItems_PageIndexChanging"
                OnRowEditing="gvItems_RowEditing"
                OnRowUpdating="gvItems_RowUpdating"
                OnRowCancelingEdit="gvItems_RowCancelingEdit">
                <Columns>
                    <asp:BoundField HeaderText="GROUP_ID" DataField="GROUP_ID" ItemStyle-Width="260px" ReadOnly="true" />
                    <asp:BoundField HeaderText="類別" DataField="GROUP_TYPE" ItemStyle-Width="80px" ReadOnly="true" />
                    <asp:BoundField HeaderText="群組名稱" DataField="TREE_GROUP_NAME" ItemStyle-Width="260px" ReadOnly="true" />
                    <asp:BoundField HeaderText="啟用" DataField="ACTIVE_TEXT" ItemStyle-Width="60px" ReadOnly="true" />
                    <asp:BoundField HeaderText="公司別ID" DataField="COMPANY_ID" ItemStyle-Width="160px" ReadOnly="true" />
                    <asp:TemplateField HeaderText="GROUP_CODE" ItemStyle-Width="160px">
                        <ItemTemplate>
                            <asp:Label ID="lblGridGroupCode" runat="server" Text='<%# Bind("GROUP_CODE") %>' />
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:TextBox ID="txtGridGroupCode" runat="server" Text='<%# Bind("GROUP_CODE") %>' MaxLength="50" Width="140px" />
                        </EditItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="操作" ItemStyle-Width="120px">
                        <ItemTemplate>
                            <asp:Button ID="btnEdit" runat="server" Text="編輯" CommandName="Edit" CausesValidation="false" />
                        </ItemTemplate>
                        <EditItemTemplate>
                            <asp:Button ID="btnUpdate" runat="server" Text="更新" CommandName="Update" CausesValidation="false" />
                            <asp:Button ID="btnCancelUpdate" runat="server" Text="取消" CommandName="Cancel" CausesValidation="false" />
                        </EditItemTemplate>
                    </asp:TemplateField>
                </Columns>
            </Fast:Grid>
        </ContentTemplate>
    </asp:UpdatePanel>
</asp:Content>

TB_EB_GROUP.aspx.cs

using Ede.Uof.Utility.Page;
using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.WebControls;

public partial class CDS_Tools_TB_EB_GROUP : BasePage
{
    private readonly string _connectionString = ConfigurationManager.ConnectionStrings["connectionstring"].ConnectionString;

    /// <summary>
    /// 初始化 UOF 線上模組與麵包屑，第一次載入時綁定組織清單。
    /// </summary>
    protected void Page_Load(object sender, EventArgs e)
    {
        Ede.Uof.Utility.OnlineManagement.OnlineUser.ChangeModule(
            Ede.Uof.EIP.SystemInfo.Current.UserGUID,
            Ede.Uof.EIP.SystemInfo.Current.UserIPAddress,
            Ede.Uof.Utility.License.Modules.UofViewer);

        if (!IsPostBack)
        {
            AddSiteMapNode("Tools");
            AddSiteMapNode("TB_EB_GROUP GROUP_CODE", Request.Url.AbsoluteUri);
            BindGrid();
        }
    }

    /// <summary>
    /// 依目前搜尋條件重新查詢，查詢時離開編輯模式避免更新到錯誤列。
    /// </summary>
    protected void btnSearch_Click(object sender, EventArgs e)
    {
        gvItems.PageIndex = 0;
        gvItems.EditIndex = -1;
        BindGrid();
    }

    /// <summary>
    /// 清除搜尋條件。預設只顯示啟用資料，組織類型則顯示全部。
    /// </summary>
    protected void btnClearSearch_Click(object sender, EventArgs e)
    {
        txtSearchGroupName.Text = string.Empty;
        txtSearchGroupCode.Text = string.Empty;
        ddlGroupTypeFilter.SelectedValue = "all";
        ddlActiveFilter.SelectedValue = "1";
        gvItems.PageIndex = 0;
        gvItems.EditIndex = -1;
        BindGrid();
    }

    /// <summary>
    /// 換頁時取消編輯狀態，避免跨頁後仍保留上一頁的 EditIndex。
    /// </summary>
    protected void gvItems_PageIndexChanging(object sender, GridViewPageEventArgs e)
    {
        gvItems.PageIndex = e.NewPageIndex;
        gvItems.EditIndex = -1;
        BindGrid();
    }

    /// <summary>
    /// 進入原地編輯模式；畫面上只有 GROUP_CODE 欄位會變成 TextBox。
    /// </summary>
    protected void gvItems_RowEditing(object sender, GridViewEditEventArgs e)
    {
        gvItems.EditIndex = e.NewEditIndex;
        BindGrid();
    }

    /// <summary>
    /// 取消原地編輯並重新綁定目前查詢結果。
    /// </summary>
    protected void gvItems_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        gvItems.EditIndex = -1;
        BindGrid();
    }

    /// <summary>
    /// 更新指定 GROUP_ID 的 GROUP_CODE。此工具頁只允許維護這一個欄位。
    /// </summary>
    protected void gvItems_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        string groupId = Convert.ToString(gvItems.DataKeys[e.RowIndex].Value);
        TextBox txtGridGroupCode = gvItems.Rows[e.RowIndex].FindControl("txtGridGroupCode") as TextBox;
        string groupCode = txtGridGroupCode == null ? string.Empty : txtGridGroupCode.Text;

        UpdateGroupCode(groupId, groupCode);
        gvItems.EditIndex = -1;
        ShowMessage("GROUP_CODE 已更新。", false);
        BindGrid();
    }

    /// <summary>
    /// 重新讀取資料並綁定 FastGrid。
    /// </summary>
    private void BindGrid()
    {
        gvItems.DataSource = GetAllData();
        gvItems.DataBind();
    }

    /// <summary>
    /// 讀取 TB_EB_GROUP 清單。
    /// GroupSource 先計算同父層節點的字母排序，GroupTree 再遞迴組出 SORT_PATH，
    /// 讓每一階層各自依 GROUP_NAME 排序，同時保留父子樹狀關係。
    /// </summary>
    private DataTable GetAllData()
    {
        using (SqlConnection conn = new SqlConnection(_connectionString))
        using (SqlCommand cmd = new SqlCommand(@"
WITH GroupSource AS
(
    SELECT
           g.[GROUP_ID],
           g.[GROUP_TYPE],
           g.[GROUP_NAME],
           g.[PARENT_GROUP_ID],
           parentGroup.[GROUP_NAME] AS [PARENT_GROUP_NAME],
           g.[LEV],
           g.[GROUP_CODE],
           g.[ACTIVE],
           g.[COMPANY_ID],
           ROW_NUMBER() OVER (
               PARTITION BY g.[GROUP_TYPE], ISNULL(g.[PARENT_GROUP_ID], N'')
               ORDER BY ISNULL(g.[GROUP_NAME], N''), g.[GROUP_ID]
           ) AS [SIBLING_SORT]
    FROM [dbo].[TB_EB_GROUP] g WITH (NOLOCK)
    LEFT JOIN [dbo].[TB_EB_GROUP] parentGroup WITH (NOLOCK)
      ON parentGroup.[GROUP_ID] = g.[PARENT_GROUP_ID]
),
GroupTree AS
(
    SELECT
           g.[GROUP_ID],
           g.[GROUP_TYPE],
           g.[GROUP_NAME],
           g.[PARENT_GROUP_ID],
           g.[PARENT_GROUP_NAME],
           g.[LEV],
           g.[GROUP_CODE],
           g.[ACTIVE],
           g.[COMPANY_ID],
           CAST(RIGHT(N'000000' + CONVERT(nvarchar(6), g.[SIBLING_SORT]), 6) AS nvarchar(max)) AS [SORT_PATH]
    FROM GroupSource g
    WHERE g.[PARENT_GROUP_ID] IS NULL
       OR NOT EXISTS (
            SELECT 1
            FROM GroupSource parentGroup
            WHERE parentGroup.[GROUP_ID] = g.[PARENT_GROUP_ID]
              AND parentGroup.[GROUP_TYPE] = g.[GROUP_TYPE]
       )

    UNION ALL

    SELECT
           child.[GROUP_ID],
           child.[GROUP_TYPE],
           child.[GROUP_NAME],
           child.[PARENT_GROUP_ID],
           child.[PARENT_GROUP_NAME],
           child.[LEV],
           child.[GROUP_CODE],
           child.[ACTIVE],
           child.[COMPANY_ID],
           parentTree.[SORT_PATH] + N'.' + RIGHT(N'000000' + CONVERT(nvarchar(6), child.[SIBLING_SORT]), 6) AS [SORT_PATH]
    FROM GroupSource child
    INNER JOIN GroupTree parentTree
      ON parentTree.[GROUP_ID] = child.[PARENT_GROUP_ID]
     AND parentTree.[GROUP_TYPE] = child.[GROUP_TYPE]
)
SELECT
       g.[GROUP_ID],
       g.[GROUP_TYPE],
       g.[GROUP_NAME],
       REPLICATE(NCHAR(12288), CASE WHEN g.[LEV] > 0 THEN g.[LEV] - 1 ELSE 0 END) +
           CASE WHEN g.[LEV] > 0 THEN NCHAR(65122) + N'---- ' ELSE N'' END +
           ISNULL(g.[GROUP_NAME], N'') AS [TREE_GROUP_NAME],
       g.[PARENT_GROUP_ID],
       g.[PARENT_GROUP_NAME],
       g.[LEV],
       g.[GROUP_CODE],
       CASE ISNULL(g.[ACTIVE], 0)
            WHEN 1 THEN N'Y'
            ELSE N'N'
       END AS [ACTIVE_TEXT],
       g.[COMPANY_ID]
FROM GroupTree g
WHERE (@GROUP_NAME = N'' OR g.[GROUP_NAME] LIKE @GROUP_NAME_LIKE)
  AND (@GROUP_CODE = N'' OR g.[GROUP_CODE] LIKE @GROUP_CODE_LIKE)
  AND (@GROUP_TYPE_FILTER = N'all' OR g.[GROUP_TYPE] = @GROUP_TYPE_FILTER)
  AND (@ACTIVE_FILTER = N'all' OR ISNULL(g.[ACTIVE], 0) = @ACTIVE_VALUE)
ORDER BY
    CASE g.[GROUP_TYPE]
        WHEN N'Department' THEN 0
        WHEN N'Group' THEN 1
        ELSE 2
    END,
    g.[SORT_PATH],
    g.[GROUP_NAME],
    g.[GROUP_ID]
OPTION (MAXRECURSION 0)", conn))
        using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
        {
            // 這些篩選值只影響查詢，不會改動組織表資料。
            string searchGroupName = (txtSearchGroupName.Text ?? string.Empty).Trim();
            string searchGroupCode = (txtSearchGroupCode.Text ?? string.Empty).Trim();
            string groupTypeFilter = ddlGroupTypeFilter.SelectedValue ?? "all";
            string activeFilter = ddlActiveFilter.SelectedValue ?? "1";

            cmd.Parameters.Add("@GROUP_NAME", SqlDbType.NVarChar, 50).Value = searchGroupName;
            cmd.Parameters.Add("@GROUP_NAME_LIKE", SqlDbType.NVarChar, 200).Value = "%" + EscapeLikeValue(searchGroupName) + "%";
            cmd.Parameters.Add("@GROUP_CODE", SqlDbType.NVarChar, 50).Value = searchGroupCode;
            cmd.Parameters.Add("@GROUP_CODE_LIKE", SqlDbType.NVarChar, 200).Value = "%" + EscapeLikeValue(searchGroupCode) + "%";
            cmd.Parameters.Add("@GROUP_TYPE_FILTER", SqlDbType.NVarChar, 10).Value = groupTypeFilter;
            cmd.Parameters.Add("@ACTIVE_FILTER", SqlDbType.NVarChar, 10).Value = activeFilter;
            cmd.Parameters.Add("@ACTIVE_VALUE", SqlDbType.Bit).Value = activeFilter == "1";

            DataTable dt = new DataTable();
            adapter.Fill(dt);
            return dt;
        }
    }

    /// <summary>
    /// 實際寫入資料庫的唯一方法：只更新 TB_EB_GROUP.GROUP_CODE。
    /// GROUP_ID 來自 Grid DataKey，不接受畫面輸入，降低誤改其他資料的風險。
    /// </summary>
    private void UpdateGroupCode(string groupId, string groupCode)
    {
        using (SqlConnection conn = new SqlConnection(_connectionString))
        using (SqlCommand cmd = new SqlCommand(@"
UPDATE [dbo].[TB_EB_GROUP]
   SET [GROUP_CODE] = @GROUP_CODE
 WHERE [GROUP_ID] = @GROUP_ID", conn))
        {
            cmd.Parameters.Add("@GROUP_ID", SqlDbType.NVarChar, 50).Value = groupId;
            cmd.Parameters.Add("@GROUP_CODE", SqlDbType.NVarChar, 50).Value = ToDbValue(groupCode, 50);
            conn.Open();
            cmd.ExecuteNonQuery();
        }
    }

    /// <summary>
    /// 將空白 GROUP_CODE 轉成 DB NULL，並依欄位長度截斷。
    /// </summary>
    private object ToDbValue(string value, int maxLength)
    {
        string text = Truncate((value ?? string.Empty).Trim(), maxLength);
        if (string.IsNullOrEmpty(text))
        {
            return DBNull.Value;
        }

        return text;
    }

    /// <summary>
    /// 防止輸入超過資料表 GROUP_CODE nvarchar(50) 長度。
    /// </summary>
    private string Truncate(string value, int maxLength)
    {
        if (string.IsNullOrEmpty(value) || value.Length <= maxLength)
        {
            return value;
        }

        return value.Substring(0, maxLength);
    }

    /// <summary>
    /// 轉義 LIKE 條件中的萬用字元，避免使用者搜尋 % 或 _ 時變成全表萬用。
    /// </summary>
    private string EscapeLikeValue(string value)
    {
        return (value ?? string.Empty)
            .Replace("[", "[[]")
            .Replace("%", "[%]")
            .Replace("_", "[_]");
    }

    /// <summary>
    /// 顯示操作結果；錯誤紅色，成功綠色。
    /// </summary>
    private void ShowMessage(string message, bool isError)
    {
        lblMessage.Text = message;
        lblMessage.ForeColor = isError ? System.Drawing.Color.Red : System.Drawing.Color.Green;
    }
}