Changeset 451

Timestamp:

Oct 24, 2012, 10:42:58 PM (12 years ago)

Author:

chronos

Message:

• Upraveno: Korekce tabulek Product a StockItem.

Location:

trunk

Files:

• finance/finance.php (modified) (2 diffs)
• finance/index.php (modified) (1 diff)
• form_classes.php (modified) (2 diffs)
• sql/updates/446.sql (modified) (2 diffs)
• temp/product.php (modified) (1 diff)

trunk/finance/finance.php

@@ -170 +170 @@
       $Cash = $Cash[0];
     
-      $DbResult2 = $this->Database->query('SELECT SUM(Consumption) FROM Product WHERE (User='.$Member['Id'].') AND (Used = 1)');
+      $DbResult2 = $this->Database->query('SELECT SUM(`Product`.`Consumption`) * `StockItem`.`Amount` '.
+        'FROM `StockItem` JOIN `Product` ON `Product`.`Id` = `StockItem`.`Product` '.
+        'WHERE (`StockItem`.`Location` = '.$Member['Id'].') AND (`StockItem`.`TimeElimination` IS NULL)');
       $ConsumptionPlus = $DbResult2->fetch_row();
       $ConsumptionPlus = $ConsumptionPlus[0];
@@ -232 +234 @@
       $RowP2 = $DbResult2->fetch_array();
 
-      $DbResult2 = $this->Database->query('SELECT SUM(Price) AS Price, SUM(Consumption) AS Consumption FROM Product WHERE Segment='.$NetworkSegment['Id'].' AND Used=1');
+      $DbResult2 = $this->Database->query('SELECT SUM(`Product`.`BuyPrice`) * `StockItem`.`Amount` AS `Price`, '.
+        'SUM(`Product`.`Consumption`) * `StockItem`.`Amount` AS `Consumption` '.
+        'FROM `StockItem` JOIN `Product` ON `Product`.`Id` = `StockItem`.`Product` '.
+        'WHERE (`StockItem`.`Segment`='.$NetworkSegment['Id'].') AND (`StockItem`.`TimeElimination` IS NULL)');
       $Row2 = $DbResult2->fetch_array();
       $DbResult2 = $this->Database->query('SELECT COUNT(*) FROM Member WHERE NetworkSegment='.$NetworkSegment['Id']);

trunk/finance/index.php

@@ -57 +57 @@
     $TotalGain = 0;
     $TotalExpense = 0;
-    $DbResult = $this->Database->query('SELECT SUM(Consumption) FROM Product WHERE Used=1');
+    $DbResult = $this->Database->query('SELECT SUM(Product.Consumption) * StockItem.Amount FROM StockItem '.
+      'JOIN Product ON Product.Id=StockItem.Product WHERE (StockItem.TimeElimination IS NULL)');
     $Row = $DbResult->fetch_array();
     $TotalConsumption = $this->System->Modules['Finance']->W2Kc($Row[0]);

trunk/form_classes.php

@@ -110 +110 @@
     'Items' => array(
       'Name' => array('Type' => 'String', 'Caption' => 'Jméno', 'Default' => ''),
-      'SellPrice' => array('Type' => 'Integer', 'Caption' => 'Cena', 'Default' => '0'),
+      'SellPrice' => array('Type' => 'Integer', 'Caption' => 'Prodejní cena', 'Default' => '0'),
+      'BuyPrice' => array('Type' => 'Integer', 'Caption' => 'Kupní cena', 'Default' => '0'),
       'VAT' => array('Type' => 'Integer', 'Caption' => 'DPH', 'Default' => '0'),
       'Consumption' => array('Type' => 'Integer', 'Caption' => 'Spotřeba', 'Default' => ''),
@@ -133 +134 @@
       'Location' => array('Type' => 'TMember', 'Caption' => 'Umístění', 'Default' => '', 'Null' => true),
       'Segment' => array('Type' => 'TNetworkSegment', 'Caption' => 'Úsek sítě', 'Default' => '', 'Null' => true),
+      'Info' => array('Type' => 'Text', 'Caption' => 'Informace', 'Default' => ''),
     ),
   ),

trunk/sql/updates/446.sql

@@ -4 +4 @@
 RENAME TABLE `StockCard` TO `Product` ;
 ALTER TABLE `Product` CHANGE `Code` `Code` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_czech_ci NULL ;
+ALTER TABLE `Product` ADD `BuyPrice` FLOAT NOT NULL AFTER `SellPrice`;
 
 DROP TABLE IF EXISTS `StockItem`;
@@ -31 +32 @@
 ALTER TABLE `StockItem` CHANGE `Segment` `Segment` INT( 11 ) NULL;
 ALTER TABLE `StockItem` ADD `Info` TEXT NOT NULL ;
+ALTER TABLE `StockItem` CHANGE `TimeElimination` `TimeElimination` DATE NULL;
 
 ALTER TABLE `Stock` ADD `Location` INT NULL , ADD INDEX ( `Location` );

trunk/temp/product.php

@@ -13 +13 @@
   
   $System->Database->insert('Product', array('Name' => $DbRow['Name'], 'SellPrice' => $DbRow['Price'],
-     'Consumption' => $DbRow['Consumption'], 'UnitOfMeasure' => 1, 'VAT' => 20));
+    'BuyPrice' => $DbRow['Price'],
+    'Consumption' => $DbRow['Consumption'], 'UnitOfMeasure' => 1, 'VAT' => 20));
   $ProductId = $System->Database->insert_id;
   $Database->insert('StockItem', array('Stock' => 1, 'Product' => $ProductId, 'Amount' => $DbRow['Count'],
     'Segment' => $DbRow['Segment'], 'TimeEnlistment' => $DbRow['Date'], 'SerialNumber' => $DbRow['DeviceId'],
-      'Location' => $DbRow['User'], 'Info' => $DbRow['Info']));
+    'Location' => $DbRow['User'], 'Info' => $DbRow['Info']));
 }