public void StudyStarted(int postId, int userId)
{
var model = _dataAccess.GetListBySql <AbPostLearnRecord>("select * from Ab_PostLearnRecord where PostId = " + postId + " and UserId = " + userId).FirstOrDefault();
if (model == null || model.EndTime.HasValue)
{
return;
}
var NoPassObligatoryCourseCount = (int)_dataAccess.ExecuteScalar(string.Format(@"
select count(0)
from Ab_AbilityResource
left join Ab_PostAbility on Ab_AbilityResource.AbilityId = Ab_PostAbility.AbilityId and Ab_PostAbility.PostId = {0}
left join Len_LearningRecord on Len_LearningRecord.LearnPath = 2
and Len_LearningRecord.UserId = {1}
and Len_LearningRecord.SourceId = Ab_PostAbility.RecordId
and Len_LearningRecord.CourseId = Ab_AbilityResource.ResourceId
where Ab_AbilityResource.ResourceType = 0 and Ab_AbilityResource.IsDelete = 0
and Ab_AbilityResource.ResourceId not in (select CourseId from Ab_PostElectiveCourse where PostId = {0})
and Ab_PostAbility.IsDelete = 0
and (Len_LearningRecord.LearnProcess is null or Len_LearningRecord.LearnProcess = 1)", postId, userId));
if (NoPassObligatoryCourseCount > 0)
{
return;
}
model.EndTime = DateTime.Now;
_dataAccess.UpdateEntity(model);
}
/// <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());
}
public int GetUserStageLearnPercent(int userId, int applyId)
{
var apply = _dataAccess.Get <PromotionApply>(applyId);
var stage = _dataAccess.Get <PromotionStage>(apply.CurrentStageId);
int completeCount = (int)_dataAccess.ExecuteScalar(string.Format(@"select count(0) from Len_LearningRecord
where LearnProcess = 2 and LearnPath = 3
and SourceId in (select RecordId from Ab_LearningRecord where StageId = {0} and ApplyId = {2})
and UserId = {1}", apply.CurrentStageId, userId, applyId));
int allCount = (int)_dataAccess.ExecuteScalar(string.Format(@"select count(0) from Ab_AbilityResource
where Ab_AbilityResource.IsDelete = 0 and Ab_AbilityResource.ResourceType = 0
and Ab_AbilityResource.AbilityId in (
select AbilityId from Ab_PostAbility where IsDelete = 0 and PostId = {0}
)", stage.PostId));
if (stage.ExamId > 0)
{
allCount++;
var record = _dataAccess.GetListBySql <AbPromotionStageLenRecord>(string.Format(@"select * from Ab_PromotionStageLenRecord where PromotionId = {0} and ApplyId = {1} and StageId = {2} and UserId = {3}", apply.PromotionId, applyId, apply.CurrentStageId, userId)).FirstOrDefault();
if (record != null)
{
var examuserlist = new RetechWing.BusinessCommon.Examination.ExaminationCommonMethod().GetExamSendStudentWithByRelationIdAndUserId(record.RecordId, 2, userId);
if (examuserlist.Any(p => p.IsPass == 1))
{
completeCount++;
}
}
}
return(allCount == 0 ? 0 : completeCount * 100 / allCount);
}
/// <summary>
/// 赞某一动态
/// </summary>
/// <param name="activityId"></param>
/// <param name="userId"></param>
/// <param name="operSuccess">是否成功：如果是false,则表明用户已经赞过了，不能重复赞</param>
/// <returns></returns>
public int PraiseActivity(int activityId, int userId, out bool operSuccess)
{
var sqlwhere = string.Format("Int_ActivityPraise.ActivityId={0} AND Int_ActivityPraise.UserId={1}",
activityId, userId);
var model = _dataAccess.GetList <ActivityPraise>(sqlwhere).FirstOrDefault();
if (model == null)
{
model = new ActivityPraise();
model.ActivityId = activityId;
model.UserId = userId;
model.PraiseTime = DateTime.Now;
_dataAccess.AddEntity(model);
//更新冗余
_dataAccess.UpdateFieldCalc("Int_UserActivity", "PraiseCount", "PraiseCount+1", "ActivityId=" + activityId);
operSuccess = true;
}
else
{
operSuccess = false;
}
return
((int)_dataAccess.ExecuteScalar(
"SELECT PraiseCount FROM Int_UserActivity WHERE ActivityId=" + activityId));
}
public int GetUserSurveyId(int userId, int surveyId)
{
var sql = "SELECT UserSurveyId FROM Cde_UserSurvey WHERE SurveyId=" + surveyId + " AND UserId=" + userId;
var model = (int)_dataAccess.ExecuteScalar(sql);
return(model);
}
/// <summary>
/// 获取技能的课程数量
/// </summary>
/// <param name="abilityIds"></param>
/// <returns></returns>
public int GetAbilityCourseCount(IEnumerable <int> abilityIds)
{
if (abilityIds.Count() == 0)
{
return(0);
}
return((int)_dataAccess.ExecuteScalar("select count(0) from Ab_AbilityResource where ResourceType = 0 and IsDelete = 0 and AbilityId in (" + abilityIds.GetString() + ")"));
}
/// <summary>
/// 获取收件箱未读消息数量
/// </summary>
/// <param name="recUserId"></param>
/// <returns></returns>
public int GetNotReadRecMessageCount(int recUserId)
{
return((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.SendUserId > 0 and Sys_UserMessage.RecStatus = 0
and Sys_UserMessage.RecUserId = {0}", recUserId)));
}
public int GetUserLearnScore(int userId)
{
var total =
(int)
_dataAccess.ExecuteScalar(
"SELECT ISNULL(SUM(LearnScore),0) FROM Len_Achievement WHERE UserId=" + userId);
return(total);
}
/// <summary>
///
/// </summary>
/// <param name="total"></param>
/// <param name="name"></param>
/// <param name="org"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public List <UserPromotionWay> GetUserPromotionWays(out int total, string name, string org, int pageIndex, int pageSize)
{
var selectSql = @"SELECT *
FROM ( SELECT u.UserId ,
u.Username ,
u.Realname ,
d.LevelPath DeptName ,
p.PostName ,
( SELECT COUNT(1)
FROM Ab_PromotionApply pay
WHERE pay.UserId = u.UserId
AND pay.ApproveStatus = 2
) AS WayCount
FROM Sys_Users u
LEFT JOIN Sys_Posts p ON p.PostId = u.PostId
LEFT JOIN Sys_Department d ON d.DeptId = u.DeptId
) a
WHERE a.WayCount > 0";
string where = "";
if (!string.IsNullOrEmpty(name))
{
where += string.Format(" AND (a.Username LIKE '%{0}%' OR a.Realname LIKE '%{0}%')", name.ReplaceSql());
}
if (!string.IsNullOrEmpty(org))
{
where += string.Format(" AND (a.DeptName LIKE '%{0}%' OR a.PostName LIKE '%{0}%')", org.ReplaceSql());
}
var countSql = @"SELECT COUNT(1)
FROM ( SELECT u.UserId ,
u.Username ,
u.Realname ,
d.LevelPath DeptName ,
p.PostName ,
( SELECT COUNT(1)
FROM Ab_PromotionApply pay
WHERE pay.UserId = u.UserId
AND pay.ApproveStatus = 2
) AS WayCount
FROM Sys_Users u
LEFT JOIN Sys_Posts p ON p.PostId = u.PostId
LEFT JOIN Sys_Department d ON d.DeptId = u.DeptId
) a
WHERE a.WayCount > 0";
total = (int)_dataAccess.ExecuteScalar(countSql + where);
if (total == 0)
{
return(new List <UserPromotionWay>());
}
return(_dataAccess.FetchListBySql <UserPromotionWay>(
string.Format(DataAccessProvider.DataAccess.PagingSelect, selectSql + where, "Username DESC", (pageIndex - 1) * pageSize + 1,
pageIndex * pageSize)).ToList());
}
/// <summary>
/// 获取用户加入了多少个版块
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public int GetUserCollectCount(int userId)
{
var sql = @"SELECT COUNT(*)
FROM bbs_CollectSubSection c
JOIN bbs_SubSection s ON s.SubSectionId = c.SubSectionId
WHERE s.IsDelete=0 AND c.UserId=" + userId;
var count = (int)_dataAccess.ExecuteScalar(sql);
return(count);
}
public IList <ViewUserSurvey> GetUserSurveys(out int total, int userId, int status, string surveyTitle,
DateTime?sstime,
DateTime?setime,
DateTime?estime,
DateTime?eetime,
int pageIndex, int pageSize, string orderField)
{
var sqlwhere = string.Format("WHERE si.Status=1 AND si.IsDelete=0 AND si.SurveyTitle LIKE '%{0}%' AND usy.UserId=" + userId, surveyTitle.ReplaceSql());
if (status != -1)
{
sqlwhere += " AND usy.ApproveFlag=" + status;
}
if (sstime.HasValue)
{
sqlwhere += " AND si.StartTime>='" + sstime.Value.ToString("yyyy-MM-dd HH:mm:ss") + "'";
}
if (setime.HasValue)
{
sqlwhere += " AND si.StartTime<'" + setime.Value.ToString("yyyy-MM-dd HH:mm:ss") + "'";
}
if (estime.HasValue)
{
sqlwhere += " AND si.EndTime>='" + estime.Value.ToString("yyyy-MM-dd HH:mm:ss") + "'";
}
if (eetime.HasValue)
{
sqlwhere += " AND si.EndTime<'" + eetime.Value.ToString("yyyy-MM-dd HH:mm:ss") + "'";
}
var dataSql = @"SELECT usy.UserSurveyId,si.SurveyId, si.SurveyTitle,si.StartTime,si.EndTime,usy.ApproveFlag ,usy.ReportGenerated
FROM Cde_UserSurvey usy
LEFT JOIN Cde_SurveyInfo si ON usy.SurveyId=si.SurveyId
" + sqlwhere;
var countSql = @"SELECT COUNT(1)
FROM Cde_UserSurvey usy
LEFT JOIN Cde_SurveyInfo si ON usy.SurveyId=si.SurveyId
" + sqlwhere;
total =
(int)
_dataAccess.ExecuteScalar(countSql);
if (total == 0)
{
return(new List <ViewUserSurvey>());
}
if (string.IsNullOrEmpty(orderField))
{
orderField = "StartTime DESC";
}
return(_dataAccess.FetchListBySql <ViewUserSurvey>(
string.Format(DataAccessProvider.DataAccess.PagingSelect, dataSql, orderField, (pageIndex - 1) * pageSize + 1, pageIndex * pageSize),
new { surveyTitle }).ToList());
}
/// <summary>
/// 获取用户解答过的问题数量
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public int GetUserAnswerCount(int userId)
{
var totalSql =
string.Format(@"SELECT COUNT(*) FROM
(
SELECT DISTINCT ua.questionId FROM Int_UserAnswer ua JOIN Int_Question q ON q.QuestionId = ua.QuestionId
WHERE q.IsDelete=0 AND ua.UserId={0}
) a", userId);
// var sql = @"SELECT COUNT(*) FROM Int_UserAnswer ua JOIN Int_Question q ON q.QuestionId = ua.QuestionId
//WHERE q.IsDelete=0 AND ua.UserId=" + userId;
return((int)_dataAccess.ExecuteScalar(totalSql));
}
/// <summary>
/// 修改问题。如果问题被问卷引用，则不对原问题修改，直接新增一个问题，否则直接修改
/// </summary>
/// <param name="question"></param>
/// <returns></returns>
public bool UpdateQuestion(Models.Cde.Cde_IndexQuestion question)
{
int usecount =
(int)
_dataAccess.ExecuteScalar(@"SELECT count(1) FROM Cde_SurveyQuestion sq JOIN Cde_SurveyExampaper se ON sq.ExampaperID=se.ExampaperID
WHERE se.IsDelete=0 AND se.TenantId=1" + question.TenantId);
if (usecount > 0)
{
AddQuestion(question);
}
else
{
//直接修改
var sqlwhere = "Cde_IndexQuestionAnswer.QuestionId=" + question.QuestionId;
_dataAccess.DeleteEntities <Cde_IndexQuestionAnswer>(sqlwhere);
_dataAccess.UpdateEntity(question);
foreach (var answer in question.Answers)
{
answer.QuestionId = question.QuestionId;
}
_dataAccess.AddEntities(question.Answers);
}
return(true);
}
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);
}
public JsonResult GetTotalCount()
{
//人数(排除已删除的)
int userCount = _userManager.GetUserCount(1);
//课程数量
string coursekey = "courseCount";
object coursecount = CacheHelper.CacheService.Get(coursekey);
if (coursecount == null)
{
coursecount = _courseManager.GetCourseCount(CurrentTenant.TenantId);
CacheHelper.CacheService.Set(coursekey, coursecount, CachingExpirationType.Stable);
}
//培训班数量
var trainClassCount = _dataAccess.ExecuteScalar("SELECT Count(1) FROM Tr_TrainClass WHERE IsDelete=0");
//考试数量
var examCount = _examinationManager.GetExaminationCount();
return(Json(new
{
UserCounts = userCount,
CourseCounts = coursecount == null ? 0 : coursecount,
TrainClassCount = trainClassCount,
ExamCounts = examCount
}, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// 根据类型获取该类型下的所有培训班审批数
/// </summary>
/// <param name="userId"></param>
/// <param name="classType"></param>
/// <returns></returns>
public int GetClassProvalListCountByType(int userId, int classType)
{
var sql = string.Format(@"WITH tbinfo AS (
SELECT row_number()OVER(PARTITION BY tp.PlanClassID ORDER BY tp.Step) number,PlanClassID,CreateUserID,ApproveStatus FROM Tr_PlanApproveList tp
WHERE Batch=(SELECT max(batch) FROM Tr_PlanApproveList WHERE PlanClassID=tp.PlanClassID AND Type=1)
AND Type=1)
SELECT count(*)
FROM Tr_TrainClass tt
LEFT JOIN Tr_PlanClass tp ON tp.PlanClassID=tt.PlanClassId
LEFT JOIN Tr_YearPlan ty ON ty.ID=tp.YearPlanID
LEFT JOIN Sys_Department sd ON sd.DeptId=tt.DeptID
LEFT JOIN Sys_Users syu ON syu.UserId=tt.CreateUserID
WHERE tt.ApproveStatus>0 AND tt.IsDelete=0
and ClassId IN (
SELECT PlanClassID FROM tbinfo info WHERE CreateUserID={0}
and (SELECT count(1) FROM tbinfo WHERE number<info.number AND PlanClassID=info.PlanClassID and ApproveStatus!=2)=0
) AND tt.ClassType={1}" , userId, classType);
int count;
var count_result = _dataAccess.ExecuteScalar(sql);
if (count_result == null || !int.TryParse(count_result.ToString(), out count))
{
return(0);
}
return(count);
}
/// <summary>
/// 获取分数下子分类的个数
/// </summary>
/// <param name="categoryId"></param>
/// <returns></returns>
public int GetCategoryChildCount(int categoryId)
{
return
((int)
_dataAccess.ExecuteScalar(
"select count(0) from Res_EBookCategory where IsDelete = 0 and ParentId = " +
categoryId));
}
/// <summary>
/// 我收藏的知识【收藏时间】
/// </summary>
/// <param name="totalCount"></param>
/// <param name="userId"></param>
/// <param name="resourcename"></param>
/// <param name="starttime"></param>
/// <param name="endtime"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="order"></param>
/// <returns></returns>
public IEnumerable <KL_Resource> GetMyCollectResourceList(out int totalCount, int userId, string resourcename, DateTime?starttime, DateTime?endtime, int pageSize, int pageIndex, string order = " MyCollectTime desc ")
{
string where = string.Format(" KL_Resource.IsDeleted = 0 and KL_Resource.Status = 1 and ( KL_Resource.IsOpen = 1 or ( KL_Resource.IsOpen = 2 and ( select count(0) from KL_KnowledgeOpenUser where KL_KnowledgeOpenUser.ResourceId = KL_Resource.ResourceId and KL_KnowledgeOpenUser.UserId = {0}) > 0 ) ) and KL_Resource.ResourceId in (select ResourceId from KL_MyFavorite where UserId = {0} and Status = 0)", userId);
string timeWhere = "1 = 1";
if (!string.IsNullOrWhiteSpace(resourcename))
{
where += string.Format(" and KL_Resource.ResourceName like '%{0}%'", resourcename.ReplaceSql());
}
if (starttime.HasValue)
{
timeWhere += string.Format(" and MyCollectTime >= '{0}'", starttime.Value);
}
if (endtime.HasValue)
{
timeWhere += string.Format(" and MyCollectTime <= '{0}'", endtime.Value);
}
totalCount = (int)_dataAccess.ExecuteScalar(string.Format(@"select count(*) from ( select (select top 1 FavTime from KL_MyFavorite where ResourceId = KL_Resource.ResourceId and Status = 0 and UserId = {0}) as MyCollectTime
from KL_Resource
left join KL_KnowledgeSort on KL_Resource.SortId = KL_KnowledgeSort.SortId
left join Sys_Users on Sys_Users.UserId = KL_Resource.UserId
where " + where + " ) a where " + timeWhere, userId));
if (totalCount == 0)
{
return(new List <KL_Resource>());
}
var sql = string.Format(@" SELECT * FROM (
select KL_Resource.ResourceId
,KL_Resource.ResourceName
,KL_Resource.Realname
,KL_Resource.CreateTime
,KL_Resource.CanDownload
,KL_Resource.AvgScore
,KL_KnowledgeSort.SortName
,Sys_Users.Realname as CreaterName
,(select top 1 FavTime from KL_MyFavorite where ResourceId = KL_Resource.ResourceId and Status = 0 and UserId = {0}) as MyCollectTime
from KL_Resource
left join KL_KnowledgeSort on KL_Resource.SortId = KL_KnowledgeSort.SortId
left join Sys_Users on Sys_Users.UserId = KL_Resource.UserId
where " + where + " ) tab where " + timeWhere, userId);
var list = _dataAccess.GetListBySql <KL_Resource>(sql, pageIndex, pageSize, string.IsNullOrWhiteSpace(order) ? "MyCollectTime desc " : order);
if (list.Count() > 0)
{
var tmp = _dataAccess.FetchListBySql <int>(string.Format("select ResourceId from KL_LoadRecord where ResourceId in ({0}) and UserId = {1} and Status = 0", list.Select(p => p.ResourceId).GetString(), userId));
foreach (var item in list)
{
item.IsMyDownload = tmp.Contains(item.ResourceId) ? 1 : 0;
}
}
return(list);
}
public List <MyPromotionWay> GetMyPromotionWayList(out int total, int userId, string promotionName, int pageIndex = 1, int pageSize = int.MaxValue)
{
total = (int)_dataAccess.ExecuteScalar(string.Format(@"select count(0)
from Ab_PromotionApply
left join Ab_PromotionWay on Ab_PromotionWay.PromotionId = Ab_PromotionApply.PromotionId
where Ab_PromotionApply.IsDelete = 0 and Ab_PromotionApply.UserId = {0}
and Ab_PromotionApply.ApproveStatus <> 3
and Ab_PromotionWay.PromotionName like '%{1}%'", userId, promotionName.ReplaceSql()));
var list = _dataAccess.GetListBySql <MyPromotionWay>(string.Format(@"select Ab_PromotionApply.ApplyId
,Ab_PromotionApply.StageId
,Ab_PromotionApply.CurrentStageId
,Ab_PromotionApply.OldPostId
,Ab_PromotionApply.PostId
,Ab_PromotionApply.UserId
,Ab_PromotionApply.ApplyTime
,Ab_PromotionApply.ApproveStatus
,Ab_PromotionApply.Reason
,Ab_PromotionApply.PromotionStatus
,Ab_PromotionApply.StageStartTime
,Ab_PromotionApply.PromotionSucceedTime
,Ab_PromotionWay.*
,Ab_PromotionStage.Period as CurrentStagePeriod
from Ab_PromotionApply
left join Ab_PromotionWay on Ab_PromotionWay.PromotionId = Ab_PromotionApply.PromotionId
left join Ab_PromotionStage on Ab_PromotionStage.StageId = Ab_PromotionApply.CurrentStageId
where Ab_PromotionApply.IsDelete = 0 and Ab_PromotionApply.UserId = {0}
and Ab_PromotionApply.ApproveStatus <> 3
and Ab_PromotionWay.PromotionName like '%{1}%'", userId, promotionName.ReplaceSql()), pageIndex, pageSize, "ApplyId desc").ToList();
if (list.Count > 0)
{
string stageWhere = string.Format(" Ab_PromotionStage.IsDelete = 0 and Ab_PromotionStage.PromotionId in ({0}) ", list.Select(p => p.PromotionId).GetString());
var stageList = _dataAccess.GetList <PromotionStage>(stageWhere);
foreach (var item in list)
{
item.Stages.AddRange(stageList.FindAll(p => p.PromotionId == item.PromotionId).OrderBy(p => p.OrderNum));
}
}
return(list);
}
/// <summary>
/// 培训统计-维度：培训
/// </summary>
/// <param name="pageSize"></param>
/// <param name="trainIds"></param>
/// <param name="total"></param>
/// <param name="tenantId"></param>
/// <param name="pageIndex"></param>
/// <returns></returns>
public IEnumerable <Rpt_TrainStByTrain> GetTrainSt(out int total, int tenantId, int pageIndex, int pageSize, int[] trainIds, string order = " TrainId DESC")
{
string where = "";
if (trainIds != null && trainIds.Length > 0)
{
where = " AND ti.TrainId IN (" + trainIds.GetString() + ")";
}
var countSql = @"SELECT COUNT(1) FROM Tr_TrainingInfo ti
WHERE IsDeleted=0 AND ti.ScorePublished=1 AND Status=4 AND EndTime<'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' AND TenantId=" + tenantId + where;
total = (int)_dataAccess.ExecuteScalar(countSql);
var sql = @"SELECT ti.TrainId, ti.TrainName,ti.LearnTime,u.Realname, ti.StartTime,ti.EndTime
,(SELECT COUNT(0) FROM Tr_TrainRegister tr WHERE tr.TrainId=ti.TrainId AND tr.ApplyStatus=2)
+(SELECT COUNT(0) FROM Tr_TrainUser tu WHERE tu.TrainId=ti.TrainId) AS TrainUserCount
FROM Tr_TrainingInfo ti
LEFT JOIN Sys_Users u ON ti.Creater=u.UserId
WHERE ti.Status=4 AND ti.ScorePublished=1 AND ti.IsDeleted=0 AND EndTime<'" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "' AND ti.TenantId=" + tenantId + where;
var list =
_dataAccess.FetchListBySql <Rpt_TrainStByTrain>(string.Format(DataAccessProvider.DataAccess.PagingSelect, sql, order,
(pageIndex - 1) * pageSize + 1, pageIndex * pageSize));
return(list);
}
/// <summary>
/// 获取最后在线时间
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public static DateTime GetLastLoginTime(int userId)
{
var sql = string.Format(@"SELECT TOP 1 LoginTime FROM Sys_LoginLog
WHERE UserId={0}
ORDER BY LoginTime DESC", userId);
var dt = _dataAccess.ExecuteScalar(sql);
if (dt != null)
{
return(DateTime.Parse(dt.ToString()));
}
return(DateTime.Now);
}
/// <summary>
/// 添加积分记录
/// </summary>
/// <param name="ruleId"></param>
/// <param name="integration"></param>
/// <param name="userId"></param>
/// <param name="tenantId"></param>
/// <param name="desc"></param>
public void AddIntegration(int ruleId, int integration, int userId, int tenantId, string desc = "")
{
var userIntegral = new UserIntegral();
userIntegral.RuleId = ruleId;
userIntegral.Integral = integration;
userIntegral.TimeToGet = DateTime.Now;
userIntegral.UserId = userId;
userIntegral.IntegralDesc = desc;
#region 冗余处理
if (IsUseRedis)
{
var userTotal = rdHelper.HGet(userTotalIntegralKey, userId.ToString());
if (userTotal == null)
{
userTotal = ((int)_dataAccess.ExecuteScalar("select sum(integral) from int_userintegral where UserId=" + userId) + integration).ToString();
}
else
{
userTotal = (userTotal.ToInt32() + integration).ToString();
}
rdHelper.HSet(userTotalIntegralKey, userId.ToString(), userTotal.ToString());
}
else
{
//使用MySql
var existsSql = "SELECT COUNT(*) FROM Int_UserTotalIntegral WHERE UserId=" + userId;
if (_dataAccess.ExecuteScalar(existsSql).ToInt32() == 0)
{
//新增
var insertSql =
string.Format("INSERT into Int_UserTotalIntegral(UserId,TotalIntegral)values({0},{1});", userId, integration);
_dataAccess.ExecuteSql(insertSql);
}
else
{
_dataAccess.ExecuteSql(
string.Format(
"update Int_UserTotalIntegral set TotalIntegral=TotalIntegral+{0} WHERE UserId={1}",
integration, userId));
}
}
#endregion
_dataAccess.AddEntity(userIntegral);
}
/// <summary>
/// 获取最大用户Id
/// （该方法不是线程安全的）
/// </summary>
/// <param name="tenantId"></param>
/// <returns></returns>
public string GetMaxUserCode(int tenantId)
{
var sql = "SELECT TOP 1 UserCode FROM Sys_Users WHERE TenantId=" + tenantId + " ORDER BY usercode DESC";
var code = _dataAccess.ExecuteScalar(sql);
if (code != null)
{
int num;
if (int.TryParse(code.ToString().TrimStart('0'), out num))
{
num = num + 1;
return(num.ToString().PadLeft(6, '0'));
}
}
return("000001");
}
/// <summary>
/// 获取培训班参与人员
/// </summary>
/// <param name="classId"></param>
/// <param name="userId"></param>
/// <param name="username"></param>
/// <param name="realname"></param>
/// <param name="postName"></param>
/// <param name="deptName"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public PagedList <SysUser> GetTrainClassToUsers(int classId, int[] userId, string username = "",
string realname = "", string postName = "", string deptName = "", int pageIndex = 1, int pageSize = int.MaxValue)
{
var dataSql = string.Format(@"SELECT u.*,d.DeptName,p.PostName,isnull(
(SELECT tu.IsJoinSurvey FROM Tr_TrainClassToUser AS tu WHERE tu.IsDelete=0 AND tu.ClassId={0} AND tu.UserId=u.UserId),0) AS IsJoinSurvey
FROM Sys_Users AS u
LEFT JOIN Sys_Department AS d ON d.DeptId=u.DeptId
LEFT JOIN Sys_Posts AS p ON p.PostId=u.PostId ", classId);
var countSql = @"select count(1) FROM Sys_Users AS u
LEFT JOIN Sys_Department AS d ON d.DeptId=u.DeptId
LEFT JOIN Sys_Posts AS p ON p.PostId=u.PostId ";
string sqlwhere = " WHERE u.Status=0 ";
if (userId.Count() > 0)
{
sqlwhere += string.Format(" and u.UserId in ({0}) ", userId.GetString());
}
if (!string.IsNullOrEmpty(username))
{
sqlwhere += string.Format(" AND u.Username LIKE '%{0}%'", username.ReplaceSql());
}
if (!string.IsNullOrEmpty(realname))
{
sqlwhere += string.Format(" AND u.Realname LIKE '%{0}%'", realname.ReplaceSql());
}
if (!string.IsNullOrEmpty(postName))
{
sqlwhere += string.Format(" AND p.PostName LIKE '%{0}%'", postName.ReplaceSql());
}
if (!string.IsNullOrEmpty(deptName))
{
sqlwhere += string.Format(" AND d.DeptName LIKE '%{0}%'", deptName.ReplaceSql());
}
var count = (int)_dataAccess.ExecuteScalar(countSql + sqlwhere);
if (count == 0)
{
return(new PagedList <SysUser>(new List <SysUser>(), pageIndex, pageSize, 0));
}
var realsql = string.Format(_dataAccess.PagingSelect, dataSql + sqlwhere, "UserId desc",
(pageIndex - 1) * pageSize + 1, pageIndex * pageSize);
var list = _dataAccess.FetchListBySql <SysUser>(realsql);
return(new PagedList <SysUser>(list, pageIndex, pageSize, count));
}
public IEnumerable <GiveMeCourseList> GetDesignateUserDetail(out int total, int type, int designateUserId, int userId, string courseName, DateTime?sTime, DateTime?eTime, int status, int pageIndex, int pageSize, string sortField)
{
var infotype = type == -1 ? "0,2" : "1";
var dataSql = string.Format(@"SELECT d.DesignateId,c.CourseName,d.CompleteTime,c.CourseId,
(select Count(0) from Len_LearningRecord where Len_LearningRecord.courseId = c.CourseId and LearnPath = 0)
as LearnUserCount ,
c.AvgScore,lr.LearnProcess,lr.EndTime FROM Len_DesignateCourse d
LEFT JOIN Res_Course c ON d.CourseId=c.CourseId
LEFT JOIN Len_LearningRecord lr ON d.CourseId=lr.CourseId AND lr.UserId=d.UserId AND lr.LearnPath=0
WHERE d.UserId={0} AND d.DesignateUser={1}
AND d.InfoType IN ({2})", userId, designateUserId, infotype);
var sqlwhere = string.Format("AND c.CourseName LIKE '%{0}%'", courseName.ReplaceSql());
if (sTime.HasValue)
{
sqlwhere += " AND d.CompleteTime>='" + sTime.Value.ToString("yyyy-MM-dd") + "'";
}
if (eTime.HasValue)
{
sqlwhere += " AND d.CompleteTime<'" + eTime.Value.AddDays(1).ToString("yyyy-MM-dd") + "'";
}
if (status == 1)
{
sqlwhere += " AND lr.LearnProcess=2";
}
if (status == 0)
{
sqlwhere += " AND (lr.LearnProcess<>2 OR lr.LearnProcess IS NULL )";
}
var countSql = string.Format(@"SELECT COUNT(*)
FROM Len_DesignateCourse d
LEFT JOIN Res_Course c ON d.CourseId=c.CourseId
LEFT JOIN Len_LearningRecord lr ON d.CourseId=lr.CourseId AND lr.UserId=d.UserId AND lr.LearnPath=0
WHERE d.UserId={0} AND d.DesignateUser={1}
AND d.InfoType IN ({2})", userId, designateUserId, infotype);
total = (int)_dataAccess.ExecuteScalar(countSql + sqlwhere);
if (total == 0)
{
return(new GiveMeCourseList[0]);
}
var list = _dataAccess.FetchListBySql <GiveMeCourseList>(
string.Format(DataAccessProvider.DataAccess.PagingSelect, dataSql + sqlwhere, "CourseId",
(pageIndex - 1) * pageSize + 1, pageSize * pageIndex));
return(list);
}
public void GetJoinCount(ResSurveyInfo info)
{
var userManager = new UserManager();
if (info.RelUserType == 0)
{
int totalUserCount = userManager.GetUserCount(info.TenantId);
info.JoinCount = totalUserCount;
}
else
{
info.JoinCount =
(int)
_dataAccess.ExecuteScalar("SELECT COUNT(1) FROM Res_SurveyToUser WHERE SurveyId=" +
info.SurveyID);
}
//查找已做的问卷
info.CompleteCount = _dataAccess.GetCount <ResParticipateSurvey>("Res_ParticipateSurvey.Path=0 AND Res_ParticipateSurvey.SurveyID =" + info.SurveyID);
}
public List <PostAbility> GetPostsAbility(out int total, int[] postIds, int pageIndex, int pageSize)
{
var sql = @"
SELECT pa.RecordId ,
ai.AbilityId ,
pa.PostId ,
p.PostName,
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.IsDelete=0 AND pa.PostId IN (" + postIds.GetString() + ")";
var countSql = @"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.IsDelete=0 AND pa.PostId IN (" + postIds.GetString() + ")";
total = (int)_dataAccess.ExecuteScalar(countSql);
if (total == 0)
{
return(new List <PostAbility>());
}
var list = _dataAccess.GetListBySql <PostAbility>(sql, pageIndex, pageSize, "PostId DESC");
return(list.ToList());
}
/// <summary>
///
/// </summary>
/// <param name="name"></param>
/// <param name="tenantId"></param>
/// <returns></returns>
public static string GetConfig(string name, int tenantId)
{
var configKey = "SystemConfig:Tenant:" + tenantId + ":name:" + name;
var configValue = CacheHelper.CacheService.Get <string>(configKey);
if (configValue == null)
{
var sql = "select ConfigValue from Sys_Config where TenantId = " + tenantId + " and ConfigName='" + name.ReplaceSql() + "'";
configValue = DataAccess.ExecuteScalar(sql) as string;
if (configValue == null)
{
configValue = string.Empty;
var config = new Models.SysConfig();
config.ConfigCode = name;
config.ConfigName = name;
config.ConfigValue = configValue;
config.TenantId = tenantId;
DataAccess.AddEntity(config);
CacheHelper.CacheService.Add(configKey, configValue, CachingExpirationType.Stable);
}
}
return(configValue);
}
/// <summary>
/// 获取某人关注了多少人
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public int GetAttentionCount(int userId)
{
var sql = "SELECT COUNT(*) FROM Int_Relationship WHERE UserId=" + userId;
return((int)_dataAccess.ExecuteScalar(sql));
}
/// <summary>
/// 个人学习记录统计 -【列表】
/// </summary>
/// <returns></returns>
public IEnumerable <Rpt_UserStatisticsByUser> GetUserStatisticsByUser(out int totalCount, int tenantId, string username, string realname, string postname, string deptname, int sex, DateTime?starttime, DateTime?endtime, int?pageIndex, int?pageSize)
{
string courseWhere = "", trainWhere = "", outTrainWhere = "";
if (starttime.HasValue)
{
courseWhere += string.Format(" and EndTime >= '{0}'", starttime.Value);
trainWhere += string.Format(" and EndTime >= '{0}'", starttime.Value);
outTrainWhere += string.Format(" and EndTime >= '{0}'", starttime.Value);
}
if (endtime.HasValue)
{
courseWhere += string.Format(" and EndTime <= '{0}'", endtime.Value);
trainWhere += string.Format(" and EndTime <= '{0}'", endtime.Value);
outTrainWhere += string.Format(" and EndTime >= '{0}'", endtime.Value);
}
totalCount = (int)_dataAccess.ExecuteScalar(string.Format(@"select count(0)
from Sys_Users
left join Sys_Posts on Sys_Users.PostId = Sys_Posts.PostId
left join Sys_Department on Sys_Users.DeptId = Sys_Department.DeptId
where Sys_Users.Status = 0
and Sys_Users.UserName like '%{0}%'
and Sys_Users.RealName like '%{1}%'
and Sys_Posts.PostName like '%{2}%'
and Sys_Department.DeptName like '%{3}%'
and ( Sys_Users.Sex = {4} or {4} = 99)
and Sys_Users.TenantId = {5}
", username.ReplaceSql()
, realname.ReplaceSql()
, postname.ReplaceSql()
, deptname.ReplaceSql()
, sex
, tenantId));
var sql = string.Format(@"select Sys_Users.UserId
,Sys_Users.UserName
,Sys_Users.Realname
,Sys_Posts.PostName
,Sys_Department.DeptName
,(
select count(0) from Len_LearningRecord
where --LearnProcess = 2 and
LearnPath = 0 and SourceId = 0 and userid = Sys_Users.UserId
and CourseId in (select CourseId from Res_Course where Status = 0)
{5}
) as courseCompleteCount
,(
select count(0) from Tr_TrainClass
where ClassId in (
select ClassId from Tr_TrainClassToUser where UserId = Sys_Users.UserId and ApproveStatus=2 and IsDelete=0 and Type=0
)
and IsDelete = 0
and ApproveStatus = 2
{6}
) as trainCompleteCount
,(
select count(0) from Tr_OuterPlan
where ID in(
select ClassId from Tr_TrainClassToUser where UserId = Sys_Users.UserId and ApproveStatus=2 and IsDelete=0 and Type=1
)
and IsDelete = 0
{8}
) as outTrainCompleteCount
from Sys_Users
left join Sys_Posts on Sys_Users.PostId = Sys_Posts.PostId
left join Sys_Department on Sys_Users.DeptId = Sys_Department.DeptId
where Sys_Users.Status = 0
and Sys_Users.UserName like '%{0}%'
and Sys_Users.RealName like '%{1}%'
and Sys_Posts.PostName like '%{2}%'
and Sys_Department.DeptName like '%{3}%'
and ( Sys_Users.Sex = {4} or {4} = 99)
and Sys_Users.TenantId = {7}
", username.ReplaceSql()
, realname.ReplaceSql()
, postname.ReplaceSql()
, deptname.ReplaceSql()
, sex
, courseWhere
, trainWhere
, tenantId
, outTrainWhere);
if (pageIndex.HasValue && pageSize.HasValue)
{
sql = string.Format(DataAccessProvider.DataAccess.PagingSelect, sql, "userId", (pageIndex.Value - 1) * pageSize.Value + 1, pageIndex.Value * pageSize.Value);
}
var list = _dataAccess.GetListBySql <Rpt_UserStatisticsByUser>(sql);
var examuser = _examTestDB.GetAllListByTenantId <tbExamSendStudent>(false, tenantId, "UserID", "RelationID").Where(p => p.SourceType == 0);
var examlist = _examinationDB.GetAllListByTenantId <tbExamination>(false, tenantId)
.Where(p => (!starttime.HasValue || p.ExamEndTime >= starttime.Value) &&
(!endtime.HasValue || p.ExamEndTime <= endtime.Value)
&& p.AlreadySendScore == 1).Select(p => p._id);
foreach (var item in list)
{
item.ExamCompleteCount = examuser.Count(p => p.UserID == item.UserId && examlist.Contains(p.RelationID));
}
return(list);
}
