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