/// <returns></returns>
public static IEnumerable <ServiceProfessionalService> GetList(int serviceProfessionalUserID, int jobTitleID = -1, int packageID = -1, int pricingTypeID = -1, bool?isAddon = null, Visibility clientVisibility = null)
{
clientVisibility = clientVisibility ?? Visibility.BookableByPublic();
var packages = GetPricingPackagesByProviderPosition(serviceProfessionalUserID, jobTitleID, packageID, pricingTypeID, isAddon, clientVisibility);
return(BindDetailsToProviderPackage(packages).ToList());
}
private static dynamic QueryPackagesByMulti(Database db, int providerUserID, int positionID = -1, int packageID = -1, int pricingTypeID = -1, bool?isAddon = null, Visibility clientVisibility = null)
{
// By default, return pricings that are bookable by the public
clientVisibility = clientVisibility ?? Visibility.BookableByPublic();
const string SQLGetPackagesByMulti = SQLSelectFromPackage + @"
INNER JOIN
PricingType As PT
ON P.PricingTypeID = PT.PricingTypeID
AND P.LanguageID = PT.LanguageID
AND P.CountryID = PT.CountryID
INNER JOIN
PositionPricingType AS PPT
ON PPT.PositionID = P.PositionID
AND PPT.PricingTypeID = PT.PricingTypeID
AND PPT.LanguageID = PT.LanguageID
AND PPT.CountryID = PT.CountryID
AND PPT.Active = 1
WHERE p.ProviderUserID = @0
AND (@1 = -1 OR P.PositionID = @1)
AND
p.LanguageID = @2 AND p.CountryID = @3
AND
p.Active = 1
AND (@4 = -1 OR p.ProviderPackageID = @4)
AND (@5 = -1 OR p.PricingTypeID = @5)
AND (@6 = -1 OR P.IsAddOn = @6)
AND P.VisibleToClientID IN ({0})
ORDER BY PT.DisplayRank ASC
";
// Database.Query does not natively expand SQL IN clause list, so do it manually :(
string query = String.Format(SQLGetPackagesByMulti, String.Join(",", clientVisibility.VisibleToClientIDs()));
return(db.Query(query,
providerUserID,
positionID,
LcData.GetCurrentLanguageID(),
LcData.GetCurrentCountryID(),
packageID,
pricingTypeID,
(isAddon.HasValue ? (isAddon.Value ? 1 : 0) : -1)
));
}