public static int GetStartIndex(string tableName)
{
using (AppBLL bll = new AppBLL())
{
DataTable table = bll.FillDataTableByText("Select * from " + tableName);
if (table.Rows.Count == 0)
{
return(1);
}
table = bll.FillDataTableByText("select Max(Cast(Right(SystemID,8) as Integer )) as MaxID from " + tableName);
return(int.Parse(table.Rows[0]["MaxID"].ToString()) + 1);
}
}
public static string GetScope(string teacherId)
{
using (AppBLL bll = new AppBLL())
{
var sql = "select * from s_tb_teacherscope where teacherid=@teacherId and teachertype=2";
return(JsonConvert.SerializeObject(bll.FillDataTableByText(sql, new { teacherId = teacherId })));
}
}
public static string GetGradeScope(string teacherId)
{
using (AppBLL bll = new AppBLL())
{
var sql = "Select scope,GradeName from s_tb_teacherscope,tdGradeCode " +
"Where teachertype=3 and teacherID=@teacherId and scope= Gradeno";
return(JsonConvert.SerializeObject(bll.FillDataTableByText(sql, new { teacherId = teacherId })));
}
}
public static string GetQueryBTeacher(int?micyear, string gradeCourse, int?gradecode, int?testtypes, int?testno, string stuId, int?order)
{
using (AppBLL bll = new AppBLL())
{
var sql = "Select GradeName+'('+substring(classCode,3,2)+')班' as class," +
"ClassSN," +
"CourseName," +
"TypeName," +
"SRID," +
"StdName," +
"NumScore," +
"LevelScore," +
"StandardScore," +
"GradeOrder," +
"ClassOrder," +
"markName, " +
"markcode, " +
"teacherName,ClassCode,Testno " +
"from s_vw_ClassScoreNum " +
" Where ClassCode=@gradecode " +
" and Academicyear=@micyear";
if (!string.IsNullOrEmpty(gradeCourse))
{
sql += " and CourseCode in (" + gradeCourse + ")";
}
if (testtypes != null)
{
sql += " and TestType=" + testtypes + " ";
}
if (testno != null)
{
sql += " and TestNo=" + testno + "";
}
if (stuId != "")
{
sql += " and SRID in(" + stuId + ")";
}
if (order == null)
{
sql += " Order By Testno,NumScore desc";
}
else if (order == 0)
{
sql += " Order By classSN";
}
else
{
sql += " Order By NumScore desc";
}
return(JsonConvert.SerializeObject(bll.FillDataTableByText(sql,
new
{
gradecode = gradecode,
micyear = micyear
})));
}
}
protected void ExportXJ()
{
int micYear = int.Parse(Request["micYear"]);
string semester = Request["semester"];
string gradeNo = Request["gradeNo"];
string courseCode = Request["courseCode"];
int testType = int.Parse(Request["testType"]);
int testNo = int.Parse(Request["testNo"]);
int scoreSort = int.Parse(Request["scoreSort"]);
using (AppBLL bll = new AppBLL())
{
var sql = "";
DataTable table = null;
if (testType == 1)
{
sql += scoreSort == 1 ? ",avg(Numscore) as Score,operator" : ",avg(standardscore) as Score,operator";
sql += " from s_vw_ClassScoreNum "
+ " where Gradeno=@gradeNo"
+ " and Academicyear=@micYear"
+ " and semester=@semester"
+ " and CourseCode=@courseCode"
+ " and TestType=@testType"
+ " and STATE is NULL"
+ " group by Academicyear,SRID,CourseCode,Teacherid,MarkCode,operator";
table = bll.FillDataTableByText(sql, new { gradeno = gradeNo, micYear = micYear, semester = semester, courseCode = courseCode, testType = testType });
}
else
{
sql += scoreSort == 1 ? ",Numscore as Score,operator" : ",standardscore as Score,operator";
sql += " from s_vw_ClassScoreNum"
+ " Where GradeNo=@gradeNo"
+ " and Academicyear=@micYear"
+ " and CourseCode=@courseCode"
+ " and TestNo=@testNo"
+ " and STATE is NULL";
table = bll.FillDataTableByText(sql, new { gradeno = gradeNo, micYear = micYear, courseCode = courseCode, testno = testNo });
}
string fileName = string.Format("{0}.xls", "学籍数据");
string excelHtml = DataTableToHtml(table, "学 籍");
UtilHelper.DownToExcel(fileName, excelHtml);
}
}
public static string GetGradeByGradeNo(int micyear, int gradeNo)
{
using (AppBLL bll = new AppBLL())
{
var sql = "select GradeBriefName+'('+substring(tbGradeClass.classNo,3,2)+')班' AS GradeBriefName,classNo " +
"from tdGradeCode,tbGradeClass WHERE tbGradeClass.Gradeno=tdGradeCode.GradeNo " +
"AND tbGradeClass.academicYear=@micyear and tbGradeClass.GradeNo=@gradeNo";
return(JsonConvert.SerializeObject(bll.FillDataTableByText(sql, new { micyear = micyear, gradeNo = gradeNo })));
}
}
public static string GetQueryGradeManager(int?micyear, string gradeCourse, int?gradecode, int?testtypes, int?testno, string classCode, string stuId, int?order)
{
using (AppBLL bll = new AppBLL())
{
var sql = "Select GradeName+'('+substring(Classcode,3,2)+')班' as class," +
"ClassSN ," +
"stdName , " +
"CourseName ," +
"TypeName ," +
"TestNo ," +
"NumScore ," +
"GradeOrder," +
"ClassOrder," +
"teacherName," +
"markcode, " +
"ClassCode " +
"from s_vw_ClassScoreNum " +
" Where Academicyear=@micyear ";
if (gradecode != null)
{
sql += "and GradeNo=" + gradecode + "";
}
if (!string.IsNullOrEmpty(gradeCourse))
{
sql += " and CourseCode in (" + gradeCourse + ")";
}
if (testtypes != null)
{
sql += " and TestType=" + testtypes + " ";
}
if (testno != null)
{
sql += " and TestNo=" + testno + "";
}
if (classCode != "")
{
sql += " and ClassCode in(" + classCode + ")";
}
if (stuId != null)
{
sql += " and SRID =" + stuId + "";
}
if (order == 1)
{
sql += " Order By Testno,NumScore DESC,courseName";
}
else
{
sql += " Order By ClassCode,ClassSN,courseName";
}
DataTable table = bll.FillDataTableByText(sql, new { micyear = micyear });
return(JsonConvert.SerializeObject(table));
}
}
public static bool fRightYesNO(string UserName, string FunctionID)
{
if (UserName.Equals("Jim.Liu"))
{
return(true);
}
using (AppBLL bll = new AppBLL())
{
if (FunctionID.Substring(11, 5).Equals("00"))
{
var sql = string.Format("Select FunctionID from tbUserGroupInfo,tbrights where tbUserGroupInfo.TeacherID=tbRights.TeacherID and FunctionID=\'{0}\' and Name=\'{1}\'", FunctionID, UserName);
DataTable table = bll.FillDataTableByText(sql);
if (table.Rows.Count > 0)
{
return(true);
}
sql = string.Format("Select * from tbUserGroupInfo where Name=\'{0}\' and userorGroup=\'1\'", UserName);
table = bll.FillDataTableByText(sql);
if (table.Rows.Count == 0)
{
return(false);
}
string teacherID = table.Rows[0]["TeacherID"].ToString();
sql = "SELECT distinct TbGroupInfo.GroupID, TbRights.FunctionID, TbRights.SysNo,"
+ " TbUserGroupInfo1.TeacherID FROM TbUserGroupInfo INNER JOIN "
+ " TbGroupInfo ON TbUserGroupInfo.TeacherID = TbGroupInfo.TeacherID INNER JOIN "
+ " TbRights INNER JOIN TbUserGroupInfo TbUserGroupInfo1 ON "
+ " TbRights.TeacherID = TbUserGroupInfo1.TeacherID ON TbGroupInfo.GroupID = TbRights.TeacherID "
+ " Where tbUserGroupInfo.TeacherID =\'{0}\' and TbRights.FunctionID=\'{1}\'";
table = bll.FillDataTableByText(sql);
return(table.Rows.Count > 0);
}
else
{
return(true);
}
}
}
public static DataTable GetTestLoginByYear(int micYear)
{
using (AppBLL bll = new AppBLL())
{
var sql = "select TestType,TestNo as TestLoginNo, case when TestType=0 then '平时' + CAST(testno as varchar)" +
" when TestType=1 then '期中' + CAST(testno as varchar)" +
" else '期末' + CAST(testno as varchar) end Name" +
" from s_tb_testlogin where Academicyear='{0}' order by cast(testno as int)";
sql = string.Format(sql, micYear);
return(bll.FillDataTableByText(sql));
}
}
/// <summary>
/// 导出用户清单
/// </summary>
protected void ExportUserGroup()
{
var schoolName = Request["school"];
using (AppBLL bll = new AppBLL())
{
var sql = "select distinct Name as 用户名称,Password as 密码 from tbUserGroupInfo where teacherid<'9999999990000' and (Status<>'1' or status is null) order by Name";
DataTable table = bll.FillDataTableByText(sql);
string fileName = string.Format("{0}.xls", schoolName);
string excelHtml = DataTableToHtml(table, string.Format("{0}成绩分析系统用户清单", schoolName));
UtilHelper.DownToExcel(fileName, excelHtml);
}
}
public static int SaveUserGroup(UserGroupInfo userGroup)
{
using (AppBLL bll = new AppBLL())
{
if (userGroup.TeacherID.Trim().Equals("-1"))
{
//新增查重
var sql = "Select * from tbUserGroupInfo Where Name=@UserName";
DataTable table = bll.FillDataTableByText(sql, new { UserName = userGroup.Name });
if (table.Rows.Count > 0)
{
return(-1); //数据库中已经存在名称为{0}的用户或组
}
}
return(bll.ExecuteNonQuery("USP_System_InsertUserGroup", userGroup));
}
}
public static string BuildSystemIdBegin()
{
using (AppBLL bll = new AppBLL())
{
DataTable table = bll.FillDataTableByText("Select SchoolCode,AcadEmicYear from tbSchoolBaseInfo");
if (table.Rows.Count == 0)
{
return("-1");
}
var SchoolNo = table.Rows[0]["SchoolCode"].ToString();
string CurrentYear = table.Rows[0]["AcadEmicYear"].ToString();
if (string.IsNullOrEmpty(CurrentYear.Trim()))
{
return("-2");
}
return(string.Format("{0}{1}", SchoolNo, CurrentYear));
}
}
public static ResultEntity RemoveUserGroup(UserGroupInfo userGroup)
{
try
{
using (AppBLL bll = new AppBLL())
{
var sql = "";
DataTable table;
sql = "Select Count(*) as iCount from tbLog where TeacherID=@p";
table = bll.FillDataTableByText(sql, new { p = userGroup.TeacherID });
if (int.Parse(table.Rows[0][0].ToString()) > 0)
{
return new ResultEntity()
{
State = -1, Context = "发现此用户有日志信息！"
}
}
;
sql = "Select * from tbRights where TeacherID=@p";
table = bll.FillDataTableByText(sql, new { p = userGroup.TeacherID });
if (table.Rows.Count > 0)
{
return new ResultEntity()
{
State = -2, Context = "请先去除用户(组)权限！"
}
}
;
if (userGroup.UserOrGroup.Equals("1"))
{
//用户
sql = "Select * from tbTeacherClass where TeacherID=@p and AcademicYear=(Select top 1 AcadEmicYear from tbSchoolBaseInfo)";
table = bll.FillDataTableByText(sql, new { p = userGroup.TeacherID });
if (table.Rows.Count > 0)
{
return new ResultEntity()
{
State = -3, Context = "老师本学年有课！"
}
}
;
sql = "Select * from tbGroupInfo where TeacherID=@p";
table = bll.FillDataTableByText(sql, new { p = userGroup.TeacherID });
if (table.Rows.Count > 0)
{
return new ResultEntity()
{
State = -4, Context = string.Format("把({0})从组去除！", userGroup.Name)
}
}
;
}
else
{
sql = "Select * from tbGroupInfo where GroupID=@p";
table = bll.FillDataTableByText(sql, new { p = userGroup.TeacherID });
if (table.Rows.Count > 0)
{
return new ResultEntity()
{
State = -5, Context = string.Format("请把组({0})的所有用户去除！", userGroup.Name)
}
}
;
}
var strTemp = userGroup.TeacherID.Substring(10, 4);
if (strTemp.Equals("0001") || strTemp.Equals("0002") || strTemp.Equals("0003") ||
strTemp.Equals("1001") || strTemp.Equals("0888"))
{
return(new ResultEntity()
{
State = -6, Context = "系统保留用户(组)！"
});
}
sql = "Select TeacherID,Name from tbUserGroupInfo Where TeacherID=@p";
table = bll.FillDataTableByText(sql, new { p = userGroup.TeacherID });
if (table.Rows.Count == 0)
{
return new ResultEntity()
{
State = -7, Context = "数据库错误！"
}
}
;
sql = "Delete from tbUserGroupinfo Where TeacherID=@p";
bll.ExecuteNonQueryByText(sql, new { p = userGroup.TeacherID });
}
return(new ResultEntity()
{
State = 1, Context = string.Format("用户(组)<{0}>已经删除！", userGroup.Name)
});
}
catch (Exception ex)
{
return(new ResultEntity()
{
State = -8, Context = string.Format("用户(组)删除失败！", userGroup.Name)
});
}
}
public static string GetQueryTeacher(int?micyear, string teacherid, int?gradeCourse, int?gradecode, int?testtypes, int?testno, string stuId, int?order)
{
using (AppBLL bll = new AppBLL())
{
var sql = "Select GradeName+'('+substring(classCode,3,2)+')班' as class," +
"ClassSN," +
"StdName," +
"NumScore," +
"LevelScore," +
"StandardScore ," +
"GradeOrder," +
"ClassOrder," +
"markName ," +
"TypeName ," +
"testno ," +
"SchoolID ," +
"courseName, " +
"markcode " +
" from s_vw_ClassScoreNum left join S_tb_SchoolID on s_vw_ClassScoreNum.SRID=S_tb_SchoolID.SRID" +
" Where AcademicYear=@micyear" +
" and ClassCode=@gradecode and CourseCode=@gradeCourse" +
" and TeacherID=@teacherid";
if (testtypes != null)
{
sql += " and TestType=" + testtypes + " ";
}
if (testno != null)
{
sql += " and TestNo=" + testno + "";
}
if (stuId != "")
{
sql += " and s_vw_ClassScoreNum.SRID in(" + stuId + ")";
}
if (order == null)
{
if (stuId != "")
{
sql += " Order By ClassCode,ClassSN";
}
else
{
sql += " Order By Testno,NumScore DESC";
}
}
else if (order == 0)
{
sql += " Order By ClassCode";
}
else
{
sql += " Order By NumScore desc";
}
return(JsonConvert.SerializeObject(bll.FillDataTableByText(sql,
new
{
micyear = micyear,
teacherid = teacherid,
gradeCourse = gradeCourse,
gradecode = gradecode
})));
}
}
public static void gp_ScoreTj(int micYear, string Semester, string testType, string testNo, string courseCode, string gradeOrClassNo, int Flag)
{
using (AppBLL bll = new AppBLL())
{
var sql = Flag == 0 ? "Delete from s_tb_GradeStat where" : "Delete from s_tb_ClassStat where";
sql += " Academicyear='{0}'"
+ " and Testno='{1}'"
+ " and CourseCode='{2}'";
sql += Flag == 0 ? " and GradeNo='{3}'" : "and ClassNo='{3}'";
sql = string.Format(sql, micYear, testNo, courseCode, gradeOrClassNo);
bll.ExecuteNonQueryByText(sql);
//先插入数据
if (Flag == 0)
{
sql = " insert Into s_tb_GradeStat(AcademicYear,semester,CourseCode,TestType,TestNo,GradeNo)";
}
else
{
sql = " insert Into s_tb_ClassStat(AcademicYear,semester,CourseCode,TestType,TestNo,ClassNo)";
}
sql += " values('{0}','{1}','{2}','{3}','{4}','{5}')";
sql = string.Format(sql, micYear, Semester, courseCode, testType, testNo, gradeOrClassNo);
bll.ExecuteNonQueryByText(sql);
//将统计数据修改为当前正确值
//先将0-0.05数据添入
sql = " select count(*) as S_5 from s_vw_ClassScoreNum"
+ " where NumScore/cast(substring(Markcode,2,3) as numeric(5,2) )<0.05 "
+ " and substring(Markcode,1,1)='1'"
+ " and Academicyear='{0}'"
+ " and TestNo='{1}'"
+ " and CourseCode='{2}'";
sql += Flag == 0 ? " and GradeNo='{3}'" : "and ClassNo='{3}'";
sql = string.Format(sql, micYear, testNo, courseCode, gradeOrClassNo);
DataTable table = bll.FillDataTableByText(sql);
var s_num = int.Parse(table.Rows[0]["S_5"].ToString());
//更新到数据库
sql = Flag == 0 ? "Update s_tb_GradeStat set S_5={4}" : "Update s_tb_ClassStat set S_5={4}";
sql += " where Academicyear='{0}'"
+ " and TestNo='{1}'"
+ " and CourseCode='{2}'";
sql += Flag == 0 ? " and GradeNo='{3}'" : "and ClassNo='{3}'";
sql = string.Format(sql, micYear, testNo, courseCode, gradeOrClassNo, s_num);
bll.ExecuteNonQueryByText(sql);
//二将S_100数据添入
sql = " select count(*) as S_5 from s_vw_ClassScoreNum"
+ " where NumScore/cast(substring(Markcode,2,3) as numeric(5,2) )>=0.95 "
+ " and substring(Markcode,1,1)='1'"
+ " and Academicyear='{0}'"
+ " and TestNo='{1}'"
+ " and CourseCode='{2}'";
sql += Flag == 0 ? " and GradeNo='{3}'" : "and ClassNo='{3}'";
sql = string.Format(sql, micYear, testNo, courseCode, gradeOrClassNo);
table = bll.FillDataTableByText(sql);
s_num = int.Parse(table.Rows[0]["S_5"].ToString());
//更新到数据库
sql = Flag == 0 ? " Update s_tb_GradeStat set S_100={4}" : "Update s_tb_ClassStat set S_100={4}";
sql += " where Academicyear='{0}'"
+ " and TestNo='{1}'"
+ " and CourseCode='{2}'";
sql += Flag == 0 ? " and GradeNo='{3}'" : "and ClassNo='{3}'";
sql = string.Format(sql, micYear, testNo, courseCode, gradeOrClassNo, s_num);
bll.ExecuteNonQueryByText(sql);
var LowScore = 0.05f;
var highScore = 0.1f;
for (var i = 0; i <= 17; i++)
{
sql = " select count(*) as S_5 from s_vw_ClassScoreNum"
+ " where NumScore/cast(substring(Markcode,2,3) as numeric(5,2) )>={4}"
+ " and NumScore/cast(substring(Markcode,2,3) as numeric(5,2) )<{5}"
+ " and substring(Markcode,1,1)='1'"
+ " and Academicyear='{0}'"
+ " and TestNo='{1}'"
+ " and CourseCode='{2}'";
sql += Flag == 0 ? " and GradeNo='{3}'" : "and ClassNo='{3}'";
sql = string.Format(sql, micYear, testNo, courseCode, gradeOrClassNo, LowScore, highScore);
table = bll.FillDataTableByText(sql);
s_num = int.Parse(table.Rows[0]["S_5"].ToString());
switch (i)
{
case 0:
sql = Flag == 0 ? "Update s_tb_GradeStat Set S_10={0}" : "Update s_tb_ClassStat Set S_10={0}";
break;
case 1:
sql = Flag == 0 ? "Update s_tb_GradeStat Set S_15={0}" : "Update s_tb_ClassStat Set S_15={0}";
break;
case 2:
sql = Flag == 0 ? "Update s_tb_GradeStat Set S_20={0}" : "Update s_tb_ClassStat Set S_20={0}";
break;
case 3:
sql = Flag == 0 ? "Update s_tb_GradeStat Set S_25={0}" : "Update s_tb_ClassStat Set S_25={0}";
break;
case 4:
sql = Flag == 0 ? "Update s_tb_GradeStat Set S_30={0}" : "Update s_tb_ClassStat Set S_30={0}";
break;
case 5:
sql = Flag == 0 ? "Update s_tb_GradeStat Set S_35={0}" : "Update s_tb_ClassStat Set S_35={0}";
break;
case 6:
sql = Flag == 0 ? "Update s_tb_GradeStat Set S_40={0}" : "Update s_tb_ClassStat Set S_40={0}";
break;
case 7:
sql = Flag == 0 ? "Update s_tb_GradeStat Set S_45={0}" : "Update s_tb_ClassStat Set S_45={0}";
break;
case 8:
sql = Flag == 0 ? "Update s_tb_GradeStat Set S_50={0}" : "Update s_tb_ClassStat Set S_50={0}";
break;
case 9:
sql = Flag == 0 ? "Update s_tb_GradeStat Set S_55={0}" : "Update s_tb_ClassStat Set S_55={0}";
break;
case 10:
sql = Flag == 0 ? "Update s_tb_GradeStat Set S_60={0}" : "Update s_tb_ClassStat Set S_60={0}";
break;
case 11:
sql = Flag == 0 ? "Update s_tb_GradeStat Set S_65={0}" : "Update s_tb_ClassStat Set S_65={0}";
break;
case 12:
sql = Flag == 0 ? "Update s_tb_GradeStat Set S_70={0}" : "Update s_tb_ClassStat Set S_70={0}";
break;
case 13:
sql = Flag == 0 ? "Update s_tb_GradeStat Set S_75={0}" : "Update s_tb_ClassStat Set S_75={0}";
break;
case 14:
sql = Flag == 0 ? "Update s_tb_GradeStat Set S_80={0}" : "Update s_tb_ClassStat Set S_80={0}";
break;
case 15:
sql = Flag == 0 ? "Update s_tb_GradeStat Set S_85={0}" : "Update s_tb_ClassStat Set S_85={0}";
break;
case 16:
sql = Flag == 0 ? "Update s_tb_GradeStat Set S_90={0}" : "Update s_tb_ClassStat Set S_90={0}";
break;
default:
sql = Flag == 0 ? "Update s_tb_GradeStat Set S_95={0}" : "Update s_tb_ClassStat Set S_95={0}";
break;
}
sql += " Where AcademicYear={1}"
+ " and TestNo={2}"
+ " and CourseCode={3}";
sql += Flag == 0 ? " and GradeNo='{4}'" : " and ClassCode={4}";
sql = string.Format(sql, s_num, micYear, testNo, courseCode, gradeOrClassNo);
bll.ExecuteNonQueryByText(sql);
LowScore += 0.05f;
highScore += 0.05f;
}
}
}