Changeset 802

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.

Location:

trunk

Files:

• Application/UpdateTrace.php (modified) (3 diffs)
• Application/Version.php (modified) (1 diff)
• Modules/Finance/Finance.php (modified) (1 diff)
• Modules/Stock/Stock.php (modified) (9 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'),
     ));
   }

trunk/Application/Version.php

@@ -1 +1 @@
 <?php
 
-$Revision = 801; // Subversion revision
-$DatabaseRevision = 800; // SQL structure revision
-$ReleaseTime = strtotime('2016-02-22');
+$Revision = 802; // Subversion revision
+$DatabaseRevision = 802; // SQL structure revision
+$ReleaseTime = strtotime('2016-02-23');

trunk/Modules/Finance/Finance.php

@@ -621 +621 @@
   function BeforeInsertFinanceInvoice($Form)
   {
+    // Get new DocumentLineCode by selected invoice Group
     if(array_key_exists('Time', $Form->Values)) $Year = date("Y", $Form->Values['Time']);
       else $Year = date("Y", $Form->Values['ValidFrom']);
-    $FinanceGroup = $this->System->Modules['Finance']->GetFinanceGroupById($Form->Values['Group'], 'FinanceInvoiceGroup');
-    $Form->Values['BillCode'] = $this->System->Modules['Finance']->GetNextDocumentLineNumberId($FinanceGroup['DocumentLine'], $Year);
+    $Group = $this->System->Modules['Finance']->GetFinanceGroupById($Form->Values['Group'], 'FinanceInvoiceGroup');
+    $Form->Values['BillCode'] = $this->System->Modules['Finance']->GetNextDocumentLineNumberId($Group['DocumentLine'], $Year);
     return($Form->Values);
   }

trunk/Modules/Stock/Stock.php

