CREATE DEFINER=`root`@`%` PROCEDURE `procInitData`(IN sId1 INT,IN sId2 INT,IN sId3 INT,IN sId4 INT,IN sId5 INT,IN sId6 INT,IN sId7 INT,IN sId8 INT,IN sId9 INT,IN sId10 INT,IN pName VARCHAR(50))
BEGIN
##传参例子:376,0,0,0,0,0,0,0,0,0,'肩并肩',sId为参加活动的店铺Id,pName为活动名称,
##sId没有则写0,一次最多传10个店铺id
##店铺ID
DECLARE shopId INT DEFAULT 0;
##店铺名称
DECLARE shopName varchar(64);
##活动ID
DECLARE promotionId INT DEFAULT 0;
##店铺活动ID
DECLARE shopPromotionId INT DEFAULT 0;
##活动名称
DECLARE promotionName varchar(64);
##商品ID
DECLARE commodityId INT DEFAULT 0;
##商品详情图片
DECLARE imagePath varchar(128);
##BANNER图片1的路径
DECLARE bannerImagePathOne varchar(128);
##BANNER图片2的路径
DECLARE bannerImagePathTwo varchar(128);
##BANNER图片3的路径
DECLARE bannerImagePathThree varchar(128);
##BANNER图片4的路径
DECLARE bannerImagePathFour varchar(128);
##BANNER图片5的路径
DECLARE bannerImagePathFive varchar(128);
##BANNER图片6的路径
DECLARE bannerImagePathSix varchar(128);
##商品名称
DECLARE commodityName varchar(64);
##商品价格
DECLARE commodityPrice INT DEFAULT 0;
##商品原价
DECLARE originalPrice INT DEFAULT 0;
##商品类型 1:平台活动商品,2:店铺活动商品，3：店铺商品（无活动，拓展字段）
DECLARE commodityType varchar(64);
##商品状态 1:上架 2:下架
DECLARE commodityState varchar(64);
##商品描述
DECLARE commodityDesc varchar(64);
##是否可用抵扣券
DECLARE couponFlag varchar(64);
##员工获得分润比例
DECLARE gainRate varchar(64);
#------------------------------------------
DECLARE done INT DEFAULT FALSE;
#变量输出结果
DECLARE `result` varchar(2000);
#定义一个接收结果集的游标查店铺
DECLARE cur_new CURSOR FOR select SHOP_ID,SHOP_NAME from t_shop where SHOP_ID in (sId1,sId2,sId3,sId4,sId5,sId6,sId7,sId8,sId9,sId10);
#定义一个接收结果集的游标查平台商品,商品状态要已上架
DECLARE cur_new_two CURSOR FOR SELECT COMMODITY_ID,COMMODITY_NAME,COMMODITY_PRICE,ORIGINAL_PRICE,
COMMODITY_TYPE,COMMODITY_STATE,COMMODITY_DESC,COUPON_FLAG,IMAGE_PATH,BANNER_IMAGE_PATH_ONE,BANNER_IMAGE_PATH_TWO,
BANNER_IMAGE_PATH_THREE,BANNER_IMAGE_PATH_FOUR,BANNER_IMAGE_PATH_FIVE,BANNER_IMAGE_PATH_SIX,GAIN_RATE
from t_commodity where SHOP_ID=0 and COMMODITY_STATE='1';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
#店铺参与的活动 pName COLLATE utf8_unicode_ci,把pName转成COLLATE utf8_unicode_ci格式因为数据库是该格式
select PROMOTION_ID,PROMOTION_NAME INTO @promotionIds,@promotionNames FROM t_promotion where PROMOTION_NAME COLLATE utf8mb4_unicode_ci = pName;
#打开游标
OPEN cur_new;
#遍历店游标
read_loop: LOOP
#把参数赋给变量shopId,shopName
FETCH NEXT from cur_new INTO shopId,shopName;
IF done THEN
LEAVE read_loop;
END IF;
##增加活动-店铺之间的关系
INSERT INTO `t_pool_shop_promotion` VALUES (NULL,@promotionIds,@promotionNames , shopId, shopName,
'','','0','0','0','0','0','0',
(SELECT DATE_ADD(current_date,INTERVAL 30 DAY)),NOW(),(SELECT DATE_ADD(current_date,INTERVAL 12 MONTH)),NOW(),NOW());
##查询店铺与活动之间的关系##活动结束时间要大于当前时间
select SHOP_PROMOTION_ID,PROMOTION_NAME INTO shopPromotionId,promotionName FROM t_pool_shop_promotion where SHOP_ID =shopId and PROMOTION_ID=@promotionId and PROMOTION_END_TIME >NOW();
SET done=0;
##平台商品游标-----------------开始---------------------------------------------------
OPEN cur_new_two;
##遍历游标（内循环）
inner_loop:LOOP
#把参数赋给变量
FETCH NEXT from cur_new_two INTO commodityId,commodityName,commodityPrice,originalPrice,commodityType,commodityState,commodityDesc,
couponFlag,imagePath,bannerImagePathOne,bannerImagePathTwo,bannerImagePathThree,bannerImagePathFour,bannerImagePathFive,bannerImagePathSix,gainRate;
IF done THEN
LEAVE inner_loop;
END IF;
set @result2 = '生成店铺活动商品!' ;
##生成店铺活动商品,shopPromotionId与promotionName另外设置
INSERT INTO `t_commodity` VALUES (NULL, shopId , shopPromotionId , promotionName, commodityName ,commodityPrice, originalPrice, NULL, '2', '1', commodityDesc,couponFlag,
imagePath, bannerImagePathOne,bannerImagePathTwo,bannerImagePathThree, bannerImagePathFour, bannerImagePathFive, bannerImagePathSix,gainRate,'0','0','0');
END LOOP inner_loop;
##平台商品游标------------------结束---------------------------------------------------------------
##关闭商品游标
CLOSE cur_new_two;
SET done=0;
END LOOP;
set @result = '处理成功!';
#关闭店铺游标
CLOSE cur_new;
#输出结果
select @result;
select @result2;
END
