Exemple #1
0
 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));
     }
 }
Exemple #2
0
 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);
        }