public int Usp_UserOrg_Insert(UserOrgEntity entity)
{
using (AppBLL bll = new AppBLL())
{
bll.DbContext = bll.CreateDbContext(true);
try
{
DataHelper.ExecuteNonQuery("Usp_User_Insert", entity.User);
if (entity.User.I_Category == 105 && !string.IsNullOrEmpty(entity.Org.C_Name))
{
/// 机构用户 190 审核未通过 185 审核通过
OrganizationEntity orgEntity = bll.GetDataItem<OrganizationEntity>("Usp_Org_ById", new { Id = entity.Org.Id });
if (orgEntity!=null && orgEntity.I_Audited >190 && entity.Org.I_Audited <= 190)
{
///设置审核日期
entity.Org.D_Confirm = DateTime.Now;
}
if (orgEntity == null)
{
entity.Org.Id = entity.User.Id;
}
DataHelper.ExecuteNonQuery("Usp_Org_Insert", entity.Org);
WriteLog("用户及机构资料编辑", entity);
}
bll.DbContext.CommitTransaction();
return 1;
}
catch (Exception ex)
{
return -1;
}
}
}
/// <summary>
/// 返回条件节点的期望值
/// </summary>
/// <param name="flowAttachment"></param>
/// <returns></returns>
public int FindExpect(FlowAttachment flowAttachment)
{
using (AppBLL bll = new AppBLL())
{
FlowKinkEntity flowKind = bll.GetDataItem <FlowKinkEntity>("Usp_FlowKind_Get", new { Id = flowAttachment.Kind });
return((int)flowKind.I_Flow);
}
}
public static IList <GradeCourse> GetCourseCodeAll()
{
using (AppBLL bll = new AppBLL())
{
var sql = "Select FullName,CourseCode from tdCourseCode Where SubString(CourseCode,2,1) =1";
return(bll.FillListByText <GradeCourse>(sql, new {}));
}
}
public static IList <GradeCode> GetGradeAll()
{
using (AppBLL bll = new AppBLL())
{
var sql = "Select * from tdGradeCode";
return(bll.FillListByText <GradeCode>(sql, new {}));
}
}
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 IList <TestLogin> GetTestNo(int micyear, int testtype)
{
using (AppBLL bll = new AppBLL())
{
var sql = "Select testno from s_tb_Testlogin where Academicyear=@micyear" +
" and testtype=@testtype ";
return(bll.FillListByText <TestLogin>(sql, new { micyear = micyear, testtype = testtype }));
}
}
public static IList <Student> GetStudentsByGrade(int academicyear, string classNo)
{
using (AppBLL bll = new AppBLL())
{
var sql = "Select SRID AS StudentId,StdName from s_vw_ClassStudent" +
" Where Academicyear=@academicyear and ClassCode in (" + classNo + ")";
return(bll.FillListByText <Student>(sql, new { academicyear = academicyear }));
}
}
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
})));
}
}
public static IList <GradeCourse> GetBCourse(int teacherScope)
{
using (AppBLL bll = new AppBLL())
{
var sql = "select a.Coursecode as CourseCode,b.BriefName as FullName from tbCourseUse a,tdCourseCode b " +
"Where a.coursecode=b.coursecode and SubString(a.CourseCode,2,1) =1 and a.GradeNo=@teacherScope group by a.Coursecode,BriefName";
return(bll.FillListByText <GradeCourse>(sql, new { teacherScope = teacherScope }));
}
}
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 IList <Student> GetStudent(int academicyear, int classcode)
{
using (AppBLL bll = new AppBLL())
{
var sql = "Select SRID AS StudentId,StdName from s_vw_ClassStudent" +
" Where Academicyear=@academicyear and ClassCode=@classcode";
return(bll.FillListByText <Student>(sql, new { academicyear = academicyear, classcode = classcode }));
}
}
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 IList <UserAuth> GetUserAuths(string teacher)
{
using (AppBLL bll = new AppBLL())
{
var sql = "SELECT a.FuncId, b.FuncName, b.Description, b.FuncType" +
" FROM s_tb_Rights a INNER JOIN s_tb_FunctionNew b ON a.FuncId = b.FuncID" +
" where a.TeacherID in (select @teacherID" +
" union Select GroupID from tbGroupInfo where teacherID=@teacherID)";
return(bll.FillListByText <UserAuth>(sql, new { teacherID = teacher }));
}
}
public static IList <GradeCode> GetGradeCodeByTeacherId(int micyear, string teacherid)
{
using (AppBLL bll = new AppBLL())
{
var sql = "SELECT a.ClassID as GradeNo, c.GradeBriefName+'('+substring(a.ClassID,3,2)+')班' as GradeName" +
" FROM tbTeacherClass a INNER JOIN tbGradeClass b ON a.AcademicYear = b.AcadEmicYear AND " +
" a.ClassID = b.ClassNo INNER JOIN tdGradeCode c ON b.GradeNo = c.GradeNo" +
" where a.AcademicYear=@micyear and a.teacherID=@teacherid Group by a.ClassID,c.GradeBriefName";
return(bll.FillListByText <GradeCode>(sql, new { micyear = micyear, teacherid = teacherid }));
}
}
public static IList <GradeCourse> GetGradeCourseByTeacherId(int micyear, string teacherid)
{
using (AppBLL bll = new AppBLL())
{
var sql = "select a.CourseCode,b.FullName from tbTeacherClass as A,tdCourseCode as B" +
" where A.CourseCode=B.CourseCode and teacherid=@teacherid" +
" and a.Academicyear=@micyear" +
" group by A.coursecode,b.FullName";
return(bll.FillListByText <GradeCourse>(sql, new { micyear = micyear, teacherid = teacherid }));
}
}
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 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));
}
}
public static IList <GradeCourse> GetGradeCourse(int micYear, GradeCode gradeCode)
{
using (AppBLL bll = new AppBLL())
{
var sql = "SELECT a.Academicyear,a.GradeNo, a.CourseCode, b.FullName"
+ " FROM tbCourseUse a LEFT OUTER JOIN"
+ " tdCourseCode b ON a.coursecode = b.CourseCode"
+ " where a.Academicyear=@micYear"
+ " and a.GradeNo=@gradeNo";
return(bll.FillListByText <GradeCourse>(sql, new { micYear = micYear, gradeNo = gradeCode.GradeNo }));
}
}
/// <summary>
/// 查找最新的app下载
/// </summary>
private void fetchingLastVersionOfApp()
{
using (var apps = new AppBLL())
{
var app = apps.FindList <TB_Application>(f => f.Useable == true, "CreateTime", true).FirstOrDefault();
if (null != app)
{
download.HRef = app.Download;
download.Title = app.Description_en;
}
}
}
public static int ChangePwd(string teacherID, string oldPwd, string newPwd, int status)
{
using (AppBLL bll = new AppBLL())
{
ChangePwdEntity entity = new ChangePwdEntity()
{
TeacherID = teacherID, OldPwd = oldPwd, NewPwd = newPwd, Status = status, Result = 0
};
bll.ExecuteNonQuery("USP_System_ChangePwd", entity);
return(entity.Result);
}
}
/// <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 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 mf_getTable()
{
using (AppBLL bll = new AppBLL())
{
int tempStr = 1;
DataTable table = bll.FillDataTable("p_getTableName", null);
if (table.Rows.Count > 0)
{
tempStr = int.Parse(table.Rows[0][0].ToString()) + 1;
}
return(string.Format("s_tb_TempScore{0}", tempStr));
}
}
public static IList <GradeCourse> GetGradeCourse(GradeCode gradeCode, int flag)
{
using (AppBLL bll = new AppBLL())
{
var sql = "SELECT a.Academicyear,a.GradeNo, a.CourseCode, b.FullName" +
" FROM tbCourseUse a LEFT OUTER JOIN" +
" tdCourseCode b ON a.coursecode = b.CourseCode" +
" where (@flag=-1 or b.IsDownLoad = @flag)" +
" and a.GradeNo=@gradeNo" +
" group by a.Academicyear,a.gradeno,a.coursecode, b.FullName";
return(bll.FillListByText <GradeCourse>(sql, new { gradeNo = gradeCode.GradeNo, flag = flag }));
}
}
public static IList <Academicyear> GetAcademicyear()
{
using (AppBLL bll = new AppBLL())
{
var sql = "select academicyear as MicYear from tbGradeClass group by academicyear";
IList <Academicyear> years = bll.FillListByText <Academicyear>(sql, null);
if (years == null || !years.Any())
{
sql = "SELECT AcademicYear as MicYear FROM tbSchoolBaseInfo";
years = bll.FillListByText <Academicyear>(sql, null);
}
return(years);
}
}
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 IList <TestType> GetTestTypeByCourse(int micYear, GradeCourse gradeCourse, GradeCode gradeCode)
{
using (AppBLL bll = new AppBLL())
{
var sql = " SELECT a.TestType as Code, b.TypeName as Name"
+ " FROM s_tb_testlogin a INNER JOIN"
+ " s_tb_TestTypeInfo b ON a.TestType = b.TestType"
+ " where a.AcademicYear=@micYear"
+ " and a.coursecode in ('00000', @courseCode)"
+ " and a.GradeNo in ('00',@gradeNo)"
+ " group by a.testtype,b.typename "
+ " order by a.testtype ";
return(bll.FillListByText <TestType>(sql, new { micYear = micYear, courseCode = gradeCourse.CourseCode, gradeNo = gradeCode.GradeNo }));
}
}
public static FuncEntry GetFuncTree()
{
using (AppBLL bll = new AppBLL())
{
var sql = "Select Cast(FuncId as int) as FuncId, FuncName, Description, FuncType, Cast(FuncID0 as int) As Parent, SysNo from s_tb_FunctionNew Where FuncName!='-' order by FuncID";
IList <FuncEntry> funcs = bll.FillListByText <FuncEntry>(sql, null);
var functions = from v in funcs where v.Parent == -1 select v;
if (!functions.Any())
{
return(null);
}
var rootFunc = functions.First();
BuildChild(rootFunc, funcs);
return(rootFunc);
}
}
public static IList <GradeCourse> GetCourseByYear(int academicYear)
{
try
{
using (AppBLL bll = new AppBLL())
{
var sql = "SELECT a.CourseCode, b.FullName FROM tbCourseUse a LEFT OUTER JOIN tdCourseCode b ON a.coursecode = b.CourseCode "
+ " where a.Academicyear=@micYear group by A.coursecode,b.FullName order by A.Coursecode";
return(bll.FillListByText <GradeCourse>(sql, new { micYear = academicYear }));
}
}
catch (Exception ex)
{
throw ex;
}
}
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 IList <GradeClass> GetGradeClassByYear(Academicyear academicYear)
{
try
{
using (AppBLL bll = new AppBLL())
{
var sql = "select GradeBriefName+'('+substring(classNo,3,2)+')班' AS GradeBriefName,ClassNo" +
" from tdGradeCode g,tbGradeClass c " +
" where c.gradeno=g.GradeNo and c.academicyear=2013";
return(bll.FillListByText <GradeClass>(sql, new { micYear = academicYear.MicYear }));
}
}
catch (Exception ex)
{
throw ex;
}
}
public int Usp_User_Register(UserEntity user)
{
try
{
using (AppBLL bll = new AppBLL())
{
DataTable table = bll.FillDataTable("Usp_User_GetByMobile", new { C_Mobile = user.C_Mobile });
/// 手机号码已经注册
if (table.Rows.Count > 0) return -2;
user.I_Flag = 1;
user.D_Create = DateTime.Now;
user.C_Login = user.C_Mobile;
user.C_Password = SHA256(user.C_Password);
WriteLog("用户注册", user);
return bll.ExecuteNonQuery("Usp_User_Insert", user);
}
}
catch (Exception ex)
{
return -1;
}
}
/// <summary>
/// 流程审核
/// </summary>
/// <param name="flowEngine">流程</param>
/// <param name="auditType">审核类型</param>
/// <param name="auditDesc">审核描述</param>
/// <param name="userId">Sys_User.Id</param>
/// <returns>true 审核成功 false 不能审核</returns>
public bool Audit(FlowEngine flowEngine, AuditType auditType, string auditDesc, int userId)
{
using (AppBLL bll = new AppBLL())
{
Participant participant = null;
Conclusion conclusion = null;
IList<FunctionEntity> userFuncs = bll.FillList<FunctionEntity>("Usp_Funcs_ByUser", new { UserId = userId });
foreach (var userFunc in userFuncs)
{
participant = new Participant() { Category = 1, Department = -1, Reference = (long)userFunc.Id };
conclusion = new Conclusion(auditType, (long)userId, DateTime.Now) { Description = auditDesc };
if (flowEngine.Audit(conclusion, participant))
{
return true;
}
}
participant = new Participant() { Category = 0, Department = userId, Reference = -1 };
conclusion = new Conclusion(auditType, userId, DateTime.Now) { Description = auditDesc };
if (flowEngine.Audit(conclusion, participant))
{
return true;
}
}
return false;
}
/// <summary>
/// 判断是否属于当前人审核
/// </summary>
/// <param name="flowEngine"></param>
/// <param name="userId"></param>
/// <returns></returns>
public bool CanAudit(FlowEngine flowEngine, int userId)
{
Step current = flowEngine.GetCurrent();
if (current == null || current.NodeValidCount == 0) return false;
IList<Node> nodes = current.Nodes;
using (AppBLL bll = new AppBLL())
{
IList<FunctionEntity> userFuncs = bll.FillList<FunctionEntity>("Usp_Funcs_ByUser", new { UserId = userId });
foreach (var userFunc in userFuncs)
{
var auditNodes = from node in nodes
where node.Participant.Reference == userFunc.Id || node.Participant.Department == userId
select node;
if (auditNodes.Any()) return true;
}
}
return false;
}