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);
}
