using Dm;
using Model.ModelDto.SysDto;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WMS.Entity.Context;
using WMS.Entity.SysEntity;
using WMS.IDAL.ISysInterface;

namespace WMS.DAL.SysInfrastructure
{
    public class MaterialsRepository : BaseRepository<SysMaterials>, IMaterialsRepository
    {
        private static readonly SqlSugarScope Db = DataContext.Db;
        public MaterialsRepository() : base(Db)
        {
        }

        #region wms

        /// <summary>
        /// 获取物料信息列表
        /// </summary>
        /// <param name="SkuNo">物料号</param>
        /// <param name="SkuName">物料名称</param>
        /// <param name="AuditStatusNo">审核状态</param>
        /// <param name="Type">类型</param>
        /// <param name="IsControlled">是否受控</param>
        /// <param name="IsInspect">是否免检</param>
        /// <param name="Environment">存储环境</param>
        /// <returns></returns>
        public List<MaterialsDto> GetMateList(string skuNo, string SkuName, string AuditStatusNo, string Type, string IsControlled, string IsInspect, string Environment)
        {
            string str = "select mate.*,user1.RealName CreateUserName,pack.PackagName PackagName,unit.UnitName UnitName from SysMaterials mate left join SysUserInfor user1 on mate.CreateUser = user1.Id left join SysPackag pack on mate.PackagNo = pack.PackagNo left join SysUnit unit on mate.UnitNo = unit.UnitNo where mate.IsDel = @isdel";
            //判断物料号是否为空
            if (skuNo != "undefined")
            {
                if (!string.IsNullOrEmpty(skuNo))
                {
                    str += " and mate.SkuNo like @skuno";
                }
            }
            //判断物料名称
            if (SkuName != "undefined")
            {
                if (!string.IsNullOrEmpty(SkuName))
                {
                    str += " and mate.SkuName like @skuname";
                }
            }
            //判断审核状态
            if (AuditStatusNo != "undefined")
            {
                if (!string.IsNullOrEmpty(AuditStatusNo))
                {
                    str += " and mate.AuditStatusNo = @auditstatusno";
                }
            }
            //判断类型
            if (Type != "undefined")
            {
                if (!string.IsNullOrEmpty(Type))
                {
                    str += " and mate.Type = @type";
                }
            }
            //判断是否受控
            if (IsControlled != "undefined")
            {
                if (!string.IsNullOrEmpty(IsControlled))
                {
                    str += " and mate.IsControlled = @iscontrolled";
                }
            }
            //判断是否免检
            if (IsInspect != "undefined")
            {
                if (!string.IsNullOrEmpty(IsInspect))
                {
                    str += " and mate.IsInspect = @isinspect";
                }
            }
            //判断存储环境
            if (Environment != "undefined")
            {
                if (!string.IsNullOrEmpty(Environment))
                {
                    str += " and mate.Environment = @environment";
                }
            }

            str += " order by mate.SkuNo";

            List<MaterialsDto> matedto = Db.Ado.SqlQuery<MaterialsDto>(str, new
            {
                isdel = "0", //是否删除
                skuno = "%" + skuNo + "%", //物料号
                skuname = "%" + SkuName + "%",// 物料名称
                auditstatusno = AuditStatusNo,// 审核状态
                type = Type,// 类型
                iscontrolled = IsControlled,// 是否受控
                isinspect = IsInspect,// 是否免检
                environment = Environment,// 存储环境
            });
            return matedto;
        }

        /// <summary>
        /// 根据id查询物料信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public SysMaterials GetMateById(int id)
        {
            string str = "select * from SysMaterials where IsDel = @isdel and Id = @id";
            SysMaterials mate = Db.Ado.SqlQuerySingle<SysMaterials>(str, new
            {
                isdel = "0", //是否删除
                id //id
            });
            return mate;
        }

        /// <summary>
        /// 根据编号查询物料信息
        /// </summary>
        /// <param name="SkuNo">物料编号</param>
        /// <returns></returns>
        public List<SysMaterials> GetMateByNo(string SkuNo)
        {
            string str = "select * from SysMaterials where SkuNo = @skuno";
            List<SysMaterials> mate = Db.Ado.SqlQuery<SysMaterials>(str, new
            {
                skuno = SkuNo //物料编号
            });
            return mate;
        }

