Files
2017-10-10 09:11:12 +02:00

47 lines
984 B
SQL

ALTER TABLE `VatReports`
ADD COLUMN `OpenTransactionId` INT NOT NULL DEFAULT 0 AFTER `Open`,
ADD COLUMN `CloseTransactionId` INT NOT NULL DEFAULT 0 AFTER `OpenTransactionId`,
ADD INDEX `Ix_OpenTx` (`OpenTransactionId` ASC),
ADD INDEX `Ix_CloseTx` (`CloseTransactionId` ASC);
#
DROP PROCEDURE IF EXISTS `SetVatReportOpenTransaction`
#
DROP PROCEDURE IF EXISTS `SetVatReportCloseTransaction`
#
CREATE PROCEDURE `SetVatReportOpenTransaction`(
IN vatReportId INTEGER,
IN openTransactionId INTEGER
)
BEGIN
UPDATE VatReports SET OpenTransactionId=openTransactionId
WHERE VatReports.VatReportId=vatReportId;
SELECT ROW_COUNT() AS RecordsUpdated;
END
#
CREATE PROCEDURE `SetVatReportCloseTransaction`(
IN vatReportId INTEGER,
IN closeTransactionId INTEGER
)
BEGIN
UPDATE VatReports SET CloseTransactionId=closeTransactionId
WHERE VatReports.VatReportId=vatReportId
AND VatReports.CloseTransactionId=0;
SELECT ROW_COUNT() AS RecordsUpdated;
END