-
Portfolio:
- Development |
- Production |
- Animation |
- Design |
- Illustration |
- Performance |
- Writing |
- Marketing |
- Direction
This automated SQL SP progressed a drawing prize amount for every jackpot over a certain amount detected on a specific range of gaming machines (those in the high limit room). It was hand-coded using SQL Query Analyzer, and fed variables to a Flash animation displaying the progressive. This ran every 5 minutes, within scheduled jobs on a SQL Server, and used BCP as well as an XP_CMDSHELL call to initiate copying of text files containing generated variables to a remote computer to feed the associated Flash application.
Jackpot Progressive (SQL Stored Procedure), 2008:
if exists (select * from dbo.sysobjects where id = object_id('dbo.usp_GoldenJackpotProgressive') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.usp_GoldenJackpotProgressive
GO
/***************************************************************************
Date Version Name
09/27/2008 11.5.2 Dana Laratta
Automated SP to increment progressive based on jackpot $1,200+
located in the High Limit room. Exports to a local .txt file
and calls a .bat file to copy results to Flash animation local
***************************************************************************/
CREATE PROCEDURE dbo.usp_GoldenJackpotProgressive
@increment_amount money
, @increment_jpminimum money
, @specifiedmeasurestart datetime = NULL
, @specifiedmeasureend datetime = NULL
AS
BEGIN
--vars for testing
--DECLARE @increment_amount money
--DECLARE @increment_jpminimum money
--DECLARE @specifiedmeasurestart datetime
--DECLARE @specifiedmeasureend datetime
--set @increment_amount = 25
--set @increment_jpminimum = 1200
--Track Qualifying Jackpots in Subset of SlotNumbers and Icrement a Figure by $25 apieace
--Create Table to hold last five qualifying jackpots
if exists (select * from dbo.sysobjects where id = object_id(N'usr_GoldenJackpotProgDisplay_Last5') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table gcgmkt.dbo.usr_GoldenJackpotProgDisplay_Last5
CREATE TABLE gcgmkt.dbo.usr_GoldenJackpotProgDisplay_Last5
(DisplayDatetxt varchar(10)
, SlotNumbertxt varchar(6)
, Denominationtxt varchar(6)
, Amounttxt money
, JackpotFillDate datetime)
--Declare local variables
DECLARE @progressive money
, @progressions int
, @progressivetxt varchar(20)
, @progressionstxt varchar(20)
, @measurestart datetime
, @measureend datetime
, @lastdaytxt varchar(10)
, @firstdaytxt varchar(10)
, @Output varchar(3000)
, @bcpCommand varchar(8000)
if @specifiedmeasurestart is not null
begin
set @measurestart = @specifiedmeasurestart
end
else
begin
set @measurestart = dateadd(hh,+3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
end
set @firstdaytxt = convert(varchar(10), @measurestart, 101)
if @specifiedmeasureend is not null
begin
set @measureend = @specifiedmeasureend
end
else
begin
set @measureend = dateadd(hh,-6,dateadd(mm,1+datediff(mm,0,getdate()),0))
end
set @lastdaytxt = convert(varchar(10), @measureend, 101)
--print @measurestart
--print @firstdaytxt
--print @measureend
--print @lastdaytxt
--Populate Table to hold last five qualifying jackpots
insert into gcgmkt.dbo.usr_GoldenJackpotProgDisplay_Last5
(DisplayDatetxt
, SlotNumbertxt
, Denominationtxt
, Amounttxt
, JackpotFillDate)
select top 5
isnull(convert(varchar(10),tx.MachineDateTime, 110),0),
isnull(convert(varchar(6),tx.SlotID),0),
isnull(convert(varchar(6),tx.Denomination),0),
isnull(convert(money, tx.JackpotFillAmt),0),
isnull(tx.MachineDateTime,0)
from gmgoasissql.WinOasis.dbo.JF_JACKPOTFILL tx
INNER JOIN usr_gmg_gjr_slotnumbers gjr
ON tx.SlotID=gjr.SlotNumber
Where tx.MachineDateTime > @measurestart
and tx.MachineDateTime < @measureend
and tx.JackpotFillAmt >= @increment_jpminimum
and tx.TransType = 'J'
and tx.IsCancelCredit = 'N'
and tx.VoidedDateTime is null
order by tx.MachineDateTime desc
--Populate Progressive Prize Variable
select @progressions = isnull(count(distinct tx.TicketNum), 0)
,@progressive = isnull((count(distinct tx.TicketNum) * @increment_amount), 0)
from gmgoasissql.WinOasis.dbo.JF_JACKPOTFILL tx
INNER JOIN usr_gmg_gjr_slotnumbers gjr
ON tx.SlotID=gjr.SlotNumber
Where tx.MachineDateTime > @measurestart
and tx.MachineDateTime < @measureend
and tx.JackpotFillAmt >= @increment_jpminimum
and tx.TransType = 'J'
and tx.IsCancelCredit = 'N'
and tx.VoidedDateTime is null
set @progressivetxt = REPLACE( convert(varchar(20), @progressive, 1) ,'.00', '')
set @progressionstxt = convert(varchar(20), @progressions)
--print @progressivetxt
--print @progressionstxt
--Variables for output of last 5
DECLARE @d1 varchar(10)
, @n1 varchar(6)
, @dn1 varchar(6)
, @amt1 varchar(20)
, @d2 varchar(10)
, @n2 varchar(6)
, @dn2 varchar(6)
, @amt2 varchar(20)
, @d3 varchar(10)
, @n3 varchar(6)
, @dn3 varchar(6)
, @amt3 varchar(20)
, @d4 varchar(10)
, @n4 varchar(6)
, @dn4 varchar(6)
, @amt4 varchar(20)
, @d5 varchar(10)
, @n5 varchar(6)
, @dn5 varchar(6)
, @amt5 varchar(20)
SELECT @d1 = DisplayDatetxt from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
SELECT @n1 = SlotNumbertxt from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
SELECT @dn1 = Denominationtxt from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
SELECT @amt1 = replace( convert(varchar(20), Amounttxt, 1) ,'.00', '') from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
DELETE FROM usr_GoldenJackpotProgDisplay_Last5
WHERE DisplayDatetxt = @d1
and SlotNumbertxt = @n1
and Denominationtxt = @dn1
and Amounttxt = convert(money, @amt1)
SELECT @d2 = DisplayDatetxt from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
SELECT @n2 = SlotNumbertxt from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
SELECT @dn2 = Denominationtxt from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
SELECT @amt2 = replace( convert(varchar(20), Amounttxt, 1) ,'.00', '') from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
DELETE FROM usr_GoldenJackpotProgDisplay_Last5
WHERE DisplayDatetxt = @d2
and SlotNumbertxt = @n2
and Denominationtxt = @dn2
and Amounttxt = convert(money, @amt2)
SELECT @d3 = DisplayDatetxt from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
SELECT @n3 = SlotNumbertxt from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
SELECT @dn3 = Denominationtxt from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
SELECT @amt3 = replace( convert(varchar(20), Amounttxt, 1) ,'.00', '') from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
DELETE FROM usr_GoldenJackpotProgDisplay_Last5
WHERE DisplayDatetxt = @d3
and SlotNumbertxt = @n3
and Denominationtxt = @dn3
and Amounttxt = convert(money, @amt3)
SELECT @d4 = DisplayDatetxt from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
SELECT @n4 = SlotNumbertxt from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
SELECT @dn4 = Denominationtxt from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
SELECT @amt4 = replace( convert(varchar(20), Amounttxt, 1) ,'.00', '') from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
DELETE FROM usr_GoldenJackpotProgDisplay_Last5
WHERE DisplayDatetxt = @d4
and SlotNumbertxt = @n4
and Denominationtxt = @dn4
and Amounttxt = convert(money, @amt4)
SELECT @d5 = DisplayDatetxt from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
SELECT @n5 = SlotNumbertxt from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
SELECT @dn5 = Denominationtxt from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
SELECT @amt5 = replace( convert(varchar(20), Amounttxt, 1) ,'.00', '') from usr_GoldenJackpotProgDisplay_Last5
where JackpotFillDate in (select top 1 JackpotFillDate from usr_GoldenJackpotProgDisplay_Last5)
DELETE FROM usr_GoldenJackpotProgDisplay_Last5
WHERE DisplayDatetxt = @d5
and SlotNumbertxt = @n5
and Denominationtxt = @dn5
and Amounttxt = convert(money, @amt5)
SET @Output = '&lastdaytxt=' + @lastdaytxt
+ '&firstdaytxt=' + @firstdaytxt
+ '&progressive=' + isnull(@progressivetxt,'0')
+ '&progressions=' + isnull(@progressionstxt,'0')
+ '&d1=' + isnull(@d1,'')
+ '&n1=' + isnull(@n1,'')
+ '&dn1=' + isnull(@dn1,'')
+ '&amt1=' + isnull(@amt1,'')
+ '&d2=' + isnull(@d2,'')
+ '&n2=' + isnull(@n2,'')
+ '&dn2=' + isnull(@dn2,'')
+ '&amt2=' + isnull(@amt2,'')
+ '&d3=' + isnull(@d3,'')
+ '&n3=' + isnull(@n3,'')
+ '&dn3=' + isnull(@dn3,'')
+ '&amt3=' + isnull(@amt3,'')
+ '&d4=' + isnull(@d4,'')
+ '&n4=' + isnull(@n4,'')
+ '&dn4=' + isnull(@dn4,'')
+ '&amt4=' + isnull(@amt4,'')
+ '&d5=' + isnull(@d5,'')
+ '&n5=' + isnull(@n5,'')
+ '&dn5=' + isnull(@dn5,'')
+ '&amt5=' + isnull(@amt5,'')
--print @Output
--print @progressivetxt
--print @progressionstxt
--Create concentrated table of variables for display program
if exists (select * from dbo.sysobjects where id = object_id(N'usr_GoldenJackpotProgDisplay_Output') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table gcgmkt.dbo.usr_GoldenJackpotProgDisplay_Output
CREATE TABLE gcgmkt.dbo.usr_GoldenJackpotProgDisplay_Output (DisplayVars VARCHAR(3000))
INSERT INTO gcgmkt.dbo.usr_GoldenJackpotProgDisplay_Output (DisplayVars) VALUES (@Output)
--run xp_cmdshell to bcp to export winning person info to text
SET @bcpCommand = 'bcp "SELECT * FROM gcgmkt.dbo.usr_GoldenJackpotProgDisplay_Output" queryout C:\sqlexporttemp\GoldenJackpotProg\output.txt -S gcgbhmktsql -U sa -P m@rk3t! -c'
SET NOCOUNT ON
EXEC master..xp_cmdshell @bcpCommand, NO_OUTPUT
EXEC master..xp_cmdshell 'cmd /C "C:\sqlexporttemp\GoldenJackpotProg\GJRPRogXsfer.bat"', NO_OUTPUT
SET NOCOUNT OFF
END
GO
Disclaimer:
Golden Casino Group properties ©Golden Gaming Inc. Affiliate and partner materials are property of the respective enterprise.
- Development/Database
- Example Links
- SQL Query & Stored Procedure Development
- Dynamic Data Signage Applications
- Happy Game Family - Static Site
- Keep of Imbarkus - Dynamic Site
- Old Web Site Development
