/// <summary>
/// 获取ApiConfig列表
/// </summary>
/// <returns></returns>
public async Task <IEnumerable <ApiConfig> > GetApiConfigList()
{
try
{
string sql = "SELECT * FROM ApiConfig ";
return(await _mysqlRespository.QueryListAsync(sql, new ApiConfig()));
}
catch (Exception ex)
{
_log.Error("GetApiConfigList method error:" + ex);
throw;
}
}
/// <summary>
/// 根据查询条件获取设置信息
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public async Task <IEnumerable <Setting> > GetSettingsAsync(SettingQuery query)
{
try
{
string sql = "SELECT * FROM Setting WHERE 1=1";
if (query.Id != null)
{
sql += " AND id = @Id";
}
if (!string.IsNullOrWhiteSpace(query.SystemCode))
{
sql += " AND systemCode = @SystemCode";
}
if (query.TypeCode != null)
{
sql += " AND typeCode = @TypeCode";
}
var info = MapperManager.SetMapper <Setting, SettingQuery>(query);
return(await _mysqlRespository.QueryListAsync(sql, info));
}
catch (Exception ex)
{
_log.Error("GetSettingsAsync method error:" + ex);
throw;
}
}
/// <summary>
/// 根据查询条件获取设置信息
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public async Task <IEnumerable <Coupon> > GetCouponsAsync(CouponQuery query)
{
try
{
string sql = "SELECT * FROM Coupon WHERE 1=1";
if (query.UserId != null)
{
sql += " AND userId = @UserId";
}
var info = MapperManager.SetMapper <Coupon, CouponQuery>(query);
return(await _mysqlRespository.QueryListAsync(sql, info));
}
catch (Exception ex)
{
_log.Error("GetCouponsAsync method error:" + ex);
throw;
}
}
/// <summary>
/// 根据产品ID查找媒体信息列表
/// </summary>
/// <param name="proId"></param>
/// <returns></returns>
public async Task <IEnumerable <MediaInfo> > GetMediaInfoListById(string proId)
{
try
{
if (string.IsNullOrWhiteSpace(proId))
{
throw new ArgumentNullException("proId is not null");
}
var query = new MediaQuery
{
MPid = new Guid(proId)
};
string sql = "SELECT * FROM MediaInfo WHERE mPId=@MPid";
return(await _mysqlRespository.QueryListAsync <MediaInfo, MediaQuery>(sql, query));
}
catch (Exception ex)
{
_log.Error("GetMediaInfoListById method error:" + ex);
throw;
}
}
/// <summary>
/// 根据参数获取用户信息(可分页)
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public async Task <IEnumerable <User> > GetCustomerInfoByQueryAsync(UserQuery query)
{
try
{
if (query == null)
{
_log.Error("GetCustomerInfoByQueryAsync's error：selQuery is not null");
throw new Exception("GetCustomerInfoByQueryAsync's error：selQuery is not null");
}
string sql = "SELECT * FROM `User` WHERE 1=1 ";
if (query.CId != null)
{
sql += " AND cId=@cId";
}
if (query.CIdentity != null)
{
sql += " AND cIdentity=@CIdentity";
}
if (!string.IsNullOrWhiteSpace(query.CName))
{
sql += " AND cName=@CName";
}
if (!string.IsNullOrWhiteSpace(query.CEmail))
{
sql += " AND cEmail=@CEmail";
}
if (!string.IsNullOrWhiteSpace(query.CPhone))
{
sql += " AND cPhone=@CPhone";
}
if (query.CIdentity != null)
{
sql += " AND cIdNum=@CIdentity";
}
if (!string.IsNullOrWhiteSpace(query.CNickName))
{
sql += " AND cNickName=@CNickName";
}
if (!string.IsNullOrWhiteSpace(query.CPasswd))
{
sql += " AND cPasswd=@cPasswd";
}
if (!string.IsNullOrWhiteSpace(query.Sort))
{
query.Sort = SqlHandler.ReplaceSQLChar(query.Sort);
sql += $" ORDER BY {query.Sort}";
if (!string.IsNullOrWhiteSpace(query.Order))
{
query.Order = SqlHandler.ReplaceSQLChar(query.Order);
sql += $" {query.Order}";
}
}
if (query.PageIndex != 0 && query.PageSize != 0)
{
query.PageIndex = (query.PageIndex - 1) * query.PageSize;
sql += $" LIMIT {query.PageIndex},{query.PageSize}";
}
var info = SetMapper(query);
var res = await _mysqlRespository.QueryListAsync(sql, info);
return(res);
}
catch (Exception ex)
{
_log.Error("GetCustomerInfoByQueryAsync method error:" + ex);
throw;
}
}
/// <summary>
/// 根据条件查询产品列表
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public async Task <IEnumerable <TravelInfo> > GetTravelInfoListAsync(TravelInfoQuery query)
{
try
{
string sql = "SELECT * FROM TravelInfo WHERE 1=1";
#region 条件
if (query.ProId != null)
{
sql += " AND proId = @ProId";
}
if (!string.IsNullOrWhiteSpace(query.ProType))
{
query.ProType = SqlHandler.ReplaceSQLChar(query.ProType);
query.ProType = $"%{query.ProType}%";
sql += " AND proType LIKE @ProType";
}
if (!string.IsNullOrWhiteSpace(query.Country
))
{
if (query.Country.StartsWith("!"))
{
query.Country = query.Country.TrimStart('!');
sql += " AND country <> @Country";
}
else
{
sql += " AND country = @Country";
}
}
if (!string.IsNullOrWhiteSpace(query.City
))
{
sql += " AND city = @City";
}
if (!string.IsNullOrWhiteSpace(query.Area
))
{
sql += " AND area = @Area";
}
if (query.StartTime != null)
{
sql += " AND startTime >= @StartTime";
}
if (query.Month?.Length > 0)
{
sql += " AND month IN @Month";
}
if (query.EndTime != null)
{
sql += " AND endTime <= @EndTime";
}
if (query.ProDays?.Length > 0)
{
if (query.ProDays.Length > 1)
{
sql += " AND proDays IN @ProDays";
}
else
{
sql += " AND proDays = @ProDays";
}
}
if (query.Them?.Length > 0)
{
for (var i = 0; i < query.Them.Length; i++)
{
if (i == 0)
{
sql += " AND them LIKE '%" + query.Them[i] + "%'";
}
else
{
sql += " OR them LIKE '%" + query.Them[i] + "%'";
}
}
}
if (!string.IsNullOrWhiteSpace(query.Sort))
{
query.Sort = SqlHandler.ReplaceSQLChar(query.Sort);
sql += $" ORDER BY {query.Sort}";
if (!string.IsNullOrWhiteSpace(query.Order))
{
query.Order = SqlHandler.ReplaceSQLChar(query.Order);
sql += $" {query.Order}";
}
}
#endregion
if (query.PageIndex != 0 && query.PageSize != 0)
{
query.PageIndex = (query.PageIndex - 1) * query.PageSize;
sql += $" LIMIT {query.PageIndex},{query.PageSize}";
}
return(await _mysqlRespository.QueryListAsync <TravelInfo, TravelInfoQuery>(sql, query));
}
catch (Exception ex)
{
_log.Error("GetTravelInfoListAsync method error:" + ex);
throw;
}
}