Description:
When I Synchronize the model, it always makes the same alterations to the Accounts table - even if I have only just forward engineered the model.
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
ALTER TABLE `accounts`.`Account`
CHANGE COLUMN `EndingBalance` `EndingBalance` DECIMAL(10) NULL DEFAULT NULL ;
ALTER TABLE `accounts`.`Payments`
CHANGE COLUMN `PaymentAmount` `PaymentAmount` DECIMAL(10) NOT NULL DEFAULT 0 ;
-- -----------------------------------------------------
-- Placeholder table for view `accounts`.`Extended_Document`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `accounts`.`Extended_Document` (`idDocument` INT, `DocumentMemo` INT, `DocumentTypeId` INT, `DocType` INT, `Sign` INT, `DocumentDate` INT, `DocumentNameAddressId` INT, `DocumentName` INT, `DocumentAddress` INT, `DocumentIdentifier` INT, `AccountingAmount` INT, `AccountingOutstanding` INT, `DocumentAmount` INT, `DocumentOutstanding` INT, `DocumentAccountId` INT, `DocumentAccountName` INT, `Cleared` INT, `DocumentVatAmount` INT, `VatPaid` INT);
-- -----------------------------------------------------
-- Placeholder table for view `accounts`.`Extended_Line`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `accounts`.`Extended_Line` (`idLine` INT, `Qty` INT, `ProductId` INT, `LineAmount` INT, `VatCodeId` INT, `VatRate` INT, `VatAmount` INT, `ProductName` INT, `UnitPrice` INT, `Code` INT, `VatDescription` INT, `DocumentId` INT, `JournalNum` INT, `Memo` INT, `AccountId` INT, `AccountName` INT, `AccountDescription` INT);
-- -----------------------------------------------------
-- Placeholder table for view `accounts`.`Vat_Journal`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `accounts`.`Vat_Journal` (`VatType` INT, `Memo` INT, `VatCodeId` INT, `VatRate` INT, `VatAmount` INT, `LineAmount` INT);
USE `accounts`;
-- -----------------------------------------------------
-- View `accounts`.`Extended_Document`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `accounts`.`Extended_Document`;
USE `accounts`;
CREATE OR REPLACE VIEW `Extended_Document` AS
SELECT idDocument, DocumentMemo, DocumentTypeId, DocType, Sign,
DocumentDate, Journal.NameAddressId AS DocumentNameAddressId, Name AS DocumentName, DocumentAddress, DocumentIdentifier,
Journal.Amount As AccountingAmount,
Journal.Outstanding As AccountingOutstanding,
-Journal.Amount * DocumentType.Sign As DocumentAmount,
-Journal.Outstanding * DocumentType.Sign As DocumentOutstanding,
Journal.AccountId AS DocumentAccountId, AccountName As DocumentAccountName, Journal.Cleared,
VatJournal.Amount * DocumentType.Sign As DocumentVatAmount,
VatPaid
FROM Document
JOIN DocumentType ON idDocumentType = DocumentTypeId
JOIN Journal ON Journal.DocumentId = idDocument AND Journal.JournalNum = 1
JOIN Account ON idAccount = Journal.AccountId
JOIN NameAddress ON idNameAddress = NameAddressId
LEFT JOIN Journal AS VatJournal ON VatJournal.DocumentId = idDocument AND VatJournal.AccountId = 8
;
USE `accounts`;
-- -----------------------------------------------------
-- View `accounts`.`Extended_Line`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `accounts`.`Extended_Line`;
USE `accounts`;
CREATE OR REPLACE VIEW `Extended_Line` AS
SELECT Line.*, Product.ProductName, Product.UnitPrice, VatCode.Code, VatCode.VatDescription, Journal.DocumentId, Journal.JournalNum, Journal.Memo, Journal.AccountId, Account.AccountName, Account.AccountDescription
FROM Line
JOIN Product ON Product.idProduct = Line.ProductId
JOIN VatCode ON VatCode.idVatCode = Line.VatCodeId
JOIN Journal ON Journal.idJournal = Line.idLine
JOIN Account ON Account.idAccount = Journal.AccountId
;
USE `accounts`;
-- -----------------------------------------------------
-- View `accounts`.`Vat_Journal`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `accounts`.`Vat_Journal`;
USE `accounts`;
CREATE OR REPLACE VIEW `Vat_Journal` AS
SELECT Extended_Document.*, CASE
WHEN DocumentAccountId = 1 THEN -1
WHEN DocumentAccountId = 2 THEN 1
WHEN DocumentAmount * Sign < 0 THEN -1
ELSE 1
END AS VatType,
Memo,
Line.VatCodeId, Line.VatRate,
SUM(Line.VatAmount) VatAmount, SUM(Line.LineAmount) AS LineAmount
FROM Extended_Document
JOIN Journal ON IdDocument = DocumentId
JOIN Line ON idLine = idJournal
WHERE DocumentTypeId IN (1, 3, 4, 6)
OR Line.VatCodeId IS NOT NULL
GROUP BY idDocument, Line.VatCodeId, Line.VatRate
;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
How to repeat:
Open the model. Forward engineer it to a server. Synchronize the model.
NB: There doesn't seem to be any way to attach the model to this bug report! Therefore I have included the forward engineer SQL so you could build a model from it:
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
-- -----------------------------------------------------
-- Schema accounts
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `accounts` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `accounts` ;
-- -----------------------------------------------------
-- Table `accounts`.`AccountType`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `accounts`.`AccountType` ;
CREATE TABLE IF NOT EXISTS `accounts`.`AccountType` (
`idAccountType` INT NOT NULL AUTO_INCREMENT,
`Heading` VARCHAR(45) NOT NULL DEFAULT 'Other',
`AcctType` VARCHAR(45) NOT NULL,
`Negate` TINYINT(1) NOT NULL DEFAULT 0,
`BalanceSheet` TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`idAccountType`),
UNIQUE INDEX `Name_UNIQUE` (`AcctType` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `accounts`.`Account`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `accounts`.`Account` ;
CREATE TABLE IF NOT EXISTS `accounts`.`Account` (
`idAccount` INT NOT NULL AUTO_INCREMENT,
`AccountName` VARCHAR(45) NOT NULL,
`AccountDescription` VARCHAR(45) NOT NULL,
`AccountTypeId` INT NOT NULL,
`Protected` TINYINT(1) NOT NULL DEFAULT 0,
`EndingBalance` DECIMAL(10) NULL,
`NextChequeNumber` INT NOT NULL DEFAULT 0,
`NextDepositNumber` INT NOT NULL DEFAULT 0,
PRIMARY KEY (`idAccount`),
UNIQUE INDEX `AccountName_UNIQUE` (`AccountName` ASC),
INDEX `fk_Account_AccountType1_idx` (`AccountTypeId` ASC),
CONSTRAINT `fk_Account_AccountType1`
FOREIGN KEY (`AccountTypeId`)
REFERENCES `accounts`.`AccountType` (`idAccountType`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `accounts`.`DocumentType`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `accounts`.`DocumentType` ;
CREATE TABLE IF NOT EXISTS `accounts`.`DocumentType` (
`idDocumentType` INT NOT NULL AUTO_INCREMENT,
`DocType` VARCHAR(45) NOT NULL,
`PrimaryAccountId` INT NULL,
`NameType` CHAR(1) NOT NULL DEFAULT 'O',
`Sign` INT NOT NULL DEFAULT 1,
PRIMARY KEY (`idDocumentType`),
UNIQUE INDEX `DocumentName_UNIQUE` (`DocType` ASC),
INDEX `fk_DocumentType_Account1_idx` (`PrimaryAccountId` ASC),
CONSTRAINT `fk_DocumentType_Account1`
FOREIGN KEY (`PrimaryAccountId`)
REFERENCES `accounts`.`Account` (`idAccount`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `accounts`.`Document`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `accounts`.`Document` ;
CREATE TABLE IF NOT EXISTS `accounts`.`Document` (
`idDocument` INT NOT NULL AUTO_INCREMENT,
`DocumentMemo` TEXT NOT NULL,
`DocumentTypeId` INT NOT NULL,
`DocumentAddress` TEXT NOT NULL,
`DocumentDate` DATETIME NOT NULL,
`DocumentIdentifier` VARCHAR(45) NOT NULL,
`VatPaid` INT NULL DEFAULT 0,
PRIMARY KEY (`idDocument`),
INDEX `fk_Document_DocumentType_idx` (`DocumentTypeId` ASC),
INDEX `DocumentDate` (`DocumentDate` ASC),
CONSTRAINT `fk_Document_DocumentType`
FOREIGN KEY (`DocumentTypeId`)
REFERENCES `accounts`.`DocumentType` (`idDocumentType`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `accounts`.`NameAddress`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `accounts`.`NameAddress` ;
CREATE TABLE IF NOT EXISTS `accounts`.`NameAddress` (
`idNameAddress` INT NOT NULL AUTO_INCREMENT,
`Type` CHAR(1) NOT NULL,
`Name` VARCHAR(45) NOT NULL,
`Address` TEXT NOT NULL,
`PostCode` VARCHAR(45) NOT NULL,
`Telephone` VARCHAR(45) NOT NULL,
`Email` VARCHAR(45) NOT NULL,
`Contact` VARCHAR(45) NOT NULL,
`Hidden` TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (`idNameAddress`),
UNIQUE INDEX `Type_Name` (`Type` ASC, `Name` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `accounts`.`Journal`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `accounts`.`Journal` ;
CREATE TABLE IF NOT EXISTS `accounts`.`Journal` (
`idJournal` INT NOT NULL AUTO_INCREMENT,
`DocumentId` INT NOT NULL,
`AccountId` INT NOT NULL,
`Memo` VARCHAR(45) NOT NULL,
`JournalNum` INT NOT NULL,
`Amount` DECIMAL(10,2) NOT NULL,
`Outstanding` DECIMAL(10,2) NOT NULL DEFAULT 0,
`Cleared` CHAR(1) NOT NULL,
`NameAddressId` INT NULL,
PRIMARY KEY (`idJournal`),
INDEX `fk_Journal_Document1_idx` (`DocumentId` ASC),
INDEX `fk_Journal_Account1_idx` (`AccountId` ASC),
UNIQUE INDEX `Document_Num` (`DocumentId` ASC, `JournalNum` ASC),
INDEX `fk_Journal_NameAddress1_idx` (`NameAddressId` ASC),
CONSTRAINT `fk_Journal_Document1`
FOREIGN KEY (`DocumentId`)
REFERENCES `accounts`.`Document` (`idDocument`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Journal_Account1`
FOREIGN KEY (`AccountId`)
REFERENCES `accounts`.`Account` (`idAccount`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Journal_NameAddress1`
FOREIGN KEY (`NameAddressId`)
REFERENCES `accounts`.`NameAddress` (`idNameAddress`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `accounts`.`VatCode`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `accounts`.`VatCode` ;
CREATE TABLE IF NOT EXISTS `accounts`.`VatCode` (
`idVatCode` INT NOT NULL AUTO_INCREMENT,
`Code` VARCHAR(45) NOT NULL,
`VatDescription` VARCHAR(45) NOT NULL,
`Rate` DECIMAL(10,2) NOT NULL,
PRIMARY KEY (`idVatCode`),
UNIQUE INDEX `Name_UNIQUE` (`Code` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `accounts`.`Product`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `accounts`.`Product` ;
CREATE TABLE IF NOT EXISTS `accounts`.`Product` (
`idProduct` INT NOT NULL AUTO_INCREMENT,
`ProductName` VARCHAR(45) NOT NULL,
`ProductDescription` VARCHAR(45) NOT NULL,
`UnitPrice` DECIMAL(10,2) NOT NULL,
`VatCodeId` INT NULL,
`AccountId` INT NOT NULL,
PRIMARY KEY (`idProduct`),
UNIQUE INDEX `Name_UNIQUE` (`ProductName` ASC),
INDEX `fk_Product_VatCode1_idx` (`VatCodeId` ASC),
INDEX `fk_Product_Account1_idx` (`AccountId` ASC),
CONSTRAINT `fk_Product_VatCode1`
FOREIGN KEY (`VatCodeId`)
REFERENCES `accounts`.`VatCode` (`idVatCode`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Product_Account1`
FOREIGN KEY (`AccountId`)
REFERENCES `accounts`.`Account` (`idAccount`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `accounts`.`Line`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `accounts`.`Line` ;
CREATE TABLE IF NOT EXISTS `accounts`.`Line` (
`idLine` INT NOT NULL,
`Qty` DOUBLE NOT NULL DEFAULT 0,
`ProductId` INT NULL,
`LineAmount` DECIMAL(10,2) NOT NULL,
`VatCodeId` INT NULL,
`VatRate` DECIMAL(10,2) NOT NULL DEFAULT 0,
`VatAmount` DECIMAL(10,2) NOT NULL DEFAULT 0,
INDEX `fk_Line_VatCode1_idx` (`VatCodeId` ASC),
INDEX `fk_Line_Product1_idx` (`ProductId` ASC),
INDEX `fk_Line_Journal1_idx` (`idLine` ASC),
PRIMARY KEY (`idLine`),
CONSTRAINT `fk_Line_VatCode1`
FOREIGN KEY (`VatCodeId`)
REFERENCES `accounts`.`VatCode` (`idVatCode`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Line_Product1`
FOREIGN KEY (`ProductId`)
REFERENCES `accounts`.`Product` (`idProduct`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Line_Journal1`
FOREIGN KEY (`idLine`)
REFERENCES `accounts`.`Journal` (`idJournal`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `accounts`.`Settings`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `accounts`.`Settings` ;
CREATE TABLE IF NOT EXISTS `accounts`.`Settings` (
`idSettings` INT NOT NULL AUTO_INCREMENT,
`DefaultBankAccount` INT NULL,
`CompanyName` VARCHAR(45) NOT NULL,
`CompanyAddress` TEXT NOT NULL,
`CompanyPhone` VARCHAR(45) NOT NULL,
`CompanyEmail` VARCHAR(45) NOT NULL,
`WebSite` VARCHAR(45) NOT NULL,
`VatRegistration` VARCHAR(45) NOT NULL,
`CompanyNumber` VARCHAR(45) NOT NULL,
`YearStartMonth` INT NOT NULL DEFAULT 1,
`YearStartDay` INT NOT NULL DEFAULT 0,
`TermsDays` INT NOT NULL DEFAULT 14,
`NextInvoiceNumber` INT NOT NULL DEFAULT 1,
`NextBillNumber` INT NOT NULL DEFAULT 1,
`NextJournalNumber` INT NOT NULL DEFAULT 1,
`DatabaseLogging` INT NOT NULL DEFAULT 0,
PRIMARY KEY (`idSettings`),
INDEX `fk_Settings_Account1_idx` (`DefaultBankAccount` ASC),
CONSTRAINT `fk_Settings_Account1`
FOREIGN KEY (`DefaultBankAccount`)
REFERENCES `accounts`.`Account` (`idAccount`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `accounts`.`AuditTrail`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `accounts`.`AuditTrail` ;
CREATE TABLE IF NOT EXISTS `accounts`.`AuditTrail` (
`idAuditTrail` INT NOT NULL AUTO_INCREMENT,
`DateChanged` DATETIME NOT NULL,
`TableName` VARCHAR(45) NOT NULL,
`ChangeType` INT NOT NULL,
`RecordId` INT NOT NULL,
`Record` TEXT NOT NULL,
PRIMARY KEY (`idAuditTrail`),
INDEX `DateIndex` (`DateChanged` ASC, `idAuditTrail` ASC),
INDEX `RecordIndex` (`TableName` ASC, `RecordId` ASC, `ChangeType` ASC, `DateChanged` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `accounts`.`Report`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `accounts`.`Report` ;
CREATE TABLE IF NOT EXISTS `accounts`.`Report` (
`idReport` INT NOT NULL AUTO_INCREMENT,
`ReportName` VARCHAR(45) NOT NULL,
`ReportType` VARCHAR(45) NOT NULL,
`ReportSettings` TEXT NOT NULL,
`ReportGroup` VARCHAR(45) NOT NULL,
PRIMARY KEY (`idReport`),
UNIQUE INDEX `Name_Index` (`ReportGroup` ASC, `ReportName` ASC))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `accounts`.`Payments`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `accounts`.`Payments` ;
CREATE TABLE IF NOT EXISTS `accounts`.`Payments` (
`idPayment` INT NOT NULL,
`idPaid` INT NOT NULL,
`PaymentAmount` DECIMAL(10) NOT NULL DEFAULT 0,
INDEX `PK` (`idPayment` ASC, `idPaid` ASC),
INDEX `fk_Payments_Document2_idx` (`idPaid` ASC),
CONSTRAINT `fk_Payments_Document1`
FOREIGN KEY (`idPayment`)
REFERENCES `accounts`.`Document` (`idDocument`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Payments_Document2`
FOREIGN KEY (`idPaid`)
REFERENCES `accounts`.`Document` (`idDocument`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
USE `accounts` ;
-- -----------------------------------------------------
-- Placeholder table for view `accounts`.`Extended_Document`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `accounts`.`Extended_Document` (`idDocument` INT, `DocumentMemo` INT, `DocumentTypeId` INT, `DocType` INT, `Sign` INT, `DocumentDate` INT, `DocumentNameAddressId` INT, `DocumentName` INT, `DocumentAddress` INT, `DocumentIdentifier` INT, `AccountingAmount` INT, `AccountingOutstanding` INT, `DocumentAmount` INT, `DocumentOutstanding` INT, `DocumentAccountId` INT, `DocumentAccountName` INT, `Cleared` INT, `DocumentVatAmount` INT, `VatPaid` INT);
-- -----------------------------------------------------
-- Placeholder table for view `accounts`.`Extended_Line`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `accounts`.`Extended_Line` (`idLine` INT, `Qty` INT, `ProductId` INT, `LineAmount` INT, `VatCodeId` INT, `VatRate` INT, `VatAmount` INT, `ProductName` INT, `UnitPrice` INT, `Code` INT, `VatDescription` INT, `DocumentId` INT, `JournalNum` INT, `Memo` INT, `AccountId` INT, `AccountName` INT, `AccountDescription` INT);
-- -----------------------------------------------------
-- Placeholder table for view `accounts`.`Vat_Journal`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `accounts`.`Vat_Journal` (`VatType` INT, `Memo` INT, `VatCodeId` INT, `VatRate` INT, `VatAmount` INT, `LineAmount` INT);
-- -----------------------------------------------------
-- View `accounts`.`Extended_Document`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `accounts`.`Extended_Document` ;
DROP TABLE IF EXISTS `accounts`.`Extended_Document`;
USE `accounts`;
CREATE OR REPLACE VIEW `Extended_Document` AS
SELECT idDocument, DocumentMemo, DocumentTypeId, DocType, Sign,
DocumentDate, Journal.NameAddressId AS DocumentNameAddressId, Name AS DocumentName, DocumentAddress, DocumentIdentifier,
Journal.Amount As AccountingAmount,
Journal.Outstanding As AccountingOutstanding,
-Journal.Amount * DocumentType.Sign As DocumentAmount,
-Journal.Outstanding * DocumentType.Sign As DocumentOutstanding,
Journal.AccountId AS DocumentAccountId, AccountName As DocumentAccountName, Journal.Cleared,
VatJournal.Amount * DocumentType.Sign As DocumentVatAmount,
VatPaid
FROM Document
JOIN DocumentType ON idDocumentType = DocumentTypeId
JOIN Journal ON Journal.DocumentId = idDocument AND Journal.JournalNum = 1
JOIN Account ON idAccount = Journal.AccountId
JOIN NameAddress ON idNameAddress = NameAddressId
LEFT JOIN Journal AS VatJournal ON VatJournal.DocumentId = idDocument AND VatJournal.AccountId = 8
;
-- -----------------------------------------------------
-- View `accounts`.`Extended_Line`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `accounts`.`Extended_Line` ;
DROP TABLE IF EXISTS `accounts`.`Extended_Line`;
USE `accounts`;
CREATE OR REPLACE VIEW `Extended_Line` AS
SELECT Line.*, Product.ProductName, Product.UnitPrice, VatCode.Code, VatCode.VatDescription, Journal.DocumentId, Journal.JournalNum, Journal.Memo, Journal.AccountId, Account.AccountName, Account.AccountDescription
FROM Line
JOIN Product ON Product.idProduct = Line.ProductId
JOIN VatCode ON VatCode.idVatCode = Line.VatCodeId
JOIN Journal ON Journal.idJournal = Line.idLine
JOIN Account ON Account.idAccount = Journal.AccountId
;
-- -----------------------------------------------------
-- View `accounts`.`Vat_Journal`
-- -----------------------------------------------------
DROP VIEW IF EXISTS `accounts`.`Vat_Journal` ;
DROP TABLE IF EXISTS `accounts`.`Vat_Journal`;
USE `accounts`;
CREATE OR REPLACE VIEW `Vat_Journal` AS
SELECT Extended_Document.*, CASE
WHEN DocumentAccountId = 1 THEN -1
WHEN DocumentAccountId = 2 THEN 1
WHEN DocumentAmount * Sign < 0 THEN -1
ELSE 1
END AS VatType,
Memo,
Line.VatCodeId, Line.VatRate,
SUM(Line.VatAmount) VatAmount, SUM(Line.LineAmount) AS LineAmount
FROM Extended_Document
JOIN Journal ON IdDocument = DocumentId
JOIN Line ON idLine = idJournal
WHERE DocumentTypeId IN (1, 3, 4, 6)
OR Line.VatCodeId IS NOT NULL
GROUP BY idDocument, Line.VatCodeId, Line.VatRate
;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
Description: When I Synchronize the model, it always makes the same alterations to the Accounts table - even if I have only just forward engineered the model. SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; ALTER TABLE `accounts`.`Account` CHANGE COLUMN `EndingBalance` `EndingBalance` DECIMAL(10) NULL DEFAULT NULL ; ALTER TABLE `accounts`.`Payments` CHANGE COLUMN `PaymentAmount` `PaymentAmount` DECIMAL(10) NOT NULL DEFAULT 0 ; -- ----------------------------------------------------- -- Placeholder table for view `accounts`.`Extended_Document` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `accounts`.`Extended_Document` (`idDocument` INT, `DocumentMemo` INT, `DocumentTypeId` INT, `DocType` INT, `Sign` INT, `DocumentDate` INT, `DocumentNameAddressId` INT, `DocumentName` INT, `DocumentAddress` INT, `DocumentIdentifier` INT, `AccountingAmount` INT, `AccountingOutstanding` INT, `DocumentAmount` INT, `DocumentOutstanding` INT, `DocumentAccountId` INT, `DocumentAccountName` INT, `Cleared` INT, `DocumentVatAmount` INT, `VatPaid` INT); -- ----------------------------------------------------- -- Placeholder table for view `accounts`.`Extended_Line` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `accounts`.`Extended_Line` (`idLine` INT, `Qty` INT, `ProductId` INT, `LineAmount` INT, `VatCodeId` INT, `VatRate` INT, `VatAmount` INT, `ProductName` INT, `UnitPrice` INT, `Code` INT, `VatDescription` INT, `DocumentId` INT, `JournalNum` INT, `Memo` INT, `AccountId` INT, `AccountName` INT, `AccountDescription` INT); -- ----------------------------------------------------- -- Placeholder table for view `accounts`.`Vat_Journal` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `accounts`.`Vat_Journal` (`VatType` INT, `Memo` INT, `VatCodeId` INT, `VatRate` INT, `VatAmount` INT, `LineAmount` INT); USE `accounts`; -- ----------------------------------------------------- -- View `accounts`.`Extended_Document` -- ----------------------------------------------------- DROP TABLE IF EXISTS `accounts`.`Extended_Document`; USE `accounts`; CREATE OR REPLACE VIEW `Extended_Document` AS SELECT idDocument, DocumentMemo, DocumentTypeId, DocType, Sign, DocumentDate, Journal.NameAddressId AS DocumentNameAddressId, Name AS DocumentName, DocumentAddress, DocumentIdentifier, Journal.Amount As AccountingAmount, Journal.Outstanding As AccountingOutstanding, -Journal.Amount * DocumentType.Sign As DocumentAmount, -Journal.Outstanding * DocumentType.Sign As DocumentOutstanding, Journal.AccountId AS DocumentAccountId, AccountName As DocumentAccountName, Journal.Cleared, VatJournal.Amount * DocumentType.Sign As DocumentVatAmount, VatPaid FROM Document JOIN DocumentType ON idDocumentType = DocumentTypeId JOIN Journal ON Journal.DocumentId = idDocument AND Journal.JournalNum = 1 JOIN Account ON idAccount = Journal.AccountId JOIN NameAddress ON idNameAddress = NameAddressId LEFT JOIN Journal AS VatJournal ON VatJournal.DocumentId = idDocument AND VatJournal.AccountId = 8 ; USE `accounts`; -- ----------------------------------------------------- -- View `accounts`.`Extended_Line` -- ----------------------------------------------------- DROP TABLE IF EXISTS `accounts`.`Extended_Line`; USE `accounts`; CREATE OR REPLACE VIEW `Extended_Line` AS SELECT Line.*, Product.ProductName, Product.UnitPrice, VatCode.Code, VatCode.VatDescription, Journal.DocumentId, Journal.JournalNum, Journal.Memo, Journal.AccountId, Account.AccountName, Account.AccountDescription FROM Line JOIN Product ON Product.idProduct = Line.ProductId JOIN VatCode ON VatCode.idVatCode = Line.VatCodeId JOIN Journal ON Journal.idJournal = Line.idLine JOIN Account ON Account.idAccount = Journal.AccountId ; USE `accounts`; -- ----------------------------------------------------- -- View `accounts`.`Vat_Journal` -- ----------------------------------------------------- DROP TABLE IF EXISTS `accounts`.`Vat_Journal`; USE `accounts`; CREATE OR REPLACE VIEW `Vat_Journal` AS SELECT Extended_Document.*, CASE WHEN DocumentAccountId = 1 THEN -1 WHEN DocumentAccountId = 2 THEN 1 WHEN DocumentAmount * Sign < 0 THEN -1 ELSE 1 END AS VatType, Memo, Line.VatCodeId, Line.VatRate, SUM(Line.VatAmount) VatAmount, SUM(Line.LineAmount) AS LineAmount FROM Extended_Document JOIN Journal ON IdDocument = DocumentId JOIN Line ON idLine = idJournal WHERE DocumentTypeId IN (1, 3, 4, 6) OR Line.VatCodeId IS NOT NULL GROUP BY idDocument, Line.VatCodeId, Line.VatRate ; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; How to repeat: Open the model. Forward engineer it to a server. Synchronize the model. NB: There doesn't seem to be any way to attach the model to this bug report! Therefore I have included the forward engineer SQL so you could build a model from it: SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'; -- ----------------------------------------------------- -- Schema accounts -- ----------------------------------------------------- CREATE SCHEMA IF NOT EXISTS `accounts` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ; USE `accounts` ; -- ----------------------------------------------------- -- Table `accounts`.`AccountType` -- ----------------------------------------------------- DROP TABLE IF EXISTS `accounts`.`AccountType` ; CREATE TABLE IF NOT EXISTS `accounts`.`AccountType` ( `idAccountType` INT NOT NULL AUTO_INCREMENT, `Heading` VARCHAR(45) NOT NULL DEFAULT 'Other', `AcctType` VARCHAR(45) NOT NULL, `Negate` TINYINT(1) NOT NULL DEFAULT 0, `BalanceSheet` TINYINT(1) NOT NULL DEFAULT 0, PRIMARY KEY (`idAccountType`), UNIQUE INDEX `Name_UNIQUE` (`AcctType` ASC)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `accounts`.`Account` -- ----------------------------------------------------- DROP TABLE IF EXISTS `accounts`.`Account` ; CREATE TABLE IF NOT EXISTS `accounts`.`Account` ( `idAccount` INT NOT NULL AUTO_INCREMENT, `AccountName` VARCHAR(45) NOT NULL, `AccountDescription` VARCHAR(45) NOT NULL, `AccountTypeId` INT NOT NULL, `Protected` TINYINT(1) NOT NULL DEFAULT 0, `EndingBalance` DECIMAL(10) NULL, `NextChequeNumber` INT NOT NULL DEFAULT 0, `NextDepositNumber` INT NOT NULL DEFAULT 0, PRIMARY KEY (`idAccount`), UNIQUE INDEX `AccountName_UNIQUE` (`AccountName` ASC), INDEX `fk_Account_AccountType1_idx` (`AccountTypeId` ASC), CONSTRAINT `fk_Account_AccountType1` FOREIGN KEY (`AccountTypeId`) REFERENCES `accounts`.`AccountType` (`idAccountType`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `accounts`.`DocumentType` -- ----------------------------------------------------- DROP TABLE IF EXISTS `accounts`.`DocumentType` ; CREATE TABLE IF NOT EXISTS `accounts`.`DocumentType` ( `idDocumentType` INT NOT NULL AUTO_INCREMENT, `DocType` VARCHAR(45) NOT NULL, `PrimaryAccountId` INT NULL, `NameType` CHAR(1) NOT NULL DEFAULT 'O', `Sign` INT NOT NULL DEFAULT 1, PRIMARY KEY (`idDocumentType`), UNIQUE INDEX `DocumentName_UNIQUE` (`DocType` ASC), INDEX `fk_DocumentType_Account1_idx` (`PrimaryAccountId` ASC), CONSTRAINT `fk_DocumentType_Account1` FOREIGN KEY (`PrimaryAccountId`) REFERENCES `accounts`.`Account` (`idAccount`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `accounts`.`Document` -- ----------------------------------------------------- DROP TABLE IF EXISTS `accounts`.`Document` ; CREATE TABLE IF NOT EXISTS `accounts`.`Document` ( `idDocument` INT NOT NULL AUTO_INCREMENT, `DocumentMemo` TEXT NOT NULL, `DocumentTypeId` INT NOT NULL, `DocumentAddress` TEXT NOT NULL, `DocumentDate` DATETIME NOT NULL, `DocumentIdentifier` VARCHAR(45) NOT NULL, `VatPaid` INT NULL DEFAULT 0, PRIMARY KEY (`idDocument`), INDEX `fk_Document_DocumentType_idx` (`DocumentTypeId` ASC), INDEX `DocumentDate` (`DocumentDate` ASC), CONSTRAINT `fk_Document_DocumentType` FOREIGN KEY (`DocumentTypeId`) REFERENCES `accounts`.`DocumentType` (`idDocumentType`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `accounts`.`NameAddress` -- ----------------------------------------------------- DROP TABLE IF EXISTS `accounts`.`NameAddress` ; CREATE TABLE IF NOT EXISTS `accounts`.`NameAddress` ( `idNameAddress` INT NOT NULL AUTO_INCREMENT, `Type` CHAR(1) NOT NULL, `Name` VARCHAR(45) NOT NULL, `Address` TEXT NOT NULL, `PostCode` VARCHAR(45) NOT NULL, `Telephone` VARCHAR(45) NOT NULL, `Email` VARCHAR(45) NOT NULL, `Contact` VARCHAR(45) NOT NULL, `Hidden` TINYINT(1) NOT NULL DEFAULT 0, PRIMARY KEY (`idNameAddress`), UNIQUE INDEX `Type_Name` (`Type` ASC, `Name` ASC)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `accounts`.`Journal` -- ----------------------------------------------------- DROP TABLE IF EXISTS `accounts`.`Journal` ; CREATE TABLE IF NOT EXISTS `accounts`.`Journal` ( `idJournal` INT NOT NULL AUTO_INCREMENT, `DocumentId` INT NOT NULL, `AccountId` INT NOT NULL, `Memo` VARCHAR(45) NOT NULL, `JournalNum` INT NOT NULL, `Amount` DECIMAL(10,2) NOT NULL, `Outstanding` DECIMAL(10,2) NOT NULL DEFAULT 0, `Cleared` CHAR(1) NOT NULL, `NameAddressId` INT NULL, PRIMARY KEY (`idJournal`), INDEX `fk_Journal_Document1_idx` (`DocumentId` ASC), INDEX `fk_Journal_Account1_idx` (`AccountId` ASC), UNIQUE INDEX `Document_Num` (`DocumentId` ASC, `JournalNum` ASC), INDEX `fk_Journal_NameAddress1_idx` (`NameAddressId` ASC), CONSTRAINT `fk_Journal_Document1` FOREIGN KEY (`DocumentId`) REFERENCES `accounts`.`Document` (`idDocument`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_Journal_Account1` FOREIGN KEY (`AccountId`) REFERENCES `accounts`.`Account` (`idAccount`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_Journal_NameAddress1` FOREIGN KEY (`NameAddressId`) REFERENCES `accounts`.`NameAddress` (`idNameAddress`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `accounts`.`VatCode` -- ----------------------------------------------------- DROP TABLE IF EXISTS `accounts`.`VatCode` ; CREATE TABLE IF NOT EXISTS `accounts`.`VatCode` ( `idVatCode` INT NOT NULL AUTO_INCREMENT, `Code` VARCHAR(45) NOT NULL, `VatDescription` VARCHAR(45) NOT NULL, `Rate` DECIMAL(10,2) NOT NULL, PRIMARY KEY (`idVatCode`), UNIQUE INDEX `Name_UNIQUE` (`Code` ASC)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `accounts`.`Product` -- ----------------------------------------------------- DROP TABLE IF EXISTS `accounts`.`Product` ; CREATE TABLE IF NOT EXISTS `accounts`.`Product` ( `idProduct` INT NOT NULL AUTO_INCREMENT, `ProductName` VARCHAR(45) NOT NULL, `ProductDescription` VARCHAR(45) NOT NULL, `UnitPrice` DECIMAL(10,2) NOT NULL, `VatCodeId` INT NULL, `AccountId` INT NOT NULL, PRIMARY KEY (`idProduct`), UNIQUE INDEX `Name_UNIQUE` (`ProductName` ASC), INDEX `fk_Product_VatCode1_idx` (`VatCodeId` ASC), INDEX `fk_Product_Account1_idx` (`AccountId` ASC), CONSTRAINT `fk_Product_VatCode1` FOREIGN KEY (`VatCodeId`) REFERENCES `accounts`.`VatCode` (`idVatCode`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_Product_Account1` FOREIGN KEY (`AccountId`) REFERENCES `accounts`.`Account` (`idAccount`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `accounts`.`Line` -- ----------------------------------------------------- DROP TABLE IF EXISTS `accounts`.`Line` ; CREATE TABLE IF NOT EXISTS `accounts`.`Line` ( `idLine` INT NOT NULL, `Qty` DOUBLE NOT NULL DEFAULT 0, `ProductId` INT NULL, `LineAmount` DECIMAL(10,2) NOT NULL, `VatCodeId` INT NULL, `VatRate` DECIMAL(10,2) NOT NULL DEFAULT 0, `VatAmount` DECIMAL(10,2) NOT NULL DEFAULT 0, INDEX `fk_Line_VatCode1_idx` (`VatCodeId` ASC), INDEX `fk_Line_Product1_idx` (`ProductId` ASC), INDEX `fk_Line_Journal1_idx` (`idLine` ASC), PRIMARY KEY (`idLine`), CONSTRAINT `fk_Line_VatCode1` FOREIGN KEY (`VatCodeId`) REFERENCES `accounts`.`VatCode` (`idVatCode`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_Line_Product1` FOREIGN KEY (`ProductId`) REFERENCES `accounts`.`Product` (`idProduct`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_Line_Journal1` FOREIGN KEY (`idLine`) REFERENCES `accounts`.`Journal` (`idJournal`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `accounts`.`Settings` -- ----------------------------------------------------- DROP TABLE IF EXISTS `accounts`.`Settings` ; CREATE TABLE IF NOT EXISTS `accounts`.`Settings` ( `idSettings` INT NOT NULL AUTO_INCREMENT, `DefaultBankAccount` INT NULL, `CompanyName` VARCHAR(45) NOT NULL, `CompanyAddress` TEXT NOT NULL, `CompanyPhone` VARCHAR(45) NOT NULL, `CompanyEmail` VARCHAR(45) NOT NULL, `WebSite` VARCHAR(45) NOT NULL, `VatRegistration` VARCHAR(45) NOT NULL, `CompanyNumber` VARCHAR(45) NOT NULL, `YearStartMonth` INT NOT NULL DEFAULT 1, `YearStartDay` INT NOT NULL DEFAULT 0, `TermsDays` INT NOT NULL DEFAULT 14, `NextInvoiceNumber` INT NOT NULL DEFAULT 1, `NextBillNumber` INT NOT NULL DEFAULT 1, `NextJournalNumber` INT NOT NULL DEFAULT 1, `DatabaseLogging` INT NOT NULL DEFAULT 0, PRIMARY KEY (`idSettings`), INDEX `fk_Settings_Account1_idx` (`DefaultBankAccount` ASC), CONSTRAINT `fk_Settings_Account1` FOREIGN KEY (`DefaultBankAccount`) REFERENCES `accounts`.`Account` (`idAccount`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `accounts`.`AuditTrail` -- ----------------------------------------------------- DROP TABLE IF EXISTS `accounts`.`AuditTrail` ; CREATE TABLE IF NOT EXISTS `accounts`.`AuditTrail` ( `idAuditTrail` INT NOT NULL AUTO_INCREMENT, `DateChanged` DATETIME NOT NULL, `TableName` VARCHAR(45) NOT NULL, `ChangeType` INT NOT NULL, `RecordId` INT NOT NULL, `Record` TEXT NOT NULL, PRIMARY KEY (`idAuditTrail`), INDEX `DateIndex` (`DateChanged` ASC, `idAuditTrail` ASC), INDEX `RecordIndex` (`TableName` ASC, `RecordId` ASC, `ChangeType` ASC, `DateChanged` ASC)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `accounts`.`Report` -- ----------------------------------------------------- DROP TABLE IF EXISTS `accounts`.`Report` ; CREATE TABLE IF NOT EXISTS `accounts`.`Report` ( `idReport` INT NOT NULL AUTO_INCREMENT, `ReportName` VARCHAR(45) NOT NULL, `ReportType` VARCHAR(45) NOT NULL, `ReportSettings` TEXT NOT NULL, `ReportGroup` VARCHAR(45) NOT NULL, PRIMARY KEY (`idReport`), UNIQUE INDEX `Name_Index` (`ReportGroup` ASC, `ReportName` ASC)) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `accounts`.`Payments` -- ----------------------------------------------------- DROP TABLE IF EXISTS `accounts`.`Payments` ; CREATE TABLE IF NOT EXISTS `accounts`.`Payments` ( `idPayment` INT NOT NULL, `idPaid` INT NOT NULL, `PaymentAmount` DECIMAL(10) NOT NULL DEFAULT 0, INDEX `PK` (`idPayment` ASC, `idPaid` ASC), INDEX `fk_Payments_Document2_idx` (`idPaid` ASC), CONSTRAINT `fk_Payments_Document1` FOREIGN KEY (`idPayment`) REFERENCES `accounts`.`Document` (`idDocument`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_Payments_Document2` FOREIGN KEY (`idPaid`) REFERENCES `accounts`.`Document` (`idDocument`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; USE `accounts` ; -- ----------------------------------------------------- -- Placeholder table for view `accounts`.`Extended_Document` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `accounts`.`Extended_Document` (`idDocument` INT, `DocumentMemo` INT, `DocumentTypeId` INT, `DocType` INT, `Sign` INT, `DocumentDate` INT, `DocumentNameAddressId` INT, `DocumentName` INT, `DocumentAddress` INT, `DocumentIdentifier` INT, `AccountingAmount` INT, `AccountingOutstanding` INT, `DocumentAmount` INT, `DocumentOutstanding` INT, `DocumentAccountId` INT, `DocumentAccountName` INT, `Cleared` INT, `DocumentVatAmount` INT, `VatPaid` INT); -- ----------------------------------------------------- -- Placeholder table for view `accounts`.`Extended_Line` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `accounts`.`Extended_Line` (`idLine` INT, `Qty` INT, `ProductId` INT, `LineAmount` INT, `VatCodeId` INT, `VatRate` INT, `VatAmount` INT, `ProductName` INT, `UnitPrice` INT, `Code` INT, `VatDescription` INT, `DocumentId` INT, `JournalNum` INT, `Memo` INT, `AccountId` INT, `AccountName` INT, `AccountDescription` INT); -- ----------------------------------------------------- -- Placeholder table for view `accounts`.`Vat_Journal` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `accounts`.`Vat_Journal` (`VatType` INT, `Memo` INT, `VatCodeId` INT, `VatRate` INT, `VatAmount` INT, `LineAmount` INT); -- ----------------------------------------------------- -- View `accounts`.`Extended_Document` -- ----------------------------------------------------- DROP VIEW IF EXISTS `accounts`.`Extended_Document` ; DROP TABLE IF EXISTS `accounts`.`Extended_Document`; USE `accounts`; CREATE OR REPLACE VIEW `Extended_Document` AS SELECT idDocument, DocumentMemo, DocumentTypeId, DocType, Sign, DocumentDate, Journal.NameAddressId AS DocumentNameAddressId, Name AS DocumentName, DocumentAddress, DocumentIdentifier, Journal.Amount As AccountingAmount, Journal.Outstanding As AccountingOutstanding, -Journal.Amount * DocumentType.Sign As DocumentAmount, -Journal.Outstanding * DocumentType.Sign As DocumentOutstanding, Journal.AccountId AS DocumentAccountId, AccountName As DocumentAccountName, Journal.Cleared, VatJournal.Amount * DocumentType.Sign As DocumentVatAmount, VatPaid FROM Document JOIN DocumentType ON idDocumentType = DocumentTypeId JOIN Journal ON Journal.DocumentId = idDocument AND Journal.JournalNum = 1 JOIN Account ON idAccount = Journal.AccountId JOIN NameAddress ON idNameAddress = NameAddressId LEFT JOIN Journal AS VatJournal ON VatJournal.DocumentId = idDocument AND VatJournal.AccountId = 8 ; -- ----------------------------------------------------- -- View `accounts`.`Extended_Line` -- ----------------------------------------------------- DROP VIEW IF EXISTS `accounts`.`Extended_Line` ; DROP TABLE IF EXISTS `accounts`.`Extended_Line`; USE `accounts`; CREATE OR REPLACE VIEW `Extended_Line` AS SELECT Line.*, Product.ProductName, Product.UnitPrice, VatCode.Code, VatCode.VatDescription, Journal.DocumentId, Journal.JournalNum, Journal.Memo, Journal.AccountId, Account.AccountName, Account.AccountDescription FROM Line JOIN Product ON Product.idProduct = Line.ProductId JOIN VatCode ON VatCode.idVatCode = Line.VatCodeId JOIN Journal ON Journal.idJournal = Line.idLine JOIN Account ON Account.idAccount = Journal.AccountId ; -- ----------------------------------------------------- -- View `accounts`.`Vat_Journal` -- ----------------------------------------------------- DROP VIEW IF EXISTS `accounts`.`Vat_Journal` ; DROP TABLE IF EXISTS `accounts`.`Vat_Journal`; USE `accounts`; CREATE OR REPLACE VIEW `Vat_Journal` AS SELECT Extended_Document.*, CASE WHEN DocumentAccountId = 1 THEN -1 WHEN DocumentAccountId = 2 THEN 1 WHEN DocumentAmount * Sign < 0 THEN -1 ELSE 1 END AS VatType, Memo, Line.VatCodeId, Line.VatRate, SUM(Line.VatAmount) VatAmount, SUM(Line.LineAmount) AS LineAmount FROM Extended_Document JOIN Journal ON IdDocument = DocumentId JOIN Line ON idLine = idJournal WHERE DocumentTypeId IN (1, 3, 4, 6) OR Line.VatCodeId IS NOT NULL GROUP BY idDocument, Line.VatCodeId, Line.VatRate ; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;