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 (!string.IsNullOrEmpty(skuNo))
            {
                str += " and mate.SkuNo like @skuno";
            }
            //判断物料名称
            if (!string.IsNullOrEmpty(skuName))
            {
                str += " and mate.SkuName like @skuname";
            }
            //判断审核状态
            if (!string.IsNullOrEmpty(auditStatusNo))
            {
                str += " and mate.AuditStatusNo = @auditstatusno";
            }
            //判断类型
            if (!string.IsNullOrEmpty(type))
            {
                str += " and mate.Type = @type";
            }
            //判断是否受控
            if (!string.IsNullOrEmpty(isControlled))
            {
                str += " and mate.IsControlled = @iscontrolled";
            }
            //判断是否免检
            if (!string.IsNullOrEmpty(isInspect))
            {
                str += " and mate.IsInspect = @isinspect";
            }
            //判断存储环境
            if (!string.IsNullOrEmpty(environment))
            {
                str += " and mate.Environment = @environment";
            }
            List<MaterialsDto> matedto = Db.Ado.SqlQuery<MaterialsDto>(str, new
            {
                isdel = "0", //是否删除
                skuno = "%" + skuNo + "%",// 物料名称
                skuname = "%" + skuName + "%",// 物料名称
                auditstatusno = auditStatusNo,// 审核状态
                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 = "9", //审核状态
                type = mate.Type, //类型
                iscontrolled = string.IsNullOrWhiteSpace(mate.IsControlled) ? "0" : mate.IsControlled, //是否受控
                isinspect = string.IsNullOrWhiteSpace(mate.IsInspect) ? "0" : mate.IsInspect, //是否免检
                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 //低库存
            });
            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
            });
            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
            });
            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
    }
}