多表增刪改查範例

本章介紹學習多張表增、刪、改、查功能如何實現，下面以銷貨出庫單作為範例，該業務欄位如下：

銷貨出庫單欄位

• 銷貨單號、銷貨日期、狀態、客戶、備註

銷貨出庫單明細欄位

• 商品編碼、商品名稱、規格型號、數量、單位、單價、金額

該範例適用於出貨明細數量較小情況，單據表頭和表體組合查詢和提交。

對於出貨明細數量較大的情況，建議表頭與表體分開查詢和提交，表體採用分頁查詢。

1. 前後端共用

1.1. 建立實體類

• 在KIMS專案Entities資料夾下建立KmBill和KmBillList實體類
• 該類繼承EntityBase類
• 屬性使用Column特性描述，用於生成頁面欄位和資料校驗

//銷貨出庫單
public class KmBill : EntityBase
{
    [Column("銷貨單號", "", true, "1", "50")]
    public string? BillNo { get; set; }
    ......
    [Column("客戶", "", true, "1", "50")]
    public string? BillDate { get; set; }
    [Column("備註", "", false)]
    public string? Note { get; set; }

    //出貨明細，為了降低程式碼量，擴充套件實體類，不再建立DTO類
    //此處使用虛屬性，ORM對映SQL時忽略該屬性
    public virtual List<KmBillList>? Lists { get; set; }
}
//銷貨出庫單明細
public class KmBillList : EntityBase
{
    [Column("銷貨單ID", "", true, "1", "50")]
    public string? HeadId { get; set; }
    [Column("商品編碼", "", true, "1", "50")]
    public string? Code { get; set; }
    [Column("數量", "", false)]
    public decimal? Qty { get; set; }
    [Column("單價", "", false)]
    public decimal? Price { get; set; }
    [Column("金額", "", false)]
    public decimal? Amount { get; set; }

    //如下虛屬性用於關聯商品資料表查詢顯示
    public virtual string? Name { get; set; }  //商品名稱
    public virtual string? Model { get; set; } //規格型號
    public virtual string? Unit { get; set; }   //計量單位
}

1.2. 建立Client類

• 在KIMS專案Clients資料夾下建立BillClient類
• 該類是前後端資料互動介面，繼承ClientBase類
• 該類只需提供分頁查詢、刪除和儲存，匯入功能由框架統一非同步處理

public class BillClient : ClientBase
{
    public BillClient(Context context) : base(context) { }

    public Task<PagingResult<KmBill>> QueryBillsAsync(PagingCriteria criteria) => Context.QueryAsync<KmBill>("Bill/QueryBills", criteria);
    public Task<Result> DeleteBillsAsync(List<KmBill> models) => Context.PostAsync("Bill/DeleteBills", models);
    public Task<KmBill> GetBillAsync(string id) => Context.GetAsync<KmBill>($"Bill/GetBill?id={id}");
    public Task<Result> SaveBillAsync(KmBill model) => Context.PostAsync("Bill/SaveBill", model);
}

2. 前端

2.1. 建立List頁面

• 在KIMS.Razor專案BillData資料夾下建立BillList類
• 該類是資料列表頁面，繼承WebGridView<KmBill, BillForm>類
• 列表頁面按鈕和欄位在框架模組管理中設定

class BillList : WebGridView<KmBill, BillForm>
{
    protected override Task InitPageAsync()
    {
        //表格欄位格式化顯示
        //銷貨單號連結，點選顯示銷貨單檢視表單
        Column(c => c.BillNo).Template((b, r) => b.Link(r.BillNo, Callback(() => View(r))));
        Column(c => c.Status).Template(BillStatusCell);//顯示狀態標籤
        return base.InitPageAsync();
    }

    //分頁查詢
    protected override Task<PagingResult<KmBill>> OnQueryData(PagingCriteria criteria)
    {
        return Client.Bill.QueryBillsAsync(criteria);
    }
    
    public void New() => ShowForm();//新增按鈕方法
    public void DeleteM() => DeleteRows(Client.Bill.DeleteBillsAsync);//批次刪除按鈕方法
    public void Edit(KmBill row) => ShowForm(row);//編輯操作方法
    public void Delete(KmBill row) => DeleteRow(row, Client.Bill.DeleteBillsAsync);//刪除操作方法
}

2.2. 建立Form頁面

• 在KIMS.Razor專案BillData\Forms資料夾下建立BillForm類
• 該類是資料編輯和檢視明細頁面，繼承WebForm類

