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 CLayer.Invoice GetInvoice(long invoiceId)
{
string sql = "Select * from Invoices Where InvoiceId=" + invoiceId.ToString();
DataTable dt = Connection.GetSQLTable(sql);
CLayer.Invoice data = null;
if (dt.Rows.Count == 0)
{
return(data);
}
data = new CLayer.Invoice();
DataRow dr = dt.Rows[0];
data.InvoiceId = Connection.ToLong(dr["InvoiceId"]);
data.InvoiceNumber = Connection.ToString(dr["InvoiceNumber"]);
data.Discounts = Connection.ToDouble(dr["Discounts"]);
data.DueDate = Connection.ToDate(dr["DueDate"]);
data.HtmlSection1 = Connection.ToString(dr["HtmlSection1"]);
data.HtmlSection2 = Connection.ToString(dr["HtmlSection2"]);
data.HtmlSection3 = Connection.ToString(dr["HtmlSection3"]);
data.InvoiceDate = Connection.ToDate(dr["InvoiceDate"]);
data.InvoiceType = Connection.ToInteger(dr["InvoiceType"]);
data.MailedDate = Connection.ToDate(dr["MailedDate"]);
data.OfflineBookingId = Connection.ToLong(dr["OfflineBookingId"]);
data.Others = Connection.ToDouble(dr["Others"]);
data.Reimbursements = Connection.ToDouble(dr["Reimbursements"]);
data.Status = Connection.ToInteger(dr["Status"]);
data.ToAddress = Connection.ToString(dr["ToAddress"]);
return(data);
}
public CLayer.SupplierPayment GetPaymentdetails(string refId)
{
CLayer.SupplierPayment user = null;
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
param.Add(Connection.GetParameter("pRefId", DataPlug.DataType._Varchar, refId));
DataTable dt = Connection.GetTable("OfflineBooking_GetAllByRefid", param);
if (dt.Rows.Count > 0)
{
user = new CLayer.SupplierPayment();
user.Buyprice = Connection.ToDouble(dt.Rows[0]["TotalBuyPrice"]);
user.propertyname = Connection.ToString(dt.Rows[0]["PropertyName"]);
user.propertycity = Connection.ToString(dt.Rows[0]["PropertyCityname"]);
// FirstName = Connection.ToString(dr["FirstName"]),
// LastName = Connection.ToString(dr["LastName"]),
user.SupplierEmail = Connection.ToString(dt.Rows[0]["SupplierEmail"]);
user.SupplierName = Connection.ToString(dt.Rows[0]["SupplierName"]);
user.Buyprice = Connection.ToDouble(dt.Rows[0]["TotalBuyPrice"]);
user.AmountPaid = Connection.ToDouble(dt.Rows[0]["amount"]);
user.SupplierMobile = Connection.ToString(dt.Rows[0]["SupplierMobile"]);
user.OrderId = Connection.ToString(dt.Rows[0]["Orderno"]);
user.CheckIn_date = Connection.ToDate(dt.Rows[0]["CheckIn_date"]);
user.CustomerName = Connection.ToString(dt.Rows[0]["CustName"]);
// DeletedDate = Connection.ToDate(dr["DeletedDate"]),
user.CheckOut_date = Connection.ToDate(dt.Rows[0]["CheckOut_date"]);
user.BookingDate = Connection.ToDate(dt.Rows[0]["BookingDate"]);
user.supplierId = Connection.ToLong(dt.Rows[0]["SupplierId"]);
user.supplierPayment = Connection.ToBoolean(dt.Rows[0]["supplierPayment"]);
}
return(user);
}
public List <CLayer.SupplierPayment> GetAllSupllierPaymentVoucher(long SupplierPaymentId)
{
List <CLayer.SupplierPayment> users = new List <CLayer.SupplierPayment>();
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
param.Add(Connection.GetParameter("pSupplierPaymentID", DataPlug.DataType._Int, SupplierPaymentId));
DataSet ds = Connection.GetDataSet("supplierpaymentvoucher_GetAllBySupplierPaymentID", param);
foreach (DataRow dr in ds.Tables[0].Rows)
{
users.Add(new CLayer.SupplierPayment()
{
grossAmount = Connection.ToDecimal(dr["grossAmount"]),
tdsAmount = Connection.ToDecimal(dr["tdsAmount"]),
netAmtPaid = Connection.ToDecimal(dr["netAmtPaid"]),
modeofPayment = Connection.ToString(dr["modeofPayment"]),
BAnk = Connection.ToString(dr["bank"]),
NoOfPaxChild = Connection.ToLong(dr["NoOfPaxChild"]),
NoOfPaxAdult = Connection.ToLong(dr["NoOfPaxAdult"]),
NoofRooms = Connection.ToLong(dr["NoofRooms"]),
Guestname = Connection.ToString(dr["Guestname"]),
// Salutation = Connection.ToString(dr["Salutation"]),
propertyname = Connection.ToString(dr["PropertyName"]),
propertycity = Connection.ToString(dr["propertycity"]),
Region = Connection.ToString(dr["Region"]),
Amount = Connection.ToDecimal(dr["Amount"]),
SupplierCity = Connection.ToString(dr["SupplierCity"]),
// FirstName = Connection.ToString(dr["FirstName"]),
// LastName = Connection.ToString(dr["LastName"]),
SupplierEmail = Connection.ToString(dr["SupplierEmail"]),
SupplierName = Connection.ToString(dr["SupplierName"]),
Buyprice = Connection.ToDouble(dr["TotalBuyPrice"]),
pdtPayment = Connection.ToDate(dr["dtPayment"]),
AmountPaid = Connection.ToDouble(dr["amount"]),
SupplierMobile = Connection.ToString(dr["SupplierMobile"]),
OrderId = Connection.ToString(dr["Orderno"]),
CheckIn_date = Connection.ToDate(dr["CheckIn_date"]),
// DeletedDate = Connection.ToDate(dr["DeletedDate"]),
CheckOut_date = Connection.ToDate(dr["CheckOut_date"]),
BookingDate = Connection.ToDate(dr["BookingDate"]),
NoofDays = Connection.ToLong(dr["Noofnight"]),
Payamount = Connection.ToDecimal(dr["PayAmount"])
// Status = Connection.ToInteger(dr["Status"]),
// PANNo = Connection.ToString(dr["PANNo"]),
// UserCode = Connection.ToString(dr["UserCode"]),
});
}
return(users);
}
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.Tax> GetAmadeusTaxRates(long PropertyID)
{
List <CLayer.Tax> result = new List <CLayer.Tax>();
try
{
string sql = "Select * From amadeustaxrates Where PropertyID=" + PropertyID + "";
DataTable dt = Connection.GetSQLTable(sql);
CLayer.Tax temp;
foreach (DataRow dr in dt.Rows)
{
temp = new CLayer.Tax();
temp.TaxId = Connection.ToInteger(dr["TaxId"]);
temp.PropertyId = Connection.ToLong(dr["PropertyID"]);
temp.BookingCode = Connection.ToString(dr["BookingCode"]);
temp.BookingId = Connection.ToLong(dr["BookingId"]);
temp.CGSTTitle = Connection.ToString(dr["CGSTTitle"]);
temp.SGSTTitle = Connection.ToString(dr["SGSTTitle"]);
temp.IGSTTitle = Connection.ToString(dr["IGSTTitle"]);
temp.TotalCGSTTaxAmount = Connection.ToDecimal(dr["TotalCGSTTaxAmount"]);
temp.TotalSGSTTaxAmount = Connection.ToDecimal(dr["TotalSGSTTaxAmount"]);
temp.TotalIGSTTaxAmount = Connection.ToDecimal(dr["TotalIGSTTaxAmount"]);
temp.AccommodationId = Connection.ToLong(dr["AccommodationId"]);
temp.TaxRate = Connection.ToDouble(dr["TaxRate"]);
temp.CGSTTaxRate = Connection.ToDouble(dr["CGSTTaxRate"]);
temp.SGSTTaxRate = Connection.ToDouble(dr["SGSTTaxRate"]);
temp.IGSTTaxRate = Connection.ToDouble(dr["IGSTTaxRate"]);
temp.CGSTTaxRate = (temp.CGSTTaxRate <= 0) ? Convert.ToDouble(GetValue(CLayer.Settings.CGST_TAXRATE)) : temp.CGSTTaxRate;
temp.SGSTTaxRate = (temp.SGSTTaxRate <= 0) ? Convert.ToDouble(GetValue(CLayer.Settings.SGST_TAXRATE)) : temp.SGSTTaxRate;
temp.IGSTTaxRate = (temp.IGSTTaxRate <= 0) ? Convert.ToDouble(GetValue(CLayer.Settings.IGST_TAXRATE)) : temp.IGSTTaxRate;
result.Add(temp);
}
}
catch (Exception ex)
{
result = null;
throw ex;
}
return(result);
}
public List <CLayer.BookingItemTax> GetAllByBookingItem(long bookingItemId)
{
string sql = "SELECT tt.Title,bt.Rate,bt.Amount,bt.OnGrandTotal FROM bookingitem_tax bt INNER JOIN tax t ON bt.TaxId = t.TaxId ";
sql = sql + " INNER JOIN taxtitle tt ON t.TaxTitleId = tt.TaxTitleId Where bt.BookingItemId=" + bookingItemId.ToString();
DataTable dt = Connection.GetSQLTable(sql);
List <CLayer.BookingItemTax> result = new List <CLayer.BookingItemTax>();
CLayer.BookingItemTax temp;
foreach (DataRow dr in dt.Rows)
{
temp = new CLayer.BookingItemTax();
temp.Title = Connection.ToString(dr["Title"]);
temp.OnGrandTotal = Connection.ToBoolean(dr["OnGrandTotal"]);
temp.Amount = Connection.ToDouble(dr["Amount"]);
temp.Rate = Connection.ToDouble(dr["Rate"]);
result.Add(temp);
}
return(result);
}
public List <CLayer.SupplierPayment> GetAllSupllierPaymentSearch(int status, string searchString, int searchItem, int userType, int start, int limit)
{
List <CLayer.SupplierPayment> users = new List <CLayer.SupplierPayment>();
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("pUserType", DataPlug.DataType._Int, userType));
param.Add(Connection.GetParameter("pStart", DataPlug.DataType._Int, start));
param.Add(Connection.GetParameter("pLimit", DataPlug.DataType._Int, limit));
DataSet ds = Connection.GetDataSet("OfflineBookingSupplierpayment_GetSearch", param);
foreach (DataRow dr in ds.Tables[1].Rows)
{
users.Add(new CLayer.SupplierPayment()
{
// Salutation = Connection.ToString(dr["Salutation"]),
propertyname = Connection.ToString(dr["PropertyName"]),
// FirstName = Connection.ToString(dr["FirstName"]),
// LastName = Connection.ToString(dr["LastName"]),
SupplierEmail = Connection.ToString(dr["SupplierEmail"]),
SupplierName = Connection.ToString(dr["SupplierName"]),
Buyprice = Connection.ToDouble(dr["TotalBuyPrice"]),
supplierPayment = Connection.ToBoolean(dr["supplierPayment"]),
AmountPaid = Connection.ToDouble(dr["amount"]),
SupplierMobile = Connection.ToString(dr["SupplierMobile"]),
OrderId = Connection.ToString(dr["Orderno"]),
CheckIn_date = Connection.ToDate(dr["CheckIn_date"]),
// DeletedDate = Connection.ToDate(dr["DeletedDate"]),
CheckOut_date = Connection.ToDate(dr["CheckOut_date"]),
BookingDate = Connection.ToDate(dr["BookingDate"]),
// Status = Connection.ToInteger(dr["Status"]),
// PANNo = Connection.ToString(dr["PANNo"]),
// UserCode = Connection.ToString(dr["UserCode"]),
TotalRows = Connection.ToLong(ds.Tables[0].Rows[0]["NumberOfRows"])
});
}
return(users);
}
public List <CLayer.SupplierPaymetList> getPaymentList(string pSearchString, int start, int limit, int pSearchType = 0)
{
List <CLayer.SupplierPaymetList> result = new List <CLayer.SupplierPaymetList>();
CLayer.SupplierPaymetList res = null;
List <DataPlug.Parameter> param = new List <DataPlug.Parameter>();
param.Add(Connection.GetParameter("pSearchString", DataPlug.DataType._Text, pSearchString));
param.Add(Connection.GetParameter("pSearchType", DataPlug.DataType._Int, pSearchType));
param.Add(Connection.GetParameter("pstart", DataPlug.DataType._Int, start));
param.Add(Connection.GetParameter("pLimit", DataPlug.DataType._Int, limit));
DataSet ds = Connection.GetDataSet("SupplierPaymentList_Get", param);
foreach (DataRow dr in ds.Tables[1].Rows)
{
res = new CLayer.SupplierPaymetList()
{
SupplierPaymentId = Connection.ToLong(dr["SupplierPaymentId"]),
SupplierId = Connection.ToLong(dr["SupplierId"]),
OfflineSupplierEmail = Connection.ToString(dr["OfflineSupplierEmail"]),
SupplierPayment = Connection.ToDecimal(dr["SupplierPayment"]),
dtPayment = Connection.ToDate(dr["dtPayment"]),
grossAmount = Connection.ToDecimal(dr["grossAmount"]),
tdsAmount = Connection.ToDecimal(dr["tdsAmount"]),
AmountPaid = Connection.ToDouble(dr["Amount"]),
netAmtPaid = Connection.ToDecimal(dr["netAmtPaid"]),
modeofPayment = Connection.ToString(dr["modeofPayment"]),
CheckIn_date = Connection.ToDate(dr["CheckIn_date"]),
CheckOut_date = Connection.ToDate(dr["CheckOut_date"]),
bank = Connection.ToString(dr["bank"]),
SupplierName = Connection.ToString(dr["Supplier_Name"]),
Prop_Name = Connection.ToString(dr["Prop_Name"]),
Prop_ID = Connection.ToLong(dr["Prop_ID"]),
OrderNo = Connection.ToString(dr["OrderNo"]),
TotalRows = Connection.ToLong(ds.Tables[0].Rows[0]["NumberOfRows"])
};
result.Add(res);
}
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);
}
