USE [HFL_dev]
GO
/****** Object:  StoredProcedure [dbo].[tr_searchTours]    Script Date: 06.04.2017 2:09:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[tr_searchTours]
    @subTypeID int = 0,
    @gooID int = 0,
    @countryGooID int = 0,
    @outfitterID int = 0,
    @priceMin decimal(18,2) = 0,
    @priceMax decimal(18,2) = 0,
    @from date,
    @to date,
    @peopleCount int = 0,
    @duration int = 0,
    @customFilter customFilter READONLY,
    @onlyHot bit = 0,
    @sort nvarchar(20) = 0,
    @skip int = 0,
    @pageSize int = 4,
    @total int output
AS

DECLARE @tbl table(
    [tourID] [int],
    [tourName] [varchar] (64),
    [outfiterName] [nvarchar](256),
    [outfiterID] [int],
    [price] decimal(18,2),
    [discount] float,
    [discountPrice] decimal(18,2),
    [discountDate] datetime,
    [tourDesc] [nvarchar](max),
    [countryID] int,
    [countryName] [nvarchar](128),
    [goos] [nvarchar](max),
    [thumb] [nvarchar](256),
    [duration] int,
    [duration2] int,
    [durationName] [nvarchar](20),
    [beginDate] datetime,
    [endDate] datetime,
    [minAge] int,
    [minPeopleCount] int,
    PriceSort int,
    NewSort int,
    CountrySort int,
    PopularSort int
)
DECLARE @rowCount int

INSERT INTO @tbl
    SELECT
        baseTours.id tourID,
        baseTours.name tourName,
        outfiterItem.companyName outfiterName,
        outfiterItem.id outfiterID,
        (SELECT TOP 1 [price] FROM [dbo].[GetTourBasePriceTable] (baseTours.id, @priceMin, @priceMax, @from, @to, @peopleCount,valutaItem.koef)) [price],
        (SELECT TOP 1 [discount] FROM [dbo].[GetTourBasePriceTable] (baseTours.id, @priceMin, @priceMax, @from, @to, @peopleCount,valutaItem.koef)) [discount],
        (SELECT TOP 1 [discountPrice] FROM [dbo].[GetTourBasePriceTable] (baseTours.id, @priceMin, @priceMax, @from, @to, @peopleCount,valutaItem.koef)) [discountPrice],
        (SELECT TOP 1 [discountDate] FROM [dbo].[GetTourBasePriceTable] (baseTours.id, @priceMin, @priceMax, @from, @to, @peopleCount,valutaItem.koef)) [discountDate],
        baseTours.[desc] tourDesc,
        baseTours.countryGooID countryID,
        (select name from hf_geo_gooPlaces where id = baseTours.countryGooID ) countryName,
        (select top 1 (goo.name + ', ' + (select name from hf_geo_gooPlaces where goo.id=id))
            from hf_tr_tourGeos
            inner join hf_geo_gooPlaces goo on goo.id=hf_tr_tourGeos.gooID
            where tourID=baseTours.id) goos,
        (select top 1 u.data2
                    from us_objectResources o
                    inner join us_resources u on o.[resourceID]=u.id
                    where o.itemID=baseTours.id and o.code='tour') thumb,
        baseTours.duration duration,
        baseTours.duration2 duration2,
        (select name from [dbo].[hf_tr_durationTypes] where id = baseTours.[durationTypeID] ) durationName,
        baseTours.beginDate beginDate,
        baseTours.endDate endDate,
        baseTours.minAge minAge,
        @peopleCount minPeopleCount,

        ROW_NUMBER() OVER (ORDER BY price) AS PriceSort,
        ROW_NUMBER() OVER (ORDER BY baseTours.created desc ) AS NewSort,
        ROW_NUMBER() OVER (ORDER BY baseTours.countryGooID) AS CountrySort,
        ROW_NUMBER() OVER (ORDER BY (select count(*) from hf_ord_orders where tourID= baseTours.id)) AS PopularSort
    FROM
        hf_tr_baseTours AS baseTours
        JOIN hf_outfiters as outfiterItem ON baseTours.outfiterID = outfiterItem.id
        JOIN hf_valuta as valutaItem on outfiterItem.tourValutaID = valutaItem.id

    WHERE
    (isDeleted IS NULL OR isDeleted = 0) -- not deleted
    and
    statusID = 3 -- published
    and --only hot deals
    (@onlyHot = 0 or ((SELECT TOP 1 [discount] FROM [dbo].[GetTourBasePriceTable] (baseTours.id,@priceMin,@priceMax, @from,@to, @peopleCount, valutaItem.koef)) > 0))
    and  -- geo
    ((@gooID=0 OR exists(
            select * from hf_tr_tourGeos
            inner join hf_geo_gooPlaces on hf_tr_tourGeos.gooID=hf_geo_gooPlaces.id
            where tourID=baseTours.id and ( hf_geo_gooPlaces.id=@gooID )
            )
            OR (baseTours.countryGooID = @gooID))
       AND
        (@countryGooID=0 OR (baseTours.countryGooID = @countryGooID))
    )
    and  -- outfitter
    (@outfitterID=0 OR outfiterItem.id = @outfitterID
    )
    and  -- from, to
    (
        exists(
            select hf_tr_tourSchedules.id from hf_tr_tourSchedules
            inner join hf_tr_tourScheduleDurations on hf_tr_tourScheduleDurations.tourScheduleID=hf_tr_tourSchedules.id
            where tourID=baseTours.id and
                (@peopleCount = 0 or (@peopleCount = 1 and minPeopleCount is null and maxPeopleCount is null) or (minPeopleCount <= @peopleCount and @peopleCount <= maxPeopleCount))
                    and
                (@from is null or @from >= minDate) and (@to is null or @to <= maxDate)
        )
    )
    and --duration
    (
        ((@duration=''))
        or
        ((@duration='-1') and ((durationTypeID=1 and duration<1440) or (durationTypeID=2 and duration<24)))
        or
        ((@duration='1') and ((durationTypeID=3 and duration=1) or (durationTypeID=2 and duration=24)))
        or
        ((@duration='2') and (durationTypeID=3 and ((duration>=2 and duration<=3 )or(duration2  is not null and duration2>=2 and duration2<=3))))
        or
        ((@duration='4') and (durationTypeID=3 and ((duration>=4 and duration<=7) or (duration2  is not null and duration2>=4 and duration2<=7))))
        or
        ((@duration='8') and (durationTypeID=3 and ((duration>=8 and duration<=15) or (duration2  is not null and duration2>=8 and duration2<=15))))
        or
        ((@duration='15') and (durationTypeID=3 and (duration>15 or (duration2  is not null and duration2>15))))
    )
    and  --price
    (
    (SELECT TOP 1 [price] FROM [dbo].[GetTourBasePriceTable] (baseTours.id, @priceMin, @priceMax, @from, @to , @peopleCount, valutaItem.koef)) > 0
    )
    and --subtypeID
    (
      (@subTypeID=0) or (baseTours.subTypeID=@subTypeID and ((SELECT COUNT(*) FROM @customFilter)= 0 OR
        (CASE (SELECT code FROM hf_tr_tourSubTypes WHERE id = @subTypeID)
            WHEN 'diving' THEN dbo.GetFilterScubaDivingTours(baseTours.id, @customFilter)
            WHEN 'climbing' THEN dbo.GetFilterMountainTours(baseTours.id, @customFilter)
            WHEN 'rockClimbing' THEN dbo.GetFilterRockClimbingTours(baseTours.id, @customFilter)
            WHEN 'mountaineeringClimbing' THEN dbo.GetFilterMountaineeringTours(baseTours.id, @customFilter)
            WHEN 'auto' THEN dbo.GetFilterAutoTours(baseTours.id, @customFilter)
            WHEN 'spaeleology' THEN dbo.GetFilterSpaeleologyTours(baseTours.id, @customFilter)
            WHEN 'rafting' THEN dbo.GetFilterRaftingTours(baseTours.id, @customFilter)
            WHEN 'trekking' THEN dbo.GetFilterTrekkingTours(baseTours.id, @customFilter)
            WHEN 'velo' THEN dbo.GetFilterBicycleTours(baseTours.id, @customFilter)
            WHEN 'parus' THEN dbo.GetFilterParusTours(baseTours.id, @customFilter)
            WHEN 'cruise' THEN dbo.GetFilterCruiseTours(baseTours.id, @customFilter)
            WHEN 'volcano' THEN dbo.GetFilterVolcanoTours(baseTours.id, @customFilter)
            WHEN 'jumping' THEN dbo.GetFilterJumpingTours(baseTours.id, @customFilter)
            WHEN 'moto' THEN dbo.GetFilterMotoTours(baseTours.id, @customFilter)
            WHEN 'mixClimbing' THEN dbo.GetFilterClimbingTours(baseTours.id, @customFilter)
            WHEN 'iceClimbing' THEN dbo.GetFilterIceClimbingTours(baseTours.id, @customFilter)
            WHEN 'boulderingClimbing' THEN dbo.GetFilterBoulClimbingTours(baseTours.id, @customFilter)
            WHEN 'canyoningClimbing' THEN dbo.GetFilterCanyonClimbingTours(baseTours.id, @customFilter)
            WHEN 'scramblingClimbing' THEN dbo.GetFilterScrClimbingTours(baseTours.id, @customFilter)
            WHEN 'expedition' THEN dbo.GetFilterExpClimbingTours(baseTours.id, @customFilter)
            WHEN 'mountainVelo' THEN dbo.GetFilterBicycleMountainTours(baseTours.id, @customFilter)
            WHEN 'hikking' THEN dbo.GetFilterHikkingTours(baseTours.id, @customFilter)

            else 1
        END) = 1)
        )
    )
SET @rowCount = @@ROWCOUNT


INSERT INTO @tbl
    SELECT
        baseTours.id tourID,
        baseTours.name tourName,
        outfiterItem.companyName outfiterName,
        outfiterItem.id outfiterID,
        (SELECT TOP 1 [price] FROM [dbo].[GetTourBasePriceTable] (baseTours.id,@priceMin,@priceMax, @from,@to, 0,valutaItem.koef)) [price],
        (SELECT TOP 1 [discount] FROM [dbo].[GetTourBasePriceTable] (baseTours.id,@priceMin,@priceMax, @from,@to, 0,valutaItem.koef)) [discount],
        (SELECT TOP 1 [discountPrice] FROM [dbo].[GetTourBasePriceTable] (baseTours.id,@priceMin,@priceMax, @from,@to, 0,valutaItem.koef)) [discountPrice],
        (SELECT TOP 1 [discountDate] FROM [dbo].[GetTourBasePriceTable] (baseTours.id,@priceMin,@priceMax, @from,@to, 0,valutaItem.koef)) [discountDate],
        baseTours.[desc] tourDesc,
        baseTours.countryGooID countryID,
        (select name from hf_geo_gooPlaces where id = baseTours.countryGooID ) countryName,
        (select top 1 (goo.name + ', ' + (select name from hf_geo_gooPlaces where goo.id=id))
            from hf_tr_tourGeos
            inner join hf_geo_gooPlaces goo on goo.id=hf_tr_tourGeos.gooID
            where tourID=baseTours.id) goos,
        (select top 1 u.data2
                    from us_objectResources o
                    inner join us_resources u on o.[resourceID]=u.id
                    where o.itemID=baseTours.id and o.code='tour') thumb,
        baseTours.duration duration,
        baseTours.duration2 duration2,
        (select name from [dbo].[hf_tr_durationTypes] where id = baseTours.[durationTypeID] ) durationName,
        baseTours.beginDate beginDate,
        baseTours.endDate endDate,
        baseTours.minAge minAge,
        (SELECT TOP 1 minPeopleCount FROM hf_tr_tourSchedules WHERE tourID = baseTours.id AND EXISTS(SELECT TOP 1 id FROM hf_tr_tourScheduleTariffs WHERE tourScheduleID = hf_tr_tourSchedules.id)) minPeopleCount,

        (@rowCount + ROW_NUMBER() OVER (ORDER BY (SELECT TOP 1 minPeopleCount FROM hf_tr_tourSchedules WHERE tourID = baseTours.id AND EXISTS(SELECT TOP 1 id FROM hf_tr_tourScheduleTariffs WHERE tourScheduleID = hf_tr_tourSchedules.id)), price)) AS PriceSort,
        (@rowCount + ROW_NUMBER() OVER (ORDER BY (SELECT TOP 1 minPeopleCount FROM hf_tr_tourSchedules WHERE tourID = baseTours.id AND EXISTS(SELECT TOP 1 id FROM hf_tr_tourScheduleTariffs WHERE tourScheduleID = hf_tr_tourSchedules.id)), baseTours.created desc )) AS NewSort,
        (@rowCount + ROW_NUMBER() OVER (ORDER BY (SELECT TOP 1 minPeopleCount FROM hf_tr_tourSchedules WHERE tourID = baseTours.id AND EXISTS(SELECT TOP 1 id FROM hf_tr_tourScheduleTariffs WHERE tourScheduleID = hf_tr_tourSchedules.id)), baseTours.countryGooID)) AS CountrySort,
        (@rowCount + ROW_NUMBER() OVER (ORDER BY (SELECT TOP 1 minPeopleCount FROM hf_tr_tourSchedules WHERE tourID = baseTours.id AND EXISTS(SELECT TOP 1 id FROM hf_tr_tourScheduleTariffs WHERE tourScheduleID = hf_tr_tourSchedules.id)), (select count(*) from hf_ord_orders where tourID= baseTours.id))) AS PopularSort
    FROM
        hf_tr_baseTours AS baseTours
        JOIN hf_outfiters as outfiterItem ON baseTours.outfiterID = outfiterItem.id
        JOIN hf_valuta as valutaItem on outfiterItem.tourValutaID = valutaItem.id
    WHERE
    (baseTours.id not in (SELECT tourID FROM @tbl))
    and
    (isDeleted IS NULL OR isDeleted = 0) -- not deleted
    and
    statusID = 3 -- published
    and --only hot deals
    (@onlyHot = 0 or ((SELECT TOP 1 [discount] FROM [dbo].[GetTourBasePriceTable] (baseTours.id,@priceMin,@priceMax, @from,@to, 0,valutaItem.koef)) > 0))
    and  -- geo
    ((@gooID=0 OR exists(
            select * from hf_tr_tourGeos
            inner join hf_geo_gooPlaces on hf_tr_tourGeos.gooID=hf_geo_gooPlaces.id
            where tourID=baseTours.id and ( hf_geo_gooPlaces.id=@gooID )
            )
            OR (baseTours.countryGooID = @gooID))
       AND
        (@countryGooID=0 OR (baseTours.countryGooID = @countryGooID))
    )
    and  -- outfitter
    (@outfitterID=0 OR outfiterItem.id = @outfitterID
    )
    and  -- from, to
    (
        exists(
            select hf_tr_tourSchedules.id from hf_tr_tourSchedules
            inner join hf_tr_tourScheduleDurations on hf_tr_tourScheduleDurations.tourScheduleID=hf_tr_tourSchedules.id
            where tourID=baseTours.id and
                (@from is null or @from >= minDate) and
                (@to is null or @to <= maxDate)
        )
    )
    and --duration
    (
        ((@duration=''))
        or
        ((@duration='-1') and ((durationTypeID=1 and duration<1440) or (durationTypeID=2 and duration<24)))
        or
        ((@duration='1') and ((durationTypeID=3 and duration=1) or (durationTypeID=2 and duration=24)))
        or
        ((@duration='2') and (durationTypeID=3 and ((duration>=2 and duration<=3 )or(duration2  is not null and duration2>=2 and duration2<=3))))
        or
        ((@duration='4') and (durationTypeID=3 and ((duration>=4 and duration<=7) or (duration2  is not null and duration2>=4 and duration2<=7))))
        or
        ((@duration='8') and (durationTypeID=3 and ((duration>=8 and duration<=15) or (duration2  is not null and duration2>=8 and duration2<=15))))
        or
        ((@duration='15') and (durationTypeID=3 and (duration>15 or (duration2  is not null and duration2>15))))
    )
    and  --price
    (
    (SELECT TOP 1 [price] FROM [dbo].[GetTourBasePriceTable] (baseTours.id,@priceMin,@priceMax, @from,@to, 0,valutaItem.koef)) > 0
    )
    and --subtypeID
    (
      (@subTypeID=0) or (baseTours.subTypeID=@subTypeID and ((SELECT COUNT(*) FROM @customFilter)= 0 OR
        (CASE (SELECT code FROM hf_tr_tourSubTypes WHERE id = @subTypeID)
            WHEN 'diving' THEN dbo.GetFilterScubaDivingTours(baseTours.id, @customFilter)
            WHEN 'climbing' THEN dbo.GetFilterMountainTours(baseTours.id, @customFilter)
            WHEN 'rockClimbing' THEN dbo.GetFilterRockClimbingTours(baseTours.id, @customFilter)
            WHEN 'mountaineeringClimbing' THEN dbo.GetFilterMountaineeringTours(baseTours.id, @customFilter)
            WHEN 'auto' THEN dbo.GetFilterAutoTours(baseTours.id, @customFilter)
            WHEN 'spaeleology' THEN dbo.GetFilterSpaeleologyTours(baseTours.id, @customFilter)
            WHEN 'rafting' THEN dbo.GetFilterRaftingTours(baseTours.id, @customFilter)
            WHEN 'trekking' THEN dbo.GetFilterTrekkingTours(baseTours.id, @customFilter)
            WHEN 'velo' THEN dbo.GetFilterBicycleTours(baseTours.id, @customFilter)
            WHEN 'parus' THEN dbo.GetFilterParusTours(baseTours.id, @customFilter)
            WHEN 'cruise' THEN dbo.GetFilterCruiseTours(baseTours.id, @customFilter)
            WHEN 'volcano' THEN dbo.GetFilterVolcanoTours(baseTours.id, @customFilter)
            WHEN 'jumping' THEN dbo.GetFilterJumpingTours(baseTours.id, @customFilter)
            WHEN 'moto' THEN dbo.GetFilterMotoTours(baseTours.id, @customFilter)
            WHEN 'mixClimbing' THEN dbo.GetFilterClimbingTours(baseTours.id, @customFilter)
            WHEN 'iceClimbing' THEN dbo.GetFilterIceClimbingTours(baseTours.id, @customFilter)
            WHEN 'boulderingClimbing' THEN dbo.GetFilterBoulClimbingTours(baseTours.id, @customFilter)
            WHEN 'canyoningClimbing' THEN dbo.GetFilterCanyonClimbingTours(baseTours.id, @customFilter)
            WHEN 'scramblingClimbing' THEN dbo.GetFilterScrClimbingTours(baseTours.id, @customFilter)
            WHEN 'expedition' THEN dbo.GetFilterExpClimbingTours(baseTours.id, @customFilter)
            WHEN 'mountainVelo' THEN dbo.GetFilterBicycleMountainTours(baseTours.id, @customFilter)
            WHEN 'hikking' THEN dbo.GetFilterHikkingTours(baseTours.id, @customFilter)

            else 1
        END) = 1)
        )
    )

set @total = @rowCount + @@ROWCOUNT


SELECT
    tourID,
    tourName,
    outfiterName,
    outfiterID,
    price,
    [discount],
    [discountPrice],
    [discountDate],
    tourDesc,
    countryID,
    countryName,
    goos,
    thumb,
    duration,
    duration2,
    durationName,
    beginDate,
    endDate,
    minAge,
    minPeopleCount
FROM @tbl
    where
    (CASE @sort
        WHEN 'price' THEN PriceSort
        WHEN 'new' THEN NewSort
        WHEN 'country' THEN CountrySort
        WHEN 'popular' THEN PopularSort
     end) BETWEEN @skip+1 AND @skip + @pageSize
ORDER BY
     CASE @sort
        WHEN 'price' THEN PriceSort
        WHEN 'new' THEN NewSort
        WHEN 'country' THEN CountrySort
        WHEN 'popular' THEN PopularSort
     END;