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

Timestamp:

Jul 7, 2015, 12:12:12 AM (9 years ago)

Author:

chronos

Message:

• Modified: Partialy converted test BillCode column to table DocumentLineCode reference for FinanceInvoice and FinanceOperation.

File:

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

trunk/Common/Setup/Updates.php

@@ -1435 +1435 @@
   $Manager->Execute('UPDATE `FinanceInvoice` SET `Direction` = 1 WHERE ValueSign=1');
 }
+
+function UpdateTo742($Manager)
+{
+  $Manager->Execute('CREATE TABLE IF NOT EXISTS `DocumentLineCode` (
+  `Id` int(11) NOT NULL AUTO_INCREMENT,
+  `DocumentLine` int(11) NULL,
+  `Name` varchar(255) NOT NULL,
+        PRIMARY KEY (`Id`),
+        UNIQUE KEY `Name` (`Name`),
+  KEY `DocumentLine` (`DocumentLine`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;');
+  $Manager->Execute('ALTER TABLE `DocumentLineCode`
+ADD CONSTRAINT `DocumentLineCode_ibfk_1` FOREIGN KEY (`DocumentLine`) REFERENCES `DocumentLine` (`Id`);');
+  
+  // Transform finance operations
+  $Manager->Execute('ALTER TABLE `FinanceOperation` CHANGE `BillCode` `BillCodeText` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL');
+  $Manager->Execute('ALTER TABLE `FinanceOperation` ADD `BillCode` INT NULL AFTER `BillCodeText`, ADD INDEX (`BillCode`)');
+  $Manager->Execute('ALTER TABLE `FinanceOperation` ADD FOREIGN KEY (`BillCode`) REFERENCES `DocumentLineCode`(`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT;');
+  $DbResult = $Manager->Execute('SELECT `FinanceOperation`.`Id`, `FinanceOperation`.`BillCodeText`, `FinanceOperationGroup`.`DocumentLine` '.
+    'FROM `FinanceInvoice` '.
+    'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id` = `FinanceOperation`.`Group` '.
+    'WHERE `FinanceOperation`.`BillCodeText`!=""');
+  while($DbRow = $DbResult->fetch_assoc())
+  {
+    if($DbRow['DocumentLine'] == '') $DbRow['DocumentLine'] = 'NULL';
+    $Manager->Execute('INSERT INTO `DocumentLineCode` (`Id` ,`DocumentLine` ,`Name`) '.
+      'VALUES (NULL , '.$DbRow['DocumentLine'].', "'.$DbRow['BillCodeText'].'");');
+    $CodeId = $Manager->Database->insert_id;
+    $Manager->Execute('UPDATE `FinanceOperation` SET `BillCode`='.$CodeId.' WHERE `Id`='.$DbRow['Id']);
+  }
+  $Manager->Execute('ALTER TABLE `FinanceOperation` DROP `BillCodeText`;');
+  
+  // Transform invoices
+  $Manager->Execute('ALTER TABLE `FinanceInvoice` CHANGE `BillCode` `BillCodeText` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL');
+  $Manager->Execute('ALTER TABLE `FinanceInvoice` ADD `BillCode` INT NULL AFTER `BillCodeText`, ADD INDEX (`BillCode`)');
+  $Manager->Execute('ALTER TABLE `FinanceInvoice` ADD FOREIGN KEY (`BillCode`) REFERENCES `DocumentLineCode`(`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT;');
+  $DbResult = $Manager->Execute('SELECT `FinanceInvoice`.`Id`, `FinanceInvoice`.`BillCodeText`, `FinanceInvoiceGroup`.`DocumentLine` '.
+    'FROM `FinanceInvoice` '.
+    'LEFT JOIN `FinanceInvoiceGroup` ON `FinanceInvoiceGroup`.`Id` = `FinanceInvoice`.`Group` '.
+    'WHERE `FinanceInvoice`.`BillCodeText`!=""');
+  while($DbRow = $DbResult->fetch_assoc())
+  {
+    if($DbRow['DocumentLine'] == '') $DbRow['DocumentLine'] = 'NULL';
+        $Manager->Execute('INSERT INTO `DocumentLineCode` (`Id` ,`DocumentLine` ,`Name`) '.
+      'VALUES (NULL , '.$DbRow['DocumentLine'].', "'.$DbRow['BillCodeText'].'");');
+    $CodeId = $Manager->Database->insert_id;
+    $Manager->Execute('UPDATE `FinanceInvoice` SET `BillCode`='.$CodeId.' WHERE `Id`='.$DbRow['Id']);
+  }
+  $Manager->Execute('ALTER TABLE `FinanceInvoice` DROP `BillCodeText`;');
+  
+        //$Manager->Execute('RENAME TABLE `FinanceOperationGroup` TO `FinanceOperationTemplate`');
+        //$Manager->Execute('RENAME TABLE `FinanceInvoiceGroup` TO `FinanceInvoiceTemplate`');
+}
+
 
 class Updates
@@ -1503 +1557 @@
       735 => array('Revision' => 736, 'Function' => 'UpdateTo736'),
       736 => array('Revision' => 739, 'Function' => 'UpdateTo739'),
-        739 => array('Revision' => 740, 'Function' => 'UpdateTo740'),
-        740 => array('Revision' => 741, 'Function' => 'UpdateTo741'),
+          739 => array('Revision' => 740, 'Function' => 'UpdateTo740'),
+      740 => array('Revision' => 741, 'Function' => 'UpdateTo741'),
+      741 => array('Revision' => 742, 'Function' => 'UpdateTo742'),
     ));
   }