WEBUI/WMS.BZCore/ServiceCore/FeiXu/FxInventoryCore.cs

using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WMS.Info;
using WMS.Util;

namespace WMS.Core.ServiceCore
{
    public class FxInventoryCore
    {
        /// <summary>
        /// 获取分页数据
        /// <summary>
        /// <returns></returns>
        public IEnumerable<dynamic> GetInventoryPageList(Pagination pagination, string queryJson)
        {
            SqlSugarClient client = null;
            try
            {
                client = SysDbCore.GetDbCtx();
                int count = 0;
                var queryParam = queryJson.ToJObject();

                var db = client.Queryable<BILL_INVENTORY>();

                //单据日期
                if (!queryParam["Btime"].IsEmpty() && !queryParam["Etime"].IsEmpty())
                {
                    db.Where(ord => ord.F_addTime >= queryParam["Btime"].ToDate() && ord.F_addTime <= queryParam["Etime"].ToDate());
                }
                if (!queryParam["F_matNo"].IsEmpty())
                {
                    string matNo = queryParam["F_matNo"].ToString();
                    db.Where(ord => ord.F_matNo.Contains(matNo));
                }
                if (!queryParam["F_trayNo"].IsEmpty())
                {
                    string trayNo = queryParam["F_trayNo"].ToString();
                    db.Where(ord => ord.F_trayNo.Contains(trayNo));
                }
                if (!queryParam["F_boxNo"].IsEmpty())
                {
                    string boxNo = queryParam["F_boxNo"].ToString();
                    db.Where(ord => ord.F_boxNo.Contains(boxNo));
                }
                if (!queryParam["F_uidOrBox"].IsEmpty())
                {
                    string boxNo = queryParam["F_uidOrBox"].ToString();
                    db.Where(ord => ord.F_boxNo.Contains(boxNo)|| ord.F_uid.Contains(boxNo));
                }
                
                if (!queryParam["F_projectNo"].IsEmpty())
                {
                    string projectNo = queryParam["F_projectNo"].ToString();
                    db.Where(ord => ord.F_projectNo.Contains(projectNo));
                }
                if (!queryParam["F_matType"].IsEmpty())
                {
                    string matType = queryParam["F_matType"].ToString();
                    db.Where(ord => ord.F_matType == FuncStr.NullToInt(matType));
                }
                if (pagination.sord.ToUpper() != "ASC")
                {
                    pagination.sidx = pagination.sidx + " DESC";
                }
                // (case when regexp_substr(ord.F_NO,'[^_]+',1,2) is null then ord.F_NO else regexp_substr(ord.F_NO,'[^_]+',1,2) end)
                var list = db.OrderBy(pagination.sidx).Select<dynamic>(@"
                                                                [F_no],
                                                               [F_warehouseNo],
                                                                [F_projectNo],
                                                               [F_matNo],
                                                               [F_matName],
                                                               [F_matType],
                                                               [F_quantity],
                                                               [F_lockQty],
                                                                [F_trayNo],
                                                                 [F_boxNo],
                                                               [F_batchNo], 
                                                               [F_memo],
                                                               [F_addUserNo],
                                                               [F_addTime]").ToPageList(pagination.page, pagination.rows, ref count);
                pagination.records = count;
                return list;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                client.Dispose();
            }
        }
        /// <summary>
        /// 获取分页数据
        /// <summary>
        /// <returns></returns>
        public IEnumerable<dynamic> GetCheckInventoryPageList(Pagination pagination, string queryJson)
        {
            SqlSugarClient client = null;
            try
            {
                client = SysDbCore.GetDbCtx();
                int count = 0;
                var queryParam = queryJson.ToJObject();

                var db = client.Queryable<BILL_INVENTORY, BASE_LOCATION>((inv, loc) => new object[] { JoinType.Inner, inv.F_trayNo == loc.F_trayNo }).Where((inv, loc) => loc.F_status == (int)EWareCellState.Stored);
                if (!queryParam["matNo"].IsEmpty())
                {
                    string matNo = queryParam["matNo"].ToString();
                    db.Where((inv, loc) => inv.F_matNo.Contains(matNo));
                }
                if (!queryParam["trayNo"].IsEmpty())
                {
                    string trayNo = queryParam["trayNo"].ToString();
                    db.Where((inv, loc) => inv.F_trayNo.Contains(trayNo));
                }
                if (!queryParam["matType"].IsEmpty())
                {
                    string matType = queryParam["matType"].ToString();
                    db.Where((inv, loc) => inv.F_matType == FuncStr.NullToInt(matType));
                }
                pagination.sidx = $"inv.{pagination.sidx }";
                if (pagination.sord.ToUpper() != "ASC")
                {
                    pagination.sidx = $"{pagination.sidx } DESC";
                }
                var list = db.OrderBy(pagination.sidx).Select<dynamic>(@"
                                                               inv.[F_no],
                                                               inv.[F_warehouseNo],
                                                               inv.[F_projectNo],
                                                               inv.[F_matNo],
                                                               inv.[F_matName],
                                                               inv.[F_matType],
                                                               inv.[F_quantity],
                                                               inv.[F_lockQty],
                                                               inv.[F_trayNo],
                                                               inv.[F_boxNo],
                                                               inv.[F_batchNo], 
                                                               inv.[F_memo],
                                                               inv.[F_addUserNo],
                                                               inv.[F_addTime]").ToPageList(pagination.page, pagination.rows, ref count);
                pagination.records = count;
                return list;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                client.Dispose();
            }
        }

    }
}