/// <summary>
/// 获取问题答案
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public List <Answer> GetAnswersByAsk(int id)
{
return(DbHelperSQL.ExcuteScaler <Answer>(@"
select t1.AnswerId,t4.UserName,t4.Logo, t1.content,t1.createtime,t1.isaccept,t1.answerid from answer t1
join teacher t2 on t1.teacherid = t2.teacherid
join member t4 on t2.memberid = t4.id where t1.askid=@0 order by isaccept desc", id));
}
public List <DataModel> GetVideoInfoListByCplId(string cplId)
{
var sqlstring = @"SELECT schr.ScheduleId,schr.RecordStartTime FROM ScheduleRecord schr INNER JOIN Schedule sch ON schr.ScheduleId=sch.ScheduleId WHERE sch.CplId=@0";
return(DbHelperSQL.ExcuteScaler <DataModel>(sqlstring, cplId));
// return null;
}
/// <summary>
/// 获取我的回答
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
public List <Answer> GetAnswers(int id)
{
return(DbHelperSQL.ExcuteScaler <Answer>(@"
select t3.content ask, t4.UserName, t1.content,t1.createtime from answer t1
join teacher t2 on t1.teacherid = t2.teacherid and t2.memberid=@0
join ask t3 on t1.askid = t3.askid
join member t4 on t3.memberid = t4.id order by t1.orderby,t1.createtime desc", id));
}
/// <summary>
/// 获取上课老师
/// </summary>
/// <param name="classId"></param>
/// <returns></returns>
public List <TeamTeature> GetClassTeacher(int classId)
{
object limitIds = ValidLimitTeacher(classId);
if (limitIds == null || limitIds.ToString() == "")
{
return(DbHelperSQL.ExcuteScaler <TeamTeature>(@"select t1.teacherid id,t1.Video,t3.cnname name,t3.enname,t1.signature,t3.logo from teacher t1 inner join member t3 on t1.memberid=t3.id and t1.enabled=1 and t1.teachertype=0 and t3.Enabled=1
inner join courseteacher t2 on t1.teacherid = t2.teacherid
inner join classplan t4 on t2.courseid = t4.courseid and t4.classid =@0 where t3.Enabled=1", classId));
}
else
{
return(DbHelperSQL.ExcuteScaler <TeamTeature>(@"select t1.teacherid id,t1.Video,t3.cnname name,t3.enname,t1.signature,t3.logo from teacher t1 inner join member t3 on t1.memberid = t3.id and t1.enabled = 1 and t1.teachertype = 0 and t3.Enabled=1 and t1.teacherid in (" + limitIds.ToString() + ")"));
}
}
/// <summary>
/// 获取学员直播地址
/// </summary>
/// <param name="cplId"></param>
/// <param name="courseType"></param>
/// <returns></returns>
public GenseeDirectSee GetMemberScheduleByCpl(int cplId, int courseType)
{//1）大课堂和公开测评课：AttendeeUrl
//2）一对一和一对六，公开课 PanelListUrl
// 3 CourseType 课程类型 0：收费课程，1：公开课
var sql = "select webcastid,CplId,{0} url,ScheduleId,{1} token,clength courselength,begintime begintime,endtime,isfull from Schedule where cplid=@0 and CourseType=@1";
//免费课 大课堂
if (courseType == 1 || IsDaketan(cplId))
{
sql = string.Format(sql, "AttendeeUrl", "AttendeeToken");
}
else
{
sql = string.Format(sql, "PaneListUrl", "PaneListToken");
}
return(DbHelperSQL.ExcuteScaler <GenseeDirectSee>(sql, cplId, courseType).FirstOrDefault());
}
/// <summary>
/// 获取教师评价 学员对老师的评价
/// </summary>
/// <param name="memberId"></param>
/// <returns></returns>
public List <MyEvaluate> GetTeacherToStudentEvaluates(int memberId)
{
var sql = @"
SELECT t1.rate1 remark,
t1.ClassId,
t1.Content,
t5.About,
t5.CnName CourseName,
t3.CnName Teacher,
t3.Logo,
0 coursetype, t1.cplid,
t1.CreateTime FROM ClassComment t1 INNER JOIN Teacher t2 ON t1.TeacherId = t2.TeacherId and t2.MemberId=@0 AND t1.CommentType=0
INNER JOIN Member t3 ON t1.MemberId = t3.Id
INNER JOIN ClassPlan t4 ON t1.ClassId = t4.ClassId INNER JOIN Course t5 ON t4.CourseId = t5.CourseId";
return(DbHelperSQL.ExcuteScaler <MyEvaluate>(sql, memberId));
}
/// <summary>
/// 获取学员评价 老师对学员的评价
/// </summary>
/// <param name="memberId"></param>
/// <returns></returns>
public List <MyEvaluate> GetTeacherEvaluates(int memberId)
{
var sql = @"
SELECT
t1.rate1 remark,
t1.ClassId,
t3.About,
t1.Content,
t3.CnName CourseName,
t6.CnName Teacher,
t6.Logo,
0 coursetype, t1.cplid,
t1.CreateTime FROM ClassComment t1
inner join ClassPlan t2 on t1.classid = t2.classid inner join course t3 on t2.courseid=t3.courseid
inner join Teacher t4 on t4.TeacherId = t1.TeacherId
left JOIN Member t6 ON t4.MemberId = t6.id where t1.memberid=@0 and CommentType=1 ORDER BY CourseName desc";
return(DbHelperSQL.ExcuteScaler <MyEvaluate>(sql, memberId));
}
/// <summary>
/// 获取页脚文章列表
/// </summary>
/// <returns></returns>
public List <ArtcleViewModel> GetArtcleListForFooter()
{
var pId = System.Configuration.ConfigurationManager.AppSettings["footerParentId"].ToString();
var templist = new List <ArtcleViewModel>();
// var sqlstring = "SELECT * FROM ArticleCate WHERE ParentId=4";
var catelist = DbHelperSQL.ExcuteScaler <ArticleCate>("SELECT * FROM ArticleCate WHERE ParentId=@0 ", pId).ToList();
if (catelist != null && catelist.Count > 0)
{
foreach (var item in catelist)
{
ArtcleViewModel model = new ArtcleViewModel();
model.ArtcleCateName = item.CateName;
model.ArtcleList = new List <Article>();
var sqlartstring = string.Format("SELECT articleid id,title,description,content FROM Article WHERE CateId={0}", item.CateId);
model.ArtcleList = DbHelperSQL.ExcuteScaler <Article>(sqlartstring).ToList();
templist.Add(model);
}
}
return(templist);
}
public List <OpenClass> GetOpenClass(int memberId)
{
// t4.num,
// t4.exist,
string sql = @"
select oc.openclassid id,classtime,tm.ENName teacher,
t3.CnName title,coursedesc about,MaxStudentNum maxNum,
(CASE WHEN t4.num IS NULL THEN 0 ELSE t4.num end) AS Num,
(CASE WHEN t4.exist IS NULL THEN 0 ELSE t4.exist end) AS Exist,
u.Hash ImgUrl,CONVERT(varchar(100), FreeStartTime, 20) as FreeStartTime,CONVERT(varchar(100), FreeEndTime, 20) as FreeEndTime from openclass oc inner join teacher tc on oc.teacherid = tc.teacherid
INNER JOIN Member tm ON tm.id = tc.MemberId
INNER JOIN OpenCourse t3 ON oc.CourseId = t3.CourseId
INNER JOIN UploadFile u ON u.FileId=t3.ThumbId
Left JOIN (
SELECT count(*) num,OpenClassId,
(CASE WHEN (SELECT memberid FROM OpenClassMember
WHERE MemberId=@0 AND OpenClassId = t1.OpenClassId) is NULL THEN 0 ELSE 1 end)exist
FROM OpenClassMember t1 GROUP BY OpenClassId) t4 ON oc.openclassid = t4.openclassid
WHERE oc.IsClosed>=1 order by FreeStartTime desc";
var list = DbHelperSQL.ExcuteScaler <OpenClass>(sql, memberId);
return(list);
}
/// <summary>
/// 获取学员评价 学员对老师的评价
/// </summary>
/// <param name="memberId"></param>
/// <returns></returns>
public List <MyEvaluate> GetStudentEvaluates(int memberId)
{
var sql = @"
SELECT
t1.rate1 remark,
t1.ClassId,
t1.Content,
t3.About,
t3.CnName CourseName,
t6.EnName Teacher,
t6.Logo,
0 coursetype,t1.cplid,
t1.CreateTime FROM ClassComment t1
inner join Teacher t5 on t1.teacherid = t5.teacherid AND t1.CplId>0 AND t1.MemberId=@0 and t1.CommentType=0
inner join Member t6 ON t5.MemberId = t6.id
inner join ClassPlan t2 on t1.classid = t2.classid
inner join course t3 on t2.courseid=t3.courseid
UNION
SELECT
t1.rate1 remark,
t1.ClassId,
t1.Content,
t3.CourseDesc About,
t3.CnName CourseName,
t6.EnName Teacher,
t6.Logo,
1 coursetype,t1.cplid,
t1.CreateTime FROM ClassComment t1
inner JOIN OpenClass t2 on t1.classid = t2.openclassid AND t1.CplId=0 AND t1.memberid=@0
inner join opencourse t3 on t2.courseid=t3.courseid
inner join Teacher t5 on t1.teacherid = t5.teacherid
left JOIN Member t6 ON t5.MemberId = t6.id
ORDER BY CourseName desc";
return(DbHelperSQL.ExcuteScaler <MyEvaluate>(sql, memberId));
}
public Article GetArticle(int id)
{
return(DbHelperSQL.ExcuteScaler <Article>(" select articleid id,title,description,content from article where enabled=1 and articleid=@0 ", id).FirstOrDefault());
}
public List <Article> GetArticles()
{
var code = System.Configuration.ConfigurationManager.AppSettings["OneWord"].ToString();
return(DbHelperSQL.ExcuteScaler <Article>(" select articleid id,title,WeiXinUrl,description,content from article t1,ArticleCate t2 where t1.cateid=t2.cateid and t2.code=@0 and enabled=1 order by orderby desc ", code));
}
/// <summary>
/// 获取课程计划
/// </summary>
/// <param name="memberId"></param>
/// <returns></returns>
public List <ClassPlanRlt> GetClassPlans(int memberId)
{
return(DbHelperSQL.ExcuteScaler <ClassPlanRlt>(@"
SELECT cp.ClassId Id, c.CnName Name,cc.CategoryName,cp.ClassTime,cp.Total,(SELECT sum(1) from StudyPlan WHERE ClassId=cp.ClassId)CurrentClass FROM ClassPlanMember cpm, ClassPlan cp,Course c,CourseCategory cc WHERE cpm.ClassId = cp.ClassId AND cpm.MemberId=@0
AND cp.CourseId =c.CourseId AND cp.ClassCategoryId = cc.CategoryId", memberId));
}
public CourseLesson GetOpenClassLesson(int cplId)
{
return(DbHelperSQL.ExcuteScaler <CourseLesson>("select freestarttime classstarttime,openclassid cplid from OpenClass where openclassid=@0", cplId).FirstOrDefault());
}
public GenseeDirectSee GetTeacherScheduleByCpl(int cplId, int courseType)
{
return(DbHelperSQL.ExcuteScaler <GenseeDirectSee>("select webcastid,CplId,PaneListUrl url,ScheduleId,OrganizerToken token,clength courselength,begintime begintime,endtime,isfull from Schedule where cplid=@0 and CourseType=@1", cplId, courseType).FirstOrDefault());
}
/// <summary>
/// 获取外教
/// </summary>
/// <returns></returns>
public List <TeamTeature> GetForeignTeature()
{
return(DbHelperSQL.ExcuteScaler <TeamTeature>("SELECT m.Id,m.Logo,m.EnName Name,t.Signature,t.Video FROM Member m,Teacher t WHERE m.MemberType=3 AND m.Id=t.MemberId AND t.Visible=1 AND t.Enabled=1 And t.TeacherType=0 ORDER BY t.orderby"));
}
public List <Cart> GetCart(int memberId)
{
return(DbHelperSQL.ExcuteScaler <Cart>(@"SELECT cartId id,mc.cardid,cc1.cardname,mc.giftids,'' giftnames,
qty number,mc.createtime,cc1.Price
FROM MemberCart mc INNER JOIN CourseCard cc1 ON mc.MemberId= @0 AND mc.cardid=cc1.cardid", new object[] { memberId }));
}
/// <summary>
/// 获取我的课程卡次
/// </summary>
/// <param name="memberId"></param>
/// <returns></returns>
public List <MyCourseCard> GetMyCourseCard(int memberId)
{
var sqlstring = @"SELECT ROW_NUMBER() OVER (ORDER BY OptionStatus desc) AS RowNumber,* from CourseCardViewForPerson WHERE MemberId=@0 ORDER BY OptionStatus desc ";
return(DbHelperSQL.ExcuteScaler <MyCourseCard>(sqlstring, memberId));
}
public List <BuyCourseCard> GetCourseCard(int type, int cardTypeId)
{
return(DbHelperSQL.ExcuteScaler <BuyCourseCard>(@"SELECT CardId,CardName,CardCategoryId,CardTypeId,Total,Months,Price,ClassContent,Discount FROM CourseCard t1 INNER JOIN CourseCategory t3 ON t1.CardCategoryId=t3.CategoryId where t1.IsGift=0 AND t1.Enabled=1 and t3.Code=@0 and t1.CardTypeId=@1", type, cardTypeId));
}
public List <WebSitePic> GetWebSitePic(int siteType, int typeId)
{
return(DbHelperSQL.ExcuteScaler <WebSitePic>("select * from WebSitePic where enabled=1 and siteType=@0 and typeId=@1", siteType, typeId));
}
public List <Orders> GetOrder(string orderNo, int memberId)
{
return(DbHelperSQL.ExcuteScaler <Orders>("select orderid,orderno,totalamount,ordertime from orders where orderno=@0 and memberid=@1", orderNo, memberId));
}
public List <T> GetModels(string where, params object[] paramters)
{
return(DbHelperSQL.ExcuteScaler <T>(CreateSqlBuilder(where), paramters));
}
public List <T> GetClassModels(string sql, params object[] paramters)
{
return(DbHelperSQL.ExcuteScaler <T>(sql, paramters));
}
public List <Ask> GetAsks(int id)
{
return(DbHelperSQL.ExcuteScaler <Ask>("select askid, content, createtime,(select count(1) from answer t2 where t2.askid=t1.askid) answernum from ask t1 where memberid = @0 and closed = 0 order by orderby asc", id));
}
public CourseLesson GetClassLesson(int cplId)
{
return(DbHelperSQL.ExcuteScaler <CourseLesson>("select classstarttime,cplid from classplanlesson where cplid=@0", cplId).FirstOrDefault());
}