Smartsite Documentatie > Features & Modules > Add-On Modules > Ogone Payment Module > Ogone database schema

Ogone database schema

Release 1.2 - ...

The Ogone database requires the schema elements shown in the diagram below. The full creation script is available at the end of the page.

Ogone database schema

PaymentOrderArch is an archive table, which is not currently in use.

/* --------------------------------------------------------------------------------------------- */
-- Smartsite iXperion Ogone script for SQL Server
-- Copyright 2009-2010 Seneca B.V.
/* --------------------------------------------------------------------------------------------- */
-- This script can be run repeatedly.

SET NOCOUNT ON;
SET ANSI_DEFAULTS ON;
SET CURSOR_CLOSE_ON_COMMIT OFF;
SET IMPLICIT_TRANSACTIONS OFF;

DECLARE @err_msg nvarchar(4000), @err_sev int;

/* --------------------------------------------------------------------------------------------- */
/* Prepare Options */

DECLARE @verbose INT;
DECLARE @insertContent INT;
DECLARE @instanceName VARCHAR(50);
DECLARE @caller VARCHAR(100);

SET @verbose = 1;
SET @insertContent = 1;
SET @instanceName = NULL;
SET @caller = '2_Ogone_SchemaObjects';

IF OBJECT_ID('tempdb..##smsTempSetupOptions') IS NOT NULL
SELECT @verbose=Verbose, @insertContent=InsertContent, @instanceName=InstanceName
FROM ##smsTempSetupOptions;

/* --------------------------------------------------------------------------------------------- */
IF (@verbose>0) PRINT '
Executing ' + @caller + '.sql.';
/* --------------------------------------------------------------------------------------------- */

BEGIN TRY
BEGIN TRANSACTION;

/* --------------------------------------------------------------------------------------------- */
/* Table with payment status codes used by Smartsite */

IF NOT EXISTS (SELECT 1 FROM sys.tables st WHERE st.name='PayOrderStatus')
BEGIN
IF (@verbose=1) PRINT 'Adding table PayOrderStatus.';
CREATE TABLE PayOrderStatus
(
Nr INT NOT NULL,
Name VARCHAR(128) NOT NULL,
SortIndex INT NOT NULL CONSTRAINT DF_PayOrderStatus_SortIndex DEFAULT(0),
CONSTRAINT PK_PayOrderStatus PRIMARY KEY CLUSTERED (Nr)
);

INSERT INTO PayOrderStatus(Nr, Name, SortIndex) VALUES (1,'Uncertain',1);
INSERT INTO PayOrderStatus(Nr, Name, SortIndex) VALUES (2,'Aborted',2);
INSERT INTO PayOrderStatus(Nr, Name, SortIndex) VALUES (3,'Payed',3);
INSERT INTO PayOrderStatus(Nr, Name, SortIndex) VALUES (4,'Unpayed',4);
INSERT INTO PayOrderStatus(Nr, Name, SortIndex) VALUES (5,'Refunded',5);
INSERT INTO PayOrderStatus(Nr, Name, SortIndex) VALUES (6,'Inconsistent',6);
END

/* --------------------------------------------------------------------------------------------- */
/* Table with Ogone status codes */

IF NOT EXISTS (SELECT 1 FROM sys.tables st WHERE st.name='PayOgoneStatus')
BEGIN
IF (@verbose=1) PRINT 'Adding table PayOgoneStatus.';
CREATE TABLE PayOgoneStatus
(
Nr INT IDENTITY(1,1) NOT NULL,
Name VARCHAR(128) NOT NULL,
Status INT NOT NULL,
PayOrderStatus INT NOT NULL,
CONSTRAINT PK_PayOgoneStatus PRIMARY KEY CLUSTERED (Nr),
CONSTRAINT FK_PayOgoneStatus_PayOrderStat FOREIGN KEY (PayOrderStatus) REFERENCES PayOrderStatus(Nr),
CONSTRAINT UN_PayOgoneStatus_Status UNIQUE (Status)
);

