private QueryWhere GetQueryWhere(long userId, GoodsQueryParam param)
{
string where = "";
var nativeParams = new Dictionary <string, object>()
{
{ "userId", userId }
};
if (param.AmountMin.HasValue)
{
where += " and g.Amount >= CAST(@amountMin as money)";
nativeParams["amountMin"] = param.AmountMin;
}
if (param.AmountMax.HasValue)
{
where += " and g.Amount <= CAST(@amountMax as money)";
nativeParams["amountMax"] = param.AmountMax;
}
return(new QueryWhere
{
Clause = where,
Params = nativeParams
});
}
public override async Task <int> GetCount(long userId, GoodsQueryParam param)
{
string sql = @"select count(distinct g.Id) Count
from PurchaseItems pi
inner join Goods g on pi.GoodId = g.Id
inner join Purchases p on pi.PurchaseId = p.Id
where p.UserId = @userId {0}" ;
var queryWhere = GetQueryWhere(userId, param);
return(await _connection.QueryFirstAsync <int>(String.Format(sql, queryWhere.Clause), queryWhere.Params));
}
public override async Task <IEnumerable <GoodDTO> > Get(long userId, GoodsQueryParam param)
{
string sql = @"select distinct g.Name, g.Amount
from PurchaseItems pi
inner join Goods g on pi.GoodId = g.Id
inner join Purchases p on pi.PurchaseId = p.Id
where p.UserId = @userId {0}
order by g.Amount desc" ;
var queryWhere = GetQueryWhere(userId, param);
return(await _connection.QueryAsync <GoodDTO>(String.Format(sql, queryWhere.Clause), queryWhere.Params));
}