-
Notifications
You must be signed in to change notification settings - Fork 0
/
tbltblRateSearchCode.sql
345 lines (282 loc) · 14.9 KB
/
tbltblRateSearchCode.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
CREATE TABLE `tblRateSearchCode` (
`RateSearchCodeID` INT(11) NOT NULL AUTO_INCREMENT,
`CodedeckID` INT(11) NOT NULL,
`CompanyID` INT(11) NOT NULL,
`CountryID` INT(11) NOT NULL,
`RowCode` VARCHAR(50) NOT NULL COLLATE 'utf8_unicode_ci',
`Code` VARCHAR(50) NOT NULL COLLATE 'utf8_unicode_ci',
`RowCodeRateID` INT(11) NOT NULL,
`CodeRateID` INT(11) NOT NULL,
PRIMARY KEY (`RateSearchCodeID`),
INDEX `CompanyID` (`CompanyID`),
INDEX `CodeRateID` (`CodeRateID`),
INDEX `Code` (`Code`),
INDEX `RowCodeRateID` (`RowCodeRateID`)
)
COLLATE='utf8_unicode_ci'
ENGINE=InnoDB;
----------------------------------------------------------------------------------------------------------------
tblRateSearchCode
----------------------------------------------------------------------------------------------------------------
RowNo RowCode Code x Description CodedeckID
----------------------------------------------------------------------------------------------------------------
1 9124 9124 India-Fixed 1
2 9124 912 India-Fixed 1
3 9124 91 India-Fixed 1
----------------------------------------------------------------------------------------------------------------
1 9124843 9124843 India-Fixed-Mobile 1
2 9124843 912484 India-Fixed-Mobile 1
3 9124843 91248 India-Fixed-Mobile 1
4 9124843 9124 India-Fixed-Mobile 1
5 9124843 912 India-Fixed-Mobile 1
6 9124843 91 India-Fixed-Mobile 1
----------------------------------------------------------------------------------------------------------------
1 912 912 India-Landline 1
2 912 91 India-Landline 1
----------------------------------------------------------------------------------------------------------------
1 91 91 India 1
----------------------------------------------------------------------------------------------------------------
------------------- NEW QUERY LCR ----------------------------------
IF (@p_ShowAllVendorCodes = 1) THEN
insert into tmp_search_code_ (RowCode,Code)
SELECT DISTINCT rsc.RowCode, rsc.Code FROM tbltblRateSearchCode rsc
INNER JOIN tblRate r on r.Code = rsc.RowCode AND
WHERE r.CompanyID = @p_companyid -- no codedeck condition.
AND
(
(
( CHAR_LENGTH(RTRIM(@p_code)) = 0 OR r.Code LIKE REPLACE(@p_code,'*', '%') )
AND ( @p_Description = '' OR r.Description LIKE REPLACE(@p_Description,'*', '%') )
)
)
order by Code desc;
ELSE
insert into tmp_search_code_ ( RowCode, Code )
SELECT DISTINCT RowCode , Code FROM tbltblRateSearchCode
INNER JOIN tblRate r on r.Code = rsc.RowCode
WHERE r.CompanyID = @p_companyid AND r.CodeDeckId = @p_codedeckID -- codedeck condition.
AND
(
(
( CHAR_LENGTH(RTRIM(@p_code)) = 0 OR r.Code LIKE REPLACE(@p_code,'*', '%') )
AND ( @p_Description = '' OR r.Description LIKE REPLACE(@p_Description,'*', '%') )
)
)
order by Code desc;
END IF;
IF @p_ShowAllVendorCodes = 1 THEN
-- can use same tmp_search_code_ table instead of tmp_all_code_ .
/*insert into tmp_all_code_ (RowCode,Code,RowNo)
select RowCode , loopCode,RowNo
from (
select RowCode , loopCode,
@RowNo := ( CASE WHEN ( @prev_Code = tbl1.RowCode ) THEN @RowNo + 1
ELSE 1
END
) as RowNo,
@prev_Code := tbl1.RowCode
from (
SELECT distinct f.Code as RowCode, LEFT(f.Code, x.RowNo) as loopCode FROM (
SELECT @RowNo := @RowNo + 1 as RowNo
FROM mysql.help_category
,(SELECT @RowNo := 0 ) x
limit 15
) x
INNER JOIN tmp_search_code_ AS f
ON x.RowNo <= LENGTH(f.Code)
order by RowCode desc, LENGTH(loopCode) DESC
) tbl1
, ( Select @RowNo := 0 ) x
) tbl order by RowCode desc, LENGTH(loopCode) DESC ;
*/
ELSE
-- can use same tmp_search_code_ table instead of tmp_all_code_ .
/* insert into tmp_all_code_ (RowCode,Code,RowNo)
select RowCode , loopCode,RowNo
from (
select RowCode , loopCode,
@RowNo := ( CASE WHEN ( @prev_Code = tbl1.RowCode ) THEN @RowNo + 1
ELSE 1
END
) as RowNo,
@prev_Code := tbl1.RowCode
from (
SELECT distinct f.Code as RowCode, LEFT(f.Code, x.RowNo) as loopCode FROM (
SELECT @RowNo := @RowNo + 1 as RowNo
FROM mysql.help_category
,(SELECT @RowNo := 0 ) x
limit 15
) x
INNER JOIN tmp_search_code_ AS f
ON x.RowNo <= LENGTH(f.Code)
AND
(
(
( CHAR_LENGTH(RTRIM(@p_code)) = 0 OR f.Code LIKE REPLACE(@p_code,'*', '%') )
)
)
INNER JOIN tblRate as tr on tr.CodeDeckId = @p_codedeckID AND f.Code=tr.Code
INNER JOIN tblRate as tr1 on tr1.CodeDeckId = @p_codedeckID AND LEFT(f.Code, x.RowNo) = tr1.Code
order by RowCode desc, LENGTH(loopCode) DESC
) tbl1
, ( Select @RowNo := 0 ) x
) tbl order by RowCode desc, LENGTH(loopCode) DESC ;
*/
END IF;
-- new RG Query
insert into tmp_code_ ( RowCode, Code )
SELECT DISTINCT RowCode , Code
from tbltblRateSearchCode rsc
JOIN tmp_Raterules_ rr
ON ( fn_IsEmpty(rr.code) OR (rsc.Code LIKE (REPLACE(rr.code,'*', '%%')) ))
AND
( fn_IsEmpty(rr.DestinationType) OR ( rsc.`Type` = rr.DestinationType ))
AND
( fn_IsEmpty(rr.DestinationCountryID) OR (rsc.`CountryID` = rr.DestinationCountryID ))
where rsc.CodeDeckId = @v_codedeckid_
order by Code,RowCode desc;
insert into tmp_code_origination
SELECT tblRate.code
FROM tblRate
JOIN tmp_Raterules_ rr
ON ( fn_IsEmpty(rr.OriginationCode) OR (tblRate.Code LIKE (REPLACE(rr.OriginationCode,'*', '%%'))) )
AND
( fn_IsEmpty(rr.OriginationType) OR ( tblRate.`Type` = rr.OriginationType ))
AND
( fn_IsEmpty(rr.OriginationCountryID) OR (tblRate.`CountryID` = rr.OriginationCountryID ))
where tblRate.CodeDeckId = @v_codedeckid_
Order by tblRate.code ;
------------------- OLD LCR QUERY ----------------------------------
IF (@p_ShowAllVendorCodes = 1) THEN
insert into tmp_search_code_
SELECT DISTINCT LEFT(f.Code, x.RowNo) as loopCode FROM (
SELECT @RowNo := @RowNo + 1 as RowNo
FROM mysql.help_category
,(SELECT @RowNo := 0 ) x
limit 15
) x
INNER JOIN (
SELECT distinct Code , Description from tblRate
WHERE CompanyID = @p_companyid
AND
(
(
( CHAR_LENGTH(RTRIM(@p_code)) = 0 OR Code LIKE REPLACE(@p_code,'*', '%') )
AND ( @p_Description = '' OR Description LIKE REPLACE(@p_Description,'*', '%') )
)
)
) f
ON x.RowNo <= LENGTH(f.Code)
order by loopCode desc;
ELSE
insert into tmp_search_code_
SELECT DISTINCT LEFT(f.Code, x.RowNo) as loopCode FROM (
SELECT @RowNo := @RowNo + 1 as RowNo
FROM mysql.help_category
,(SELECT @RowNo := 0 ) x
limit 15
) x
INNER JOIN tblRate AS f
ON f.CompanyID = @p_companyid AND f.CodeDeckId = @p_codedeckID
AND
(
(
( CHAR_LENGTH(RTRIM(@p_code)) = 0 OR f.Code LIKE REPLACE(@p_code,'*', '%') )
AND ( fn_IsEmpty(@p_Description) OR f.Description LIKE REPLACE(@p_Description,'*', '%') )
)
)
AND x.RowNo <= LENGTH(f.Code)
order by loopCode desc;
END IF;
IF @p_ShowAllVendorCodes = 1 THEN
insert into tmp_all_code_ (RowCode,Code,RowNo)
select RowCode , loopCode,RowNo
from (
select RowCode , loopCode,
@RowNo := ( CASE WHEN ( @prev_Code = tbl1.RowCode ) THEN @RowNo + 1
ELSE 1
END
) as RowNo,
@prev_Code := tbl1.RowCode
from (
SELECT distinct f.Code as RowCode, LEFT(f.Code, x.RowNo) as loopCode FROM (
SELECT @RowNo := @RowNo + 1 as RowNo
FROM mysql.help_category
,(SELECT @RowNo := 0 ) x
limit 15
) x
INNER JOIN tmp_search_code_ AS f
ON x.RowNo <= LENGTH(f.Code)
order by RowCode desc, LENGTH(loopCode) DESC
) tbl1
, ( Select @RowNo := 0 ) x
) tbl order by RowCode desc, LENGTH(loopCode) DESC ;
ELSE
insert into tmp_all_code_ (RowCode,Code,RowNo)
select RowCode , loopCode,RowNo
from (
select RowCode , loopCode,
@RowNo := ( CASE WHEN ( @prev_Code = tbl1.RowCode ) THEN @RowNo + 1
ELSE 1
END
) as RowNo,
@prev_Code := tbl1.RowCode
from (
SELECT distinct f.Code as RowCode, LEFT(f.Code, x.RowNo) as loopCode FROM (
SELECT @RowNo := @RowNo + 1 as RowNo
FROM mysql.help_category
,(SELECT @RowNo := 0 ) x
limit 15
) x
INNER JOIN tmp_search_code_ AS f
ON x.RowNo <= LENGTH(f.Code)
AND
(
(
( CHAR_LENGTH(RTRIM(@p_code)) = 0 OR f.Code LIKE REPLACE(@p_code,'*', '%') )
)
)
INNER JOIN tblRate as tr on tr.CodeDeckId = @p_codedeckID AND f.Code=tr.Code
INNER JOIN tblRate as tr1 on tr1.CodeDeckId = @p_codedeckID AND LEFT(f.Code, x.RowNo) = tr1.Code
order by RowCode desc, LENGTH(loopCode) DESC
) tbl1
, ( Select @RowNo := 0 ) x
) tbl order by RowCode desc, LENGTH(loopCode) DESC ;
END IF;
----------------------------- RG OLD Query
insert into tmp_code_
SELECT DISTINCT LEFT(f.Code, x.RowNo) as loopCode
FROM (
SELECT @RowNo := @RowNo + 1 as RowNo
FROM mysql.help_category
,(SELECT @RowNo := 0 ) x
limit 15
) x
INNER JOIN
(
SELECT
distinct
tblRate.code
FROM tblRate
JOIN tmp_Raterules_ rr
ON ( fn_IsEmpty(rr.code) OR (tblRate.Code LIKE (REPLACE(rr.code,'*', '%%')) ))
AND
( fn_IsEmpty(rr.DestinationType) OR ( tblRate.`Type` = rr.DestinationType ))
AND
( fn_IsEmpty(rr.DestinationCountryID) OR (tblRate.`CountryID` = rr.DestinationCountryID ))
where tblRate.CodeDeckId = @v_codedeckid_
Order by tblRate.code
) as f
ON x.RowNo <= LENGTH(f.Code) AND f.Code = LEFT(f.Code, x.RowNo) -- Added 14-06-19
order by loopCode desc;
insert into tmp_code_origination
SELECT tblRate.code
FROM tblRate
JOIN tmp_Raterules_ rr
ON ( fn_IsEmpty(rr.OriginationCode) OR (tblRate.Code LIKE (REPLACE(rr.OriginationCode,'*', '%%'))) )
AND
( fn_IsEmpty(rr.OriginationType) OR ( tblRate.`Type` = rr.OriginationType ))
AND
( fn_IsEmpty(rr.OriginationCountryID) OR (tblRate.`CountryID` = rr.OriginationCountryID ))
where tblRate.CodeDeckId = @v_codedeckid_
Order by tblRate.code ;