Advertisement
gilstr

Untitled

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