托盘明细导出GetBindListDaoChu

admin

2 天以前 90a811e92481b86581460da8c330ff407bf531fe

 Wms/WMS.BLL/DataServer/StockDetailServer.cs

@@ -1,9 +1,10 @@
using System;
using Model.ModelDto.DataDto;
using Model.ModelDto.SysDto;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Model.ModelDto.DataDto;
using SqlSugar;
using WMS.BLL.LogServer;
using WMS.DAL;
using WMS.Entity.BllAsnEntity;
@@ -254,9 +255,17 @@
        /// <param name="bitPalletMark">零托标记</param>
        /// <param name="bitBoxMark">零箱标记</param>
        /// <param name="inspectStatus">质量状态</param>
        /// <param name="ownerNo">货主编码</param>
        /// <param name="ownerName">货主名称</param>
        /// <param name="startTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <param name="wareHouseNo">所属仓库</param>
        /// <param name="areaNo">所属区域</param>
        /// <returns></returns>
        public List<DataStockDetail> GetBindListDaoChu(string skuNo, string skuName, string palletNo, string lotNo, string boxNo, string status, string inspectMark, string bitPalletMark, string bitBoxMark, string inspectStatus)
        public List<StockDetailDto> GetBindListDaoChu(string skuNo, string skuName, string palletNo, string lotNo, string boxNo, string status, string inspectMark, string bitPalletMark, string bitBoxMark, string inspectStatus, string ownerNo, string ownerName, string startTime, string endTime, string wareHouseNo, string areaNo)
        {
            #region GetBindListDaoChu
            /*
            string str = "select Id,LotNo,LotText,SupplierLot,SkuNo,SkuName,Qty,LockQty,FrozenQty,AreaNo,LocatNo,PalletNo,PalletNo2," +
                "PalletNo3,InspectMark,BitPalletMark from DataStockDetail where IsDel = @isdel";
            //判断物料号是否为空
@@ -389,6 +398,133 @@
                }
            }
            return boxInforList;
            */
            #endregion
            string listWhere = "a.IsDel='0'";
            //物料号
            if (!string.IsNullOrEmpty(skuNo))
            {
                listWhere += $@" and a.SkuNo like'%{skuNo}%'";
            }
            //物料名称
            if (!string.IsNullOrEmpty(skuName))
            {
                listWhere += $@" and a.SkuName like'%{skuName}%'";
            }

            //托盘号
            if (!string.IsNullOrEmpty(palletNo))
            {
                listWhere += $@" and a.PalletNo like'%{palletNo}%'";
            }

            //批次
            if (!string.IsNullOrEmpty(lotNo))
            {
                listWhere += $@" and a.LotNo like'%{lotNo}%'";
            }

            //零托标记
            if (!string.IsNullOrEmpty(bitPalletMark))
            {
                listWhere += $@" and a.BitPalletMark='{bitPalletMark}'";
            }

            //货主编码
            if (!string.IsNullOrEmpty(ownerNo))
            {
                listWhere += $@" and a.OwnerNo like'%{ownerNo}%'";
            }
            //货主名称
            if (!string.IsNullOrEmpty(ownerName))
            {
                listWhere += $@" and a.OwnerName like'%{ownerName}%'";
            }

            //开始时间
            if (!string.IsNullOrEmpty(startTime))
            {
                listWhere += $" and a.CompleteTime>='{startTime}'";
            }
            //结束时间
            if (!string.IsNullOrEmpty(endTime))
            {
                endTime = Convert.ToDateTime(endTime).AddDays(1).ToString();
                listWhere += $" and a.CompleteTime<'{endTime}'";
            }

            //仓库
            if (!string.IsNullOrEmpty(wareHouseNo))
            {
                listWhere += $" and a.WareHouseNo='{wareHouseNo}'";
            }
            //区域
            if (!string.IsNullOrEmpty(areaNo))
            {
                listWhere += $" and a.AreaNo='{areaNo}'";
            }

            //箱码明细条件
            string boxWhere = "IsDel='0'";
            //箱码
            if (!string.IsNullOrEmpty(boxNo))
            {
                boxWhere += $@" and BoxNo like'%{boxNo}%'";
            }
            //箱支状态
            if (!string.IsNullOrEmpty(status))
            {
                boxWhere += $@" and Status='{status}'";
            }
            //检验标记
            if (!string.IsNullOrEmpty(inspectMark))
            {
                boxWhere += $@" and InspectMark='{inspectMark}'";
                listWhere += $@" and a.InspectMark='{inspectMark}'";
            }
            //零箱标记
            if (!string.IsNullOrEmpty(bitBoxMark))
            {
                boxWhere += $@" and BitBoxMark='{bitBoxMark}'";
            }
            //质量状态
            if (!string.IsNullOrEmpty(inspectStatus))
            {
                boxWhere += $@" and InspectStatus='{inspectStatus}'";
                listWhere += $@" and a.InspectStatus='{inspectStatus}'";
            }

            //判断是否有查箱码信息
            if (boxWhere != "IsDel='0'")
            {
                listWhere += $@" and a.Id in (select StockDetailId from DataBoxInfo Where {boxWhere} group by StockDetailId)";
            }

            string listSql = $@"select a.Id,c.WareHouseNo+'-'+c.WareHouseName as WareHouseName,b.RoadwayNo+'-'+b.RoadwayName as RoadwayName,d.AreaNo+'-'+d.AreaName as AreaName,a.LocatNo,a.PalletNo,a.SkuNo,a.SkuName,a.LotNo,a.SupplierLot,a.Qty,a.LockQty,a.FrozenQty,e.DictName as Status,f.DictName as InspectStatus,a.InspectMark,case a.BitPalletMark when 0 then '否' when 1 then '是' else '' end BitPalletMark,a.ProductionTime,a.ExpirationTime,a.OwnerNo,a.OwnerName,a.SupplierNo,a.SupplierName,a.Demo
   from DataStockDetail a
   left join SysStorageRoadway b on b.RoadwayNo=a.RoadwayNo
   left join SysWareHouse c on c.WareHouseNo=a.WareHouseNo
   left join SysStorageArea d on d.AreaNo=a.AreaNo
   left join (select DictNo,DictName from SysDictionary where ParentNo='DataStockDetailStatus') e on e.DictNo=a.Status
   left join (select DictNo,DictName from SysDictionary where ParentNo='InspectStatus') f on f.DictNo=a.InspectStatus
   where {listWhere}
   order by a.PalletNo,a.SkuNo,a.LotNo";
            List<StockDetailDto> boxInforList = Db.Ado.SqlQuery<StockDetailDto>(listSql);

            foreach (var item in boxInforList)
            {
                //储位地址，加上单引号是防止导出到excel自动把前面的0给去掉
                if (!string.IsNullOrEmpty(item.LocatNo) && item.LocatNo.Substring(0, 1) == "0")
                {
                    item.LocatNo = $@"'{item.LocatNo}";
                }
                //物料编码
                if (!string.IsNullOrEmpty(item.SkuNo) && item.SkuNo.Substring(0, 1) == "0")
                {
                    item.SkuNo = $@"'{item.SkuNo}";
                }
            }
            return boxInforList;
        }
        #endregion