public List <PromotionStage> GetPostList(IEnumerable <int> postId) { string sql = string.Format(@" select Sys_Posts.PostId ,Sys_Posts.PostName ,( select sum(LearnScore) from Res_Course where CourseId in ( select Ab_AbilityResource.ResourceId from Ab_AbilityResource left join Ab_PostElectiveCourse on Ab_PostElectiveCourse.PostId = Sys_Posts.PostId and Ab_AbilityResource.AbilityId = Ab_PostElectiveCourse.AbilityId and Ab_AbilityResource.ResourceId = Ab_PostElectiveCourse.CourseId where ResourceType = 0 and IsDelete = 0 and Ab_AbilityResource.AbilityId in ( select AbilityId from Ab_PostAbility where PostId = Sys_Posts.PostId and IsDelete = 0 ) and Ab_PostElectiveCourse.recordid is null ) ) as MaxObligatoryLessScore ,( select sum(LearnScore) from Res_Course where CourseId in ( select Ab_AbilityResource.ResourceId from Ab_AbilityResource left join Ab_PostElectiveCourse on Ab_PostElectiveCourse.PostId = Sys_Posts.PostId and Ab_AbilityResource.AbilityId = Ab_PostElectiveCourse.AbilityId and Ab_AbilityResource.ResourceId = Ab_PostElectiveCourse.CourseId where ResourceType = 0 and IsDelete = 0 and Ab_AbilityResource.AbilityId in ( select AbilityId from Ab_PostAbility where PostId = Sys_Posts.PostId and IsDelete = 0 ) and Ab_PostElectiveCourse.recordid is not null ) ) as MaxElectiveLessScore from Sys_Posts where PostId in ({0})", postId.GetString()); var list = _dataAccess.GetListBySql <PromotionStage>(sql).ToList(); return(list); }
public IEnumerable <Models.ResTeacher> GetTeachers(out int total, string name, string level, int pageIndex, int pageSize, string sort) { var dataSql = @"SELECT u.UserId, u.Username,u.[Password],u.TenantId,u.Realname,u.Email,u.Phone,u.Leader,u.UserCode,u.UserType,u.Sex,u.PostId,u.DeptId,u.Birthday,u.Freezed,u.HighestEdu,u.Location,u.CreateTime, u.NamePin,u.LoginFailure,u.[Lookup],u.LastLoginTime,u.Schools,u.MaritalState,u.HomeTel,u.HomeAddr,u.QqNo,u.Height,u.IDCard,u.IDType,u.WorkYear,u.[Signature],u.ModificationDate,u.Major,u.TechnicalTitle,u.JobDirection,u.AdministrativeTitle, t.TeacherId,t.Expertise,t.TeacherLevel,t.Adress,t.WorkTime,t.JoinTime,t.TeacherTime,t.Sort,t.[Level],t.TeacherType, t.Price,Contact,t.[Object],t.Education,t.Specialty,t.TimeAgreement,t.Course,t.TeachingStyle,t.ExpertField FROM Sys_Users u RIGHT OUTER JOIN Res_Teacher t ON u.UserId = t.UserId WHERE (u.Status = 0) AND (u.Freezed = 0) AND t.IsDelete=0 "; dataSql += string.Format("AND u.Realname LIKE '%{0}%' ", name.ReplaceSql()); var countSql = @"SELECT COUNT(*) FROM Sys_Users u RIGHT OUTER JOIN Res_Teacher t ON u.UserId = t.UserId WHERE (u.Status = 0) AND (u.Freezed = 0) AND t.IsDelete=0 " + string.Format("AND u.Realname LIKE '%{0}%' ", name.ReplaceSql()); if (!String.IsNullOrEmpty(level)) { dataSql += " AND t.Level like '%" + level + "%'"; countSql += " AND t.Level like '%" + level + "%'"; } total = (int)_dataAccess.ExecuteScalar(countSql); if (total == 0) { return(new BindingList <ResTeacher>()); } var list = _dataAccess.GetListBySql <ResTeacher>(string.Format(DataAccessProvider.DataAccess.PagingSelect, dataSql, sort , (pageIndex - 1) * pageSize + 1, pageIndex * pageSize)); return(list); }
/// <summary> /// 获取推荐课程 /// </summary> /// <param name="tags">标签</param> /// <param name="courseId"></param> /// <param name="userId"></param> /// <param name="top"></param> /// <param name="tenantId"></param> /// <returns></returns> public List <MyLearningCourse> GetRecommendCourse(string tags, int courseId, int userId, int top, int tenantId) { string sql = @"select Res_Course.*, Len_LearningRecord.RecordId, Len_LearningRecord.LearnProcess, Len_LearningRecord.LastLearnTime, (select cast(sum(score) as DECIMAL)/count(0) from Res_CourseCommentScore where courseId = Res_Course.CourseId) as CourseCommentScore, (select count(0) from Res_CourseCommentScore where courseId = Res_Course.CourseId) as CourseCommentUserCount, (select Count(0) from Len_LearningRecord where courseId = Res_Course.CourseId) as LearnUserCount from Res_Course left join Len_LearningRecord on Res_Course.CourseId = Len_LearningRecord.CourseId and Len_LearningRecord.UserId = " + userId + @" and Len_LearningRecord.LearnPath = 0 where Res_Course.Status = 0 and Res_Course.CourseType = 0 and Res_Course.CourseId != " + courseId; var tagArray = tags.Split(','); var tagWhere = string.Empty; for (int i = 0; i < tagArray.Length; i++) { var s = tagArray[i]; if (i > 0) { tagWhere += " OR "; } tagWhere += "Res_Course.Tags LIKE '%" + s.ReplaceSql() + "%'"; } sql += " AND ( " + tagWhere + " )"; sql = "select top " + top + " * from (" + sql + ") as t order by CourseId desc"; var list = _dataAccess.GetListBySql <MyLearningCourse>(sql).ToList(); return(list); }
/// <summary> /// 获取评价的列表 /// </summary> /// <param name="total"></param> /// <param name="bookId"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public IEnumerable <EBookComment> GetEBookComment(out int total, int bookId, int pageIndex = 0, int pageSize = int.MaxValue) { var list = _dataAccess.GetEntities <EBookComment>(out total, string.Format(@"where Res_EbookComment.BookId = {0} and Res_EbookComment.Status = 0 and Res_EbookComment.CommentType = 0", bookId), (pageIndex - 1) * pageSize + 1, pageSize); var listIds = list.Select(p => p.CommentId); if (listIds.Count() > 0) { string scoreSql = string.Format(@"select Res_EbookCommentScore.* from Res_EbookCommentScore where Res_EbookCommentScore.Status = 0 and Res_EbookCommentScore.BookId = {0}", bookId); var listScore = _dataAccess.GetListBySql <EBookCommentScore>(scoreSql); int antotal; var answerList = _dataAccess.GetEntities <EBookComment>(out antotal, string.Format(@"where Res_EbookComment.BookId = {0} and Res_EbookComment.Status = 0 and Res_EbookComment.CommentType = 1 and Res_EbookComment.ParentId in ({1})", bookId, listIds.GetString())); foreach (var item in list) { if (answerList.Count() > 0) { item.CommentAnswer = answerList.Where(p => p.ParentId == item.CommentId).ToList(); } var tmp = listScore.FirstOrDefault(p => p.UserId == item.UserId); if (tmp != null) { item.CommentScore = tmp.Score; } } } return(list); }
/// <summary> /// 获取指定角色下的用户 /// </summary> /// <param name="roleId"></param> /// <returns></returns> public DeptUser[] FindUserInRole(int roleId) { string sql = @"SELECT Dept_Users.* FROM Dept_Users LEFT JOIN Dept_RoleUser ON Dept_Users.UserId=Dept_RoleUser.UserId WHERE Dept_Users.Status=0 and Dept_Users.IsDelete=0 AND Dept_RoleUser.RoleId=" + roleId; return(_dataAccess.GetListBySql <DeptUser>(sql).ToArray()); }
/// <summary> /// /// </summary> /// <param name="total"></param> /// <param name="postName"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public List <PostAbilityDetail> GetPostAbilityList(out int total, string postName, int pageIndex, int pageSize) { total = (int)_dataAccess.ExecuteScalar(string.Format(@" select count(0) from Sys_Posts where Sys_Posts.PostName like '%{0}%'", postName.ReplaceSql())); if (total == 0) { return(new List <PostAbilityDetail>()); } return(_dataAccess.GetListBySql <PostAbilityDetail>(string.Format(@" select Sys_Posts.PostId ,Sys_Posts.PostName ,(select Count(0) from Sys_Users where PostId = Sys_Posts.PostId and Status = 0) as PostUserCount ,(select Count(0) from Ab_PostLearnRecord where PostId = Sys_Posts.PostId and EndTime is null) as LearningCount ,(select Count(0) from Ab_PostLearnRecord where PostId = Sys_Posts.PostId and EndTime is not null) as LearnedCount ,(select count(0) from Ab_PostAbility where PostId = Sys_Posts.PostId and IsDelete = 0) as AbilityCount ,( select sum(DATEDIFF(DAY,starttime,endtime) + 1) from Ab_PostLearnRecord where PostId = Sys_Posts.PostId and Endtime is not null ) as LearnedAllTime from Sys_Posts where Sys_Posts.PostName like '%{0}%'", postName.ReplaceSql()), pageIndex, pageSize, "postId").ToList()); }
/// <summary> /// 获取指定角色下的用户 /// </summary> /// <param name="roleId"></param> /// <returns></returns> public SysUser[] FindUserInRole(int roleId) { string sql = @"SELECT Sys_Users.* FROM Sys_Users LEFT JOIN Sys_RoleUser ON Sys_Users.UserId=Sys_RoleUser.UserId WHERE Sys_Users.Status=0 and Sys_Users.IsDelete=0 AND Sys_RoleUser.RoleId=" + roleId; return(_dataAccess.GetListBySql <SysUser>(sql).ToArray()); }
/// <summary> /// 获取系统中的所有一级和二级分类 /// </summary> /// <param name="tenantId"></param> /// <returns></returns> public IEnumerable <QuestionSort> GetFirstAndSecondSorts(int tenantId) { var sql = @"SELECT * FROM Int_QuestionSort WHERE (ParentId=0 OR ParentId IN (SELECT SortId FROM Int_QuestionSort WHERE ParentId=0) ) AND IsDelete=0"; return(_dataAccess.GetListBySql <QuestionSort>(sql)); }
/// <summary> /// 获取指定角色下的用户 /// </summary> /// <param name="roleId"></param> /// <returns></returns> public IEnumerable <SysAdmin> FindUserInRole(int roleId) { string sql = @"SELECT Sys_Admin.* FROM Sys_Admin LEFT JOIN Sys_AdminRole ON Sys_Admin.AdminId=Sys_AdminRole.AdminId WHERE Sys_Admin.Status=0 AND Sys_AdminRole.RoleId=" + roleId; return(_dataAccess.GetListBySql <SysAdmin>(sql)); }
/// <summary> /// 收件箱 /// </summary> /// <returns></returns> public IEnumerable <SysUserMessage> GetRecList(out int totalCount, int tenantId, int recUserId, string title, DateTime?starttime, DateTime?endtime, string sendrealname, int recstatus, int pageIndex, int pageSize) { string where = ""; if (starttime.HasValue) { where += string.Format(" and Sys_UserMessageContent.CreateTime >= '{0}'", starttime.Value); } if (endtime.HasValue) { where += string.Format(" and Sys_UserMessageContent.CreateTime <= '{0}'", endtime.Value.AddSeconds(59)); } totalCount = (int)_dataAccess.ExecuteScalar(string.Format(@"select count(0) from Sys_UserMessage left join Sys_UserMessageContent on Sys_UserMessage.MessageId = Sys_UserMessageContent.MessageId left join Sys_Users on Sys_Users.UserId = Sys_UserMessage.SendUserId where Sys_UserMessage.TenantId = {0} and Sys_UserMessage.SendUserId > 0 and Sys_UserMessage.RecStatus < 2 and Sys_UserMessage.RecUserId = {1} and Sys_Users.Realname like '%{2}%' and Sys_UserMessageContent.Title like '%{3}%' and ( Sys_UserMessage.RecStatus = {4} or {4} = 99 ) {5} ", tenantId , recUserId , sendrealname.ReplaceSql() , title.ReplaceSql() , recstatus , where)); if (totalCount == 0) { return(new List <SysUserMessage>()); } string sql = string.Format(@"select Sys_UserMessage.RecordId ,Sys_UserMessageContent.Title ,Sys_Users.Realname as SendUserRealName ,Sys_UserMessageContent.CreateTime ,Sys_UserMessage.RecStatus ,Sys_UserMessage.SendUserId from Sys_UserMessage left join Sys_UserMessageContent on Sys_UserMessage.MessageId = Sys_UserMessageContent.MessageId left join Sys_Users on Sys_Users.UserId = Sys_UserMessage.SendUserId where Sys_UserMessage.TenantId = {0} and Sys_UserMessage.SendUserId > 0 and Sys_UserMessage.RecStatus < 2 and Sys_UserMessage.RecUserId = {1} and Sys_Users.Realname like '%{2}%' and Sys_UserMessageContent.Title like '%{3}%' and ( Sys_UserMessage.RecStatus = {4} or {4} = 99 ) {5} ", tenantId , recUserId , sendrealname.ReplaceSql() , title.ReplaceSql() , recstatus , where); return(_dataAccess.GetListBySql <SysUserMessage>(string.Format(DataAccessProvider.DataAccess.PagingSelect, sql, "RecordId desc", (pageIndex - 1) * pageSize + 1, pageIndex * pageSize))); }
/// <summary> /// /// </summary> /// <param name="roleId"></param> /// <returns></returns> public List <vTenantFunction> GetPermissionsByRoleId(int roleId) { string sql = @"SELECT m.* FROM Sys_Permission m INNER JOIN Sys_RolePermission rm ON m.PermissionId=rm.PermissionId LEFT JOIN Sys_Roles r ON rm.RoleId=r.RoleId WHERE rm.TenantId=r.TenantId and m.UserType=0 AND r.RoleId=@roleId"; var param = new { roleId }; return(_dataAccess.GetListBySql <vTenantFunction>(sql, param).ToList()); }
/// <summary> /// /// </summary> /// <param name="userId"></param> /// <returns></returns> public List <vTenantFunction> GetPermissionsByUserId(int userId) { string sql = @"SELECT distinct m.* FROM vTenantFunction m LEFT JOIN Sys_RolePermission rm ON m.PermissionId=rm.PermissionId LEFT JOIN Sys_RoleUser ru ON ru.RoleId=rm.RoleId LEFT JOIN Sys_Roles r ON ru.RoleId=r.RoleId OR r.IsDefault=1 LEFT JOIN Sys_Users u ON u.UserId=ru.UserId AND m.TenantId=u.TenantId WHERE r.Freezed = 0 and r.Status = 0 and u.UserId=" + userId + " ORDER BY m.Sort"; return(_dataAccess.GetListBySql <vTenantFunction>(sql).ToList()); }
public List <PostAbility> GetPostAbility(out int total, int postId, string abname, int catId, string level, int pageIndex, int pageSize) { var sql = string.Format(@"SELECT pa.RecordId , ai.AbilityId , pa.PostId , ai.AbilityName , ai.AbilityLevel , ac.CategoryName , ( SELECT COUNT(1) FROM Ab_AbilityResource WHERE Ab_AbilityResource.AbilityId = ai.AbilityId AND ResourceType = 0 and IsDelete = 0 ) AS CourseCount , ( SELECT COUNT(1) FROM Ab_AbilityResource WHERE Ab_AbilityResource.AbilityId = ai.AbilityId AND ResourceType = 1 and IsDelete = 0 ) AS KnowledgeCount FROM Ab_PostAbility pa LEFT JOIN Sys_Posts p ON p.PostId = pa.PostId LEFT JOIN Ab_AbilityInfo ai ON ai.AbilityId = pa.AbilityId LEFT JOIN Ab_AbilityCategory ac ON ac.CategoryId = ai.CategoryId WHERE pa.PostId=@postId AND pa.IsDelete=0 AND ai.IsDelete=0 AND ai.AbilityName LIKE '%{0}%' AND (@catId=-1 OR @catId=ai.CategoryId) " + (string.IsNullOrEmpty(level) ? "" : " AND ai.AbilityLevel=@level"), abname.ReplaceSql()); var countSql = string.Format(@"SELECT COUNT(1) FROM Ab_PostAbility pa LEFT JOIN Sys_Posts p ON p.PostId = pa.PostId LEFT JOIN Ab_AbilityInfo ai ON ai.AbilityId = pa.AbilityId LEFT JOIN Ab_AbilityCategory ac ON ac.CategoryId = ai.CategoryId WHERE pa.PostId=@postId AND pa.IsDelete=0 AND ai.IsDelete=0 AND ai.AbilityName LIKE '%{0}%' AND (@catId=-1 OR @catId=ai.CategoryId) " + (string.IsNullOrEmpty(level) ? "" : " AND ai.AbilityLevel=@level"), abname.ReplaceSql()); var ps = new { postId, catId, level, abname }; total = _dataAccess.FetchListBySql <int>(countSql, ps).First(); if (total == 0) { return(new List <PostAbility>()); } var list = _dataAccess.GetListBySql <PostAbility>(sql, pageIndex, pageSize, "AbilityId DESC", ps); return(list.ToList()); }
public IEnumerable <KL_ResourceTypeCount> GetResourceCount(int tenantId) { var sql = string.Format(@"SELECT COUNT(*) Total,ResourceType FROM KL_Resource WHERE IsDeleted=0 AND Status=1 AND TenantId={0} GROUP BY ResourceType", tenantId); return(_dataAccess.GetListBySql <KL_ResourceTypeCount>(sql)); }
/// <summary> /// 加入用户 /// </summary> /// <param name="userids"></param> private void AddUsers(IEnumerable <int> userids) { if (!userids.Any()) { return; } var userlist = _dataAccess.GetListBySql <SysUser>("select UserId,Realname from Sys_Users where UserId in (" + userids.GetString() + ") and Status = 0"); var insertSql = new StringBuilder(); foreach (var userid in userids) { var tmp = userlist.FirstOrDefault(p => p.UserId == userid); if (tmp == null) { continue; } insertSql.AppendLine(string.Format("INSERT INTO ofuser (username,plainPassword,encryptedPassword,name,email,creationDate,modificationDate) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}') ;" , userid, "123456", adminPassword, tmp.Realname, "", "001404781934905", "0")); } using (IDbConnection conn = OpenMySqlConnection()) { conn.Execute(insertSql.ToString()); } }
/// <summary> /// 获取指定用户最后一次发表的说说 /// </summary> /// <param name="userId"></param> /// <returns></returns> public IntUserActivity GetUserLastActivity(int userId) { var sql = string.Format(@"SELECT TOP 1 * FROM Int_UserActivity WHERE UserId={0} ORDER BY ActionTime desc", userId); return(_dataAccess.GetListBySql <IntUserActivity>(sql).FirstOrDefault()); }
/// <summary> /// /// </summary> /// <param name="adminId"></param> /// <returns></returns> public List <Models.Oper.Sys_OperPermission> GetAdminPermission(int adminId) { var sql = @"SELECT p.* FROM Sys_OperRolePermission rr JOIN Sys_OperPermission p ON rr.PermissionId=p.PermissionId"; sql += " WHERE rr.RoleId=" + adminId; return(_dataAccess.GetListBySql <Models.Oper.Sys_OperPermission>(sql).ToList()); }
/// <summary> /// 热门小组 /// </summary> /// <param name="total"></param> /// <param name="userId"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public List <Group> GetHotGroup(out int total, int tenantId, int pageIndex, int pageSize) { var list = _dataAccess.GetListBySql <Group>(string.Format(@"select * from ( select Int_Group.GroupId ,Int_Group.GroupName ,Int_Group.FrontImage ,( select count(0) from Int_GroupTopic where GroupId = Int_Group.GroupId and IsDelete = 0 ) as TopicCount ,( select count(0) from Int_GroupTopic where GroupId = Int_Group.GroupId and IsDelete = 0 and CreateTime >= '{1}' and CreateTime <= '{2}' ) as TopicCount7Day ,( select count(0) from Int_GroupTopicReply where TopicId in ( select TopicId from Int_GroupTopic where GroupId = Int_Group.GroupId and IsDelete = 0 ) and IsDelete = 0 ) as TopicReplyCount ,( select count(0) from Int_GroupMember where GroupId = Int_Group.GroupId and Status = 0 ) as MemberCount from Int_Group where Int_Group.IsDelete = 0 and Int_Group.TenantId = {0} ) a order by MemberCount desc,TopicCount desc,TopicReplyCount desc,TopicCount7Day desc", tenantId, DateTime.Now.AddDays(-7), DateTime.Now)); total = list.Count(); return(list.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToList()); }
public Cde_SurveyExampaper GetFullExampaper(int id) { var exampaper = GetExampaper(id); if (exampaper == null) { return(null); } var questions = _dataAccess.GetListBySql <Cde_IndexQuestion>(@"SELECT sq.ShowOrder, iq.* ,s1.SortName,s2.SortName AS SecondSortName FROM Cde_SurveyQuestion sq JOIN Cde_IndexQuestion iq ON iq.QuestionId = sq.QuestionID LEFT JOIN Cde_IndexSort s1 ON s1.SortId = iq.SortId LEFT JOIN Cde_IndexSort s2 ON s2.SortId=iq.SecondSortId WHERE sq.ExampaperID = " + id + " ORDER BY sq.ShowOrder"); var answers = _dataAccess.GetList <Cde_IndexQuestionAnswer>(" Cde_IndexQuestionAnswer.QuestionId IN (" + questions.Select(p => p.QuestionId).GetString() + ") "); exampaper.Questions.AddRange(questions); foreach (var question in questions) { question.Answers.AddRange(answers.Where(p => p.QuestionId == question.QuestionId)); } return(exampaper); }
//private ExamTestDB _examTestDB = new ExamTestDB(); /// <summary> /// 课程统计(纬度:课程)- 图表 /// </summary> /// <param name="tenantId"></param> /// <param name="startTime"></param> /// <param name="endTime"></param> /// <param name="courseId"></param> /// <param name="coursecode"></param> /// <param name="coursename"></param> /// <param name="categoryname"></param> /// <param name="commentscore"></param> /// <returns></returns> public IEnumerable <Rpt_CourseStatisticsByCourse> GetCourseStatisticsByCourse(int tenantId, int[] courseId, DateTime?startTime, DateTime?endTime, string coursecode, string coursename, string categoryname, double commentscore) { string alreadyWhere = ""; string learningWhere = "and LearnProcess = 1 "; string addLearningWhere = ""; if (startTime.HasValue || endTime.HasValue) { learningWhere = "and (( LearnProcess = 1 " + (endTime.HasValue ? string.Format("and StartTime <= '{0}' ", endTime.Value.ToString()) : "") + ") "; if (endTime.HasValue) { learningWhere += string.Format(" or ( LearnProcess = 2 and EndTime >= '{0}' and StartTime <= '{0}' )", endTime.Value.ToString()); } learningWhere += ")"; } if (startTime.HasValue) { alreadyWhere += string.Format("and EndTime >= '{0}' ", startTime.Value.ToString()); addLearningWhere += string.Format("and StartTime >= '{0}' ", startTime.Value.ToString()); } if (endTime.HasValue) { alreadyWhere += string.Format("and EndTime <= '{0}' ", endTime.Value.ToString()); addLearningWhere += string.Format("and StartTime <= '{0}' ", endTime.Value.ToString()); } string sql = string.Format(@"select * from ( select Res_Course.CourseId,Res_Course.CourseCode,Res_Course.CourseName ,Res_CourseCategory.LevelDesc as CourseCategoryName ,(select cast(sum(score) as DECIMAL)/count(0) from Res_CourseCommentScore where courseId = Res_Course.CourseId) as CourseCommentScore ,(select count(0) from Len_LearningRecord where LearnProcess = 2 and LearnPath = 0 and SourceId = 0 and courseId = Res_Course.CourseId {2}) as AlreadyCount ,(select count(0) from Len_LearningRecord where courseId = Res_Course.CourseId and LearnPath = 0 and SourceId = 0 {3}) as LearningCount ,(select count(0) from Len_LearningRecord where LearnProcess = 1 and LearnPath = 0 and SourceId = 0 and courseId = Res_Course.CourseId {4}) as AddLearningCount from Res_Course left join Res_CourseCategory on Res_Course.CategoryId = Res_CourseCategory.CategoryId where Res_Course.status = 0 and Res_Course.CourseType=0 and Res_Course.TenantId = {0} {1} and Res_Course.CourseCode like '%{5}%' and Res_Course.CourseName like '%{6}%' and Res_CourseCategory.LevelDesc like '%{7}%' ) as t where (t.CourseCommentScore = {8} or {8} = 99) ", tenantId , (courseId.Length == 0 ? "" : "and Res_Course.CourseId in (" + courseId.GetString() + ")") , alreadyWhere , learningWhere , addLearningWhere , coursecode.ReplaceSql() , coursename.ReplaceSql() , categoryname.ReplaceSql() , commentscore); IEnumerable <Rpt_CourseStatisticsByCourse> list = _dataAccess.GetListBySql <Rpt_CourseStatisticsByCourse>(sql); return(list); }
public IEnumerable <AbilityInfo> GetAbilityCourses(IEnumerable <int> abilityIds) { if (abilityIds.Count() == 0) { return(new List <AbilityInfo>()); } var abls = _dataAccess.GetListBySql <AbilityInfo>("SELECT AbilityId,AbilityName,AbilityLevel FROM Ab_AbilityInfo WHERE AbilityId IN (" + abilityIds.GetString() + ")"); var courses = _dataAccess.FetchListBySql <AbilityCourse>(@"SELECT ar.RecordId,c.CourseId,c.CourseCode,c.CourseName,ar.AbilityId,0 as IsElective FROM Ab_AbilityResource ar LEFT JOIN Res_Course c ON ar.ResourceId=c.CourseId WHERE ar.IsDelete=0 AND ar.ResourceType=0 AND ar.AbilityId IN (" + abilityIds.GetString() + ")"); foreach (var info in abls) { info.RefeCourses.AddRange(courses.Where(p => p.AbilityId == info.AbilityId)); } return(abls); }
public void AddPostLearnRecord(int postId, int userId) { var model = _dataAccess.GetListBySql <AbPostLearnRecord>("select * from Ab_PostLearnRecord where PostId = " + postId + " and UserId = " + userId).FirstOrDefault(); if (model != null) { return; } model = new AbPostLearnRecord { PostId = postId, UserId = userId, EndTime = null, StartTime = DateTime.Now }; _dataAccess.AddEntity(model); }
/// <summary> /// 根据用户名获取权限 /// </summary> /// <param name="adminId"></param> /// <returns></returns> public List <Models.Oper.Sys_OperPermission> GetAdminPermission(int adminId) { var sql = @"SELECT distinct p.* FROM Sys_OperRolePermission rr JOIN Sys_OperPermission p ON rr.PermissionId=p.PermissionId"; sql += string.Format(@" WHERE rr.RoleId IN (SELECT RoleId FROM Sys_AdminRole WHERE AdminId={0} and RoleId in (select RoleId from Sys_OperationsRole where IsDelete=0 and Status=0))", adminId); return(_dataAccess.GetListBySql <Models.Oper.Sys_OperPermission>(sql).ToList()); }
/// <summary> /// 获取用户可创建主题的主版块 /// </summary> /// <param name="userId"></param> /// <returns></returns> public List <MainSection> GetUserCanCreateTopicMainSection(int userId) { var sql = string.Format(@"SELECT DISTINCT ss.* FROM bbs_SubSection ss LEFT OUTER JOIN bbs_CollectSubSection css ON css.SubSectionId = ss.SubSectionId WHERE ( ss.Creater = {0} OR css.UserId = {0}) AND IsDelete = 0", userId); var subs = _dataAccess.GetListBySql <SubSection>(sql); var mains = _dataAccess.GetList <MainSection>("bbs_MainSection.SectionId IN (" + subs.Select(p => p.Sectionid).GetString() + ") AND bbs_MainSection.IsDelete=0"); foreach (var main in mains) { main.SubSections.AddRange(subs.Where(p => p.Sectionid == main.SectionId)); } return(mains); }
/// <summary> /// 获取订单教材集合 /// </summary> /// <param name="Id">订单Id</param> /// <param name="deptId">服务中心Id</param> /// <returns></returns> public IEnumerable <ResTeacherBook> GetOrderBooks(int Id, int deptId) { string sql = string.Format(@"SELECT t1.BookId,t1.BookCode, t1.BookName,t1.BookType, t1.Press,t1.ISBN, t0.Price,t1.Stock,t0.Stock as orderStock FROM Res_BookDeptDetials AS t0 LEFT JOIN Res_TeacherBook AS t1 ON t1.BookId=t0.BookID WHERE t0.BookBuyID={0} AND t0.DeptID={1}", Id, deptId); return(_dataAccess.GetListBySql <ResTeacherBook>(sql)); }
/// <summary> /// 获取资源包列表 /// </summary> /// <returns></returns> public IEnumerable <ResResourcePackage> GetResourcePackageList(out int totalRecords, string name = "", string code = "", int status = -1, int pageIndex = 0, int pageSize = 20, string orderSql = " ORDER BY p.Id desc ") { var sqlwhere = new StringBuilder(" p.Status>=0"); if (status >= 0) { sqlwhere.AppendFormat(" and p.Status ={0}", status); } if (!string.IsNullOrEmpty(name)) { sqlwhere.AppendFormat(" and p.PackageName like '%{0}%'", name); } if (!string.IsNullOrEmpty(code)) { sqlwhere.AppendFormat(" and p.PackageCode like '%{0}%'", code); } //modify by ltc 2015-2-3 reason:将r1.Status=0修改成r1.Status!=-1,修复bug186,被冻结的也算一个资源,删除的不算 var sqlstr = string.Format(@"SELECT * FROM ( SELECT row_number() OVER({1}) number, count(*)OVER(PARTITION BY null) Totalcount, p.*, ResourceCount=(SELECT count(*) from Res_Resource as r1 WHERE r1.PackageId=p.Id AND r1.Status!=-1) FROM Res_ResourcePackage p where {0} ) result WHERE result.number BETWEEN @startLength*(@startIndex-1)+1 AND @startLength*@startIndex ", sqlwhere, orderSql); var param = new { startIndex = pageIndex, startLength = pageSize }; var list = _dataAccess.GetListBySql <ResResourcePackage>(sqlstr, param).ToList(); totalRecords = list.Count > 0 ? list[0].Totalcount : 0; return(list); }
/// <summary> /// 获取未关注的用户列表 /// </summary> /// <param name="tenantId"></param> /// <param name="userId"></param> /// <param name="type">0:未关注;1:已关注;-1:全部</param> /// <param name="name"></param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public IEnumerable <RetechWing.Models.SysUser> GetNoAttentionUserList(int tenantId, int userId, int type, string name, int pageIndex, int pageSize) { string sql = ""; if (type == -1) { sql = string.Format("select UserId,Realname,Photo,(select count(0) from Int_Relationship where UserId = {0} and UserId2 = Sys_Users.UserId ) as IsAttention from Sys_Users where Realname like '%{1}%' and TenantId = {2} and Status = 0 and UserId <> {0}", userId, name.ReplaceSql(), tenantId); } else if (type == 0) { sql = string.Format("select UserId,Realname,Photo,0 as IsAttention from Sys_Users where UserId not in ( select UserId2 from Int_Relationship where UserId = {0} ) and Realname like '%{1}%' and TenantId = {2} and Status = 0 and UserId <> {0}", userId, name.ReplaceSql(), tenantId); } else if (type == 1) { sql = string.Format("select UserId,Realname,Photo,1 as IsAttention from Sys_Users where UserId in ( select UserId2 from Int_Relationship where UserId = {0} ) and Realname like '%{1}%' and TenantId = {2} and Status = 0 and UserId <> {0}", userId, name.ReplaceSql(), tenantId); } return(_dataAccess.GetListBySql <RetechWing.Models.SysUser>(sql, pageIndex, pageSize, "userid")); }
/// <summary> /// 批量冻结/解冻服务中心 /// </summary> /// <param name="deptId"></param> /// <param name="flag">0-冻结 1-解冻</param> /// <returns></returns> public bool FreezeDept(int[] deptId, int flag = 0) { if (deptId.Length == 0) { return(false); } var users = _dataAccess.GetListBySql <SysDepartment>("select Status,DepartmentId from Sys_Department where DepartmentId in (" + deptId.GetString() + ") and IsDelete = 0"); var tmp1 = users.Where(p => p.Status == 0).Select(p => p.DepartmentId); var tmp2 = users.Where(p => p.Status == 1).Select(p => p.DepartmentId); if (flag == 1) { _dataAccess.UpdateField("Sys_Department", "Status", "0", "DepartmentId in (" + tmp2.GetString() + ")"); } else { _dataAccess.UpdateField("Sys_Department", "Status", "1", "DepartmentId in (" + tmp1.GetString() + ")"); } return(true); }
/// <summary> /// 批量冻结/解冻用户 /// </summary> /// <param name="userId"></param> /// <param name="flag">0-冻结 1-解冻</param> /// <returns></returns> public bool FreezeUser(int[] userId, int flag = 0) { if (userId.Length == 0) { return(false); } var users = _dataAccess.GetListBySql <SysUser>("select Status,UserId from Sys_Users where UserId in (" + userId.GetString() + ") and IsDelete = 0"); var tmp1 = users.Where(p => p.Status == 0).Select(p => p.UserId); var tmp2 = users.Where(p => p.Status == 1).Select(p => p.UserId); if (flag == 1) { _dataAccess.UpdateField("Sys_Users", "Status", "0", "UserId in (" + tmp2.GetString() + ")"); } else { _dataAccess.UpdateField("Sys_Users", "Status", "1", "UserId in (" + tmp1.GetString() + ")"); } return(true); }
/// <summary> /// 查询此人是否申请过此路径 /// true:存在(不可申请);false:不存在(可申请) /// </summary> /// <param name="promotionId"></param> /// <param name="userId"></param> /// <returns></returns> public bool IsExsits(int promotionId, int userId) { var apply = _dataAccess.GetListBySql <PromotionApply>(string.Format( "select * from Ab_PromotionApply where PromotionId = {0} and UserId = {1} and ApproveStatus in (0,2) and IsDelete = 0 ", promotionId, userId)).FirstOrDefault(); if (apply == null) { return(false); } if (apply.ApproveStatus == 0 || apply.PromotionStatus == 1 || (!apply.StageStartTime.HasValue)) { return(true); } var stage = _dataAccess.Get <PromotionStage>(apply.CurrentStageId); if (stage.Period > 0 && apply.StageStartTime.Value.AddDays(stage.Period) < DateTime.Now) { return(false); } return(true); }