public void Q2()
{
using (var db = GetInstance())
{
//db.Database.IsEnableLogEvent = true;
db.Aop.OnLogExecuting = (sql, pars) =>
{
Console.WriteLine(sql + " " + pars);
};
#region dr ot entity
db.IgnoreColumns.Add("TestId", "Student");
var s1 = db.Queryable <Student>().Select(it => new ViewModelStudent2 {
Name = it.Name, Student = it
}).ToList();
var s2 = db.Queryable <Student>().Select(it => new { id = it.Id, w = new { x = it } }).ToList();
var s3 = db.Queryable <Student>().Select(it => new { newid = it.Id }).ToList();
var s4 = db.Queryable <Student>().Select(it => new { newid = it.Id, obj = it }).ToList();
var s5 = db.Queryable <Student>().Select(it => new ViewModelStudent2 {
Student = it, Name = it.Name
}).ToList();
#endregion
#region sql and parameters validate
var t1 = db.Queryable <Student, School>((st, sc) => new object[] {
JoinType.Inner, st.Id == sc.Id
}).GroupBy(st => st.Id).Having(st => SqlFunc.AggregateAvg(st.Id) == 1).Select(st => new { avgId = SqlFunc.AggregateAvg(st.Id) }).ToSql();
base.Check("SELECT AVG([st].[ID]) AS [avgId] FROM [STudent] st Inner JOIN [School] sc ON ( [st].[ID] = [sc].[Id] ) GROUP BY [st].[ID] HAVING (AVG([st].[ID]) = @Const0 ) ",
new List <SugarParameter>()
{
new SugarParameter("@Const0", 1)
}
,
t1.Key, t1.Value, " select t1 Error");
var t2 = db.Queryable <School, School>((st, st2) => new object[] {
JoinType.Left, st.Id == st2.Id
})
.Where(st => st.Id > 0)
.Select((st, st2) => new { stid = st.Id, scId = st2.Id, xx = st }).ToSql();
base.Check("SELECT [st].[Id] AS [stid] , [st2].[Id] AS [scId] , [st].[Id] AS [School.Id] , [st].[Name] AS [School.Name] FROM [School] st Left JOIN [School] st2 ON ( [st].[Id] = [st2].[Id] ) WHERE ( [st].[Id] > @Id0 ) "
, new List <SugarParameter>()
{
new SugarParameter("@Id0", 0)
}, t2.Key, t2.Value, "select t2 Error");
var t3 = db.Queryable <Student, School, School>((st, sc, sc2) => new object[] {
JoinType.Left, st.SchoolId == sc.Id,
JoinType.Left, sc2.Id == sc.Id
}).Where(st => st.Id > 0)
.Select <School>((st) => new School()
{
Id = st.Id
}).ToSql();
base.Check("SELECT [st].[ID] AS [Id] FROM [STudent] st Left JOIN [School] sc ON ( [st].[SchoolId] = [sc].[Id] ) Left JOIN [School] sc2 ON ( [sc2].[Id] = [sc].[Id] ) WHERE ( [st].[ID] > @Id0 ) ",
new List <SugarParameter>()
{
new SugarParameter("@Id0", 0)
}, t3.Key, t3.Value, "select t3 Error");
db.Aop.OnLogExecuting = (sql, pars) =>
{
base.Check(" SELECT COUNT(1) FROM (SELECT [st].[ID] FROM [STudent] st Left JOIN [School] sc ON ( [st].[SchoolId] = [sc].[Id] ) Left JOIN [School] sc2 ON ( [sc2].[Id] = [sc].[Id] ) GROUP BY [st].[ID] ) CountTable ",
null, sql, null, "select t4 Error");
};
var t4 = db.Queryable <Student, School, School>((st, sc, sc2) => new object[] {
JoinType.Left, st.SchoolId == sc.Id,
JoinType.Left, sc2.Id == sc.Id
}).GroupBy(st => st.Id).Select(st => st.Id).Count();
DateTime?result = DateTime.Now;
var t5 = db.Queryable <Student>().Where(it => it.CreateTime > result.Value.Date).ToSql();
base.Check("SELECT [ID],[SchoolId],[Name],[CreateTime] FROM [STudent] WHERE ( [CreateTime] > @Const0 )",
new List <SugarParameter>()
{
new SugarParameter("@Const0", result.Value.Date)
}, t5.Key, t5.Value, "select t5 Error");
db.Ado.IsEnableLogEvent = false;
var t6 = db.Queryable <DataTestInfo2>().Where(it => SqlFunc.HasValue(it.Bool2) == false).ToSql();
base.Check("SELECT [PK],[Bool1],[Bool2],[Text1] FROM [DataTestInfo2] WHERE (( CASE WHEN ( [Bool2]<>'' AND [Bool2] IS NOT NULL ) THEN 1 ELSE 0 END ) = @Const0 )",
new List <SugarParameter>()
{
new SugarParameter("@Const0", false)
}, t6.Key, t6.Value, "select t6 Error");
var t7 = db.Queryable <Student>().Select(it => new DataTestInfo2()
{
Bool1 = SqlFunc.IIF(SqlFunc.Subqueryable <Student>().Where(x => x.Id
== it.Id).Any(), true, false)
}).ToSql();
base.Check("SELECT ( CASE WHEN (EXISTS ( SELECT * FROM [STudent] WHERE ( [ID] = [it].[ID] ) )) THEN @MethodConst0 ELSE @MethodConst1 END ) AS [Bool1] FROM [STudent] it ",
new List <SugarParameter>()
{
new SugarParameter("@MethodConst0", true),
new SugarParameter("@MethodConst1", false)
}, t7.Key, t7.Value, "select t7 Error");
#endregion
try
{
var t8 = db.Queryable <Student, School, School>((st, sc, sc2) => new object[] {
JoinType.Left, st.SchoolId == sc.Id,
JoinType.Left, sc2.Id == sc.Id
}).Where(st => st.Id > 0)
.Select <School>((st1) => new School()
{
Id = st1.Id
}).ToList();
}
catch (Exception ex)
{
if (!ex.Message.Contains("English Message : Join st needs to be the same as Select st1"))
{
throw new Exception("selec t8 error");
}
Console.WriteLine(ex.Message);
}
try
{
var t8 = db.Queryable <Student, School>((st, sc) => st.Id == sc.Id).Where(st => st.Id > 0)
.Where(x => x.Id == 1)
.Select <School>((st) => new School()
{
Id = st.Id
}).ToList();
}
catch (Exception ex)
{
if (!ex.Message.Contains("English Message : Join st needs to be the same as Where x"))
{
throw new Exception("selec t8 error");
}
Console.WriteLine(ex.Message);
}
try
{
var t8 = db.Queryable <Student, School>((st, sc) => st.Id == sc.Id)
.Sum(x => x.Id);
}
catch (Exception ex)
{
if (!ex.Message.Contains("English Message : Join st needs to be the same as Sum x"))
{
throw new Exception("selec t8 error");
}
Console.WriteLine(ex.Message);
}
}
}
public void Q2()
{
using (var db = GetInstance())
{
//db.Database.IsEnableLogEvent = true;
db.Ado.LogEventStarting = (sql, pars) =>
{
Console.WriteLine(sql + " " + pars);
};
#region dr ot entity
db.IgnoreColumns.Add("TestId", "Student");
var s1 = db.Queryable <Student>().Select(it => new ViewModelStudent2 {
Name = it.Name, Student = it
}).ToList();
var s2 = db.Queryable <Student>().Select(it => new { id = it.Id, w = new { x = it } }).ToList();
var s3 = db.Queryable <Student>().Select(it => new { newid = it.Id }).ToList();
var s4 = db.Queryable <Student>().Select(it => new { newid = it.Id, obj = it }).ToList();
var s5 = db.Queryable <Student>().Select(it => new ViewModelStudent2 {
Student = it, Name = it.Name
}).ToList();
#endregion
#region sql and parameters validate
var t1 = db.Queryable <Student, School>((st, sc) => new object[] {
JoinType.Inner, st.Id == sc.Id
}).GroupBy(st => st.Id).Having(st => SqlFunc.AggregateAvg(st.Id) == 1).Select(st => new { avgId = SqlFunc.AggregateAvg(st.Id) }).ToSql();
base.Check("SELECT AVG(`st`.`ID`) AS `avgId` FROM `STudent` st Inner JOIN `School` sc ON ( `st`.`ID` = `sc`.`Id` ) GROUP BY `st`.`ID` HAVING (AVG(`st`.`ID`) = @Const0 ) ",
new List <SugarParameter>()
{
new SugarParameter("@Const0", 1)
}
,
t1.Key, t1.Value, " select t1 Error");
var t2 = db.Queryable <School, School>((st, st2) => new object[] {
JoinType.Left, st.Id == st2.Id
})
.Where(st => st.Id > 0)
.Select((st, st2) => new { stid = st.Id, scId = st2.Id, xx = st }).ToSql();
base.Check("SELECT `st`.`Id` AS `stid` , `st2`.`Id` AS `scId` , `st`.`Id` AS `School.Id` , `st`.`Name` AS `School.Name` FROM `School` st Left JOIN `School` st2 ON ( `st`.`Id` = `st2`.`Id` ) WHERE ( `st`.`Id` > @Id0 ) "
, new List <SugarParameter>()
{
new SugarParameter("@Id0", 0)
}, t2.Key, t2.Value, "select t2 Error");
var t3 = db.Queryable <Student, School, School>((st, sc, sc2) => new object[] {
JoinType.Left, st.SchoolId == sc.Id,
JoinType.Left, sc2.Id == sc.Id
}).Where(st => st.Id > 0)
.Select <School>((st) => new School()
{
Id = st.Id
}).ToSql();
base.Check("SELECT `st`.`ID` AS `Id` FROM `STudent` st Left JOIN `School` sc ON ( `st`.`SchoolId` = `sc`.`Id` ) Left JOIN `School` sc2 ON ( `sc2`.`Id` = `sc`.`Id` ) WHERE ( `st`.`ID` > @Id0 ) ",
new List <SugarParameter>()
{
new SugarParameter("@Id0", 0)
}, t3.Key, t3.Value, "select t3 Error");
#endregion
}
}
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
});
}
public object GetLatesSubjectScore(string department, string Level)
{
DateTime?dateTime = personalscroceManager.CurrentDb.AsQueryable().OrderBy(t => t.AchieveDate, OrderByType.Desc).Select(t => t.AchieveDate).First();
return(personalscroceManager.Db.Queryable <personalscroce, dicsubject, personalfiles>((t1, t2, t3) => t1.SubjectGuid == t2.Guid && t1.PGuid == t3.Guid).WhereIF(!string.IsNullOrEmpty(department), (t1, t2, t3) => t3.Department == department).WhereIF(!string.IsNullOrEmpty(Level), (t1, t2, t3) => t3.Level == Level).Where((t1, t2, t3) => t1.AchieveDate == dateTime).GroupBy((t1, t2, t3) => t2.SubjectName).Select((t1, t2, t3) => new { SubjectName = t2.SubjectName, Avg = SqlFunc.AggregateAvg((double)(t1.Score)) }).ToList());
}
public void Q2()
{
using (var db = GetInstance())
{
//db.Database.IsEnableLogEvent = true;
db.Aop.OnLogExecuting = (sql, pars) =>
{
Console.WriteLine(sql + " " + pars);
};
#region dr ot entity
db.IgnoreColumns.Add("TestId", "Student");
var s1 = db.Queryable <Student>().Select(it => new ViewModelStudent2 {
Name = it.Name, Student = it
}).ToList();
var s2 = db.Queryable <Student>().Select(it => new { id = it.Id, w = new { x = it } }).ToList();
var s3 = db.Queryable <Student>().Select(it => new { newid = it.Id }).ToList();
var s4 = db.Queryable <Student>().Select(it => new { newid = it.Id, obj = it }).ToList();
var s5 = db.Queryable <Student>().Select(it => new ViewModelStudent2 {
Student = it, Name = it.Name
}).ToList();
#endregion
#region sql and parameters validate
var t1 = db.Queryable <Student, School>((st, sc) => new object[] {
JoinType.Inner, st.Id == sc.Id
}).GroupBy(st => st.Id).Having(st => SqlFunc.AggregateAvg(st.Id) == 1).Select(st => new { avgId = SqlFunc.AggregateAvg(st.Id) }).ToSql();
base.Check("SELECT AVG([st].[ID]) AS [avgId] FROM [STudent] st Inner JOIN School sc ON ( [st].[ID] = [sc].[Id] ) GROUP BY [st].[ID] HAVING (AVG([st].[ID]) = @Const0 ) ",
new List <SugarParameter>()
{
new SugarParameter("@Const0", 1)
}
,
t1.Key, t1.Value, " select t1 Error");
var t2 = db.Queryable <School, School>((st, st2) => new object[] {
JoinType.Left, st.Id == st2.Id
})
.Where(st => st.Id > 0)
.Select((st, st2) => new { stid = st.Id, scId = st2.Id, xx = st }).ToSql();
base.Check("SELECT [st].[Id] AS [stid] , [st2].[Id] AS [scId] , [st].[Id] AS [School.Id] , [st].[Name] AS [School.Name] FROM [School] st Left JOIN School st2 ON ( [st].[Id] = [st2].[Id] ) WHERE ( [st].[Id] > @Id0 ) "
, new List <SugarParameter>()
{
new SugarParameter("@Id0", 0)
}, t2.Key, t2.Value, "select t2 Error");
var t3 = db.Queryable <Student, School, School>((st, sc, sc2) => new object[] {
JoinType.Left, st.SchoolId == sc.Id,
JoinType.Left, sc2.Id == sc.Id
}).Where(st => st.Id > 0)
.Select <School>((st) => new School()
{
Id = st.Id
}).ToSql();
base.Check("SELECT [st].[ID] AS [Id] FROM [STudent] st Left JOIN School sc ON ( [st].[SchoolId] = [sc].[Id] ) Left JOIN School sc2 ON ( [sc2].[Id] = [sc].[Id] ) WHERE ( [st].[ID] > @Id0 ) ",
new List <SugarParameter>()
{
new SugarParameter("@Id0", 0)
}, t3.Key, t3.Value, "select t3 Error");
#endregion
}
}
public object GetSubjectAchive(string pguid)
{
return(personalscroceManager.Db.Queryable <personalscroce, dicsubject>((t1, t2) => t1.SubjectGuid == t2.Guid).GroupBy((t1, t2) => new { t2.SubjectName }).Select((t1, t2) => new { Subject = t2.SubjectName, Score = SqlFunc.AggregateAvg((double)t1.Score) }).ToList());
}