1 USE [MapCDE_1]
2 GO
3 /****** Object: StoredProcedure [dbo].[sp_getLocations] Script Date: 04/21/2014 11:27:20 ******/
4 SET ANSI_NULLS ON
5 GO
6 SET QUOTED_IDENTIFIER ON
7 GO
8
9
10
11 -- =============================================
12 -- Author: <Author,,Name>
13 -- Create date: <Create Date,,>
14 -- Description: <Description,,>
15 -- =============================================
16 ALTER PROCEDURE [dbo].[sp_getLocations]
17 @sMainType VarChar(MAX),
18 @sSubRecomand VarChar(30),
19 @sSubScenery VarChar(30),
20 @sSubHotel VarChar(30),
21 @sSubEatery VarChar(100),
22 @sSearchCondition VarChar(MAX),
23 @suserCondition VarChar(MAX),
24 @sUserId VarChar(30),
25 @iCount int,
26 @iStartCount int
27
28 AS
29 --Declare @sSql VarChar(MAX)
30 Declare @sSql1 VarChar(MAX)
31 Declare @sSql2 VarChar(MAX)
32 Declare @sSql3 VarChar(MAX)
33 Declare @sSql4 VarChar(MAX)
34 Declare @sSql5 VarChar(MAX)
35 Declare @sSql6 VarChar(MAX)
36 Declare @sSql7 VarChar(MAX)
37 Declare @sSql8 VarChar(MAX)
38 Declare @sSql9 VarChar(MAX)
39 Declare @sSql10 VarChar(MAX)
40 Declare @sSql11 VarChar(MAX)
41 Declare @iGetCount int
42 set @iGetCount = 0
43 Set @sSql1 = ' SELECT Convert(varchar,L.ID) ID, U.TOUR_TIME, L.COUNTRY, L.PROVINCE, L.CITY, L.LONGITUDE, L.LATITUDE, L.LOCATIONNAME, L.LOCATIONTYPE, L.ADDRESS,L.COMPANYID,''7'' AS HAVETOGO FROM dbo.TBL_LOCATIONINFO L INNER JOIN dbo.TBL_PERSONALMEDIAINFO U ON L.ID = U.LOCATIONID AND U.ISCATEGORYLINK = ''1'' WHERE 1 = 1 '
44 Set @sSql2 = ' SELECT Convert(varchar,L.ID) ID, ISNULL(B.TOUR_TIME, ''0'') AS TOUR_TIME, L.COUNTRY, L.PROVINCE, L.CITY, L.LONGITUDE, L.LATITUDE, L.LOCATIONNAME, L.LOCATIONTYPE, L.ADDRESS,L.COMPANYID,''6'' AS HAVETOGO FROM dbo.TBL_RECOMMENDINFO AS R INNER JOIN dbo.TBL_LOCATIONINFO AS L ON L.ID = R.LOCATIONID LEFT OUTER JOIN dbo.TBL_SCENERYSHOTS AS B ON L.ID = B.LOCATIONID WHERE 1=1 '
45 Set @sSql3 = ' SELECT Convert(varchar,L.ID) ID,''0'' AS TOUR_TIME, L.COUNTRY, L.PROVINCE, L.CITY, L.LONGITUDE, L.LATITUDE, L.LOCATIONNAME, L.LOCATIONTYPE, L.ADDRESS,L.COMPANYID ,''5'' AS HAVETOGO FROM dbo.TBL_LOCATIONINFO L WHERE L.LOCATIONTYPE = ''6'' '
46 Set @sSql4 = ' SELECT Convert(varchar,L.ID) ID,''0'' AS TOUR_TIME, L.COUNTRY, L.PROVINCE, L.CITY, L.LONGITUDE, L.LATITUDE, L.LOCATIONNAME, L.LOCATIONTYPE, L.ADDRESS,L.COMPANYID,''4'' AS HAVETOGO FROM dbo.TBL_RAILWAYSTATION B1 INNER JOIN dbo.TBL_LOCATIONINFO L ON B1.LOCATIONID = L.ID AND B1.DLEVEL = ''1'' WHERE L.LOCATIONTYPE = ''7'' '
47 Set @sSql5 = ' SELECT Convert(varchar,L.ID) ID, ''0'' AS TOUR_TIME, L.COUNTRY, L.PROVINCE, L.CITY, L.LONGITUDE, L.LATITUDE, L.LOCATIONNAME, L.LOCATIONTYPE, L.ADDRESS,L.COMPANYID,''3'' AS HAVETOGO FROM dbo.TBL_LOCATIONINFO L WHERE L.LOCATIONTYPE = ''9'' '
48 Set @sSql6 = ' SELECT Convert(varchar,L.ID) ID, S.TOUR_TIME AS TOUR_TIME, L.COUNTRY, L.PROVINCE, L.CITY, L.LONGITUDE, L.LATITUDE, L.LOCATIONNAME, L.LOCATIONTYPE, L.ADDRESS,L.COMPANYID ,S.HAVETOGO AS HAVETOGO FROM dbo.TBL_LOCATIONINFO L LEFT JOIN dbo.TBL_SCENERYSHOTS S ON L.ID = S.LOCATIONID WHERE L.LOCATIONTYPE = ''5'' '
49 Set @sSql7 = ' SELECT Convert(varchar,L.ID) ID, ''0'' AS TOUR_TIME, L.COUNTRY, L.PROVINCE, L.CITY, L.LONGITUDE, L.LATITUDE, L.LOCATIONNAME, L.LOCATIONTYPE, L.ADDRESS,L.COMPANYID,''0'' AS HAVETOGO FROM dbo.TBL_LOCATIONINFO L LEFT JOIN dbo.TBL_HOTELS S ON L.ID = S.LOCATIONID WHERE L.LOCATIONTYPE = ''3'' '
50 Set @sSql8 = ' SELECT Convert(varchar,L.ID) ID, ''0'' AS TOUR_TIME, L.COUNTRY, L.PROVINCE, L.CITY, L.LONGITUDE, L.LATITUDE, L.LOCATIONNAME, L.LOCATIONTYPE, L.ADDRESS,L.COMPANYID,''0'' AS HAVETOGO FROM dbo.TBL_LOCATIONINFO L LEFT JOIN dbo.TBL_EATRY S ON L.ID = S.LOCATIONID WHERE L.LOCATIONTYPE = ''2'' '
51 Set @sSql9 = ' SELECT Convert(varchar,L.ID) ID, ''0'' AS TOUR_TIME, L.COUNTRY, L.PROVINCE, L.CITY, L.LONGITUDE, L.LATITUDE, L.LOCATIONNAME, L.LOCATIONTYPE, L.ADDRESS,L.COMPANYID ,''0'' AS HAVETOGO FROM dbo.TBL_LOCATIONINFO L WHERE L.LOCATIONTYPE = ''4'' '
52 Set @sSql10 = ' SELECT (''_''+Convert(varchar,L.ID)) as ID , ''0'' AS TOUR_TIME, L.COUNTRYNAME as COUNTRY, L.PROVINCENAME as PROVINCE, L.CITYNAME as CITY, L.LONGITUDE, L.LATITUDE, L.LOCATIONNAME, L.LOCATIONTYPE, L.ADDRESS,L.COMPANYID,''0'' AS HAVETOGO FROM dbo.TBL_USERLOC L LEFT JOIN dbo.TBL_USERS U ON L.COMPANYID = U.COMPANY_ID WHERE 1 = 1 '
53 Set @sSql11 = ' SELECT Convert(varchar,L.ID) ID, ''0'' AS TOUR_TIME, L.COUNTRY, L.PROVINCE, L.CITY, L.LONGITUDE, L.LATITUDE, L.LOCATIONNAME, L.LOCATIONTYPE, L.ADDRESS,L.COMPANYID ,''0'' AS HAVETOGO FROM dbo.TBL_LOCATIONINFO L WHERE L.LOCATIONTYPE = ''8'' '
54
55
56
57 If @sUserId <> ''
58 Set @sSql1 = @sSql1 + ' And U.USERID in (' + @sUserId + ')'
59
60 If @sUserId <> ''
61 Set @sSql10 = @sSql10 + ' And U.ID in (' + @sUserId + ')'
62
63 If @sSubRecomand <> ''
64 Set @sSql2 = @sSql2 + ' And R.COMMENDLEV in (' + @sSubRecomand + ')'
65
66 If @sSubHotel <> ''
67 Set @sSql7 = @sSql7 + ' And S.STAR in (' + @sSubHotel + ')'
68
69 If @sSubScenery <> ''
70 Set @sSql6 = @sSql6 + ' And S.HAVETOGO in (' + @sSubScenery + ') '
71
72 If @sSubEatery <> ''
73 Set @sSql8 = @sSql8 + ' And S.DTYPE in (' + @sSubEatery + ')'
74
75 If @iGetCount < @iCount
76 Begin
77 EXEC ('Insert into TBL_TEMPLOCATION ' + @sSql10+ @suserCondition + @sSearchCondition)
78
79 Set @iGetCount = @iGetCount + @@ROWCOUNT
80 End
81
82 If CHARINDEX('0', @sMainType) > 0 And @iGetCount < @iCount
83 Begin
84 --Set @sSQL = 'Insert into TBL_TEMPLOCATION ' + @sSql1 + ' WHERE ' + @sSearchCondition
85 EXEC ('Insert into TBL_TEMPLOCATION ' + @sSql1 + @sSearchCondition)
86 Set @iGetCount = @iGetCount + @@ROWCOUNT
87 End
88
89
90 If CHARINDEX('1', @sMainType) > 0 And @iGetCount < @iCount
91 Begin
92 EXEC ('Insert into TBL_TEMPLOCATION ' + @sSql2 + @sSearchCondition +
93 'And Convert(varchar,L.ID) not in (SELECT ID FROM TBL_TEMPLOCATION )')
94
95 Set @iGetCount = @iGetCount + @@ROWCOUNT
96 End
97
98 If CHARINDEX('6', @sMainType) > 0 And @iGetCount < @iCount
99 Begin
100 EXEC ('Insert into TBL_TEMPLOCATION ' + @sSql3 + @sSearchCondition + '
101 And Convert(varchar,L.ID) not in (SELECT ID FROM TBL_TEMPLOCATION )')
102
103 Set @iGetCount = @iGetCount + @@ROWCOUNT
104 End
105
106 If CHARINDEX('7', @sMainType) > 0 And @iGetCount < @iCount
107 Begin
108 EXEC ('Insert into TBL_TEMPLOCATION ' + @sSql4 + @sSearchCondition + '
109 And Convert(varchar,L.ID) not in (SELECT ID FROM TBL_TEMPLOCATION )')
110
111 Set @iGetCount = @iGetCount + @@ROWCOUNT
112 End
113
114 If CHARINDEX('9', @sMainType) > 0 And @iGetCount < @iCount
115 Begin
116 EXEC ('Insert into TBL_TEMPLOCATION ' + @sSql5 + @sSearchCondition + '
117 And Convert(varchar,L.ID) not in (SELECT ID FROM TBL_TEMPLOCATION )')
118
119 Set @iGetCount = @iGetCount + @@ROWCOUNT
120 End
121
122
123 If CHARINDEX('5', @sMainType) > 0 And @iGetCount < @iCount
124 Begin
125 --select 'Insert into TBL_TEMPLOCATION ' + @sSql6 + ' And ' + @sSearchCondition + '
126 -- And L.ID not in (SELECT ID FROM TBL_TEMPLOCATION )'
127 EXEC ('Insert into TBL_TEMPLOCATION ' + @sSql6 + @sSearchCondition + '
128 And Convert(varchar,L.ID) not in (SELECT ID FROM TBL_TEMPLOCATION ) order by S.HAVETOGO desc')
129
130 Set @iGetCount = @iGetCount + @@ROWCOUNT
131 End
132
133 If CHARINDEX('3', @sMainType) > 0 And @iGetCount < @iCount
134 Begin
135 --select 'Insert into TBL_TEMPLOCATION ' + @sSql7 + ' And ' + @sSearchCondition + '
136 -- And L.ID not in (SELECT ID FROM TBL_TEMPLOCATION )'
137 EXEC ('Insert into TBL_TEMPLOCATION ' + @sSql7 + @sSearchCondition + '
138 And Convert(varchar,L.ID) not in (SELECT ID FROM TBL_TEMPLOCATION )')
139
140 Set @iGetCount = @iGetCount + @@ROWCOUNT
141 End
142
143 If CHARINDEX('2', @sMainType) > 0 And @iGetCount < @iCount
144 Begin
145 EXEC ('Insert into TBL_TEMPLOCATION ' + @sSql8 + @sSearchCondition + '
146 And Convert(varchar,L.ID) not in (SELECT ID FROM TBL_TEMPLOCATION )')
147
148 Set @iGetCount = @iGetCount + @@ROWCOUNT
149 End
150
151 If CHARINDEX('4', @sMainType) > 0 And @iGetCount < @iCount
152 Begin
153 EXEC ('Insert into TBL_TEMPLOCATION ' + @sSql9 + @sSearchCondition + '
154 And Convert(varchar,L.ID) not in (SELECT ID FROM TBL_TEMPLOCATION )')
155
156 Set @iGetCount = @iGetCount + @@ROWCOUNT
157 End
158 If CHARINDEX('8', @sMainType) > 0 And @iGetCount < @iCount
159 Begin
160 EXEC ('Insert into TBL_TEMPLOCATION ' + @sSql11 + @sSearchCondition + '
161 And Convert(varchar,L.ID) not in (SELECT ID FROM TBL_TEMPLOCATION )')
162
163 Set @iGetCount = @iGetCount + @@ROWCOUNT
164 End
165
166 --Exec ('SELECT * FROM TBL_TEMPLOCATION
167 -- DELETE FROM TBL_TEMPLOCATION')
168 -- --DELETE FROM TBL_TEMPLOCATION
169 Exec ('SELECT k.* from(SELECT ROW_NUMBER()over(order by HAVETOGO desc ) rownumber, ID, TOUR_TIME,
170 COUNTRY, PROVINCE, CITY, LONGITUDE, LATITUDE, LOCATIONNAME, LOCATIONTYPE, ADDRESS,COMPANYID,HAVETOGO
171 FROM TBL_TEMPLOCATION)k where k.rownumber> '+ @iStartCount +' And k.rownumber <= '+ @iCount +';
172 DELETE FROM TBL_TEMPLOCATION;')
