-- MySQL dump 10.13 Distrib 5.1.55, for Win32 (ia32) -- -- Host: localhost Database: StockManage -- ------------------------------------------------------ -- Server version 5.1.55-community-log /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; drop database if exists StockManage; create database StockManage; use StockManage; -- -- Table structure for table `shop` -- DROP TABLE IF EXISTS `shop`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `shop` ( `Id` int(4) NOT NULL AUTO_INCREMENT, `ShopID` varchar(32) NOT NULL, `ShopName` varchar(32) NOT NULL, PRIMARY KEY (`Id`), UNIQUE KEY `ShopID` (`ShopID`), UNIQUE KEY `ShopName` (`ShopName`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `shop` -- LOCK TABLES `shop` WRITE; /*!40000 ALTER TABLE `shop` DISABLE KEYS */; INSERT INTO `shop` VALUES (1,'001','总店'); /*!40000 ALTER TABLE `shop` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `shopstorage` -- DROP TABLE IF EXISTS `shopstorage`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `shopstorage` ( `Id` int(4) NOT NULL AUTO_INCREMENT, `ShopID` varchar(32) NOT NULL, `TypeID` varchar(32) NOT NULL, `ModelID` varchar(32) NOT NULL, `StorNumber` int(4) unsigned NOT NULL, PRIMARY KEY (`Id`), UNIQUE KEY `ShopID` (`ShopID`,`TypeID`,`ModelID`), KEY `TypeID` (`TypeID`), KEY `ModelID` (`ModelID`), CONSTRAINT `shopstorage_ibfk_1` FOREIGN KEY (`ShopID`) REFERENCES `shop` (`ShopID`), CONSTRAINT `shopstorage_ibfk_2` FOREIGN KEY (`TypeID`) REFERENCES `waretype` (`TypeID`), CONSTRAINT `shopstorage_ibfk_3` FOREIGN KEY (`ModelID`) REFERENCES `waremodel` (`ModelID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `shopstorage` -- LOCK TABLES `shopstorage` WRITE; /*!40000 ALTER TABLE `shopstorage` DISABLE KEYS */; /*!40000 ALTER TABLE `shopstorage` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `waredispatch` -- DROP TABLE IF EXISTS `waredispatch`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `waredispatch` ( `Id` int(8) NOT NULL AUTO_INCREMENT, `TypeID` varchar(32) NOT NULL, `ModelID` varchar(32) NOT NULL, `ShopID` varchar(32) NOT NULL, `DispNumber` int(4) unsigned NOT NULL, `DispDate` date NOT NULL, `SaleState` enum('NoSale','SomeSale','AllSale') NOT NULL, `RemainNum` int(4) unsigned NOT NULL, `StockRecord` varchar(64) NOT NULL, PRIMARY KEY (`Id`), KEY `TypeID` (`TypeID`), KEY `TypeID_2` (`TypeID`,`ModelID`), KEY `ShopID` (`ShopID`), KEY `DispDate` (`DispDate`), KEY `ModelID` (`ModelID`), CONSTRAINT `waredispatch_ibfk_1` FOREIGN KEY (`TypeID`) REFERENCES `waretype` (`TypeID`), CONSTRAINT `waredispatch_ibfk_2` FOREIGN KEY (`ModelID`) REFERENCES `waremodel` (`ModelID`), CONSTRAINT `waredispatch_ibfk_3` FOREIGN KEY (`ShopID`) REFERENCES `shop` (`ShopID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `waredispatch` -- LOCK TABLES `waredispatch` WRITE; /*!40000 ALTER TABLE `waredispatch` DISABLE KEYS */; /*!40000 ALTER TABLE `waredispatch` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `waremodel` -- DROP TABLE IF EXISTS `waremodel`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `waremodel` ( `Id` int(4) NOT NULL AUTO_INCREMENT, `ModelID` varchar(32) NOT NULL, `ModelName` varchar(32) NOT NULL, `TypeID` varchar(32) NOT NULL, PRIMARY KEY (`Id`), UNIQUE KEY `ModelID` (`ModelID`), UNIQUE KEY `ModelName` (`ModelName`,`TypeID`), KEY `TypeID` (`TypeID`), CONSTRAINT `waremodel_ibfk_1` FOREIGN KEY (`TypeID`) REFERENCES `waretype` (`TypeID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `waremodel` -- LOCK TABLES `waremodel` WRITE; /*!40000 ALTER TABLE `waremodel` DISABLE KEYS */; /*!40000 ALTER TABLE `waremodel` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `warereject` -- DROP TABLE IF EXISTS `warereject`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `warereject` ( `Id` int(8) NOT NULL AUTO_INCREMENT, `TypeID` varchar(32) NOT NULL, `ModelID` varchar(32) NOT NULL, `ShopID` varchar(32) NOT NULL, `RejNumber` int(4) unsigned NOT NULL, `RejDate` date NOT NULL, `DispRecord` varchar(64) NOT NULL, PRIMARY KEY (`Id`), KEY `TypeID` (`TypeID`), KEY `TypeID_2` (`TypeID`,`ModelID`), KEY `ShopID` (`ShopID`), KEY `RejDate` (`RejDate`), KEY `ModelID` (`ModelID`), CONSTRAINT `warereject_ibfk_1` FOREIGN KEY (`TypeID`) REFERENCES `waretype` (`TypeID`), CONSTRAINT `warereject_ibfk_2` FOREIGN KEY (`ModelID`) REFERENCES `waremodel` (`ModelID`), CONSTRAINT `warereject_ibfk_3` FOREIGN KEY (`ShopID`) REFERENCES `shop` (`ShopID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `warereject` -- LOCK TABLES `warereject` WRITE; /*!40000 ALTER TABLE `warereject` DISABLE KEYS */; /*!40000 ALTER TABLE `warereject` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `waresale` -- DROP TABLE IF EXISTS `waresale`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `waresale` ( `Id` int(8) NOT NULL AUTO_INCREMENT, `TypeID` varchar(32) NOT NULL, `ModelID` varchar(32) NOT NULL, `ShopID` varchar(32) NOT NULL, `SaleNumber` int(4) unsigned NOT NULL, `SaleDate` date NOT NULL, `DispRecord` varchar(64) NOT NULL, PRIMARY KEY (`Id`), KEY `TypeID` (`TypeID`), KEY `TypeID_2` (`TypeID`,`ModelID`), KEY `ShopID` (`ShopID`), KEY `SaleDate` (`SaleDate`), KEY `ModelID` (`ModelID`), CONSTRAINT `waresale_ibfk_1` FOREIGN KEY (`TypeID`) REFERENCES `waretype` (`TypeID`), CONSTRAINT `waresale_ibfk_2` FOREIGN KEY (`ModelID`) REFERENCES `waremodel` (`ModelID`), CONSTRAINT `waresale_ibfk_3` FOREIGN KEY (`ShopID`) REFERENCES `shop` (`ShopID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `waresale` -- LOCK TABLES `waresale` WRITE; /*!40000 ALTER TABLE `waresale` DISABLE KEYS */; /*!40000 ALTER TABLE `waresale` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `warestock` -- DROP TABLE IF EXISTS `warestock`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `warestock` ( `Id` int(8) NOT NULL AUTO_INCREMENT, `TypeID` varchar(32) NOT NULL, `ModelID` varchar(32) NOT NULL, `StockNumber` int(4) unsigned NOT NULL, `StockDate` date NOT NULL, `DispState` enum('NoDisp','SomeDisp','AllDisp') NOT NULL, `RemainNum` int(4) unsigned NOT NULL, PRIMARY KEY (`Id`), KEY `TypeID` (`TypeID`), KEY `TypeID_2` (`TypeID`,`ModelID`), KEY `StockDate` (`StockDate`), KEY `ModelID` (`ModelID`), CONSTRAINT `warestock_ibfk_1` FOREIGN KEY (`TypeID`) REFERENCES `waretype` (`TypeID`), CONSTRAINT `warestock_ibfk_2` FOREIGN KEY (`ModelID`) REFERENCES `waremodel` (`ModelID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `warestock` -- LOCK TABLES `warestock` WRITE; /*!40000 ALTER TABLE `warestock` DISABLE KEYS */; /*!40000 ALTER TABLE `warestock` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `warestorage` -- DROP TABLE IF EXISTS `warestorage`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `warestorage` ( `Id` int(4) NOT NULL AUTO_INCREMENT, `TypeID` varchar(32) NOT NULL, `ModelID` varchar(32) NOT NULL, `StorNumber` int(4) unsigned NOT NULL, PRIMARY KEY (`Id`), KEY `TypeID` (`TypeID`), KEY `ModelID` (`ModelID`), CONSTRAINT `warestorage_ibfk_1` FOREIGN KEY (`TypeID`) REFERENCES `waretype` (`TypeID`), CONSTRAINT `warestorage_ibfk_2` FOREIGN KEY (`ModelID`) REFERENCES `waremodel` (`ModelID`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `warestorage` -- LOCK TABLES `warestorage` WRITE; /*!40000 ALTER TABLE `warestorage` DISABLE KEYS */; /*!40000 ALTER TABLE `warestorage` ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table `waretype` -- DROP TABLE IF EXISTS `waretype`; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE `waretype` ( `Id` int(4) NOT NULL AUTO_INCREMENT, `TypeID` varchar(32) NOT NULL, `TypeName` varchar(32) NOT NULL, PRIMARY KEY (`Id`), UNIQUE KEY `TypeID` (`TypeID`), UNIQUE KEY `TypeName` (`TypeName`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*!40101 SET character_set_client = @saved_cs_client */; -- -- Dumping data for table `waretype` -- LOCK TABLES `waretype` WRITE; /*!40000 ALTER TABLE `waretype` DISABLE KEYS */; /*!40000 ALTER TABLE `waretype` ENABLE KEYS */; UNLOCK TABLES; -- -- Dumping routines for database 'StockManage' -- /*!50003 DROP PROCEDURE IF EXISTS `Pro_Clear` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8 */ ; /*!50003 SET character_set_results = utf8 */ ; /*!50003 SET collation_connection = utf8_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `Pro_Clear`() begin truncate table warereject; truncate table waresale; truncate table waredispatch; truncate table warestock; truncate table shopstorage; truncate table warestorage; truncate table waremodel; truncate table waretype; truncate table shop; insert into shop(ShopID, ShopName) values ('001', '总店'); end */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `Pro_DelShop` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8 */ ; /*!50003 SET character_set_results = utf8 */ ; /*!50003 SET collation_connection = utf8_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `Pro_DelShop`(in szShopIDs varchar(4096)) begin declare strSql varchar(4096); set strSql = concat('delete from Shop where ShopID in (', szShopIDs, ')'); set @sql1 = strSql; prepare stmt_p from @sql1; execute stmt_p; end */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `Pro_DelWareModel` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8 */ ; /*!50003 SET character_set_results = utf8 */ ; /*!50003 SET collation_connection = utf8_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `Pro_DelWareModel`(in szWareModelIDs varchar(4096)) begin declare strSql varchar(4096); set strSql = concat('delete from WareModel where ModelID in (', szWareModelIDs, ')'); set @sql1 = strSql; prepare stmt_p from @sql1; execute stmt_p; end */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `Pro_DelWareType` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8 */ ; /*!50003 SET character_set_results = utf8 */ ; /*!50003 SET collation_connection = utf8_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `Pro_DelWareType`(in szWareTypeIDs varchar(4096)) begin declare strSql varchar(4096); set strSql = concat('delete from WareType where TypeID in (', szWareTypeIDs, ')'); set @sql1 = strSql; prepare stmt_p from @sql1; execute stmt_p; end */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `Pro_Disp` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8 */ ; /*!50003 SET character_set_results = utf8 */ ; /*!50003 SET collation_connection = utf8_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `Pro_Disp`(in p_TypeID varchar(32), in p_ModelID varchar(32), in p_DispNum int, in p_DispDate varchar(32), in p_DispShop varchar(32)) BEGIN declare sum int default 0; declare gap int default 0; declare every_id int default 0; declare every_num int default 0; declare record varchar(64); set record = '';-- 不能置为NULL,否则后面concat就加不上内容 start TRANSACTION; while_label:WHILE TRUE DO set @stmt = concat("select Id, RemainNum from WareStock where TypeID = '", p_TypeID, "' and ModelID = '", p_ModelID, "' and DispState <> 'AllDisp' order by StockDate, Id asc limit 0, 1 into @t_Id, @t_num"); prepare s from @stmt; execute s; DEALLOCATE prepare s; set @stmt = NULL; set every_id = @t_Id; set every_num = @t_num; IF (sum + every_num) = p_DispNum THEN UPDATE WareStock set RemainNum = 0, DispState = 'AllDisp' where Id = every_id; set record = concat(record, cast(every_id as char(8)), "|", cast(every_num as char(8)), ","); leave while_label; elseif (sum + every_num > p_DispNum) THEN -- elseif 不是 else if set gap = p_DispNum - sum; update WareStock set RemainNum = every_num - gap, DispState = 'SomeDisp' where Id = every_id;-- 可以直接用运算 set record = concat(record, cast(every_id as char(8)), "|", cast(gap as char(8)), ","); leave while_label; else update WareStock set RemainNum = 0, DispState = 'AllDisp' where Id = every_id; set record = concat(record, cast(every_id as char(8)), "|", cast(every_num as char(8)), ","); set sum = sum + every_num; end if; end WHILE; set @stmt2 = concat("insert into waredispatch (TypeID, ModelID, DispNumber, ShopID, DispDate, SaleState, RemainNum, StockRecord) values ('", p_TypeID, "','", p_ModelID, "',", cast(p_DispNum as char(8)), ",'", p_DispShop, "','", p_DispDate, "','NoSale',", cast(p_DispNum as char(8)), ",'", record, "')"); prepare s2 from @stmt2; execute s2; deallocate prepare s2; set @stmt2 = NULL; update ShopStorage set StorNumber = StorNumber - p_DispNum where ShopID = '001' and TypeID = p_TypeID and ModelID = p_ModelID; insert into shopstorage (ShopID, TypeID, ModelID, StorNumber) values (p_DispShop, p_TypeID, p_ModelID, cast(p_DispNum as char(8))) on duplicate key update StorNumber = StorNumber + p_DispNum; COMMIT; end */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `Pro_RecoverDisp` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8 */ ; /*!50003 SET character_set_results = utf8 */ ; /*!50003 SET collation_connection = utf8_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `Pro_RecoverDisp`(in p_Record varchar(128), in p_Cnt int, in p_DelID int) BEGIN declare i int; declare rec int default 0; declare ori int default 0; declare stock int default 0; declare subRemain varchar(128); start TRANSACTION; set i = 0; set subRemain = p_Record; while i < p_Cnt DO set @every_sub = substring_index(SubRemain, ',', 1); set SubRemain = substring_index(SubRemain, ',', i - p_Cnt); set @t_id = substring_index(@every_sub, '|', 1); set @t_rec = substring_index(@every_sub, '|', -1); set rec = @t_rec; select RemainNum from warestock where Id = @t_id into @t_ori; select StockNumber from warestock where Id = @t_id into @t_stock; set ori = @t_ori; set stock = @t_stock; if ori + rec < stock THEN update warestock set RemainNum = ori + rec, DispState = 'SomeDisp' where Id = @t_id; elseif ori + rec = stock THEN update warestock set RemainNum = ori + rec, DispState = 'NoDisp' where Id = @t_id; end if; set i = i + 1; set rec = 0; set ori = 0; set stock = 0; end while; select TypeID, ModelID, ShopID, DispNumber from WareDispatch where Id = p_DelID into @t_type, @t_model, @t_shop, @t_num; update ShopStorage set StorNumber = StorNumber + @t_num where ShopID = '001' and TypeID = @t_type and ModelID = @t_model; update ShopStorage set StorNumber = StorNumber - @t_num where ShopID = @t_shop and TypeID = @t_type and ModelID = @t_model; delete from waredispatch where Id = p_DelID; commit; END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `Pro_RecoverRej` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8 */ ; /*!50003 SET character_set_results = utf8 */ ; /*!50003 SET collation_connection = utf8_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `Pro_RecoverRej`(in p_Record varchar(128), in p_Cnt int, in p_DelID int) BEGIN declare i int; declare rec int default 0; declare ori int default 0; declare disp int default 0; declare subRemain varchar(128); start TRANSACTION; set i = 0; set subRemain = p_Record; while i < p_Cnt DO set @every_sub = substring_index(SubRemain, ',', 1); set SubRemain = substring_index(SubRemain, ',', i - p_Cnt); set @t_id = substring_index(@every_sub, '|', 1); set @t_rec = substring_index(@every_sub, '|', -1); set rec = @t_rec; select RemainNum from waredispatch where Id = @t_id into @t_ori; select DispNumber from waredispatch where Id = @t_id into @t_disp; set ori = @t_ori; set disp = @t_disp; if ori + rec < disp THEN update waredispatch set RemainNum = ori + rec, SaleState = 'SomeSale' where Id = @t_id; elseif ori + rec = disp THEN update waredispatch set RemainNum = ori + rec, SaleState = 'NoSale' where Id = @t_id; end if; set i = i + 1; set rec = 0; set ori = 0; set disp = 0; end while; select TypeID, ModelID, ShopID, RejNumber from WareReject where Id = p_DelID into @t_type, @t_model, @t_shop, @t_num; update ShopStorage set StorNumber = StorNumber + @t_num where ShopID = @t_shop and TypeID = @t_type and ModelID = @t_model; update ShopStorage set StorNumber = StorNumber - @t_num where ShopID = '001' and TypeID = @t_type and ModelID = @t_model; delete from WareReject where Id = p_DelID; commit; END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `Pro_RecoverSale` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8 */ ; /*!50003 SET character_set_results = utf8 */ ; /*!50003 SET collation_connection = utf8_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `Pro_RecoverSale`(in p_Record varchar(128), in p_Cnt int, in p_DelID int) BEGIN declare i int; declare rec int default 0; declare ori int default 0; declare disp int default 0; declare subRemain varchar(128); start TRANSACTION; set i = 0; set subRemain = p_Record; while i < p_Cnt DO set @every_sub = substring_index(SubRemain, ',', 1); set SubRemain = substring_index(SubRemain, ',', i - p_Cnt); set @t_id = substring_index(@every_sub, '|', 1); set @t_rec = substring_index(@every_sub, '|', -1); set rec = @t_rec; select RemainNum from waredispatch where Id = @t_id into @t_ori; select DispNumber from waredispatch where Id = @t_id into @t_disp; set ori = @t_ori; set disp = @t_disp; if ori + rec < disp THEN update waredispatch set RemainNum = ori + rec, SaleState = 'SomeSale' where Id = @t_id; elseif ori + rec = disp THEN update waredispatch set RemainNum = ori + rec, SaleState = 'NoSale' where Id = @t_id; end if; set i = i + 1; set rec = 0; set ori = 0; set disp = 0; end while; select TypeID, ModelID, ShopID, SaleNumber from waresale where Id = p_DelID into @t_type, @t_model, @t_shop, @t_num; update ShopStorage set StorNumber = StorNumber + @t_num where ShopID = @t_shop and TypeID = @t_type and ModelID = @t_model; delete from waresale where Id = p_DelID; commit; END */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `Pro_Reject` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8 */ ; /*!50003 SET character_set_results = utf8 */ ; /*!50003 SET collation_connection = utf8_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `Pro_Reject`(in p_TypeID varchar(32), in p_ModelID varchar(32), in p_ShopID varchar(32), in p_RejNum int, in p_RejDate varchar(32)) BEGIN declare sum int default 0; declare gap int default 0; declare every_id int default 0; declare every_num int default 0; declare record varchar(64); set record = '';-- 不能置为NULL,否则后面concat就加不上内容 start TRANSACTION; while_label:WHILE TRUE DO set @stmt = concat("select Id, RemainNum from WareDispatch where TypeID = '", p_TypeID, "' and ModelID = '", p_ModelID, "' and ShopID = '", p_ShopID, "' and SaleState <> 'AllSale' order by DispDate, Id asc limit 0, 1 into @t_Id, @t_num"); prepare s from @stmt; execute s; DEALLOCATE prepare s; set @stmt = NULL; set every_id = @t_Id; set every_num = @t_num; IF (sum + every_num) = p_RejNum THEN UPDATE waredispatch set RemainNum = 0, SaleState = 'AllSale' where Id = every_id; set record = concat(record, cast(every_id as char(8)), "|", cast(every_num as char(8)), ","); leave while_label; elseif (sum + every_num > p_RejNum) THEN -- elseif 不是 else if set gap = p_RejNum - sum; update waredispatch set RemainNum = every_num - gap, SaleState = 'SomeSale' where Id = every_id;-- 可以直接用运算 set record = concat(record, cast(every_id as char(8)), "|", cast(gap as char(8)), ","); leave while_label; else update waredispatch set RemainNum = 0, SaleState = 'AllSale' where Id = every_id; set record = concat(record, cast(every_id as char(8)), "|", cast(every_num as char(8)), ","); set sum = sum + every_num; end if; end WHILE; set @stmt2 = concat("insert into WareReject (TypeID, ModelID, ShopID, RejNumber, RejDate, DispRecord) values ('", p_TypeID, "','", p_ModelID, "','", p_ShopID, "',", cast(p_RejNum as char(8)), ",'", p_RejDate, "','", record, "')"); prepare s2 from @stmt2; execute s2; deallocate prepare s2; set @stmt2 = NULL; update ShopStorage set StorNumber = StorNumber - p_RejNum where ShopID = p_ShopID and TypeID = p_TypeID and ModelID = p_ModelID; update ShopStorage set StorNumber = StorNumber + p_RejNum where ShopID = '001' and TypeID = p_TypeID and ModelID = p_ModelID; COMMIT; end */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!50003 DROP PROCEDURE IF EXISTS `Pro_Sale` */; /*!50003 SET @saved_cs_client = @@character_set_client */ ; /*!50003 SET @saved_cs_results = @@character_set_results */ ; /*!50003 SET @saved_col_connection = @@collation_connection */ ; /*!50003 SET character_set_client = utf8 */ ; /*!50003 SET character_set_results = utf8 */ ; /*!50003 SET collation_connection = utf8_general_ci */ ; /*!50003 SET @saved_sql_mode = @@sql_mode */ ; /*!50003 SET sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ; DELIMITER ;; /*!50003 CREATE*/ /*!50020 DEFINER=`root`@`localhost`*/ /*!50003 PROCEDURE `Pro_Sale`(in p_TypeID varchar(32), in p_ModelID varchar(32), in p_ShopID varchar(32), in p_SaleNum int, in p_SaleDate varchar(32)) BEGIN declare sum int default 0; declare gap int default 0; declare every_id int default 0; declare every_num int default 0; declare record varchar(64); set record = '';-- 不能置为NULL,否则后面concat就加不上内容 start TRANSACTION; while_label:WHILE TRUE DO set @stmt = concat("select Id, RemainNum from WareDispatch where TypeID = '", p_TypeID, "' and ModelID = '", p_ModelID, "' and ShopID = '", p_ShopID, "' and SaleState <> 'AllSale' order by DispDate, Id asc limit 0, 1 into @t_Id, @t_num"); prepare s from @stmt; execute s; DEALLOCATE prepare s; set @stmt = NULL; set every_id = @t_Id; set every_num = @t_num; IF (sum + every_num) = p_SaleNum THEN UPDATE waredispatch set RemainNum = 0, SaleState = 'AllSale' where Id = every_id; set record = concat(record, cast(every_id as char(8)), "|", cast(every_num as char(8)), ","); leave while_label; elseif (sum + every_num > p_SaleNum) THEN -- elseif 不是 else if set gap = p_SaleNum - sum; update waredispatch set RemainNum = every_num - gap, SaleState = 'SomeSale' where Id = every_id;-- 可以直接用运算 set record = concat(record, cast(every_id as char(8)), "|", cast(gap as char(8)), ","); leave while_label; else update waredispatch set RemainNum = 0, SaleState = 'AllSale' where Id = every_id; set record = concat(record, cast(every_id as char(8)), "|", cast(every_num as char(8)), ","); set sum = sum + every_num; end if; end WHILE; set @stmt2 = concat("insert into WareSale (TypeID, ModelID, ShopID, SaleNumber, SaleDate, DispRecord) values ('", p_TypeID, "','", p_ModelID, "','", p_ShopID, "',", cast(p_SaleNum as char(8)), ",'", p_SaleDate, "','", record, "')"); prepare s2 from @stmt2; execute s2; deallocate prepare s2; set @stmt2 = NULL; update ShopStorage set StorNumber = StorNumber - p_SaleNum where ShopID = p_ShopID and TypeID = p_TypeID and ModelID = p_ModelID; COMMIT; end */;; DELIMITER ; /*!50003 SET sql_mode = @saved_sql_mode */ ; /*!50003 SET character_set_client = @saved_cs_client */ ; /*!50003 SET character_set_results = @saved_cs_results */ ; /*!50003 SET collation_connection = @saved_col_connection */ ; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2011-05-14 21:16:12