多表增刪改查示例

本章介紹學(xué)習(xí)多張表增、刪、改、查功能如何實(shí)現(xiàn)，下面以銷貨出庫單作為示例，該業(yè)務(wù)欄位如下：

銷貨出庫單欄位

• 銷貨單號(hào)、銷貨日期、狀態(tài)、客戶、備注

銷貨出庫單明細(xì)欄位

• 商品編碼、商品名稱、規(guī)格型號(hào)、數(shù)量、單位、單價(jià)、金額

該示例適用于出貨明細(xì)數(shù)量較小情況，單據(jù)表頭和表體組合查詢和提交。

對(duì)于出貨明細(xì)數(shù)量較大的情況，建議表頭與表體分開查詢和提交，表體采用分頁查詢。

1. 前后端共用

1.1. 創(chuàng)建實(shí)體類

• 在KIMS項(xiàng)目Entities文件夾下創(chuàng)建KmBill和KmBillList實(shí)體類
• 該類繼承EntityBase類
• 屬性使用Column特性描述，用于生成頁面字段和數(shù)據(jù)校驗(yàn)

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

    //出貨明細(xì)，為了降低代碼量，擴(kuò)展實(shí)體類，不再創(chuàng)建DTO類
    //此處使用虛屬性，ORM映射SQL時(shí)忽略該屬性
    public virtual List<KmBillList>? Lists { get; set; }
}
//銷貨出庫單明細(xì)
public class KmBillList : EntityBase
{
    [Column("銷貨單ID", "", true, "1", "50")]
    public string? HeadId { get; set; }
    [Column("商品編碼", "", true, "1", "50")]
    public string? Code { get; set; }
    [Column("數(shù)量", "", false)]
    public decimal? Qty { get; set; }
    [Column("單價(jià)", "", false)]
    public decimal? Price { get; set; }
    [Column("金額", "", false)]
    public decimal? Amount { get; set; }

    //如下虛屬性用于關(guān)聯(lián)商品資料表查詢顯示
    public virtual string? Name { get; set; }  //商品名稱
    public virtual string? Model { get; set; } //規(guī)格型號(hào)
    public virtual string? Unit { get; set; }   //計(jì)量單位
}

1.2. 創(chuàng)建Client類

• 在KIMS項(xiàng)目Clients文件夾下創(chuàng)建BillClient類
• 該類是前后端數(shù)據(jù)交互接口，繼承ClientBase類
• 該類只需提供分頁查詢、刪除和保存，導(dǎo)入功能由框架統(tǒng)一異步處理

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. 創(chuàng)建List頁面

• 在KIMS.Razor項(xiàng)目BillData文件夾下創(chuàng)建BillList類
• 該類是數(shù)據(jù)列表頁面，繼承WebGridView<KmBill, BillForm>類
• 列表頁面按鈕和欄位在框架模塊管理中配置

class BillList : WebGridView<KmBill, BillForm>
{
    protected override Task InitPageAsync()
    {
        //表格欄位格式化顯示
        //銷貨單號(hào)鏈接，點(diǎn)擊顯示銷貨單查看表單
        Column(c => c.BillNo).Template((b, r) => b.Link(r.BillNo, Callback(() => View(r))));
        Column(c => c.Status).Template(BillStatusCell);//顯示狀態(tài)標(biāo)簽
        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. 創(chuàng)建Form頁面

• 在KIMS.Razor項(xiàng)目BillData\Forms文件夾下創(chuàng)建BillForm類
• 該類是數(shù)據(jù)編輯和查看明細(xì)頁面，繼承WebForm<KmBill>類

[Dialog(980, 580)]//設(shè)置對(duì)話框大小
class BillForm : WebForm<KmBill>
{
    private KmBill? head;
    //初始化表單，查詢表頭表體組合數(shù)據(jù)
    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 =>
            {
                //銷貨單號(hào)不可編輯，自動(dòng)生成編號(hào)
                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>("商品明細(xì)", "", attr =>
            {
                attr.Set(c => c.ReadOnly, ReadOnly)
                    .Set(c => c.Data, head?.Lists);//設(shè)置表體數(shù)據(jù)
            });
        });
    }
    //表單底部按鈕
    protected override void BuildButtons(RenderTreeBuilder builder)
    {
        builder.Button(FormButton.Save, Callback(OnSave), !ReadOnly);
        base.BuildButtons(builder);
    }
    //保存按鈕方法
    private void OnSave()
    {
        SubmitAsync(data =>
        {
            head?.FillModel(data);//自動(dòng)填充表單修改數(shù)據(jù)
            return Client.Bill.SaveBillAsync(head);
        });
    }
}

