public List <GetActiveProviders_Result> GetActiveProvider()
{
using (BusinessRulesDBEntities db = new BusinessRulesDBEntities())
{
List <GetActiveProviders_Result> providers_Results = db.GetActiveProviders().ToList();
return(providers_Results);
}
}
public List <ClsProvider> GetProviderDetails()
{
List <ClsProvider> providers = new List <ClsProvider>();
BusinessRulesDBEntities db = new BusinessRulesDBEntities();
var provs = db.HotelProviders.ToList();
foreach (var item in provs)
{
ClsProvider provider = new ClsProvider();
provider.Currency = item.Provider_Currency_Code;
provider.ProviderId = item.Provider_ID.Value;
provider.ProviderName = item.Provider_Name;
provider.status = item.Active_Status.Value;
if (provider.status == true)
{
providers.Add(provider);
}
}
return(providers);
}
public DiscountRule GetDiscountByID(int DiscountID)
{
DiscountRule discount = new DiscountRule();
using (BusinessRulesDBEntities db = new BusinessRulesDBEntities())
{
SalesDiscount salesDiscount = db.SalesDiscounts.Where(a => a.ID == DiscountID).FirstOrDefault();
if (salesDiscount != null)
{
discount.ID = salesDiscount.ID;
discount.Name = salesDiscount.DiscountName;
discount.Base = salesDiscount.DiscountBase;
discount.commAmount = salesDiscount.CommAmt.Value;
discount.commRelatedUnit = salesDiscount.CommRelatedUnit;
discount.commRound = salesDiscount.CommRound;
discount.commType = salesDiscount.CommType;
discount.EndDate = salesDiscount.DiscountEndDate.Value;
discount.Priority = salesDiscount.DiscountPriority.Value;
discount.StartDate = salesDiscount.DiscountStartDate.Value;
List <SalesDiscountCriteria> discountCriteria = db.SalesDiscountCriterias.Where(a => a.DiscountID == discount.Name).ToList();
if (discountCriteria.Count > 0)
{
discount.CriteriaList = (from dc in discountCriteria
join c in db.Criterias_Difinition on dc.DiscountCriteria equals c.ID.ToString()
select new SalesRulesCriteria
{
criteriaName = c.criteriaName,
operation = dc.operation,
textValue = dc.CriteriaValueText,
value = dc.CriteriaValue
}).ToList();
}
}
}
return(discount);
}
public MarkupRule GetMarkupByID(int MarkupID)
{
MarkupRule markup = new MarkupRule();
using (BusinessRulesDBEntities db = new BusinessRulesDBEntities())
{
SalesMarkup salesMarkup = db.SalesMarkups.Where(a => a.ID == MarkupID).FirstOrDefault();
if (salesMarkup != null)
{
markup.ID = salesMarkup.ID;
markup.Name = salesMarkup.MarkupName;
markup.Base = salesMarkup.MarkupBase;
markup.commAmount = salesMarkup.CommAmt.Value;
markup.commRelatedUnit = salesMarkup.CommRelatedUnit;
markup.commRound = salesMarkup.CommRound;
markup.commType = salesMarkup.CommType;
markup.EndDate = salesMarkup.MarkupEndDate.Value;
markup.Priority = salesMarkup.MarkupPriority.Value;
markup.StartDate = salesMarkup.MarkupStartDate.Value;
List <SalesMarkupCriteria> markupCriteria = db.SalesMarkupCriterias.Where(a => a.MarkupID == markup.Name).ToList();
if (markupCriteria.Count > 0)
{
markup.CriteriaList = (from mc in markupCriteria
join c in db.Criterias_Difinition on mc.MarkupCriteria equals c.ID.ToString()
select new SalesRulesCriteria
{
criteriaName = c.criteriaName,
operation = mc.operation,
textValue = mc.CriteriaValueText,
value = mc.CriteriaValue
}).ToList();
}
}
}
return(markup);
}
public async Task <EveryDayCurrenciesConversion> GetEveryDayCurrenciesConversion(string fromCurrency, string toCurrency, string SID, DateTime dateTime)
{
ConverterCurrency currencyData = new ConverterCurrency();
using (BusinessRulesDBEntities db = new BusinessRulesDBEntities())
{
if (fromCurrency == null)
{
fromCurrency = "KWD";
}
if (fromCurrency.ToLower() == "769")
{
fromCurrency = "KWD";
}
if (fromCurrency.ToLower() == toCurrency.ToLower())
{
return(new EveryDayCurrenciesConversion()
{
ToCurrency = toCurrency,
FromCurrency = fromCurrency,
Customer_Purchase_Rate = 1.0,
Customer_Sell_Rate = 1.0,
Supplier_Purchase_Rate = 1.0,
Supplier_Sell_Rate = 1.0
});
}
if (dateTime.ToString("dd MMM yyyy").ToLower() == DateTime.Now.ToString("dd MMM yyyy").ToLower())
{
var client = new HttpClient();
var path = ConfigurationSettings.AppSettings["CurrApi"];
var url = path + "/api/CurrencyApi?fromCurrency=" + fromCurrency + "&toCurrency=" + toCurrency;
var response = client.GetAsync(url).Result;
if (response.IsSuccessStatusCode)
{
currencyData = await response.Content.ReadAsAsync <ConverterCurrency>();
}
else
{
}
if (currencyData != null) //Check Change Rate == 0
{
//Log Exchange Rate
LoggingHelper.WriteToFile("CurrencyExchangeRate", "SearchID " + SID, "fromCurrency = " + fromCurrency + " & toCurrency = " + toCurrency, "CurrencyExchangeRateVal " + currencyData.ExchangeRate);
return(new EveryDayCurrenciesConversion()
{
ToCurrency = currencyData.ToCurrency,
FromCurrency = currencyData.FromCurrency,
Customer_Purchase_Rate = currencyData.ExchangeRate,
Customer_Sell_Rate = currencyData.ExchangeRate,
Supplier_Purchase_Rate = currencyData.ExchangeRate,
Supplier_Sell_Rate = currencyData.ExchangeRate
});
}
else
{
return(null);
}
}
return(null);
}
}
///////Un used XXXXXXXXXXXXXXXXXXXXX /////////////////////////////////////////////
private List <MarkupRule> GetMarkup(string Category, string POS, string service)
{
List <MarkupRule> list = new List <MarkupRule>();
if (string.IsNullOrEmpty(Category))
{
Category = "Hotel";
}
if (string.IsNullOrEmpty(POS))
{
POS = "KW";
}
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
DataSet DS = new DataSet();
SqlDataAdapter DA = new SqlDataAdapter();
DS.Clear();
SqlCommand com = new SqlCommand();
com.Connection = connection;
com.CommandType = CommandType.StoredProcedure;
com.CommandText = "[dbo].[GetSalesMarkup]";
com.Parameters.Add(new SqlParameter("pos", POS));
com.Parameters.Add(new SqlParameter("category", Category));
com.Parameters.Add(new SqlParameter("Service", service));
DA.SelectCommand = com;
DA.Fill(DS);
com.Dispose();
DA.Dispose();
connection.Close();
if (DS.Tables.Count > 0)
{
list = DS.Tables[0].AsEnumerable().Select(row => new MarkupRule()
{
ID = row.Field <int>("ID"),
Name = row.Field <string>("MarkupName"),
Base = row.Field <string>("MarkupBase"),
chargeId = row.Field <int>("ChargeId"),
Branch = row.Field <string>("MarkupBranch"),
commType = row.Field <string>("CommType"),
commAmount = row.Field <double>("CommAmt"),
Product = row.Field <string>("MarkupProduct"),
commRelatedUnit = row.Field <string>("CommRelatedUnit"),
StartDate = row.Field <DateTime>("MarkupStartDate"),
EndDate = row.Field <DateTime>("MarkupEndDate"),
Status = row.Field <bool>("MarkupStatus"),
Priority = row.Field <int>("MarkupPriority"),
commRound = row.Field <string>("CommRound"),
}).ToList();
List <SalesMarkupCriteria> criteriaList = DS.Tables[1].AsEnumerable().Select(row => new SalesMarkupCriteria()
{
MarkupID = row.Field <string>("MarkupID"),
MarkupCriteria = row.Field <string>("criteriaName"),
operation = row.Field <string>("operation"),
CriteriaValue = row.Field <string>("CriteriaValue"),
CriteriaValueText = row.Field <string>("CriteriaValueText"),
}).ToList();
List <SalesRulesFlightOption> flighoption = new List <SalesRulesFlightOption>();
if (Category.ToLower() == "flight")
{
using (BusinessRulesDBEntities db = new BusinessRulesDBEntities())
{
List <string> markupids = list.Select(a => a.Name).ToList();
flighoption = db.SalesRulesFlightOptions.Where(a => a.Category.ToLower() == "markup" && markupids.Contains(a.CategotyId)).ToList();
}
}
foreach (var item in list)
{
if (Category.ToLower() == "flight")
{
var flighoptiondata = flighoption.Where(a => a.CategotyId == item.Name).FirstOrDefault();
if (flighoptiondata != null)
{
item.AllowMultiAirlines = flighoptiondata.AllowMultiAirlines.Value;
item.AllowMultiDestination = flighoptiondata.AllowMultiDestination.Value;
}
}
item.CriteriaList = criteriaList.Where(a => a.MarkupID == item.Name).Select(a => new SalesRulesCriteria
{
criteriaName = a.MarkupCriteria,
operation = a.operation,
textValue = a.CriteriaValueText,
value = a.CriteriaValue
}).ToList();
if (item.CriteriaList.Count == 0)
{
item.IsGeneric = true;
}
}
}
return(list);
}
}
public AdminBookingDetails GetBookingDetails(string BN)
{
try
{
HotelBookingDBEntities BookingDB = new HotelBookingDBEntities();
SearchDBEntities searchDB = new SearchDBEntities();
AdminBookingDetails requiredData = new AdminBookingDetails();
hotelsDBEntities db = new hotelsDBEntities();
CurrencyRepo repo = new CurrencyRepo();
BusinessRulesDBEntities dbp = new BusinessRulesDBEntities();
double CancelRate = 0;
var BaseCur = ConfigurationSettings.AppSettings["BaseCur"];
var BookinData = BookingDB.HotelsBookings.FirstOrDefault(a => a.Booking_No.ToLower() == BN.ToLower());
var searchData = searchDB.SearchCriterias.FirstOrDefault(a => a.sID == BookinData.SessionId);
requiredData.bookingNo = BN;
requiredData.bookingPhoneCode = BookinData.Booking_Phone_Code;
requiredData.bookingTime = BookinData.Booking_Time.Value;
requiredData.CBNumberData.LanguagesList = BookingDB.Languages.ToList();
var lang = requiredData.CBNumberData.LanguagesList.FirstOrDefault(a => a.LanguageCode.ToLower() == searchData.language.ToLower());
if (lang != null)
{
requiredData.CBNumberData.Language = lang.LanguageID;
}
requiredData.CBNumberData.PointsOfSaleList = BookingDB.PointsOfSales.ToList();
var pointofsale = requiredData.CBNumberData.PointsOfSaleList.FirstOrDefault(a => a.NameCode.ToLower() == searchData.pos.ToLower());
if (pointofsale != null)
{
requiredData.CBNumberData.PointOfSale = int.Parse(pointofsale.Code);
}
requiredData.CBNumberData.SourceList = BookingDB.SourceTraffics.ToList();
var src = requiredData.CBNumberData.SourceList.FirstOrDefault(a => a.Source.ToLower() == searchData.source.ToLower());
if (src != null)
{
requiredData.CBNumberData.Source = src.Code;
}
requiredData.checkIN = searchData.dateFrom.Value;
requiredData.checkOut = searchData.dateTo.Value;
requiredData.city = searchData.cityName;
var cityData = db.Cities.FirstOrDefault(c => c.ID.ToString() == searchData.cityName);
requiredData.cityName = cityData.City1;
var conversionRate = repo.GetEveryDayCurrenciesConversion(BookinData.Sell_Currency, BaseCur, searchData.sID, DateTime.Now).Result.Customer_Sell_Rate;
requiredData.costAmount = BookinData.Sell_Price.Value * conversionRate;
requiredData.costCurrency = BaseCur;
requiredData.country = cityData.countryName;
requiredData.customerEmail = BookinData.Booking_Email;
var name = BookinData.Pax_Name.Split(' ');
requiredData.customerFirstName = name[0];
requiredData.customerID = BookinData.Customer_ID;
requiredData.customerLastName = name[1];
requiredData.customerPhone = BookinData.Booking_phone;
requiredData.customerPhoneCode = BookinData.Booking_Phone_Code;
requiredData.ForeignAmount = BookinData.Sell_Price.Value; //**//
requiredData.hotel = BookinData.Hotel_ID;
requiredData.hotelConfirmationNo = "";
var hoteldata = db.hotels.FirstOrDefault(a => a.hotelID == BookinData.Hotel_ID);
requiredData.hotelName = hoteldata.hotelName;
requiredData.lastBookingStatus = BookinData.Booking_Status;
requiredData.paxName = BookinData.Pax_Name;
requiredData.paxQty = BookinData.Pax_Qty.Value;
// get provider
var prov = dbp.HotelProviders.FirstOrDefault(a => a.Provider_ID.ToString() == BookinData.Provider_ID);
if (prov != null)
{
requiredData.provider = prov.Provider_Name;
}
requiredData.roomsQty = BookinData.Rooms_Qty.Value;
requiredData.salesChannel = searchData.source;
requiredData.sellCurrency = BookinData.Sell_Currency;//*///
var ExchangeRate = repo.GetEveryDayCurrenciesConversion(BaseCur, BookinData.Sell_Currency, searchData.sID, DateTime.Now).Result.Customer_Sell_Rate;
requiredData.sellCurrencyExchRate = ExchangeRate; //**///from base to user//
requiredData.sellPrice = BookinData.Sell_Price.Value * conversionRate; //**// base curr//
var CostData = BookingDB.AvailabilityRes.FirstOrDefault(a => a.BookingNum == BookinData.Booking_No && a.Sid == BookinData.SessionId);
requiredData.SupplierCost = CostData.NewTotalcost.Value; /////////////////****************** //
requiredData.TotalCostDinars = BookinData.Sell_Price.Value * conversionRate; //**//***with base curr//
var dataHotelStautsList = BookingDB.HotelBookingStatus.Where(a => a.Booking_No == BN).ToList();
foreach (var item in dataHotelStautsList)
{
BookingStatusList bookingStatus = new BookingStatusList();
bookingStatus.Booking_No = BookinData.Booking_No;
bookingStatus.Booking_Status = item.Booking_Status;
bookingStatus.Status_Time = item.Status_Time.Value;
requiredData.BookingStatusList.Add(bookingStatus);
}
var BookingRooms = BookingDB.HotelBookingRooms.Where(x => x.Booking_No == BookinData.Booking_No && x.SID == BookinData.SessionId).ToList();
for (int i = 0; i < BookingRooms.Count; i++)
{
BookingRoom bookingRoom = new BookingRoom();
bookingRoom.bookingNo = BookingRooms[i].Booking_No;
bookingRoom.meal = BookingRooms[i].Meal;
bookingRoom.paxQty = BookingRooms[i].Paxs_Qty.Value;
bookingRoom.roomCategory = BookingRooms[i].Room_Category;
bookingRoom.roomNo = i + 1;//**// incremented//
var roomnum = BookingRooms[i].Room_No.ToString();
var RoomsStats = BookingDB.HotelBookingRoomsStatus.Where(a => a.Booking_No == BN && a.Room_No.ToString() == roomnum).OrderByDescending(a => a.Id).FirstOrDefault();
if (RoomsStats != null)
{
bookingRoom.RoomStatus = RoomsStats.Room_Status.ToString();//status add culomn//
}
bookingRoom.roomType = BookingRooms[i].Room_Category;
var availabilityHBroom = BookingDB.availabilityRoomRes.Where(x => x.BookingNum == BN && x.roomId.ToString() == roomnum).OrderByDescending(a => a.id).FirstOrDefault();
if (availabilityHBroom != null)
{
bookingRoom.TotalCostPerRoom = availabilityHBroom.Cost.Value;///********************************* provider cost//
}
else
{
var availabilityroom = searchDB.SearchRoomResults.FirstOrDefault(x => x.sID == BookinData.SessionId && x.HotelCode == BookinData.Hotel_ID && x.RoomCode == BookingRooms[i].Room_No.ToString());
if (availabilityroom != null)
{
bookingRoom.TotalCostPerRoom = availabilityroom.costPrice.Value;
}
}
var roomNum = BookingRooms[i].Room_No.Value;
var RoomCancels = BookingDB.CancelPolicies.Where(x => x.RoomCode == roomNum && x.Sid == BookinData.SessionId && x.HotelCode == BookinData.Hotel_ID).ToList();
if (RoomCancels.Count > 0)
{
CancelRate = repo.GetEveryDayCurrenciesConversion(RoomCancels[0].Currency, BaseCur, searchData.sID, DateTime.Now).Result.Customer_Sell_Rate;
}
foreach (var item in RoomCancels)
{
SupplierCancellation supplierCancellation = new SupplierCancellation();
CustomerCancellation cancellation = new CustomerCancellation();
cancellation.Booking_No = BookinData.Booking_No;
cancellation.Date_From = item.FromDate.Value;
cancellation.Date_To = item.ToDate;
cancellation.ID = item.Id;
cancellation.No_Show_Amount = (double)Math.Round(item.SellPrice.Value, 3) * CancelRate;
cancellation.Room_No = BookingRooms[i].Room_No.Value;
cancellation.Rule_Text = cancellation.No_Show_Amount.ToString() + " From " + item.FromDate.Value;
supplierCancellation.Booking_No = BookinData.Booking_No;
supplierCancellation.Date_From = item.FromDate.Value;
supplierCancellation.Date_To = item.ToDate;
supplierCancellation.ID = item.Id;
supplierCancellation.No_Show_Amount = (double)Math.Round(item.SellPrice.Value, 3) * CancelRate;
supplierCancellation.Room_No = BookingRooms[i].Room_No.Value;
bookingRoom.SupplierCancellations.Add(supplierCancellation);
bookingRoom.CustomerCancellations.Add(cancellation);
}
var BookingNights = BookingDB.HotelBookingNights.Where(a => a.Booking_No == BookinData.Booking_No && a.Room_No == roomNum).ToList();
foreach (var item in BookingNights)
{
BookingNight night = new BookingNight();
night.bookingNo = item.Booking_No;
night.currency = BaseCur;
night.exchangeRate = 1;
night.nightDate = item.NightDate.Value;
night.rate = item.Rate.Value * conversionRate;
night.roomNo = item.Room_No.Value;
bookingRoom.bookingNights.Add(night);
}
var BookingPaxes = BookingDB.HotelBookingPaxs.Where(a => a.SID == BookinData.SessionId && a.Booking_No == BookinData.Booking_No && a.Room_No == roomNum && a.PaxNo == 1).ToList();
foreach (var item in BookingPaxes)
{
BookingPassenger passenger = new BookingPassenger();
passenger.bookingNo = item.Booking_No;
passenger.DateOfBirth = Convert.ToDateTime("0001- 01-01T00:00:00");//********* BD and Just lead(okay)
passenger.firstName = item.First_name;
passenger.lastName = item.Last_Name;
passenger.nationality = searchData.passengerNationality;
passenger.paxType = item.Pax_Type;
passenger.phone = item.Phone;
passenger.phoneCode = item.Phone_Code;
passenger.roomNo = item.Room_No.Value;
passenger.salutation = item.Salutations;
bookingRoom.bookingPassengers.Add(passenger);
}
var dataRoomStautsList = BookingDB.HotelBookingRoomStatusValues.ToList();
foreach (var item in dataRoomStautsList)
{
RoomStatusList roomStatus = new RoomStatusList();
roomStatus.ID = item.ID;
roomStatus.Status = item.Status;
bookingRoom.RoomStatusList.Add(roomStatus);
}
requiredData.bookingRooms.Add(bookingRoom);
}
return(requiredData);
}
catch (Exception ex)
{
LoggingHelper.WriteToFile("AdminPannelController/ERROR/", "GetBookingDetails" + "INDAL" + BN, "Data", "bookin is " + BN + " and booking is" + BN + ex.Message + ex.StackTrace);
return(new AdminBookingDetails());
}
}