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