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