2.3. 創(chuàng)建表體頁面

• 在KIMS.Razor項(xiàng)目BillData\Forms文件夾下創(chuàng)建BillListGrid類
• 該類是表體數(shù)據(jù)編輯表格，繼承EditGrid<KmBillList>類

//可編輯表體組件
class BillListGrid : EditGrid<KmBillList>
{
    public BillListGrid()
    {
        OrderBy = nameof(KmBillList.ItemNo);//默認(rèn)排序
        Name = "商品明細(xì)";
    }
    //初始化表格欄位
    protected override Task OnInitializedAsync()
    {
        //如下欄位有Edit方法為可編輯欄位，否則不可編輯
        var builder = new ColumnBuilder<KmBillList>();
        //商品庫存選擇器，彈出對(duì)話框查詢商品庫存，雙擊選擇要出庫的商品
        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();
    }
    //切換商品編碼，自動(dòng)帶出商品信息
    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;
    }
    //更改數(shù)量，自動(dòng)計(jì)算金額
    private void OnQtyChanged(KmBillList row, object value)
    {
        var qty = Utils.ConvertTo<decimal>(value);
        row.Amount = Utils.Round(qty * (row.Price ?? 0), 2);
    }
    //更改單價(jià)，自動(dòng)計(jì)算金額
    private void OnPriceChanged(KmBillList row, object value)
    {
        var price = Utils.ConvertTo<decimal>(value);
        row.Amount = Utils.Round(price * row.Qty, 2);
    }
    //更改金額，自動(dòng)計(jì)算單價(jià)
    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. 創(chuàng)建Controller類

• 在KIMS.Core項(xiàng)目Controllers文件夾下創(chuàng)建BillController類
• 該類為服務(wù)端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. 創(chuàng)建Service類

• 在KIMS.Core項(xiàng)目Services文件夾下創(chuàng)建BillService類
• 該類為業(yè)務(wù)邏輯服務(wù)類，繼承ServiceBase類

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

        //此處增加刪除數(shù)據(jù)校驗(yàn)
        return Database.Transaction(Language.Delete, db =>
        {
            foreach (var item in models)
            {
                //刪除表體
                BillRepository.DeleteBillLists(db, item.Id);
                db.Delete(item);
            }
        });
    }
    //獲取組合數(shù)據(jù)
    internal KmBill GetBill(string id)
    {
        if (string.IsNullOrEmpty(id))//id為空返回默認(rèn)值
            return GetDefaultBill();

        var entity = Database.QueryById<KmBill>(id);
        if (entity == null)//為空返回默認(rèn)值
            entity = GetDefaultBill();
        else//否則組裝表體數(shù)據(jù)返回
            entity.Lists = BillRepository.GetBillLists(Database, id);
        return entity;
    }
    //保存數(shù)據(jù)
    internal Result SaveBill(KmBill model)
    {
        if (model == null)
            return Result.Error("不能提交空數(shù)據(jù)！");

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

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

            //清空表體合計(jì)數(shù)據(jù)
            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);
                }
                //合計(jì)表體數(shù)據(jù)
                model.TotalAmount = lists.Sum(l => l.Amount);
                model.GoodsName = string.Join(",", lists.Select(l => l.Name));
            }
            db.Save(model);
        }, model);
    }
    //獲取默認(rèn)表頭
    private KmBill GetDefaultBill()
    {
        return new KmBill
        {
            BillNo = GetBillMaxNo(Database),
            BillDate = DateTime.Now,
            Status = "暫存",
            Lists = new List<JxBillList>()
        };
    }
    //獲取銷貨單最大編號(hào)
    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. 創(chuàng)建Repository類

• 在KIMS.Core項(xiàng)目Repositories文件夾下創(chuàng)建BillRepository類
• 該類為數(shù)據(jù)訪問類

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);//查詢條件自動(dòng)綁定
    }
    //獲取銷貨單最大編號(hào)
    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
    //根據(jù)表頭ID獲取表體數(shù)據(jù)
    internal static List<KmBillList> GetBillLists(Database db, string headId)
    {
        //關(guān)聯(lián)商品資料表查詢商品信息
        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 });
    }
    //根據(jù)表頭ID刪除表體數(shù)據(jù)
    internal static void DeleteBillLists(Database db, string headId)
    {
        var sql = "delete from KmBillList where HeadId=@headId";
        db.Execute(sql, new { headId });
    }
}

4. 運(yùn)行測(cè)試

• 運(yùn)行效果如下

image.png

image.png