托盘明细导出GetBindListDaoChu

admin

2 天以前 90a811e92481b86581460da8c330ff407bf531fe

 Wms/WMS.BLL/DataServer/StockDetailServer.cs

@@ -1,11 +1,13 @@
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;
using WMS.Entity.BllQualityEntity;
using WMS.Entity.Context;
using WMS.Entity.DataEntity;
@@ -13,11 +15,11 @@

namespace WMS.BLL.DataServer
{
    public class StockDetailServer:DbHelper<DataStockDetail>,IStockDetailServer
    public class StockDetailServer : DbHelper<DataStockDetail>, IStockDetailServer
    {
        private static readonly SqlSugarScope Db = DataContext.Db;

        public StockDetailServer():base(Db)
        public StockDetailServer() : base(Db)
        {
        }

@@ -36,17 +38,21 @@
        /// <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<StockDetailDto> GetBindList(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)
        public List<StockDetailDto> GetBindList(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)
        {
            string str = "select detail.*,house.WareHouseNo + '-' + house.WareHouseName as WareHouseName,roadway.RoadwayNo + '-' + roadway.RoadwayName as RoadwayName," +
                "area.AreaNo + '-' + area.AreaName as AreaName " +
                "from DataStockDetail detail " +
                "left join SysStorageRoadway roadway on detail.RoadwayNo = roadway.RoadwayNo " +
                "left join SysWareHouse house on detail.WareHouseNo = house.WareHouseNo " +
                "left join SysStorageArea area on detail.AreaNo = area.AreaNo " +
                "where detail.IsDel = @isdel";
            string str = $@"select detail.*,house.WareHouseNo+'-'+house.WareHouseName as WareHouseName,roadway.RoadwayNo+'-'+roadway.RoadwayName as RoadwayName,area.AreaNo+'-'+area.AreaName as AreaName 
from DataStockDetail detail 
left join SysStorageRoadway roadway on detail.RoadwayNo=roadway.RoadwayNo 
left join SysWareHouse house on detail.WareHouseNo=house.WareHouseNo 
left join SysStorageArea area on detail.AreaNo=area.AreaNo 
where detail.IsDel=@isdel";
            //判断物料号是否为空
            if (!string.IsNullOrEmpty(skuNo))
            {
@@ -70,7 +76,7 @@
            //判断零托标记是否为空
            if (!string.IsNullOrEmpty(bitPalletMark))
            {
                str += " and detail.BitPalletMark = @bitpalletmark";
                str += " and detail.BitPalletMark=@bitpalletmark";
            }
            //判断货主编码是否为空
            if (!string.IsNullOrEmpty(ownerNo))
@@ -84,17 +90,30 @@
            }
            if (!string.IsNullOrEmpty(startTime))
            {
                str += $" and detail.CompleteTime >= '{startTime}'";
                str += $" and detail.CompleteTime>='{startTime}'";
            }
            if (!string.IsNullOrEmpty(endTime))
            {
                endTime = Convert.ToDateTime(endTime).AddDays(1).ToString();
                str += $" and detail.CompleteTime < '{endTime}'";
                str += $" and detail.CompleteTime<'{endTime}'";
            }
            //重复条件
            //if (!string.IsNullOrEmpty(WareHouseNo)) 
            //{
            //    str += $" and detail.WareHouseNo = '{WareHouseNo}'";
            //}
            if (!string.IsNullOrEmpty(WareHouseNo))
            {
                str += $" and detail.WareHouseNo='{WareHouseNo}'";
            }
            if (!string.IsNullOrEmpty(AreaNo))
            {
                str += $" and detail.AreaNo='{AreaNo}'";
            }

            int i = 0;
            //在箱码明细中获取相应数据
            string boxstr = "select StockDetailId from DataBoxInfo Where IsDel = @isdel";
            string boxstr = "select StockDetailId from DataBoxInfo Where IsDel=@isdel";
            //判断箱码是否为空
            if (!string.IsNullOrEmpty(boxNo))
            {
@@ -104,27 +123,27 @@
            //判断箱支状态是否为空
            if (!string.IsNullOrEmpty(status))
            {
                boxstr += " and Status = @status";
                boxstr += " and Status=@status";
                i = 1;
            }
            //判断检验标记是否为空
            if (!string.IsNullOrEmpty(inspectMark))
            {
                boxstr += " and InspectMark = @inspectmark";
                str += " and detail.InspectMark = @inspectmark";
                boxstr += " and InspectMark=@inspectmark";
                str += " and detail.InspectMark=@inspectmark";
                i = 1;
            }
            //判断零箱标记是否为空
            if (!string.IsNullOrEmpty(bitBoxMark))
            {
                boxstr += " and BitBoxMark = @bitboxmark";
                boxstr += " and BitBoxMark=@bitboxmark";
                i = 1;
            }
            //判断质量状态是否为空
            if (!string.IsNullOrEmpty(inspectStatus))
            {
                boxstr += " and InspectStatus = @inspectstatus";
                str += " and detail.InspectStatus = @inspectstatus";
                boxstr += " and InspectStatus=@inspectstatus";
                str += " and detail.InspectStatus=@inspectstatus";
                i = 1;
            }
            //将箱码数据进行排序
@@ -160,11 +179,11 @@
                }
                if (!string.IsNullOrEmpty(arr))
                {
                    str += $" and Id in ({arr})";
                    str += $" and detail.Id in ({arr})";
                }
                else
                {
                    str += " and Id = ''";
                    str += " and detail.Id=''";
                }
            }

@@ -187,13 +206,44 @@

            return boxInforList;
        }
        /// <summary>
        /// 托盘明细备注
        /// </summary>
        /// <param name="id"></param>
        /// <param name="demo"></param>
        /// <param name="userId"></param>
        /// <exception cref="Exception"></exception>
        public void EditStockDetailDemo(int id, string demo, int userId)
        {
            try
            {
                var detail = Db.Queryable<DataStockDetail>().First(m => m.IsDel == "0" && m.Id == id);
                if (detail == null)
                {
                    throw new Exception("未查询到库存明细信息");
                }
                detail.Demo = demo + "".Trim();
                detail.UpdateUser = userId;
                detail.UpdateTime = DateTime.Now;

                int i = Db.Updateable(detail).ExecuteCommand();
                if (i > 0)
                {
                    //添加操作日志
                    new OperationASNServer().AddLogOperationAsn("库存统计", "托盘明细", detail.PalletNo, "编辑", $"编辑了托盘号为{detail.PalletNo}的备注信息", userId);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        #endregion

        #region 导出托盘明细
        /// <summary>
        /// 获取托盘明细
        /// 导出托盘明细
        /// </summary>
        /// <param name="skuNo">物料号</param>
        /// <param name="skuName">物料名称</param>
@@ -205,10 +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";
            //判断物料号是否为空
@@ -341,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