using System;
using System.Collections.Generic;
using System.Text;
using SqlSugar;
using WMS.DAL;
using WMS.Entity.Context;
using WMS.Entity.DataEntity;
using WMS.IBLL.IDataServer;

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

        public StockDetailServer():base(Db)
        {
        }

        #region 托盘明细

        /// <summary>
        /// 获取托盘明细
        /// </summary>
        /// <param name="skuNo">物料号</param>
        /// <param name="skuName">物料名称</param>
        /// <param name="palletNo">托盘号</param>
        /// <param name="lotNo">批次号</param>
        /// <param name="boxNo">箱码</param>
        /// <param name="status">箱支状态</param>
        /// <param name="inspectMark">检验标记</param>
        /// <param name="bitPalletMark">零托标记</param>
        /// <param name="bitBoxMark">零箱标记</param>
        /// <param name="inspectStatus">质量状态</param>
        /// <returns></returns>
        public List<DataStockDetail> GetBindList(string skuNo, string skuName, string palletNo, string lotNo, string boxNo, string status, string inspectMark, string bitPalletMark, string bitBoxMark, string inspectStatus)
        {
            string str = "select Id,LotNo,LotText,SupplierLot,SkuNo,SkuName,Qty,LockQty,FrozenQty,WareHouseNo,AreaNo,LocatNo,PalletNo,PalletNo2,PalletNo3,InspectMark,BitPalletMark from DataStockDetail where IsDel = @isdel";
            //判断物料号是否为空
            if (!string.IsNullOrEmpty(skuNo))
            {
                str += " and SkuNo like @skuno";
            }
            //判断物料名称是否为空
            if (!string.IsNullOrEmpty(skuName))
            {
                str += " and SkuName like @skuname";
            }
            //判断托盘号是否为空
            if (!string.IsNullOrEmpty(palletNo))
            {
                str += " and PalletNo like @palletno";
            }
            //判断批次是否为空
            if (!string.IsNullOrEmpty(lotNo))
            {
                str += " and LotNo like @lotno";
            }
            //判断零托标记是否为空
            if (!string.IsNullOrEmpty(bitPalletMark))
            {
                str += " and BitPalletMark = @bitpalletmark";
            }

            int i = 0;
            //在箱码明细中获取相应数据
            string boxstr = "select StockDetailId from DataBoxInfo Where IsDel = @isdel";
            //判断箱码是否为空
            if (!string.IsNullOrEmpty(boxNo))
            {
                boxstr += " and BoxNo like @boxno";
                i = 1;
            }
            //判断箱支状态是否为空
            if (!string.IsNullOrEmpty(status))
            {
                boxstr += " and Status = @status";
                i = 1;
            }
            //判断检验标记是否为空
            if (!string.IsNullOrEmpty(inspectMark))
            {
                boxstr += " and InspectMark = @inspectmark";
                str += " and InspectMark = @inspectmark";
                i = 1;
            }
            //判断零箱标记是否为空
            if (!string.IsNullOrEmpty(bitBoxMark))
            {
                boxstr += " and BitBoxMark = @bitboxmark";
                i = 1;
            }
            //判断质量状态是否为空
            if (!string.IsNullOrEmpty(inspectStatus))
            {
                boxstr += " and InspectStatus = @inspectstatus";
                str += " and InspectStatus = @inspectstatus";
                i = 1;
            }
            //将箱码数据进行排序
            boxstr += " order by StockDetailId";
            //查出箱码明细中信息
            List<int> stockDetailId = Db.Ado.SqlQuery<int>(boxstr, new
            {
                isdel = "0", //是否删除
                boxno = "%" + boxNo + "%", //箱码
                status, //箱支状态
                inspectmark = inspectMark, //检验标记
                bitboxmark = bitBoxMark, //零箱标记
                inspectstatus = inspectStatus //质量状态
            });
            //去重
            // List<int> newArr = null;
            string arr = "";
            //判断是否有查箱码信息
            if (i == 1)
            {
                if (stockDetailId.Count > 0)
                {
                    //去重
                    arr += stockDetailId[0].ToString();

                    for (int a = 1; a < stockDetailId.Count; a++)
                    {
                        if (stockDetailId[a] != stockDetailId[a - 1])
                        {
                            arr += ',' + stockDetailId[a].ToString();
                        }
                    }
                }
                if (!string.IsNullOrEmpty(arr))
                {
                    str += $" and Id in ({arr})";
                }
                else
                {
                    str += " and Id = ''";
                }
            }

            //排序
            str += " order by PalletNo,SkuNo,LotNo";
            List<DataStockDetail> boxInforList = Db.Ado.SqlQuery<DataStockDetail>(str, new
            {
                isdel = "0", //是否删除
                skuno = "%" + skuNo + "%", //物料号
                skuname = "%" + skuName + "%", //物料名称
                palletno = "%" + palletNo + "%", //托盘
                lotno = "%" + lotNo + "%", //批次号
                bitpalletmark = bitPalletMark, //零托标记
                inspectmark = inspectMark, //检验标记
                inspectstatus = inspectStatus, //质量状态
                //stockdetailid = "(" + arr + ")" //库存明细id
            });

            return boxInforList;
        }


        #endregion

        #region 导出托盘明细
        /// <summary>
        /// 获取托盘明细
        /// </summary>
        /// <param name="skuNo">物料号</param>
        /// <param name="skuName">物料名称</param>
        /// <param name="palletNo">托盘号</param>
        /// <param name="lotNo">批次号</param>
        /// <param name="boxNo">箱码</param>
        /// <param name="status">箱支状态</param>
        /// <param name="inspectMark">检验标记</param>
        /// <param name="bitPalletMark">零托标记</param>
        /// <param name="bitBoxMark">零箱标记</param>
        /// <param name="inspectStatus">质量状态</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)
        {
            string str = "select Id,LotNo,LotText,SupplierLot,SkuNo,SkuName,Qty,LockQty,FrozenQty,AreaNo,LocatNo,PalletNo,PalletNo2,PalletNo3,InspectMark,BitPalletMark from DataStockDetail where IsDel = @isdel";
            //判断物料号是否为空
            if (!string.IsNullOrEmpty(skuNo))
            {
                str += " and SkuNo like @skuno";
            }
            //判断物料名称是否为空
            if (!string.IsNullOrEmpty(skuName))
            {
                str += " and SkuName like @skuname";
            }
            //判断托盘号是否为空
            if (!string.IsNullOrEmpty(palletNo))
            {
                str += " and PalletNo like @palletno";
            }
            //判断批次是否为空
            if (!string.IsNullOrEmpty(lotNo))
            {
                str += " and LotNo like @lotno";
            }
            //判断零托标记是否为空
            if (!string.IsNullOrEmpty(bitPalletMark))
            {
                str += " and BitPalletMark = @bitpalletmark";
            }

            int i = 0;
            //在箱码明细中获取相应数据
            string boxstr = "select StockDetailId from DataBoxInfo Where IsDel = @isdel";
            //判断箱码是否为空
            if (!string.IsNullOrEmpty(boxNo))
            {
                boxstr += " and BoxNo like @boxno";
                i = 1;
            }
            //判断箱支状态是否为空
            if (!string.IsNullOrEmpty(status))
            {
                boxstr += " and Status = @status";
                i = 1;
            }
            //判断检验标记是否为空
            if (!string.IsNullOrEmpty(inspectMark))
            {
                boxstr += " and InspectMark = @inspectmark";
                str += " and InspectMark = @inspectmark";
                i = 1;
            }
            //判断零箱标记是否为空
            if (!string.IsNullOrEmpty(bitBoxMark))
            {
                boxstr += " and BitBoxMark = @bitboxmark";
                i = 1;
            }
            //判断质量状态是否为空
            if (!string.IsNullOrEmpty(inspectStatus))
            {
                boxstr += " and InspectStatus = @inspectstatus";
                str += " and InspectStatus = @inspectstatus";
                i = 1;
            }
            //将箱码数据进行排序
            boxstr += " order by StockDetailId";
            //查出箱码明细中信息
            List<int> stockDetailId = Db.Ado.SqlQuery<int>(boxstr, new
            {
                isdel = "0", //是否删除
                boxno = "%" + boxNo + "%", //箱码
                status, //箱支状态
                inspectmark = inspectMark, //检验标记
                bitboxmark = bitBoxMark, //零箱标记
                inspectstatus = inspectStatus //质量状态
            });
            //去重
            // List<int> newArr = null;
            string arr = "";
            //判断是否有查箱码信息
            if (i == 1)
            {
                if (stockDetailId.Count > 0)
                {
                    //去重
                    arr += stockDetailId[0].ToString();

                    for (int a = 1; a < stockDetailId.Count; a++)
                    {
                        if (stockDetailId[a] != stockDetailId[a - 1])
                        {
                            arr += ',' + stockDetailId[a].ToString();
                        }
                    }
                }
                if (!string.IsNullOrEmpty(arr))
                {
                    str += $" and Id in ({arr})";
                }
                else
                {
                    str += " and Id = ''";
                }
            }

            //排序
            str += " order by LotNo,SkuNo,PalletNo";
            List<DataStockDetail> boxInforList = Db.Ado.SqlQuery<DataStockDetail>(str, new
            {
                isdel = "0", //是否删除
                skuno = "%" + skuNo + "%", //物料号
                skuname = "%" + skuName + "%", //物料名称
                palletno = "%" + palletNo + "%", //托盘
                lotno = "%" + lotNo + "%", //批次号
                bitpalletmark = bitPalletMark, //零托标记
                inspectmark = inspectMark, //检验标记
                inspectstatus = inspectStatus, //质量状态
                //stockdetailid = "(" + arr + ")" //库存明细id
            });
            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
    }
}