public override async Task <IReadOnlyCollection <Bill> > GetMultipleAsync(BillCriteria billCriteria = null)
{
try
{
var bills = new List <Bill>();
var rawQuery = $"select * from [{nameof(Bill)}]";
var wherePart = this.GetWhereConditions(billCriteria);
var sql = rawQuery
+ (wherePart.sql is not null && wherePart.sql != string.Empty ? " where " + wherePart.sql : string.Empty)
+ " " + this.GetSortingSQL();
var cmd = new CommandDefinition(
commandText: sql,
parameters: wherePart.parameters, // clauses.Values.SelectMany(x => (k => k.Item1, k => k.Item2)).ToDictionary(x => x,
transaction: this.GetTransactionIfAvailable());
//var resultsGrid = SqlMapper.QueryMultipleAsync(this._connection, cmd);
//var x = await resultsGrid.Result.ReadAsync();
await using (var reader = await SqlMapper.ExecuteReaderAsync(cnn: this._connection, cmd).ConfigureAwait(false))
{
while (await reader.ReadAsync().ConfigureAwait(false))
{
// TODO: cache column names composition
bills.Add(
new Bill(
id: await reader.GetSafeAsync <long>(nameof(Bill.Id)).ConfigureAwait(false),
supplierId: await reader.GetSafeAsync <long>(nameof(Bill.SupplierId)).ConfigureAwait(false),
releaseDate: await reader.GetSafeAsync <DateTime>(nameof(Bill.ReleaseDate)).ConfigureAwait(false),
dueDate: await reader.GetSafeAsync <DateTime>(nameof(Bill.DueDate)).ConfigureAwait(false),
paymentDate: await reader.GetSafeAsync <DateTime?>(nameof(Bill.PaymentDate)).ConfigureAwait(false),
registrationDate: await reader.GetSafeAsync <DateTime>(nameof(Bill.RegistrationDate)).ConfigureAwait(false),
amount: await reader.GetSafeAsync <double>(nameof(Bill.Amount)).ConfigureAwait(false),
agio: await reader.GetSafeAsync <double>(nameof(Bill.Agio)).ConfigureAwait(false),
additionalCosts: await reader.GetSafeAsync <double>(nameof(Bill.AdditionalCosts)).ConfigureAwait(false),
code: await reader.GetSafeAsync <string>(nameof(Bill.Code)).ConfigureAwait(false),
notes: await reader.GetSafeAsync <string>(nameof(Bill.Notes)).ConfigureAwait(false)));
}
}
return(bills.AsReadOnly());
}
catch (InvalidCastException ex)
{
Debug.WriteLine(ex);
return(Array.Empty <Bill>());
}
catch (Exception ex)
{
Debug.WriteLine(ex);
return(Array.Empty <Bill>());
}
}
public async Task<IReadOnlyCollection<BillDto>> GetAsync(BillCriteria billCriteria)
{
IReadOnlyCollection<Bill> bills = null;
using (var uow = await this._billingUnitOfWorkFactory.CreateAsync().ConfigureAwait(false))
{
bills = await uow.Bills.GetMultipleAsync(billCriteria).ConfigureAwait(false);
}
var billDTOs = bills.Select(s => BillDto.From(s)).ToList().AsReadOnly();
return billDTOs;
}
public Task <DetailedBillDto> GetDetailedBillsAsync(BillCriteria criteria = null)
{
throw new NotImplementedException();
}
private (string sql, IDictionary <string, object> parameters) GetWhereConditions(BillCriteria billCriteria)
{
if (billCriteria == null)
{
return(null, null);
}
//IDictionary<string, IEnumerable<Tuple<string, object>>> clauses = new Dictionary<string, IEnumerable<Tuple<string, object>>>();
IList <string> whereSqlClauses = new List <string>();
var whereParamsDict = new Dictionary <string, object>();
if (billCriteria.SupplierId.HasValue)
{
const string paramKey = "@" /*+ nameof(billCriteria) + "." */ + nameof(billCriteria.SupplierId);
whereSqlClauses.Add(nameof(Bill.SupplierId) + " = " + paramKey);
whereParamsDict.Add(paramKey, billCriteria.SupplierId);
}
if (billCriteria.IsPaid.HasValue)
{
whereSqlClauses.Add(nameof(Bill.PaymentDate) + (billCriteria.IsPaid.Value ? " is not " : " is ") + "NULL");
}
if (billCriteria.ReleaseDateRange != null)
{
if (billCriteria.ReleaseDateRange.Start.HasValue && billCriteria.ReleaseDateRange.End.HasValue)
{
const string startParamKey = "@" + nameof(billCriteria.ReleaseDateRange) + nameof(billCriteria.ReleaseDateRange.Start);
const string endParamKey = "@" + nameof(billCriteria.ReleaseDateRange) + nameof(billCriteria.ReleaseDateRange.End);
if (billCriteria.ReleaseDateRange.Start.Value == billCriteria.ReleaseDateRange.End.Value)
{
whereSqlClauses.Add(nameof(Bill.ReleaseDate) + " = " + startParamKey);
}
else
{
whereSqlClauses.Add(nameof(Bill.ReleaseDate) + " between " + startParamKey + " and " + endParamKey);
}
whereParamsDict.Add(startParamKey, billCriteria.ReleaseDateRange.Start);
whereParamsDict.Add(endParamKey, billCriteria.ReleaseDateRange.End);
}
else if (billCriteria.ReleaseDateRange.Start.HasValue)
{
const string startParamKey = "@" + nameof(billCriteria.ReleaseDateRange) + nameof(billCriteria.ReleaseDateRange.Start);
whereSqlClauses.Add(nameof(Bill.ReleaseDate) + " >= " + startParamKey);
whereParamsDict.Add(startParamKey, billCriteria.ReleaseDateRange.Start);
}
else
{
const string endParamKey = "@" + nameof(billCriteria.ReleaseDateRange) + nameof(billCriteria.ReleaseDateRange.End);
whereSqlClauses.Add(nameof(Bill.ReleaseDate) + " <= " + endParamKey);
whereParamsDict.Add(endParamKey, billCriteria.ReleaseDateRange.End);
}
}
// TODO: use stringbuilder
return(string.Join(" and ", whereSqlClauses), whereParamsDict);
}
public override Task <Bill> GetSingleAsync(BillCriteria billCriteria)
{
throw new NotImplementedException();
}
public abstract Task <IReadOnlyCollection <Bill> > GetMultipleAsync(BillCriteria billCriteria = null);
public abstract Task <Bill> GetSingleAsync(BillCriteria billCriteria);