public async Task <IActionResult> MakeWishList([FromQuery] RequestMakeWishInfo request) { string activityNo = this.GetActivityNo(); var(dataList, total) = await _operatApplication.MakeWishList(activityNo, request); return(this.MyOK(new { dataList, total })); }
/// <summary> /// 许愿列表 /// </summary> /// <param name="request"></param> /// <returns></returns> public async Task <(IEnumerable <MakeWishInfo>, int)> MakeWishList(string activityNo, RequestMakeWishInfo request) { int newPageIndex = (request.pageIndex - 1) * request.pageSize; string strWhere = string.Empty; if (!string.IsNullOrEmpty(request.wishName)) { strWhere += " AND bas.wish_name LIKE CONCAT('%',@wishName,'%')"; } if (!string.IsNullOrEmpty(request.mchName)) { strWhere += " AND mch.mch_name LIKE CONCAT('%',@mchName,'%')"; } if (!string.IsNullOrEmpty(request.tel)) { strWhere += " AND mem.tel = @tel"; } if (request.wishType.HasValue) { strWhere += " AND bas.wish_type = @wishType"; } if (request.status.HasValue) { strWhere += request.status == 1 ? " AND mak.`status` in (1,4)" : " AND mak.`status` = @status"; } if (request.exchangeStatus.HasValue) { strWhere += request.exchangeStatus == 3 ? " AND mak.mch_no is NULL" : " AND mak.exchange_status = @exchangeStatus"; } if (request.startTime.HasValue && request.endTime.HasValue) { request.endTime = request.endTime.Value.AddDays(1).AddSeconds(-1); strWhere += " AND mak.created_time BETWEEN @startTime AND @endTime"; } if (request.upStartTime.HasValue && request.upEndTime.HasValue) { request.upEndTime = request.upEndTime.Value.AddDays(1).AddSeconds(-1); strWhere += " AND mak.updated_time BETWEEN @upStartTime AND @upEndTime"; } if (request.exStartTime.HasValue && request.exEndTime.HasValue) { request.exEndTime = request.exEndTime.Value.AddDays(1).AddSeconds(-1); strWhere += " AND mak.exchange_time BETWEEN @exStartTime AND @exEndTime"; } if (request.wishId > 0) { strWhere += " AND mak.id=@wishId"; } string strSql = $@"SELECT bas.wish_name wishName, mem.nick_name nickName, mem.tel, mak.created_time createdTime, bas.wish_type wishType, bas.rules rulesStr, bas.wish_unit wishUnit, mak.`status`, mak.updated_time updatedTime, mch.mch_name mchName, IF(mak.mch_no is NULL,NULL,mak.exchange_status) exchangeStatus, mak.exchange_time exchangeTime, mak.`code`, COUNT( hel.id ) helpTotal, mak.id wishId FROM t_make_wish mak LEFT JOIN t_wish_base bas ON bas.wish_no = mak.wish_no LEFT JOIN t_member mem ON mem.id = mak.member_id LEFT JOIN t_merchant mch ON mch.mch_no = mak.mch_no LEFT JOIN t_help_wish hel ON hel.make_wish_id = mak.id WHERE mak.activity_no = @activityNo {strWhere} GROUP BY bas.wish_name, mem.nick_name, mem.tel, mak.created_time, bas.wish_type, bas.rules, bas.wish_unit, mak.`status`, mak.updated_time, mch.mch_name, mak.exchange_status, mak.exchange_time, mak.`code`, mak.id ORDER BY mak.created_time ASC, mak.id ASC LIMIT {newPageIndex},{request.pageSize}; SELECT COUNT( DISTINCT mak.id ) FROM t_make_wish mak LEFT JOIN t_wish_base bas ON bas.wish_no = mak.wish_no LEFT JOIN t_member mem ON mem.id = mak.member_id LEFT JOIN t_merchant mch ON mch.mch_no = mak.mch_no LEFT JOIN t_help_wish hel ON hel.make_wish_id = mak.id WHERE mak.activity_no = @activityNO {strWhere};"; var param = new DynamicParameters(); param.Add("activityNo", activityNo); param.AddDynamicParams(request); var(dataList, total) = await _jiakeRepository.SQLQueryWithReturnAsync <MakeWishInfo>(strSql, param); return(dataList, total); }