/// <summary> /// 获取文件路径 /// </summary> /// <param name="classId"></param> /// <param name="classIndex"></param> /// <returns></returns> public static List <vw_ClassListJob> ClassListJob(string classid, int classindex) { string table = string.Empty, fields = string.Empty, orderby = string.Empty, where = string.Empty; //定义结构 fields = @" * "; //输出字段 table = @" vw_ClassListJob "; //表或者视图 orderby = "id"; //排序信息 StringBuilder sb = new StringBuilder(); //构建where条件 sb.Append("select * from vw_ClassListJob where "); sb.Append(" 1=1 "); if (!string.IsNullOrWhiteSpace(classid))//班级ID { sb.Append(" and classid = @ClassID"); } if (classindex != 0)//班级行号 { sb.Append(" and classindex = @ClassIndex "); } sb.Append(" order by id "); var parameters = new DynamicParameters(); parameters.Add("@classid", classid); parameters.Add("@classindex", classindex); return(MsSqlMapperHepler.SqlWithParams <vw_ClassListJob>(sb.ToString(), parameters, DBKeys.PRX)); }
/// <summary> /// 根据班级ID获取学员考勤信息 /// </summary> /// <param name="classid"></param> /// <returns></returns> public static List <vw_AttendanceRecord> GetAttendanceRecordByClassID(string classid, int ClassIndex) { string strsql = "select * from vw_AttendanceRecord where ClassID = '" + classid + "' and ClassIndex = " + ClassIndex; List <vw_AttendanceRecord> ret = new List <vw_AttendanceRecord>(); ret = MsSqlMapperHepler.SqlWithParams <vw_AttendanceRecord>(strsql, null, DBKeys.PRX); return(ret); }
/// <summary> /// 获取启用课程下拉 /// </summary> /// <returns></returns> public static List <CommonEntity> GetCourseIL() { List <CommonEntity> ret; string sql = "select id,CourseName as name from Course"; ret = MsSqlMapperHepler.SqlWithParams <CommonEntity>(sql.ToString(), null, DBKeys.PRX); return(ret); }
/// <summary> /// 跳转查询账号信息 /// </summary> /// <param name="apid"></param> /// <returns></returns> public static List <SYSAccount> GetAccounts_Update(string ACC_Account) { String sql = "select * from SYS_Account where ACC_Account = @ACC_Account "; var dynamic = new DynamicParameters(); dynamic.Add("@ACC_Account", ACC_Account); return(MsSqlMapperHepler.SqlWithParams <SYSAccount>(sql, dynamic, DBKeys.PRX)); }
/// <summary> /// 根据预约ID获取更近记录 /// </summary> /// <param name="apid"></param> /// <returns></returns> public static List <FollowRecord> GetFollowListByAPID(string apid) { string sql = @" select * from FollowRecord where APID = @APID order by id desc"; var parameters = new DynamicParameters(); parameters.Add("@APID", apid); return(MsSqlMapperHepler.SqlWithParams <FollowRecord>(sql, parameters, DBKeys.PRX)); }
/// <summary> /// 获取获取试听,判断是否有重复试听记录 /// </summary> /// <param name="apid"></param> /// <returns></returns> public static List <vw_Enroll> GetEnrollPrintByApidAndClassid(string apid, string classid) { String sql = "select * from vw_Enroll where APID = @APID and ClassID = @ClassID"; var dynamic = new DynamicParameters(); dynamic.Add("@APID", apid); dynamic.Add("@ClassID", classid); return(MsSqlMapperHepler.SqlWithParams <vw_Enroll>(sql, dynamic, DBKeys.PRX)); }
/// <summary> /// 获取班级字典信息,不包括试听 /// </summary> /// <param name="dicTypeID"></param> /// <returns>用于下拉的绑定项目</returns> public static List <CommonEntity> GetClassesItemList() { StringBuilder sb = new StringBuilder(); sb.Append(" select ID,ClassName name from Classes"); sb.Append(" WHERE TeachTypeID <> 1 and StateID = 1"); var parameters = new DynamicParameters(); return(MsSqlMapperHepler.SqlWithParams <CommonEntity>(sb.ToString(), parameters, DBKeys.PRX)); }
public static List <vw_Menu> GetMenuList(string loginid) { StringBuilder sb = new StringBuilder(); sb.AppendFormat("SELECT *,'{0}' + URL as logurl ", preurl); sb.AppendFormat(" FROM [vw_StaffMenu] a WITH(NOLOCK)"); sb.AppendFormat(" where ACC_Account = '{0}'", loginid); sb.AppendFormat(" ORDER BY ParentId,OrderIndex"); return(MsSqlMapperHepler.SqlWithParams <vw_Menu>(sb.ToString(), null, DBKeys.PRX)); }
/// <summary> /// 获取优惠下拉 /// </summary> /// <returns></returns> public static List <Discount> GetDiscountItems() { StringBuilder sb = new StringBuilder(); sb.Append("SELECT * "); sb.Append(" FROM [Discount] a WITH(NOLOCK)"); sb.Append(" WHERE a.StateID <> 2"); sb.Append(" ORDER BY CreateTime desc"); return(MsSqlMapperHepler.SqlWithParams <Discount>(sb.ToString(), null, DBKeys.PRX)); }
/// <summary> /// 获取字典列表 /// </summary> /// <param name="dicTypeID"></param> /// <returns>用于下拉的绑定项目</returns> public static List <string> GetSYS_SystemRoleList_ROLE_Id(int ROLE_Id) { StringBuilder sb = new StringBuilder(); sb.Append("SELECT ROLE_Name as name FROM SYS_SystemRole"); sb.Append(" WHERE ROLE_Id = @ROLE_Id"); var parameters = new DynamicParameters(); parameters.Add("@ROLE_Id", ROLE_Id); return(MsSqlMapperHepler.SqlWithParams <string>(sb.ToString(), parameters, DBKeys.PRX)); }
/// <summary> /// 获取字典列表 /// </summary> /// <param name="dicTypeID"></param> /// <returns>用于下拉的绑定项目</returns> public static List <SYS_Role> GetSYS_SystemRoleList(int ROLE_OrderIndex) { StringBuilder sb = new StringBuilder(); sb.Append("SELECT ROLE_Id as id,ROLE_Name as name FROM SYS_SystemRole"); sb.Append(" WHERE ROLE_OrderIndex = @ROLE_OrderIndex"); var parameters = new DynamicParameters(); parameters.Add("@ROLE_OrderIndex", ROLE_OrderIndex); return(MsSqlMapperHepler.SqlWithParams <SYS_Role>(sb.ToString(), parameters, DBKeys.PRX)); }
/// <summary> /// 甲方获取所报班级的名称 /// </summary> /// <param name="dicTypeID"></param> /// <returns>用于下拉的绑定项目</returns> public static List <vw_Enroll> Getvw_EnrollList(string StudentID) { StringBuilder sb = new StringBuilder(); sb.Append("select ID,ClassName as name,ClassHour,UsedHour from vw_Enroll"); sb.Append(" where TeachTypeID<>1 and StudentID=@StudentID"); var parameters = new DynamicParameters(); parameters.Add("@StudentID", StudentID); return(MsSqlMapperHepler.SqlWithParams <vw_Enroll>(sb.ToString(), parameters, DBKeys.PRX)); }
/// <summary> /// 新增,返回的是主键 /// </summary> /// <param name="btn"></param> /// <returns></returns> // public static bool AddClassList(ClassList Clas,Date date,Classes Clss,Weekday weekday) //{ // var number = 0;//因为事务查询数据库会直接卡死 // bool ret = false; // DBRepository db = new DBRepository(DBKeys.PRX); // try // { // db.BeginTransaction();//事务开始 // var lenthed = date.End_Date.Subtract(date.Start_Date).Days; //取日期之间的差距天数 // for (int i = 0; i <= lenthed; i++) // { // var Date = date.Start_Date.AddDays(i); // var week = Convert.ToInt32(Date.DayOfWeek); //这个时间是星期几 // int Monday=-1; // int Tuesday = -1; // int Wednesday = -1; // int Thursday = -1; // int Friday = -1; // int Saturday = -1; // int Sunday = -1; // if (!string.IsNullOrWhiteSpace(weekday.Monday)) // { // Monday = week.ToString().IndexOf(weekday.Monday); //判断是否包含在星期里面 // } // if (!string.IsNullOrWhiteSpace(weekday.Tuesday)) // { // Tuesday = week.ToString().IndexOf(weekday.Tuesday); //判断是否包含在星期里面 // } // if (!string.IsNullOrWhiteSpace(weekday.Wednesday)) // { // Wednesday = week.ToString().IndexOf(weekday.Wednesday); //判断是否包含在星期里面 // } // if (!string.IsNullOrWhiteSpace(weekday.Thursday)) // { // Thursday = week.ToString().IndexOf(weekday.Thursday); //判断是否包含在星期里面 // } // if (!string.IsNullOrWhiteSpace(weekday.Friday)) // { // Friday = week.ToString().IndexOf(weekday.Friday); //判断是否包含在星期里面 // } // if (!string.IsNullOrWhiteSpace(weekday.Saturday)) // { // Saturday = week.ToString().IndexOf(weekday.Saturday); //判断是否包含在星期里面 // } // if (!string.IsNullOrWhiteSpace(weekday.Sunday)) // { // Sunday = week.ToString().IndexOf(weekday.Sunday); //判断是否包含在星期里面 // } // if (Monday > -1 || Tuesday > -1 || Wednesday > -1 || Thursday > -1 || Friday > -1 || Saturday > -1 || Sunday > -1) // { // if (number==0) // { // number = Getnumber(Clas.ClassID);//取行号 // } // number = number + 1; // Clas.weekday = week;//星期几 // Clas.ClassDate = Date;//上课日期 // Clas.ClassIndex = number; //班次序号,也就是班级生成的集体上课记录 // db.Insert<ClassList>(Clas); //增加排课表数据 // // MsSqlMapperHepler.Insert<ClassList>(Clas, DBKeys.PRX); //增加排课表数据 // List<Enroll> Enroll = GetEnrollByID(Clas.ClassID);//获取Enroll报名表的数据 // AttendanceRecord attend = new AttendanceRecord(); // attend.CreateTime = DateTime.Now; //创建时间 // attend.CreatorId = Clas.CreatorId; //创建人 // attend.ClassID = Clas.ClassID;//班级编号 // attend.ClassIndex = number;//班次序号,也就是班级生成的集体上课记录 // attend.AttendanceTypeID = 1;//上课状态,默认为1,未考勤 // attend.AttendanceWayID = 3;//AttendanceWayID默认3,教师操作的考勤。 // for (int j = 0; j < Enroll.Count(); j++) // { // attend.StudentID = Enroll[j].StudentID; // db.Insert<AttendanceRecord>(attend);//增加上课记录表数据 // //MsSqlMapperHepler.Insert<AttendanceRecord>(attend, DBKeys.PRX); //增加上课记录表数据 // } // } // } // if (UpdateClasses(Clas.ClassID, Clss.TotalLesson,db) > 0)//反写回Classes班级维护表的总课时字段 // { // db.Commit(); //事务提交 // ret = true;//新增成功 // } // db.Dispose(); //资源释放 // } // catch (Exception ex) // { // db.Rollback(); // db.Dispose();//资源释放 // throw new Exception(ex.Message + "。" + ex.InnerException.Message); // } // return ret; //} /// <summary> /// 获取Enroll表数据 /// </summary> /// <param name="ID"></param> /// <returns></returns> //public static Enroll GetEnrollByID(string ClassID) //{ // return MsSqlMapperHepler.GetOne<Enroll>(ClassID, DBKeys.PRX); //} public static List <Enroll> GetEnrollByID(string ClassID) { StringBuilder sb = new StringBuilder(); sb.Append("select * from Enroll "); sb.Append(" where ClassID=@ClassID "); var parameters = new DynamicParameters(); parameters.Add("@ClassID", ClassID); return(MsSqlMapperHepler.SqlWithParams <Enroll>(sb.ToString(), parameters, DBKeys.PRX)); }
/// <summary> /// 获取学生字典列表 /// </summary> /// <param name="dicTypeID"></param> /// <returns>用于下拉的绑定项目</returns> public static List <CommonEntity> GetStudentsList() { StringBuilder sb = new StringBuilder(); sb.Append("select id,name from Students"); sb.Append(" WHERE 1=@one"); var parameters = new DynamicParameters(); var one = 1; parameters.Add("@one", one); return(MsSqlMapperHepler.SqlWithParams <CommonEntity>(sb.ToString(), parameters, DBKeys.PRX)); }
/// <summary> /// 获取分校字典列表 /// </summary> /// <param name="dicTypeID"></param> /// <returns>用于分校下拉的绑定项目</returns> public static List <CommonEntity> Get_SYS_Company_List() { StringBuilder sb = new StringBuilder(); sb.Append(" select COMP_Code as ID,COMP_Name as name from SYS_Company "); sb.Append(" WHERE 1 = @StateID"); var parameters = new DynamicParameters(); var StateID = 1; parameters.Add("@StateID", StateID); return(MsSqlMapperHepler.SqlWithParams <CommonEntity>(sb.ToString(), parameters, DBKeys.PRX)); }
/// <summary> /// 获取课程字典列表 /// </summary> /// <param name="dicTypeID"></param> /// <returns>用于下拉的绑定项目</returns> public static List <CommonEntity> GetCourseList() { StringBuilder sb = new StringBuilder(); sb.Append(" select ID,CourseName name from Course"); sb.Append(" WHERE StateID <> @StateID"); var parameters = new DynamicParameters(); var StateID = 2; parameters.Add("@StateID", StateID); return(MsSqlMapperHepler.SqlWithParams <CommonEntity>(sb.ToString(), parameters, DBKeys.PRX)); }
/// <summary> /// 刷新排课记录,主要用于补漏 /// </summary> public static bool RefreshClassList(string classid, string loginid) { bool ret = false; DBRepository db = new DBRepository(DBKeys.PRX); string strsql = "SELECT * FROM dbo.Enroll WHERE ClassID = '" + classid + "'"; List <Enroll> enlist = MsSqlMapperHepler.SqlWithParams <Enroll>(strsql, null, DBKeys.PRX);//找到未报名记录 Classes cl = db.GetById <Classes>(classid); db.BeginTransaction();//事务开始 try { foreach (var en in enlist) { if (cl.StateID.Value == 2 || cl.StateID.Value == 3)//如果班级状态是已排课,或已上课,生成课程表AttendanceRecord { List <vw_ClassAttendanceList> clist = new List <vw_ClassAttendanceList>(); clist = db.Query <vw_ClassAttendanceList>("select * from vw_ClassAttendanceList where ClassID = '" + cl.ID + "'", null).ToList(); //int aa = Convert.ToInt32(en.ClassHour) < clist.Count() ? Convert.ToInt32(en.ClassHour) : clist.Count();//取较小数做循环 int aa = clist.Count();//循环全部 for (int i = 0; i < aa; i++) { AttendanceRecord attendold = db.Query <AttendanceRecord>("select * from AttendanceRecord where StudentID = '" + en.StudentID + "' and ClassID = '" + en.ClassID + "' and ClassIndex = " + (i + 1), null).FirstOrDefault(); AttendanceRecord attend = new AttendanceRecord(); attend.CreateTime = DateTime.Now; //创建时间 attend.CreatorId = loginid; //创建人 attend.ClassID = en.ClassID; //班级编号 attend.ClassIndex = i + 1; //班次序号,也就是班级生成的集体上课记录 attend.AttendanceTypeID = 1; //上课状态,默认为1,未考勤 attend.StudentID = en.StudentID; //学员号 if (attendold == null) { db.Insert <AttendanceRecord>(attend);//增加上课记录表数据 } } } //string delstr = "delete from AttendanceRecord where " } db.Commit(); db.Dispose(); ret = true; } catch (Exception ex) { db.Rollback(); db.Dispose(); throw new Exception(ex.Message); } return(ret); }
/// <summary> /// 获取班级下拉 /// </summary> /// <param name="dicTypeID"></param> /// <returns>班级用于下拉的绑定项目</returns> public static List <CommonEntity> GetClassesList(int CourseID, string ClassID) { StringBuilder sb = new StringBuilder(); sb.Append("select distinct cl.id+','+CAST(cll.ClassIndex as nvarchar(10)) as id,cl.ClassName+'('+convert(varchar(100), cll.ClassDate ,23)+')' as Name"); sb.Append(" from Classes cl inner join ClassList cll"); sb.Append(" on cl.ID=cll.ClassID "); sb.Append(" where cl.StateID <> 4 and cl.CourseID = @CourseID and cl.id <> @ClassID"); var parameters = new DynamicParameters(); parameters.Add("@CourseID", CourseID); parameters.Add("@ClassID", ClassID); return(MsSqlMapperHepler.SqlWithParams <CommonEntity>(sb.ToString(), parameters, DBKeys.PRX)); }
/// <summary> /// 根据角色的权限来获取相应的留言信息 /// </summary> /// <param name="roleid"></param> /// <returns></returns> public static List <vw_Message> GetMessageList(List <string> roleids) { StringBuilder where = new StringBuilder(); foreach (string rid in roleids) { where.AppendFormat("or ToRoles like '%{0}%'", rid); } StringBuilder sql = new StringBuilder(); sql.AppendFormat(@"select top 100 * from vw_Message where 1=1 {0} {1}", where.ToString(), "order by CreateTime desc"); List <vw_Message> ret = MsSqlMapperHepler.SqlWithParams <vw_Message>(sql.ToString(), null, DBKeys.PRX); return(ret); }
/// <summary> /// 获取字典列表 /// </summary> /// <param name="dicTypeID"></param> /// <returns>因为学员状态有重新的名称,所以单独写一个下拉的绑定</returns> public static List <CommonEntity> GetDictionary_StudentSource_List(int DicTypeID) { StringBuilder sb = new StringBuilder(); sb.Append("SELECT DicItemID id,DicItemName name"); sb.Append(" FROM DictionaryItem a WITH(NOLOCK)"); sb.Append(" INNER JOIN DictionaryType b WITH(NOLOCK) ON a.DicTypeID = b.DicTypeID"); sb.Append(" WHERE b.DicTypeID = @DicTypeID and a.recordState <> '2' AND a.DicItemID < > 0 AND a.DicItemID <> 1"); sb.Append(" ORDER BY Sort"); var parameters = new DynamicParameters(); parameters.Add("@DicTypeID", DicTypeID); return(MsSqlMapperHepler.SqlWithParams <CommonEntity>(sb.ToString(), parameters, DBKeys.PRX)); }
/// <summary> ///乙方获取所报班级的名称 /// </summary> /// <param name="dicTypeID"></param> /// <returns>用于下拉的绑定项目</returns> public static List <CommonEntity> GetClasses(string StudentID) { StringBuilder sb = new StringBuilder(); sb.Append("select ClassID as ID,ClassName+'-已报'as Name from vw_Enroll"); sb.Append(" where StudentID=@StudentID"); sb.Append(" union "); sb.Append(" select ID,ClassName as Name from vw_Classes"); sb.Append(" where StateID<>4 and ID not in "); sb.Append(" ( select ClassID from vw_Enroll "); sb.Append(" where StudentID=@StudentID ) "); var parameters = new DynamicParameters(); parameters.Add("@StudentID", StudentID); return(MsSqlMapperHepler.SqlWithParams <CommonEntity>(sb.ToString(), parameters, DBKeys.PRX)); }
/// <summary> /// 获取分校字典列表 /// </summary> /// <param name="dicTypeID"></param> /// <returns>用于分校下拉的绑定项目</returns> public static List <CommonEntity> Get_SYS_Company_COMP_Code(string COMP_Code) { StringBuilder sb = new StringBuilder(); if (string.IsNullOrEmpty(COMP_Code) || COMP_Code == "1") { sb.Append("SELECT '' AS ID,'--请选择--' AS name UNION ALL select COMP_Code as ID,COMP_Name as name from SYS_Company "); sb.Append(" WHERE 1 = 1 "); } else { sb.Append("select COMP_Code as ID,COMP_Name as name from SYS_Company "); sb.Append(" WHERE COMP_Code = @COMP_Code"); } var parameters = new DynamicParameters(); parameters.Add("@COMP_Code", COMP_Code); return(MsSqlMapperHepler.SqlWithParams <CommonEntity>(sb.ToString(), parameters, DBKeys.PRX)); }
/// <summary> /// 获取对学生评价 /// </summary> /// <param name="classId"></param> /// <param name="classIndex"></param> /// <returns></returns> public static List <vw_StudentEvaluate> getStudentEvaluate(String classId, int classIndex) { string table = string.Empty, fields = string.Empty, orderby = string.Empty, where = string.Empty; //定义结构 fields = @" * "; //输出字段 table = @" vw_StudentEvaluate "; //表或者视图 orderby = "StudentID"; //排序信息 StringBuilder sb = new StringBuilder(); //构建where条件 sb.Append("select * from vw_StudentEvaluate where "); sb.Append(" 1=1 "); if (!string.IsNullOrWhiteSpace(classId))//按钮中文名称 { sb.Append(" and ClassID = @ClassID"); } if (classIndex != 0)//按钮中文名称 { sb.Append(" and ClassIndex = @ClassIndex "); } sb.Append(" order by StudentID "); //if (!string.IsNullOrWhiteSpace(search.BTN_Name_En))//城市 // sb.AppendFormat(" and BTN_Name_En like '%{0}%' ", search.BTN_Name_En); var parameters = new DynamicParameters(); parameters.Add("@ClassID", classId); parameters.Add("@ClassIndex", classIndex); return(MsSqlMapperHepler.SqlWithParams <vw_StudentEvaluate>(sb.ToString(), parameters, DBKeys.PRX)); // var list = CommonPage<vw_StudentEvaluate>.GetPageList( //out allcount, table, fields: fields, where: where.Trim(), //orderby: orderby, pageindex: search.CurrentPage, pagesize: search.PageSize, connect: DBKeys.PRX); // return new PagedList<vw_StudentEvaluate>(list, search.CurrentPage, search.PageSize, allcount); }
/// <summary> /// 获取学生的考勤情况 /// </summary> /// <param name="classId"></param> /// <param name="classIndex"></param> /// <returns></returns> public static List <AttendanceRecord> getStudentCheck(String classId, int classIndex) { /** * string sql = @" select * from FollowRecord where APID = @APID"; * var parameters = new DynamicParameters(); * parameters.Add("@APID", apid); * * */ StringBuilder sb = new StringBuilder();//构建where条件 // //sb.Append("select a.*,c.Name as Name,c.BindPhone as Phone ,"); //sb.Append(" (b.ClassHour-b.UsedHour) as LeftHour from AttendanceRecord a, Enroll b, Students c "); //sb.Append(" where a.StudentID=b.StudentID and b.StudentID = c.ID "); // sb.Append(@" select a.[ID] ,b.[StudentID] ,a.[ClassID] ,a.[ClassIndex] ,a.[ClockTime] ,a.[AttendanceTypeID] ,a.[AttendanceWayID] ,a.[Evaluate] ,a.[Remark] ,a.[CreateTime] ,a.[CreatorId] ,a.[OutStatus] ,a.[UpdateTime] ,a.[UpdatorId], c.Name as Name, c.BindPhone as Phone, (b.ClassHour-b.UsedHour) as LeftHour FROM Enroll b INNER JOIN ClassList e ON b.ClassID =e.ClassID inner join AttendanceRecord a on a.StudentID=b.StudentID AND a.ClassID=b.ClassID AND a.ClassIndex = e.ClassIndex LEFT join Students c on b.StudentID = c.ID WHERE 1=1 and b.StateID <> 6 and b.StateID <> 5 "); //去掉了冻结和完结的人数 if (!string.IsNullOrWhiteSpace(classId)) //按钮中文名称 { sb.Append(" and b.ClassID = @ClassID "); } if (classIndex != 0)//按钮中文名称 { sb.Append(" and e.ClassIndex = @ClassIndex "); } //if (!string.IsNullOrWhiteSpace(search.BTN_Name_En))//城市 // sb.AppendFormat(" and BTN_Name_En like '%{0}%' ", search.BTN_Name_En); var parameters = new DynamicParameters(); parameters.Add("@ClassID", classId); parameters.Add("@ClassIndex", classIndex); return(MsSqlMapperHepler.SqlWithParams <AttendanceRecord>(sb.ToString(), parameters, DBKeys.PRX)); // var list = CommonPage<vw_StudentEvaluate>.GetPageList( //out allcount, table, fields: fields, where: where.Trim(), //orderby: orderby, pageindex: search.CurrentPage, pagesize: search.PageSize, connect: DBKeys.PRX); // return new PagedList<vw_StudentEvaluate>(list, search.CurrentPage, search.PageSize, allcount); }
/// <summary> /// 处理原始考勤记录 /// </summary> public static void DealAttendanceOriginal(string operatorid) { string strsql = "select * from AttendanceOriginal where RecogniseTime is null"; List <AttendanceOriginal> aolist = MsSqlMapperHepler.SqlWithParams <AttendanceOriginal>(strsql, null, DBKeys.PRX);//获取所有未识别的数据 DBRepository db = new DBRepository(DBKeys.PRX); try { db.BeginTransaction(); foreach (AttendanceOriginal ao in aolist)//循环处理 { //智能匹配到这个学员报名过的,时间最接近的班 StringBuilder str = new StringBuilder(); str.AppendFormat(@"SELECT TOP 1 * from ClassList WHERE ABS(DATEDIFF(Hour,ClassDate,'{0}')) < 4 AND ClassID IN (SELECT ClassID FROM Enroll WHERE StudentID = {1}) ORDER BY ABS(DATEDIFF(Hour,ClassDate,'{0}'))", ao.workDates, ao.UserID); ClassList cl = db.Query <ClassList>(str.ToString()).FirstOrDefault(); //找到唯一班次 if (cl != null) //打卡正确,找到班次 { //是否之前有考勤,有过考勤记录则不处理 string strar = "select top 1 * from AttendanceRecord where ClassID = '" + cl.ClassID + "' and ClassIndex = " + cl.ClassIndex + " and StudentID = '" + ao.UserID + "'"; AttendanceRecord ar = db.Query <AttendanceRecord>(strar).FirstOrDefault(); if (ar == null)//不存在就新增 { ar = new AttendanceRecord(); ar.StudentID = ao.UserID; ar.ClassID = cl.ClassID; ar.ClassIndex = cl.ClassIndex; ar.ClockTime = null; ar.AttendanceTypeID = 1;//未考勤 ar.AttendanceWayID = null; ar.CreateTime = DateTime.Now; ar.CreatorId = operatorid; int arid = db.Insert(ar); ar = db.GetById <AttendanceRecord>(arid); } if (ar.AttendanceTypeID == 1) //还没考勤过 { ar.ClockTime = ao.workDates; ar.AttendanceTypeID = 2; //正常 ar.AttendanceWayID = 2; //设备考勤 ar.UpdateTime = DateTime.Now; ar.UpdatorId = operatorid; string stren = "select * from Enroll where StudentID = '" + ao.UserID + "' and ClassID = '" + cl.ClassID + "'"; Enroll en = db.Query <Enroll>(stren).FirstOrDefault();// 找到报名记录 if (en == null) { ao.Recognise = "无效"; ao.Remark = "没有报名记录"; ao.RecogniseTime = DateTime.Now; db.Update(ao); return; //throw new Exception("没有报名记录"); } else //扣掉学时 { //-----添加课时变化日志记录 begin TransferRecord tr = new TransferRecord(); //添加课时变化日志记录 tr.StudentID = en.StudentID; tr.BeforeHours = en.ClassHour - en.UsedHour; tr.AfterHours = en.ClassHour - en.UsedHour - 1; tr.TypeID = 4;//考勤机自动识别 tr.CreateTime = DateTime.Now; tr.CreatorId = operatorid; tr.ENID = en.ID; tr.ClassID = en.ClassID; tr.ClassIndex = ar.ClassIndex; db.Insert(tr); //-----添加课时变化日志记录 end en.UsedHour = en.UsedHour + 1; en.UpdateTime = DateTime.Now; en.UpdatorId = operatorid; db.Update(en); if (cl.StateID < 3) //班级状态如果没上课,设置成上课 { cl.StateID = 3; db.Update(cl); } ao.Recognise = "有效"; ao.Remark = "更新考勤记录,识别成功,当前剩余课时:" + (en.ClassHour - en.UsedHour).ToString(); ao.Classid = cl.ClassID; ao.ClassIndex = cl.ClassIndex; ao.RecogniseTime = DateTime.Now; db.Update(ao); } ar.Remark = ao.Remark; db.Update(ar); } else { ao.Recognise = "无效"; ao.Remark = "重复的考勤"; ao.RecogniseTime = DateTime.Now; ao.Classid = cl.ClassID; ao.ClassIndex = cl.ClassIndex; db.Update(ao); } } else//没找到对应的班次 { ao.Recognise = "无效"; ao.Remark = "未找到对应的班次"; ao.RecogniseTime = DateTime.Now; db.Update(ao); } } db.Commit(); db.Dispose(); } catch (Exception ex) { db.Rollback(); db.Dispose(); throw new Exception(ex.Message); } }
/// <summary> /// 根据报名号,返回课时消耗记录 /// </summary> /// <param name="enid"></param> /// <returns></returns> public static List <TransferRecord> GetHoursLogByENID(string enid) { string strsql = "select a.*,dbo.getDicNameByID(23,a.TypeID) AS TypeName from [TransferRecord] a where a.ENID = '" + enid + "'"; return(MsSqlMapperHepler.SqlWithParams <TransferRecord>(strsql, null, DBKeys.PRX)); }
/// <summary> /// 获取所有在职的教师 /// </summary> /// <returns></returns> public static List <Teachers> getOnWorkTeachers() { string strsql = "select * from Teachers where LeaveDate is null"; return(MsSqlMapperHepler.SqlWithParams <Teachers>(strsql, null, DBKeys.PRX)); }