@@ -16 +16 @@
   function DoStart()
   {
+    global $Config;
+   
     $this->System->FormManager->RegisterClass('Product', array(
       'Title' => 'Produkty',
@@ -21 +23 @@
       'DefaultSortColumn' => 'Name',
       'Items' => array(
+        'Manufacturer' => array('Type' => 'TSubject', 'Caption' => 'Výrobce', 'Default' => '', 'Null' => true),
+        'Code' => array('Type' => 'String', 'Caption' => 'Kód', 'Default' => ''),
         'Name' => array('Type' => 'String', 'Caption' => 'Jméno', 'Default' => ''),
         'SellPrice' => array('Type' => 'Integer', 'Caption' => 'Prodejní cena', 'Default' => '0', 'Suffix' => 'Kč'),
@@ -27 +31 @@
         'Consumption' => array('Type' => 'Integer', 'Caption' => 'Spotřeba', 'Default' => '', 'Suffix' => 'Watt'),
         'Supplier' => array('Type' => 'TSubject', 'Caption' => 'Dodavatel', 'Default' => '', 'Null' => true),
-        'Manufacturer' => array('Type' => 'TSubject', 'Caption' => 'Výrobce', 'Default' => '', 'Null' => true),
-        'Code' => array('Type' => 'String', 'Caption' => 'Kód', 'Default' => ''),
         'UnitOfMeasure' => array('Type' => 'TUnitOfMeasure', 'Caption' => 'Měrná jednotka', 'Default' => '', 'Null' => true),
         'StockSerialNumbers' => array('Type' => 'TStockSerialNumberListProduct', 'Caption' => 'Položky na skladě', 'Default' => '', 'Suffix' => 'ks'),
@@ -79 +81 @@
       'DefaultSortColumn' => 'Time',
       'Items' => array(
-        'DocumentLine' => array('Type' => 'TDocumentLine', 'Caption' => 'Dokladová řada', 'Default' => ''),
-        'BillCode' => array('Type' => 'String', 'Caption' => 'Označení', 'Default' => ''),
+        'Group' => array('Type' => 'TStockMoveGroup', 'Caption' => 'Skupina', 'Default' => ''),
+        'BillCode' => array('Type' => 'TDocumentLineCode', 'Caption' => 'Označení', 'Default' => '', 'ReadOnly' => true),
         'Time' => array('Type' => 'DateTime', 'Caption' => 'Čas', 'Default' => ''),
-        'StockFrom' => array('Type' => 'TStock', 'Caption' => 'Zdrojový sklad', 'Default' => '', 'Null' => true),
-        'StockTo' => array('Type' => 'TStock', 'Caption' => 'Cílový sklad', 'Default' => '', 'Null' => true),
+        'Stock' => array('Type' => 'TStock', 'Caption' => 'Sklad', 'Default' => '', 'Null' => true),
         'File' => array('Type' => 'TFile', 'Caption' => 'Doklad', 'Default' => '', 'Null' => true),
         'Price' => array('Type' => 'Float', 'Caption' => 'Cena', 'Default' => '',
@@ -91 +92 @@
       ),
       'BeforeInsert' => array($this, 'BeforeInsertStockMove'),
-    ));
-    $this->System->FormManager->RegisterClass('StockMoveUnion', array(
-      'Title' => 'Skladový pohyb',
-      'BaseTable' => 'StockMove',
-      'DefaultSortColumn' => 'Time',
-      'SQL' => '(SELECT *, -1 AS `Direction`, `StockFrom` AS `Stock`, `StockTo` AS `StockOther` FROM `StockMove`) '.
-        'UNION (SELECT *, 1 AS `Direction`, `StockTo` AS `Stock`, `StockFrom` AS `StockOther` FROM `StockMove`)',
-      'Items' => array(
-        'DocumentLine' => array('Type' => 'TDocumentLine', 'Caption' => 'Dokladová řada', 'Default' => ''),
-        'BillCode' => array('Type' => 'String', 'Caption' => 'Označení', 'Default' => ''),
-        'Time' => array('Type' => 'DateTime', 'Caption' => 'Čas', 'Default' => ''),
-        'Stock' => array('Type' => 'TStock', 'Caption' => 'První sklad', 'Default' => '', 'Null' => true),
-        'StockOther' => array('Type' => 'TStock', 'Caption' => 'Druhý sklad', 'Default' => '', 'Null' => true),
-        'File' => array('Type' => 'TFile', 'Caption' => 'Doklad', 'Default' => '', 'Null' => true),
-        'Price' => array('Type' => 'Float', 'Caption' => 'Cena', 'Default' => '',
-          'ReadOnly' => true, 'Suffix' => 'Kč', 'SQL' => '(SELECT `TX`.`Direction` * SUM(`StockMoveItem`.`UnitPrice` * `StockMoveItem`.`Amount`) FROM `StockMoveItem` '.
-          'WHERE `StockMoveItem`.`StockMove`=#Id)'),
-        'Items' => array('Type' => 'TStockMoveItemListStockMove', 'Caption' => 'Položky', 'Default' => ''),
-      ),
-      'BeforeInsert' => array($this, 'BeforeInsertStockMove'),
+      //'AfterInsert' => array($this, 'AfterInsertStockMove'),
+      //'BeforeModify' => array($this, 'BeforeModifyStockMove'),
     ));
     $this->System->FormManager->RegisterClass('StockMoveItem', array(
       'Title' => 'Položka skladového pohybu',
       'Table' => 'StockMoveItem',
-      'DefaultSortColumn' => 'Text',
+      'DefaultSortColumn' => 'Product',
       'Items' => array(
         'StockMove' => array('Type' => 'TStockMove', 'Caption' => 'Skladový pohyb', 'Default' => ''),
         'Product' => array('Type' => 'TProduct', 'Caption' => 'Produkt', 'Default' => ''),
+        'UnitPrice' => array('Type' => 'Integer', 'Caption' => 'Jednotková cena', 'Default' => '0', 'Suffix' => 'Kč'),
         'Amount' => array('Type' => 'Integer', 'Caption' => 'Množství', 'Default' => '1'),
-        'Text' => array('Type' => 'String', 'Caption' => 'Text', 'Default' => ''),
-        'UnitPrice' => array('Type' => 'Integer', 'Caption' => 'Jednotková cena', 'Default' => '0', 'Suffix' => 'Kč'),
+        'Total' => array('Type' => 'Integer', 'Caption' => 'Celkem', 'Default' => '', 'Suffix' => 'Kč',
+          'ReadOnly' => true, 'SQL' => 'ROUND(`UnitPrice` * `Amount`, '.$Config['Finance']['Rounding'].')'),
+      ),
+    ));
+    $this->System->FormManager->RegisterClass('StockMoveGroup', array(
+      'Title' => 'Skupina skladových pohybů',
+      'Table' => 'StockMoveGroup',
+      'Items' => array(
+        'Name' => array('Type' => 'String', 'Caption' => 'Název', 'Default' => '0'),
+        'DocumentLine' => array('Type' => 'TDocumentLine', 'Caption' => 'Dokladová řada', 'Default' => '0'),
+        'ValueSign' => array('Type' => 'TFinanceValueSign', 'Caption' => 'Znaménko hodnoty', 'Default' => '0'),
+        'Direction' => array('Type' => 'TFinanceDirection', 'Caption' => 'Směr', 'Default' => '0'),
+        'Items' => array('Type' => 'TStockMoveListGroup', 'Caption' => 'Operace', 'Default' => ''),
       ),
     ));
@@ -148 +143 @@
       'Filter' => '1',
     ));
+    $this->System->FormManager->RegisterFormType('TStockMoveListGroup', array(
+      'Type' => 'ManyToOne',
+      'Table' => 'StockMove',
+      'Id' => 'Id',
+      'Ref' => 'Group',
+      'Filter' => '1',
+    ));
     $this->System->FormManager->RegisterFormType('TStockMoveStock', array(
       'Type' => 'ManyToOne',
-      'Table' => 'StockMoveUnion',
+      'Table' => 'StockMove',
       'Id' => 'Id',
       'Ref' => 'Stock',
@@ -159 +161 @@
       'Table' => 'StockMove',
       'Id' => 'Id',
-      'Name' => 'BillCode',
+      'Name' => '(SELECT `DocumentLineCode`.`Name` FROM `DocumentLineCode` WHERE `Id`=`StockMove`.`BillCode`)',
       'Filter' => '1',
     ));
@@ -172 +174 @@
       'Type' => 'Reference',
       'Table' => 'Stock',
+      'Id' => 'Id',
+      'Name' => 'Name',
+      'Filter' => '1',
+    ));
+    $this->System->FormManager->RegisterFormType('TStockMoveGroup', array(
+      'Type' => 'Reference',
+      'Table' => 'StockMoveGroup',
       'Id' => 'Id',
       'Name' => 'Name',
@@ -196 +205 @@
     if(array_key_exists('Time', $Form->Values)) $Year = date("Y", $Form->Values['Time']);
       else $Year = date("Y", $Form->Values['ValidFrom']);
-    $DocumentLine = $Form->Values['DocumentLine'];
-    $Form->Values['BillCode'] = $this->System->Modules['Finance']->GetNextDocumentLineNumber($DocumentLine, $Year);
+    $Group = $this->System->Modules['Finance']->GetFinanceGroupById($Form->Values['Group'], 'StockMoveGroup');
+    $Form->Values['BillCode'] = $this->System->Modules['Finance']->GetNextDocumentLineNumberId($Group['DocumentLine'], $Year);
     return($Form->Values);
   }