public string GetPersonOffersWithCondition(OfferCriteria offerCriteria, string language, int user_id) { string Query = " select * from personoffers_v pv join PERSON_REQUEST_V PRO on pv.order_id = PRO.order_id " + " where confirmation not in (" + (int)Enums.orderStatus.REJECTED + "," + (int)Enums.orderStatus.CONFIRMED + " ) and colorLang = '" + language + "' and OrderTYPELANG = '" + language + "' and PRO.user_id = " + user_id + " and 1=1 "; //filters if (offerCriteria.modelID != 0) { Query += " and model_id =" + offerCriteria.modelID + " "; } if (offerCriteria.orderType != 0) { Query += " and PRO.ORDERTYPE_ID =" + offerCriteria.orderType + " "; } string PriceCondition = ""; string Sort = ""; string RatingCondition = " and pv.rating >= " + offerCriteria.rate + " "; Sort = new Sort().getSortOffer(offerCriteria.sortType, "pv"); if (offerCriteria.fromPrice != 0 && offerCriteria.toPrice != 0) { PriceCondition = " and pv.price >= " + offerCriteria.fromPrice + " and pv.price <= " + offerCriteria.toPrice; } return(Query + PriceCondition + RatingCondition + Sort); }
public List <OfferDTO> GetOffers(OfferCriteria offerCriteria) { OfferQuery offerQuery = new OfferQuery(language); System.Data.DataTable OfferDT = conn_db.ReadTable(offerQuery.GetOffersWithCondition(offerCriteria.filters, offerCriteria.sort)); OfferDTO offersDTO; List <OfferDTO> Offers = new List <OfferDTO>(); if (OfferDT.Rows.Count == 0) { throw new EmptyViewException(language, Messages.GetMessage(language, TypeM.OFFER, offerM.OFFER_NOT_FOUND)); } foreach (System.Data.DataRow dataRow in OfferDT.Rows) { offersDTO = new OfferDTO(); offersDTO.OFFER_ID = dataRow["OFFER_ID"] is DBNull ? 0 : Convert.ToInt32(dataRow["OFFER_ID"]); offersDTO.ORDER_ID = dataRow["ORDER_ID"] is DBNull ? 0 : Convert.ToInt32(dataRow["ORDER_ID"]); offersDTO.PRICE = dataRow["PRICE"] is DBNull ? 0 : Convert.ToDouble(dataRow["PRICE"]); offersDTO.SUPPLIER_Name = dataRow["SUPPLIER_Name"] is DBNull ? "" : dataRow["SUPPLIER_Name"].ToString(); offersDTO.timeValue = dataRow["timeValue"] is DBNull ? 0 : Convert.ToInt32(dataRow["timeValue"]); offersDTO.timeFlag = dataRow["timeFlag"] is DBNull ? 0 : Convert.ToInt32(dataRow["timeFlag"]); offersDTO.supplierImage = dataRow["supplierImage"] is DBNull ? "" : dataRow["supplierImage"].ToString(); offersDTO.PHONENUMBER = dataRow["PHONENUMBER"] is DBNull ? "" : dataRow["PHONENUMBER"].ToString(); offersDTO.lat = dataRow["LOCATIONX"] is DBNull ? 0 : Convert.ToDouble(dataRow["LOCATIONX"]); offersDTO.lng = dataRow["LOCATIONY"] is DBNull ? 0 : Convert.ToDouble(dataRow["LOCATIONY"]); offersDTO.Rating = dataRow["RATING"] is DBNull ? 0 : Convert.ToDouble(dataRow["RATING"]); offersDTO.offerStatus = dataRow["offer_status"] is DBNull ? 0 : Convert.ToInt32(dataRow["offer_status"]); offersDTO.RateType = new Enums().checkRateTypeWords(offersDTO.Rating, language); offersDTO.RateTypeId = (int)new Enums().checkRateType(offersDTO.Rating); if (offerCriteria.lat == 0 || offerCriteria.lng == 0 || offersDTO.lat == 0 || offersDTO.lng == 0) { offersDTO.DISTANCE = 0; offersDTO.time = "0"; } else { double distanceMiles = Math.Sqrt( Math.Pow((offerCriteria.lat - offersDTO.lat) * 69, 2) + Math.Pow((offerCriteria.lng - offersDTO.lng) * 69.172, 2)); offersDTO.DISTANCE = Math.Round(distanceMiles * 1.60934, 2); offersDTO.time = new ServicesRepository(language).getTime(offerCriteria.lat + "," + offerCriteria.lng, offersDTO.lat + "," + offersDTO.lng); } Offers.Add(offersDTO); } return(Offers); }
public List <ProductItem> Search(OfferCriteria crit, int?aanbiedingId = null, int?vestigingId = null, Vestiging vestiging = null, int pageNr = 1, int pageSize = 5, List <int> propertyValues = null) { var list = QuerySearch(crit, aanbiedingId, vestigingId, vestiging, propertyValues); int total = list.Count(); return(list.ToList().Select(s => new ProductItem() { CamperAanbiedingModel = CamperAanbiedingModel.Parse(s.CamperAanbiedingModel, ""), PriceInfoModel = PriceInfoModel.Parse(s.PriceInfoModel), Total = total }).ToList()); }
public Response GetOffers(OfferCriteria offerCriteria) { try { List <OfferDTO> offers = new OfferRepository.OfferRepository(language).GetOffers(offerCriteria); return(new Response(true, Messages.GetMessage(language, Messages.TypeM.DEFAULT, defaultM.DATAGOT), offers)); } catch (EmptyViewException EmptyViewException) { return(new Response(true, EmptyViewException.RespMessage, EmptyViewException, new List <string>())); } catch (Exception ex) { return(new Response(false, Messages.GetMessage(language, Messages.TypeM.DEFAULT, Messages.defaultM.UNEXPERROR), new List <string>())); } }
public ActionResult <Response> GetSupplierOffers([FromQuery(Name = "lang")] string lang, [FromBody] OfferCriteria offerCriteria) { //check AccessToken string user_id = new UserService(lang).CheckAccessTokenUser(offerCriteria.accessToken); if (user_id != null) { return(new OfferService(lang).GetSupplierOffers(offerCriteria, Convert.ToInt32(user_id))); } else { return(new Response(false, Messages.GetMessage(lang, TypeM.DEFAULT, defaultM.WRONG_ACCESS_TOKEN), new List <string>())); } }
/// <summary> /// Create a query search /// </summary> /// <param name="crit">all criterial to search</param> /// <param name="aanbiedingId">Service id</param> /// <param name="vestigingId">location id</param> /// <param name="vestiging">loction</param> /// <param name="propertyValues">list of property value</param> /// <returns></returns> private IQueryable <ProductItemQueryable> QuerySearch(OfferCriteria crit, int?aanbiedingId = null, int?vestigingId = null, Vestiging vestiging = null, List <int> propertyValues = null) { if (crit.StartDate == DateTime.MinValue) { crit.StartDate = DateTime.Now; crit.EndDate = crit.StartDate.AddDays(7); } var aanbiedingen = _unitOfWork.CamperAanbiedingRepository.FindAll(aanbieding => aanbieding.IsActief && aanbieding.Camper.Leverancier.Status == 1); if (aanbiedingId != null) { aanbiedingen = aanbiedingen.Where(a => a.Id == (int)aanbiedingId); } var ophaalBestemming = crit.OphaalBestemmingId; if (vestiging != null) { vestigingId = vestiging.Id; ophaalBestemming = vestiging.BestemmingId; } if (vestigingId != null) { aanbiedingen = aanbiedingen.Where(a => a.Vestigings.Select(v => v.Id).Contains((int)vestigingId)); } if (aanbiedingId == null && vestigingId == null) { //if (crit.EigenschapWaarden != null && crit.EigenschapWaarden.Count > 0) //{ // var offerEigenschapWaardens = crit.EigenschapWaarden.Select(e => e.Id); // var eigenschapwaardeIds = _unitOfWork.EigenschapWaardeRepository // .FindAll(ew => offerEigenschapWaardens.Contains(ew.Id)).Select(x => x.Id); // // filter op aanbiedingen waarvan alle crit.Eigenschapwaarden voorkomen in de campereigenschappen. // aanbiedingen = aanbiedingen.Where( // aanbieding => eigenschapwaardeIds.All(eigenschapwaardeId => // aanbieding.Camper.CamperEigenschappens.Select( // camperEigenschapwaardeId => // camperEigenschapwaardeId.EigenschapWaardeId). // Contains(eigenschapwaardeId)) // ); //} if (propertyValues != null) { var eigenschapwaardeIds = _unitOfWork.EigenschapWaardeRepository .FindAll(ew => propertyValues.Contains(ew.Id)).Select(x => x.Id); // filter op aanbiedingen waarvan alle crit.Eigenschapwaarden voorkomen in de campereigenschappen. aanbiedingen = aanbiedingen.Where( aanbieding => eigenschapwaardeIds.All(eigenschapwaardeId => aanbieding.Camper.CamperEigenschappens.Select( camperEigenschapwaardeId => camperEigenschapwaardeId.EigenschapWaardeId). Contains(eigenschapwaardeId)) ); } } // Join result met prijs //IQueryable<CamperAanbiedingMetPrijzen> result; if (ophaalBestemming == 0) { ophaalBestemming = crit.ZoekBestemmingId; } int returnDestinationId = (crit.TerugbrengBestemmingId > 0 && crit.TerugbrengBestemmingId != crit.OphaalBestemmingId) ? crit.TerugbrengBestemmingId : ophaalBestemming; var resultaat = aanbiedingen.Join(_unitOfWork.context.GetPrijsInformatie(ophaalBestemming, crit.NumberOfAdults, crit.NumberOfChildrens, crit.StartDate, crit.EndDate, crit.ReserveringsDatum, returnDestinationId), aanbieding => aanbieding.Id, prijs => prijs.CamperAanbiedingId, (aanbieding, prijs) => new { aanbieding, prijs }); if (aanbiedingId == null && vestigingId == null) { resultaat = resultaat.Where(a => a.prijs.TotaalPakketPrijsPerPersoon >= crit.MinPrijs && a.prijs.TotaalPakketPrijsPerPersoon <= crit.MaxPrijs); } // 12972-25610 - Camper classification resultaat = crit.Busjetype == BusjeType.Personenbus ? resultaat.Where(c => c.aanbieding.Camper.Classification == (int)BusjeType.Personenbus) : resultaat.Where(c => c.aanbieding.Camper.Classification == (int)BusjeType.BestelBusje || c.aanbieding.Camper.Classification == (int)BusjeType.VerhuisWagen); // 12972-24136 - Check locations in case of one way rental if (crit.TerugbrengBestemmingId > 0 && crit.TerugbrengBestemmingId != crit.OphaalBestemmingId) { var suppliers = _unitOfWork.CamperAanbiedingRepository .FindAll(ca => ca.Vestigings .Where(v => v.Status == 1) .Select(v => v.BestemmingId) .Contains(crit.TerugbrengBestemmingId)) .Select(c => c.Camper.LeverancierId).Distinct(); resultaat = resultaat.Where(a => suppliers.Contains(a.aanbieding.Camper.LeverancierId)); } // Sort switch (crit.Sortering.ToLower()) { case "bestgeboekt": resultaat = resultaat.OrderBy(a => a.aanbieding.IsBestGeboekt).ThenBy(a => a.prijs.TotaalPakketPrijsPerPersoon); break; default: resultaat = resultaat.OrderBy(a => a.prijs.TotaalPakketPrijsPerPersoon); break; } return(resultaat.Select(p => new ProductItemQueryable() { CamperAanbiedingModel = p.aanbieding, PriceInfoModel = p.prijs })); }
public int CountSearch(OfferCriteria crit, int?aanbiedingId = null, int?vestigingId = null, Vestiging vestiging = null) { return(QuerySearch(crit, aanbiedingId, vestigingId, vestiging).Count()); }
public List <UserOffer> GetPrsonOffers(OfferCriteria offerCriteria, int user_id) { OfferQuery offerQuery = new OfferQuery(language); System.Data.DataTable OfferDT = conn_db.ReadTable(offerQuery.GetPersonOffersWithCondition(offerCriteria, language, user_id)); UserOffer userOffer; List <UserOffer> userOffers = new List <UserOffer>(); if (OfferDT.Rows.Count == 0) { throw new EmptyViewException(language, Messages.GetMessage(language, TypeM.OFFER, offerM.OFFER_NOT_FOUND)); } foreach (System.Data.DataRow dataRow in OfferDT.Rows) { userOffer = new UserOffer(); userOffer.ORDERTYPE_NAME = dataRow["ORDERTYPE_NAME"] is DBNull ? "" : dataRow["ORDERTYPE_NAME"].ToString(); userOffer.colorname = dataRow["COLOR_NAME"] is DBNull ? "" : dataRow["COLOR_NAME"].ToString(); if (language == Messages.language.ar.ToString()) { userOffer.brandname = dataRow["BRANDNAME_AR"].ToString(); userOffer.modelname = dataRow["MODELNAME_AR"].ToString(); } else { userOffer.brandname = dataRow["BRANDNAME_EN"].ToString(); userOffer.modelname = dataRow["MODELNAME_EN"].ToString(); } userOffer.OFFER_ID = dataRow["OFFER_ID"] is DBNull ? 0 : Convert.ToInt32(dataRow["OFFER_ID"]); userOffer.ORDER_ID = dataRow["ORDER_ID"] is DBNull ? 0 : Convert.ToInt32(dataRow["ORDER_ID"]); try { userOffer.ORDER_DATE = Convert.ToDateTime(dataRow["ORDERDATE"].ToString()).ToString("dd-MM-yyyy"); } catch (Exception ex) { userOffer.ORDER_DATE = ""; } userOffer.ORDER_NO = dataRow["ORDER_IDENTITY"] is DBNull ? 0 : Convert.ToInt64(dataRow["ORDER_IDENTITY"]); userOffer.PRICE = dataRow["PRICE"] is DBNull ? 0 : Convert.ToDouble(dataRow["PRICE"]); userOffer.SUPPLIER_Name = dataRow["SUPPLIER_Name"] is DBNull ? "" : dataRow["SUPPLIER_Name"].ToString(); userOffer.timeValue = dataRow["timeValue"] is DBNull ? 0 : Convert.ToInt32(dataRow["timeValue"]); userOffer.timeFlag = dataRow["timeFlag"] is DBNull ? 0 : Convert.ToInt32(dataRow["timeFlag"]); userOffer.supplierImage = dataRow["supplierImage"] is DBNull ? "" : dataRow["supplierImage"].ToString(); userOffer.PHONENUMBER = dataRow["PHONENUMBER"] is DBNull ? "" : dataRow["PHONENUMBER"].ToString(); userOffer.lat = dataRow["LOCATIONX"] is DBNull ? 0 : Convert.ToDouble(dataRow["LOCATIONX"]); userOffer.lng = dataRow["LOCATIONY"] is DBNull ? 0 : Convert.ToDouble(dataRow["LOCATIONY"]); userOffer.Rating = dataRow["RATING"] is DBNull ? 0 : Convert.ToDouble(dataRow["RATING"]); userOffer.Rater_No = dataRow["Rater_NO"] is DBNull ? 0 : Convert.ToInt32(dataRow["Rater_NO"]); userOffer.offerStatus = dataRow["offer_status"] is DBNull ? 0 : Convert.ToInt32(dataRow["offer_status"]); userOffer.RateType = new Enums().checkRateTypeWords(userOffer.Rating, language); userOffer.RateTypeId = (int)new Enums().checkRateType(userOffer.Rating); if (offerCriteria.lat == 0 || offerCriteria.lng == 0 || userOffer.lat == 0 || userOffer.lng == 0) { userOffer.DISTANCE = 0; userOffer.time = "0"; } else { double distanceMiles = Math.Sqrt( Math.Pow((offerCriteria.lat - userOffer.lat) * 69, 2) + Math.Pow((offerCriteria.lng - userOffer.lng) * 69.172, 2)); userOffer.DISTANCE = Math.Round(distanceMiles * 1.60934, 2); userOffer.time = new ServicesRepository(language).getTime(offerCriteria.lat + "," + offerCriteria.lng, userOffer.lat + "," + userOffer.lng); } userOffers.Add(userOffer); } return(userOffers); }
public List <UserOffer> GetSupplierOffers(OfferCriteria offerCriteria, int user_id) { OfferQuery offerQuery = new OfferQuery(language); OrderQuery orderQuery = new OrderQuery(language); System.Data.DataTable OfferDT = new System.Data.DataTable(); switch (offerCriteria.offerType) { case (int)OfferType.offerNotAccepted: OfferDT = conn_db.ReadTable(offerQuery.GetsupplierOffersUnConfirmed(user_id, language)); break; case (int)OfferType.offerAccepted: OfferDT = conn_db.ReadTable(offerQuery.GetsupplierOffersConfirmed(user_id, language)); break; case (int)OfferType.offerIsFixing: OfferDT = conn_db.ReadTable(offerQuery.GetsupplierOffersStart(user_id, language)); break; case (int)OfferType.offerFinishFixing: OfferDT = conn_db.ReadTable(offerQuery.GetsupplierOffersfinish(user_id, language)); break; default: OfferDT = conn_db.ReadTable(offerQuery.GetsupplierOffersUnConfirmed(user_id, language)); break; } UserOffer userOffer; List <UserOffer> userOffers = new List <UserOffer>(); if (OfferDT.Rows.Count == 0) { throw new EmptyViewException(language, Messages.GetMessage(language, TypeM.OFFER, offerM.OFFER_NOT_FOUND)); } foreach (System.Data.DataRow dataRow in OfferDT.Rows) { userOffer = new UserOffer(); userOffer.ORDERTYPE_NAME = dataRow["ORDERTYPE_NAME"] is DBNull ? "" : dataRow["ORDERTYPE_NAME"].ToString(); userOffer.colorname = dataRow["COLOR_NAME"] is DBNull ? "" : dataRow["COLOR_NAME"].ToString(); userOffer.firstName = dataRow["FIRSTNAME"].ToString(); userOffer.lastName = dataRow["LASTNAME"].ToString(); userOffer.carImage = dataRow["CARIMAGE"].ToString(); userOffer.userImage = dataRow["userIMAGE"].ToString(); if (language == Messages.language.ar.ToString()) { userOffer.brandname = dataRow["BRANDNAME_AR"].ToString(); userOffer.modelname = dataRow["MODELNAME_AR"].ToString(); } else { userOffer.brandname = dataRow["BRANDNAME_EN"].ToString(); userOffer.modelname = dataRow["MODELNAME_EN"].ToString(); } userOffer.OFFER_ID = dataRow["OFFER_ID"] is DBNull ? 0 : Convert.ToInt32(dataRow["OFFER_ID"]); userOffer.ORDER_ID = dataRow["ORDER_ID"] is DBNull ? 0 : Convert.ToInt32(dataRow["ORDER_ID"]); userOffer.PRICE = dataRow["PRICE"] is DBNull ? 0 : Convert.ToDouble(dataRow["PRICE"]); userOffer.SUPPLIER_Name = dataRow["SUPPLIER_Name"] is DBNull ? "" : dataRow["SUPPLIER_Name"].ToString(); userOffer.timeValue = dataRow["timeValue"] is DBNull ? 0 : Convert.ToInt32(dataRow["timeValue"]); userOffer.timeFlag = dataRow["timeFlag"] is DBNull ? 0 : Convert.ToInt32(dataRow["timeFlag"]); userOffer.supplierImage = dataRow["supplierImage"] is DBNull ? "" : dataRow["supplierImage"].ToString(); userOffer.PHONENUMBER = dataRow["PHONENUMBER"] is DBNull ? "" : dataRow["PHONENUMBER"].ToString(); userOffer.lat = dataRow["LOCATIONX"] is DBNull ? 0 : Convert.ToDouble(dataRow["LOCATIONX"]); userOffer.lng = dataRow["LOCATIONY"] is DBNull ? 0 : Convert.ToDouble(dataRow["LOCATIONY"]); userOffer.Rating = dataRow["RATING"] is DBNull ? 0 : Convert.ToDouble(dataRow["RATING"]); userOffer.offerStatus = dataRow["offer_status"] is DBNull ? 0 : Convert.ToInt32(dataRow["offer_status"]); userOffer.RateType = new Enums().checkRateTypeWords(userOffer.Rating, language); userOffer.RateTypeId = (int)new Enums().checkRateType(userOffer.Rating); if (offerCriteria.lat == 0 || offerCriteria.lng == 0 || userOffer.lat == 0 || userOffer.lng == 0) { userOffer.DISTANCE = 0; userOffer.time = "0"; } else { double distanceMiles = Math.Sqrt( Math.Pow((offerCriteria.lat - userOffer.lat) * 69, 2) + Math.Pow((offerCriteria.lng - userOffer.lng) * 69.172, 2)); userOffer.DISTANCE = Math.Round(distanceMiles * 1.60934, 2); userOffer.time = new ServicesRepository(language).getTime(offerCriteria.lat + "," + offerCriteria.lng, userOffer.lat + "," + userOffer.lng); } userOffers.Add(userOffer); } return(userOffers); }