Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /* ==Scripting Parameters==
- Source Server Version : SQL Server 2016 (13.0.4474)
- Source Database Engine Edition : Microsoft SQL Server Standard Edition
- Source Database Engine Type : Standalone SQL Server
- Target Server Version : SQL Server 2017
- Target Database Engine Edition : Microsoft SQL Server Standard Edition
- Target Database Engine Type : Standalone SQL Server
- */
- USE [Reports]
- GO
- /****** Object: StoredProcedure [dbo].[Alert_Income_Access_table_check_NEW] Script Date: 7/16/2019 11:08:06 AM ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
-
- ALTER PROCEDURE [dbo].[Alert_Income_Access_table_check_NEW]
-
- AS
- BEGIN
- declare @Brand nvarchar(50);
- declare @GetDepositQueryForExec varchar(max);
- declare @GetDepositQuery varchar(max)
- --declare @currentDatetime datetime = '2017-05-29 09:05:00';
- declare @currentDatetime datetime = getdate();
- declare @brands table (brand nvarchar(50), idx smallint Primary Key IDENTITY(1,1))
- insert into @brands select Brand from Company_Brands where company = 'all' and active = 1
- select @GetDepositQuery = 'select * from openquery(DATABASE,''SELECT
- ''''SITE'''',
- max(case when name=''''ia_data_generated_intervals'''' then value else null end) as ia_data_generated_intervals,
- max(case when name=''''AFFILIATION_SYSTEM'''' then value else null end) AFFILIATION_SYSTEM
- FROM MdlEnv_tblSystemParams where name in(''''ia_data_generated_intervals'''',''''AFFILIATION_SYSTEM'''')
- '')'
- IF OBJECT_ID('tempdb..#DS1') IS NOT NULL DROP TABLE #DS1
- create table #DS1 (brand nvarchar(50),
- Wbatches varchar(200),
- AFFILIATION_SYSTEM varchar(200)) -- table to hold results
- DECLARE @i int; DECLARE @numrows int
- SET @i = 1
- SET @numrows = (SELECT COUNT(*) FROM @brands)
- IF @numrows > 0
- WHILE (@i <= (SELECT MAX(idx) FROM @brands))
- BEGIN
- select @Brand = (SELECT brand FROM @brands WHERE idx = @i)
- begin try
- select @GetDepositQueryForExec = replace(replace(@GetDepositQuery,'SITE',@Brand),'DATABASE',@Brand + '_replica')
- Insert into #DS1
- Exec(@GetDepositQueryForExec)
- end try
- begin catch
- select ERROR_MESSAGE();
- end catch
- SET @i = @i + 1
- END
- declare @brands2 table (brand nvarchar(50), idx smallint Primary Key IDENTITY(1,1))
- insert into @brands2 select distinct Brand from #DS1 where AFFILIATION_SYSTEM<>'NO_AFFILIATION_SYSTEM'
- --select * from @brands2
- -- select * from #DS1
- IF OBJECT_ID('tempdb..#brandBatchs') IS NOT NULL DROP TABLE #brandBatchs
- create table #brandBatchs
- (brand nvarchar(50),
- HH int,
- beforeBatch int)
- insert into #brandBatchs
- select sub.*,
- coalesce(
- lag(sub.hour) over(partition by sub.brand order by sub.hour),
- (
- SELECT
- max(cast(Split.a.value('.', 'VARCHAR(100)')as int)/100) AS hour
- FROM
- (
- SELECT brand,
- CAST ('<M>' + REPLACE(replace(replace(Wbatches,']',''),'[',''), ',', '</M><M>') + '</M>' AS XML) AS Data
- FROM #DS1
- ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a)
- where A.brand=sub.brand)) as beforeBatch
- from(
- SELECT A.brand,
- cast(Split.a.value('.', 'VARCHAR(100)')as int)/100 AS hour
- FROM
- (
- SELECT brand,
- CAST ('<M>' + REPLACE(replace(replace(Wbatches,']',''),'[',''), ',', '</M><M>') + '</M>' AS XML) AS Data
- FROM #DS1
- ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a) )sub
- -- select * from #brandBatchs
- select @GetDepositQuery = 'select * from openquery(DATABASE,''SELECT
- ''''SITE'''',
- player_id,
- creation_time,
- (select max(creation_time) from income_access_registration) as IALastUpdate
- from ia_new_registered_players where creation_time between ''''BEFOREHHH'''' and ''''HHHH'''' and permanent_tag = 0
- and ia_new_registered_players.player_id not in (select playerId from income_access_registration)
- '')'
- IF OBJECT_ID('tempdb..#DS3') IS NOT NULL DROP TABLE #DS3
- create table #DS3 (brand nvarchar(50),
- playerid int,
- PlayerReg datetime,
- lastIAUpdate datetime) -- table to hold results
- SET @i = 1
- SET @numrows = (SELECT COUNT(*) FROM @brands2)
- IF @numrows > 0
- WHILE (@i <= (SELECT MAX(idx) FROM @brands2))
- BEGIN
- select @Brand = (SELECT brand FROM @brands2 WHERE idx = @i)
- 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))
- begin
- -- select @Brand
- begin try
- 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'))
- ,'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
- (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
- -- select @GetDepositQueryForExec
- Insert into #DS3
- Exec(@GetDepositQueryForExec)
- end try
- begin catch
- select ERROR_MESSAGE();
- end catch
- -- select concat(cast(getdate() as date),' ',cast(datepart(HH,getdate()) as varchar)+':00:00')
- end
- SET @i = @i + 1
- END
- -- select * from #DS3 -- registration
- if exists( select * from #DS3 where PlayerReg < lastIAUpdate)
- begin
- declare @htmlTable nvarchar(max);
- set @htmlTable=
- N'<H1>Missing players from Income Access Registration table </H1>' +
- N'<table border="1">' +
- N'<tr><th>brand</th> '+
- N'<th>player_id</th>' +
- N'<th>PlayerCreateTime</th>' +
- N'<th>lastIAUpdate</th>' +
- CAST ( ( SELECT td = brand, '',
- td = playerid, '',
- td = PlayerReg , '',
- td = lastIAUpdate
- FROM #DS3
- WHERE PlayerReg < lastIAUpdate
- FOR XML PATH('tr'), TYPE
- ) AS NVARCHAR(MAX) ) +
- N'</table>' ;
- declare @mRecipients varchar(200) =(select distinct email from [dbo].[mailing_addresses] where [group]='General Issue2');
- declare @mCopy_recipients varchar(200) =(select distinct email from [dbo].[mailing_addresses] where [group]='techSupport');
- EXEC msdb.dbo.sp_send_dbmail
- --@recipients='ron.s@cg.solutions',
- @recipients=@mRecipients,
- @copy_recipients=@mCopy_recipients,
- --@recipients = 'gil@gamescale.com;it@boitsoft.com',
- --@copy_recipients = 'technical_support@gamescale.com',
- @subject = 'Income Access Registration table is not updated',
- @body = @htmlTable,
- @body_format = 'HTML',
- @profile_name= 'MSSQL_Mail'
- end
- -- IA_sales check
- declare @GetDepositQueryForExec1 varchar(max);
- declare @GetDepositQuery1 varchar(max)
- select @GetDepositQuery1 = 'select * from openquery(DATABASE,''select ''''SITE'''',
- missing.*,
- MAX(CAST(CONCAT(sa.year,
- ''''-'''',
- sa.month,
- ''''-'''',
- sa.day,
- '''' '''',
- sa.batch / 100,
- '''':00:00'''')
- AS DATETIME)) AS maxIA
- FROM
- (SELECT
- p.id, MAX(S.time) lastestActivity
- FROM
- MdlGm_tblPlayers p
- JOIN MdlGm_tblPlayers_L01 l ON p.id = l.refid
- JOIN MdlCsh_tblPlayerAggregateResult S ON S.playerId = p.id
- LEFT JOIN income_access_sales a ON a.playerid = p.id
- -- AND a.`year` >= YEAR(NOW() - INTERVAL 1 DAY)
- -- AND a.`month` >= MONTH(NOW() - INTERVAL 1 DAY)
- -- AND a.`day` >= DAY(NOW() - INTERVAL 1 DAY)
- WHERE
- p.CompletReg = 1 AND p.TestAccount = 0
- AND l.tag_status = 100
- AND l.permanent_tag = 0
- -- AND S.time BETWEEN ''''BEFOREHHH'''' AND ''''HHHH''''
- AND S.time > ''''BEFOREHHH''''
- AND S.time < ''''HHHH''''
- AND S.type IN (''''win'''',''''approvedwithdraw'''',''''balanceadjustment'''',''''bet'''',''''cpredem'''',''''bonus'''',''''cashbonus'''',''''deposit'''',''''reversaldeposit'''')
- AND l.tag_status_modified < ''''HHHH''''
- AND l.btag IS NOT NULL
- AND a.playerid IS NULL
- GROUP BY p.id
- UNION ALL
- SELECT
- p.id, MAX(S.time) lastestActivity
- FROM
- MdlGm_tblPlayers p
- JOIN MdlGm_tblPlayers_L01 l ON p.id = l.refid
- JOIN MdlCsh_tblPlayerAggregateResult S ON S.playerId = p.id
- LEFT JOIN income_access_sales a ON a.playerid = p.id
- -- AND a.`year` >= YEAR(NOW() - INTERVAL 1 DAY)
- -- AND a.`month` >= MONTH(NOW() - INTERVAL 1 DAY)
- -- AND a.`day` >= DAY(NOW() - INTERVAL 1 DAY)
- WHERE
- p.CompletReg = 1 AND p.TestAccount = 0
- AND l.tag_status = 100
- AND l.permanent_tag = 0
- -- AND S.time BETWEEN ''''BEFOREHHH'''' AND ''''HHHH''''
- AND S.time > ''''BEFOREHHH''''
- AND S.time < ''''HHHH''''
- AND S.type IN (''''win'''',''''approvedwithdraw'''',''''balanceadjustment'''',''''bet'''',''''cpredem'''',''''bonus'''',''''cashbonus'''',''''deposit'''',''''reversaldeposit'''')
- AND l.tag_status_modified >= ''''HHHH''''
- AND l.btag IS NOT NULL
- AND a.playerid IS NULL
- GROUP BY p.id
- HAVING TAG_STATUS_IN_DATE(p.id, ''''HHHH'''') > 0) missing
- LEFT JOIN
- income_access_sales sa ON sa.playerId = missing.id
- AND sa.`YEAR` >= YEAR (NOW())
- AND sa.`MONTH` >= MONTH (NOW())
- AND sa.`DAY` >= DAY (NOW())
- GROUP BY missing.id
- '')'
- IF OBJECT_ID('tempdb..#DS2') IS NOT NULL DROP TABLE #DS2
- create table #DS2 (Brand nvarchar(50),
- playerid int,
- lastActivity datetime,
- IALast datetime) -- table to hold results
- DECLARE @d int; DECLARE @numrows1 int
- SET @d = 1
- SET @numrows1 = (SELECT COUNT(*) FROM @brands2)
- IF @numrows1 > 0
- WHILE (@d <= (SELECT MAX(idx) FROM @brands2))
- BEGIN
- select @Brand = (SELECT brand FROM @brands2 WHERE idx = @d)
- 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))
- begin
- begin try
- 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'))
- ,'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
- (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
- -- select 'sales',@GetDepositQueryForExec1
- Insert into #DS2
- Exec(@GetDepositQueryForExec1)
- end try
- begin catch
- select ERROR_MESSAGE();
- end catch
- end
- SET @d = @d + 1
- END
- -- select * from #DS2
- IF OBJECT_ID('tempdb..#g1') IS NOT NULL
- DROP TABLE #g1
- CREATE TABLE #g1 (-------------change here for your columns-------------
- brand nvarchar(50),
- lastBatchRun datetime
- )
- select @GetDepositQuery = 'select * from openquery(DATABASE,''SELECT
- ''''SITE'''',
- max(cast(concat(las.year,''''-'''',las.month,''''-'''',las.day,'''' '''',las.batch/100,'''':00:00'''') as datetime)) as lastbatch
- from income_access_sales las
- '')'
- SET @i = 1
- SET @numrows = (SELECT COUNT(*) FROM @brands2)
- IF @numrows > 0
- WHILE (@i <= (SELECT MAX(idx) FROM @brands2))
- BEGIN
- select @Brand = (SELECT brand FROM @brands2 WHERE idx = @i)
- select @GetDepositQueryForExec = replace(replace(@GetDepositQuery,'SITE',@Brand),'DATABASE',@Brand + '_replica')
- Insert into #g1
- Exec(@GetDepositQueryForExec)
- SET @i = @i + 1
- END
- -- select * from #g1
- /* SELECT #DS2.brand,
- playerid,
- lastActivity ,
- IALast,
- #g1.lastBatchRun
- FROM #DS2 left join #g1 on #DS2.Brand=#g1.brand
- where lastActivity>lastBatchRun*/
- if exists( select * from #DS2 left join #g1 on #DS2.Brand=#g1.brand
- where lastActivity>lastBatchRun)
- begin
- declare @htmlTable1 nvarchar(max);
- set @htmlTable=
- N'<H1>Missing players from Income Access Sales table </H1>' +
- N'<table border="1">' +
- N'<tr><th>brand</th> '+
- N'<th>playerid</th>' +
- N'<th>lastest Activity</th>' +
- N'<th>Last Appearance in IASales</th>' +
- N'<th>IA Last Update</th>' +
- CAST ( ( SELECT td = #DS2.brand, '',
- td = playerid, '',
- td = lastActivity , '',
- td= case when convert(varchar,IALast,120) is null then ' ' else convert(varchar,IALast,120) end, '',
- td= #g1.lastBatchRun
- FROM #DS2 left join #g1 on #DS2.Brand=#g1.brand
- where lastActivity>lastBatchRun
- FOR XML PATH('tr'), TYPE
- ) AS NVARCHAR(MAX) ) +
- N'</table>' ;
- declare @mRecipients1 varchar(200) =(select distinct email from [dbo].[mailing_addresses] where [group]='General Issue2');
- declare @mCopy_recipients1 varchar(200) =(select distinct email from [dbo].[mailing_addresses] where [group]='techSupport');
- EXEC msdb.dbo.sp_send_dbmail
- --@recipients='ron.s@cg.solutions',
- @recipients=@mRecipients1,
- @copy_recipients=@mCopy_recipients1,
- --@recipients = 'gil@gamescale.com;it@boitsoft.com',
- --@copy_recipients = 'technical_support@gamescale.com',
- @subject = 'Income Access Sales table is not updated',
- @body = @htmlTable,
- @body_format = 'HTML',
- @profile_name= 'MSSQL_Mail'
- end
- end
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement