OA2023/OASystem/OASystem.Infrastructure/Repositories/Resource/ResItemInfoRepository.cs

using OASystem.Domain.Dtos.Resource;
using OASystem.Domain.Entities.Resource;
using OASystem.Domain.ViewModels.Resource;
using Serilog;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace OASystem.Infrastructure.Repositories.Resource
{
    public class ResItemInfoRepository : BaseRepository<Res_ItemDetailInfo, Res_ItemInfoView>
    {
        public ResItemInfoRepository(SqlSugarClient sqlSugar) : base(sqlSugar)
        {
        }

        #region 供应商

        /// <summary>
        /// 供应商列表查询
        /// </summary>
        /// <param name="_dto"></param>
        /// <returns></returns>
        public Res_ItemVendorListView GetVendorList(Search_ResItemVendorDto _dto)
        {
            string sqlWhere = string.Format(" Where IsDel=0 ");

            #region SqlWhere

            if (!string.IsNullOrEmpty(_dto.VendorAddress))//供应商联系地址
            {
                sqlWhere += string.Format(" And [Address] Like '%{0}%' ", _dto.VendorAddress);
            }
            if (!string.IsNullOrEmpty(_dto.VendorLinker))//供应商联系人
            {
                sqlWhere += string.Format(" And Linker Like '%{0}%' ", _dto.VendorLinker);
            }
            if (!string.IsNullOrEmpty(_dto.VendorMobile))//供应商联系电话
            {
                sqlWhere += string.Format(" And Mobile Like '%{0}%' ", _dto.VendorMobile);
            }
            if (!string.IsNullOrEmpty(_dto.VendorName))//供应商公司名称/简称
            {
                sqlWhere += string.Format(" And ( FullName Like '%{0}%' Or ShortName Like '%{0}%' ) ", _dto.VendorName);
            }

            //if (!string.IsNullOrEmpty(sqlWhere.Trim()))
            //{
            //    Regex r = new Regex("And");
            //    sqlWhere = r.Replace(sqlWhere, "Where", 1);
            //}
            #endregion

            int currPIndex = (((_dto.PageIndex > 0) ? (_dto.PageIndex - 1) : 0) * _dto.PageSize) + 1;
            int currPSize = (((_dto.PageIndex > 0) ? (_dto.PageIndex - 1) : 0) + 1) * _dto.PageSize;

            string sql = string.Format(@" Select ROW_NUMBER() Over(order By Id desc) as RowNumber,* From ( 
 Select Id,FullName,ShortName,Address,Linker,Job,Mobile,Remark From Res_ItemVendor With(Nolock) {2}
) ) as tb Where tb.RowNumber Between {0} And {1} ", currPIndex, currPSize, sqlWhere);
            Res_ItemVendorListView rst = new Res_ItemVendorListView();
            rst.CurrPageIndex = currPIndex;
            rst.CurrPageSize = currPSize;
            rst.DataList = _sqlSugar.SqlQueryable<Res_ItemVendorView>(sql).ToList();
            if (rst.DataList.Count > 0)
            {
                string sqlCount = string.Format(@" Select Id From Res_ItemVendor With(Nolock) {0} ", sqlWhere);
                int dataCount = _sqlSugar.SqlQueryable<Res_ItemVendorView>(sqlCount).Count();
                rst.DataCount = dataCount;
            }

            return rst;
        }

        /// <summary>
        /// 供应商信息查询
        /// </summary>
        /// <param name="_dto"></param>
        /// <returns></returns>
        public Res_ItemVendorView getVendorInfo(Search_ResItemVendorDto _dto)
        {
            string sqlWhere = string.Format(" Where IsDel=0 ");

            if (_dto.VendorId > 0)
            {
                Res_ItemVendorView _view = _sqlSugar.Queryable<Res_ItemVendorView>().Where(s => s.Id == _dto.VendorId).First();
                return _view;
            }
            else
            {
                return null;
            }

            return null;
        }

        /// <summary>
        /// 创建供应商信息
        /// </summary>
        /// <param name="_dto"></param>
        /// <returns></returns>
        public async Task<bool> addVendorInfo(Edit_ResItemVendorDto _dto)
        {
            Dictionary<string, object> dic = new Dictionary<string, object>();

            dic.Add("FullName", _dto.VendorFullName);
            dic.Add("ShortName", string.IsNullOrEmpty(_dto.VendorShortName) ? "" : _dto.VendorShortName);
            dic.Add("Address", string.IsNullOrEmpty(_dto.VendorAddress) ? "" : _dto.VendorAddress);
            dic.Add("Linker", _dto.VendorLinker);
            dic.Add("Job", string.IsNullOrEmpty(_dto.VendorJob) ? "" : _dto.VendorJob);
            dic.Add("Mobile", _dto.VendorMobile);
            dic.Add("CreateUserId", _dto.SysUserId);
            dic.Add("CreateTime", DateTime.Now);
            dic.Add("DeleteUserId", 0);
            dic.Add("DeleteTime", "Null");
            dic.Add("Remark", _dto.Remark);
            dic.Add("IsDel", 0);

            return await InsertDataByDictionary(dic, "Res_ItemVendor");

        }


        /// <summary>
        /// 修改供应商信息
        /// </summary>
        /// <param name="_dto"></param>
        /// <returns></returns>
        public async Task<bool> updVendorInfo(Res_ItemVendor _entity)
        {
            var result = await _sqlSugar.Updateable<Res_ItemVendor>()
                .Where(s => s.Id == _entity.Id)
                .UpdateColumns(s => new { s.FullName, s.ShortName, s.Address, s.Linker, s.Mobile, s.Job, s.Remark })
                .ExecuteCommandAsync();

            return result > 0;
        }

        /// <summary>
        /// 删除供应商信息
        /// </summary>
        /// <param name="_entity"></param>
        /// <returns></returns>
        public async Task<bool> delVendorInfo(Res_ItemVendor _entity)
        {
            _entity.IsDel = 1;
            _entity.DeleteUserId = _entity.CreateUserId;
            _entity.DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            var result = await _sqlSugar.Updateable<Res_ItemVendor>()
               .Where(s => s.Id == _entity.Id)
               .UpdateColumns(s => new { s.IsDel, s.DeleteTime, s.DeleteUserId })
               .ExecuteCommandAsync();

            return result > 0;
        }

        #endregion

        #region 物料

        /// <summary>
        /// 物料信息列表查询
        /// </summary>
        /// <param name="_dto"></param>
        /// <returns></returns>
        public Res_ItemInfoListView GetItemList(Search_ItemInfoDto _dto)
        {
            string sqlWhere = string.Format(" Where d.IsDel=0 ");

            #region SqlWhere

            if (!string.IsNullOrEmpty(_dto.ItemName))//物料名称
            {
                sqlWhere += string.Format(" And d.ItemName Like '%{0}%' ", _dto.ItemName);
            }
            if (_dto.VendorId > 0)//供应商Id
            {
                sqlWhere += string.Format(" And d.VendorId = {0} ", _dto.VendorId);
            }
            if (_dto.SetDataId > 0)//类型Id
            {
                sqlWhere += string.Format(" And d.SetDataId = {0} ", _dto.SetDataId);
            }
            if (_dto.MinRate > 0)//价格区间(最低)
            {
                sqlWhere += string.Format(" And d.MinRate >= {0} ", _dto.MinRate);
            }
            if (_dto.MaxRate > 0)//价格区间(最高)
            {
                sqlWhere += string.Format(" And d.MaxRate <= {0} ", _dto.MaxRate);
            }

            //if (!string.IsNullOrEmpty(sqlWhere.Trim()))
            //{
            //    Regex r = new Regex("And");
            //    sqlWhere = r.Replace(sqlWhere, "Where", 1);
            //}
            #endregion

            int currPIndex = (((_dto.PageIndex > 0) ? (_dto.PageIndex - 1) : 0) * _dto.PageSize) + 1;
            int currPSize = (((_dto.PageIndex > 0) ? (_dto.PageIndex - 1) : 0) + 1) * _dto.PageSize;

            string sql = string.Format(@" Select ROW_NUMBER() Over(order By Id desc) as RowNumber,* From ( 
 Select d.Id as ItemId,d.VendorId,v.FullName as VendorFullName,v.ShortName as VendorShortName,sd.Id as SetDataId,sd.Name as ItemTypeName,d.MinRate,d.MaxRate,d.CurrRate,d.Remark
 From Res_ItemDetail as d With(Nolock) 
 Inner Join Res_ItemVendor as v With(Nolock) On d.VendorId=v.Id
 Inner Join Sys_SetData as sd With(Nolock) On d.SetDataId=sd.Id
{2}
) ) as tb Where tb.RowNumber Between {0} And {1} ", currPIndex, currPSize, sqlWhere);
            Res_ItemInfoListView rst = new Res_ItemInfoListView();
            rst.CurrPageIndex = currPIndex;
            rst.CurrPageSize = currPSize;
            rst.DataList = _sqlSugar.SqlQueryable<Res_ItemInfoView>(sql).ToList();
            if (rst.DataList.Count > 0)
            {
                string sqlCount = string.Format(@" Select Id From Res_ItemDetail as d 5 With(Nolock) {0} ", sqlWhere);
                int dataCount = _sqlSugar.SqlQueryable<Res_ItemInfoView>(sqlCount).Count();
                rst.DataCount = dataCount;
            }

            return rst;
        }

        /// <summary>
        /// 物料详细信息查询
        /// </summary>
        /// <param name="_dto"></param>
        /// <returns></returns>
        public Res_ItemInfoView getItemInfo(Search_ItemInfoDto _dto)
        {
            string sqlWhere = string.Format(" Where IsDel=0 ");

            if (_dto.ItemId > 0)
            {
                string sql = string.Format(@" Select d.Id as ItemId,d.VendorId,v.FullName as VendorFullName,v.ShortName as VendorShortName,sd.Id as SetDataId,sd.Name as ItemTypeName,
 d.MinRate,d.MaxRate,d.CurrRate,d.Remark
 From Res_ItemDetail as d With(Nolock) Where d.Id={0} ", _dto.ItemId);

                Res_ItemInfoView _view = _sqlSugar.SqlQueryable<Res_ItemInfoView>(sql).First();
                return _view;
            }
            else
            {
                return null;
            }

            return null;
        }

        public async Task<bool> updItemInfo(Res_ItemDetailInfo _entity)
        {
            var source = await _sqlSugar.Queryable<Res_ItemDetailInfo>().Where(s => s.Id == _entity.Id).FirstAsync();
            if (source != null)
            {
                DateTime dtNow = DateTime.Now;
                if (_entity.CurrRate < source.MinRate)
                {
                    _entity.MinRate = _entity.CurrRate;
                    _entity.MinDt = dtNow;
                }
                else
                {
                    _entity.MinRate = source.MinRate;
                    _entity.MinDt = source.MinDt;
                }

                if (_entity.CurrRate > source.MaxRate)
                {
                    _entity.MaxRate = _entity.CurrRate;
                    _entity.MaxDt = dtNow;
                }
                else
                {
                    _entity.MaxRate = source.MaxRate;
                    _entity.MaxDt = source.MaxDt;
                }
                _entity.CurrDt = dtNow;


                Log.Warning("物料信息修改，原数据:【" + JsonSerializer.Serialize(source) + "】,修改参数:【" + JsonSerializer.Serialize(_entity) + "】");


                var result = await _sqlSugar.Updateable<Res_ItemDetailInfo>()
                   .Where(s => s.Id == _entity.Id)
                   .UpdateColumns(s => new { s.CurrDt, s.CurrRate, s.ItemName, s.MaxDt, s.MaxRate, s.MinDt, s.MinRate, s.Remark, s.SetDataId, s.VendorId })
                   .ExecuteCommandAsync();
                return result > 0;
            }
            return false;
        }

        /// <summary>
        /// 删除供应商信息
        /// </summary>
        /// <param name="_entity"></param>
        /// <returns></returns>
        public async Task<bool> delItemInfo(Res_ItemDetailInfo _entity)
        {
            _entity.IsDel = 1;
            _entity.DeleteUserId = _entity.CreateUserId;
            _entity.DeleteTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            var result = await _sqlSugar.Updateable<Res_ItemDetailInfo>()
               .Where(s => s.Id == _entity.Id)
               .UpdateColumns(s => new { s.IsDel, s.DeleteTime, s.DeleteUserId })
               .ExecuteCommandAsync();

            return result > 0;
        }

        #endregion


        /// <summary>
        /// 获取 物料类型列表
        /// </summary>
        /// <returns></returns>
        public List<SetDataView> GetItemTypeListBySetData()
        {
            string sql = string.Format(@" Select d.Id,d.Name,d.STid From Sys_SetData as d With(Nolock) 
Inner Join Sys_SetDataType as t With(Nolock) On d.STid=t.Id 
Where t.IsDel=0 And d.IsDel=0 And t.Id=58 ");
            List<SetDataView> list = _sqlSugar.SqlQueryable<SetDataView>(sql).ToList();

            return list;
        }
    }
}