Changeset 740 for trunk/Common/Setup/Updates.php

Timestamp:

Jul 3, 2015, 11:36:07 PM (9 years ago)

Author:

chronos

Message:

• Modified: Changed Direction column of FinanceOperation and Invoices to more general ValueSign.
• FinanceOperationGroup and InvoiceGroup tables introduced and operations and invoices are now linked directly

to group id instead of DocumentLine.

File:

• trunk/Common/Setup/Updates.php (modified) (2 diffs)

trunk/Common/Setup/Updates.php

@@ -1354 +1354 @@
   `Address` varchar(255) NOT NULL,
   `Domain` int(11) NOT NULL,
-  `Sequence` int(11) NOT NULL
+  `Sequence` int(11) NOT NULL,
 ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;');
 
@@ -1362 +1362 @@
   $Manager->Execute('ALTER TABLE `NetworkDomainServer`
 ADD CONSTRAINT `NetworkDomainServer_ibfk_1` FOREIGN KEY (`Domain`) REFERENCES `NetworkDomain` (`Id`);');
-
+}
+
+function UpdateTo740($Manager)
+{
+  $Manager->Execute('CREATE TABLE IF NOT EXISTS `FinanceInvoiceGroup` (
+  `Id` int(11) NOT NULL AUTO_INCREMENT,
+  `Name` varchar(255) NOT NULL,
+  `DocumentLine` int(11) NOT NULL,
+  `ValueSign` int(11) NOT NULL,
+  PRIMARY KEY (`Id`),
+  KEY `DocumentLine` (`DocumentLine`)
+) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;');
+  $Manager->Execute('ALTER TABLE `FinanceInvoiceGroup`
+ADD CONSTRAINT `FinanceInvoiceGroup_ibfk_1` FOREIGN KEY (`DocumentLine`) REFERENCES `DocumentLine` (`Id`);');
+  $Manager->Execute('INSERT INTO `FinanceInvoiceGroup` (`Id`, `Name`, `DocumentLine`, `ValueSign`) VALUES
+(1, "Přijaté faktury", 5, -1),
+(2, "Vydané faktury", 6, 1);');
+
+  // Transform FinanceInvoice.DocumentLine reference to FinanceInvoiceGroup
+  $Manager->Execute('ALTER TABLE `FinanceInvoice` ADD `Group` INT NULL AFTER `Id`;');
+  $Manager->Execute('ALTER TABLE `FinanceInvoice` ADD INDEX(`Group`);');
+  $Manager->Execute('ALTER TABLE `FinanceInvoice` ADD FOREIGN KEY (`Group`) REFERENCES `FinanceInvoiceGroup`(`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT;');
+  $Manager->Execute('UPDATE `FinanceInvoice` SET `Group`=1 WHERE `DocumentLine`=5');
+  $Manager->Execute('UPDATE `FinanceInvoice` SET `Group`=2 WHERE `DocumentLine`=6');
+  $Manager->Execute('ALTER TABLE `FinanceInvoice` DROP FOREIGN KEY `FinanceInvoice_ibfk_3`;');
+  $Manager->Execute('ALTER TABLE `FinanceInvoice` DROP `DocumentLine`;');
+
+  $Manager->Execute('CREATE TABLE IF NOT EXISTS `FinanceOperationGroup` (
+  `Id` int(11) NOT NULL AUTO_INCREMENT,
+  `Name` varchar(255) NOT NULL,
+  `DocumentLine` int(11) NOT NULL,
+  `ValueSign` int(11) NOT NULL,
+  PRIMARY KEY (`Id`),
+  KEY `DocumentLine` (`DocumentLine`)
+) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;');
+  $Manager->Execute('ALTER TABLE `FinanceOperationGroup`
+ADD CONSTRAINT `FinanceOperationGroup_ibfk_1` FOREIGN KEY (`DocumentLine`) REFERENCES `DocumentLine` (`Id`);');
+  $Manager->Execute('INSERT INTO `FinanceOperationGroup` (`Id`, `Name`, `DocumentLine`, `ValueSign`) VALUES
+(1, "Příjem do pokladny", 1, 1),
+(2, "Výdej z pokladny", 2, -1),
+(3, "Příjem na účet", 3, 1),
+(4, "Výdej z účtu", 4, -1),
+(5, "Nezařazené (ručně vyčistit)", 1, 1);');
+
+  // Transform FinanceOperation.DocumentLine reference to FinanceOperationGroup
+  $Manager->Execute('ALTER TABLE `FinanceOperation` ADD `Group` INT NULL AFTER `Id`;');
+  $Manager->Execute('ALTER TABLE `FinanceOperation` ADD INDEX (`Group`);');
+  $Manager->Execute('ALTER TABLE `FinanceOperation` ADD FOREIGN KEY (`Group`) REFERENCES `FinanceOperationGroup`(`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT;');
+  $Manager->Execute('UPDATE `FinanceOperation` SET `Group`=1 WHERE `DocumentLine`=1');
+  $Manager->Execute('UPDATE `FinanceOperation` SET `Group`=2 WHERE `DocumentLine`=2');
+  $Manager->Execute('UPDATE `FinanceOperation` SET `Group`=3 WHERE `DocumentLine`=3');
+  $Manager->Execute('UPDATE `FinanceOperation` SET `Group`=4 WHERE `DocumentLine`=4');
+  $Manager->Execute('UPDATE `FinanceOperation` SET `Group`=5 WHERE `DocumentLine`=10');
+  $Manager->Execute('UPDATE `FinanceOperation` SET `Group`=5 WHERE `DocumentLine`=5');
+  $Manager->Execute('ALTER TABLE `FinanceOperation` DROP FOREIGN KEY `FinanceOperation_ibfk_6`;');
+  $Manager->Execute('ALTER TABLE `FinanceOperation` DROP `DocumentLine`;');
+
+  $Manager->Execute('ALTER TABLE `FinanceInvoice` CHANGE `Direction` `ValueSign` INT(11) NOT NULL;');
+  $Manager->Execute('ALTER TABLE `FinanceOperation` CHANGE `Direction` `ValueSign` INT(11) NOT NULL;');
 }