private UserPostAbilityExam GetExam(int examId, int reId, int userId)
{
var exampaperDB = new RetechWing.Exam.DataAgent.ExampaperDb();
var examinationDB = new RetechWing.Exam.DataAgent.ExaminationDb();
var exam = exampaperDB.GetSingleById <tbExampaperRedundancy>(examId);
var examuser = examinationDB.GetExamSendStudentWithByRelationIdAndUserIdAndExampaperId(reId, 2, examId, userId);
var result = new UserPostAbilityExam();
if (examuser == null)
{
//没参加考试
result.ExamCount = -1;
result.ExamStatus = "未参加";
result.GetScore = -1;
result.IsPassed = "--";
}
else
{
result.ExamCount = examuser.TestTimes;
result.ExamStatus = "已参加";
result.GetScore = examuser.Score;
result.IsPassed = examuser.IsPass == 0 ? "未通过" : "已通过";
}
result.ExanName = exam.ExampaperTitle;
result.AllowCount = 100;
result.ExamTimeLength = 100;
//result.PassScore=exam.ExampaperScore*
result.TotalScore = exam.ExampaperScore;
return(result);
}
public List <UserPromotionDetail> GetUserPromotionDetails(int userId)
{
//第一步，查询出所有申请过的晋升路径
var sql = @"SELECT pa.PromotionId,pw.PromotionName,pa.ApplyId,pa.CurrentStageId
FROM Ab_PromotionApply pa
LEFT JOIN Ab_PromotionWay pw ON pw.PromotionId = pa.PromotionId
WHERE pa.ApproveStatus in (2,4) and pa.UserId=" + userId;
var list = _dataAccess.FetchListBySql <UserPromotionDetail>(sql);
//--第二步，查询出晋升路径下的阶段（通过晋升记录）
var applyIds = list.Select(p => p.ApplyId).Distinct().GetString();
var stageSql = @"SELECT ps.StageId,ps.ExamId,pslr.ApplyId,p.PostName,p.PostId,ps.OrderNum,ps.Period,pslr.StartTime,pslr.EndTime
FROM Ab_PromotionStageLenRecord pslr
LEFT JOIN Ab_PromotionStage ps ON ps.StageId = pslr.Stageid
LEFT JOIN Sys_Posts p ON p.PostId = ps.PostId
WHERE pslr.ApplyId IN (" + applyIds + ")";
var details = _dataAccess.FetchListBySql <UserApplyPromotionStageDetail>(stageSql);
var currentDetails = _dataAccess.GetList <PromotionStage>("Ab_PromotionStage.StageId in ( select CurrentStageId from Ab_PromotionApply where Ab_PromotionApply.ApplyId in (" + applyIds + "))");
foreach (var detail in list)
{
var tmp = details.Where(p => p.ApplyId == detail.ApplyId);
detail.StageDetails.AddRange(tmp);
if (tmp.Any(p => p.StageId == detail.CurrentStageId))
{
continue;
}
var currentStage = currentDetails.Find(p => p.StageId == detail.CurrentStageId);
detail.StageDetails.Add(new UserApplyPromotionStageDetail
{
StageId = currentStage.StageId,
ExamId = currentStage.ExamId,
ApplyId = detail.ApplyId,
PostName = currentStage.PostName,
OrderNum = currentStage.OrderNum,
Period = currentStage.Period,
StartTime = DateTime.Now,
EndTime = null,
StudyDays = 1,
PostId = currentStage.PostId
});
}
//--第三步，找到阶段下的所有课程和考试
//准备计算学习进度的数据
var postIds = details.Select(p => p.PostId).GetString();
//找到课程
var resourceSql = @"
SELECT ResourceId , ar.AbilityId , pa.PostId, ply.ApplyId
FROM Ab_AbilityResource ar
INNER JOIN Ab_PostAbility pa ON pa.AbilityId = ar.AbilityId
INNER JOIN Ab_PromotionApply ply ON ply.PostId = pa.PostId
WHERE ResourceType=0 AND pa.PostId IN(" + postIds + ") and ar.isdelete = 0 -- and pa.isdelete = 0 and ply.isdelete = 0";
List <dynamic> resourceList = _dataAccess.FetchListBySql <dynamic>(resourceSql).ToList();
//第四步，查询学习记录
var lrsql = @"SELECT CourseId,LearnProcess,ApplyId,StageId FROM Len_LearningRecord llr
JOIN Ab_LearningRecord alr ON llr.SourceId=alr.RecordId
WHERE llr.LearnPath=3 AND alr.ApplyId IN (" + applyIds + ")";
//阶段学习记录
var stageLearnRecords =
_dataAccess.GetListBySql <AbPromotionStageLenRecord>(
string.Format(@"select RecordId,StageId,ApplyId from Ab_PromotionStageLenRecord where ApplyId IN( {0}) ", applyIds));
var learnRecordList = _dataAccess.FetchListBySql <dynamic>(lrsql);
foreach (var promotionDetail in list)
{
foreach (var detail in promotionDetail.StageDetails)
{
var stageTotalCourse =
resourceList.Count(p => detail.PostId == p.PostId && p.ApplyId == detail.ApplyId);
var passnum = 0;
var courseIds = "";
//查询课程通过数
foreach (var o in learnRecordList)
{
if (o.ApplyId == detail.ApplyId && o.StageId == detail.StageId)
{
//判断是否通过
if (o.LearnProcess == 2)
{
courseIds += o.CourseId + ",";
passnum++;
}
}
}
var sqls = "";
//获得课程学分
if (!String.IsNullOrEmpty(courseIds) && courseIds.Split(',').Count() > 0)
{
courseIds = courseIds.TrimEnd(',');
sqls = "select sum(LearnScore)as totalScore from dbo.Res_Course where CourseId in(" + courseIds + ")";
var score = _dataAccess.FetchListBySql <int>(sqls).ToList();
detail.Score = score[0];
}
else
{
detail.Score = 0;
}
//获得综合得分
var totalScore = _dataAccess.ExecuteScalar(
"select sum(Score) as totalScore from dbo.Ab_AbilityRating where PromotionId=" + promotionDetail.PromotionId + " and PostId=" + detail.PostId + " and UserId=" + userId);
if (totalScore != null && !String.IsNullOrEmpty(totalScore.ToString()))
{
detail.totalScore = detail.Score + Convert.ToInt32(totalScore.ToString());
}
else
{
detail.totalScore = detail.Score;
}
//考试是否通过的判断
if (detail.ExamId > 0)
{
stageTotalCourse += 1;
var record =
stageLearnRecords.FirstOrDefault(
p => p.ApplyId == detail.ApplyId && p.StageId == detail.StageId);
if (record == null)
{
break;
}
var examuser = new RetechWing.Exam.DataAgent.ExaminationDb().GetExamSendStudentWithByRelationIdAndUserId(record.RecordId, 2, userId).FirstOrDefault();
if (examuser == null)
{
break;
}
if (examuser.ExamPaperID != detail.ExamId || examuser.IsPass == 0)
{
break;
}
passnum++;
}
detail.Process = stageTotalCourse > 0 ? (int)(passnum * 100m / stageTotalCourse) : 0;
}
}
return(list.ToList());
}
/// <summary>
///
/// </summary>
/// <param name="applyId"></param>
/// <returns></returns>
public List <PromotionStageUserStatus> GetPromotionUserDetail(int applyId)
{
var list = new List <PromotionStageUserStatus>();
var apply = _dataAccess.Get <PromotionApply>(applyId);
if (apply.ApproveStatus == 3)
{
return(list);
}
var stage = _dataAccess.Get <PromotionStage>(apply.CurrentStageId);
if (apply.ApproveStatus == 0 || apply.ApproveStatus == 1)
{
var model = new PromotionStageUserStatus()
{
ApproveStatus = apply.ApproveStatus,
PostName = stage.PostName,
StageDesc = stage.Description,
Reason = apply.Reason,
OrderNum = 0
};
list.Add(model);
return(list);
}
list =
_dataAccess.FetchListBySql <PromotionStageUserStatus>(@"SELECT pslr.RecordId,pst.PromotionId,pst.StageId,pst.ExamId,p.PostId,p.PostName,pslr.EndTime PromotionSuccessTime,pst.Description StageDesc,pst.OrderNum,pst.Period,pslr.StartTime
FROM Ab_PromotionStageLenRecord pslr
JOIN Ab_PromotionStage pst ON pst.StageId = pslr.Stageid
JOIN Sys_Posts p ON p.PostId = pst.PostId
WHERE ApplyId=" + applyId).ToList();
foreach (var way in list)
{
if (way.PromotionSuccessTime.HasValue)
{
way.ApproveStatus = 4;
}
else
{
if (way.Period == 0)
{
way.ApproveStatus = 2;
}
else
{
if (way.StartTime.AddDays(way.Period) > DateTime.Now)
{
way.ApproveStatus = 2;
}
else
{
way.ApproveStatus = 3;
}
}
}
if (way.ApproveStatus == 2)
{
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 in (select ApplyId from Ab_PromotionApply where UserId = {1} and PromotionId = {2} and ApproveStatus = 2 and IsDelete = 0))
and UserId = {1}", way.StageId, apply.UserId, apply.PromotionId));
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}
)", way.PostId));
if (stage.ExamId > 0)
{
allCount++;
var examuser = new RetechWing.Exam.DataAgent.ExaminationDb().GetExamSendStudentWithByRelationIdAndUserId(way.StageId, 2, apply.UserId).FirstOrDefault();
if (examuser != null && examuser.ExamPaperID == stage.ExamId && examuser.IsPass == 1)
{
completeCount++;
}
}
way.PromotionProcess = allCount == 0 ? 0 : completeCount * 100 / allCount;
}
else if (way.ApproveStatus == 3)
{
way.CourseName = _dataAccess.FetchListBySql <string>(string.Format(@"select CourseName from Res_Course
where CourseId in (
select ResourceId 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}
)
)
and CourseId not in (
select CourseId from Len_LearningRecord
where LearnProcess = 2 and LearnPath = 3
and SourceId in (select RecordId from Ab_LearningRecord where StageId = {1} and ApplyId = {4})
and UserId = {2} and EndTime <= '{3}'
)", way.PostId
, way.StageId
, apply.UserId
, way.StartTime.AddDays(way.Period).ToString("yyyy-MM-dd HH:mm:ss")
, applyId)).ToList();
if (way.ExamId > 0)
{
var examuser =
new RetechWing.Exam.DataAgent.ExaminationDb().GetExamSendStudentWithByRelationIdAndUserId(
way.RecordId, 2, apply.UserId).FirstOrDefault();
if (!(examuser != null && examuser.ExamPaperID == stage.ExamId && examuser.IsPass == 1))
{
var paper = new RetechWing.Exam.DataAgent.ExampaperDb().GetSingleById <tbExampaperRedundancy>(stage.ExamId);
if (paper != null)
{
way.ExamName = paper.ExampaperTitle;
}
}
}
}
}
if (list.Count == 0 || apply.CurrentStageId != list.OrderByDescending(p => p.OrderNum).First().StageId)
{
list.Add(new PromotionStageUserStatus()
{
PostName = stage.PostName,
ApproveStatus = 2,
PromotionProcess = 0,
StageDesc = stage.Description
});
}
return(list);
}
