public int UpdateGDSCity(int StateID, string City = "Others")
{
int result = 0;
string sql = "SELECT COUNT(*) FROM City WHERE StateID='" + StateID + "' and name='" + City + "'";
object obj = Connection.ExecuteSQLQueryScalar(sql);
if (Connection.ToInteger(obj) < 1)
{
CLayer.City objCity = new CLayer.City();
objCity.CityId = 0;
objCity.Name = City;
objCity.StateId = StateID;
objCity.ForListing = true;
objCity.Keywords = City;
result = Save(objCity);
}
else
{
sql = "SELECT cityid FROM City WHERE StateID='" + StateID + "' and name='" + City + "'";
obj = Connection.ExecuteSQLQueryScalar(sql);
result = Connection.ToInteger(obj);
}
return(result);
}
public string Getb2bname(long pB2BId)
{
string sql = "SELECT Name FROM b2b WHERE B2BId=" + pB2BId;
object obj = Connection.ExecuteSQLQueryScalar(sql);
return(Connection.ToString(obj));
}
public long GetMaximumDailyEntitlement(long pUserID)
{
string sql = "SELECT ifnull(MaximumDailyEntitlement,0) FROM b2b_user WHERE userid=" + pUserID;
object obj = Connection.ExecuteSQLQueryScalar(sql);
return(Connection.ToLong(obj));
}
public decimal GetTotalTax(long propertyId)
{
string sql = "Select Sum(TaxValue) as val From propertyTax Where PropertyId=" + propertyId.ToString();
object obj = Connection.ExecuteSQLQueryScalar(sql);
return(Connection.ToDecimal(obj));
}
public double GetAgentCommission(long userId)
{
string sql = "Select MarkupPercent From b2b Where b2bId=" + userId.ToString();
object obj = Connection.ExecuteSQLQueryScalar(sql);
return(Connection.ToDouble(obj));
}
public string GetAccommodationTitle(long accommodationId)
{
string sql = "SELECT act.Title FROM accommodation a INNER JOIN accommodationtype act ON a.AccommodationTypeId = act.AccommodationTypeId WHERE a.AccommodationId =" + accommodationId.ToString();
object obj = Connection.ExecuteSQLQueryScalar(sql);
return(Connection.ToString(obj));
}
public string GetBusinessName(long userId)
{
string sql = "Select Name from b2b Where B2bId=" + userId.ToString();
object obj = Connection.ExecuteSQLQueryScalar(sql);
return(Connection.ToString(obj));
}
public string GetGDSCountry(long BookingId)
{
string sql = "SELECT IFNULL(c.name,0) AS CountryName FROM bookingitems bite INNER JOIN country c ON IFNULL(GDSCountry,0)=c.countryid WHERE bite.`BookingId`=" + BookingId.ToString() + " LIMIT 1";
object obj = Connection.ExecuteSQLQueryScalar(sql);
return(Connection.ToString(obj));
}
public string GetRatesApplied(long bookingItemId)
{
string sql = "Select RatesApplied From BookingItems Where BookingItemId=" + bookingItemId.ToString();
object obj = Connection.ExecuteSQLQueryScalar(sql);
return(Connection.ToString(obj));
}
public decimal GetFirstDayCharge(long bookingItemId)
{
string sql = "Select FirstDayCharge from bookingitems where BookingItemId=" + bookingItemId.ToString();
object obj = Connection.ExecuteSQLQueryScalar(sql);
return(Connection.ToDecimal(obj));
}
public decimal GetGrantTotalTaxbyBookingId(long BookingId)
{
string sql = "SELECT SUM(bite.TotalRateTax) FROM bookingitems bite WHERE bite.`BookingId`=" + BookingId.ToString();
object obj = Connection.ExecuteSQLQueryScalar(sql);
return(Connection.ToDecimal(obj));
}
public CLayer.ObjectStatus.StatusType GetStatus(long bookingItemId)
{
string sql = "Select Status from bookingitems where BookingItemId=" + bookingItemId.ToString();
object obj = Connection.ExecuteSQLQueryScalar(sql);
return((CLayer.ObjectStatus.StatusType)Connection.ToInteger(obj));
}
public int GetBookedUserId(long bookingItemId)
{
string sql = "Select b.ByUserId From b.booking on bi.bookingitems on b.BookingId=bi.BookingId Where bi.BookingItemId=" + bookingItemId.ToString();
object obj = Connection.ExecuteSQLQueryScalar(sql);
return(Connection.ToInteger(obj));
}
public long GetCustomBookByItemId(long bookingItemId)
{
string sql = "Select IsCustomBook From BookingItems Where BookingItemId=" + bookingItemId.ToString();
object obj = Connection.ExecuteSQLQueryScalar(sql);
return(Connection.ToLong(obj));
}
public long GetInventoryAPIType(long propertyid)
{
string sql = "Select InventoryAPIType from property where PropertyId = " + propertyid.ToString();
object obj = Connection.ExecuteSQLQueryScalar(sql);
return(Connection.ToLong(obj));
}
public string GetBillingFor(long BookingId)
{
string sql = "SELECT IFNULL(BillingFor,0) AS BillingFor FROM booking WHERE BookingId = " + BookingId.ToString();
object obj = Connection.ExecuteSQLQueryScalar(sql);
return(Connection.ToString(obj));
}
public string GetPropertyTitle(long accommodationId)
{
string sql = "Select p.Title From property p inner join accommodation a on p.PropertyId = a.PropertyId Where a.AccommodationId=" + accommodationId.ToString();
object obj = Connection.ExecuteSQLQueryScalar(sql);
return(Connection.ToString(obj));
}
public long GetAssisted_By(long BookingId)
{
string sql = "SELECT IFNULL(Assisted_By,0) AS Assisted_By FROM booking WHERE BookingId = " + BookingId.ToString();
object obj = Connection.ExecuteSQLQueryScalar(sql);
return(Connection.ToLong(obj));
}
public long getAccommodationCountByStatus(int status, long BookingId)
{
string sql = "SELECT COUNT(bi.AccommodationId) FROM booking b INNER JOIN bookingitems bi ON bi.BookingId=b.BookingId WHERE b.Status=" + status + " AND b.BookingId=" + BookingId;
object obj = Connection.ExecuteSQLQueryScalar(sql);
return(Connection.ToLong(obj));
}
public int GetExternalInventoryReqByBookingId(long BookingId)
{
string sql = "Select count(*) from bookingexternalinventoryrequest where Booking_Id=" + BookingId.ToString();
object obj = Connection.ExecuteSQLQueryScalar(sql);
return(Connection.ToInteger(obj));
}
public long LastApproverID(long userId, long bookingid)
{
string sql = "SELECT approver_id FROM b2b_approvers b2b INNER JOIN booking b ON b2b.user_id = b.byuserid WHERE user_id = " + userId + " AND bookingid = " + bookingid + " ORDER BY approver_order DESC LIMIT 1;";
object obj = Connection.ExecuteSQLQueryScalar(sql);
return(Connection.ToLong(obj));
}
public int GDSKeywordSave(CLayer.Country country)
{
string pKeyWords = country.KeyWords;
string sql = "select keywords from country where countryid=" + country.CountryId + "";
string result = Convert.ToString(Connection.ExecuteSQLQueryScalar(sql));
if (!string.IsNullOrEmpty(result))
{
string[] resultList = result.Split(',');
for (int i = 0; i < resultList.Length; i++)
{
if (resultList[i] != pKeyWords)
{
if (!result.Contains(pKeyWords))
{
country.KeyWords = result + "," + pKeyWords;
}
else
{
country.KeyWords = result;
}
}
}
}
sql = "Update country set keywords='" + country.KeyWords + "' where countryid=" + country.CountryId + "";
object obj = Connection.ExecuteSQLQueryScalar(sql);
return(Connection.ToInteger(obj));
}
public int CountOfUnSubscribed()
{
string sql = "SELECT COUNT(EmailId) AS UnSubscriptionCount FROM subscriptions WHERE UnSubscribed=1";
object obj = Connection.ExecuteSQLQueryScalar(sql);
return(Connection.ToInteger(obj));
}
public string UpdateGDSCountry(string CountryName)
{
string pKeyword = CountryName;
string CountryFromKeyWord = GetCountryWithKeyWords(CountryName);
CountryName = string.IsNullOrEmpty(CountryFromKeyWord) ? CountryName : CountryFromKeyWord;
string sql = "SELECT COUNT(*) FROM country WHERE NAME='" + CountryName + "'";
object obj = Connection.ExecuteSQLQueryScalar(sql);
if (Connection.ToInteger(obj) < 1)
{
CLayer.Country country = new CLayer.Country();
country.Name = CountryName;
country.ForProperty = true;
country.Status = 1;
// country.KeyWords = CountryName;
Save(country);
}
else
{
string sqlc = "SELECT CountryID FROM country WHERE NAME='" + CountryName + "'";
object objc = Connection.ExecuteSQLQueryScalar(sqlc);
CLayer.Country country = new CLayer.Country();
country.CountryId = Connection.ToLong(objc);
country.Name = CountryName;
country.ForProperty = true;
country.Status = 1;
// country.KeyWords = pKeyword;
Save(country);
}
return(CountryName);
}
public int GetCountLimitOfUsers(long pB2BId)
{
string sql = "SELECT MaximumStaff FROM b2b WHERE B2BId=" + pB2BId;
object obj = Connection.ExecuteSQLQueryScalar(sql);
return(Connection.ToInteger(obj));
}
public int GDSSave(CLayer.Country country)
{
string pKeyWords = country.KeyWords;
string sql = "select keywords from country where countryid=" + country.CountryId + "";
string result = Convert.ToString(Connection.ExecuteSQLQueryScalar(sql));
if (!string.IsNullOrEmpty(result))
{
string[] resultList = result.Split(',');
for (int i = 0; i < resultList.Length; i++)
{
if (resultList[i] != pKeyWords)
{
country.KeyWords = result + "," + pKeyWords;
}
}
}
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
param.Add(Connection.GetParameter("pCountryId", DataPlug.DataType._BigInt, country.CountryId));
param.Add(Connection.GetParameter("pName", DataPlug.DataType._Varchar, country.Name));
param.Add(Connection.GetParameter("pCode", DataPlug.DataType._Varchar, country.Code));
param.Add(Connection.GetParameter("pIsDefault", DataPlug.DataType._Bool, country.IsDefault));
param.Add(Connection.GetParameter("pStatus", DataPlug.DataType._Int, country.Status));
param.Add(Connection.GetParameter("pForProperty", DataPlug.DataType._Bool, country.ForProperty));
param.Add(Connection.GetParameter("pKeyWords", DataPlug.DataType._Varchar, country.KeyWords));
object objresult = Connection.ExecuteQueryScalar("GDScountry_Save", param);
return(Connection.ToInteger(objresult));
}
public int GetNoofApprovers(long pUserID)
{
string sql = "SELECT COUNT(*) FROM b2b_approvers WHERE user_id=" + pUserID;
object obj = Connection.ExecuteSQLQueryScalar(sql);
return(Connection.ToInteger(obj));
}
public long GetPropertyId(long accommodationId)
{
string sql = "Select PropertyId From accommodation Where AccommodationId=" + accommodationId.ToString();
object obj = Connection.ExecuteSQLQueryScalar(sql);
return(Connection.ToLong(obj));
}
public long GetCountForBookings(long bookuserid, DateTime FDate, DateTime Tdate)
{
string sql = "Select Count(*) From booking Where PaymentOption = 3 and ( BookingDate >= '" + FDate.ToString("yyyy/MM/dd HH:mm:ss") + "' and BookingDate <= '" + Tdate.ToString("yyyy/MM/dd HH:mm:ss") + "' ) and status = 2 and ByUserId=" + bookuserid.ToString();
object obj = Connection.ExecuteSQLQueryScalar(sql);
return(Connection.ToLong(obj));
}
public int GetStatus(long invoiceId)
{
string sql = "Select ifnull(Status,1) as status From Invoices Where invoiceId=" + invoiceId.ToString();
object obj = Connection.ExecuteSQLQueryScalar(sql);
return(Connection.ToInteger(obj));
}