ALTER PROCEDURE [vacancies].[NannyVacancies]
    @UserType INT = NULL,
    @UserId uniqueidentifier = NULL,
    @VacancyLabelType INT = NULL,

    @RowIndex INT = 0,
    @PageSize INT = 20,
    @RowCount INT output,

    @SortOrder INT = 3,

    @Kind tinyint = 2,
    @Regions IntegersTable readonly,
    @Region INT = NULL,
    @LANGUAGE INT = NULL,

    @Radius tinyint = NULL,

    @DistrictId INT = NULL,
    @AreaId INT = NULL,
    @DaysAgo tinyint = NULL,
    @IncludeAgencyVacancies bit = NULL,

    @NannyInhabitancies BIGINT = NULL,
    @NannyChildMaxCount INT = NULL,

    @SalaryByAgreement bit = NULL,
    @NannyMinHourlySalary INT = NULL,
    @NannyMaxHourlySalary INT = NULL

AS
BEGIN
    SET nocount ON;

    DECLARE @OrderByDate INT = 3
    DECLARE @SearchCenter geography

    IF (@Radius IS NOT NULL)
        SET @SearchCenter = vacancies.GetDistrictPoint(@DistrictId)

    DECLARE @labels TABLE (VacancyId INT NOT NULL PRIMARY KEY)

    INSERT INTO @labels
        SELECT * FROM vacancies.GetLabels(@UserId, @VacancyLabelType)

    DECLARE @vacancies TABLE (VacancyId INT NOT NULL PRIMARY KEY)

    INSERT INTO @vacancies
        SELECT VacancyId
        FROM vacancies.Vacancies WITH (nolock)
        WHERE
            Kind = @Kind
            AND EXISTS (SELECT 1 FROM @Regions WHERE VALUE = Region)
            AND (@LANGUAGE IS NULL OR LANGUAGE = @LANGUAGE)
            AND (@AreaId IS NULL OR AreaId = @AreaId)

            AND (@SearchCenter IS NOT NULL OR @DistrictId IS NULL OR DistrictId = @DistrictId)
            AND (@SearchCenter IS NULL OR @SearchCenter.STDistance(Point) < @Radius * 1000)

            AND (@UserId IS NULL OR @VacancyLabelType IS NULL OR VacancyId IN (SELECT VacancyId FROM @labels))

            AND (@DaysAgo IS NULL OR getutcdate() <= dateadd(DAY, @DaysAgo, PublicationDate))
            AND (@IncludeAgencyVacancies = 1 OR [VacancyOwnerType] = 1)


            AND (@NannyChildMaxCount IS NULL OR @NannyChildMaxCount = 4 OR [NannyChildMaxCount] <= @NannyChildMaxCount)
            AND (@NannyInhabitancies IS NULL OR [NannyInhabitancies] & @NannyInhabitancies = @NannyInhabitancies)

            AND EXISTS (SELECT 1 FROM vacancies.SalaryFilter(@SalaryByAgreement, @NannyMinHourlySalary, @NannyMaxHourlySalary, NannyHourlySalary))

    SELECT @RowCount = COUNT(*) FROM @vacancies

    SELECT * FROM (
        SELECT
            ROW_NUMBER() OVER (
                ORDER BY CASE  @SortOrder
                        WHEN @OrderByDate THEN -1 * datediff(SECOND, getutcdate(), vacancies.PublicationDate)
                    END
                ) AS RowNum,
            Vacancies.VacancyId AS Id,
            vacancies.Title,
            vacancies.[Description],
            vacancies.PublicationDate,
            vacancies.VacancyOwnerType,
            vacancies.Region,
            vacancies.AreaName,
            vacancies.DistrictName,
            vacancies.OwnerName,
            vacancies.OwnerId,
            vacancies.AgeMinimum,
            vacancies.AgeMaximum,
            vacancies.Experience,
            vacancies.WorkStart,
            vacancies.NannyHourlySalary,
            vacancies.JobKindAlias,
            vacancies.DistrictAlias,
            vacancies.AreaAlias,
            vacancies.AddressShort,
            vacancies.CompanyCardId
        FROM vacancies.Vacancies WITH (nolock)
            INNER JOIN @vacancies ids ON ids.VacancyId = Vacancies.VacancyId
        ) AS DATA
    WHERE
        RowNum > @RowIndex AND RowNum <= @RowIndex + @PageSize
    ORDER BY RowNum
END