public async Task <IActionResult> SelectScheduleListByCondition([FromBody] GetScheduleListByConditionRequestDto requestDto) { if (string.IsNullOrWhiteSpace(requestDto.MerchantGuid)) { requestDto.MerchantGuid = UserID; } var result = await new ConsumptionBiz().SelectScheduleListByCondition(requestDto); return(Success(result)); }
/// <summary> /// 按条件查询排班列表 List /// </summary> /// <param name="fromId"></param> /// <param name="projectGuid"></param> /// <returns></returns> public async Task <GetScheduleListByConditionPageResponseDto> SelectScheduleListByCondition(GetScheduleListByConditionRequestDto requestDto) { using (var conn = MySqlHelper.GetConnection()) { var wherePhone = " "; var whereMerchantGuid = " "; var whereProjectGuid = " "; var whereDate = " "; var whereTherapistGuid = " "; if (!string.IsNullOrWhiteSpace(requestDto.Phone)) { wherePhone += $" AND g.phone = '{requestDto.Phone}' "; } if (!string.IsNullOrWhiteSpace(requestDto.MerchantGuid)) { whereMerchantGuid += $" And a.merchant_guid ='{requestDto.MerchantGuid}' "; } if (!string.IsNullOrWhiteSpace(requestDto.ProjectGuid)) { whereProjectGuid += $" AND a.project_guid = '{requestDto.ProjectGuid}' "; } if (requestDto.Date != null) { whereDate += $" AND d.schedule_date='{requestDto.Date.Value.ToString("yyyy/MM/dd")}' "; } if (!string.IsNullOrWhiteSpace(requestDto.TherapistGuid)) { whereTherapistGuid += $" And b.therapist_guid='{requestDto.TherapistGuid}' "; } var sql = $@"DROP TEMPORARY TABLE IF EXISTS tmp_schedule; DROP TEMPORARY TABLE IF EXISTS tmp_goods; DROP TEMPORARY TABLE IF EXISTS tmp_result; DROP TEMPORARY TABLE IF EXISTS tmp_result_1; CREATE TEMPORARY TABLE tmp_schedule AS SELECT distinct d.schedule_date, d.schedule_guid, a.project_guid, b.therapist_guid, b.therapist_name FROM t_mall_project_merchant a INNER JOIN t_merchant_therapist b ON a.merchant_guid = b.merchant_guid INNER JOIN t_merchant_therapist_project c ON c.therapist_guid = b.therapist_guid AND c.project_guid = a.project_guid INNER JOIN t_merchant_schedule AS d ON d.target_guid = b.therapist_guid WHERE 1=1 {whereMerchantGuid} {whereProjectGuid} {whereDate} {whereTherapistGuid} ORDER BY schedule_date; CREATE TEMPORARY TABLE tmp_goods AS SELECT a.project_guid, g.user_guid, a.goods_item_guid FROM t_consumer_goods_item a INNER JOIN t_consumer_goods f ON f.goods_guid = a.goods_guid INNER JOIN t_utility_user g ON g.user_guid = f.user_guid WHERE f.available=1 and a.remain > 0 AND ( ( DATE_FORMAT( '{requestDto.Date.Value.ToString("yyyy/MM/dd")}', '%Y-%m-%d' ) BETWEEN DATE_FORMAT( f.effective_start_date, '%Y-%m-%d' ) AND DATE_FORMAT( f.effective_end_date, '%Y-%m-%d' ) ) OR ( f.effective_start_date IS NULL AND effective_end_date IS NULL ) ) {whereProjectGuid} {wherePhone} LIMIT 1; CREATE TEMPORARY TABLE tmp_result AS SELECT a.schedule_guid, a.schedule_date, a.therapist_guid, a.therapist_name, b.goods_item_guid FROM tmp_schedule a LEFT JOIN tmp_goods b ON a.project_guid = b.project_guid ORDER BY a.schedule_date; CREATE TEMPORARY TABLE tmp_result_1 SELECT * FROM tmp_result; SELECT count( 1 ) as TotalNum FROM tmp_result_1; SELECT * FROM tmp_result order by schedule_date LIMIT {(requestDto.PageIndex - 1) * requestDto.PageSize}, {requestDto.PageSize}; " ; var reader = await conn.QueryMultipleAsync(sql); int total = (await reader.ReadAsync <TotalNumDto>()).FirstOrDefault().TotalNum; var result = await reader.ReadAsync <GetScheduleListByConditionResponseDto>(); GetScheduleListByConditionPageResponseDto responseDto = new GetScheduleListByConditionPageResponseDto { Total = total, CurrentPage = result }; return(responseDto); } }