public static int Grant(string teacher, string funcID, int rtype, int sysNo)
{
using (AppBLL bll = new AppBLL())
{
var sql = "Insert Into s_tb_rights(teacherID,FuncID,Rtype,SYSNO) Values(@UserID,@FuncID,@rtype,@sysNo)";
return(bll.ExecuteNonQueryByText(sql, new { UserID = teacher, FuncID = funcID, rtype = rtype, sysNo = sysNo }));
}
}
public static int JoinGroup(string teacher, string groupID)
{
using (AppBLL bll = new AppBLL())
{
var sql = "insert into tbGroupInfo(teacherID, groupID) Values(@teacherID, @GroupID)";
return(bll.ExecuteNonQueryByText(sql, new { teacherID = teacher, GroupID = groupID }));
}
}
public static int LeaveGroup(string teacher, string groupID)
{
using (AppBLL bll = new AppBLL())
{
var sql = "delete from tbGroupInfo Where teacherID=@teacherID and Groupid=@GroupID";
return(bll.ExecuteNonQueryByText(sql, new { teacherID = teacher, GroupID = groupID }));
}
}
private static void Revoke(AppBLL bll, string teacher, FuncEntry funcEntry)
{
if (funcEntry.Kind > 0)
{
var sql = "Delete from s_tb_rights Where TeacherID=@UserID and FuncID=@FuncID and SYSNO=2";
bll.ExecuteNonQueryByText(sql, new { UserID = teacher, FuncID = funcEntry.FuncID });
}
if (funcEntry.Children.Any())
{
foreach (var func in funcEntry.Children)
{
Revoke(bll, teacher, func);
}
}
}
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 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;
}
}
}