[Dialog(980, 580)]//設定對話方塊大小
class BillForm : WebForm<KmBill>
{
    private KmBill? head;
    //初始化表單，查詢表頭表體組合資料
    protected override async Task InitFormAsync()
    {
        var model = TModel;
        head = await Client.Bill.GetBillAsync(model.Id);
    }
    //表單佈局
    protected override void BuildFields(FieldBuilder<KmBill> builder)
    {
        builder.Hidden(f => f.Id);//隱藏欄位
        builder.Table(table =>
        {
            table.ColGroup(11, 25, 11, 25, 11, 17);
            table.Tr(attr =>
            {
                //銷貨單號不可編輯，自動生成編號
                table.Field<Text>(f => f.BillNo).Enabled(false).Build();
                table.Field<Date>(f => f.BillDate).Build();
                table.Field<Text>(f => f.Status).Enabled(false).Build();
            });
            table.Tr(attr => table.Field<TextArea>(f => f.Note).ColSpan(5).Build());
            builder.FormList<BillListGrid>("商品明細", "", attr =>
            {
                attr.Set(c => c.ReadOnly, ReadOnly)
                    .Set(c => c.Data, head?.Lists);//設定表體資料
            });
        });
    }
    //表單底部按鈕
    protected override void BuildButtons(RenderTreeBuilder builder)
    {
        builder.Button(FormButton.Save, Callback(OnSave), !ReadOnly);
        base.BuildButtons(builder);
    }
    //儲存按鈕方法
    private void OnSave()
    {
        SubmitAsync(data =>
        {
            head?.FillModel(data);//自動填充表單修改資料
            return Client.Bill.SaveBillAsync(head);
        });
    }
}

2.3. 建立表體頁面

• 在KIMS.Razor專案BillData\Forms資料夾下建立BillListGrid類
• 該類是表體資料編輯表格，繼承EditGrid類

//可編輯表體元件
class BillListGrid : EditGrid<KmBillList>
{
    public BillListGrid()
    {
        OrderBy = nameof(KmBillList.ItemNo);//預設排序
        Name = "商品明細";
    }
    //初始化表格欄位
    protected override Task OnInitializedAsync()
    {
        //如下欄位有Edit方法為可編輯列位，否則不可編輯
        var builder = new ColumnBuilder<KmBillList>();
        //商品庫存選擇器，彈出對話方塊查詢商品庫存，雙擊選擇要出庫的商品
        builder.Field(r => r.Code).Edit(new GoodsStock(), OnCodeChanged);
        builder.Field(r => r.Name);//不可編輯
        builder.Field(r => r.Model);
        builder.Field(r => r.Qty).Edit<Number>(OnQtyChanged);//可編輯
        builder.Field(r => r.Unit);
        builder.Field(r => r.Price).Edit<Number>(OnPriceChanged);
        builder.Field(r => r.Amount).IsSum().Edit<Number>(OnAmountChanged);
        builder.Field(r => r.Note).Edit();
        Columns = builder.ToColumns();
        return base.OnInitializedAsync();
    }
    //切換商品編碼，自動帶出商品資訊
    private void OnCodeChanged(KmBillList row, object value)
    {
        var g = value as StockInfo;
        row.Type = g?.Type;
        row.Code = g?.Code;
        row.Name = g?.Name;
        row.Model = g?.Model;
        row.Unit = g?.Unit;
    }
    //更改數量，自動計算金額
    private void OnQtyChanged(KmBillList row, object value)
    {
        var qty = Utils.ConvertTo<decimal>(value);
        row.Amount = Utils.Round(qty * (row.Price ?? 0), 2);
    }
    //更改單價，自動計算金額
    private void OnPriceChanged(KmBillList row, object value)
    {
        var price = Utils.ConvertTo<decimal>(value);
        row.Amount = Utils.Round(price * row.Qty, 2);
    }
    //更改金額，自動計算單價
    private void OnAmountChanged(KmBillList row, object value)
    {
        var amount = Utils.ConvertTo<decimal>(value);
        row.Price = row.Qty == 0 ? 0 : Utils.Round(amount / row.Qty, 2);
    }
}

3. 後端

3.1. 建立Controller類

• 在KIMS.Core專案Controllers資料夾下建立BillController類
• 該類為伺服器端WebApi，繼承BaseController類

[Route("[controller]")]
public class BillController : BaseController
{
    private BillService Service => new(Context);

    [HttpPost("[action]")]
    public PagingResult<KmBill> QueryBills([FromBody] PagingCriteria criteria) => Service.QueryBills(criteria);

    [HttpPost("[action]")]
    public Result DeleteBills([FromBody] List<KmBill> models) => Service.DeleteBills(models);

    [HttpGet("[action]")]
    public KmBill GetBill([FromQuery] string id) => Service.GetBill(id);
    
