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(_dto.BusRange))//供应商经营范围
            {
                //List<int> list = _dto.BusRange.Split(',').ToList().ConvertAll(t => Convert.ToInt32(t));
                //单数值匹配
                sqlWhere += string.Format(" And BusRange Like '%,{0},%' ", _dto.BusRange);
            }

            //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 * From(Select ROW_NUMBER() Over(order By Id desc) as RowNumber, 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;
            List<Res_ItemVendorView> dataSource = _sqlSugar.SqlQueryable<Res_ItemVendorView>(sql).ToList();

            foreach (var item in dataSource)
            {
                string rangeStr = "";
                List<int> rangeList = item.BusRange.TrimStart(',').Split(',').ToList().ConvertAll(t => Convert.ToInt32(t));
                foreach (var item2 in rangeList)
                {
                    rangeStr += GetText_BusRanger(item2) + ",";
                }
                item.BusRange_Text = rangeStr;
            }
            rst.DataList = new List<Res_ItemVendorView>(dataSource);

            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;
        }

        private string GetText_BusRanger(int sdid)
        {
            List<Sys_SetData> dataList = _sqlSugar.Queryable<Sys_SetData>().Where(s => s.STid == 21).ToList();
            string result = "未知类型" + sdid;
            if (dataList.FirstOrDefault(s => s.Id == sdid) != null)
            {
                result = dataList.FirstOrDefault(s => s.Id == sdid).Name;
            }
            return result;
        }

        /// <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();
                string rangeStr = "";
                List<int> rangeList = _view.BusRange.TrimStart(',').Split(',').ToList().ConvertAll(t => Convert.ToInt32(t));
                foreach (var item2 in rangeList)
                {
                    rangeStr += GetText_BusRanger(item2) + ",";
                }
                _view.BusRange_Text = rangeStr;
                return _view;
            }
            else
            {
                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);
            dic.Add("BusRange", string.IsNullOrEmpty(_dto.BusRange) ? "," : _dto.BusRange);

            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>()
                .SetColumns(it => it.FullName == _entity.FullName)
                .SetColumns(it => it.ShortName == _entity.ShortName)
                .SetColumns(it => it.Address == _entity.Address)
                .SetColumns(it => it.Linker == _entity.Linker)
                .SetColumns(it => it.Mobile == _entity.Mobile)
                .SetColumns(it => it.BusRange == _entity.BusRange)
                .SetColumns(it => it.Job == _entity.Job)
                .SetColumns(it => it.Remark == _entity.Remark)
                .Where(s => s.Id == _entity.Id)
                .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>()
                .SetColumns(it => it.IsDel == _entity.IsDel)
                .SetColumns(it => it.DeleteTime == _entity.DeleteTime)
                .SetColumns(it => it.DeleteUserId == _entity.DeleteUserId)
               .Where(s => s.Id == _entity.Id)
               .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.ItemTypeId > 0)//类型Id
            {
                sqlWhere += string.Format(" And d.ItemTypeId = {0} ", _dto.ItemTypeId);
            }
            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 * FROM(
 Select ROW_NUMBER() Over(order By d.Id desc) as RowNumber, d.ItemName,d.Id as ItemId,d.VendorId,v.FullName as VendorFullName,
 v.ShortName as VendorShortName,t.Id as ItemTypeId,t.TypeName 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 Res_ItemType as t With(Nolock) On d.ItemTypeId=t.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  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.ItemName,d.Id as ItemId,d.VendorId,v.FullName as VendorFullName,v.ShortName as VendorShortName,t.Id as ItemTypeId,t.TypeName as ItemTypeName,
 d.MinRate,d.MaxRate,d.CurrRate,d.Remark
 From Res_ItemDetail as d With(Nolock)
 Inner Join Res_ItemType as t With(Nolock) On d.ItemTypeId=t.Id
Inner Join Res_ItemVendor as v With(Nolock) On d.VendorId=v.Id
Where d.Id={0} ", _dto.ItemId);

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

            //return null;
        }

        /// <summary>
        /// 修改物料信息
        /// </summary>
        /// <param name="_entity"></param>
        /// <returns></returns>
        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>()
                .SetColumns(it => it.CurrDt == _entity.CurrDt)
                .SetColumns(it => it.CurrRate == _entity.CurrRate)
                .SetColumns(it => it.ItemName == _entity.ItemName)
                .SetColumns(it => it.MaxDt == _entity.MaxDt)
                .SetColumns(it => it.MaxRate == _entity.MaxRate)
                .SetColumns(it => it.MinDt == _entity.MinDt)
                .SetColumns(it => it.MinRate == _entity.MinRate)
                .SetColumns(it => it.Remark == _entity.Remark)
                .SetColumns(it => it.ItemTypeId == _entity.ItemTypeId)
                .SetColumns(it => it.VendorId == _entity.VendorId)
                   .Where(s => s.Id == _entity.Id)
                   .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>()
                .SetColumns(it => it.IsDel == _entity.IsDel)
                .SetColumns(it => it.DeleteTime == _entity.DeleteTime)
                .SetColumns(it => it.DeleteUserId == _entity.DeleteUserId)
               .Where(s => s.Id == _entity.Id)
               .ExecuteCommandAsync();

            return result > 0;
        }

        #endregion

    }
}