public static void Init()
{
SqlSugarClient Db = new SqlSugarClient(new ConnectionConfig()
{
ConnectionString = Config.ConnectionString,
DbType = DbType.SqlServer,
IsAutoCloseConnection = true,
//MoreSettings = new ConnMoreSettings()
//{
// PgSqlIsAutoToLower = true //我们这里需要设置为false
//},
InitKeyType = InitKeyType.Attribute,
});
//调式代码 用来打印SQL
Db.Aop.OnLogExecuting = (sql, pars) =>
{
Console.WriteLine(sql);
};
var list2 = Db.Queryable <Order, OrderItem, Custom>((o, i, c) => new JoinQueryInfos(
JoinType.Left, o.Id == i.OrderId,
JoinType.Left, c.Id == o.CustomId
))
.Select((o, i, c) => new Order
{
Id = SqlFunc.IsNull(
SqlFunc.Subqueryable <Order>().Where(f => f.CreateTime > Convert.ToDateTime(o.CreateTime.AddDays(-1))).Select(f => f.Id)
, -1)
}).ToList();
}
/// <summary>
/// 出入库数量
/// </summary>
/// <param name="tWMStaQuery"></param>
/// <returns></returns>
public decimal CalcuInAndOut(TWMStaQuery tWMStaQuery)
{
decimal tradeNum = 0; //入库和出库的差
tradeNum = _db.Instance.Queryable <TWMSalesCountDbModel>().Where(p => p.MaterialId == tWMStaQuery.MaterialId && p.WarehouseId == tWMStaQuery.WarehouseId).
Sum(p => SqlFunc.IsNull(p.WhNumber, 0) - SqlFunc.IsNull(p.WhSendNumber, 0));
return(tradeNum);
}
/// <summary>
/// 新增T_BM_SupplierFile数据
/// </summary>
/// <param name="requestObject">Post请求参数</param>
/// <returns>返回响应结果对象，包括响应代码，新增操作结果</returns>
public async Task <ResponseObject <bool> > PostAsync(RequestPost <TBMSupplierFileAddModel> requestObject, CurrentUser currentUser)
{
var currDb = _db.Instance;//事务需要使用同一个 SqlSugarClient对象实例
try
{
//没有新增数据，返回错误信息
if (requestObject.PostData == null)
{
return(ResponseUtil <bool> .FailResult(false, "PostData不能为null"));
}
//开启事务
currDb.BeginTran();
//插入主表数据
var mapMainModel = _mapper.Map <TBMSupplierFileDbModel>(requestObject.PostData);
mapMainModel.CompanyId = currentUser.CompanyID;
if (_db.Instance.Queryable <TBMSupplierFileDbModel>().Any(p => p.SupplierCode == requestObject.PostData.SupplierCode && p.CompanyId == currentUser.CompanyID &&
SqlFunc.IsNull(p.DeleteFlag, false) == false)
)
{
throw new Exception("编码重复");
}
var mainId = await currDb.Insertable(mapMainModel).ExecuteReturnIdentityAsync();
if (requestObject.PostData.ChildList != null && requestObject.PostData.ChildList.Count() > 0)
{
//更新明细表外键ID值
requestObject.PostData.ChildList.ForEach(p => p.SupplierId = mainId);
//插入从表数据
var mapDetailModelList = _mapper.Map <List <TBMSupplierContactAddModel>, List <TBMSupplierContactDbModel> >(requestObject.PostData.ChildList);
var result = await currDb.Insertable(mapDetailModelList).ExecuteCommandAsync() > 0;
}
//提交事务
currDb.CommitTran();
//返回执行结果
return(ResponseUtil <bool> .SuccessResult(true));
}
catch (Exception ex)
{
//回滚事务
currDb.RollbackTran();
//返回异常结果
return(ResponseUtil <bool> .FailResult(false, ex.Message));
}
}
public static void Funs()
{
var db = GetInstance();
var t1 = db.Queryable <Student>().Where(it => SqlFunc.ToLower(it.Name) == SqlFunc.ToLower("JACK")).ToList();
var t2 = db.Queryable <Student>().Where(it => SqlFunc.IsNull(it.Name, "nullvalue") == "nullvalue").ToList();
var t3 = db.Queryable <Student>().Where(it => SqlFunc.MergeString("a", it.Name) == "nullvalue").ToList();
//SELECT [Id],[SchoolId],[Name],[CreateTime] FROM [Student] WHERE ((LOWER([Name])) = (LOWER(@MethodConst0)) )
/***More Functions***/
//SqlFunc.IsNullOrEmpty(object thisValue)
//SqlFunc.ToLower(object thisValue)
//SqlFunc.string ToUpper(object thisValue)
//SqlFunc.string Trim(object thisValue)
//SqlFunc.bool Contains(string thisValue, string parameterValue)
//SqlFunc.ContainsArray(object[] thisValue, string parameterValue)
//SqlFunc.StartsWith(object thisValue, string parameterValue)
//SqlFunc.EndsWith(object thisValue, string parameterValue)
//SqlFunc.Equals(object thisValue, object parameterValue)
//SqlFunc.DateIsSame(DateTime date1, DateTime date2)
//SqlFunc.DateIsSame(DateTime date1, DateTime date2, DateType dataType)
//SqlFunc.DateAdd(DateTime date, int addValue, DateType millisecond)
//SqlFunc.DateAdd(DateTime date, int addValue)
//SqlFunc.DateValue(DateTime date, DateType dataType)
//SqlFunc.Between(object value, object start, object end)
//SqlFunc.ToInt32(object value)
//SqlFunc.ToInt64(object value)
//SqlFunc.ToDate(object value)
//SqlFunc.ToString(object value)
//SqlFunc.ToDecimal(object value)
//SqlFunc.ToGuid(object value)
//SqlFunc.ToDouble(object value)
//SqlFunc.ToBool(object value)
//SqlFunc.Substring(object value, int index, int length)
//SqlFunc.Replace(object value, string oldChar, string newChar)
//SqlFunc.Length(object value) { throw new NotImplementedException(); }
//SqlFunc.AggregateSum(object thisValue)
//SqlFunc.AggregateAvg<TResult>(TResult thisValue)
//SqlFunc.AggregateMin(object thisValue)
//SqlFunc.AggregateMax(object thisValue)
//SqlFunc.AggregateCount(object thisValue)
}
/// <summary>
/// 获取公司下所有的数据字典
/// </summary>
/// <param name="CompanyID"></param>
/// <returns></returns>
public async Task <List <TBMDictionaryCacheModel> > GetAllDictionary(int CompanyID)
{
try
{
var query = _db.Instance.Queryable <TBMDictionaryDbModel, TBMDictionaryTypeDbModel, TSMUserAccountDbModel, TSMUserAccountDbModel>(
(t, t0, t1, t2) => new object[]
{
JoinType.Left, t.TypeId == t0.ID,
JoinType.Left, t.CreateId == t1.ID,
JoinType.Left, t.UpdateId == t2.ID
}).Where((t, t0, t1, t2) => SqlFunc.IsNull(t.DeleteFlag, false) == false && t.CompanyId == CompanyID);
var queryData = await query.Select(
(t, t0, t1, t2) => new TBMDictionaryCacheModel
{
ID = t.ID,
TypeId = t.TypeId,
TypeName = t0.TypeName,
DicCode = t.DicCode,
DicValue = t.DicValue,
Remark = t.Remark,
CreateTime = t.CreateTime,
CreateId = t.CreateId,
CreateName = t1.AccountName,
UpdateName = t.UpdateId == null ? "" : t2.AccountName,
UpdateTime = t.UpdateTime,
UpdateId = t.UpdateId,
CompanyId = t.CompanyId,
DeleteFlag = t.DeleteFlag,
})
.ToListAsync();
return(queryData);
}
catch (Exception ex)
{
return(new List <TBMDictionaryCacheModel>());
}
}
public static void Init()
{
var db = NewUnitTest.Db;
db.CodeFirst.InitTables <Export>();
db.CodeFirst.InitTables <LoadCon>();
db.CodeFirst.InitTables <ExToCon>();
var LclId = "FCL";
var conno = "conno";
var withSameCarriCarNo = db.Queryable <Export>()
.Where(export => SqlFunc.IsNull(export.IeId, "E") == "E")
.Where(export => SqlFunc.Subqueryable <LoadCon>().Where(loadconn =>
SqlFunc.Subqueryable <ExToCon>().Where(extocon =>
extocon.ExId == export.Id && extocon.LcId == loadconn.Id).Any())
.WhereIF(LclId == "FCL",
loadconn => SqlFunc.IsNull(loadconn.ConNo, "") == conno)
.WhereIF(LclId != "FCL",
loadconn => SqlFunc.IsNull(loadconn.ConNo, "") == conno && export.LclId == "FCL")
.Any())
.ToList();
}
} //用来处理CellGroup表的常用操作
public AAResultInfo SearchLanewayByCellGroupOID(int stationAreaType, long cellGroupOID, int[] availableLaneWays)
{
AAResultInfo retInfo = new AAResultInfo();
retInfo.RetCode = "-1";
//long ProductGroupOID = CellGroupDb.AsQueryable().Where(it=>it.OID==cellGroupOID).First().ProductGroupOID;
long ProductGroupOID = CellGroupDb.AsQueryable().InSingle(cellGroupOID).ProductGroupOID;
string strFilterRule = GetSqlFilterStringByFilterRule(new int[2] {
1, 2
}, cellGroupOID);
var ExcludeLocations = Db.Queryable <RackLocationInfo, RackCellInfo, RackContainerInfo>((location, cell, container) => new object[] {
JoinType.Inner, location.RackCellOID == cell.OID,
JoinType.Left, location.OID == container.LocationOID
}).Where((location, cell, container) => location.Location == 1 && SqlFunc.IsNullOrEmpty(container.LocationOID))
.GroupBy((location, cell, container) => cell.OID)
.Select((location, cell, container) => new ExcludeLocationsInfo {
CellOID = cell.OID, IsExistsProduct = "1"
});
var availableRackAreaTemp = Db.Queryable <RackLocationInfo, RackCellInfo, RackBunchInfo, RackSideInfo, RackAreaInfo,
RackContainerInfo, CellGroup, Enable_RackAreaInfo, Enable_RackArea_InfeedInfo, Match_RackCell_SearchInfeedInfo>(
(st, st1, st2, st3, st4, st5, st6, st7, st8, st9) => new object[] {
JoinType.Inner, st.RackCellOID == st1.OID,
JoinType.Inner, st1.RackBunchOID == st2.OID,
JoinType.Inner, st2.RackSideOID == st3.OID,
JoinType.Inner, st3.RackAreaOID == st4.OID,
JoinType.Left, st.OID == st5.LocationOID,
JoinType.Left, st5.CellGroupOID == st6.OID,
JoinType.Left, st4.OID == st7.RackAreaOID,
JoinType.Left, st4.OID == st8.RackAreaOID,
JoinType.Left, st.OID == st9.LocationOID
}).Where((st, st1, st2, st3, st4, st5, st6, st7, st8, st9) => SqlFunc.IsNull(st5.LocationOID, 0) == 0 && SqlFunc.IsNull <bool>(st7.IsLocked, false) == false &&
SqlFunc.IsNull <bool>(st8.IsLocked, false) == false && SqlFunc.IsNull(st9.LocationOID, 0) == 0 && availableLaneWays.Contains(st4.LaneWay))
.Select((st, st1, st2, st3, st4, st5, st6, st7, st8, st9) => new AvailableRackAreaTempInfo {
RackCellOID = st.RackCellOID, RackAreaOID = st4.OID
});
var availableRackArea = Db.Queryable(availableRackAreaTemp, ExcludeLocations, JoinType.Left, (availableTemp, excude) => availableTemp.RackCellOID == excude.CellOID)
.GroupBy((availableTemp, excude) => availableTemp.RackAreaOID).Select(
(availableTemp, excude) => new AvailableRackAreaInfo {
RackAreaOID = availableTemp.RackAreaOID, AvailLocationCount = SqlFunc.AggregateCount(availableTemp.RackAreaOID)
});
var ProductGroupCounter = Db.Queryable <Match_RackArea_ProductGroupCounterInfo>().Where(it => it.ProductGroupOID == ProductGroupOID).Select(it => new MatchProductGroupCounterInfo
{
ProductGroupOID = it.ProductGroupOID, RackAreaOID = it.RackAreaOID, AllocateCount = it.AllocateCount, RemainCount = it.RemainCount, ProductAllocTime = it.RackAreaAllocTime,
ProductAllocSpanTime = Convert.ToString(it.RackAreaAllocTime)
});
var matchRackAreaTemp = Db.Queryable(availableRackArea, ProductGroupCounter, JoinType.Left, (available, ProductGroup) => available.RackAreaOID == ProductGroup.RackAreaOID).Select(
(available, ProductGroup) => new MatchRackAreaInfeedInfo {
RackAreaOID = available.RackAreaOID, AvailLocationCount = available.AvailLocationCount, TotalAllocateCount = ProductGroup.AllocateCount,
ProductAllocTime = ProductGroup.ProductAllocTime, ProductAllocSpanTime = ProductGroup.ProductAllocSpanTime
});
var matchRackAreaInfeedTemp = Db.Queryable <RackAreaInfo, RackStationInfo, RackStation_AreaInfo, Enable_RackAreaInfo, Enable_RackArea_InfeedInfo, Match_RackArea_CounterInfo, Match_RackArea_WeightInfo,
Match_MCTTaskCounterInfo>((info, info1, info2, info3, info4, counter, weighter, info5) => new object[] {
JoinType.Left, info.OID == info1.RackAreaOID,
JoinType.Left, info1.StationAreaOID == info2.OID,
JoinType.Left, info3.RackAreaOID == info.OID,
JoinType.Left, info4.RackAreaOID == info.OID,
JoinType.Left, counter.RackAreaOID == info.OID,
JoinType.Left, weighter.RackAreaOID == info.OID,
JoinType.Left, info5.MCTID == info.LaneWay
}).Select((info, info1, info2, info3, info4, counter, weighter, info5) => new MatchRackAreaInfeedTempInfo {
RackAreaOID = info.OID, AreaWeightValue = weighter.AreaWeightValue, CurrentTaskAmount = info5.CurrentTaskAmount,
KPLValue = weighter.KPLValue, RackAreaAllocTime = counter.AlternateTime, AreaPriority = weighter.AreaPriority, Station = info1.Station, PLCDestValue = info1.PLCValue, AreaIsLock = info3.IsLocked, AreaInfeedIsLock = info4.IsLocked,
StationArea = info2.StationArea
});
var matchRackAreaInfeed = Db.Queryable(matchRackAreaTemp, matchRackAreaInfeedTemp, JoinType.Left, (j1, j2) => j1.RackAreaOID == j2.RackAreaOID).
Where((j1, j2) => j1.AvailLocationCount > 0 && SqlFunc.IsNull <bool>(j2.AreaIsLock, false) == false && SqlFunc.IsNull <bool>(j2.AreaInfeedIsLock, false) == false && j2.StationArea == stationAreaType)
.Select((j1, j2) => new MatchRackAreaInfeedInfo {
RackAreaOID = j1.RackAreaOID, Station = j2.Station, AreaIsLock = j2.AreaIsLock, AreaInfeedIsLock = j2.AreaInfeedIsLock, StationArea = j2.StationArea
}).ToList();
//string strCMDSQL = SearchCellSQL.GenerateMatchAreaCommandSQL(ProductGroupOID, strFilterRule, strOrder, availableLaneWays, stationAreaType);
//if (findRackAreaRow == null)
//{
// retInfo.RetCode = "03";
// retInfo.RetMessage = string.Format("No available roadway, please check,CellGroupOID:{0}", cellGroupOID);
//}
//else
//{
// retInfo.RetCode = "00";
// retInfo.RackAreaOID = DataTypeConverter.GetIntValue(findRackAreaRow["RackAreaOID"]);
// // retInfo.RetDest = ServiceContext.Get_SP_PLCDest(retInfo.RackAreaOID);
// retInfo.RetDest = DataTypeConverter.GetIntValue(findRackAreaRow["PLCDestValue"]);
// retInfo.Station = DataTypeConverter.GetIntValue(findRackAreaRow["Station"]);
// retInfo.CellGroupOID = cellGroupOID;
// retInfo.RetMessage = string.Format("Allocate a new roadway,CellGroupOID({0}),Roadway ID({1}),PlcDest({2}),Station({3})", cellGroupOID, retInfo.RackAreaOID, retInfo.RetDest, retInfo.Station);
// //保存巷道分配结果
// DBAccess.Instance.Match_RackArea_SearchInfeed_InsertOrUpdate(retInfo.RackAreaOID, cellGroupOID);
// //保存巷道分配记录
// DBAccess.Instance.Match_RackArea_ProductGroupCounter_InsertOrUpdate(retInfo.RackAreaOID, ProductGroupOID);
// //更新巷道分配时间
// DBAccess.Instance.Update_Match_RackArea_Counter(retInfo.RackAreaOID);
// //增加某种分类的数量
// DBAccess.Instance.UpdateMatchRackAreaProductGroupCountByRackAreaAndProductGroupOID(retInfo.RackAreaOID, ProductGroupOID);
//}
return(retInfo);
}
/// <summary>
/// 获取T_SSM_SalesOrderMain主表数据数据
/// </summary>
/// <param name="requestObject">Get请求参数</param>
/// <param name="currentUser"></param>
/// <returns>返回响应结果对象，包括响应代码，查询操作结果</returns>
public async Task <ResponseObject <List <TSSMSalesOrderMainQueryModel> > > GetMainListAsync(RequestGet requestObject, CurrentUser currentUser)
{
try
{
List <TSSMSalesOrderMainQueryModel> queryData = null; //查询结果集对象
RefAsync <int> totalNumber = -1; //总记录数
var query = _db.Instance.Queryable <TSSMSalesOrderMainDbModel, TBMCustomerFileDbModel, TSMUserAccountDbModel,
TBMDictionaryDbModel, TBMDictionaryDbModel, TSMUserAccountDbModel, TSMUserAccountDbModel>
(
(t, t0, t1, t2, t3, t4, t5) => new object[]
{
JoinType.Left, t.CustomerId == t0.ID,
JoinType.Left, t.SalesmanId == t1.ID,
JoinType.Left, t.OrderTypeId == t2.ID,
JoinType.Left, t.SettlementTypeId == t3.ID,
JoinType.Left, t.AuditId == t4.ID,
JoinType.Left, t.OperatorId == t5.ID
}
).Where((t, t0, t1, t2, t3, t4, t5) => t.DeleteFlag == false && SqlFunc.IsNull(t.IsMaterial, false) == false);
//查询条件
if (requestObject.QueryConditions != null && requestObject.QueryConditions.Count > 0)
{
//只能查询主表数据，且主表别名必须是t
var conditionals = SqlSugarUtil.GetConditionalModels(requestObject.QueryConditions);
foreach (ConditionalModel item in conditionals)
{
if (item.FieldName.ToLower() == "customername")
{
item.FieldName = $"t0.{item.FieldName}";
continue;
}
item.FieldName = $"t.{item.FieldName}";
}
query.Where(conditionals);
}
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
foreach (var item in requestObject.OrderByConditions)
{
var exp = SqlSugarUtil.GetOrderByLambda <TSSMSalesOrderMainDbModel>(item.Column);
if (exp == null)
{
continue;
}
if (item.Condition.ToLower() != "asc" && item.Condition.ToLower() != "desc")
{
continue;
}
query.OrderBy($"{item.Column} {item.Condition}");
}
}
//执行查询
if (requestObject.IsPaging)
{
queryData = await query
.Select((t, t0, t1, t2, t3, t4, t5) => new TSSMSalesOrderMainQueryModel
{
ID = t.ID,
CustomerId = t.CustomerId,
CustomerName = t0.CustomerName,
SalesmanId = t.SalesmanId,
SalesmanName = t1.AccountName,
OrderNo = t.OrderNo,
OrderTypeId = t.OrderTypeId,
OrderTypeName = t2.DicValue,
SettlementTypeId = t.SettlementTypeId,
SettementTypeName = t3.DicValue,
Currency = t.Currency,
ReceiptAddress = t.ReceiptAddress,
OrderDate = t.OrderDate,
AuditStatus = t.AuditStatus,
AuditId = t.AuditId,
AuditName = t4.AccountName,
AuditTime = t.AuditTime,
OperatorId = t.OperatorId,
OperatorName = t5.AccountName,
ContactName = t.ContactName,
ContactNumber = t.ContactNumber,
CompanyId = t.CompanyId,
DeleteFlag = t.DeleteFlag,
TransferStatus = t.TransferStatus,
SalesAmount = t.SalesAmount,
SalesNum = t.SalesNum,
TransProdStatus = t.TransProdStatus
})
.Where(t => t.CompanyId == currentUser.CompanyID && !t.DeleteFlag)
.ToPageListAsync(requestObject.PageIndex, requestObject.PageSize, totalNumber);
}
else
{
queryData = await query
.Select((t, t0, t1, t2, t3, t4, t5) => new TSSMSalesOrderMainQueryModel
{
ID = t.ID,
CustomerId = t.CustomerId,
CustomerName = t0.CustomerName,
SalesmanId = t.SalesmanId,
SalesmanName = t1.AccountName,
OrderNo = t.OrderNo,
OrderTypeId = t.OrderTypeId,
OrderTypeName = t2.DicValue,
SettlementTypeId = t.SettlementTypeId,
SettementTypeName = t3.DicValue,
Currency = t.Currency,
ReceiptAddress = t.ReceiptAddress,
OrderDate = t.OrderDate,
AuditStatus = t.AuditStatus,
AuditId = t.AuditId,
AuditName = t4.AccountName,
AuditTime = t.AuditTime,
OperatorId = t.OperatorId,
OperatorName = t5.AccountName,
ContactName = t.ContactName,
ContactNumber = t.ContactNumber,
CompanyId = t.CompanyId,
DeleteFlag = t.DeleteFlag,
TransferStatus = t.TransferStatus,
SalesAmount = t.SalesAmount,
SalesNum = t.SalesNum,
TransProdStatus = t.TransProdStatus
})
.Where(t => t.CompanyId == currentUser.CompanyID && !t.DeleteFlag)
.ToListAsync();
}
//是否可编辑
queryData.ForEach(p => p.AllowEdit = p.AuditStatus != 2 && p.OperatorId == currentUser.UserID);
//返回执行结果
return(ResponseUtil <List <TSSMSalesOrderMainQueryModel> > .SuccessResult(queryData, totalNumber));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <List <TSSMSalesOrderMainQueryModel> > .FailResult(null, ex.Message));
}
}
private async Task <ResponseObject <List <TSSMSalesOrderDetailQueryModel> > > QueryDetailListAsync(int requestObject)
{
try
{
//查询结果集对象
List <TSSMSalesOrderDetailQueryModel> queryData = null;
//总记录数
RefAsync <int> totalNumber = -1;
var query = _db.Instance.Queryable <TSSMSalesOrderDetailDbModel>();
//执行查询
queryData = await _db.Instance.Queryable <TSSMSalesOrderDetailDbModel, TBMMaterialFileDbModel,
TBMDictionaryDbModel, TBMDictionaryDbModel, TBMDictionaryDbModel, TBMDictionaryDbModel, TBMDictionaryDbModel,
TBMPackageDbModel, TMMColorSolutionMainDbModel, TBMDictionaryDbModel>
(
(t, t0, t1, t2, t3, t4, t5, t6, t7, t8) => new object[]
{
JoinType.Left, t.MaterialId == t0.ID,
JoinType.Left, t0.MaterialTypeId == t1.ID,
JoinType.Left, t0.ColorId == t2.ID,
JoinType.Left, t0.BaseUnitId == t3.ID,
JoinType.Left, t0.SalesUnitId == t4.ID,
JoinType.Left, t0.WarehouseUnitId == t5.ID,
JoinType.Left, t.PackageId == t6.ID,
JoinType.Left, t.ColorSolutionId == t7.ID,
JoinType.Left, t0.ProduceUnitId == t8.ID
}
)
.Where(t => t.MainId == requestObject)
.Select((t, t0, t1, t2, t3, t4, t5, t6, t7, t8) => new TSSMSalesOrderDetailQueryModel
{
ID = t.ID,
MainId = t.MainId,
MaterialId = t.MaterialId,
MaterialCode = t0.MaterialCode,
MaterialName = t0.MaterialName,
MaterialTypeId = t0.MaterialTypeId,
MaterialTypeName = t1.DicValue,
ColorId = t0.ColorId,
ColorName = t2.DicValue,
Spec = t0.Spec,
BaseUnitId = t0.BaseUnitId,
BaseUnitName = t3.DicValue,
SalesUnitId = t0.SalesUnitId,
SalesUnitName = SqlFunc.IsNullOrEmpty(t4.ID) ? t3.DicValue : t4.DicValue,
SalesRate = t0.SalesRate,
GoodsCode = t.GoodsCode,
GoodsName = t.GoodsName,
UnitPrice = t.UnitPrice,
SalesNum = t.SalesNum,
SalesAmount = t.SalesAmount,
DeliveryPeriod = t.DeliveryPeriod,
TransferNum = t.TransferNum,
Remark = t.Remark,
WarehouseRate = t0.WarehouseRate,
WarehouseUnitId = t0.WarehouseUnitId,
WarehouseUnitName = SqlFunc.IsNullOrEmpty(t5.ID) ? t3.DicValue : t5.DicValue,
PackageId = t.PackageId,
PackageCode = t6.DicCode,
PackageName = t6.DicValue,
ColorSolutionId = t.ColorSolutionId,
ColorSolutionName = t7.SolutionCode,
TransProdNum = t.TransProdNum,
ProduceUnitId = t0.ProduceUnitId,
ProduceUnitName = SqlFunc.IsNullOrEmpty(t8.ID) ? t3.DicValue : t8.DicValue,
ProduceRate = t0.ProduceRate
})
.ToListAsync();
//计算已发和待出库数量
var dataList = await _db.Instance.Queryable <TWMSalesMainDbModel, TWMSalesDetailDbModel>(
(t, t0) => new object[]
{
JoinType.Left, t.ID == t0.MainId
})
.Where((t, t0) => t.SourceId == requestObject && SqlFunc.IsNull(t.DeleteFlag, false) == false)
.Select((t, t0) => new { t.ID, t.AuditStatus, t0.MaterialId, t0.ActualNum })
.ToListAsync();
queryData.ForEach(p =>
{
p.AlreadyNum = dataList.Where(p1 => p1.AuditStatus == 2 && p1.MaterialId == p.MaterialId).Sum(p2 => p2.ActualNum);
p.WaitNum = dataList.Where(p1 => (p1.AuditStatus == null || p1.AuditStatus != 2) && p1.MaterialId == p.MaterialId).Sum(p2 => p2.ActualNum);
});
//返回执行结果
return(ResponseUtil <List <TSSMSalesOrderDetailQueryModel> > .SuccessResult(queryData, totalNumber));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <List <TSSMSalesOrderDetailQueryModel> > .FailResult(null, ex.Message));
}
}
/// <summary>
/// 自动计算
/// </summary>
/// <param name="orderID">生产订单ID</param>
/// <param name="Type">1,表示有配色，2表示无配色</param>
/// <param name="currentUser">当前用户</param>
/// <returns>计算是否成功</returns>
public async Task <ResponseObject <MrpResultModel> > AutoComputeMRP(int orderID, int Type, CurrentUser currentUser)
{
try
{
TMMProductionOrderMainDbModel mainEntity = _db.Instance.Queryable <TMMProductionOrderMainDbModel>().Where(p => p.ID == orderID &&
p.CompanyId == currentUser.CompanyID && p.MRPStatus == false).First();
if (mainEntity == null)
{
return(ResponseUtil <MrpResultModel> .FailResult(null, "生产订单不存在，或MRP已经算过了不能重复计算"));
}
List <TBMMaterialFileCacheModel> MMaterialList = BasicCacheGet.GetMaterial(currentUser);
_db.Instance.BeginTran();
var bomResult = await _iTMMProductionOrderMainService.CreateOrderBom(mainEntity.ID, currentUser); //生成BOM清单
if (!bomResult.Result)
{
throw new Exception(bomResult.ErrorInfo);
}
//数据字典
var tBMDictionary = _db.Instance.Queryable <TBMDictionaryDbModel>().Where(p => p.CompanyId == currentUser.CompanyID).ToList().ToDictionary(p => p.ID, p => p.DicValue);
//仓库
var warehouseDic = _db.Instance.Queryable <TBMWarehouseFileDbModel>().Where(t => SqlFunc.IsNull(t.DeleteFlag, false) != true &&
t.CompanyId == currentUser.CompanyID).ToList().ToDictionary(p => p.ID, p => new { Name = p.WarehouseName, Code = p.Code });
List <InventoryResultModel> result = new List <InventoryResultModel>();
//其他出入库
var otherCount = _db.Instance.Queryable <TWMOtherCountDbModel>().
Select(p => new InAndOutModel()
{
MaterialId = p.MaterialId,
WarehouseId = p.WarehouseId,
WhNumber = p.WhNumber,
WhSendNumber = p.WhSendNumber
});
//盘亏盘盈出入库
var pCount = _db.Instance.Queryable <TWMProfitDeficitCountDbModel>().
Select(p => new InAndOutModel()
{
MaterialId = p.MaterialId,
WarehouseId = p.WarehouseId,
WhNumber = p.WhNumber,
WhSendNumber = p.WhSendNumber
});
//销售出入库
var SaleCount = _db.Instance.Queryable <TWMSalesCountDbModel>().
Select(p => new InAndOutModel()
{
MaterialId = p.MaterialId,
WarehouseId = p.WarehouseId,
WhNumber = p.WhNumber,
WhSendNumber = p.WhSendNumber
});
//采购出入库
var PurchaseCount = _db.Instance.Queryable <TWMPurchaseCountDbModel>().
Select(p => new InAndOutModel()
{
MaterialId = p.MaterialId,
WarehouseId = p.WarehouseId,
WhNumber = p.WhNumber,
WhSendNumber = p.WhSendNumber
});
//生产出入库
var ProductCount = _db.Instance.Queryable <TWMProductionCountDbModel>().
Select(p => new InAndOutModel()
{
MaterialId = p.MaterialId,
WarehouseId = p.WarehouseId,
WhNumber = p.WhNumber,
WhSendNumber = p.WhSendNumber
});
//出入库数量
var allCount = _db.Instance.UnionAll(otherCount, pCount, SaleCount, PurchaseCount, ProductCount).GroupBy(p => new { p.MaterialId }).
Select(p => new TradeInventoryModel()
{
TradeNumber = SqlFunc.AggregateSum(p.WhNumber) - SqlFunc.AggregateSum(p.WhSendNumber),
MaterialId = p.MaterialId,
}).AS("t100");
var materialFileQuery = _db.Instance.Queryable <TMMProductionOrderBOMSumDbModel>().Where(p => p.ProOrderId == orderID);
var ts = _db.Instance.Queryable(materialFileQuery, allCount, JoinType.Inner, (p1, p2) => p1.MaterialId == p2.MaterialId).Select((p1, p2) => new InventoryOut
{
MaterialId = p2.MaterialId,
Amount = p2.TradeNumber
});
//销售单 所有物料的出入库数量
var tsout1 = ts.ToList();
var tsOut = tsout1.GroupBy(p => p.MaterialId).Select(p => new InventoryOut()
{
MaterialId = p.Key, Amount = p.Sum(m => m.Amount)
}).ToList();
#region 待出库数量
//其他待出库
var otherToOut = _db.Instance.Queryable <TWMOtherWhSendMainDbModel, TWMOtherWhSendDetailDbModel>((t1, t2) =>
new object[] { JoinType.Inner, t1.ID == t2.MainId }).Where((t1, t2) =>
t1.AuditStatus != 2 &&
t1.DeleteFlag == false).Select((t1, t2) => new InventoryOut {
MaterialId = t2.MaterialId, Amount = t2.ActualNumber
});
//盘亏出库
var deficitToOut = _db.Instance.Queryable <TWMDeficitMainDbModel, TWMDeficitDetailDbModel>((t1, t2) =>
new object[] { JoinType.Inner, t1.ID == t2.MainId }).Where((t1, t2) =>
t1.AuditStatus != 2 &&
t1.DeleteFlag == false).Select((t1, t2) => new InventoryOut {
MaterialId = t2.MaterialId, Amount = t2.ActualNumber
});
//销售出库
var saleToOut = _db.Instance.Queryable <TWMSalesMainDbModel, TWMSalesDetailDbModel>((t1, t2) =>
new object[] { JoinType.Inner, t1.ID == t2.MainId }).Where((t1, t2) =>
t1.AuditStatus != 2 &&
t1.DeleteFlag == false).Select((t1, t2) => new InventoryOut {
MaterialId = t2.MaterialId, Amount = t2.ActualNum
});
//生产待出库
var productToOut = _db.Instance.Queryable <TWMProductionMainDbModel, TWMProductionDetailDbModel>((t1, t2) =>
new object[] { JoinType.Inner, t1.ID == t2.MainId }).Where((t1, t2) =>
t1.AuditStatus != 2 &&
t1.DeleteFlag == false).Select((t1, t2) => new InventoryOut {
MaterialId = t2.MaterialId, Amount = t2.ActualNum
});
var allToOut = _db.Instance.UnionAll(otherToOut, deficitToOut, saleToOut, productToOut).AS("t101");
var materialFileQuery1 = _db.Instance.Queryable <TMMProductionOrderBOMSumDbModel>().Where(p => p.ProOrderId == orderID);
var tsToOut = _db.Instance.Queryable(materialFileQuery1, allToOut, JoinType.Inner, (p1, p2) => p1.ID == p2.MaterialId).Select((p1, p2) => new InventoryOut
{
MaterialId = p2.MaterialId,
Amount = p2.Amount
});
//销售单所有物料的待出库数量
var tsToOutSum = tsToOut.ToList().GroupBy(p => p.MaterialId).Select(p => new InventoryOut()
{
MaterialId = p.Key, Amount = p.Sum(m => m.Amount)
}).ToList();
#endregion
//期初
List <InventoryOut> prime = _db.Instance.Queryable <TMMProductionOrderBOMSumDbModel, TWMPrimeCountDbModel>((t, t1) => new object[] {
JoinType.Inner, t.MaterialId == t1.MaterialId
}).Select((t, t1) => t1).Distinct().ToList().GroupBy(p => p.MaterialId).Select(p => new InventoryOut()
{
MaterialId = p.Key, Amount = p.Sum(m => m.PrimeNum)
}).ToList();;
var Allmaterial = _db.Instance.Queryable <TMMProductionOrderBOMSumDbModel>().Where(p => p.ProOrderId == orderID).ToList();
Dictionary <int, string> colorSolution = _db.Instance.Queryable <TMMColorSolutionMainDbModel, TBMPackageDbModel>((t1, t2) =>
new object[] { JoinType.Inner, t1.PackageId == t2.ID }).
Where((t1, t2) => t2.CompanyId == currentUser.CompanyID).ToList().ToDictionary(p => p.ID, p => p.SolutionCode);
List <int> colorIDS = colorSolution.Keys.ToList();
//生产订单的所有物料
List <TMMProductionOrderBOMSumDbModel> sumList = _db.Instance.Queryable <TMMProductionOrderBOMSumDbModel>().Where(p => p.ProOrderId == orderID).ToList();
foreach (var item in sumList)
{
decimal primeAmount = 0; //期初数量
decimal TradeNumber = 0; //出入库数量
decimal toOutAmount = 0; //待出数量
TBMMaterialFileCacheModel materialFile = MMaterialList.Where(p => p.ID == item.MaterialId).FirstOrDefault();
if (materialFile == null)
{
throw new Exception($"物料ID:{item.MaterialId}，不存在");
}
InventoryOut primeEntity = prime.Where(p => p.MaterialId == item.MaterialId).FirstOrDefault();
if (primeEntity != null)
{
primeAmount = primeEntity.Amount;
}
InventoryOut tradeEntity = tsOut.Where(p => p.MaterialId == item.MaterialId).FirstOrDefault();
if (tradeEntity != null)
{
TradeNumber = tradeEntity.Amount;
}
InventoryOut toOutEntity = tsToOutSum.Where(p => p.MaterialId == item.MaterialId).FirstOrDefault();
if (toOutEntity != null)
{
toOutAmount = toOutEntity.Amount;
}
decimal avaibleAmountUnit = primeAmount + TradeNumber - toOutAmount; //仓库数量
decimal avaibleAmount = UnitChange.TranserUnit(materialFile, UnitType.Warehouse, UnitType.Produce, avaibleAmountUnit); //生产数量
if (avaibleAmount >= item.TotalValue)
{
item.PurchaseNum = 0;
item.PickNum = item.TotalValue;
}
else
{
if (avaibleAmount < 0)
{
avaibleAmount = 0;
}
item.PurchaseNum = item.TotalValue - avaibleAmount;
item.PickNum = avaibleAmount;
}
item.PurchaseTransNum = 0;
item.PickTransNum = 0;
}
if (sumList.Count() > 0)
{
_db.Instance.Updateable(sumList).ExecuteCommand();
}
mainEntity.MRPStatus = true;
mainEntity.MRPTime = DateTime.Now;
_db.Instance.Updateable(mainEntity).UpdateColumns(p => new { p.MRPStatus, p.MRPTime }).ExecuteCommand();
_db.Instance.CommitTran();
MrpResultModel bomList;
if (Type == 1)
{
bomList = GetProcuctBomByOrderID(orderID, currentUser);
}
else
{
bomList = GetProcuctBomByOrderID(orderID, currentUser);
}
return(ResponseUtil <MrpResultModel> .SuccessResult(bomList));
}
catch (Exception ex)
{
_db.Instance.RollbackTran();
return(ResponseUtil <MrpResultModel> .FailResult(null, ex.Message));
}
}
/// <summary>
/// 新增T_BM_MaterialFile数据
/// </summary>
/// <param name="requestObject">返回响应结果对象，包括响应代码，新增操作结果</param>
/// <param name="currentUser"></param>
/// <returns></returns>
public async Task <ResponseObject <TBMMaterialFileAddModel, bool> > PostAsync(RequestObject <TBMMaterialFileAddModel> requestObject, CurrentUser currentUser)
{
try
{
var dic = BasicCacheGet.GetDic(currentUser);
//如果没有新增数据，返回错误信息
if (requestObject.PostData == null && requestObject.PostDataList == null)
{
return(ResponseUtil <TBMMaterialFileAddModel, bool> .FailResult(requestObject, false, "PostData不能都为null"));
}
var result = false;
//批量新增的优先级高于单条数据新增，且只会执行一个新增操作
if (requestObject.PostDataList != null && requestObject.PostDataList.Count > 0)
{
//var addList = _mapper.Map<List<TBMMaterialFileAddModel>, List<TBMMaterialFileDbModel>>(requestObject.PostDataList);
//result = await _db.Instance.Insertable(addList).ExecuteCommandAsync() > 0;
}
else
{
var addModel = _mapper.Map <TBMMaterialFileDbModel>(requestObject.PostData);
var oldModel = _db.Instance.Queryable <TBMMaterialFileDbModel>().Where(p => SqlFunc.IsNull(p.DeleteFlag, false) == false &&
p.CompanyId == currentUser.CompanyID && p.MaterialCode == addModel.MaterialCode).First();
if (oldModel != null)
{
return(ResponseUtil <TBMMaterialFileAddModel, bool> .FailResult(requestObject, false, addModel.MaterialCode + " 已经存在"));
}
if (addModel.ColorId != null)
{
var oldModel1 = _db.Instance.Queryable <TBMMaterialFileDbModel>().Where(p => SqlFunc.IsNull(p.DeleteFlag, false) == false &&
p.CompanyId == currentUser.CompanyID && p.MaterialName == addModel.MaterialName && p.ColorId == addModel.ColorId).First();
if (oldModel1 != null)
{
string colorName = "此颜色";
var colorEntity = dic.Where(p => p.ID == addModel.ColorId).FirstOrDefault();
if (null != colorEntity)
{
colorName = colorEntity.DicValue;
}
return(ResponseUtil <TBMMaterialFileAddModel, bool> .FailResult(requestObject, false, addModel.MaterialName + $" {colorName}已经存在"));
}
}
addModel.CompanyId = currentUser.CompanyID;
result = await _db.Instance.Insertable(addModel).ExecuteCommandAsync() > 0;
}
ClearCache(currentUser);
//返回执行结果
if (result)
{
return(ResponseUtil <TBMMaterialFileAddModel, bool> .SuccessResult(requestObject, true));
}
return(ResponseUtil <TBMMaterialFileAddModel, bool> .FailResult(requestObject, false, "新增数据失败!"));
}
catch (Exception ex)
{
//返回异常结果
return(ResponseUtil <TBMMaterialFileAddModel, bool> .FailResult(requestObject, false, ex.Message));
}
}
/// <summary>
/// 修改T_BM_MaterialFile数据
/// </summary>
/// <param name="requestObject">返回响应结果对象，包括响应代码，修改操作结果</param>
/// <param name="currentUser"></param>
/// <returns></returns>
public async Task <ResponseObject <TBMMaterialFileEditModel, bool> > PutAsync(RequestObject <TBMMaterialFileEditModel> requestObject, CurrentUser currentUser)
{
try
{
//执行结果
var result = false;
//没有修改信息，返回错误信息
if (requestObject.PostDataList == null && requestObject.PostData == null)
{
return(ResponseUtil <TBMMaterialFileEditModel, bool> .FailResult(requestObject, false, "PostData不能都为null"));
}
//批量更新优先级高于单记录更新
if (requestObject.PostDataList != null && requestObject.PostDataList.Count > 0)
{
////批量更新
//var editList = _mapper.Map<List<TBMMaterialFileEditModel>, List<TBMMaterialFileDbModel>>(requestObject.PostDataList);
//result = await _db.Instance.Updateable(editList).ExecuteCommandAsync() > 0;
}
else
{
//单记录更新
var editModel = _mapper.Map <TBMMaterialFileDbModel>(requestObject.PostData);
var oldModel = _db.Instance.Queryable <TBMMaterialFileDbModel>().Where(p =>
SqlFunc.IsNull(p.DeleteFlag, false) == false &&
p.CompanyId == currentUser.CompanyID &&
p.MaterialCode == editModel.MaterialCode && p.ID != editModel.ID).First();
if (oldModel != null)
{
return(ResponseUtil <TBMMaterialFileEditModel, bool> .FailResult(requestObject, false, editModel.MaterialCode + " 已经存在"));
}
editModel.CompanyId = currentUser.CompanyID;
result = await _db.Instance.Updateable(editModel).IgnoreColumns(p => new { p.CompanyId, p.PackageID, p.ColorSolutionID }).ExecuteCommandAsync() > 0;
}
ClearCache(currentUser);
//返回执行结果
if (result)
{
return(ResponseUtil <TBMMaterialFileEditModel, bool> .SuccessResult(requestObject, true));
}
return(ResponseUtil <TBMMaterialFileEditModel, bool> .FailResult(requestObject, false, "修改数据失败!"));
}
catch (Exception ex)
{
//返回异常结果
return(ResponseUtil <TBMMaterialFileEditModel, bool> .FailResult(requestObject, false, ex.Message));
}
}
/// <summary>
/// 获取T_BM_MaterialFile数据
/// </summary>
/// <param name="requestObject">返回响应结果对象，包括响应代码，查询操作结果</param>
/// <param name="currentUser"></param>
/// <returns></returns>
public async Task <ResponseObject <TBMMaterialFileQueryModel, List <TBMMaterialFileQueryModel> > > GetNoMemory(RequestObject <TBMMaterialFileQueryModel> requestObject, CurrentUser currentUser)
{
try
{
var tBMDictionaryDbModel = _db.Instance.Queryable <TBMDictionaryDbModel>().Where(p => p.CompanyId == currentUser.CompanyID).ToList().ToDictionary(p => p.ID, p => p.DicValue);
List <TBMMaterialFileQueryModel> queryData = null; //查询结果集对象
RefAsync <int> totalNumber = -1; //总记录数
var query = _db.Instance.Queryable <TBMMaterialFileDbModel, TBMDictionaryDbModel, TBMWarehouseFileDbModel>(
(t, t0, t1) => new object[]
{
JoinType.Left, t.ColorId == t0.ID,
JoinType.Left, t.DefaultWarehouseId == t1.ID,
}).Where((t, t0, t1) => t.CompanyId == currentUser.CompanyID && SqlFunc.IsNull(t.DeleteFlag, false) == false);
//查询条件
if (requestObject.QueryConditions != null && requestObject.QueryConditions.Count > 0)
{
var conditionals = SqlSugarUtil.GetConditionalModels(requestObject.QueryConditions);
query.Where(conditionals);
}
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
foreach (var item in requestObject.OrderByConditions)
{
var exp = SqlSugarUtil.GetOrderByLambda <TBMMaterialFileDbModel>(item.Column);
if (exp == null)
{
continue;
}
if (item.Condition.ToLower() != "asc" &&
item.Condition.ToLower() != "desc")
{
continue;
}
query.OrderBy($"{item.Column} {item.Condition}");
}
}
//执行查询
if (requestObject.IsPaging)
{
queryData = await query.Select(
(t, t0, t1) => new TBMMaterialFileQueryModel
{
ID = t.ID,
MaterialCode = t.MaterialCode,
MaterialName = t.MaterialName,
Spec = t.Spec,
ColorId = t.ColorId,
ColorName = t0.DicValue,
MaterialTypeId = t.MaterialTypeId,
DefaultWarehouseId = t.DefaultWarehouseId,
DefaultWarehouseName = t1.WarehouseName,
ShelfLife = t.ShelfLife,
HighInventory = t.HighInventory,
LowInventory = t.LowInventory,
BaseUnitId = t.BaseUnitId,
ProduceUnitId = t.ProduceUnitId,
ProduceRate = t.ProduceRate,
PurchaseUnitId = t.PurchaseUnitId,
PurchaseRate = t.PurchaseRate,
SalesUnitId = t.SalesUnitId,
SalesRate = t.SalesRate,
WarehouseUnitId = t.WarehouseUnitId,
WarehouseRate = t.WarehouseRate,
Remark = t.Remark,
Url = t.Url,
ColorSolutionID = t.ColorSolutionID,
PackageID = t.PackageID
})
.ToPageListAsync(requestObject.PageIndex, requestObject.PageSize, totalNumber);
}
else
{
queryData = await query.Select(
(t, t0, t1) => new TBMMaterialFileQueryModel
{
ID = t.ID,
MaterialCode = t.MaterialCode,
MaterialName = t.MaterialName,
Spec = t.Spec,
ColorId = t.ColorId,
ColorName = t0.DicValue,
MaterialTypeId = t.MaterialTypeId,
DefaultWarehouseId = t.DefaultWarehouseId,
DefaultWarehouseName = t1.WarehouseName,
ShelfLife = t.ShelfLife,
HighInventory = t.HighInventory,
LowInventory = t.LowInventory,
BaseUnitId = t.BaseUnitId,
ProduceUnitId = t.ProduceUnitId,
ProduceRate = t.ProduceRate,
PurchaseUnitId = t.PurchaseUnitId,
PurchaseRate = t.PurchaseRate,
SalesUnitId = t.SalesUnitId,
SalesRate = t.SalesRate,
WarehouseUnitId = t.WarehouseUnitId,
WarehouseRate = t.WarehouseRate,
Remark = t.Remark,
Url = t.Url,
ColorSolutionID = t.ColorSolutionID,
PackageID = t.PackageID
})
.ToListAsync();
}
queryData.ForEach((x) => {
if (tBMDictionaryDbModel.ContainsKey(x.BaseUnitId))
{
x.BaseUnitName = tBMDictionaryDbModel[x.BaseUnitId];
}
if (x.ProduceUnitId != null && tBMDictionaryDbModel.ContainsKey(x.ProduceUnitId.Value))
{
x.ProduceUnitName = tBMDictionaryDbModel[x.ProduceUnitId.Value];
}
if (x.PurchaseUnitId != null && tBMDictionaryDbModel.ContainsKey(x.PurchaseUnitId.Value))
{
x.PurchaseUnitName = tBMDictionaryDbModel[x.PurchaseUnitId.Value];
}
if (x.SalesUnitId != null && tBMDictionaryDbModel.ContainsKey(x.SalesUnitId.Value))
{
x.SalesUnitName = tBMDictionaryDbModel[x.SalesUnitId.Value];
}
if (x.WarehouseUnitId != null && tBMDictionaryDbModel.ContainsKey(x.WarehouseUnitId.Value))
{
x.WarehouseUnitName = tBMDictionaryDbModel[x.WarehouseUnitId.Value];
}
if (tBMDictionaryDbModel.ContainsKey(x.MaterialTypeId))
{
x.MaterialTypeName = tBMDictionaryDbModel[x.MaterialTypeId];
}
});
//返回执行结果
return(ResponseUtil <TBMMaterialFileQueryModel, List <TBMMaterialFileQueryModel> > .SuccessResult(requestObject, queryData, totalNumber));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <TBMMaterialFileQueryModel, List <TBMMaterialFileQueryModel> > .FailResult(requestObject, null, ex.Message));
}
}
/// <summary>
///
/// </summary>
/// <param name="requestObject"></param>
/// <param name="currentUser"></param>
/// <returns></returns>
public async Task <ResponseObject <List <InventoryResultModel> > > LoadReport(RequestGet requestObject, CurrentUser currentUser)
{
try
{
//数据字典
var tBMDictionary = _db.Instance.Queryable <TBMDictionaryDbModel>().Where(p => p.CompanyId == currentUser.CompanyID).ToList().ToDictionary(p => p.ID, p => p.DicValue);
var warehouseDic = _db.Instance.Queryable <TBMWarehouseFileDbModel>().Where(t => SqlFunc.IsNull(t.DeleteFlag, false) != true &&
t.CompanyId == currentUser.CompanyID).ToList().ToDictionary(p => p.ID, p => new { Name = p.WarehouseName, Code = p.Code });
List <InventoryResultModel> result = new List <InventoryResultModel>();
//其他出入库
var otherCount = _db.Instance.Queryable <TWMOtherCountDbModel>().
Select(p => new InAndOutModel()
{
MaterialId = p.MaterialId,
WarehouseId = p.WarehouseId,
WhNumber = p.WhNumber,
WhSendNumber = p.WhSendNumber
});
//盘亏盘盈出入库
var pCount = _db.Instance.Queryable <TWMProfitDeficitCountDbModel>().
Select(p => new InAndOutModel()
{
MaterialId = p.MaterialId,
WarehouseId = p.WarehouseId,
WhNumber = p.WhNumber,
WhSendNumber = p.WhSendNumber
});
//销售出入库
var SaleCount = _db.Instance.Queryable <TWMSalesCountDbModel>().
Select(p => new InAndOutModel()
{
MaterialId = p.MaterialId,
WarehouseId = p.WarehouseId,
WhNumber = p.WhNumber,
WhSendNumber = p.WhSendNumber
});
//采购出入库
var PurchaseCount = _db.Instance.Queryable <TWMPurchaseCountDbModel>().
Select(p => new InAndOutModel()
{
MaterialId = p.MaterialId,
WarehouseId = p.WarehouseId,
WhNumber = p.WhNumber,
WhSendNumber = p.WhSendNumber
});
//生产出入库
var ProductCount = _db.Instance.Queryable <TWMProductionCountDbModel>().
Select(p => new InAndOutModel()
{
MaterialId = p.MaterialId,
WarehouseId = p.WarehouseId,
WhNumber = p.WhNumber,
WhSendNumber = p.WhSendNumber
});
//出入库数量
var allCount = _db.Instance.UnionAll(otherCount, pCount, SaleCount, PurchaseCount, ProductCount).GroupBy(p => new { p.MaterialId, p.WarehouseId }).
Select(p => new TradeInventoryModel()
{
TradeNumber = SqlFunc.AggregateSum(p.WhNumber) - SqlFunc.AggregateSum(p.WhSendNumber),
MaterialId = p.MaterialId,
WarehouseId = p.WarehouseId
}).AS("t100");
var materialFileQuery = _db.Instance.Queryable <TBMMaterialFileDbModel>().Where(p => p.CompanyId == currentUser.CompanyID);
var ts = _db.Instance.Queryable(materialFileQuery, allCount, JoinType.Inner, (p1, p2) => p1.ID == p2.MaterialId);
#region 待出库数量
//其他待出库
var otherToOut = _db.Instance.Queryable <TWMOtherWhSendMainDbModel, TWMOtherWhSendDetailDbModel>((t1, t2) =>
new object[] { JoinType.Inner, t1.ID == t2.MainId }).Where((t1, t2) =>
t1.AuditStatus != 2 &&
t1.DeleteFlag == false).Select((t1, t2) => new InventoryOut {
MaterialId = t2.MaterialId, Amount = t2.ActualNumber
});
//盘亏出库
var deficitToOut = _db.Instance.Queryable <TWMDeficitMainDbModel, TWMDeficitDetailDbModel>((t1, t2) =>
new object[] { JoinType.Inner, t1.ID == t2.MainId }).Where((t1, t2) =>
t1.AuditStatus != 2 &&
t1.DeleteFlag == false).Select((t1, t2) => new InventoryOut {
MaterialId = t2.MaterialId, Amount = t2.ActualNumber
});
//销售出库
var saleToOut = _db.Instance.Queryable <TWMSalesMainDbModel, TWMSalesDetailDbModel>((t1, t2) =>
new object[] { JoinType.Inner, t1.ID == t2.MainId }).Where((t1, t2) =>
t1.AuditStatus != 2 &&
t1.DeleteFlag == false).Select((t1, t2) => new InventoryOut {
MaterialId = t2.MaterialId, Amount = t2.ActualNum
});
//生产待出库
var productToOut = _db.Instance.Queryable <TWMProductionMainDbModel, TWMProductionDetailDbModel>((t1, t2) =>
new object[] { JoinType.Inner, t1.ID == t2.MainId }).Where((t1, t2) =>
t1.AuditStatus != 2 &&
t1.DeleteFlag == false).Select((t1, t2) => new InventoryOut {
MaterialId = t2.MaterialId, Amount = t2.ActualNum
});
var allToOut = _db.Instance.UnionAll(otherToOut, deficitToOut, saleToOut, productToOut).AS("t101");
var tsToOut = allToOut.ToList().GroupBy(p => p.MaterialId).Select(p => new InventoryOut()
{
MaterialId = p.Key, Amount = p.Sum(m => m.Amount)
}).ToList()
.ToDictionary(p => p.MaterialId, p => new { MaterialId = p.MaterialId, Amount = p.Amount });
#endregion
string[] cQuery = { "warehouseid" };
//查询条件
if (requestObject.QueryConditions != null && requestObject.QueryConditions.Count > 0)
{
var QueryConditions1 = requestObject.QueryConditions.Where(p => !cQuery.Contains(p.Column.ToLower())).ToList();
if (QueryConditions1.Count() > 0)
{
var conditionals1 = SqlSugarUtil.GetConditionalModels(QueryConditions1);
foreach (ConditionalModel item in conditionals1)
{
item.FieldName = $"p1.{item.FieldName}";
}
ts.Where(conditionals1);
}
var QueryConditions2 = requestObject.QueryConditions.Where(p => cQuery.Contains(p.Column.ToLower())).FirstOrDefault();
if (QueryConditions2 != null)
{
int WarehouseId = Convert.ToInt32(QueryConditions2.Content);
ts = ts.Where((p1, p2) => p2.WarehouseId == WarehouseId);
}
}
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
foreach (var item in requestObject.OrderByConditions)
{
var exp = SqlSugarUtil.GetOrderByLambda <TBMMaterialFileDbModel>(item.Column);
if (exp == null)
{
continue;
}
if (item.Condition.ToLower() != "asc" &&
item.Condition.ToLower() != "desc")
{
continue;
}
ts.OrderBy($"p1.{item.Column} {item.Condition}");
}
}
#region 最新采购/生产时间
var Purchase = _db.Instance.Queryable <TPSMPurchaseOrderDetailDbModel, TPSMPurchaseOrderMainDbModel>(
(t1, t2) => new object[] { JoinType.Left, t1.MainId == t2.ID })
.Where((t1, t2) => t2.CompanyId == currentUser.CompanyID && SqlFunc.IsNull(t2.DeleteFlag, false) != true &&
t2.AuditStatus == 2
)
.GroupBy((t1, t2) => t1.MaterialId)
.Select((t1, t2) => new { MaterialId = t1.MaterialId, OrderDate = SqlFunc.AggregateMax(t2.OrderDate) }).ToList()
.ToDictionary(p => p.MaterialId, p => new { MaterialId = p.MaterialId, OrderDate = p.OrderDate });
var Production = _db.Instance.Queryable <TMMProductionOrderDetailDbModel, TMMProductionOrderMainDbModel>(
(t1, t2) => new object[] { JoinType.Left, t1.MainId == t2.ID })
.Where((t1, t2) => t2.CompanyId == currentUser.CompanyID && SqlFunc.IsNull(t2.DeleteFlag, false) != true &&
t2.AuditStatus == 2)
.GroupBy((t1, t2) => t1.MaterialId)
.Select((t1, t2) => new { MaterialId = t1.MaterialId, OrderDate = SqlFunc.AggregateMax(t2.OrderDate) }).ToList()
.ToDictionary(p => p.MaterialId, p => new { MaterialId = p.MaterialId, OrderDate = p.OrderDate });
#endregion
int totalNum = -1;
if (requestObject.IsPaging)
{
int skipNum = requestObject.PageSize * (requestObject.PageIndex - 1);
totalNum = ts.Count();
result = await ts.Select((p1, p2) => new InventoryResultModel
{
MaterialName = p1.MaterialName,
BaseUnitId = p1.BaseUnitId,
ColorId = p1.ColorId,
MaterialCode = p1.MaterialCode,
MaterialId = p1.ID,
Spec = p1.Spec,
WarehouseAmount = p2.TradeNumber,
WarehouseUnitId = p1.WarehouseUnitId,
WarehouseRate = p1.WarehouseRate,
WarehouseId = p2.WarehouseId,
ShelfLife = p1.ShelfLife
}).Skip(skipNum).Take(requestObject.PageSize).ToListAsync();
}
else
{
result = await ts.Select((p1, p2) => new InventoryResultModel
{
MaterialName = p1.MaterialName,
BaseUnitId = p1.BaseUnitId,
ColorId = p1.ColorId,
MaterialCode = p1.MaterialCode,
MaterialId = p1.ID,
Spec = p1.Spec,
WarehouseAmount = p2.TradeNumber,
WarehouseUnitId = p1.WarehouseUnitId,
WarehouseRate = p1.WarehouseRate,
WarehouseId = p2.WarehouseId,
ShelfLife = p1.ShelfLife
}).ToListAsync();
}
var TWMPrimeCountDbList = _db.Instance.Queryable <TWMPrimeCountDbModel>().Where(p => p.CompanyId == currentUser.CompanyID).ToList();
result.ForEach(x =>
{
#region 最新生产/采购时间
string Production_PurchaseDateTime = "";
if (Production.ContainsKey(x.MaterialId))
{
Production_PurchaseDateTime += "" + Convert.ToDateTime(Production[x.MaterialId].OrderDate).ToString("yyyy-MM-dd");
}
else
{
Production_PurchaseDateTime += "无生产";
}
if (Purchase.ContainsKey(x.MaterialId))
{
Production_PurchaseDateTime += "/" + Convert.ToDateTime(Purchase[x.MaterialId].OrderDate).ToString("yyyy-MM-dd");
}
else
{
Production_PurchaseDateTime += "/无采购";
}
if (Production_PurchaseDateTime != "")
{
x.Production_PurchaseDateTime = Production_PurchaseDateTime;
}
#endregion
if (tsToOut.ContainsKey(x.MaterialId))
{
x.WarehouseAvailabilityAmount = x.WarehouseAmount - tsToOut[x.MaterialId].Amount;
}
if (x.WarehouseUnitId.HasValue)
{
if (tBMDictionary.ContainsKey(x.WarehouseUnitId.Value))
{
x.WarehouseUnitName = tBMDictionary[x.WarehouseUnitId.Value];
}
}
if (warehouseDic.ContainsKey(x.WarehouseId))
{
x.WarehouseName = warehouseDic[x.WarehouseId].Name;
x.WarehouseCode = warehouseDic[x.WarehouseId].Code;
}
if (tBMDictionary.ContainsKey(x.BaseUnitId))
{
x.BaseUnitName = tBMDictionary[x.BaseUnitId];
}
if (string.IsNullOrEmpty(x.WarehouseUnitName))
{
x.WarehouseUnitName = x.BaseUnitName;
}
if (x.WarehouseRate == null)
{
x.WarehouseRate = 1;
}
if (x.ColorId.HasValue)
{
if (tBMDictionary.ContainsKey(x.ColorId.Value))
{
x.ColorName = tBMDictionary[x.ColorId.Value];
}
}
var firstEntity = TWMPrimeCountDbList.Where(p => p.WarehouseId == x.WarehouseId && p.MaterialId == x.MaterialId).FirstOrDefault();
if (firstEntity != null)
{
x.WarehouseAmount = x.WarehouseAmount + firstEntity.PrimeNum;
x.PrimeNum = firstEntity.PrimeNum;
}
});
return(ResponseUtil <List <InventoryResultModel> > .SuccessResult(result, totalNum));
}
catch (Exception ex)
{
return(ResponseUtil <List <InventoryResultModel> > .FailResult(null, ex.Message));
}
}
/// <summary>
/// 获取T_PSM_PurchaseOrderMain主表数据数据
/// </summary>
/// <param name="requestObject">Get请求参数</param>
/// <param name="currentUser"></param>
/// <returns>返回响应结果对象，包括响应代码，查询操作结果</returns>
public async Task <ResponseObject <List <TPSMPurchaseOrderMainQueryModel> > > GetMainListAsync(RequestGet requestObject, CurrentUser currentUser)
{
try
{
List <TPSMPurchaseOrderMainQueryModel> queryData = null; //查询结果集对象
RefAsync <int> totalNumber = -1; //总记录数
var query = _db.Instance.Queryable <TPSMPurchaseOrderMainDbModel,
TSMUserAccountDbModel, TBMDictionaryDbModel, TBMDictionaryDbModel, TSMUserAccountDbModel,
TSMUserAccountDbModel, TMMPurchaseApplyMainDbModel>(
(t, t1, t2, t3, t4, t5, t6) => new object[]
{
JoinType.Left, t.BuyerId == t1.ID,
JoinType.Left, t.OrderTypeId == t2.ID,
JoinType.Left, t.SettlementTypeId == t3.ID,
JoinType.Left, t.AuditId == t4.ID,
JoinType.Left, t.OperatorId == t5.ID,
JoinType.Left, t.SourceId == t6.ID
});
//查询条件
if (requestObject.QueryConditions != null && requestObject.QueryConditions.Count > 0)
{
var lessSourceID = requestObject.QueryConditions.Where(p => p.Column.ToLower() == "sourceid" && p.Condition == ConditionEnum.LessThan).FirstOrDefault();
if (lessSourceID != null)
{
requestObject.QueryConditions.Remove(lessSourceID);
query = query.Where((t, t1, t2, t3, t4, t5, t6) => SqlFunc.IsNull(t6.SourceId, 0) == 0);
}
if (requestObject.QueryConditions.Count > 0)
{
var conditionals = SqlSugarUtil.GetConditionalModels(requestObject.QueryConditions);
foreach (ConditionalModel item in conditionals)
{
if (item.FieldName.ToLower() == "suppliername")
{
item.FieldName = $"t0.{item.FieldName}";
continue;
}
item.FieldName = $"t.{item.FieldName}";
}
query.Where(conditionals);
}
}
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
foreach (var item in requestObject.OrderByConditions)
{
var exp = SqlSugarUtil.GetOrderByLambda <TPSMPurchaseOrderMainDbModel>(item.Column);
if (exp == null)
{
continue;
}
if (item.Condition.ToLower() != "asc" &&
item.Condition.ToLower() != "desc")
{
continue;
}
query.OrderBy($"{item.Column} {item.Condition}");
}
}
//执行查询
if (requestObject.IsPaging)
{
queryData = await query
.Select((t, t1, t2, t3, t4, t5, t6) => new TPSMPurchaseOrderMainQueryModel
{
ID = t.ID,
BuyerId = t.BuyerId,
BuyerName = t1.AccountName,
OrderNo = t.OrderNo,
OrderDate = t.OrderDate,
OrderTypeId = t.OrderTypeId,
OrderTypeName = t2.DicValue,
SettlementTypeId = t.SettlementTypeId,
SettlementTypeName = t3.DicValue,
Currency = t.Currency,
AuditStatus = t.AuditStatus,
AuditId = t.AuditId,
AuditName = t4.AccountName,
AuditTime = t.AuditTime,
OperatorId = t.OperatorId,
OperatorName = t5.AccountName,
ContactName = t.ContactName,
ContactNumber = t.ContactNumber,
CompanyId = t.CompanyId,
DeleteFlag = t.DeleteFlag,
TransferStatus = t.TransferStatus,
PurchaseNum = t.PurchaseNum,
PurchaseAmount = t.PurchaseAmount,
AllowEdit = t.AuditStatus != 2 && t.OperatorId == currentUser.UserID,
SourceId = t.SourceId,
SourceNo = t6.PurchaseNo
})
.Where((t) => t.CompanyId == currentUser.CompanyID && !t.DeleteFlag)
.ToPageListAsync(requestObject.PageIndex, requestObject.PageSize, totalNumber);
}
else
{
queryData = await query
.Select((t, t1, t2, t3, t4, t5, t6) => new TPSMPurchaseOrderMainQueryModel
{
ID = t.ID,
BuyerId = t.BuyerId,
BuyerName = t1.AccountName,
OrderNo = t.OrderNo,
OrderDate = t.OrderDate,
OrderTypeId = t.OrderTypeId,
OrderTypeName = t2.DicValue,
SettlementTypeId = t.SettlementTypeId,
SettlementTypeName = t3.DicValue,
Currency = t.Currency,
AuditStatus = t.AuditStatus,
AuditId = t.AuditId,
AuditName = t4.AccountName,
AuditTime = t.AuditTime,
OperatorId = t.OperatorId,
OperatorName = t5.AccountName,
ContactName = t.ContactName,
ContactNumber = t.ContactNumber,
CompanyId = t.CompanyId,
DeleteFlag = t.DeleteFlag,
TransferStatus = t.TransferStatus,
PurchaseNum = t.PurchaseNum,
PurchaseAmount = t.PurchaseAmount,
SourceNo = t6.PurchaseNo,
SourceId = t.SourceId
})
.Where((t) => t.CompanyId == currentUser.CompanyID && !t.DeleteFlag)
.ToListAsync();
}
//返回执行结果
return(ResponseUtil <List <TPSMPurchaseOrderMainQueryModel> > .SuccessResult(queryData, totalNumber));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <List <TPSMPurchaseOrderMainQueryModel> > .FailResult(null, ex.Message));
}
}
/// <summary>
/// 导入期初
/// </summary>
public async Task <ResponseObject <List <string> > > ImportPrime(RequestPost <ImportPrimeModel> requestObject, CurrentUser currentUser)
{
try
{
//没有新增数据，返回错误信息
if (requestObject.PostDataList == null || requestObject.PostDataList.Count() == 0)
{
return(ResponseUtil <List <string> > .FailResult(null, "PostDataList必须有值"));
}
//合法性检查
//仓库
var Warehouses = _db.Instance.Queryable <TBMWarehouseFileDbModel>().
Where(p => p.CompanyId == currentUser.CompanyID && SqlFunc.IsNull(p.DeleteFlag, false) == false).ToList();
//单位
var unitList = _db.Instance.Queryable <TBMDictionaryDbModel, TBMDictionaryTypeDbModel>(
(t, t0) => new object[]
{
JoinType.Left, t.TypeId == t0.ID,
}).Where((t, t0) => SqlFunc.IsNull(t.DeleteFlag, false) == false && t.CompanyId == currentUser.CompanyID &&
SqlFunc.IsNull(t0.DeleteFlag, false) == false && t0.TypeName == "计量单位"
).ToList();
//物料
var MaterialList = _db.Instance.Queryable <TBMMaterialFileDbModel>().Where(t => SqlFunc.IsNull(t.DeleteFlag, false) == false && t.CompanyId == currentUser.CompanyID).ToList();
List <TWMPrimeCountDbModel> tWMPrimeCountDbModels = new List <TWMPrimeCountDbModel>();
var importPrimeModels = requestObject.PostDataList;
int index = 1;
List <string> errorList = new List <string>();
List <TWMPrimeCountDbModel> TWMPrimeCountList = new List <TWMPrimeCountDbModel>();
foreach (var item in importPrimeModels)
{
string msg = string.Empty;
TWMPrimeCountDbModel tWMPrimeCountDbModel = new TWMPrimeCountDbModel();
var material = MaterialList.Where(p => p.MaterialCode == item.MaterialCode).FirstOrDefault();
if (material != null)
{
tWMPrimeCountDbModel.MaterialId = material.ID;
int unitId = material.WarehouseUnitId == null ? material.BaseUnitId : material.WarehouseUnitId.Value;
TBMDictionaryDbModel unit = unitList.Where(p => p.ID == unitId).FirstOrDefault();
if (unit.DicValue != item.UnitName)
{
msg += $"库存单位应该为{unit.DicValue}";
}
}
else
{
msg += $"物料代码{item.MaterialCode}不存在;";
}
var houses = Warehouses.Where(p => p.WarehouseName == item.WarehouseName).FirstOrDefault();
if (houses != null)
{
tWMPrimeCountDbModel.WarehouseId = houses.ID;
}
tWMPrimeCountDbModel.PrimeNum = item.Num;
tWMPrimeCountDbModel.CompanyId = currentUser.CompanyID;
if (msg != string.Empty)
{
msg = $"第{index}行数据：{msg}";
errorList.Add(msg);
}
else
{
TWMPrimeCountList.Add(tWMPrimeCountDbModel);
}
index++;
}
if (errorList.Count() > 0)
{
return(ResponseUtil <List <string> > .FailResult(errorList));
}
if (TWMPrimeCountList.Count() > 0)
{
await _db.Instance.Insertable <TWMPrimeCountDbModel>(TWMPrimeCountList).ExecuteCommandAsync();
}
return(ResponseUtil <List <string> > .SuccessResult(null));
}
catch (Exception ex)
{
return(ResponseUtil <List <string> > .FailResult(null, ex.Message));
}
}
/// <summary>
/// 导出期初模板
/// </summary>
/// <param name="currentUser"></param>
/// <returns></returns>
public async Task <ResponseObject <ExportOpeningTemplateModel> > ExportOpeningTemplate(CurrentUser currentUser)
{
string error = string.Empty;
int index = 0;
String warehouseName = "";
var warehouseDic = await _db.Instance.Queryable <TBMWarehouseFileDbModel>().Where(t => SqlFunc.IsNull(t.DeleteFlag, false) == false &&
t.CompanyId == currentUser.CompanyID).ToListAsync();
warehouseDic.ForEach(x =>
{
warehouseName += "," + x.WarehouseName;
});
//单位
var unitList = _db.Instance.Queryable <TBMDictionaryDbModel, TBMDictionaryTypeDbModel>(
(t, t0) => new object[]
{
JoinType.Left, t.TypeId == t0.ID,
}).Where((t, t0) => SqlFunc.IsNull(t.DeleteFlag, false) == false && t.CompanyId == currentUser.CompanyID &&
SqlFunc.IsNull(t0.DeleteFlag, false) == false && t0.TypeName == "计量单位"
).Select((t, t0) => t).ToList();
ExportOpeningTemplateModel result = new ExportOpeningTemplateModel();
var materialFileQuery = await _db.Instance.Queryable <TBMMaterialFileDbModel>().Where(p => p.CompanyId == currentUser.CompanyID && SqlFunc.IsNull(p.DeleteFlag, false) == false)
.Select(p => new MaterialFileTemplate
{
BasicUnitId = p.BaseUnitId,
WareUnitId = p.WarehouseUnitId,
MaterialCode = p.MaterialCode,
MaterialName = p.MaterialName,
Spec = p.Spec
}).ToListAsync();
materialFileQuery.ForEach((x) => {
index++;
if (x.WareUnitId != null)
{
var unitDic = unitList.Where(p => p.ID == x.WareUnitId).FirstOrDefault();
if (unitDic == null)
{
error += $"物料：{x.MaterialName},代码{x.MaterialCode}的 仓库单位已经被删除，请重新设置{System.Environment.NewLine}";
}
else
{
x.WareUnitName = unitDic.DicValue;
}
}
else
{
var unitDic = unitList.Where(p => p.ID == x.BasicUnitId).FirstOrDefault();
if (unitDic == null)
{
error += ($"物料：{x.MaterialName},代码{x.MaterialCode}的 基本单位已经被删除，请重新设置{System.Environment.NewLine}");
}
else
{
x.WareUnitName = unitDic.DicValue;
}
}
});
if (error != string.Empty)
{
throw new Exception(error);
}
result.List = materialFileQuery;
result.WarehouseName = warehouseName.Substring(1).Split("1");
return(ResponseUtil <ExportOpeningTemplateModel> .SuccessResult(result));
}
/// <summary>
/// 获取T_BM_Package数据
/// </summary>
/// <param name="requestObject">返回响应结果对象，包括响应代码，查询操作结果</param>
/// <returns></returns>
public async Task <ResponseObject <List <TBMPackageQueryModel> > > GetAsync(RequestGet requestObject, CurrentUser currentUser)
{
try
{
List <TBMPackageQueryModel> queryData = null; //查询结果集对象
RefAsync <int> totalNumber = -1; //总记录数
var query = _db.Instance.Queryable <TBMPackageDbModel, TSMUserAccountDbModel, TSMUserAccountDbModel>(
(t, t1, t2) => new object[]
{
JoinType.Left, t.CreateId == t1.ID,
JoinType.Left, t.UpdateId == t2.ID
}).Where((t, t1, t2) => SqlFunc.IsNull(t.DeleteFlag, false) == false && t.CompanyId == currentUser.CompanyID);
//查询条件
if (requestObject.QueryConditions != null && requestObject.QueryConditions.Count > 0)
{
var conditionals = SqlSugarUtil.GetConditionalModels(requestObject.QueryConditions);
foreach (ConditionalModel item in conditionals)
{
item.FieldName = item.FieldName = $"t.{item.FieldName}";
}
query.Where(conditionals);
}
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
foreach (var item in requestObject.OrderByConditions)
{
var exp = SqlSugarUtil.GetOrderByLambda <TBMPackageDbModel>(item.Column);
if (exp == null)
{
continue;
}
if (item.Condition.ToLower() != "asc" &&
item.Condition.ToLower() != "desc")
{
continue;
}
query.OrderBy($"{item.Column} {item.Condition}");
}
}
//执行查询
if (requestObject.IsPaging)
{
queryData = await query
.Where(t => t.DeleteFlag == false)
.Select((t, t1, t2) => new TBMPackageQueryModel
{
ID = t.ID,
DicCode = t.DicCode,
DicValue = t.DicValue,
Remark = t.Remark,
CreateTime = t.CreateTime,
CreateId = t.CreateId,
CreateName = t1.AccountName,
UpdateTime = t.UpdateTime,
UpdateId = t.UpdateId,
UpdateName = t2.AccountName,
ImgPath = t.ImgPath,
})
.ToPageListAsync(requestObject.PageIndex, requestObject.PageSize, totalNumber);
}
else
{
queryData = await query
.Where(t => t.DeleteFlag == false)
.Select((t, t1, t2) => new TBMPackageQueryModel
{
ID = t.ID,
DicCode = t.DicCode,
DicValue = t.DicValue,
Remark = t.Remark,
CreateTime = t.CreateTime,
CreateId = t.CreateId,
CreateName = t1.AccountName,
UpdateTime = t.UpdateTime,
UpdateId = t.UpdateId,
UpdateName = t2.AccountName,
ImgPath = t.ImgPath,
})
.ToListAsync();
}
//返回执行结果
return(ResponseUtil <List <TBMPackageQueryModel> > .SuccessResult(queryData, totalNumber));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <List <TBMPackageQueryModel> > .FailResult(null, ex.Message));
}
}
public static void Updateable()
{
Db.CodeFirst.InitTables(typeof(UnitUser));
Db.DbMaintenance.TruncateTable <UnitUser>();
Db.Insertable(new UnitUser()
{
USER_ID = 1, USER_ACCOUNT = "a", USER_PWD = "b", USER_NAME = "c", PWD_LASTCHTIME = DateTime.Now, PWD_ERRORCOUNT = 1, PWD_LASTERRTIME = DateTime.Now
}).ExecuteCommand();
Db.Updateable(new UnitUser()
{
USER_ID = 1, PWD_LASTERRTIME = null
}).WhereColumns(it => new { it.PWD_ERRORCOUNT, it.PWD_LASTERRTIME }).ExecuteCommand();
Db.CodeFirst.InitTables(typeof(UnitBoolTest));
var x = new UnitBoolTest();
Db.Updateable <UnitBoolTest>().SetColumns(it => new UnitBoolTest()
{
BoolValue = !it.BoolValue
}).Where(it => it.Id == 1).ExecuteCommand();
Db.Updateable <UnitBoolTest>().SetColumns(it => it.BoolValue == !it.BoolValue).Where(it => it.Id == 1).ExecuteCommand();
Db.Updateable <UnitBoolTest>().SetColumns(it => new UnitBoolTest()
{
BoolValue = x.BoolValue
}).Where(it => it.Id == 1).ExecuteCommand();
Db.Updateable <UnitBoolTest>().SetColumns(it => it.BoolValue == x.BoolValue).Where(it => it.Id == 1).ExecuteCommand();
Db.Updateable <UnitBoolTest>().SetColumns(it => new UnitBoolTest()
{
BoolValue = !x.BoolValue
}).Where(it => it.Id == 1).ExecuteCommand();
Db.Updateable <UnitBoolTest>().SetColumns(it => it.BoolValue == !x.BoolValue).Where(it => it.Id == 1).ExecuteCommand();
Db.Updateable <UnitBoolTest>(x).ReSetValue(it => it.BoolValue = it.BoolValue).ExecuteCommand();
Db.Updateable <UnitBoolTest>(x).ReSetValue(it => it.BoolValue = true).ExecuteCommand();
Db.Updateable <UnitBoolTest>(x).ReSetValue(it => it.BoolValue = !it.BoolValue).ExecuteCommand();
Db.Updateable <UnitBoolTest>(x).UpdateColumns(it => new { it.BoolValue }).ExecuteCommand();
UnitSaveDiary saveDiary = new UnitSaveDiary();
saveDiary.ID = 2;
saveDiary.TypeID = 10;
saveDiary.TypeName = "类型100";
saveDiary.Title = "标题1000";
saveDiary.Content = "内容";
saveDiary.Time = DateTime.Now;
saveDiary.IsRemind = false;//无论传false/true 最终执行的结果都是以true执行的
var sql = Db.Updateable <UnitDiary>().SetColumns(it => new UnitDiary()
{
IsRemind = saveDiary.IsRemind,
}).Where(it => it.ID == saveDiary.ID).ToSql();
UValidate.Check(sql.Key, @"UPDATE [Diary] SET
[IsRemind] = @Const0 WHERE ( [ID] = @ID1 )", "Updateable");
sql = Db.Updateable <UnitDiary>().SetColumns(it => new UnitDiary()
{
TypeID = saveDiary.TypeID,
}).Where(it => it.ID == saveDiary.ID).ToSql();
UValidate.Check(sql.Key, @"UPDATE [Diary] SET
[TypeID] = @Const0 WHERE ( [ID] = @ID1 )", "Updateable");
sql = Db.Updateable <UnitDiary>().SetColumns(it => new UnitDiary()
{
TypeID = saveDiary.TypeID,
}).Where(it => it.ID == saveDiary.ID).ToSql();
UValidate.Check(sql.Key, @"UPDATE [Diary] SET
[TypeID] = @Const0 WHERE ( [ID] = @ID1 )", "Updateable");
sql = Db.Updateable <NullTest>().SetColumns(it => new NullTest()
{
p = true
}).Where(it => it.id == 1).ToSql();
UValidate.Check(sql.Key, @"UPDATE [NullTest] SET
[p] = @Const0 WHERE ( [id] = @id1 )", "Updateable");
sql = Db.Updateable <NullTest>().SetColumns(it => new NullTest()
{
p2 = true
}).Where(it => it.id == 1).ToSql();
UValidate.Check(sql.Key, @"UPDATE [NullTest] SET
[p2] = @Const0 WHERE ( [id] = @id1 )", "Updateable");
Db.Updateable <Order>()
.SetColumns(it => it.Name == "a")
.SetColumns(it => it.CreateTime == DateTime.Now)
.SetColumns(it => it.Price == 1).Where(it => it.Id == 1).ExecuteCommand();
Db.Updateable <Order>()
.SetColumns(it => new Order {
Name = "a", CreateTime = DateTime.Now
})
.SetColumns(it => it.Price == 1).Where(it => it.Id == 1).ExecuteCommand();
Db.Updateable <Order>()
.SetColumns(it => new Order {
Name = "a", CreateTime = DateTime.Now
})
.SetColumns(it => new Order()
{
Price = 1
}).Where(it => it.Id == 1).ExecuteCommand();
Db.Updateable <Order>()
.SetColumns(it => new Order {
Name = it.Id > 0 ?"1":"2", CreateTime = DateTime.Now
})
.Where(it => it.Id == 1).ExecuteCommand();
Db.Updateable <Order>()
.SetColumns(it => new Order {
Name = SqlFunc.IsNull(it.Name, "a") + "b", CreateTime = DateTime.Now
})
.Where(it => it.Id == 1).ExecuteCommand();
Db.CodeFirst.InitTables <Unitbluecopy>();
Db.Insertable(new Unitbluecopy()).UseSqlServer().ExecuteBulkCopy();
}
public static void Queryable()
{
var pageindex = 1;
var pagesize = 10;
var total = 0;
var totalPage = 0;
var list = Db.Queryable <Order>().ToPageList(pageindex, pagesize, ref total, ref totalPage);
//Db.CodeFirst.InitTables(typeof(CarType));
//Db.Updateable<CarType>()
// .SetColumns(it => new CarType { State = SqlSugar.SqlFunc.IIF(it.State == true, false, true) }).Where(it => true)
// .ExecuteCommand();
//Db.CodeFirst.InitTables(typeof(TestTree));
//Db.DbMaintenance.TruncateTable<TestTree>();
//Db.Ado.ExecuteCommand("insert testtree values(hierarchyid::GetRoot(),geography :: STGeomFromText ('POINT(55.9271035250276 -3.29431266523898)',4326),'name')");
//var list2 = Db.Queryable<TestTree>().ToList();
Db.CodeFirst.InitTables <UnitGuidTable>();
Db.Queryable <UnitGuidTable>().Where(it => it.Id.HasValue).ToList();
Db.Queryable <Order>().Where(it => SqlSugar.SqlFunc.Equals(it.CreateTime.Date, it.CreateTime.Date)).ToList();
var sql = Db.Queryable <UnitSelectTest>().Select(it => new UnitSelectTest()
{
DcNull = it.Dc,
Dc = it.Int
}).ToSql().Key;
UValidate.Check(sql, "SELECT [Dc] AS [DcNull] , [Int] AS [Dc] FROM [UnitSelectTest]", "Queryable");
sql = Db.Updateable <UnitSelectTest2>(new UnitSelectTest2()).ToSql().Key;
UValidate.Check(sql, @"UPDATE [UnitSelectTest2] SET
[Dc]=@Dc,[IntNull]=@IntNull WHERE [Int]=@Int", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns(it => it.CreateTime).ToSql().Key;
UValidate.Check(sql, "SELECT [Id],[Name],[Price],[CustomId] FROM [Order] ", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns(it => new { it.Id, it.Name }).ToSql().Key;
UValidate.Check(sql, "SELECT [Price],[CreateTime],[CustomId] FROM [Order] ", "Queryable");
sql = Db.Queryable <Order>().IgnoreColumns("id").ToSql().Key;
UValidate.Check(sql, "SELECT [Name],[Price],[CreateTime],[CustomId] FROM [Order] ", "Queryable");
var cts = IEnumerbleContains.Data();
var list2 = Db.Queryable <Order>()
.Where(p => /*ids.*/ cts.Select(c => c.Id).Contains(p.Id)).ToList();
var cts2 = IEnumerbleContains.Data().ToList();;
var list3 = Db.Queryable <Order>()
.Where(p => /*ids.*/ cts2.Select(c => c.Id).Contains(p.Id)).ToList();
var list4 = Db.Queryable <Order>()
.Where(p => new List <int> {
1, 2, 3
}.Where(b => b > 1).Contains(p.Id)).ToList();
Db.CodeFirst.InitTables <UnitTest3>();
var list5 = Db.Queryable <UnitTest3>().Where(it => SqlSugar.SqlFunc.ToString(it.Date.Value.Year) == "1").ToList();
var list6 = Db.Queryable <UnitTest3>().Where(it => it.Date.Value.Year == 1).ToList();
var list7 = Db.Queryable <UnitTest3>().Where(it => it.Date.Value.Date == DateTime.Now.Date).ToList();
SaleOrder saleOrderInfo = new SaleOrder();
Db.CodeFirst.InitTables <SaleOrder>();
var result = Db.GetSimpleClient <SaleOrder>().Update(o => new SaleOrder()
{
OrderStatus = 1,
CheckMan = saleOrderInfo.CheckMan,
CheckTime = DateTime.Now
}, o => o.OrderSn == saleOrderInfo.OrderSn && o.OrderStatus != 1);
var ids = Enumerable.Range(1, 11).ToList();
var list8 = Db.Queryable <Order>().Where(it => SqlFunc.ContainsArrayUseSqlParameters(ids, it.Id)).ToList();
var result2 = Db.Queryable <Unit_SYS_USER>().Where(o => o.XH == UserLoginInfo.XH).Select(o => o.XH).ToSql();
var x = Db.Queryable <BoolTest1>().Select(it => new BoolTest2()
{
a = it.a
}).ToSql();
UValidate.Check(x.Key, "SELECT [a] AS [a] FROM [BoolTest1] ", "Queryable");
x = Db.Queryable <BoolTest2>().Select(it => new BoolTest1()
{
a = it.a.Value
}).ToSql();
UValidate.Check(x.Key, "SELECT [a] AS [a] FROM [BoolTest2] ", "Queryable");
var db = Db;
db.CodeFirst.InitTables <UserInfo, UserIpRuleInfo>();
db.Deleteable <UserInfo>().ExecuteCommand();
db.Deleteable <UserIpRuleInfo>().ExecuteCommand();
db.Insertable(new UserInfo()
{
Id = 1,
Password = "******",
UserName = "******"
}).ExecuteCommand();
db.Insertable(new UserIpRuleInfo()
{
Addtime = DateTime.Now,
UserName = "******",
Id = 11,
UserId = 1,
Description = "xx",
IpRange = "1",
RuleType = 1
}).ExecuteCommand();
var vmList = db.Queryable <UserInfo, UserIpRuleInfo>(
(m1, m2) => m1.Id == m2.UserId
).Where((m1, m2) => m1.Id > 0).Select((m1, m2) => new UserIpRuleInfo()
{
IpRange = m2.IpRange,
Addtime = m2.Addtime,
RuleType = m2.RuleType,
}).ToList();
if (string.IsNullOrEmpty(vmList.First().IpRange))
{
throw new Exception("Queryable");
}
Db.Insertable(new Order()
{
CreateTime = DateTime.Now, CustomId = 1, Name = "a", Price = 1
}).ExecuteCommand();
var sa = Db.SqlQueryable <Order>("SELECT * FroM [ORDER] where id in (@id) ");
sa.AddParameters(new List <SugarParameter>()
{
new SugarParameter("id", new int[] { 1 })
});
int i = 0;
var salist = sa.ToPageList(1, 2, ref i);
db.CodeFirst.InitTables <UnitBytes11>();
db.Insertable(new UnitBytes11()
{
bytes = null, name = "a"
}).ExecuteCommand();
db.Insertable(new UnitBytes11()
{
bytes = new byte[] { 1, 2 }, name = "a"
}).ExecuteCommand();
var bytes = db.Queryable <UnitBytes11>().Select(it => new
{
b = it.bytes,
name = "a"
}).ToList();
var bytes2 = db.Queryable <UnitBytes11>().Select(it => new
{
b = it
}).ToList();
db.CodeFirst.InitTables <BoolTest1>();
db.CodeFirst.InitTables <BoolTest2>();
db.Queryable <BoolTest1>().Where(it => !it.a).ToList();
var test01 = db.Queryable <SaleOrder>().GroupBy(it => new { it.CheckTime.Value.Date })
.Select(it => new { x = it.CheckTime.Value.Date }).ToList();
var q1 = db.Queryable <BoolTest1>();
var x1 = q1.Clone().AS("BoolTest11");
var x2 = q1.Clone().AS("BoolTest12");
var q2 = db.UnionAll(x1, x2).ToSql();
if (!q2.Key.Contains("BoolTest11") || !q2.Key.Contains("BoolTest12"))
{
throw new Exception("unit query error");
}
db.Queryable <Order>().Where(it => SqlFunc.Round(it.Id, 2) == SqlFunc.Abs(it.Id)).ToList();
db.Insertable(new Order()
{
CreateTime = Convert.ToDateTime("2021-1-1"), CustomId = 1, Name = "a", Price = 0
}).ExecuteCommand();
db.Insertable(new Order()
{
CreateTime = Convert.ToDateTime("2021-1-9"), CustomId = 1, Name = "a", Price = 0
}).ExecuteCommand();
db.Insertable(new Order()
{
CreateTime = Convert.ToDateTime("2021-9-11"), CustomId = 1, Name = "a", Price = 0
}).ExecuteCommand();
db.Insertable(new Order()
{
CreateTime = Convert.ToDateTime("2021-11-30"), CustomId = 1, Name = "a", Price = 0
}).ExecuteCommand();
var d1 = db.Queryable <Order>()
.Where(it => it.CreateTime.Day == 1 && it.CreateTime.Year == 2021)
.Select(it => it.CreateTime.ToString("yyyy-MM-dd")).ToList();
Check.Exception(d1.Last() != "2021-01-01", "unit error");
var d11 = db.Queryable <Order>()
.Where(it => it.CreateTime.Day == 9 && it.CreateTime.Year == 2021)
.Select(it => it.CreateTime.ToString("yyyy-MM-dd")).ToList();
Check.Exception(d11.Last() != "2021-01-09", "unit error");
var d111 = db.Queryable <Order>()
.Where(it => it.CreateTime.Day == 11 && it.CreateTime.Year == 2021)
.Select(it => it.CreateTime.ToString("yyyy-MM-dd")).ToList();
Check.Exception(d111.Last() != "2021-09-11", "unit error");
var d1111 = db.Queryable <Order>()
.Where(it => it.CreateTime.Day == 30 && it.CreateTime.Year == 2021)
.Select(it => it.CreateTime.ToString("yyyy-MM-dd")).ToList();
Check.Exception(d1111.Last() != "2021-11-30", "unit error");
var d11111 = db.Queryable <Order>()
.Where(it => it.CreateTime.ToString("yyyy-MM-dd") == "2021-11-30")
.Select(it => it.CreateTime.ToString("yyyy-MM-dd")).ToList();
Check.Exception(d11111.Last() != "2021-11-30", "unit error");
db.CodeFirst.InitTables <UnitEnumadfa>();
db.Insertable(new UnitEnumadfa()).ExecuteCommand();
db.Insertable(new UnitEnumadfa()
{
Type = DbType.Sqlite
}).ExecuteCommand();
var listEnum = db.Queryable <UnitEnumadfa>().ToList();
var d111111 = db.Queryable <Order>()
.Where(it => it.Id == SqlFunc.IF(true).Return(1).End(0))
.ToList();
var d1111111 = db.Queryable <Order>()
.Where(it => it.Id == SqlFunc.IF(it.Id > 0).Return(1).End(0))
.ToList();
var d11111111 = db.Queryable <Order>()
.Where(it => it.Id == (it.Id > 0? (it.Id == 1?11:1):2))
.ToList();
var d111111111 = db.Queryable <Order>()
.Where(it => it.Id == (it.Id > 0 ? (it.Id == 1 ? 11 : (it.Id == 2?2:1)) : 2))
.ToList();
bool?bq = true;
var d1111111111 = db.Queryable <BoolTest1>().Where(it => it.a.Equals(bq.Value)).ToArray();
var d11111111111 = db.Queryable <BoolTest1>().Where(it => SqlFunc.IIF(bq.Value, 1, 2) == 1).ToArray();
var d111111111111 = db.Queryable <BoolTest1>().Select(it => new { x = SqlFunc.IsNull(it.a, false) }).ToArray();
db.CodeFirst.InitTables <SqlSugarDemo.UserEntity, SqlSugarDemo.RoleEntity, SqlSugarDemo.UserRoleEntity>();
var data = new SqlSugarDemo.UserEntity()
{
CardNo = "",
CompanyWX = "",
Credential = "",
EmailAccount = "",
EndDate = DateTime.Now,
FailedLoginPwdCount = 1,
IsChangePassword = true,
IsReal = 1,
LastLoginDate = DateTime.Now,
ManageAccount = Guid.NewGuid(),
ManageOrg = Guid.NewGuid(),
NickName = "",
PhoneAccount = "",
RealName = "",
VerificationLoginPwdDate = DateTime.Now,
SafePhone = "",
Sex = 1,
StartDate = DateTime.Now,
StopLoginTime = DateTime.Now,
UserAccount = "",
UserId = Guid.NewGuid(),
UserType = 1
};
db.Insertable(data).ExecuteCommand();
//var role = new SqlSugarDemo.RoleEntity()
//{
// RoleId=Guid.NewGuid(),
// ManageAccount= Guid.NewGuid(),
// ManageOrg=Guid.NewGuid(),
// OrganizationId=Guid.NewGuid(),
// UnitPrice=1,
// Quantity=1,
// RoleName="",
// RoleType=1,
// SortNum=1
//};
//db.Insertable(role).ExecuteCommand();
//db.Insertable(new SqlSugarDemo.UserRoleEntity()
//{
// RoleId= role.RoleId,
// UserId=data.UserId
//}).ExecuteCommand();
var d1111111111111 = db.Queryable <SqlSugarDemo.UserEntity>()
.Mapper <SqlSugarDemo.UserEntity, SqlSugarDemo.RoleEntity, SqlSugarDemo.UserRoleEntity>(it => ManyToMany.Config(it.UserId, it.RoleId)).InSingle(data.UserId);
}
/// <summary>
/// 历史记录
/// </summary>
/// <param name="requestObject"></param>
/// <param name="currentUser"></param>
/// <returns></returns>
public async Task <ResponseObject <List <HistoryInventory> > > History(HistoryInventoryQuery requestObject, CurrentUser currentUser)
{
List <HistoryInventory> result = new List <HistoryInventory>();
//其他出库
var otherOut = _db.Instance.Queryable <TWMOtherWhDetailDbModel, TWMOtherWhMainDbModel, TBMMaterialFileDbModel>((t1, t2, t3) => new object[] {
JoinType.Inner,
t1.MainId == t2.ID,
JoinType.Inner,
t1.MaterialId == t3.ID
}).Where((t1, t2, t3) => t2.DeleteFlag == false && t2.AuditStatus == 2 && t2.CompanyId == currentUser.CompanyID).Select((t1, t2, t3) => new HistoryInventory
{
ID = t2.ID,
OrderNO = t2.WarehousingOrder,
MaterialId = t1.MaterialId,
BaseUnitId = t3.BaseUnitId,
OperateType = 1,
WarehouseId = t1.WarehouseId,
OpearateDate = t2.WarehousingDate,
TypeName = "其他入库",
InventoryType = t2.WarehousingType,
WarehouseAmount = t1.ActualNumber,
WarehouseUnitId = t3.WarehouseUnitId,
WarehouseRate = t3.WarehouseRate
});
//
var otherIn = _db.Instance.Queryable <TWMOtherWhSendDetailDbModel, TWMOtherWhSendMainDbModel, TBMMaterialFileDbModel>((t1, t2, t3) => new object[] {
JoinType.Inner,
t1.MainId == t2.ID,
JoinType.Inner,
t1.MaterialId == t3.ID
}).Where((t1, t2, t3) => t2.DeleteFlag == false && t2.AuditStatus == 2 && t2.CompanyId == currentUser.CompanyID).Select((t1, t2, t3) => new HistoryInventory
{
ID = t2.ID,
OrderNO = t2.WhSendOrder,
MaterialId = t1.MaterialId,
BaseUnitId = t3.BaseUnitId,
OperateType = 2,
WarehouseId = t1.WarehouseId,
OpearateDate = t2.WhSendDate,
TypeName = "其他出库",
InventoryType = t2.WhSendType,
WarehouseAmount = t1.ActualNumber,
WarehouseUnitId = t3.WarehouseUnitId,
WarehouseRate = t3.WarehouseRate
});
//盘盈入库
var profitDetail = _db.Instance.Queryable <TWMProfitDetailDbModel, TWMProfitMainDbModel, TBMMaterialFileDbModel>((t1, t2, t3) => new object[] {
JoinType.Inner,
t1.MainId == t2.ID,
JoinType.Inner,
t1.MaterialId == t3.ID
}).Where((t1, t2, t3) => t2.DeleteFlag == false && t2.AuditStatus == 2 && t2.CompanyId == currentUser.CompanyID).Select((t1, t2, t3) => new HistoryInventory
{
ID = t2.ID,
OrderNO = t2.WarehousingOrder,
MaterialId = t1.MaterialId,
BaseUnitId = t3.BaseUnitId,
OperateType = 1,
WarehouseId = t1.WarehouseId,
OpearateDate = t2.WarehousingDate,
TypeName = "盘盈入库",
InventoryType = t2.WarehousingType,
WarehouseAmount = t1.ActualNumber,
WarehouseUnitId = t3.WarehouseUnitId,
WarehouseRate = t3.WarehouseRate
});
//盘亏出库
var deficit = _db.Instance.Queryable <TWMDeficitDetailDbModel, TWMDeficitMainDbModel, TBMMaterialFileDbModel>((t1, t2, t3) => new object[] {
JoinType.Inner,
t1.MainId == t2.ID,
JoinType.Inner,
t1.MaterialId == t3.ID
}).Where((t1, t2, t3) => t2.DeleteFlag == false && t2.AuditStatus == 2 && t2.CompanyId == currentUser.CompanyID).Select((t1, t2, t3) => new HistoryInventory
{
ID = t2.ID,
OrderNO = t2.WhSendOrder,
MaterialId = t1.MaterialId,
BaseUnitId = t3.BaseUnitId,
OperateType = 2,
WarehouseId = t1.WarehouseId,
OpearateDate = t2.WhSendDate,
TypeName = "盘亏出库",
InventoryType = t2.WhSendType,
WarehouseAmount = t1.ActualNumber,
WarehouseUnitId = t3.WarehouseUnitId,
WarehouseRate = t3.WarehouseRate
});
//生产入库
var productionIn = _db.Instance.Queryable <TWMProductionWhDetailDbModel, TWMProductionWhMainDbModel, TBMMaterialFileDbModel>((t1, t2, t3) => new object[] {
JoinType.Inner,
t1.MainId == t2.ID,
JoinType.Inner,
t1.MaterialId == t3.ID
}).Where((t1, t2, t3) => t2.DeleteFlag == false && t2.AuditStatus == 2 && t2.CompanyId == currentUser.CompanyID).Select((t1, t2, t3) => new HistoryInventory
{
ID = t2.ID,
OrderNO = t2.WarehousingOrderNo,
MaterialId = t1.MaterialId,
BaseUnitId = t3.BaseUnitId,
OperateType = 1,
WarehouseId = t1.WarehouseId,
OpearateDate = t2.WarehousingDate,
TypeName = "生产入库",
InventoryType = t2.WarehousingType,
WarehouseAmount = t1.ActualNum,
WarehouseUnitId = t3.WarehouseUnitId,
WarehouseRate = t3.WarehouseRate
});
//生产出库
var productionOut = _db.Instance.Queryable <TWMProductionDetailDbModel, TWMProductionMainDbModel, TBMMaterialFileDbModel>((t1, t2, t3) => new object[] {
JoinType.Inner,
t1.MainId == t2.ID,
JoinType.Inner,
t1.MaterialId == t3.ID
}).Where((t1, t2, t3) => t2.DeleteFlag == false && t2.AuditStatus == 2 && t2.CompanyId == currentUser.CompanyID).Select((t1, t2, t3) => new HistoryInventory
{
ID = t2.ID,
OrderNO = t2.WhSendOrder,
MaterialId = t1.MaterialId,
BaseUnitId = t3.BaseUnitId,
OperateType = 2,
WarehouseId = t1.WarehouseId,
OpearateDate = t2.WhSendDate,
TypeName = "生产出库",
InventoryType = t2.WhSendType,
WarehouseAmount = t1.ActualNum,
WarehouseUnitId = t3.WarehouseUnitId,
WarehouseRate = t3.WarehouseRate
});
//销售出库
var saleDetail = _db.Instance.Queryable <TWMSalesDetailDbModel, TWMSalesMainDbModel, TBMMaterialFileDbModel>((t1, t2, t3) => new object[] {
JoinType.Inner,
t1.MainId == t2.ID,
JoinType.Inner,
t1.MaterialId == t3.ID
}).Where((t1, t2, t3) => t2.DeleteFlag == false && t2.AuditStatus == 2 && t2.CompanyId == currentUser.CompanyID).Select((t1, t2, t3) => new HistoryInventory
{
ID = t2.ID,
OrderNO = t2.WhSendOrder,
MaterialId = t1.MaterialId,
BaseUnitId = t3.BaseUnitId,
OperateType = 1,
WarehouseId = t1.WarehouseId,
OpearateDate = t2.WhSendDate,
TypeName = "销售出库",
InventoryType = t2.WhSendType,
WarehouseAmount = t1.ActualNum,
WarehouseUnitId = t3.WarehouseUnitId,
WarehouseRate = t3.WarehouseRate
});
//采购入库
var purchaseIn = _db.Instance.Queryable <TWMPurchaseDetailDbModel, TWMPurchaseMainDbModel, TBMMaterialFileDbModel>((t1, t2, t3) => new object[] {
JoinType.Inner,
t1.MainId == t2.ID,
JoinType.Inner,
t1.MaterialId == t3.ID
}).Where((t1, t2, t3) => t2.DeleteFlag == false && t2.AuditStatus == 2 && t2.CompanyId == currentUser.CompanyID).Select((t1, t2, t3) => new HistoryInventory
{
ID = t2.ID,
OrderNO = t2.WarehousingOrderNo,
MaterialId = t1.MaterialId,
BaseUnitId = t3.BaseUnitId,
OperateType = 1,
WarehouseId = t1.WarehouseId,
OpearateDate = t2.WarehousingDate,
TypeName = "采购入库",
InventoryType = t2.WarehousingType,
WarehouseAmount = t1.ActualNum,
WarehouseUnitId = t3.WarehouseUnitId,
WarehouseRate = t3.WarehouseRate
});
//单位
var unitList = _db.Instance.Queryable <TBMDictionaryDbModel, TBMDictionaryTypeDbModel>(
(t, t0) => new object[]
{
JoinType.Left, t.TypeId == t0.ID,
}).Where((t, t0) => SqlFunc.IsNull(t.DeleteFlag, false) == false && t.CompanyId == currentUser.CompanyID &&
SqlFunc.IsNull(t0.DeleteFlag, false) == false
).ToList();
//出入库数量
var allCountOrgin = _db.Instance.UnionAll(otherOut, otherIn, profitDetail,
deficit, productionIn, productionOut, saleDetail, purchaseIn)
.Where(p => p.MaterialId == requestObject.MaterialId && p.WarehouseId == requestObject.WarehouseId);
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
foreach (var item in requestObject.OrderByConditions)
{
var exp = SqlSugarUtil.GetOrderByLambda <HistoryInventory>(item.Column);
if (exp == null)
{
continue;
}
if (item.Condition.ToLower() != "asc" &&
item.Condition.ToLower() != "desc")
{
continue;
}
allCountOrgin.OrderBy($"{item.Column} {item.Condition}");
}
}
var allCount = allCountOrgin.AS("t101");
int totalNum = -1;
if (requestObject.IsPaging)
{
int skipNum = requestObject.PageSize * (requestObject.PageIndex - 1);
result = await allCount.Skip(skipNum).Take(requestObject.PageSize).ToListAsync();
totalNum = allCount.Count();
}
else
{
result = await allCount.ToListAsync();
}
result.ForEach((x) =>
{
var unit = unitList.Where(p => p.ID == x.WarehouseUnitId).FirstOrDefault();
if (unit != null)
{
x.WarehouseUnitName = unit.DicValue;
}
var basicunit = unitList.Where(p => p.ID == x.BaseUnitId).FirstOrDefault();
if (basicunit != null)
{
x.BaseUnitName = basicunit.DicValue;
}
});
return(ResponseUtil <List <HistoryInventory> > .SuccessResult(result, totalNum));
}
public static void Easy()
{
var db = GetInstance();
var dbTime = db.GetDate();
var getAll = db.Queryable <Student>().Select <object>("*").ToList();
var getAll2 = db.Queryable <Student>().Select(it => it.Name.Substring(0, 4)).ToList();
var getAll22 = db.Queryable <Student>().ToDataTable();
var getAll222 = db.Queryable <Student>().ToJson();
var getAll22222 = db.Queryable <Student>().ToArray();
var getAll2222 = db.Queryable <Student>().OrderBy(it => it.Name.Length).ToJson();
var getAll3 = db.Queryable <Student>().OrderBy(it => new { it.Id, it.Name }).GroupBy(it => new { it.Id, it.Name }).Select <object>("id").ToList();
var getRandomList = db.Queryable <Student>().OrderBy(it => SqlFunc.GetRandom()).ToList();
var getAllOrder = db.Queryable <Student>().OrderBy(it => it.Id).OrderBy(it => it.Name, OrderByType.Desc).ToList();
var getId = db.Queryable <Student>().Select(it => it.Id).ToList();
var getNew = db.Queryable <Student>().Where(it => it.Id == 1).Select(it => new { id = SqlFunc.IIF(it.Id == 0, 1, it.Id), it.Name, it.SchoolId }).ToList();
var getAllNoLock = db.Queryable <Student>().With(SqlWith.NoLock).ToList();
var getByPrimaryKey = db.Queryable <Student>().InSingle(2);
var getSingleOrDefault = db.Queryable <Student>().Where(it => it.Id == 1).Single();
var getFirstOrDefault = db.Queryable <Student>().First();
var getByWhere = db.Queryable <Student>().Where(it => it.Id == 1 || it.Name == "a").ToList();
var getByWhere2 = db.Queryable <Student>().Where(it => it.Id == DateTime.Now.Year).ToList();
var getByFuns = db.Queryable <Student>().Where(it => SqlFunc.IsNullOrEmpty(it.Name)).ToList();
var sum = db.Queryable <Student>().Select(it => it.SchoolId).ToList();
var sum2 = db.Queryable <Student, School>((st, sc) => st.SchoolId == sc.Id).Sum((st, sc) => sc.Id);
var isAny = db.Queryable <Student>().Where(it => it.Id == -1).Any();
var isAny2 = db.Queryable <Student>().Any(it => it.Id == -1);
var count = db.Queryable <Student>().Count(it => it.Id > 0);
var date = db.Queryable <Student>().Where(it => it.CreateTime.Value.Date == DateTime.Now.Date).ToList();
var getListByRename = db.Queryable <School>().AS("Student").ToList();
var in1 = db.Queryable <Student>().In(it => it.Id, new int[] { 1, 2, 3 }).ToList();
var in2 = db.Queryable <Student>().In(new int[] { 1, 2, 3 }).ToList();
int[] array = new int[] { 1, 2 };
var in3 = db.Queryable <Student>().Where(it => SqlFunc.ContainsArray(array, it.Id)).ToList();
var group = db.Queryable <Student>().GroupBy(it => it.Id)
.Having(it => SqlFunc.AggregateCount(it.Id) > 10)
.Select(it => new { id = SqlFunc.AggregateCount(it.Id) }).ToList();
var between = db.Queryable <Student>().Where(it => SqlFunc.Between(it.Id, 1, 20)).ToList();
var getTodayList = db.Queryable <Student>().Where(it => SqlFunc.DateIsSame(it.CreateTime, DateTime.Now)).ToList();
var joinSql = db.Queryable("student", "s").OrderBy("id").Select("id,name").ToPageList(1, 2);
var getDay1List = db.Queryable <Student>().Where(it => it.CreateTime.Value.Hour == 1).ToList();
var getDateAdd = db.Queryable <Student>().Where(it => it.CreateTime.Value.AddDays(1) == DateTime.Now).ToList();
var getDateIsSame = db.Queryable <Student>().Where(it => SqlFunc.DateIsSame(DateTime.Now, DateTime.Now, DateType.Hour)).ToList();
var getSqlList = db.Queryable <Student>().AS("(select * from student) t").ToList();
var getUnionAllList = db.UnionAll(db.Queryable <Student>().Where(it => it.Id == 1), db.Queryable <Student>().Where(it => it.Id == 2)).ToList();
var getUnionAllList2 = db.UnionAll(db.Queryable <Student>(), db.Queryable <Student>()).ToList();
var getUnionAllList3 = db.UnionAll(db.Queryable <Student>()
.Select(it => new Student {
Id = SqlFunc.ToInt32(1), Name = SqlFunc.ToString("2"), SchoolId = Convert.ToInt32(3)
})
, db.Queryable <Student>()
.Select(it => new Student {
Id = SqlFunc.ToInt32(11), Name = SqlFunc.ToString("22"), SchoolId = Convert.ToInt32(33)
}))
.Select(it => new Student()
{
Id = SqlFunc.ToInt32(111), Name = SqlFunc.ToString("222")
}).ToList();
var test1 = db.Queryable <Student, School>((st, sc) => st.SchoolId == sc.Id).Where(st => st.CreateTime > SqlFunc.GetDate()).Select((st, sc) => SqlFunc.ToInt64(sc.Id)).ToList();
var test2 = db.Queryable <Student, School>((st, sc) => st.SchoolId == sc.Id)
.Where(st =>
SqlFunc.IF(st.Id > 1)
.Return(st.Id)
.ElseIF(st.Id == 1)
.Return(st.SchoolId).End(st.Id) == 1).Select(st => st).ToList();
var test3 = db.Queryable <DataTestInfo2>().Select(it => it.Bool1).ToSql();
var test4 = db.Queryable <DataTestInfo2>().Select(it => new { b = it.Bool1 }).ToSql();
DateTime?result = DateTime.Now;
var test5 = db.Queryable <Student>().Where(it => it.CreateTime > result.Value.Date).ToList();
var test6 = db.Queryable <DataTestInfo2>().Where(it => SqlFunc.HasValue(it.Bool2) == true && SqlFunc.HasValue(it.Bool2) == true).ToList();
var test7 = db.Queryable <DataTestInfo2>().Where(it => SqlFunc.HasValue(it.Bool1) && SqlFunc.HasValue(it.Bool1)).ToList();
var test8 = db.Queryable <Student>().Where(it => SqlFunc.HasValue(it.SchoolId) && SqlFunc.HasValue(it.SchoolId)).ToList();
bool?b = false;
var test9 = db.Queryable <DataTestInfo2>().Where(it => it.Bool1 == b).ToList();
var test10 = db.Queryable <Student>(db.Queryable <Student>().Select(it => new Student()
{
Name = it.Name.Substring(0, 1)
})).GroupBy(it => it.Name).ToList();;
var test11 = db.Queryable <Student>().Distinct().ToList();
var test12 = db.Queryable <Student>().Distinct().Select(it => new Student {
Name = it.Name
}).ToList();
var test13 = db.Queryable <Student>().Where(it => DateTime.Parse("2014-1-1") == DateTime.Now).Where(it => Boolean.Parse("true") == true).ToList();
var test14 = db.Queryable <DataTestInfo2>().Where(it => Convert.ToBoolean(it.Bool1)).ToList();
var test15 = db.Queryable <DataTestInfo2>().Where(it => it.Bool2.Value && it.Bool1).ToList();
var test16 = db.Queryable <DataTestInfo2>().Where(it => !it.Bool2.Value && !it.Bool1).ToList();
var test17 = db.Queryable <DataTestInfo2>().Where(it => it.Bool1 && it.Bool1).ToList();
var test18 = db.Queryable <Student>().Where(it => it.SchoolId.HasValue && it.SchoolId.HasValue).ToList();
var test19 = db.Queryable <Student>().Where(it => it.SchoolId.HasValue && it.SchoolId.HasValue && it.SchoolId.HasValue).ToList();
var test20 = db.Queryable <Student>().Where(it => it.SchoolId.HasValue && SqlFunc.IsNullOrEmpty(it.Name)).ToList();
var test21 = db.Queryable <Student>().Where(it => !it.SchoolId.HasValue && it.Name == "").ToList();
var test22 = db.Queryable <Student>().Where(it => !it.SchoolId.HasValue && it.SchoolId.HasValue).ToList();
var test23 = db.Queryable <Student>().Where(it => !(it.Id == 1) && it.Name == "").ToList();
var test24 = db.Queryable <Student>().Where(it => string.IsNullOrEmpty("a")).Where(it => string.IsNullOrEmpty(it.Name)).ToList();
var test25 = db.Queryable <Student>().Where(it => SqlFunc.IIF(it.Id == 0, 1, 2) == 1).ToList();
var test26 = db.Queryable <Student>().Where(it => (it.Name == null?2:3) == 1)
.ToList();
var test27 = db.Queryable <Student>().Select(x => new {
name = x.Name == null?"1":"2"
}).ToList();
var test28 = db.Queryable <Student>().Select(x => new Student {
Name = x.Name == null ? "1" : "2"
}).ToList();
var test29 = db.Queryable <Student>().Where(it => it.Id % 1 == 0).ToList();
var test30 = db.Queryable <Student>().Select(x => new Student
{
Name = x.Name ?? "a"
}).ToList();
var test31 = db.Queryable <Student>().Where(it => (it.Name ?? "a") == "a").ToList();
var test32 = db.Queryable <Student>().Where(it => it.Name == null ? true : false).ToList();
var test33 = db.Queryable <Student>().Where(it => SqlFunc.IIF(it.Name == null, true, false)).ToList();
var test34 = db.Queryable <Student>().Where(it => SqlFunc.IIF(it.Name == null || 1 == 1, true, false)).ToList();
var test35 = db.Queryable <Student>().Where(it => it.Id == 1 && SqlFunc.IF(it.Id == 1).Return(true).End(false)).ToList();
var test36 = db.Queryable <Student>().Where(it => it.Id == 1 && it.SchoolId.HasValue).ToList();
var test37 = db.Queryable <Student>().Where(it => it.Id == 1 && SqlFunc.IIF(it.Id == 1, true, false)).ToList();
var test38 = db.Queryable <Student>().Where(it => it.Id == 1 && SqlFunc.IIF(it.Id == 1, true, false) == true).ToList();
var test39 = db.Queryable <Student>().Where(it => it.Id == 1 && (it.Id == 1?true:false)).ToList();
var test40 = db.Queryable <Student>().Where(it => it.Id == 1 && Convert.ToBoolean("true")).ToList();
var test41 = db.Queryable <Student>().Where(it => it.Id == ((it.Id == 1?2:3) == 2?1:2)).ToList();
var test42 = db.Queryable <Student>().Where(it => new int[] { 1, 2, 3 }.Contains(1)).ToList();
var test43 = db.Queryable <Student>().Where(it => new int[] { 1, 2, 3 }.Contains(it.Id)).ToList();
var test44 = db.Queryable <Student>().Select(it => new {
x = SqlFunc.Subqueryable <DataTestInfo>().Where(x => false).Sum(x => x.Decimal1)
}).ToList();
decimal?p = null;
var test45 = db.Queryable <DataTestInfo>().Select(it => new {
x = p
}).ToList();
var test46 = db.Queryable <Student>().Where(it => it.CreateTime > SqlFunc.ToDate(DateTime.Now.Date)).ToList();
var test47 = db.Queryable <Student>().Where(it => string.IsNullOrEmpty(it.Name) == true).ToList();
var test48 = db.Queryable <Student>().Where(it => it.CreateTime != null).Where(it => SqlFunc.ToDate(it.CreateTime).Date == DateTime.Now.Date).ToList();
var test49 = db.Queryable <Student>().Where(it => it.CreateTime != null).Where(it => SqlFunc.ToDate(it.CreateTime).Year == DateTime.Now.Year).ToList();
var test50 = db.Queryable <Student>().Where(it => it.CreateTime != null).Where(it => SqlFunc.ToDate(it.CreateTime).Year == SqlFunc.GetDate().Year).ToList();
var test51 = db.Queryable <Student>().Select(it => new { x = SqlFunc.ToDate(it.CreateTime).Year + "-" }).ToList();
var test52 = db.Queryable <Student>().Select(it => SqlFunc.IsNull(it.CreateTime, SqlFunc.GetDate())).ToList();
var test53 = db.Queryable <Student>().Select(it => SqlFunc.IsNull(it.CreateTime, SqlFunc.GetDate())).First();
var test54 = db.Queryable <Student>().Where(it => it.CreateTime == test52.First().Value).ToList();
var test55 = db.Queryable <Student>().Select(it => new {
isAny = SqlFunc.Subqueryable <School>().Any()?1:2
}).ToList();
var test56 = db.Queryable <Student>().Select(it => new {
isAny = SqlFunc.Subqueryable <Student>().Any(),
isAny2 = SqlFunc.Subqueryable <Student>().Where(s => false).Any()
}).ToList();
var totalPage = 0;
var total = 0;
db.Queryable <Student>().ToPageList(1, 2, ref total, ref totalPage);
}
private static void Subqueryable()
{
var db = GetInstance();
var i = 0;
var sumflat2num = db.Queryable <Student, Student>((s1, s2) =>
new object[] { JoinType.Left, s1.Id == s2.Id })
.Select((s1, s2) => new Student
{
Id = SqlFunc.IsNull(SqlFunc.AggregateSum(SqlFunc.IIF(s1.Id == 1, s1.Id, s1.Id * -1)), 0)
})
.First();
var getAll11 = db.Queryable <Student>().Where(it => SqlFunc.Subqueryable <School>().Where(s => s.Id == it.Id).Max(s => s.Id) == i).ToList();
var getAll12 = db.Queryable <Student>().Where(it => SqlFunc.Subqueryable <School>().Where(s => s.Id == it.Id).Max(s => s.Id) == 1).ToList();
var getAll7 = db.Queryable <Student>().Where(it => SqlFunc.Subqueryable <School>().Where(s => s.Id == it.Id).Any()).ToList();
var getAll9 = db.Queryable <Student>().Where(it => SqlFunc.Subqueryable <School>().Where(s => s.Id == it.Id).Count() == 1).ToList();
var getAll10 = db.Queryable <Student>().Where(it => SqlFunc.Subqueryable <School>().Where(s => s.Id == it.Id).OrderBy(s => s.Id).Select(s => s.Id) == 1).ToList();
var getAll14 = db.Queryable <Student>().Where(it => SqlFunc.Subqueryable <School>().Where(s => s.Id == it.Id).OrderByDesc(s => s.Id).Select(s => s.Id) == 1).ToList();
var getAll8 = db.Queryable <Student>().Where(it => SqlFunc.Subqueryable <School>().Where(s => s.Id == it.Id).Where(s => s.Name == it.Name).NotAny()).ToList();
var getAll1 = db.Queryable <Student>().Where(it => it.Id == SqlFunc.Subqueryable <School>().Where(s => s.Id == it.Id).Select(s => s.Id)).ToList();
var getAll2 = db.Queryable <Student, School>((st, sc) => new object[] {
JoinType.Left, st.Id == sc.Id
})
.Where(st => st.Id == SqlFunc.Subqueryable <School>().Where(s => s.Id == st.Id).Select(s => s.Id))
.ToList();
var getAll3 = db.Queryable <Student, School>((st, sc) => new object[] {
JoinType.Left, st.Id == sc.Id
})
.Select(st =>
new
{
name = st.Name,
id = SqlFunc.Subqueryable <School>().Where(s => s.Id == st.Id).Select(s => s.Id)
})
.ToList();
var getAll4 = db.Queryable <Student>().Select(it =>
new
{
name = it.Name,
id = SqlFunc.Subqueryable <School>().Where(s => s.Id == it.Id).Select(s => s.Id)
}).ToList();
var getAll5 = db.Queryable <Student>().Select(it =>
new Student
{
Name = it.Name,
Id = SqlFunc.Subqueryable <School>().Where(s => s.Id == it.Id).Select(s => s.Id)
}).ToList();
var getAll6 = db.Queryable <Student>().Select(it =>
new
{
name = it.Name,
id = SqlFunc.Subqueryable <Student>().Where(s => s.Id == it.Id).Sum(s => (int)s.SchoolId)
}).ToList();
var getAll66 = db.Queryable <Student>().Select(it =>
new
{
name = it.Name,
id = SqlFunc.Subqueryable <Student>().Where(s => s.Id == it.Id).Sum(s => s.SchoolId.Value)
}).ToList();
var getAll666 = db.Queryable <Student>().Select(it =>
new
{
name = it.Name,
id = SqlFunc.Subqueryable <Student>().Where(s => s.Id == it.Id).Min(s => s.Id)
}).ToList();
}
/// <summary>
/// 删除配色项目，所有节点
/// </summary>
/// <param name="requestDelete"></param>
/// <returns></returns>
public async Task <ResponseObject <bool> > DeleteAllAsync(RequestDelete <DeleteModel> requestDelete, CurrentUser currentUser)
{
try
{
//如果没有新增数据，返回错误信息
if (requestDelete.PostData == null)
{
return(ResponseUtil <bool> .FailResult(false, "PostData至少包含一条数据"));
}
var toDelteModel = _db.Instance.Queryable <TMMColorItemDbModel> ()
.Where(p => p.PackageId == requestDelete.PostData.ID).Select(p => p).ToList();
var dic = _db.Instance.Queryable <TBMDictionaryDbModel, TBMDictionaryTypeDbModel>((t, t1) => new object[] {
JoinType.Inner, t.TypeId == t1.ID
}).Where((t, t1) => t1.TypeName == "配色方案" && t1.CompanyId == currentUser.CompanyID).ToList();
var packageList = _db.Instance.Queryable <TBMPackageDbModel>().Where(p => p.CompanyId == currentUser.CompanyID).ToList();
foreach (var itemDel in toDelteModel)
{
if (_db.Instance.Queryable <TMMBOMMainDbModel, TMMBOMDetailDbModel>((t, t1) => new object[] { JoinType.Inner, t.ID == t1.MainId })
.Any((t, t1) => t.PackageId == itemDel.PackageId && t1.ItemId == itemDel.ItemId)) //此包型的BOM是否包含被删除的配色项目
{
string itemName = dic.Where(p => p.ID == itemDel.ItemId).FirstOrDefault()?.DicValue;
string packageName = packageList.Where(p => p.ID == itemDel.PackageId).FirstOrDefault()?.DicValue;
bool isExis = _db.Instance.Queryable <TMMProductionOrderDetailDbModel, TMMProductionOrderMainDbModel>((t, t1) => new object[] {
JoinType.Inner, t.MainId == t1.ID
}).Any((t, t1) => t1.DeleteFlag == false &&
SqlFunc.IsNull(t1.MRPStatus, false) == false && t1.AuditStatus == 2 &&
t.PackageId == itemDel.PackageId
); // 是否存在没有算料的生产单
if (isExis)
{
throw new Exception($"已审核通过的生产单，正在使用包型'{packageName}'Bom的配色项目{itemName},请将生产单算料，或者删除包型Bom的配色项目'{itemName}'");
}
//删除此包型所有的配色项目
var deleteColorSolution = _db.Instance.Deleteable <TMMColorSolutionDetailDbModel>().Where(p => p.ItemId == itemDel.ItemId &&
SqlFunc.Subqueryable <TMMColorSolutionMainDbModel>().Where(p1 => p1.ID == p.MainId && p1.PackageId == itemDel.PackageId).Any());
//删除BOM
_db.Instance.Deleteable <TMMBOMDetailDbModel>().Where(p => p.ItemId == itemDel.ItemId && SqlFunc.Subqueryable <TMMBOMMainDbModel>().
Where(p1 => p1.ID == p.MainId && p1.PackageId == itemDel.PackageId).Any());
}
}
var result = await _db.Instance.Deleteable <TMMColorItemDbModel>()
.Where(p => p.PackageId == requestDelete.PostData.ID)
.ExecuteCommandAsync() > 0;
//返回执行结果
return(result ? ResponseUtil <bool> .SuccessResult(true) : ResponseUtil <bool> .FailResult(false, "新增数据失败!"));
}
catch (Exception ex)
{
//返回异常结果
return(ResponseUtil <bool> .FailResult(false, ex.Message));
}
}
/// <summary>
/// 修改T_BM_CustomerFile数据
/// </summary>
/// <param name="requestObject">Put请求参数</param>
/// <returns>返回响应结果对象，包括响应代码，修改操作结果</returns>
public async Task <ResponseObject <bool> > PutAsync(RequestPut <TBMCustomerFileEditModel> requestObject, CurrentUser current)
{
var currDb = _db.Instance;//事务需要使用同一个 SqlSugarClient对象实例
try
{
if (requestObject.PostData == null)
{
return(ResponseUtil <bool> .FailResult(false, "PostData不能为null"));
}
//开启事务
currDb.BeginTran();
//修改主表信息
var mainModel = _mapper.Map <TBMCustomerFileDbModel>(requestObject.PostData);
if (_db.Instance.Queryable <TBMCustomerFileDbModel>().Any(p => p.CustomerCode == requestObject.PostData.CustomerCode &&
p.CompanyId == current.CompanyID && p.ID != requestObject.PostData.ID && SqlFunc.IsNull(p.DeleteFlag, false) == false))
{
throw new Exception("编码重复");
}
var mainFlag = await currDb.Updateable(mainModel).IgnoreColumns(p => new { p.CompanyId, p.DeleteFlag }).ExecuteCommandAsync() > 0;
/*
* 修改明细逻辑
* 1.根据主单ID查询现有明细数据
* 2.PostData.ChildList中明细ID <= 0的新增
* 3.PostData.ChildList中明细ID > 0的修改
* 4.删除不在PostData.CihldList中的数据
*/
var detailFlag = true;
var detailModels = _mapper.Map <List <TBMCustomerContactEditModel>,
List <TBMCustomerContactDbModel> >(requestObject.PostData.ChildList);
foreach (var item in detailModels)
{
if (!detailFlag)
{
break;
}
item.CustomerId = mainModel.ID;
//新增或修改明细数据
detailFlag = item.ID <= 0
? await currDb.Insertable(item).ExecuteCommandIdentityIntoEntityAsync()
: await currDb.Updateable(item).ExecuteCommandAsync() > 0;
}
//删除明细数据
if (detailFlag)
{
if (requestObject.PostData.ChildList == null || requestObject.PostData.ChildList.Count() == 0)
{
detailFlag = currDb.Deleteable <TBMCustomerContactDbModel>()
.Where(p => p.CustomerId == mainModel.ID)
.ExecuteCommand() >= 0;
}
else
{
var detailIds = detailModels.Select(p => p.ID).ToList();
detailFlag = currDb.Deleteable <TBMCustomerContactDbModel>()
.Where(p => !detailIds.Contains(p.ID) && p.CustomerId == mainModel.ID)
.ExecuteCommand() >= 0;
}
}
//提交事务
currDb.CommitTran();
//返回执行结果
return(mainFlag && detailFlag ? ResponseUtil <bool> .SuccessResult(true) : ResponseUtil <bool> .FailResult(false, "修改数据失败!"));
}
catch (Exception ex)
{
//回滚事务
currDb.RollbackTran();
//返回异常结果
return(ResponseUtil <bool> .FailResult(false, ex.Message));
}
}
/// <summary>
/// 新增T_BM_WarehouseFile数据
/// </summary>
/// <param name="requestObject">返回响应结果对象，包括响应代码，新增操作结果</param>
/// <param name="UserID">操作人ID</param>
/// <returns></returns>
public async Task <ResponseObject <TBMWarehouseFileAddModel, bool> > PostAsync(RequestObject <TBMWarehouseFileAddModel> requestObject, int UserID)
{
try
{
var curentDB = _db.Instance;
SMUserInfo sMUserInfo = SMCurentUserManager.GetCurentUserID(UserID, curentDB);
//如果没有新增数据，返回错误信息
if (requestObject.PostData == null && requestObject.PostDataList == null)
{
return(ResponseUtil <TBMWarehouseFileAddModel, bool> .FailResult(requestObject, false, "PostData,PostDataList不能都为null"));
}
var result = false;
//批量新增的优先级高于单条数据新增，且只会执行一个新增操作
if (requestObject.PostDataList != null && requestObject.PostDataList.Count > 0)
{
var addList = _mapper.Map <List <TBMWarehouseFileAddModel>, List <TBMWarehouseFileDbModel> >(requestObject.PostDataList);
foreach (var item in addList)
{
item.CompanyId = sMUserInfo.CompanyId.Value;
}
result = await curentDB.Insertable(addList).ExecuteCommandAsync() > 0;
}
else
{
var addModel = _mapper.Map <TBMWarehouseFileDbModel>(requestObject.PostData);
addModel.CompanyId = sMUserInfo.CompanyId.Value;
addModel.DeleteFlag = false;
var oldModel = curentDB.Queryable <TBMWarehouseFileDbModel>().Where(p => SqlFunc.IsNull(p.DeleteFlag, false) == false && p.WarehouseName == addModel.WarehouseName &&
p.CompanyId == sMUserInfo.CompanyId.Value).First();
if (oldModel != null)
{
return(ResponseUtil <TBMWarehouseFileAddModel, bool> .FailResult(requestObject, false, addModel.WarehouseName + " 已经存在"));
}
var oldModelCode = curentDB.Queryable <TBMWarehouseFileDbModel>().Where(p => SqlFunc.IsNull(p.DeleteFlag, false) == false && p.Code == addModel.Code &&
p.CompanyId == sMUserInfo.CompanyId.Value).First();
if (oldModelCode != null)
{
return(ResponseUtil <TBMWarehouseFileAddModel, bool> .FailResult(requestObject, false, "编号:" + addModel.Code + " 已经存在"));
}
addModel.CompanyId = sMUserInfo.CompanyId.Value;
result = await _db.Instance.Insertable(addModel).ExecuteCommandAsync() > 0;
}
//返回执行结果
if (result)
{
return(ResponseUtil <TBMWarehouseFileAddModel, bool> .SuccessResult(requestObject, true));
}
return(ResponseUtil <TBMWarehouseFileAddModel, bool> .FailResult(requestObject, false, "新增数据失败!"));
}
catch (Exception ex)
{
//返回异常结果
return(ResponseUtil <TBMWarehouseFileAddModel, bool> .FailResult(requestObject, false, ex.Message));
}
}
/// <summary>
/// 获取生产单对应的EXCEL数据
/// </summary>
/// <param name="orderID"></param>
/// <param name="currentUser"></param>
/// <returns></returns>
public AllBomExcel GetProductDataForExcel(int orderID, CurrentUser currentUser)
{
AllBomExcel allBomExcel = new AllBomExcel();
#region 生产单
var query = _db.Instance.Queryable <TMMProductionOrderBOMDbModel,
TMMColorSolutionMainDbModel, TBMMaterialFileDbModel, TBMDictionaryDbModel, TBMPackageDbModel, TBMDictionaryDbModel,
TBMDictionaryDbModel>((t, t1, t2, t3, t4, t5, t6) => new object[] {
JoinType.Left, t.ColorSolutionId == t1.ID,
JoinType.Inner, t.MaterialId == t2.ID,
JoinType.Inner, t2.ColorId == t3.ID,
JoinType.Inner, t4.ID == t.PackageId,
JoinType.Left, t5.ID == t.PartId,
JoinType.Left, t6.ID == t.ItemId
}).
Where((t, t1, t2, t3, t4, t5, t6) => t.ProOrderId == orderID).Select((t, t1, t2, t3, t4, t5, t6) => new TMMProductionOrderBOMQueryModel()
{
ID = t.ID,
ProOrderId = t.ProOrderId,
MaterialId = t.MaterialId,
ColorSolutionCode = SqlFunc.IsNull(t1.SolutionCode, "无配色"),
MaterialName = t2.MaterialName,
SingleValue = t.SingleValue,
PartId = t.PartId,
PartName = t5.DicValue,
ProductionNum = t.ProductionNum,
ColorId = t2.ColorId,
PackageId = t.PackageId,
ColorName = SqlFunc.IsNull(t3.DicValue, "无色"),
PackageName = t4.DicValue,
ItemId = t.ItemId,
ItemName = t6.DicValue
}).ToList();
allBomExcel.ProductionOrderBOMQueryModelList = query;
#endregion
//bom
var PackageColor = _db.Instance.Queryable <TMMProductionOrderDetailDbModel, TMMProductionOrderMainDbModel, TBMPackageDbModel>((t, t1, t2) => new object[] {
JoinType.Inner, t.MainId == t1.ID,
JoinType.Inner, t.PackageId == t2.ID
}).Where((t, t1, t2) => t.ID == orderID).Select((t, t1, t2) => new { PackageId = t.PackageId, ColorSolutionId = t.ColorSolutionId, ProductionNum = t.ProductionNum, PackageName = t2.DicValue }).ToList();
var PackageList = PackageColor.Where(p => p.ColorSolutionId != null).Select(p => p.PackageId).ToList().Distinct(); //有配色方案的包型
var NoPackageList = PackageColor.Where(p => p.ColorSolutionId == null).Select(p => p.PackageId).ToList().Distinct(); //无配色方案的包型
var ColorSolutionIdList = PackageColor.Where(p => p.ColorSolutionId != null).Select(p => new { p.PackageId, p.ColorSolutionId }).ToList().Distinct();
//有配色的Bom
var PackBom = _db.Instance.Queryable <TMMBOMDetailDbModel, TBMDictionaryDbModel, TMMFormulaDbModel, TMMPackageColorItemDbModel, TMMBOMMainDbModel>((t, t1, t2, t3, t4) => new object[] {
JoinType.Left, t.PartId == t1.ID,
JoinType.Left, t.Formula == t2.ID.ToString(),
JoinType.Inner, t.ItemId == t3.ID,
JoinType.Inner, t.MainId == t4.ID
}).Where((t, t1, t2, t3, t4) => PackageList.Contains(t4.PackageId));
PackBom.OrderBy($"t3.ItemName asc");
var queryData = PackBom.Select((t, t1, t2, t3, t4) => new TMMBOMDetailQueryExcelModel
{
ID = t.ID,
MainId = t.MainId,
ItemId = t.ItemId,
ItemName = t3.ItemName,
MaterialName = t.MaterialName,
PartId = t.PartId,
PartName = t1.DicValue,
LengthValue = t.LengthValue,
WidthValue = t.WidthValue,
NumValue = t.NumValue,
WideValue = t.WideValue,
LossValue = t.LossValue,
SingleValue = t.SingleValue,
Formula = t.Formula,
FormulaName = t2.FormulaName,
PackageId = t4.PackageId
}).ToList();
allBomExcel.TMMBOMDetailQueryExcelList = queryData;
List <PackageColorExcelModel> PackageColorExcelModelList = new List <PackageColorExcelModel>();
foreach (var packageItem in PackageList)
{
var colorIdList = ColorSolutionIdList.Where(p => p.PackageId == packageItem).Select(p => p.PackageId).ToList();
var detailList = _db.Instance.Queryable <TMMColorSolutionDetailDbModel, TMMPackageColorItemDbModel, TBMDictionaryDbModel, TMMColorSolutionMainDbModel>(
(t, t0, t1, t2) => new object[]
{
JoinType.Left, t.ItemId == t0.ID,
JoinType.Left, t.ColorId == t1.ID,
JoinType.Inner, t.MainId == t2.ID
}
).Where((t, t0, t1, t2) => colorIdList.Contains(t2.ID)).Select((t, t0, t1, t2) => new TMMColorSolutionDetailQueryExcelModel
{
ID = t.ID,
MainId = t.MainId,
ItemId = t.ItemId,
ItemName = t0.ItemName,
ColorId = t.ColorId,
ColorName = t1.DicValue,
SolutionCode = t2.SolutionCode
}).ToList();
PackageColorExcelModel packageColorExcelModel = new PackageColorExcelModel();
packageColorExcelModel.PackageId = packageItem;
var dt = new DataTable();
dt.Columns.Add($"数量");
foreach (var item in detailList.Select(p => new { p.MainId, p.SolutionCode }).Distinct())
{
var num = PackageColor.Where(p => p.ColorSolutionId == item.MainId).Sum(p => p.ProductionNum);
//添加动态列
foreach (var item2 in detailList.Where(p => p.MainId == item.MainId))
{
if (!dt.Columns.Contains($"{item2.ItemName}"))
{
dt.Columns.Add($"{item2.ItemName}");
}
var newRow = dt.NewRow();
newRow[$"{item2.ItemName}"] = item2.ColorId;
newRow["数量"] = num;
dt.Rows.Add(newRow);
}
}
packageColorExcelModel.dt = dt;
PackageColorExcelModelList.Add(packageColorExcelModel);
}
allBomExcel.PackageColorExcelModelList = PackageColorExcelModelList;
return(allBomExcel);
}
/// <summary>
/// 修改T_BM_WarehouseFile数据
/// </summary>
/// <param name="requestObject">返回响应结果对象，包括响应代码，修改操作结果</param>
/// <param name="userID"></param>
/// <returns></returns>
public async Task <ResponseObject <TBMWarehouseFileEditModel, bool> > PutAsync(RequestObject <TBMWarehouseFileEditModel> requestObject, int userID)
{
try
{
var curentDB = _db.Instance;
SMUserInfo sMUserInfo = SMCurentUserManager.GetCurentUserID(userID, curentDB);
//执行结果
var result = false;
//没有修改信息，返回错误信息
if (requestObject.PostDataList == null && requestObject.PostData == null)
{
return(ResponseUtil <TBMWarehouseFileEditModel, bool> .FailResult(requestObject, false, "PostData不能都为null"));
}
//批量更新优先级高于单记录更新
if (requestObject.PostDataList != null && requestObject.PostDataList.Count > 0)
{
//批量更新
var editList = _mapper.Map <List <TBMWarehouseFileEditModel>, List <TBMWarehouseFileDbModel> >(requestObject.PostDataList);
result = await _db.Instance.Updateable <TBMWarehouseFileDbModel>(editList).IgnoreColumns(p => new { p.DeleteFlag, p.Code, p.CompanyId }).ExecuteCommandAsync() > 0;
}
else
{
//单记录更新
var editModel = _mapper.Map <TBMWarehouseFileDbModel>(requestObject.PostData);
var oldModel = _db.Instance.Queryable <TBMWarehouseFileDbModel>().Where(p => SqlFunc.IsNull(p.DeleteFlag, false) == false &&
p.WarehouseName == editModel.WarehouseName &&
p.CompanyId == sMUserInfo.CompanyId.Value &&
p.ID != editModel.ID
).First();
if (oldModel != null)
{
return(ResponseUtil <TBMWarehouseFileEditModel, bool> .FailResult(requestObject, false, editModel.WarehouseName + " 已经存在"));
}
result = await _db.Instance.Updateable(editModel).IgnoreColumns(p => new { p.DeleteFlag, p.Code, p.CompanyId }).ExecuteCommandAsync() > 0;
}
//返回执行结果
if (result)
{
return(ResponseUtil <TBMWarehouseFileEditModel, bool> .SuccessResult(requestObject, true));
}
return(ResponseUtil <TBMWarehouseFileEditModel, bool> .FailResult(requestObject, false, "修改数据失败!"));
}
catch (Exception ex)
{
//返回异常结果
return(ResponseUtil <TBMWarehouseFileEditModel, bool> .FailResult(requestObject, false, ex.Message));
}
}
private MrpResultModel GetProcuctBomByOrderID(int orderID, CurrentUser currentUser)
{
MrpResultModel result = new MrpResultModel();
#region 明细
List <TMMProductionOrderBOMQueryModel> deatail = new List <TMMProductionOrderBOMQueryModel>();
List <TBMMaterialFileCacheModel> MMaterialList = BasicCacheGet.GetMaterial(currentUser);
var query = _db.Instance.Queryable <TMMProductionOrderBOMDbModel,
TMMColorSolutionMainDbModel, TBMMaterialFileDbModel, TBMDictionaryDbModel, TBMPackageDbModel>((t, t1, t2, t3, t4) => new object[] {
JoinType.Left, t.ColorSolutionId == t1.ID,
JoinType.Inner, t.MaterialId == t2.ID,
JoinType.Inner, t2.ColorId == t3.ID,
JoinType.Inner, t4.ID == t.PackageId
}).Where((t, t1, t2, t3, t4) => t.ProOrderId == orderID).Select((t, t1, t2, t3, t4) => new TMMProductionOrderBOMQueryModel()
{
ID = t.ID,
ProOrderId = t.ProOrderId,
MaterialId = t.MaterialId,
ColorSolutionCode = SqlFunc.IsNull(t1.SolutionCode, "无配色"),
MaterialName = t2.MaterialName,
SingleValue = t.SingleValue,
ProductionNum = t.ProductionNum,
ColorId = t2.ColorId,
PackageId = t.PackageId,
ColorName = SqlFunc.IsNull(t3.DicValue, "无色"),
PackageName = t4.DicValue
});
List <TMMProductionOrderBOMQueryModel> bomList = query.ToList();
var packBomGroup = bomList.GroupBy(p => new { p.PackageName, p.PackageId });
foreach (var item in packBomGroup)
{
var PackageName = item.Key.PackageName;
var packageId = item.Key.PackageId;
var itemDeatails = item.ToList();
var MaterialGroup = itemDeatails.GroupBy(p => new { p.MaterialName, p.MaterialId, p.ColorName, p.ColorId });
foreach (var itemChild in MaterialGroup)
{
var MaterialName = itemChild.Key.MaterialName;
var materialID = itemChild.Key.MaterialId;
var ColorName = itemChild.Key.ColorName;
var colorId = itemChild.Key.ColorId;
var singleGroup = itemChild.ToList().GroupBy(p => p.SingleValue);
foreach (var itemChildChild in singleGroup)
{
var single = itemChildChild.Key;
var list = itemChildChild.ToList();
TMMProductionOrderBOMQueryModel model = new TMMProductionOrderBOMQueryModel();
model.PackageId = packageId;
model.MaterialId = materialID;
model.MaterialName = MaterialName;
model.PackageName = PackageName;
model.ColorName = ColorName;
model.ColorId = colorId;
model.SingleValue = single;
model.ProductionNum = list.Sum(p => p.ProductionNum);
model.ColorSolutionCode = string.Join(",", list.Select(p => p.ColorSolutionCode).Distinct());
deatail.Add(model);
}
}
}
deatail.ForEach((x) =>
{
TBMMaterialFileCacheModel item = MMaterialList.Where(p => p.ID == x.MaterialId).FirstOrDefault();
if (item != null)
{
x.MaterialName = item.MaterialName;
x.MaterialCode = item.MaterialCode;
x.BaseUnitId = item.BaseUnitId;
x.BaseUnitName = item.BaseUnitName;
x.ProduceUnitId = item.ProduceUnitId;
x.ProduceUnitName = item.ProduceUnitName;
}
});
#endregion
List <TMMProductionOrderBOMSumQueryModel> bomListSummary = _db.Instance.Queryable <TMMProductionOrderBOMSumDbModel>().
Where((t) => t.ProOrderId == orderID).Select((t) => new TMMProductionOrderBOMSumQueryModel()
{
ID = t.ID,
ProOrderId = t.ProOrderId,
MaterialId = t.MaterialId,
ColorSolutionCode = t.ColorSolutionCode,
TotalValue = t.TotalValue,
PurchaseNum = t.PurchaseNum,
PurchaseTransNum = t.PurchaseTransNum,
PickNum = t.PickNum,
PickTransNum = t.PickTransNum,
PickTotalNum = t.PickTotalNum,
}).ToList();
bomListSummary.ForEach((x) =>
{
TBMMaterialFileCacheModel item = MMaterialList.Where(p => p.ID == x.MaterialId).FirstOrDefault();
if (item != null)
{
x.MaterialName = item.MaterialName;
x.MaterialCode = item.MaterialCode;
x.BaseUnitId = item.BaseUnitId;
x.BaseUnitName = item.BaseUnitName;
x.ProduceUnitId = item.ProduceUnitId;
x.ProduceUnitName = item.ProduceUnitName;
}
});
result.deatails = deatail;
result.summary = bomListSummary;
return(result);
}
/// <summary>
/// 获取T_BM_WarehouseFile数据
/// </summary>
/// <param name="requestObject">返回响应结果对象，包括响应代码，查询操作结果</param>
/// <returns></returns>
public async Task <ResponseObject <TBMWarehouseFileQueryModel, List <TBMWarehouseFileQueryModel> > > GetAsync(RequestObject <TBMWarehouseFileQueryModel> requestObject, int UserID)
{
try
{
var curentDB = _db.Instance;
var smUserInfo = SMCurentUserManager.GetCurentUserID(UserID, curentDB);
List <TBMWarehouseFileQueryModel> queryData = null; //查询结果集对象
RefAsync <int> totalNumber = -1; //总记录数
var query = curentDB.Queryable <TBMWarehouseFileDbModel, TSMUserAccountDbModel, TSMUserInfoDbModel>(
(t, t0, t1) => new object[]
{
JoinType.Left, t.PrincipalId == t0.ID,
JoinType.Left, t0.UserInfoId == t1.ID
}).Where((t, t0, t1) => SqlFunc.IsNull(t.DeleteFlag, false) != true && t.CompanyId == smUserInfo.CompanyId);
//查询条件
if (requestObject.QueryConditions != null && requestObject.QueryConditions.Count > 0)
{
var conditionals = SqlSugarUtil.GetConditionalModels(requestObject.QueryConditions);
foreach (ConditionalModel item in conditionals)
{
item.FieldName = $"t.{item.FieldName}";
}
query.Where(conditionals);
}
//排序条件
if (requestObject.OrderByConditions != null && requestObject.OrderByConditions.Count > 0)
{
foreach (var item in requestObject.OrderByConditions)
{
var exp = SqlSugarUtil.GetOrderByLambda <TBMWarehouseFileDbModel>(item.Column);
if (exp == null)
{
continue;
}
if (item.Condition.ToLower() != "asc" &&
item.Condition.ToLower() != "desc")
{
continue;
}
query.OrderBy($"{item.Column} {item.Condition}");
}
}
//执行查询
if (requestObject.IsPaging)
{
queryData = await query.Select((t, t0, t1) => new TBMWarehouseFileQueryModel
{
ID = t.ID,
Code = t.Code,
WarehouseName = t.WarehouseName,
WarehouseAddress = t.WarehouseAddress,
PrincipalId = t.PrincipalId,
RealName = t.PrincipalId == null ? "" : t0.AccountName,
Status = t.Status,
Remark = t.Remark,
CompanyId = t.CompanyId,
DeleteFlag = t.DeleteFlag,
})
.ToPageListAsync(requestObject.PageIndex, requestObject.PageSize, totalNumber);
}
else
{
queryData = await query.Select((t, t0, t1) => new TBMWarehouseFileQueryModel
{
ID = t.ID,
Code = t.Code,
WarehouseName = t.WarehouseName,
WarehouseAddress = t.WarehouseAddress,
PrincipalId = t.PrincipalId,
RealName = t.PrincipalId == null ? "" : t0.AccountName,
Status = t.Status,
Remark = t.Remark,
CompanyId = t.CompanyId,
DeleteFlag = t.DeleteFlag,
})
.ToListAsync();
}
//返回执行结果
return(ResponseUtil <TBMWarehouseFileQueryModel, List <TBMWarehouseFileQueryModel> > .SuccessResult(requestObject, queryData, totalNumber));
}
catch (Exception ex)
{
//返回查询异常结果
return(ResponseUtil <TBMWarehouseFileQueryModel, List <TBMWarehouseFileQueryModel> > .FailResult(requestObject, null, ex.Message));
}
}