/*
*生成商超订单存储过程
*
*/
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 ;
*生成商超订单存储过程
*
*/
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 ;