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<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<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 List<SupplierInfo> GetSuppliers(string mode)
{
try
{
var db = new MySqlDatabase(DbConfiguration.DatabaseRead);
MySqlCommand command = CommandBuilder.BuildGetSuppliersCommand(mode, db.Connection);
DataSet dataSet = db.ExecuteQuery(command);
return GetSuppliers(dataSet);
}
catch (Exception ex)
{
DBExceptionLogger.LogException(ex, Source, "GetSuppliers", Severity.Critical);
return null;
}
}
public Voucher GetVoucher(string voucherCode)
{
try
{
var db = new MySqlDatabase(DbConfiguration.DatabaseRead);
MySqlCommand command = CommandBuilder.BuildGetVoucherCommand(voucherCode, db.Connection);
DataSet dataSet = db.ExecuteQuery(command);
return GetVoucher(dataSet);
}
catch (Exception ex)
{
DBExceptionLogger.LogException(ex, Source, "GetPaymentProviderDetails", Severity.Critical);
return null;
}
}
public List<FlightRequest> GetFlightRequests()
{
List<FlightRequest> flightRequests = new List<FlightRequest>();
var db = new MySqlDatabase(DbConfiguration.DatabaseRead);
try
{
DataSet dataset = db.ExecuteQuery(CommandBuilder.BuildGetFlightRequestCommand(db.Connection));
if (dataset != null && dataset.Tables.Count > 0)
{
if (dataset.Tables[0].Rows != null && dataset.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in dataset.Tables[0].Rows)
{
if (!Convert.IsDBNull(row["RequestId"]))
{
var request = new FlightRequest()
{
RequestId = row["RequestId"].GetString(),
FriendlyRequestId = row["FriendlyRequestId"].GetString(),
RequestAddDate = row["AddDate"].GetDate(),
FirstName = row["FirstName"].GetString(),
LastName = row["LastName"].GetString(),
FromAirportName = row["FromAirportName"].GetString(),
ToAirportName = row["ToAirportName"].GetString(),
TravelDate = row["TravelDate"].GetDate(),
RequesterBidAmount = row["BidAmount"].GetDecimal(),
AirlineBidAmount = row["AirlineBidAmount"].GetDecimal(),
TrainPnr = row["TrainPnr"].GetString(),
PaxCount = row["PaxCount"].GetInt(),
ProcessedDate = DateTime.Now
};
flightRequests.Add(request);
}
}
}
}
}
catch (Exception ex)
{
DBExceptionLogger.LogException(ex, Source, "GetFlightRequests", Severity.Critical);
throw ex;
}
return flightRequests;
}
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";
}
public SalesSummary GetSalesSummary(string fromAirport, string toAirport, string airlineCode, DateTime date)
{
try
{
var db = new MySqlDatabase(DbConfiguration.DatabaseRead);
MySqlCommand command = CommandBuilder.BuildGetSalesSummaryCommand(fromAirport, toAirport, airlineCode,
date, db.Connection);
DataSet dataSet = db.ExecuteQuery(command);
SalesSummary salesSummary = GetSalesSummary(dataSet);
return salesSummary;
}
catch (Exception ex)
{
DBExceptionLogger.LogException(ex, Source, "GetSalesSummary", Severity.Critical);
return null;
}
}
public SupplierInfo GetSupplier(string provider, string mode)
{
try
{
var db = new MySqlDatabase(DbConfiguration.DatabaseRead);
MySqlCommand command = CommandBuilder.BuildGetSupplierCommand(provider, mode, db.Connection);
DataSet dataSet = db.ExecuteQuery(command);
var suppliers = GetSuppliers(dataSet);
if (suppliers != null && suppliers.Count > 0)
return suppliers[0];
}
catch (Exception ex)
{
DBExceptionLogger.LogException(ex, Source, "GetSuppliers", Severity.Critical);
}
return null;
}
public Token GetToken(string tokenId)
{
try
{
var db = new MySqlDatabase(DbConfiguration.TokenDB);
MySqlCommand cmd = CommandBuilder.BuildGetTokenCommand(db.Connection, tokenId);
DataSet dataset = db.ExecuteQuery(cmd);
var token = GetToken(dataset);
if (token != null)
{
if (token.TokenExpirationDate >= DateTime.Now)
{
return token;
}
}
}
catch (Exception ex)
{
DBExceptionLogger.LogException(ex, Source, "GetToken", Severity.Critical);
return null;
}
return null;
}
public ReservationListing GetReservationListing(RequestParams requestParams)
{
try
{
var db = new MySqlDatabase(DbConfiguration.DatabaseRead);
ReservationFilterInfo filterInfo = null;
PagingInfo pagingInfo = null;
if (requestParams != null)
{
filterInfo = (requestParams.FilterInfo != null &&
requestParams.FilterInfo is ReservationFilterInfo)
? requestParams.FilterInfo as ReservationFilterInfo
: null;
pagingInfo = requestParams.PagingInfo ?? new PagingInfo
{
PageSize = 10,
PageNumber = 1
};
}
MySqlCommand command = CommandBuilder.BuildGetReservationListingCommand(filterInfo, pagingInfo,
db.Connection);
DataSet dataSet = db.ExecuteQuery(command);
ReservationListing reservationListing = GetReservationListing(dataSet);
return reservationListing;
}
catch (Exception ex)
{
DBExceptionLogger.LogException(ex, Source, "GetReservationListing", Severity.Critical);
return null;
}
}
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 List<Request> GetRequestsByAccountId(string accountId)
{
var requests = new List<Request>();
try
{
var db = new MySqlDatabase(DbConfiguration.DatabaseRead);
DataSet dataset =
db.ExecuteQuery(CommandBuilder.BuildGetRequestsByAccountIdCommand(accountId, db.Connection));
requests = GetRequests(dataset, db);
return requests;
}
catch (Exception ex)
{
DBExceptionLogger.LogException(ex, Source, "GetRequestsByAccountId", Severity.Critical);
}
return requests;
}
public string GetRequestIdByNumber(string requestNumber)
{
try
{
var db = new MySqlDatabase(DbConfiguration.DatabaseRead);
DataSet dataset = db.ExecuteQuery(CommandBuilder.BuildGetRequestIdCommand(requestNumber, db.Connection));
return GetRequestId(dataset);
}
catch (Exception ex)
{
DBExceptionLogger.LogException(ex, Source, "GetRequestIdByNumber", Severity.Critical);
}
return null;
}
public Request GetRequest(string requestId)
{
if (string.IsNullOrEmpty(requestId)) return null;
try
{
var db = new MySqlDatabase(DbConfiguration.DatabaseRead);
DataSet dataset = db.ExecuteQuery(CommandBuilder.BuildGetRequestCommand(requestId, db.Connection));
List<Request> requests = GetRequests(dataset, db);
if (requests != null && requests.Count > 0)
return requests[0];
else
return null;
}
catch (Exception ex)
{
DBExceptionLogger.LogException(ex, Source, "GetRequest", Severity.Critical);
}
return null;
}
public List<FlightRequest> GetProcessedFlightRequests()
{
List<FlightRequest> flightRequests = new List<FlightRequest>();
var db = new MySqlDatabase(DbConfiguration.DatabaseRead);
try
{
DataSet dataset = db.ExecuteQuery(CommandBuilder.BuildGetProcessedRequestsCommand(db.Connection));
if (dataset != null && dataset.Tables.Count > 0)
{
if (dataset.Tables[0].Rows != null && dataset.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in dataset.Tables[0].Rows)
{
if (!Convert.IsDBNull(row["RequestId"]))
{
var request = ParseProcessedFlightRequest(db, row);
flightRequests.Add(request);
}
}
}
}
}
catch (Exception ex)
{
DBExceptionLogger.LogException(ex, Source, "GetProcessedFlightRequests", Severity.Critical);
throw ex;
}
return flightRequests;
}
public FlightRequest GetProcessedFlightRequest(string requestId)
{
var db = new MySqlDatabase(DbConfiguration.DatabaseRead);
try
{
DataSet dataset = db.ExecuteQuery(CommandBuilder.BuildGetProcessedRequestCommand(db.Connection, requestId));
if (dataset != null && dataset.Tables.Count > 0)
{
if (dataset.Tables[0].Rows != null && dataset.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in dataset.Tables[0].Rows)
{
if (!Convert.IsDBNull(row["RequestId"]))
{
var request = ParseProcessedFlightRequest(db, row);
return request;
}
}
}
}
}
catch (Exception ex)
{
DBExceptionLogger.LogException(ex, Source, "GetProcessedFlightRequest(requestId)", Severity.Critical);
}
return null;
}
public AdminAccount GetAdminAccount(string accountId)
{
try
{
var db = new MySqlDatabase(DbConfiguration.DatabaseRead);
MySqlCommand command;
command = CommandBuilder.BuildGetAdminAccountCommand(accountId, db.Connection);
DataSet dataSet = db.ExecuteQuery(command);
AdminAccount account = GetAdminAccount(dataSet);
return account;
}
catch (Exception ex)
{
DBExceptionLogger.LogException(ex, Source, "GetAdminAccountByAccountId", Severity.Critical);
return null;
}
}
public string GetAccountIdForSocialAccount(string accountId, string accountType)
{
try
{
var db = new MySqlDatabase(DbConfiguration.DatabaseRead);
DataSet dataSet =
db.ExecuteQuery(CommandBuilder.GetAccountIdForSocialAccountCommand(accountId, accountType,
db.Connection));
return GetAccountId(dataSet);
}
catch (Exception ex)
{
DBExceptionLogger.LogException(ex, Source, "GetAccountIdForSocialAccount", Severity.Critical);
return string.Empty;
}
}
public List<Traveller> GetTravellers(string accountId)
{
try
{
var db = new MySqlDatabase(DbConfiguration.DatabaseRead);
DataSet dataSet = db.ExecuteQuery(CommandBuilder.BuildGetTravellersByAccountIdCommand(accountId, db.Connection));
List<Traveller> travellers = GetTravellers(dataSet);
return travellers;
}
catch (Exception ex)
{
DBExceptionLogger.LogException(ex, Source, "GetTravellers(accountId)", Severity.Critical);
return null;
}
}
public List<Request> GetTomorrowsRequests()
{
var requests = new List<Request>();
try
{
var db = new MySqlDatabase(DbConfiguration.DatabaseRead);
DataSet dataset =
db.ExecuteQuery(CommandBuilder.BuildGetTomorrowsRequestsCommand(db.Connection));
requests = GetRequests(dataset, db, true);
return requests;
}
catch (Exception ex)
{
DBExceptionLogger.LogException(ex, Source, "GetTomorrowsRequests", Severity.Critical);
}
return requests;
}
private static Address GetAddress(int addressId)
{
var db = new MySqlDatabase(DbConfiguration.DatabaseRead);
DataSet dataSet = db.ExecuteQuery(CommandBuilder.BuildGetAddressCommand(addressId, db.Connection));
if (dataSet != null && dataSet.Tables.Count > 0)
{
if (dataSet.Tables[0].Rows != null && dataSet.Tables[0].Rows.Count == 1)
{
DataRow row = dataSet.Tables[0].Rows[0];
if (Convert.IsDBNull(row["AddressId"]))
return null;
var address = new Address
{
Id = addressId,
AddressLine1 =
row["AddressLine1"].GetString(),
AddressLine2 =
row["AddressLine2"].GetString(),
City =
row["CityName"].GetString(),
Country =
row["CountryCode"].GetString(),
State = row["State"].GetString(),
ZipCode =
row["ZipCode"].GetString(),
AddressType = row["AddressType"].GetEnum(AddressType.Billing),
};
return address;
}
}
return null;
}
private List<Passenger> GetPassengers(string requestId, MySqlDatabase db)
{
var passengerlist = new List<Passenger>();
DataSet dataset = db.ExecuteQuery(CommandBuilder.BuildGetPassengersRequest(requestId, db.Connection));
if (dataset != null && dataset.Tables.Count > 0)
{
if (dataset.Tables[0].Rows != null && dataset.Tables[0].Rows.Count > 0)
{
foreach (DataRow row in dataset.Tables[0].Rows)
{
if (!Convert.IsDBNull(row["PassengerId"]) && !Convert.IsDBNull(row["RequestId"]))
{
var passenger = new Passenger
{
PassengerId = row["PassengerId"].GetString(),
Title = row["Title"].GetString(),
FirstName = row["FirstName"].GetString(),
LastName = row["LastName"].GetString(),
Gender = row["Gender"].GetString(),
DateOfBirth = row["DateOfBirth"].GetDate()
};
passengerlist.Add(passenger);
}
}
}
}
return passengerlist;
}
public Account GetAccount(string email, string password, string type)
{
try
{
var db = new MySqlDatabase(DbConfiguration.DatabaseRead);
MySqlCommand command;
command = string.IsNullOrEmpty(type)
? CommandBuilder.BuildGetAccountCommand(email, password, db.Connection)
: CommandBuilder.BuildGetSocialAccountCommand(email, type, db.Connection);
DataSet dataSet = db.ExecuteQuery(command);
Account account = GetAccount(dataSet);
return account;
}
catch (Exception ex)
{
DBExceptionLogger.LogException(ex, Source, "GetAccount", Severity.Critical);
return null;
}
}
public bool Exists(string socialAccountId, string socialAccountType)
{
try
{
var db = new MySqlDatabase(DbConfiguration.DatabaseRead);
DataSet dataSet =
db.ExecuteQuery(CommandBuilder.BuildSocialAccountExistsCommand(socialAccountId, socialAccountType,
db.Connection));
if (dataSet != null && dataSet.Tables.Count > 0)
{
if (dataSet.Tables[0].Rows != null && dataSet.Tables[0].Rows.Count == 1)
{
DataRow row = dataSet.Tables[0].Rows[0];
if (Convert.IsDBNull(row[0]))
return false;
return row[0].GetInt() == 1;
}
}
}
catch (Exception ex)
{
DBExceptionLogger.LogException(ex, Source, "Exists(string,string)", Severity.Critical);
return false;
}
return false;
}
public string GetAccountIdForMobile(string mobile)
{
try
{
var db = new MySqlDatabase(DbConfiguration.DatabaseRead);
DataSet dataSet =
db.ExecuteQuery(CommandBuilder.BuildGetAccountIdForMobileCommand(mobile, db.Connection));
return GetAccountId(dataSet);
}
catch (Exception ex)
{
DBExceptionLogger.LogException(ex, Source, "GetAccountIdForMobile", Severity.Critical);
return string.Empty;
}
}