API tools faq
paste
Advertisement
anamnian

Untitled

anamnian
Apr 5th, 2017
89
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
MySQL 15.39 KB | None | 0 0
raw download clone embed print report
  1. USE [HFL_dev]
  2. GO
  3. /****** Object:  StoredProcedure [dbo].[tr_searchTours]    Script Date: 06.04.2017 2:09:39 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8.  
  9. ALTER PROCEDURE [dbo].[tr_searchTours]
  10.     @subTypeID int = 0,
  11.     @gooID int = 0,
  12.     @countryGooID int = 0,
  13.     @outfitterID int = 0,
  14.     @priceMin decimal(18,2) = 0,
  15.     @priceMax decimal(18,2) = 0,
  16.     @from date,
  17.     @to date,
  18.     @peopleCount int = 0,
  19.     @duration int = 0,
  20.     @customFilter customFilter READONLY,
  21.     @onlyHot bit = 0,
  22.     @sort nvarchar(20) = 0,
  23.     @skip int = 0,
  24.     @pageSize int = 4,
  25.     @total int output
  26. AS
  27.  
  28. DECLARE @tbl table(  
  29.     [tourID] [int],  
  30.     [tourName] [varchar] (64),      
  31.     [outfiterName] [nvarchar](256),  
  32.     [outfiterID] [int],
  33.     [price] decimal(18,2),
  34.     [discount] float,
  35.     [discountPrice] decimal(18,2),
  36.     [discountDate] datetime,
  37.     [tourDesc] [nvarchar](max),
  38.     [countryID] int,   
  39.     [countryName] [nvarchar](128),
  40.     [goos] [nvarchar](max),
  41.     [thumb] [nvarchar](256),
  42.     [duration] int,
  43.     [duration2] int,
  44.     [durationName] [nvarchar](20),
  45.     [beginDate] datetime,
  46.     [endDate] datetime,
  47.     [minAge] int,
  48.     [minPeopleCount] int,
  49.     PriceSort int,
  50.     NewSort int,
  51.     CountrySort int,
  52.     PopularSort int
  53. )  
  54. DECLARE @rowCount int
  55.  
  56. INSERT INTO @tbl
  57.     SELECT
  58.         baseTours.id tourID,
  59.         baseTours.name tourName,       
  60.         outfiterItem.companyName outfiterName,
  61.         outfiterItem.id outfiterID,
  62.         (SELECT TOP 1 [price] FROM [dbo].[GetTourBasePriceTable] (baseTours.id, @priceMin, @priceMax, @from, @to, @peopleCount,valutaItem.koef)) [price],
  63.         (SELECT TOP 1 [discount] FROM [dbo].[GetTourBasePriceTable] (baseTours.id, @priceMin, @priceMax, @from, @to, @peopleCount,valutaItem.koef)) [discount],
  64.         (SELECT TOP 1 [discountPrice] FROM [dbo].[GetTourBasePriceTable] (baseTours.id, @priceMin, @priceMax, @from, @to, @peopleCount,valutaItem.koef)) [discountPrice],
  65.         (SELECT TOP 1 [discountDate] FROM [dbo].[GetTourBasePriceTable] (baseTours.id, @priceMin, @priceMax, @from, @to, @peopleCount,valutaItem.koef)) [discountDate],
  66.         baseTours.[desc] tourDesc,
  67.         baseTours.countryGooID countryID,
  68.         (select name from hf_geo_gooPlaces where id = baseTours.countryGooID ) countryName,
  69.         (select top 1 (goo.name + ', ' + (select name from hf_geo_gooPlaces where goo.id=id))
  70.             from hf_tr_tourGeos
  71.             inner join hf_geo_gooPlaces goo on goo.id=hf_tr_tourGeos.gooID
  72.             where tourID=baseTours.id) goos,
  73.         (select top 1 u.data2
  74.                     from us_objectResources o
  75.                     inner join us_resources u on o.[resourceID]=u.id
  76.                     where o.itemID=baseTours.id and o.code='tour') thumb,
  77.         baseTours.duration duration,
  78.         baseTours.duration2 duration2,
  79.         (select name from [dbo].[hf_tr_durationTypes] where id = baseTours.[durationTypeID] ) durationName,
  80.         baseTours.beginDate beginDate,
  81.         baseTours.endDate endDate,
  82.         baseTours.minAge minAge,
  83.         @peopleCount minPeopleCount,
  84.          
  85.         ROW_NUMBER() OVER (ORDER BY price) AS PriceSort,
  86.         ROW_NUMBER() OVER (ORDER BY baseTours.created desc ) AS NewSort,
  87.         ROW_NUMBER() OVER (ORDER BY baseTours.countryGooID) AS CountrySort,
  88.         ROW_NUMBER() OVER (ORDER BY (select count(*) from hf_ord_orders where tourID= baseTours.id)) AS PopularSort
  89.     FROM
  90.         hf_tr_baseTours AS baseTours
  91.         JOIN hf_outfiters as outfiterItem ON baseTours.outfiterID = outfiterItem.id
  92.         JOIN hf_valuta as valutaItem on outfiterItem.tourValutaID = valutaItem.id
  93.  
  94.     WHERE
  95.     (isDeleted IS NULL OR isDeleted = 0) -- not deleted
  96.     and
  97.     statusID = 3 -- published
  98.     and --only hot deals
  99.     (@onlyHot = 0 or ((SELECT TOP 1 [discount] FROM [dbo].[GetTourBasePriceTable] (baseTours.id,@priceMin,@priceMax, @from,@to, @peopleCount, valutaItem.koef)) > 0))
  100.     and  -- geo        
  101.     ((@gooID=0 OR exists(
  102.             select * from hf_tr_tourGeos
  103.             inner join hf_geo_gooPlaces on hf_tr_tourGeos.gooID=hf_geo_gooPlaces.id
  104.             where tourID=baseTours.id and ( hf_geo_gooPlaces.id=@gooID )
  105.             )
  106.             OR (baseTours.countryGooID = @gooID))
  107.        AND
  108.         (@countryGooID=0 OR (baseTours.countryGooID = @countryGooID))
  109.     )
  110.     and  -- outfitter      
  111.     (@outfitterID=0 OR outfiterItem.id = @outfitterID
  112.     )
  113.     and  -- from, to
  114.     (
  115.         exists(
  116.             select hf_tr_tourSchedules.id from hf_tr_tourSchedules
  117.             inner join hf_tr_tourScheduleDurations on hf_tr_tourScheduleDurations.tourScheduleID=hf_tr_tourSchedules.id
  118.             where tourID=baseTours.id and
  119.                 (@peopleCount = 0 or (@peopleCount = 1 and minPeopleCount is null and maxPeopleCount is null) or (minPeopleCount <= @peopleCount and @peopleCount <= maxPeopleCount))  
  120.                     and
  121.                 (@from is null or @from >= minDate) and (@to is null or @to <= maxDate)      
  122.         )  
  123.     )
  124.     and --duration
  125.     (
  126.         ((@duration=''))
  127.         or
  128.         ((@duration='-1') and ((durationTypeID=1 and duration<1440) or (durationTypeID=2 and duration<24)))
  129.         or
  130.         ((@duration='1') and ((durationTypeID=3 and duration=1) or (durationTypeID=2 and duration=24)))
  131.         or
  132.         ((@duration='2') and (durationTypeID=3 and ((duration>=2 and duration<=3 )or(duration2  is not null and duration2>=2 and duration2<=3))))
  133.         or
  134.         ((@duration='4') and (durationTypeID=3 and ((duration>=4 and duration<=7) or (duration2  is not null and duration2>=4 and duration2<=7))))
  135.         or
  136.         ((@duration='8') and (durationTypeID=3 and ((duration>=8 and duration<=15) or (duration2  is not null and duration2>=8 and duration2<=15))))
  137.         or
  138.         ((@duration='15') and (durationTypeID=3 and (duration>15 or (duration2  is not null and duration2>15))))
  139.     )  
  140.     and  --price
  141.     (
  142.     (SELECT TOP 1 [price] FROM [dbo].[GetTourBasePriceTable] (baseTours.id, @priceMin, @priceMax, @from, @to , @peopleCount, valutaItem.koef)) > 0
  143.     )
  144.     and --subtypeID
  145.     (
  146.       (@subTypeID=0) or (baseTours.subTypeID=@subTypeID and ((SELECT COUNT(*) FROM @customFilter)= 0 OR
  147.         (CASE (SELECT code FROM hf_tr_tourSubTypes WHERE id = @subTypeID)
  148.             WHEN 'diving' THEN dbo.GetFilterScubaDivingTours(baseTours.id, @customFilter)
  149.             WHEN 'climbing' THEN dbo.GetFilterMountainTours(baseTours.id, @customFilter)
  150.             WHEN 'rockClimbing' THEN dbo.GetFilterRockClimbingTours(baseTours.id, @customFilter)
  151.             WHEN 'mountaineeringClimbing' THEN dbo.GetFilterMountaineeringTours(baseTours.id, @customFilter)
  152.             WHEN 'auto' THEN dbo.GetFilterAutoTours(baseTours.id, @customFilter)
  153.             WHEN 'spaeleology' THEN dbo.GetFilterSpaeleologyTours(baseTours.id, @customFilter)
  154.             WHEN 'rafting' THEN dbo.GetFilterRaftingTours(baseTours.id, @customFilter)
  155.             WHEN 'trekking' THEN dbo.GetFilterTrekkingTours(baseTours.id, @customFilter)
  156.             WHEN 'velo' THEN dbo.GetFilterBicycleTours(baseTours.id, @customFilter)
  157.             WHEN 'parus' THEN dbo.GetFilterParusTours(baseTours.id, @customFilter)
  158.             WHEN 'cruise' THEN dbo.GetFilterCruiseTours(baseTours.id, @customFilter)
  159.             WHEN 'volcano' THEN dbo.GetFilterVolcanoTours(baseTours.id, @customFilter)
  160.             WHEN 'jumping' THEN dbo.GetFilterJumpingTours(baseTours.id, @customFilter)
  161.             WHEN 'moto' THEN dbo.GetFilterMotoTours(baseTours.id, @customFilter)
  162.             WHEN 'mixClimbing' THEN dbo.GetFilterClimbingTours(baseTours.id, @customFilter)
  163.             WHEN 'iceClimbing' THEN dbo.GetFilterIceClimbingTours(baseTours.id, @customFilter)
  164.             WHEN 'boulderingClimbing' THEN dbo.GetFilterBoulClimbingTours(baseTours.id, @customFilter)
  165.             WHEN 'canyoningClimbing' THEN dbo.GetFilterCanyonClimbingTours(baseTours.id, @customFilter)
  166.             WHEN 'scramblingClimbing' THEN dbo.GetFilterScrClimbingTours(baseTours.id, @customFilter)
  167.             WHEN 'expedition' THEN dbo.GetFilterExpClimbingTours(baseTours.id, @customFilter)
  168.             WHEN 'mountainVelo' THEN dbo.GetFilterBicycleMountainTours(baseTours.id, @customFilter)
  169.             WHEN 'hikking' THEN dbo.GetFilterHikkingTours(baseTours.id, @customFilter)
  170.  
  171.             else 1
  172.         END) = 1)
  173.         )
  174.     )
  175. SET @rowCount = @@ROWCOUNT
  176.  
  177.  
  178. INSERT INTO @tbl
  179.     SELECT
  180.         baseTours.id tourID,
  181.         baseTours.name tourName,       
  182.         outfiterItem.companyName outfiterName,
  183.         outfiterItem.id outfiterID,
  184.         (SELECT TOP 1 [price] FROM [dbo].[GetTourBasePriceTable] (baseTours.id,@priceMin,@priceMax, @from,@to, 0,valutaItem.koef)) [price],
  185.         (SELECT TOP 1 [discount] FROM [dbo].[GetTourBasePriceTable] (baseTours.id,@priceMin,@priceMax, @from,@to, 0,valutaItem.koef)) [discount],
  186.         (SELECT TOP 1 [discountPrice] FROM [dbo].[GetTourBasePriceTable] (baseTours.id,@priceMin,@priceMax, @from,@to, 0,valutaItem.koef)) [discountPrice],
  187.         (SELECT TOP 1 [discountDate] FROM [dbo].[GetTourBasePriceTable] (baseTours.id,@priceMin,@priceMax, @from,@to, 0,valutaItem.koef)) [discountDate],
  188.         baseTours.[desc] tourDesc,
  189.         baseTours.countryGooID countryID,
  190.         (select name from hf_geo_gooPlaces where id = baseTours.countryGooID ) countryName,
  191.         (select top 1 (goo.name + ', ' + (select name from hf_geo_gooPlaces where goo.id=id))
  192.             from hf_tr_tourGeos
  193.             inner join hf_geo_gooPlaces goo on goo.id=hf_tr_tourGeos.gooID
  194.             where tourID=baseTours.id) goos,
  195.         (select top 1 u.data2
  196.                     from us_objectResources o
  197.                     inner join us_resources u on o.[resourceID]=u.id
  198.                     where o.itemID=baseTours.id and o.code='tour') thumb,
  199.         baseTours.duration duration,
  200.         baseTours.duration2 duration2,
  201.         (select name from [dbo].[hf_tr_durationTypes] where id = baseTours.[durationTypeID] ) durationName,
  202.         baseTours.beginDate beginDate,
  203.         baseTours.endDate endDate,
  204.         baseTours.minAge minAge,
  205.         (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,
  206.          
  207.         (@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,
  208.         (@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,
  209.         (@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,
  210.         (@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
  211.     FROM
  212.         hf_tr_baseTours AS baseTours
  213.         JOIN hf_outfiters as outfiterItem ON baseTours.outfiterID = outfiterItem.id
  214.         JOIN hf_valuta as valutaItem on outfiterItem.tourValutaID = valutaItem.id
  215.     WHERE
  216.     (baseTours.id not in (SELECT tourID FROM @tbl))
  217.     and
  218.     (isDeleted IS NULL OR isDeleted = 0) -- not deleted
  219.     and
  220.     statusID = 3 -- published
  221.     and --only hot deals
  222.     (@onlyHot = 0 or ((SELECT TOP 1 [discount] FROM [dbo].[GetTourBasePriceTable] (baseTours.id,@priceMin,@priceMax, @from,@to, 0,valutaItem.koef)) > 0))
  223.     and  -- geo        
  224.     ((@gooID=0 OR exists(
  225.             select * from hf_tr_tourGeos
  226.             inner join hf_geo_gooPlaces on hf_tr_tourGeos.gooID=hf_geo_gooPlaces.id
  227.             where tourID=baseTours.id and ( hf_geo_gooPlaces.id=@gooID )
  228.             )
  229.             OR (baseTours.countryGooID = @gooID))
  230.        AND
  231.         (@countryGooID=0 OR (baseTours.countryGooID = @countryGooID))
  232.     )
  233.     and  -- outfitter      
  234.     (@outfitterID=0 OR outfiterItem.id = @outfitterID
  235.     )
  236.     and  -- from, to
  237.     (
  238.         exists(
  239.             select hf_tr_tourSchedules.id from hf_tr_tourSchedules
  240.             inner join hf_tr_tourScheduleDurations on hf_tr_tourScheduleDurations.tourScheduleID=hf_tr_tourSchedules.id
  241.             where tourID=baseTours.id and
  242.                 (@from is null or @from >= minDate) and
  243.                 (@to is null or @to <= maxDate)      
  244.         )  
  245.     )
  246.     and --duration
  247.     (
  248.         ((@duration=''))
  249.         or
  250.         ((@duration='-1') and ((durationTypeID=1 and duration<1440) or (durationTypeID=2 and duration<24)))
  251.         or
  252.         ((@duration='1') and ((durationTypeID=3 and duration=1) or (durationTypeID=2 and duration=24)))
  253.         or
  254.         ((@duration='2') and (durationTypeID=3 and ((duration>=2 and duration<=3 )or(duration2  is not null and duration2>=2 and duration2<=3))))
  255.         or
  256.         ((@duration='4') and (durationTypeID=3 and ((duration>=4 and duration<=7) or (duration2  is not null and duration2>=4 and duration2<=7))))
  257.         or
  258.         ((@duration='8') and (durationTypeID=3 and ((duration>=8 and duration<=15) or (duration2  is not null and duration2>=8 and duration2<=15))))
  259.         or
  260.         ((@duration='15') and (durationTypeID=3 and (duration>15 or (duration2  is not null and duration2>15))))
  261.     )  
  262.     and  --price
  263.     (
  264.     (SELECT TOP 1 [price] FROM [dbo].[GetTourBasePriceTable] (baseTours.id,@priceMin,@priceMax, @from,@to, 0,valutaItem.koef)) > 0
  265.     )
  266.     and --subtypeID
  267.     (
  268.       (@subTypeID=0) or (baseTours.subTypeID=@subTypeID and ((SELECT COUNT(*) FROM @customFilter)= 0 OR
  269.         (CASE (SELECT code FROM hf_tr_tourSubTypes WHERE id = @subTypeID)
  270.             WHEN 'diving' THEN dbo.GetFilterScubaDivingTours(baseTours.id, @customFilter)
  271.             WHEN 'climbing' THEN dbo.GetFilterMountainTours(baseTours.id, @customFilter)
  272.             WHEN 'rockClimbing' THEN dbo.GetFilterRockClimbingTours(baseTours.id, @customFilter)
  273.             WHEN 'mountaineeringClimbing' THEN dbo.GetFilterMountaineeringTours(baseTours.id, @customFilter)
  274.             WHEN 'auto' THEN dbo.GetFilterAutoTours(baseTours.id, @customFilter)
  275.             WHEN 'spaeleology' THEN dbo.GetFilterSpaeleologyTours(baseTours.id, @customFilter)
  276.             WHEN 'rafting' THEN dbo.GetFilterRaftingTours(baseTours.id, @customFilter)
  277.             WHEN 'trekking' THEN dbo.GetFilterTrekkingTours(baseTours.id, @customFilter)
  278.             WHEN 'velo' THEN dbo.GetFilterBicycleTours(baseTours.id, @customFilter)
  279.             WHEN 'parus' THEN dbo.GetFilterParusTours(baseTours.id, @customFilter)
  280.             WHEN 'cruise' THEN dbo.GetFilterCruiseTours(baseTours.id, @customFilter)
  281.             WHEN 'volcano' THEN dbo.GetFilterVolcanoTours(baseTours.id, @customFilter)
  282.             WHEN 'jumping' THEN dbo.GetFilterJumpingTours(baseTours.id, @customFilter)
  283.             WHEN 'moto' THEN dbo.GetFilterMotoTours(baseTours.id, @customFilter)
  284.             WHEN 'mixClimbing' THEN dbo.GetFilterClimbingTours(baseTours.id, @customFilter)
  285.             WHEN 'iceClimbing' THEN dbo.GetFilterIceClimbingTours(baseTours.id, @customFilter)
  286.             WHEN 'boulderingClimbing' THEN dbo.GetFilterBoulClimbingTours(baseTours.id, @customFilter)
  287.             WHEN 'canyoningClimbing' THEN dbo.GetFilterCanyonClimbingTours(baseTours.id, @customFilter)
  288.             WHEN 'scramblingClimbing' THEN dbo.GetFilterScrClimbingTours(baseTours.id, @customFilter)
  289.             WHEN 'expedition' THEN dbo.GetFilterExpClimbingTours(baseTours.id, @customFilter)
  290.             WHEN 'mountainVelo' THEN dbo.GetFilterBicycleMountainTours(baseTours.id, @customFilter)
  291.             WHEN 'hikking' THEN dbo.GetFilterHikkingTours(baseTours.id, @customFilter)
  292.  
  293.             else 1
  294.         END) = 1)
  295.         )
  296.     )
  297.  
  298. set @total = @rowCount + @@ROWCOUNT    
  299.  
  300.  
  301. SELECT
  302.     tourID,
  303.     tourName,
  304.     outfiterName,
  305.     outfiterID,
  306.     price,
  307.     [discount],
  308.     [discountPrice],
  309.     [discountDate],
  310.     tourDesc,
  311.     countryID,
  312.     countryName,
  313.     goos,
  314.     thumb,
  315.     duration,
  316.     duration2,
  317.     durationName,
  318.     beginDate,
  319.     endDate,
  320.     minAge,
  321.     minPeopleCount
  322. FROM @tbl
  323.     where  
  324.     (CASE @sort
  325.         WHEN 'price' THEN PriceSort
  326.         WHEN 'new' THEN NewSort    
  327.         WHEN 'country' THEN CountrySort    
  328.         WHEN 'popular' THEN PopularSort    
  329.      end) BETWEEN @skip+1 AND @skip + @pageSize
  330. ORDER BY
  331.      CASE @sort
  332.         WHEN 'price' THEN PriceSort
  333.         WHEN 'new' THEN NewSort    
  334.         WHEN 'country' THEN CountrySort    
  335.         WHEN 'popular' THEN PopularSort          
  336.      END;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!