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