// 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, VoucherTransactionsDataOrdinals ordinals)
{
if (!reader.IsDBNull(ordinals.VoucherNumber))
{
VoucherNumber = reader.GetInt32(ordinals.VoucherNumber);
}
if (!reader.IsDBNull(ordinals.VoucherDescription))
{
VoucherDescription = reader.GetString(ordinals.VoucherDescription);
}
if (!reader.IsDBNull(ordinals.DateTime))
{
DateTime = reader.GetDateTime(ordinals.DateTime);
}
if (!reader.IsDBNull(ordinals.PaidCurrencyAmount))
{
PaidCurrencyAmount = reader.GetDecimal(ordinals.PaidCurrencyAmount);
}
if (!reader.IsDBNull(ordinals.PaidCurrencyType))
{
PaidCurrencyType = reader.GetString(ordinals.PaidCurrencyType);
}
if (!reader.IsDBNull(ordinals.ExchangeRate))
{
ExchangeRate = reader.GetDecimal(ordinals.ExchangeRate);
}
if (!reader.IsDBNull(ordinals.BookingCurrencyAmount))
{
BookingCurrencyAmount = reader.GetDecimal(ordinals.BookingCurrencyAmount);
}
if (!reader.IsDBNull(ordinals.BookingCurrencyType))
{
BookingCurrencyType = reader.GetString(ordinals.BookingCurrencyType);
}
if (!reader.IsDBNull(ordinals.FinancialCostcentreCode))
{
FinancialCostcentreCode = reader.GetString(ordinals.FinancialCostcentreCode);
}
if (!reader.IsDBNull(ordinals.FinancialCostcentreName))
{
FinancialCostcentreName = reader.GetString(ordinals.FinancialCostcentreName);
}
if (!reader.IsDBNull(ordinals.DefaultUserCode))
{
DefaultUserCode = reader.GetString(ordinals.DefaultUserCode);
}
if (!reader.IsDBNull(ordinals.DefaultUserName))
{
DefaultUserName = reader.GetString(ordinals.DefaultUserName);
}
if (!reader.IsDBNull(ordinals.FinancialPaymentId))
{
FinancialPaymentId = reader.GetGuid(ordinals.FinancialPaymentId);
}
if (!reader.IsDBNull(ordinals.BookingId))
{
BookingId = reader.GetGuid(ordinals.BookingId);
}
}
/// <summary>voucher usage details</summary>
/// <cardinality>Many</cardinality>
public List <VoucherTransactionsData> VoucherTransactions(
Guid financialVoucherId
)
{
var ret = new List <VoucherTransactionsData>();
string sql = @"
select top 50
fv.voucher_number
,fv.voucher_description
,fbt.date_time
,fpv.amount as paid_currency_amount
,fvc.financial_currency_type_rcd as paid_currency_type
,fc.equals_amount as exchange_rate
,fbt.amount as booking_currency_amount
,fc.financial_currency_type_rcd as booking_currency_type
,fcc.financial_costcentre_code
,fcc.financial_costcentre_name
,du.default_user_code
,du.default_user_name
,fbt.financial_payment_id
,fbt.booking_id
from financial_booking_transaction as fbt
inner join financial_currency as fc on fc.financial_currency_id = fbt.financial_currency_id
inner join financial_payment as fp on fp.financial_payment_id = fbt.financial_payment_id
inner join financial_payment_type_ref as fptr on fptr.financial_payment_type_rcd = fp.financial_payment_type_rcd
inner join financial_payment_voucher as fpv on fpv.financial_payment_voucher_id = fp.financial_payment_voucher_id
inner join financial_voucher as fv on fv.financial_voucher_id = fpv.financial_voucher_id
inner join financial_currency as fvc on fvc.financial_currency_id = fpv.financial_currency_id
inner join financial_costcentre as fcc on fcc.financial_costcentre_id = fbt.financial_costcentre_id
inner join default_user as du on du.default_user_id = fbt.user_id
where fv.financial_voucher_id = @financial_voucher_id
order by fbt.date_time
";
using (var conn = new SqlConnection(Conn.ConnectionString)) {
conn.Open();
conn.BeginTransaction(IsolationLevel.ReadUncommitted).Commit();
using (var command = new SqlCommand(sql, conn)) {
command.Parameters.Add("@financial_voucher_id", SqlDbType.UniqueIdentifier).Value = financialVoucherId;
Logging log = Logging.PerformanceTimeStart("SolutionNorSolutionPort.DataAccessLayer.FinancialSearch.VoucherTransactions");
IDataReader reader = command.ExecuteReader(CommandBehavior.SingleResult);
log.PerformanceTimeStop(sql, command);
var ordinals = new VoucherTransactionsDataOrdinals(reader);
while (reader.Read())
{
var data = new VoucherTransactionsData();
data.Populate(reader, ordinals);
ret.Add(data);
}
reader.Close();
}
return(ret);
}
}
