From 3c5fedf323ea5a43b29240ae3c0d52f74c6c2733 Mon Sep 17 00:00:00 2001 From: Thomas Mudrunka Date: Tue, 22 Nov 2011 15:52:56 +0100 Subject: [PATCH] Pridan system skladovych rezerv --- assistants/stats.inc.php | 12 ++++++++++-- install.sql | 9 +++++---- locale/cs/messages.inc.php | 4 ++++ 3 files changed, 19 insertions(+), 6 deletions(-) diff --git a/assistants/stats.inc.php b/assistants/stats.inc.php index 2169da1..ea294cc 100644 --- a/assistants/stats.inc.php +++ b/assistants/stats.inc.php @@ -18,14 +18,22 @@ $queries = array( //TODO: use build_query_select()!!! "Použito celkem $month" => 'SELECT COUNT(item_id),SUM(item_price_in) FROM item WHERE item_valid_till=0 AND status_id = 2'.$month_sql, "Prodáno celkem $month" - => 'SELECT COUNT(item_id),SUM(item_price_out),SUM(item_price_in),(SUM(item_price_out)-SUM(item_price_in)) FROM item WHERE item_valid_till=0 AND status_id = 3'.$month_sql, + => 'SELECT COUNT(item_id),SUM(item_price_out),SUM(item_price_in),(SUM(item_price_out)-SUM(item_price_in)) as sale_profit FROM item WHERE item_valid_till=0 AND status_id = 3'.$month_sql, "Skladem celkem $month" => 'SELECT COUNT(item_id),SUM(item_price_in) FROM item WHERE item_valid_till=0 AND status_id = 1'.$month_sql, - "Bilance celkem $month" + "Bilance celkem =(prodej - všechny nákupy) $month" => "SELECT ( SUM(item_price_out) -(SELECT SUM(item_price_in) FROM item WHERE item_valid_till=0$month_sql) ) FROM item WHERE item_valid_till=0 AND ( status_id = 3 )$month_sql", + "Nutno koupit" + => 'SELECT room_id,room_name,model_id,model_name,model_barcode,model_reserve,'. + ' COUNT(item_id),SUM(item_quantity),model_reserve-SUM(item_quantity) as item_quantity_to_buy'. + ' FROM item LEFT JOIN model USING(model_id) LEFT JOIN room USING(room_id)'. + ' WHERE item_valid_till=0 AND status_id=1'. + ' GROUP BY model_id,room_id'. + ' HAVING SUM(item_quantity) 'SELECT room_id,room_name,model_id,model_name,model_barcode,COUNT(item_id),SUM(item_quantity)'. ' FROM item LEFT JOIN model USING(model_id) LEFT JOIN room USING(room_id)'. diff --git a/install.sql b/install.sql index fb3a363..3212267 100644 --- a/install.sql +++ b/install.sql @@ -27,7 +27,7 @@ CREATE TABLE `category` ( `category_name` varchar(64) collate utf8_czech_ci NOT NULL, PRIMARY KEY (`category_id`), UNIQUE KEY `category_name` (`category_name`) -) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci; +) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci; SET character_set_client = @saved_cs_client; -- @@ -36,7 +36,7 @@ SET character_set_client = @saved_cs_client; LOCK TABLES `category` WRITE; /*!40000 ALTER TABLE `category` DISABLE KEYS */; -INSERT INTO `category` VALUES (1,'picovinky'),(2,'Tezka technika, Traktory, etc..'); +INSERT INTO `category` VALUES (3,'koren'),(8,'koren/a'),(10,'koren/a/a'),(11,'koren/a/c'),(9,'koren/a/d'),(12,'koren/a/o'),(13,'novy/x/y/z'),(1,'picovinky'),(2,'Tezka technika, Traktory, etc..'); /*!40000 ALTER TABLE `category` ENABLE KEYS */; UNLOCK TABLES; @@ -202,7 +202,7 @@ UNLOCK TABLES; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; --- Dump completed on 2011-10-25 5:51:31 +-- Dump completed on 2011-11-22 14:52:26 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; @@ -256,6 +256,7 @@ CREATE TABLE `model` ( `model_countable` int(1) NOT NULL default '1', `model_price_in` decimal(9,2) default NULL, `model_price_out` decimal(9,2) default NULL, + `model_reserve` int(11) unsigned default '0', `model_descript` varchar(1024) collate utf8_czech_ci NOT NULL, PRIMARY KEY (`model_id`), UNIQUE KEY `model_barcode` (`model_barcode`), @@ -263,7 +264,7 @@ CREATE TABLE `model` ( KEY `producer_id` (`producer_id`), CONSTRAINT `model_ibfk_1` FOREIGN KEY (`category_id`) REFERENCES `category` (`category_id`), CONSTRAINT `model_ibfk_2` FOREIGN KEY (`producer_id`) REFERENCES `producer` (`producer_id`) -) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci; +) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci; SET character_set_client = @saved_cs_client; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; diff --git a/locale/cs/messages.inc.php b/locale/cs/messages.inc.php index 614e7a3..9f99216 100644 --- a/locale/cs/messages.inc.php +++ b/locale/cs/messages.inc.php @@ -28,6 +28,7 @@ $LOCALE_MESSAGES = array( 'model_barcode:' => 'Čárový kód modelu:', 'model_price_in' => 'Předpokládaná cena', 'model_price_out' => 'Doporučená cena', + 'model_reserve' => 'Rezerva', 'model_countable' => 'Model má sériová č.', 'room_name' => 'Sklad', @@ -51,6 +52,9 @@ $LOCALE_MESSAGES = array( 'status_relations' => 'V tomto stavu', '_relations' => ' jinde', + 'sale_profit' => 'Zisk z prodeje', + 'item_quantity_to_buy' => 'Nakoupit minimálně', + 'item' => '[položka]', 'producer' => '[výrobce]', 'room' => '[sklad]', -- 2.30.2