生成订单存储过程(mysql)

/*
 *生成商超订单存储过程
 *
 */
DELIMITER $$

DROP PROCEDURE IF EXISTS generate_storeOrder_cart$$

CREATE DEFINER=`lixin`@`%` PROCEDURE generate_storeOrder_cart(IN _orderNo VARCHAR(50),IN _customerId BIGINT,IN _sendDescription VARCHAR(200),IN _remark VARCHAR(200),IN _amount DECIMAL(13,2),IN _source INT,IN _customerAddId BIGINT,
  IN _couponId BIGINT,IN _cartIds VARCHAR(4000),IN _goodsIds VARCHAR(4000),IN _numIds VARCHAR(2000),OUT _code VARCHAR(6),OUT _msg VARCHAR(200),OUT _orderId BIGINT)
label_pro:BEGIN
/**
 过程说明：新增商超订单
 创建人：luolong
 创建时间：2016412
 入参：     
       _orderNo:订单编号
       _customerId：用户id,
       _sendDescription:送达时间描述
       _remark：订单备注
       _amount:订单金额
       _source：订单来源
       _customerAdd：收货地址id
       _couponId:优惠劵id
       _cartIds:购物车ids
       _goodsIds:商品ids
       _numIds:商品数量ids
 出参：
       _orderId：订单id
       _code:返回编码
       _msg:返回信息
       
*/
  DECLARE cnt INT DEFAULT 0; #_goodsIds被分割数据个数
  DECLARE cnt_i INT DEFAULT 0;
  DECLARE size INT;#用户未支付的订单size
  DECLARE orderNo VARCHAR(50);#订单编码
  DECLARE orderId INT;#订单id
  DECLARE cartId INT;#购物车id
  DECLARE goodsId INT;#商品id
  DECLARE goodsNumber INT;#商品数量
  DECLARE shopId,goodsProperty,goodsInventory INT;#店铺id,商品属性,库存
  DECLARE shopEnabledFlag VARCHAR(2);#店铺是否可用 'Y'代表可用 'N'代表不可用
  DECLARE goodsName VARCHAR(100);#商品名称
  DECLARE goodsInPrice,goodsOutPrice,commissionRate DECIMAL(13,2) DEFAULT 0.00;#商品进价,商品售价,佣金比例
  DECLARE couponState,activityType INT;#优惠劵状态,优惠劵活动类型
  DECLARE distributionFlag INT;#店铺是否开启免配送 0 关闭 1 开启
  DECLARE distributionAmount,freeAmount DECIMAL(13,2);#配送金额  免配送金额
  DECLARE totalAmount,commissionAmountTotal,costAmountTotal,couponAmount DECIMAL(13,2) DEFAULT 0.00;#订单总金额,佣金总金额,进价总金额,优惠劵金额
 
  #异常声明
    DECLARE _err INT DEFAULT 0;
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING , NOT FOUND
  BEGIN
        SET _err=1;
        ROLLBACK;
  END;
  START TRANSACTION;
  SET _code='000000';
  SET _msg='处理成功';
  #查询用户是否有未支付的订单
  SELECT COUNT(id) INTO size FROM kb_store_order WHERE 1=1 AND state=1 AND created_by=_customerId;
  IF size >  0 THEN
     SET _code='000006';
     SET _msg='有未支付的订单,不能下单';
     SELECT _code,_msg;
     ROLLBACK;
     LEAVE label_pro;
  END IF;
  #获取订单编号,将订单编号后面拼上随机3位数
  SET orderNo=CONCAT(_orderNo,CEILING(RAND()*500+500));
  #生成商超订单
  INSERT INTO kb_store_order (order_no,state,pay_type,city_manage_id,rate_discount,amount,shop_id,
                            source,contacts,contact_phone,address,address_detail,remark,rank_flag,display_flag,integral,integral_money,
                            coupon_id,coupon_money,customerAdd_id,commission_amount,cost_amount,send_time,complete_time,send_description,created_by,creation_date,enabled_flag)
   VALUES(orderNo,1,null,null,null,0,null,_source,null,null,null,null,_remark,'N',1,null,null,_couponId,null,_customerAddId,0,0,null,null,_sendDescription,_customerId,NOW(),1);
  IF _err=1 THEN
     SET _code='999999';
     SET _msg='创建订单失败';
     SELECT _code,_msg;
     LEAVE label_pro;
  END IF;
  #获取订单id
  SET orderId = LAST_INSERT_ID();
  #获取_goodsIds被分割数据个数
  SET cnt = 1+(LENGTH(_goodsIds) - LENGTH(REPLACE(_goodsIds,',','')));
  #遍历_goodsIds(商品ids)
  WHILE cnt_i<cnt DO
        SET cnt_i = cnt_i + 1;
    SET cartId=REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(_cartIds,',',cnt_i)),',',1)) + 0;
    SET goodsId=REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(_goodsIds,',',cnt_i)),',',1)) + 0;
    SET goodsNumber=REVERSE(SUBSTRING_INDEX(REVERSE(SUBSTRING_INDEX(_numIds,',',cnt_i)),',',1)) + 0;
    #查询商品信息
    SELECT
            t1.shop_id,t4.enabled_flag,t2.name,t2.goods_property,t3.commission,t1.goods_in_price,t1.goods_out_price,IFNULL(t1.goods_inventory ,0),t4.distribution_flag,t4.distribution_amount,t4.free_amount
      INTO shopId,shopEnabledFlag,goodsName,goodsProperty,commissionRate,goodsInPrice,goodsOutPrice,goodsInventory,distributionFlag,distributionAmount,freeAmount
        FROM
             (
                    SELECT id,shop_id,goods_id,goods_state,goods_in_price,goods_out_price,goods_inventory FROM kb_store_goods_relation WHERE id=goodsId
                         UNION ALL
                     SELECT id,shop_id,goods_id,goods_state,goods_in_price,special_price goods_out_price,goods_inventory FROM kb_store_sale_goods_relation WHERE id=goodsId
             ) t1
            LEFT JOIN kb_goods t2 ON t1.goods_id = t2.id
            LEFT JOIN kb_goods_category t3 ON t2.second_id = t3.id
            LEFT JOIN kb_shop t4 ON t1.shop_id = t4.id            
            WHERE 1=1 AND t1.goods_state =4;
        IF _err=1 THEN
            SET _code='000009';
      SET _msg='该商品不存在或已下架';
      SELECT _code,_msg;
            ROLLBACK;
            LEAVE label_pro;
    END IF;
    IF goodsInventory < goodsNumber THEN
            SET _code='000007';
      SET _msg=CONCAT('[',goodsName,']库存不足');
      SELECT _code,_msg;
      ROLLBACK;
            LEAVE label_pro;
    END IF;
    IF shopEnabledFlag !='Y' THEN
      SET _code='000010';
      SET _msg='商铺不存在或已停用';
      SELECT _code,_msg;
      ROLLBACK;
            LEAVE label_pro;
    END IF;
    #新增订单详细信息
    INSERT INTO kb_store_order_detail(order_id,goods_id,parent_id,rate_discount,cost_price,discount_price,number,cost_total,commission_rate,commission_amount,cost_amount,discount_total)
            VALUES
    (orderId,goodsId,null,null,goodsOutPrice,null,goodsNumber,goodsOutPrice*goodsNumber,commissionRate,ROUND(0.01*goodsOutPrice*goodsNumber*commissionRate,2),goodsInPrice*goodsNumber,null);
    IF _err=1 THEN
            SET _code='999999';
            SET _msg='新增订单详细信息失败';
            SELECT _code,_msg;
            LEAVE label_pro;
        END IF;
        #更新商品库存
    IF goodsProperty=3 THEN
      UPDATE kb_store_sale_goods_relation set goods_inventory = IFNULL(goods_inventory,0)-goodsNumber,
            goods_sales_count=IFNULL(goods_sales_count,0)+goodsNumber
            WHERE id=goodsId;
    ELSE
      UPDATE kb_store_goods_relation set goods_inventory = IFNULL(goods_inventory,0)-goodsNumber,
            goods_sales_count=IFNULL(goods_sales_count,0)+goodsNumber
            WHERE id=goodsId;
    END IF;
    IF _err=1 THEN
            SET _code='999999';
      SET _msg='更新商品库存信息失败';
      SELECT _code,_msg;
            LEAVE label_pro;
    END IF;
    #删除购物车信息
    DELETE FROM kb_shopping_cart WHERE id=cartId;
    IF _err=1 THEN
            SET _code='999999';
      SET _msg='删除购物车信息失败';
      SELECT _code,_msg;
            LEAVE label_pro;
    END IF;
    
  END WHILE;
    #查询订单总金额,佣金金额,进价总金额
  SELECT SUM(cost_total),SUM(commission_amount),SUM(cost_amount) INTO totalAmount,commissionAmountTotal,costAmountTotal FROM kb_store_order_detail WHERE order_id = orderId;
  #店铺是否开启免配送(0 关闭  1 开启)
  IF distributionFlag = 0 THEN
    SET totalAmount = totalAmount + distributionAmount;
    SET freeAmount = 0;
  END IF;
  IF distributionFlag = 1 THEN
    IF totalAmount >= freeAmount THEN
      SET freeAmount = distributionAmount;
    ELSE
      SET totalAmount = totalAmount + distributionAmount;
      SET freeAmount =0;
    END IF;
  END IF;
  #有没有优惠劵id
    IF _couponId IS NOT NULL THEN
        SELECT t2.amount,t1.use_state,t1.activity_type INTO couponAmount,couponState,activityType FROM kb_customer_coupon_relation t1 LEFT JOIN kb_coupon t2 ON t1.coupon_id = t2.id WHERE t1.id = _couponId;
    SET totalAmount = totalAmount -couponAmount;
        IF couponState = 2 THEN
      SET _code='000011';
      SET _msg='该优惠卷已使用过了';
      SELECT _code,_msg;
      ROLLBACK;
      LEAVE label_pro;
    END IF;
    #更新优惠劵的状态为已使用
    UPDATE kb_customer_coupon_relation set use_state=2
        WHERE id=_couponId;
    IF _err=1 THEN
                SET _code='999999';
                SET _msg='更新优惠劵状态失败';
                SELECT _code,_msg;
                LEAVE label_pro;
        END IF;
    #更新用户与红包与优惠劵关系表
    IF activityType = 2 THEN
    UPDATE kb_customer_red_coupon_relation SET state = 2 WHERE customer_coupon_id=_couponId;
      IF _err=1 THEN
                SET _code='999999';
                SET _msg='用户与红包与优惠劵关系表状态失败';
                SELECT _code,_msg;
                LEAVE label_pro;
          END IF;
    END IF;
    #插入用户优惠劵使用记录表
    INSERT INTO kb_customer_coupon_log ( customer_id, customer_coupon_id, creation_date ) VALUES ( _customerId, _couponId, NOW() );
    IF _err=1 THEN
                SET _code='999999';
                SET _msg='插入用户优惠劵使用记录表失败';
                SELECT _code,_msg;
                LEAVE label_pro;
    END IF;
  END IF;    
  #判断传过来金额与算出来的总金额是否相等
  IF _amount!= totalAmount THEN
        SET _code='000008';
    SET _msg='传过来总金额有问题';
    SELECT _code,_msg;
    ROLLBACK;
    LEAVE label_pro;
  END IF;
  #更新订单金额
  UPDATE kb_store_order SET amount=totalAmount,commission_amount=commissionAmountTotal,cost_amount=costAmountTotal,
  coupon_money=couponAmount,shop_id=shopId,distribution_amount=distributionAmount,free_amount=freeAmount
  WHERE id = orderId;
  IF _err=1 THEN
    SET _code='999999';
    SET _msg='更新订单金额失败';
    SELECT _code,_msg;
    LEAVE label_pro;
  END IF;
  #新增订单日志
  INSERT INTO kb_store_order_log
        (order_id,state,description,created_by,creation_date) VALUES(orderId,1,'生成商超订单',_customerId,NOW());
    IF _err=1 THEN
    SET _code='999999';
    SET _msg='新增订单日志失败';
    SELECT _code,_msg;
    LEAVE label_pro;
  END IF;
  #修改最后使用地址的lastUseTime
  UPDATE kb_customer_address SET last_use_time=NOW() WHERE id= _customerAddId;
    IF _err=1 THEN
    SET _code='999999';
    SET _msg='修改最后使用地址的lastUseTime失败';
    SELECT _code,_msg;
    LEAVE label_pro;
  END IF;
  SET _orderId = orderId;
  SELECT _code,_msg,_orderId;
  COMMIT;      
    END$$

DELIMITER ;