76 lines
2.2 KiB
SQL
76 lines
2.2 KiB
SQL
ALTER TABLE `FinancialTransactions`
|
|
ADD COLUMN `ImportSha256` VARCHAR(80) NOT NULL DEFAULT '' AFTER `ImportHash`
|
|
|
|
#
|
|
|
|
DROP PROCEDURE IF EXISTS `CreateFinancialTransactionStub`
|
|
|
|
#
|
|
|
|
DROP PROCEDURE IF EXISTS `SetFinancialTransactionImportSha256`
|
|
|
|
#
|
|
|
|
CREATE PROCEDURE `CreateFinancialTransactionStub`(
|
|
IN organizationId INTEGER,
|
|
IN financialAccountId INTEGER,
|
|
IN amountCents BIGINT,
|
|
IN dateTime DATETIME,
|
|
IN comment TEXT,
|
|
IN importHash VARCHAR(32),
|
|
IN importSha256 VARCHAR(80),
|
|
IN personId INTEGER
|
|
)
|
|
BEGIN
|
|
|
|
DECLARE financialTransactionId INTEGER;
|
|
DECLARE existingSha256 VARCHAR(80);
|
|
|
|
SELECT 0 INTO financialTransactionId;
|
|
|
|
IF ((SELECT COUNT(*) FROM FinancialTransactions WHERE FinancialTransactions.ImportHash=importHash) = 0)
|
|
THEN
|
|
INSERT INTO FinancialTransactions (OrganizationId, DateTime, Comment, ImportHash, ImportSha256)
|
|
VALUES (organizationId, dateTime, comment, importHash, importSha256);
|
|
|
|
SELECT LAST_INSERT_ID() INTO financialTransactionId;
|
|
|
|
INSERT INTO FinancialTransactionRows (FinancialAccountId, FinancialTransactionId, AmountCents, CreatedDateTime, CreatedByPersonId)
|
|
VALUES (financialAccountId, financialTransactionId, amountCents, DateTime, personId);
|
|
|
|
ELSE
|
|
|
|
SELECT FinancialTransactions.FinancialTransactionId INTO financialTransactionId FROM FinancialTransactions WHERE FinancialTransactions.ImportHash=importHash;
|
|
SELECT FinancialTransactions.ImportSha256 INTO existingSha256 FROM FinancialTransactions WHERE FinancialTransactions.ImportHash=importHash;
|
|
|
|
IF (existingSha256 != importSha256)
|
|
THEN
|
|
|
|
UPDATE FinancialTransactions
|
|
SET FinancialTransactions.ImportSha256=importSha256
|
|
WHERE FinancialTransactions.FinancialTransactionId=financialTransactionId;
|
|
|
|
SELECT -financialTransactionId INTO financialTransactionId;
|
|
|
|
END IF;
|
|
|
|
END IF;
|
|
|
|
SELECT financialTransactionId AS Identity;
|
|
|
|
END
|
|
|
|
#
|
|
|
|
|
|
CREATE PROCEDURE `SetFinancialTransactionImportSha256`(
|
|
IN financialTransactionId INTEGER,
|
|
IN importSha256 VARCHAR(80)
|
|
)
|
|
BEGIN
|
|
|
|
UPDATE FinancialTransactions SET FinancialTransactions.ImportSha256 = importSha256
|
|
WHERE FinancialTransactions.FinancialTransactionId = financialTransactionId;
|
|
|
|
END
|