Player Elite Interface | Rest API
      Back to home
      1. INTERNAL - Bepoz Help Guides
      2. Installation / System Setup Guides
      3. Player Elite Interface | Rest API

      Player Elite - SQL Stored Procedure Creation

      Overview

      This article provides an overview of how to create a stored procedure which will send Voucher redemption information to Player Elite.

      Prerequisites

      • The operator should be able to access SQL Server Management Studio and have permission to create and execute the query

      About Player Elite

      • Player Elite is a KIOSK-providing company that uses the Bepoz XML API to retrieve information from Bepoz database

      Creating the Stored Procedure

      1. After logging into SQL Server Management Studio, select the appropriate Database from the dropdown 

      2. Click on the 'New Query' button from the toolbar

      3. Enter the Stored Procedure script as given below

      4. Click on the 'Execute' button which creates a stored procedure for Player Elite

      5. Once the stored procedure is created, it returns the redeemed voucher information to Player Elite

      SQL Script:

      SET ANSI_NULLS ON

      GO

      SET QUOTED_IDENTIFIER ON

      GO

      -- =============================================

      -- Player Elite Redeemed Vouchers Report

      -- =============================================

      CREATE PROCEDURE [dbo].[spPlayerElite]

      AS

      select

      voucher.VoucherType,

      case when voucher.AccountID != 0

           then account.AccNumber

           else '0'

      end 'Member Number',

      TransactionID,

      VoucherSetup.Name 'Voucher',

      RefID 'VoucherID',

      voucher.Lookup 'VoucherLookup',

      case voucher.VoucherType

          when 1 then convert(varchar,voucher.AmountIssued / 100) + ' ' + 'Voucher(s)'

          when 2 then convert(varchar,voucher.AmountIssued / 100) + ' ' + 'Voucher(s)'

          when 3 then convert(varchar,voucher.AmountIssued / 100) + ' ' + 'Voucher(s)'

          when 6 then convert(varchar,voucher.AmountIssued / 100) + '%'  + ' ' + '+' + ' ' + 'Max Discount:' + ' ' + convert(varchar,cast(voucher.MaximumDiscount as money) / 100)

          when 8 then convert(varchar,voucher.AmountIssued / 100) + '%'

          else convert(varchar,cast(voucher.amountissued as money)/100)

      end 'AmountIssued',

      convert(money,Amount) /100 'Amount Redeemed in Sale',

      case voucher.VoucherType

          when 1 then convert(varchar,voucher.AmountLeft / 100) + ' ' + 'Voucher(s)'

          when 2 then convert(varchar,voucher.AmountLeft / 100) + ' ' + 'Voucher(s)'

          when 3 then convert(varchar,voucher.AmountLeft / 100) + ' ' + 'Voucher(s)'

          when 6 then convert(varchar,voucher.AmountLeft / 100) + ' ' + 'Voucher(s)'

          when 8 then convert(varchar,voucher.AmountLeft / 100) + ' ' + 'Voucher(s)'

          else convert(varchar,cast(voucher.AmountLeft as money)/100)

      end 'AmountLeftOverall',

      convert(money,voucher.UsedValue) /100 'TotalUsedValue'

      from TransPayments

      left join Voucher on Voucher.VoucherID = TransPayments.RefID

      left join VoucherSetup on voucher.VoucherSetupID = VoucherSetup.VoucherSetupID

      left join Account on account.AccountID = voucher.AccountID

      where PaymentType in (65537, 24) and TransactionID in 

      (select TransactionID from Transactions where (ShiftID between CONVERT(varchar,CAST(DATEDIFF(day, '2001-01-01', GETDATE()) AS varchar) - (select

      case when getdate() > DATEADD(day,DATEDIFF(day,'20010101',GETDATE()),'2001-01-01T05:00:00') AND getdate() <

           DATEADD(day,DATEDIFF(day,'20010101',GETDATE()),'2001-01-01T23:59:59')

           then 30

           else 1

           end)) + '0' 

      and CONVERT(varchar,CAST(DATEDIFF(day, '2001-01-01', GETDATE()) AS varchar) - 0) + '9' ))