public CLayer.Accommodation Get(long AccommodationId)
{
CLayer.Accommodation result = null;
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
param.Add(Connection.GetParameter("pAccommodationId", DataPlug.DataType._BigInt, AccommodationId));
DataTable dt = Connection.GetTable("accommodation_Get", param);
if (dt.Rows.Count > 0)
{
result = new CLayer.Accommodation()
{
AccommodationId = Connection.ToLong(dt.Rows[0]["AccommodationId"]),
AccommodationTypeId = Connection.ToInteger(dt.Rows[0]["AccommodationTypeId"]),
StayCategoryId = Connection.ToInteger(dt.Rows[0]["StayCategoryId"]),
AccommodationCount = Connection.ToInteger(dt.Rows[0]["AccommodationCount"]),
PropertyId = Connection.ToLong(dt.Rows[0]["PropertyId"]),
Description = Connection.ToString(dt.Rows[0]["Description"]),
Location = Connection.ToString(dt.Rows[0]["Location"]),
MaxNoOfPeople = Connection.ToInteger(dt.Rows[0]["MaxNoOfPeople"]),
MaxNoOfChildren = Connection.ToInteger(dt.Rows[0]["MaxNoOfChildren"]),
MinNoOfPeople = Connection.ToInteger(dt.Rows[0]["MinNoOfPeople"]),
BedRooms = Connection.ToInteger(dt.Rows[0]["BedRooms"]),
Area = Connection.ToDecimal(dt.Rows[0]["Area"]),
TotalAccommodations = Connection.ToInteger(dt.Rows[0]["TotalAccommodations"]),
Status = Connection.ToInteger(dt.Rows[0]["Status"])
};
}
return(result);
}
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 List <CLayer.Rates> GetCalcDailyRates(long accommodationId)
{
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
List <CLayer.Rates> result = new List <CLayer.Rates>();
param.Add(Connection.GetParameter("pAccommodationId", DataPlug.DataType._BigInt, accommodationId));
DataTable dt = Connection.GetTable("ratesaccomodation_GetCalcDailyRates", param);
CLayer.Rates rt1;
foreach (DataRow dr in dt.Rows)
{
rt1 = new CLayer.Rates();
rt1.RateFor = Connection.ToInteger(dr["RateFor"]);
rt1.RateId = Connection.ToLong(dr["RateId"]);
rt1.AccommodationId = accommodationId;
rt1.DailyRate = Connection.ToDecimal(dr["DailyRate"]);
rt1.CalcDailyRate = Connection.ToDecimal(dr["CalcDailyRate"]);
rt1.GuestRate = Connection.ToDecimal(dr["GuestRate"]);
rt1.LongTermRate = Connection.ToDecimal(dr["LongTermRate"]);
rt1.MonthlyRate = Connection.ToDecimal(dr["MonthlyRate"]);
rt1.WeeklyRate = Connection.ToDecimal(dr["WeeklyRate"]);
result.Add(rt1);
}
return(result);
}
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 CLayer.B2BApprovers GetApproverUsers(long userId, long BookingID)
{
string sql = " SELECT b.userid,IFNULL(a.approver_id,0) AS b2b_approver_id,a.approver_order AS Approver_Order,IFNULL(ba.approval_status,0) AS ApprovalStatus,(SELECT CONCAT(Firstname,' ',lastname) FROM `user` WHERE userid=a.approver_id) AS UserName,(SELECT email FROM `user` WHERE userid=a.approver_id) AS approveremail,(SELECT passwordhash FROM `user` WHERE userid=a.approver_id) AS passwordhash " +
" FROM b2b_approvers a INNER JOIN USER b ON a.user_id=b.userid AND a.user_id=" + userId.ToString() + "" +
" LEFT JOIN booking_approvals ba ON ba.approver_id = a.approver_id AND ba.booking_id = " + BookingID.ToString() + " LIMIT 1";
//string sql = " SELECT b.userid,IFNULL(a.approver_id, 0) AS b2b_approver_id, a.approver_order AS Approver_Order,IFNULL(ba.approval_status, 0) AS ApprovalStatus,(SELECT CONCAT(Firstname, ' ', lastname) FROM `user` WHERE userid = a.approver_id) AS UserName,(SELECT email FROM `user` WHERE userid = a.approver_id) AS approveremail,(SELECT passwordhash FROM `user` WHERE userid = a.approver_id) AS passwordhash," +
// " b1.approvedstatus FROM b2b_approvers a" +
// " INNER JOIN USER b ON a.user_id = b.userid AND a.user_id = " + userId.ToString() + "" +
// " LEFT JOIN booking_approvals ba ON ba.approver_id = a.approver_id AND ba.booking_id = " + BookingID.ToString() + "" +
// " LEFT JOIN booking b1 ON ba.booking_id = b1.bookingid WHERE b1.approvedstatus != 2 LIMIT 1";
DataTable dt = Connection.GetSQLTable(sql);
CLayer.B2BApprovers objResult = new CLayer.B2BApprovers();
if (dt != null && dt.Rows.Count > 0)
{
objResult.b2b_approver_id = Connection.ToLong(dt.Rows[0]["b2b_approver_id"]);
objResult.user_id = Connection.ToLong(dt.Rows[0]["userid"]);
objResult.approver_id = Connection.ToLong(dt.Rows[0]["b2b_approver_id"]);
objResult.approver_order = Connection.ToInteger(dt.Rows[0]["Approver_Order"]);
objResult.created_by = Connection.ToLong(dt.Rows[0]["b2b_approver_id"]);
// objResult.created_date = Connection.ToDate(dt.Rows[0]["created_date"]);
objResult.approver_email = Connection.ToString(dt.Rows[0]["approveremail"]);
objResult.approver_password = Connection.ToString(dt.Rows[0]["passwordhash"]);
objResult.username = Connection.ToString(dt.Rows[0]["UserName"]);
objResult.status = Connection.ToInteger(dt.Rows[0]["ApprovalStatus"]);
}
return(objResult);
}
public List <CLayer.Tax> GetOnStatus(CLayer.ObjectStatus.StatusType status)
{
List <CLayer.Tax> result = new List <CLayer.Tax>();
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
param.Add(Connection.GetParameter("pStatus", DataPlug.DataType._Int, (int)status));
DataTable dt = Connection.GetTable("tax_GetOnStatus", null);
foreach (DataRow dr in dt.Rows)
{
result.Add(new CLayer.Tax()
{
TaxId = Connection.ToInteger(dt.Rows[0]["TaxId"]),
Title = Connection.ToString(dt.Rows[0]["Title"]),
Rate = Connection.ToDecimal(dt.Rows[0]["Title"]),
CountryId = Connection.ToInteger(dt.Rows[0]["CountryId"]),
StateId = Connection.ToInteger(dt.Rows[0]["StateId"]),
Description = Connection.ToString(dt.Rows[0]["Description"]),
Notes = Connection.ToString(dt.Rows[0]["Notes"]),
IsStandard = Connection.ToBoolean(dt.Rows[0]["IsStandard"]),
Status = Connection.ToInteger(dt.Rows[0]["Status"])
});
}
return(result);
}
public List <CLayer.Tax> GetAllForProperty(long propertyId, DateTime checkIn, DateTime bookingDate)
{
List <CLayer.Tax> result = new List <CLayer.Tax>();
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
param.Add(Connection.GetParameter("pPropertyId", DataPlug.DataType._BigInt, propertyId));
param.Add(Connection.GetParameter("pCheckIn", DataPlug.DataType._Date, checkIn));
param.Add(Connection.GetParameter("pBooking", DataPlug.DataType._Date, bookingDate));
DataTable dt = Connection.GetTable("tax_GetAllByProperty", param);
foreach (DataRow dr in dt.Rows)
{
result.Add(new CLayer.Tax()
{
TaxId = Connection.ToInteger(dr["TaxId"]),
TaxTitleId = Connection.ToInteger(dr["TaxTitleId"]),
Rate = Connection.ToDecimal(dr["Rate"]),
CountryId = Connection.ToInteger(dr["CountryId"]),
// Country = Connection.ToString(dr["CountryName"]),
StateId = Connection.ToInteger(dr["StateId"]),
// State = Connection.ToString(dr["StateName"]),
CityId = Connection.ToInteger(dr["CityId"]),
// City = Connection.ToString(dr["CityName"]),
Notes = Connection.ToString(dr["Notes"]),
Status = Connection.ToInteger(dr["Status"]),
StartDate = Connection.ToDate(dr["StartDate"]),
EndDate = Connection.ToDate(dr["EndDate"]),
OnDate = Connection.ToInteger(dr["OnDate"]),
OnTotalAmount = Connection.ToBoolean(dr["OnTotalAmount"]),
PriceUpto = Connection.ToDecimal(dr["PriceUpto"]),
Unlimited = Connection.ToBoolean(dr["UnlimitedPrice"])
});
}
return(result);
}
public List <CLayer.B2B> GetAllSupplier(long userId, int userType)
{
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
param.Add(Connection.GetParameter("pB2bId", DataPlug.DataType._BigInt, userId));
param.Add(Connection.GetParameter("pType", DataPlug.DataType._BigInt, userType));
DataTable dt = Connection.GetTable("supplier_GetAll", param);
List <CLayer.B2B> result = new List <CLayer.B2B>();
foreach (DataRow dr in dt.Rows)
{
result.Add(new CLayer.B2B()
{
B2BId = Connection.ToLong(dr["B2BId"]),
Name = Connection.ToString(dr["Name"]),
MarkupPercent = Connection.ToDecimal(dr["MarkupPercent"]),
CommissionPercent = Connection.ToDecimal(dr["CommissionPercent"]),
UserCode = Connection.ToString(dr["UserCode"]),
ServiceTaxRegNo = Connection.ToString(dr["ServiceTaxRegNo"]),
VATRegNo = Connection.ToString(dr["VATRegNo"]),
UserId = Connection.ToLong(dr["UserId"]),
Email = Connection.ToString(dr["Email"]),
UserType = Connection.ToInteger(dr["UserType"]),
RequestStatus = Connection.ToInteger(dr["RequestStatus"]),
PropertyDescription = Connection.ToString(dr["PropertyDescription"]),
AvailableProperties = Connection.ToInteger(dr["AvailableProperties"])
});
}
return(result);
}
public CLayer.B2B GetbookingcreditforCorte(long b2bId)
{
CLayer.B2B b2b = null;
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
param.Add(Connection.GetParameter("pB2BId", DataPlug.DataType._BigInt, b2bId));
DataTable dt = Connection.GetTable("b2bcorporate_Getbookingcredits", param);
if (dt.Rows.Count > 0)
{
b2b = new CLayer.B2B();
b2b.B2BId = Connection.ToLong(dt.Rows[0]["B2BId"]);
b2b.Name = Connection.ToString(dt.Rows[0]["Name"]);
b2b.UserCode = Connection.ToString(dt.Rows[0]["UserCode"]);
b2b.MarkupPercent = Connection.ToDecimal(dt.Rows[0]["MarkupPercent"]);
b2b.CommissionPercent = Connection.ToDecimal(dt.Rows[0]["CommissionPercent"]);
b2b.ServiceTaxRegNo = Connection.ToString(dt.Rows[0]["ServiceTaxRegNo"]);
b2b.VATRegNo = Connection.ToString(dt.Rows[0]["VATRegNo"]);
b2b.MaximumStaff = Connection.ToInteger(dt.Rows[0]["MaximumStaff"]);
b2b.RequestStatus = Connection.ToInteger(dt.Rows[0]["RequestStatus"]);
b2b.Email = Connection.ToString(dt.Rows[0]["Email"]);
b2b.UserId = Connection.ToLong(dt.Rows[0]["UserId"]);
b2b.CompanyRegNo = Connection.ToString(dt.Rows[0]["CompanyRegNo"]);
b2b.UserType = Connection.ToInteger(dt.Rows[0]["UserType"]);
b2b.PropertyDescription = Connection.ToString(dt.Rows[0]["PropertyDescription"]);
b2b.AvailableProperties = Connection.ToInteger(dt.Rows[0]["AvailableProperties"]);
b2b.PANNo = Connection.ToString(dt.Rows[0]["PANNo"]);
b2b.ContactDesignation = Connection.ToString(dt.Rows[0]["ContactDesignation"]);
b2b.IsCreditBooking = Connection.ToInteger(dt.Rows[0]["IsCreditBooking"]);
b2b.CreditDays = Connection.ToInteger(dt.Rows[0]["CreditDays"]);
b2b.CreditAmount = Connection.ToDecimal(dt.Rows[0]["CreditAmount"]);
b2b.TotalCreditAmount = Connection.ToDecimal(dt.Rows[0]["TotalCreditAmount"]);
}
return(b2b);
}
public List <CLayer.OfflineBooking> SearchPaymentCustomerList(string name, int start, int limit)
{
List <CLayer.OfflineBooking> users = new List <CLayer.OfflineBooking>();
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
param.Add(Connection.GetParameter("pName", DataPlug.DataType._Varchar, name));
param.Add(Connection.GetParameter("pStart", DataPlug.DataType._Int, start));
param.Add(Connection.GetParameter("pLimit", DataPlug.DataType._Int, limit));
DataSet ds = Connection.GetDataSet("SP_SearchPaymentListToCustomer", param);
foreach (DataRow dr in ds.Tables[1].Rows)
{
users.Add(new CLayer.OfflineBooking()
{
CustomerId = Connection.ToLong(dr["b2bid"]),
CustomerName = Connection.ToString(dr["customername"]),
CustomerEmail = Connection.ToString(dr["Email"]),
CustomerAddress = Connection.ToString(dr["Address"]),
CustomerMobile = Connection.ToString(dr["Mobile"]),
CustomerCountry = Connection.ToInteger(dr["countryname"]),
CustomerState = Connection.ToInteger(dr["statename"]),
CustomerCity = Connection.ToInteger(dr["city"]),
CustomerCityname = Connection.ToString(dr["cityname"]),
UserType = Connection.ToLong(dr["UserType"]),
//temp.CustomerType = Connection.ToInteger(dr["UserType"]);
CategoryType = Connection.ToString(dr["UserTableCat"]),
ZipCode = Connection.ToString(dr["ZipCode"]),
NoInvoiceMail = Connection.ToBoolean(dr["NoInvoiceMail"]),
TotalRows = Connection.ToLong(ds.Tables[0].Rows[0]["NumberOfRows"])
});
}
return(users);
}
public List <CLayer.Property> Searchpropertylistaftersup(string name)
{
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
param.Add(Connection.GetParameter("pName", DataPlug.DataType._Varchar, name));
DataTable dt = Connection.GetTable("b2b_Searchpropertylistaftersup", param);
List <CLayer.Property> result = new List <CLayer.Property>();
CLayer.Property temp;
foreach (DataRow dr in dt.Rows)
{
temp = new CLayer.Property();
temp.Title = Connection.ToString(dr["Title"]);
temp.PropertyId = Connection.ToLong(dr["PropertyId"]);
temp.BusinessName = Connection.ToString(dr["name"]);
temp.Email = Connection.ToString(dr["Email"]);
temp.Address = Connection.ToString(dr["address"]);
temp.SupplierName = Connection.ToString(dr["FirstName"]);
temp.Mobile = Connection.ToString(dr["Mobile"]);
temp.Country = Connection.ToInteger(dr["Country"]);
temp.State = Connection.ToInteger(dr["State"]);
temp.CityId = Connection.ToInteger(dr["CityId"]);
temp.OwnerId = Connection.ToInteger(dr["OwnerId"]);
result.Add(temp);
}
return(result);
}
public List <CLayer.OfflineBooking> SearchcustomerListlist(string name)
{
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
param.Add(Connection.GetParameter("pName", DataPlug.DataType._Varchar, name));
DataTable dt = Connection.GetTable("b2b_SearchcustomerListforofflinebook", param);
List <CLayer.OfflineBooking> result = new List <CLayer.OfflineBooking>();
CLayer.OfflineBooking temp;
foreach (DataRow dr in dt.Rows)
{
temp = new CLayer.OfflineBooking();
temp.CustomerId = Connection.ToLong(dr["b2bid"]);
temp.CustomerName = Connection.ToString(dr["customername"]);
temp.CustomerEmail = Connection.ToString(dr["Email"]);
temp.CustomerAddress = Connection.ToString(dr["Address"]);
temp.CustomerMobile = Connection.ToString(dr["Mobile"]);
temp.CustomerCountry = Connection.ToInteger(dr["countryname"]);
temp.CustomerState = Connection.ToInteger(dr["statename"]);
temp.CustomerCity = Connection.ToInteger(dr["city"]);
temp.CustomerCityname = Connection.ToString(dr["cityname"]);
temp.UserType = Connection.ToLong(dr["UserType"]);
//temp.CustomerType = Connection.ToInteger(dr["UserType"]);
temp.CategoryType = Connection.ToString(dr["UserTableCat"]);
temp.ZipCode = Connection.ToString(dr["ZipCode"]);
temp.NoInvoiceMail = Connection.ToBoolean(dr["NoInvoiceMail"]);
result.Add(temp);
}
return(result);
}
public List <CLayer.Accommodation> GetAllForOwnerProperty(long propertyId, int rateType)
{
List <CLayer.Accommodation> result = new List <CLayer.Accommodation>();
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
param.Add(Connection.GetParameter("pPropertyId", DataPlug.DataType._BigInt, propertyId));
param.Add(Connection.GetParameter("pType", DataPlug.DataType._Int, rateType));
DataTable dt = Connection.GetTable("accommodation_GetAllForProperty", param);
foreach (DataRow dr in dt.Rows)
{
result.Add(new CLayer.Accommodation()
{
AccommodationId = Connection.ToLong(dr["AccommodationId"]),
Status = Connection.ToInteger(dr["Status"]),
StayCategory = Connection.ToString(dr["Category"]),
AccommodationType = Connection.ToString(dr["AType"]),
AccommodationCount = Connection.ToInteger(dr["AccommodationCount"]),
MaxNoOfPeople = Connection.ToInteger(dr["MaxNoOfPeople"]),
MaxNoOfChildren = Connection.ToInteger(dr["MaxNoOfChildren"]),
MinNoOfPeople = Connection.ToInteger(dr["MinNoOfPeople"]),
AccommodationTypeId = Connection.ToInteger(dr["AccommodationTypeId"]),
StayCategoryId = Connection.ToInteger(dr["StayCategoryId"]),
PropertyId = Connection.ToLong(dr["PropertyId"]),
Description = Connection.ToString(dr["Description"]),
// Location = Connection.ToString(dr["Location"]),
BedRooms = Connection.ToInteger(dr["BedRooms"]),
Area = Connection.ToDecimal(dr["Area"]),
Rate = Connection.ToDecimal(dr["Rate"])
});
}
return(result);
}
public long Save_Amadeus(CLayer.Accommodation data)
{
try
{
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
param.Add(Connection.GetParameter("pAccommodationId", DataPlug.DataType._BigInt, data.AccommodationId));
param.Add(Connection.GetParameter("pAccommodationTypeId", DataPlug.DataType._Int, data.AccommodationTypeId));
param.Add(Connection.GetParameter("pStayCategoryId", DataPlug.DataType._Int, data.StayCategoryId));
param.Add(Connection.GetParameter("pAccommodationCount", DataPlug.DataType._Int, data.AccommodationCount));
param.Add(Connection.GetParameter("pPropertyId", DataPlug.DataType._BigInt, data.PropertyId));
param.Add(Connection.GetParameter("pDescription", DataPlug.DataType._Text, data.Description));
//param.Add(Connection.GetParameter("pLocation", DataPlug.DataType._Varchar, data.Location));
param.Add(Connection.GetParameter("pMaxNoOfPeople", DataPlug.DataType._Int, data.MaxNoOfPeople));
param.Add(Connection.GetParameter("pMaxNoOfChildren", DataPlug.DataType._Int, data.MaxNoOfChildren));
param.Add(Connection.GetParameter("pMinNoOfPeople", DataPlug.DataType._Int, data.MinNoOfPeople));
param.Add(Connection.GetParameter("pBedRooms", DataPlug.DataType._Int, data.BedRooms));
param.Add(Connection.GetParameter("pArea", DataPlug.DataType._Decimal, data.Area));
param.Add(Connection.GetParameter("pStatus", DataPlug.DataType._Int, data.Status));
param.Add(Connection.GetParameter("pUpdatedBy", DataPlug.DataType._BigInt, data.UpdatedById));
param.Add(Connection.GetParameter("pTotalAccommodations", DataPlug.DataType._Int, data.TotalAccommodations));
param.Add(Connection.GetParameter("pRoomType", DataPlug.DataType._Varchar, data.RoomType));
param.Add(Connection.GetParameter("pRoomTypeCode", DataPlug.DataType._Varchar, data.RoomTypeCode));
param.Add(Connection.GetParameter("pSourceofBusiness", DataPlug.DataType._Varchar, data.SourceofBusiness));
param.Add(Connection.GetParameter("pBookingCode", DataPlug.DataType._Varchar, data.BookingCode));
param.Add(Connection.GetParameter("pRatePlanCode", DataPlug.DataType._Varchar, data.RatePlanCode));
param.Add(Connection.GetParameter("pRoomStayRPH", DataPlug.DataType._Varchar, data.RoomStayRPH));
object result = Connection.ExecuteQueryScalar("accommodation_amadeus_save", param);
return(Connection.ToInteger(result));
}
catch (Exception ex22)
{
return(0);
}
}
public List <CLayer.Rooms> GetAll(long propertyid)
{
List <CLayer.Rooms> result = new List <CLayer.Rooms>();
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
param.Add(Connection.GetParameter("pPropertyId", DataPlug.DataType._BigInt, propertyid));
DataTable dt = Connection.GetTable("room_GetOnProperty", param);
foreach (DataRow dr in dt.Rows)
{
result.Add(new CLayer.Rooms()
{
RoomId = Connection.ToLong(dr["RoomId"]),
RoomTypeId = Connection.ToInteger(dr["RoomTypeId"]),
RoomType = Connection.ToString(dr["Title"]),
RoomCount = Connection.ToInteger(dr["RoomCount"]),
PropertyId = Connection.ToLong(dr["PropertyId"]),
Description = Connection.ToString(dr["Description"])
});
}
RoomType rt = new RoomType();
List <CLayer.RoomType> types = rt.GetAll();
if (result.Count() < types.Count())
{
result.Add(new CLayer.Rooms());
}
return(result);
}
public List <CLayer.User> GetAllCorporateUsers(long corporateId)
{
List <CLayer.User> result = new List <CLayer.User>();
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
param.Add(Connection.GetParameter("pCorporateId", DataPlug.DataType._BigInt, corporateId));
param.Add(Connection.GetParameter("pDeleteStatus", DataPlug.DataType._Int, CLayer.ObjectStatus.StatusType.Deleted));
//corporate_GetUsers
DataTable dt = Connection.GetTable("corporate_GetUsers", param);
CLayer.User temp;
foreach (DataRow dr in dt.Rows)
{
temp = new CLayer.User()
{
Salutation = Connection.ToString(dr["Salutation"]),
Email = Connection.ToString(dr["Email"]),
FirstName = Connection.ToString(dr["Firstname"]),
LastName = Connection.ToString(dr["Lastname"]),
Status = Connection.ToInteger(dr["Status"]),
UserId = Connection.ToLong(dr["UserId"]),
UserTypeId = Connection.ToInteger(dr["CorpUserType"]),
AssistedBookingFlag = Connection.ToInteger(dr["AssistedBooking_Flag"])//corporate user type :admin, staff
};
temp.Name = temp.Salutation + " " + temp.FirstName + " " + temp.LastName;
result.Add(temp);
}
return(result);
}
public List <CLayer.Tax> GetAll()
{
List <CLayer.Tax> result = new List <CLayer.Tax>();
DataTable dt = Connection.GetTable("tax_GetAll", null);
foreach (DataRow dr in dt.Rows)
{
result.Add(new CLayer.Tax()
{
TaxId = Connection.ToInteger(dr["TaxId"]),
Title = Connection.ToString(dr["Title"]),
TaxTitleId = Connection.ToInteger(dr["TaxTitleId"]),
Rate = Connection.ToDecimal(dr["Rate"]),
CountryId = Connection.ToInteger(dr["CountryId"]),
Country = Connection.ToString(dr["CountryName"]),
StateId = Connection.ToInteger(dr["StateId"]),
State = Connection.ToString(dr["StateName"]),
CityId = Connection.ToInteger(dr["CityId"]),
City = Connection.ToString(dr["CityName"]),
Notes = Connection.ToString(dr["Notes"]),
Status = Connection.ToInteger(dr["Status"]),
StartDate = Connection.ToDate(dr["StartDate"]),
EndDate = Connection.ToDate(dr["EndDate"]),
OnDate = Connection.ToInteger(dr["OnDate"]),
OnTotalAmount = Connection.ToBoolean(dr["OnTotalAmount"]),
PriceUpto = Connection.ToDecimal(dr["PriceUpto"]),
UpdatedBy = Connection.ToLong(dr["UpdatedBy"]),
UpdatedDate = Connection.ToDate(dr["UpdatedDate"]),
Unlimited = Connection.ToBoolean(dr["UnlimitedPrice"])
});
}
return(result);
}
public CLayer.Review Get(long PropertyId, long UserId, long BookingId)
{
CLayer.Review rev = null;
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
param.Add(Connection.GetParameter("pPropertyId", DataPlug.DataType._BigInt, PropertyId));
param.Add(Connection.GetParameter("pUserId", DataPlug.DataType._BigInt, UserId));
param.Add(Connection.GetParameter("pBookingId", DataPlug.DataType._BigInt, BookingId));
DataTable dt = Connection.GetTable("review_Details", param);
if (dt.Rows.Count > 0)
{
rev = new CLayer.Review();
rev.PropertyId = Connection.ToLong(dt.Rows[0]["PropertyId"]);
rev.UserId = Connection.ToLong(dt.Rows[0]["UserId"]);
rev.BookingId = Connection.ToLong(dt.Rows[0]["BookingId"]);
rev.Rating = Connection.ToInteger(dt.Rows[0]["Rating"]);
rev.Status = Connection.ToInteger(dt.Rows[0]["Status"]);
rev.ReviewDate = Connection.ToDate(dt.Rows[0]["ReviewDate"]);
rev.FirstName = Connection.ToString(dt.Rows[0]["FirstName"]);
rev.LastName = Connection.ToString(dt.Rows[0]["LastName"]);
rev.Title = Connection.ToString(dt.Rows[0]["Title"]);
rev.Location = Connection.ToString(dt.Rows[0]["Location"]);
rev.Address = Connection.ToString(dt.Rows[0]["Address"]);
rev.Description = Connection.ToString(dt.Rows[0]["Description"]);
rev.UpdatedBy = Connection.ToLong(dt.Rows[0]["UpdatedBy"]);
rev.UpdatedDate = Connection.ToDate(dt.Rows[0]["UpdatedDate"]);
rev.Recommended = Connection.ToBoolean(dt.Rows[0]["IsRecommended"]);
}
return(rev);
}
public int Save(CLayer.Tax data)
{
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
param.Add(Connection.GetParameter("pTaxId", DataPlug.DataType._Int, data.TaxId));
param.Add(Connection.GetParameter("pTaxTitleId", DataPlug.DataType._Int, data.TaxTitleId));
param.Add(Connection.GetParameter("pRate", DataPlug.DataType._Decimal, data.Rate));
param.Add(Connection.GetParameter("pCountryId", DataPlug.DataType._Int, data.CountryId));
param.Add(Connection.GetParameter("pStateId", DataPlug.DataType._Int, data.StateId));
param.Add(Connection.GetParameter("pCityId", DataPlug.DataType._Int, data.CityId));
param.Add(Connection.GetParameter("pNotes", DataPlug.DataType._Varchar, data.Notes));
param.Add(Connection.GetParameter("pStatus", DataPlug.DataType._Int, data.Status));
param.Add(Connection.GetParameter("pStartDate", DataPlug.DataType._DateTime, data.StartDate));
param.Add(Connection.GetParameter("pEndDate", DataPlug.DataType._DateTime, data.EndDate));
param.Add(Connection.GetParameter("pOnDate", DataPlug.DataType._Int, data.OnDate));
param.Add(Connection.GetParameter("pOnTotalAmount", DataPlug.DataType._Bool, data.OnTotalAmount));
param.Add(Connection.GetParameter("pPriceUpto", DataPlug.DataType._Decimal, data.PriceUpto));
param.Add(Connection.GetParameter("pUpdatedBy", DataPlug.DataType._BigInt, data.UpdatedBy));
param.Add(Connection.GetParameter("pUpdatedDate", DataPlug.DataType._DateTime, data.UpdatedDate));
param.Add(Connection.GetParameter("pUnlimitedPrice", DataPlug.DataType._Bool, data.Unlimited));
object result = Connection.ExecuteQueryScalar("tax_Save", param);
return(Connection.ToInteger(result));
}
public List <CLayer.Address> GetOnUser(long UserId)
{
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
param.Add(Connection.GetParameter("pUserId", DataPlug.DataType._BigInt, UserId));
DataTable dt = Connection.GetTable("address_GetOnUser", param);
List <CLayer.Address> result = new List <CLayer.Address>();
foreach (DataRow dr in dt.Rows)
{
result.Add(new CLayer.Address()
{
AddressId = Connection.ToLong(dr["AddressId"]),
UserId = Connection.ToLong(dr["UserId"]),
CityId = Connection.ToInteger(dr["CityId"]),
AddressText = Connection.ToString(dr["Address"]),
City = Connection.ToString(dr["City"]),
State = Connection.ToInteger(dr["State"]),
CountryId = Connection.ToInteger(dr["Country"]),
ZipCode = Connection.ToString(dr["ZipCode"]),
Phone = Connection.ToString(dr["Phone"]),
Mobile = Connection.ToString(dr["Mobile"]),
AddressType = Connection.ToInteger(dr["Type"])
});
}
return(result);
}
public List <CLayer.Offers> GetForAccommodation(long accommodationId, int activeStatus)
{
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
param.Add(Connection.GetParameter("pActiveStatus", DataPlug.DataType._Int, activeStatus));
param.Add(Connection.GetParameter("pAccommodationId", DataPlug.DataType._BigInt, accommodationId));
DataTable dt = Connection.GetTable("offer_ForAccommodation", param);
List <CLayer.Offers> result = new List <CLayer.Offers>();
CLayer.Offers temp;
foreach (DataRow dr in dt.Rows)
{
temp = new CLayer.Offers();
temp.OfferId = Connection.ToLong(dr["OfferId"]);
temp.OfferTitle = Connection.ToString(dr["OfferTitle"]);
temp.OfferType = Connection.ToInteger(dr["OfferType"]);
temp.RateType = Connection.ToInteger(dr["RateType"]);
temp.StartDate = Connection.ToDate(dr["StartDate"]);
temp.EndDate = Connection.ToDate(dr["EndDate"]);
temp.FreeDays = Connection.ToInteger(dr["FreeDays"]);
temp.NoOfDays = Connection.ToInteger(dr["NoOfDays"]);
temp.Amount = Connection.ToDecimal(dr["Amount"]);
result.Add(temp);
}
return(result);
}
public CLayer.Address GetOnUserId(long UserId, int Addresstype)
{
CLayer.Address result = null;
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
param.Add(Connection.GetParameter("pUserId", DataPlug.DataType._BigInt, UserId));
param.Add(Connection.GetParameter("pType", DataPlug.DataType._Int, Addresstype));
DataTable dt = Connection.GetTable("address_GetOnUserType", param);
if (dt.Rows.Count > 0)
{
result = new CLayer.Address();
result.AddressId = Connection.ToLong(dt.Rows[0]["AddressId"]);
result.UserId = Connection.ToLong(dt.Rows[0]["UserId"]);
result.CityId = Connection.ToInteger(dt.Rows[0]["CityId"]);
result.AddressText = Connection.ToString(dt.Rows[0]["Address"]);
result.City = Connection.ToString(dt.Rows[0]["City"]);
result.State = Connection.ToInteger(dt.Rows[0]["State"]);
result.CountryId = Connection.ToInteger(dt.Rows[0]["Country"]);
result.ZipCode = Connection.ToString(dt.Rows[0]["ZipCode"]);
result.Phone = Connection.ToString(dt.Rows[0]["Phone"]);
result.Mobile = Connection.ToString(dt.Rows[0]["Mobile"]);
result.AddressType = Connection.ToInteger(dt.Rows[0]["Type"]);
}
return(result);
}
public int GetCountLimitOfUsers(long pB2BId)
{
string sql = "SELECT MaximumStaff FROM b2b WHERE B2BId=" + pB2BId;
object obj = Connection.ExecuteSQLQueryScalar(sql);
return(Connection.ToInteger(obj));
}
public CLayer.Address GetPrimaryOnUser(long UserId)
{
CLayer.Address address = null;
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
param.Add(Connection.GetParameter("pUserId", DataPlug.DataType._BigInt, UserId));
DataTable dt = Connection.GetTable("address_GetPrimaryOnUser", param);
if (dt.Rows.Count > 0)
{
address = new CLayer.Address();
address.AddressId = Connection.ToLong(dt.Rows[0]["AddressId"]);
address.UserId = Connection.ToLong(dt.Rows[0]["UserId"]);
address.AddressText = Connection.ToString(dt.Rows[0]["Address"]);
address.CityId = Connection.ToInteger(dt.Rows[0]["CityId"]);
address.City = Connection.ToString(dt.Rows[0]["City"]);
address.State = Connection.ToInteger(dt.Rows[0]["State"]);
address.CountryId = Connection.ToInteger(dt.Rows[0]["Country"]);
address.ZipCode = Connection.ToString(dt.Rows[0]["ZipCode"]);
address.Phone = Connection.ToString(dt.Rows[0]["Phone"]);
address.Mobile = Connection.ToString(dt.Rows[0]["Mobile"]);
address.AddressType = Connection.ToInteger(dt.Rows[0]["Type"]);
}
return(address);
}
public CLayer.B2BApprovers CheckBookingApproversExist(long userId, long BookingID)
{
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
param.Add(Connection.GetParameter("pUserID", DataPlug.DataType._BigInt, userId));
param.Add(Connection.GetParameter("pBookingID", DataPlug.DataType._BigInt, BookingID));
DataTable dt = Connection.GetTable("CheckBookingApproversExists", param);
CLayer.B2BApprovers objResult = new CLayer.B2BApprovers();
if (dt != null && dt.Rows.Count > 0)
{
objResult.b2b_approver_id = Connection.ToLong(dt.Rows[0]["b2b_approver_id"]);
objResult.user_id = Connection.ToLong(dt.Rows[0]["userid"]);
objResult.approver_id = Connection.ToLong(dt.Rows[0]["b2b_approver_id"]);
objResult.approver_order = Connection.ToInteger(dt.Rows[0]["Approver_Order"]);
objResult.created_by = Connection.ToLong(dt.Rows[0]["b2b_approver_id"]);
// objResult.created_date = Connection.ToDate(dt.Rows[0]["created_date"]);
objResult.approver_email = Connection.ToString(dt.Rows[0]["approveremail"]);
objResult.approver_password = Connection.ToString(dt.Rows[0]["passwordhash"]);
objResult.username = Connection.ToString(dt.Rows[0]["UserName"]);
objResult.status = Connection.ToInteger(dt.Rows[0]["ApprovalStatus"]);
}
// return ids;
return(objResult);
}
public List <CLayer.Review> GetpropertyId(long propertyId, int Status)
{
List <CLayer.Review> result = new List <CLayer.Review>();
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
param.Add(Connection.GetParameter("pPropertyId", DataPlug.DataType._BigInt, propertyId));
param.Add(Connection.GetParameter("pStatus", DataPlug.DataType._Int, Status));
DataSet ds = Connection.GetDataSet("review_GetPropertyId", param);
foreach (DataRow dr in ds.Tables[1].Rows)
{
result.Add(new CLayer.Review()
{
PropertyId = Connection.ToLong(dr["PropertyId"]),
UserId = Connection.ToLong(dr["UserId"]),
BookingId = Connection.ToLong(dr["BookingId"]),
Rating = Connection.ToInteger(dr["Rating"]),
Status = Connection.ToInteger(dr["Status"]),
ReviewDate = Connection.ToDate(dr["ReviewDate"]),
FirstName = Connection.ToString(dr["FirstName"]),
LastName = Connection.ToString(dr["LastName"]),
Title = Connection.ToString(dr["Title"]),
Location = Connection.ToString(dr["Location"]),
Address = Connection.ToString(dr["Address"]),
Description = Connection.ToString(dr["Description"]),
BookingItemId = Connection.ToLong(dr["BookingItemId"]),
TotalRows = Connection.ToLong(ds.Tables[0].Rows[0]["NumberOfRows"])
});
}
return(result);
}
public List <CLayer.OfflinePayment> ReportOfflinePayment_GetAll(int status, string searchString, int searchItem, int start, int limit)
{
List <CLayer.OfflinePayment> bookings = new List <CLayer.OfflinePayment>();
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
param.Add(Connection.GetParameter("pStatus", DataPlug.DataType._Int, status));
param.Add(Connection.GetParameter("pSearchString", DataPlug.DataType._Varchar, searchString));
param.Add(Connection.GetParameter("pSearchItem", DataPlug.DataType._Int, searchItem));
param.Add(Connection.GetParameter("pStart", DataPlug.DataType._Int, start));
param.Add(Connection.GetParameter("pLimit", DataPlug.DataType._Int, limit));
DataSet ds = Connection.GetDataSet("Report_OfflinePaymentGetall", param);
foreach (DataRow dr in ds.Tables[1].Rows)
{
bookings.Add(new CLayer.OfflinePayment()
{
LoginUsername = Connection.ToString(dr["LoginUsername"]),
Name = Connection.ToString(dr["PayUserName"]),
Amount = Connection.ToDecimal(dr["PayAmount"]),
Message = Connection.ToString(dr["Message"]),
ReferenceNumber = Connection.ToString(dr["PayUserRefNo"]),
PaymentRefNo = Connection.ToString(dr["PaymentReferenceNo"]),
Mobile = Connection.ToString(dr["Mobile"]),
State = Connection.ToString(dr["State"]),
City = Connection.ToString(dr["City"]),
Email = Connection.ToString(dr["Email"]),
Country = Connection.ToString(dr["CountryName"]),
CreatedDate = Connection.ToDate(dr["CreatedDate"]),
OfflinePaymentStatus = Connection.ToInteger(dr["OfflinePaymentStatus"]),
OfflinePaymentId = Connection.ToInteger(dr["OfflinePaymentId"]),
TotalRows = Connection.ToLong(ds.Tables[0].Rows[0]["NumberOfRows"]),
});
}
return(bookings);
}
public List <CLayer.Review> GetAll(int Start, int Limit)
{
List <CLayer.Review> result = new List <CLayer.Review>();
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
param.Add(Connection.GetParameter("pStart", DataPlug.DataType._Int, Start));
param.Add(Connection.GetParameter("pLimit", DataPlug.DataType._Int, Limit));
DataSet ds = Connection.GetDataSet("review_GetAll", param);
foreach (DataRow dr in ds.Tables[1].Rows)
{
result.Add(new CLayer.Review()
{
PropertyId = Connection.ToLong(dr["PropertyId"]),
UserId = Connection.ToLong(dr["UserId"]),
BookingId = Connection.ToLong(dr["BookingId"]),
Rating = Connection.ToInteger(dr["Rating"]),
Status = Connection.ToInteger(dr["Status"]),
ReviewDate = Connection.ToDate(dr["ReviewDate"]),
FirstName = Connection.ToString(dr["FirstName"]),
LastName = Connection.ToString(dr["LastName"]),
Title = Connection.ToString(dr["Title"]),
Location = Connection.ToString(dr["Location"]),
Address = Connection.ToString(dr["Address"]),
Description = Connection.ToString(dr["Description"]),
UpdatedBy = Connection.ToLong(dr["UpdatedBy"]),
UpdatedDate = Connection.ToDate(dr["UpdatedDate"]),
Recommended = Connection.ToBoolean(dr["IsRecommended"]),
TotalRows = Connection.ToLong(ds.Tables[0].Rows[0]["NumberOfRows"])
});
}
return(result);
}
//**Added by rahul on 04/03/2020
//*This is for getting rates for property Accommodation for API
public List <CLayer.Rates> GetAccommodationRatesForAPI(string AccIds, DateTime checkIn, DateTime checkOut, int noOfDays, CLayer.Role.Roles rateType, CLayer.Role.Roles defaultType)
{
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
param.Add(Connection.GetParameter("pAccs", DataPlug.DataType._Varchar, AccIds));
param.Add(Connection.GetParameter("pCheckIn", DataPlug.DataType._Date, checkIn));
param.Add(Connection.GetParameter("pCheckOut", DataPlug.DataType._Date, checkOut));
param.Add(Connection.GetParameter("pRateType", DataPlug.DataType._Int, rateType));
param.Add(Connection.GetParameter("pDefaultRateType", DataPlug.DataType._Int, (int)defaultType));
param.Add(Connection.GetParameter("pBookingDayCount", DataPlug.DataType._Int, (int)noOfDays));
DataTable dt = Connection.GetTable("accommodation_GetAvailabilityAndRateForAPI", param);
List <CLayer.Rates> result = new List <CLayer.Rates>();
CLayer.Rates rt;
foreach (DataRow dr in dt.Rows)
{
rt = new CLayer.Rates();
rt.AccommodationId = Connection.ToLong(dr["AccommodationId"]);
rt.Amount = Connection.ToDecimal(dr["Amount"]);
rt.GuestRate = Connection.ToDecimal(dr["GuestRate"]);
rt.StartDate = Connection.ToDate(dr["startdate"]);
rt.EndDate = Connection.ToDate(dr["enddate"]);
rt.RateType = Connection.ToInteger(dr["AType"]);
rt.NoofAcc = Connection.ToInteger(dr["noofacc"]);
rt.PurchaseRateAfterTax = Connection.ToDecimal(dr["DailyRate"]);
result.Add(rt);
}
return(result);
}
public CLayer.State Get(int stateId)
{
CLayer.State state = null;
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
param.Add(Connection.GetParameter("pStateId", DataPlug.DataType._Int, stateId));
DataTable dt = Connection.GetTable("state_Get", param);
if (dt.Rows.Count > 0)
{
state = new CLayer.State();
state.StateId = Connection.ToInteger(dt.Rows[0]["StateId"]);
state.Name = Connection.ToString(dt.Rows[0]["Name"]);
state.CountryId = Connection.ToInteger(dt.Rows[0]["CountryId"]);
state.GSTStateCode = Connection.ToString(dt.Rows[0]["GSTStateCode"]);
}
else
{
state = new CLayer.State();
state.StateId = 0;
state.Name = "";
state.CountryId = 0;
state.GSTStateCode = "";
}
return(state);
}