Changeset 802 for trunk/Application/UpdateTrace.php

Timestamp:

Feb 23, 2016, 4:44:03 PM (9 years ago)

Author:

chronos

Message:

• Modified: StockMove now have single column for Stock and direction of move is given by Group column and StockMoveGroup Direction.
• Modified: StockMove BillCode made reference to DocumentLineCode.

File:

• trunk/Application/UpdateTrace.php (modified) (3 diffs)

trunk/Application/UpdateTrace.php

@@ -1826 +1826 @@
 {
   $Manager->Execute('CREATE TABLE IF NOT EXISTS `StockItemHistory` (
-    `Id` int(11) NOT NULL,
+    `Id` int(11) NOT NULL AUTO_INCREMENT,
     `StockSerialNumber` int(11) NOT NULL,
     `Time` datetime NOT NULL,
@@ -1838 +1838 @@
     '"" AS `Time`, `Info` AS `Text` FROM `StockSerialNumber` WHERE `Info` != "")');
   $Manager->Execute('ALTER TABLE `StockSerialNumber` DROP `Info`;');
+}
+
+function UpdateTo802($Manager)
+{
+  $Manager->Execute('CREATE TABLE IF NOT EXISTS `StockMoveGroup` (
+  `Id` int(11) NOT NULL AUTO_INCREMENT,
+  `Name` varchar(255) NOT NULL,
+  `DocumentLine` int(11) NOT NULL,
+  `ValueSign` int(11) NOT NULL,
+  `Direction` int(11) NOT NULL,
+  PRIMARY KEY (`Id`),
+  KEY `DocumentLine` (`DocumentLine`)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;');
+  $Manager->Execute('ALTER TABLE `StockMoveGroup` ADD FOREIGN KEY (`DocumentLine`) '.
+    'REFERENCES `DocumentLine`(`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT;');
+  $Manager->Execute('INSERT INTO `StockMoveGroup` (`Id`, `Name`, `DocumentLine`, '.
+    '`ValueSign`, `Direction`) VALUES (NULL, "Příjem", 8, 1, 0), (NULL, "Výdej", 7, -1, 1);');
+
+  $Manager->Execute('ALTER TABLE `StockMove` ADD `Group` INT NOT NULL AFTER `File`, ADD INDEX (`Group`);');
+  $Manager->Execute('ALTER TABLE `StockMove` ADD `Stock` INT NOT NULL AFTER `Time`, ADD INDEX (`Stock`);');
+  $Manager->Execute('ALTER TABLE `StockMove` ADD FOREIGN KEY (`Group`) REFERENCES `StockMoveGroup`(`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT;');
+  $Manager->Execute('UPDATE `StockMove` SET `Group`=(SELECT `Id` FROM `StockMoveGroup` WHERE `StockMoveGroup`.`DocumentLine` = `StockMove`.`DocumentLine`)');
+  $Manager->Execute('UPDATE `StockMove` SET `Stock`= `StockFrom` WHERE `DocumentLine` = 7');
+  $Manager->Execute('UPDATE `StockMove` SET `Stock`= `StockTo` WHERE `DocumentLine` = 8');
+  $Manager->Execute('ALTER TABLE `StockMove` DROP FOREIGN KEY StockMove_ibfk_2;');
+  $Manager->Execute('ALTER TABLE `StockMove` DROP FOREIGN KEY StockMove_ibfk_1;');
+  $Manager->Execute('ALTER TABLE `StockMove` DROP FOREIGN KEY StockMove_ibfk_3;');
+  $Manager->Execute('ALTER TABLE `StockMove` DROP `StockFrom`, DROP `StockTo`, DROP `DocumentLine`;');
+  $Manager->Execute('ALTER TABLE `StockMove` ADD FOREIGN KEY (`Stock`) REFERENCES `Stock`(`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT;');
+ 
+  // Make BillCode as reference to DocumentLineCode table
+  $Manager->Execute('ALTER TABLE `StockMove` CHANGE `BillCode` `BillCodeText` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL');
+  $Manager->Execute('ALTER TABLE `StockMove` ADD `BillCode` INT NULL AFTER `BillCodeText`, ADD INDEX (`BillCode`)');
+  $Manager->Execute('ALTER TABLE `StockMove` ADD FOREIGN KEY (`BillCode`) REFERENCES `DocumentLineCode`(`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT;');
+
+  $DbResult = $Manager->Execute('SELECT `StockMove`.`Id`, `StockMove`.`BillCodeText`, `StockMoveGroup`.`DocumentLine` '.
+    'FROM `StockMove` '.
+    'LEFT JOIN `StockMoveGroup` ON `StockMoveGroup`.`Id` = `StockMove`.`Group` '.
+    'WHERE `StockMove`.`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 `StockMove` SET `BillCode`='.$CodeId.' WHERE `Id`='.$DbRow['Id']);
+  }
+  $Manager->Execute('ALTER TABLE `StockMove` DROP `BillCodeText`;');
+  $Manager->Execute('ALTER TABLE `StockMoveItem` DROP `Text`;');
 }
 
@@ -1924 +1973 @@
       786 => array('Revision' => 792, 'Function' => 'UpdateTo792'),
       792 => array('Revision' => 800, 'Function' => 'UpdateTo800'),
+      800 => array('Revision' => 802, 'Function' => 'UpdateTo802'),
     ));
   }