public List<SearchItem> GetAirports()
{
var airportsList = new List<SearchItem>();
try
{
var db = new MySqlDatabase(DbConfiguration.DatabaseRead);
DataSet dataSet = db.ExecuteQuery(CommandBuilder.BuildGetAirportsCommand(db.Connection));
foreach (DataRow row in dataSet.Tables[0].Rows)
{
var item = new SearchItem
{
Name = row["AirportName"] as string,
Code = row["AirportCode"] as string,
CityName = row["CityName"] as string,
CountryCode = row["CountryCode"] as string
};
airportsList.Add(item);
}
}
catch (Exception ex)
{
DBExceptionLogger.LogException(ex, Source, "GetAirports", Severity.Critical);
}
return airportsList;
}
public List<Log> GetLogs(int? id, DateTime? timestampFrom, DateTime? timestampTo, string machineName,
string sessionId, string serviceName, string title, float timeTaken, string status,
float? timeMin,
float? timeMax, string searchText, int pageIndex, int pageSize, out int totalRowCount)
{
totalRowCount = 0;
try
{
var db = new MySqlDatabase(DbConfiguration.LogDB);
DataSet dataSet =
db.ExecuteQuery(CommandBuilder.BuildGetLogs(id, timestampFrom, timestampTo, machineName,
sessionId, serviceName, title, timeTaken, status,
timeMin,
timeMax, searchText, pageIndex, pageSize, db.Connection),
"outtotalRowCount", out totalRowCount);
return ParseLogs(dataSet);
}
catch (Exception ex)
{
var exLog = new ExceptionLog(ex, "LoggingDataProvider", "GetLogs", Severity.Normal);
SaveException(exLog);
}
return new List<Log>();
}
public List<ExceptionLog> GetExceptions(int? exceptionId, string machineName, string source, string targetSite,
string exceptionType, string appDomainName, string message,
DateTime? timestampFrom, DateTime? timestampTo,
int pageIndex, int pageSize, string searchText, string sessionId,
out int totalRowCount)
{
totalRowCount = 0;
try
{
var db = new MySqlDatabase(DbConfiguration.LogDB);
DataSet dataSet =
db.ExecuteQuery(CommandBuilder.BuildGetExceptions(exceptionId, machineName, source, targetSite,
exceptionType, appDomainName, message,
timestampFrom, timestampTo,
pageIndex, pageSize, searchText, sessionId,
db.Connection), "outtotalRowCount",
out totalRowCount);
return ParseExceptionLogs(dataSet);
}
catch (Exception ex)
{
var exLog = new ExceptionLog(ex, "LoggingDataProvider", "GetLogs", Severity.Normal);
SaveException(exLog);
}
return new List<ExceptionLog>();
}
public void SaveNewCode(string key, string value, string type)
{
try
{
var db = new MySqlDatabase(DbConfiguration.DatabaseWrite);
db.ExecuteNonQuery(CommandBuilder.BuildSaveVerificationCodeCommand(key, value, type, db.Connection));
}
catch (Exception ex)
{
DBExceptionLogger.LogException(ex, Source, "SaveNewCode", Severity.Critical);
}
}
public bool Remove(string key, string type)
{
try
{
var db = new MySqlDatabase(DbConfiguration.DatabaseWrite);
db.ExecuteNonQuery(CommandBuilder.BuildRemoveVerificationCodeCommand(key, type, db.Connection));
return true;
}
catch (Exception ex)
{
DBExceptionLogger.LogException(ex, Source, "Remove", Severity.Critical);
}
return false;
}
public bool CreateBooking(Booking booking)
{
//Steps:
//1. Create Booking Id
//2. Create Air Pnr
//3. Save Flight
//4. Save FlightFare
//5. Save Passengers
//6. Save PaymentInfo
var result = true;
try
{
int bookingId;
var db = new MySqlDatabase(DbConfiguration.ReservationDB);
var cmd = CommandBuilder.BuildCreateBookingCommand(booking.BookingType, booking.AccountId, booking.TravelDate,
booking.TravelEndDate,
booking.PassengerCount, booking.BookingStatus,
db.Connection);
db.ExecuteNonQuery(cmd, "outBookingId", out bookingId);
if (bookingId != 0)
{
booking.BookingId = bookingId.ToString();
if (booking.FlightBookings != null)
result &= booking.FlightBookings.TrueForAll(x => SaveAirPnr(bookingId, x));
if (result && booking.Charges != null)
result &= booking.Charges.TrueForAll(x => SaveCharge(bookingId, x));
return result;
}
}
catch (Exception ex)
{
DBExceptionLogger.LogException(ex, Source, "CreateBooking", Severity.Critical);
return false;
}
return false;
}
public string GetRequestByLogId(int logId)
{
try
{
var db = new MySqlDatabase(DbConfiguration.LogDB);
DataSet dataSet = db.ExecuteQuery(CommandBuilder.BuildGetRequestByLogId(logId, db.Connection));
return dataSet != null && dataSet.Tables.Count == 1 && dataSet.Tables[0].Rows != null &&
dataSet.Tables[0].Rows.Count == 1
? dataSet.Tables[0].Rows[0][0].ToString()
: "No Request Found";
}
catch (Exception ex)
{
var exLog = new ExceptionLog(ex, "LoggingDataProvider", "GetRequestByLogId", Severity.Normal);
SaveException(exLog);
}
return "No Request Found";
}
private static bool SaveAirPnr(int bookingId, AirPnr airPnr)
{
int flightId;
var db = new MySqlDatabase(DbConfiguration.ReservationDB);
var cmd = CommandBuilder.BuildSaveAirPnrCommand(bookingId, airPnr.Pnr, airPnr.BookingStatus, airPnr.Mode,
airPnr.Provider, airPnr.RequestId, airPnr.TicketNumbers, airPnr.JourneyType, db.Connection);
db.ExecuteNonQuery(cmd, "outFlightId", out flightId);
var result = flightId != 0;
if (result)
{
airPnr.FlightId = flightId.ToString();
result &= airPnr.Flights.TrueForAll(x => SaveFlight(flightId, x));
result &= airPnr.AirFare.PassengerFares.TrueForAll(x => SavePassengerFare(flightId, x));
result &= airPnr.Passengers.TrueForAll(x => SavePassenger(flightId, x));
}
return result;
}
public bool IsValid(string key, string value, string type)
{
try
{
var db = new MySqlDatabase(DbConfiguration.DatabaseRead);
DataSet dataSet =
db.ExecuteQuery(CommandBuilder.BuildVerifyVerificationCodeCommand(key, value, type, db.Connection));
if (dataSet != null && dataSet.Tables != null &&
dataSet.Tables.Count > 0 && dataSet.Tables[0].Rows != null && dataSet.Tables[0].Rows.Count > 0)
{
if (!Convert.IsDBNull(dataSet.Tables[0].Rows[0]["outresult"]))
{
return Convert.ToBoolean(dataSet.Tables[0].Rows[0]["outresult"]);
}
}
}
catch (Exception ex)
{
DBExceptionLogger.LogException(ex, Source, "IsValid", Severity.Critical);
}
return false;
}
public void SaveLog(Log log)
{
try
{
var db = new MySqlDatabase(DbConfiguration.LogDB);
db.ExecuteNonQuery(CommandBuilder.BuildSaveLog(log, db.Connection));
}
catch (Exception ex)
{
var exLog = new ExceptionLog(ex, "LoggingDataProvider", "SaveLog", Severity.Critical);
SaveException(exLog);
}
}
public void SaveException(ExceptionLog exception)
{
try
{
var db = new MySqlDatabase(DbConfiguration.LogDB);
db.ExecuteNonQuery(CommandBuilder.BuildSaveException(exception, db.Connection));
}
catch
{
}
;
}
private static bool SaveCharge(int bookingId, Charge charge)
{
int chargeId;
var db = new MySqlDatabase(DbConfiguration.ReservationDB);
var cmd = CommandBuilder.BuildSaveChargeCommand(bookingId, charge.Amount, charge.Currency,
charge.CustomerPaymentId,
charge.ErrorMessage, charge.ExternalReferenceId,
charge.InternalReferenceId, charge.IsRefund,
charge.IsSuccess, charge.Provider, charge.Mode,
charge.Type, db.Connection);
db.ExecuteNonQuery(cmd, "outChargeId", out chargeId);
charge.ChargeId = chargeId.ToString();
return chargeId != 0;
}
private static bool SavePassengerFare(int flightId, PassengerFare passengerFare)
{
var db = new MySqlDatabase(DbConfiguration.ReservationDB);
var cmd = CommandBuilder.BuildInsertPassengerFareCommand(flightId, passengerFare.PassengerType,
passengerFare.Quantity,
passengerFare.ClassOfService,
passengerFare.FareBasisCode, passengerFare.Currency,
passengerFare.BaseFare,
passengerFare.TotalTax, passengerFare.TotalFees,
passengerFare.TotalMarkup, passengerFare.TotalFare,
db.Connection);
db.ExecuteNonQuery(cmd);
var components = new List<FareComponent>();
components.AddRange(passengerFare.Fees);
components.AddRange(passengerFare.Markups);
components.AddRange(passengerFare.Taxes);
return components.TrueForAll(x => SaveFareComponent(flightId, passengerFare.PassengerType, x));
}
private static bool SavePassenger(int flightId, Passenger passenger)
{
var db = new MySqlDatabase(DbConfiguration.ReservationDB);
var cmd = CommandBuilder.BuildInsertFlightPassengerCommand(flightId, passenger.FirstName, passenger.LastName, passenger.Title,
passenger.DateOfBirth, passenger.Gender,
db.Connection);
int passengerId;
db.ExecuteNonQuery(cmd, "outPassengerId", out passengerId);
passenger.PassengerId = passengerId.ToString();
return true;
}
private static bool SaveFlight(int flightId, Flight flight)
{
var db = new MySqlDatabase(DbConfiguration.ReservationDB);
var cmd = CommandBuilder.BuildInsertFlightCommand(flightId, flight.LegNumber, flight.Index,
flight.FlightNumber,
flight.MarketingAirlineCode, flight.AirlineCode,
flight.FromAirportCode, flight.ToAirportCode,
flight.DepartureDateTime, flight.ArrivalDateTime,
flight.DurationInMinutes, flight.Stops,
flight.CabinClass, flight.LayoverAirportCode,
flight.LayoverMinutes, flight.AircraftCode, flight.AircraftName,
db.Connection);
db.ExecuteNonQuery(cmd);
return true;
}
private static bool SaveFareComponent(int flightId, PassengerType passengerType, FareComponent fareComponent)
{
var db = new MySqlDatabase(DbConfiguration.ReservationDB);
var cmd = CommandBuilder.BuildInsertFareComponentCommand(flightId, passengerType, fareComponent.Type,
fareComponent.Amount, fareComponent.Name,
db.Connection);
db.ExecuteNonQuery(cmd);
return true;
}