· 6 years ago · Jul 16, 2019, 11:14 AM
1/* ==Scripting Parameters==
2
3 Source Server Version : SQL Server 2016 (13.0.4474)
4 Source Database Engine Edition : Microsoft SQL Server Standard Edition
5 Source Database Engine Type : Standalone SQL Server
6
7 Target Server Version : SQL Server 2017
8 Target Database Engine Edition : Microsoft SQL Server Standard Edition
9 Target Database Engine Type : Standalone SQL Server
10*/
11
12USE [Reports]
13GO
14/****** Object: StoredProcedure [dbo].[Alert_Income_Access_table_check_NEW] Script Date: 7/16/2019 11:08:06 AM ******/
15SET ANSI_NULLS ON
16GO
17SET QUOTED_IDENTIFIER ON
18GO
19
20
21
22
23
24ALTER PROCEDURE [dbo].[Alert_Income_Access_table_check_NEW]
25
26AS
27BEGIN
28
29
30declare @Brand nvarchar(50);
31declare @GetDepositQueryForExec varchar(max);
32declare @GetDepositQuery varchar(max)
33--declare @currentDatetime datetime = '2017-05-29 09:05:00';
34declare @currentDatetime datetime = getdate();
35
36declare @brands table (brand nvarchar(50), idx smallint Primary Key IDENTITY(1,1))
37insert into @brands select Brand from Company_Brands where company = 'all' and active = 1
38
39
40select @GetDepositQuery = 'select * from openquery(DATABASE,''SELECT
41 ''''SITE'''',
42max(case when name=''''ia_data_generated_intervals'''' then value else null end) as ia_data_generated_intervals,
43max(case when name=''''AFFILIATION_SYSTEM'''' then value else null end) AFFILIATION_SYSTEM
44FROM MdlEnv_tblSystemParams where name in(''''ia_data_generated_intervals'''',''''AFFILIATION_SYSTEM'''')
45'')'
46
47IF OBJECT_ID('tempdb..#DS1') IS NOT NULL DROP TABLE #DS1
48create table #DS1 (brand nvarchar(50),
49Wbatches varchar(200),
50AFFILIATION_SYSTEM varchar(200)) -- table to hold results
51
52DECLARE @i int; DECLARE @numrows int
53SET @i = 1
54SET @numrows = (SELECT COUNT(*) FROM @brands)
55
56IF @numrows > 0
57 WHILE (@i <= (SELECT MAX(idx) FROM @brands))
58
59 BEGIN
60
61
62 select @Brand = (SELECT brand FROM @brands WHERE idx = @i)
63 begin try
64 select @GetDepositQueryForExec = replace(replace(@GetDepositQuery,'SITE',@Brand),'DATABASE',@Brand + '_replica')
65 Insert into #DS1
66 Exec(@GetDepositQueryForExec)
67 end try
68 begin catch
69 select ERROR_MESSAGE();
70 end catch
71
72
73
74 SET @i = @i + 1
75
76 END
77
78 declare @brands2 table (brand nvarchar(50), idx smallint Primary Key IDENTITY(1,1))
79 insert into @brands2 select distinct Brand from #DS1 where AFFILIATION_SYSTEM<>'NO_AFFILIATION_SYSTEM'
80 --select * from @brands2
81 -- select * from #DS1
82 IF OBJECT_ID('tempdb..#brandBatchs') IS NOT NULL DROP TABLE #brandBatchs
83 create table #brandBatchs
84 (brand nvarchar(50),
85 HH int,
86 beforeBatch int)
87
88 insert into #brandBatchs
89 select sub.*,
90 coalesce(
91 lag(sub.hour) over(partition by sub.brand order by sub.hour),
92 (
93 SELECT
94 max(cast(Split.a.value('.', 'VARCHAR(100)')as int)/100) AS hour
95 FROM
96 (
97 SELECT brand,
98 CAST ('<M>' + REPLACE(replace(replace(Wbatches,']',''),'[',''), ',', '</M><M>') + '</M>' AS XML) AS Data
99 FROM #DS1
100 ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a)
101 where A.brand=sub.brand)) as beforeBatch
102 from(
103 SELECT A.brand,
104 cast(Split.a.value('.', 'VARCHAR(100)')as int)/100 AS hour
105 FROM
106 (
107 SELECT brand,
108 CAST ('<M>' + REPLACE(replace(replace(Wbatches,']',''),'[',''), ',', '</M><M>') + '</M>' AS XML) AS Data
109 FROM #DS1
110 ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a) )sub
111
112 -- select * from #brandBatchs
113
114select @GetDepositQuery = 'select * from openquery(DATABASE,''SELECT
115 ''''SITE'''',
116 player_id,
117 creation_time,
118 (select max(creation_time) from income_access_registration) as IALastUpdate
119 from ia_new_registered_players where creation_time between ''''BEFOREHHH'''' and ''''HHHH'''' and permanent_tag = 0
120 and ia_new_registered_players.player_id not in (select playerId from income_access_registration)
121'')'
122
123IF OBJECT_ID('tempdb..#DS3') IS NOT NULL DROP TABLE #DS3
124create table #DS3 (brand nvarchar(50),
125playerid int,
126PlayerReg datetime,
127lastIAUpdate datetime) -- table to hold results
128
129SET @i = 1
130SET @numrows = (SELECT COUNT(*) FROM @brands2)
131
132IF @numrows > 0
133 WHILE (@i <= (SELECT MAX(idx) FROM @brands2))
134
135 BEGIN
136
137 select @Brand = (SELECT brand FROM @brands2 WHERE idx = @i)
138 if exists (select brand,HH,beforeBatch from #brandBatchs where brand=@Brand and iif(#brandBatchs.HH=24,0,#brandBatchs.HH) = cast(datepart(HH,@currentDatetime) as int))
139 begin
140 -- select @Brand
141
142 begin try
143 select @GetDepositQueryForExec = replace(replace(replace(replace(@GetDepositQuery,'SITE',@Brand),'DATABASE',@Brand + '_replica'),'HHHH',concat(cast(@currentDatetime as date),' ',iif(cast(datepart(HH,@currentDatetime) as int)<10,'0',''),cast(datepart(HH,@currentDatetime) as varchar)+':00:00'))
144 ,'BEFOREHHH',concat(cast(dateadd(dd,(select case when (#brandBatchs.brand<>'MagikCasino' and #brandBatchs.HH=9) or #brandBatchs.HH=24 then -1 else 0 end from #brandBatchs where #brandBatchs.brand=@Brand and iif(#brandBatchs.HH=24,0,#brandBatchs.HH)=cast(datepart(HH,@currentDatetime) as int)),@currentDatetime) as date),' ',--building date -1 if hour is 9
145 (select iif(iif(#brandBatchs.beforeBatch=24,0,#brandBatchs.beforeBatch)<10,'0'+cast(iif(#brandBatchs.beforeBatch=24,0,#brandBatchs.beforeBatch) as varchar),cast(#brandBatchs.beforeBatch as varchar)) from #brandBatchs where #brandBatchs.brand=@Brand and iif(#brandBatchs.HH=24,0,#brandBatchs.HH)=cast(datepart(HH,@currentDatetime) as int)),':00:00'))-- building hour converting 24 to 00
146 -- select @GetDepositQueryForExec
147 Insert into #DS3
148 Exec(@GetDepositQueryForExec)
149 end try
150 begin catch
151 select ERROR_MESSAGE();
152 end catch
153
154 -- select concat(cast(getdate() as date),' ',cast(datepart(HH,getdate()) as varchar)+':00:00')
155 end
156 SET @i = @i + 1
157
158 END
159
160 -- select * from #DS3 -- registration
161
162if exists( select * from #DS3 where PlayerReg < lastIAUpdate)
163
164begin
165
166 declare @htmlTable nvarchar(max);
167 set @htmlTable=
168 N'<H1>Missing players from Income Access Registration table </H1>' +
169 N'<table border="1">' +
170 N'<tr><th>brand</th> '+
171 N'<th>player_id</th>' +
172 N'<th>PlayerCreateTime</th>' +
173 N'<th>lastIAUpdate</th>' +
174
175
176 CAST ( ( SELECT td = brand, '',
177 td = playerid, '',
178 td = PlayerReg , '',
179 td = lastIAUpdate
180
181 FROM #DS3
182 WHERE PlayerReg < lastIAUpdate
183 FOR XML PATH('tr'), TYPE
184 ) AS NVARCHAR(MAX) ) +
185 N'</table>' ;
186 declare @mRecipients varchar(200) =(select distinct email from [dbo].[mailing_addresses] where [group]='General Issue2');
187 declare @mCopy_recipients varchar(200) =(select distinct email from [dbo].[mailing_addresses] where [group]='techSupport');
188 EXEC msdb.dbo.sp_send_dbmail
189 --@recipients='ron.s@cg.solutions',
190 @recipients=@mRecipients,
191 @copy_recipients=@mCopy_recipients,
192 --@recipients = 'gil@gamescale.com;it@boitsoft.com',
193 --@copy_recipients = 'technical_support@gamescale.com',
194 @subject = 'Income Access Registration table is not updated',
195 @body = @htmlTable,
196 @body_format = 'HTML',
197 @profile_name= 'MSSQL_Mail'
198end
199
200
201-- IA_sales check
202
203
204declare @GetDepositQueryForExec1 varchar(max);
205declare @GetDepositQuery1 varchar(max)
206select @GetDepositQuery1 = 'select * from openquery(DATABASE,''select ''''SITE'''',
207 missing.*,
208 MAX(CAST(CONCAT(sa.year,
209 ''''-'''',
210 sa.month,
211 ''''-'''',
212 sa.day,
213 '''' '''',
214 sa.batch / 100,
215 '''':00:00'''')
216 AS DATETIME)) AS maxIA
217FROM
218 (SELECT
219 p.id, MAX(S.time) lastestActivity
220 FROM
221 MdlGm_tblPlayers p
222 JOIN MdlGm_tblPlayers_L01 l ON p.id = l.refid
223 JOIN MdlCsh_tblPlayerAggregateResult S ON S.playerId = p.id
224 LEFT JOIN income_access_sales a ON a.playerid = p.id
225 -- AND a.`year` >= YEAR(NOW() - INTERVAL 1 DAY)
226 -- AND a.`month` >= MONTH(NOW() - INTERVAL 1 DAY)
227 -- AND a.`day` >= DAY(NOW() - INTERVAL 1 DAY)
228 WHERE
229 p.CompletReg = 1 AND p.TestAccount = 0
230 AND l.tag_status = 100
231 AND l.permanent_tag = 0
232 -- AND S.time BETWEEN ''''BEFOREHHH'''' AND ''''HHHH''''
233 AND S.time > ''''BEFOREHHH''''
234 AND S.time < ''''HHHH''''
235 AND S.type IN (''''win'''',''''approvedwithdraw'''',''''balanceadjustment'''',''''bet'''',''''cpredem'''',''''bonus'''',''''cashbonus'''',''''deposit'''',''''reversaldeposit'''')
236 AND l.tag_status_modified < ''''HHHH''''
237 AND l.btag IS NOT NULL
238 AND a.playerid IS NULL
239 GROUP BY p.id
240 UNION ALL
241 SELECT
242 p.id, MAX(S.time) lastestActivity
243 FROM
244 MdlGm_tblPlayers p
245 JOIN MdlGm_tblPlayers_L01 l ON p.id = l.refid
246 JOIN MdlCsh_tblPlayerAggregateResult S ON S.playerId = p.id
247 LEFT JOIN income_access_sales a ON a.playerid = p.id
248 -- AND a.`year` >= YEAR(NOW() - INTERVAL 1 DAY)
249 -- AND a.`month` >= MONTH(NOW() - INTERVAL 1 DAY)
250 -- AND a.`day` >= DAY(NOW() - INTERVAL 1 DAY)
251 WHERE
252 p.CompletReg = 1 AND p.TestAccount = 0
253 AND l.tag_status = 100
254 AND l.permanent_tag = 0
255 -- AND S.time BETWEEN ''''BEFOREHHH'''' AND ''''HHHH''''
256 AND S.time > ''''BEFOREHHH''''
257 AND S.time < ''''HHHH''''
258 AND S.type IN (''''win'''',''''approvedwithdraw'''',''''balanceadjustment'''',''''bet'''',''''cpredem'''',''''bonus'''',''''cashbonus'''',''''deposit'''',''''reversaldeposit'''')
259 AND l.tag_status_modified >= ''''HHHH''''
260 AND l.btag IS NOT NULL
261 AND a.playerid IS NULL
262 GROUP BY p.id
263 HAVING TAG_STATUS_IN_DATE(p.id, ''''HHHH'''') > 0) missing
264 LEFT JOIN
265 income_access_sales sa ON sa.playerId = missing.id
266 AND sa.`YEAR` >= YEAR (NOW())
267 AND sa.`MONTH` >= MONTH (NOW())
268 AND sa.`DAY` >= DAY (NOW())
269
270GROUP BY missing.id
271'')'
272
273IF OBJECT_ID('tempdb..#DS2') IS NOT NULL DROP TABLE #DS2
274create table #DS2 (Brand nvarchar(50),
275playerid int,
276lastActivity datetime,
277IALast datetime) -- table to hold results
278
279
280DECLARE @d int; DECLARE @numrows1 int
281SET @d = 1
282SET @numrows1 = (SELECT COUNT(*) FROM @brands2)
283
284IF @numrows1 > 0
285 WHILE (@d <= (SELECT MAX(idx) FROM @brands2))
286
287 BEGIN
288
289
290 select @Brand = (SELECT brand FROM @brands2 WHERE idx = @d)
291 if exists (select brand,HH,beforeBatch from #brandBatchs where brand=@Brand and iif(#brandBatchs.HH=24,0,#brandBatchs.HH) = cast(datepart(HH,@currentDatetime) as int))
292 begin
293 begin try
294 select @GetDepositQueryForExec1 = replace(replace(replace(replace(@GetDepositQuery1,'SITE',@Brand),'DATABASE',@Brand + '_replica'),'HHHH',concat(cast(@currentDatetime as date),' ',iif(cast(datepart(HH,@currentDatetime) as int)<10,'0',''),cast(datepart(HH,@currentDatetime) as varchar)+':00:00'))
295 ,'BEFOREHHH',concat(cast(dateadd(dd,(select case when (#brandBatchs.brand<>'MagikCasino' and #brandBatchs.HH=9) or #brandBatchs.HH=24 then -1 else 0 end from #brandBatchs where #brandBatchs.brand=@Brand and iif(#brandBatchs.HH=24,0,#brandBatchs.HH)=cast(datepart(HH,@currentDatetime) as int)),@currentDatetime) as date),' ',--building date -1 if hour is 9
296 (select iif(iif(#brandBatchs.beforeBatch=24,0,#brandBatchs.beforeBatch)<10,'0'+cast(iif(#brandBatchs.beforeBatch=24,0,#brandBatchs.beforeBatch) as varchar),cast(#brandBatchs.beforeBatch as varchar)) from #brandBatchs where #brandBatchs.brand=@Brand and iif(#brandBatchs.HH=24,0,#brandBatchs.HH)=cast(datepart(HH,@currentDatetime) as int)),':00:00'))-- building hour converting 24 to 00
297 -- select 'sales',@GetDepositQueryForExec1
298 Insert into #DS2
299 Exec(@GetDepositQueryForExec1)
300 end try
301 begin catch
302 select ERROR_MESSAGE();
303 end catch
304
305 end
306
307 SET @d = @d + 1
308
309 END
310
311
312-- select * from #DS2
313
314
315IF OBJECT_ID('tempdb..#g1') IS NOT NULL
316 DROP TABLE #g1
317CREATE TABLE #g1 (-------------change here for your columns-------------
318brand nvarchar(50),
319lastBatchRun datetime
320)
321select @GetDepositQuery = 'select * from openquery(DATABASE,''SELECT
322 ''''SITE'''',
323max(cast(concat(las.year,''''-'''',las.month,''''-'''',las.day,'''' '''',las.batch/100,'''':00:00'''') as datetime)) as lastbatch
324 from income_access_sales las
325'')'
326
327SET @i = 1
328SET @numrows = (SELECT COUNT(*) FROM @brands2)
329IF @numrows > 0
330 WHILE (@i <= (SELECT MAX(idx) FROM @brands2))
331 BEGIN
332
333 select @Brand = (SELECT brand FROM @brands2 WHERE idx = @i)
334select @GetDepositQueryForExec = replace(replace(@GetDepositQuery,'SITE',@Brand),'DATABASE',@Brand + '_replica')
335Insert into #g1
336Exec(@GetDepositQueryForExec)
337 SET @i = @i + 1
338 END
339 -- select * from #g1
340
341
342 /* SELECT #DS2.brand,
343 playerid,
344 lastActivity ,
345 IALast,
346 #g1.lastBatchRun
347 FROM #DS2 left join #g1 on #DS2.Brand=#g1.brand
348 where lastActivity>lastBatchRun*/
349
350if exists( select * from #DS2 left join #g1 on #DS2.Brand=#g1.brand
351 where lastActivity>lastBatchRun)
352
353begin
354
355 declare @htmlTable1 nvarchar(max);
356 set @htmlTable=
357 N'<H1>Missing players from Income Access Sales table </H1>' +
358 N'<table border="1">' +
359 N'<tr><th>brand</th> '+
360 N'<th>playerid</th>' +
361 N'<th>lastest Activity</th>' +
362 N'<th>Last Appearance in IASales</th>' +
363 N'<th>IA Last Update</th>' +
364
365 CAST ( ( SELECT td = #DS2.brand, '',
366 td = playerid, '',
367 td = lastActivity , '',
368 td= case when convert(varchar,IALast,120) is null then ' ' else convert(varchar,IALast,120) end, '',
369 td= #g1.lastBatchRun
370 FROM #DS2 left join #g1 on #DS2.Brand=#g1.brand
371 where lastActivity>lastBatchRun
372 FOR XML PATH('tr'), TYPE
373 ) AS NVARCHAR(MAX) ) +
374 N'</table>' ;
375 declare @mRecipients1 varchar(200) =(select distinct email from [dbo].[mailing_addresses] where [group]='General Issue2');
376 declare @mCopy_recipients1 varchar(200) =(select distinct email from [dbo].[mailing_addresses] where [group]='techSupport');
377 EXEC msdb.dbo.sp_send_dbmail
378 --@recipients='ron.s@cg.solutions',
379 @recipients=@mRecipients1,
380 @copy_recipients=@mCopy_recipients1,
381 --@recipients = 'gil@gamescale.com;it@boitsoft.com',
382 --@copy_recipients = 'technical_support@gamescale.com',
383 @subject = 'Income Access Sales table is not updated',
384 @body = @htmlTable,
385 @body_format = 'HTML',
386 @profile_name= 'MSSQL_Mail'
387end
388
389
390
391
392
393
394
395
396end