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 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.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 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 List <CLayer.OfflinePayment> GetOfflinePaymentDetails2(long OfflinePaymentId)
{
List <CLayer.OfflinePayment> result = new List <CLayer.OfflinePayment>();
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
param.Add(Connection.GetParameter("pOfflinePayId", DataPlug.DataType._BigInt, OfflinePaymentId));
DataSet ds = Connection.GetDataSet("OfflinePayment_GetDetails", param);
foreach (DataRow dr in ds.Tables[0].Rows)
{
result.Add(new CLayer.OfflinePayment()
{
//result = new CLayer.OfflinePayment(),
UserId = Connection.ToLong(dr["UserId"]),
Amount = Connection.ToDecimal(dr["Amount"]),
Name = Connection.ToString(dr["Name"]),
Message = Connection.ToString(dr["Message"]),
ReferenceNumber = Connection.ToString(dr["ReferenceNo"]),
PaymentRefNo = Connection.ToString(dr["PaymentReferenceNo"]),
CreatedDate = Connection.ToDate(dr["CreatedDate"]),
Email = Connection.ToString(dr["Email"]),
Mobile = Connection.ToString(dr["Mobile"]),
Address = Connection.ToString(dr["Address"]),
Country = Connection.ToString(dr["CountryName"]),
State = Connection.ToString(dr["StateName"]),
City = Connection.ToString(dr["CityName"]),
CountryCode = Connection.ToString(dr["CountryCode"]),
ZipCode = Connection.ToString(dr["ZipCode"]),
//result.PaymentLinkID = (Guid)(dt.Rows[0]["PaymentLinkGuidId"]);
PaymentLinkID = Connection.ToString(ds.Tables[0].Rows[0]["PaymentLinkGuidId"])
});
}
return(result);
}
public List <CLayer.Accommodation> GetProperties(int Status, string SearchString, int SearchValue, int Start, int Limit)
{
List <CLayer.Accommodation> accommodations = new List <CLayer.Accommodation>();
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("pSearchValue", DataPlug.DataType._Int, SearchValue));
param.Add(Connection.GetParameter("pStart", DataPlug.DataType._Int, Start));
param.Add(Connection.GetParameter("pLimit", DataPlug.DataType._Int, Limit));
DataSet ds = Connection.GetDataSet("offer_PropertyAllList", param);
foreach (DataRow dr in ds.Tables[1].Rows)
{
accommodations.Add(new CLayer.Accommodation()
{
PropertyId = Connection.ToLong(dr["PropertyId"]),
PropertyTitle = Connection.ToString(dr["PropertyTitle"]),
// Status = Connection.ToInteger(dr["Status"]),
Location = Connection.ToString(dr["Location"]),
Address = Connection.ToString(dr["Address"]),
Statename = Connection.ToString(dr["Statename"]),
City = Connection.ToString(dr["City"]),
Ownername = Connection.ToString(dr["Ownername"]),
TotalRows = Connection.ToLong(ds.Tables[0].Rows[0]["NumberOfRows"])
});
}
return(accommodations);
}
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 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.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 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 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 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> 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.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.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 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 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 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 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 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.OfflinePayment GetOfflinePaymentDetails(long OfflinePaymentId)
{
CLayer.OfflinePayment result = null;
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
param.Add(Connection.GetParameter("pOfflinePayId", DataPlug.DataType._BigInt, OfflinePaymentId));
DataTable dt = Connection.GetTable("OfflinePayment_GetDetails", param);
if (dt.Rows.Count > 0)
{
result = new CLayer.OfflinePayment();
result.UserId = Connection.ToLong(dt.Rows[0]["UserId"]);
result.Amount = Connection.ToDecimal(dt.Rows[0]["Amount"]);
result.Name = Connection.ToString(dt.Rows[0]["Name"]);
result.Message = Connection.ToString(dt.Rows[0]["Message"]);
result.ReferenceNumber = Connection.ToString(dt.Rows[0]["ReferenceNo"]);
result.PaymentRefNo = Connection.ToString(dt.Rows[0]["PaymentReferenceNo"]);
result.CreatedDate = Connection.ToDate(dt.Rows[0]["CreatedDate"]);
result.Email = Connection.ToString(dt.Rows[0]["Email"]);
result.Mobile = Connection.ToString(dt.Rows[0]["Mobile"]);
result.Address = Connection.ToString(dt.Rows[0]["Address"]);
result.Country = Connection.ToString(dt.Rows[0]["CountryName"]);
result.State = Connection.ToString(dt.Rows[0]["StateName"]);
result.City = Connection.ToString(dt.Rows[0]["CityName"]);
result.CountryCode = Connection.ToString(dt.Rows[0]["CountryCode"]);
result.ZipCode = Connection.ToString(dt.Rows[0]["ZipCode"]);
//result.PaymentLinkID = (Guid)(dt.Rows[0]["PaymentLinkGuidId"]);
result.PaymentLinkID = Connection.ToString(dt.Rows[0]["PaymentLinkGuidId"]);
}
return(result);
}
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 long SaveInitialCustomerPaymentData(CLayer.OfflinePayment data)
{
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
param.Add(Connection.GetParameter("pUserId", DataPlug.DataType._Int, data.UserId));
param.Add(Connection.GetParameter("pName", DataPlug.DataType._Varchar, data.Name));
param.Add(Connection.GetParameter("pRefNumber", DataPlug.DataType._Varchar, data.ReferenceNumber));
param.Add(Connection.GetParameter("pAmount", DataPlug.DataType._Decimal, data.Amount));
param.Add(Connection.GetParameter("pMessage", DataPlug.DataType._Varchar, data.Message));
param.Add(Connection.GetParameter("pAddress", DataPlug.DataType._Varchar, data.Address));
param.Add(Connection.GetParameter("pEmail", DataPlug.DataType._Varchar, data.Email));
param.Add(Connection.GetParameter("pMobile", DataPlug.DataType._Varchar, data.Mobile));
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("pCityName", DataPlug.DataType._Varchar, data.City));
param.Add(Connection.GetParameter("pZipCode", DataPlug.DataType._Varchar, data.ZipCode));
param.Add(Connection.GetParameter("pGatewaytype", DataPlug.DataType._Int, data.Gatewaytype));
param.Add(Connection.GetParameter("pStatus", DataPlug.DataType._Int, (int)CLayer.ObjectStatus.OfflinePyamentStatus.Processing));
param.Add(Connection.GetParameter("pPaymentlinkid", DataPlug.DataType._Char, data.CustomerGuid));
object result = Connection.ExecuteQueryScalar("SP_offlineCustomerPayment_Save", param);
return(Connection.ToLong(result));
}
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);
}
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 long VerifiedUpdate(CLayer.Review feedback)
{
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
param.Add(Connection.GetParameter("pPropertyId", DataPlug.DataType._BigInt, feedback.PropertyId));
param.Add(Connection.GetParameter("pUserId", DataPlug.DataType._BigInt, feedback.UserId));
param.Add(Connection.GetParameter("pBookingId", DataPlug.DataType._BigInt, feedback.BookingId));
param.Add(Connection.GetParameter("pDescription", DataPlug.DataType._Varchar, feedback.Description));
param.Add(Connection.GetParameter("pUpdatedUserId", DataPlug.DataType._BigInt, feedback.UpdatedBy));
param.Add(Connection.GetParameter("pUpdatedDate", DataPlug.DataType._DateTime, feedback.UpdatedDate));
param.Add(Connection.GetParameter("pStatus", DataPlug.DataType._Int, feedback.Status));
param.Add(Connection.GetParameter("pIsRecommended", DataPlug.DataType._Bool, feedback.Recommended));
param.Add(Connection.GetParameter("pAccommodationCity", DataPlug.DataType._Varchar, feedback.AccommodationCity));
param.Add(Connection.GetParameter("pAccessibility", DataPlug.DataType._Int, feedback.Accessibility));
param.Add(Connection.GetParameter("pEasiness", DataPlug.DataType._Int, feedback.Easiness));
param.Add(Connection.GetParameter("pCleanlinessBed", DataPlug.DataType._Int, feedback.CleanlinessBed));
param.Add(Connection.GetParameter("pCleanlinessBath", DataPlug.DataType._Int, feedback.CleanlinessBath));
param.Add(Connection.GetParameter("pSleepQuality", DataPlug.DataType._Int, feedback.SleepQuality));
param.Add(Connection.GetParameter("pStaffbehave", DataPlug.DataType._Int, feedback.Staffbehave));
param.Add(Connection.GetParameter("pOverallExp", DataPlug.DataType._Int, feedback.OverallExp));
param.Add(Connection.GetParameter("pConsiderfornext", DataPlug.DataType._Int, feedback.Considerfornext));
//param.Add(Connection.GetParameter("pRating", DataPlug.DataType._Int, feedback.Rating));
object result = Connection.ExecuteQueryScalar("review_Update", param);
return(Connection.ToLong(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 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));
}
