public BaseResponse GetCouseListByExtractor(GetCouseListByExtractorRequest request) { try { GetCouseListByExtractorResponse response = productCourseDomainService.GetCouseListByExtractor(request); return(ApiSuccessResult(response)); } catch (Exception ex) { _Log4Net.Error("PreviewJudge--异常信息", ex); return(ApiErrorResult(ex.Message)); } }
/// <summary> /// 根据提取器信息获取课程信息 /// </summary> /// <param name="request"></param> /// <returns></returns> public GetCouseListByExtractorResponse GetCouseListByExtractor(GetCouseListByExtractorRequest request) { //根据产品动销阈值定义最后销售时间 switch (request.thresholdValue) { case (int)ThresholdValue.NoLimit: request.lastSaleTime = null; break; case (int)ThresholdValue.ThreeMonths: request.lastSaleTime = DateTime.Now.AddMonths(-3); break; case (int)ThresholdValue.SixMonths: request.lastSaleTime = DateTime.Now.AddMonths(-6); break; case (int)ThresholdValue.OneYear: request.lastSaleTime = DateTime.Now.AddYears(-1); break; case (int)ThresholdValue.TwoYear: request.lastSaleTime = DateTime.Now.AddYears(-2); break; default: request.lastSaleTime = null; break; } GetCouseListByExtractorResponse response = ProductCourseRepository.GetCouseListByExtractor(request); if (response.dataList != null && response.dataList.Count > 0) { foreach (var item in response.dataList) { item.CourseStatusName = EnumExtension.ToEnumTypeDescriptionByID(typeof(CourseStatusEnum), item.CourseStatus.ToString()); } } return(response); }
/// <summary> /// 根据提取器信息获取课程信息 /// </summary> /// <param name="request"></param> /// <returns></returns> public GetCouseListByExtractorResponse GetCouseListByExtractor(GetCouseListByExtractorRequest request) { GetCouseListByExtractorResponse response = new GetCouseListByExtractorResponse(); string _sqlStr = @"SELECT tb1.*, ProductTypeName =(select top 1 ProductTypeName from T_EXT_ItemType where ProductTypeID = tb1.ProductTypeTwoID order by NodeFlag ) , CampusCountName=(convert(varchar(50),(SELECT Count(1) FROM [dbo].[T_EXT_CourseRange] where CourseID =tb1.CourseID))+'') FROM [dbo].[T_EXT_Course] tb1 "; //查询条件 StringBuilder _where = new StringBuilder(" WHERE 1=1 "); var _parameters = new DynamicParameters(); StringBuilder _join = new StringBuilder(""); //根据提取器Id查询 if (!string.IsNullOrEmpty(request.extractorDetailGuid)) { _join.AppendFormat(" inner join [dbo].[T_POC_Product] tb3 on tb1.FKProductCourseGuid=tb3.ProductGuid"); _where.AppendFormat(" and tb1.ExtractStatus = @ExtractStatus "); _where.AppendFormat(" and tb3.FKDetailGuid = @FKDetailGuid "); _parameters.Add("@ExtractStatus", request.extractStatus); _parameters.Add("@FKDetailGuid", request.extractorDetailGuid); } //根据条件查询 else { //来源 if (request.fromSystems != null && request.fromSystems.Count > 0) { _where.AppendFormat(" and tb1.POCSource in @POCSource "); _parameters.Add("@POCSource", request.fromSystems.ToArray()); } //事业部 if (request.oneOrgIds != null && request.oneOrgIds.Count > 0) { _where.AppendFormat(" and tb1.TeachLevelOneOrgID in @TeachLevelOneOrgID "); _parameters.Add("@TeachLevelOneOrgID", request.oneOrgIds.ToArray()); } //产品状态 if (request.courseStatus == 0 || request.courseStatus == 1) { _where.AppendFormat(" and tb1.CourseStatus = @CourseStatus "); _parameters.Add("@CourseStatus", request.courseStatus); } //动销阈值 if (request.lastSaleTime != null) { _join.AppendFormat(" left join [dbo].[T_EXT_ThresholdValue] tb2 on tb1.CourseID=tb2.ProductID and tb1.FromSystem = tb2.FromSystem "); _where.AppendFormat(" and tb2.LastSaleDate >= @LastSaleDate "); _parameters.Add("@LastSaleDate", request.lastSaleTime); } //课程所属年份 if (request.courseYear > 0) { _where.AppendFormat(" and tb1.CourseYear = @CourseYear "); _parameters.Add("@CourseYear", request.courseYear); } //课程所属年级 if (request.gradeId != null && request.gradeId.Count > 0) { _where.AppendFormat(" and tb1.GradeID in @GradeID "); _parameters.Add("@GradeID", request.gradeId); } //课程所属类型 if (request.categoryId != null && request.categoryId.Count > 0) { _where.AppendFormat(" and tb1.CategoryID in @CategoryID "); _parameters.Add("@CategoryID", request.categoryId); } //课程所属科目 if (request.subjectId != null && request.subjectId.Count > 0) { _where.AppendFormat(" and tb1.SubjectID in @SubjectID "); _parameters.Add("@SubjectID", request.subjectId); } //课程所属期段 if (request.termId != null && request.termId.Count > 0) { _where.AppendFormat(" and tb1.TermID in @TermID "); _parameters.Add("@TermID", request.termId); } //课程所属班型 if (request.classTypeId != null && request.classTypeId.Count > 0) { _where.AppendFormat(" and tb1.ClassTypeID in @ClassTypeID "); _parameters.Add("@ClassTypeID", request.classTypeId); } //排除的课程Id if (request.notInCourseGuid != null && request.notInCourseGuid.Count > 0) { _where.AppendFormat(" and tb1.CourseID not in @notInCourseGuid "); _parameters.Add("@notInCourseGuid", request.notInCourseGuid); } _where.AppendFormat(" and tb1.ExtractStatus = @ExtractStatus "); _parameters.Add("@ExtractStatus", (int)ExtractState.Unextracted); } //排除已删除的 _where.AppendFormat(" and tb1.CourseStatus != -1 "); //总数 string countsql = @"SELECT Count(1) FROM [dbo].[T_EXT_Course] tb1 " + _join.ToString() + _where.ToString(); response.totalCount = GetInfos <int>(EumDBName.POC, countsql, _parameters).First(); if (!request.IsCount) { //排序 string _orderBy = " order by tb1.Id desc"; //分页 var sql = _sqlStr.ToString() + _join.ToString() + _where.ToString() + _orderBy; var pageIndex = request.PageIndex; var pageSize = request.PageSize; var _queryPage = $"{sql} offset {(pageIndex - 1) * pageSize} row fetch next {pageSize} rows only"; response.dataList = GetInfos <CourseItem>(EumDBName.POC, _queryPage.ToString(), _parameters).ToList(); } return(response); }