        /// <summary>
        /// 新增物料信息
        /// </summary>
        /// <param name="mate">物料实体</param>
        /// <returns></returns>
        public async Task<int> AddMate(SysMaterials mate)
        {
            //string str = "insert into SysMaterials values(@skuno, @skuname, @standard, @auditstatusno, @type, @iscontrolled, @isinspect, @origin, @unitno, @packagno, @environment, @weight, @warranty, @price,null,null, @demo, @isdel, @createTime, @createUser, null, null,@adventtime,@lowinventory)";
            //mate.CreateTime = Db.GetDate();
            //int i = await Db.Ado.ExecuteCommandAsync(str, new
            //{
            //    skuno = mate.SkuNo, //物料号
            //    skuname = mate.SkuName, //物料名称
            //    standard = mate.Standard, //规格
            //    auditstatusno = "0", //审核状态
            //    type = mate.Type, //类型
            //    iscontrolled = "1", //是否受控
            //    isinspect = "0", //是否免检
            //    origin = "WMS", //来源
            //    unitno = mate.UnitNo, //单位
            //    packagno = mate.PackagNo, //包装
            //    environment = mate.Environment, //存储环境
            //    weight = mate.Weight, //理论重量
            //    warranty = mate.Warranty, //保质期
            //    price = mate.Price, //理论单价
            //    demo = mate.Demo, //备注
            //    isdel = "0", //是否删除
            //    createtime = Db.GetDate(), //创建时间
            //    createUser = mate.CreateUser, //创建人
            //    adventtime = mate.AdventTime, //临期
            //    lowinventory = mate.LowInventory //低库存
            //});
            mate.AuditStatusNo = "0";
            int i = await Db.Insertable(mate).ExecuteCommandAsync();
            return i;
        }

        /// <summary>
        /// 删除物料信息
        /// </summary>
        /// <param name="mate">物料实体</param>
        /// <returns></returns>
        public async Task<int> DelMate(SysMaterials mate)
        {
            //string str = "update SysMaterials set IsDel = @isdel, UpdateTime = @updatetime, UpdateUser = @updateuser where Id = @id";
            //int i = await Db.Ado.ExecuteCommandAsync(str, new
            //{
            //    isdel = "1", //是否删除 
            //    updatetime = Db.GetDate(), //更改时间
            //    updateuser = mate.UpdateUser, //更改人
            //    id = mate.Id //id
            //});
            mate.IsDel = "1";
            mate.UpdateTime = Db.GetDate(); //更改时间
            int i = await Db.Updateable(mate).ExecuteCommandAsync();
            return i;
        }

        /// <summary>
        /// 编辑物料信息
        /// </summary>
        /// <param name="mate">物料实体</param>
        /// <returns></returns>
        public async Task<int> ExitMate(SysMaterials mate)
        {

            //string str = "update SysMaterials set SkuName = @skuname, Standard = @standard, AuditStatusNo = @auditstatusno, Type = @type, IsControlled = @iscontrolled, IsInspect = @isinspect, UnitNo = @unitno, PackagNo = @packagno, Environment = @environment, Weight = @weight, Warranty = @warranty, Price = @price, Demo = @demo, UpdateTime = @updatetime, UpdateUser = @updateuser,AdventTime = @adventtime,LowInventory = @lowinventory where Id = @id";
            //int i = await Db.Ado.ExecuteCommandAsync(str, new
            //{
            //    skuname = mate.SkuName, //物料名称
            //    standard = mate.Standard, //规格
            //    auditstatusno = mate.AuditStatusNo, //审核状态
            //    type = mate.Type, //类型
            //    iscontrolled = mate.IsControlled, //是否受控
            //    isinspect = mate.IsInspect, //是否免检
            //    unitno = mate.UnitNo, //单位
            //    packagno = mate.PackagNo, //包装
            //    environment = mate.Environment, //存储环境
            //    weight = mate.Weight, //理论重量
            //    warranty = mate.Warranty, //保质期
            //    price = mate.Price, //理论单价
            //    demo = mate.Demo, //备注
            //    updatetime = Db.GetDate(), //更改时间
            //    updateuser = mate.UpdateUser, //更改人
            //    adventtime = mate.AdventTime, //临期
            //    lowinventory = mate.LowInventory, //低库存
            //    id = mate.Id //id
            //});
            int i = await Db.Updateable(mate).ExecuteCommandAsync();
            return i;
        }

        #region 包装 单位

        /// <summary>
        /// 获取计量单位信息列表
        /// </summary>
        /// <returns></returns>
        public List<SysUnit> GetUnitList()
        {
            string str = "select * from SysUnit where IsDel = @isdel";
            List<SysUnit> unitlist = Db.Ado.SqlQuery<SysUnit>(str, new
            {
                isdel = "0" //是否删除
            });
            return unitlist;
        }

