/// <summary>
/// 根据集合获取完整商品属性集合
/// </summary>
/// <returns></returns>
public List <GoodDto> GetGoodDto(GetGoodApo pageDataApo, out int totalCount)
{
totalCount = 0;
List <GoodDto> data;
//查询语句
var queryable = Db.Queryable <Goods>()
.Where(it => pageDataApo.goodsEpsDatas.Contains(it.code))
.WhereIF(!string.IsNullOrWhiteSpace(pageDataApo.name), it => it.name.Contains(pageDataApo.name))
.WhereIF(!string.IsNullOrWhiteSpace(pageDataApo.code), it => it.goods_code.Contains(pageDataApo.code))
.GroupBy(it => it.goods_code)
.OrderBy(it => it.expire_date, OrderByType.Asc)
.Select(it => new GoodDto
{
name = it.name,
goods_code = it.goods_code,
amount = SqlFunc.AggregateCount(it.id),
expire_time = SqlFunc.AggregateMin(it.expire_date),
position = it.position
});
//如果小于0，默认查全部
if (pageDataApo.PageSize > 0)
{
data = queryable.ToPageList(pageDataApo.PageIndex, pageDataApo.PageSize, ref totalCount);
}
else
{
data = queryable.ToList();
totalCount = data.Count();
}
return(data);
}
private void q6()
{
using (var db = GetInstance())
{
var join6 = db.Queryable <Student, School>((st, sc) => new object[] {
JoinType.Left, st.SchoolId == sc.Id
}).Select((st, sc) => new ViewModelStudent {
Name = st.Name, SchoolId = SqlFunc.AggregateMin(sc.Id)
}).ToSql();
string sql = @"SELECT [st].[Name] AS [Name] , MIN([sc].[Id]) AS [SchoolId] FROM [STudent] st Left JOIN [School] sc ON ( [st].[SchoolId] = [sc].[Id] ) ";
base.Check(sql, null, join6.Key, null, "join 6 Error");
}
}
/// <summary>
/// 获取所有完整商品属性集合
/// </summary>
/// <returns></returns>
public List <GoodDto> GetAllGoodsDto()
{
//查询语句
return(Db.Queryable <Goods>()
.GroupBy(it => it.goods_code)
.OrderBy(it => it.goods_code, OrderByType.Asc)
.Select(it => new GoodDto
{
name = it.name,
goods_code = it.goods_code,
amount = SqlFunc.AggregateCount(it.id),
expire_time = SqlFunc.AggregateMin(it.expire_date),
position = it.position
}).ToList());
}
private void q7()
{
using (var db = GetInstance())
{
var join7 = db.Queryable <Student, School>((st, sc) => new object[] {
JoinType.Left, st.SchoolId == sc.Id
}).Select((st, sc) => new ViewModelStudent {
Name = st.Name, SchoolId = SqlFunc.AggregateMin(sc.Id * 1)
}).ToSql();
string sql = @"SELECT [st].[Name] AS [Name] , MIN(( [sc].[Id] * @Id0 )) AS [SchoolId] FROM [STudent] st Left JOIN [School] sc ON ( [st].[SchoolId] = [sc].[Id] ) ";
base.Check(sql, new List <SugarParameter>()
{
new SugarParameter("@Id0", 1)
}, join7.Key, join7.Value, "join 7 Error");
}
}
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
});
}