Onboarding | Technical implementation
      Back to home
      1. INTERNAL - Bepoz Help Guides
      2. MyPlace
      3. Onboarding | Technical implementation

      MyPlace : Create SQL Stored Procedures Scripts

      /* ProductsUpdated */

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[ProductsUpdated]
    @date nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    select *
    from Product p
    where p.dateupdated >= @date

END

 
/* LastTransactionAccount */

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[LastTransactionAccount]
@date DATETIME2(7)
AS
BEGIN
SET NOCOUNT ON;

SELECT Transactions.AccountID as accountid
FROM TransLines JOIN Transactions ON Transactions.TransactionID = TransLines.TransactionID
AND DateTimeAdded >= @date
GROUP BY Transactions.AccountID

END


 
/* PrizePromoUpdated */

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[PrizePromoUpdated]

AS
BEGIN
SET NOCOUNT ON;
    
SELECT *
FROM PrizePromo
WHERE InActive=0

END

 
/* AccPromoUpdated */

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[AccPromoUpdated]
@date nvarchar(20)
AS
BEGIN
SET NOCOUNT ON;
    
SELECT *
FROM AccPromo

END

 
/* VoucherUpdatedMembers */

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[VoucherUpdatedMembers]
    @date nvarchar(20),
    @memberids nvarchar(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    EXEC('SELECT * FROM Voucher v WHERE v.dateupdated >= ''' + @date + ''' AND v.AccountID IN (' + @memberids + ')')

END
 
/* VoucherUpdated */

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[VoucherUpdated]
    @date nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    select *
    from Voucher v
    where v.dateupdated >= @date

END


 
/* VoucherUpdatedTotal */

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[VoucherUpdatedTotal]
    @date nvarchar(20)
AS
BEGIN
SET NOCOUNT ON;

DECLARE @total INT, @maxIssuedDate nvarchar(20)
select @total = count([VoucherID])
from Voucher v
where v.[IssuedDate] >= @date

select @maxIssuedDate = CONVERT(varchar, MAX(v.[IssuedDate]), 126)
from Voucher v
where v.[IssuedDate] >= @date

SELECT @total as total, @maxIssuedDate as maxIssuedDate

END 
/* VoucherUpdatedIndividual */

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[VoucherUpdatedIndividual]
    @memberid int
AS
BEGIN
SET NOCOUNT ON;

SELECT * FROM Voucher v WHERE v.AccountID = @memberid

END

 
/* VoucherUpdatedPaginate */

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[VoucherUpdatedPaginate]
@date nvarchar(20),
@pagenumber int,
@rowsofpage int
AS
BEGIN
SET NOCOUNT ON;

select [VoucherID],[DateUpdated],[Lookup],[PrizePromoID],[VoucherSetupID]
      ,[AccountID],[IssuedDate],[DateExpiry]
from Voucher v
where v.[IssuedDate] >= @date
ORDER BY VoucherID
OFFSET (@pagenumber - 1) * @rowsofpage ROWS     
FETCH NEXT @rowsofpage ROWS ONLY;

END

 
/* AccountsUpdated */

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[AccountsUpdated]
    @date nvarchar(20)
AS
BEGIN
    SET NOCOUNT ON;

    select a.dateupdated as accountupdated, ad.dateupdated as addressupdated, a.CountVisits AS countvisits, a.firstname, a.lastname, a.parentid as groupid, a.accountid, a.accnumber, ad.Email1st AS email, a.cardnumber, a.pointsearned - a.PointsRedeemed - a.PointsExpired AS points, a.AccountBalance as balance, ad.mobile, ad.phonehome, a.datetimeexpiry, a.datebirth, a.[status], a.usecalinkbal, a.usecalinkpnts 
from account a left join address ad on a.addressid = ad.addressid
    where a.dateupdated >= @date OR ad.DateUpdated >= @date

END

 
/* AccountsUpdatedMembers */

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[AccountsUpdatedMembers]
    @date nvarchar(20),
    @memberids nvarchar(MAX)
AS
BEGIN
    SET NOCOUNT ON;

    EXEC('select a.dateupdated as accountupdated, ad.dateupdated as addressupdated, a.CountVisits AS countvisits, a.firstname, a.lastname, a.parentid as groupid, a.accountid, a.accnumber, ad.Email1st AS email, a.cardnumber, a.pointsearned - a.PointsRedeemed - a.PointsExpired AS points, a.AccountBalance as balance, ad.mobile, ad.phonehome, a.datetimeexpiry, a.datebirth, a.[status], a.usecalinkbal, a.usecalinkpnts 
        from account a left join address ad on a.addressid = ad.addressid
        where (a.dateupdated >= ''' + @date + ''' OR ad.DateUpdated >= ''' + @date + ''')
        AND a.accountid IN (' + @memberids + ')')

END

 
/* AccountsUpdatedIndividual */

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[AccountsUpdatedIndividual]
    @memberid int
AS
BEGIN
SET NOCOUNT ON;

select a.dateupdated as accountupdated, ad.dateupdated as addressupdated, a.CountVisits AS countvisits, a.firstname, a.lastname, a.parentid as groupid, a.accountid, a.accnumber, ad.Email1st AS email, a.cardnumber, a.pointsearned - a.PointsRedeemed - a.PointsExpired AS points, a.AccountBalance as balance, ad.mobile, ad.phonehome, a.datetimeexpiry, a.datebirth, a.[status], a.usecalinkbal, a.usecalinkpnts 
    from account a left join address ad on a.addressid = ad.addressid
    where a.accountid = @memberid

END

 
/* AccPromoUpdatedIndividual */

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[AccPromoUpdatedIndividual]
    @memberid int
AS
BEGIN
SET NOCOUNT ON;

SELECT * FROM AccPromo a WHERE a.AccountID = @memberid

END



 
/* GamingAccountLowest */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GamingAccountLowest]
    @groupid nvarchar(20),
    @customflag nvarchar(50)
    
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @selectedColumn nvarchar(50);
        
    SELECT
      @selectedColumn = CASE @customflag
        WHEN 'CustomFlag_1'  THEN 'CustomFlag_1'
        WHEN 'CustomFlag_2'  THEN 'CustomFlag_2'
        WHEN 'CustomFlag_3'  THEN 'CustomFlag_3'
        WHEN 'CustomFlag_4'  THEN 'CustomFlag_4'
        WHEN 'CustomFlag_5'  THEN 'CustomFlag_5'
        WHEN 'CustomFlag_6'  THEN 'CustomFlag_6'
        WHEN 'CustomFlag_7'  THEN 'CustomFlag_7'
        WHEN 'CustomFlag_8'  THEN 'CustomFlag_8'
        WHEN 'CustomFlag_9'  THEN 'CustomFlag_9'
        WHEN 'CustomFlag_10' THEN 'CustomFlag_10'
        WHEN '1'  THEN 'CustomFlag_1'
        WHEN '2'  THEN 'CustomFlag_2'
        WHEN '3'  THEN 'CustomFlag_3'
        WHEN '4'  THEN 'CustomFlag_4'
        WHEN '5'  THEN 'CustomFlag_5'
        WHEN '6'  THEN 'CustomFlag_6'
        WHEN '7'  THEN 'CustomFlag_7'
        WHEN '8'  THEN 'CustomFlag_8'
        WHEN '9'  THEN 'CustomFlag_9'
        WHEN '10' THEN 'CustomFlag_10'
        ELSE NULL
      END
    FROM
      account

      
    EXEC('select a.dateupdated as accountupdated, ad.dateupdated as addressupdated, a.CountVisits AS countvisits, a.firstname, a.lastname, a.parentid as groupid, a.accountid, a.accnumber, ad.Email1st AS email, a.cardnumber, a.pointsearned - a.PointsRedeemed - a.PointsExpired AS points, a.AccountBalance as balance, ad.mobile, ad.phonehome, a.datetimeexpiry, a.datebirth, a.[status], a.usecalinkbal, a.usecalinkpnts,
        a.CustomFlag_1,a.CustomFlag_2,a.CustomFlag_3,a.CustomFlag_4,a.CustomFlag_5,a.CustomFlag_6,a.CustomFlag_7,a.CustomFlag_8,a.CustomFlag_9,a.CustomFlag_10
    from account a left join address ad on a.addressid = ad.addressid
    WHERE a.accountid = (
        select MIN(accountid)
        from account a
        where a.parentid = ''' + @groupid + '''
        AND a.' + @selectedColumn + '  = 1
    )')
    
END

 
/* GamingAccountCount */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GamingAccountCount]
    @groupid nvarchar(20),
    @customflag nvarchar(50)
    
AS
BEGIN
    SET NOCOUNT ON;
    
    DECLARE @selectedColumn nvarchar(50);
        
    SELECT
      @selectedColumn = CASE @customflag
        WHEN 'CustomFlag_1'  THEN 'CustomFlag_1'
        WHEN 'CustomFlag_2'  THEN 'CustomFlag_2'
        WHEN 'CustomFlag_3'  THEN 'CustomFlag_3'
        WHEN 'CustomFlag_4'  THEN 'CustomFlag_4'
        WHEN 'CustomFlag_5'  THEN 'CustomFlag_5'
        WHEN 'CustomFlag_6'  THEN 'CustomFlag_6'
        WHEN 'CustomFlag_7'  THEN 'CustomFlag_7'
        WHEN 'CustomFlag_8'  THEN 'CustomFlag_8'
        WHEN 'CustomFlag_9'  THEN 'CustomFlag_9'
        WHEN 'CustomFlag_10' THEN 'CustomFlag_10'
        WHEN '1'  THEN 'CustomFlag_1'
        WHEN '2'  THEN 'CustomFlag_2'
        WHEN '3'  THEN 'CustomFlag_3'
        WHEN '4'  THEN 'CustomFlag_4'
        WHEN '5'  THEN 'CustomFlag_5'
        WHEN '6'  THEN 'CustomFlag_6'
        WHEN '7'  THEN 'CustomFlag_7'
        WHEN '8'  THEN 'CustomFlag_8'
        WHEN '9'  THEN 'CustomFlag_9'
        WHEN '10' THEN 'CustomFlag_10'
        ELSE NULL
      END
    FROM
      account

      
    EXEC('select count(a.accountid)
    from account a
    WHERE  a.parentid = ''' + @groupid + '''
        AND a.' + @selectedColumn + '  = 1
    ')
    
END



/* GetDiscountAccount */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetDiscountAccount]
@accountID int
AS
BEGIN
SET NOCOUNT ON;

declare @discount int, @voucherDiscount int, @totalSaved int;

SELECT @discount = -SUM(DiscAmt_1 + DiscAmt_2 + DiscAmt_3)
FROM TransLines JOIN Transactions ON Transactions.TransactionID = TransLines.TransactionID
WHERE Transactions.AccountID = @accountID
AND Transactions.TransType NOT IN (-1, 0 , 257, 293, 324, 325, 533, 534, 32770, 32771, 32772, 32773, 32774, 16404, 16402, 16403, 16418, 16419, 16420, 16443, 16444, 16445, 16446, 16417, 16447, 16448, 16449)
GROUP BY Transactions.AccountID

SELECT @voucherDiscount = -SUM(DiscPercAmt + DiscAmtAmt)
FROM Transactions
WHERE AccountID = @accountID
AND TransType NOT IN (-1, 0 , 257, 293, 324, 325, 533, 534, 32770, 32771, 32772, 32773, 32774, 16404, 16402, 16403, 16418, 16419, 16420, 16443, 16444, 16445, 16446, 16417, 16447, 16448, 16449)
GROUP BY AccountID

SET @totalSaved = @discount + @voucherDiscount;

SELECT @discount AS 'discount', @voucherDiscount AS 'voucherDiscount', @totalSaved AS 'TotalSaved';

END


 

/* OperatorList */

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[OperatorList]

AS
BEGIN
SET NOCOUNT ON;

SELECT [OperatorID]
      ,[DateUpdated]
      ,[ParentID]
      ,[FirstName]
      ,[LastName] 
        FROM Operator

END



 

/* WorkstationList – Till */ 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[WorkstationList]

AS
BEGIN
SET NOCOUNT ON;

SELECT [WorkstationID]
      ,[DateUpdated]
      ,[Name]
      ,[StoreID]
      ,[TillMode]
        FROM Workstation

END


/* Receive Image */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[ReceiveImage]
@accountID INT,
   @dataImage VARCHAR(max)
AS
BEGIN
   SET NOCOUNT ON;
       
IF @accountID <= 0
BEGIN
SELECT '0' AS AccountID, '0' AS ImageID, 0 AS [Status], 'No AccountID' AS [Message];
RETURN
END

IF (SELECT COUNT(AccountID) FROM Account WHERE AccountID = @accountID) <= 0
BEGIN
SELECT '0' AS AccountID, '0' AS ImageID, 0 AS [Status], 'Account Not Found' AS [Message];
RETURN
END

BEGIN TRY
INSERT INTO ImageData(ImageType, NoDelete, Name, ImageSize, Hash1Code, Hash2Code, ImageFormat, ImageData )    
VALUES(4,0, 'profile', 0,0,0,0,
cast(N'' as xml).value('xs:base64Binary(sql:variable("@dataImage"))', 'varbinary(max)')
);
END TRY
BEGIN CATCH  
SELECT @accountID AS AccountID, '0' AS ImageID, 0 AS [Status], ERROR_MESSAGE() AS [Message];
RETURN
END CATCH

BEGIN TRY
UPDATE Account SET ImageID = SCOPE_IDENTITY() WHERE AccountID = @accountID;
END TRY
BEGIN CATCH  
SELECT @accountID AS AccountID, SCOPE_IDENTITY() AS ImageID, 0 AS [Status], ERROR_MESSAGE() AS [Message];
RETURN
END CATCH

   SELECT @accountID AS AccountID, SCOPE_IDENTITY() AS ImageID, 1 AS [Status], 'OK' AS [Message];
END

/* Accounts Updated Total */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[AccountsUpdatedTotal]
	@date nvarchar(20)
AS
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @total INT

	select @total = count(a.AccountID)
	from account a left join address ad on a.addressid = ad.addressid
	where a.dateupdated >= @date OR ad.DateUpdated >= @date

	SELECT @total as total
END



/* Accounts Updated Paginate */
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[AccountsUpdatedPaginate]
	@date nvarchar(20),
	@pagenumber int,
	@rowsofpage int
AS
BEGIN
	SET NOCOUNT ON;
	
	select a.dateupdated as accountupdated, ad.dateupdated as addressupdated, a.CountVisits AS countvisits, a.firstname, a.lastname, a.parentid as groupid, a.accountid, a.accnumber, ad.Email1st AS email, a.cardnumber, a.pointsearned - a.PointsRedeemed - a.PointsExpired AS points, a.AccountBalance as balance, ad.mobile, ad.phonehome, a.datetimeexpiry, a.datebirth, a.[status], a.usecalinkbal, a.usecalinkpnts 
	from account a left join address ad on a.addressid = ad.addressid
	where a.dateupdated >= @date OR ad.DateUpdated >= @date
	ORDER BY a.dateupdated
	OFFSET (@pagenumber - 1) * @rowsofpage ROWS	
	FETCH NEXT @rowsofpage ROWS ONLY;
	
END



/* StoreList */

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[StoreList]

AS
BEGIN
SET NOCOUNT ON;
create table #storegrps (storegid int, storegroup varchar(50))
declare @loop int,
        @sql nvarchar(max)

set @loop =    0
set @sql = ''

while @loop < 50
begin

set @sql = N'
select
'+ convert(varchar,@loop) +' as storegid,
global.storegroup'+ convert(varchar,@loop) +' as storegroup
from global
'
insert into #storegrps (storegid, storegroup)
exec sp_sqlexec @sql
set @loop = @loop + 1
end

select
storeid,
name,
venueid,
#storegrps.storegroup,
dateupdated
from store left join #storegrps on #storegrps.storegid = Store.StoreGroup

drop table #storegrps
END

/* VoucherIndividualOptimize */

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[VoucherIndividualOptimize]
	@memberid int,
	@status nvarchar(20) = NULL,
	@rangeStart nvarchar(20) = NULL,
	@rangeEnd nvarchar(20) = NULL
AS
BEGIN
SET NOCOUNT ON;

if (@status IS NULL OR @status = 'active')
    BEGIN
		SELECT v.VoucherID,v.Lookup,v.PrizePromoID,v.VoucherSetupID,v.AccountID
			,v.StopRedeems,v.VoucherType,v.VoucherApply,v.UnlimitedUse,v.AmountIssued
			,v.MaximumDiscount,v.ClaimVenueID,v.ClaimStoreID,v.AmountLeft,v.UsedCount
			,v.UsedValue,v.IssuedDate,v.DateExpiry,v.UsedDate,v.UsedTransID
			,vs.Inactive
		FROM Voucher v JOIN VoucherSetup vs ON v.VoucherSetupID =  vs.VoucherSetupID
		WHERE v.AccountID = @memberid
		AND v.StopRedeems = 0
		AND v.AmountLeft > 0
		AND (v.DateExpiry IS NULL OR convert(date, v.DateExpiry) = '0001-01-01' OR v.DateExpiry > GETDATE())
		AND vs.Inactive = 0
		ORDER BY IssuedDate DESC
    END
END
    
/* GetVoucher */
    
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
  CREATE PROCEDURE [dbo].[GetVouchers]
  @status nvarchar(20) = NULL,
  @rowsofpage int = 100,
  @lastVoucherId int = NULL,
  @issuedDate nvarchar(20) = NULL
AS
BEGIN
SET
  NOCOUNT ON;

SELECT
  v.VoucherID, v.Lookup, v.PrizePromoID, v.VoucherSetupID, v.AccountID,
  v.StopRedeems, v.VoucherType, v.VoucherApply, v.UnlimitedUse, v.AmountIssued,
  v.MaximumDiscount, v.ClaimVenueID, v.ClaimStoreID, v.AmountLeft, v.UsedCount,
  v.UsedValue, v.IssuedDate, v.DateExpiry, v.UsedDate, v.UsedTransID, vs.Inactive
FROM
  Voucher v JOIN VoucherSetup vs ON v.VoucherSetupID = vs.VoucherSetupID
WHERE
  ( @lastVoucherId IS NULL OR v.VoucherID > @lastVoucherId )
  AND ( @issuedDate IS NULL OR v.issuedDate >= @issuedDate )
  AND (
    (@status IS NULL)
    OR (
      @status = 'active' AND vs.Inactive = 0 AND v.StopRedeems = 0 AND v.AmountLeft > 0
	  AND (
		v.DateExpiry IS NULL 
		OR convert(date, v.DateExpiry) = '0001-01-01' 
		OR v.DateExpiry > GETDATE()
	  )
    )
    OR ( @status = 'inactive' AND vs.Inactive = 1 )
  )
ORDER BY v.VoucherID ASC 
OFFSET 0 ROWS 
FETCH NEXT @rowsofpage + 1 ROWS ONLY
END