public FlightEnquiryDetail GetFlightEnquiryDetail(string Id) { FlightEnquiryDetail obj = new FlightEnquiryDetail(); try { DataSet ds = new DataSet(); ds = quoteDAO.GetFlightEnquiryDetail(Id); ds.Tables[0].TableName = "Availability"; ds.Tables[1].TableName = "ArrivalTime"; ds.Tables[2].TableName = "DepartureTime"; ds.Tables[3].TableName = "Duration"; ds.Tables[4].TableName = "Stops"; ds.Tables[5].TableName = "Fare"; Availability objAvailability = new Availability(); List lstAvailability = new List (); foreach (DataRow dr in ds.Tables[11].Rows) { string TripId = dr["TripId"].ToString(); var result = from r in ds.Tables[0].AsEnumerable() where r.Field ("TripId") == TripId select r; DataTable dtResult = result.CopyToDataTable(); var response = this.Request.CreateResponse(HttpStatusCode.OK); string json = JsonConvert.SerializeObject(dtResult, Formatting.Indented); response.Content = new StringContent(json, Encoding.UTF8, "application/json"); List lst = JsonConvert.DeserializeObject>(json); objAvailability = new Availability(); foreach (AvailabilitySegment item in lst) { //var FarePrices = from r in ds.Tables[10].AsEnumerable() // where r.Field("JourneyNo") == item.JourneyNo.ToString() // select r; //item.FarePrice= var TotalFlyingHours = from r in ds.Tables[4].AsEnumerable() where r.Field("JourneyNo") == item.JourneyNo.ToString() select r; if (TotalFlyingHours.Count() > 0) { DataTable dtTotalFlyingHours = TotalFlyingHours.CopyToDataTable(); item.TotalFlyingHours = dtTotalFlyingHours.Rows[0]["FilterValue"].ToString(); } else { item.TotalFlyingHours = ""; } try { var TotalLayoverHours = from r in ds.Tables[5].AsEnumerable() where r.Field("JourneyNo") == item.JourneyNo.ToString() select r; if (TotalLayoverHours.Count() > 0) { DataTable dtTotalLayoverHours = TotalLayoverHours.CopyToDataTable(); item.TotalLayoverHours = dtTotalLayoverHours.Rows[0]["FilterValue"].ToString(); } else item.TotalLayoverHours = ""; } catch { item.TotalLayoverHours = ""; // continue; } var TotalFlightHours = from r in ds.Tables[6].AsEnumerable() where r.Field("JourneyNo") == item.JourneyNo.ToString() select r; if (TotalFlightHours.Count() > 0) { DataTable dtTotalFlightHours = TotalFlightHours.CopyToDataTable(); item.TotalFlightHours = dtTotalFlightHours.Rows[0]["FilterValue"].ToString(); } else { item.TotalFlightHours = ""; } var FarePrices = from r in ds.Tables[10].AsEnumerable() where r.Field("JourneyNo") == item.JourneyNo.ToString() select r; DataTable dtFarePrices = FarePrices.CopyToDataTable(); DataView viewFarePrices = new DataView(dtFarePrices); DataTable distinctFares = viewFarePrices.ToTable(true, "FilterValue"); // var distinctTypeIDs = farePrices.Select(x => x.typeID).Distinct(); List farePrices = new List(); foreach (DataRow drr in distinctFares.Rows) { farePrices.Add(drr["FilterValue"].ToString()); } item.FarePrices = farePrices; if (Convert.ToInt32(item.Leg) > 1) { if (ds.Tables[12].Rows.Count > 0) { var fares = from r in ds.Tables[12].AsEnumerable() where r.Field("FlightEnquiryDetailId") == item.Id.ToString() select r; if (fares.ToList().Count > 0) { DataTable dtFares = fares.CopyToDataTable(); var responseFares = this.Request.CreateResponse(HttpStatusCode.OK); string jsonFares = JsonConvert.SerializeObject(dtFares, Formatting.Indented); responseFares.Content = new StringContent(jsonFares, Encoding.UTF8, "application/json"); List lstFares = JsonConvert.DeserializeObject>(jsonFares); // objAvailability.fares = lstFares; item.fares = lstFares; } } } else { if (ds.Tables[12].Rows.Count > 0) { var fares = from r in ds.Tables[12].AsEnumerable() where r.Field("FlightEnquiryDetailId") == item.Id.ToString() select r; if (fares.ToList().Count > 0) { DataTable dtFares = fares.CopyToDataTable(); var responseFares = this.Request.CreateResponse(HttpStatusCode.OK); string jsonFares = JsonConvert.SerializeObject(dtFares, Formatting.Indented); responseFares.Content = new StringContent(jsonFares, Encoding.UTF8, "application/json"); List lstFares = JsonConvert.DeserializeObject>(jsonFares); item.fares = lstFares; } } } } objAvailability.flights = lst; //if (ds.Tables[10].Rows.Count > 0) //{ // var fares = from r in ds.Tables[10].AsEnumerable() // where r.Field("TripId") == TripId // select r; // if (fares.ToList().Count > 0) // { // DataTable dtFares = fares.CopyToDataTable(); // var responseFares = this.Request.CreateResponse(HttpStatusCode.OK); // string jsonFares = JsonConvert.SerializeObject(dtFares, Formatting.Indented); // responseFares.Content = new StringContent(jsonFares, Encoding.UTF8, "application/json"); // List lstFares = JsonConvert.DeserializeObject>(jsonFares); // objAvailability.fares = lstFares; // } //} //if (ds.Tables[10].Rows.Count > 0) //{ // var fares = from r in ds.Tables[10].AsEnumerable() // where r.Field("FlightEnquiryDetailId") == TripId // select r; // if (fares.ToList().Count > 0) // { // DataTable dtFares = fares.CopyToDataTable(); // var responseFares = this.Request.CreateResponse(HttpStatusCode.OK); // string jsonFares = JsonConvert.SerializeObject(dtFares, Formatting.Indented); // responseFares.Content = new StringContent(jsonFares, Encoding.UTF8, "application/json"); // List lstFares = JsonConvert.DeserializeObject>(jsonFares); // objAvailability.fares = lstFares; // } //} //filters List lstFV = new List(); List lstFR = new List(); FlightResultFilter fr = new FlightResultFilter(); fr.FilterType = "Arrival Time"; fr.KeyField = "ARRITIM"; foreach (DataRow dataRow in ds.Tables[1].Rows) { FlightFilterValue fv = new FlightFilterValue(); fv.FilterValue = dataRow["FilterValue"].ToString().Replace("\r\n", ""); lstFV.Add(fv); } fr.FilterValues = lstFV; lstFR.Add(fr); lstFV = new List(); fr = new FlightResultFilter(); fr.FilterType = "Departure Time"; fr.KeyField = "DEEPTIM"; foreach (DataRow dataRow in ds.Tables[2].Rows) { FlightFilterValue fv = new FlightFilterValue(); fv.FilterValue = dataRow["FilterValue"].ToString().Replace("\r\n", ""); lstFV.Add(fv); } fr.FilterValues = lstFV; lstFR.Add(fr); lstFV = new List(); fr = new FlightResultFilter(); fr.FilterType = "Total Flying Hours"; fr.KeyField = "TotalFlyingHours"; DataView view = new DataView(ds.Tables[4]); DataTable distinctValues = view.ToTable(true, "FilterValue"); foreach (DataRow dataRow in distinctValues.Rows) { FlightFilterValue fv = new FlightFilterValue(); fv.FilterValue = dataRow["FilterValue"].ToString().Replace("\r\n", ""); lstFV.Add(fv); } fr.FilterValues = lstFV; lstFR.Add(fr); lstFV = new List(); fr = new FlightResultFilter(); fr.FilterType = "Total Layover Hours"; fr.KeyField = "TotalLayoverHours"; view = new DataView(ds.Tables[5]); distinctValues = view.ToTable(true, "FilterValue"); foreach (DataRow dataRow in distinctValues.Rows) { FlightFilterValue fv = new FlightFilterValue(); fv.FilterValue = dataRow["FilterValue"].ToString().Replace("\r\n", ""); lstFV.Add(fv); } fr.FilterValues = lstFV; lstFR.Add(fr); lstFV = new List(); fr = new FlightResultFilter(); fr.FilterType = "Total Flight Hours"; fr.KeyField = "TotalFlightHours"; view = new DataView(ds.Tables[6]); distinctValues = view.ToTable(true, "FilterValue"); foreach (DataRow dataRow in distinctValues.Rows) { FlightFilterValue fv = new FlightFilterValue(); fv.FilterValue = dataRow["FilterValue"].ToString().Replace("\r\n", ""); lstFV.Add(fv); } fr.FilterValues = lstFV; lstFR.Add(fr); lstFV = new List(); fr = new FlightResultFilter(); fr.FilterType = "Fare"; fr.KeyField = "AdtFareType"; foreach (DataRow dataRow in ds.Tables[7].Rows) { FlightFilterValue fv = new FlightFilterValue(); fv.FilterValue = dataRow["FilterValue"].ToString().Replace("\r\n", ""); lstFV.Add(fv); } fr.FilterValues = lstFV; lstFR.Add(fr); lstFV = new List(); fr = new FlightResultFilter(); fr.FilterType = "AirLine"; fr.KeyField = "AirLineName"; foreach (DataRow dataRow in ds.Tables[8].Rows) { FlightFilterValue fv = new FlightFilterValue(); fv.FilterValue = dataRow["FilterValue"].ToString().Replace("\r\n", ""); lstFV.Add(fv); } fr.FilterValues = lstFV; lstFR.Add(fr); lstFV = new List(); fr = new FlightResultFilter(); fr.FilterType = "Craft Type"; fr.KeyField = "EQ"; foreach (DataRow dataRow in ds.Tables[9].Rows) { FlightFilterValue fv = new FlightFilterValue(); fv.FilterValue = dataRow["FilterValue"].ToString().Replace("\r\n", ""); lstFV.Add(fv); } fr.FilterValues = lstFV; lstFR.Add(fr); lstFV = new List(); fr = new FlightResultFilter(); fr.FilterType = "Fare Price"; fr.KeyField = "FarePrices"; view = new DataView(ds.Tables[10]); distinctValues = view.ToTable(true, "FilterValue"); foreach (DataRow dataRow in distinctValues.Rows) { FlightFilterValue fv = new FlightFilterValue(); fv.FilterValue = dataRow["FilterValue"].ToString().Replace("\r\n", ""); lstFV.Add(fv); } fr.FilterValues = lstFV; lstFR.Add(fr); lstFV = new List(); fr = new FlightResultFilter(); fr.FilterType = "Refund"; fr.KeyField = "ProductClass"; view = new DataView(ds.Tables[13]); distinctValues = view.ToTable(true, "FilterValue"); foreach (DataRow dataRow in distinctValues.Rows) { FlightFilterValue fv = new FlightFilterValue(); fv.FilterValue = dataRow["FilterValue"].ToString().Replace("\r\n", ""); lstFV.Add(fv); } fr.FilterValues = lstFV; lstFR.Add(fr); objAvailability.resultFilters = lstFR; lstAvailability.Add(objAvailability); } //var response = this.Request.CreateResponse(HttpStatusCode.OK); //string json = JsonConvert.SerializeObject(ds.Tables[0], Formatting.Indented); //response.Content = new StringContent(json, Encoding.UTF8, "application/json"); //List lst = JsonConvert.DeserializeObject>(json); obj.availabilities = lstAvailability; // string Json = JsonConvert.SerializeObject(ds, Formatting.Indented); //response.Content = new StringContent(Json, Encoding.UTF8, "application/json"); //List lstFV = new List(); //List lstFR = new List(); //FlightResultFilter fr = new FlightResultFilter(); //fr.FilterType = "Arrival Time"; //fr.KeyField = "ARRITIM"; //foreach (DataRow dataRow in ds.Tables[1].Rows) //{ // FlightFilterValue fv = new FlightFilterValue(); // fv.FilterValue = dataRow["FilterValue"].ToString().Replace("\r\n", ""); // lstFV.Add(fv); //} //fr.FilterValues = lstFV; //lstFR.Add(fr); //lstFV = new List(); //fr = new FlightResultFilter(); //fr.FilterType = "Departure Time"; //fr.KeyField = "DEEPTIM"; //foreach (DataRow dataRow in ds.Tables[2].Rows) //{ // FlightFilterValue fv = new FlightFilterValue(); // fv.FilterValue = dataRow["FilterValue"].ToString().Replace("\r\n", ""); // lstFV.Add(fv); //} //fr.FilterValues = lstFV; //lstFR.Add(fr); List lstFRouter = new List(); List lstFVouter = new List(); FlightResultFilter frouter = new FlightResultFilter(); frouter.FilterType = "Stops"; frouter.KeyField = "Stops"; foreach (DataRow dataRow in ds.Tables[3].Rows) { FlightFilterValue fv = new FlightFilterValue(); fv.FilterValue = dataRow["FilterValue"].ToString().Replace("\r\n", ""); lstFVouter.Add(fv); } frouter.FilterValues = lstFVouter; lstFRouter.Add(frouter); obj.resultFilters = lstFRouter; return obj; } catch (Exception e) { return null; } }