public void Save(CLayer.Discount data)
{
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
param.Add(Connection.GetParameter("pB2BId", DataPlug.DataType._BigInt, data.B2BId));
param.Add(Connection.GetParameter("pPropertyId", DataPlug.DataType._BigInt, data.PropertyId));
param.Add(Connection.GetParameter("pShortTerm", DataPlug.DataType._Decimal, data.ShortTermRate));
param.Add(Connection.GetParameter("pLongTerm", DataPlug.DataType._Decimal, data.LongTermRate));
Connection.ExecuteQuery("discount_Save", param);
}
public void SaveStdDiscount(CLayer.Discount data)
{
StringBuilder sql = new StringBuilder();
sql.Append("Update property Set B2BStdShortTermDis=");
sql.Append(data.ShortTermRate);
sql.Append(",B2BStdLongTermDis=");
sql.Append(data.LongTermRate);
sql.Append(" Where PropertyId =");
sql.Append(data.PropertyId);
Connection.ExecuteSqlQuery(sql.ToString());
}
public CLayer.Discount GetStdDiscount(long propertyId)
{
string sql = "Select B2BStdShortTermDis,B2BStdLongTermDis From property Where PropertyId =" + propertyId.ToString();
DataTable dt = Connection.GetSQLTable(sql);
CLayer.Discount dis = null;
if (dt.Rows.Count > 0)
{
dis = new CLayer.Discount();
dis.PropertyId = propertyId;
dis.LongTermRate = Connection.ToDouble(dt.Rows[0]["B2BStdLongTermDis"]);
dis.ShortTermRate = Connection.ToDouble(dt.Rows[0]["B2BStdShortTermDis"]);
}
return(dis);
}
public CLayer.Discount Get(long b2bId, long propertyId)
{
string sql = "Select b.B2BId,b.Name,d.ShortTerm,d.LongTerm From discount d INNER JOIN b2b b ON d.B2Bid = b.B2BId Where d.PropertyId=" + propertyId.ToString() + " And d.B2BId=" + b2bId.ToString();
DataTable dt = Connection.GetSQLTable(sql);
CLayer.Discount result = null;
if (dt.Rows.Count > 0)
{
result = new CLayer.Discount();
result.B2BId = Connection.ToLong(dt.Rows[0]["B2BId"]);
result.B2BName = Connection.ToString(dt.Rows[0]["Name"]);
result.ShortTermRate = Connection.ToDouble(dt.Rows[0]["ShortTerm"]);
result.LongTermRate = Connection.ToDouble(dt.Rows[0]["LongTerm"]);
result.PropertyId = propertyId;
}
return(result);
}
public List <CLayer.Discount> GetAll(long propertyId)
{
string sql = "Select b.B2BId,b.Name,d.ShortTerm,d.LongTerm From discount d INNER JOIN b2b b ON d.B2Bid = b.B2BId Where d.PropertyId=" + propertyId.ToString();
DataTable dt = Connection.GetSQLTable(sql);
List <CLayer.Discount> result = new List <CLayer.Discount>();
CLayer.Discount temp;
foreach (DataRow dr in dt.Rows)
{
temp = new CLayer.Discount();
temp.B2BId = Connection.ToLong(dr["B2BId"]);
temp.B2BName = Connection.ToString(dr["Name"]);
temp.ShortTermRate = Connection.ToDouble(dr["ShortTerm"]);
temp.LongTermRate = Connection.ToDouble(dr["LongTerm"]);
temp.PropertyId = propertyId;
result.Add(temp);
}
return(result);
}
public CLayer.Discount GetDiscount(long b2bId, long propertyId)
{
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
param.Add(Connection.GetParameter("pB2bId", DataPlug.DataType._BigInt, b2bId));
param.Add(Connection.GetParameter("pPropertyId", DataPlug.DataType._BigInt, propertyId));
DataTable dt = Connection.GetTable("b2b_GetDiscount", param);
CLayer.Discount result = new CLayer.Discount();
result.ShortTermRate = 0;
result.LongTermRate = 0;
if (dt.Rows.Count > 0)
{
result.ShortTermRate = Connection.ToDouble(dt.Rows[0]["DiscountShort"]);
result.LongTermRate = Connection.ToDouble(dt.Rows[0]["DiscountLong"]);
result.BaseLongTerm = Connection.ToDouble(dt.Rows[0]["BaseLong"]);
result.BaseShortTerm = Connection.ToDouble(dt.Rows[0]["BaseShort"]);
}
return(result);
}
public static void SaveStdDiscount(CLayer.Discount data)
{
DataLayer.Discount dis = new DataLayer.Discount();
dis.SaveStdDiscount(data);
}
public static List <CLayer.Rates> GetTotalRates(List <long> accIds, DateTime checkIn, DateTime checkOut, CLayer.Role.Roles rateType, long loggedInUserId, long InventoryAPIID)
{
List <CLayer.Rates> arates;
StringBuilder ids = new StringBuilder();
bool isCorporate = false;
long TAMInventoryAPIID = InventoryAPIID;
long corpId = 0;
if (rateType == CLayer.Role.Roles.Corporate)
{
CLayer.Role.Roles rle = BLayer.User.GetRole(loggedInUserId);
if (rle == CLayer.Role.Roles.CorporateUser)
{
corpId = BLayer.B2B.GetCorporateIdOfUser(loggedInUserId);
}
else
{
corpId = loggedInUserId;
}
isCorporate = true;
}
foreach (long id in accIds)
{
ids.Append(",");
ids.Append(id);
}
ids.Remove(0, 1);
List <CLayer.Rates> result = new List <CLayer.Rates>();
if (TAMInventoryAPIID == 4 || TAMInventoryAPIID == 5)
{
arates = GetAccommodationRatesForAPI(ids.ToString(), checkIn, checkOut, rateType);
}
else
{
arates = GetAccommodationRates(ids.ToString(), checkIn, checkOut, rateType);
}
DateTime currentDate;
decimal total, currate, curguest_rate, guest, PurchaseRateaAfterTaxs, totalPurchaseRateAfterTaxs, PurchaseRateBeforeTax, SellRateAfterTax, SellRateBeforeTax, Prch_Rate_AT, Prch_Rate_ATX, PurchaseRateAfterTax;
int cnt, idx;
int days = (checkOut - checkIn).Days;
long propertyId = BLayer.Accommodation.GetPropertyId(accIds[0]);
int inventory = 0;
CLayer.Discount dicnt = null;
if (isCorporate)
{
dicnt = BLayer.Discount.GetDiscount(corpId, propertyId);
}
CLayer.RateCommission rc = BLayer.Property.GetCommission(propertyId);
List <CLayer.Offers> propertyOffers = BLayer.Offers.GetForPropertyCalc(propertyId, checkIn, checkOut);
bool hasPrOffers = (propertyOffers.Count > 0);
List <CLayer.Offers> accOffers;
CLayer.Offers propOffer = null;
if (hasPrOffers)
{
propOffer = propertyOffers[0];
}
CLayer.Offers curOffer = null;
DateTime offEndDate, offStartDate;
offStartDate = offEndDate = DateTime.Today;
List <CLayer.BookingItemOffer> appliedOffers;
List <CLayer.Rates.RateValues> bookingRates;
int noOfDays = (checkOut - checkIn).Days;
int reduceDateBy = 0;
int calcDays = 0;
long curOfferId = 0;
decimal curOldRate = 0;
List <DateTime> freeDays = new List <DateTime>();
bool freeDaysExist = false;
foreach (long id in accIds)
{
List <CLayer.Rates> accrates = arates.Where(m => m.AccommodationId == id).OrderBy(x => x.StartDate).ToList();
cnt = accrates.Count();
if (cnt > 0)
{
inventory = accrates[0].NoofAcc;
}
else
{
inventory = 0;
result.Add(new CLayer.Rates()
{
Amount = 0,
GuestRate = 0,
AccommodationId = id,
NoofAcc = 0,
RateChanges = new List <CLayer.Rates.RateValues>(), //FirstDayCharge = fctotal,
SBMarkup = 0,
CorpDiscount = 0,
AppliedOffers = new List <CLayer.BookingItemOffer>()
});
continue;
}
accOffers = BLayer.Offers.GetForAccommodationCalc(id, checkIn, checkOut);
appliedOffers = new List <CLayer.BookingItemOffer>();
curOffer = null;
bookingRates = new List <CLayer.Rates.RateValues>();
if (accOffers.Count > 0)
{
curOffer = accOffers[0];
}
else
{
curOffer = null;
if (hasPrOffers)
{
curOffer = propOffer;
}
}
curOfferId = 0;
currentDate = checkIn;
total = 0;
guest = 0;
PurchaseRateaAfterTaxs = 0;
totalPurchaseRateAfterTaxs = 0;
currate = 0;
curOldRate = 0;
curguest_rate = 0;
SellRateBeforeTax = 0;
PurchaseRateBeforeTax = 0;
Prch_Rate_AT = 0;
Prch_Rate_ATX = 0;
PurchaseRateAfterTax = 0;
SellRateAfterTax = 0;
freeDays.Clear();
//calculate if free nights offer is available..
reduceDateBy = 0;
if (curOffer != null && (!isCorporate)) //offer is not available for corporate
{
//find offer applicable date
if (checkIn > curOffer.StartDate)
{
offStartDate = checkIn;
}
else
{
offStartDate = curOffer.StartDate;
}
if (checkOut < curOffer.EndDate)
{
offEndDate = checkOut;
}
else
{
offEndDate = curOffer.EndDate;
}
if (curOffer.RateType == (int)CLayer.ObjectStatus.OfferRateType.OfferFreeRate)
{
int tot = curOffer.FreeDays + curOffer.NoOfDays; //total days -Example: for 4 days booking 2 days free.. So total - minimum- 6days booking should be there
int hwmany = 0;
calcDays = (offEndDate - offStartDate).Days;
if (calcDays >= tot)
{
hwmany = calcDays / tot;
reduceDateBy = hwmany * curOffer.FreeDays;
// currentDate = currentDate.AddDays(reduceDateBy); //reduce freedays from calculation
if (reduceDateBy == 1)
{
if (offEndDate == checkOut)
{
offEndDate = offEndDate.AddDays(-1);
}
if (offEndDate >= checkIn)
{
freeDays.Add(offEndDate);
}
}
else if (reduceDateBy > 1)
{
if (offEndDate == checkOut)
{
offEndDate = offEndDate.AddDays(-1);
}
while (reduceDateBy > 0)
{
if (offEndDate >= checkIn)
{
freeDays.Add(offEndDate);
}
offEndDate.AddDays(-1 * reduceDateBy);
reduceDateBy--;
}
}
//to do offer applied markit
curOfferId = curOffer.OfferId;
appliedOffers.Add(new CLayer.BookingItemOffer()
{
OfferId = curOffer.OfferId, OfferTitle = curOffer.Title, AccommodationId = id
});
}
curOffer = null; //avoid checking other offer types
}
}
freeDaysExist = (freeDays.Count > 0);
// find tax here
decimal tax = BLayer.PropertyTax.GetTotalTax(propertyId);
while (currentDate < checkOut)
{
//never reset currate or curguest_rate here, if a rate is not found for the date then old rate is used
for (idx = 0; idx < cnt; idx++)
{
if (currentDate >= accrates[idx].StartDate && currentDate <= accrates[idx].EndDate)
{
currate = accrates[idx].Amount;
curguest_rate = accrates[idx].GuestRate;
PurchaseRateaAfterTaxs = accrates[idx].PurchaseRateAfterTax;
Prch_Rate_AT = accrates[idx].PurchaseRateAfterTax;
break;
}
}
if (curOldRate != currate)
{
bookingRates.Add(new CLayer.Rates.RateValues()
{
StartDate = currentDate.ToShortDateString(), DayCharge = (double)currate, DayGuestCharge = (double)curguest_rate, DayTotalCharge = 0, DayTotalGuestCharge = 0
});
curOldRate = currate;
}
if (curOffer != null && (!isCorporate))
{
if (currentDate >= offStartDate && currentDate <= offEndDate)
{
if (curOfferId != curOffer.OfferId && curOfferId != 0)
{
curOfferId = curOffer.OfferId;
appliedOffers.Add(new CLayer.BookingItemOffer()
{
OfferId = curOffer.OfferId, OfferTitle = curOffer.Title, AccommodationId = id
});
}
switch ((CLayer.ObjectStatus.OfferRateType)curOffer.RateType)
{
case CLayer.ObjectStatus.OfferRateType.OfferFlatRate:
total = total + curOffer.Amount;
guest = guest + curguest_rate;
break;
case CLayer.ObjectStatus.OfferRateType.OfferPercentageRate:
total = Math.Round(total + (currate - (currate * curOffer.Amount / 100)));
guest = Math.Round(guest + (curguest_rate - (curguest_rate * curOffer.Amount / 100)));
break;
}
}
else
{
total = total + currate;
totalPurchaseRateAfterTaxs = totalPurchaseRateAfterTaxs + PurchaseRateaAfterTaxs;
guest = guest + curguest_rate;
Prch_Rate_ATX = Prch_Rate_ATX + Prch_Rate_AT;
}
}
else
{
//perform calculations here
if (freeDaysExist)
{
if (!freeDays.Contains(currentDate))
{
total = total + currate;
totalPurchaseRateAfterTaxs = totalPurchaseRateAfterTaxs + PurchaseRateaAfterTaxs;
guest = guest + curguest_rate;
Prch_Rate_ATX = Prch_Rate_ATX + Prch_Rate_AT;
}
}
else
{
total = total + currate;
totalPurchaseRateAfterTaxs = totalPurchaseRateAfterTaxs + PurchaseRateaAfterTaxs;
guest = guest + curguest_rate;
Prch_Rate_ATX = Prch_Rate_ATX + Prch_Rate_AT;
}
}
currentDate = currentDate.AddDays(1);
}
decimal CalcTotalTax = Math.Round((total * tax) / 100);
decimal CalcGuestTax = Math.Round((guest * tax) / 100);
//reduce tax from supplier rate
SellRateAfterTax = total;
total = Math.Round(total / (1 + (tax / 100)));
guest = Math.Round(guest / (1 + (tax / 100)));
totalPurchaseRateAfterTaxs = Math.Round(totalPurchaseRateAfterTaxs / (1 + (tax / 100)));
SellRateBeforeTax = totalPurchaseRateAfterTaxs;
PurchaseRateBeforeTax = total;
PurchaseRateAfterTax = Prch_Rate_ATX;
//calculate corp discount and SB Markup here..
//SB Markup
decimal SBMarkup_rate = 0;
decimal SBMarkup_guest = 0;
if (days < CLayer.Rates.LONG_TERM_DAYS)
{
if (isCorporate)
{
SBMarkup_rate = Math.Round(total * ((decimal)rc.B2BShortTerm) / 100);
SBMarkup_guest = Math.Round(guest * ((decimal)rc.B2BShortTerm) / 100);
//first day charge
//diff rates calculation
for (int ai = 0; ai < bookingRates.Count; ai++)
{
bookingRates[ai].DayTotalCharge = bookingRates[ai].DayCharge + Math.Round(bookingRates[ai].DayCharge * rc.B2BShortTerm / 100);
bookingRates[ai].DayTotalGuestCharge = bookingRates[ai].DayGuestCharge + Math.Round(bookingRates[ai].DayGuestCharge * rc.B2BShortTerm / 100);
}
}
else
{
if (TAMInventoryAPIID == 4 || TAMInventoryAPIID == 5)
{
SBMarkup_rate = Math.Round(total - totalPurchaseRateAfterTaxs);
SBMarkup_guest = Math.Round(guest * ((decimal)rc.B2CShortTerm) / 100);
}
else
{
SBMarkup_rate = Math.Round(total * ((decimal)rc.B2CShortTerm) / 100);
SBMarkup_guest = Math.Round(guest * ((decimal)rc.B2CShortTerm) / 100);
}
////first day charge
for (int ai = 0; ai < bookingRates.Count; ai++)
{
bookingRates[ai].DayTotalCharge = bookingRates[ai].DayCharge + Math.Round(bookingRates[ai].DayCharge * rc.B2CShortTerm / 100);
bookingRates[ai].DayTotalGuestCharge = bookingRates[ai].DayGuestCharge + Math.Round(bookingRates[ai].DayGuestCharge * rc.B2CShortTerm / 100);
}
}
}
else
{
if (isCorporate)
{
SBMarkup_rate = Math.Round(total * ((decimal)rc.B2BLongTerm) / 100);
SBMarkup_guest = Math.Round(guest * ((decimal)rc.B2BLongTerm) / 100);
//first day charge
//diff rates calc
for (int ai = 0; ai < bookingRates.Count; ai++)
{
bookingRates[ai].DayTotalCharge = bookingRates[ai].DayTotalCharge + Math.Round(bookingRates[ai].DayCharge * rc.B2BLongTerm / 100);
bookingRates[ai].DayTotalGuestCharge = bookingRates[ai].DayTotalGuestCharge + Math.Round(bookingRates[ai].DayGuestCharge * rc.B2BLongTerm / 100);
}
}
else
{
if (TAMInventoryAPIID == 4 || TAMInventoryAPIID == 5)
{
SBMarkup_rate = Math.Round(total - totalPurchaseRateAfterTaxs);
SBMarkup_guest = Math.Round(guest * ((decimal)rc.B2CLongTerm) / 100);
}
else
{
SBMarkup_rate = Math.Round(total * ((decimal)rc.B2CLongTerm) / 100);
SBMarkup_guest = Math.Round(guest * ((decimal)rc.B2CLongTerm) / 100);
}
//diff rates calc
for (int ai = 0; ai < bookingRates.Count; ai++)
{
bookingRates[ai].DayTotalCharge = bookingRates[ai].DayTotalCharge + Math.Round(bookingRates[ai].DayCharge * rc.B2CLongTerm / 100);
bookingRates[ai].DayTotalGuestCharge = bookingRates[ai].DayTotalGuestCharge + Math.Round(bookingRates[ai].DayGuestCharge * rc.B2CLongTerm / 100);
}
}
}
//Corporate Discount
decimal add_discount = 0;
decimal base_discount = 0;
decimal guest_add_discount = 0;
decimal guest_base_discount = 0;
if (isCorporate)
{
if (days < CLayer.Rates.LONG_TERM_DAYS)
{
if (dicnt.ShortTermRate > 0)
{
add_discount = Math.Round(total * (decimal)dicnt.ShortTermRate / 100);
guest_add_discount = Math.Round(guest * (decimal)dicnt.ShortTermRate / 100);
//first day charge
//individual rates
for (int ai = 0; ai < bookingRates.Count; ai++)
{
bookingRates[ai].DayTotalCharge = bookingRates[ai].DayTotalCharge - Math.Round(bookingRates[ai].DayCharge * dicnt.ShortTermRate / 100);
bookingRates[ai].DayTotalGuestCharge = bookingRates[ai].DayTotalGuestCharge - Math.Round(bookingRates[ai].DayGuestCharge * dicnt.ShortTermRate / 100);
}
}
if (dicnt.BaseShortTerm > 0)
{
base_discount = Math.Round(total * (decimal)dicnt.BaseShortTerm / 100);
guest_base_discount = Math.Round(guest * (decimal)dicnt.BaseShortTerm / 100);
//first day charge
for (int ai = 0; ai < bookingRates.Count; ai++)
{
bookingRates[ai].DayTotalCharge = bookingRates[ai].DayTotalCharge - Math.Round(bookingRates[ai].DayCharge * dicnt.BaseShortTerm / 100, 2);
bookingRates[ai].DayTotalGuestCharge = bookingRates[ai].DayTotalGuestCharge - Math.Round(bookingRates[ai].DayGuestCharge * dicnt.BaseShortTerm / 100, 2);
}
}
}
else
{
if (dicnt.LongTermRate > 0)
{
add_discount = Math.Round(total * (decimal)dicnt.LongTermRate / 100);
guest_add_discount = Math.Round(guest * (decimal)dicnt.LongTermRate / 100);
//first day charge
//diff rates calc
for (int ai = 0; ai < bookingRates.Count; ai++)
{
bookingRates[ai].DayTotalCharge = bookingRates[ai].DayTotalCharge - Math.Round(bookingRates[ai].DayCharge * dicnt.LongTermRate / 100);
bookingRates[ai].DayTotalGuestCharge = bookingRates[ai].DayTotalGuestCharge - Math.Round(bookingRates[ai].DayGuestCharge * dicnt.LongTermRate / 100);
}
}
if (dicnt.BaseLongTerm > 0)
{
base_discount = Math.Round(total * (decimal)dicnt.BaseLongTerm / 100);
guest_add_discount = Math.Round(guest * (decimal)dicnt.BaseLongTerm / 100);
//first day charge
//diff rates calc
for (int ai = 0; ai < bookingRates.Count; ai++)
{
bookingRates[ai].DayTotalCharge = bookingRates[ai].DayTotalCharge - Math.Round(bookingRates[ai].DayCharge * dicnt.BaseLongTerm / 100);
bookingRates[ai].DayTotalGuestCharge = bookingRates[ai].DayTotalGuestCharge - Math.Round(bookingRates[ai].DayGuestCharge * dicnt.BaseLongTerm / 100);
}
}
}
}
decimal suppRate = total;
decimal suppGuestRate = guest;
if (TAMInventoryAPIID == 4 || TAMInventoryAPIID == 5)
{
total = Math.Round(total);
guest = Math.Round(guest + SBMarkup_guest - guest_add_discount - guest_base_discount);
}
else
{
total = Math.Round(total + SBMarkup_rate - add_discount - base_discount);
guest = Math.Round(guest + SBMarkup_guest - guest_add_discount - guest_base_discount);
}
//find tax on total amount and guest rate
decimal totaltax = (total * tax) / 100;
decimal guesttax = (guest * tax) / 100;
result.Add(new CLayer.Rates()
{
SupplierRate = suppRate + CalcTotalTax,
SupplierGuestRate = guest + CalcGuestTax,
Amount = total,
GuestRate = guest,
AccommodationId = id,
NoofAcc = inventory,
RateChanges = bookingRates, //FirstDayCharge = fctotal,
SBMarkup = SBMarkup_rate, //(SBMarkup_rate + SBMarkup_guest),
SBGuestMarkup = SBMarkup_guest,
CorpDiscount = (add_discount + base_discount),
CorpGuestDiscount = (guest_add_discount + guest_base_discount),
AppliedOffers = appliedOffers,
TotalRateTax = totaltax,
TotalGuestTax = guesttax,
PurchaseRateAfterTax = PurchaseRateAfterTax,
SellRateBeforeTax = PurchaseRateBeforeTax,
PurchaseRateBeforeTax = SellRateBeforeTax,
SellRateAfterTax = SellRateAfterTax
});
}
return(result);
}
