/// <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;
}
