public static ServiceProfessionalSearchResult FromDB(dynamic record, LcRest.ServiceProfessionalService.Visibility visibility)
{
if (record == null)
{
return(null);
}
var r = new ServiceProfessionalSearchResult
{
userID = record.userID,
jobTitleID = record.jobTitleID,
firstName = record.firstName,
lastName = record.lastName,
lastInitial = record.lastInitial,
publicBio = record.publicBio,
businessName = record.businessName,
instantBooking = record.instantBooking,
jobTitleNameSingular = record.jobTitleNameSingular,
otherJobTitles = record.otherJobTitles,
allJobTitles = record.allJobTitles,
distance = record.distance,
clientVisibility = visibility,
updatedDate = record.updatedDate
};
r.FillLinks();
return(r);
}
public static IEnumerable <ServiceProfessionalSearchResult> SearchByJobTitleID(int JobTitleID, decimal origLat, decimal origLong, int SearchDistance, Locale locale, LcRest.ServiceProfessionalService.Visibility visibility = null)
{
visibility = visibility ?? LcRest.ServiceProfessionalService.Visibility.BookableByPublic();
using (var db = new LcDatabase())
{
return(db.Query(@"
DECLARE @JobTitleID int
SET @JobTitleID = @0
DECLARE @origLat DECIMAL(12, 9)
SET @origLat=@1
DECLARE @origLong DECIMAL(12, 9)
SET @origLong=@2
DECLARE @SearchDistance int
SET @SearchDistance = @3
DECLARE @LanguageID int
SET @LanguageID = @4
DECLARE @CountryID int
SET @CountryID = @5
DECLARE @orig geography = geography::Point(@origLat, @origLong, 4326)
SELECT
u.userID,
u.updatedDate,
p.positionID as JobTitleID,
u.firstName,
u.lastName,
LEFT(u.lastName, 1) + '.' as lastInitial,
u.publicBio,
u.businessName,
upp.InstantBooking,
p.PositionSingular As jobTitleNameSingular,
otherJobTitles=LTRIM(STUFF((SELECT ', ' + PositionSingular FROM Positions As P0 INNER JOIN UserProfilePositions As UP0 ON P0.PositionID = UP0.PositionID WHERE UP0.UserID = u.UserID AND P0.LanguageID = @LanguageID AND P0.CountryID = @CountryID AND UP0.StatusID = 1 AND UP0.Active = 1 AND P0.PositionID != @JobTitleID AND P0.Active = 1 AND P0.Approved <> 0 FOR XML PATH('')) , 1 , 1 , '' )),
allJobTitles=LTRIM(STUFF((SELECT ', ' + PositionSingular FROM Positions As P0 INNER JOIN UserProfilePositions As UP0 ON P0.PositionID = UP0.PositionID WHERE UP0.UserID = u.UserID AND P0.LanguageID = @LanguageID AND P0.CountryID = @CountryID AND UP0.StatusID = 1 AND UP0.Active = 1 AND P0.Active = 1 AND P0.Approved <> 0 FOR XML PATH('')) , 1 , 1 , '' )),
MIN(ROUND(@orig.STDistance(geography::Point(a.Latitude, a.Longitude, 4326))/1000,1)) as distance
FROM dbo.users u
INNER JOIN dbo.userprofilepositions upp
ON u.UserID = upp.UserID
INNER JOIN serviceaddress sa
ON sa.UserID = upp.UserID
AND sa.PositionID = upp.PositionID
INNER JOIN
address a
ON a.addressID=sa.addressID
AND a.CountryID=upp.CountryID
INNER JOIN positions p
ON upp.PositionID = p.PositionID
AND upp.LanguageID = p.LanguageID
AND upp.CountryID = p.CountryID
WHERE
upp.LanguageID = @LanguageID
AND upp.CountryID = @CountryID
AND u.Active = 1
AND upp.Active = 1
AND upp.StatusID = 1
AND p.Active = 1
AND p.positionID = @JobTitleID
AND a.Latitude IS NOT NULL
AND a.Longitude IS NOT NULL
AND @orig.STDistance(geography::Point(a.Latitude, a.Longitude, 4326))/1000 <=
(CASE WHEN (sa.ServicesPerformedAtLocation = 0 AND sa.ServiceRadiusFromLocation IS NOT NULL) THEN
CONVERT(FLOAT, sa.ServiceRadiusFromLocation)
ELSE
@SearchDistance
END)
GROUP BY
u.userID,
p.positionID,
u.firstName,
u.lastName,
u.publicBio,
u.businessName,
upp.InstantBooking,
p.PositionSingular,
u.updatedDate
", JobTitleID, origLat, origLong, SearchDistance, locale.languageID, locale.countryID)
.Select(x => (ServiceProfessionalSearchResult)FromDB(x, visibility)));
}
}
public static PublicUserJobStats Get(int userID, int jobTitleID, LcRest.ServiceProfessionalService.Visibility visibility)
{
const string sqlVariableDeclaration = @"
DECLARE @userID AS int
SET @userID = @0
DECLARE @jobTitleID AS int
SET @jobTitleID = @1
DECLARE @LanguageID int
SET @LanguageID = 1
DECLARE @CountryID int
SET @CountryID = 1
;";
const string sqlProviderPackageForClient = @"
WITH ProviderPackageForClient AS
(
SELECT *
FROM ProviderPackage
WHERE ProviderPackage.Active = 1
AND ProviderUserID = @userID
AND PositionID = @jobTitleID
AND LanguageID = @LanguageID
AND CountryID = @CountryID
AND VisibleToClientID IN ({0}) -- placeholder for format
) -- END ProviderPackageForClient
,";
const string sqlRemainder = @"
CTE AS
(
SELECT
MSP.ProviderUserID
,MSP.PositionID
,MSP.LanguageID
,MSP.CountryID
,MSP.minServicePrice
,MSP.servicesCount
,MUP.PriceRateUnit
,MUP.minUnitRate
,MUP.UnitPackages
,CASE WHEN MUP.rn > 0 THEN MUP.rn ELSE 1 END as rn
FROM
(SELECT
ProviderUserID
,PositionID
,LanguageID
,CountryID
,min(ProviderPackagePrice) as minServicePrice
,servicesCount=
(SELECT count(*) FROM ProviderPackageForClient)
FROM
ProviderPackageForClient
WHERE ProviderPackageForClient.PricingTypeID != 7
GROUP BY
ProviderUserID, PositionID, LanguageID, CountryID) MSP
LEFT JOIN
(
SELECT
ProviderUserID
,PositionID
,LanguageID
,CountryID
,PriceRateUnit
,min(PriceRate) as minUnitRate
,count(distinct ProviderPackageID) as UnitPackages
,ROW_NUMBER() OVER (PARTITION BY ProviderUserID, PositionID, LanguageID, CountryID
ORDER BY count(distinct ProviderPackageID) DESC)
AS rn -- lowest RN means highest number of packages for a PriceRateUnit
FROM ProviderPackageForClient
WHERE PriceRate is not null
GROUP BY
ProviderUserID, PositionID, LanguageID, CountryID, PriceRateUnit) as MUP
ON
MSP.ProviderUserID=MUP.ProviderUserID
AND MSP.PositionID=MUP.PositionID
AND MSP.LanguageID=MUP.LanguageID
AND MSP.CountryID=MUP.CountryID
)
SELECT
ProviderUserID as userID
,PositionID as jobTitleID
,servicesCount
,minServicePrice
,minUnitRate
,priceRateUnit
,CASE WHEN (minUnitRate > 0 AND minServicePrice > 0) AND minServicePrice <= minUnitRate THEN '$' + convert(varchar, minServicePrice)
WHEN (minUnitRate > 0 AND minServicePrice > 0) AND minUnitRate < minServicePrice THEN '$' + convert(varchar, minUnitRate) + '/' + PriceRateUnit
WHEN (minServicePrice > 0 AND minUnitRate is null) THEN '$' + convert(varchar, minServicePrice)
WHEN (minUnitRate > 0 AND minServicePrice <=0 ) THEN '$' + convert(varchar, minUnitRate) + '/' + PriceRateUnit ELSE NULL END as minServiceValue
FROM CTE
WHERE rn = 1 -- select the stats at the price rate unit with the most packages
";
using (var db = new LcDatabase())
{
// Database.Query does not natively expand SQL IN clause list, so do it manually
string sqlProviderPackageForClientExpanded = String.Format(sqlProviderPackageForClient, String.Join(",", visibility.VisibleToClientIDs()));
return(FromDB(db.QuerySingle(sqlVariableDeclaration
+ sqlProviderPackageForClientExpanded
+ sqlRemainder,
userID, jobTitleID)));
}
}
