DELIMITER $$

USE `ecstore`$$

DROP PROCEDURE IF EXISTS `proc_add_warranty_card`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_add_warranty_card`()

BEGIN

     -- 获取异常信息

     DECLARE v_sql1 VARCHAR(500);

     DECLARE v_sql2 VARCHAR(500);

     #定义变量

     DECLARE w_warranty_id BIGINT(20) DEFAULT 1;

     DECLARE w_orderid BIGINT(20);

     DECLARE w_ordertime INT(10);

     DECLARE w_member_id MEDIUMINT(8);

     #定义游标遍历时，作为判断是否遍历完全部记录的标记

     DECLARE done1 INTEGER DEFAULT 0;

     DECLARE data_err INTEGER DEFAULT 0;

     DECLARE log_err INTEGER DEFAULT 0;

     #定义保修卡主表为C_WARRANTY

     DECLARE C_WARRANTY CURSOR FOR

    SELECT orde.order_id,

           orde.createtime,

           orde.member_id

    FROM `sdb_b2c_orders` AS orde

    WHERE orde.ship_status='' AND orde.status IN ('active','finish') AND (orde.warranty_id IS NULL);

     #声明当游标遍历完全部记录后将标志变量置成某个值

     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1=1;

     DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

     BEGIN

          ROLLBACK;

      GET DIAGNOSTICS CONDITION 1 v_sql1 = RETURNED_SQLSTATE,v_sql2= MESSAGE_TEXT;

      INSERT INTO `sdb_b2c_warranty_log` (`order_id`,`createtime`,`msg_text`)

      VALUES (w_orderid,UNIX_TIMESTAMP(CURDATE()),CONCAT(v_sql1,':',v_sql2));

      SET log_err=1;

     END;

     #手动提交事务

     SET autocommit=0;

     OPEN C_WARRANTY;

     #取出每条记录并赋值给相关变量，注意顺序

     FETCH C_WARRANTY INTO w_orderid, w_ordertime, w_member_id;

     SET w_warranty_id=CONCAT(DATE_FORMAT(NOW(), '%Y%m%d'),LPAD((w_warranty_id), 5, ''));

     #循环语句的关键词

     REPEAT

         -- 启动事务

         START TRANSACTION; 

         #保修卡主表添加

         INSERT INTO `sdb_b2c_warranty` (`warranty_id`,`order_id`,`ordertime`,`member_id`,`warranty_card_status`,`createtime`)

             VALUES (w_warranty_id,w_orderid,w_ordertime,w_member_id,'',UNIX_TIMESTAMP(CURDATE()));

             IF log_err=0 THEN

             #生成明细

             INSERT INTO `sdb_b2c_warranty_detail`(warranty_id,item_id,order_id,

                  obj_id,product_id,goods_id,type_id,bn,pn,`name`,nums,sendnum,addon,item_type)

             SELECT w_warranty_id,ite.item_id,ite.`order_id`,ite.obj_id,ite.product_id,

                 ite.goods_id,ite.type_id,ite.bn,pro.store_place,ite.name,ite.nums,

                 ite.sendnum,ite.addon,ite.item_type

            FROM`sdb_b2c_order_items` AS ite

            LEFT JOIN `sdb_b2c_products` AS pro ON pro.product_id=ite.product_id

            WHERE ite.order_id=w_orderid;

        END IF;

        #回写订单表保修卡号

        IF log_err=0 THEN

         UPDATE `sdb_b2c_orders` SET `warranty_id`=w_warranty_id WHERE `order_id`= w_orderid;

        END IF;

        COMMIT;

        SET log_err=0;

        SET  done1=0;

     #取出每条记录并赋值给相关变量，注意顺序

     FETCH C_WARRANTY INTO w_orderid, w_ordertime, w_member_id;

     SET  w_warranty_id =w_warranty_id+1;

     #循环语句结束

     UNTIL done1  END REPEAT;

     #关闭游标

     CLOSE C_WARRANTY;  

     BEGIN

     #如果是退货，则把保修卡状态改成无效

     DECLARE card_order_id BIGINT(20);

     -- 获取异常信息

         DECLARE v_sql1 VARCHAR(500);

         DECLARE v_sql2 VARCHAR(500);

     DECLARE card_warranty_id BIGINT(20);

     #标记循环结束

     DECLARE done2 INTEGER DEFAULT 0;

     DECLARE C_UPDATE_CARD_STATUS CURSOR FOR

             SELECT war.`order_id`,war.`warranty_id`

             FROM `sdb_b2c_orders`  AS orde

             JOIN `sdb_b2c_warranty` AS war ON orde.`order_id`=war.`order_id`

             WHERE orde.ship_status='';

     #声明当游标遍历完全部记录后将标志变量置成某个值

     DECLARE CONTINUE HANDLER FOR NOT FOUND SET done2= 1;

     DECLARE CONTINUE HANDLER FOR SQLEXCEPTION

     BEGIN

        GET DIAGNOSTICS CONDITION 1 v_sql1 = RETURNED_SQLSTATE,v_sql2= MESSAGE_TEXT;

        INSERT INTO `sdb_b2c_warranty_log` (`order_id`,`createtime`,`msg_text`)

        VALUES (w_orderid,UNIX_TIMESTAMP(CURDATE()),CONCAT(v_sql1,':',v_sql2));

     END;

     #打开明细游标

     OPEN C_UPDATE_CARD_STATUS;

        FETCH C_UPDATE_CARD_STATUS INTO card_order_id,card_warranty_id;

        REPEAT

          UPDATE sdb_b2c_warranty SET warranty_card_status='',invalid_reason='' WHERE warranty_card_status='' AND `order_id`=card_order_id;

          SET  done2=0;  #取出每条记录并赋值给相关变量，注意顺序

          FETCH C_UPDATE_CARD_STATUS INTO card_order_id,card_warranty_id;

        #循环语句结束

        UNTIL done2  END REPEAT;

     CLOSE C_UPDATE_CARD_STATUS;

     END;

END$$

DELIMITER ;