    [HttpPost("[action]")]
    public Result SaveBill([FromBody] KmBill model) => Service.SaveBill(model);
}

3.2. 建立Service類

• 在KIMS.Core專案Services資料夾下建立BillService類
• 該類為業務邏輯服務類，繼承ServiceBase類

class BillService : ServiceBase
{
    internal BillService(Context context) : base(context) { }
    //分頁查詢
    internal PagingResult<KmBill> QueryBills(PagingCriteria criteria)
    {
        return BillRepository.QueryBills(Database, criteria);
    }
    //刪除資料
    internal Result DeleteBills(List<KmBill> models)
    {
        if (models == null || models.Count == 0)
            return Result.Error(Language.SelectOneAtLeast);

        //此處增加刪除資料校驗
        return Database.Transaction(Language.Delete, db =>
        {
            foreach (var item in models)
            {
                //刪除表體
                BillRepository.DeleteBillLists(db, item.Id);
                db.Delete(item);
            }
        });
    }
    //獲取組合資料
    internal KmBill GetBill(string id)
    {
        if (string.IsNullOrEmpty(id))//id為空返回預設值
            return GetDefaultBill();

        var entity = Database.QueryById<KmBill>(id);
        if (entity == null)//為空返回預設值
            entity = GetDefaultBill();
        else//否則組裝表體資料返回
            entity.Lists = BillRepository.GetBillLists(Database, id);
        return entity;
    }
    //儲存資料
    internal Result SaveBill(KmBill model)
    {
        if (model == null)
            return Result.Error("不能提交空資料！");

        var vr = model.Validate();//驗證輸入欄位
        if (!vr.IsValid)
            return vr;

        return Database.Transaction(Language.Save, db =>
        {
            if (model.IsNew)
                model.BillNo = GetBillMaxNo(db);

            //清空表體合計資料
            model.TotalAmount = 0;
            model.GoodsName = string.Empty;
            //先刪除表體，再插入表體
            BillRepository.DeleteBillLists(db, model.Id);
            if (model.Lists != null && model.Lists.Count > 0)
            {
                var index = 0;
                var lists = new List<KmBillList>();
                foreach (var item in model.Lists)
                {
                    item.HeadId = model.Id;
                    item.ItemNo = ++index;
                    db.Insert(item);
                    lists.Add(item);
                }
                //合計表體資料
                model.TotalAmount = lists.Sum(l => l.Amount);
                model.GoodsName = string.Join(",", lists.Select(l => l.Name));
            }
            db.Save(model);
        }, model);
    }
    //獲取預設表頭
    private KmBill GetDefaultBill()
    {
        return new KmBill
        {
            BillNo = GetBillMaxNo(Database),
            BillDate = DateTime.Now,
            Status = "暫存",
            Lists = new List<JxBillList>()
        };
    }
    //獲取銷貨單最大編號
    private static string GetBillMaxNo(Database db)
    {
        var prefix = $"S{DateTime.Now:yyyy}";
        var maxNo = BillRepository.GetBillMaxNo(db, prefix);
        if (string.IsNullOrWhiteSpace(maxNo))
            maxNo = $"{prefix}00000";
        return GetMaxFormNo(prefix, maxNo);
    }
}

3.3. 建立Repository類

• 在KIMS.Core專案Repositories資料夾下建立BillRepository類
• 該類為資料存取類

class BillRepository
{
    //Head
    //分頁查詢
    internal static PagingResult<KmBill> QueryBills(Database db, PagingCriteria criteria)
    {
        var sql = "select * from KmBill where CompNo=@CompNo";
        return db.QueryPage<KmBill>(sql, criteria);//查詢條件自動繫結
    }
    //獲取銷貨單最大編號
    internal static string GetBillMaxNo(Database db, string prefix)
    {
        var sql = $"select max(BillNo) from KmBill where CompNo=@CompNo and BillNo like '{prefix}%'";
        return db.Scalar<string>(sql, new { db.User.CompNo });
    }
    //List
    //根據表頭ID獲取表體資料
    internal static List<KmBillList> GetBillLists(Database db, string headId)
    {
        //關聯商品資料表查詢商品資訊
        var sql = "select a.*,b.Name,b.Model,b.Unit from KmBillList a,KmGoods b where a.Code=b.Code and HeadId=@headId";
        return db.QueryList<KmBillList>(sql, new { headId });
    }
    //根據表頭ID刪除表體資料
    internal static void DeleteBillLists(Database db, string headId)
    {
        var sql = "delete from KmBillList where HeadId=@headId";
        db.Execute(sql, new { headId });
    }
}

4. 執行測試

• 執行效果如下