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 { public ResItemInfoRepository(SqlSugarClient sqlSugar) : base(sqlSugar) { } #region 供应商 /// /// 供应商列表查询 /// /// /// 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(sql).ToList(); if (rst.DataList.Count > 0) { string sqlCount = string.Format(@" Select Id From Res_ItemVendor With(Nolock) {0} ", sqlWhere); int dataCount = _sqlSugar.SqlQueryable(sqlCount).Count(); rst.DataCount = dataCount; } return rst; } /// /// 供应商信息查询 /// /// /// public Res_ItemVendorView getVendorInfo(Search_ResItemVendorDto _dto) { string sqlWhere = string.Format(" Where IsDel=0 "); if (_dto.VendorId > 0) { Res_ItemVendorView _view = _sqlSugar.Queryable().Where(s => s.Id == _dto.VendorId).First(); return _view; } else { return null; } return null; } /// /// 创建供应商信息 /// /// /// public async Task addVendorInfo(Edit_ResItemVendorDto _dto) { Dictionary dic = new Dictionary(); 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"); } /// /// 修改供应商信息 /// /// /// public async Task updVendorInfo(Res_ItemVendor _entity) { var result = await _sqlSugar.Updateable() .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; } /// /// 删除供应商信息 /// /// /// public async Task 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() .Where(s => s.Id == _entity.Id) .UpdateColumns(s => new { s.IsDel, s.DeleteTime, s.DeleteUserId }) .ExecuteCommandAsync(); return result > 0; } #endregion #region 物料 /// /// 物料信息列表查询 /// /// /// 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(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(sqlCount).Count(); rst.DataCount = dataCount; } return rst; } /// /// 物料详细信息查询 /// /// /// 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(sql).First(); return _view; } else { return null; } return null; } public async Task updItemInfo(Res_ItemDetailInfo _entity) { var source = await _sqlSugar.Queryable().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() .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; } /// /// 删除供应商信息 /// /// /// public async Task 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() .Where(s => s.Id == _entity.Id) .UpdateColumns(s => new { s.IsDel, s.DeleteTime, s.DeleteUserId }) .ExecuteCommandAsync(); return result > 0; } #endregion /// /// 获取 物料类型列表 /// /// public List 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 list = _sqlSugar.SqlQueryable(sql).ToList(); return list; } } }