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);
}
}