        /// <summary>
        /// 获取包装信息列表
        /// </summary>
        /// <returns></returns>
        public List<SysPackag> GetPackagList()
        {
            string str = "select * from SysPackag where IsDel = @isdel";
            List<SysPackag> packlist = Db.Ado.SqlQuery<SysPackag>(str, new
            {
                isdel = "0" //是否删除
            });
            return packlist;
        }

        #endregion

        #endregion

        #region erp

        /// <summary>
        /// 获取erp数据
        /// </summary>
        /// <returns></returns>
        public List<ERPTestDto> GetERPList()
        {
            string str = "select * from SysERPTest where isdel = @isdel";
            List<ERPTestDto> list = Db.Ado.SqlQuery<ERPTestDto>(str, new
            {
                isdel = "0" //是否删除
            });
            return list;
        }

        /// <summary>
        /// 根据id获取erp数据
        /// </summary>
        /// <param name="id">id</param>
        /// <returns></returns>
        public SysERPTest GetERPListById(int id)
        {
            string str = "select * from SysERPTest where isdel = @isdel and Id = @id";
            SysERPTest list = Db.Ado.SqlQuerySingle<SysERPTest>(str, new
            {
                id, //id
                isdel = "0" //是否删除
            });
            return list;
        }

        /// <summary>
        /// 新增erp数据
        /// </summary>
        /// <param name="erp">erp测试实体</param>
        /// <returns></returns>
        public async Task<int> AddERP(SysERPTest erp)
        {
            string str = "Insert into SysERPTest values(@skuno, @skuname, @standard, @auditstatusno, @type, @iscontrolled, @isinspect, @origin, @unitno, @packagno, @environment, @weight, @warranty, @price, @demo, @isdel, @createTime, @createUser, null, null)";
            int i = await Db.Ado.ExecuteCommandAsync(str, new
            {
                skuno = erp.SkuNo, //物料号
                skuname = erp.SkuName, //物料名称
                standard = erp.Standard, //规格
                auditstatusno = "0", //审核状态
                type = erp.Type, //类型
                iscontrolled = "1", //是否受控
                isinspect = "0", //是否免检
                origin = "ERP", //来源
                unitno = erp.UnitNo, //单位
                packagno = erp.PackagNo, //包装
                environment = erp.Environment, //存储环境
                weight = erp.Weight, //理论重量
                warranty = erp.Warranty, //保质期
                price = erp.Price, //理论单价
                demo = erp.Demo, //备注
                isdel = "0", //是否删除
                createtime = Db.GetDate(), //创建时间
                createUser = erp.CreateUser //创建人
            });
            return i;
        }

        /// <summary>
        /// 删除erp数据
        /// </summary>
        /// <param name="erp">erp测试实体</param>
        /// <returns></returns>
        public async Task<int> DelERP(SysERPTest erp)
        {
            string str = "update SysERPTest set IsDel = @isdel, UpdateTime = @updatetime, UpdateUser = @updateuser where Id = @id";
            int i = await Db.Ado.ExecuteCommandAsync(str, new
            {
                isdel = "1", //是否删除 
                updatetime = Db.GetDate(), //更改时间
                updateuser = erp.UpdateUser, //更改人
                id = erp.Id //id
            });
            return i;
        }

        /// <summary>
        /// 编辑erp数据
        /// </summary>
        /// <param name="erp">erp测试实体</param>
        /// <returns></returns>
        public async Task<int> EditERP(SysERPTest erp)
        {
            string str = "update SysERPTest set SkuName = @skuname, Standard = @standard, AuditStatusNo = @auditstatusno, Type = @type, IsControlled = @iscontrolled, IsInspect = @isinspect, Origin = @origin, UnitNo = @unitno, PackagNo = @packagno, Environment = @environment, Weight = @weight, Warranty = @warranty, Price = @price, Demo = @demo, UpdateTime = @updatetime, UpdateUser = @updateuser where Id = @id";
            int i = await Db.Ado.ExecuteCommandAsync(str, new
            {
                skuname = erp.SkuName, //物料名称
                standard = erp.Standard, //规格
                auditstatusno = erp.AuditStatusNo, //审核状态
                type = erp.Type, //类型
                iscontrolled = erp.IsControlled, //是否受控
                isinspect = erp.IsInspect, //是否免检
                origin = erp.Origin, //来源
                unitno = erp.UnitNo, //单位
                packagno = erp.PackagNo, //包装
                environment = erp.Environment, //存储环境
                weight = erp.Weight, //理论重量
                warranty = erp.Warranty, //保质期
                price = erp.Price, //理论单价
                demo = erp.Demo, //备注
                updatetime = Db.GetDate(), //更改时间
                updateuser = erp.UpdateUser, //更改人
                id = erp.Id //id
            });
            return i;
        }

        #endregion
    }
}