期初打印明细查询优化

cnf

6 天以前 eb86c89d85599da5b3f237a0e1214e45f6a90225

 WebApi/Gs.Warehouse/Services/MesInvItemBarcodesManager.cs

@@ -9,7 +9,11 @@
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using SqlSugar;
using System.Data;
using System.Data.SqlClient;
using System.Dynamic;
using System.Text;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;

namespace Gs.Warehouse.Services;

@@ -34,24 +38,78 @@
    /// <param name="query"></param>
    /// <returns></returns>
    [RequestMethod(RequestMethods.POST)]
    public ReturnDto<PageList<MesItems>> GetListPage(PageQuery query)
    public ReturnDto<PageList<dynamic>> GetListPage(PageQuery query)
    {
        var _sbWhere = new StringBuilder(" 1=1" + query.keyWhere);
        var _sbBy =new StringBuilder(query.sortName + " " + query.sortOrder);
        var pageList = new PageList<MesItems>();

        int currentPage = query.currentPage;
        int everyPageSize = query.everyPageSize;
        string sortName = query.sortName;
        string keyWhere = query.keyWhere;
        SqlParameter[] parameters =
        {
                new("@inCurrentPage", currentPage),
                new("@inEveryPageSize", everyPageSize),
                new("@inSortName", sortName),
                new("@inSortOrder", query.sortOrder),
                new("@inQueryWhere", keyWhere),
                new("@inFid", ""),
                new("@inP1", ""),
                new("@inP2", ""),
                new("@inP3", ""),
                new("@inP4", _userGuid)//当前登录用户guid，将根据他读取仓管员
            };
        var dset = new DataSet();
        var _pglist = new PageList<dynamic>
        {
            total = 0,
            everyPageSize = 0,
            pages = 0,
            list = new List<dynamic>()
        };
        try
        {
            var totalCount = GetPageTotalCount(null, query, out var itemsList, _sbWhere, _sbBy);
            pageList = new PageList<MesItems>(itemsList, totalCount,
                query.everyPageSize);
            return ReturnDto<PageList<MesItems>>.QuickReturn(pageList,
                ReturnCode.Success, "读取成功");
            dset = DbHelperSQL.RunProcedure("prc_qcdy_lst", parameters, "0");
            if (dset != null && dset.Tables.Count > 0 &&
                dset.Tables[0].Rows.Count > 0) //有数据
            {
                var intTotal = int.Parse(dset.Tables[1].Rows[0]["intTotal"].ToString());
                var pages = intTotal % everyPageSize != 0
                    ? intTotal / everyPageSize + 1
                    : intTotal / everyPageSize;
                _pglist.total = intTotal;
                _pglist.everyPageSize = everyPageSize;
                _pglist.pages = pages;
                var _dy = dset.Tables[0].TableToDynamicList();
                _pglist.list = _dy;
                //currentId = dset.Tables[1].Rows[0]["mrCgy"].ToString();
            }
        }
        catch (Exception ex)
        {
            return ReturnDto<PageList<MesItems>>.QuickReturn(pageList,
                ReturnCode.Default, ex.Message);
            LogHelper.Debug(ToString(), ex.Message);
            return ReturnDto<PageList<dynamic>>.QuickReturn(_pglist,
          ReturnCode.Exception, "读取失败！");
        }

        return ReturnDto<PageList<dynamic>>.QuickReturn(_pglist,
            ReturnCode.Success, "读取成功");

        //var pageList = new PageList<MesItems>();
        //try
        //{
        //    var totalCount = GetPageTotalCount(null, query, out var itemsList, _sbWhere, _sbBy);
        //    pageList = new PageList<MesItems>(itemsList, totalCount,
        //        query.everyPageSize);
        //    return ReturnDto<PageList<MesItems>>.QuickReturn(pageList,
        //        ReturnCode.Success, "读取成功");
        //}
        //catch (Exception ex)
        //{
        //    return ReturnDto<PageList<MesItems>>.QuickReturn(pageList,
        //        ReturnCode.Default, ex.Message);
        //}
    }

    private int GetPageTotalCount(Guid? guid, PageQuery query,
@@ -99,41 +157,82 @@
    /// <param name="model"></param>
    /// <returns></returns>
    [RequestMethod(RequestMethods.POST)]
    public ReturnDto<InitialBarcode> GetModel(
        [FromBody] MesItems model)
    public ReturnDto<ExpandoObject> GetModel(
        [FromBody] dynamic model)
    {
        // var m = base.GetById(model.Guid);
        var query = new PageQuery

        string guid = model.guid.ToString();
        dynamic m = new ExpandoObject();
        m.list = new List<dynamic>();
        m.list2 = new List<dynamic>();
        SqlParameter[] parameters =
        {
            currentPage = 1,
            everyPageSize = 1
            new("@inMainGuid", guid),
            new("@inP1", ""),
            new("@inP2", ""),
            new("@inP3", ""),
            new("@inP4", ""),
            new("@userGuid", _userGuid)
        };
        System.Text.StringBuilder sb = new StringBuilder();
        System.Text.StringBuilder order = new StringBuilder();
        order.Append(" org.FNumber asc ,a.item_no asc");
        var pageTotalCount =  GetPageTotalCount(model.Guid, query, out var itemsList, sb, order);
        var dset = new DataSet();
        try
        {
            dset = DbHelperSQL.RunProcedure("[prc_qcdy_mx]", parameters,
                "0");
            if (dset != null && dset.Tables.Count > 0 &&
                dset.Tables[0].Rows.Count > 0)
            {
                var dr = dset.Tables[0].Rows[0];
                m = dr.RowToDynamic();
                var _tb = dset.Tables[1].TableToDynamicList();
                m.list = _tb;
                //var _tb2 = dset.Tables[2].TableToDynamicList();
                //m.list2 = _tb2;
    
            }
        }
        catch (Exception ex)
        {
            LogHelper.Debug(ToString(), ex.Message);
        }

        var barcode = new InitialBarcode();
        if (m != null)
            return ReturnDto<dynamic>.QuickReturn(m, ReturnCode.Success,
                "读取成功！");
        return ReturnDto<dynamic>.QuickReturn(m, ReturnCode.Default, "读取失败！");

        if (pageTotalCount == 0)
            return ReturnDto<InitialBarcode>.QuickReturn(barcode,
                ReturnCode.Default,
                "读取失败！");
        //// var m = base.GetById(model.Guid);
        //var query = new PageQuery
        //{
        //    currentPage = 1,
        //    everyPageSize = 1
        //};
        //System.Text.StringBuilder sb = new StringBuilder();
        //System.Text.StringBuilder order = new StringBuilder();
        //order.Append(" org.FNumber asc ,a.item_no asc");
        //var pageTotalCount =  GetPageTotalCount(model.Guid, query, out var itemsList, sb, order);

        var mesItems = itemsList.FirstOrDefault();
        //var barcode = new InitialBarcode();

        //if (pageTotalCount == 0)
        //    return ReturnDto<InitialBarcode>.QuickReturn(barcode,
        //        ReturnCode.Default,
        //        "读取失败！");

        //var mesItems = itemsList.FirstOrDefault();


        if (mesItems == null)
            return ReturnDto<InitialBarcode>.QuickReturn(barcode,
                ReturnCode.Default,
                "读取失败！");
        //if (mesItems == null)
        //    return ReturnDto<InitialBarcode>.QuickReturn(barcode,
        //        ReturnCode.Default,
        //        "读取失败！");

        barcode.from = mesItems;
        //barcode.from = mesItems;

        barcode.barcodes = GetMesInvItemBarcodes(mesItems.Id);
        //barcode.barcodes = GetMesInvItemBarcodes(mesItems.Id);

        return ReturnDto<InitialBarcode>.QuickReturn(barcode,
            ReturnCode.Success, "读取成功！");
        //return ReturnDto<InitialBarcode>.QuickReturn(barcode,
        //    ReturnCode.Success, "读取成功！");
    }

    private List<MesInvItemBarcodes> GetMesInvItemBarcodes(long? ItemId)