public ActionResult PackagesPartialView(ServiceTypeEnum?st, IEnumerable <int> CatID, IEnumerable <int> ActID, int?Gsize, int?diff, int?dur, int?GuideLanguageID, IEnumerable <int> AttractID, IEnumerable <int> FacilityID, decimal?maxPrice, decimal?minPrice, int?NoPax, int?large, int?small, int?hasAc, int?HasCarrier, IEnumerable <int> DestIds, int?IsBike)
{
string Dests = "";
if (DestIds != null)
{
Dests = string.Join(",", DestIds.ToArray());
}
PetaPoco.Sql MainSql = new PetaPoco.Sql();
PetaPoco.Sql FromSql = new PetaPoco.Sql();
PetaPoco.Sql WhereSql = new PetaPoco.Sql();
ViewBag.st = st;
if (st == ServiceTypeEnum.Accomodation)
{
ViewBag.ServiceTitle = "Accomodations";
MainSql = new PetaPoco.Sql(" Select Count(rv.ReviewID) as TotalReview,Avg(rv.Value) as AvgReview,a.AccomodationID as ServiceId,@0 as ServiceTypeId, " +
"a.AccomName as ServiceName, substring(a.Description, 0, 100) + '...' as ServiceDescription," +
"g.geoName as ServiceGeoName, min(pr.price) as price ", (int)ServiceTypeEnum.Accomodation);
FromSql = new PetaPoco.Sql("from Accomodation a left join Review rv on rv.ServiceID = a.AccomodationID " +
$"left join GeoTree g on a.geoTreeId = g.GeoTreeId and a.GeoTreeID in (SELECT GeoTreeID FROM STRING_SPLIT('{Dests}', ',') CROSS APPLY dbo.GetChildGeos(value))" +
"left join Prices pr on pr.PriceID = (select top 1 PriceID from Prices where a.AccomodationID = Prices.ServiceID and Prices.WEF < GetDate() order by Prices.WEF desc) " +
"left join OptionType ot on ot.OptionTypeID = pr.OptionTypeID " +
"left join Facility_Accomodation fa on a.AccomodationID = fa.AccomodationID " +
"left join Facility f on f.FacilityID = fa.FacilityID ");
WhereSql = new PetaPoco.Sql($"where ot.ServiceTypeID=@0 ", ServiceTypeEnum.Accomodation);
if (maxPrice != null && minPrice != null)
{
WhereSql.Append(" and Price Between @0 and @1", minPrice, maxPrice);
}
if (FacilityID != null)
{
WhereSql.Append(" and fa.FacilityID in (@0)", FacilityID.ToArray());
}
MainSql.Append(FromSql);
MainSql.Append(WhereSql);
MainSql.Append(" Group by a.AccomodationID, a.AccomName, [Description], g.geoName ");
var accom = db.Query <AccomodationDets>(MainSql);
accom = accom.OrderBy(a => a.AccomName);
}
if (st == ServiceTypeEnum.CarBike)
{
ViewBag.ServiceTitle = "Car And Bikes Rental";
MainSql = new PetaPoco.Sql("Select Count(rv.ReviewID) as TotalReview,Avg(rv.Value) as AvgReview,[CarBikeID] as ServiceId, [CarBikeName] as ServiceName,@0 as ServiceTypeId, g.geoName as ServiceGeoName, " +
"substring(description, 0, 100) + '...' as ServiceDescription, min(pr.price) as price ", (int)ServiceTypeEnum.CarBike);
FromSql = new PetaPoco.Sql("From CarBike c left join Review rv on rv.ServiceID = c.CarBikeID " +
"left join GeoTree g on c.geoTreeId=c.GeoTreeId " +
"left join Prices pr on pr.PriceID = (select top 1 PriceID from Prices where c.CarBikeID = Prices.ServiceID " +
"and Prices.WEF < GetDate() order by Prices.WEF desc) " +
"left join OptionType ot on ot.OptionTypeID = pr.OptionTypeID ");
WhereSql = new PetaPoco.Sql($"where ot.ServiceTypeID=@0 and c.GeoTreeID in (SELECT GeoTreeID " +
$"FROM STRING_SPLIT('{Dests}', ',') CROSS APPLY dbo.GetChildGeos(value)) ", ServiceTypeEnum.CarBike);
if (maxPrice != null && minPrice != null)
{
WhereSql.Append(" and Price Between @0 and @1 ", minPrice, maxPrice);
}
if (NoPax != null)
{
WhereSql.Append(" and c.NoPax<=@0", NoPax);
}
if (large != null)
{
WhereSql.Append(" and c.NoLargeBags<=@0", large);
}
if (small != null)
{
WhereSql.Append(" and c.NoSmallBags<=@0", small);
}
if (hasAc != null)
{
WhereSql.Append(" and c.HasAc=@0", hasAc);
}
if (HasCarrier != null)
{
WhereSql.Append(" and c.Hascarrier=@0", HasCarrier);
}
if (IsBike != null)
{
WhereSql.Append(" and c.IsBike=@0", IsBike);
}
MainSql.Append(FromSql);
MainSql.Append(WhereSql);
MainSql.Append(" Group by c.CarBikeID, c.CarBikeName, [Description], g.geoName ");
}
if (st == ServiceTypeEnum.Packages || st == ServiceTypeEnum.SightSeeing || st == ServiceTypeEnum.Cruise)
{
ViewBag.ServiceTitle = "Our Best Tours And Excursions";
MainSql = new PetaPoco.Sql("Select Count(rv.ReviewID) as TotalReview,Avg(rv.Value) as AvgReview," +
" pv.PackageID as ServiceId,pv.ServiceTypeID ,pv.PackageName as ServiceName, " +
"substring(pv.Description, 0, 100) + '...' as ServiceDescription, min(coalesce(pr.price, 0)) as price ");
FromSql = new PetaPoco.Sql("from Package pv left join Review rv on rv.ServiceID = pv.PackageID " +
"left join Prices pr on pr.PriceID = (select top 1 PriceID from Prices " +
"where pv.PackageID = Prices.ServiceID and Prices.WEF < GetDate() order by Prices.WEF desc) " +
"left join OptionType ot on ot.OptionTypeID = pr.OptionTypeID " +
"left join Package_Category pc on pc.PackageID = pv.PackageID " +
"left join Category c on c.CategoryID = pc.CategoryID " +
"left join Package_Activity pa on pa.PackageID = pv.PackageID " +
"left join Activity a on a.ActivityID = pa.ActivityID " +
"left join Package_Attraction pat on pat.PackageID = pv.PackageID " +
"left join Attraaction at on at.AttractionID = pat.AttractionID " +
"left join Package_Language pl on pl.PackageId = pv.PackageID " +
"left join GuideLanguage gl on gl.GuideLanguageID = pl.GuideLanguageId " +
"left join Package_GeoTree pg on pg.PackageID = pv.PackageID");
WhereSql = new PetaPoco.Sql(" where pv.ServiceTypeID=@0", (int)st);
//WhereSql.Append(" and p.PackageID = pr.ServiceID and ot.OptionTypeID = pr.OptionTypeID and pr.PriceID = (select top 1 PriceID from Prices where p.PackageID = Prices.ServiceID and ot.OptionTypeID = Prices.OptionTypeID and Prices.WEF<GetDate() order by Prices.WEF desc) ");
if (maxPrice != null && minPrice != null)
{
WhereSql.Append(" and Price Between @0 and @1 ", minPrice, maxPrice);
}
if (CatID != null)
{
WhereSql.Append(" and pc.CategoryID in (@0)", CatID.ToArray());
}
if (ActID != null)
{
WhereSql.Append(" and pa.ActivityID in (@0)", ActID.ToArray());
}
if (AttractID != null)
{
WhereSql.Append(" and pat.AttractionID in (@0)", AttractID.ToArray());
}
if (Gsize != null)
{
WhereSql.Append(" and pv.GroupSize <= @0", Gsize);
}
if (diff != null)
{
WhereSql.Append(" and pv.Dificulty <= @0", diff);
}
if (dur != null)
{
WhereSql.Append(" and pv.Duration <= @0", dur);
}
if (GuideLanguageID != null)
{
WhereSql.Append(" and pl.GuideLanguageId =@0 ", GuideLanguageID);
}
//Filter for destination
WhereSql.Append($" and pg.GeoTreeId in (SELECT GeoTreeID FROM STRING_SPLIT('{Dests}', ',') CROSS APPLY dbo.GetChildGeos(value))");
MainSql.Append(FromSql);
MainSql.Append(WhereSql);
MainSql.Append(" Group by pv.PackageID, pv.ServiceTypeID,pv.PackageName, pv.[Description] ");
}
MainSql.Append(" order by min(pr.price)");
var apc = db.Fetch <AccomPackCarBike>(MainSql);
foreach (var i in apc)
{
i.ServicePic = db.FirstOrDefault <PictureDets>("Select Top 1 * From Picture Where ServiceID=@0 and ServiceTypeID=@1", i.ServiceID, i.ServiceTypeID)?.PictureName ?? "";
if (string.IsNullOrWhiteSpace(i.ServiceGeoName))
{
i.ServiceGeoName = db.First <string>("Select GeoName From GeoTree g,Package_GeoTree pg where pg.PackageID=@0 and g.GeoTreeID = pg.GeoTreeID", i.ServiceID);
}
i.Attributes = db.Fetch <Attribute>("Select * from Attribute a, Package_attribute pa where a.attributeID=pa.attributeID and pa.packageID=@0 and pa.serviceTypeId=@1", i.ServiceID, i.ServiceTypeID);
i.Icons = db.Fetch <string>("select IconPath from Icons where ServiceId=@0 and ServiceTypeId=@1", i.ServiceID, i.ServiceTypeID);
}
return(PartialView(apc));
}
