public BookingDetails GetBookingByConfirmationNo(long hostId, string confirmationNo)
{
using (var context = new AnnoDBContext())
{
BookingDetails result = null;
//TODO: Change to Linq query
string sql = @"SELECT b.confirmation_number, e.title as event_title, t.title as tier_title, e.ref_id as event_ref, t.ref_id as tier_ref, u.ref_id as customer_ref,
tic.ticket_id, tic.ticket_number, tic.status as ticket_status, tic.paid_price, tic.address as ticket_address
FROM customer_booking b
INNER JOIN customer_ticket tic ON (tic.customer_id = b.customer_id AND tic.event_id = b.event_id)
INNER JOIN events e ON (e.event_id = b.event_id)
INNER JOIN events_tier t ON (t.tier_id = tic.tier_id)
INNER JOIN customer u ON (u.customer_id = tic.customer_id)
WHERE b.record_status='Live'
AND tic.record_status='Live'
AND e.record_status='Live'
AND t.record_status='Live'
AND e.host_id=@hostId
AND b.confirmation_number=@confirmationNo";
sql = sql
.Replace("@hostId ", DataUtility.ToMySqlParam(hostId))
.Replace("@confirmationNo", DataUtility.ToMySqlParam(confirmationNo));
DataTable dt = context.DataTable(sql);
if (DataUtility.HasRecord(dt))
{
DataRow firstRow = dt.Rows[0];
result = new BookingDetails();
result.UserReferenceId = ConvertUtility.ToString(firstRow["customer_ref"]);
result.EventReferenceId = ConvertUtility.ToString(firstRow["event_ref"]);
result.ConfirmationNumber = ConvertUtility.ToString(firstRow["confirmation_number"]);
result.Tickets = new List <TicketInfo>();
foreach (DataRow row in dt.Rows)
{
TicketInfo ticket = new TicketInfo();
ticket.TicketId = ConvertUtility.ToInt64(row["ticket_id"]);
ticket.CustomerReferenceId = ConvertUtility.ToString(row["customer_ref"]);
ticket.EventReferenceId = ConvertUtility.ToString(row["event_ref"]);
ticket.TierReferenceId = ConvertUtility.ToString(row["tier_ref"]);
ticket.TicketNo = ConvertUtility.ToString(row["ticket_number"]);
ticket.Status = ConvertUtility.ToString(row["ticket_status"]);
ticket.PaidPrice = ConvertUtility.ToInt64(row["paid_price"]);
ticket.TicketAddress = ConvertUtility.ToString(row["ticket_address"]);
ticket.EventTitle = ConvertUtility.ToString(row["event_title"]);
ticket.TierTitle = ConvertUtility.ToString(row["tier_title"]);
ticket.BookingConfirmationNo = ConvertUtility.ToString(row["confirmation_number"]);
result.Tickets.Add(ticket);
}
}
return(result);
}
}
private object TryConvert(object value, string toType, ref bool succes)
{
object retval = null;
if (toType == typeof(Object).Name)
{
succes = true;
return((object)value);
}
if (toType == typeof(Guid?).Name)
{
succes = true;
return(ConvertUtility.ToGuidNullable(value));
}
if (toType == typeof(Guid).Name)
{
succes = true;
return(ConvertUtility.ToGuid(value, Guid.Empty));
}
if (toType == typeof(Byte?).Name)
{
succes = true;
return(ConvertUtility.ToByteNullable(value));
}
if (toType == typeof(Byte).Name)
{
succes = true;
return(ConvertUtility.ToByte(value, 0));
}
if (toType == typeof(int?).Name)
{
succes = true;
return(ConvertUtility.ToInt32Nullable(value));
}
if (toType == typeof(int).Name)
{
succes = true;
return(ConvertUtility.ToInt32(value, 0));
}
if (toType == typeof(decimal?).Name)
{
succes = true;
return(ConvertUtility.ToDecimalNullable(value));
}
if (toType == typeof(decimal).Name)
{
succes = true;
return(ConvertUtility.ToDecimal(value, 0));
}
if (toType == typeof(Int16?).Name)
{
succes = true;
return(ConvertUtility.ToInt16Nullable(value));
}
if (toType == typeof(Int16).Name)
{
succes = true;
return(ConvertUtility.ToInt16(value, 0));
}
if (toType == typeof(Int32?).Name)
{
succes = true;
return(ConvertUtility.ToInt32Nullable(value));
}
if (toType == typeof(Int32).Name)
{
succes = true;
return(ConvertUtility.ToInt32(value, 0));
}
if (toType == typeof(Int64?).Name)
{
succes = true;
return(ConvertUtility.ToInt64Nullable(value));
}
if (toType == typeof(Int64).Name)
{
succes = true;
return(ConvertUtility.ToInt64(value, 0));
}
if (toType == typeof(float?).Name)
{
succes = true;
return(ConvertUtility.ToSingleNullable(value));
}
if (toType == typeof(float).Name)
{
succes = true;
return(ConvertUtility.ToSingle(value, 0));
}
if (toType == typeof(Single?).Name)
{
succes = true;
return(ConvertUtility.ToSingleNullable(value));
}
if (toType == typeof(Single).Name)
{
succes = true;
return(ConvertUtility.ToSingle(value, 0));
}
if (toType == typeof(double?).Name)
{
succes = true;
return(ConvertUtility.ToDoubleNullable(value));
}
if (toType == typeof(double).Name)
{
succes = true;
return(ConvertUtility.ToDouble(value, 0));
}
if (toType == typeof(Double?).Name)
{
succes = true;
return(ConvertUtility.ToDoubleNullable(value));
}
if (toType == typeof(Double).Name)
{
succes = true;
return(ConvertUtility.ToDouble(value, 0));
}
if (toType == typeof(bool?).Name)
{
succes = true;
return(ConvertUtility.ToBooleanNullable(value));
}
if (toType == typeof(bool).Name)
{
succes = true;
return(ConvertUtility.ToBoolean(value, false));
}
if (toType == typeof(DateTime).Name)
{
DateTime DateTimeValue = DateTime.Now;
succes = DateTime.TryParse(ConvertUtility.HandleNull(value, string.Empty).ToString(), out DateTimeValue);
retval = DateTimeValue;
}
if (toType == typeof(DateTime?).Name)
{
DateTime DateTimeValue = DateTime.Now;
succes = DateTime.TryParse(ConvertUtility.HandleNull(value, string.Empty).ToString(), out DateTimeValue);
if (!succes)
{
retval = null;
}
retval = DateTimeValue;
}
return(retval);
}