public ProductCoursePageResponse GetProductCoursePageInfo(ProductCoursePageRequest request)
{
var response = new ProductCoursePageResponse();
response.DataList = ProductCourseRepository.GetProductCoursePageInfo(request, out int total);
response.Total = total;
return(response);
}
public BaseResponse GetProductCoursePageInfo(ProductCoursePageRequest request)
{
try
{
ProductCoursePageResponse response = ProductCourseRepository.GetProductCoursePageInfo(request);
return(ApiSuccessResult(response));
}
catch (Exception ex)
{
_Log4Net.Error("GetEXTCourseByPage--异常信息", ex);
return(ApiErrorResult(ex.Message));
}
}
public IActionResult ExportExcle(ProductCoursePageRequest request)
{
try
{
ProductCoursePageResponse response = ProductCourseRepository.GetProductCoursePageInfo(request);
foreach (var item in response.DataList)
{
if (item.CourseStatus == "0")
{
item.CourseStatus = "无效";
}
else if (item.CourseStatus == "1")
{
item.CourseStatus = "有效";
}
else if (item.CourseStatus == "-1")
{
item.CourseStatus = "已删除";
}
else
{
item.CourseStatus = "未知";
}
if (item.ExtractStatus == "1000")
{
item.ExtractStatus = "未提取";
}
else if (item.ExtractStatus == "2000")
{
item.ExtractStatus = "已提取";
}
else if (item.ExtractStatus == "3000")
{
item.ExtractStatus = "已关联";
}
else
{
item.ExtractStatus = "未知";
}
}
var stream = ExcelHelper.SaveExcel(response.DataList);
return(File(stream, "application/vnd.ms-excel", "课程信息.xlsx"));
}
catch (Exception ex)
{
_Log4Net.Error("ExportExcle--异常信息", ex);
return(null);
}
}
public List <ProductCoursePageInfo> GetProductCoursePageInfo(ProductCoursePageRequest request, out int total)
{
StringBuilder _query = new StringBuilder();
_query.Append(@"select c.SysDictValue PocSource,a.TeachLevelOneOrgName LevelOneOrgName,a.CourseName,b.ProductTypeName,a.CourseStatus,a.ExtractStatus,
a.FeeUnitPrice,a.FeeUnitPriceName,a.TotalClassHour,a.CourseYear,a.GradeName,a.CategoryName,a.SubjectName,a.TermName,a.ClassTypeName,
count(d.Id) AuthorizeNum,a.ProductCreatedDate as CreatedDate,a.ProductUpdateDate as UpdateDate from T_EXT_Course a
left join T_EXT_ItemType b on a.ProductTypeTwoID = b.ProductTypeID
left join T_EXT_CourseRange d on d.CourseID = a.CourseID
left join T_SYS_Dictionary c on c.SysDictKey = cast(a.POCSource as nvarchar)");
var _groupby = @" group by a.Id,c.SysDictValue,a.TeachLevelOneOrgName,a.CourseName,b.ProductTypeName,a.CourseStatus,a.ExtractStatus,
a.FeeUnitPrice,a.FeeUnitPriceName,a.TotalClassHour,a.CourseYear,a.GradeName,a.CategoryName,a.SubjectName,
a.TermName,a.ClassTypeName,a.ProductCreatedDate,a.ProductUpdateDate";
var _parameters = new DynamicParameters();
var _where = new StringBuilder(" where 1 = 1");
if (request.PocSource?.Count > 0)
{
_where.Append(" and a.POCSource in @Pocsource");
_parameters.Add("@Pocsource", request.PocSource.ToArray());
}
if (request.LevelOneOrgID?.Count > 0)
{
_where.Append(" and a.TeachLevelOneOrgID in @OneOrgId");
_parameters.Add("@OneOrgId", request.LevelOneOrgID.ToArray());
}
if (request.CourseStatus != null && request.CourseStatus != CourseStatusEnum.All)
{
_where.Append(" and a.CourseStatus = @CourseStatus");
_parameters.Add("@CourseStatus", (int)request.CourseStatus);
}
if (request.ExtractStatus != null)
{
_where.Append(" and a.ExtractStatus = @ExtractStatus");
_parameters.Add("@ExtractStatus", (int)request.ExtractStatus);
}
if (!string.IsNullOrWhiteSpace(request.CourseName))
{
var signs = new List <string> {
"'", "[", "%", "_"
};
_where.Append(" and a.CourseName like @CourseName");
_parameters.Add("@CourseName", $"%{request.CourseName.ReplaceBySigns(signs)}%");
}
var countsql = $@"with tab as(
select count(1) num from T_EXT_Course a
left join T_EXT_ItemType b on a.ProductTypeTwoID = b.ProductTypeID
left join T_EXT_CourseRange d on d.CourseID = a.CourseID
left join T_SYS_Dictionary c on c.SysDictKey = cast(a.POCSource as nvarchar)
{_where.ToString()}
{_groupby}
)
select count(1) from tab";
total = GetInfos <int>(EumDBName.POC, countsql, _parameters).First();
if (request.IsExport)
{
return(GetInfos <ProductCoursePageInfo>(EumDBName.POC, _query.ToString() + _where.ToString() + _groupby, _parameters).ToList());
}
return(GetPageInfos <ProductCoursePageInfo>(EumDBName.POC, _query.ToString() + _where.ToString() + _groupby, "a.Id ASC", request.PageIndex, request.PageSize, _parameters).ToList());
}