/// <summary> /// 获取指定课程ID的学习结业考试 /// </summary> /// <param name="id"></param> /// <param name="ClassId"></param> /// <param name="AccountId"></param> /// <returns></returns> public List<Course_ActivityLearn> GetLearnExamInfo(int id, int ClassId, int AccountId) { string sql = @"select distinct C.id as id,C.title as title,C.TimeLength as TimeLength,C.TestCnt as TestCnt,C.display as display,C.FinalTestLimit as FinalTestLimit,(case when OpenTime=0 then 0 else dateadd(DAY,OpenTime,OpenClassFrom) end) as OpenDate,(case when EndTime=0 then 0 else dateadd(DAY,EndTime,OpenClassFrom) end) as EndDate,D.Status as Status from dbo.[Traning_Detail] as A left join [dbo].[Course_UnitDetail] as B on B.trainingid = A.id and B.Delflag = 0 left join [dbo].[Course_UnitContent] as C on C.unitid = B.id and C.Delflag = 0 and C.Delflag = 0 left join Member_ClassUnitContentSchedule as D on D.UnitContent = C.Id and D.Delflag =0 and D.AccountId=@AccountId left join Class_Detail F on F.traningid = B.trainingid and F.Delflag = 0 and F.Id=@ClassId where A.Delflag = 0 and A.Id=@Id and C.UnitType=6 order by C.id"; SqlParameter[] cmdParams = new SqlParameter[]{ new SqlParameter("@Id", SqlDbType.Int, 4) { Value = id }, new SqlParameter("@ClassId", SqlDbType.Int, 4) { Value = ClassId }, new SqlParameter("@AccountId", SqlDbType.Int, 4) { Value = AccountId } }; List<Course_ActivityLearn> list = new List<Course_ActivityLearn>(); using (IDataReader reader = MSEntLibSqlHelper.ExecuteDataReaderBySql(sql, cmdParams)) { while (reader.Read()) { Course_ActivityLearn model = new Course_ActivityLearn(); model.Id = Convert.ToInt32(reader["id"]); model.Title = reader["title"].ToString(); model.TimeLength = Convert.ToInt32(reader["TimeLength"]); model.TestCnt = Convert.ToInt32(reader["TestCnt"]); model.Display = Convert.ToBoolean(reader["display"]); model.FinalTestLimit = Convert.ToBoolean(reader["FinalTestLimit"]); if (!string.IsNullOrEmpty(reader["Status"].ToString())) { model.Status = Convert.ToBoolean(reader["Status"]); } if (!string.IsNullOrEmpty(reader["OpenDate"].ToString())) { if (Convert.ToDateTime(reader["OpenDate"]).Year > 1900) { model.OpenDate = Convert.ToDateTime(reader["OpenDate"]); } else { model.OpenDate = DateTime.Now; } } if (!string.IsNullOrEmpty(reader["EndDate"].ToString())) { if (Convert.ToDateTime(reader["EndDate"]).Year > 1900) { model.EndDate = Convert.ToDateTime(reader["EndDate"]); } else { model.EndDate = DateTime.Now.AddYears(100); } } list.Add(model); } } return list; }
/// <summary> /// 获取指定章节的学习活动信息 /// </summary> /// <param name="id"></param> /// <param name="ClassId"></param> /// <param name="AccountId"></param> /// <returns></returns> public List<Course_ActivityLearn> GetActivityLearn(int id, int ClassId, int AccountId) { string sql = @"select y.id,title,content,unittype,timelength,display,OpenDate,EndDate,Status,count(mcr.Id)as mcrcount from ( select A.id as id,A.title as title,A.content as content,unittype,timelength,A.display as display,(case when OpenTime=0 then 0 else dateadd(DAY,OpenTime,OpenClassFrom) end) as OpenDate,(case when EndTime=0 then 0 else dateadd(DAY,EndTime,OpenClassFrom) end) as EndDate,D.Status as Status ,a.Sort,D.AccountId from [dbo].[Course_UnitContent] as A left join Course_UnitDetail as B on B.Id = A.UnitId and B.Delflag =0 left join Class_Detail C on C.TraningId= B.TrainingId and C.Delflag = 0 and C.Id=@ClassId left join Member_ClassUnitContentSchedule as D on D.UnitContent = A.Id and D.Delflag =0 and D.AccountId=@AccountId where unitid=@Id and A.Delflag=0 and A.Display = 1 ) y left join Member_ClassContentTimeRecord mcr on y.id=mcr.UnitContent where mcr.AccountId=y.AccountId group by y.id,title,content,unittype,timelength,display,OpenDate,EndDate,Status,Sort order by y.Sort"; SqlParameter[] cmdParams = new SqlParameter[]{ new SqlParameter("@Id", SqlDbType.Int, 4) { Value = id }, new SqlParameter("@ClassId", SqlDbType.Int, 4) { Value = ClassId }, new SqlParameter("@AccountId", SqlDbType.Int, 4) { Value = AccountId } }; List<Course_ActivityLearn> list = new List<Course_ActivityLearn>(); using (IDataReader reader = MSEntLibSqlHelper.ExecuteDataReaderBySql(sql, cmdParams)) { while (reader.Read()) { Course_ActivityLearn model = new Course_ActivityLearn(); model.Id = Convert.ToInt32(reader["id"]); model.Title = reader["title"].ToString(); model.Content = reader["content"].ToString(); model.UnitType = Convert.ToInt32(reader["unittype"]); model.TimeLength = Convert.ToInt32(reader["timelength"]); model.Display = Convert.ToBoolean(reader["display"]); if (!string.IsNullOrEmpty(reader["Status"].ToString())) { model.Status = Convert.ToBoolean(reader["Status"]); } if (!string.IsNullOrEmpty(reader["OpenDate"].ToString())) { if (Convert.ToDateTime(reader["OpenDate"]).Year > 1900) { model.OpenDate = Convert.ToDateTime(reader["OpenDate"]); } else { model.OpenDate = DateTime.Now; } } if (!string.IsNullOrEmpty(reader["EndDate"].ToString())) { if (Convert.ToDateTime(reader["EndDate"]).Year > 1900) { model.EndDate = Convert.ToDateTime(reader["EndDate"]); } else { model.EndDate = DateTime.Now.AddYears(100); } } if (!string.IsNullOrEmpty(reader["mcrcount"].ToString())) { model.Mcrcount = Convert.ToInt32(reader["mcrcount"]); } list.Add(model); } } return list; }