MYSQL
DELIMITER $$
USE `oso_isp`$$
DROP PROCEDURE IF EXISTS `proc_wh_stock_in_order_ok`$$
CREATE PROCEDURE `proc_wh_stock_in_order_ok`(
IN in_id INT -- stock_in_order_id
, IN in_user_id INT -- 操作员id
)
BEGIN
DECLARE var_id INT DEFAULT 0;
DECLARE var_asset_type INT DEFAULT 1;
DECLARE var_quantity INT DEFAULT 0;
DECLARE var_item_id INT DEFAULT 0;
DECLARE var_i INT DEFAULT 0;
DECLARE var_asset_id INT DEFAULT 0;
DECLARE done INT DEFAULT -1;
/* 声明游标 */
DECLARE myCursor CURSOR FOR
SELECT a.id,b.asset_type,a.quantity FROM wh_stock_in_order_item a
INNER JOIN wh_asset b ON a.asset_id=b.id
WHERE a.stock_in_order_id=in_id;
/* 当游标到达尾部时,mysql自动设置done=1 */
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
/* 打开游标 */
OPEN myCursor;
/* 循环开始 */
myLoop: LOOP
/* 移动游标并赋值 */
FETCH myCursor INTO var_id,var_asset_type,var_quantity;
IF done = 1 THEN
UPDATE wh_stock_in_order SET STATUS=1 WHERE id=in_id;
LEAVE myLoop;
END IF;
SELECT id INTO var_asset_id FROM wh_asset_inventory WHERE stock_in_order_item_id = var_id;
IF var_asset_id > 0 THEN
UPDATE wh_asset_inventory s JOIN(
SELECT a.id
,a.asset_id
,c.code -- 插入后还要update,加上id的流水
,0 -- location_id
,b.supplier_id
,b.buyer_id
,a.manufacture_date
,b.purchase_date
,DATE_ADD(a.manufacture_date,INTERVAL c.warranty_period MONTH) AS end_repair_date
,0 -- keeping_department_id
,b.storekeeper_id
,b.Stockroom_id
,a.quantity
,0 -- status
,a.id -- stock_in_order_item_id
,a.unit_price
,0 -- is_deleted
-- ,created_on
,in_user_id -- created_by
-- ,last_modified_by
-- ,last_modified_on
FROM wh_stock_in_order_item a
INNER JOIN wh_stock_in_order b ON a.stock_in_order_id=b.id
INNER JOIN wh_asset c ON c.id=a.asset_id
WHERE a.id = var_id AND a.is_deleted=0;
) k
ON k.id = s.stock_in_order_item_id
SET s.`asset_id` = k.asset_id
,s.`code` = k.code
,s.`location_id` = 0
,s.`supplier_id` = k.supplier_id
,s.`buyer_id` = k.buyer_id
,s.`manufacture_date` = k.manufacture_date
,s.`purchase_date` = k.purchase_date
,s.`end_repair_date` = k.end_repair_date
,s.`keeping_department_id` = 0
,s.`storekeeper_id` = k.storekeeper_id
,s.`Stockroom_id` = k.Stockroom_id
,s.`quantity` = k.quantity
,s.`status` = 0
,s.`stock_in_order_item_id` = k.id
,s.unit_price = k.unit_price
,s.`is_deleted` = 0
-- ,`created_on`
,s.`created_by` = in_user_id
-- ,`last_modified_by`
-- ,`last_modified_on`
WHERE s.is_deleted = 0 ;
ITERATE myLoop;
END IF;
INSERT INTO `wh_asset_inventory`
(
`asset_id`
,`code`
,`location_id`
,`supplier_id`
,`buyer_id`
,`manufacture_date`
,`purchase_date`
,`end_repair_date`
,`keeping_department_id`
,`storekeeper_id`
,`Stockroom_id`
,`quantity`
,`status`
,`stock_in_order_item_id`
,unit_price
,`is_deleted`
-- ,`created_on`
,`created_by`
-- ,`last_modified_by`
-- ,`last_modified_on`
)
SELECT
a.asset_id
,c.code -- 插入后还要update,加上id的流水
,0 -- location_id
,b.supplier_id
,b.buyer_id
,a.manufacture_date
,b.purchase_date
,DATE_ADD(a.manufacture_date,INTERVAL c.warranty_period MONTH)
,0 -- keeping_department_id
,b.storekeeper_id
,b.Stockroom_id
,a.quantity
,0 -- status
,a.id -- stock_in_order_item_id
,a.unit_price
,0 -- is_deleted
-- ,created_on
,in_user_id -- created_by
-- ,last_modified_by
-- ,last_modified_on
FROM wh_stock_in_order_item a
INNER JOIN wh_stock_in_order b ON a.stock_in_order_id=b.id
INNER JOIN wh_asset c ON c.id=a.asset_id
WHERE a.id = var_id AND a.is_deleted=0;
SELECT LAST_INSERT_ID() INTO var_item_id;
UPDATE wh_asset a JOIN (SELECT asset_id,unit_price FROM wh_stock_in_order_item WHERE id = var_id) b ON b.asset_id=a.id SET a.reference_price=b.unit_price;
UPDATE wh_asset_inventory SET CODE=CONCAT(CODE,'-',id) WHERE id=var_item_id; -- 更新code
/* 循环结束 */
END LOOP myLoop;
/* 关闭游标 */
CLOSE myCursor;
END$$
DELIMITER ;