INSERT INTO PayOgoneStatus (Status, Name, PayOrderStatus) VALUES ( 0, 'Ongeldig of onvolledig', 4);
INSERT INTO PayOgoneStatus (Status, Name, PayOrderStatus) VALUES ( 1, 'Geannuleerd door de klant', 2);
INSERT INTO PayOgoneStatus (Status, Name, PayOrderStatus) VALUES ( 2, 'Autorisatie geweigerd', 4);
INSERT INTO PayOgoneStatus (Status, Name, PayOrderStatus) VALUES ( 4, 'Opgeslagen bestelling', 4);
INSERT INTO PayOgoneStatus (Status, Name, PayOrderStatus) VALUES (41, 'Waiting client payment', 4);
INSERT INTO PayOgoneStatus (Status, Name, PayOrderStatus) VALUES ( 5, 'Geautoriseerd', 3);
INSERT INTO PayOgoneStatus (Status, Name, PayOrderStatus) VALUES (51, 'Autorisatie pending', 1);
INSERT INTO PayOgoneStatus (Status, Name, PayOrderStatus) VALUES (52, 'Autorisatie onzeker', 1);
INSERT INTO PayOgoneStatus (Status, Name, PayOrderStatus) VALUES (55, 'Standby', 1);
INSERT INTO PayOgoneStatus (Status, Name, PayOrderStatus) VALUES (59, 'Manueel te bekomen autorisatie', 1);
INSERT INTO PayOgoneStatus (Status, Name, PayOrderStatus) VALUES ( 6, 'Geautoriseerd en geannuleerd', 4);
INSERT INTO PayOgoneStatus (Status, Name, PayOrderStatus) VALUES (61, 'Annul. Autor. Pending', 2);
INSERT INTO PayOgoneStatus (Status, Name, PayOrderStatus) VALUES (62, 'Annul. Autor. Onzeker', 1);
INSERT INTO PayOgoneStatus (Status, Name, PayOrderStatus) VALUES (63, 'Annul. Autor geweigerd', 4);
INSERT INTO PayOgoneStatus (Status, Name, PayOrderStatus) VALUES (64, 'Geautoriseerd en geannuleerd', 4);
INSERT INTO PayOgoneStatus (Status, Name, PayOrderStatus) VALUES ( 7, 'Betaling geannuleerd', 4);
INSERT INTO PayOgoneStatus (Status, Name, PayOrderStatus) VALUES (71, 'Annul.betaling pending', 4);
INSERT INTO PayOgoneStatus (Status, Name, PayOrderStatus) VALUES (72, 'Annul. betaling onzeker', 4);
INSERT INTO PayOgoneStatus (Status, Name, PayOrderStatus) VALUES (73, 'Annul betaling geweigerd', 4);
INSERT INTO PayOgoneStatus (Status, Name, PayOrderStatus) VALUES (74, 'Betaling geannuleerd', 4);
INSERT INTO PayOgoneStatus (Status, Name, PayOrderStatus) VALUES (75, 'Annul. betaling verwerkt door merch', 4);
INSERT INTO PayOgoneStatus (Status, Name, PayOrderStatus) VALUES ( 8, 'Terugbetaald', 5);
INSERT INTO PayOgoneStatus (Status, Name, PayOrderStatus) VALUES (81, 'Terugbetaling pending', 5);
INSERT INTO PayOgoneStatus (Status, Name, PayOrderStatus) VALUES (82, 'Terugbetaling onzeker', 5);
INSERT INTO PayOgoneStatus (Status, Name, PayOrderStatus) VALUES (83, 'Terugbetaling geweigerd', 3);
INSERT INTO PayOgoneStatus (Status, Name, PayOrderStatus) VALUES (84, 'Betaling geweigerd door de bank', 3);
INSERT INTO PayOgoneStatus (Status, Name, PayOrderStatus) VALUES (85, 'Terugbet. verwerkt door merchant', 5);
INSERT INTO PayOgoneStatus (Status, Name, PayOrderStatus) VALUES ( 9, 'Betaling aangevraagd', 3);
INSERT INTO PayOgoneStatus (Status, Name, PayOrderStatus) VALUES (91, 'Betaling pending', 3);
INSERT INTO PayOgoneStatus (Status, Name, PayOrderStatus) VALUES (92, 'Betaling onzeker', 1);
INSERT INTO PayOgoneStatus (Status, Name, PayOrderStatus) VALUES (93, 'Betaling geweigerd', 4);
INSERT INTO PayOgoneStatus (Status, Name, PayOrderStatus) VALUES (94, 'Terubetaling geweigerd door de bank', 3);
INSERT INTO PayOgoneStatus (Status, Name, PayOrderStatus) VALUES (95, 'Betaling verwerkt door merchant', 3);
INSERT INTO PayOgoneStatus (Status, Name, PayOrderStatus) VALUES (99, 'Wordt verwerkt', 3);
END

