// use ordinals to speed up access to DataReader
// links:
// crud definition: https://en.wikipedia.org/wiki/Create,_read,_update_and_delete
// docLink: http://sql2x.org/documentationLink/327451c3-64a8-4de8-b359-76742d634497
// parameters:
// reader: IDataReader from SQLClient
public void Populate(IDataReader reader, VoucherSearchWithRemainingAmountDataOrdinals ordinals)
{
if (!reader.IsDBNull(ordinals.FinancialVoucherId))
{
FinancialVoucherId = reader.GetGuid(ordinals.FinancialVoucherId);
}
if (!reader.IsDBNull(ordinals.FinancialCurrencyId))
{
FinancialCurrencyId = reader.GetGuid(ordinals.FinancialCurrencyId);
}
if (!reader.IsDBNull(ordinals.VoucherNumber))
{
VoucherNumber = reader.GetInt32(ordinals.VoucherNumber);
}
if (!reader.IsDBNull(ordinals.VoucherDescription))
{
VoucherDescription = reader.GetString(ordinals.VoucherDescription);
}
if (!reader.IsDBNull(ordinals.RemainingAmount))
{
RemainingAmount = reader.GetDecimal(ordinals.RemainingAmount);
}
if (!reader.IsDBNull(ordinals.FinancialCurrencyTypeCode))
{
FinancialCurrencyTypeCode = reader.GetString(ordinals.FinancialCurrencyTypeCode);
}
if (!reader.IsDBNull(ordinals.FinancialCurrencyTypeName))
{
FinancialCurrencyTypeName = reader.GetString(ordinals.FinancialCurrencyTypeName);
}
if (!reader.IsDBNull(ordinals.ValidFromDateTime))
{
ValidFromDateTime = reader.GetDateTime(ordinals.ValidFromDateTime);
}
if (!reader.IsDBNull(ordinals.ValidUntilDateTime))
{
ValidUntilDateTime = reader.GetDateTime(ordinals.ValidUntilDateTime);
}
}
/// <summary>voucher search with remaining amount</summary>
/// <cardinality>Many</cardinality>
public List <VoucherSearchWithRemainingAmountData> VoucherSearchWithRemainingAmount(
DateTime voucherValidOnDateTime
)
{
var ret = new List <VoucherSearchWithRemainingAmountData>();
string sql = @"
select top 50
fv.financial_voucher_id
,fv.financial_currency_id
,fv.voucher_number
,fv.voucher_description
,fv.value_amount
- coalesce(
( select sum(fpv.amount)
from financial_payment_voucher as fpv
where fpv.financial_voucher_id = fv.financial_voucher_id)
, 0) as remaining_amount
,fc.financial_currency_type_code
,fc.financial_currency_type_name
,fv.valid_from_date_time
,fv.valid_until_date_time
from financial_voucher as fv
inner join financial_currency as fc on fc.financial_currency_id = fv.financial_currency_id
--realwhere
";
if (voucherValidOnDateTime != DateTime.MinValue)
{
sql += " where @voucher_valid_on_date_time between fv.valid_from_date_time and fv.valid_until_date_time";
}
sql += " order by fv.voucher_number";
using (var conn = new SqlConnection(Conn.ConnectionString)) {
conn.Open();
conn.BeginTransaction(IsolationLevel.ReadUncommitted).Commit();
using (var command = new SqlCommand(sql, conn)) {
if (voucherValidOnDateTime != DateTime.MinValue)
{
command.Parameters.Add("@voucher_valid_on_date_time", SqlDbType.DateTime).Value = voucherValidOnDateTime;
}
Logging log = Logging.PerformanceTimeStart("SolutionNorSolutionPort.DataAccessLayer.FinancialSearch.VoucherSearchWithRemainingAmount");
IDataReader reader = command.ExecuteReader(CommandBehavior.SingleResult);
log.PerformanceTimeStop(sql, command);
var ordinals = new VoucherSearchWithRemainingAmountDataOrdinals(reader);
while (reader.Read())
{
var data = new VoucherSearchWithRemainingAmountData();
data.Populate(reader, ordinals);
ret.Add(data);
}
reader.Close();
}
return(ret);
}
}