// 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, FinancialServiceDetailsForBookingDataOrdinals ordinals)
{
if (!reader.IsDBNull(ordinals.ServiceTypeName))
{
ServiceTypeName = reader.GetString(ordinals.ServiceTypeName);
}
if (!reader.IsDBNull(ordinals.ServiceDescription))
{
ServiceDescription = reader.GetString(ordinals.ServiceDescription);
}
if (!reader.IsDBNull(ordinals.ServiceRequestStatusName))
{
ServiceRequestStatusName = reader.GetString(ordinals.ServiceRequestStatusName);
}
if (!reader.IsDBNull(ordinals.ServiceAmount))
{
ServiceAmount = reader.GetDecimal(ordinals.ServiceAmount);
}
if (!reader.IsDBNull(ordinals.ServiceCurrencyTypeName))
{
ServiceCurrencyTypeName = reader.GetString(ordinals.ServiceCurrencyTypeName);
}
if (!reader.IsDBNull(ordinals.BookingServiceRequestId))
{
BookingServiceRequestId = reader.GetGuid(ordinals.BookingServiceRequestId);
}
if (!reader.IsDBNull(ordinals.ServiceSpecialServiceRequestId))
{
ServiceSpecialServiceRequestId = reader.GetGuid(ordinals.ServiceSpecialServiceRequestId);
}
if (!reader.IsDBNull(ordinals.ServiceHotelId))
{
ServiceHotelId = reader.GetGuid(ordinals.ServiceHotelId);
}
if (!reader.IsDBNull(ordinals.ServiceCarRentalId))
{
ServiceCarRentalId = reader.GetGuid(ordinals.ServiceCarRentalId);
}
}
/// <summary>Financial service details for booking</summary>
/// <cardinality>Many</cardinality>
public List <FinancialServiceDetailsForBookingData> FinancialServiceDetailsForBooking(
Guid bookingId
)
{
var ret = new List <FinancialServiceDetailsForBookingData>();
string sql = @"
select
'ssr' as service_type_name
,'(' + sssr.service_special_service_request_code
+ ') ' + sssr.service_special_service_request_name as service_description
,srsr.service_request_status_name
,sssr.amount as service_amount
,fc.financial_currency_type_name as service_currency_type_name
,booking_service_request_id
,bsr.service_special_service_request_id
,bsr.service_hotel_id
,bsr.service_car_rental_id
from booking_service_request as bsr
inner join service_request_status_ref as srsr on srsr.service_request_status_rcd = bsr.service_request_status_rcd
inner join service_special_service_request as sssr on sssr.service_special_service_request_id = bsr.service_special_service_request_id
left join financial_currency as fc on fc.financial_currency_id = sssr.financial_currency_id
where bsr.booking_id = @booking_id
union
select
'hotel' as service_type_name
,sh.hotel_name as service_description
,srsr.service_request_status_name
,sh.day_price_amount as service_amount
,fc.financial_currency_type_name as service_currency_type_name
,booking_service_request_id
,bsr.service_special_service_request_id
,bsr.service_hotel_id
,bsr.service_car_rental_id
from booking_service_request as bsr
inner join service_request_status_ref as srsr on srsr.service_request_status_rcd = bsr.service_request_status_rcd
inner join service_hotel as sh on sh.service_hotel_id = bsr.service_hotel_id
inner join financial_currency as fc on fc.financial_currency_id = sh.financial_currency_id
where bsr.booking_id = @booking_id
union
select
'ferry' as service_type_name
,fe.ferry_name as service_description
,srsr.service_request_status_name
,sh.amount as service_amount
,fc.financial_currency_type_name as service_currency_type_name
,booking_service_request_id
,bsr.service_special_service_request_id
,bsr.service_ferry_id
,bsr.service_car_rental_id
from booking_service_request as bsr
inner join service_request_status_ref as srsr on srsr.service_request_status_rcd = bsr.service_request_status_rcd
inner join service_ferry as sh on sh.service_ferry_id = bsr.service_ferry_id
inner join ferry as fe on fe.ferry_id = sh.ferry_id
inner join financial_currency as fc on fc.financial_currency_id = sh.financial_currency_id
where bsr.booking_id = @booking_id
union
select
'car' as service_type_name
,scr.car_name as service_description
,srsr.service_request_status_name
,scr.day_price_amount as service_amount
,fc.financial_currency_type_name as service_currency_type_name
,booking_service_request_id
,bsr.service_special_service_request_id
,bsr.service_hotel_id
,bsr.service_car_rental_id
from booking_service_request as bsr
inner join service_request_status_ref as srsr on srsr.service_request_status_rcd = bsr.service_request_status_rcd
inner join service_car_rental as scr on scr.service_car_rental_id = bsr.service_car_rental_id
inner join financial_currency as fc on fc.financial_currency_id = scr.financial_currency_id
where bsr.booking_id = @booking_id
--order by bsr.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("@booking_id", SqlDbType.UniqueIdentifier).Value = bookingId;
Logging log = Logging.PerformanceTimeStart("SolutionNorSolutionPort.DataAccessLayer.FinancialSearch.FinancialServiceDetailsForBooking");
IDataReader reader = command.ExecuteReader(CommandBehavior.SingleResult);
log.PerformanceTimeStop(sql, command);
var ordinals = new FinancialServiceDetailsForBookingDataOrdinals(reader);
while (reader.Read())
{
var data = new FinancialServiceDetailsForBookingData();
data.Populate(reader, ordinals);
ret.Add(data);
}
reader.Close();
}
return(ret);
}
}
