public BaseResponse GetExtractorDetaiList(GetExtractorDetaiListRequest request)
{
try
{
GetExtractorDetailListResponse response = pocExtractorDetailDomainService.GetExtractorDetaiList(request);
return(ApiSuccessResult(response));
}
catch (Exception ex)
{
_Log4Net.Error("GetExtractorDetaiList--异常信息", ex);
return(ApiErrorResult(ex.Message));
}
}
public IActionResult ExportExcle(GetExtractorDetaiListRequest request)
{
try
{
GetExtractorDetailListResponse response = pocExtractorDetailDomainService.GetExtractorDetaiList(request);
var stream = ExcelHelper.SaveExcel(response.dataList);
return(File(stream, "application/vnd.ms-excel", "提取器.xlsx"));
}
catch (Exception ex)
{
_Log4Net.Error("ExportExcle--异常信息", ex);
return(null);
}
}
/// <summary>
/// 获取提取器列表
/// </summary>
/// <returns></returns>
public GetExtractorDetailListResponse GetExtractorDetaiList(GetExtractorDetaiListRequest request)
{
GetExtractorDetailListResponse response = new GetExtractorDetailListResponse();
string sqlStr = @" select tb1.*, ( select (stuff((select ',' + SelectFieldTypeName from [T_POC_ExtractorQuery] where FKExtractorGuid =
tt.FKExtractorGuid and SelectFieldTypeCode='ExtFromSystem' for xml path('')),1,1,'')) from [T_POC_ExtractorQuery] tt where tt.SelectFieldTypeCode='ExtFromSystem' and tt.FKExtractorGuid=tb1.FKExtractorGuid group by FKExtractorGuid) as 'ExtFromSystem',
( select (stuff((select ',' + SelectFieldTypeName from [T_POC_ExtractorQuery] where FKExtractorGuid =
tt.FKExtractorGuid and SelectFieldTypeCode='OneOrg' for xml path('')),1,1,'')) from [T_POC_ExtractorQuery] tt where tt.SelectFieldTypeCode='OneOrg' and tt.FKExtractorGuid=tb1.FKExtractorGuid group by FKExtractorGuid) as 'OneOrg'
from [dbo].[T_POC_ExtractorDetail] tb1 inner join [dbo].[T_POC_Extractor] tb2 on
tb1.FKExtractorGuid = tb2.ExtractorGuid ";
//查询条件
StringBuilder _where = new StringBuilder(" WHERE 1=1 ");
var _parameters = new DynamicParameters();
StringBuilder _innerJoin = new StringBuilder("");
//来源
if (request.ExtFromSystem != null && request.ExtFromSystem.Count > 0)
{
_innerJoin.AppendFormat(@" inner join ( select FKExtractorGuid from [T_POC_ExtractorQuery] tb4 where SelectFieldTypeCode='ExtFromSystem' AND tb4.SelectFieldTypeId in @ExtFromSystem GROUP BY FKExtractorGuid ) tb3
on tb3.FKExtractorGuid =tb1.FKExtractorGuid ");
_parameters.Add("@ExtFromSystem", request.ExtFromSystem.ToArray());
}
//事业部
if (request.LevelOneGuid != null && request.LevelOneGuid.Count > 0)
{
_innerJoin.AppendFormat(@" inner join ( select FKExtractorGuid from [T_POC_ExtractorQuery] tb6 where SelectFieldTypeCode='OneOrg' AND tb6.SelectFieldTypeId in @LevelOneGuid GROUP BY FKExtractorGuid ) tb5
on tb5.FKExtractorGuid =tb1.FKExtractorGuid ");
_parameters.Add("@LevelOneGuid", request.LevelOneGuid.ToArray());
}
//提取器编号
if (!string.IsNullOrEmpty(request.ExtractorNo))
{
_where.AppendFormat(" AND tb1.ExtractorNo = @ExtractorNo");
_parameters.Add("@ExtractorNo", request.ExtractorNo);
}
//提取器状态
if (!string.IsNullOrEmpty(request.ExtractorStatus) && request.ExtractorStatus != "0")
{
_where.AppendFormat(" AND tb1.ExtractorStatus = @ExtractorStatus");
_parameters.Add("@ExtractorStatus", request.ExtractorStatus);
}
//课程所属年级
if (request.GradeID != null && request.GradeID.Count > 0)
{
_where.AppendFormat(" AND tb1.GradeID in @GradeID");
_parameters.Add("@GradeID", request.GradeID.ToArray());
}
//课程所属类型
if (request.CategoryID != null && request.CategoryID.Count > 0)
{
_where.AppendFormat(" AND tb1.CategoryID in @CategoryID");
_parameters.Add("@CategoryID", request.CategoryID.ToArray());
}
//课程所属科目
if (request.SubjectID != null && request.SubjectID.Count > 0)
{
_where.AppendFormat(" AND tb1.SubjectID in @SubjectID");
_parameters.Add("@SubjectID", request.SubjectID.ToArray());
}
//课程所属期望值
if (request.TermID != null && request.TermID.Count > 0)
{
_where.AppendFormat(" AND tb1.TermID in @TermID");
_parameters.Add("@TermID", request.TermID.ToArray());
}
//课程所属版型
if (request.ClassTypeID != null && request.ClassTypeID.Count > 0)
{
_where.AppendFormat(" AND tb1.ClassTypeID in @ClassTypeID");
_parameters.Add("@ClassTypeID", request.ClassTypeID.ToArray());
}
//总数
string countsql = @" select Count(1)
from [dbo].[T_POC_ExtractorDetail] tb1 inner join [dbo].[T_POC_Extractor] tb2 on
tb1.FKExtractorGuid = tb2.ExtractorGuid " + _innerJoin.ToString() + _where.ToString();
response.totalCount = GetInfos <int>(EumDBName.POC, countsql, _parameters).First();
//排序
string _orderBy = " order by tb1.Id desc";
//分页
var sql = sqlStr.ToString() + _innerJoin.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 <ExtractorDetailItem>(EumDBName.POC, _queryPage.ToString(), _parameters).ToList();
return(response);
}