public async Task <GetMerchantPageResponseDto> GetMerchantPageAsync(GetMerchantPageRequestDto request)
{
var sqlWhere = $@"1=1 ";
sqlWhere = $"{sqlWhere} AND status = '{MerchantModel.StatusEnum.Approved.ToString()}'";
if (!string.IsNullOrWhiteSpace(request.Name))
{
sqlWhere = $"{sqlWhere} AND Merchant_Name like @Name";
}
if (!string.IsNullOrWhiteSpace(request.Scope))
{
sqlWhere = $"{sqlWhere} AND scope like @Scope";
}
if (request.BeginDate != null)
{
request.BeginDate = request.BeginDate?.Date;
sqlWhere = $"{sqlWhere} AND creation_date > @BeginDate";
}
if (request.EndDate != null)
{
request.EndDate = request.EndDate?.AddDays(1).Date;
sqlWhere = $"{sqlWhere} AND creation_date < @EndDate";
}
if (!string.IsNullOrWhiteSpace(request.PlatformType))
{
if (request.PlatformType.ToLower() == PlatformType.CloudDoctor.ToString().ToLower())
{
sqlWhere = $"{sqlWhere} and platform_type = @PlatformType";
}
else
{
sqlWhere = $"{sqlWhere} and platform_type != '{PlatformType.CloudDoctor.ToString()}'";
}
}
var sql = $@"
SELECT * FROM(
SELECT
A.*,
CONCAT( B.base_path, B.relative_path ) AS signatureUrl,
C.user_name,
D.scope,
E.MonthlySales,
E.TotalMonthlyAmount
FROM
t_merchant A
LEFT JOIN t_utility_accessory B ON B.accessory_guid = A.signature_guid
LEFT JOIN t_utility_user C ON C.user_guid = A.merchant_guid
LEFT JOIN (
SELECT
a.merchant_guid,
GROUP_CONCAT( b.config_name ) scope
FROM
t_merchant_scope a
LEFT JOIN t_manager_dictionary b ON a.scope_dic_guid = b.dic_guid
GROUP BY
a.merchant_guid
) D ON A.merchant_guid = D.merchant_guid
LEFT JOIN (
SELECT
yue,
merchant_guid,
count( 1 ) AS MonthlySales,
SUM( product_price * product_count ) AS TotalMonthlyAmount
FROM
(
SELECT
DATE_FORMAT( a.creation_date, '%Y-%m' ) AS yue,
b.merchant_guid,
a.product_price,
a.product_count
FROM
t_mall_order_detail a
LEFT JOIN t_mall_product b ON a.product_guid = b.product_guid
WHERE
a.`enable` = 1
) t
WHERE
yue = DATE_FORMAT( now( ), '%Y-%m' )
GROUP BY
yue,
merchant_guid
) E ON A.merchant_guid = E.merchant_guid
) __t
WHERE
{sqlWhere}
ORDER BY
creation_date desc" ;
request.Name = $"%{request.Name}%";
request.Scope = $"%{request.Name}%";
return(await MySqlHelper.QueryByPageAsync <GetMerchantPageRequestDto, GetMerchantPageResponseDto, GetMerchantPageItemDto>(sql, request));
}
public async Task <GetMerchantPageResponseDto> GetMerchantPageAsync(GetMerchantPageRequestDto request)
{
var sqlWhere = $@"1=1 ";
sqlWhere = $"{sqlWhere} AND status = '{MerchantModel.StatusEnum.Approved.ToString()}'";
if (!string.IsNullOrWhiteSpace(request.Name))
{
request.Name = $"{request.Name}%";
sqlWhere = $"{sqlWhere} AND merchant_name like @Name";
}
if (!string.IsNullOrWhiteSpace(request.Scope))
{
request.Scope = $"%{request.Scope}%";
sqlWhere = $"{sqlWhere} AND scope like @Scope";
}
if (request.BeginDate != null)
{
request.BeginDate = request.BeginDate?.Date;
sqlWhere = $"{sqlWhere} AND creation_date > @BeginDate";
}
if (request.EndDate != null)
{
request.EndDate = request.EndDate?.AddDays(1).Date;
sqlWhere = $"{sqlWhere} AND creation_date < @EndDate";
}
var sql = $@"
SELECT * FROM(
SELECT
A.merchant_name,
A.merchant_guid,
A.hospital_guid,
A.account,
A.province,
A.city,
A.area,
A.street,
A.telephone,
A.STATUS,
A.creation_date,
A.enable,
CONCAT( B.base_path, B.relative_path ) AS signatureUrl,
D.scope,
C.hos_name
FROM
t_merchant A
LEFT JOIN t_utility_accessory B ON B.accessory_guid = A.signature_guid
LEFT JOIN t_doctor_hospital C ON A.hospital_guid = C.hospital_guid
LEFT JOIN (
SELECT
a.merchant_guid,
GROUP_CONCAT( b.config_name ) scope
FROM
t_merchant_scope a
LEFT JOIN t_manager_dictionary b ON a.scope_dic_guid = b.dic_guid
WHERE b.enable=1
GROUP BY
a.merchant_guid
) D ON A.merchant_guid = D.merchant_guid
) __t
WHERE
{sqlWhere}
ORDER BY
creation_date desc" ;
return(await MySqlHelper.QueryByPageAsync <GetMerchantPageRequestDto, GetMerchantPageResponseDto, GetMerchantPageItemDto>(sql, request));
}
public async Task <IActionResult> GetMerchantPageAsync([FromBody] GetMerchantPageRequestDto request)
{
var response = await new MerchantBiz().GetMerchantPageAsync(request);
return(Success(response));
}
