public void Init()
{
var communityId = "";
var buildId = "";
var unitId = "";
var keyword = "";
GetInstance().CodeFirst.InitTables(typeof(MainTable), typeof(SubTable), typeof(Brand), typeof(VendorAndBrand));
GetInstance().Queryable <MainTable>().Where(u =>
(u.CommunityID == communityId || SqlFunc.IsNullOrEmpty(communityId)) &&
(SqlFunc.Contains(u.BuildID, buildId) || SqlFunc.IsNullOrEmpty(buildId)) &&
(SqlFunc.Contains(u.UnitID, unitId) || SqlFunc.IsNullOrEmpty(unitId)) &&
(SqlFunc.Contains(u.RoomNumber, keyword) || SqlFunc.Contains(u.RoomerName, keyword) ||
SqlFunc.Contains(u.HousePlace, keyword) || SqlFunc.Contains(u.UnitName, keyword) ||
SqlFunc.Contains(u.BuildName, keyword) || SqlFunc.IsNullOrEmpty(keyword)))
.GroupBy(ru => new { ru.RoomNumber, ru.RoomID })
.Select(ru => new
{
RoomNumber = SqlFunc.AggregateMax(ru.RoomNumber),
CountRoomer = SqlFunc.AggregateCount(ru.RoomerName),
RoomID = SqlFunc.AggregateMax(ru.RoomID),
Owner = SqlFunc.Subqueryable <SubTable>().Where(r => r.RoomID == ru.RoomID && SqlFunc.Equals(r.RoomUserType, "业主") && SqlFunc.Equals(r.RoomUserType, "业主")).Select(s => s.RoomerName)
}).OrderBy((r) => r.RoomNumber, type: OrderByType.Desc).ToPageListAsync(1, 2).Wait();
GetInstance().Updateable <Student>().UpdateColumns(it =>
new Student()
{
Name = "a".ToString(),
CreateTime = DateTime.Now.AddDays(-1)
}
).Where(it => it.Id == 1).ExecuteCommand();
var list = GetInstance().Queryable <Student, School>((st, sc) => new object[] {
JoinType.Left, st.SchoolId == sc.Id && st.CreateTime == DateTime.Now.AddDays(-1)
})
.Where(st => st.Name == "jack").ToList();
GetInstance().Updateable <BugStudent>().Where(it => true).UpdateColumns(it => new BugStudent()
{
Float = 11
}).ExecuteCommand();
var reslut = GetInstance().Queryable <BugStudent>().ToList();
var list2 = GetInstance().Queryable <Brand, VendorAndBrand>((b, vb) => new object[] {
JoinType.Left, b.Id == vb.BrandId
})
.Where((b) => b.BrandType == 1).Select((b) => b).ToList();
var list3 = GetInstance().Queryable <Brand, VendorAndBrand>((b, vb) =>
b.Id == vb.BrandId)
.Where((b) => b.BrandType == 1).Select((b) => b).ToList();
var query = GetInstance().Queryable <Student>().Select(o => o);
var result = query.ToList();
}
/// <summary>
/// 统计主播的 工时收益
/// </summary>
public bool StatisticsAnchorWorkHourIncome(DateTime startTime, DateTime endTime)
{
using (var db = GetSqlSugarDB(DbConnType.QPAgentAnchorDB))
{
try
{
db.Ado.BeginTran();
var list = db.Queryable <SysAnchorRebateEntity, SysAnchorLiveRecordEntity>((it, st) => new object[] { JoinType.Left, it.AnchorID == st.aid })
.Where((it, st) => it.IsWorkHours == 1 && st.ontime >= startTime && st.ontime < endTime && st.status == 1)
.GroupBy((it, st) => new { it.AnchorID, it.LiveTime, it.Salary, it.HourRebate, it.parentID, it.GiftAmount })
.Having((it, st) => SqlFunc.AggregateSum(st.livetime) >= it.LiveTime * 60 &&
SqlFunc.Subqueryable <TipEntity>().Where(gt => gt.sendtime >= startTime && gt.sendtime < SqlFunc.AggregateMax(st.uptime).AddMinutes(3)).Where(gt => gt.AnchorID == it.AnchorID).Sum(gt => gt.totalamount) >= it.GiftAmount)
.Select((it, st) => new SysTipIncomeDetailEntity
{
ShopID = 0,
UserID = it.parentID,
AnchorID = it.AnchorID,
StartDate = startTime,
UserRebate = it.HourRebate,
CreateTime = startTime,
TipType = TipTypeEnum.其他,
IncomeType = IncomeTypeEnum.工时,
AnchorIncome = it.Salary * (100 - it.HourRebate) / 100,
UserIncome = it.Salary * it.HourRebate / 100
})
.ToList();
if (list.Count == 0)
{
return(true);
}
//批量插入工时收益明细
db.Insertable(list).ExecuteCommand();
List <int> anchorIDList = list.Select(it => it.AnchorID).ToList();
//更新工时记录 无效字段
db.Updateable <SysAnchorLiveRecordEntity>().SetColumns(it => new SysAnchorLiveRecordEntity {
status = 0
})
.Where(it => anchorIDList.Contains(it.aid) && it.ontime >= startTime && it.ontime < endTime)
.ExecuteCommand();
// 处理总收益报表
var incomeList = db.Queryable <SysIncomeEntity>().Where(it => it.opdate == startTime.Date).ToList();
var updateIncomeList = new List <SysIncomeEntity>(); //更新集合
var addIncomeList = new List <SysIncomeEntity>(); //新增集合
list.ForEach(it =>
{
//判读对应日期部分是否有该主播数据 有就更新
var updateModel = incomeList.Where(st => st.AnchorID == it.AnchorID).FirstOrDefault();
if (updateModel != null)//存在
{
updateModel.hour_income = it.AnchorIncome;
updateModel.agentHour_income = it.UserIncome;
updateIncomeList.Add(updateModel);
}
else
{
addIncomeList.Add(new SysIncomeEntity
{
AnchorID = it.AnchorID,
opdate = startTime.Date,
hour_income = it.AnchorIncome,
agentHour_income = it.UserIncome
});
}
});
if (addIncomeList.Count > 0)
{
db.Insertable(addIncomeList).ExecuteCommand();
}
if (updateIncomeList.Count > 0)
{
db.Updateable(updateIncomeList).UpdateColumns(it => new { it.hour_income, it.agentHour_income }).ExecuteCommand();
}
//更新代理余额
var agentBalance = list.GroupBy(s => new { s.UserID }).Select(group => new SysUser
{
Id = group.Key.UserID,
Balance = group.Sum(p => p.UserIncome),
}).ToList();
agentBalance.ForEach(it =>
{
db.Updateable <SysUser>().SetColumns(gt => new SysUser {
Balance = gt.Balance + it.Balance
}).Where(gt => gt.Id == it.Id).ExecuteCommand();
});
//更新主播余额
var anchorBalance = list.GroupBy(s => new { s.AnchorID }).Select(group => new SysAnchorInfoEntity
{
aid = group.Key.AnchorID,
agentGold = group.Sum(p => p.AnchorIncome),
}).ToList();
anchorBalance.ForEach(it =>
{
db.Updateable <SysAnchorInfoEntity>().SetColumns(gt => new SysAnchorInfoEntity {
agentGold = gt.agentGold + it.agentGold
})
.Where(gt => gt.aid == it.aid).ExecuteCommand();
});
db.Ado.CommitTran();
return(true);
}
catch (Exception ex)
{
db.Ado.RollbackTran();
LogHelper.WriteLogTips("按天统计工时收益异常：开始时间：" + startTime + ",结束时间：" + endTime + "。错误信息：" + ex.Message + "------" + ex.StackTrace);
return(false);
}
}
}
/// <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));
}
}
public JsonResult ChangeSubmit(SwGoods Model)
{
var jsonm = new ResultJson();
jsonm.data = Model;
var model = new SwGoods();
try
{
using (var db = SugarBase.GetIntance())
{
if (!string.IsNullOrEmpty(Model.ID))
{
model = db.Queryable <SwGoods>().Where(m => m.ID == Model.ID).First();
}
if (model == null)
{
model = new SwGoods();
}
model.Title = Model.Title;
model.Content = Model.Content;
model.GoodsKeys = Model.GoodsKeys;
model.Sort = Model.Sort;
model.IsHot = Model.IsHot;
if (Model.IsTop)
{
if (!model.IsTop)
{
var TopSortModel = db.Queryable <SwGoods>().Where(m => m.ID != model.ID).Select(m => new { TopSort = SqlFunc.AggregateMax(m.TopSort) }).First();
model.TopSort = TopSortModel.TopSort + 1;
}
}
else
{
model.TopSort = 0;
}
model.IsTop = Model.IsTop;
model.Status = Model.Status;
model.TypeID = Model.TypeID;
if (!string.IsNullOrEmpty(model.TypeID))
{
var typeModel = db.Queryable <SwGoodType>().Where(m => m.ID == model.TypeID).First();
if (typeModel != null)
{
model.TypeName = typeModel.Title;
}
}
if (string.IsNullOrEmpty(Model.ID))
{
SetSysLog("【添加文章】" + Model.Title, 3, 1);
db.Insertable(model).ExecuteCommand();
jsonm.status = 200;
}
else
{
SetSysLog("【编辑文章】" + model.Title, 3, 1);
db.Updateable(model).ExecuteCommand();
jsonm.status = 200;
}
}
}
catch (Exception ex)
{
LogProvider.Error("编辑文章", ex);
jsonm.status = 500;
jsonm.msg = "保存失败";
}
return(Json(jsonm));
}
public JsonResult setStatus(string ID, string Name, bool Value)
{
var jsonm = new ResultJson();
try
{
using (var db = SugarBase.GetIntance())
{
if (!string.IsNullOrEmpty(ID))
{
var model = db.Queryable <SwGoods>().Where(m => m.ID == ID).First();
if (model != null)
{
if (Name == "IsHot")
{
model.IsHot = Value;
}
if (Name == "IsTop")
{
if (Value)
{
if (!model.IsTop)
{
var TopSortModel = db.Queryable <SwGoods>().Where(m => m.ID != model.ID).Select(m => new { TopSort = SqlFunc.AggregateMax(m.TopSort) }).First();
model.TopSort = TopSortModel.TopSort + 1;
}
}
else
{
model.TopSort = 0;
}
model.IsTop = Value;
}
db.Updateable(model).ExecuteCommand();
}
}
else
{
jsonm.status = 500;
jsonm.msg = "修改失败";
}
}
}
catch (Exception ex)
{
LogProvider.Error("改变文章属性", ex.StackTrace, ex.Message);
jsonm.status = 500;
jsonm.msg = "修改失败";
}
return(Json(jsonm));
}
public object GetDepartmentAnalysisInfo(string department, string Level)
{
List <IConditionalModel> conModels = new List <IConditionalModel>();
//部门名称
if (!string.IsNullOrEmpty(department))
{
conModels.Add(new ConditionalModel()
{
FieldName = "Department",
FieldValue = department,
ConditionalType = ConditionalType.Equal
});
}
//人员级别
if (!string.IsNullOrEmpty(Level))
{
conModels.Add(new ConditionalModel()
{
FieldName = "Level",
FieldValue = Level,
ConditionalType = ConditionalType.Equal
});
}
//获取平均值和最高最低值
var result = personalscroceManager.Db.Queryable <personalscroce, personalfiles, dicsubject>((t1, t2, t3) => t1.PGuid == t2.Guid && t1.SubjectGuid == t3.Guid).Where(conModels).GroupBy((t1, t2, t3) => new { t1.SubjectGuid }).Select((t1, t2, t3) => new { SubjectGuid = t3.Guid, Subject = t3.SubjectName, SubjectType = t3.SubType, ScoreAvg = SqlFunc.AggregateAvg <double>((double)t1.Score), ScoreMax = SqlFunc.AggregateMax(t1.Score), ScoreMin = SqlFunc.AggregateMin(t1.Score) }).ToList();
JArray jArray = new JArray();
//获取最高得分者
foreach (var item in result)
{
JObject jObject = JObject.Parse(Newtonsoft.Json.JsonConvert.SerializeObject(item));
List <IConditionalModel> conditionalModels = new List <IConditionalModel>();
conditionalModels.Add(new ConditionalModel()
{
FieldName = "Score",
FieldValue = item.ScoreMax.Value.ToString(),
ConditionalType = ConditionalType.Equal
});
conditionalModels.Add(new ConditionalModel()
{
FieldName = "SubjectGuid",
FieldValue = item.SubjectGuid,
ConditionalType = ConditionalType.Equal
});
conditionalModels.AddRange(conModels);
var names = personalfilesManager.Db.Queryable <personalscroce, personalfiles>((t1, t2) => t1.PGuid == t2.Guid).Where(conditionalModels).Select((t1, t2) => t2.Name).ToList().Distinct();
//最高得分者名称
string maxScoreName = string.Join("#", names);
jObject["maxScoreName"] = maxScoreName;
//获取历史最高得分
string sql = $" SELECT DISTINCT(`Name`),Score from personalscroce t1 JOIN personalfiles t2 ON t1.PGuid=t2.Guid where SubjectGuid='{item.SubjectGuid}' and Score= (SELECT MAX(Score) from personalscroce where SubjectGuid='{item.SubjectGuid}')";
var dt = personalscroceManager.Db.Ado.GetDataTable(sql);
int maxHistoryScore = 0;
List <string> maxHistoryScoreName = new List <string>();
foreach (DataRow row in dt.Rows)
{
maxHistoryScore = int.Parse(row[1].ToString());
maxHistoryScoreName.Add(row[0].ToString());
}
jObject["maxHistoryScoreName"] = string.Join("#", maxHistoryScoreName);
jObject["maxHistoryScore"] = maxHistoryScore;
jArray.Add(jObject);
}
//获取单位指标
double power = jArray.Where(t => t["SubjectType"].ToString() == "力量").Average(t => double.Parse(t["ScoreAvg"].ToString()));
double speed = jArray.Where(t => t["SubjectType"].ToString() == "速度").Average(t => double.Parse(t["ScoreAvg"].ToString()));
double sensitivity = jArray.Where(t => t["SubjectType"].ToString() == "灵敏度").Average(t => double.Parse(t["ScoreAvg"].ToString()));
double endurance = jArray.Where(t => t["SubjectType"].ToString() == "耐力").Average(t => double.Parse(t["ScoreAvg"].ToString()));
double flexibility = jArray.Where(t => t["SubjectType"].ToString() == "柔韧性").Average(t => double.Parse(t["ScoreAvg"].ToString()));
Dictionary <string, double> keyValuePairs = new Dictionary <string, double>();
keyValuePairs.Add("力量", power);
keyValuePairs.Add("速度", speed);
keyValuePairs.Add("灵敏度", sensitivity);
keyValuePairs.Add("耐力", endurance);
keyValuePairs.Add("柔韧性", flexibility);
string advanceSubject = keyValuePairs.Where(t => t.Value == keyValuePairs.Max(g => g.Value)).Select(t => t.Key).FirstOrDefault();
string weakSubject = keyValuePairs.Where(t => t.Value == keyValuePairs.Min(g => g.Value)).Select(t => t.Key).FirstOrDefault();
return(new
{
data = jArray.OrderBy(t => t["SubjectType"]).ToList(),
power = Math.Round(power, 1),
speed = Math.Round(speed, 1),
sensitivity = Math.Round(sensitivity, 1),
endurance = Math.Round(endurance, 1),
flexibility = Math.Round(flexibility, 1),
advanceSubject = advanceSubject,
weakSubject = weakSubject
});
}
