public Account CreateAccount(string email, string firstName, string lastName, string mobile, string hashPwd,
bool isEmailVerified = false)
{
try
{
int accountId;
var db = new MySqlDatabase(DbConfiguration.DatabaseWrite);
MySqlCommand cmd = CommandBuilder.BuildCreateAccountCommand(db.Connection, email, firstName, lastName,
mobile ?? "", hashPwd, isEmailVerified);
db.ExecuteNonQuery(cmd, "outaccountid", out accountId);
if (accountId != 0)
return new Account
{
AccountId = accountId.ToString(),
Email = email,
FirstName = firstName,
LastName = lastName,
Mobile = mobile,
IsEmailActivated = false,
IsMobileVerified = false,
IsEnabled = true
};
}
catch (Exception ex)
{
DBExceptionLogger.LogException(ex, Source, "CreateAccount", Severity.Critical);
return null;
}
return null;
}
public Token GenerateToken(string email, string name, string mobile, int passengers, decimal payableAmount, string refId, string reqId)
{
try
{
int tokenId;
var db = new MySqlDatabase(DbConfiguration.TokenDB);
var createDate = DateTime.Now;
var expiryDate = createDate.AddHours(48);
MySqlCommand cmd = CommandBuilder.BuildGenerateTokenCommand(db.Connection, email, name, mobile, passengers, payableAmount, createDate, expiryDate, refId, reqId);
db.ExecuteNonQuery(cmd, "outTokenId", out tokenId);
if (tokenId != 0)
return new Token
{
Id = tokenId.ToString(),
EmailId = email,
Name = name,
Mobile = mobile,
PassengersCount = passengers,
PayableAmount = payableAmount,
TokenCreationDate = createDate,
TokenExpirationDate = expiryDate,
PaymentReferenceId = refId,
RequestId = reqId
};
}
catch (Exception ex)
{
DBExceptionLogger.LogException(ex, Source, "GenerateToken", Severity.Critical);
return null;
}
return null;
}
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 void UpdateVoucherCount(string voucherCode)
{
try
{
var db = new MySqlDatabase(DbConfiguration.DatabaseRead);
MySqlCommand command = CommandBuilder.BuildUpdateVoucherCommand(voucherCode, db.Connection);
db.ExecuteNonQuery(command);
}
catch (Exception ex)
{
DBExceptionLogger.LogException(ex, Source, "GetPaymentProviderDetails", Severity.Critical);
}
}
public bool ChangeMobile(string accountId, string mobile)
{
try
{
var db = new MySqlDatabase(DbConfiguration.DatabaseWrite);
int result;
db.ExecuteNonQuery(CommandBuilder.BuildChangeMobileCommand(accountId, mobile, db.Connection), "result",
out result);
return result == 1;
}
catch (Exception ex)
{
DBExceptionLogger.LogException(ex, Source, "ChangeMobile", 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;
}
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;
}
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;
}
public bool SetPassword(string accountId, string hashPwd)
{
try
{
var db = new MySqlDatabase(DbConfiguration.DatabaseWrite);
db.ExecuteNonQuery(CommandBuilder.BuildSetPasswordCommand(accountId, hashPwd, db.Connection));
return true;
}
catch (Exception ex)
{
DBExceptionLogger.LogException(ex, Source, "SetPassword", Severity.Critical);
}
return false;
}
public void SaveException(ExceptionLog exception)
{
try
{
var db = new MySqlDatabase(DbConfiguration.LogDB);
db.ExecuteNonQuery(CommandBuilder.BuildSaveException(exception, db.Connection));
}
catch
{
}
;
}
private int GetNextWaitlistPosition(string fromAirport, string toAirport, DateTime dateTime)
{
Lock.EnterWriteLock();
int wlposition;
try
{
var db = new MySqlDatabase(DbConfiguration.DatabaseWrite);
MySqlCommand cmd = CommandBuilder.BuildSaveWaitlistPositionCommand(db.Connection, fromAirport, toAirport,
dateTime);
db.ExecuteNonQuery(cmd, "outWLPosition", out wlposition);
}
finally
{
Lock.ExitWriteLock();
}
return wlposition;
}
public void CreateSocialAccount(string accountId, string socialAccountId, string socialAccountType)
{
try
{
var db = new MySqlDatabase(DbConfiguration.DatabaseWrite);
MySqlCommand cmd = CommandBuilder.BuildCreateSocialAccountCommand(db.Connection, accountId,
socialAccountId, socialAccountType);
db.ExecuteNonQuery(cmd);
}
catch (Exception ex)
{
DBExceptionLogger.LogException(ex, Source, "CreateSocialAccount", Severity.Critical);
}
}
public Traveller SaveOrUpdateTraveller(Traveller traveller, out string errorMessage)
{
try
{
errorMessage = string.Empty;
var db = new MySqlDatabase(DbConfiguration.DatabaseWrite);
int travellerId;
db.ExecuteNonQuery(CommandBuilder.BuildSaveOrUpdateTravellerCommand(db.Connection, traveller), "outTravellerId",
out travellerId);
traveller.TravellerId = travellerId;
return traveller;
}
catch (Exception ex)
{
errorMessage = ex.Message;
DBExceptionLogger.LogException(ex, Source, "SaveOrUpdateTraveller", Severity.Critical);
return null;
}
}
public Account UpdatePersonalInfo(Account account, out string errorMessage)
{
errorMessage = string.Empty;
try
{
int addressId = 0;
var db = new MySqlDatabase(DbConfiguration.DatabaseWrite);
if (account.BillingAddress != null)
{
MySqlCommand cmd = CommandBuilder.BuildUpdateAddressCommand(db.Connection, account.BillingAddress);
db.ExecuteNonQuery(cmd, "outAddressid", out addressId);
}
MySqlCommand cmdUpdatePersonalInfo = CommandBuilder.BuildspUpdatePersonalInfoCommand(db.Connection,
account.AccountId,
account.FirstName,
account.LastName,
addressId);
db.ExecuteNonQuery(cmdUpdatePersonalInfo);
return account;
}
catch (Exception ex)
{
errorMessage = ex.Message;
DBExceptionLogger.LogException(ex, Source, "UpdateAccount", Severity.Critical);
return null;
}
}
public bool UpdatePassword(string accountId, string hashCurrPwd, string hashNewPwd)
{
try
{
var db = new MySqlDatabase(DbConfiguration.DatabaseWrite);
int result;
db.ExecuteNonQuery(
CommandBuilder.BuildUpdatePasswordCommand(accountId, hashCurrPwd, hashNewPwd, db.Connection),
"result", out result);
return result == 1;
}
catch (Exception ex)
{
DBExceptionLogger.LogException(ex, Source, "UpdatePassword", Severity.Critical);
return false;
}
}
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;
}
public bool DeleteTraveller(string travellerId, out string errorMessage)
{
try
{
errorMessage = string.Empty;
var db = new MySqlDatabase(DbConfiguration.DatabaseWrite);
int result;
db.ExecuteNonQuery(CommandBuilder.BuildDeleteTravellerCommand(db.Connection, travellerId));
return true;
}
catch (Exception ex)
{
errorMessage = ex.Message;
DBExceptionLogger.LogException(ex, Source, "ChangeEmail", 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 SetMobileVerified(string email)
{
try
{
var db = new MySqlDatabase(DbConfiguration.DatabaseWrite);
MySqlCommand cmd = CommandBuilder.BuildSetMobileVerifiedCommand(db.Connection, email);
db.ExecuteNonQuery(cmd);
}
catch (Exception ex)
{
DBExceptionLogger.LogException(ex, Source, "SetMobileVerified", Severity.Critical);
}
}
private void SavePassengers(IEnumerable<Passenger> passengers, string requestId)
{
try
{
var db = new MySqlDatabase(DbConfiguration.DatabaseWrite);
foreach (Passenger passenger in passengers)
{
db.ExecuteNonQuery(CommandBuilder.BuildSavePassenger(passenger, requestId, db.Connection));
}
}
catch (Exception ex)
{
DBExceptionLogger.LogException(ex, Source, "SavePassengers", Severity.Critical);
throw ex;
}
}
public bool DeleteAccount(string accountId)
{
try
{
var db = new MySqlDatabase(DbConfiguration.DatabaseWrite);
MySqlCommand cmd = CommandBuilder.BuildDeleteAccountCommand(db.Connection, accountId);
db.ExecuteNonQuery(cmd);
return true;
}
catch (Exception ex)
{
DBExceptionLogger.LogException(ex, Source, "DeleteAccount", Severity.Critical);
return false;
}
}