Files

176 lines
5.5 KiB
SQL

CREATE TABLE `FinancialAccountDocuments` (
`FinancialAccountDocumentId` INT NOT NULL AUTO_INCREMENT,
`FinancialAccountId` INT NOT NULL,
`FinancialAccountDocumentTypeId` INT NOT NULL,
`UploadedDateTime` DATETIME NOT NULL,
`UploadedByPersonId` INT NOT NULL,
`ConcernsPeriodStart` DATETIME NOT NULL,
`ConcernsPeriodEnd` DATETIME NOT NULL,
`RawDocumentText` TEXT NOT NULL,
PRIMARY KEY (`FinancialAccountDocumentId`),
INDEX `ixAccountId` (`FinancialAccountId` ASC),
INDEX `ixDocType` (`FinancialAccountDocumentTypeId` ASC),
INDEX `ixUploadedTime` (`UploadedDateTime` ASC),
INDEX `ixUploadedPerson` (`UploadedByPersonId` ASC),
INDEX `ixPeriod` (`ConcernsPeriodStart` ASC))
#
CREATE TABLE `FinancialAccountDocumentTypes` (
`FinancialAccountDocumentTypeId` INT NOT NULL AUTO_INCREMENT,
`Name` VARCHAR(128) NOT NULL,
PRIMARY KEY (`FinancialAccountDocumentTypeId`),
INDEX `ixName` (`Name` ASC))
#
CREATE TABLE `FinancialTransactionRowImportHashes` (
`FinancialTransactionRowImportHashId` INT NOT NULL AUTO_INCREMENT,
`FinancialTransactionRowId` INT NOT NULL,
`FinancialAccountDocumentId` INT NOT NULL,
`Sha256` VARCHAR(128) NOT NULL DEFAULT '',
`ProvidedUniqueId` VARCHAR(512) NOT NULL DEFAULT '',
PRIMARY KEY (`FinancialTransactionRowImportHashId`),
INDEX `ixTransactionRow` (`FinancialTransactionRowId` ASC),
INDEX `ixDocument` (`FinancialAccountDocumentId` ASC),
INDEX `ixSha256` (`Sha256` ASC),
INDEX `ixUniqueId` (`ProvidedUniqueId` ASC))
#
DROP PROCEDURE IF EXISTS `CreateFinancialAccountDocument`
#
DROP PROCEDURE IF EXISTS `SetFinancialTransactionRowProvidedUniqueId`
#
DROP PROCEDURE IF EXISTS `SetFinancialTransactionRowImportSha256`
#
CREATE PROCEDURE `CreateFinancialAccountDocument`(
IN financialAccountId INT,
IN financialAccountDocumentType VARCHAR(128),
IN uploadedDateTime DATETIME,
IN uploadedByPersonId INT,
IN concernsPeriodStart DATETIME,
IN concernsPeriodEnd DATETIME,
IN rawDocumentText TEXT
)
BEGIN
DECLARE documentTypeId INT;
IF ((SELECT COUNT(*) FROM FinancialAccountDocumentTypes WHERE FinancialAccountDocumentTypes.Name=financialAccountDocumentType) = 0)
THEN
INSERT INTO FinancialAccountDocumentTypes(Name)
VALUES (financialAccountDocumentType);
SELECT LAST_INSERT_ID() INTO documentTypeId;
ELSE
SELECT FinancialAccountDocumentTypes.FinancialAccountDocumentTypeId
INTO documentTypeId
FROM FinancialAccountDocumentTypes
WHERE FinancialAccountDocumentTypes.Name=financialAccountDocumentType;
END IF;
INSERT INTO FinancialAccountDocuments (FinancialAccountId,FinancialAccountDocumentTypeId,UploadedDateTime,UploadedByPersonId,ConcernsPeriodStart,ConcernsPeriodEnd,RawDocumentText)
VALUES (financialAccountId,documentTypeId,uploadedDateTime,uploadedByPersonId,concernsPeriodStart,concernsPeriodEnd,rawDocumentText);
SELECT LAST_INSERT_ID() AS Identity;
END
#
CREATE PROCEDURE `SetFinancialTransactionRowProvidedUniqueId`(
IN financialTransactionRowId INT,
IN financialAccountDocumentId INT,
IN providedUniqueId VARCHAR(512)
)
BEGIN
DECLARE recordId INT;
IF ((SELECT COUNT(*) FROM FinancialTransactionRowImportHashes
WHERE FinancialTransactionRowImportHashes.FinancialTransactionRowId=financialTransactionRowId
AND FinancialTransactionRowImportHashes.FinancialAccountDocumentId=financialAccountDocumentId) = 0)
THEN
SELECT FinancialTransactionRowImportHashes.FinancialTransactionRowImportHashId
INTO recordId
FROM FinancialTransactionRowImportHashes
WHERE FinancialTransactionRowImportHashes.FinancialTransactionRowId=financialTransactionRowId
AND FinancialTransactionRowImportHashes.FinancialAccountDocumentId=financialAccountDocumentId;
UPDATE FinancialTransactionRowImportHashes
SET FinancialTransactionRowImportHashes.ProvidedUniqueId=providedUniqueId
WHERE FinancialTransactionRowImportHashes.FinancialTransactionRowImportHashId=recordId;
ELSE
INSERT INTO FinancialTransactionRowImportHashes (FinancialTransactionRowId,FinancialAccountDocumentId,ProvidedUniqueId)
VALUES (financialTransactionRowId,financialAccountDocumentId,providedUniqueId);
SELECT LAST_INSERT_ID() INTO recordId;
END IF;
SELECT recordId AS Identity;
END
#
CREATE PROCEDURE `SetFinancialTransactionRowImportSha256`(
IN financialTransactionRowId INT,
IN financialAccountDocumentId INT,
IN sha256 VARCHAR(128)
)
BEGIN
DECLARE recordId INT;
IF ((SELECT COUNT(*) FROM FinancialTransactionRowImportHashes
WHERE FinancialTransactionRowImportHashes.FinancialTransactionRowId=financialTransactionRowId
AND FinancialTransactionRowImportHashes.FinancialAccountDocumentId=financialAccountDocumentId) = 0)
THEN
SELECT FinancialTransactionRowImportHashes.FinancialTransactionRowImportHashId
INTO recordId
FROM FinancialTransactionRowImportHashes
WHERE FinancialTransactionRowImportHashes.FinancialTransactionId=financialTransactionId
AND FinancialTransactionRowImportHashes.FinancialAccountDocumentId=financialAccountDocumentId;
UPDATE FinancialTransactionRowImportHashes
SET FinancialTransactionRowImportHashes.Sha256=sha256
WHERE FinancialTransactionRowImportHashes.FinancialTransactionImportHashId=recordId;
ELSE
INSERT INTO FinancialTransactionRowImportHashes (FinancialTransactionId,FinancialAccountDocumentId,Sha256)
VALUES (financialTransactionId,financialAccountDocumentId,sha256);
SELECT LAST_INSERT_ID() INTO recordId;
END IF;
SELECT recordId AS Identity;
END