using Model.InterFaceModel;
using Model.ModelDto;
using Model.ModelDto.SysDto;
using Model.ModelVm;
using Model.ModelVm.SysVm;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using Model.ModelVm.BllAsnVm;
using Newtonsoft.Json;
using Utility.Tools;
using WMS.BLL.LogServer;
using WMS.DAL;
using WMS.Entity.BllAsnEntity;
using WMS.Entity.Context;
using WMS.Entity.SysEntity;
using WMS.IBLL.IBllAsnServer;

namespace WMS.BLL.BllAsnServer
{
    public class ArrivalNoticeServer : DbHelper<BllArrivalNotice>, IArrivalNoticeServer
    {
        #region 依赖注入 
        private static readonly SqlSugarScope Db = DataContext.Db;

        public ArrivalNoticeServer() : base(Db)
        {
        }
        #endregion

        #region 事件方法
        public List<ArrivalNoticeDto> GetArrivalNoticeList(ArrivalNoticeVm model, out int count)
        {
            string sqlString = string.Empty;
            string sqlCount = string.Empty;
            string sqlPub = string.Empty;
            try
            {
                sqlCount += "SELECT DISTINCT COUNT(tb1.ID) FROM BllArrivalNotice AS tb1 ";
                sqlString += "SELECT DISTINCT tb1.*,tb3.RealName as CreateUserName,tb4.RealName as UpdateUserName FROM BllArrivalNotice AS tb1 ";
                sqlPub += "LEFT JOIN BllArrivalNoticeDetail AS tb2 ON tb1.ASNNo = tb2.ASNNo ";
                sqlPub += "LEFT JOIN SysUserInfor AS tb3 ON tb1.CreateUser = tb3.Id ";
                sqlPub += "LEFT JOIN SysUserInfor AS tb4 ON tb1.UpdateUser = tb4.Id ";
                sqlPub += $"WHERE tb1.ASNNo LIKE '%{model.ASNNo}%' AND tb2.LotNo LIKE '%{model.LotNo}%' ";
                sqlPub += $"AND tb2.SkuNo  LIKE '%{model.SkuNo}%' AND tb2.SkuName  LIKE '%{model.SkuName}%' ";
                sqlPub += $"AND tb1.CustomerName  LIKE '%{model.CustomerName}%' ";
                if (!string.IsNullOrEmpty(model.Status))
                {
                    sqlPub += $"AND tb1.Status = '{model.Status}' ";
                }
                if (!string.IsNullOrEmpty(model.Type))
                {
                    sqlPub += $"AND tb1.Type = '{model.Type}' ";
                }
                if (!string.IsNullOrEmpty(model.StartTime))
                {
                    sqlPub += $"AND tb1.CreateTime >= '{model.StartTime}' ";
                }
                if (!string.IsNullOrEmpty(model.EndTime))
                {
                    sqlPub += $"AND tb1.CreateTime <= '{model.EndTime}' ";
                }
                sqlCount += sqlPub + "AND tb1.IsDel = '0' ";
                sqlPub += "AND tb1.IsDel = '0' order by tb1.ASNNo desc ";
                if (model.Page == 0)
                {
                    model.Page = 1;
                }
                sqlString += sqlPub + $" offset {((model.Page - 1) * model.Limit)} rows fetch next {model.Limit} rows only;";

                var com = new Common();
                count = com.GetRowCount(sqlCount);

                var modelList = Db.Ado.SqlQuery<ArrivalNoticeDto>(sqlString);

                return modelList;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public List<ArrivalNoticeDetailDto> GetArrivalNoticeDetailList(ArrivalNoticeDetailVm model, out int count)
        {
            string sqlString = string.Empty;
            string sqlCount = string.Empty;
            int rowCount = 1;
            try
            {
                if (model.Page == 0)
                {
                    model.Page = 1;
                }
                sqlCount += $"SELECT COUNT(ID) FROM BllArrivalNoticeDetail where ASNNo = '{model.ASNNo}' and isdel = '0';";
                var com = new Common();
                count = com.GetRowCount(sqlCount);
                if (count != 0)
                {
                    rowCount = count;
                }

                sqlString += "SELECT DISTINCT tb1.*,tb2.Status as AsnStatus,tb3.RealName as CreateUserName, ";
                sqlString += "tb4.RealName as UpdateUserName,isnull(tb6.UnitName,tb5.UnitNo) as UnitName,tb7.PackagName ";
                sqlString += "FROM BllArrivalNoticeDetail AS tb1 ";
                sqlString += "LEFT JOIN BllArrivalNotice AS tb2 ON tb1.ASNNo = tb2.ASNNo ";
                sqlString += "LEFT JOIN SysUserInfor AS tb3 ON tb1.CreateUser = tb3.Id ";
                sqlString += "LEFT JOIN SysUserInfor AS tb4 ON tb1.UpdateUser = tb4.Id ";
                sqlString += "LEFT JOIN SysMaterials AS tb5 on tb1.SkuNo = tb5.SkuNo ";
                sqlString += "LEFT JOIN SysUnit AS tb6 on tb5.UnitNo = tb6.UnitNo ";
                sqlString += "LEFT JOIN SysPackag AS tb7 on tb1.PackagNo = tb7.PackagNo ";
                sqlString += $"WHERE tb1.ASNNo = '{model.ASNNo}' AND tb1.IsDel = '0' order by tb1.SkuNo desc ";
                sqlString += $"offset {((model.Page - 1) * model.Limit)} rows fetch next {rowCount} rows only;";

                var modelList = Db.Ado.SqlQuery<ArrivalNoticeDetailDto>(sqlString);

                return modelList;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        //获取物料信息（添加入库单明细使用）
        public List<MaterialsDto> GetMaterialsList(GetMaterialsVm model)
        {
            try
            {
                Expression<Func<SysMaterials, bool>> item = Expressionable.Create<SysMaterials>()
                    .AndIF(!string.IsNullOrWhiteSpace(model.IsControlled), it => it.IsControlled == model.IsControlled)
                    .AndIF(true, it => it.IsDel == "0")
                    .And(it => it.SkuNo != "100099")
                    .AndIF(!string.IsNullOrWhiteSpace(model.SkuNo),
                    (it => it.SkuNo.Contains(model.SkuNo.Trim()) ||
                    it.SkuName.Contains(model.SkuNo.Trim()) ||
                    it.Standard.Contains(model.SkuNo.Trim())))
                    .ToExpression();//注意 这一句 不能少

                var data = DataContext.Db.Queryable<SysMaterials>().Where(item)
                    .Includes(x => x.TypeInfo)
                    .Includes(x => x.EnvironmentInfo)
                    .Includes(x => x.UnitInfo)
                    .Includes(x => x.PackagInfo)
                    .ToList();

                return data.Select(m => new MaterialsDto()
                {
                    //Id = m.Id,
                    SkuNo = m.SkuNo,
                    SkuName = m.SkuName,
                    Standard = m.Standard,
                    AuditStatusNo = m.AuditStatusNo,
                    Type = m.Type,
                    TypeName = m.TypeInfo == null ? "" : m.TypeInfo.DictName,
                    IsControlled = m.IsControlled,
                    IsInspect = m.IsInspect,
                    Origin = m.Origin,
                    UnitNo = m.UnitNo,
                    UnitName = m.UnitInfo == null ? "" : m.UnitInfo.UnitName,
                    PackagNo = m.PackagNo,
                    PackagName = m.PackagInfo == null ? "" : m.PackagInfo.PackagName,
                    Environment = m.Environment,
                    EnvironmentName = m.EnvironmentInfo == null ? "" : m.EnvironmentInfo.DictName,
                    Weight = m.Weight,
                    Warranty = m.Warranty,
                    Price = m.Price,
                    Demo = m.Demo,
                }).ToList();
            }
            catch (Exception e)
            {
                throw e;
            }
        }

        //添加入库单据
        public string AddArrivalNotice(ArrivalNoticeVm model)
        {
            string strMessage = "";
            string sqlString = string.Empty;
            string sqlDetailStr = string.Empty;
            try
            {
                if (model.Origin == "录入")
                {
                    model.ASNNo = new Common().GetMaxNo("ASN");
                }
                else
                {
                    if (string.IsNullOrEmpty(model.ASNNo))
                    {
                        throw new Exception("单据号不可为空!");
                    }
                    if (string.IsNullOrEmpty(model.Type))
                    {
                        throw new Exception("单据类型不可为空!");
                    }
                }

                sqlString += "Insert into BllArrivalNotice (ASNNo,Type,Origin,CustomerNo,";
                sqlString += "CustomerName,LotNo,LotText,SupplierLot,CreateUser) values ( ";
                sqlString += $"'{model.ASNNo}','{model.Type}','{model.Origin}','{model.CustomerNo}', ";
                sqlString += $"'{model.CustomerName}','{model.LotNo}','{model.LotText}','{model.SupplierLot}','{model.CreateUser}');";

                var detailModels = model.AsnDetail;
                foreach (ArrivalNoticeDetailVm detailModel in detailModels)
                {
                    if (string.IsNullOrEmpty(detailModel.SkuNo))
                    {
                        strMessage = "-1:物料编码不可为空;";
                        continue;
                    }
                    if (string.IsNullOrEmpty(detailModel.SkuName))
                    {
                        strMessage = "-1:物料名称不可为空;";
                        continue;
                    }
                    if (detailModel.Qty == null || detailModel.Qty <= 0)
                    {
                        strMessage = "-1:数量必须大于0;";
                        continue;
                    }
                    if (string.IsNullOrEmpty(detailModel.LotNo))
                    {
                        strMessage = "-1:批次号不可为空;";
                        continue;
                    }
                    // 判断是否已存在当前明细
                    string sqlCount = $"SELECT COUNT(ID) FROM BllArrivalNoticeDetail where ASNNo = '{model.ASNNo}' ";
                    sqlCount += $"and SkuNo ='{detailModel.SkuNo}' and LotNo = '{detailModel.LotNo}' and id != '{detailModel.Id}' and isdel = '0';";
                    var com = new Common().GetRowCount(sqlCount);
                    if (com > 0)
                    {
                        strMessage = "-1:物料和批次号重复;";
                        continue;
                    }
                    if (detailModel.Price == null)
                    {
                        detailModel.Price = 0;
                    }
                    // 计算出总金额
                    detailModel.Money = detailModel.Price * detailModel.Qty;
                    sqlDetailStr += "Insert into BllArrivalNoticeDetail (ASNNo,SkuNo,SkuName,Standard,";
                    sqlDetailStr += "LotNo,LotText,Qty,PackagNo,Price,Money,IsBale,IsBelt,SupplierLot,Status,UDF1,UDF2,UDF3,UDF4,UDF5,CreateUser) values ( ";
                    sqlDetailStr += $"'{model.ASNNo}','{detailModel.SkuNo}','{detailModel.SkuName}','{detailModel.Standard}', ";
                    sqlDetailStr += $"'{detailModel.LotNo}','{detailModel.LotText}','{detailModel.Qty}','{detailModel.PackagNo}','{detailModel.Price}', ";
                    sqlDetailStr += $"{detailModel.Money},'{detailModel.IsBale}','{detailModel.IsBelt}','{detailModel.SupplierLot}','0','{detailModel.UDF1}','{detailModel.UDF2}','{detailModel.UDF3}','{detailModel.UDF4}','{detailModel.UDF5}','{model.CreateUser}');";
                }

                if (sqlDetailStr == string.Empty)
                {
                    return strMessage;
                }
                Db.Ado.BeginTran();

                int rowCount = Db.Ado.ExecuteCommand(sqlString);
                var rowDetailCount = 1;
                if (sqlDetailStr.Length > 0)
                {
                    rowDetailCount = Db.Ado.ExecuteCommand(sqlDetailStr);
                }
                Db.Ado.CommitTran();
                if (rowCount > 0 && rowDetailCount > 0)
                {
                    new OperationASNServer().AddLogOperationAsn("入库作业", "入库单据", model.ASNNo, "添加", $"添加了单据号为{model.ASNNo}的单据信息", Convert.ToInt32(model.CreateUser));
                    return strMessage;
                }
                else
                {
                    Db.Ado.RollbackTran();
                    return "-2:添加失败数据回滚!";
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        //编辑入库单据
        public string EditArrivalNotice(ArrivalNoticeVm model)
        {
            string strMessage = "";
            string sqlString = string.Empty;
            string sqlDetailStr = string.Empty;
            try
            {
                if (model.Origin != "录入")
                {
                    strMessage = "-1:上游系统下发的单据，不可编辑;";
                    return strMessage;
                }

                sqlString += $"select * from BllArrivalNotice where ASNNo = '{model.ASNNo}' and isDel = '0';";
                var asnInfo = Db.Ado.SqlQuery<BllArrivalNotice>(sqlString).FirstOrDefault();
                if (asnInfo.Status != "0")
                {
                    strMessage = "-1:单据状态变更，不可编辑;";
                    return strMessage;
                }

                sqlString = "UPDATE BllArrivalNotice SET ";
                sqlString += $"CustomerNo = '{model.CustomerNo}',CustomerName = '{model.CustomerName}',";
                sqlString += $"LotNo = '{model.LotNo}',LotText = '{model.LotText}',";                
                sqlString += $"UpdateTime = GETDATE(),UpdateUser = '{model.CreateUser}' ";
                sqlString += $"WHERE ASNNo = '{model.ASNNo}'";

                var detailModels = model.AsnDetail;
                sqlDetailStr += $"SELECT * FROM BllArrivalNoticeDetail WHERE ASNNo = '{model.ASNNo}' and isDel = '0';";
                var dbDetailModels = DataContext.Db.Ado.SqlQuery<BllArrivalNoticeDetail>(sqlDetailStr);

                sqlDetailStr = string.Empty;
                // 处理已删除的明细
                foreach (BllArrivalNoticeDetail dbDetailModel in dbDetailModels)
                {
                    if (detailModels.Count(it => it.Id == dbDetailModel.Id) == 0)
                    {
                        sqlDetailStr += $"UPDATE BllArrivalNoticeDetail SET IsDel = '1',";
                        sqlDetailStr += $"UpdateTime = GETDATE(),UpdateUser = '{model.CreateUser}' ";
                        sqlDetailStr += $"WHERE Id = {dbDetailModel.Id};";
                    }
                }

                // 处理修改和添加的明细
                foreach (ArrivalNoticeDetailVm detailModel in detailModels)
                {
                    if (string.IsNullOrEmpty(detailModel.SkuNo))
                    {
                        strMessage = "-1:物料编码不可为空;";
                        continue;
                    }
                    if (string.IsNullOrEmpty(detailModel.SkuName))
                    {
                        strMessage = "-1:物料名称不可为空;";
                        continue;
                    }
                    if (detailModel.Qty == null || detailModel.Qty <= 0)
                    {
                        strMessage = "-1:数量必须大于0;";
                        continue;
                    }
                    if (string.IsNullOrEmpty(detailModel.LotNo))
                    {
                        strMessage = "-1:批次号不可为空;";
                        continue;
                    }
                    // 判断是否已存在当前明细
                    string sqlCount = $"SELECT COUNT(ID) FROM BllArrivalNoticeDetail where ASNNo = '{model.ASNNo}' ";
                    sqlCount += $"and SkuNo ='{detailModel.SkuNo}' and LotNo = '{detailModel.LotNo}' and id != '{detailModel.Id}' and isdel = '0';";
                    var com = new Common().GetRowCount(sqlCount);
                    if (com > 0)
                    {
                        strMessage = "-1:物料和批次号重复;";
                        continue;
                    }

                    if (detailModel.Price == null)
                    {
                        detailModel.Price = 0;
                    }
                    if (detailModel.Id == 0 || detailModel.Id == null)
                    {
                        // 计算出总金额
                        detailModel.Money = detailModel.Price * detailModel.Qty;
                        sqlDetailStr += "Insert into BllArrivalNoticeDetail (ASNNo,SkuNo,SkuName,Standard,";
                        sqlDetailStr += "LotNo,LotText,Qty,PackagNo,Price,Money,IsBale,IsBelt,SupplierLot,Status,UDF1,UDF2,UDF3,UDF4,UDF5,CreateUser) values ( ";
                        sqlDetailStr += $"'{model.ASNNo}','{detailModel.SkuNo}','{detailModel.SkuName}','{detailModel.Standard}', ";
                        sqlDetailStr += $"'{detailModel.LotNo}','{detailModel.LotText}','{detailModel.Qty}','{detailModel.PackagNo}','{detailModel.Price}', ";
                        sqlDetailStr += $"{detailModel.Money},'{detailModel.IsBale}','{detailModel.IsBelt}','{detailModel.SupplierLot}','0','{detailModel.UDF1}','{detailModel.UDF2}','{detailModel.UDF3}','{detailModel.UDF4}','{detailModel.UDF5}','{model.CreateUser}');";
                    }
                    else
                    {
                        //判断是否更改
                        int rowNum = dbDetailModels
                            .Count(it => it.Id == detailModel.Id
                                         && it.Qty == detailModel.Qty
                                         && it.LotNo == detailModel.LotNo
                                         && it.LotText == detailModel.LotText
                                         && it.SupplierLot == detailModel.SupplierLot
                                         && it.IsBale == detailModel.IsBale
                                         && it.IsBelt == detailModel.IsBelt
                                         && it.UDF1 == detailModel.UDF1
                                         && it.UDF2 == detailModel.UDF2
                                         && it.UDF3 == detailModel.UDF3
                                         && it.UDF4 == detailModel.UDF4
                                         && it.UDF5 == detailModel.UDF5);
                        if (rowNum > 0)
                        {
                            continue;
                        }
                        // 计算出总金额
                        detailModel.Money = detailModel.Price * detailModel.Qty;
                        sqlDetailStr += $"UPDATE BllArrivalNoticeDetail SET ";
                        sqlDetailStr += $"Qty = '{detailModel.Qty}',LotNo = '{detailModel.LotNo}',Money='{detailModel.Money}', ";
                        sqlDetailStr += $"LotText = '{detailModel.LotText}',SupplierLot = '{detailModel.SupplierLot}', ";
                        sqlDetailStr += $"IsBale = '{detailModel.IsBale}',IsBelt = '{detailModel.IsBelt}', ";
                        sqlDetailStr += $"UDF1 = '{detailModel.UDF1}',UDF2 = '{detailModel.UDF2}',UDF3 = '{detailModel.UDF3}',UDF4 = '{detailModel.UDF4}',UDF5 = '{detailModel.UDF5}',";
                        sqlDetailStr += $"UpdateTime = GETDATE(),UpdateUser = '{model.CreateUser}' ";
                        sqlDetailStr += $"WHERE Id = {detailModel.Id};";
                    }
                }

                Db.Ado.BeginTran();

                int rowCount = Db.Ado.ExecuteCommand(sqlString);
                var rowDetailCount = 1;
                if (sqlDetailStr.Length > 0)
                {
                    rowDetailCount = Db.Ado.ExecuteCommand(sqlDetailStr);
                }
                Db.Ado.CommitTran();
                if (rowCount > 0 && rowDetailCount > 0)
                {
                    new OperationASNServer().AddLogOperationAsn("入库作业", "入库单据", model.ASNNo, "编辑", $"编辑了单据号为{model.ASNNo}的单据信息", Convert.ToInt32(model.CreateUser));
                    return strMessage;
                }
                else
                {
                    Db.Ado.RollbackTran();
                    return "-2:添加失败数据回滚!";
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        //删除入库单
        public string DelArrivalNotice(ArrivalNoticeVm model)
        {
            try
            {
                var sqlString = $"SELECT * FROM BllArrivalNotice WHERE ASNNo = '{model.ASNNo}' AND IsDel = 0 AND Status = '0';";
                var asnModel = Db.Ado.SqlQuery<BllArrivalNotice>(sqlString).FirstOrDefault();

                if (asnModel == null)
                {
                    return "-1:入库单状态已变更，不可删除!";

                }
                if (asnModel.Origin != "录入")
                {
                    return "-1:上游系统下发的单据，不可删除;";

                }

                // 删除总单
                sqlString = $"UPDATE BllArrivalNotice SET IsDel = '1',";
                sqlString += $"UpdateTime = GETDATE(),UpdateUser = '{model.CreateUser}' ";
                sqlString += $"WHERE ASNNo = '{model.ASNNo}';";

                // 删除明细单
                sqlString += $"UPDATE BllArrivalNoticeDetail SET IsDel = '1',";
                sqlString += $"UpdateTime = GETDATE(),UpdateUser = '{model.CreateUser}' ";
                sqlString += $"WHERE ASNNo = '{model.ASNNo}';";
                Db.Ado.BeginTran();
                int rowCount = Db.Ado.ExecuteCommand(sqlString);

                if (rowCount < 2)
                {
                    Db.RollbackTran();
                    return "-1:删除失败！";
                }
                Db.Ado.CommitTran();
                new OperationASNServer().AddLogOperationAsn("入库作业", "入库单据", model.ASNNo, "删除", $"删除了单据号为{model.ASNNo}的单据信息", Convert.ToInt32(model.CreateUser));
                return "";
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        //删除入库单明细
        public string DelArrivalNoticeDetail(ArrivalNoticeDetailVm model)
        {
            string sqlString = string.Empty;
            try
            {
                sqlString = $"SELECT * FROM BllArrivalNotice WHERE ASNNo = '{model.ASNNo}' AND IsDel = 0 AND Status = '0';";
                var asnModel = Db.Ado.SqlQuery<BllArrivalNotice>(sqlString).FirstOrDefault();

                if (asnModel == null)
                {
                    return "-1:入库单状态已变更，不可删除!";

                }
                if (asnModel.Origin != "录入")
                {
                    return "-1:上游系统下发的单据，不可删除;";

                }

                sqlString = $"SELECT * FROM BllArrivalNoticeDetail WHERE ASNNo = '{model.ASNNo}' and Id <> {model.Id};";
                var detailModel = Db.Ado.SqlQuery<ArrivalNoticeDetailVm>(sqlString);

                if (detailModel.Count <= 0)
                {
                    sqlString = $"UPDATE BllArrivalNotice SET IsDel = '1',";
                    sqlString += $"UpdateTime = GETDATE(),UpdateUser = '{model.CreateUser}' ";
                    sqlString += $"WHERE ASNNo = '{model.ASNNo}';";
                }
                else
                {
                    sqlString = $"UPDATE BllArrivalNotice SET ";
                    sqlString += $"UpdateTime = GETDATE(),UpdateUser = '{model.CreateUser}' ";
                    sqlString += $"WHERE ASNNo = '{model.ASNNo}';";
                }

                sqlString += $"UPDATE BllArrivalNoticeDetail SET IsDel = '1',";
                sqlString += $"UpdateTime = GETDATE(),UpdateUser = '{model.CreateUser}' ";
                sqlString += $"WHERE Id = '{model.Id}';";

                Db.Ado.BeginTran();
                int rowCount = Db.Ado.ExecuteCommand(sqlString);

                if (rowCount < 2)
                {
                    Db.RollbackTran();
                    return "-1:删除失败！";
                }
                Db.Ado.CommitTran();
                var detail = Db.Queryable<BllArrivalNoticeDetail>().First(m => m.Id == model.Id);
                new OperationASNServer().AddLogOperationAsn("入库作业", "入库单据", model.ASNNo, "删除", $"删除了单据号为{model.ASNNo}、物料为{detail.SkuNo}、批次号为{detail.LotNo}的单据信息", Convert.ToInt32(model.CreateUser));
                return "";
            }
            catch (Exception ex)
            {
                Db.RollbackTran();
                throw new Exception(ex.Message);
            }
        }
        //关闭入库单
        public string CloseArrivalNotice(int id, int userId)
        {
            try
            {
                var notice = Db.Queryable<BllArrivalNotice>().Where(m => m.IsDel == "0" && m.Id == id).ToList().FirstOrDefault();
                if (notice == null)
                {
                    throw new Exception("未查询到入库单据信息");
                }
                //if (notice.Status != "2")
                //{
                //    throw new Exception("参数异常,请检查状态是否为执行完成");
                //}

                var sqlString = "";

                // 总单关单
                sqlString = $"UPDATE BllArrivalNotice SET Status = '3',";
                sqlString += $"CompleteTime = GETDATE(),UpdateTime = GETDATE(),UpdateUser = '{userId}' ";
                sqlString += $"WHERE Id = '{id}';";

                // 将未完成的明细改为已完成
                sqlString += $"UPDATE BllArrivalNoticeDetail SET Status = '2',";
                sqlString += $"CompleteTime = GETDATE(),UpdateTime = GETDATE(),UpdateUser = '{userId}' ";
                sqlString += $"WHERE Id = '{id}' and Status <> '2';";
                Db.Ado.BeginTran();
                int rowCount = Db.Ado.ExecuteCommand(sqlString);

                if (rowCount <= 0)
                {
                    Db.RollbackTran();
                    return "-1:关单失败！";
                }
                Db.Ado.CommitTran();
                new OperationASNServer().AddLogOperationAsn("入库作业", "入库单据", notice.ASNNo, "关单", $"关闭了单据号为{notice.ASNNo}的单据信息", userId);
                return "";
            }
            catch (Exception ex)
            {
                Db.RollbackTran();
                throw ex;
            }
        }
        //入库单撤销申请
        public void CancelOrder(int id,string reason,int userId)
        {
            try
            {
                var notice = Db.Queryable<BllArrivalNotice>().First(m => m.IsDel == "0" && m.Id == id);
                if (notice == null)
                {
                    throw new Exception("未查询到入库单据信息");
                }
                if (notice.Status!="1" && notice.Status != "2")
                {
                    throw new Exception("入库单据状态不是正在执行或执行完成，不能撤销");
                }

                var bind = Db.Queryable<BllPalletBind>().Where(m => m.IsDel == "0" && m.ASNNo == notice.ASNNo).ToList();
                if (bind.Count==0)
                {
                    throw new Exception("未查询到入库单据的托盘绑定信息");
                }

                if (bind.Count(m=>m.Status== "1")>0)
                {
                    throw new Exception("当前入库单据的托盘绑定信息有正在执行的，请稍后完成后再撤销");
                }

                var auditLog = Db.Queryable<BllAuditLog>().Where(m => m.IsDel == "0" && m.Status == "0");
                var boxInfo = Db.Queryable<BllBoxInfo>().Where(m => m.IsDel == "0" && m.ASNNo == notice.ASNNo);
                if (auditLog.Count(m=>m.OrderNo == notice.ASNNo)>0)
                {
                    throw new Exception("当前入库单据已有撤销审核，请等待审核完成");
                }
                

                var startStatusName = "";
                var endStatusName = "";
                switch (notice.Status)
                {
                    case "1":
                        startStatusName = "正在执行";
                        endStatusName = "等待执行";
                        break;
                    case "2":
                        startStatusName = "执行完成";
                        endStatusName = "正在执行";
                        break;
                    default:
                        throw new Exception("单据状态错误"); 
                }

                var palletStr = "";
                var msgS = "";
                var msgL = "";
                foreach (var item in bind)
                {
                    if (auditLog.Count(m=>m.OrderNo ==item.ASNNo && m.PalletNo.Contains(item.PalletNo)) >0)
                    {
                        continue;
                    }

                    if (!palletStr.Contains(item.PalletNo))
                    {
                        palletStr += item.PalletNo+";";
                    }
                    
                    var info = boxInfo.First(m => m.BindNo == item.Id);
                    if (!msgS.Contains(info.SkuNo))
                    {
                        msgS += info.SkuNo + ";";
                    }

                    if (!msgL.Contains(info.LotNo))
                    {
                        msgL += info.LotNo + ";";

                    }
                }
                var time = DateTime.Now;
                var log = new BllAuditLog()
                {
                    OrderNo = notice.ASNNo,
                    PalletNo = palletStr,
                    Msg = $"物料：{msgS} 批次：{msgL}",
                    Reason = reason,
                    Status = "0",
                    Opinion = "",
                    StartStatus = startStatusName,
                    EndStatus = endStatusName,
                    FunctionCode = "001",

                    IsDel = "0",
                    CreateUser = userId,
                    CreateTime = time
                }; 
                Db.Insertable(log).ExecuteCommand();
                new OperationASNServer().AddLogOperationAsn("入库作业", "入库单据", notice.ASNNo, "添加", $"添加了单据号为{notice.ASNNo}的单据撤销申请", userId);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }

        /// <summary>
        /// 维护入库单备注信息
        /// </summary>
        /// <param name="id"></param>
        /// <param name="demo"></param>
        /// <param name="userId"></param>
        public void EditNoticeDemo(int id, string demo, int userId)
        {
            try
            {
                var notice = Db.Queryable<BllArrivalNotice>().First(m => m.IsDel == "0" && m.Id == id);
                if (notice == null)
                {
                    throw new Exception("未查询到入库单据信息");
                }
                notice.Demo = demo + "".Trim();
                notice.UpdateUser = userId;
                notice.UpdateTime = DateTime.Now;

                int i = Db.Updateable(notice).ExecuteCommand();
                if (i > 0)
                {
                    //添加操作日志
                    new OperationASNServer().AddLogOperationAsn("入库作业", "入库单据", notice.ASNNo, "编辑", $"编辑了单据号为{notice.ASNNo}的备注信息", userId);
                }                
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        #endregion

        #region 接口方法
        /// <summary>
        /// 创建入库单据（上游系统）
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public ErpModel CreateAsn(AsnInfo model)
        {
            try
            {
                var resultModel = new ErpModel() { Success = -1, Message = "" };
                if (string.IsNullOrEmpty(model.AsnType))
                {
                    resultModel.Message = "单据类型不可为空!";
                    return resultModel;
                }
                if (string.IsNullOrEmpty(model.Customer))
                {
                    resultModel.Message = "客户不可为空!";
                    return resultModel;
                }
                if (string.IsNullOrEmpty(model.OrderCode))
                {
                    resultModel.Message = "上游系统单号不可为空!";
                    return resultModel;
                }
                if (model.AsnDetails.Count <= 0)
                {
                    resultModel.Message = "入库单明细不可为空!";
                    return resultModel;
                }

                // 根据客户编号获取客户名称
                var CustomerModel = Db.Queryable<SysCustomer>().First(it => it.CustomerNo == model.Customer && it.IsDel == "0");
                if (CustomerModel == null)
                {
                    resultModel.Message = "客户编号不存在!";
                    return resultModel;
                }

                // 入库总表信息
                string asnNo = new Common().GetMaxNo("ASN");
                var asnModel = new BllArrivalNotice()
                {
                    ASNNo = asnNo,
                    Type = model.AsnType,
                    Origin = model.Origin,
                    CustomerNo = model.Customer,
                    CustomerName = CustomerModel.CustomerName,
                    OrderCode = model.OrderCode,
                };

                // 入库明细表信息
                List<BllArrivalNoticeDetail> detailModels = new List<BllArrivalNoticeDetail>();
                foreach (AsnDetail asnDetailModel in model.AsnDetails)
                {
                    if (string.IsNullOrEmpty(asnDetailModel.SkuNo))
                    {
                        resultModel.Message = "物料编码不可为空!";
                        return resultModel;
                    }

                    if (asnDetailModel.Qty <= 0)
                    {
                        resultModel.Message = "数量应为正整数!";
                        return resultModel;
                    }
                    if (string.IsNullOrEmpty(asnDetailModel.LotNo))
                    {
                        resultModel.Message = "批次号不可为空!";
                        return resultModel;
                    }

                    // 获取物料详细信息
                    var skuModel = Db.Queryable<SysMaterials>().First(it => it.SkuNo == asnDetailModel.SkuNo && it.IsDel == "0");
                    if (skuModel == null)
                    {
                        resultModel.Message = "不存在当前物料信息!";
                        return resultModel;
                    }

                    // 验证包装信息是否存在

                    var detailModel = new BllArrivalNoticeDetail()
                    {
                        ASNNo = asnNo,
                        SkuNo = asnDetailModel.SkuNo,
                        SkuName = skuModel.SkuName,
                        Standard = skuModel.Standard,
                        LotNo = asnDetailModel.LotNo,
                        LotText = "",
                        Qty = asnDetailModel.Qty,
                        PackagNo = asnDetailModel.PackagNo,
                        SupplierLot = asnDetailModel.SupplierLot,
                        Status = "0",
                        CreateUser = 0
                    };

                    detailModels.Add(detailModel);
                }

                Db.BeginTran();
                // 插入入库单总表
                Db.Insertable(asnModel).ExecuteCommand();

                // 插入入库单明细表
                Db.Insertable(detailModels).ExecuteCommand();
                Db.CommitTran();

                resultModel.Success = 0;
                resultModel.Message = "成功";
                return resultModel;
            }
            catch (Exception ex)
            {
                Db.RollbackTran();
                throw ex;
            }
        }

        //订单回传上游系统
        public bool FinishAsn(int id, string url, int userId)
        {
            try
            {
                var notice = Db.Queryable<BllArrivalNotice>().First(m => m.Id == id && m.IsDel == "0");
                if (notice == null)
                {
                    throw new Exception("未查询到单据信息");
                }
                var detail = Db.Queryable<BllArrivalNoticeDetail>().Where(m => m.ASNNo == notice.ASNNo && m.IsDel == "0").ToList();
                if (detail.Count == 0)
                {
                    throw new Exception("未查询到单据明细信息");
                }
                var list = new List<AsnDetail>();
                foreach (var d in detail)
                {
                    var item = new AsnDetail()
                    {
                        SkuNo = d.SkuNo,
                        LotNo = d.LotNo,
                        Qty = int.Parse(d.CompleteQty.ToString()),
                        PackagNo = d.PackagNo,
                        SupplierLot = d.SupplierLot
                    };
                    list.Add(item);
                }
                var soInfo = new AsnInfo()
                {
                    OrderCode = notice.OrderCode,
                    AsnDetails = list
                };
                // 通过接口发送至erp
                var jsonData = JsonConvert.SerializeObject(soInfo);

                var response = HttpHelper.DoPost(url, jsonData, "入库单完成上传", "ERP");

                //解析返回数据
                var obj = JsonConvert.DeserializeObject<ErpModel>(response);
                if (obj.Success != 0)
                {
                    throw new Exception("上传失败" + obj.Message);
                }
                notice.Status = "4";
                notice.UpdateTime = DateTime.Now;
                notice.UpdateUser = userId;
                Db.Updateable(notice).ExecuteCommand();
                return true;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
        }

        #endregion
    }
    
}