311 lines
8.6 KiB
SQL
311 lines
8.6 KiB
SQL
CREATE TABLE `FinancialAccountAutomationProfiles` (
|
|
`FinancialAccountAutomationProfileId` INT NOT NULL AUTO_INCREMENT,
|
|
`Guid` VARCHAR(48) NOT NULL,
|
|
`Name` VARCHAR(128) NOT NULL,
|
|
`FinancialAccountAutomationProfileTypeId` INT NOT NULL,
|
|
`DataProviderTypeId` INT NOT NULL,
|
|
`ProvidedBy` VARCHAR(512) NOT NULL,
|
|
`DateTimeUpdatedUtc` DATETIME NOT NULL,
|
|
PRIMARY KEY (`FinancialAccountAutomationProfileId`),
|
|
INDEX `Index_DataProviderTypeId` (`DataProviderTypeId` ASC),
|
|
INDEX `Index_AutomationTypeId` (`FinancialAccountAutomationProfileTypeId` ASC),
|
|
INDEX `Index_Updated` (`DateTimeUpdatedUtc` ASC),
|
|
INDEX `Index_Name` (`Name` ASC))
|
|
|
|
#
|
|
|
|
|
|
CREATE TABLE `FinancialAccountAutomationTypes` (
|
|
`FinancialAccountAutomationTypeId` INT NOT NULL AUTO_INCREMENT,
|
|
`Name` VARCHAR(128) NOT NULL,
|
|
PRIMARY KEY (`FinancialAccountAutomationTypeId`),
|
|
INDEX `Index_Name` (`Name` ASC))
|
|
|
|
|
|
#
|
|
|
|
|
|
CREATE TABLE `DataProviderTypes` (
|
|
`DataProviderTypeId` INT NOT NULL AUTO_INCREMENT,
|
|
`Name` VARCHAR(128) NOT NULL,
|
|
PRIMARY KEY (`DataProviderTypeId`),
|
|
INDEX `Index_Name` (`Name` ASC))
|
|
|
|
|
|
#
|
|
|
|
|
|
CREATE TABLE `FinancialAccountAutomationProfileFieldSets` (
|
|
`FinancialAccountAutomationProfileFieldSetId` INT NOT NULL AUTO_INCREMENT,
|
|
`FinancialAccountAutomationProfileId` INT NOT NULL,
|
|
`Culture` VARCHAR(16) NOT NULL,
|
|
PRIMARY KEY (`FinancialAccountAutomationProfileFieldSetId`),
|
|
INDEX `Index_Profile` (`FinancialAccountAutomationProfileId` ASC),
|
|
INDEX `Index_Culture` (`Culture` ASC))
|
|
|
|
|
|
#
|
|
|
|
|
|
CREATE TABLE `FieldSetData` (
|
|
`FieldSetId` INT NOT NULL,
|
|
`FieldSetTypeId` INT NOT NULL,
|
|
`DataKeyId` INT NOT NULL,
|
|
`DataValue` VARCHAR(2048) NOT NULL,
|
|
`DateTimeUpdatedUtc` DATETIME NOT NULL,
|
|
PRIMARY KEY (`FieldSetId`, `DataKeyId`, `FieldSetTypeId`),
|
|
INDEX `Index_Data` (`DataValue` ASC))
|
|
|
|
|
|
#
|
|
|
|
|
|
CREATE TABLE `DataKeys` (
|
|
`DataKeyId` INT NOT NULL AUTO_INCREMENT,
|
|
`Name` VARCHAR(128) NOT NULL,
|
|
PRIMARY KEY (`DataKeyId`),
|
|
INDEX `Index_Name` (`Name` ASC))
|
|
|
|
|
|
#
|
|
|
|
|
|
CREATE TABLE `FieldSetTypes` (
|
|
`FieldSetTypeId` INT NOT NULL AUTO_INCREMENT,
|
|
`Name` VARCHAR(128) NOT NULL,
|
|
PRIMARY KEY (`FieldSetTypeId`),
|
|
INDEX `Index_Name` (`Name` ASC))
|
|
|
|
|
|
#
|
|
|
|
|
|
CREATE TABLE `FinancialAccountAutomationProfileCountries` (
|
|
`FinancialAccountAutomationProfileId` INT NOT NULL,
|
|
`CountryId` INT NOT NULL,
|
|
PRIMARY KEY (`FinancialAccountAutomationProfileId`, `CountryId`))
|
|
|
|
|
|
#
|
|
|
|
|
|
CREATE TABLE `FinancialAccountAutomationProfileHistory` (
|
|
`FinancialAccountAutomationProfileId` INT NOT NULL,
|
|
`Xml` TEXT NOT NULL,
|
|
`DateTimeUpdatedUtc` VARCHAR(45) NOT NULL,
|
|
INDEX `Index_Profile` (`FinancialAccountAutomationProfileId` ASC),
|
|
INDEX `Index_Updated` (`DateTimeUpdatedUtc` ASC))
|
|
|
|
|
|
#
|
|
|
|
|
|
CREATE TABLE `FinancialAccountAutomationProfileVotes` (
|
|
`FinancialAccountAutomationProfileId` INT NOT NULL,
|
|
`CountryId` INT NOT NULL,
|
|
`Culture` VARCHAR(16) NOT NULL,
|
|
`Yes` TINYINT NOT NULL,
|
|
`SwarmopsInstallationId` VARCHAR(40) NOT NULL,
|
|
`PersonId` VARCHAR(45) NOT NULL,
|
|
`DateTimeUtc` DATETIME NOT NULL,
|
|
PRIMARY KEY (`FinancialAccountAutomationProfileId`, `CountryId`, `Culture`, `PersonId`, `SwarmopsInstallationId`),
|
|
INDEX `Index_Yes` (`Yes` ASC))
|
|
|
|
|
|
#
|
|
|
|
|
|
CREATE PROCEDURE `CreateFinancialAccountAutomationProfile`(
|
|
IN guid VARCHAR(48),
|
|
IN name VARCHAR(128),
|
|
IN financialAccountAutomationTypeName VARCHAR(128),
|
|
IN dataProviderTypeName VARCHAR(128),
|
|
IN providedBy VARCHAR(1024),
|
|
IN dateTimeUpdatedUtc DATETIME
|
|
)
|
|
BEGIN
|
|
|
|
DECLARE automationTypeId INTEGER;
|
|
DECLARE providerTypeId INTEGER;
|
|
|
|
SELECT 0 INTO automationTypeId;
|
|
SELECT 0 INTO providerTypeId;
|
|
|
|
|
|
IF ((SELECT COUNT(*) FROM FinancialAccountAutomationTypes WHERE FinancialAccountAutomationTypes.Name=financialAccountAutomationTypeName) = 0)
|
|
THEN
|
|
INSERT INTO FinancialAccountAutomationTypes (Name)
|
|
VALUES (financialAccountAutomationTypeName);
|
|
|
|
SELECT LAST_INSERT_ID() INTO automationTypeId;
|
|
|
|
ELSE
|
|
|
|
SELECT FinancialAccountAutomationTypes.FinancialAccountAutomationTypeId INTO automationTypeId FROM FinancialAccountAutomationTypes
|
|
WHERE FinancialAccountAutomationTypes.Name=financialAccountAutomationTypeName;
|
|
|
|
END IF;
|
|
|
|
|
|
IF ((SELECT COUNT(*) FROM DataProviderTypes WHERE DataProviderTypes.Name=dataProviderTypeName) = 0)
|
|
THEN
|
|
INSERT INTO DataProviderTypes (Name)
|
|
VALUES (dataProviderTypeName);
|
|
|
|
SELECT LAST_INSERT_ID() INTO providerTypeId;
|
|
|
|
ELSE
|
|
|
|
SELECT DataProviderTypes.DataProviderTypeId INTO providerTypeId FROM DataProviderTypes
|
|
WHERE DataProviderTypes.Name=dataProviderTypeName;
|
|
|
|
END IF;
|
|
|
|
|
|
INSERT INTO FinancialAccountAutomationProfiles (Guid, Name, FinancialAccountAutomationProfileTypeId, DataProviderTypeId, ProvidedBy, DateTimeUpdatedUtc)
|
|
VALUES (guid,name,automationTypeId,providerTypeId,providedBy,dateTimeUtc);
|
|
|
|
SELECT LAST_INSERT_ID() AS Identity;
|
|
|
|
END
|
|
|
|
|
|
#
|
|
|
|
|
|
CREATE PROCEDURE `SetFinancialAccountAutomationProfileField`(
|
|
IN financialAccountAutomationProfileId INT,
|
|
IN culture VARCHAR(16),
|
|
IN dataKey VARCHAR(128),
|
|
IN dataValue VARCHAR(2048),
|
|
IN dateTimeUpdatedUtc DATETIME
|
|
)
|
|
BEGIN
|
|
|
|
DECLARE fieldSetId INTEGER;
|
|
DECLARE dataKeyId INTEGER;
|
|
DECLARE fieldSetTypeId INTEGER;
|
|
|
|
SELECT 0 INTO fieldSetId;
|
|
SELECT 0 INTO fieldSetTypeId;
|
|
SELECT 0 INTO dataKeyId;
|
|
|
|
|
|
IF ((SELECT COUNT(*) FROM FieldSetTypes WHERE FieldSetTypes.Name="FinancialAccountAutomationProfile") = 0)
|
|
THEN
|
|
|
|
INSERT INTO FieldSetTypes (Name) VALUES ("FinancialAccountAutomationProfile");
|
|
SELECT LAST_INSERT_ID() INTO fieldSetTypeId;
|
|
|
|
ELSE
|
|
|
|
SELECT FieldSetTypes.FieldSetTypeId INTO fieldSetTypeId FROM FieldSetTypes
|
|
WHERE FieldSetTypes.Name="FinancialAccountAutomationProfile";
|
|
|
|
END IF;
|
|
|
|
|
|
IF ((SELECT COUNT(*) FROM FinancialAccountAutomationProfileFieldSets WHERE
|
|
FinancialAccountAutomationProfileFieldSets.FinancialAccountAutomationProfileId=financialAccountAutomationProfileId AND
|
|
FinancialAccountAutomationProfileFieldSets.Culture = culture) = 0)
|
|
THEN
|
|
|
|
INSERT INTO FinancialAccountAutomationProfileFieldSets (FinancialAccountAutomationProfileId,Culture)
|
|
VALUES (financialAccountAutomationProfileId,culture);
|
|
|
|
SELECT LAST_INSERT_ID() INTO fieldSetId;
|
|
|
|
ELSE
|
|
|
|
SELECT FinancialAccountAutomationProfileFieldSets.FinancialAccountAutomationProfileFieldSetId INTO fieldSetId FROM FinancialAccountAutomationProfileFieldSets
|
|
WHERE FinancialAccountAutomationProfileFieldSets.FinancialAccountAutomationProfileId=financialAccountAutomationProfileId AND
|
|
FinancialAccountAutomationProfileFieldSets.Culture=culture;
|
|
|
|
END IF;
|
|
|
|
|
|
IF ((SELECT COUNT(*) FROM DataKeys WHERE DataKeys.Name=dataKey) = 0)
|
|
THEN
|
|
|
|
INSERT INTO DataKeys (Name) VALUES (dataKey);
|
|
SELECT LAST_INSERT_ID() INTO dataKeyId;
|
|
|
|
ELSE
|
|
|
|
SELECT DataKeys.DataKeyId INTO dataKeyId FROM DataKeys
|
|
WHERE DataKeys.Name = dataKey;
|
|
|
|
END IF;
|
|
|
|
|
|
IF ((SELECT COUNT(*) FROM FieldSetData
|
|
WHERE FieldSetData.DataKeyId = dataKeyId
|
|
AND FieldSetData.FieldSetId = fieldSetId
|
|
AND FieldSetData.FieldSetTypeId = fieldSetTypeId) = 0)
|
|
THEN
|
|
|
|
INSERT INTO FieldSetData (FieldSetId,FieldSetTypeId,DataKeyId,DataValue,DateTimeUpdatedUtc)
|
|
VALUES (fieldSetId,fieldSetTypeId,dataKeyId,dataValue,dateTimeUpdatedUtc);
|
|
|
|
ELSE
|
|
|
|
UPDATE FieldSetData SET FieldSetData.DataValue=dataValue,FieldSetData.UpdatedDateTimeUtc=updatedDateTimeUtc
|
|
WHERE FieldSetData.FieldSetId=fieldSetId
|
|
AND FieldSetData.FieldSetTypeId=fieldSetTypeId
|
|
AND FieldSetData.DataKeyId=dataKeyId;
|
|
|
|
END IF;
|
|
|
|
END
|
|
|
|
|
|
#
|
|
|
|
|
|
|
|
CREATE PROCEDURE `SetFinancialAccountAutomationProfileCountryEnabled`(
|
|
IN financialAccountAutomationProfileId INT,
|
|
IN countryId INT,
|
|
IN enabled INT)
|
|
BEGIN
|
|
|
|
IF (enabled=0)
|
|
THEN
|
|
|
|
DELETE FROM FinancialAccountAutomationProfileCountries
|
|
WHERE FinancialAccountAutomationProfileCountries.FinancialAccountAutomationProfileId=financialAccountAutomationProfileId
|
|
AND FinancialAccountAutomationProfileCountries.CountryId=countryId;
|
|
|
|
ELSE
|
|
|
|
IF ((SELECT COUNT(*) FROM FinancialAccountAutomationProfileCountries
|
|
WHERE FinancialAccountAutomationProfileCountries.CountryId=countryId
|
|
AND FinancialAccountAutomationProfileCountries.FinancialAccountAutomationProfileId=financialAccountAutomationProfileId) = 0)
|
|
THEN
|
|
|
|
INSERT INTO FinancialAccountAutomationProfileCountries(FinancialAccountAutomationProfileId,CountryId)
|
|
VALUES (financialAccountAutomationProfileId,countryId);
|
|
|
|
END IF;
|
|
|
|
END IF;
|
|
|
|
END
|
|
|
|
|
|
#
|
|
|
|
|
|
CREATE PROCEDURE `SetFinancialAccountAutomationProfileHistoryEntry`(
|
|
IN financialAccountAutomationProfileId INT,
|
|
IN xml TEXT,
|
|
IN dateTimeUpdatedUtc DATETIME)
|
|
BEGIN
|
|
|
|
INSERT INTO FinancialAccountAutomationProfileHistory (FinancialAccountAuomationProfileId, Xml, DateTimeUpdatedUtc)
|
|
VALUES (financialAccountAutomationProfileId, xml, dateTimeUpdatedUtc);
|
|
|
|
END
|
|
|
|
|