public TransferSupplierCredentials GetSupplierRouteBySupplierCodeAndAgencyCode(string agencyCode, string supplierCode, string routeFlag)
{
using (var _ctx = new MediationEntities())
{
var supplierBasicDetails = (from sm in _ctx.tblSupplierMasters
join ag in _ctx.tblAgencies on sm.AgencyID equals ag.AgencyID
join sapi in _ctx.tblSupplierApiInfoes on sm.SupplierId equals sapi.SupplierId
join roe in _ctx._tblRateofExchange on sm.AgencyID equals roe.AgencyID
where sm.Status == "T" && ag.AgencyCode == agencyCode && sapi.SupplierRoute == routeFlag && sm.SupplierCode == supplierCode && roe.Status == 1
select new TransferSupplierCredentials
{
//AccountNumber = sm.AccountNumber,
//Password = sm.Password,
//UserName = sm.UserName,
//BaseUrl = sm.BaseUrl,
//RequestUrl = sapi.RequestUrl,
//AgencyID = ag.AgencyID,
//AgencyCode = ag.AgencyCode,
//SupplierId = sm.SupplierId,
//ToCurrency = roe.CurrencyCode,
//ToROEValue = roe.ReteofExchange,
//SupplierCode = sm.SupplierCode
}).FirstOrDefault();
return(supplierBasicDetails);
}
}
public async Task <SupplierAgencyDetails> GetSupplierAgencyBasicDetails(string agencyCode, string status, string supplierCode)
{
using (var _ctx = new MediationEntities())
{
var supplierBasicDetails = (from sm in _ctx.tblSupplierMasters
join ag in _ctx.tblAgencies on sm.AgencyID equals ag.AgencyID
join roe in _ctx._tblRateofExchange on sm.AgencyID equals roe.AgencyID
where sm.Status == status && ag.AgencyCode == agencyCode && roe.Status == 1 &&
sm.SupplierCode == supplierCode
select new SupplierAgencyDetails
{
AccountNumber = sm.AccountNumber,
AgencyID = ag.AgencyID,
Password = sm.Password,
Status = sm.Status,
SupplierCode = sm.SupplierCode,
SupplierName = sm.SupplierName,
UserName = sm.UserName,
BaseUrl = sm.BaseUrl,
ToCurrency = roe.CurrencyCode,
ToROEValue = roe.ReteofExchange,
AgencyCode = ag.AgencyCode
}).FirstOrDefault();
return(supplierBasicDetails);
}
}
public async Task <string> GetAgencySupplierResponse(AgencySupplierResponseEntity message)
{
try
{
message.SearchDateTime = DateTime.Now;
int minToAdd = 20;
using (var _ctx = new MediationEntities())
{
const string sql = @"EXEC [dbo].[spGetAgencySupplierResponse]
@AgencyCode
,@SearchDateTime
,@Key
,@Status
,@SupplierCode
,@MinutesToAdd
";
var result = _ctx.Database
.SqlQuery <SpBaseEntity>(
sql
, new SqlParameter("AgencyCode", message.AgencyCode)
, new SqlParameter("SearchDateTime", message.SearchDateTime)
, new SqlParameter("Key", message.Key)
, new SqlParameter("Status", message.Status)
, new SqlParameter("SupplierCode", message.SupplierCode)
, new SqlParameter("MinutesToAdd", minToAdd)
).FirstOrDefault();
if (result == null)
{
result.Data = null;
}
if (result.StatusCode != "OK-200")
{
result.Data = null;
}
return(result.Data);
}
}
catch (Exception ex)
{
return(null);
}
}
public async Task <string> GetUserIDfromRefID(string BookingRefID)
{
string userID = "";
long _bookrefid = Convert.ToInt64(BookingRefID);
using (var _ctx = new MediationEntities())
{
var refIDuser = (from us in _ctx.tblBookings
where us.BookingRefID == _bookrefid
select new
{
UserID = us.UserID
}).ToList();
userID = refIDuser[0].UserID.ToString();
}
return(userID);
}
public async Task <bool> AddSupplierResponse(List <AgencySupplierResponseEntity> message)
{
bool isUpdated = false;
try
{
using (var _ctx = new MediationEntities())
{
message.ForEach(_ =>
{
_ctx.tblAgencySeacrhDetails.Add(new tblAgencySeacrhDetail()
{
AgencyCode = _.AgencyCode,
SearchDateTime = _.SearchDateTime,
Status = _.Status,
SupplierCode = _.SupplierCode,
Search = _.Search,
Key = _.Key
});
});
await _ctx.SaveChangesAsync();
isUpdated = true;
return(isUpdated);
}
}
catch (DbEntityValidationException e)
{
var outputLines = new List <string>();
foreach (var eve in e.EntityValidationErrors)
{
outputLines.Add(string.Format("{0}: Entity of type \"{1}\" in state \"{2}\" has the following validation errors:", DateTime.Now, eve.Entry.Entity.GetType().Name, eve.Entry.State));
foreach (var ve in eve.ValidationErrors)
{
outputLines.Add(string.Format("- Property: \"{0}\", Error: \"{1}\"", ve.PropertyName, ve.ErrorMessage));
}
}
return(isUpdated);
}
catch (Exception ex)
{
throw;
}
}
public async Task <TransferSupplierCredentials> GeBasicDetailsOfTransferSupplier(string agencyCode, string supplierCode, string status)
{
using (var _ctx = new MediationEntities())
{
const string sql = @"EXEC spGetSupplierBasicDetails @agencyCode, @supplierCode, @status";
var result = await _ctx.Database
.SqlQuery <TransferSupplierCredentials>(
sql
, new SqlParameter("agencyCode", agencyCode)
, new SqlParameter("supplierCode", supplierCode)
, new SqlParameter("status", status)
).FirstOrDefaultAsync();
return(result);
}
}
public async Task <List <SupplierAgencyDetails> > GetSupplierAgencyBasicDetailswithsuppliercode(string agencyCode, string status, string supplierCode)
{
using (var _ctx = new MediationEntities())
{
var supplierBasicDetails = await(from sm in _ctx.tblSupplierMasters
join ag in _ctx.tblAgencies on sm.AgencyID equals ag.AgencyID
//join sapi in _ctx.tblSupplierApiInfoes on sm.SupplierId equals sapi.SupplierId
where sm.Status == status && ag.AgencyCode == agencyCode && sm.SupplierCode == supplierCode
select new SupplierAgencyDetails
{
AccountNumber = sm.AccountNumber,
AgencyID = ag.AgencyID,
Password = sm.Password,
Status = sm.Status,
SupplierCode = sm.SupplierCode,
SupplierName = sm.SupplierName,
UserName = sm.UserName,
AgencyCode = ag.AgencyCode
}).ToListAsync();
return(supplierBasicDetails);
}
}
public async Task <GetTripDetailsModelRS> GetTravellerDetailsfromDB(string bookingRefID)
{
GetTripDetailsModelRS _GetTripDetailsModelRS = new GetTripDetailsModelRS();
using (var _ctx = new MediationEntities())
{
long _bookref = Convert.ToInt64(bookingRefID);
var BookingRefIDBasicData = (from sm in _ctx.tblBookings
join bs in _ctx.tblBookingStatus on sm.BookingStatusCode equals bs.BookingStatusCode
join ur in _ctx.tblUsers on sm.UserID equals ur.UserID
join ag in _ctx.tblAgencies on ur.AgencyID equals ag.AgencyID
join bc in _ctx.tblAirBookingCost on sm.BookingRefID equals bc.BookingRefID
join bd in _ctx._tblAirBookingData on sm.BookingRefID equals bd.BookingRefID
join sp in _ctx.tblSupplierMasters on sm.SupplierID equals sp.SupplierId
where sm.BookingRefID == _bookref
select new
{
BookingDate = sm.BookingDate,
UserID = sm.UserID,
BookingID = sm.BookingRefID,
SupplierBookingReference = sm.SupplierBookingReference,
CancellationDeadline = sm.CancellationDeadline,
BookingStatusCode = sm.BookingStatusCode,
BookingStatus = bs.BookingStatus,
AgencyCode = ag.AgencyCode,
TotalBaseNet = bc.TotalBaseNet,
TotalTaxNet = bc.TotalTaxNet,
TotalNet = bc.TotalNet,
NetCurrency = bc.NetCurrency,
MarkupTypeID = bc.MarkupTypeID,
MarkupValue = bc.MarkupValue,
MarkupCurrency = bc.MarkupCurrency,
SellAmount = bc.SellAmount,
SellCurrency = bc.SellCurrency,
AdditionalServiceFee = bc.AdditionalServiceFee,
CancellationAmount = bc.CancellationAmount,
CancellationCurrency = bc.CancellationCurrency,
SupplierCode = sp.SupplierCode,
UniqueID = bd.UniqueID
}).ToList();
//To Response Json Start
if (BookingRefIDBasicData.Count > 0)
{
Dbconnectionresponse _Dbconnectionresponse = new Dbconnectionresponse()
{
AgencyCode = BookingRefIDBasicData[0].AgencyCode,
BookingId = BookingRefIDBasicData[0].BookingID.ToString(),
SupplierCode = BookingRefIDBasicData[0].SupplierCode,
BookingStatus = BookingRefIDBasicData[0].BookingStatusCode,
TicketLimit = DateTime.Parse(BookingRefIDBasicData[0].CancellationDeadline.ToString()).ToString("dd-MM-yyyy hh:mm:ss"),
BookingDate = DateTime.Parse(BookingRefIDBasicData[0].BookingDate.ToString()).ToString("dd-MM-yyyy hh:mm:ss"),
UniqueID = BookingRefIDBasicData[0].UniqueID
};
Dbtotalfaregroup _Dbtotalfaregroup = new Dbtotalfaregroup()
{
totalBaseNet = BookingRefIDBasicData[0].TotalBaseNet.ToString(),
totalTaxNet = BookingRefIDBasicData[0].TotalTaxNet.ToString(),
markupTypeID = BookingRefIDBasicData[0].MarkupTypeID.ToString(),
netCurrency = BookingRefIDBasicData[0].NetCurrency.ToString(),
markupValue = BookingRefIDBasicData[0].MarkupValue.ToString(),
markupCurrency = BookingRefIDBasicData[0].MarkupCurrency,
sellAmount = BookingRefIDBasicData[0].SellAmount.ToString(),
sellCurrency = BookingRefIDBasicData[0].SellCurrency.ToString(),
additionalServiceFee = BookingRefIDBasicData[0].AdditionalServiceFee.ToString(),
cancellationAmount = BookingRefIDBasicData[0].CancellationAmount.ToString(),
cancellationCurrency = BookingRefIDBasicData[0].CancellationCurrency,
paidDate = "",
paidAmount = BookingRefIDBasicData[0].TotalNet.ToString(),
paymentTypeID = ""
};
_Dbconnectionresponse.DBTotalfaregroup = _Dbtotalfaregroup;
//To Response Json End
var RefIDBaggDetails = (from bg in _ctx.tblAirbaggageDetails
where bg.BookingRefID == _bookref
select new
{
paxType = bg.PAXType,
cabinBaggageQuantity = bg.CabinBaggageQuantity,
cabinBaggageUnit = bg.CabinBaggageUnit,
checkinBaggageQuantity = bg.CheckinBaggageQuantity,
checkinBaggageUnit = bg.CheckinBaggageUnit
}).ToList();
//To Response Json Start
int totalbaggdetails = RefIDBaggDetails.Count;
if (totalbaggdetails > 0)
{
Dbairbagdetail[] _Dbairbagdetails = new Dbairbagdetail[totalbaggdetails];
int bagSeq = 0;
foreach (var item in RefIDBaggDetails)
{
Dbairbagdetail _Dbairbagdetail = new Dbairbagdetail()
{
paxType = item.paxType,
cabinBaggageQuantity = item.cabinBaggageQuantity.ToString(),
cabinBaggageUnit = item.cabinBaggageUnit,
checkinBaggageQuantity = item.checkinBaggageQuantity.ToString(),
checkinBaggageUnit = item.checkinBaggageUnit
};
_Dbairbagdetails[bagSeq] = _Dbairbagdetail;
++bagSeq;
}
_Dbconnectionresponse.DBairBagDetails = _Dbairbagdetails;
}
else
{
Dbairbagdetail[] _Dbairbagdetails = new Dbairbagdetail[0];
_Dbconnectionresponse.DBairBagDetails = _Dbairbagdetails;
}
//To Response Json End
var RefIDCostbreakup = (from br in _ctx.tblAirBookingCostBreakup
join bc in _ctx.tblAirBookingCost on br.BookingCostID equals bc.BookingCostID
where bc.BookingRefID == _bookref
select new
{
paxType = br.PaxType,
paxtotalTaxNet = br.TaxNet,
paxtotalBaseNet = br.BaseNet,
paxmarkupTypeID = br.MarkupTypeID,
paxnetCurrency = br.NetCurrency,
paxmarkupValue = br.MarkupValue,
paxmarkupCurrency = br.MarkupCurrency,
paxsellAmount = br.SellAmount,
paxsellCurrency = br.SellCurrency,
paxadditionalServiceFee = br.AdditionalServiceFee,
paxquantity = br.NoOfPAx,
paxpaidAmount = br.SellAmount
}).ToList();
//To Response Json Start
int totalpricebk = RefIDCostbreakup.Count;
if (totalpricebk > 0)
{
int costSeq = 0;
Dbcostbreakuppax[] _Dbcostbreakuppaxs = new Dbcostbreakuppax[totalpricebk];
foreach (var item in RefIDCostbreakup)
{
Dbcostbreakuppax _Dbcostbreakuppax = new Dbcostbreakuppax()
{
paxType = item.paxType,
paxquantity = item.paxquantity.ToString(),
paxtotalBaseNet = item.paxtotalBaseNet.ToString(),
paxtotalTaxNet = item.paxtotalTaxNet.ToString(),
paxmarkupTypeID = item.paxmarkupTypeID.ToString(),
paxnetCurrency = item.paxnetCurrency.ToString(),
paxmarkupValue = item.paxmarkupValue.ToString(),
paxmarkupCurrency = item.paxmarkupCurrency.ToString(),
paxsellAmount = item.paxsellAmount.ToString(),
paxsellCurrency = item.paxsellCurrency.ToString(),
paxadditionalServiceFee = item.paxadditionalServiceFee.ToString(),
paxpaidAmount = item.paxpaidAmount.ToString()
};
_Dbcostbreakuppaxs[costSeq] = _Dbcostbreakuppax;
++costSeq;
}
_Dbconnectionresponse.DBcostBreakuppax = _Dbcostbreakuppaxs;
}
else
{
Dbcostbreakuppax[] _Dbcostbreakuppaxs = new Dbcostbreakuppax[0];
_Dbconnectionresponse.DBcostBreakuppax = _Dbcostbreakuppaxs;
}
//To Response Json End
var RefIDfarerule = (from fr in _ctx.tblAirFarerules
where fr.BookingRefID == _bookref
select new
{
FareRule = fr.FareRule,
Segment = fr.Segment,
FareRef = fr.FareRef,
FilingAirline = fr.FilingAirline,
MarketingAirline = fr.MarketingAirline
}).ToList();
//To Response Json Start
int totRefIDfarerule = RefIDfarerule.Count;
if (totRefIDfarerule > 0)
{
int ruleSeq = 0;
Dbfareruleseg[] _Dbfarerulesegs = new Dbfareruleseg[totRefIDfarerule];
foreach (var item in RefIDfarerule)
{
Dbfareruleseg _Dbfareruleseg = new Dbfareruleseg()
{
fareRule = item.FareRule,
Segment = item.Segment,
FareRef = item.FareRef,
FilingAirline = item.FilingAirline,
MarketingAirline = item.MarketingAirline
};
_Dbfarerulesegs[ruleSeq] = _Dbfareruleseg;
++ruleSeq;
}
_Dbconnectionresponse.DBfareruleseg = _Dbfarerulesegs;
}
else
{
Dbfareruleseg[] _Dbfarerulesegs = new Dbfareruleseg[0];
_Dbconnectionresponse.DBfareruleseg = _Dbfarerulesegs;
}
//To Response Json End
var RefIDTravellers = (from ap in _ctx.tblAirPassengers
join tl in _ctx.tblTitles on ap.TitleID equals tl.TitleID
join pd in _ctx._tblAirPassengerDoc on ap.PaxID equals pd.PaxID
join doc in _ctx._tblDocType on pd.DocTypeID equals doc.DocTypeID
join pax in _ctx._tblPassengerType on ap.PaxTypeID equals pax.PaxTypeID
where ap.BookingRefID == _bookref
select new
{
Title = tl.Title,
GivenName = ap.GivenName,
Surname = ap.Surname,
BirthDate = ap.DateofBirth,
DocType = doc.DocType,
DocumentNumber = pd.DocumentNumber,
DocIssueCountry = pd.IssueCountry,
ExpireDate = pd.ExpiryDate,
AirPnr = ap.PNR,
AirTicketNo = ap.ETicketNo,
paxtype = pax.PaxType
}).ToList();
//To Response Json Start
int totRefIDTravellers = RefIDTravellers.Count;
if (totRefIDTravellers > 0)
{
int travellerSeq = 0;
Dbtravelerinfo[] _Dbtravelerinfos = new Dbtravelerinfo[totRefIDTravellers];
foreach (var item in RefIDTravellers)
{
Dbtravelerinfo _Dbtravelerinfo = new Dbtravelerinfo()
{
PassengerType = item.paxtype.ToString(),
GivenName = item.GivenName,
NamePrefix = item.Title,
Surname = item.Surname,
BirthDate = Convert.ToDateTime(item.BirthDate).ToString("dd-MM-yyyy"),
DocType = item.DocType,
DocumentNumber = item.DocumentNumber,
DocIssueCountry = item.DocIssueCountry,
ExpireDate = Convert.ToDateTime(item.ExpireDate).ToString("dd-MM-yyyy"),
AirPnr = item.AirPnr,
AirTicketNo = item.AirTicketNo
};
_Dbtravelerinfos[travellerSeq] = _Dbtravelerinfo;
++travellerSeq;
}
_Dbconnectionresponse.DBTravelerInfo = _Dbtravelerinfos;
}
else
{
Dbtravelerinfo[] _Dbtravelerinfos = new Dbtravelerinfo[0];
_Dbconnectionresponse.DBTravelerInfo = _Dbtravelerinfos;
}
//To Response Json End
var RefiDLegs = (from lg in _ctx.tblAirOriginDestinationOption
where lg.BookingRefID == _bookref
select new
{
OriginDestinationID = lg.OriginDestinationID,
RefNumber = lg.RefNumber,
DirectionID = lg.RefNumber,
ElapsedTime = lg.ElapsedTime
}).ToList();
int totRefiDLegs = RefiDLegs.Count;
if (totRefiDLegs > 0)
{
Dbflleggroup[] _Dbflleggroups = new Dbflleggroup[totRefiDLegs];
int legSeq = 0;
foreach (var item in RefiDLegs)
{
Dbflleggroup _Dbflleggroup = new Dbflleggroup();
_Dbflleggroup.elapsedtime = item.ElapsedTime;
long _LegID = item.OriginDestinationID;
var RefIDSegmant = (from seg in _ctx.tblAirSegment
join segf in _ctx._tblAirSegmentBookingAvail on seg.SegmentID equals segf.SegmentID
where seg.OriginDestinationID == _LegID
select new
{
FareBasis = "",
DepartureDate = seg.DepartureDateTime,
ArrivalDate = seg.ArrivalDateTime,
DepartureFrom = seg.DepartureAirportLocationCode,
DepartureTo = seg.ArrivalAirportLocationCode,
MarketingCompany = seg.MarketingAirlineCode,
OperatingCompany = seg.OperatingAirlineCode,
FlightNumber = seg.FlightNumber,
BookingClass = "",
terminalTo = seg.ArrivalAirportTerminal,
terminalFrom = seg.DepartureAirportTerminal,
flightequip = seg.EquipmentAirEquipType,
cabin = segf.ResBookDesigCabinCode
}).ToList();
int totRefIDSegmant = RefIDSegmant.Count;
if (totRefIDSegmant > 0)
{
int segSeq = 0;
Dbsegment[] _Dbsegments = new Dbsegment[totRefIDSegmant];
foreach (var segitem in RefIDSegmant)
{
Dbsegment _Dbsegment = new Dbsegment();
_Dbsegment.DepartureDate = Convert.ToDateTime(segitem.DepartureDate).ToString("dd-MM-yyyy");
_Dbsegment.DepartureTime = Convert.ToDateTime(segitem.DepartureDate).ToString("HHmm");
_Dbsegment.ArrivalDate = Convert.ToDateTime(segitem.ArrivalDate).ToString("dd-MM-yyyy");
_Dbsegment.ArrivalTime = Convert.ToDateTime(segitem.ArrivalDate).ToString("HHmm");
_Dbsegment.DepartureFrom = segitem.DepartureFrom;
_Dbsegment.DepartureTo = segitem.DepartureTo;
_Dbsegment.MarketingCompany = segitem.MarketingCompany;
_Dbsegment.OperatingCompany = segitem.OperatingCompany;
_Dbsegment.FlightNumber = segitem.FlightNumber;
_Dbsegment.BookingClass = segitem.BookingClass;
_Dbsegment.terminalTo = segitem.terminalTo;
_Dbsegment.terminalFrom = segitem.terminalFrom;
_Dbsegment.flightequip = segitem.flightequip;
_Dbsegment.cabin = segitem.cabin;
_Dbsegments[segSeq] = _Dbsegment;
++segSeq;
}
_Dbflleggroup.from = RefIDSegmant[0].DepartureFrom;
_Dbflleggroup.to = RefIDSegmant[totRefIDSegmant - 1].DepartureTo;
_Dbflleggroup.DBsegments = _Dbsegments;
}
else
{
Dbsegment[] _Dbsegments = new Dbsegment[0];
_Dbflleggroup.DBsegments = _Dbsegments;
}
_Dbflleggroups[legSeq] = _Dbflleggroup;
++legSeq;
}
_Dbconnectionresponse.DBFLLegGroup = _Dbflleggroups;
}
else
{
Dbflleggroup[] _Dbflleggroups = new Dbflleggroup[0];
_Dbconnectionresponse.DBFLLegGroup = _Dbflleggroups;
}
var refidPaxAdtcound = (from px in _ctx.tblAirBookingCostBreakup
join pc in _ctx.tblAirBookingCost_1 on px.BookingCostID equals pc.BookingCostID
where pc.BookingRefID == _bookref && px.PaxType == "ADT"
select new
{
PaxType = px.PaxType,
quantity = px.NoOfPAx
}).ToList();
var refidPaxchdcound = (from px in _ctx.tblAirBookingCostBreakup
join pc in _ctx.tblAirBookingCost_1 on px.BookingCostID equals pc.BookingCostID
where pc.BookingRefID == _bookref && px.PaxType == "CHD"
select new
{
PaxType = px.PaxType,
quantity = px.NoOfPAx
}).ToList();
var refidPaxinfcound = (from px in _ctx.tblAirBookingCostBreakup
join pc in _ctx.tblAirBookingCost_1 on px.BookingCostID equals pc.BookingCostID
where pc.BookingRefID == _bookref && px.PaxType == "INF"
select new
{
PaxType = px.PaxType,
quantity = px.NoOfPAx
}).ToList();
if (refidPaxAdtcound.Count > 0)
{
_Dbconnectionresponse.ADT = Convert.ToInt16(refidPaxAdtcound[0].quantity);
}
else
{
_Dbconnectionresponse.ADT = 0;
}
if (refidPaxchdcound.Count > 0)
{
_Dbconnectionresponse.CHD = Convert.ToInt16(refidPaxchdcound[0].quantity);
}
else
{
_Dbconnectionresponse.CHD = 0;
}
if (refidPaxinfcound.Count > 0)
{
_Dbconnectionresponse.INF = Convert.ToInt16(refidPaxinfcound[0].quantity);
}
else
{
_Dbconnectionresponse.INF = 0;
}
_GetTripDetailsModelRS.DBConnectionResponse = _Dbconnectionresponse;
}
else
{
}
}
return(_GetTripDetailsModelRS);
}
