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

Timestamp:

Dec 29, 2014, 12:09:46 AM (10 years ago)

Author:

chronos

Message:

• Modified: Table StockItem renamed to StockSerialNumber.
• Added: Preparation for improvement of stock handling. Stock items change should be covered by tables StockMove and StockMoveItem. Each StockMove have to have BillCode identification accoroding stock move document line.

File:

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

trunk/Common/Setup/Updates.php

@@ -914 +914 @@
 function UpdateTo696($Manager)
 {
-        $Manager->Execute('ALTER TABLE `NetworkInterfaceUpDown` ADD `Duration` INT NOT NULL ;');
-        $Manager->Execute('UPDATE `NetworkInterfaceUpDown` AS `TM` SET `Duration` = IFNULL(TIMESTAMPDIFF(SECOND,
-    (SELECT `Time` FROM (SELECT * FROM `NetworkInterfaceUpDown`) AS `TA` WHERE (`TA`.`Time` < `TM`.`Time`)
-    AND (`TA`.`Interface`=`TM`.`Interface`) ORDER BY `TA`.`Time` DESC LIMIT 1), `TM`.`Time`), 0)');
+  $Manager->Execute('ALTER TABLE `NetworkInterfaceUpDown` ADD `Duration` INT NOT NULL ;');
+  $Manager->Execute('UPDATE `NetworkInterfaceUpDown` AS `TM` SET `Duration` = IFNULL(TIMESTAMPDIFF(SECOND,
+  (SELECT `Time` FROM (SELECT * FROM `NetworkInterfaceUpDown`) AS `TA` WHERE (`TA`.`Time` < `TM`.`Time`)
+  AND (`TA`.`Interface`=`TM`.`Interface`) ORDER BY `TA`.`Time` DESC LIMIT 1), `TM`.`Time`), 0)');
 }
 
 function UpdateTo697($Manager)
 {
-        $Manager->Execute('ALTER TABLE `NetworkInterfaceUpDown` CHANGE `Duration` `Duration` INT( 11 ) NULL ;');
-        $Manager->Execute('UPDATE `NetworkInterfaceUpDown` SET `Duration` = NULL');
-        $Manager->Execute('UPDATE `NetworkInterfaceUpDown` AS `TM` SET `Duration` = TIMESTAMPDIFF(SECOND,
-    `TM`.`Time`, (SELECT `Time` FROM (SELECT * FROM `NetworkInterfaceUpDown`) AS `TA` WHERE (`TA`.`Time` > `TM`.`Time`)
-    AND (`TA`.`Interface`=`TM`.`Interface`) ORDER BY `TA`.`Time` ASC LIMIT 1))');
+  $Manager->Execute('ALTER TABLE `NetworkInterfaceUpDown` CHANGE `Duration` `Duration` INT( 11 ) NULL ;');
+  $Manager->Execute('UPDATE `NetworkInterfaceUpDown` SET `Duration` = NULL');
+  $Manager->Execute('UPDATE `NetworkInterfaceUpDown` AS `TM` SET `Duration` = TIMESTAMPDIFF(SECOND,
+  `TM`.`Time`, (SELECT `Time` FROM (SELECT * FROM `NetworkInterfaceUpDown`) AS `TA` WHERE (`TA`.`Time` > `TM`.`Time`)
+  AND (`TA`.`Interface`=`TM`.`Interface`) ORDER BY `TA`.`Time` ASC LIMIT 1))');
 }
 
 function UpdateTo707($Manager)
 {
-        $Manager->Execute('ALTER TABLE `NetworkDevice` CHANGE `Product` `Product` INT(11) NULL;');
+  $Manager->Execute('ALTER TABLE `NetworkDevice` CHANGE `Product` `Product` INT(11) NULL;');
+}
+
+function UpdateTo710($Manager)
+{       
+  $Manager->Execute('RENAME TABLE `StockItem` TO `StockSerialNumber`;');
+  $Manager->Execute('UPDATE `Action` SET `URL`="/is/?t=StockSerialNumber&a=list",`Title`="Sériová čísla" WHERE `URL`="/is/?t=StockItem&a=list";');
+  
+  // StockMove
+  $Manager->Execute('CREATE TABLE IF NOT EXISTS `StockMove` (
+  `Id` int(11) NOT NULL,
+  `Time` datetime NOT NULL,
+  `StockFrom` int(11) DEFAULT NULL,
+  `StockTo` int(11) DEFAULT NULL,
+  `BillCode` varchar(255) NOT NULL,
+  `DocumentLine` int(11) NOT NULL,
+  `File` int(11) DEFAULT NULL
+  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;');
+    
+  $Manager->Execute('ALTER TABLE `StockMove`
+  ADD PRIMARY KEY (`Id`), ADD KEY `DocumentLine` (`DocumentLine`), ADD KEY `StockFrom` (`StockFrom`), ADD KEY `StockTo` (`StockTo`), ADD KEY `File` (`File`);');
+    
+  $Manager->Execute('ALTER TABLE `StockMove`
+  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT;');
+  
+  $Manager->Execute('ALTER TABLE `StockMove`
+  ADD CONSTRAINT `StockMove_ibfk_1` FOREIGN KEY (`StockFrom`) REFERENCES `Stock` (`Id`),
+  ADD CONSTRAINT `StockMove_ibfk_2` FOREIGN KEY (`StockTo`) REFERENCES `Stock` (`Id`),
+  ADD CONSTRAINT `StockMove_ibfk_3` FOREIGN KEY (`DocumentLine`) REFERENCES `DocumentLine` (`Id`);');
+  
+  // StockMoveItem
+  $Manager->Execute('CREATE TABLE IF NOT EXISTS `StockMoveItem` (
+  `Id` int(11) NOT NULL,
+  `StockMove` int(11) NOT NULL,
+  `Product` int(11) NOT NULL,
+  `Amount` int(11) NOT NULL,
+  `Text` varchar(255) NOT NULL,
+  `UnitPrice` int(11) NOT NULL
+  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;');
+  
+  $Manager->Execute('ALTER TABLE `StockMoveItem`
+  ADD PRIMARY KEY (`Id`), ADD KEY `Product` (`Product`), ADD KEY `StockMove` (`StockMove`);');
+  
+  $Manager->Execute('ALTER TABLE `StockMoveItem`
+  MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT;');
+
+  $Manager->Execute('ALTER TABLE `StockMoveItem`
+  ADD CONSTRAINT `StockMoveItem_ibfk_2` FOREIGN KEY (`Product`) REFERENCES `Product` (`Id`),
+  ADD CONSTRAINT `StockMoveItem_ibfk_1` FOREIGN KEY (`StockMove`) REFERENCES `StockMove` (`Id`);');  
+
+  // IS menu item
+  $Manager->Execute('INSERT INTO `Action` (`Id` ,`Name` ,`Title` ,`Type` ,`URL` ,
+`Group` ,`Icon` ,`PermissionOperation` ,`Enable`) VALUES (
+NULL , "", "Skladové pohyby", "1", "/is/?t=StockMove&a=list", NULL , NULL , NULL , "1");');
+  $ActionId = $Manager->Database->insert_id;
+  $DbResult = $Manager->Execute('SELECT `Id` FROM `MenuItem` WHERE `Name`="Sklad"');
+  if($DbResult->num_rows > 0)
+  {
+        $DbRow = $DbResult->fetch_assoc();
+    $Manager->Execute("INSERT INTO `MenuItem` (`Id` ,`Name` ,`Parent` ,`Action` ,`Menu`) ".
+      "VALUES (NULL , 'Skladové pohyby', ".$DbRow['Id'].", '".$ActionId."', '1');");
+  }
 }
 
@@ -987 +1048 @@
       696 => array('Revision' => 697, 'Function' => 'UpdateTo697'),
       697 => array('Revision' => 707, 'Function' => 'UpdateTo707'),
+      707 => array('Revision' => 710, 'Function' => 'UpdateTo710'),
     ));
   }