/* --------------------------------------------------------------------------------------------- */
/* Table that holds the payments */

IF NOT EXISTS (SELECT 1 FROM sys.tables st WHERE st.name='PaymentOrder')
BEGIN
IF (@verbose=1) PRINT 'Adding table PaymentOrder.';

CREATE TABLE PaymentOrder 
(
Nr INT IDENTITY(1,1) NOT NULL,
Checkout VARCHAR(200) NOT NULL,
OrderId VARCHAR(30) NOT NULL,
Serial INT,
Amount INT NOT NULL,
Currency VARCHAR(3) NOT NULL,
CustomerName VARCHAR(256),
CustomerEmail VARCHAR(50),
CustomerAddress VARCHAR(256),
CustomerZip VARCHAR(10),
CustomerCity VARCHAR(128),
CustomerCountry VARCHAR(128),
CustTelephone VARCHAR(32),
PayId VARCHAR(32),
PaymentMethod VARCHAR(32),
Brand VARCHAR(32),
Status INT,
OgoneStatus INT NULL,
PaymentResult VARCHAR(MAX),
PayDate DATETIME,
ModDate DATETIME,
ExtraData VARCHAR(200),
CONSTRAINT PK_PaymentOrder PRIMARY KEY CLUSTERED (Nr),
CONSTRAINT FK_PaymentOrder_Status FOREIGN KEY (Status) REFERENCES PayOrderStatus(Nr),
CONSTRAINT UN_PaymentOrder_OrderId_Serial UNIQUE (OrderId, Serial)
);
END

/* --------------------------------------------------------------------------------------------- */
/* Table that holds archived payments */

IF NOT EXISTS (SELECT 1 FROM sys.tables st WHERE st.name='PaymentOrderArch')
BEGIN
IF (@verbose=1) PRINT 'Adding table PaymentOrderArch.';

CREATE TABLE PaymentOrderArch
(
Nr INT IDENTITY(1,1) NOT NULL,
Checkout VARCHAR(200) NOT NULL,
OrderId VARCHAR(30) NOT NULL,
Serial INT,
Amount INT NOT NULL,
Currency VARCHAR(3) NOT NULL,
CustomerName VARCHAR(256),
CustomerEmail VARCHAR(50),
CustomerAddress VARCHAR(256),
CustomerZip VARCHAR(10),
CustomerCity VARCHAR(128),
CustomerCountry VARCHAR(128),
CustTelephone VARCHAR(32),
PayId VARCHAR(32),
PaymentMethod VARCHAR(32),
Brand VARCHAR(32),
Status INT,
OgoneStatus INT NULL,
PaymentResult VARCHAR(MAX),
PayDate DATETIME,
ModDate DATETIME,
ExtraData VARCHAR(200),
CONSTRAINT PK_PaymentOrderArch PRIMARY KEY CLUSTERED (Nr),
CONSTRAINT FK_PaymentOrderArch_Status FOREIGN KEY (Status) REFERENCES PayOrderStatus(Nr),
CONSTRAINT UN_PayOrderArch_OrderId_Serial UNIQUE (OrderId, Serial)
);
END

/* --------------------------------------------------------------------------------------------- */

COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
PRINT
'
Error in ' + @caller + '.sql. Actual Line ' + CAST (ERROR_LINE() AS VARCHAR(10)) + '.
';

SET @err_msg = ERROR_MESSAGE();
SET @err_sev = ERROR_SEVERITY();

RAISERROR(@err_msg, @err_sev, 1);
END CATCH

/* --------------------------------------------------------------------------------------------- */
/* End-of-File */
Features & Modules