From fae8a9349499cd045bc73e8fd22aeae52618a46c Mon Sep 17 00:00:00 2001 From: WangHan <wwh_work@126,com> Date: 星期四, 19 十二月 2024 10:02:02 +0800 Subject: [PATCH] 采购明细查询修改，查询本级及以下 --- consum-base/doc/更新正式环境需要添加.sql | 29 +++++++++++++++++++++++++++++ 1 files changed, 29 insertions(+), 0 deletions(-) diff --git "a/consum-base/doc/\346\233\264\346\226\260\346\255\243\345\274\217\347\216\257\345\242\203\351\234\200\350\246\201\346\267\273\345\212\240.sql" "b/consum-base/doc/\346\233\264\346\226\260\346\255\243\345\274\217\347\216\257\345\242\203\351\234\200\350\246\201\346\267\273\345\212\240.sql" index e69de29..acba5e1 100644 --- "a/consum-base/doc/\346\233\264\346\226\260\346\255\243\345\274\217\347\216\257\345\242\203\351\234\200\350\246\201\346\267\273\345\212\240.sql" +++ "b/consum-base/doc/\346\233\264\346\226\260\346\255\243\345\274\217\347\216\257\345\242\203\351\234\200\350\246\201\346\267\273\345\212\240.sql" @@ -0,0 +1,29 @@ +-- 鍒涘缓瀛樺偍杩囩▼寮�濮�----------- +DELIMITER // +CREATE PROCEDURE `InsertL_WH_GOODS` (IN p_WAREHOUSE_FLOW_ID BIGINT,IN p_WAREHOUSE_TYPE SMALLINT,IN p_WAREHOUSE_ID BIGINT,IN p_WH_GOODS_RECORD_ID BIGINT,IN p_RECORD_TYPE SMALLINT,IN p_WAREHOUSE_NAME VARCHAR (100),IN p_COST_TYPE SMALLINT,IN p_BASE_GOODS_TEMPLATE_ID BIGINT,IN p_GOODS_TEMPLATE_NAME VARCHAR (200),IN p_BASE_GOODS_MODELS_ID BIGINT,IN p_BASE_GOODS_MODELS_NAME VARCHAR (200),IN p_SUPPLIER VARCHAR (200),IN p_BUY_TYPE SMALLINT,IN p_UNIT VARCHAR (10),IN p_PRICE BIGINT,IN p_PROCURE_DATE BIGINT,IN p_STATES INT,IN p_DEAL_TIME BIGINT,IN p_times INT) BEGIN DECLARE i INT DEFAULT 1; DECLARE v_WH_GOODS_ID BIGINT; DECLARE CONTINUE + HANDLER FOR SQLEXCEPTION BEGIN-- An error occurred, rollback the transaction + ROLLBACK; END;-- Start the transaction +START TRANSACTION; WHILE i<=p_times + DO + INSERT INTO L_WH_GOODS (WAREHOUSE_TYPE,WAREHOUSE_ID,WAREHOUSE_NAME,COST_TYPE,BASE_GOODS_TEMPLATE_ID,GOODS_TEMPLATE_NAME,BASE_GOODS_MODELS_ID,BASE_GOODS_MODELS_NAME,SUPPLIER,BUY_TYPE,UNIT,PRICE,PROCURE_DATE,STATES) VALUES (p_WAREHOUSE_TYPE,p_WAREHOUSE_ID,p_WAREHOUSE_NAME,p_COST_TYPE,p_BASE_GOODS_TEMPLATE_ID,p_GOODS_TEMPLATE_NAME,p_BASE_GOODS_MODELS_ID,p_BASE_GOODS_MODELS_NAME,p_SUPPLIER,p_BUY_TYPE,p_UNIT,p_PRICE,p_PROCURE_DATE,p_STATES); + SET v_WH_GOODS_ID=LAST_INSERT_ID();-- 鎻掑叆鍚庤幏鍙朓D骞跺瓨鍏_WH_GOODS_RECORD_DETAILS琛� + INSERT INTO L_WH_GOODS_RECORD_DETAILS (WH_GOODS_RECORD_ID,WH_GOODS_ID,THIS_TYPE) VALUES (p_WH_GOODS_RECORD_ID,v_WH_GOODS_ID,p_RECORD_TYPE);-- 鎻掑叆鍒癓_GOODS_WH_RECORD琛� + UPDATE L_GOODS_WH_RECORD SET LAST_RECORD=0 WHERE WH_GOODS_ID=v_WH_GOODS_ID AND WAREHOUSE_ID=p_WAREHOUSE_ID AND WAREHOUSE_TYPE=p_WAREHOUSE_TYPE AND LAST_RECORD=1; + INSERT INTO L_GOODS_WH_RECORD (WAREHOUSE_FLOW_ID,WH_GOODS_ID,WAREHOUSE_TYPE,WAREHOUSE_ID,DEAL_TIME,LAST_RECORD) VALUES (p_WAREHOUSE_FLOW_ID,v_WH_GOODS_ID,p_WAREHOUSE_TYPE,p_WAREHOUSE_ID,p_DEAL_TIME,1); + SET i=i+1; END WHILE;-- If no errors, commit the transaction +COMMIT; END +-- 鍒涘缓瀛樺偍杩囩▼缁撴潫 ----------- + +-- 鍒涘缓瀛樺偍杩囩▼寮�濮�----------- +DELIMITER // +CREATE PROCEDURE `insert_WH_GOODS_RECORD_DETAILS` (IN WHGOODSIDList TEXT,IN WH_GOODS_RECORD_ID BIGINT,IN THIS_TYPE INT) BEGIN DECLARE i INT DEFAULT 1; DECLARE n INT DEFAULT 0; DECLARE WH_GOODS_ID BIGINT; +SET n=LENGTH(WHGOODSIDList)-LENGTH( + REPLACE (WHGOODSIDList,',',''))+1; WHILE i<=n +DO +SET WH_GOODS_ID=SUBSTRING_INDEX(SUBSTRING_INDEX(WHGOODSIDList,',',i),',',-1); +INSERT INTO L_WH_GOODS_RECORD_DETAILS (WH_GOODS_RECORD_ID,WH_GOODS_ID,THIS_TYPE) VALUES (WH_GOODS_RECORD_ID,WH_GOODS_ID,THIS_TYPE); +SET i=i+1; END WHILE; END +-- 鍒涘缓瀛樺偍杩囩▼缁撴潫 ----------- + +# 2023 12 07 +alter table base_category modify d_user_id BIGINT(16); -- Gitblit v1.9.1